I was playing around with making a SQLite extension in Nim the other day. Here's a simple example of how to do this. This example defines a hello function in SQL that takes one parameter and returns "Hello <value>". With a bit more effort you can do things like create virtual tables, etc.
## Nim version of Hello World SQLite plugin
##
## compile as DLL/.so
## nim c --app:lib helloworld.nim
##
## Test with something like the following:
##
## sqlite3
## sqlite> .load libhelloworld
## sqlite> CREATE TABLE testing(id INTEGER PRIMARY KEY, name STRING);
## sqlite> INSERT INTO testing VALUES (1, 'Alice'), (2, 'Bob');
## sqlite> SELECT id, hello(name) FROM testing;
## 1|Hello Alice
## 2|Hello Bob
## sqlite>
import sqlite3
{.emit:"""
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1
"""
.}
# This is the logic for our 'hello()' function that will be available in SQLite
proc helloFunc(context : Pcontext, argcount: int32, argv: PValueArg) {.cdecl.} =
assert argcount == 1
var
output = "Hello " & $value_text(argv[0])
length = output.len + 1
result_text(context, output, length.int32, SQLITE_TRANSIENT)
proc sqlite3_helloworld_init(sqlite3 : PSqlite3, pzErrMsg: ptr string, pApi: ptr int32) : cint {.exportc, dynlib.} =
result = SQLITE_OK
{.emit:"""
SQLITE_EXTENSION_INIT2(pApi);
"""
.}
var
# Can be pointer to any extra data you want available in your function
# but it has to be scoped to survive the length of your
# database connection and not just this proc
userData = cast[pointer](0)
let
funcName = "hello" # name that will be available in SQL.
numOfArgs = 1.int32
encoding = SQLITE_UTF8.int32
# These must be null pointers for just a basic SQL function
stepFunc = cast[Create_function_step_func](0)
finalFunc = cast[Create_function_final_func](0)
discard create_function(sqlite3, funcName, numOfArgs, encoding,
userData, helloFunc, stepFunc, finalFunc)
Line 55 is the call to register the function with SQLite. Off the top of my head, I'm not sure what would be crashing there. There is no check about whether the PSqlite3 object is valid. That is supplied by SQLite, but I suppose we should be checking its validity.
Independent of this, I did just post a more cleaned up version of this on Github as nim-sqliteext, https://github.com/chrisheller/nim-sqliteext. That uses some macro code to inspect a Nim proc and setup the glue code so that your function can be more Nim focused. It is very basic at this point though (only SQLite functions, no virtual tables, etc, only string/number for type mapping).
I'd be curious if you get the same error with that code.
yeah:
sqlite> .load libhello_world.dylib
Traceback (most recent call last)
functions.nim(141) sqlite3_helloworld_init
SIGSEGV: Illegal storage access. (Attempt to read from nil?)
I've been testing this with a similar setup to yours, but maybe we have different options for the SQLite library we're testing against. The default SQLite install on my Mac didn't have loadable extension support compiled in, so I tested with a separate SQLite amalgamation. Its the same version of SQLite that you were testing with.
I'm presuming that you took the default options to 'configure' when you built your SQLite, but if not let me know.
For what it's worth, changing the Makefile nim invocation to
nim --app:lib --dynlibOverride:libsqlite3 --passC:'-I$(HEADERS)' --passL:'-L$(LIB) -lsqlite3' c blah.nim
now does seem to look at the correct headers, but generates a series of clang errors, including
error: variable has incomplete type 'void'
N_CDECL(void, sqlite3_result_text)(tyObject_Context__lqWKcpVZHBAvFwpem6LnQA* para1, NCSTRING para2, NI32 para3, tyProc__32w2ZypAyMWuajvi2Jt79cg para4);
/usr/local/opt/sqlite/include/sqlite3ext.h:465:40: note: expanded from macro 'sqlite3_result_text'
#define sqlite3_result_text sqlite3_api->result_text
^
and
error: expected ';' after top level declarator
/usr/local/opt/sqlite/include/sqlite3ext.h:465:51: note: expanded from macro 'sqlite3_result_text'
#define sqlite3_result_text sqlite3_api->result_text
^
from the same source line, then
error: redefinition of 'sqlite3_api' with a different type: 'NI32' (aka 'int') vs 'const sqlite3_api_routines *' (aka 'const struct sqlite3_api_routines *')
N_CDECL(NI32, sqlite3_create_function)(tyObject_Sqlite3__xRZkbCqVWSKf8kX4o9cbKXA* para1, NCSTRING zFunctionName, NI32 nArg, NI32 eTextRep, void* para5, tyProc__6Yks26GSlDD9ceXKMfCff9ag xFunc, tyProc__6Yks26GSlDD9ceXKMfCff9ag xStep, tyProc__WvL29aegxbmsZ0AcAEAA4CA xFinal);
^
/usr/local/opt/sqlite/include/sqlite3ext.h:418:40: note: expanded from macro 'sqlite3_create_function'
#define sqlite3_create_function sqlite3_api->create_function
^
and "too many errors emitted".
I'm encouraged to be working against the correct source, but need to learn a bit more to understand these errors...
This looks insanely cool, I just stumbled over the thread.
I never even had the idea to do this, but if you can make it work well-ish, I'll likely also try my hand. I'd love nothing more to be able to build something for this tiny database that I've fallen in love with given how much it can do. Wish I could favourite a thread