pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: andrew <TheDog@TheSoftwareSmith.Com.Au>
To: pgsql-sql@postgresql.org
Subject: m4 macros plus PostgreSQL anyone?
Date: Mon, 10 Jul 2000 12:48:16 +1000
Message-ID: <3969396F.385DAE7A@TheSoftwareSmith.Com.Au> (raw)

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. <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;
);

view thread (2+ messages)  latest in thread

Message-ID: <3969396F.385DAE7A@TheSoftwareSmith.Com.Au>
Permalink:  ../3969396F.385DAE7A@TheSoftwareSmith.Com.Au/
Also on:    postgresql.org/message-id/3969396F.385DAE7A@TheSoftwareSmith.Com.Au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: TheDog@TheSoftwareSmith.Com.Au
  Subject: Re: m4 macros plus PostgreSQL anyone?
  In-Reply-To: <3969396F.385DAE7A@TheSoftwareSmith.Com.Au>
  References: <3969396F.385DAE7A@TheSoftwareSmith.Com.Au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox