NAME

DESCRIPTION

CREATING A SCHEMA

And of course, you can create one through a custom Perl script which uses the various "Alzabo::Create::*" classes. Here's the beginning of such a script:

  use Alzabo::Create::Schema;

  eval
  {
      my $s = Alzabo::Create::Schema->new( name  => 'foo',
                                           rdbms => 'MySQL' );

      my $table = $s->make_table( name => 'some_table' );

      my $a_col = $table->make_column( name => 'a_column',
                                       type => 'int',
                                       nullable   => 0,
                                       sequenced  => 0,
                                       attributes => [ 'unsigned' ] );

      $table->add_primary_key($a_col);

      my $b_col = $table->make_column( name => 'b_column',
                                       type => 'varchar',
                                       length   => 240,
                                       nullable => 0 );

      $table->make_index( columns => [ { column => $b_col,
                                         prefix => 10 } ] );

      ...

      $s->save_to_file;
  };

  if ($@) { handle exceptions }

USAGE EXAMPLES

  TABLE: Movie
  movie_id                 tinyint      -- primary key
  title                    varchar(200)
  release_year             year

  TABLE: Person
  person_id                tinyint      -- primary key
  name                     varchar(200)
  birthdate                date
  birthplace_location_id   tinyint      -- foreign key to location

  TABLE: Job
  job_id                   tinyint      -- primary key
  job                      varchar(200) -- something like 'actor' or 'director'

  TABLE: Credit
  movie_id                 tinyint      -- primary key part 1, foreign key to movie
  person_id                tinyint      -- primary key part 2, foreign key to person
  job_id                   tinyint      -- primary key part 3, foreign key to job

  TABLE: Location
  location_id              tinyint      -- primary key
  location                 varchar(200) -- 'New York City' or 'USA'
  parent_location_id       tinyint      -- foreign key to location

  my $schema = Alzabo::Runtime::Schema->load_from_file( name => 'movies' );

  my $person_t = $schema->table('Person');
  my $credit_t = $schema->table('Credit');
  my $movie_t  = $schema->table('Movie');
  my $job_t    = $schema->table('Job');

  # returns a row representing this person.
  my $person = $person_t->row_by_pk( pk => 42 );

  # all the rows in the credit table that have the person_id of 42.
  my $cursor =
      $person->rows_by_foreign_key
          ( foreign_key =>
            $person_t->foreign_keys_by_table($credit_t) );

  print $person->select('name'), " was in the following films:\n\n";

  while (my $credit = $cursor->next)
  {
      # rows_by_foreign_key returns a RowCursor object.  We immediately
      # call its next method, knowing it will only have one row (if
      # it doesn't then our referential integrity is in trouble!)
      my $movie =
          $credit->rows_by_foreign_key
              ( foreign_key =>
                $credit_t->foreign_keys_by_table($movie_t) )->next;

      my $job =
          $credit->rows_by_foreign_key
              ( foreign_key =>
                $credit_t->foreign_keys_by_table($job_t) )->next;

      print $movie->select('title'), " released in ", $movie->select('release_year'), "\n";
      print '  ', $job->('job'), "\n";
  }

  # Assume that the method_namer() subroutine pluralizes things as one
  # would expect.
  use Alzabo::MethodMaker( schema      => 'movies',
                           all         => 1,
                           name_maker  => \&method_namer );

  my $schema = Alzabo::Runtime::Schema->load_from_file( name => 'movies' );

  # instantiates a row representing this person.
  my $person = $schema->Person->row_by_pk( pk => 42 );

  # all the rows in the credit table that have the person_id of 42.
  my $cursor = $person->Credits;

  print $person->name, " was in the following films:\n\n";

  while (my $credit = $cursor->next)
  {
      my $movie = $credit->Movie;

      my $job = $credit->Job;

      print $movie->title, " released in ", $movie->release_year, "\n";
      print '  ', $job->job, "\n";
  }

  $movie->update( title => 'Chungking Express',
                  year  => 1994 );

  $movie->title('Chungking Express');

  $movie->delete;

  sub update_location
  {
      my $self = shift; # this is the row object

      my %data = @_;

      if ( $data{parent_location_id} )
      {
          my $parent_location_id = $data{parent_location_id};
          my $location_t = $schema->table('Location');

          while ( my $location =
                  $location_t->row_by_pk( pk => $parent_location_id ) )
          {
              die "Insert into location would create loop"
                  if $location->select('parent_location_id') == $data{location_id};

              $parent_location_id = $location->select('parent_location_id');
          }
      }
  }

  sub update_location
  {
      my $self = shift; # this is the row object

      my %data = @_;

      if ( $data{parent_location_id} )
      {
          my $location = $self;
          while ( my $location = $location->parent )
          {
              die "Insert into location would create loop"
                  if $location->parent_location_id == $data{location_id};
          }
      }
  }

 use Alzabo::SQLMaker::MySQL qw(MAX NOW PI);

 my $max =
     $table->function( select => MAX( $table->column('budget') ),
                       where  => [ $table->column('country'), '=', 'USA' ] );

 $table->insert( values => { create_date => NOW() } );

 $row->update( pi => PI() );

 my $cursor =
     $table->rows_where( where =>
                         [ $table->column('expire_date'), '<=', NOW() ] );

 my $cursor =
     $table->rows_where( where =>
                         [ LENGTH( $table->column('password') ), '<=', 5 ] );

  my $schema = Alzabo::Create::Schema->load_from_file( name => 'movies' );

  foreach my $t ( $schema->tables )
  {
      foreach my $c ( $t->columns )
      {
           if ( $c->is_primary_key and lc $c->type eq 'tinyint' )
           {
                $c->set_type('int');
                $c->add_attribute('unsigned');
           }
      }
  }

  $schema->create( user => 'user', password => 'password' );
  $schema->save_to_file;

