--- trunk/sql/mkindex.pl	2005/07/23 15:46:24	24
+++ trunk/sql/mkindex.pl	2005/08/02 15:20:44	37
@@ -1,15 +1,19 @@
 #!/usr/bin/perl -w
 
 # Helper script to produce alter tables for inherited tables and
-# indexes from source shema
+# indexes from source shema like this:
+#
+# ./mkindex schema.sql | psql database_name
 
 use strict;
-#use Data::Dumper;
+use Data::Dumper;
 
 my $out;
 
 my ($table, $inherit);
 
+print "begin;\n";
+
 while (<>) {
 	chomp;
 
@@ -26,17 +30,25 @@
 		$out->{index}->{$2}->{$table} = $1;
 	}
 
-	if (/\s*inherits\s*\(\s*(\S+)\s*\)/) {
+	if (/\s*inherits\s*\(\s*(\S+)\s*\)/i) {
 		$out->{inherits}->{$table} = $1;
 	}
 
+	if (s/^\s*(\S+)(.+?)references\s+(\S+)\s*\((\S+)\)([^,]*)([,\s]*)$/\t$1$2$6/i) {
+#	if (/^\s*(\S+)(.+?)references\s+(\S+)\s*\((\S+)\)/) {
+		@{ $out->{references}->{$table}->{$1} } = ( $3, $4, $5 );
+	}
+
+	print "$_\n";
+	print STDERR "# $_\n";
+
 }
 
-#print STDERR Dumper($out);
+print STDERR Dumper($out);
 
 foreach my $table (keys %{ $out->{inherits} }) {
-	my $parent = $out->{inherits}->{$table} || die;
-	my $pk = $out->{table_pk}->{$parent} || die;
+	my $parent = $out->{inherits}->{$table} || die "$table doesn't inherit anything";
+	my $pk = $out->{table_pk}->{$parent} || die "$parent doesn't have primary key";
 	my $seq = $parent . '_' . $pk . '_seq';
 	print qq{alter table $table alter column $pk set default nextval('$seq');\n};
 }
@@ -49,4 +61,25 @@
 	}
 }
 
-	
+foreach my $table (keys %{ $out->{references} }) {
+	foreach my $field (keys %{ $out->{references}->{$table} }) {
+		my $fk = $out->{references}->{$table}->{$field} || die;
+		my $func = $table . '_' . $field . '_fkey';
+		print qq{
+create or replace function $func() returns TRIGGER AS
+\$\$
+DECLARE
+BEGIN
+IF NEW.$field IN (select $fk->[1] from $fk->[0]) THEN
+	RETURN NEW;
+ELSE
+	RAISE EXCEPTION 'insert or update on table "%" violates foreign key constraint for "$table" table', TG_RELNAME;
+END IF;
+END;
+\$\$ language 'plpgsql';
+CREATE TRIGGER $func BEFORE INSERT ON $table FOR EACH ROW EXECUTE PROCEDURE $func();
+};
+	}
+}
+
+print "commit;\n";