---
jupytext:
  text_representation:
    extension: .md
    format_name: myst
    format_version: 0.13
    jupytext_version: 1.16.0
kernelspec:
  name: python3
  display_name: Python 3
---

# PostgreSQL avancé

```{code-cell} python
:tags: [hide-input]

import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import matplotlib.gridspec as gridspec
import numpy as np
import pandas as pd
import seaborn as sns

sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1)
```

> **Note** : Les exemples SQL de ce chapitre supposent une connexion à un serveur PostgreSQL. Ils sont présentés comme blocs illustratifs et ne sont pas exécutés dans ce notebook. Les cellules Python exécutables simulent certains comportements avec des structures de données Python standard.

## Types JSON et JSONB

PostgreSQL est l'un des rares SGBD relationnels à offrir un support natif et performant des données semi-structurées au format JSON.

```{prf:definition}
:label: ch13-def-json-jsonb

PostgreSQL propose deux types JSON :

- **`JSON`** : stockage textuel du document JSON, tel quel. La validation syntaxique est effectuée à l'insertion, mais aucune indexation interne n'est réalisée.
- **`JSONB`** (*JSON Binary*) : le document est décomposé et stocké dans un format binaire interne. Les clés sont dédupliquées et triées. L'accès aux champs est plus rapide, et les index GIN sont supportés. C'est le type recommandé dans la quasi-totalité des cas.
```

### Opérateurs JSON

```{prf:remark}
:label: ch13-rem-operateurs-json

Les principaux opérateurs sur JSON/JSONB :

| Opérateur | Description | Exemple |
|---|---|---|
| `->` | Accès à une clé, retourne JSON | `doc -> 'nom'` |
| `->>` | Accès à une clé, retourne TEXT | `doc ->> 'nom'` |
| `#>` | Accès par chemin (tableau de clés), retourne JSON | `doc #> '{adresse,ville}'` |
| `#>>` | Accès par chemin, retourne TEXT | `doc #>> '{adresse,ville}'` |
| `@>` | Contient (JSONB) | `doc @> '{"age": 30}'` |
| `<@` | Est contenu dans (JSONB) | |
| `?` | La clé existe (JSONB) | `doc ? 'email'` |
| `jsonb_each` | Décompose en lignes clé/valeur | |
```

Exemples illustratifs (PostgreSQL) :

```sql
-- Création d'une table avec colonne JSONB
CREATE TABLE produits (
    id      SERIAL PRIMARY KEY,
    nom     TEXT NOT NULL,
    details JSONB
);

INSERT INTO produits (nom, details) VALUES
  ('Ordinateur', '{"marque": "Lenko", "ram_go": 16, "tags": ["laptop","pro"]}'),
  ('Souris',     '{"marque": "Logitux", "sans_fil": true, "tags": ["peripherique"]}');

-- Lecture d'un champ
SELECT nom, details ->> 'marque' AS marque FROM produits;

-- Filtre sur une valeur JSON
SELECT nom FROM produits WHERE details @> '{"sans_fil": true}';

-- Existence d'une clé
SELECT nom FROM produits WHERE details ? 'ram_go';

-- Décomposer les paires clé/valeur
SELECT nom, cle, valeur
FROM   produits, jsonb_each_text(details);

-- Mise à jour d'un champ JSONB (opérateur ||)
UPDATE produits
SET    details = details || '{"stock": 42}'
WHERE  nom = 'Ordinateur';

-- Index GIN pour les recherches JSONB
CREATE INDEX idx_produits_details ON produits USING GIN (details);
```

```{prf:example}
:label: ch13-ex-json-python

Simulation Python du comportement de JSONB — accès et filtrage sur des documents JSON :
```

```{code-cell} python
import json

produits = [
    {"id": 1, "nom": "Ordinateur",
     "details": {"marque": "Lenko", "ram_go": 16, "tags": ["laptop","pro"]}},
    {"id": 2, "nom": "Souris",
     "details": {"marque": "Logitux", "sans_fil": True, "tags": ["peripherique"]}},
    {"id": 3, "nom": "Clavier",
     "details": {"marque": "Meccasoft", "mecanique": True, "ram_go": None,
                 "tags": ["peripherique","pro"]}},
]

# Équivalent de : details ->> 'marque'
print("Marques :", [p["details"].get("marque") for p in produits])

# Équivalent de : details @> '{"sans_fil": true}'
sans_fil = [p["nom"] for p in produits if p["details"].get("sans_fil")]
print("Sans fil :", sans_fil)

# Équivalent de : details ? 'ram_go'
avec_ram = [p["nom"] for p in produits if "ram_go" in p["details"]]
print("Avec clé ram_go :", avec_ram)

# Équivalent de jsonb_each_text — aplatir clé/valeur
rows = []
for p in produits:
    for k, v in p["details"].items():
        rows.append({"produit": p["nom"], "cle": k, "valeur": str(v)})
pd.DataFrame(rows).head(8)
```

## Tableaux (ARRAY)

```{prf:definition}
:label: ch13-def-array

PostgreSQL supporte les **tableaux** comme type natif. Une colonne peut contenir un tableau de n'importe quel type de base (`INTEGER[]`, `TEXT[]`, `REAL[]`…). Les tableaux sont indexés à partir de 1 en PostgreSQL.
```

Exemples illustratifs (PostgreSQL) :

```sql
CREATE TABLE projets (
    id   SERIAL PRIMARY KEY,
    nom  TEXT,
    tags TEXT[]
);

INSERT INTO projets VALUES (1, 'Alpha', ARRAY['urgent','web','frontend']);
INSERT INTO projets VALUES (2, 'Beta',  '{backend,api,urgent}');

-- Accès au premier élément
SELECT nom, tags[1] AS premier_tag FROM projets;

-- Recherche : le tableau contient-il 'urgent' ?
SELECT nom FROM projets WHERE 'urgent' = ANY(tags);

-- Tous les tags doivent être présents
SELECT nom FROM projets WHERE ARRAY['urgent','web'] <@ tags;

-- Développer un tableau en lignes
SELECT nom, unnest(tags) AS tag FROM projets;

-- Agrégation en tableau
SELECT ARRAY_AGG(nom ORDER BY nom) AS noms FROM projets;
```

```{prf:remark}
:label: ch13-rem-array-vs-json

Les tableaux sont préférables à JSONB quand les éléments sont homogènes (même type) et que l'on n'a pas besoin d'une structure imbriquée. Pour des structures hiérarchiques ou hétérogènes, JSONB est plus adapté.
```

## Types personnalisés : CREATE TYPE

```{prf:definition}
:label: ch13-def-create-type

PostgreSQL permet de définir des **types personnalisés** :

- **Type énuméré** (`ENUM`) : un ensemble fini de valeurs textuelles ordonnées.
- **Type composite** : un enregistrement avec des champs typés, similaire à un `struct`.
- **Type domaine** : un type de base avec des contraintes supplémentaires.
```

Exemples illustratifs (PostgreSQL) :

```sql
-- Type énuméré
CREATE TYPE statut_commande AS ENUM (
    'en_attente', 'validee', 'expediee', 'livree', 'annulee'
);

ALTER TABLE commandes ADD COLUMN statut statut_commande DEFAULT 'en_attente';

-- Les valeurs sont ordonnées : on peut comparer
SELECT * FROM commandes WHERE statut > 'validee';

-- Type composite
CREATE TYPE adresse AS (
    rue     TEXT,
    ville   TEXT,
    code_postal CHAR(5)
);

CREATE TABLE clients (
    id        SERIAL PRIMARY KEY,
    nom       TEXT,
    livraison adresse
);

INSERT INTO clients VALUES (1, 'Alice', ROW('12 rue des Roses', 'Paris', '75001'));

-- Accès aux champs du type composite
SELECT nom, (livraison).ville FROM clients;

-- Type domaine (contrainte sur un type de base)
CREATE DOMAIN email AS TEXT
    CHECK (VALUE ~ '^[^@]+@[^@]+\.[^@]+$');
```

## LATERAL JOIN

```{prf:definition}
:label: ch13-def-lateral

La clause `LATERAL` permet à une sous-requête dans le `FROM` de référencer des colonnes des tables listées avant elle dans le même `FROM`. C'est une forme de "boucle SQL" : pour chaque ligne de la table de gauche, la sous-requête est évaluée avec les valeurs de cette ligne.
```

Exemples illustratifs (PostgreSQL) :

```sql
-- Pour chaque client, récupérer ses 3 dernières commandes
SELECT c.nom, cmd.id, cmd.montant, cmd.date
FROM   clients c
CROSS  JOIN LATERAL (
    SELECT id, montant, date
    FROM   commandes
    WHERE  client_id = c.id
    ORDER  BY date DESC
    LIMIT  3
) AS cmd;

-- LATERAL avec une fonction retournant un ensemble
SELECT p.nom, tag
FROM   projets p
CROSS  JOIN LATERAL unnest(p.tags) AS tag;
```

```{prf:remark}
:label: ch13-rem-lateral-vs-sous-requete

