On Wed, Jul 13, 2005 at 05:23:06PM -0500, Jeffrey Melloy wrote:
> I'm currently looking at implementing a system almost exactly like this,
> and I was wondering if there is anything around that does this.
Attached find how GNUmed does it based on recent discussion
here on the list.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
-- =============================================
-- project: GNUmed
-- $Source: /cvsroot/gnumed/gnumed/gnumed/server/sql/gmSchemaRevision.sql,v $
-- $Id$
-- license: GPL
-- author: Karsten.Hilbert@xxxxxxx
-- =============================================
-- import this file into any database you create and
-- add the revision of your schema files into the revision table,
-- this will allow for a simplistic manual database schema revision control,
-- that may come in handy when debugging live production databases,
-- TODO: add log_script_insertion(text, text, boolean)
-- for your convenience, just copy/paste the following lines:
-- (don't worry about the filename/revision that's in there, it will
-- be replaced automagically with the proper data by "cvs commit")
-- do simple schema revision tracking
-- INSERT INTO gm_schema_revision (filename, version, is_core) VALUES('$RCSfile: gmSchemaRevision.sql,v $', '$Revision: 1.12 $', True/False);
-- =============================================
-- force terminate + exit(3) on errors if non-interactive
\set ON_ERROR_STOP 1
-- ---------------------------------------------
create table gm_schema_revision (
pk serial primary key,
filename text
not null,
version text
not null,
is_core boolean
not null,
imported timestamp with time zone
not null
DEFAULT CURRENT_TIMESTAMP,
unique (filename, version)
);
-- ---------------------------------------------
create table gm_database_revision (
pk serial primary key,
single_row_enforcer boolean
unique
check (single_row_enforcer is True),
identity_hash text
not null
);
-- ---------------------------------------------
create function calc_db_identity_hash() returns string as '
declare
_row record;
_total text;
begin
total := '''';
for _row in (select filename, version from gm_schema_revision where is_core order by filename, version) loop
_total := total || select _row.filename || _row.version;
end loop;
select into _tmp select md5(_total);
return _tmp;
end;' language 'plpgsql';
-- =============================================
GRANT SELECT on
gm_schema_revision
, gm_database_revision
TO group "gm-public";
-- =============================================
-- $Log: gmSchemaRevision.sql,v $
-- Revision 1.12 2005/03/01 20:38:19 ncq
-- - varchar -> text
--
-- Revision 1.11 2003/06/10 08:56:59 ncq
-- - schema_revision -> gm_schema_revision
--
-- Revision 1.10 2003/05/12 12:43:39 ncq
-- - gmI18N, gmServices and gmSchemaRevision are imported globally at the
-- database level now, don't include them in individual schema file anymore
--
-- Revision 1.9 2003/01/20 09:15:30 ncq
-- - unique (file, version)
--
-- Revision 1.8 2003/01/17 00:41:33 ncq
-- - grant select rights to all
--
-- Revision 1.7 2003/01/02 01:25:23 ncq
-- - GnuMed internal tables should be named gm_*
--
-- Revision 1.6 2002/12/01 13:53:09 ncq
-- - missing ; at end of schema tracking line
--
-- Revision 1.5 2002/11/17 08:24:55 ncq
-- - store timestamp not just date
--
-- Revision 1.4 2002/11/17 08:22:44 ncq
-- - forgot DEFAULT
--
-- Revision 1.3 2002/11/17 08:20:15 ncq
-- - added timestamp field
--
-- Revision 1.2 2002/11/16 00:25:59 ncq
-- - added some clarification
--
-- Revision 1.1 2002/11/16 00:23:20 ncq
-- - provisions for simple database schema revision tracking
-- - read the source for instructions
--
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
