Prepare the migrations

    dbic-migration -Ilib status
    Schema is 3
    Deployed database is 2

So first off we've check the status of our system and can see the database is one version behind. Let's prepare the migration:

    dbic-migration -Ilib prepare

And as usual we can see a bunch of new version 3 directories and some 2-3 directories related to upgrades. As in the previous migration, we'll skip the 3-2 downgrade, instead covering downgrades in a later section. Here's some of the new directory structure:

    /share
      /fixtures
        /1
          (Same as before)
        /2
          (Same as before)
        /3
          /conf
            all_tables.json
            countries.json
      /migrations
        /_common
          (Same as before)
        /_source
          (System files for DBIC::DeploymentHandler)
        /SQLite
          /deploy
            /1
              (Same as before)
            /2
              (Same as before)
            /3
              001-auto-_VERSION.sql
              001-auto.sql
          /downgrade
            /2-1
            /3-2
          /upgrade
            /1-2
              (Same as before)
            /2-3
              001-auto.sql
      musicbase-schema.db

At this point there should be no surprises. You'll notice that "prepare" made a fresh "all_tables.json" for you, which has added your new bridge table and it also copied over your custom "countries.json" from the previous version. Please note that this is a dumb copy; no attempt has been made to convert the configuration to make sense with any database structural changes. You'll need to examine your custom configurations and manually make any needed changes. In this case the existing custom configuration is fine for this version, so we can move on to reviewing the deploy and upgrades.

There should be no surprises under "deploy/3/*". The only reason you'll need to poke into these files is if you need to see the full DDL as part of helping you craft the upgrades correctly, or if your DBA needs to tweak aspects of the design (change indexes, storage engines, etc.)

The real fun begins under "share/migrations/SQLite/upgrade/2-3/*". Again you have the "001-auto.sql" file, which is the first, best guess on how to upgrade the database. We need to poke into that and shape it into something that can work for both our database structure and our data. Let's look at the the suggestion. Open "share/migrations/SQLite/upgrade/2-3/001-auto.sql" in your text editor:

    BEGIN;

    CREATE TABLE artist_cd (
      artist_fk integer NOT NULL,
      cd_fk integer NOT NULL,
      PRIMARY KEY (artist_fk, cd_fk),
      FOREIGN KEY(artist_fk) REFERENCES artist(artist_id),
      FOREIGN KEY(cd_fk) REFERENCES cd(cd_id)
    );

    CREATE INDEX artist_cd_idx_artist_fk ON artist_cd (artist_fk);

    CREATE INDEX artist_cd_idx_cd_fk ON artist_cd (cd_fk);

    CREATE TEMPORARY TABLE cd_temp_alter (
      cd_id INTEGER PRIMARY KEY NOT NULL,
      title varchar(96) NOT NULL
    );

    INSERT INTO cd_temp_alter SELECT cd_id, title FROM cd;

    DROP TABLE cd;

    CREATE TABLE cd (
      cd_id INTEGER PRIMARY KEY NOT NULL,
      title varchar(96) NOT NULL
    );

    INSERT INTO cd SELECT cd_id, title FROM cd_temp_alter;

    DROP TABLE cd_temp_alter;

    COMMIT;

This doesn't look too bad. From first review it looks to me like we are just missing transfering data from the old relationship to the new bridge table. So like last time we will bust this up into a few steps, under new file names but under this directory. Lets handle this in bits.

    touch share/migrations/SQLite/upgrade/2-3/001-create_artist_cd.sql

And then open that in your editor. We will copy the first bit of the "001-auto.sql", having to do with creating the new table and indexes over:

    BEGIN;

    CREATE TABLE artist_cd (
      artist_fk integer NOT NULL,
      cd_fk integer NOT NULL,
      PRIMARY KEY (artist_fk, cd_fk),
      FOREIGN KEY(artist_fk) REFERENCES artist(artist_id),
      FOREIGN KEY(cd_fk) REFERENCES cd(cd_id)
    );

    CREATE INDEX artist_cd_idx_artist_fk ON artist_cd (artist_fk);
    CREATE INDEX artist_cd_idx_cd_fk ON artist_cd (cd_fk);

    COMMIT;

Feel free to add additional SQL style comments, in order to help anyone down the road understand what you are doing!

Now we need to populate that with existing data. We will make a separate upgrade step for that:

    touch share/migrations/SQLite/upgrade/2-3/002-populate_artist_cd.sql

Open "share/migrations/SQLite/upgrade/2-3/002-populate_artist_cd.sql" and here's the SQL to enter:

    ;
    BEGIN;

    INSERT INTO artist_cd(artist_fk,cd_fk) select artist_fk,cd_id FROM cd;

    COMMIT;

Luckily everything we need exists in the current "cd" table, so this is a straightup insert. In this case I didn't use a Perl deploy run script since I felt the performance benefit of a native SQL approach outweighed the advantage of database portability. If I ever needed to make this work on say MySQL or Pg, I'd need to rewrite it, and there's not a lot of SQL so I am willing to take that risk.

