Suggestion: Domoticz Database Edit Mode

[kimhav]

Once a while it happens that the sensors or RFXMeters which provides input to a Domoticz instance tends to go bananas when electricity comes back after power outtakes or when replacing batteries in rain gauges, etc. The suggested solution to handle this is often to clone the database make the changes and copy and replace the original database with the changed one to fix what ever values one wants to get ride of or need to change. Just to give an example is that the other day I had to remove the mover for the pulse meters (RFXMeter) which of course gave a bump in the values (Sunday):

Domoticz_ErrorValue.png (41.95 KiB) Viewed 4202 times

The suggestion is to add Edit Mode to the View data table function which would solve a lot of pain when these small hiccups happens. Whether it's possible or not based on the database structure or I don't know and whether using a dummy device have any kind of implication. But, just quickly looking, it feels that we're almost there, and so close to be able to edit the values.

[Egregius]

You can use adminer on the live database without stopping domoticz

[Dnpwwo]

@kimhav,

You can already maintain the logged data without restoring old databases or anything else.

You should be able to click the data point you want to remove on the graph with the shift key held down and you will be asked if you want to delete it, something like:

Code: Select all

Are you sure to remove this value at ?: Date: 2018-07-07 Value: 6.8

select Ok and the data value will be removed.

[kimhav]

Yes, that is correct that I can remove logged data on the 25h graph but it seems that Domoticz isn't recalculating the Last Week view and the number there can't be removed. Hence the reason why I feel that having a simple edit option directly in Domoticz would make a lot of users happy.

[Toulon7559]

The 'Shift-Method' is not always working.
Like mentioned in the message of kimhav, in the last week of September the Rain Gauge in my Nexus_PWS needed replacement of the batteries.
Requires that after restart of the sensor you 'replace' the fresh sensor popping up by the 'old' sensor.
Unfortunately in the resulting data a cumulative value popped up for that day, which was far beyond reality.

Not knowing a better way for correction, tried to delete the erroneous value with the 'Shift-method', but in vain:
no reaction from Domoticz.
Furthermore, deletion is not always the aim, but correction may be appropriate:
would be nice to have a simple & quick editor directly going to the database to correct 'small & isolated' value-errors, both on recent data and on somewhat older data [ e.g. when you detect afterwards that a sensor went haywire during your holiday].

[kimhav]

Here is an example of typical erroneous value that I would like to be able to set to zero as it's occurs after a power outtake... I guess that most would agree that the value is far from valid for a standard household and no I don't have a flux capacitor in my car.

Domoticz_Meter_Day_Spike.png (35.8 KiB) Viewed 4016 times

What also could be nice would be the possibility to add specific rules to meters within what variables one would expect the reported values to be within when it comes to the aggregation of the values which Domoticz performs. And as mentioned, it's not a new issue, and it one of those annoying things which would be nice to find a good solution for.

[Xztraz]

wouldn't it be better to make options for the energy current/inc to handle incoming data a bit more predictable. this applies to dummy sensor too!

as for example. using an incrementing counter for power usage. it will reset during power outages and such. domoticz should handle that. and also some kind of limiting function. if value waay bigger than normal max reading just ignore value and set new base number.

this would make data input from ex espeasy alot easier too and results more exact than using calculated for energy usage (calculated from current load which is not a accurate).

also when energy current values are saved to database every 5 minutes. only the last value seem to be saved. should be an average

[kimhav]

Agree and we both are looking for an automatic way for the system to handles errors.

[Xztraz]

your error value seem to be related some limit of a variable or that it became negative/roll around.

ULLONG_MAX Maximum value for a variable of type unsigned long long 18446744073709551615 (0xffffffffffffffff)

It is solvable by feeding a counter dummy sensor - script that correct values and fixes base value if incoming value less than counter(local variable)- to a new counter - script to update watt current/inc meter.

but it's using 2 extra dummy devices and a local variable per measuring device. if applied to say measure 20 different devices.. ugh..

