Vers le "Nested Algebra" dans SQL SERVER 2005-->2008 R2

Connaissances techniques requises : UDT, UDF, UDA (CLR SQL SERVER), C#

Connaissances théoriques requises : Le modèle relationnel et le modèle des relations emboitées (Nested Algebra)

Le modèle relationnel introduit par Codd prend en charge des types atomiques (chaîne de caractères, entiers, reéels, etc.) pour définir des schémas relationnels et interdit l'utilisation des types structurés (la première forme normale). Depuis l'intégration de la CLR dans la version 2005 (et ensuite 2008, 2008 R2) de SQL SERVER, nous pouvons détourner cette contrainte et définir des types structurés grâce aux spécifications des User-Defined Type (UDT). Ainsi, nous pouvons utiliser ces types pour encoder/décoder des vecteurs, ensembles, etc. L'intégration de ces nouveaux types offrent de nouvelles possibilités de représentation de données et permettent éventuellement de définir de nouveaux opérateurs de manipulation de données, notamment de types structurées.

Prenons, par exemple, le schéma relationnel

Salarié1(Entreprise:String, prénomSalarié String)

dont l'instance est la relation "Salarié1" contenant les tuples

("Renault","Thomas")

("Renault","Celine")

("Renault","Jean")

("Renault","Lea")

("PSA","Françoise")

("PSA","Lucas")

("PSA","Mathieu")

et le schéma relationnel

Salarié2(Entreprise:String, prénomSalariés String)

dont l'instance est la relation "Salarié2" contenant les tuples

("Renault","Thomas;Celine;Jean;Lea")

("PSA","Françoise;Lucas;Mathieu")

Nous voyons bien que "Salarié1" et "Salarié2" contiennent les mêmes informations, mais "Salarié2" ne contient pas de doublons. Cela peut être très utile quand il s'agit de relations volumineuses contrairement à la relation "Salarié1".

Par ailleurs, l'attribut "prénomSalariés" de la relation "Salarié2" représente un ensemble de prénoms concaténés et stockés dans une seule valeur de type chaine de caractères. Cette structure ne correspond pas à la structure naturel d'ensemble .

Supposons que nous disposons du type "SetOfString" qui représente un ensemble de valeurs de type chaine de caractère, et considérons le schéma relationnel

Salarié3(Entreprise:String, ensPrénomSalariés SetOfString)

dont l'instance est la relation "Salarié3" contenant les tuples

("Renault",{"Thomas"; "Celine";"Jean";"Lea"})

("PSA",{"Françoise";"Lucas";"Mathieu"})

Nous voyons bien que "Salarié2" et "Salarié3" contiennent les mêmes informations, mais l'attribut "ensPrénomSalariés" de la relation "Salarié3" est un ensemble de valeurs de type de chaine de caractères contrairement à l'attribut "prénomSalariés" de la relation "Salarié2" qui est de type chaîne de caractères.

La structure (ensemble) de Salarié3.ensPrénomSalariés convient mieux que celle (chaine de caractères) de Salarié2.prénomSalariés, dans le sens où nous pouvons définir plusieurs opérateurs (intersection, union, différence, etc.) ensemblistes, de façon simple, sur Salarié3.ensPrénomSalariés qui ne conviennent pas au type chaine de caractères (intersection, différence, ... de deux chaines de caractères ?!).

Après avoir définit le type "SetOfString", nous avons besoin de définir des fonctions d'agrégation pour transformer un ensemble de valeurs de type chaine de caractères en une valeur de type "SetOfString". Théoriquement cette opération n'est pas possible dans l'algèbre relationnelle (sinon la première forme normale n'est pas vérifiée) par contre elle est bien définie dans le modèle des relations emboîtéeset correspond à l'opérateur Nest.

En plus des UDT, la CLR de SQL SERVER support la définition de fonctions d'agrégation définies par utilisateur (UDA) accessible via SQL en utilisant la clause GROUP BY.

Un deuxième opérateur ("Unnest"), défini dans le modèle des relations emboîtées, permet de dégrouper une valeur de type ensemble ("SetOfString") pour récupérer un ensemble de valeurs de types atomiques. Je vais proposer une requête (avec des fonctions spécifiques) sql qui permet de reproduire cette opération.

Le type "SetOfString"

Je commence par définir un UDT qui implante le type ensemble de chaîne de caractères. Ce UDT correspondra à une classe de nom "SetOfString".

