Bio::D...pg_fts(3pm)

💖 Bio::D...pg_fts 💖

okay bestie, so this page is the manual for Bio::DB::GFF::Adaptor::dbi::pg_fts — it's one of those User Contributed Perl Documentation pages, and it's lowkey iconic if you're the kind of person who wants full text searching in a GFF database. buckle up 🎀

---

✨ NAME ✨

Bio::DB::GFF::Adaptor::dbi::pg_fts — a database adaptor for a specific postgres schema, with a TSearch2 implementation. that's the whole vibe. 🦋

---

✨ SYNOPSIS ✨

#create new GFF database connection
my $db      = Bio::DB::GFF->new( -adaptor => 'dbi::pg_fts',
                                 -dsn     => 'dbi:Pg:dbname=worm');

#add full text indexing 'stuff'
#assumes that TSearch2 is available to PostgreSQL
#this will take a VERY long time for a reasonably large database
$db->install_TSearch2();

...some time later...
#we don't like full text searching...
$db->remove_TSearch2();

like, that's it. that's the whole arc. install it, wait a very long time, regret it, remove it. character development fr fr 💀

---

✨ DESCRIPTION ✨

so this adaptor is based on Bio::DB::GFF::Adaptor::dbi::pg, but it implements the TSearch2 PostgreSQL contrib module so you get fast full text searching. no cap. if you wanna use this module with your PostgreSQL GFF database, you gotta make TSearch2 available in the database first. non-negotiable. 💅

to use this adaptor, follow these steps:

• Install TSearch2 contrib module for Pg — can be as easy as sudo yum install postgresql-contrib, or you may need to recompile PostgreSQL to include it. see <http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html> for more details.
• Load the TSearch2 functions to you database — run:

% cat tsearch2.sql | psql <your database>

• Load your data using the pg adaptor:

% bp_pg_bulk_load_gff.pl -c -d yeast saccharomyces_cerevisiae.gff

or

% bp_load_gff.pl -c -d yeast -a dbi::pg saccharomyces_cerevisiae.gff

• Add GFF/TSearch2 specific modifications — execute a perl script like this one:

#!/usr/bin/perl -w
use strict;

use Bio::DB::GFF;

my $db = Bio::DB::GFF->new(
    -adaptor   => 'dbi::pg_fts',
    -dsn       => 'dbi:Pg:dbname=yeast',
    -user      => 'scott',
  );

print "Installing TSearch2 columns...\n";

$db->install_TSearch2();

print "Done\n";

heads up bestie: that last step takes a LONG time. for a S. cerevisiae database with 15K rows, it took over an hour on my laptop, and with a C. elegans database (~10 million rows) it took well over a day. 😭

if at some point you add more data you your database, you need to run a similar script to the one above, only executing the update_TSearch2() method instead. and finally — if you wanna remove the TSearch2 columns from your database and go back to using the pg adaptor, you can execute a script like the one above, only executing the remove_TSearch2() method. it's giving ✨irreversible life choices✨ but it's actually reversible, slay.

---

✨ NOTES ABOUT TSearch2 SEARCHING ✨

you should know a few things about how searching with TSearch2 works in the GBrowse environment:

1. TSearch2 does not do wild cards, so you should encourage your users not to use them. if wild cards are used, the adaptor will fall back on an ILIKE search, which will be much slower. 🐌
2. However, TSearch2 does do 'word stemming'. that is, if you search for 'copy', it will find 'copy', 'copies', and 'copied'. she's multilingual 💅
3. TSearch2 does not do phrase searching; all of the terms in the search string are ANDed together.

---

✨ ACKNOWLEDGEMENTS ✨

special thanks to Russell Smithies and Paul Smale at AgResearch in New Zealand for giving me their recipe for doing full text indexing in a GFF database. absolute legends, we love them for this 🫶

---

✨ BUGS ✨

please report bugs to the BioPerl and/or GBrowse mailing lists (<mailto:bioperl-l@lists.open-bio.org> and <mailto:gmod-gbrowse@lists.sourceforge.net> respectively). don't suffer in silence bestie, tell the mailing list 💜

---

✨ SEE ALSO ✨

please see Bio::DB::GFF::Adaptor::dbi::pg for more information about tuning your PostgreSQL server for GFF data, and for general information about GFF database access, see Bio::DB::GFF.

---

✨ AUTHOR ✨

Scott Cain, cain@cshl.edu

---

✨ APPENDIX ✨

search_notes 🍓

Title   : search_notes
Usage   : @search_results = $db->search_notes("full text string",$limit)
Function: Search the notes for a text string, using PostgreSQL TSearch2
Returns : array of results
Args    : full text search string, and an optional row limit
Status  : public

this one is based on the mysql-specific method that makes use of the TSearch2 functionality in PosgreSQL's contrib directory. given a search string, it performs a full-text search of the notes table and returns an array of results. each row of the returned array is a arrayref containing the following fields:

column 1   A Bio::DB::GFF::Featname object, for passing to segment()
column 2   The text of the note
column 3   A relevance score.

three little fields, living their best lives. 📊

make_features_by_name_where_part 🌈

Title   : make_features_by_name_where_part
Function: constructs a TSearch2-compliant WHERE clause for a name search
Status  : protected

install_TSearch2 🧚

Title   : install_TSearch2
Function: installs schema modifications for use with TSearch2
Usage   : $db->install_TSearch2
Status  : public

update_TSearch2 ⭐

Title   : update_TSearch2
Function: Updates TSearch2 columns
Usage   : $db->update_TSearch2
Status  : public

remove_TSearch2 🦋

Title   : remove_TSearch2
Function: Removes TSearch2 columns
Usage   : $db->remove_TSearch2
Status  : public

---

and that's the whole page bestie!! this lil doc is perl v5.30.0, dated 2020-01-13, and it lives under the manual name Bio::D...pg_fts(3pm) (truncated in the header, as manpages do 🙈). go forth and full-text search responsibly 👑✨