Friday, March 02, 2007

Ruby: sqldsl new version

I recently put sqldsl to work on my current project. Part of putting it into our codebase was extending it to support some additional syntax. The new syntax support was extracted and put into the 1.2.2 release of sqldsl.

The 1.2.2 release adds a few minor features and a significant change.

The where block now allows you to specify conditions using traditional ruby syntax.
Select[:column1].from[:table1, :table2].where do
table1.column1 >= 1
table1.column2 <= 20
table1.column3 = table2.column1
table1.column4 = 'foo'
This change creates very nice looking code in the where block, but I'm not entirely sure it's a step in the right direction. While it looks great in the above example it also allows you to use variables, which can be confusing.
some_val = 1
Select[:column1].from[:table1].where do
column1 = some_val
end.to_sql #=> "select column1 from table1 where column1 = 1
In the above example column1 and some_val look very similar, but create very different output in the generated sql string. The alternative is to use the existing equal syntax.
Select[:column1].from[:table1].where do
equal :column1, some_val
In the previous example it is very clear that column1 is a column and some_val is a variable. The problem with this solution is that it begins to fall down when you consider <=, >=, etc. To support conditions with these operators we would have needed to implement the following (arguably ugly) syntax.
Select[:column1].from[:table1].where do
less_than_equal :column1, some_val
When faced with this decision we opted for the example that simply allowed ruby syntax.

The not_null method was added to the options available in where conditions.
Select[:column1].from[:table1].where do
not_null :column2
end.to_sql #=> "select column1 from table1 where column2 is not null"
The is_not_in method was added to String, Numeric, and Symbol.
Select[:column1].from[:table1].where do
:column1.is_not_in do
end.to_sql #=> "select column1 from table1 where column1 not in (select column1 from table2)"
Table and column aliasing was also added in this release.
Select[:column1 => :total].from[:table1 => :results].to_sql #=> "select column1 as total from table1 as results"
sqldsl 1.2.2 is available via a gem install.
> gem install sqldsl
Post a Comment