Friday, March 09, 2007

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.

13 comments:

  1. How do you handle OR conditions? That's the biggest stumbling block in my thinking since Ruby's going to short-circuit dsl evaluation if you use the normal operators.

    Also, I'm curious why you chose #= instead of #== for equality. It seems a little jarring?

    ReplyDelete
  2. sam,
    I've not needed an OR condition yet. I hadn't really thought about it. I wouldn't add it to the library until I (or someone else using the lib) needed it. I could picture it working like:

    Select.all.from[:table1].where do
    column1 = 1
    end.or do
    column1 = 'Y'
    end

    Like I said though, I haven't needed this, so I haven't thought much about it.

    As far as using #=, I was/am trying to follow SQL syntax as much as possible to keep the barrier to entry as low as possible.

    ReplyDelete
  3. Jacob Fugal10:16 PM

    I'm curious also how you manage "not" and "not equal". Ruby doesn't allow you to redefine the "!" operator (and "a != b" is implicitly converted to "!(a == b)")...

    Jacob Fugal

    ReplyDelete
  4. If you'd chosen just black on black text, this blog wouldn't been much less readable. ;) Fortunaly Opera has user mode button to turn off CSS...

    But the sqldsl thing is nice (I think that constructing sql from raw strings is evil - somewhere you'll surely forget the sql_escape).

    ReplyDelete
  5. Jacob,
    I haven't needed not equal yet. After spending about 15 minutes thinking about it, I would probably use the <=> operator since in SQL I would likely use <>. It's not quite the ideal scenario, but it's close enough that I can sleep at night. Of course, I'd love to hear better suggestions.
    Jay

    ReplyDelete
  6. Jacob,
    I could also use:
    table1.column1.not_equal table1.column2

    ReplyDelete
  7. I noticed in the RDOC that you added an OrWhereCondition. But from the RDOCs it looks like you do not wrap the generated SQL in paranthesis ... that u emit ...
    or foo.bar = 1 and foo.baz = 2
    instead of
    or (foo.bar = 1 and foo.baz = 2)

    It would seem that this wrapping would be necessary to prevent logical ambiguity when combining and's and or's.

    ReplyDelete
  8. Bill,
    Good catch. That's why I dislike adding features instead of extracting them. I'll update the docs and change the code.
    Thanks, Jay

    ReplyDelete
  9. Hi Jay!
    Good work! Probably I'll use SQLDSL in one of my projects (MetaRails) but I've a question: Did you know how to handle JOIN sentences?

    Thanks!

    ReplyDelete
  10. hello spejman,
    I haven't added join support, but I have been thinking about it.

    I expect it will work like this:

    Select.all.from(:table1).inner_join(:table2).on
    table1.column1 = table2.table1_id
    ...
    end

    a) does that appear to be a suitable solution
    b) what types of joins are you looking for? (e.g. inner, left outer?)

    ReplyDelete
  11. what I really need is a kind of inner joins nesting method, because one of the compotents of MetaRails is a kind of query generator.

    We can have sentences like:
    SELECT * FROM t1 AS a INNER JOIN t2 AS b ON a.id = b.id
    INNER JOIN t3 c ON b.id2 = c.id
    WHERE c.attr1 = "foo" and b.attr1 = "foo2"

    Does your proposed method support this kind of sentences?

    Thanks for your attention. If you are interested in this query generator, please contact with me and I'll give you instructions of how try a demo.

    ReplyDelete
  12. (after I make the addition) you should be able to do this with the following syntax.

    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.where do
    c.attr1 = "foo"
    b.attr1 = "foo2"
    end

    ReplyDelete
  13. It will be great to have these additions. I think that will be enough.

    Thanks!

    ReplyDelete

Note: Only a member of this blog may post a comment.