SQL - Niveau de compatibilité

 Ajuster le niveau de compatibilité d'une base peut être utile pour disposer :

• de stabilité (quand on baisse le niveau) 
• ou des dernières nouveautés (quand on monte le niveau).

Dernièrement, j'ai restauré une base sur un serveur SQL dernière génération et je me suis aperçu que la compatibilité de la base restaurée ne me permettait pas d'utiliser certaines "nouvelles fonctions" (Comme STRING_SPLIT par exemple) !

Ajuster la compatibilité, n'est pas trop sorcier, le plus complexe est de s'assurer que tout fonctionne dans l'applicatif associé (La je peux rien pour vous aider), mais aussi trouver la bonne valeur du niveau ajustable en fonction de la version de son serveur SQL.

Ok SQL Server est super bien documenté, en cherchant un peu on arrive : 

1. A savoir sur quelle version du serveur SQL on est
2. A trouver le niveau de compatibilité associé
3. A exécuter la bonne requête.

Oui, oui on y arrive aisément. Ou alors…

Mais moi j'ai écris cette requête que je peux utiliser les yeux fermés et toujours de la même manière après chaque restauration de base :

DECLARE @ModelLevel INT, @CurrentLevel INT;

SELECT @ModelLevel   = [compatibility_level] FROM sys.databases WHERE name = 'model';

SELECT @CurrentLevel = [compatibility_level] FROM sys.databases WHERE name = DB_NAME();

IF (@CurrentLevel < @ModelLevel)

BEGIN

    DECLARE @sql VARCHAR(MAX);

    SET @sql  = 'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = ' + FORMAT(@ModelLevel, '0') + ';';

    EXEC (@sql);

END 

En gros,  on utilise le niveau de compatibilité de la base "Model" pour ajuster le niveau de la base en cours. La base "Model" étant toujours au meilleur niveau associé à notre serveur SQL (si on y a pas déjà touché !!), en faisant cela on est sûr de prendre le bon niveau.

Bon c'est peut être pas super utile, mais à moi cela me sert assez souvent !

Enjoy ?

SQL Server : Recupérer l'auto incrémenté (1 de plus !)

 Pour récupérer la valeur d'une colonne auto incrémenté dans SQL Server on dispose de 3 méthodes : 

• SCOPE_IDENTITY()
• @@IDENTITY
• IDENT_CURRENT('Table')

Pour trouver quoi utiliser et quand, je vous laisse voir les documentation associées.

Pour ma part, j'ai pris l'habitude d'utiliser SCOPE_IDENTITY(), mais récemment, je me suis retrouvé bloqué par un INSERT dans une vue avec un trigger INSTEAD OF qui fait que le contexte d'insertion est différent du context d'appel et donc qui fait que  SCOPE_IDENTITY() renvoie NULL ;-(

Du coup pour ne plus se faire avoir (nouvelle grande décision), maintenant j'utilise l'instruction ci dessous plus longue mais marche dans plus de cas :

DECLARE @id INT;
SELECT @id = COALESCE(SCOPE_IDENTITY(), @@Identity);

Bien sûr il faut remettre cette astuce dans le seul contexte ou l'on ne veut insérer qu'une seule ligne à la fois.
Il faut utiliser une clause OUTPUT si plusieurs lignes vont être insérées en une seule opération.

Enjoy !

SQL Server : Jours de la semaine

 J'y ai passé plus de 5 minutes sur ce sujet (Et j'ai pas retrouvé mon post précédent :-( sur le sujet), c'est trop long, donc encore un petit mémo pour s'en rappeler :

Dans SQL Server les jours de la semaine sont renvoyés par la fonction DATEPART(WEEKDAY, @date)

La fonction renvoie un nombre de 1 à 7 qui correspond au numéro d'ordre du jour dans la semaine.

Le premier jour étant... paramétrable !   

....La galère commence !

SELECT @@DATEFIRST est le paramètre qui renvoie 1 si le premier jour est le lundi, 2 si c'est le mardi, ... (devinez la suite)

On peut changer cela avec un SET DATEFIRST x (Ou x vaut de 1 lundi à 7 dimanche)

Donc pour connaitre toujours de la même manière le jour de la semaine maintenant je fais :

SELECT CASE ((DATEPART(WEEKDAY, @date) + @@DATEFIRST -1 ) % 7)
WHEN 0 THEN 'Dimanche' WHEN 1 THEN 'Lundi' WHEN 2 THEN 'Mardi' WHEN 3 THEN 'Mercredi' .... WHEN 6 THEN 'Samedi' END

L'idée est de toujours retomber sur ses pattes en prenant un modulo 7, mais le modulo 7 renvoie de 0 à 6 !
En revanche WEEKDAY et @@DATEFIRST renvoient des nombres entre 1 et 7.
Et j'aimerais bien en profiter pour caler le résultat sur l'énumération l'énumération DayOfWeek de C# (Pour laquelle le Dimanche est un 0, le Lundi 1, Mardi 2, ... Samedi 6) 

C'est pour cela que j'ajoute -1 à la somme de @@DATEFIRST et du jour de la semaine a pour faire en sorte qu'un dimanche renvoie toujours 0, lundi renvoie toujours 1, ...

Enjoy ! 

SQL : Compter les objets de la base

Pour découvrir une base de données il est pas mal de compter ce quelle contient. 

Voici la requête que j'utilise :

SELECT *
FROM ( SELECT
  CASE [type] WHEN 'AF' THEN 'Fonction d''agrégation (CLR)'
              WHEN 'C'  THEN 'contrainte CHECK'
      WHEN 'D'  THEN 'DEFAULT (contrainte ou autonome)'
      WHEN 'F'  THEN 'Contrainte FOREIGN KEY'
              WHEN 'FN' THEN 'Fonction scalaire SQL'
              WHEN 'FS' THEN 'Fonction scalaire d''assembly (CLR)'
              WHEN 'FT' THEN 'Fonction table d''assembly (CLR)'
              WHEN 'IF' THEN 'Fonction table en ligne SQL'
              WHEN 'IT' THEN 'Table interne'
              WHEN 'P'  THEN 'Procédure stockée SQL'
              WHEN 'PC' THEN 'Procédure stockée d’assembly (CLR)'
              WHEN 'PG' THEN 'Repère de plan'
              WHEN 'PK' THEN 'Contrainte PRIMARY KEY'
              WHEN 'R'  THEN 'Règle (ancienne, autonome)'
              WHEN 'RF' THEN 'Procédure de filtre de réplication'
              WHEN 'S'  THEN 'Table de base système'
              WHEN 'SN' THEN 'Synonyme'
              WHEN 'SO' THEN 'Objet séquence'
              WHEN 'U'  THEN 'Table (définie par l''utilisateur)'
              WHEN 'V'  THEN 'Vue'
              WHEN 'EC' THEN 'Contrainte Edge'
              WHEN 'SQ' THEN 'File d''attente du service'
              WHEN 'TA' THEN 'Déclencheur d''assembly DML (CLR)'
              WHEN 'TF' THEN 'Fonction table SQL'
              WHEN 'TR' THEN 'Déclencheur DML SQL'
              WHEN 'TT' THEN 'Type de table'
              WHEN 'UQ' THEN 'Contrainte UNIQUE'
              WHEN 'X'  THEN 'Procédure stockée étendue'
              WHEN 'ST' THEN 'Arbre de statistiques (STATS_TREE)'
              WHEN 'ET' THEN 'Table externe'
            ELSE [Type] 
            END AS [Type]
  , COUNT(*) AS Nombre
FROM sys.objects 
GROUP BY [type]
UNION (SELECT 'Colonnes de table', COUNT(*) FROM sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_id)
UNION (SELECT 'Colonnes de vues', COUNT(*)  FROM sys.views v INNER JOIN sys.columns c ON v.object_id = c.object_id)
UNION (SELECT 'Index ' + i.type_desc, COUNT(*) FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id GROUP BY i.type_desc)
UNION (SELECT 'Trigger ' + g.type_desc, COUNT(*) FROM sys.tables t INNER JOIN sys.triggers g ON t.object_id = g.object_id GROUP BY g.type_desc)
 ) T
 WHERE T.Nombre > 0

Y a pas grand chose à détailler donc juste...

Enjoy !

SQL - générer des nombres

Parfois, on a besoin en SQL de remonter une liste de nombre pour combiner avec des tables ou pour démultiplier un résultat.

J'ai trouvé ce code de @slartidan sur service Stack : https://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n BETWEEN @userinput1 AND @userinput2
ORDER BY 1

Le code est simple et permet d'avoir une table avec une colonne contenant les entiers entre @userinput1 et @userinput2.
C'est simple clair, facile a écrire facile à adapter, élégant.

Rien à dire de plus !

Arduino : Mettre à l'heure un module RTC

Premier article sur l'Arduino !

