The SQL Server 2-D Matrix Builder

Première partie : comment utiliser
« The SQL Server 2-D Matrix Builder »

Par Pascal Belaud, Microsoft France
http://blogs.msdn.com/Pascal
11 Février 2008 - Version 1.0

Sommaire

Pourquoi a-t’on besoin d’une matrice dans Microsoft SQL Server ?
Mode de licence de « The SQL Server 2-D Matrix Builder »
Installation de « The SQL Server 2-D Matrix Builder »
Vue d’ensemble de l’utilisation de « The SQL Server 2-D Matrix Builder »
Exemple d’utilisation n°1 : cas simple
Exemple d’utilisation n°2 : cas complexe
Exemple d’utilisation n°3 : cas encore plus complexe
Ensuite ?

Pourquoi a-t’on besoin d’une matrice dans Microsoft SQL Server ?

Le projet The SQL Server 2-D Matrix Builder est né d’un besoin que j’ai eu lors du développement de la plateforme de Reporting utilisée dans le projet Vinci, projet qui a pour vocation de gérer tout le backend nécessaire à l’organisation de l’événement Microsoft TechDays 2008 :

http://blogs.msdn.com/pascal/archive/tags/Vinci/default.aspx

Nous avons eu besoin à un moment donné de fournir l’agenda complet des TechDays 2008. Pour cela, nous souhaitions que la matrice qui soit renvoyée contienne :

• En colonne, la liste des timeslots disponibles (11H-12H, 13H-14H, 15H-16H…)
• En ligne, la liste des salles disponibles (Salle 242, Salle 243, Amphi Bleu…)
• A l’intersection des deux, le code de la session technique qui est joué (DEV302, DAT402…)

Or la difficulté de générer une telle requête vient du fait que la liste des colonnes à renvoyer est complètement dynamique (la liste des salles utilisées peut varier en fonction des situations). Et c’est là que m‘est venu l’idée de tirer profit de la puissance de l’intégration de .NET dans le moteur de base de données SQL Server 2005, appelée aussi SQLCLR.

Pour faire simple, la Figure 1 expose un schéma de tables assez classique.

Figure 1

Ce que nous souhaitons, c’est pouvoir appeler une procédure stockée (ou une requête SQL libre) capable de renvoyer le résultat se trouvant dans la Figure 2.

Figure 2

Haut de page

Mode de licence de « The SQL Server 2-D Matrix Builder »

Vous pouvez librement utiliser la librairie décrite dans cet article sans que son auteur ou Microsoft puisse être tenu pour responsable des conséquences induites par son utilisation, et ce qu’elles que puissent être celles-ci.

Vous trouverez les détails complets sur cet aspect sur le site CodePlex sur lequel ce projet a été publié :

http://www.codeplex.com/SQL2DMatrixBuilder

Haut de page

Installation de « The SQL Server 2-D Matrix Builder »

Ce projet a été développé en utilisant l’une des fonctionnalités de SQL Server 2005 les plus importantes  pour les développeurs, à savoir l’intégration de la plateforme .NET directement au niveau du moteur de la base de données (SQLCLR).

Avant de démarrer l’installation, vous devez avoir autorisé l’utilisation de .NET dans votre instance SQL Server. La Figure 2 montre comment faire cela.

Figure 3

Pour pouvoir déployer dans votre base de données SQL Server, l’Assembly .NET qui a été développée, il faut exécuter (en tant que system administrators [sa]) le code T-SQL se trouvant dans la Figure 1.

Figure 4

Il faut noter que cette assembly a été signée par son développeur, Pascal Belaud. Veillez bien à le vérifier dans les propriétés de la DLL (Figure 5, Figure 6 et Figure 7).

Figure 5

Figure 6

Figure 7

Haut de page

Vue d’ensemble de l’utilisation de « The SQL Server 2-D Matrix Builder »

Nous verrons dans les trois exemples  fournis comment utiliser en détail la procédure stockée [TheMatrix]. Nous allons décrire les paramètres attendus par cette procédure stockée (Figure 8).

Figure 8

Comme vous pouvez le constater, il va falloir définir les trois requêtes à utiliser pour décrire :

• Les en-têtes de colonnes à utiliser
• Les lignes se trouvant dans la première colonne
• Le contenu de la matrice

