SQL Pagination

Y-a-t-il des "Best Practices" pour paginer le résultat d'une requête ?
Si on a une requête du style :

SELECT xxx
FROM -- [... suite du FROM + WHERE très complexe ]

Et qu'on veut la paginer...
...Que faut il pour le faire correctement ?
 en entrée : la page à afficher, la taille des page et l'information de tri
 en sortie : il nous faut : le nombre total de pages, et ou le nombre total d'éléments et les X éléments seulement.
Le but aussi est de ne pas faire deux fois la requête : une pour compter et une pour renvoyer les X éléments demandés !!

Voila ce que je propose :

1) variables de pagination : @page est le numéro de la page demandée (commence à 1 !!)
@pageSize est le nombre de lignes dans la page (ici 20)

DECLARE @page INT = 1, @pageSize INT = 20

2) Entourer la requête de :

SELECT *, CEILING(x.nb_results / @pageSize) AS nb_pages
FROM
( 
   SELECT xxx
...

La colonne remontée nb_pages contiendra un entier avec le nombre total de pages possibles

3) Dans le SELECT ajouter les colonnes :
   
   , ROW_NUMBER() OVER(ORDER BY --- critère de tri ) AS rnk
  , COUNT(*) OVER() AS nb_results
   
   FROM -- [... suite du FROM + WHERE très complexe ]

La colonne rnk avec le ROW_NUMBER() permet d'attribuer un numéro à chaque ligne remontée
Pour ce faire il faut définir la ou les colonnes sur lesquelles trier
Remarque il n'est pas nécessaire de remonter cette colonne dans le résultat final.

La colonne nb_results avec le  COUNT(*) OVER() permet de calculer le nombre total de ligne, même si on filtres celle ci : Magique ! Merci SQL Server

4) à la fin de la requête on ajoute

) AS newTable
WHERE rnk BETWEEN ((@page - 1) * @pageSize + 1) AND (@page * @pageSize)

C'est ici qu'on prélève les lignes qui correspondent à la page à visualiser.


Enjoy !

SQL : Les propriétés étendues

SQL Server permet d'ajouter des "méta données" dans une base de données : Les "extended properties".
Les propriétés étendues sont des éléments "clés / valeurs" associé à un élément de la base de données.

Ca sert à quoi ?

A qualifier un élément : documentation interne de la bdd
A rendre dynamique des interfaces : on peut imaginer ajouter des données qui permettent de générer l'IHM d'une saisie de table, ou d'avoir des message d'erreurs, de validation personnalisable...


Pour gérer cela trois procédures stockées :

• sp_addextendedproperty
• sp_updateextendedproperty
• sp_dropextendedproperty

Pour dans l'ordre : Ajouter, Modifier, Supprimer des propriétés étendues

les 6 derniers paramètres de ces procédures, permettent d'indiquer où associer l'information.
Level0type = 'SCHEMA'
Level0Name = 'dbo'

Level1Type = 'TABLE'
Level1Name = 'utilisateur'

Level2Type = 'COLUMN'
Level2Name = 'util_nom'

==> Indique la colonne 'util_nom' de la table 'utilisateur' du schéma 'dbo'

Pour associer une propriété à une table : ne pas préciser le 'level2'
Pour associer une propriété à une vue, fonction, procédure ==> changer les infos dans le 'level1' en conséquence !
Pour associer une propriété à la base elle même : ne pas préciser de niveaux !

Comment on retrouve les infos ?

Avec SSMS : Dans la fenêtre propriétés de l'objet concerné il y a un onglet "propriétés étendues" qui les liste aisément.

Pour les interroger, SQL Server fournit une vue sys.fn_listextendedproperty censée remonter une ou plusieurs infos en fonction des filtre passés : j'ai pas réussi à l'utiliser dans les scénarios suivants : donne moi toutes les propriétés étendues, ou donne moi toutes les propriétés de toutes les tables...
... a creuser ?!??

Heureusement en cherchant un peu on trouve que c'est dans la table : sys.extended_properties que sont stockées les informations.
Voici donc 3 requêtes pour retrouver les informations associées à :

• La base de données :

SELECT name, value 
FROM sys.extended_properties
WHERE major_id = 0 AND minor_id = 0 AND class = 0

• Une Table :

SELECT s.name AS [schema_name], o.name AS [table_name] , p.name, p.value
FROM sys.extended_properties p
INNER JOIN sys.tables t ON p.major_id = t.object_id AND p.minor_id = 0 AND p.class = 1
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = 'utilisateur'

• Toutes les colonnes d'une table :

SELECT s.name AS [schema_name], o.name AS [table_name], c.name AS [column_name], p.name, p.value
FROM sys.extended_properties p
INNER JOIN sys.tables t ON p.major_id = t.object_id
INNER JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = p.minor_id AND p.class = 1
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = 'utilisateur' 

.. reste à adapter en fonction des besoins.

Enjoy

Add : Avec la suppression de l'objet de la base de données (Ex : suppression d'une colonne) les propriétés étendues sont elles aussi supprimées automatiquement (sans bloquer la suppression). Merci SQL Server !

SQL Server Ecrire un message pendant une procédure

Afin d'écrire un message durant l'exécution d'une requête longue on peut utiliser :

RAISERROR (@msg, 0, 1) WITH NOWAIT

Cette instruction déclenche un message de sévérité = 0 (autant dire pas grave du tout !)
et d'un statut de 1 (qui sera ignoré dans ce cas)
l'activateur NOWAIT fait le boulot attendu ==> afficher l'information immédiatement au client
ce que l'instruction PRINT permet pas !

Enjoy !

Les tests en C#

Après quelques mise en place de tests unitaires sur des solutions, voici un compte rendu de mes points de réflexions sur la mise en place de les tests unitaire en c#


J'ai utilise les outils de base de visual studio


1) Une classe de test par classe testée : le nom de la classe de tests doit être d'une forme dérivée de la classe testée. Par exemple :  Test[Nom de la classe testée]

Cela permet de s'y retrouver.

2) Une méthode de test (au moins) par méthode testée. Le nom de la méthode doit être d'une forme dérivée de la méthode testée. Par exemple : Test[Nom de la méthode testée]

Cela permet de s'y retrouver.

3) Chaque appel à Assert.xxx doit renvoyer un message personnalisé.
Le message doit être clair sur : la classe testée, la méthode testé,  le contexte du test et ce qui n'a pas marché.


   

Les Cursors SQL

Cela sert à parcourir de manière itérative des données.
Voici la séquence de bouts de code à utiliser :

Etape 1) Les variables des données à manipuler (ici un int et un Varchar(50)
DECLARE @a INT, @b VARCHAR(50);

Etape 2) déclaration du curseur
DECLARE variable_cursor CURSOR FOR 
SELECT  x, y FROM ...
;

• Adapter le SELECT en fonction des besoins, les colonnes remontées correspondent aux variables de l'étape 1)
• Le nom de la variable du curseur ne comporte pas de @ !

Etape 3) ouverture du curseur et remplissage des variables
OPEN variable_cursor;
FETCH NEXT FROM variable_cursor INTO @a, @b;

On voit bien ici qu'il faut une adéquation des variables aux colonnes du SELECT !

Etape 4) Boucle tant que c'est pas fini
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  --- Faire ici le traitement utilisant nos variables remplies @a et @b

Etape 5) Fin de boucle
  FETCH NEXT FROM variable_cursor INTO @a, @b;
END

Etape 6) Nettoyage : Très très important : ne pas oublier

CLOSE variable_cursor;
DEALLOCATE variable_cursor;


Enjoy

Shrink des logs d'un BDD SQL Server

Voici un script pour réduite la taille du journal des transactions

DECLARE @size INT;
SET @size = 10;   --- Taille du log à la fin en MegaBytes

