Sequel praise and Sqlite type translation problems 2008-03-20

I ran into a very annoying problem with Sqlite yesterday, triggered by Sequel. But first some praise for how simple Sequel makes things most of the time:

One of the nice things about Sequel is that it allows me to write complex queries in pure ruby, without ever (so far, anyway) dipping into actual Sequel.

Last night I was throwing together a tag histogram for this blog, and suddenly everything blew up.

The offending code was this, which nicely illustrates Sequel:

Tag.select(:name,:count[:name].as(:count).
  group_by(:name).order_by(:count.desc)

Which I during my debugging discovered could be reduced to:

Tag.group_and_count(:name).order_by(:count.desc)

It does order by count, but ascending by default. What's even nicer is Sequels "dataset" concept, which let me write this:

class Tag
  def dataset.histogram
     group_and_count(:name).order_by(:count.desc)
  end
end

By creating the method on the dataset I can chain it with other Sequel methods:

   Tag.histogram.limit(10)

Very nice and neat. However, the above all blew up for me. I figured I'd try running everything off Sqlite since I'm not exactly expecting this blog to draw a ton of traffic given the extreme geekery likely to be found here on a regular basis.

Part of the problem is that Sqlite is dynamically typed. It's one of the things I love about Sqlite usually, and it makes it a very nice match for Ruby most of the time. The problem is that when reading things out of Sqlite it all comes back as strings. So the Ruby bindings add type translation to translate the strings back into native Ruby. The combination is potentially very powerful, as nothing stops you from automatically serializing and unserializing full Ruby objects (and combined with Sqlite callbacks you could actually do a lot of wonderfully evil things with that, including calling methods on the objects stored in the database).

The problem is that at least as used by Sequel this blows up when one of the columns doesn't have a type, for example because it's not a column but the return value of a function.

For now I've solved this by changing this part of sqlite3/resultset.rb from this:

if @db.type_translation
  row = @stmt.types.zip( row ).map do |type, value|
    @db.translator.translate( type , value )
  end
end

Into:

if @db.type_translation
  row = @stmt.types.zip( row ).map do |type, value|
    @db.translator.translate( type ? type : "NUMERIC" , value )
  end
end

It's not a good solution, so I won't submit it upstream, but it works for now. The problem is that it will break for any function returning a string instead of a number.