Dans SQLite, privilégiez les tables STRICT

 (evanhahn.com)
1 points par GN⁺ 3 시간 전 | 1 commentaires | Partager sur WhatsApp
  • Ajouter STRICT à la fin de la définition d’une table permet de bloquer plus tôt les erreurs de type, comme du texte arbitraire dans une colonne entière, et de renforcer l’intégrité des données
  • Lors des insertions et mises à jour, les types sont vérifiés, mais les valeurs convertibles sans perte comme '123' restent autorisées, et les types de colonnes sont limités à INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Pour les colonnes devant accepter plusieurs types, ANY permet de combiner validation stricte et stockage flexible au sein d’une même table
  • Il n’est pas possible de convertir directement une table existante en STRICT ; il faut créer une nouvelle table et copier les données, avec nettoyage ou conversion des données invalides si nécessaire
  • Les tables STRICT ne sont prises en charge qu’à partir de SQLite 3.37.0 et, même s’il existe en théorie un coût de vérification, des tests non officiels n’ont pas montré de différence nette de performances ni de taille de fichier

Créer une table STRICT

  • Les tables STRICT de SQLite appliquent une vérification de type plus stricte, comparable à celle d’autres moteurs SQL
  • Il suffit d’ajouter STRICT à la fin de la définition CREATE TABLE
CREATE TABLE people (name TEXT) STRICT;

Vérification de type à l’insertion et à la mise à jour

  • Dans une table SQLite classique, une colonne INTEGER peut contenir du texte comme 'garbage', mais une table STRICT traite ce mismatch de type comme une erreur
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- traité normalement

CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- erreur : cannot store TEXT value in INTEGER column
  • La même validation s’applique aussi à UPDATE, ce qui empêche également les types invalides lors d’une modification après stockage
  • En revanche, si une valeur peut être convertie sans perte, elle reste autorisée dans une table STRICT
    • La chaîne '123' peut être intégralement convertie en entier 123, donc les deux insertions ci-dessous sont traitées de la même manière
INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

Limitation des types dès la définition de la table

  • Dans une table classique, on peut utiliser dans la déclaration d’une colonne des noms de type que SQLite ne prend pas réellement en charge
  • Avec une table STRICT, utiliser un nom de type non pris en charge provoque une erreur dès la création
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;
  • Les seuls types de colonnes autorisés sont INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Un type doit être indiqué pour chaque colonne ; il n’est donc pas possible de l’omettre comme dans CREATE TABLE tbl (name)

Conserver de la flexibilité avec ANY

  • Si vous devez stocker des données dont le type n’est pas fixe, vous pouvez utiliser une colonne ANY
  • Même dans une table STRICT, une colonne ANY peut contenir tous les types : entiers, texte, nombres réels, BLOB, etc.
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 d’une table existante

  • Il n’existe pas de méthode ALTER pour convertir directement une table non stricte en table STRICT ; il est donc plus simple de créer les tables de manière stricte dès le départ
  • Pour convertir une table existante, il faut passer par création d’une nouvelle table STRICT → copie des données → remplacement de l’ancienne table
CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;
  • Si les données existantes ont de mauvais types, par exemple du texte dans une colonne entière, la copie peut échouer
    • Avant la migration, il peut être nécessaire de nettoyer les données ou d’utiliser CAST pour les convertir
  • Il est aussi possible de n’appliquer STRICT qu’aux nouvelles tables, mais cela introduit des niveaux de validation différents selon les tables, ce qui peut rendre le comportement moins prévisible qu’avec un schéma uniformément souple

Quand le typage flexible peut être approprié

  • L’équipe de SQLite a aussi documenté séparément les avantages du typage flexible, avec des cas où le comportement par défaut est utile
  • Une table non stricte peut être raisonnable pour :
    • un stockage clé-valeur pur qui conserve différents types tels quels
    • un espace destiné à stocker des attributs hétérogènes
    • l’import direct de CSV désordonnés lorsqu’il faut préserver même les valeurs invalides sans perte
  • Mais des types inattendus peuvent provoquer des bugs subtils ; pour des tables générales, il est souvent préférable d’obtenir une erreur immédiate plutôt que de les accepter silencieusement
  • Le code source de SQLite contient aussi un commentaire qualifiant les tables non strictes de legacy, mais c’est un indice moins fiable que la documentation officielle

