Switch - A Query Language for Ruby

Switch is a deep embedding of relational Queries into Ruby. With Switch there is no syntactic or stylistic difference between Ruby programs that operate over in-memory array objects or database-resident tables, even if these programs rely on array order or nesting. Switch's buil-in compiler and SQL code generator guarantee to emit few queries, addressing long-standing performance problems that trace back to Rails' ActiveRecord database binding.

Looks like Ruby, but performs like handcrafted SQL

is the ideal that drives the research and development behind Switch.

Installation

Setting up Pathfinder

The relational algebra plans provided by Switch are usually too unwiedly to be handled directly by database systems. We rely thus on Pathfinder to optimize the plans in order to get efficient SQL-Code.

On Mac OSX a proper brew formula should be available soon:

brew install pathfinder

For other platforms download the Pathfinder source tarball here.

Download Switch

Get a clone of Switch

git clone git@github.com:manuelmayr/Switch.git

and run rake package in the Switch directory.

A simple

gem install ./pkg/switch-0.0.1.gem

completes the installation and takes care of dependencies.

Using Switch

Use the following snippet as IRB initialization file

$ irb -f irb_init.rb

to play aroung with Switch.

Switch uses ActiveRecord merely as an infrastructure to interact with different database systems. ActiveRecord is thus not involved in the translation scheme we are using.

# filename: irb_init.rb
require "switch"
require "logger"

include Switch
Queryable.engine = Engine.new ActiveRecord::Base

ActiveRecord::Base.logger = Logger.new("/tmp/switch.log")
ActiveRecord::Base.logger.level = Logger::DEBUG
ActiveRecord::Base.logger.datetime_format = "%Y-%m-%d %H:%M:%S"

ActiveRecord::Base.configurations = { 
  'pg' => {
     :username => '<username>',
     :password => '<password>',
     :adapter  => :postgresql,
     :encoding => 'utf8',
     :database => '<database>'
   },  
   'db2' => {
     :username => '<username>',
     :password => '<password>',
     :adapter  => :ibm_db,
     :encoding => 'utf8',
     :database => '<database>'
   },  
}


ActiveRecord::Base.establish_connection 'db2'  # for DB2 or pg for postgres

The queries generated by Switch are dumped to /tmp/switch.log.

Examples

The to_sql trigger method is used to display the associated SQL-Code of a Query. Use the each trigger method to execute a query on a database and marshal the result back to Ruby objects.

Executing a query on a database:

# Articles is a table in a database automatically recognized by Switch as such
Articles.select { |id, name, price| price >= 42 }.each do |a|
  puts "#{a[:name]} => #{a[:price]}"
end

Literals

The rather tedious constructor Atom is only used when we use a literal as source object to a query.
Switch is able to cope with

1. numeric literals
2. strings
3. arbitrary nested lists
4. records

Numeric Literals and Strings

Atom(42).to_sql
Atom(3.14).to_sql
Atom("Hello World!").to_sql

Arbitrary Nested Lists

Atom([1,2,3]).to_sql
Atom([ [1,2], [3,4] ]).to_sql 
Atom([ [1,2], 3 ]).to_sql     # Attention: This is not possible

# more complicated stuff
Atom([[1,2,3],[4,5,6]]).flatten.take(4).drop(3).to_sql # => [4]
Atom([[[1,2,3],[4,5,6]]]).nth(1).nth(2).take(2).drop(1).to_sql # => [5] 

Records

Atom({ a:10, b:42 }).to_sql
Atom({ a:{ b:10, c:42 }, d:[12,67], e:"34" }).to_sql

Table Access

Tables in a database are automatically recognized by Switch. Supposed you have a table Articles in your database, accessing it via Switch is really smooth.

Articles.to_sql

Switch checks if there is really an Articles table in your database and provides a queryable object. Each table is simply a list of record. In case of Articles it would e.g. be the following ([{ id:1, name:"IPhone 4S", price:629.00 }, { id:2, name:"Macbook Air 13", price:1249.00}, ...])

# getting the name of each article
Articles.map { |a| a.name }.take(5).to_sql
# or
Articles.map { |id,name,price| name }.take(5).sql

# adding 42 to the price of each product
# that is cheaper than 23 bucks
Articles.select { |a| a.price < 23 }.
         map { |a| 
                   { id:a.id,
                     name:a.name,
                     price:a.price + 42 } }.
         to_sql

Use Ruby Syntax

Atom([[1,2,3],[3]]).map(&:length).to_sql # => [3,1]

Query Combinators aka Methods

The examples above are just a small subset of what you can accomplish with Switch. Switch supports the following methods you can use in your queries:

map select group_by sort_by partition uniq flatten flat_map zip all? any?
none? empty? one? member? append reverse take drop take_while drop_while
at first last length avg sum max min max_by min_by

Additional

The to_sql method accepts a file or a string to write to
to_sql("foo.sql") or to_sql(File.open("foo.sql", "w")).
Usually it writes to STDOUT.

References

• A Deep Embedding of Queries into Ruby
  Torsten Grust, Manuel Mayr.
  In Proceedings of the 28th IEEE International Conference on Data Engineering (ICDE 2012), Washington, DC, USA, April 2012.