1 |
#!/usr/bin/perl -w |
2 |
|
3 |
# Helper script to produce alter tables for inherited tables and |
4 |
# indexes from source shema like this: |
5 |
# |
6 |
# ./mkindex schema.sql | psql database_name |
7 |
|
8 |
use strict; |
9 |
use Data::Dumper; |
10 |
|
11 |
my $out; |
12 |
|
13 |
my ($table, $inherit); |
14 |
|
15 |
print "begin;\n"; |
16 |
|
17 |
while (<>) { |
18 |
chomp; |
19 |
|
20 |
if (/create\s+table\s+(\S+)/i) { |
21 |
$table = $1; |
22 |
} |
23 |
|
24 |
next unless ($table); |
25 |
|
26 |
if (/primary\s+key\s*\(\s*(\S+)\s*\)/i ) { |
27 |
$out->{table_pk}->{$table} = $1; |
28 |
} |
29 |
if (/^\s*(\S+)\s*.+?--\s*((?:unique\s+)*index)/i) { |
30 |
$out->{index}->{$2}->{$table} = $1; |
31 |
} |
32 |
|
33 |
if (/\s*inherits\s*\(\s*(\S+)\s*\)/i) { |
34 |
$out->{inherits}->{$table} = $1; |
35 |
} |
36 |
|
37 |
if (s/^\s*(\S+)(.+?)references\s+(\S+)\s*\((\S+)\)/\t$1$2/i) { |
38 |
# if (/^\s*(\S+)(.+?)references\s+(\S+)\s*\((\S+)\)/) { |
39 |
@{ $out->{references}->{$table}->{$1} } = ( $3, $4 ); |
40 |
} |
41 |
|
42 |
print "$_\n"; |
43 |
print STDERR "# $_\n"; |
44 |
|
45 |
} |
46 |
|
47 |
print STDERR Dumper($out); |
48 |
|
49 |
foreach my $table (keys %{ $out->{inherits} }) { |
50 |
my $parent = $out->{inherits}->{$table} || die; |
51 |
my $pk = $out->{table_pk}->{$parent} || die; |
52 |
my $seq = $parent . '_' . $pk . '_seq'; |
53 |
print qq{alter table $table alter column $pk set default nextval('$seq');\n}; |
54 |
} |
55 |
|
56 |
foreach my $type (keys %{ $out->{index} }) { |
57 |
foreach my $table (keys %{ $out->{index}->{$type} }) { |
58 |
my $f = $out->{index}->{$type}->{$table} || die; |
59 |
my $i = $table . '_' . $f . '_ind'; |
60 |
print qq{create $type $i on $table($f);\n}; |
61 |
} |
62 |
} |
63 |
|
64 |
foreach my $table (keys %{ $out->{references} }) { |
65 |
foreach my $field (keys %{ $out->{references}->{$table} }) { |
66 |
my $fk = $out->{references}->{$table}->{$field} || die; |
67 |
my $func = $table . '_' . $field . '_fkey'; |
68 |
print qq{ |
69 |
create or replace function $func() returns TRIGGER AS |
70 |
\$\$ |
71 |
DECLARE |
72 |
BEGIN |
73 |
IF NEW.$field IN (select $fk->[1] from $fk->[0]) THEN |
74 |
RETURN NEW; |
75 |
ELSE |
76 |
RAISE EXCEPTION 'insert or update on table "%" violates foreign key constraint for "$table" table', TG_RELNAME; |
77 |
END IF; |
78 |
END; |
79 |
\$\$ language 'plpgsql'; |
80 |
CREATE TRIGGER $func BEFORE INSERT ON $table FOR EACH ROW EXECUTE PROCEDURE $func(); |
81 |
}; |
82 |
} |
83 |
} |
84 |
|
85 |
print "commit;\n"; |