Utiliser SQLite dans une application AIR

L'environnement AIR (Adobe Integrated Runtime) permet de créer des applications riches exécutées en local sous la forme d'applications clientes autonomes sous toutes les plateformes.

Il y a de multiples raisons d'utiliser SQLite avec AIR :

• SQLite est open source et implémente la majeure partie de la norme SQL-92 ;
• la base de données est contenue dans un fichier unique et léger, aucun serveur n'est donc nécessaire ;
• SQLite est une bibliothèque écrite en C qui est embarquée dans Adobe AIR, il n'y a donc aucune librairie supplémentaire à utiliser.

AIR permet l'utilisation de SQLite en mode synchrone ou asynchrone.
L'avantage d'utiliser le mode asynchrone est que l'interface n'est pas figée pendant les opérations mais l'inconvénient est que le nombre de lignes de code est plus important.
En utilisant le mode synchrone le code est plus simple et cela reste suffisant dans la plupart des cas.

La base de données étant stockée dans un fichier unique il faut donc déterminer le fichier à utiliser pour établir la connexion à la base.

Dans l'exemple l'objet de type File pointe sur le fichier "database.sqlite" présent dans le répertoire de stockage de l'application.
Ce répertoire unique et spécifique à l'application est créé lors du premier accès à la propriété applicationStorageDirectory.
Il utilise le protocole app-storage: et non le classique file:, l'URL du fichier dans l'exemple est donc "app-storage:/database.sqlite".
L'emplacement du dossier sur le disque peut varier suivant le système d'exploitation. Un exemple sur MacOS :
/Users/<nom utilisateur>/Library/Preferences/<nom application>/Local Store.

Lorsque la base de données n'a pas déjà été créée, l'objet File doit pointer sur un fichier inexistant. Cela peut être vérifié avec la propriété File.exists et dans ce cas il faut utiliser un mode de connexion spécifique à savoir SQLMode.CREATE pour que le fichier soit créé lors de la connexion.

Les différents modes de connexion sont les suivants :

• SQLMode.READ : la connexion est ouverte en mode lecture seule ;
• SQLMode.UPDATE : la connexion est ouverte pour les mises à jour mais une nouvelle base de données n'est pas créée si le fichier spécifié n'existe pas ;
• SQLMode.CREATE : la connexion est ouverte pour les mises à jour et un fichier de base de données est créée si le fichier spécifié n'existe pas.

Les méthodes open et openAsync de la classe SQLConnection sont utilisées pour établir la connexion.

 var DatabaseFile:File = File.applicationStorageDirectory.resolvePath("database.sqlite");

 var connection:SQLConnection = new SQLConnection();
 connection.open(DatabaseFile, SQLMode.READ);
 ...
 connection.close();

 var DatabaseFile:File = File.applicationStorageDirectory.resolvePath("database.sqlite");

 var connection:SQLConnection = new SQLConnection();
 connection.addEventListener(SQLEvent.OPEN, DatabaseOpenSuccessHandler);
 connection.openAsync(DatabaseFile, SQLMode.READ);

 private function DatabaseOpenSuccessHandler(evt:SQLEvent):void
 {
     ...
     connection.close();
 }

Il est aussi possible de travailler sur plusieurs bases de données à partir de la même connexion.

La méthode attach permet d'ajouter une base à la connexion en spécifiant un nom unique pour la base.
De cette façon il est ensuite possible d'indiquer explicitement dans les requêtes la base à laquelle correspond une table en utilisant la syntaxe [nom-base].[nom-table].
Les noms "main" et "temp" ne doivent pas être utilisés.
Comme pour la base principale il faut définir le fichier qui contiendra la base.

L'appel à la méthode detach supprime la connexion à la base correspondant au nom spécifié.

  var DatabaseFile:File = File.applicationStorageDirectory.resolvePath("database.sqlite");
  var DatabaseFile2:File = File.applicationStorageDirectory.resolvePath("database2.sqlite");

  var connection:SQLConnection = new SQLConnection();
  connection.open(DatabaseFile, SQLMode.READ);
  ...
  connection.attach("autrebase", DatabaseFile2);

  // Execution de requêtes avec la syntaxe autrebase.[nom-table]

  connection.detach("autrebase");
  ...
  connection.close();

L'exécution des requêtes s'effectue avec la classe SQLStatement.
Il faut spécifier la connexion utilisée qui doit être ouverte au préalable et ensuite définir la requête SQL à exécuter simplement par l'appel à la méthode execute.

Le résultat est récupéré dans un objet SQLResult.

Dans le cas d'une requête d'insertion dans une table avec une clé auto-incrémentée la propriété lastInsertRowID contient l'identifiant généré pour le dernier enregistrement inséré.

Dans le cas d'une requête de mise à jour la propriété rowsAffected contient le nombre d'enregistrements affectés par la mise à jour.

Dans le cas d'une requête de sélection la propriété data est un tableau contenant les enregistrements retournés. Si le résultat ne contient aucun enregistrement la propriété data est égale à null.


Par défaut chaque élément du tableau est une variable Object dont les noms des propriétés correspondent aux noms des colonnes des données du jeu de résultats.

Il est possible de spécifier le type des objets correspondant aux résultats d'une requête en utilisant l'attribut itemClass (de type Class) de la classe SQLStatement.
De cette façon les objets contenus dans le tableau de résultats ne seront plus de type Object mais du type spécifié.
Ces objets sont instanciés automatiquement.

Attention, la classe spécifiée doit avoir un constructeur qui ne requiert aucun paramètre. De plus, pour chaque champ sélectionné doit correspondre une propriété de la classe.

Dans les exemples suivants les requêtes sont exécutées sur une simple table "Utilisateur" avec la structure suivante :

  var statement:SQLStatement = new SQLStatement();
  statement.sqlConnection = connection;
  statement.text = "SELECT * FROM Utilisateur";

  statement.execute();

  var result:SQLResult = statement.getResult();

  connection.close();

  var count:uint = 0;
  if(result.data != null)
  {
      count = result.data.length;
  }
  trace("Il y a " + count + " utilisateur(s)");

  var statement:SQLStatement = new SQLStatement();
  statement.addEventListener(SQLEvent.RESULT, DatabaseResultHandler);
  statement.sqlConnection = connection;
  statement.text = "SELECT * FROM Utilisateur";
  statement.execute();

  private function DatabaseResultHandler(evt:SQLEvent):void
  {
      var result:SQLResult = SQLStatement(event.target).getResult();

      var count:uint = 0;
      if(result.data != null)
      {
          count = result.data.length;
      }
      trace("Il y a " + count + " utilisateur(s)");
  }

Par défaut voici les objets récupérés :

L'exemple suivant utilise la classe Utilisateur pour typer les résultats :

  public class Utilisateur
  {
      public var Nom:String;
      public var Prenom:String;
      public var DateInscription:Date;
    
      public function Utilisateur()
      {
          Nom = "";
          Prenom = "";
          DateInscription = new Date();
      }
    
      public function get Description():String
      {
          return "Utilisateur: " + Nom + " " + Prenom + " Inscrit le " + DateInscription.toDateString();
      }
  }

  var statement:SQLStatement = new SQLStatement();
  statement.sqlConnection = connection;
  statement.text = "SELECT * FROM Utilisateur";
  statement.itemClass = Utilisateur;

  statement.execute();

  var result:SQLResult = statement.getResult();

  connection.close();

  Utilisateur ut;
  for(var i:uint=0; i<statement.data.length; i++)
  {
    ut = statement.data[i];
    trace( ut.Description );
  }

Les objets Utilisateur récupérés :

Lors de la construction des requêtes il est préférable d'utiliser les paramètres de l'objet SQLStatement. Cela évite de devoir convertir manuellement les valeurs en chaînes de caractères et donc limite le risque d'erreur.

Les paramètres peuvent être nommés ou non.
Dans le cas de paramètres nommés la propriété parameters est un objet dont le nom des propriétés correspond aux noms des paramètres. Les paramètres doivent être préfixés par le caractère '@' ou ':'.
Si les paramètres ne sont pas nommés (?) la propriété parameters est un tableau contenant l'ensemble des paramètres.

  statement.text = "SELECT * FROM Utilisateur WHERE Name=@Name";
  statement.parameters["@name"] = user.name;

  statement.text = "SELECT * FROM Utilisateur WHERE Name=?";
  statement.parameters[0] = user.name;

La gestion des erreurs lors de la connexion ou de l'exécution d'une requête s'effectue de deux façons différentes, en mode synchrone ou asynchrone.

En mode synchrone il faut gérer les exceptions de type SQLError :

  try
  {
      var statement:SQLStatement = new SQLStatement();
      statement.sqlConnection = connection;
      statement.text = "SELECT * FROM Utilisateur";
    
      statement.execute();
    
      var result:SQLResult = statement.getResult();
    
      var count:uint = 0;
      if(result.data != null)
      {
          count = result.data.length;
      }
      trace("Il y a " + count + " utilisateur(s)");
  }
  catch(error:SQLError)
  {
      trace("Erreur : " + error.toString());
  }
  finally
  {
      connection.close();
  }

En mode asynchrone il faut écouter l'évènement SQLErrorEvent.ERROR sur un objet SQLConnection ou SQLStatement :

  statement.addEventListener(SQLErrorEvent.ERROR, DatabaseErrorHandler);

  private function DatabaseErrorHandler(evt:SQLErrorEvent):void
  {
      trace("Erreur : " + evt.error.toString());
  }

Les transactions sont généralement utilisées pour assurer l'intégrité des données.
Elles permettent d'exécuter plusieurs requêtes et de pouvoir revenir à l'état initial de la base dans le cas où une des requêtes provoque une erreur.
Les données contenues dans la base restent donc cohérentes.

Avec SQLite les transactions sont aussi utilisées dans le cas d'une insertion importante de données.
En effet lorsqu'un grand nombre de requêtes d'insertion est effectué à la suite, l'utilisation d'une transaction améliore grandement les performances.
L'exécution est jusqu'à deux fois plus rapide pour l'insertion de quelques milliers d'enregistrements.

Les transactions sont gérées directement avec la classe SQLConnection qui contient les trois méthodes classiques begin, commit et rollback.
La méthode begin est utilisée pour démarrer une nouvelle transaction avec le mode spécifié (voir ci-dessous).
La méthode commit est utilisée pour valider et terminer la transaction.
La méthode rollback est utilisée pour annuler la transaction.

Les différents modes de transaction sont les suivants :

• SQLTransactionLockType.DEFERRED : un verrou n'est pas acquis avant la première lecture ou écriture ;
• SQLTransactionLockType.EXCLUSIVE: un verrou est acquis dès que possible et aucune autre instance de la classe SQLConnection ne peut lire ou écrire dans la base de données ;
• SQLTransactionLockType.IMMEDIATE : un verrou est acquis dès que possible et les autres instances de la classe SQLConnection peuvent uniquement lire la base de données.

Les transactions ne sont pas limitées aux opérations effectuées sur une seule base, elles peuvent contenir des opérations effectuées sur différentes bases attachées à la connexion.

  try
  {
      connection.begin(SQLTransactionLockType.EXCLUSIVE);

      // Opérations ...

      connection.commit();
  }
  catch(error:SQLError)
  {
      connection.rollback();
      trace("Erreur : " + error.toString());
  }
  finally
  {
      connection.close();
  }

Lorsqu'une base contient beaucoup de données, il est souvent nécessaire de ne récupérer qu'une partie des résultats, par exemple pour l'affichage dans une liste.

Il y a deux façons de procéder, la première est d'utiliser le mécanisme de la classe SQLStatement qui permet de limiter le nombre d'enregistrements retournés en passant ce nombre en paramètre de la méthode execute. Les enregistrements suivants sont récupérés avec l'appel à la méthode next. La propriété complete indique si l'ensemble des enregistrements a été retourné.

L'inconvénient de cette fonctionnalité est qu'on ne peut pas contrôler l'ordre dans lequel les enregistrements sont récupérés.

L'autre possibilité est de modifier directement la requête SQL pour filtrer les éléments retournés.
Les mots clés LIMIT et OFFSET permettent d'effectuer une pagination personnalisée.

L'exemple suivant permet de récupérer les 5 premiers utilisateurs :

  statement.text = "SELECT * FROM Utilisateur LIMIT @count OFFSET @start";
  statement.parameters["@count"] = 5;
  statement.parameters["@start"] = 0;

La classe SQLConnection permet de récupérer le schéma de la base (SQLSchemaResult) contenant la liste et la description des tables (SQLTableSchema), des colonnes (SQLColumnSchema), des vues (SQLViewSchema) et des triggers (SQLTriggerSchema).

L'exemple suivant montre comment afficher la liste des tables :

  connection.loadSchema();
  var schema:SQLSchemaResult = connection.getSchemaResult();
  for each (var table:SQLTableSchema in schema.tables)
  {
      trace(table.name);
  }

Les différents types à utiliser pour définir les colonnes des tables sont les suivants :

• TEXT (ou STRING)
• NUMERIC
• INTEGER
• REAL (ou NUMBER)
• BOOLEAN
• DATE
• XML
• XMLLIST
• OBJECT
• NONE

Il est possible de stocker des objets complexes avec le type OBJECT, il suffit d'ajouter à la classe la métadonnée [RemoteObject].
L'objet sera automatiquement sérialisé au format AMF3.

Le tableau ci-dessous indique la correspondance avec les types AS3 les plus communs :

Le type NUMERIC accepte les type uint , int ou Number et retourne un type différent suivant la valeur.
Par exemple si l'objet initial est de type Number et vaut 2 le type récupéré par la suite sera uint.

Vous pouvez consulter la documentation sur les types de données SQLite dans AIR pour plus d'informations.

L'utilisation de SQLite dans une application AIR est très simple et rapide à mettre en place. Une utilisation poussée est aussi possible grâce aux nombreuses fonctionnalités présentées dans cet article.

Liens utiles :

• présentation AIR [lien]
• le forum SQLite [lien]
• le forum Flex [lien]

Consultez aussi le tutoriel et créez une application avec SQLite !

Un grand merci à ellene pour son aide et à jacques_jean et Kerod pour leur relecture.



Article au format PDF