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

💖 NAME 💖

Bio::DB::GFF::Adaptor::dbi::mysqlcmap — the database adaptor for an integraded CMap/GBrowse mysql schema. (Yes bestie, that's this page: Bio::D...sqlcmap(3pm), the one your terminal is being all dramatic about truncating 💅)

---

✨ SYNOPSIS ✨

okay so the entire synopsis is literally just:

See Bio::DB::GFF

that's it. go read that one. 🎀

---

🦋 DESCRIPTION 🦋

This adaptor implements a specific mysql database schema that is compatible with Bio::DB::GFF. It inherits from Bio::DB::GFF::Adaptor::dbi, which itself inherits from Bio::DB::GFF.

The schema uses several tables, and we are going to meet every single one of them, no skipping, no skimming, periodt. 👑

🌸 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
• 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)

ngl, 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. so like. we don't. it's giving tradeoff 😭

🧚 cmap_feature (replaces fgroup) 🧚

This is the group table. There is one row for each group. This is the shared table between CMap and GBrowse. There are many CMap related columns but only a few that GBrowse uses.

GBrowse Columns:

• feature_id — the group ID (integer)
• gclass — the class of the group (string)
• feature_name — the name of the group (string)

The group table serves multiple purposes, she's versatile like that:

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

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

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

🎀 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 🔥

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.

🌈 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. it just does it for you, no need to ask 🫶

• -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. This should probably be moved to the parent class. (not us being shady about it, it's literally what the docs say 💀)

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

✨ 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

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

🎀 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 are 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_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.

⭐ 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 is here to overcome deficiencies in mysql's INSERT syntax.

💜 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

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

✨ search_notes ✨

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

Okay so this one is a mysql-specific method, iykyk. Hand it a search string and it does a full-text search of the notes table and hands you back an array of results. Every row of that returned array is an arrayref, and the fields inside it are, in order:

• column 1 — a Bio::DB::GFF::Featname object, which you can pass straight to segment() 🫶
• column 2 — the text of the note
• column 3 — a relevance score

---

💅 make_features_order_by_part 💅

• Title — make_features_order_by_part
• Usage — ($query,@args) = $db->make_features_order_by_part()
• Function — it makes 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. No cap, that's the whole job. ✨

---

🌸 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 —

not the empty fields bestie 😭 the docs really said "figure it out" and left every single one of these blank. iconic honestly. we're keeping them blank because that's literally what the page says — no invented lore here 💀

---

🦋 BUGS 🦋

none ;-) 💖

Like. None. Zero. The bugs have left the chat. periodt.

---

🎀 SEE ALSO 🎀

• Bio::DB::GFF
• bioperl

---

👑 AUTHOR 👑

Ben Faga <faga@cshl.org>.

Modified from mysql.pm by:

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.

---

and that's a wrap on the whole page, bestie 🫶 This little treasure came out of perl v5.30.0 on 2020-01-13, served up fresh by User Contributed Perl Documentation as Bio::D...sqlcmap(3pm). go forth and search some notes 💅✨🌸