SQL conception et mise en œuvre

Présentation du standard SQL


précédentsommairesuivant

V. Approfondissement SQL

V-A. Préambule

  • Aller encore plus loin :

    • tout en évitant le pire.
  • Appréhender de nouveaux objets :

    • fonctions utiles,
    • vues.
  • Utiliser des requêtes préparées.

Notre tour du SQL va se terminer. Il nous reste encore à voir des objets spéciaux, souvent utilisés, comme les vues et les procédures stockées. Nous allons aussi nous intéresser aux requêtes préparées. Nous terminerons avec une liste de recommandations pour éviter une mauvaise utilisation du SQL.

V-A-1. Menu

  • Fonctions de base.
  • Vues.
  • Requêtes préparées.
  • Index.
  • Ce qu’il ne faut pas faire.

V-A-2. Objectifs

  • Utiliser des objets avancés.
  • Gagner en performances.
  • Éviter les pièges les plus fréquents.

V-B. Fonctions de base

  • Transtypage.
  • Manipulation de chaînes.
  • Manipulation de types numériques.
  • Manipulation de dates.
  • Génération de jeu de données.

PostgreSQL propose un nombre important de fonctions permettant de manipuler les différents types de données disponibles. Les étudier de façon exhaustive n’est pas l’objet de ce module. Néanmoins, le manuel de PostgreSQL établit une liste complète des fonctions disponibles dans le SGBD.

V-B-1. Transtypage

  • Conversion d’un type de données vers un autre type de données.
  • CAST (expression AS type).
  • expression::type.

Les opérateurs de transtypage permettent de convertir une donnée d’un type particulier vers un autre type. La conversion échoue si les types de données sont incompatibles.

Exemples

Transtypage incorrect d’une chaîne de caractères vers un entier :

 
Sélectionnez
1.
2.
3.
SELECT 'toto'::integer;
ERROR:  invalid input syntax for integer: "toto"
LINE 1: SELECT 'toto'::integer;

V-B-2. Opérations simples sur les chaînes

  • Concaténation : chaîne1 || chaîne2.
  • Longueur de la chaîne : char_length(chaîne).
  • Conversion en minuscules : lower(chaîne).
  • Conversion en majuscules : upper(chaîne).

L’opérateur de concaténation permet de concaténer deux chaînes de caractères :

 
Sélectionnez
SELECT 'Bonjour' || ', Monde!';
-----------------
 Bonjour, Monde!

Il permet aussi de concaténer une chaîne de caractères avec d’autres types de données :

 
Sélectionnez
SELECT 'Texte ' || 1::integer;
---------
 Texte 1

La fonction char_length() permet de connaître la longueur d’une chaîne de caractères :

 
Sélectionnez
SELECT char_length('Texte' || 1::integer);
-------------
           6

Les fonctions lower et upper permettent de convertir une chaîne respectivement en minuscules et en majuscules :

 
Sélectionnez
SELECT lower('Bonjour, Monde!');
-----------------
 bonjour, monde!

SELECT upper('Bonjour, Monde!');
-----------------
 BONJOUR, MONDE!

V-B-3. Manipulations de chaînes

  • Extrait une chaîne à partir d’une autre : substring(chaîne [from int] [for int]).
  • Emplacement d’une sous-chaîne : position(sous-chaîne in chaîne).

La fonction substring permet d’extraire une chaîne de caractères à partir d’une chaîne en entrée. Il faut lui indiquer, en plus de la chaîne source, la position de départ, et la longueur de la sous-chaîne. Par exemple :

 
Sélectionnez
1.
2.
3.
4.
5.
SELECT substring('Bonjour, Monde' from 5 for 4);
 substring
-----------
 our,
(1 row)

Notez que vous pouvez aussi utiliser un appel de fonction plus standard :

 
Sélectionnez
SELECT substring('Bonjour, Monde', 5, 4);
 substring
-----------
 our,
(1 row)

La fonction position indique la position d’une chaîne de caractères dans la chaîne indiquée. Par exemple :

 
Sélectionnez
SELECT position (',' in 'Bonjour, Monde');
 position
----------
        8
(1 row)

La combinaison des deux est intéressante :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT version();
                            version
-------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) ...
(1 row)

SELECT substring(version() from 1 for position(' on' in version()));
    substring
-----------------
 PostgreSQL 10.0

V-B-4. Manipulation de types numériques

  • Opérations arithmétiques.
  • Manipulation de types numériques.
  • Génération de données.

V-B-5. Opérations arithmétiques

  • Addition : +.
  • Soustraction : -.
  • Multiplication : *.
  • Division : /.
  • Reste (modulo) : %.

L’ensemble des opérations arithmétiques disponibles sont documentées dans le manuel.

V-B-6. Fonctions numériques courantes

  • Arrondi : round(numeric).
  • Troncature : trunc(numeric [, precision]).
  • Entier le plus petit : floor(numeric).
  • Entier le plus grand : ceil(numeric).

Ces fonctions sont décrites dans le manuel.

V-B-7. Génération de données

  • Générer une suite d’entiers : generate_series(borne_debut, borne_fin, intervalle).
  • Générer un nombre aléatoire : random().

La fonction generate_series(n, m) est spécifique à PostgreSQL et permet de générer une suite d’entiers compris entre une borne de départ et une borne de fin, en suivant un certain intervalle :

 
Sélectionnez
SELECT generate_series(1, 4);
 generate_series
-----------------
               1
               2
               3
               4
(4 rows)

La déclinaison generate_series(n, m, interval) permet de spécifier un incrément pour chaque itération :

 
Sélectionnez
SELECT generate_series(1, 10, 4);
 generate_series
-----------------
               1
               5
               9
(3 rows)

Quant à la fonction random(), elle génère un nombre aléatoire, de type numeric, compris entre 0 et 1.

 
Sélectionnez
SELECT random();
      random
-------------------
 0.381810061167926
(1 row)

Pour générer un entier compris entre 0 et 100, il suffit de réaliser la requête suivante :

 
Sélectionnez
SELECT round(100*random())::integer;
 round
-------
    74
(1 row)

Il est possible de contrôler la graine du générateur de nombres aléatoires en positionnant le paramètre de session SEED :

SET SEED = 0.123;

ou à l’aide de la fonction setseed() :

SELECT setseed(0.123);

La graine est un flottant compris entre -1 et 1.

Ces fonctions sont décrites dans le manuel de PostgreSQL.

V-B-8. Manipulation de dates

  • Obtenir la date et l’heure courante.
  • Manipuler des dates.
  • Opérations arithmétiques.
  • Formatage de données.

V-B-9. Date et heure courante

  • Retourne la date courante : current_date.
  • Retourne l’heure courante : current_time.
  • Retourne la date et l’heure courante : current_timestamp.

Les fonctions current_date et current_time permettent d’obtenir respectivement la date courante et l’heure courante. La première fonction retourne le résultat sous la forme d’un type date et la seconde sous la forme d’un type time with time zone.

La fonction current_timestamp et son synonyme now() permettent d’obtenir la date et l’heure courante, le résultat étant de type timestamp with time zone.

Les fonctions current_date, current_time et current_timestamp n’ont pas besoin d’être invoquées avec les parenthèses ouvrantes et fermantes typiques de l’appel d’une fonction. En revanche, l’appel de la fonction now() requiert ces parenthèses.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SELECT current_date;
 current_date
--------------
 2017-10-04
(1 row)

SELECT current_time;
    current_time
--------------------
 16:32:47.386689+02
(1 row)

SELECT current_timestamp;
       current_timestamp
-------------------------------
 2017-10-04 16:32:50.314897+02
(1 row)

SELECT now();
              now
-------------------------------
 2017-10-04 16:32:53.684813+02
(1 row)

Il est possible d’utiliser ces variables comme valeur par défaut d’une colonne :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  dateheure_creation TIMESTAMP DEFAULT current_timestamp,
  valeur VARCHAR);

INSERT INTO test (valeur) VALUES ('Bonjour, monde!');

SELECT * FROM test;
 id |     dateheure_creation     |     valeur
----+----------------------------+-----------------
  1 | 2017-10-04 16:33:46.961088 | Bonjour, monde!
(1 row)

V-B-10. Manipulation des données

  • Âge :

    • par rapport à la date courante : age(timestamp),
    • par rapport à une date de référence : age(timestamp, timestamp).

La fonction age(timestamp) permet de déterminer l’âge de la date donnée en paramètre par rapport à la date courante. L’âge sera donné sous la forme d’un type interval.

La forme age(timestamp, timestamp) permet d’obtenir l’âge d’une date par rapport à une autre date, par exemple pour connaître l’âge de Gaston Lagaffe au 5 janvier 1997 :

 
Sélectionnez
SELECT age(date '1997-01-05', date '1957-02-28');
           age
-------------------------
 39 years 10 mons 5 days

V-B-11. Tronquer et extraire

  • Troncature d’une date : date_trunc(text, timestamp).
  • Exemple : date_trunc('month' from date_naissance).
  • Extrait une composante de la date : extract(text, timestamp).
  • Exemple : extract('year' from date_naissance).

La fonction date_trunc(text, timestamp) permet de tronquer la date à une précision donnée. La précision est exprimée en anglais, et autorise les valeurs suivantes :

  • microseconds ;
  • milliseconds ;
  • second ;
  • minute ;
  • hour ;
  • day ;
  • week ;
  • month ;
  • quarter ;
  • year ;
  • decade ;
  • century ;
  • millennium.

La fonction date_trunc() peut agir sur une donnée de types timestamp, date ou interval. Par exemple, pour arrondir l’âge de Gaston Lagaffe de manière à ne représenter que le nombre d’années :

 
Sélectionnez
1.
2.
3.
4.
5.
SELECT date_trunc('year',
    age(date '1997-01-05', date '1957-02-28')) AS age_lagaffe;
 age_lagaffe
-------------
 39 years

La fonction extract(text from timestamp) permet d’extraire uniquement une composante donnée d’une date, par exemple l’année. Elle retourne un type de données flottant double precision.

 
Sélectionnez
1.
2.
3.
4.
5.
SELECT extract('year' from
    age(date '1997-01-05', date '1957-02-28')) AS age_lagaffe;
 age_lagaffe
-------------
          39

V-B-12. Arithmétique sur les dates

  • Opérations arithmétiques sur timestamp, time ou date :

    • date/time - date/time = interval,
    • date/time + time = date/time,
    • date/time + interval = date/time.
  • Opérations arithmétiques sur interval :

    • interval * numeric = interval,
    • interval / numeric = interval,
    • interval + interval = interval.

La soustraction de deux types de données représentant des dates permet d’obtenir un intervalle qui représente le délai écoulé entre ces deux dates :

 
Sélectionnez
1.
2.
3.
4.
SELECT timestamp '2012-01-01 10:23:10' - date '0001-01-01' AS soustraction;
     soustraction
----------------------
 734502 days 10:23:10

L’addition entre deux types de données est plus restreinte. En effet, l’expression de gauche est obligatoirement de type timestamp ou date et l’expression de droite doit être obligatoirement de type time. Le résultat de l’addition permet d’obtenir une donnée de type timestamp, avec ou sans information sur le fuseau horaire selon que cette information est présente ou non sur l’expression de gauche.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT timestamp '2001-01-01 10:34:12' + time '23:56:13' AS addition;
      addition
---------------------
 2001-01-02 10:30:25

SELECT date '2001-01-01' + time '23:56:13' AS addition;
      addition
---------------------
 2001-01-01 23:56:13

L’addition d’une donnée datée avec une donnée de type interval permet d’obtenir un résultat du même type que l’expression de gauche :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT timestamp with time zone '2001-01-01 10:34:12' +
    interval '1 day 1 hour' AS addition;
        addition
------------------------
 2001-01-02 11:34:12+01

SELECT date '2001-01-01'  + interval '1 day 1 hour' AS addition;
      addition
---------------------
 2001-01-02 01:00:00

SELECT time '10:34:24' + interval '1 day 1 hour' AS addition;
 addition
----------
 11:34:24

Une donnée de type interval peut subir des opérations arithmétiques. Le résultat sera de type interval :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT interval '1 day 1 hour' * 2 AS multiplication;
 multiplication
-----------------
 2 days 02:00:00

SELECT interval '1 day 1 hour' / 2 AS division;
 division
----------
 12:30:00

SELECT interval '1 day 1 hour' + interval '2 hour' AS addition;
    addition
----------------
 1 day 03:00:00

SELECT interval '1 day 1 hour' - interval '2 hour' AS soustraction;
  soustraction
-----------------
 1 day -01:00:00

V-B-13. Date vers chaîne

  • Conversion d’une date en chaîne de caractères : to_char(timestamp, text).
  • Exemple : to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS').

La fonction to_char() permet de restituer une date selon un format donné :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT current_timestamp;
       current_timestamp
-------------------------------
 2017-10-04 16:35:39.321341+02

SELECT to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS');
       to_char
---------------------
 04/10/2017 16:35:43

V-B-14. Chaîne vers date

  • Conversion d’une chaîne de caractères en date : to_date(text, text) to_date('05/12/2000', 'DD/MM/YYYY').
  • Conversion d’une chaîne de caractères en timestamp : to_timestamp(text, text) to_timestamp('05/12/2000 12:00:00', 'DD/MM/YYYY HH24:MI:SS') .
  • Paramètre datestyle.

Quant à la fonction to_date(), elle permet de convertir une chaîne de caractères dans une donnée de type date. La fonction to_timestamp() permet de réaliser la même chose, mais en donnée de type timestamp.

 
Sélectionnez
1.
2.
3.
4.
SELECT to_timestamp('04/12/2000 12:00:00', 'DD/MM/YYYY HH24:MI:SS');
to_timestamp
------------------------
 2000-12-04 12:00:00+01

Ces fonctions sont détaillées dans la section concernant les fonctions de formatage de données du manuel.

Le paramètre DateStyle contrôle le format de saisie et de restitution des dates et heures. La documentation de ce paramètre permet de connaître les différentes valeurs possibles. Il reste néanmoins recommandé d’utiliser les fonctions de formatage de date qui permettent de rendre l’application indépendante de la configuration du SGBD.

La norme ISO impose le format de date “année/mois/jour”. La norme SQL est plus permissive et permet de restituer une date au format “jour/mois/année” si DateStyle est égal à 'SQL, DMY'.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SET datestyle = 'ISO, DMY';

SELECT current_timestamp;
              now
-------------------------------
 2017-10-04 16:36:38.189973+02

SET datestyle = 'SQL, DMY';

SELECT current_timestamp;
              now
---------------------------------
 04/10/2017 16:37:04.307034 CEST

V-B-15. Génération de données

  • Générer une suite de timestamps : generate_series(timestamp_debut, timestamp_fin, intervalle)

La fonction generate_series(date_debut, date_fin, interval) permet de générer des séries de dates :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT generate_series(date '2012-01-01',date '2012-12-31',interval '1 month');
    generate_series
------------------------
 2012-01-01 00:00:00+01
 2012-02-01 00:00:00+01
 2012-03-01 00:00:00+01
 2012-04-01 00:00:00+02
 2012-05-01 00:00:00+02
 2012-06-01 00:00:00+02
 2012-07-01 00:00:00+02
 2012-08-01 00:00:00+02
 2012-09-01 00:00:00+02
 2012-10-01 00:00:00+02
 2012-11-01 00:00:00+01
 2012-12-01 00:00:00+01
(12 rows)

V-C. Vues

  • Tables virtuelles :

    • définies par une requête SELECT,
    • définition stockée dans le catalogue de la base de données.
  • Objectifs :

    • masquer la complexité d’une requête,
    • masquer certaines données à l’utilisateur.

Les vues sont des tables virtuelles qui permettent d’obtenir le résultat d’une requête SELECT. Leur définition est stockée dans le catalogue système de la base de données.

De cette façon, il est possible de créer une vue à destination de certains utilisateurs pour combler différents besoins :

  • permettre d’interroger facilement une vue qui exécute une requête complexe, lourde à écrire et utilisée fréquemment ;
  • masquer certaines lignes ou certaines colonnes aux utilisateurs, pour amener un niveau de sécurité complémentaire ;
  • rendre les données plus intelligibles, en nommant mieux les colonnes d’une vue et/ou en simplifiant la structure de données.

En plus de cela, les vues permettent d’obtenir facilement des valeurs dérivées d’autres colonnes. Ces valeurs dérivées pourront alors être utilisées simplement en appelant la vue plutôt qu’en réécrivant systématiquement le calcul de dérivation à chaque requête qui le nécessite.

V-C-1. Création d’une vue

  • Une vue porte un nom au même titre qu’une table :

    • elle sera nommée avec les mêmes règles.
  • Ordre de création d’une vue : CREATE VIEW vue (colonne ...) AS SELECT ....

Bien qu’une vue n’ait pas de représentation physique directe, elle est accédée au même titre qu’une table avec SELECT et dans certains cas avec INSERT, UPDATE et DELETE. La vue logique ne distingue pas les accès à une vue des accès à une table. De cette façon, une vue doit utiliser les mêmes conventions de nommage qu’une table.

Une vue est créée avec l’ordre SQL CREATE VIEW :

 
Sélectionnez
1.
2.
3.
4.
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW nom
    [ ( nom_colonne [, ...] ) ]
    [ WITH ( nom_option_vue [= valeur_option_vue] [, ... ] ) ]
    AS requete

Le mot-clé CREATE VIEW permet de créer une vue. Si elle existe déjà, il est possible d’utiliser CREATE OR REPLACE VIEW qui aura pour effet de créer la vue si elle n’existe pas ou de remplacer la définition de la vue si elle existe déjà. Attention, les colonnes et les types de données retournés par la vue ne doivent pas changer.

La clause nom permet de nommer la vue. La clause nom_colonne, ... permet lister explicitement les colonnes retournées par une vue ; cette clause est optionnelle, mais recommandée pour mieux documenter la vue.

La clause requete correspond simplement à la requête SELECT exécutée lorsqu’on accède à la vue.

Exemples :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE phone_data (person text, phone text, private boolean);

CREATE VIEW phone_number (person, phone) AS
    SELECT person, CASE WHEN NOT private THEN phone END AS phone
    FROM phone_data;

GRANT SELECT ON phone_number TO secretary;

V-C-2. Lecture d’une vue

  • Une vue est lue comme une table :

    • SELECT * FROM vue; .

Une vue est lue de la même façon qu’une table. On utilisera donc l’ordre SELECT pour le faire. L’optimiseur de PostgreSQL remplacera l’appel à la vue par la définition de la vue pendant la phase de réécriture de la requête. Le plan d’exécution prendra alors compte des particularités de la vue pour optimiser les accès aux données.

Exemples :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE TABLE phone_data (person text, phone text, private boolean);

CREATE VIEW phone_number (person, phone) AS
    SELECT person, CASE WHEN NOT private THEN phone END AS phone
    FROM phone_data;

INSERT INTO phone_data (person, phone, private)
  VALUES ('Titi', '0123456789', true);

INSERT INTO phone_data (person, phone, private)
  VALUES ('Rominet', '0123456788', false);

SELECT person, phone FROM phone_number;
 person  |   phone
---------+------------
 Titi    |
 Rominet | 0123456788
(2 rows)

V-C-3. Sécurisation d’une vue

  • Sécuriser une vue :

    • droits avec GRANT et REVOKE.
  • Utiliser les vues comme moyen de filtrer les lignes est dangereux :

    • option security_barrier.

