Klein, schnell und verlässlich – mit SQLite flanschen Sie mit wenigen Handgriffen eine relationale Datenbank an Skripte und Programme.

Schon bei kleinen Mengen strukturierter Daten regt sich oft der Wunsch nach einer Datenbank. Die Hürde, einen Server aufzusetzen, hält aber nicht selten von dem an sich sinnvollen Vorhaben ab. SQLite [1] bietet einen Kompromiss zwischen Aufwand und Nutzen. Im Gegensatz zu Datenbanken wie MySQL oder Oracle handelt es sich bei SQLite nicht um eigene Server-Software, sondern um eine Bibliothek, die den Zugriff auf die in einer Datei gelagerten Daten verwaltet. Die bei anderen Datenbanken übliche Verwaltung der Berechtigungen regelt das Programm über die Zugriffsrechte zu dieser Datei.

Wie für Bibliotheken üblich bietet auch SQLite die Möglichkeit, die Software in andere Programme einzubinden. Damit stehen in diesen die Funktionen der Datenbank automatisch bereit. Daneben existieren auch Schnittstellen zu Skriptsprachen, Darunter zu Perl, PHP und Python. Schließlich gibt noch es ein Kommandozeilen-Interface, mit dessen Hilfe Sie SQLite über Shell-Befehle steuern.

Einen ähnlichen Ansatz verfolgt das Projekt Recutils [2]. Dahinter verbirgt sich ebenfalls eine Bibliothek, die den Zugriff auf die Daten verwaltet, und die wie SQLite die Möglichkeit zum Einbinden in eigene Programmen oder zum Skripten mitbringt. Die Datenbanken liegen dabei in gewöhnlichen Textdateien. Die Flexibilität der Abfragesprache SQL (siehe Kasten “Structured Query Language”) steht hier, im Gegensatz zu SQLite, nicht bereit.

SELECT spalte1, spalte2, spalte3 FROM tabelle WHERE spalte1 < 10;

SELECT * FROM tabelle1, tabelle2 WHERE tabelle1.feld1 = tabelle2.feld2;

Datenbanken bieten in der Regel unterschiedliche Feldtypen an, um die darin enthaltenen Werte korrekt zu behandeln. Unglücklicherweise erweist sich SQLite in Sachen Datentypen als sehr lax: Sie dürfen durchaus einen Text in ein als Integer definiertes Feld einfügen, SQLite stört das nicht.

Ein Datumstyp fehlt übrigens komplett. Als Alternative bietet sich entweder ein Integer-Feld an (interpretiert als Unix-Timestamp), ein Text-Feld ("YYYY-MM-DD HH:MM:SS.SSS") oder ein Real-Feld (interpretiert als Julianisches Datum). Die Tabelle “Datentypen” gibt eine Übersicht über die prinzipiell in SQLite vorhandenen Typen.

In der Praxis

SQLite ist Public-Domain-Software und kommt in vielen kommerziellen und freien Projekten zum Einsatz. Prominente Beispiele: Die Webbrowser Firefox, Chrome und Safari, der Mailclient Thunderbird, die VoIP-Software Skype sowie die Mobile-Betriebssysteme Android, Symbian und iOS.

Darüber hinaus verwenden Webanwendungen wie Serendipity ( Abbildung 1), Mediawiki (Abbildung 2) und viele weitere Webapplikationen und CMS-Systeme die Software. Für große, stark frequentierte Seiten bietet sich freilich der Einsatz eines echten Datenbank-Servers an. Bei kleineren Blogs oder Wikis lohnt sich aber – vor allem, wenn der Provider nur PHP, aber keine Datenbank bereitstellt – durchaus der Griff zu SQLite. Auch für erste Experimente am heimischen Linux-PC eignet sich die schlanke Datenbank bestens.

Abbildung 1: Serendipity bietet bei der Installation die Möglichkeit an, die Daten in einer SQLite-Datenbank zu speichern.

Abbildung 2: Auch Mediawiki setzt auf Wunsch auf SQLite als Grundlage.

Falls möglich empfiehlt es sich, dabei die SQLite-Datenbank in einem Verzeichnis anzulegen, das nicht direkt im Wurzelverzeichnis der Webpräsenz liegt – etwa in /var/tmp, wo die Daten nach einem Neustart erhalten bleiben. Noch besser wäre freilich ein eigenes Verzeichnis.

Ist beides nicht möglich, weil Sie nur auf das Web-Verzeichnis Schreibzugriff haben, bleibt noch die Möglichkeit, die Datenbank mittels der Datei .htaccess vor dem direkten Zugriff schützen. Serendipity beispielsweise macht das automatisch. Ein Beispiel dazu zeigt Listing 1. Haben die Datenbanken eine andere Extension als .db (.sqlite ist ebenfalls üblich) oder andere Dateien verwenden dieselbe Extension, gilt es natürlich, die Sperre aus dem Beispiel entsprechend anzupassen.

# .htaccess-File, um *.db-Dateien
# (SQLite)  zu schützen
<Files *.db>
    deny from all
</Files>

Auf der Kommandozeile

SQLite liegt vielen Distributionen den beiden Versionen 2.x und 3.x bei. Zwischen den zwei Entwicklungslinien hat sich allerdings das Format der Datenbankdateien geändert. Daher gilt es, darauf zu achten, dass Sie die richtige Version des Kommandozeilen-Tools (sqlite für Version 2, sqlite3 für Version 3) verwenden. Ein Konvertieren zwischen den beiden Formaten klappt aber unkompliziert (Listing 2).

$ sqlite3 version3.sqlite .dump | sqlite version2.sqlite
$ sqlite version2.sqlite .dump | sqlite3 version3.sqlite

Dieser Artikel behandelt, wenn nicht anders angegeben, die aktuellere Version 3. Das passende Kommandozeilen-Tool sqlite3 akzeptiert neben Optionen auch einen Dateinamen als Parameter sowie optional die Angabe eines SQL-Kommandos. Über die Optionen ändern Sie zum Beispiel das Ausgabeformat. Der einzig notwendige Parameter ist der Dateiname der Datenbank. Steht danach noch ein SQL-Befehl, führt die Software diesen auf die Datei aus.

Alternativ setzen Sie interaktiv Befehle ab. Die eingebauten Kommandos des Tools beginnen mit einem Punkt (.help listet zum Beispiel diese Befehle auf), ansonsten folgen die Befehle im Wesentlichen der bekannten SQL-Syntax mit einem Semikolon am Ende. Die Software führt diese sofort aus.

Listing 3 zeigt eine kurze Session. Dabei gibt SQLite die Datenbank aus, die in den folgenden Programmierbeispielen zum Einsatz kommt, und zwar einmal als Liste (select * from kunden;) und einmal als SQL-Dump (.dump).

