From: Ezra Zygmuntowicz <ezmobius@...>
Date: 2006-08-26T04:22:18+09:00
Subject: Re: SQL Generator library

--Apple-Mail-9--14610294
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed

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


Hey Ken-

	You may be interested in taking a look at a library I wrote called  
ez-where[1]. It is distributed as a plugin for rals and focuses on  
building the WHERE clause for active record queries. But most of it  
can be used for generating where clauses and could be used wihout  
rails as a stand alone where generator. Its focus is on having a nice  
ruby dsl style syntax for  declaring the conditions as objects.

	Here are a few simple examples from the test cases:

    def test_cc_with_multiple_statements
       a = c { foo == 'bar' }
       b = c { baz =~ '%qux%' }
       c = c { age > 20 }
       d = c { gemz === (1..5) }
       expected = ["((foo = ?) AND (baz LIKE ?)) OR ((age > ?) AND  
NOT (gemz IN (?)))", "bar", "%qux%", 20, [1, 2, 3, 4, 5]]
       assert_equal expected, (a + b | c - d).to_sql
    end

    def test_multi_clause
       expected = ["(my_table.title LIKE ? OR my_table.subtitle  
LIKE ? OR my_table.body LIKE ? OR my_table.footnotes LIKE ? OR  
my_table.keywords LIKE ?)", "%package%", "%package%", "%package%", "% 
package%", "%package%"]

       multi = EZ::Where::MultiClause.new 
([:title, :subtitle, :body, :footnotes, :keywords], :my_table)
       multi =~ '%package%'
       assert_equal expected, multi.to_sql

       cond = EZ::Where::Condition.new :my_table
       cond.any_of(:title, :subtitle, :body, :footnotes, :keywords)  
=~ '%package%'

       assert_equal expected, cond.to_sql

       cond = EZ::Where::Condition.new :my_table
       cond.any_of(:title, :subtitle, :body, :footnotes, :keywords)  
=~ '%package%'
       cond.all_of(:active, :flagged) == true

       expected = ["(my_table.title LIKE ? OR my_table.subtitle  
LIKE ? OR my_table.body LIKE ? OR my_table.footnotes LIKE ? OR  
my_table.keywords LIKE ?) AND (my_table.active = ? AND  
my_table.flagged = ?)", "%package%", "%package%", "%package%", "% 
package%", "%package%", true, true]
       assert_equal expected, cond.to_sql

       expected = ["(my_table.title LIKE ? OR my_table.subtitle  
LIKE ? OR my_table.body LIKE ? OR my_table.footnotes LIKE ? OR  
my_table.keywords LIKE ?) OR (my_table.active = ? AND  
my_table.flagged = ?)", "%package%", "%package%", "%package%", "% 
package%", "%package%", true, true]
       assert_equal expected, cond.to_sql(:or)
    end

Cheers-
-Ezra

[1] svn://rubyforge.org/var/svn/ez-where





--Apple-Mail-9--14610294--