Bio-graphics, BioSQL and Rails part 1

January 7, 2009

In these series I will show you how to quickly add graphics support to a bioinformatics database rails application. We are going to use the biographics library by Jan Aerts, the BioSQL database schema, and rails 2.2.2 (also works with 2.3.2)  In this simple example we want to represent a sequence as a graphic, such that we can view it in a web browser more or less the way Gbrowse works. Each main feature has different subfeatures at different locations along it.

——————————————————————- main feature

——- ——– ———— ——– ——    subfeatures

We need to have the following installed, rails 2.1.1, bio 2.1, biographics 1.4 all available as gems and a database based on BioSQL schema.

We need to download the BioSQL schema located here. The latest version as of this writing is BioSQL v1.0 (code-named Tokyo) release, v1.0.1. Create a database called biosql_development. I am on Ubuntu Linux with Mysql 5.0.

george:>mysql -u george -p
:enter password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database biosql_development;
Query OK, 1 row affected (0.02 sec)

mysql> 

I have created a database called biosql_development. Why am i not using migrations? The reason is that BioSQL has some agreed standards on table names and schema convection which are not compatible with rails database creation and table naming conventions. However Rails allows us to override these default convections, when working with legacy databases, as will be our case.

After creating the database, load the BioSQL schema to the empty database. First we need to tell mysql which database to use.

mysql> use biosql_development;

then load the schema

mysql> source /home/george/Desktop/downloadsfolder/biosql-1.0.1/sql/biosqldb-mysql.sql;
Query OK, 0 rows affected, 1 warning (0.48 sec)

Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected, 1 warning (0.01 sec)

 ........ trucated
mysql>

Now we need to create a rails application and connect to this database.

I use the Netbeans IDE development environment for creating ruby and rails applications. Go ahead and create a rails application and specify to use mysql as the database adapter.

To connect to our legacy database, we need to override some convections. First disable table plurulization, and tell rails that the table primary name is named as tablename_id as opposed to just the id column expected by rails. To do that

Create a new file in your application configurations/initializers directory called override_rails.rb (you can call it whatever).

 class ActiveRecord::Base
  self.pluralize_table_names = false

  self.primary_key_prefix_type = :table_name_with_underscore
 end

The two lines above tells ActiveRecord not to expect the table names to be plural and that the primary key for each table is named as tablename_id format.

Also create another one called external_libraries.rb in the initializers directory, as you can tell this is where I want to put my require statements for loading external libraries.

require 'rubygems'

#load the bioinformatics library
require 'bio'

#load the biographics library
require 'bio-graphics'

#load the sql views extension library
gem 'rails_sql_views'
require 'rails_sql_views'

This file loads our gems. The rails_sql_views gem allows us to create views and access them by creating models corresponding to the views.

At this point if you run rake db:schema:dump, we will have a rails based BioSQL schema and which we can conveniently use to create a BioSQL database on any Relational database that rails supports and this includes Microsoft SQL server, DB2, Oracle, SQLlite and a host of others. All that would be required is to change the database.yml file to suit the adapter of choice and then execute rake db:schema:load to load the BioSQL schema.

Please note that if your are using rails 2.2.2,  you may want to comment the lines

unless Kernel.respond_to?(:gem)
  Kernel.send :alias_method, :gem, :require_gem
end

in rails_sql_views(0.6.1), otherwise running db:schema:dump will cause rake to abort.

In the next part I will describe how to create the necessary resources for our RESTful(Representational State Transfer) bioinformatics web application and rendering of the graphics.

Written by George Posted in biographics, bioinformatics, bioruby, databases, ruby on rails, tutorials Tagged with bioruby