APSQL

Construire un jeu d’essai pour une base de test

La mise au point d'un jeu d'essai, n'est pas une opération simple et pour qu'il soit réaliste, la base doit présenter une variété de données réaliste. Je vous propose donc de monter un jeux d'essai à partir d'un fichier de 40 000 clients et d'un catalogue de près de 4 000 articles.

Localiser une base de noms cohérents

Le site fakenamegenerator, permet de produire au format csv une base de 40000 noms à consonance française. Même si l’on se rend compte facilement que les informations ont était générées cela donne une bonne base de travail et permet de travailler avec une base de test qui ne contient pas juste quelques lignes.

Après inscription sur le site fakenamegenrator, vous recevrez par mail le lien vers votre fichier. Privilégier le format csv qui permet une importation facile dans SQL Server

Localiser une base d’articles cohérents

Pour les articles, je vous propose d’utiliser le catalogue de produits du site materiel.net. En effet ce site permet de télécharger le catalogue au format csv ou bien xml (http://www.materiel.net/ctn/faq.html) . Dans le cas qui nous intéresse ici, le format csv sera retenu.

Valoriser les tables

Pour travailler avec les données au format csv et les importer facilement dans la base, il est possible d'utiliser la méthode OPENROWSET. Cette méthode va prendre en  paramètre le nom du fichier de données ainsi que la référence à un fichier de format afin d'indiquer à OPENROWSET comment lire et traiter les données.

Afin de faciliter les traitements des données, toutes les fichiers sont enregistrés dans le dossier c:\exemple.

Valoriser la table des clients

Le fichier de format (fakename.fmt) va indiquer à OPENROWSET comment lire et traiter le fichier CSV. Ce fihcier de format va contenir les informations suivante:

  • la version de SQL Server
  • le nombre de colonnes

Puis chaque colonne va être détaillée sur une ligne de la façon suivante:

  • le numéro de la colonne
  • le type de donnée à utiliser pour interpréter les données dans SQL Server
  • la longueur du préfixe
  • le nombre de caractères pour cette colonne
  • le caractère de séparation
  • le numero d'ordre de la colonne dans la pseudo table en sortie d'OPENROWSET
  • le nom de cette colonne dans la table de sortie
  • le classement à utiliser pour interpréter les données.

Le fichier de format correspondant à notre fichier fakename.csv est présenté ci-dessous:

9.0
13
1       SQLCHAR       0       100     ","     1     Gender            ""
2       SQLCHAR       0       100     ","     2     Prenom            SQL_Latin1_General_CP1_CI_AI
3       SQLCHAR       0       100     ","     3     Nom               SQL_Latin1_General_CP1_CI_AI
4       SQLCHAR       0       100     ","     4     Adresse           SQL_Latin1_General_CP1_CI_AI
5       SQLCHAR       0       100     ","     5     Ville             SQL_Latin1_General_CP1_CI_AI
6       SQLCHAR       0       100     ","     6     CodePostal        ""
7       SQLCHAR       0       100     ","     7     EMail             ""
8       SQLCHAR       0       100     ","     8     Telephone         ""
9       SQLCHAR       0       100     ","     9     NeLe              ""
10      SQLCHAR       0       100     ","     10    CCType            ""
11      SQLCHAR       0       100     ","     11    CCNumber          ""
12      SQLCHAR       0       100     ","     12    CCexpires         ""
13      SQLCHAR       0       100     "\r\n"  13    NationalID        ""
 

Il est alors possible d'exécuter l'instruction OPENROWSET afin d'extraire les données depuis le fichier et d'afficher le resultat depuis SQL Server Management Studio.

select *
from openrowset(bulk N'c:\exemple\fakename.csv',
 formatfile='c:\exemple\fakename.fmt') as fakename;
			

A partir de cette étape tout deviens plus facile, car il est possible d'utiliser les fonctions de SQL Server pour transformer/adapter les données à la base.On se rend compte que la première ligne contient le nom des colonnes, pour éviter l'importation de ces données dans la base le plus simple est de supprimer cette ligne depuis le fichier csv.Lors de la mise au point de la requête d'extraction de données, il est plus rapide de travailler sur un jeu restreint de données et c'est pourquoi, il est judicieux d'utiliser la clause TOP de l'instruction select, comme illustré ci dessous:

select top 100 *
from openrowset(bulk N'c:\exemple\fakename.csv',
 formatfile='c:\exemple\fakename.fmt') as fakename;
			

Si par exemple, la table des clients possède la structure suivante:

Schéma de la table des clients

Il est possible de mettre au point la requête d'extraction suivante:

select top 100 nom, prenom,adresse, codepostal,ville,telephone
from openrowset(bulk N'c:\exemple\fakename.csv',
 formatfile='c:\exemple\fakename.fmt') as fakename;
 			

Si la table client ne possède pas de type identity sur la colonne numéro, il est préférable de doter cette colonne de la propriété. Toutefois, comme il n'est pas possible de modifier une colonne pour ajouter simplement cette propriété, il est préférable de passer par SSMS qui va se charger de réaliser les opérations nécessaire afin de se retrouver au final avec une colonne de type identity pour le numéro.

Positionner le type identity sur la colonne qui sert d'identifiant à la table clients

Les données peuvent alors être insérer dans la table client à l'aide du script suivant:

insert into clients (nom, prenom, adresse, codepostal, ville, telephone)
select nom, prenom,adresse, convert(int,codepostal),ville,telephone
from openrowset(bulk N'c:\exemple\fakename.csv',
 formatfile='c:\exemple\fakename.fmt') as fakename;
			

Valoriser la table des articles

Comme pour l'opération précédente, le fichier de données (materiel.csv) et le fichier de format (materiel.fmt) serons localisés dans le dossier c:\exemple.Ici également la première ligne du fichier CSV contient nom des colonnes et sera donc supprimé du fichier afin d'éviter une ligne d'information dénuée de sens dans la table des articles.Le fichier de format pourra être celui-ci:

9.0
12
1       SQLCHAR       0       100     "\t"    1     Name              ""
2       SQLCHAR       0       100     "\t"    2     brand             SQL_Latin1_General_CP1_CI_AI
3       SQLCHAR       0       100     "\t"    3     Price             SQL_Latin1_General_CP1_CI_AI
4       SQLCHAR       0       100     "\t"    4     Abstract          SQL_Latin1_General_CP1_CI_AI
5       SQLCHAR       0       400     "\t"    5     URL               SQL_Latin1_General_CP1_CI_AI
6       SQLCHAR       0       100     "\t"    6     Shipping          ""
7       SQLCHAR       0       100     "\t"    7     availability      ""
8       SQLCHAR       0       100     "\t"    8     code              ""
9       SQLCHAR       0       100     "\t"    9     genre             ""
10      SQLCHAR       0       100     "\t"    10    ecotaxe            ""
11      SQLCHAR       0       100     "\t"    11    EAN                ""
12      SQLCHAR       0       100     "\r\n"  12    CatName            ""			
			

Cette fois ci le caractère utilisé pour passer d'un champ à un autre est la tabulation représentée par le caractère \t dans le fichier de format.

La validité du fichier de format pourra être évaluée à l'aide de la requête suivante:

select top 100 *
from openrowset(bulk N'c:\exemple\materiel.csv',
 formatfile='c:\exemple\materiel.fmt') as materiel;
			

Pour une table des articles qui possède la structure suivante:

Structure de la table des articles

Il est possible de valoriser les colonnes reference_art, designation_art et prixht_art de façon simple à l'aide de la requête suivante:

insert into articles (reference_art, designation_art, prixht_art)
select code, name, price
from openrowset(bulk N'c:\exemple\materiel.csv',
 formatfile='c:\exemple\materiel.fmt') as materiel;
			

Valoriser des commandes

Si les tables des commandes et des lignes de commandes possèdent la structure suivante:

Structure de la table des commandes et celle des lignes de commandes

Alors il est possible d'exécuter un script Transact SQL afin de gérer le nombre de commandes enregistrées dans le paramètre @nbreCommandes (500 dans l'exemple suivant). Pour chaque commande le numero du client va être tiré au sort de même que le nombre de jours qui sépare la date actuelle de la date de création de la commande.

