trunk/bin/pgest-index.pl

#!/usr/bin/perl -w

use strict;
use Search::Estraier 0.06;
use DBI;
use Data::Dumper;
use Encode qw/from_to/;
use Time::HiRes qw/time/;
use Getopt::Long;

=head1 NAME

pgest-index.pl - create full-text index of some columns in your database

=cut

my $c = {
        name => 'imenik',
        node_url => 'http://localhost:1978/node/imenik',
        dbi => 'Pg:dbname=vip',
        sql => qq{
                select ime,tel from imenik
        },
        pk_col => 'tel',
        db_encoding => 'iso-8859-2',
        debug => 1,
        user => 'admin',
        passwd => 'admin',
};

GetOptions($c, qw/node_url=s sql=s pk_col=s eb_encoding=s debug+ user=s passwd=s/);

warn "# c: ", Dumper($c) if ($c->{debug});

# create and configure node
my $node = new Search::Estraier::Node(
        url => $c->{node_url},
        user => $c->{user},
        passwd => $c->{passwd},
        croak_on_error => 1,
        create => 1,
        debug => $c->{debug} >= 4 ? 1 : 0,
);

# create DBI connection
my $dbh = DBI->connect("DBI:$c->{dbi}","","") || die $DBI::errstr;

my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();
$sth->execute() || die $sth->errstr();

my @cols = @{ $sth->{NAME} };

warn "# columns: ",join(",", @cols),"\n" if ($c->{debug});

my $total = $sth->rows;
my $i = 1;

my $t = time();
my $pk_col = $c->{pk_col} || 'id';

while (my $row = $sth->fetchrow_hashref() ) {

        warn "# row: ",Dumper($row) if ($c->{debug} >= 3);

        # create document
        my $doc = new Search::Estraier::Document;

        if (my $id = $row->{$pk_col}) {
                $doc->add_attr('@uri', $id);
        } else {
                die "can't find pk_col column '$pk_col' in results\n";
        }

        printf "%4d ",$i;

        while (my ($col,$val) = each %{$row}) {

                if ($val) {
                        # change encoding?
                        from_to($val, ($c->{db_encoding} || 'ISO-8859-1'), 'UTF-8');

                        # add attributes (make column usable from attribute search)
                        $doc->add_attr($col, $val);

                        # add body text to document (make it searchable using full-text index)
                        $doc->add_text($val);

                        print "R";
                } else {
                        print ".";
                }

        }

        warn "# doc draft: ",$doc->dump_draft, "\n" if ($c->{debug} >= 2);

        die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });

        printf (" %d%% %.1f/s\n", int(( $i++ / $total) * 100), ( $i / (time() - $t) ) );

}

my $table = $c->{name} || die "no name?";

my $cols = "'" . join("','", @cols) . "'";

foreach my $t (qw/UPDATE INSERT DELETE/) {

        my $lc_t = lc($t);

        $dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });

        my $sql = qq{

                CREATE TRIGGER pgest_trigger_${lc_t} AFTER ${t}
                        ON ${table} FOR EACH ROW
                        EXECUTE PROCEDURE pgest_trigger('$c->{node_url}','$c->{user}','$c->{passwd}',
                                '$c->{pk_col}', $cols
                        )

        };

        warn "$sql\n";

        $dbh->do( $sql ) || die $dbh->errstr();

}
1	dpavlin	62	#!/usr/bin/perl -w
2
3			use strict;
4			use Search::Estraier 0.06;
5			use DBI;
6			use Data::Dumper;
7			use Encode qw/from_to/;
8			use Time::HiRes qw/time/;
9			use Getopt::Long;
10
11			=head1 NAME
12
13			pgest-index.pl - create full-text index of some columns in your database
14
15			=cut
16
17			my $c = {
18			name => 'imenik',
19			node_url => 'http://localhost:1978/node/imenik',
20			dbi => 'Pg:dbname=vip',
21			sql => qq{
22			select ime,tel from imenik
23			},
24			pk_col => 'tel',
25			db_encoding => 'iso-8859-2',
26			debug => 1,
27			user => 'admin',
28			passwd => 'admin',
29			};
30
31			GetOptions($c, qw/node_url=s sql=s pk_col=s eb_encoding=s debug+ user=s passwd=s/);
32
33			warn "# c: ", Dumper($c) if ($c->{debug});
34
35			# create and configure node
36			my $node = new Search::Estraier::Node(
37			url => $c->{node_url},
38			user => $c->{user},
39			passwd => $c->{passwd},
40			croak_on_error => 1,
41			create => 1,
42			debug => $c->{debug} >= 4 ? 1 : 0,
43			);
44
45			# create DBI connection
46			my $dbh = DBI->connect("DBI:$c->{dbi}","","") \|\| die $DBI::errstr;
47
48			my $sth = $dbh->prepare($c->{sql}) \|\| die $dbh->errstr();
49			$sth->execute() \|\| die $sth->errstr();
50
51			my @cols = @{ $sth->{NAME} };
52
53			warn "# columns: ",join(",", @cols),"\n" if ($c->{debug});
54
55			my $total = $sth->rows;
56			my $i = 1;
57
58			my $t = time();
59			my $pk_col = $c->{pk_col} \|\| 'id';
60
61			while (my $row = $sth->fetchrow_hashref() ) {
62
63			warn "# row: ",Dumper($row) if ($c->{debug} >= 3);
64
65			# create document
66			my $doc = new Search::Estraier::Document;
67
68			if (my $id = $row->{$pk_col}) {
69			$doc->add_attr('@uri', $id);
70			} else {
71			die "can't find pk_col column '$pk_col' in results\n";
72			}
73
74			printf "%4d ",$i;
75
76			while (my ($col,$val) = each %{$row}) {
77
78			if ($val) {
79			# change encoding?
80			from_to($val, ($c->{db_encoding} \|\| 'ISO-8859-1'), 'UTF-8');
81
82			# add attributes (make column usable from attribute search)
83			$doc->add_attr($col, $val);
84
85			# add body text to document (make it searchable using full-text index)
86			$doc->add_text($val);
87
88			print "R";
89			} else {
90			print ".";
91			}
92
93			}
94
95			warn "# doc draft: ",$doc->dump_draft, "\n" if ($c->{debug} >= 2);
96
97			die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });
98
99			printf (" %d%% %.1f/s\n", int(( $i++ / $total) * 100), ( $i / (time() - $t) ) );
100
101			}
102
103			my $table = $c->{name} \|\| die "no name?";
104
105			my $cols = "'" . join("','", @cols) . "'";
106
107			foreach my $t (qw/UPDATE INSERT DELETE/) {
108
109			my $lc_t = lc($t);
110
111			$dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });
112
113			my $sql = qq{
114
115			CREATE TRIGGER pgest_trigger_${lc_t} AFTER ${t}
116			ON ${table} FOR EACH ROW
117			EXECUTE PROCEDURE pgest_trigger('$c->{node_url}','$c->{user}','$c->{passwd}',
118			'$c->{pk_col}', $cols
119			)
120
121			};
122
123			warn "$sql\n";
124
125			$dbh->do( $sql ) \|\| die $dbh->errstr();
126
127			}