[Catalyst] Maintaining updated and created timestamps in database

[Brandon Black]

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 :)

-- Brandon