SQLite ist eine unterschätzte Datenbank. Manche glauben sogar, SQLite wäre keine „richtige“ Datenbank und nicht für den Produktionsbetrieb geeignet. Tatsächlich ist SQLite eine sehr solide Datenbank, die auch Terabytes an Daten verwalten kann. Lediglich die Netzwerkschicht fehlt.
SQLite ist nämlich „nur“ eine Bibliothek – kein Server. Einerseits scheidet SQLite damit für viele Anwendungen aus, andererseits ist es dadurch für viele andere Anwendungen gerade richtig. Offenbar sind das sogar so viele Anwendungen, das SQLite – laut eigener Angabe – die am öftesten installierte und genutzte Datenbank ist. Das ist vermutlich nur möglich, weil SQLite lizenzfrei ist („public domain“). Wenn man also SQL nutzen möchte, um Daten in einer Datei zu speichern, ist SQLite die erste Wahl.
Der SQL-Dialekt von SQLite braucht den Vergleich auch nicht zu fürchten. Die With
-Klausel wurde in SQLite zum Beispiel vier Jahre früher als in MySQL eingeführt. Zuletzt wurde SQLite um Window-Funktionen erweitert – nur fünf Monate nachdem MySQL Window-Funktionen eingeführt hat.
Dieser Artikel widmet sich den SQL-Erweiterungen, die SQLite im Jahr 2018 erfahren hat. Das sind also die neuen SQL-Funktionen der Versionen 3.22.0 bis 3.26.0.
Inhalt:
- Boolean-Literale und Tests
- Window-Funktionen
Filter
-KlauselInsert … on conflict
(“Upsert”)- Rename Column
- Als Nächstes auf Modern-SQL.com
Boolean-Literale und Tests
Beim Datentypen Boolean mogelt SQLite: Boolean
wird zwar als Typennamen akzeptiert, ist aber letztendlich nur ein Alias für Integer (analog zu MySQL). Die Wahrheitswerte true und false werden – wie in C – durch die Werte 1 und 0 repräsentiert.
Ab Version 3.23.0 kennt SQLite die Schlüsselworte true
und false
als Synonyme für die Werte 1 und 0 und unterstützt den Test is [not] true|false
. Das Schlüsselwort unknown
wird jedoch generell nicht unterstützt. Man kann stattdessen – wie zuvor – null
verwenden, da die Boolean-Werte unknown und null
ununterscheidbar sind.
Die Literale true
und false
können die Lesbarkeit von Values
- und Set
-Klauseln in Insert
- und Update
-Anweisungen deutlich erhöhen.
Der is [not] true|false
-Test ist nützlich, weil er etwas anderes bedeutet, als die entsprechenden Vergleichsoperatoren:
WHERE c <> FALSE
bedeutet etwas anderes als
WHERE c IS NOT FALSE
Wenn c
den Null-Wert hat, ist das Ergebnis der Bedingung c <> false
unknown. Da die Where
-Klausel nur true akzeptiert – false und unknown werden abgelehnt – scheinen solche Zeilen nicht im Ergebnis auf.
Im Gegensatz dazu ist das Ergebnis von c is not false
auch dann true, wenn c
den Null-Wert hat. Die zweite Where
-Klausel akzeptiert also auch Zeilen, in denen c
den Null-Wert hat.
Eine andere Möglichkeit, dieselben Zeilen auszuwählen, ist, den Null-Fall separat zu akzeptieren.
WHERE c <> FALSE OR c IS NULL
Diese Variante ist natürlich länger und auch etwas redundant (c
wird zweimal genannt). Letztendlich kann der Is not false
-Test also dazu verwendet werden, solche Or … is null
-Konstruktionen zu vermeiden. Mehr dazu in „Binäre Entscheidungen auf Basis dreiwertiger Ergebnisse“.
Hinweis in eigener Sache: Schulung in Wien
SQL Performance und modernes SQL – das sind die Themen meiner 5-tägigen SQL-Schulung im Mai. Mehr darüber und andere Schulungsformate auf winand.at.
Die Unterstützung der Boolean Literale und des Boolean Tests in SQLite ist auf ähnlichem Niveau wie in anderen Open-Source-Datenbanken. Einzig die Unterstützung für is [not] unknown
fehlt bei SQLite im Vergleich zu anderen Datenbanken. Interessanterweise sind diese Boolean-Funktionen bei den unten gezeigten kommerziellen Produkten generell nicht verfügbar.
Window-Funktionen
SQLite 3.25.0 führt Window-Funktionen ein. Wer Window-Funktionen kennt, weiß, dass das eine große Sache ist. Wer Window-Funktionen nicht kennt, sollte sie kennenlernen! Dieser Artikel ist nicht der richtige Ort, um Window-Funktionen zu erklären. Ich übertreibe aber nicht, wenn ich sage, dass Window-Funktionen die wichtigste „moderne SQL“-Funktion überhaupt sind.
Der Funktionsumfang der Over
-Klausel ist bei SQLite ziemlich nahe an dem anderer Datenbanken. Der einzig nennenswerte Rückstand ist, dass Range
-Rahmen (frame) keine Numerischen- oder Intervall-Distanzen unterstützen (nur current row
und unbounded preceding|following
). Das ist dieselbe Einschränkung, die auch SQL Server hat, und PostgreSQL hatte, als Window-Funktionen in SQLite eingeführt wurden. Mit PostgreSQL 11 wurde diese Beschränkung in der Zwischenzeit aufgehoben.
Die Menge der angebotenen Window-Funktionen ist bei SQLite ebenfalls am aktuellen Stand anderer Datenbanken. Die nennenswerten Lücken (distinct
in Aggregatfunktionen, width_bucket
, respect|ignore nulls
und from first|last
) gibt es auch in einigen anderen Produkten.
Filter
-Klausel
Obwohl die Filter
-Klausel nur eine syntaktische Schönheitsmaßname ist – mit einem Case
-Ausdruck kann man dasselbe Ergebnis erzielen – denke ich, dass die Filter
-Klausel essenziell ist, weil sie das Lernen maßgeblich erleichtert.
Betrachte dazu die folgenden Select
-Klauseln. Welche ist einfacher zu verstehen?
SELECT SUM(umsatz) gesamt_umsatz
, SUM(CASE WHEN produkt = 1
THEN umsatz
END
) prod1_umsatz
...
vs.
SELECT SUM(umsatz) gesamt_umsatz
, SUM(umsatz) FILTER(WHERE produkt = 1) prod1_umsatz
...
Dieses Beispiel fasst zusammen, was die Filter
-Klausel macht: Sie ist ein Zusatz zu Aggregatfunktionen, der dafür sorgt, dass nur die Werte bestimmter Zeilen aggregiert werden. Die Pivot-Technik ist der häufigste Anwendungsfall der Filter
-Klausel. Dazu zählt auch die Transformation von Attributen im Entity-Attribute-Value (EAV) Modell in Spalten.
SQLite 3.25.0 führ die Filter
-Klausel für Aggregatfunktionen ein, wenn sie die Over
-Klausel verwenden — also nicht mit group by
. Leider bedeutet das, dass man die Filter
-Klausel für die oben genannten Anwendungsfälle in SQLite noch nicht nutzen kann. Man muss dafür also nach wie vor auf Case
-Ausdrücke zurückgreifen. Ich hoffe, dass sich das bald ändern wird.
Insert … on conflict
(“Upsert”)
Mit SQLite 3.24.0 wurde das sogenannte „Upsert“ eingeführt: ein Insert
-Statement, das die Behandlung von Primärschlüssel- und Unqiue-Constraint-Verletzungen erlaubt. Damit kann man diese Fehler ignorieren (on conflict … do nothing
) oder ein Update auf die bereits existierende Zeile ausführen (on conflict … do update …
).
Upsert ist eine proprietäre SQL-Erweiterung – d. h. es kommt nicht aus dem SQL-Standard und ist daher in der Matrix unten Grau gehalten. SQLite verwendet jedoch dieselbe Syntax wie PostgreSQL.0 Der SQL-Standard sieht für diese Anforderung das Merge
-Statement vor.
Anders als PostgreSQL hat SQLite aufgrund einer syntaktischen Mehrdeutigkeit ein Problem mit der folgenden Anweisung.
INSERT INTO ziel
SELECT *
FROM quelle
ON CONFLICT (id)
DO UPDATE SET wert = excluded.wert
Laut Dokumentation liegt das Problem darin, dass der Parser nicht weiß, ob das Token on
zu einem Join gehört, oder eine Upsert-Klausel einleitet. Diese Mehrdeutigkeit kann man vermeiden, indem man der Abfrage eine weitere Klausel hinzufügt – z. B. where true
.
INSERT INTO target
SELECT *
FROM source
WHERE true
ON CONFLICT (id)
DO UPDATE SET val = excluded.val
Rename Column
Eine weitere proprietäre Erweiterung, die SQLite eingeführt hat, ist das Umbenennen von Spalten in Basistabellen.1 Der SQL-Standard bietet keine Funktion dafür an.2
SQLite folgt der Syntax, die auch von vielen anderen Produkten angeboten wird.
ALTER TABLE … RENAME COLUMN … TO …
Andere Neuerungen
Neben den SQL-Änderungen gab es in 2018 natürlich auch Erweiterungen der API. Ein Blick in die Neuerungen von SQLite lohnt sich.
Als Nächstes auf Modern-SQL.com
Der nächste Artikel über PostgreSQL 11 ist bereits in Arbeit. Folge modern-sql.com via Twitter, E-Mail oder RSS.
Wenn Du mehr über modernes SQL lernen willst, solltest du dir meine Schulung im Mai ansehen. Neben den oben erwähnten Window-Funktionen behandle ich dort auch Rekursionen, Indizierung und verfestige die Grundlagen. Schulungsgrundlage ist der aktuelle Entwurf meines nächsten Buches. Schulung jetzt ansehen!