Wikipédia:Requêtes SQL

Introduction

MediaWiki utilise le serveur de base de données relationnelle MySQL pour stocker ses données. Comme pour toute base de données, il est possible de lui adresser des requêtes pour obtenir par exemple la liste des articles modifiés un jour donné. C'est un outil très utile pour la maintenance et pour guider les bots dans leur travail.

Les requêtes peuvent se faire de plusieurs façons :

• en demandant à un développeur de faire la requête pour vous (demande à faire sur m:Requests for queries)

 : les développeurs font des requêtes sur les versions actuelles des bases de données, ils peuvent lancer des requêtes qui modifient les données

 : il faut attendre plusieurs heures ou jours avant que sa requête soit effectuée. La requête peut être inadaptée.

• via un outil externe comme Wikisign : http://www.wikisign.org (site fermé, continuera peut-être sur toolserver ou sur les serveurs hébergés en France)

 : évite d'avoir à installer et télécharger une base de données, permet de faire des requêtes sur tous les Wikipédia

 : temps d'attente (la requête est placée dans une file), travaille sur des bases qui ne sont pas toujours à jour

• sur son ordinateur, en téléchargeant la base de données du projet désiré [1] et en installant MySQL. Il faut toutefois une bonne connexion à Internet avant d'envisager ce type d'utilisation.

 : On peut faire la requête que l'on veut et rapidement, possibilité d'expérimenter et de faire un miroir de Wikipédia.

 : la base n'est pas à jour et seules les requêtes d'extraction ont un réel intérêt (les modifications dans votre version locale ne servent pas à grand chose). Il faut régulièrement mettre à jour la base de données.

Les admins pouvaient auparavant faire des requêtes SQL sur la base en temps réel mais cette fonction a été désactivée le 19 mai 2005.

Depuis l'été 2005, les dumps SQL ont été remplacé par des dumps en XML. Des informations supplémentaires sont disponibles sur la page « Requêtes XML », il est possible d'insérer la base en XML dans une base de type SQL. Les instructions contenues dans cet article restent donc valables et utiles.

Voir aussi :

• w:Wikipedia:Database_queries
• structure des tables

Tutorial : installation locale et requêtes

Les requêtes peuvent être faites sur votre ordinateur pour autant que vous ayez MySQL et la base de données du projet. Ce tutorial donne les grandes lignes pour l'installation. Pour plus d'informations, il est fortement conseillé de se référer à la documentation de MySQL. Le manuel en français se trouve sur : http://dev.mysql.com/doc/mysql/fr/index.html.

Téléchargement de la base de données

La première étape consiste à télécharger un dump de la base de données désirée. Toutes les bases de données se trouvent sur le site de Wikimedia : download.wikimedia.org. Pour débuter, on prendra la table cur du projet fr.wikipedia.org. Elle fait environ 1 Go. Pour avoir les tables complètes du Wikipédia francophone, il faudra télécharger plusieurs Go de données.

Attention : Depuis l'été 2005, les dumps SQL ont été remplacé par des dumps en XML. Des informations supplémentaires sont disponibles sur la page « Requêtes XML », il est possible d'insérer la base en XML dans une base de type SQL.

Installation de MySQL

Attention, il semble que certaines versions de MySQL sont assez problématiques avec les énormes bases de données de Wikipédia. Les versions testées avec succès sont la 4.1.15 (sous Debian) et la 5.0.9 (sous Gentoo). Avec la version 4.0.24, l'insertion de la base de données depuis le fichier provoque des erreurs comme cur table is full qu'il n'a pas été possible de corriger malgré l'utilisation du SET BIG_TABLES = 1.

GNU/Linux

La procédure varie ici selon votre distribution GNU/Linux. Il y a des paquets déjà tout prêts pour Debian, Slackware, RedHat, etc. Les utilisateurs de Gentoo pourront passer par portage (emerge mysql). Si aucun paquet n'est disponible pour votre distribution ou que vous voulez compiler directement les sources, vous devez les télécharger depuis MySQL.com. Les informations sur la compilation sont présentes dans le fichier ou sur le site.

Windows

