Bio-DB-GFF
view release on metacpan or search on metacpan
lib/Bio/DB/GFF/Adaptor/dbi/mysqlcmap.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 cmap_feature.feature_id field joins with the fdata.feature_id field.
Examples:
mysql> select * from cmap_feature where feature_name='sjj_2L52.1';
+--------------+-------------+--------------+
| feature_id | gclass | feature_name |
+--------------+-------------+--------------+
| 69736 | PCR_product | sjj_2L52.1 |
+--------------+-------------+--------------+
1 row in set (0.70 sec)
mysql> select fref,fstart,fstop from fdata,cmap_feature
where gclass='PCR_product' and feature_name = 'sjj_2L52.1'
and fdata.feature_id=cmap_feature.feature_id;
+---------------+--------+-------+
| 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,cmap_feature,ftype
where gclass='PCR_product'
and feature_name = 'sjj_2L52.1'
and fdata.feature_id=cmap_feature.feature_id
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:mysql" is added to the database DSN identifier
automatically if it is not there already.
Argument Description
-------- -----------
-dsn the DBI data source, e.g. 'dbi:mysql:ens0040' or "ens0040"
-user username for authentication
-pass the password for authentication
=cut
lib/Bio/DB/GFF/Adaptor/dbi/mysqlcmap.pm view on Meta::CPAN
UNIQUE KEY map_set_id (map_set_id,species_id,short_name,accession_id),
KEY cmap_map_set_idx (can_be_reference_map,is_enabled,species_id,display_order,published_on,short_name)
) TYPE=MyISAM;
} # table
},
cmap_next_number => {
table=>q{
create table cmap_next_number (
table_name varchar(40) NOT NULL default '',
next_number int(11) NOT NULL default '0',
PRIMARY KEY (table_name)
) TYPE=MyISAM;
}, # table
insert=>{next_num=>q[ insert into cmap_next_number (table_name,next_number) VALUES ('cmap_feature',82);]}
},
cmap_species => {
table=>q{
create table cmap_species (
species_id int(11) NOT NULL default '0',
accession_id varchar(20) NOT NULL default '',
common_name varchar(64) NOT NULL default '',
full_name varchar(64) NOT NULL default '',
display_order int(11) NOT NULL default '1',
PRIMARY KEY (species_id),
KEY acc_id_species_id (accession_id,species_id)
) TYPE=MyISAM;
} # table
},
cmap_xref => {
table=>q{
create table cmap_xref (
xref_id int(11) NOT NULL default '0',
table_name varchar(30) NOT NULL default '',
object_id int(11) default NULL,
display_order int(11) NOT NULL default '1',
xref_name varchar(200) NOT NULL default '',
xref_url text NOT NULL,
PRIMARY KEY (xref_id),
KEY table_name (table_name,object_id,display_order)
) TYPE=MyISAM;
} # table
},
);
return \%schema;
}
=head2 make_classes_query
Title : make_classes_query
Usage : ($query,@args) = $db->make_classes_query
Function: return query fragment for generating list of reference classes
Returns : a query and args
Args : none
Status : public
=cut
sub make_classes_query {
my $self = shift;
return 'SELECT DISTINCT gclass FROM cmap_feature WHERE NOT ISNULL(gclass)';
}
=head2 setup_load
Title : setup_load
Usage : $db->setup_load
Function: called before load_gff_line()
Returns : void
Args : none
Status : protected
This method performs schema-specific initialization prior to loading a
set of GFF records. It prepares a set of DBI statement handlers to be
used in loading the data.
=cut
sub setup_load {
my $self = shift;
my $dbh = $self->features_db;
if ($self->lock_on_load) {
my @tables = map { "$_ WRITE"} $self->tables;
my $tables = join ', ',@tables;
$dbh->do("LOCK TABLES $tables");
}
#xx1
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 feature_id FROM cmap_feature WHERE feature_name=? AND gclass=?');
my $insert_group = $dbh->prepare_delayed(' INSERT into cmap_feature (feature_id, accession_id,feature_name, gclass ) VALUES (?,feature_id,?,?)');
my $aux_insert_group = $dbh->prepare_delayed(' update cmap_next_number set next_number = next_number +1 where table_name=\'cmap_feature\'');
my $next_id_group = $dbh->prepare_delayed('select next_number from cmap_next_number where table_name=\'cmap_feature\'');
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,feature_id,ftarget_start,ftarget_stop)
VALUES(?,?,?,?,?,?,?,?,?,?,?)
END
;
$self->{load_stuff}{sth}{lookup_ftype} = $lookup_type;
lib/Bio/DB/GFF/Adaptor/dbi/mysqlcmap.pm view on Meta::CPAN
# get the object ID from a named table
sub get_table_id {
my $self = shift;
my $table = shift;
my @ids = @_;
# irritating warning for null id
my $id_key;
{
local $^W=0;
$id_key = join ':',@ids;
}
my $s = $self->{load_stuff};
my $sth = $s->{sth};
my $dbh = $self->features_db;
unless (defined($s->{$table}{$id_key})) {
#########################################
# retrieval of the last inserted id is now located at the adaptor and not in caching_handle
#######################################
if ( (my $result = $sth->{"lookup_$table"}->execute(@ids)) > 0) {
$s->{$table}{$id_key} = ($sth->{"lookup_$table"}->fetchrow_array)[0];
} else {
if (defined($sth->{"next_id_$table"})){
$sth->{"insert_$table"}->execute(3,'string1','string2');
# Can't use auto incrementing
$sth->{"next_id_$table"}->execute();
$s->{$table}{$id_key} = ($sth->{"next_id_$table"}->fetchrow_array)[0];
if ($s->{$table}{$id_key}){
$sth->{"insert_$table"}->execute($s->{$table}{$id_key},@ids);
$sth->{"aux_insert_$table"}->execute() if $sth->{"aux_insert_$table"};
}
}
else{
$sth->{"insert_$table"}->execute(@ids);
$s->{$table}{$id_key} = $self->insertid($sth->{"insert_$table"}) unless $s->{$table}{$id_key};
$sth->{"aux_insert_$table"}->execute() if $sth->{"aux_insert_$table"};
}
}
}
my $id = $s->{$table}{$id_key};
unless (defined $id) {
warn "No $table id for $id_key ",$dbh->errstr," Record skipped.\n";
return;
}
$id;
}
#-----------------------------------
=head2 make_features_by_name_where_part
Title : make_features_by_name_where_part
Usage : $db->make_features_by_name_where_part
Function: create 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 =~ /\*/) {
$name =~ tr/*/%/;
return ("cmap_feature.gclass=? AND cmap_feature.feature_name LIKE ?",$class,$name);
} else {
return ("cmap_feature.gclass=? AND cmap_feature.feature_name=?",$class,$name);
}
}
=head2 make_features_join_part
Title : make_features_join_part
Usage : $string = $db->make_features_join_part()
Function: make join part of the features query
Returns : a string
Args : none
Status : protected
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;
cmap_feature.feature_id = fdata.feature_id
AND ftype.ftypeid = fdata.ftypeid
END1
cmap_feature.feature_id = fdata.feature_id
AND ftype.ftypeid = fdata.ftypeid
AND fattribute.fattribute_id=fattribute_to_feature.fattribute_id
AND fdata.fid=fattribute_to_feature.fid
END2
}
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{
lib/Bio/DB/GFF/Adaptor/dbi/mysqlcmap.pm view on Meta::CPAN
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.
=cut
sub search_notes {
my $self = shift;
my ($search_string,$limit) = @_;
$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,feature_name as gname,fattribute_value
FROM cmap_feature,fattribute_to_feature,fdata
WHERE cmap_feature.feature_id=fdata.feature_id
AND fdata.fid=fattribute_to_feature.fid
END
;
$query .= " AND ($search) " if ($search);
my $sth = $self->dbh->do_query($query);
my @results;
while (my ($class,$name,$note) = $sth->fetchrow_array) {
next unless $class && $name; # sorry, ignore NULL objects
my @matches = $note =~ /($regex)/g;
my $relevance = 10*@matches;
my $featname = Bio::DB::GFF::Featname->new($class=>$name);
push @results,[$featname,$note,$relevance];
last if $limit && @results >= $limit;
}
@results;
}
# sub search_notes {
# my $self = shift;
# my ($search_string,$limit) = @_;
# my $query = FULLTEXTSEARCH;
# $query .= " limit $limit" if defined $limit;
# my $sth = $self->dbh->do_query($query,$search_string,$search_string);
# my @results;
# while (my ($class,$name,$note,$relevance) = $sth->fetchrow_array) {
# next unless $class && $name; # sorry, ignore NULL objects
# $relevance = sprintf("%.2f",$relevance); # trim long floats
# my $featname = Bio::DB::GFF::Featname->new($class=>$name);
# push @results,[$featname,$note,$relevance];
# }
# @results;
# }
=head2 make_features_order_by_part
Title : make_features_order_by_part
Usage : ($query,@args) = $db->make_features_order_by_part()
Function: make the ORDER BY part of the features() query
Returns : a SQL fragment and bind arguments, if any
Args : none
Status : protected
This method creates the part of the features query that immediately
follows the ORDER BY part of the query issued by features() and
related methods.
=cut
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='
. $data_source
. '&highlight='
.$result_ref->{'feature_name'}
. '&feature_type_'
( run in 0.584 second using v1.01-cache-2.11-cpan-364913b4093 )