Postgresql : schémas et friandises

Publié le 17 mars 2008 par greg

Allez, c'est promis, le prochain article portera sur autre chose que postgresql :o)

Aujourd'hui, au menu : la fonctionnalité derrière laquelle court l'équipe de développement de mysql : les schémas.

Qu'est ce qu'un schéma ?

Pour vous donner une illustration simple, un schéma est un peu l'équivalent d'une feuille de DBDesigner. Vous pouvez y mettre ce que bon vous semble, des tables, des vues, des séquences, des fonctions etc etc sauf des utilisateurs, ces derniers sont des objets qui sont transversals à tout le moteur de base de données.
Je laisse cette idée faire son chemin lentement, rendez vous compte ! On peut mettre plusieurs feuilles de schéma dans une base !

Reprenons notre base de données de bibliothèque avec nos 3 tables ouvrage, livre et magazine. Imaginons maintenant que nous devons développer une application de compatibilité pour notre bibliothèque, nous allons forcément ajouter des objets dans la base. Un schéma est le meilleur moyen de nos assurer que nous n'allons pas polluer la base existante.

test=> CREATE SCHEMA compta;
CREATE SCHEMA

Créons maintenant la table «transaction» qui va nous permettre de concentrer les dépenses faites sur les ouvrages :

test=> CREATE TABLE compta.depense (id SERIAL PRIMARY KEY, ouvrage_id INT NOT NULL, amount NUMERIC(5,2) NOT NULL, comment TEXT, CONSTRAINT prix_positif CHECK(amount >= 0));
NOTICE: CREATE TABLE will create implicit sequence "depense_id_seq" for serial column "depense.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depense_pkey" for table "depense"

CREATE TABLE
test=> d compta.depense
                                 Table "compta.depense"
   Column   |     Type     |                          Modifiers
------------+--------------+-------------------------------------------------------------
id         | integer      | not null default nextval('compta.depense_id_seq'::regclass)
ouvrage_id | integer      | not null
amount     | numeric(5,2) | not null
comment    | text         |
Indexes:
    "depense_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "prix_positif" CHECK (amount >= 0::numeric)

test=> INSERT INTO compta.depense (ouvrage_id, amount, comment) VALUES ( 6, 10.99, 'frais reliure');
INSERT 0 1
test=> SELECT * FROM compta.depense;
id | ouvrage_id | amount |    comment
----+------------+--------+---------------
1 |          6 | 10.99 | frais reliure
(1 row)

test=> _

Le tour est joué ! Nous avons un schéma à nous que nous pouvons peupler au grés de nos besoins. Il suffit d'indexer le nom des objets avec le nom du schéma pour les manipuler. Cependant et jusqu'à ce jour, nous n'avons jamais précisé de nom de schéma pour accéder aux objets que nous avions créés. Cela peut nous amener à penser qu'il existe un schéma par défaut. En fait oui et non. Si nous demandons à Postgresql de nous afficher la liste des tables de notre base de données voila ce qu'il nous donne :

test=> dt
          List of relations
Schema |   Name   | Type | Owner
--------+----------+-------+----------
public | emprunt | table | testuser
public | livre    | table | testuser
public | magazine | table | testuser
public | ouvrage | table | testuser
(4 rows)

test=> _

D'une part, il existe bien un schéma appelé «public» qui contient les objets que nous avions précédemment créés, d'autre part, au voleur ! Ou est passé notre table «dépense» ? Cela vient de ce qui s'appelle le «search_path». Le «search_path» est une variable propre à la session de votre client qui vous permet de définir quel schéma vous allez voir par défaut.

test=> SHOW search_path ;
search_path
----------------
"$user",public
(1 row)

test=> _

Cela signifie que par défaut, postgresql va vous montrer d'abord les objets du schéma qui porterait le nom de l'utilisateur postgresql avec lequel vous vous connectez (s'il existe) et ensuite les objets du schéma «public». Que se passe-t-il si nous demandons à postgres de nous montrer les éléments du schéma «compta» ?

test=> SET search_path TO compta;
SET
test=> dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
compta | depense | table | testuser
(1 row)

test=> _

Un peu rude peut être, nous allons sans doute avoir besoin des objets du schéma public, c'est en général une bonne idée de laisser ce schéma comme étant le dernier que postgresql vous donne.

test=> SET search_path TO compta, public;
SET
test=> dt
          List of relations
Schema |   Name   | Type | Owner
--------+----------+-------+----------
compta | depense | table | testuser
public | emprunt | table | testuser
public | lib_user | table | testuser
public | livre    | table | testuser
public | magazine | table | testuser
(5 rows)

test=> _

Les feuilles de schéma agissent un peu comme des calques sous photosh ... heu .. gimp, on voit d'abord les objets du schéma le plus proche puis les objets du schéma le plus lointain (qu'on a dit que c'était une bonne idée si c'était «public»). Que se passe-t-il alors si nous surchargeons un objet qui existe dans «public» ?

Par exemple la table ouvrage n'est pas des plus explicite pour notre comptable, nous allons la remplacer par une vue dans notre schéma «compta» :

test=> CREATE VIEW compta.ouvrage AS SELECT public.ouvrage.*, pg_catalog.pg_class.relname AS "type" FROM public.ouvrage, pg_class WHERE public.ouvrage.tableoid = pg_catalog.pg_class.oid;
CREATE VIEW
test=> SELECT * FROM ouvrage;
id | created_at | type
----+----------------------------+----------
6 | 2008-03-13 21:54:43.376901 | livre
4 | 2008-03-13 21:52:59.303918 | magazine
5 | 2008-03-13 21:53:04.249153 | magazine
8 | 2008-03-16 23:56:01.075842 | magazine
9 | 2008-03-16 23:56:01.075842 | magazine
(5 rows)

test=> _

Nous pouvons ainsi reconstruire dans notre schéma une base à partir des données des autres schémas. Vous remarquerez au passage que pour obtenir le type d'ouvrage, nous faisons appel à la relation «pg_class» du schéma «pg_catalog». Ce schéma est un schéma système au même tître que le «information_schema». Il permet de stocker les informations relatives à votre base de données sans vous embêter. Par défaut, vous ne le voyez par car il n'est pas dans le search_path.

Mais revenons à nos moutons, puisque nous pouvons créer une vision de la base de données orientée selon notre métier, allons y carrément :

test=> DROP VIEW ouvrage;
DROP VIEW
test=> CREATE VIEW
  ouvrage
AS
SELECT
    public.ouvrage.*,
    pg_catalog.pg_class.relname AS "type",
   sum (CASE WHEN compta.depense.amount IS NULL THEN 0 ELSE compta.depense.amount END) AS amount
FROM
    public.ouvrage
      LEFT JOIN
        compta.depense
      ON
        public.ouvrage.id = compta.depense.ouvrage_id,
    pg_class
WHERE
      public.ouvrage.tableoid = pg_catalog.pg_class.oid
GROUP BY
    public.ouvrage.id,
    public.ouvrage.created_at,
    pg_catalog.pg_class.relname
ORDER BY
    amount
DESC
;
CREATE VIEW
test=> SELECT * FROM ouvrage;
id |         created_at         |   type   | amount
----+----------------------------+----------+--------
6 | 2008-03-13 21:54:43.376901 | livre    | 10.99
4 | 2008-03-13 21:52:59.303918 | magazine |      0
5 | 2008-03-13 21:53:04.249153 | magazine |      0
8 | 2008-03-16 23:56:01.075842 | magazine |      0
9 | 2008-03-16 23:56:01.075842 | magazine |      0
(5 rows)

test=> _

Ce qui revient à définir dans le modèle de notre application de comptabilité, une nouvelle classe «ouvrage» qui répond de façon plus pertinente à notre besoin métier.

Conclusion
Les schémas sont des sortes de namespaces qui agissent un peu comme des feuilles de calques que l'on peut superposer afin d'orienter une base de données selon nos besoins. Cela permet de compartimenter notre base et en utilisant par exemple les permissions, empêcher que des applications viennent fouiller dans nos données tout en se laissant la liberté de ne laisser voir que ce qui est utile.

Pour ceux qui sont arrivés jusque là en un seul morceau, un dernier détail, notre