Ruby: sqldsl 1.4.0 released

This morning I released the 1.4.0 version of sqldsl. This release is mostly in response to the suggestions that sqldsl has recently gotten from the Ruby Community. The new version can be retrieved via "gem install sqldsl" as soon as the file propagates. For now, it can be downloaded from the download page.

Breaking Changes:
Table and column aliasing is now done with an 'as' method instead of using hashes. This change was necessary since column order is important and hashes are unordered.

irb> Select[:column1.as(:book), 1.as(:total), 'foo'.as(:constant)].to_sql
=> "select column1 as book, 1 as total, 'foo' as constant"

irb> Select.all.from[:table1.as(:aliased_table_name)].to_sql
=> "select * from table1 aliased_table_name"

Equality in the where clause has been changed from a single equals to double equals. I personally preferred the single equals; however, it broke in scenarios where columns were not prefixed by table names. The previous solution did not work because ruby assigned a local variable instead of raising a method_missing and allowing the ReceiveAny class to handle the assignment.

The new syntax is the same, except it uses == instead of =.

Select[:column1].from[:table1].where do
  column1 == 99
end.to_sql
=> "select column1 from table1 where column1 = 99"

New Feature:
Inner Joins are now also supported

Select.all.from[:t1.as(:a)].inner_join[:t2.as(:b)].on do
  a.id == b.id
end.inner_join[:t3.as(:c)].on do
  b.id2 == c.id
end.to_sql
=> "select * from t1 a inner join t2 b on a.id = b.id inner join t3 c on b.id2 = c.id"

Bug Fix:
Or conditions are surrounded by parenthesis.

Select[:column1].from[:table1].where do
  column1.equal 0
end.or do
  column1 > 100
end.to_sql
=> "select column1 from table1 where column1 = 0 or (column1 > 100)"

Ruby: Why sqldsl?

I recently wrote about introducing sqldsl into my current project. This led to a few conversations on what are the pros and cons of using sqldsl.

Cons:

• Additional characters required in simple statements.

  "select column1 from table1 
  where name = '#{person_name}'"
  
  # becomes
  
  Select[:column1].from[:table1].where do
    name = person_name
  end

• Another library creates a larger barrier to entry when dealing with a foreign code base.

Pros:

• Objects know how to quote themselves.

  1.to_sql                     #=> "1"
  "foo".to_sql                 #=> "'foo'"
  :column.to_sql               #=> "column"
  [1, 'foo', :column].to_sql   #=> "1, 'foo', column"

• Syntax Highlighting
• Better than string concatenation to generate SQL statements.

  "escaped_values = hash.values.collect { |value| sql_escape value }
  insert into #{hash.keys.join(", ")} values #{escaped_values.join(", ")}"
  
  # becomes
  
  Insert.into[hash.keys.to_sql].values[hash.values.to_sql]

• Much better support for conditional statement building.

  "select column1 from table1, table2
  where table1.column1 = table2.table1_id
  #{"and table1.column2 >= #{quantity}" if quantity > 0}"
  
  # becomes
  
  Select[:column1].from[:table1, :table2].where do
    table1.column1 = table2.table1_id
    table1.column2 >= quantity if quantity > 0
  end

• Fail without requiring a database trip for referencing tables in the where statement that do not appear in the table list.

  Select[:column1].from[table1].where do
    table1.column1 = table2.column1
  end  #=> Raises an error stating that table2 does not exist in the from clause.

• Open for extension where necessary. For example, ActiveRecord::Base.execute could be changed to take a block.

  class ActiveRecord::Base
    alias original_execute execute
    def execute(arg, &block)
      original_execute block.call.to_sql(connection)
    end
  end

  If this change were made, the sqldsl library could use the connection to create vendor specific sql. This allows for things like changing sql to be prepared statement compatible when the oracle adapter supports this in the future. The point is, as long as your sql is nothing more than a string it is not able to be easily modified. However, an object representation of your sql could be evaluated in various contexts to produce different results.

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.

Significant:
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'
end

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
end

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
end

When faced with this decision we opted for the example that simply allowed ruby syntax.

Minor:
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
    Select[:column1].from[:table2]
  end
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