Tyler MacDonald > DBIx-Migration-Directories-0.11 > DBIx::Migration::Directories

Download:
DBIx-Migration-Directories-0.11.tar.gz

Dependencies

Annotate this POD

CPAN RT

Open  0
View/Report Bugs
Module Version: 0.11   Source   Latest Release: DBIx-Migration-Directories-0.12

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:

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.

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.

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 or want a specific DBIx::Migration::Directories schema version (but why would you want that?)

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 ^

TODO ^

AUTHOR ^

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

LICENSE ^

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

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!

SEE ALSO ^

DBIx::Migration::Directories::Build, DBIx::Migration::Directories::Base, DBD::Pg, DBD::mysql

syntax highlighting: