SQLite Vues, ​​index et déclencheur avec exemple

Dans l'utilisation quotidienne de SQLite, vous aurez besoin de quelques outils d'administration sur votre base de données. Vous pouvez également les utiliser pour rendre l'interrogation de la base de données plus efficace en créant des index, ou plus réutilisable en créant des vues.

SQLite Consultation

Les vues sont très similaires aux tableaux. Mais les vues sont des tableaux logiques ; ils ne sont pas stockés physiquement comme les tables. Une vue est composée d'une instruction select.

Vous pouvez définir une vue pour vos requêtes complexes et réutiliser ces requêtes à tout moment en appelant la vue directement au lieu de réécrire les requêtes.

Instruction CREATE VIEW

Pour créer une vue sur une base de données, vous pouvez utiliser l'instruction CREATE VIEW suivie du nom de la vue, puis placer la requête souhaitée après cela.

Exemple : Dans l'exemple suivant, nous allons créer une vue nommée « AllStudentsView » dans la base de données exemple « TutorialsSampleDB.db », comme suit :

Étape 1) Ouvrez « Ordinateur » et accédez au répertoire suivant « C:\sqlite », puis ouvrez « sqlite3.exe » :

Étape 2) Ouvrez la base de données « TutorialsSampleDB.db » à l’aide de la commande suivante :

Étape 3) Voici une syntaxe de base de la commande sqlite3 pour créer la vue

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Il ne devrait y avoir aucun résultat de la commande comme celui-ci :

Étape 4) Pour vous assurer que la vue est créée, vous pouvez sélectionner la liste des vues dans la base de données en exécutant la commande suivante :

SELECT name FROM sqlite_master WHERE type = 'view';

Vous devriez voir la vue « AllStudentsView » renvoyée :

Étape 5) Maintenant que notre vue est créée, vous pouvez l'utiliser comme une table normale, comme ceci :

SELECT * FROM AllStudentsView;

Cette commande interrogera la vue « AllStudents » et en sélectionnera toutes les lignes, comme indiqué dans la capture d'écran suivante :

Vues temporaires

Les vues temporaires sont temporaires pour la connexion à la base de données actuelle utilisée pour la créer. Ensuite, si vous fermez la connexion à la base de données, toutes les vues temporaires seront automatiquement supprimées. Les vues temporaires sont créées à l'aide de l'une des commandes suivantes :

• CRÉER UNE VUE TEMPÉRATURE, ou
• CRÉER UNE VUE TEMPORAIRE.

Les vues temporaires sont utiles si vous souhaitez effectuer certaines opérations pour le moment et que vous n'avez pas besoin qu'il s'agisse d'une vue permanente. Ainsi, vous créez simplement une vue temporaire, puis effectuez votre traitement en utilisant cette vue. Later lorsque vous fermez la connexion avec la base de données, elle sera automatiquement supprimée.

Exemple :

Dans l'exemple suivant, nous allons ouvrir une connexion à une base de données, puis créer une vue temporaire.

Après cela, nous fermerons cette connexion et vérifierons si la vue temporaire existe toujours ou non.

Étape 1) Ouvrez sqlite3.exe depuis le répertoire « C:\sqlite » comme expliqué précédemment.

Étape 2) Ouvrez une connexion à la base de données « TutorialsSampleDB.db » en exécutant la commande suivante :

.open TutorialsSampleDB.db

Étape 3) Saisissez la commande suivante qui créera une vue temporaire « AllStudentsTempView » :

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Étape 4) Assurez-vous que la vue temporaire « AllStudentsTempView » est créée en exécutant la commande suivante :

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Étape 5) Fermez sqlite3.exe et ouvrez-le à nouveau.

Étape 6) Ouvrez une connexion à la base de données « TutorialsSampleDB.db » à l’aide de la commande suivante :

.open TutorialsSampleDB.db

Étape 7) Exécutez la commande suivante pour obtenir la liste des vues temporaires créées sur la base de données :

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Vous ne devriez voir aucune sortie car la vue temporaire que nous avons créée est supprimée lorsque nous avons fermé la connexion à la base de données à l'étape précédente. Sinon, tant que vous maintenez la connexion avec la base de données ouverte, vous pourrez voir la vue temporaire avec les données.

Remarques :

• Vous ne pouvez pas utiliser les instructions INSERT, DELETE ou UPDATE avec des vues, vous seul pouvez utiliser la commande « sélectionner parmi les vues » comme indiqué à l'étape 5 de l'exemple CREATE View.
• Pour supprimer une VUE, vous pouvez utiliser l'instruction « DROP VIEW » :

DROP VIEW AllStudentsView;

Pour vous assurer que la vue est supprimée, vous pouvez exécuter la commande suivante qui vous donne la liste des vues de la base de données :

SELECT name FROM sqlite_master WHERE type = 'view';

Aucune vue ne sera renvoyée car la vue a été supprimée, comme suit :

Outre la réutilisation de requêtes complexes avec des vues, SQLite récupère également plus rapidement les lignes correspondantes grâce aux index.

SQLite Sommaire

Si vous avez un livre et que vous souhaitez rechercher un mot-clé sur ce livre. Vous rechercherez ce mot-clé dans l’index du livre. Ensuite, vous accéderez au numéro de page de ce mot-clé pour lire plus d’informations sur ce mot-clé.

Cependant, s'il n'y a pas d'index sur ce livre ni de numéros de page, vous parcourrez tout le livre du début à la fin jusqu'à ce que vous trouviez le mot-clé que vous recherchez. Et cela est très difficile surtout lorsque vous disposez d’un index et d’un processus très lent pour rechercher un mot-clé.

Index dans SQLite (et le même concept valable pour d'autres Systèmes de gestion de bases de données également) fonctionne de la même manière que les index que l’on trouve au dos des livres.

Lorsque vous recherchez certaines lignes dans un SQLite tableau avec critères de recherche, SQLite recherchera sur toutes les lignes du tableau jusqu'à ce qu'il trouve les lignes que vous recherchez qui correspondent aux critères de recherche. Et ce processus devient très lent lorsque vous avez des tables plus grandes.

Les index accéléreront les requêtes de recherche de données et aideront à effectuer la récupération de données à partir des tables. Les index sont définis sur les colonnes du tableau.

Améliorer les performances avec les index :

Les index peuvent améliorer les performances de la recherche de données sur une table. Lorsque vous créez un index sur une colonne, SQLite créera une structure de données pour cet index où chaque valeur de champ a un pointeur vers la ligne entière à laquelle appartient la valeur.

Ensuite, si vous exécutez une requête avec une condition de recherche sur une colonne faisant partie d'un index, SQLite recherchera d'abord la valeur sur l'index. SQLite je ne scannerai pas toute la table pour cela. Ensuite, il lira l'emplacement où pointe la valeur pour la ligne du tableau. SQLite localisera la ligne à cet emplacement et la récupérera.

Cependant, si la colonne que vous recherchez ne fait pas partie d'un index, SQLite effectuera une analyse des valeurs de colonne pour trouver les données que vous recherchez. Ce sera généralement un processus plus lent s’il n’y a pas d’index.

Imaginez un livre sans index et vous devez rechercher un mot spécifique. Vous scannerez tout le livre de la première page à la dernière page à la recherche de ce mot. Cependant, si vous avez un index sur ce livre, vous chercherez d’abord le mot dessus. Obtenez le numéro de page où il se trouve, puis accédez-y. Ce qui sera beaucoup plus rapide que de numériser le livre entier d’un bout à l’autre.

SQLite CRÉER UN INDEX

Pour créer un index sur une colonne, vous devez utiliser la commande CREATE INDEX. Et vous devez le définir comme suit :

• Vous devez spécifier le nom de l'index après la commande CREATE INDEX.
• Après le nom de l'index, vous devez mettre le mot-clé « ON », suivi du nom de la table dans laquelle l'index sera créé.
• Ensuite, la liste des noms de colonnes utilisés pour l'index.
• Vous pouvez utiliser l'un des mots-clés suivants « ASC » ou « DESC » après n'importe quel nom de colonne pour spécifier un ordre de tri utilisé pour classer les données d'index.

Exemple :

Dans l'exemple suivant, nous allons créer un index « StudentNameIndex » sur la table « students » de la base de données « Students », comme suit :

Étape 1) Accédez au dossier « C:\sqlite » comme expliqué précédemment.

Étape 2) Ouvrez sqlite3.exe.

Étape 3) Ouvrez la base de données « TutorialsSampleDB.db » à l’aide de la commande suivante :

.open TutorialsSampleDB.db

Étape 4) Créez un nouvel index « StudentNameIndex » à l'aide de la commande suivante :

CREATE INDEX StudentNameIndex ON Students(StudentName);

