1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
#!/usr/bin/env perl
#
# make_audit_ddl
#
# Perl script reads idb ddl from cvs module directories and
# for each table produces the corresponding audit table named
# tablename_audit.
#
# columns of type 'serial' are changed to 'int'
# primary key declarations are removed
# foreign key declarations are removed
# unique column declarations are removed
# additional columns added to each table:
# transaction_date timestamp not null
# transaction_type char not null
# all priveleges granted to public
#
#
#---------------------------------------------------------------------------#
#---------------------------------------------------------------------------#
#
## General setup
#
$cvsdir = "/Users/emmert/cvs/scratch/idb/flydb/";
#$cvsdir = "../";
## open the master module list
$modlist = $cvsdir . "idb-full.modules";
open(mlist,$modlist);
while (<mlist>) {
chop($_) if $_ =~ /\n$/;
## now for each module open the .sql file for parsing
$sqlfile = $cvsdir . $_;
# print "\nopening $sqlfile...\n";
open(sfile,$sqlfile);
## for each table we add _audit to the table name, get rid of foreign
## keys, and set up grant statement.
##
## printing here is a little fiddly to handle correct placement of commas
## in create table statements...
while (<sfile>) {
if ( ($_ !~ /^#/) && ($_ !~ /^\s*$/) && ($_ !~ /foreign key/) && ($_ !~ /unique/) && ($_ !~ /primary key/) ) {
chop($_) if $_ =~ /\n$/;
chop($_) if $_ =~ /\,$/;
$_ =~ s/serial/int/;
if ($_ =~ /(^.*create table) (.*) \(/) {
$tabst = 0;
$_ = $1 . " " . $2 . "_audit (";
$tname = $2 . "_audit";
print "$_";
}
elsif ($_ =~ /\)\;/) {
print ",\n\ttransaction_date timestamp not null,";
print "\n\ttransaction_type char not null";
print "\n$_\n";
print "GRANT ALL on $tname to PUBLIC;\n\n";
}
elsif ($tabst == 0) {
print "\n$_";
$tabst++;
}
## when create view statements appear we can assume that the create table
## statements are all handled...
elsif ($_ =~ /create view/) {
close(sfile);
}
else {
if ($_ =~ /timeentered|timelastmod/) {
$_ =~ s/ default current_timestamp//;
}
print ",\n$_";
$tabst++;
}
}
}
}
|