Compatibilité des versions

  • Les tables STRICT ont été introduites dans SQLite 3.37.0, publié en novembre 2021
  • Elles ne peuvent pas être utilisées avec des versions antérieures de SQLite
  • Les anciennes versions ne peuvent pas non plus lire une base de données contenant des tables STRICT
    • Si vous créez une table STRICT avec une version récente puis ouvrez la même base avec SQLite 3.36.0, une erreur se produit

Performances et espace de stockage

  • Les tables STRICT effectuent une vérification supplémentaire du type des données lors des insertions et mises à jour, ce qui pourrait en théorie les rendre plus lentes
  • Dans des tests non officiels avec l’insertion de plusieurs millions de lignes dans une table de 100 colonnes, aucune différence de performances nette n’a été observée sur plusieurs machines
  • La taille du fichier de base de données sur disque était également identique, même s’il ne s’agissait pas d’un benchmark rigoureux et que certaines différences ont pu passer inaperçues
  • Il est aussi possible que le fait d’éviter l’enregistrement accidentel de valeurs incompatibles avec l’affinité des colonnes améliore au contraire les performances, mais cela n’a pas été vérifié séparément

Points à évaluer avant adoption

  • Les tables STRICT ne résolvent pas tous les problèmes de données, mais elles réduisent les erreurs liées aux types et renforcent l’intégrité des données
  • Dans la plupart des cas, il suffit d’ajouter STRICT à la définition de la table, ce qui rend l’adoption simple
  • Pour des tables classiques, les bénéfices d’une validation stricte des types l’emportent généralement sur le coût de la migration, l’incompatibilité avec les anciennes versions et la flexibilité réduite

À lire aussi

