Programmation, côté serveur

0. Introduction

Une fois que les données sont stockées dans une base de données abritée par un serveur SGBD, il faut manipuler les données.

On peut confier cette tâche aux applications clientes (par exemple Django). Mais pour beaucoup de choses, il est préférables que des opérations soient réalisées sur le SGBD même, pour des raisons d’une part d’efficacité, et d’autre part d’un meilleur contrôle de la cohérence des données.

Les vues

Une première façon de procéder est de créer des vues de la base, effectuent des calculs, et affichant de manière dynamique des valeurs calculées.

Une vue est une présentation permanente de données, construite à partir des données des tables de la base de données. Cela permet en particulier d’offir à différentes personnes, qui agissent toutes sur une même table, une porte d’entrée uniquement sur l’aspect des données qui les concernent.

Pour une documentation plus exhaustive que ce cours, on pourra se référer

à la documentation de postgreSQL
au site postgresqltutorial

Les procédures stockées et le langage de programmation PL/pgSQL

Pour aller plus loin il est nécessaire de disposer d’un langage de programmation procédural, et pas seulement déclaratif, comme l’est SQL.

PostgeSQL propose par défaut le langage PL/pgSQL, qui est une variante du langage PL/SQL de Oracle (Procedural Language/Structured Query Language). Il permet de définir des fonctions et des procédures stockées, auxquelles clients peuvent ensuite avoir accès pour manipuler des données. Pour cette partie, on pourra se référer

à "Introduction à PL-SQL" de D. Gonzalez
à la documentation de PostgreSQL
au site postgresqltutorial

On notera que PostgreSQL propose aussi des extensions permettant d’écrire des fonctions en d’autres langages (C, java, python, etc.), mais nous ne les regarderons pas ici.

Les déclencheurs (triggers)

On peut créer des déclencheurs, qui réalisent une action lorsque certains événements sont effectués sur la base. Par exemple, lorsqu’une nouvelle donnée est insérée dans une table, un déclencheur peut effectuer des actions sur d’autres enregistrements, voire sur d’autres tables. Cela permet un contrôle de la cohérence des données bien plus fin que ce que permet le mécanisme de contraintes provenant des clés étrangères ou des clauses CHECK.

On pourra consulter

la documentation PostgreSQL
le site postgresqltutorial

I. Les vues

Un premier exemple :

CREATE VIEW SalePerOrder AS
SELECT
orderNumber, SUM(quantityOrdered * priceEach) total
FROM
orderDetails
GROUP by orderNumber
ORDER BY total DESC;

On peut ensuite l’utiliser :

SELECT
*
FROM
salePerOrder;

Un deuxième exemple :

CREATE VIEW BigSalesOrder AS
SELECT
orderNumber, ROUND(total,2) as total
FROM
saleperorder
WHERE
total > 60000;

Une vue peut aussi être construite avec une jointure :

CREATE VIEW customerOrders AS
SELECT
d.orderNumber,
customerName,
SUM(quantityOrdered * priceEach) total
FROM
orderDetails d
INNER JOIN
orders o ON o.orderNumber = d.orderNumber
INNER JOIN
customers c ON c.customerNumber = c.customerNumber
GROUP BY d.orderNumber
ORDER BY total DESC;

Une vue peut aussi être modifiée. Soit la vue :

CREATE VIEW officeInfo
AS
SELECT officeCode, phone, city
FROM offices;

Quand on modifie un enregistrement

UPDATE officeInfo
SET
phone = ’+33 14 723 5555’
WHERE
officeCode = 4;

le contenu de la table d’origine (ici office) est modifié.

II. Le langage de programmation PL/pgSQL

Les blocs

L’unité de base est le bloc, qui est un objet de type texte (donc encadré par des apostrophes simples, ou mieux par le signe $$).

Voici un exemple

$$
BEGIN
INSERT INTO test.compte VALUES (25,36);
END
$$

La façon de base pour lancer un bloc est :

DO le_bloc ;

Ainsi, rentrer ceci dans psql

DO
$$
BEGIN
INSERT INTO test.compte VALUES (25,36);
END
$$ ;

est exactement la même chose que

INSERT INTO test.compte VALUES (25,36);

Le structure complète d’un bloc est :

$$
<<nom_block>> (1)
DECLARE  (2)
  variable1 type1 ;
  variable2 type2 := valeur ; (3)
BEGIN  (4)
   instruction1 ;
   ...
END nom_block  (4)
$$

1	Le label est optionnel
2	Toutes les variables utilisées dans le bloc doivent être déclarées, et éventuellement initialisées
3	Initialisation de la variable
4	Pas de point-virgule

Voici un exemple utilisant deux variables initialisées.

DO
$$
DECLARE
prenom VARCHAR(8) := 'François';
nom VARCHAR(6) := 'Ducrot';
BEGIN
RAISE INFO 'Bonjour % %', prenom, nom;  (1)
END
$$ ;

1	Notez l’usage des placeholders `%`, ainsi que de la commande `RAISE` qui affiche un message avec un niveau `DEBUG`, `LOG`, `INFO`, `NOTICE`, `WARNING`, et `EXCEPTION`. Seule la dernière déclanche une erreur.

Comprendre la commande :

DO $$
<<outer_block>>
DECLARE
  counter integer := 0;
BEGIN
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;

   DECLARE
       counter integer := 0;
   BEGIN
       counter := counter + 10;
       RAISE NOTICE 'The current value of counter in the subblock is %', counter;
       RAISE NOTICE 'The current value of counter in the outer block is %', outer_block.counter;
   END;

   RAISE NOTICE 'The current value of counter in the outer block is %', counter;
END outer_block $$;

et prévoir ce qu’elle va rendre.

Procédures et Fonctions

Un bloc tout seul n’a pas beaucoup d’intérêt. Il faut l’utiliser dans une procédure ou une fonction.

La syntaxe d’une procédure est :

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $$
    stored_procedure_body;
$$;

Une procédure s’appelle par la commande :

CALL procedure_name(parameter_list);

La syntaxe d’une fonction est :

CREATE [OR REPLACE] FUNCTION nom_fonction(
	variable1 type1,
	variable2 type2,
    ...)
RETURNS type_de_sortie AS $$
contenu_du_bloc
$$
LANGUAGE plpgsql;

Une fois une fonction céée, on peut y faire appel à l’intérieur d’une commande SQL, exactement de la même façon que les fonctions intégrées par défaut dans SQL.

Il y a quelques variantes concernant la façon de définir les variables d’entrée-sortie.

Quelques exemples :

CREATE FUNCTION somme(
	a INTEGER,
	b INTEGER)
RETURNS INTEGER AS $$
BEGIN
 	RETURN a+b ;
END;
$$
LANGUAGE plpgsql;

On peut l’appeler par

SELECT somme(1,2);

Une autre façon de faire la même chose :

CREATE OR REPLACE FUNCTION somme(
	IN a INTEGER,
	IN b INTEGER,
	OUT c INTEGER)
RETURNS INTEGER AS $$
BEGIN
 	c := a+b ;
END;
$$
LANGUAGE plpgsql;

Pour cet exercice, on s’inspirera des solutions proposées sur postgresqltutorial

On suppose ici que vous avez créé une table test.comptes sur le serveur PostgreSQL par une commande

------------- Création du schéma et de la table
drop schema if exists test cascade;

create schema test;
create table test.comptes(id INTEGER PRIMARY KEY, montant NUMERIC);

insert into test.comptes values(0,25.3);
insert into test.comptes values(1,265.35);
insert into test.comptes values(2,1000);
insert into test.comptes values(3,-35.3);
insert into test.comptes values(4,689.25);

Ecrire une fonction sans argument qui rend la somme des montants des comptes.

Ecrire une fonction prenant en argument un nombre s et rendant la liste des comptes et des montants correspondants, tels que ces montant soit supérieurs à s.

Ecrire une procédure prenant en argument un montant s et deux identifiants de comptes x et y et effectuant le tranfert de s de x à y et rendant une erreur si le montant de x n’est pas suffisant.

