|
|
本館粉絲專頁 |
|
|
使用者:Minwei/維基筆記/Schema轉換/New Tables
台灣棒球維基館
< 使用者:Minwei | 維基筆記 | Schema轉換
目次 |
[編輯] Externallinks table
MediaWiki version: 1.9
The externallinks table is where MediaWiki stores and tracks external links. This table was introduced after 1.5.
mysql> describe mw_externallinks; +----------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+------+-----+---------+-------+ | el_from | int(8) unsigned | NO | MUL | 0 | | | el_to | blob | NO | MUL | NULL | | | el_index | blob | NO | MUL | NULL | | +----------+-----------------+------+-----+---------+-------+
[編輯] Filearchive table
MediaWiki version: 1.9
mysql> describe mw_filearchive;
+----------------------+--------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| fa_id | int(11) | NO | PRI | NULL | auto_increment |
| fa_name | varchar(255) | NO | MUL | NULL | |
| fa_archive_name | varchar(255) | YES | | NULL | |
| fa_storage_group | varchar(16) | YES | MUL | NULL | |
| fa_storage_key | varchar(64) | YES | | NULL | |
| fa_deleted_user | int(11) | YES | MUL | NULL | |
| fa_deleted_timestamp | char(14) | YES | MUL | NULL | |
| fa_deleted_reason | text | YES | | NULL | |
| fa_size | int(8) unsigned | YES | | 0 | |
| fa_width | int(5) | YES | | 0 | |
| fa_height | int(5) | YES | | 0 | |
| fa_metadata | mediumblob | YES | | NULL | |
| fa_bits | int(3) | YES | | 0 | |
| fa_media_type | enum('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE') | YES | | NULL | |
| fa_major_mime | enum('unknown','application','audio','image','text','video','message','model','multipart') | YES | | unknown | |
| fa_minor_mime | varchar(32) | YES | | unknown | |
| fa_description | tinyblob | YES | | NULL | |
| fa_user | int(5) unsigned | YES | | 0 | |
| fa_user_text | varchar(255) | YES | | NULL | |
| fa_timestamp | char(14) | YES | | NULL | |
+----------------------+--------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
20 rows in set (0.01 sec)
[編輯] Job table
MediaWiki version: 1.6 and after
應該不是很重要的資料表。
mysql> describe mw_job; +---------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------------+------+-----+---------+----------------+ | job_id | int(9) unsigned | NO | PRI | NULL | auto_increment | | job_cmd | varchar(255) | NO | MUL | NULL | | | job_namespace | int(11) | NO | | NULL | | | job_title | varchar(255) | NO | | NULL | | | job_params | blob | NO | | NULL | | +---------------+-----------------+------+-----+---------+----------------+ 5 rows in set (0.03 sec)
[編輯] Langlinks table
MediaWiki version: 1.7 and after
This table tracks interlanguage links.
mysql> describe mw_langlinks; +----------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+------+-----+---------+-------+ | ll_from | int(8) unsigned | NO | PRI | 0 | | | ll_lang | varchar(10) | NO | PRI | NULL | | | ll_title | varchar(255) | NO | | NULL | | +----------+-----------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
[編輯] Page table
維基的核心。文件的「文字」是儲存在text。如要檢索則是從page_title欄位先下手。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. 從1.4版的Cur table轉換過來。
mysql> describe mw_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) | 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 | | +-----------------------+---------------------+------+-----+---------+----------------+ 11 rows in set (0.02 sec)
對映不到1.4版的欄位
page_latest
This is the key to rev_id for the current revision. This may be 0 during page creation.
page_len
←麻煩
Uncompressed length in bytes of the page's current source text.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| cur_id | int(8) unsigned | PRI | auto_increment | ||
| cur_namespace | tinyint(2) unsigned | MUL | 0 | ||
| cur_title | varchar(255) binary | MUL | |||
| cur_text | mediumtext | ||||
| cur_comment | tinyblob | ||||
| cur_user | int(5) unsigned | MUL | 0 | ||
| cur_user_text | varchar(255) binary | MUL | |||
| cur_timestamp | varchar(14) binary | MUL | |||
| cur_restrictions | tinyblob | ||||
| cur_counter | bigint(20) unsigned | 0 | |||
| cur_is_redirect | tinyint(1) unsigned | 0 | |||
| cur_minor_edit | tinyint(1) unsigned | 0 | |||
| cur_is_new | tinyint(1) unsigned | 0 | |||
| cur_random | double unsigned | MUL | 0 | ||
| inverse_timestamp | varchar(14) binary | ||||
| cur_touched | varchar(14) binary |
Some information in the cur table relates to the last change of the article (which might well be its creation, which is indicated by cur_is_new). cur_text holds the wikitext. cur_comment is the edit summary of the last change; cur_user and cur_user_text identify the user that made that change, where cur_user holds the ID of that user, if s/he was logged in. cur_timestamp notes the time and date of the change, cur_minor_edit carries the state of that checkbox upon saving.
[編輯] Pagelinks table
The table is new to version 1.5. 是從1.4版的「links table」及「brokenlinks」合併而成。MediaWiki version: 1.11
- Links table
- l_from is the cur_id of the article that contains the link. (This is a change from 1.2, wherein the title of the article was stored. This was not very wise, and has now been fixed.)
- l_to is the cur_id of the "target" article.
- Brokenlinks table
- The links and brokenlinks tables have been merged to a single pagelinks table, which records the namespace+title key pair of target links rather than the page ID or the prefixed title.
- The brokenlinks table stores links to articles that don't exist yet. This is used for Special:Wantedpages (which is sometimes disabled), and perhaps for some link caching. It is the twin of the links table.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| bl_from | int(8) unsigned | MUL | 0 | ||
| bl_to | varchar(255) binary | MUL |
Tracks all internal links in the Wiki. Each entry contains the source page's ID, and the namespace (number) and article name (in text) that is being linked to within that source page. There may be many instances of the source page's ID, as many as the internal links within it, but there can be only one entry per internal link for any page ID (or MYSQL will yell out a fatal error).
Note that the target page may or may not exist, and due to renames and deletions may refer to different page records as time goes by.
mysql> describe mw_pagelinks; +--------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+-------+ | pl_from | int(10) unsigned | NO | PRI | 0 | | | pl_namespace | int(11) | NO | PRI | 0 | | | pl_title | varchar(255) | NO | PRI | | | +--------------+------------------+------+-----+---------+-------+
[編輯] Page restrictions table
mysql> describe mw_page_restrictions; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | pr_page | int(8) | NO | PRI | NULL | | | pr_type | varchar(255) | NO | PRI | NULL | | | pr_level | varchar(255) | NO | MUL | NULL | | | pr_cascade | tinyint(4) | NO | MUL | NULL | | | pr_user | int(8) | YES | | NULL | | | pr_expiry | char(14) | YES | | NULL | | | pr_id | int(10) unsigned | NO | UNI | NULL | auto_increment | +------------+------------------+------+-----+---------+----------------+ 7 rows in set (0.30 sec)5 rows in set (0.02 sec)
[編輯] Querycache info table
MediaWiki version: 1.7 and after
mysql> describe mw_querycache_info; +---------------+-------------+------+-----+----------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+----------------+-------+ | qci_type | varchar(32) | NO | PRI | NULL | | | qci_timestamp | char(14) | NO | | 19700101000000 | | +---------------+-------------+------+-----+----------------+-------+ 2 rows in set (0.01 sec)
[編輯] Querycachetwo table
MediaWiki version: 1.9 and after
Table used for caching expensive grouped queries that need two links (for example double-redirects)
.mysql> describe mw_querycachetwo; +------------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-----------------+------+-----+---------+-------+ | qcc_type | char(32) | NO | MUL | NULL | | | qcc_value | int(5) unsigned | NO | | 0 | | | qcc_namespace | int(11) | NO | | 0 | | | qcc_title | char(255) | NO | | NULL | | | qcc_namespacetwo | int(11) | NO | | 0 | | | qcc_titletwo | char(255) | NO | | NULL | | +------------------+-----------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)
[編輯] Redirect table
MediaWiki version: 1.9 and after
mysql> describe mw_redirect; +--------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------+------+-----+---------+-------+ | rd_from | int(8) unsigned | NO | PRI | 0 | | | rd_namespace | int(11) | NO | MUL | 0 | | | rd_title | varchar(255) | NO | | NULL | | +--------------+-----------------+------+-----+---------+-------+ 3 rows in set (0.02 sec)
[編輯] Revision table
MediaWiki version: 1.10 and after
See http://www.mediawiki.org/wiki/Revision_table.
mysql> describe mw_revision; +----------------+---------------------+------+-----+---------+----------------+ | 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) | NO | MUL | NULL | | | rev_timestamp | char(14) | NO | MUL | NULL | | | rev_minor_edit | tinyint(1) unsigned | NO | | 0 | | | rev_deleted | tinyint(1) unsigned | NO | | 0 | | | rev_len | int(8) unsigned | YES | | NULL | | | rev_parent_id | int(8) unsigned | YES | | NULL | | +----------------+---------------------+------+-----+---------+----------------+ 11 rows in set
[編輯] Templatelinks table
MediaWiki version: 1.9
mysql> describe mw_templatelinks; +--------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------+------+-----+---------+-------+ | tl_from | int(8) unsigned | NO | PRI | 0 | | | tl_namespace | int(11) | NO | PRI | 0 | | | tl_title | varchar(255) | NO | PRI | NULL | | +--------------+-----------------+------+-----+---------+-------+ 3 rows in set (0.02 sec)
[編輯] Text table
The text table holds the wikitext of individual page revisions. Field names are a holdover from the 'old' revisions table in MediaWiki 1.4 and earlier.MediaWiki version: 1.9
Field names are a holdover from the old revisions table in MediaWiki 1.4 and earlier.
mysql> describe mw_text; +-----------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------+------+-----+---------+----------------+ | old_id | int(8) unsigned | NO | PRI | NULL | auto_increment | | old_text | mediumblob | NO | | NULL | | | old_flags | tinyblob | NO | | NULL | | +-----------+-----------------+------+-----+---------+----------------+ 3 rows in set
- old_id
- revision.rev_text_id in revision table is a key to this column.
- old_text
- The wikitext of the page.
- old_flags
- Comma-separated list of flags:
old_flags: -- gzip: text is compressed with PHP's gzdeflate() function. -- utf8: text was stored as UTF-8. -- If $wgLegacyEncoding option is on, rows *without* this flag -- will be converted to UTF-8 transparently at load time. -- object: text field contained a serialized PHP object. -- The object either contains multiple versions compressed -- together to achieve a better compression ratio, or it refers -- to another row where the text can be found.
[編輯] Trackbacks table
MediaWiki version: 1.5 and after
+----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | tb_id | int(11) | NO | PRI | NULL | auto_increment | | tb_page | int(11) | YES | MUL | NULL | | | tb_title | varchar(255) | NO | | NULL | | | tb_url | varchar(255) | NO | | NULL | | | tb_ex | text | YES | | NULL | | | tb_name | varchar(255) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+
[編輯] Transcache table
MediaWiki version: 1.5 and after
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | tc_url | varchar(255) | NO | PRI | NULL | | | tc_contents | text | YES | | NULL | | | tc_time | int(11) | NO | | NULL | | +-------------+--------------+------+-----+---------+-------+
