TextSearch/Pg.pm untested

filter: Statementstmt 0.0% 0 / 62 Branchbran 0.0% 0 / 10 Conditioncond 0.0% 0 / 15 MC/DCmcdc 0.0% 0 / 9 Subroutinesub 0.0% 0 / 6 Podpod 0.0% 0 / 6 totaltotal 0.0% 0 / 108 CC 6 SCAR 19.3
CC
6
Cov
0%
GetQuery
12.0
CreateIndex
6.0
IndexFile
6.0
CRAP 6.9
1 ######################################################################
2 ## ##
3 ## TextSearch::Pg - Postgres specific routines for TextSearch ##
4 ## ##
5 ######################################################################
6  
7 package DBIx::TextSearch;
8  
9 ######################################################################
10 sub CreateIndex {
11 # create database tables
12 # tables: _doc_ID - URI, title, docID, md5
13 # _words - w_ID, word
14 # _description - m_ID, meta desription
15  
16 my $self = shift();
17 my $dbh = $self->{dbh};
18  
19 # see if this index seems to exist - don't want to overwrite an
20 # existing index
21 my @tables = $dbh->tables;
22 my $exists = undef;
23 foreach my $table (@tables) {
24 if ($table =~ m/$self->{name}_(docid|words|description)/) {
25 # index tables exist
26 croak "Creating index $self->{name} would use some existing tables. Aborting\n";
27 }
28 }
29  
30  
31 # prep SQL statements to create index tables
32 my $sql_docID = "create table $self->{name}_docID (" .
33 "URI varchar(255), title varchar(100), d_ID int4, md5 varchar(50))";
34 my $sql_words = "create table $self->{name}_words (" .
35 "w_ID int4, word text)";
36 my $sql_meta = "create table $self->{name}_description (" .
37 "m_ID int4, description text)";
38  
39 # create index tables
40 $dbh->do($sql_docID) or croak $dbh->errstr;
41 $dbh->do($sql_words) or croak $dbh->errstr;
42 $dbh->do($sql_meta) or croak $dbh->errstr;
43 }
44 ######################################################################
45 sub RemoveDocument {
46 # remove a single document from the database
47 my ($self, $uri) = @_;
48 my $sql = "delete from $self->{name}_docid, $self->{name}_words, " .
49 "$self->{name}_description where uri='$uri' and ((w_id = d_id) and ".
50 "(m_id = d_id))";
51 $self->{dbh}->do($sql) or carp("Can't delete document $uri: $self->{dbh}->errstr");
52 }
53 ######################################################################
54 sub IndexFile {
55 # given URI, title, description, document
56 # contents, index this document calling syntax:
57 # $self->IndexFile($params{uri}, $title, $description, $md5 $content);
58 my ($self, $uri, $title, $desc, $md5, $content) = @_;
59  
60 # remove this document from the index if it has been indexed previously
61 my $sql = "select d_id from $self->{name}_docid where uri = '$uri'";
62 my $sth = $self->{dbh}->prepare($sql);
63 $sth->execute();
64 if ($sth->rows > 0) {
65 $self->RemoveDocument($uri);
66 }
67 $sth->finish;
68 undef $sql;
69 undef $sth;
70  
71 my $doc = \$content; # HTML::TokeParser needs a ref to document
72 # content, DBI inserts the raw ref
73 # SCALAR(0x...) if passed a ref.
74  
75 # find a unique document ID number for this document
76 my $sql_docid = "select d_id from " . $self->{name} . "_docID order " .
77 "by d_id desc limit 1 offset 0";
78 my $sth_docid = $self->{dbh}->prepare($sql_docid);
79 $sth_docid->execute();
80  
81 my $docid = $sth_docid->fetchrow_array();
82 $sth_docid->finish();
83 ++$docid;
84  
85 #
86 # insert values into database.
87 #
88  
89 # URI, title, doc_id, md5
90 my $sql_main = "insert into " . $self->{name} . "_docid " .
91 "(uri, title, d_id, md5) values ('$uri', '$title', '$docid', '$md5')";
92 $self->{dbh}->do($sql_main) or say $sql_main;
93  
94 # words
95 my $sql_words = "insert into $self->{name}_words " .
96 "(w_id, word) values ('$docid', '$content')";
97 $self->{dbh}->do($sql_words);
98  
99 # meta description
100 my $sql_meta = "insert into " . $self->{name} . "_description " .
101 "(m_id, description) values ('$docid', '$desc')";
102 $self->{dbh}->do($sql_meta);
103  
104 # this document is now indexed.
105 }
106 ######################################################################
107 sub GetQuery {
108 # create and return a query via Text::Query::BuildSQLPg
109 my $self = shift;
110 my %params = @_;
111 my $parser;
112  
113 # select either advanced parser or simple parser
114 if ($params{parser} eq 'advanced') {
115 $parser = new Text::Query($params{query},
116 -parse => 'Text::Query::ParseAdvanced',
117 -solve => 'Text::Query::SolveSQL',
118 -build => 'Text::Query::BuildSQLPg',
119 -fields_searched =>
120 'title, description, word'
121 );
122 } elsif ($params{parser} eq 'simple') {
123 $parser = new Text::Query($params{query},
124 -parse => 'Text::Query::ParseSimple',
125 -solve => 'Text::Query::SolveSQL',
126 -build => 'Text::Query::BuildSQLPg',
127 -fields_searched =>
128 'title, description, word'
129 );
130 } else {
131 carp "parser type not defined\n";
132 }
133  
134 # generate the query
135 my $query = "select distinct uri, title, description from " .
136 "$self->{'name'}_docid, $self->{'name'}_description," .
137 " $self->{'name'}_words where " . $parser->matchstring() .
138 " and (( m_id=d_id) and (d_id=w_id))";
139 return $query;
140 }
141 ######################################################################
142 sub FlushIndex {
143 # delete data from the index (not tables)
144 my $self = shift();
145 my @tables = ("$self->{name}_docid",
146 "$self->{name}_words",
147 "$self->{name}_description");
148  
149 foreach my $table (@tables) {
150 $self->{dbh}->do("delete from $table")
151 or cluck "Can't remove contents of index table $table: ".
152 $dbh->errstr;
153 }
154 }
155 ######################################################################
156 sub MD5 {
157 # return the md5sum for an indexed document
158 # timestamp of indexed file
159 my ($self, $doc) = @_;
160 print "Doc uri $doc\n";
161 my $qry = "select md5 from $self->{name}_docid where " .
162 "uri = '$doc'";
163  
164 say "query for indexed md5sum: $qry\n";
165  
166 my $sth = $self->{dbh}->prepare($qry);
167 $sth->execute;
168 my @md5 = $sth->fetchrow_array;
169 my $md5_db = shift @md5;
170 unless ($md5_db ) { $md5_db = 'none' }
171 return $md5_db;
172 }
173 ######################################################################
174 1;