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 to that which does not get used very frequently, but can be indispensable in some situations.
Normally it is your Ruby (or Python, or Go, or whatever) program which calls SQLite to make it “do stuff”. Most calls will be mapped to a native call like sqlite3_exec() which will do “SQLite things” and return you a result, converted into data structures accessible to your runtime. But there is another possible direction here - SQLite can actually call your code instead.
There is no support for stored procedures in SQLite (which is imaginable, since the database already lives inside of a very large stored procedure - your program, basically). But: in addition to stored procedures, databases sometimes support something called UDFs, which stands for User-Defined Functions. Most SQL databases have some functions built in:
calls a built-in SQL function called RANDOM, which will accept no arguments and return you a random integer. A call like this:
