/[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 66 by dpavlin, Mon Aug 7 14:56:08 2006 UTC revision 67 by dpavlin, Mon Aug 7 16:38:24 2006 UTC
# Line 16  pgest-index.pl - create full-text index Line 16  pgest-index.pl - create full-text index
16    
17  my $c = {  my $c = {
18          name => 'imenik',          name => 'imenik',
         node_url => 'http://localhost:1978/node/imenik',  
19          dbi => 'Pg:dbname=vip',          dbi => 'Pg:dbname=vip',
20          sql => qq{          sql => qq{
21                  select ime,tel from imenik                  select ime,tel from imenik
22          },          },
23          pk_col => 'tel',          pk => 'tel',
24          debug => 1,          debug => 1,
         user => 'admin',  
         passwd => 'admin',  
25  };  };
26    
27  GetOptions($c, qw/node_url=s sql=s pk_col=s debug+ user=s passwd=s/);  =head1 SYNOPSIS
28    
29      pgest-index.pl --create movies --sql "select id,title,year from movies"
30    
31      pgsql-index.pl --drop movies
32    
33    Options:
34    
35    =over 4
36    
37    =item --create name
38    
39    Create index C<name> and create triggers on table with same name
40    
41    =item --drop name
42    
43    Remove triggers from table C<name> and node with same name
44    
45    =item --node-url http://localhost:1978/node/name
46    
47    Full URI to node. If it's not specified, it's assumed that you are using
48    Hyper Estraier on C<http://localhost:1978/>.
49    
50    =item --sql "select col1,col2 from name"
51    
52    SQL query which will return names of columns which are included in full-text
53    index. Have in mind that you can't use aliases (as I<something>) in this SQL
54    query (or triggers will be created with wrong fields).
55    
56    =item --pk id
57    
58    Specify name of primary key column in SQL query. If not specified, C<id> will be used.
59    
60    =item --user login
61    
62    =item --passwd password
63    
64    Username and password to use when connecting to Hyper Estraier. If not specified,
65    C<admin> and C<admin> will be used.
66    
67    =item --debug
68    
69    Dump debugging output. It may be specified multiple times for more verbose debugging.
70    
71    =back
72    
73    =cut
74    
75    GetOptions($c, qw/create=s drop=s node_url=s sql=s pk=s user=s passwd=s debug+/);
76    
77  warn "# c: ", Dumper($c) if ($c->{debug});  warn "# c: ", Dumper($c) if ($c->{debug});
78    
79  my $table = $c->{name} || die "no name?";  my $table = $c->{create} || $c->{drop} || die "$0 (--create|--drop) name [--sql='select id,foo,bar from table']\n";
80    
81    $c->{node_url} = 'http://localhost:1978/node/' . $table;
82    
83    $c->{user} ||= 'admin';
84    $c->{passwd} ||= 'admin';
85    
86  # create and configure node  # create and configure node
87  my $node = new Search::Estraier::Node(  my $node = new Search::Estraier::Node(
# Line 43  my $node = new Search::Estraier::Node( Line 93  my $node = new Search::Estraier::Node(
93          debug => $c->{debug} >= 4 ? 1 : 0,          debug => $c->{debug} >= 4 ? 1 : 0,
94  );  );
95    
96    # create DBI connection
97    my $dbh = DBI->connect("DBI:$c->{dbi}","","") || die $DBI::errstr;
98    
99    # drop existing triggers
100    sub drop_triggers {
101            my $table = shift || die "no table?";
102    
103            warn "removing triggers from $table\n";
104    
105            foreach my $t (qw/UPDATE INSERT DELETE/) {
106                    my $lc_t = lc($t);
107                    $dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });
108            }
109    }
110    
111    if ($c->{drop}) {
112            drop_triggers( $table );
113            warn "removing node $table\n";
114            $node->master(
115                    action => 'nodedel',
116                    name => $table,
117            );
118            exit;
119    }
120    
121    # clear existing node
122  $node->master(  $node->master(
123          action => 'nodeclr',          action => 'nodeclr',
124          name => $table,          name => $table,
125  );  );
126    
 # create DBI connection  
 my $dbh = DBI->connect("DBI:$c->{dbi}","","") || die $DBI::errstr;  
   
127  # create PostgreSQL functions  # create PostgreSQL functions
128  $dbh->do(qq{  $dbh->do(qq{
129    
# Line 66  CREATE OR REPLACE FUNCTION pgest_trigger Line 139  CREATE OR REPLACE FUNCTION pgest_trigger
139  }) || die $dbh->errstr();  }) || die $dbh->errstr();
140    
141    
142  # drop existing triggers  drop_triggers( $table );
 foreach my $t (qw/UPDATE INSERT DELETE/) {  
         my $lc_t = lc($t);  
         $dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });  
 }  
143    
144  $dbh->begin_work;  $dbh->begin_work;
145    
# Line 85  my $total = $sth->rows; Line 154  my $total = $sth->rows;
154  my $i = 1;  my $i = 1;
155    
156  my $t = time();  my $t = time();
157  my $pk_col = $c->{pk_col} || 'id';  my $pk = $c->{pk} || 'id';
158    
159  while (my $row = $sth->fetchrow_hashref() ) {  while (my $row = $sth->fetchrow_hashref() ) {
160    
# Line 94  while (my $row = $sth->fetchrow_hashref( Line 163  while (my $row = $sth->fetchrow_hashref(
163          # create document          # create document
164          my $doc = new Search::Estraier::Document;          my $doc = new Search::Estraier::Document;
165    
166          if (my $id = $row->{$pk_col}) {          if (my $id = $row->{$pk}) {
167                  $doc->add_attr('@uri', $id);                  $doc->add_attr('@uri', $id);
168          } else {          } else {
169                  die "can't find pk_col column '$pk_col' in results\n";                  die "can't find pk column '$pk' in results\n";
170          }          }
171    
172          printf "%4d ",$i;          printf "%4d ",$i;
# Line 137  foreach my $t (qw/UPDATE INSERT DELETE/) Line 206  foreach my $t (qw/UPDATE INSERT DELETE/)
206                  CREATE TRIGGER pgest_trigger_${lc_t} AFTER ${t}                  CREATE TRIGGER pgest_trigger_${lc_t} AFTER ${t}
207                          ON ${table} FOR EACH ROW                          ON ${table} FOR EACH ROW
208                          EXECUTE PROCEDURE pgest_trigger('$c->{node_url}','$c->{user}','$c->{passwd}',                          EXECUTE PROCEDURE pgest_trigger('$c->{node_url}','$c->{user}','$c->{passwd}',
209                                  '$c->{pk_col}', $cols                                  '$c->{pk}', $cols
210                          )                          )
211    
212          };          };
Legend:
Removed from v.66  
changed lines
  Added in v.67
  ViewVC Help
Powered by ViewVC 1.1.26