"""
Naive - 2.7048650000069756 seconds
With transaction - 2.3600640000076964 seconds
With transaction and prepared statement - 0.637083999987226 seconds
With pragmas, transaction and prepared statement - 0.6406159999896772 seconds
With multirow inserts, pragmas, transaction and prepared statement - 0.3141590000013821 seconds
"""
Ce qu'il faut surtout retenir :
* Utiliser des pragmas pour désactiver le mode synchrone et la journalisation expose au risque de corruption de données, et ne fait rien gagner en performances (ou très peu sur disque magnétique)
* Sur disque magnétique, les transactions apportent un ENORME gain de performance (99.5%) en mémoire ou SSD, c'est plus modeste (18%)
* Les requêtes préparées font encore gagner 66% de perf par-dessus
* Les insertions multi-lignes font encore gagner 50%
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.
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.
Dire que ce sujet m’aura arraché quelques cheveux est un euphémisme, et pourtant dieu sait que j’en ai pas vraiment besoin, ça tombe déjà tout seul. Certes Gitea supporte trois moteurs différents (SQLite, MySQL/MariaDB, PostgreSQL), ça ne l’empêche pas de ne pas proposer de méthode officielle ni d’outil pour migrer une installation existante d’un moteur à l’autre. J’ai donc retroussé mes manches, roté un litre de sang ou deux, mais j’ai trouvé comment faire et surtout réussi. Je vous raconte.
Ceci est principalement une concaténation de ce que j’ai commencé à faire en stream le 17 janvier dernier, et terminé le dimanche 26 suivant. J’étais parti de la base d’un message sur le forum de Gitea cherchant à faire exactement la même chose. J’en avais déduit une série d’étapes que j’ai plus ou moins respectées, assez simple:
Dumper la base actuelle via la commande intégrée (qui génère un dump sql de la base SQLite); l’instance doit être démarrée pour ça, because binaire gitea
Couper gitea pour éviter un delta
Archiver le dossier sur le NAS, pour tout restaurer à l’état initial si besoin
Récupérer le fichier sql du dump
Lancer une instance vierge de gitea sur MySQL/MariaDB directement avec docker-compose, initialiser l’installation, et dumper la base créée
Retirer les INSERT INTO du fichier de dump MySQL, et y ajouter à la fin tous les INSERT INTO du fichier de dump SQLite
Importer dans l’instance MySQL cible, Dans mon cas, MariaDB, mais c’est pareil
Reconfigurer son Gitea pour qu’il l’utilise à la place
Retirer le fichier sqlite du dossier de données (on l’a toujours dans l’archive, remember)
Redémarrer gitea
Et à la vérité, même avec les travaux supplémentaires post-stream, au niveau du fichier de dump lui-même, il s’avère qu’il n’y a pas besoin de faire plus que les trois étapes mentionnées au dessus. Comment est-ce possible !?
Le problème qu’on avait donc au moment de tenter l’import dans l’instance se produisait sur des INSERT donc certains champs contenaient des emojis. La plupart du temps, ça se produit pour des histoires d’encodage au niveau des fichiers ou de la base. C’est d’ailleurs ce qui m’a fait perdre du temps pendant le live, où on a tenté en vain de convertir les fichiers avant de tenter l’import, avec un résultat parfois très surprenant, comme ce passage par UTF-16 puis UTF-8 qui s’est soldé par un fichier aussi compréhensible que du chinois, et pour cause, il était rempli de caractères chinois !
L’image de la satisfaction sur mon visage
La joie des encodages dans les bases de données
Dans le schéma (qu’on peut consulter via MySQL ou dans le fichier lui-même), beaucoup de champs/tables sont encodées en utf8mb4, c’est à dire UTF-8 avec la possibilité de coder les caractères sur 4 octets. Avec l’embonpoint qu’a pris UTF-8 ces dernières années pour inclure toujours plus d’emojis, c’est une nécessité adoptée par énormément d’applications dont les données sont fournies par les utilisateurs, et les forges git ne sont pas épargnées (WordPress aussi par exemple est passé à l’utf8mb4 il y a un bon moment déjà).
Ce qui m’a interpellé c’est qu’effectivement, l’erreur sur laquelle je butais concernait des emojis, plus précisément ceux que j’ai foutu dans le code de mon premier pipeline Gitea Actions de livraison du LinkFree (autre sujet découvert en live, abonnez-vous/follow toussa toussa). Dans le fichier on voit bien qu’ils sont sur quatre octets. Mais alors, qu’est-ce qui coince ? En effet, en cherchant un peu, on tombe sur un thread qui dit que parfois, le serveur force un autre mode d’encodage si on lui dit pas de faire autrement, et conseille d’utiliser la commande SET NAMES utf8mb4 pour forcer les paramètres de fonctionnement. Plus étrangement, c’est pourtant ce que j’ai au début du fichier de dump, donc pourquoi ne pas en tenir compte ?
Apparemment, c’est ce que fait mon serveur au moment où le client se connecte, je me retrouve en utf8mb3. Et donc, avec la première méthode d’import, celle qu’on conseille dans 99,99% des cas, il n’en tient pas compte. Oui oui, avec mysql gitea <dump.sql, et même si les premières lignes du fichier en question disent de passer en utf8mb4, il n’en tient pas compte.
Un des soucis avec cette méthode d’ailleurs, c’est qu’il abandonne à la première erreur. Ce qui fait qu’on ne sait pas trop combien de lignes sont concernées par le problème. Je change donc mon fusil d’épaule et tente de faire autrement. En effet, le client mysql permet, après la connexion, de « sourcer » un fichier sql depuis le système de fichiers (je vous laisse la page de doc pour en connaitre les détails). En tentant basiquement le coup, j’ai eu raison de vouloir le tenter, mais ça m’a aussi joué un tour : il affiche les erreurs, toutes cette fois, mais aussi un message de retour pour chaque INSERT réussi. Pas de bol, y’en a plus de 30000, et un historique de 9000 lignes seulement dans Windows Terminal. Il va donc falloir ruser.
Je tente une chose: utiliser l’option « -e » de mysql, qui permet de lancer une ou plusieurs requêtes après la connexion, un peu à la manière d’SSH, ce qui rend la main dans le shell juste après (ça connecte, ça exécute, ça déconnecte). Pour pouvoir parcourir les résultats, je renvoie la sortie standard dans un fichier. Et là, très bonne surprise : les erreurs sont renvoyées sur l’erreur standard, mais comme je renvois la sortie standard dans un fichier, je n’ai QUE les erreurs à l’écran !
Il n’y en a pas tant que ça, mais je découvre d’autres erreurs. Je décide de les prendre dans l’ordre et de m’atteler donc au souci d’emojis. Il ne tient pas compte de l’entête du fichier ? Pas grave, on est en mode « -e », j’ajoute donc le SET NAMES en premier avant de faire mon source :
mysql -p$MARIADB_ROOT_PASSWORD -e "set NAMES utf8mb4; source /root/gitea-final.sql;"
Tada, plus d’erreurs d’emojis, mais il reste quelque chose d’encore plus cryptique:
ERROR 1292 (22007) at line 31080 in file: '/root/gitea-final.sql': Truncated incorrect DOUBLE value: 'push failed: remote: Invalid username or password.'
ERROR 1292 (22007) at line 31907 in file: '/root/gitea-final.sql': Truncated incorrect DOUBLE value: '<a href="/Seboss666/collect/commit/79b78d68891f8e7b19c6e7b6968914ea2607d2f7">change last films from 10 to 30 (see #4)'
ERROR 1292 (22007) at line 31910 in file: '/root/gitea-final.sql': Truncated incorrect DOUBLE value: 'Flask framework has an extension flask-bootstrap (as mentioned and used in the mega-tutorial), and Bootstrap was one of the c...'
ERROR 1292 (22007) at line 31911 in file: '/root/gitea-final.sql': Truncated incorrect DOUBLE value: '<a href="/Seboss666/collect/commit/5ad6c4660205e3b5c0c29f92bc9ab8adf042cfc8">change default type for new movies (#3)'
ERROR 1292 (22007) at line 31918 in file: '/root/gitea-final.sql': Truncated incorrect DOUBLE value: '<a href="/Seboss666/k3s_platform/commit/edfe7190ab523b635390f5dff4a6b08f69d59c66">consul migration (issue #7)'
Bon, il n’y en a pas beaucoup, et c’est vite trouvé : y’a un truc avec une syntaxe pourtant correcte de concaténation de caractères à base de « double pipes », avec entre deux, un caractère encodé. En tout cas sur l’instant et avec une recherche rapide sur DDG ça a l’air correct et supporté. Cette fois, le caractère en question est censé vouloir dire « retour à la ligne », l’équivalent du « \n » qu’on rencontre parfois, dans les formats sérialisés notamment (coucou JSON). Mais cette erreur SQL par contre, elle couvre beaucoup de cas d’usages différents à voir les résultats de recherche que je trouve, et qui ne correspondent pas au format de données auquel je fais face.
INSERT INTO `push_mirror` (`id`, `repo_id`, `remote_name`, `remote_address`, `sync_on_commit`, `interval`, `created_unix`, `last_update`, `last_error`) VALUES (1,39,'remote_mirror_f93HyYmLhw','https://github.com/seboss666/ovh-dynhost-helm.git',1,28800000000000,1686342369,1737866664,'push failed: remote: Invalid username or password.' || X'0a' || 'fatal: Authentication failed for ''https://github.com/seboss666/ovh-dynhost-helm.git/''' || X'0a' || ' - remote: Invalid username or password.' || X'0a' || 'fatal: Authentication failed for ''https://github.com/seboss666/ovh-dynhost-helm.git/''' || X'0a' || ' - remote: Invalid username or password.' || X'0a' || 'fatal: Authentication failed for ''https://github.com/seboss666/ovh-dynhost-helm.git/''' || X'0a0a');
Je vous passe la bonne heure à tenter des trucs pourris, à me dire que peut-être je vais bricoler à la main pour retoucher les lignes, voire tenter de réécrire les INSERT en question en utilisant la fonction CONCAT (prévue justement pour concaténer des chaînes de caractères), avant de tomber sur cet article qui parle d’un comportement avec dBeaver (trousse à outil pour bases de données qui peut être assez puissant), et dont la source m’avait déjà cassé les couilles par le passé chez un client d’LBN: les modes SQL. Dans l’article, il parle d’un mode en particulier, PIPES_AS_CONCAT. Une petite vérification rapide montre que je n’ai effectivement pas ce mode d’activé par défaut, il ne reconnait donc pas la syntaxe de concaténation ! Sur l’instant je peste sur le fait qu’il va vraiment falloir que je me penche sur la configuration de mon instance MariaDB et je tente de modifier ma commande pour en arriver à la version finale:
mysql -p$MARIADB_ROOT_PASSWORD -e 'set NAMES utf8mb4; SET sql_mode=(SELECT CONCAT(@@sql_mode, ",PIPES_AS_CONCAT")); source /root/gitea-final.sql;' > source_result.log
Et là, c’est fini, plus d’erreurs !!!
Au tour de Gitea de se faire reconfigurer la face
En effet, maintenant qu’on a une base fonctionnelle, comment lui dire de l’utiliser ? Là, c’est via la documentation à la fois de gitea lui-même et de l’image docker que la solution sera trouvée.
Déjà pour commencer, j’ai la base de données, mais il me faut l’utilisateur et le mot de passe qui vont bien avec. Un coup de GRANT ALL PRIVILEGES plus tard (oui c’est toujours supporté dans MariaDB), c’est fait. Au début, je me dis que je vais la jouer ceinture/bretelles, à savoir modifier le fichier de conf ET les variables d’environnement pour m’assurer que ça fonctionne comme il faut. Côté fichier, simple, je me rends sur mon NAS, entre dans le dossier de configuration de gitea, édite le fichier avec vi (non, pas vim), et modifie les paramètres de la section [database] pour pointer sur MariaDB (la doc officielle de Gitea est particulièrement complète sur ce point).
Pour les variables d’environnement, c’est un poil plus sport. Toutes mes variables sont actuellement définies directement dans le manifeste deployment, je décide de rester comme ça pour l’instant (j’exclus pas de déporter ça dans un ConfigMap plus tard pour la lisibilité), sauf pour un seul d’entre eux, évident: le mot de passe. Lui, je le fous dans un secret via kubectl et référence celui-ci dans mon déploiement.
En gros, ça donne ça :
Au début j’ai une jolie erreur au démarrage, en vérifiant le fichier de configuration il s’avère que ce que j’avais écrit a été remplacé par le contenu fourni dans les variables d’environnement (ce qui me rassure sur le fonctionnement de l’image finalement), mais j’avais été un peu vite dans les copier/coller et merdé le pointage de la base, mauvais nom d’hôte. Je corrige et réapplique, une minute plus tard, j’entends mon NAS gratter salement. Mais pas d’erreur, je confirme en supprimant le fichier sqlite du dossier de données, tout va bien, c’est donc réel. Je tente un push des modifications sur le déploiement de Gitea pour confirmer, tout est toujours bon !
D’autres erreurs possibles ?
Étant donné que la méthode ressemble quand même un peu à du bricolage, c’est pas impossible. Dans le thread d’origine, le gars a expliqué qu’il a quand même dû reconfigurer son fournisseur d’authentification. Je sais pas si c’est lié directement à la migration ou à un autre point de configuration en lien (l’archi autour a peut-être changé, le mode de déploiement aussi, etc), mais en tout cas, dans mon cas, il s’avère que les rares erreurs sont plus liées à la configuration « par défaut » de mon instance MariaDB qui mériterait un peu plus d’amour (ne serait-ce que pour la mettre à jour).
Et on a la chance de pas avoir de contraintes sur de la clé étrangère ou autre, auquel cas il aurait probablement fallu passer par le snippet de Deblan (alias deblantv sur Twitch) qu’il m’a gentiment écrit pendant le live, qui permet de rajouter les commandes de désactivation des contraintes de clés avant de tenter les INSERT. On n’en a pas eu besoin au final, mais je tenais à le remercier pour sa participation, c’est donc tout naturellement que je l’ai tenu informé sur Mastodon en avant-première des avancées, étant donné que c’est sur ce réseau qu’il a vu que j’étais en live et est passé nous voir.
Enfin voilà, j’ai quand même réussi à faire ce que je voulais, et ça fonctionne comme je l’attendais. C’était pourtant pas gagné
Connexion
