Supercharge SQLite with Ruby Functions

An interesting twist in my recent usage of SQLite was the fact that I noticed my research scripts and the database intertwine more. SQLite is unique in that it really lives in-process, unlike standalone database servers. There is a feature that does not get used very frequently but can be indispensable in some situations.

Understanding SQLite’s Unique Strengths

Normally, it is your Ruby (or Python, or Go, or whatever) program which calls SQLite to make it “do stuff”. However, SQLite can call your code, exposing unique capabilities.

User-Defined Functions in SQLite

SQLite doesn’t support stored procedures, but it does support user-defined functions (UDFs). UDFs let you define your own SQL functions, expanding SQLite’s functionality.

For instance, SELECT RANDOM() calls a built-in SQL function called RANDOM, which returns a random integer. Similarly, SELECT LOWER(‘HELLO’) converts text to lowercase using the LOWER function. These are scalar functions, processing data from a single result row.

Creating Your Own Scalar UDF

At Cheddar, a small library called Tou generates time-ordered UUIDs. We can make these available to our database:

We create an enumerator for a deterministic sequence of Tou IDs. This allows inserting users with IDs into our table. SQLite can provide these values through functions like TOU(), enabling automatic ID generation in queries.

Enhancing Queries with Ruby Functions

We expose our ID generator to SQLite using:

db.create_function("TOU", _arity = 0) do |func|
  func.result = id_gen.next
end

Text Matching with Ruby Regex

For regex matching in SQLite, create a Ruby function:

db.create_function("TEXT_MATCHES_RE", _arity = 2) do |func, text, regexp_str|
  re = Regexp.new(regexp_str)
  func.result = text&.match?(re) ? 1 : 0
end

Aggregate Functions and Statistical Analysis

While SQLite offers MIN and MAX, more advanced functions like standard deviation can be added:

class StddevSampHandler
  # Code for standard deviation
end

Example Usage

db.query("CREATE TABLE metric_samples (name VARCHAR NOT NULL, value FLOAT)")
# Inserting and querying data

Considerations for UDFs

SQL functions can be used in queries and SQL statements run by the database. However, they cannot be stored with the database or executed without the host application defining the function.

Function Retention with SQLITE_DIRECTONLY

To prevent functions from misuse, mark them as SQLITE_DIRECTONLY:

db.create_function("TOU2", _arity = 0, text_rep_and_flags) do |func|
  func.result = id_gen.next
end

Conclusion

Wiring Ruby methods into SQLite offers powerful capabilities for data exploration and processing. When used appropriately, these functions can provide valuable enhancements to your database operations.