Structure récursive ou simple sous-requête ?

Bonjour à tous,

J'ai affaire à un problème de requête SQL qui me dépasse, et je n'ai pas trouvé de solution ni même une piste valable en recherchant sur le web (forums, FAQ, etc.)

Pour info, je travaille sur une base Hyperfile à partir d'un site web développé avec WebDev9, mais je crois que mon problème est indépendant du SGBD, c'est surtout le raisonnement de construction de la requête qui me pose problème, pas la syntaxe ou les performances pour le moment...

La base en elle-même a de très nombreuses tables, mais l'ironie de ma situation, c'est que la requête ne porte que sur une seule table PIECES (contenant toutes les pièces d'un ensemble de machines) :oops:

Les tables n'ont pas été créées à partir d'ordres SQL, mais grosso modo la structure de la table est la suivante :

CODE_PIECE (string, 210) (clef de la table)
APPARTENANCE (string, 50)
...
DESIGNATION (string, 200)
FABRICANT (string, 50)
et une trentaine d'autres colonnes sans lien avec ma requête

_______________________

Les pièces d'une machine sont classées selon une structure arborescente :
la colonne APPARTENANCE contient la valeur de CODE_PIECE de la pièce dont elle fait partie, à part les quelques pièces de niveau supérieur, auxquelles toutes les autres se rattachent directement ou indirectement (et qui ont donc une chaine vide dans la colonne APPARTENANCE)

Par exemple :
La pièce principale d'une machine :
[MOTEUR FTGP-573F] CODE_PIECE = "ABCDEF", APPARTENANCE = ""
Deux pièces :
[PARTIE_1_MOTEUR] CODE_PIECE = "XYZXYZ", APPARTENANCE = "ABCDEF" et
[PARTIE_2_MOTEUR] CODE_PIECE = "AZERTY", APPARTENANCE = "ABCDEF"

Et ainsi de suite :
[HELICE_1_PARTIE_1] CODE_PIECE = "UIOPQS", APPARTENANCE = "XYZXYZ"
...

...avec tout un arbre de pièces jusqu'à la dernière vis et au dernier écrou de la machine. :roll:

Et donc mon problème est le suivant : il faut que j'affiche la liste des pièces d'une machine en conservant la structure arborescente :

[MOTEUR FTGP-573F]
[PARTIE_1_MOTEUR]
[HELICE_1_PARTIE_1]
[SUPPORT_PALE_1_HELICE_1]
...
[HELICE_2_PARTIE_1]
...
[PARTIE_1_MOTEUR]
...

J'ai écarté pour le moment les questions de présentation des données (ordonnées et avec alinea pour faire comme une sorte de "treeview statique"). La difficulté qui m'arrête, c'est que je ne sais pas combien de "niveaux" il y a en-dessous de chaque pièce... comment faire ?

Mes élucubrations actuelles (qui je l'espère ne vont pas m'expédier vers le bétisier ou même directement sur la case "the daily WTF" :aie: ) vont vers ce genre de pistes :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

SELECT * FROM PIECES as Pn WHERE CODE_PIECE =   ( SELECT APPARTENANCE FROM PIECES as P2 WHERE CODE_PIECE =   ( SELECT APPARTENANCE FROM PIECES as P3 WHERE CODE_PIECE = 'code de la machine dont je veux la liste des pièces'

---

...mais avec un nombre variable de "niveaux" ... 8O


Hum... merci aux courageux qui sont arrivés jusqu'ici, et encore plus aux éventuels oufs qui auraient la gentillesse (et les capacités) de m'aider à passer cette embûche ^^

Comme tu ne peux pas savoir combien tu as de niveaux, une requête simple ne peux pas répondre à ton problème.
Il te faut donc soit une requête récursive, soit une gestion applicative de ta nomenclature.

Je ne sais pas si WinDev prend en charge les requêtes récursives

Moi petit padawan pas bien parler ordinateur :oops:

Quand tu dis "gestion applicative de ma nomenclature"... tu veux dire : modifier la structure de ma BDD pour que l'écriture des requêtes soit plus facile ?
(comme dans cet exemple que je suis en train de lire par pure curiosité étant donné qu'ici je n'ai pas la liberté de modifier la BDD...)

...ou bien faire ce travail d'exploration des données en exploitant des structures de boucles imbriquées avec le code serveur qui reçoit les données ? (c'est-à-dire en "cousant" ensemble plusieurs requêtes plus simples)

la deuxième solution...

SQL est cpabale de parcourir des arbres et des graphes à l'aide de requête récursives depuis la norme SQL:1999.
Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...te-recursives/

Néanmoins je doute qu'Hyperfile dont le niveau de SQL est plus que très limité supporte ce genre de construction.
Si vous avez un volume de données important, orientez vous vers un SGBDR qui suporte ce genre de requêtes SQL (IBM DB2, SQL Server, Sybase, FireBird...).

Sinon il existe une alternative, c'est de modélisez votre arborescence à l'aide d'intervalle comme je l'ai décrit dans cet article :
http://sqlpro.developpez.com/cours/s...te-recursives/
Dès lors plus aucune réquête est récursive, mais cela nécessite d'utiliser des procédures stockée transactionnées. Or là aussi, vu la structure "fichier" de Hyperfile je doute que cela soit possible.

Bref, lorsque l'on a un outil inadapté il est peut être raisonnable d'en changer.

En outre sur la remarque de al_24 (qui d'habitude nous sort des choses judicieuses...) qui préconise la 2e solution, je vous la déconseille. En effet pour faire ceci et garder la cohérence des données il faudrait vérrouiller la totalité de la table pendant toute la durée du traitement et compte tenu du parcours itératif et multiples des lignes... vous risquez des performances catastrophiques !

Enfin sur la qualité de votre modèle de données :
1) l'utilisation d'une clef de table avec un "string" de 210 est un truc abérant... Pourquoi pas non plus pendant qu'on y est utiliser comme clef 666 lignes des versets du Coran piochés au hasard ?
2) le clou de la désolation est enfoncé lorsque l'on constate que la colonne APPARTENANCE est elle de longueur 50... et que vous allez mettre en jointure ces deux colonnes.
Soyez au moins cohérent, soit les 2 colonnes sont en 50 soit en 210. Mais si vous voulez un minimum de performances, mieux vaudrait que vos colonnes clef soit des entiers.
3) enfin il serait judicieux que vous normalisiez vos données notamment en externalisant tout ce qui peut être redondant. Exemple, la colonne constructeur n'a rien à faire dans votre table et devrait être dans une table des constructeurs auquel on fait référence par une jointure. Cela améliorera très notablement les performances à tous les niveaux (SELECT, INSERT...)

