/[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 68 by dpavlin, Mon Aug 7 17:05:07 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 184  my $i = 1; Line 211  my $i = 1;
211  my $t = time();  my $t = time();
212  my $pk = $c->{pk} || 'id';  my $pk = $c->{pk} || 'id';
213    
214    warn "indexing existing ",$sth->rows," rows\n";
215    
216  while (my $row = $sth->fetchrow_hashref() ) {  while (my $row = $sth->fetchrow_hashref() ) {
217    
218          warn "# row: ",Dumper($row) if ($c->{debug} >= 3);          warn "# row: ",Dumper($row) if ($c->{debug} >= 3);
# Line 197  while (my $row = $sth->fetchrow_hashref( Line 226  while (my $row = $sth->fetchrow_hashref(
226                  die "can't find pk column '$pk' in results\n";                  die "can't find pk column '$pk' in results\n";
227          }          }
228    
229          printf "%4d ",$i;          my $log = sprintf "%4d ",$i;
230    
231          while (my ($col,$val) = each %{$row}) {          while (my ($col,$val) = each %{$row}) {
232    
# Line 208  while (my $row = $sth->fetchrow_hashref( Line 237  while (my $row = $sth->fetchrow_hashref(
237                          # add body text to document (make it searchable using full-text index)                          # add body text to document (make it searchable using full-text index)
238                          $doc->add_text($val);                          $doc->add_text($val);
239    
240                          print "R";                          $log .= "R";
241                  } else {                  } else {
242                          print ".";                          $log .= ".";
243                  }                  }
244    
245          }          }
# Line 219  while (my $row = $sth->fetchrow_hashref( Line 248  while (my $row = $sth->fetchrow_hashref(
248    
249          die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });          die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });
250    
251          printf (" %d%% %.1f/s\n", int(( $i++ / $total) * 100), ( $i / (time() - $t) ) );          $log .= sprintf(" %d%% %.1f/s\r", int(( $i++ / $total) * 100), ( $i / (time() - $t) ) );
252    
253            print STDERR $log;
254    
255  }  }
256    
# Line 241  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    
294  print "  print "
295  ## example SQL search query:  -- example SQL search query:
296    
297  SELECT $col_names  SELECT $col_names
298  FROM pgest(  FROM pgest(
# Line 266  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.68  
changed lines
  Added in v.78
  ViewVC Help
Powered by ViewVC 1.1.26