View on GitHub

lp4-2019

SQL avancé avec MySQL : Mise en pratique

Nous allons découvrir un langage côté MySQL pour créer des :

Voici les tables sur lesquelles nous allons travailler

banque-mld

Règles de gestion :

Un Compte est associé à un et un seul Titulaire

Un Compte correspond à un seul Type de compte

Un Compte peut avoir aucune ou plusieurs Opérations

Une Opération de virement d’un Compte à un autre Compte doit se faire si et seulement si le compte débiteur a un solde suffisant. Si ce n’est pas le cas, la transaction ne pourra pas se réaliser !

Base de données sous MySQL

Vous devez créer une base de données vierge nommée : banque

Script SQL pour créer nos 4 tables

CREATE TABLE `titulaire` (
  `CODE` int(4) NOT NULL,
  `PRENOM` varchar(32) NOT NULL,
  `NOM` varchar(32) NOT NULL,
  `ADRESSE` varchar(32) DEFAULT NULL,
  `CODEPOSTAL` char(5) DEFAULT NULL,
  PRIMARY KEY (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `titulaire` (`CODE`, `PRENOM`, `NOM`, `ADRESSE`, `CODEPOSTAL`) VALUES
(1000, 'Philippe', 'Bouget', '52 rue de la Java', '75013'),
(1001, 'Mireille', 'Kupper', '22 rue Loin de Paris', '91000'),
(1002, 'Juliette', 'Barats', '13 rue de la Bureautique', '75011'),
(1003, 'Malika', 'Chabira', '23 rue de l\'insertion', '7018'),
(1004, 'Josselin', 'Tobelem', '18 rue de SpringBoot', '93000'),
(1005, 'Jonathan', 'Siffert', '22 rue des Projets', '93000');

CREATE TABLE `typecompte` (
  `CODE` char(1) NOT NULL,
  `INTITULE` varchar(25) NOT NULL,
  PRIMARY KEY (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `typecompte` (`CODE`, `INTITULE`) VALUES
('C', 'Compte courant'),
('E', 'Compte épargne'),
('L', 'Compte épargne Logement');

CREATE TABLE `compte` (
  `NUMERO` int(5) NOT NULL,
  `CODETYPECOMPTE` char(1) NOT NULL,
  `CODETITULAIRE` int(4) NOT NULL,
  `SOLDE` float(10,2) NOT NULL,
  PRIMARY KEY (`NUMERO`),
  FOREIGN KEY (`CODETYPECOMPTE`)
    REFERENCES `typecompte` (`CODE`),
  FOREIGN KEY (`CODETITULAIRE`)
   REFERENCES `titulaire` (`CODE`)
   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `compte` (`NUMERO`, `CODETYPECOMPTE`, `CODETITULAIRE`, `SOLDE`) VALUES
(10000, 'C', 1000, 3500.00),
(10001, 'C', 1001, 7000.00),
(10002, 'C', 1002, 2800.00),
(10003, 'C', 1003, 15200.00),
(10005, 'C', 1004, 60000.00),
(10006, 'C', 1005, 140000.00),
(10007, 'L', 1000, 100.00),
(10008, 'L', 1001, 100.00),
(10009, 'L', 1002, 200.00);

CREATE TABLE `operations` (
  `NUMERO` int(11) NOT NULL AUTO_INCREMENT,
  `NUMEROCOMPTE` int(5) NOT NULL,
  `DATE` date NOT NULL,
  `LIBELLE` varchar(32) NOT NULL,
  `MONTANT` float(10,2) NOT NULL,
  `TYPE` char(1) NOT NULL,
  PRIMARY KEY (`NUMERO`),
  FOREIGN KEY (`NUMEROCOMPTE`)
    REFERENCES `COMPTE` (`NUMERO`)
    ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `operations` (`NUMERO`, `NUMEROCOMPTE`, `DATE`, `LIBELLE`, `MONTANT`, `TYPE`) VALUES
(1, 10000, '2018-10-01', 'Dépôt', 3500.00, '+'),
(2, 10001, '2018-10-18', 'Salaire', 7000.00, '+'),
(3, 10002, '2018-11-02', 'Dépôt', 2800.00, '+'),
(4, 10003, '2018-10-09', 'Salaire ', 15200.00, '+'),
(5, 10005, '2018-12-19', 'Dépôt', 150.00, '+'),
(6, 10006, '2018-12-19', 'Dépôt', 507.00, '+'),
(7, 10001, '2018-08-10', 'Dépôt ouverture', 2050.00, '+');

1 Procédures Stockées

1.1 le Cadeau de la banque

La banque décide de fêter la nouvelle année en partageant ses gains avec les anciens clients. Pour cela, elle ajoutera une somme sur tous les comptes des clients ayant effectués des opérations antérieures au 31/10/2018 et dont le montant du versement était supérieur à 2000 euros (valeur constante).

La directrice Elise vous demande d’écrire et de lancer une procédure stockée nommée proc_cadeau permettant d’ajouter 19 euros (pour chaque opération trouvée)pour tous les comptes concernés et d’effectuer automatiquement la modification sur le solde du compte des clients. Il faut prévoir de réutiliser cette procédure l’an prochain et considérer que le montant du cadeau et la date peuvent changer !

2 Triggers

2.1 Duplication de données dans une autre table sous condition

Créer une table identique à celle de compte que vous nommerez compteriche. Vous pouvez ne pas activer les clefs étrangères codetypecompte et codetitulaire pour cette table.

CREATE TABLE IF NOT EXISTS `compteriche` (
  `NUMERO` int(11) NOT NULL,
  `CODETYPECOMPTE` char(1) NOT NULL,
  `CODETITULAIRE` int(4) NOT NULL,
  `SOLDE` float(10,2) NOT NULL,
  PRIMARY KEY (`NUMERO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

La directrice vous demande d’écrire un Trigger qui permet lors de chaque ouverture de compte courant dont la somme est supérieure à 10000 euros :

2.2 Historisation automatique avec le Trigger : histotitulaire

On vous demande de mettre en place un trigger qui permet après chaque mise à jour des informations d’un Titulaire, de conserver les anciennes données dans la table en y ajoutant la date du jour de la modification et le type d’action. Ici, l’action sera égale à “MAJ” pour mise à jour.

Voici le script de la Table :

CREATE TABLE `histotitulaire` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `DATEACTION` datetime NOT NULL,
  `ACTION` varchar(15) DEFAULT NULL,
  `CODE` int(4) NOT NULL,
  `PRENOM` varchar(32) NOT NULL,
  `NOM` varchar(32) NOT NULL,
  `ADRESSE` varchar(32) DEFAULT NULL,
  `CODEPOSTAL` char(5) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3 Transactions

Voici un lien vers une démo d’appli qui utilise la transaction avant la mise en pratique.

Demo Transaction

Grâce à votre code java, vous allez effectuer une connexion à la base de données banque et réaliser les opérations suivantes sous la forme d’une Transaction pour respecter les contraintes ACID.

Vous avez un tutoriel sur OpenClassroom qui est bien écrit et peut vous aider et aussi des explications détaillées ci-dessous.

Tuto sur openclassroom sur les Transactions avec Spring

Généralités

Une transaction est un bloc d’instructions de mise à jour dans la base de données. C’est une suite de requêtes INSERT, UPDATE ou DELETE qui ne sera validée définitivement qu’à la fin du bloc.

On commence une transaction par l’instruction START TRANSACTION, et c’est l’instruction COMMIT qui valide cette transaction.

Il est possible d’annuler une transaction en cours grâce à l’instruction ROLLBACK.

Propriétés d’une transaction

L’acronyme ACID désigne les quatre attributs fondamentaux d’un gestionnaire de transaction :

Autocommit

Par défaut, une base de données est en autocommit. C’est à dire que chaque instruction de mise à jour est définitive dans la base.

La transaction se résume en une seule ligne.

L’instruction START TRANSACTION bascule l’autocommit à false (faux).
Une autre manière de faire une transaction, c’est de basculer l’autocommit à false avec la syntaxe suivante : (SET autocommit=0).

Dans ce cas, la transaction démarre à la première mise à jour. La validation définitive est toujours avec le COMMIT.
C’est souvent de cette façon qu’on gère les transactions dans un programme java ou php.

Moteur de stockage

Les bases de données fonctionnent avec des moteurs différents. On ne choisit pas son moteur sous Oracle ou Postgres. Par contre avec MySql, nous avons le choix entre InnoDB et MylSAM. MylSAM ne gère pas les transactions (ni la gestion des clefs étrangères d’ailleurs).

Fonctionnement

Lorsque l’on commence une transaction, toutes les mises à jour mettent la base dans un état transitoire.

Cet état n’est visible que de la session en cours. Si un autre client se connecte sur la base, il ne voit pas cette transaction.

Si cette transaction échoue la base revient à l’état précédent.
On peut comparer cela à une zone tampon, ou à une branche sous git.

Transactions avec Spring

Annotation

Comme d’habitude, Spring utilise une annotation : @Transactional.
Les transactions se font au niveau du service.

Il suffit de mettre cette annotation au niveau de la méthode pour que cette méthode démarre et valide la transaction. Si une exception survient dans cette méthode, automatiquement un rollback est effectué sur la transaction en cours.
Si tout ce passe bien, un commit est effectué à la fin de la méthode.

gestionnaire de transaction

La transaction a besoin d’un gestionnaire de transaction.

Ce gestionnaire sera différent selon qu’on utilise du jdbc, du jpa, ou autre. Heureusement avec Spring Boot, il n’y a pas besoin de configurer ce gestionnaire.

La propagation

Nous allons voir la propagation du contexte transactionnel.

Spring a ajouté un niveau supplémentaire : la transaction logique.

Une méthode @Transactional peut appeler une autre méthode @Transactional. Ceci déclenche une sous-transaction.

Que faut-il faire dans ce cas pour garder les propriétés ACID de la transaction ?

Continuer dans la même transaction ? Faire une nouvelle transaction ?

2 méthodes de propagation sont intéressantes :

  • @Transactional(propagation=Propagation.REQUIRED) (par défaut)
  • @Transactional(propagation=Propagation.REQUIRES_NEW) (créer une nouvelle transaction physique)

Lisez le paragraphe propagation du contexte transactionnel de ce tuto pour bien comprendre la différence avant de vous lancer dans le TP pour effectuer l’exercice sur le virement.

## Transactions et notions de verrous

Lorsque vous effectuer 2 opérations de virement au même moment, que faire ?

Une première solution pour résoudre ces incohérences est de poser un verrou sur une table (en écriture ou en lecture, suivant le cas) avant d’effectuer toute séquence d’opération réalisant une transaction sur cette table.

Un seul verrou en écriture peut être détenu à un moment donné, et un verrou en lecture ne peut être détenu en même temps qu’un verrou en écriture. Les ordres pertinents sont :

LOCK TABLES table1 READ, table2 READ, ...;
LOCK TABLES table1 WRITE, table2 WRITE, ...;
UNLOCK TABLES;

Attention : LOCK TABLES commence par relâcher tous les verrous existants.

Ajouter des verrous aux endroits appropriés (il n’est pas toujours possible de choisir les endroits les plus appropriés : une limitation technique de MySQL empêche de poser des verrous à l’intérieur d’une procédure stockée). Lesquels des trois problèmes signalés précédemment sont-ils résolus ?

ALTER TABLE nom_table ENGINE=InnoDB;

Les transactions fonctionnent de la manière suivante :

– Le début d’une transaction est indiqué par l’ordre START TRANSACTION; – Tout ordre de mise à jour effectué à l’intérieur d’une transaction demande un verrou sur la ou les lignes correspondantes – Un verrou explicite peut être demandé sur une ligne donnée en ajoutant l’indication FOR UPDATE à la fin d’un ordre SELECT – Les verrous sont relâchés à la fin d’une transaction, soit par un ordre COMMIT qui valide la transaction, soit par un ordre ROLLBACK qui annule l’ensemble de la transaction. Une panne ou une erreur d’exécution ont le même effet qu’un ROLLBACK. Utiliser le système transactionnel de MySQL pour résoudre les trois incohérences constatées précédemment.

Le moteur InnoDB** possède en fait plusieurs modes de gestion des transactions :

Ces modes peuvent être choisis avant le début d’une transaction, avec l’ordre SET TRANSACTION ISOLATION LEVEL.

Pour comprendre les différences, expérimentez ces quatre modes. Quels avantages voyez-vous à chacun de ces modes ?