Bon, en fait, non, je ne pense pas vraiment ce que je dis dans le titre de cette entrée : le SQL est, au niveau syntaxique, un langage invraisemblablement pourri. En revanche, ce qui est vrai, c'est qu'au chapitre des technologies qui donnent envie de s'en servir, quand on a une base de données SQL, on a envie de faire des requêtes dessus pour tout et n'importe quoi. Or il se trouve que les entrées de ce blog sont stockées dans une base PostgreSQL (alimentée depuis les fichiers XML que je tape moi-même à la main). Donc je peux avoir la réponse à plein de questions inutiles, du genre :
- Les 5 entrées les plus longues :
SELECT edate , id , title , length(content::text) AS len FROM entries ORDER BY len DESC LIMIT 5 ;(réponse : la plus longue, sans surprise, est celle concernant la vérité en mathématiques avec 105687 caractères dans le source, suivie de celle sur les polynômes symétriques avec 91005, celle sur la définition des schémas avec 85217, celle sur les nombres surréels avec 80163, et celle sur la physique fondamentale avec 72161). - Les 5 mois où j'ai le plus écrit en nombre de
caractères :
SELECT substring(edate from 1 for 7) AS month , sum(length(content::text)) AS totallen FROM entries GROUP BY month ORDER BY totallen DESC LIMIT 5 ;(réponse : octobre 2011 avec 240253 caractères, suivi de août 2003 avec 215774 caractères, septembre 2003 avec 212698 caractères, octobre 2003 avec 177191 caractères, et janvier 2004 avec 174844 caractères). - Les 5 mois où j'ai le plus écrit en nombre
d'entrées :
SELECT substring(edate from 1 for 7) AS month , count(length(content::text)) AS totalcnt FROM entries GROUP BY month ORDER BY totalcnt DESC LIMIT 5 ;(réponse : août 2003 avec 79 entrées suivi de, septembre 2003 avec 67 entrées, octobre 2003 avec 62 entrées, janvier 2004 avec 58 entrées et février 2004 avec 53 entrées). - Les 5 mois où j'ai écrit les entrées les plus longues en
moyenne :
SELECT substring(edate from 1 for 7) AS month , sum(length(content::text))/count(length(content::text)) AS meanlen FROM entries GROUP BY month ORDER BY meanlen DESC LIMIT 5 ;(réponse : avril 2014 avec 21426 caractères en moyenne, suivi de décembre 2012 avec 20952 caractères en moyenne, novembre 2011 avec 20301 caractères en moyenne, décembre 2011 avec 18834 caractères en moyenne et décembre 2013 avec 18031 caractères en moyenne). - Les 5 entrées les plus fréquemment liées depuis
d'autres :
WITH wreftable AS ( SELECT edate , id , (unnest(xpath('.//h:a/@d:wref', content, ARRAY[ARRAY['h', 'http://www.w3.org/1999/xhtml'], ARRAY['d', 'http://www.madore.org/~david/NS/daml/']])))::text AS wref FROM entries ) , linktable AS ( SELECT edate , id , substring(wref FROM '#d.([0-9]{4}-[0-9]{2}-[0-9]{2})') AS tgt_edate , substring(wref FROM '#d.[0-9]{4}-[0-9]{2}-[0-9]{2}.([0-9]{4})')::integer AS tgt_id FROM wreftable ) SELECT tgt_edate AS edate , tgt_id AS id , title , count(l.id) AS cnt FROM entries e , linktable l WHERE e.id=l.tgt_id AND e.edate=l.tgt_edate AND l.tgt_id != l.id GROUP BY tgt_edate , tgt_id , title ORDER BY cnt DESC LIMIT 5 ;(réponse : l'entrée la plus souvent citée par moi-même est celle-ci, 44 fois, suivie de celle-ci, 25 fois, celle-ci, 18 fois, la toute première, 16 fois, et celle qui est aussi la plus longue, aussi 16 fois ; en bonne logiquexkcd, ces liens sont eux-mêmes comptés). - Les 5 liens externes que j'ai faits le plus souvent :
WITH hreftable AS ( SELECT edate , id , (unnest(xpath('.//h:a/@href', content, ARRAY[ARRAY['h', 'http://www.w3.org/1999/xhtml'], ARRAY['d', 'http://www.madore.org/~david/NS/daml/']])))::text AS href FROM entries ) SELECT href , count(id) AS cnt FROM hreftable WHERE NOT href ~ '^\.' GROUP BY href ORDER BY cnt DESC LIMIT 5 ;(réponse : c'est Mozilla qui vient en premier, 44 fois, avant Google, 32 fois, Unicode, 29 fois, l'ENS, aussi 29 fois, et Debian, 18 fois ; de nouveau, ces liens sont eux-mêmes comptés).
J'aurais aussi aimé extraire quelque chose comme
« les balises HTML que j'aime le plus », mais apparemment
le XPath de PostgreSQL 9.1
est buggué limité : si
j'essaie SELECT unnest(xpath('.//name()',
'<foo><bar /></foo>'::xml)) ;, il me prétend
que .//name() est une expression XPath
invalide, ce qui est clairement faux parce qu'elle
nécessite XPath 2.0 (il est probablement gêné par le
fait que ça renvoie des chaînes au lieu de renvoyer
du XML — mais je ne vois pas comment faire
autrement).
Bon, bien sûr, j'ai écrit tout ça juste pour pouvoir me souvenir plus tard de comment on fait ce type de requêtes. Quand j'aurai unifié la base des commentaires à la base des entrées, je pourrai poser d'autres questions rigolotes. Mais en tout état de cause, il y a quelque chose de vraiment amusant à pouvoir interroger des ensembles de données de cette façon (fût-ce avec une syntaxe complètement pourrie). À un certain moment, un forum de discussion que je fréquentais à l'ENS avait une base de données des messages dans le même genre, et c'était très distrayant de pouvoir lui poser toutes sortes de questions menant à des statistiques gratuites et vaguement absurdes (du style : classer les intervenants par le nombre moyen de réponses que leurs messages provoquent).
Remarquer que les navigateurs permettent aussi des choses
amusantes. Par exemple, j'ai récemment voulu couper les entrées les
plus longues de ce blog (« couper » signifiant que l'entrée complète
n'apparaît que sur sa page individuelle, les pages du mois ou des
catégories la contenant étant abrégées par un lien lire la
suite
qui y renvoie). La recherche des entrées par nombre de
caractères du source peut ne pas être la plus pertinente, alors j'ai
recherché les entrées, dans une page comme celle rassemblant
mes entrées mathématiques
(et qui est beaucoup trop longue) en triant par nombre de
pixels du rendu effectué par le navigateur : il suffit pour cela
d'ouvrir la page en question, d'ouvrir une console JavaScript
(control-shift-K sous Firefox) et de taper quelque chose comme
ceci :
entries = Array.prototype.slice.call(document.querySelectorAll("article.weblog-entry"))
entries.sort(function(a,b){var ah=a.clientHeight; var bh=b.clientHeight; return (ah<bh ? 1 : ah>bh ? -1 : 0);})
list = entries.slice(0, 10).map(e=>({"id": e.id, "height": e.clientHeight, "title": e.querySelector(".entry-title").textContent}))
console.table(list)
Et hop ! J'ai un joli tableau des dix entrées les plus hautes (en
pixels) dans la page que je suis en train de regarder. La
fonction console.table est
un goodie
de Firefox (disponible à partir de la version 34) qui est bien
pratique dans ce genre de situation. La ligne précédente utilise
les lambda-expressions
d'ECMAscript 6, qui sont aussi bien pratiques.
Bref, JavaScript — et la console JavaScript des navigateurs — est
aussi quelque chose de bien pratique et puissant (je prends l'exemple
de mon blog, mais ça peut servir dans plein de contextes d'interroger
ou de manipuler la page HTML qu'on est en train de
regarder), malheureusement, comme SQL, encombré par une
syntaxe souvent très peu heureuse.