Lorsqu'on utilise un module RTC (Real Time Clock : DS1307 ou autre) connecté à un Arduino la première difficulté que j'ai rencontré (en dehors des branchements initiaux) est d'arriver à mettre à l'heure l'horloge !

Voici 2 programmes pour y arriver aisément :

• Le premier à téléverser dans l'Arduino auquel est branché l'horloge
• Le second pour le PC qui fournira l'heure 

Ça fait quoi ces programmes ?
Le programme Arduino se connecte au module RTC et transmet régulièrement via le port série, l'heure du module. il est aussi capable, via ce même port série, si on transmet une chaîne représentant une date et heure, de mettre à jour le module RTC, qui grâce à sa pile pourra ainsi la conserver.
De plus, si on appuie une fois sur le bouton, il peut demander au programme PC de lui transmettre la date et heure. Un appuie long sur le bouton arrête le programme PC qui sinon tourne en boucle indéfiniment.

Le programme PC, une application console, se connecte à l'Arduino via le port série, il affiche ce qui arrive de l'Arduino, et analyse les commandes :

• Adjust dans ce cas, il envoie la date et l'heure.
• Quit dans ce cas il se termine en fermant comme il faut le port série

Comment faut faire ?

1. Récupérer les codes source sur Git HUB
2. Brancher le module RTC et le bouton sur l'Arduino (6 fils : voir schéma ci-dessous)
3. Connecter l'Arduino au PC : (Vérifier le port série utilisé et ajuster dans le code si besoin)
4. Téléverser le programme dans l'Arduino
5. Dans Visual Studio Code charger le programme PC (Nécessite .NET CORE 3.1 SDK) et vérifier aussi que le port série est le bon
6. Exécuter le programme compilé (commande dans la console : Donet Run ou Donet Build et lancer l'exécutable généré dans le dossier Bin)
7. A ce stade les informations de l'horloge doivent défiler sur la console (1 par seconde si le module RTC est déjà initialisé, sinon message d'erreur)
8. Appuyer sur le bouton câblé sur l'Arduino ==> Le module RTC est à l'heure (du PC) !
9. Appuyer et maintenir le bouton ==> le programme PC s'arrête proprement.
10. Enjoy !!

Explications et branchements : Arduino
Un module RTC DS1307 se connecte via le bus I2C de l'Arduino, Je vous fais pas de détail y a pleins de bons tutoriels pour cela. Le module est connecté comme suit :

• SDL sur la PIN A4
• SDA sur le PIN A5 
• GND sur GND
• VCC sur 5V

L'adresse de ma carte RTC sur le bus I2C est 0x68 cette valeur est ajustable dans le code (par une constante) en fonction du module utilisé.
En plus du module RTC il faut un bouton poussoir branché sur une sortie digitale de l'Arduino (PIN 2).

Le programme à téléverser dans l'Arduino est composé de 3 fichiers :

• Horloge.h : Fichier d'entête de la librairie simplifiant les accès au module RTC DS1307
• Horloge.cpp : Le code de la librairie
• MAJ_Horloge.ino : Le programme détaillé ci-dessous.

Le programme fait aussi appel à 2 librairies de communication "classique" pour l'Arduino : SPI.h et Adafruit_SSD1306.h.

