1 |
#!/usr/bin/python |
2 |
#!/usr/bin/python |
3 |
############################################################################# |
4 |
# |
5 |
# auditrail.py - automatic generation of audit trail triggers for |
6 |
# any gnumed database. |
7 |
# Principle: any table that needs to be audited (all modifications |
8 |
# logged) must inherit a given parent table |
9 |
# This script finds all descendant of that parent table and |
10 |
# creates all neccessary tables and trigger functions |
11 |
# neccessary to establish the audit trail |
12 |
# --------------------------------------------------------------------------- |
13 |
# |
14 |
# @author: Dr. Horst Herb |
15 |
# @copyright: author |
16 |
# @license: GPL (details at http://www.gnu.org) |
17 |
# @dependencies: nil |
18 |
# @change log: |
19 |
# 12.07.2001 hherb first draft, untested |
20 |
# |
21 |
# @TODO: Almost everything |
22 |
############################################################################ |
23 |
"""Module that allows to create "audit trail" tables, triggers and functions |
24 |
""" |
25 |
|
26 |
import pg |
27 |
|
28 |
#this query returns all tables inherited from a specified table in a tuple list |
29 |
query_table_descendants = """select relname, oid from pg_class where \ |
30 |
pg_class.oid in (select inhrelid from pg_inherits where inhparent = \ |
31 |
(select oid from pg_class where relname = '%s'));""" |
32 |
|
33 |
#this query returns all attributes AND their data types in a tuple list |
34 |
query_table_attributes = """SELECT a.attname, format_type(a.atttypid, a.atttypmod) \ |
35 |
FROM pg_class c, pg_attribute a |
36 |
WHERE c.relname = '%s' |
37 |
AND a.attnum > 0 AND a.attrelid = c.oid |
38 |
ORDER BY a.attnum;""" |
39 |
|
40 |
trigger_statement = """CREATE TRIGGER %s BEFORE UPDATE OR DELETE ON %s \n \ |
41 |
FOR EACH ROW EXECUTE PROCEDURE %s;""" |
42 |
|
43 |
|
44 |
|
45 |
def o(s): |
46 |
print s |
47 |
|
48 |
#================================================================= |
49 |
|
50 |
def get_children(db, parent_table): |
51 |
"""Returns all descendants of "parent_table" in a tuple list |
52 |
Iterate through the list and access individual table names by index 0""" |
53 |
|
54 |
return db.query(query_table_descendants % parent_table).getresult() |
55 |
|
56 |
#================================================================= |
57 |
|
58 |
def get_attributes(db, child_table): |
59 |
return db.query(query_table_attributes % child_table).getresult() |
60 |
|
61 |
#================================================================= |
62 |
|
63 |
def create_audit_table(tablename): |
64 |
o("DROP TABLE %s;\n" % tablename) |
65 |
o("CREATE TABLE %s () INHERITS(audit_object);" % tablename) |
66 |
|
67 |
#================================================================= |
68 |
|
69 |
def create_trigfunc(db, child_table, audit_prefix="audit_"): |
70 |
tablename = child_table[0] |
71 |
funcname = audit_prefix + tablename |
72 |
trigname = 'tr_' + funcname |
73 |
auditname = tablename + '_hx' |
74 |
|
75 |
#check first whether the audit table exists, and create it if not |
76 |
if db.query("SELECT oid FROM pg_class where relname = '%s'" % auditname).ntuples() == 0: |
77 |
create_audit_table(auditname) |
78 |
|
79 |
o("DROP FUNCTION %s();\n" % funcname) |
80 |
|
81 |
o("CREATE FUNCTION %s() RETURNS OPAQUE AS '" % funcname) |
82 |
o("BEGIN") |
83 |
o("NEW.updated := OLD.updated+1;") |
84 |
o("INSERT INTO %s VALUES (" % auditname) |
85 |
attributes = get_attributes(db, tablename) |
86 |
for attr in attributes: |
87 |
o("OLD." + attr[0]) |
88 |
o(");") |
89 |
o("return NEW;") |
90 |
o("END' LANGUAGE 'plpgsql';\n") |
91 |
|
92 |
o("DROP TRIGGER %s ON %s:\n" % (trigname, funcname)) |
93 |
o(trigger_statement % (trigname, tablename, funcname)+'\n') |
94 |
|
95 |
|
96 |
#================================================================= |
97 |
|
98 |
|
99 |
if __name__ == "__main__" : |
100 |
|
101 |
dbname = raw_input("name of database:") |
102 |
parenttable = raw_input("name of parent table:") |
103 |
|
104 |
#connect to the database backend |
105 |
db = pg.connect(dbname) |
106 |
|
107 |
#get a list of all tables derived from gnumed_object |
108 |
children = get_children(db, parenttable) |
109 |
|
110 |
#for each derived table |
111 |
for child in children: |
112 |
create_trigfunc(db, child) |