[Catalyst] Maintaining updated and created timestamps in database

[Matt S Trout]

On Wed, Mar 01, 2006 at 11:22:52PM -0600, Brandon Black wrote:
> On 3/1/06, hkclark at gmail.com <hkclark at gmail.com> wrote:
> > I know RoR will automatically maintain the dates of columns named
> > "updated_on" and "created_on".  Can I do a similar thing with
> > Catalyst?  My currently plan is to use  Catalyst::Model::CDBI.
> >
> > I took a look at the EasyCMS example app and I see that it has a
> > "posted" column in a couple of tables.  I can't find any code inside
> > EasyCMS itself that is doing the update (but I could be overlooking
> > it), so I assume these are auto-updating (but I can't find any docs
> > that provide details on that.
> >
> > Any suggestions?
> >
> 
> The "created_on" part can be handled automagically at the database
> level for most SQL implementations.  For instance, in PostgreSQL, you
> would define the column as:
> 
> created_on TIMESTAMP NOT NULL DEFAULT current_timestamp
> 
> (assuming I didn't make a silly syntax error there).  You would still
> be able to override that by explicitly setting it during a create or
> an update, which might or might not be desirable.
> 
> You could also do both the created_on and the updated_on as triggers
> at the database level.  The advantage of using triggers is that you
> could enforce that created_on and updated_on always really reflect
> just that, and not allow them to be arbitrarily set to other values by
> your code (or by any other future code that might make a SQL
> connection to your database).
> 
> I would try to handle it at the DB level along those kinds of lines if
> it were me.
> 
> I don't know anything about doing it in Class::DBI, so I'll leave that
> one alone.
> 
> To do it in DBIx::Class will require someone writing a new add-on
> component module for it.  Having it always just happen for columns
> with the magic names created_on and updated_on is probably a Bad Idea
> in the general case, but one could make it so that the new component
> (DBIx::Class::AutoTimestamps or some such thing) would take a new
> setup-time call like __PACKAGE__->auto_timestamp_cols({ create =>
> created_on, update => updated_on }); when the source class is set up.
> 
> Writing such a package is left as an exercise for the next guy to read this :)

sub insert {
  my $self = shift;
  $self->set_column('created_on', time());
  $self->next::method(@_);
}

sub update {
  my $self = shift;
  $self->set_column('updated_on', time());
  $self->next::method(@_);
}

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +