Le Hollandais Volant

Un article plutôt « contre » SQLite dans un environnement serveur (typiquement un site web).

Le principal argument pour moi est celui-ci :

Decoupling storage from compute is the default architecture because it’s a really good idea.

Avec les serveurs SQL, on peut installer le gestionnaire de base de données sur une machine et le serveur web sur une autre. C’est même l’idée de base.

Avec SQLite, on n’a pas à faire ça, mais alors une seule machine et probablement aussi un seul processus gère la totalité d’une action demandée, ce qui peut la ralentir.

Mon site tourne sur SQLite parce qu’il est léger. Je ne suis ni Facebook, ni Amazon et je n’ai pas besoin de beaucoup. Mais dès qu’on veut monter en charge, il faut scinder les tâches et pour ça SQLite ne marchera pas.

Après l’article dit bien que SQLite n’a aucun problème de fiabilité ni scalabilité : une base de données SQLite de plusieurs Go fonctionnera très bien (si elle est bien architecturée). C’est juste que SQLite est assez mauvais pour gérer des tâches en parallèle, et c’est ça qui fera qu’elle sera mise à rude épreuve dès qu’un site Web commence à avoir beaucoup de visiteurs simultanées, en particulier en écriture.

Un blog unipersonnel avec peu d’écritures concurrentes (blog, etc.) n’aura aucun problème à fonctionner sous SQLite. Même avec beaucoup de visites. Mais n’utilisez pas ça pour un forum, un tchat, un réseau social.

Une explication sur le LEFT JOIN en SQL. Et pourquoi le « ON » doit rester simple, avec les filtres et conditions suivants mis dans un WHERE.

(et effectivement, les jointures SQL sont le genre de trucs où on prend très vite la tête, un peu comme les regex, même si personnellement je préfère les regex)

Pourquoi SQLite mérite d'être considéré à sa juste valeur pour les projets de développement web.

Je plussoie.
Pour des applications personnelles (blogs, etc.) c’est l’idéal. Les limites de SQLite ne sont pas un problème pour un blog. La limite du nombre d’écritures concurrentes par exemple ne se posera que pour l’écriture d’un forum, d’un tchat, d’un "pad" à plusieurs ou d’un Wiki avec plein de gens qui bossent dessus en même temps.

Et il existe des API ou des soft pour lire du SQLite dans tous les langages de prog et sur tous les systèmes.

Un outil (en ligne et en JS) pour explorer les méta-données d’un fichier .sqlite

C’est vraiment génial ça :-D

(oui je découvre des choses :p)

Normalement, en PDO, un fetchAll() retourne un objet, ou, dans mon cas, un tableau. Chaque élément du tableau est lui-même un tableau contenant les colonnes de la BDD. :

$bdd->query("SELECT id, nom, prenom FROM table")->fetchAll(PDO::FETCH_ASSOC);

array(
  [0] => Array
        (
            [id] => 184e34da69fe1a6b2337be58388dd3b9
            [nom] => dupond
            [prenom] => jean
        )

  [1] => Array
        (
            [id] => 53957652a3c1f9b6790717888fea0a31
            [nom] => ofgates
            [prenom] => john
        )
)

Notez que j’ai utilisé PDO::FETCH_ASSOC qui permet d’avoir un tableau associatif "colonne => valeur". Si on ne le met pas, on aura un index de la colonne, en plus d’un assoc :

  [1] => Array
        (
            [0] => 53957652a3c1f9b6790717888fea0a31
            [id] => 53957652a3c1f9b6790717888fea0a31
            [1] => ofgates
            [nom] => ofgates
            [2] => john
            [prenom] => john
        )

Ça peut servir si l’on fait des ittérations numériques sur les index, mais dans mon cas, j’utilise toujours les noms des colonnes.

Maintenant, problème : dans le tableau de tous les résultats obtenu avec fetchAll(), on a un index : 0, 1, mis en évidence là :

  [0] => Array
        (
            …
        )

  [1] => Array
        (
           … 
        )

Moi j’ai besoin que ces 0 et 1 soient remplacés par l’ID de l’élément du tableau.
Je sais que dans ma BDD, l’ID, qui n’est autre qu’un MD5() ou un GUID, est unique.

Je peux utiliser l’option PDO::FETCH_UNIQUE :

$bdd->query("SELECT id, nom, prenom FROM table")->fetchAll(PDO::FETCH_UNIQUE);

Cela va utiliser le contenu de la première colonne ("id") comme index du tableau retourné par fetchAll() :

