SQL sendTo - Problem storeState vs update

[BananaJoe]

Ich nutze sendTo um historische Werte in die Datenbank zu schreiben:

sendTo("sql.0", "storeState", {
    "id": myId,
    "state": { 
        "val": myVAL, 
        "ts": myTS,
        "ack": true
    }
}, async function (result) {
    //console.log(result);
});

Bisher habe ich immer, bevor ich das gemacht habe, vorher alle Datenpunkte gelöscht:

// Alles löschen
sendTo("sql.0", "deleteAll", {
    "id": myIdDeleteAll,
}, async function (result) {
      //console.log(result);
});

Nun würde ich dei Werte aber gerne fortschreiben, dabei kann es sein das alles Werte schon vorhanden sind, kann auch sein das neue hinzukommen. Wenn ich nun weiter storeState nehme bekomme ich Fehlermeldungen:

2022-06-06 12:45:08.881  - error: sql.0 (1910630) Cannot insert INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (104, 1654631220000, 0, 1, 0, 0);: Error: Duplicate entry '104-1654631220000' for key 'PRIMARY' (id: 0_userdata.0.Solaranlage.pvforecasts.Forecast_solar.600W)

Ok, dafür müsste ich update nehmen:

sendTo("sql.0", "update", {
    "id": myId,
    "state": { 
        "val": myVAL, 
        "ts": myTS,
        "ack": true
    }
}, async function (result) {
    //console.log(result);
});

update funktioniert aber nur wenn es den den Eintrag schon gibt. Wenn es den nicht gibt verschwindet der Wert im Nirwana.
Ich habe in der Hilfe und im Forum nichts gefunden (oder verwende die falschen Suchbegriffe):

Gibt es eine Möglichkeit aka "Erstelle wenn noch nicht vorhanden, ansonsten aktualisiere"?
Oder müsste ich für jeden Wert vorher prüfen ob es diesen gibt und dann die entsprechende Methode nehmen?

[fastfoot]

@bananajoe du könntest sendTo("sql.0", "query", sql_statement.....) verwenden und da dein eigenes SQL verwenden. Für MariaDB gibt es das REPLACE INTO TABLE statement oder auch das INSERT ON DUPLICATE KEY UPDATE statement.

Wenn das gut funktioniert könnte man auch überlegen ob man storeState evtl. damit ersetzen kann, sprich ein enhancement issue erstellt

[BananaJoe]

@fastfoot Dann müsste ich mich selbst darum kümmern erst die Zuordnung den Tabellen datapoints und ts_numbers zu holen ... mhh, steht ja auch in einem der Beispiele zum Adapter.

Ich habe mich gerade an einer Prüfung versucht ob es den Wert denn schon gibt.
getHistory will mir eigentlich einen Zeitbereich zurück geben, ich will aber ja genau einen Wert dessen Zeitstempel ich kenne .. und scheitere gerade auch daran (bekomme immer leer Abfrage zurück).

Da hab ich noch einen Moment zu tun ...

[fastfoot]

@bananajoe nichts ist unmöglich
REPLACE INTO ts_number (id,ts,val,ack,_from,q) VALUES ((SELECT id FROM datapoints WHERE NAME = "tankerkoenig.0.stations.5.e5.feed"), 1654516496102, 1.029, 1, 1, 0) funktioniert

[fastfoot]

@bananajoe sagte in SQL sendTo - Problem storeState vs update:

Ich habe mich gerade an einer Prüfung versucht ob es den Wert denn schon gibt.

hier mal ein fertiges Beispiel, wozu prüfen wenn die DB das auch kann?

let id = 'tankerkoenig.0.stations.5.e5.feed';
let ts = 1654516496145;
let value = 0.029;
let ack=1;
let _from=1;
let q=0;
let statement = `
    REPLACE INTO iobroker.ts_number 
        (id, ts, val, ack, _from, q) 
        VALUES ((SELECT id FROM iobroker.datapoints WHERE name = "${id}"), ${ts}, ${value}, ${ack}, ${_from}, ${q})
`
//log(statement)
sendTo("sql.0", "query", statement, e => {
    if (e && e.error) log(e.error)
    else log("Done!")
})

[BananaJoe]

@fastfoot mhh, dein Beispiel versucht den Wert zu aktualisieren, wenn das klappt kommt der else log("Done!") Zweig, ansonsten der if (e && e.error) log(e.error) ?

Wenn es einen Fehler gibt müsste ich dann also noch mal mit storeState nachsetzen?

Mein Problem ist vermutlich auch eher der Natur das ich das in Blockly in einer JS Funktion nutzen möchte und - wieder einmal - Probleme hatte die Variablen so ins Statement einzubauen das er es ohne meckern annimmt.

Dein Weg wäre natürlich viel eleganter, schon mal Danke für die Mühe.

[fastfoot]

@bananajoe sagte in SQL sendTo - Problem storeState vs update:

Wenn es einen Fehler gibt müsste ich dann also noch mal mit storeState nachsetzen?

