NAME

DBIx::Migration::Directories - Install/remove/upgrade/downgrade SQL schemas

SYNOPSIS

use DBIx::Migration::Directories;
use DBI;

my $dbh = DBI->connect('db:something:some=where', 'user', 'pass');

my $m = DBIx::Migration::Directories->new(
   base                    => '/path/where/schemas/are',
   schema                  => 'MyApp',
   desired_version_from    => 'MyApp::DataPackage',
   dbh                     => $dbh
);

$m->migrate
    or die "Installing database failed!";

DESCRIPTION

DBIx::Migration::Directories provides you with a framework for managing database schemas easily. You create a directory to hold your schema, then in that directory create sub-directories containing the SQL code to install, remove, upgrade, or downgrade your schema. The schema layout is described in more detail in the "DIRECTORY LAYOUT" section below.

When asked to install, upgrade, or downgrade a database schema, DBIx::Migration::Directories will look at these directories and attempt to find the shortest path between two schema versions. It will then run the entire upgrade code in one transaction, rolling back if the upgrade fails.

Multiple database schemas can be managed within one database. DBIx::Migration::Directories currently requires two tables to exist in your database to track schema version numbers and upgrade/downgrade history. These tables are called "migration_schema_version" and "migration_schema_log", and are also manageable using DBIx::Migration::Directories.

END USERS

Take a look at the migrate-database-schema documentation, and the documentation that came with the package whose schema you wish to use.

DEVELOPERS

Read on...

FEATURES

The design goal of DBIx::Migration::Directories is to relieve the developer and end-users' burden in building, supporting, and using packages that require a database schema (or schemas) to function. It provides a convention for developers wishing to build such a package, and an easy-to-use commandline tool for end-users to manage their databases. Here are some of it's life-easing features:

Works with Postgres, MySQL, and SQLite2
Easily extensible for other database engines

It's easy to add support for another low-level database, by adding a new migration schema and possibly a DBIx::Migration::Directories subclass to deal with driver-specific quirks
Install, upgrade, remove, *or* downgrade database schemas

Given the appropriate database schema directory layout, DBIx::Migration::Directories can migrate schemas in both directions, from completely uninstalled to the latest and greatest version or anything in-between.
Easy binding of schema versions to perl modules

Simply running "migrate-database-system My::Perl::Package" will attempt to load My::Perl::Package, and use it's $VERSION as the desired version of the My-Perl-Package database schema
"_common" and "_generic" schemas avoid duplication of effort

When you are supporting multiple database engines in your schema, you can place the SQL that's common to all of them in the "_common" directory, or even put it all there and only put small differences in the engine- specific schema directories.

When your one schema is expected to work on all SQL engines (hah, good luck!), you can place it in the "_generic" schema.
Test framework that keeps your databases clean

The DBIx::Migration::Directories::Test test framework tries it's hardest to make sure your database tests are handled cleanly; your schema is installed when the test starts, and uninstalled when the test finishes, even if your tests die.
Module::Build subclass makes it easy to install schema files

The DBIx::Migration::Directories::Build package is a Module::Build subclass. When used by your package, your database schemas will be installed in the same way as DBIx::Migration::Directories's were, keeping everything nice and organized.

USAGE

There are only a few steps to go through to get your perl/DBI package ready to use DBIx::Migration::Directories to manage it's database schemas.

Set up your build environment

DBIx::Migration::Directories uses the Module::Build package to build and install itself, and packages that use it should, too. The old ExtUtils::MakeMakemaker method of installing perl packages just wasn't flexible enough.

Once you have your package set up to use Module::Build, it is simple to make it use DBIx::Migration::Directories as well. A subclass of Module::Build is provide that should help put your schemas in the correct location for migration. See DBIx::Migration::Directories::Build for more information.
Set up your migration directory tree

The migration directory tree describe above needs to go into a specific directory in your build if you want it to be useable, and be installed when your perl package is. See "DIRECTORY LAYOUT" below, and DBIx::Migration::Directories::Build.
Have your package call DBIx::Migration::Directories to install/update it's schema

How and when you do this depends on your package. If you are writing an apache module, you may want to do it when the apache module is first used by the server. If you are writing an application, you might want to do it during server startup. If you are writing some other helper module, you may want to skip doing it yourself, and just provide a method or documentation on how the end-user can do it themselves.

No matter where you do it, the best way to install/update the schema is usually to create a new DBIx::Migration::Directories object, then call the "full_migrate" method on it, detailed below. In some circumstances, this may not be sufficient, in which case there are lower-level methods you can poke around with. :-) See "METHODS" below.