Il est possible d’accorder (ou de révoquer) à un utilisateur les mêmes droits sur une vue que sur une table :

 
Sélectionnez
1.
2.
3.
4.
5.
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] nom_table [, ...]
         | ALL TABLES IN SCHEMA nom_schéma [, ...] }
    TO { [ GROUP ] nom_rôle | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Le droit SELECT autorise un utilisateur à lire une table. Les droits INSERT, UPDATE et DELETE permettent de contrôler les accès en mise à jour à une vue.

Les droits TRUNCATE et REFERENCES n’ont pas d’utilité avec une vue. Ils ne sont tout simplement pas supportés, car TRUNCATE n’agit que sur une table et une clé étrangère ne peut être liée d’une table qu’à une autre table.

Les vues sont parfois utilisées pour filtrer les lignes pouvant être lues par l’utilisateur. Cette protection peut être contournée si l’utilisateur a la possibilité de créer une fonction. À partir de PostgreSQL 9.2, l’option security_barrier permet d’éviter ce problème.

Exemples :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
postgres=# CREATE TABLE elements (id serial, contenu text, prive boolean);
CREATE TABLE
postgres=# INSERT INTO elements (contenu, prive)
VALUES ('a', false), ('b', false), ('c super prive', true),
       ('d', false), ('e prive aussi', true);
INSERT 0 5
postgres=# SELECT * FROM elements;
 id |    contenu    | prive
----+---------------+-------
  1 | a             | f
  2 | b             | f
  3 | c super prive | t
  4 | d             | f
  5 | e prive aussi | t
(5 rows)

La table elements contient cinq lignes, trois considérées comme privées. Nous allons donc créer une vue permettant de ne voir que les lignes publiques.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
postgres=# CREATE OR REPLACE VIEW elements_public AS
SELECT * FROM elements
WHERE CASE WHEN current_user='postgres' THEN TRUE ELSE NOT prive END;
CREATE VIEW
postgres=# SELECT * FROM elements_public;
 id |    contenu    | prive
----+---------------+-------
  1 | a             | f
  2 | b             | f
  3 | c super prive | t
  4 | d             | f
  5 | e prive aussi | t
(5 rows)

postgres=# CREATE USER u1;
CREATE ROLE
postgres=# GRANT SELECT ON elements_public TO u1;
GRANT
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> SELECT * FROM elements;
ERROR:  permission denied for relation elements
postgres=> SELECT * FROM elements_public ;
 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f
(3 rows)

L’utilisateur u1 n’a pas le droit de lire directement la table elements, mais a le droit d’y accéder via la vue elements_public, uniquement pour les lignes dont le champ prive est à false.

Avec une simple fonction, cela peut changer :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
postgres=> CREATE OR REPLACE FUNCTION abracadabra(integer, text, boolean)
RETURNS bool AS $$
BEGIN
RAISE NOTICE '% - % - %', $ 1, $ 2, $ 3;
RETURN true;
END$$
LANGUAGE plpgsql
COST 0.0000000000000000000001;
CREATE FUNCTION
postgres=> SELECT * FROM elements_public WHERE abracadabra(id, contenu, prive);
NOTICE:  1 - a - f
NOTICE:  2 - b - f
NOTICE:  3 - c super prive - t
NOTICE:  4 - d - f
NOTICE:  5 - e prive aussi - t
 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f
(3 rows)

Que s’est-il passé ? pour comprendre, il suffit de regarder l’EXPLAIN de cette requête :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
postgres=> EXPLAIN SELECT * FROM elements_public
WHERE abracadabra(id, contenu, prive);
                                                        QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on elements  (cost=0.00..28.15 rows=202 width=37)
   Filter: (abracadabra(id, contenu, prive) AND
           CASE WHEN ("current_user"() = 'u1'::name)
                THEN (NOT prive) ELSE true END)
(2 rows)

La fonction abracadabra a un coût si faible que PostgreSQL l’exécute avant le filtre de la vue. Du coup, la fonction voit toutes les lignes de la table.

Seul moyen d’échapper à cette optimisation du planificateur, utiliser l’option security_barrier en 9.2 :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE OR REPLACE VIEW elements_public WITH (security_barrier) AS
SELECT * FROM elements
WHERE CASE WHEN current_user='postgres' THEN true ELSE NOT prive END;

CREATE VIEW
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> SELECT * FROM elements_public WHERE abracadabra(id, contenu, prive);
NOTICE:  1 - a - f
NOTICE:  2 - b - f
NOTICE:  4 - d - f
 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f
(3 rows)

postgres=> EXPLAIN SELECT * FROM elements_public WHERE
abracadabra(id, contenu, prive);
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Subquery Scan on elements_public  (cost=0.00..34.20 rows=202 width=37)
   Filter: abracadabra(elements_public.id, elements_public.contenu,
           elements_public.prive)
   ->  Seq Scan on elements  (cost=0.00..28.15 rows=605 width=37)
         Filter: CASE WHEN ("current_user"() = 'u1'::name)
                 THEN (NOT prive) ELSE true END
(4 rows)

Voir aussi cet article de blog.

V-C-4. Mise à jour des vues

  • Trigger INSTEAD OF.
  • Updatable view (PostgreSQL 9.3).

La mise à jour des vues était impossible auparavant sans programmation complémentaire.

Depuis PostgreSQL 9.3, le moteur gère lui-même la possibilité de mettre à jour des vues simples. Les critères permettant de déterminer si une vue peut être mise à jour ou non sont assez simples à résumer : la vue doit reprendre la définition de la table avec éventuellement une clause WHERE pour restreindre les résultats. Attention néanmoins, la clause WITH CHECK OPTION n’est pas encore supportée par PostgreSQL. L’absence de support de cette clause permet à l’utilisateur d’insérer des données qui ne satisfont pas les critères de filtrage de la vue. Par exemple, il est possible d’insérer un numéro de téléphone privé alors que la vue ne permet pas d’afficher les numéros privés.

Pour gérer les cas plus complexes, PostgreSQL permet de créer des triggers INSTEAD OF sur des vues. Cette fonctionnalité est disponible depuis la version 9.1. Une alternative est d’utiliser le système de règles (RULES), mais cette pratique est peu recommandée du fait de la difficulté de débogage et de la maintenance que cela entraîne.

Un trigger INSTEAD OF permet de déclencher une fonction utilisateur lorsqu’une opération de mise à jour est déclenchée sur une vue. Le code de la fonction sera exécuté en lieu et place de la mise à jour.

Exemples :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
CREATE TABLE phone_data (person text, phone text, private boolean);


CREATE VIEW maj_phone_number (person, phone, private) AS
    SELECT person, phone, private
      FROM phone_data
     WHERE private = false;


CREATE VIEW phone_number (person, phone) AS
    SELECT person, CASE WHEN NOT private THEN phone END AS phone
      FROM phone_data;


INSERT INTO phone_number VALUES ('Titi', '0123456789');
ERROR:  cannot insert into column "phone" of view "phone_number"
DETAIL:  View columns that are not columns of their base relation are not updatable.
HINT:  To make the view insertable, provide an unconditional ON INSERT
       DO INSTEAD rule or an INSTEAD OF INSERT trigger.


INSERT INTO maj_phone_number VALUES ('Titi', '0123456789', false);


CREATE OR REPLACE FUNCTION phone_number_insert_row()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $function$
BEGIN
  INSERT INTO phone_data (person, phone, private)
    VALUES (NEW.person, NEW.phone, false);
  RETURN NEW;
END;
$function$;


CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON phone_number
    FOR EACH ROW
    EXECUTE PROCEDURE phone_number_insert_row();


INSERT INTO phone_number VALUES ('Rominet', '0123456788');


SELECT * FROM phone_number;
 person  |   phone
---------+------------
 Titi    | 0123456789
 Rominet | 0123456788
(2 rows)

V-C-5. Mauvaises utilisations

  • Prolifération des vues :

    • créer une vue doit se justifier,
    • ne pas créer une vue par table.

La création d’une vue doit être pensée préalablement et doit se justifier du point de vue de l’application ou d’une règle métier. Toute vue créée doit être documentée, au moins en plaçant un commentaire sur la vue .

Bien qu’une vue n’ait pas de représentation physique, elle occupe malgré tout de l’espace disque. En effet, le catalogue système comporte une entrée pour chaque vue créée, autant d’entrées qu’il y a de colonnes à la vue, etc. Trop de vues entraînent donc malgré tout l’augmentation de la taille du catalogue système, donc une empreinte mémoire plus importante, car ce catalogue reste en général systématiquement présent en cache.

V-D. Requêtes préparées

  • Exécution en deux temps :

    • préparation du plan d’exécution de la requête,
    • exécution de la requête en utilisant le plan préparé.
  • Objectif :

    • éviter simplement les injections SQL,
    • améliorer les performances.

Les requêtes préparées, aussi appelées requêtes paramétrées, permettent de séparer la phase de préparation du plan d’exécution de la phase d’exécution. Le plan d’exécution qui est alors généré est générique, car les paramètres de la requête sont inconnus à ce moment-là.

L’exécution est ensuite commandée par l’application, en passant l’ensemble des valeurs des paramètres de la requête. De plus, ces paramètres sont passés de façon à éviter les injections SQL.

L’exécution peut être ensuite commandée plusieurs fois, sans avoir à préparer le plan d’exécution. Cela permet un gain important en termes de performances, car l’étape d’analyse syntaxique et de recherche du plan d’exécution optimal n’est plus à faire.

L’utilisation de requêtes préparées peut toutefois être contre-performante si les sessions ne sont pas maintenues et les requêtes exécutées une seule fois. En effet, l’étape de préparation oblige à un premier aller-retour entre l’application et la base de données et l’exécution oblige à un second aller- retour, ajoutant ainsi une surcharge qui peut devenir significative.

V-D-1. Utilisation

  • PREPARE, préparation du plan d’exécution d’une requête.
  • EXECUTE, passage des paramètres de la requête et exécution réelle.
  • L’implémentation dépend beaucoup du langage de programmation utilisé :

    • le connecteur JDBC supporte les requêtes préparées,
    • le connecteur PHP/PDO également.

L’ordre PREPARE permet de préparer le plan d’exécution d’une requête. Le plan d’exécution prendra en compte le contexte courant de l’utilisateur au moment où la requête est préparée, et notamment le search_path. Tout changement ultérieur de ces variables ne sera pas pris en compte à l’exécution.

L’ordre EXECUTE permet de passer les paramètres de la requête et de l’exécuter.

La plupart des langages de programmation mettent à disposition des méthodes qui permettent d’employer les mécanismes de préparation de plans d’exécution directement. Les paramètres des requêtes seront alors transmis un à un à l’aide d’une méthode particulière.

Voici comment on prépare une requête :

 
Sélectionnez
PREPARE req1 (text) AS
    SELECT person, phone FROM phone_number WHERE person = $1;

Le test suivant montre le gain en performance qu’on peut attendre d’une requête préparée :

  • Préparation de la table :
 
Sélectionnez
1.
2.
3.
CREATE TABLE t1 (c1 integer primary key, c2 text);
INSERT INTO t1 select i, md5(random()::text)
FROM generate_series(1, 1000000) AS i;
  • Préparation de deux scripts SQL, une pour les requêtes standards, l’autre pour les requêtes préparées :
 
Sélectionnez
1.
2.
3.
4.
5.
$ for i in $(seq 1 100000); do
    echo "SELECT * FROM t1 WHERE c1=$i;";
done > requetes_std.sql
echo "PREPARE req AS SELECT * FROM t1 WHERE c1=\$1;" > requetes_prep.sql
for i in $(seq 1 100000); do echo "EXECUTE req($i);"; done >> requetes_prep.sql
  • Exécution du test (deux fois pour s’assurer que les temps d’exécution sont réalistes) :
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
$ time psql -f requetes_std.sql postgres >/dev/null

real  0m12.742s
user  0m2.633s
sys 0m0.771s
$ time psql -f requetes_std.sql postgres >/dev/null

real  0m12.781s
user  0m2.573s
sys 0m0.852s
$ time psql -f requetes_prep.sql postgres >/dev/null

real  0m10.186s
user  0m2.500s
sys 0m0.814s
$ time psql -f requetes_prep.sql postgres >/dev/null

real  0m10.131s
user  0m2.521s
sys 0m0.808s

Le gain est de 16 % dans cet exemple. Il peut être bien plus important. En lisant 500000 lignes (et non pas 100000), on arrive à 25 % de gain.

V-E. Indexation

  • Comment gagner en performances.
  • Index :

    • représentation valeur / pointeur,
    • arbre de valeurs.

Quand on cherche à récupérer une ou plusieurs valeurs sur une très grosse table, il serait dommage de devoir lire toutes les lignes de la table. Un peu comme lorsqu’on cherche un nom dans un annuaire, avoir une table des matières indiquant à quelle page se trouvent les noms commençant par telle ou telle lettre permet de trouver plus rapidement le nom recherché.

Un index est donc tout simplement ça : un moyen rapide de trouver l’information recherchée. Il est construit généralement sous la forme d’un arbre, car c’est la forme la plus aboutie pour trouver rapidement une valeur. Lorsque la valeur est trouvée, le pointeur vers la table est disponible et permet de ne lire que le (ou les) bloc(s) concerné(s) de la table.

V-E-1. Créer un index

 
Sélectionnez
1.
2.
3.
4.
5.
6.
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ]
  ON table_name
  ( { column_name | ( expression ) }
    [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
  [ TABLESPACE tablespace_name ]
  [ WHERE predicate ]

Le plus simple moyen de créer un index est d’indiquer la ou les colonnes à indexer et le nom de la table. Voici un exemple :

CREATE INDEX i_c1_t1 on t1(c1);

Cependant, il existe beaucoup d’autres options. Il est possible d’indiquer l’ordre des valeurs (ascendant ou descendant), ainsi que l’emplacement des valeurs NULL.

V-E-2. Lecture avec index

  • L’index permet une recherche plus rapide.
  • Il n’y a besoin de lire que quelques blocs :

    • et non pas la table entière.

L’index contient des informations redondantes. Il contient uniquement les données disponibles dans la tête. Son intérêt n’est donc pas ce qu’il stocke, mais la façon dont il le fait. Le stockage réalisé par l’index est optimisé pour la recherche.

L’exemple suivant montre la rapidité pour trouver un élément dans une table en contenant un million, avec un index et sans index :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
CREATE TABLE t1 (c1 integer, c2 text);
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;

\timing
Timing is on.

select * from t1 where c1=500000;
   c1   |      c2
--------+--------------
 500000 | Ligne 500000
(1 row)

Time: 109.569 ms

select * from t1 where c1=500000;
   c1   |      c2
--------+--------------
 500000 | Ligne 500000
(1 row)

Time: 109.386 ms

CREATE INDEX ON t1(c1);

SELECT * FROM t1 WHERE c1=500000;
   c1   |      c2
--------+--------------
 500000 | Ligne 500000
(1 row)

Time: 0.675 ms

Donc il faut 100 millisecondes pour trouver une ligne parmi un million sans index, et 0,7 milliseconde avec un index. Le gain est appréciable.

V-E-3. Insertion avec index

  • Accélère la recherche :

    • mais ralentit les écritures.
  • Une écriture dans la table doit mettre à jour l’index.
  • Faire attention à ne pas créer des index inutiles.

Voici un exemple montrant la différence de durée d’exécution d’une insertion d’un million de lignes sur une table sans index, puis avec un index :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
DROP TABLE t1;
CREATE TABLE t1 (c1 integer, c2 text);
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;
Time: 2761.236 ms

DROP TABLE t1;
CREATE TABLE t1 (c1 integer, c2 text);
CREATE INDEX ON t1(c1);
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;
Time: 5401.505 ms

On passe ici du simple au double. Il est donc essentiel pour les performances de faire attention aux index créés. Oui, ils permettent de gagner en performances lors des lectures, mais les écritures sont forcément ralenties. La création d’un index doit se faire en ayant pris ceci en considération.

V-E-4. Tri avec un index

  • Un index ne sert pas qu’à la recherche.
  • Les données étant triées, il sert aussi à accélérer les tris.

Voici un exemple montrant la différence de durée d’exécution d’un tri sur un million de lignes sur une table sans index, puis avec un index :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
\o /dev/null
DROP TABLE t1;
CREATE TABLE t1 (c1 integer, c2 text);
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;
\timing

SELECT * FROM t1 ORDER BY c1;
Time: 618.203 ms

CREATE INDEX ON t1(c1);
SELECT * FROM t1 ORDER BY c1;
Time: 359.369 ms

Dans cet exemple, le tri est deux fois plus rapide avec un index. Il est donc tout à fait concevable d’ajouter un index uniquement pour satisfaire un tri.

V-E-5. Index partiel

  • N’indexer qu’une partie des données.
  • Requête typique :SELECT * FROM taches WHERE fait IS false ORDER BY date_rendu; .
  • Index typique : CREATE INDEX ON taches(date_rendu) WHERE fait IS false;.
  • L’index indiqué ici permet de réaliser le filtre et le tri en un seul parcours d’index. Il aidera fortement cette requête. En revanche, il y a peu de chances que cet index soit utile pour d’autres requêtes.

L’autre intérêt de cet index concerne sa volumétrie sur disque. Sur cette requête typique, il clair que, plus le temps passe, et plus il y aura de tâches réalisées. Du coup, même si la table grossit rapidement, l’index devrait rester à la même volumétrie. En tout cas, il grossira beaucoup moins que la table.

V-E-6. Index fonctionnel

  • Indexer le résultat d’une expression.
  • Fonctions immuables seulement.
  • Requête typique : SELECT * FROM employes WHERE upper(nom)='DUPONT';.
  • Index typique : CREATE INDEX ON employes(upper(nom));.

L’idée de ce type d’index est de satisfaire les requêtes qui font une recherche sur le résultat d’un calcul ou d’une fonction.

Dans la table suivante :

CREATE TABLE employe(id serial, nom text, prenom text);

si on exécute la requête de recherche sur le nom, comme :

SELECT * FROM employes WHERE nom ILIKE 'DUPONT';

ou comme :

SELECT * FROM employes WHERE upper(nom)='DUPONT';

PostgreSQL doit faire un parcours séquentiel, car il ne peut pas utiliser un index sur nom. Il faut utiliser la deuxième variante de la recherche et créer l’index, non pas sur la colonne nom, mais sur le résultat de la fonction upper sur la colonne nom.

V-F. Ce qu’il ne faut pas faire

  • Modélisation.
  • Écriture de requêtes.
  • Conception de l’application.

Cette partie présente différents problèmes fréquemment rencontrés et leurs solutions.

V-F-1. Absence de contraintes

  • Concerne surtout les clés étrangères.
  • Parfois (souvent?) ignorées pour diverses raisons :

    • performances,
    • flexibilité du modèle de données,
    • compatibilité avec d’autres SGBD,
    • héritage de MySQL/MyISAM,
    • commodité de développement.
  • Conséquences :

    • problèmes d’intégrité des données,
    • procédures de vérification de cohérence des données.

Les contraintes d’intégrités et notamment les clés étrangères sont parfois absentes des modèles de données. Les problématiques de performances et de flexibilité sont souvent mises en avant alors que les contraintes sont justement une aide pour l’optimisation de requêtes par le planificateur et empêchent des incohérences dans les données.

Bien que cela puisse sembler être une bonne idée en début de projet, l’absence de ces contraintes va poser des problèmes d’intégrité des données au fur et à mesure de l’évolution des besoins et du code. Les données seront très certainement manipulées de façon différente par la suite, par différentes applications, entraînant ainsi de réels problèmes d’intégrité des données. Mais surtout, du point de vue applicatif, il est très difficile, si ce n’est impossible, d’empêcher deux écritures concurrentes qui mèneraient à une incohérence (cas d’une Race Condition). Imaginez le scénario suivant :

  • La transaction x1 s’assure que la donnée est présente dans la table t1.
  • La transaction x2 supprime la donnée précédente dans la table t1.
  • La transaction x1 insère une ligne dans la table t2 faisant référence à la ligne de t1 qu’elle pense encore présente.

Ce cas est très facilement gérable pour un moteur de base de données si une clé étrangère existe. Redévelopper ces mêmes contrôles dans la couche applicative sera toujours plus coûteux en termes de performances, voire impossible à faire dans certains cas sans passer par la base de données elle-même (multiples serveurs applicatifs accédant à la même base de données).

Il peut s’en suivre des calculs d’agrégats faux et des problèmes applicatifs de toutes sortes. Souvent, plutôt que de corriger le modèle de données, des procédures de vérification de la cohérence des données seront mises en place, entraînant ainsi un travail supplémentaire pour trouver et corriger les incohérences.

L’absence de clés étrangères et plus généralement de contraintes d’intégrité entraîne dans le temps une charge de travail supplémentaire. Il est bien préférable de mieux concevoir le modèle de données dès le début avec les bonnes contraintes d’intégrité.

Parfois, les clés étrangères sont supprimées simplement parce que des transactions sont en erreur, car des données sont insérées dans une table fille sans avoir alimenté la table mère. Des identifiants de clés étrangères de la table fille sont absents de la table mère, entraînant l’arrêt en erreur de la transaction. Il est possible de contourner cela en différant la vérification des contraintes d’intégrité à la fin de la transaction.

L’exemple ci-dessous montre l’utilisation de la vérification des contraintes d’intégrité en fin de transaction.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE mere (id integer, t text);
CREATE TABLE fille (id integer, mere_id integer, t text);
ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);
ALTER TABLE fille
  ADD CONSTRAINT fk_mere_fille
      FOREIGN KEY (mere_id)
      REFERENCES mere (id)
         MATCH FULL
         ON UPDATE NO ACTION
         ON DELETE CASCADE
         DEFERRABLE;

La transaction insère d’abord les données dans la table fille, puis ensuite dans la table mère :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
BEGIN TRANSACTION;
SET CONSTRAINTS all deferred;

INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');

INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');

COMMIT;

V-F-2. Stockage EAV

  • Entité-Attribut-Valeur.
  • Pourquoi :

    • flexibilité du modèle de données,
    • adapter sans délai ni surcoût le modèle de données.
  • Conséquences :

    • création d’une table : identifiant, nom_attribut, valeur,
    • requêtes abominables et coûteuses.
  • Solutions :

    • revenir sur la conception du modèle de données,
    • utiliser un type de données plus adapté (hstore, jsonb).

Le modèle relationnel a été critiqué depuis sa création pour son manque de souplesse pour ajouter de nouveaux attributs ou pour proposer plusieurs attributs sans pour autant nécessiter de redévelopper l’application.

La solution souvent retenue est d’utiliser une table à tout faire entité-attribut-valeur qui est associée à une autre table de la base de données. Techniquement, une telle table comporte trois colonnes. La première est un identifiant généré permet de référencer la table mère. Les deux autres colonnes stockent le nom de l’attribut et la valeur représentés.

Ainsi, pour reprendre l’exemple des informations de contacts pour un individu, une table personnes permet de stocker un identifiant de personne. Une table personne_attributs permet d’associer des données à un identifiant de personne. Le type de données de la colonne est souvent prévu largement pour faire tenir tout type d’informations, mais sous forme textuelle. Les données ne peuvent donc pas être validées.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
CREATE TABLE personnes (id SERIAL PRIMARY KEY);

CREATE TABLE personne_attributs (
  id_pers INTEGER NOT NULL,
  nom_attr varchar(20) NOT NULL,
  val_attr varchar(100) NOT NULL
);

INSERT INTO personnes (id) VALUES (nextval('personnes_id_seq')) RETURNING id;
 id
----
  1

INSERT INTO personne_attributs (id_pers, nom_attr, val_attr)
  VALUES (1, 'nom', 'Prunelle'),
         (1, 'prenom', 'Léon');
(...)

Un tel modèle peut sembler souple, mais pose plusieurs problèmes. Le premier concerne l’intégrité des données. Il n’est pas possible de garantir la présence d’un attribut comme on le ferait avec une contrainte NOT NULL. Si l’on souhaite stocker des données dans un autre format qu’une chaîne de caractères, pour bénéficier des contrôles de la base de données sur ce type, la seule solution est de créer autant de colonnes d’attributs qu’il y a de types de données à représenter. Ces colonnes ne permettront pas d’utiliser des contraintes CHECK pour garantir la cohérence des valeurs stockées avec ce qui est attendu, car les attributs peuvent stocker n’importe quelle donnée.

Les requêtes SQL qui permettent de récupérer les données requises dans l’application sont également particulièrement lourdes à écrire et à maintenir, à moins de récupérer les données attribut par attribut.

Des problèmes de performances vont très rapidement se poser. Cette représentation des données entraîne souvent l’effondrement des performances d’une base de données relationnelle. Les requêtes sont difficilement optimisables et nécessitent de réaliser beaucoup d’entrées-sorties disque, au contraire d’une table, jointe éventuellement à d’autres tables, où les accès par index permettent de minimiser les entrées-sorties.

Lorsque de telles solutions sont déployées pour stocker des données transactionnelles, il vaut mieux revenir à un modèle de données traditionnel qui permet de typer correctement les données, de mettre en place les contraintes d’intégrité adéquates et d’écrire des requêtes SQL efficaces.

Dans d’autres cas, il vaut mieux utiliser un type de données de PostgreSQL qui est approprié, comme hstore ou jsonb, qui permet de stocker des données sous la forme clé->valeur. Le type de données hstore existe depuis longtemps dans PostgreSQL et a pour principal défaut de ne pouvoir représenter que des chaînes de caractères. Le type jsonb est une nouveauté de PostgreSQL 9.4 et permet de représenter des documents JSON, en les stockant dans un format interne optimisé et indexable. Ces types de données peuvent être indexés pour garantir de bons temps de réponse pour les requêtes qui nécessitent des recherches sur certaines clés ou certaines valeurs.

Voici l’exemple précédent revu avec l’extension hstore :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
CREATE EXTENSION hstore;
CREATE TABLE personnes (id SERIAL PRIMARY KEY, attributs hstore);

INSERT INTO personnes (attributs) VALUES ('nom=>Prunelle, prenom=>Léon');
INSERT INTO personnes (attributs) VALUES ('prenom=>Gaston,nom=>Lagaffe');
INSERT INTO personnes (attributs) VALUES ('nom=>DeMaesmaker');

SELECT * FROM personnes;
 id |              attributs
----+--------------------------------------
  1 | "nom"=>"Prunelle", "prenom"=>"Léon"
  2 | "nom"=>"Lagaffe", "prenom"=>"Gaston"
  3 | "nom"=>"DeMaesmaker"
(3 rows)

SELECT id, attributs->'prenom' AS prenom FROM personnes;
 id |  prenom
----+----------
  1 | Léon
  2 | Gaston
  3 |
(3 rows)

SELECT id, attributs->'nom' AS nom FROM personnes;
 id |     nom
----+-------------
  1 | Prunelle
  2 | Lagaffe
  3 | DeMaesmaker
(3 rows)

V-F-3. Attributs multicolonnes

  • Pourquoi ?

    • stocker plusieurs attributs pour une même ligne,
    • exemple : les différents numéros de téléphone d’une personne.
  • Pratique courante :

    • ajouter plusieurs colonnes portant le même nom.
  • Conséquences :

    • et s’il faut rajouter encore une colonne ?
    • maîtrise de l’unicité des valeurs,
    • requêtes complexes à maintenir.
  • Solutions :

    • créer une table de dépendance,
    • utiliser un type tableau.

Dans certains cas, le modèle de données doit être étendu pour pouvoir stocker des données complémentaires. Un exemple typique est une table qui stocke les informations pour contacter une personne. Une table personnes ou contacts possède une colonne telephone qui permet de stocker le numéro de téléphone d’une personne. Or, une personne peut disposer de plusieurs numéros. Le premier réflexe est souvent de créer une seconde colonne telephone_2 pour stocker un numéro de téléphone complémentaire. S’en suit une colonne telephone_3, voire telephone_4 en fonction des besoins.

Dans de tels cas, les requêtes deviennent plus complexes à maintenir et il est difficile de garantir l’unicité des valeurs stockées pour une personne, car l’écriture des contraintes d’intégrité devient de plus en plus complexe au fur et à mesure que l’on ajoute une colonne pour stocker un numéro.

La solution la plus pérenne pour gérer ce cas de figure est de créer une table de dépendance qui est dédiée au stockage des numéros de téléphone. Ainsi, la table personnes ne portera plus de colonnes telephone, mais une table telephones portera un identifiant référençant une personne et un numéro de téléphone. Ainsi, si une personne dispose de plusieurs numéros de téléphone, la table telephones comportera autant de lignes qu’il y a de numéros pour une personne.

Les différents numéros de téléphone seront obtenus par jointure entre la table personnes et la table telephones. L’application se chargera de l’affichage.

Ci-dessous, un exemple d’implémentation du problème où une table telephones dans laquelle plusieurs numéros seront stockés sur plusieurs lignes plutôt que dans plusieurs colonnes.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE personnes (
  per_id SERIAL PRIMARY KEY,
  nom VARCHAR(50) NOT NULL,
  pnom VARCHAR(50) NOT NULL,
  ...
);

CREATE TABLE telephones (
  per_id INTEGER NOT NULL,
  numero VARCHAR(20),
  PRIMARY KEY (per_id, numero),
  FOREIGN KEY (per_id) REFERENCES personnes (per_id)
);

L’unicité des valeurs sera garantie à l’aide d’une contrainte d’unicité posée sur l’identifiant per_id et le numéro de téléphone.

Une autre solution consiste à utiliser un tableau pour représenter cette information. D’un point de vue conceptuel, le lien entre une personne et son ou ses numéros de téléphone est plus une « composition » qu’une réelle « relation » : le numéro de téléphone ne nous intéresse pas en tant que tel, mais uniquement en tant que détail d’une personne. On n’accédera jamais à un numéro de téléphone séparément : la table telephones donnée plus haut n’a pas de clé « naturelle », un simple rattachement à la table personnes par l’id de la personne. Sans même parler de partitionnement, on gagnerait donc en performances en stockant directement les numéros de téléphone dans la table personnes, ce qui est parfaitement faisable sous PostgreSQL :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
CREATE TABLE personnes (
  per_id SERIAL PRIMARY KEY,
  nom VARCHAR(50) NOT NULL,
  pnom VARCHAR(50) NOT NULL,
  numero VARCHAR(20)[]
);

-- Ajout d'une personne
INSERT INTO personnes (nom, pnom, numero)
     VALUES ('Simpson', 'Omer', '{0607080910}');

SELECT *
  FROM personnes;
 per_id |   nom   | pnom |    numero
--------+---------+------+--------------
      1 | Simpson | Omer | {0607080910}
(1 ligne)

-- Ajout d'un numéro de téléphone pour une personne donnée :
UPDATE personnes
   SET numero = numero || '{0102030420}'
 WHERE per_id = 1;

-- Vérification de l'ajout :
SELECT *
  FROM personnes;
 per_id |   nom   | pnom |         numero
--------+---------+------+-------------------------
      1 | Simpson | Omer | {0607080910,0102030420}

-- Séparation des éléments du tableau :
SELECT per_id, nom, pnom, unnest(numero) AS numero
  FROM personnes;
 per_id |   nom   | pnom |   numero
--------+---------+------+------------
      1 | Simpson | Omer | 0607080910
      1 | Simpson | Omer | 0102030420
(2 lignes)

V-F-4. Choix des types de données

  • Objectif :

    • représenter des valeurs décimales.
  • Pratique courante :

    • utiliser le type float ou double.
  • Problèmes :

    • les types flottants ne stockent qu’une approximation de la valeur représentée,
    • les erreurs d’arrondis se multiplient,
    • les requêtes produisent des résultats faux.
  • Solutions :

    • privilégier le type numeric(x, y) pour les calculs précis (financiers notamment).

Certaines applications scientifiques se contentent de types flottants standards, car ils permettent d’encoder des valeurs plus importantes que les types entiers standards. Néanmoins, les types flottants sont peu précis, notamment pour les applications financières où une erreur d’arrondi n’est pas envisageable.

Exemple montrant une perte de précision dans les calculs :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
test=# CREATE TABLE comptes (compte_id SERIAL PRIMARY KEY, solde FLOAT);
CREATE TABLE

test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2),
(100000000000000.1);
INSERT 0 4

