From ClassDBI

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
);