1 -- SQL to create the initial tables for the Wikipedia database.
2 -- This is read and executed by the install script; you should
3 -- never have to run it by itself.
5 -- Indexes should be defined here; please import the rest from indexes.sql.
7 CREATE TABLE /*$wgDBprefix*/user (
8 user_id int(5) unsigned NOT NULL auto_increment,
9 user_name varchar(255) binary NOT NULL default '',
10 user_real_name varchar(255) binary NOT NULL default '',
11 user_password tinyblob NOT NULL default '',
12 user_newpassword tinyblob NOT NULL default '',
13 user_email tinytext NOT NULL default '',
14 user_emailauthenticationtimestamp varchar(14) binary NOT NULL default '0',
15 user_options blob NOT NULL default '',
16 user_touched char(14) binary NOT NULL default '',
17 user_token char(32) binary NOT NULL default '',
18 user_email_authenticated CHAR(14) BINARY,
19 user_email_token CHAR(32) BINARY,
20 user_email_token_expires CHAR(14) BINARY,
22 PRIMARY KEY user_id (user_id),
23 INDEX user_name (user_name(10)),
24 INDEX (user_email_token)
27 -- TODO: de-blob this; it should be a property table
28 CREATE TABLE /*$wgDBprefix*/user_rights (
29 ur_user int(5) unsigned NOT NULL,
30 ur_rights tinyblob NOT NULL default '',
31 UNIQUE KEY ur_user (ur_user)
34 -- The following table is no longer needed with Enotif >= 2.00
35 -- Entries for newtalk on user_talk page are handled like in the watchlist table
36 -- CREATE TABLE /*$wgDBprefix*/user_newtalk (
37 -- user_id int(5) NOT NULL default '0',
38 -- user_ip varchar(40) NOT NULL default '',
39 -- INDEX user_id (user_id),
40 -- INDEX user_ip (user_ip)
43 CREATE TABLE /*$wgDBprefix*/page (
45 page_id int(8) unsigned NOT NULL auto_increment,
46 page_namespace tinyint NOT NULL,
47 page_title varchar(255) binary NOT NULL,
49 -- Mutable information
50 page_restrictions tinyblob NOT NULL default '',
51 page_counter bigint(20) unsigned NOT NULL default '0',
52 page_is_redirect tinyint(1) unsigned NOT NULL default '0',
53 page_is_new tinyint(1) unsigned NOT NULL default '0',
54 page_random real unsigned NOT NULL,
55 page_touched char(14) binary NOT NULL default '',
57 -- Handy key to revision.rev_id of the current revision
58 page_latest int(8) unsigned NOT NULL,
59 page_len int(8) unsigned NOT NULL,
61 PRIMARY KEY page_id (page_id),
62 UNIQUE INDEX name_title (page_namespace,page_title),
64 -- Special-purpose indexes
69 CREATE TABLE /*$wgDBprefix*/revision (
70 rev_id int(8) unsigned NOT NULL auto_increment,
71 rev_page int(8) unsigned NOT NULL,
72 rev_text_id int(8) unsigned NOT NULL,
73 rev_comment tinyblob NOT NULL default '',
74 rev_user int(5) unsigned NOT NULL default '0',
75 rev_user_text varchar(255) binary NOT NULL default '',
76 rev_timestamp char(14) binary NOT NULL default '',
77 rev_minor_edit tinyint(1) unsigned NOT NULL default '0',
78 rev_deleted tinyint(1) unsigned NOT NULL default '0',
80 PRIMARY KEY rev_page_id (rev_page, rev_id),
81 UNIQUE INDEX rev_id (rev_id),
82 INDEX rev_timestamp (rev_timestamp),
83 INDEX page_timestamp (rev_page,rev_timestamp),
84 INDEX user_timestamp (rev_user,rev_timestamp),
85 INDEX usertext_timestamp (rev_user_text,rev_timestamp)
90 -- Holds text of individual page revisions.
92 CREATE TABLE /*$wgDBprefix*/text (
93 old_id int(8) unsigned NOT NULL auto_increment,
94 old_text mediumtext NOT NULL default '',
95 old_flags tinyblob NOT NULL default '',
97 PRIMARY KEY old_id (old_id)
100 CREATE TABLE /*$wgDBprefix*/archive (
101 ar_namespace tinyint(2) unsigned NOT NULL default '0',
102 ar_title varchar(255) binary NOT NULL default '',
103 ar_text mediumtext NOT NULL default '',
104 ar_comment tinyblob NOT NULL default '',
105 ar_user int(5) unsigned NOT NULL default '0',
106 ar_user_text varchar(255) binary NOT NULL,
107 ar_timestamp char(14) binary NOT NULL default '',
108 ar_minor_edit tinyint(1) NOT NULL default '0',
109 ar_flags tinyblob NOT NULL default '',
110 ar_rev_id int(8) unsigned,
112 KEY name_title_timestamp (ar_namespace,ar_title,ar_timestamp)
116 -- Track links that do exist
117 -- l_from and l_to key to cur_id
119 CREATE TABLE /*$wgDBprefix*/links (
120 l_from int(8) unsigned NOT NULL default '0',
121 l_to int(8) unsigned NOT NULL default '0',
122 UNIQUE KEY l_from(l_from,l_to),
127 -- Track links to pages that don't yet exist.
128 -- bl_from keys to cur_id
129 -- bl_to is a text link (namespace:title)
131 CREATE TABLE /*$wgDBprefix*/brokenlinks (
132 bl_from int(8) unsigned NOT NULL default '0',
133 bl_to varchar(255) binary NOT NULL default '',
134 UNIQUE KEY bl_from(bl_from,bl_to),
139 -- Track links to images *used inline*
140 -- il_from keys to cur_id, il_to keys to image_name.
141 -- We don't distinguish live from broken links.
143 CREATE TABLE /*$wgDBprefix*/imagelinks (
144 il_from int(8) unsigned NOT NULL default '0',
145 il_to varchar(255) binary NOT NULL default '',
146 UNIQUE KEY il_from(il_from,il_to),
151 -- Track category inclusions *used inline*
152 -- cl_from keys to cur_id, cl_to keys to cur_title of the category page.
153 -- cl_sortkey is the title of the linking page or an optional override
154 -- cl_timestamp marks when the link was last added
156 CREATE TABLE /*$wgDBprefix*/categorylinks (
157 cl_from int(8) unsigned NOT NULL default '0',
158 cl_to varchar(255) binary NOT NULL default '',
159 cl_sortkey varchar(255) binary NOT NULL default '',
160 cl_timestamp timestamp NOT NULL,
161 UNIQUE KEY cl_from(cl_from,cl_to),
162 KEY cl_sortkey(cl_to,cl_sortkey(128)),
163 KEY cl_timestamp(cl_to,cl_timestamp)
167 -- Stores (possibly gzipped) serialized objects with
168 -- cache arrays to reduce database load slurping up
169 -- from links and brokenlinks.
171 CREATE TABLE /*$wgDBprefix*/linkscc (
172 lcc_pageid INT UNSIGNED NOT NULL UNIQUE KEY,
173 lcc_cacheobj MEDIUMBLOB NOT NULL
176 CREATE TABLE /*$wgDBprefix*/site_stats (
177 ss_row_id int(8) unsigned NOT NULL,
178 ss_total_views bigint(20) unsigned default '0',
179 ss_total_edits bigint(20) unsigned default '0',
180 ss_good_articles bigint(20) unsigned default '0',
181 UNIQUE KEY ss_row_id (ss_row_id)
184 CREATE TABLE /*$wgDBprefix*/hitcounter (
185 hc_id INTEGER UNSIGNED NOT NULL
186 ) TYPE=HEAP MAX_ROWS=25000;
188 CREATE TABLE /*$wgDBprefix*/ipblocks (
189 ipb_id int(8) NOT NULL auto_increment,
190 ipb_address varchar(40) binary NOT NULL default '',
191 ipb_user int(8) unsigned NOT NULL default '0',
192 ipb_by int(8) unsigned NOT NULL default '0',
193 ipb_reason tinyblob NOT NULL default '',
194 ipb_timestamp char(14) binary NOT NULL default '',
195 ipb_auto tinyint(1) NOT NULL default '0',
196 ipb_expiry char(14) binary NOT NULL default '',
198 PRIMARY KEY ipb_id (ipb_id),
199 INDEX ipb_address (ipb_address),
200 INDEX ipb_user (ipb_user)
203 CREATE TABLE /*$wgDBprefix*/image (
204 img_name varchar(255) binary NOT NULL default '',
205 img_size int(8) unsigned NOT NULL default '0',
206 img_width int(5) NOT NULL default '0',
207 img_height int(5) NOT NULL default '0',
208 img_metadata mediumblob NOT NULL,
209 img_bits int(3) NOT NULL default '0',
210 img_type int(3) NOT NULL default '0',
211 img_description tinyblob NOT NULL default '',
212 img_user int(5) unsigned NOT NULL default '0',
213 img_user_text varchar(255) binary NOT NULL default '',
214 img_timestamp char(14) binary NOT NULL default '',
216 PRIMARY KEY img_name (img_name),
217 INDEX img_size (img_size),
218 INDEX img_timestamp (img_timestamp)
221 CREATE TABLE /*$wgDBprefix*/oldimage (
222 oi_name varchar(255) binary NOT NULL default '',
223 oi_archive_name varchar(255) binary NOT NULL default '',
224 oi_size int(8) unsigned NOT NULL default 0,
225 oi_width int(5) NOT NULL default 0,
226 oi_height int(5) NOT NULL default 0,
227 oi_bits int(3) NOT NULL default 0,
228 oi_type int(3) NOT NULL default 0,
229 oi_description tinyblob NOT NULL default '',
230 oi_user int(5) unsigned NOT NULL default '0',
231 oi_user_text varchar(255) binary NOT NULL default '',
232 oi_timestamp char(14) binary NOT NULL default '',
234 INDEX oi_name (oi_name(10))
237 CREATE TABLE /*$wgDBprefix*/recentchanges (
238 rc_id int(8) NOT NULL auto_increment,
239 rc_timestamp varchar(14) binary NOT NULL default '',
240 rc_cur_time varchar(14) binary NOT NULL default '',
241 rc_user int(10) unsigned NOT NULL default '0',
242 rc_user_text varchar(255) binary NOT NULL default '',
243 rc_namespace tinyint(3) NOT NULL default '0',
244 rc_title varchar(255) binary NOT NULL default '',
245 rc_comment varchar(255) binary NOT NULL default '',
246 rc_minor tinyint(3) unsigned NOT NULL default '0',
247 rc_bot tinyint(3) unsigned NOT NULL default '0',
248 rc_new tinyint(3) unsigned NOT NULL default '0',
249 rc_cur_id int(10) unsigned NOT NULL default '0',
250 rc_this_oldid int(10) unsigned NOT NULL default '0',
251 rc_last_oldid int(10) unsigned NOT NULL default '0',
252 rc_type tinyint(3) unsigned NOT NULL default '0',
253 rc_moved_to_ns tinyint(3) unsigned NOT NULL default '0',
254 rc_moved_to_title varchar(255) binary NOT NULL default '',
255 rc_patrolled tinyint(3) unsigned NOT NULL default '0',
256 rc_ip char(15) NOT NULL default '',
258 PRIMARY KEY rc_id (rc_id),
259 INDEX rc_timestamp (rc_timestamp),
260 INDEX rc_namespace_title (rc_namespace, rc_title),
261 INDEX rc_cur_id (rc_cur_id),
262 INDEX new_name_timestamp(rc_new,rc_namespace,rc_timestamp),
266 CREATE TABLE /*$wgDBprefix*/watchlist (
267 wl_user int(5) unsigned NOT NULL,
268 wl_namespace tinyint(2) unsigned NOT NULL default '0',
269 wl_title varchar(255) binary NOT NULL default '',
270 wl_notificationtimestamp varchar(14) binary NOT NULL default '0',
271 UNIQUE KEY (wl_user, wl_namespace, wl_title),
272 KEY namespace_title (wl_namespace,wl_title)
275 CREATE TABLE /*$wgDBprefix*/math (
276 math_inputhash varchar(16) NOT NULL,
277 math_outputhash varchar(16) NOT NULL,
278 math_html_conservativeness tinyint(1) NOT NULL,
281 UNIQUE KEY math_inputhash (math_inputhash)
285 -- Table searchindex must be MyISAM for fulltext support
287 CREATE TABLE /*$wgDBprefix*/searchindex (
288 si_page int(8) unsigned NOT NULL,
289 si_title varchar(255) NOT NULL default '',
290 si_text mediumtext NOT NULL default '',
291 UNIQUE KEY (si_page),
292 FULLTEXT si_title (si_title),
293 FULLTEXT si_text (si_text)
297 CREATE TABLE /*$wgDBprefix*/interwiki (
298 iw_prefix char(32) NOT NULL,
299 iw_url char(127) NOT NULL,
300 iw_local BOOL NOT NULL,
301 UNIQUE KEY iw_prefix (iw_prefix)
304 -- Used for caching expensive grouped queries
305 CREATE TABLE /*$wgDBprefix*/querycache (
306 qc_type char(32) NOT NULL,
307 qc_value int(5) unsigned NOT NULL default '0',
308 qc_namespace tinyint(2) unsigned NOT NULL default '0',
309 qc_title char(255) binary NOT NULL default '',
310 KEY (qc_type,qc_value)
313 -- For a few generic cache operations if not using Memcached
314 CREATE TABLE /*$wgDBprefix*/objectcache (
315 keyname char(255) binary not null default '',
318 unique key (keyname),
322 -- For storing revision text
323 CREATE TABLE /*$wgDBprefix*/blobs (
324 blob_index char(255) binary NOT NULL default '',
325 blob_data longblob NOT NULL default '',
326 UNIQUE key blob_index (blob_index)
329 -- For article validation
331 CREATE TABLE /*$wgDBprefix*/validate (
332 `val_user` int(11) NOT NULL default '0',
333 `val_page` int(11) unsigned NOT NULL default '0',
334 `val_revision` int(11) unsigned NOT NULL default '0',
335 `val_type` int(11) unsigned NOT NULL default '0',
336 `val_value` int(11) default '0',
337 `val_comment` varchar(255) NOT NULL default '',
338 KEY `val_user` (`val_user`,`val_revision`)
341 CREATE TABLE /*$wgDBprefix*/logging (
342 -- Symbolic keys for the general log type and the action type
343 -- within the log. The output format will be controlled by the
344 -- action field, but only the type controls categorization.
345 log_type char(10) NOT NULL default '',
346 log_action char(10) NOT NULL default '',
349 log_timestamp char(14) NOT NULL default '19700101000000',
351 -- The user who performed this action; key to user_id
352 log_user int unsigned NOT NULL default 0,
354 -- Key to the page affected. Where a user is the target,
355 -- this will point to the user page.
356 log_namespace tinyint unsigned NOT NULL default 0,
357 log_title varchar(255) binary NOT NULL default '',
359 -- Freeform text. Interpreted as edit history comments.
360 log_comment varchar(255) NOT NULL default '',
362 -- LF separated list of miscellaneous parameters
363 log_params blob NOT NULL default '',
365 KEY type_time (log_type, log_timestamp),
366 KEY user_time (log_user, log_timestamp),
367 KEY page_time (log_namespace, log_title, log_timestamp)
374 -- Hold group name and description
375 CREATE TABLE /*$wgDBprefix*/`group` (
376 group_id int(5) unsigned NOT NULL auto_increment,
377 group_name varchar(50) NOT NULL default '',
378 group_description varchar(255) NOT NULL default '',
379 group_rights tinyblob,
380 PRIMARY KEY (group_id)
383 -- Relation table between user and groups
384 CREATE TABLE /*$wgDBprefix*/user_groups (
385 ug_user int(5) unsigned NOT NULL default '0',
386 ug_group int(5) unsigned NOT NULL default '0',
387 PRIMARY KEY (ug_user,ug_group)