declare @nocli int;
declare @nbJours int;
declare @indice int;
declare @nbreCommandes int;
begin
	set @nbreCommandes=500;
	set @indice=0;
	while (@indice <@nbreCommandes) begin
		-- Trouver un n° de client
		set @nocli=0;	
		while (not(@nocli between 1 and 40000)) begin
			select @nocli=round(rand()*100000,0)
		end;
		-- Trouver une date
		set @nbJours=0;
		select @nbJours=round(rand()*100,0)
		-- Ajouter une commande
		insert into commandes(client, date_cde) values (@nocli,getdate()-@nbJours )
		-- passer à la commande suivante
		set @indice=@indice+1;
	end;
end;
			

Valoriser les lignes de commandes

Dès lors il est possible de générer le détail de chaque commande. Le script va utiliser un curseur afin de parcourir toutes les commandes enregistrées. Pour chaque commande, le script va appeler la procédure genererNombre afin de connaitre le nombre de lignes pour cette commande.

Puis pour chaque ligne de commande la procédure trouverArticle va permettre de tirer au sort une référence d'articles et un nouvel appel à genererNombre va permettre de fixer une quantité.

create procedure genererNombre(@valeurMaxi smallint, @nombre smallint out)  as
begin
	declare @n int;
	set @n=0;
	while (not (@n between 1 and @valeurMaxi))begin
		select @n=round(rand()*10000/*Il faut le mettre en correspondance avec valeurMaxi*/,0)
	end;
	set @nombre= convert(smallint,@n);