On retrouve dans les déclaration (#define) successivement :

• La déclaration de l'adresse du module RTC (ici 0x68)
• La déclaration de la pin du bouton poussoir (ici PIN 2)
• La déclaration du temps "d'appui long" sur le bouton au dela duquel on stoppe le programme PC (ici 3000 = 3 secondes)

Ensuite les variables dont myhorloge qui est une instance de la classe permettant de manipuler le module RTC (Obtenir l'heure,l'ajuster et pouvoir l'afficher simplement).
Je ne détaille pas les autres variables les commentaires s'en charge pour moi.

La suite : la fonction Setup() :

• Positionne le bouton sur la bonne pin et en mode INPUT_PULLUP, 
• Initialise le port série, 
• Initialise la communication avec le module RTC
• Donne quelques instructions sommaires à l'utilisateur sur le port série.

La fonction loop() :
Gère quand le bouton bouton est enfoncé :

En mode INPUT_PULLUP c'est quand l'état du bouton est à LOW que le bouton est enfoncé, dans ce cas on regarde si ce n'est pas la première fois que l'on passe dans la boucle et le temps depuis le premier appui sur le bouton : s'il dépasse le temps prévu (DELAY_QUIT) on envoie la commande quit sur le port série pour forcer le programme PC à s'arrêter proprement.
Si c'est le premier appui sur le bouton, on mémorise le temps pour commencer à compter.

La suite : quand le bouton n'est pas appuyé

Si le bouton est relâche, s'il a été appuyé au moins une fois on envoie la commande adjust sur le port série pour demander au programme PC de nous transmettre l'heure (le traitement de la réception des données se fait plus bas).
Le reste du loop permet de demander l'heure au module RTC et de l'afficher à intervalle régulier. (Gère aussi le cas ou le module n'est pas correctement initialisé)

Le traitement des caractère reçus par l'Arduino se fait dans le gestionnaire d’événements du port série serialEvent().

La fonction lit les caractères reçus et les concatène jusqu'à la réception d'un retour chariot (\n).
A la réception d'un retour chariot, la fonction demande à la méthode stringValide() si la chaîne reçue est une chaîne valide représentant une date et heure au format dd/MM/yyyy HH:mm:ss. Si oui, la date reçue est utilisée pour mettre à jour le module RTC via la méthode myhorloge.adjust();

Comme avec un port série il y a souvent des parasites, une boucle est faite pour traiter toutes les chaînes reçues d'au moins 19 caractères (La longueur du template dd/MM/yyyy HH:mm:ss) en supprimant au fur et a mesure le premier caractère.


Il reste deux fonctions dans le programme : stringValide()et isValidNumber().
Ces deux fonctions ont pour but l'analyse de la chaîne reçue et renvoie True si c'est OK : Tous les contrôles possibles sont fait :

• Les caractères  doivent être des chiffres aux bons emplacements 
• Les séparateurs sont aussi contrôlés
• Les valeurs des chiffres aussi (Jours de 1 à 31, mois de 1 à 12, ...)
• Les mois à 28, 29 ou 30 jours sont aussi contrôles (avec détection des années bissextiles).

Voir le détail de ces fonctions (un peu longue) dans le code source.



Explications : Programme PC
Le programme PC est écrit en C#,
Il nécessite le téléchargement du SDK .NET CORE (accessible à l'adresse suivante : https://dotnet.microsoft.com/download)
Il est compilé avec Visual Studio Code (Éditeur simple pour faire cela téléchargeable à l'adresse : https://code.visualstudio.com/download)

Le dossier de la solution contient un seul projet application console composé d'un seul fichier de code source Program.cs.

Le programme utilise un Package : System.IO.Ports téléchargé via la commande la la console de Visual Studio Code > donet add package System.IO.ports

Le programme déclare deux constantes

PORTNAME : Le nom du port série à utiliser pour parler avec l'Arduino.
BAUDRATE : la vitesse de dialogue.

Une amélioration certaine de l'application serait de pouvoir fournir cela sur la ligne de commande de l'exécutable (TODO !-).

La méthode Main() traite l'intégralité du programme qui consiste en 3 étapes :

Ouvrir le port série, lire les lignes de caractères recues et les traiter des trois manières suivantes :

• Soit c'est la commande quit dans ce cas la boucle While se termine, le programme aussi
• Soit c'est la commande adjust qui est reçue dans se cas le programme envoie la date et l'heure sur le port série.
• Pour toute autre chaîne reçue elle est affiché dans la Console.

A noter : 
le try... finally garantit que le port série sera toujours proprement fermé, même si l'application plantait.
le using(var myport = new SerialPort(...)) { ... } qui englobe le contenu garantit aussi que les ressources associées au port série seront nettoyées proprement par le système lors de l'arrêt du programme.


Voila voila !

SQL - Surveiller un compteur d'identité

Sur les tables SQL Server mettre un compteur d'identité comme clé primaire, c'est bien. (Ça résous pas mal de problématiques).
Mais quand la base a une forte (très forte) activité et que l'on utilise des colonnes de type INT pour ces compteurs, ben des fois on arrive au bout du compteur !!!
A ce moment la c'est trop tard votre application va subir des bugs à répétition jusqu’à ce que vous solutionniez ce problème. Autant dire qu'il faut réagir vite et sous la pression. C'est pas bon.

Pour éviter cela je propose de surveiller ces compteurs et de faire un reporting qui vous indique quand agir par anticipation.

Voici ci dessous une procédure qui stocke dans une table dbo.compteur_identite la valeur de l'auto-incrémenté et combien de lignes il reste à insérer sereinement.
En dessous de 1 Million vous avez un warning.

--- Vider la table si elle existe déjà
IF (EXISTS (SELECT 1 FROM sys.schemas s 
                     INNER JOIN sys.tables t ON t.schema_id = s.schema_id 
                     WHERE s.name = 'dbo' 
                       AND t.name = 'compteur_identite'))
BEGIN
 DROP TABLE dbo.compteur_identite
END 

--- recréer la table de sortie
CREATE TABLE dbo.compteur_identite ( [schema] VARCHAR(10) NOT NULL
, [table] VARCHAR(100) NOT NULL, [index_pk] VARCHAR(100) NOT NULL
, [column] VARCHAR(100) NOT NULL, [type] VARCHAR(25) NOT NULL
, [seed] BIGINT NULL, [increment] BIGINT  NULL
, [max_id] NUMERIC(38,0)  NULL, [current] NUMERIC(38,0) NULL
, [nombre de lignes restant] NUMERIC(38,0) NULL
, [critique] VARCHAR(10) NULL)


--- curseur pour parcourir toutes les tables de la base de données
DECLARE @schema VARCHAR(5);
DECLARE @table VARCHAR(100);
DECLARE curseur CURSOR FOR SELECT s.name, o.name 
                           FROM sys.schemas s 
                           INNER JOIN sys.objects o ON o.schema_id = s.schema_id 
                           WHERE s.name <> 'sys' 
                             AND o.name NOT IN ('MASTERKEY', 'spt_values')  --- ici exclure ce qui n'est pas nécessaire

OPEN curseur
FETCH NEXT FROM curseur INTO  @schema, @table
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
  PRINT @schema + '.' + @table;
  --- insertion dans la table de sortie
  INSERT INTO dbo.compteur_identite ( [schema]
    , [table], [index_pk]
    , [column], [type]
    , [seed], [increment]
    , [max_id], [current]
    , [nombre de lignes restant]
    , [critique])
  SELECT s.name AS [schema]
    , o.name AS [table], i.name AS [index_pk]
    , c.name AS [column], t.name AS [type]
    , IDENT_SEED(@schema + '.' + @table) AS [seed], IDENT_INCR(@schema + '.' + @table) AS [increment]
    , CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) AS [max_id], IDENT_CURRENT( @schema + '.' + @table) AS [current]
    , CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) AS [nombre de lignes restant]
    , CASE WHEN CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table )  < 1000000 THEN 'Warning' ELSE 'Non' END AS [critique]
  FROM sys.indexes i 
  INNER JOIN sys.objects o ON i.object_id = o.object_id 
  INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
  INNER JOIN sys.index_columns cl ON cl.object_id = o.object_id AND i.index_id = cl.index_id
  INNER JOIN sys.columns c ON c.object_id = o.object_id AND cl.column_id = c.column_id AND c.is_identity = 1
  INNER JOIN sys.systypes t ON c.user_type_id = t.xusertype AND t.collation IS NULL AND t.length > 0 AND xprec <> 0
  WHERE i.type = 1  --- pk
    AND s.name = @schema 
    AND o.name = @table
  ;
  FETCH NEXT FROM curseur INTO  @schema, @table;
END
CLOSE curseur;
DEALLOCATE curseur;

--- Affichage du resultat du jour (Tri en fonction des besoins !)
SELECT * FROM dbo.compteur_identite
ORDER BY [schema], [table], [index_pk]
 ;

Bien sût cette requête doit être adaptée a vos besoins.

Quelques infos clé :

• Le nom de la table dbo.compteur_identite est arbitraire choisissez celui qui vous fait plaisir ;-)
• Dans la déclaration du curseur sur la liste des tables a surveiller retirer les tables inutiles, moi j'ai retiré le schéma sys et les deux tables 'MASTERKEY' et 'spt_values'. 
• Calcul de la colonne "Critique", j'ai posé un seuil à 1 million de lignes restant le seuil doit être ajusté en fonction de la vitesse de remplissage et de votre capacité à agir lorsqu'il n'y a plus assez de place.
• Les filtres sur les types de colonne sont assez obscur, je les ai déterminé par recherche : Tout ce qui a une collation c'est des colonnes de type texte : pas de problème ici. Tout les type avec une longeur à 0 ou négative (il y en an si si) sont filtré pour que les calculs restent cohérents, les type avec une xprec à 0 sont aussi filtrées (Exemple les type images).
• La requête finale est a ajusté en fonction de vos besoins et peut être jouée autant de fois que nécessaire.

Enjoy !