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