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.
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
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.
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.
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:
Puis chaque colonne va être détaillée sur une ligne de la façon suivante:
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:
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.
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;
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:
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;
Si les tables des commandes et des lignes de commandes possèdent la structure suivante:
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;
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.
