Utilisation de SQLite avec FireDAC

De RAD Studio
Aller à : navigation, rechercher

Remonter à Utilisation des SGBD (FireDAC)


Cet article de référence comporte plusieurs sections :

  • Introduction à SQLite : rappelle les fonctionnalités de SQLite, les fonctionnalités non traitées, les applications possibles et les applications non adaptées à SQLite.
  • Utilisation d'une base de données SQLite : explique comment créer une base de données SQLite, s'y connecter et la gérer dans une application Delphi.
  • Base de données SQLite cryptée : le cryptage de base de données est l'une des fonctionnalités majeures de SQLite. Cette rubrique explique son fonctionnement et la manière de le contrôler.
  • Types de données SQLite : SQLite utilise un système de types de données unique. Il est difficile de stocker et d'extraire efficacement les données des applications Delphi si l'on ignore son fonctionnement.
  • Commandes SQL de SQLite : principaux aspects du dialecte SQL de SQLite pour les développeurs d'applications Delphi.
  • Transactions, verrouillage et curseurs SQLite : explique le fonctionnement des transactions dans un environnement SQLite.
  • Extension du moteur SQLite : en tant que SGBD incorporé, le moteur SQLite peut être étendu par le code d'application Delphi.
  • Techniques SQLite avancées : enfin, cette section présente certains concepts SQLite avancés, comme la consignation des mises à jour et l'autorisation SQL.

Cet article nécessite de connaître les fonctionnalités de base de FireDAC et les principales API de bibliothèques. Nous recommandons aux novices de commencer par l'article Introduction et de consulter la démo FireDAC\Samples\Getting Started\SQLite.

Sommaire

Introduction à SQLite

Base de données SQLite

SQLite est un moteur incorporé de bases de données SQL, développé par SQLite Consortium. C'est le SGBD le plus largement déployé dans le monde (500 millions d'installations environ). Vous pouvez le trouver sur tous les périphériques mobiles iOS et Android, ainsi que sur les postes de travail Mac OS. Il est utilisé par Firefox, Skype, et le logiciel anti-virus McAfee.

Fonctionnalités de SQLite

La source liste les éléments suivants :

  • Les transactions sont de type ACID (Atomic, Consistent, Isolated et Durable), y compris après une défaillance système et une panne de courant.
  • Aucune configuration - aucune configuration ni administration n'est requise.
  • Implémente la plupart des paramètres de la norme SQL92. Prend en charge les déclencheurs de table et les vues.
  • Une base de données complète est stockée dans un fichier disque multiplate-forme unique.
  • Prend en charge les bases de données de plusieurs téraoctets, ainsi que les chaînes et blobs de plusieurs gigaoctets.
  • Plus rapide que les principaux moteurs de bases de données client/serveur pour les opérations les plus courantes.
  • Autonome : aucune dépendance externe.
  • Multi-périphérique : Windows, macOS, iOS et Android sont tous pris en charge.
  • Les sources sont dans le domaine public. Leur utilisation est libre.
  • API très puissante, vous permettant d'étendre le moteur dans quasiment toutes les zones.
  • SQLite permet de profiter de performances d'accès aux données optimales par rapport aux autres moteurs incorporés de bases de données fichier-serveur et client-serveur utilisés par les applications Delphi. Nous connaissons de nombreuses applications réussies utilisant des bases de données de plusieurs gigaoctets. Par exemple, Silwood Technology Saphir est construit avec SQLite, Delphi et FireDAC.

Fonctionnalités manquantes de SQLite

D'après notre expérience, les développeurs recherchent souvent les fonctionnalités suivantes, qui ne sont pas fournies dans SQLite :

  • Procédures stockées -- FireDAC fournit des API de fonctions personnalisées.
  • Atteindre un ensemble de fonctions pré-intégrées -- FireDAC pré-installe environ 50 fonctions standard.
  • Système de sécurité, notamment un concept utilisateur et un concept droits d'accès -- FireDAC fournit une base de données cryptée, protégée par mot de passe et des rappels spéciaux pour filtrer les actions des utilisateurs.
  • Classements (format ASCII et binaire uniquement) -- FireDAC vous permet de définir des classements personnalisés.

Applications SQLite

La source liste les éléments suivants :

  • Format de fichier d'application -- SQLite a été utilisé avec beaucoup de succès comme format de fichier sur disque pour les plates-formes de bureau.
  • Périphériques et applications incorporés -- La base de données SQLite nécessitant peu ou pas d'administration, SQLite constitue un bon choix pour les périphériques ou les services fonctionnant sans surveillance et sans support technique.
  • Sites Web -- SQLite fonctionne généralement bien comme moteur de base de données de sites Web générant un trafic moyen à faible (soit 99,9 % des sites Web).
  • Remplacement des fichiers disque Ad-hoc -- SQLite fonctionne particulièrement bien en remplacement des fichiers de données Ad-hoc.
  • Bases de données internes ou temporaires -- Pour les programmes, il est possible de simplifier les opérations de filtre et de tri en chargeant les données dans la base de données SQLite en mémoire et en utilisant une commande SQL à l'échelle complète.
  • Support de base de données d'entreprise pendant les démonstrations ou les tests.
  • Pédagogie en matière de bases de données.

Applications NON ADAPTEES à SQLite

La source liste les éléments suivants :

  • Simultanéité élevée -- SQLite utilise des verrous de lecture/écriture sur l'ensemble du fichier de base de données. Ainsi, lorsqu'un processus est en train de lire une partie de la base de données, tous les autres processus sont dans l'incapacité d'écrire sur une autre partie de la base de données. De même, lorsqu'un processus est en train d'écrire dans la base de données, tous les autres processus sont dans l'incapacité de lire une autre partie de la base de données.
  • Applications client/serveur -- Si la plupart de vos programmes client accèdent à une base de données commune sur un réseau, vous devez envisager d'utiliser un moteur de base de données client/serveur à la place de SQLite. SQLite fonctionne sur un système de fichiers réseau, mais en raison de la latence associée à la plupart des systèmes de fichiers réseau, les performances ne sont pas idéales.
  • Très grands ensembles de données (N To).

