How do I put a Ruby function into a SQLite3 query?

Question

I have a function which I need to put into a SQLite3 query.

I have the following method:

def levenshtein(a, b)
  case
    when a.empty? then b.length
    when b.empty? then a.length
    else [(a[0] == b[0] ? 0 : 1) + levenshtein(a[1..-1], b[1..-1]),
      1 + levenshtein(a[1..-1], b),
      1 + levenshtein(a, b[1..-1])].min
  end
end

and I want to do a query that looks something like this:

@results = The_db.where('levenshtein("name", ?) < 3', '#{userinput}')

What I want to find the values of name in The_db where the edit distance between the value of the name column and the user input is less than 3. The problem is that I don't know how to use a Ruby function in a query. Is this even possible?

Answer 1

Have a look at the create_function method. You can use it to create a custom function like this (where you have already defined your levenshtein Ruby method):

db.create_function "levenshtein", 2 do |func, a, b|
  func.result = levenshtein(a, b)
end

You can then use it in your SQL:

# first set up some data
db.execute 'create table names (name varchar(30))'
%w{Sam Ian Matt John Albert}.each do |n|
  db.execute 'insert into names values (?)', n
end

#Then use the custom function
puts db.execute('select * from names where levenshtein(name, ?) < 3', 'Jan')

In this example the output is

Sam
Ian
John

I haven’t tested this in Rails, but I think it should work, the query string is just passed through to the database. You can get the Sqlite db object using ActiveRecord::Base.connection.raw_connection. (I don’t know how to configure Rails so that all ActiveRecord connections have the function defined however – it does seem to work if you add the function in the controller, but that isn’t ideal).

Having shown how this can be done, I’m not sure if it should be done in a web app. You probably don’t want to use Sqlite in production. Perhaps it could be useful if you’re using e.g. Postgres with its own levenshtein function in production (as suggested in the Tin Man’s answer), but want to use Sqlite in development.

Answer 2

If you need to retrieve the value of a column to insert it into a Ruby function, then you have to retrieve that value first.

The DBM can't call your method in your running code; You have to have the value, pass it to your method, then use the result in a secondary query.

Or use a DBM that has a Levenshtein function built in, like PostgreSQL or define it in pure SQL.