Définition des données

Introduction

Le langage de définition des données s’appuie sur trois ordres : CREATE, ALTER et DROP déclinés pour chaque objet de la base de données. Chaque objet peut correspondre soit à un objet physique, donc à des fichiers dans les systèmes de fichiers, soit à un objet logique, et donc seulement une définition stockée dans le catalogue de la base de données.

L’exécution de ces ordres implique nécessairement un verrouillage exclusif des objets concernés. Ce verrouillage étant exclusif, aucun autre verrou en lecture ou en écriture ne peut être maintenu. Ceci ne pose pas de problème lors de la création d’un objet puisqu’aucune session concurrente ne peut l’avoir réclamé mais lors de la suppression d’un objet, il faut attendre que tous les verrous existants soient relâchés. En ce qui concerne une base de données par exemple, il ne doit pas y avoir de sessions connectées à cette base.

L’impact le plus important concerne la modification d’un objet, car le verrou est conservé durant tout le temps d’exécution de la commande, et ceci peut donc avoir un impact sur l’exécution de requêtes concurrentes.

Les ordres de définition des données doivent donc être exécutés avec précaution...

Les espaces de tables

Un espace de tables est un répertoire d’un système de fichiers, dans lequel PostgreSQL écrit les fichiers des tables et des index. Par défaut, PostgreSQL dispose d’un espace de tables situé dans le répertoire du groupe de bases de données. Il est possible de créer d’autres espaces de tables, permettant ainsi à l’administrateur de choisir l’emplacement du stockage d’une table ou d’un index.

Plusieurs motifs peuvent amener un administrateur à créer un espace de tables :

Les bases de données

Dans une instance de PostgreSQL, une base de données est un conteneur. Elle contient des schémas, des tables, des index et tous les objets utiles à une application. Elle accueille aussi les connexions depuis les applications clientes. En effet, lorsqu’une connexion est ouverte sur une base de données particulière, il n’est pas possible d’utiliser directement des objets créés dans d’autres bases de données.

Il est donc important de répartir correctement les objets et données des applications dans les bases de données, notamment en utilisant la notion de schéma. La création d’une base de données peut s’effectuer avec l’ordre CREATE DATABASE ou avec la commande du système d’exploitation createdb.

Quelques paramètres permettent de personnaliser la création d’une base de données :

Les schémas

Les schémas, aussi appelés espaces de noms, existent implicitement dans toutes les bases de données. Il s’agit d’une notion purement logique qui permet de regrouper par nom les objets comme les tables, les vues, les fonctions, définissant ainsi un chemin d’accès.

De cette façon, dans une même base de données, plusieurs objets peuvent avoir le même nom mais être distincts physiquement. Le nom du schéma préfixant le nom de l’objet, la distinction entre les objets est claire.

L’analogie peut être faite avec la notion de package ou de namespace dans différents langages de programmation. Des objets avec des noms identiques (attributs, méthodes...), mais avec une implémentation différente peuvent être distingués par le nom du package. Dans PostgreSQL, la notion est identique, en considérant le fait qu’une classe corresponde à une table et l’implémentation aux données. Le package correspond donc à un schéma. Malgré cette analogie, il est à noter une différence importante : il n’existe qu’un seul niveau de profondeur avec les schémas. Dans PostgreSQL, il n’est pas possible de créer des schémas dans un schéma.

Plusieurs schémas existent initialement dans une base de données...

Les tables

Une table est l’élément de base d’un serveur de bases de données. C’est dans une table que les données sont stockées et c’est l’organisation des tables qui détermine la qualité de la base de données.

Le choix des types de données et les liens entre les tables font partie de l’étape de conception de la base de données pour qu’elle soit performante et pérenne.

L’ordre CREATE TABLE permet de mettre en œuvre les choix de cette conception. 

Le synopsis minimal de cette commande est le suivant :

CREATE [ TEMP | UNLOGGED ] TABLE [ IF NOT EXISTS ] nomtable ( [ 
  { nomcol type [ COLLATE collation ] [ contraintecolonne [...] ] 
    | contraintetable  
    | LIKE source_table [ like_option ... ]  
  } [,...] ] )  
[ INHERITS ( parent_table [, ... ] ) ]  
[ WITH ( storage_parameter [= value] [, ... ] )  
  | WITH OIDS | WITHOUT OIDS ]  
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]  
[ TABLESPACE tablespace_name ] 
 

