Getting funky with SQLite? Correct way to test if database table exists?

[Jibun-no-Kage]

Trying to do three(3) things:

1. Test if table exists
2. Create table if not
3. Insert data once 1 and 2 above met

However, I although I think my table exist query is solid, not sure I am testing the right way for its existence. My flow keeps trying to create a table that already exists.

[{"id":"f5b5863a097442c5","type":"sqlite","z":"96084617c91478f3","mydb":"8abcf8d84952c138","sqlquery":"msg.topic","sql":"","name":"Database","x":1000,"y":1200,"wires":[["612c5a5b8f70a475","492d9db3bdbc0d20"]],"info":"CREATE TABLE 'Solar Array Monitor'\r\n    ID INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    Time DATETIME DEFAULT CURRENT_TIMESTAMP,\r\n    Seconds REAL,\r\n    Result BOOLEAN,\r\n    Port INTEGER,\r\n    Site TEXT,\r\n    Device TEXT\r\n);"},{"id":"a907ef6fe9f19033","type":"function","z":"96084617c91478f3","name":"Table?","func":"msg.topic = \"SELECT name FROM sqlite_master WHERE type='$Type' AND name='$Name'\";\nmsg.payload = { '$Type': 'table', '$Name': 'Solar Array Monitor Data' }\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":870,"y":1200,"wires":[["f5b5863a097442c5"]]},{"id":"6fe49ad64c93ba5f","type":"function","z":"96084617c91478f3","name":"Insert","func":"msg.topic = \"INSERT 'Solar Array Monitor' ('Device', 'Port', 'Result', 'Seconds) VALUES ($Device, $Port, $Result, $Seconds)\";\nmsg.payload = { '$Device': null, '$Port': null, '$Result': null, '$Seconds': null};\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1410,"y":1220,"wires":[["b3f5c843278da167"]]},{"id":"612c5a5b8f70a475","type":"switch","z":"96084617c91478f3","name":"Table?","property":"payload[0]","propertyType":"msg","rules":[{"t":"null"},{"t":"nnull"}],"checkall":"false","repair":false,"outputs":2,"x":1150,"y":1180,"wires":[["70b5e387ca4bc32e"],["6fe49ad64c93ba5f"]]},{"id":"b3f5c843278da167","type":"debug","z":"96084617c91478f3","name":"Message","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1540,"y":1220,"wires":[]},{"id":"70b5e387ca4bc32e","type":"function","z":"96084617c91478f3","name":"Create","func":"msg.topic = \"CREATE TABLE 'Solar Array Monitor' (Id INTEGER PRIMARY KEY AUTOINCREMENT, Device TEXT, Port NUMERIC, Result NUMERIC, Seconds REAL)\";\nmsg.payload = null;\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1290,"y":1140,"wires":[["50913e07c34f73e3"]]},{"id":"50913e07c34f73e3","type":"sqlite","z":"96084617c91478f3","mydb":"8abcf8d84952c138","sqlquery":"msg.topic","sql":"","name":"Database","x":1420,"y":1140,"wires":[["1be79d270d4f540f","6fe49ad64c93ba5f"]],"info":"CREATE TABLE 'Solar Array Monitor'\r\n    ID INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    Time DATETIME DEFAULT CURRENT_TIMESTAMP,\r\n    Seconds REAL,\r\n    Result BOOLEAN,\r\n    Port INTEGER,\r\n    Site TEXT,\r\n    Device TEXT\r\n);"},{"id":"1be79d270d4f540f","type":"debug","z":"96084617c91478f3","name":"Message","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1580,"y":1120,"wires":[]},{"id":"5a1f6a321502a554","type":"inject","z":"96084617c91478f3","name":"Test","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":750,"y":1200,"wires":[["a907ef6fe9f19033"]]},{"id":"492d9db3bdbc0d20","type":"debug","z":"96084617c91478f3","name":"Message","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1160,"y":1220,"wires":[]},{"id":"8abcf8d84952c138","type":"sqlitedb","db":"/tmp/Solar Array Monitor.sqlite","mode":"RWC"},{"id":"aba5b8b9bb6cc5cf","type":"global-config","env":[],"modules":{"node-red-node-sqlite":"1.1.1"}}]

[zenofmud]

You could send this query:

SELECT COUNT(*) FROM demo

using your table name in place of ‘demo’

Then add a catch all node and attach a switch node to check if the table doesn’t exist

[{"id":"34fa18bc173f3f29","type":"sqlite","z":"a0ea77c7428470e5","mydb":"75000055dcd574e5","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":490,"y":320,"wires":[["ef4e05bc26131bb6"]]},{"id":"75946763301df9d5","type":"debug","z":"a0ea77c7428470e5","name":"table does not exist","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":690,"y":420,"wires":},{"id":"ef4e05bc26131bb6","type":"debug","z":"a0ea77c7428470e5","name":"table exists","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":320,"wires":},{"id":"1c044597d2e4ddb5","type":"inject","z":"a0ea77c7428470e5","name":"SELECT COUNT() FROM demo","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT COUNT() FROM demo","payload":"","payloadType":"date","x":230,"y":320,"wires":[["34fa18bc173f3f29"]]},{"id":"78b17c05f7b7e189","type":"catch","z":"a0ea77c7428470e5","name":"","scope":null,"uncaught":false,"x":240,"y":420,"wires":[["03e522c764952591"]]},{"id":"03e522c764952591","type":"switch","z":"a0ea77c7428470e5","name":"","property":"error.message","propertyType":"msg","rules":[{"t":"cont","v":"no such table","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":410,"y":420,"wires":[["75946763301df9d5"]]},{"id":"75000055dcd574e5","type":"sqlitedb","db":"/Volumes/Gremil-two/Paul_Stuff/databases/mqtttracking","mode":"RWC"}]

MAKE SURE to set the database directory in the sqlite node.

[E1cid]

You could just create table if it does not exist with statement similar to

 CREATE TABLE IF NOT EXISTS tablename (
columnname TEXT NOT NULL UNIQUE);

It would return an empty array so you could ignre the returned payload and then write your data.

[ThingsTinkerer]

This is a very useful idempotent trick! I've used it a lot with success.

[Nodi.Rubrum]

@E1cid,

What is the correct way to test for an empty array? Say msg.payload[0] returned as 'nothing' not null and if I check for 'is empty' in my switch node, it seems to ignore that test? So I have something not quite right in the switch node.

[Colin]

Do you mean it is not an empty array but is an array of length 1 with string 'nothing' in the element?

[Colin]

The switch node correctly finds an empty array as empty for me.

[{"id":"367db265c1d2c593","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[]","payloadType":"json","x":300,"y":3780,"wires":[["917a5c819cb34ef8"]]},{"id":"917a5c819cb34ef8","type":"switch","z":"bdd7be38.d3b55","name":"","property":"payload","propertyType":"msg","rules":[{"t":"empty"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":440,"y":3780,"wires":[["19de9da9a95b489a"],["0decd481295cd456"]]},{"id":"19de9da9a95b489a","type":"debug","z":"bdd7be38.d3b55","name":"EMPTY","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":570,"y":3720,"wires":[]},{"id":"0decd481295cd456","type":"debug","z":"bdd7be38.d3b55","name":"debug 27","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":580,"y":3840,"wires":[]}]

[Nodi.Rubrum]

Yes, something is a bit odd with my test flow, 1) after the table is created, and I try to insert the database is marked read only. 2) no matter what I do, create table, insert, etc. I always get a payload[0] null returned. For a table created this is seemly OK per the documentation, but any insert should return 1 rows changed per the documentation.

I have done may flows with MySQL even MariaDB, this is my first time attempting to use SQLite. And frankly, it is turning out to be surprise, to seemly get it to work correctly. The same queries via direct sqlite3 session work fine, but using NR, the results are confusing thus far.

[Jibun-no-Kage]

Well first, I am experiencing some thing near the following...

Sqlite3 data insert problem

The issue has been corrected with version 1.0.2: node-red-stop cd .node-red npm install node-red-node-sqlite@1.0.2 node-red-start Note that when binding a payload the UPDATE also needs to be sent with "$", example: UPDATE users_connection_data SET connected = $connected, date = $date WHERE ip = $ip;

Not identical but similar enough to find it interesting.

In that, I cannot get any variant of 'via msg.topic' configuration in the sqlite node to work with any variant of msg.params. Even though there are many examples via Google suggesting this.

So, I changed to a 'prepared' query node configuration, which ignores msg.topic and requires a static query syntax, this works with msg.params use. Or has thus far, as 10 to 20 tests.

As for the database instance going read only, that has not a materialized once I got the insert sequence working consistently. And yes, the user that NR runs under, has explicit permission to the file system and directory where the database file is located.

I still cannot get msg.payload to show any evidence of success or failure of any query. This seems really odd, compared to how MySQL or MariaDB query results are communicated back to NR flows.

[Jibun-no-Kage]

Oh... I figured out the read only issue... if I attempt a DROP TABLE query, it fails and the database goes read only. No error reported to NR. This is not a NR issue, it happens via direct execution in the sqlite3 shell as well.