/[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 62 by dpavlin, Mon Aug 7 13:24:49 2006 UTC revision 66 by dpavlin, Mon Aug 7 14:56:08 2006 UTC
# Line 1  Line 1 
1  #!/usr/bin/perl -w  #!/usr/bin/perl -w
2    
3  use strict;  use strict;
4  use Search::Estraier 0.06;  use Search::Estraier 0.07;
5  use DBI;  use DBI;
6  use Data::Dumper;  use Data::Dumper;
7  use Encode qw/from_to/;  use Encode qw/from_to/;
# Line 22  my $c = { Line 22  my $c = {
22                  select ime,tel from imenik                  select ime,tel from imenik
23          },          },
24          pk_col => 'tel',          pk_col => 'tel',
         db_encoding => 'iso-8859-2',  
25          debug => 1,          debug => 1,
26          user => 'admin',          user => 'admin',
27          passwd => 'admin',          passwd => 'admin',
28  };  };
29    
30  GetOptions($c, qw/node_url=s sql=s pk_col=s eb_encoding=s debug+ user=s passwd=s/);  GetOptions($c, qw/node_url=s sql=s pk_col=s debug+ user=s passwd=s/);
31    
32  warn "# c: ", Dumper($c) if ($c->{debug});  warn "# c: ", Dumper($c) if ($c->{debug});
33    
34    my $table = $c->{name} || die "no name?";
35    
36  # create and configure node  # create and configure node
37  my $node = new Search::Estraier::Node(  my $node = new Search::Estraier::Node(
38          url => $c->{node_url},          url => $c->{node_url},
# Line 42  my $node = new Search::Estraier::Node( Line 43  my $node = new Search::Estraier::Node(
43          debug => $c->{debug} >= 4 ? 1 : 0,          debug => $c->{debug} >= 4 ? 1 : 0,
44  );  );
45    
46    $node->master(
47            action => 'nodeclr',
48            name => $table,
49    );
50    
51  # create DBI connection  # create DBI connection
52  my $dbh = DBI->connect("DBI:$c->{dbi}","","") || die $DBI::errstr;  my $dbh = DBI->connect("DBI:$c->{dbi}","","") || die $DBI::errstr;
53    
54    # create PostgreSQL functions
55    $dbh->do(qq{
56    
57    CREATE OR REPLACE FUNCTION pgest(text, text, text, int, text, text, text, int, int, text[])
58            RETURNS setof record
59            AS 'pgest','pgest_node'
60            LANGUAGE 'C' IMMUTABLE CALLED ON NULL INPUT;
61    
62    CREATE OR REPLACE FUNCTION pgest_trigger() RETURNS TRIGGER
63            AS 'pgest', 'pgest_trigger'
64            LANGUAGE 'C' STRICT;
65    
66    }) || die $dbh->errstr();
67    
68    
69    # drop existing triggers
70    foreach my $t (qw/UPDATE INSERT DELETE/) {
71            my $lc_t = lc($t);
72            $dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });
73    }
74    
75    $dbh->begin_work;
76    
77  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();  my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();
78  $sth->execute() || die $sth->errstr();  $sth->execute() || die $sth->errstr();
79    
# Line 76  while (my $row = $sth->fetchrow_hashref( Line 105  while (my $row = $sth->fetchrow_hashref(
105          while (my ($col,$val) = each %{$row}) {          while (my ($col,$val) = each %{$row}) {
106    
107                  if ($val) {                  if ($val) {
                         # change encoding?  
                         from_to($val, ($c->{db_encoding} || 'ISO-8859-1'), 'UTF-8');  
   
108                          # add attributes (make column usable from attribute search)                          # add attributes (make column usable from attribute search)
109                          $doc->add_attr($col, $val);                          $doc->add_attr($col, $val);
110    
# Line 100  while (my $row = $sth->fetchrow_hashref( Line 126  while (my $row = $sth->fetchrow_hashref(
126    
127  }  }
128    
129  my $table = $c->{name} || die "no name?";  my $cols = "'" . join("', '", @cols) . "'";
   
 my $cols = "'" . join("','", @cols) . "'";  
130    
131  foreach my $t (qw/UPDATE INSERT DELETE/) {  foreach my $t (qw/UPDATE INSERT DELETE/) {
132    
133          my $lc_t = lc($t);          my $lc_t = lc($t);
134    
         $dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });  
   
135          my $sql = qq{          my $sql = qq{
136    
137                  CREATE TRIGGER pgest_trigger_${lc_t} AFTER ${t}                  CREATE TRIGGER pgest_trigger_${lc_t} AFTER ${t}
# Line 120  foreach my $t (qw/UPDATE INSERT DELETE/) Line 142  foreach my $t (qw/UPDATE INSERT DELETE/)
142    
143          };          };
144    
145          warn "$sql\n";          #warn "$sql\n";
146    
147          $dbh->do( $sql ) || die $dbh->errstr();          $dbh->do( $sql ) || die $dbh->errstr();
148    
149  }  }
150    
151    $dbh->commit;
152    
153    my $col_names = join(', ', @cols);
154    my $col_def = join(', ', map { "$_ text" } @cols);
155    
156    print "
157    ## example SQL search query:
158    
159    SELECT $col_names
160    FROM pgest(
161            -- node, login, passwd, depth
162            '$c->{node_url}', '$c->{user}', '$c->{passwd}', 0,
163            -- full text search
164            'foo bar',
165            -- attribute filter, order, limit, offset
166            null, null, null, null,
167            -- return columns
168            array[$cols]
169    ) as ($col_def);
170    
171    ";
Legend:
Removed from v.62  
changed lines
  Added in v.66
  ViewVC Help
Powered by ViewVC 1.1.26