MetaModel et filtre

Version imprimable

1 pièce(s) jointe(s)

MetaModel et filtre

Salut à tous,

Je viens de mettre en place l’excellent article de SQLPro, sur La technique des métadonnées http://sqlpro.developpez.com/cours/m...n/metadonnees/

L'article se termine par requête pour faire l'interrogation du model
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT * FROM TR_PROSPECT_PRP PRP LEFT OUTER JOIN T_DONNEE_DON DON ON PRP.PRP_ID = DON.CRE_LIGNE WHERE DON.TBL_ID = 44 -- lien pour la table TR_PROSPECT_PRP AND DON.CAR_ID = 123 -- lien pour une caractéristique donnée, par exemple 'CHIFFRE D''AFFAIRE' AND CRE.CRE_VALEUR = valeur 1 -- valeur demandée pour la caractéristique sus mentionnée AND DON.CAR_ID = 475 AND CRE.CRE_VALEUR = valeur 2 AND DON.CAR_ID = 74 AND CRE.CRE_VALEUR = valeur 3 AND DON.CAR_ID = 7 AND CRE.CRE_VALEUR = valeur 4
Je voudrais essayer de mettre en place cette requête de manière dynamique un peu comme un filtre que l'utilisateur pourrait configuré (à la manière de TFS)

Pièce jointe 157869

Comment me conseilleriez vous de faire ?

03/10/2014, 10h32
SQLpro

Il faut travailler avec du SQL dynamique.
En gros construisez les jointures et la clause WHERE en fonction des cases cochées.

NOTA : aujourd'hui je récrirais cet article en utilisant du XML et non plus des tables de méta données. En effet, avec un SGBDR comme SQL Server dont le XML est "miseajourable" et indexable, cela devient facile de faire un méta modèle super light ! (et en plus performant...)

A +
03/10/2014, 10h40
rvzip64

Si c'est l'auteur qui répond c'est encore mieux :)
Merci pour l'information !

La seule modification que j'ai apporté à votre modèle est de passer en CRE_VALEUR en sql_variant , comme vous l'aviez conseillé dans un autre post.

Par la même occasion, si j'utilise du SQL dynamique, je suppose que le plus simple est de caster le type de la valeur en construisant la requête ?

Concernant le fichier XML , je ne pense pas l'utiliser (peut être à tord) vu que ma table DONNEES fait à l'heure actuelle 2 Go
03/10/2014, 10h54
SQLpro

Citation:

Envoyé par rvzip64

La seule modification que j'ai apporté à votre modèle est de passer en CRE_VALEUR en sql_variant , comme vous l'aviez conseillé dans un autre post.

Il y a des avantages et des inconvénients...

Citation:

Par la même occasion, si j'utilise du SQL dynamique, je suppose que le plus simple est de caster le type de la valeur en construisant la requête ?

C'est préférable.

Citation:

Concernant le fichier XML , je ne pense pas l'utiliser (peut être à tord) vu que ma table DONNEES fait à l'heure actuelle 2 Go

Tort (de tortueux) et non pas tord (de tordre). Mais peut être êtes vous tordu ? !!! :)
Et alors ? Avez-vous essayé ?
Pour ma part aujourd'hui je n'utilise plus que le XML dans ce genre de cas (plus efficace).

A +
03/10/2014, 13h07
rvzip64

Citation:

Tort (de tortueux) et non pas tord (de tordre). Mais peut être êtes vous tordu ? !!!

:mrgreen:

Citation:

Et alors ? Avez-vous essayé ?
Pour ma part aujourd'hui je n'utilise plus que le XML dans ce genre de cas (plus efficace).

Absolument pas !
Par simple curiosité, quel est la taille de votre plus gros fichier XML ?
03/10/2014, 14h08
rvzip64

Par le plus grand des hasards, y a pas déjà des librairies qui permettent de faire ça ?
03/10/2014, 14h44
aieeeuuuuu

Bonjour,

Je pense qu'il y a méprise.

A mon avis, ne parle pas d'un fichier XML pour contenir vos données, mais bien d'une colonne de type XML contenant les propriétés supplémentaires.

Pas besoin de librairies, vous pouvez manipuler le contenu de ces colonnes directement dans vos requêtes.
03/10/2014, 15h13
SQLpro

Jetez un coup d’œil la dessus :
http://blog.developpez.com/sqlpro/p1...vec-sql-server
Il va falloir que j'écrive un article sur ce sujet !

A +
03/10/2014, 15h15
rvzip64

Ah oui en effet, ça risque de changer pas mal de chose.
Je vais regarder ça :)

Voici un exemple complet, qui peut servir de point de départ :

1 - La table avec une colonne de méta données XML

Code:

1
2
3
4
5
6
CREATE TABLE T_PERSONNE_PRS
(PRS_ID           INT IDENTITY PRIMARY KEY,
 PRS_NOM          CHAR(32) NOT NULL,
 PRS_PRENOM_USUEL VARCHAR(25) NOT NULL,
 PRS_DATA         XML);
GO

2 - les données du test :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
INSERT INTO T_PERSONNE_PRS
VALUES
('DUPONT', 'Marc',
N'
<data>
   <date_de_naissance datatype="date">2001-01-12</date_de_naissance>
   <sexe datatype="char(5)">homme</sexe>
   <date_de_deces datatype="date">2014-07-18</date_de_deces>
</data>'),
('MARTIN', 'Élise',
N'
<data>
   <date_de_naissance datatype="date">1988-06-07</date_de_naissance>
   <sexe datatype="char(5)">femme</sexe>
   <telephone_fixe datatype="varchar(20)">0622441189</telephone_fixe>
</data>');
GO

Deux personnes sont insérées avec des métadonnées différentes.

3 - La requête magique, qui extrait toutes les méta données d'un seul coup avec affichage du type !

Code:

1
2
3
4
5
6
SELECT X.PRS_ID, PRS_NOM, PRS_PRENOM_USUEL,  
       Txml.XMLdata.value('fn:local-name(.)', 'NVARCHAR(256)') AS BALISE,
       Txml.XMLdata.value('.', 'NVARCHAR(256)') AS VALEUR,
       Txml.XMLdata.value('@datatype', 'NVARCHAR(256)') AS TYPE
FROM   T_PERSONNE_PRS AS X
       OUTER APPLY X.PRS_DATA.nodes('data/*') AS Txml(XMLdata);

Resultat :

Code:

1
2
3
4
5
6
7
8
PRS_ID   PRS_NOM    PRS_PRENOM_USUEL  BALISE              VALEUR          TYPE
-------- ---------- ----------------- ------------------- --------------- ----------
3        DUPONT     Marc              date_de_naissance   2001-01-12      date
3        DUPONT     Marc              sexe                homme           char(5)
3        DUPONT     Marc              date_de_deces       2014-07-18      date
4        MARTIN     Élise             date_de_naissance   1988-06-07      date
4        MARTIN     Élise             sexe                femme           char(5)
4        MARTIN     Élise             telephone_fixe      0622441189      varchar(20)

Il ne vous reste plus qu'à :
1) indexer le XML
2) faire de la requête finale une vue
3) implémenter la méthode modify sur le XML si vous voulez le modifier (ajout ou suppression d'un noeud, ou modification de la valeur dans un noeud existant).

A +