Introduction à SQL

Introduction à SQLite

Le cours Développer des applications Android en Kotlin suppose que vous connaissez les sujets suivants :

  • Les bases de données en général
  • Les bases de données SQL en particulier
  • Le langage SQL utilisé pour interagir avec les bases de données

Cette page fait office de rappel et de mémento.

Bases de données SQL

Les bases de données SQL permettent de stocker des données dans des tables constituées de lignes et de colonnes :

  • L'intersection d'une ligne et d'une colonne est appelée champ.
  • Les champs contiennent des données, des références à d'autres champs ou des références à d'autres tables.
  • Chaque ligne contient une entité identifiée par un ID unique, qui est généralement utilisé comme clé primaire.
  • Chaque colonne est identifiée par un nom unique dans chaque table.

SQLite

SQLite implémente un moteur de base de données SQL présentant les caractéristiques suivantes :

  • Autonome (aucun autre composant requis)
  • Sans serveur (aucun backend de serveur requis)
  • Sans configuration (aucune configuration requise pour votre application)
  • Transactionnel (les modifications apportées à une transaction dans SQLite se produisent entièrement ou pas du tout)

SQLite est le moteur de base de données le plus déployé au monde. Le code source de SQLite appartient au domaine public. Pour en savoir plus sur la base de données SQLite, consultez le site Web de SQLite.

Exemple de table

  • Une base de données nommée DATABASE_NAME
  • Une table nommée WORD_LIST_TABLE
  • Des colonnes _id, word et definition

Après avoir inséré les mots alpha et beta, sachant que alpha a deux définitions, la table peut se présenter comme suit :

DATABASE_NAME

WORD_LIST_TABLE

_id

word

definition

1

"alpha"

"first letter"

2

"beta"

"second letter"

3

"alpha"

"particle"

Pour rechercher le contenu d'une ligne spécifique, utilisez _id ou récupérez des lignes à l'aide de requêtes qui sélectionnent des lignes de la table en spécifiant des contraintes.

Transactions

Une transaction est une séquence d'opérations effectuées comme une seule unité logique de travail. Pour être considérée comme une transaction, une unité logique de travail doit présenter quatre propriétés, à savoir atomicité, cohérence, isolation et durabilité (ACID) :

  • Atomicité. Soit toutes les modifications de données d'une transaction sont effectuées, soit aucune n'est effectuée. L'atomicité est vraie même si un plantage du programme, un plantage du système d'exploitation ou une coupure de courant interrompt l'écriture de la modification sur le disque.
  • Cohérence. Lorsqu'une transaction est terminée, toutes les données doivent rester cohérentes.
  • Isolation. Les modifications apportées par des transactions simultanées doivent être isolées les unes des autres. Une transaction reconnaît les données dans l'état dans lequel elles se trouvaient avant qu'une autre transaction simultanée ne les modifie, ou elle reconnaît les données une fois la deuxième transaction effectuée. La transaction ne reconnaît pas d'état intermédiaire.
  • Durabilité. Une fois la transaction terminée, ses effets s'appliquent de manière définitive dans le système. Les modifications sont conservées même en cas de défaillance du système.

Exemples de transactions :

  • Transfert d'argent d'un compte épargne vers un compte courant
  • Saisie d'un terme et d'une définition dans un dictionnaire
  • Validation d'une liste de modifications pour la branche master

Pour en savoir plus sur les transactions, consultez la page Atomic Commit In SQLite (Validation atomique en SQLite).

Langage de requête

Vous utiliserez le langage de requête SQL pour interagir avec la base de données. Les requêtes peuvent être très complexes, mais il existe quatre opérations de base :

  • Insérer des lignes
  • Supprimer des lignes
  • Mettre à jour des valeurs dans des lignes
  • Récupérer des lignes répondant à certains critères

Sur Android, l'objet d'accès aux données (DAO, Data Access Object) propose des méthodes pratiques pour insérer, supprimer et mettre à jour la base de données. Pour obtenir une description complète du langage de requête, consultez la page SQL As Understood By SQLite (Le langage SQL en SQLite).

Structure d'une requête

Une requête SQL est très structurée. Prenons cet exemple de requête :

  • SELECT word, definition FROM WORD_LIST_TABLE WHERE word="alpha"

Sa version générique se présente comme suit :

  • SELECT colonnes FROM table WHERE colonne="valeur"

Voici les éléments qui composent l'exemple de requête :

  • SELECT colonnes : sélectionne les colonnes à renvoyer. Utilisez * pour renvoyer toutes les colonnes.
  • FROM table : indique la table à partir de laquelle vous souhaitez obtenir les résultats.
  • WHERE : mot clé facultatif qui précède les conditions devant être remplies, par exemple colonne="valeur". Les opérateurs les plus courants sont =, LIKE, < et >. Pour relier plusieurs conditions, utilisez AND ou OR.

Autres éléments d'une requête :

  • ORDER BY : expression clé facultative permettant de trier les résultats par colonne. Spécifiez ASC pour l'ordre croissant et DESC pour l'ordre décroissant. Si vous n'indiquez pas d'ordre, les résultats sont triés dans l'ordre par défaut. Il se peut donc qu'ils ne soient pas ordonnés.
  • LIMIT : mot clé permettant de définir un nombre limite de résultats.

Exemples de requêtes et de résultats

Les requêtes suivantes utilisent la table définie précédemment :

SELECT * FROM WORD_LIST_TABLE

Récupère toutes les lignes de la table WORD_LIST_TABLE.
SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2 Sélectionne les colonnes word et definition de tous les éléments dont l'id est supérieur à 2.
Renvoie

[["alpha", "particle"]]

SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%"

Renvoie l'id du mot alpha avec la sous-chaîne art dans la définition.

[["3"]]

SELECT definition FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1

Sélectionne toutes les définitions. Effectue un tri dans l'ordre inverse, puis obtient la première ligne. Le tri s'effectue en fonction de la colonne spécifiée, à savoir word. Notez qu'il est possible d'effectuer un tri sur une colonne qui ne sera pas renvoyée.

[["second letter"]]

SELECT * FROM WORD_LIST_TABLE LIMIT 2,1

Renvoie 1 élément à partir de la position 2. La première position porte le numéro 1 (pas zéro). Renvoie [["2", "beta", "second letter"]]

Vous pouvez vous entraîner à créer et à interroger des bases de données sur le site Web SQL Fiddle.

Requêtes vers SQLite pour Android

Vous pouvez envoyer des requêtes à la base de données SQLite du système Android en tant que requêtes brutes ou en tant que paramètres.

La méthode rawQuery(String sql, String[] selectionArgs) exécute le code SQL fourni. Elle renvoie un Cursor pour l'ensemble de résultats. Voici les deux premiers exemples ci-dessus représentés sous forme de requêtes brutes :

1

String query = "SELECT * FROM WORD_LIST_TABLE";
rawQuery(query, null);

2

query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? ";
String[] selectionArgs = new String[]{"2"}
rawQuery(query, selectionArgs) ;

La méthode query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) interroge la table indiquée. Elle renvoie un Cursor pour l'ensemble de résultats. Voici une requête montrant comment renseigner les arguments :

SELECT * FROM WORD_LIST_TABLE
WHERE word="alpha"
ORDER BY word ASC LIMIT 2,1;

La requête renvoie le résultat suivant :

[["alpha", "particle"]]

Exemples d'arguments que vous pouvez utiliser :

String table = "WORD_LIST_TABLE"
String[] columns = new String[]{"*"};
String selection = "word = ?"
String[] selectionArgs = new String[]{"alpha"};
String groupBy = null;
String having = null;
String orderBy = "word ASC"
String limit = "2,1"

query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

Remarque : Dans la pratique, vous ne créeriez pas de variables pour les valeurs null. Consultez la documentation Android sur SQLiteDatabase pour connaître les versions disponibles pour cette méthode avec différents paramètres.

Curseurs

Un curseur est un pointeur sur une ligne de données structurées. On peut considérer qu'il s'agit d'un pointeur vers une ligne dans une table.

Une requête renvoie un objet Cursor qui pointe vers le premier élément du résultat de la requête. La classe Cursor fournit des méthodes permettant de déplacer le curseur dans le résultat de la requête, ainsi que des méthodes pour obtenir les données issues des colonnes de chaque ligne du résultat.

Lorsqu'une méthode renvoie un objet Cursor, vous itérez le résultat, vous extrayez les données, vous effectuez une opération sur les données et vous fermez le curseur pour libérer la mémoire.

En savoir plus