Dans l’article précédent je vous expliquais comment utiliser SQLite avec UWP, il est temps d’aller un peu plus loin dans la connaissance de cette base de données et de ses possibilités…
SQLite.NET
Gérer des données c’est en fait ce à quoi sert l’informatique, science du traitement de l’information. Savoir comment on peut prendre en charge ces dernières sur une plateforme est donc essentiel. SQLite.NET est une couche qui autorise l’exploitation de bases de données portables utilisant le moteur SQLite. Légèreté, simplicité d’utilisation, fiabilité ont fait le succès de ce moteur. L’utiliser sous UWP permet d’ors et déjà de concevoir des applications portables ambitieuses. Avec un seul code, un seul exécutable, un seul Store. Regardons de plus près ce qu’on peut en attendre…
Obtenir une connexion
Comme n’importe quelle base de données SQLite ne peut s’utiliser qu’une fois qu’on a obtenu une connexion active. Et pour l’obtenir il faut définir le chemin d’accès à la base ainsi que le nom de cette dernière.
Dans l’article précédent le chemin était construit de la sorte :
var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
Le stockage se fait dans LocalFolder de ApplicationData. Sur ma machine pour l’application “App3” ce chemin est le suivant :
C:\Users\Olivier\AppData\Local\Packages\8a6dbf70-b051-4496-9ab3-e98ec06c712d_4a5vmqx2t774c\LocalState\db.sqlite
On voit que ce chemin se situe dans les fichiers privés de l’utilisateur. Il n’y a donc pas de partage de données entre différents utilisateurs de la même machine. Les bases de données légères et embarquées de type SQLite ne fonctionnent d’ailleurs presque jamais en mode multi-user, que cela soit simultanément ou non. Si on désire partager des données entre utilisateurs il faut opter pour un stockage dans le cloud par exemple.
Avec UWP il est possible d’utiliser des API du type
public static IAsyncOperation<StorageFile> GetFileFromPathAsync(string path)
Ce qui permet d’atteindre d’autres zones de stockage mais là encore il faut être très attentif à l’exposition des données privées et au partage des fichiers qui généralement ne peuvent être ouverts que par une application à la fois.
Une fois le path créé et le nom de la base défini il est possible d’ouvrir la connexion. Dans l’article précédent cela était fait dans une section “using” pour des raisons évidentes s’agissant de ressources non gérées (au sens not managed de .NET) qu’il faut veiller à libérer systématiquement. Le code était pour rappel :
using (SQLite.Net.SQLiteConnection conn =
new SQLite.Net.SQLiteConnection(
new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))
{ ... }
La plateforme précisée est WinRT, il n’y a pas de choix UWP (au moins pour l’instant) car techniquement les deux mondes restent très proches et que les exigences notamment de sécurité sont fondamentalement les mêmes.
Créer des tables
La création de table est un moment essentiel qui suit généralement l’obtention de la connexion. Cette création est non destructive et c’est pour cela qu’on la rencontre systématiquement dans la plupart des applications : on s’assure de cette façon que la table existe et si elle n’existe pas elle est créée avant toute tentative d’accès.
Dans le code exemple la table était créée de la façon suivante :
conn.CreateTable<Message>();
“conn” étant la connexion. La méthode CreateTable prend le type de la classe qui sera persistée.
La classe persistée est un POCO tout ce qu’il y a de plus classique. On peut parfaitement travailler avec ce “minimum”. Toutefois nous le verrons plus loin des attributs spécifiques permettent de préciser de nombreuses options essentielles comme les clés primaires, les index, etc.
Créer et lire des données
Ce qu’on attend d’une base de données c’est de permettre les opérations CRUD. Heureusement SQLite fourni le nécessaire et de façon simple. Ainsi créer un nouvel enregistrement se fera comme suit :
var m = new Message {Title = "Titre " , Stamp = DateTime.Now};
conn.Insert(m);
En reprenant la classe Message de l’article précédent qui contient un titre et un date on voit comment une instance est créée de façon tout à fait classique puis comment elle est persistée par la méthode Insert de la connexion.
On peut ensuite accéder à toutes les données de la table :
conn.Table<Message>()
Tous les enregistrements sont retournés, ce qui peut être dangereux pour la mémoire ! En général une telle source de données est exploitée au sein d’une requête LINQ quii filtrent ce qui est remonté :
var query = from m in conn.Table<Message>() where m.Title="Le Titre" orderby m.Stamp descending select m;
On peut bien entendu obtenir directement un enregistrement par son ID si on a eu la bonne idée d’en ajouter un dans la classe persistée…
On peut ainsi faire évoluer la classe de test Message en lui ajoutant une clé primaire :
public class Message
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string Title { get; set; }
public DateTime Stamp { get; set; }
}
On commence à voir apparaitre quelques attributs spécifiques de SQLite. Il s’obtiennent en ajoutant le using suivant :
using SQLite.Net.Attributes;
Dans la classe Message il existe désormais un champ “Id” de type integer qui est marqué comme étant la clé primaire. De plus nous choisissons de rendre cet entier autoincrémenté, c’est SQLite qui se chargera donc de tout. Notamment à la sortie de l’appel à la méthode Insert (voir plus haut) le champ “Id” contiendra automatiquement une valeur valide.
Grâce à cet Id il est possible d’obtenir maintenant un enregistrement précis :
var m1 = conn.Get<Message>(3);
“m1” contiendra en sortie une instance du Message ayant un Id = 3.
Les Attributs SQLite.NET
Comme nous l’avons entrevu plus haut il existe des attributs permettant d’agir plus finement sur le comportement de SQLite qu’avec des POCO de base.
Fixer la clé primaire est en général un préliminaire incontournable pour gérer des données efficacement, c’est le rôle l’attribut PrimaryKey. Mais attention, c’est SQLite, lire “light” à la fin, pas SQL Server ! Donc la clé primaire ne peut être que simple (pas de clé composée) et de type integer.
On peut décider de fixer soi-même la valeur (dans certains cas c’est plus pratique) ou bien de laisser le moteur attribuer une valeur autoincrémentée c’est le rôle de l’attribut AutoIncrement.
On peut ensuite agir sur les noms des colonnes qui, par défaut, sont ceux des propriétés. Avec l’attribut Colum(name) on peut changer le nom d’un champ et avec Table(name) celui de la table (qui prend le nom de la classe par défaut).
A quoi cela sert-il ? Dans le mode de fonctionnement montré ici avec SQLite.Net pas à grand chose. Mais SQLIte est une base de données qui se pilote en SQL et selon la circonstance on peut être amené à travailler directement dans ce langage. Et il peut alors être plus pratique d’avoir des noms de tables et de colonnes qu’on fixe soi-même. Sinon il n’y a pas d’intérêt.
Il est aussi possible pour les champs string de fixer une longueur maximale par l’attribut MaxLength(int). La troncature n’a lieu qu’au moment d’un Insert ou d’un Update, c’est au code de l’application de vérifier si l’utilisateur est bien limité dans ses saisies par exemple.
L’attribut Ignore est intéressant car il permet d’éviter le stockage d’un champ. Et pourquoi en voudrions-nous pas stocker un champ en particulier ? D’abord s’il est d’un type que SQLite ne peut pas stocker (pas de sérialisation disponible), ou bien s’il s’agit d’une collection que SQLite ne peut pas mapper automatiquement ou encore si on a ajouter des champs calculés qui n’ont pas de raison d’être stockés.
Enfin l’attribut Unique permet de forcer l’unicité des valeurs d’une colonne. La clé primaire pourrait jouer ce rôle mais comme elle ne peut être qu’entière il s’avère souvent indispensable de fixer l’unicité par exemple d’un titre, d’un nom de société, d’une référence article etc.
Mais il existe d’autres attributs dont le rôle peut s’avérer important, par exemple Indexed qui ajoute un index sur la colonne et qui accélère les recherche et les tris sur cette dernière. Autre attribut plus “sournois” mais qui peut sauver la vie dans le cas d’applications utilisant des données de plusieurs langues ou des champs avec une casse variable, “Collation()”. Cet attribut accepte les valeurs BINARY, RTRIM et NOCASE. Le mode binaire est celui par défaut et toute recherche d’une chaine (par simple égalité) ne fonctionne que si les deux chaines sont rigoureusement identiques. Avec RTRIM les espaces de fin sont supprimés dans les comparaisons ce qui élargit le champ de l’égalité. Et avec NOCASE les différences de casse sont ignorées ce qui est primordial dans une application professionnelle mais qui peut se régler en amont ou bien dans les requêtes en utilisant des comparaisons ignorant la casse plutôt qu’un simple “égal”. Toutefois cette dernière possibilité, simple et efficace, est à préférer si les attributs sont bien positionnés.
L’attribut Default permet de fixer une valeur par défaut mais avec certaines restrictions et un fonctionnement qui me semble étrange, donc jusqu’à plus ample informé je n’en conseille pas l’utilisation. Si des valeurs par défaut doivent être fixées utilisez des champs initialisés ou le constructeur de la classe.
NotNull est un attribut commun pour les SGBD et il permet bien entendu d’indiquer qu’un champ ne peut pas avoir de valeur nulle. Il faut alors gérer le rejet lors d’un Insert ou d’un Update. Personnellement avec ce genre de petite base de données je préfère lui en demander le minimum… Il doit y avoir des classes métiers qui gèrent les valeurs par défaut, l’interdiction des null, les longueurs maximales etc. Créer une exception ou un rejet au niveau de la base de données est trop “bas niveau” pour l’utilisateur. Mieux vaut l’empêcher de saisir des bêtises plutôt que de le laisser faire et déléguer la remontée des erreurs à la couche d’accès aux données… D’ailleurs ce principe est vrai même si on utilise SQL Server avec 1000 utilisateurs utilisant de gros PC. Sauf que dans ce dernier cas la base pouvant être utilisée par plusieurs application il convient de la protéger par un schéma rigoureux, c’est donc d’autres raisons qui poussent alors à fixer ces paramètres au niveau SGBD. Avec SQLite je vous l’ai dit, point de partage entre applications ni d’accès multi-user, donc cet impératif n’existe pas.
CRUD
Je vous ai déjà présenté plusieurs opérations de base comme le Get, l’Insert ou Table qui permet de remonter tous les enregistrements ou de participer à une requête LINQ comme source de données. Il manque bien entendu la mise à jour qui se fait ainsi en utilisant la méthode Update de la connexion :
m1.Title = "MODIFIE";
conn.Update(m1);
Et bien entendu la suppression qui s’effectue de la même façon en utilisant Delete. DeleteAll permet de vider une table d’un seul coup tout comme DropTable permet de la supprimer définitivement de la base. Cela est très pratique pour les applications qui utilisent des tables temporaires pour le besoin d’une session de travail. On ouvre la connexion, on détruit la table et on la créée juste derrière. On est certain de disposer d’un espace de travail “propre”. Attention à la gestion du tombstoning et des suspensions…
Les requêtes
On a vu que les requêtes pouvait s’écrire en utilisant LINQ ce qui permet de rester en C# de bout en bout et de bénéficier d’un contrôle syntaxique à la compilation et même lors de l’écriture du code grâce à IntelliSense et à des outils comme Resharper. Mais il est possible d’écrire aussi des requêtes SQL
C’est à cela que servent Query<T> et Execute, le premier retournant des résultats l’autre non.
var r = conn.Query<Message>("SELECT TITLE FROM Message WHERE Field1>300");
L’exemple ci-dessus retournera une liste d’objets Message dont le champ Field1 est supérieur à 300. Mais attention… Comme nous avons un SELECT TITLE seule cette propriété sera remontée dans les instances de Message, même l’ID restera à zéro… C’est en revanche plus efficace et lorsqu’on manipule des objets pouvant être lourds et / ou nombreux ce type de requête est bien plus rapide et économe en mémoire que son équivalent LINQ.
Execute s’utilise de la même façon mais sans retour de données, ce qui est donc à préférer pour des insertions ou des updates par exemple. Mais dans ce cas l’utilisation des méthodes montrées plus haut sont parfaitement adaptées et moins risquées que du SQL non contrôlé à la compilation. Néanmoins on peut avoir à mettre à jour de très nombreux enregistrements sur un champ (changer une quantité, une date…) et l’utilisation de Execute sera bien plus rapide et économe que la manipulation un par un d’objet via la connexion.
Requêter directement en SQL peut ainsi s’avérer crucial pour garantir de bonnes performances et une utilisation mémoire moindre.
Les Transactions
Malgré sa simplicité SQLite “a tout d’une grande” et l’un des points emblématiques qui différencient un SGBD d’une simple gestion de fichiers est certainement le support des transactions.
On retrouve donc naturellement un BeginTransaction, un RollBack et un Commit qui opèrent tel qu’on s’y attend.
SQLite va même plus loin puisqu’il est possible de poser des point de sauvegarde dans une transaction et d’effectuer un Rollback jusqu’à l’un de ces points. Il s’agit là de subtilités peu utilisées car les cas d’utilisation où cela serait justifié manque un peu… Mais allez savoir, un jour cela pourra vous être utile !
Les méthodes moins connues
SQLite s’avère très complet et offre de nombreuses autres possibilités au développeur. Par exemple InsertAll(IEnumerable, bool runInTransaction) peut s’avérer particulièrement efficace pour faire des “bulk insert” le tout protégé ou non par une transaction automatique. Le même type de méthode existe pour les updates aussi.
On trouve aussi dans le source de SQLite.Net une méthode CreateDatabaseBackup qui serait fort utile, mais je n’ai pas réussi à obtenir autre chose qu’un blocage infini sans exception… Le fichier est bien créé mais vide et c’est tout… Il faudra certainement attendre des mises à jour pour voir ces fonctions moins utilisées fonctionner correctement. Ce sera un plus car sauvegarder une base est généralement une bonne idée ! On notera que cette méthode bien que publique n’est pas décorée par l’attribut PublicAPI est-ce à dire qu’elle n’est pas encore prête… Il faudra attendre donc !
Plus utile il existe aussi des choses Find<T>(Expression<Func<T, bool>>) qui autorise la recherche d’un enregistrement via une expression lambda.
D’autres méthodes peuvent être découvertes dans le source de SQLite.NET mais ce qu’on attend d’une telle “mini base de données” c’est avant tout de pouvoir facilement insérer et supprimer un nombre restreint d’enregistrements et cela est largement couvert par tout ce que nous avons vu jusqu’ici.
Les SQLite-NET Extensions
Je fais partie de ceux qui pensent que pour une petite application les possibilités de SQLite.NET sont bien suffisantes dans la grande majorité des cas. Etant donnée la portabilité de UWP vers les smartphones notamment il ne me semble guère raisonnable de se lancer dans de la gestion lourde de data. UWP n’est pas encore prêt à remplacer totalement WPF et les vraies applications de bureau classiques sur de nombreux points. Mais autant l’écart était net avec WinRT autant il est déjà plus mince avec UWP. Par exemple Entity Framework 7 qui sera bientôt disponible (et en Open Source) supportera UWP ce qui signifie qu’on pourra bénéficier d’un puissant système de gestion de données capable de dialoguer avec les principales bases de données (à condition que des connecteurs soient disponibles).
En dehors de SQLite il faut malgré tout noter qu’il existe un connecteur pour MySQL qui autorise le dialogue en TCP avec un serveur. C’est là une solution intéressante et unique pour l’instant permettant de connecter une App universelle à un serveur. Mais si cela peut fonctionner sur un PC on se doute que ce n’est pas vraiment utilisable sur smartphone ce qui fait perdre l’intérêt de la portabilité UWP. Du coup autant faire du WPF avec SQL Server ou utiliser Azure, c’est étudié pour …
Bref on l’a compris gérer des données en UWP c’est avoir conscience que cela doit fonctionner sur smartphone donc qu’il faut de la “retenue” dans le brassage des informations. Pas de big data avec UWP ! Mais parfois des applications n’ayant pas à traiter beaucoup de données en volume n’en restent pas moins sophistiquées.
Sophistiquées ? J’entends par là que bien que manipulant peu de données (par rapport à une grosse application d’entreprise) l’application peut avoir des besoins plus subtiles que l’artillerie de base CRUD. Par exemple la gestion des relations entre les tables, les un-vers-un, un-vers-plusieurs etc.
Dans un tel cas tout gérer “à la main” à partir de simple opérations de base peut rendre l’écriture de l’application assez longue et donc onéreuse.
Pour ces cas extrêmes il existe les SQLite-NET Extensions.
Par l’ajout de nouveaux attributs pour décorer les classes cette librairie rend automatique la gestion des relations entre instances. Ono-to-one, one-to-many, many-to-one, many-to-many sont prises en charge automatiquement ce qui forcément rend bien des services même dans des cas pas si compliqués que ça.
Voici un exemple de définition SQLite.NET classique comme nous en avons vus dans cet article :
public class Stock
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[MaxLength(8)]
public string Symbol { get; set; }
}
public class Valuation
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[Indexed]
public int StockId { get; set; }
public DateTime Time { get; set; }
public decimal Price { get; set; }
}
Une gestion de titres boursiers, avec une action et son historique de valeurs. Ici c’est le développeur qui gèrera la relation par des requêtes utilisant les ID.
Et voici sa version décorée par SQLite-NET Extensions :
public class Stock
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[MaxLength(8)]
public string Symbol { get; set; }
[OneToMany(CascadeOperations = CascadeOperation.All)] // One to many relationship with Valuation
public List<Valuation> Valuations { get; set; }
}
public class Valuation
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[ForeignKey(typeof(Stock))] // Specify the foreign key
public int StockId { get; set; }
public DateTime Time { get; set; }
public decimal Price { get; set; }
[ManyToOne] // Many to one relationship with Stock
public Stock Stock { get; set; }
}
On note la présence d’un attribut définissant une relation un-vers-plusieurs sur la propriété Valuations de l’action (Stock). De la même façon on voit un autre attribut dans la classe Valuation décorant la propriété Stock en Plusieurs-vers-un, l’inverse donc.
Grâce à ses définitions supplémentaires l’extension est capable de remonter des grappes de données soulageant le développeur de la gestion relationnelle de ces dernières. On peut interroger les données de façon habituelles pour n’obtenir que les entités de niveau supérieur ou bien utiliser de nouvelles méthodes qui étendent l’objet connexion de SQLite telle que GetWithChildren qui agit comme un Get mais en remplissant automatiquement les champs impliquant une relation. Dans l’exemple de code ci-dessus cela revient à obtenir une action avec tout son historique de valeurs déjà chargé dans la propriété Valuations.
Il existe un paquet Nuget appelé SQLite.Net Extensions-PCL en version 1.3.0 au moment où j’écris ces lignes (https://www.nuget.org/packages/SQLiteNetExtensions/).
Vous trouverez aussi plus de précision sur ce site qui présente les différentes facettes des extensions.
Conclusion
La boite à outils s’étoffe !
Après vous avoir présenté la plateforme UWP, la façon dont on designe les applications et comment on utilise les spécificités de XAML ou les fonctionnalités d’une famille de machines, je viens de vous donner les moyens de gérer des données même relationnelles !
Le tout avec un seul code, un seul exécutable pour toutes les machines utilisant UWP… Une même application pour un PC ou un smartphone, sans aller chercher les Hololens ou la XBox, c’est déjà un avantage énorme !
Le tout en conservant tout ce qu’on sait sur C# et XAML comme au bon vieux temps de Silverlight, n’est-ce pas magnifique ? !
Si, bien sur. Il faut juste maintenant que les DSI comprennent que leur intérêt se trouve dans UWP bien plus que dans du couteux BYOD… On a déjà dépassé les 50 millions de Windows 10 déployés et tous les smartphones Windows 8 feront tourner Windows 10 d’ici peu. La base d’utilisateurs existe déjà. Il est temps de mettre les logiciels en chantier !
Stay Tuned !