[Catalyst] Templates in the database

[Bill Moseley]

On Sat, Jan 20, 2007 at 10:13:13PM +0000, Jess Robinson wrote:
> I kept meaning to upload it to CPAN, once TT 2.15 was out.. so now I have 
> (it should go in the TT repository eventually..) I needed to patch 
> Template::Provider to make the code somewhat saner, which got into 2.15.
> Yes, it does use normal TT caching, not that I've tried it out much.

Is the code around?  Or are you waiting to upload to CPAN?
> >   create table table_history (
> >       id              SERIAL PRIMARY KEY,
> >       person          integer REFERENCES person,
> >       update_time     timestamp(0) with time zone NOT NULL DEFAULT 
> >       CURRENT_TIMESTAMP,
> >       table_name      text NOT NULL,
> >       row_id          text NOT NULL
> >   );
> >
> >   create table column_history (
> >       change_set      integer REFERENCES table_history ON DELETE CASCADE,
> >       column_name     text,
> >       column_value    text,
> >       PRIMARY KEY     (change_set, column_name)
> >   );
> >
> >Which I don't really like because I'm storing table names and column
> >names which just seems dirty.  No referential integrity.
> 
> That is a bit scary, why not just copy the whole row, and compute with a 
> VIEW or something which ones changed?

Copy the entire row?  If I follow the reason is that the above handles
history for a bunch of different tables.  So they don't have the same
columns.

When I want to track a table I just add this to my model class for
that table:

    __PACKAGE__->track_history;

and then triggers are created to write any changed columns to the
history tables.


> 
> >I've thought about using a single table where the table has two
> >"primary" keys -- one a sequence and the other a non-unique name (such
> >as the template path name).

> Could do that too.. I think audit tables and triggers are cleaner in the 
> long run.. The common case, fetching a template, doesnt get bogged down by 
> all the historical data in the same table.

I'm not too worried about that at this point.  I've currently got
about 500 templates.  So if each template had 100 edits which seems
unlikely) that's still a small table.  Plus, the "current" rows can be
found with an index so seems like it would not be much work.  Probably
only need a few revisions back for any row, anyway.

Thanks for the comments!

-- 
Bill Moseley
moseley at hank.org