Category Archives: Perl

Getting ActiveRecord-like ‘created_at’ behaviour with Perl’s Class::DBI

I really like ActiveRecord and on a recent project I wanted to be able to create fields in my database called ‘created_at’ and have them automatically filled with the current date at time of insert just as ActiveRecord does. Unfortunatly I was forced to use Perl’s Class::DBI* so I came up a simple method of creating the same behaviour.

First, create a parent class that all of your Class::DBI table classes will inherit from:

package DB::DBI;
use base 'Class::DBI';
use POSIX qw(strftime);

#Your connection string will vary:
__PACKAGE__->connection('dbi:mysql:database=db_name;host=127.0.0.1',"username","password");

# Automatically add timestamp to 'created_at' fields.
__PACKAGE__->add_trigger(before_create => sub {
my $self = shift;
$self->can('created_at') and $self->set('created_at', strftime("%Y-%m-%d %H:%M:%S", localtime));
});

Now create a class for each of your tables inheriting from the parent class:

package DB::MyTable;
# Inherit from DB::DBI
use base 'DB::DBI';

__PACKAGE__->table('my_table');
__PACKAGE__->columns(All => qw/id field1 field2 field3 created_at/);

And now when you do a DB::MyTable->create({...}) your created_at field will be automatically populated with the current timestamp. Yay!

* And no, I wasn’t allowed to use DBIx::Class.

Set the format of ‘Date’ fields returned by Oracle

For some reason Oracle returns the date from ‘date’ fields in the format ‘dd-short_month_name-yy’ (or something like that) by default. This is crazy, who uses the date in that format? No-one, that’s who. To change the format of dates returned for all queries returned to a database handle do something like this:

$dbh->do("alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'");

(or the equivalent in your language of choice) and the date returned from any date fields will be sensible, much like the way postgres does it. And why does Oracle not allow the use of limit? I guess this comes as a £10,000 upgrade or something.

How to get the SQL that Perl’s DBI is running

It seems to be a question that comes up fairly often, and it is pretty easy:

$dbh->{TraceLevel} = "SQL";

Follow

Get every new post delivered to your Inbox.