sql_mapper examples

sql_mapper_examples.rb

require 'rubygems'
require 'sql_mapper'

# sql_mapper is built on top of ActiveRecord and is meant to augment it in this specific case:  1. You
# want fast read access for large data sets, 2. the data can be flat (denormalized) and 3. you don't
# need AR's magic.  It also assumes the ability to comfortably craft raw sql.  Sql is a data retrieval DSL
# after all ;)
ActiveRecord::Base.establish_connection :adapter => 'sqlite3', :database => ':memory:'
conn = ActiveRecord::Base.connection
conn.execute 'create table foos (id serial, name string)'
(0..25).each do |i|
  conn.execute "insert into foos values (#{i}, 'foo_#{i}')"
end

class Foo
  attr_accessor :id, :name

  def initialize(id, name)
    @id = id
    @name = name
  end

  def to_s
    "#{id}: #{name}"
  end
end

# You can execute raw sql inline the results of which will be marshalled
# into structs with attributes matching the column names.
foos = ActiveRecord::SqlMapper.fetch :query => "select * from foos"
foos.each do |foo|
  puts "#{foo.id}: #{foo.name}"
end

# You can configure sql_mapper so that SQL is mapped to logical names and could
# have custom result classes if you want to have logic tied to your result
# classes.  For rails, this should be put in an initializer.
ActiveRecord::SqlMapper.config do
  result_class Struct  # You can override the default result class.
  map :all_foos, "select * from foos"
  map :a_foo, "select * from foos where id = :id"
  map :a_foo2, "select * from foos where id = ?", Foo
end

# You can fetch results using a query's logical name.  Results here are
# marshalled into the default result_class defined in the config.
foos = ActiveRecord::SqlMapper.fetch :query => :all_foos
foos.each do |foo|
  puts "#{foo.id}: #{foo.name}"
end

# You can fetch results using a query's logical name and interpolating
# parameters into the sql (safe & sanitized).  fetch_one is a convenience
# method to return a single object instead of an array of objects.
foo = ActiveRecord::SqlMapper.fetch_one :query => :a_foo, :params => {:id => 1}
puts "#{foo.id}: #{foo.name}"

# If the named query mapping overrode the default result_class, results
# will be of the type of the overriding class.
foo = ActiveRecord::SqlMapper.fetch_one :query => :a_foo2, :params => 2
puts foo.class

# You can also overrie the result class in the fetch invocation, too.
foo = ActiveRecord::SqlMapper.fetch_one :query => :a_foo, :params => {:id => 3}, :result_class => Foo
puts foo.class

sql_mapper_perf.rb

require 'rubygems'
require 'sql_mapper'

# ActiveRecord is great, but can be slower than dirt when operating with large data sets.  sql_mapper
# is an extension that eases the use of raw sql when you want speed with large data sets and can
# sacrifice AR's magic.  Currently, its limited to read-only data.
ActiveRecord::Base.establish_connection :adapter => 'sqlite3', :database => ':memory:'
conn = ActiveRecord::Base.connection
conn.execute 'create table foos (id serial, name string)'

def prof(msg="Exec time")
  start = Time.now
  yield
  finish = Time.now
  exec_time = (finish - start) * 1000
  puts "#{msg}: #{exec_time}"
  nil
end

prof 'insertion' do
  (0..100000).each do |i|
    conn.execute "insert into foos values (#{i}, 'foo_#{i}')"
  end
end

class Foo < ActiveRecord::Base
end

# profile how long it takes to execute the raw sql
prof 'raw_sql' do
  conn.execute "select * from foos"
end

# Profile how long it takes to fetch 100000 records and populate
# ActiveRecord objects.
prof 'Foo.all' do
  Foo.all
end

# Profile how long it takes to fetch 100000 records and populate Structs
# using sql_mapper.  This is > an order of magnitude faster than using
# standard ActiveRecord objects.
prof 'SqlMapper' do
  ActiveRecord::SqlMapper.fetch :query => "select * from foos"
end