DIRECTORY LAYOUT

In your schema's directory (see DBIx::Migration::Directories::Build to understand where that should live), you should create a subdirectory for each DBD driver you will support. This directory should be named the same as what the driver calls itself; if you are using DBD::Pg, you would want to name your schema directory "Pg", DBD::mysql users would call it "mysql", etc. There are a few exceptions, see "special schemas" below.

Inside that directory, create a directory for the initial version of your schema. (Tip: it's easier to manage if your schema version numbers match up with your package's $VERSION numbers.) For now, let's say this directory ends up being "0.01".

Inside that directory, place your database schema. When the schema install/upgrade occurs, these files will be executed in order, sorted by their names. This makes it easy to split up your schema files by table, and still have them installed in the correct order to satisfy FOREIGN KEYs or other constraints. A typical schema directory might look something like this:

100_mystore_users.sql
120_mystore_user_flags.sql
140_mystore_user_log.sql
200_mystore_products.sql
220_mystore_product_comments.sql

When you have multiple versions of your schema, you will want to create a directory for each version, and place every line of SQL needed to create them in those directories.

Next comes migration.

When asked to migrate a schema, DBIx::Migration::Directories first checks if we already have an older (or newer) version installed, and if so, what version that is. It then attempts to find a path between the currently installed schema and the one you want.

These paths are defined by schema directories named with two version numbers; first the version number you're coming from, followed by a dash, then the version number you're going to. So the directory "0.01-0.02" would define the SQL neccessary upgrade a version 0.01 schema to 0.02, and the directory "0.02-0.01" would define the SQL needed to downgrade from version 0.02 to 0.01. (This would presumedly drop any new tables/columns, remove any new static data, etc.)

For example, if you had 4 versions of your database schema, your schema directory may look as follows:

0.01
0.01-0.00
0.01-0.02
0.01-0.04
0.02
0.02-0.00
0.02-0.01
0.02-0.03
0.03
0.03-0.02
0.03-0.04
0.04
0.04-0.03
0.04-0.00

In most cases, people won't want/need to downgrade, so whether or not you support that in your schema is up to you. However, there is one common downgrade case you may want to consider: un-installing your package. If you want people to be able to do this easily, it's best to provide a way to migrate from each version of your schema to version 0 (eg; directories called 0.02-0 or 0.02-0.00, or a path from 0.02-0.01, then from 0.01-0.00). If you don't provide these schemas, it will be a lot more difficult for an end user to completely remove your package. If you do provide these schemas, completely removing your schema from the system is as easy as calling the delete_schema() method, described below.

Special Schemas

There are two "driver" directories that may contain special schemas:

_common

If some of your SQL is shared across multiple drivers, you can place it here and it will be picked up whenever the same file does not exist in your driver-specific schema. For example, if you had the files:

Pg/0.01/100_create_table.sql
Pg/0.01/110_update_table.sql
mysql/0.01/100_create_table.sql
mysql/0.01/105_insert_rows.sql
mysql/0.01/110_update_table.sql
_common/0.01/105_insert_rows.sql
_common/0.01/120_create_another_table.sql

Then version 0.01 of your schema would be this when used with postgresql:

Pg/0.01/100_create_table.sql
_common/0.01/105_insert_rows.sql
Pg/0.01/110_update_table.sql
_common/0.01/120_create_another_table.sql

But this when running under mysql:

mysql/0.01/100_create_table.sql
mysql/0.01/105_insert_rows.sql
mysql/0.01/110_update_table.sql
_common/0.01/120_create_another_table.sql

_generic

If there is no directory for the DBD driver in use, but there is a _generic directory, that directory will be used for the schema instead. This is a good place to put a schema that is so simple that it should work on virtually any SQL engine.

Important note about Schema Files

DBIx::Migration::Directories sends the contents of the schema's files to the database engine as a sequence of SQL commands. We consider an "SQL command" a sequence of text, delimited by a semicolon (;) at the end of a line.

If you need to supply a string with a semicolon followed by a newline to your schema, you can do something like this:

