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 :

Logique business : coté 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 :

Logique business : coté API

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 :

Logique business : coté SGBDR

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 :

customer_id	name	surname	balance
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);

customer_id	name	surname	balance
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.

Il est donc assez difficile à récupérer et modifier une procédure stockée. Pour cette raison, certains développeurs ne les aiment pas :

• Pas très compatible avec la gestion de code moderne (ex. GIT)

• Difficile à distinguer les versions déployées

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.