L’exemple
Notes préliminaires :
- Je simplifie au maximum les tables, l’important étant d’avoir une base pour comprendre. N’utilisez pas ça tel quel en production.
- Ce que je vous présente a été testé sur PostgreSQL version 10.6 et PHP 7.2.15.
Maintenant qu’on a vu ces petites remarques, voyons l’exemple qui nous servira à élaborer un rapport via une fonction stockée.
Imaginons un site web, qui propose des articles d’actualités. Chaque article n’a qu’un seul créateur, mais peut avoir par la suite plusieurs contributeur⋅rice⋅s. Les articles peuvent avoir différentes photos, et l’application stocke les visites faites pour chaque article.
Pour avoir un suivi de certains articles, un système de rapport a été envisagé. Celui-ci doit avoir pour chaque article demandé :
- les infos de base sur l’article lui-même ;
- les infos de base sur les modifications, qui a modifié un article et quand ;
- la personne qui a le plus contribué aux modifications de l’article ;
- savoir si, parmi les photos de l’article, il y en a une qui est utilisé dans au moins un autre article ;
- les différentes visites effectuées depuis le début du mois en cours.
Bien, ça fait pas mal de choses n’est-ce pas ? Alors commençons par créer les tables de contenus et de liaisons :
-- La table des AUTEUR⋅RICE⋅S
-- --------------------------
-- Oui on fait simple, juste ID, nom et prénom
DROP TABLE IF EXISTS authors CASCADE;
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
firstname TEXT,
lastname TEXT
);
-- La table des ARTICLES
-- ---------------------
-- Le scrict nécessaire : ID, titre, contenu et l’ID de la personne l’ayant
-- créé.
DROP TABLE IF EXISTS articles CASCADE;
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT DEFAULT '',
author_id INTEGER REFERENCES authors(id) NOT NULL
);
-- La table des PHOTOS
-- -------------------
-- Contient notamment le chemin vers l’image ainsi qu’un titre associé
DROP TABLE IF EXISTS photos CASCADE;
CREATE TABLE photos (
id SERIAL PRIMARY KEY,
file_path TEXT NOT NULL,
title TEXT DEFAULT ''
);
-- La table enregistrant les modification des ARTICLES
-- ---------------------------------------------------
-- Outre les ID de l’article et de l’auteur⋅rice, on y stocke aussi le moment
-- de la modification.
DROP TABLE IF EXISTS articles_authors;
CREATE TABLE articles_authors (
article_id INTEGER REFERENCES articles(id),
author_id INTEGER REFERENCES authors(id),
done_at TIMESTAMPTZ DEFAULT current_timestamp
);
-- La table de liaison ARTICLES/PHOTOS
-- -----------------------------------
DROP TABLE IF EXISTS articles_photos;
CREATE TABLE articles_photos (
article_id INTEGER REFERENCES articles(id),
photo_id INTEGER REFERENCES photos(id)
);
-- La table enregistrant les vues sur les ARTICLES
-- -----------------------------------------------
-- Il y a notamment l’IP du visiteur et le moment.
DROP TABLE IF EXISTS visitors CASCADE;
CREATE TABLE visitors (
id SERIAL PRIMARY KEY,
article_id INTEGER REFERENCES articles(id),
ip INET NOT NULL,
hit_at TIMESTAMPTZ DEFAULT current_timestamp
);Je vous avez prévenu, je reste simple sur les définitions de table, pas de contrainte, pas de déclencheur…
Bon, des tables, c’est bien, mais des tables avec des données, c’est mieux. Alors peuplons tout ça :
BEGIN;
-- Question bonus : ce sont des noms de femmes connues… Les connaissez-vous ?
INSERT INTO authors (firstname, lastname)
VALUES ('Ada', 'Lovelace'),
('Grace', 'Hopper'),
('Jean', 'Sammet'),
('Karen', 'Spärck Jones'),
('Mary Allen', 'Wilkes'),
('Margaret', 'Hamilton'),
('Sue', 'Gardner')
;
INSERT INTO articles (title, body, author_id)
VALUES (
'PostgreSQL, le SGBD qui rivalise avec Oracle',
'Lorem ipsum dolor sit amet',
1
),
(
'Utilisez les logiciels libres !',
'Sed porta et magna non tempor. Duis ornare lorem non ipsum consectetur suscipit.',
2
);
INSERT INTO photos (file_path, title)
VALUES (
'/some/path/to/image/1.webp',
'Un éléphant bleu'
),
(
'/some/path/to/image/2.webp',
'Un graphique avec des courbes'
),
(
'/some/path/to/image/3.webp',
'Photo d’un développeur heureux'
),
(
'/some/path/to/image/4.webp',
'Logo de PostgreSQL'
),
(
'/some/path/to/image/5.webp',
'Logo de Linux'
),
(
'/some/path/to/image/6.webp',
'Logo de la FSF'
),
(
'/some/path/to/image/7.webp',
'Photo de chat'
)
;
INSERT INTO articles_authors (article_id, author_id, done_at)
VALUES (1, 2, '2019-01-23 15:33:02'),
(1, 1, '2019-01-27 16:18:01'),
(1, 4, '2019-04-14 08:22:05'),
(1, 7, '2019-06-01 10:11:12'),
(2, 3, '2019-02-18 12:05:29'),
(1, 1, '2019-03-02 14:02:45'),
(2, 5, '2019-01-23 10:09:23'),
(2, 6, '2019-03-04 12:34:56'),
(2, 6, '2019-03-04 12:45:34'),
(2, 3, '2019-04-24 23:56:43'),
(2, 2, '2019-05-06 00:34:57'),
(2, 2, '2019-05-25 03:45:12');
-- Notez l’emploi en double de l’image avec l’ID 2
INSERT INTO articles_photos (article_id, photo_id)
VALUES (1, 1),
(1, 2),
(1, 3),
(1, 4),
(2, 2),
(2, 5),
(2, 6),
(2, 7);
-- Si vous testez les scripts de cet article de blog, alors faites attention,
-- pour avoir un résultat non vide dans la dernière section du rapport, il
-- vous faudra mettre d’autres dates plus proches de votre présent si vous
-- lisez ça quelques semaines après la publication de cet article.
INSERT INTO visitors (article_id, ip, hit_at)
VALUES (1, '157.97.180.252', '2019-11-28 20:21:23'),
(1, '164.48.79.238', '2019-11-29 22:02:32'),
(1, '93.50.86.47', '2019-11-30 13:34:56'),
(1, '38.111.247.114', '2019-12-01 10:11:12'),
(1, '89.120.108.142', '2019-12-03 10:33:44'),
(1, '247.212.104.147', '2019-12-03 11:02:03'),
(1, '216.138.234.120', '2019-12-03 12:30:32'),
(1, '170.96.214.104', '2019-12-04 07:17:05'),
(2, '156.119.148.244', '2019-11-28 20:24:04'),
(2, '135.219.138.131', '2019-11-30 07:43:03'),
(2, '68.152.127.172', '2019-12-02 07:59:59'),
(2, '159.220.241.7', '2019-12-02 08:15:16'),
(2, '247.75.172.136', '2019-12-02 09:01:18'),
(2, '48.210.225.110', '2019-12-04 23:24:25'),
(2, '95.139.142.102', '2019-12-04 23:34:23')
;
COMMIT;Pour nous aider dans notre tâche, créons quelques vues : elle serviront pour la fonction stockée, et, dans la vraie vie, pourront être utilisées pour d’autres besoins.
Généralement, ne vous privez pas de faire des vues dans vos bases de données. C’est toujours une bonne idée.
-- Vue listant les contributions faites sur les articles
DROP VIEW IF EXISTS v_contrib_on_article;
CREATE VIEW v_contrib_on_article AS
SELECT aa.done_at AS made_at,
CONCAT(a.firstname, ' ', UPPER(a.lastname)) AS name,
(a.id = ar.author_id) AS is_original_author,
ar.id AS article_id,
ar.title AS article_title
FROM authors AS a
LEFT JOIN articles_authors AS aa ON a.id = aa.author_id
LEFT JOIN articles AS ar ON aa.article_id = ar.id
LEFT JOIN authors AS a2 ON a2.id = ar.author_id
ORDER BY ar.id ASC, aa.done_at DESC;
-- Vue listant les visites reçue pour le mois en cours, triées par date
-- décroissante.
DROP VIEW IF EXISTS v_month_visit_on_article;
CREATE VIEW v_month_visit_on_article AS
SELECT *
FROM visitors
WHERE (
DATE(hit_at) > date_trunc('month', NOW())::DATE
OR
DATE(hit_at) = date_trunc('month', NOW())::DATE
)
ORDER BY hit_at DESC
;
-- Vue listant les contributions par article et par autrices, avec le nombre
-- d’interventions. Comprend aussi les créatrices des articles.
-- À utiliser dans un SELEC … LIMIT 1;
DROP VIEW IF EXISTS v_top_contrib_on_article;
CREATE VIEW v_top_contrib_on_article AS
SELECT article_id,
author_id,
CONCAT(firstname, ' ', UPPER(lastname)) AS name,
count(*) over(partition by CONCAT(article_id, ' ', author_id)) AS cnt
FROM
(
SELECT aa.article_id,
aa.author_id,
a.firstname,
a.lastname
FROM articles_authors AS aa LEFT JOIN authors AS a ON a.id = aa.author_id
UNION ALL
SELECT ar.id,
ar.author_id,
a.firstname,
a.lastname
FROM articles AS ar LEFT JOIN authors AS a ON a.id = ar.author_id
) AS top_contributors
ORDER BY article_id, cnt DESC
;La matière première étant là, créons la fonction maintenant ! Celle-ci va comporter des reférences à des curseurs en paramètres d’entrée, en plus de l’ID de l’article, afin de choisir éventuellement les parties du rapport à utiliser ensuite. Le fait d’avoir les curseurs en paramètres permet de les sélectionner facilement ensuite. Car on peut très bien utiliser des curseurs non nommés, mais au final, c’est moins pratique.
CREATE OR REPLACE FUNCTION article_report(
articleID INT,
infos refcursor,
contribs refcursor,
topContribs refcursor,
imgMore refcursor,
visits refcursor
)
RETURNS SETOF refcursor AS $$
BEGIN
-- Les infos de base de l’article dont on construit le rapport
OPEN infos FOR
SELECT articles.*, CONCAT(firstname, ' ', UPPER(lastname)) AS author_name
FROM articles
LEFT JOIN authors ON articles.author_id = authors.id
WHERE articles.id = @articleID;
RETURN NEXT infos;
-- Les contributions que l’articles a reçues
OPEN contribs FOR
SELECT made_at, name, is_original_author
FROM v_contrib_on_article
WHERE article_id = @articleID;
RETURN NEXT contribs;
-- La meilleure contributrice à l’article
OPEN topContribs FOR
SELECT name, cnt
FROM v_top_contrib_on_article
WHERE article_id = @articleID
LIMIT 1;
RETURN next topContribs;
-- Liste des éventuelles images en commun avec d’autres articles
OPEN imgMore FOR
SELECT *
FROM articles_photos AS ap
LEFT JOIN photos AS p ON ap.photo_id = p.id
WHERE photo_id IN(
SELECT photo_id
FROM articles_photos
WHERE article_id = @articleID
) AND article_id <> @articleID
;
RETURN next imgMore;
-- Les visites du mois
OPEN visits FOR
SELECT ip, hit_at
FROM v_month_visit_on_article
WHERE article_id = @articleID;
RETURN next visits;
RETURN;
END;
$$ language 'plpgsql';Nous avons absolument tout. Testons ! Par exemple avec l’article ayant l’ID 2 :
BEGIN;
-- notez que vous pouvez donner ce que vous voulez comme nom aux curseurs.
SELECT article_report(2, 'ci', 'cc', 'ct', 'cg', 'cv');
FETCH ALL IN "ci";
FETCH ALL IN "cc";
FETCH ALL IN "ct";
FETCH ALL IN "cg";
FETCH ALL IN "cv";
COMMIT;Et nous obtenons bien une sortie ! Voici ce que nous avons :
BEGIN
article_report
----------------
ci
cc
ct
cv
(4 rows)
id | title | body | author_id | author_name
----+---------------------------------+---------------------------+-----------+----------
2 | Utilisez les logiciels libres ! | Sed porta [...] suscipit. | 2 | Grace HOPPER
(1 row)
made_at | name | is_original_author
------------------------+-------------------+--------------------
2019-05-25 03:45:12+02 | Grace HOPPER | t
2019-05-06 00:34:57+02 | Grace HOPPER | t
2019-04-24 23:56:43+02 | Jean SAMMET | f
2019-03-04 12:45:34+01 | Margaret HAMILTON | f
2019-03-04 12:34:56+01 | Margaret HAMILTON | f
2019-02-18 12:05:29+01 | Jean SAMMET | f
2019-01-23 10:09:23+01 | Mary Allen WILKES | f
(7 rows)
name | cnt
--------------+-----
Grace HOPPER | 3
(1 row)
article_id | photo_id | id | file_path | title
------------+----------+----+----------------------------+-------------------------------
1 | 2 | 2 | /some/path/to/image/2.webp | Un graphique avec des courbes
(1 row)
ip | hit_at
----------------+------------------------
95.139.142.102 | 2019-12-04 23:34:23+01
48.210.225.110 | 2019-12-04 23:24:25+01
247.75.172.136 | 2019-12-02 09:01:18+01
159.220.241.7 | 2019-12-02 08:15:16+01
68.152.127.172 | 2019-12-02 07:59:59+01
(5 rows)
COMMITParfait ! On obtient bien le rapport que l’on souhaite via la base de données en natif. Dans l’ordre des sorties :
- les curseurs retournés par la fonction lors du
SELECT; - les informations de bases sur l’article concerné, avec son contenu, son titre, son ID, l’ID de son autrice et le nom complet de celle-ci ;
- la liste des modifications effectuées sur l’article, avec la date localisée, le nom de la personne et un booléen pour savoir si c’est l’autrice de l’article original ou non ;
- la personne ayant le plus contribué aux modifications de l’article ;
- la présence d’une image en commun avec au moins un autre article ;
- les différentes vues de l’article par date décroissante et avec l’IP du visiteur.
Comment récupérer cet ensemble de résultats via PDO
Le système de rapport est fonctionnel en base de donnée. Voyons comment nous allons récupérer un rapport via PHP.
Un mot tout d’abord : je vais montrer des exemples agnostiques, pas de framework particulier pour illustrer ça, je prends juste PHP/PDO natif brut de pomme.
D’après la documentation PDO, pour récupérer les différents jeux de résultats, il faut utiliser PDOStatement::nextRowset. Bien, testons.
// Données de connexion à adapter chez vous
// Et dans la vraie vie, ne mettez pas ça en dur dans le code.
$dsn = 'pgsql:dbname=mydb;host=127.0.0.1;port=5432';
$user = 'michel';
$password = 'MyP0Vv3rP4S5w0rD';
// dans la vraie vie®, c’est avec des paramètres
$sql = "SELECT article_report(1, 'ci', 'cc', 'ct', 'cg', 'cv');";
// Connexion DB
try {
$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connexion échouée : ' . $e->getMessage();
}
// Facultatif, j’aime bien que les données DB retournées
// par PDO soient sous la forme d’objets
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
// Facultatif, définit la façon dont on lève les erreurs
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// On exécute la requête
$stmt = $dbh->query($sql);
$i = 1;
do {
$rowset = $stmt->fetchAll(PDO::FETCH_NUM);
if ($rowset) {
// Permet d’afficher les résultats pour chaque set
var_dump("Set number $i");
array_map(function($row) {
array_map('var_dump', $row);
}, $rowset);
}
$i++;
} while ($stmt->nextRowset());Malheureusement, il y a un problème en exécutant ce script PHP :
PHP Fatal error: Uncaught PDOException: SQLSTATE[IM001]:
Driver does not support this function: driver does not support multiple rowsetsBon. Et avec PDO::ATTR_EMULATE_PREPARES ? Ça fonctionne ? Non, à Vrai comme à
Faux, on obtient la même erreur.
La réponse est simple : le driver PDO pour PostgreSQL ne supporte pas l’utilisation des curseurs multiples
La solution au problème
Mais heureusement, nous pouvons nous en sortir. Il ne faudra juste pas utiliser la solution fournie par défaut par PDO, il va falloir boucler, mais autrement, en allant chercher chaque curseur et en requêtant dessus, le tout dans une transaction.
Reprenons notre script afin d’exécuter notre SELECT article_report(2, 'ci', 'cc', 'ct', 'cg', 'cv');
// Données de connexion
$dsn = 'pgsql:dbname=mydb;host=127.0.0.1;port=5432';
$user = 'michel';
$password = 'MyP0Vv3rP4S5w0rD';
$sql = "SELECT article_report(:id, 'ci', 'cc', 'ct', 'cg', 'cv');";
$id = 2;
// Connexion DB
try {
$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connexion échouée : ' . $e->getMessage();
}
// Facultatif, j’aime bien que les données DB retournées
// par PDO soient sous la forme d’objets
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
// Facultatif, définit la façon dont on lève les erreurs
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$stmt = $dbh->prepare($sql);
// $id de l’article récupéré d’une façon X ou Y, ce n’est pas le sujet
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$cursors = $stmt->fetchAll();
$stmt->closeCursor();
$results = array();
foreach($cursors as $k => $v){
$stmt = $dbh->query('FETCH ALL IN "'. $v->article_report .'";');
$results[$k] = $stmt->fetchAll();
$stmt->closeCursor();
}
$dbh->commit();
// oui en JSON, pour avoir quelque chose de plus lisible
var_dump(json_encode($results, JSON_PRETTY_PRINT));Miracle, ça fonctionne puisqu’on obtient ça :
[
[
{
"id": 1,
"title": "PostgreSQL, le SGBD qui rivalise avec Oracle",
"body": "Lorem ipsum dolor sit amet",
"author_id": 1,
"author_name": "Ada LOVELACE"
}
],
[
{
"made_at": "2019-06-01 10:11:12+02",
"name": "Sue GARDNER",
"is_original_author": false
},
{
"made_at": "2019-04-14 08:22:05+02",
"name": "Karen SP\u00c4RCK JONES",
"is_original_author": false
},
{
"made_at": "2019-03-02 14:02:45+01",
"name": "Ada LOVELACE",
"is_original_author": true
},
{
"made_at": "2019-01-27 16:18:01+01",
"name": "Ada LOVELACE",
"is_original_author": true
},
{
"made_at": "2019-01-23 15:33:02+01",
"name": "Grace HOPPER",
"is_original_author": false
}
],
[
{
"name": "Ada LOVELACE",
"cnt": 3
}
],
[
{
"article_id": 2,
"photo_id": 2,
"id": 2,
"file_path": "\/some\/path\/to\/image\/2.webp",
"title": "Un graphique avec des courbes"
}
],
[
{
"ip": "170.96.214.104",
"hit_at": "2019-12-04 07:17:05+01"
},
{
"ip": "216.138.234.120",
"hit_at": "2019-12-03 12:30:32+01"
},
{
"ip": "247.212.104.147",
"hit_at": "2019-12-03 11:02:03+01"
},
{
"ip": "89.120.108.142",
"hit_at": "2019-12-03 10:33:44+01"
},
{
"ip": "38.111.247.114",
"hit_at": "2019-12-01 10:11:12+01"
}
]
]Testons avec l’autre ID :
[
[
{
"id": 2,
"title": "Utilisez les logiciels libres\u00a0!",
"body": "Sed porta et magna non tempor. Duis ornare lorem non ipsum consectetur suscipit.",
"author_id": 2,
"author_name": "Grace HOPPER"
}
],
[
{
"made_at": "2019-05-25 03:45:12+02",
"name": "Grace HOPPER",
"is_original_author": true
},
{
"made_at": "2019-05-06 00:34:57+02",
"name": "Grace HOPPER",
"is_original_author": true
},
{
"made_at": "2019-04-24 23:56:43+02",
"name": "Jean SAMMET",
"is_original_author": false
},
{
"made_at": "2019-03-04 12:45:34+01",
"name": "Margaret HAMILTON",
"is_original_author": false
},
{
"made_at": "2019-03-04 12:34:56+01",
"name": "Margaret HAMILTON",
"is_original_author": false
},
{
"made_at": "2019-02-18 12:05:29+01",
"name": "Jean SAMMET",
"is_original_author": false
},
{
"made_at": "2019-01-23 10:09:23+01",
"name": "Mary Allen WILKES",
"is_original_author": false
}
],
[
{
"name": "Grace HOPPER",
"cnt": 3
}
],
[
{
"article_id": 1,
"photo_id": 2,
"id": 2,
"file_path": "\/some\/path\/to\/image\/2.webp",
"title": "Un graphique avec des courbes"
}
],
[
{
"ip": "95.139.142.102",
"hit_at": "2019-12-04 23:34:23+01"
},
{
"ip": "48.210.225.110",
"hit_at": "2019-12-04 23:24:25+01"
},
{
"ip": "247.75.172.136",
"hit_at": "2019-12-02 09:01:18+01"
},
{
"ip": "159.220.241.7",
"hit_at": "2019-12-02 08:15:16+01"
},
{
"ip": "68.152.127.172",
"hit_at": "2019-12-02 07:59:59+01"
}
]
]Parfait ! On sait récupérer un rapport DB avec PHP !
Avantages de ce système
Le fait de construire un rapport le plus possible côté DB permet d’éviter les failles par injection SQL du côté PHP. C’est déjà un premier point intéressant.
Ensuite, une procédure stockée c’est court à appeler : il faut son nom et les éventuels paramètres. Toute la tartine de code n’est pas à faire côté PHP et à envoyer au SGBD, on diminue le trafic réseau et donc la latence de cette manière.
Bien sûr, c’est aussi du Do Not Repeat Yourself. C’est du code réutilisable.
Le SGBD utilise des systèmes de cache pour l’exécution de ses procédures (compilation des plans d’exécution).
Conclusion
Un SGBD n’est pas un sac dans lequel on balance des données qu’on pioche plus tard. Dans SGBD, il y a le mot Gestion. Les procédures stockées, comme les vues, les fonctions, les déclencheurs, etc. permettent de définir une logique d’utlisation de ses données.
La réalisation de rapport est une de ces logiques qui ont parfaitement leur place du côté de la base de données.
PostgreSQL fait partie de ces SGBD de niveau professionnel, avec des possibilités poussées. S’en passer serait dommage. Il faut penser à utiliser PHP (ou Python, ou C#, etc.) plus comme un passe-plat de données, et envisager le SGBD comme une sorte d’API fournissant des données.
Et si vous commenciez à coder votre première procédure de rapport aujourd’hui ?
