Toolserver:Database schema

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

Page

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

• 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

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

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

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

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

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

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

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

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

User

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

User_groups table
Field	Type	Null	Key	Default	Extra
ug_user	int(5) unsigned	No		0
ug_group	char(16)	No

Images and media

Image

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

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

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

Imagelinks table
Field	Type	Null	Key	Default	Extra
il_from	var(8) unsigned	No		0
il_to	varchar(255)	No

IP blocks

Ipblocks

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

Math

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

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

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

Hitcounter table
Field	Type	Null	Key	Default	Extra
hc_id	int(10) unsigned	No		0

Interwiki

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

Toolserver database

• Please note: These tables are not replicated as such, but created for toolserver users.
• Database is "toolserver"

Namespace table

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

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

• 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