Sans `LATERAL`, une sous-requête dans le `FROM` ne peut pas référencer d'autres tables du même `FROM` — elle est évaluée indépendamment. `LATERAL` lève cette restriction et est indispensable quand on veut appliquer une fonction de table ou une sous-requête corrélée ligne par ligne.
```

## Window functions avancées

```{prf:definition}
:label: ch13-def-window-avancee

Les **fonctions de fenêtre avancées** de PostgreSQL incluent des fonctions statistiques et de distribution non disponibles dans tous les SGBD :

- `MODE() WITHIN GROUP (ORDER BY col)` : valeur la plus fréquente.
- `PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col)` : percentile par interpolation continue.
- `PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY col)` : percentile discret (valeur réellement présente dans les données).
```

Exemples illustratifs (PostgreSQL) :

```sql
SELECT
    departement_id,
    MODE()              WITHIN GROUP (ORDER BY salaire)        AS salaire_mode,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salaire)       AS mediane_continue,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salaire)       AS mediane_discrete,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salaire)       AS p90
FROM employes
GROUP BY departement_id;
```

```{code-cell} python
# Simulation Python des percentiles et du mode
import statistics

donnees = {
    "Informatique": [4200, 4500, 3900, 4800, 4100],
    "Marketing":    [3800, 4100, 3500, 3800, 4400],
    "RH":           [3200, 3500, 3100, 3200, 3400],
}

rows = []
for dept, salaires in donnees.items():
    rows.append({
        "departement":          dept,
        "mode":                 statistics.mode(salaires),
        "mediane":              statistics.median(salaires),
        "p90":                  np.percentile(salaires, 90),
        "salaire_moyen":        round(statistics.mean(salaires), 1),
    })
pd.DataFrame(rows)
```

## Schémas et namespaces

```{prf:remark}
:label: ch13-rem-schemas

Un **schéma** PostgreSQL est un espace de noms qui regroupe des objets (tables, vues, fonctions, types…). Par défaut, tout est dans le schéma `public`. Les schémas permettent de :

- Organiser un grand nombre de tables par domaine fonctionnel.
- Permettre à différentes applications de partager une même base sans collision de noms.
- Contrôler les droits au niveau du schéma entier.

```sql
CREATE SCHEMA rh;
CREATE SCHEMA finance;

CREATE TABLE rh.employes      (...);
CREATE TABLE finance.budgets  (...);

-- Droits au niveau schéma
GRANT USAGE ON SCHEMA rh TO rh_team;
GRANT SELECT ON ALL TABLES IN SCHEMA rh TO rh_team;

-- search_path : ordre de recherche des schémas
SET search_path TO rh, public;
```

## Extensions

```{prf:remark}
:label: ch13-rem-extensions

PostgreSQL dispose d'un système d'extensions qui enrichit ses fonctionnalités sans modifier le code source :

| Extension | Rôle |
|---|---|
| `pg_stat_statements` | Statistiques sur les requêtes exécutées (temps, fréquence) |
| `uuid-ossp` | Génération d'UUID (RFC 4122) |
| `pgcrypto` | Fonctions cryptographiques (hachage, chiffrement) |
| `unaccent` | Suppression des accents pour la recherche |
| `postgis` | Support géospatial (voir chapitre suivant) |

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();   -- ex: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, rows
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;
```

## Full-text search

```{prf:definition}
:label: ch13-def-fts

PostgreSQL intègre un moteur de **recherche plein texte** (*full-text search*) basé sur deux types :

- `tsvector` : représentation normalisée d'un document (lexèmes, positions, poids).
- `tsquery` : requête de recherche (opérateurs `&`, `|`, `!`, `<->` pour la proximité).

Les fonctions `to_tsvector(langue, texte)` et `to_tsquery(langue, termes)` convertissent texte et requête. L'opérateur `@@` teste la correspondance. Un index `GIN` sur `tsvector` rend la recherche très rapide.
```

Exemples illustratifs (PostgreSQL) :

```sql
CREATE TABLE articles (
    id      SERIAL PRIMARY KEY,
    titre   TEXT,
    contenu TEXT,
    ts_doc  TSVECTOR GENERATED ALWAYS AS
             (to_tsvector('french', titre || ' ' || contenu)) STORED
);

CREATE INDEX idx_articles_fts ON articles USING GIN (ts_doc);

-- Recherche
SELECT titre
FROM   articles
WHERE  ts_doc @@ to_tsquery('french', 'base & données');

-- Mise en évidence des termes trouvés
SELECT titre,
       ts_headline('french', contenu, to_tsquery('french', 'base & données'))
FROM   articles
WHERE  ts_doc @@ to_tsquery('french', 'base & données');
```

## Visualisation : JSON vs JSONB

```{code-cell} python
:tags: [hide-input]

fig, axes = plt.subplots(1, 2, figsize=(13, 5))
palette = sns.color_palette("muted", 6)

# --- Schéma comparatif JSON vs JSONB ---
ax = axes[0]
ax.set_xlim(0, 10)
ax.set_ylim(0, 10)
ax.axis('off')
ax.set_title("JSON vs JSONB : stockage et accès", fontsize=12, fontweight='bold')

def box2(ax, x, y, w, h, color, text, fs=9):
    r = mpatches.FancyBboxPatch((x-w/2, y-h/2), w, h,
        boxstyle="round,pad=0.1", edgecolor='white', facecolor=color,
        alpha=0.85, linewidth=1.2, zorder=3)
    ax.add_patch(r)
    ax.text(x, y, text, ha='center', va='center', fontsize=fs,
            color='white', fontweight='bold', zorder=4, multialignment='center')

box2(ax, 2.5, 8.5, 3.5, 0.9, palette[0], "Type JSON\n(texte verbatim)", 9.5)
box2(ax, 7.5, 8.5, 3.5, 0.9, palette[2], "Type JSONB\n(binaire décomposé)", 9.5)

items_json = ["Stockage : texte brut", "Doublons de clés autorisés",
              "Ordre des clés préservé", "Pas d'index GIN natif",
              "Accès : parse à chaque fois", "INSERT rapide"]
items_jsonb = ["Stockage : binaire interne", "Clés dédupliquées",
               "Ordre des clés non garanti", "Index GIN supporté",
               "Accès : O(log n)", "INSERT légèrement plus lent"]

for i, txt in enumerate(items_json):
    y_pos = 7.2 - i * 0.9
    box2(ax, 2.5, y_pos, 3.5, 0.7, palette[0], txt, 8)
for i, txt in enumerate(items_jsonb):
    y_pos = 7.2 - i * 0.9
    box2(ax, 7.5, y_pos, 3.5, 0.7, palette[2], txt, 8)

# --- Benchmark opérateurs (simulé) ---
ax2 = axes[1]
ops = ['->', '->>', '@>', '?', '#>']
temps_json  = [1.0, 1.1, 8.5, 9.2, 1.2]   # ms (simulé)
temps_jsonb = [0.3, 0.3, 0.1, 0.1, 0.4]   # ms (simulé, avec index GIN)

x_pos = np.arange(len(ops))
w = 0.35
bars1 = ax2.bar(x_pos - w/2, temps_json,  w, label='JSON',  color=palette[0], alpha=0.8)
bars2 = ax2.bar(x_pos + w/2, temps_jsonb, w, label='JSONB', color=palette[2], alpha=0.8)
ax2.set_xticks(x_pos)
ax2.set_xticklabels(ops, fontsize=11)
ax2.set_ylabel("Temps relatif (normalisé)")
ax2.set_title("Opérateurs JSON vs JSONB\n(temps relatif simulé)", fontsize=12, fontweight='bold')
ax2.legend(fontsize=10)
ax2.set_ylim(0, 11)
ax2.text(0.5, -0.12, "Note : valeurs illustratives, avec index GIN sur JSONB",
         transform=ax2.transAxes, ha='center', fontsize=8, style='italic', color='gray')

plt.savefig("_build_pg_json_jsonb.png", dpi=120, bbox_inches='tight')
plt.show()
```

## Résumé

```{prf:remark}
:label: ch13-rem-synthese

PostgreSQL va bien au-delà du SQL standard et offre des fonctionnalités avancées qui en font un SGBD polyvalent :

**Données semi-structurées** :
- `JSONB` stocke les documents JSON sous forme binaire indexable ; préférer `JSONB` à `JSON` dans presque tous les cas.
- Les opérateurs `->`, `->>`, `@>`, `?` permettent un accès et un filtrage fins.

**Types riches** :
- Les tableaux (`ARRAY`) simplifient les listes homogènes sans table de jointure.
- Les types personnalisés (`ENUM`, types composites, domaines) renforcent l'intégrité des données.

**Requêtes avancées** :
- `LATERAL` permet des sous-requêtes corrélées dans le `FROM`.
- `PERCENTILE_CONT`, `PERCENTILE_DISC`, `MODE` enrichissent l'analyse statistique.

**Organisation et recherche** :
- Les schémas organisent les objets et isolent les droits.
- Les extensions (`uuid-ossp`, `pg_stat_statements`, `pgcrypto`) enrichissent les fonctionnalités.
- Le full-text search (`tsvector`, `tsquery`, index GIN) permet une recherche textuelle performante nativement.
```