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'