Wikipedia:SQL-opdrachten

---

Tot april 2004 konden moderators via een speciale pagina SQL opdrachten doen op de Wikipedia database. Vanaf april 2004 tot juni 2005 was dat alleen mogelijk door zelf een database dump te downloaden en een query te doen m.b.v. bijvoorbeeld MySQL.

Maar sinds juni 2005 heeft een Duitse Wikipediaan een website ontwikkeld, Wikisign, waar je online een SQL opdracht kunt doen op een recente database dump.

Hier volgen enkele ideeën voor zoekopdrachten:

Inhoud 1 Nieuwe gebruikers vinden 2 Problematische links 3 Statistieken trekken 4 Wikipedia Internationaal 5 Opsporen spellingsfouten 6 Directe links vinden 7 Alle pagina's van een naamruimte weergeven 8 Alle beveiligde pagina's tonen 9 Diversen 10 Vervangende opdrachten voor de uitgeschakelde speciale pagina's 11 Wijzigen status gebruiker 12 Gebruikers met sysop/ontwikkelaarsstatus vinden 13 Verdachte tekens voor toekomstige upgrades 14 Nog meer ideeën

[bewerk] Nieuwe gebruikers vinden

De volgende zoekopdracht geeft een overzicht van de laatste 20 nieuwe geregistreerde gebruikers die een artikel hebben ingevoerd. Het zou bijvoorbeeld gebruikt kunnen worden om nieuwelingen te verwelkomen:

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

Deze zoekopdracht is echter niet perfect; het registreert alleen wijzigingen waarbij niet iemand anders later dezelfde pagina heeft gewijzigd. Om die te vinden, kunt u gebruikmaken van:

SELECT user_name, COUNT(*)
FROM user, old
WHERE user_name=old_user_text
GROUP BY user_id
ORDER BY user_id
DESC LIMIT 20 

[bewerk] Problematische links

De volgende zoekopdracht vindt zogenaamde 'dubbele redirects', waarbij een pagina A redirect naar een pagina B, die zelf weer redirect naar een pagina C:

SELECT l_from, cb.cur_title, cb.cur_text
FROM links, cur AS ca, cur AS cb
WHERE ca.cur_is_redirect=1
 AND cb.cur_is_redirect=1
 AND l_to=cb.cur_id
 AND l_from=ca.cur_title
LIMIT 250

Om pagina's met verwijzingen naar zichzelf te zoeken, kan de volgende opdracht gebruikt worden (Let op! Deze kost veel rekentijd!)

SELECT cur_title
FROM cur, links
WHERE cur_is_redirect=0
 AND cur_namespace=0
 AND l_from=cur_title
 AND l_to=cur_id 

Voor redirects naar niet-bestaande pagina's kan de volgende opdracht gebruikt worden:

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

Voor pagina's die verwijzen naar doorverwijspagina's (en daar waarschijnlijk beter omheen zouden kunnen worden geleid):

SELECT cur1.cur_title, COUNT(*) AS co
FROM cur AS cur1, links AS links1, links AS links2
WHERE links2.L_from <> "Wikipedia:Doorverwijspagina"
 AND links2.l_to = cur1.cur_id
 AND links1.l_from="Wikipedia:Doorverwijspagina"
 AND links1.l_to = cur1.cur_id
GROUP BY cur1.cur_title
ORDER BY co DESC
LIMIT 100

[bewerk] Statistieken trekken

Om de artikels met meer dan 1500 tekens te vinden:

SELECT COUNT(*)
FROM cur
WHERE LENGTH(cur_text)>1500
 AND cur_namespace=0 

[bewerk] Wikipedia Internationaal

Voor pagina's die nog geen en: verwijzing hebben:

SELECT cur_title
FROM cur
WHERE cur_text NOT LIKE "%[[en:%"
 AND cur_namespace=0
 AND cur_is_redirect=0

Een dergelijke lijst kan natuurlijk ook voor een andere taal worden aangemaakt (vervang dan de en: door de aanduiding van de betreffende taal), maar is dan natuurlijk wel een stuk langer...

[bewerk] Opsporen spellingsfouten

Om alle pagina's met "iets" te vinden. Opgepast: dit zou wel eens lang kunnen duren.

SELECT cur_title
FROM cur
WHERE cur_namespace=0
 AND cur_text REGEXP "[[:<:]]iets[[:>:]]"
ORDER BY cur_title 

[bewerk] Directe links vinden

Om directe links te vinden in een artikel. Dus iets als "http://www.eenofanderdomein.con/iets.gif". De "gif" in de query kan je aanpassen in de gewenste uitgang.

SELECT cur_title
FROM cur
WHERE cur_text LIKE "%http://%.gif%"
 AND cur_namespace=0

[bewerk] Alle pagina's van een naamruimte weergeven

Deze is om een lijst te maken met alle pagina's van de "wikipedia"-naamruimte.

SELECT CONCAT("Wikipedia:",cur_title) AS cur_title
FROM cur
WHERE cur_namespace=4
 AND cur_is_redirect=0
ORDER BY cur_title 

[bewerk] Alle beveiligde pagina's tonen

SELECT cur_namespace, cur_title
FROM cur
WHERE cur_restrictions != ''