Utilisation d'une base de données SQLite

Connexion à une base de données SQLite à partir d'une application Delphi

Pour lier le pilote FireDAC SQLite dans l'application Delphi, ajoutez un TFDPhysSQLiteDriverLink dans une fiche ou un module de données. Pour établir une connexion à une base de données SQLite, spécifiez les paramètres du pilote SQLite, soit au minimum : 

DriverID=SQLite
Database=<path to SQLite database>

Par défaut, tous les paramètres du pilote SQLite sont définis pour un accès via une connexion unique haute performance à une base de données dans un environnement stable. La commande PRAGMA permet de configurer SQLite. La plupart des paramètres du pilote FireDAC SQLite correspondent aux commandes pragma. Par ailleurs, SQLiteAdvanced permet de spécifier plusieurs commandes pragma séparées par des ';' comme paramètre de connexion unique.

Les autres cas d'utilisation de SQLite sont les suivants :

N° Spécificité de l'application Description
1 Lecture de bases de données volumineuses. Définissez CacheSize sur un plus grand nombre de pages. Elles seront utilisées pour mettre en cache les données de la base de données. La taille totale du cache sera CacheSize * <taille des pages de la base de données>.
2 Mise à jour exclusive de la base de données. Envisagez de définir JournalMode sur WAL (plus d'informations).
3 Transactions de mises à jour longues. Définissez CacheSize sur un plus grand nombre de pages. Cela vous permettra d'exécuter des transactions comportant de nombreuses mises à jour sans surcharger le cache mémoire avec des pages "brouillon".
4 Quelques processus de mises à jour simultanés. Définissez LockingMode sur Normal pour activer l'accès partagé à la base de données. Définissez Synchronous sur Normal ou Full pour que les données validées soient visibles par les autres. Définissez UpdateOptions.LockWait sur True pour activer l'attente de verrous. Augmentez BusyTimeout pour accroître le temps d'attente d'un verrou. Envisagez de définir JournalMode sur WAL.
5 Quelques threads de mises à jour simultanés. Voir le numéro 4. Définissez également SharedCache sur False pour minimiser les conflits de verrouillage.
6 Quelques transactions de mises à jour simultanées. Voir le numéro 4 ou 5. Définissez également TxOptions.Isolation sur xiSnapshot ou xiSerializible pour éviter les possibles verrous mortels de transactions.
7 Sécurité élevée Définissez Synchronous sur Full pour protéger la base de données contre la perte des données validées. Voir aussi le numéro 3. Envisagez de crypter la base de données pour fournir l'intégrité.
8 Confidentialité élevée. Cryptez la base de données pour fournir la confidentialité et l'intégrité.
9 Temps de développement Définissez LockingMode sur Normal pour activer l'utilisation simultanée de la base de données SQLite dans l'EDI et d'un programme débogué.

Création d'une base de données SQLite à partir d'une application Delphi

Par défaut, la base de données SQLite est créée à l'établissement d'une connexion si aucune n'existe déjà. Pour un contrôle explicite, l'application Delphi peut spécifier : 

OpenMode=CreateUTF8 | CreateUTF16 | ReadWrite | ReadOnly

Les deux premières valeurs utilisées pour la création diffèrent au niveau de l'encodage utilisé pour la nouvelle base de données. Nous vous recommandons également de définir page_size sur 4096 ou plus pour les bases de données comportant des tables de plusieurs lignes. Pour cela, vous pouvez spécifier le code suivant à la création : 

SQLiteAdvanced=page_size=4096

Envisagez de spécifier des paramètres en utilisant SQLiteAdvanced :

Notez qu'après la création du fichier de base de données, la taille est de zéro. En conséquence, l'encodage de la base de données, la taille de page et les autres paramètres persistants ne sont pas enregistrés dans la base de données. Pour rendre ce type de paramètres persistants, l'application doit créer au moins une table.

Utilisation d'une base de données SQLite en mémoire dans une application Delphi

SQLite offre une autre fonctionnalité unique, sa capacité à exploiter des bases de données en mémoire pures. Cela signifie qu'aucun fichier n'est créé pour stocker les objets de bases de données et que tout est conservé dans la mémoire. De cette manière, vous profitez d'une meilleure sécurité, de performances accrues et d'exigences restreintes en matière de droits d'accès à l'environnement d'une application Delphi.

Pour créer et ouvrir une base de données SQLite en mémoire, utilisez les paramètres suivants : 

DriverID=SQLite
Database=:memory:

Ou laissez simplement le paramètre Database vide : 

DriverID=SQLite

Un client de FireDAC disposait d'une base de données SQLite sur une ressource réseau partagée. Cette base de données était un catalogue de produits accessible en lecture seule. Elle comportait de nombreux attributs pour les produits stockés. Pour améliorer radicalement les performances, le client à utilisé un TFDSQLiteBackup afin de déplacer l'intégralité de la base de données vers la base de données en mémoire. Exemple de code : 

FDConnection1.DriverName := 'SQLite';
FDConnection1.Open;

FDSQLiteBackup1.Database := '\\srv\db\data.sdb';
FDSQLiteBackup1.DestDatabaseObj := FDConnection1.CliObj;
FDSQLiteBackup1.DestMode := smCreate;
FDSQLiteBackup1.Backup;

Utilisation d'Unicode et des bases de données SQLite

FireDAC prend entièrement en charge le langage Unicode de Delphi (à partir de Delphi 2009). Pour SQLite, cela signifie que :

  • FireDAC configure automatiquement une base de données SQLite pour échanger toutes les métadonnées encodées au format UTF-16, lors de l'utilisation de Delphi 2009 ou ultérieur. Dan Delphi 2007 ou antérieur, les métadonnées sont encodées au format ANSI.
  • Les données sont définies et échangées comme expliqué dans la section "Mappage de SQLite sur les types de données FireDAC".

Utilisation de plusieurs bases de données SQLite dans une application Delphi

SQLite vous permet d'utiliser plusieurs bases de données en une seule connexion. La base de données spécifiée par le paramètre Database est la base de données principale. Pour attacher d'autres bases de données, l'application Delphi doit utiliser la commande ATTACH. Par exemple : 

FDConnection1.ExecSQL('ATTACH ''c:\hr.sdb'' AS hr');
FDConnection1.ExecSQL('ATTACH ''c:\cust.sdb'' AS cust');
FDQuery1.Open('select * from "Orders" o ' +
  'left join hr."Employees" e on o.EmployeeID = e.EmployeeID ' +
  'left join cust."Customers" c on o.CustomerID = c.CustomerID');
Remarque: FireDAC interprète le nom d'une base de données comme un nom de catalogue.

Gestion d'une base de données SQLite à partir d'une application Delphi

Une application de base de données SQLite Delphi (et pas seulement) adéquate doit tenir compte des faits suivants :

  • La base de données SQLite peut être fragmentée et ne plus être optimale suite à de nombreuses mises à jour ou suppressions d'enregistrements "en dur". L'appel à la méthode TFDSQLiteValidate.Sweep optimise la base de données. Cette méthode correspond à la commande VACUUM et à PRAGMA auto_vacuum. Exemple :
FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.Sweep;
  • L'optimiseur de requêtes SQLite construit un meilleur plan d'exécution de requêtes lorsqu'il dispose de statistiques à jour sur la base de données. SQLite ne met pas automatiquement à jour les statistiques. L'appel à la méthode TFDSQLiteValidate.Analyze les collecte. Cette méthode utilise la commande ANALYZE. Une application peut collecter les statistiques de la base de données complète :
FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.Analyze;
  • La base de données SQLite peut être corrompue ou mal formée. Pour vérifier son intégrité, utilisez la méthode TFDSQLiteValidate.CheckOnly. Notez que pour réparer une base de données SQLite détériorée, l'application Delphi doit restaurer cette base de données à partir d'une sauvegarde. La méthode CheckOnly utilise le gestionnaire d'événement OnProgress pour notifier les problèmes. Cette méthode applique la commande PRAGMA integrity_check.
procedure TForm1.FDSQLiteValidate1Progress(ASender: TFDPhysDriverService; const AMessage: String);
begin
  Memo1.Lines.Add(AMessage);
end;

FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.OnProgress := Form1Progress;
FDSQLiteValidate1.CheckOnly;
  • La base de données SQLite doit être sauvegardée régulièrement pour éviter la perte de données. Le composant TFDSQLiteBackup effectue une copie de sauvegarde de la base de données. Le code de sauvegarde le plus simple est celui-ci :
FDSQLiteBackup1.Database := 'c:\db.sdb';
FDSQLiteBackup1.DestDatabase := 'c:\db.backup';
FDSQLiteBackup1.DestMode := smCreate;
FDSQLiteBackup1.Backup;

Base de données SQLite cryptée

Approche

L'une des fonctionnalités spécifiques de SQLite est le cryptage haut débit renforcé de bases de données. Vous pouvez ainsi rendre le contenu du fichier de base de données confidentiel et appliquer un contrôle d'intégrité sur le fichier de base de données.

Le format d'une base de données cryptée n'est pas compatible avec d'autres extensions de cryptage SQLite similaires. Vous ne pouvez donc pas utiliser une base de données cryptée avec des bibliothèques non FireDAC. Si vous devez le faire, commencez par décrypter la base de données avec l'outil d'origine, puis cryptez-la avec FireDAC.

Le cryptage est fourni via l'approche SQLite officiellement supportée - un code codec personnalisé et une compilation réalisée en définissant le paramètre SQLITE_HAS_CODEC. Toutes les routines de cryptage sont implémentées sur Delphi et incorporées au code sqlite3. Le cryptage est donc correctement géré pour les éléments suivants :

Modes de cryptage

Mode Description Usage
AES-NNN Les algorithmes aes-NNN sont des compositions génériques de AES-CTR et AES-CBC-MAC. Ces compositions garantissent la confidentialité et l'intégrité, c'est-à-dire que seules les entités ayant accès au mot de passe correct peuvent lire et modifier les pages de la base de données cryptée. Ces algorithmes ajoutent un supplément linéaire de 32 octets par page à votre base de données cryptée.

Ils permettent de détecter les tentatives les plus malveillantes d'injection de données dans la base de données. Toutefois, ils n'empêchent pas ces tentatives et ne permettent pas d'annuler de telles modifications. Ils agissent en complément des sauvegardes fréquentes, mais sont bien meilleurs que la plupart des autres schémas de cryptage des bases de données puisqu'ils vous avertissent en cas d'attaque et vous indiquent à quel moment restaurer à partir d'une sauvegarde. Notez que l'algorithme aes-NNN, seul, ne détecte pas la suppression de pages entières à la fin de la base de données (mais il détecte les suppressions au milieu de la base de données). Il ne détecte pas non plus les attaques consistant à rétablir une version antérieure de la base de données en utilisant le même mot de passe.

L'algorithme AES-NNN offre une confidentialité et une intégrité optimales. Cependant, cette capacité s'acccompagne d'une diminution sensible des performances par rapport aux autres modes de cryptage.
AES-CTR-NNN Les algorithmes aes-ctr-NNN sont de type AES-CTR uniquement. Ils ne détectent pas les modifications de la base de données, mais ils fournissent une confidentialité en cas d'attaques passives. C'est-à-dire que tant que l'attaquant n'a pas accès à votre mot de passe et ne tente pas de modifier la base de données pour voir comment votre application réagit aux modifications, vos données restent aussi confidentielles que votre application le permet.

Il va sans dire que les algorithmes protègent uniquement votre fichier de base de données contre les attaquants qui ne sont pas en mesure d'exploiter votre application FireDAC en utilisant, par exemple, un débogueur pour extraire le mot de passe. De la même manière, si vous stockez votre mot de passe dans un fichier de configuration ou sous la forme d'une constante dans le logiciel même, il sera plutôt facile pour un attaquant, même modérément compétent, de le trouver et de compromettre votre sécurité.

L'algorithme AES-CTR-NNN offre une confidentialité optimale, mais aucune intégrité. C'est à cette condition que les performances sont améliorées.
AEC-ECB-NNN Les algorithmes aes-ecb-NNN sont de type AES-ECB uniquement. Ils ne détectent pas les modifications de la base de données, et ne fournissent pas une confidentialité élevée en cas d'attaques passives, contrairement aux algorithmes AES-NNN et AES-CTR-NNN. L'algorithme AES-ECB-NNN offre une confidentialité tout aussi faible et aucune intégrité. Cependant, il fournit les meilleures performances par rapport aux autres modes de cryptage.

NNN correspond à la taille de la clé, qui peut être 128, 192 ou 256 bits.

Configuration du cryptage

Le cryptage peut être contrôlé :

  • via les paramètres de définition de la connexion Encrypt, NewPassword et Password.
  • via le composant service TFDSQLiteSecurity.

Les paramètres password de définition de la connexion peuvent avoir la forme suivante : 

[aes-128 | aes-192 | aes-256 | aes-ctr-128 | aes-ctr-192 | aes-ctr-256 |
 aes-ecb-128 | aes-ecb-192 | aes-ecb-256:] password

"aes-XXX-NNN" : est un préfixe facultatif, contrôlant l'algorithme de chiffrement à utiliser. S'il n'est pas spécifié, alors les éléments suivants sont utilisés :

  • un algorithme spécifié par le paramètre Encrypt
  • aes-256 si rien n'est spécifié

FireDAC prend en charge les opérations de cryptage :

Opération En utilisant des paramètres En utilisant TFDSQLiteSecurity
Ouvrir une base de données cryptée Password=xxxx ---
Crypter une base de données non cryptée NewPassword=xxxx FDSQLiteSecurity1.Database := '...';

FDSQLiteSecurity1.Password := 'xxxx';

FDSQLiteSecurity1.SetPassword;

Changer le mot de passe d'une base de données cryptée Password=xxxx

NewPassword=yyyy

FDSQLiteSecurity1.Database := '...';

FDSQLiteSecurity1.Password := 'xxxx';

FDSQLiteSecurity1.ToPassword := 'yyyy';

FDSQLiteSecurity1.ChangePassword;

Décrypter une base de données cryptée Password=xxxx

NewPassword=

FDSQLiteSecurity1.Database := '...';

FDSQLiteSecurity1.Password := 'xxxx';

FDSQLiteSecurity1.RemovePassword;

Vérifier le statut de cryptage d'une base de données ---

NewPassword=

FDSQLiteSecurity1.Database := '...';

FDSQLiteSecurity1.Password := 'xxxx';

ShowMessage(FDSQLiteSecurity1.CheckEncryption);

Extension SQL

La commande ATTACH a une extension. La syntaxe complète de la commande ATTACH est désormais la suivante : 

ATTACH [DATABASE] 'filename' [AS name] [KEY 'password']

Si KEY est omis, alors la valeur du mot de passe est héritée de la base de données principale. Pour spécifier un mot de passe vide afin d'attacher une base de données non cryptée, utilisez un code similaire au code suivant : 

ATTACH 'D:\tmp\test.db' AS tst KEY ''

Types de données SQLite

Mappage de SQLite sur les types de données FireDAC

SQLite utilise un système de types de données "sans type". Cela signifie que vous pouvez utiliser n'importe quel identifiant comme nom de type de données pour une colonne. Par exemple, "Delphi" fonctionne également et correspond au type de données chaîne. Pour que l'approche SQLite soit plus compatible avec d'autres SGBD et Delphi, mais aussi plus accessible aux développeurs d'applications Delphi, FireDAC reconnaît les noms de types de données présentés dans le tableau suivant :

Nom du type Description
ROWID | _ROWID_ | OID dtInt64, Attrs = [caSearchable, caAllowNull, caROWID]
BIT | BOOL | BOOLEAN | LOGICAL | YESNO dtBoolean
TINYINT | SHORTINT | INT8 [UNSIGNED] dtSByte / dtByte
BYTE | UINT8 dtByte
SMALLINT | INT16 [UNSIGNED] dtInt16 / dtUInt16
WORD | UINT16 | YEAR dtUInt16
MEDIUMINT | INTEGER | INT | INT32 [UNSIGNED] dtInt32 / dtUInt32
LONGWORD | UINT32 dtUInt32
BIGINT | INT64 | COUNTER | AUTOINCREMENT | IDENTITY [UNSIGNED] dtInt64 / dtUInt64
LONGLONGWORD | UINT64 dtUInt64
REAL | FLOAT | DOUBLE dtDouble
SINGLE [PRECISION] [(P, S)] dtSingle / dtBCD / dtFmtBCD
DECIMAL | DEC | NUMERIC | NUMBER [UNSIGNED] [(P, S)] dtSByte / dtInt16 / dtInt32 / dtInt64

dtByte / dtUInt16 / dtUInt32 / dtUInt64

dtBCD / dtFmtBCD

MONEY | SMALLMONEY | CURRENCY | FINANCIAL [(P, S)] dtCurrency
DATE | SMALLDATE dtDate
DATETIME | SMALLDATETIME dtDateTime
TIMESTAMP dtDateTimeStamp
TIME dtTime
CHAR | CHARACTER [(L)] dtAnsiString, Len = L, Attrs = [caFixedLen]
VARCHAR | VARCHAR2 | TYNITEXT | CHARACTER VARYING | CHAR VARYING [(L)] dtAnsiString, Len = L
NCHAR | NATIONAL CHAR | NATIONAL CHARACTER [(L)] dtWideString, Len = L, Attrs = [caFixedLen]
NVARCHAR | NVARCHAR2 | NATIONAL CHAR VARYING | STRING [(L)] dtWideString, Len = L
RAW | TYNIBLOB | VARBINARY | BINARY | BINARY VARYING [(L)] dtByteString, Len = L
BLOB | MEDIUMBLOB | IMAGE | LONGBLOB | LONG BINARY | LONG RAW | LONGVARBINARY | GENERAL | OLEOBJECT | TINYBLOB dtBlob
MEDIUMTEXT | LONGTEXT | CLOB | MEMO | NOTE | LONG | LONG TEXT | LONGCHAR | LONGVARCHAR | TINYTEXT dtMemo
TEXT | NTEXT | WTEXT | NCLOB | NMEMO | LONG NTEXT | LONG WTEXT | NATIONAL TEXT | LONGWCHAR | LONGWVARCHAR | HTML dtWideMemo
XMLDATA | XMLTYPE | XML dtXML
GUID | UNIQUEIDENTIFIER dtGUID
Autres types de données dtWideString
Remarque: Avec SQLite, la propriété FormatOptions.StrsTrim fonctionne pour tous les types de données chaîne.

Types de données SQLite spéciaux

Pour ajouter une colonne auto-incrémentée à une table, définissez une colonne en tant que INTEGER PRIMARY KEY AUTOINCREMENT. Ce type est mappé sur dtInt32, Attrs = [caAutoInc]. Pour plus de détails concernant la gestion des colonnes auto-incrémentées, lire "Champs auto-incrémentés".

Les colonnes avec les noms de type ROWID, _ROWID_ ou OID sont considérées comme des colonnes d'identification de ligne. Ces types sont mappés sur dtInt64, Attrs = [caSearchable, caAllowNull, caROWID]. Pour plus de détails concernant la gestion des colonnes d'identification de ligne, consultez "Champs d'identification unique". Le type ROWID de SQLite est le moyen le plus rapide d'accéder à une ligne spécifique : 

SELECT * FROM Orders WHERE ROWID = :RID

Ajustement du mappage FireDAC

Certains paramètres du pilote SQLite permettent à l'application Delphi d'ajuster la représentation des données :

Paramètre Description
StringFormat = Choose | Unicode | ANSI Si le format Unicode est utilisé, tous les dtAnsiString et dtMemo sont à nouveau présentés au client en tant que dtWideString et dtWideMemo. Si le format ANSI est utilisé, tous les dtWideString et dtWideMemo sont à nouveau présentés au client en tant que dtAnsiString et dtMemo. Si Choose est utilisé, un type de chaîne sera défini en utilisant le nom du type.
GUIDFormat = String | Binary Si le format binaire est utilisé, les dtGUID sont stockés dans une base de données en tant que valeur binaire de TGUID. Si le format chaîne est utilisé, les données apparaissent sous forme de chaîne au format {xxxxxxx}. Le format binaire requiert moins d'espace dans la base de données. Le format chaîne est plus facile à lire.
DateTimeFormat = String | Binary | DateTime Si le format binaire est utilisé, dtDate, dtTime et dtDateTime sont stockés dans une base de données en tant que valeur double au format de date julienne. Si le format chaîne est utilisé, les données apparaissent en tant que chaîne de caractères au format 'aaaa-mm-jj hh24:mi:ss'. Si le format DateTime est utilisé, les données apparaissent en tant que valeur double au format date / heure Delphi. Le format binaire requiert moins d'espace dans la base de données. Le format chaîne est plus utilisé dans les expressions SQL SQLite, DateTime peut être compatible avec certaines applications Delphi anciennes.
Remarque: La modification de GUIDFormat ou DateTimeFormat, lorsque la base de données n'est pas vide, peut générer des erreurs, car FireDAC peut échouer à lire et analyser les valeurs stockées.

Pour une expression dans une liste SELECT, SQLite évite les informations sur le nom du type. Lorsque l'ensemble de résultats n'est pas vide, FireDAC utilise les types de données valeur du premier enregistrement. Lorsqu'il est vide, FireDAC décrit ces colonnes en tant que dtWideString. Pour spécifier explicitement le type de données de la colonne, ajoutez  ::<nom du type> à l'alias de la colonne : 

SELECT count(*) as "cnt::INT" FROM mytab

Si l'application Delphi requiert une représentation de type de données native SQLite, utilisez les règles de mappage FireDAC. Par exemple, mappez les colonnes TEXT sur dtAnsiString et les colonnes INT sur dtInt64 : 

with FDQuery1.FormatOptions do begin
  OwnMapRules := True;
  with MapRules do begin
    SourceDataType := dtMemo;
    TargetDataType := dtAnsiString;
  end;
  with MapRules do begin
    SourceDataType := dtInt32;
    TargetDataType := dtInt64;
  end;
end;

Nombres avec une précision élevée

En raison du système de types SQLite, les nombres longs (>= 20 chiffres) sont reconnus comme ayant une affinité de type REAL. Par conséquent, une valeur est arrondie au type REAL (double type Delphi) avec une précision à 15 chiffres. Pour résoudre ce problème, l'application doit utiliser TEXT ou un type de données similaire et une règle de mappage transtypant dtAnsiString en dtFmtBCD ou un type de données similaire. Notez que ce problème ne provient pas de FireDAC et qu'il n'existe pas d'autre solution pour le moment.

Commandes SQL de SQLite

Dialecte SQL

Bien que SQLite soit parfaitement conforme à la norme SQL 92 ANSI, certaines fonctionnalités et commandes ne sont pas supportées et d'autres, performantes, sont ajoutées. Vous trouverez plus d'informations sur le dialecte SQL de SQLite sur les pages suivantes :

Groupes de commandes SQL SQLite

Le pilote FireDAC SQLite prend en charge les groupes de commandes SQL. Les commandes SQL doivent être séparées par ';'. SQLite vous permet de combiner de nombreuses commandes dans un groupe, y compris DDL et DML. Par exemple : 

with FDQuery1.SQL do begin

  SQL.Clear;
  SQL.Add('create table dbms (id integer, name varchar(20));');
  SQL.Add('insert into tab values (1, ''sqlite'');');
  SQL.Add('insert into tab values (2, ''mysql'');');
  SQL.Add('insert into tab values (3, ''firebird'');');
  SQL.Add('create table langs (id integer, name varchar(20));');
  SQL.Add('insert into tab values (1, ''delphi'');');
  SQL.Add('insert into tab values (2, ''c'');');
  SQL.Add('insert into tab values (3, ''c++'');');
  SQL.Add('select * from dbms;');
  SQL.Add('select * from langs;');

end;
FDQuery1.Open;
// process here the DBMS list
FDQuery1.NextRecordSet;
// process here the programming languages list

Dialecte des scripts SQL

Le TFDScript de FireDAC ne prend pas en charge la syntaxe SQLite, où les commandes de contrôle des scripts commencent à partir de '.'.

Array DML

A partir de la version v 3.7.11, SQLite prend en charge la commande INSERT avec plusieurs VALEURS. FireDAC utilise cette fonctionnalité pour implémenter Array DML, lorsque Params.BindMode = pbByNumber. Sinon, FireDAC émule Array DML. Par exemple : 

// here ADQuery1.Params collection is filled by 3 parameters
FDQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
// set "by number" parameter binding mode
FDQuery1.Params.BindMode := pbByNumber;
FDQuery1.Params.ArraySize := 100;
for i := 0 to FDQuery1.Params.ArraySize - 1 do begin
  FDQuery1.Params[0].AsIntegers[i] := i;
  FDQuery1.Params[1].AsStrings[i] := 'qwe';
  FDQuery1.Params[2].Clear(i);
end;
FDQuery1.Execute(FDQuery1.Params.ArraySize);

Transactions, verrouillage, threads et curseurs SQLite

Verrouillage et mises à jour simultanées

Consultez les articles SQLite d'origine suivants :

SQLite, en tant que SGBD fichier-serveur, verrouille les tables de la base de données lors des mises à jour. Les paramètres suivants ont des répercussions sur l'accès simultané :

  • lorsque plusieurs threads mettent à jour la même base de données, définissez le paramètre de connexion SharedCache sur False. Cela permet d'éviter les verrous mortels potentiels.
  • lorsque plusieurs processus ou threads mettent à jour les mêmes tables de base de données, définissez LockingMode sur Normal pour activer l'accès simultané aux tables. Définissez également le paramètre de connexion Synchronous sur Full ou sur Normal. De cette manière, SQLite met à jour un fichier de base de données directement à la fin de la transaction et les autres connexions voient les mises à jour de façon prévisible.
  • pour éviter les conflits de verrouillage entre les connexions, définissez UpdateOptions.LockWait sur True et BusyTimeout sur une valeur supérieure.
  • pour éviter les conflits de verrouillage entre les transactions de mises à jour dont l'exécution est longue, définissez TADConnection.TxOptions.Isolation sur xiSnapshot ou sur xiSerializible.

Transactions et modes d'isolement

SQLite prend en charge les transactions normales et les transactions imbriquées (points de vérification). Il ne prend pas en charge les transactions multiples. Voici une liste des modes d'isolement pris en charge par SQLite :

Mode Correspond à
xiDirtyRead PRAGMA read_uncommitted = 1
xiReadCommitted BEGIN TRANSACTION DEFERRED
xiRepeatableRead Le même que xiReadCommitted.
xiSnapshot BEGIN TRANSACTION DEFERRED
xiSerializible BEGIN TRANSACTION EXCLUSIVE

Transactions et commandes DML

L'insertion de commandes dans une transaction peut considérablement améliorer les performances de SQLite. Cela est particulièrement vrai lorsque les modifications de données sont conséquentes. Il en va de même avec la fonctionnalité Array DML de FireDAC. Vous devez donc insérer le code de modification des données dans une transaction pour profiter de meilleures performances : 

FDConnection1.StartTransaction;
try
  FDQuery1.SQL.Text := 'insert into tab values (:id, :name)';
  FDQuery1.Params.ArraySize := 10;
  for i := 0 to FDQuery1.Params.ArraySize - 1 do begin
    FDQuery1.Params[0].AsIntegers[i] := i;
    FDQuery1.Params[0].AsStrings[i] := 'name' + IntTostr(i);
  end;
  FDQuery1.Execute(FDQuery1.Params.ArraySize, 0);
  FDConnection1.Commit;
except
  FDConnection1.Rollback;
  raise;
end;

Transactions et curseurs

SQLite n'autorise pas l'annulation d'une transaction, lorsque les commandes comportent des ensembles de résultats qui n'ont pas encore été extraits. Pour éviter cela, FireDAC extrait tous les enregistrements restants d'un ensemble de résultats lors de l'appel de la méthode Rollback. Voir la propriété FetchOptions.AutoFetchAll.

Extension du moteur SQLite

Fonctions personnalisées

SQLite ne supporte pas le concept de procédure stockée ou de fonction, car il autorise l'utilisation de l'environnement du langage de l'hôte pour étendre les fonctionnalités du moteur. SQLite autorise le recensement des fonctions de langage de l'hôte dans le moteur SQLite et leur utilisation dans les commandes SQL. FireDAC simplifie ce processus en introduisant le composant TFDSQLiteFunction.

Pour construire une fonction, le développeur doit définir FunctionName, ArgumentsCount et créer le gestionnaire d'événement OnCalculate. La définition de Active sur True recense la fonction personnalisée du moteur SQLite. Par exemple : 

procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunction;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
  AOutput.AsInteger := AInputs[0].AsInteger * AInputs[1].AsInteger;
end;

FDSQLiteFunction1.DriverLink := FDPhysSQLiteDriverLink1;
FDSQLiteFunction1.FunctionName := 'XmY';
FDSQLiteFunction1.ArgumentsCount := 2;
FDSQLiteFunction1.OnCalculate := FDSQLiteFunction1Calculate;
FDSQLiteFunction1.Active := True;

Utilisation de cette fonction : 

FDQuery1.Open('select RegionID, XmY(RegionID, 10) from "Region"');

Une fonction peut appeler les méthodes FireDAC pour interroger une base de données. Pour créer une fonction personnalisée avec les valeurs par défaut ou un nombre différent d'arguments, vous devez spécifier le même FunctionName et un nombre différent d'arguments. Cela permet de recenser une fonction surchargée dans le moteur SQLite.

Vous trouverez l'exemple ci-dessus ainsi que d'autres exemples de fonctions dans le dossier FireDAC\Samples\DBMS Specific\SQLite\UserFunc.

FireDAC implémente et installe sur une connexion SQLite environ 50 fonctions, disponibles en standard pour la plupart des SGBD et implémentées par le moteur d'expressions local de FireDAC. Notez que lorsque vous créez une connexion SQLite au moment de l'exécution, vous devez inclure l'unité FireDAC.Stan.ExprFuncs dans la clause "uses", sinon une exception est déclenchée : 

[FireDAC][Phys][SQLite] ERROR: no such function: UCASE.

Pour rendre les fonctions personnalisées accessibles à la conception, créez un package de conception personnalisé avec un module de données, déposez les composants sur ce module, et configurez de manière adéquate. Créez le module dans la section d'initialisation de l'unité module et détruisez-le dans la section de finalisation. Ensuite, installez votre package dans l'EDI de Delphi.

Voir aussi la vidéo de Ron Grove.

Classements personnalisés

SQLite stocke et gère toutes les données caractères au format UTF8 ou UTF16, selon le paramètre de connexion OpenMode. Lorsque SQLite doit comparer ou trier des données caractères, il doit connaître les règles à utiliser pour ces opérations. Ces règles sont connues sous le terme "classement".

SQLite propose plusieurs classements intégrés. Aucun d'entre eux ne produit un tri correct des phrases allemandes, cyrilliques, arabes, et ainsi de suite. Vous devez utiliser le composant TFDSQLiteCollation pour créer votre propre classement. Définissez CollationName, Flags, LocaleName, puis définissez Active sur True pour recenser le classement avec le moteur SQLite. Par exemple : 

FDSQLiteCollation1.DriverLink := FDPhysSQLiteDriverLink1;
FDSQLiteCollation1.CollationName := 'UTF16NoCase';
FDSQLiteCollation1.Flags := [sfIgnoreCase];
FDSQLiteCollation1.Active := True;

La configuration du composant ci-dessus avec la valeur par défaut CollationKind=scCompareString implémente un classement Unicode insensible à la casse standard. L'application peut implémenter des classements personnalisés en utilisant CollationKind=scCustomUTF16 or scCustomUTF8 et en implémentant le gestionnaire d'événement OnCompare. Voici comment utiliser ce classement : 

SELECT * FROM "Employees" ORDER BY LastName COLLATE UTF16NoCase

Pour spécifier le classement par défaut d'une colonne, vous pouvez procéder comme suit : 

CREATE TABLE IF NOT EXISTS test_col (f1 VARCHAR(10) COLLATE UTF16NoCase)
Remarque: S'il n'y a aucune possibilité de spécifier le classement par défaut pour une connexion, une base de données ou une table, vous trouverez les exemples de classement ci-dessus dans le dossier FireDAC\Samples\DBMS Specific\SQLite\UserCollation.

Si vous n'utilisez pas de classements personnalisés, SQLite utilise par défaut un ordre de tri binaire. Pour le mode Fenêtre Données dynamiques de TFDTable, il est important d'utiliser les mêmes ordres de tri côté client et côté base de données. Pour activer l'ordre de tri binaire côté client, définissez FormatOptions.SortLocale sur 0.

Evénements de la base de données

FireDAC prend en charge la notification de certains événements à une application Delphi à partir d'un déclencheur de base de données SQLite (par exemple, la modification de données). Pour cela, FireDAC utilise une approche similaire à Firebird et recense la fonction personnalisée POST_EVENT. Pour l'appeler à partir d'un déclencheur, utilisez le code suivant : 

CREATE TRIGGER update_orders UPDATE ON "Orders"
BEGIN
  SELECT POST_EVENT('Orders');
END;

Pour recevoir une notification d'événement, l'application Delphi utilise le composant TFDEventAlerter. Par exemple : 

FDEventAlerter1.Names.Text := 'Orders';
FDEventAlerter1.Options.Synchronize := True;
FDEventAlerter1.OnAlter := DoAlert;
FDEventAlerter1.Active := True;

procedure TForm1.DoAlert(ASender: TFDCustomEventAlerter;
  const AEventName: String; const AArgument: Variant);
begin
  if CompareText(AEventName, 'Orders') = 0 then
    qryOrders.Refresh;
end;

Sources de données personnalisées

Le moteur SQL local vous permet d'utiliser les descendants TDataSet dans vos requêtes SQL. FireDAC utilise l'API des tables virtuelles SQLite pour implémenter SQL local.

Techniques SQLite avancées

Raccordement des mises à jour d'une base de données

SQLite fournit une API unique vous permettant de surveiller toutes les mises à jour d'une base de données. Cette fonctionnalité peut par exemple être utilisée pour consigner toutes les mises à jour d'une base de données. Pour utiliser cette API, une application Delphi doit définir le gestionnaire d'événement OnUpdate de l'objet TSQLiteDatabase, qui est un objet d'encapsulation de connexion de base de données. Raccordez cet événement après l'ouverture d'une connexion de base de données. Par exemple : 

procedure TForm1.DoUpdate(ADB: TSQLiteDatabase; AOper: Integer; const ADatabase, ATable: String; ARowid: sqlite3_int64);
begin
  Memo1.Lines.Add(Format('%d - %s - %s - %u', [AOper, ADatabase, ATable, ARowid]));
end;

FDConnection1.Connected := True;
TSQLiteDatabase(FDConnection1.ConnectionIntf.CliObj).OnUpdate := DoUpdate;

Vous trouverez cet exemple dans le dossier FireDAC\Samples\DBMS Specific\SQLite\OnUpdate.

Contrôle des droits d'accès à une base de données

SQLite est un SGBD incorporé. Il s'agit donc d'un SGBD d'utilisateur unique qui n'a pas besoin des concepts utilisateur, droits d'accès, ou autres. Une application peut néanmoins bénéficier d'un contrôle des droits d'accès, par exemple :

  • Une application peut restreindre les droits en fonction de la licence de l'utilisateur final. Une licence démo limite les possibilités, tandis qu'une licence complète donne accès à toutes les possibilités.
  • Les frameworks d'accès aux données multiniveaux peuvent utiliser leur propre concept utilisateur et contrôler l'accès aux données en utilisant une approche générique.

Là encore, SQLite fournit une fonctionnalité unique vous permettant d'autoriser ou non des commandes SQL. Pour utiliser cette API, une application Delphi doit définir le gestionnaire d'événement OnAutorize de l'objet TSQLiteDatabase, qui est un objet d'encapsulation de connexion de base de données. Raccordez cet événement après l'ouverture d'une connexion de base de données. Par exemple : 

procedure TForm1.DoAuthorize(ADB: TSQLiteDatabase; ACode: Integer; const AArg1, AArg2, AArg3, AArg4: String; var AResult: Integer);
begin
  Memo1.Lines.Add(Format('%d - %s - %s - %s - %s', [ACode, AArg1, AArg2, AArg3, AArg4]));

  // Deny any delete operation
  if ACode = SQLITE_DELETE then
    AResult := SQLITE_DENY
  else
    AResult := SQLITE_OK;
end;

FDConnection1.Connected := True;
TSQLiteDatabase(FDConnection1.ConnectionIntf.CliObj).OnAutorize := DoAuthorize;

Vous trouverez cet exemple dans le dossier FireDAC\Samples\DBMS Specific\SQLite\OnAuthorize.

Utilisation de l'API de bas niveau SQLite

Si vous souhaitez profiter de performances maximales d'accès aux données SQLite, vous devez envisager d'utiliser les classes d'encapsulation de l'API SQLite de FireDAC. Cette API orientée objet de bas niveau est utilisée par le pilote FireDAC SQLite. Elle ne fait l'objet d'aucune documentation et n'est pas officiellement prise en charge.

L'exemple suivant montre comment contrôler des transactions et extraire des enregistrements en utilisant une commande SELECT paramétrée : 

uses
  FireDAC.Phys.SQLiteWrapper;

procedure TForm1.FormCreate(Sender: TObject);
var
  oDB: TSQLiteDatabase;
  oTran: TSQLiteStatement;
  oStmt: TSQLiteStatement;
  i: Integer;
begin
  FDConnection1.Connected := True;
  oDB := TSQLiteDatabase(FDConnection1.CliObj);

  oTran := TSQLiteStatement.Create(oDB);
  try
    // start transaction
    oTran.Prepare('BEGIN');
    oTran.Execute;

    oStmt := TSQLiteStatement.Create(oDB);
    try
      // prepare statement
      oStmt.Prepare('select * from "Orders" where OrderID > :ID1 and OrderID < :ID2');

      // add bind variables (parameters)
      for i := 1 to oStmt.ParamDefsCount do
        TSQLiteBind.Create(oStmt.Params);

      // add column variables (fields)
      for i := 1 to oStmt.ColumnDefsCount do
        TSQLiteColumn.Create(oStmt.Columns).Index := i - 1;

      // set parameter values and execute
      oStmt.Params[0].AsInteger := 11000;
      oStmt.Params[1].AsInteger := 12000;
      oStmt.Execute;

      // fetch records and read columns
      while oStmt.Fetch do
        Memo1.Lines.Add(Format('OrderID: %d, CustomerID: %s',
          [oStmt.Columns[0].AsInteger, oStmt.Columns[1].AsString]));
    finally
      oStmt.Free;
    end;

    // commit transaction
    oTran.Unprepare;
    oTran.Prepare('COMMIT');
    oTran.Execute;
  finally
    oTran.Free;
  end;
end;

Voir aussi

Exemples

Récupérée de « http://docwiki.embarcadero.com/RADStudio/Sydney/f/index.php?title=Utilisation_de_SQLite_avec_FireDAC&oldid=215277 »