1 commentaires

 
GN⁺ 3 시간 전
Commentaires sur Hacker News

  • Comme SQLite ne permet pas de passer une table en mode strict avec ALTER et qu’il faut copier les données d’une table non stricte vers une table stricte, une conversion dans les deux sens a été ajoutée à sqlite-utils 4.1
    Dans le CLI, on peut l’utiliser avec uvx sqlite-utils transform data.db mytable --strict, et en Python avec db.table("mytable").transform(strict=True)
    Les notes de version sont disponibles sur https://sqlite-utils.datasette.io/en/stable/changelog.html#v..., la documentation de l’API Python sur https://sqlite-utils.datasette.io/en/stable/python-api.html#..., et la documentation du CLI sur https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

  • https://sqlite.org/flextypegood.html explique pourquoi le typage flexible est la valeur par défaut et pourquoi cela ne changera probablement pas, mais cela ne correspond pas à l’expérience réelle
    Contrairement à l’idée qu’on pourrait facilement repérer et corriger le fait d’avoir mis le nom d’un client dans l’entier Customer.creditScore, une ligne corrompue est difficile à récupérer et les données ont peut-être complètement disparu
    Il est aussi très difficile d’adhérer à l’argument selon lequel des vérifications de type strictes ne bloqueraient que des erreurs faciles à trouver et rendraient donc la détection et la correction des bugs plus difficiles

    • Il est difficile de comprendre l’idée selon laquelle la sécurité et la fiabilité apportées par une configuration de base de données plus robuste rendraient au contraire les bugs de données plus difficiles à corriger
      Dans Postgres, on a plutôt tendance à ajouter autant de vérifications et de garde-fous que possible, ce qui évite ensuite d’avoir à traquer des erreurs qui n’auraient jamais dû se produire
    • MongoDB avait aussi une logique similaire selon laquelle on peut tout stocker, mais la plupart des gens ont fini par constater à l’usage que c’est généralement une mauvaise propriété
      Cela ressemble moins à un principe d’ingénierie rigoureux qu’au produit de l’époque où SQLite a été conçu et des convictions très fortes de son auteur, avec l’impression qu’après de nombreuses critiques, il maintient sa position en mobilisant n’importe quelle justification
      Des types comme le JSON ou HSTORE de Postgres permettent d’obtenir la flexibilité souhaitée, mais il est presque toujours préférable de la proposer comme un choix quand on en a besoin plutôt que d’imposer par défaut un typage illimité

  • Ce serait bien que STRICT soit la valeur par défaut
    Mis à part ce point, le développeur de SQLite est une personne remarquable qui a créé un excellent outil

    • Même les clés étrangères ne sont pas activées par défaut, il faut utiliser PRAGMA foreign_keys = ON;
      Plus gênant encore, il n’existe pas de pragma équivalent pour les tables strictes, donc il faut ajouter le STRICT non standard à chaque CREATE TABLE
      Un pragma global STRICT a bien été envisagé, mais n’a pas été implémenté ; on peut consulter https://sqlite.org/foreignkeys.html et https://sqlite.org/forum/forumpost/1b9d073a37ca5998
    • SQLite accorde une grande importance à la rétrocompatibilité et change très rarement les valeurs par défaut
      L’idée est d’éviter qu’un logiciel prévu pour SQLite 3.53, une fois mis à niveau vers 3.54, se mette soudainement à créer des tables strictes avec CREATE TABLE, provoque des erreurs, puis casse l’ensemble
    • Les contraintes de clé étrangère souffrent d’un problème similaire, elles aussi désactivées par défaut pour des raisons de compatibilité
      On en vient à se demander si, à une époque, SQLite ne prenait en charge que la syntaxe des clés étrangères sans en implémenter réellement la fonctionnalité
    • Il faudrait aussi un vrai type date/horodatage, pas juste des chaînes de caractères
    • Il n’y a aucune raison qu’une base de données autorise par erreur l’insertion d’un mauvais type, et la philosophie de SQLite sur la sûreté des types est décevante
      Il a fallu nettoyer du code déployé sur des milliers d’appareils qui stockait les chaînes '1' et '0' dans une colonne booléenne, ce qui n’avait rien d’agréable
      Il n’existe pas non plus de type horodatage, donc il faut les stocker dans des colonnes texte, et même les fonctions standard de date et d’heure produisent yyyy-mm-dd HH:MM:SS, qu’il faut supposer implicitement en UTC, au lieu du format ISO yyyy-mm-ddTHH:MM:SSZ
      SQLite est vraiment un excellent projet, mais certaines décisions de conception sont déconcertantes

  • En venant d’environnements SQL d’entreprise, je n’ai jamais vraiment pris SQLite au sérieux parce que les types de champs n’y sont pas imposés par défaut, et j’ai aussi été surpris quand il est devenu la base des métadonnées d’applications sur smartphone
    Cela me rappelle cette vieille histoire réseau où l’on choisit UDP pour sa faible latence et sa simplicité, puis où l’on reconstruit directement dans l’application la plupart des mécanismes de fiabilité de TCP

    • Quand on l’implémente soi-même, on peut le faire différemment de TCP et parfois obtenir un gros avantage, QUIC et HTTP/3 en sont des exemples
      Mais je ne vois pas en quoi réimplémenter soi-même la vérification de types au-dessus d’un SQLite non strict apporterait des avantages comparables
    • Les clés étrangères n’étaient même pas prises en charge avant 2009 et ne sont toujours pas activées par défaut : https://sqlite.org/foreignkeys.html
    • Cette approche peut aussi réussir commercialement dans le monde réel : https://aeron.io/
    • Je peux comprendre qu’on ne prenne pas SQLite au sérieux si la vérification de types n’est pas pleinement prise en charge, mais la plupart des bases de données n’offrent pas non plus une configuration par défaut parfaite
      PostgreSQL aussi vise par défaut des systèmes modestes, donc il faut le configurer pour obtenir les meilleures performances
      De plus, TCP est fondé sur les flux, ce qui entraîne des problèmes de blocage HOL dans beaucoup d’applications, et si l’on construisait soi-même une couche de fiabilité sur UDP, on pourrait au final obtenir de meilleurs résultats que TCP malgré tout le travail supplémentaire
    • Si vous vous intéressez aux Jeep ou aux Bronco, vous n’irez pas chercher un critique automobile généraliste
      Ils vont dire qu’elles sont bruyantes et se conduisent mal, mais c’est parce qu’ils jugent avec leur propre cas d’usage un produit conçu dès le départ pour autre chose

  • Les tables strictes ont l’inconvénient de ne pas permettre certains types comme Date, mais elles devraient quand même être la valeur par défaut
    Si plusieurs applications partagent une base de données, il faut pouvoir faire confiance aux types déclarés, et si l’une stocke une chaîne dans une colonne numérique, toutes les autres sont cassées
    À l’inverse, dans le cas d’usage principal de SQLite comme base embarquée, il n’y a généralement qu’une seule application, donc il y a aussi l’avantage de pouvoir faire évoluer le schéma au lieu de créer une nouvelle base et copier les données
    Dans ce cas, le code de l’application sait à quoi s’attendre dans chaque colonne, y compris en présence de types mixtes

    • SQLite n’a que 5 types de données : INTEGER, TEXT, BLOB, REAL, NUMERIC : https://sqlite.org/datatype3.html
    • SQLite n’a pas de type date, et il n’existe pas non plus de moyen d’appeler EXPLAIN sur une requête SELECT arbitraire pour récupérer le nom de type indiqué de façon purement formelle
      Donc même si vous utilisez des noms comme DATE ou DATETIME, on ne peut même pas les inférer
    • Date n’est pas un vrai type, cela crée seulement une colonne avec une affinité numérique

  • Le mode strict limite la notation des types de colonnes et empêche d’utiliser des noms plus parlants ; du coup, le code ne peut pas exploiter ces noms lorsqu’il fait la correspondance entre types de base de données et types applicatifs, ce qui entrave au contraire un système de types plus strict au niveau de l’application
    Plus de détails ici : https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type... ; si vous manipulez la base avec des crates comme sqlx en Rust, je pense qu’il vaut mieux éviter le mode strict

  • Je veux des types stricts pour les bases de données et les RPC, mais SQLite a un usage un peu différent, et en l’utilisant directement vous comprendrez peut-être mieux https://sqlite.org/flextypegood.html
    Par exemple, viser le fait qu’un script arbitraire non écrit pour SQLite puisse quand même fonctionner par hasard est une propriété que les autres SGBD ne prennent généralement pas en compte

    • Dans la section avantages/inconvénients de ce document, il manque l’argument central
      Le principal avantage du typage flexible est de faire évoluer facilement le schéma
      Quand les besoins d’une base embarquée changent, on peut modifier le schéma sur place au lieu de créer une nouvelle base et de migrer les données, et si une seule application lit et écrit, ce n’est pas surprenant qu’une chaîne apparaisse dans une colonne entière
      À l’inverse, si plusieurs applications se mettent à jour chacune à leur rythme et partagent la même base de données, le schéma est un contrat, donc un seul type erroné peut casser les autres applications
      Si une table créée par une application est utilisée par une autre, les types de données convenus doivent être respectés strictement
    • Dans la plupart des cas, on vérifie à la compilation que le code respecte la structure de types, et le schéma SQLite développé en parallèle fournit les informations nécessaires à l’analyse statique
      Revérifier à l’exécution n’apporte pas de gain réel, et un code que l’analyse statique a confirmé comme insérant une chaîne ne va pas muter soudainement pour insérer un entier
      SQLite n’est pas comme Postgres, où plusieurs applications partagent les données et où il faut faire confiance à des tiers
      Dans un environnement comme Postgres, la validation à l’exécution est indispensable, mais SQLite est fondamentalement conçu pour une application et une base de données, donc il suffit de faire confiance à son propre code que l’on peut évaluer à la compilation
      Dans la situation atypique où plusieurs applications partagent un seul fichier, on peut activer les tables strictes

  • Le typage dynamique peut convenir à des usages comme un simple magasin clé-valeur, mais il faut se demander laquelle des deux options respecte le plus le principe de moindre surprise : que INTEGER accepte 'hello world' sans erreur, ou qu’il faille expliciter un mot-clé comme NONSTRICT ou un type ANY pour pouvoir insérer une telle valeur
    J’imagine que la grande majorité des utilisateurs de SQLite ne s’attendent pas à ce que la première possibilité existe

    • C’est peut-être parce que SQLite visait une base de données sans types tout en voulant donner à la syntaxe un aspect proche du SQL standard
      Cela correspond aussi à l’explication selon laquelle on espère que du code prévu pour d’autres SGBD fonctionnera aussi par hasard dans SQLite
      Malgré tout, c’est très surprenant qu’un utilisateur qui n’a pas omis le type puisse écrire INTEGER et y insérer du texte

  • Il m’est déjà arrivé qu’un UUID commençant par une valeur comme 08123… soit interprété en octal, ce qui a converti à tort une partie en nombre
    C’était déroutant et pénible, mais j’ai réglé le problème en appliquant STRICT et en recréant toute la table

    • Ce n’était pas SQLite, mais un pilote du langage qui a été trop serviable
      SQLite n’a pas de type octal

  • J’utilise par défaut CREATE TABLE ... STRICT WITHOUT ROWID, et je ne vois pas vraiment de raison de faire autrement