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

Ezen az oldalon egy technikai leírást olvashatsz a Wikipédia szerkesztéséről. Tartsd szem előtt, hogy nem minden ajánlott, ami technikailag megvalósítható!


Rövidíté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ázis-kezelő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.

Mielőtt saját adatbázist telepítenél – a Quarry

[szerkesztés]

Ez az útmutató régebben készült. Azóta elérhetővé vált egy webes szolgáltatás, amelynek segítségével saját adatbázis-kezelő telepítése és saját adatbázispéldány letöltögetése helyett az interneten futtathatod a lekérdezéseidet. Azért is érdemes kipróbálni, mert így nem a saját géped erőforrásait fogyasztja a lekérdezés, hanem egy erre felkészített hardverkonfiguráción futtathatod.

A Quarry címe: https://quarry.wmflabs.org/. A bejelentkezéshez az itteni azonosítódat használhatod OAuth hitelesítésen keresztül. A lekérdezéseid mindenki számára láthatóak lesznek.

Az eszköz leírása, egyelőre csak angolul: Research:Quarry. Az adatbázis tábláinak leírását a fejlécben is megtalálod.

Megjegyzések

[szerkesztés]
  • 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 reguláris kifejezés sima szöveg wikilistává alakítására:
s/(\ |\|)//g;s/^/*\[\[/;s/$/\]\]/

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

[szerkesztés]

Előkészületek

[szerkesztés]

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 (2016. december 20.) a MySQL Community Server 5.7.17.
  • Telepítsd a MySQL-t.
  • A MySQL 2011 októberében aktuális 5.7-es verziójához szükséges a .NET keretrendszer 4. verziójának megléte is.
  • Töltsd le és telepítsd a MySQL Workbencht (ez nem kötelező, de hasznos). A Workbench egy grafikus felhasználói felület a MySQL adminsztrálásához és lekérdezések írásához és futtatásához. A szerver címének 'localhost'-ot adj meg, semmi mást nem kell átállítani.
  • Töltsd le az mwdumper programot és nevezd át mwdumper.jar-ra (az útmutató írásakor mwdumper-1.25.jar).
  • 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

[szerkesztés]

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. De elvileg az is elég, ha a tables, revisions és a text táblákat csonkolod (truncate).
  • 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: http://download.wikimedia.org/huwiki/.) 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:
java -jar 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:
java -jar 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 1000 laponként üzenetet kapunk.
Dump beolvasása közvetlenül az adatbázisba (példa, sortörések nélkül, előtte létre kell hozni a táblákat a következő pont szerint):
java -client 
  -classpath mwdumper-1.16.jar;mysql-connector-java-5.1.22\mysql-connector-java-5.1.22-bin.jar;commons-compress-1.4.1.jar 
  org.mediawiki.dumper.Dumper "--output=mysql://127.0.0.1/huwiki?user=root&password=jelszó" 
  --format=sql:1.5 huwiki-latest-pages-articles.xml.bz2
Szükséges modulok:
Lásd még mw:Manual:MWDumper#Direct connection to MySQL.
  • 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
mysql -u root -p huwiki < 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

[szerkesztés]

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

MySQL server has gone away
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
Úgy tűnik, nem elég egy helyen állítani, lásd http://dba.stackexchange.com/questions/45087/max-allowed-packet-in-mysql.
"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

[szerkesztés]
Elavult

Módosítók

[szerkesztés]

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

[szerkesztés]

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

[szerkesztés]

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

[szerkesztés]

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

[szerkesztés]

Dupla átirányítások

[szerkesztés]

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

[szerkesztés]

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

[szerkeszté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

[szerkesztés]

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

[szerkesztés]

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

[szerkesztés]

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

[szerkesztés]
  • 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

[szerkesztés]

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

[szerkesztés]

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

[szerkesztés]
  • SELECT user_name, user_rights from user where user_rights != "" and user_rights != "bot" ORDER BY user_name LIMIT 100

Képek

[szerkesztés]

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)

[szerkesztés]
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)

[szerkesztés]
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

[szerkesztés]
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

[szerkesztés]

Külső hivatkozások

[szerkesztés]