Autor Thema: Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)  (Gelesen 72097 mal)

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
Hallo Joe, @all,

im ersten Beitrag gibt es die V7.16.0.

Enthalten ist eine Funktion "get ... dbValue <SQL-Statement>".
Diese Funktion arbeitet Blocking speziell für die Verwendung in User-eigenen Prozeduren/Prozessen. Die Eingabe akzeptiert Mehrzeiler und gibt ebenso mehrzeilige Ergebisse zurück (sofern vorhanden). Werden mehrere Felder selektiert und zurück gegeben erfolgt die Feldtrennung mit dem Trenner des Attr "sqlResultFieldSep" (default "|"), die Ergebniszeilen mit Newline "\n":

Hier einige Beispiele wie es angewendet werden kann.

In der FHEMWEB Kommandozeile:

{fhem("get Rep.LogDB1 dbValue select device,count(*) from history where timestamp > '2018-04-01' group by device")}
oder

get Rep.LogDB1 dbValue select device,count(*) from history where timestamp > '2018-04-01' group by device"
oder

{CommandGet(undef,"Rep.LogDB1 dbValue select device,count(*) from history where timestamp > '2018-04-01' group by device")}
Erstellt man eine kleine Routine in 99_myUtils, kann es so aussehen:

###########################################
#   dbvalue
###########################################
sub dbval ($$) {
  my ($name,$cmd) = @_;
  my $ret = CommandGet(undef,"$name dbValue $cmd"); 
return $ret;
}

Und dann in der FHEMWEB Kommandozeile:

{dbval("Rep.LogDB1","select count(*) from history")}
bzw. in einem eigenen Programm:

my $ret = dbval("Rep.LogDB1","select count(*) from history")

Vielleicht noch nicht 100%ig rund.
Wie immer freue ich mich über Testergebnisse oder Vorschläge.

Grüße,
Heiko
« Letzte Änderung: 13 April 2018, 19:52:05 von DS_Starter »
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
Ich habe noch etwas weiterentwickelt (V7.17.0 im ersten Beitrag)
Zunächst für MYSQL habe ich DbReadingsVal implementiert. Danke an Joe für das Statement !

Aus der Commandref:

Sobald ein DbRep-Device definiert ist, wird die Funktion DbReadingsVal zur Verfügung gestellt. Mit dieser Funktion läßt sich, ähnlich dem allgemeinen ReadingsVal, der Wert eines Readings aus der Datenbank abrufen. Die Funktionsausführung erfolgt blockierend. Die Befehlssyntax ist:

    DbReadingsVal("<name>","<device:reading>","<timestamp>","<default>")

    Beispiele:
    $ret = DbReadingsVal("Rep.LogDB1","MyWetter:temperature","2018-01-13 08:00:00","");
    attr <name> userReadings oldtemp {DbReadingsVal("Rep.LogDB1","MyWetter:temperature","2018-04-13 08:00:00","")}

    <name>               : Name des abzufragenden DbRep-Device
    <device:reading>    : Device:Reading dessen Wert geliefert werden soll
    <timestamp>        : Zeitpunkt des zu liefernden Readingwertes (*) in der Form "YYYY-MM-DD hh:mm:ss"
    <default>             : Defaultwert falls kein Readingwert ermittelt werden konnte


(*) Es wird der zeitlich zu <timestamp> passendste Readingwert zurück geliefert, falls kein Wert exakt zu dem angegebenen Zeitpunkt geloggt wurde.

Für SQLite und PostgreSQL muss das Statement noch angepast werden um es auch für diese DB's zu implementieren.

Grüße,
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter

Offline JoeALLb

  • Hero Member
  • *****
  • Beiträge: 1366
Hallo Heiko,

Wow, tolle Entwicklung, herzlichen Dank!!
Komme jedoch etwas ab Montag dazu mir das näher anzusehen, bin das WE über unterwegs....

SG Joe
FHEM-Server auf IntelAtom+Debian (8.1 Watt), KNX,
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
Hi Joe,

dein Statement arbeitet sehr schnell auf meinen Marias. Da ist die blocking Verarbeitung auch überhaupt kein Problem.
Super !
Nur die Statements für SQLite und Postgre fehlen nun noch. Mal schauen ob ich das alleine hinbekomme.

Bis dann,
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter

Offline JoeALLb

  • Hero Member
  • *****
  • Beiträge: 1366
Da könnte ich leider nur durch googeln helfen, nutze das selber nirgends.

Die Idee, da es schnell sein muss, ist eben den Union zu nehmen, statt
  order by abs(timestampaSoll-timestamp) , wo das Ergebnis für jeden Datensatz ausgerechnet werden (also mit füll Tablet scan)

Im Prinzip sollte das bei den anderen ganz ähnlich funktionieren....

SG Joe
FHEM-Server auf IntelAtom+Debian (8.1 Watt), KNX,
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
Für SQLIte habe ich es nun auch hinbekommen  ;)  und die Version neu hochgeladen.
Nun fehlt noch Postgre  ... aber jetzt ist erstmal WE ..

Grüße
Heiko
« Letzte Änderung: 15 April 2018, 22:37:09 von DS_Starter »
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter

Offline JoeALLb

  • Hero Member
  • *****
  • Beiträge: 1366
Hallo Heiko,

funktioniert toll!

Beispiel:
Über
attr VorlaufGesamt stateFormat {\
sprintf "%4.1f° ".\
             "(%4.1f°) ", \
  ReadingsNum($name,"temperature",undef),\
  DbReadingsVal("dbRep","$name:temperature",strftime("%Y-%m-%d %H:%M:%S", localtime(time-60*10)),"-100");;\
}
kann ich mir nun immer den Temperaturwert von vor 10 Minuten im Status mit anzeigen lassen. Somit lässt sich ein Trend leicht erkennen :D

