Data Modeling with Alzabo

Reuven takes a detour this month and shows you how to bridge the object-relational gap.
Using Our Schema from a Program

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.

Rewriting in Alzabo

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

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState