Types de données SQLite : Examen approfondi des classes de stockage

SQLite utilise un système de types flexible basé sur des classes de stockage et l'affinité des types. Contrairement aux SGBDR traditionnels qui imposent un typage strict, SQLite permet une gestion plus dynamique des types. Il est important de bien comprendre ce modèle afin de garantir la cohérence des données et d'éviter les erreurs subtiles dans la logique de l'application.

Dans cet article, nous explorerons le système de types SQLite et nous vous montrerons comment travailler avec les types de données courants dans SQLite. Si vous débutez avec SQL, nous vous recommandons de commencer par notre cours Introduction à SQL afin d'acquérir des bases solides. De plus, je trouve que l'aide-mémoire SQL Basics Cheat Sheet, que vous pouvez télécharger, est une référence utile car il contient toutes les fonctions SQL les plus courantes.

Concepts fondamentaux du système de types SQLite

Pour travailler avec SQLite, il est essentiel de comprendre comment il stocke et interprète les données en interne. Je vais vous expliquer ci-dessous comment un système de type SQLite conçoit des applications fiables et efficaces.

Explication des classes de stockage

SQLite utilise cinq classes de stockage pour représenter les valeurs en interne :

• NULL représente une valeur manquante ou non définie.

• INTEGER représente un entier signé stocké dans 1 à 8 octets, selon la magnitude.

• REAL décrit un nombre à virgule flottante stocké sous la forme d'une valeur IEEE-754 de 8 octets.

• TEXT stocke des chaînes de texte encodées en UTF-8, UTF-16BE ou UTF-16LE.

• BLOB stocke les données binaires exactement telles qu'elles ont été saisies, sans aucune transformation.

Types de données SQLite. Source de l'image : OpenAI

Ces classes de stockage diffèrent des types de données déclarés dans un schéma de tableau. Une colonne peut être déclarée comme VARCHAR(50) ou BOOLEAN, mais la valeur réelle est stockée à l'aide de l'une des cinq classes de stockage en fonction de son contenu. Cette séparation confère à SQLite sa nature dynamique et flexible, mais elle exige également des développeurs qu'ils soient attentifs à la validation des données et à la cohérence des types dans le code de leur application.

Affinité de type et comportement en colonne

Dans SQLite, l'affinité de type est un type recommandé attribué à une colonne en fonction de son type de données déclaré. SQLite utilise les règles suivantes pour déterminer l'affinité à partir du texte du type déclaré.

• Les colonnes déclarées comme « VARCHAR », « CHAR » ou « TEXT » obtiennent une affinité « TEXT ».

• Les types tels que INT et INTEGER ont une affinité INTEGER.

• Les déclarations telles que « DECIMAL », « NUMERIC » ou « BOOLEAN » obtiennent l'affinité « NUMERIC ».

• Les types tels que REAL, FLOAT ou DOUBLE ont une affinité REAL.

• L'affinité par défaut est BLOB si aucun type reconnu n'est déclaré.

Par exemple, une colonne déclarée comme « VARCHAR(100) » aura une affinité « TEXT », ce qui signifie que SQLite tentera de convertir les valeurs en texte lors de leur stockage. Cette affinité détermine la manière dont SQLite convertit et stocke les données, mais n'empêche pas le stockage d'autres types dans cette colonne.

La typologie manifeste et ses implications

SQLite utilise le typage explicite, ce qui signifie que le type de données est associé à la valeur individuelle, et non au conteneur de colonnes. Cela permet à plusieurs types de valeurs de coexister dans une même colonne. Par exemple, une colonne déclarée comme INTEGER peut contenir des valeurs TEXT ou BLOB.

Le typage manifeste permet aux développeurs d'insérer divers types de données sans modifier le schéma. Cependant, cela peut entraîner des données incohérentes, un tri imprévisible et une plus grande dépendance à la logique de l'application pour appliquer les règles de type.

Je vous recommande de suivre notre cours sur la conception de bases de données afin d'apprendrela gestion des bases de données,notamment l'organisation des données dans différents types de données et la gestion des bases de données.

Utilisation des types de données courants dans SQLite

Nous allons maintenant examiner comment traiter les types de données courants dans SQLite, tels que le texte, les nombres, les booléens, les dates et les données binaires.

Texte, nombres et booléens

Les déclarations de données couramment utilisées dans SQLite comprennent TEXT, INTEGER, REAL, NUMERIC et BOOLEAN, mais les valeurs sont stockées en fonction de leur affinité de type et non de leur type strict. Voici des cas d'utilisation pour chaque type de données dans un schéma réel.

• TEXT stocke des chaînes de caractères, adaptées aux noms, adresses e-mail, URL et données textuelles générales.

• INTEGER est idéal pour les nombres entiers tels que les comptes, les identifiants et les codes, offrant un stockage et un tri efficaces.

• REAL stocke les nombres à virgule flottante pour les mesures, les devises ou les valeurs non entières.

• L'affinité NUMERIC couvre les valeurs déclarées comme DECIMAL, BOOLEAN ou similaires. En fonction de la valeur, SQLite tente de les stocker sous la forme INTEGER ou REAL.

• BOOLEAN n'est pas une classe de stockage native, mais est simulée en stockant FALSE ( 0 ) ou TRUE ( 1 ) comme valeurs d'INTEGER en arrière-plan.

Je recommande d'utiliser INTEGER pour les booléens avec des contraintes explicites côté application, tandis que NUMERIC convient aux valeurs nécessitant une analyse numérique flexible.

Dates, heures et stratégies temporelles

SQLite ne dispose pas de type natif pour les dates et les heures ( DATE ou DATETIME ), mais prend en charge les formats suivants pour le stockage des données temporelles :

• TEXT: Enregistre les dates/heures sous forme de chaînes ISO8601 telles que « YYYY-MM-DD HH:MM:SS ». Ce format est lisible par l'homme et compatible avec de nombreux outils, mais peut être plus lent pour les comparaisons.

• REAL: Stocke les dates sous forme de nombres de jours juliens (virgule flottante), ce qui est utile pour les calculs de dates et les requêtes de plage, mais moins lisible.

• INTEGER: Stocke les horodatages Unix, en secondes depuis le 1er janvier 1970. Il est efficace pour le stockage et les comparaisons numériques rapides, mais n'est pas lisible par l'homme.

Lorsque vous travaillez avec des données temporelles, vous pouvez utiliser les fonctions suivantes : date(), time(), datetime(), julianday() et strftime() pour analyser, formater et convertir différents formats temporels.

Cas limites binaires et numériques

SQLite prend également en charge les données binaires via la classe de stockage ` BLOB `. Les BLOB sont utiles pour stocker des images, des fichiers ou toute autre donnée non textuelle. Lorsque vous travaillez avec des types de données d'BLOB, veuillez toujours utiliser des requêtes paramétrées ou des liaisons pour insérer des BLOB. Vous pouvez également stocker des fichiers binaires volumineux en externe et y faire référence à l'aide de chemins d'accès ou de hachages.

Bien que SQLite puisse stocker des entiers jusqu'à 8 octets (-2^63 à 2^63-1), les valeurs dépassant cette plage peuvent être converties de manière silencieuse en type de données REAL. Cela peut entraîner une perte de précision des valeurs. SQLite ne garantit pas une précision décimale stricte. Par conséquent, veuillez toujours utiliser des bibliothèques externes ou une logique d'application pour les données financières lorsque vous avez besoin de précision.

Application d'une structure avec des tableaux STRICT

Contrairement au système de typage flexible traditionnel, SQLite a introduit les tableaux de type « STRICT » dans la version 3.37.0. Un tableau d'STRICT s fournit un mode de typage plus strict qui impose une correspondance exacte des types pour les valeurs des colonnes. Lorsque vous déclarez un tableau comme étant de typ STRICT, chaque valeur insérée doit correspondre au type déclaré de la colonne, et toute incompatibilité entraîne une erreur. 

Le mode « STRICT » améliore la cohérence et la validation des données. Cela permet également de détecter rapidement les erreurs liées au type lors du développement et rend le comportement de SQLite plus prévisible, plus proche des SGBDR traditionnels.

Avant d'utiliser STRICT en tant que développeur, veuillez noter qu'il n'est disponible que dans SQLite 3.37.0 et versions ultérieures. De plus, vous ne pouvez pas mélanger les types d'STRICT s et les types manifestes dans le même tableau.

Considérations relatives aux performances et à la conception

SQLite dispose d'un système de types flexible qui a des implications sur les performances. SQLite utilise un encodage à longueur variable pour les valeurs d'INTEGER s et d'REAL s, ce qui signifie que les nombres plus petits utilisent moins d'octets. Cela contribue à réduire la taille de la base de données, en particulier pour les ensembles de données volumineux. 

