/[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 82 by dpavlin, Sun Nov 26 21:21:46 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    If name of primary key begins with C<_> it will not be added into text
65    indexing (so you won't be able to find prmary key value, but it will still
66    be available as attribute value).
67    
68  =item --user login  =item --user login
69    
# Line 60  C<admin> and C<admin> will be used. Line 74  C<admin> and C<admin> will be used.
74    
75  =item --debug  =item --debug
76    
77  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
78    debugging.
79    
80  =back  =back
81    
# Line 100  my $dbh = DBI->connect("DBI:$c->{dbi}"," Line 115  my $dbh = DBI->connect("DBI:$c->{dbi}","
115  sub drop_triggers {  sub drop_triggers {
116          my $table = shift || die "no table?";          my $table = shift || die "no table?";
117    
118          warn "removing triggers from $table\n";          my $sth = $dbh->prepare(qq{
119                    SELECT relname,tgname
120          foreach my $t (qw/UPDATE INSERT DELETE/) {                  FROM pg_trigger JOIN pg_class ON relfilenode = tgrelid
121                  my $lc_t = lc($t);                  WHERE tgname LIKE 'pgest_trigger_%' AND relname = ?
122                  $dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });          }) || $dbh->errstr;
123    
124            $sth->execute( $table ) || $sth->errstr();
125    
126            warn "there are ", $sth->rows, " triggers instead of just 3, dropping all\n" if ($sth->rows != 3);
127    
128            while (my $row = $sth->fetchrow_hashref) {
129                    my $sql = sprintf(qq{ DROP TRIGGER %s ON %s }, $row->{tgname}, $row->{relname} );
130                    #warn "# $sql\n";
131                    $dbh->do( $sql ) || $dbh->errstr;
132          }          }
133    
134            warn "removed ", $sth->rows, " triggers from $table\n" if ($sth->rows);
135    
136  }  }
137    
138  if ($c->{drop}) {  if ($c->{drop}) {
# Line 145  if (! $c->{pk}) { Line 172  if (! $c->{pk}) {
172    
173          warn "# finding primary key for $table\n" if ($c->{debug});          warn "# finding primary key for $table\n" if ($c->{debug});
174    
175          $c->{pk} = $dbh->selectrow_array(qq{          my $index_fmt = qq{
176                  SELECT                  SELECT
177                          a.attname, t.typname                          a.attname, t.typname
178                  FROM pg_type t, pg_attribute a                  FROM pg_type t, pg_attribute a
179                  WHERE t.oid = a.atttypid AND attrelid = (                  WHERE t.oid = a.atttypid AND attrelid = (
180                          SELECT indexrelid                          SELECT indexrelid
181                          FROM pg_class c, pg_index i                          FROM pg_class c, pg_index i
182                          WHERE c.relname = '$table'                          WHERE c.relname = '%s'
183                                  AND c.oid = i.indrelid                                  AND c.oid = i.indrelid
184                                  AND indisprimary                                  AND %s
185                                  AND indnatts = 1                                  AND indnatts = 1
186                  )                  )
187          });          };
188    
189            $c->{pk} = $dbh->selectrow_array( sprintf($index_fmt, $table, 'indisprimary') );
190            
191            $c->{pk} ||= $dbh->selectrow_array( sprintf($index_fmt, $table, 'indisunique') );
192    
193  }  }
194    
195  die "$0: can't find single column primary key for table ${table}. Please specify column with --pk\n" unless ($c->{pk});  die "$0: can't find single column primary key for table ${table}. Please specify column with --pk\n" unless ($c->{pk});
196    
197  warn "using primary key $c->{pk}\n";  warn "using primary key $c->{pk}", $c->{pk} =~ m/^_/ ? " (not indexed)" : "", "\n";
198    
199  $dbh->begin_work;  $dbh->begin_work;
200    
201  $c->{sql} ||= "select * from $table";  $c->{sql} ||= "select * from $table";
202    
203  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();
204  $sth->execute() || die $sth->errstr();  $sth->execute() || die $sth->errstr;
205    
206  my @cols = @{ $sth->{NAME} };  my @cols = @{ $sth->{NAME} };
207    
# Line 184  my $i = 1; Line 215  my $i = 1;
215  my $t = time();  my $t = time();
216  my $pk = $c->{pk} || 'id';  my $pk = $c->{pk} || 'id';
217    
218    warn "indexing existing ",$sth->rows," rows\n";
219    
220  while (my $row = $sth->fetchrow_hashref() ) {  while (my $row = $sth->fetchrow_hashref() ) {
221    
222          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 230  while (my $row = $sth->fetchrow_hashref(
230                  die "can't find pk column '$pk' in results\n";                  die "can't find pk column '$pk' in results\n";
231          }          }
232    
233          printf "%4d ",$i;          my $log = sprintf "%4d ",$i;
234    
235          while (my ($col,$val) = each %{$row}) {          while (my ($col,$val) = each %{$row}) {
236    
# Line 206  while (my $row = $sth->fetchrow_hashref( Line 239  while (my $row = $sth->fetchrow_hashref(
239                          $doc->add_attr($col, $val);                          $doc->add_attr($col, $val);
240    
241                          # add body text to document (make it searchable using full-text index)                          # add body text to document (make it searchable using full-text index)
242                          $doc->add_text($val);                          $doc->add_text($val) unless ($col =~ m/^_/);
243    
244                          print "R";                          $log .= "R";
245                  } else {                  } else {
246                          print ".";                          $log .= ".";
247                  }                  }
248    
249          }          }
# Line 219  while (my $row = $sth->fetchrow_hashref( Line 252  while (my $row = $sth->fetchrow_hashref(
252    
253          die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });          die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });
254    
255          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) ) );
256    
257            print STDERR $log;
258    
259  }  }
260    
# Line 241  foreach my $t (qw/UPDATE INSERT DELETE/) Line 276  foreach my $t (qw/UPDATE INSERT DELETE/)
276    
277          #warn "$sql\n";          #warn "$sql\n";
278    
279          $dbh->do( $sql ) || die $dbh->errstr();          $dbh->do( $sql ) || die $dbh->errstr;
280    
281  }  }
282    
283    warn "created consistency triggers\n";
284    
285  $dbh->commit;  $dbh->commit;
286    
287    =head1 SEARCHING
288    
289    At end of each run, this script will output example search SQL query on STDOUT.
290    
291    You can use it to quickly construct queries for your application.
292    
293    =cut
294    
295  my $col_names = join(', ', @cols);  my $col_names = join(', ', @cols);
296  my $col_def = join(', ', map { "$_ text" } @cols);  my $col_def = join(', ', map { "$_ text" } @cols);
297    
298  print "  print "
299  ## example SQL search query:  -- example SQL search query:
300    
301  SELECT $col_names  SELECT $col_names
302  FROM pgest(  FROM pgest(
# Line 266  FROM pgest( Line 311  FROM pgest(
311  ) as ($col_def);  ) as ($col_def);
312    
313  ";  ";
314    
315    __END__
316    
317    =head1 AUTHOR
318    
319    Dobrica Pavlinusic <dpavlin@rot13.org>
320    
321    L<http://www.rot13.org/~dpavlin/>
322    
323    =head1 LICENSE
324    
325    This product is licensed under GNU Public License (GPL) v2 or later.
326    
327    =cut
328    
Legend:
Removed from v.68  
changed lines
  Added in v.82
  ViewVC Help
Powered by ViewVC 1.1.26