SQLite - Tabellen erzeugen, ändern, löschen

Dem Relationalen Modell entsprechend, besteht SQL aus unterschiedlichen Teilen. Der Strukturelle Teil deckt die Erzeugung, Änderung und Löschung der verschiedenen Datenbankobjekte ab und wird dementsprechend "Daten Definitions Sprache" genannt. Ein anderer Teil der Sprache dient dem Abrufen und der Manipulation der gespeicherten Daten und wird "Daten Manipulations Sprache" genannt. Für diese beiden Teile gibt es die Abkürzungen "DDL" und "DML", entprechend der englischen Bezeichungen.

Die nachfolgenden Kommandos sind also dem DDL-Teil der SQL-Sprache zu zuordnen.

CREATE TABLE - Kommando

Betrachtet man das entsprechende Syntaxdiagramm "create-table-stmt" zum CREATE - Kommando und nimmt darin den "schnellsten" Weg, so erhält man für das CREATE-Kommando die folgende minimale Syntax zum Erzeugen einer Tabelle:

create table tabellenname ( spaltenname );

Es gibt in der SQLite Sprachreferenz für das create table Kommando auch eine eigene Seite mit umfangreichen Informationen; man beachte dazu den entsprechenden Querverweis lang_createtable.html in dem oben genannten Syntaxdiagramm.

Die nachfolgende Kommandosequenz erzeugt eine Tabelle mit einer Spalte , füllt diese mit sechs Werten und zeigt dann schliesslich den Tabelleninhalt an:

