SQLite a aussi besoin d’éditions à la Rust

 (mort.coffee)
3 points par GN⁺ 4 시간 전 | 2 commentaires | Partager sur WhatsApp
  • SQLite est un RDBMS autonome largement utilisé pour le stockage local et les projets embarqués, mais il conserve des valeurs par défaut défavorables à la sécurité et aux performances pour des raisons de rétrocompatibilité
  • Par défaut, il n’impose pas les contraintes de clés étrangères et peut réutiliser les ROWID, ce qui peut créer des problèmes d’intégrité des données où les publications d’un utilisateur supprimé se retrouvent associées à un autre utilisateur ayant reçu le même ID
  • Les tables ordinaires stockent aussi des types de données différents de ceux des colonnes déclarées ; les tables STRICT l’empêchent, mais il faut indiquer strict pour chaque table, ce qui entre en conflit avec les usages fondés sur des noms de types personnalisés
  • Le SQLITE_BUSY immédiat lors d’écritures concurrentes, le WAL désactivé et les réglages de synchronisation conservateurs peuvent être améliorés respectivement via les pragmas busy_timeout, journal_mode et synchronous
  • Permettre de choisir un ensemble de valeurs par défaut sûres via des éditions basées sur l’année, comme PRAGMA edition = 2026, permettrait de faire évoluer les réglages par défaut de SQLite sans casser les comportements existants

Les forces de SQLite et ses valeurs par défaut vieillissantes

  • SQLite peut être considéré comme la base de données des projets embarqués et le standard de fait du stockage local ; certains logiciels serveur, comme lobste.rs, fonctionnent aussi sur SQLite
  • Contrairement aux RDBMS traditionnels exécutés comme processus séparés, c’est un RDBMS sous forme de bibliothèque, ce qui permet de garder un logiciel autonome
  • Comme avec un format de fichier ordinaire, il n’est pas nécessaire d’écrire un sérialiseur et un parseur personnalisés ; SQLite offre donc à la fois les avantages d’un RDBMS et ceux du stockage fondé sur des fichiers
  • Mais plusieurs valeurs par défaut inadaptées continuent de poser problème, car elles affectent l’intégrité des données, la concurrence et les performances

Problèmes d’intégrité causés par l’absence d’application des clés étrangères

  • Les clés étrangères sont un moyen essentiel de maintenir la cohérence d’une base de données et d’éviter les références cassées, mais SQLite ne les impose pas par défaut
  • Même si posts.user_id est déclaré comme référence à users.id, la configuration par défaut n’empêche pas les comportements suivants
    • créer une publication sans ID utilisateur valide
    • supprimer un utilisateur tout en conservant ses publications
  • INTEGER PRIMARY KEY devient un alias du ROWID de la table, et l’algorithme d’attribution des ROWID de SQLite réutilise des ID dans certaines situations
    • Si Bob écrit une publication avec l’ID 1 puis supprime seulement son compte, aucune erreur de clé étrangère ne se produit
    • Si Alice reçoit ensuite l’ID 1 réutilisé, les anciennes publications de Bob se retrouvent associées à Alice
    • Comme les requêtes elles-mêmes réussissent normalement, le problème est plus difficile à détecter qu’une simple référence cassée
  • On peut imposer les clés étrangères en activant le pragma suivant pour chaque connexion
PRAGMA foreign_keys = ON;
  • Si cela avait été appliqué dès le départ, la suppression d’un utilisateur ayant encore des publications aurait été interrompue par une erreur FOREIGN KEY constraint failed

Les tables ordinaires acceptent des types différents de ceux déclarés

  • Les valeurs SQLite sont de type NULL, INTEGER, REAL, TEXT ou BLOB, mais la déclaration des colonnes d’une table ordinaire ne limite pas strictement les types stockables : elle détermine une affinité de type (type affinity)
  • Les règles de stockage suivantes s’appliquent à une colonne d’affinité INTEGER
    • un TEXT interprétable comme un entier est converti en INTEGER
    • un TEXT interprétable comme un réel est converti en REAL, un flottant double précision
    • les autres valeurs sont stockées telles quelles, avec leur type d’origine
  • Les autres affinités ont leurs propres règles de conversion
    • l’affinité BLOB stocke les valeurs telles quelles
    • l’affinité TEXT conserve BLOB, TEXT et NULL tels quels, et convertit les nombres en TEXT
    • l’affinité REAL se comporte comme l’affinité INTEGER, mais convertit les entiers en REAL
  • Ainsi, même dans une colonne duration_sec INTEGER, on peut stocker une chaîne non numérique comme 'Way too long, I mean come on'
  • Dans un projet réel, il est aussi arrivé que des chaînes '1' et '0' soient enregistrées au lieu des nombres 1 et 0 utilisés comme booléens, ce qui a nécessité nettoyage des données et débogage

Tables STRICT et conflit avec les types personnalisés

  • Les tables STRICT lèvent des erreurs de type, comme cannot store TEXT value in INTEGER column, lorsqu’une valeur du mauvais type est insérée dans une colonne
CREATE TABLE music (
  id INTEGER PRIMARY KEY,
  name TEXT,
  duration_sec INTEGER
) strict;

  • Comme il n’existe pas de pragma rendant globalement toutes les tables strictes, il faut ajouter strict à chaque instruction CREATE TABLE sans exception

  • SQLite défend l’idée que les types flexibles sont utiles, mais même dans une table STRICT, il est possible de créer une colonne acceptant toutes sortes de valeurs en déclarant le type ANY

    • On peut distinguer explicitement les colonnes autorisant n’importe quel type de celles qui imposent un type précis

  • Le problème de compatibilité le plus concret est que les tables STRICT ne se contentent pas d’imposer les types : elles changent aussi les règles d’interprétation des spécificateurs de type

  • Comment les tables ordinaires interprètent les noms de types

    • Les tables SQLite ordinaires déterminent l’affinité à partir de la chaîne de type déclarée
    • Si elle contient "INT", elle est traitée comme INTEGER
    • Si elle contient "CHAR", "CLOB" ou "TEXT", elle devient TEXT
    • Si elle contient "BLOB", ou si le type est omis, elle est traitée comme BLOB
    • Si elle contient "REAL", "FLOA" ou "DOUB", elle devient REAL
    • Si aucune condition ne s’applique, elle est traitée comme NUMERIC
    • En combinant ce typage souple avec ces règles, on peut attacher aux colonnes des noms de types personnalisés comme DATETIME, KEY_VALUE_SET ou COLOR
    • Les connecteurs ou wrappers de base de données peuvent regarder le nom du type pour sérialiser et désérialiser automatiquement
    • Même sans fonctionnalité dédiée, le nom du type documente en lui-même les données attendues dans la colonne
    • Passer simplement STRICT en valeur par défaut obligerait à renoncer à cet usage ; il faudrait donc des alias explicites de types personnalisés
    CREATE TYPE KEY_VALUE_SET = TEXT;
    • Si des alias comme KEY_VALUE_SET pouvaient être utilisés dans les tables strictes, les schémas pourraient aussi consigner le motif de données attendu pour les colonnes TEXT que l’application doit parser
    • Une fonctionnalité permettant d’associer des contraintes CHECK aux types personnalisés serait aussi utile
    • La norme SQL:1999 comprend déjà une syntaxe d’alias de type prenant en charge les contraintes, CREATE DOMAIN ; SQLite pourrait donc l’implémenter en prenant en charge cette instruction standard

Écritures concurrentes et SQLITE_BUSY

  • SQLite autorise plusieurs lectures concurrentes, mais ne traite qu’une seule écriture à la fois
  • Avec la configuration par défaut, lorsque deux processus tentent simultanément d’obtenir un verrou d’écriture, l’un d’eux reçoit immédiatement une erreur SQLITE_BUSY au lieu d’attendre
    • Comme une écriture qui effectue des I/O disque peut naturellement prendre du temps, il est plus logique d’attendre un certain délai que le verrou soit libéré
    • Il est déjà arrivé que des systèmes réels s’interrompent par intermittence, obligeant à écrire soi-même une boucle de retry
  • Le réglage suivant ne renvoie SQLITE_BUSY qu’après avoir réessayé d’obtenir le verrou pendant au maximum 5 secondes
PRAGMA busy_timeout = 5000;
  • En exploitation normale, il est préférable de concentrer toutes les écritures dans un seul processus, si possible dans un seul thread, et les écritures concurrentes ne peuvent de toute façon pas être rapides
  • Il existe toutefois des cas d’exploitation légitimes où des écritures concurrentes sont nécessaires
    • nettoyer une base de données de manière interactive avec l’outil en ligne de commande sqlite3
    • exécuter des scripts pour des tâches d’administration rares sans frontend séparé
  • À cause des réglages par défaut, ces opérations peuvent provoquer un SQLITE_BUSY inattendu et interrompre le logiciel en cours d’exécution

WAL et valeurs par défaut de performance

  • Bien configuré, SQLite peut être assez rapide pour remplir une partie des rôles habituellement confiés à de gros serveurs comme PostgreSQL ou MySQL, mais ses performances par défaut ne sont pas bonnes
  • On trouvera plus de détails sur l’optimisation en environnement serveur dans Optimizing SQLite for servers
  • Le principal problème de valeur par défaut concerne le Write-Ahead Log, c’est-à-dire le fait que le WAL est désactivé
PRAGMA journal_mode = WAL;
  • Dans la plupart des situations, WAL augmente fortement la vitesse d’écriture
  • Utiliser aussi le réglage suivant permet de réduire fortement le nombre de synchronisations disque sans risque de corruption des données
PRAGMA synchronous = NORMAL;

Éditions SQLite basées sur l’année

  • Changer directement les valeurs par défaut actuelles casserait d’anciens logiciels et pourrait rendre les utilisateurs méfiants vis-à-vis des futures mises à jour de SQLite
  • Pour conserver la rétrocompatibilité tout en permettant de choisir de meilleures valeurs par défaut, on pourrait ajouter un super pragma
PRAGMA edition = 2026;
  • L’édition 2026 agirait au minimum comme un alias pour les réglages suivants
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
  • Dans cette édition, le mode strict serait aussi appliqué par défaut aux nouvelles tables
  • Comme les éditions de Rust, ce mécanisme permettrait de choisir explicitement de nouveaux comportements par défaut tout en préservant le comportement du code existant
  • Contrairement à un mode unique comme "use strict"; en JavaScript, les éditions basées sur l’année peuvent faire évoluer, au fil du temps, l’ensemble des valeurs par défaut jugées raisonnables
  • Par exemple, si WAL2 de Hctree était intégré à la branche principale en 2034, un futur PRAGMA edition = 2034 pourrait être configuré pour choisir journal_mode = WAL2
  • Un système d’éditions pourrait constituer un compromis permettant à SQLite de faire évoluer ses valeurs par défaut de sécurité et de performance sans casser le comportement par défaut des programmes existants

À lire aussi

2 commentaires

 
tomskang 2 시간 전

La théorie paraît intéressante, mais si on prend en compte les conflits de compatibilité et le surcoût, j’ai l’impression que ce serait plutôt une perte.
SQLite est un SGBD né pour fonctionner dans des environnements embarqués comme Aegis,
et dans le monde de l’embarqué, on caste des void** à tout-va et on redéfinit des fonctions après une déclaration __weak__. Dans un tel univers, je me demande vraiment si c’est nécessaire.
 
