pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
m4 macros plus PostgreSQL anyone?
2+ messages / 2 participants
[nested] [flat]

* m4 macros plus PostgreSQL anyone?
@ 2000-07-10 02:48 andrew <TheDog@TheSoftwareSmith.Com.Au>
  0 siblings, 0 replies; 2+ messages in thread

From: andrew @ 2000-07-10 02:48 UTC (permalink / raw)
  To: 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 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;
);

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: m4 macros plus PostgreSQL anyone?
@ 2000-07-10 04:10 K Parker <kparker@eudoramail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: K Parker @ 2000-07-10 04:10 UTC (permalink / raw)
  To: pgsql-sql@hub.org

No advice, just a big question: why M4?

I've made great use of M4 myself for gluing a constant- and macro-defining capability to languages that don't have such (even Foxpro--no, no, please don't ask!) but in this day and age perl would be far more expressive and capable for the things you are trying to do, wouldn't it?  



Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-07-10 04:10 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-10 02:48 m4 macros plus PostgreSQL anyone? andrew <TheDog@TheSoftwareSmith.Com.Au>
2000-07-10 04:10 Re: m4 macros plus PostgreSQL anyone? K Parker <kparker@eudoramail.com>

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