Copyright Notice
This text is copyright by InfoStrada Communications, Inc., and is used with their permission. Further distribution or use is not permitted.This text has appeared in an edited form in Linux Magazine magazine. However, the version you are reading here is as the author originally submitted the article for publication, not after their editors applied their creativity.
Please read all the information in the table of contents before using this article.
Linux Magazine Column 88 (Dec 2006)
[suggested title: ``Using Rose::DB::Object'']
In [the last two columns], I introduced Rose::DB::Object (RDBO) as
the current ``best of breed'' Object-Relational mappers, and set up a
sample database structure to demonstrate some basic features. The
database relates people to the films in which they appear, and the
studios that produce those films. I then showed how to set up the
``metadata'' for RDBO so that I can perform common operations by writing
relatively simple Perl code.
Let's populate our database with a few well-known movie people. We'll
do this by creating a new RDBO-derived object with the proper column
data, and then calling the save method on it:
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 DateTime object understands, or
whatever the native format understands. I'm using YYYY-MM-DD
because it seems to work.
Besides the two columns set here, each row also has an id field. Once
the row is saved, the value is populated in our object:
my $george = My::RDBO::Person->new
(name => 'George Lucas',
birthdate => '1944-05-14',
)->save;
print $george->id, "\n";
This prints ``4'' as the fourth entry added. Note that save returns
the object as well, making it easy to chain the method calls.
Now that we've added a few values, let's see what we have in the table. We can fetch the information for an individual if we 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;
By selecting id 2, we'll retrieve Harrison Ford's information:
Harrison Ford [2] born on 1942-07-13T00:00:00
Note that the timestamp is converted to ISO-8601 for SQLite. In other
databases, it retains native formatting. If we ask for a bogus ID,
RDBO throws an error by default. We can add speculative to the
load method to return an undef indicator 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";
}
}
which 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
We could awkardly use this to dump every row of the database, because
the keys are assigned sequentially. However, once we start deleting
rows, or if we have non-sequential primary keys, we need other ways
to ask for all rows. For that, we go to the manager class for the
table. In this case, we can ask 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 so far,
in an unspecified order, although it looks like SQLite is giving it to
me in the order of row creation. To add order to our chaos, we add a
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 birthdate 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, we'll rarely just want a complete dump of everything we've
entered. The real work comes in when we make SQL-style queries. In RDBO,
we specify this in a 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;
}
And this finds Harrison Ford and Carrie Fisher, as expected. The ordering
is unspecified, so if it matters, we can add our sort_by back in:
... ->get_persons
(query => [name => {like => '%is%'},
],
sort_by => 'birthdate DESC',
) ...
We can also find everyone born before the 50's:
... ->get_persons
(query => [birthdate => {lt => '1950-01-01'},
],
sort_by => 'birthdate DESC',
) ...
which 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 results in 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 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, we can set up an iterator on all people, by descending birthdate:
my $iter = My::RDBO::Person::Manager->get_persons_iterator
(sort_by => 'birthdate DESC',
);
Now we need to walk the iterator, by calling next on it until
it returns a false value:
while (my $person = $iter->next) {
printf "%s [%d] born on %s\n",
$person->name, $person->id, $person->birthdate;
}
And finally, we 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 to fetch him back, we don't know the ID. So we have to 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), we'll just die if there's not exactly one. Now we can update the one, most easily with a loop:
for my $cushing (@$cushings) {
$cushing->birthdate('1913-05-26');
$cushing->save;
}
Unless we save explicitly here, the database is not updated, because
the in-memory version is the only value affected. Our 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
We could also combine these steps using the 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, we 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 ],
We've now covered the C, R, and U (Create, Retrieve, and Update) of CRUD. It's time to look at the D: Delete.
We 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 we didn't know the exact primary key of a row to be deleted, we
must again go to the manager. This time, we use delete_persons
with a where clause similar to the 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:
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!