Wikipédia:Adatbázis-lekérdezések

A Wikipédia szócikkek szövegei, a laptörténetek, a naplók és minden más adat (a képeket és egyéb fájlokat kivéve) a Wikimédia Alapítvány központi adatbázisszerverén tárolódik. Erről az adatbázisról (az érzékeny adatok, mint pl. felhasználói beállítások vagy nem nyilvános naplók kivételével) néhány hetente másolatok készülnek, amelyek a download.wikipedia.org címről letölthetőek, és egy adatbáziskezelőbe betöltve szinte bármilyen lista, statisztika vagy egyéb információ kinyerhető belőlük. Ez a lap ehhez nyújt útmutatást. Ha nem tudsz egyedül megfogalmazni vagy futtatni egy lekérdezést, a Wikipédia:SQL-futtatási kérések oldalon kérhetsz segítséget.

Megjegyzések

• Ha a vágólap segítségével másolsz ki egy lekérdezést erről az oldalról, bizonyos böngészők az elejére egy * jelet tesznek. Ezt ki kell törölni! (A lekérdezésnek a SELECT szóval kell kezdődnie.)
• A legtöbb lenti lekérdezés szócikkek címeit adja vissza. Ezek nem tartalmazzák a névteret! A névtér az adatbázisban egy numerikus kódként szerepel, a page_namespace mezőben. A kódokat itt találod meg.
• Windows alatt a Mysql által visszaadott listát a legegyszerűbben úgy tudod sima szöveggé alakítani, hogy elmented (exportálod) az eredményt Excel fájlba, betöltöd Excel alá, és ott elmented Unicode text formátumban.
• Egy szabályos kifejezés a sima szöveg wikilistává alakítására:

s/(\ |\|)//g;s/^/*\[\[/;s/$/\]\]/

Gyorstalpaló lekérdezések futtatásához MySQL-en Windows alatt

Előkészületek

Az előkészületek körülbelül egy órát vesznek igénybe szélessávú kapcsolattal. Ezeket csak egyszer, a legelső alkalommal kell elvégezni:

• Töltsd le a MySQL-t is a mysql.com-ról. Az ajánlott változat jelenleg (2008. december 29.) a MySQL Community Server 5.1 Essentials csomagja.
• Telepítsd a MySQL-t.
• A MySQL 2011 októberében aktuális 5.5-ös verziójához szükséges a .NET keretrendszer 4. verziójának megléte is.
• Töltsd le és telepítsd a MySQL Query Browsert (ez nem kötelező, de hasznos). A Query Browser egy grafikus felhasználói felület a MySQL lekérdezésekhez. A szerver címének 'localhost'-ot adj meg, semmi mást nem kell átállítani.
• Töltsd le az mwdumper programot.
• Ha nincs JRE a gépeden, telepítsd fel innen. (Általában van; itt ellenőrizheted. Ha van JRE, egy táncoló figurát fogsz látni néhány sorral a Test your JVM felirat alatt. A reklámszűrők, mint az Adblock, megzavarhatják a tesztet, azokat kapcsold ki előtte!)

Az adatbázis frissítése

A továbbiakat minden alkalommal meg kell ismételni, amikor frissíteni akarod az adatbázist:

• Hozd létre a huwiki adatbázist a következő paranccsal (például a MySQL parancssorából, vagy, ha telepítetted, a MySQL Query Browserből):

CREATE DATABASE huwiki;

(Ha már létezik ilyen néven adatbázis, töröld ki előbb, vagy válassz más nevet.)

• Töltsd le azt az adatbázis dumpot a download.wikimedia.org-ról, amin az SQL lekérdezéseket futtatni akarod. (Ez általában a legfrissebb pages-articles fájl, ha csak a lapok aktuális állapotaira vagy kíváncsi; és a legfrissebb pages-meta-history, ha a korábbiakra is. Bővebb információért válaszd ki a legfrissebb dátumot itt.) Vedd a figyelőlistádra {{a dump dátuma}} sablont, ha mindig értesülni akarsz a legújabb változat megjelenéséről.
• Tömörítsd ki a letöltött adatbázist vagy adatbázisokat. A 7z kiterjesztésűekhez a 7-zip tömörítőre is szükséged lesz; a gz-t a legtöbb modern tömörítő ismeri. A bz2 kiterjesztésű fájlokat nem szükséges kitömörítened; ha valamiért mégis szeretnéd, használd a bzip tömörítőt. A gz és bz2 fájlokat a WinZip programmal vagy (a megfelelő pluginek birtokában) Total Commanderrel is kitömörítheted. VIGYÁZAT: a kitömörített fájl sokkal nagyobb lesz, mint az eredeti. Az aktuális verzióhoz ezerötszáz megabájt, a teljes laptörténethez több tíz gigabájt hely kell! Az adatbázisba importáláshoz pedig még egyszer annyi helyre lesz szükséged a MySQL által használt meghajtón.)
• Ha a kitömörített fájl nem sql, hanem xml kiterjesztésű, konvertáld sql-lé az alábbi paranccsal:

