1 -- Experimental table definitions for Microsoft SQL Server with
2 -- content-holding fields switched to explicit BINARY charset.
3 -- ------------------------------------------------------------
5 -- SQL to create the initial tables for the MediaWiki database.
6 -- This is read and executed by the install script; you should
7 -- not have to run it by itself unless doing a manual install.
12 -- The comments in this and other files are
13 -- replaced with the defined table prefix by the installer
14 -- and updater scripts. If you are installing or running
15 -- updates manually, you will need to manually insert the
16 -- table prefix if any when running these scripts.
21 -- The user table contains basic account information,
22 -- authentication keys, etc.
24 -- Some multi-wiki sites may share a single central user table
25 -- between separate wikis using the $wgSharedDB setting.
27 -- Note that when a external authentication plugin is used,
28 -- user table entries still need to be created to store
29 -- preferences and to key tracking information in the other
33 CREATE TABLE /*$wgDBprefix*/user (
34 user_id INT NOT NULL PRIMARY KEY IDENTITY(0,1),
35 user_name NVARCHAR(255) NOT NULL UNIQUE DEFAULT '',
36 user_real_name NVARCHAR(255) NOT NULL DEFAULT '',
37 user_password NVARCHAR(255) NOT NULL DEFAULT '',
38 user_newpassword NVARCHAR(255) NOT NULL DEFAULT '',
39 user_newpass_time DATETIME NULL,
40 user_email NVARCHAR(255) NOT NULL DEFAULT '',
41 user_options NVARCHAR(MAX) NOT NULL DEFAULT '',
42 user_touched DATETIME NOT NULL DEFAULT GETDATE(),
43 user_token NCHAR(32) NOT NULL DEFAULT '',
44 user_email_authenticated DATETIME DEFAULT NULL,
45 user_email_token NCHAR(32) DEFAULT '',
46 user_email_token_expires DATETIME DEFAULT NULL,
47 user_registration DATETIME DEFAULT NULL,
48 user_editcount INT NULL
50 CREATE INDEX /*$wgDBprefix*/user_email_token ON /*$wgDBprefix*/[user](user_email_token);
51 CREATE UNIQUE INDEX /*$wgDBprefix*/[user_name] ON /*$wgDBprefix*/[user]([user_name]);
55 -- User permissions have been broken out to a separate table;
56 -- this allows sites with a shared user table to have different
57 -- permissions assigned to a user in each project.
59 -- This table replaces the old user_rights field which used a
60 -- comma-separated blob.
61 CREATE TABLE /*$wgDBprefix*/user_groups (
62 ug_user INT NOT NULL REFERENCES /*$wgDBprefix*/[user](user_id) ON DELETE CASCADE,
63 ug_group NVARCHAR(16) NOT NULL DEFAULT '',
65 CREATE UNIQUE clustered INDEX /*$wgDBprefix*/user_groups_unique ON /*$wgDBprefix*/user_groups(ug_user, ug_group);
66 CREATE INDEX /*$wgDBprefix*/user_group ON /*$wgDBprefix*/user_groups(ug_group);
68 -- Stores notifications of user talk page changes, for the display
69 -- of the "you have new messages" box
70 -- Changed user_id column to mwuser_id to avoid clashing with user_id function
71 CREATE TABLE /*$wgDBprefix*/user_newtalk (
72 user_id INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/[user](user_id) ON DELETE CASCADE,
73 user_ip NVARCHAR(40) NOT NULL DEFAULT '',
74 user_last_timestamp DATETIME NOT NULL DEFAULT '',
76 CREATE INDEX /*$wgDBprefix*/user_group_id ON /*$wgDBprefix*/user_newtalk([user_id]);
77 CREATE INDEX /*$wgDBprefix*/user_ip ON /*$wgDBprefix*/user_newtalk(user_ip);
80 -- User preferences and other fun stuff
81 -- replaces old user.user_options BLOB
83 CREATE TABLE /*$wgDBprefix*/user_properties (
85 up_property NVARCHAR(32) NOT NULL,
86 up_value NVARCHAR(MAX),
88 CREATE UNIQUE clustered INDEX /*$wgDBprefix*/user_props_user_prop ON /*$wgDBprefix*/user_properties(up_user, up_property);
89 CREATE INDEX /*$wgDBprefix*/user_props_prop ON /*$wgDBprefix*/user_properties(up_property);
93 -- Core of the wiki: each page has an entry here which identifies
94 -- it by title and contains some essential metadata.
96 CREATE TABLE /*$wgDBprefix*/page (
97 page_id INT NOT NULL PRIMARY KEY clustered IDENTITY,
98 page_namespace INT NOT NULL,
99 page_title NVARCHAR(255) NOT NULL,
100 page_restrictions NVARCHAR(255) NULL,
101 page_counter BIGINT NOT NULL DEFAULT 0,
102 page_is_redirect BIT NOT NULL DEFAULT 0,
103 page_is_new BIT NOT NULL DEFAULT 0,
104 page_random NUMERIC(15,14) NOT NULL DEFAULT RAND(),
105 page_touched DATETIME NOT NULL DEFAULT GETDATE(),
106 page_latest INT NOT NULL,
107 page_len INT NOT NULL,
109 CREATE UNIQUE INDEX /*$wgDBprefix*/page_unique_name ON /*$wgDBprefix*/page(page_namespace, page_title);
110 CREATE INDEX /*$wgDBprefix*/page_random_idx ON /*$wgDBprefix*/page(page_random);
111 CREATE INDEX /*$wgDBprefix*/page_len_idx ON /*$wgDBprefix*/page(page_len);
115 -- Every edit of a page creates also a revision row.
116 -- This stores metadata about the revision, and a reference
117 -- to the TEXT storage backend.
119 CREATE TABLE /*$wgDBprefix*/revision (
120 rev_id INT NOT NULL UNIQUE IDENTITY,
121 rev_page INT NOT NULL,
122 rev_text_id INT NOT NULL,
123 rev_comment NVARCHAR(max) NOT NULL,
124 rev_user INT NOT NULL DEFAULT 0 /*REFERENCES [user](user_id)*/,
125 rev_user_text NVARCHAR(255) NOT NULL DEFAULT '',
126 rev_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
127 rev_minor_edit BIT NOT NULL DEFAULT 0,
128 rev_deleted BIT NOT NULL DEFAULT 0,
130 rev_parent_id INT DEFAULT NULL,
133 CREATE UNIQUE clustered INDEX /*$wgDBprefix*/revision_unique ON /*$wgDBprefix*/revision(rev_page, rev_id);
134 CREATE UNIQUE INDEX /*$wgDBprefix*/rev_id ON /*$wgDBprefix*/revision(rev_id);
135 CREATE INDEX /*$wgDBprefix*/rev_timestamp ON /*$wgDBprefix*/revision(rev_timestamp);
136 CREATE INDEX /*$wgDBprefix*/page_timestamp ON /*$wgDBprefix*/revision(rev_page, rev_timestamp);
137 CREATE INDEX /*$wgDBprefix*/user_timestamp ON /*$wgDBprefix*/revision(rev_user, rev_timestamp);
138 CREATE INDEX /*$wgDBprefix*/usertext_timestamp ON /*$wgDBprefix*/revision(rev_user_text, rev_timestamp);
142 -- Holds TEXT of individual page revisions.
144 -- Field names are a holdover from the 'old' revisions table in
145 -- MediaWiki 1.4 and earlier: an upgrade will transform that
146 -- table INTo the 'text' table to minimize unnecessary churning
147 -- and downtime. If upgrading, the other fields will be left unused.
148 CREATE TABLE /*$wgDBprefix*/text (
149 old_id INT NOT NULL PRIMARY KEY clustered IDENTITY,
150 old_text TEXT NOT NULL,
151 old_flags NVARCHAR(255) NOT NULL,
155 -- Holding area for deleted articles, which may be viewed
156 -- or restored by admins through the Special:Undelete interface.
157 -- The fields generally correspond to the page, revision, and text
158 -- fields, with several caveats.
159 -- Cannot reasonably create views on this table, due to the presence of TEXT
161 CREATE TABLE /*$wgDBprefix*/archive (
162 ar_namespace SMALLINT NOT NULL DEFAULT 0,
163 ar_title NVARCHAR(255) NOT NULL DEFAULT '',
164 ar_text NVARCHAR(MAX) NOT NULL,
165 ar_comment NVARCHAR(255) NOT NULL,
166 ar_user INT NULL REFERENCES /*$wgDBprefix*/[user](user_id) ON DELETE SET NULL,
167 ar_user_text NVARCHAR(255) NOT NULL,
168 ar_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
169 ar_minor_edit BIT NOT NULL DEFAULT 0,
170 ar_flags NVARCHAR(255) NOT NULL,
173 ar_deleted BIT NOT NULL DEFAULT 0,
174 ar_len INT DEFAULT NULL,
176 ar_parent_id INT NULL,
178 CREATE INDEX /*$wgDBprefix*/ar_name_title_timestamp ON /*$wgDBprefix*/archive(ar_namespace,ar_title,ar_timestamp);
179 CREATE INDEX /*$wgDBprefix*/ar_usertext_timestamp ON /*$wgDBprefix*/archive(ar_user_text,ar_timestamp);
180 CREATE INDEX /*$wgDBprefix*/ar_user_text ON /*$wgDBprefix*/archive(ar_user_text);
184 -- Track page-to-page hyperlinks within the wiki.
186 CREATE TABLE /*$wgDBprefix*/pagelinks (
187 pl_from INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
188 pl_namespace SMALLINT NOT NULL DEFAULT 0,
189 pl_title NVARCHAR(255) NOT NULL DEFAULT '',
191 CREATE UNIQUE INDEX /*$wgDBprefix*/pl_from ON /*$wgDBprefix*/pagelinks(pl_from,pl_namespace,pl_title);
192 CREATE UNIQUE INDEX /*$wgDBprefix*/pl_namespace ON /*$wgDBprefix*/pagelinks(pl_namespace,pl_title,pl_from);
195 -- Track template inclusions.
197 CREATE TABLE /*$wgDBprefix*/templatelinks (
198 tl_from INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
199 tl_namespace SMALLINT NOT NULL DEFAULT 0,
200 tl_title NVARCHAR(255) NOT NULL DEFAULT '',
202 CREATE UNIQUE INDEX /*$wgDBprefix*/tl_from ON /*$wgDBprefix*/templatelinks(tl_from,tl_namespace,tl_title);
203 CREATE UNIQUE INDEX /*$wgDBprefix*/tl_namespace ON /*$wgDBprefix*/templatelinks(tl_namespace,tl_title,tl_from);
206 -- Track links to images *used inline*
207 -- We don't distinguish live from broken links here, so
208 -- they do not need to be changed ON upload/removal.
210 CREATE TABLE /*$wgDBprefix*/imagelinks (
211 il_from INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
212 il_to NVARCHAR(255) NOT NULL DEFAULT '',
213 CONSTRAINT /*$wgDBprefix*/il_from PRIMARY KEY(il_from,il_to),
215 CREATE UNIQUE INDEX /*$wgDBprefix*/il_from_to ON /*$wgDBprefix*/imagelinks(il_from,il_to);
216 CREATE UNIQUE INDEX /*$wgDBprefix*/il_to_from ON /*$wgDBprefix*/imagelinks(il_to,il_from);
219 -- Track category inclusions *used inline*
220 -- This tracks a single level of category membership
221 -- (folksonomic tagging, really).
223 CREATE TABLE /*$wgDBprefix*/categorylinks (
224 cl_from INT NOT NULL DEFAULT 0,
225 cl_to NVARCHAR(255) NOT NULL DEFAULT '',
226 cl_sortkey NVARCHAR(150) NOT NULL DEFAULT '',
227 cl_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
228 CONSTRAINT /*$wgDBprefix*/cl_from PRIMARY KEY(cl_from, cl_to),
230 CREATE UNIQUE INDEX /*$wgDBprefix*/cl_from_to ON /*$wgDBprefix*/categorylinks(cl_from,cl_to);
231 -- We always sort within a given category...
232 CREATE INDEX /*$wgDBprefix*/cl_sortkey ON /*$wgDBprefix*/categorylinks(cl_to,cl_sortkey);
234 CREATE INDEX /*$wgDBprefix*/cl_timestamp ON /*$wgDBprefix*/categorylinks(cl_to,cl_timestamp);
238 -- Track all existing categories. Something is a category if 1) it has an en-
239 -- try somewhere in categorylinks, or 2) it once did. Categories might not
240 -- have corresponding pages, so they need to be tracked separately.
242 CREATE TABLE /*$wgDBprefix*/category (
243 cat_id int NOT NULL IDENTITY(1,1),
244 cat_title nvarchar(255) NOT NULL,
245 cat_pages int NOT NULL default 0,
246 cat_subcats int NOT NULL default 0,
247 cat_files int NOT NULL default 0,
248 cat_hidden tinyint NOT NULL default 0,
251 CREATE UNIQUE INDEX /*$wgDBprefix*/cat_title ON /*$wgDBprefix*/category(cat_title);
252 -- For Special:Mostlinkedcategories
253 CREATE INDEX /*$wgDBprefix*/cat_pages ON /*$wgDBprefix*/category(cat_pages);
256 CREATE TABLE /*$wgDBprefix*/change_tag (
257 ct_rc_id int NOT NULL default 0,
258 ct_log_id int NOT NULL default 0,
259 ct_rev_id int NOT NULL default 0,
260 ct_tag varchar(255) NOT NULL,
261 ct_params varchar(255) NOT NULL,
263 CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_rc_tag ON /*$wgDBprefix*/change_tag(ct_rc_id,ct_tag);
264 CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_log_tag ON /*$wgDBprefix*/change_tag(ct_log_id,ct_tag);
265 CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_rev_tag ON /*$wgDBprefix*/change_tag(ct_rev_id,ct_tag);
266 CREATE INDEX /*$wgDBprefix*/change_tag_tag_id ON /*$wgDBprefix*/change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
268 CREATE TABLE /*$wgDBprefix*/tag_summary (
269 ts_rc_id INT NOT NULL default 0,
270 ts_log_id INT NOT NULL default 0,
271 ts_rev_id INT NOT NULL default 0,
272 ts_tags varchar(255) NOT NULL
274 CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_rc_id ON /*$wgDBprefix*/tag_summary(ts_rc_id);
275 CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_log_id ON /*$wgDBprefix*/tag_summary(ts_log_id);
276 CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_rev_id ON /*$wgDBprefix*/tag_summary(ts_rev_id);
278 CREATE TABLE /*$wgDBprefix*/valid_tag (
279 vt_tag varchar(255) NOT NULL PRIMARY KEY
283 -- Table for storing localisation data
285 CREATE TABLE /*$wgDBprefix*/l10n_cache (
287 lc_lang NVARCHAR(32) NOT NULL,
290 lc_key NVARCHAR(255) NOT NULL,
293 lc_value TEXT NOT NULL DEFAULT '',
295 CREATE INDEX /*$wgDBprefix*/lc_lang_key ON /*$wgDBprefix*/l10n_cache (lc_lang, lc_key);
298 -- Track links to external URLs
299 -- IE >= 4 supports no more than 2083 characters in a URL
300 CREATE TABLE /*$wgDBprefix*/externallinks (
301 el_from INT NOT NULL DEFAULT '0',
302 el_to VARCHAR(2083) NOT NULL,
303 el_index VARCHAR(896) NOT NULL,
305 -- Maximum key length ON SQL Server is 900 bytes
306 CREATE INDEX /*$wgDBprefix*/externallinks_index ON /*$wgDBprefix*/externallinks(el_index);
309 -- Track INTerlanguage links
311 CREATE TABLE /*$wgDBprefix*/langlinks (
312 ll_from INT NOT NULL DEFAULT 0,
313 ll_lang NVARCHAR(20) NOT NULL DEFAULT '',
314 ll_title NVARCHAR(255) NOT NULL DEFAULT '',
315 CONSTRAINT /*$wgDBprefix*/langlinks_pk PRIMARY KEY(ll_from, ll_lang),
317 CREATE UNIQUE INDEX /*$wgDBprefix*/langlinks_reverse_key ON /*$wgDBprefix*/langlinks(ll_lang,ll_title);
320 -- Track inline interwiki links
322 CREATE TABLE /*$wgDBprefix*/iwlinks (
323 -- page_id of the referring page
324 iwl_from INT NOT NULL DEFAULT 0,
326 -- Interwiki prefix code of the target
327 iwl_prefix NVARCHAR(20) NOT NULL DEFAULT '',
329 -- Title of the target, including namespace
330 iwl_title NVARCHAR(255) NOT NULL DEFAULT '',
333 CREATE UNIQUE INDEX /*$wgDBprefix*/iwl_from ON /*$wgDBprefix*/iwlinks(iwl_from,iwl_prefix,iwl_title);
334 CREATE UNIQUE INDEX /*$wgDBprefix*/iwl_prefix ON /*$wgDBprefix*/iwlinks(iwl_prefix,iwl_title);
338 -- Contains a single row with some aggregate info
339 -- ON the state of the site.
341 CREATE TABLE /*$wgDBprefix*/site_stats (
342 ss_row_id INT NOT NULL DEFAULT 1 PRIMARY KEY,
343 ss_total_views BIGINT DEFAULT 0,
344 ss_total_edits BIGINT DEFAULT 0,
345 ss_good_articles BIGINT DEFAULT 0,
346 ss_total_pages BIGINT DEFAULT -1,
347 ss_users BIGINT DEFAULT -1,
348 ss_active_users BIGINT DEFAULT -1,
349 ss_admins INT DEFAULT -1,
350 ss_images INT DEFAULT 0,
353 -- INSERT INTO site_stats DEFAULT VALUES;
356 -- Stores an ID for every time any article is visited;
357 -- depending ON $wgHitcounterUpdateFreq, it is
358 -- periodically cleared and the page_counter column
359 -- in the page table updated for the all articles
360 -- that have been visited.)
362 CREATE TABLE /*$wgDBprefix*/hitcounter (
363 hc_id BIGINT NOT NULL
367 -- The Internet is full of jerks, alas. Sometimes it's handy
368 -- to block a vandal or troll account.
370 CREATE TABLE /*$wgDBprefix*/ipblocks (
371 ipb_id INT NOT NULL PRIMARY KEY,
372 ipb_address NVARCHAR(255) NOT NULL,
373 ipb_user INT NOT NULL DEFAULT 0,
374 ipb_by INT NOT NULL DEFAULT 0,
375 ipb_by_text NVARCHAR(255) NOT NULL DEFAULT '',
376 ipb_reason NVARCHAR(255) NOT NULL,
377 ipb_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
378 ipb_auto BIT NOT NULL DEFAULT 0,
379 ipb_anon_only BIT NOT NULL DEFAULT 0,
380 ipb_create_account BIT NOT NULL DEFAULT 1,
381 ipb_enable_autoblock BIT NOT NULL DEFAULT 1,
382 ipb_expiry DATETIME NOT NULL DEFAULT GETDATE(),
383 ipb_range_start NVARCHAR(32) NOT NULL DEFAULT '',
384 ipb_range_end NVARCHAR(32) NOT NULL DEFAULT '',
385 ipb_deleted BIT NOT NULL DEFAULT 0,
386 ipb_block_email BIT NOT NULL DEFAULT 0,
387 ipb_allow_usertalk BIT NOT NULL DEFAULT 0,
388 ipb_parent_block_id INT DEFAULT NULL,
390 -- Unique index to support "user already blocked" messages
391 -- Any new options which prevent collisions should be included
392 --UNIQUE INDEX ipb_address (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only),
393 CREATE UNIQUE INDEX /*$wgDBprefix*/ipb_address ON /*$wgDBprefix*/ipblocks(ipb_address, ipb_user, ipb_auto, ipb_anon_only);
394 CREATE INDEX /*$wgDBprefix*/ipb_user ON /*$wgDBprefix*/ipblocks(ipb_user);
395 CREATE INDEX /*$wgDBprefix*/ipb_range ON /*$wgDBprefix*/ipblocks(ipb_range_start, ipb_range_end);
396 CREATE INDEX /*$wgDBprefix*/ipb_timestamp ON /*$wgDBprefix*/ipblocks(ipb_timestamp);
397 CREATE INDEX /*$wgDBprefix*/ipb_expiry ON /*$wgDBprefix*/ipblocks(ipb_expiry);
401 -- Uploaded images and other files.
402 CREATE TABLE /*$wgDBprefix*/image (
403 img_name varchar(255) NOT NULL default '',
404 img_size INT NOT NULL DEFAULT 0,
405 img_width INT NOT NULL DEFAULT 0,
406 img_height INT NOT NULL DEFAULT 0,
407 img_metadata TEXT NOT NULL, -- was MEDIUMBLOB
408 img_bits SMALLINT NOT NULL DEFAULT 0,
409 img_media_type NVARCHAR(MAX) DEFAULT 'UNKNOWN',
410 img_major_mime NVARCHAR(MAX) DEFAULT 'UNKNOWN',
411 img_minor_mime NVARCHAR(MAX) NOT NULL DEFAULT 'unknown',
412 img_description NVARCHAR(MAX) NOT NULL,
413 img_user INT NOT NULL DEFAULT 0,
414 img_user_text VARCHAR(255) NOT NULL DEFAULT '',
415 img_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
416 img_sha1 VARCHAR(255) NOT NULL default '',
418 -- Used by Special:Imagelist for sort-by-size
419 CREATE INDEX /*$wgDBprefix*/img_size ON /*$wgDBprefix*/[image](img_size);
420 -- Used by Special:Newimages and Special:Imagelist
421 CREATE INDEX /*$wgDBprefix*/img_timestamp ON /*$wgDBprefix*/[image](img_timestamp)
422 CREATE INDEX /*$wgDBprefix*/[img_sha1] ON /*wgDBprefix*/[image](img_sha1)
425 -- Previous revisions of uploaded files.
426 -- Awkwardly, image rows have to be moved into
427 -- this table at re-upload time.
429 CREATE TABLE /*$wgDBprefix*/oldimage (
430 oi_name VARCHAR(255) NOT NULL DEFAULT '',
431 oi_archive_name VARCHAR(255) NOT NULL DEFAULT '',
432 oi_size INT NOT NULL DEFAULT 0,
433 oi_width INT NOT NULL DEFAULT 0,
434 oi_height INT NOT NULL DEFAULT 0,
435 oi_bits SMALLINT NOT NULL DEFAULT 0,
436 oi_description NVARCHAR(MAX) NOT NULL,
437 oi_user INT NOT NULL DEFAULT 0,
438 oi_user_text VARCHAR(255) NOT NULL DEFAULT '',
439 oi_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
440 oi_metadata TEXT NOT NULL,
441 oi_media_type NVARCHAR(MAX) DEFAULT 'UNKNOWN',
442 oi_major_mime NVARCHAR(MAX) NOT NULL DEFAULT 'UNKNOWN',
443 oi_minor_mime NVARCHAR(MAX) NOT NULL DEFAULT 'unknown',
444 oi_deleted BIT NOT NULL default 0,
445 oi_sha1 VARCHAR(255) NOT NULL default '',
447 CREATE INDEX /*$wgDBprefix*/oi_usertext_timestamp ON /*$wgDBprefix*/oldimage(oi_user_text,oi_timestamp);
448 CREATE INDEX /*$wgDBprefix*/oi_name_timestamp ON /*$wgDBprefix*/oldimage(oi_name, oi_timestamp);
449 CREATE INDEX /*$wgDBprefix*/oi_name_archive_name ON /*$wgDBprefix*/oldimage(oi_name,oi_archive_name);
450 CREATE INDEX /*$wgDBprefix*/[oi_sha1] ON /*$wgDBprefix*/oldimage(oi_sha1);
453 -- Record of deleted file data
455 CREATE TABLE /*$wgDBprefix*/filearchive (
456 fa_id INT NOT NULL PRIMARY KEY,
457 fa_name NVARCHAR(255) NOT NULL DEFAULT '',
458 fa_archive_name NVARCHAR(255) DEFAULT '',
459 fa_storage_group NVARCHAR(16),
460 fa_storage_key NVARCHAR(64) DEFAULT '',
462 fa_deleted_timestamp NVARCHAR(14) DEFAULT NULL,
463 fa_deleted_reason NVARCHAR(255),
464 fa_size SMALLINT DEFAULT 0,
465 fa_width SMALLINT DEFAULT 0,
466 fa_height SMALLINT DEFAULT 0,
467 fa_metadata NVARCHAR(MAX), -- was mediumblob
468 fa_bits SMALLINT DEFAULT 0,
469 fa_media_type NVARCHAR(11) DEFAULT NULL,
470 fa_major_mime NVARCHAR(11) DEFAULT 'unknown',
471 fa_minor_mime NVARCHAR(32) DEFAULT 'unknown',
472 fa_description NVARCHAR(255),
473 fa_user INT DEFAULT 0,
474 fa_user_text NVARCHAR(255) DEFAULT '',
475 fa_timestamp DATETIME DEFAULT GETDATE(),
476 fa_deleted BIT NOT NULL DEFAULT 0,
478 -- Pick by image name
479 CREATE INDEX /*$wgDBprefix*/filearchive_name ON /*$wgDBprefix*/filearchive(fa_name,fa_timestamp);
480 -- Pick by dupe files
481 CREATE INDEX /*$wgDBprefix*/filearchive_dupe ON /*$wgDBprefix*/filearchive(fa_storage_group,fa_storage_key);
482 -- Pick by deletion time
483 CREATE INDEX /*$wgDBprefix*/filearchive_time ON /*$wgDBprefix*/filearchive(fa_deleted_timestamp);
485 CREATE INDEX /*$wgDBprefix*/filearchive_user ON /*$wgDBprefix*/filearchive(fa_deleted_user);
488 -- Primarily a summary table for Special:Recentchanges,
489 -- this table contains some additional info on edits from
490 -- the last few days, see Article::editUpdates()
492 CREATE TABLE /*$wgDBprefix*/recentchanges (
494 rc_timestamp DATETIME DEFAULT GETDATE(),
495 rc_cur_time DATETIME DEFAULT GETDATE(),
496 rc_user INT DEFAULT 0,
497 rc_user_text NVARCHAR(255) DEFAULT '',
498 rc_namespace SMALLINT DEFAULT 0,
499 rc_title NVARCHAR(255) DEFAULT '',
500 rc_comment NVARCHAR(255) DEFAULT '',
501 rc_minor BIT DEFAULT 0,
502 rc_bot BIT DEFAULT 0,
503 rc_new BIT DEFAULT 0,
504 rc_cur_id INT DEFAULT 0,
505 rc_this_oldid INT DEFAULT 0,
506 rc_last_oldid INT DEFAULT 0,
507 rc_type tinyint DEFAULT 0,
508 rc_patrolled BIT DEFAULT 0,
509 rc_ip NCHAR(40) DEFAULT '',
510 rc_old_len INT DEFAULT 0,
511 rc_new_len INT DEFAULT 0,
512 rc_deleted BIT DEFAULT 0,
513 rc_logid INT DEFAULT 0,
514 rc_log_type NVARCHAR(255) NULL DEFAULT NULL,
515 rc_log_action NVARCHAR(255) NULL DEFAULT NULL,
516 rc_params NVARCHAR(MAX) DEFAULT '',
518 CREATE INDEX /*$wgDBprefix*/rc_timestamp ON /*$wgDBprefix*/recentchanges(rc_timestamp);
519 CREATE INDEX /*$wgDBprefix*/rc_namespace_title ON /*$wgDBprefix*/recentchanges(rc_namespace, rc_title);
520 CREATE INDEX /*$wgDBprefix*/rc_cur_id ON /*$wgDBprefix*/recentchanges(rc_cur_id);
521 CREATE INDEX /*$wgDBprefix*/new_name_timestamp ON /*$wgDBprefix*/recentchanges(rc_new,rc_namespace,rc_timestamp);
522 CREATE INDEX /*$wgDBprefix*/rc_ip ON /*$wgDBprefix*/recentchanges(rc_ip);
523 CREATE INDEX /*$wgDBprefix*/rc_ns_usertext ON /*$wgDBprefix*/recentchanges(rc_namespace, rc_user_text);
524 CREATE INDEX /*$wgDBprefix*/rc_user_text ON /*$wgDBprefix*/recentchanges(rc_user_text, rc_timestamp);
527 CREATE TABLE /*$wgDBprefix*/watchlist (
528 wl_user INT NOT NULL,
529 wl_namespace SMALLINT NOT NULL DEFAULT 0,
530 wl_title NVARCHAR(255) NOT NULL DEFAULT '',
531 wl_notificationtimestamp NVARCHAR(14) DEFAULT NULL,
534 CREATE UNIQUE INDEX /*$wgDBprefix*/namespace_title ON /*$wgDBprefix*/watchlist(wl_namespace,wl_title);
536 -- Needs fulltext index.
537 CREATE TABLE /*$wgDBprefix*/searchindex (
538 si_page INT NOT NULL unique REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
539 si_title varbinary(max) NOT NULL,
540 si_text varbinary(max) NOT NULL,
541 si_ext CHAR(4) NOT NULL DEFAULT '.txt',
543 CREATE FULLTEXT CATALOG wikidb AS DEFAULT;
544 CREATE UNIQUE CLUSTERED INDEX searchindex_page ON searchindex (si_page);
545 CREATE FULLTEXT INDEX on searchindex (si_title TYPE COLUMN si_ext, si_text TYPE COLUMN si_ext)
546 KEY INDEX searchindex_page
549 -- This table is not used unless profiling is turned on
550 CREATE TABLE profiling (
551 pf_count INTEGER NOT NULL DEFAULT 0,
552 pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
553 pf_name NVARCHAR(200) NOT NULL,
554 pf_server NVARCHAR(200) NULL
556 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
559 -- Recognized INTerwiki link prefixes
561 CREATE TABLE /*$wgDBprefix*/interwiki (
562 iw_prefix NCHAR(32) NOT NULL PRIMARY KEY,
563 iw_url NCHAR(127) NOT NULL,
564 iw_api TEXT NOT NULL DEFAULT '',
565 iw_wikiid NVARCHAR(64) NOT NULL DEFAULT '',
566 iw_local BIT NOT NULL,
567 iw_trans BIT NOT NULL DEFAULT 0,
571 -- Used for caching expensive grouped queries
573 CREATE TABLE /*$wgDBprefix*/querycache (
574 qc_type NCHAR(32) NOT NULL,
575 qc_value INT NOT NULL DEFAULT '0',
576 qc_namespace SMALLINT NOT NULL DEFAULT 0,
577 qc_title NCHAR(255) NOT NULL DEFAULT '',
578 CONSTRAINT /*$wgDBprefix*/qc_pk PRIMARY KEY (qc_type,qc_value)
582 -- For a few generic cache operations if not using Memcached
584 CREATE TABLE /*$wgDBprefix*/objectcache (
585 keyname NCHAR(255) NOT NULL DEFAULT '',
586 [value] NVARCHAR(MAX), -- IMAGE,
587 exptime DATETIME, -- This is treated as a DATETIME
589 CREATE CLUSTERED INDEX /*$wgDBprefix*/[objectcache_time] ON /*$wgDBprefix*/objectcache(exptime);
590 CREATE UNIQUE INDEX /*$wgDBprefix*/[objectcache_PK] ON /*wgDBprefix*/objectcache(keyname);
592 -- Cache of INTerwiki transclusion
594 CREATE TABLE /*$wgDBprefix*/transcache (
595 tc_url NVARCHAR(255) NOT NULL PRIMARY KEY,
596 tc_contents NVARCHAR(MAX),
597 tc_time INT NOT NULL,
600 CREATE TABLE /*$wgDBprefix*/logging (
601 log_id INT PRIMARY KEY IDENTITY,
602 log_type NCHAR(10) NOT NULL DEFAULT '',
603 log_action NCHAR(10) NOT NULL DEFAULT '',
604 log_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
605 log_user INT NOT NULL DEFAULT 0,
606 log_user_text NVARCHAR(255) NOT NULL DEFAULT '',
607 log_namespace INT NOT NULL DEFAULT 0,
608 log_title NVARCHAR(255) NOT NULL DEFAULT '',
609 log_page INT NULL DEFAULT NULL,
610 log_comment NVARCHAR(255) NOT NULL DEFAULT '',
611 log_params NVARCHAR(MAX) NOT NULL,
612 log_deleted BIT NOT NULL DEFAULT 0,
614 CREATE INDEX /*$wgDBprefix*/type_time ON /*$wgDBprefix*/logging (log_type, log_timestamp);
615 CREATE INDEX /*$wgDBprefix*/user_time ON /*$wgDBprefix*/logging (log_user, log_timestamp);
616 CREATE INDEX /*$wgDBprefix*/page_time ON /*$wgDBprefix*/logging (log_namespace, log_title, log_timestamp);
617 CREATE INDEX /*$wgDBprefix*/times ON /*$wgDBprefix*/logging (log_timestamp);
618 CREATE INDEX /*$wgDBprefix*/log_user_type_time ON /*$wgDBprefix*/logging (log_user, log_type, log_timestamp);
619 CREATE INDEX /*$wgDBprefix*/log_page_id_time ON /*$wgDBprefix*/logging (log_page,log_timestamp);
621 CREATE TABLE /*$wgDBprefix*/log_search (
622 -- The type of ID (rev ID, log ID, rev timestamp, username)
623 ls_field NVARCHAR(32) NOT NULL,
624 -- The value of the ID
625 ls_value NVARCHAR(255) NOT NULL,
627 ls_log_id INT NOT NULL default 0,
629 CREATE UNIQUE INDEX /*$wgDBprefix*/ls_field_val ON /*$wgDBprefix*/log_search (ls_field,ls_value,ls_log_id);
630 CREATE INDEX /*$wgDBprefix*/ls_log_id ON /*$wgDBprefix*/log_search (ls_log_id);
633 -- Jobs performed by parallel apache threads or a command-line daemon
634 CREATE TABLE /*$wgDBprefix*/job (
635 job_id INT NOT NULL PRIMARY KEY,
636 job_cmd NVARCHAR(200) NOT NULL DEFAULT '',
637 job_namespace INT NOT NULL,
638 job_title NVARCHAR(200) NOT NULL,
639 job_params NVARCHAR(255) NOT NULL,
641 CREATE INDEX /*$wgDBprefix*/job_idx ON /*$wgDBprefix*/job(job_cmd,job_namespace,job_title);
643 -- Details of updates to cached special pages
644 CREATE TABLE /*$wgDBprefix*/querycache_info (
645 qci_type NVARCHAR(32) NOT NULL DEFAULT '' PRIMARY KEY,
646 qci_timestamp NVARCHAR(14) NOT NULL DEFAULT '19700101000000',
649 -- For each redirect, this table contains exactly one row defining its target
650 CREATE TABLE /*$wgDBprefix*/redirect (
651 rd_from INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/[page](page_id) ON DELETE CASCADE,
652 rd_namespace SMALLINT NOT NULL DEFAULT '0',
653 rd_title NVARCHAR(255) NOT NULL DEFAULT '',
654 rd_interwiki NVARCHAR(32) DEFAULT NULL,
655 rd_fragment NVARCHAR(255) DEFAULT NULL,
657 CREATE UNIQUE INDEX /*$wgDBprefix*/rd_ns_title ON /*$wgDBprefix*/redirect(rd_namespace,rd_title,rd_from);
659 -- Used for caching expensive grouped queries that need two links (for example double-redirects)
660 CREATE TABLE /*$wgDBprefix*/querycachetwo (
661 qcc_type NCHAR(32) NOT NULL,
662 qcc_value INT NOT NULL DEFAULT 0,
663 qcc_namespace INT NOT NULL DEFAULT 0,
664 qcc_title NCHAR(255) NOT NULL DEFAULT '',
665 qcc_namespacetwo INT NOT NULL DEFAULT 0,
666 qcc_titletwo NCHAR(255) NOT NULL DEFAULT '',
667 CONSTRAINT /*$wgDBprefix*/qcc_type PRIMARY KEY(qcc_type,qcc_value),
669 CREATE UNIQUE INDEX /*$wgDBprefix*/qcc_title ON /*$wgDBprefix*/querycachetwo(qcc_type,qcc_namespace,qcc_title);
670 CREATE UNIQUE INDEX /*$wgDBprefix*/qcc_titletwo ON /*$wgDBprefix*/querycachetwo(qcc_type,qcc_namespacetwo,qcc_titletwo);
673 --- Used for storing page restrictions (i.e. protection levels)
674 CREATE TABLE /*$wgDBprefix*/page_restrictions (
675 pr_page INT NOT NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
676 pr_type NVARCHAR(200) NOT NULL,
677 pr_level NVARCHAR(200) NOT NULL,
678 pr_cascade SMALLINT NOT NULL,
680 pr_expiry DATETIME NULL,
681 pr_id INT UNIQUE IDENTITY,
682 CONSTRAINT /*$wgDBprefix*/pr_pagetype PRIMARY KEY(pr_page,pr_type),
684 CREATE INDEX /*$wgDBprefix*/pr_page ON /*$wgDBprefix*/page_restrictions(pr_page);
685 CREATE INDEX /*$wgDBprefix*/pr_typelevel ON /*$wgDBprefix*/page_restrictions(pr_type,pr_level);
686 CREATE INDEX /*$wgDBprefix*/pr_pagelevel ON /*$wgDBprefix*/page_restrictions(pr_level);
687 CREATE INDEX /*$wgDBprefix*/pr_cascade ON /*$wgDBprefix*/page_restrictions(pr_cascade);
690 -- Protected titles - nonexistent pages that have been protected
691 CREATE TABLE /*$wgDBprefix*/protected_titles (
692 pt_namespace int NOT NULL,
693 pt_title NVARCHAR(255) NOT NULL,
694 pt_user int NOT NULL,
695 pt_reason NVARCHAR(3555),
696 pt_timestamp DATETIME NOT NULL,
697 pt_expiry DATETIME NOT NULL default '',
698 pt_create_perm NVARCHAR(60) NOT NULL,
699 PRIMARY KEY (pt_namespace,pt_title),
701 CREATE INDEX /*$wgDBprefix*/pt_timestamp ON /*$wgDBprefix*/protected_titles(pt_timestamp);
704 -- Name/value pairs indexed by page_id
705 CREATE TABLE /*$wgDBprefix*/page_props (
706 pp_page int NOT NULL,
707 pp_propname NVARCHAR(60) NOT NULL,
708 pp_value NVARCHAR(MAX) NOT NULL,
709 PRIMARY KEY (pp_page,pp_propname)
712 -- A table to log updates, one text key row per update.
713 CREATE TABLE /*$wgDBprefix*/updatelog (
714 ul_key NVARCHAR(255) NOT NULL,
718 -- NOTE To enable full text indexing on SQL 2008 you need to create an account FDH$MSSQLSERVER
719 -- AND assign a password for the FDHOST process to run under
720 -- Once you have assigned a password to that account, you need to run the following stored procedure
721 -- replacing XXXXX with the password you used.
722 -- EXEC sp_fulltext_resetfdhostaccount @username = 'FDH$MSSQLSERVER', @password = 'XXXXXX' ;
725 --- Add the full-text capabilities, depricated in SQL Server 2005, FTS is enabled on all user created tables by default unless you are using SQL Server 2005 Express
726 --sp_fulltext_database 'enable';
727 --sp_fulltext_catalog 'WikiCatalog', 'create'
730 --sp_fulltext_table 'Articles', 'activate'