The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Rose-DBx-Object-Builder-0.09
River stage zero No dependents
++
/ Rose::DBx::Object::Builder

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.