Datenlogging mit PostgreSQL

Wenn Interesse besteht, würde ich hier mal ein kleines Tutorial schreiben wie man eine Datenbank anlegt, Daten hineinschreibt und wieder ausliest und weiterverarbeiten kann.
Das ganze auf Basis der PostgreSQL.

Der ein oder andere traut sich doch nicht an eine Datenbank, weil er glaubt, es wäre Hexerei. Ich bin in diesen Sachen auch eher Anfänger aber wenn man sich mal damit befasst, ist es gar nicht so schwer. :slight_smile:

Das ganze soll auch nicht komplex werden, sondern einfach gehalten werden so dass am Ende jeder der Lust hat sich diese selbst erweitern kann.

Hallo Rainer,
das ist eine gute Idee, hatte auch schon einige male überlegt eine DB einzubinden,
hatte aber für die Basic’s (welche nehm ich, wie mach ich Backups, Anbindung mir PHP, …) bis jetzt noch keine Zeit.
lg
Andreas

Das wäre doch mal ne schöne Idee. :loveips:

vor allem ist postgresql eine gute wahl

Als erstes benötigen wir die Datenbank. Diese bekommen wir hier. Die richtige Installerversion 9.0.3-1 auswählen und downloaden. Eine Anleitung zum installieren ist ganz unten auf der Seite zufinden.

Die Datenbank wird für unsere Zwecke ohne jegliche Erweiterung installiert. Den Port merken, wird später noch gebraucht.
Wenn alles geklappt hat, öffnen wir in Windows unter Start-> alle Programme PostgresSQL 9.0 pgAdmin III.
Auf der linken Seite sollte der Objektbrowser zusehen sein, ansonsten unter Ansicht den Haken setzen. Als erstes legen wir eine neue Login-Rolle an. Für unsere Zwecke vergeben wir einen Rollennamen ips, Passwort ips der Rest bleibt wie er ist ->OK. Jetzt legen wir eine Datenbank mit Name ipsgraphenlogging an, Eigentümer ips und OK. Damit wird unsere Datenbank erstellt und sollte im Objektbrowser jetzt unter Datenbanken sichtbar sein.

Um über PHP mit der Datenbank kommunizieren zu können, benötigen wir eine Extension und eine dll Datei. Diese sind im Anhang.

Datei libpq.dll kommt in den IPS Ordner, Extension php_pgsql.dll in den ext Ordner. Dazu den Dienst beenden und die Dateien dorthin kopieren. Nach Dienststart im Log kontrollieren ob sie eingebunden sind. Alternativ mit

<? phpinfo();?>

Jetzt kann es losgehen. In IPS legen wir uns eine Kategorie Datenbank Graphen an. Darunter ein Script Variablenupdate. In das Script, kopieren wir folgenden Inhalt.

<?
// Verbindungsaufbau und Auswahl der Datenbank
$dbconn = pg_connect("host=192.168.1.45 port=5432 dbname=ipsgraphenlogging user=ips password=ips") or die ('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());

// Tabelle erstellen über ausführen, bitte nur einmal klicken ansonsten hagelt es Fehlermeldungen!
if($IPS_SENDER == "Execute")
{
   $create = "    CREATE TABLE Variablen (
                    ID SERIAL,
                    VARID INTEGER NOT NULL,
                    VARNAME TEXT,
                    VARSUFFIX TEXT,
                    VALUE REAL,
                    TYP INTEGER,
                    UPDATEZEIT TIMESTAMP,
                    PRIMARY KEY(ID)
                    );";
    pg_query($dbconn, $create);
}

// Datenbank mit Werten von Variablen füllen
if($IPS_SENDER == "Variable")
{
   $variablenName = IPS_GetObject($IPS_VARIABLE);
    $variable = IPS_GetVariable($IPS_VARIABLE);
    $varType = $variable['VariableValue']['ValueType'];
    $profil = IPS_GetVariableProfile($variable['VariableCustomProfile']);
    $suffix = $profil['Suffix'];
    setDataSql($IPS_VARIABLE, utf8_encode($variablenName['ObjectName']), utf8_encode($suffix), $IPS_VALUE, $varType, date('Y-m-d H:i'));
}

