Modifier automatiquement des données sélectionnées

Pour ce troisième exemple, nous allons nous intéresser au schéma (là encore très simplifié) d’une application qui permet de vendre des produits en France et au Royaume-Uni, en euro, livre et dollar. Cette application possède une table produit, qui contient le produit (son identifiant) et son prix hors taxe en euro.

Structure de la table produit:

CREATE TABLE produit (
  id_produit mediumint(8) unsigned NOT NULL auto_increment,
  prix_ht decimal(6,2) default NULL,
  PRIMARY KEY  (id_produit)
)

Nous disposons également des tables devise et tva qui gèrent respectivement le taux de change des devises et la TVA de différents pays.

Tables devise et tva:

CREATE TABLE devise (
  devise enum('Euro', 'Dollar', 'Livre') NOT NULL,
  valeur decimal(6,5) default NULL,
  PRIMARY KEY  (devise)
);

INSERT INTO devise VALUES ('Livre',0.66017);
INSERT INTO devise VALUES ('Dollar',1.29852);
INSERT INTO devise VALUES ('Euro',1);

CREATE TABLE tva (
  pays enum('France', 'Royaume-Uni') NOT NULL,
  normal decimal(3,1) default NULL,
  reduit decimal(3,1) default NULL,
  PRIMARY KEY  (pays)
);

INSERT INTO tva VALUES ('Royaume-Uni',17.5,5.0);
INSERT INTO tva VALUES ('France',19.6,5.5);

Le besoin est le suivant : disposer simplement des prix TTC pour chaque pays.

On va donc créer deux vues par pays qui nous permettrons de disposer des prix TTC en fonction de la devise.

La vue produit_france, contient les produits, le prix TTC et le prix TTC réduit qui correspond à l’ajout de la TVA réduite. Les prix sont en euros.

Vue produit_france :

CREATE VIEW 'produit_france' AS
SELECT 'produit'.'id_produit' AS 'produit',
round((((('produit'.'prix_ht' * 'tva'.'normal') / 100) + 'produit'.'prix_ht')
     * 'devise'.'valeur'),2) AS 'Prix_ttc_€',
round((((('produit'.'prix_ht'* 'tva'.'reduit') / 100) + 'produit'.'prix_ht')
     * 'devise'.'valeur'),2) AS 'Prix_ttc_reduit_€'
FROM (('produit' join 'tva') join 'devise')
WHERE (('tva'.'pays' = 'France') and ('devise'.'devise' = 'Euro'));

Certains clients préférant la monnaie de l’oncle Sam, une deuxième vue, produit_france_dollar, est nécessaire pour avoir les prix en dollar.

Vue produit_france_dollar:

CREATE VIEW 'produit_france_dollar' AS
SELECT 'produit'.'id_produit' AS 'produit',
round((((('produit'.'prix_ht' * 'tva'.'normal') / 100) + 'produit'.'prix_ht')
     * 'devise'.'valeur'),2) AS 'Prix_ttc_$',
round((((('produit'.'prix_ht'* 'tva'.'reduit') / 100) + 'produit'.'prix_ht')
     * 'devise'.'valeur'),2) AS 'Prix_ttc_reduit_$'
FROM (('produit' join 'tva') join 'devise')
WHERE (('tva'.'pays' = 'France') and ('devise'.'devise' = 'Dollar'));

Même principe pour le Royaume-Uni :

CREATE VIEW 'produit_royaume_uni' AS
SELECT 'produit'.'id_produit' AS 'produit',
round((((('produit'.'prix_ht' * 'tva'.'normal') / 100) + 'produit'.'prix_ht')
     * 'devise'.'valeur'),2) AS 'Net_price_£',
round((((('produit'.'prix_ht'* 'tva'.'reduit') / 100) + 'produit'.'prix_ht')
     * 'devise'.'valeur'),2) AS 'reduced_ Net_price_£'
FROM (('produit' join 'tva') join 'devise')
WHERE (('tva'.'pays' = 'Royaume-Uni') and ('devise'.'devise' = 'Livre'));

Avec les prix en dollar :

CREATE VIEW 'produit_royaume_uni_dollar' AS
SELECT 'produit'.'id_produit' AS 'produit',
round((((('produit'.'prix_ht' * 'tva'.'normal') / 100) + 'produit'.'prix_ht')
     * 'devise'.'valeur'),2) AS 'Net_price_$',
round((((('produit'.'prix_ht'* 'tva'.'reduit') / 100) + 'produit'.'prix_ht')
     * 'devise'.'valeur'),2) AS 'reduced_ Net_price_$'
FROM (('produit' join 'tva') join 'devise')

WHERE (('tva'.'pays' = 'Royaume-Uni') and ('devise'.'devise' = 'Dollar'));

(à suivre… Conserver la structure d’une table)

Tags: exemples vues, MySQL, MySQL 5, vue

Masquer la complexité du schéma

L’équipe de développement doit écrire un moteur de recherche pour une application de commerce électronique. Voici un extrait des tables de la base de données impliquées dans la recherche des produits du site.