Préalablement à la définition des attributs d’une table, un certain nombre de caractéristiques annexes peuvent être précisées afin de modifier le comportement d’une table dans la base de données.

En premier lieu, les modificateurs TEMP et UNLOGGED permettent de modifier la nature d’une table :

Les vues

Une vue est l’équivalent d’une requête SELECT mais stockée sous la forme d’une relation équivalente à une table mais en lecture seule. Les données affichées par une vue ne sont pas modifiables et la requête SELECT sous-jacente est lancée à chaque appel de la vue. L’intérêt d’une vue est de présenter les données d’une certaine façon et d’être toujours disponible.

L’ordre CREATE VIEW permet de créer une vue, en se basant sur une requête SELECT, comme dans le synopsis suivant :

CREATE [ OR REPLACE ] [ TEMP ] VIEW nom [ ( colonne, ... ) ]  
  WITH ( nomoption = valeur] [, ... ] ) 
  AS requete  
  WITH [ CASCADED | LOCAL ] CHECK OPTION ; 
 

Par défaut, le nom des colonnes de la vue correspond au nom des colonnes retourné par la requête SELECT. Il est possible de modifier ces noms de colonnes en les précisant entre parenthèses, après le nom de la table.

De plus, il est possible de remplacer une vue existante, en ajoutant les mots-clés OR REPLACE après CREATE, à condition que les colonnes de la vue soient identiques en nombre et en type.

La clause TEMP modifie la portée de la vue en la rendant accessible uniquement dans la session en cours et seulement pendant le temps de la session. La vue est donc supprimée à la fin de la session. Lorsqu’une vue temporaire a le même nom qu’un objet existant, elle est prioritaire sur cet objet pendant la session.

Il est possible de mettre à jour les données présentées par une vue dans certaines conditions précises. La clause WITH CHECK OPTION permet de vérifier que les modifications apportées aux données correspondent aux clauses de la vue, c’est-à-dire que la donnée modifiée fera partie de la vue. Les modificateurs LOCAL et CASCADED permettent...

Le système de règles

Il existe dans PostgreSQL un système de réécriture de requêtes, appelé règles de réécriture, qui permet de modifier en profondeur l’interprétation des ordres SQL. Le comportement est assez proche de celui des déclencheurs, mais alors qu’un déclencheur permet des actions avant ou après une requête, une règle permet de détourner une action afin d’en effectuer une autre à la place.

Le synopsis de l’ordre de création d’une règle est le suivant :

CREATE [ OR REPLACE ] RULE nom AS ON evenement  
    TO table [ WHERE condition ]  
    DO [ ALSO | INSTEAD ] { NOTHING | commande |  
( commande ; commande ... ) } 
 

Les mots-clés OR REPLACE permettent de remplacer une règle existante donc avec le même nom.

L’événement doit être un des quatre ordres de manipulation des données (SELECT, UPDATE, INSERT et DELETE).

La condition permet de filtrer la réécriture, en fonction des valeurs de la ligne sélectionnée, avec le mot-clé OLD désignant la ligne.

Le mot-clé ALSO précise que la requête qui déclenche la réécriture est effectuée, alors que le mot-clé INSTEAD indique que cette requête ne sera pas exécutée....

L’héritage

PostgreSQL propose un système d’héritage aux tables, ce qui permet à une table fille d’hériter des colonnes de la table mère, et d’avoir ses propres colonnes en plus. Lorsqu’un tuple est inséré dans la table fille, les données sont aussi visibles depuis la table mère. Seules les colonnes propres à la table fille sont stockées physiquement dans cette table.

Par exemple, la table prestations de la base de données clients permet de stocker des informations génériques sur des prestations. En créant une table formations, héritant de la table prestations, il est possible de spécialiser cette table, en ajoutant des champs, comme dans l’exemple suivant :

CREATE TABLE formations  (  
   nb_stagiaires int,  
   plandecours varchar(25),  
   type varchar(5) check ( type in ('intra','inter') ))  
INHERITS (prestations) ; 
 

La description de la table par la commande \d montre que les colonnes de la table mère font effectivement partie de cette nouvelle table :

clients=# \d formations  
                   Table "public.formations"  
     Colonne      |          Type            | Modificateurs  
——————————————————+——————————————————————————+———————-...

Gestion de données externes

PostgreSQL dispose de mécanismes permettant d’accéder à des données situées à l’extérieur d’une instance. L’implémentation répond au standard SQL/MED.

Cette implémentation permet d’utiliser un composant externe qui définit les méthodes utiles pour se connecter à un service, comme un serveur de base de données, ou ouvrir une ressource, comme un fichier CSV. Ces composants ne sont pas fournis avec PostgreSQL, excepté le connecteur vers PostgreSQL, mais par des projets externes. Ces implémentations sont appelées des « wrappers ».

Si ce mécanisme permet d’utiliser des données externes en lecture et en écriture, les composants spécifiques n’implémentent pas tous l’écriture de données.

Une fois le composant externe installé, il est possible de définir une connexion vers un service externe et d’établir une correspondance entre un utilisateur local à l’instance PostgreSQL avec un utilisateur autorisé à se connecter au service externe.

Enfin, une table étrangère est créée afin de faire correspondre une entité du service externe avec une relation locale à l’instance PostgreSQL.

Les index

Un index est un bon outil pour améliorer les performances de la base de données. L’analogie avec un livre permet de comprendre le rôle joué par un index dans une base de données. Sans index, lorsqu’un lecteur cherche une information, il doit lire le livre jusqu’à trouver ce qu’il cherche. Selon la taille du livre et l’endroit où se situe l’information, début ou fin, la recherche peut être longue. Dans la plupart des ouvrages techniques, l’éditeur place un index de quelques pages, contenant les mots-clés considérés comme étant susceptibles de faire l’objet d’une recherche afin de faciliter cette recherche. Le lecteur n’a plus qu’à rechercher dans l’index et se rendre à la page indiquée.

Les serveurs de bases de données stockent les données dans des tables et doivent lire les tables lorsqu’un utilisateur recherche une donnée. La méthode la plus simple est de parcourir séquentiellement une table, jusqu’à rencontrer la donnée recherchée. Cette méthode fonctionne bien tant que la table a un volume rendant les temps de parcours correct. Au-delà d’une limite qui dépend des types de données utilisés, du nombre de colonnes de la table et du nombre de tuples, le parcours séquentiel de la table est trop long pour obtenir des temps de réponse raisonnables. Il devient nécessaire de créer un ou plusieurs index, en fonction des recherches effectuées sur la table.

L’analogie avec le livre s’arrête là. En effet, un livre a un contenu statique, au contraire d’une base de données, dont le contenu évolue dans le temps. Cela signifie que les index doivent être mis à jour en même temps que la table. Plus une table a d’index associés, plus les temps de mise à jour lors d’une insertion, d’une mise à jour ou d’une suppression des données sont longs. Le temps susceptible d’être gagné en lecture est perdu en écriture.

La création d’un index est donc un équilibre entre les améliorations souhaitées en lecture et les baisses de performances en écriture....

Séquences et attribut d’identité

Une séquence est un type d’objet un peu particulier. Une séquence correspond à un compteur qui peut être manipulé avec quelques fonctions ; notamment la fonction nextval() qui permet d’incrémenter la valeur du compteur et de la récupérer. Ceci permet par exemple d’obtenir une sorte d’incrémentation automatique d’une colonne. De plus, il est possible de partager une même séquence entre plusieurs tables, créant ainsi un identifiant unique inter-tables.

L’attribut d’identité apparaît avec la version 10 de PostgreSQL. Il s’agit d’une fonctionnalité très proche des séquences, mais implémentant dans PostgreSQL ce qui est défini dans la norme SQL ISO.

Les types de données SERIAL et BIGSERIAL créent automatiquement une séquence et utilisent la fonction nextval() dans l’expression de la valeur par défaut de la colonne.

Dans une table comme la table prestations, la clé primaire est définie comme étant un entier. Il est possible d’utiliser une séquence pour l’alimenter automatiquement. L’exemple suivant montre l’usage le plus simple :

CREATE TABLE prestations (   prest_id serial primary key ,  
   [ ... ]   
) ; 
 

qui est l’équivalent...

Types de données

Il existe de nombreux types de données dans PostgreSQL, correspondant pour la plupart aux types de données de la norme SQL.

Domaines

Un domaine de données est une extension d’un type de données, associé à des contraintes et des vérifications qui permettent de ne définir qu’une fois cet ensemble et de le réutiliser dans plusieurs tables. Par exemple, un champ adresse_email peut être défini comme domaine et réutilisé dans toute une application.

CREATE DOMAIN nom [AS] typedonnee  
    [ DEFAULT expression ]  
    [ [ CONSTRAINT contrainte ]  
{ NOT NULL | NULL | CHECK (expression) } ] 
 

ALTER DOMAIN nom { SET DEFAULT expression | DROP DEFAULT } 
ALTER DOMAIN nom { SET | DROP } NOT NULL 
ALTER DOMAIN nom ADD contrainte [ NOT VALID ] 
ALTER DOMAIN nom DROP CONSTRAINT [ IF EXISTS ] contrainte 
[ RESTRICT | CASCADE ] 
ALTER DOMAIN...

Recherche textuelle

La recherche textuelle dans PostgreSQL est un ensemble de fonctions permettant l’analyse et l’indexation de textes en utilisant des racines des mots de ce texte, tout en fournissant des éléments de tris, en fonction de la proximité du résultat avec la recherche.

Contrairement aux expressions régulières ou à l’opérateur LIKE, ce type de recherche permet l’utilisation d’index améliorant les performances et facilite la recherche des racines de mots, en fonction d’une langue, permettant ainsi des recherches sur des mots similaires.

Les mots sont convertis en racines, ou lexèmes, permettant le rapprochement de mots similaires ayant la même racine.

Une recherche dans un texte est la correspondance entre un document de type tsvector, et une requête de type tsquery. Cette correspondance est vraie ou fausse et déterminée par l’opérateur @@.

Recherche dans une table

La recherche sur une ou plusieurs colonnes de type text dans une table passe par la conversion vers un document tsvector, ce qui permet de l’associer à une recherche tsquery. Par exemple, la requête suivante permet de rechercher le mot-clé ’Linux’ dans le champ plandecours de la table formations :

SELECT * FROM formations WHERE to_tsvector(plandecours) @@ 
to_tsquery( 'Linux');
 

Afin d’améliorer...

Extensions

Les extensions permettent d’ajouter des fonctionnalités à PostgreSQL.

Une extension est un ensemble cohérent de fonctions, types de données, opérateurs ou tout autre objet utile, fournissant une nouvelle fonctionnalité à PostgreSQL. Une extension peut être fournie par PostgreSQL ou par n’importe quel autre fournisseur.

Ces extensions peuvent aussi bien fournir de nouveaux types de données, comme ltree ou prefix, que des outils d’administration comme pg_stat_statement ou pg_buffercache.

Lorsque PostgreSQL est installé par un système de paquetages, comme sur les distributions GNU/Linux Debian, Ubuntu, Red Hat ou CentOS, un paquet spécifique contient les extensions fournies par PostgreSQL : postgresql-contrib-10 pour Debian et Ubuntu ou postgresql10-contrib pour Red Hat et CentOS.

Des extensions non fournies par PostgreSQL sont installables par le système de paquetages. Chaque extension alors fait l’objet d’un paquet spécifique, par exemple postgresql-10-ip4r pour Debian et Ubuntu ou ip4r10 pour Red Hat ou CentOS.

Ces paquets font référence à une version majeure spécifique de PostgreSQL, d’une part parce que la plupart d’entre elles sont écrites en langage C, et doivent donc être compilées en fonction de la version choisie, et d’autre part parce que les fichiers doivent être installés dans l’arborescence de la version majeure de PostgreSQL.

Une fois les fichiers contenant le code de l’extension installé, cette extension n’est pas immédiatement disponible. Il reste à créer les objets dans la base de données souhaitée, pour permettre leur utilisation, par exemple dans la création d’une table pour un type de données ou dans une requête SELECT pour une fonction.

CREATE EXTENSION [ IF NOT EXISTS ] extension 
    [ WITH ] [ SCHEMA nomschema ] 
    [ VERSION version ] [ FROM ancienneversion ] 
    [ CASCADE ] 
 

Opérateurs et fonctions

Manipulation des données

[ WITH [ RECURSIVE ] requête_cte [, ...] ]  
INSERT INTO table   
    [ ( colonne [, ...] ) ]   
    {   
    DEFAULT VALUES |   
         VALUES ( { expression | DEFAULT } [, ...] ) |   
         requête   
          }   
    [ ON CONFLICT [ ON CONSTRAINT contrainte ]  
      DO NOTHING | DO UPDATE SET { colonne = expression } [, ...]
[ WHERE condition ] ]  
   [ RETURNING * | expr ] 

INSERT INTO clients (cl_nom, cl_adresse) VALUES ('S.T.E.R.E.G', 
'2 rue des Incas, 25340 St-Anois') ; 
 

INSERT INTO prestations VALUES ( 1 , 'intitule' ,  
 ,  '' , '13/06/2006', '12/06/2006', true ,'') ;