Manual:Revision table

The revision table holds metadata for every edit done to a page within the wiki.

Every edit of a page creates a revision row, which holds information such as the user who made the edit, the time at which the edit was made, and a reference to the new wikitext in the  table.

Note that a row is partly about the edit operation and partly about the result of that operation, the new wikitext.

It does not give a direct reference to the old wikitext.

1>m:Special:MyLanguage/Help:Import|Import of the last revision of a page from another wiki produces two entries in the revision table, one with the date and wikitext of the imported revision, and one with the import date.

The wikitext of the latter, which becomes that of the current page, is, if a page with the same name already existed, that of the more recent of the two pages.

The revision table is very similar to the  table.

The revision table is used for 1>m:Special:MyLanguage/Help:Page history|page history and listings.

The  table is used for, , , and, in the case of page creation, for the.

Differences include:


 * The <tvar|1>recentchanges</> table also records logged events such as page moves and deletions


 * Items in the <tvar|2>recentchanges</> table are periodically purged; those in the <tvar|3>revision</> table are retained permanently, unless the page is deleted or the site owner runs the <tvar|1></> maintenance script.


 * as said, import of a page revision not only adds an entry to the <tvar|1>revision</> table with the import date, but also one with the original date.

Deleted revisions are moved to the <tvar|1></> table.

The revision table and the <tvar|3>text</> table were introduced in <tvar|1></> to supersede the <tvar|2></> table.

rev_id
This field holds the primary key for each revision.

<tvar|1></> is a foreign key to this field.

The <tvar|1>rev_id</> numbers have been preserved across deletion/undeletion since the table's inception in MediaWiki 1.5, when <tvar|1>rev_id</> and <tvar|2>text_id</> superseded the old <tvar|3>cur_id</>.

rev_page
This field holds a reference to the <tvar|1></> to which this revision pertains.

The number in this field is equal to the <tvar|1></> field of said page.

This should never be invalid; if it is, that revision won't show up in the page history.

If <tvar|1></> links to a revision with an invalid rev_page, this will cause the "The revision #0 of the page named 'Foo' does not exist" error.

rev_text_id
This is a foreign key to <tvar|1></> in the <tvar|2></> table.

(The <tvar|1>text</> table is where the actual bulk text is stored.)

It's possible for multiple revisions to use the same text - for instance, revisions where only metadata is altered, or where a <tvar|1></> is done to a previous version.

If rows in the <tvar|1></> table with <tvar|2> = rev_id</> exist, this field should be ignored (and may be 0) in favor of the corresponding data from the slots and content tables.

rev_comment
This field holds an editor's 1>m:Special:MyLanguage/Help:Edit summary</>|edit summary (editor's comment on revision).

This text is shown in the 1>m:Special:MyLanguage/Help:Page history</>|history and 2>Special:MyLanguage/Help:User contributions</>|contributions. ( The table contains a copy used for, ,  , and, in the case of page creation, for the . )

It is rendered in a sanitized subset of wiki markup.

<tvar|1></> of the <tvar|2>revision_comment_temp</> table and the <tvar|3></> table should be used instead!

rev_comment_id
This field holds a reference to the <tvar|1></> row containing the editor's comment.

rev_user
This is equal to the <tvar|1></> of the user who made this edit.

The value for this field is 0 for anonymous edits, initializations scripts, and for some mass imports.

Note, there are bugs: <tvar|1>T112384</>.

rev_user_text
This field holds the text of the editor's, or the IP address of the editor if the revision was done by an unregistered user.

In anonymous revisions imported from UseModWiki or early incarnations of the Phase II software, this field may contain an IP address with the final octet obscured (i.e. <tvar|1> </> such as <tvar|2>24.150.61.xxx</>; see <tvar|3>bug 3631</>).

Some edits imported from UseModWiki may contain a Reverse DNS lookup hostname like <tvar|1> </> or <tvar|2> </>.

rev_timestamp
Holds the <tvar|1></> of the edit.

rev_minor_edit
Records whether the user marked the '1>m:Special:MyLanguage/Help:Minor edit</>|minor edit' checkbox.

If the value for this field is 1, then the edit was declared as 'minor'; it is 0 otherwise.

Many automated edits are marked as minor.

rev_deleted
This field is reserved for <tvar|1></> system.

