Script oder Blockly um Daten in die MySQL Datenbank zu schreiben

[apollon77]

Warte auf die neuesten Version des SQL-Adapters" …

Ich habe da eine neue Funktion eingebaut um trotz "log changes only" in regelmäßigen abständen den Werte nochmal reinzuschreiben. Genau aus solchen Gründen ...

Wie oft wird denn der Wert um den es geht aktualisiert? Zwischendrin auch wenn der Wert gleich bleibt ?

Versuchs mal damit ... ansonsten hatte ich noch die Idee das zu erweitern das das auch für "sehr selten aktualisierte Werte" geht, das ist aber noch nicht eingebaut

[Jeeper.at]

Alles klar, ich warte.

Die Werte werden auch schon mal über längere Zeit nicht aktualisiert. Beispiel: Eine Variable zeigt an wann das IPAD geladen wird. Spiele ich nicht oft damit rum, dann wird es nur einmal in der Woche geladen (Und damit ändert sich der Wert auch nur einmal in der Woche. OK 2 Mal. Von False zu True und ein paar Stunden später umgekehrt. Aber dann eben lange nicht mehr.)

Extremstes Bespiel ist wohl die CCU-Reboot Variable. Die kann auch schon mal Monate unverändert sein. Obwohl man natürlich über die Sinnhaftigkeit der Visualisierung dieser Variable streiten kann.

Aber das sich ein paar Tage nichts ändert kann schon mal vorkommen.

Viele Dank für deine Bemühungen

Günther

[apollon77]

Dachte ich mir … ich erweitere das mal heute Abend, teste es bei meiner InfluxDB und gebe es dann auch für SQL und History weiter als Feature

[apollon77]

Da ich gerade keinen SQL-Adapter am kaufen habe (und auch keine HW frei hab spontan) bräuchte ich von den SQL-Nutzern mal 3 Queries:

1.) Liste aller in der DB gespeicherten IDs (also hm-rpc.1.<seriennnummer>.<channel>.<state>)

2.) Datum des ältesten Eintrags zu einer ID (am Ende also etwas wie "select min(timestamp) …")

3.) (falls das überhaupt geht) Liste der Tage in der DB wo Log-Daten vorhanden sind (also etwas wie "... group by "year+month+day")

Ich habe mich mit der DB-Struktur die vom SQL-Adapter aufgebaut wird noch nicht tief beschäftigt und wenn jemand das getan hat hilft es hier wenn ich die Queries bekommen kann

Wenn ich das habe kann ich das gleiche was ich für die Influx gerade am testen bin auch für SQL bereitstellen.</state></channel></seriennnummer>

[Jeeper.at]

Hallo,

ich versuche gerade dir die Daten die du brauchst zu liefern.

Leider komme ich nicht weiter.

Habe folgendes Script von GIT genommen und erweitert.

var kaffee = "hm-rega.0.2463"/*SV_Kaffeemaschine*/;

on(kaffee, function(co) {
    if (co.state.val === true)
    {
    log('start_vorher');
sendTo('SQL.0', 'query', 'SELECT * FROM datapoints', function (result) {
    log('start_nachher');
    log(result.error);
    if (result.error) {
        console.error(result.error);
    } else {
        // show result
        log('show Result');
         console.log('Rows: ' + JSON.stringify(result.result));
    }
})}});

Leider kommt er nur zum ersten log (start_vorher). Zum 2. kommt er nicht. Geändert habe ich nur im sendTo Befehl das "SQL.0", das war vorher klein geschrieben. Da kommt er aber mit einem Fehler, das er die Datenbank nicht findet.

LG

Günther

[apollon77]

Was hast du für eine DB dahinter? Gibt das iobroker-Log irgendwas her?

[Jeeper.at]

Hallo,

meine Datenbank ist eine MySql Datenbank.

Das Log gibt nichts her.

Steht nur folgendes drinnen (Debug Level)

javascript.0
2016-11-03 18:40:54.447
debug
sendTo "query" to system.adapter.SQL.0 from system.adapter.javascript.0: SELECT * FROM datapoints 
javascript.0
2016-11-03 18:40:54.447
info
script.js.eigene_Skripte.sql_test: start_vorher 
inMem
2016-11-03 18:40:54.447
debug
message * javascript.0.sql_test val=true, ack=false, ts=1478194854448, q=0, from=system.adapter.web.0, lc=1478194854448 

Lg

Günther

[apollon77]

Versuch mal iobroker.datapoints als tabellenname

[Thisoft]

@jeeper.at: Dass der Block hinter der Query nicht ausgeführt wird ist wohl "normal". Ich kann's auch nicht wirklich fundiert erklären, aber es hat was mit der asynchronen Abarbeitung in Javascript zu tun. Dadurch läuft der Code bereits weiter während die Query noch kein Ergebnis geliefert hat. Man muss mit dem Ergebnis eine separate Funktion aufrufen. Die wird erst dann ausgeführt wenn das Ergebnis da ist.

@Apollon: Ich hatte ja vor einiger Zeit mal ein Script angefangen was Daten von History nach SQL-Db konvertieren sollte. Das ist wieder etwas in Vergessenheit geraten. Ich hänge dir das mal hier an. Da müssten die von Dir angesprochenen Queries mehr oder weniger drin sein. Zumindest sollte das schon mal ein wenig die Db-Struktur verdeutlichen. Ich habe allerdings einen MS-SQL-Server im Einsatz. Ich glaube aber nicht dass die Db-Struktur in MySQL anders ist lediglich die Syntax bei komplexeren Queries kann manchmal etwas abweichen.

! ```
var Testlauf = true; //bei true wird nichts in die DB geschrieben - nur geloggt var HistoryInstanz = 'history.0'; var sqlInstanz = 'sql.0'; var dpID = "0"; ! var SelectorALL = $('channel[state.id=*mqtt*]'); SelectorALL.each(function (id, i) { log(id); if (!id.match('system\..')){ log(id); var Zieltabelle = 'ts_string'; var dType = 0; var HistoryEnabled = false; var dPoint = getObject(id,"true"); if (dPoint && dPoint.common) { var settings = dPoint.common.history || dPoint.common.custom; if (settings) { HistoryEnabled = settings[HistoryInstanz] ? settings[HistoryInstanz].enabled : false; } } if (HistoryEnabled) { log("Datenpunkt '" + id + "' wird exportiert"); //ToSQL(id); } } }); ! function ToSQL(aktID){ sendTo(HistoryInstanz, 'getHistory', {id: aktID,options: {aggregate: 'none'}}, function (result1) { if (result1 && result1.result.length>0) { console.log(result1.result.length + ' Datensätze aus History gelesen'); sendTo(sqlInstanz, 'query', 'SELECT * FROM datapoints WHERE name = \'' + aktID + '\'', function (result2) { if (result2 && result2.result.length>0){ dpID = result2.result[0].id; dType =result2.result[0].type; log("ID " + dpID + " in SQL-Db bereits vorhanden"); } else { sendTo(sqlInstanz, 'query', 'SELECT TOP 1 id FROM datapoints ORDER BY id DESC', function (result3) { dpID = result3.result[0].id+1; dType = 1; if (typeof result1.result[0].val == 'boolean'){dType = 2} if (typeof result1.result[0].val == 'number'){dType = 0} var InsertString = ''; InsertString = InsertString.concat(dpID,',',aktID,',',dType); log('SQL-Anweisung: ' + 'INSERT INTO datapoints VALUES(' + InsertString + ')'); if (!Testlauf){sendTo(sqlInstanz, 'query', 'INSERT INTO datapoints VALUES('+ InsertString + ')')} }); } if (dType == 2) {Zieltabelle = 'ts_bool'} if (dType === 0) {Zieltabelle = 'ts_number'} var DSimportiert = 0; for (var i = 0; i < result1.result.length; i++) { var InsertString = ''; var Wert =result1.result[i].val; if (CheckDuplicate(dpID,result1.result[i].ts) === 0){ if (Zieltabelle != 'ts_string') {Wert = Number(result1.result[i].val)} InsertString = InsertString.concat(dpID,',',result1.result[i].ts,',',Wert,',',Number(result1.result[i].ack),',9,',result1.result[i].q); DSimportiert += 1; //log('SQL-Anweisung: ' + 'INSERT INTO ' + Zieltabelle + ' VALUES(' + InsertString + ')'); if (!Testlauf){sendTo(sqlInstanz, 'query', 'INSERT INTO ' + Zieltabelle + ' VALUES(' + InsertString + ')')} } //else {log("Datensatz zum DP '" + aktID + " mit Timestamp " + result1.result[i].ts + " bereits in Datenbank vorhanden!")} } log(DSimportiert + ' Datensätze in SQL-Db geschrieben.'); }); } }); } ! function CheckDuplicate(id, ts){ sendTo(sqlInstanz, 'query', 'SELECT Count(*) As Anzahl FROM ' + Zieltabelle + ' WHERE id = ' + id + ' And ts = ' + ts, function (result3) { return result3.result[0].Anzahl; }); }
! Du kannst mir auch gerne den vorhandenen Stand des angepassten Adapters zur Verfügung stellen. Ich teste dann mal auf SQL-Server und kann auch kleinere Fehlerchen gleich korrigieren.
! Gruß Thilo[/i][/i][/i][/i][/i][/i][/i]

[Jeeper.at]

Und ich habe gerade direkt auf der Datenbank einen Dump gemacht. Wenn die Daten noch gebraucht werden, dann kann ich sie Euch zukommen lassen.

LG

Günther

[apollon77]

Aaaaaaalso Offtopic …

SQL-Adapter: Damit das Importer-Skript funktioniert braucht es mindestens den letzten SQL-Stand vom GitHub (1.3.0). Da ist die "storeState" Methode drin.

History-Adapter: Der aktuellste Stand inkl. README.md Doku ist in jedem Fall auf meinem Github Fork (https://github.com/Apollon77/ioBroker.history), es liegt offiziell gerade als Pull-Request bei Bluefox. AM Ende reichen aber die Skripte im Verzeichnis "converter" aus.

Der reine Converter sollte mit der neuen SQL-Version direkt funktionieren, wobei in dem Fall keinerlei Doublettenchecks stattfinden!! Das muss man also selber sicherstellen über den anzugebenden "Starttag".

Damit alles korrekt läuft braucht man noch ein "analyzesql.js" in Anlehnung an "analyzeinflux.js" was Sammlung der existierenden Datensätze übernimmt sodass das Skript weiss was es nicht übertragen soll.

Dafür bräuchte man die queries ... wie oben geschrieben

Feedback und Meinungen jederzeit gern

Ingo F

[Thisoft]

Hallo Ingo,

jetzt bin ich irgendwie verwirrt :?

Es ging doch hier um die Funktion dass quasi "Leerzeiten" mit periodischen Einträgen aufgefüllt werden um "Löcher" in Flot-Charts zu vermeiden - oder?

Das Thema mit dem Import- bzw. Converterscript war ja eigentlich andere Baustelle. Aber wennschon, wo gibt's denn jetzt Dein aktuelles Script? Außerdem, in dem Script was ich oben eingefügt habe ist ja schon die Zuordnung auf bereits vorhandene ID's, Ermittlung des Starttages und Dublettenprüfung drin. Wollen wir das vielleicht zusammenbringen und "Rund" machen?

Gruß Thilo

[apollon77]

Aaah dann hatte ich dich missverstanden

Ja in dem Thread geht es um was anderes: Das "regelmäßig schreiben" hab ich jetzt funktionierend für den Influxadapter und gerade bei mir im "Test-Livebetrieb". Wenn das alles passt dann baue ich es heute Abend analog in History und SQL ein Das macht alles "ab neustart" aber nicht in die Vergangenheit!

Das hat auch nichts spezielles weil es die vorhandenen Funktionen benutzt und über spezielle Timeouts den letzten State einfach nochmal schreibt

[apollon77]

@Thisoft:

Das Thema mit dem Import- bzw. Converterscript war ja eigentlich andere Baustelle. Aber wennschon, wo gibt's denn jetzt Dein aktuelles Script? Außerdem, in dem Script was ich oben eingefügt habe ist ja schon die Zuordnung auf bereits vorhandene ID's, Ermittlung des Starttages und Dublettenprüfung drin. Wollen wir das vielleicht zusammenbringen und "Rund" machen? `

mein Skript siehe Link oben … aktuell noch in meinem Histpry-Adapter Fork

[apollon77]

Regelmäßiges Loggen von Datenpunkten per SQL-Adapter auch für selten aktualisierte Datenpunkte:

https://github.com/Apollon77/ioBroker.s … e9057750ea

Teste es heute abend beim History-Adapter und wenn das klappt gebe ich es Bluefox als Pullrequest damit es im offiziellen Github drin ist.

[Jeeper.at]

Klasse, werde es dann sobald verfügbar gleich testen.

LG

Günther

[Thisoft]

Also - - - @apollon77: Brauchst Du jetzt noch konkrete Queries o.ä. bezüglich SQL-Server oder sollen wir jetzt erstmal auf den geänderten SQL-Adapter warten?

[apollon77]

Also das "relogging" ist im geänderten Adapter drin und dafür brauche ich nichts.

siehe: http://forum.iobroker.net/viewtopic.php?p=39028#p39028

Die Queries bräuchte ich für den History->SQL-Konverter immer noch. Habe bisher an der Stelle noch nichts weiter gemacht …

siehe http://forum.iobroker.net/viewtopic.php?f=36&t=3965

[Thisoft]

Ja, wie gesagt. Das Script für History->SQL Konverter habe ich ja oben schon eingefügt. Das kannst Du gerne verwenden. Da sind auch so Sachen wie Prüfung auf bereits vorhandene ID des DP's und doppelte Datensätze mit drin. Ich kann das Script nur momentan nicht ausgiebig testen da ich History nie wirklich im Einsatz hatte und nur mal paar DP's testweise geloggt habe. Ich hatte das Script gebaut um damit mal von einer SQL-Db zur anderen konvertieren zu können. Das hat sich aber etwas verschoben - ich warte noch auf die Freigabe für schöne, neue Hardware (intel NUC)

[apollon77]

Aaaalso UPDATE!!

Neue Version von SQL-Adapter zum Testen verfügbar. Fügt das regelmäßige schreiben auch ohne Änderung hinzu:

siehe http://forum.iobroker.net/viewtopic.php … 190#p39190

Und zum Thema "Converter History -> SQL" gibt es auch Neuigkeiten:

History-Adapter und SQL-Adapter vom Github nehmen und dann gibt unter https://github.com/ioBroker/ioBroker.history in der Readme unter "Data converter" auch Infos und den aktuellen Stand. Ich habe es rudimentär mit SQLite getestet und klappte soweit ... jetzt wären freiwillige gefragt

siehe http://forum.iobroker.net/viewtopic.php?f=36&t=3965