view release on metacpan or  search on metacpan

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

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>:

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

=head2 Using SQL functions

Each subclass of Alzabo::SQLMaker is capable of exporting functions
that allow you to use all the SQL functions that your RDBMS provides.
These functions are normal Perl functions.  They take as arguments
normal scalars (strings and numbers), C<Alzabo::Column> objects, or
the return value of another SQL function.  They may be used to select
data via the C<select()> and C<function()> methods in both the
L<C<Alzabo::Runtime::Table>|Alzabo::Runtime::Table/"function and
select"> and
L<C<Alzabo::Runtime::Schema>|Alzabo::Runtime::Schema/"function and
select"> classes.  They may also be used as part of updates, inserts,
and where clauses in any place that is valid SQL.

Examples:

 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 ] );

The documentation for the Alzabo::SQLMaker subclass for your RDBMS
will contain a detailed list of all exportable functions.

=head2 Row Objects Not in the Database

Sometimes you'll want to create an object with the row object API, but
which does not represent a row in the database.  See the
L<C<Alzabo::Runtime::Row> documentation|Alzabo::Runtime::Row/POTENTIAL
ROWS> for details on how these objects can be created.

=head2 Changing the schema

In MySQL, there are a number of various types of integers.  The type
C<TINYINT> can hold values from -128 to 127.  But what if have more
than 127 movies?  And if that's the case we might have more than 127
people too.

For safety's sake, it might be best to make all of the primary key
integer columns C<INT> columns instead.  And while we're at it we want
to make them C<UNSIGNED> as well, as we don't need to insert negative
numbers into these columns.

You could break out the RDBMS manual (because you probably forgot the
exact C<ALTER TABLE> syntax you'll need).  Or you could use Alzabo.
Note that this time we use a
L<C<Alzabo::Create::Schema>|Alzabo::Create::Schema> object, not
L<C<Alzabo::Runtime::Schema>|Alzabo::Runtime::Schema>.

  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;

Because Alzabo keeps track of the schema's state the last time it was