Le langage SQL Partie 2 PDF

Fichiers et bases de données Le langage SQL Partie 2 (UE 219) HELHA – Campus Mons – IRAM Cours de Mr. Colmant An...

Fichiers et bases de données Le langage SQL Partie 2 (UE 219) HELHA – Campus Mons – IRAM Cours de Mr. Colmant Année académique 2024-2025 Bases de données HELHa 2BI A. Colmant 2014 1 Table des matières 1La gestion des utilisateurs..................................................................................................................3 Les tables système...........................................................................................................................3 La gestion des utilisateurs et des privilèges.....................................................................................3 Gestion des privilèges avec les triggers et procédures stockées......................................................7 Utilisation des rôles.........................................................................................................................7 2Les transactions..................................................................................................................................8 3Les verrous.......................................................................................................................................10 4Les requêtes préparées......................................................................................................................11 Les variables utilisateur.................................................................................................................11 Les requêtes préparées avec MySQL.............................................................................................12 L'utilisation de PDO avec PHP......................................................................................................12 5Les procédures stockées...................................................................................................................13 La construction des procédures stockées.......................................................................................13 Les variables locales......................................................................................................................14 Les conditions................................................................................................................................14 Les boucles....................................................................................................................................15 La gestion des erreurs....................................................................................................................15 6Les triggers.......................................................................................................................................17 7Les vues............................................................................................................................................19 Les vues.........................................................................................................................................19 Les tables temporaires...................................................................................................................21 Les vues matérialisées...................................................................................................................22 La création d'une table sur base de la structure et des données d'une autre table..........................23 Bases de données HELHa 2BI A. Colmant 2014 2 1 La gestion des utilisateurs Les tables système information_schema contient les informations sur les tables, les colonnes, le type des colonnes, les procédures. performance_schema contient des informations sur les actions effectuées sur le serveur. mysql contient des informations sur le serveur comme les utilisateurs et leurs privilèges. Les utilisateurs sont stockés dans la table user avec leurs privilèges globaux (sur le serveur). La table db contient les privilèges par base de données, tables_priv au niveau des tables, columns_priv au niveau des colonnes et proc_priv au niveau des procédures stockées. La gestion des utilisateurs et des privilèges Chaque utilisateur possède des privilèges sur des bases de données, des tables, des vues,… Les données peuvent être directement insérées (modifiées et supprimées) dans les tables présentes dans mysql. Cette démarche est néanmoins à éviter car elle nécessite de bien maîtriser la structure des tables correspondantes. Pour créer un utilisateur, on va utiliser : CREATE USER ‘nom_user’@’nom_hote’ [IDENTIFIED BY ‘mot_de_passe’] ; La clause IDENTIFIED BY n’est pas obligatoire, l’utilisateur n’a pas de mot de passe si rien n’est précisé. Le mot de passe est haché avant d’être stocké dans mysql.user. Diverses options peuvent être configurées (durée avant expiration du mot de passe, nombre de tentatives avant blocage du compte,...). Pour modifier le mot de passe d’un utilisateur, il faut utiliser la fonction ALTER USER (qui permet aussi de modifier certaines options liées à un utilisateur, voir ci-dessous) et mettant : ALTER USER 'nom_user'@'nom_hote' IDENTIFIED BY 'mot_de_passe' ; Les privilèges suivants peuvent être attribués aux utilisateurs : pour la gestion des enregistrements : SELECT, INSERT, UPDATE, DELETE ; pour la gestion de la structure des tables : CREATE, CREATE TEMPORARY TABLES, CREATE VIEW (nécessite les privilèges SELECT sur les colonnes présentes dans la vue), ALTER, DROP ; pour les autres éléments de la base de données : CREATE ROUTINE (créer des procédures stockées), ALTER ROUTINE (modifier et supprimer des procédures stockées), EXECUTE (exécuter des procédures stockées), INDEX (créer et supprimer des index), TRIGGER (créer et supprimer des triggers), LOCK TABLES (verrouiller des tables, nécessite SELECT), CREATE USER (gérer les utilisateurs). Bases de données HELHa 2BI A. Colmant 2014 3 Table 14.3 Permissible Privileges for GRANT and REVOKE Privilege Meaning and Grantable Levels ALL Grant all privileges at specified access level except GRANT OPTION [PRIVILEGES] ALTER Enable use of ALTER TABLE. Levels: Global, database, table. ALTER ROUTINE Enable stored routines to be altered or dropped. Levels: Global, database, procedure. CREATE Enable database and table creation. Levels: Global, database, table. CREATE ROUTINE Enable stored routine creation. Levels: Global, database. CREATE Enable tablespaces and log file groups to be created, altered, or dropped. TABLESPACE Level: Global. CREATE TEMPORARY Enable use of CREATE TEMPORARY TABLE. Levels: Global, database. TABLES Enable use of CREATE USER, DROP USER, RENAME USER, and CREATE USER REVOKE ALL PRIVILEGES. Level: Global. CREATE VIEW Enable views to be created or altered. Levels: Global, database, table. DELETE Enable use of DELETE. Level: Global, database, table. DROP Enable databases, tables, and views to be dropped. Levels: Global, database, table. EVENT Enable use of events for the Event Scheduler. Levels: Global, database. EXECUTE Enable the user to execute stored routines. Levels: Global, database, table. FILE Enable the user to cause the server to read or write files. Level: Global. GRANT OPTION Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy. INDEX Enable indexes to be created or dropped. Levels: Global, database, table. INSERT Enable use of INSERT. Levels: Global, database, table, column. Enable use of LOCK TABLES on tables for which you have the SELECT LOCK TABLES privilege. Levels: Global, database. Enable the user to see all processes with SHOW PROCESSLIST. Level: PROCESS Global. PROXY Enable user proxying. Level: From user to user. REFERENCES Enable foreign key creation. Levels: Global, database, table, column. RELOAD Enable use of FLUSH operations. Level: Global. REPLICATION CLIENT Enable the user to ask where master or slave servers are. Level: Global. REPLICATION Enable replication slaves to read binary log events from the master. Level: SLAVE Global. Bases de données HELHa 2BI A. Colmant 2014 4  Privilege Meaning and Grantable Levels SELECT Enable use of SELECT. Levels: Global, database, table, column. SHOW DATABASES Enable SHOW DATABASES to show all databases. Level: Global. SHOW VIEW Enable use of SHOW CREATE VIEW. Levels: Global, database, table. SHUTDOWN Enable use of mysqladmin shutdown. Level: Global. Enable use of other administrative operations such as CHANGE MASTER SUPER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global. TRIGGER Enable trigger operations. Levels: Global, database, table. UPDATE Enable use of UPDATE. Levels: Global, database, table, column. USAGE Synonym for “no privileges” http://dev.mysql.com/doc/refman/5.7/en/grant.html En plus de préciser le privilège accordé, il faut préciser quelle est sa portée : *.* (privilège global sur toutes les bases de données), * (porte sur tous les objets dans la base de données, correspond à *.* si aucune base de données n’est sélectionnée), nom_bd.* (porte sur tous les objets dans la base de données), nom_bd.nom_table (porte sur la table précisée), nom_table (porte sur la table de la base de données dans laquelle on se trouve), nom_bd.nom_routine (porte sur la procédure dans la base de données précisée). Le CREATE USER (ainsi que le ALTER USER qui permet de modifier un utilisateur déjà existant) peut accepter différentes options parmi lesquelles (dans l'ordre dans lequel elles peuvent être ajoutées) : IDENTIFIED [WITH méthodes d'authentification (choix parmi : MYSQL_NATIVE_PASSWORD, SHA256_PASSWORD et CACHING_SHA2_PASSWORD)] BY motdepasse ; DEFAULT ROLE role1 [, role2] : il est possible d'assigner aux utilisateurs un rôle. L'utilisateur possède alors tous les droits liés au rôle. Pour créer un rôle, il faut faire appel à CREATE ROLE role1 ; + GRANT pour attribuer les droits directement au rôle plutôt qu'à un utilisateur. WITH options : les options se trouvent parmi MAX_QUERIES_PER_HOUR (limite le nombre de commandes de l’utilisateur), MAX_UPDATES_PER_HOUR (limite le nombre de modifications), MAX_CONNECTIONS_PER_HOUR (limite le nombre de connexions) et MAX_USER_CONNECTIONS. La valeur associée à l'option est indiquée juste à côté de celle-ci (le 0 signifie qu'il n'y a pas de limite). Pour mettre plusieurs options, il faut les mettre les unes après les autres avec leur valeur sans les séparer par une virgule (ex : WITH MAX_QUERIES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 2). On peut aussi obliger le changement régulier de mot de passe en précisant la validité d'un mot de passe : PASSWORD EXPIRE INTERVAL x DAY. On peut également préciser le nombre d'anciens mots de passe à retenir afin d'empêcher un utilisateur de réutiliser un ancien mot de passe. Par exemple, pour empêcher la réutilisation des 3 derniers mots de Bases de données HELHa 2BI A. Colmant 2014 5  passe : PASSWORD HISTORY 3. On peut également préciser le nombre de tentatives de connexion avant blocage du compte avec FAILED_LOGIN_ATTEMPTS suivi de la valeur souhaité. On peut également décider du nombre de jours de blocage du compte avec PASSWORD_LOCK_TIME suivi du nombre de jours. Pour terminer on peut verrouiller le compte (ou le déverrouiller) en mettant ACCOUNT LOCK ou UNLOCK. Pour ajouter des privilèges, il faut utiliser : GRANT privilege [(col1, col2,…)] [, privilege [(col1, col2,…)], …] ON [type_objet] niveau_privilege TO user [IDENTIFIED BY ‘mot_de_passe’] ; col1, col2,… permet de préciser que les privilèges ne s’appliquent qu’à certaines colonnes. Le type d’objet permet de préciser s’il s’agit d’une TABLE ou d’une PROCEDURE en cas de nom ambigu. Si l’utilisateur n’existe pas, il est créé. Par contre, s’il existe déjà et que la clause IDENTIFIED BY est ajoutée alors le mot de passe est modifié. Pour retirer un privilège à un utilisateur, on utilise REVOKE privilege [, privilege,…] ON niveau_privilege FROM user ; Pour accorder tous les privilèges à un utilisateur, on place ALL ou ALL PRIVILEGES : GRANT ALL ON niveau_privilege TO ‘nom_user’@’nom_hote’ ; Avec le ALL, un utilisateur possède tous les privilèges sauf le droit d’accorder des privilèges aux autres utilisateurs. Pour lui permettre d’accorder des privilèges, il faut préciser GRANT OPTION (ne peut pas être utilisé avec ALL) dans les privilèges. Il est possible de préciser à la fin du GRANT … WITH GRANT OPTION (fonctionne avec ALL). Un utilisateur ne peut accorder que des droits qu’il possède lui-même. Pour supprimer un utilisateur, il suffit de faire DROP USER ‘nom_user’@’nom_hote’ ; Le nom d’hôte ou une partie de celui-ci peut être remplacé par % pour permettre aux utilisateurs de se connecter à partir d’un hôte différent (ex. : ‘190.54.2.%’ ou ‘%’ pour n’importe quel serveur, qui correspond à ne mettre aucun hôte). Pour renommer un utilisateur, on utilise RENAME USER ‘ancien_nom’@’nom_hote’ TO ‘nouv_nom’@’nom_hote’ ; Pour voir les droits de l'utilisateur qui a ouvert la session, on utilise SHOW GRANTS ; Il est aussi possible de voir les droits d'un autre utilisateur en mettant SHOW GRANTS FOR user ; Si l'utilisateur en question est lié à un ou des rôles (à partir de mysql 8.0) alors, pour avoir le contenu des rôles, il faudra rajouter un USING : SHOW GRANTS FOR user USING role1 [,role2]. Pour afficher la liste des utilisateurs créés sur un serveur et visualiser les droits qui leur sont accordés, il faut accéder à la base de données MYSQL puis afficher le contenu de la table USER. Pour voir quel utilisateur à le droit d'accorder quel privilège, il faut utiliser la table USER_PRIVILEGES de la base de données INFORMATION_SCHEMA. Bases de données HELHa 2BI A. Colmant 2014 6 Gestion des privilèges avec les triggers et procédures stockées Dans le cas des triggers, procédures stockées et des vues, le privilège de l’utilisateur créant ceux-ci est vérifié (accès autorisé aux tables,…). L’utilisateur qui exécute un trigger, une procédure stockée ou une vue doit disposer des privilèges pour réaliser cette action (et donc pas des droits utilisés à l’intérieur du trigger, de la procédure ou de la vue). Lors de la création d’un trigger, d’une procédure stockée ou d’une vue, il est possible de préciser quel est l’utilisateur qui est responsable de la définition de celle-ci (et qui doit posséder les droits pour faire cette action). Choisir l’utilisateur nécessite le privilège global SUPER (accordé au moins à root). Pour faire ce choix, il faut mettre DEFINER = ‘nom_user’@’nom_hote’ juste après CREATE. Par défaut, c’est le CURRENT_USER( ) qui est sélectionné. Pour que la procédure stockée ou la vue vérifie les droits de l’utilisateur qui invoque celle-ci plutôt que celui qui la définit, on place SQL SECURITY {DEFINER | INVOKER} juste après DEFINER. Par défaut, c’est l’option DEFINER qui est sélectionnée. L’option INVOKER permet de préciser que c’est l’utilisateur qui invoque dont les droits sont vérifiés. Utilisation des rôles Depuis MySQL 8, il est possible d'utiliser des rôles qui vont être assignés à des utilisateurs. Pour créer un rôle, il faut utiliser la commande CREATE ROLE. Des droits sont ensuite attribués au rôle de la même manière que pour les utilisateurs avec la commande GRANT droit TO role ON base_de_donnees. Pour attribuer ensuite un rôle à un utilisateur, il faut utiliser la commande GRANT role1, role2 TO utilisateur1, utilisateur2. Il n'est pas possible d'assigner à la fois des rôles et des privilèges dans la même instruction. Pour que les privilèges liés à un rôle puissent être utilisés, il faut faire appel à la commande SET DEFAUT ROLE role1, role2,... TO utilisateur1, utilisateur2,... Cela permet de spécifier quels seront les rôles qui seront actifs lorsque l'utilisateur se connectera au serveur. Si tous les rôles doivent être actifs, il suffit d'indiquer ALL dans la liste des rôles. La commande SET ROLE permet de modifier la liste des rôles actifs dans la session en cours. En mettant NONE, aucun rôle n'est actif. En spécifiant un ou plusieurs rôles, seuls ces rôles sont actifs. En mettant ALL, tous les rôles sont actifs dans la session. Pour vérifier les droits d'un utilisateur, en mettant SHOW GRANTS, on verra les droits donnés à l'utilisateur + les rôles qui lui ont été attribués. On ne verra par contre pas les droits liés aux rôles octroyés à l'utilisateur. Pour voir les droits liés à un rôle que possède l'utilisateur, il faut compléter la commande SHOW GRANTS avec USING role1, role2. Bases de données HELHa 2BI A. Colmant 2014 7 Que faire ? Exemple Créer un nouvel utilisateur CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user'; Changer le mot de passe d'un ALTER USER 'user1'@'localhost' IDENTIFIED BY 'nouvMDP'; utilisateur Accorder des droits à un GRANT SELECT ON exemple_livres.* TO 'user1'@'localhost'; utilisateur Retirer des droits à un REVOKE SELECT ON exemple_livres.* FROM utilisateur 'user1'@'localhost'; Accorder tous les droits à un GRANT ALL ON exemple_livres.* TO 'user1'@'localhost'; utilisateur Créer une vue exécutée avec CREATE DEFINER = 'user1'@'localhost' SQL SECURITY les droits de l'utilisateur qui INVOKER VIEW view_nombre_livres AS SELECT COUNT(*) l'appelle AS nbliv FROM livre; Créer une vue exécutée avec CREATE DEFINER = 'user1'@'localhost' SQL SECURITY les droits de l'utilisateur qui l'a DEFINER VIEW view_nombre_livres_2 AS SELECT COUNT(*) créée AS nbliv FROM livre; Limiter l'utilisation d'une base ALTER USER 'user10'@'localhost' WITH de données par un utilisateur MAX_QUERIES_PER_HOUR 2; Supprimer un utilisateur DROP USER 'user2'@'localhost'; Renommer un utilisateur RENAME USER 'user10'@'localhost' TO 'user4'@'localhost'; Voir les privilèges de SHOW GRANTS; l'utilisateur ayant ouvert la session Créer un rôle CREATE ROLE 'role_dev' ; Attribuer des droits à un rôle GRANT update, delete on madb.* to 'role_dev'; Attribuer un rôle à un GRANT 'role_dev' TO 'user1'@'localhost'; utilisateur Activer des rôles par défaut SET DEFAULT ROLE ALL TO 'user_a1'@'localhost'; (ici activation de tous les rôles) 2 Les transactions Une transaction permet de regrouper plusieurs requêtes en un bloc. Lors de l’exécution d’une transaction, les requêtes sont exécutées les unes après les autres. Si une requête échoue, toute la transaction est annulée. De la même manière, si la transaction ne va pas à son terme (interruption des requêtes), elle est également annulée. Les requêtes ne sont validées que si toutes les requêtes sont réalisées. La transaction est alors terminée. Bases de données HELHa 2BI A. Colmant 2014 8 Seules les tables qui utilisent InnoDB (et non MyISAM) sont transactionnelles avec MySQL. Par défaut, une requête étant considérée comme une transaction, le comportement de MySQL est de valider automatiquement et directement chaque requête (ce qui empêche toute annulation). Pour éviter la validation automatique, il faut préciser SET autocommit = 0 ; Il faut alors préciser si on souhaite valider les requêtes (COMMIT ;) ou les annuler (ROLLBACK ;). Une nouvelle transaction est donc créée après chaque commit ou rollback. Lorsque la validation est automatique (SET autocommit=1 ;), il est possible d’utiliser des transactions. Pour cela, on démarre une transaction : START TRANSACTION ; La transaction se clôture dès qu’un commit ou un rollback est effectué. Il est possible d’éviter d’annuler toutes les requêtes d’une transaction et de placer des jalons permettant d’annuler seulement une partie des requêtes. Il faut d’abord placer un jalon : SAVEPOINT nom_jalon ; Pour annuler les requêtes jusqu’à un jalon : ROLLBACK [WORK] TO [SAVEPOINT] nom_jalon ; Il est aussi possible de retirer un jalon : RELEASE SAVEPOINT nom_jalon ; Certaines commandes SQL valident les transactions sans annulation possible. Il s’agit de commandes modifiant la structure de la base de données et non les données, à savoir : la création ou la suppression d’une base de données, la création, la modification ou la suppression d’une table, la création ou la suppression d’un index, la création des procédures stockées et des vues, la gestion des utilisateurs, le démarrage d’une nouvelle transaction (pas possible d’avoir une transaction dans une transaction), la création ou la suppression d’une verrou de table et l’ajout de données avec LOAD DATA. Lorsqu’une transaction est en cours et qu’elle modifie certaines lignes, un verrou est posé sur celles- ci tant que la transaction n’est pas terminée (les lignes ne sont donc pas utilisables par un autre utilisateur). Les transactions doivent respecter le principe ACID : Atomicité (les requêtes de la transaction doivent former un tout), Cohérence (les données doivent être cohérentes dans tous les cas), Isolation (une transaction ne doit pas interagir avec une autre transaction), Durabilité (les données doivent pouvoir être récupérées après la transaction). Que faire ? Exemple Empêcher la validation SET AUTOCOMMIT = 0 ; automatique de chaque requête (les requêtes peuvent alors être annulées ou validées) Annuler une requête / une ROLLBACK ; transaction Valider une requête / une COMMIT ; transaction Redemander la validation SET AUTO_COMMIT = 1 ; automatique de chaque requête Commencer une transaction START TRANSACTION ; Bases de données HELHa 2BI A. Colmant 2014 9 Ajouter un jalon pour SAVEPOINT mon_jalon ; permettre une annulation partielle de la transaction Annuler la transaction jusqu'à ROLLBACK TO mon_jalon ; un jalon Retirer un jalon RELEASE SAVEPOINT mon_jalon ; 3 Les verrous Un verrou peut être posé soit sur une table entière soit une partie des lignes d’une table. Cette deuxième possibilité est préférable car elle évite de bloquer inutilement d’autres utilisateurs. Pour verrouiller une table, on utilise l’instruction suivante : LOCK TABLES nom_table [READ | WRITE] [, nom_table2 …] ; En mettant READ, la lecture dans la table est toujours possible mais pas l’écriture car l’utilisateur ayant posé le verrou ne pourra que lire les éléments dans la table. Avec WRITE, la lecture comme la modification dans la table sont impossibles pour les autres utilisateurs. Lors de la pose d’un verrou, l’utilisateur responsable du verrou ne pourra utiliser que les tables qu’il a verrouillées. Tous les verrous nécessaires aux requêtes doivent donc être placés en une fois. En effet, l’instruction LOCK commence par libérer tous les verrous en cours de l’utilisateur. Il est important de préciser que START TRANSACTION a aussi pour effet de retirer les verrous de l’utilisateur. Il faut aussi noter que LOCK TABLES a pour effet de valider (commit) la transaction qui aurait au préalable débuté par START TRANSACTION. Pour utiliser le LOCK TABLES dans une transaction, il faut passer utiliser SET autocommit = 0 ;, ce qui va avoir pour effet de nécessiter une validation des requêtes. SI LOCK TABLES est utilisé, il valide les instructions qui le précèdent mais une nouvelle transaction commence alors directement et devra être validée (ou annulée). La validation de la requête devra être réalisée avant la libération des verrous. Pour déverrouiller les tables (on ne peut pas les déverrouiller une à une), on écrit : UNLOCK TABLES ; Un verrou de lignes peut être posé uniquement sur des tables utilisant InnoDB. Il y a des verrous partagés (lecture possible mais pas la modification) et des verrous exclusifs (ni lecture ni modification). Les requêtes de modification et de suppression placent des verrous exclusifs sur les lignes concernées. Les requêtes d’insertion imposent un verrou exclusif sur la ligne ajoutée. Pour insérer un verrou partagé, on ajoute LOCK IN SHARE MODE à la fin du SELECT pour placer le verrou. Pour placer un verrou exclusif, on ajoute FOR UPDATE à la fin du SELECT. Ces verrous de ligne sont donc libérés dès la fin de la requête qui a posé le verrou. Dans le cas des transactions, les verrous de lignes ne sont libérés qu’après un commit ou un rollback. Dans les cas des verrous exclusifs, les autres utilisateurs pourront lire le contenu de toutes les tables (même celles qui sont verrouillées) car ils travaillent sur les versions non modifiées. Pour forcer le système à mettre à jour les données dans les tables, il faut poser un verrou partagé. Lorsqu’un verrou de lignes est posé sur une colonne non indexée, le verrou bloque toutes les lignes de la table et pas juste les lignes souhaitées. Le niveau d’isolation des transactions peut être modifié : SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE } ; GLOBAL signifie que la modification porte pour tous les utilisateurs, SESSION fait porter la modification sur la seule session courante. Par défaut, la Bases de données HELHa 2BI A. Colmant 2014 10 modification porte sur la prochaine transaction de la session courante. REPEATABLE READ signifie qu’une lecture non-verrouillée répétée donnera toujours le même résultat (choix par défaut) sans se soucier des autres sessions. Avec READ COMMITTED, chaque select non verrouillé va faire une mise à jour des données avec toutes les modifications effectuées et validées par les autres sessions. Avec READ UNCOMMITTED, le select non verrouillé verra en plus les modifications effectuées (validées et non validées) par les autres sessions (donc des données qui seront peut-être annulées par la suite sont aussi présentes). SERIALIZABLE transforme tous les select non verrouillés en SELECT … LOCK IN SHARE MODE ; Que faire ? Exemple Placer un verrou en lecture sur LOCK TABLES livre READ ; une table (lecture toujours possible pour les autres utilisateurs) Placer un verrou en écriture LOCK TABLES livre WRITE ; sur une table (plus d'accès possible pour les autres utilisateurs) Retirer tous les verrous placés UNLOCK TABLES ; sur une ou des tables Placer un verrou partagé sur SELECT * FROM livre WHERE compteurlivre BETWEEN 1 certaines lignes d'une table AND 15 LOCK IN SHARE MODE ; Placer un verrou exclusif sur SELECT * FROM livre WHERE compteurlivre BETWEEN 1 certaines lignes d'une table AND 15 FOR UPDATE ; Faire en sorte que, dans la SET TRANSACTION ISOLATION LEVEL READ prochaine transaction, les COMMITTED ; select non verrouillés récupèrent des données mises à jour par les autres sessions 4 Les requêtes préparées Les variables utilisateur Les variables utilisateur sont toutes précédées d’un @. Pour créer ou modifier une variable utilisateur, il faut utiliser SET @nom_variable = valeur ; Ces variables peuvent ensuite être affichées au moyen d’un select. Elles peuvent être utilisées dans toute requête (pour remplacer une valeur ou pour effectuer un calcul). Si une variable est utilisée sans avoir été initialisée, sa valeur est nulle. Ces variables n’existent que pour la session dans laquelle elles sont créées. Bases de données HELHa 2BI A. Colmant 2014 11 Les requêtes préparées avec MySQL Une requête qui revient souvent et dont seuls certains paramètres varient peut être enregistrée comme une requête préparée. Une requête préparée n’existe que dans la session dans laquelle elle est créée. Voici sa syntaxe : PREPARE nom_requete FROM ‘requete’ ; Les paramètres sont représentés par des ? et ne peuvent remplacer que des valeurs. Si une requête possédait déjà le nom donné, elle remplace la précédente (qui est donc perdue). Pour utiliser la requête, EXECUTE nom_requete [USING @parametre1, @parametre2,…] ; Le passage des valeurs se fait donc uniquement par des variables utilisateur. Il faut qu’il y ait autant de paramètres passés que de paramètres demandés par la requête. Pour supprimer une requête préparée, on fait appel à DEALLOCATE PREPARE nom_requete ; Les requêtes sont généralement préparées par un autre langage de programmation utilisé avec MySQL (comme par exemple en utilisant PDO avec PHP). L’utilité des requêtes préparées est d’éviter les injections SQL et d’améliorer les performances pour les requêtes répétées régulièrement (car elles ne doivent pas être chaque fois compilées et ne doivent pas faire l’objet d’une recherche des tables concernées). L'utilisation de PDO avec PHP La première chose à réaliser pour pouvoir communiquer entre une application PHP et la base de données MySQL est d'établir une connexion entre les 2 en créant un objet PDO dans lequel on précise l'emplacement de la base de données ainsi que le nom d'utilisateur avec lequel se connecter à la base de données et le mot de passe de celui-ci : $bdd = new PDO('mysql:host=localhost;dbname=magasins', 'root', ''). Ensuite, afin d'éviter l'injection SQL, il est préférable d'utiliser une requête préparée au lieu de fournir directement la requête à la base de données. Une requête préparée permet de spécifier quelle requête exécuter avec d'éventuels arguments qui prendront place dans la requête (valeurs récupérées dans l'application). Pour préciser des arguments dans une requête, il faut placer des variables précédées de « : ». Voici l'exemple d'une requête préparée limitant le nombre d'enregistrements retournés : $bdd->prepare("SELECT nomempl, prenomemp FROM employe LIMIT :nb"). Avant de pouvoir exécuter la requête préparée, il faut lier les arguments à leur valeur. Cela se fait au moyen d'un bindValue : $requete->bindValue(':nb', 5, PDO::PARAM_INT). Dès que les arguments sont tous liés à la requête, il faut exécuter celle-ci en utilisant la commande execute : $requete->execute(). Pour afficher le contenu récupérée par la requête, il faut récupérer les enregistrements (plusieurs colonnes) un par un. La fonction fetch permet de récupérer un enregistrement dans lequel il faut alors récupérer les différents éléments (le nom de la colonne est celle récupérée avec la requête) : $empl = $requete->fetch(). Pour récupérer un élément d'un enregistrement : $empl['nomempl']. Lorsque le résultat de la requête n'est plus utile, il faut libérer le tableau : $requete->closeCursor(). Que faire ? Exemple Créer une variable utilisateur SET @var_user = 5 ; utilisable pendant toute la session Afficher la valeur d'une SELECT @var_user ; Bases de données HELHa 2BI A. Colmant 2014 12 variable utilisateur Créer une requête préparée PREPARE livres_avec_pages FROM 'SELECT * FROM livre demandant un paramètre WHERE nbpages >?' ; Utiliser une requête préparée SET @nb_p = 400 ; demandant un paramètre EXECUTE livres_avec_pages USING @nb_p ; Supprimer une requête DEALLOCATE PREPARE livres_avec_pages ; préparée Créer une connexion entre une $bdd = new PDO('mysql:host=localhost;dbname=magasins', 'root', application PHP et une base de '') ; données Créer une requête préparée en $requete = $bdd->prepare("SELECT nomempl, prenomemp y intégrant un argument FROM employe LIMIT :nb"); Lier un argument à une $nb =...; requête préparée $requete->bindValue(':nb', $nb, PDO::PARAM_INT); Exécuter une requête préparée $requete->execute(); Parcourir le résultat récupéré while ($empl = $requete->fetch()) par une requête { echo $empl['nomempl'].' '.$empl['prenomemp'].''; } Fermer le contenu de la $requete->closeCursor(); requête 5 Les procédures stockées La construction des procédures stockées Une procédure stockée contient une série d’instructions SQL qui sont exécutées lorsque la procédure est appelée. Celle-ci est stockée en mémoire de manière permanente contrairement à une requête préparée dont la durée de vie est limitée à la session qui la crée. Pour créer une procédure stockée, on utilise la syntaxe suivante : CREATE PROCEDURE nom_procedure ([arg1 [,arg2,…]]) instructions ; Des arguments peuvent être fournis à la procédure (les parenthèses sont obligatoires). S’il n’y a qu’une seule instruction SQL, elle est placée directement après les parenthèses. S’il y a plusieurs instructions, elles sont placées entre BEGIN instructions END ; (chaque instruction est suivie d’un ;). Attention toutefois car le serveur MySQL va considérer que la procédure stockée se termine au premier point-virgule rencontré (donc la première instruction). Pour résoudre ce problème, il faut changer dans la session dans laquelle la procédure stockée est créée le délimiteur pour pouvoir ajouter la procédure. Il faut donc définir un nouveau délimiteur (ne pas utiliser un caractère souvent utilisé) de la manière suivante : DELIMITER delimiteur. Ex. : DELIMITER | CREATE PROCEDURE maproc( ) BEGIN SELECT * FROM R1 ; END | Bases de données HELHa 2BI A. Colmant 2014 13 Les arguments d’une procédure peuvent être de 3 sortes : IN (pour les arguments reçus par la procédure et qui sont utilisés dans celle-ci), OUT (pour les valeurs retournées par la procédure et utilisables en dehors de celle-ci) et INOUT (pour les arguments reçus par la procédure et qui peuvent être modifiés et retournés dans celle-ci). Chaque argument sera défini par un sens (IN, OUT ou INOUT), un nom (ne peut pas être le nom d’une colonne présente dans une des tables utilisées dans la requête) et un type. Pour utiliser un argument OUT, il faut préciser dans une des requêtes de la procédure que la valeur est attribuée (ne peut être utilisé qu’avec des requêtes ne renvoyant qu’une seule ligne). Le mot-clé à utiliser est le INTO. Ex. : DELIMITER | CREATE PROCEDURE compter_R1(IN val INT, OUT compte INT) BEGIN SELECT COUNT(*) INTO compte FROM R1 WHERE col1 = val ; END |. Si plusieurs valeurs (plusieurs colonnes mais une seule ligne) doivent être récupérées, on place d’abord les noms des colonnes puis le INTO puis le nom des variables dans lesquelles sont stockées le résultat. Ex. : SELECT col1, col2 INTO arg1, arg2 FROM R1 ; Pour utiliser une procédure stockée, on fait CALL nom_procedure ( ); Dans le cas des arguments, les valeurs sont passées soit en utilisant des variables utilisateur soit en passant directement la valeur. Ex. : SET @valeur := 1 ; CALL compter(@valeur, @compte) ; SELECT @compte ; Pour supprimer une procédure, on indique DROP PROCEDURE nom_procedure ; Les variables locales Les variables locales se définissant en utilisant DECLARE nomVariable typeVariable [DEFAULT valeur] ; Une telle déclaration doit se trouver au début du bloc dans lequel on souhaite l’utiliser. La variable locale est alors utilisable dans le bloc (jusqu’au END) et les éventuels sous-blocs. Comme pour les variables utilisateur, il faut choisir un nom différent du nom d’une colonne utilisée. Pour changer sa valeur, on peut soit utiliser SET… soit SELECT … INTO … Les conditions Les conditions peuvent s’exprimer en MySQL sous la forme suivante : IF condition THEN instructions [ELSEIF condition2 THEN instructions …] [ELSE instructions] END IF ; Il peut y avoir plusieurs ELSEIF (mais pas après le ELSE). Une autre structure peut s’utiliser : le CASE. Sa syntaxe est la suivante : CASE variable_a_evaluer WHEN valeur1 THEN instructions [WHEN valeur2 THEN …] [ELSE instructions] END CASE; Le CASE peut s’utiliser dans la sélection d’une colonne (utilisation du AS pour donner un alias à la nouvelle colonne). Le CASE se termine alors par END et non END CASE. Ex. : SELECT col1, CASE WHEN col2 = val1 THEN 'message1' WHEN col2 = val2 THEN 'message2' ELSE 'message3' END AS nouveau_nom FROM R1 ; Il existe une structure IF beaucoup plus compacte : IF (condition, valeur_si_vrai, valeur_si_faux). Bases de données HELHa 2BI A. Colmant 2014 14 Les boucles L’instruction WHILE permet de faire une boucle : WHILE condition DO instructions END WHILE ; La boucle est exécutée tant que la condition reste vraie. L’instruction REPEAT exécute la boucle jusqu’à ce que la condition soit vraie (au moins une fois car la condition se trouve à la fin de la boucle) : REPEAT instructions UNTIL condition END REPEAT ; On peut aussi donner un nom à un bloc d’instructions (ainsi qu’à un WHILE ou un REPEAT) de la manière suivante : nom_bloc : BEGIN instructions END nom_bloc ; L’instruction LEAVE nom_bloc ; permet de sortir du bloc mentionné. L’instruction ITERATE nom_bloc ; (seulement dans une boucle) permet de passer à l’itération suivante (ce qui suit n’est donc pas exécuté). La dernière possibilité de faire une boucle est d’utiliser : [nom_boucle : ] LOOP instructions END LOOP [nom_boucle] ; Elle s’utilise uniquement avec une instruction LEAVE pour pouvoir en sortir. La gestion des erreurs Les gestionnaires d’erreur doivent se placer après les variables locales mais avant les instructions dans une procédure. Un gestionnaire d’erreur précise les instructions qu’il faut exécuter si une erreur survient. Pour le définir, on indique : DECLARE {EXIT | CONTINUE} HANDLER FOR {num_erreur | {SQLSTATE id_erreur} | condition} instructions. EXIT fait sortir de la procédure après le traitement de l’erreur tandis que CONTINUE permet de reprendre la procédure après traitement de l’erreur. L’erreur visée peut être mentionnée grâce à un numéro d’erreur MySQL, à l’identifiant de l’état SQL ou encore avec une condition (il s’agit en fait d’un nom donné à une erreur). Le numéro d'erreur MySQL est un nombre entier indiqué notamment lorsqu’une erreur se déclenche : ERROR XXXX … L'identifiant de l'état SQL est une chaîne de 5 caractères qui doit être précédée de SQLSTATE. Il commence par ‘00’ si l’instruction a réussi, donne 23000 en cas d’erreur sur une contrainte, 42000 pour plusieurs lignes alors qu’une seule attendue, 21000 une sous-requête renvoie plusieurs lignes alors qu’une seule attendue,… Il regroupe plusieurs erreurs et est donc moins précis qu’un numéro d’erreur. La déclaration d’une condition doit se trouver avant le gestionnaire d’erreurs : DECLARE nom_erreur CONDITION FOR {SQLSTATE id_erreur | num_erreur} ; Ce nom d’erreur peut alors être utilisé dans le gestionnaire d’erreur. Il existe des conditions prédéfinies qui permettent de traiter certains types d’erreur : SQLWARNING pour les avertissements (identifiant d’état SQL commençant par ‘01’), NOT FOUND pour les curseurs (identifiant d’état SQL commençant par ‘02’) et SQLEXCEPTION pour toutes les erreurs (identifiant d’état SQL ne commençant ni par ‘00’ ni par ‘01’ ni par ‘02’). La liste des erreurs est disponible sur la documentation de MySQL : https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html. Les avertissements peuvent également être traités de la même manière. Pour gérer plusieurs erreurs avec un même gestionnaire on sépare les erreurs par des virgules. Si plusieurs gestionnaires peuvent attraper une erreur, c’est le plus précis qui gère l’erreur (exemple si un gestionnaire avec numéro d’erreur et un autre avec un identifiant d’état, c’est le gestionnaire avec le numéro d'erreur qui Bases de données HELHa 2BI A. Colmant 2014 15 effectuera le traitement). Que faire ? Exemple Changer de délimiteur ( ; par DELIMITER | défaut) Créer une procédure stockée DELIMITER | recevant un argument et ne CREATE PROCEDURE augmentation_prix (IN pourc renvoyant rien NUMERIC(3,2)) BEGIN UPDATE livre SET prixconseille = prixconseille * (1+pourc); END | Appeler une procédure SET @augm = 0.1 ; stockée demandant un CALL augmentation_prix(@augm) ; paramètre Supprimer une procédure DROP PROCEDURE augmentation_prix ; stockée Créer une procédure qui DELIMITER | reçoit un paramètre et CREATE PROCEDURE nombre_livres_plus_pages (IN nbpag INT, renvoie un résultat OUT nbliv INT) BEGIN SELECT COUNT(*) INTO nbliv FROM livre WHERE nbpages >nbpag; END | Créer une procédure qui DELIMITER | reçoit 2 paramètres et utilise CREATE PROCEDURE prix_pages (IN nbpag INT, IN prixmax une variable locale et une NUMERIC(5,2)) condition BEGIN DECLARE moyenne NUMERIC(5,2); SELECT AVG(prixconseille) INTO moyenne FROM livre WHERE nbpages > nbpag; IF moyenne > prixmax THEN UPDATE livre SET prixconseille = prixconseille*0.95 WHERE nbpages > nbpag; END IF; END | Créer une procédure qui DELIMITER | reçoit un paramètre, renvoie CREATE PROCEDURE aug_prix_moyenne (IN moy NUMERIC(5,2), un résultat et utilise des OUT nbaugm INT) variables locales et une BEGIN boucle DECLARE nb INT; DECLARE moyenne NUMERIC(5,2); SET nb = 0; SELECT AVG(prixconseille) INTO moyenne FROM livre; WHILE moyenne < moy DO UPDATE livre SET prixconseille = prixconseille*1.01; Bases de données HELHa 2BI A. Colmant 2014 16  SELECT AVG(prixconseille) INTO moyenne FROM livre; SET nb = nb+1; END WHILE; SET nbaugm = nb; END | 6 Les triggers Les transactions et les requêtes préparées ne sont pas utilisables avec les triggers. Les triggers servent à exécuter un bloc d’instructions lorsque le contenu d’une table à laquelle ils sont liés est modifié (insertion, mise à jour, suppression d’enregistrement). Un trigger est donc déclenché par un événement qui peut soit être un ajout (INSERT), soit une modification (UPDATE) soit une suppression (DELETE). Les instructions du trigger peuvent être exécutées soit juste avant soit juste après l’événement déclencheur. Un trigger ne peut modifier ou insérer des enregistrements que dans des tables autres que la table liée au trigger. Dans la table liée au trigger, il peut que consulter ou modifier l’enregistrement concerné par l’événement. Un trigger peut servir à contrôler les règles d’intégrité pour certaines colonnes (les valeurs acceptables pour celle-ci) ainsi qu’à permettre l’archivage des données plutôt que leur suppression. Il permet également de maintenir un historique des actions effectuées. La création d’un trigger se fait de la manière suivante : CREATE TRIGGER nom moment evenement ON table FOR EACH ROW instructions ; Le moment où le trigger peut être déclenché est soit avant (BEFORE) soit après (AFTER) un événement. Un événement ne peut être qu’un (et un seul par trigger) des choix suivants : INSERT, UPDATE, DELETE. Il ne peut pas y avoir de doublon moment/événement sur une table. Pour le nom des triggers, il est conseillé de procéder de la manière suivante : moment_evenement_table (ex. : after_insert_livre). Depuis la version 5.7.2 de MySQL, il est possible de placer plusieurs triggers identiques (moment/événement) sur une même table. Les triggers sont alors exécutés dans l'ordre dans lequel ils ont été créés. Il est possible de décider de l'ordre d'exécution en plaçant dans les triggers les instructions PRECEDES ou FOLLOWS suivies du nom du trigger qui précède ou suit celui-en cours de création après le FOR EACH ROW. Dans les instructions, il est possible de récupérer les valeurs avant l’événement déclencheur avec OLD (les données ne peuvent qu’être lues) et les valeurs après l’événement déclencheur avec NEW (les données peuvent être lues et modifiées). OLD et NEW n’existent tous les deux que dans le cas de la modification (seulement OLD pour la suppression et seulement NEW pour l’insertion). Exemple : OLD.col1, NEW.col1,… Dans un trigger, il n'est pas permis de commencer ou de terminer une transaction. Les commandes suivantes sont donc interdites : START TRANSACTION, COMMIT et ROLLBACK. Il est par contre possible de faire une annulation jusqu'à un point de sauvegarde car cela ne met pas fin à la transaction. Bases de données HELHa 2BI A. Colmant 2014 17 La valeur d’une colonne référencée par NEW peut être modifiée en utilisant BEFORE mais pas AFTER. Si un trigger BEFORE déclenche une erreur qui n’est pas traitée alors l’événement déclencheur n’est pas effectué ni un éventuel trigger AFTER. Si un trigger AFTER génère une erreur alors l’événement déclencheur échoue (rollback). Un trigger peut être supprimé au moyen de DROP TRIGGER nom_trigger ; Lors de la suppression d’une table, tous les triggers qui sont liés à elle sont supprimés. Pour retrouver la liste des triggers, il faut soit demander de les afficher au moyen d'un SHOW TRIGGERS (uniquement les triggers concernant les tables présentes dans la base de données utilisée au moment de l'appel de la commande) soit aller dans la base de données nommées INFORMATION_SCHEMA et afficher le contenu de la table TRIGGERS qui reprend tous les triggers présents dans toutes les bases de données sur le serveur (colonne TRIGGER_NAME pour récupérer uniquement le nom). Que faire ? Exemple Créer un trigger sur une table DELIMITER | (ici pour gérer les CREATE TRIGGER before_delete_livre BEFORE DELETE ON suppressions) livre FOR EACH ROW BEGIN INSERT INTO livre_bis(isbn, titre, datesortie, nbpages, prixconseille, compteurlivre, supprime) VALUES (OLD.isbn, OLD.titre, OLD.datesortie, OLD.nbpages, OLD.prixconseille, OLD.compteurlivre, true); END | Supprimer un trigger DROP TRIGGER before_delete_livre; Préciser l'ordre des triggers qui DELIMITER | partagent un même CREATE TRIGGER before_delete_livre_bis BEFORE DELETE moment/événement sur une ON livre FOR EACH ROW PRECEDES before_delete_livre table BEGIN END | Afficher les triggers SHOW TRIGGERS ; concernant les tables d'une base de données Bases de données HELHa 2BI A. Colmant 2014 18 7 Les vues Les vues Une vue permet de récupérer des enregistrements à l’aide d’une requête et de permettre l’exploitation des données à partir de la vue plutôt que des tables. La vue porte un nom et une requête de sélection. Au même titre que les tables et les procédures stockées, une vue est stockée de manière durable dans la base de données. Il n’y a que la requête qui est stockée, le résultat de celle- ci est donc chaque fois recalculé. Une vue sert principalement à simplifier les requêtes complexes en permettant de récupérer un résultat intermédiaire souvent utilisé qui peut ensuite être exploité. Les vues facilitent également les interactions entre la base de données et une application extérieure (PHP ou autre) en évitant de devoir redéfinir toutes les requêtes écrites dans l’application extérieure à chaque changement de la structure des tables de la base de données. Les vues vont aussi permettre d’affiner les droits accordés aux utilisateurs de la base de données en cachant par exemple une partie du contenu de certaines tables qui ne doit pas être visible pour tous les utilisateurs. Pour créer une vue, on utilise la commande la suivante : CREATE VIEW nom_vue AS requete ; Le CREATE peut éventuellement être complété par OR REPLACE. Dans ce cas, si une vue portant le même nom existe, elle est remplacée par la nouvelle. Sans le OR REPLACE, une erreur est déclenchée si une vue porte déjà le même nom. Pour afficher le contenu d’une vue, il suffit de placer le nom de la vue dans le FROM d’une requête de sélection. Les vues apparaissant dans le détail des tables (SHOW TABLES), il est conseillé de placer un terme indiquant qu’il s’agit d’une vue devant le nom de celle-ci. Par exemple, toutes les vues pourraient avoir un nom commençant par view_nomdelavue. Les noms des colonnes dans la vue seront les mêmes que les noms placés dans la requête de sélection. Les alias peuvent bien sûr être utilisés pour donner d’autres noms aux colonnes. Deux colonnes ne peuvent bien entendu pas avoir le même nom. Attention, le nom de la table dont est extraite la colonne n’intervient pas dans le nom. Donc une requête comme la suivante ne fonctionne pas sans alias : SELECT R1.nom, R2.nom FROM R1 INNER JOIN R2 ON R1.col1 = R2.col1 ; Une autre solution consiste à donner le nom souhaité de toutes les colonnes à la suite du nom de la vue (donc avant la requête). Cette solution présente toutefois l’inconvénient qu’il faut s’assurer que les colonnes soient déclarées dans le même ordre que dans la requête. Ex.: CREATE VIEW (nouv_col1, nouv_col2) AS SELECT col1, col2 FROM R1 ; La requête placée dans une vue ne peut pas contenir de référence à une variable locale, une variable utilisateur ou encore à une variable système. A part cela, tout est possible avec les requêtes d’une vue (where, group by, jointures,…). Si la structure d’une table est modifiée après la création d’une vue portant sur celle-ci, la vue renvoie toujours le même résultat qu’avant la modification. Donc si la requête est la suivante : Bases de données HELHa 2BI A. Colmant 2014 19 CREATE VIEW view_r1 AS SELECT * FROM R1 ; et qu’une colonne est ajoutée à R1, cette nouvelle colonne n’apparaîtra pas dans le résultat de la vue. La vue doit être recréée pour tenir compte de la modification de la structure. Si un tri est effectué dans une vue, il n’est pris en compte que si la requête d’utilisation de la vue n’impose pas un tri. Donc, avec la vue suivante : CREATE VIEW view_nom AS SELECT * FROM R1 ORDER BY col1 ; le tri ne sert que si la requête d’utilisation ne demande pas un autre tri. Voici un exemple d’une requête qui ne tiendrait pas compte du tri de la vue : SELECT * FROM view_nom ORDER BY col2 ; Il est déconseillé d’utiliser une clause LIMIT dans une vue car, si la requête qui utilise la vue demande aussi un LIMIT, le comportement adopté par MySQL est indéterminé. Il est bien entendu possible d’utiliser une vue comme n’importe quelle table et donc de sélectionner uniquement une partie des colonnes renvoyées, de lui placer une clause WHERE, une jointure,… Une vue peut être modifiée après création en utilisant la commande ALTER VIEW. Cette commande a le même effet qu’un CREATE OR REPLACE à condition que la vue existe. Si aucune vue ne porte le nom placé dans le ALTER VIEW, une erreur est déclenchée. Pour supprimer une vue, il faut utiliser DROP VIEW [IF EXISTS] view_nom ; Lors de la création d’une vue, il est possible de spécifier quel algorithme sera utilisé lors de la récupération des données (donc au moment de l’utilisation de la vue pas de sa création). Pour cela, il faut ajouter la clause suivante entre CREATE et VIEW : ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}. Cette option n’est valable que pour MySQL. Par défaut, UNDEFINED est sélectionné et laisse donc le choix à MySQL entre les 2 autres options (MERGE est préféré quand c’est possible car il est plus performant). MERGE sert à préciser que la requête de la vue est complétée des conditions utilisées lors de l’appel de la vue. Ex. : CREATE ALGORITHM = MERGE VIEW view_R1 AS SELECT * FROM R1 WHERE col1 = val1 ; Si l’appel de la vue est le suivant : SELECT * FROM view_R1 WHERE col2 > val2 ;, alors la requête réellement exécutée par la vue sera la suivante : SELECT * FROM view_R1 WHERE col1 = val1 AND col2 > val2 ; Dans le cas de TEMPTABLE, la requête de la vue est d’abord exécutée et son résultat stocké dans une table intermédiaire avant de lui appliquer le filtre demandé lors de l’appel de la vue. Dans notre exemple précédent, si la vue est définie de la manière suivante : CREATE ALGORITHM=TEMPTABLE VIEW view_R1 AS… alors, avec le même appel, le résultat de la requête SELECT * FROM R1 WHERE col1 = val1 ; est stocké dans une table intermédiaire sur laquelle est appliquée la condition suivante WHERE col2 > val2 ; Une vue avec TEMPTABLE est moins performante car la sélection des enregistrements sur la table intermédiaire ne dispose pas d’index. L’algorithme MERGE ne peut pas être utilisé avec les clauses suivantes contenues dans la requête de sélection de la vue : DISTINCT, LIMIT, une fonction d’agrégation, GROUP BY, HAVING, UNION ou une sous-requête dans le SELECT. Une vue permet également de modifier le contenu des tables (UPDATE). Dans le cas d’une vue avec une jointure, la modification ne peut porter que sur une seule des tables. UPDATE view_nom SET col1 = val1, col2 = val2 WHERE… ne fonctionne que si col1 et col2 font partie de la même table. La vue doit être basée sur un algorithme MERGE. La modification n’est donc pas possible Bases de données HELHa 2BI A. Colmant 2014 20 pour les clauses non utilisables avec MERGE (voir plus haut) ainsi qu’avec des vues faisant appel à une vue ne répondant pas à ces critères. Pour l’insertion de données (INSERT), les règles sont les mêmes que pour la modification. De plus, les règles suivantes sont d’application : toutes les colonnes de la table qui ne possèdent pas de valeur par défaut et qui n’acceptent pas de valeur NULL doivent être présentes dans la vue et dans l’insertion. Dans le cas des jointures, l’insertion n’est possible qu’avec des jointures internes et sur une seule table. Les colonnes sélectionnées par la vue ne peuvent pas contenir d’expression (fonctions mathématiques, de texte,…). Une colonne ne peut pas non plus être présente plusieurs fois dans la vue. Une option peut être ajoutée à la vue pour spécifier les vérifications à réaliser lors de l’insertion ou la modification de données à partir de la vue. CREATE VIEW view_nom AS SELECT * FROM R1 WHERE col1 = val1 WITH [CASCADED | LOCAL] CHECK OPTION ; Avec WITH CHECK OPTION, les modifications et insertions doivent respecter la condition placée dans le WHERE. Donc il n’y aura pas d’insertion d’un enregistrement dont la valeur de col1 n’est pas val1 ni de modification d’un enregistrement si col1 ne vaut plus val1. L’option LOCAL signifie que les conditions de la requête de sélection sont appliquées et les conditions des vues sous-jacentes ne sont appliquées que si ces vues contiennent également l'option WITH CHECK OPTION. L'option CASCADED (par défaut) signifie que les conditions des éventuelles vues sous-jacentes sont aussi vérifiées (même si elles ne mentionnent pas de WITH CHECK OPTION). Ex. : CREATE VIEW view_nom AS SELECT * FROM R1 WHERE col1 = val1 ; CREATE VIEW view_nom2 AS SELECT * FROM view_nom WHERE col2 > val2 WITH CHECK OPTION; Lors de la modification et de l’ajout à partir de la vue view_nom2, si LOCAL est appliqué, alors seule la condition col2 > val2 doit être respectée. Par contre, avec CASCADED, non seulement la condition col2 > val2 doit être respectée mais également la condition col1 = val1. La suppression de données (DELETE) à partir d’une vue est possible si la modification est possible et que la requête de sélection de la vue ne concerne qu’une seule table. Les tables temporaires Une table temporaire n’existe que dans la session dans laquelle elle est créée. Elle disparaît donc dès la fermeture de la session dans laquelle elle a été construite. Pour créer une table temporaire, la syntaxe est la même que pour la création d’une table avec TEMPORARY ajouté entre CREATE et TABLE. Une table temporaire n’apparaît pas dans SHOW TABLES ; car seules les tables permanentes ainsi que les vues sont affichées par cette commande. La modification d’une table temporaire se fait en utilisant directement ALTER TABLE comme pour une table permanente. Pour la suppression d’une table temporaire, TEMPORARY peut être ajouté (mais n’est pas obligatoire) entre DROP et TABLE. Si TEMPORARY est ajouté, la table n’est supprimée que s’il s’agit d’une table temporaire. C’est donc plus sûr pour éviter une suppression d’une table permanente par erreur. L’utilisation (insertion, modification, suppression d’un enregistrement) de ces tables temporaires est identique aux tables permanentes. Bases de données HELHa 2BI A. Colmant 2014 21 Une table temporaire peut éventuellement porter le même nom qu’une table permanente. Pour la session dans laquelle elle est déclarée, elle cache la table permanente. Une table temporaire ne peut pas contenir de contrainte de clé étrangère et on ne peut pas faire référence plus d’une fois à une table temporaire dans une requête (ce qui empêche les auto-jointures et les sous-requêtes sur la même table). Ex. : SELECT * FROM tmp_R1 INNER JOIN tmp_R1 AS joint_R1 ON … ; ne fonctionne pas si tmp_R1 est une table temporaire. La modification de la structure d’une table temporaire (via CREATE, ALTER ou DROP) n’entraîne pas de validation d’une transaction contrairement aux tables permanentes. Ces commandes ne peuvent pas non plus être annulées avec un ROLLBACK. Les tables temporaires permettent un gain de performance si une même requête doit être effectuée plusieurs fois (pas de recalcul nécessaire). Les résultats des recherches sur cette table temporaire peuvent aussi être accélérés en plaçant des index sur les colonnes fortement sollicitées. Les tables temporaires peuvent également servir pour effectuer des tests sur une table avant de pratiquer les opérations souhaitées sur la table permanente. Elles peuvent être utilisées pour récupérer un résultat sur plusieurs lignes dans une procédure stockée. La table temporaire est créée dans la procédure et peut être récupérée après l’exécution de celle-ci. Les vues matérialisées Les vues matérialisées n’existent pas avec MySQL au contraire de certains SGBD. Une vue matérialisée ne contient pas la requête de sélection comme une vue traditionnelle mais directement le résultat de la requête. En MySQL, il faut créer une nouvelle table avec le contenu de la requête souhaitée. Pour vider le contenu d’une table, on peut utiliser TRUNCATE. Contrairement à DELETE qui supprime les lignes une par une, TRUNCATE supprime en réalité toute la table et recrée sa structure. Les clés étrangères ne permettent donc pas cette opération. La clause ON DELETE n’est pas appelée lors d’un TRUNCATE. TRUNCATE valide aussi une transaction, il n’y a donc pas de ROLLBACK possible. Pour mettre à jour une vue matérialisée, on peut opter soit pour une mise à jour à la demande soit pour une mise à jour à chaque modification de la table. Pour la mise à jour à la demande, on crée une procédure qui vide la vue puis la remplit avec les données complètes. Pour une mise à jour automatique, les triggers sont utilisés. Une option permet d’éviter que MySQL mette le résultat d’une requête en cache (il ne refait plus les calculs pour obtenir le résultat mais renvoie directement le cache) : SELECT SQL_NO_CACHE … Si on compare les temps d’exécution de requêtes sur les tables, sur les vues et sur les vues matérialisées, les résultats sont à peu près similaires pour les tables et les vues mais les résultats pour les vues matérialisées sont bien meilleurs. Toutefois, il faut choisir les requêtes pour lesquelles il est intéressant de créer une vue matérialisée. En effet, c’est intéressant pour des requêtes avec des TEMPTABLE et pour les données qui ne sont pas souvent mises à jour (dans le cas contraire l’utilisation des triggers fait perdre beaucoup de temps). Bases de données HELHa 2BI A. Colmant 2014 22 La création d'une table sur base de la structure et des données d'une autre table Une copie complète de la structure d’une table peut être réalisée avec la commande suivante : CREATE [TEMPORARY] TABLE copie LIKE table_acopier ; Cette copie englobe les types des colonnes, les contraintes, les valeurs par défaut,… à l’exception des clés étrangères s’il s’agit d’une table temporaire. Les enregistrements présents dans la table à copier ne sont pas transférés dans la nouvelle table. Pour insérer le contenu, il suffit d’utiliser un INSERT couplé à un SELECT : INSERT INTO copie SELECT * FROM table_acopier ; Une autre manière de procéder qui permet de créer la table et de la remplir de données est : CREATE [TEMPORARY] TABLE autre_copie SELECT col1, col2 FROM table_acopier [WHERE … ] ; Dans ce cas, seules les colonnes indiquées sont créées dans la nouvelle table et les index, clés primaires et étrangères ainsi que l’auto-incrémentation ne se retrouvent pas dans la nouvelle table (on ne retrouve que les valeurs par défaut et les NOT NULL). Il est toutefois possible de préciser la structure avant l’ajout des données de la manière suivante : CREATE [TEMPORARY] TABLE autre_copie(col1 INT AUTO_INCREMENT, col2 VARCHAR(10) NOT NULL,…, PRIMARY KEY(col1), INDEX(col2),…) SELECT … ; L’ajout des données dans la table se fait au moyen des noms des colonnes. Il faut donc qu’il y ait correspondance entre le nom des colonnes décrivant la structure et les noms des colonnes récupérés par le SELECT. Les alias peuvent bien entendu être utilisés. Si le SELECT contient des colonnes qui ne sont pas reprises dans la description de la nouvelle table alors les colonnes sont créées avec le type spécifié dans la table à copier. Il n’est donc pas nécessaire de repréciser le type de chaque colonne mais juste celles qui doivent être redéfinies ou utilisées dans une contrainte. On peut donc aussi créer des tables et les remplir en utilisant les colonnes de plusieurs tables (utilisation de jointures). Que faire ? Exemple Créer une vue CREATE VIEW view_nombre_livres AS SELECT COUNT(*) AS nbliv FROM livre; Utiliser une vue SELECT nbliv FROM livre; Créer ou remplacer une vue si CREATE OR REPLACE VIEW view_nombre_livres AS SELECT elle existe COUNT(*) AS nbliv FROM livre; Modifier une vue existante ALTER VIEW view_nombre_livres AS SELECT COUNT(*) AS nblivres FROM livre; Supprimer une vue DROP VIEW view_nombre_livres; Créer une vue en utilisant CREATE ALGORITHM=MERGE VIEW view_livres_pages AS l'algorithme « merge » SELECT * FROM livre WHERE nbpages > 450; Mettre à jour une table par UPDATE view_livres_pages SET prixconseille = prixconseille * l'intermédiaire d'une vue 0.99; Ajouter un enregistrement à INSERT INTO view_livres_pages (isbn, titre, nbpages, datesortie, partir d'une vue prixconseille) VALUES ('978-2-2515-54-2','MySQL pour les pros',550,'2016-01-05',25.0); Supprimer un enregistrement à DELETE FROM view_livres_pages WHERE compteurlivre = 14; partir d'une vue Bases de données HELHa 2BI A. Colmant 2014 23 Créer une table temporaire CREATE TEMPORARY TABLE table_temp (...); Modifier la structure d'une ALTER TABLE table_temp... ; table temporaire Supprimer une table DROP TEMPORARY TABLE table_temp; temporaire Vider tout le contenu d'une TRUNCATE livre_bis; table Créer une table sur base de la CREATE TABLE livre_bis LIKE livre; structure d'une autre Insérer les données d'une table INSERT INTO livre_bis SELECT * FROM livre; dans une autre table Créer une table sur base de la CREATE TABLE livre_ter SELECT isbn, titre FROM livre; structure et du contenu d'une autre (pas de gestion des contraintes) Bases de données HELHa 2BI A. Colmant 2014 24