Attempted to Post to CVS

I just tried to attempt to commit something to the CVS server for the first time (biosql-phylodb-mysql.sql). This was my first attempt to commit with CVS and I am not sure if it worked. It seems like SVN is a bit easier to use.

MySQL Schema Changes, Blog comments enabled

I've made changes to the MySQL schema to fit the changes made by H. Lapp to the Postgres version of the PhyloDB extensions. Since MySQL does not support booleans I used ENUM:

is_rooted ENUM ('FALSE', 'TRUE') DEFAULT 'TRUE'

in the tree table.

Comments are now enabled in the blog, I did not know that they were turned off.

PhyInit: INT(10) != INTEGER

I fixed the Foreign Key problems for some of tables that make references to other PhyloDB tables. However linking to the other BioSQL tables seems to be a problem because the INTEGER values in the Phylo tables are created as INT(11) while the INTEGER values in the other BioSQL tables are INT(10) UNSIGNED.

I am going to make all of the integer values in the PhyloDB tables INT(10) so that the foreign key values will work. This will also make the tables consistent with the rest of BioSQL.

PhyInit: Change to InnoDB tables causes ALTER TABLE errors

Changing the table types to InnoDB now causes problems with using ALTER TABLE to create foreign keys.
For example:

ALTER TABLE tree ADD CONSTRAINT FKNode
FOREIGN KEY (node_id) REFERENCES node (node_id);

is giving the error:

DBD::mysql::db do failed: Can't create table './biosql/#sql-cc7_bba.frm' (errno: 150) at ./PhyInit.pl line 363, <> line 1.

Typing the SQL code directly in the MySQL Command line gives:

ERROR 1005: Can't create table './biosql/#sql-cc7_ba6.frm' (errno: 150)

It looks there may be some help in an online discussion of this issue. It is odd that it flags this as a "Can't create table error" when this is really an ALTER TABLE problem.

Info on Foreign Key constraints is also in the MySQL manual. The conditions for foreign key definitions that are listed in the MySQL manual are:

• Both tables must be InnoDB tables and they must not be TEMPORARY tables.
  All of my tables are now InnoDB tables so this is not the problem.

• Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same.
  Both columns in the broken SQL are INT(11) so this is probably not the problem.
  

• In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.
  This is it, adding indexes to the tables fixed the problem.
  

• In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
  This is it, adding indexes to the tables fixed the problem.
  

• Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length.
  This is not the problem since the columns are INT(11).
  

• If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.
  This is not the problem since FKnode is a unique value in the database. As a test, I ran the SQL without specifying the symbol, and I still have the error.
  

I am crossing these off the list as I can ..

Transaction Support in MySQL

I am working with the parseTreesPG.pl script to make it work with MySQL and I am having trouble with transaction support. The use of

$dbh->commit();

is currently causing fatal errors with the message

commit ineffective with AutoCommit enabled at ./parseTreesPG.pl line 736

According to the documentation, this error message occurs when AutoCommit is off, or when transactions are not supported by the system you are using.

It looks like transaction support for MySQL has been around for a few years, but I have never worked with transactions before so this is new for me.

I am working through the Requirements for Transaction Support in MySQL to see where the trouble is.

• The version of MySQL I am using (4.0.18-standard) should support transactions
• The version of DBD:MySQL I am using supports transactions
• ISAM and MyISAM tables in MySQL do NOT support transaction support.
  
• The tables that do support transaction support are: BDB, InnoDB and Gemini.

The code that I am using to create tables currenty does not specify the table type, so MyISAM tables are being created. So .. my guess is that the MyISAM tables are the problem.

It looks like I will need to make sure that MySQL is creating InnoDB tables by modifying the PhyInit.pl script CREATE TABLE syntax to specify the table type as INNODB, this would be something like:

CREATE TABLE tree (
tree_id INTEGER NOT NULL auto_increment,
name VARCHAR(32) NOT NULL,
identifier VARCHAR(16),
node_id INTEGER NOT NULL
, PRIMARY KEY (tree_id)
, UNIQUE (name)
,
)TYPE=INNODB;

PhyInit: Create tables

I added the code to create the phylo tables if they do not exist. The other BioSQL tables are not currently created, and the current version uses hard coded SQL instead of running external SQL code. The current implementation will only work with MySQL.

Download of source available: PhyInit.pl

PhyInit: Creating Database if It Does Not Exist

I worked more on defining the command line options that I want to use. These were updated on the project web page.

I incorporated these command lines option into PhyInit. I added the ability to check for the existence of a MySQL database with the name passed from the command line. If the database name does not exist, a new database will be created. This assumes that the user has permissions to create databases on the db host. I first ask if the user really wants to create the database before doing so. The creation of a new database will only work in MySQL.

For the duration of gsoc, I will use MySQL database back end.

Today I found out how to use perldoc to print help statements from POD documentation. This is pretty useful since I used to always write PrintHelp subfunctions that duplicated what is written in the POD documentation.

system("perldoc $0");

Simple but not something that I have used before. I have not tested this in Windoze to see if it works across platforms.

Command Line Tweaks and Starting PhyInit.pl

I have been slightly modifying the command line arguments I initially suggested to make the arguments consistant with the existing BioSQL related scripts. (ie --dbuser in addition to -u will be valid arguments). This will require that I use Getopt::Long. I am also trying to decide if I want to mix caps in with the command line (ie. should it be PhyInit.pl, phyinit.pl, OR phy_init.pl). These are pretty minor issues but something I want to make consistant with the prexisting bioperl code.

After looking through the exising BioSQL code, I really like the idea of defaulting many of the command line variables to environmental variables, for example

 my $usrname = $ENV{DBI_USER};
 my $pass = $ENV{DBI_PASSWORD};

This is a somewhat obvious thing to do in retrospect, but not something that I have done before.

On the project page I have been going through the existing code in the codebase that is relevant to my proposed programs and linking to the locations of the code in CVS. I was wanting to finish this up tonight, but it looks like the wg_phyloinformatics wiki is down for the evening.

I have started the PhyInit.pl program and will make my first commit to subversion tonight. This will not be a finished program, but I want to be in the practice of making commits on a daily basis as much as possible.