MYSQL UDF, trees of data, hierarchy

In a previous post I described how to write a mysql user defined function (UDF) in C to create a translate-dna-to-protein function for mysql. Now, I've been playing with hierachies and I wrote a few UDFs to explore a tree of data; I guess this could have been done with a mysql stored procedure but I'm currently using an old server (and I need a deeper knowledge of mysql5 :-) ). The source code is available [here].
The tree itself is written as a sorted static const array of data so you cannot modify it via a mysql command but you'll have to recompile the code.

typedef struct Taxonomy
    {
    /** the ncbi-id */
    taxon_type_t tax_id;
    /** id of the parent */
    taxon_type_t parent_id;
    /** scientific name */
    char name[TAXON_NAME_SIZE];
    }Taxon,*const TaxonPtr;

It may also not be suitable for large trees. In my example, I've been using a subset of the NCBI taxonomy:

static const Taxon all_taxons[]={
{1, -1, "root"},
{2759, 131567, "Eukaryota"},
{6072, 33208, "Eumetazoa"},
{7711, 33511, "Chordata"},
{7742, 89593, "Vertebrata"},
{7776, 7742, "Gnathostomata"},
{8287, 117571, "Sarcopterygii"},
{9347, 32525, "Eutheria"},
{9443, 314146, "Primates"},
...)
};

but one could imagine something like this...

{1,-1,"root"},
{2,1,"world"},
{3,2,"europe"},
{4,3,"france"},
...

or...

{1,-1,"rdf:Property"},
{2,1,"foaf:knows"},
{3,2,"rel:friendOf"},
{4,2,"rel:childOf"},
...

the source taxonudf.c was successfuly compiled on my computer using the following command-line.

gcc -fPIC -shared -DDBUG_OFF -O3 -I/usr/include/mysql -lmysqlclient -o /usr/lib/taxonudf.o taxonudf.c

taxon_name returns the scientific name of an organism from a ncbi-tax-id.

mysql> create function taxon_name
returns string soname "taxonudf.o";
Query OK, 0 rows affected (0,00 sec)

mysql> select taxon_name(9606);
+------------------+
| taxon_name(9606) |
+------------------+
| Homo sapiens     |
+------------------+
1 row in set (0,43 sec)

tax_id returns the ncbi-tax-id from from its name.

mysql> create function taxon_id
returns integer soname "taxonudf.o";
Query OK, 0 rows affected (0,00 sec)

mysql> select taxon_id("Homo sapiens");
+--------------------------+
| taxon_id("Homo sapiens") |
+--------------------------+
|                     9606 |
+--------------------------+
1 row in set (0,00 sec)

taxon_childof returns wether a node in the hierarchy is a descendant of another node.

mysql> create function taxon_childof
returns integer soname "taxonudf.o";
Query OK, 0 rows affected (0,00 sec)

mysql> select taxon_childof(taxon_id("Homo"),taxon_id("Homo Sapiens"))
as "is Homo child of Homo.Sapiens ?";
+---------------------------------+
| is Homo child of Homo.Sapiens ? |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0,00 sec)


mysql> select taxon_childof(taxon_id("Homo Sapiens"),taxon_id("Homo"))
as "Is Homo.Sapiens descendant of Homo ?";
+--------------------------------------+
| Is Homo.Sapiens descendant of Homo ? |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0,00 sec)

taxon_com is an aggregate function which finds the common ancestral node in a set of node.

mysql> create aggregate function taxon_com
returns integer soname "taxonudf.o";
Query OK, 0 rows affected (0,00 sec)

mysql> create temporary table t1(cluster varchar(20),taxon int);
Query OK, 0 rows affected (0,07 sec)

mysql> insert into t1(cluster,taxon) values("A",251093),
("A",9781), ("A",37348),("B",9605),("B",9606),("B",63221),
("C",32523),("C",33154),("C",7776),("C",9443);
Query OK, 10 rows affected (0,03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select cluster,taxon as "ncbi-id",taxon_name(taxon) as "Name",taxon_childof(taxon,taxon_id("Primates")) as "Is_Primate" from t1;
+---------+---------+-------------------------------+------------+
| cluster | ncbi-id | Name                          | Is_Primate |
+---------+---------+-------------------------------+------------+
| A       |  251093 | Elephas antiquus              |          0 |
| A       |    9781 | Elephantidae gen. sp.         |          0 |
| A       |   37348 | Mammuthus                     |          0 |
| B       |    9605 | Homo                          |          1 |
| B       |    9606 | Homo sapiens                  |          1 |
| B       |   63221 | Homo sapiens neanderthalensis |          1 |
| C       |   32523 | Tetrapoda                     |          0 |
| C       |   33154 | Fungi/Metazoa group           |          0 |
| C       |    7776 | Gnathostomata                 |          0 |
| C       |    9443 | Primates                      |          1 |
+---------+---------+-------------------------------+------------+
10 rows in set (0,00 sec)

mysql> select cluster,taxon_com(taxon) as ncbi_id
from t1 group by cluster;
+---------+---------+
| cluster | ncbi_id |
+---------+---------+
| A       |    9780 |
| B       |    9605 |
| C       |   33154 |
+---------+---------+
3 rows in set (0.00 sec)

That's all folks

Technorati Keywords: udf mysql tree hierarchy function C