Generate database tables with rose::dbx::object::builder

Generating Database Tables

Assuming that Postgres is installed locally and can be connected using trust authentication:

1. Create a temporary database called 'demo' (owned by user postgres)
2. Create a new Perl script and create a new Rose::DBx::Object::Builder instance

  #!/usr/bin/perl
  use strict;
  use warnings;

  use Rose::DBx::Object::Builder;

  my $builder = Rose::DBx::Object::Builder->new(
    config => {
      db => {
        type => 'Pg', 
        name => 'demo', 
        username => 'postgres', 
        password => '', 
        tables_are_singular => 1
      }
    }
  );

Here, the credentials for connecting to the local Postgres database are optional. However, we do have to nominate the database type (defaulted to MySQL), which determines the generated schema.

3. Write about the business objects required

  my $text = 'Employee has a first name (required), last name (required), email(unique), photo, gender,
              position (has a title and description), and address.
              Projects have name, description, date, image, and cost.
              Employees have many projects and vice versa.';

4. Pass the text to the Builder instance to generate the table schemas

  print $builder->show($text);

This prints:

  CREATE TABLE position (
  	id SERIAL PRIMARY KEY,
  	title VARCHAR(255),
  	description TEXT
  );

  CREATE TABLE employee (
  	id SERIAL PRIMARY KEY,
  	first_name VARCHAR(255) NOT NULL,
  	last_name VARCHAR(255) NOT NULL,
  	email VARCHAR(255) UNIQUE,
  	photo VARCHAR(255),
  	gender VARCHAR(255),
  	position_id INTEGER,
  	address VARCHAR(255),
  	FOREIGN KEY (position_id) REFERENCES position (id) ON UPDATE CASCADE ON DELETE CASCADE
  );

  CREATE TABLE project (
  	id SERIAL PRIMARY KEY,
  	name VARCHAR(255),
  	description TEXT,
  	date DATE,
  	image VARCHAR(255),
  	cost DECIMAL(13,2)
  );

  CREATE TABLE employee_project_map (
  	id SERIAL PRIMARY KEY,
  	employee_id INTEGER,
  	project_id INTEGER,
  	FOREIGN KEY (employee_id) REFERENCES employee (id) ON UPDATE CASCADE ON DELETE CASCADE,
  	FOREIGN KEY (project_id) REFERENCES project (id) ON UPDATE CASCADE ON DELETE CASCADE
  );

5. If we have happy with the tables, then we can either call parse() to generate a one-liner, copy and paste it into the psql command line

  print $builder->parse($text);

or ask Builder to build it for us directly, since we have provided sufficient credentials for Builder to connect to the local Postgres database

  $builder->parse($text);
  $builder->build();

Complete Source

  #!/usr/bin/perl
  use strict;
  use warnings;


  use Rose::DBx::Object::Builder;

  my $builder = Rose::DBx::Object::Builder->new(
    config => {
      db => {
        type => 'Pg', 
        name => 'demo', 
        username => 'postgres', 
        password => '', 
        tables_are_singular => 1
      }
    }
  );

  my $text = 'Employee has a first name (required), last name (required), email(unique), photo, gender, 
              position (has a title and description), and address.
              Projects have name, description, date, image, and cost.
              Employees have many projects and vice versa.';
            
  $builder->parse($text);
  $builder->build();