trunk/scripts/dbi-indexer.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/;

=head1 NAME

dbi-indexer.pl - example indexer of DBI sources for Search::Estraier

=cut

my $c = {
        node_url => 'http://localhost:1978/node/dbi',
        dbi => 'Pg:dbname=azop',
        sql => qq{
                select * from history_collection_view_cache
        },
        db_encoding => 'iso-8859-2',
        debug => 0,
};

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

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

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

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

my $t = time();

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

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

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

        $doc->add_attr('@uri', $row->{_id});

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

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

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

}
1	dpavlin	131	#!/usr/bin/perl -w
2
3			use strict;
4	dpavlin	144	use Search::Estraier 0.06;
5	dpavlin	131	use DBI;
6			use Data::Dumper;
7			use Encode qw/from_to/;
8	dpavlin	146	use Time::HiRes qw/time/;
9	dpavlin	131
10			=head1 NAME
11
12			dbi-indexer.pl - example indexer of DBI sources for Search::Estraier
13
14			=cut
15
16	dpavlin	144	my $c = {
17			node_url => 'http://localhost:1978/node/dbi',
18			dbi => 'Pg:dbname=azop',
19			sql => qq{
20			select * from history_collection_view_cache
21			},
22	dpavlin	145	db_encoding => 'iso-8859-2',
23	dpavlin	144	debug => 0,
24	dpavlin	131	};
25
26			# create and configure node
27			my $node = new Search::Estraier::Node(
28	dpavlin	144	url => $c->{node_url},
29	dpavlin	131	user => 'admin',
30			passwd => 'admin',
31			croak_on_error => 1,
32	dpavlin	144	create => 1,
33			debug => $c->{debug},
34	dpavlin	131	);
35
36			# create DBI connection
37	dpavlin	144	my $dbh = DBI->connect("DBI:$c->{dbi}","","") \|\| die $DBI::errstr;
38	dpavlin	131
39	dpavlin	144	my $sth = $dbh->prepare($c->{sql}) \|\| die $dbh->errstr();
40	dpavlin	131	$sth->execute() \|\| die $sth->errstr();
41
42	dpavlin	144	warn "# columns: ",join(",",@{ $sth->{NAME} }),"\n" if ($c->{debug});
43	dpavlin	131
44			my $total = $sth->rows;
45			my $i = 1;
46
47	dpavlin	146	my $t = time();
48
49	dpavlin	131	while (my $row = $sth->fetchrow_hashref() ) {
50
51	dpavlin	144	warn "# row: ",Dumper($row) if ($c->{debug});
52	dpavlin	131
53			# create document
54			my $doc = new Search::Estraier::Document;
55
56			$doc->add_attr('@uri', $row->{_id});
57
58			printf "%4d ",$i;
59
60			while (my ($col,$val) = each %{$row}) {
61
62			if ($val) {
63			# change encoding?
64	dpavlin	145	from_to($val, ($c->{db_encoding} \|\| 'ISO-8859-1'), 'UTF-8');
65	dpavlin	131
66			# add attributes (make column usable from attribute search)
67			$doc->add_attr($col, $val);
68
69			# add body text to document (make it searchable using full-text index)
70			$doc->add_text($val);
71
72			print "R";
73			} else {
74			print ".";
75			}
76
77			}
78
79	dpavlin	144	warn "# doc draft: ",$doc->dump_draft, "\n" if ($c->{debug});
80	dpavlin	131
81			die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });
82	dpavlin	146
83			printf (" %d%% %.1f/s\n", int(( $i++ / $total) * 100), ( $i / (time() - $t) ) );
84
85	dpavlin	131	}