sG
Joe
FHEM-Server auf IntelAtom+Debian (8.1 Watt), KNX,
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
Hi Joe,

na das ist doch prima.  :D

Da ich es auch noch für Postgre hinbekommen hatte, kann ich den neuen Stand auch einchecken.

LG,
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter

Offline Thowe

  • New Member
  • *
  • Beiträge: 11
Hallo Heiko,
ich schließe mich der Lobgemeinde zu Deinem dbrep-Modul (V7.17.0) an.

Allerdings habe ich ein Problem mit einem sqlCmd, mit dem ich ein maxValue-Wert in der DB umbenennen möchte.
define Rep.HT_Tagesenergieaufnahme DbRep DBLogging
attr Rep.HT_Tagesenergieaufnahme aggregation day
attr Rep.HT_Tagesenergieaufnahme device HT_Tagesenergieaufnahme
attr Rep.HT_Tagesenergieaufnahme reading state
attr Rep.HT_Tagesenergieaufnahme showproctime 1
attr Rep.HT_Tagesenergieaufnahme timestamp_begin previous_day_begin
attr Rep.HT_Tagesenergieaufnahme timestamp_end previous_day_end

define Vortag.Rep.HT_Tagesenergieaufnahme_max at *00:02:40 set Rep.HT_Tagesenergieaufnahme maxValue writeToDB

define N.Vortag.Rep.HT_Tagesenergieaufnahme_max notify Rep.HT_Tagesenergieaufnahme:(\d).*HT_Tagesenergieaufnahme__state__MAX.* {fhem("set Tagesgesamtenergieaufnahme_HT $EVTPART1 ;; set Rep.HT_Tagesenergieaufnahme sqlCmd UPDATE history SET timestamp = timestamp, device = 'Tagesgesamtenergieaufnahme_HT', reading = 'state' where TIMESTAMP >= §timestamp_begin§ and TIMESTAMP <= §timestamp_end§ and device = 'HT_Tagesenergieaufnahme' and reading = 'max_day_state'")}

Das führt zu folgendem Fehler:
DbRep Rep.HT_Tagesenergieaufnahme - ERROR - DBD::mysql::st execute failed: Unknown column 'timestamp_begin' in 'where clause' at ./FHEM/93_DbRep.pm line 5267

Viele Grüße,
Thowe
« Letzte Änderung: 20 April 2018, 10:47:57 von Thowe »

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
Hallo Thowe,

Zitat
Das führt zu folgendem Fehler:
DbRep Rep.HT_Tagesenergieaufnahme - ERROR - DBD::mysql::st execute failed: Unknown column 'timestamp_begin' in 'where clause' at ./FHEM/93_DbRep.pm line 5267

Ja,der Programmierer hat einen Fehler gemacht  :o
Habs mit der V7.17.1 im ersten Beitrag korrigiert, probiers mal bei dir.

Grüße,
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter

Offline Thowe

  • New Member
  • *
  • Beiträge: 11
Hallo Heiko,
Respekt, Deine Response-Zeiten und das Ergebnis: Funktioniert!

Vielen Dank,
Thowe


Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
 :D... ich checke die Version ein. Ist dann morgen früh im Regelupdate.

LG,
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter

Offline Thowe

  • New Member
  • *
  • Beiträge: 11
Hallo Heiko,

ich erhalte mit
define Rep.Monatskosten.HNT_Energie DbRep DBLogging
attr Rep.Monatskosten.HNT_Energie aggregation no
attr Rep.Monatskosten.HNT_Energie device Tageskosten.HNT_Energie
attr Rep.Monatskosten.HNT_Energie reading state
attr Rep.Monatskosten.HNT_Energie showproctime 1
attr Rep.Monatskosten.HNT_Energie timestamp_begin current_month_begin

define Calc.Rep.Monatskosten.HNT_Energie at *00:03:00 set Rep.Monatskosten.HNT_Energie sumValue writeToDB
und DB-Inhalt:

mysql> select * from history where device like '%HNT%';
+---------------------+-------------------------+-------+--------------------------+---------+-------------------+------+
| TIMESTAMP           | DEVICE                  | TYPE  | EVENT                    | READING | VALUE             | UNIT |
+---------------------+-------------------------+-------+--------------------------+---------+-------------------+------+
| 2018-04-21 23:59:59 | Tageskosten.HNT_Energie | DUMMY | state: 0.322139537113933 | state   | 0.322139537113933 |      |
+---------------------+-------------------------+-------+--------------------------+---------+-------------------+------+

 keinen Eintrag in die DB und folgende Warning:
If you want write results back to database, attributes "device" and "reading" must be set.
In that case "device" mustn't be a devspec and mustn't contain SQL-Wildcard (%).
The "reading" to evaluate has to be a single reading and no list.

Diese Meldung macht m.M.n. in dieser Situation keinen Sinn.
 
Viele Grüße,
Thowe

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
Hallo Thowe,

Zitat
Diese Meldung macht m.M.n. in dieser Situation keinen Sinn.
Da hast du Recht.
Ich schaue mir das an und melde mich ....

LG,
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 2645
Hallo Thowe,

habe es mit V7.17.2 abgeändert. (erster Beitrag)

Checke es mal bitte bei dir ob das Ergebnis den Erwartungen entspricht.

LG,
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 9/64 Bit  (Stretch),
DbLog/DbRep mit MariaDB auf Synology 415+,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN,Log2Syslog,SMAEM,SMAInverter