It's a bitfield in which the values are DELETED_TEXT = 1; DELETED_COMMENT = 2; DELETED_USER = 4; and DELETED_RESTRICTED = 8.

So, for example, if nothing has been deleted from that revision, then the value is 0; if both the comment and user have been deleted, then the value is 6.

rev_len
This field contains the length of the article after the revision, in bytes.

Used in history pages.

Corresponds to <tvar|1></>.

rev_parent_id
The rev_id of the previous revision to the page.

Corresponds to <tvar|1></>. For edits which are new page creations, rev_parent_id = 0.

Usage
This field is used to add support for a tree structure.

This field is e.g. used to calculate the size difference of a certain revision with the previous revision in the page history view.

If a parent ID points to a revision that's associated with a different page, MediaWiki will still use that parent revision as a basis of comparison for purposes of calculating size difference.

If a revision is deleted from the database, and another revision's parent ID still points to it, then MediaWiki will behave the same as if there were no parent revision; i.e. it will assume the previous size was 0.

Transferred revisions
When revisions are imported from another wiki, the imported revisions' parent ID tree structure from the source wiki is retained (this is implemented by each revision's, other than the tail revision, having a XML element that is used to populate rev_parent_id), and the destination wiki's revisions' parent IDs are not updated.

Likewise, when 1>Special:MyLanguage/Manual:Merging page histories</>|page histories are merged, the parent IDs of the revisions from the source and destination pages are not updated.

<tvar|1></> can be used to populate <tvar|2>rev_parent_id</> based on revision timestamps and revision IDs.

In the case of an 1>Special:MyLanguage/Manual:Edit conflict</>|edit conflict, the revision ID of the edit that is saved first (causing the conflict) will be used as the parent ID of the edit that is saved second (after resolving the conflict).

rev_sha1
This field is used to add the SHA-1 text content hash in base-36 (generated by <tvar|1></>.)

rev_content_model
Content model, see <tvar|1>CONTENT_MODEL_XXX</> constants in <tvar|2></>.

These IDs will be exposed in the API and XML dumps.

Extensions that define their own content model IDs should take care to avoid conflicts.

Using the extension name as a prefix is recommended, for example <tvar|1>'myextension-somecontent'</>.

Possible values are, e.g.: 'wikitext', 'javascript', 'css', 'text', and 'json'

If rows in the slots table with <tvar|1>slot_revision_id = rev_id</> this field should be ignored (and may be NULL) in favor of the corresponding data from the slots and content tables.

rev_content_format
Content format, see <tvar|1>CONTENT_FORMAT_XXX</> constants in <tvar|2></>.

These should be MIME types, and will be exposed in the API and XML dumps.

Extensions are free to use the below formats, or define their own.

