Author: PI and more

MQTT out for Domoticz

MQTT out for Domoticz

I have been playing with MQTT for Domoticz. After some initial fiddling I have figured out all the commands to send to Domoticz using MQTT for all the different dummy sensors.

All MQTT messages (which you send to topic domoticz/in) have the following JSON format:

{ "command" : "" , "idx" :  , "nvalue" :  ,
 "svalue" : "" ...... }

In a JSON format you can have spaces in between, so “command”:”test” is the same as “command” : “test”. The default command for Domoticz is udevice so you can leave that out if you want to but I have included it in all of them.

Weather sensors

Barometer

The barometer shows the atmospheric pressure and a prediction. You can send digits for the pressure.

{"command":"udevice", "idx":1234, "svalue":"ba;pr"}

where ba is the pressure and pr is the prediction which can have the following values:

pr can have the following values:

  • 0, no prediction
  • 1, sunny
  • 2, partly cloudy
  • 3, cloudy
  • 4, rain
  • 5, unknown
  • 6, cloudy/rain

Temperature+Barometer

The Temperature+Humidity sensor will show just that and a prediction. You can send digits for the temperature but only the first is displayed (it is rounded off). You can send digits for the pressure as well.

{"command":"udevice", "idx":1234, "svalue":"tm;ba;pr"}

where tm is the temperature, ba is the pressure and pr is the prediction which can have the following values:

pr can have the following values:

  • 0, no prediction
  • 1, sunny
  • 2, partly cloudy
  • 3, cloudy
  • 4, rain
  • 5, unknown
  • 6, cloudy/rain

Temperature+Humidity+Barometer

The temperature+Humidity+Barometer sensor will show just that and an environment level and a prediction. You can send digits for the temperature but only the first is displayed (it is rounded off). The humidity only displays whole numbers (chops off any digits).

{"command":"udevice", "idx":1234, "svalue":"tm;hu;lv;ba;pr"}

where tm is the temperature, hu is the humidity and lv is the environment level, ba is the pressure and pr is the prediction.

lv can have the following values:

  • 0, normal
  • 1, comfortable
  • 2, dry
  • 3, wet

pr can have the following values:

  • 0, no prediction
  • 1, sunny
  • 2, partly cloudy
  • 3, cloudy
  • 4, rain
  • 5, unknown
  • (oddly enough 6 cloudy/rain does not work for this sensor)

Rain

The rain sensor will show the amount of rain fallen in mm and a rate in mm/h. You can send

{"command":"udevice", "idx":1234, "svalue":"rt;fl"}

where rt is the rate in mm/h fl is amount of rain fallen. PLEASE NOTE that fl is cumulative, so if the value is 3 mm and you send 2 mm, it will be 5 mm.

Solar radiation

The solar radiation sensor will show just that in W/m2. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

UV

The UV sensor will show just that in the UV Index. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"uv;0"}

where uv is the uv index.

Visibility

The visibility sensor will show just that in km. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Wind

The wind sensor will show wind direction, velocity and gust speed. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"an;dr;sp;gu;tm;ch"}

where an and dr are used to show the angle and wind direction, where an is the angle (can be any number and my have digits after the decimal) and dr is the direction. You can use N, NE, E, SE, S, SW, W and NW. sp is the speed in 0.1 m/s and gu is the gust speed in 0.1 m/s.

Wind+Temperature+Chill

The wind+temperature+chill sensor uses the same command as the wind sensor except in this case the tm (temperature, you can not use digits after the decimal) and ch (chill temperature, you can not use digits after the decimal) are shown.

Temperature sensors

Humidity

The humidity sensor will show humidity and an environment level. You can only send whole numbers.

{"command":"udevice", "idx":1234, "svalue":"lv", "nvalue":hu}

where hu is the humidity and lv is the environment level which can have the following values:

  • 0, normal
  • 1, comfortable
  • 2, dry
  • 3, wet

Temperature+Humidity

The temperature+humidity sensor will show humidity and an environment level. For the temperature you can send digits after the decimal. For humidity anything after the decimal will be chopped off.

{"command":"udevice", "idx":1234, "svalue":"tm;hu;lv"}

where tm is the temperature, hu is the humidity and lv is the environment level which can have the following values:

  • 0, normal
  • 1, comfortable
  • 2, dry
  • 3, wet

