/[pgestraier]/trunk/bin/pgest-index.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/bin/pgest-index.pl

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

revision 70 by dpavlin, Mon Aug 7 21:08:41 2006 UTC revision 78 by dpavlin, Tue Aug 8 11:40:09 2006 UTC
# Line 15  pgest-index.pl - create full-text index Line 15  pgest-index.pl - create full-text index
15  =cut  =cut
16    
17  my $c = {  my $c = {
18          debug => 1,          debug => 0,
19  };  };
20    
21  =head1 SYNOPSIS  =head1 SYNOPSIS
# Line 54  not needed for search. Line 54  not needed for search.
54  =item --pk id  =item --pk id
55    
56  Specify name of primary key column in SQL query. If you allready have primary  Specify name of primary key column in SQL query. If you allready have primary
57  key on table, and it consists of simgle column (compaund keys are not supported)  key on table or unique index and it consists of simgle column
58  it will be picked up automatically.  (compaund keys are not supported) it will be picked up automatically.
59    
60  If you specify value which is not unique, you will get just last occurence  If you specify value which is not unique, you will get just last occurence
61  of that item in index (which might be what you want). That's because specified  of that item in index (which might be what you want). That's because specified
# Line 111  my $dbh = DBI->connect("DBI:$c->{dbi}"," Line 111  my $dbh = DBI->connect("DBI:$c->{dbi}","
111  sub drop_triggers {  sub drop_triggers {
112          my $table = shift || die "no table?";          my $table = shift || die "no table?";
113    
114          warn "removing triggers from $table\n";          my $sth = $dbh->prepare(qq{
115                    SELECT relname,tgname
116          foreach my $t (qw/UPDATE INSERT DELETE/) {                  FROM pg_trigger JOIN pg_class ON relfilenode = tgrelid
117                  my $lc_t = lc($t);                  WHERE tgname LIKE 'pgest_trigger_%' AND relname = ?
118                  $dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });          }) || $dbh->errstr;
119    
120            $sth->execute( $table ) || $sth->errstr();
121    
122            warn "there are ", $sth->rows, " triggers instead of just 3, dropping all\n" if ($sth->rows != 3);
123    
124            while (my $row = $sth->fetchrow_hashref) {
125                    my $sql = sprintf(qq{ DROP TRIGGER %s ON %s }, $row->{tgname}, $row->{relname} );
126                    #warn "# $sql\n";
127                    $dbh->do( $sql ) || $dbh->errstr;
128          }          }
129    
130            warn "removed ", $sth->rows, " triggers from $table\n" if ($sth->rows);
131    
132  }  }
133    
134  if ($c->{drop}) {  if ($c->{drop}) {
# Line 156  if (! $c->{pk}) { Line 168  if (! $c->{pk}) {
168    
169          warn "# finding primary key for $table\n" if ($c->{debug});          warn "# finding primary key for $table\n" if ($c->{debug});
170    
171          $c->{pk} = $dbh->selectrow_array(qq{          my $index_fmt = qq{
172                  SELECT                  SELECT
173                          a.attname, t.typname                          a.attname, t.typname
174                  FROM pg_type t, pg_attribute a                  FROM pg_type t, pg_attribute a
175                  WHERE t.oid = a.atttypid AND attrelid = (                  WHERE t.oid = a.atttypid AND attrelid = (
176                          SELECT indexrelid                          SELECT indexrelid
177                          FROM pg_class c, pg_index i                          FROM pg_class c, pg_index i
178                          WHERE c.relname = '$table'                          WHERE c.relname = '%s'
179                                  AND c.oid = i.indrelid                                  AND c.oid = i.indrelid
180                                  AND indisprimary                                  AND %s
181                                  AND indnatts = 1                                  AND indnatts = 1
182                  )                  )
183          });          };
184    
185            $c->{pk} = $dbh->selectrow_array( sprintf($index_fmt, $table, 'indisprimary') );
186            
187            $c->{pk} ||= $dbh->selectrow_array( sprintf($index_fmt, $table, 'indisunique') );
188    
189  }  }
190    
# Line 181  $dbh->begin_work; Line 197  $dbh->begin_work;
197  $c->{sql} ||= "select * from $table";  $c->{sql} ||= "select * from $table";
198    
199  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();
200  $sth->execute() || die $sth->errstr();  $sth->execute() || die $sth->errstr;
201    
202  my @cols = @{ $sth->{NAME} };  my @cols = @{ $sth->{NAME} };
203    
# Line 256  foreach my $t (qw/UPDATE INSERT DELETE/) Line 272  foreach my $t (qw/UPDATE INSERT DELETE/)
272    
273          #warn "$sql\n";          #warn "$sql\n";
274    
275          $dbh->do( $sql ) || die $dbh->errstr();          $dbh->do( $sql ) || die $dbh->errstr;
276    
277  }  }
278    
279    warn "created consistency triggers\n";
280    
281  $dbh->commit;  $dbh->commit;
282    
283  =head1 SEARCHING  =head1 SEARCHING
Legend:
Removed from v.70  
changed lines
  Added in v.78
  ViewVC Help
Powered by ViewVC 1.1.26