SQLite : moteur BDR embarqué

Généralités sur les bases de données

SQLite est un moteur de base de données. Une base de données (BDD) est un outil permettant de stocker de nombreuses données dans un ensemble structuré. Plutôt que d’éparpiller l’enregistrement de l’information dans plusieurs fichiers différents, la bases de données relationnelle centralise l’information pour y accèder simplement.

Les Systèmes de Gestion de Base de Données Relationnelles (SGBDR) sont les outils permettant de gérer des ensembles de données complexes. Parmi les solutions payantes : IBM DB2, Oracle, Microsoft SQL Server,… et les solutions gratuites : PostGreSQL, SQLite, MySQL,… Un tel système permet donc de simplifier la mise à jour et/ou la suppression des données mais aussi d’éviter les incohérences (doublons).

Quel que soit le serveur cible, la formulation des requêtes est standardisée dans un langage commun : le langage SQL (Structured Query Language).

Le modèle client/serveur

Chacun des systèmes cités ci-dessus fonctionne selon le modèle client/serveur :

  • le coeur de l’application (BDD + moteur) est installé sur un serveur ;
  • l’application cliente est installé sur des postes de travail, les clients.

Chaque client accède, selon les autorisations définies par l’administrateur de BDD, aux données de la base par un protocole d’échange. Les échanges entre le client et le serveur sont fait de requêtes et de réponses. Une fois connecté au serveur, le client peut interroger le serveur à l’aide de requête (extraction, ajout, suppresion, mise à jour de données,…). Le serveur exécute la requête et retourne les résultats au client.

Le modèle embarqué

Les SGBDR client/serveur sont des candidats sérieux pour des volumes de données massifs mais impose l’installation de solutions lourdes. Ces outils ne sont pas adaptés aux applications embarquées sur un Raspberry. Alors comment embarquer ses données persistentes au sein même de l’application et les gérer de manière relationnelle ?

On peut envisager d’embarquer les données dans l’application à l’aide d’un fichier texte. Toutes les données enregistrées sont accessibles de manière séquentielle et l’on peut effectuer des recherches sous réserve que les critères de recherche ne soient pas trop complexes. En effet, un simple fichier montrera trés vite ses limites dès lors qu’on souhaitera :

  • effectuer des sélections et des tris sur les données ;
  • gérer des volumes de données importants ;
  • relier les données entre elles par des relations hierarchiques ;
  • gérer les accès parallèles de plusieurs utilisateurs.

Avec ce type de BDD élementaire (et non relationnelle), on sera trés rapidement confronté à des problèmes de structuratuiion de données et de performances.

Pour embarquer ses données persistentes de manière relationnelle dans l’application, SQLite est la solution idéale.

SQLite 3

SQLite est un moteur de base de données relationnelles. Il ne fonctione pas selon le modèle client/serveur, il est directement embarqué dans les applications qui l’utilisent. Les données ainsi que la structure des tables, les index et tout le nécessaire est stocké dans un fichier.

SQLite est libre, gratuit et fourni dans la bibliothèque standard de Python.

Ultra-dominant sur le marché des base de données embarquées, SQLite est le moteur de base de données le plus utilisé au monde. Il est notamment utilisé par Firefox pour stocker les onglets ouverts et trés répandu sur les terminaux mobiles iPhone et Android.

SQLite est une solution légère pour gérer une base de données relationnelles de taille raisonnable. SQLite ne gère pas les utilisateurs et les droits d’accès : il s’agit d’un outil monoutilisateur. On peut à minima restreindre les accès au fichier via la gestion des droits système.

Ecrit en C, SQLite respecte la spécification SQL-92 à quelques exceptions près, dont l’absence de GRANT ou de REVOKE puisque les droits ne sont pas gérés.

La structure de la base de données SQLite

Les SGDB classiques imposent pour chaque colonne un type et potentiellement une longueur tel que VARCHAR(32) pour une chaine de 32 caractères maximum ou INTEGER(4) pour un entier signé représenté sur 4 octets. Pour optimiser le stockage et l’accès aux données, chaque donnée renseignée dans une colonne doit respecter le type attendu. A défaut, la donnée est supprimée, ignorée ou encore tronquée.

