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( ... )

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}