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