In BioSQL, PhyloDB module, there are tables like this:

<br/>CREATE TABLE tree_qualifier_value<br/>(<br/> tree_id integer NOT NULL, -- The tree with which the metadata is being associated.<br/> term_id integer NOT NULL, -- The name of the metadate element as a term from a controlled vocabulary (or ontology).<br/> "value" text, -- The value of the metadata element.<br/> rank integer NOT NULL DEFAULT 0, -- The index of the metadata value if there is more than one value for the same metadata element. If there is only one value, this may be left at the default of zero.<br/> CONSTRAINT tree_qualifier_value_pkey PRIMARY KEY (tree_id, term_id, rank),<br/> CONSTRAINT fkterm_treequal FOREIGN KEY (term_id)<br/> REFERENCES term (term_id) MATCH SIMPLE<br/> ON UPDATE NO ACTION ON DELETE NO ACTION,<br/> CONSTRAINT fktree_treequal FOREIGN KEY (tree_id)<br/> REFERENCES tree (tree_id) MATCH SIMPLE<br/> ON UPDATE NO ACTION ON DELETE CASCADE<br/>)<br/>

This becomes a problem when I want to retrieve values that are related to one tree. For example, here are some data from the table

tree_id	term_id	value	rank
2	1	"Wagner78"	0
2	2	"Single Tree"	0
2	3	"Wagner78"	0
2	5	"885"	0
2	6	"Ladiges, P. Y.; Humphries, C. J."	0
2	7	"A cladistic study of Arillastrum, Angophora, and Eucalyptus (Myrtaceae). "	0
2	8	"Transformed cladistic; character compatibility; branch and bound, and Farris-Wagner methods ...	0

There is another table term that has term_id, and name. For example, the term (term_id=2) has the name 'dc.title'
I found a few ways to join the data and create a tabular format that each row belongs to one tree_id.

First, I can use what is called self join :

select t1.tree_id, t1.value as dc_title, t2.value as dc_identifier, t3.value as dc_abstract<br/>FROM  tree_qualifier_value t1, tree_qualifier_value t2, tree_qualifier_value t3, term te1, term te2, term te3<br/>where t1.tree_id = t2.tree_id and t2.tree_id = t3.tree_id and t1.term_id = te1.term_id and te1.name='dc.title' and t2.term_id = te2.term_id and te2.name='dc.identifier'<br/>and t3.term_id = te3.term_id and te3.name='dc.abstract'

Here I used different table aliases for the same table, and each alias represents a different instance of the table.

Another way is to use FULL OUTER JOIN:
select * from <br/>(SELECT tree_id, tq.value as dc_identifier FROM tree_qualifier_value tq, term te WHERE tq.term_id = te.term_id and te.name='dc.identifier') as ide<br/>FULL OUTER JOIN<br/>(SELECT tree_id, tq.value as dc_title FROM tree_qualifier_value tq, term te WHERE tq.term_id = te.term_id and te.name='dc.title' ) as title<br/>using (tree_id)<br/>FULL OUTER JOIN<br/>(SELECT tree_id, tq.value as dc_abstract FROM tree_qualifier_value tq, term te WHERE tq.term_id = te.term_id and te.name='dc.abstract' ) as abstract<br/>USING (tree_id)<br/>

Interestingly, I also tried to use the PostgreSQL XML support to generate a piece of XML from that table. Here instead of using JOIN, I used UNION:

SELECT tree_id, xmlagg(xvalue) as xml FROM (<br/>SELECT tree_id, xmlforest(tq.value as "dc:identifier") as xvalue <br/>FROM tree_qualifier_value tq, term te <br/>WHERE tq.term_id = te.term_id and te.name='dc.identifier' <br/>UNION ALL <br/>SELECT tq.tree_id, xmlforest(tq.value as "dc:title") as xvalue <br/>FROM tree_qualifier_value tq, term te <br/>WHERE tq.term_id = te.term_id and te.name='dc.title' <br/>UNION ALL <br/>SELECT tq.tree_id, xmlforest(tq.value as "dc:abstract") as xvalue <br/>FROM tree_qualifier_value tq, term te <br/>WHERE tq.term_id = te.term_id and te.name='dc.abstract'<br/>) AS tab GROUP BY tree_id

This will produce some thing like:

<br/>"<dc:identifier>885</dc:identifier><dc:title>A cladistic study of Arillastrum, Angophora, and Eucalyptus (Myrtaceae). </dc:title><dc:abstract>Transformed cladistic; character compatibility; branch and bound, and Farris-Wagner methods ...</dc:abstract>"<br/>

For a table with 5287 different tree_id, and a total of 42320 rows, the three method takes 1080ms, 1136ms, 1338ms (average of three tests). With no surprise that the last method takes longer as it puts the XML together.

BioSQL is a relational model that covers many kinds of data models in biology. The core schema has models for sequences, features, annotations, taxonomy (focused on NCBI taxonomy), and ontologies. It also has a PhyloDB extension module.
In this post, I'll record how I setup BioSQL on a Postgresql database on Ubuntu Jaunty (9.04)
Install PostgreSQL
There is a very good step-by-step instruction on how to install PostgreSQL on Ubuntu. Here is the link:
https://help.ubuntu.com/community/PostgreSQL
Even though it doesn't cover Ubuntu version after 8.04, and it says it's for PostgreSQL 8.1, I can still follow all the steps to install PostgreSQL 8.3 on Jaunty.
Install BioSQL
The BioSQL website has detailed installation instructions. But it is also very verbose, and the page has instructions for MySQL, PostgreSQL, and Oracle, even steps on how to install the databases. I wish there were separate pages for each database, though. Anyway, creating a BioSQL database on PostgreSQL is very straight forward. Here is what you all need to do:

> createdb biosql 
> psql biosql < biosqldb-pg.sql

To add the PhyloDB extension, first follow the instruction to check it out from SVN. Then do

> psql biosql < biosql-phylodb-pg.sql

Import NCBI Taxonomy into BioSQL
BioSQL has several scripts to import data into the database. Here I am just explaining how to import the NCBI taxonomy into the database.
First bioperl and bioperl-db need to be installed. Again, these steps are included in the BioSQL installation page. However, it's much easier to do it the Ubuntu way.

> sudo apt-get install bioperl

That'll install bioperl 1.5.2, with bioperl-db.
Then simply run (suppose you have already setup the PostgreSQL and you can run it with your username.

> load_ncbi_taxonomy.pl --diver Pg --download yes<br /><br />Update: 7/23/2009<br />Hilmar has pointed out that the bioperl in my post is only 1.5.2, and it seems in Karmic (9.10), ubuntu will have BioPERL 1.6.0 available in universe.<br /> <br /><br /><br />

It turns out that I have to create database myself. I decided to install postgresql on my ubuntu server, and dump treebase data into it.

To install postgresql 8.3 on Ubuntu 9.04, basically I followed the steps in this article. Although the author was addressing an earlier version of Ubuntu (8.04), I found the steps are still correct.

http://hocuspokus.net/2008/05/install-postgresql-on-ubuntu-804

This post is based on postgresql 8.1. It also shows how to make the current user a superuser.

https://help.ubuntu.com/community/PostgreSQL

I also installed pgadmin3 on another ubuntu box where I have a ubuntu desktop installed. I found that in order to access the server remotely, I have to do the following on the server:

1. Add port 5432 to the allowed list
2. Give user a password. Do something like

<code>sudo su postgres -c psql 
=# ALTER USER myusername WITH PASSWORD 'password';
=# \q
</code>