trunk/bin/pgest-index.pl

#!/usr/bin/perl -w

use strict;
use Search::Estraier 0.07;
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',
        debug => 1,
        user => 'admin',
        passwd => 'admin',
};

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

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

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

# 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,
);

$node->master(
        action => 'nodeclr',
        name => $table,
);

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

# create PostgreSQL functions
$dbh->do(qq{

CREATE OR REPLACE FUNCTION pgest(text, text, text, int, text, text, text, int, int, text[])
        RETURNS setof record
        AS 'pgest','pgest_node'
        LANGUAGE 'C' IMMUTABLE CALLED ON NULL INPUT;

CREATE OR REPLACE FUNCTION pgest_trigger() RETURNS TRIGGER
        AS 'pgest', 'pgest_trigger'
        LANGUAGE 'C' STRICT;

}) || die $dbh->errstr();


# drop existing triggers
foreach my $t (qw/UPDATE INSERT DELETE/) {
        my $lc_t = lc($t);
        $dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });
}

$dbh->begin_work;

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) {
                        # 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 $cols = "'" . join("', '", @cols) . "'";

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

        my $lc_t = lc($t);

        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();

}

$dbh->commit;

my $col_names = join(', ', @cols);
my $col_def = join(', ', map { "$_ text" } @cols);

print "
## example SQL search query:

SELECT $col_names
FROM pgest(
        -- node, login, passwd, depth
        '$c->{node_url}', '$c->{user}', '$c->{passwd}', 0,
        -- full text search
        'foo bar',
        -- attribute filter, order, limit, offset
        null, null, null, null,
        -- return columns
        array[$cols]
) as ($col_def);

";
1	#!/usr/bin/perl -w
2
3	use strict;
4	use Search::Estraier 0.07;
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	debug => 1,
26	user => 'admin',
27	passwd => 'admin',
28	};
29
30	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});
33
34	my $table = $c->{name} \|\| die "no name?";
35
36	# create and configure node
37	my $node = new Search::Estraier::Node(
38	url => $c->{node_url},
39	user => $c->{user},
40	passwd => $c->{passwd},
41	croak_on_error => 1,
42	create => 1,
43	debug => $c->{debug} >= 4 ? 1 : 0,
44	);
45
46	$node->master(
47	action => 'nodeclr',
48	name => $table,
49	);
50
51	# create DBI connection
52	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();
78	$sth->execute() \|\| die $sth->errstr();
79
80	my @cols = @{ $sth->{NAME} };
81
82	warn "# columns: ",join(",", @cols),"\n" if ($c->{debug});
83
84	my $total = $sth->rows;
85	my $i = 1;
86
87	my $t = time();
88	my $pk_col = $c->{pk_col} \|\| 'id';
89
90	while (my $row = $sth->fetchrow_hashref() ) {
91
92	warn "# row: ",Dumper($row) if ($c->{debug} >= 3);
93
94	# create document
95	my $doc = new Search::Estraier::Document;
96
97	if (my $id = $row->{$pk_col}) {
98	$doc->add_attr('@uri', $id);
99	} else {
100	die "can't find pk_col column '$pk_col' in results\n";
101	}
102
103	printf "%4d ",$i;
104
105	while (my ($col,$val) = each %{$row}) {
106
107	if ($val) {
108	# add attributes (make column usable from attribute search)
109	$doc->add_attr($col, $val);
110
111	# add body text to document (make it searchable using full-text index)
112	$doc->add_text($val);
113
114	print "R";
115	} else {
116	print ".";
117	}
118
119	}
120
121	warn "# doc draft: ",$doc->dump_draft, "\n" if ($c->{debug} >= 2);
122
123	die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });
124
125	printf (" %d%% %.1f/s\n", int(( $i++ / $total) * 100), ( $i / (time() - $t) ) );
126
127	}
128
129	my $cols = "'" . join("', '", @cols) . "'";
130
131	foreach my $t (qw/UPDATE INSERT DELETE/) {
132
133	my $lc_t = lc($t);
134
135	my $sql = qq{
136
137	CREATE TRIGGER pgest_trigger_${lc_t} AFTER ${t}
138	ON ${table} FOR EACH ROW
139	EXECUTE PROCEDURE pgest_trigger('$c->{node_url}','$c->{user}','$c->{passwd}',
140	'$c->{pk_col}', $cols
141	)
142
143	};
144
145	#warn "$sql\n";
146
147	$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	";