Has anyone used the macro processor m4 to generate PostgreSQL (or any other
sql) code automatically from a data dictionary? We have made a good start
on this, but would like to avoid reinventing any wheels, or wasting time
down blind alleys.
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. )
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;
);
