/[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 69 by dpavlin, Mon Aug 7 17:30:56 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 72  Dump debugging output. It may be specifi Line 66  Dump debugging output. It may be specifi
66    
67  =cut  =cut
68    
69    my $usage = "$0 database_name (--create|--drop) name [--sql='select id,foo,bar from table'] [--pk=id]\n";
70    
71  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+/);
72    
73    my $dbname = shift @ARGV || die $usage;
74    
75    $c->{dbi} = 'Pg:dbname=' . $dbname;
76    
77  warn "# c: ", Dumper($c) if ($c->{debug});  warn "# c: ", Dumper($c) if ($c->{debug});
78    
79  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;
80    
81  $c->{node_url} = 'http://localhost:1978/node/' . $table;  $c->{node_url} = 'http://localhost:1978/node/' . $table;
82    
# Line 141  CREATE OR REPLACE FUNCTION pgest_trigger Line 141  CREATE OR REPLACE FUNCTION pgest_trigger
141    
142  drop_triggers( $table );  drop_triggers( $table );
143    
144    if (! $c->{pk}) {
145    
146            warn "# finding primary key for $table\n" if ($c->{debug});
147    
148            $c->{pk} = $dbh->selectrow_array(qq{
149                    SELECT
150                            a.attname, t.typname
151                    FROM pg_type t, pg_attribute a
152                    WHERE t.oid = a.atttypid AND attrelid = (
153                            SELECT indexrelid
154                            FROM pg_class c, pg_index i
155                            WHERE c.relname = '$table'
156                                    AND c.oid = i.indrelid
157                                    AND indisprimary
158                                    AND indnatts = 1
159                    )
160            });
161    
162    }
163    
164    die "$0: can't find single column primary key for table ${table}. Please specify column with --pk\n" unless ($c->{pk});
165    
166    warn "using primary key $c->{pk}\n";
167    
168  $dbh->begin_work;  $dbh->begin_work;
169    
170    $c->{sql} ||= "select * from $table";
171    
172  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();
173  $sth->execute() || die $sth->errstr();  $sth->execute() || die $sth->errstr();
174    
175  my @cols = @{ $sth->{NAME} };  my @cols = @{ $sth->{NAME} };
176    
177    die "SQL '$c->{sql}' didn't include primary key $c->{pk}\n" unless grep(/^\Q$c->{pk}\E$/, @cols);
178    
179  warn "# columns: ",join(",", @cols),"\n" if ($c->{debug});  warn "# columns: ",join(",", @cols),"\n" if ($c->{debug});
180    
181  my $total = $sth->rows;  my $total = $sth->rows;
# Line 156  my $i = 1; Line 184  my $i = 1;
184  my $t = time();  my $t = time();
185  my $pk = $c->{pk} || 'id';  my $pk = $c->{pk} || 'id';
186    
187    warn "indexing existing ",$sth->rows," rows\n";
188    
189  while (my $row = $sth->fetchrow_hashref() ) {  while (my $row = $sth->fetchrow_hashref() ) {
190    
191          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 199  while (my $row = $sth->fetchrow_hashref(
199                  die "can't find pk column '$pk' in results\n";                  die "can't find pk column '$pk' in results\n";
200          }          }
201    
202          printf "%4d ",$i;          my $log = sprintf "%4d ",$i;
203    
204          while (my ($col,$val) = each %{$row}) {          while (my ($col,$val) = each %{$row}) {
205    
# Line 180  while (my $row = $sth->fetchrow_hashref( Line 210  while (my $row = $sth->fetchrow_hashref(
210                          # add body text to document (make it searchable using full-text index)                          # add body text to document (make it searchable using full-text index)
211                          $doc->add_text($val);                          $doc->add_text($val);
212    
213                          print "R";                          $log .= "R";
214                  } else {                  } else {
215                          print ".";                          $log .= ".";
216                  }                  }
217    
218          }          }
# Line 191  while (my $row = $sth->fetchrow_hashref( Line 221  while (my $row = $sth->fetchrow_hashref(
221    
222          die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });          die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });
223    
224          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) ) );
225    
226            print STDERR $log;
227    
228  }  }
229    
# Line 223  my $col_names = join(', ', @cols); Line 255  my $col_names = join(', ', @cols);
255  my $col_def = join(', ', map { "$_ text" } @cols);  my $col_def = join(', ', map { "$_ text" } @cols);
256    
257  print "  print "
258  ## example SQL search query:  -- example SQL search query:
259    
260  SELECT $col_names  SELECT $col_names
261  FROM pgest(  FROM pgest(
Legend:
Removed from v.67  
changed lines
  Added in v.69
  ViewVC Help
Powered by ViewVC 1.1.26