User:RichMorin/mw revision

Every edit of a page also creates a revision row. This stores metadata about the revision and a reference to the text-storage backend.

Inter-table Relationships

rev_page - page ID ( page.page_id)

rev_text_id - text ID ( text.old_id)

rev_user - user ID ( user.user_id)

rev_user_text - user name ( user.user_name)

MySQL Table Description

mysql> desc mw_revision;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| rev_id         | int(8) unsigned     |      | PRI | NULL    | auto_increment |
| rev_page       | int(8) unsigned     |      | PRI | 0       |                |
| rev_text_id    | int(8) unsigned     |      |     | 0       |                |
| rev_comment    | tinyblob            |      |     |         |                |
| rev_user       | int(5) unsigned     |      | MUL | 0       |                |
| rev_user_text  | varchar(255)        |      | MUL |         |                |
| rev_timestamp  | varchar(14)         |      | MUL |         |                |
| rev_minor_edit | tinyint(1) unsigned |      |     | 0       |                |
| rev_deleted    | tinyint(1) unsigned |      |     | 0       |                |
+----------------+---------------------+------+-----+---------+----------------+
9 rows in set

Annotated Table Creation Code

-- Every edit of a page creates also a revision row.
-- This stores metadata about the revision, and a reference
-- to the text storage backend.

CREATE TABLE /*$wgDBprefix*/revision (

  rev_id              int(8)         unsigned     NOT NULL  auto_increment,
  
  -- Key to page_id. This should _never_ be invalid.

  rev_page            int(8)         unsigned     NOT NULL,
  
  -- Key to text.old_id, 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 a rollback to a previous version.

  rev_text_id         int(8)         unsigned     NOT NULL,
  
  -- Text comment summarizing the change.
  -- This text is shown in the history and other change lists,
  -- rendered in a subset of wiki markup.

  rev_comment         tinyblob                    NOT NULL  default '',
  
  -- Key to user.user_id of the user who made this edit.
  -- Stores 0 for anonymous edits and for some mass imports.

  rev_user            int(5)         unsigned     NOT NULL  default '0',
  
  -- Text username or IP address of the editor.

  rev_user_text       varchar(255)   binary       NOT NULL  default '',
  
  -- Timestamp

  rev_timestamp       char(14)       binary       NOT NULL  default '',
  
  -- Records whether the user marked the 'minor edit' checkbox.
  -- Many automated edits are marked as minor.

  rev_minor_edit      tinyint(1)     unsigned     NOT NULL  default '0',
  
  -- Not yet used; reserved for future changes to the deletion system.

  rev_deleted         tinyint(1)     unsigned     NOT NULL  default '0',
  
PRIMARY KEY           rev_page_id (rev_page, rev_id),
UNIQUE INDEX          rev_id (rev_id),
INDEX                 rev_timestamp (rev_timestamp),
INDEX                 page_timestamp (rev_page, rev_timestamp),
INDEX                 user_timestamp (rev_user, rev_timestamp),
INDEX                 usertext_timestamp (rev_user_text, rev_timestamp)

) ENGINE=InnoDB;