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::DBI 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 [EMAIL PROTECTED] _______________________________________________ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/