Ereignisse in Datenbank speichern & abfragen
Manchmal möchte man über die Ereignisse im eigenen Smarthome-System Bescheid wissen. Um dies zu ermöglichen wird im heutigen Tutorial eine neue Tabelle in der Datenbank angelegt, in der die Ereignisse abgespeichert werden. Außerdem wird ein Skript angelegt, um per PHP neue Ereignisse abzuspeichern und eines, um mit Python neue Skripte anzulegen. Damit die Ereignisse später einfach abgefragt werden können, wird die Schnittstelle angepasst.
Dieses Tutorial gibt es auch als Video
Tabelle in Datenbank erstellen
Um die neue Tabelle anzulegen öffnest du zuerst phpLiteAdmin, indem du in deinem Browser die Adresse „http://[IP DEINES PI]/database/phpliteadmin.php“ aufrufst.
Unter dem Punkt „Create new table on database data.sqlite“ trägst du nun bei „Name“ „EVENTS“ und bei „Number of fields“ „3“ ein und klickst auf „Go“.
Im nächsten Fenster wird die Tabelle definiert, wie im folgenden Bild zu sehen.
So wird die Tabelle für die Ereignisse definiert.
Mit einem Klick auf „Create“ schließt du den Vorgang ab.
Tabelle „userdata“ anpassen
Die Tabelle „userdata“ wird jetzt um ein Feld erweitert. In diesem Feld wird bei jedem Nutzer der letzte Zeitpunkt abgespeichert, an dem die vorhandenen Ereignisse abgefragt wurden, damit die Ereignisse gezählt werden können, die seitdem in der Datenbank abgespeichert wurden. Dazu klickst in der Leiste links auf data.sqlite und dann bei der Tabelle „userdata“ auf „Structure“.
So kannst du die Struktur der Tabelle ändern.
Anschließend klickst du bei „Add 1 field at end of table“ auf „Go“ und definierst die Tabelle, wie in folgendem Bild zu sehen.
Das Feld „EVENTS_LAST_CHECKED“ ist vom Typ Integer.
Mit einem Klick auf „Add fields“ wird das Feld der Tabelle hinzugefügt. Du kannst phpLiteAdmin jetzt schließen.
Ereignisse abfragen und erstellen
Als erstes startest du FileZilla und verbindest dich mitdeinem Server. Dann erstellst du im Serverhauptverzeichnis die Datei „events.php“ und öffnest sie. Anschließend fügst du den folgenden Quellcode ein:
<?php
function getEvents($username, $type, $limit, $offset, $db){
//Ereignisse laden
if($type !== ""){
$results = $db->prepare("SELECT * FROM 'EVENTS' WHERE TYPE == :type ORDER BY TIMESTAMP DESC LIMIT :limit OFFSET :offset");
$results->execute(array('type' => $type, 'limit' => $limit, 'offset' => $offset));
}
else{
$results = $db->prepare("SELECT * FROM 'EVENTS' ORDER BY TIMESTAMP DESC LIMIT :limit OFFSET :offset");
$results->execute(array('limit' => $limit, 'offset' => $offset));
}
$events = array();
//JSON-Objekt erstellen und füllen
foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
$event_item = array('text' => $row['TEXT'], 'time' => $row['TIMESTAMP'], 'type' => $row['TYPE']);
array_push($events, $event_item);
}
//EVENTS_LAST_CHECKED für $username mit aktueller Zeit aktualisieren
$query = $db->prepare("UPDATE 'userdata' SET 'EVENTS_LAST_CHECKED' = :time WHERE USERNAME == :username");
$query->execute(array('time' => time(), 'username' => $username));
//JSON-Objekt ausgeben
return json_encode(array('events' => $events));
}
function getUnseenEvents($username, $db){
//EVENTS_LAST_CHECKED für Nutzer $username abfragen
$query = $db->prepare("SELECT EVENTS_LAST_CHECKED FROM 'userdata' WHERE USERNAME == :username");
$query->execute(array('username' => $username));
$lastChecked = $query->fetch(PDO::FETCH_ASSOC)['EVENTS_LAST_CHECKED'];
if($lastChecked != false){
//Ereignisse zählen
$results = $db->prepare("SELECT COUNT(*) FROM 'EVENTS' WHERE TIMESTAMP > :time");
$results->execute(array('time' => $lastChecked));
foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
return $row['COUNT(*)'];
}
}
else{
return "usernotfound";
}
}
function getEventTypes($db){
//Eventtypen laden
$results = $db->prepare("SELECT DISTINCT TYPE FROM EVENTS ORDER BY TYPE");
$results->execute();
$types = array();
//JSON-Objekt erstellen und füllen
foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
array_push($types, $row['TYPE']);
}
//JSON-Objekt ausgeben
return json_encode(array('types' => $types));
}
function addEvent($type, $text, $db){
//Ereignis in Datenbank schreiben
$statement = $db->prepare("INSERT INTO EVENTS (TYPE, TEXT, TIMESTAMP) VALUES (?,?,?)");
$statement->execute(array($type, $text, time()));
return "eventadded";
}
?>
Erklärung des Codes
In der Datei sind die folgenden 4 Methoden enthalten:
- getEvents($username, $type, $limit, $offset, $db) => Gibt so viele Ereignisse vom Typ $type aus (gibt alle aus, wenn $type nicht definiert), wie in $limit definiert, und beginnt mit dem Zählen bei $offset. Aktualisiert anschließend den Abrufzeitpunkt des Nutzers $username
- getUnseenEvents($username, $db) => Gibt die Anzahl der Ereignisse aus, die nach dem Zeittempel letzten Abrufen durch den Nutzer $username hinzugefügt wurden
- getEventTypes($db) => Gibt alle vorhandenen Ereignis-Typen aus, die in der Datenbank vorhanden sind
- addEvent($type, $text, $db) => Fügt ein Ereignis mit dem Typ $type und dem Text $text mit der aktuellen Zeit der Datenbank hinzu
Schnittstelle anpassen
Als nächstes wird die Schnittstelle angepasst. Dazu öffnest du die Datei „api.php“ und fügst die entsprechenden Zeilen hinzu (Zeile 18 & 19, sowie die Zeilen 34 – 46):
<?php
//Datenbankverbindung herstellen
$SQLITEdb = "database/data.sqlite";
$db = new PDO("sqlite:".$SQLITEdb);
//Funksteckdosen
include "getModes.php";
include "setModes.php";
//Andere
include "getRooms.php";
include "getSensorData.php";
include "getSystemInfo.php";
include "getRoomData.php";
include "getGraphData.php";
//Diese Zeile wurde hinzugefügt
include "events.php";
//Szenen
include "scenes/getScenes.php";
include "scenes/runScene.php";
include "scenes/createScene.php";
$validUser = validateUser($_POST['username'], $_POST['password'], $db);
if($validUser){
switch($_POST['action']){
case "getrooms":
echo getRooms($db);
break;
//Diese Zeilen wurden hinzugefügt
case "getevents":
echo getEvents($_POST['username'], $_POST['type'], $_POST['limit'], $_POST['offset'], $db);
break;
case "geteventtypes":
echo getEventTypes($db);
break;
case "addevent":
echo addEvent($_POST['type'], $_POST['text'], $db);
break;
case "getunseenevents":
echo getUnseenEvents($_POST['username'], $db);
break;
case "getgraphdata":
echo getGraphData($_POST['room'], $_POST['value'], $_POST['von'], $_POST['bis'], $db);
break;
case "getroomdata":
echo getRoomData($_POST['room'], $db);
break;
case "getmodes":
echo getModes($_POST['room'], $_POST['device'], $db);
break;
case "setmodes":
echo setModes($_POST['room'], $_POST['device'], $_POST['zustand'], $db);
break;
case "getsensordata":
echo getSensorData($_POST['room'], $_POST['value'], $_POST['showeinheit'], $db);
break;
case "runscene":
echo runScene($_POST['room'], $_POST['name'], $db);
break;
case "createscene":
echo createScene($_POST['devices'], $_POST['rooms'], $_POST['types'], $_POST['values'], $_POST['conditions'], $_POST['room'], $_POST['name'], $db);
break;
case "getscenes":
echo getScenes($_POST['room'], $db);
break;
case "getsysteminfo":
echo getSystemInfo();
break;
}
}
function validateUser($username, $password, $db){
//wird noch implementiert
return true;
}
?>
Ereignisse per Python hinzufügen
Damit du Ereignisse auch per Python in die Datenbank einfügen kanst, wird nun auch noch ein Python-Skript angelegt. Mit diesem Skript können dann andere Programme ganz einfach ein Ereignis in der Datenbank abspeichern. Um dieses Skript anzulegen, erstellst du mit FileZilla im Ordner „python“ eine neue Datei namens „add-event.py“, öffnest sie und fügst den folgenden Code ein:
#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3 as lite #Für die Datenbankverbindung
import datetime #Um Datum und Uhrzeit abzufragen
import sys #Um Kommandozeilenargumente zu lesen
now = datetime.datetime.now().strftime("%s")
con = lite.connect('/var/www/html/database/data.sqlite')
def addEvent(type, text):
#Daten in Datenbank schreiben
cur = con.cursor()
cur.execute("INSERT INTO EVENTS (TIMESTAMP, TEXT, TYPE) VALUES ('"+now+"', '"+text+"', '"+type+"')")
con.commit()
#Skriptaufruf: sudo python /var/www/html/python/add-event.py [TYP] [TEXT]
text = ""
if(len(sys.argv) > 2):
for index in range(2, len(sys.argv)):
if(index > 2):
text = text+" "
text = text+sys.argv[index]
addEvent(sys.argv[1], text)
Skriptaufruf
Das Skript kannst du nun folgendermaßen aufrufen:
sudo python /var/www/html/python/add-event.py [TYP] [TEXT]
Also zum Beispiel folgendermaßen:
sudo python /var/www/html/python/add-event.py Server Der Server ist jetzt online.
Mit diesem Aufruf wird nun ein Ereignis zur aktuellen Zeit mit dem Typ [TYP] und dem Text [TEXT] in die Datenbank geschrieben. Dieses Beispiel wird im folgenden Abschnitt dazu genutzt, um beim Systemstart ein Ereignis anzulegen.
Ereignis bei Serverneustart hinzufügen
Abschließend zeige ich dir noch, wie du ein Ereignis in der Datenbank speichern kannst, wenn der Server neu gestartet wurde. Dies wird mit einem Cronjob gemacht.
Mit einem Cronjob können Befehle bei Systemstart oder nach einem bestimmten Zeitmuster ausgeführt werden. Um einen Cronjob anzulegen, muss zuerst die Crontab-Datei geöffnet werden:
crontab -e
Damit nun bei jedem Neustart des Servers ein Ereignis in der Datenbank angelegt wird, kannst du nun den folgenden Text in die Crontab-Datei schreiben:
@reboot sudo python /var/www/html/python/add-event.py Server Der Server ist jetzt online.
Wenn du Fragen oder Probleme hast, kannst du mir gerne einen Kommentar hinterlassen.
