SQL Noir est un jeu libre (licence MIT) par Hristo « Cool as a cucumber » Bogoev, où vous incarnerez le rôle d’une personne enquêtant sur un crime, mais à grand renfort de requêtes SQL. Le SQL pour Structured Query Language ou « langage de requêtes structurées » est un langage informatique normalisé servant à exploiter des bases de données relationnelles (Wikipédia).

Bref vous avez une interface web qui vous permet de faire des requêtes dans les bases de données de témoins, suspects, enregistrements audio ou vidéo, etc., et vous devez trouver qui est la personne ayant commis le crime. Sur le principe vous allez identifier des éléments dans les données, traquer les infos correspondantes ou manquantes, faire le lien entre les éléments, repérer des liens entre personnes ou des transactions, et tout cela avec des requêtes SQL.

Il y a actuellement 4 enquêtes disponibles (et probablement plus à venir). C'est rapide, ludique, joli et ergonomique. L'outil aide en suggérant les mots clés SQL ou les noms de tables par exemple. L'outil dispose d'une zone pour prendre des notes, ce qui est à la fois pratique pour garder trace des requêtes SQL, mais surtout des résultats, et vous en aurez besoin pour les cas compliqués.

Le premier commit du projet date du début du mois, et le projet est donc assez jeune, tout en étant à la fois prometteur, et déjà très sympa.

Note: full disclosure, LinuxFr.org utilise du SQL. Cette information est-elle pertinente ici ? Absolument pas, mais des fois il y a des infos inutiles dans les enquêtes. Et merci à @siltaer d'avoir partagé ce message qui m'a fait découvrir ce jeu.

Illico Editor est un (petit) couteau suisse de la qualification de données développé à l’origine pour permettre aux experts métiers de transformer les données sans recourir à la programmation… le tout dans une simple page HTML (pas de serveur Web) donc une utilisation à travers le navigateur.

Aujourd’hui, plus de 150 transformations de données sont disponibles prêtes à l'emploi.

Particularité : chaque transformation exécutée ainsi que son résultat sont inscrits dans un journal de bord créant ainsi une sorte de procédure-type sans effort.

Publié sous licence GPL, le code d’Illico est globalement très basique : standards HTML5/CSS3/JS, et zéro dépendance, bibliothèque ou appel à un code tiers. Les données restent dans le (cache du) navigateur.
Les algorithmes sont très simples. La complexité est plutôt liée à la manière d’imaginer de nouvelles transformations de données, à la fois génériques (paramétrables) tout en restant simples pour l’utilisateur (nombre réduit de paramètres).

Sommaire

• Quelques limites à connaître
•  Objet de la dépêche
  • Avant de commencer
• Nouveaux tutoriels
  • Transposer une matrice
  • Comparer (rapidement) des groupes de colonnes
  • Comparer des lignes dans un fichier cumul
  • Créer un fichier cumul à partir de deux sources aux formats proches
  • Reconstituer des calendriers
  • Fidélisation (suivre une cohorte)
• Nouvelles fonctionnalités
  • Valeurs en liste : compacter, inverser l’ordre, filtrer
  • Valeurs en liste : lister les permutations, mélanger la liste
  • enlever les accents et les cédilles de l’en-tête
  • Permuter les colonnes
  • Numéroter chaque série
  • Obtenir les méta-données des colonnes sélectionnées
  • Décaler les dates
  • Jours de la semaine
  • Transformation des périodes « temps : intervalles »
  • Calculs
  • Convertir d’un système de numération à un autre
  • Matrice : transposée, inverser, trier

Quelques limites à connaître

Dans mon usage, des crashs du navigateur ont été constatés sur des grands jeux de données avec les fonctionnalités qui sollicitent le plus grand nombre de comparaisons (précisément le calcul de la distance d’édition / lignes).

Pour un grand volume de données, mon conseil serait d’opter pour Opera/Vivaldi qui proposent à l’utilisateur d’augmenter la mémoire allouée à la page (plutôt que de faire crasher l’onglet/navigateur) ; de réduire le jeu de données aux colonnes/lignes à traiter (ce qui réduirait la taille), avant de se lancer dans les transformations ; ou d’opter pour des outils plus adaptés à cette volumétrie.

Un test sur des données factices m’avait permis d’identifier des tailles limites de jeu de données : https://illico.ti-nuage.fr/doc/build/html/fct/principes.html#jeu-de-donnees-volumineux

 Objet de la dépêche

Cette dépêche fait écho à la précédente de janvier 2021.

