Verwende in SQLite bevorzugt STRICT-Tabellen

 (evanhahn.com)
1 Punkte von GN⁺ 3 시간 전 | 1 Kommentare | Auf WhatsApp teilen
  • Wenn am Ende der Tabellendefinition STRICT ergänzt wird, lassen sich Typfehler wie beliebiger Text in Integer-Spalten frühzeitig verhindern und so die Datenintegrität erhöhen
  • Beim Einfügen und Aktualisieren werden Typen geprüft, Werte, die sich ohne Verlust umwandeln lassen, wie '123', sind jedoch erlaubt; Spaltentypen sind auf INT, INTEGER, REAL, TEXT, BLOB, ANY beschränkt
  • Für Spalten, die mehrere Typen enthalten müssen, kann ANY angegeben werden, sodass strenge Validierung und flexible Speicherung in derselben Tabelle kombiniert werden können
  • Bestehende Tabellen lassen sich nicht direkt in STRICT umwandeln; dafür sind das Erstellen einer neuen Tabelle und das Kopieren der Daten nötig, fehlerhafte Bestandsdaten müssen bereinigt oder typkonvertiert werden
  • STRICT-Tabellen werden erst ab SQLite 3.37.0 unterstützt; es gibt zwar theoretischen Prüfaufwand, in inoffiziellen Experimenten wurden jedoch keine deutlichen Unterschiede bei Performance oder Dateigröße festgestellt

STRICT-Tabellen erstellen

  • STRICT-Tabellen in SQLite wenden, ähnlich wie andere SQL-Engines, eine strengere Typprüfung an
  • Dazu wird am Ende der CREATE TABLE-Definition STRICT angehängt
CREATE TABLE people (name TEXT) STRICT;

Typprüfung beim Einfügen und Aktualisieren

  • Normale SQLite-Tabellen können auch Text wie 'garbage' in einer INTEGER-Spalte speichern, STRICT-Tabellen behandeln eine Typabweichung jedoch als Fehler
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- 정상 처리됨

CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- 오류: cannot store TEXT value in INTEGER column
  • Dieselbe Prüfung gilt auch für UPDATE, sodass auch beim Ändern von Werten nach dem Speichern falsche Typen verhindert werden
  • Wenn ein Wert jedoch ohne Verlust konvertiert werden kann, ist er auch in STRICT-Tabellen erlaubt
    • Der String '123' kann vollständig in den Integer 123 umgewandelt werden, daher werden die beiden folgenden Einfügungen gleich behandelt
INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

Typbeschränkungen bei der Tabellendefinition

  • In normalen Tabellen können auch Typnamen in Spaltendeklarationen verwendet werden, die SQLite nicht unterstützt
    • GARBAGE, DATETIME, JSON, UUID und BLOBB werden alle akzeptiert
    • Solche Deklarationen können Tippfehler sein oder aus einem Missverständnis der von SQLite unterstützten Datentypen entstehen
  • STRICT-Tabellen lösen bereits beim Erstellen einen Fehler aus, wenn ein nicht unterstützter Typname verwendet wird
CREATE TABLE tbl (name GARBAGE) STRICT;
CREATE TABLE tbl (name DATETIME) STRICT;
CREATE TABLE tbl (name JSON) STRICT;
CREATE TABLE tbl (name UUID) STRICT;
CREATE TABLE tbl (name BLOBB) STRICT;
  • Erlaubt sind nur die Spaltentypen INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Für jede Spalte muss ein Typ angegeben werden; CREATE TABLE tbl (name) ohne Typangabe ist daher nicht möglich

Flexibilität mit ANY beibehalten

  • Wenn Daten mit uneinheitlichen Typen gespeichert werden müssen, kann eine ANY-Spalte verwendet werden
  • Auch innerhalb einer STRICT-Tabelle lassen sich in ANY-Spalten alle Typen speichern, darunter Integer, Text, Real-Zahlen und BLOBs
CREATE TABLE tbl (value ANY) STRICT;

INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');
INSERT INTO tbl (value) VALUES (12.34);
INSERT INTO tbl (value) VALUES (X'8647');

Migration bestehender Tabellen

  • Es gibt keine Möglichkeit, eine bestehende nicht strikte Tabelle per ALTER direkt in eine STRICT-Tabelle umzuwandeln; einfacher ist es daher, sie von Anfang an strikt zu erstellen
  • Um eine bestehende Tabelle umzustellen, ist der Ablauf neue STRICT-Tabelle erstellen → Daten kopieren → bestehende Tabelle ersetzen nötig
CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;
  • Wenn in Integer-Spalten bestehender Daten etwa Text steht und die Typen damit falsch sind, kann beim Kopieren ein Fehler auftreten
    • Vor der Migration müssen Daten gegebenenfalls bereinigt oder mit CAST typkonvertiert werden
  • Eine Strategie, STRICT nur auf neue Tabellen anzuwenden, ist ebenfalls möglich; dann unterscheidet sich jedoch die Strenge der Validierung je nach Tabelle, was das Verhalten schwerer vorhersehbar machen kann als bei ausschließlich lockeren Tabellen

Wann flexible Typisierung geeignet ist

  • Die SQLite-Entwickler haben die Vorteile flexibler Typisierung separat zusammengefasst und nennen Fälle, in denen das Standardverhalten nützlich ist
  • Nicht strikte Tabellen können für folgende Zwecke sinnvoll sein
    • Ein reiner Key-Value-Store, der mehrere Typen unverändert aufnimmt
    • Ein Bereich zum Speichern sonstiger Attribute mit unterschiedlichen Typen
    • Wenn schmutzige CSV-Dateien direkt importiert werden und auch ungültige Werte ohne Verlust erhalten bleiben müssen
  • Unerwartete Typen können jedoch subtile Bugs verursachen; bei gewöhnlichen Tabellen ist es daher vorteilhafter, sofort einen Fehler auszugeben, statt sie stillschweigend zu erlauben
  • Im SQLite-Quellcode gibt es auch einen Kommentar, der nicht strikte Tabellen als legacy bezeichnet, doch das taugt schwerlich als verlässlichere Grundlage als die offizielle Dokumentation

Versionskompatibilität

  • STRICT-Tabellen wurden mit SQLite 3.37.0 eingeführt, das im November 2021 veröffentlicht wurde
  • In älteren SQLite-Versionen können STRICT-Tabellen nicht verwendet werden
  • Ältere Versionen können auch Datenbanken nicht lesen, die STRICT-Tabellen enthalten
    • Wird mit einer aktuellen SQLite-Version eine STRICT-Tabelle erstellt und anschließend dieselbe Datenbank mit SQLite 3.36.0 geöffnet, tritt ein Fehler auf

Performance und Speicherplatz

  • Da STRICT-Tabellen beim Einfügen und Aktualisieren Datentypen zusätzlich prüfen, können sie theoretisch langsamer sein
  • In einem inoffiziellen Experiment, bei dem mehrere Millionen Zeilen in eine Tabelle mit 100 Spalten eingefügt wurden, zeigte sich auf mehreren Computern kein deutlicher Performance-Unterschied
  • Auch die Größe der Datenbankdatei auf der Festplatte war gleich; da es sich aber nicht um einen gründlichen Benchmark handelte, könnten unentdeckte Unterschiede bestehen
  • Es ist auch möglich, dass sich die Performance verbessert, weil versehentlich gespeicherte Werte verhindert werden, die nicht zur Spaltenaffinität (column affinity) passen; das wurde jedoch nicht separat überprüft

Worauf bei der Einführung zu achten ist

  • STRICT-Tabellen lösen nicht alle Datenprobleme, reduzieren aber typbezogene Fehler und stärken die Datenintegrität
  • Meist genügt es, der Tabellendefinition STRICT hinzuzufügen, sodass die Einführung einfach ist
  • Bei gewöhnlichen Tabellen überwiegen die Vorteile strenger Typvalidierung gegenüber dem Migrationsaufwand, der Inkompatibilität mit älteren Versionen und der eingeschränkten Flexibilität

Verwandte Beiträge

1 Kommentare

 
GN⁺ 3 시간 전
Hacker-News-Kommentare

  • Da SQLite keine Funktion hat, um eine Tabelle per ALTER in den Strict-Modus umzuwandeln und man die Daten einer nicht-strikten Tabelle stattdessen in eine strikte Tabelle kopieren muss, wurde in sqlite-utils 4.1 eine bidirektionale Konvertierungsfunktion ergänzt
    In der CLI kann man uvx sqlite-utils transform data.db mytable --strict verwenden, in Python db.table("mytable").transform(strict=True)
    Die Release Notes gibt es unter https://sqlite-utils.datasette.io/en/stable/changelog.html#v..., die Python-API-Dokumentation unter https://sqlite-utils.datasette.io/en/stable/python-api.html#... und die CLI-Dokumentation unter https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

  • https://sqlite.org/flextypegood.html erklärt, warum flexible Typisierung der Standard ist und sich das wahrscheinlich auch künftig nicht ändern wird, aber das passt nicht zu praktischen Erfahrungen
    Anders als die Erklärung nahelegt, dass man einen Kundennamen in Customer.creditScore vom Typ Integer leicht entdecken und korrigieren könne, sind beschädigte Zeilen schwer wiederherzustellen und die Daten möglicherweise vollständig verloren
    Der Logik, dass strenge Typprüfung nur leicht auffindbare Fehler verhindere und dadurch die Erkennung und Behebung von Bugs sogar erschwere, ist ebenfalls kaum zuzustimmen

    • Die Sichtweise, dass die Sicherheit und Verlässlichkeit einer vertrauenswürdigen Datenbankkonfiguration die Behebung von Datenfehlern eher erschwere, ist schwer nachzuvollziehen
      In Postgres fügt man möglichst viele Prüfungen und Schutzmechanismen hinzu, damit man Fehler, die von vornherein nicht hätten auftreten dürfen, später gar nicht erst suchen muss
    • Auch bei MongoDB gab es eine ähnliche Logik, dass man alles Mögliche speichern könne, aber die meisten haben nach praktischer Nutzung erkannt, dass das meist eine schlechte Eigenschaft ist
      Das wirkt weniger wie ein strenges Engineering-Prinzip als wie ein Ergebnis der Zeit, in der SQLite entstand, und der starken Überzeugungen des Autors; nach viel Kritik entsteht der Eindruck, dass die Position um jeden Preis verteidigt wird
      Mit Typen wie JSON oder HSTORE in Postgres lässt sich die gewünschte Flexibilität bieten, aber fast immer ist es besser, das bei Bedarf auszuwählen, statt unbegrenzte Typisierung standardmäßig zu erzwingen

  • STRICT als Standard wäre wünschenswert
    Abgesehen davon sind die SQLite-Entwickler großartige Leute, die ein hervorragendes Werkzeug gebaut haben

    • Sogar Fremdschlüssel sind nicht standardmäßig aktiviert, sodass man PRAGMA foreign_keys = ON; verwenden muss
      Das größere Problem ist, dass es für strikte Tabellen kein entsprechendes pragma gibt und man daher an jedes CREATE TABLE das nicht standardisierte STRICT anhängen muss
      Ein globales STRICT-pragma wurde zwar erwogen, aber nicht umgesetzt; Näheres dazu unter https://sqlite.org/foreignkeys.html und https://sqlite.org/forum/forumpost/1b9d073a37ca5998
    • SQLite legt großen Wert auf Abwärtskompatibilität und ändert Standardwerte fast nie
      So soll vermieden werden, dass Software für SQLite 3.53 nach einem Upgrade auf 3.54 plötzlich mit CREATE TABLE strikte Tabellen erzeugt, Fehler auslöst und alles kaputtgeht
    • Auch bei Fremdschlüssel-Constraints gibt es ein ähnliches Problem, da sie aus Kompatibilitätsgründen standardmäßig deaktiviert sind
      Man fragt sich, ob SQLite zeitweise nur die Fremdschlüssel-Syntax unterstützt hat, ohne die Funktion tatsächlich zu implementieren
    • Es braucht auch einen richtigen Datums-/Zeitstempeltyp statt nur einfacher Strings
    • Es gibt keinen guten Grund, warum eine Datenbank versehentlich falsche Typen zulassen sollte, und die Philosophie der Typsicherheit in SQLite ist bedauerlich
      Es war überhaupt kein Vergnügen, Code zu bereinigen, der auf Tausenden von Geräten Strings '1' und '0' in einer Booleschen Spalte gespeichert hat
      Es gibt auch keinen Timestamp-Typ, also muss man in einer Textspalte speichern, und selbst die Standard-Datums-/Zeitfunktionen erzeugen yyyy-mm-dd HH:MM:SS, wobei UTC implizit angenommen wird, statt des ISO-Formats yyyy-mm-ddTHH:MM:SSZ
      SQLite ist wirklich ein großartiges Projekt, aber einige Designentscheidungen sind irritierend

  • Aus der Perspektive einer Enterprise-SQL-Umgebung habe ich SQLite nie ernsthaft in Betracht gezogen, weil Feldtypen standardmäßig nicht erzwungen werden, und selbst als es zur Grundlage für App-Metadaten auf Smartphones wurde, hat mich das überrascht.
    Das erinnert an die alte Netzwerkgeschichte, in der man sich wegen niedriger Latenz und Einfachheit für UDP entscheidet und dann den Großteil der Zuverlässigkeitsfunktionen von TCP in der Anwendung selbst wieder nachbaut.

    • Wenn man es selbst implementiert, kann man es anders als TCP bauen und manchmal große Vorteile erzielen; QUIC und HTTP/3 sind dafür Beispiele.
      Aber es scheint nicht so, als entstünden vergleichbare Stärken, wenn man Typprüfungen selbst auf ein nicht-striktes SQLite aufsetzt.
    • Fremdschlüssel wurden vor 2009 überhaupt nicht unterstützt und sind bis heute nicht standardmäßig aktiviert: https://sqlite.org/foreignkeys.html
    • Manchmal lässt sich ein solcher Ansatz sogar geschäftlich erfolgreich machen: https://aeron.io/
    • Wenn die Typprüfung nicht vollständig unterstützt wird, kann ich nachvollziehen, dass man SQLite nicht ernst nimmt, aber die meisten Datenbanken liefern ebenfalls keine perfekten Standardeinstellungen.
      Auch PostgreSQL ist in den Defaults auf schwache Systeme ausgelegt, sodass für optimale Leistung Konfiguration nötig ist.
      Außerdem ist TCP streambasiert, daher haben viele Anwendungen Probleme mit HOL-Blocking; wenn man eine Zuverlässigkeitsschicht selbst über UDP baut, kann man nach viel Arbeit auch ein besseres Ergebnis als mit TCP erreichen.
    • Wenn du dich für einen Jeep oder Bronco interessierst, würdest du auch keine gewöhnlichen Autokritiker suchen.
      Sie würden bemängeln, dass sie laut sind und sich schlecht fahren, aber sie beurteilen Produkte für einen anderen Einsatzzweck so, als wäre nur ihr eigener Use Case relevant.

  • Strikte Tabellen haben den Nachteil, dass sich einige Typen wie Date nicht verwenden lassen, sollten aber trotzdem der Standard sein.
    Wenn mehrere Anwendungen sich eine Datenbank teilen, muss man deklarierten Typen vertrauen können; speichert eine davon Strings in einer Zahlenspalte, gehen alle anderen kaputt.
    Andererseits wird die eingebettete Datenbank, der primäre Einsatzzweck von SQLite, meist nur von einer einzigen Anwendung verwendet; dort hat es auch Vorteile, das Schema weiterentwickeln zu können, statt eine neue Datenbank zu erzeugen und zu kopieren.
    In diesem Fall weiß der Anwendungscode, was in jeder Spalte zu erwarten ist, auch bei gemischten Typen.

    • SQLite hat nur 5 Datentypen: INTEGER, TEXT, BLOB, REAL, NUMERIC: https://sqlite.org/datatype3.html
    • SQLite hat keinen Datentyp für Datum, und es gibt auch keine Möglichkeit, EXPLAIN auf ein beliebiges SELECT-Query anzuwenden, um den formal angegebenen Typnamen zu erhalten.
      Deshalb lässt sich selbst bei Namen wie DATE oder DATETIME nicht einmal darauf schließen.
    • Date ist kein echter Typ, sondern erzeugt nur eine Spalte mit numerischer Affinität.

  • Der strikte Modus beschränkt die Schreibweise von Spaltentypen, sodass man keine aussagekräftigeren Namen verwenden kann, und verhindert damit eher ein strengeres Typsystem auf Anwendungsebene, weil der Code diese Namen beim Mapping zwischen Datenbanktypen und Anwendungstypen nicht nutzen kann.
    Mehr dazu steht unter https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type=comment, und wenn man mit Crates wie sqlx in Rust mit Datenbanken arbeitet, halte ich es für besser, den strikten Modus zu vermeiden.

  • Bei Datenbanken und RPC möchte ich strikte Typen, aber SQLite verfolgt einen etwas anderen Zweck; wenn man es direkt nutzt, versteht man vielleicht eher https://sqlite.org/flextypegood.html.
    Das Ziel, dass auch irgendein beliebiges Skript, das nicht für SQLite geschrieben wurde, zufällig funktioniert, ist etwa eine Eigenschaft, die bei anderen DBMS normalerweise nicht mitgedacht wird.

    • Im Abschnitt zu Vor- und Nachteilen des Dokuments fehlt das zentrale Argument.
      Der Hauptvorteil flexibler Typisierung ist, dass sich Schemas leicht weiterentwickeln lassen.
      Wenn sich die Anforderungen an eine eingebettete Datenbank ändern, kann man das Schema direkt an Ort und Stelle ändern, statt jedes Mal eine neue Datenbank zu erstellen und Daten zu migrieren; und wenn nur eine Anwendung liest und schreibt, ist es keine Überraschung, wenn zu einer Integer-Spalte auch Strings dazukommen.
      Wenn dagegen mehrere Anwendungen denselben Datenbankbestand teilen und jeweils nach ihrem eigenen Zeitplan aktualisiert werden, dann ist das Schema ein Vertrag, und ein einzelner falscher Typ kann andere Anwendungen zerstören.
      Wenn eine Anwendung eine Tabelle erstellt und eine andere sie nutzt, müssen die vereinbarten Datentypen strikt eingehalten werden.
    • Die meisten prüfen schon zur Compile-Zeit, ob der Code der Typstruktur entspricht, und das parallel entwickelte SQLite-Schema liefert die für statische Analyse nötigen Typinformationen.
      Eine zusätzliche Prüfung zur Laufzeit bringt praktisch keinen Gewinn, und Code, bei dem die statische Analyse bestätigt hat, dass er einen String einfügt, mutiert nicht plötzlich dazu, einen Integer einzufügen.
      SQLite ist nicht wie Postgres, wo mehrere Anwendungen Daten teilen und man Dritten vertrauen muss.
      In einer Umgebung wie Postgres ist Laufzeitvalidierung essenziell, aber SQLite wurde grundsätzlich für eine Anwendung und eine Datenbank entworfen, daher muss man nur dem eigenen Code vertrauen, den man zur Compile-Zeit bewerten kann.
      In dem untypischen Fall, dass mehrere Anwendungen eine Datei teilen, kann man strikte Tabellen aktivieren.

  • Dynamische Datentypen mögen für Zwecke wie einen einfachen Key-Value-Store passen, aber man sollte sich fragen, was eher dem Prinzip der geringsten Überraschung entspricht: dass INTEGER ohne Fehler 'hello world' akzeptiert, oder dass man nur mit einem Keyword wie NONSTRICT oder dem Typ ANY solche Werte einfügen kann.
    Die meisten SQLite-Nutzer würden vermutlich nicht erwarten, dass Ersteres möglich ist.

    • Das könnte daran liegen, dass SQLite zwar eine typfreie Datenbank anstrebte, die Syntax aber wie Standard-SQL aussehen lassen wollte.
      Das passt auch zur Erklärung, dass Code für andere DBMS zufällig auch unter SQLite funktionieren soll.
      Trotzdem ist es äußerst überraschend, dass ein Nutzer nicht etwa den Typ weggelassen hat, sondern explizit INTEGER angibt und dennoch Text einfügen kann.

  • Es gab einmal einen Fall, in dem eine UUID mit einem Wert wie 08123… begann und als Oktalzahl geparst wurde, wodurch ein Teil fälschlich in eine Zahl umgewandelt wurde.
    Das war verwirrend und lästig, ließ sich aber durch STRICT und das komplette Neuerstellen der Tabelle beheben.

    • Das lag nicht an SQLite, sondern daran, dass der Sprachtreiber zu hilfreich sein wollte.
      SQLite kennt keinen Oktalzahl-Typ.

  • Standardmäßig CREATE TABLE ... STRICT WITHOUT ROWID verwenden; ich sehe keinen guten Grund, es anders zu machen