Accès et gestion des bases de données locales

Présentation rapide de SQLite

SQLite, comme son nom l’indique, utilise un dialecte de SQL pour effectuer des requêtes (SELECT), des manipulations de données (INSERT, etc.) et des définitions de données (CREATE TABLE, etc.). À certains moments, il s’écarte du standard SQL-92, comme la plupart des autres SGBDR, d’ailleurs. La bonne nouvelle est que SQLite est si efficace en terme de mémoire que le moteur d’exécution d’Android peut l’inclure dans son intégralité : vous n’êtes donc pas obligé de vous contenter d’un sous-ensemble de ses fonctionnalités pour gagner de la place. La plus grosse différence avec les autres SGBDR concerne principalement le typage des données. Tant que vous pouvez préciser les types des colonnes dans une instruction CREATE TABLE et tant que SQLite les utilise comme indication, tout va pour le mieux. Vous pouvez mettre les données que vous voulez dans les colonnes que vous souhaitez. Vous voulez placer une chaîne dans une colonne INTEGER ? Pas de problème ! Et vice versa ? Cela marche aussi ! C’est ce que SQLite appelle « typage manifeste » ; il est décrit de la façon suivante dans sa documentation2 : Avec le typage manifeste, le type d’une donnée est une propriété de la valeur elle-même, pas de la colonne dans laquelle la valeur est stockée. SQLite permet donc de stocker une valeur de n’importe quel type dans n’importe quelle colonne, quel que soit le type déclaré de cette colonne. Certaines fonctionnalités standard de SQL ne sont pas reconnues par SQLite, notamment les contraintes FOREIGN KEY, les transactions imbriquées, RIGHT OUTER JOIN, FULL OUTER JOIN et certaines variantes de ALTER TABLE. Ces remarques mises à part, vous disposez d’un SGBDR complet, avec des triggers, des transactions, etc. Les instructions SQL de base, comme SELECT, fonctionnent exactement comme vous êtes en droit de l’attendre. Si vous êtes habitué à travailler avec un gros SGBDR comme Oracle, vous pourriez considérer que SQLite est un « jouet », mais n’oubliez pas que ces deux systèmes ont été conçus pour résoudre des problèmes différents et que vous n’êtes pas près de voir une installation complète d’Oracle sur un téléphone. 

Commencer par le début

Android ne fournit aucune base de données de son propre chef. Si vous voulez utiliser SQLite, vous devez créer votre propre base, puis la remplir avec vos tables, vos index et vos données. Pour créer et ouvrir une base de données, la meilleure solution consiste à créer une sousclasse de SQLiteOpenHelper. Cette classe enveloppe tout ce qui est nécessaire à la création et à la mise à jour d’une base, selon vos spécifications et les besoins de votre application. Cette sous-classe aura besoin de trois méthodes : ● Un constructeur qui appelle celui de sa classe parente et qui prend en paramètre le Context (une Activity), le nom de la base de données, une éventuelle fabrique de curseur (le plus souvent, ce paramètre vaudra null) et un entier représentant la version du schéma de la base. ● onCreate(), à laquelle vous passerez l’objet SQLiteDatabase que vous devrez remplir avec les tables et les données initiales que vous souhaitez. ● onUpgrade(), à laquelle vous passerez un objet SQLiteDatabase ainsi que l’ancien et le nouveau numéro de version. Pour convertir une base d’un ancien schéma à un nouveau, l’approche la plus simple consiste à supprimer les anciennes tables et à en créer de nouvelles. Le Chapitre 28 donnera tous les détails nécessaires. Le reste de ce chapitre est consacré à la création et à la suppression des tables, à l’insertion des données, etc. Il présentera également un exemple de sous-classe de SQLiteOpenHelper. Pour utiliser votre sous-classe, créez une instance et demandez-lui d’appeler getReadableDatabase() ou getWriteableDatabase() selon que vous vouliez ou non modifier son contenu : db=(new DatabaseHelper(getContext())).getWritableDatabase(); return (db == null) ? false : true; Cet appel renverra une instance de SQLiteDatabase qui vous servira ensuite à interroger ou à modifier la base de données. Lorsque vous avez fini de travailler sur cette base (lorsque l’activité est fermée, par exemple), il suffit d’appeler la méthode close() de cette instance pour libérer votre connexion.

Mettre la table

Pour créer des tables et des index, vous devez appeler la méthode execSQL() de l’objet SQLiteDatabase en lui passant l’instruction du LDD (langage de définition des données) que vous voulez exécuter. En cas d’erreur, cette méthode renvoie null.Vous pouvez, par exemple, utiliser le code suivant : db.execSQL(« CREATE TABLE constantes (_id INTEGER PRIMARY KEY AUTOINCREMENT, titre TEXT, valeur REAL); »); Cet appel crée une table constantes avec une colonne de clé primaire _id qui est un entier incrémenté automatiquement (SQLite lui affectera une valeur pour vous lorsque vous insérerez les lignes). Cette table contient également deux colonnes de données : titre (un texte) et valeur (un nombre réel). SQLite créera automatiquement un index sur la colonne de clé primaire – si vous le souhaitez, vous pouvez en ajouter d’autres à l’aide d’instructions CREATE INDEX. Le plus souvent, vous créerez les tables et les index dès la création de la base de données ou, éventuellement, lorsqu’elle devra être mise à jour suite à une nouvelle version de votre application. Si les schémas des tables ne changent pas, les tables et les index n’ont pas besoin d’être supprimés mais, si vous devez le faire, il suffit d’utiliser execSQL() afin d’exécuter les instructions DROP INDEX et DROP TABLE.

Ajouter des données

Lorsque l’on crée une base de données et une ou plusieurs tables, c’est généralement pour y placer des données. Pour ce faire, il existe principalement deux approches. Vous pouvez encore utiliser execSQL(), comme vous l’avez fait pour créer les tables. Cette méthode permet en effet d’exécuter n’importe quelle instruction SQL qui ne renvoie pas de résultat, ce qui est le cas d’INSERT, UPDATE, DELETE, etc. Vous pourriez donc utiliser ce code : db.execSQL(« INSERT INTO widgets (name, inventory) »+ « VALUES (’Sprocket’, 5) »); Une autre solution consiste à utiliser insert(), update() et delete() sur l’objet SQLiteDatabase. Ces méthodes utilisent des objets ContentValues qui implémentent une interface ressemblant à Map mais avec des méthodes supplémentaires pour prendre en compte les types de SQLite : outre get(), qui permet de récupérer une valeur par sa clé, vous disposez également de getAsInteger(), getAsString(), etc. La méthode insert() prend en paramètre le nom de la table, celui d’une colonne pour l’astuce de la colonne nulle et un objet ContentValues contenant les valeurs que vous voulez placer dans cette ligne. L’astuce de la colonne nulle est utilisée dans le cas où l’instance de ContentValues est vide – la colonne indiquée pour cette astuce recevra alors explicitement la valeur NULL dans l’instruction INSERT produite par insert(). ContentValues cv=new ContentValues(); cv.put(Constantes.TITRE, « Gravity, Death Star I »); cv.put(Constantes.VALEUR, SensorManager.GRAVITY_DEATH_STAR_I); db.insert(« constantes », getNullColumnHack(), cv); Livre Android.book Page 220 Dimanche, 8. novembre 2009 12:23 12 customer 27921 at Fri Mar 11 19:19:45 +0100 2011 Propriété de Albiri Sigue Chapitre 20 Accès et gestion des bases de données locales 221 La méthode update() prend en paramètre le nom de la table, un objet ContentValues contenant les colonnes et leurs nouvelles valeurs et, éventuellement, une clause WHERE et une liste de paramètres qui remplaceront les marqueurs présents dans celle-ci. update() n’autorisant que des valeurs fixes pour mettre à jour les colonnes, vous devrez utiliser execSQL() si vous souhaitez affecter des résultats calculés. La clause WHERE et la liste de paramètres fonctionnent comme les paramètres positionnels qui existent également dans d’autres API de SQL : // remplacements est une instance de ContentValues String[] params=new String[] {« snicklefritz »}; db.update(« widgets », remplacements, « name=? », params); La méthode delete() fonctionne comme update() et prend en paramètre le nom de la table et, éventuellement, une clause WHERE et une liste des paramètres positionnels pour cette clause.

Le retour de vos requêtes

Comme pour INSERT, UPDATE et DELETE, vous pouvez utiliser plusieurs approches pour récupérer les données d’une base SQLite avec SELECT : ● rawQuery() permet d’exécuter directement une instruction SELECT. ● query() permet de construire une requête à partir de ses différentes composantes. Un sujet de confusion classique est la classe SQLiteQueryBuilder et le problème des curseurs et de leurs fabriques.

Requêtes brutes

La solution la plus simple, au moins du point de vue de l’API, consiste à utiliser rawQuery() en lui passant simplement la requête SELECT. Cette dernière peut contenir des paramètres positionnels qui seront remplacés par les éléments du tableau passé en second paramètre. Voici un exemple : Cursor c=db.rawQuery(« SELECT name FROM sqlite_master WHERE type=’table’ AND name=’constantes’ », null); Ici, nous interrogeons une table système de SQLite (sqlite_master) pour savoir si la table constantes existe déjà. La valeur renvoyée est un Cursor qui dispose de méthodes permettant de parcourir le résultat (voir la section « Utilisation des curseurs »). Si vos requêtes sont bien intégrées à votre application, c’est une approche très simple. En revanche, elle se complique lorsqu’une requête comprend des parties dynamiques que les paramètres positionnels ne peuvent plus gérer. Si l’ensemble de colonnes que vous voulez Livre Android.book Page 221 Dimanche, 8. novembre 2009 12:23 12 customer 27921 at Fri Mar 11 19:19:45 +0100 2011 Propriété de Albiri Sigue 222 L’art du développement Android récupérer n’est pas connu au moment de la compilation, par exemple, concaténer les noms des colonnes pour former une liste délimitée par des virgules peut être ennuyeux – c’est là que query() entre en jeu.

Requêtes normales

La méthode query() prend en paramètre les parties d’une instruction SELECT afin de construire la requête. Ces différentes composantes apparaissent dans l’ordre suivant dans la liste des paramètres : 1. Le nom de la table interrogée. 2. La liste des colonnes à récupérer. 3. La clause WHERE, qui peut contenir des paramètres positionnels. 4. La liste des valeurs à substituer à ces paramètres positionnels. 5. Une éventuelle clause GROUP BY. 6. Une éventuelle clause ORDER BY. 7. Une éventuelle clause HAVING. À part le nom de la table, ces paramètres peuvent valoir null lorsqu’ils ne sont pas nécessaires : String[] colonnes={« ID », « inventory »}; String[] params={« snicklefritz »}; Cursor result=db.query(« widgets », colonnes, « name=? », params, null, null, null); Utilisation des « builders » Une autre possibilité consiste à utiliser SQLiteQueryBuilder, qui offre bien plus de possibilités pour construire les requêtes complexes, notamment celles qui impliquent d’unir les résultats de plusieurs sous-requêtes, par exemple. En outre, l’interface SQLiteQueryBuilder s’accorde parfaitement avec l’interface ContentProvider pour exécuter les requêtes. Un patron de conception classique pour l’implémentation de la méthode query() de votre fournisseur de contenu consiste donc à créer un objet SQLiteQueryBuilder, à lui fournir certaines valeurs par défaut, puis à lui faire construire (et, éventuellement, exécuter) la requête complète en combinant ces valeurs par défaut avec celles qui ont été passées au fournisseur de contenu lors de la demande de requête. Voici, par exemple, un extrait de code d’un fournisseur de contenu utilisant SQLiteQueryBuilder : @Override public Cursor query(Uri url, String[] projection, String selection, String[] selectionArgs, String sort) { Livre Android.book Page 222 Dimanche, 8. novembre 2009 12:23 12 customer 27921 at Fri Mar 11 19:19:45 +0100 2011 Propriété de Albiri Sigue Chapitre 20 Accès et gestion des bases de données locales 223 SQLiteQueryBuilder qb=new SQLiteQueryBuilder(); qb.setTables(getTableName()); if (isCollectionUri(url)) { qb.setProjectionMap(getDefaultProjection()); } else { qb.appendWhere(getIdColumnName()+ »= »+url.getPathSegments().get(1)); } String orderBy; if (TextUtils.isEmpty(sort)) { orderBy=getDefaultSortOrder(); } else { orderBy=sort; } Cursor c=qb.query(db, projection, selection, selectionArgs, null, null, orderBy); c.setNotificationUri(getContext().getContentResolver(), url); return c; } Les fournisseurs de contenu (content provider) seront expliqués en détail dans la cinquième partie de ce livre. Ici, nous pouvons nous contenter de remarquer que : 1. Nous construisons un objet SQLiteQueryBuilder. 2. Nous lui indiquons la table concernée par la requête avec setTables(getTableName()). 3. Soit nous lui indiquons l’ensemble de colonnes à renvoyer par défaut (avec setProjectionMap()), soit nous lui donnons une partie de clause WHERE afin d’identifier une ligne précise de la table à partir d’un identifiant extrait de l’URI fournie à l’appel de query() (avec appendWhere()). 4. Enfin, nous lui demandons d’exécuter la requête en mélangeant les valeurs de départ avec celles fournies à query() (qb.query(db, projection, selection, selectionArgs, null, null, orderBy)). Au lieu de faire exécuter directement la requête par l’objet SQLiteQueryBuilder, nous aurions pu appeler buildQuery() pour la produire et renvoyer l’instruction SELECT dont nous avions besoin ; nous aurions alors pu l’exécuter nous-mêmes.