Vous trouverez le programme d'installation de MySQL sur le site officiel MySQL.com. À noter que vous pouvez probablement passer par EasyPHP : easyphp.org qui comprend également MySQL (pas testé). Vous devez lancer le serveur MySQL via le menu « Démarrer » ou alors en ligne de commande via « l'Invite de commandes » (menu Démarrer → Exécuter... → cmd, ensuite mysql).

Installation de la base de données téléchargée depuis Wikimedia

Une fois le téléchargement terminé, il faut décompresser le fichier avec gunzip (fichier ayant l'extension .gz) ou avec bunzip2 (fichier ayant l'extension .bz2). Sous Windows, vous pouvez utiliser l'utilitaire 7-Zip pour faire cela. Contrôlez que vous ayez assez de place sur le disque, le fichier final au format SQL est environ 5 fois plus gros que le fichier compressé. Une fois le fichier prêt, il faut créer la base de données dans MySQL et insérer les informations du script sql.

Attention : Depuis l'été 2005, les dumps SQL ont été remplacés par des dumps en XML. Des informations supplémentaires sont disponibles sur la page « Requêtes XML », il est possible d'insérer la base en XML dans une base de type SQL.

Dans MySQL, il faut taper les commandes suivantes :

wikilover@wikipedia:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 5.0.10-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE wikipedia DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.37 sec)
mysql> USE wikipedia;
Database changed
mysql> SOURCE nom_du_fichier_wikimedia.sql;

La copie des informations dans les tables après avoir lancé SOURCE peut prendre plusieurs dizaines de minutes, voire plus d'une heure sur des machines moins récentes.

Essais de requêtes

Pour vérifier que tout a l'air bien installé, nous allons lancer une requête sur la base de données :

mysql> SELECT cur_namespace, cur_title, cur_timestamp FROM cur LIMIT 10;

Vous devriez obtenir quelque chose de similaire à cette sortie :

+---------------+-----------------------+----------------+
| cur_namespace | cur_title             | cur_timestamp  |
+---------------+-----------------------+----------------+
|             0 | $CAN                  | 20041130131746 |
|             0 | 'Bar-khams            | 20050411081441 |
|             0 | 'Ndrangheta           | 20050604203559 |
|             0 | (1)_Cérès             | 20050321202409 |
|             0 | (1036)_Ganymède       | 20050619045642 |
|             0 | (10979)_Fristephenson | 20050619142704 |
|             0 | (11169)_Alkon         | 20050619142533 |
|             0 | (1221)_Amor           | 20050619045612 |
|             0 | (132)_Aethra          | 20050619052312 |
|             0 | (133)_Cyrène          | 20050619024124 |
+---------------+-----------------------+----------------+
10 rows in set (0.00 sec)

Les articles se trouvent dans la table cur (pour current articles). Pour avoir une idée des champs et de la structure de la base de données, le lien suivant (en anglais) est incontournable : résumé des tables

Pour un tutorial en français sur les requêtes et la syntaxe SQL : [2]. Attention ! Certaines syntaxes possibles avec Oracle ou PostgreSQL ne sont pas possibles ou formulées différemment avec MySQL.

Essayons une autre requête. Nous allons extraire le texte de l'article RC4. Il faut donc faire une requête sur la table cur où le titre cur_title est égal à "RC4" en indiquant que nous désirons en sortie le contenu, soit cur_text. Cela peut se faire de cette manière :

mysql> SELECT cur_text FROM cur WHERE cur_title='RC4'; 

