Re: Templates in the database

On Thu, 18 Jan 2007, Bill Moseley wrote:

> I currently have most templates (TT) on disk -- and
many I'd consider
> to be an integral part of the application and are thus
under revision
> control with the rest of the application.
>
> But, it would make managing the running application a
bit easier if
> the templates were in the database.  But, I don't
really want to give
> up revision control.
>
> First, I'm curious if many people are serving templates
from the
> database.

I have plans to do that (which went as far as writing 
Template::Provider:BI and
including it in my app), but not actually 
using yet.

> I'm also curious about methods you might use for
revision management
> of the templates (or any content) that are in the
database.

I assume this is some sort of CMS-like app where the users
can edit 
templates/data etc? I hadn't thought of that side of it yet,
but I imagine 
a decent set of auditing tables would do the trick. Give the
templates 
versions, and always show the newest, or always show 1 which
is live, and 
have the older ones renumbered (this is what our work system
does). Or, 
have the tepmlate table itself stay clean, and have a
trigger 
copy-on-update to an audit table (which keeps down the size
of the most 
used table). (hmm, you said that down below, I should read
more)

Our work system very crudely just shows which fields have
changed, but not 
how.. annoying that.. better ways, maybe Test:ifferenc
es, which IIRC has 
a nice display to show them?

>
>
> I currently have "static content" served from
the database -- but I
> haven't overridden Template::Provider, rather I fetch
the template and
> pass it as a scalar ref to TT.  But, all other
templates are
> still served off disk (wrappers, footers, headers,
layout, etc).
>
> I do maintain a history of these pages, and I have a
crude "diff" view
> using Algorithm:iff.  I
have a table that holds the history of the
> content and use triggers to write to it on update.

That sonuds like quite a sane way to do it, and how I
probably would.


Jess

_______________________________________________
List: Catalystlists.rawmode.org
Listinfo: ht
tp://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-
archive.com/catalystlists.rawmode.org/
Dev site: http://dev.catalyst.per
l.org/

On Sat, Jan 20, 2007 at 02:25:39PM +0000, Jess Robinson
wrote:
> I have plans to do that (which went as far as writing 
> Template::Provider:BI and
including it in my app), but not actually 
> using yet.

Can you show the Provider code?  Or did you just use some of
the code
examples posted to the TT list over the years (or perhaps
examples
from Slash)?  Did you use the standard Provider's method of
caching
templates (once fetched from the database)?

Might be handy to have the Provider insert the template into
the
database if not found in the database but found on disk. 
Then can
still use the svn repo as the location for the default
templates
and the database will get initiallized with the templates
automatically.

Any concern for performance with the templates in the
database?
I suspect not since it's very commonly done.  Probably would
be smart
to have a flag somewhere that indicates when any database
template was
last updated that all processes can check -- a single stat
would
likely be faster than hitting the database a few dozen times
to test
for newer versions.


> I assume this is some sort of CMS-like app where the
users can edit 
> templates/data etc?

It's a CMS for the people that run the site -- not for
general users,
although that would be basically the same code.  I'm
thinking it will
be easier to code this myself than teach them how to use
subversion. ;)

> I hadn't thought of that side of it yet, but I imagine

> a decent set of auditing tables would do the trick.
Give the templates 
> versions, and always show the newest, or always show 1
which is live, and 
> have the older ones renumbered (this is what our work
system does). Or, 
> have the tepmlate table itself stay clean, and have a
trigger 
> copy-on-update to an audit table (which keeps down the
size of the most 
> used table). (hmm, you said that down below, I should
read more)

Ya, I've tracked history a number of ways, but never really
sure what
I like best:

For one database object I have its table plus another
history table
that has the same columns and use triggers to copy the data
on update.

Ashley's example stores the diff in the database, where I
just store
previous versions.  I'm not too worried about disk space at
this time.

I also have a bunch of config-type tables where I'd like to
know when
someone changes a column, so in that case I have two
tables:

    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.

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).  Then the trick is finding the
most
current.  I could look for the one with the largest primary
key, or
maybe with the most recent "last_updated"
timestamp.  But, I think I
might have boolean "current" and make UNIQUE(
path_name, current )
so that there can only be one current one.

Something like:

    create table template (
        id              SERIAL PRIMARY KEY,
        path            text,
        current         boolean,  -- true if this is the
current template
        created_time    timestamp(0) with time zone not null
default now(),
        update_by       integer REFERENCES person,
        CONSTRAINT      one_current_template UNIQUE( path,
current )
    );
    create index template_path ON template ( path );
    create index template_current ON template ( current );

And maybe a "current_template" view class and
override update/insert
to always insert into the base table.

And never update rather always insert changes.

-- 
Bill Moseley
moseleyhank.org


_______________________________________________
List: Catalystlists.rawmode.org
Listinfo: ht
tp://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-
archive.com/catalystlists.rawmode.org/
Dev site: http://dev.catalyst.per
l.org/