RUR: A modern Ruby replacement of SQL

rur_intro.rb

# RUR (Relationally Universal Ruby) is a Ruby relational algebra DSL 
# that compiles to SQL, allowing you to write complex
# queries without leaving your favorite language. It is going to be a nice
# Ruby-like replacement of SQL in the same manner CoffeeScript is a
# Ruby-like replacement of JavaScript. It will use all the features
# found in a modern RDBMS (views, temp views, constraints, triggers, functions,
# etc) without being tightly coupled to one particular implementation.
#
# Unlike ActiveRecord, which maps tables to classes, RUR maps relations to
# objects.
#
# Like Sequel, RUR uses a global object to interact with the database.
# 
# An example of a complex query that would be possible to be written in RUR
# without resorting to SQL in any form, with no SQL leaking in your code:
#
# CREATE OR REPLACE TEMP VIEW profit_by_month AS (
#     SELECT to_month(sales_drill.date) AS month,
#            SUM("profit in EUR ")::int AS profit
#     FROM "tutuf::visual_query"."Sales Drill" AS sales_drill
#     GROUP BY month);
#
# CREATE OR REPLACE TEMP VIEW product_types_profit_by_month AS(
#   WITH filler AS (
#     SELECT DISTINCT generate_series('2013-11-01'::date,
#                                     '2013-12-04'::date,
#                                     '1 month')::timestamp without time zone AS date,
#                     specific_type AS product_type
#     FROM products)
#   SELECT COALESCE(to_month(filler.date), to_month(sales_drill.date)) AS period,
#          product_type,
#          COALESCE( SUM("profit in EUR ")::int, 0)  AS profit
#   FROM "tutuf::visual_query"."Sales Drill" AS sales_drill
#   RIGHT JOIN filler ON to_month(filler.date) = to_month(sales_drill.date)
#                     AND filler.product_type = sales_drill.type
#   WHERE date BETWEEN '2013-11-01' AND '2013-12-04'
#   GROUP BY period,
#            product_type
#   ORDER BY period DESC,
#            profit DESC);
#
# SELECT product_type,
#        period,
#        (100 * product_types_profit_by_month.profit::numeric(10,2) / 
#          profit_by_month.profit::numeric(10,2))::numeric(10,2) AS "% profit"
# FROM profit_by_month
# JOIN product_types_profit_by_month ON profit_by_month.month =
# product_types_profit_by_month.period ORDER BY "% profit" DESC
#
#
# Some simpler examples to help you get a notion of RUR:

# load relations from the database
tablets = DB['tablets']
brands = DB['brands']

# read metadata about relations that is stored in the database only
tablets.associations
# => [DB['brands']]

# math notation for relational algebra operations
tablet_brands = tablets × brands

tablet_brand_names = tablet_brands[brands.name]

# idiomatic Ruby code for idiomatic operations
users = Table.create(id: serial, 
                     name: string(unique),
                     admin: boolean(default: false))
users.insert(name: clients.name)
admins = View.create(users[admin: true])

# support for PostgreSQL schemas
ekont.cities.insert( ... )

axiom – ruby gem that simplifies querying of structured data using relational algebra.

relation = Axiom::Relation.new(
  [[:id, String], 
   [:name, String],
   [:color, String],
   [:weight, Float],
   [:city, String]],
  [
    ['P1', 'Nut',   'Red',   12.0, 'London'],
    ['P2', 'Bolt',  'Green', 17.0, 'Paris' ],
    ['P3', 'Screw', 'Blue',  17.0, 'Oslo'  ],
    ['P4', 'Screw', 'Red',   14.0, 'London'],
    ['P5', 'Cam',   'Blue',  12.0, 'Paris' ],
    ['P6', 'Cog',   'Red',   19.0, 'London'],
 ]
)

# Relational Operators
# --------------------

# projection
new_relation = relation.project([:id])

# rename
new_relation = relation.rename(id: :other_id, name: :other_name)

# restriction
new_relation = relation.restrict do |r|
  r.name.eq('Screw').or(r.city.eq('London'))