Lastly, we need to alter the "cd" table to get rid of the now unneeded relationship:

    touch share/migrations/SQLite/upgrade/2-3/003-alter_cd.sql

Then open that in your text editor, and lets bring over the last part from "001-auto.sql":

    BEGIN;

    CREATE TEMPORARY TABLE cd_temp_alter (
      cd_id INTEGER PRIMARY KEY NOT NULL,
      title varchar(96) NOT NULL
    );

    INSERT INTO cd_temp_alter SELECT cd_id, title FROM cd;

    DROP TABLE cd;

    CREATE TABLE cd (
      cd_id INTEGER PRIMARY KEY NOT NULL,
      title varchar(96) NOT NULL
    );

    INSERT INTO cd SELECT cd_id, title FROM cd_temp_alter;

    DROP TABLE cd_temp_alter;

    COMMIT;

Again, since SQLite doesn't have any DDL alter to remove FK's we need to roundtrip the data via a temporary table. If you had a million+ rows you might worry about this approach :) Again, I will accept this DDL change for the scope of our limited requirement.

So, that's all the changes. Like last time, remember to remove the suggested upgrade script:

    rm share/migrations/SQLite/upgrade/2-3/001-auto.sql

And you'd probably wish to commit these files now if you are using an source control system (and if you are not, prepare for pain!)

Next step will be to perform the upgrade.

Requirements Change! Mistakes Happen!

This really isn't a problem at all. Its actually very easy to step back and redo your version. You could handle this in two ways. Either you've been very good and making sure you made good downgrades (we didn't :) ) or you can force install the database to an arbitrary older version and start again with the "prepare" command. We will take this second option for this tutorial.

So for the purposes of our tutorial, lets say that suddenly we realize our design for the "country" table is terrible wrong. We've been putting real country names in the table, and in English, but now we want to internationalize our site. That means we should avoid English words in our seed data, and instead use normalized codes that our UI layer can use and leverage existing internationalization and localization tools against. So we need to change that country table, and do so in a way to make sure we keep our existing country information correct. Lastly, we want to add a few new countries to the list as well as one more artist to the system. Thats a bunch of changes, so lets get to it!

Changes to be made:

    1 - Change Country table to use codes not English names
    2 - Update the table data to match above
    3 - Add new countries to list
    4 - Add one additional artist

Let's start by altering "lib/MusicBase/Schema/Result/Country.pm"

    package MusicBase::Schema::Result::Country;

    use strict;
    use warnings;
    use base 'DBIx::Class::Core';

    __PACKAGE__->table('country');
    __PACKAGE__->add_columns(
      'country_id' => {
        data_type => 'integer',
      },
      'code' => {
        data_type => 'char',
        size => '3',
      });

    __PACKAGE__->set_primary_key('country_id');
    __PACKAGE__->add_unique_constraint(['code']);
    __PACKAGE__->has_many(
      'artist_rs' => "MusicBase::Schema::Result::Artist",
      {'foreign.country_fk'=>'self.country_id'});

    1;

So the change here is we drop the 'name' column, and replace it with a 'code' column, that is fixed as a 3 character datatype. We decided that we'd use the standard international 3 digit codes for countries, that's something we can use to wrap internationalization UI around. We also change the unique constraint that used to be on 'name' to 'code'. Everything else stays the same.

There's no other database changes, the rest will be handled in the migration so let's set our database back to version 2 (so that we have something to diff against and also migrate data from).

    $ dbic-migration -Ilib drop_tables
    Dropping table country
    Dropping table cd
    Dropping table artist_cd
    Dropping table track
    Dropping table artist
    Dropping table dbix_class_deploymenthandler_versions

    $ dbic-migration -Ilib install --to_version 2
    $ dbic-migration -Ilib populate
    Reading configurations from .../MusicBase/share/fixtures/2/conf
    Restored set all_tables to database

When the tables are small like this, you can get away with just dropping them and rebuilding everything from the ground up. Once the tables are large you will probably need to actually write correct downgrades, since that would be much more efficient.

Now we are ready to prepare the version (again):

    dbic-migration -Ilib prepare --force_overwrite

We need to use the "force_overwrite" flag to tell DBIx::Class::DeploymentHandler that it is ok to overwrite the generated files. Since you've been making all your customizations in new files we don't have to worry about accidentally blowing away anything important. You'd expect some output like so:

    Overwriting existing DDL-YML file - ...
    Overwriting existing DDL file - ...
    Overwriting existing DDL-YML file - ...
    Overwriting existing DDL file - ...
    Your Database version must be lower than than your schema version
    in order to prepare upgrades / downgrades
    Copying Fixture Confs from .../MusicBase/share/fixtures/2/conf to 
      .../MusicBase/share/fixtures/3/conf

Output above has been abbreviated a bit to highlight the important information. Don't worry about that "Copying Fixture Confs from ..." overwriting any of your custom changes, if there is a file in the target directory matching we just skip the copy (we always assume if the file is there that you may have made some changes you'd rather not lose).

