/[gnumed]/gnumed/gnumed/server/utils/audittrail.py
ViewVC logotype

Contents of /gnumed/gnumed/server/utils/audittrail.py

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.2 - (show annotations) (download) (as text)
Wed Sep 4 13:13:39 2002 UTC (21 years, 8 months ago) by hherb
Branch: MAIN
Changes since 1.1: +6 -5 lines
File MIME type: text/x-python
hardcoded tablenames removed

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)

savannah-hackers-public@gnu.org
ViewVC Help
Powered by ViewVC 1.1.26