SQLite in 2018

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:

1. Boolean-Literale und Tests
2. Window-Funktionen
3. Filter-Klausel
4. Insert … on conflict („Upsert“)
5. Rename Column

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“.

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.

1. Nur true und false, kein unknown. Verwende stattdessen null
2. Kein is [not] unknown. Verwende stattdessen is [not] null

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.

1. Keine Verkettung von Fensterdefinitionen
2. Keine <Distanz> (nur unbounded und current row) und keine Datums- und Zeit-Typen

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.

1. DISTINCT-Aggregate werden nicht unterstützt
2. Auch ohne ORDER BY-Klausel
3. Kein respect|ignore nulls
4. Keine negativen Offset-Werte • Proprietäre Null-Behandlung: lead(<expr>, 'IGNORE NULLS') (ein String-Argument)
5. Kein Default-Wert möglich (kein drittes Argument) • Kein respect|ignore nulls
6. Keine negativen Offset-Werte • Kein ignore nulls
7. Keine negativen Offset-Werte
8. Proprietäre Null-Behandlung: first_value(<expr> IGNORE NULLS) (kein Komma)
9. Proprietäre Null-Behandlung: first_value(<expr>, 1, null, 'IGNORE NULLS') (ein String-Argument)
10. Kein ignore nulls
11. Kein from last
12. Kein ignore nulls • Kein from last

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

1. Siehe auch log errors für insert, update, delete und merge („DML error logging“)
2. On conflict darf nicht direkt auf die From-Klausel einer Abfrage folgen. Wenn nötig, füge where true hinzu
3. ⚡Mehrdeutigkeiten werden nicht immer gemeldet
4. Nicht alle Operationen • ⚡Mehrdeutigkeiten werden nicht immer gemeldet • Nicht alle Syntax-Varianten
5. Nicht alle Operationen

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 …

1. Siehe sp_rename.

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.