implemented core of catalog-entry-item schema based on PostgreSQL General Bits

[webpac2] / sql / mkindex.pl

diff --git a/sql/mkindex.pl b/sql/mkindex.pl
new file mode 100755 (executable)
index 0000000..8872492
--- /dev/null
+++ b/sql/mkindex.pl
@@ -0,0 +1,52 @@
+#!/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};
+       }
+}
+
+