Diskuse k Wikipedii:Chybějící stránky/Z databáze

Získání seznamu chybějících stránek z databáze

Oproti ziskání z textového dumpu má výhodu v tom, že je přesnější: hledá skutečně existující odkazy na chybějící stránky a dovoluje filtrovat podle jmenných prostorů. Našel jsem na disku pracovní poznámky z května 2012, kdy jsem se tím zabýval, tak je sem vkládám, kdyby v tom někdo chtěl pokračovat. Skončil jsem tím, že dokážu vygenerovat alfanumericky setříděný seznam skutečně chybějících stránek podle zadaných kritérií, ještě chybí napsat skript, který ten soubor roztřídí podle počátečních znaků nebo digramů a zaktualizuje na webu.

1. Import

Do databáze naimportovat dumpy cswiki-*-page.sql (seznam existujících stránek) a cswiki-*-pagelinks.sql (tabulka vnitřních odkazů). Import tabulky page trval 19 minut, pagelinks 5 hodin, databáze má velikost 1,5 GB.

2. Zjištění počtu odkazů

Všechny odkazy:

mysql> select count(*) from pagelinks;
+----------+
| count(*) |
+----------+
| 14027814 | 
+----------+
1 row in set (20.83 sec)

Jen odkazy do hlavního jmenného prostoru:

mysql> select count(*) from pagelinks where pl_namespace=0;
+----------+
| count(*) |
+----------+
| 12381448 | 
+----------+
1 row in set (4 min 58.17 sec)

Uníkátní cíle odkazů do hlavního jmenného prostoru:

mysql> select count(*) from pagelinks where pl_namespace=0 group by pl_title;
(...)
1296805 rows in set (14.66 sec)

Jak vyhledat provázání odkazů (zvolil jsem NS 101 /Portál diskuse/ kvůli menšímu počtu odkazů a tedy větší rychlosti odezvy):

mysql> select pagelinks.pl_namespace as ns_to, pagelinks.pl_title as title_to, \
page.page_namespace as ns_from, page.page_title as title_from \
from pagelinks left join page on pl_from=page_id where pl_namespace=101;
+-------+-------------------------------------------------------------------+---------+---------------------------------------------------------------+
| ns_to | title_to                                                          | ns_from | title_from                                                    |
+-------+-------------------------------------------------------------------+---------+---------------------------------------------------------------+
|   101 | Aktuality                                                         |      11 | Nejnovější_aktuality                                          | 
|   101 | Aktuality                                                         |       2 | PastoriBot/log/3                                              | 
(...)
234 rows in set (0.79 sec)

(ve skutečnosti trvalo déle)

3. Filtrování podle zdroje odkazů

Z pagelinks nechat jen ty odkazy, které vedou do hlavního jmenného prostoru a současně stránka s tímto názvem neexistuje v page s číslem NS 0.

Následující příklad hledá odkazy vedoucí z NS 101, aby výsledek byl rychlejší:

mysql> select distinct pl_namespace, pl_title from pagelinks where pl_namespace=101 \
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title);
+--------------+--------------------------------------+
| pl_namespace | pl_title                             |
+--------------+--------------------------------------+
|          101 | Dánsko                               | 
|          101 | Gruzie                               | 
|          101 | Hnutí_pro-life                       | 
|          101 | Hnutí_pro-life/Další_obrázky         | 
|          101 | Hnutí_pro-life/Další_zajímavosti     | 
|          101 | Hnutí_pro-life/Historie              | 
|          101 | Houby/Požadované_články              | 
|          101 | Izrael/Připravované                  | 
|          101 | Kosovo                               | 
|          101 | Kosovo/Šablony                       | 
|          101 | Literatura/Informace                 | 
|          101 | Mafia                                | 
|          101 | Most                                 | 
|          101 | Novověk/Připravované                 | 
|          101 | Plzeň/Obrázek                        | 
|          101 | Portál:Biografie                     | 
|          101 | Programovací_jazyky                  | 
|          101 | Stargate                             | 
|          101 | Starověký_Řím                        | 
|          101 | Včelařství/Informace                 | 
|          101 | Číslicová_technika                   | 
+--------------+--------------------------------------+
21 rows in set (0.06 sec)

