Hi all,
I’ve been looking at the Phrasebook Design Pattern recently.
Specifically, I’d like to use it for externalizing SQL statements. Even
more specifically, I’d like to use Ruby with YAML (versus XML, for
example).
For a quick rundown on what I’m talking about, download the Sharon and
Pinchuk article:
http://jerry.cs.uiuc.edu/~plop/plop2k/proceedings/Pinchuk/Pinchuk.pdf
Also visit: http://www.perl.com/pub/a/2002/10/22/phrasebook.html
The article is perl specific, but generic enough to where I think you’ll
get the idea.
Also see http://lwn.net/Articles/14150/ for some issues with Perl’s
Class::Phrasebook that I wish to avoid at the outset.
Being a YAML neophyte, I’m trying to come up with a general ‘schema’ for
how the yaml should look for this, but I’m not entirely sure. Something
like:
sql.yml - very raw idea
main_query:
sql: select * from some_table, other_table where some_column = ? and
some_column = other_column
comment: run this first
vendor:
oracle:
hint: /*+ use some_other_index /
outer_join: true
postgres:
hint: / I don’t know if postgres has hints */
outer_join: true
phone_query
sql: select tn from tn_table where tn = ?
comment: gets the telephone
I don’t think this parses the way I’d like. I’m now sure how it should
look and that’s where, for now, I need some help. The general idea is
to keep the SQL external from the program itself. Within the program,
with our imaginary API, you would do something like:
require "phrasebook/sql"
p = Phrasebook::SQL.new(sql.yml)
sth.execute( p.get(:sql => “main_query”,:vendor => “oracle”,:some_column
=> 5551212) )
Where p.get(:sql=>“main_query”) would return:
select /*+ use some_other index */
from some_table, other_table
where column = ?
and some_column = other_column (+)
Using this (theoretical) approach you can keep the vendor specific
portions of the SQL separate and let the phrasebook construct the sql as
appropriate. It would require some training to understand vendor
specific stuff - perhaps it could be put in a separate yaml file. 
So, in addition to comments about how the YAML should look, what do
folks think about a Ruby/SQL/YAML phrasebook in general? Comments,
suggestions, API specific ideas all welcome.
Regards,
Dan
···
–
a = [74, 117, 115, 116, 32, 65, 110, 111, 116, 104, 101, 114, 32, 82]
a.push(117,98, 121, 32, 72, 97, 99, 107, 101, 114)
puts a.pack(“C*”)