L’orientation de SQLite est différente. La donnée n’est pas altérée et sera toujours stockée, même si elle ne correspond pas au type attendu : avec SQLite, le typage est dynamique. Cela signifie que le type n’est pas associé à une colonne intégralement mais unitairement à chaque donnée selon ce qu’elle contient. Le type d’une donnée est appelée classe de stockage.

Les classes de stockage SQLite

Les données contenues dans une base de données SQLite possèdent l’un des types suivants :

TypeDescription
NULLreprésente la données NULL
INTEGERentier stocké automatiquement sur 1, 2, 3 4, 6 ou 8 octets selon sa taille
REALnombre à virgule flottante stocké sur 8 octets
TEXTchaine de caractère stockée selon l'encodage de la base de données (UTF-8, UTF-16BE ou UTF-16LE)
BLOBbinaire stocké tel quel

Il n’y a pas de classe de stockage pour les booléens qui sont stockés sous forme d’entier (False : 0 et True : 1). Les dates non plus n’ont pas de format dédié. On pourra choisir parmi les classes de stockage suivantes :

  • TEXT : stockage de la date au format ISO 8601 (YYYY-MM-DD HH:MM:SS.SSS) ;
  • REAL :stockage sous la forme d’un jour julien dont la partie flottante représente les heures, minutes et secondes ;
  • INTEGER : stockage sous forme d’un timestamp Unix.

Les types d’affinité

Il est néanmoins possible d’affecter un type à une colonne : il s’agit d’un type d’affinité, c’est-à-dire un type qui sera préférentiellement choisi pour stocker la donnée à enregistrer. A défaut, si le typage préférentiel n’est pas possible sur la donnée d’entrée, une classe de stockage plus « générique » sera alors choisie.

Le tableau ci-dessous décrit l’influence des types d’affinités sur la classe de stockage choisie selon la donnée d’entrée à stocker.

Type d'affinitéDonnée d'entréeClasse de stockage choisie pour stocker la donnée
TEXTNULLNULL
TEXTChaine de caractères (tout type de caractères) TEXT
TEXTOctet ne faisant pas partie de la table d'encodageBLOB
REALNULLNULL
REALNombre entierREAL
REALNombre réelREAL
REALChaine de caractères numérique représentant un nombre réel ou nonREAL
REALChaine de caractère ne représentant pas un nombre ou octet faisant partie de la table de caractères d'encodageTEXT
REALOctet ne faisant pas partie de la table d'encodageBLOB
INTEGERNULLNULL
INTEGERNombre entierINTEGER
INTEGERNombre réel avec partie décimale nulleINTEGER
INTEGERNombre réel avec partie décimale non nulleREAL
INTEGERChaine de caractères numérique représentant un nombre entier ou réel avec partie décimale nulleINTEGER
INTEGERChaine de caractères numérique représentant un nombre réel avec partie décimale non nulleREAL
INTEGERChaine de caractère ne représentant pas un nombre ou octet faisant partie de la table de caractères d'encodageTEXT
INTEGEROctet ne faisant pas partie de la table d'encodageBLOB
NUMERICNULLNULL
NUMERICNombre entierINTEGER
NUMERICNombre réel avec partie décimale nulleINTEGER
NUMERICNombre réel avec partie décimale non nulleREAL
NUMERICChaine de caractères numérique représentant un nombre entier ou réel avec partie décimale nulleINTEGER
NUMERICChaine de caractères numérique représentant un nombre réel avec partie décimale non nulleREAL
NUMERICChaine de caractère ne représentant pas un nombre ou octet faisant partie de la table de caractères d'encodageTEXT
NUMERICOctet ne faisant pas partie de la table d'encodageBLOB
NONEALLClasse de stockage utilisant le type de la donnée d'entrée

On remarque les points suivants :

  • la donnée NULL est stockée avec le type NULL, quel que soit le type d’affinité ;
  • le type d’affinité NONE est sans influence sur la classe de stockage choisie qui sera identique au type de la donnée d’entrée ;
  • lorsque conversion vers le type d’affinité souhaité est possible, alors elle est réalisée. Dès que la conversion n’est plus possible, c’est la classe de stockage la plus proche qui est choisie pour stocker l’information sans l’altérer. Par exemple, l’insertion de la chaîne de caractères ‘42.1’ dans une colonne de type d’affinité INTEGER entrainera une altération de la donnée si la classe de stockage était INTEGER puisqu’on stockerait le chiffre entier 42. C’est donc la classe de stockage REAL qui est choisie alors que le type d’affinité était INTEGER.

Pour illustrer ces propos, créons une table « example » de 5 colonnes, chacune ayant un type d’affinité différent :

Exécutons les réquêtes d’insertions suivantes :

Selon la valeur de la donnée DATA et donc son type, la classe de stockage dépendra du type d’affinité de la colonne dans laquelle on souhaite insérer la donnée. Le tableau suivant énumère les types de données enregistrées :

Type d'affinité'42.0''42.1'42.042x'0042'NULL
TEXTTEXT ('42.0')TEXT ('42.1')TEXT ('42.0')TEXT ('42')BLOBNULL
NUMERICINTEGER (42)REAL (42.1)INTEGER (42)INTEGER (42)BLOBNULL
INTEGERINTEGER (42)REAL (42.1)INTEGER (42)INTEGER (42)BLOBNULL
REALREAL (42.0)REAL (42.1)REAL (42.0)REAL (42.0)BLOBNULL
NONETEXT ('42.0')TEXT ('42.1')REAL (42.0)INT (42)BLOBNULL

On se résume, dans une base de données SQLite :

  • les données peuvent être de 5 types différents : NULL, TEXT, REAL, INTEGER et BLOB ;
  • chaque colonne aura un typage préférentiel vers lequel les données seront converties si possible ;
  • au sein d’une même colonne, les données peuvent avoir des types différents.

 Déclaration des colonnes

On vient de voir les mécanismes en jeu pour la typages des données stockées en base. Même si SQLite ne manipule que 5 types de données (NULL, TEXT, REAL, INTEGER et BLOB), il est tout à fait possible de déclarer une table SQLite en utilisant des types utilisés dans les SGBD classiques.

Par exemple, la requête suivante :

créera une table ayant les mêmes types d’affinités que la requête suivante :

De manière générale, les équivalences sont les suivantes :

  • tous les types contenant INT (INT, INTEGER, TINYINT, UNSIGNED BIG INT, INT8,…) seront associés au type d’affinité INTEGER ;
  • tous les types contenant CHAR (CHAR, VARCHAR, VARYING CHARACTER,…) ainsi que TEXT et CLOB seront associés au type d’affinité TEXT ;
  • les types REAL, DOUBLE, FLOAT seront associées au type d’affinité REAL ;
  • les types NUMERIC, DECIMAL, BOOLEAN, DATE et DATTIME seront associés au type d’affinité NUMERIC ;
  • le type BLOB est associé au type d’affinité NONE.

A mon sens, si le script SQL n’a pas vocation à étre transposé sur un autre SGBD, il est préférable d’utiliser exclusivement les types d’affinité SQLite.

Clé primaire et index d’unicité d’une base de données SQLite

SQLite présente une particularité quant aux clés primaires. Il est possible de créer une table de la manière suivante :

Aucune clé primaire n’a été spécifiée mais l’unicité de chaque enregistrement est gérée par SQLite. Lorsque l’on fait un SELECT * sur cette table, l’ensemble des données de chaque colonne est retourné. Même si elle ne s’affiche pas, chaque table d’une base SQlite possède une colonne suppémentaire automatiquement créée par SQLite. Nommée index d’unicité, cette colonne ROWID contient un entier unique dans la table pour chaque enregistrement. Les données sont stockées sous forme d’arbre binaire contenant une entrée pour chaque ligne utilisant la valeur ROWID comme clé. Les recherhces et tri des enregistrement par ROWID sont rapides.

On peut spécifier une contrainte PRIMARY KEY sur une (ou plusieurs) colonnes de la table pour définir la clé primaire. Si la contrainte PRIMARY KEY porte sur une unique colonne de type INTEGER comme avec la requête suivante :

alors un alias est crée vers la colonne de type ROWID.

Les contraintes

