Données persistantes et réseau : SQL, HTTP, API.

• Modéliser des données en tables avec clés primaires et étrangères, en évitant la redondance.
• Écrire et optimiser des requêtes SQL sur des centaines de milliers de lignes.
• Exposer les données d’un robot via une API HTTP/JSON et les consommer depuis Python ou un navigateur.

Références : programme NSI terminale (bases de données), freeCodeCamp « Relational Database », Designing Data-Intensive Applications (Kleppmann) chapitres 1-3, MDN Web Docs.

Une seule table « mesure » avec les colonnes robot_nom, robot_modele, capteur_type… répéterait « R2, explorateur, ultrason, cm » 200 fois : gaspillage, et si le robot est renommé, il faut modifier 200 lignes (risque d’incohérence). La normalisation : chaque fait est stocké une fois, les tables se référencent par des clés. La clé primaire identifie une ligne ; la clé étrangère pointe vers la clé primaire d’une autre table. SQLite est une vraie base SQL dans un fichier, présente dans chaque téléphone et navigateur ; PostgreSQL et MySQL ajoutent le réseau et la concurrence.

JOIN (interne) : seulement les lignes qui ont une correspondance des deux côtés. LEFT JOIN : toutes les lignes de gauche, avec NULL à droite si rien ne correspond. Une jointure est mathématiquement un produit cartésien filtré ; le moteur SQL choisit l’algorithme (boucles imbriquées, hachage, tri-fusion — les mêmes que dans L03-L04) selon les index et les statistiques. Ce planificateur de requêtes est l’un des logiciels les plus sophistiqués qui existent : vous dites quoi, il décide comment. C’est le paradigme déclaratif.

Un index est un arbre B (cousin de l’AVL du module L03) sur une ou plusieurs colonnes : la recherche passe de O(n) (« SCAN ») à O(log n) (« SEARCH USING INDEX »). Il coûte de l’espace et ralentit un peu les insertions : on indexe les colonnes des WHERE et JOIN fréquents, pas tout. Une transaction garantit ACID : Atomicité (tout ou rien), Cohérence (les contraintes tiennent), Isolation (les transactions concurrentes ne se voient pas à moitié), Durabilité (une fois validé, c’est sur le disque même si le courant coupe). Un journal de robot écrit par transactions de 100 mesures survit à un arrêt brutal.

Codes : 200 OK, 201 créé, 400 requête invalide, 401/403 non autorisé, 404 introuvable, 500 erreur serveur. HTTPS = HTTP chiffré par TLS : sans lui, tout ce qui transite (mots de passe compris) est lisible par le réseau. Une API REST organise les URL par ressources (noms) et les actions par méthodes (verbes).

R1 = "SELECT r.nom FROM robot r JOIN participation p ON p.robot_id = r.id GROUP BY r.id HAVING COUNT(*) >= 2"
R2 = "SELECT m.nom, COUNT(p.robot_id) FROM mission m LEFT JOIN participation p ON p.mission_id = m.id GROUP BY m.id ORDER BY m.nom"
R3 = "SELECT r.nom, m.nom FROM participation p JOIN robot r ON r.id = p.robot_id JOIN mission m ON m.id = p.mission_id WHERE p.role IN ('explorateur', 'vedette') ORDER BY r.nom, m.nom"

Dans R2, COUNT(p.robot_id) (et non COUNT(*)) compte 0 pour la mission sans participant : COUNT(colonne) ignore les NULL.

def transferer_role(cur2, a, b, m):
    cur2.execute("SAVEPOINT t")
    try:
        role = cur2.execute("SELECT role FROM participation WHERE robot_id = ? AND mission_id = ?", (a, m)).fetchone()
        if role is None or cur2.execute("SELECT 1 FROM robot WHERE id = ?", (b,)).fetchone() is None:
            raise ValueError("transfert impossible")
        cur2.execute("DELETE FROM participation WHERE robot_id = ? AND mission_id = ?", (a, m))
        cur2.execute("INSERT INTO participation VALUES (?, ?, ?)", (b, m, role[0]))
        cur2.execute("RELEASE t")
    except Exception:
        cur2.execute("ROLLBACK TO t"); cur2.execute("RELEASE t"); raise

def analyser(texte):
    tete, _, corps = texte.partition("\r\n\r\n")
    lignes = tete.split("\r\n"); methode, cible, _ = lignes[0].split(" ")
    u = urlparse(cible); entetes = {}
    for l in lignes[1:]:
        k, _, v = l.partition(":"); entetes[k.strip().lower()] = v.strip()
    if entetes.get("content-type", "").startswith("application/json") and corps: corps = json.loads(corps)
    return {"methode": methode, "chemin": u.path, "params": parse_qs(u.query), "entetes": entetes, "corps": corps}

def valider(c):
    err = []; connues = {"action", "vitesse", "angle"}
    err += [f"{k}: inconnu" for k in c if k not in connues]
    a = c.get("action")
    if a not in ("avancer", "stop", "tourner"): err.append("action: obligatoire, parmi avancer/stop/tourner")
    def nombre(champ, lo, hi):
        if champ not in c: err.append(f"{champ}: obligatoire")
        elif not isinstance(c[champ], (int, float)) or not lo <= c[champ] <= hi: err.append(f"{champ}: nombre entre {lo} et {hi} attendu")
    if a == "avancer": nombre("vitesse", -1, 1)
    if a == "tourner": nombre("angle", -180, 180)
    return err

q("""SELECT capteur_id, t, valeur FROM mesure m
     WHERE valeur = (SELECT MAX(valeur) FROM mesure WHERE capteur_id = m.capteur_id)""")
q("""SELECT r.nom, COUNT(*) FROM mesure m JOIN capteur c ON m.capteur_id = c.id JOIN robot r ON c.robot_id = r.id
     WHERE c.type = 'ultrason' AND m.valeur < 20 GROUP BY r.nom""")
q("""SELECT CAST(t AS INTEGER) s, ROUND(AVG(valeur), 2) FROM mesure m JOIN capteur c ON m.capteur_id = c.id
     WHERE c.type = 'temperature' GROUP BY s ORDER BY 2 DESC LIMIT 3""")
q("""SELECT r.nom, c.type, COUNT(*), ROUND(AVG(valeur), 2),
            ROUND(SQRT(AVG(valeur * valeur) - AVG(valeur) * AVG(valeur)), 2)
     FROM mesure m JOIN capteur c ON m.capteur_id = c.id JOIN robot r ON c.robot_id = r.id
     GROUP BY r.nom, c.type""")

Si SQRT n’est pas disponible dans votre SQLite, calculez la variance en SQL et la racine en Python. La sous-requête corrélée de la question 1 est réévaluée par ligne : sur de gros volumes, préférez une jointure avec un GROUP BY.

CREATE TABLE participation (
  id INTEGER PRIMARY KEY,
  robot_id INTEGER NOT NULL REFERENCES robot(id),
  epreuve_id INTEGER NOT NULL REFERENCES epreuve(id),
  score REAL NOT NULL CHECK (score >= 0),
  temps REAL,
  UNIQUE (robot_id, epreuve_id)          -- un robot participe une fois par épreuve
);
CREATE INDEX idx_part_robot ON participation (robot_id);

Le classement « meilleur score par épreuve puis somme » demande une sous-requête groupée (meilleur score par (équipe, épreuve)) puis un GROUP BY équipe : deux niveaux d’agrégation, cas classique d’examen NSI.

def selection(t, p): return [l for l in t if p(l)]
def projection(t, cols): return [{c: l[c] for c in cols} for l in t]
def jointure_naive(a, b, ka, kb):
    return [{**x, **{f"b.{k}": v for k, v in y.items()}} for x in a for y in b if x[ka] == y[kb]]
def jointure_hachage(a, b, ka, kb):
    index = {}
    for y in b: index.setdefault(y[kb], []).append(y)
    return [{**x, **{f"b.{k}": v for k, v in y.items()}} for x in a for y in index.get(x[ka], [])]
def groupement(t, cle, agregats):
    groupes = {}
    for l in t: groupes.setdefault(l[cle], []).append(l)
    return [{cle: k, **{nom: f(g) for nom, f in agregats.items()}} for k, g in groupes.items()]

import time, random
A = [dict(id=i, x=random.random()) for i in range(20_000)]
B = [dict(a_id=random.randrange(20_000), y=random.random()) for _ in range(20_000)]
t0 = time.perf_counter(); n1 = len(jointure_naive(A, B, "id", "a_id")); t1 = time.perf_counter()
n2 = len(jointure_hachage(A, B, "id", "a_id")); t2 = time.perf_counter()
print(n1 == n2, f"naïve {t1 - t0:.1f} s, hachage {t2 - t1:.2f} s")

La jointure par hachage exige de tenir une table en mémoire ; si elle ne tient pas, ou si un index existe déjà sur la clé, ou si les tables sont minuscules, la boucle imbriquée (éventuellement avec index) gagne. Le planificateur choisit sur des estimations de taille — et se trompe parfois, d’où EXPLAIN.