dcsimg

Rose::DB::Object, Part Three

Create, retrieve, update, and delete records easily with the Rose::DB::Object object-relational mapper.

My last two columns introduced package "c">Rose::DB::Object (RDBO) as the current “best of
breed” object-relational mapper. Over the span of those
columns, I created a sample database structure to demonstrate some
basic features. Specifically, SQLite is the
database engine, and the sample database relates an actor to the
films in which he or she appeared, and relates each film to the the
studio that produced it. I also showed how to set up the
“metadata” for RDBO, so common database operations can
be performed with relatively simple Perl
code.

With the scaffolding in place, let’s populate the database
with a few well-known actors. Adding records is simple: create a
new RDBO-derived object with the proper column data, and then call
the object’s save() method:

My::RDBO::Person->new(
    name => ’Mark Hamill’,
    birthdate => ’1951-09-25’,
)->save;

My::RDBO::Person->new(
    name => ’Harrison Ford’,
    birthdate => ’1942-07-13’,
)->save;

My::RDBO::Person->new(
    name => ’Carrie Fisher’,
    birthdate => ’1956-10-21’,
)->save;

The date format here is whatever a "c">DateTime object understands, or whatever the native
format understands. I’m using "c">YYYY-MM-DD because it seems to work and the dates are
human-readable.

Besides the two columns that are explicitly set, each row also
has an id field. Once the new row is saved,
that value is populated in each new object:

my $george = My::RDBO::Person->new(
    name => ’George Lucas’,
    birthdate => ’1944-05-14’,
)->save;

print $george->id, "\n";

This prints 4, since its the fourth entry
added.

Conveniently, save() returns the object
as well, making it easy to chain the method calls.

Now that the database has a few values, let’s see
what’s in the table. You can fetch the information for an
individual if you know his id:

my $person = My::RDBO::Person->new(id => 2)->load;
printf "%s [%d] born on %s\n",
$person->name, $person->id, $person->birthdate;

Selecting id 2 retrieves Harrison
Ford’s information:

Harrison Ford [2] born on 1942-07-13T00:00:00

Here, the timestamp is converted to ISO-8601 for SQLite. In
other databases, it retains native formatting.

If you ask for a bogus ID, RDBO throws an error by default. You
can add speculative to the load method to
return undef instead:

for my $id (1..10) {
    if (my $person = My::RDBO::Person
    ->new(id => $id)
    ->load(speculative => 1)) {
      printf "%s [%d] born on %s\n",
    $person->name, $person->id, $person->birthdate;
    } else {
      print "no such person $id\n";
    }
}

The code snippet prints…

Mark Hamill [1] born on 1951-09-25T00:00:00
Harrison Ford [2] born on 1942-07-13T00:00:00
Carrie Fisher [3] born on 1956-10-21T00:00:00
George Lucas [4] born on 1944-05-14T00:00:00
no such person 5
no such person 6
no such person 7
no such person 8
no such person 9
no such person 10

You could awkardly use such a code snippet to dump every row of
the database, because the keys are assigned sequentially. However,
once you delete even a few rows, or if you have non-sequential
primary keys, you’d need other ways to ask for all rows.

For that, go to the manager class for the
table. In this case, ask "c">My::RDBO::Person::Manager:

for my $person (@{My::RDBO::Person::Manager->get_persons}) {
    printf "%s [%d] born on %s\n",
        $person->name, $person->id, $person->birthdate;
}

The get_persons() method returns an
arrayref of every person added so far, in an unspecified order
(although it looks like SQLite returns rows in the order of row
creation). To add order to such chaos, add a "c">sort_by clause:

for my $person (@{My::RDBO::Person::Manager->get_persons
(sort_by => ’birthdate DESC’)}) {
    printf "%s [%d] born on %s\n",
        $person->name, $person->id, $person->birthdate;
}

Now the result is sorted by birth date in a descending
order:

Carrie Fisher [3] born on 1956-10-21T00:00:00
Mark Hamill [1] born on 1951-09-25T00:00:00
George Lucas [4] born on 1944-05-14T00:00:00
Harrison Ford [2] born on 1942-07-13T00:00:00

Of course, you’ll rarely just want a complete dump of
everything. The real work comes in with SQL-
style queries. In RDBO, we specify this in a "c">query clause to the get_persons()
method. First, let’s find everyone whose name contains
is:

for my $person (@{My::RDBO::Person::Manager->get_persons
    (query => [name => {like => ’%is%’},],
)}) {
    printf "%s [%d] born on %s\n",
      $person->name, $person->id, $person->birthdate;
}

This finds Harrison Ford and "c">Carrie Fisher, as expected. The ordering

is unspecified, so if it matters, add the "c">sort_by back in:

…->get_persons
  (query => [name => {like => ’%is%’},
    ],
   sort_by => ’birthdate DESC’,
  ) …

You can also find everyone born before the 50’s:

… ->get_persons
  (query => [birthdate => {lt => ’1950-01-01’},
    ],
   sort_by => ’birthdate DESC’,
  ) …

This shows Lucas and Ford being the two oldies of the bunch.
ANDing those together means simply including the two clauses inside
the arrayref, since and is the default
joiner:

… ->get_persons
  (query => 
    [birthdate => {lt => ’1950-01-01’},
     name => {like => ’%is%’},],
   sort_by => ’birthdate DESC’,
  ) …

This code indicates that Harrison Ford in a class by himself. To
or the cases together, we to switch from the
default ANDing to ORing:

… ->get_persons
  (query => [or => [birthdate => {lt => ’1950-01-01’},
        name => {like => ’%is%’}, ], ],
   sort_by => ’birthdate DESC’,
  ) …

And now only Mark Hamill is left out, being both too young and
not having a properly shaped name. The query language (described in
Rose::DB::Object::QueryBuilder) is quite
compact and maps well to the underlying SQL. In fact, you can
include SQL snippets directly, for the few uncommon operations not
trivially supported.

This form of fetching returns the entire result set as one
arrayref. Some databases support an incremental response for the
results, and for large result sets, this can be a more efficient
way to use memory. Regardless of whether or not the database
supports incremental responses, you can set up an "i">iterator for any of the queries above. A call to
get_persons_iterator will return a
Rose::DB::Object::Iterator object.

For example, to fetch everyone, but one person at a time, set up
an iterator on all people, by descending birthdate:

my $iter = My::RDBO::Person::Manager->get_persons_iterator
    (sort_by => ’birthdate DESC’,);

Now, walk the iterator by calling next on
it until the iterator returns a false value:

while (my $person = $iter->next) {
    printf "%s [%d] born on %s\n",
    $person->name, $person->id, $person->birthdate;
}

And finally, YOU can perform meta-operations on the
iterator:

print "total persons: ", $iter->total, "\n";

(Using an iterator in this way won’t have any negative
impact on databases that don’t support incremental
fetching.)

Let’s add Peter Cushing, initially without a birthdate,
and then fix that. First, adding him is easy:

My::RDBO::Person->new
    (name => ’Peter Cushing’,
)->save;

But, Cushing’s recrord lacks an ID. To retrieve the ID,
ask the manager class to look for him by name:

my $cushings = My::RDBO::Person::Manager->get_persons
    (query => [name => ’Peter Cushing’]);
die "expected 1 cushing, got ".@$cushings unless @$cushings == 1;

We expect exactly one Peter Cushing here. However, since the
name field is not unique (and it can’t be, since there are
many film industry members with the same name), the code dies if
there’s not exactly one.

Now you can update the one, most easily with a loop:

for my $cushing (@$cushings) {
    $cushing->birthdate(’1913-05-26’);
    $cushing->save;
}

Unless you save explicitly, the database
is not updated, because the in-memory version is the only value
affected.

The menagerie of names now looks like:

Carrie Fisher [3] born on 1956-10-21T00:00:00
Mark Hamill [1] born on 1951-09-25T00:00:00
George Lucas [4] born on 1944-05-14T00:00:00
Harrison Ford [2] born on 1942-07-13T00:00:00
Peter Cushing [5] born on 1913-05-26T00:00:00

You can also combine these steps using the "c">update_persons() manager method:

My::RDBO::Person->new
  (name => ’Peter Cushing’,
)->save;

my $updated = My::RDBO::Person::Manager->update_persons
    (where => [ name => ’Peter Cushing’ ],
     set => { birthdate => ’1913-05-26’ },
);

print "$updated rows updated\n";

Here, the where clause acts just like the
query clause that appeared earlier, and the
set clause provides the updated values.

To be safer, you might want to update only the ones that have
NULL for a current birthdate, which you can get by modifying that
clause to:

where => [ name => ’Peter Cushing’, birthdate => undef ],

So far, you’ve seen the” C,” ” R,” and” U” (” Create,” ”
Retrieve,” and” Update”) of CRUD. It’s time to look at the”
D,” or” Delete.”

You can ask an individual row to delete itself:

my $mark = My::RDBO::Person->new(id => 1);
$mark->delete or warn "Mark Hamill not found";

This identifies Mark Hamill (id 1), and deletes the record. Note
that we didn’t need to load the row first, although
it’s not an error to misspecify the id (nothing happens).

If you don’t know the exact primary key of a row to be
deleted, you must again go to the manager. This time, you can use
delete_persons() with a "c">where clause similar to the "c">update_persons():

my $deleted = My::RDBO::Person::Manager->delete_persons
    (where => [ name => ’Mark Hamill’ ]);
print "$deleted rows deleted\n";

The queries can include any normal query syntax, such
as…

my $deleted = My::RDBO::Person::Manager->delete_persons
    (where => [or => [birthdate => {lt => ’1950-01-01’},
          name => {like => ’%is%’},
         ],
      ]);

… which deletes everyone except
Mark Hamill.

Well, that’s the basics of CRUD with RDBO. Next month,
I’ll continue by showing how to perform CRUD involving more
than one table. Until then, enjoy!

Comments are closed.