1 |
#!/usr/bin/perl -w |
2 |
|
3 |
# Helper script to produce alter tables for inherited tables and |
4 |
# indexes from source shema |
5 |
|
6 |
use strict; |
7 |
#use Data::Dumper; |
8 |
|
9 |
my $out; |
10 |
|
11 |
my ($table, $inherit); |
12 |
|
13 |
while (<>) { |
14 |
chomp; |
15 |
|
16 |
if (/create\s+table\s+(\S+)/i) { |
17 |
$table = $1; |
18 |
} |
19 |
|
20 |
next unless ($table); |
21 |
|
22 |
if (/primary\s+key\s*\(\s*(\S+)\s*\)/i ) { |
23 |
$out->{table_pk}->{$table} = $1; |
24 |
} |
25 |
if (/^\s*(\S+)\s*.+?--\s*((?:unique\s+)*index)/i) { |
26 |
$out->{index}->{$2}->{$table} = $1; |
27 |
} |
28 |
|
29 |
if (/\s*inherits\s*\(\s*(\S+)\s*\)/) { |
30 |
$out->{inherits}->{$table} = $1; |
31 |
} |
32 |
|
33 |
} |
34 |
|
35 |
#print STDERR Dumper($out); |
36 |
|
37 |
foreach my $table (keys %{ $out->{inherits} }) { |
38 |
my $parent = $out->{inherits}->{$table} || die; |
39 |
my $pk = $out->{table_pk}->{$parent} || die; |
40 |
my $seq = $parent . '_' . $pk . '_seq'; |
41 |
print qq{alter table $table alter column $pk set default nextval('$seq');\n}; |
42 |
} |
43 |
|
44 |
foreach my $type (keys %{ $out->{index} }) { |
45 |
foreach my $table (keys %{ $out->{index}->{$type} }) { |
46 |
my $f = $out->{index}->{$type}->{$table} || die; |
47 |
my $i = $table . '_' . $f . '_ind'; |
48 |
print qq{create $type $i on $table($f);\n}; |
49 |
} |
50 |
} |
51 |
|
52 |
|