[Ruby-DBI-devel] "WHERE" clause generation

[Ben G. <bg...@in...>]

Hi all,

I recently wrote a ruby file that some people might find useful.  Its
purpose is to make generating SQL statements (especially those involving
"WHERE" clauses easier.  The "meat" is an abstract class "Clause" with
some subclasses, FixedClause, BasicClause, AndClause, OrClause...  These
clauses together use the "Interpreter" design pattern to allow nesting
of clauses, so an "AndClause" can contain 2 or more other types of
clause, and those clauses can be other "AndClause"s, "OrClause"s or
"BasicClause"s.  Here's a sample usage:




require 'clauses'

include DBI::SQL::Clauses

frank_20_plus = AndClause.new(BasicClause.new(*%w"Name LIKE Frank%"),
                              BasicClause.new("Age", ">", "20"))

dave_carp_30 = AndClause.new(LiteralClause.new("Name LIKE 'Dave%'"),
                             BasicClause.new("Age", "30"),
                             BasicClause.new("City", "Carp"))

friends = OrClause.new(frank_20_plus,
                       dave_carp_30,
                       "Name = 'Gunther Schwinn'")

res = friends.get_pair
# res[0] == "(((Name LIKE ?) AND (Age > ?)) OR ((Name LIKE ?) AND (Age = ?) AND (City = ?)) OR (Name = ?))"
# res[1] == ["Frank%", 20, "Dave%", 30, "Carp", "Gunther Schwinn"]

# or

res = friends.get_clause
# res == "(((Name LIKE "Frank%") AND (Age > 20)) OR ((Name LIKE "Dave%") AND (Age = 30) AND (City = "Carp")) OR (Name = "Gunther Schwinn"))"

DBI.connect('DBI:Mysql:test', 'test', 'password') { |dbh|
  dbh.prepareFixedSelect('Friends', '*', friends) { |sql|
    all = dbh.select_all(sql)
  }
}



The file, "clauses.rb" puts the Clause classes in the DBI::SQL::Clauses
module, and adds a few methods to DBI.DatabaseHandle.  There are still
some implementation details I'm trying to figure out, such as, how best
to handle quoting of strings in the clauses -- they don't know about any
active DatabaseHandle so they can't use the current DB to quote.

You can get the file here:

http://infofiend.com/dbi/clauses.rb

If anybody finds it useful I'd be happy to either put it up on RAA or
roll it into the DBI code somewhere.

Ben
-- 
Ben Giddings <bg...@in...>