WSoR datasets/wp claimed pages

This table contains a list of pages claimed by each WikiProject and the template(s) used to claim them.

Location

db1048:shawn.wp_claimed_pages

Fields

	shawn@internproxy:~$ mysql -h db1048 -e "EXPLAIN wp_claimed_pages;SELECT * FROM wp_claimed_pages LIMIT 3" shawn
	+--------------------+----------------+------+-----+---------+-------+
	| Field              | Type           | Null | Key | Default | Extra |
	+--------------------+----------------+------+-----+---------+-------+
	| claimed_page_title | varbinary(255) | YES  |     | NULL    |       |
	| claimed_page_id    | int(11)        | YES  | MUL | NULL    |       |
	| wikiproject        | varbinary(255) | YES  | MUL | NULL    |       |
	| template           | varbinary(255) | YES  |     | NULL    |       |
	+--------------------+----------------+------+-----+---------+-------+
	+--------------------------------+-----------------+------------------+------------------+
	| claimed_page_title             | claimed_page_id | wikiproject      | template         |
	+--------------------------------+-----------------+------------------+------------------+
	| Common_Language_Infrastructure |            7238 | WikiProject_.NET | WikiProject_.NET |
	| C_Sharp_(programming_language) |          162923 | WikiProject_.NET | WikiProject_.NET |
	| .NET_Framework                 |          174740 | WikiProject_.NET | WikiProject_.NET |
	+--------------------------------+-----------------+------------------+------------------+

Each row represents a a page claimed by a WikiProject.

• claimed_page_id: id of the claimed page
• claimed_page_title: page title
• wikiproject: name of the WikiProject claiming the page
• template: template used to claim the page

Reproduction

Create Table:

	CREATE TABLE `wp_claimed_pages` (
	  `claimed_page_title` varbinary(255) DEFAULT NULL,
	  `claimed_page_id` int(11) DEFAULT NULL,
	  `wikiproject` varbinary(255) DEFAULT NULL,
	  `template` varbinary(255) DEFAULT NULL,
	  KEY `wikiproject` (`wikiproject`),
	  KEY `claimed_page_id` (`claimed_page_id`),
	  KEY `claimed_page_id_2` (`claimed_page_id`)
	)

Insert article talk pages (ns1):

SELECT talk_page.page_title as claimed_page_title, talk_page.page_id as claimed_page_id, wp_templates.pl_title as wikiproject, wp_templates.page_title as template
     FROM
          shawn.wp_template_links wp_templates
     INNER JOIN
          enwiki.templatelinks transclusions
           ON
               wp_templates.page_title = transclusions.tl_title
                AND transclusions.tl_namespace = 10
     INNER JOIN
          enwiki.page talk_page
           ON
               talk_page.page_id = transclusions.tl_from
               AND talk_page.page_namespace = 1
     INNER JOIN
          enwiki.pagelinks tp_links
           ON tp_links.pl_from = talk_page.page_id
           AND tp_links.pl_namespace = 10
           AND tp_links.pl_title = wp_templates.pl_title

Insert article pages (ns0). You must insert the article talk pages first!

INSERT INTO wp_claimed_pages
SELECT cp.claimed_page_title,
       p.page_id,
       cp.wikiproject,
       cp.template
FROM   `wp_claimed_pages` cp
       JOIN enwiki.page p
         ON cp.`claimed_page_title` = p.page_title
            AND p.page_namespace = 0;

Notes

This table was generated on 2011-08-18 18:43:51. It depends on wp_template_links.