end;
go
create procedure trouverArticle (@code nvarchar(50) out)as
begin
	declare @numero smallint;
	declare lesArticles cursor for select reference_art from articles;
	declare @i smallint;

	exec dbo.genererNombre @valeurMaxi=9455, @nombre=@numero output
	set @i=1;
	open lesArticles;
	fetch lesArticles into @code;
	while (@@fetch_status=0 AND @i<@numero) begin
		set @i=@i+1;
		fetch lesArticles into @code;
	end;
	close lesArticles;
	deallocate lesArticles;
end;

declare lesCommandes cursor for select numero from commandes;
declare @nocde int;
declare @nbreLigne smallint;
declare @nolig smallint;
declare @article nvarchar(50);
declare @qte smallint;
begin
	open lesCommandes;
	fetch lesCommandes into @nocde;
	while (@@fetch_status=0) begin
		-- combien de lignes pour cette commande?
		exec dbo.genererNombre @valeurMaxi=10, @nombre=@nbreLigne output
		set @nolig=1;
		while (@nolig<=@nbreLigne)begin
			exec dbo.trouverarticle @code=@article output
			exec dbo.genererNombre @valeurMaxi=10, @nombre=@qte output
			-- Enregistrer la ligne de commande
			insert into lignes_cde(commande, ligne, article, quantite) values(@nocde, @nolig, @article, @qte);
			-- Passer à la ligne de commande suivante
			set @nolig=@nolig+1;
		end;
		-- Traiter la commande suivante
		fetch lesCommandes into @nocde;
	end;
	close lesCommandes;
	deallocate lesCommandes;
end;

			

A la fin de l'exécution du script, votre base est valorisée avec un nombre conséquent de données et permet de faire des manipulations qui ont du sens que ce soit au niveau de l'écriture des requêtes qu'au niveau des tâches administratives.