mwdumper.jar fájlnév.xml --output=file:fájlnév.sql --format=sql:1.5

Ahol fájlnév a fájl neve (pl. pages-meta-history). Ha a fájl bzip tömörítésű, kitömörítés nélkül is konvertálhatod:

mwdumper.jar fájlnév.xml.bz2 --output=file:fájlnév.sql --format=sql:1.5

A konvertálás a fájl méretétől és a géptől függően hosszú ideig tarthat, miközben a program működéséről visszajelzést nem kapunk.

Elvileg egyenesen az adatbázisba is lehet konvertálni az mwdumper.jar fájlnév.xml[.bz2] --output="mysql://localhost/huwiki?user=root&password=jelszó" --format=sql:1.5 paranccsal. Gyakorlatilag ha sikerül, írd meg, hogy csináltad :)

• Válaszd ki a huwiki adatbázist (use huwiki; vagy duplakattintás a Query Browserben), és töltsd le és futtasd le a tables.sql fájlt (Query Browser File menüpontjában a Load Script paranccsal + Execute gombbal, vagy a "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -uroot -pjelszó -Dhuwiki --default-character-set=utf8 < tables.sql parancssori utasítással).
• Állítsd vissza az adatbázist a letöltött SQL fájlból. Ugyanúgy kell, mint az előző pontban, csak tables.sql helyett az mwdumper által készített sql fájl nevét add meg.
• Várd meg, amíg a MySQL végrehajtja az adatbázis visszaállítását (ez jó néhány percbe beletelhet).
• Ha a MySQL elkészült a visszaállítással, kezdheted futtatni a lekérdezéseket. Kezdetnek próbálkozz meg néhány példával erről az oldalról. Egyszerűen csak gépeld be őket soronként, a sorok végén entert ütve. A legutolsó sort pontosvesszővel zárd.

Problémák

Ha bármi problémád vagy kérdésed van az itt leírtakkal, jelezd a vitalapon.

valami csomagmérettel (packet size) kapcsolatos hibát ad

nyisd meg szerkesztésre a MySQL könyvtárában a my.ini fájlt, keresd meg a [mysqld] szöveget, és írd be alá ezt: max_allowed_packet = 16M

"duplicate entry valami for key" hibát ad, ahol valami egy szócikk neve (előtte esetleg egy szám és egy kötőjel), az ékezetes betű helyén kérdőjellel

karakterkódolási probléma. Ha rájössz, hogy kell megoldani, feltétlenül írd ide :) (valami valahol nem UTF-8-ra van állítva)

Innentől lefelé elavult

---

A közösségi lap egy része még lefordítandó. Segíts te is a fordításban!

---

Módosítók

To limit results:

"SELECT ... LIMIT 20".

You can also add an offset;

"SELECT ... LIMIT 100, 20" will give you 20 records starting at the 101st.

Lekérdezések új szerkesztők megtalálásához

Here's a couple of queries to find the 20 most newly created users - useful to find people to give welcome messages. It only counts users that have made edits (most users who create a login never make a single contribution and are just passing by, so it's not really worth welcoming them).

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

The above query gives the user names of the 20 most recently created users and the number of edits they have made. The query is not perfect - only edits that have not been 'overwritten' by other edits are counted here. To do the same thing looking only at 'overwritten' edits use:

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

Lekérdezések csonkok megtalálásához

For short pages containing "see" (takes about 2 seconds):

• SELECT cur_title FROM cur WHERE cur_is_redirect=0 AND LENGTH(cur_text)<50 AND cur_text LIKE '%see%'

finds all pages with less than 50 chars.

Lekérdezések egyetlen változattal rendelkező cikkek megtalálásához

SELECT cur_title FROM cur WHERE cur_is_new AND cur_namespace=0;

Finds all pages that have only been edited once.

Lekérdezések hibás hivatkozások kiszűréséhez

Dupla átirányítások

Article namespace:

• SELECT ca.cur_namespace, ca.cur_title FROM cur ca, cur cb, links l WHERE ca.cur_is_redirect=1 AND cb.cur_is_redirect=1 AND l.l_from=ca.cur_title AND l.l_to=cb.cur_id AND ca.cur_namespace=0 LIMIT 250

Talk namespace:

