[Catalyst] Templates in the database

[Jess Robinson]

On Sat, 20 Jan 2007, Bill Moseley wrote:

> 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?

Yes, it's on CPAN, I uploaded it as I wrote that last email ;)

>>>   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.

I figured that, so why not an audit table for each of the tables you're 
tracking columns in? Hmm, why no integrity? Can't you reference the 
databases internal table catalog?

> 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.

Nice, is that a DBIx::Class component or something?

>
>>
>>> 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!

Anytime, it's interesting stuff!

Jess