NAME

Pg::DatabaseManager - Manage installation and migration of an application's (Postgres) database

VERSION

version 0.06

SYNOPSIS

use Pg::DatabaseManager;

Pg::DatabaseManager->new_with_options()->update_or_install_db();

or subclass it ...

package MyApp::DatabaseManager;

use Moose;

extends 'Pg::DatabaseManager';

has '+app_name' => ( default => 'MyApp' );

has '+contrib_files' => ( default => [ 'citext.sql' ] );

DESCRIPTION

This class provides an object which can be used to drop, create, and migrate an application's Postgres database.

It uses MooseX::Getopt so that it can be invoked easily from a command-line script, but it is also designed to be usable from another module.

THE VERSION TABLE

In order to perform migrations, your application database must define a table to store the database version. By default, this class expects this table to be named "Version", but you can override this by setting the appropriate attribute.

However, the structure of the table is fixed, and must look like this:

CREATE TABLE "Version" (
    version  INTEGER  PRIMARY KEY
);

This table should never contain more than one row (for obvious reasons).

You must include the version table in your database SQL DDL file.

You must also include a single line which inserts the current database version into this table:

INSERT INTO "Version" (version) VALUES (6);

This class will parse the database SQL file to find this line in order to determine the current database version.

MIGRATIONS

Migrations are defined in their own directory. That directory in turn contains one directory per database version (except version 1). The per-version directories should each contain one or more files. The files will be executed in the order that Perl's sort returns them. You can number them (01-do-x.sql, 02-do-y.sql) to ensure a clear ordering.

The files can either contain SQL or Perl code. Any file ending in ".sql" is assumed to contain SQL (duh) and is executed using the psql utility.

Otherwise, the file should contain Perl code which defines an anonymous subroutine. That subroutine will be called with the Pg::DatabaseManager object as its only argument.

Allowing Perl migration files lets you do things like import contrib SQL files as part of a migration, for example:

{
    use strict;
    use warnings;

    return sub {
        my $manager = shift;
        $manager->import_contrib_file('citext.sql');
    };
}

This is the entire migration file.

This module always dumps the existing database (with data) to a file in the temp directory before running migrations.

Testing Migrations

See the Pg::DatabaseManager::TestMigrations module for a tool which you can use to test your migrations as part of your test suite.

METHODS

This class provides the following methods:

Pg::DatabaseManager->new( ... )

This method accepts the following parameters:

• db_name

  The name of the database that this object will work with. Required.

• app_name

  The name of the application that this database is for. This is only used in informational messages. It defaults to the same value as db_name.

  It cannot be set from the command line.

• db_owner

  The name of the database owner, which will be used when the database is created. Optional.

• db_encoding

  The encoding to use when the database is created. Optional.

• username

• password

• host

• port

  These are connection parameters to be used when dropping, creating, or modifying the database. All parameters are optional.

  Note that the username will be used as the database owner if it is provided but db_owner is not.

• ssl

  If this is true, then connecting to the database server will require an SSL connection.

• sql_file

  The SQL file that contains the DDL to create the database.

  This file must not contain DROP DATABASE or CREATE DATABASE statements. Those actions will be taken care of by this code. Required.

• contrib_files

  This is an array reference of contrib file names like citext.sql or pgxml.sql. These files will be loaded after creating the database, but before running the DDL in the sql_file. Optional.

• version_table

  The name of the table which contains the database version. See "THE VERSION TABLE" for details. Defaults to "Version">

• migrations_dir

  The directory which contains migrations for the database. This is required, but the directory can be empty. However, if the database needs to be migrated from one version to another, it expects to find migrations for every appropriate version in this directory.

• drop

  If this is true, then the database will be dropped and then recreated.

• quiet

  Setting this to true suppresses any output from this module.

$manager->update_or_install_db()

This is the one public "do it" method. It will update or install the database as needed. If the drop parameter was true, then it will drop any existing database first, meaning it will always install a new database from scratch.

This method tests whether it can connect to the database server's template1 database, and dies if it cannot connect.

$manager->import_contrib_file( $file )

Given a contrib file name such as "citext.sql" or "pgxml.sql", this method finds the file and imports it into the database. If it cannot find the named file, it dies.

SUBCLASSING

This module is designed to be subclassed. In particular, it may make sense for a subclass to provide defaults for various attributes. Please see Silki::DatabaseManager in the Silki distribution's inc dir for an example.

In future versions of this module, I plan to document more of the internals as a stable subclassing interface. For now, if you subclass this module, please let me know what parts of the interface you overrode.

BUGS

Please report any bugs or feature requests to bug-pg-databasemanager@rt.cpan.org, or through the web interface at http://rt.cpan.org. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

DONATIONS

If you'd like to thank me for the work I've done on this module, please consider making a "donation" to me via PayPal. I spend a lot of free time creating free software, and would appreciate any support you'd care to offer.

Please note that I am not suggesting that you must do this in order for me to continue working on this particular software. I will continue to do so, inasmuch as I have in the past, for as long as it interests me.

Similarly, a donation made in this way will probably not make me work on this software much more, unless I get so many donations that I can consider working on free software full time, which seems unlikely at best.

To donate, log into PayPal and send money to autarch@urth.org or use the button on this page: http://www.urth.org/~autarch/fs-donation.html

AUTHOR

Dave Rolsky <autarch@urth.org>

COPYRIGHT AND LICENSE

This software is Copyright (c) 2011 by Dave Rolsky.

This is free software, licensed under:

The Artistic License 2.0 (GPL Compatible)