Manual:page table

From MediaWiki.org
Jump to: navigation, search
Manual:ContentsManual:Contents MediaWiki database layoutManual:Database layout page table


MediaWiki version: 1.5

The page table can be considered the "core of the wiki". Each page in a MediaWiki installation has an entry here which identifies it by title and contains some essential metadata. It was first introduced in r6710, in MediaWiki 1.5.

The text of the page itself is stored in the text table. To retrieve the text of an article, MediaWiki first searches for page_title in the page table. Then, page_latest is used to search the revision table for rev_id, and rev_text_id is obtained in the process. The value obtained for rev_text_id is used to search for old_id in the text table to retrieve the text. When a page is deleted, the revisions are moved to the archive table.

Note Note: If you want to completely delete a page manually from the database, be sure to delete the entry for the page in the page table, and for all the page's revisions in the revision table, and all of the text rows corresponding only to the page in the text table. This can be done by deleting the page row, then running maintenance/deleteOrphanedRevisions.php. See also title 'Deleting pages with their relationships in text and revision tables' below how to do.

Fields[edit]

page_id[edit]

Uniquely identifying primary key. This value is preserved across edits, renames, and, as of MediaWiki 1.27, deletions, via an analogous field in the archive table (introduced in MediaWiki 1.11). For example, for this page, page_id = 10501. [1][2] This field can be accessed by WikiPage::getId(), Title::getArticleID(), etc.

page_namespace[edit]

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[edit]

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[edit]

MediaWiki version: 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").

Note Note: Beginning with MediaWiki 1.10, page protection controls were moved to the page restrictions table, so this field will be empty in databases generated by more current versions of MediaWiki. However, this field is still used in current versions of MediaWiki for rows generated by older versions of MediaWiki!

page_counter[edit]

<translate>

MediaWiki version{{<tvar|1>#if:</>|s}}:</translate>

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 the $wgDisableCounters global for details.

page_is_redirect[edit]

A value of 1 here indicates the article is a redirect; it is 0 in all other cases.

page_is_new[edit]

This field stores whether the page is a new entry or not; if the field contains a value of 1, then it indicates that the page is a new entry with only one edit. It is 0 in all other cases. Newly restored pages count as new entries, even if there is more than one revision. Rollback links are not displayed if the page is new, since there is nothing to roll back to.

page_random[edit]

Random decimal value, between 0 and 1, used for Special:Random (see Manual:Random page for more details). Generated by wfRandom().

page_touched[edit]

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[edit]

MediaWiki version: 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[edit]

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[edit]

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[edit]

MediaWiki version: 1.21

Content model, see CONTENT_MODEL_XXX constants. Comparable to revision.rev_content_model.

page_lang[edit]

MediaWiki version: 1.24

Page content language. Set to the default value of NULL at the time of page creation.

Schema summary[edit]

MediaWiki version: 1.23
MariaDB> 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_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        | double unsigned     | NO   | MUL | NULL           |                |
| page_touched       | binary(14)          | NO   |     |                |                |
| page_latest        | int(10) unsigned    | NO   |     | NULL           |                |
| page_len           | int(10) unsigned    | NO   | MUL | NULL           |                |
| page_content_model | varbinary(32)       | YES  |     | NULL           |                |
| page_links_updated | varbinary(14)       | YES  |     | NULL           |                |
+--------------------+---------------------+------+-----+----------------+----------------+
13 rows in set (0.00 sec)


MediaWiki version: 1.21
mysql> 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_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        | double unsigned     | NO   | MUL | NULL           |                |
| page_touched       | binary(14)          | NO   |     |                |                |
| page_latest        | int(10) unsigned    | NO   |     | NULL           |                |
| page_len           | int(10) unsigned    | NO   | MUL | NULL           |                |
| page_content_model | varbinary(32)       | YES  |     | NULL           |                |
+--------------------+---------------------+------+-----+----------------+----------------+
12 rows in set (0.00 sec)


MediaWiki versions: 1.18 – 1.19

DESCRIBE page; in MediaWiki 1.19 results in:

+-------------------+---------------------+------+-----+----------------+----------------+
| 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_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       | double unsigned     | NO   | MUL | NULL           |                |
| page_touched      | binary(14)          | NO   |     |                |                |
| page_latest       | int(10) unsigned    | NO   |     | NULL           |                |
| page_len          | int(10) unsigned    | NO   | MUL | NULL           |                |
+-------------------+---------------------+------+-----+----------------+----------------+

Wonder why the tinyint(1) flags page_is_redirect and page_is_new were lengthened to tinyint(3)!

MediaWiki version: 1.12

DESCRIBE page; in MediaWiki 1.12 results in:

+-----------------------+---------------------+------+-----+---------+----------------+
| 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)        | 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           | double unsigned     | NO   | MUL | NULL    |                |
| page_touched          | char(14)            | NO   |     | NULL    |                |
| page_latest           | int(8) unsigned     | NO   |     | NULL    |                |
| page_len              | int(8) unsigned     | NO   | MUL | NULL    |                |
+-----------------------+---------------------+------+-----+---------+----------------+
MediaWiki version: 1.11

DESCRIBE page; in MediaWiki 1.11 results in:

+-----------------------+---------------------+------+-----+---------+----------------+
| 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)        | 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           | double 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    |                |
+-----------------------+---------------------+------+-----+---------+----------------+