mysql> select pl_namespace as ns_to, pl_title as title_to, page_namespace as ns_from, page_title as title_from \
from pagelinks left join page on (pl_from=page_id) where pl_namespace=101 \
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title);
+-------+--------------------------------------+---------+-------------------------------------------------------+
| ns_to | title_to                             | ns_from | title_from                                            |
+-------+--------------------------------------+---------+-------------------------------------------------------+
|   101 | Dánsko                               |       2 | RgC/Pískoviště_1/Pomoc                                | 
|   101 | Gruzie                               |     100 | Gruzie/Informace                                      | 
|   101 | Gruzie                               |     100 | Gruzie                                                | 
|   101 | Hnutí_pro-life                       |       4 | Pod_lípou/Archiv_2010/02                              | 
|   101 | Hnutí_pro-life/Další_obrázky         |       2 | DeeMusil/Portál:Hnutí_pro-life/Další_obrázky          | 
|   101 | Hnutí_pro-life/Další_zajímavosti     |       2 | DeeMusil/Portál:Hnutí_pro-life/Další_zajímavosti      | 
|   101 | Hnutí_pro-life/Historie              |       2 | DeeMusil/Portál:Hnutí_pro-life/Historie               | 
|   101 | Houby/Požadované_články              |     100 | Houby/Požadované_články                               | 
|   101 | Izrael/Připravované                  |       2 | Faigl.ladislav/diskuse-archiv5                        | 
|   101 | Kosovo                               |     100 | Kosovo                                                | 
|   101 | Kosovo/Šablony                       |     100 | Kosovo/Šablony                                        | 
|   101 | Literatura/Informace                 |     100 | Literatura/Informace                                  | 
|   101 | Mafia                                |       3 | Dominik_Žilka                                         | 
|   101 | Most                                 |     100 | Most                                                  | 
|   101 | Novověk/Připravované                 |     100 | Novověk/Připravované                                  | 
|   101 | Plzeň/Obrázek                        |     100 | Plzeň/Obrázek                                         | 
|   101 | Portál:Biografie                     |     100 | Portál:Biografie                                      | 
|   101 | Programovací_jazyky                  |       3 | Jakub0304                                             | 
|   101 | Stargate                             |       3 | BobM/archiv04                                         | 
|   101 | Stargate                             |       2 | Zipacna1/Archiv_diskuze_2008-2009                     | 
|   101 | Starověký_Řím                        |     100 | Starověký_Řím                                         | 
|   101 | Starověký_Řím                        |     100 | Starověký_Řím/Záhlaví                                 | 
|   101 | Včelařství/Informace                 |     100 | Včelařství/Informace                                  | 
|   101 | Číslicová_technika                   |       4 | Diskuse_o_smazání/Portál:Číslicová_technika           | 
+-------+--------------------------------------+---------+-------------------------------------------------------+
24 rows in set (0.01 sec)

Všechny odkazy na chybějící stránky vedoucí z NS 0:

mysql> select pl_namespace as ns_to, pl_title as title_to, page_namespace as ns_from, page_title as title_from \
from pagelinks left join page on (pl_from=page_id) where pl_namespace=0 and page_namespace=0 \
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title);
(...)
2143556 rows in set (12 min 23.40 sec)

Seznam unikátních chybějících stránek odkazovaných z NS 0:

mysql> select pl_namespace as ns_to, pl_title as title_to from pagelinks left join page on (pl_from=page_id) \
where pl_namespace=0 and page_namespace in (0) \
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title) group by title_to;
(...)
934521 rows in set (25 min 42.35 sec)

Ve více jmenných prostorech (uvažované 0 (hlavní), 2 (wikipedista), 14 (kategorie), 100 (portál) a vybrané stránky ze 4 (Wikipedie), např. požadované články):

mysql> select pl_namespace as ns_to, pl_title as title_to, page_namespace as ns_from, page_title as title_from \
from pagelinks left join page on (pl_from=page_id) where pl_namespace=0 \
and (page_namespace in (0, 2, 14, 100) or (page_namespace=4 and page_title like 'Požadované%')) \
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title) order by title_to;

mysql> select pl_title as title_to, page_namespace as ns_from, page_title as title_from from pagelinks \
left join page on (pl_from=page_id) where pl_namespace=0 and (page_namespace in (0, 2, 14, 100) \
or (page_namespace=4 and (page_title like 'Požadované%' or page_title like 'Cizojazyčné%' or page_title like 'WikiProjekt%'))) \
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title) order by title_to;
2227007 rows in set (25 min 2.70 sec)

(potřebovalo cca 1,1 GB na dočasnou tabulku a další cca 3 GB na další odkládací prostor!)

Seznam unikátních chybějících stránek – tohle potřebujeme

mysql> select pl_title as title_to from pagelinks left join page on (pl_from=page_id) \
where pl_namespace=0 and (page_namespace in (0, 2, 14, 100) \
or (page_namespace=4 and (page_title like 'Požadované%' or page_title like 'Cizojazyčné%' or page_title like 'WikiProjekt%'))) \
and not exists (select * from page where page_namespace=pl_namespace and page_title=pl_title) group by title_to;
962235 rows in set (43 min 49.87 sec)

(potřebovalo cca 236 MB na dočasnou tabulku a další cca 1 GB odkládacího prostoru)