Temperature

The temperature sensor will show the temperature. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Temperature+Humidity+Barometer

See section Weather sensors.

Wind+Temperature+Chill

See section Weather sensors.

Utility sensors

Air quality

The air quality sensor will some value in ppm. You can only send whole numbers.

{"command":"udevice", "idx":1234, "nvalue":3}

Lux

The lux sensor will show the amount of light in lux. Any extra digits send are chopped off.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Pressure

The pressure sensor will show the pressure in bar. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Distance

The distance sensor will show the distance in cm. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Leaf wetness

The leaf wetness sensor will show the leaf wetness. You can only send whole numbers.

{"command":"udevice", "idx":1234, "nvalue":34}

Percentage

The percentage sensor will show a percentage. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Scale

The scale sensor will show the weight of something in kg. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Thermostat setpoint

The thermostat setpoint sensor will show setpoint of a thermostat. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Soil moisture

The soil moisture sensor will show the soil moisture in centiBars. You can only send whole numbers.

{"command":"udevice", "idx":1234, "nvalue":34}

Sound level

The sound level sensor will show the level of sound in decibel of something. Digits after the decimal are dropped.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Custom

The custom sensor will show what you want in the units you want. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Alert

The alert sensor will show you a message and an alert level.

{"command":"udevice", "idx":1234, "svalue":"any text", "nvalue":lvl}

where level can be any of the following:

  • 0 off, grey
  • 1 ok, green
  • 2 yellow, level 1 warning
  • 3 orange, level 2 warning
  • 4 red, level 3 warning

Text

The text sensor will show any text you want.

{"command":"udevice", "idx":1234, "svalue":"any text"}

Ampere (1 phase)

The ampere (1 phase) sensor will show the power draw in A. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Ampere (3 phase)

The ampere (3 phase) sensor will show the power draw in A for 3 phases. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"p1;p2;p3"}

where p1, p2 and p3 are the the power draw for every phase.

Usage

The voltage sensor will show the usage in Watt of something. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Voltage

The voltage sensor will show voltage of something. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Electric

The electric sensor will show the current usage of electricity in Watt and the cumulative usage in kWh. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"us;cu"}

where us is usage in Watt and cu is cumulative usage in Wh (even though it is displayed in kWh)

P1 Smart meter

The P1 Smart meter sensor allows you to show you power consumption, low and high usage, low and high return, actual consumption and actual return.

{"command":"udevice", "idx":1234, "svalue":"lu;hu;lr;hr;ac;ar"}

where lu is low tariff usage (in Wh so 1000 is 1 kWh), hu is high tariff usage (in Wh), lr is low tariff return (in Wh), hr is high tariff return (in Wh), ac is actual consumption (in W) and ar is current usage (in W).

Gas

The gas sensor will show the usage of gas in m3. You can add digits after the decimal if needed.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Waterflow

The waterflow sensor shows the water flow in liters / minute. You can add digits after the decimal.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

Counter

The counter sensor will show usage. You can add digits after the decimal if needed. The main display will show it rounded of to 2 digits while beneath it, it will show the whole number you upload. For some reason the log shows the value for every hour and that value is the value at the start of the hour.

{"command":"udevice", "idx":1234, "svalue":"3.45"}

The tricky part is that the type of counter determines what number you should upload:

  • Energy, upload per Wh, displayed in kWh
  • Gas, upload per 0.01 m3, displayed in m3
  • Water, upload per 10 l, display in l (and in m3 below the main display)
  • Counter, upload per 1 something, also displayed in the same format
  • Energy generated, upload per Wh, displayed in kWh
  • Time, have not figured out how to use this yet

Counter incremental

The counter incremental sensor will show the same thing as its counterpart counter but every time a value is send, it is added to the amount instead of replacing the amount.

Managed counter

Haven’t figured out yet what makes this different but it seems to act like a normal counter as described above.

Advertisements
Domoticz, ESP & MQTT, startup

Domoticz, ESP & MQTT, startup

Of course domotica with Domoticz is fun but it’s more fun if you can combine your own ESPs with Domoticz. That is what I have been fiddling with and what I will describe here.

