Comment faire des comparaisons multi-tables?

**bzn141** · 30/06/2018, 21h50

Bonjour,

J'ai, disons, une table d'objets:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

OBJ: IDOBJ, NOM, DATE_INS, ENV, PROPRIETE_OBJ_1, PROPRIETE_OBJ_2, PROPRIETE_OBJ_...

(où DATE_INS est la date d'insertion, et ENV son environnement - parmi 4: INT, REC, PPR, PRD)
liée (1:n) à plusieurs tables (une dizaine) sur le champ IDOBJ:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
DEP1: IDOBJ, PROPRIETE_DEP1_1, PROPRIETE_DEP1_2, …
DEP2: IDOBJ, PROPRIETE_DEP2_1, PROPRIETE_DEP2_2, …

avec parfois encore d'autres tables liées à une table DEPn.

Je voudrais comparer, globalement, les objets de mêmes noms les plus récents sur chacun des ENV.
Le résultat attendu serait:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
NOM     "Version"  INT      REC       PPR       PRD
------- ---------- -------- --------- --------- ---------
OBJ1     1         1        1         1         1
OBJ2     1         1        0         0         0 
OBJ2     2         0        1         1         1
OBJ3     1         1        0         0         0
OBJ3     2         0        1         0         0
OBJ3     3         0        0         0         1

avec Version: quelque chose comme un checksum de l'objet complet tel que défini dans la table OBJ et les tables dépendantes.
et INT, REC, PPR, PRD: le nombre de fois (1 au plus par construction) où l'objet apparaît avec cette version dans chacun des environnements.
(Ce genre de présentation, sous Oracle, s'obtient avec PIVOT, mais je serais également content avec la présentation suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
NOM      "Version"    ENV
------- -------- -------
OBJ1     1            INT
OBJ1     1            REC
OBJ1     1            PPR
OBJ1     1            PRD
OBJ2     1            INT
OBJ2     2            REC
OBJ2     2            PPR
OBJ2     2            PRD
OBJ3     1            INT
OBJ3     2            REC
OBJ3     3            PRD

Mais comment puis calculer facilement un tel champ de "version" de mon objet?

Voilà le problème brut, si quelqu'un a une idée pour le mettre en œuvre simplement, je suis preneur!

Merci d'avance!

**StringBuilder** · 30/06/2018, 23h32

Le "version" serait par exemple quelque chose d'unique pour l'ensemble des propriétés de l'objet dans l'ensemble des stables liées ?

A froids, je dirais une concaténation de tous les attributs, avec un group_concat ou assimilé (si supporté par le SGBD) puis un checksum type MD5 dessus pour obtenir une valeur "unique" moins longue à manipuler.

Sous SQL Server, une sous-requête "for xml" ou "for json" permettra d'éviter de faire les concaténations manuelles pour obtenir une chaîne avec toutes les valeurs d'attributs.

Par exemple :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
select a.id, a.nom, 
	(
		select q.nom as question, r.valeur as reponse
		from jeu.reponse r
		inner join jeu.question q on q.id = r.question_id
		where r.animal_id = a.id
		for json path, root('Animaux')
	) as json
from jeu.animal a

Donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
id	nom	json
9	un chat	{"Animaux":[{"question":"Est-ce un félin ?","reponse":true}]}
2	Un cheval 	{"Animaux":[{"question":"Peut-on le monter? ","reponse":true}]}
6	un chevreuil	{"Animaux":[{"question":"Vit-il dans la forêt ?","reponse":true}]}
5	un chien	{"Animaux":[{"question":"Est-ce un canidé ?","reponse":true}]}
10	un lynx	NULL
7	une abeille	{"Animaux":[{"question":"Vit-il en colonies ?","reponse":true},{"question":"Est-ce un mammifère ?","reponse":false}]}
3	une carpe	{"Animaux":[{"question":"Est-ce un poisson ?","reponse":true}]}
8	une chauve-souris	{"Animaux":[{"question":"Est-ce un mammifère ?","reponse":true},{"question":"Est-ce un félin ?","reponse":false}]}
4	une mouche	{"Animaux":[{"question":"Est-ce un insect ?","reponse":true},{"question":"Vit-il en colonies ?","reponse":false}]}
1	Une vache 	{"Animaux":[{"question":"Peut-on le monter? ","reponse":false},{"question":"Est-ce un poisson ?","reponse":false},{"question":"Est-ce un insect ?","reponse":false},{"question":"Est-ce un canidé ?","reponse":false},{"question":"Vit-il dans la forêt ?","reponse":false}]}

Du coup :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
select a.id, a.nom, 
	HASHBYTES ('MD5', (
		select q.nom as question, r.valeur as reponse
		from jeu.reponse r
		inner join jeu.question q on q.id = r.question_id
		where r.animal_id = a.id
		for json path, root('Animaux')
	)) as md5
from jeu.animal a

Donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
id	nom	md5
9	un chat	0x9F0DE4F17F4BEFDB7D7DB7801A622FB3
2	Un cheval 	0x010DAFAA921227948A34C80089FFE0ED
6	un chevreuil	0x73E1E7B5F788185A2BDCA0F7FD36B8AB
5	un chien	0x2EB7473C03B11A98CFA0441496D4C248
10	un lynx	NULL
7	une abeille	0x06777E773E1AD64059FA5EFC8320B54A
3	une carpe	0x853C6CAB5A99EF28B85CA8EA64F8898B
8	une chauve-souris	0x86E4B5F926992973F20B045B799048CD
4	une mouche	0xAFC375FC57607225A2A028910432CA8F
1	Une vache 	0xEA26EAE51FF96E9F6D52DA0904D1CB51

**bzn141** · 01/07/2018, 00h50

C'est exactement cela que j'avais en tête… sauf qu'il s'agit d'une base Oracle 11, qui ne semble pas accepter le GROUP_CONCAT...
Du coup, je ne suis pas sûr de pouvoir mettre en œuvre cette solution…

L'une des difficultés est, en plus, que l'idéal serait d'établir dynamiquement la liste des champs de chacune des tables OBJ, DEP1, DEP2, … pour les concaténer (en enlevant juste l'IDOBJ, qui par définition sera différent d'une version à l'autre, et le champ ENV pour la même raison). Cela simplifiera la maintenance de la requête quand on ajoutera des propriétés supplémentaires à l'une ou l'autre table...

**bzn141** · 01/07/2018, 15h50

J'ai fait une première expérience, en me disant que j'allais utiliser les fonctions XML pour "concaténer" toutes les données liées à mon objet avant d'en calculer un cheksum ou md5.

Voici ma requête et le résultat:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT u.UPROC_NAME, u.ENVCODE, XMLELEMENT("Uproc", XMLFOREST(u.LABEL, u.VUPR, u.SHELL, u.UPT, u.APPLI)) AS UPR_DEFINITION
FROM
  G_UNI_UPROCS u
WHERE
  UPROC_NAME='SOC_EXTPRM';
 
UPROC_NAME	ENVCODE	UPR_DEFINITION
------------	-------	------------------------------------------------------------------------------------
SOC_EXTPRM	INT	<Error>
SOC_EXTPRM	PRD	<Error>
SOC_EXTPRM	REC	<Error>
SOC_EXTPRM	PPR	<Error>

Je ne vois pas ce qui cause l'erreur, car ma requête est en tout point semblable à celle donnée sur le site Oracle: https://docs.oracle.com/en/database/...XMLFOREST.html

Précisons que mon "OBJET" est une "UPROC", et les différentes propriétés sont u.LABEL, u.VUPR, u.SHELL, u.UPT, u.APPLI; je n'ai pas encore traité les tables liées puisque déjà je ne parviens pas à mes fins…
Pour info, voici la requête sans les fonctions XML et le résultat:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT UPROC_NAME, ENVCODE, u.LABEL, u.VUPR, u.SHELL, u.UPT, u.APPLI FROM G_UNI_UPROCS u WHERE UPROC_NAME='SOC_EXTPRM';
 
UPROC_NAME	ENVCODE	LABEL					VUPR	SHELL	UPT	APPLI
-----------	-------	------------------------------------	----	-------	-------	-----
SOC_EXTPRM	INT	Extraction du parametrage des noeuds	0	DEFAULT	CL_INT	DU
SOC_EXTPRM	PRD	Extraction du parametrage des noeuds	0	DEFAULT	CL_INT	DU
SOC_EXTPRM	REC	Extraction du parametrage des noeuds	0	DEFAULT	CL_INT	DU
SOC_EXTPRM	PPR	Extraction du parametrage des noeuds	0	DEFAULT	CL_INT	DU

**bzn141** · 02/07/2018, 10h54

Bonjour,
En fait, ma requête fonctionne correctement: seul le client SQL utilisé (Squirrel 3.5.3) semble ne pas aimer le XML! Quand j'aurais finalisé une solution, je la posterai, mais je pense être en bonne voie.