La difficulté est de générer la bonne requête avec les bonnes jointures (plus d’une dizaine), à chaque recherche. Une solution pour faciliter le travail des développeurs est de créer une vue qui fait référence à toutes les tables impliquées dans la recherche des produits. Les recherches se feront donc sur cette vue, avec des requêtes plus simples à écrire.

La vue est créée avec l’algorithme TEMPTABLE, les verrous sur les tables sous-jacentes seront libérés plus rapidement ce qui permettra de moins pénaliser les autres requêtes. TEMPTABLE a la particularité de rendre la vue non modifiable, mais cela ne gêne pas du tout, car la vue n’est accédée qu’en lecture.

Extrait de la vue « moteur de recherche »:

CREATE ALGORITHM = TEMPTABLE
VIEW moteur_de_recherche
AS
SELECT
   s.product_name as nom_produit,
   ... (les champs nécessaires)
FROM product s
 LEFT JOIN product_files sf ON s.product_id=sf.file_product_id
 LEFT JOIN ..... (toutes les tables impliquées)
WHERE ... ;

Cette astuce permet de « simplifier » de façon assez significative le schéma.

Un dernier mot pour noter que cette solution ne permet pas d’améliorer les performances de la recherche, dans le sens où la requête qui génère la vue est lancée à chaque appel de cette dernière.

(à suivre… Modifier automatiquement des données sélectionnées)

Tags: MySQL, MySQL 5, vue

Utiliser les vues

Voici quelques exemples pratiques très simples pour illustrer les différents besoins auxquels peuvent répondre les vues. On aura ici, une vue administrateur de base de données. Les objets créés ne seront pas utilisés directement par les utilisateurs mais aux travers d’une application.

Contrôler l’intégrité en restreignant l’accès aux données pour améliorer la confidentialité

La table employe de mon application, contient toutes les informations sur les employées.

CREATE TABLE `employe` (
  `id_employe` int(10) unsigned NOT NULL auto_increment,
  `nom` char(45) NOT NULL,
  `prenom` char(45) NOT NULL,
  `tel_perso` char(10) NOT NULL,
  `tel_bureau` char(10) NOT NULL,
  `statut` char(45) NOT NULL,
  `ville` char(45) NOT NULL,
  `salaire` decimal(7,2) NOT NULL,
  PRIMARY KEY  (`id_employe`)
) ;

Toutes les informations présentes dans cette table ne sont pas pertinentes pour les trois types d’utilisateurs suivant: le comptable, la secrétaire pour Paris et la secrétaire pour le reste de la France.

Une solution est donc de créer une vue par type.

Pour le comptable, il faut avoir accès aux champs nom, prénom, téléphone du bureau, statut et salaire de chaque employée. On fait donc un partitionnement vertical de la table employe. La vue correspondante est la suivante :

CREATE ALGORITHM=MERGE  SQL SECURITY DEFINER VIEW `v_comptable` AS
SELECT nom, prenom, tel_bureau, statut, salaire FROM employe;

Le profil « secrétaire pour Paris », n’a pas besoin de l’identifiant et il ne doit surtout pas avoir accès aux salaires, cette information étant confidentielle. Autre restriction, ce profil ne gère que les employées de la filiale de Paris. Le partitionnement est vertical et horizontal.

CREATE ALGORITHM= MERGE SQL SECURITY DEFINER VIEW `v_secretaire_paris` AS
SELECT nom, prenom, tel_perso, tel_bureau, statut FROM employe
WHERE ville = 'Paris';

Notre troisième vue est très proche de la deuxième. La seule différence vient du fait que là, on veut les employés qui ne travaillent pas à Paris.

CREATE ALGORITHM= MERGE SQL SECURITY DEFINER VIEW `v_secretaire_autre` AS
SELECT nom, prenom, tel_perso, tel_bureau, statut FROM employe
WHERE ville <> 'Paris';

(à suivre… Masquer la complexité du schéma)

Tags: MySQL, MySQL 5, vue

Restrictions

Lors de la création d’une vue, certaines contraintes doivent être prises en compte :

• Il n’est pas possible de créer un index sur une vue
• La vue ne peut pas contenir de sous-requêtes dans la clause FROM du SELECT.
• Il n’est pas possible d’utiliser de variables dans une vue.
• Les objets (tables et vues) nécessaires à la création de la vue doivent exister avant de la créer.
• Si un objet référencé par la vue est effacé, la vue n’est alors plus accessible.
• Une vue ne peut référencer une table temporaire (TEMPORARY TABLE)
• Il n’est pas possible de créer des vues temporaires.
• Il n’est pas possible d’associer un trigger à une vue.
• La définition d’une vue est « gelée » dans une requête préparée.

Exemple:

mysql> CREATE VIEW ma_vue AS SELECT 'première valeur';
Query OK, 0 rows affected (0.24 sec)

mysql> desc ma_vue;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| première valeur | varchar(15) | NO   |     |         |       |
+-----------------+-------------+------+-----+---------+-------+
1 row in set (0.50 sec)

