Manual:page テーブル
| ↑ Manual:コンテンツ | MediaWiki のデータベース レイアウト | page table/ja |
Contents
| MediaWiki バージョン: | ≧ 1.5 |
page テーブル は「ウィキの中核」とみなすことができます。 このテーブルには、ある一つのMediaWiki内にある各ページに対応したエントリーがあります。エントリーはタイトルによって識別され、そのほかにもいくつかの不可欠なメタデータも含まれています。 それは MediaWiki 1.5 のリビジョン r6710 で初めて導入されました。
ページの内容そのものは text テーブルに保存されます。 記事の内容を取り出す際、MediaWikiはpageテーブルの page_title をまず検索します。 そして revision テーブルの rev_id から page_latest と一致する値を検索し、rev_text_id を得ます。 得られた rev_text_id を text テーブルの old_id から検索し、ページ内容を取り出します。 ページが削除されると、各リビジョンは archive テーブルに移動されます。
フィールド
page_id
Uniquely identifying primary key. This value is preserved across edits and renames.
Page IDs do not change when pages are moved, but they do change when pages are deleted and then restored. As of MediaWiki 1.27, this value is still 'preserved' across deletions via an analogous field in the archive table (introduced in MediaWiki 1.11).
MediaWiki offers a number of relevant tools:
- The page ID of any page (except special pages) can be looked up in the "page information" link from the Tools menu.
- The magic word
{{PAGEID}}can be used to return the page id of a page. - The special page Special:Redirect can be used to access pages via their page IDs. For example, Special:Redirect/page/24280, redirects to the present page.
- index.php accepts the parameter
curidto access pages via their page IDs. For example, /w/index.php?curid=24280 will load the present page.
There are also many API features that either return the page ID or use it as an input.
For example, for the present page, page_id = 10501, see http://www.mediawiki.org/w/api.php?action=query&prop=info&titles=Manual:Page%20table and http://www.mediawiki.org/w/api.php?action=query&prop=info&pageids=10501.
This field can be accessed by WikiPage::getId(), Title::getArticleID(), etc.
page_namespace
A page name is broken into a namespace and a title. The namespace keys are UI-language-independent constants, defined in includes/Defines.php.
This field contains the number of the page's namespace. The values range from 0 to 15 for the standard namespaces, and from 100 to 2147483647 for custom namespaces.
page_title
The sanitized page title, without the namespace, with a maximum of 255 characters (binary). It is stored as text, with spaces replaced by underscores. The real title shown in articles is just this title with underscores (_) converted to spaces ( ). For example, a page titled "Talk:Foo Bar" would have "Foo_Bar" in this field.
page_restrictions
| MediaWiki バージョン: | ≦ 1.9 |
Comma-separated set of permission keys indicating who can move or edit the page. Edit and move sections are separated by a colon (e.g., "edit=autoconfirmed,sysop:move=sysop").
page_counter
| MediaWiki バージョン: | ≦ 1.24 |
Number of times the page has been viewed. This feature was completely removed in MediaWiki 1.25, following a request for comment. Even before that, many sites including Wikimedia projects disabled it to increase performance; see Manual:$wgDisableCounters for details.
page_is_redirect
A value of 1 here indicates the article is a redirect; it is 0 in all other cases.
page_is_new
This field stores whether the page is a new, meaning it either has only one revision or has not been edited since being restored, even if there is more than one revision.
If the field contains a value of 1, then it indicates that the page is a new; otherwise, it is 0.
Rollback links are not displayed if the page is new, since there is nothing to roll back to.
page_random
Random decimal value, between 0 and 1, used for Special:Random (see Manual:おまかせ表示 for more details). wfRandom() によって生成されます。
Around 2005, a bug caused these random values to be non-uniform. Since the field is set at page creation, wikis that have existed for a long time might still have some of these erroneous values. See T208909.
page_touched
This timestamp is updated whenever the page changes in a way requiring it to be re-rendered, invalidating caches. Aside from editing, this includes permission changes, creation or deletion of linked pages, and alteration of contained templates. Set to $dbw->timestamp() at the time of page creation.
page_links_updated
| MediaWiki バージョン: | ≧ 1.23 |
This timestamp is updated whenever a page is re-parsed and it has all the link tracking tables updated for it. This is useful for de-duplicating expensive backlink update jobs. Set to the default value of NULL when the page is created by WikiPage::insertOn().
page_latest
This is a foreign key to rev_id for the current revision.
It may be 0 during page creation.
It needs to link to a revision with a valid revision.rev_page, or there will be the "The revision #0 of the page named 'Foo' does not exist" error when one tries to view the page.
Can be obtained via WikiPage::getLatest().
page_len
Uncompressed length in bytes of the page's current source text.
This however, does not apply to images which still have records in this table.
Instead, the uncompressed length in bytes of the description for the file is used as the latter is in the text.old_text field.
The Wikipage class in includes/WikiPage.php has two methods, viz., insertOn() and updateRevisionOn() that are responsible for populating these details.
page_content_model
| MediaWiki バージョン: | ≧ 1.21 |
Content model, see CONTENT_MODEL_XXX constants. Comparable to revision.rev_content_model.
page_lang
| MediaWiki バージョン: | ≧ 1.24 |
ページ本文の言語です。 Set to the default value of NULL at the time of page creation.
Schema summary
| MediaWiki バージョン: | ≧ 1.25 |
DESCRIBE page;
+--------------------+---------------------+------+-----+----------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+----------------+----------------+ | page_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | page_namespace | int(11) | NO | MUL | NULL | | | page_title | varbinary(255) | NO | | NULL | | | page_restrictions | tinyblob | NO | | NULL | | | page_is_redirect | tinyint(3) unsigned | NO | MUL | 0 | | | page_is_new | tinyint(3) unsigned | NO | | 0 | | | page_random | double unsigned | NO | MUL | NULL | | | page_touched | binary(14) | NO | | | | | page_links_updated | varbinary(14) | YES | | NULL | | | page_latest | int(10) unsigned | NO | | NULL | | | page_len | int(10) unsigned | NO | MUL | NULL | | | page_content_model | varbinary(32) | YES | | NULL | | | page_lang | varbinary(35) | YES | | NULL | | +--------------------+---------------------+------+-----+----------------+----------------+
| MediaWiki バージョン: | 1.24 |
DESCRIBE page;
+--------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+---------+----------------+ | page_id | int(10) unsigned | NO | PRI | NULL | AUTO_INCREMENT | | page_namespace | int(11) | NO | MUL | NULL | | | page_title | varchar(255) binary | NO | | NULL | | | page_restrictions | tinyblob | NO | | NULL | | | page_counter | bigint(20) unsigned | NO | | 0 | | | page_is_redirect | tinyint(3) unsigned | NO | MUL | 0 | | | page_is_new | tinyint(3) unsigned | NO | | 0 | | | page_random | real unsigned | NO | MUL | NULL | | | page_touched | binary(14) | NO | | NULL | | | page_links_updated | varbinary(14) | YES | | NULL | | | page_latest | int(10) unsigned | NO | | NULL | | | page_len | int(10) unsigned | NO | MUL | NULL | | | page_content_model | varbinary(32) | YES | | NULL | | | page_lang | varbinary(35) | YES | | NULL | | +--------------------+---------------------+------+-----+---------+----------------+
| MediaWiki バージョン: | 1.23 |
DESCRIBE page;
+--------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+---------+----------------+ | page_id | int(10) unsigned | NO | PRI | NULL | AUTO_INCREMENT | | page_namespace | int(11) | NO | MUL | NULL | | | page_title | varchar(255) binary | NO | | NULL | | | page_restrictions | tinyblob | NO | | NULL | | | page_counter | bigint(20) unsigned | NO | | 0 | | | page_is_redirect | tinyint(3) unsigned | NO | MUL | 0 | | | page_is_new | tinyint(3) unsigned | NO | | 0 | | | page_random | real unsigned | NO | MUL | NULL | | | page_touched | binary(14) | NO | | NULL | | | page_links_updated | varbinary(14) | YES | | NULL | | | page_latest | int(10) unsigned | NO | | NULL | | | page_len | int(10) unsigned | NO | MUL | NULL | | | page_content_model | varbinary(32) | YES | | NULL | | +--------------------+---------------------+------+-----+---------+----------------+
| MediaWiki バージョン: | 1.21 – 1.22 |
DESCRIBE page;
+--------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+---------+----------------+ | page_id | int(10) unsigned | NO | PRI | NULL | AUTO_INCREMENT | | page_namespace | int(11) | NO | MUL | NULL | | | page_title | varchar(255) binary | NO | | NULL | | | page_restrictions | tinyblob | NO | | NULL | | | page_counter | bigint(20) unsigned | NO | | 0 | | | page_is_redirect | tinyint(3) unsigned | NO | MUL | 0 | | | page_is_new | tinyint(3) unsigned | NO | | 0 | | | page_random | real unsigned | NO | MUL | NULL | | | page_touched | binary(14) | NO | | NULL | | | page_latest | int(10) unsigned | NO | | NULL | | | page_len | int(10) unsigned | NO | MUL | NULL | | | page_content_model | varbinary(32) | YES | | NULL | | +--------------------+---------------------+------+-----+---------+----------------+
| MediaWiki バージョン: | 1.19 – 1.20 |
DESCRIBE page;
+-------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+----------------+ | page_id | int(10) unsigned | NO | PRI | NULL | AUTO_INCREMENT | | page_namespace | int(11) | NO | MUL | NULL | | | page_title | varchar(255) binary | NO | | NULL | | | page_restrictions | tinyblob | NO | | NULL | | | page_counter | bigint(20) unsigned | NO | | 0 | | | page_is_redirect | tinyint(3) unsigned | NO | MUL | 0 | | | page_is_new | tinyint(3) unsigned | NO | | 0 | | | page_random | real unsigned | NO | MUL | NULL | | | page_touched | binary(14) | NO | | NULL | | | page_latest | int(10) unsigned | NO | | NULL | | | page_len | int(10) unsigned | NO | MUL | NULL | | +-------------------+---------------------+------+-----+---------+----------------+
| MediaWiki バージョン: | 1.10 – 1.18 |
DESCRIBE page;
+-------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+----------------+ | page_id | int(10) unsigned | NO | PRI | NULL | AUTO_INCREMENT | | page_namespace | int(11) | NO | MUL | NULL | | | page_title | varchar(255) binary | NO | | NULL | | | page_restrictions | tinyblob | NO | | NULL | | | page_counter | bigint(20) unsigned | NO | | 0 | | | page_is_redirect | tinyint(3) unsigned | NO | | 0 | | | page_is_new | tinyint(3) unsigned | NO | | 0 | | | page_random | real unsigned | NO | MUL | NULL | | | page_touched | binary(14) | NO | | NULL | | | page_latest | int(10) unsigned | NO | | NULL | | | page_len | int(10) unsigned | NO | MUL | NULL | | +-------------------+---------------------+------+-----+---------+----------------+
| MediaWiki バージョン: | 1.5 – 1.9 |
DESCRIBE page;
+-------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+----------------+ | page_id | int(8) unsigned | NO | PRI | NULL | AUTO_INCREMENT | | page_namespace | int(11) | NO | MUL | NULL | | | page_title | varchar(255) binary | NO | | NULL | | | page_restrictions | tinyblob | NO | | NULL | | | page_counter | bigint(20) unsigned | NO | | 0 | | | page_is_redirect | tinyint(1) unsigned | NO | | 0 | | | page_is_new | tinyint(1) unsigned | NO | | 0 | | | page_random | real unsigned | NO | MUL | NULL | | | page_touched | char(14) binary | NO | | NULL | | | page_latest | int(8) unsigned | NO | | NULL | | | page_len | int(8) unsigned | NO | MUL | NULL | | +-------------------+---------------------+------+-----+---------+----------------+
インデックス
| MediaWiki バージョン: | ≧ 1.28 |
SHOW INDEX IN page;
+-------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | page | 0 | PRIMARY | 1 | page_id | A | 0 | NULL | NULL | | BTREE | | | | page | 0 | name_title | 1 | page_namespace | A | 0 | NULL | NULL | | BTREE | | | | page | 0 | name_title | 2 | page_title | A | 0 | NULL | NULL | | BTREE | | | | page | 1 | page_random | 1 | page_random | A | 0 | NULL | NULL | | BTREE | | | | page | 1 | page_len | 1 | page_len | A | 0 | NULL | NULL | | BTREE | | | | page | 1 | page_redirect_namespace_len | 1 | page_is_redirect | A | 0 | NULL | NULL | | BTREE | | | | page | 1 | page_redirect_namespace_len | 2 | page_namespace | A | 0 | NULL | NULL | | BTREE | | | | page | 1 | page_redirect_namespace_len | 3 | page_len | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Sample MySQLコードの例
一覧ページとその他の必須テーブルとの関係
以下のコードにより、中心となるテーブル（page, revision, text）から全記事の最新版を選択できます。
SELECT
p.page_id AS "page_id",
CAST(p.page_title AS CHAR(10000) CHARACTER SET utf8) AS "page_title",
r.rev_text_id AS "revision_id",
t.old_id AS "text_id"
FROM
page p
INNER JOIN revision r
ON p.page_latest = r.rev_id
INNER JOIN text t
ON r.rev_text_id = t.old_id
Other important considerations:
- to find undeleted pages add "r.rev_deleted = 0"
- to find pages in namespace 0 add "p.page_namespace = 0"
- to find pages that are not redirects add "p.page_is_redirect = 0"
These additional statements can be added either as conditions to a Where statement or as conditions on the appropriate Inner Join statement.
Deleting pages with their relationships in text and revision tables
The following shows the steps to run to help you delete pages and their children from the main pages in the page, revision and text tables.
Listing all related table keys
--
-- Listing page titles and related relationships to other tables (revision, text, page)
--
SELECT
`p`.`page_id` AS "page_id",
CAST(`p`.`page_title` AS CHAR(10000) CHARACTER SET utf8) AS "page_title",
`r`.`rev_text_id` AS "revision_id",
`t`.`old_id` AS "text_id"
FROM
page p
INNER JOIN `revision` r
ON `p`.`page_id` = `r`.`rev_page` -- Confirmed to be reference to page.page_id
INNER JOIN `text` t
ON `r`.`rev_text_id` = `t`.`old_id` -- Confirmed to be reference to revision table
WHERE
`p`.`page_title` LIKE '%Tests/parent-a%'; -- Match with title of a parent page
Deleting from database the rows
--
-- Making a comma separated list of each table ids to delete, Will be used in a delete transaction
--
SELECT
CONCAT('IN(', GROUP_CONCAT(`p`.`page_id`), ')') AS 'page',
CONCAT('IN(', GROUP_CONCAT(`r`.`rev_text_id`), ')') AS 'revision',
CONCAT('IN(', GROUP_CONCAT(`t`.`old_id`), ')') AS 'text'
FROM
page p
INNER JOIN `revision` r
ON `p`.`page_id` = `r`.`rev_page` -- Confirmed to be reference to page.page_id
INNER JOIN text t
ON `r`.`rev_text_id` = `t`.`old_id` -- Confirmed to be reference to revision table
WHERE
p.page_title LIKE '%Tests/parent-a%'; -- Match with title of a parent page
以下のような結果が得られるでしょう。
+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+ | page | revision | text | +--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+ | IN(5530,5528,5529,5530,5529,5528,5532,5532,5532) | IN(9918,9921,9917,9919,9920,9916,9922,9915,9923) | IN(9918,9921,9917,9919,9920,9916,9922,9915,9923) | +--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+ 1 row in set (0.07 sec)
以下ののクエリで結合したidを置き換えてください。
--
-- DELETING pages in one transaction
-- NOTE: adjust the content IN(...) with your own :)
--
SET autocommit=0;
START TRANSACTION;
DELETE FROM `page` WHERE page_id IN(5530,5528,5529,5530,5529,5528,5532,5532,5532);
DELETE FROM `revision` WHERE rev_text_id IN(9918,9921,9917,9919,9920,9916,9922,9915,9923);
DELETE FROM `text` WHERE old_id IN(9918,9921,9917,9919,9920,9916,9922,9915,9923);
COMMIT;
この後、この管理スクリプトを使って掃除のようなことができます。
php maintenance/deleteOrphanedRevisions.php