/[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 69 by dpavlin, Mon Aug 7 17:30:56 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 47  SQL query which will return names of col Line 47  SQL query which will return names of col
47  index. Have in mind that you can't use aliases (as I<something>) in this SQL  index. Have in mind that you can't use aliases (as I<something>) in this SQL
48  query (or triggers will be created with wrong fields).  query (or triggers will be created with wrong fields).
49    
50    If SQL query isn't specified, default one C<< select * from movies >> will
51    be created. That will be B<serious performance hit> if all columns are
52    not needed for search.
53    
54  =item --pk id  =item --pk id
55    
56  Specify name of primary key column in SQL query. If not specified, C<id> will be used.  Specify name of primary key column in SQL query. If you allready have primary
57    key on table or unique index and it consists of simgle column
58    (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
61    of that item in index (which might be what you want). That's because specified
62    C<pk> column will be used for C<@uri> in Hyper Estraier.
63    
64  =item --user login  =item --user login
65    
# Line 60  C<admin> and C<admin> will be used. Line 70  C<admin> and C<admin> will be used.
70    
71  =item --debug  =item --debug
72    
73  Dump debugging output. It may be specified multiple times for more verbose debugging.  Dump debugging output. It may be specified multiple times for more verbose
74    debugging.
75    
76  =back  =back
77    
# Line 100  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 145  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 170  $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 245  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
284    
285    At end of each run, this script will output example search SQL query on STDOUT.
286    
287    You can use it to quickly construct queries for your application.
288    
289    =cut
290    
291  my $col_names = join(', ', @cols);  my $col_names = join(', ', @cols);
292  my $col_def = join(', ', map { "$_ text" } @cols);  my $col_def = join(', ', map { "$_ text" } @cols);
293    
# Line 270  FROM pgest( Line 307  FROM pgest(
307  ) as ($col_def);  ) as ($col_def);
308    
309  ";  ";
310    
311    __END__
312    
313    =head1 AUTHOR
314    
315    Dobrica Pavlinusic <dpavlin@rot13.org>
316    
317    L<http://www.rot13.org/~dpavlin/>
318    
319    =head1 LICENSE
320    
321    This product is licensed under GNU Public License (GPL) v2 or later.
322    
323    =cut
324    
Legend:
Removed from v.69  
changed lines
  Added in v.78
  ViewVC Help
Powered by ViewVC 1.1.26