NAME
Rose::DBx::Object::Builder - Database Table Schema Generation for Rose::DB::Object
SYNOPSIS
use Rose::DBx::Object::Builder;
my $text = 'Employees have username, password, and position (has a title and description). Projects have name and due date. Employees have many Projects and vice versa.';
my $builder = Rose::DBx::Object::Builder->new(parse => $text);
print $builder->show();
# prints:
CREATE TABLE positions (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
description TEXT
) TYPE=INNODB;
CREATE TABLE employees (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE,
password VARCHAR(255),
position_id INTEGER,
FOREIGN KEY (position_id) REFERENCES positions (id) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;
CREATE TABLE projects (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
due_date DATE
) TYPE=INNODB;
CREATE TABLE employees_projects_map (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
employee_id INTEGER,
project_id INTEGER,
FOREIGN KEY (employee_id) REFERENCES employees (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (project_id) REFERENCES projects (id) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;
DESCRIPTION
Rose::DBx::Object::Builder generates database table schemas from simple, succinct sentences. It alleviates the need to specify the data type for every table columns and simplifies the tedious work of setting up common table relationships. The generated table schemas follow the default conventions of Rose::DB::Object.
METHODS
new
To instantiate a new Builder object:
my $builder = Rose::DBx::Object::Builder->new(config => {db => {type => 'Pg', tables_are_singular => 1}}, parse => 'Users have many photo albums');
Since Builder inherits from Rose::Object, the above line is equivalent to:
my $builder = Rose::DBx::Object::Builder->new();
$builder->config({db => {type => 'Pg', tables_are_singular => 1}});
$builder->parse('Users have many photo albums');
config
The default configurations of a Builder object can be retrieved by:
my $config = $builder->config();
This method accepts a hashref for configuring the Builder instance.
db
The db
option configures database related settings, for instance:
$builder->config({
db => {
name => 'product',
type => 'Pg', # defaulted to 'mysql'
host => '10.0.0.1',
port => '5432',
username => 'admin',
password => 'password',
tables_are_singular => 1, # table name conventions, defaulted to undef
table_prefix => 'test_', # specify a prefix for table names
options => {RaiseError => 1} # database connection options
}
});
Database connection credentials are only required if you are planning to create or alter an existing database using the generated schema. Otherwise, type
and tables_are_singular
are the only required parameters that can affect the generated schemas.
format
The format
option is a hash of coderefs that get triggered to sanitize the expression (sentences), table names, and column names. The expression, table names, or column names are passed to the callback coderef as the only argument.
expression
-
The default coderef formats the given expression (sentences) into lower case and strips out all unnecessary 'the', 'a', and 'an'. The word 'and' is converted to a comma.
table
-
The default coderef trims the whitespaces from the beginning and the end of the given table name and convert all other space characters in between to underscore.
column
-
The default coderef for formatting column names is identical to the one used to format table names.
table
This option defines the CREATE TABLE statement templates for various database types. The built-in supported databases are MySQL, PostgreSQL, and SQLite. This option allows us to change the CREATE TABLE statement for existing or new database types. For instance, we can change the default MySQL storage engine to MyISAM:
$config->{table}->{mysql} = 'CREATE TABLE [% table_name %] ([% columns %]) TYPE=MyISAM;';
primary_key
Builder adds a primary key to all the tables it generate. This option defines the default primary key name and the primary key column type for each database type.
$config->{primary_key}->{name} = 'code'; # change to primary key name to 'code', defaulted to 'id'
$config->{primary_key}->{type}->{Pg} = 'PRIMARY KEY'; # defaulted to 'SERIAL PRIMARY KEY'
foreign_key
Similar to the primary_key
option, this defines various settings for foreign keys:
$config->{foreign_key}->{suffix} = '_code'; # defaulted to '_id'
$config->{foreign_key}->{type}->{mysql} = 'BIGINT'; # defaulted to 'INTEGER'
$config->{foreign_key}->{clause} = 'FOREIGN KEY ([% foreign_key %]) REFERENCES [% reference_table %] ([% reference_primary_key %]) ON DELETE SET NULL'; # defaulted to 'ON UPDATE CASCADE ON DELETE CASCADE'
add_clause
This option defines the ALTER TABLE statement to add foreign keys for 'has many' construct.
map_table
This option defines table name of the mapping table when generating many to many relationships, which is defaulted to [% table_name %]_[% foreign_table_name %]_map
.
columns
Builder has a list of built-in column definitions, which are essentially attribute name to column data type mappings. The default list of columns can be retrieved by:
print join (', ', keys %{$builder->config()->{columns}});
Builder tries to match the name of given attribute to the column definitions defined in columns
. Failing that, it will use the built-in 'name' column definition, which is by default set to 'VARCHAR(255)'.
We can customise or add new column definitions to suit our needs, for example:
$config->{columns}->{status} = qq(VARCHAR(10) DEFAULT 'Active');
...
$builder->parse('Order has order number, purchase date, and status');
generates:
CREATE TABLE orders (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_number NUMERIC,
purchase_date DATE,
status VARCHAR(10) DEFAULT 'Active'
) TYPE=INNODB;
parse
parse
accepts a text string and returns the generated database table schemas. The input text string could be a series of sentences (delimited by periods) in one of the following constructs:
- "... has ..."
-
The "has" keyword can be used to define object attributes, i.e. the columns of a table, separated by commas. For instance:
'Employee has first name, last name, and email'
It can be also used to establish "has a" relationships between tables:
'Employee has first name, last name, email, and position (has title and description).'
The above expression is equivalent to:
'Position has title and description. Employee has first name, last name, email, and position ID (reference).'
The '(reference)' clause indicates that the attribute 'position ID' is a foreign key that references a table called 'position', assuming that table names are singular and no table prefix is defined.
We can also explicitly specify the actual referenced table. For instance, the expression:
'A project has a name and a main task ID (references task)'
generates the following table schema for MySQL:
CREATE TABLE project ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), main_task_id INTEGER, FOREIGN KEY (main_task_id) REFERENCES task (id) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB;
We can explicitly assign a column definition to an attribute, which ultimately determines the data type of the column. For instance:
'Product has name and total (integer).'
generates:
CREATE TABLE product ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), total INTEGER ) TYPE=INNODB;
It is possible to define "has a" relationships between objects recursively in one sentence, although it may become convoluted. For example:
'Employee has first name, last name, email, and position (has title, description, and classification (has category and sub category)).'
The word "has" and "have" are interchangeable in Builder.
- "... has many ..."
-
We can add a "has many" relationship between two existing tables, for instance:
'The company has many locations.'
generates:
ALTER TABLE location ADD company_id INTEGER; ALTER TABLE location ADD FOREIGN KEY (company_id) REFERENCES company (id) ON UPDATE CASCADE ON DELETE CASCADE;
In essence, the "has many" keyword creates a foreign key in the corresponding table.
- "... has many ... and vice versa"
-
This construct is a shortcut to establish a "many to many" relationship between two existing tables by generating the intermediate table. For instance:
'Employees have many Projects and vice versa.'
The above line is equivalent to:
'Employee project map has an employee ID (reference) and an project ID (reference).'
- "... has ... as ..."
-
This construct allows us to add a "has a" relationship between existing tables, for instance:
'Employee has a position as role.'
generates:
ALTER TABLE employee ADD role_id INTEGER; ALTER TABLE employee ADD FOREIGN KEY (role_id) REFERENCES position (id) ON UPDATE CASCADE ON DELETE CASCADE;
show
show
returns the generated schemas in a readable format:
print $builder->show();
build
build
executes the generated schemas using the database connection credentials defined in config
or the provided database handler:
$builder->build();
# or,
$builder->build($dbh);
SEE ALSO
Rose::DB::Object, Rose::DBx::Object::Renderer, DBI
AUTHOR
Xufeng (Danny) Liang (danny.glue@gmail.com)
COPYRIGHT & LICENSE
Copyright 2009 Xufeng (Danny) Liang, All Rights Reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.