It is recommended to stick with the [<tvar|url>http://www.iana.org/assignments/media-types/media-types.xhtml</> conventions for MIME types].

Possible values are, e.g.: 'text/x-wiki', 'text/javascript', 'text/css', 'text/plain', 'text/html', 'application/vnd.php.serialized', 'application/json', 'application/xml'

If rows in the slots table with slot_revision_id = rev_id exist, this field should be ignored (and may be NULL).

Schema summary
<tt>DESCRIBE revision;</tt> ++-+--+-+++ ++-+--+-+++ ++-+--+-+++
 * Field             | Type                | Null | Key | Default        | Extra          |
 * rev_id            | int(10) unsigned    | NO   | PRI | NULL           | auto_increment |
 * rev_page          | int(10) unsigned    | NO   | MUL | NULL           |                |
 * rev_text_id       | int(10) unsigned    | NO   |     | 0              |                |
 * rev_comment       | varbinary(767)      | NO   |     |                |                |
 * rev_user          | int(10) unsigned    | NO   | MUL | 0              |                |
 * rev_user_text     | varbinary(255)      | NO   | MUL |                |                |
 * rev_timestamp     | binary(14)          | NO   | MUL |                |                |
 * rev_minor_edit    | tinyint(3) unsigned | NO   |     | 0              |                |
 * rev_deleted       | tinyint(3) unsigned | NO   |     | 0              |                |
 * rev_len           | int(10) unsigned    | YES  |     | NULL           |                |
 * rev_parent_id     | int(10) unsigned    | YES  |     | NULL           |                |
 * rev_sha1          | varbinary(32)       | NO   |     |                |                |
 * rev_content_model | varbinary(32)       | YES  |     | NULL           |                |
 * rev_content_format | varbinary(64)      | YES  |     | NULL           |                |

<tt>DESCRIBE revision;</tt> ++-+--+-+++ ++-+--+-+++ ++-+--+-+++
 * Field             | Type                | Null | Key | Default        | Extra          |
 * rev_id            | int(10) unsigned    | NO   | PRI | NULL           | auto_increment |
 * rev_page          | int(10) unsigned    | NO   | MUL | NULL           |                |
 * rev_text_id       | int(10) unsigned    | NO   |     | NULL           |                |
 * rev_comment       | varbinary(767)      | NO   |     |                |                |
 * rev_user          | int(10) unsigned    | NO   | MUL | 0              |                |
 * rev_user_text     | varbinary(255)      | NO   | MUL |                |                |
 * rev_timestamp     | binary(14)          | NO   | MUL |                |                |
 * rev_minor_edit    | tinyint(3) unsigned | NO   |     | 0              |                |
 * rev_deleted       | tinyint(3) unsigned | NO   |     | 0              |                |
 * rev_len           | int(10) unsigned    | YES  |     | NULL           |                |
 * rev_parent_id     | int(10) unsigned    | YES  |     | NULL           |                |
 * rev_sha1          | varbinary(32)       | NO   |     |                |                |
 * rev_content_model | varbinary(32)       | YES  |     | NULL           |                |
 * rev_content_format | varbinary(64)      | YES  |     | NULL           |                |

<tt>DESCRIBE revision;</tt> ++-+--+-+++ ++-+--+-+++ ++-+--+-+++
 * Field             | Type                | Null | Key | Default        | Extra          |
 * rev_id            | int(10) unsigned    | NO   | PRI | NULL           | auto_increment |
 * rev_page          | int(10) unsigned    | NO   | MUL | NULL           |                |
 * rev_text_id       | int(10) unsigned    | NO   |     | NULL           |                |
 * rev_comment       | varbinary(767)      | NO   |     | NULL           |                |
 * rev_user          | int(10) unsigned    | NO   | MUL | 0              |                |
 * rev_user_text     | varbinary(255)      | NO   | MUL |                |                |
 * rev_timestamp     | binary(14)          | NO   | MUL |                |                |
 * rev_minor_edit    | tinyint(3) unsigned | NO   |     | 0              |                |
 * rev_deleted       | tinyint(3) unsigned | NO   |     | 0              |                |
 * rev_len           | int(10) unsigned    | YES  |     | NULL           |                |
 * rev_parent_id     | int(10) unsigned    | YES  |     | NULL           |                |
 * rev_sha1          | varbinary(32)       | NO   |     |                |                |
 * rev_content_model | varbinary(32)       | YES  |     | NULL           |                |
 * rev_content_format | varbinary(64)      | YES  |     | NULL           |                |

<tt>DESCRIBE revision;</tt> ++-+--+-+-++ ++-+--+-+-++ ++-+--+-+-++
 * Field             | Type                | Null | Key | Default | Extra          |
 * rev_id            | int(10) unsigned    | NO   | PRI | NULL    | AUTO_INCREMENT |
 * rev_page          | int(10) unsigned    | NO   | PRI | NULL    |                |
 * rev_text_id       | int(10) unsigned    | NO   |     | NULL    |                |
 * rev_comment       | tinyblob            | NO   |     | NULL    |                |
 * rev_user          | int(10) unsigned    | NO   | MUL | 0       |                |
 * rev_user_text     | varchar(255) binary | NO   | MUL | NULL    |                |
 * rev_timestamp     | binary(14)          | NO   | MUL | NULL    |                |
 * rev_minor_edit    | tinyint(3) unsigned | NO   |     | 0       |                |
 * rev_deleted       | tinyint(3) unsigned | NO   |     | 0       |                |
 * rev_len           | int(10) unsigned    | YES  |     | NULL    |                |
 * rev_parent_id     | int(10) unsigned    | YES  |     | NULL    |                |
 * rev_sha1          | varbinary(32)       | NO   |     | NULL    |                |
 * rev_content_model | varbinary(32)       | YES  |     | NULL    |                |
 * rev_content_format | varbinary(64)      | YES  |     | NULL    |                |

<tt>DESCRIBE revision;</tt> ++-+--+-+-++ ++-+--+-+-++ ++-+--+-+-++
 * Field         | Type                | Null | Key | Default | Extra          |
 * rev_id        | int(10) unsigned    | NO   | PRI | NULL    | AUTO_INCREMENT |
 * rev_page      | int(10) unsigned    | NO   | PRI | NULL    |                |
 * rev_text_id   | int(10) unsigned    | NO   |     | NULL    |                |
 * rev_comment   | tinyblob            | NO   |     | NULL    |                |
 * rev_user      | int(10) unsigned    | NO   | MUL | 0       |                |
 * rev_user_text | varchar(255) binary | NO   | MUL | NULL    |                |
 * rev_timestamp | binary(14)          | NO   | MUL | NULL    |                |
 * rev_minor_edit | tinyint(3) unsigned | NO  |     | 0       |                |
 * rev_deleted   | tinyint(3) unsigned | NO   |     | 0       |                |
 * rev_len       | int(10) unsigned    | YES  |     | NULL    |                |
 * rev_parent_id | int(10) unsigned    | YES  |     | NULL    |                |
 * rev_sha1      | varbinary(32)       | NO   |     | NULL    |                |

<tt>DESCRIBE revision;</tt> ++-+--+-+-++ ++-+--+-+-++ ++-+--+-+-++
 * Field         | Type                | Null | Key | Default | Extra          |
 * rev_id        | int(10) unsigned    | NO   | PRI | NULL    | AUTO_INCREMENT |
 * rev_page      | int(10) unsigned    | NO   | PRI | NULL    |                |
 * rev_text_id   | int(10) unsigned    | NO   |     | NULL    |                |
 * rev_comment   | tinyblob            | NO   |     | NULL    |                |
 * rev_user      | int(10) unsigned    | NO   | MUL | 0       |                |
 * rev_user_text | varchar(255) binary | NO   | MUL | NULL    |                |
 * rev_timestamp | binary(14)          | NO   | MUL | NULL    |                |
 * rev_minor_edit | tinyint(3) unsigned | NO  |     | 0       |                |
 * rev_deleted   | tinyint(3) unsigned | NO   |     | 0       |                |
 * rev_len       | int(10) unsigned    | YES  |     | NULL    |                |
 * rev_parent_id | int(10) unsigned    | YES  |     | NULL    |                |

<tt>DESCRIBE revision;</tt> ++-+--+-+-++ ++-+--+-+-++ ++-+--+-+-++
 * Field         | Type                | Null | Key | Default | Extra          |
 * rev_id        | int(8) unsigned     | NO   | PRI | NULL    | AUTO_INCREMENT |
 * rev_page      | int(8) unsigned     | NO   | PRI | NULL    |                |
 * rev_text_id   | int(8) unsigned     | NO   |     | NULL    |                |
 * rev_comment   | tinyblob            | NO   |     | NULL    |                |
 * rev_user      | int(5) unsigned     | NO   | MUL | 0       |                |
 * rev_user_text | varchar(255) binary | NO   | MUL | NULL    |                |
 * rev_timestamp | char(14) binary     | NO   | MUL | NULL    |                |
 * rev_minor_edit | tinyint(1) unsigned | NO  |     | 0       |                |
 * rev_deleted   | tinyint(1) unsigned | NO   |     | 0       |                |

Indexes
<tt>SHOW INDEX IN revision;</tt> <pre style="width: 170%"> +--++-+--+---+---+-+--++--++-+---+ +--++-+--+---+---+-+--++--++-+---+ +--++-+--+---+---+-+--++--++-+---+
 * Table   | Non_unique | Key_name            | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 * revision |         0 | PRIMARY             |            1 | rev_id        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         0 | rev_page_id         |            1 | rev_page      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         0 | rev_page_id         |            2 | rev_id        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | rev_timestamp       |            1 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_timestamp      |            1 | rev_page      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_timestamp      |            2 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | user_timestamp      |            1 | rev_user      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | user_timestamp      |            2 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | usertext_timestamp  |            1 | rev_user_text | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | usertext_timestamp  |            2 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_user_timestamp |            1 | rev_page      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_user_timestamp |            2 | rev_user      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_user_timestamp |            3 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |