Robert Klemme
8/25/2006 10:33:00 PM
Ken Bloom wrote:
> The main goal of this library is to be able to construct an SQL statement
> from "slices" that concern different aspects of the final query (perhaps
> in different places in your code) and then combine them all together into
> one statement easily.
So you are basically constructing a complex filter condition or even a
complete SQL statement via some form of object graph, correct?
> Another important goal of this library is to give some consistent Ruby
> syntax to three statements (INSERT, SELECT, and UPDATE) that seem to have
> different enough syntax that one has two write different code to generate
> each kind of statement.
What about DELETE? Also, I'm not sure how you are able to create a
consistent syntax for all of these as they perform different operations
and need a differing set of inputs.
> I use my SQL database (specifically MySQL) largely as a bulk data
> processing engine, by doing INSERT...SELECT or CREATE TABLE...SELECT
> statements. This library is intended to make that kind of coding easier. I
> expect that Object Relational mappers (such as ActiveRecord) are more
> useful for most people, who are performing queries and
> inserting/updating/querying for individual records. In time, I'll probably
> add classes to help with these too, to have some consistency
>
> An interesting library that I've seen is CLSQL[1] for Common LISP, or
> SchemeQL[2] for Scheme that have similar goals. Scheme and LISP's use
> of s-expressions make it very easy to construct an entire sublanguage for
> the WHERE clause, simply by list parsing. I'm not sure if it's
> possible to do that in Ruby (if anyone has, I'd like to know. Criteria[3]
> perhaps.), but this library covers some basic steps in ironing out SQL's
> complexities. I also can't get the compile time checking that SchemeQL
> advertises, mostly because of duck typing, but also to an extent becasue
> Ruby isn't compiled.
>
> This library doesn't try to abstract out the limitations of your DBMS, and
> I think that the SQL it uses should be fairly portable, in large measure
> because it hasn't attempted to deal with serious CREATE TABLE statements,
> where a lot of syntax concerning types, keys and sequences is much more
> variable.
But it contains DDL and thus is not portable. With SQL you can only
rely on DML being portable. And even then you will occasionally stumble
into things you cannot do without DB specific SQL generation. Maybe you
should plan for multi dialect SQL generation if you intend to distribute
this to a wider audience. If people find it useful I am sure they will
want to use it with their favorite brand of RDBMS...
Cheers
robert