Now, let's look at the new "share/migrations/SQLite/upgrade/2-3/001-auto.sql"

    CREATE TEMPORARY TABLE country_temp_alter (
      country_id INTEGER PRIMARY KEY NOT NULL,
      code char(3) NOT NULL
    );

    INSERT INTO country_temp_alter SELECT country_id, code FROM country;

    DROP TABLE country;

    CREATE TABLE country (
      country_id INTEGER PRIMARY KEY NOT NULL,
      code char(3) NOT NULL
    );

    CREATE UNIQUE INDEX country_code02 ON country (code);

    INSERT INTO country SELECT country_id, code FROM country_temp_alter;

    DROP TABLE country_temp_alter;

Again, to be brief I've only included above the new statements related to our changes to the "country" table. This actually seems pretty good. Lets break that out into a separate file, and add some statements to move data from the old name to the new code columns:

    touch share/migrations/SQLite/upgrade/2-3/004-alter_country.sql

And open that new file in your editor, add the following:

    BEGIN;

    CREATE TEMPORARY TABLE country_temp_alter (
      country_id INTEGER PRIMARY KEY NOT NULL,
      code char(3) NOT NULL
    );

    -- Match to current data
    INSERT INTO country_temp_alter SELECT country_id, 'can' FROM country where name='Canada';
    INSERT INTO country_temp_alter SELECT country_id, 'usa' FROM country where name='USA';
    INSERT INTO country_temp_alter SELECT country_id, 'mex' FROM country where name='Mexico';
    -- End Match

    ;
    DROP TABLE country;

    CREATE TABLE country (
      country_id INTEGER PRIMARY KEY NOT NULL,
      code char(3) NOT NULL
    );

    CREATE UNIQUE INDEX country_code02 ON country (code);

    INSERT INTO country SELECT country_id, code FROM country_temp_alter;

    DROP TABLE country_temp_alter;

    COMMIT;

So I basically just took that change and swapped:

    INSERT INTO country_temp_alter SELECT country_id, code FROM country;

with the three separate INSERTs so I could properly map the English country names to the new 3 character country codes. Otherwise I've kept the rest. This again is a good example of how your diff should both change the database and alter your data in a consistent manner.

Don't forget to delete the "001-auto.sql" file:

    rm share/migrations/SQLite/upgrade/2-3/001-auto.sql

In order to complete our new requirements, lets create some Perl run files to add some new country codes, and one new Artist:

    touch share/migrations/SQLite/upgrade/2-3/005-new_countries.pl
    touch share/migrations/SQLite/upgrade/2-3/006-new_artist.pl

Open "share/migrations/SQLite/upgrade/2-3/005-new_countries.pl" in your editor and add the following:

    use strict;
    use warnings;
    use DBIx::Class::Migration::RunScript;

    migrate {
      shift->schema
        ->resultset('Country')
        ->populate([
          ['code'],
          ['bel'],
          ['deu'],
          ['fra'],
      ]);
    }

Finally open "share/migrations/SQLite/upgrade/2-3/006-new_artist.pl" in your editor and add the following:

    use strict;
    use warnings;
    use DBIx::Class::Migration::RunScript;

    migrate {
      shift->schema
        ->resultset('Artist')
        ->create({
          name => 'JoJo',
          country_fk => {code=>'usa'},
          artist_cds => [
            { cd_fk => {
                title=>'My Cool New Album'}
            }
          ],
        });
    }

You might notice that the relationship names in "006-new_artist.pl" don't exactly match those in our schema. As I mentioned before, this is because the $schema that is passed as the first (and only) argument to your anonymous subroutinues is NOT the schema that comes from MusicBase::Schema but instead it is generated directly from the database using DBIx::Class::Schema::Loader This is because your schema is going to change a lot, we can't rely on it always being backwardly compatible with every version of the database.

If you every get confused about what the auto generated schema looks like, you can always use the "make_schema" command:

    ## example command, don't need to run as part of the tutorial
    dbic-migration -Ilib make_schema

And that will dump the current database version generated schema to "share/dumped_db". You can also set a debugging %ENV variable which will dump to STDOUT the generated classes:

    ## example command, don't need to run as part of the tutorial
    export DBIC_MIGRATION_DEBUG=1

Everything is ready to go. Lets run the upgrade:

    dbic-migration -Ilib upgrade

As before, let's peek inside the database for a quick sanity check:

    $sqlite3 share/musicbase-schema.db
    SQLite version 3.7.5
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"

    sqlite> .tables
    artist                       country                              
    artist_cd                    dbix_class_deploymenthandler_versions
    cd                           track

    sqlite> select * from country;
    1|can
    2|mex
    3|usa
    4|bel
    5|deu
    6|fra
    sqlite> .q

Of course, we really should have some test cases! But first, don't forget to dump your fixtures:

    dbic-migration -Ilib dump_all_sets