test=# SELECT SUM(solde) FROM comptes;
       sum
-----------------
 100000100010010
(1 row)

L’utilisation du type numeric permet d’éviter la perte de précision :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
test=# CREATE TABLE comptes (compte_id SERIAL PRIMARY KEY, solde NUMERIC);
CREATE TABLE

test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2),
(100000000000000.1);
INSERT 0 4

test=# SELECT SUM(solde) FROM comptes;
        sum
-------------------
 100000100010010.5
(1 row)

V-F-5. Problèmes courants d’écriture de requêtes

  • Utilisation de NULL.
  • Ordre implicite des colonnes.
  • Requêtes spaghetti.
  • Moteur de recherche avec LIKE.

Le langage SQL est généralement méconnu, ce qui amène à l’écriture de requêtes peu performantes, voire peu pérennes.

V-F-6. Ordre implicite des colonnes

  • Objectif :

    • s’économiser d’écrire la liste des colonnes dans une requête.
  • Problèmes :

    • si l’ordre des colonnes change, les résultats changent,
    • résultats faux,
    • données corrompues.
  • Solutions :

    • nommer les colonnes impliquées.

Le langage SQL permet de s’appuyer sur l’ordre physique des colonnes d’une table. Or, faire confiance à la base de données pour conserver cet ordre physique peut entraîner de graves problèmes applicatifs en cas de changements. Dans le meilleur des cas, l’application ne fonctionnera plus, ce qui permet d’éviter les corruptions de données silencieuses, où une colonne prend des valeurs destinées normalement à être stockées dans une autre colonne. Si l’application continue de fonctionner, elle va générer des résultats faux et des incohérences d’affichage.

C’est pourquoi, il est préférable de lister explicitement les colonnes dans les ordres INSERT et SELECT, afin de garder un ordre d’insertion déterministe. Par exemple, l’ordre des colonnes peut changer notamment lorsque certains ETL sont utilisés pour modifier le type d’une colonne varchar(10) en varchar(11). Par exemple, pour la colonne username, l’ETL Kettle génère les ordres suivants :

 
Sélectionnez
1.
2.
3.
4.
ALTER TABLE utilisateurs ADD COLUMN username_KTL VARCHAR(11);
UPDATE utilisateurs SET username_KTL=username;
ALTER TABLE utilisateurs DROP COLUMN username;
ALTER TABLE utilisateurs RENAME username_KTL TO username

Il génère des ordres SQL inutiles et consommateurs d’entrées/sorties disques, car il doit générer des ordres SQL compris par tous les SGBD du marché. Or, tous les SGBD ne permettent pas de changer le type d’une colonne aussi simplement que dans PostgreSQL.

Exemples :

Exemple de modification du schéma pouvant entraîner des problèmes d’insertion si les colonnes ne sont pas listées explicitement :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
CREATE TABLE insere (id integer PRIMARY KEY, col1 varchar(5), col2 integer);

INSERT INTO insere VALUES (1, 'XX', 10);

SELECT * FROM insere ;
 id | col1 | col2
----+------+------
  1 | XX   |   10

ALTER TABLE insere ADD COLUMN col1_tmp varchar(6);
UPDATE insere SET col1_tmp = col1;
ALTER TABLE insere DROP COLUMN col1;
ALTER TABLE insere RENAME COLUMN col1_tmp TO col1;

INSERT INTO insere VALUES (2, 'XXX', 10);
ERROR:  invalid input syntax for integer: "XXX"
LINE 1: INSERT INTO insere VALUES (2, 'XXX', 10);
                                      ^

SELECT * FROM insere ;
 id | col2 | col1
----+------+------
  1 |   10 | XX

V-F-7. Code spaghetti

Le problème est similaire à tout autre langage :

  • Code spaghetti pour le SQL :

    • écriture d’une requête à partir d’une autre requête,
    • ou évolution d’une requête au fil du temps avec des ajouts.
  • Vite ingérable :

    • ne pas hésiter à reprendre la requête à zéro, en repensant sa sémantique,
    • souvent, un changement de spécification est un changement de sens, au niveau relationnel, de la requête,
    • ne pas la patcher !

Image non disponible

Un exemple (sous Oracle) :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
SELECT  Article.datem                          AS Article_1_9,
    Article.degre_alcool                   AS Article_1_10,
    Article.id                             AS Article_1_19,
    Article.iddf_categor                   AS Article_1_20,
    Article.iddp_clsvtel                   AS Article_1_21,
    Article.iddp_cdelist                   AS Article_1_22,
    Article.iddf_cd_prix                   AS Article_1_23,
    Article.iddp_agreage                   AS Article_1_24,
    Article.iddp_codelec                   AS Article_1_25,
    Article.idda_compo                     AS Article_1_26,
    Article.iddp_comptex                   AS Article_1_27,
    Article.iddp_cmptmat                   AS Article_1_28,
    Article.idda_articleparent             AS Article_1_29,
    Article.iddp_danger                    AS Article_1_30,
    Article.iddf_fabric                    AS Article_1_33,
    Article.iddp_marqcom                   AS Article_1_34,
    Article.iddp_nomdoua                   AS Article_1_35,
    Article.iddp_pays                      AS Article_1_37,
    Article.iddp_recept                    AS Article_1_40,
    Article.idda_unalvte                   AS Article_1_42,
    Article.iddb_sitecl                   AS Article_1_43,
    Article.lib_caisse                     AS Article_1_49,
    Article.lib_com                        AS Article_1_50,
    Article.maj_en_attente                 AS Article_1_61,
    Article.qte_stk                        AS Article_1_63,
    Article.ref_tech                       AS Article_1_64,
    1                                      AS Article_1_70,
    CASE
      WHEN (SELECT COUNT(MA.id)
            FROM   da_majart MA
                   join da_majmas MM
                     ON MM.id = MA.idda_majmas
                   join gt_tmtprg TMT
                     ON TMT.id = MM.idgt_tmtprg
                   join gt_prog PROG
                     ON PROG.id = TMT.idgt_prog
            WHERE  idda_article = Article.id
               AND TO_DATE(TO_CHAR(PROG.date_lancement, 'DDMMYYYY')
                           || TO_CHAR(PROG.heure_lancement, ' HH24:MI:SS'),
                            'DDMMYYYY HH24:MI:SS') >= SYSDATE) >= 1 THEN 1
      ELSE 0
    END                                           AS Article_1_74,
    Article.iddp_compnat                          AS Article_2_0,
    Article.iddp_modven                           AS Article_2_1,
    Article.iddp_nature                           AS Article_2_2,
    Article.iddp_preclin                          AS Article_2_3,
    Article.iddp_raybala                          AS Article_2_4,
    Article.iddp_sensgrt                          AS Article_2_5,
    Article.iddp_tcdtfl                           AS Article_2_6,
    Article.iddp_unite                            AS Article_2_8,
    Article.idda_untgrat                          AS Article_2_9,
    Article.idda_unpoids                          AS Article_2_10,
    Article.iddp_unilogi                          AS Article_2_11,
    ArticleComplement.datem                       AS ArticleComplement_5_6,
    ArticleComplement.extgar_depl                 AS ArticleComplement_5_9,
    ArticleComplement.extgar_mo                   AS ArticleComplement_5_10,
    ArticleComplement.extgar_piece                AS ArticleComplement_5_11,
    ArticleComplement.id                          AS ArticleComplement_5_20,
    ArticleComplement.iddf_collect                AS ArticleComplement_5_22,
    ArticleComplement.iddp_gpdtcul                AS ArticleComplement_5_23,
    ArticleComplement.iddp_support                AS ArticleComplement_5_25,
    ArticleComplement.iddp_typcarb                AS ArticleComplement_5_27,
    ArticleComplement.mt_ext_gar                  AS ArticleComplement_5_36,
    ArticleComplement.pres_cpt                    AS ArticleComplement_5_44,
    GenreProduitCulturel.code                     AS GenreProduitCulturel_6_0,
    Collection.libelle                            AS Collection_8_1,
    Gtin.date_dern_vte                            AS Gtin_10_0,
    Gtin.gtin                                     AS Gtin_10_1,
    Gtin.id                                       AS Gtin_10_3,
    Fabricant.code                                AS Fabricant_14_0,
    Fabricant.nom                                 AS Fabricant_14_2,
    ClassificationVenteLocale.niveau1             AS ClassificationVenteL_16_2,
    ClassificationVenteLocale.niveau2             AS ClassificationVenteL_16_3,
    ClassificationVenteLocale.niveau3             AS ClassificationVenteL_16_4,
    ClassificationVenteLocale.niveau4             AS ClassificationVenteL_16_5,
    MarqueCommerciale.code                        AS MarqueCommerciale_18_0,
    MarqueCommerciale.libellelong                 AS MarqueCommerciale_18_4,
    Composition.code                              AS Composition_20_0,
    CompositionTextile.code                       AS CompositionTextile_21_0,
    AssoArticleInterfaceBalance.datem             AS AssoArticleInterface_23_0,
    AssoArticleInterfaceBalance.lib_envoi         AS AssoArticleInterface_23_3,
    AssoArticleInterfaceCaisse.datem              AS AssoArticleInterface_24_0,
    AssoArticleInterfaceCaisse.lib_envoi          AS AssoArticleInterface_24_3,
    NULL                                          AS TypeTraitement_25_0,
    NULL                                          AS TypeTraitement_25_1,
    RayonBalance.code                             AS RayonBalance_31_0,
    RayonBalance.max_cde_article                  AS RayonBalance_31_5,
    RayonBalance.min_cde_article                  AS RayonBalance_31_6,
    TypeTare.code                                 AS TypeTare_32_0,
    GrilleDePrix.datem                            AS GrilleDePrix_34_1,
    GrilleDePrix.libelle                          AS GrilleDePrix_34_3,
    FicheAgreage.code                             AS FicheAgreage_38_0,
    Codelec.iddp_periact                          AS Codelec_40_1,
    Codelec.libelle                               AS Codelec_40_2,
    Codelec.niveau1                               AS Codelec_40_3,
    Codelec.niveau2                               AS Codelec_40_4,
    Codelec.niveau3                               AS Codelec_40_5,
    Codelec.niveau4                               AS Codelec_40_6,
    PerimetreActivite.code                        AS PerimetreActivite_41_0,
    DonneesPersonnalisablesCodelec.gestionreftech AS DonneesPersonnalisab_42_0,
    ClassificationArticleInterne.id               AS ClassificationArticl_43_0,
    ClassificationArticleInterne.niveau1          AS ClassificationArticl_43_2,
    DossierCommercial.id                          AS DossierCommercial_52_0,
    DossierCommercial.codefourndc                 AS DossierCommercial_52_1,
    DossierCommercial.anneedc                     AS DossierCommercial_52_3,
    DossierCommercial.codeclassdc                 AS DossierCommercial_52_4,
    DossierCommercial.numversiondc                AS DossierCommercial_52_5,
    DossierCommercial.indice                      AS DossierCommercial_52_6,
    DossierCommercial.code_ss_classement          AS DossierCommercial_52_7,
    OrigineNegociation.code                       AS OrigineNegociation_53_0,
    MotifBlocageInformation.libellelong           AS MotifBlocageInformat_54_3,
    ArbreLogistique.id                            AS ArbreLogistique_63_1,
    ArbreLogistique.codesap                       AS ArbreLogistique_63_5,
    Fournisseur.code                              AS Fournisseur_66_0,
    Fournisseur.nom                               AS Fournisseur_66_2,
    Filiere.code                                  AS Filiere_67_0,
    Filiere.nom                                   AS Filiere_67_2,
    ValorisationAchat.val_ach_patc                AS Valorisation_74_3,
    LienPrixVente.code                            AS LienPrixVente_76_0,
    LienPrixVente.datem                           AS LienPrixVente_76_1,
    LienGratuite.code                             AS LienGratuite_78_0,
    LienGratuite.datem                            AS LienGratuite_78_1,
    LienCoordonnable.code                         AS LienCoordonnable_79_0,
    LienCoordonnable.datem                        AS LienCoordonnable_79_1,
    LienStatistique.code                          AS LienStatistique_81_0,
    LienStatistique.datem                         AS LienStatistique_81_1
FROM   da_article Article
       join (SELECT idarticle,
                    poids,
                    ROW_NUMBER()
                      over (
                        PARTITION BY RNA.id
                        ORDER BY INNERSEARCH.poids) RN,
                    titre,
                    nom,
                    prenom
             FROM   da_article RNA
                    join (SELECT idarticle,
                        pkg_db_indexation.CALCULPOIDSMOTS(chaine,
                            'foire vins%') AS POIDS,
                        DECODE(index_clerecherche, 'Piste.titre', chaine,
                                                   '')                AS TITRE,
                        DECODE(index_clerecherche, 'Artiste.nom_prenom',
                            SUBSTR(chaine, 0, INSTR(chaine, '_') - 1),
                                                   '')               AS NOM,
                        DECODE(index_clerecherche, 'Artiste.nom_prenom',
                            SUBSTR(chaine, INSTR(chaine, '_') + 1),
                                                   '')               AS PRENOM
                          FROM   ((SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticle'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticle'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art_bal AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticleBalance'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art_bal AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticleBalance'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT index_idenreg AS IDARTICLE,
                                          C.lib_com     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.libelleCommercial'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.lib_com     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.libelleCommercial'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT idda_article AS IDARTICLE,
                                          C.gtin       AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                 'Gtin.gtin'
                                          join da_gtin C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT idda_article AS IDARTICLE,
                                          C.gtin       AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Gtin.gtin'
                                          join da_gtin C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT idda_article AS IDARTICLE,
                                          C.ref_frn    AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                        'ArbreLogistique.referenceFournisseur'
                                          join da_arblogi C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT idda_article AS IDARTICLE,
                                          C.ref_frn    AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                        'ArbreLogistique.referenceFournisseur'
                                          join da_arblogi C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1))) INNERSEARCH
                      ON INNERSEARCH.idarticle = RNA.id) SEARCHMC
         ON SEARCHMC.idarticle = Article.id
            AND 1 = 1
       left join da_artcmpl ArticleComplement
              ON Article.id = ArticleComplement.idda_article
       left join dp_gpdtcul GenreProduitCulturel
              ON ArticleComplement.iddp_gpdtcul = GenreProduitCulturel.id
       left join df_collect Collection
              ON ArticleComplement.iddf_collect = Collection.id
       left join da_gtin Gtin
              ON Article.id = Gtin.idda_article
                 AND Gtin.principal = 1
                 AND Gtin.db_suplog = 0
       left join df_fabric Fabricant
              ON Article.iddf_fabric = Fabricant.id
       left join dp_clsvtel ClassificationVenteLocale
              ON Article.iddp_clsvtel = ClassificationVenteLocale.id
       left join dp_marqcom MarqueCommerciale
              ON Article.iddp_marqcom = MarqueCommerciale.id
       left join da_compo Composition
              ON Composition.id = Article.idda_compo
       left join dp_comptex CompositionTextile
              ON CompositionTextile.id = Article.iddp_comptex
       left join da_arttrai AssoArticleInterfaceBalance
              ON AssoArticleInterfaceBalance.idda_article = Article.id
                 AND AssoArticleInterfaceBalance.iddp_tinterf = 1
       left join da_arttrai AssoArticleInterfaceCaisse
              ON AssoArticleInterfaceCaisse.idda_article = Article.id
                 AND AssoArticleInterfaceCaisse.iddp_tinterf = 4
       left join dp_raybala RayonBalance
              ON Article.iddp_raybala = RayonBalance.id
       left join dp_valdico TypeTare
              ON TypeTare.id = RayonBalance.iddp_typtare
       left join df_categor Categorie
              ON Categorie.id = Article.iddf_categor
       left join df_grille GrilleDePrix
              ON GrilleDePrix.id = Categorie.iddf_grille
       left join dp_agreage FicheAgreage
              ON FicheAgreage.id = Article.iddp_agreage
       join dp_codelec Codelec
         ON Article.iddp_codelec = Codelec.id
       left join dp_periact PerimetreActivite
              ON PerimetreActivite.id = Codelec.iddp_periact
       left join dp_perscod DonneesPersonnalisablesCodelec
              ON Codelec.id = DonneesPersonnalisablesCodelec.iddp_codelec
                 AND DonneesPersonnalisablesCodelec.db_suplog = 0
                 AND DonneesPersonnalisablesCodelec.iddb_sitecl = 1012124
       left join dp_clsart ClassificationArticleInterne
              ON DonneesPersonnalisablesCodelec.iddp_clsart =
                ClassificationArticleInterne.id
       left join da_artdeno ArticleDenormalise
              ON Article.id = ArticleDenormalise.idda_article
       left join df_clasmnt ClassementFournisseur
              ON ArticleDenormalise.iddf_clasmnt = ClassementFournisseur.id
       left join tr_dosclas DossierDeClassement
              ON ClassementFournisseur.id = DossierDeClassement.iddf_clasmnt
                 AND DossierDeClassement.date_deb <= '2013-09-27'
                 AND COALESCE(DossierDeClassement.date_fin,
                    TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
       left join tr_doscomm DossierCommercial
              ON DossierDeClassement.idtr_doscomm = DossierCommercial.id
       left join dp_valdico OrigineNegociation
              ON DossierCommercial.iddp_dossref = OrigineNegociation.id
       left join dp_motbloc MotifBlocageInformation
              ON MotifBlocageInformation.id = ArticleDenormalise.idda_motinf
       left join da_arblogi ArbreLogistique
              ON Article.id = ArbreLogistique.idda_article
                 AND ArbreLogistique.princ = 1
                 AND ArbreLogistique.db_suplog = 0
       left join df_filiere Filiere
              ON ArbreLogistique.iddf_filiere = Filiere.id
       left join df_fourn Fournisseur
              ON Filiere.iddf_fourn = Fournisseur.id
       left join od_dosal dossierALValo
              ON dossierALValo.idda_arblogi = ArbreLogistique.id
                 AND dossierALValo.idod_dossier IS NULL
       left join tt_val_dal valoDossier
              ON valoDossier.idod_dosal = dossierALValo.id
                 AND valoDossier.estarecalculer = 0
       left join tt_valo ValorisationAchat
              ON ValorisationAchat.idtt_val_dal = valoDossier.id
                 AND ValorisationAchat.date_modif_retro IS NULL
                 AND ValorisationAchat.date_debut_achat <= '2013-09-27'
                 AND COALESCE(ValorisationAchat.date_fin_achat,
                    TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
                 AND ValorisationAchat.val_ach_pab IS NOT NULL
       left join da_lienart assoALPXVT
              ON assoALPXVT.idda_article = Article.id
                 AND assoALPXVT.iddp_typlien = 14893
       left join da_lien LienPrixVente
              ON LienPrixVente.id = assoALPXVT.idda_lien
       left join da_lienart assoALGRAT
              ON assoALGRAT.idda_article = Article.id
                 AND assoALGRAT.iddp_typlien = 14894
       left join da_lien LienGratuite
              ON LienGratuite.id = assoALGRAT.idda_lien
       left join da_lienart assoALCOOR
              ON assoALCOOR.idda_article = Article.id
                 AND assoALCOOR.iddp_typlien = 14899
       left join da_lien LienCoordonnable
              ON LienCoordonnable.id = assoALCOOR.idda_lien
       left join da_lienal assoALSTAT
              ON assoALSTAT.idda_arblogi = ArbreLogistique.id
                 AND assoALSTAT.iddp_typlien = 14897
       left join da_lien LienStatistique
              ON LienStatistique.id = assoALSTAT.idda_lien WHERE
SEARCHMC.rn = 1
   AND ( ValorisationAchat.id IS NULL
          OR ValorisationAchat.date_debut_achat = (
                 SELECT MAX(VALMAX.date_debut_achat)
                 FROM   tt_valo VALMAX
                 WHERE  VALMAX.idtt_val_dal = ValorisationAchat.idtt_val_dal
                    AND VALMAX.date_modif_retro IS NULL
                    AND VALMAX.val_ach_pab IS NOT NULL
                    AND VALMAX.date_debut_achat <= '2013-09-27') )
   AND ( Article.id IN (SELECT A.id
                        FROM   da_article A
                               join du_ucutiar AssoUcUtiAr
                                 ON AssoUcUtiAr.idda_article = A.id
                               join du_asucuti AssoUcUti
                                 ON AssoUcUti.id = AssoUcUtiAr.iddu_asucuti
                        WHERE  ( AssoUcUti.iddu_uti IN ( 90000000000022 ) )
                           AND a.iddb_sitecl = 1012124) )
   AND Article.db_suplog = 0
ORDER  BY SEARCHMC.poids ASC

Ce code a été généré initialement par Hibernate, puis édité plusieurs fois à la main.

V-F-8. Recherche textuelle

  • Objectif :

    • ajouter un moteur de recherche à l’application.
  • Pratique courante :

    • utiliser l’opérateur LIKE.
  • Problèmes :

    • requiert des index spécialisés,
    • recherche uniquement le terme exact.
  • Solution :

    • utiliser Full Text Search.

Les bases de données qui stockent des données textuelles ont souvent pour but de permettre des recherches sur ces données textuelles.

La première solution envisagée lorsque le besoin se fait sentir est d’utiliser l’opérateur LIKE. Il permet en effet de réaliser des recherches de motif sur une colonne stockant des données textuelles. C’est une solution simple et qui peut s’avérer simpliste dans de nombreux cas.

Tout d’abord, les recherches de type LIKE '%motif%' ne peuvent généralement pas tirer partie d’un index btree normal. Cela étant dit, depuis la version 9.2, le module pg_trgm permet d’optimiser ces recherches à l’aide d’un index GiST ou GIN.

Mais cette solution n’offre pas la même souplesse que la recherche plein texte, en anglais Full Text Search, de PostgreSQL.

Exemples :

L’exemple ci-dessous montre l’utilisation du module pg_trgm pour accélérer une recherche avec LIKE'%motif%' :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
CREATE INDEX idx_appellation_libelle ON appellation
USING btree (libelle varchar_pattern_ops);

EXPLAIN SELECT * FROM appellation WHERE libelle LIKE '%wur%';
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on appellation  (cost=0.00..6.99 rows=3 width=24)
   Filter: (libelle ~~ '%wur%'::text)

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_appellation_libelle_trgm ON appellation
USING gist (libelle gist_trgm_ops);

EXPLAIN SELECT * FROM appellation WHERE libelle LIKE '%wur%';
                                        QUERY PLAN

-----------------------------------------------------------------------------
 Bitmap Heap Scan on appellation  (cost=4.27..7.41 rows=3 width=24)
   Recheck Cond: (libelle ~~ '%wur%'::text)
   ->  Bitmap Index Scan on idx_appellation_libelle_trgm  (cost=0.00..4.27...)
         Index Cond: (libelle ~~ '%wur%'::text)

V-G. Conclusion

  • Des objets de plus en plus complexes :

    • mais performants,
    • et remplissant un besoin.
  • Des conseils :

    • pour améliorer les performances,
    • et pour éviter les pièges.

Nous avons vu dans ce module de nouveaux objets SQL comme les procédures stockées livrées par défaut avec PostgreSQL, ainsi que les vues. Nous avons vu aussi les requêtes préparées, mécanisme censé fournir un surplus de performances si vous avez de nombreuses requêtes à exécuter qui suivent un même motif.

Nous avons fini avec des conseils, principalement pour éviter les pièges habituels, mais aussi pour améliorer les performances.

V-H. Travaux pratiques

V-H-1. Énoncés

  1. Ajouter une adresse mail à chaque contact avec la concaténation du nom avec le texte ‘@dalibo.com’.
  2. Concaténer nom et adresse mail des contacts français sous la forme ‘nom‘
  3. Même demande, mais avec le nom en majuscules et l’adresse mail en minuscules.
  4. Ajouter la colonne prix_total de type numeric(10,2) à la table commandes. Mettre à jour la colonne prix_total des commandes avec les informations des lignes de la table lignes_commandes.
  5. Récupérer le montant total des commandes par mois pour l’année 2010. Les montants seront arrondis à deux décimales.
  6. Supprimer les commandes de mai 2010.
  7. Réexécuter la requête trouvée au point 5.
  8. Qu’observez-vous ?
  9. Corriger le problème rencontré.
  10. Créer une vue calculant le prix total de chaque commande.

V-H-2. Solutions

  1. Ajouter une adresse mail à chaque contact avec la concaténation du nom avec le texte ‘@dalibo.com’.
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
BEGIN ;

UPDATE contacts
SET email = nom||'@dalibo.com'
;

COMMIT ;

Note : pour éviter de mettre à jour les contacts ayant déjà une adresse mail, il suffit d’ajouter une clause WHERE :

 
Sélectionnez
1.
2.
3.
4.
UPDATE contacts
SET email = nom||'@dalibo.com'
WHERE email IS NULL
;
  1. Concaténer nom et adresse mail des contacts français sous la forme ‘nom‘
 
Sélectionnez
1.
2.
3.
SELECT nom||' <'||email||'>'
FROM contacts
;
  1. Même demande mais avec le nom en majuscules et l’adresse mail en minuscules.
 
Sélectionnez
1.
2.
3.
SELECT upper(nom)||' <'||lower(email)||'>'
FROM contacts
;
  1. Ajouter la colonne prix_total de type numeric(10,2) à la table commandes. Mettre à jour la colonne prix_total des commandes avec les informations des lignes de la table lignes_commandes.
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
ALTER TABLE commandes ADD COLUMN prix_total numeric(10,2);
BEGIN ;

UPDATE commandes c
SET prix_total= (
   /* cette sous-requête fait une jointure entre lignes_commandes et la table
      commandes à mettre à jour pour calculer le prix par commande */
   SELECT SUM(quantite * prix_unitaire - remise)
   FROM lignes_commandes lc
   WHERE lc.numero_commande=c.numero_commande
)
-- on peut récupérer le détail de la mise à jour directement dans la requête :
-- RETURNING numero_commande, prix_total
;

COMMIT ;

Une autre variante de cette requête serait :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
UPDATE commandes c SET prix_total=prix_calc
FROM (
    SELECT numero_commande, SUM(quantite * prix_unitaire - remise) AS prix_calc
        FROM lignes_commandes
       GROUP BY numero_commande
) as prix_detail
WHERE prix_detail.numero_commande = c.numero_commande

Bien que cette dernière variante soit moins lisible, elle est bien plus rapide sur un gros volume de données.

  1. Récupérer le montant total des commandes par mois pour l’année 2010. Les montants seront arrondis à deux décimales.
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
SELECT extract('month' from date_commande) AS numero_mois,
    round(sum(prix_total),2) AS montant_total
FROM commandes
WHERE date_commande >= to_date('01/01/2010', 'DD/MM/YYYY')
   AND date_commande < to_date('01/01/2011', 'DD/MM/YYYY')
GROUP BY 1
ORDER BY 1
;

Attention, il n’y a pas de contrainte NOT NULL sur le champ date_commande, donc s’il existe des commandes sans date de commande, celles-ci seront agrégées à part des autres, puisque extract() renverra NULL pour ces lignes.

  1. Supprimer les commandes de mai 2010.
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
BEGIN;

/* en raison de la présence de clés étrangères, il faut en premier leur
   supprimer les lignes de la table lignes_commandes correspondant aux
   commandes à supprimer */
DELETE
FROM lignes_commandes
WHERE numero_commande IN (
   SELECT numero_commande
   FROM commandes
   WHERE date_commande >= to_date('01/05/2010', 'DD/MM/YYYY')
      AND date_commande < to_date('01/06/2010', 'DD/MM/YYYY')
   )
;

-- ensuite seulement on peut supprimer les commandes
DELETE
FROM commandes
WHERE date_commande >= to_date('01/05/2010', 'DD/MM/YYYY')
   AND date_commande < to_date('01/06/2010', 'DD/MM/YYYY')
;

COMMIT ;

Le problème de l’approche précédente est d’effectuer l’opération en deux temps. Il est possible de réaliser la totalité des suppressions dans les deux tables lignes_commandes et commandes en une seule requête en utilisant une CTE :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
WITH del_lc AS (
   /* ici on déclare la CTE qui va se charger de supprimer les lignes dans la
      table lignes_commandes et retourner les numéros de commandes supprimés */
   DELETE
   FROM lignes_commandes
   WHERE numero_commande IN (
      SELECT numero_commande
      FROM commandes
      WHERE date_commande >= to_date('01/05/2010', 'DD/MM/YYYY')
         AND date_commande < to_date('01/06/2010', 'DD/MM/YYYY')
      )
   RETURNING numero_commande
)
/* requête principale, qui supprime les commandes dont les numéros
   correspondent aux numéros de commandes remontés par la CTE */
DELETE
FROM commandes c
WHERE EXISTS (
   SELECT 1
   FROM del_lc
   WHERE del_lc.numero_commande = c.numero_commande
   )
;
  1. Réexécuter la requête trouvée au point 5.
 
Sélectionnez
1.
2.
3.
4.
5.
6.
SELECT extract('month' from date_commande) AS numero_mois,
    round(sum(prix_total),2) AS montant_total
FROM commandes
GROUP BY 1
ORDER BY 1
;
  1. Qu’observez-vous ?

La ligne correspondant au mois de mai a disparu.

  1. Corriger le problème rencontré.
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT numero_mois, round(coalesce(sum(prix_total), 0.0),2) AS montant_total
  /* la fonction generate_series permet de générer une pseudo-table d'une
     colonne contenant les chiffres de 1 à 12 */
FROM generate_series(1, 12) AS numero_mois
  /* le LEFT JOIN entre cette pseudo-table et la table commandes permet de
     s'assurer que, même si aucune commande n'a eu lieu sur un mois, la ligne
     correspondante sera tout de même présente */
LEFT JOIN commandes ON extract('month' from date_commande) = numero_mois
      AND date_commande >= to_date('01/01/2010', 'DD/MM/YYYY')
      AND date_commande < to_date('01/01/2011', 'DD/MM/YYYY')
GROUP BY 1
ORDER BY 1
;

Notez l’utilisation de la fonction coalesce() dans le SELECT, afin d’affecter la valeur 0.0 aux lignes « ajoutées » par le LEFT JOIN qui n’ont par défaut aucune valeur (NULL).

  1. Créer une vue calculant le prix total de chaque commande.
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
CREATE VIEW commande_montant AS
SELECT
   numero_commande,
   sum(quantite * prix_unitaire - remise) AS total_commande
FROM lignes_commandes
GROUP BY numero_commande
;
  1. Réécrire la requête du point 5 pour utiliser la vue créée au point 10.
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT extract('month' from date_commande) AS numero_mois,
    round(sum(total_commande),2) AS montant_total
FROM commandes c
JOIN commande_montant cm ON cm.numero_commande = c.numero_commande
WHERE date_commande >= to_date('01/01/2010', 'DD/MM/YYYY')
   AND date_commande < to_date('01/01/2011', 'DD/MM/YYYY')
GROUP BY 1
ORDER BY 1
;

précédentsommairesuivant

  

Licence Creative Commons
Le contenu de cet article est rédigé par Dalibo SCOP et est mis à disposition selon les termes de la Licence Creative Commons Attribution - Pas d'Utilisation Commerciale - Partage dans les Mêmes Conditions 3.0 non transposé.
Les logos Developpez.com, en-tête, pied de page, css, et look & feel de l'article sont Copyright © 2018 Developpez.com.