Criteria README
===============
Criteria is a module for abstracting queries to various data sets.
For instance, you might have a flat text file, or an array of Ruby
objects, or a SQL database, and wish to perform the same query on
any given source, without different versions of code for each.
This module was inspired by the work of flgr (on #ruby-lang) on
Junction, and the ENV.var work by h9k (also on #ruby-lang).
Here are some examples:
idx1 = SQLTable.new("orders")
q1 = (idx1.price > idx1.paid) & (idx1.duedate < Time.now.to_i)
q1.limit = 5
q1.order = :ASC
q1.order_by = idx1.name, idx1.age
puts q1.select
# => SELECT * FROM orders WHERE ((orders.price > orders.paid) AND
# (orders.duedate < 1062616643)) LIMIT 5 ORDER BY orders.name,
# orders.age ASC
The generic Table superclass with the same query:
idx2 = Table.new
q2 = (idx2.price > idx2.paid) & (idx2.duedate < Time.now.to_i)
puts q2.inspect
puts q1.inspect
# => (& (> :price :paid) (< :duedate 1062616719))
# => (& (> :price :paid) (< :duedate 1062616719))
The very simple 'mysql' table included works like SQLTable, except
it actually returns a MysqlRes for immediate use. There are other
included table types as well.
Download
--------
Changes
-------
1.1a - Bugfix: Parameterized query results now preserve attribute
information from subclasses.
1.1 - Feature: SQLTable now abstracts CREATE TABLE, INSERT, UPDATE,
DELETE, and DROP TABLE. See the reference for details.
Feature: MysqlTable updated to support new SQLTable features.
Also, #select_parse now parses output into Ruby types if
specified with create().
Feature: Criterion superclass now uses #x? to generate
Placeholder arguments, and #[] for parameterized queries.
See reference for details.
Bugfix: Selections from a FileTable query are now sorted even
if the key is not part of the selection. Thanks to Brett
Norris for finding this one.
Requirements
------------
* ruby 1.8
Install
-------
De-Compress archive and enter its top directory.
Then type:
$ ruby install.rb config
$ ruby install.rb setup
($ su)
# ruby install.rb install
You can also install files into your favorite directory
by supplying install.rb some options. Try "ruby install.rb --help".
Usage
-----
Basics
------
To use the Critera module, it's helpful to understand how it works.
The Table superclass is a class with most of the default instance
methods removed. Instead, when given a method (including any
operator methods), it generates a Criterion object in
#method_missing.
A Criterion is similar to a Table---it's another nearly-blank class,
except it tracks the method call given to Table. It also chains to
another Criterion. When evaluated recursively along this chain, the
syntax of the statement can be restored.
To use it, merely chain a number of method calls together:
t = Table.new
t.foo.bar # => (bar :foo)
t.foo.bar(42) # => (bar :foo 42)
You should think of "foo" as a field name here, rather than a
method. It represents each object in table in the field "foo".
If we try sending this odd parameters, it becomes apparent quickly:
t.foo 42 # => (42 :foo) # This makes no sense
Operator methods chain naturally as well, and retain their natural
ruby precedence. Since && and || are not methods, you must use &
and |, so beware:
t.foo > t.bar & t.baz < t.quux
# => (< (> :foo (& :bar :baz)) :quux)
Not quite what we intended! Try this:
(t.foo > t.bar) & (t.baz < t.quux)
# => (& (> :foo :bar) (< :baz :quuz))
Much better. & and | chain normally, of course:
(t.foo > t.bar) & (t.baz < t.quuz) | (t.foo <=> t.quux)
# => (| (& (> :foo :bar) (< :baz :quuz)) (<=> :foo :quux))
New as of 2003/10/04: Placeholders and parameterized queries.
It's nice to be able to store a given query in a constant or
variable so you can reference it in a concise manner:
TBL_MINORS = (tbl.age < 18)
TBL_MINORS.sort_by = [:name, :age]
Most queries, however, involve some sort of variable parameters, so
constructing the query once with static values is not possible.
Placeholders now remedy this:
TBL_AGE_RANGE = (tbl.age > tbl.x?) & (tbl.age < tbl.x?)
TBL_AGE_RANGE.order_by = [:name, :age]
The #x? call defines a "placeholder" variable. You can fill these
in later as desired:
TBL_AGE_RANGE[20, 30]
# => (& (> :age 20)
# (< :age 30))
This works automatically for subclasses such as SQLTable.
SQLTable
--------
The various subclasses of Table work the same; they merely provide
ways for rendering the results differently. SQLTable is one of
these; it generates a SQLCriterion, which provides various useful
methods for generating SQL queries:
t1 = SQLTable.new("table1")
t2 = SQLTable.new("table2")
q1 = (t1.name.like "B%") # => (like :name "B%")
q2 = (t2.price < 50) # => (< :price 50)
q1.select
# => "SELECT * FROM table1 WHERE (table1.name like 'B%')"
q2.select(t2.type)
# => "SELECT table2.type FROM table2 WHERE (table2.price < 50)"
q1.left_join(t2).select
# => "SELECT * FROM table1 LEFT JOIN table2 WHERE (table1.name like 'B%')"
j = (t1.id == t2.id)
q1.left_outer_join(t2, j).select(t1.name)
# => "SELECT table1.name FROM table1 LEFT OUTER JOIN (table1.id = table2.id) WHERE (table1.name like 'B%')"
As you can see, most SQL operations are available to you. Further
options for limiting and ordering results are also available; see
't/sql.rb' for an example.
ArrayTable
----------
While Ruby has blocks, and makes this operation somewhat redundant,
it may be useful if you need to abstract the same query to an
array. You may add a list of objects to an array, and create a
query which calls methods directly on each object, and returns the
resulting members which match.
For an example, see "t/array.rb"
FileTable
---------
Somewhat more useful than ArrayTable is FileTable, which lets you
treat a flat text file as a table. An example file is provided in
"t/file.dat", along with the test "t/file.rb". Rows and their types
are defined, and matching rows returned.
MysqlTable
----------
MysqlTable is probably more useful than SQLTable alone, as it gives
you results from a MySQL table instead of merely returning the SQL
statement. All you have to do is give it a Mysql connection as well
as a table name, and it can be otherwise used the same as SQLTable.
As you can see from the code, MysqlTable is a very simple subclass
of SQLTable. Further databases can easily be supported with only a
few similar lines of code. If you come up with any, please submit
them for inclusion! I have not yet had the time to make more, and I
don't have the resources to create one for every DB that exists.
Further Development
-------------------
If anyone comes up with any other useful Table classes or patches,
please send them to me for inclusion.
In the future, I'd like to include support for abstracted UPDATEs to
all queries, and even table creation. For some, this is tricky
(such as the FileTable, since the goal is to support very large
files that can't be processed in memory), but generally this won't
be too hard to accomplish.
The main purpose for the Criteria module is for integration into
Mephle (http://mephle.org/), for quick, indexed lookups across
thousands or millions of objects. The original SQLIndex class was
very ugly, and, of course, tied to SQL. The new Criteria module
should abstract away SQL while leaving the same capabilities, in a
much more elegant form.
Reference
---------
SQLTable
--------
SQLTable::new("TABLE")
Return a new SQLTable with the name TABLE. This name will be
used in generating queries where appropriate.
SQLTable::create("TABLE", ...)
Generate a CREATE TABLE statement for table TABLE. Arguments
that follow should be arrays in the following form:
[:COLUMN-TITLE, RUBY-CLASS[, "SQL DECLARATION"]]
Following the column declarations, SQLFn.* may be used to specify
additional declarations. The only specifically predefined
declaration at this time is SQLFn.pk, which defines a primary
key. A complete example follows:
SQLTable.create("Person",
[:id, Integer],
[:first, String, "VARCHAR(15)"],
[:last, String, "VARCHAR(15)"],
[:age, Integer],
SQLFn.pk(:id))
# => "CREATE TABLE Person (id int, first VARCHAR(15), last
# VARCHAR(15), age int, PRIMARY KEY(id))"
SQLTable::drop("TABLE")
Generate a DROP TABLE statement for the table TABLE.
SQLTable#
Return a SQLCriterion for . This is used for producing
query chains, which is how queries are produced. There are
certain exceptions to . The basic methods outlined in the
Blank base class, all methods with leading underscore, and the
following defined instance methods are reserved and should not be
used in queries.
SQLTable#insert(HASH)
SQLTable#insert(VALUE1[, VALUE2[, VALUE3[, ...]]])
Generate an INSERT statement for the table. The first syntax
allows a hash of column titles to values. This is compatible
with Ruby's current "named parameter" scheme:
tbl = SQLTable.new("Person")
tbl.insert(:name => "John Doe",
:age => 42)
# => "INSERT INTO Person (age, name) VALUES (42, 'John Doe')"
As columns are taken from a hash, the actual insert order is not
guaranteed.
The second syntax is for inserting a complete row where column
titles are not necessary:
tbl.insert(nil, "Jane Doe", 42)
# => "INSERT INTO Person VALUES (NULL, 'Jane Doe', 42)"
As you can see, values are translated naturally into their
respective SQL types.
SQLFn
-----
SQLFn::
Generate a SQLFn object for various purposes, useful for CREATE
TABLE. The result is the string in uppercase, unless
otherwise defined.
SQLFn.unique(:first, :last) # => UNIQUE(first, last)
SQLFn::pk(COL1[, COL2[, ...]])
Generate a PRIMARY KEY specification, as follows:
SQLFn.pk(:id1, :id2) # => PRIMARY KEY(id1, id2)
SQLCriterion
------------
SQLCriterion#
Generate a new SQLCriterion term with the name . This is
used to chain criteria to produce queries. There are certain
exceptions to . The basic methods outlined in the Blank
base class, all methods with leading underscore, and the
following defined instance methods are reserved and should not be
used in queries.
SQLCriterion#update(HASH)
Generate an UPDATE statement. HASH should be a hash of column
titles to values. This is compatible with Ruby's "named
parameter" scheme:
(tbl.name.nil?).update(:name => "John Doe")
# => "UPDATE Person SET name = 'John Doe' WHERE (Person.name
# IS NULL)"
The special chain '*' may be specified to refer to all rows:
tbl.*.update(:time => Time.now)
Criteria chains may be used for values to specify relative
updates:
(tbl.bday == "2003/10/04").update(:age => tbl.age + 1)
# => "UPDATE Person SET age = (Person.age + 1) WHERE
# (Person.bday = '2003/10/04')"
SQLTable#select([C1[, C2[, ...]]])
Generate a SELECT statement. If no columns are specified, all
columns are selected with "SELECT *". The special chain * may be
used to refer to all rows:
tbl.*.select
# => "SELECT * FROM Person"
Columns should be specified via Criteria, so all the proper table
references are picked up:
tbl.*.select(tbl.name, tbl.age)
# => "SELECT Person.name, Person.age FROM Person"
SQLCriterion#(TABLE)
SQLCriterion#(QUERY, TABLE)
Generate a join query of type . The valid join types are as
follows:
join inner_join cross_join
left_join left_outer_join
right_join right_outer_join
full_join full_outer_join
For OUTER-type joins, the join query may be specifed as a
parameter:
idx1 = SQLTable.new("my_table")
idx2 = SQLTable.new("my_other_table")
join = (idx1.id == idx2.order_id)
query = (idx1.name == "Crono")
query.left_outer_join(join, idx2).select(idx1.id, idx2)
# => "SELECT my_table.id, my_other_table.* FROM my_table,
# my_other_table LEFT OUTER JOIN my_other_table ON (my_table.id
# = my_other_table.order_id) WHERE (my_table.name = 'Crono')"
SQLCriterion#delete
Generate a DELETE statement for the query:
(tbl.age < 18).delete
# => "DELETE FROM Person WHERE (Person.age < 18)"
The special chain '*' may be specified to refer to all rows:
tbl.*.delete
# => "DELETE FROM Person"
MysqlTable
----------
This class reimplements the various functions in SQLTable, and
applies the queries directly to the database. In addition, it
overrides some internal functions to provide automatic type escaping
for strings and other types.
MysqlTable::new(DB, "TABLE"[, TYPEMAP])
This creates a new MysqlTable object for the table TABLE, using
the connection DB. DB should be a connection as generated by
Mysql.connect in the ruby-mysql package.
TYPEMAP may be specified to do automatic conversions to ruby
types for each column during a SELECT. See #select_parse below.
MysqlTable::create(DB, "TABLE", ...)
This is identical to SQLTable::create, except it actually
performs the CREATE TABLE over the connection DB. It then
returns a new MysqlTable object.
Special Note: If a table already exists, it will NOT be flagged
as an error. This is so you can specify a table =
MysqlTable.create statement in your code without checking. Use
the appropriate ruby-mysql functions if you want to check for a
table's prior existence.
MysqlTable::drop(DB, "TABLE")
Drop the table TABLE given DB.
MysqlTable#insert
Functions identically to SQLTable#insert, except the query is
performed on the table.
MysqlCriterion
--------------
This class reimplements the various functions in SQLCriterion, and
applies the queries directly to the database. All functions perform
identically, except they return a MysqlRes object instead of the SQL
string. Exceptions noted below.
MysqlCriterion#select_parse(...)
This functions identically to #select, except instead of
returning a MysqlRes, it returns a MysqlParsedRes, which parses
columns.
MysqlParsedRes
--------------
This is _not_ a subclass of MysqlRes. Instead, it provides a few
useful functions for accessing the MysqlRes.
MysqlParsedRes#each BLOCK
Iterate through the result, much like MysqlRes#each. Results are
parsed according to the specification to the original MysqlTable.
MysqlParsedRes#to_a
Returns the entire (parsed) result as an array. Useful for small
results where iteration is overkill.
MysqlParsedRes#num_rows
Returns the number of rows in the result.
MysqlParsedRes#res
Returns the MysqlRes object.
License
-------
Criteria - Ruby language-level query abstraction
Copyright (C) 2003 Ryan Pavlik
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.
This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Lesser General Public License for more details.
You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
Ryan Pavlik <rpav@mephle.com>