BioPerl
view release on metacpan or search on metacpan
Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
multiple exons of the same transcript. It is also used to assign a
name and class to a singleton feature. Finally, the group table is
used to identify the target of a similarity hit. This is consistent
with the way in which the group field is used in the GFF version 2
format.
The fgroup.gid field joins with the fdata.gid field.
Examples:
sql> select * from fgroup where gname='sjj_2L52.1';
+-------+-------------+------------+
| gid | gclass | gname |
+-------+-------------+------------+
| 69736 | PCR_product | sjj_2L52.1 |
+-------+-------------+------------+
1 row in set (0.70 sec)
sql> select fref,fstart,fstop from fdata,fgroup
where gclass='PCR_product' and gname = 'sjj_2L52.1'
and fdata.gid=fgroup.gid;
+---------------+--------+-------+
| fref | fstart | fstop |
+---------------+--------+-------+
| CHROMOSOME_II | 1586 | 2355 |
+---------------+--------+-------+
1 row in set (0.03 sec)
=item ftype
This table contains the feature types, one per row. Columns are:
ftypeid the feature type ID (integer)
fmethod the feature type method name (string)
fsource the feature type source name (string)
The ftype.ftypeid field joins with the fdata.ftypeid field. Example:
sql> select fref,fstart,fstop,fmethod,fsource from fdata,fgroup,ftype
where gclass='PCR_product'
and gname = 'sjj_2L52.1'
and fdata.gid=fgroup.gid
and fdata.ftypeid=ftype.ftypeid;
+---------------+--------+-------+-------------+-----------+
| fref | fstart | fstop | fmethod | fsource |
+---------------+--------+-------+-------------+-----------+
| CHROMOSOME_II | 1586 | 2355 | PCR_product | GenePairs |
+---------------+--------+-------+-------------+-----------+
1 row in set (0.08 sec)
=item fdna
This table holds the raw DNA of the reference sequences. It has three
columns:
fref reference sequence name (string)
foffset offset of this sequence
fdna the DNA sequence (longblob)
To overcome problems loading large blobs, DNA is automatically
fragmented into multiple segments when loading, and the position of
each segment is stored in foffset. The fragment size is controlled by
the -clump_size argument during initialization.
=item fattribute_to_feature
This table holds "attributes", which are tag/value pairs stuffed into
the GFF line. The first tag/value pair is treated as the group, and
anything else is treated as an attribute (weird, huh?).
CHR_I assembly_tag Finished 2032 2036 . + . Note "Right: cTel33B"
CHR_I assembly_tag Polymorphism 668 668 . + . Note "A->C in cTel33B"
The columns of this table are:
fid feature ID (integer)
fattribute_id ID of the attribute (integer)
fattribute_value text of the attribute (text)
The fdata.fid column joins with fattribute_to_feature.fid.
=item fattribute
This table holds the normalized names of the attributes. Fields are:
fattribute_id ID of the attribute (integer)
fattribute_name Name of the attribute (varchar)
=back
=head2 Data Loading Methods
In addition to implementing the abstract SQL-generating methods of
Bio::DB::GFF::Adaptor::dbi, this module also implements the data
loading functionality of Bio::DB::GFF.
=cut
=head2 new
Title : new
Usage : $db = Bio::DB::GFF->new(@args)
Function: create a new adaptor
Returns : a Bio::DB::GFF object
Args : see below
Status : Public
The new constructor is identical to the "dbi" adaptor's new() method,
except that the prefix "dbi:pg" is added to the database DSN identifier
automatically if it is not there already.
Argument Description
-------- -----------
-dsn the DBI data source, e.g. 'dbi:Pg:dbname=:ens0040' or "ens0040"
-user username for authentication
-pass the password for authentication
=cut
Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
a more efficient way: L<Bio::DB::GFF::Adaptor::dbi::pg_fts>,
which inherits from this adaptor and uses the optional PostgreSQL
module TSearch2 for full text indexing. See that adaptor's
documentation for more information.
See also L<Bio::DB::GFF>
Title : search_notes
Usage : @search_results = $db->search_notes("full text search string",$limit)
Function: Search the notes for a text string, using mysql full-text search
Returns : array of results
Args : full text search string, and an optional row limit
Status : public
This is a replacement for the mysql-specific method. Given a search string, it
performs a ILIKE 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, suitable for passing to segment()
column 2 The text of the note
column 3 A relevance score.
Note that for large databases this can be very slow and may result in
time out or 500-cgi errors. If this is happening on a regular basis,
you should look into using L<Bio::DB::GFF::Adaptor::dbi::pg_fts> which
implements the TSearch2 full text indexing scheme.
=cut
sub search_notes{
# my $self = shift;
# my ($search_string,$limit) = @_;
#
# $search_string =~ tr/*/%/s;
# $search_string = '%'.$search_string unless $search_string =~ /^\%/;
# $search_string = $search_string.'%' unless $search_string =~ /\%$/;
# warn "search_string:$search_string";
# my $query = FULLTEXTWILDCARD;
# $query .= " limit $limit" if defined $limit;
# my $sth = $self->dbh->do_query($query,$search_string);
#
# my @results;
# while (my ($class,$name,$note) = $sth->fetchrow_array) {
#
# next unless $class && $name; # sorry, ignore NULL objects
# my $featname = Bio::DB::GFF::Featname->new($class=>$name);
#
# push @results,[$featname,$note,0]; #gbrowse expects a score, but
# #pg doesn't give one, thus the 0
# }
# warn @results;
#
# return @results;
}
=head2 make_meta_set_query
Title : make_meta_set_query
Usage : $sql = $db->make_meta_set_query
Function: return SQL fragment for setting a meta parameter
Returns : SQL fragment
Args : none
Status : public
By default this does nothing; meta parameters are not stored or
retrieved.
=cut
sub make_meta_set_query {
return 'INSERT INTO fmeta VALUES (?,?)';
}
sub make_classes_query {
my $self = shift;
return 'SELECT DISTINCT gclass FROM fgroup WHERE NOT gclass IS NULL';
}
sub chunk_size {
my $self = shift;
$self->meta('chunk_size') || DEFAULT_CHUNK;
}
sub getseqcoords_query {
my $self = shift;
return GETSEQCOORDS ;
}
sub getaliascoords_query{
my $self = shift;
return GETALIASCOORDS ;
}
sub getforcedseqcoords_query{
my $self = shift;
return GETFORCEDSEQCOORDS ;
}
sub getaliaslike_query{
my $self = shift;
return GETALIASLIKE ;
}
sub make_features_select_part {
my $self = shift;
my $options = shift || {};
my $s;
if (my $b = $options->{bin_width}) {
$s = <<END;
fref,
1+$b*floor(fstart/$b) as fstart,
$b*(1+floor(fstart/$b)) as fstop,
CASE WHEN fsource IS NULL THEN fmethod
ELSE fmethod||':'||fsource,
'bin',
count(*) as fscore,
Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
my ($class,$name,$location,$callback) = @_;
$callback || $self->throw('must provide a callback argument');
my @bin_parts = split /\s*OR/, $self->bin_query($location->[1],$location->[2]) if $location;
my $select = $self->make_features_select_part;
my $from = $self->make_features_from_part(undef,{sparse_groups=>1});
my ($where,@args) = $self->make_features_by_name_where_part($class,$name);
my $join = $self->make_features_join_part;
my $range = $self->pg_make_features_by_range_where_part('overlaps',
{refseq=>$location->[0],
class =>'',
start=>$location->[1],
stop =>$location->[2]}) if $location;
my @temp_args;
my @query_pieces;
my $query;
if (@bin_parts) {
foreach my $bin (@bin_parts) {
my $temp_query = "SELECT $select FROM $from WHERE $join AND $where AND $range AND $bin\n";
push @temp_args, @args;
push @query_pieces, $temp_query;
}
@args = @temp_args;
$query = join("UNION\n", @query_pieces);
} else {
$query = "SELECT $select FROM $from WHERE $where AND $join";
}
my $sth = $self->dbh->do_query($query,@args);
my $count = 0;
while (my @row = $sth->fetchrow_array) {
$callback->(@row);
$count++;
}
$sth->finish;
return $count;
}
sub update_sequences {
my $self = shift;
my $dbh = $self->features_db;
$dbh->do("SELECT setval('public.fdata_fid_seq', max(fid)+1) FROM fdata");
$dbh->do("SELECT setval('public.fattribute_fattribute_id_seq', max(fattribute_id)+1) FROM fattribute");
$dbh->do("SELECT setval('public.fgroup_gid_seq', max(gid)+1) FROM fgroup");
$dbh->do("SELECT setval('public.ftype_ftypeid_seq', max(ftypeid)+1) FROM ftype");
1;
}
=head2 make_features_by_name_where_part
Title : make_features_by_name_where_part
Usage : $db->make_features_by_name_where_part
Function: Overrides a function in Bio::DB::GFF::Adaptor::dbi to insure
that searches will be case insensitive. It creates the SQL
fragment needed to select a feature by its group name & class
Returns : a SQL fragment and bind arguments
Args : see below
Status : Protected
=cut
sub make_features_by_name_where_part {
my $self = shift;
my ($class,$name) = @_;
if ($name !~ /\*/) {
#allows utilization of an index on lower(gname)
return ("fgroup.gclass=? AND lower(fgroup.gname) = lower(?)",$class,$name);
}
else {
$name =~ tr/*/%/;
return ("fgroup.gclass=? AND lower(fgroup.gname) LIKE lower(?)",$class,$name);
}
}
#
# Methods from dbi.pm that need to be overridden to make
# searching for fref case insensitive
#
#
sub get_dna {
my $self = shift;
my ($ref,$start,$stop,$class) = @_;
my ($offset_start,$offset_stop);
my $has_start = defined $start;
my $has_stop = defined $stop;
my $reversed;
if ($has_start && $has_stop && $start > $stop) {
$reversed++;
($start,$stop) = ($stop,$start);
}
# turn start and stop into 0-based offsets
my $cs = $self->dna_chunk_size;
$start -= 1; $stop -= 1;
$offset_start = int($start/$cs)*$cs;
$offset_stop = int($stop/$cs)*$cs;
my $sth;
# special case, get it all
if (!($has_start || $has_stop)) {
$sth = $self->dbh->do_query('select fdna,foffset from fdna where lower(fref)=lower(?) order by foffset',$ref);
}
elsif (!$has_stop) {
$sth = $self->dbh->do_query('select fdna,foffset from fdna where lower(fref)=lower(?) and foffset>=? order by foffset',
$ref,$offset_start);
}
else { # both start and stop defined
$sth = $self->dbh->do_query('select fdna,foffset from fdna where lower(fref)=lower(?) and foffset>=? and foffset<=? order by foffset',
$ref,$offset_start,$offset_stop);
}
my $dna = '';
while (my($frag,$offset) = $sth->fetchrow_array) {
substr($frag,0,$start-$offset) = '' if $has_start && $start > $offset;
$dna .= $frag;
}
substr($dna,$stop-$start+1) = '' if $has_stop && $stop-$start+1 < length($dna);
if ($reversed) {
$dna = reverse $dna;
$dna =~ tr/gatcGATC/ctagCTAG/;
}
$sth->finish;
$dna;
}
sub refseq_query {
my $self = shift;
my ($refseq,$refclass) = @_;
my $query = "lower(fdata.fref)=lower(?)";
return wantarray ? ($query,$refseq) : $self->dbh->dbi_quote($query,$refseq);
}
sub make_types_where_part {
my $self = shift;
my ($srcseq,$start,$stop,$want_count,$typelist) = @_;
my (@query,@args);
if (defined($srcseq)) {
push @query,'lower(fdata.fref)=lower(?)';
push @args,$srcseq;
if (defined $start or defined $stop) {
$start = 1 unless defined $start;
$stop = MAX_SEGMENT unless defined $stop;
my ($q,@a) = $self->overlap_query($start,$stop);
push @query,"($q)";
push @args,@a;
}
}
if (defined $typelist && @$typelist) {
my ($q,@a) = $self->types_query($typelist);
push @query,($q);
push @args,@a;
}
my $query = @query ? join(' AND ',@query) : '1=1';
return wantarray ? ($query,@args) : $self->dbh->dbi_quote($query,@args);
}
sub get_feature_id {
my $self = shift;
my ($ref,$start,$stop,$typeid,$groupid) = @_;
my $s = $self->{load_stuff};
unless ($s->{get_feature_id}) {
my $dbh = $self->features_db;
$s->{get_feature_id} =
$dbh->prepare_delayed('SELECT fid FROM fdata WHERE lower(fref)=lower(?) AND fstart=? AND fstop=? AND ftypeid=? AND gid=?');
}
my $sth = $s->{get_feature_id} or return;
$sth->execute($ref,$start,$stop,$typeid,$groupid) or return;
my ($fid) = $sth->fetchrow_array;
return $fid;
}
sub _delete {
my $self = shift;
( run in 0.876 second using v1.01-cache-2.11-cpan-364913b4093 )