Code SQL invalide pour les clefs étrangères composites #14

Closed
Epithumia opened this Issue Nov 28, 2015 · 9 comments

Projects

None yet

2 participants

@Epithumia

MoCoDo traite les clés étrangères n-aires comme n clés étrangères séparées, ce qui a deux effets :

  • le premier est esthétique, à savoir qu'on a une flèche depuis chaque clé étrangère vers chaque clé primaire référencée. C'est sous-optimal (je préfère avoir des accolades et flécher de la clé étrangère double vers la clé primaire double) mais cela n'empêche pas (trop) la lecture.
  • le second effet est que le code (Oracle au moins) généré est invalide.

Prenons l’exemple des animaux donné par défaut sur la page Web de MoCoDo (les types sont là juste pour avoir quelque chose) :

CREATE TABLE "ANIMAL" (
  "code_espèce" Integer,
  "nom" Varchar2(20),
  "sexe" Varchar2(1),
  "date_naissance" Varchar2(20),
  "date_décès" Varchar2(20),
  "code_espèce mère" Integer,
  "nom mère" Varchar2(20),
  PRIMARY KEY("code_espèce", "nom")
);

Cela crée la table ANIMAL.

CREATE TABLE "A_PÈRE" (
  "code_espèce" None,
  "nom" None,
  "code_espèce père présumé" None,
  "nom père présumé" None,
  PRIMARY KEY("code_espèce", "nom", "code_espèce père présumé", "nom père présumé")
);

Cela crée la table A_PÈRE.

ALTER TABLE "A_PÈRE" ADD FOREIGN KEY ("code_espèce") REFERENCES "ANIMAL" ("code_espèce");
ALTER TABLE "A_PÈRE" ADD FOREIGN KEY ("nom") REFERENCES "ANIMAL" ("nom");
ALTER TABLE "A_PÈRE" ADD FOREIGN KEY ("code_espèce père présumé") REFERENCES "ANIMAL" ("code_espèce");
ALTER TABLE "A_PÈRE" ADD FOREIGN KEY ("nom père présumé") REFERENCES "ANIMAL" ("nom");

Echec x4, ce qui est parfaitement normal puisqu'un clé étrangère (et par extension l'instruction Foreign_Key) fait référence à une clé primaire ; si la clé primaire est double, la clé étrangère doit l'être aussi, sinon elle ne permet plus de vérifier l'intégrité du couple.

La bonne syntaxe est :

ALTER TABLE "A_PÈRE" ADD FOREIGN KEY ("code_espèce", "nom") REFERENCES "ANIMAL" ("code_espèce", "nom");
ALTER TABLE "A_PÈRE" ADD FOREIGN KEY ("code_espèce père présumé", "nom père présumé") REFERENCES "ANIMAL" ("code_espèce", "nom");

Idéalement, la représentation MLD dans MoCoDo devrait refléter cela. Plutôt que noter
A PÈRE: #code espèce->ANIMAL->code espèce, _#nom->ANIMAL->nom, _#code espèce père présumé->ANIMAL->code espèce, _#nom père présumé->ANIMAL->nom
il faudrait pouvoir noter
A PÈRE: (#code espèce,_#nom)->ANIMAL->(code espèce,nom), (_#code espèce père présumé, _#nom père présumé)->ANIMAL->(code espèce, nom)
Et la cerise sur la gâteau serait de faire une seule flèche (ou au moins un seul corps de flèche) depuis le couple clé étrangère vers le couple clé primaire.

@laowantong
Owner

Très intéressant. Ne me servant pas moi-même de cette fonctionnalité de Mocodo, et utilisant encore moins Oracle, j'avais peu de chance de détecter l'erreur. Juste pour être sûr, les problèmes que vous me signalez se répètent ailleurs pour le même MCD, n'est-ce pas? Merci de confirmer que les modifications souhaitées seraient celles-là.

Le cas échéant, le problème devrait pouvoir être réglé en ajoutant quelques expressions régulières dans ce gabarit. Je verrai alors si le même traitement devrait être appliqué aux autres dialectes de SQL.

Concernant le diagramme relationnel, la syntaxe que vous proposez est parfaitement pertinente, et ne devrait pas être plus difficile à générer. Par contre, l'exploiter graphiquement demanderait pas mal d'efforts. Et malheureusement, certaines améliorations de la sortie graphique (notamment les associations réflexives et le placement des cardinalités) sont prioritaires dans le temps que je peux consacrer à Mocodo en ce moment.

@laowantong laowantong self-assigned this Nov 28, 2015
@laowantong laowantong added the bug label Nov 28, 2015
@Epithumia

Pour le SQL les modifications seraient effectivement celles dans le gist. Je suspecte que pour les autres moteurs SQL les modifications seraient similaires, du coup.

@laowantong
Owner

Si vous en avez besoin dans l'immédiat, voilà un oracle.json patché. Attention, seules les clefs doubles sont gérées pour le moment (cela couvre normalement tous les cas signalés dans animaux.mcd). Dans la semaine, j'essaierai de rajouter la gestion des clefs triples et quadruples (je ne pense pas qu'il soit possible de généraliser à une arité quelconque avec juste des expressions régulières), de porter les modifications pour les autres moteurs, de mettre à jour les tests, et enfin de sortir une nouvelle version. Merci de votre contribution.

@Epithumia

Je vois plusieurs façons de traiter le tout, en fait, après en fonction de la représentation interne à MoCoDo il faudra voir ce qui est le plus simple.

Pour le passage MCD->MLD

  • Possibilité 1 :
    Dans le MCD, quand on détecte un import double(/triple/n-uple), on génère directement le fichier MLD en regroupant toutes les clés importées pour donner des relations de la forme (fk1,fk2...)->CIBLE->(pk1,pk2,...)
  • Possibilité 2 :
    On garde le passage MCD->MLD actuel, puis on applique une transformation itérative dessus (il faut en gros autant d'itérations que le plus gros import). Sur le fichier MLD1 "à l'ancienne" on applique une première règle de transformation similaire à celle ajoutée à oracle.json, à savoir que si on trouve en regexp qu'on a SOURCE: fk1->CIBLE->pk1, fk2->CIBLE->pk2 [, ], on concatène en SOURCE: (fk1,fk2)->CIBLE->(pk1,pk2), [, ]. ensuite on répète N-1 fois quasiment la même chose mais cette fois ci en capturant SOURCE: ()->CIBLE->(), fk3->CIBLE->pk3 [, ] et en concaténant de la même manière en SOURCE: (,fk3)->CIBLE->(,pk3), [, ].

Passage MLD->SQL
Idéalement, on a ma syntaxe, auquel cas c'est beaucoup plus simple à parser vers SQL, puisque SOURCE: ()->CIBLE->() devient ALTER TABLE SOURCE ADD FOREIGN KEY () REFERENCES CIBLE() et cela marche indépendamment du nombre de clés.

@laowantong
Owner

Oui, vous avez bien vu les deux possibilités:

  1. soit on modifie la génération du schéma relationnel interne de façon à représenter explicitement les migrations de clefs primaires composites. C'est un changement profond, qui demande à repenser certaines structure de données, ajouter du code et probablement revoir l'ensemble des gabarits de relation templates;
  2. soit on corrige a posteriori les représentations externes concernées (i. e., SQL + diagramme relationnel) en fixant une limite arbitraire sur l'arité maximale. C'est un changement superficiel, qui ne demande qu'à ajouter quelques expressions régulières dans les gabarits en question, comme j'ai commencé à le faire sur oracle.json.

Concernant le diagramme relationnel, dans l'un ou l'autre cas, ces modifications ne sont intéressantes, et même possibles, que si elles sont accompagnées du traitement graphique idoine (accolades ou fusion des corps de flèches).

Dans l'immédiat, je ne pense corriger que la partie « bogue » (i. e., erreur SQL), et ceci sans me replonger dans le code proprement dit. Pour la partie « amélioration graphique », je ne m'en occuperai qu'après avoir réglé les défauts, à mon sens autrement prévalents, que j'ai évoqués plus haut.

Bien entendu, vos priorités ne sont pas nécessairement les mêmes, et c'est pourquoi je reste ouvert à tout PR de votre part.

@laowantong laowantong pushed a commit that referenced this issue Dec 3, 2015
laowantong Fix: composite foreign keys in SQL templates
cf. #14
+ default values for missing data types
+ fix: incomplete primary keys in SQLite output
27f2dd8
@laowantong
Owner

La sortie SQL devrait maintenant être correcte dans les différents formats. J'ai ajouté une classe de tests dédiés, quelques mots dans la documentation et, dans les gabarits, des commentaires pour les expressions régulières les plus obscures. Bonne nouvelle: contrairement à ce que j'avais avancé, il ne devrait pas y avoir de limitation sur l'arité.

Et j'ai appris au passage que les clefs étrangères composites étaient déconseillées en pratique.

@laowantong laowantong changed the title from Code SQL invalide généré to Code SQL invalide pour les clefs étrangères composites Dec 3, 2015
@laowantong laowantong closed this Dec 3, 2015
@Epithumia

Super. Sinon détail, la question SO ne commente pas sur le fait que les clefs étrangères composites sont déconseillées ou non.

@laowantong
Owner

Oui, désolé, j'en avais plusieurs ouvertes, et j'ai copié le mauvais lien. C'est corrigé dans mon message précédent.

@laowantong
Owner

Et j'ai oublié de noter que j'en ai profité pour découvrir et corriger une autre erreur dans la génération SQL: j'oubliais de mettre en commentaire les contraintes de clefs étrangères référençant une table réduite à son identifiant, et donc mise elle-même en commentaire. Il y a des exemples dans les tests.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment