We started to use MonetDB, a column store database in a couple of projects involving querying over our DataWarehouses, created from public open data. The goal of one of the projects, called Educational Data Lab, is to return information about educational indicators in Brazil, with its historical evolution. We have nowadays about 100Gb of data being processed and queried and it will grow significantly in the following months. The performance of MonetDb showed to be really good, so we started to use it as our main database (a TPC-H is available in their sites). So we started to develop applications in Ruby and specially in Node.js using MonetDb. We will present them in several posts, starting with the Ruby Active Record for MonetDb.
Our active record implements the majority of the fuctionalities of an Active Record, which can be downloaded here . It was implemented by Joao Vitor Risso, a former CS-student who worked in our research group. Below there is a full description on how to install it, It is also available at: https://github.com/C3SL/activerecord-monetdb-adapter/blob/master/doc/rails.txt (contributions to complete it are welcome).
= MonetDB on rails =
This file documents the steps necessary to make rails working with MonetDB.
Database management in rails is performend via the ActiveRecord (ORM) adapter (http://api.rubyonrails.org/files/vendor/rails/activerecord/README.html).
The ruby/monetdb driver comes packaged as:
1) a standalone driver (only depends on ruby 1.8 and ruby 1.9);
2) and activerecord connector built on top of the standalone driver.
The following steps assumes that you already have obtained and installed a working copy of the rails framework and the ‘rubygems’ package manager.
For more informations about rubygems please refer to: http://rubyforge.org/projects/rubygems/
== Installation ==
On rpm base systems both the activerecord and standalone driver should be installed via the monetdb-clients package.
Please note that the building and installing process do not require root privileges.
=== Install the standalone driver ===
If you are using a source code version of monetdb cd into the driver directory located at ‘./clients/src/ruby’
First build a gem file starting from the given gemspec:
$ gem build ruby-monetdb-sql-0.1.gemspec
and install the resulting gem with the command:
$ gem install –local ruby-monetdb-sql-0.1.gem
=== Install the activerecord connector ===
If you are using a source code version of monetdb cd into the activerecord adapter directory located at ‘./clients/src/ruby/adapter’
First build a gem file starting from the given gemspec:
$ gem build activerecord-monetdb-adapter-0.1.gemspec
and install the resulting gem with the command:
$ gem install –local activerecord-monetdb-adapter-0.1.gem
== Configuration ==
Create a new rails application with the ‘rails’ command and cd into it:
$ rails myapp
$ cd myapp
This will create the usual Rails folder structure.
In order to start using rails with MonetDB as a backend we need to properly configure the databases by editing the file ‘config/database.yml’
This is a sample configuration used to connect to a database named ‘test’:
adapter: monetdb
username: monetdb
password: monetdb
hostname: localhost
port: 50000
database: test
Where adapter is the name of the activerecord connector you want to use (in this case ‘monetdb’).
The driver works either by directly connecting to a mserver5 instance or through merovingian. Make sure to create the database (‘test’) before proceeding.
Once the database.yml file as been properly configured proceed by creating the Controller files either manually or by using the scaffolding script:
$ ./script/generate scaffold
Operations on the database are performed in the fashion of rake tasks; once the Controllers have been properly created and customized, schemas can be generated with the task ‘migrate’:
$ rake db:migrate
The current schema version number can be obtained with the task ‘version’:
$ rake db:version
It is possible to revert to previous schemas definitions via the ‘rollback’ task:
$ rake db:rollback
In the same way it is possible to drop all generated schemas with the ‘drop’ task:
$ rake db:drop:all
== References ==
What follows is a list of tutorials about getting started with rails.
1) http://www.akitaonrails.com/2007/12/12/rolling-with-rails-2-0-the-first-full-tutorial
2) http://oreilly.com/ruby/archive/rails.html
3) http://developer.apple.com/tools/rubyonrails.html