Pour ce faire, nous allons utiliser un User Defined Type spécifique qui a également été développé en .NET pour l’occasion : QueryBuilder (Figure 9)

Figure 9

Ce type va nous permettre de spécifier quel type de requête nous souhaitons exécuter (texte SQL libre ou procédure stockée). Nous allons également avoir la possibilité de déclarer les éventuels paramètres d’entrée nécessaire. Enfin, nous allons également avoir la possibilité de spécifier précisément le type qui est renvoyé par la requête.

Pour indiquer que nous souhaitons exécuter du texte SQL libre, nous devons invoquer, en T-SQL, la méthode statique New_QueryText tandis que si nous souhaitons exécuter une procédure stockée, c’est la méthode statique New_StoredProcedure qui doit être invoquée (Figure 10).

Figure 10

Si vos requêtes  nécessitent des paramètres en entrée, il est possible de spécifier ceux-ci en invoquant la méthode SetParameter (Figure 11).

Figure 11

Enfin, [TheMatrix] va être capable d’inférer automatiquement le type à utiliser pour gérer le retour de l’exécution de vos procédures stockées. Cependant, dans certains cas, cette inférence de type peut ne pas être judicieuse dans votre cas. Vous pouvez donc imposer le type à utiliser en invoquant les méthodes SetContentType, SetContentType2 et SetContentType3. La X montre un exemple d’utilisation de chacune des trois méthodes.

Figure 12

Les valeurs adéquates pour le paramètre dbtype sont l’une des valeurs de l’énumération .NET System.Data.SqlDbType (Figure 13).

Figure 13

A noter qu’enfin il est possible d’exécuter cette requête avec le mode diagnostic activé afin de vérifier que la procédure [TheMatrix] a bien compris votre intention. Pour cela, il suffit de positionner le paramètre @diagnose à 1. Dans ce cas, un log détaillé apparaîtra dans l’onglet « Messages » (Figure 14).

Figure 14

Haut de page

Exemple d’utilisation n°1 : cas simple

Imaginons que nous gèrerions la vie d’un collège et notamment des notions telles que :

• Les classes.
• Les matières.
• Les coefficients des matières enseignées dans telle ou telle classe.

Il est fort probable que ceci soit ramené à la création de trois tables dans notre base de données, une pour gérer les classes, une pour gérer les matières enseignées au collège et enfin une permettant de relier une classe à une matière et qui permet de saisir le coefficient affecté à la matière pour la classe en question (Figure 15).

Figure 15

L’objectif de ce projet est de bénéficier d’une procédure stockée capable de nous renvoyer les données précédentes sous la forme de la Figure 16.

Figure 16

On voit que la matrice renvoyée ici est totalement dynamique au niveau des lignes mais également, et surtout, au niveau des colonnes. On constate également que certains cellules de la matrice ne sont pas remplies car, par exemple, la matière Allemand n’est pas enseignée à la classe de 6°A et qu’en lieu et place de la valeur manquante, nous voyons qu’un champ NULL a été affecté.

Concernant les colonnes renvoyées, on constate qu’elles sont de deux catégories :

• La première colonne (="Matière"), qui indique ici le type de valeur que l’on trouve dans la première colonne.
• Le reste des colonnes, qui correspondent au contenu de la table [Education Nationale].[Classe] trié dans un ordre spécifique.

Concernant les données renvoyées dans la première colonne, elles correspondent au contenu de la table [Education Nationale].[Matière] trié dans un ordre spécifique.

Enfin, le contenu de la matrice proprement dite, il correspond au contenu de la table [Education Nationale].[MatièreClasse] et notamment la colonne MatièreClasse_Coefficient, qui est la valeur réellement affichée dans la matrice.

Si l’on ramène cela à des requêtes SQL, cela nous donne les requêtes visibles dans la Figure 17:

Figure 17

Il faut bien noter que, dans les deux cas, ces requêtes doivent renvoyer et une clé primaire et une valeur à afficher. Les clés primaires renvoyées serviront un peu plus tard à retrouver les bonnes valeurs pour les coefficients lors du remplissage de la matrice.

Pour définir ces requêtes au niveau de notre matrice, nous devons utiliser des variables de type QueryBuilder. Leur utilisation est on ne peut plus simple (Figure 18).

Figure 18