array(
  [184e34da69fe1a6b2337be58388dd3b9] => Array
        (
            [0] => 184e34da69fe1a6b2337be58388dd3b9
            [id] => 184e34da69fe1a6b2337be58388dd3b9
            [1] => dupond
            [nom] => dupond
            [2] => jean
            [prenom] => jean
        )

  [53957652a3c1f9b6790717888fea0a31] => Array
        (
            [0] => 53957652a3c1f9b6790717888fea0a31
            [id] => 53957652a3c1f9b6790717888fea0a31
            [1] => ofgates
            [nom] => ofgates
            [2] => john
            [prenom] => john
        )
)

Ça marche, mais ça remis les données redondantes.
Heureusement, on peut mettre les deux options :

$bdd->query("SELECT id, nom, prenom FROM table")->fetchAll(PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

Et là, j’ai ce que je veux :

array(
  [184e34da69fe1a6b2337be58388dd3b9] => Array
        (
            [id] => 184e34da69fe1a6b2337be58388dd3b9
            [nom] => dupond
            [prenom] => jean
        )

  [53957652a3c1f9b6790717888fea0a31] => Array
        (
            [id] => 53957652a3c1f9b6790717888fea0a31
            [nom] => ofgates
            [prenom] => john
        )
)

Attention :
— il existe un grand nombre d’options PDO. Ici, j’en ai deux qui sont compatibles entre-elles. Toutes ne le sont pas.
— FETCH_UNIQUE va faire en sorte que les éléments de l’index du tableau seront unique (c’est obligé, pour un tableau). Ici ça ne poste pas de problème car l’ID est un hash unique (je déclare cette colonne comme UNIQUE quand je construit la BDD, par exemple). Si votre "id" n’est pas unique, les derniers écraseront les premiers.
— FETCH_UNIQUE utilise la première colonne demandé dans la requête. Ici, c’est mon "id". Si j’avais voulu utiliser "nom", j’aurais dû utiliser cette requête :

$bdd->query("SELECT nom, id, prenom FROM table")->fetchAll(PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

Ici, la première colonne est "nom", et "id" vient en second, suivi de "prenom".

Bien-sûr, si l’utilise le « * », on peut faire ça :

$bdd->query("SELECT nom, * FROM table")->fetchAll(PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

Pour forcer d’avoir "nom" en premier, même si ce n’est pas la colonne en première dans la liste des colonnes de la BDD.

J’arrête là pour les exemples, ça me suffit.
Je vous laisse voir les différents modes. Le "FETCH_KEY_PAIR" peut-être utile parfois aussi.

Bon, un truc probablement trivial en SQL, mais je dois la noter.

Pour mon lecteur RSS.
J’ai une table avec les flux, et une table avec les posts.
Dans celle avec les posts, j’ai l’id du flux. Logique.

Quand je liste les flux, je veux un compte des nombres de posts non lus qui lui sont associés :

flux1 : 10
flux2 : 5
flux3 : 0
flux4 : 0

Donc ça :

SELECT flux.id, COUNT(posts.id) AS unread FROM flux LEFT JOIN posts ON (posts.flux_id = flux.id) WHERE posts.unread=TRUE GROUP BY flux.id 

Sauf que ça, ça me retourne :

flux1 : 10
flux2 : 5

Comment faire pour avoir ceux où le nombre de posts est 0 ?
Il suffit de mettre le WHERE dans le la parenthèse :

SELECT flux.id, COUNT(posts.id) AS unread FROM flux LEFT JOIN posts ON (posts.flux_id = flux.id AND posts.unread=TRUE) GROUP BY flux.id 

Et là :

flux1 : 10
flux2 : 5
flux3 : 0
flux4 : 0

*faceplam*
J’ai mis beaucoup trop de temps à chercher ça

Du coup la requête complète, avec un ordre selon le titre du site dont j’ai le flux, et en incluant aussi (au passage), la date du dernier poste pour un flux donné :

SELECT flux.id, COUNT(posts.id) AS unread, MAX(posts.date) AS latestpost FROM flux LEFT JOIN posts ON (posts.flux_id = flux.id AND post.unread=TRUE) GROUP BY flux.id ORDER BY flux.titre

C’est là que je suis heureux de ne plus avoir de fichiers plain text serializé ;)

(Ces fichiers sérialisés sont très bon quand on a une liste de posts simple, mais ça devient vite la merde quand on a plusieurs tables dont les données sont mises en relation (les posts appartiennent à un flux, par exemple ; ou les commentaires à un article, les fichiers mp3 à un album, à un artiste…)

Ça peut être utile.

Au boulot, j’ai à répertorier des trucs. Beaucoup de trucs. Donc comme tout bon bullshitto-corporate en entreprise, j’utilise Excel. Puis ça m’a saoulé, j’ai utilisé LibreOffice Base : un truc destiné à ça, au fond, bien plus qu’un tableau.

Et puis ça m’a saoûlé : Base n’est pas pratique, passe son temps à se mettre en travers de mon chemin, etc. (j’ai pas essayé MS Access, mais vu tout le bien qu’on en dit, j’ai préféré éviter).

Du coup j’ai sorti SQLiteBrowser et j’ai tout mis dans un fichier SQLite. C’est nettement plus rapide.

Pour insérer des données, soit je fais des requêtes manuelles (si j’en ai beaucoup, j’en écris des dizaines à la fois avec SublimeText), soit je les copie-colle dans Excel, j’exporte en CSV et j’importe dans SQLiteBrowser.

C’est de la bidouille, mais c’est rapide et faisable. Et j’épate tout le monde quand je retrouve un enregistrement très précis en 4 secondes, là où les autres, encore au format papier doivent trouver le bâtiment, le carton, le dossier, puis la fiche où est l’information. Et je peux compter, filtrer, comparer, faire des stats sur les enregistrement.

SQL = le bien.

Faire un rechercher-rempacer directement avec une requête SQLite :

update table set field = replace(field, 'search', 'replace') where field like '%search%';

Remplacera directement dans le champ "field" toutes les occurrences de "search" par "replace".

bien-sûr, on peut ne pas utiliser "field" à chaque fois. On peut vouloir stocker dans "field1" le résultat d’un S&R sur le contenu d’un champ "field2", le tout avec une condition sur "field3".

update table set field1 = replace(field2, 'search', 'replace') where field3 like '%search%';

On n’est même pas obligé de mettre le "where", mais ça sera bien plus long.

Voir la doc : https://sqlite.org/lang_corefunc.html#replace

en SQL.

Woah, je ne savais pas que SQLite pouvait directement exporter en JSON, ou même faire des DB temporaires dans la RAM. :o

In a client/server database, each SQL statement requires a message round-trip from the application to the database server and back to the application. Doing over 200 round-trip messages, sequentially, can be a serious performance drag.

SQLite is not client/server, however. The SQLite database runs in the same process address space as the application. Queries do not involve message round-trips, only a function call. The latency of a single SQL query is far less in SQLite. Hence, using a large number of queries with SQLite is not the problem.

Père Noël be like :

SELECT * from children WHERE behaviour = 'nice'

Soit c’est un troll, soit c’est une critique de tous les "code of conduct" absurdes qu’on voit désormais.

J’ai du mal à voir quelqu’un capable de pondre SQLite être aussi con que ça :O

Quoi qu’il en soit, tout ça s’applique aux dév de SQLite (ceux qui font SQLite), pas ceux qui l’utilisent (ni en tant que codeurs qui utilisent SQLite dans le code, ni l’utilisateur final), donc à la limite… osef :) .

Je me mets à optimiser un peu mon lecteur RSS. Pas qu’il soit lent (il tri 70k entrées RSS en 0,22 secondes), mais je viens de trouver un truc.

Dans le lecteur, je fais une requête particulière pour compter le nombre d’éléments non lus pour chaque flux :

SELECT bt_feed, SUM(bt_statut) AS nbrun FROM rss GROUP BY bt_feed

Ensuite, en PHP, je fais une liste des flux avec le nombre d’éléments non-lus associés. Jusque là, rien d’alarmant.

Sauf que la liste des flux utilisée en PHP à ce stade est déjà recyclée. J’ai juste besoin d’une autre requête pour obtenir le nombre d’articles non lus.

Je peux optimiser un peu ma seconde requête :

SELECT bt_feed, SUM(bt_statut) AS nbrun FROM rss WHERE bt_statut = 1 GROUP BY bt_feed

Ici, le « WHERE bt_statut = 1 » signifie qu’il ne doit faire la "somme" que sur les éléments dont le statut est 1 (pas ceux où il est à 0).

Étant donnée que la BDD contient ~69 000 éléments lus (0) et seulement ~1 000 éléments non lus (1), ça revient à sommer sur seulement 1k éléments au lieu de 69k.

Ça me fait gagner environ 10~15 % du temps de la requête : je passe de 0,22 s à 0,19 s. C’est loin d’être du grappillage de micro-secondes.

Utiliser SQLite permet de gagner ~35 % de performances (en moyenne) pour stocker des fichiers, par rapport à un système de fichier normal.

Le gain de perfs est le plus impressionnant sur W10.