Vous devriez obtenir ceci (l'article peut avoir été modifié depuis). La sortie a été volontairement formatée et raccourcie avec des retours à la ligne:

+-------------------------------------------------------------------
--------------------------------------------------------------------
---------------------------+
| cur_text                                                          
                                                                    
                                                                    
                                                                    
                                                                    
                                                                    
                                                                    
                           |
+-------------------------------------------------------------------
--------------------------------------------------------------------
---------------------------+
| [[de:RC4]] [[en:RC4]] [[nl:RC4 (encryptie)]] [[pt:RC4]]

'''RC4''' est une méthode de [[chiffrement]] de flux issue des [[Laboratoires RSA]].
Elle a été principalement conçue par [[Ronald Rivest]], professeur au [[MIT]] 
et coauteur du chiffrement [[Rivest Shamir Adleman|RSA]].

[[Arcfour]] est une méthode libre de chiffrement, similaire à RC4 et postée sur 
[[Usenet]] par un anonyme affirmant avoir désassemblé RC4.

[[Catégorie:Algorithme de cryptographie symétrique]]

{{Cryptologie}}            |
+----------------------------------------------------------------
---------------------------+
1 row in set (0.00 sec)

Maintenant, nous allons déterminer la date de la dernière modification :

mysql> SELECT cur_timestamp FROM cur WHERE cur_title = 'RC4' ;

+----------------+
| cur_timestamp  |
+----------------+
| 20050521141535 |
+----------------+
1 row in set (0.00 sec)

La sortie n'est pas très lisible, il est possible de la formater pour avoir une date plus convenable :

mysql> SELECT DATE_FORMAT(cur_timestamp, '%Y %D %M %h:%i:%s %x') FROM cur WHERE cur_title = 'RC4';

+----------------------------------------------------+
| DATE_FORMAT(cur_timestamp, '%Y %D %M %h:%i:%s %x') |
+----------------------------------------------------+
| 2005 21st May 02:15:35 2005                        |
+----------------------------------------------------+
1 row in set (0.00 sec)

Ces requêtes sont simples mais elles peuvent être plus complexes avec des temps de recherche plus longs. N'oubliez pas que la base de données d'un Wikipédia est très grosse et que cela peut prendre plusieurs minutes.

Convertir un résultat MySQL vers la syntaxe Wiki

Pour convertir une liste depuis la sortie en mode texte de MySQL vers une syntaxe Wiki, on peut passer par des expressions rationnelles. À la main c'est aussi possible mais fastidieux.

Par exemple, nous voulons faire une liste de 10 articles qui contiennent le nom "Terre du Milieu". Nous lançons la requête suivante qui vérifie le contenu du texte des articles. On obtient une liste de 10 articles :

mysql> SELECT cur_title FROM cur WHERE cur_text LIKE "%Terre du Milieu%" LIMIT 10;
+--------------------------------------+
| cur_title                            |
+--------------------------------------+
| Lune                                 |
| Mythes_et_création_du_monde          |
| Fantastique                          |
| Alphabet                             |
| John_Ronald_Reuel_Tolkien            |
| Comté                                |
| Le_Seigneur_des_Anneaux              |
| Sauron                               |
| Jeu_en_ligne_massivement_multijoueur |
| Hobbit                               |
+--------------------------------------+
10 rows in set (32.78 sec)

Sous GNU/Linux, il existe un utilitaire nommé Sed qui va nous aider pour la conversion. Sous Windows, cet utilitaire est présent dans la suite Unix Utils for Windows que nous ne saurions trop vous conseiller d'installer mais aussi dans Cygwin.

La première chose à faire est de copier la sortie MySQL ci-dessus dans un fichier (pas besoin de tout copier, seulement les lignes où apparaissent les articles). Ensuite, on invoque Sed avec une expression rationnelle qui substitue la barre verticale de gauche par [[. On chaîne cela avec une seconde substitution qui cherche une suite d'espace avec une barre verticale à la fin, cette chaîne est remplacée par ]]. La syntaxe des expressions rationnelles fait assez peur au départ mais en lisant quelques tutoriaux (par exemple [3]), vous devriez y voir plus clair.

Au final, le remplacement se fait comme ceci :

dake@gentoo /tmp $ sed -e 's/^| /[[/g;s/ *|$/]]/g' terre.txt

[[Lune]]
[[Mythes_et_création_du_monde]]
[[Fantastique]]
[[Alphabet]]
[[John_Ronald_Reuel_Tolkien]]
[[Comté]]
[[Le_Seigneur_des_Anneaux]]
[[Sauron]]
[[Jeu_en_ligne_massivement_multijoueur]]
[[Hobbit]]

Pour ajouter une étoile (pour faire une liste) au début de chaque article et trier la liste par ordre alphabétique, il suffit de modifier la conversion et d'appeler l'utilitaire sort :

dake@gentoo /tmp $ sed -e 's/^| /*[[/g;s/ *|$/]]/g' terre.txt | sort

*[[Alphabet]]
*[[Comté]]
*[[Fantastique]]
*[[Hobbit]]
*[[Jeu_en_ligne_massivement_multijoueur]]
*[[John_Ronald_Reuel_Tolkien]]
*[[Le_Seigneur_des_Anneaux]]
*[[Lune]]
*[[Mythes_et_création_du_monde]]
*[[Sauron]]

Requêtes utiles

Articles

Recherche des pages protégées

    SELECT cur_title, cur_restrictions, cur_namespace
    FROM cur
    WHERE cur_restrictions != ''
    ORDER BY cur_title
    LIMIT 100

Recherche des pages contenues dans l'espace méta

   SELECT cur_title
   FROM cur
   WHERE cur_namespace = 4
   ORDER BY cur_title
   LIMIT 100

Rechercher les pages qui ne sont pas liées à un autre Wikipedia

(pour l'anglais) :

   SELECT cur_title
   FROM cur
   WHERE cur_text NOT LIKE "%» :
   SELECT cur_title
   FROM cur
   WHERE LENGTH(cur_title) > 49
     AND cur_namespace = 0
     AND NOT cur_is_redirect
   ORDER BY LENGTH(cur_title) DESC, cur_title ASC
   LIMIT 100

Recherche les pages dont aucun mot de paragraphe d'introduction n'est en gras

    SELECT cur_title
    FROM cur
    WHERE cur_namespace = 0
      AND cur_is_redirect = 0
      AND cur_text NOT LIKE "%'''%"
      AND cur_text NOT LIKE "%<b>%"
      AND cur_text NOT LIKE "%disambiguation%"
      AND cur_text NOT LIKE "%list%"
      AND cur_title NOT LIKE "1%"
    LIMIT 200, 150

Liste de toutes les pages meta

Liste de toutes les pages qui ont été créées après une certaine date.

• pour les derniers jours

   SELECT rc_timestamp, rc_namespace, rc_title
   FROM recentchanges
   WHERE rc_new = 1
     AND rc_minor = 0
     AND rc_namespace = 4

• Pour trouver la date de création d'une page, vous devez chercher, dans la table old, la plus ancienne édition pour cette page (si elle a été modifiée plus d'une fois !). Il faut donc chercher les 'plus récentes des plus anciennes modifications'.... Attention : cela peut être très long:

   SELECT min(old_timestamp), old_namespace, old_title AS creation
   FROM old
   WHERE old_namespace = 4 GROUP BY old_title
   ORDER BY creation DESC

• Celles qui n'ont été éditées qu'une fois sont faciles à trouver, parce que la table cur marque les édits qui sont nouveaux:

   SELECT cur_timestamp AS creation, cur_namespace, cur_title
   FROM cur
   WHERE cur_namespace = 4
     AND cur_is_new = 1
     AND cur_is_redirect = 0
   ORDER BY creation DESC

Liste de toutes les pages incluses dans l'espace encyclopédique, qui contiennent une certaine chaîne de caractères

   SELECT cur_namespace, cur_title
   FROM searchindex, cur
   WHERE si_page = cur_id
     AND MATCH(si_text) AGAINST('some phrase' IN BOOLEAN MODE)

Plus lent:

   SELECT cur_namespace, cur_title
   FROM cur
   WHERE cur_text LIKE '%some phrase%'

Liste les articles courts

   SELECT cur_namespace, cur_title, CHAR_LENGTH(cur_text)
   FROM cur
   WHERE cur_namespace = 0
     AND cur_is_redirect = 0
     AND LENGTH(cur_text) < 300
   ORDER BY CHAR_LENGTH(cur_text) ASC
   LIMIT 500

Recherche des pages des anciennes phases

   SELECT cur_title, cur_namespace, cur_is_redirect
   FROM cur
   WHERE cur_title LIKE 'Wikipedia-%'
   LIMIT 50

Articles les plus liés

   SELECT cur_title, COUNT(*) AS cnt
   FROM cur, links
   WHERE links.l_to = cur.cur_id
     AND cur_namespace = 0
   GROUP BY links.l_to
   ORDER BY cnt DESC
   LIMIT 100

Page de discussion

Recherche des pages de discussion orphelines

   SELECT cur_title
   FROM cur
   GROUP BY cur_title
   HAVING MIN(cur_namespace) = 1

Images

Images d'un utilisateur

   SELECT img_size, img_name, img_description, img_timestamp
   FROM image
   WHERE img_user_text = 'Pierre Dupont'
   ORDER BY img_timestamp DESC
   LIMIT 1000

Images sans catégorie (licence inconnue) et comprenant le terme "logo"

   SELECT cur_namespace, cur_title
   FROM cur
   WHERE cur_namespace = 6
     AND cur_text LIKE '%logo%'
     AND cur_text NOT LIKE '%{{%'
   ORDER BY cur_title, cur_namespace
   LIMIT 4000

Liste les articles par nombre d'images

   SELECT count(*) AS numlinks, cur_title
   FROM imagelinks, cur 
   WHERE cur_namespace = 0 AND il_from = cur_id
   GROUP BY il_from
   ORDER BY numlinks DESC
   LIMIT 500

Media

Liste les fichiers audio

   SELECT img_name, img_user
   FROM image
   WHERE img_media_type = "AUDIO"
   ORDER BY img_name
   LIMIT 1000

PS : on peut remplacer AUDIO par VIDEO, MULTIMEDIA...

Recherche d'une redirection

   SELECT cur_title
   FROM cur
   WHERE cur_is_redirect=1
   LIMIT 99

Redirect ayant au moins une page qui pointe sur eux

   SELECT cur_title, cur_namespace, COUNT(l_from)
   FROM cur left join links ON l_to = cur_id
   WHERE cur_is_redirect = 1
   GROUP BY cur_title, cur_namespace
   HAVING count(l_from) > 0
   LIMIT 100

Recherche les redirects cassés

   SELECT cur_title, cur_namespace
   FROM cur, brokenlinks
   WHERE cur_id = bl_from AND cur_is_redirect = 1
   LIMIT 100

Articles contenant un lien vers un redirect

donc potentiellement à corriger (Ryo):

   SELECT ca.cur_title, cb.cur_title AS lien
   FROM links, cur AS ca, cur AS cb
   WHERE cb.cur_is_redirect = 1
     AND l_to = cb.cur_id
     AND l_from = ca.cur_title
     AND ca.cur_namespace = 0
   LIMIT 20

Utilisateurs

Recherche des nouveaux utilisateurs

   SELECT user_name, COUNT(*)
   FROM user, cur
   WHERE user_id=cur_user
   GROUP BY user_id
   ORDER BY user_id DESC
   LIMIT 10,20

Statistiques

Pour calculer les stats, on peut se servir d'un programme bot. Tim en a écrit un en Rebol, mais je n'ai pas encore eu le temps de le tester. -- youssef

Toutes les éditions (top) de cette semaine, par ordre alphabétique.

(Changer le 'timestamp' suivant la date recherchée.)

   SELECT cur_user_text, COUNT(*) AS count
   FROM cur
   WHERE cur_timestamp > '20030209000000'
     AND cur_timestamp < '20030218000000'
     AND cur_user != 0
   GROUP BY cur_user
   ORDER BY cur_user_text asc

Les utilisateurs les plus actifs :

   SELECT cur_user_text, COUNT(*) AS count
   FROM cur
   WHERE cur_timestamp > '20030209000000'
     AND cur_timestamp < '20030218000000'
     AND cur_user != 0
   GROUP BY cur_user ORDER BY count DESC
   LIMIT 20

en total :

   SELECT cur_user_text, COUNT(*) AS count
   FROM cur
   WHERE cur_user != 0
   GROUP BY cur_user ORDER BY count DESC
   LIMIT 100

sur RC :

   SELECT rc_user_text, COUNT(*) AS count
   FROM recentchanges
   WHERE rc_user != 0
   GROUP BY rc_user_text ORDER BY count DESC
   LIMIT 100

Éditions

   SELECT rc_user_text, COUNT(*) AS count
   FROM recentchanges
   WHERE rc_user != 0
     AND rc_timestamp > '20030209000000'
     AND rc_timestamp < '20030218000000'
   GROUP BY rc_user_text
   ORDER BY rc_user_text
   LIMIT 999

Liens externes

• Projet interrompu
• m:Requests for queries
• pt:Wikipedia:Estaleiro/Estatísticas