Trying to do three(3) things:
- Test if table exists
- Create table if not
- 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"}}]