[bewerk] Diversen

To make a list of active Wikipedians i would like a query that gives back all registerd users that have made at least X edits in the last X days. giskart 13:06 Feb 2, 2003 (UTC)

Hmm, doing this exactly is kinda tricky, but approximately sure. :) Something like these; you have to separately query the current and old revisions, at least for now:

SELECT cur_user_text, COUNT(*) AS count
FROM cur
WHERE cur_timestamp > '20030127000000' AND cur_user != 0
GROUP BY cur_user
HAVING count > 5
ORDER BY count DESC

SELECT old_user_text, COUNT(*) AS count
FROM old
WHERE old_timestamp > '20030127000000' AND old_user != 0
GROUP BY old_user
HAVING count > 5
ORDER BY count DESC

You'll have to manually construct a timestamp with the cutoff date you want; the above is 27 January 2003 at midnight. Use a smaller count than the 'real' cutoff, as edits will be split between the two tables. --Brion 18:10 Feb 2, 2003 (UTC)

[bewerk] Vervangende opdrachten voor de uitgeschakelde speciale pagina's

• Korte artikels:

SELECT cur_title, 'xxx', LENGTH(cur_text), '!'
FROM cur
WHERE cur_namespace=0
 AND cur_is_redirect=0
ORDER BY LENGTH(cur_text)
LIMIT 200

• Lange artikels:

SELECT cur_title, 'xxx', LENGTH(cur_text), '!'
FROM cur
WHERE cur_namespace=0
 AND cur_is_redirect=0
ORDER BY LENGTH(cur_text) DESC
LIMIT 200

• Meest gewenste artikelen:

SELECT bl_to, 'xxx', COUNT(DISTINCT bl_from) AS nlinks, '!'
FROM brokenlinks
GROUP BY bl_to
HAVING nlinks > 1
ORDER BY nlinks DESC

• Doorverwijspagina's:

SELECT links2.l_from, 'xxx', cur1.cur_title, '!'
FROM cur AS cur1, links AS links1, links AS links2
WHERE links2.L_from <> "Wikipedia:Doorverwijspagina"
 AND links2.l_to=cur1.cur_id
 AND links1.l_from="Wikipedia:Doorverwijspagina"
 AND links1.l_to=cur1.cur_id
ORDER BY links2.l_from
LIMIT 200

(hier moeten helaas de Overleg/Gebruiker/Wikipedia-pagina's nog met de hand tussenuit gezocht worden)

• Dubbele redirects:

SELECT l_from, 'xxx', cb.cur_title, 'yyy', cb.cur_text, '!'
FROM links, cur AS ca, cur AS cb
WHERE ca.cur_is_redirect=1
 AND cb.cur_is_redirect=1
 AND l_to=cb.cur_id
 AND l_from=ca.cur_title
LIMIT 250

• Gebroken redirects:

SELECT cur_namespace, cur_title, '!'
FROM cur, brokenlinks
WHERE bl_from=cur_id
 AND cur_is_redirect=1

• Zelfverwijzingen:

SELECT cur_title, '!'
FROM cur, links
WHERE cur_namespace=0
 AND l_from=cur_title
 AND l_to=cur_id

• Aantal tabellen:

SELECT count(*)
FROM cur
WHERE cur_namespace=0
 AND cur_is_redirect=0
 AND cur_text LIKE '%<Table%' 

Dit is niet identiek aan het aantal echte tabellen, bijvoorbeeld worden er veel tabellen gebruikt om alleen de afbeelding aan de rechterzijde van de pagina te tonen.

• Aantal artikelen met een afbeelding en een tabel (meestal betekent dit een afbeelding in een tabel):

SELECT count(*)
FROM cur
WHERE cur_namespace=0
 AND cur_is_redirect=0
 AND cur_text LIKE '%<table%'
 AND cur_text LIKE '%[afbeelding%'
LIMIT 100

Het aftrekken van bovenstaande twee levert een betere benadering van het aantal echte tabellen.

• Behalve met tabellen maken andere encyclopedien vaak reclame met het aantal afbeeldingen. Voor wikipedia kan dit aantal bepaald worden met

SELECT count(*)
FROM image

[bewerk] Wijzigen status gebruiker

Werkt enkel indien men reeds systeemontwikkelaars status heeft

UPDATE user
SET user_rights='sysop,developer'
WHERE user_id=X;

x is de naam van de doelgebruiker.

[bewerk] Gebruikers met sysop/ontwikkelaarsstatus vinden

SELECT CONCAT("* [[Gebruiker:",user_name,"|",user_name,"]]")
FROM user
WHERE user_rights != ""
ORDER BY user_name
LIMIT 100

[bewerk] Verdachte tekens voor toekomstige upgrades

SELECT cur_namespace, cur_title
FROM cur
WHERE cur_text REGEXP CONCAT('[',char(0x80),'-',char(0x90),char(0x95),'-',char(0x9f),']')
ORDER BY cur_title
LIMIT 100

[bewerk] Nog meer ideeën

Op de Engelse versie van deze pagina kunt u nog meer ideeën vinden, en links naar een overzicht van de diverse database-elementen, zodat u ook zelf een query kunt samenstellen.