Struktur der Datenbank optimieren

Teile diesen Beitrag

In der App zum Smarthome-System soll es möglich sein, Geräte, Nutzer, Szenen, Räume und mehr, direkt in der App zu erstellen und zu verwalten.

Damit dies funktioniert und damit alle möglichen Fehler vermieden werden können, muss die Struktur der Datenbank etwas angepasst werden.

Das geschieht mit ein paar SQL-Befehlen, die ich dir im folgenden Beitrag erkläre. An den Serverskripten wird heute nur eine Zeile verändert, in den nächsten Beiträgen werden aber auch noch die Sensorwerteverwaltung an die neue Struktur angepasst.

Achtung!

Bis die Änderungen am System vollständig abgeschlossen sind, kann es sein, dass die Smarthome App und Teile des Systems nicht richtig oder sogar gar nicht funktionieren.

Eventuell solltest du warten, bis alle Beiträge zur Systemänderung online sind und sie alle auf einmal abarbeiten. Im letzten Beitrag zur Systemänderung wird dann vermerkt, dass das System anschließend wieder funktioniert.

Datenbank-Backup

Bevor die Struktur der Datenbank geändert wird, sollte unbedingt ein Backup der Datenbank-Datei angelegt werden. Dann kann dieses Backup aieder eingespielt werdem, falls bei der Veränderung der Struktur ein Fehler auftritt.

Dazu verbindest du dich per FTP mit deinem Server und öffnest das Verzeichnis “database”. Dort kopierst du dir die Datei “data.sqlite” auf deine Festplatte.

Wenn es nun bei den Änderungen in der Datenbank ein Problem gibt, kann dieses Backup einfach wieder auf den Server kopiert und dort überschrieben werden.

Struktur der Datenbank ändern

Da der Server SQLite als Datenbanksystem verwendet, lässt sich die Struktur der einzelnen Tabellen nichtmehr verändern. Lediglich die Datentypen können verändert oder neue Spalten angelegt werden. Deshalb wird jede Tabelle mit einem SQL-Befehl in eine neue Tabelle mit verbesserter Struktur kopiert und die alte Tabelle gelöscht. Anschließend muss die neue Tabelle nur noch umbenannt werden.

Um die Struktur der Datenbank nun anzupassen, rufst du mit deinem Browser die Adresse “http://[IP DEINES PI]/database/phpLiteAdmin.php” auf.

Dort klickst du oben auf den Tab “SQL”. Hier können nun die folgenden SQL-Befehle eingegeben werden.

Mit diesem SQL-Kommando wird die Struktur geändert.

Mit diesem SQL-Kommando wird die Struktur geändert.

Tabelle “userdata”

Als erstes wird die Tabelle “userdata” angepasst. Der folgende Befehl erstellt eine Tabelle namens “userdata_kopie”, bei der das Feld “USERNAME” nicht leer sein darf und außerdem der Primärschlüssel ist. Das heißt, dass jeder Nutzername sinnvollerweise nur einmal vorhanden sein darf. Außerdem ein neues Feld “PASSWORD” hinzugefügt das ebenfalls nicht leer sein darf. Dieses Feld wird im weiteren Verlauf des Tutorials zur Nutzerauthentifizierung verwendet und für die bereits vorhandenen Datenreihen mit dem Wert “password” beschrieben. Dazu jedoch mehr im entsprechenden Tutorial. Anschließend wird der Inhalt der Tabelle “userdata” in die neue Tabelle kopiert und die Tabelle “userdata” gelöscht. Kopiere den folgenden Code in das SQL-Textfeld und Klicke auf “Go”:

CREATE TABLE userdata_kopie(
USERNAME TEXT NOT NULL,
PASSWORD TEXT NOT NULL,
IP TEXT,
AT_HOME INTEGER,
EVENTS_LAST_CHECKED INTEGER,
PERMISSIONS TEXT,
PRIMARY KEY (USERNAME)
);
INSERT INTO userdata_kopie(USERNAME, PASSWORD, IP, AT_HOME, EVENTS_LAST_CHECKED, PERMISSIONS)
SELECT USERNAME, 'password', IP, AT_HOME, EVENTS_LAST_CHECKED, PERMISSIONS FROM userdata;
DROP TABLE userdata;

Tabelle “ZWAVE_SENSOREN”

Tabelle “SCENES”

Im Gegensatz zur Tabelle “SCENES” dürfen bei der neuen Tabelle “SCENE_KOPIE” die Felder “NAME” und “ROOM” nicht leer sein und jeder Name muss einzigartig sein. Führe den folgenden Code im SQL-Textfeld aus:

CREATE TABLE SCENES_KOPIE(
NAME TEXT NOT NULL,
ROOM TEXT NOT NULL DEFAULT 'NONE',
ACTIONS TEXT,
PRIMARY KEY (NAME)
);
INSERT INTO SCENES_KOPIE(NAME, ROOM, ACTIONS)
SELECT NAME, ROOM, ACTIONS FROM SCENES;
DROP TABLE SCENES;

Tabelle “ROOMS”

Da in der App auch die Räume des Smarthome-Systems verwaltet werden können, und jedem Raum individuell ein Icon zugeordnet werden kann, wird in der Kopie der Tabelle “ROOMS” eine neue Spalte namens “ICON” erstellt, die als Standardwert “room” erhält. Außerdem dürfen die Spalten “NAME”, “LOCATION” und “ICON” nicht leer sein und die Spalte “LOCATION” ist der Primärschlüssel der Tabelle. Kopiere den Code in das Textfeld und klicke auf “Go”:

CREATE TABLE ROOMS_KOPIE(
NAME TEXT NOT NULL,
LOCATION TEXT NOT NULL,
ICON TEXT NOT NULL DEFAULT room,
PRIMARY KEY (LOCATION)
);
INSERT INTO ROOMS_KOPIE(NAME, LOCATION)
SELECT NAME, LOCATION FROM ROOMS;
DROP TABLE ROOMS;

Tabelle “funksteckdosen”

Mit dem vorletzten Code-Teil wird die Tabelle “funksteckdosen_kopie” angelegt. Bei ihr dürfen im Gegensatz zur Originaltabelle “funksteckdosen” die Spalten “ROOM”, “DEVICE”, “HAUSCODE”, “STECKDOSENNUMMER”, “ZUSTAND”, “ICON” und “NAME” nicht leer sein. Außerdem wurden manche Spalten ein wenig angepasst. Die Spalte “DEVICE” hat nun den Datentyp “INTEGER” und ist der Primärschlüssel der Tabelle. Der “ZUSTAND” der Tabelle wird jetzt nichtmehr als Zahl, sondern als “BOOLEAN” abgespeichert. Außerdem wurde der Spalte “ICON” ein Standardwert zugewiesen, nämlich “device”, und keine der Spalten darf einen leeren Wert erhalten. Führe auch den folgenden Codeblock im SQL-Eingabefeld aus:

CREATE TABLE funksteckdosen_kopie(
ROOM TEXT NOT NULL,
DEVICE INTEGER NOT NULL PRIMARY KEY,
HAUSCODE TEXT NOT NULL,
STECKDOSENNUMMER TEXT NOT NULL,
ZUSTAND BOOLEAN NOT NULL DEFAULT 0,
ICON TEXT NOT NULL DEFAULT device,
NAME TEXT NOT NULL
);
INSERT INTO funksteckdosen_kopie(ROOM, HAUSCODE, STECKDOSENNUMMER, ZUSTAND, ICON, NAME)
SELECT ROOM, HAUSCODE, STECKDOSENNUMMER, ZUSTAND, ICON, NAME FROM funksteckdosen;
DROP TABLE funksteckdosen;

Tabelle “SENSOR_DATA”

Abschließend wird die Tabelle SENSOR_DATA angepasst. Im Gegensatz zu den anderen geänderten Tabellen wird der aktuelle Inhalt der Tabelle SENSOR_DATA jedoch nicht in die neue Tabelle kopiert, sondern gelöscht, da die alten Datensätze nichtmehr zur neuen Struktur kompatibel sind.

CREATE TABLE SENSOR_DATA_KOPIE(
DEVICE_ID TEXT NOT NULL,
DEVICE_TYPE TEXT NOT NULL,
DATETIME DATETIME NOT NULL,
VALUE NUMERIC NOT NULL
);
DROP TABLE SENSOR_DATA;

Tabellen umbenennen

Damit die neu erstellten Tabellen nun von den Skripten verwendet werden können, müssen die Namen noch angepasst werden, damit sie genau so heißen, wie die vorherigen Originaltabellen. Dazu wird bei jeder Tabelle der Teil “_KOPIE” entfernt. Um Tabellen umzubenennen, klickst du auf eine Tabelle und wählst anschließend in der Leiste oben den Punkt “Rename”.

Nach der Optimierung der Struktur werden die Tabellen umbenannt.

Nach der Optimierung der Struktur werden die Tabellen umbenannt.

Skript anpassen

Neben ein paar kleinen Änderungen in der Sensorwerteverwaltung und den Skripten “getModes.php”, “setModes.php”, “getRoomData.php” und “api.php” muss außerdem eine Zeile im Skript “getRooms.php” etwas angepasst werden.

Da der Tabelle “ROOMS” eine neue Spalte namens “ICON” hinzugefügt wurde, die den anzuzeigenden Icon des jeweiligen Raumes enthält, soll dieser auch von der getRooms-Methode ausgegebem werden.

Dazu öffnest du in FileZilla die Datei “getRooms.php” und bearbeitest, wie im folgenden Skript zu sehen, die Zeile 15, indem du den Wert des Icon-Feldes zum Array hinzufügst.

<?php
 
function getRooms($db){
    //Räume laden
    $results = $db->prepare("SELECT * FROM 'ROOMS'");
 
    $results->execute();
 
    $rooms = array();
 
    //JSON-Objekt erstellen und füllen
    foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
		
		if(!hasPermission($row['LOCATION'], $db)){
			continue;
		}
		
        $room_item = array('name' => $row['NAME'], 'location' => $row['LOCATION'], 'icon' => $row['ICON']);
        array_push($rooms, $room_item);
    }
 
    //JSON-Objekt ausgeben
    header('Content-type: application/json');
    return json_encode(array('rooms' => $rooms));
}
 
?>

Anschließend speicherst du das Skript und lädst die Änderungen auf den Server hoch.

Wenn du Fragen oder Probleme hast, kannst du mir gerne einen Kommentar hinterlassen.

Teile diesen Beitrag
, ,
Vorheriger Beitrag
NodeMCU ESP8266 Einführungstutorial
Nächster Beitrag
Optimierung der Sensorwerteverwaltung

Ähnliche Beiträge

Menü