L'affinité de type influence également la manière dont les valeurs sont stockées et comparées. Les types incohérents dans une colonne peuvent réduire l'efficacité des index, car SQLite peut effectuer des conversions de type supplémentaires lors des recherches, ce qui ralentit les requêtes.

Veuillez envisager les stratégies suivantes pour concevoir des schémas et des requêtes efficaces.

• Définissez des colonnes avec des affinités claires et cohérentes afin de minimiser la surcharge liée à la coercition de type.

• Envisagez d'utiliser des transtypages explicites dans les requêtes lorsque la conversion de type est nécessaire afin de garantir un comportement cohérent.

• Utilisez l'affinité INTEGER pour les identifiants numériques et les compteurs, REAL pour les données à virgule flottante et TEXT pour les chaînes de caractères.

• Évitez de mélanger différents types de données dans la même colonne afin d'éviter des problèmes de tri et de comparaison.

• Normalisez les schémas afin d'éviter les conversions de types redondantes ou la duplication des données.

• Veuillez utiliser EXPLAIN QUERY PLAN pour analyser les performances et affiner les requêtes.

Exemples de types de données SQLite avec des modèles de schéma

Les schémas de tableaux suivants illustrent l'utilisation réfléchie des types de données, de l'affinité des types et des contraintes de SQLite.

Tableau des utilisateurs avec simulation booléenne et contraintes

Dans l'exemple ci-dessous :

• is_active simule une valeur booléenne à l'aide de l'attribut « INTEGER » avec une contrainte « CHECK ».

• TEXT Le type de données garantit la cohérence des valeurs de chaîne, tandis que le type booléen ( INTEGER ) est utilisé pour la logique booléenne.

 -- Create users table with auto-incrementing rowid and enforce uniqueness
CREATE TABLE users (
    id INTEGER PRIMARY KEY, 
    username TEXT NOT NULL UNIQUE, 
    email TEXT NOT NULL, 
    is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))  -- Simulates boolean
);

Tableau des commandes utilisant des données temporelles et la précision numérique

Dans la requête ci-dessous, total utilise NUMERIC pour les valeurs de type monétaire afin de gérer la précision au niveau de l'application. order_date est stocké sous le format ISO-8601 TEXT, ce qui garantit la lisibilité et la compatibilité avec les fonctions de date.

-- Create orders table with row identifier and foreign key in practice
-- ISO date format "YYYY-MM-DD"
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,  
    customer_id INTEGER NOT NULL, 
    total NUMERIC NOT NULL, 
    order_date TEXT NOT NULL CHECK (length(order_date) = 10)  );

Tableau de fichiers avec vérification du type et du format binaire

Dans la requête ci-dessous, content utilise BLOB pour stocker des données binaires brutes. uploaded_at stocke l'heure sous forme d'INTEGER pour gagner de l'espace et améliorer les performances.

-- Create files table with raw binary data and Unix timestamp (epoch seconds)
CREATE TABLE files (
    file_id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    content BLOB NOT NULL,  
    uploaded_at INTEGER NOT NULL 
);

Vous pouvez vérifier le type lors de l'exécution à l'aide de la requête suivante :

SELECT typeof(uploaded_at), typeof(content) FROM files;

Je vous recommande de suivre notre cours Introduction aux bases de données relationnelles en SQL pour en savoirplus sur les structures de données et la création de tableaux dans les bases de données.

Comparaison de SQLite avec d'autres bases de données

Le système de types de SQLite est plus flexible que celui des SGBDR traditionnels tels que MySQL ou PostgreSQL. Le tableau ci-dessous compare SQLite à ces bases de données.

Conclusion

SQLite dispose d'un système de typage flexible qui offre divers avantages lorsqu'il est utilisé de manière réfléchie. Les développeurs peuvent concevoir des schémas qui équilibrent adaptabilité et fiabilité en comprenant les classes de stockage, l'affinité des types et le typage manifeste. Des fonctionnalités telles que les tableaux d'STRICT, l'utilisation cohérente des types et la normalisation contribuent à renforcer la structure lorsque cela est nécessaire.

Ensuite, je vous recommande de suivre nos cours Analyse exploratoire des données dans SQL et Manipulation des données dans SQL afin d'apprendre à analyser différents types de données à l'aide de requêtes SQL avancées.