view release on metacpan or  search on metacpan

lib/Alzabo/Intro.pod  view on Meta::CPAN

distribution, provides another means of creating a schema.

And of course, you can create one through a custom Perl script which
uses the various C<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 }

=head1 USAGE EXAMPLES

Alzabo is a powerful tool but as with many powerful tools it can also
be a bit overwhelming at first.  The easiest way to understand some of
its basic capabilities is through some examples.  Let's first assume
that you've created the following schema:

  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

=head2 Fetching data

In Alzabo, data is returned in the form of a L<row
object|Alzabo::Runtime::Row>.  This object can be used to access the
data for an individual row.

Unless you are retrieving a row via a unique identifier (usually its
primary key), you will be given a L<cursor|Alzabo::Runtime::RowCursor>
object.  This is quite similar to how C<DBI> uses statement handles
and is done for similar reasons.

First of all, let's do something simple. Let's assume I have a
person_id value and I want to find all the movies that they were in
and print the title, year of release, and the job they did in the
movie.  Here's what it looks like:

  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";
  }

A more sophisticated version of this code would take into account that
a person can do more than one job in the same movie.

The method names are quite verbose, so let's redo the example using
L<C<Alzabo::MethodMaker>|Alzabo::MethodMaker>:

  # 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";
  }

=head2 Updating data

Updates are done by calling the C<update()> method on a row object:

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

If you are using C<Alzabo::MethodMaker>, the per-column accessors it
generates for row objects can be used to set a column's value:

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

Be careful with this, though, because updates are done immediately
against the RDBMS, meaning each call to a setter method issues an
C<UPDATE> query.  It's much more efficient to call the C<update()>
method once with multiple values.

=head2 Deleting data

To delete a row, just call it's C<delete()> method:

  $movie->delete;

=head2 Validating data

Let's assume that we've been passed a hash of values representing an
update to the location table. Here's a way of making sure that that
this update won't lead to a loop in terms of the parent/child
relationships.

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

Once again, let's rewrite the code to use
L<C<Alzabo::MethodMaker>|Alzabo::MethodMaker>: