Algorithmes et Bases de Données, année 2019

Gestion des types avancés

Nous allons étudiés la gestion des types avancés par PostgreSQL. Voici la liste (quasi-)exaustive des types supporté:

Numeric Types
Monetary Types
Character Types
Binary Data Types
Date/Time Types
Boolean Type
Enumerated Types
Geometric Types
Network Address Types
Bit String Types
Text Search Types
XML Type
JSON Types
Arrays
Composite Types
Range Types
Domain Types
Pseudo-Types

Nous allons nous concentrer aujourd’hui sur les types Enumerated et Composite, Array, Text Search et JSON ainsi que sur les mécanismes d’indexation de ces derniers.

Les types d’énumérations

Les types d’énumération et composite permettent de créer des types personalisés adaptés au contexte. Le type d’énumération est simplement un ensemble fini de type, pratique pour gérer des informations statiques:

 CREATE TYPE compte_status AS ENUM ('créé', 'actif', 'inactif', 'membre', 'pré-inscrit');
 CREATE TABLE comptes (id SERIAL, pseudo TEXT, status compte_status);
 INSERT INTO comptes (pseudo, status) VALUES ('charles', 'membre');
 INSERT INTO comptes (pseudo, status) VALUES ('paul', 'actif');
 SELECT * FROM comptes;

id | pseudo  | status 
----+---------+--------
  1 | charles | membre
  2 | paul    | actif
 (2 lignes)
 
 INSERT INTO comptes (pseudo, status) VALUES ('julie', 'passif');

ERREUR:  valeur en entrée invalide pour le enum compte_status : « passif »

Le type d’énumération n’est pas compatible avec du text mais il est possible de réaliser un cast à l’aide de l’opérateur ̀::text`.

 CREATE TABLE val (v TEXT);
 INSERT INTO val VALUES ('member'), ('actif'); 
 SELECT * FROM comptes, val WHERE status = v;

ERREUR:  l'opérateur n'existe pas : compte_status = text
LIGNE 1 : SELECT * FROM comptes, val WHERE status = v;
                                                  ^
ASTUCE : Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.

 SELECT * FROM comptes, val WHERE status::text = v;
 id | pseudo  | status |   v 
----+---------+--------+-------
  2 | paul    | actif  | actif
(1 ligne)

L’avantage par rapport à du texte est la place occupé par un type d’énumération: il occupe 4 octets dans la table. L’avantage par rapport à un encodage par des valeurs entières est la lisibilité ainsi que d’éviter des erreurs lors de l’écriture de requête (la comparaison de deux champs non comparable).

Les types composites

Le type composite permet de définir simplement des types à la manière d’un struct en C. Il est évidemment possible d’utiliser des types composites dans des types composites ou des types énums, ce qui permet de construire des objets complexes. En reprenant le type énum précédant:

 CREATE TYPE utils_type AS (pseudo TEXT, inscription TIMESTAMP, status COMPTE_STATUS);
 CREATE TABLE utilisateurs (id SERIAL, utilisateur UTILS_TYPE);
 INSERT INTO utilisateurs (utilisateur) VALUES (ROW('cha_rio', now(), 'membre')), (ROW('polo', now(), 'inactif'));
 SELECT * FROM utilisateurs;
 id |                  utilisateur
----+-----------------------------------------------
  1 | (cha_rio,"2019-04-23 11:36:56.988859",membre)
  2 | (polo,"2019-04-23 11:36:56.988859",inactif)
(2 lignes)

Attention, pour construire les valeurs, il faut utiliser la commande ROW afin de différencier d’un tuple. Il est possible d’acceder à un champ et de le modifier grâce à un opérateur .. On peut également applatir l’objet en un tuple grace à l’opérateur *.

 SELECT id, (utilisateur).* FROM utilisateurs WHERE (utilisateur).status = 'membre';
 pseudo  |        inscription         | status 
---------+----------------------------+--------
 cha_rio | 2019-04-23 11:36:56.988859 | membre
(1 ligne)

L’utilisation de type composite permet de coller plus fidelement aux applications fortement orientés objet et régler des problèmes de compatibilité entre le modèle relationnel et le modèle objet. Il est également possible de définir des fonctions opérants sur ces objets dans un langage de programmation intégré à postgreSQL.

Attention: Il faut utiliser des parenthèse autours des types composites.

Les types array

Il est possible de stocker un grand nombre de valeurs d’un seul type dans un champ grâce au type array. Ce type est très utile quand les valeurs sont statiques ou très faiblement dynamique car ajouter un élément à un array nécessite de tout recopier.

C’est un type qui est bien plus concis que simplement un codage relationnel et plus agréable à manipuler. Il est particulièrement efficace pour stocker des vecteurs de (petite) tailles fixée quand on souhaite pouvoir en récupérer une des composante rapidement.

Pour utiliser un type array, on utilise la notation []. Pour renseigner des valeurs, on utilise le constructeur ARRAY:

 CREATE TABLE textes (id SERIAL, content TEXT, tags TEXT[]);
 INSERT INTO textes (content, tags) values ('Ceci est un joli texte', ARRAY['Wouhaou', 'top']), ('Ceci est moins joli', ARRAY['Rembourser', 'Invitation', 'Démission']); 
 SELECT * FROM textes;
 id |        content         |               tags                
----+------------------------+-----------------------------------
  1 | Ceci est un joli texte | {Wouhaou,top}
  2 | Ceci est moins joli    | {Rembourser,Invitation,Démission}
(2 lignes)

 SELECT id, tags[1] FROM textes;
 id |    tags    
----+------------
  1 | Wouhaou
  2 | Rembourser
(2 lignes)

 UPDATE textes SET tags = array_append(tags, 'plouf') WHERE id=1;
 SELECT id, tags[3] FROM textes;
 id |   tags    
----+-----------
  2 | Démission
  1 | plouf
(2 lignes)

 SELECT content, UNNEST(tags) FROM textes;
        content         |   unnest   
------------------------+------------
 Ceci est moins joli    | Rembourser
 Ceci est moins joli    | Invitation
 Ceci est moins joli    | Démission
 Ceci est un joli texte | Wouhaou
 Ceci est un joli texte | top
 Ceci est un joli texte | plouf
(6 lignes)

 SELECT content, UNNEST(tags) as tags FROM textes;
        content         |    tags    
------------------------+------------
 Ceci est moins joli    | Rembourser
 Ceci est moins joli    | Invitation
 Ceci est moins joli    | Démission
 Ceci est un joli texte | Wouhaou
 Ceci est un joli texte | top
 Ceci est un joli texte | plouf
(6 lignes)

Il est également possible de créer dynamiquement des ARRAY à partir de résultat d’une requete SQL:

SELECT ARRAY_AGG(tags) FROM (SELECT UNNEST(tags) as tags FROM textes WHERE id=1) as s;
      array_agg      
---------------------
 {Wouhaou,top,plouf}

Le type array permet de faire de la recherche simplement grâce notamment aux opérateurs any and all:

 INSERT INTO textes (content, tags) VALUES ('plouf plouf', ARRAY['plouf']);
 SELECT * FROM textes WHERE 'plouf'=any(tags);
 id |        content         |        tags         
----+------------------------+---------------------
  1 | Ceci est un joli texte | {Wouhaou,top,plouf}
  3 | plouf plouf            | {plouf}
(2 lignes)

 SELECT * FROM textes WHERE 'plouf'=all(tags);
 id |   content   |  tags   
----+-------------+---------
  3 | plouf plouf | {plouf}
(1 ligne)

Les types JSON

On termine par la gestion native par PostgreSQL des documents JSON. Ceux ci bénéficie de deux implémentations: une en simple texte (type json) et une version binaire parsé (jsonb).

La première est plus efficace pour l’ingestion rapide de données et la seconde plus efficace pour la mis à jour et la recherche dans les données.

 INSERT INTO messages (content) VALUES ('{"un":"json"}');
 INSERT INTO messages (content) VALUES ('{"un":"json", "avec":["une", "liste"]}');
 INSERT INTO messages (content) VALUES ('{"un":{"json":"recursif"}, "avec":["une",{"liste":"recursive"}]}');
 SELECT * FROM messages;
 id |                                content                                
----+-----------------------------------------------------------------------
  1 | {"un": "json"}
  2 | {"un": "json", "avec": ["une", "liste"]}
  3 | {"un": {"json": "recursif"}, "avec": ["une", {"liste": "recursive"}]}
(3 lignes)

 SELECT content->'un' FROM messages;
       ?column?       
----------------------
 "json"
 "json"
 {"json": "recursif"}
(3 lignes)

 SELECT content->'avec'->(1) FROM messages;
        ?column?        
------------------------
 
 "liste"
 {"liste": "recursive"}
(3 lignes)

Il est possible de filtrer les JSON (uniquement jsonb) par l’opérateur de containement @>:

 SELECT content->'avec' as value FROM messages WHERE content @> '{"avec":[]}'::jsonb;
              value              
---------------------------------
 ["une", "liste"]
 ["une", {"liste": "recursive"}]
(2 lignes)

 SELECT content->'avec' as value FROM messages WHERE content @> '{"avec":["liste"]}'::jsonb;
      value       
------------------
 ["une", "liste"]
(1 ligne)

Indexation des types composées

Que ce soit les types composites, les ARRAY ou les jsonb, il est possible d’utiliser des indexes dédiés qui accélèrent énormément la recherche: les indexes inversés GIN (pour Generalized Inverted Index). Les indexes renversés sont standards notamment pour faire de la recherches textuelles: plutôt que stocker dans une structure de données la répartition des valeurs, on va associer à chaque valeur qui apparaît la liste des lignes.

Les indexes classiques btree et hash sont également utilisable mais ne supportent comme opérations que l’égalité ou la différence. Pour explorer l’intérieur des structures on favorisera l’index GIN: il permet d’accélérer fortement pour les array les opération:

&& intersection non vide
== l’égalité
@> l’inclusion

Compiled the: dim. 07 janv. 2024 23:19:23 CET