Mais on peut aussi utiliser des variables positionnelles :

Structures de contrôle

On retrouve des structures de contrôle semblables à celles de la plupart des langages de programmation.

Structure conditionnelle IF

IF condition-1 THEN
  if-statement;
ELSIF condition-2 THEN
  elsif-statement-2;
ELSE
  else-statement;
END IF:

Structure conditionnelle CASE

CASE
    WHEN boolean-expression-1 THEN
      statements
  [ WHEN boolean-expression-2 THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

Boucle infinie

<<label>>
LOOP
   Statements;
   EXIT [<<label>>] WHEN condition;
END LOOP;

Boucle Tant Que

[ <<label>> ]
WHILE condition LOOP
   statements;
END LOOP;

Boucle itérative

[ <<label>> ]
FOR loop_counter IN [ REVERSE ] depart..arrivee [ BY expression ] LOOP
    statements
END LOOP [ label ];

Ecrire une fonction fibonacci(n) rendant le n-ième nombre de Fibonacci.

III. Les déclencheurs (Trigger)

Règles actives (Event Condition Action : ECA)

On désigne par là une structure de règles dans les architectures pilotées par les événements, comme un SGBD. Un SGBD est soumis à des événements (création, insertion, modification, effacement ou destruction) ; on peut le programmer pour qu’il réagisse face à ces événements.

Le mécanisme des déclencheurs (triggers) est spécifique aux bases de données ; il permet de perfectionner le système des contraintes d’intégrité, déjà présentes dans la base. On peut citer quelques applications :

génération automatique de valeurs manquantes
éviter des modifications invalides
génération d’archives ou de logs
propagation de mises à jour
etc.

Les règles sont structurées en trois parties

La partie événement spécifie ce qui déclenche l’action.
La partie condition est un test logique qui évlue s’il y a lieu d’agir.
La partie action décrit les opérations effectuées sur les données.

Contrôle de l’exécution d’un trigger

Le moment de déclenchement du trigger par apport à l’événement

avant (BEFORE)
après (AFTER)
à la place de (INSTEAD OF) : ne fonctionne que pour les vues, et on n’en parlera pas ici.

Les modalités d’exécution

une exécution par enregistrement modifié (row trigger :`FOR EACH ROW`)
une seule exécution par événement déclenchant (statement trigger : FOR EACH STATEMENT)

L'accès aux données

les données avant l’événement OLD
les données une fois l’événement passé NEW

La syntaxe des triggers

CREATE [ OR REPLACE ] TRIGGER name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE } (1)
    ON table
    [ FROM referenced_table_name ]
    FOR  EACH { ROW | STATEMENT }  (2)
    [ WHEN ( condition ) ]  (3)
    EXECUTE PROCEDURE function_name ( arguments )  (4)

1	moment du déclenchement
2	modalité
3	condition éventuelle
4	action

Exemples

Pour chaque ordre de modification de la table account, on exécute au préalable une procédure de vérification.

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE PROCEDURE check_account_update();

On veut enregistrer dans une table externe toutes les modifications réellement effectuées :

CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.* )
    EXECUTE PROCEDURE log_account_update();

Un exemple détaillé

On suppose qu’on a créé deux tables

CREATE TABLE employees(
   id SERIAL PRIMARY KEY,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL
);
CREATE TABLE employee_audits (
   id SERIAL PRIMARY KEY,
   employee_id INT NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   changed_on TIMESTAMP(6) NOT NULL
);

On définit une fonction qui insère les modifications dans la table employee_audits :

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS trigger AS
$$
BEGIN
   IF NEW.last_name <> OLD.last_name THEN
       INSERT INTO employee_audits(employee_id,last_name,changed_on)
       VALUES(OLD.id,OLD.last_name,now());
   END IF;

   RETURN NEW;
END;
$$

et enfin, on crée un trigger :

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

Essayez de bien comprendre la strucure de ce trigger.
Testez le fonctionnement de ce trigger.
Dans le programme proposé, la condition d’exécution est écrite dans la procédure. Modifiez ceci, en mettant la condition dans le trigger.