Il est courant, voire universel, de stocker les données hors-ligne de nos applications dans une base de données SQLite. Une fois la première version de notre application sur le Store (ou déployée en production dans l’entreprise), il devient nécessaire de gérer correctement les montées de versions de la base de données. Cet article présentera quelques bonnes pratiques mises en place chez Infinite Square.

Le contexte

Pour résumer, SQlite permet de mettre en place une base de données relationnelle dans un fichier. Elle est donc facilement embarquable dans une application Windows et très pratique car celles-ci ne peuvent pas communiquer (facilement) avec une base de données sur la même machine du fait du sandboxing des applications Windows Store.

SQlite est fourni sur leur site sous la forme d’une DLL qu’il faut embarquer avec l’application. Pour vous faciliter le travail, ils proposent une extension Visual Studio qui permet d’automatiser cela pour vous. Elle est installée au niveau machine mais vous pouvez le faire manuellement au niveau “solution” en lisant cet article.

Pour utiliser les méthodes de cette DLL, il faut utiliser le mécanisme de DLLImport. SQLite étant une DLL native,  votre application ne pourra plus cibler n’importe quel CPU (Any CPU) mais devra être compilée pour chaque architecture spécifiquement (x86, x64, ARM, …). Pas de panique, cela est supporté de base par SQlite mais il faut bien penser à mettre à jour l’architecture ciblée par la solution après l’ajout de la référence si l’on veut que cela compile. Pensez aussi à référencer l’extension Visual C++ 2015 runtime dans votre projet principal.

Définir les différentes méthodes DLLImport étant assez fastidieux, nous avons l’habitude de passer par le très bon package Nuget SQLite-Net. Celui-ci ajoute 2 classes principales dans votre projet : SQLite.cs et SQLiteAsync.cs. Le premier définit toutes les méthodes de bases et le second utilise les classes du premier fichier pour permettre une utilisation asynchrone (attention, chacune des actions de cette classe va vous créer un Thread – mais gérer correctement la concurrence).

Avec Visual Studio 2015, Nuget se met à jour … mais enlève sa fonctionnalité d’ajout de fichiers à l’installation. Cela est justement ce que faisait le package Nuget SQLite-net qui ne marche donc plus tel quel. Pas de panique, il y a deux solutions :

1. Prendre les fichiers soi même sur GitHub : https://github.com/praeclarum/sqlite-net
2. Utiliser le package SQLite-net PCL (en majuscule) qui est un fork “compilé”.

Création de la base de données et d’une table

Le fonctionnement d’SQLite-Net est à base de classe .Net simple (POCO) : il est capable d’utiliser les APIs de réflexions pour générer les requêtes SQL nécessaires.

Pour créer une table dans une base de données, il suffit d’appeler la méthode CreateTable sur une connexion que l’on vient d’ouvrir. Elle prend un paramètre permettant de configurer la création. J’aime bien avoir la création automatique d’index et de clef primaire basée sur des conventions de nommage ( = la propriété se termine par Id), c’est ce qu’il y a dans l’exemple ci-dessous.

using (var con = new SQLiteConnection("LocalCache.db"))
{
    con.CreateTable(typeof(MaClasse), CreateFlags.AllImplicit);
}

Stocker et détecter le numéro de version

Il devient alors intéressant de stocker le numéro de version de notre base de données qui est différent de la version de l’application.

On peut le faire en utilisant une API spécifique à la plateforme mais SQLite propose ce qu’il faut nativement en utilisant la pragma “user_version”. Cela est plus performant et votre base de données se suffit à elle-même avec cette solution.

Voici un exemple de code de lecture et d’écriture :

// Lecture dans la db
var version = sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version");

// Ecriture dans la db
sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version=1;");

La valeur retournée par défaut est 0.

Effectuer concrètement la montée de version d’une table

La mise à jour du schéma d’une table peut être fait à la main mais SQLite-Net permet de faire cela automatiquement pour vous en utilisant la méthode CreateTable à nouveau.

Le fonctionnement interne est le suivant :

• Création d’un tableau de mapping entre le type .Net, ses propriétés et les correspondances SQLite
• Création de la table via une requête SQL (avec la condition SQL de ne le faire que si elle n’existe pas déjà).
• Si la table existait, alors comparaison avec les informations en base et ajout des colonnes manquantes via exécution de code SQL

Les scénarios non pris en charge sont donc les suivants :

• Suppression de colonne :  la colonne et ses données restent – inutilisées - en base. Ce n’est pas forcément critique et il reste possible de faire le nettoyage à la main.
• Changement du type de colonne : cas potentiellement rare à prendre en charge manuellement.

La migration d’une table vers un nouveau schéma (en utilisant une connexion SQLAsync) est donc très simple :

 await sqLiteAsyncConnection.CreateTablesAsync(
                            CreateFlags.AllImplicit,
                            typeof(MaClasse));

Mettre cela en œuvre

On va alors pouvoir orchestrer ces différentes possibilités pour avoir un mécanisme fiable :

1. Ouverture de la connexion,
2. Lecture du numéro de version en DB
3. Si version = 0 alors création de toutes les tables et stockage de la version la plus haute en base,
4. Si version = x, alors mise à jour vers la version x+1 via CreateTable et éventuellement du code custom, stockage en db de la version “x+1,
5. Ainsi de suite jusqu’à la version finale

Voici le code montant jusqu’à une version 3 ma base de données :

var sqLiteAsyncConnection = new SQLiteAsyncConnection("database.db");

//quele version actuellement ?
var version = sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version");


// la base est toute fraîche
if (version == "0")
{
    await sqLiteAsyncConnection.CreateTablesAsync(
        CreateFlags.AllImplicit,
        typeof(MaClass),
        typeof(UneAutreTable),
        typeof(EncoreUne)
        );

    // La table est à jour complétement
    sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version=3;");

    version = "3";
}

// version 1 --> migration vers la v2
if (version == "1")
{
    // si l'on avait changé la définition de UneAutreTable
    await sqLiteAsyncConnection.CreateTablesAsync(CreateFlags.AllImplicit, typeof(UneAutreTable));
    
    // éventuellemnt migration manuelle ici d'une donnée / colonne

    // passage en v2
    sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version=2;");
    version = "2";
}

// version 2 --> migration vers la v3
if (version == "2")
{
    // si l'on avait changé la définition de EncoreUne
    await sqLiteAsyncConnection.CreateTablesAsync(CreateFlags.AllImplicit, typeof(EncoreUne));
    sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version=3;");
    version = "3";
}

Automatiser via des tests unitaires (ou manuels).

Pour automatiser vos tests de montées de version, il est impératif de garder une version de la base de donnée en version 1.

Etant donné que cela est un fichier, vous pouvez tout simplement en garder une copie lors de la mise en production de la première version de l’application.

Le mécanisme de montée de version mis en place fera passer la base de données par toutes les version intermédiaires jusqu'à la finale vous assurant que cela fonctionne bien.

Bon code !