Hello,
I have an annoying issue with sqlite.
I have a flow to check if my nVidia Shield is blocked to go on Youtube, I use PiHole to avoid my children to watch YouTube every day.
First I count how many records I have in the DB, with a count in a select statement for the client and the group, before I insert the record to the database.
The first time I run the flow, everything works, as the select return 0.
If the next day I don't unblocked, it does nothing, as I want.
But the third day, if I have unblocked Youtube, the first select statement return 1 instead of 0. As if the flow don't see the change in the database, like a cache. But, if I run in the console the query, the result is good.
For information, I have NR in a container, and I use a custom image to use adb.
Here my Dockerfile:
FROM nodered/node-red:latest-18
USER root
RUN \
echo "*** Install adb" ; \
apk add --no-cache android-tools
USER node-red
If I restart the container that works, but the problem returns later.
Here the flow I have currently:
[{"id":"b289383d44ee87c6","type":"subflow","name":"Alerte Gotify","info":"","category":"","in":[{"x":80,"y":80,"wires":[{"id":"24250bbdfd21e680"}]}],"out":[{"x":520,"y":80,"wires":[{"id":"43a5e1f5241a1436","port":0}]}],"env":[],"meta":{},"color":"#DDAA99"},{"id":"24250bbdfd21e680","type":"function","z":"b289383d44ee87c6","name":"Construct POST","func":"var texte = msg.payload;\nmsg.payload = {\n \"title\": \"Node-RED Home Assistant\",\n \"message\": texte,\n \"priority\": 5\n};\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":220,"y":80,"wires":[["43a5e1f5241a1436"]]},{"id":"43a5e1f5241a1436","type":"http request","z":"b289383d44ee87c6","name":"Gotify","method":"POST","ret":"obj","paytoqs":"ignore","url":"https://gotify.chucky2401.duckdns.org/message?token=ABgL.rrPgOuqVD6","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":410,"y":80,"wires":[[]]},{"id":"0260961d70d9aa9a","type":"subflow","name":"Restart Shield","info":"","category":"","in":[{"x":60,"y":60,"wires":[{"id":"7a523936177e03a0"}]}],"out":[{"x":700,"y":120,"wires":[{"id":"9b9175e3a9fa9b39","port":0}]}],"env":[],"meta":{},"color":"#DDAA99"},{"id":"7a523936177e03a0","type":"exec","z":"0260961d70d9aa9a","command":"adb connect 192.168.1.16:5555","addpay":"","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"ADB Connect","x":200,"y":60,"wires":[["d00a8d1c066fadfa"],[],[]]},{"id":"9b9175e3a9fa9b39","type":"exec","z":"0260961d70d9aa9a","command":"adb kill-server","addpay":"","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"ADB Kill","x":600,"y":120,"wires":[[],[],[]]},{"id":"d00a8d1c066fadfa","type":"exec","z":"0260961d70d9aa9a","command":"adb shell dumpsys power","addpay":"","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"Dumpsys Power","x":400,"y":60,"wires":[["2e6a4f8afdd63212"],[],[]]},{"id":"2e6a4f8afdd63212","type":"function","z":"0260961d70d9aa9a","name":"nVidia Status","func":"const re = new RegExp(\"mWakefulness=([A-Za-z]+)\\n\");\nmsg.match = (msg.payload).match(re)[1];\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":590,"y":60,"wires":[["8c62e7e82d229593"]]},{"id":"8c62e7e82d229593","type":"switch","z":"0260961d70d9aa9a","name":"If Awake","property":"match","propertyType":"msg","rules":[{"t":"eq","v":"Awake","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":400,"y":120,"wires":[["f3e056e0edbf7fb3"],["9b9175e3a9fa9b39"]]},{"id":"f3e056e0edbf7fb3","type":"exec","z":"0260961d70d9aa9a","command":"adb reboot","addpay":"","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"ADB Reboot","x":390,"y":180,"wires":[["9b9175e3a9fa9b39"],[],[]]},{"id":"bcef1420b275df09","type":"tab","label":"Reblock YouTube","disabled":false,"info":"","env":[]},{"id":"8447073cfdfa9df1","type":"sqlite","z":"bcef1420b275df09","mydb":"1dc4cc53aafd3e69","sqlquery":"prepared","sql":"SELECT\n COUNT(1) AS Compteur\nFROM\n client_by_group\nWHERE\n client_id = $clientId\nAND group_id = 6","name":"PiHole","x":310,"y":220,"wires":[["ee19d59cae6cfc50","afc72260c969aa28"]]},{"id":"41f1278d80ca94d6","type":"inject","z":"bcef1420b275df09","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":100,"wires":[["3c902481cdcca886"]]},{"id":"76272edb7f20ae3a","type":"debug","z":"bcef1420b275df09","name":"Debug PiHole","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":860,"y":380,"wires":[]},{"id":"3c902481cdcca886","type":"function","z":"bcef1420b275df09","name":"Query Parameters","func":"msg.payload = null;\nmsg.params = {\n $clientId:7\n};\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":350,"y":100,"wires":[["8447073cfdfa9df1"]]},{"id":"afc72260c969aa28","type":"switch","z":"bcef1420b275df09","name":"Shield blocked ?","property":"payload[0].Compteur","propertyType":"msg","rules":[{"t":"neq","v":"0","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":480,"y":220,"wires":[["76272edb7f20ae3a"],["25a7056d5d0564cf"]]},{"id":"7e958f461d6fcd56","type":"docker-container-actions","z":"bcef1420b275df09","name":"Restart PiHole","config":"1fd8e763e474591d","container":"pihole","containertype":"str","action":"restart","options":"","optionstype":"str","image":"","imagetype":"str","pullimage":false,"deletecontainer":false,"stream":false,"createOptions":"","startOptions":"","createOptionsType":"json","startOptionsType":"json","x":480,"y":320,"wires":[["f95e858204804bf3"]]},{"id":"25a7056d5d0564cf","type":"sqlite","z":"bcef1420b275df09","mydb":"1dc4cc53aafd3e69","sqlquery":"prepared","sql":"INSERT INTO\n client_by_group(client_id, group_id)\nVALUES ($clientId, 6)","name":"Reblock Shield","x":280,"y":320,"wires":[["7e958f461d6fcd56"]]},{"id":"83e14a95548bdd56","type":"cronplus","z":"bcef1420b275df09","name":"","outputField":"payload","timeZone":"Europe/Paris","storeName":"","commandResponseMsgOutput":"output1","defaultLocation":"","defaultLocationType":"default","outputs":1,"options":[{"name":"fixedDate","topic":"forceupdate","payloadType":"str","payload":"fixed","expressionType":"dates","expression":"2024-02-14 16:15 GMT+1","location":"","offset":"0","solarType":"all","solarEvents":"sunrise,sunset"},{"name":"schedule1","topic":"topic1","payloadType":"default","payload":"","expressionType":"cron","expression":"0 0 2 * * * *","location":"","offset":"0","solarType":"all","solarEvents":"sunrise,sunset"}],"x":100,"y":140,"wires":[["3c902481cdcca886"]]},{"id":"f95e858204804bf3","type":"subflow:0260961d70d9aa9a","z":"bcef1420b275df09","name":"","x":280,"y":380,"wires":[["e81617d268d0d741"]]},{"id":"e81617d268d0d741","type":"subflow:b289383d44ee87c6","z":"bcef1420b275df09","name":"","x":470,"y":380,"wires":[["76272edb7f20ae3a"]]},{"id":"ee19d59cae6cfc50","type":"debug","z":"bcef1420b275df09","name":"debug 10","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":460,"y":180,"wires":[]},{"id":"4d7a988df3f7c451","type":"comment","z":"bcef1420b275df09","name":"Shield blocked","info":"","x":720,"y":320,"wires":[]},{"id":"bd7b9992d7d79559","type":"comment","z":"bcef1420b275df09","name":"Shield not blocked","info":"","x":390,"y":280,"wires":[]},{"id":"1dc4cc53aafd3e69","type":"sqlitedb","db":"/pihole/gravity.db","mode":"RW"},{"id":"1fd8e763e474591d","type":"docker-configuration","host":"/var/run/docker.sock","port":"","ca":"","cert":"","key":""}]
Thank you in advance for your help.