A +

Tout d'abord merci à vous deux pour tous ces conseils précieux :)

Alors voilà :

- cette base de données (et sa structure de données délirante), je ne l'ai ni voulue, ni conçue, je suis arrivé dans l'entreprise il y a deux mois, et en effet j'ai eu assez mal aux cheveux en constatant la manière dont elle était construite (je ne vous ai donné que les éléments utiles à ma question, mais des bizarreries comme celles pointées par SQLpro, il y en a beaucoup d'autres et c'est pour ma pomme :fou: )

- il m'est impossible de la changer (non pas techniquement mais parce que je ne suis pas le décisionnaire sur ce point), je vais donc essayer de trouver la solution la "moins pire" et sauver les meubles comme ça...

- merci pour le lien vers l'article sur les structures récursives, je vais aller lire ça de suite, ne serait-ce que pour ma culture personnelle. (d'ailleurs j'en profite pour te remercier (SQLpro) pour les nombreux articles sur SQL que tu as écrits et qui m'ont été particulièrement utiles jusqu'à ce jour :ave:

CONCLUSION : je crois que je vais faire une requête simple de sélection des pièces directement liées à une pièce donnée, et traiter seulement dans le code de ma page l'exploration de N niveaux fixés à l'avance et assumés comme suffisants. C'est une solution qui n'est pas satisfaisante dans l'absolu, mais qui, dans le contexte, permet peut-être de "sauver les meubles"...