trunk/sql/mkindex.pl

#!/usr/bin/perl -w

# Helper script to produce alter tables for inherited tables and
# indexes from source shema

use strict;
#use Data::Dumper;

my $out;

my ($table, $inherit);

while (<>) {
        chomp;

        if (/create\s+table\s+(\S+)/i) {
                $table = $1;
        }

        next unless ($table);

        if (/primary\s+key\s*\(\s*(\S+)\s*\)/i ) {
                $out->{table_pk}->{$table} = $1;
        }
        if (/^\s*(\S+)\s*.+?--\s*((?:unique\s+)*index)/i) {
                $out->{index}->{$2}->{$table} = $1;
        }

        if (/\s*inherits\s*\(\s*(\S+)\s*\)/) {
                $out->{inherits}->{$table} = $1;
        }

}

#print STDERR Dumper($out);

foreach my $table (keys %{ $out->{inherits} }) {
        my $parent = $out->{inherits}->{$table} || die;
        my $pk = $out->{table_pk}->{$parent} || die;
        my $seq = $parent . '_' . $pk . '_seq';
        print qq{alter table $table alter column $pk set default nextval('$seq');\n};
}

foreach my $type (keys %{ $out->{index} }) {
        foreach my $table (keys %{ $out->{index}->{$type} }) {
                my $f = $out->{index}->{$type}->{$table} || die;
                my $i = $table . '_' . $f . '_ind';
                print qq{create $type $i on $table($f);\n};
        }
}

        
1	dpavlin	24	#!/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 (/\sinherits\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