/[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 67 by dpavlin, Mon Aug 7 16:38:24 2006 UTC revision 70 by dpavlin, Mon Aug 7 21:08:41 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 = {
         name => 'imenik',  
         dbi => 'Pg:dbname=vip',  
         sql => qq{  
                 select ime,tel from imenik  
         },  
         pk => 'tel',  
18          debug => 1,          debug => 1,
19  };  };
20    
# Line 53  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, and it consists of simgle column (compaund keys are not supported)
58    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 66  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    
78  =cut  =cut
79    
80    my $usage = "$0 database_name (--create|--drop) name [--sql='select id,foo,bar from table'] [--pk=id]\n";
81    
82  GetOptions($c, qw/create=s drop=s node_url=s sql=s pk=s user=s passwd=s debug+/);  GetOptions($c, qw/create=s drop=s node_url=s sql=s pk=s user=s passwd=s debug+/);
83    
84    my $dbname = shift @ARGV || die $usage;
85    
86    $c->{dbi} = 'Pg:dbname=' . $dbname;
87    
88  warn "# c: ", Dumper($c) if ($c->{debug});  warn "# c: ", Dumper($c) if ($c->{debug});
89    
90  my $table = $c->{create} || $c->{drop} || die "$0 (--create|--drop) name [--sql='select id,foo,bar from table']\n";  my $table = $c->{create} || $c->{drop} || die $usage;
91    
92  $c->{node_url} = 'http://localhost:1978/node/' . $table;  $c->{node_url} = 'http://localhost:1978/node/' . $table;
93    
# Line 141  CREATE OR REPLACE FUNCTION pgest_trigger Line 152  CREATE OR REPLACE FUNCTION pgest_trigger
152    
153  drop_triggers( $table );  drop_triggers( $table );
154    
155    if (! $c->{pk}) {
156    
157            warn "# finding primary key for $table\n" if ($c->{debug});
158    
159            $c->{pk} = $dbh->selectrow_array(qq{
160                    SELECT
161                            a.attname, t.typname
162                    FROM pg_type t, pg_attribute a
163                    WHERE t.oid = a.atttypid AND attrelid = (
164                            SELECT indexrelid
165                            FROM pg_class c, pg_index i
166                            WHERE c.relname = '$table'
167                                    AND c.oid = i.indrelid
168                                    AND indisprimary
169                                    AND indnatts = 1
170                    )
171            });
172    
173    }
174    
175    die "$0: can't find single column primary key for table ${table}. Please specify column with --pk\n" unless ($c->{pk});
176    
177    warn "using primary key $c->{pk}\n";
178    
179  $dbh->begin_work;  $dbh->begin_work;
180    
181    $c->{sql} ||= "select * from $table";
182    
183  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();
184  $sth->execute() || die $sth->errstr();  $sth->execute() || die $sth->errstr();
185    
186  my @cols = @{ $sth->{NAME} };  my @cols = @{ $sth->{NAME} };
187    
188    die "SQL '$c->{sql}' didn't include primary key $c->{pk}\n" unless grep(/^\Q$c->{pk}\E$/, @cols);
189    
190  warn "# columns: ",join(",", @cols),"\n" if ($c->{debug});  warn "# columns: ",join(",", @cols),"\n" if ($c->{debug});
191    
192  my $total = $sth->rows;  my $total = $sth->rows;
# Line 156  my $i = 1; Line 195  my $i = 1;
195  my $t = time();  my $t = time();
196  my $pk = $c->{pk} || 'id';  my $pk = $c->{pk} || 'id';
197    
198    warn "indexing existing ",$sth->rows," rows\n";
199    
200  while (my $row = $sth->fetchrow_hashref() ) {  while (my $row = $sth->fetchrow_hashref() ) {
201    
202          warn "# row: ",Dumper($row) if ($c->{debug} >= 3);          warn "# row: ",Dumper($row) if ($c->{debug} >= 3);
# Line 169  while (my $row = $sth->fetchrow_hashref( Line 210  while (my $row = $sth->fetchrow_hashref(
210                  die "can't find pk column '$pk' in results\n";                  die "can't find pk column '$pk' in results\n";
211          }          }
212    
213          printf "%4d ",$i;          my $log = sprintf "%4d ",$i;
214    
215          while (my ($col,$val) = each %{$row}) {          while (my ($col,$val) = each %{$row}) {
216    
# Line 180  while (my $row = $sth->fetchrow_hashref( Line 221  while (my $row = $sth->fetchrow_hashref(
221                          # add body text to document (make it searchable using full-text index)                          # add body text to document (make it searchable using full-text index)
222                          $doc->add_text($val);                          $doc->add_text($val);
223    
224                          print "R";                          $log .= "R";
225                  } else {                  } else {
226                          print ".";                          $log .= ".";
227                  }                  }
228    
229          }          }
# Line 191  while (my $row = $sth->fetchrow_hashref( Line 232  while (my $row = $sth->fetchrow_hashref(
232    
233          die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });          die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });
234    
235          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) ) );
236    
237            print STDERR $log;
238    
239  }  }
240    
# Line 219  foreach my $t (qw/UPDATE INSERT DELETE/) Line 262  foreach my $t (qw/UPDATE INSERT DELETE/)
262    
263  $dbh->commit;  $dbh->commit;
264    
265    =head1 SEARCHING
266    
267    At end of each run, this script will output example search SQL query on STDOUT.
268    
269    You can use it to quickly construct queries for your application.
270    
271    =cut
272    
273  my $col_names = join(', ', @cols);  my $col_names = join(', ', @cols);
274  my $col_def = join(', ', map { "$_ text" } @cols);  my $col_def = join(', ', map { "$_ text" } @cols);
275    
276  print "  print "
277  ## example SQL search query:  -- example SQL search query:
278    
279  SELECT $col_names  SELECT $col_names
280  FROM pgest(  FROM pgest(
# Line 238  FROM pgest( Line 289  FROM pgest(
289  ) as ($col_def);  ) as ($col_def);
290    
291  ";  ";
292    
293    __END__
294    
295    =head1 AUTHOR
296    
297    Dobrica Pavlinusic <dpavlin@rot13.org>
298    
299    L<http://www.rot13.org/~dpavlin/>
300    
301    =head1 LICENSE
302    
303    This product is licensed under GNU Public License (GPL) v2 or later.
304    
305    =cut
306    
Legend:
Removed from v.67  
changed lines
  Added in v.70
  ViewVC Help
Powered by ViewVC 1.1.26