Discussion :

[bzn141]

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]

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]

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]

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]

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.

[Waldar]

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

[bzn141]

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 :

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

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 :

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

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]

[bzn141]

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 :

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

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

[bzn141]

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

Code :

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

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!