GN⁺ 4 시간 전
Avis sur Lobste.rs

  • J’ai écrit ce billet après avoir vu hier la nouvelle indiquant que lobste.rs était passé à SQLite (https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite)
    En lisant la discussion de l’époque, je me suis rappelé les petits désagréments des valeurs par défaut de SQLite et j’en ai fait un billet de blog

    • J’ai lu cette discussion moi aussi et suis arrivé à la même conclusion
      Si tu as le temps, ça vaudrait le coup de partager ce billet aussi sur le forum SQLite : https://sqlite.org/forum/forum

  • En SQL, CREATE TYPE sert à créer des types composites, et Postgres prend en charge avec cela les plages, les énumérations, les tableaux, ainsi que des types de base assez complexes
    La fonctionnalité souhaitée se rapproche plutôt de CREATE DOMAIN. On peut créer un nouveau type (newtype) à partir d’un type concret de base et aussi définir des valeurs par défaut et des contraintes, ce qui rend beaucoup plus concis un schéma où l’on répète la même contrainte CHECK dans plusieurs tables. Par exemple, on peut écrire create domain mything as text; ou create domain mything as integer not null check (VALUE < 5);

    • J’aurais dû vérifier d’abord s’il existait déjà une fonctionnalité d’alias de type. J’ajouterai ce point à l’article

  • Il faudrait aussi traiter un piège assez dangereux : busy_timeout seul ne permet pas d’éviter les erreurs SQLITE_BUSY qui surviennent immédiatement
    Si l’on démarre une transaction avec une requête en lecture seule, puis qu’on exécute une requête d’écriture pour tenter de promouvoir le verrou détenu, et qu’une autre écriture est en cours, cela échoue immédiatement, indépendamment du réglage de busy_timeout. Pour résoudre le problème, il faut utiliser begin immediate

    • Je sais que ce n’est pas un vrai bug, mais honnêtement c’est un comportement qui ressemble presque à un bug

  • Il faut tenir compte du fait que certains des paramètres regroupés dans l’édition proposée sont, dans le modèle de SQLite, des propriétés de connexion, tandis que les autres sont des propriétés du fichier de base de données ou de chaque table
    PRAGMA journal_mode = WAL est conservé dans le fichier de base de données, mais PRAGMA foreign_keys = ON doit être défini à chaque connexion. STRICT est une propriété de chaque table, ce qui permet de mélanger, dans une même base de données, des tables strictes et d’autres qui ne le sont pas. Il faut donc décider si ce PRAGMA unifié doit être défini comme une propriété du fichier de base de données ou de la connexion. Je me demande aussi s’il existe un mécanisme intégré qui avertit pour éviter qu’une ancienne version de la bibliothèque ne modifie et ne casse un fichier contenant des fonctionnalités qu’elle ne connaît pas

  • L’approche par éditions semble globalement être un bon compromis entre l’objectif des développeurs de SQLite de préserver la rétrocompatibilité et des valeurs par défaut moins déroutantes pour les utilisateurs qui veulent les réglages modernes recommandés

  • Je suis récemment devenu à fond sur RocksDB. C’est une bibliothèque de base de données embarquée comme SQLite, mais je considère justement que l’absence de SQL est un gros avantage
    La contrainte consistant à devoir exprimer toutes les opérations en SQL me semble surtout gêner la manipulation de la base de données

    • RocksDB est une base de données clé-valeur et SQLite une base de données relationnelle ; ce sont donc des concepts distincts, conçus pour des objectifs différents
      J’ai du mal à imaginer comment migrer vers RocksDB, ou une autre base de données clé-valeur, un système qui utilise SQL comme lobste.rs

  • Je ne suis pas d’accord pour dire que définir busy_timeout est la solution. SQLite est très performant lorsqu’on dispose d’un unique thread d’écriture et qu’on traite par lots des transactions logiques indépendantes les unes des autres
    La méthode précise est expliquée ici : https://andersmurphy.com/2025/12/…

    • Pour les charges de travail courantes, il vaut mieux limiter l’écriture à une seule opération et ne pas concevoir une architecture où plusieurs écritures sont en concurrence permanente
      Mais il est aussi courant de modifier directement la base de données pour des tâches de nettoyage, ou d’exécuter en script des opérations d’administration ponctuelles qui ne justifient pas la création d’une interface d’administration séparée. Dans ce cas, il n’est pas souhaitable qu’un serveur ou une application puisse s’arrêter de façon aléatoire simplement parce que, par malchance, les moments d’exécution se sont chevauchés