mysql> PREPARE req_prepare FROM 'SELECT * FROM ma_vue';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE req_prepare;
+-----------------+
| première valeur |
+-----------------+
| première valeur |
+-----------------+
1 row in set (0.01 sec)

ALTER VIEW ma_vue AS SELECT 'deuxième valeur';
Query OK, 0 rows affected (0.05 sec)

mysql> desc ma_vue;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| deuxième valeur | varchar(15) | NO   |     |         |       |
+-----------------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> EXECUTE req_prepare;
+-----------------+
| première valeur |
+-----------------+
| première valeur |
+-----------------+
1 row in set (0.00 sec)

Il faut en fait recréer la requête préparée :

mysql> DEALLOCATE PREPARE req_prepare;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE req_prepare FROM 'SELECT * FROM ma_vue';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE req_prepare;
+-----------------+
| deuxième valeur |
+-----------------+
| deuxième valeur |
+-----------------+
1 row in set (0.00 sec)

(à suivre… utiliser les vues)

Tags: MySQL 5, prepare statement, vue

Syntaxe d’une vue

CREATE VIEW

La commande MySQL pour créer une vue est assez proche de la syntaxe du standard SQL.

CREATE VIEW nom_de_la_vue AS requête_select

CREATE TABLE etudiant (

id_etudiant int unsigned primary key,

nom char(30),

prenom char(30),

age tinyint unsigned,

cursus enum('Licence', 'Master', 'Doctorat'));

CREATE VIEW v_etudiant_liste AS SELECT nom, prenom FROM etudiant;

Après avoir créé la table etudiant, on crée la vue v_etudiant_liste qui contient le nom et le prénom des étudiants.

Il est possible d’ajouter d’autres informations lors de la création de la vue:

CREATE

[OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = { user | CURRENT_USER }]

[SQL SECURITY { DEFINER | INVOKER }]

VIEW nom_de_la_vue [(colonne(s))]

AS requête_select

[WITH [CASCADED | LOCAL] CHECK OPTION]

Voici dans le détail les différentes clauses.

OR REPLACE

Si une vue du même nom existe, elle est alors supprimée et remplacée par la nouvelle.

ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}

Clause non standard, qui prend les valeurs suivantes:

• UNDEFINED : C’est la valeur par défaut. MySQL décide lui-même quel algorithme choisir entre MERGE et TEMPTABLE.
• MERGE : utilise la requête SQL ayant servie à la création de la vue comme base d’opération. En d’autres termes, faire une requête sur la vue revient à faire la même requête sur la ou les tables sous-jacentes.
• TEMPTABLE : utilise une table temporaire créée pour stocker (temporairement) les résultats. Un intérêt de cet algorithme est de libérer plus rapidement les verrous sur les tables sous-jacentes. Les autres requêtes sont alors moins pénalisées.

Il faut noter également qu’une vue avec pour valeur MERGE sera modifiable alors qu’avec la valeur TEMPTABLE elle ne le sera pas.

DEFINER = { user | CURRENT_USER }

Clause non standard qui permet d’assigner un créateur à la vue. Par défaut, le créateur de la vue est DEFINER = current_user, c’est-à-dire, l’utilisateur qui exécute la commande CREATE VIEW. Il est cependant possible d’assigner la vue à un autre compte utilisateur, à condition d’avoir le droit SUPER.

SQL SECURITY { DEFINER | INVOKER }

Clause non standard qui permet de définir quels seront les droits de l’utilisateur, lors de l’exécution de la vue. Deux valeurs sont possibles:

• DEFINER qui permet d’exécuter la vue avec les droits du créateur. C’est la valeur par défaut.
• INVOKER qui permet d’exécuter la vue avec ses propres droits.

WITH [CASCADED | LOCAL] CHECK OPTION

Permet de vérifier les contraintes spécifiées dans la clause WHERE d’une vue modifiable lorsque l’on y modifie ses données. Deux valeurs sont possibles:

• CASCADED, la valeur par défaut. Elle permet de vérifier la contrainte pour la vue ainsi que pour les vues sous-jacentes dont elle dérive.
• LOCAL qui permet de vérifier seulement la contrainte de la vue.

ALTER VIEW

Une fois la vue créée, il est bien évidement possible de la modifier avec la commande ALTER VIEW.

ALTER definer='secretaire'@'localhost' VIEW v_etudiant_liste AS SELECT nom, prenom, cursus FROM etudiant;

Cette commande modifie la clause DEFINER en lui assignant le compte secretaire@localhost et modifie la définition de la vue en rajoutant le champ cursus.

DROP VIEW

Permet d’effacer une vue.

DROP VIEW v_etudiant_liste, v_prof_liste;

Supprime les vues v_etudiant_liste et v_prof_liste. Il est possible d’ajouter la clause IF EXISTS qui retourne un avertissement au lieu d’une erreur si la vue à effacer n’existe pas.

(à suivre… les restrictions pour créer une vue)

Tags: MySQL, vue