• SELECT ca.cur_namespace, ca.cur_title, FROM cur ca, cur cb, links l WHERE ca.cur_is_redirect=1 AND cb.cur_is_redirect=1 AND l.l_from=CONCAT("Talk:",ca.cur_title) AND l.l_to=cb.cur_id AND ca.cur_namespace=1 LIMIT 250

These queries are slow. You may have to reduce the limit for them to work.

Egyéb

List all articles that link to a file on http://meta.wikipedia.com (takes 2-3 seconds):

• SELECT cur_title FROM cur WHERE cur_text LIKE "%http://meta.wikipedia.com/upload/%" AND cur_namespace=0

To find articles that link to jpegs, both external and internal, with the old non-[[Image:]] style (this isn't perfect, it also finds a few other pages):

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

For articles containing a link to itself (!!24 seconds!!)

• 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 LIMIT 20

For redirects to non-existing pages (note: when a page is found that is redirecting correctly, it usually means that there is text going with the redirect, 2-4 seconds):

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

Lekérdezés olyan szócikkek kiszűréséhez, amelyek első mondatában nincs kiemelés

Which means they don't have bold titles in the first sentence!

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>%" LIMIT 50

Lekérdezés olyan szócikkek kiszűréséhez, amelyek első mondatában nincs kiemelés, és nem egyértelműsítő lapok

Same as above only removes disambiguation pages which don't need bolds. 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 "%disambig%" LIMIT 50

Lekérdezés allapok megtalálásához

This finds all articles containing a "/" character; most of these are subpages.

• SELECT cur_title FROM cur WHERE cur_namespace=0 and cur_title like "%/%" and cur_is_redirect=0

Lekérdezések statisztikákhoz

For a count of how many articles are more than 1500 characters long, use:

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

Lekérdezések blokkolt felhasználókról

• SELECT DISTINCT ipb_address, COUNT(*) AS times, MIN(ipb_reason) AS reason, user_name AS blocker, DATE_FORMAT(MIN(ipb_timestamp), '%b %e %Y %k:%i') AS date FROM ipblocks, user WHERE user_id=ipb_by GROUP BY ipb_address ORDER BY ipb_timestamp

returns the blocked IP addresses, and the first entry for the blocking (some IP addresses have been blocked by multiple people), ordered by date.

This is mostly unnecessary (but may be of interest as an example query), as the Special:Ipblocklist returns similar information.

Monty Python Repülő Cirkusza

Find all pages with "it's" for spellchecking purposes. WARNING: takes a long time. May want to apply a limit (see above)

• SELECT cur_title from cur where cur_namespace=0 and cur_text regexp "[[:<:]]it's[[:>:]]"

Vandalizmus

When a certain IP-number or user has been found to be vandalising, it might be that some of their problems may already have been solved by someone else and others not. The following query shows those pages which the vandal (or otherwise problematic user) was the last to edit:

• SELECT cur_title FROM cur WHERE cur_user_text="name" LIMIT 100

If the vandal is a registered user, use their Username for name, if not, use their IP-number.

Lekérdezés fejlesztők, adminisztrátorok megtalálásához

• SELECT user_name, user_rights from user where user_rights != "" and user_rights != "bot" ORDER BY user_name LIMIT 100

Képek

The queries here produce valid links when the results are copied and pasted into a page. Not very pretty, but functional. With 70,000 images in en, these take about 60 seconds to run the first time.

Egy szerkesztő összes képe (beleértve a régi változatokat is)

select distinct concat('<br>[[:Image:', img_name, ']]') as name
from image where img_user_text='USER_NAME_HERE'
union
select distinct concat('<br>[[:Image:', oi_name , ']]') as name
from oldimage where oi_user_text='USER_NAME_HERE'
order by name asc;

Egy szerkesztő összes képe (csak az aktuális változatok)

select distinct concat('<br>[[:Image:', img_name, ']]') as name
from image where img_user_text='USER_NAME_HERE'
order by name asc;

Minden képleíró lap, amit egy adott szerkesztő szerkesztett

select distinct concat('<br>[[:Image:', cur_title, ']]') as title
from cur where cur_namespace=6 and cur_user_text='USER_NAME_HERE'
union
select distinct concat('<br>[[:Image:', old_title , ']]') as title
from old where old_namespace=6 and old_user_text='USER_NAME_HERE'
order by title asc;

Lásd még

• Wikipédia:Wikipédia-tükör telepítése
• Wikipédia:SQL-futtatási kérések
• Wikipédia:SQL-lekérdezések (gyakran használt lekérdezések másolható formában)

Külső hivatkozások

• m:Data dumps
• Az adatbázisséma részletes leírása
• en:Database layout