SQL : Changer l'ordre d'éléments trié (avec des trous)

Mon besoin :
Je pars d'une liste d'éléments triés, mais je n'en n'affiche qu'une partie (les visibles) je veux faire monter ou descendre un élément dans la liste sans perdre l'ordre avec ceux non affichés.

Un exemple complet en SQL :

CREATE TABLE dbo.trimoi (
     tri int not null,
     nom varchar(50) not null,
     visible bit not null)

La colonne tri sert a trier la liste, la colonne nom identifie un élément de la liste et la colonne visible simule mon filtre d'affichage des éléments.

Un petit jeu de tests :
INSERT INTO dbo.trimoi (tri, nom, visible) 
     VALUES (1, 'Trois', 1),
            (2, 'second', 0),
            (3, 'premier', 1),
            (4, 'quatre', 1),
            (5, 'cinq', 1)

Les données en liste filtrée donnent :
SELECT * FROM dbo.trimoi WHERE visible = 1 ORDER BY tri

donne :
tri nom visible
1 Trois 1
3 premier 1
4 quatre 1
5 cinq 1

Imaginons que je veuille "monter" l'élément cinq d'un cran. Dans ce cas, il doit passer avant quatre.

Voici la procédure que je propose pour "monter un élément"
DECLARE @nom VARCHAR(50) = 'cinq'

DECLARE @pos INT
SELECT TOP 1 @pos = tri FROM dbo.trimoi WHERE visible = 1 
                                          AND tri < (SELECT tri FROM dbo.trimoi WHERE nom = @nom) ORDER BY tri DESC

IF (@pos IS NOT NULL)
BEGIN
  UPDATE dbo.trimoi SET tri = tri + 1 
  FROM dbo.trimoi
  WHERE tri >= @pos
    AND tri < (SELECT tri FROM dbo.trimoi WHERE nom = @nom)

  UPDATE dbo.trimoi SET tri = @pos WHERE nom = @nom
END 

L'exécution donne le bon résultat.
Si l'on réitère plusieurs fois l'exécution l'élément cinq monte progressivement, en sautant bien l'élément qui est masqué : second.

Quelques commentaires :
Le premier SELECT permet de trouver et d'affecter à la variable @pos la position finale de l'élément à monter.
On applique dans la requête le filtre qui nous intéresse (ici : visible = 1) ainsi que la recherche de l'élément tri le plus grand en dessous de l'élément 'cinq' (TOP 1 + ORDER BY tri DESC)

Si on a trouvé une position (cas ou cinq est en tête de liste) alors on fait les 2 updates :
1) Ajouter 1 à la colonne tri de tous les éléments entre @pos inclus et la position de l'élément cinq
2) La colonne tri de l'élément cinq prend la position @pos.

Pour être complet voici la procédure qui permet de redescendre 'cinq'
DECLARE @nomDesc VARCHAR(50) = 'cinq'

DECLARE @posDesc INT
SELECT TOP 1 @posDesc = tri FROM dbo.trimoi WHERE visible = 1 
                                              AND tri > (SELECT tri FROM dbo.trimoi WHERE nom = @nomDesc) ORDER BY tri ASC

IF (@posDesc IS NOT NULL)
BEGIN
  UPDATE dbo.trimoi SET tri = tri - 1 
  FROM dbo.trimoi
  WHERE tri <= @posDesc
    AND tri > (SELECT tri FROM dbo.trimoi WHERE nom = @nomDesc)

  UPDATE dbo.trimoi SET tri = @posDesc WHERE nom = @nomDesc
END 

Enjoy

c# : Formater un booléen en Oui/Non

Pour formater un booléen en Oui/Non

string.Format("{0:Oui;;Non}", Convert.ToInt32(nombool));
ou
$"gna gna gna {Convert.ToInt32(nombool):Oui;;Non}";

N'oubliez pas le Convert en int sinon le résultat affiché ne sera pas le bon !!!!

On utilise ici le format personnalisé des entiers avec deux points virgules :

Premier format pour les entiers positifs : ici on force le Oui
Second format pour les entiers négatifs : ici on ne met rien pour dire de faire comme les entiers positifs
Troisième format pour la valeur zéro : ici on force à Non

Convert.ToInt32(Bool) renvoie 1 ou 0 on gère ainsi tous les cas

SQL SERVER Identity faire des trous dans les autoincrementés

Voici comment faire un trou dans un compteur auto incrémenté SQL SERVER.

Bon cela peut être utile même si s'est à utiliser avec parcimonie.

Un besoin est d'insérer tout de suite la dernière valeur possible d'une liste qui ne grossira pas au delà d'une valeur connue. Cela permet d'avoir le dernier Id supérieur à tous les autres insérés plus tard.

Ci dessous l'exemple complet :

--- création de la table

CREATE TABLE [dbo].[fiche](

       [fich_id] [int] IDENTITY(1,1) NOT NULL,

       [fich_nom] [varchar](50) NOT NULL,

       [fich_code] [varchar](50) NOT NULL,

       [fich_lang] [varchar](10) NOT NULL,

       [fich_type] [tinyint] NOT NULL,

 CONSTRAINT [PK_FICHE] PRIMARY KEY CLUSTERED ([fich_id] ASC)

)

--- remplissage

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T1', 'C1', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T2', 'C2', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T3', 'C3', 'fr-FR', 3)

--- vérification

SELECT * From fiche

fich_id fich_nom fich_code fich_lang fich_type

1 T1 C1 fr-FR 3

2 T2 C2 fr-FR 3

3 T3 C3 fr-FR 3

--- on crée le trou

IF (NOT EXISTS(SELECT 1 FROM [dbo].[fiche] WHERE fich_id = 9))

BEGIN

  DECLARE @fichId INT

  SELECT @fichId = ISNULL(MAX(fich_id), 0) FROM [dbo].[fiche] WHERE fich_id <> 9

  SET IDENTITY_INSERT [dbo].[fiche] ON;

  INSERT INTO fiche(fich_id, fich_nom, fich_code, fich_lang, fich_type) VALUES (9, 'T9', 'C9', 'fr-FR', 3)

  SET IDENTITY_INSERT [dbo].[fiche] OFF;

  DBCC CHECKIDENT ([dbo.fiche], RESEED , @fichId);

  --- on le fait une seconde fois par sécurité

  DBCC CHECKIDENT ([dbo.fiche], RESEED , @fichId);

END

--- vérification

SELECT * From fiche

fich_id fich_nom fich_code fich_lang fich_type

1 T1 C1 fr-FR 3

2 T2 C2 fr-FR 3

3 T3 C3 fr-FR 3

9 T9 C9 fr-FR 3

--- Vérification : on peut insérer jusqu'au dernier Id

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T4', 'C4', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T5', 'C5', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T6', 'C6', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T7', 'C7', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T8', 'C8', 'fr-FR', 3)

--- vérification

SELECT * From fiche

fich_id fich_nom fich_code fich_lang fich_type

1 T1 C1 fr-FR 3

2 T2 C2 fr-FR 3

3 T3 C3 fr-FR 3

4 T4 C4 fr-FR 3

5 T5 C5 fr-FR 3

6 T6 C6 fr-FR 3

7 T7 C7 fr-FR 3

8 T8 C8 fr-FR 3

9 T9 C9 fr-FR 3

--- la ça plante !!

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T10', 'C10', 'fr-FR', 3)

Msg 2627, Niveau 14, État 1, Ligne 1

Violation de la contrainte PRIMARY KEY 'PK_FICHE'. Impossible d'insérer une clé en double dans l'objet 'dbo.fiche'.

L'instruction a été arrêtée.

c# : La fonction Aggregate

La fonction aggregate peut être utile dès lors qu'on manipule des listes :

Le premier usage :
          string[] mots = { "A", "b", "C" };
          string result = mots.Aggregate("Début", (x, y) => x + "," + y, x => x.ToLower());

La variable result contiendra : "début,a,b,c"

Premier paramètre optionnel permet d'ajouter un premier élément à la liste.

Second paramètre : la méthode d'agrégation

Dernier paramètre optionnel une méthode de finalisation du résultat

la version la version plus simple : string result = mots.Aggregate((x, y) => x + "," + y);

La variable result contiendra : "A,b,C"

Redis Managment Studio

Pour interroger simplement Redis voici une application sous Windows : Redis Managment Studio

Permet de définir ses serveur Redis, de s'y connecter...

Du coup on peut visualiser ou ajuster la config...

... ou requêter les clés...

... Voir et modifier une clé...

... et plus encore :

• Lancer une trace,
• Utiliser les messages en abonnement ou publication,
• Voir les clients connectés,
• Voir les slaves d'une réplication,
• Mode sentinelle,

L'application utilise une librairie C# pour la communication avec REDIS qui implémente toutes les fonctions existantes dans la version 2.6.
Je fournirais les sources de la librairie et de l'application quand elles seront "montrables".

Ci dessous le lien vers l'exécutable du programme
Enjoy

SQL : Connaitre la liste des bases d'un serveur

Pour connaitre simplement la liste des bases disponibles sur une instance d'un serveur SQL Server :

Ma première approche fut :

SELECT name FROM sys.databases ORDER BY name

Ouai bof

Maintenant je préfère Plus simple, et semble t il plus perein :

EXEC sp_databases

A noter que ces 2 requêtes fonctionnent quelque soit la base sur laquelle on les exécutes.

sp_database nécessite l'autorisation "CREATE DB" ou équivalent et il faut s'assurer que "VIEW ANY DEFINITION" n'est pas refusé.

SQL : Savoir si une base est Publisher d'une réplication

Une première requête pour lister les tables ou vues publiées dans une publication SQL Server

... Cela reste à affiner et comprendre...

USE distribution;

SELECT DISTINCT @@SERVERNAME, P.Publication, A.Article
FROM dbo.MSdistribution_status AS s
INNER JOIN dbo.MSdistribution_agents AS Agents ON Agents.[id] = S.agent_id
INNER JOIN dbo.MSpublications AS P ON P.publication = Agents.publication
INNER JOIN dbo.MSarticles AS A ON A.article_id = S.article_id and P.publication_id = A.publication_id
WHERE Agents.subscriber_db NOT LIKE 'virtual'
 --AND s.UndelivCmdsInDistDB <> 0
--ORDER BY UndelivCmdsInDistDB DESC

A+

Mise à jour
J'ai trouvé plus simple et plus clair : Mon objectif étant de savoir si la base associée à ma chaîne de connexion est répliquée (= publisher d'un réplication) ou pas ?

Pour être une base publisher d'une réplication l'instance de SQL SERVER doit avoir une base "Distribution".
Dans cette base la table système : MSPublications liste les publications et les bases qui les concernent.
En Transac SQL cela donne un truc du style :

DECLARE @replique BIT = 0;
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'distribution')
BEGIN
IF EXISTS (SELECT * FROM distribution.dbo.MSPublications WHERE publisher_db = DB_NAME())
  BEGIN 
    SET @replique = 1;
  END
END

SELECT @replique;

Y a plus qu'a en faire une fonction pour pouvoir l'utiliser simplement...


Mise à jour N°2

Savoir si une table est publiée dans une réplication

DECLARE @table varchar(255) = 'article'  --- nom de la table a chercher

DECLARE @replique BIT = 0;

IF (EXISTS (SELECT 1 FROM sys.databases WHERE name = 'distribution'))
BEGIN
 IF (EXISTS (SELECT 1 FROM distribution.dbo.MSPublications WHERE publisher_db = DB_NAME()))
  BEGIN 
    IF (EXISTS (SELECT 1 FROM distribution.dbo.MSarticles WHERE article = @table))
    BEGIN
      SET @replique = 1;
    END
  END
END

SELECT @replique;

Enjoy