From: John Carter <john.carter@...>
Date: 2008-09-26T08:43:31+09:00
Subject: How to make Ruby _THE_ scripting language of choice, fold in SQLite

Much has been written about the OOP / RDBMS impedance mismatch....

But when it comes down to it, SQL wins for certain tasks, and
ruby/perl/python wins for other tasks.

Sure we have adapters that provide access from Ruby to SQL databases,
but at some primitive level... the query is written as a string, the
string is fed to a SQL interpreter, the query is optimized by the SQL
engine and the results returned.

In some cases the results are returned as strings in other cases
various levels of conversion can be ommitted.

The fact is the function call notation is insufficiently rich to
specify an arbitary SQL statement without reducing to the (very)
lowest common denominator (string) and then invoking a (heavyweight)
SQL interpretor.

Suggestion to make Ruby the undisputed leader of the scripting languages...
   1) Extend Ruby syntax to accept a useful subset of SQLite SQL.

   2) incorporate the appropriate bits of SQLITE into the Ruby
      interpretor to evaluate it.

   3) and perhaps optionally generate string queries for
      (mysql/postgres/oracle...) backends.

   4) provide a means of transparently mapping certain classes to
      tables so that they are automagically persisted at commit points,
      on garbage collection or program exit, and automagically
      revivified on reference.

   5) Hide the distinction between Object "handles" and foreign
      keys. ie. If instance variable @c of an object of class A is
      a reference to an instance of class / table C, then
      following that reference may cause that row of table C to be
      looked up and revivified as an instance of class C.

# Attaches, unless told otherwise to sqlite db __FILE__.db
require 'rubysql'

class Customer
   attr_primary_key :name
   attr_reader :street, :city

# Map this class to a sql table called Customer with primary key :name and fields street and city.
   include persistent
end

class Invoice
   attr_foreign :customer, Customer

end

# It's persisted, so even if this appears a useless and can be garbage
# collected, it will at least be added to Customer table
Customer.new( "Joe Blogs", "52 hell", "Doggcity")

jane = Customer.new( "Jane Blag", "52 Sell", "Dodgcity")

a = Invoice.new

# The following three lines should be equivalent...
a.customer = "Jane Blag" 
a.customer = jane
a.customer = select * from Customer where name == "Jane Blag"

# An both should transparently and automagically allow ...
assert_equal( "Dodgcity", a.customer.city)

and a.customer.city be the same as...
   select city from Customer where name == a.customer;