There are basically 2 ways to connect to Domoticz, through HTTP or MQTT. With HTTP you can only tell Domoticz what to do. With MQTT however, Domoticz shares it’s changes so you can also respond to changes. I choose to go the MQTT route because that way I can create sensors and switches with my ESPs.

To get things started, you need to have an MQTT broker. There have been many people describing this on their blogs, so I will not cover that here. Suffice it to say that I choose to put the MQTT broker on my NAS since that is always up anyway. Once you have the MQTT broker, you need to configure MQTT for Domoticz. This is described nicely on their Wiki. You will also find instructions on how to install Node-RED. I did install that but you don’t need it for these projects. Once you have installed MQTT for Domoticz, you need to tell it to publish everything on the flat version (called out in the drop down list).

We are getting there. Once you have installed and configured MQTT for Domoticz, use an MQTT viewer to view the messages it generates. I personally like mqtt-spy. Remember you have to subscribe to the topic domoticz/out. Look at some of the messages and how they are structured. As you can see it’s JSON formatted. The messages you post to Domoticz on the domoticz/in topic are also JSON formatted.

Ok, let’s configure a dummy switch in Domoticz. I called mine TESTMQTT but you can call it whatever you want. Make sure it is a switch. Once you did that, write down the id number.

Good, now let’s play. Turn it on and back off again. Now go to your viewer and look for the id of your switch. If you are using mqtt-spy, it is the search button on the right of the screen next to where all the messages are shown. Once you find your message, you will see that it looks something like this:

{
  "Battery" : 255,
  "RSSI" : 12,
  "description" : "",
  "dtype" : "Light/Switch",
  "id" : "00014648",
  "idx" : 1528,
  "name" : "TESTMQTT",
  "nvalue" : 0,
  "stype" : "Switch",
  "svalue1" : "0",
  "switchType" : "On/Off",
  "unit" : 1
}

I won’t go in detail now, just now that “idx” is your id and in the case of a switch, “nvalue” tells whether it was turned on (1) or off (0).

Next, let’s turn on the switch using MQTT. Make sure the switch is off and than go to your viewer and send this message (remember to use your id number, not mine)

topic: domoticz/in
message: { "command" : "switchlight" , "idx" : 1528 , "switchcmd" : "On" }

And click on publish. Your dummy switch should turn on.

Next up is coding an ESP to be a light. As we need JSON to listen to the switches, we need to install the ArduinoJson library which you install like any library. We also need to install the pubsubclient library which again installs like any library. However in this case we need to make a change after the installation. Go to your libraries directory and go to the pubsubclient-master/src directory. There is a file called PubSubClient.h. Open it to make a change. We need to increase the maximum packet size. Around line 26 there is a line that we need to change. It should read after you’re done:

#define MQTT_MAX_PACKET_SIZE 512

Next save the file and we are ready to try some code. We will start with something simple, the internal led being turned on and off through Domoticz. I will start by sharing the code (also available on github-mark-32px Github) and discuss it below the code:

#include <ESP8266WiFi.h>
#include <PubSubClient.h>
#include <ArduinoJson.h>

const char* ssid = "MySSID";
const char* password = "MyPassword";
const char* mqtt_server = "mqtt.broker.com";
#define LED D4
#define MYIDX 1528

WiFiClient espClient;
PubSubClient client(espClient);

long lastMsg = 0;
char msg[50];
int value = 0;
char macAddr[18];
char mqttClient[25];

void setup_wifi() {
  delay(10);
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);

  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  randomSeed(micros());

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}

void actionOn () {
  digitalWrite(LED,LOW);
}

void actionOff () {
  digitalWrite(LED,HIGH);
}

void errorOccurred (String err) {
  Serial.println(err);
}

void callback(char* topic, byte* payload, unsigned int length) {
  DynamicJsonBuffer jsonBuffer(512);
  JsonObject& root = jsonBuffer.parseObject(payload);
  if (!root.success()) {
    errorOccurred("Parse json failed");
  } else {
    int idx = root["idx"];
    if (idx==MYIDX) {
      if (root["nvalue"]==0) {
        actionOff();
      } else {
        actionOn();
      }
    }
  }
}

void reconnect() {
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    sprintf(mqttClient,"ESP%s",macAddr);
    Serial.println(mqttClient);
    if (client.connect(mqttClient)) {
      Serial.println("connected");
      client.publish("outTopic","hello world");
      client.subscribe("domoticz/out");
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      delay(5000);
    }
  }
}

