Bio-DB-GFF
view release on metacpan or search on metacpan
bin/bp_bulk_load_gff view on Meta::CPAN
by using the --Temporary switch.
END
my @fasta_files_to_be_unlinked;
my @files = (FDATA,FTYPE,FGROUP,FDNA,FATTRIBUTE,FATTRIBUTE_TO_FEATURE);
foreach (@files) {
$FH{$_} = IO::File->new(">$tmpdir/$_.$$") or die $_,": $!";
$FH{$_}->autoflush;
}
if ( $use_pg ) {
$FH{FDATA() }->print("COPY fdata (fid, fref, fstart, fstop, fbin, ftypeid, fscore, fstrand, fphase, gid, ftarget_start, ftarget_stop) FROM stdin;\n");
$FH{FTYPE() }->print("COPY ftype (ftypeid, fmethod, fsource) FROM stdin;\n");
$FH{FGROUP() }->print("COPY fgroup (gid, gclass, gname) FROM stdin;\n");
$FH{FATTRIBUTE() }->print("COPY fattribute (fattribute_id, fattribute_name) FROM stdin;\n");
$FH{FATTRIBUTE_TO_FEATURE()}->print("COPY fattribute_to_feature (fid, fattribute_id, fattribute_value) FROM stdin;\n");
}
my $FID = 1;
my $GID = 1;
my $FTYPEID = 1;
my $ATTRIBUTEID = 1;
my %GROUPID = ();
my %FTYPEID = ();
my %ATTRIBUTEID = ();
bin/bp_bulk_load_gff view on Meta::CPAN
for (my $i=0; $i < @$group_name; $i++) {
$group_class->[$i] ||= '\N';
$group_name->[$i] ||= '\N';
$target_start ||= '\N';
$target_stop ||= '\N';
$method ||= '\N';
$source ||= '\N';
my $fid = $FID++;
my $gid = $GROUPID{lc join('',$group_class->[$i],$group_name->[$i])} ||= $GID++;
my $ftypeid = $FTYPEID{lc join('',$source,$method)} ||= $FTYPEID++;
my $bin = bin($start,$stop,$db->min_bin);
$FH{ FDATA() }->print( join("\t",$fid,$ref,$start,$stop,$bin,$ftypeid,$score,$strand,$phase,$gid,$target_start,$target_stop),"\n" );
if ($use_mysqlcmap){
my $feature_id = next_number(
db => $cmap_db,
table_name => 'cmap_feature',
id_field => 'feature_id',
)
or die 'No feature id';
my $direction = $strand eq '-' ? -1:1;
$FH{ FGROUP() }->print(
join("\t",$feature_id,$feature_id,'NULL',0, $group_name->[$i],0,0,'NULL',1,$direction, $group_class->[$i],)
,"\n"
) unless $DONE{"G$gid"}++;
}
else {
$FH{ FGROUP() }->print( join("\t",$gid,$group_class->[$i],$group_name->[$i]),"\n") unless $DONE{"G$gid"}++;
}
$FH{ FTYPE() }->print( join("\t",$ftypeid,$method,$source),"\n" ) unless $DONE{"T$ftypeid"}++;
foreach (@$attributes) {
my ($key,$value) = @$_;
my $attributeid = $ATTRIBUTEID{$key} ||= $ATTRIBUTEID++;
$FH{ FATTRIBUTE() }->print( join("\t",$attributeid,$key),"\n" ) unless $DONE{"A$attributeid"}++;
$FH{ FATTRIBUTE_TO_FEATURE() }->print( join("\t",$fid,$attributeid,$value),"\n");
}
bin/bp_fast_load_gff view on Meta::CPAN
my $group_class = $gclass->[$i];
my $group_name = $gname->[$i];
$group_class ||= '\N';
$group_name ||= '\N';
$target_start ||= '\N';
$target_stop ||= '\N';
$method ||= '\N';
$source ||= '\N';
my $fid = $FID++;
my $gid = $GROUPID{lc join($;,$group_class,$group_name)} ||= $GID++;
my $ftypeid = $FTYPEID{lc join($;,$source,$method)} ||= $FTYPEID++;
my $bin = bin($start,$stop,$db->min_bin);
$FH{ FDATA() }->print( join("\t",$fid,$ref,$start,$stop,$bin,$ftypeid,$score,$strand,$phase,$gid,$target_start,$target_stop),"\n" );
$FH{ FGROUP() }->print( join("\t",$gid,$group_class,$group_name),"\n" ) unless $DONE{"fgroup$;$gid"}++;
$FH{ FTYPE() }->print( join("\t",$ftypeid,$method,$source),"\n" ) unless $DONE{"ftype$;$ftypeid"}++;
foreach (@$attributes) {
my ($key,$value) = @$_;
my $attributeid = $ATTRIBUTEID{lc $key} ||= $ATTRIBUTEID++;
$FH{ FATTRIBUTE() }->print( join("\t",$attributeid,$key),"\n" ) unless $DONE{"fattribute$;$attributeid"}++;
$FH{ FATTRIBUTE_TO_FEATURE() }->print( join("\t",$fid,$attributeid,$value),"\n");
}
if ( $FEATURES % 1000 == 0) {
bin/bp_fast_load_gff view on Meta::CPAN
foreach (@files,@fasta_files_to_be_unlinked) {
unlink "$tmpdir/$_.$$";
}
}
# load copies of some of the tables into memory
sub load_tables {
my $dbh = shift;
print STDERR "loading normalized group, type and attribute information...";
$FID = 1 + get_max_id($dbh,'fdata','fid');
$GID = 1 + get_max_id($dbh,'fgroup','gid');
$FTYPEID = 1 + get_max_id($dbh,'ftype','ftypeid');
$ATTRIBUTEID = 1 + get_max_id($dbh,'fattribute','fattribute_id');
get_ids($dbh,\%DONE,\%GROUPID,'fgroup','gid','gclass','gname');
get_ids($dbh,\%DONE,\%FTYPEID,'ftype','ftypeid','fsource','fmethod');
get_ids($dbh,\%DONE,\%ATTRIBUTEID,'fattribute','fattribute_id','fattribute_name');
print STDERR "ok\n";
}
sub get_max_id {
my $dbh = shift;
my ($table,$id) = @_;
my $sql = "select max($id) from $table";
my $result = $dbh->selectcol_arrayref($sql) or die $dbh->errstr;
lib/Bio/DB/GFF.pm view on Meta::CPAN
my $self = shift;
my $id = ref($_[0]) eq 'ARRAY' ? $_[0] : \@_;
my %groups; # cache the groups we create to avoid consuming too much unecessary memory
my $features = [];
my $callback = sub { push @$features,$self->make_feature(undef,\%groups,@_) };
$self->_feature_by_id($id,'feature',$callback);
return wantarray ? @$features : $features->[0];
}
*fetch_feature_by_id = \&get_feature_by_id;
=head2 get_feature_by_gid
Title : get_feature_by_gid
Usage : $db->get_feature_by_gid($id)
Function: fetch segments by feature ID
Returns : a Bio::DB::GFF::Feature object
Args : the feature ID
Status : public
This method can be used to fetch a feature from the database using its
group ID. Not all GFF databases support IDs, so be careful with this.
The group ID is often more interesting than the feature ID, since
groups can be complex objects containing subobjects.
=cut
sub get_feature_by_gid {
my $self = shift;
my $id = ref($_[0]) eq 'ARRAY' ? $_[0] : \@_;
my %groups; # cache the groups we create to avoid consuming too much unecessary memory
my $features = [];
my $callback = sub { push @$features,$self->make_feature(undef,\%groups,@_) };
$self->_feature_by_id($id,'group',$callback);
return wantarray ? @$features : $features->[0];
}
*fetch_feature_by_gid = \&get_feature_by_gid;
=head2 delete_fattribute_to_features
Title : delete_fattribute_to_features
Usage : $db->delete_fattribute_to_features(@ids_or_features)
Function: delete one or more fattribute_to_features
Returns : count of fattribute_to_features deleted
Args : list of features or feature ids
Status : public
lib/Bio/DB/GFF.pm view on Meta::CPAN
return bless {ids=>$ids,db=>$db,type=>$type},$class;
}
sub next_seq {
my $self = shift;
my $next = shift @{$self->{ids}};
return unless $next;
my $name = ref($next) eq 'ARRAY' ? Bio::DB::GFF::Featname->new(@$next) : $next;
my $segment = $self->{type} eq 'name' ? $self->{db}->segment($name)
: $self->{type} eq 'feature' ? $self->{db}->fetch_feature_by_id($name)
: $self->{type} eq 'group' ? $self->{db}->fetch_feature_by_gid($name)
: $self->throw("Bio::DB::GFF::ID_Iterator called to fetch an unknown type of identifier");
$self->throw("id does not exist") unless $segment;
return $segment;
}
package Bio::DB::GFF::FeatureIterator;
$Bio::DB::GFF::FeatureIterator::VERSION = '1.7.4';
sub new {
my $self = shift;
my @features = @_;
lib/Bio/DB/GFF/Adaptor/dbi.pm view on Meta::CPAN
=cut
sub _feature_by_id {
my $self = shift;
my ($ids,$type,$callback) = @_;
$callback || $self->throw('must provide a callback argument');
my $select = $self->make_features_select_part;
my $from = $self->make_features_from_part;
my ($where,@args) = $type eq 'feature' ? $self->make_features_by_id_where_part($ids)
: $self->make_features_by_gid_where_part($ids);
my $join = $self->make_features_join_part;
my $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;
lib/Bio/DB/GFF/Adaptor/dbi.pm view on Meta::CPAN
$search_string =~ tr/*?//d;
my @words = $search_string =~ /(\w+)/g;
my $regex = join '|',@words;
my @searches = map {"fattribute_value LIKE '%${_}%'"} @words;
my $search = join(' OR ',@searches);
my $query = <<END;
SELECT distinct gclass,gname,fattribute_value,fmethod,fsource
FROM fgroup,fattribute_to_feature,fdata,ftype
WHERE fgroup.gid=fdata.gid
AND fdata.fid=fattribute_to_feature.fid
AND fdata.ftypeid=ftype.ftypeid
AND ($search)
END
;
my $sth = $self->dbh->do_query($query);
my @results;
while (my ($class,$name,$note,$method,$source) = $sth->fetchrow_array) {
next unless $class && $name; # sorry, ignore NULL objects
lib/Bio/DB/GFF/Adaptor/dbi.pm view on Meta::CPAN
return ("fgroup.gclass=? AND fgroup.gname=?",$class,$name);
}
}
sub make_features_by_alias_where_part {
my $self = shift;
my ($class,$name) = @_;
if ($name =~ /\*/) {
$name =~ tr/*/%/;
$name =~ s/_/\\_/g;
return ("fgroup.gclass=? AND fattribute_to_feature.fattribute_value LIKE ? AND fgroup.gid=fdata.gid AND fattribute.fattribute_name in ('Alias','Name') AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id AND fattribute_to_feature.fid=...
} else {
return ("fgroup.gclass=? AND fattribute_to_feature.fattribute_value=? AND fgroup.gid=fdata.gid AND fattribute.fattribute_name in ('Alias','Name') AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id AND fattribute_to_feature.fid=fdata...
}
}
sub make_features_by_attribute_where_part {
my $self = shift;
my $attributes = shift;
my @args;
my @sql;
foreach (keys %$attributes) {
lib/Bio/DB/GFF/Adaptor/dbi.pm view on Meta::CPAN
=cut
sub make_features_by_id_where_part {
my $self = shift;
my $ids = shift;
my $set = join ",",@$ids;
return ("fdata.fid IN ($set)");
}
=head2 make_features_by_gid_where_part
Title : make_features_by_id_where_part
Usage : $db->make_features_by_gid_where_part($ids)
Function: create the SQL fragment needed to select a set of features by their ids
Returns : a SQL fragment and bind arguments
Args : arrayref of IDs
Status : Protected
=cut
sub make_features_by_gid_where_part {
my $self = shift;
my $ids = shift;
my $set = join ",",@$ids;
return ("fgroup.gid IN ($set)");
}
=head2 make_features_from_part
Title : make_features_from_part
Usage : $string = $db->make_features_from_part()
Function: make from part of the features query
Returns : a string
Args : none
lib/Bio/DB/GFF/Adaptor/dbi.pm view on Meta::CPAN
This method creates the part of the features query that immediately
follows the WHERE keyword.
=cut
sub make_features_join_part {
my $self = shift;
my $options = shift || {};
return !$options->{attributes} ? <<END1 : <<END2;
fgroup.gid = fdata.gid
AND ftype.ftypeid = fdata.ftypeid
END1
fgroup.gid = fdata.gid
AND ftype.ftypeid = fdata.ftypeid
AND fattribute.fattribute_id=fattribute_to_feature.fattribute_id
AND fdata.fid=fattribute_to_feature.fid
END2
}
=head2 make_features_order_by_part
Title : make_features_order_by_part
Usage : ($query,@args) = $db->make_features_order_by_part()
lib/Bio/DB/GFF/Adaptor/dbi.pm view on Meta::CPAN
=cut
sub make_features_group_by_part {
my $self = shift;
my $options = shift || {};
if (my $att = $options->{attributes}) {
my $key_count = keys %$att;
return unless $key_count > 1;
return ("fdata.fid,fref,fstart,fstop,fsource,
fmethod,fscore,fstrand,fphase,gclass,gname,ftarget_start,
ftarget_stop,fdata.gid
HAVING count(fdata.fid) > ?",$key_count-1);
}
elsif (my $b = $options->{bin_width}) {
return "fref,fstart,fdata.ftypeid";
}
}
=head2 refseq_query
lib/Bio/DB/GFF/Adaptor/dbi.pm view on Meta::CPAN
=cut
# this method is called when needed to look up a feature's ID
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 fref=? 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;
}
=head2 make_abscoord_query
lib/Bio/DB/GFF/Adaptor/dbi.pm view on Meta::CPAN
$result;
}
# implement the _delete_groups() method
sub _delete_groups {
my $self = shift;
my @group_ids = @_;
my $dbh = $self->features_db;
my $fields = join ',',map{$dbh->quote($_)} @group_ids;
foreach my $gid (@group_ids){
my @features = $self->get_feature_by_gid($gid);
$self->delete_features(@features);
}
my $query = "delete from fgroup where gid in ($fields)";
warn "$query\n" if $self->debug;
my $result = $dbh->do($query);
defined $result or $self->throw($dbh->errstr);
$result;
}
# implement the _delete() method
sub _delete {
my $self = shift;
my $delete_spec = shift;
lib/Bio/DB/GFF/Adaptor/dbi/mysql.pm view on Meta::CPAN
use constant GETSEQCOORDS =><<END;
SELECT fref,
IF(ISNULL(gclass),'Sequence',gclass),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup
WHERE fgroup.gname=?
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
GROUP BY fref,fstrand,gname
END
;
use constant GETALIASCOORDS =><<END;
SELECT fref,
IF(ISNULL(gclass),'Sequence',gclass),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup,fattribute,fattribute_to_feature
WHERE fattribute_to_feature.fattribute_value=?
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
AND fattribute.fattribute_name='Alias'
AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id
AND fattribute_to_feature.fid=fdata.fid
GROUP BY fref,fstrand,gname
END
;
use constant GETALIASLIKE =><<END;
SELECT fref,
IF(ISNULL(gclass),'Sequence',gclass),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup,fattribute,fattribute_to_feature
WHERE fattribute_to_feature.fattribute_value LIKE ?
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
AND fattribute.fattribute_name='Alias'
AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id
AND fattribute_to_feature.fid=fdata.fid
GROUP BY fref,fstrand,gname
END
;
use constant GETFORCEDSEQCOORDS =><<END;
SELECT fref,
IF(ISNULL(gclass),'Sequence',gclass),
min(fstart),
max(fstop),
fstrand
FROM fdata,fgroup
WHERE fgroup.gname=?
AND fgroup.gclass=?
AND fdata.fref=?
AND fgroup.gid=fdata.gid
GROUP BY fref,fstrand
END
;
use constant FULLTEXTSEARCH => <<END;
SELECT distinct gclass,gname,fattribute_value,MATCH(fattribute_value) AGAINST (?) as score,fmethod,fsource
FROM fgroup,fattribute_to_feature,fdata,ftype
WHERE fgroup.gid=fdata.gid
AND fdata.fid=fattribute_to_feature.fid
AND fdata.ftypeid=ftype.ftypeid
AND MATCH(fattribute_value) AGAINST (?)
END
;
=head1 DESCRIPTION
This adaptor implements a specific mysql database schema that is
compatible with Bio::DB::GFF. It inherits from
lib/Bio/DB/GFF/Adaptor/dbi/mysql.pm view on Meta::CPAN
This is the feature data table. Its columns are:
-
fid feature ID (integer)
fref reference sequence name (string)
fstart start position relative to reference (integer)
fstop stop position relative to reference (integer)
ftypeid feature type ID (integer)
fscore feature score (float); may be null
fstrand strand; one of "+" or "-"; may be null
fphase phase; one of 0, 1 or 2; may be null
gid group ID (integer)
ftarget_start for similarity features, the target start position (integer)
ftarget_stop for similarity features, the target stop position (integer)
Note that it would be desirable to normalize the reference sequence
name, since there are usually many features that share the same
reference feature. However, in the current schema, query performance
suffers dramatically when this additional join is added.
=item fgroup
This is the group table. There is one row for each group. Columns:
gid the group ID (integer)
gclass the class of the group (string)
gname the name of the group (string)
The group table serves multiple purposes. As you might expect, it is
used to cluster features that logically belong together, such as the
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:
mysql> select * from fgroup where gname='sjj_2L52.1';
+-------+-------------+------------+
| gid | gclass | gname |
+-------+-------------+------------+
| 69736 | PCR_product | sjj_2L52.1 |
+-------+-------------+------------+
1 row in set (0.70 sec)
mysql> 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:
mysql> 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
lib/Bio/DB/GFF/Adaptor/dbi/mysql.pm view on Meta::CPAN
$b*(1+floor(fstart/$b)) as fstop,
IF(ISNULL(fsource),fmethod,concat(fmethod,':',fsource)),'bin',
count(*) as fscore,
'.','.','bin',
IF(ISNULL(fsource),concat(fref,':',fmethod),concat(fref,':',fmethod,':',fsource)),
NULL,NULL,NULL,NULL
END
;
} else {
$s = <<END;
fref,fstart,fstop,fsource,fmethod,fscore,fstrand,fphase,gclass,gname,ftarget_start,ftarget_stop,fdata.fid,fdata.gid
END
;
}
$s .= ",count(fdata.fid)" if $options->{attributes} && keys %{$options->{attributes}}>1;
$s;
}
# IMPORTANT NOTE:
# WHETHER OR NOT THIS WORKS IS CRITICALLY DEPENDENT ON THE RELATIVE MAGNITUDE OF THE
sub make_features_from_part {
my $self = shift;
my $sparse_types = shift;
my $options = shift || {};
my $sparse_groups = $options->{sparse_groups};
my $index = $sparse_groups ? ' USE INDEX(gid)'
: $sparse_types ? ' USE INDEX(ftypeid)'
: '';
return $options->{attributes} ? "fdata${index},ftype,fgroup,fattribute,fattribute_to_feature\n"
: "fdata${index},ftype,fgroup\n";
}
=head2 search_notes
Title : search_notes
Usage : @search_results = $db->search_notes("full text search string",$limit)
lib/Bio/DB/GFF/Adaptor/dbi/mysql.pm view on Meta::CPAN
create table fdata (
fid int not null auto_increment,
fref varchar(100) not null,
fstart int not null,
fstop int not null,
fbin double precision,
ftypeid int not null,
fscore float,
fstrand enum('+','-'),
fphase enum('0','1','2'),
gid int not null,
ftarget_start int,
ftarget_stop int,
primary key(fid),
unique index(fref,fbin,fstart,fstop,ftypeid,gid),
index(ftypeid),
index(gid)
) $engine=MyISAM
} # fdata table
}, # fdata
fgroup =>{
table=> qq{
create table fgroup (
gid int not null auto_increment,
gclass varchar(100),
gname varchar(100),
primary key(gid),
unique(gclass,gname)
) $engine=MyISAM
}
},
ftype => {
table=> qq{
create table ftype (
ftypeid int not null auto_increment,
fmethod varchar(100) not null,
lib/Bio/DB/GFF/Adaptor/dbi/mysql.pm view on Meta::CPAN
my $tables = join ', ',@tables;
$dbh->do("LOCK TABLES $tables");
}
# for my $table (qw(fdata)) {
# $dbh->do("alter table $table disable keys");
# }
my $lookup_type = $dbh->prepare_delayed('SELECT ftypeid FROM ftype WHERE fmethod=? AND fsource=?');
my $insert_type = $dbh->prepare_delayed('INSERT INTO ftype (fmethod,fsource) VALUES (?,?)');
my $lookup_group = $dbh->prepare_delayed('SELECT gid FROM fgroup WHERE gname=? AND gclass=?');
my $insert_group = $dbh->prepare_delayed('INSERT INTO fgroup (gname,gclass) VALUES (?,?)');
my $lookup_attribute = $dbh->prepare_delayed('SELECT fattribute_id FROM fattribute WHERE fattribute_name=?');
my $insert_attribute = $dbh->prepare_delayed('INSERT INTO fattribute (fattribute_name) VALUES (?)');
my $insert_attribute_value = $dbh->prepare_delayed('INSERT INTO fattribute_to_feature (fid,fattribute_id,fattribute_value) VALUES (?,?,?)');
my $insert_data = $dbh->prepare_delayed(<<END);
INSERT INTO fdata (fref,fstart,fstop,fbin,ftypeid,fscore,
fstrand,fphase,gid,ftarget_start,ftarget_stop)
VALUES(?,?,?,?,?,?,?,?,?,?,?)
END
;
$self->{load_stuff}{sth}{lookup_ftype} = $lookup_type;
$self->{load_stuff}{sth}{insert_ftype} = $insert_type;
$self->{load_stuff}{sth}{lookup_fgroup} = $lookup_group;
$self->{load_stuff}{sth}{insert_fgroup} = $insert_group;
$self->{load_stuff}{sth}{insert_fdata} = $insert_data;
lib/Bio/DB/GFF/Adaptor/dbi/mysql.pm view on Meta::CPAN
=cut
# this method is called when needed to look up a feature's ID
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 fref=? 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 _add_interval_stats_table {
my $self = shift;
my $schema = $self->schema;
lib/Bio/DB/GFF/Adaptor/dbi/mysqlcmap.pm view on Meta::CPAN
This is the feature data table. Its columns are:
-
fid feature ID (integer)
fref reference sequence name (string)
fstart start position relative to reference (integer)
fstop stop position relative to reference (integer)
ftypeid feature type ID (integer)
fscore feature score (float); may be null
fstrand strand; one of "+" or "-"; may be null
fphase phase; one of 0, 1 or 2; may be null
feature_id group ID used to be 'gid' (integer)
ftarget_start for similarity features, the target start position (integer)
ftarget_stop for similarity features, the target stop position (integer)
Note that it would be desirable to normalize the reference sequence
name, since there are usually many features that share the same
reference feature. However, in the current schema, query performance
suffers dramatically when this additional join is added.
=item cmap_feature (replaces fgroup)
lib/Bio/DB/GFF/Adaptor/dbi/mysqlcmap.pm view on Meta::CPAN
sub make_features_order_by_part {
my $self = shift;
my $options = shift || {};
return "cmap_feature.feature_name";
}
=head2 create_cmap_viewer_link
Title : create_cmap_viewer_link
Usage : $link_str = $db->create_cmap_viewer_link(data_source=>$ds,group_id=>$gid)
Function:
Returns :
Args :
Status :
=cut
sub create_cmap_viewer_link {
my $self = shift;
my %args = @_;
my $data_source = $args{'data_source'};
my $gid = $args{'group_id'};
my $link_str = undef;
my $db = $self->features_db;
my $sql_str = qq[
select f.feature_name,
f.feature_type_accession feature_type_aid,
m.accession_id as map_aid,
ms.accession_id as map_set_aid
from cmap_feature f,
cmap_map m,
cmap_map_set ms
where f.map_id=m.map_id
and ms.map_set_id=m.map_set_id
and f.feature_id=$gid
];
my $result_ref = $db->selectrow_hashref($sql_str,{ Columns => {} });
if ( $result_ref ) {
$link_str='/cgi-bin/cmap/viewer?ref_map_set_aid='
. $result_ref->{'map_set_aid'}
. '&ref_map_aids='
. $result_ref->{'map_aid'}
. '&data_source='
lib/Bio/DB/GFF/Adaptor/dbi/oracle.pm view on Meta::CPAN
use constant GETSEQCOORDS =><<END;
SELECT fref,
NVL(gclass,'Sequence'),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup
WHERE fgroup.gname=?
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
GROUP BY fref,fstrand,gclass,gname
END
;
use constant GETALIASCOORDS =><<END;
SELECT fref,
NVL(gclass,'Sequence'),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup,fattribute,fattribute_to_feature
WHERE fattribute_to_feature.fattribute_value=?
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
AND fattribute.fattribute_name='Alias'
AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id
AND fattribute_to_feature.fid=fdata.fid
GROUP BY fref,fstrand,gclass,gname
END
;
use constant GETALIASLIKE =><<END;
SELECT fref,
NVL(gclass,'Sequence'),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup,fattribute,fattribute_to_feature
WHERE fattribute_to_feature.fattribute_value LIKE ?
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
AND fattribute.fattribute_name='Alias'
AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id
AND fattribute_to_feature.fid=fdata.fid
GROUP BY fref,fstrand,gname
END
;
use constant GETFORCEDSEQCOORDS =><<END;
SELECT fref,
NVL(gclass,'Sequence'),
min(fstart),
max(fstop),
fstrand
FROM fdata,fgroup
WHERE fgroup.gname=?
AND fgroup.gclass=?
AND fdata.fref=?
AND fgroup.gid=fdata.gid
GROUP BY fref,fstrand,gclass
END
;
########################
# moved from mysqlopt.pm
########################
# this is the largest that any reference sequence can be (100 megabases)
use constant MAX_BIN => 100_000_000;
lib/Bio/DB/GFF/Adaptor/dbi/oracle.pm view on Meta::CPAN
This is the feature data table. Its columns are:
fid feature ID (integer)
fref reference sequence name (string)
fstart start position relative to reference (integer)
fstop stop position relative to reference (integer)
ftypeid feature type ID (integer)
fscore feature score (float); may be null
fstrand strand; one of "+" or "-"; may be null
fphase phase; one of 0, 1 or 2; may be null
gid group ID (integer)
ftarget_start for similarity features, the target start position (integer)
ftarget_stop for similarity features, the target stop position (integer)
Note that it would be desirable to normalize the reference sequence
name, since there are usually many features that share the same
reference feature. However, in the current schema, query performance
suffers dramatically when this additional join is added.
=item fgroup
This is the group table. There is one row for each group. Columns:
gid the group ID (integer)
gclass the class of the group (string)
gname the name of the group (string)
The group table serves multiple purposes. As you might expect, it is
used to cluster features that logically belong together, such as the
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
lib/Bio/DB/GFF/Adaptor/dbi/oracle.pm view on Meta::CPAN
create table fdata (
fid INTEGER NOT NULL,
fref VARCHAR(100) DEFAULT '' NOT NULL,
fstart INTEGER DEFAULT '0' NOT NULL,
fstop INTEGER DEFAULT '0' NOT NULL,
fbin NUMBER DEFAULT '0.000000' NOT NULL,
ftypeid INTEGER DEFAULT '0' NOT NULL,
fscore NUMBER ,
fstrand VARCHAR2(3) CHECK (fstrand IN ('+','-')),
fphase VARCHAR2(3) CHECK (fphase IN ('0','1','2')),
gid INTEGER DEFAULT '0' NOT NULL,
ftarget_start INTEGER ,
ftarget_stop INTEGER ,
CONSTRAINT fdata_pk PRIMARY KEY (fid)
)
}, # fdata table
index=>{
fdata_fref_idx => q{
CREATE UNIQUE INDEX fdata_fref_idx ON fdata (fref,fbin,fstart,fstop,ftypeid,gid)
},
fdata_ftypeid_idx => q{
CREATE INDEX fdata_ftypeid_idx ON fdata (ftypeid)
},
fdata_gid_idx => q{
CREATE INDEX fdata_gid_idx ON fdata (gid)
}
}, # fdata indexes
sequence=> {
fdata_fid_sq => q{
CREATE SEQUENCE fdata_fid_sq START WITH 1
}
}, # fdata sequences
trigger=> {
lib/Bio/DB/GFF/Adaptor/dbi/oracle.pm view on Meta::CPAN
}
}# fdata triggers
}, # fdata
fgroup => {
table => q{
CREATE TABLE fgroup (
gid INTEGER NOT NULL,
gclass VARCHAR(100) ,
gname VARCHAR(100) ,
CONSTRAINT fgroup_pk PRIMARY KEY (gid)
)
}, # fgroup table
index => {
fgroup_gclass_idx => q{
CREATE UNIQUE INDEX fgroup_gclass_idx ON fgroup (gclass,gname)
}
}, # fgroup indexes
sequence => {
fgroup_gid_sq => q{
CREATE SEQUENCE fgroup_gid_sq START WITH 1
}
}, # fgroup sequences
trigger => {
fgroup_gid_ai => q{
CREATE OR REPLACE TRIGGER fgroup_gid_ai
BEFORE INSERT ON fgroup
FOR EACH ROW WHEN (new.gid IS NULL OR new.gid = 0)
BEGIN
SELECT fgroup_gid_sq.nextval INTO :new.gid FROM dual;
END;
}
} # fgroup triggers
}, # fgroup
ftype => {
table => q{
CREATE TABLE ftype (
ftypeid INTEGER NOT NULL,
lib/Bio/DB/GFF/Adaptor/dbi/oracle.pm view on Meta::CPAN
my @tables = map { "$_ WRITE"} $self->tables;
my $tables = join ', ',@tables;
$dbh->do("LOCK TABLES $tables");
}
my $lookup_type = $dbh->prepare_delayed('SELECT ftypeid FROM ftype WHERE fmethod=? AND fsource=?');
my $insert_type = $dbh->prepare_delayed('INSERT INTO ftype (fmethod,fsource) VALUES (?,?)');
my $sequence_type = (keys %{$schema->{ftype}{sequence}})[0];
my $insertid_type = $dbh->prepare_delayed("SELECT $sequence_type.CURRVAL FROM dual");
my $lookup_group = $dbh->prepare_delayed('SELECT gid FROM fgroup WHERE gname=? AND gclass=?');
my $insert_group = $dbh->prepare_delayed('INSERT INTO fgroup (gname,gclass) VALUES (?,?)');
my $sequence_group = (keys %{$schema->{fgroup}{sequence}})[0];
my $insertid_group = $dbh->prepare_delayed("SELECT $sequence_group.CURRVAL FROM dual");
my $lookup_attribute = $dbh->prepare_delayed('SELECT fattribute_id FROM fattribute WHERE fattribute_name=?');
my $insert_attribute = $dbh->prepare_delayed('INSERT INTO fattribute (fattribute_name) VALUES (?)');
my $sequence_attribute = (keys %{$schema->{fattribute}{sequence}})[0];
my $insertid_attribute = $dbh->prepare_delayed("SELECT $sequence_attribute.CURRVAL FROM dual");
my $insert_attribute_value = $dbh->prepare_delayed('INSERT INTO fattribute_to_feature (fid,fattribute_id,fattribute_value) VALUES (?,?,?)');
my $insert_data = $dbh->prepare_delayed(<<END);
INSERT INTO fdata (fref,fstart,fstop,fbin,ftypeid,fscore,
fstrand,fphase,gid,ftarget_start,ftarget_stop)
VALUES(?,?,?,?,?,?,?,?,?,?,?)
END
;
my $delete_existing_data = $dbh->prepare_delayed('DELETE FROM fdata WHERE fref=? AND fstart=? AND fstop=? AND fbin=? AND ftypeid=? AND GID=?');
my $sequence_data = (keys %{$schema->{fdata}{sequence}})[0];
my $insertid_data = $dbh->prepare_delayed("SELECT $sequence_data.CURRVAL FROM dual");
$self->{load_stuff}{sth}{lookup_ftype} = $lookup_type;
lib/Bio/DB/GFF/Adaptor/dbi/oracle.pm view on Meta::CPAN
$search_string =~ tr/*?//d;
my @words = $search_string =~ /(\w+)/g;
my $regex = join '|',@words;
my @searches = map {"fattribute_value LIKE '%${_}%'"} @words;
my $search = join(' OR ',@searches);
my $query = <<END;
SELECT distinct gclass,gname,fattribute_value,fmethod,fsource
FROM fgroup,fattribute_to_feature,fdata,ftype
WHERE fgroup.gid=fdata.gid
AND fdata.fid=fattribute_to_feature.fid
AND fdata.ftypeid=ftype.ftypeid
AND ($search)
END
;
my $sth = $self->dbh->do_query($query);
my @results;
while (my ($class,$name,$note,$method,$source) = $sth->fetchrow_array) {
next unless $class && $name; # sorry, ignore NULL objects
lib/Bio/DB/GFF/Adaptor/dbi/oracle.pm view on Meta::CPAN
$b*(1+floor(fstart/$b)) as fstop,
NVL2(fsource,fmethod||':'||fsource,fmethod),'bin',
count(*) as fscore,
'.','.','bin',
NVL2(fsource , fref||':'||fmethod||':'||fsource , fref||':'||fmethod),
NULL,NULL,NULL,NULL
END
;
} else {
$s = <<END;
fref,fstart,fstop,fsource,fmethod,fscore,fstrand,fphase,gclass,gname,ftarget_start,ftarget_stop,fdata.fid,fdata.gid
END
;
}
$s .= ",count(fdata.fid)" if $options->{attributes} && keys %{$options->{attributes}}>1;
$s;
}
sub make_features_from_part_bkup {
my $self = shift;
my $sparse = shift;
lib/Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
use constant GETSEQCOORDS =><<END;
SELECT fref,
COALESCE(gclass,'Sequence'),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup
WHERE lower(fgroup.gname) = lower(?)
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
GROUP BY fref,fstrand,gclass,gname
END
;
use constant GETALIASCOORDS =><<END;
SELECT fref,
COALESCE(gclass,'Sequence'),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup,fattribute,fattribute_to_feature
WHERE lower(fattribute_to_feature.fattribute_value)=lower(?)
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
AND fattribute.fattribute_name='Alias'
AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id
AND fattribute_to_feature.fid=fdata.fid
GROUP BY fref,fstrand,gclass,gname
END
;
use constant GETALIASLIKE =><<END;
SELECT fref,
COALESCE(gclass,'Sequence'),
min(fstart),
max(fstop),
fstrand,
gname
FROM fdata,fgroup,fattribute,fattribute_to_feature
WHERE lower(fattribute_to_feature.fattribute_value) LIKE lower(?)
AND fgroup.gclass=?
AND fgroup.gid=fdata.gid
AND fattribute.fattribute_name='Alias'
AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id
AND fattribute_to_feature.fid=fdata.fid
GROUP BY fref,fstrand,gname
END
;
use constant GETFORCEDSEQCOORDS =><<END;
SELECT fref,
COALESCE(gclass,'Sequence'),
min(fstart),
max(fstop),
fstrand
FROM fdata,fgroup
WHERE lower(fgroup.gname) = lower(?)
AND fgroup.gclass=?
AND lower(fdata.fref) = lower(?)
AND fgroup.gid=fdata.gid
GROUP BY fref,fstrand,gclass
END
;
use constant FULLTEXTWILDCARD => <<END;
SELECT distinct gclass,gname,fattribute_value
FROM fgroup,fattribute_to_feature,fdata
WHERE fgroup.gid=fdata.gid
AND fdata.fid=fattribute_to_feature.fid
AND lower(fattribute_to_feature.fattribute_value) LIKE lower(?)
END
;
########################
# moved from mysqlopt.pm
########################
# this is the largest that any reference sequence can be (100 megabases)
lib/Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
This is the feature data table. Its columns are:
fid feature ID (integer)
fref reference sequence name (string)
fstart start position relative to reference (integer)
fstop stop position relative to reference (integer)
ftypeid feature type ID (integer)
fscore feature score (float); may be null
fstrand strand; one of "+" or "-"; may be null
fphase phase; one of 0, 1 or 2; may be null
gid group ID (integer)
ftarget_start for similarity features, the target start position (integer)
ftarget_stop for similarity features, the target stop position (integer)
Note that it would be desirable to normalize the reference sequence
name, since there are usually many features that share the same
reference feature. However, in the current schema, query performance
suffers dramatically when this additional join is added.
=item fgroup
This is the group table. There is one row for each group. Columns:
gid the group ID (integer)
gclass the class of the group (string)
gname the name of the group (string)
The group table serves multiple purposes. As you might expect, it is
used to cluster features that logically belong together, such as the
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
lib/Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
CREATE TABLE "fdata" (
"fid" serial NOT NULL,
"fref" character varying(100) DEFAULT '' NOT NULL,
"fstart" integer DEFAULT '0' NOT NULL,
"fstop" integer DEFAULT '0' NOT NULL,
"fbin" double precision DEFAULT '0.000000' NOT NULL,
"ftypeid" integer DEFAULT '0' NOT NULL,
"fscore" double precision DEFAULT NULL,
"fstrand" character varying(3) DEFAULT NULL,
"fphase" character varying(3) DEFAULT NULL,
"gid" integer DEFAULT '0' NOT NULL,
"ftarget_start" integer DEFAULT NULL,
"ftarget_stop" integer DEFAULT NULL,
CONSTRAINT chk_fdata_fstrand CHECK (fstrand IN ('+','-')),
CONSTRAINT chk_fdata_fphase CHECK (fphase IN ('0','1','2')),
CONSTRAINT pk_fdata PRIMARY KEY (fid)
)
}, # fdata table
#CONSTRAINT fref_fdata UNIQUE (fref, fbin, fstart, fstop, ftypeid, gid)
# fdata_fref_idx => q{ CREATE UNIQUE INDEX fdata_fref_idx ON fdata (fref,fbin,fstart,fstop,ftypeid,gid)},
index=>{
fdata_fref_idx => q{
CREATE INDEX fdata_fref_idx ON fdata (fref,fbin,fstart,fstop,ftypeid,gid)
},
fdata_ftypeid_idx => q{
CREATE INDEX fdata_ftypeid_idx ON fdata (ftypeid)
},
fdata_gid_idx => q{
CREATE INDEX fdata_gid_idx ON fdata (gid)
}
}, # fdata indexes
}, # fdata
fgroup => {
table => q{
CREATE TABLE "fgroup" (
"gid" serial NOT NULL,
"gclass" character varying(100) DEFAULT NULL,
"gname" character varying(100) DEFAULT NULL,
CONSTRAINT pk_fgroup PRIMARY KEY (gid)
)
}, # fgroup table
index => {
fgroup_gclass_idx => q{
CREATE UNIQUE INDEX fgroup_gclass_idx ON fgroup (gclass,gname)
},
fgroup_gname_idx => q{
CREATE INDEX fgroup_gname_idx ON fgroup(gname)
},
lib/Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
if ($self->lock_on_load) {
my @tables = map { "$_ WRITE"} $self->tables;
my $tables = join ', ',@tables;
$dbh->do("LOCK TABLES $tables");
}
my $lookup_type = $dbh->prepare_delayed('SELECT ftypeid FROM ftype WHERE fmethod=? AND fsource=?');
my $insert_type = $dbh->prepare_delayed('INSERT INTO ftype (fmethod,fsource) VALUES (?,?)');
my $insertid_type = $dbh->prepare_delayed("SELECT currval('ftype_ftypeid_seq')");
my $lookup_group = $dbh->prepare_delayed('SELECT gid FROM fgroup WHERE lower(gname)=lower(?) AND gclass=?');
my $insert_group = $dbh->prepare_delayed('INSERT INTO fgroup (gname,gclass) VALUES (?,?)');
my $insertid_group = $dbh->prepare_delayed("SELECT currval('fgroup_gid_seq')");
my $lookup_attribute = $dbh->prepare_delayed('SELECT fattribute_id FROM fattribute WHERE fattribute_name=?');
my $insert_attribute = $dbh->prepare_delayed('INSERT INTO fattribute (fattribute_name) VALUES (?)');
my $insertid_attribute = $dbh->prepare_delayed("SELECT currval('fattribute_fattribute_id_seq')");
my $insert_attribute_value = $dbh->prepare_delayed('INSERT INTO fattribute_to_feature (fid,fattribute_id,fattribute_value) VALUES (?,?,?)');
my $insert_data = $dbh->prepare_delayed(<<END);
INSERT INTO fdata (fref,fstart,fstop,fbin,ftypeid,fscore,
fstrand,fphase,gid,ftarget_start,ftarget_stop)
VALUES(?,?,?,?,?,?,?,?,?,?,?)
END
;
my $delete_existing_data = $dbh->prepare_delayed('DELETE FROM fdata WHERE fref=? AND fstart=? AND fstop=? AND fbin=? AND ftypeid=? AND GID=?');
my $insertid_data = $dbh->prepare_delayed("SELECT currval('fdata_fid_seq')");
$self->{load_stuff}{sth}{lookup_ftype} = $lookup_type;
$self->{load_stuff}{sth}{insert_ftype} = $insert_type;
$self->{load_stuff}{sth}{insertid_ftype} = $insertid_type;
$self->{load_stuff}{sth}{lookup_fgroup} = $lookup_group;
lib/Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
'bin',
count(*) as fscore,
'.','.','bin',
CASE WHEN fsource IS NULL THEN fref||':'||fmethod
ELSE fref||':'||fmethod||':'||fsource,
NULL,NULL,NULL,NULL
END
;
} else {
$s = <<END;
fref,fstart,fstop,fsource,fmethod,fscore,fstrand,fphase,gclass,fgroup.gname,ftarget_start,ftarget_stop,fdata.fid,fdata.gid
END
;
}
$s .= ",count(fdata.fid)" if $options->{attributes} && keys %{$options->{attributes}}>1;
$s;
}
sub make_features_from_part_bkup {
my $self = shift;
my $sparse = shift;
lib/Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
$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
lib/Bio/DB/GFF/Adaptor/dbi/pg.pm view on Meta::CPAN
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;
my $delete_spec = shift;
lib/Bio/DB/GFF/Adaptor/dbi/pg_fts.pm view on Meta::CPAN
=cut
# a simple postgres adaptor
use strict;
use Bio::DB::GFF::Adaptor::dbi;
use base qw(Bio::DB::GFF::Adaptor::dbi::pg);
use constant FULLTEXTSEARCH => <<END;
SELECT distinct gclass,gname,fattribute_value,fmethod,fsource
FROM fgroup,fattribute_to_feature,fdata,ftype
WHERE fgroup.gid=fdata.gid
AND fdata.fid=fattribute_to_feature.fid
AND fdata.ftypeid=ftype.ftypeid
AND (fattribute_to_feature.idxfti @@ to_tsquery('default', ?))
END
;
use constant FULLTEXTWILDCARD => <<END;
SELECT distinct gclass,gname,fattribute_value,fmethod,fsource
FROM fgroup,fattribute_to_feature,fdata,ftype
WHERE fgroup.gid=fdata.gid
AND fdata.fid=fattribute_to_feature.fid
AND fdata.ftypeid=ftype.ftypeid
AND lower(fattribute_to_feature.fattribute_value) LIKE lower(?)
END
;
sub new {
my $class = shift;
my $self = $class->SUPER::new(@_);
return $self;
t/BioDBGFF.t view on Meta::CPAN
is($overlap[1]->strand,-1);
# testing feature id and group_id
my $tf = $overlap[0];
ok(defined $tf->id);
my $t1 = $db->fetch_feature_by_id($tf->id);
is($t1->id,$tf->id);
SKIP: {
if (defined $tf->group_id) {
my $t2 = $db->fetch_feature_by_gid($tf->group_id);
is($t2->group_id,$tf->group_id);
is($t2->group_id,$t1->group_id);
} else {
skip("fetch_feature_by_gid() not implemented by this adaptor",2);
}
}
$segment1 = $db->segment('-class' => 'Transcript',
'-name' => 'trans-4',
'-start' => 1,
'-stop' => 6000);
is($segment1->strand,1);
@overlap = sort {$a->start <=> $b->start} $segment1->features('transcript');
is($overlap[0]->name,'trans-4');
( run in 0.895 second using v1.01-cache-2.11-cpan-5735350b133 )