SQLite question

#1

I am using node-red-node-sqlite to connect to a sqlite3 db. I need to load a sqlite extension prior to submitting a select.

When I do this at the command line, it seems to work ok:
select load_extension('mod_spatialite');
SELECT id, (6371000 * acos ( cos ( radians(42.48) ) * cos ( radians ( latitude ) ) * cos( radians( longitude ) - radians(-70.8) ) + sin ( radians(42.48) ) * sin( radians( latitude ) ) ) ) AS distance, ssid from log order by distance;

I am getting this from nodered:

Error: SQLITE_ERROR: not authorized

The error seems to be in response to the ‘select load_extension() call.

I have tried to use msg.topic, fixed statement and prepared statement.

So Im wondering if:
-There might be a way to have the select load_extension automatically execute upon connect? From what I understand sqlite3 can accept a 2nd argument (after the db name) that is a SQL statement.

-Perhaps Im not using the node correctly?

#2

I haven't used load_extension but I wonder whether it is a path issue. Try specifying the full path and filename of the extension file in the load_extension call. Try it in the command line first. If you are using Windows then I don't know what you need to do with the backslash characters in the path. You may need someone else to comment on that.

#3

We currently don't expose load_extension so it would need to be added.
How we do that needs a bit of thought, and input from you folk. Is it common to add more than one extension ? or is it only one at a time ? Do they tend to be statically loaded ? (ie once at start time) - or need to be added during run time?

#4

As a work-around, can it be loaded from the command line using an exec node?

#5

You will have to use :

From the command-line shell, extensions can be loaded using the ".load" dot-command. For example:

.load ./YourCode

#6

I tried the full path on the command line and it seemed to work, so I tried it in node-red-node-sqlite as a fixed statement. This only resulted in the same 'not authorized' error.
This is on a raspberry pi

#7

The extension I am using is to enable spatial data functions (spatialite). Their docs indicate that loading the extension dynamically seems to be preferred. Here is a link to that doc:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

Looking at other comments, I did try to use the .load 'full-path-to-mod_spatialite' and it seemed to work. So I am considering using a .sqliterc file (which I believe permits the use of any of the dot commands) and will have to test.

I am hoping that node-red-node-sqlite will cause .sqliterc to be used and that a .load call therein will be successful.

Many Thanks for the ideas/discusssion

#8

Yes I have tried this workaround and it works but I would of course prefer to use the node

#9

So, Sorry to report that the node-red-node-sqlite does not appear to use the .sqliterc file.
I added a .sqliterc file with one line that uses the dot command .load [extension].

When I start sqlite3 command line, the .sqliterc file is read and I am able to execute a select statement that includes functions that are only available if the extension is loaded..

When I submit the same select statement in the node, an error is returned that indicates the extension is not loaded.

So if anyone has any ideas about how I might load an extension in a flow that uses node-red-sqlite-node I am eager to try. Otherwise I guess I will need to use the workaround.

Thanks

#10

Windows generally supports forward slashes. If you use Windows style backslashes, you have to escape them if in a double-quoted string.

#11

At the risk of repeating myslef...

#12

My input is that I am only currently loading a single extension at this time, but I could imagine loading multiple extensions.

I would prefer the dynamic loading option because I know it works and the docs from spatialite seem to prefer that method as well.

Thanks

#13

Ok - so allowing it via a msg property probably makes sense rather than adding to the config.
If you could try something for me... find the file sqlite.js in node_modules/node-red-node-sqlite and edit it... around line 50 - add the if statement below

        node.on("input", function(msg) {
            if (msg.hasOwnProperty("extension")) {
                node.mydbConfig.db.loadExtension(msg.extension, function(err) {
                    if (err) { node.error(err,msg); }
                });
            }

Then you can send in a message with msg.extension set to the full filename of the extension.
Let me know if it helps ...

#14

OK so I added the if statement at around line 50, restarted and seeing this:

I was only supposed to add the 'if' right? I omitted the line starting with node.on.

#15

Tried to reinstall node-red-node-sqlite and got this error log:

0 info it worked if it ends with ok
1 verbose cli [ '/usr/bin/node',
1 verbose cli '/usr/bin/npm',
1 verbose cli 'install',
1 verbose cli 'node-red-node-sqlite' ]
2 info using npm@5.6.0
3 info using node@v8.11.4
4 verbose npm-session 18d7a0b8eaa6b47e
5 silly install loadCurrentTree
6 silly install readLocalPackageData
7 http fetch GET 200 https://registry.npmjs.org/node-red-node-sqlite 89ms (from cache)
8 silly pacote tag manifest for node-red-node-sqlite@latest fetched in 192ms
9 silly install loadIdealTree
10 silly install cloneCurrentTreeToIdealTree
11 silly install loadShrinkwrap
12 silly install loadAllDepsIntoIdealTree
13 silly saveTree node-red-node-sqlite@0.3.2
14 verbose stack Error: Refusing to install package with name "node-red-node-sqlite" under a package
14 verbose stack also called "node-red-node-sqlite". Did you name your project the same
14 verbose stack as the dependency you're installing?
14 verbose stack
14 verbose stack For more information, see:
14 verbose stack https://docs.npmjs.com/cli/install#limitations-of-npms-install-algorithm
14 verbose stack at checkSelf (/usr/lib/node_modules/npm/lib/install/validate-args.js:64:14)
14 verbose stack at Array. (/usr/lib/node_modules/npm/node_modules/slide/lib/bind-actor.js:15:8)
14 verbose stack at LOOP (/usr/lib/node_modules/npm/node_modules/slide/lib/chain.js:15:14)
14 verbose stack at /usr/lib/node_modules/npm/node_modules/slide/lib/chain.js:18:7
14 verbose stack at hasMinimumFields (/usr/lib/node_modules/npm/lib/install/validate-args.js:30:12)
14 verbose stack at Array. (/usr/lib/node_modules/npm/node_modules/slide/lib/bind-actor.js:15:8)
14 verbose stack at LOOP (/usr/lib/node_modules/npm/node_modules/slide/lib/chain.js:15:14)
14 verbose stack at chain (/usr/lib/node_modules/npm/node_modules/slide/lib/chain.js:20:5)
14 verbose stack at /usr/lib/node_modules/npm/lib/install/validate-args.js:16:5
14 verbose stack at /usr/lib/node_modules/npm/node_modules/slide/lib/async-map.js:52:35
14 verbose stack at Array.forEach ()
14 verbose stack at /usr/lib/node_modules/npm/node_modules/slide/lib/async-map.js:52:11
14 verbose stack at Array.forEach ()
14 verbose stack at asyncMap (/usr/lib/node_modules/npm/node_modules/slide/lib/async-map.js:51:8)
14 verbose stack at module.exports (/usr/lib/node_modules/npm/lib/install/validate-args.js:15:3)
14 verbose stack at Array. (/usr/lib/node_modules/npm/node_modules/slide/lib/bind-actor.js:15:8)
15 verbose cwd /home/pi/.signalk/red/node_modules/node-red-node-sqlite
16 verbose Linux 4.14.52-v7+
17 verbose argv "/usr/bin/node" "/usr/bin/npm" "install" "node-red-node-sqlite"
18 verbose node v8.11.4
19 verbose npm v5.6.0
20 error code ENOSELF
21 error Refusing to install package with name "node-red-node-sqlite" under a package
21 error also called "node-red-node-sqlite". Did you name your project the same
21 error as the dependency you're installing?
...
22 verbose exit [ 1, true ]

#16

This error resolved after a reboot. Go Figure. I tried a service restart first which led to experiencing the above error. After a reboot my sqlite node is back, so on to testing.

#17

OK, After a few errors on my part I got the flow going again.
Using a change node, I set msg.extension to the full path of the extension (the same path I use in the .sqliterc file with the .load command).

Then the flow goes to the sqlite node where I am using a fixed sql statement.

New Error:

#18

OK - is there anything in the console log also ?
And are you also sending a query in the same msg ?(hopefully not)...

#19

Same error seen in console log but nothing more.

The query is directly in the sqlite node (as a fixed statement). The change node is setting the msg.extension as shown here.

#20

OK - I have pushed node-red-node-sqlite@0.3.3 - which has a better implementation of the fix that sorts out that timing problem. I don't have a valid extension to try - but at least it now tries to load a file (and for me then fails)... hopefully will work for you with a correct file.
I did notice it seems to want the full path, and filename - but didn't need the final extension ( On Mac .dylib) but the error message was quite clear.