---
title: The Ideal of Agnosticism
teaser: Pros and cons of committing to one database server.
tags: web,rails
author: Floyd Wright
published_on: 2007-10-09
---
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](http://errtheblog.com/posts/63-full-of-ambition), which has lately
taken the effort to a new level), and of course, thoughtbot has our [own
contribution](https://github.com/thoughtbot/squirrel).
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:
```ruby
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] (unofficially). Luckily it's relatively simple in
code, once you learn to ignore the erroneous example:
```ruby
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.
[documented on gemtacular]: http://gemtacular.com/gemdocs/sqlite3-ruby-0.5.0/doc/