Data Modeling with Alzabo

Once you execute the SQL from within the schema editor, you have two ways to access the data. You may, of course, access it directly using DBI (or a similar interface from another language), creating and executing SQL queries.

For example, let's assume that I have created my addressbook schema with the following table:

CREATE TABLE People (
    person_id    SERIAL    NOT NULL,
    first_name   TEXT      NOT NULL,
    last_name    TEXT      NOT NULL,
    birthday     DATE      NOT NULL,
    PRIMARY KEY(person_id)
);

In order to make things a bit more interesting, let's populate our table with some values:

INSERT INTO People (first_name, last_name, birthday)
VALUES ('Reuven', 'Lerner',
'1970-Jul-14');
INSERT INTO People (first_name, last_name, birthday)
VALUES ('Atara Margalit', 'Lerner-Friedman',
        '2000-Dec-16');

Listing 1 contains a simple Perl program that uses DBI to retrieve the names (and birthdays) of people in our addressbook who match the SQL pattern entered on the command line. (SQL patterns are much simpler than UNIX regular expressions—there are only two characters: % matches zero or more characters and _ matches exactly one character.)

Listing 1. retrieve-today-birthday.pl, which uses DBI to retrieve the names of people in our addressbook table whose birthdays are today.

We retrieve the user's input on the command line and place % signs before and after it to ensure that the string will match, regardless of where it occurs in the first_name or last_name column. Then we connect to the database, turning on AutoCommit (as the DBI documentation encourages us to do) and activating the RaiseError and PrintError diagnostic aids.

Finally, we create our SQL query in the $sql variable, making sure to use placeholders (“?”) instead of directly interpolating variables. Not only does this reduce the risk of someone messing with our SQL, but some database drivers will take advantage of our placeholders in subsequent queries, giving us a speed boost.

Let's rewrite this program using Alzabo instead of straight DBI. We won't write the SQL ourselves or connect to the database ourselves. Rather, we will create a new schema object, naming the schema that we created with Alzabo's interactive tool. This object has a number of methods that let us perform many of the tasks for which we would otherwise use DBI.

As you can see from Listing 2, there are not many differences between the two versions until we connect to the data source. In the DBI version of the program, we connected to the data source itself with DBI->connect. In Alzabo, however, we connect to a schema, which is presumably attached to a database, and assign it to the object $schema.

Listing 2. retrieve-birthday-alzabo.pl, an Alzabo implementation of the program in Listing 1.

Using $schema, we retrieve a table object associated with one of our tables:

my $people = $schema->table("People");

Now that we have an object mapped to our People table, we can retrieve selected rows from the table. The easiest way to retrieve rows is with the rows_where method. This returns a single object of type Alzabo::Runtime::RowCursor:

my $row_cursor =
    $people->rows_where
        (where => [[$people->column('first_name'),
                   'LIKE', $look_for_name],
                 'or',
                 [$people->column('last_name'),
                  'LIKE',
                  $look_for_name]]);

Alzabo's WHERE clauses usually consist of a three-element list: a column object, a comparison operator and a value or second column object. We can compare the first_name column for equality with Zaphod with:

where => [$table->column('first_name'), '=',
'Zaphod']

In Listing 2, we have made this a bit more complicated, linking two array references with the OR boolean operator:

where => [[$people->column('first_name'),
           'LIKE', $look_for_name],
          'or',
          [$people->column('last_name'),
           'LIKE', $look_for_name]]

Alzabo is smart enough to realize that the first and third elements of its WHERE clause are array references, and it turns the above code into the appropriate SQL.

Once we have our RowCursor object, we iterate through each row with the next_row method:

while (my $row = $row_cursor->next_row)
{
   my $first_name = $row->select('first_name');
   my $last_name = $row->select('last_name');
   my $birthday = $row->select('birthday');
   print "$first_name $last_name
          (birthday: $birthday)\n";
   $rows_returned++;
}