Vous ne devriez voir aucun résultat pour ceci :

Étape 5) Pour vous assurer que l'index a été créé vous pouvez exécuter la requête suivante, qui vous donnera la liste des index créés dans la table Etudiants :

PRAGMA index_list(Students);

Vous devriez voir l'index que nous venons de créer renvoyé :

Remarques :

• Les index peuvent être créés non seulement sur la base de colonnes mais également d'expressions. Quelque chose comme ça:

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

Le « OrderTotalIndex » sera basé sur la colonne OrderId ainsi que sur la multiplication de la valeur de la colonne Quantité et de la valeur de la colonne Prix. Ainsi, toute requête pour « OrderId » et « Quantity*Price » sera efficace car la requête utilisera l’index.

• Si vous avez spécifié une clause WHERE dans l'instruction CREATE INDEX, l'index sera un index partiel. Dans ce cas, il y aura des entrées dans l'index uniquement pour les lignes qui correspondent aux conditions de la clause WHERE. Par exemple, dans l'index suivant :

  CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
  WHERE Quantity > 10000;

  (Dans l'exemple ci-dessus, l'index sera un index partiel car une clause WHERE est spécifiée. Dans ce cas, l'index sera appliqué uniquement aux commandes dont la valeur de quantité est supérieure à 10000 . Notez que cet index est appelé un index partiel. index à cause de la clause WHERE, et non de l'expression utilisée dessus. Cependant, vous pouvez utiliser les expressions avec des index normaux.)

• Vous pouvez utiliser l'instruction CREATE UNIQUE INDEX au lieu de CREATE INDEX pour éviter les entrées en double pour les colonnes et ainsi toutes les valeurs de la colonne indexée seront uniques.
• Pour supprimer un index, utilisez la commande DROP INDEX suivie du nom de l'index à supprimer.

Alors que les index accélèrent les lectures, les déclencheurs permettent SQLite Répondre automatiquement à chaque modification des données.

SQLite Gâchette

Introduction à la SQLite Gâchette

Les déclencheurs sont des opérations prédéfinies automatiques exécutées lorsqu'une action spécifique se produit sur une table de base de données. Un déclencheur peut être défini pour être déclenché chaque fois que l'une des actions suivantes se produit sur une table :

• INSÉRER dans un tableau.
• SUPPRIMER les lignes d'un tableau.
• MISE À JOUR d'une des colonnes du tableau.

SQLite prend en charge le déclencheur FOR EACH ROW afin que les opérations prédéfinies dans le déclencheur soient exécutées pour toutes les lignes impliquées dans les actions survenues sur la table (qu'il s'agisse d'insertion, de suppression ou de mise à jour).

SQLite CRÉER UN DÉCLENCHEUR

Pour créer un nouveau TRIGGER, vous pouvez utiliser l'instruction CREATE TRIGGER comme suit :

• Après CREATE TRIGGER, vous devez spécifier un nom de déclencheur.
• Après le nom du déclencheur, vous devez spécifier quand exactement le nom du déclencheur doit être exécuté. Vous avez trois options :
  • AVANT – le déclencheur sera exécuté avant l’instruction INSERT, UPDATE ou delete spécifiée.
  • Après – le déclencheur sera exécuté après l'instruction INSERT, UPDATE ou delete spécifiée.
  • INSTEAD OF – Il remplacera l'action qui a déclenché le déclencheur par l'instruction spécifiée dans TRIGGER. Le déclencheur INSTEAD OF n’est pas applicable aux tables, uniquement aux vues.
• Ensuite, vous devez spécifier le type d'action, le déclencheur se déclenchera quand cela se produira. Soit DELETE, INSERT ou UPDATE.
• Vous pouvez choisir un nom de colonne facultatif afin que le déclencheur ne se déclenche que si l'action s'est produite sur cette colonne.
• Ensuite, vous devez spécifier le nom de la table dans laquelle le déclencheur sera créé.
• Dans le corps du déclencheur, vous devez spécifier l'instruction qui doit être exécutée pour chaque ligne lorsque le déclencheur est déclenché.

Les déclencheurs seront activés (déclenchés) uniquement en fonction du type d'instruction spécifié dans la commande create trigger. Par exemple:

• Le déclencheur BEFORE INSERT sera activé (déclenché) avant toute instruction d'insertion.
• Le déclencheur AFTER UPDATE sera activé (déclenché) après toute instruction de mise à jour,… et ainsi de suite.

À l'intérieur du déclencheur, vous pouvez faire référence aux valeurs nouvellement insérées à l'aide du mot-clé « new ». Vous pouvez également faire référence aux valeurs supprimées ou mises à jour à l’aide de l’ancien mot-clé. Comme suit:

• À l’intérieur des déclencheurs INSERT – un nouveau mot-clé peut être utilisé.
• À l’intérieur des déclencheurs UPDATE – des mots-clés nouveaux et anciens peuvent être utilisés.
• À l’intérieur des déclencheurs DELETE – l’ancien mot-clé peut être utilisé.

Exemple

Nous allons maintenant créer un déclencheur qui s'activera avant d'insérer un nouvel étudiant dans la table « Étudiants ».

Le système enregistrera l'étudiant nouvellement inséré dans la table « StudentsLog » avec un horodatage automatique correspondant à la date et à l'heure actuelles de l'exécution de l'instruction d'insertion. Voici un exemple :

Étape 1) Accédez au répertoire « C:\sqlite » et exécutez sqlite3.exe.