DECLARE @database VARCHAR(200);
SET @database = DB_NAME();  --- Obtient le nom de la base en cours

--- le shrink des logs
DECLARE @sqlShrink VARCHAR(MAX);
SET @sqlShrink = '
DECLARE @logfilename NVARCHAR(200);

USE [' + @database + '];
ALTER DATABASE ['+ @database +'] SET RECOVERY SIMPLE;

DECLARE crsor CURSOR FOR
SELECT [name] FROM [sys].[database_files] 
WHERE [type] = 1;
OPEN crsor 
  FETCH crsor  INTO @logfilename
  WHILE @@FETCH_STATUS = 0
  BEGIN
    DBCC SHRINKFILE (@logfilename, '+CONVERT(VARCHAR(20), @size)+');
    FETCH crsor INTO @logfilename
  END
CLOSE crsor
DEALLOCATE crsor

ALTER DATABASE [' + @database + '] SET RECOVERY FULL;


USE master;
';
EXEC (@sqlShrink);


Dans le principe :
1) On passe la base en cours dans le mode Recovery Simple
2) Pour chaque fichier de log (LDF) on fait un DBCC SHRINKFILE
3) On remet la base dans le mode Recovery Full

Il doit surement pouvoir être amélioré mais bon ça fait le job !

Merci aux auteurs initaux du script ;-)

Web services avec ServiceStack

Ci dessous la procédure de mise en oeuvre (minimale) de web services avec la librairie ServiceStack.

1) Télécharger les librairies qui vont bien : un NuGet en cherchant : "ServiceStack.ServiceInterface".

2) Dans un namespace de votre lib ou du site Web : Fichier AppHost.cs
  Classe qui dérive de AppHostBase et qui permet l'initialisation des web Services
  Le constructeur à surcharger fait tout le boulot en une ligne !
      public AppHost()
      : base("Ma librairie HttpListener", typeof(AppHost).Assembly)
    {
    }

3) Dans AppHost Toujours : override de la méthode Configure pour définir le comportement des Web services : Ajout de chaînes de connexions aux bases, entête http, compression, ...

4) Les web services : Je fais toujours un dossier par Web Service avec 3 classes : La classe Request, la classe Response, la classe web service

 5) La request : Classe qui gère les paramètres d'appel à un ou plusieurs Web services
  doit contenir les attributs suivants :

•    [Api("Nom de l'api")]  pour indiquer dans quel groupe d'api se trouve le web service
•    [Route("Url du Web service avec paramètres obligatoires", vers = "GET, POST, PUT, ...")] pour indiquer l'url du web service et les verbes auquel il doivent répondre :

Les 2 attributs sont dans le name space : ServiceStack.ServiceHost
L'attribut Route peut être mis autant de fois que nécessaire (Permet d'avoir différentes URL en fonction des besoins, ou rendre des paramètres obligatoires en fonction du verbe utilisé)
Les paramètres obligatoires de l'URL doivent être entre accolades exemple :
[Route("/commandeGet/{ApiKey}/", Verbs = "GET")] ici ApiKey doit correspondre à une propriété de la classe request.
Il faudra programmer une méthode pour chaque verbe Http déclaré ici dans la classe service.

De plus il est pas mal (mais optionnel) d'indiquer ce que retourne le Web Service en ajoutant l'interface IReturn<>
  public class MaClasseRequest : IReturn<MaClasseResponse>
 
6) La response : Classe qui contient les données de la réponse. Rien à signaler de plus sur cette classe !

7) Le service : Classe qui contient les méthodes de traitement des Web Services :
Exemple : public object Get(MaClasseRequest request)
Le nom de la méthode est le nom du verbe HTTP utilisé (Attention à la casse : dans le Request c'est en majuscule, ici c'est en Camel Upper Name)
Le paramètre de la méthode est le nom de la classe Request définie
Le type de retour est object car on retourne soit une Response soit une HttpErrror !

La classe service doit dériver de la classe Service  de ServiceStack.ServiceInterface