Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)

[ch.eick]

Die variablen werden natürlich ersetzt. Das sqlCmd muss man einfach neu reinkopieren. Bei Skripten ist das eh kein Problem.

Okay, dann kommt das mit in mein Scheduling Device :-)

Vielen Dank für die schnelle Unterstützung
      Christian

[ch.eick]

Hallo Heiko,
sorry für meine verwirrte Rückmeldung.

Jetzt geht es mit dem §device§ und §reading§ irgendwie nicht mehr.

Ich hatte bei meinem Test etwas durcheinander mit dem reload.

Getestet hatte ich FVERSION   93_DbRep.pm:v8.42.9-s24929/2021-09-06 und da ging es soweit.
Jetzt habe ich allerdings FVERSION 93_DbRep.pm:v8.43.0-s24929/2021-09-06 und da geht es wieder nicht :-(

Leider hatte ich zwei Sachen gleichzeitig gemacht und das mit den readings und userExitFn getestet.

[DS_Starter]

Hallo Christian,

Bin zur zeit unterwegs und schaue mir am we nochmal an.

Grüsse,
Heiko

[DS_Starter]

Hallo Christian,

hast im Statement den gekennzeichneten Teil vergessen:

SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE=§device§ AND READING=§reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING;

Damit läuft es es.

[ch.eick]

Hallo Christian,

hast im Statement den gekennzeichneten Teil vergessen:

SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE=§device§ AND READING=§reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING;

Damit läuft es.

Leider nein

Code: [Auswählen]

Internals:
   DATABASE   fhem
   DEF        LogDB
   FUUID      614b0a08-f33f-61a8-f1e8-10ef59dfe90ba545
   FVERSION   93_DbRep.pm:v8.43.0-s24929/2021-09-06
   LASTCMD    sqlCmd SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE=§device§ AND READING=§reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING
   MODEL      Client
   NAME       LogDBRep_Statistic_previous_Year
   NOTIFYDEV  global,LogDBRep_Statistic_previous_Year
   NR         541
   NTFY_ORDER 50-LogDBRep_Statistic_previous_Year
   ROLE       Client
   STATE      done
   TYPE       DbRep
   UTF8       1
   HELPER:
     DBLOGDEVICE LogDB
     GRANTS     USAGE,ALL PRIVILEGES
     IDRETRIES  2
     MINTS      2019-04-03 00:23:42
     PACKAGE    main
     SQLHIST   
     UEFN_REGEXP .*:.*
     USEREXITFN splitReading
     VERSION    8.43.0
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      0
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   OLDREADINGS:
   READINGS:
     2021-09-25 10:33:55   SqlResultRow_1  TIMESTAMP|READING|VALUE
     2021-09-25 10:33:55   sqlCmd          SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE=§device§ AND READING=§reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING
     2021-09-25 10:33:55   sqlResultNumRows 0
     2021-09-25 10:33:55   state           done
Attributes:
   DbLogExclude .*
   allowDeletion 0
   comment    Version 2021.09.23 15:00
   device     WR_1_API
   reading    Statistic_%Year  EXCLUDE=%Autarky%,%Rate%
   room       System
   userExitFn splitReading .*:.*
   verbose    3


[DS_Starter]

Doch:

Code: [Auswählen]

2021.09.25 10:42:32.913 4: DbRep Rep.LogDB - -------- New selection ---------
2021.09.25 10:42:32.914 4: DbRep Rep.LogDB - Command: sqlCmd SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE=§device§ AND READING=§reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING;
2021.09.25 10:42:32.917 4: DbRep Rep.LogDB - FullDay option: 0
2021.09.25 10:42:32.917 4: DbRep Rep.LogDB - Timestamp begin human readable: 2021-01-01 00:00:00
2021.09.25 10:42:32.918 4: DbRep Rep.LogDB - Timestamp end human readable: 2021-09-24 23:59:59
2021.09.25 10:42:32.935 4: DbRep Rep.LogDB - adminCredentials successfully read from file
2021.09.25 10:42:32.939 4: DbRep Rep.LogDB - database user for operation: root
2021.09.25 10:42:32.940 4: DbRep Rep.LogDB - SQL execute: SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE='MyWetter' AND READING='temperature' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING;
2021.09.25 10:42:32.979 4: DbRep Rep.LogDB - SQL result: 2020-12-31 00:02:59 temperature 0

Naja, dein Attribut reading passt natürlich nicht, wird ja 1:1 übernommen.

[ch.eick]

Hmm, ?

Code: [Auswählen]

2021.09.25 10:45:15.062 4: DbRep LogDBRep_Statistic_previous_Year - -------- New selection ---------
2021.09.25 10:45:15.062 4: DbRep LogDBRep_Statistic_previous_Year - Command: sqlCmd SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE=\u00a7device\u00a7 AND READING=\u00a7reading\u00a7 AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING
2021.09.25 10:45:15.062 4: DbRep LogDBRep_Statistic_previous_Year - Timestamp begin human readable: not set
2021.09.25 10:45:15.062 4: DbRep LogDBRep_Statistic_previous_Year - Timestamp end human readable: not set
2021.09.25 10:45:15.085 5: DbRep LogDBRep_Statistic_previous_Year - start BlockingCall with PID "17956"
2021.09.25 10:45:15.140 4: DbRep LogDBRep_Statistic_previous_Year - database user for operation: fhemuser
2021.09.25 10:45:15.141 4: DbRep LogDBRep_Statistic_previous_Year - SQL execute: SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE='WR_1_API' AND READING='Statistic_%Year  EXCLUDE=%Autarky%,%Rate%' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING;
2021.09.25 10:45:15.164 5: DbRep LogDBRep_Statistic_previous_Year - BlockingCall finished PID "17956"
2021.09.25 10:45:15.164 5: DbRep LogDBRep_Statistic_previous_Year - SQL result decoded: TIMESTAMP|READING|VALUE

Bei meinem ersten Test wurde aber der SQL Syntax vom §reading§ auch noch korrekt aufgelöst, was jetzt nicht mehr der Fall ist und somit natürlich nichts aus der Tabelle ausgewählt wird.

EDIT:
Da lief es bereits richtig

Code: [Auswählen]

...WHERE DEVICE=§device§ AND §reading§ AND...

...WHERE DEVICE='WR_1_API' AND READING LIKE 'Statistic_%Year' AND READING NOT LIKE '%Autarky%' AND READING NOT LIKE '%Rate%' AND...

[DS_Starter]

Dein reading-Attr:

 

Code: [Auswählen]

Statistic_%Year  EXCLUDE=%Autarky%,%Rate%

kann in dem Kontext nicht klappen weil reading mit §reading§ im sqlCmd 1:1 übernommen wird.

In festgelegten sql Commands löse ich EXCLUDE= im Modul entsprechend auf um die SQL zu gestalten. Aber in der freien sqlCmd geht das nicht. Hier hat der user die Gewalt über das was er ausführen möchte und muß die SQL entsprechend bauen.
Die Platzhalter können hier nur eine kleine Hilfe zur Verallgemeinerung sein.

[ch.eick]

Dein reading-Attr:

 

Code: [Auswählen]

Statistic_%Year  EXCLUDE=%Autarky%,%Rate%

kann in dem Kontext nicht klappen weil reading mit §reading§ im sqlCmd 1:1 übernommen wird.

In festgelegten sql Commands löse ich EXCLUDE= im Modul entsprechend auf um die SQL zu gestalten. Aber in der freien sqlCmd geht das nicht. Hier hat der user die Gewalt über das was er ausführen möchte und muß die SQL entsprechend bauen.
Die Platzhalter können hier nur eine kleine Hilfe zur Verallgemeinerung sein.

Was mich jedoch wundert ist, dass es bereits in FVERSION   93_DbRep.pm:v8.42.9-s24929/2021-09-06 funktioniert hat.
Ansonsten hatte ich dann noch eine Meldung zur Feldlänge von §reading§ , die wohl auf 64 Zeichen begrenzt ist.

[DS_Starter]

Was mich jedoch wundert ist, dass es bereits in FVERSION   93_DbRep.pm:v8.42.9-s24929/2021-09-06 funktioniert hat.

Schwer vorstellbar weil es bei der V noch keine Platzhalter/Ersetzungen von §device§, §reading§ gab.
Who knows ... Magie .... 

[ch.eick]

Schwer vorstellbar weil es bei der V noch keine Platzhalter/Ersetzungen von §device§, §reading§ gab.
Who knows ... Magie .... 

Sehr merkwürdig, da ich das list Device ja geposted hatte.

Da §reading§ von der Länge limitiert ist kann ich es dann so leider nicht verwenden. Ich wollte es natürlich dem Syntax vom Attribut reading angepasst nutzen.
Es ja dann auch ohne, also mit voll ausgeschriebenem SQL SELECT.

Ich habe mal in den Code geschaut, wäre es denkbar die Funktion DbRep_createCommonSql() zu verwenden, om den §reading§ Syntax zu übertragen?
Leider kann ich noch nicht genug Perl :-(

EDIT: Ich habe mal Jugend forscht gemacht und die Funktion DbRep_createCommonSql() aufgerufen.
Leider bin ich mit dem $selspec nicht so klar gekommen ;-)

Code: [Auswählen]

  # Allow inplace replacement of keywords for timings (use time attribute syntax), device, reading
  $sql =~ s/§timestamp_begin§/'$runtime_string_first'/g;
  $sql =~ s/§timestamp_end§/'$runtime_string_next'/g;

## ch.eick patch ########################################
#  $sql =~ s/§device§/'$device'/xg   if ($device);
#  $sql =~ s/§reading§/'$reading'/xg if ($reading);
 
  if ($reading) {
    $reading = DbRep_createCommonSql($hash,undef,undef,$reading,undef,undef,undef);
    $reading =~ s/AND 1 \;//ig;
    $sql =~ s/§reading§/$reading/ig;
  }
  if ($device) {
    $device  = DbRep_createCommonSql($hash,undef,$device,undef,undef,undef,undef);
    $device  =~ s/AND 1 \;//ig;
    $sql =~ s/§device§/$device§/ig;
  }

#########################################################
Das kann man so natürlich noch nicht einbauen, es soll nur ein erste Ansatz sein.


So würde es dann jetzt mit meinem Patch aussehen.

Code: [Auswählen]

2021.09.25 14:43:38.946 4: DbRep LogDBRep_Statistic_previous_Year - -------- New selection ---------
2021.09.25 14:43:38.946 4: DbRep LogDBRep_Statistic_previous_Year - Command: sqlCmd SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE \u00a7device\u00a7 AND \u00a7reading\u00a7 AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING
2021.09.25 14:43:38.946 4: DbRep LogDBRep_Statistic_previous_Year - Timestamp begin human readable: not set
2021.09.25 14:43:38.946 4: DbRep LogDBRep_Statistic_previous_Year - Timestamp end human readable: not set
2021.09.25 14:43:38.968 5: DbRep LogDBRep_Statistic_previous_Year - start BlockingCall with PID "32497"
2021.09.25 14:43:39.015 4: DbRep LogDBRep_Statistic_previous_Year - database user for operation: fhemuser
2021.09.25 14:43:39.019 1: PERL WARNING: Use of uninitialized value $device in pattern match (m//) at ./FHEM/93_DbRep.pm line 9870.
2021.09.25 14:43:39.020 1: PERL WARNING: Use of uninitialized value $idevice in split at ./FHEM/93_DbRep.pm line 9896.
2021.09.25 14:43:39.020 5: DbRep LogDBRep_Statistic_previous_Year - Devices for operation -
included (0): 
included with wildcard: 
excluded (0): 
excluded with wildcard:
2021.09.25 14:43:39.023 5: DbRep LogDBRep_Statistic_previous_Year - Readings for operation -
included (0): 
included with wildcard: Statistic_%Year
excluded (0): 
excluded with wildcard: %Autarky%,%Rate%
2021.09.25 14:43:39.024 1: PERL WARNING: Use of uninitialized value $selspec in concatenation (.) or string at ./FHEM/93_DbRep.pm line 9561.
2021.09.25 14:43:39.025 1: PERL WARNING: Use of uninitialized value $addon in concatenation (.) or string at ./FHEM/93_DbRep.pm line 9636.
2021.09.25 14:43:39.026 5: DbRep LogDBRep_Statistic_previous_Year - Devices for operation -
included (1): WR_1_API
included with wildcard: 
excluded (0): 
excluded with wildcard:
2021.09.25 14:43:39.026 1: PERL WARNING: Use of uninitialized value $reading in substitution (s///) at ./FHEM/93_DbRep.pm line 9973.
2021.09.25 14:43:39.027 1: PERL WARNING: Use of uninitialized value $reading in pattern match (m//) at ./FHEM/93_DbRep.pm line 9975.
2021.09.25 14:43:39.027 1: PERL WARNING: Use of uninitialized value $ireading in split at ./FHEM/93_DbRep.pm line 9996.
2021.09.25 14:43:39.028 5: DbRep LogDBRep_Statistic_previous_Year - Readings for operation -
included (0): 
included with wildcard: 
excluded (0): 
excluded with wildcard:
2021.09.25 14:43:39.029 4: DbRep LogDBRep_Statistic_previous_Year - SQL execute: SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE  ( DEVICE = 'WR_1_API' )  AND  ( READING LIKE 'Statistic_%Year' ) AND READING NOT LIKE '%Autarky%' AND READING NOT LIKE '%Rate%'  AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING;
2021.09.25 14:43:39.081 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_EnergyFeedInGrid_Year 5200
2021.09.25 14:43:39.081 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_EnergyHomeBat_Year 1435
2021.09.25 14:43:39.082 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_EnergyHomeGrid_Year 2386
2021.09.25 14:43:39.082 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_EnergyHomePvSum_Year 4765
2021.09.25 14:43:39.082 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_EnergyHomePv_Year 3330
2021.09.25 14:43:39.082 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_EnergyHome_Year 7149
2021.09.25 14:43:39.082 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_TotalConsumption_Year 7152
2021.09.25 14:43:39.082 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_Yield_NoBat_Year 8530
2021.09.25 14:43:39.083 4: DbRep LogDBRep_Statistic_previous_Year - SQL result: 2020-12-31 14:57:03 Statistic_Yield_Year 9965
2021.09.25 14:43:39.097 5: DbRep LogDBRep_Statistic_previous_Year - BlockingCall finished PID "32497"
2021.09.25 14:43:39.098 5: DbRep LogDBRep_Statistic_previous_Year - SQL result decoded: TIMESTAMP|READING|VALUE\u00a72020-12-31 14:57:03|Statistic_EnergyFeedInGrid_Year|5200\u00a72020-12-31 14:57:03|Statistic_EnergyHomeBat_Year|1435\u00a72020-12-31 14:57:03|Statistic_EnergyHomeGrid_Year|2386\u00a72020-12-31 14:57:03|Statistic_EnergyHomePvSum_Year|4765\u00a72020-12-31 14:57:03|Statistic_EnergyHomePv_Year|3330\u00a72020-12-31 14:57:03|Statistic_EnergyHome_Year|7149\u00a72020-12-31 14:57:03|Statistic_TotalConsumption_Year|7152\u00a72020-12-31 14:57:03|Statistic_Yield_NoBat_Year|8530\u00a72020-12-31 14:57:03|Statistic_Yield_Year|9965


[DS_Starter]

Ich habe mal in den Code geschaut, wäre es denkbar die Funktion DbRep_createCommonSql() zu verwenden, om den §reading§ Syntax zu übertragen?

Ja, wäre evtl. möglich. Muß ich aber noch überlegen ob man sich damit keine Baustelle aufmacht.

Da §reading§ von der Länge limitiert ist kann ich es dann so leider nicht verwenden. Ich wollte es natürlich dem Syntax vom Attribut reading angepasst nutzen.

Ich habe die Bescnränkung der Zeichenanzahl entfernt. Das stammte noch aus der Zeit als man in device, reading nur einen einzigen Wert eintragen konnte. Hat sich inzwischen überlebt.
Ist ins contrib geladen.

[ch.eick]

Ja, wäre evtl. möglich. Muß ich aber noch überlegen ob man sich damit keine Baustelle aufmacht.
Ich habe die Bescnränkung der Zeichenanzahl entfernt. Das stammte noch aus der Zeit als man in device, reading nur einen einzigen Wert eintragen konnte. Hat sich inzwischen überlebt.
Ist ins contrib geladen.

Okay,
die Grundlagenstudie habe ich jetzt bei mir lokal eingebaut und es läuft soweit. Lass Dir ruhig Zeit darüber nachzudenken, ich bin nächste Woche mal kurz weg :-)

Vielen Dank, das Du für meine Ideen immer ein offenes Ohr hast
     Christian

[RalfRog]

Hallo ch.eick & Muenzi

Da ihr euch aktuell mit der Datenbank und auch Datenreduzierung beschäftigt habt, mal ne Frage:
Habt ihr schonmal festgstellt, dass die Datenreduzierung am Ende des angegebenen Zeitintervalls nicht ganz sauber arbeitet (oder bin ich allein bzw. mache ich was falsch).

Code: [Auswählen]

z.B.
reduceLogNbl 90 average=day include=<device>:<reading>aufgeworfen hier https://forum.fhem.de/index.php/topic,121043.0.html
und https://forum.fhem.de/index.php/topic,53584.1425.html

Betrifft bei mit sowohl das Kommando "reducelogNBL" im DB-Device als auch "reducelog" über ein DBRep-Device


Gruß Ralf

[ch.eick]

Hallo Ralf,
das nutze ich nicht. Ich schreib mir meist selber schnell ein SQL und sammle nir sehr sparsam Daten.
VG
   Christian