SQLite is great for local storage, jugglery with every single node with db operation creates mess in tab space.
Would you care to expand on this since I have no idea what you mean.
1 Like
I want to pass to msg.topic multiple SQL statements and execute at once:
CREATE TABLE City ( Id INTEGER PRIMARY KEY, Name STRING ); INSERT INTO City VALUES(null, 'Warsaw'); INSERT INTO City VALUES(null, 'Poznan');
Instead execute them separately.
Sample below:
[{"id":"b21d1f1d.62a0e","type":"inject","z":"18570be0.597ed4","name":"","topic":"CREATE TABLE IF NOT EXISTS City ( Id INTEGER PRIMARY KEY, Name STRING ); INSERT INTO City VALUES(null, Warsaw); INSERT INTO City VALUES(null, Poznan);","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":460,"wires":[["98dcf78e.647bf8"]]},{"id":"98dcf78e.647bf8","type":"sqlite","z":"18570be0.597ed4","mydb":"92ac279b.86a7e8","sqlquery":"msg.topic","sql":"","name":"","x":250,"y":460,"wires":[["b60c54de.ba4118"]]},{"id":"7a8f4239.008f7c","type":"inject","z":"18570be0.597ed4","name":"","topic":"SELECT * FROM City","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":500,"wires":[["43d78354.b535ec"]]},{"id":"43d78354.b535ec","type":"sqlite","z":"18570be0.597ed4","mydb":"92ac279b.86a7e8","sqlquery":"msg.topic","sql":"","name":"","x":250,"y":500,"wires":[["13d9bc86.e19903"]]},{"id":"b60c54de.ba4118","type":"debug","z":"18570be0.597ed4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":390,"y":460,"wires":[]},{"id":"13d9bc86.e19903","type":"debug","z":"18570be0.597ed4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":390,"y":500,"wires":[]},{"id":"92ac279b.86a7e8","type":"sqlitedb","z":"","db":"test"}]
How would you expect the output to look like? Would the output from all the statements be bunched together in an array? What if the middle statement caused an error, how would you expect it to be handled? I would never code a program that fed one statement after another, I would always do an error check.
And what is wrong with using multiple nodes?
Well, you're CREATE TABLE would only normally happen once wouldn't it so that shouldn't be much of an issue.
In regard to inserts. SQLite supports batch inserts using begin and end transaction functions. Squeezing Performance from SQLite: Insertions | by Jason Feinstein | Medium
When you don't explicitly start a transaction, SQLlite adds one for every statement because it is on the end transaction that it actually writes to disk rather than just to memory. So wrapping your inserts in a transaction will give you some benefit. However, if you use a prepared statement to do your inserts, you should get nearly the same performance anyway.
This is not real world example, it's just to point the problem, there are use cases to work that way.
@TotallyInformation could you show me how to do a transaction with above example?
OK, so here is a raw SQL example taken from SQLite Transaction Explained By Practical Examples
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_no = 100;
UPDATE accounts
SET balance = balance + 1000
WHERE account_no = 200;
INSERT INTO account_changes(account_no,flag,amount,changed_at)
values(100,'-',1000,datetime('now'));
INSERT INTO account_changes(account_no,flag,amount,changed_at)
values(200,'+',1000,datetime('now'));
COMMIT;
It's been a while since I did any SQL but I think that if you separate out your create, then form a string such as:
BEGIN TRANSACTION; INSERT INTO City VALUES(null, 'Warsaw'); INSERT INTO City VALUES(null, 'Poznan'); COMMIT;
Then the data will only be committed to disk on the COMMIT statement. I think that you can probably do 999/# fields INSERTs in a single transaction.
The SQL is just a string so you can use JavaScript to form it quite easily.
See also Transaction
you could also form up several queries into an array of strings in msg.payload - feed that through a split node to create one message per item and then through a change node to move the payload to topic before feeding to the sqlite node.
@TotallyInformation I've already tried that, It doesn't work It process only first statement - when I run batch for the second time I get an error "Error: SQLITE_ERROR: cannot start a transaction within a transaction".
[{"id":"b105cd3e.5f79e","type":"inject","z":"8dfa453c.668ce8","name":"","topic":"SELECT * FROM City","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":200,"wires":[["55b00e57.50a49"]]},{"id":"55b00e57.50a49","type":"sqlite","z":"8dfa453c.668ce8","mydb":"92ac279b.86a7e8","sqlquery":"msg.topic","sql":"","name":"","x":330,"y":200,"wires":[["bafb47cc.da6e68"]]},{"id":"bafb47cc.da6e68","type":"debug","z":"8dfa453c.668ce8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":470,"y":200,"wires":[]},{"id":"8f217c0d.40fe9","type":"sqlite","z":"8dfa453c.668ce8","mydb":"92ac279b.86a7e8","sqlquery":"msg.topic","sql":"","name":"","x":330,"y":160,"wires":[["98534300.962ea"]]},{"id":"a42881d5.6351f","type":"inject","z":"8dfa453c.668ce8","name":"","topic":"BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS City ( Id INTEGER PRIMARY KEY, Name STRING ); INSERT INTO City VALUES(null, 'Warsaw'); INSERT INTO City VALUES(null, 'Poznan'); COMMIT;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":160,"wires":[["8f217c0d.40fe9"]]},{"id":"98534300.962ea","type":"debug","z":"8dfa453c.668ce8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":470,"y":160,"wires":[]},{"id":"92ac279b.86a7e8","type":"sqlitedb","z":"","db":"test"}]
@dceejay ... and after - join node, this is my current solution but instead one node (sqlite) there are four (split, change, sqlite, join).
Repeating this multiple times in flow creates unnecessary mess.
OK, I've did some research and resolved the issue, I'll send PR.
Here it is: https://github.com/node-red/node-red-nodes/pull/466
1 Like
Don't know if you get notification on post edit so pinging back, above is a link to PR.
Yes - I see all the channels :-)... comments incoming - on github
