Rose Recipes

This is a scratchpad of sorts of various common questions or use cases and how they're tackled in the rdbo mindset.

Recipes or FAQs

How can I backup Rose objects?

Answered by Jason Purdy

Sometimes you may find yourself writing code that deletes objects and perhaps you'd like to back up the objects before they evaporate.

Simple Case

Rose::DB has Storable hooks such that you can automatically freeze/thaw Rose::DB objects, with a caveat that before you thaw, you need to use My::DB in order to bring about awareness of your database before the objects are reinstated. More explanation is available in its documentation.

More Complex Case

Storable is great for one table with no relationships, but if you have one-to-many or many-to-many relationships with associated content, Storable will not be able to handle that. Instead, you can use the as_json and new_from_json helper methods from Rose::DB::Object::Helpers.

Most Complex Case

In one posting, it was pointed out that serializing objects with associated data in a many-to-many relationship will lead to redundant data and may cause problems when trying to restore the backup. In this case, it's recommended that you backup each table individually.

How do I get the maximum value of a table?

Answered by Jason Purdy

Rose::DB::Object::Manager has functions for getting a count of objects, but what if you wanted to just get the maximum value out of all of the tables. In Ye Ol' Days of doing things, it'd be a simple matter of:

my $dbh = DBI->connect( ... );
my $sth = $dbh->prepare( 'SELECT MAX(last_sync) FROM contact' );
$sth->execute;
my ( $max_time ) = $sth->fetchrow_array;
$sth->finish;
$dbh->disconnect;
# roll your own parse_timestamp to convert $max_time to a DateTime obj (if you need it)

But as a mindful RDBO citizen, you want to know how to do this in the Rose way. (NOTE: This is my approach and I definitely welcome improvements/suggestions) You could put this code in your Rose::DB::Object or Rose::DB::Object::Manager subclass.

sub get_maximum_timestamp {
    my ($class, %args) = @_;
    my $db = $args{'db'} || QSR::RDB->init_db; # QSR::RDB is a Rose::DB subclass example
    my $sth = $db->dbh->prepare( 'SELECT MAX(last_sync) FROM contact' );
    $sth->execute;
    my ( $t ) = $sth->fetchrow_array;
    my $max = $db->parse_timestamp( $t );
    $sth->finish;
    return $max;
}

Then in your application, you would have something like this:

# ...
my $max = QSR::RDB::Contact->get_maximum_timestamp;
# now you can do whatever you want w/ $max
# $max is a DateTime object

This recipe is specific to MySQL's SQL specification, so if you're using another database flavor, you'll have to modify the SQL code above. This code can also be tweaked to use other aggregate operations, such as MIN(), AVERAGE(), etc.