A l’aide de quelques mots clés, SQLite permet de gérer des contraintes sur une ou plusieurs colonnes. Le tableau ci-dessous décrit les mots clés :

Mot cléDescriptionPortée de la contrainte appliquable sur plusieurs colonnes
NOT NULLLa valeur NULL n'est pas admiseNON
AUTOINCREMENTmodifie l'algorithme de génération des index d'unicitéNON
CHECKla donnée doit respecter une contrainteOUI
DEFAULTla donnée possède une valeur par défautNON
COLLATEméthode de comparaison de la donnéeNON
PRIMARY KEYla (ou les) colonne(s) est (sont) clé(s) primaire(s)OUI
UNIQUEla donnée de la colonne (ou le n-uplet des colonnes) est uniqueOUI
FOREIGN KEYla donnée de la colonne est une clé étrangèreOUI

Les contraintes PRIMARY KEY et NOT NULL

Qu’elle porte sur une ou plusieurs colonnes, il ne peut y avoir qu’une seule PRIMARY KEY par table. La contrainte PRIMARY KEY est optionnelle sur les tables avec ROWID.

Contrairement au standard SQL, SQLite autorise la valeur NULL pour une PRIMARY KEY* (pour des raisons de conpatiblités avec les versions antérieures buguées). L’unicité des lignes est tout de même assurée car les valeurs NULL seront considérées distinctes de toutes les autres valeurs, y compris les autres valeurs NULL.

* Sauf si la colonne est INTEGER PRIMARY KEY ou s’il s’agit d’une table sans ROWID ou encore si la colonne est déclarée avec la contrainte NOT NULL.

On pourra combiner les contraintes PRIMARY KEY et NOT NULL pour interdire la valeur NULL sur la clé primaire.

La contrainte UNIQUE

Les contraintes UNIQUE et PRIMARY KEY sont similaires à l’exception faite qu’une même table peut avoir plusieurs contraintes UNIQUE mais une seule PRIMARY KEY. Avec la contrainte UNIQUE également, les valuers NULL sont considérées distinctes les unes des autres.

La contrainte AUTOINCREMENT

Par défaut, les index d’unicité garantissent seulement qu’à chaque instant, aucun enregistrement ne peut avoir le même identifiant qu’un autre enregistrement. Quand on fait un INSERT, si la valeur à insérer dans de la colonne ROWID ou INTEGER PRIMARY KEY n’est pas précisée, elle sera renseignée automatiquement avec un integer non utilisé (généralement, un incrément du plus grand ROWID utilisé à l’instant t) que le mot clé AUTOINCREMENT soit précisé ou non.

Avec AUTOINCREMENT, on ajoute une contrainte supplémentaire qui garanti que chaque numéro est unique à chaque instant mais aussi qu’il ne pourra pas être réutilisé, même après suppression de l’enregistrement, tout au long de la vie de la base de données.

La contrainte COLLATE

La contrainte COLLATE accepte les valeurs suivantes :

  • BINARY : option par défaut, méthode de comparaison binaire sans se se soucier de l’encodage des caractères ;
  • RTRIM : comme BINARY, mais les caractères espaces sont ignorés lors de la comparaison ;
  • NOCASE : comme BINARY mais aucune distinction ne sera faite entre les les 26 lettres majuscules de l’alphabet et leurs équivalentes minuscules, la comparaison ne sera pas « case sensitive ».

Sauf si on précise une méthode avec la contrainte COLLATE dans une requête SELECT dans laquelle la clause WHERE compare deux champs de la table, alors c’est la méthode de comparaison du premier champs cité dans la requête qui est utilisée.

Des exemples complets sont proposés sur le site officiel à l’adresse : www.sqlite.org/datatype3.html.

La contrainte CHECK

La contrainte CHECK ne peut pas contenir une requête. Si l’on souhaite forcer le type des colonnes, on peut le faire avec la contrainte CHECK :

Voilà, c’était plutôt long mais il s’agit d’une introduction nécessaire pour comprendre les mécanismes de stockages des données avec SQLite. On peut maintenant envisager d’embarquer les données dans une application Python sans qu’il soit nécessaire d’installer quoi que ce soit d’autre et les performances seront au rendez-vous, même sur un système embarqué tel que le Raspberry.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *