Getting 'Could not load toolkit script: luasql.sqlite3' error when trying to setup DB connection

[manishb17276158]

I'm trying to connect to a local sqlite database to locally store which collections my plugin is touching. All works well, when I run my code through the command line, but I received the following error when trying to run the plugin via Lightroom:

Could not load toolkit script: luasql.sqlite3.

I've seeded the database with data outside of the plugin. I'm currently working on just getting my plugin to connect to the database and output the simple select query. Next step will be to write to it.

Obviously luasql.sqlite3 can't be found via Lightroom, but I'm not sure how to best address the issue. Here is my code that works via command line. Any assistance would be appreciated.

luasql = require "luasql.sqlite3"
env = luasql.sqlite3()
db = env:connect('testing.db')

cursor = assert(db:execute('Select * from CollectionTable'))
row = {}
while cursor:fetch(row) do
    print(table.concat(row, '|'))
end
cursor:close()
db:close()
env:close()

[johnrellis]

Perusing the source at https://github.com/LuaDist/luasql-sqlite3,  it appears that "luasql.sqlite3" loads (or is) a dynamic library written in C. The Lightroom SDK doesn't allow such dynamic libraries to be loaded.

For each query or update the plugin does, it could run the command-line program "sqlite3" using LrTasks.execute(), reading input commands from a temp file and writing the results to another temp file. That may be efficient enough for your purposes ("sqlite3" is pretty fast).  

If you need lower-latency execution of queries and updates, you could write a "server" program that your plugin connects to using LrSocket (you need two sockets, one for reading and one for writing to the server).  The server program starts "sqlite3" with pipes connected to its standard input and output, and forwards data between the sockets and the pipes.

On Mac, you may be able to use nc to do this (nc ... | sqlite3 | nc ...), but I've never tried it. I don't know if there's an equivalent on Windows.