Re: SQL Generator library

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

Relevant Pages

  • Re: SQL Generator library
    ... a relatively clean way. ... SQL is extremely flexible, ... syntax to three statements that seem to have ... SchemeQL[2] for Scheme that have similar goals. ...
    (comp.lang.ruby)
  • A parametric objects system
    ... messaging syntax resembles that of Objective-C. ... I am aware of variety of object systems devised for Scheme ... polymorphism, ad-hoc generics, parametric datatype ... maintain any registry of classes the resulting class ...
    (comp.lang.scheme)
  • Re: Connection.Execute causes untrappable runtime error
    ... Set Recordset = Database.Execute ... If I want to do a delete or Update, I do a Database.Execute ("SQL Syntax") ... I believe that the dataenvironment is the best thing in vb6 (The program are ...
    (microsoft.public.vb.database.ado)
  • Re: To LinQ or not LinQ
    ... i.e. "the SQL-like" syntax. ... You say SQL is set oriented and Linq is sequence oriented. ... that the languages differ in meaning even when they superficially look ... relational models are tightly coupled with SQL or that SQL is the ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQL, lite lite lite
    ...  'sqllite3' statements are valid SQL expressions, ... how and whether it can include arbitrary Python ... whether to permit transactions; and what the simplest and coolest ... thing you can do with a little Python syntax is. ...
    (comp.lang.python)