Dans Visual Studio 2010 :

• Nouveau projet --> Base de données --> SQL Server --> créer un "Projet de base de données CLR SQL Visual C#" portant le nom "SqlServerProject1".
• Spécifier la base de données sur laquelle le projet sera déployé (supposons qu'elle se nom "base1").
• Ajouter dans le projet "SqlServerProject1" le nouvel élément UDT (Type défini par l'utilisateur) portant le nom "SetOfString.cs".
• Mettre le code suivant dans "SetOfString.cs" :

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.IO;

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = -1)]

public class SetOfString : ArrayList, INullable, IBinarySerialize

{

public override string ToString()

{

if (this.Count == 0)

return BegSet + "" + EndPar;

else if (this.Count == 1)

return BegSet + this[0].ToString() + EndPar;

else

{

string res = BegSet + this[0].ToString();

for (int i = 1; i < this.Count; i++)

{

res += SEP + this[i].ToString();

}

res += EndPar;

return res;

}

}

public bool IsNull

{

get

{

// Placez votre code ici

return m_Null;

}

}

public static SetOfString Null

{

get

{

SetOfString h = new SetOfString();

h.m_Null = true;

return h;

}

}

public static SetOfString Parse(SqlString s)

{

if (s.IsNull)

return Null;

SetOfString u = new SetOfString();

string ss = s.Value.Substring(1, s.Value.Length - 2);

string[] eleSet = ss.Split(SEP);

for (int i = 0; i <>

{
u.Add(eleSet[i]);
}
return u;
}
#region SQl Functions
[Microsoft.SqlServer.Server.SqlFunction()]
public String GetElementAt(Int32 pos)
{
return (String)this[pos];
}
[Microsoft.SqlServer.Server.SqlFunction()]
public Int32 Cardinale()
{
return this.Count;
}
#endregion
#region Reader and Writer
public void Write(BinaryWriter w)
{
w.Write(this.Count);
String c;
for (int i = 0; i < this.Count; i++)
{
c = (String)this[i];
w.Write(c);
}
}
public void Read(BinaryReader r)
{
Int32 count = r.ReadInt32();
String e;
for (int i = 0; i <>

{
e = r.ReadString();
this.Add(e);
}
}
#endregion
#region constants
public const char SEP = ';';
public const char BegSet = '{';
public const char EndPar = '}';
#endregion
private bool m_Null;
}

• Déployer le projet sur "base1".

• Créer la relation "Salarié3" avec la commande SQL suivante :
  

CREATE TABLE [dbo].[Salarié3](
[Entreprise] [nvarchar](50) NULL,
[EnsPrenomSalaries] [dbo].[SetOfString] NULL
) ON [PRIMARY]

et les commandes SQL suivantes pour alimenter cette relation

INSERT INTO [base1].[dbo].[Salarié3]
([Entreprise],[EnsPrenomSalaries])
VALUES('Renault','{Thomas;Celine;Jean;Lea}')
GO
INSERT INTO [base1].[dbo].[Salarié3]
([Entreprise],[EnsPrenomSalaries])
VALUES('PSA','{Françoise;Lucas;Mathieu}')
GO

Notez bien que j'ai défini deux SQL FUNCTION dans "SetOfString" : GetElementAt et Cardinale.

La commande SQL

SELECT [Entreprise]  
,[EnsPrenomSalaries].GetElementAt(0)
FROM [base1].[dbo].[Salarié3]

retourne l'ensemble des tuples de la relation "Salarié3" en séléctionnant l'attribut "Entreprise" et le premier prénom contenu dans l'attribut "EnsPrenomSalaries" ; cette opération est effectuée grace au UDF "GetElementAt" défini dans le UDT "SetOfString".

La fonction Cardinale() retourne le nombre d'éléments contenus dans une valeur de type "SetOfString".

Nest vs GROUP BY (avec les UDAs)

L'opération Nest consiste à grouper des tuples selon un critère et calculer la fonction d'agrégation pour chaque groupe. Je vais me servir de la clause GROUP BY pour reproduire l'opérateur Nest. A présent je vais définir un UDA pour pouvoir construire une valeur de type "SetOfString" à partir d'un ensemble de valeur de type "nvarchar".

• Ajouter dans le projet "SqlServerProject1" le nouvel élément Agrégat portant le nom "StringToSetOfString.cs"
• Mettre le code suivant dans "StringToSetOfString.cs"

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(

Format.UserDefined, 

MaxByteSize = 8000, 

IsInvariantToNulls = true,

IsInvariantToDuplicates = false, 

IsInvariantToOrder = true, 

IsNullIfEmpty = true)]

public struct StringToSetOfString : IBinarySerialize

{

SetOfString list;

public void Init()

{

this.list = new SetOfString();

}

public void Accumulate(String Value)

{

this.list.Add(Value);

}

public void Merge(StringToSetOfString Group)

{

this.list.AddRange(Group.list);

}

public SetOfString Terminate()

{

return list;

}

#region IBinarySerialize Membres

public void Read(System.IO.BinaryReader r)

{

SetOfString val = new SetOfString();

val.Read(r);

this.list = val;

}

public void Write(System.IO.BinaryWriter w)

{

this.list.Write(w);

}

#endregion

}

• Créer la relation "Salarié1" et alimenter cette relation avec les commandes SQL suivantes

CREATE TABLE [dbo].[Salarié1](
[Entreprise] [nchar](10) NULL,    
[Prenom] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT INTO [base1].[dbo].[Salarié1]([Entreprise],[Prenom]) VALUES ('Renault','Thomas')
GO
INSERT INTO [base1].[dbo].[Salarié1]([Entreprise],[Prenom]) VALUES ('Renault','Celine')
GO
INSERT INTO [base1].[dbo].[Salarié1]([Entreprise],[Prenom]) VALUES ('Renault','Jean')
GO
INSERT INTO [base1].[dbo].[Salarié1]([Entreprise],[Prenom]) VALUES('Renault','Lea')
GO
INSERT INTO [base1].[dbo].[Salarié1]([Entreprise],[Prenom]) VALUES('PSA','Françoise')
GO
INSERT INTO [base1].[dbo].[Salarié1]([Entreprise],[Prenom]) VALUES('PSA','Lucas')
GO
INSERT INTO [base1].[dbo].[Salarié1]([Entreprise],[Prenom]) VALUES('PSA','Mathieu')
GO

La commande SQL suivante permet d'agréger les tuples de "Salarié1"

SELECT [Entreprise]
,[dbo].[StringToSetOfString]([Prenom])
FROM [base1].[dbo].[Salarié1]
GROUP BY [Entreprise]

Unnest vs requête relationnelle

Maintenant, je me propose de faire l'opération inverse de Nest. C'est à dire de dégrouper les valeurs contenus dans une valeur de type ensemble, notamment "SetOfString" et, notamment, transformer la relation Salarié3 en Salarié1.

Pour ce faire, nous allons construire une relation nommé "Count" de schéma relationnel Count(Value:int) et qui contient les tuples

(0)

(1)

(2)

(3)

(4)

(5)

(6)

La commande SQL suivante

SELECT [Entreprise]
, [EnsPrenomSalaries].GetElementAt([Value])
FROM [Salarié3], [Count]
Where [Value]<[EnsPrenomSalaries].Cardinale()

permet de dégrouper les éléments contenu dans une valeur de type ensemble de string "SetOfString". Cette requête effectue le produit cartésien entre les tuples de "Salarié3" et les tuples de "Count" et pour chaque couple de tuples la requête calcule fonction définie par l'utilisateur "GetElementAt(Value)" appliqué à l'attribut "EnsPrenomSalaries" de la relation "Salarié" qui retourne l'élément qui se trouve à la position "Value" dans la valeur de l'attribut "EnsPrenomSalaries".

Donc grace aux valeurs 0,1, 2, ... , 6 de l'attribut "Value" dans "Count" nous pouvons parcourir les éléments stockés dans une valeur de l'attribut "EnsPrenomSalaries" (de type "SetOfString") en invoquant la fonction "GetElementAt(Value)". La clause Where [Value]<[EnsPrenomSalaries].Cardinale()

évite que la fonction "GetElementAt(Value)" ne déclenche une exception, car les tailles des valeurs de l'attribut "EnsPrenomSalaries" ne sont pas égaux.

Comme vous l'auriez remarqué, cette opération n'est valable que pour les valeurs de "SetOfString" qui ne dépassent pas la taille de 6 éléments ... donc pour repousser cette limite jusqu'à une taille de 2000 ... il faut ajouter les valeurs 7, 8, ... 2000 dans Count