Si créer un CMS de blog basé sur SQlite est une chose, promouvoir SQLite comme base de données en est une autre. Avec ce premier billet je commence une petite série de tutoriels sur SQlite, ses fonctionnalités, ses particularités et ses défauts.

Une base de données est avant tout faite pour stocker des informations dans différents types de champs adaptés aux données qu'ils doivent stocker, que ce soit des données numériques (entier ou à virgule, négatif ou positif), du texte ou encore des dates. Chaque SGBDR utilise des types de champs statiques (INT, VARCHAR, TEXT, DATE, etc) sauf SQLite qui lui utilise un typage dynamique des champs.

En effet avec SQLite le type de donnée est associée à la donnée elle-même, et non pas à la colonne comme c'est le cas dans les autres SGBDR. Concrètement ce n'est pas le type de la colonne qui va déterminer comment doit être manipulée la donnée mais la donnée elle-même.

5 classes de stockage, pas une de plus

Alors qu'avec MySQL on a à notre disposition plus d'une vingtaine de types de champs différents, avec SQLite on a 5 classes pour stocker et manipuler les données. Ces classes permettent de couvrir la totalité des données manipulées par les bases de données.

• INTEGER : pour tous les nombres entiers, qu'ils soient signés ou non. Utilisée également pour les clef primaires.
• REAL : pour les nombres à virgules.
• TEXT : cette classe est utilisée pour toutes les données textuelles.
• BLOB : pour stocker et manipuler les données sous leur forme orginelle. par exemple pour stocker un boléen.
• NULL : utilisée si la données est NULL.

Pour illustrer voilà un petit exemple sous forme d'une requête d'insertion de différentes données :

INSERT INTO table VALUES('texte', 3.14159, 42, '12');

Avec cette requête, la première donnée sera manipulée comme du TEXT, la seconde selon le type REAL, la troisième selon le type INTEGER et la dernière avec le type TEXT puisque le nombre a été quoté il est considéré comme du texte.

Comprendre le principe d'affinité des colonnes

Lors de la création d'une table, on va définir pour chaque colonne son affinité, c'est à dire le type de données qu'il est recommandé de stocker dans la colonne. Mais ce n'est qu'une recommandation, pas une obligation qui permet donc une grande souplesse dans le stockage des données.

SQlite propose 5 types d'affinités pour chaque colonne : TEXT, NUMERIC INTEGER REAL et NONE. Ainsi lors d'une insertion dans une table, SQLite va essayer de rendre compatible la donnée insérée avec l'affinité de la colonne.

Avec une colonne d'affinité TEXT, les types de classes NULL, TEXT et BLOB sont compatibles. En revanche si l'on veut insérer une donnée numérique (type INTEGER ou REAL), elle sera "convertie" en TEXT.

Pour une colonne d'affinité NUMERIC, le système va convertir les données en classe INTEGER ou REAL en essayant de ne pas perde d'information. Si cela ne marche pas les données seront alors stockées selon la classe TEXT.

Avec les colonnes d'affinité INTEGER, le traitement est le même qu'avec les colonnes d'affinité NUMERIC sauf que les nombres à virgules seront tronqués pour être stockés selon la classe INTEGER. Un 3.14159 deviendra 3.

A l'inverse avec les colonnes d'affinité REAL, les entiers seront convertis en nombres à virgule. Enfin l'affinité NONE permet de stocker les données en gardant leur classe d'origine.

L'affinité pour la compatibilité avec les autres SGBDR

Les concepteurs de SQLite en ont profité pour rendre compatible leur système avec les autres SGBDR. Si vous essayer de créer une table SQLite avec une requête de création de MySQL, il n'y aura probablement pas de problème. SQlite va, pour chaque type de colonne MySQL, y associer une affinité de colonne afin de rendre par la suite la table entièrement compatible avec SQlite.

Par exemple avec un champ de type INT, on obtiendra une colonne d'affinité INTEGER, pour un champ de type VARCHAR(120) on obtiendra une colonne d'affinité TEXT.

J'espère au final que je ne vous ai pas trop embrouillé l'esprit avec les affinités et les classes de stockage. Au début on a du mal mais au final on trouve cela plus simple à utiliser. La conception de la table restant toutefois une étape clef de l'utilisation de SQLite.