Nous devons, dans un premier temps, invoquer la méthode statique New_QueryText sur notre type QueryBuilder pour pouvoir lui fournir les requêtes, sous forme de texte, à utiliser.

Il faut maintenant mettre au point la requête qui  va nous permettre de remplir le contenu de la matrice. Nous devons pour cela renvoyer un jeu d’enregistrements contenant 3 colonnes :

• La clé primaire de la classe
• La clé primaire de la matière
• Le coefficient à afficher

La Figure 19 montre un exemple d’une telle requête SQL :

Figure 19

Il suffit maintenant de définir la variable de type QueryBuilder que l’on va utiliser pour définir cette requête. C’est ce que nous avons fait dans la Figure 20.

Figure 20

A partir de là, nous avons quasiment tout ce qu’il faut pour déclencher l’appel à la procédure stockée qui va nous générer en retour la matrice attendue. Pour cela, nous devons appeler la procédure stockée [TheMatrix] et lui fournir les informations suivantes :

• La requête pour remplir la première colonne
• La valeur à afficher dans l’en-tête de cette colonne
• La requête pour remplir les en-têtes des autres colonnes
• La requête pour remplir la matrice
• La valeur à utiliser en cas de donnée manquante dans la matrice

C’est ce qui a été fait dans la Figure 21.

Figure 21

L’exécution de cette requête donne lieu à la création de la matrice présentée à la Figure 22.

Figure 22

Haut de page

Exemple d’utilisation n°2 : cas complexe

Dans la base de données précédente, nous avons également des tables nous permettant de représenter la notion d’élèves, d’examens et de notes obtenues à ces examens (Figure 23).

Figure 23

L’idée désormais est de construire une matrice permettant de voir l’ensemble des notes des élèves d’une classe donnée, pour une matière donnée et pour un trimestre donné. La Figure 24 montre le type de retour que nous attendons.

Figure 24

Pour faire ceci, nous allons mélanger des appels à des procédures stockées mais également à des requêtes SQL de type texte. De plus, l’ensemble des appels que nous allons effectuer nécessite la fourniture de plusieurs paramètres en entrée, ce qui n’était pas le cas dans l’exemple n°1.

La liste des élèves de la première colonne est fournie par la procédure stockée [ListeDesElèvesParClasse] (Figure 25), procédure qui prend le paramètre @Classe_ID en entrée correspondant à l’identifiant de la classe pour laquelle nous souhaitons la liste des élèves.

Figure 25

La liste des colonnes correspond à la liste des examens effectués pour une matière donnée et un trimestre donnée. Celle-ci est fournie par la procédure stockée [ListeDesExamensParMatière] (Figure 26), procédure qui prend les paramètres suivants en entrée :

• @Classe_ID : correspond à l’identifiant de la classe pour laquelle nous souhaitons la liste des examens
• @Matière_ID : correspond à l’identifiant de la matière pour laquelle nous souhaitons la liste des examens
• @Trimestre : correspond au numéro de trimestre souhaité pour les examens

Figure 26