void setup() {
  pinMode(LED,OUTPUT);
  digitalWrite(LED,LOW);
  Serial.begin(9600);
  setup_wifi();
  client.setServer(mqtt_server,1883);
  client.setCallback(callback);
  byte ar[6];
  WiFi.macAddress(ar);
  sprintf(macAddr,"%02X%02X%02X%02X%02X%02X",ar[0],ar[1],ar[2],ar[3],ar[4],ar[5]);
}

void loop() {
  if (!client.connected()) {
    reconnect();
  }
  client.loop();
}

Let’s start at the top. We include the needed libraries and define our constants for wifi and the MQTT broker. I use a Wemos D1 so my internal led is linked to D4 and my test Domoticz dummy switch has id 1528. Initialize the wifi client and the MQTT client. I keep the mac address which I use for creating the client id and the name of the MQTT client.

The function setup_wifi() does what it says. The functions actionOn() and actionOff() are called to turn on and turn off our switch. In this case it is just write to a port but you can put more here. The function errorOccurred gets called when an error happens. It now just outputs to serial but there might be a need to change that.

The function callback() is the interesting one. This one gets called every time we receive a message on a topic that we are subscribed to. We assume the data is in JSON format and decode it using the ArduinoJson library. Next we check if this was a success and if so, if the idx passed is the one we are looking for. If it is, check nvalue and determine what to do based on it’s value.

The function reconnect() is straight from the pubsubclient example and basically reconnects if the connection is dropped and subscribes to our domoticz/out topic.

Finally the function setup() sets everything up, including getting a unique MQTT client name and the function loop() continously checks for messages.

If you upload this to your ESP it should work. Remember that we do not set any initial values so, the light starts on but maybe in Domoticz it is still off which means you will have to click twice for any change.

This is a very simple example, hardly any error checking but for some of you this may be enough to get going. In the next example I will expand a bit and also publish to Domoticz.

 

Domoticz and Xiaomi button

Domoticz and Xiaomi button

I bought a Xiaomi gateway to be used with my Domoticz. Initially I was not sure it would work but I’m pleased with the result. The main reason for me to want to integrate it is because it has relatively cheap buttons and a multifunctional cube. These buttons support click and double click (the square ones), including long press and long press release (the circle ones).

Xiamoi Round ButtonXiaomi Square ButtonXiamoi Cube

 

The cube has all kinds of motion it recognizes, rotate, fall, spin, flip, double tap, shake etc.

On AliExpress, you should be able to get the buttons for around 8-9 Euro each. The cube for around 12 Euro.