INSERT INTO foo VALUES (''bar;''||''
baz;'));

If you need to create a trigger with well formatted SQL statements in it, it's more readable to put an empty comment ( --) after the line that ends with a semicolon:

CREATE OR REPLACE FUNCTION lock_archive () RETURNS TRIGGER LANGUAGE PLPGSQL AS
'BEGIN
  IF OLD.archived THEN
    RAISE EXCEPTION ''This record has been archived.''; --
  END IF; --
    RETURN NEW; --
 END;';

METHODS

Constructor

new()

Creates a new DBIx::Migration::Directories object ready to manage your database schema. The following options are supported:

dbh
schema
desired_version
desired_version_from
driver
base
dir

Typically, you will only need to supply the "dbh" and "schema" parameters, and one of "desired_version" or "desired_version_from".

dbh: Required. The DBIx::Transaction database handle to use to manage the schema. This handle should already be connected to the database that you wish to manage.
schema: The name of the schema you wish to manage. Either this parameter, or the desired_version_from parameter is required. If this option is not specified, desired_version_from is used instead, with all double-colons (::) converted to dashes (-), as is the perl way.
desired_version: The schema version number that you wish to migrate to. Schema versions may be an integer or a decimal, but may not be "double-decimals" (eg; 0.0001 is allowed, but 0.0.1 is not.)
desired_version_from: If desired_version is not specified, DBIx::Migration::Directories will attempt to load the package named in desired_version_from. If successful, the desired version will be set to the value of that package's $SCHEMA_VERSION global variable, or it's $VERSION global variable if $SCHEMA_VERSION was not specified. This can make it easier for your package to specify what version of the schema it's API supports.
driver: The name of the DBD driver we are using. You normally don't want to specify this option; DBIx::Migration::Directories will automatically pull the driver name out of the database handle you pass along.
base: The location on your filesystem where all database schemas are stored. This option is typically set by the user when the DBIx::Migration::Directories package is installed. If you are installing your schemas somewhere else (or not installing them at all - however this is not a suggested practice), you may wish to set this option.
dir: The full path to your database schema, including the schema and driver name, but not the version number. The default for this option is generated from the base, schema, and driver options. It may be useful to override this option during unit testing, when your schema files have not yet been installed.

High-Level Methods

These are the methods that are most commonly used to manage schemas with DBIx::Migration::Directories. Most of these methods will return a true value for success, a false value for failure, and will throw a die() exception for serious failure (eg; no migration path, database handle issues, etc.)

full_migrate(%args)

This method first checks to see if DBIx::Migration::Directories's schema is installed and up-to-date, then does the same migration with your schema. Since you need the DBIx::Migration::Directories schema in order to revision your schema, it is usually best to use this method to install/upgrade your schema.

If you specify any arguments, they are passed onto the DBIx::Migration::Directories constructor that is used to check on it's own schemas. You won't normally need to do this, unless you are storing the schemas in a funny location or want a specific DBIx::Migration::Directories schema version (but why would you want that?)

full_delete_schema(%args)

This method attempts to remove your database schema. If that is successful, it then checks to see if your database contains any other schemas that use DBIx::Migration::Directories. If there aren't any, the DBIx-Migration-Directories schema tables are removed as well.

If you specify any arguemnts, they are passed onto the DBIx::Migration::Directories constructor that is used to check on it's own schemas. You won't normally need to do this, unless you are storing the schemas in a funny location.

migrate()

Attempts to migrate your schema to the desired version, without doing the check on DBIx::Migration::Directories's schema that is done by full_migrate().

migrate_to($version)

Attempts to migrate your schema to a specific version, other than the one supplied in desired_version.

delete_schema()

Attempts to migrate your schema to version "0", which you should set up to complete remove it from the database. If that is successful, then the schema's records are removed from DBIx::Migration::Directories's tables.

get_current_version()

Looks at DBIx::Migration::Directories's tables to see what the currently installed schema's version is. If the schema is installed, the current version will be returned, otherwise this method will return undef.

Mid-Level Methods

These methods are used by the high-level methods above, and you usually won't have to use them yourself. They provide more fine-grain control over your schema and give you more detailed information about it.

migration_schema(%args)

Returns a DBIx::Migration::Directories object suitable for manipulating the DBIx-Migration-Directories schema itself.

It is the equivalent to calling:

$schema->new(
    dbh       => $schema->{dbh},
    schema    =>  'DBIx-Migration-Directories',
    %args
);

migrate_migration(%args)

This method simply attempts to migrate the DBIx::Migration::Directories schema using the same database handle you passed it. It is used by the full_migrate() method described above.

It is the equivalent to calling:

$schema->migration_schema(%args)->migrate();

delete_migration(%args)

This method simply attempts to remove the DBIx::Migration::Directories schema using the same database handle you passed it. It is used by the full_delete_schema() method described above.

It is the equivalent to calling:

$schema->migration_schema(%args)->delete_schema();

migrate_from_to($from, $to)

Attempt to migrate from a particular version to the specified version. Using this method on it's own can be dangerous unless you are absolutely sure of which version you currently have. It's far better to use migrate_to() and let the module figure out where you're currently at.

detect_desired_version()

Returns the highest available version we can migrate to from our current version. Returns the current version of our schema if there are no forwards migrations available, or returns undef if we have a version of the schema that is not in the schema directory.

set_desired_version()

Calls detect_desired_version(). If a valid version number found, this object's desired_version property is set and the version number is returned. If not, an exception is raised.

This method is called when a DBIx::Migration::Directories object is initialized if you do not supply desired_version or desired_version_from to new().

Low-Level Methods

All of these methods are used by the Mid-Level and High-Level methods above. The only one I expect you to find remotely useful on it's own is the "version_update_sql" method, but here's the what they all do:

version_update_sql($from, $to)

Returns the SQL required to log that the database was migrated from version $from to version $to, as an array of SQL statements.

The first statement will be either an UPDATE or an INSERT, depending on if your schema already has an entry in the migration tables. This determination is made by the get_current_version() method above, so you will want to call that first to ensure these queries are accurate.

It might be useful to use this method if your package had a schema before, and you are updating it to include migration support with DBIx::Migration::Directories. In those cases, a schema probably already exists in the database, and you'll have to "fake" the migration log information in.

Note that this method only returns the SQL neccessary to update the logs, it doesn't execute it itself.

delete_schema_record()

Remove any knowledge about your schema from the migration log. This method is called by the delete_schema() method once your schema has been successfully removed, and it probably isn't safe to call it on it's own unless you are sure your schema is gone.

refresh()

Look at the schema directory and build a table of what's inside it. This is called automatically by new(), so you shouldn't need to call it yourself unless you change that directory's contents while the migration object exists. (And why would you do that???)

migration_path($from, $to)

Attempt to build a path between schema versions, assuming your current schema version is $from, and you want to get to $to. If a path is found, it is returned as an array of subdirectory names. If a path is not found, an exception is raised.

dir_sql($subdir)

Given a directory relative to your schema's directory, reads all of the files inside of it, and returns an array of the SQL statements they contain. Raises an exception if the directory or any of it's non-hidden files cannot be read.

The SQL statements are split based on the presence of a semicolon (;) at the end of a line, or on a line by itself. That means that you can not change SQL statements in mid-line.

dir_migration_sql($dir)

Returns the SQL returned from the dir_sql() method for the specified directory, plus the SQL required to update the migration schema to log this upgrade, as returned by version_update_sql().

migration_path_sql(@path)

For each directory in @path, returns the result of dir_migration_sql(). The result is a full set of SQL queries neccessary to follow a database migration path.

Other Methods

The other methods that DBIx::Migration::Directories uses to handle database migrations are supplied by the DBIx::Migration::Directories::Base base class. See it's documentation for a description of these.

BUGS

MySQL driver

MySQL's error and transaction handling are different than other database drivers. Some workarounds have been applied and everything seems to work, but there could be other problems as well. If you find one, let me know!

TODO

Handle fancier version numbers
Add migration schemas for a wider variety of DBMS.
Bubble some build-time stuff up as enhancements to Module::Build
Support inter-schema dependancies

(eg; "My-Image-Gallery" depends on "Some-User-Management-Schema", etc...)

AUTHOR

Tyler "Crackerjack" MacDonald <japh@crackerjack.net>

LICENSE

This is free software; You may distribute it under the same terms as perl itself.

THANKS

Mischa Sandberg <mischa.sandberg@telus.net>: For teaching me a lot about PostgreSQL, and SQL database issues in general. Without him I wouldn't have even known I needed a module like this. :-)
Ken Williams <kwilliams@cpan.org>: For the kick-ass Module::Build framework that makes a package like this sane to maintain.
Paul Johnson <pjcj@cpan.org>: For Devel::Cover, which makes it easy for me to know when I can say "It really works!"
Sedara MacDonald: My daughter, for bringing me cookies while I coded this!

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

END USERS

DEVELOPERS

FEATURES

USAGE

DIRECTORY LAYOUT

Special Schemas

Important note about Schema Files

METHODS

Constructor

High-Level Methods

Mid-Level Methods

Low-Level Methods

Other Methods

BUGS

TODO

AUTHOR

LICENSE

THANKS

SEE ALSO

Module Install Instructions