NAME

DBIx::Class::Migration::Tutorial::AddMySQL - Add MySQL migration

GOAL

So far in the tutorial we've been building migrations for SQLite. This is a good database for initial prototyping and local development, but at some point you'll need a more production oriented database. By the end of this tutorial you should have the basics of adding support for MySQL to your migrations. We'll add MySQL migrations for version 3.

Add Version 3 Migrations for Mysql

Since we build out migrations from the current Schema, you can only prepare files for version 3 (at this point in the tutorial you should be at version 3).

Let's check our status

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

If you've been following along with the tutorial, you should see something like the above. Now, lets prepare migration files for MySQL:

    $ dbic-migration -Ilib prepare --database MySQL --force_overwrite

    Overwriting existing DDL-YML file - .../share/migrations/_source/deploy/3/001-auto.yml
    Overwriting existing DDL file - .../share/migrations/MySQL/deploy/3/001-auto.sql
    Overwriting existing DDL-YML file - ../share/migrations/_source/deploy/3/001-auto-__VERSION.yml
    Overwriting existing DDL file - .../share/migrations/MySQL/deploy/3/001-auto-__VERSION.sql

    Your Database version must be lower than than your schema version
      in order to prepare upgrades / downgrades
    Copying Fixture Confs from .../share/fixtures/2/conf to .../share/fixtures/3/conf

You need to "force_overwrite" since DBIx::Class::DeploymentHandler needs to update some system managed files (nothing you've customized). Additionally, don't worry about the message, "Copying Fixture Confs from..." since we won't ever overwrite your customized fixtures.

You'll also notice that we can't build version 2 to 3 upgrades for MySQL, since we don't have a version 2 of the database for MySQL. We'll skip working on upgrade files for MySQL since they won't essentially be at all different from the work you've don't already of SQLite. If you needed to rebuild all the versions, you actually can install down to Version 1 and build each step for MySQL (an exercise I'll leave to your practice session!)

Let's see what's been added to "share":

    /share
      /migrations
      /MySQL
        /deploy
          /3
            001-auto-_VERSION.sql
            001-auto.sql

So this should start to look familiar to you. Basically we just have a full DDL to deploy our MySQL database.

How to install the version 3 MySQL

So far you've only done "install" and "upgrade" to the default SQlite database (under "share"). If you want to run those commands against MySQL, obviously you'll need a running MySQL instance. We can build a MySQL sandbox for you in the "target_dir", similarly to how we did for Sqlite. To do this you need to set the "sandbox_class" flag. Also, you should add Test::mysqld to your "dist.ini" file, and get that installed (you'll need to install MySQL on your development computer, but it doesn't need to be running, just 'findable' in your $PATH:

"dist.ini"

    name    = DBIx-Class-Migration
    author  = John Napiorkowski <jjnapiork@cpan.org>
    license = Perl_5
    copyright_holder = John Napiorkowski
    copyright_year   = 2012
    abstract = Tutorial Application for DBIx-Class-Migration

    version = 0.001

    [@Basic]
    [Prereqs]
    DBIx::Class = 0
    DBIx::Class::Migration = 0
    DBD::mysql = 0
    Test::mysqld = 0


    [Prereqs / TestRequires]
    Test::Most = 0
    Test::DBIx::Class = 0

and install:

     dzil listdeps | cpanm

If you have any trouble, you'll need to resolve that before moving on with the tutorial. In my experience, DBD::mysql installs easily if you make sure $PATH can find the Mysql "bin" area ("mysql_config", etc).

Assuming you get MySQL properly installed, lets build a sandbox:

    $dbic-migration -Ilib --sb MySQLSandbox status
    Schema is 3
    Database is not currently installed

Just like with the "schema_class" flag, you can set an %ENV variable to set your sandbox type for the shell:

    ## example
    export DBIC_MIGRATION_SANDBOX_CLASS=MySQLSandbox

We won't do this, since we'd like to continue controlling if we are using the default sqlite sandbox or our new mysql sandbox.

Once you run that command you'll see a new directory in your "target_dir", which in this tutorial is under "share":

    /share
        /fixtures
        /migrations
        /musicbase-schema
        musicbase-schema.db

that new "share/musicbase-schema" directory contains the actual mysql sandbox files. You should probably set your repository to ignore this directory, since it is unlikely you want those shared with other developers.

BTW, if you peek inside "share/musicbase-schema/bin" you'll spot a couple of helper scripts:

    /bin
      start
      stop
      use

"start" and "stop" starts and stops the sandbox (by default we stop the sandbox when your migration command exits). You'll use this if you want to "use" the sandbox (opens a mysql shell) or if you want to let you application use the sandbox (for example later on if you are using Catalyst you'd want to start the sandbox so that you can expose to to your web application).

You'd install the database and fixtures like so:

    dbic-migration -Ilib --sb MySQLSandbox install
    dbic-migration -Ilib --sb MySQLSandbox populate

That would give you:

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

Now you database is ready to use!

    $ dbic-migration -Ilib --sb MySQLSandbox status
    Schema is 3
    Database is 3

SUMMARY

Like the section on Testing, this one was pretty short. At this point you should have a good idea of the effort it would require to use your migrations against databases other than the default SQlite.

NEXT STEPS

Proceed to DBIx::Class::Migration::Tutorial::Catalyst

AUTHOR

See DBIx::Class::Migration for author information

COPYRIGHT & LICENSE

See DBIx::Class::Migration for copyright and license information