NAME

Class::DBI::DDL - Combined with Class::DBI to create and dispose of tables

SYNOPSIS

  package My::DBI;
  use base 'Class::DBI::DDL';

  # __PACKAGE__->set_db('Main', 'dbi:Pg:dbname=test', 'test', 'test');
  __PACKAGE->set_db('Main', 'dbi:mysql:test', 'test', 'test');

  package My::Folk;

  use base 'My::DBI';

  # Regular Class::DBI definitions...
  __PACKAGE__->table('folks');
  __PACKAGE__->columns(Primary => 'id');
  __PACKAGE__->columns(Essential => qw(first_name last_name age));
  __PACKAGE__->has_many(favorite_colors => 'My::Favorite');

  # DDL methods
  __PACKAGE__->column_definitions([
      [ id         => 'int',  'not null', 'auto_increment' ],
      [ first_name => 'varchar(20)', 'not null' ],
      [ last_name  => 'varchar(20)', 'not null' ],
      [ age        => 'numeric(3)',  'not null' ],
  ]);

  __PACKAGE__->index_definitions([
      [ Unique => qw(last_name first_name) ],
  ]);

  __PACKAGE__->create_table;

  package My::Favorite;

  use base 'My::DBI';

  # Class::DBI definitions...
  __PACKAGE__->table('favorites');
  __PACKAGE__->columns(Primary => 'id');
  __PACKAGE__->columns(Essential => qw(folk color));
  __PACKAGE__->has_a(folk => 'My::Folk');

  # DDL methods
  __PACKAGE__->column_definitions([
      [ id    => 'int',  'not null', 'auto_increment' ],
      [ folk  => 'numeric(5)',  'not null' ],
      [ color => 'varchar(20)', 'not null' ],
  ]);

  __PACKAGE__->index_definitions([
      [ Unique  => qw(folk color) ],
      [ Foreign => 'folk', 'My::Folk', 'id' ],
  ]);

  __PACKAGE__->create_table;

DESCRIPTION

This module is used to added to a Class::DBI class to allow it to automatically generate DDL calls to create a table if it doesn't exist in the database already. It attempts to do so in such a way as to be database independent whenever possible.

Use the typical "Class::DBI" methods to build your class methods. Then, use the "column_definitions" and "index_definitions" methods to define the structure of the table. Finally, call "create_table" and the system will attempt to create the table if the table cannot be found.

DBI DEPENDENCE

The functionality provided by this library attempts to depend on as little that is database or driver specific as possible. However, it does, at this time, require that the DBD driver have a functioning "tables" method for listing tables in the database. Such dependence may later be emulated in the same way "DRIVER DEPENDENT OPERATIONS" is done, if necessary, but it is not at this time.

DRIVER DEPENDENT OPERATIONS

It also has some special support for situations where standard SQL generation will fail for a given database. The primary use of this facility is to make sure that auto-increment fields are properly handled. This system uses the the "auto_increment" property notation used by MySQL to handle this. This system does not work well with the "sequence" method of "Class::DBI".

METHODS

In addition to the method found in Class::DBI, this package defines the following:

column_definitions

  __PACKAGE__->column_definitions($array_reference);

The array reference passed should contain an element for each column given to the "columns" method of "Class::DBI". Each element is an array reference whose first element is the column name. The rest of the elements after the column name are used to define the column. Typically, the column type will be next followed by any flags, such as "NULL", "NOT NULL", "AUTO_INCREMENT", etc. Don't use index constraints here such as "PRIMARY" or "UNIQUE".

index_definitions

  __PACKAGE__->index_definitions($array_reference);

The array reference passed should contain an element for each column index to create in addition to the primary key. Currently, two index types are supported: "UNIQUE" and "FOREIGN". The "UNIQUE" index will create an index that constrains the columns so that there are no duplicates in the given fields. The "FOREIGN" index will create a link between databases and should enforce referential integrity--if the underlying driver supports it.

Each element of the column index is an array reference whose first element is the name of the type of index to use--this name is case-insensitive. Following this are the arguments to that type of index, whose format varies depending upon the index type:

UNIQUE: For a "UNIQUE" index, an array or array reference contain column names follows the "UNIQUE" keyword. The given column names will be used to create the index.
FOREIGN: A "FOREIGN" index takes exactly three arguments. The first and third arguments are column names and the second is the name of a package. The column name arguments may either be a single column name, or an array reference containing multiple column names. In any case, the first and third arguments must have exactly the same number of elements. The package name in the second argument should point to another "Class::DBI" class that has already been defined.

create_table

  __PACKAGE__->create_table;

  # -- OR --

  __PACKAGE__->create_table(sub { ... });

This method does most of the real work of this package. It takes the given "column_definitions" and "index_definitions" and some other "Class::DBI" information to create the table if the table does not already exist in the database.

If the method is passed a code reference, then the given code will be executed if the table is created. The code reference will be called after the table exists. This is so the user may populate the table with a "starter database" if the table needs to have some data in it at creation time.

drop_table

  __PACKAGE->drop_table;

This method undoes the work of "create_table". It does nothing if the table doesn't exist.

HELPER METHODS

The "Class::DBI::DDL" package uses helper methods named "pre_create_table", "post_create_table", "pre_drop_table", and "post_drop_table" to take care of work that is specific to a database driver--specifically setting up auto_increment columns or stripping out unsupported constraints or indexes.

As of this writing, "Class::DBI::DDL" supports "DBD::Pg" and "DBD::mysql" directly, but provides a default that is general enough to work under most other environments. To define a new helper for another database driver, just create a package named "Class::DBI::DDL::Driver", where "Driver" is the name of the database driver name returned by:

  $dbh->{Driver}->{Name}

After this class is installed somewhere in the Perl include path, it will be automatically loaded. If you create such a driver, please send it to me and I will consider its inclusion in the next release.

Here are described the workings of the default helper methods--please let me know if this could be improved to be more general as this is largely untested!

pre_create_table

  Class::DBI::DDL::Driver->pre_create_table($class)

As its first argument (besides the invocant) it is passed the class name of the caller. This method is called before "create_table" processes any of the column or index information.

The default method simply checks for the "auto_increment" property in the column definitions. If found, it drops the "auto_increment" property and adds a trigger that finds the maximum value in the column and adds one to that value and sets the column to the incremented value. Thus, this emulates the auto_increment feature for any database that supports the MAX aggregate function.

post_create_table

  Class::DBI::DDL::Driver->post_create_table($class)

As its argument (besides the invocant) it is passed the class name of the caller. This method is called after "create_table" has created the table and before the start database method is called (if present).

The default method does nothing.

pre_drop_table

  Class::DBI::DDL::Driver->pre_drop_table($class)

As its argument (besides the invocant) it is passed the class name of the caller. This method is called before "drop_table" drops the table.

The default method does nothing.

post_drop_table

  Class::DBI::DDL::Driver->post_drop_table($class)

As its argument (besides the invocant) it is passed the class name of the caller. This method is called after "drop_table" drops the table.

The default method does nothing.

AUTHOR

Andrew Sterling Hanenkamp <sterling@hanenkamp.com>

LICENSE AND COPYRIGHT

This module is free software and is distributed under the same license as Perl itself.