NAME

GOAL

In preparation for this section, you might wish to revisit the documentation for DBIx::Class::DeploymentHandler and in particular DBIx::Class::DeploymentHandler::Manual::Intro

Using the dbic-migration commandline tool

    dbic-migration version --schema_class MusicBase::Schema -Ilib

This should return (something similar to):

    Application version is 0.025

Let's do a quick review. "dbic-migration" is your main gateway to managing your migrations. When using the tool you will give it one command (such as "version" above) and any number of option flags (starting with "--" or "-").

NOTE: The version reported might be different from the one mentioned in the above documentation. If you have a much older (or newer) version, please note this tutorial was written against the one mentioned, and although I will strive for backward compatibility feature sets might change.

When using the tool, you'll need to specify the "schema_class" that you are creating and using migrations for. Typically this will be your subclass of DBIx::Class::Schema, and you'll need to extend the Perl module search path with "I" as above. This is so that "perl" will know about your custom application libraries.

If you are going to be working with one schema for a bit, you can export "DBIC_MIGRATION_SCHEMA_CLASS" into your current shell, that way you don't need to keep retyping it. If your shell is "bash" you can do this with the following command:

    export DBIC_MIGRATION_SCHEMA_CLASS=MusicBase::Schema

Then you can simply do:

    dbic-migration -Ilib version

For the remainder of the tutorial, I will assume your Schema Class has been exported. Remember, you can always specific with the "--schema_class" option flag.

Before we move on, let's see the status of your schema and database:

    dbic-migration -Ilib status

This should return an error similar to the following:

    Failed to find share dir for dist 'MusicBase-Schema' at ....

Why did this happen? In order to use DBIx::Class::Migration you need to tell it where to put the migration files. You'd use the "--target_dir" option flag to do this, but if you don't provide a value, it will automatically assume you have a "share" directory in the home directory of you application and wish to put files there. This is a good, accepted community practice for storing non code data for your project and I recommend you follow it. Let's create the share directory and try again:

    mkdir share
    dbic-migration -Ilib status

You should now get:

    Schema is 1
    Database is not currently installed

Great! Now you have the basics of using the commandline tool!

Prepare migration files

    dbic-migration -Ilib prepare

You should see:

    There is no current database deployed, so I can't prepare upgrades
    or downgrades

NOTE: If you are getting some wild debugging messages, please see DBIx::Class::Migration::FAQ for details.

Since this is the first version, we won't create any upgrade or downgrade migrations. Okay, lets see what we now have:

In your "share" directory you now have the following:

    /share
      /fixtures
        /1
          /conf
            all_tables.json
      /migrations
        /_source  (There's stuff in here, but we won't peek!)
        /SQLite
          /deploy
            /1
              001-auto-_VERSION.sql
              001-auto.sql
      musicbase-schema.db

So let's review. We created a default fixture configuration that just serializes all the database information. This is probably not great for the long term but until you get the hang of creating custom fixture configurations (and for this you need to review DBIx::Class::Fixtures) it will serve. In any case you can take a quick peek to get the idea:

"/share/fixtures/1/conf/all_tables.json"

    {
       "sets" : [
          {
             "quantity" : "all",
             "class" : "Cd"
          },
          {
             "quantity" : "all",
             "class" : "Track"
          },
          {
             "quantity" : "all",
             "class" : "Artist"
          }
       ],
       "might_have" : {
          "fetch" : 0
       },
       "belongs_to" : {
          "fetch" : 0
       },
       "has_many" : {
          "fetch" : 0
       }
    }

DBIx::Class::Fixtures uses JSON for its configuration. In this case you can note that we are just dumping all the rows in all the tables. You will see that each time you prepare a version, we always build a fresh "all_tables.json" for you to use as a default (in other words, don't change this one :) ).

You should also note that the path to your fixtures and your migrations contain the schema version number you have prepared. You'll see later that as you add more schema versions this becomes your primary way of managing all the directories.

Three other files of interest have been created. The first is "001-auto-_VERSION.sql" which is the DDL (data description language) for the default database (SQLite) to create the meta table that DBIx::Class::DeploymentHandler uses to keep track of the version history for your deployments. We also create a full DDL for the tables that make up your application. In this case we have one table for each of the Artist, CD and Track Result classes.

"/share/migrations/SQLite/deploy/1/001-auto.sql"

    BEGIN TRANSACTION;
    --
    -- Table: artist
    --
    CREATE TABLE artist (
      artist_id INTEGER PRIMARY KEY NOT NULL,
      name varchar(96) NOT NULL
    );
    --
    -- Table: cd
    --
    CREATE TABLE cd (
      cd_id INTEGER PRIMARY KEY NOT NULL,
      artist_fk integer NOT NULL,
      title varchar(96) NOT NULL,
      FOREIGN KEY(artist_fk) REFERENCES artist(artist_id)
    );
    CREATE INDEX cd_idx_artist_fk ON cd (artist_fk);
    --
    -- Table: track
    --
    CREATE TABLE track (
      track_id INTEGER PRIMARY KEY NOT NULL,
      cd_fk integer NOT NULL,
      title varchar(96) NOT NULL,
      FOREIGN KEY(cd_fk) REFERENCES cd(cd_id)
    );
    CREATE INDEX track_idx_cd_fk ON track (cd_fk);
    COMMIT

You should review this DDL to make sure it properly reflects your schema.

DBIx::Class::DeploymentHandler will build full DDL for each of the databases you are creating migrations for. We feel this is the best approach since it lets you take maximum advantage of your target database. By default, if you don't specify a database (using the "database" option flag - only needed if you aren't doing the "default" thing; DBIx::Class::Migration will figure out the right thing to do in nearly all circumstances) we build migrations for SQLite, since that is easy to use and test, but you can always build any of the supported databases. For example if you ran the following:

    dbic-migration -Ilib prepare --database MySQL --database SQLite

