/[webpac2]/trunk/sql/mkindex.pl
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /trunk/sql/mkindex.pl

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 24 by dpavlin, Sat Jul 23 15:46:24 2005 UTC revision 25 by dpavlin, Sat Jul 23 18:21:01 2005 UTC
# Line 1  Line 1 
1  #!/usr/bin/perl -w  #!/usr/bin/perl -w
2    
3  # Helper script to produce alter tables for inherited tables and  # Helper script to produce alter tables for inherited tables and
4  # indexes from source shema  # indexes from source shema like this:
5    #
6    # ./mkindex schema.sql | psql database_name
7    
8  use strict;  use strict;
9  #use Data::Dumper;  use Data::Dumper;
10    
11  my $out;  my $out;
12    
13  my ($table, $inherit);  my ($table, $inherit);
14    
15    print "begin;\n";
16    
17  while (<>) {  while (<>) {
18          chomp;          chomp;
19    
# Line 30  while (<>) { Line 34  while (<>) {
34                  $out->{inherits}->{$table} = $1;                  $out->{inherits}->{$table} = $1;
35          }          }
36    
37            if (s/^\s*(\S+)(.+?)references\s+(\S+)\s*\((\S+)\)/$1$2/i) {
38                    @{ $out->{references}->{$table}->{$1} } = ( $3, $4 );
39            }
40    
41            print "$_\n";
42            print STDERR "# $_\n";
43    
44  }  }
45    
46  #print STDERR Dumper($out);  print STDERR Dumper($out);
47    
48  foreach my $table (keys %{ $out->{inherits} }) {  foreach my $table (keys %{ $out->{inherits} }) {
49          my $parent = $out->{inherits}->{$table} || die;          my $parent = $out->{inherits}->{$table} || die;
# Line 49  foreach my $type (keys %{ $out->{index} Line 60  foreach my $type (keys %{ $out->{index}
60          }          }
61  }  }
62    
63            foreach my $table (keys %{ $out->{references} }) {
64            foreach my $field (keys %{ $out->{references}->{$table} }) {
65                    my $fk = $out->{references}->{$table}->{$field} || die;
66                    my $func = $table . '_' . $field . '_fkey';
67                    print qq{
68    create or replace function $func() returns TRIGGER AS
69    \$\$
70    DECLARE
71    BEGIN
72    IF NEW.$field IN (select $fk->[1] from $fk->[0]) THEN
73            RETURN NEW;
74    ELSE
75            RAISE EXCEPTION 'insert or update on table "%" violates foreign key constraint for "$table" table', TG_RELNAME;
76    END IF;
77    END;
78    \$\$ language 'plpgsql';
79    };
80            }
81    }
82    
83    print "commit;\n";
Legend:
Removed from v.24  
changed lines
  Added in v.25
  ViewVC Help
Powered by ViewVC 1.1.26