$  sqlite3 test.sqlite
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
kunden
sqlite> select * from kunden;
1|Max Mustermann
2|John Doe
3|Erika Mustermann
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE kunden(kdnr INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "kunden" VALUES(1,'Max Mustermann');
INSERT INTO "kunden" VALUES(2,'John Doe');
INSERT INTO "kunden" VALUES(3,'Erika Mustermann');
COMMIT;
sqlite> .quit

SQLite auf der GUI

Auf der SQLite-Homepage finden sich Verweise auf etliche grafische Management-Tools zum Verwalten von SQLite-Datenbanken [3]. Exemplarisch kommt in diesem Beitrag der SQLite-Manager [4], zum Einsatz, ein Firefox-Plugin. Nach dessen Download und Installation starten Sie das Programm über das Firefox-Menü Extras | SQLite Manager. Nach dem Start haben Sie die Möglichkeit, entweder eine der Firefox-internen SQLite-Datenbanken zu begutachten (Abbildung  3) oder eine eigene Datenbank anzulegen oder zu öffnen.

Abbildung 3: Das Firefox-Addon SQLite-Manager zeigt auf Wunsch auch die internen Datenbanken des Browsers an.

Abbildung 4 zeigt sehr anschaulich die Möglichkeiten des Plugins: So fragen Sie damit komfortabel Werte ab, ändern oder löschen diese und legen bei Bedarf neue Tabellen an. Es empfiehlt sich, Datenbanken von fremden Anwendungen (wie die Firefox-eigenen SQLite-Datenbanken oder jene von Thunderbird) nur sehr vorsichtig zu bearbeiten und vor den Experimenten Sicherungen anzulegen.

Abbildung 4: Der SQLite-Manager vereinfacht das Bearbeiten einer entsprechenden Datenbank.

Einsatz in Skripten

Viele Programmier- und Skriptsprachen eignen sich, um ein Gerüst um SQLite zu bauen, wie die folgenden Beispiele in Perl, PHP und C zeigen. Diese decken bei weitem nicht alle Möglichkeiten ab – eine umfassendere Dokumentation findet sich jedoch in der Dokumentation der beschriebenen Module.

In Perl erlaubt das Modul DBI den Zugriff auf Datenbanken. Über verschiedene Backend-Driver (DBD) regelt es den Zugriff auf die unterschiedlichen Datenbanktypen. Eine ausführliche Dokumentation der Funktionen finden Sie in den Manpages zu DBI und DBD::SQLite.

Das Beispiel aus Listing 4 zeigt, wie Sie eine Tabelle erzeugen, dann drei Datensätze einfügen und anschließend die Werte mit einem Select-Statement wieder abfragen. Die Angabe des Typs (Zeile 19) ist nötig, weil sonst standardmäßig der Typ TEXT zum Einsatz kommt. Das Löschen der Tabelle (Zeile 7) ermöglicht es, das Beispiel mehrfach hintereinander ohne Fehlermeldung zu testen.

#!/usr/bin/perl -w
use strict;
use DBI qw(:sql_types);
my $databasefile='test.sqlite';
my $dbh = DBI->connect("dbi:SQLite:dbname=$databasefile",'','');
# einfache SQL Befehle direkt ausführen
$dbh->do('DROP TABLE IF EXISTS kunden;');
$dbh->do('CREATE TABLE kunden(kdnr INTEGER PRIMARY KEY, name TEXT);');
# Kurzform von: Befehl vorbereiten + ausführen:
my $sth = $dbh->prepare("INSERT INTO kunden VALUES (1,'Max Mustermann')");
$sth->execute();
# Befehl vorbereiten und mehrfach ausführen.
$sth = $dbh->prepare('INSERT INTO kunden VALUES (?, ?)');
$sth->execute(2, 'John Doe');         # 2. Kunden einfügen
$sth->execute(3, 'Erika Mustermann'); # 3. Kunden einfügen
# Werte abfragen:
$sth = $dbh->prepare('SELECT * FROM kunden WHERE kdnr >= ?;');
$sth->bind_param(1, 2, {TYPE => SQL_INTEGER}); # 1. Platzhalter (?), Wert: 2, Typ: Integer
$sth->execute();
my ($kdnr, $name);
# Perl Variablen an Spalten binden:
$sth->bind_columns(\$kdnr, \$name);
while ($sth->fetch) {
       print "Kundennr: $kdnr, Name: $name\n"; # Daten ausgeben (oder sonstwie weiterverarbeiten)
}
$dbh->disconnect();

Bei vielen ähnlichen SQL-Statements, wie zum Beispiel in Listing 3 mit mehreren Insert-Anweisungen, empfiehlt sich der Einsatz von prepare() und execute(), anstatt alles direkt mit do() zu erledigen. Auf diese Weise braucht die Software das SQL-Statement nur einmal zu analysieren. Das beschleunigt das Ausführen.

$dbh->do("SELECT * FROM kunden WHERE kdnr >= $nummer;");

$dbh->do("SELECT * FROM kunden WHERE kdnr >= 2 ; DROP TABLE kunden ;");

Das DBI-Modul bietet Zugriff auf etliche Datenbanken. Um beispielsweise anstatt SQLite eine MySQL-Datenbank anzusprechen, ersetzen Sie Zeile 5 durch folgende Zeilen:

my $dbh = DBI->connect('DBI:mysql:database=test; host='Host', 'Username', 'Passwort');

Die Programmiersprache PHP bietet mehrere Möglichkeiten zum Anbinden einer SQLite-Datenbank. Das Beispiel in Listing 5 macht dasselbe wie das Perl-Beispiel (Listing 4) und verwendet dabei die PDO-Klasse (ab PHP >= 5.1):

<?php
$dbh = new PDO('sqlite:test.sqlite');
$dbh->query('DROP TABLE IF EXISTS kunden;');
$dbh->query('CREATE TABLE kunden(kdnr INTEGER PRIMARY KEY, name TEXT);');
$sth = $dbh->prepare('INSERT INTO kunden VALUES (1,\'Max Mustermann\')');
$sth->execute();
$sth = $dbh->prepare('INSERT INTO kunden VALUES (:kdnr, :name)');
$sth->bindParam(':kdnr', $kdnr, PDO::PARAM_INT); /* Platzhalter ":kdnr" die Variable $kdnr zuweisen */
$sth->bindParam(':name', $name, PDO::PARAM_STR);
$kdnr=2 ; $name ='John Doe';
$sth->execute(); /* Statement ausführen, Werte in die Platzhalter einfügen */
$kdnr=3 ; $name ='Erika Mustermann';
$sth->execute();
$sth = $dbh->prepare('SELECT * FROM kunden WHERE kdnr >= :kdnr;');
$sth->bindParam(':kdnr', $kdnr, PDO::PARAM_INT);
$kdnr=2;
$sth->execute();
/* Alle Werte auf einmal in ein Array holen und ausgeben */
$result = $sth->fetchAll();
print_r($result);
?>

Bei sehr umfangreichen Abfrage-Ergebnissen empfiehlt es sich, statt der Methode fetchAll() jeden Datensatz einzeln abzuholen und auszugeben. Das PDO-Modul bietet Zugriff auf unterschiedliche Datenbanken – für MySQL müsste beispielsweise die zweite Zeile von Listing 5 wie folgt lauten:

$dbh = new PDO('mysql:host=localhost;dbname=test','Username','Passwort');

Neben dem hier vorgestellten PDO-Modul gäbe es in PHP noch andere Möglichkeiten, um auf Datenbanken zuzugreifen, zum Beispiel MDB2 [5] oder SQLite-Funktionen [6] aus PEAR.

Listing 6 zeigt das Programmbeispiel in C. Es gibt wieder einen Befehl, um ein SQL-Statement sofort abzusetzen (sqlite3_exec()), sowie eine Variante mit den Prepared Statements. Das Beispiel kompilieren Sie, das SQLite3-Developmentpaket sqlite3-devel (OpenSuse) oder libsqlite3-dev (Debian) vorausgesetzt, mittels

gcc -Wall -lsqlite3 -osqlite-example sqlite-example.c

#include <stdio.h>
#include <sqlite3.h>
#define DATABASENAME "test.sqlite"
int main(){
  sqlite3 *db;
  sqlite3_stmt *stmt;
  char *error_exec=NULL;
  sqlite3_open(DATABASENAME, &db); /* Öffnen der Datenbank */
  /* SQL-Befehl sofort ausführen (entspricht Perl do() oder PHP query() */
  /* Beispiel mit Fehlerabfrage (das sollte immer gemacht werden) */
  if (sqlite3_exec(db, "DROP TABLE IF EXISTS kunden;", NULL, NULL, &error_exec) != SQLITE_OK){
    printf("SQLite Fehler: %s\n",error_exec);
    sqlite3_free(error_exec); /* Fehlermeldungsstring freigeben */
    error_exec=NULL;
  };
  sqlite3_exec(db, "CREATE TABLE kunden(kdnr INTEGER PRIMARY KEY, name TEXT);", NULL, NULL, NULL);
  sqlite3_prepare_v2(db, "INSERT INTO kunden VALUES (1,'Max Mustermann');", -1, &stmt, 0);
  sqlite3_step(stmt); /* prepared Statement auführen */
  sqlite3_finalize(stmt); /* Statement wieder freigeben */
  sqlite3_prepare_v2(db, "INSERT INTO kunden VALUES (?, ?);", -1, &stmt, 0);
  sqlite3_bind_int(stmt, 1, 2);
  sqlite3_bind_text(stmt, 2, "John Doe", -1, SQLITE_STATIC);
  sqlite3_step(stmt);
  sqlite3_reset(stmt);
  sqlite3_bind_int(stmt, 1, 3); /* Der 1. (2. Argument) Platzhalter (?) des Statements bekommt den Wert 3 zugewiesen */
  sqlite3_bind_text(stmt, 2, "Erika Mustermann", -1, SQLITE_STATIC);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
  sqlite3_prepare_v2(db, "SELECT * FROM kunden WHERE kdnr >= ?;", -1, &stmt, 0);
  sqlite3_bind_int(stmt, 1, 2);
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    int kundennummer = sqlite3_column_int(stmt, 0);
   /* das 2. Argument (0) gibt die Ergebnisspalte an, also 1. Spalte (Zählung beginnt bei 0) */
    char *kundenname = (char *)sqlite3_column_text(stmt, 1);
    printf("Kundennr: %d, Kundenname: %s \n",kundennummer, kundenname);
  }
  sqlite3_finalize(stmt);
  sqlite3_close(db); /* Schliessen der Datenbank */
  return 0;
}

Wann lohnt es sich, SQLite in eigenen Programmen einzusetzen? Speziell größere Datenmengen sind in C recht umständlich zu verwalten. Das Problem zwingt Sie dazu, entweder über Pointer-Arithmetik Listen zu verwalten oder Arrays mit fixen Größen anzulegen (und dann daran herumzuwursteln, wenn es doch zu klein ist). Mit einer SQLite-Datenbank gelingt das Verwalten wesentlich einfacher.

Wie würden Sie in C beispielsweise zu einer gegebenen Punktmenge den Punkt finden, zu dem ein neuer Punkt am nächsten ist? Alle Punkte in ein Array packen, über alles iterieren, von jedem Punkt den Abstand berechnen und dann das Minimum suchen. Mühsam. Mit SQL wäre das ein Einzeiler:

SELECT x,y FROM ebene ORDER BY (x-?)*(x-?)+(y-?)*(y-?) LIMIT 1

Fazit

Mit SQLite stehen ausgewachsene Funktionen einer Datenbank sehr einfach in Programmen und Skripten bereit. Auf der SQLite-Homepage finden Sie viele weitere Informationen und Tutorials – sowohl für Anwender als auch Entwickler. Im Gegensatz zu anderen Projekten, wo Sie im Spannungsfeld zwischen “klein”, “schnell” und “verlässlich” üblicherweise nur zwei Eigenschaften wählen dürfen, könnte das Motto von SQLite lauten: Nimm einfach alle drei.