When you use the button, click on it for example, it shows up as such in Domoticz but the status is not reset even though the button is no longer pressed. I wanted the status of those buttons to always be Off unless an event just happened so what I did is create an event per button to execute the needed actions and than reset itself. Again not real rocket science but if you need it, feel free to copy it. Please note that in order for you to reset the button, you need to know it’s index number (the number in the Idx column when you look at devices.

--
-- Button1, device id = 8
--

commandArray = {}

for deviceName,deviceValue in pairs(devicechanged) do
    --
    -- Only respond on Button1 and if the status is not Off
    --
    if (deviceName=="Button1" and deviceValue~="Off") then
        --
        -- This button has 4 possible statusses.
        -- Check for each one and execute.
        --
        if (deviceValue=="Click") then
        elseif (deviceValue=="Double Click") then
        elseif (deviceValue=="Long Click") then
        elseif (deviceValue=="Long Click Release") then
        end
        --
        -- Reset the button to Off
        --
        commandArray[#commandArray + 1] = {["UpdateDevice"] = "8|Off|0"}
    end
end

return commandArray

The code for the cube is similar:

--
-- Cube1, device id = 115
--

commandArray = {}

for deviceName,deviceValue in pairs(devicechanged) do
    --
    -- Only respond on Cube1 and if the status is not Off
    --
    if (deviceName=="Cube1" and deviceValue~="Off") then
        --
        -- This cube has 10 possible statusses.
        -- Check for each one and execute.
        --
        print ("Cube1: "..deviceValue)
        if (deviceValue=="flip90") then
        elseif (deviceValue=="flip180") then
        elseif (deviceValue=="move") then
        elseif (deviceValue=="tap_twice") then
        elseif (deviceValue=="shake_air") then
        elseif (deviceValue=="swing") then
        elseif (deviceValue=="alert") then
        elseif (deviceValue=="free_fall") then
        elseif (deviceValue=="clock_wise") then
        elseif (deviceValue=="anti_clock_wise") then
        end
        --
        -- Reset the cube to Off
        --
        commandArray[#commandArray + 1] = {["UpdateDevice"] = "115|Off|0"}
    end
end

return commandArray
Domoticz device matching

Domoticz device matching

With Domoticz you will have multiple devices that need to be aligned, for example your remote and your light bulb, especially if you use 433 MHz devices. I migrated from my Homewizard to Domoticz and I have a lot of 433 MHz switches and sensors which I have reused. I even still have my Homewizard (to get the data from the temperature and rain sensors and the solar panels, power and water usage. With a lot of my 433 MHz devices, they can respond to a number of codes (most I believe can accept 4) but in my Domoticz screens, I want to know the real status of for example a light bulb.

Let’s elaborate with an example. I have a lamp near my TV and my Homewizard can turn it on and off by sending a code. I have captured that code on my Domoticz and that same code is in my system for the status of the lamp. So far, so good. I also have a remote which I use to turn on this light. It has a different code and in my Domoticz it is logged as remote X, button 1-1 for example. If I press the button on the remote, in Domoticz I can see that button pressed but the light is not on according to Domoticz. This is where my script comes in. It’s a script that aligns devices, so in this case “Lamp TV” and “Remote X, 1-1”.

This is the LUA code and it’s triggered for every device change:

--
-- Match devices event
--
-- Match the status of 2 devices. Match both directions or just left
-- to right or right to left
--

commandArray = {}

function matchStatus (fromD, toD, dirD)
    for deviceName,deviceValue in pairs(devicechanged) do
        --
        -- if the changed device is the left device and the direction
        -- is either B(oth) or L(eft only) then align them
        --
        if (deviceName==fromD and (dirD=="B" or dirD=="L")) then
            --
            -- Only change if the device value is different
            --
            if (otherdevices[toD]~=deviceValue) then
                commandArray[toD] = deviceValue
                print("MATCH:"..fromD.." ("..deviceValue..") to "..toD)
            end
        --
        -- if the changed device is the right device and the direction
        -- is either B(oth) or R(ight only) then align them
        --
        elseif (deviceName==toD and (dirD=="B" or dirD=="R")) then
            --
            -- Only change if the device value is different
            --
            if (otherdevices[fromD]~=deviceValue) then
                commandArray[fromD] = deviceValue
                print("MATCH:"..toD.." ("..deviceValue..") to "..fromD)
            end
        end
    end
end


--
-- B both sides should match
-- L left side copies to right side only
-- R right side copies to left side only
--
matchStatus("Lamp TV","Remote X, 1-1","B")

return commandArray

It’s not rocket science but might fill the need for someone else. Feel free to use and adapt it. If you come up with a good addition, please let me know.

Domoticz auto cleaning

Domoticz auto cleaning

When I started out with Domoticz I was very pleased. Smooth running, fast and very responsive. After some time though, I noticed that the webinterface was slow, some of the scripts slow etcetera. After some frustrating investigation I found it helps to restart Domoticz daily. I know, Linux, it should not be needed but it does the trick for me. What helps is that during this restart, between the shutdown and the restart, I clean the database and reload the daemons. I also move the backup files to my NAS and the same for the log file. That way I can keep the logs and backups for longer since the card for the Pi is not that large. I have been so happy with the result that I’ve decided to share it.

In the cron I have put a daily task to restart Domoticz. I have decided to do this at exactly midnight because of some counter scripts that I have running on Domoticz.

Open crontab:

sudo crontab -e

and enter this line:

0 0 * * * /home/pi/restart.sh

The script restart.sh does the following

  • stop Domoticz
  • clean the database
  • reload the daemons
  • save the log file
  • move the log files to the NAS
  • start a new log
  • move the backup files to the NAS
  • restart Domoticz
#!/bin/bash

#
# Show date and time of restart (convenient when manually restarting)
#
echo $(date +%F_%H-%M-%S)
echo .

#
# Stop Domoticz
#
echo Stopping Domoticz
sudo service domoticz.sh stop

#
# Clean up the database
#
echo Cleaning database
sudo php /home/pi/domoticz/scripts/my_own/cleanup_db.php

#
# Reload the daemons
#
echo Reloading daemons
sudo systemctl daemon-reload

#
# Save the log file
#
cp /home/pi/log/domoticz.log /home/pi/log/$(date +%F_%H-%M-%S).domoticz.log
chown pi:pi /home/pi/log/*

#
# Move log to the NAS and start a new one
#
mv /home/pi/log/2* /mnt/synology/log/
echo . > /home/pi/log/domoticz.log

#
# Move all backup files except that of today to the NAS
#
DAY=$(date +%d)
echo $DAY
FILES=/home/pi/domoticz/backups/daily/*
for f in $FILES
do
  if [ $f != "/home/pi/domoticz/backups/daily/backup-day-$DAY-Domoticz.db" ]
    then
      sudo mv $f /mnt/synology/backups/
  fi
done

#
# Restart Domoticz
#
echo Starting Domoticz
sudo service domoticz.sh start

 

Connecting Pi to Synology

Connecting Pi to Synology

I have a Synology and Domoticz on a Raspberry Pi. I wanted to be able to transfer files quickly between the two (log files, backups etc) but never got the connection working until I decided I really needed it to work and finally got it to work. As always, the steps are not that hard, if you know them.

This guy has described it perfectly.

I originally also created a domoticz user on my Synology but that is not needed. I did create a special shared folder for my Domoticz so I know only that as accessible. I am sure I can get NFS permissions set up somehow. For now, only my Domoticz can access that folder through NFS. One thing to note is that the files created by the Domoticz host is that the don’t have a username as owner but rather the user id.

Great, and now that that’s setup, I can get the log and backup files on my Synology. I will document this in another blog.

Fixing database of Domoticz

Fixing database of Domoticz

I have turned on automatic backup for Domomticz which keeps a copy of your database every hour and every day and every month. What I failed to check was whether there was enough room on my SD card to cope with (24+31+some months) * size of database. It should but as it turned out it didn’t. Mainly because the database had grown to over 650 MB (my blog on how I got this down to normal size and keep it that way here). Domoticz crashed and left me with a corrupted database. It took me some time to figure out what to do so I wanted to document this for others who encounter the same problem.

First things first. Domoticz had crashed because my disk was full. The problems did not stop there of course because a full volume means a whole lot of things did not work as they should. To quickly get some breathing room, I just deleted some old backups:

sudo rm /home/pi/domoticz/backups/hourly/backup-hour-??-Domoticz.db

Next up is fixing the database. In my case I had to stop Domoticz because even though the database crashed, Domoticz was up and (not) running).

sudo service domoticz.sh stop

Next we need to check the database. If you don’t have SQLite3 on your pi, you need to install it:

sudo apt-get install sqlite3

Go to your subdirectory of Domoticz and start SQLite3 with your database:

sqlite3 domoticz.db

Next you want to check whether it is corrupted using:

PRAGMA integrity_check;

If all is fine, great!, you can leave SQLite3 again using:

.exit

If not, let’s get the database fixed. The way to do this is to export the database, modify the generated SQL file and import it in a new database. Remember, do this with a COPY of your database so exit SQLite3. Let’s go through all the steps.

sudo service domoticz.sh stop
cd /home/pi/domoticz
cp domoticz.db test.db 
sqlite3 test.db


Please note that copying the database could take a long time if you haven’t cleaned it recently. After SQLite3 starts, create an export:

.mode insert
.output test.sql
.dump
.exit

Again dumping the database could take a long time if your database is large. Next you create a new database with the created dump:

sqlite3 new.db < test.sql

Again, this could take a while. When it finished, you can open your new database:

sqlite3 new.db

and check the integrity:

PRAGMA integrity_check;

It should now return without error and return an ok. Now let’s verify that there is at least something:

.tables

You should see a whole list of tables. Let’s verify that there is data in them:

SELECT COUNT(*) FROM LightingLog;

If you see tables and log entries, we can assume the fix worked. Exit SQLite3:

.exit

Now we need to remove the old database and replace it with our new database (and clean up after ourselves):

rm domoticz.db
mv new.db domoticz.db
rm test.db
rm test.sq

You can now restart Domoticz or run the cleanup of the database first and restart it afterwards.

sudo service domoticz.sh start

 

 

 

Clean database for Domoticz

Clean database for Domoticz

I have had my Domoticz for quite some time and I noticed that the database was quite large. Even so large that the backup of that database by Domoticz itself caused it to crash. So I researched how to keep the database size down to a normal level (for reference, it was well over 650 MB in size). When I first tried to do anything to the database, I found it was corrupt. Fixing that is a whole different story which you can read here.

The Domoticz database is a SQLite3 database. You can easily open it if you have SQLite3 installed on your pi (or whatever you run Domoticz on). If you haven’t:

sudo apt-get install sqlite3

After that you can open the database by going to your Domoticz directory and executing this:

sqlite3 domoticz.db

You can verify if it is open by checking for example all tables in it:

.tables

To check whether your database is corrupt you can use:

PRAGMA integrity_check;

To exit SQLite3 you use:

.exit

As it turns out, Domoticz is not always cleaning up properly. It leaves records in the database without a parent. Also old records remain in there, even if you have set a limit in the settings tab. What I have done is taken a script I found on on the forum and updated it with some extra tables. I run this script every morning through crontab:

 /usr/bin/php /home/pi/domoticz/scripts/my_own/cleanup_db.php

The updated script:

<?php
echo "<xmp>";
echo "Cleaning up tables.\n\n";
#
# Open the database
#
$db = new SQLite3('/home/pi/domoticz/domoticz.db');
#
# Your cut off date
# 86400 seconds is one day
#
$clean = strftime("%G-%m-%d %k:%M:%S",time()-86400);
#
# All the tables I could find
#
$tables = array('MultiMeter','MultiMeter_Calendar','Percentage',
'Percentage_Calendar','Rain','Rain_Calendar','Temperature',
'Temperature_Calendar','UV','UV_Calendar','Wind','Wind_Calendar',
'Meter','Meter_Calendar','Fan','Fan_Calendar','LightingLog');
#
# Loop through all tables to clean up
#
foreach ($tables as $table) {
  echo "Table: $table\n";
  $query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in
(select ID from DeviceStatus where Used = 1)");
  if ($query) {
    $rows = $db->changes();
    if ($rows>0)
      echo $rows." rows removed from $table\n";
  }
  $query=$db->exec("DELETE FROM $table WHERE Date < '$clean'");
  if ($query) {
    $rows=$db->changes();
    if ($rows>0)
      echo $rows." rows removed from $table\n";
   }
}
#
# Remove the empty space from the database
#
echo "\nStarting VACUUM\n";
$sql = 'VACUUM;';
if (!$result = $db->exec($sql)) {
  die('There was an error running
the query [' . $db->error . ']');
}
echo "Done VACUUM\n";
echo "</xmp>\n";
?>

 

Remember to check the database location and your retention in the script. This script deletes everything older than a day. If your database is large and you run this the first time, be prepared for something that could take more than 1 hour. Subsequent times should be minimal. Don’t abort!!! because you could break your database. For me, the database went from 693 MB to 6 MB. The first couple of times I ran the script on the command prompt to make sure it ran ok. Please remember to make a copy of your database BEFORE you test the script.

Mi Flora and the ESP32

Mi Flora and the ESP32

I initially bought the ESP32 to connect to my Mi Flora sensors. So after I managed to figure out the basics for working with them, I wanted to dive right in and get the Mi Floras working.

First stop, search engine and of lucky for me, somebody already did a lot of the work. On GitHub, user sidddy has a working version of getting an ESP32 talking to a Mi Flora. This was an excellent start as it works quite nicely. The code is straight forward, the ESP wakes up, sets up wifi and bluetooth connection, connects to the Mi Flora, looks for the services it needs, posts the values on MQTT and sleeps again.

As I said, excellent starting point but I wanted a bit more. First, I found that the connection does not always succeed but since these ESPs are now hidden in my garden I wanted to know if the values of the Mi Floras were not updated in my domotica whether it was a problem of the ESP or the Mi Flora. To do that I modified the code so that it sends out an MQTT message with it’s own mac address so I know it was alive. Next, I wanted to have it query multiple Mi Floras. Actually I was hoping to have just one ESP32 for my garden but the range of the ESP32 and the Mi Flora was a bit shorter than I hoped. For me they needed to be at most about 3 meters apart.

I modified the code to sequentially poll 4 Mi Floras in one part of the garden. I managed to get this working only to find that sometimes it can ‘hang’ waiting on the right response from the Mi Flora. Instead of extending the time it stays awake, I decided I would try to poll them all but I would choose randomly from the devices that I did not yet poll. Once in operation, I also found that sometimes you get values that you do not want to have in your domotica environment, 0 degrees temperature (in Spring) or 0% humidity so I have added some checks around each value as well. If the range is off, it will just not post that particular value to MQTT. All in all I found that if I poll them every 30 minutes (a bit frequent, I know) than I have a pretty good chance of them reporting back at least once in every 2 hours which is the limit I have set in my domotica environment.

So the end result, which I have posted on github-mark-32pxGitHub, is an extension of the mentioned version and all credits go to sidddy as the only thing I did was add a bit of code around his ground work.

 

ESP32

ESP32

I have been meaning to try the new ESP32 for quite a while and I finally got around to ordering them and trying them. As you may know, the ESP32 is the successor of the ESP8266 and the biggest addition, at least for me, is BLE, bluetooth low energy, support. So before I go into how I managed to get it to talk to several MiFloras, let’s first set it up to work with Arduino.

Of course it does not make sense to rewrite what others have done. I followed this article to set up Arduino to work with the ESP32.

When you’re done, read on.

What I could not find in the article is how to configure some of the settings in Arduino, so here is what I have and what works for me:

ESP32 settings

What also gave me some issues is getting the code to upload. As you can see in the picture above, I bought 2 different versions, a small one for which you have to solder the pins yourself and which has 2 buttons on the back and the ESP32 DEV module. Initially I could not get the code to upload to the ESP32 DEV module. When I tried the same for the samller module, it seemd to work. After some trial and error I figured out how to upload to the ESP32 DEV module as well. Tell Arduino to upload and after it is done compiling, it will show you:

esptool.py v2.3.1
Connecting........_______

This will continue and eventually you will get a timeout unless…. you press the button marked boot and hold it until Arduino starts uploading. Than you can let go. I don’t know why this is not needed for the smaller one and maybe it is a setting in Arduino which puts it in boot mode, similar to what could be done for the ESP8266. If I find it, I will update this article.

Now, you are all done and you want to start uploading some BLE sketches only to find it does not allow you to do so. Something about memory.  Luckily somebody already figured out what needs to be done. You need to change the partition size as described in this article. I choose method 3 but instead of creating a new type, I just updated the existing type (lazy, I know). I changed the following 2 files (Windows platform):

C:\Program Files (x86)\Arduino\hardware\espressif\esp32\boards.txt

and changed the following (marked in bold underline):

##############################################################

esp32.name=ESP32 Dev Module

esp32.upload.tool=esptool
esp32.upload.maximum_size=1966080
esp32.upload.maximum_data_size=294912
esp32.upload.wait_for_upload_port=true

esp32.serial.disableDTR=true
esp32.serial.disableRTS=true

and changed the file (path clipped to prevent scrolling):

C:\Pro...ino\hardware\espressif\esp32\tools\partitions\default.csv

to (full file):

# Name,   Type, SubType, Offset,  Size, Flags
nvs,      data, nvs,     0x9000,  0x5000,
otadata,  data, ota,     0xe000,  0x2000,
app0,     app,  ota_0,   0x10000, 0x1E0000,
app1,     app,  ota_1,   0x1F0000,0x1E0000,
eeprom,   data, 0x99,    0x3F0000,0x1000,
spiffs,   data, spiffs,  0x3F1000,0xF000,

So now you can upload your sketch and you might run into the next problem; either the ESP keeps rebooting and you get a message about brown out. There are people that stop brownout detection (see Google) but what worked for me, at least during development on the computer, change the cable to a better one. The cheap Chinese cables gave me brownouts while the more sturdy cables I had worked.

Next step…. using the ESP32 to talk to the Xiaomi MiFlora.