GFF/Adaptor/dbi/mysql.pm

filter: Statementstmt 15.0% 32 / 213 Branchbran 0.0% 0 / 62 Conditioncond 0.0% 0 / 22 MC/DCmcdc 0.0% 0 / 14 Subroutinesub 31.2% 10 / 32 Podpod 57.1% 12 / 21 totaltotal 14.8% 54 / 364 CC 42 SCAR 35.4
CC
42
Cov
11%
load_gff_line
132.0
search_notes
42.0
get_abscoords_bkup
30.0
CRAP 34.5
« GFF/Adaptor/dbi/pg_fts.pm ↑ Summary GFF/Adaptor/memory.pm »
1 package Bio::DB::GFF::Adaptor::dbi::mysql;
2 4 $Bio::DB::GFF::Adaptor::dbi::mysql::VERSION = '1.7.4';
3 =head1 NAME
4  
5 Bio::DB::GFF::Adaptor::dbi::mysql -- Database adaptor for a specific mysql schema
6  
7 =head1 SYNOPSIS
8  
9 See L<Bio::DB::GFF>
10  
11 =cut
12  
13 # a simple mysql adaptor
14 4 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
15 4 ▶use Bio::DB::GFF::Util::Rearrange; # for rearrange()
Subroutine BEGIN: called CC=1 SCAR=0.0
16 4 ▶use Bio::DB::GFF::Util::Binning;
Subroutine BEGIN: called CC=1 SCAR=0.0
17 4 ▶use base qw(Bio::DB::GFF::Adaptor::dbi);
Subroutine BEGIN: called CC=1 SCAR=0.0
18  
19 4 ▶use constant MAX_SEGMENT => 100_000_000; # the largest a segment can get
Subroutine BEGIN: called CC=1 SCAR=0.0
20  
21 4 ▶use constant GETSEQCOORDS =><<END;
Subroutine BEGIN: called CC=1 SCAR=0.0
22 SELECT fref,
23 IF(ISNULL(gclass),'Sequence',gclass),
24 min(fstart),
25 max(fstop),
26 fstrand,
27 gname
28 FROM fdata,fgroup
29 WHERE fgroup.gname=?
30 AND fgroup.gclass=?
31 AND fgroup.gid=fdata.gid
32 GROUP BY fref,fstrand,gname
33 END
34 ;
35  
36 4 ▶use constant GETALIASCOORDS =><<END;
Subroutine BEGIN: called CC=1 SCAR=0.0
37 SELECT fref,
38 IF(ISNULL(gclass),'Sequence',gclass),
39 min(fstart),
40 max(fstop),
41 fstrand,
42 gname
43 FROM fdata,fgroup,fattribute,fattribute_to_feature
44 WHERE fattribute_to_feature.fattribute_value=?
45 AND fgroup.gclass=?
46 AND fgroup.gid=fdata.gid
47 AND fattribute.fattribute_name='Alias'
48 AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id
49 AND fattribute_to_feature.fid=fdata.fid
50 GROUP BY fref,fstrand,gname
51 END
52 ;
53  
54 4 ▶use constant GETALIASLIKE =><<END;
Subroutine BEGIN: called CC=1 SCAR=0.0
55 SELECT fref,
56 IF(ISNULL(gclass),'Sequence',gclass),
57 min(fstart),
58 max(fstop),
59 fstrand,
60 gname
61 FROM fdata,fgroup,fattribute,fattribute_to_feature
62 WHERE fattribute_to_feature.fattribute_value LIKE ?
63 AND fgroup.gclass=?
64 AND fgroup.gid=fdata.gid
65 AND fattribute.fattribute_name='Alias'
66 AND fattribute_to_feature.fattribute_id=fattribute.fattribute_id
67 AND fattribute_to_feature.fid=fdata.fid
68 GROUP BY fref,fstrand,gname
69 END
70 ;
71  
72 4 ▶use constant GETFORCEDSEQCOORDS =><<END;
Subroutine BEGIN: called CC=1 SCAR=0.0
73 SELECT fref,
74 IF(ISNULL(gclass),'Sequence',gclass),
75 min(fstart),
76 max(fstop),
77 fstrand
78 FROM fdata,fgroup
79 WHERE fgroup.gname=?
80 AND fgroup.gclass=?
81 AND fdata.fref=?
82 AND fgroup.gid=fdata.gid
83 GROUP BY fref,fstrand
84 END
85 ;
86  
87 4 ▶use constant FULLTEXTSEARCH => <<END;
Subroutine BEGIN: called CC=1 SCAR=0.0
88 SELECT distinct gclass,gname,fattribute_value,MATCH(fattribute_value) AGAINST (?) as score,fmethod,fsource
89 FROM fgroup,fattribute_to_feature,fdata,ftype
90 WHERE fgroup.gid=fdata.gid
91 AND fdata.fid=fattribute_to_feature.fid
92 AND fdata.ftypeid=ftype.ftypeid
93 AND MATCH(fattribute_value) AGAINST (?)
94 END
95 ;
96  
97 =head1 DESCRIPTION
98  
99 This adaptor implements a specific mysql database schema that is
100 compatible with Bio::DB::GFF. It inherits from
101 Bio::DB::GFF::Adaptor::dbi, which itself inherits from Bio::DB::GFF.
102  
103 The schema uses several tables:
104  
105 =over 4
106  
107 =item fdata
108  
109 This is the feature data table. Its columns are:
110 -
111 fid feature ID (integer)
112 fref reference sequence name (string)
113 fstart start position relative to reference (integer)
114 fstop stop position relative to reference (integer)
115 ftypeid feature type ID (integer)
116 fscore feature score (float); may be null
117 fstrand strand; one of "+" or "-"; may be null
118 fphase phase; one of 0, 1 or 2; may be null
119 gid group ID (integer)
120 ftarget_start for similarity features, the target start position (integer)
121 ftarget_stop for similarity features, the target stop position (integer)
122  
123 Note that it would be desirable to normalize the reference sequence
124 name, since there are usually many features that share the same
125 reference feature. However, in the current schema, query performance
126 suffers dramatically when this additional join is added.
127  
128 =item fgroup
129  
130 This is the group table. There is one row for each group. Columns:
131  
132 gid the group ID (integer)
133 gclass the class of the group (string)
134 gname the name of the group (string)
135  
136 The group table serves multiple purposes. As you might expect, it is
137 used to cluster features that logically belong together, such as the
138 multiple exons of the same transcript. It is also used to assign a
139 name and class to a singleton feature. Finally, the group table is
140 used to identify the target of a similarity hit. This is consistent
141 with the way in which the group field is used in the GFF version 2
142 format.
143  
144 The fgroup.gid field joins with the fdata.gid field.
145  
146 Examples:
147  
148 mysql> select * from fgroup where gname='sjj_2L52.1';
149 +-------+-------------+------------+
150 | gid | gclass | gname |
151 +-------+-------------+------------+
152 | 69736 | PCR_product | sjj_2L52.1 |
153 +-------+-------------+------------+
154 1 row in set (0.70 sec)
155  
156 mysql> select fref,fstart,fstop from fdata,fgroup
157 where gclass='PCR_product' and gname = 'sjj_2L52.1'
158 and fdata.gid=fgroup.gid;
159 +---------------+--------+-------+
160 | fref | fstart | fstop |
161 +---------------+--------+-------+
162 | CHROMOSOME_II | 1586 | 2355 |
163 +---------------+--------+-------+
164 1 row in set (0.03 sec)
165  
166 =item ftype
167  
168 This table contains the feature types, one per row. Columns are:
169  
170 ftypeid the feature type ID (integer)
171 fmethod the feature type method name (string)
172 fsource the feature type source name (string)
173  
174 The ftype.ftypeid field joins with the fdata.ftypeid field. Example:
175  
176 mysql> select fref,fstart,fstop,fmethod,fsource from fdata,fgroup,ftype
177 where gclass='PCR_product'
178 and gname = 'sjj_2L52.1'
179 and fdata.gid=fgroup.gid
180 and fdata.ftypeid=ftype.ftypeid;
181 +---------------+--------+-------+-------------+-----------+
182 | fref | fstart | fstop | fmethod | fsource |
183 +---------------+--------+-------+-------------+-----------+
184 | CHROMOSOME_II | 1586 | 2355 | PCR_product | GenePairs |
185 +---------------+--------+-------+-------------+-----------+
186 1 row in set (0.08 sec)
187  
188 =item fdna
189  
190 This table holds the raw DNA of the reference sequences. It has three
191 columns:
192  
193 fref reference sequence name (string)
194 foffset offset of this sequence
195 fdna the DNA sequence (longblob)
196  
197 To overcome problems loading large blobs, DNA is automatically
198 fragmented into multiple segments when loading, and the position of
199 each segment is stored in foffset. The fragment size is controlled by
200 the -clump_size argument during initialization.
201  
202 =item fattribute_to_feature
203  
204 This table holds "attributes", which are tag/value pairs stuffed into
205 the GFF line. The first tag/value pair is treated as the group, and
206 anything else is treated as an attribute (weird, huh?).
207  
208 CHR_I assembly_tag Finished 2032 2036 . + . Note "Right: cTel33B"
209 CHR_I assembly_tag Polymorphism 668 668 . + . Note "A->C in cTel33B"
210  
211 The columns of this table are:
212  
213 fid feature ID (integer)
214 fattribute_id ID of the attribute (integer)
215 fattribute_value text of the attribute (text)
216  
217 The fdata.fid column joins with fattribute_to_feature.fid.
218  
219 =item fattribute
220  
221 This table holds the normalized names of the attributes. Fields are:
222  
223 fattribute_id ID of the attribute (integer)
224 fattribute_name Name of the attribute (varchar)
225  
226 =back
227  
228 =head2 Data Loading Methods
229  
230 In addition to implementing the abstract SQL-generating methods of
231 Bio::DB::GFF::Adaptor::dbi, this module also implements the data
232 loading functionality of Bio::DB::GFF.
233  
234 =cut
235  
236  
237 =head2 new
238  
239 Title : new
240 Usage : $db = Bio::DB::GFF->new(@args)
241 Function: create a new adaptor
242 Returns : a Bio::DB::GFF object
243 Args : see below
244 Status : Public
245  
246 The new constructor is identical to the "dbi" adaptor's new() method,
247 except that the prefix "dbi:mysql" is added to the database DSN identifier
248 automatically if it is not there already.
249  
250 Argument Description
251 -------- -----------
252  
253 -dsn the DBI data source, e.g. 'dbi:mysql:ens0040' or "ens0040"
254  
255 -user username for authentication
256  
257 -pass the password for authentication
258  
259 =cut
260  
261 #'
262  
263 0 ▶sub new {
Subroutine new: not called CC=3 SCAR=24.8
Pod documented
264 0 my $class = shift;
265 0 my ($dsn,$other) = rearrange([
266 [qw(FEATUREDB DB DSN)],
267 ],@_);
268 0 ▶ $dsn = "dbi:mysql:$dsn" if !ref($dsn) && $dsn !~ /^(?:dbi|DBI):/;
Branch: 1 branch
BranchTrueFalseTotal
unless ref $dsn or $dsn =~ /^(?:dbi|DBI):/ 0 0 0
Condition0%0/3
ref $dsn or $dsn =~ /^(?:dbi|DBI):/
l!l&&r!l&&!r
000
MC/DC0%0/2
ref $dsn or $dsn =~ /^(?:dbi|DBI):/
ref $dsn $dsn =~ /^(?:dbi|DBI):/
Truth table0%0/3
A or B
A= ref $dsnB= $dsn =~ /^(?:dbi|DBI):/
ABresult
1X1
011
000
269 0 my $self = $class->SUPER::new(-dsn=>$dsn,%$other);
270 0 $self;
271 }
272  
273 =head2 get_dna
274  
275 Title : get_dna
276 Usage : $string = $db->get_dna($name,$start,$stop,$class)
277 Function: get DNA string
278 Returns : a string
279 Args : name, class, start and stop of desired segment
280 Status : Public
281  
282 This method performs the low-level fetch of a DNA substring given its
283 name, class and the desired range. This should probably be moved to
284 the parent class.
285  
286 =cut
287  
288 0 ▶sub getseqcoords_query {
Subroutine getseqcoords_query: not called CC=1 SCAR=6.9
Pod undocumented
289 0 my $self = shift;
290 0 return GETSEQCOORDS ;
291 }
292  
293 0 ▶sub getaliascoords_query{
Subroutine getaliascoords_query: not called CC=1 SCAR=6.9
Pod undocumented
294 0 my $self = shift;
295 0 return GETALIASCOORDS ;
296 }
297  
298  
299 0 ▶sub getforcedseqcoords_query{
Subroutine getforcedseqcoords_query: not called CC=1 SCAR=6.9
Pod undocumented
300 0 my $self = shift;
301 0 return GETFORCEDSEQCOORDS ;
302 }
303  
304  
305 0 ▶sub getaliaslike_query{
Subroutine getaliaslike_query: not called CC=1 SCAR=6.9
Pod undocumented
306 0 my $self = shift;
307 0 return GETALIASLIKE ;
308 }
309  
310  
311 # override parent
312 0 ▶sub get_abscoords_bkup {
Subroutine get_abscoords_bkup: not called CC=5 SCAR=34.0
Pod undocumented
313 0 my $self = shift;
314 0 my ($name,$class,$refseq) = @_;
315  
316 0 my $result = $self->SUPER::get_abscoords(@_);
317 0 ▶ return $result if $result;
Branch: 1 branch
BranchTrueFalseTotal
if $result 0 0 0
318  
319 0 my $sth;
320 0 ▶ if ($name =~ s/\*/%/g) {
Branch: 1 branch
BranchTrueFalseTotal
if ($name =~ s/\*/%/g) { } 0 0 0
321 0 $sth = $self->dbh->do_query(GETALIASLIKE,$name,$class);
322 } else {
323 0 $sth = $self->dbh->do_query(GETALIASCOORDS,$name,$class);
324 }
325 0 my @result;
326 0 ▶ while (my @row = $sth->fetchrow_array) { push @result,\@row }
Branch: 1 branch
BranchTrueFalseTotal
if (my(@row) = $sth->fetchrow_array) 0 0 0
327 0 $sth->finish;
328  
329 0 ▶ if (@result == 0) {
Branch: 1 branch
BranchTrueFalseTotal
if (@result == 0) { } 0 0 0
330 0 $self->error("$name not found in database");
331 0 return;
332 } else {
333 0 return \@result;
334 }
335  
336 }
337  
338  
339  
340 0 ▶sub make_features_select_part {
Subroutine make_features_select_part: not called CC=5 SCAR=34.0
Pod documented
341 0 my $self = shift;
342 0 ▶ my $options = shift || {};
Condition0%0/2
shift() || {}
l!l
00
MC/DC0%0/1
shift() || {}
shift()
Truth table0%0/2
A
A= shift()
Aresult
11
00
343 0 my $s;
344 0 ▶ if (my $b = $options->{bin_width}) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $b = $options->{'bin_width'}) { } 0 0 0
345  
346 0 $s = <<END;
347 fref,
348 1+$b*floor(fstart/$b) as fstart,
349 $b*(1+floor(fstart/$b)) as fstop,
350 IF(ISNULL(fsource),fmethod,concat(fmethod,':',fsource)),'bin',
351 count(*) as fscore,
352 '.','.','bin',
353 IF(ISNULL(fsource),concat(fref,':',fmethod),concat(fref,':',fmethod,':',fsource)),
354 NULL,NULL,NULL,NULL
355 END
356 ;
357 } else {
358 0 $s = <<END;
359 fref,fstart,fstop,fsource,fmethod,fscore,fstrand,fphase,gclass,gname,ftarget_start,ftarget_stop,fdata.fid,fdata.gid
360 END
361 ;
362 }
363 0 ▶ $s .= ",count(fdata.fid)" if $options->{attributes} && keys %{$options->{attributes}}>1;
Branch: 1 branch
BranchTrueFalseTotal
if $options->{"attributes"} and keys %{$$options{"attributes"};} > 1 0 0 0
Condition0%0/3
$options->{"attributes"} and keys %{$$options{"attributes"};} > 1
!ll&&!rl&&r
000
MC/DC0%0/2
$options->{"attributes"} and keys %{$$options{"attributes"};} > 1
$options->{"attributes"} keys %{$$options{"attributes"};} > 1
Truth table0%0/3
A and B
A= $options->{"attributes"}B= keys %{$$options{"attributes"};} > 1
ABresult
0X0
100
111
364 0 $s;
365 }
366  
367  
368 # IMPORTANT NOTE:
369 # WHETHER OR NOT THIS WORKS IS CRITICALLY DEPENDENT ON THE RELATIVE MAGNITUDE OF THE
370 0 ▶sub make_features_from_part {
Subroutine make_features_from_part: not called CC=5 SCAR=34.0
Pod documented
371 0 my $self = shift;
372 0 my $sparse_types = shift;
373 0 ▶ my $options = shift || {};
Condition0%0/2
shift() || {}
l!l
00
MC/DC0%0/1
shift() || {}
shift()
Truth table0%0/2
A
A= shift()
Aresult
11
00
374 0 my $sparse_groups = $options->{sparse_groups};
375 0 ▶ my $index = $sparse_groups ? ' USE INDEX(gid)'
Branch: 2 branches
BranchTrueFalseTotal
$sparse_groups ? : 0 0 0
$sparse_types ? : 0 0 0
376 : $sparse_types ? ' USE INDEX(ftypeid)'
377 : '';
378 0 ▶ return $options->{attributes} ? "fdata${index},ftype,fgroup,fattribute,fattribute_to_feature\n"
Branch: 1 branch
BranchTrueFalseTotal
$options->{'attributes'} ? : 0 0 0
379 : "fdata${index},ftype,fgroup\n";
380 }
381  
382 =head2 search_notes
383  
384 Title : search_notes
385 Usage : @search_results = $db->search_notes("full text search string",$limit)
386 Function: Search the notes for a text string, using mysql full-text search
387 Returns : array of results
388 Args : full text search string, and an optional row limit
389 Status : public
390  
391 This is a mysql-specific method. Given a search string, it performs a
392 full-text search of the notes table and returns an array of results.
393 Each row of the returned array is a arrayref containing the following fields:
394  
395 column 1 A Bio::DB::GFF::Featname object, suitable for passing to segment()
396 column 2 The text of the note
397 column 3 A relevance score.
398  
399 =cut
400  
401 0 ▶sub search_notes {
Subroutine search_notes: not called CC=6 SCAR=37.4
Pod documented
402 0 my $self = shift;
403 0 my ($search_string,$limit) = @_;
404  
405 0 $search_string =~ tr/*?//d;
406  
407 0 my $query = FULLTEXTSEARCH;
408 0 ▶ $query .= " limit $limit" if defined $limit;
Branch: 1 branch
BranchTrueFalseTotal
if defined $limit 0 0 0
409 0 my $sth = $self->dbh->do_query($query,$search_string,$search_string);
410 0 my @results;
411 0 ▶ while (my ($class,$name,$note,$relevance,$method,$source) = $sth->fetchrow_array) {
Branch: 1 branch
BranchTrueFalseTotal
if (() = $sth->fetchrow_array) 0 0 0
412 0 ▶ next unless $class && $name; # sorry, ignore NULL objects
Branch: 1 branch
BranchTrueFalseTotal
unless $class and $name 0 0 0
Condition0%0/3
$class and $name
!ll&&!rl&&r
000
MC/DC0%0/2
$class and $name
$class $name
Truth table0%0/3
A and B
A= $classB= $name
ABresult
0X0
100
111
413 0 $relevance = sprintf("%.2f",$relevance); # trim long floats
414 0 my $featname = Bio::DB::GFF::Featname->new($class=>$name);
415 0 my $type = Bio::DB::GFF::Typename->new($method,$source);
416 0 push @results,[$featname,$note,$relevance,$type];
417 }
418  
419 #added result filtering so that this method returns the expected results
420 #this section of code used to be in GBrowse's do_keyword_search method
421  
422 0 my $match_sub = 'sub {';
423 0 foreach (split /\s+/,$search_string) {
424 0 $match_sub .= "return unless \$_[0] =~ /\Q$_\E/i; ";
425 }
426 0 $match_sub .= "};";
427 0 my $match = eval $match_sub;
428  
429 0 my @matches = grep { $match->($_->[1]) } @results;
430  
431 0 return @matches;
432 }
433  
434  
435  
436 ################################ loading and initialization ##################################
437  
438 =head2 schema
439  
440 Title : schema
441 Usage : $schema = $db->schema
442 Function: return the CREATE script for the schema
443 Returns : a list of CREATE statemetns
444 Args : none
445 Status : protected
446  
447 This method returns a list containing the various CREATE statements
448 needed to initialize the database tables.
449  
450 =cut
451  
452 0 ▶sub schema {
Subroutine schema: not called CC=2 SCAR=17.9
Pod documented
453 0 my $self = shift;
454 0 my $dbh = $self->dbh;
455 0 my ($version) = $dbh->selectrow_array('select version()');
456 0 my ($major, $minor) = split /\./, $version;
457 0 $version = "$major.$minor";
458 0 ▶ my $engine = $version >= 4.1 ? 'ENGINE' : 'TYPE';
Branch: 1 branch
BranchTrueFalseTotal
$version >= 4.1 ? : 0 0 0
459 0 my %schema = (
460 fdata =>{
461 table=> qq{
462 create table fdata (
463 fid int not null auto_increment,
464 fref varchar(100) not null,
465 fstart int not null,
466 fstop int not null,
467 fbin double precision,
468 ftypeid int not null,
469 fscore float,
470 fstrand enum('+','-'),
471 fphase enum('0','1','2'),
472 gid int not null,
473 ftarget_start int,
474 ftarget_stop int,
475 primary key(fid),
476 unique index(fref,fbin,fstart,fstop,ftypeid,gid),
477 index(ftypeid),
478 index(gid)
479 ) $engine=MyISAM
480 } # fdata table
481 }, # fdata
482  
483 fgroup =>{
484 table=> qq{
485 create table fgroup (
486 gid int not null auto_increment,
487 gclass varchar(100),
488 gname varchar(100),
489 primary key(gid),
490 unique(gclass,gname)
491 ) $engine=MyISAM
492 }
493 },
494  
495 ftype => {
496 table=> qq{
497 create table ftype (
498 ftypeid int not null auto_increment,
499 fmethod varchar(100) not null,
500 fsource varchar(100),
501 primary key(ftypeid),
502 index(fmethod),
503 index(fsource),
504 unique ftype (fmethod,fsource)
505 ) $engine=MyISAM
506 } #ftype table
507 }, #ftype
508  
509 fdna => {
510 table=> qq{
511 create table fdna (
512 fref varchar(100) not null,
513 foffset int(10) unsigned not null,
514 fdna longblob,
515 primary key(fref,foffset)
516 ) $engine=MyISAM
517 } # fdna table
518 },#fdna
519  
520 fmeta => {
521 table=> qq{
522 create table fmeta (
523 fname varchar(255) not null,
524 fvalue varchar(255) not null,
525 primary key(fname)
526 ) $engine=MyISAM
527 } # fmeta table
528 },#fmeta
529  
530 fattribute => {
531 table=> qq{
532 create table fattribute (
533 fattribute_id int(10) unsigned not null auto_increment,
534 fattribute_name varchar(255) not null,
535 primary key(fattribute_id)
536 ) $engine=MyISAM
537 } #fattribute table
538 },#fattribute
539  
540 fattribute_to_feature => {
541 table=> qq{
542 create table fattribute_to_feature (
543 fid int(10) not null,
544 fattribute_id int(10) not null,
545 fattribute_value text,
546 key(fid,fattribute_id),
547 key(fattribute_value(48)),
548 fulltext(fattribute_value)
549 ) $engine=MyISAM
550 } # fattribute_to_feature table
551 },# fattribute_to_feature
552  
553 finterval_stats => {
554 table=> qq{
555 create table finterval_stats (
556 ftypeid integer not null,
557 fref varchar(100) not null,
558 fbin integer not null,
559 fcum_count integer not null,
560 primary key(ftypeid,fref,fbin)
561 ) $engine=MyISAM
562 } # finterval_stats table
563 },# finterval_stats
564  
565 );
566 0 return \%schema;
567 }
568  
569  
570  
571 =head2 make_classes_query
572  
573 Title : make_classes_query
574 Usage : ($query,@args) = $db->make_classes_query
575 Function: return query fragment for generating list of reference classes
576 Returns : a query and args
577 Args : none
578 Status : public
579  
580 =cut
581  
582 0 ▶sub make_classes_query {
Subroutine make_classes_query: not called CC=1 SCAR=6.9
Pod documented
583 0 my $self = shift;
584 0 return 'SELECT DISTINCT gclass FROM fgroup WHERE NOT ISNULL(gclass)';
585 }
586  
587  
588 =head2 make_meta_set_query
589  
590 Title : make_meta_set_query
591 Usage : $sql = $db->make_meta_set_query
592 Function: return SQL fragment for setting a meta parameter
593 Returns : SQL fragment
594 Args : none
595 Status : public
596  
597 By default this does nothing; meta parameters are not stored or
598 retrieved.
599  
600 =cut
601  
602 0 ▶sub make_meta_set_query {
Subroutine make_meta_set_query: not called CC=1 SCAR=6.9
Pod documented
603 0 return 'REPLACE INTO fmeta VALUES (?,?)';
604 }
605  
606 =head2 setup_load
607  
608 Title : setup_load
609 Usage : $db->setup_load
610 Function: called before load_gff_line()
611 Returns : void
612 Args : none
613 Status : protected
614  
615 This method performs schema-specific initialization prior to loading a
616 set of GFF records. It prepares a set of DBI statement handlers to be
617 used in loading the data.
618  
619 =cut
620  
621 0 ▶sub setup_load {
Subroutine setup_load: not called CC=2 SCAR=17.9
Pod documented
622 0 my $self = shift;
623  
624 0 my $dbh = $self->features_db;
625  
626 0 ▶ if ($self->lock_on_load) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->lock_on_load) 0 0 0
627 0 my @tables = map { "$_ WRITE"} $self->tables;
628 0 my $tables = join ', ',@tables;
629 0 $dbh->do("LOCK TABLES $tables");
630 }
631 # for my $table (qw(fdata)) {
632 # $dbh->do("alter table $table disable keys");
633 # }
634  
635 0 my $lookup_type = $dbh->prepare_delayed('SELECT ftypeid FROM ftype WHERE fmethod=? AND fsource=?');
636 0 my $insert_type = $dbh->prepare_delayed('INSERT INTO ftype (fmethod,fsource) VALUES (?,?)');
637  
638 0 my $lookup_group = $dbh->prepare_delayed('SELECT gid FROM fgroup WHERE gname=? AND gclass=?');
639 0 my $insert_group = $dbh->prepare_delayed('INSERT INTO fgroup (gname,gclass) VALUES (?,?)');
640  
641 0 my $lookup_attribute = $dbh->prepare_delayed('SELECT fattribute_id FROM fattribute WHERE fattribute_name=?');
642 0 my $insert_attribute = $dbh->prepare_delayed('INSERT INTO fattribute (fattribute_name) VALUES (?)');
643 0 my $insert_attribute_value = $dbh->prepare_delayed('INSERT INTO fattribute_to_feature (fid,fattribute_id,fattribute_value) VALUES (?,?,?)');
644  
645 0 my $insert_data = $dbh->prepare_delayed(<<END);
646 INSERT INTO fdata (fref,fstart,fstop,fbin,ftypeid,fscore,
647 fstrand,fphase,gid,ftarget_start,ftarget_stop)
648 VALUES(?,?,?,?,?,?,?,?,?,?,?)
649 END
650 ;
651  
652  
653 0 $self->{load_stuff}{sth}{lookup_ftype} = $lookup_type;
654 0 $self->{load_stuff}{sth}{insert_ftype} = $insert_type;
655 0 $self->{load_stuff}{sth}{lookup_fgroup} = $lookup_group;
656 0 $self->{load_stuff}{sth}{insert_fgroup} = $insert_group;
657 0 $self->{load_stuff}{sth}{insert_fdata} = $insert_data;
658 0 $self->{load_stuff}{sth}{lookup_fattribute} = $lookup_attribute;
659 0 $self->{load_stuff}{sth}{insert_fattribute} = $insert_attribute;
660 0 $self->{load_stuff}{sth}{insert_fattribute_value} = $insert_attribute_value;
661 0 $self->{load_stuff}{types} = {};
662 0 $self->{load_stuff}{groups} = {};
663 0 $self->{load_stuff}{counter} = 0;
664 }
665  
666 =head2 load_gff_line
667  
668 Title : load_gff_line
669 Usage : $db->load_gff_line($fields)
670 Function: called to load one parsed line of GFF
671 Returns : true if successfully inserted
672 Args : hashref containing GFF fields
673 Status : protected
674  
675 This method is called once per line of the GFF and passed a series of
676 parsed data items that are stored into the hashref $fields. The keys are:
677  
678 ref reference sequence
679 source annotation source
680 method annotation method
681 start annotation start
682 stop annotation stop
683 score annotation score (may be undef)
684 strand annotation strand (may be undef)
685 phase annotation phase (may be undef)
686 group_class class of annotation's group (may be undef)
687 group_name ID of annotation's group (may be undef)
688 target_start start of target of a similarity hit
689 target_stop stop of target of a similarity hit
690 attributes array reference of attributes, each of which is a [tag=>value] array ref
691  
692 =cut
693  
694 0 ▶sub load_gff_line {
Subroutine load_gff_line: not called CC=11 SCAR=48.8
Pod documented
695 0 my $self = shift;
696 0 my $gff = shift;
697  
698 0 my $s = $self->{load_stuff};
699 0 my $dbh = $self->features_db;
700 0 local $dbh->{PrintError} = 0;
701  
702 0 ▶ defined(my $typeid = $self->get_table_id('ftype', $gff->{method} => $gff->{source})) or return;
Branch: 1 branch
BranchTrueFalseTotal
defined(my $typeid = $self->get_table_id("ftype", $gff->{"method"}, $gff->{"source"})) or return 0 0 0
703 0 ▶ defined(my $groupid = $self->get_table_id('fgroup',$gff->{gname} => $gff->{gclass})) or return;
Branch: 1 branch
BranchTrueFalseTotal
defined(my $groupid = $self->get_table_id("fgroup", $gff->{"gname"}, $gff->{"gclass"})) or return 0 0 0
704  
705 0 ▶ if ($gff->{stop}-$gff->{start}+1 > $self->max_bin) {
Branch: 1 branch
BranchTrueFalseTotal
if ($gff->{"stop"} - $gff->{"start"} + 1 > $self->max_bin) 0 0 0
706 0 warn "$gff->{gclass}:$gff->{gname} is ",$gff->{stop}-$gff->{start}+1,
707 " bp long, but the maximum indexable feature is set to ",$self->max_bin," bp.\n";
708 0 warn "Please set the maxbin value to a length at least as large as the largest feature you wish to store.\n";
709 0 warn "\n* You will need to reinitialize the database from scratch.\n";
710 0 warn "* With the Perl API you do this using the -max_bin argument to \$db->initialize().\n";
711 0 warn "* With the command-line tools you do with this with --maxfeature option.\n";
712 }
713  
714 0 my $bin = bin($gff->{start},$gff->{stop},$self->min_bin);
715 my $result = $s->{sth}{insert_fdata}->execute($gff->{ref},
716 $gff->{start},$gff->{stop},$bin,
717 $typeid,
718 $gff->{score},$gff->{strand},$gff->{phase},
719 $groupid,
720 0 $gff->{tstart},$gff->{tstop});
721  
722 0 ▶ warn $dbh->errstr,"\n" && return unless $result;
Branch: 1 branch
BranchTrueFalseTotal
unless $result 0 0 0
723  
724 my $fid = $dbh->{mysql_insertid}
725 0 ▶ || $self->get_feature_id($gff->{ref},$gff->{start},$gff->{stop},$typeid,$groupid);
Condition0%0/3
$dbh->{'mysql_insertid'} || $self->get_feature_id($gff->{'ref'}, $gff->{'start'}, $gff->{'stop'}, $typeid, $groupid)
l!l&&r!l&&!r
000
MC/DC0%0/2
$dbh->{'mysql_insertid'} || $self->get_feature_id($gff->{'ref'}, $gff->{'start'}, $gff->{'stop'}, $typeid, $groupid)
$dbh->{'mysql_insertid'} $self->get_feature_id($gff->{'ref'}, $gff->{'start'}, $gff->{'stop'}, $typeid, $groupid)
Truth table0%0/3
A || B
A= $dbh->{'mysql_insertid'}B= $self->get_feature_id($gff->{'ref'}, $gff->{'start'}, $gff->{'stop'}, $typeid, $groupid)
ABresult
1X1
011
000
726  
727  
728 # insert attributes
729 0 foreach (@{$gff->{attributes}}) {
730 0 ▶ defined(my $attribute_id = $self->get_table_id('fattribute',$_->[0])) or return;
Branch: 1 branch
BranchTrueFalseTotal
defined(my $attribute_id = $self->get_table_id("fattribute", $_->[0])) or return 0 0 0
731 0 $s->{sth}{insert_fattribute_value}->execute($fid,$attribute_id,$_->[1]);
732 }
733  
734 0 ▶ if ( (++$s->{counter} % 1000) == 0) {
Branch: 1 branch
BranchTrueFalseTotal
if (++$s->{"counter"} % 1000 == 0) 0 0 0
735 0 print STDERR "$s->{counter} records loaded...";
736 0 ▶ print STDERR -t STDOUT && !$ENV{EMACS} ? "\r" : "\n";
Branch: 1 branch
BranchTrueFalseTotal
-t STDOUT && !$ENV{'EMACS'} ? : 0 0 0
Condition0%0/3
-t STDOUT and not $ENV{"EMACS"}
!ll&&!rl&&r
000
MC/DC0%0/2
-t STDOUT and not $ENV{"EMACS"}
-t STDOUT not $ENV{"EMACS"}
Truth table0%0/3
A and not(B)
A= -t STDOUTB= not $ENV{"EMACS"}
ABresult
0X0
100
111
737 }
738  
739 0 $fid;
740 }
741  
742 0 ▶sub finish_load {
Subroutine finish_load: not called CC=1 SCAR=6.9
Pod documented
743 0 my $self = shift;
744 0 my $dbh = $self->features_db;
745 0 local $dbh->{PrintError} = 0;
746 # for my $table (qw(fdata)) {
747 # $dbh->do("alter table $table enable keys");
748 # }
749 0 $self->SUPER::finish_load;
750 }
751  
752  
753 0 ▶sub insert_sequence {
Subroutine insert_sequence: not called CC=3 SCAR=24.8
Pod undocumented
754 0 my $self = shift;
755 0 my($id,$offset,$seq) = @_;
756 my $sth = $self->{_insert_sequence}
757 0 ▶ ||= $self->dbh->prepare_delayed('replace into fdna values (?,?,?)');
Condition0%0/3
$self->{"_insert_sequence"} ||= $self->dbh->prepare_delayed("replace into fdna values (?,?,?)")
l!l&&r!l&&!r
000
MC/DC0%0/2
$self->{"_insert_sequence"} ||= $self->dbh->prepare_delayed("replace into fdna values (?,?,?)")
$self->{"_insert_sequence"} $self->dbh->prepare_delayed("replace into fdna values (?,?,?)")
Truth table0%0/3
A ||= B
A= $self->{"_insert_sequence"}B= $self->dbh->prepare_delayed("replace into fdna values (?,?,?)")
ABresult
1X1
011
000
758 0 ▶ $sth->execute($id,$offset,$seq) or $self->throw($sth->errstr);
Branch: 1 branch
BranchTrueFalseTotal
$sth->execute($id, $offset, $seq) or $self->throw($sth->errstr) 0 0 0
759 }
760  
761  
762 =head2 get_table_id
763  
764 Title : get_table_id
765 Usage : $integer = $db->get_table_id($table,@ids)
766 Function: get the ID of a group or type
767 Returns : an integer ID or undef
768 Args : none
769 Status : private
770  
771 This internal method is called by load_gff_line to look up the integer
772 ID of an existing feature type or group. The arguments are the name
773 of the table, and two string identifiers. For feature types, the
774 identifiers are the method and source. For groups, the identifiers
775 are group name and class.
776  
777 This method requires that a statement handler named I<lookup_$table>,
778 have been created previously by setup_load(). It is here to overcome
779 deficiencies in mysql's INSERT syntax.
780  
781 =cut
782  
783 #'
784 # get the object ID from a named table
785 0 ▶sub get_table_id {
Subroutine get_table_id: not called CC=5 SCAR=34.0
Pod documented
786 0 my $self = shift;
787 0 my $table = shift;
788 0 my @ids = @_;
789  
790 # irritating warning for null id
791 0 my $id_key;
792 {
793 0 local $^W=0;
794 0 $id_key = join ':',@ids;
795 }
796  
797 0 my $s = $self->{load_stuff};
798 0 my $sth = $s->{sth};
799 0 my $dbh = $self->features_db;
800  
801 0 ▶ unless (defined($s->{$table}{$id_key})) {
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $s->{$table}{$id_key}) 0 0 0
802  
803 #########################################
804 # retrieval of the last inserted id is now located at the adaptor and not in caching_handle
805 #######################################
806 0 ▶ if ( (my $result = $sth->{"lookup_$table"}->execute(@ids)) > 0) {
Branch: 1 branch
BranchTrueFalseTotal
if ((my $result = $$sth{"lookup_$table"}->execute(@ids)) > 0) { } 0 0 0
807 0 $s->{$table}{$id_key} = ($sth->{"lookup_$table"}->fetchrow_array)[0];
808 } else {
809 $sth->{"insert_$table"}->execute(@ids)
810 0 ▶ && ($s->{$table}{$id_key} = $self->insertid($sth->{"insert_$table"}));
Branch: 1 branch
BranchTrueFalseTotal
$$sth{"insert_$table"}->execute(@ids) and $s->{$table}{$id_key} = $self->insertid($$sth{"insert_$table"}) 0 0 0
811 #&& ($s->{$table}{$id_key} = $sth->{"insert_$table"}{sth}{mysql_insertid});
812 #&& ($s->{$table}{$id_key} = $sth->{"insert_$table"}->insertid);
813 }
814 }
815  
816 0 my $id = $s->{$table}{$id_key};
817 0 ▶ unless (defined $id) {
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $id) 0 0 0
818 0 warn "No $table id for $id_key ",$dbh->errstr," Record skipped.\n";
819 0 return;
820 }
821 0 $id;
822 }
823  
824 0 ▶sub insertid {
Subroutine insertid: not called CC=1 SCAR=6.9
Pod undocumented
825 0 my $self = shift;
826 0 my $s = shift ;
827 0 $s->{mysql_insertid};
828 }
829  
830  
831 =head2 get_feature_id
832  
833 Title : get_feature_id
834 Usage : $integer = $db->get_feature_id($ref,$start,$stop,$typeid,$groupid)
835 Function: get the ID of a feature
836 Returns : an integer ID or undef
837 Args : none
838 Status : private
839  
840 This internal method is called by load_gff_line to look up the integer
841 ID of an existing feature. It is ony needed when replacing a feature
842 with new information.
843  
844 =cut
845  
846 # this method is called when needed to look up a feature's ID
847 0 ▶sub get_feature_id {
Subroutine get_feature_id: not called CC=4 SCAR=30.0
Pod documented
848 0 my $self = shift;
849 0 my ($ref,$start,$stop,$typeid,$groupid) = @_;
850 0 my $s = $self->{load_stuff};
851 0 ▶ unless ($s->{get_feature_id}) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($s->{"get_feature_id"}) 0 0 0
852 0 my $dbh = $self->features_db;
853 $s->{get_feature_id} =
854 0 $dbh->prepare_delayed('SELECT fid FROM fdata WHERE fref=? AND fstart=? AND fstop=? AND ftypeid=? AND gid=?');
855 }
856 0 ▶ my $sth = $s->{get_feature_id} or return;
Branch: 1 branch
BranchTrueFalseTotal
my $sth = $s->{"get_feature_id"} or return 0 0 0
857 0 ▶ $sth->execute($ref,$start,$stop,$typeid,$groupid) or return;
Branch: 1 branch
BranchTrueFalseTotal
$sth->execute($ref, $start, $stop, $typeid, $groupid) or return 0 0 0
858 0 my ($fid) = $sth->fetchrow_array;
859 0 return $fid;
860 }
861  
862 0 ▶sub _add_interval_stats_table {
Subroutine _add_interval_stats_table: not called CC=2 SCAR=17.9
863 0 my $self = shift;
864 0 my $schema = $self->schema;
865 0 my $create_table_stmt = $schema->{'finterval_stats'}{'table'};
866 0 $create_table_stmt =~ s/create table/create table if not exists/i;
867 0 my $dbh = $self->features_db;
868 0 ▶ $dbh->do($create_table_stmt) || warn $dbh->errstr;
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do($create_table_stmt) or warn $dbh->errstr 0 0 0
869 }
870  
871 0 ▶sub disable_keys {
Subroutine disable_keys: not called CC=1 SCAR=6.9
Pod undocumented
872 0 my $self = shift;
873 0 my $table = shift;
874 0 my $dbh = $self->dbh;
875 0 $dbh->do("alter table $table disable keys");
876 }
877 0 ▶sub enable_keys {
Subroutine enable_keys: not called CC=1 SCAR=6.9
Pod undocumented
878 0 my $self = shift;
879 0 my $table = shift;
880 0 my $dbh = $self->dbh;
881 0 $dbh->do("alter table $table enable keys");
882 }
883  
884  
885 4 1;
886  
887  
888  
889 __END__
« GFF/Adaptor/dbi/pg_fts.pm ↑ Summary GFF/Adaptor/memory.pm »