Rails on Ice

If you want to do fast analysis on very large datasets or you need to extract a small sliver of data from a very wide table or set of tables, check out the free version of the Info-Bright MySql data storage engine.  The non-commercial version is called ICE (InfoBright Community Edition.)  http://www.infobright.org  It's intended for data warehouse and archival applications.

A crucial point about ICE is that it's read-only.  It doesn't support insert, delete or update.  You can create a schema (with some restrictions,) and you can do any other SQL supported by MySql.  What this means is that to get data into ICE tables you have to use 'load data infile'.  The commercial version doesn't have that restriction.

So putting your Rails app on ICE is probably out.  But what if you want to use Rails migrations to generate a schema with ICE or otherwise use Ruby Active-Record to access an ICE database?

I downloaded the Linux 64 bit version of ICE from the  InfoBright website and set it up; it was basically a stand-alone version of MySql with the InfoBright storage engine built in.  I was able to connect to it using the standard MySql database adapter, but attempting to re-create a schema from my Migration classes resulted in errors from MySql.  It turns out ICE, because it doesn't support 'insert', doesn't allow primary keys or auto_increment type columns.  In a read-only situation this is okay, but it means your old DDL won't work. 

I needed to replace the DDL generated in the 'create_

table' method in migrations with something that ICE would accept.  In every ActiveRecord database adapter class there's a definition of the data types for the database system in question.  MySql looks like

def native_database_types #:nodoc:
        {
          :primary_key => “int(11) DEFAULT NULL auto_increment PRIMARY KEY”.freeze,
          :string      => { :name => “varchar”, :limit => 255 },
          :text        => { :name => “text” },
          :integer     => { :name => “int”, :limit => 4 },
          :float       => { :name => “float” },
          :decimal     => { :name => “decimal” },
          :datetime    => { :name => “datetime” },
          :timestamp   => { :name => “datetime” },
          :time        => { :name => “time” },
          :date        => { :name => “date” },
          :binary      => { :name => “blob” },
          :boolean     => { :name => “tinyint”, :limit => 1 }
        }
      end

We need to change the 'primary_key'. (And raise an exception on 'binary' -- InfoBright doesn't support blobs.)   Since ICE is really just MySql with some restrictions we could extend the MySql database adapter creating an ICE adapter and over-ride the above method.  It turns out to be a bit harder than that, but not much.

Below is the bare minimum you need to get the ICE adapter running.  This version doesn't trap invalid DDL or unsupported SQL but it will work with simple migrations.

require 'active_record/connection_adapters/abstract_adapter'
require 'active_record/connection_adapters/mysql_adapter'

module ActiveRecord

class Base
  def self.ice_connection(config)
        config = config.symbolize_keys
        host     = config[:host]
        port     = config[:port]
        socket   = config[:socket]
        username = config[:username] ? config[:username].to_s : ‘root’
        password = config[:password].to_s
 
        if config.has_key?(:database)
          database = config[:database]
        else
          raise ArgumentError, “No database specified. Missing argument: database.”
        end
 
        require_mysql
        mysql = Mysql.init
        mysql.ssl_set(config[:sslkey], config[:sslcert], config[:sslca], config[:sslcapath], config[:sslcipher]) if config[:sslkey]
 

  ConnectionAdapters::IceAdapter.new(mysql, logger, [host, username, password, database, port, socket], config)   
  end
 
end

module ConnectionAdapters
class IceAdapter < MysqlAdapter

def initialize(connection, logger, connection_options, config)
  puts “connecting to ICE”
  super(connection, logger, connection_options, config)
end

# Info-Bright Community Edition (BrightHouse engine)
  def adapter_name
    ‘Ice’
  end
 
  def ice_connection
    puts “Connecting to ICE”
    super.mysql_connection
  end

def native_database_types #:nodoc:
 puts “** In BrightHouse SQL types”
        {
          :primary_key => “int auto_increment”,
          :string      => { :name => “varchar”, :limit => 255 },
          :text        => { :name => “text” },
          :integer     => { :name => “int”, :limit => 4 },
          :float       => { :name => “float” },
          :decimal     => { :name => “decimal” },
          :datetime    => { :name => “datetime” },
          :timestamp   => { :name => “datetime” },
          :time        => { :name => “time” },
          :date        => { :name => “date” },
          :binary      => { :name => “blob” },
          :boolean     => { :name => “int”, :limit => 1 }
        }
      end
    end
  end
end

You put this file alongside the other database adapter files in active_record and in your database.yml config use "adapter"=>'Ice'.