Bio::D...::mysql(3pm)

💖 Bio::D...::mysql 💖

okay bestie, so this is the manpage for Bio::D...::mysql(3pm) — a Bio::DB::GFF::Adaptor::dbi::mysql situation, and honestly? it's giving database adaptor for a specific mysql schema. we love a niche icon. 🌸

---

✨ NAME ✨

Bio::DB::GFF::Adaptor::dbi::mysql — it's a Database adaptor for a specific mysql schema. periodt. 💅

---

🌈 SYNOPSIS 🌈

go look at Bio::DB::GFF, babe. that's the whole vibe.

---

🦋 DESCRIPTION 🦋

so this adaptor is out here implementing a whole specific mysql database schema that plays nice with Bio::DB::GFF. it inherits from Bio::DB::GFF::Adaptor::dbi, which itself inherits from Bio::DB::GFF — it's a whole family tree, iykyk. 👑

the schema uses several tables, and we're gonna meet every single one:

🌟 fdata

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)

ngl it would be desirable to normalize the reference sequence name, since usually there are like, so many features sharing the same reference feature. BUT. in the current schema, query performance suffers dramatically when this additional join gets added. so we don't. tragic but true. 😭

🌟 fgroup

this is the group table. one row per group, no exceptions. columns:

• gid — the group ID (integer)
• gclass — the class of the group (string)
• gname — the name of the group (string)

the group table is doing the absolute most, serving multiple purposes. as you might expect, it clusters features that logically belong together — like the multiple exons of the same transcript, cute. it's also used to assign a name and class to a singleton feature. and finally, the group table is used to identify the target of a similarity hit. all of which is consistent with the way the group field is used in the GFF version 2 format. multi-talented fr fr. 💖

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)

🌟 ftype

this table holds 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)

🌟 fdna

this table holds the raw DNA of the reference sequences. three columns, that's it:

• 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. slay, automated. ✨

🌟 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.

🌟 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)

---

🎀 Data Loading Methods 🎀

on top of implementing the abstract SQL-generating methods of Bio::DB::GFF::Adaptor::dbi, this module also implements the data loading functionality of Bio::DB::GFF. overachiever behavior, honestly. 💅

---

⭐ 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 gets added to the database DSN identifier automatically if it's not already there. it's literally doing the work for you, bestie.

Argument       Description
--------       -----------

-dsn           the DBI data source, e.g. 'dbi:mysql:ens0040' or "ens0040"

-user          username for authentication

-pass          the password for authentication

---

⭐ get_dna

• Title: get_dna
• Usage: $string = $db->get_dna($name,$start,$stop,$class)
• Function: get DNA string
• Returns: a string
• Args: name, class, start and stop of desired segment
• Status: Public

this method performs the low-level fetch of a DNA substring given its name, class and the desired range. ngl this should probably be moved to the parent class, but here we are. 🫶

---

⭐ search_notes

• Title: search_notes
• Usage: @search_results = $db->search_notes("full text search string",$limit)
• Function: Search the notes for a text string, using mysql full-text search
• Returns: array of results
• Args: full text search string, and an optional row limit
• Status: public

this one is a mysql-specific method. given a search string, it performs a full-text search of the notes table and returns an array of results. every row of that returned array is an arrayref containing:

• 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.

---

⭐ schema

• Title: schema
• Usage: $schema = $db->schema
• Function: return the CREATE script for the schema
• Returns: a list of CREATE statemetns
• Args: none
• Status: protected

this method returns a list containing the various CREATE statements needed to initialize the database tables. (yes, statemetns. the original said it like that and we respect the typo era. 😭)

---

⭐ 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

---

⭐ make_meta_set_query

• Title: make_meta_set_query
• Usage: $sql = $db->make_meta_set_query
• Function: return SQL fragment for setting a meta parameter
• Returns: SQL fragment
• Args: none
• Status: public

by default this does absolutely nothing; meta parameters are not stored or retrieved. it's giving minimal effort and honestly, same. 💀

---

⭐ 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. prep queen. ✨

---

⭐ load_gff_line

• Title: load_gff_line
• Usage: $db->load_gff_line($fields)
• Function: called to load one parsed line of GFF
• Returns: true if successfully inserted
• Args: hashref containing GFF fields
• Status: protected

this method is called once per line of the GFF and passed a series of parsed data items that get stored into the hashref $fields. the keys are:

• ref — reference sequence
• source — annotation source
• method — annotation method
• start — annotation start
• stop — annotation stop
• score — annotation score (may be undef)
• strand — annotation strand (may be undef)
• phase — annotation phase (may be undef)
• group_class — class of annotation's group (may be undef)
• group_name — ID of annotation's group (may be undef)
• target_start — start of target of a similarity hit
• target_stop — stop of target of a similarity hit
• attributes — array reference of attributes, each of which is a [tag=>value] array ref

---

⭐ get_table_id

• Title: get_table_id
• Usage: $integer = $db->get_table_id($table,@ids)
• Function: get the ID of a group or type
• Returns: an integer ID or undef
• Args: none
• Status: private

this internal method is called by load_gff_line to look up the integer ID of an existing feature type or group. the arguments are the name of the table, and two string identifiers. for feature types, the identifiers are the method and source. for groups, the identifiers are group name and class.

this method requires that a statement handler named lookup_$table, have been created previously by setup_load(). it's here to overcome deficiencies in mysql's INSERT syntax. we don't make the rules, we just work around them. 🌈

---

⭐ get_feature_id

• Title: get_feature_id
• Usage: $integer = $db->get_feature_id($ref,$start,$stop,$typeid,$groupid)
• Function: get the ID of a feature
• Returns: an integer ID or undef
• Args: none
• Status: private

this internal method is called by load_gff_line to look up the integer ID of an existing feature. it is ony needed when replacing a feature with new information. (again with the typos — ony. iconic.) 💅

---

💀 BUGS 💀

none ;-)

like genuinely. none. the audacity, the confidence. we stan. 🔥

---

🌸 SEE ALSO 🌸

• Bio::DB::GFF
• bioperl

---

👑 AUTHOR 👑

okay so who even MADE this masterpiece?? it's giving legend behaviour, and the icon in question is:

• Lincoln Stein <lstein@cshl.org> 💖

---

Copyright (c) 2002 Cold Spring Harbor Laboratory.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. 🫶

bestie that's the whole legal vibe — free software, same terms as Perl itself, no cap. redistribute it, modify it, go off. just don't come for the copyright notice. 💅

---

and that's the whole page, we ate and left no crumbs ✨

this one's from perl v5.30.0, dated 2020-01-13, and yeah the name is literally Bio::D...::mysql(3pm) — abbreviated like a text message because even manpage titles gotta be mysterious sometimes 🙈

thanks for reading the whole thing, bestie, you're so real for that 💜🧚