On Sat, 26 Aug 2006 00:33:03 +0200, Robert Klemme wrote:

> 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?

I'm not sure what you mean by an object graph.

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

While there's a lot of difference in the syntax, there's quite a bit of
similarity in the underlying components.

While a straight INSERT statement (not yet implemented) is quite different
than a SELECT statement, an INSERT...SELECT statement, a SELECT, a
CREATE TABLE...SELECT, and an UPDATE statement are all quite similar in
terms of inputs, but all a bit different (and complicated) in terms of
where to put things like column names.

I haven't implemented a DELETE statement yet (mostly because when they
get complicated enough in MYSQL, I have to do it in 3 steps:
CREATE...SELECT, DELETE, DROP TABLE), but I'll have a look at how to do
that.

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

Precisely

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

I haven't tried to abstract any of the nonportable stuff out. I've
abstracted out the basic syntax of SQL. All of the more advanced stuff,
you're on your own.

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

What I have now should work with most brands of RDBMS.

--Ken

-- 
Ken Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu/~kbloom1/