Concernant le contenu de la matrice, son contenu est fourni par l’appel d’une requête SQL (sous forme de texte (Figure 27). Cependant, cette requête a également trois paramètres en entrée :

• @Classe_ID : correspond à l’identifiant de la classe pour laquelle nous souhaitons la liste des notes
• @Matière_ID : correspond à l’identifiant de la matière pour laquelle nous souhaitons la liste des notes
• @Trimestre : correspond au numéro de trimestre souhaité pour les notes

Figure 27

Supposons que nous souhaitions ramener les notes de tous les élèves de 6°B (promotion 2007) en mathématique sur le deuxième trimestre de l’année. Nous allons tout d’abord définir les trois variables qui vont nous permettre de stocker ces valeurs (Figure 28).

Figure 28

Nous allons ensuite définir la requête qui va être utilisée pour remplir la première colonne (Figure 29). Il faut noter l’appel à la méthode SetParameter de notre type QueryBuilder, ce qui nous permet de fournir la valeur du paramètre attendue (@Classe_ID) par notre procédure stockée.

Figure 29

Nous allons faire de même pour la requête renvoyant la liste des examens du trimestre en question (Figure 30).

Figure 30

Enfin, nous allons définir la requête qui va remplir la matrice (Figure 31).

Figure 31

A noter qu’ici, nous avons aussi la possibilité de définir nous-mêmes le type des cellules de la matrice. Si on ne précise rien, une inférence du type sera effectuée. ATTENTION, cette inférence peut ne pas utiliser le type le plus approprié à votre situation. C’est la raison pour laquelle, il peut être utile de spécifier nous-mêmes ce type (Figure 32).

Figure 32

Désormais, nous pouvons appeler la procédure stockée [TheMatrix] comme indiqué à la Figure 33.

Figure 33

L’exécution de cette procédure renvoie le résultat de la Figure 34.

Figure 34

Haut de page

Exemple d’utilisation n°3 : cas encore plus complexe

Dans cet exemple, nous allons partir des tables qui ont été utilisées dans les deux exemples précédents (Figure 35).

Figure 35

Nous voulons ici construire une matrice très riche. Celle-ci devra contenir, pour une classe donnée, la moyenne calculée de tous les examens pour chacun des élèves. Nous voulons de plus la moyenne du premier trimestre, du second trimestre, du troisième trimestre et de l’année pour chacun des élèves. Ce n’est pas tout !! Nous souhaitons également voir la moyenne du premier trimestre, du second trimestre, du troisième trimestre et de l’année pour chacune des matières, tous les élèves confondus. Bien qu’en d’apparence complexe, la réalisation d’une telle matrice va être un jeu d’enfants. La Figure 36 montre le type de sortie que nous souhaitons.

Figure 36

La complexité (relative !) de cet exemple par rapport à l’exemple n°2 est que nous allons devoir mélanger des données provenant de la base de données (comme Elève 1, Elève 2…), avec des données non présentes dans la base comme (Moyenne 1er trimestre ->, Moyenne 2nd trimestre ->, …). Il  va donc falloir « affecter » des clés primaires à ces pseudos enregistrements et les faire passer comme des élèves pour le contenu de la première colonne et comme des matières pour la liste des en-têtes de colonne de la matrice. Nous allons donc fixer des clés primaires pour ces 8 enregistrements supplémentaires (les 4 dernières lignes et les 4 derniers en-têtes de colonne).

Le type de clé primaire des élèves est [int], tandis que celui des matières est [uniqueidentifier] (Figure 37).

Figure 37

Il ne nous reste plus maintenant qu’à définir les trois procédures stockées qui vont être appelées pour construire la matrice attendue :

• [ListeDesElèvesParClasseAvecMoyenne] (Figure 38) pour la liste des élèves
• [ListeDesMatièresParClasse] (Figure 39) pour la liste des matières
• [CalculDesMoyennesParClasse] (de Figure 40 à Figure 46) pour le calcul des moyennes

Le contenu même de ces trois procédures n’est pas vraiment intéressant ici. Il faut juste remarquer l’utilisation de table temporaire pour pouvoir stocker à la fois les valeurs en provenance de la base de données mais également les valeurs fictives décrites précédemment.

Figure 38

Figure 39

Figure 40

Figure 41

Figure 42

Figure 43

Figure 44

Figure 45

Figure 46

Il ne reste plus qu’à écrire le code d’appel à la procédure [TheMatrix]. Tout d’abord, nous devons déclarer toutes les variables nécessaires à l’appel (Figure 47).

Figure 47

Enfin, il suffit de réaliser l’appel (Figure 48).

Figure 48

La Figure 49 montre le résultat de l’exécution finale de cette procédure.

Figure 49

Ce qui, dans Microsoft Excel et après un petit formatage cosmétique, donne la Figure 50.

Figure 50

Haut de page

Ensuite ?

Cet article est seulement la première étape de ce projet. Ce projet vient d’être publié (binaire + code source Visual Basic .NET [VB]) sur le site de partage des projets Open Source baptisé CodePlex (http://www.codeplex.com)

http://www.codeplex.com/SQL2DMatrixBuilder

Il y’aura également très prochainement un article technique décrivant cette fois-ci l’envers du décor, à savoir comment j’ai développé ce projet.

Enfin, pour suivre les nouveautés sur ce projet, vous pouvez également vous rendre sur mon blog à l’adresse suivante :

http://blogs.msdn.com/pascal/archive/tags/The+SQL+Server+2-D+Matrix+Builder/default.aspx

Haut de page