Schema generation

= Automatically generating schema from Class::DBI =

I've written a set of functions to automatically generate schemas from my Class::DBI objects, as well as wrapped the Class::DBI code into something that removes much of the boilerplate of defining the subclasses for each table. This ensures that the database schema stays in sync with the Class::DBI schema and that the database structure is only documented in one place.

To do:
 * Clean up Schema.pm code for submission to CPAN
 * Add more relationship types
 * Provide callbacks for triggers, etc

Here is a trivial sample showing some of the functions:

package BankDB; use strict; use warnings; use base 'Class::DBI'; use Schema;

__PACKAGE__->connection(       'dbi:SQLite2:dbname=bank.db',        ,         );

__PACKAGE__->create_table( accounts => 'BankDB::Account',       id              => 'INT SERIAL',        name            => 'VARCHAR(80)',        liability       => 'INT',               # asset=0, liability=1        -may_have       => parent => 'BankDB::Account',        -has_many       => ledgers => 'BankDB::Ledger',        -unique         => 'name, parent', );

__PACKAGE__->create_table( invoices => 'BankDB::Invoice',       id              => 'INT SERIAL',        date            => 'DATETIME',        memo            => 'TEXT',        due_date        => 'DATE',        paid_date       => 'DATE', );

__PACKAGE__->create_table( transactions => 'BankDB::Transaction',       id              => 'INT SERIAL',        memo            => 'TEXT',        date            => 'DATETIME', );

__PACKAGE__->create_table( ledger => 'BankDB::Ledger',       id              => 'INT SERIAL',        amount          => 'CURRENCY',          reimburse       => 'INT',       # 0 == no, 1 == unpaid, 2 == paid        -must_have      => tx => 'BankDB::Transaction',        -must_have      => account => 'BankDB::Account',        -may_have       => invoice => 'BankDB::Invoice', );

Calling BankDB->schema returns:

DROP TABLE accounts; CREATE TABLE accounts (       id INT SERIAL,        name VARCHAR(80),        liability INT,        parent INT REFERENCES accounts,        UNIQUE( name, parent ) );

DROP TABLE invoices; CREATE TABLE invoices (       id INT SERIAL,        date DATETIME,        memo TEXT,        due_date DATE,        paid_date DATE );

DROP TABLE transactions; CREATE TABLE transactions (       id INT SERIAL,        memo TEXT,        date DATETIME );

DROP TABLE ledger; CREATE TABLE ledger (       id INT SERIAL,        amount CURRENCY,        reimburse INT,        tx INT REFERENCES transactions NOT NULL,        account INT REFERENCES accounts NOT NULL,        invoice INT REFERENCES invoices );