Page 1 of 1

Suggestion: Domoticz Database Edit Mode

Posted: Monday 08 October 2018 20:18
by 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
Domoticz_ErrorValue.png (41.95 KiB) Viewed 5415 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.

Re: Suggestion: Domoticz Database Edit Mode

Posted: Monday 08 October 2018 22:38
by Egregius
You can use adminer on the live database without stopping domoticz

Re: Suggestion: Domoticz Database Edit Mode

Posted: Tuesday 09 October 2018 11:13
by 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.

Re: Suggestion: Domoticz Database Edit Mode

Posted: Tuesday 09 October 2018 16:44
by 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.

Re: Suggestion: Domoticz Database Edit Mode

Posted: Tuesday 09 October 2018 17:32
by 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].

Re: Suggestion: Domoticz Database Edit Mode

Posted: Sunday 21 October 2018 17:02
by 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. :D

Domoticz_Meter_Day_Spike.png
Domoticz_Meter_Day_Spike.png (35.8 KiB) Viewed 5229 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.

Re: Suggestion: Domoticz Database Edit Mode

Posted: Tuesday 13 August 2019 17:13
by 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

Re: Suggestion: Domoticz Database Edit Mode

Posted: Friday 16 August 2019 10:12
by kimhav
Agree and we both are looking for an automatic way for the system to handles errors.

Re: Suggestion: Domoticz Database Edit Mode

Posted: Saturday 17 August 2019 14:11
by 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

Re: Suggestion: Domoticz Database Edit Mode

Posted: Friday 23 September 2022 13:49
by nitpicker
Egregius wrote: Monday 08 October 2018 22:38 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!

Re: Suggestion: Domoticz Database Edit Mode

Posted: Friday 30 September 2022 10:14
by Egregius
What did you try? What doesn't work? What error message are you getting?

Re: Suggestion: Domoticz Database Edit Mode

Posted: Monday 10 October 2022 14:06
by 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.

Re: Suggestion: Domoticz Database Edit Mode

Posted: Tuesday 11 October 2022 13:28
by Egregius
nitpicker wrote: Monday 10 October 2022 14:06 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.

Re: Suggestion: Domoticz Database Edit Mode

Posted: Wednesday 12 October 2022 21:39
by 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?

Re: Suggestion: Domoticz Database Edit Mode

Posted: Thursday 13 October 2022 14:52
by nitpicker
It just won't open the database... any help is appreciated...

sudo chmod 777 domoticz.db

Re: Suggestion: Domoticz Database Edit Mode

Posted: Sunday 08 January 2023 12:19
by 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 !!!

Re: Suggestion: Domoticz Database Edit Mode

Posted: Monday 18 November 2024 12:32
by TiXav
Thant you, your php script is still working