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

Squeel lets you rewrite...

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

...as...

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