TreeBASE in MySQL

Roderic Page (r.page@bio.gla.ac.uk)

The original TreeBASE is implemented in 4D Originally a copy of the database was obtained from the TreeBASE FTP site, and opened in 4D 2003. Each table was exported to tab-delimited text, then imported into MySQL. Note that this copy of TreeBASE is out of date, the last study it contains is S1045, which was added 5 March 2004. For the mapping project, a more recent copy of the tables were obtained. You can recreate this database using the MySQL dump provided (see below).

Schema

The schema for the main tables in TreeBASE is shown below:

Schema produced by SQLEditor

Setting up MySQL

Install MySQL for your platform.

Mac OS X

Apple provide some instructions on installing MySQL, as does the MySQL documentation.

One potential gotcha is that after installation your system might not be able to find the MySQL client. For example, in the Terminal window you might type mysql and get this in response:

-bash: mysql: command not found

This indicates that the MySQL programs are not on your path. You will need to edit your .bash_profile file to add them to your PATH environment variable.

Recent MySQL versions for Mac OS X come with a Preference Pane to make it easy to stop and start the MySQL server. You need to place this file (typically called MySQL.prefPane) in the PreferencePanes folder in your Library folder. If you don't have a PreferencePanes folder, simply create one.

You may also wish to install a graphical client, such as CocoaMySQL (not MySQL 5) or Navicat.

Importing TreeBASE

The file tb.sql contains the tables needed for the mapping project.

You need to create a database for the project. The instructions below are for a command line interface. You might also be able to use a graphical MySQL client to do some or all of the steps below, but importing the actual data (which is around 80 Mb in size) may be slow.

Create MySQL database

Create a new database called “tb”:

 mysqladmin -u dba_user -p create tb

MySQL will prompt for the dba_user database password and then create the initial database files, in this case for a database called tb. Next you must login and set the access database rights:

 mysql -u dba_user -p

Again, you will be asked for the dba_user database password. At the MySQL prompt, enter following command:

 GRANT ALL PRIVILEGES ON tb.*
 TO nobody@localhost IDENTIFIED BY 'password';

where

• 'tb' is the name of the database
• 'nobody@localhost' is the userid of your webserver MySQL account
• 'password' is the password required to log in as the MySQL user

If successful, MySQL will reply with

Query OK, 0 rows affected

to activate the new permissions you must enter the command

flush privileges;

and then enter '\q' to exit MySQL

>Once the database tb is created you need to load the required tables, which are in the file tb.sql:

mysql -u nobody -p tb < tb.sql