nein! wenn es die id zB nicht gäbe dann wird dieser Fehler geloggt. Ansonsten kümmert sich das REPLACE INTO um doppelte Werte, d.h. wenn der Wert existiert (der primary key oder unique key) dann wird zuerst gelöscht und dann erfolgt das Insert, wenn nichts existiert erfolgt ein normales Update.

Probier doch einfach mal

[fastfoot]

@bananajoe hier als Blockly

Spoiler

<xml xmlns="https://developers.google.com/blockly/xml">
 <variables>
   <variable id="RtPM2%k{F`%!q{Rk]|a.">id</variable>
   <variable id=";!)T*gf?`G!J~iZ#4,CG">ts</variable>
   <variable id="epT$}5hcYx3b^`jz:~#d">value</variable>
   <variable id="GAGtL,+-C3?x`s;u1nsR">ack</variable>
   <variable id="rHJp6FCFj,U],$ysF[xX">from</variable>
   <variable id="p~yp5Fejp5Y|*K$i[(u%">q</variable>
 </variables>
 <block type="procedures_defcustomnoreturn" id="ppt|ajrQncolYtqyk1q1" x="62" y="38">
   <mutation statements="false">
     <arg name="id" varid="RtPM2%k{F`%!q{Rk]|a."></arg>
     <arg name="ts" varid=";!)T*gf?`G!J~iZ#4,CG"></arg>
     <arg name="value" varid="epT$}5hcYx3b^`jz:~#d"></arg>
     <arg name="ack" varid="GAGtL,+-C3?x`s;u1nsR"></arg>
     <arg name="from" varid="rHJp6FCFj,U],$ysF[xX"></arg>
     <arg name="q" varid="p~yp5Fejp5Y|*K$i[(u%"></arg>
   </mutation>
   <field name="NAME">InsertToHistory</field>
   <field name="SCRIPT">bGV0IHN0YXRlbWVudCA9IGANCiAgICBSRVBMQUNFIElOVE8gaW9icm9rZXIudHNfbnVtYmVyIA0KICAgICAgICAoaWQsIHRzLCB2YWwsIGFjaywgX2Zyb20sIHEpIA0KICAgICAgICBWQUxVRVMgKChTRUxFQ1QgaWQgRlJPTSBpb2Jyb2tlci5kYXRhcG9pbnRzIFdIRVJFIG5hbWUgPSAiJHtpZH0iKSwgJHt0c30sICR7dmFsdWV9LCAke2Fja30sICR7ZnJvbX0sICR7cX0pDQpgDQovL2xvZyhzdGF0ZW1lbnQpDQpzZW5kVG8oInNxbC4wIiwgInF1ZXJ5Iiwgc3RhdGVtZW50LCBlID0+IHsNCiAgICBpZiAoZSAmJiBlLmVycm9yKSBsb2coZS5lcnJvcikNCiAgICBlbHNlIGxvZygiRG9uZSEiKQ0KfSk=</field>
   <comment pinned="false" h="80" w="160">Beschreibe diese Funktion …</comment>
 </block>
 <block type="procedures_callcustomnoreturn" id="JoDo6nn!){b@{QRXrB[u" inline="false" x="63" y="62">
   <mutation name="InsertToHistory">
     <arg name="id"></arg>
     <arg name="ts"></arg>
     <arg name="value"></arg>
     <arg name="ack"></arg>
     <arg name="from"></arg>
     <arg name="q"></arg>
   </mutation>
   <value name="ARG0">
     <block type="text" id="%p#q#1)8i%dSRk5DL07l">
       <field name="TEXT">tankerkoenig.0.stations.5.e5.feed</field>
     </block>
   </value>
   <value name="ARG1">
     <block type="math_number" id=":/78vBD}_o2?@:S(I?ZZ">
       <field name="NUM">1654516496145</field>
     </block>
   </value>
   <value name="ARG2">
     <block type="math_number" id="c5[*,G)#wa(~pNU76x1H">
       <field name="NUM">55.89</field>
     </block>
   </value>
   <value name="ARG3">
     <block type="math_number" id="20UH^hgYc(x[ff8AFLg=">
       <field name="NUM">1</field>
     </block>
   </value>
   <value name="ARG4">
     <block type="math_number" id=";^6heBy3kPnz_;P0++$P">
       <field name="NUM">1</field>
     </block>
   </value>
   <value name="ARG5">
     <block type="math_number" id="eWLzW-vnc1F%*khHrZb{">
       <field name="NUM">0</field>
     </block>
   </value>
 </block>
</xml>

[BananaJoe]

@fastfoot Funktioniert 1A! Kein gemecker mehr im Log und in mysql sehe ich wie die Werte aktualisiert werden.

Ich will nämlich neben Forecast.solar auch Solcast abfragen - die senden aber immer nur eine Vorhersage ab dem Zeitpunkt der Frage. Sieht in der Tagesgrafik von 0:00 bis 23:59 Uhr aber dann natürlich schlecht aus.

Abgesehen das ich auch so bisher in den Diagrammen nicht zurückgehen konnte bzw. dann halt die Vorhersage fehlte.

Besten Dank, ich vermute das kann ich noch an vielen anderen Stellen brauchen.

[fastfoot]

@bananajoe sagte in SQL sendTo - Problem storeState vs update:

Besten Dank...

immer gerne