GFF/Adaptor/dbi/pg_fts.pm

filter: Statementstmt 20.7% 17 / 82 Branchbran 0.0% 0 / 40 Conditioncond 0.0% 0 / 3 MC/DCmcdc 0.0% 0 / 2 Subroutinesub 45.4% 5 / 11 Podpod 100% 6 / 6 totaltotal 19.4% 28 / 144 CC 22 SCAR 37.0
CC
22
Cov
14%
search_notes
90.0
update_TSearch2
42.0
remove_TSearch2
30.0
CRAP 40.3
« GFF/Adaptor/dbi/mysqlcmap.pm ↑ Summary GFF/Adaptor/dbi/mysql.pm »
1 package Bio::DB::GFF::Adaptor::dbi::pg_fts;
2 1 $Bio::DB::GFF::Adaptor::dbi::pg_fts::VERSION = '1.7.4';
3  
4 =head1 NAME
5  
6 Bio::DB::GFF::Adaptor::dbi::pg_fts -- Database adaptor for a specific postgres schema with a TSearch2 implementation
7  
8 =head1 SYNOPSIS
9  
10 #create new GFF database connection
11 my $db = Bio::DB::GFF->new( -adaptor => 'dbi::pg_fts',
12 -dsn => 'dbi:Pg:dbname=worm');
13  
14 #add full text indexing 'stuff'
15 #assumes that TSearch2 is available to PostgreSQL
16 #this will take a VERY long time for a reasonably large database
17 $db->install_TSearch2();
18  
19 ...some time later...
20 #we don't like full text searching...
21 $db->remove_TSearch2();
22  
23 =head1 DESCRIPTION
24  
25 This adaptor is based on Bio::DB::GFF::Adaptor::dbi::pg but it implements
26 the TSearch2 PostgreSQL contrib module for fast full text searching. To
27 use this module with your PostgreSQL GFF database, you need to make
28 TSearch2 available in the database.
29  
30 To use this adaptor, follow these steps:
31  
32 =over
33  
34 =item Install TSearch2 contrib module for Pg
35  
36 Can be as easy as `sudo yum install postgresql-contrib`, or you may
37 need to recompile PostgreSQL to include it. See
38 L<http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html>
39 for more details
40  
41 =item Load the TSearch2 functions to you database
42  
43 % cat tsearch2.sql | psql <your database>
44  
45 =item Load your data using the pg adaptor:
46  
47 % bp_pg_bulk_load_gff.pl -c -d yeast saccharomyces_cerevisiae.gff
48  
49 or
50  
51 % bp_load_gff.pl -c -d yeast -a dbi::pg saccharomyces_cerevisiae.gff
52  
53 =item Add GFF/TSearch2 specific modifications
54  
55 Execute a perl script like this one:
56  
57 #!/usr/bin/perl -w
58 use strict;
59  
60 use Bio::DB::GFF;
61  
62 my $db = Bio::DB::GFF->new(
63 -adaptor => 'dbi::pg_fts',
64 -dsn => 'dbi:Pg:dbname=yeast',
65 -user => 'scott',
66 );
67  
68 print "Installing TSearch2 columns...\n";
69  
70 $db->install_TSearch2();
71  
72 print "Done\n";
73  
74 =back
75  
76 Note that this last step will take a long time. For a S. cerevisiae
77 database with 15K rows, it took over an hour on my laptop, and
78 with a C. elegans database (~10 million rows) it took well over a day.
79  
80 If at some point you add more data you your database, you need to run
81 a similar script to the one above, only executing the update_TSearch2()
82 method. Finally, if you want to remove the TSearch2 columns from your
83 database and go back to using the pg adaptor, you can execute a script
84 like the one above, only executing the remove_TSearch2() method.
85  
86 =head1 NOTES ABOUT TSearch2 SEARCHING
87  
88 You should know a few things about how searching with TSearch2 works in
89 the GBrowse environment:
90  
91 =over
92  
93 =item 1
94  
95 TSearch2 does not do wild cards, so you should encourage your users not
96 to use them. If wild cards are used, the adaptor will fall back on
97 an ILIKE search, which will be much slower.
98  
99 =item 2
100  
101 However, TSearch2 does do 'word stemming'. That is, if you search
102 for 'copy', it will find 'copy', 'copies', and 'copied'.
103  
104 =item 3
105  
106 TSearch2 does not do phrase searching; all of the terms in the
107 search string are ANDed together.
108  
109 =back
110  
111 =head1 ACKNOWLEDGEMENTS
112  
113 Special thanks to Russell Smithies and Paul Smale at AgResearch in
114 New Zealand for giving me their recipe for doing full text indexing
115 in a GFF database.
116  
117 =head1 BUGS
118  
119 Please report bugs to the BioPerl and/or GBrowse mailing lists
120 (L<mailto:bioperl-l@lists.open-bio.org> and L<mailto:gmod-gbrowse@lists.sourceforge.net>
121 respectively).
122  
123 =head1 SEE ALSO
124  
125 Please see L<Bio::DB::GFF::Adaptor::dbi::pg> for more information
126 about tuning your PostgreSQL server for GFF data, and for general
127 information about GFF database access, see L<Bio::DB::GFF>.
128  
129 =head1 AUTHOR
130  
131 Scott Cain, cain@cshl.edu
132  
133 =head1 APPENDIX
134  
135 =cut
136  
137 # a simple postgres adaptor
138 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
139 1 ▶use Bio::DB::GFF::Adaptor::dbi;
Subroutine BEGIN: called CC=1 SCAR=0.0
140 1 ▶use base qw(Bio::DB::GFF::Adaptor::dbi::pg);
Subroutine BEGIN: called CC=1 SCAR=0.0
141  
142 1 ▶use constant FULLTEXTSEARCH => <<END;
Subroutine BEGIN: called CC=1 SCAR=0.0
143 SELECT distinct gclass,gname,fattribute_value,fmethod,fsource
144 FROM fgroup,fattribute_to_feature,fdata,ftype
145 WHERE fgroup.gid=fdata.gid
146 AND fdata.fid=fattribute_to_feature.fid
147 AND fdata.ftypeid=ftype.ftypeid
148 AND (fattribute_to_feature.idxfti @@ to_tsquery('default', ?))
149 END
150 ;
151  
152 1 ▶use constant FULLTEXTWILDCARD => <<END;
Subroutine BEGIN: called CC=1 SCAR=0.0
153 SELECT distinct gclass,gname,fattribute_value,fmethod,fsource
154 FROM fgroup,fattribute_to_feature,fdata,ftype
155 WHERE fgroup.gid=fdata.gid
156 AND fdata.fid=fattribute_to_feature.fid
157 AND fdata.ftypeid=ftype.ftypeid
158 AND lower(fattribute_to_feature.fattribute_value) LIKE lower(?)
159 END
160 ;
161  
162 0 ▶sub new {
Subroutine new: not called CC=1 SCAR=6.9
Pod documented
163 0 my $class = shift;
164 0 my $self = $class->SUPER::new(@_);
165 0 return $self;
166 }
167  
168 =head2 search_notes
169  
170 Title : search_notes
171 Usage : @search_results = $db->search_notes("full text string",$limit)
172 Function: Search the notes for a text string, using PostgreSQL TSearch2
173 Returns : array of results
174 Args : full text search string, and an optional row limit
175 Status : public
176  
177 This is based on the mysql-specific method that makes use of the TSearch2
178 functionality in PosgreSQL's contrib directory. Given a search string,
179 it performs a full-text search of the notes table and returns an array
180 of results. Each row of the returned array is a arrayref containing
181 the following fields:
182  
183 column 1 A Bio::DB::GFF::Featname object, for passing to segment()
184 column 2 The text of the note
185 column 3 A relevance score.
186  
187 =cut
188  
189 0 ▶sub search_notes {
Subroutine search_notes: not called CC=9 SCAR=45.0
Pod documented
190 0 my $self = shift;
191 0 my ($search_string,$limit) = @_;
192  
193 0 my @terms = split /\s+/, $search_string;
194  
195 0 my $sth;
196 0 ▶ if ($search_string =~ /\*/) {
Branch: 2 branches
BranchTrueFalseTotal
if ($search_string =~ /\*/) { } 0 0 0
elsif (@terms == 1) { } 0 0 0
197 0 $search_string =~ tr/*/%/s;
198 0 my $query = FULLTEXTWILDCARD;
199 0 ▶ $query .= " limit $limit" if defined $limit;
Branch: 1 branch
BranchTrueFalseTotal
if defined $limit 0 0 0
200 0 $sth = $self->dbh->do_query($query,$search_string);
201 }
202 elsif (@terms == 1) {
203 0 my $query = FULLTEXTSEARCH;
204 0 ▶ $query .= " limit $limit" if defined $limit;
Branch: 1 branch
BranchTrueFalseTotal
if defined $limit 0 0 0
205 0 $sth = $self->dbh->do_query($query,$search_string);
206 }
207 else {
208 0 my $query = FULLTEXTSEARCH;
209 0 my $andstring = join (' & ', @terms);
210 # $query .= qq{ AND (fattribute_to_feature.fattribute_value ILIKE '\%$search_string%')};
211 0 ▶ $query .= " LIMIT $limit" if defined $limit;
Branch: 1 branch
BranchTrueFalseTotal
if defined $limit 0 0 0
212 0 $sth = $self->dbh->do_query($query,$andstring);
213 }
214
215 0 my @results;
216 0 ▶ while (my ($class,$name,$note,$method,$source) = $sth->fetchrow_array) {
Branch: 1 branch
BranchTrueFalseTotal
if (() = $sth->fetchrow_array) 0 0 0
217  
218 0 ▶ next unless $class && $name; # sorry, ignore NULL objects
Branch: 1 branch
BranchTrueFalseTotal
unless $class and $name 0 0 0
Condition0%0/3
$class and $name
!ll&&!rl&&r
000
MC/DC0%0/2
$class and $name
$class $name
Truth table0%0/3
A and B
A= $classB= $name
ABresult
0X0
100
111
219 0 my $featname = Bio::DB::GFF::Featname->new($class=>$name);
220 0 my $type = Bio::DB::GFF::Typename->new($method,$source);
221 0 push @results,[$featname,$note,0,$type]; #gbrowse expects a score, but
222 #pg doesn't give one, thus the 0
223 }
224  
225 0 return @results;
226 }
227  
228 =head2 make_features_by_name_where_part
229  
230 Title : make_features_by_name_where_part
231 Function: constructs a TSearch2-compliant WHERE clause for a name search
232 Status : protected
233  
234 =cut
235  
236 #need a make_features_by_name_where_part method to override pg
237 0 ▶sub make_features_by_name_where_part {
Subroutine make_features_by_name_where_part: not called CC=3 SCAR=24.8
Pod documented
238 0 my $self = shift;
239 0 my ($class,$name) = @_;
240  
241 0 my @terms = split /\s+/, $name;
242  
243 0 ▶ if ($name =~ /\*/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($name =~ /\*/) { } 0 0 0
244 0 $name =~ tr/*/%/s;
245 0 return ("fgroup.gclass=? AND lower(fgroup.gname) LIKE lower(?)",$class,$name);
246 }
247 else {
248 0 my $where_str = "fgroup.gclass=? AND (fgroup.idxfti @@ to_tsquery('default', ?)) ";
249 0 ▶ if (@terms == 1) {
Branch: 1 branch
BranchTrueFalseTotal
if (@terms == 1) { } 0 0 0
250 0 return ($where_str,$class,$name);
251 }
252 else {
253 0 my $andstring = join (' & ', @terms);
254 # $where_str .= qq{ AND (fgroup.gname ILIKE '\%$name%')};
255 0 return ($where_str,$class,$andstring);
256 }
257 }
258 }
259  
260 =head2 install_TSearch2
261  
262 Title : install_TSearch2
263 Function: installs schema modifications for use with TSearch2
264 Usage : $db->install_TSearch2
265 Status : public
266  
267 =cut
268  
269  
270 #needs method for installing TSearch2 (does that mean that the SQL for
271 #creating the tables and functions should go in here? That would be
272 #the safest and easiest thing to do
273 0 ▶sub install_TSearch2 {
Subroutine install_TSearch2: not called CC=3 SCAR=24.8
Pod documented
274 0 my $self = shift;
275  
276 0 my $dbh = $self->features_db;
277  
278 0 ▶ $dbh->do('ALTER TABLE fattribute_to_feature ADD COLUMN idxFTI tsvector')
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("ALTER TABLE fattribute_to_feature ADD COLUMN idxFTI tsvector") or $self->throw("adding FTI column to f_to_f failed") 0 0 0
279 or $self->throw('adding FTI column to f_to_f failed');
280  
281 0 ▶ $dbh->do('ALTER TABLE fgroup ADD COLUMN idxFTI tsvector')
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("ALTER TABLE fgroup ADD COLUMN idxFTI tsvector") or $self->throw("adding FTI column to fgroup failed") 0 0 0
282 or $self->throw('adding FTI column to fgroup failed');
283  
284 0 $self->update_TSearch2();
285  
286 0 return;
287 }
288  
289 =head2 update_TSearch2
290  
291 Title : update_TSearch2
292 Function: Updates TSearch2 columns
293 Usage : $db->update_TSearch2
294 Status : public
295  
296 =cut
297  
298  
299 0 ▶sub update_TSearch2 {
Subroutine update_TSearch2: not called CC=6 SCAR=37.4
Pod documented
300 0 my $self = shift;
301  
302 0 my $dbh = $self->features_db;
303  
304 0 $self->warn('updating full text column; this may take a very long time...');
305 0 ▶ $dbh->do("UPDATE fattribute_to_feature "
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("UPDATE fattribute_to_feature SET idxFTI= to_tsvector('default', fattribute_value) WHERE idxFTI IS NULL") or $self->throw("updating fti column failed") 0 0 0
306 ."SET idxFTI= to_tsvector('default', fattribute_value) "
307 ."WHERE idxFTI IS NULL")
308 or $self->throw('updating fti column failed');
309 0 ▶ $dbh->do("UPDATE fgroup "
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("UPDATE fgroup SET idxFTI= to_tsvector('default', gname) WHERE idxFTI IS NULL") or $self->throw("updating fgroup fti column failed") 0 0 0
310 ."SET idxFTI= to_tsvector('default', gname) "
311 ."WHERE idxFTI IS NULL")
312 or $self->throw('updating fgroup fti column failed');
313  
314 0 $self->warn('Preliminary optimization of database; this may also take a long time...');
315 0 ▶ $dbh->do('VACUUM FULL ANALYZE')
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("VACUUM FULL ANALYZE") or $self->throw("vacuum failed") 0 0 0
316 or $self->throw('vacuum failed');
317  
318 0 $self->warn('Updating full text index; again, this may take a long time');
319 0 ▶ $dbh->do('CREATE INDEX idxFTI_idx ON fattribute_to_feature '
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("CREATE INDEX idxFTI_idx ON fattribute_to_feature USING gist(idxFTI)") or $self->warn("creating full text index failed") 0 0 0
320 .'USING gist(idxFTI)')
321 or $self->warn('creating full text index failed');
322 0 ▶ $dbh->do('CREATE INDEX fgroup_idxFTI_idx ON fgroup '
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("CREATE INDEX fgroup_idxFTI_idx ON fgroup USING gist(idxFTI)") or $self->warn("creating fgroup full text index failed") 0 0 0
323 .'USING gist(idxFTI)')
324 or $self->warn('creating fgroup full text index failed');
325  
326 0 $self->warn('Optimizing database; hopefully, this will not take as long as other steps');
327 0 $dbh->do('VACUUM FULL ANALYZE');
328 0 $dbh->do("SELECT set_curcfg('default')");
329  
330 0 return;
331 }
332  
333 =head2 remove_TSearch2
334  
335 Title : remove_TSearch2
336 Function: Removes TSearch2 columns
337 Usage : $db->remove_TSearch2
338 Status : public
339  
340 =cut
341  
342 0 ▶sub remove_TSearch2 {
Subroutine remove_TSearch2: not called CC=5 SCAR=34.0
Pod documented
343 0 my $self = shift;
344  
345 0 my $dbh = $self->features_db;
346  
347 0 $self->warn('Removing full text search capabilities');
348 0 ▶ $dbh->do('DROP INDEX idxFTI_idx')
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("DROP INDEX idxFTI_idx") or $self->throw("dropping full text index failed") 0 0 0
349 or $self->throw('dropping full text index failed');
350 0 ▶ $dbh->do('DROP INDEX fgroup_idxFTI_idx')
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("DROP INDEX fgroup_idxFTI_idx") or $self->throw("dropping full text index failed") 0 0 0
351 or $self->throw('dropping full text index failed');
352  
353 0 ▶ $dbh->do('ALTER TABLE fattribute_to_feature DROP COLUMN idxFTI')
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("ALTER TABLE fattribute_to_feature DROP COLUMN idxFTI") or $self->throw("dropping full text column failed") 0 0 0
354 or $self->throw('dropping full text column failed');
355 0 ▶ $dbh->do('ALTER TABLE fgroup DROP COLUMN idxFTI')
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do("ALTER TABLE fgroup DROP COLUMN idxFTI") or $self->throw("dropping full text column failed") 0 0 0
356 or $self->throw('dropping full text column failed');
357  
358  
359 0 return;
360 }
361  
362  
363 1 1;
« GFF/Adaptor/dbi/mysqlcmap.pm ↑ Summary GFF/Adaptor/dbi/mysql.pm »