Étape 2) Ouvrez la base de données « TutorialsSampleDB.db » en exécutant la commande suivante :

.open TutorialsSampleDB.db

Étape 3) Créez le déclencheur « InsertIntoStudentTrigger » en exécutant la commande suivante :

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

La fonction « datetime() » vous renverra l'horodatage de la date et de l'heure actuelles lors de l'exécution de l'instruction INSERT. Ainsi, nous pourrons consigner la transaction d'insertion en ajoutant automatiquement un horodatage à chaque transaction.

La commande devrait s'exécuter correctement et vous n'obtenez aucun résultat :

Le déclencheur « InsertIntoStudentTrigger » s’active à chaque insertion d’un nouvel étudiant dans la table « students ». Le mot-clé « new » fait référence aux valeurs à insérer. Par exemple, « new.StudentId » correspond à l’identifiant de l’étudiant inséré.

Nous allons maintenant tester le comportement du déclencheur lorsque nous insérons un nouvel étudiant.

Étape 4) Écrivez la commande suivante qui insérera un nouvel étudiant dans la table des étudiants :

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

Étape 5) Saisissez la commande suivante qui sélectionnera toutes les lignes de la table « StudentsLog » :

SELECT * FROM StudentsLog;

Vous devriez voir une nouvelle ligne renvoyée pour le nouvel étudiant que nous venons d'insérer :

Cette ligne a été insérée par le déclencheur avant d'insérer le nouvel étudiant avec l'identifiant 11.

Dans cet exemple, nous avons utilisé le déclencheur « InsertIntoStudentTrigger » que nous avons créé pour consigner automatiquement toutes les transactions d'insertion dans la table « StudentsLog ». De la même manière, vous pouvez consigner toutes les opérations de mise à jour ou de suppression.

Empêcher les mises à jour involontaires avec des déclencheurs :

En utilisant les déclencheurs BEFORE UPDATE sur une table, vous pouvez empêcher les instructions de mise à jour sur une colonne en fonction d'une expression.

Exemple

Dans l'exemple suivant, nous empêcherons toute instruction update de mettre à jour la colonne « studentname » dans la table Students :

Étape 1) Accédez au répertoire « C:\sqlite » et exécutez sqlite3.exe.

Étape 2) Ouvrez la base de données « TutorialsSampleDB.db » en exécutant la commande suivante :

.open TutorialsSampleDB.db

Étape 3) Créez un nouveau déclencheur « preventUpdateStudentName » sur la table « Students » en exécutant la commande suivante

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

La commande « RAISE » générera une erreur avec le message « Vous ne pouvez pas mettre à jour studentname », puis empêchera l'exécution de l'instruction de mise à jour.

Nous allons maintenant vérifier que le déclencheur fonctionne bien et qu'il empêche toute mise à jour de la colonne studentname.

Étape 4) Exécutez la commande de mise à jour suivante, qui remplacera le nom de l'étudiant « Jack » par « Jack1 ».

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

Vous devriez recevoir le message d'erreur spécifié dans le déclencheur, indiquant que « Vous ne pouvez pas mettre à jour studentname », comme suit :

Étape 5) Exécutez la commande suivante, qui sélectionnera la liste des noms des étudiants dans la table des étudiants.

SELECT StudentName FROM Students;

Vous devriez voir que le nom de l'élève « Jack » est toujours le même et ne change pas :

FAQ