Handbuch:Seitentabelle

From MediaWiki.org
Jump to navigation Jump to search
This page is a translated version of the page Manual:Page table and the translation is 24% complete.
Other languages:
Deutsch • ‎English • ‎Yorùbá • ‎dansk • ‎español • ‎français • ‎polski • ‎українська • ‎中文 • ‎日本語 • ‎한국어
Handbuch:Inhaltsübersicht MediaWiki database layout page table


MediaWiki Version: 1.5

Die page-Tabelle kann als der "Kern des Wikis" betrachtet werden. Jede Seite einer MediaWiki-Installation hat hier einen Eintrag, der sie anhand des Titels identifiziert und einige wesentliche Metadaten enthält. Es wurde erstmals in r6710, der MediaWiki-Version 1.5, eingeführt.

Der Text der Seite selbst wird in der text -Tabelle gespeichert. Um den Text eines Artikels abzurufen, sucht MediaWiki zunächst nach page_title in der Seitentabelle. Dann wird page_latest verwendet, um die revision -Tabelle nach rev_id zu durchsuchen, und dabei wird rev_text_id erhalten. Der für rev_text_id erhaltene Wert wird verwendet, um in der Texttabelle nach old_id zu suchen, um den Text abzurufen. Wenn eine Seite gelöscht wird, werden die Revisionen in die archive -Tabelle verschoben.

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 below how to do.

Felder

page_id

Eindeutige Identifizierung des Primärschlüssels. This value is preserved across edits and renames.

Page IDs do not change when pages are moved, but they may change when pages are deleted and then restored. As of MediaWiki 1.27, the historical page ID is persisted in the archive table, and restored pages attempt to reclaim their old page ID.

MediaWiki offers a number of relevant tools:

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 https://www.mediawiki.org/w/api.php?action=query&prop=info&titles=Manual:Page%20table and https://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 99 for the core namespaces, and from 100 to 10,000 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 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").

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

MediaWiki Version: 1.24

Anzahl der Seitenbetrachtungen. Diese Funktion wurde in MediaWiki 1.25 nach einer Aufforderung zur Stellungnahme vollständig entfernt. Schon vorher haben viele Websites, darunter auch Wikimedia-Projekte, es aus Gründen der Leistungssteigerung deaktiviert; für Details siehe Handbuch $wgDisableCounters .

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:Random page for more details). Generated by 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 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

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

Unkomprimierte Länge des aktuellen Quelltextes der Seite in Bytes.

Dies gilt jedoch weder für Bilder, die noch Datensätze in dieser Tabelle haben, noch für Vorlagen, die zusätzlichen Text einfügen können. Stattdessen wird die unkomprimierte Länge in Bytes der description für die Datei verwendet, da letztere im Feld text.old_text steht.

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 Version: 1.21

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

page_lang

MediaWiki Version: 1.24

Sprache des Seiteninhalts. Set to the default value of NULL at the time of page creation.

Schematische Übersicht

MediaWiki Version: 1.35
Gerrit change 581141

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            | YES  |     | 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 Versions: 1.25 – 1.34
Gerrit change 167655

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 Version: 1.24
Gerrit change 135312

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 Version: 1.23
Gerrit change 101170

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 Versions: 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 Versions: 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 Versions: 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 Versions: 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    |                |
+-------------------+---------------------+------+-----+---------+----------------+

Indexes

MediaWiki Version: 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 code

Listing pages and relations with other essential tables

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

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

Siehe auch