Projets de bases de données · ESILV · 2023
Bases de données
D'une première base pour une boutique de fleurs, en binôme, à l'optimisation d'une base Oracle de 8 845 lignes de commande : deux projets qui montrent le chemin entre une base qui marche et une base qui tient. Les requêtes du rapport tournent ici sur les mêmes données, les déclencheurs se testent en direct, et la requête d'inscription de l'application se laisse démonter caractère par caractère.
Démonstrations web réalisées avec une assistance d'IA, à partir des scripts SQL, du code et des rapports des projets.
4e année · Advanced Database Management · Oracle
Classer sans sous-requête
Les fonctions analytiques (OVER (…)) calculent sur un groupe de lignes sans
les fusionner : un rang, une moyenne sur les lignes voisines, une part du total. Les requêtes ci-dessous
sont celles du projet, recalculées sur ses données : les résultats sont ceux des captures d'Oracle du
rapport.
Trois façons de numéroter les ex æquo : RANK saute des places (1, 2, 2, 4), DENSE_RANK n'en saute pas (1, 2, 2, 3), ROW_NUMBER départage arbitrairement (1, 2, 3, 4).
Survolez ou parcourez une ligne au clavier : la fenêtre qui sert à sa moyenne s'allume. Le projet utilisait 2 lignes précédentes, soit 3 commandes par moyenne.
Lire un plan d'exécution
Avant d'exécuter une requête, Oracle choisit un plan : quelles tables lire en entier, quels index suivre,
dans quel ordre joindre. EXPLAIN PLAN le montre, avec un coût estimé. Pour
les questions F, G et H du rapport, nous avons écrit une première requête, lu son plan, puis précalculé
l'agrégat dans une vue matérialisée.
-
F. Les dix fournisseurs au plus fort taux de pièces rejetées
44 5Voir les deux plans
SELECT STATEMENT coût 44 SORT ORDER BY VIEW WINDOW SORT PUSHED RANK NESTED LOOPS (deux sous-requêtes corrélées par fournisseur, chacune avec TABLE ACCESS FULL de PURCHASEORDERHEADER)SELECT STATEMENT coût 5 SORT ORDER BY VIEW WINDOW SORT PUSHED RANK MAT_VIEW ACCESS FULL MV_VENDOR_REJECTION_RATE -
G. Les dix fournisseurs aux plus grosses quantités commandées
22 4Voir les deux plans
SELECT STATEMENT coût 22 SORT ORDER BY VIEW WINDOW SORT PUSHED RANK HASH GROUP BY NESTED LOOPS TABLE ACCESS FULL PURCHASEORDERDETAIL (8 845 lignes) INDEX UNIQUE SCAN PK_PURCHASEORDERHEADER INDEX UNIQUE SCAN PK_VENDORSELECT STATEMENT coût 4 VIEW WINDOW SORT PUSHED RANK MAT_VIEW ACCESS FULL MV_VENDOR_ORDER_QUANTITY (86 lignes) -
H. Les dix produits les plus commandés
20 4Voir les deux plans
SELECT STATEMENT coût 20 SORT ORDER BY VIEW WINDOW SORT PUSHED RANK HASH GROUP BY TABLE ACCESS FULL PURCHASEORDERDETAILSELECT STATEMENT coût 4 VIEW WINDOW SORT PUSHED RANK MAT_VIEW ACCESS FULL MV_PRODUCT_ORDER_QUANTITY (265 lignes)
Coûts estimés par l'optimiseur d'Oracle, relevés dans le rapport. Plans abrégés.
Un coût n'est pas une durée
Le coût est une estimation de l'optimiseur, en unités internes : il sert à comparer deux plans de la même base, pas à chronométrer. Le vrai gain se mesure à l'exécution.
Précalculer a un prix
Une vue matérialisée stocke le résultat : la requête G devient une lecture de 86 lignes au lieu de 8 845. Mais sans clause de rafraîchissement, Oracle la met à jour à la demande : sans REFRESH, elle montre l'état du jour de sa création.
Même exercice, autre base
Au TP 4 du même cours, douze requêtes sur une base de vols, d'avions et de pilotes : chacune écrite, expliquée, puis réécrite avec index et vues matérialisées, coût avant et après.
Deux déclencheurs qui se marchent dessus
Le projet demandait deux déclencheurs. Le premier (J) garde une trace de chaque ligne de commande modifiée et recalcule le sous-total de la commande. Le second (K) refuse un sous-total qui ne correspond pas aux lignes. Chacun a été testé seul, et chacun marchait. Testez-les sur les lignes de la commande de test du rapport (n° 10001).
| Ligne | Produit | Quantité | Prix unitaire | Action |
|---|
PurchaseOrderHeader · commande 10001
Sous-total enregistré : 200
Console SQL
| Ligne | Quantité | Prix | Enregistrée |
|---|---|---|---|
| vide | |||
K seul
Tentez un sous-total de 1 000 : K le compare à la somme des lignes, 200, et lève l'erreur ORA-20002, avec le message écrit dans le projet. Un sous-total de 200 passe.
J seul
Décochez K, puis modifiez une ligne : J copie l'ancienne ligne dans Transaction_History et ajuste le sous-total de la différence. C'est ainsi que le projet l'a testé.
Les deux ensemble
Avec K installé, modifier une ligne échoue : J met à jour l'en-tête, ce qui réveille K, qui relit la table des lignes pendant qu'on la modifie. Oracle l'interdit (table « en mutation ») et annule tout. Aucun des deux tests séparés ne pouvait le montrer.
Les messages d'erreur sont ceux d'Oracle en français ; ORA-20002 est tel que capturé dans le rapport. Le blocage des deux déclencheurs ensemble est déduit des règles d'Oracle : le projet ne les a jamais exécutés ensemble.
3e année · bases de données et interopérabilité · en binôme
Une boutique de fleurs, relue avec des yeux de sécurité
Premier vrai projet de base de données : une boutique de fleurs, avec ses clients, commandes, bouquets, fleurs, accessoires et magasins, dans MySQL. Nous avons conçu ensemble le schéma entité-association ; mon binôme a créé les tables, le jeu de données et une première version en console ; j'ai construit l'interface graphique en C# (WPF), relié ses écrans aux fonctions et corrigé les bogues trouvés en chemin, dans la base comme dans le code.
Trois ans plus tard, en cybersécurité, je relis l'écran d'inscription autrement. Sa requête est construite en collant les saisies entre des apostrophes, exactement comme ici : modifiez les champs et regardez la requête changer de forme.
Requête du projet, par concaténation
donnée saisie saisie devenue du code neutralisé par un commentaire
La même, paramétrée
Les valeurs partent à part, comme des données : aucune saisie ne peut changer la forme de la requête. Les écrans de compte du projet le faisaient déjà ; l'inscription, non.
Paramétrer, toujours
Une seule requête collée à la main suffit : ici, il suffit de remplir son code postal d'une certaine façon pour s'offrir le statut de fidélité « Or ».
Aucun secret en clair
Les mots de passe étaient stockés et comparés en clair, les numéros de carte aussi. Aujourd'hui : un hachage lent et salé (Argon2id) pour les premiers, et pour les seconds, ne jamais les stocker et laisser le prestataire de paiement les garder.
Pas de compte root
L'application se connectait en root, mot de passe écrit dans le code. Un compte dédié, limité aux tables utiles, et des identifiants hors du dépôt réduisent les dégâts d'une injection.
Ce que j'en ai retenu
- Filtrer après avoir trié Notre « top 10 des parts du total » filtrait avec ROWNUM avant tout tri : il renvoyait dix produits quelconques. La capture du rapport le montre ; nous ne l'avions pas remarqué. FETCH FIRST 10 ROWS ONLY, après ORDER BY, règle le problème.
- Lire un plan avant d'optimiser Lectures complètes, index, jointures imbriquées : le plan dit où part le travail. On optimise ce qu'il montre, pas ce qu'on imagine.
- Tester l'ensemble, pas seulement les pièces Deux déclencheurs corrects séparément peuvent se bloquer l'un l'autre. Pour un total dérivé, un calcul à la lecture (vue) ou une seule procédure est plus sûr qu'une cascade.
- Des types qui disent vrai Notre schéma rangeait les montants de l'en-tête de commande (sous-total, taxes, port) dans des colonnes texte (VARCHAR2). Un montant se stocke en NUMBER : sinon chaque calcul dépend d'une conversion implicite et du réglage régional.
- Paramétrer toutes les requêtes L'injection SQL n'a besoin que d'une requête construite par concaténation. Même un nom comme O'Brien suffisait à casser l'inscription.
- Penser à la fuite dès la conception Mots de passe hachés, cartes jamais stockées, droits minimaux : ce qui n'est pas dans la base ne peut pas en fuir.
Données : tables d'achats d'AdventureWorks, base d'exemple de Microsoft (licence MIT), utilisées telles quelles par le projet.