Stored procedures
Stored procedures
Nous pouvons pré-coder des fonctions directement dans la base de données, qui se compose de plusieurs commandes SQL, ainsi que des transactions.
Quels avantages ?
La logique business est codée à un point central de la plateforme
Moins d’erreurs
Maintenance plus facile
Empêcher de l’accès direct aux tables par les utilisateurs et/ou APIS
Abstraire de la complexité
Contrôle d’accès et permissions
Imaginons le transfert d’argent dans une plateforme bancaire. La séquence de requêtes se trouve où ?
On peut, bien sûr, implémenter la logique dans le client :

En revanche, ceci est une très mauvaise idée :
Duplication X fois de la logique (pour chaque type de client qui existe) Si le règlement bancaire change ?
Il ne faut jamais faire confiance aux clients !
Logiquement, la plupart de la logique se trouvent plutôt en interne, dans nos APIs :

C'est plus sécurisé, mais, on a potentiellement les mêmes problèmes de maintenance. Il y a une duplication de la logique sur les différents APIs. Une solution potentielle est d'avoir un API monolithique, ou bien une librairie partagée entre toutes les implémentations.
Sinon, on peut héberger le logique business juste à côté des données :

Malgré l'API utilisé, la séquence nécessaire pour une opération reste inchangé. Et en plus, ni l'API ni le client voit les détails de l'opération.
Nous allons créer une procédure stockée qui agit sur plusieurs tables pour affecter un transfert d'argent. Voici le DDL pour les deux tables :
create table customer (
customer_id int not null auto_increment,
name varchar(256),
surname varchar(256),
balance decimal(10,2) not null default(0),
primary key (customer_id)
);
create table journal (
journal_id int not null auto_increment,
customer_id int not null,
amount decimal(10,2) not null default(0),
date timestamp not null,
primary key (journal_id),
foreign key (customer_id) references customer(customer_id)
);
insert into customer (name, surname, balance) values ("Kevin", "Glass", 5000);
insert into customer (name, surname, balance) values ("Pierre", "Laurendeau", 7000);
insert into customer (name, surname, balance) values ("Florence", "Perrousset", 3500);
On peut rédiger une fonction (en tant que stored procedure) qui peut être invoqué à tout moment, avec des paramètres, pour effectuer un transfert d'argent :
DELIMITER //
create procedure Transfer (amount decimal(10,2), from_id int, to_id int) modifies sql data
begin
declare exit handler for sqlexception
begin
rollback;
resignal;
end;
start transaction;
select @soldeFrom:=balance from customer where customer_id=from_id for update;
select @soldeTo:=balance from customer where customer_id=to_id for update;
set @soldeTo:=@soldeTo + amount;
set @soldeFrom:=@soldeFrom - amount;
if @soldeFrom < 0 then
signal sqlstate '45000' set message_text="Not enough available for transfer.";
end if;
update customer set balance = @soldeTo where customer_id=to_id;
update customer set balance = @soldeFrom where customer_id=from_id;
set @ts = CURRENT_TIMESTAMP();
insert into journal (customer_id, amount, date) values (to_id, amount, @ts);
insert into journal (customer_id, amount, date) values (from_id, -amount, @ts);
commit;
end;
//
DELIMITER ;
Un délimiteur temporaire
On commence notre procédure stockée par :
DELIMITER //
En fait, ici, on change le symbole reconnu par l'interprète SQL pour signaler la fin d'une commande. On le fait parce qu'on veut empêcher l'interprète d'interpréter les commandes qui se trouvent à l'intérieur de notre procédure qu'on est en train de rédiger !
Pour signaler la fin de déclaration de notre procédure, on évoque le délimiteur :
//
Une fois terminé, on rétablit le délimiteur :
DELIMITER ;
Commande pour la création d'une procédure
La procédure se crée avec une syntaxe précis :
create procedure NOM ( paramètres séparés par des virgules ) modifies sql data
begin
/** Les instructions de la procédure **/
end;
Après la déclaration d'une procédure, on peut l'invoquer à tout moment avec :
call NOM(... paramètres ...);
Gestion d'erreurs
Il faut toujours penser aux erreurs qui peuvent arriver. Nous commençons donc par préciser que faire en cas d'erreur :
declare exit handler for sqlexception
begin
rollback;
resignal;
end;
Dans cet exemple, on précise que dans le cas d'un sqlexception
, on fait d'abord un rollback
, puis on renvoie l'erreur plus haut dans le tas d'appels (avec resignal
).
Comment signaler manuellement une erreur ? Avec la commande signal
:
if @soldeFrom < 0 then
signal sqlstate '45000' set message_text="Not enough available for transfer.";
end if;
Le state 45000
est réservé aux erreurs personnalisées.
Invoquer une procédure stockée
Nous commençons avec les données suivantes :
1
Kevin
Glass
5000.00
2
Pierre
Laurendeau
7000.00
3
Florence
Perrousset
3500.00
On peut utiliser notre procédure afin d'effectuer un transfert de 2000 € de Kevin à Pierre :
call Transfer(2000, 1, 2);
1
Kevin
Glass
3000.00
2
Pierre
Laurendeau
9000.00
3
Florence
Perrousset
3500.00
Tout s'est bien passé !
Essayons avec une erreur :
call Transfer(5000, 1, 2);
ERROR 1644 (45000): Not enough available for transfer.
Lister, modifier une procédure
On peut récupérer la liste de procédures pour notre base de données (on filtre par base de données, car il y en a déjà d'autres qui existent dans le SGBDR) :
show procedure status where db = 'bank';
On peut récupérer le listing d'une procédure existante :
show create procedure Transfer;
On ne peut pas modifier directement les procédures. On est obligé de les supprimer et les remplacer avec une autre version.
drop procedure [nom];
Certains clients comme MySQL Workbench ont une interface qui facilite ces opérations.
Exercice
Vous êtes éditeur d’une solution de publicité en ligne qui va récompenser l’internaute de l’attention passée sur les pubs (exemple, le navigateur Brave).
Au cœur de votre solution est la logique business suivante :
Vous avez une base d’éditeurs partenaires (journaux, blogs, média, etc.). Chaque éditeur classe leurs contenus par une ou plusieurs catégories : sport, actualité, divertissement, science, politique, enfant
Un annonceur de pub prépaye un budget total pour une publicité. Pour chaque pub, l’annonceur précise le prix-par-vu qu’il est prêt à payer par chaque catégorie cible. Plus le prix est élevé, plus la pub aura la chance d’apparaître dans un site éditeur de ce genre.
Quand la pub apparaît sur le site d’un éditeur de contenu (journal, blog, etc.), le prix (correspondant à la catégorie) est déduit du budget total de l’annonceur :
75% est alloué au compte de l’éditeur
25% est alloué au compte de l’internaute
Créez le schéma pour votre solution, ainsi qu’une procédure SQL qui effectue le transfert de valeur au moment d’une vue sur une page web d’un éditeur.
Votre procédure doit être tolérante aux fautes ainsi que des requêtes concurrentes !
Attention : l’annonceur ne peut jamais avoir un solde négatif.
Chaque partie prenante doit être capable de récupérer son historique : ex. L’annonceur veut voir la répartition de dépense sur les éditeurs, l’éditeur veut voir la répartition des sources de revenu, l’internaute veut voir l’historique de pubs déjà vues.
Dernière mise à jour