view release on metacpan or  search on metacpan

Bio/DB/GFF/Adaptor/dbi/pg_fts.pm  view on Meta::CPAN

  my ($class,$name) = @_;

  my @terms = split /\s+/, $name; 

  if ($name =~ /\*/) {
    $name =~ tr/*/%/s;
    return ("fgroup.gclass=? AND lower(fgroup.gname) LIKE lower(?)",$class,$name);
  }
  else {
    my $where_str = "fgroup.gclass=? AND (fgroup.idxfti @@ to_tsquery('default', ?)) ";
    if (@terms == 1) {
      return ($where_str,$class,$name);
    }
    else {
      my $andstring = join (' & ', @terms);
#      $where_str .= qq{ AND (fgroup.gname ILIKE '\%$name%')};
      return ($where_str,$class,$andstring); 
    }
  }
}

=head2 install_TSearch2

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

=cut


#needs method for installing TSearch2 (does that mean that the SQL for
#creating the tables and functions should go in here?  That would be
#the safest and easiest thing to do
sub install_TSearch2 {
  my $self = shift;

  my $dbh = $self->features_db;

  $dbh->do('ALTER TABLE fattribute_to_feature ADD COLUMN idxFTI tsvector') 
     or $self->throw('adding FTI column to f_to_f failed');

  $dbh->do('ALTER TABLE fgroup ADD COLUMN idxFTI tsvector')
     or $self->throw('adding FTI column to fgroup failed');

  $self->update_TSearch2();

  return;
}

=head2 update_TSearch2

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

=cut


sub update_TSearch2 {
  my $self = shift;

  my $dbh = $self->features_db;

  $self->warn('updating full text column; this may take a very long time...');
  $dbh->do("UPDATE fattribute_to_feature "
          ."SET idxFTI= to_tsvector('default', fattribute_value) "
          ."WHERE idxFTI IS NULL") 
       or $self->throw('updating fti column failed');
  $dbh->do("UPDATE fgroup "
          ."SET idxFTI= to_tsvector('default', gname) "
          ."WHERE idxFTI IS NULL")
       or $self->throw('updating fgroup fti column failed');

  $self->warn('Preliminary optimization of database; this may also take a long time...');
  $dbh->do('VACUUM FULL ANALYZE')
       or $self->throw('vacuum failed');

  $self->warn('Updating full text index; again, this may take a long time');
  $dbh->do('CREATE INDEX idxFTI_idx ON fattribute_to_feature '
          .'USING gist(idxFTI)')
       or $self->warn('creating full text index failed');
  $dbh->do('CREATE INDEX fgroup_idxFTI_idx ON fgroup '
          .'USING gist(idxFTI)')
       or $self->warn('creating fgroup full text index failed');

  $self->warn('Optimizing database; hopefully, this will not take as long as other steps');
  $dbh->do('VACUUM FULL ANALYZE');
  $dbh->do("SELECT set_curcfg('default')");

  return;
}

=head2 remove_TSearch2

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

=cut

sub remove_TSearch2 {
  my $self = shift;

  my $dbh = $self->features_db;

  $self->warn('Removing full text search capabilities');
  $dbh->do('DROP INDEX idxFTI_idx')
     or $self->throw('dropping full text index failed');
  $dbh->do('DROP INDEX fgroup_idxFTI_idx')
     or $self->throw('dropping full text index failed');

  $dbh->do('ALTER TABLE fattribute_to_feature DROP COLUMN idxFTI')
     or $self->throw('dropping full text column failed');
  $dbh->do('ALTER TABLE fgroup DROP COLUMN idxFTI')
     or $self->throw('dropping full text column failed');


  return;