end

# natural join
new_relation = relation.join(other)        # OR relation + other

# product
new_relation = relation.product(other)     # OR relation * other

# intersection
new_relation = relation.intersect(other)   # OR relation & other

# union
new_relation = relation.union(other)       # OR relation | other

# difference
new_relation = relation.difference(other)  # OR relation - other

# theta-join
#
# NOTE: theta-join is effectively restricting a product of the relations
new_relation = relation.join(other) { |r| r.id.gte(r.other_id) }

# group
grouped = relation.group(products: [:product_name])

# ungroup
ungrouped = relation.ungroup(:products)

# wrap
wrapped = relation.wrap(address: [:street, :city, :state, :zip, :country])

# unwrap
unwrapped = relation.unwrap(:address)

# extend
new_relation = relation.extend do |r| 
  r.add(:pounds) { |t| t[:weight] * 2.2 }
end

# summarize
new_relation = relation.summarize(relation.project([:city])) do |r|
  r.add(:count, r.id.count)
end
new_relation = relation.summarize(relation.project([:city])) do |r|
  r.add(:count) { |acc, t| acc.to_i + 1 }
end


# Updatable Views
# ---------------

# add a set to a relation variable
new_relation = relation.insert(other)

# remove a set from a relation variable
new_relation = relation.delete(other)

# replace a relation variable with another set
new_relation = relation.replace(other)

Switch – a deep-embedding of queries into Ruby and Ruby on Rails, which aims to blur the traditional lines between the host language and the relational database backend. With Switch, there are

1. no syntactic or stylistic differences between Ruby programs that operate over in-memory array objects or database-resident tables,
2. even if these programs rely on array order and nesting.
3. Switch’s built-in compiler and SQL code generator guarantee to emit few queries, addressing performance issues that arise in ActiveRecord.

# 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

Theoretical background on Switch: A Deep Embedding of Queries into Ruby

SSQL – a way of embedding SQL as S-expressions into Scheme code.

Okay, so it turns out that this idea works beautifully. Let's look at why I think this DSL was a failure. One reason is the fact that SQL is a huge language, especially when you consider all the extensions provided by various implementations. ... The SQL grammar is so complicated that its sheer size has serious performance implications on a parser.

Recently, the relational algebra has been gaining some more interest. For example, there's Alf for Ruby. I think this is a better approach; relational algebra has just a handful of concepts and there's no syntax associated with it, so you can invent your own syntax to best fit your DSL. ... The flip side of such a high level of abstraction is that it will be harder to make use of any killer features offered by your RDBMS; you get the lowest common denominator in features.
Optimizing queries also becomes hard. You can no longer hand-optimize them when writing them, and you'd probably end up with an optimizer in your library. This is pretty insane, since there's also an SQL optimizer and query planner inside your RDBMS, so you're doing twice the work, and there's twice the opportunity for getting it wrong.

There's a popular library for Common Lisp called CLSQL. It looks like an enormous amount of engineering went into it. If that's required to get a useful SQL DSL, it might not be worth it unless the advantages outweigh the effort required. Note that even after 10 years of development, CLSQL still has no outer join support. I think that's indicative of how hard it is to properly support SQL from a DSL.

FitNesse is the acceptance test wiki (Java)

Idiomatic ruby:

Version.find_by_name('TODO')
  .fixed_issues
  .map{ |issue| issue.category }
  .uniq

... and ActiveRecord:

 IssueCategory.joins(
      'RIGHT JOIN issues ON issue_categories.id = issues.category_id
       JOIN versions ON issues.fixed_version_id = versions.id')
    .where("versions.name='TODO'")
    .distinct
    .order(:name)

Dataflow in Ruby – code in terms of a pipeline for data, transforming it and summarizing it into information.

Our job is not to write software. Our job is to turn data into information. That we do that through software is an implementation detail.

Squeel lets you rewrite...

Article.where ['created_at >= ?', 2.weeks.ago]

...as...

Article.where{created_at >= 2.weeks.ago}