Cet article est divisé en 3 parties :

• Quels sont les impacts d’un mode offline sur le fonctionnement d’un app mobile ?
• Les spécificités de SQLite
• Les différents type connecteurs SQLite

Quels sont les impacts d’un mode offline sur le fonctionnement d’une app mobile ?

Avant de se lancer tête baissée dans la mise en place d'un mode offline sur app mobile il faut analyser les impacts.
La première étape consiste à définir les données qui seront sauvegardées en local, leur durée de vie...

Techniquement cela ajoute aussi de nouveaux challenges :

• Gérer la création de la base de données
• Gérer les montées de version de la base de données

Pour adresser de façon correcte ces nouveaux challenges il faut bien comprendre le fonctionnement de SQLite.

Les spécificités de SQLite

SQLite gère :

• Les index
• Les clefs primaires
• Les clefs étrangères
• Les contraintes
• Les triggers
• Les vues
• Les transactions

En revanche SQLite ne gère pas :

• Les procédures stockées
• RIGHT ou FULL OUTERJOIN

Les types

• NULL. The value is a NULL value.
• INTEGER. Un nombre entier signé stocké dans 1, 2, 3, 4, 6, or 8 bytes selon la magnitude de la valeur
• REAL. Un nombre a virgule flottante
• TEXT. Une valeur de type texte
• BLOB. Une valeur de type blob

Mais où est le type date?

Il n'existe pas. Par contre 3 types sont adaptés pour stocker la date :

• TEXT au format ISO860 ("YYYY-MM-DD HH:MM:SS.SSS")
• REAL représentant le nombre de jours juliens depuis le 24 novembre 4714 avant J.C.
• INT le nombre de secondes depuis 1970-01-01 00:00:00 UTC

SQLite fournit une fonction date permettant de convertir un INT ou un REAL en date

Exemple d'utilisation de la fonction date :

SELECT date(maColonneDateTime) FROM matable;
--OUTPUT : 2018-02-13

Comme pour la date, SQLite fournit une fonctions time permettant de convertir un INT ou un REAL en temps

Exemple d'utilisation de la fonction time :

SELECT time(maColonneDateTime) FROM matable;
--OUTPUT : 10:19:23

Modifier la structure des tables

La montée de version d'une structure d'une base de données n'est pas si simple qu'il n'y parait.

Le ALTER TABLE supporte :

• Le renommage de table
• L’ajout de colonne :
• Mais pas les colonnes de type PRIMARY KEY
• Ou avec une valeur par défaut CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, ou une expression
• Si la colonne n’autorise pas les NULL il faut préciser une valeur par défaut
• …
• On ne peut pas supprimer une colonne
• On ne peut pas supprimer un index
  

On peut contourner ces limites de la façon suivante :

--Désactivation des contraintes sur les clefs étrangères
PRAGMA foreign_keys=off;

--Début de la transaction
BEGIN TRANSACTION;

--Renommage de la table
ALTER TABLE table1 RENAME TO _table1_old;

--Recréation de la table
CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

-- Insertion des anciennes données 
INSERT INTO table1 (column1, column2, ... column_n)
  SELECT column1, column2, ... column_n
  FROM _table1_old;

-- Suppression de la table temporaire
DROP TABLE _table1_old;
COMMIT;

PRAGMA foreign_keys=on;

L'affinité

Pour être « compatible » avec les autres moteurs de base de données, SQLite définit des affinités entres les types non gérés par SQLite.
L'affinité est une sorte d'alias entre les types SQL d'autres vers les 5 types de celui-ci.
Par exemple si vous déclarez une colonne INT celle-ci utilisera le type INTEGER.


Les règles pour définir l'affinité sont les suivantes :

1. Si le type déclaré contient "INT" alors il sera considéré comme un INTEGER.

2. Si le type déclaré contient "CHAR", "CLOB", ou "TEXT" alors il sera considéré comme un TEXT.
   Par exemple le type VARCHAR contient la chaîne de caractères "CHAR" il sera donc utilisé avec le type TEXT de SQLite.

3. Si le type déclaré contient la chaîne de caractère "BLOB" ou si aucun type n'est spécifié alors la colonne utilisera le type SQLite BLOB.

4. Si le type déclaré contient l'une des chaînes "REAL", "FLOA" ou "DOUB", alors la colonne a une affinité REAL.

5. Sinon, si le type ne respecte aucune des règles précédentes, il sera considéré comme un NUMERIC.
   

Il faut donc choisir son type SQL en connaissance de cause.
L'utilisation d'un VARCHAR(50) ne sera pas plus optimisé que l'utilisation du type TEXT car celui-ci utilisera le type TEXT SQLite.

Exemples d'utilisation des règles d'affinités :

 Le mode de journal WAL

Le mode de journal WAL est significativement plus rapide dans la plupart des scénarios.
Le WAL autorise plus de concurrence en évitant les blocages liés aux opérations de lecture sur l’écriture et réciproquement. Ainsi la lecture et l'écriture peuvent se dérouler simultanément.

Les opérations d’écriture sur le disque sont plus séquentielle en utilisant WAL.

Activer le mode de journal WAL :

WAL PRAGMA journal_mode=WAL;

A noter que l'activation de ce mode crée deux fichiers supplémentaires (un fichier -shm et un fichier -wal).

Gérer la version de la base de données

Ce pragma permet de récupérer la version de base données.
Ce numéro de version est géré uniquement par le développeur.

Récupérer la version de la base de données :

 PRAGMA user_version;

Ce même pragma permet aussi de définir la version de la base de données.

 PRAGMA user_version = integer;

Grâce à ce PRAGMA vous avez tout pour détecter quand votre base de données doit monter de version.

La commande VACUUM

La commande VACUUM re construit la base de données.

Cela permet :

• Supprimer la fragmentation de celle-ci
• Réorganiser les données

Après la suppression d'un grand nombre de données il est conseillé de l'utiliser pour éviter la fragmentation et donc de garder de bonnes performances.

Définir le délai d'attente de SQLite

Une connexion SQLite attend un certain nombre de temps quand la table est verrouillée (à cause d’une opération d’écriture par exemple).

Lorsque vous faîtes des opérations de synchronisations qui sont longues :  vous pouvez décider de laisser l'utilisateur afficher d'autres données et donc de fait effectuer des opérations de lectures en concurences de vos opérations d'écritures.
Or si le premier traitement est trop long (la valeur par défaut est de 100 millisecondes) SQLite lance une exception appelé "SQLite Busy".

On peut récupérer la valeur associée à la connexion en utilisant la commande suivante :

PRAGMA busy_timeout;

On peut modifier le temps en utilisant la commande ci-dessous :

 PRAGMA busy_timeout = milliseconds;

Les différents connecteurs SQLite

Il existe de nombreux connecteurs SQLite, mais les 3 principaux sont les suivants :

• Microsoft.Data.Sqlite
• SQLite.NET
• EFCore

Microsoft.Data.Sqlite

Microsoft.Data.Sqlite est l'implémentation .Net Standard 2.0 de l'accès donnée SQLite.
Il implémente ADO.Net, si vous avez déjà utilisé ADO.Net avec d'autres base de données vous n'avez rien à de plus apprendre.

Lien vers le nuget

Les avantages :

• Implémentation ADO.net classique, aucun besoin de monter en compétence sur les API
• Aucune limite
• Support de la lib par Microsoft

Les inconvenients :

•  Un peu trop basique ?
• Pas de mapping d'objet automatique

A noter : si vous désirez supporter les versions d'UWP qui ne supporte pas encore .NetStandard 2.0 vous pouvez utiliser une autre dll appelé Mono.Data.Sqlite.

SQLite.NET

SQLiteNet est un ORM permettant d'accéder simplement à une base de données SQLite.
Pendant très longtemps il était le principal ORM utilisé lors de développements Xamarin.

Lien vers le nuget

Les avantages :

• Simple à utiliser
• Gère la création des tables
• Map automatiquement vos objets
• Support de .Net Standard 1.4

Les inconvénients :

• La lib a quelques bugs
• L’API publique est quelque fois déconcertante
• Maintenu par un développeur et non par une organisation

Entity Framework Core

Entity Framework Core alias EFCore est un ORM développé par Microsoft.
Celui ci a une implémentation .Net Standard 2.0 donc les compétences que vous avez acquis coté serveur en l'utilisant par exemple avec SQLServer sont intégralement reutilisable.

Les avantages :

• Simple
• Support du mapping automatique des objets
• Réutilisation des compétences Entity Framework.
• Support de la lib par Microsoft

 Les inconvénients :

• Le tacking d'objet est peut être un peu trop lourd pour une app mobile

Happy coding

Pour aller plus loin :

• Livre dédié à Xamarin
• SQLite et performances : ne récupérez de la base que ce dont vous avez besoin #UWP
• Documentation officielle SQLite