Partie 1 · Le cas

Le cas unitee et le modèle de données

Seconde moitié du cours : une étude de cas (« Veille automatisée des marchés publics, secteur bâtiment modulaire, bâtiment en kit et assemblage rapide », SQL avancé : procédures, fonctions, triggers, transactions, sauvegarde, automatisation). L'entreprise unitee conçoit, assemble et déploie des bâtiments modulaires, des structures préfabriquées, des bases vie de chantier, des classes temporaires et des extensions rapides pour des clients publics et parapublics.

Aujourd'hui la veille est manuelle : plusieurs collaborateurs consultent différents portails, ce qui provoque des pertes de temps, des doublons, des oublis et une qualification irrégulière. La direction veut une base relationnelle qui centralise les annonces, calcule un niveau de pertinence, suit les événements techniques, déclenche des notifications et produit un tableau de bord. Elle ne veut pas « un simple ensemble de tables » mais un système SQL robuste, industrialisable et supervisable.

Ce que le système doit faire : récupérer des annonces depuis plusieurs sources ; détecter et empêcher les doublons ; évaluer les marchés selon des critères métier ; générer une alerte e-mail pour les opportunités critiques ; laisser une trace de toute opération importante ; prévoir une sauvegarde complète ; fournir un dashboard.

Remarque : le cours ne fixe pas de SGBD (il évoque ENUM, LONGTEXT, DATETIME, DECIMAL). Les exercices de ce site tournent sur SQLite : la syntaxe est parfois adaptée (exemple ajouté).

Les cinq volets

Volet	Objectif	Détails attendus
Collecte	recevoir ou intégrer de nouvelles annonces	API, scraping, flux RSS ; planification via CRON ; formats JSON, XML, HTML ; déduplication initiale
Qualification	évaluer et prioriser selon la pertinence	score (mots-clés, montant, localisation) ; niveau faible, moyen, élevé ; stockage du score
Traçabilité	suivi complet des actions techniques et métier	logs techniques (erreurs, appels API) ; logs métier (qualification, modification) ; historisation ; auditabilité
Sauvegarde	sécurité et restauration des données	sauvegardes automatiques quotidiennes/hebdomadaires ; export SQL ou dump ; versionning ; stockage externe possible
Pilotage	analyser et suivre via des indicateurs	dashboard (graphiques, KPI) ; statistiques ; filtres ; historique et tendances

Vérifie que tu as compris

Quel volet du cas est chargé de l'« auditabilité » et de l'historisation des changements ?

Données et modélisation

Chaque annonce peut contenir : identifiant externe (VARCHAR(100), pas forcément unique globalement : à utiliser avec la source, clé composite), source (table référentiel recommandée), titre (VARCHAR(255), index pour recherche rapide), résumé (TEXT, peut être NULL), description détaillée (LONGTEXT), acheteur public (normalisation possible : table acheteur), localisation (VARCHAR(150), peut être découpée en ville, département, région), date de publication (DATETIME, index important), date limite de réponse (DATETIME, permet des alertes), budget estimatif (DECIMAL(15,2), peut être NULL), lien source (TEXT, doit être unique si possible), mots-clés détectés (idéalement table mot_cle + relation N:N), statut de traitement (ENUM ou VARCHAR(50), ex : NEW, QUALIFIED, IGNORED, RESPONDED).

La partie modélisation demande : identifier les entités, proposer tables principales et tables de support, définir clés primaires et étrangères, préciser les contraintes d'unicité pertinentes, proposer une stratégie d'indexation minimale, et justifier les tables techniques de logs, notifications et sauvegardes. Le schéma doit servir à la fois l'exploitation métier et la supervision technique.

À toi de coder

Empêcher les doublons : clé composite (source, identifiant externe)

Cas unitee : une même annonce peut être relayée par plusieurs sources, et l'identifiant externe n'est pas unique globalement : on l'utilise avec la source (clé composite). Les doublons doivent être détectés et empêchés.

Complète la contrainte d'unicité sur (source_id, ext_id) et utilise l'insertion qui ignore les doublons. Le même X1 venant de deux sources différentes doit être conservé deux fois ; le doublon exact (même source, même X1) doit être ignoré. (Syntaxe SQLite : exemple adapté ; le cours ne fixe pas le SGBD.)

import sqlite3
con = sqlite3.connect(":memory:", isolation_level=None)
con.executescript("""
CREATE TABLE source(id INTEGER PRIMARY KEY, nom TEXT UNIQUE);
INSERT INTO source(nom) VALUES ('BOAMP'), ('TED');
CREATE TABLE marche(
  id INTEGER PRIMARY KEY,
  source_id INTEGER REFERENCES source(id),
  ext_id TEXT,
  titre TEXT,
  UNIQUE (___, ___)
);
""")
annonces = [(1, "X1", "Classes modulaires"), (1, "X1", "Classes modulaires (relais)"),
            (2, "X1", "Classes modulaires"), (1, "X2", "Base vie")]
for a in annonces:
    con.execute("INSERT ___ IGNORE INTO marche(source_id, ext_id, titre) VALUES (?, ?, ?)", a)
nb = con.execute("SELECT COUNT(*) FROM marche").fetchone()[0]
nb_x1 = con.execute("SELECT COUNT(*) FROM marche WHERE ext_id = 'X1'").fetchone()[0]

Ctrl/⌘+Entrée

À toi de coder

Indexer la date de publication

Le cas précise que la date de publication est un « index important » (et le titre : index pour recherche rapide). Un index évite de parcourir toute la table. EXPLAIN QUERY PLAN (SQLite) montre ce que le moteur compte faire.

Complète la création de l'index idx_marche_date sur date_pub. Le plan avant l'index parcourt la table (SCAN), le plan après utilise l'index.

import sqlite3
con = sqlite3.connect(":memory:", isolation_level=None)
con.execute("CREATE TABLE marche(id INTEGER PRIMARY KEY, titre TEXT, date_pub TEXT)")
con.executemany("INSERT INTO marche(titre, date_pub) VALUES (?, ?)", [(f"m{i}", f"2024-03-{i % 28 + 1:02d}") for i in range(200)])
req = "SELECT * FROM marche WHERE date_pub = '2024-03-02'"
plan_avant = " ".join(str(r) for r in con.execute("EXPLAIN QUERY PLAN " + req))
con.execute("CREATE INDEX idx_marche_date ON marche(___)")
plan_apres = " ".join(str(r) for r in con.execute("EXPLAIN QUERY PLAN " + req))

Ctrl/⌘+Entrée