Indices[edit]

MediaWiki version: 1.20


mysql> 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         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| page  |          0 | name_title                  |            1 | page_namespace   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| page  |          0 | name_title                  |            2 | page_title       | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| page  |          1 | page_random                 |            1 | page_random      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| page  |          1 | page_len                    |            1 | page_len         | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| page  |          1 | page_redirect_namespace_len |            1 | page_is_redirect | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| page  |          1 | page_redirect_namespace_len |            2 | page_namespace   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| page  |          1 | page_redirect_namespace_len |            3 | page_len         | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Sample MySQL code[edit]

Listing pages and relations with other essential tables[edit]

The following code will select the most recent versions of all articles from core tables: [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[edit]

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

You should have a result set like this

+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+
| 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)

Replace the concatenated id in the following query

-- 
-- 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;

After, you can do some cleanup using this maintenance script:

php maintenance/deleteOrphanedRevisions.php

See also[edit]

DatabasesManual:Database layout Engines: MySQL/MariaDBManual:MySQLOracleManual:OraclePostgreSQLManual:PostgreSQLSQLiteManual:SQLite
Technical documentation: SchemaManual:Database layout (tablesCategory:MediaWiki database tables) – API property associationsAPI:Database field and API property associationsField prefixesDatabase field prefixesPrimary key storage in other fieldsManual:Primary key storage in other fieldsWikimedia extension tablesCategory:Wikimedia extension database tables
Configuration: SettingsManual:Configuration_settings#Database_settingsSharingManual:Shared database
Development: AccessManual:Database accessOptimizationDatabase optimizationPolicyDevelopment policy#Database_patchesUpdaterManual:DatabaseUpdater.phpExtension schema updatesManual:Hooks/LoadExtensionSchemaUpdatesPatch fileManual:SQL patch file
Core tables: archivecategorycategorylinkschange_tagconfigexternallinksfilearchivehitcounterimageimagelinksinterwikiiwlinksipblocksjobl10n_cachelanglinkslogginglog_searchmsg_resourcemsg_resource_linksmodule_depsobjectcacheoldimagepagepagelinkspage_propspage_restrictionsprotected_titlesquerycachequerycachetwoquerycache_inforecentchangesredirectrevisionsearchindexsitessite_identifiers - site_statstag_summarytemplatelinkstexttranscacheupdateloguploadstashuseruser_former_groupsuser_groupsuser_newtalkuser_propertiestag_summaryvalid_tagwatchlist
Language: English  • 日本語