Datenbanken mit SQLite

Heute ist Stammtischzeit:
Jeden Donnerstag 20:30 Uhr hier im Chat.
Wer Lust hat, kann sich gerne beteiligen. ;)
  • Einführung:

    Manch einer möchte mit seinem RaspberryPI etwas in Datenbanken schreiben aber nutzt hierfür das allgemein verbreitete MySQL, das aber eigentlich erst auf vernünftigen Servern zum Einsatz kommt - also solche die sowohl genug CPU als auch RAM haben...

    MySQL hat nämlich den Nachteil das eine Server-Software benötigt wird: der MySQL Daemon
    Der verbraucht aber permanent relativ viel RAM, auch wenn die Datenbanken garnicht genutzt werden...

    Da der RPI aber nicht wirklich sooo viel RAM und CPU Leistung hat, reicht es für kleinere Projekte wie Temperatur Messungen oder allgemein Sensoren usw auch eine Resourcen-schonendere Datenbank-Software zu nutzen: SQLite


    SQLite Installation:

    Zunächst möchte ich euch beschreiben wie ihr SQLite für sowohl die Konsole als auch den Webserver installiert:

    Für die Konsole - um SQLite zB auch in bash Scripts benutzen zu können, benötigt ihr folgendes:

    Code
    apt-get install sqlite3

    Für den Webserver (apache2 oder lighttpd) benötigt ihr:

    Code
    apt-get install php5-sqlite

    Entsprechende PHP5 Module natürlich vorrausgesetzt:
    für apache2: apt-get install php5
    für nginx: apt-get install php5-fpm
    für lighttpd: apt-get install php5-cgi


    Web-based Admin Tool:

    Für MySQL gibt es ja ein sehr bekanntes Admin Tool für den Browser: phpMyAdmin
    Das gibts natürlich auch für SQLite:

    http://code.google.com/p/phpliteadmin/

    Der grösste Vorteil hierbei ist das nur eine einzige php Datei benötigt wird: phpliteadmin.php
    Ein Nachteil wäre u.U. das man nur ein Passwort festlegen kann, man kann also nicht mehrere Benutzer festlegen die auf unterschiedliche Datenbanken Zugriff haben, aber ich denke das kann man im Bezug auf den Raspberry auch ausser Acht lassen :)

    Falls gewünscht kann ich auch noch auf die Installation und Einrichtung näher eingehen aber das lass ich hier jetzt erstmal weg, sollte eigentlich selbsterklärend sein


    Eine Alternative dazu wäre aber auch: http://sourceforge.net/projects/sqlitebrowser/


    Anwendung:

    Nun ans Eingemachte ;)

    Im Gegensatz zu MySQL werden bei SQLite einzelne Datenbank-Dateien verwendet. Wenn man SQLite ausschlieslich über die Konsole, also mit bash / sh oder python Scrpts benutzen möchte sähe das folgendermassen aus:

    Anwendung über die Konsole:

    Beim sqlite3 Befehl muss das erste Parameter die Datenbankdatei sein und danach kommt der jeweilige Befehl in Gänsefüsschen-oben ( " ).
    Anhand eines Beispiels lässt sich das denk ich am besten erklären:

    sqlite3 /var/www/db.sqlite "CREATE TABLE IF NOT EXISTS settings (id INTEGER PRIMARY KEY,user_id INT,setting TEXT,value TEXT);"

    /var/www/db.sqlite ist die Datenbankdatei
    CREATE TABLE IF NOT EXISTS sorgt dafür das eine Tabelle nur dann erzeugt wird falls diese noch nicht existiert
    settings ist der Name der Tabelle
    (id INTEGER PRIMARY KEY,user_id INT,setting TEXT,value TEXT) sind die Spalten mit den dazugehörigen Typen die in der Tabelle settings erzeugt werden sollen (INT ist eine Abkürzung für INTEGER)

    Der Name der Datenbankdatei ist übriegends egal, könnt ihr frei wählen

    Eine genauere Beschreibung der möglichen SQLite Syntax könnt ihr hier nachlesen: http://www.sqlite.org/lang.html
    Welche Typen möglich sind könnt ihr unter "CREATE TABLE" nachlesen


    Ausserdem gibt es auch einen Weg zu prüfen ob in der Datenbankdatei eine entsprechende Tabelle überhaupt existiert:

    sqlite3 /var/www/db.sqlite "SELECT count(name) FROM sqlite_master WHERE type='table' AND name=\"settings\""

    Eine Tabelle sqlite_master muss hierfür nicht extra erzeugt werden, das ist eine SQLite interne Tabelle...

    Der Ausgabewert wäre entweder 1 wenn die Tabelle settings existiert oder 0 wenn nicht. Das könnte man also auch in ein Script paken was erst prüft ob eine Tabelle existiert und falls nicht wird sie angelegt, aber dazu gerne später mehr falls gewünscht ;)

    Eine Datenbank-Abfrage könnte so aussehen:

    Code
    sqlite3 /var/www/db.sqlite "SELECT * FROM auth WHERE 1"

    Das verarbeiten einer solchen Datenbank-Abfrage gestaltet sich in bash/sh allerdings leider etwas umständlich:


    Anwendung über PHP:

    In PHP gäbe es 2 verschiedene Wege - wenn ihr über google nach "php sqlite" sucht dann findet ihr leider die veralteten "sqlite_open()" usw Befehle zB auf php.net, aber das ist primär für die etwas ältere SQLite2.. Teilweise lässt sich das auch mit SQLite3 verwenden aber irgendwann stösst man an die Grenzen des Machbaren und dann wäre es ziemlich ärgerlich alle bereits geschriebenen PHP Scripte umschreiben zu müssen - deshalb lieber von Anfang an mit PDO arbeiten :cool:

    PDO bietet für viele verschiedene SQL Datenbank-Typen einheitliche und übergreifende Befehle, man kann also die selben Befehle für sowohl SQLite als auch MySQL oder MSSQL usw nutzen - und ist zudem auch schnell(er)


    Ich gehe an dieser Stelle kurz auf einen wichtigen Punkt ein der nachfolgend klarer werden sollte:

    Wenn ihr in euren Scripts (php oder bash ist egal) öfters immer wieder die selben Dinge ausführen wollt, also der Code sich im Prinzip wiederholt, dann nutzt am besten sogenannte " function " s
    Das hat den einfachen Grund das ihr nur den Code in dieser function verändert brauch aber nicht 20x im ganzen Script verstreut

    Function's sind im grunde sowas wie Befehle die ihr deklariert und später an x-beliebiger Stelle immer wieder ausführen könnt


    Ich habe in meinen PHP Scripts folgende Functions in Benutzung mit denen ich sowohl das Ansprechen der Datenbankdatei regel als auch die Datenbankabfragen - und fehler die ggf beim öffnen der Datenbankdatei auftreten usw

    [code=php]// DB connect
    function db_con($DBfile) {
    if (!$db = new PDO("sqlite:$DBfile")) {
    $e="font-size:23px; text-align:left; color:firebrick; font-weight:bold;";
    echo "<b style='".$e."'>Fehler beim öffnen der Datenbank $DBfile:</b><br/>";
    echo "<b style='".$e."'>".$db->errorInfo()."</b><br/>";
    die;
    }
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $db;
    }

    // DB Query
    function db_query($sql) {
    global $db;
    $result = $db->query($sql) OR db_error($sql,$db->errorInfo());
    return $result;
    }

    // DB errors
    function db_error($sql,$error) {
    die('<small><font color="#ff0000"><b>[DB ERROR]</b></font></small><br/><br/><font color="#800000"><b>'.$error.'</b><br/><br/>'.$sql.'</font>');
    }

    // Add HTML character incoding to strings
    function db_output($string) {
    return htmlspecialchars($string);
    }
    // Add slashes to incoming data
    function db_input($string) {
    if (function_exists('mysql_real_escape_string')) {
    return mysql_real_escape_string($string);
    }
    return addslashes($string);
    }[/php]

    Die functions könnt ihr im PHP Script an jede x-beliebige Stelle einfügen - manche setzen sie nach ganz unten, andere wiederum erstellen dafür eine extra Datei die im Hauptscript dann included wird...

    In dem eigentlichen PHP Script würde dann folgendes stehen:

    [code=php]$SQLITEdb = "/var/www/db.sqlite";

    $db = db_con($SQLITEdb);
    $query = $db->query("SELECT pass FROM auth WHERE user='".$USER."'");
    $result = $query->fetch(PDO::FETCH_ASSOC);
    if ($result['pass'] == md5($PWD)) {
    $query = $db->query("UPDATE auth SET lastlogin='".time()."' WHERE user='".$USER."'");
    }[/php]

    $SQLITEdb legt die Datenbankdatei fest - ihr könnt aber natürlich auch mehrere nutzen..
    $db = db_con($SQLITEdb); damit wird die function db_con mit dem Parameter der Datenbankdatei aufgerufen - wenn ihr unterschiedliche Datenbankdateien nutzen wollt könnt ihr an dieser Stelle auch $db = db_con("/var/www/db2.sqlite"); nutzen
    $query = $db->query("SELECT pass FROM auth WHERE user='".$USER."'"); nutzt in diesem Fall keine meiner functions sondern eine php übliche pipe ( -> ). $USER ist eine Variable die ich vorher über $_POST erhalten und abgefragt habe.. der Rest sollte hoffentlich klar sein :huh:
    $result = $query->fetch(PDO::FETCH_ASSOC); hier wird nun ein PDO globaler Befehl genutzt um die Rückgabewerte des query's in das Array $result zu schreiben, die dann anschliesen genutzt werden können:
    if ($result['pass'] == md5($PWD)) { hier wird nun der Rückgabewert des query's verwendet, in diesem Fall aus der Spalte "pass" des Benutzers $USER

    Eine weitere Möglichkeit mit Rückgabewerten umzugehen wäre auch folgendes:
    [code=php]$db = db_con($SQLITEdb);
    $query = $db->query("SELECT id,user FROM auth WHERE 1");
    while($result = $query->fetch(PDO::FETCH_ASSOC)){
    $id = $result['id'];
    $AddedUsers[$id] = $result['user'];
    }
    [/php]
    Hier wird eine while Schleife genutzt weil mehr als eine Zeile zurückgegeben werden könnte. Es befinden sich also mehrere verschiedene Einträge in der Tabelle " auth "

    Und auch noch mal ein Beispiel zu meiner db_query function:
    [code=php]$db = db_con($SQLITEdb);
    $query = $db_query("SELECT pass FROM auth WHERE user='".$USER."'");[/php]


    Das wars dann eigentlich schon - ich hoffe das war nicht allzuschwer beschrieben :s , ansonsten beantworte ich euch gerne weitere Fragen :)


    //EDIT:


    Bezüglich Python siehe folgende Beiträge:
    SQLite Auslesen und mit Wert vergleichen
    Highscore-Liste
    Daten aus Dateien einlesen und als Variablen verwenden

    //EDIT: Wichtiger Hinweis bezüglich Python: Wie sage ich es in Python ? (Einsteigerfragen)

    • Offizieller Beitrag

    Die SQL Befehle würde ich ebenfalls in den Code Tag schreiben.

    Ich benutzte ebenfalls den sqlitebrowser. Für Firefox gibt es noch den

    SQLite Manager
    17219.png?modified=1331247702

    Zu phpliteadmin:
    Eigentlich benötigt man doch einen Webserver(apache, nginx, lighttpd) dahinter oder?


    Ansonsten schönes Tutorial.

    Gruß,
    ps915

    Well in my humble opinion, of course without offending anyone who thinks differently from my point of view, but also by looking into this matter in a different way and without fighting and by trying to make it clear and by considering each and every one's opinion, I honestly believe that I completely forgot what I was going to say.


  • Die SQL Befehle würde ich ebenfalls in den Code Tag schreiben.

    Welche SQL Befehle meinst du?

    Meinst du vielleicht die sqlite3 befehle? In CODE tags funktionieren hier aber leider keine Farben :(


    Zu phpliteadmin:
    Eigentlich benötigt man doch einen Webserver(apache, nginx, lighttpd) dahinter oder?

    Ja, zum anzeigen von PHP Scripts auf Web Ebene brauch man generell einen Webserver wie apache2 o.ä. , deswegen auch "Web-Based Admin Tool" ;)

  • Hallo,

    ich besitze seit zwei Wochen nun nen Raspberry Pi und verwende ihn hauptsächlich um Temperatur Daten die ich von Atmega Nodes bekomme darzustellen.

    Mit meinem Gossen php hab ich es auch schon geschafft die Daten zu erfassen und in Text Datei zur Weiterverarbeitung zu schreiben.

    Jetzt wollte ich auf eine Datenbank umsteigen um das ganze etwas übersichtlicher zu gestalten als mit unzähligen Textdateien.


    Über die Foren suche bin ich auf dein Tutorial gestoßen.
    (Wie so oft über eines von dir, wenn ich nach Problemlösung für den PI suche :D)


    Nun zu meinem eigentlichen Problem:

    Ich hab selbst nach stundenlangem suchen keine Anleitung im Netz gefunden die von Grund auf erklärt wie man da ran geht und anfängt.

    Hast du vielleicht einen Tipp für mich?
    Einen Link oder auch nur nach was ich am besten suchen soll?


    Grüße David

  • ;)

    Wie hast du das denn bisher gemacht?
    Frage wäre halt wie du die Daten erfasst - läuft da ein Script was die Atmega Nodes abfragt?

    Im Grunde brauchst du eine Schnittstelle was die Daten abfragt/Empfängt und worüber du die Daten dann in eine Datenbank schreibst. Anschließend baust du zB ein PHP Script um die Daten aus der Datenbank abzufragen und darzustellen

    Ggf. kannst du dich am Temperatur Funk Sensor Projekt orientieren - da empängt eine Sensor.pl die Daten und PHP Scripts sorgen dann fürs eintragen in eine SQLite Datenbank (add.php) sowie darstellen der Daten (index.php usw)

  • Die Nodes sind mit einem NRF24 ausgestattet und momentan läuft das so das ein Arduino als Gateway fungiert der per Wlan in meinem Netzwerk hängt.
    (unter anderem sitzt da noch ein 434mhz Sender drauf um Funksteckdosen zu schalten)

    Auf dem Pi hab ich mit PHP nen UDP Server laufen der die Daten vom Arduino entgegen nimmt und die Werte in passende Textdateien schreibt node1.txt, node2.txt....

    Momentan möchte ich erst mal nur die Textdateien mit der DB ersetzten.

    Am Schluss wird es dann wohl so sein das ich dem PI direkt ein NRF24 spendiere und er die Daten über bash oder Python oder was weiß ich, dort ablegt und der Webserver sich die Daten wieder holt um sie zu verarbeiten.


    Wie gesagt ich hab den PI erst seit zwei Wochen und meine letzten Ausflüge in PHP und bash liegen schon Jahre zurück.
    Ich wollte mich erst mal auf eines konzentrieren und nicht gleich mit 3 Programmiersprachen gleichzeitig anfangen.

    Deshalb die Frage nach einem "Grundkurs" in sqlite mit PHP. Mein PHP ist zwar auch alles andere als der Hit aber zumindest hab ich damit schon ein paar Sachen gemacht.

    Das Temperatur Funksensor Projekt werde ich mir mal genau durchlesen. Wäre ja nicht das erste mal das ich mir anhand eines Beispieles alles zusammen Reime :D


    PS: hab grad mal ein bischen gestöbert in dem Beitrag also ich verwende einen Atmega 328P habe es geschaft mit den NRF24 und nem DS18B20 auf 40µA zu kommen im Sleep mode. Wird dann alle 5min aufgeweckt und schickt nen Wert.

    Einmal editiert, zuletzt von zebbel (9. Februar 2014 um 18:57)

  • Auch wenn es etwas off-Topic ist:

    Ich arbeite in der Berufsschule fast ausschließlich mit dem iPad. Ich habe dort alle Arbeitsblätter drauf und fertige auch Mitschriften etc. an.
    Nun fangen wir an, mit Datenbanken zu arbeiten. Uns ist dabei überlassen, welches Datenbanksystem wir nutzen, da es vorrangig um SQL geht.
    Ich würde auch hier gerne mit dem iPad arbeiten um nicht mein Notebook o.Ä. mitschleppen zu müssen.
    Ich habe eine App gefunden (MySQL Lite), mit der ich mich auf einen MySQL Server verbinden kann und dort dann auch SQL Befehle auf die einzelnen Datenbanken/Tabellen absetzen kann. Das wollte ich nun einmal testen. Ich würde mich dazu dann von der Berufsschule aus übers Internet auf meinen RPi verbinden, auf dem die MySQL Datenbanken laufen.
    Jedoch würde ich meinen RPi gerne entlasten und auf eine SQLite Datenbank umsteigen. Kennt vielleicht jemand eine App, mit der das auch gehen würde?

    Viele Grüße

    Tobi/Bart

  • zebbel: Dann brauchst du dein PHP-UDP ja nur so abändern das die Daten wie in meiner add.php zur SQLite Datenbank hinzugefügt wird ;)


    Bart: Das Problem mit SQLite ist dass es auf Dateien basiert, man "verbindet" sich also zu einer local liegenden Datei aber nicht übers Netzwerk, also kein socket o.ä.

    Es gäbe dennoch ein paar Möglichkeiten... Eine davon habe ich selber schon verwendet: Zugriff auf die Datenbank-Datei über eine Netzwerkfreigabe

    Weitere sind auf folgendem Link beschrieben, aber die habe ich noch nie getestet: http://www2.sqlite.org/cvstrac/wiki?p=SqliteNetwork
    Inwiefern da allerdings was für dein iPad dabei ist kann ich auch nicht sagen..

    Vielleicht wäre für dich PostgreSQL eine brauchbare Alternative

  • Jetzt hoffe ich nochmal auf deine Hilfe :blush:

    Das mit dem lesen und schreiben in die sqlite von PHP aus hab ich im griff :D


    Ich hab es auch nach langem hin und her geschafft mein Funkmodul direkt am Pi zum laufen zu bekommen und ein c++ Programm zusammen zu schreiben um die empfangenen Daten in der Shell auszugeben.

    Jetzt möchte ich die Daten darüber aber auch in die sqlite bekommen.

    Am liebsten wäre es mir wenn ich aus dem c++ Programm die Daten an ne PHP übergeben könnte die dann den Rest macht.


    Hast du da nen Tipp für mich wie ich das am besten machen kann?


    Hier mal das Programm wo die Daten einliest und über die Shell ausgibt:


    Hab es schon alleine hin bekommen :D
    system() war das Zauberwort...

    Einmal editiert, zuletzt von zebbel (12. Februar 2014 um 03:57)

  • Hallo,

    evtl kann mir einer kurz unter die Arme greifen

    Ich habe ein sqlite DB auf meinem PI, da werden alle paar Minuten Daten reingeschrieben.

    Datenbank: DB_sqlite
    Tabelle: TAB_sqlite

    Nun möche ich per Cronjob alle 5 Minuten den letzen Wert von einigen Spalten in eine txt Datei schreiben.
    Also, Spalten habe ich ca 20.
    SPALTE1, SPALTE 5 und SPALTE10 soll der letzte Wert
    in eine Text stehen.

    Finde leider nicht den Ansatz....

Jetzt mitmachen!

Du hast noch kein Benutzerkonto auf unserer Seite? Registriere dich kostenlos und nimm an unserer Community teil!