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
- numeric literals
- strings
- arbitrary nested lists
- 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.