Au-delà des corrections de bug et des améliorations (gestion des nombres décimaux et négatifs pour les intervalles, options supplémentaires pour décider l’interprétation de “valeurs” vides), je voulais présenter ici la trentaine de nouvelles fonctionnalités/traitements et les nouveaux tutoriels.

Avant de commencer

Dans Illico, l’expression valeurs en liste désigne

• des données présentées sous la forme a, b, c (le séparateur peut être un caractère ou une chaîne)
• des listes de couples de valeurs xxx:1 / yyy:2 / zzz:3 (un séparateur de liste / + un délimiteur {clé => valeur} ici :

Nouveaux tutoriels

La section tutoriels décrit des cas concrets pour lesquels il n’existe pas de résolution « en 1 étape ».
Dans certains cas, une fonctionnalité a été développée pour couvrir tout ou partie de la résolution.

Ces tutoriels sont détaillés pas à pas dans la section “tutoriels” afin d’être utilisés comme support de formation.

Je résume ici leur logique.

Transposer une matrice

Au sens “mathématique” du terme, bascule les lignes en colonnes et vice-versa :

nombre d’étapes/actions du tutoriel : 6

une nouvelle fonctionnalité a été développée par la suite pour transposer les données en 1 clic/étape/action

Comparer (rapidement) des groupes de colonnes

Comparer des groupes de colonnes prises deux à deux était déjà possible. Cependant, avec un grand nombre de colonne, l’opération pouvait s’avérer fastidieuse et source d’erreurs.
Le tutoriel présente une manière plus générique de comparer un grand nombre de colonne de deux fichiers sources avec le même en-tête, par exemple la description d’une même population sur deux années différentes.

nombre d’étapes/actions du tutoriel : (2 par fichier source) + 4

l’intérêt de ce tutoriel réside surtout dans le fait de rendre la complexité du traitement indépendante du nombre (de paires) de colonnes à comparer

Comparer des lignes dans un fichier cumul

On souhaite identifier des différences mais cette fois au sein d’un même fichier de données décrivant un cumul.
Il peut s’agir par exemple de deux jeux de données mis bout-à-bout décrivant une même population sur deux années différentes.

nombre d’étapes/actions du tutoriel : 3

Créer un fichier cumul à partir de deux sources aux formats proches

Le cas a été rencontré lors d’une analyse de journaux comptables où les jeux de données présentaient des rubriques/codes comptables en colonne.
D’un mois sur l’autre, le nombre et l’ordre de ces colonnes/rubriques différaient. Le tutoriel permet de s’affranchir de ces variations de la structure des données.

nombre d’étapes/actions du tutoriel : (4 par fichier source) + 3

Reconstituer des calendriers

Autre cas de figure rencontré, les données décrivent des personnes présentes sur des périodes avec en colonne la date de début, la date de fin, puis les autres données.
À partir de ces données, on recherche les dates/jours exactes qui ont rassemblé le plus de personne.

La résolution consiste à générer l’ensemble des jours (entre la date de début et la date de fin), c’est-à-dire une description des faits à une échelle unitaire/atomique (chaque ligne décrivant alors une date et non une période).

Trois approches sont proposées dans le tutoriel : entre 3 et 6 étapes/actions

Fidélisation (suivre une cohorte)

La problématique soulevée était de comprendre les parcours, trajectoires pour une population donnée.

Exemple simplifié : 4 lignes de données décrivent (dans l’ordre chronologique) les états/statuts successifs d’un individu, à raison d’un par ligne : a -> b -> c -> d.

dans la pratique, le jeu de données décrivait une population d’individu avec des trajectoire de 4 à 50 états, parfois circulaires a -> b -> a -> d -> c

On souhaite identifier :

1. le parcours par rapport à l’état initial pour l’individu pris en exemple, le résultat sera la relation suivante : a => {b -> c -> d}
2. les changements d’état (de proche en proche) pour le même exemple, le résultat sera une liste de couple de valeurs : (a => b), (b => c), (c => d)
3. les relations entre l’état initial et n’importe quel autre état du parcours même exemple, le résultat sera trois couples de valeurs : (a => b), (a => c), (a => d)
4. les relations entre n’importe quel état du parcours et n’importe quel autre état rencontré par la suite même exemple, le résultat sera six couples :
   • (a => b), (a => c), (a => d)
   • (b => c), (b => d)
   • (c => d)

La fonctionnalité utilisée possède une option “scénario” avec les 4 choix.
Ainsi, on définit « ce que représente les données » en précisant le ou les séparateurs, et la transformation est appliquée selon la demande.

Les 4 scénarios sont proposés dans le tutoriel : 3 étapes/actions (une 4ème étape est nécessaire si on souhaite étudier à part le 1er état et l’état terminal de la trajectoire)

Nouvelles fonctionnalités

La majorité des nouvelles fonctionnalités concerne

• des traitements de dates (décalage, conversion)
• des traitements d’intervalles numériques
• des traitements de périodes (intervalles de dates)

Elles sont présentées ci-dessous dans leur rubrique respective (dans l’ordre d’apparition des rubriques dans Illico et dans la documentation).

(dans l’application, chaque écran permettant d’exécuter une transformation possède un lien vers la section/page concernée dans la documentation)

Valeurs en liste : compacter, inverser l’ordre, filtrer

compacter les listes

rubrique « valeurs en liste : agrégats"

Pour une liste qui présente des répétitions—a,a,b,c,a,d,b—les deux options de cette transformation permettent d’obtenir :

• a,b,c,a,d,b : réduire à une occurrence, pour chaque série
• a,b,c,d : conserver globalement les premières occurrences
• c,a,d,b : conserver globalement les dernières occurrences

inverser l’ordre des éléments des listes

rubrique « valeurs en liste : structure"

Pour une colonne décrivant des listes d’éléments—a:1, b:2—,

• inverse l’ordre des valeurs des listes (b:2, a:1)
• inverse l’ordre des valeurs des listes imbriquées seulement (1:a, 2:b)
• inverse l’ordre des listes imbriquées et des valeurs dans ces listes (2:b, 1:a)

filtrer ou exclure les valeurs d’une liste

rubrique « valeurs en liste : filtres"

compare les listes de valeurs d’une colonne par rapport à une autre colonne de référence

• égal
• différent de
• supérieur/inférieur ou égal à
• strictement supérieur/inférieur à

réduire la liste à certaines clés

conserver/exclure certains couples {clé:valeur} lorsque la clé existe dans une autre colonne (qui contient pour chaque ligne la liste de clés à conserver ou à exclure)

Par exemple—et sans devoir utiliser des regex/expressions rationnelles—la liste 2021=3,2022=1,2024=4 pourra être réduite à 2022=1,2024=4 si la clé 2021 existe dans la colonne de contrôle.

Valeurs en liste : lister les permutations, mélanger la liste

rubrique valeurs en liste : enrichissement

lister les permutations des valeurs d’une liste

produit la liste de toutes les permutations des valeurs des listes de la colonne sélectionnée.

mélanger les valeurs de la liste

applique le mélange de Fisher-Yates sur les valeurs de la liste

enlever les accents et les cédilles de l’en-tête

rubrique « en-tête"

surtout utile lorsque l’on part d’un tableur et que l’on cherche à injecter les données dans une base de données ne tolérant pas ces caractères dans les en-têtes

Permuter les colonnes

rubrique « colonnes : ordre"

Dans le cas d’un export de données depuis un logiciel métier, ou suite à certaines transformations, certaines colonnes peuvent être générées dans un ordre qui ne s’avère pas très intuitif.

Cette nouvelle fonctionnalité inverse en 1 clic l’ordre des colonnes sélectionnées en permutant (au choix)

• 1ʳᵉ et 2ᵉ, 3ᵉ et 4ᵉ, etc.
• 1ʳᵉ et dernière, 2ᵉ et avant-dernière, etc.

Numéroter chaque série

rubrique “lignes”

Dans Illico, le terme série désigne une suite de lignes contiguës qui possèdent la même valeur dans la colonne sélectionnée (un identifiant par exemple).
Si l’identifiant réapparaît plus loin dans les données, il s’agira d’une nouvelle série.

(une autre transformation permet déjà de numéroter chaque ligne de la série)

Obtenir les méta-données des colonnes sélectionnées

rubrique “agrégats”

Pour les colonnes sélectionnées, indique

• si la colonne ne contient que des valeurs uniques (les valeurs vides sont comptées à part)
• le nombre de lignes sans valeur (valeur vide)
• le nombre de valeurs renseignées (valeur non-vide)
• la cardinalité : nombre de valeurs différentes rencontrées dans la colonne

Décaler les dates

rubrique “temps”

décaler les dates avec 1 constante (saisie par l’utilisateur)

permet de décaler les dates d’une colonne à partir d’une constante (on précise l’unité : nombre de jours, de semaines, de mois ou d’années)

décaler des dates selon 1 autre colonne

idem précédemment mais en se basant sur les valeurs d’une autre colonne plutôt qu’une constante

Jours de la semaine

rubrique “temps”

donner le nom des jours de la semaine

la date est alors recodée : lundi, mardi…

compter chacun des jours de la semaine

nombre de lundis, de mardis, etc. dans l’intervalle décrit par des colonnes début et fin de la période

obtenir le numéro du jour dans l’année

1 pour le 1ᵉʳ janvier, 32 pour le 1ᵉʳ février…

Transformation des périodes « temps : intervalles »

compléter un intervalle de date (2 colonnes : début et fin de la période)

crée une liste de jour/date dans l’intervalle décrit

rechercher une date dans un intervalle de date

compare 1 colonne (date recherchée) par rapport à 2 autres colonnes décrivant une période (début et fin de la période)

combiner deux périodes (4 colonnes)

option (au choix) : obtenir

• une fusion : période englobant les deux [min, max]
• une union : période englobant les deux seulement si intersection
• une intersection : plus petite période commune

comparer les dates et une liste de seuils (saisie par l’utilisateur)

détecter des collisions de périodes

portée de la détection

• rechercher pour l’ensemble des données
• rechercher dans les lignes qui partagent un même identifiant (les lignes comparées ne sont pas forcément contiguës)
• rechercher dans les lignes qui décrivent une série (lignes contiguës avec un même identifiant)

Calculs

rubrique “calculs”

calculer une opération sur 1 colonne : options

options :

• opérations : minimum, maximum, moyenne, somme
• valeurs vides : ignorées ou traduites par zéro
• calcul : total ou cumulé
  • option si cumulé : en partant de la première ou dernière ligne
• résultat : global ou local
  • option si local : pour chaque série ou pour chaque identifiant

calculer une opération avec 1 constante (saisie par l’utilisateur)

calculer une somme ou une moyenne sur x colonnes

Convertir d’un système de numération à un autre

rubrique “enrichissement”

conversion depuis et vers une base binaire, octale, décimale, hexadécimale

Matrice : transposée, inverser, trier

rubrique “matrice”

calculer la transposée

Transpose le jeu de données : les lignes deviennent les colonnes et inversement ; la ligne d’en-tête devient la première colonne ; la première colonne devient la ligne d’en-tête.

inverser l’ordre des lignes

Inverse l’ordre des lignes du jeu de données : la première ligne devient la dernière, la dernière devient la première, etc.

trier par ordre alphabétique

options

• ordre des lettres : A…Z…a…z…É…é ou A…É…Z…a…é…z
• placer les valeurs vides : au début ou à la fin

trier par ordre numérique

option : les valeurs vides sont

• les plus petites (seront placées au début du tableau)
• les plus grandes (seront placées à la fin du tableau)
• égales à zéro

trier par ordre chronologique

option : les valeurs vides sont

• dans le passé lointain
• dans un futur lointain
• égales à la date du jour
• égales à une date précise (à saisir)

Cette dépêche présente Zaibu, une application web auto-hébergeable permettant de conserver un journal structuré de ses dégustations de bières et de vins. Développée avec SQLPage, elle met l’accent sur la simplicité, l’indépendance et le respect de la vie privée. Contrairement aux solutions centralisées comme Untappd ou Vivino, Zaibu ne collecte aucune donnée et reste entièrement sous le contrôle de l’utilisateur.

Note : n’ayant absolument aucune compétence ni aucun talent en graphisme, le logo a été créé avec Bing Image Creator et retravaillé et vectorisé par mes soins. Je sais, çaymal.

Sommaire

• • Pourquoi créer Zaibu ?
    • Un besoin personnel
    • Une occasion de tester SQLPage
  • Une approche libre et auto-hébergeable
  • Une interface simple et accessible
  • Et maintenant ?

L’alcool est dangereux pour la santé, même en petite quantité. Le vin et la bière, comme les autres alcools, induisent une dépendance et tuent. Il est recommandé de ne pas consommer plus de 2 verres par jour, et de ne pas boire d’alcool au moins 2 jours par semaine. Si vous avez des doutes sur votre consommation, n’hésitez pas à contacter un professionnel de santé.

Pourquoi créer Zaibu ?

Zaibu répond avant tout à un besoin très concret : garder une trace de ses dégustations de boissons (uniquement bières et vins pour l’instant) sans dépendre d’applications trop encombrées ou propriétaires qui exploitent les données de leurs utilisateurs.

Ce projet est en fait l’évolution d’un simple fichier texte mis en forme selon une structure plus ou moins régulière. Il était à l’origine partagé via Nextcloud, un service de stockage et de synchronisation de fichiers, libre et auto-hébergeable. Pour passer de ce fichier brut à une véritable application, plusieurs outils ont été utilisés:

• Makefile : un fichier de configuration pour GNU Make, permettant d’automatiser diverses tâches (ici, la conversion du fichier texte).
• Gawk : une version libre de l’outil AWK, qui lit et transforme le contenu du fichier texte pour l’adapter au format voulu.
• textql : un utilitaire en ligne de commande qui interprète des fichiers texte (CSV, TSV…) comme des tables SQL, ce qui facilite le chargement des données dans une base SQLite.

Grâce à cette chaîne d’outils, le fichier texte initial a pu être converti en une base de données exploitable, pour ensuite alimenter l’application Zaibu.

Pour ceux qui collectionnent les bouteilles comme d’autres collectionnent les timbres, c’est un outil pratique et léger, conçu pour être maîtrisé de bout en bout : le code source est distribué sous licence libre (AGPLv3), l’application est facile à héberger sur son propre serveur, et consomme très peu de ressources.

Un objectif secondaire était de tester les capacités de l’outil SQLPage pour le développement rapide d’applications de gestion de données.

Un besoin personnel

Il peut être difficile de se souvenir d’une bonne bière artisanale goûtée l’année passée ou du vin qui vous a tant plu à un mariage. Un carnet de notes ou un tableau dans un logiciel de bureautique peuvent dépanner, mais on s’y perd vite, et ce n’est pas toujours très pratique à consulter sur son téléphone quand on est en pleine dégustation.

Zaibu propose un formulaire simple où vous pouvez renseigner le nom, le producteur, le style, l’amertume, le taux d’alcool, vos impressions… Une fois la dégustation terminée, vous conservez une trace précise, consultable à tout moment. En un coup d’œil, vous pouvez comparer vos différents coups de cœur ou vous rappeler pourquoi un vin particulier ne vous avait pas convaincu.

Une occasion de tester SQLPage

Zaibu a aussi été conçu comme une démonstration technique. Il a servi de terrain d’expérimentation pour un nouvel outil, SQLPage, qui permet de créer une application web de gestion et d’affichage de données complète sans s’encombrer de milliers de lignes de code. En partant de requêtes de bases de données très simples, on obtient un site fonctionnel rapidement.

Ici il s’agit d’une application de type CRUD dans sa plus simple expression, donc parfaitement adaptée à être écrite en pur SQL. Même si certains traitements nécessitent de se creuser un peu plus la tête quand rien d’autre n’est disponible, il existe généralement une manière d’arriver à ses fins (et on découvre parfois avec bonheur des subtilités du langage qu’on ignorait !).

C’est le framework parfait pour créer rapidement ses propres outils tout en gardant la maîtrise complète de sa donnée, en utilisant une base de données que l’on peut héberger soi-même facilement.

Une approche libre et auto-hébergeable

De nombreuses applications existent déjà, mais elles imposent souvent la création d’un compte, exploitent les données des utilisateurs et monétisent leur activité via la publicité ou des abonnements. Zaibu prend le contre-pied en offrant une solution entièrement libre, légère et indépendante.

L’application repose sur SQLite, un système de gestion de base de données qui se distingue des bases de données traditionnelles comme MySQL ou PostgreSQL. Contrairement à ces dernières, qui nécessitent un serveur dédié fonctionnant en arrière-plan pour gérer les requêtes et stocker les informations, SQLite est une base de données embarquée.

Cela signifie que toutes les données sont enregistrées directement dans un fichier unique sur l’ordinateur ou le serveur où l’application est installée. Il n’y a donc pas besoin d’installer et de configurer un logiciel supplémentaire pour gérer la base de données. Cette approche simplifie considérablement l’installation et l’utilisation de l’application, surtout pour des utilisateurs qui ne sont pas familiers avec l’administration de serveurs.

Et puis bien sûr, son code est ouvert. C’est comme une bière artisanale : vous savez exactement quels ingrédients sont utilisés, comment ils interagissent, et si l’envie vous prend, vous pouvez modifier la recette pour l’adapter à vos préférences. Vous pouvez la brasser tel quel, y ajouter une touche personnelle, ou même la partager améliorée avec d’autres passionnés. Ici, tout est transparent et modifiable.

Une interface simple et accessible

Pensée pour une utilisation mobile et desktop, l’interface de Zaibu permet d’ajouter rapidement une dégustation, sans fioritures. Sur smartphone, il devient facile de consulter ses notes en magasin ou chez un caviste pour retrouver une référence appréciée ou éviter une déception.

Et maintenant ?

Zaibu est encore jeune et perfectible. L’application pourrait évoluer avec des fonctionnalités comme le partage entre utilisateurs ou l’intégration d’une base collaborative… N’hésitez pas à faire vos retours dans les commentaires !

Et si le principe vous intéresse, vous pouvez aussi découvrir Mon petit potager du même auteur et construit sur le même framework, cette fois pour suivre les récoltes de son jardin et la pluviométrie.