Month: November 2018

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
Advertisements
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.