Octopus Energy Integration: Display graph of usage?

[janusn]

@Biscuit

I am currently trying out Node-RED with node-red-nodes installed. I must be so dumb that I encountered a stupid error.

I have created a simple flow as following:

The properties of node HA2 SQLite is:

SQLStatement:
	SELECT
		metadata_id
	FROM
		states_meta
	WHERE
		entity_id = 'sensor.octopus_energy_electricity_[meter#]_[mpan#]_current_accumulative_cost'
;

The database was opened in Read-Only mode. It generated the following error when injected:

Error: SQLITE_READONLY: attempt to write a readonly database

However, the same SQL statement runs fine from CLI at readonly mode:

sqlite> .header on
sqlite> .open --readonly home-assistant_v2.db
sqlite>SELECT
    metadata_id
FROM
    states_meta
WHERE
    entity_id = 'sensor.octopus_energy_electricity_[meter#]_[mpan#]_current_accumulative_cost'
;
metadata_id
7361

What have I done wrong? How to fix it?

[Biscuit (Geoff)]

You will find the node settings I used in the code export in my earlier post. From memory, the node is very particular, and I think read only requires prepared statements, and even if you don’t use parameters in the statement, msg.payload input needs to be an empty object {}.

[janusn]

@Biscuit

Thanks for your reply.

Yes, I did try “prepared statements” as well. It resulted in the same error. As a simple SELECT statement, it should not require write access.

Does it mean write permission is a must even though when it has nothing to write to the database?

[janusn]

I have tried again with the docker mounting the home-assistant_v2.db file as rw and changed the sqlite node to RWC mode. It still generated the same error.

It seems to be something else with my configurations. Let me dig deeper.

[janusn]

I have tested with a sqlite database file created by node-red-node-sqlite. It was fine.

However, for the sqlite file home-assistant_v2.db mounted from HA. It always throws the error Error: SQLITE_READONLY: attempt to write a readonly database even when I have chown’ed the file to 1000, the default user id of node-red container.

Home Assistant and Node-RED are run in their separated docker containers.

Here are my compose.yaml files:

$ cat homeassistant/compose.yaml
---
include:
  - ./volumes.yaml

services:
  homeassistant:
    image: linuxserver/homeassistant:latest
    container_name: homeassistant
    deploy:
      resources:
        limits:
          memory: 1G
    environment:
      - PUID=3005
      - PGID=3005
      - TZ=Europe/London
      - REQUESTS_CA_BUNDLE=/etc/ssl/certs/ca-certificates.crt
    volumes:
      - "/etc/localtime:/etc/localtime:ro"
      - "./custom-cont-init.d:/custom-cont-init.d:ro"
      - "./config:/config"
      - type: volume
        source: cctv
        target: /cctv
        volume:
          subpath: ha2/hass_events
      - type: volume
        source: cctv
        target: /config/nest
        volume:
          subpath: ha2/nest

    ports:
      - 8123:8123
    restart: unless-stopped

    depends_on:
      mosquitto:
        condition: service_started
        restart: false

$ cat node-red/compose.yaml
---
services:
  node-red:
    container_name: node-red
    image: nodered/node-red:latest
    deploy:
      resources:
        limits:
          memory: 256M
    restart: unless-stopped
#     user: "3005:3005"
    environment:
      - TZ=Europe/London
    ports:
      - "1880:1880"
    volumes:
      - ./data:/data:rw
      - "../homeassistant/config/home-assistant_v2.db:/mnt/ha2/config/home-assistant_v2.db:rw"

And I have verified the permissions of the file home-assistant_v2.db inside the container node-red:

$ sudo docker exec -it -u 1000 node-red bash
e607b472495d:~$ id
uid=1000(node-red) gid=1000(node-red) groups=1000(node-red)
e607b472495d:~$ ls -lh /mnt/ha2/config/
total 4G     
-rw-rw-rw-    1 1000     1000        6.6G Jun 20 17:40 home-assistant_v2.db
e607b472495d:~$ ps aux | grep "node-red"
    1 node-red  0:00 {entrypoint.sh} /bin/bash ./entrypoint.sh
    7 node-red  0:12 node-red
  778 node-red  0:00 bash
  846 node-red  0:00 ps aux
  847 node-red  0:00 grep node-red

Is there anything I have missed?

[Biscuit (Geoff)]

The SQLite DB is file based, not server, so any connection has to be on the same physical machine. I use an HA Blue, fully supervised, and HA takes care of the docker containers and the required interconnections.

There is an SQLite Web interface add-on which adds easy access to the DB, and in Node-RED (add-on) I can use the file path /homeassistant/home-assistant_v2.db and the supervisor takes care of how it works. Other than that I have no idea of either the issue or a solution.

As you have found, posting on the Node-RED forum will typically prompt a suggestion to ask here as this is an “HA thing”, and posting here you may have to post outside of NR specifically, framing your question in terms of SQLite and containers.

[janusn]

@Biscuit

Thanks for your help.

I am happy to report that I have found the culprit. There are two more files accompany the sqlite database file home-assistant_v2.db that I have mounted inside the node-red container. The error is gone once I have mounted the extra files, home-assistant_v2.db-wal and home-assistant_v2.db-wal inside the node-red container.

One more question. What kind of helper entities do you use for the node-red to import the data into?

Edit: I discovered that Node-RED companion create the entity in Home Assistant.