Bio-graphics, BioSQL and Rails part 1

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)


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

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

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

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.



  1. Pingback: Bio-graphics, BioSQL and Rails part 2 « Biorelated
  2. nsaunders

    I used Rails 2.2.2 and rake db:schema:dump gave the error:

    rake aborted!
    undefined method `require_gem’ for module `Kernel’

    Turns out that rails_sql_views.rb (0.6.1) contains:

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

    require_gem is no longer used. Commenting out those 3 lines fixed the rake problem.

  3. Pingback: Neil Saunders @ wikidot: Rails Biosql Bioruby Biographics
  4. Pingback: Honolulu Hacker » Blog Archive » Getting started with BioRuby and Ruby on Rails.
  5. Ra

    good work!
    Do you know that BioSQL I/O is supported in BioRuby ? Models are there, so you/we could try to build a rails app with them.

    * try to use the Bio::SQL … for building a rails test app
    * integrate migration for BioSQL into BioRuby
    * extending test

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s