We'd build migrations for both MySQL and SQLite. We'll try that later on, for now let's stick to SQLite, since that is very low effort and you have plenty to learn already!

There's one more file we've created "musicbase-schema.db" which is an empty SQLite database you can use for testing your migrations or for prototyping.

You've now completed creating your first migration!

Using a source control repository

In "git" you can add or modify ".gitignore" in your project directory:

    share/musicbase-schema/*
    share/musicbase-schema.db

This will ignore both SQLite databases and any Mysql or Postgresql sandboxes you might create (as we will in a later section of the tutorial).

Customizing the Migration

    mkdir share/migrations/_common
    mkdir share/migrations/_common/deploy
    mkdir share/migrations/_common/deploy/1
    touch share/migrations/_common/deploy/1/002-demo.pl

Then open the file "share/migrations/_common/deploy/1/002-demo.pl" in your editor of choice and add the following code:

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

    migrate {

      my $artist_rs = shift
        ->schema->resultset('Artist');

      $artist_rs->create({
        name =>'Michael Jackson',
        cds => [
          { title => 'Thriller', tracks => [
            { title => 'Beat It' },
            { title => 'Billie Jean' }],
          },
          { title => 'Bad', tracks => [
            { title => 'Dirty Diana' },
            { title => 'Smooth Criminal'},
            { title => 'Leave Me Alone' }],
          },
        ]
      });

      $artist_rs->create({
        name =>'Eminem',
        cds => [
          { title => 'The Marshall Mathers LP', tracks => [
            { title => 'Stan' },
            { title => 'The Way I Am' }],
          },
        ]});

    };

If you read the DBIx::Class::DeploymentHandler docs, you know that in addition to running SQL files natively against your database of choice, you can also create Perl run scripts, which is a Perl file that returns an anonymous subroutine (similar to Plack). That subroutine should expect to get one argument from the deployment handler, which is a schema object upon which you can run DBIx::Class commands.

IMPORTANT: The schema that is passed to your subroutine reference is one that we autogenerate using DBIx::Class::Schema::Loader. It is not the same as your application subclass of DBIx::Class::Schema (for us that would be MusicBase::Schema). Since your schema is going to be in flux, we can't rely on it for creating Perl run files. Because of this, the names of the relationships will reflect those that DBIx::Class::Schema::Loader generates as part of its introspection of the database. If you get confused and can't figure out the generated schema, you can always dump it with the "make_schema" command.

Why use Perl instead of SQL? You should use what makes sense to you and what you are comfortable with. I use Perl when I can since I can put that in the "_common" directory and use it for all the database I create migrations for. That saves me a bit of repeating myself, but adds a bit of complexity to understanding.

You should also notice that the file we created starts with '002'. This ensures that it will run after the '001' file (in this case we run "001-auto.sql" first.

Awesome, you now have customized your first migration. Now we can install it!

    dbic-migration -Ilib install

    sqlite3 share/musicbase-schema.db

    SQLite version 3.7.5
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    sqlite>

    .tables

    artist               dbix_class_deploymenthandler_versions
    cd                   track

    select * from artist;

    1|Michael Jackson
    2|Eminem

    dbic-migration -Ilib dump_all_sets

    /share
      /fixtures
        /1
          /all_tables
            _dumper_version
              /artist
                1.fix
                2.fix
              /cd
                (1-3.fix)
              /track
                (1-7.fix)
          /conf
            all_tables.json
      /migrations
        (...)

    dbic-migration -Ilib delete_table_rows

    dbic-migration -Ilib populate

    Reading configurations from .../share/fixtures/1/conf
    Restored set all_tables to database

    sqlite3 share/musicbase-schema.db

    sqlite> select * from track;
    1|3|The Way I Am
    2|3|Stan
    3|1|Billie Jean
    4|2|Leave Me Alone
    5|2|Smooth Criminal
    6|1|Beat It
    7|2|Dirty Diana

SUMMARY

At this point I'd say you have the minimum setup for being able to do real database development.

NEXT STEPS

AUTHOR

COPYRIGHT & LICENSE