The Ideal of Agnosticism

Less than anything, less than I could possibly say, does SQL makes me happy. In fact, it gives me Tourette’s, even-no-especially when properly formatted. Hey guys how LEFT INNER JOIN are you SELECT WHERE doing INSERT SUM. I know at least a few of you out there agree with me, because plenty of you are doing what you can to shield us sensitive code-aesthetes from the horror of this profane language (especially Ambition, which has lately taken the effort to a new level), and of course, thoughtbot has our own contribution.

But every once in a while, some odd requirement, and more inevitably, the need to optimize require confronting this beast. This is inevitable, but worse than actually seeing 10 lines of SQL dropped like a load of bricks onto my lovely Ruby method is the actual violation of ActiveRecord, one of whose most important jobs it is to not just shield us from SQL, but from database dependency. And many times, if you can’t do it with Rails’ finders, or even Squirrel and its ilk, then your implementation is going to end up depending on a particular database backing the app to even work. As I’m sure you’re all aware, this is bad for two reasons:

• The remote chance your client switches db technology in the future (a point on which you may or may not have sold him or her Rails)
• More pressingly (and annoyingly), the inability to use a different db technology for testing and developing, namely that wonder of size, speed and portability, SQLite

Well just this thing happened on a project I’m working on, twice actually, and both times I actually managed to solve the problem by delving deeper into an object you might not have worked with on day-to-day development: the connection object. The connection object is an instance of Rails’, or more specifically, ActiveRecord’s db adapter, which it has kindly tailored to your db in its automagical way that it has, making all your finders work properly. It can be found at ActiveRecord::Base.connection (unless you’re using db specific Models, but really) Sadly, you have betrayed this gift by slapping it in the face with your own clammy SQL, which it can’t help you with. Unless of course, you make amends by placing the SQL in the adapter itself, thereby increasing its power rather than insulting its heritage. You can do this in the same way you open any other class, by sticking a file in lib.

Here is an example where I reconciled MySQL and SQLite, once bitter enemies over the pointless dispute of random function syntax:

module SQLiteAdapterExt
  def rand(seed=Kernel.rand)
    "RANDOM(#{seed})"
  end
end
module MySQLAdapterExt
  def rand(seed=Kernel.rand)
    "RAND(#{seed})"
  end
end
ActiveRecord::ConnectionAdapters::MysqlAdapter.send(:include, MySQLAdapterExt)
ActiveRecord::ConnectionAdapters::SQLite3Adapter.send(:include, SQLiteAdapterExt)

Now instead of forcing one or the other, you can just call ActiveRecord::Base.connection.rand(12345), and blam, you get database agnostic random result sorting, for example (You’ll of course need to extend every database you want to support).

But what if you need something even more obscure, say regular expression matching? Well, if you’re using MySQL, it’s happily baked right in. But your SQLite backed test? Nothing doing. From the SQLite3 docs:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named regexp is added at run-time, that function will be called in order to implement the REGEXP operator.

In order to remedy this, you’ll need to make use of SQLite’s user-defined functions, which needless to say, aren’t well documented. sqlite3-ruby, the Ruby binding for SQLite3, provides an API for defining these: create_function, which is documented on gemtacular. Luckily it’s relatively simple in code, once you learn to ignore the erroneous example:

if ActiveRecord::Base.connection.adapter_name.eql?("SQLite")
  db = ActiveRecord::Base.connection.instance_variable_get(:@connection)
  db.create_function("regexp", 2) do |func, expr, value|
    begin
      if value.to_s && value.to_s.match(Regexp.new(expr.to_s))
        func.set_result 1
      else
        func.set_result 0
      end
    rescue => e
      puts "error: #{e}"
    end
  end
end

In brief, SQLite passes two parameters to REGEXP, the expression, and the value of the column, and includes it in the results if the function returns 1, and not if it returns 0. You may want to remove the puts in the rescue clause if you’re not using this in a development/test environment, but for some reason the documented set_error method seemed to break the whole thing for me. Kudos to those with suggestions for improvements!

update I had some trouble with reloading issues in the development environment and ended up having to move the UDF logic into the model. This isn’t the happiest solution since this logic is in no way necessarily tied to one specific model (although it may have, as in my case, been implemented for its sake), so if I find something better, I’ll post it.