To illustrate the benefits of this approach, consider the following example:
UserTable(
User,
{
{fId, text,},
{fName, text,},
{pRole, integer,},
{pGroup, integer,},
{fOk, integer,1}},
{
{fId}})
This is automatically expanded via our macro library into the maintenance script listed at the bottom of this message. Please excuse the obvious mistakes. We are still debugging and have only been using PostgreSQL a couple of months and m4 for a couple of days. Any advice or recount of your own experiences would be appreciated.
About Us: we are an IT startup based in Tasmania. It is our mission to reengineer corporate database systems for the web using strictly open source technology and state of the art methodologies. Naturally the bulk of what we develop will eventually be released as open source as well, once we get our business model sorted out. (My partner and I bet our houses and our careers on PostgreSQL and this little venture. Any advice on that would be welcome too. <grin>)
Cheers,
Andrew Smith
---
/* rebuild user table tUsers */
ALTER TABLE tUsers RENAME TO tOldUsers;
DROP SEQUENCE nUser;
CREATE SEQUENCE nUser;
CREATE TABLE tUsers
(
kUser INTEGER NOT NULL DEFAULT NEXTVAL('nUser'),
fId TEXT NOT NULL,
fName TEXT NOT NULL,
pRole INTEGER NOT NULL,
pGroup INTEGER NOT NULL,
fOk INTEGER NOT NULL DEFAULT 1
);
GRANT ALL ON tUsers TO PUBLIC;
BEGIN;
INSERT INTO tUsers (kUser,fId,fName,pRole,pGroup,fOk)
SELECT kUser,fId,fName,pRole,pGroup,fOk FROM
tOldUsers;
SELECT SETVAL('nUser',(SELECT MAX(kUser) FROM tUsers)) FROM tUsers
LIMIT 1;
COMMIT;
DROP TABLE tOldUsers;
CREATE INDEX tUsers0 ON tUsers(kUser);
CREATE INDEX tUsers1 ON tUsers(fId);
/* rebuild user history table hUsers */
ALTER TABLE hUsers RENAME TO hOldUsers;
DROP SEQUENCE gUser;
CREATE SEQUENCE gUser;
CREATE TABLE hUsers
(
jUser INTEGER NOT NULL DEFAULT NEXTVAL('gUser'),
hUser INTEGER NOT NULL,
hAction CHAR NOT NULL,
hWhen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
kUser INTEGER NOT NULL,
fId TEXT NOT NULL,
fName TEXT NOT NULL,
pRole INTEGER NOT NULL,
pGroup INTEGER NOT NULL,
fOk INTEGER NOT NULL DEFAULT 1
);
GRANT ALL ON hUsers TO PUBLIC;
BEGIN;
INSERT INTO hUsers (jUser,hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT jUser,hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk
FROM hOldUsers;
SELECT SETVAL('gUser',(SELECT MAX(jUser) FROM hUsers)) FROM hUsers
LIMIT 1;
COMMIT;
DROP TABLE hOldUsers;
CREATE INDEX hUsers0 ON hUsers(jUser);
CREATE INDEX hUsers1 ON hUsers(kUser);
SELECT SETVAL('nUser',(SELECT MAX(kUser) FROM hUsers)) FROM hUsers LIMIT 1;
/* create rules for history table maintenance */
VACUUM ANALYZE pg_proc; DROP RULE rUsersU0;
VACUUM ANALYZE pg_proc; DROP RULE rUsersD0;
VACUUM ANALYZE pg_proc; DROP RULE rUsersI0;
VACUUM ANALYZE pg_proc; DROP VIEW vUsers;
VACUUM ANALYZE pg_proc;
CREATE VIEW vUsers AS SELECT a.fId AS hUser,b.kUser,b.fId,b.fName,b.pRole,b.pGroup,b.fOk
FROM tUsers a,tUsers b;
GRANT ALL ON vUsers TO PUBLIC;
VACUUM ANALYZE pg_proc;
CREATE RULE rUsersI0 AS ON INSERT TO vUsers DO INSTEAD
(
INSERT INTO tUsers(fId,fName,pRole,pGroup,fOk)
VALUES (new.fId,new.fName,new.pRole,new.pGroup,new.fOk);
INSERT INTO hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT a.kUser,'i',CURRENT_TIMESTAMP,(SELECT
last_value FROM nUser),new.fId,new.fName,new.pRole,new.pGroup,new.fOk
FROM tUsers a WHERE a.fId
= new.hUser;
);
CREATE RULE rUsersD0 AS ON DELETE TO vUsers DO INSTEAD
(
INSERT INTO hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT a.kUser,'d',CURRENT_TIMESTAMP,old.kUser,old.fId,old.fName,old.pRole,old.pGroup,old.fOk
FROM tUsers a WHERE a.fId
= old.hUser;
DELETE FROM tUsers WHERE kUser = old.kUser;
);
CREATE RULE rUsersU0 AS ON UPDATE TO vUsers DO INSTEAD
(
INSERT INTO hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT a.kUser,'u',CURRENT_TIMESTAMP,new.kUser,new.fId,new.fName,new.pRole,new.pGroup,new.fOk
FROM tUsers a WHERE a.fId
= new.hUser;
UPDATE tUsers SET
fId = new.fId,
fName = new.fName,
pRole = new.pRole,
pGroup = new.pGroup,
fOk = new.fOk
WHERE kUser = old.kUser;
);