function setDataSql($varID, $varName, $suffix, $value, $typ, $time)
{
    global $dbconn;
    $setSQL = 'INSERT INTO Variablen(varid, varname, varsuffix, value, typ, updatezeit) VALUES ';
    $setSQL .= "('".$varID."','".$varName."','".$suffix."','".$value."','".$typ."','".$time."');";
    pg_query($dbconn, $setSQL);
}

// Verbindung schliessen
pg_close($dbconn);
?>

Hier brauchen wir jetzt wieder die Portnummer welche wir uns merken sollten(hoffentlich aufgeschrieben, sonst nachsehen). In $dbconn unter Port eintragen falls different und die IP-Adresse anpassen. Nun klicken wir einmal auf Ausführen, jetzt wird unsere erste Tabelle in der Datenbank erstellt. Zur Kontrolle sehen wir im pgAdmin nach(einmal auf den rot-grünen Kreis klicken) und unter unserer Datenbank sollte eine Tabelle vorhanden sein.(Tabellen(1)).
Sooft auf + klicken bis unsere Spalten erscheinen.
Ab sofort können wir die Tabelle mit Daten füllen. Dazu erstellen wir im Script ein Ereignis(Ausgelöstes Ereignis) wählen eine Variable aus, welche wir loggen wollen und wählen Bei Variablenänderung.

Ab jetzt wird bei jeder Variablenänderung der Variable diese mit ID, Namen, Wert, Typ, Suffix und Zeit in die Datenbank geschrieben.

Zur Kontrolle in pgAdmin auf variablen->rechte Maustaste->Daten anzeigen->Alle Zeilen zeigen. Es sollte eine Darstellung wie im Anhang ersichtlich sein. Bei entsprechenden Daten.

sql.rar (182 KB)

Alles schön und gut wenn ich sie jetzt in die Datenbank verbannt habe, ich will aber was mit meinen Daten machen. Also müssen sie wieder in lesbarer Form ausgelesen werden. Dazu gibt es zig Möglichkeiten, wir beschränken uns hiermal auf 3.

Auslesen einer einzelnen Variable, mehrere Variablen über Tage oder einem Zeitraum zwischen 2 Zeitangaben.

Dafür dient uns diese Script

<?
// Verbindungsaufbau und Auswahl der Datenbank
$dbconn = pg_connect("host=192.168.1.45 port=5432 dbname=ipsgraphenlogging user=ips password=ips") or die ('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());

$varID = array(35017, 46552); // IDs der zu loggenden Variablen
$tage = 2; // Auswahl Tage
$startZeit = date("d.m.Y H:i", time()); // Zeitangabe von
$endZeit = date("d.m.Y H:i", time()-3600); // Zeitangabe bis
echo $startZeit." - ".$endZeit."
\r"; // nur zur Info

// Eine SQL-Abfrage ausführen für eine Variable
//$query = 'SELECT * FROM Variablen WHERE varid = 35017';
//$result = pg_query($query) or die ('Abfrage fehlgeschlagen: ' . pg_last_error());

// Eine SQL-Abfrage ausführen für mehrere Variable mit Zeitangabe
for($i = 0; $i < count($varID); $i++)
{
    if($tage == 0)
    {
        $query = "SELECT value, to_char(updatezeit, 'dd.mm.yyyy hh24:mi'), varsuffix FROM variablen WHERE updatezeit BETWEEN "."'$endZeit'"." and " ."'$startZeit'". "and varid =".$varID[$i];
    }
    else
    {
       $query = "SELECT value, to_char(updatezeit, 'dd.mm.yyyy hh24:mi'), varsuffix FROM variablen WHERE current_date -".$tage." < updatezeit  and varid =".$varID[$i];
    }
    $result = pg_query($query) or die ('Abfrage fehlgeschlagen: ' .pg_last_error());
    
    // Ergebnis ausgeben
    while($data = pg_fetch_assoc($result))
    {
        //print_r($data);
        echo $data['to_char']." ".$data['value'].utf8_decode($data['varsuffix'])."
";
    }
}

// Verbindung schliessen
pg_close($dbconn);
?>

damit können wir jetzt rumspielen und freuen uns über unsere erste Datenbank.
Die Daten könnten so weiter verarbeitet werden, z.B zum Graph erzeugen.

Danke für deine Arbeit.
Mal eine Frage: Ich höre zum ersten Mal von dieser postgresql-Datenbank.
Ist die besser als mysql oder SQLite ? Oder hat dies andere Gründe?

Hallo Rainer,

das ist sicher eine Glaubensfrage wie IE und FF mit den Konformitäten. :wink:

Alles andere hatten wir ja schon, warum also nicht mal was neues. Sonst hab ich damit keine Hintergedanken.

Wer zu faul ist seine ganzen geloggten Variablen per Hand im Script einzutragen, kann von mir ein Script dafür bekommen. Veröffentlichen werde ich es nicht, da man mit Bedacht dran gehen muss.

Hallo
Ich finde es echt toll dass Ihr euch die Mühe macht eine Datenbank zu erklären.
Leider klappt es bei mir noch nicht wie es soll.
Ich habe eigentlich alles gemacht wie beschrieben.

Nur wo soll ich dies ausführen und was muss ich dann für Dateien finden?
<? phpinfo();?>

Ich habe mal das Skript so eingefügt wie beschrieben und die Datenbank installiert und konfiguriert. Ich weiß leider nicht welche IP ich angeben soll .
Wie Ihr auf dem Bild erkennen könnt habe ich es mit der vom Rechner versucht.
Bitte um Hilfe Fränki

Ich weiß leider nicht welche IP ich angeben soll .

Die IP des Rechners wo die Datenbank drauf läuft.

Die beiden Dateien musst Du dort hin kopieren wie angegeben.!

Hallo Rainer
Das ich es als Laie geschafft habe, alles so funktioniert wie du es beschrieben hast grenzt an ein Wunder oder besser gesagt Du hast deine Aufge ganz toll erledigt.
Danke für deine Mühe.
Gruß Fränki

P.s. Kannst du mir das Skript für die Variabelübernahme zusenden

Hallo
Mit der Gefahr dass ich gleich fertig gemacht werde da ich eine Frage stelle die jeder eigentlich beantworten kann.

Ist es problemlos möglich das Skript auf eine andere Datenbank umzuschreiben? Ich habe leider keine Ahnung von diesen Dingen und bin auf andere angewiesen.
Ich habe ein Ziel dass meine Investition irgend wann mal so fruchtet dass ich zufrieden bin.

Ich hoffe der Spruch gilt auch hier dass es keine dumme Fragen gibt sondern nur dumme Antworten.
Mein Wunsch wäre es dass es mal so aussieht wie in diesem Projekt.
http://www.ip-symcon.de/forum/f16/verbrauchsdaten-visualisieren-ipsymcon-6354/

Danke mit der Bitte um Hilfe
Fränki

script-3.txt (944 Bytes)

Hallo Fränki,

sicher ist das möglich. Du musst nur die entsprechende Tabelle in deiner Datenbank anlegen. Das Script was Du angehängt hast, schreibt nur die Daten in die Tabelle.

Hallo
Danke für deine Antwort.
Ich habe eine Datenbank angelegt und versuche nun eine Tabelle mit CID , Float, anzulegen.
Leider finde ich bei Dateityp meiner Tabellenspalte den Wert " FLOAT" nicht.
Heißt dieser dort anders?
Danke Fränki

Du könntest z.B. REAL verwenden wenn Du bei Postgre bist.

Rainer, diese Anleitung ist „AMAZING“! In kurzer Zeit konnte ich endlich mein SQL-Logging aktivieren! Danke!!!

Nun frage ich mich, was ich mit den alten Beständen aus logging.db machen soll. Da Du praktisch an alles gedacht hast, kann ich nicht der Versuchung widerstehen und fragen: „Hast Du zufällig auch noch einen Script, der durch die alten Datenbestände von logging.db loopt, die geloggten Variablen holt, und nach Postgres migriert???“ Vielen Dank im Voraus für Deine geschätzte Hilfe!

Dafür hab ich kein Script. Das wird auch mit einem Script nicht so einfach gehen. Je nachdem wie deine Struktur ist wird es ohne Handarbeit warscheinlich nicht gehen.
Diese Arbeit hab ich mir nicht gemacht, war damals alles Neuanlage.

Danke trotzdem. Wenn ich mir die Möglichkeiten von Postgres anschaue, so denke ich, dass sich die Migration auf jeden Fall lohnt!