m4 macros plus PostgreSQL anyone? - Mailing list pgsql-sql

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