sqlite>create table tabelle(spalte);
sqlite>insert into tabelle(spalte) values(104);
sqlite>insert into tabelle(spalte) values('Die Zahl Einhundertundvier');
sqlite>insert into tabelle(spalte) values(x'68');
sqlite>insert into tabelle(spalte) values('Ein Byte mit dem Wert 'x''68''');
sqlite>insert into tabelle(spalte) values('01.01.2000');
sqlite>insert into tabelle(spalte) values('Ein Datum');
sqlite>
sqlite>select * from tabelle;
spalte
------
104
Die Zahl Einhundertundvier
h
Ein Byte mit dem Wert x'68'
01.01.2000
Ein Datum
sqlite>

Speicherklassen

Beachtenswert ist, dass in dem Beispiel die einzige Spalte mit unterschiedlichen Datentypen gefüllt wird. SQLite verhält sich hier wie eine typenlose Sprache, z.B. JavaScript. Beim Anlegen der Tabelle wurde letztlich für die Spalte auch gar kein Datentyp explizit angegeben. Die Spalte nimmt stets den Datentyp entsprechend der literalen Repräsentation ihres aktuellen Wertes an. Einmal ist sie eine Zahl und dann wieder ein String usw.  Aber selbst wenn man einen Datentyp für die Spalte explizit festgelegt hätte, z.B. "integer", könnte man noch immer beliebige Werte in die Spalte eingeben, also insbesondere  auch einen String.

Eine wesentliche Bedeutung der Datentypen liegt in der Art und Weise, wie SQLite Werte auf der Festplatte speichert. So wird z.B. ein integer, je nach Grösse der Zahl,  mit einem bis acht Bytes gespeichert.  Die SQLite-Dokumentation spricht deshalb auch von "Speicherklassen", anstatt von Datentypen.  Weiterhin ist der Datentyp einer Spalte massgeblich dafür, wie die Werte der Spalte sortiert werden, dazu später mehr.

Vergleichbarkeit unterschiedlicher Speicherklassen

Da in ein und derselben Spalte unterschiedliche Speicherklassen vorkommen können, bedarf es einiger Regeln für die Vergleichbarkeit der Daten in dieser Spalte:

• NULL: ist stets kleiner als ein Wert in jeder anderen Speicherklasse
• integer,real: wird numerisch verglichen
• text: ist stets grösser als jeder integer, der Vergleich erfolgt gemäss der collate-Angabe in der Spaltendefinition
• blob: blob ist stets grösser als ein Wert jeder anderen Speicherklasse.

Constraints

Wie das letzte Beispiel bereits vermuten lässt, gibt es in SQL Sprachkonstrukte, mit denen sich bis zu einem gewissen Grad Datenintegrität in einer SQLite-Datenbank durch Einschränkung der möglichen Werte, die eine Spalte annehmen können soll, erzwingen lässt: Constraints(=Einschränkungen). Constraints können sowohl auf Spaltenebene, als auch auf Tabellenebene definiert werden.

Eine typische Tabellen-Definition sieht dann wie folgt aus:

-- Tabelle 'pflanzen' erzeugen
create table  pflanzen (
	id integer primary key, 
	name text not null collate nocase, 
	art text not null default 'Obst', 
	farbe text not null default 'grün', 
	geschmack text not null default 'sauer', 
	groesse text not null default 'klein',
	unique(name,art),
	check(length(name)>0)
);

Constraints auf Spaltenebene

• Die Spalte "id" ist vom Typ "integer" und hat ein Constraint "primary key" , welches für die Eindeutigkeit der Spaltenwerte sorgt und in dieser Typ-Constraint-Kombination ein autoincrement realisiert, falls die Spalte bei Einfügeoperationen nicht angegeben wird. Wird jedoch ein Wert eingegeben der schon existiert, wird ein Fehler geworfen!
• Die Spalte "name" ist vom Typ "text" und hat zwei Constraints:
• not null :  null-Werte sind nicht erlaubt; es muss also mindestens ein Zeichen eingegeben werden. Einfügeanweisungen müssen explizit einen Wert angeben ( denn das Weglassen einer Spalte in einer Einfügeanweisung bedeutet, dass null in die Spalte eingegeben würde ) und Änderungsanweisungen können den Spalteninhalt nicht nach null abändern.  Will man trotz des not null Constraints die implizite Werteingabe in die Spalte ( also Weglassen des Spaltennamens in der entsprechenden EinfügeAnweisung ) zulassen, so kann man das mittels der Vorgabe eines default Wertes für die Spalte erreichen, der eben genau dann zum Tragen kommt, wenn explizit kein Spaltenwert angegeben wird.
• collate nocase:  Beim Vergleichen ( collate )und insbesondere auch bei der Indexierung wird Gross-/Kleinschreibung nicht berücksichtigt. Das Standardverhalten ist collate binary, dabei erfolgt der Vergleich Byteweise.
• Die Spalte "art" ist vom Typ "text", darf keine null-Werte enthalten und wird mit dem default Wert 'Obst' belegt, falls sie bei einer Einfügeoperation nicht angegeben wird.

Die anderen Spalten sollten sich nun selbst erklären.

Constraints auf Tabellenebene

Am Ende des Kommandos gibt es noch weitere  Einschränkungen:

• unique für die Spalten "name" und "art", so dass entsprechende Wertekombinationen eindeutig sein müssen. Eine Pflanze Namens "Kirsche" der Art "Obst" kann es deshalb nur einmal in der Tabelle geben, jeder weitere Versuch diese Werte-Kombination aus "name" und "art" einzugeben wirft dann einen Fehler.

• check für die Spalte Name. Check erlaubt die Verwendung von Ausdrücken zur Einschränkung der Eingabemöglickkeiten. In diesem Fall muss mindestens ein Zeichen in die Spalte "name" eingegeben werden, insbesondere ist jetzt explizit auch kein leerer String erlaubt ( der wäre zwar not null, jedoch mit einer String-Länge==0). Eine Auflistung der in SQLite verfügbaren Funktionen findet man in der Funtions-Referenz der SQLite-Dokumentation.

Contraints Tabellenübergreifend

Überlegt man sich einmal, dass in der Beispiel-Tabelle die Felder farbe, art, geschmack und groesse  mit Freitext gefüllt werden, so sollte klar sein, dass alleine schon Rechtschreibfehler zu Problemen bei der Auswertung des Datenbestandes führen können. würde man jetzt diese Tabelle in eine Datentabelle und mehrere Nachschlage-Tabellen zerlegen und die Daten geignet miteinander verknüpfen, dann könnten nur noch definierte Begriffe verwendet werden. In diesem Fall müsste aber gewährleistet sein, dass in der Nachschlage-Tabelle nicht Daten gelöscht werden können, auf die aus der Datentabelle heraus verwiesen wird. Zu diesem Zweck bietet das relationale Datenmodel die so genannte referentielle Integrität. Nachfolgend eine Tabellenzerlegung bezüglich des Feldes "art":

-- Tabelle 'pflanzen' erzeugen
create table  pflanzen (
	id integer primary key, 
	name text not null collate nocase, 
	art_id integer references pflanzen_art(id) on delete restrict deferrable initially deferred, 
	farbe text not null default 'grün', 
	geschmack text not null default 'sauer', 
	groesse text not null default 'klein',
	unique(name,art),
	check(length(name)>0)
);
-- Tabelle 'pflanzen_art' erzeugen
create table  pflanzen_art (
	id integer primary key, 
	bezeichnung text not null collate nocase 	
);

Das Feld "art_id" referenziert auf das Feld "id" in der Tabelle pflanzen_art und schränkt die Löschmöglichkeit derart ein, dass ein Datensatz in der Tabelle pflanzen_art solange nicht gelöscht werden kann, wie es noch Verweise aus einer anderen Tabelle auf ihn gibt. Ausserdem wird hier mit der deferrable Klausel bestimmt, dass der Constraint verzögert ( deferred ) durchgesetzt wird, also erst am Ende einer Transaktion. Man kann auch festlegen, dass dies direkt ( immidiate ) geschehen soll, also sofort beim Löschversuch.

Hier die vollständige Syntax der referentiellen Integrität:

create table <tabellen-name> (
<spalten-definition> references <fremd-tabellen-name>(<spalten-name>)
on { delete | update } <integritäts-aktion> [not] deferrable [initially { deferred | immidiate }, ]
);

Es sind folgende Integritäts-Aktionen möglich:

• set null:  das betreffende Feld wird auf null gesetzt
• set default: das betreffende Feld wird auf den Vorgabewert gesetzt
• cascade: wird der Eltern-Schlüssel geändert, werden alle Kind-Schlüssel mit geändert. Beim Löschen werden alle Kind-Datensätze mit gelöscht.
• restrict: Ändern oder Löschen verhindern, wenn dabei in der Kind-Tabelle verwaiste Datensätze entstehen würden.
• no action: Es wird keine Aktion durchgeführt, aber am Ende des Kommandos bzw. am Ende der Transaktion ( falls deferred gesetzt ist ) wird eine entsprechende Fehlermeldung gegeben.

CREATE INDEX - Kommando

Mit Hilfe eines Index kann die Geschwindigkeit von Datenbankabfragen gesteigert werden. In SQLite  gibt es bei der Anwendung  einige Besonderheiten zu beachten:

• bei der Verwendung von Vergleichsausdrücken bzw. dem  IN - Operator wird, soweit vorhanden, auf Ein-Spalten-Indizes zurückgegriffen .
• bei Mehrspalten-Indizes werden bei einer Abfrage nur die von links nach rechts lückenlos mit der Indexdefinition korrespondierenden aufeinander folgenden Spalten berücksichtigt.
• bei Mehrspalten-Indizes werden von links nach rechts  Vergleichsausdrücke mit "=" bzw. "IN" berücksichtigt. Alle auf einem anderen als diesen beiden Opereatoren folgenden Spalten-Vergleiche werden nicht berücksichtigt.

Es folgen Beispiele für diese Aussagen, hier zunächst die Syntax für die Erzeugung eines Indexes:

create [unique] index <index-name> on <tabellen-name> ( spaltennamen );

Die Spaltennamen werden als komma-separierte Liste angegeben, wenn mehr als eine Spalte benutzt werden soll. Unique erzwingt Eindeutigkeit der Wertekombination bezüglich der angegebenen Spalten. Die Reihenfolge der Spalten in der Liste bestimmt, wonach sortiert wird ( zuerst nach der ersten Spalte, dann nach der zweiten Spalte usw. ).

Die Verwendung von Indizes sollte wohlüberlegt sein, denn auf der einen Seite können zwar Abfragen  beschleunigt werden, auf der anderen Seite aber vergrössern sie aber den Speicherplatzbedarf der Datenbank sowie den Zeitaufwand beim Einfügen, Ändern und Löschen von Datensätzen der entsprechenden Tabelle. Jeder Index speichert die von ihm eingeschlossenen Spalten redundant ab. Würde man z.B. einen Index pro Spalte einer Tabelle erzeugen, dann hätte man den Speicherplatz dieser Tabelle bereits verdoppelt.

Grössenänderung des Speicherplatzbedarfs

Vor der Ausführung des nachfolgenden Kommandos sollte man sich einmal die Grösse der Datenbankdatei der Pflanzendatenbank auf der Festplatte ansehen:

create  index idx_pflanzen_name on pflanzen(name);

Wie gross ist sie nach der Erzeugung des Index?

Ein weiterer Versuch:

create index idx_pflanzen_art on pflanzen(art);

Und wieder ist die Datenbankdatei merklich grösser geworden.

Zeitverhalten bei Einfüge- und Änderungsoperationen

Ebenso lässt sich das Zeitverhalten mit und ohne Index testen. Dafür gibt es im SQLite-Kommandozeilen-Programm bzw. im SQLite3Editor den Befehl  .timer . Schaltet man den Timer ein ( .timer on ) , so wird die Ausführungsdauer eines Kommandos nach dessen Beendigung angezeigt. Wie lange dauern Einfüge- und Änderungsanweisung jeweils mit und ohne Index.  Der Unterschied ist allerdings bei einer sehr kleinen Datenbank wahrscheinlich nicht oder kaum wahrnehmbar; mit zunehmender Datenbankgrösse gewinnt er jedoch an  Gewicht.

Zeitverhalten bei Abfrageoperationen

Den gleichen Test kann man auch bezüglich Suchanfragen durchführen: Zeitverhalten jeweils mit und ohne passendem Index. Dieser Unterschied kann je nach Art der Suchanfrage  schon bei kleinen Datenbanken zu Gunsten einer höheren Ausführungsgeschwindigkeit gravierend sein.

ALTER TABLE - Kommando

Will man an einer bereits vorhandenen Tabelle Änderungen vornehmen, z.B. hinzufügen einer Spalte oder ändern des Tabellennamens, dann lässt sich dies mit dem Kommando ALTER  erreichen:

alter table tabellenname { rename to neuername |  add column spaltendefinition };

Innerhalb der geschweiften Klammern stehen hier die Kommando-Optionen, getrennt durch einen senkrechten Strich, von denen genau eine ausgewählt werden muss.

Nachfolgend wird der bereits bekannten Tabelle "pflanzen"  eine Spalte "erntezeit" hinzugefügt:

alter table pflanzen
add column erntezeit text not null default 'Herbst' collate nocase; 

Die Spaltendefinition entspricht dabei exakt der Syntax, wie sie auch beim Erzeugen einer Tabelle zu verwenden ist.

Will man die Tabelle "pflanzen" z.B. in "fruechte" umbenennen,  so lässt sich das wie folgt erreichen:

alter table pflanzen rename to fruechte; 

Ändern oder löschen vorhandener Spalten

Offensichtlich lassen sich vorhandene Spalten mit dem Kommando ALTER nicht ändern oder löschen. Zu diesem Zweck müsste die gesamte Tabelle gelöscht und in der gewünschten veränderten Form neu erzeugt werden. Eventuell vorhandene Daten müssten  dann vorher ausgelagert und nachher wieder  passend eingefügt werden.  Man könnte die Tabelle natürlich auch umbenennen, dann in veränderter Form neu anlegen und schliesslich die Daten aus der umbenannten Tabelle passend in die neue Tabelle einfügen.

DROP TABLE - Kommando

Mit dem DROP-Kommando kann eine Tabellendefinition aus einer Datenbank komplett  entfernt werden, dabei werden  auch die in der Tabelle ggf. gespeicherten Daten gelöscht:

drop table tabellenname;

Mit dem nachfolgenden Kommando lässt sich schnell eine Testtabelle erzeugen und mit dem .table Kommando anzeigen:

sqlite>create table a(b);
sqlite>.tables
a
sqlite>

nach der drop Anweisung ist die Tabelle wieder verschwunden:

sqlite>drop a; 
sqlite>.tables
sqlite>

Die Darstellung geht von einer leeren Datenbank zu Beginn und am Ende aus.

CREATE TRIGGER - Kommando

Trigger sind Ereignis-Handler in einer SQL-Datenbank, die an Tabellen gebunden werden und auf Einfüge-, Änderungs und Löschoperationen reagieren. Die allgemeine  Syntax des create trigger - Kommandos ist:

create [ temp | temporary ] trigger <trigger-name> [ before | after ] [ insert | delete | update | update of <spalten-name(n)> ] on <tabellen-name> begin <sql-kommandos> end ;

Trigger können definiert werden für die Reaktion vor oder nach der Operatation, sie können temporär erzeugt oder in der Datenbank gespeichert werden und sie können sich auf eine beliebige Änderungen der Tabelle oder auf die Änderung ausgewählter Spalten beziehen, je nach dem, für welches Ereignis sie ausgelegt wurden.

Insert und Delete - Trigger

Einfüge- und Löschoperationen betreffen stets eine komplette Tabellenzeile, da sich diese nicht auf einzelne Spalten beziehen können. Jedoch kann innerhalb des entsprechenden Triggers auf die Spalten der vom Ereignis betroffenen Zeile zugegriffen werden. Da es bei der Löschoperation keine neuen und bei der Einfügeoperation keine alten Daten gibt, kann hier lediglich wie folgt auf Spalten referenziert werden:

• INSERT :  new.<spalten-name>
• DELETE: old.<spalten-name>

Update -Trigger

Update-Trigger können sich auf eine Spaltenauswahl beziehen.  Innerhalb des Update-Trigger Anweisungsblocks kann man auf den alten und auf den neuen Inhalt einer Spalte zugreifen:

• auf den alten Inhalt mit  old.<spalten-name>  und auf den neuen mit  new.<spalten-name> .

Ein Update Trigger für die bereits bekannte Tabelle "pflanzen" könnte dann so aussehen:

create trigger farbe_upd update of farbe on pflanzen 
	begin  
		if length(trim(new.farbe)) < 1  then farbe=old.farbe;
	end;

Dieser Trigger verhindert, dass ein leerer String in die Spalte  "farbe" eingegeben werden kann. Natürlich ist  für diesen speziellen Zweck ein Check-Constraint besser geeignet.