Hi there
I work in Postgres, SQLAlchemy and autoload=True.
I would like to keep historical records on separate tables, with the
historical column values, user id and modification time of the rows.
I've set up triggers on insert/update/delete, and was wondering how to
get hold of the current user id from inside the trigger.
My current schema looks like the following:
CREATE TABLE authenticateduser (
uid VARCHAR(20) PRIMARY KEY REFERENCES users
);
INSERT INTO authenticateduser (uid) VALUES ('n/a');
CREATE TABLE contracts (
cod_contract VARCHAR(5) PRIMARY KEY,
cod_customer VARCHAR(10) REFERENCES customers,
[...more stuff...]
);
CREATE TABLE history_contracts (
event_number SERIAL PRIMARY KEY,
cod_contract VARCHAR(5) NOT NULL REFERENCES contracts,
cod_customer VARCHAR(10) REFERENCES customers,
[...more stuff...]
uid_modified VARCHAR(20) NOT NULL REFERENCES users,
ts_modified TIMESTAMP NOT NULL,
)
CREATE OR REPLACE FUNCTION modify_contract() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO history_contracts ( cod_contract, cod_customer,
[...more stuff...], uid_modified, ts_modified )
VALUES ( NEW.cod_contract, NEW.cod_customer, [...more
stuff...], (SELECT uid FROM authenticateduser), CURRENT_TIMESTAMP );
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER modify_contract
AFTER INSERT OR UPDATE
ON contracts
FOR EACH ROW EXECUTE PROCEDURE modify_contract();
CREATE OR REPLACE FUNCTION delete_contract() RETURNS TRIGGER AS $$
BEGIN
DELETE FROM history_contracts
WHERE cod_contract = OLD.cod_contract;
RETURN OLD;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER delete_contract
BEFORE DELETE
ON contracts
FOR EACH ROW EXECUTE PROCEDURE delete_contract();
This way, every history row gets recorded with uid_modified == 'n/a'.
What I'd like to do, is creating a TEMPORARY table upon initiation of a
web request, and store the user id of the current authenticated user.
The temporary table would have precedence over the one in the schema,
and the trigger should thus read the real user.
Which brings me to the rude patch in sa_rwt:
> [run_with_transaction.when("_use_sa()")]
> def sa_rwt(func, *args, **kw):
> log.debug("New SA transaction")
> eng = get_engine()
-- from here --
> try:
> eng.execute("CREATE TEMPORARY TABLE authenticateduser(uid
> VARCHAR (20)) ON COMMIT PRESERVE ROWS")
> except sqlalchemy.exceptions.SQLError:
> pass
> eng.execute("DELETE FROM authenticateduser")
> if identity.current.user:
> eng.execute("INSERT INTO authenticateduser (uid) VALUES
> ('%s')" % identity.current.user.uid) # don't look at the missing
> bind, it's not the point
> else:
> eng.execute("INSERT INTO authenticateduser (uid) VALUES ('n/a')")
> session.flush()
-- to here --
> transaction = session.create_transaction()
> try:
> retval = func(*args, **kw)
> transaction.commit()
> except (cherrypy.HTTPRedirect,cherrypy.InternalRedirect):
> try:
> transaction.commit()
> except Exception,e:
> retval = dispatch_exception(e,args,kw)
> else:
> raise
> except Exception, e:
> transaction.rollback()
> retval = dispatch_exception(e,args,kw)
> return retval
Within the boundaries of my understanding, this recipe should work...
the PRESERVE ROWS option leaves the table for the whole session,
otherwise it would get destroied a microsecond later with the first commit.
It seems to work, but it's a bit fragile (I cannot reproduce the errors
at the moment) and before digging deeper with the debugger I'd like to
know if the idea is feasible, or if you see any problem and have a
simpler way to do that.
Thank you
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
