Comment faire des comparaisons multi-tables?

Version imprimable

Bonjour,

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

Code:

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:

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:

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:

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!

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:

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:

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:

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:

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

01/07/2018, 00h50
bzn141

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...

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:

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:

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

02/07/2018, 10h54
bzn141

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.
02/07/2018, 14h50
Waldar

Pas besoin de passer par du XML avec Oracle :
https://docs.oracle.com/cd/E11882_01...htm#SQLRF30030

Je connais ce LISTAGG. Mais cela ne correspond pas à mon besoin en l'occurrence. En revanche, générer un XML avec l'objet, ses propriétés et tous les sous-éléments liés, ça me convient bien davantage! Mais merci tout de même.

Et un super lien pour générer un tel xml: http://www.experience-developpement....ns-vos-select/

Voici un embryon de ma solution, sachant que j'aurai une quinzaine de tables liées comme G_UNI_UPR_LAUNCHVAR_T:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
  u.UPROC_NAME,
  n.ENVCODE,
  XMLSERIALIZE(CONTENT XMLELEMENT(
    NAME "Uproc",
    XMLATTRIBUTES(u.UPROC_NAME AS "UPROC_NAME",u.VUPR AS "VUPR",u.LABEL AS "LABEL",u.UPT AS "UPT",u.SHELL AS "SHELL",u.IS_BINARY AS "IS_BINARY",u.CLFIL AS "CLFIL",u.FP_REF AS "FP_REF",u.APPLI AS "APPLI",u.DOM AS "DOM",u.DEF_INFO AS "DEF_INFO",u.MEMO_TYPE AS "MEMO_TYPE",u.MEMO_NUMBER AS "MEMO_NUMBER",u.UCLASS AS "UCLASS",u.MAIL_T AS "MAIL_T",u.MAIL_A AS "MAIL_A",u.MAIL_O AS "MAIL_O",u.MAIL_R AS "MAIL_R",u.MAIL_LOG_SEND AS "MAIL_LOG_SEND",u.MAIL_LOG_MAXSIZE AS "MAIL_LOG_MAXSIZE",u.MAIL_ENABLED AS "MAIL_ENABLED",u.MAIL_VERSION AS "MAIL_VERSION",u.AUTOPATCH AS "AUTOPATCH",u.INTERACTIVE AS "INTERACTIVE",u.AUTORETRY_COUNT AS "AUTORETRY_COUNT",u.AUTORETRY_DELAY_MM AS "AUTORETRY_DELAY_MM",u.STATUSMGT_STATUS AS "STATUSMGT_STATUS",u.STATUSMGT_TYPE AS "STATUSMGT_TYPE",u.STATUSMGT_FILE_TO_PARSE AS "STATUSMGT_FILE_TO_PARSE",u.STATUSMGT_OP AS "STATUSMGT_OP",u.STATUSMGT_PATTERN AS "STATUSMGT_PATTERN",to_char(u.LFORM) AS "LFORM" ),
    XMLELEMENT(
      NAME "tvars",
      (SELECT XMLAGG(XMLELEMENT(NAME "tvar", XMLATTRIBUTES(vt.VPOS AS "VPOS",vt.VNAME AS "VNAME",vt.VVALUE AS "VVALUE",vt.VLENGTH AS "VLENGTH"))) FROM G_UNI_UPR_LAUNCHVAR_T vt WHERE vt.UPRID=u.UPRID )
    )
)) AS UPR_DEFINITION
FROM
  USOCPRMP0.V_UNI_SRC_LASTEXT n
  INNER JOIN USOCPRMP0.G_UNI_UPROCS u ON u.EXTID=n.EXTID
WHERE
  UPROC_NAME='SOC_EXTPRM';

ce qui donne:

Code:

1
2
3
4
5
6
7
8
9
10
11
<Uproc UPROC_NAME="SOC_EXTPRM" VUPR="0" LABEL="Extraction du parametrage des noeuds" UPT="CL_INT" SHELL="DEFAULT" IS_BINARY="0" FP_REF="801" APPLI="DU" DOM="I" MEMO_TYPE="NONE" MEMO_NUMBER="0" MAIL_T="0" MAIL_A="0" MAIL_O="0" MAIL_R="0" MAIL_LOG_SEND="0" MAIL_LOG_MAXSIZE="0" MAIL_ENABLED="0" MAIL_VERSION="1" AUTOPATCH="0" INTERACTIVE="0" AUTORETRY_COUNT="0" AUTORETRY_DELAY_MM="0" STATUSMGT_STATUS="COMPLETED" STATUSMGT_TYPE="RET" STATUSMGT_OP="EQUAL" STATUSMGT_PATTERN="0">
	<tvars>
		<tvar VPOS="0" VNAME="SOCLEDU_TYPE" VVALUE="DUTKit" VLENGTH="10"></tvar>
		<tvar VPOS="0" VNAME="SOCIETE_A_EXTRAIRE" VVALUE="NONE" VLENGTH="6"></tvar>
		<tvar VPOS="0" VNAME="NOEUD_A_EXTRAIRE" VVALUE="NONE" VLENGTH="64"></tvar>
		<tvar VPOS="0" VNAME="SOCLEDU_DEBUG" VVALUE="OFF" VLENGTH="3"></tvar>
		<tvar VPOS="0" VNAME="ESPACE_A_EXTRAIRE" VLENGTH="1"></tvar>
		<tvar VPOS="0" VNAME="FIC_TO_INSERT" VLENGTH="255"></tvar>
		<tvar VPOS="0" VNAME="SOCLEDU_SIMU" VVALUE="N" VLENGTH="10"></tvar>
	</tvars>
</Uproc>

Il ne resterait donc plus, éventuellement, qu'à faire un hash du xml en question pour avoir la "version" de mon objet - en l'occurrence, une "uproc" [(c) Orsyp/Automic/CA]

Bon, sur la base de l'exemple donné dans mon précédent commentaire, je parviens à générer une arborescence XML qui contient tous les éléments de mon Objet (ici, une uproc).
Mais j'ai ensuite un problème pour le manipuler:
- Je ne peux pas calculer directement un ORA_HASH de l'objet XML, parce que c'est un CLOB (ORA_HASH donne un résultat aléatoire sur des CLOB).
- Si je découpe mon XML en n fragments de 4000 caractères convertis en CHAR, c'est bon pour les deux premiers fragments, mais si je veux calculer le troisième fragment, j'ai l'erreur "ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop petit"
- Les XML obtenus font dans les 50000 caractères au plus.

J'ai trouvé sur internet une fonction ORA_HASH_CLOB ainsi définie:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 CREATE OR REPLACE FUNCTION ora_hash_clob ( p_clob IN CLOB ) RETURN CLOB IS TYPE t_ora_hash_tab IS TABLE OF NUMBER INDEX BY binary_integer; l_ora_hash_tab t_ora_hash_tab; l_line VARCHAR2(4000); l_ora_hash_key NUMBER; l_ora_hash_clob_key CLOB; BEGIN FOR i in 1 .. ceil(length(p_clob)/4000) LOOP l_line := to_char(substr(p_clob, (i-1)*4000+1,4000)); SELECT ora_hash(l_line) INTO l_ora_hash_key FROM dual; l_ora_hash_tab(i) := l_ora_hash_key; END LOOP; FOR i IN 1..l_ora_hash_tab.count LOOP l_ora_hash_clob_key := l_ora_hash_clob_key || to_clob(l_ora_hash_tab(i)); END LOOP; RETURN l_ora_hash_clob_key; END;
Mais celle-ci génère également des erreurs...

Bref, si quelqu'un avait une solution... le problème étant que l'on ne peut pas utiliser directement le XML comme "version", en tout cas dans Oracle, parce que c'est un CLOB (pas de GROUP BY possible).

04/07/2018, 12h04
bzn141

Finalement, j'ai trouvé la solution pour calculer ma "version" d'objet:

Code:

UTL_RAW.CAST_TO_BINARY_INTEGER(DBMS_CRYPTO.HASH(UPR_DEFINITION,1))

où UPR_DEFINITION est mon contenu XML.
Il faut juste que l'utilisateur ait suffisamment de droits pour invoquer ces deux fonctions!