TRANSACTIONS

If you would like to use transactions explicitly in your code, please make sure to use the "Alzabo::Schema" class's "begin_work()", "commit()", and "rollback()" methods.

EXCEPTIONS

Also see the "Alzabo::Exceptions" documentation, which lists all of the different exception used by Alzabo.

This is similar to using "DBI" with the "RaiseError" attribute set to a true value.

Its important to note that some methods (such as the driver's "rollback()" method) may use "eval" internally. This means that if you intend to use them as part of the cleanup after an exception, you may need to store the original exception in another variable, as $@ will be overwritten at the next "eval".

In addition, some methods you might use during cleanup can throw exceptions of their own.

This is the point where I start wishing Perl had a real exception handling mechanism built into the language.

BACKWARDS COMPATIBILITY

As of Alzabo version 0.65, Alzabo can now detect older schemas and will attempt to update them if possible.

When you attempt to load a schema, whether of the "Alzabo::Create::Schema" or "Alzabo::Runtime::Schema" classes, Alzabo will determine what version of Alzabo created that schema.

If updates are necessary, Alzabo will first back up your existing files with the extension .bak.v{version}, where "{version}" is the version of Alzabo which created the schema.

Then it will alter the schema as necessary and save it to disk.

This will all happen transparently, as long as the process which initiated this process can write to the schema files and the directory they are in.

Alzabo will need the "Alzabo::Create::*" classes to update the schema. If these have not been loaded already, Alzabo will do so and issue a warning to say that this has happened, in case you would like to restart the process without these classes loaded.

MULTIPLE COPIES OF THE SAME SCHEMA

Every time you call "create()" or "sync_backend()", the schema will consider itself to have been instantiated. This means that if you call "create()" twice, each time with a different "schema_name" parameter, then you will probably not be able to generate useful diffs via the "make_sql()" method in the future.

This is a bug that is unlikely to be fixed.

MULTIPLE RDBMS SUPPORT

The first, the "Alzabo::Driver::*" hierarchy, is used to handle communication with the database. It uses "DBI" and the appropriate "DBD::*" module to handle communications. It provides a higher level of abstraction than "DBI", requiring that the RDBMS specific modules implement methods to do such things as create databases or return the next value in a sequence.

The second, the "Alzabo::RDBMSRules::*" hierarchy, is used during schema creation in order to validate user input such as schema and table names. It also generates DDL SQL to create the database or turn one schema into another (sort of a SQL diff). Finally, it also handles reverse engineering of an existing database.

The "Alzabo::SQLMaker::*" hierarchy is used to generate DML SQL and handle bound parameters.

The RDBMS to be used is specified when creating the schema. Currently, there is no easy way to convert a schema from one RDBMS to another, though this is a future goal.

REFERENTIAL INTEGRITY

Alzabo enforces these referential integrity rules:

• Inserts

  An attempt to insert a value into a table's foreign key column(s) will fail if the value does not exist in the foreign table.

  If a table is dependent on another table, any columns from the dependent table involved in the relationship will be treated as not nullable.

  If the relationship is one-to-one, all columns involved in the foreign key will be treated as if they had a unique constraint on them (as a group if there is more than one) unless any of the columns being inserted are NULL.

  The exception to this rule is that no attempt is made to enforce constraints on a table's primary key, as that could conceivably make it impossible to insert a row into the table.

• Updates

  Updates follow the same rules as inserts.

• Deletes

  When a row is deleted, foreign tables which are dependent on the one being deleted will have the relevant rows deleted. Otherwise, the foreign table's related column(s) will simply be set to NULL.

AUTHOR