Toolserver:Database schema

From mediawiki.org

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)


Toolserver:Category:Database schema

Retrieved from "https://www.mediawiki.org/w/index.php?title=Toolserver:Database_schema&oldid=5085446"