Toolserver:Database schema
Jump to navigation
Jump to search
This page was moved from the Toolserver wiki.
Toolserver has been replaced by Toolforge. As such, the instructions here may no longer work, but may still be of historical interest.
Please help by updating examples, links, template links, etc. If a page is still relevant, move it to a normal title and leave a redirect.

Full MediaWiki database schema full size
The Toolserver uses a subset of the standard MediaWiki database schema (inset). Specific tables are listed below. You can see the most current version of these by typing DESCRIBE followed by the name of the table.
To see descriptions of the fields in a table, please click the section name for that table.
Article text and associated[edit]
Page[edit]
Each PAGE has an entry here which identifies it by title and contains some essential metadata.
Page table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
page_id | int(8) unsigned | No | 0 | ||
page_namespace | int(11) | No | 0 | ||
page_title | varbinary(255) | No | |||
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 | 0 | ||
page_touched | varbinary(14) | No | |||
page_latest | int(8) unsigned | No | 0 | ||
page_len | int(8) unsigned | No | 0 |
Text[edit]
- This table does not carry any current text (all July 2005 or older).
- If your tool requires access to current text, please see the WikiProxy page on Meta.
Text table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
old_id | int(8) unsigned | No | 0 | ||
old_text | mediumtext | No | Null | ||
old_flags | tinyblob | No | Null |
Revision[edit]
Revision table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
rev_id | int(8) unsigned | No | 0 | ||
rev_page | int(8) unsigned | No | 0 | ||
rev_text_id | int(8) unsigned | No | 0 | ||
rev_comment | varbinary(255) | Yes | Null | ||
rev_user | int(5) unsigned | No | 0 | ||
rev_user_text | varbinary(255) | No | |||
rev_timestamp | varbinary(14) | No | |||
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 |
Archive[edit]
Archive table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
ar_namespace | int(11) | No | 0 | ||
ar_title | varbinary(255) | No | |||
ar_user | int(5) unsigned | No | 0 | ||
ar_user_text | varbinary(255) | No | |||
ar_timestamp | varbinary(255) | No | |||
ar_minor_edit | tinyint(1) | No | 0 | ||
ar_flags | tinyblob | No | Null | ||
ar_rev_id | int(8) unsigned | Yes | Null | ||
ar_len | int(8) unsigned | Yes | Null | ||
ar_page_id | int(10) unsigned | Yes | Null | ||
ar_parent_id | int(10) unsigned | Yes | Null |
Page_restrictions[edit]
Page_restrictions table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
pr_page | int(8) | No | 0 | ||
pr_type | varbinary(255) | No | |||
pr_level | varbinary(255) | No | |||
pr_cascade | tinyint(4) | No | 0 | ||
pr_user | int(8) | Yes | Null | ||
pr_expiry | varbinary(14) | Yes | Null | ||
pr_id | int(10) unsigned | No | 0 |
Redirect[edit]
Redirect table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
rd_from | int(8) unsigned | No | 0 | ||
rd_namespace | int(11) | No | 0 | ||
rd_title | varbinary(255) | No |
Pagelinks[edit]
Pagelinks table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
pl_from | int(8) unsigned | No | 0 | ||
pl_namespace | int(11) | No | 0 | ||
pl_title | varbinary(255) | No |
Templatelinks[edit]
Templatelinks table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
tl_from | int(8) unsigned | No | 0 | ||
tl_namespace | int(11) | No | 0 | ||
tl_title | varbinary(255) | No |
Categorylinks[edit]
Categorylinks table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
cl_from | int(8) unsigned | No | 0 | ||
cl_to | varbinary(255) | No | |||
cl_sortkey | varbinary(230) | No | |||
cl_timestamp | timestamp | No | 0000-00-00 00:00:00 | ||
cl_sortkey_prefix | varbinary(255) | No | |||
cl_collation | varbinary(32) | No | |||
cl_type | enum('page','subcat','file') | No | page |
Langlinks[edit]
Langlinks table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
ll_from | int(8) unsigned | No | 0 | ||
ll_lang | varbinary(20) | No | |||
ll_title | varbinary(255) | No |
User and associated[edit]
User[edit]
User table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
user_id | int(5) unsigned | No | 0 | ||
user_name | varchar(255) | No | |||
user_registration | varchar(14) | Yes | Null | ||
user_editcount | int(11) | Yes | Null |
User_groups[edit]
User_groups table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
ug_user | int(5) unsigned | No | 0 | ||
ug_group | char(16) | No |
Images and media[edit]
Image[edit]
Image table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
img_name | varchar(255) | No | |||
img_size | int(8) unsigned | No | 0 | ||
img_width | int(5) | No | 0 | ||
img_height | int(5) | No | 0 | ||
img_metadata | mediumblob | No | Null | ||
img_bits | int(3) | No | 0 | ||
img_media_type | enum('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE') | Yes | Null | ||
img_major_mime | enum('unknown','application','audio','image','text','video','message','model','multipart') | No | Unknown | ||
img_minor_mime | varchar(32) | No | Unknown | ||
img_description | tinyblob | No | Null | ||
img_user | int(5) unsigned | No | 0 | ||
img_user_text | varchar(255) | No | |||
img_timestamp | varchar(14) | No | |||
img_sha1 | varchar(32) | No |
Filearchive[edit]
Filearchivee table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
fa_id | int(11) | No | 0 | ||
fa_name | varchar(255) | No | |||
fa_archive_name | varchar(255) | Yes | |||
fa_storage_group | varchar(16) | Yes | Null | ||
fa_storage_key | varchar(64) | Yes | |||
fa_deleted_user | int(11) | Yes | Null | ||
fa_deleted_timestamp | varchar(14) | Yes | |||
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','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE') | Yes | unknown | ||
fa_minor_mime | varchar(32) | Yes | unknown | ||
fa_user | int(5) unsigned | Yes | 0 | ||
fa_user_text | varchar(255) | Yes | |||
fa_timestamp | varchar(14) | Yes | |||
fa_deleted | tinyint(1) unsigned | No | 0 |
Oldimage[edit]
Oldimage table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
oi_name | varchar(255) | No | |||
oi_archive_name | varchar(255) | No | |||
oi_size | int(8) unsigned | No | 0 | ||
oi_width | int(5) | No | 0 | ||
oi_height | int(5) | No | 0 | ||
oi_bits | int(3) | No | 0 | ||
oi_description | tinyblob | No | Null | ||
oi_user | int(5) unsigned | No | 0 | ||
oi_user_text | varchar(255) | No | |||
oi_timestamp | varchar(14) | No | |||
oi_metadata | mediumblob | No | Null | ||
oi_media_type | enum('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE') | Yes | Null | ||
oi_major_mime | enum('unknown','application','audio','image','text','video','message','model','multipart') | No | Unknown | ||
oi_minor_mime | varchar(32) | No | Unknown | ||
oi_deleted | tinyint(3) unsigned | No | |||
oi_sha1 | varchar(32) | No |
Imagelinks[edit]
Imagelinks table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
il_from | var(8) unsigned | No | 0 | ||
il_to | varchar(255) | No |
IP blocks[edit]
Ipblocks[edit]
IPBlocks table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
ipb_id | int(8) | No | 0 | ||
ipb_address | varbinary(255) | No | |||
ipb_user | int(8) unsigned | No | 0 | ||
ipb_by | int(8) unsigned | No | 0 | ||
ipb_reason | tinyblob | No | Null | ||
ipb_timestamp | varchar(14) | No | |||
ipb_auto | tinyint(1) | No | 0 | ||
ipb_anon_only | tinyint(1) | No | 0 | ||
ipb_create_account | tinyint(1) | No | 1 | ||
ipb_expiry | varchar(14) | No | |||
ipb_range_start | varbinary(255) | No | |||
ipb_range_end | varbinary(255) | No | |||
ipb_enable_autoblock | tinyint(1) | No | 1 | ||
ipb_deleted | tinyint(1) | No | 0 | ||
ipb_block_email | tinyint(1) | No | 0 |
Other tables[edit]
Math[edit]
Math table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
math_inputhash | varchar(16) | No | |||
math_outputhash | varchar(16) | No | |||
math_html_conservativeness | tinyint(1) | No | 0 | ||
math_html | text | Yes | Null | ||
math_mathml | text | Yes | Null |
Site_stats[edit]
Site_stats table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
ss_row_id | int(8) unsigned | No | 0 | ||
ss_total_views | bigint(20) unsigned | Yes | 0 | ||
ss_total_edits | bigint(20) unsigned | Yes | 0 | ||
ss_good_articles | bigint(20) unsigned | Yes | 0 | ||
ss_total_pages | bigint(20) | Yes | -1 | ||
ss_users | bigint(20) | Yes | -1 | ||
ss_admins | int(10) | Yes | -1 | ||
ss_images | int(10) | Yes | 0 |
Logging[edit]
Logging table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
log_type | varchar(10) | No | 0 | ||
log_action | varchar(10) | No | 0 | ||
log_timestamp | varchar(14) | No | 19700101000000 | ||
log_user | int(10) unsigned | No | 0 | ||
log_namespace | int(11) | No | 0 | ||
log_title | varchar(255) | No | |||
log_comment | varchar(255) | No | |||
log_params | blob | Yes | Null |
Hitcounter[edit]
Hitcounter table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
hc_id | int(10) unsigned | No | 0 |
Interwiki[edit]
Interwiki table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
iw_prefix | char(32) | No | |||
iw_url | char(127) | No | |||
iw_local | tinyint(1) | No | 0 | ||
iw_trans | tinyint(1) | No | 0 |
Global tables[edit]
Toolserver database[edit]
- Please note: These tables are not replicated as such, but created for toolserver users.
- Database is "toolserver"
Namespace table[edit]
Namespace table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
dbname | varbinary(32) | No | Null | ||
domain | varbinary(48) | No | Null | ||
ns_id | int(8) | No | Null | ||
ns_name | varbinary(255) | Yes | Null |
Wiki table[edit]
Wiki table | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
dbname | varbinary(32) | No | Null | ||
lang | varbinary(16) | No | Null | ||
family | varbinary(16) | No | Null | ||
domain | varbinary(48) | Yes | Null | ||
size | int(11) | Yes | Null | ||
is_meta | tinyint(4) | No | Null | ||
is_closed | tinyint(4) | No | Null | ||
is_multilang | tinyint(4) | No | Null | ||
is_sensitive | tinyint(4) | No | Null | ||
root_category | varbinary(255) | Yes | Null | ||
server | tinyint(4) | Yes | Null |
Globaluser table[edit]
- This table is replicated.
- Database is "centralauth_p"
- Currently you can connect to it via "mysql -h sql-s3 centralauth_p" or "mysql -hcentralauth-p.userdb or sql metawiki_p (command-line access).
mysql> describe global_group_permissions; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | ggp_group | varchar(255) | NO | | NULL | | | ggp_permission | varchar(255) | NO | | NULL | | +----------------+--------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> describe global_user_groups ; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | gug_user | int(11) | NO | | NULL | | | gug_group | varchar(255) | NO | | NULL | | +-----------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> describe globaluser; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | gu_id | int(11) | NO | | 0 | | | gu_name | varchar(255) | YES | | NULL | | | gu_registration | varchar(14) | YES | | NULL | | | gu_home_db | varchar(255) | YES | | NULL | | | gu_locked | tinyint(1) | NO | | 0 | | | gu_hidden | tinyint(1) | NO | | 0 | | +-----------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> describe localnames; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | ln_wiki | varchar(255) | NO | | | | | ln_name | varchar(255) | NO | | | | +---------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> describe localuser; +-----------------------+-----------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------------------------------------+------+-----+---------+-------+ | lu_wiki | varchar(255) | NO | | NULL | | | lu_name | varchar(255) | NO | | | | | lu_attached_timestamp | varchar(14) | YES | | NULL | | | lu_attached_method | enum('primary','empty','mail','password','admin','new','login') | YES | | NULL | | +-----------------------+-----------------------------------------------------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)