PublicHouse: A Publicly Queryable Warehouse of Biological Databases
V3.7 8/22/2006
Overview
PublicHouse is a publicly queryable set of biological databases
constructed using the BioWarehouse. It provides an environment for
large-scale data mining using SQL statements issued across the Internet. User registration is required to access PublicHouse.
Datasets currently loaded within PublicHouse:
| NCBI Taxonomy | 07/21/2006 |
| Enzyme | 39.0 |
| CMR | 19.0 |
| Gene Ontology | 2006-07 |
| MetaCyc Chemical Compound Ontology | 10.1 |
| MultiFun Gene Ontology | 10.1 |
| MetaCyc Pathway Ontology | 10.1 |
| BioCyc | 10.1 |
The PublicHouse server runs the MySQL relational database engine (v. 4.1.12), and is based on version 3.7 of BioWarehouse. The server has 1
GB of RAM and an Intel Pentium 4 2.8 GHz processor.
In order to use PublicHouse, you must register to obtain a PublicHouse login.
Note that BioWarehouse also supports the KEGG database, but because of
KEGG licensing restrictions we are unable to make it available within
PublicHouse. To query KEGG, you may download and install BioWarehouse
locally, and load it with KEGG.
The ENZYME database in PublicHouse was kindly obtained from the Swiss Institute for Bioinformatics: http://www.isb-sib.ch/
Usage Guidelines
- Querying: Queries can be run
via the Bio-SPICE Dashboard’s DataWarehouse Query Analyzer, or
directly from a MySQL client (on any platform) using the mysql command line
interface.
- If querying via the mysql
client, you will need to download and install the client (Windows
documentation; binaries can be download here)
- Connection parameters: IP =
publichouse.sri.com (128.18.40.146), port number = 3306 (default),
database name = biospice. When prompted, please give your user name and
the password you were provided after PublicHouse registration.
- Because PublicHouse is a
relatively low power machine, please note that the server will
terminate queries which take longer than 15 min to run. Should you
require greater processing power, we suggest you install BioWarehouse
locally at your institution.
Example Queries
Connection parameters:
Host: publichouse.sri.com
Port: 3306
Database: biospice
Using a MySql Client
Launch the MySQL client as described below:
[OSprompt] mysql –D biospice -p -u YourLogin -h publichouse.sri.com
Enter password: YourPassword
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 446 to server version: 3.23.58
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> select version,description from Warehouse;
+---------+-------------+
| version | description |
+---------+-------------+
| 3 | NULL |
+---------+-------------+
1 row in set (0.00 sec)
mysql> select name,wid,version,loaddate from DataSet;
+-----------+-----+---------+---------------------+
| name | wid | version | loaddate |
+-----------+-----+---------+---------------------+
| AgroCyc | 2 | 8.5 | 2004-09-22 17:18:27 |
| HpyCyc | 3 | 8.5 | 2004-09-22 17:24:02 |
| HumanCyc | 4 | 8.5 | 2004-09-22 17:25:55 |
| MtbcdcCyc | 5 | 8.5 | 2004-09-22 17:42:29 |
| MtbrvCyc | 6 | 8.5 | 2004-09-22 17:46:42 |
| VchoCyc | 7 | 8.5 | 2004-09-22 17:50:47 |
| Enzyme | 8 | unknown | 2004-09-22 00:00:00 |
+-----------+-----+---------+---------------------+
7 rows in set (0.00 sec)
mysql> select * from Reaction where ecnumber='1.2.3.4';
+-------+--------+----------+------------------+-------------+------------+
| WID | DeltaG | ECNumber | ECNumberProposed | Spontaneous | DataSetWID |
+-------+--------+----------+------------------+-------------+------------+
| 92147 | NULL | 1.2.3.4 | NULL | NULL | 8 |
+-------+--------+----------+------------------+-------------+------------+
1 row in set (0.03 sec)
mysql> select * from Reaction where ecnumber='1.1.1.1';
+-------+--------+----------+------------------+-------------+------------+
| WID | DeltaG | ECNumber | ECNumberProposed | Spontaneous | DataSetWID |
+-------+--------+----------+------------------+-------------+------------+
| 2658 | NULL | 1.1.1.1 | NULL | NULL | 2 |
| 21432 | NULL | 1.1.1.1 | NULL | NULL | 4 |
| 60262 | NULL | 1.1.1.1 | NULL | NULL | 5 |
| 71020 | NULL | 1.1.1.1 | NULL | NULL | 6 |
| 81436 | NULL | 1.1.1.1 | NULL | NULL | 7 |
| 90299 | NULL | 1.1.1.1 | NULL | NULL | 8 |
+-------+--------+----------+------------------+-------------+------------+
6 rows in set (0.02 sec)
mysql> select * from EnzymaticReaction where wid=2658;
Empty set (0.01 sec)
mysql> select * from EnzymaticReaction where reactionwid=2658;
+-------+-------------+------------+------------+-------------------+------------+
| WID | ReactionWID | ProteinWID | ComplexWID | ReactionDirection | DataSetWID |
+-------+-------------+------------+------------+-------------------+------------+
| 13660 | 2658 | 3280 | NULL | NULL | 2 |
| 14150 | 2658 | 4126 | NULL | NULL | 2 |
| 14577 | 2658 | 3443 | NULL | NULL | 2 |
| 14581 | 2658 | 3381 | NULL | NULL | 2 |
| 14600 | 2658 | 7561 | NULL | NULL | 2 |
| 14626 | 2658 | 6643 | NULL | NULL | 2 |
| 14637 | 2658 | 6065 | NULL | NULL | 2 |
+-------+-------------+------------+------------+-------------------+------------+
7 rows in set (0.01 sec)
Using the Bio-SPICE Dashboard
Please visit the Bio-SPICE project at https://biospice.org/ for more information.
The Warehouse Query Analyzer and its documentation have been updated,
so please make sure you update your copy of the Analyzer by going to
the Update Center and obtaining the new version.
The Analyzer requires the user to set the username and password
(“your username; your password”; - below) to access
PublicHouse (although the IP address – 128.18.40.146
– and databasename – biospice – of
publichouse are hard-coded).
The queries described below can be run by pasting your query
(“your query” – below) in the Value field
when editing the parameters of the Query Analyzer:
Documentation
Please see the BioWarehouse documentation.
Note: a good understanding of
the BioWarehouse schema is necessary for most queries. The schema
documentation is available via the above link.
Current Limitations of PublicHouse:
- Loaders: Limitations of individual
loaders are described in each loader’s documentation
- Database performance: If you encounter
what you believe to be unacceptable query performance, please your
query so we can investigate available performance tuning options.
Support
For support and inquiries please contact
Periodic announcements about the availability of PublicHouse and
BioWarehouse releases are sent via the BioWarehouse Announce mailing
list.
PublicHouse registrations are automatically subscribed to the announcement list.
Please send email to
to join the list.