i think there is code regarding s0 usb sensor hardware that already does the correction of data if it rolls back to 0 and also it feeds a current/inc meter direct accurately. shouldn't be to hard reusing that code for dummy sensors and so

[nitpicker]

You can use adminer on the live database without stopping domoticz

How did you do that? I tried several things, but if you can post a how-to, it would help me (and others).

Thanks in advance!

[Egregius]

What did you try? What doesn't work? What error message are you getting?

[nitpicker]

I have created the following structure:

Code: Select all

    adminer.php
    index.php
    plugins/
           |__ login-password-less.php
           |__ plugin.php

When I browse to http://xxx.xxx.xxx.xxx, I get the: Apache2 Debian Default Page
When I browse to http://xxx.xxx.xxx.xxx/adminer.php, I get the: Adminer login page
When I browse to http://xxx.xxx.xxx.xxx/index.php, I get the This page isn’t working

I used the following instructions: https://stackoverflow.com/questions/655 ... ith-sqlite without the first 4 steps. Instead I installed php7.3-sqlite , now I get:

Database: \home\pi\domoticz\domoticz.db
Invalid database.

[Egregius]

When I browse to http://xxx.xxx.xxx.xxx/index.php, I get the This page isn’t working

This is your problem. You have to get this page working. There should be an error log of your webserver showing what the issue is.

[jake]

I could use some help with adminer too, since it is completely new for me.

I installed adminer through apt on my RPI. It gets installed in /usr/share/adminer. That doesn't make it run in the /var/www/html folder

I tried the adminer docker image. That seems fully configured. But, in the browser you need to fill in a server, user name, pw and database

I don't know what to fill in for either field. What is the server and port for the domoticz database?
The database is on \home\pi\domoticz\domoticz.db but with Docker that is outside of its container. How to point to the correct file?

[nitpicker]

It just won't open the database... any help is appreciated...

sudo chmod 777 domoticz.db

[eddieb]

I was struggeling with adminer too ..

adminer is installed on my rpi4 by using apt install adminer and that works.
made adminer availiable in /etc/apache2/conf-enabled and restarted apache

I found the next solution working :

create a file /etc/adminer/domoticz.php with the following content :

Code: Select all

<?php

define('ADMINER_DIR', '/usr/share/adminer');

function adminer_object() {
    // required to run any plugin
    include_once ADMINER_DIR . "/plugins/plugin.php";

    // autoloader
    foreach (glob(ADMINER_DIR . "/plugins/*.php") as $filename) {
        include_once $filename;
    }

    include_once ADMINER_DIR . "/plugins/login-password-less.php";

    class AdminerCustomization extends AdminerPlugin {
        function loginFormField($name, $heading, $value) {
            return parent::loginFormField($name, $heading, str_replace('value="server"', 'value="sqlite"', $value));
        }
	function databases($flush = true) {
            if (isset($_GET['sqlite']))
            	return ["/home/pi/domoticz/domoticz.db"];
#               # return ["/path/to/first.db", "/path/to/second.db"];
	    return get_databases($flush);
	}	    
    }
    
    return new AdminerCustomization(array(
        // TODO: inline the result of password_hash() so that the password is not visible in source codes
        new AdminerLoginPasswordLess(password_hash("password", PASSWORD_DEFAULT)),
    ));
}
#include ADMINER_DIR .'/adminer.php';
include ADMINER_DIR . '/editor.php';
#include ADMINER_DIR . '/editor-4.8.1.php';
?>

go to http://<domoticzIP>/adminer/domoticz.php and type a username but no password
you should be able to see the domoticz tables !!!

As the apache user does not have write access to the database you need to
chmod 777 /home/pi/domoticz/domoticz.db as root
to enable write access

WARNING, YOU CAN SCREW your domoticz install by making changes to the database !!!


WARNING 2, there is NO PROTECTION on this,
don't do this is your domoticz is exposed to the internet !!!

[TiXav]

Thant you, your php script is still working