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_id
NOT NULL PRIMARY KEY clustered
IDENTITY,
163 ar_namespace
SMALLINT NOT NULL DEFAULT 0,
164 ar_title
NVARCHAR(255) NOT NULL DEFAULT '',
165 ar_text
NVARCHAR(MAX) NOT NULL,
166 ar_comment
NVARCHAR(255) NOT NULL,
167 ar_user
INT NULL REFERENCES /*$wgDBprefix*/[user](user_id
) ON DELETE SET NULL,
168 ar_user_text
NVARCHAR(255) NOT NULL,
169 ar_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
170 ar_minor_edit
BIT NOT NULL DEFAULT 0,
171 ar_flags
NVARCHAR(255) NOT NULL,
174 ar_deleted
BIT NOT NULL DEFAULT 0,
175 ar_len
INT DEFAULT NULL,
177 ar_parent_id
INT NULL,
179 CREATE INDEX /*$wgDBprefix*/ar_name_title_timestamp
ON /*$wgDBprefix*/archive(ar_namespace
,ar_title
,ar_timestamp
);
180 CREATE INDEX /*$wgDBprefix*/ar_usertext_timestamp
ON /*$wgDBprefix*/archive(ar_user_text
,ar_timestamp
);
181 CREATE INDEX /*$wgDBprefix*/ar_user_text
ON /*$wgDBprefix*/archive(ar_user_text
);
185 -- Track page-to-page hyperlinks within the wiki.
187 CREATE TABLE /*$wgDBprefix*/pagelinks (
188 pl_from
INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id
) ON DELETE CASCADE,
189 pl_namespace
SMALLINT NOT NULL DEFAULT 0,
190 pl_title
NVARCHAR(255) NOT NULL DEFAULT '',
192 CREATE UNIQUE INDEX /*$wgDBprefix*/pl_from
ON /*$wgDBprefix*/pagelinks(pl_from
,pl_namespace
,pl_title
);
193 CREATE UNIQUE INDEX /*$wgDBprefix*/pl_namespace
ON /*$wgDBprefix*/pagelinks(pl_namespace
,pl_title
,pl_from
);
196 -- Track template inclusions.
198 CREATE TABLE /*$wgDBprefix*/templatelinks (
199 tl_from
INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id
) ON DELETE CASCADE,
200 tl_namespace
SMALLINT NOT NULL DEFAULT 0,
201 tl_title
NVARCHAR(255) NOT NULL DEFAULT '',
203 CREATE UNIQUE INDEX /*$wgDBprefix*/tl_from
ON /*$wgDBprefix*/templatelinks(tl_from
,tl_namespace
,tl_title
);
204 CREATE UNIQUE INDEX /*$wgDBprefix*/tl_namespace
ON /*$wgDBprefix*/templatelinks(tl_namespace
,tl_title
,tl_from
);
207 -- Track links to images *used inline*
208 -- We don't distinguish live from broken links here, so
209 -- they do not need to be changed ON upload/removal.
211 CREATE TABLE /*$wgDBprefix*/imagelinks (
212 il_from
INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id
) ON DELETE CASCADE,
213 il_to
NVARCHAR(255) NOT NULL DEFAULT '',
214 CONSTRAINT /*$wgDBprefix*/il_from
PRIMARY KEY(il_from
,il_to
),
216 CREATE UNIQUE INDEX /*$wgDBprefix*/il_from_to
ON /*$wgDBprefix*/imagelinks(il_from
,il_to
);
217 CREATE UNIQUE INDEX /*$wgDBprefix*/il_to_from
ON /*$wgDBprefix*/imagelinks(il_to
,il_from
);
220 -- Track category inclusions *used inline*
221 -- This tracks a single level of category membership
222 -- (folksonomic tagging, really).
224 CREATE TABLE /*$wgDBprefix*/categorylinks (
225 cl_from
INT NOT NULL DEFAULT 0,
226 cl_to
NVARCHAR(255) NOT NULL DEFAULT '',
227 cl_sortkey
NVARCHAR(150) NOT NULL DEFAULT '',
228 cl_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
229 CONSTRAINT /*$wgDBprefix*/cl_from
PRIMARY KEY(cl_from
, cl_to
),
231 CREATE UNIQUE INDEX /*$wgDBprefix*/cl_from_to
ON /*$wgDBprefix*/categorylinks(cl_from
,cl_to
);
232 -- We always sort within a given category...
233 CREATE INDEX /*$wgDBprefix*/cl_sortkey
ON /*$wgDBprefix*/categorylinks(cl_to
,cl_sortkey
);
235 CREATE INDEX /*$wgDBprefix*/cl_timestamp
ON /*$wgDBprefix*/categorylinks(cl_to
,cl_timestamp
);
239 -- Track all existing categories. Something is a category if 1) it has an en-
240 -- try somewhere in categorylinks, or 2) it once did. Categories might not
241 -- have corresponding pages, so they need to be tracked separately.
243 CREATE TABLE /*$wgDBprefix*/category (
244 cat_id
int NOT NULL IDENTITY(1,1),
245 cat_title
nvarchar(255) NOT NULL,
246 cat_pages
int NOT NULL default 0,
247 cat_subcats
int NOT NULL default 0,
248 cat_files
int NOT NULL default 0,
249 cat_hidden tinyint
NOT NULL default 0,
252 CREATE UNIQUE INDEX /*$wgDBprefix*/cat_title
ON /*$wgDBprefix*/category(cat_title
);
253 -- For Special:Mostlinkedcategories
254 CREATE INDEX /*$wgDBprefix*/cat_pages
ON /*$wgDBprefix*/category(cat_pages
);
257 CREATE TABLE /*$wgDBprefix*/change_tag (
258 ct_rc_id
int NOT NULL default 0,
259 ct_log_id
int NOT NULL default 0,
260 ct_rev_id
int NOT NULL default 0,
261 ct_tag
varchar(255) NOT NULL,
262 ct_params
varchar(255) NOT NULL,
264 CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_rc_tag
ON /*$wgDBprefix*/change_tag(ct_rc_id
,ct_tag
);
265 CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_log_tag
ON /*$wgDBprefix*/change_tag(ct_log_id
,ct_tag
);
266 CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_rev_tag
ON /*$wgDBprefix*/change_tag(ct_rev_id
,ct_tag
);
267 CREATE INDEX /*$wgDBprefix*/change_tag_tag_id
ON /*$wgDBprefix*/change_tag(ct_tag
,ct_rc_id
,ct_rev_id
,ct_log_id
);
269 CREATE TABLE /*$wgDBprefix*/tag_summary (
270 ts_rc_id
INT NOT NULL default 0,
271 ts_log_id
INT NOT NULL default 0,
272 ts_rev_id
INT NOT NULL default 0,
273 ts_tags
varchar(255) NOT NULL
275 CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_rc_id
ON /*$wgDBprefix*/tag_summary(ts_rc_id
);
276 CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_log_id
ON /*$wgDBprefix*/tag_summary(ts_log_id
);
277 CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_rev_id
ON /*$wgDBprefix*/tag_summary(ts_rev_id
);
279 CREATE TABLE /*$wgDBprefix*/valid_tag (
280 vt_tag
varchar(255) NOT NULL PRIMARY KEY
284 -- Table for storing localisation data
286 CREATE TABLE /*$wgDBprefix*/l10n_cache (
288 lc_lang
NVARCHAR(32) NOT NULL,
291 lc_key
NVARCHAR(255) NOT NULL,
294 lc_value
TEXT NOT NULL DEFAULT '',
296 CREATE INDEX /*$wgDBprefix*/lc_lang_key
ON /*$wgDBprefix*/l10n_cache (lc_lang
, lc_key
);
299 -- Track links to external URLs
300 -- IE >= 4 supports no more than 2083 characters in a URL
301 CREATE TABLE /*$wgDBprefix*/externallinks (
302 el_id
INT NOT NULL PRIMARY KEY clustered
IDENTITY,
303 el_from
INT NOT NULL DEFAULT '0',
304 el_to
VARCHAR(2083) NOT NULL,
305 el_index
VARCHAR(896) NOT NULL,
307 -- Maximum key length ON SQL Server is 900 bytes
308 CREATE INDEX /*$wgDBprefix*/externallinks_index
ON /*$wgDBprefix*/externallinks(el_index
);
311 -- Track INTerlanguage links
313 CREATE TABLE /*$wgDBprefix*/langlinks (
314 ll_from
INT NOT NULL DEFAULT 0,
315 ll_lang
NVARCHAR(20) NOT NULL DEFAULT '',
316 ll_title
NVARCHAR(255) NOT NULL DEFAULT '',
317 CONSTRAINT /*$wgDBprefix*/langlinks_pk
PRIMARY KEY(ll_from
, ll_lang
),
319 CREATE UNIQUE INDEX /*$wgDBprefix*/langlinks_reverse_key
ON /*$wgDBprefix*/langlinks(ll_lang
,ll_title
);
322 -- Track inline interwiki links
324 CREATE TABLE /*$wgDBprefix*/iwlinks (
325 -- page_id of the referring page
326 iwl_from
INT NOT NULL DEFAULT 0,
328 -- Interwiki prefix code of the target
329 iwl_prefix
NVARCHAR(20) NOT NULL DEFAULT '',
331 -- Title of the target, including namespace
332 iwl_title
NVARCHAR(255) NOT NULL DEFAULT '',
335 CREATE UNIQUE INDEX /*$wgDBprefix*/iwl_from
ON /*$wgDBprefix*/iwlinks(iwl_from
,iwl_prefix
,iwl_title
);
336 CREATE UNIQUE INDEX /*$wgDBprefix*/iwl_prefix
ON /*$wgDBprefix*/iwlinks(iwl_prefix
,iwl_title
);
340 -- Contains a single row with some aggregate info
341 -- ON the state of the site.
343 CREATE TABLE /*$wgDBprefix*/site_stats (
344 ss_row_id
INT NOT NULL DEFAULT 1 PRIMARY KEY,
345 ss_total_views
BIGINT DEFAULT 0,
346 ss_total_edits
BIGINT DEFAULT 0,
347 ss_good_articles
BIGINT DEFAULT 0,
348 ss_total_pages
BIGINT DEFAULT -1,
349 ss_users
BIGINT DEFAULT -1,
350 ss_active_users
BIGINT DEFAULT -1,
351 ss_admins
INT DEFAULT -1,
352 ss_images
INT DEFAULT 0,
355 -- INSERT INTO site_stats DEFAULT VALUES;
358 -- Stores an ID for every time any article is visited;
359 -- depending ON $wgHitcounterUpdateFreq, it is
360 -- periodically cleared and the page_counter column
361 -- in the page table updated for the all articles
362 -- that have been visited.)
364 CREATE TABLE /*$wgDBprefix*/hitcounter (
365 hc_id
BIGINT NOT NULL
369 -- The Internet is full of jerks, alas. Sometimes it's handy
370 -- to block a vandal or troll account.
372 CREATE TABLE /*$wgDBprefix*/ipblocks (
373 ipb_id
INT NOT NULL PRIMARY KEY,
374 ipb_address
NVARCHAR(255) NOT NULL,
375 ipb_user
INT NOT NULL DEFAULT 0,
376 ipb_by
INT NOT NULL DEFAULT 0,
377 ipb_by_text
NVARCHAR(255) NOT NULL DEFAULT '',
378 ipb_reason
NVARCHAR(255) NOT NULL,
379 ipb_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
380 ipb_auto
BIT NOT NULL DEFAULT 0,
381 ipb_anon_only
BIT NOT NULL DEFAULT 0,
382 ipb_create_account
BIT NOT NULL DEFAULT 1,
383 ipb_enable_autoblock
BIT NOT NULL DEFAULT 1,
384 ipb_expiry DATETIME
NOT NULL DEFAULT GETDATE(),
385 ipb_range_start
NVARCHAR(32) NOT NULL DEFAULT '',
386 ipb_range_end
NVARCHAR(32) NOT NULL DEFAULT '',
387 ipb_deleted
BIT NOT NULL DEFAULT 0,
388 ipb_block_email
BIT NOT NULL DEFAULT 0,
389 ipb_allow_usertalk
BIT NOT NULL DEFAULT 0,
390 ipb_parent_block_id
INT DEFAULT NULL,
392 -- Unique index to support "user already blocked" messages
393 -- Any new options which prevent collisions should be included
394 --UNIQUE INDEX ipb_address (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only),
395 CREATE UNIQUE INDEX /*$wgDBprefix*/ipb_address
ON /*$wgDBprefix*/ipblocks(ipb_address
, ipb_user
, ipb_auto
, ipb_anon_only
);
396 CREATE INDEX /*$wgDBprefix*/ipb_user
ON /*$wgDBprefix*/ipblocks(ipb_user
);
397 CREATE INDEX /*$wgDBprefix*/ipb_range
ON /*$wgDBprefix*/ipblocks(ipb_range_start
, ipb_range_end
);
398 CREATE INDEX /*$wgDBprefix*/ipb_timestamp
ON /*$wgDBprefix*/ipblocks(ipb_timestamp
);
399 CREATE INDEX /*$wgDBprefix*/ipb_expiry
ON /*$wgDBprefix*/ipblocks(ipb_expiry
);
403 -- Uploaded images and other files.
404 CREATE TABLE /*$wgDBprefix*/image (
405 img_name
varchar(255) NOT NULL default '',
406 img_size
INT NOT NULL DEFAULT 0,
407 img_width
INT NOT NULL DEFAULT 0,
408 img_height
INT NOT NULL DEFAULT 0,
409 img_metadata
TEXT NOT NULL, -- was MEDIUMBLOB
410 img_bits
SMALLINT NOT NULL DEFAULT 0,
411 img_media_type
NVARCHAR(MAX) DEFAULT 'UNKNOWN',
412 img_major_mime
NVARCHAR(MAX) DEFAULT 'UNKNOWN',
413 img_minor_mime
NVARCHAR(MAX) NOT NULL DEFAULT 'unknown',
414 img_description
NVARCHAR(MAX) NOT NULL,
415 img_user
INT NOT NULL DEFAULT 0,
416 img_user_text
VARCHAR(255) NOT NULL DEFAULT '',
417 img_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
418 img_sha1
VARCHAR(255) NOT NULL default '',
420 -- Used by Special:Imagelist for sort-by-size
421 CREATE INDEX /*$wgDBprefix*/img_size
ON /*$wgDBprefix*/[image
](img_size
);
422 -- Used by Special:Newimages and Special:Imagelist
423 CREATE INDEX /*$wgDBprefix*/img_timestamp
ON /*$wgDBprefix*/[image
](img_timestamp
)
424 CREATE INDEX /*$wgDBprefix*/[img_sha1
] ON /*wgDBprefix*/[image
](img_sha1
)
427 -- Previous revisions of uploaded files.
428 -- Awkwardly, image rows have to be moved into
429 -- this table at re-upload time.
431 CREATE TABLE /*$wgDBprefix*/oldimage (
432 oi_name
VARCHAR(255) NOT NULL DEFAULT '',
433 oi_archive_name
VARCHAR(255) NOT NULL DEFAULT '',
434 oi_size
INT NOT NULL DEFAULT 0,
435 oi_width
INT NOT NULL DEFAULT 0,
436 oi_height
INT NOT NULL DEFAULT 0,
437 oi_bits
SMALLINT NOT NULL DEFAULT 0,
438 oi_description
NVARCHAR(MAX) NOT NULL,
439 oi_user
INT NOT NULL DEFAULT 0,
440 oi_user_text
VARCHAR(255) NOT NULL DEFAULT '',
441 oi_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
442 oi_metadata
TEXT NOT NULL,
443 oi_media_type
NVARCHAR(MAX) DEFAULT 'UNKNOWN',
444 oi_major_mime
NVARCHAR(MAX) NOT NULL DEFAULT 'UNKNOWN',
445 oi_minor_mime
NVARCHAR(MAX) NOT NULL DEFAULT 'unknown',
446 oi_deleted
BIT NOT NULL default 0,
447 oi_sha1
VARCHAR(255) NOT NULL default '',
449 CREATE INDEX /*$wgDBprefix*/oi_usertext_timestamp
ON /*$wgDBprefix*/oldimage(oi_user_text
,oi_timestamp
);
450 CREATE INDEX /*$wgDBprefix*/oi_name_timestamp
ON /*$wgDBprefix*/oldimage(oi_name
, oi_timestamp
);
451 CREATE INDEX /*$wgDBprefix*/oi_name_archive_name
ON /*$wgDBprefix*/oldimage(oi_name
,oi_archive_name
);
452 CREATE INDEX /*$wgDBprefix*/[oi_sha1
] ON /*$wgDBprefix*/oldimage(oi_sha1
);
455 -- Record of deleted file data
457 CREATE TABLE /*$wgDBprefix*/filearchive (
458 fa_id
INT NOT NULL PRIMARY KEY,
459 fa_name
NVARCHAR(255) NOT NULL DEFAULT '',
460 fa_archive_name
NVARCHAR(255) DEFAULT '',
461 fa_storage_group
NVARCHAR(16),
462 fa_storage_key
NVARCHAR(64) DEFAULT '',
464 fa_deleted_timestamp
NVARCHAR(14) DEFAULT NULL,
465 fa_deleted_reason
NVARCHAR(255),
466 fa_size
SMALLINT DEFAULT 0,
467 fa_width
SMALLINT DEFAULT 0,
468 fa_height
SMALLINT DEFAULT 0,
469 fa_metadata
NVARCHAR(MAX), -- was mediumblob
470 fa_bits
SMALLINT DEFAULT 0,
471 fa_media_type
NVARCHAR(11) DEFAULT NULL,
472 fa_major_mime
NVARCHAR(11) DEFAULT 'unknown',
473 fa_minor_mime
NVARCHAR(32) DEFAULT 'unknown',
474 fa_description
NVARCHAR(255),
475 fa_user
INT DEFAULT 0,
476 fa_user_text
NVARCHAR(255) DEFAULT '',
477 fa_timestamp DATETIME
DEFAULT GETDATE(),
478 fa_deleted
BIT NOT NULL DEFAULT 0,
480 -- Pick by image name
481 CREATE INDEX /*$wgDBprefix*/filearchive_name
ON /*$wgDBprefix*/filearchive(fa_name
,fa_timestamp
);
482 -- Pick by dupe files
483 CREATE INDEX /*$wgDBprefix*/filearchive_dupe
ON /*$wgDBprefix*/filearchive(fa_storage_group
,fa_storage_key
);
484 -- Pick by deletion time
485 CREATE INDEX /*$wgDBprefix*/filearchive_time
ON /*$wgDBprefix*/filearchive(fa_deleted_timestamp
);
487 CREATE INDEX /*$wgDBprefix*/filearchive_user
ON /*$wgDBprefix*/filearchive(fa_deleted_user
);
490 -- Primarily a summary table for Special:Recentchanges,
491 -- this table contains some additional info on edits from
492 -- the last few days, see Article::editUpdates()
494 CREATE TABLE /*$wgDBprefix*/recentchanges (
496 rc_timestamp DATETIME
DEFAULT GETDATE(),
497 rc_cur_time DATETIME
DEFAULT GETDATE(),
498 rc_user
INT DEFAULT 0,
499 rc_user_text
NVARCHAR(255) DEFAULT '',
500 rc_namespace
SMALLINT DEFAULT 0,
501 rc_title
NVARCHAR(255) DEFAULT '',
502 rc_comment
NVARCHAR(255) DEFAULT '',
503 rc_minor
BIT DEFAULT 0,
504 rc_bot
BIT DEFAULT 0,
505 rc_new
BIT DEFAULT 0,
506 rc_cur_id
INT DEFAULT 0,
507 rc_this_oldid
INT DEFAULT 0,
508 rc_last_oldid
INT DEFAULT 0,
509 rc_type tinyint
DEFAULT 0,
510 rc_patrolled
BIT DEFAULT 0,
511 rc_ip
NCHAR(40) DEFAULT '',
512 rc_old_len
INT DEFAULT 0,
513 rc_new_len
INT DEFAULT 0,
514 rc_deleted
BIT DEFAULT 0,
515 rc_logid
INT DEFAULT 0,
516 rc_log_type
NVARCHAR(255) NULL DEFAULT NULL,
517 rc_log_action
NVARCHAR(255) NULL DEFAULT NULL,
518 rc_params
NVARCHAR(MAX) DEFAULT '',
520 CREATE INDEX /*$wgDBprefix*/rc_timestamp
ON /*$wgDBprefix*/recentchanges(rc_timestamp
);
521 CREATE INDEX /*$wgDBprefix*/rc_namespace_title
ON /*$wgDBprefix*/recentchanges(rc_namespace
, rc_title
);
522 CREATE INDEX /*$wgDBprefix*/rc_cur_id
ON /*$wgDBprefix*/recentchanges(rc_cur_id
);
523 CREATE INDEX /*$wgDBprefix*/new_name_timestamp
ON /*$wgDBprefix*/recentchanges(rc_new
,rc_namespace
,rc_timestamp
);
524 CREATE INDEX /*$wgDBprefix*/rc_ip
ON /*$wgDBprefix*/recentchanges(rc_ip
);
525 CREATE INDEX /*$wgDBprefix*/rc_ns_usertext
ON /*$wgDBprefix*/recentchanges(rc_namespace
, rc_user_text
);
526 CREATE INDEX /*$wgDBprefix*/rc_user_text
ON /*$wgDBprefix*/recentchanges(rc_user_text
, rc_timestamp
);
529 CREATE TABLE /*$wgDBprefix*/watchlist (
530 wl_user
INT NOT NULL,
531 wl_namespace
SMALLINT NOT NULL DEFAULT 0,
532 wl_title
NVARCHAR(255) NOT NULL DEFAULT '',
533 wl_notificationtimestamp
NVARCHAR(14) DEFAULT NULL,
536 CREATE UNIQUE INDEX /*$wgDBprefix*/namespace_title
ON /*$wgDBprefix*/watchlist(wl_namespace
,wl_title
);
538 -- Needs fulltext index.
539 CREATE TABLE /*$wgDBprefix*/searchindex (
540 si_page
INT NOT NULL unique REFERENCES /*$wgDBprefix*/page(page_id
) ON DELETE CASCADE,
541 si_title
varbinary(max) NOT NULL,
542 si_text
varbinary(max) NOT NULL,
543 si_ext
CHAR(4) NOT NULL DEFAULT '.txt',
545 CREATE FULLTEXT
CATALOG wikidb
AS DEFAULT;
546 CREATE UNIQUE CLUSTERED
INDEX searchindex_page
ON searchindex (si_page
);
547 CREATE FULLTEXT
INDEX on searchindex (si_title
TYPE COLUMN si_ext
, si_text
TYPE COLUMN si_ext
)
548 KEY INDEX searchindex_page
551 -- This table is not used unless profiling is turned on
552 CREATE TABLE profiling (
553 pf_count
INTEGER NOT NULL DEFAULT 0,
554 pf_time
NUMERIC(18,10) NOT NULL DEFAULT 0,
555 pf_name
NVARCHAR(200) NOT NULL,
556 pf_server
NVARCHAR(200) NULL
558 CREATE UNIQUE INDEX pf_name_server
ON profiling (pf_name
, pf_server
);
561 -- Recognized INTerwiki link prefixes
563 CREATE TABLE /*$wgDBprefix*/interwiki (
564 iw_prefix
NCHAR(32) NOT NULL PRIMARY KEY,
565 iw_url
NCHAR(127) NOT NULL,
566 iw_api
TEXT NOT NULL DEFAULT '',
567 iw_wikiid
NVARCHAR(64) NOT NULL DEFAULT '',
568 iw_local
BIT NOT NULL,
569 iw_trans
BIT NOT NULL DEFAULT 0,
573 -- Used for caching expensive grouped queries
575 CREATE TABLE /*$wgDBprefix*/querycache (
576 qc_type
NCHAR(32) NOT NULL,
577 qc_value
INT NOT NULL DEFAULT '0',
578 qc_namespace
SMALLINT NOT NULL DEFAULT 0,
579 qc_title
NCHAR(255) NOT NULL DEFAULT '',
580 CONSTRAINT /*$wgDBprefix*/qc_pk
PRIMARY KEY (qc_type
,qc_value
)
584 -- For a few generic cache operations if not using Memcached
586 CREATE TABLE /*$wgDBprefix*/objectcache (
587 keyname
NCHAR(255) NOT NULL DEFAULT '',
588 [value] NVARCHAR(MAX), -- IMAGE,
589 exptime DATETIME
, -- This is treated as a DATETIME
591 CREATE CLUSTERED
INDEX /*$wgDBprefix*/[objectcache_time
] ON /*$wgDBprefix*/objectcache(exptime
);
592 CREATE UNIQUE INDEX /*$wgDBprefix*/[objectcache_PK
] ON /*wgDBprefix*/objectcache(keyname
);
594 -- Cache of INTerwiki transclusion
596 CREATE TABLE /*$wgDBprefix*/transcache (
597 tc_url
NVARCHAR(255) NOT NULL PRIMARY KEY,
598 tc_contents
NVARCHAR(MAX),
599 tc_time
INT NOT NULL,
602 CREATE TABLE /*$wgDBprefix*/logging (
603 log_id
INT PRIMARY KEY IDENTITY,
604 log_type
NCHAR(10) NOT NULL DEFAULT '',
605 log_action
NCHAR(10) NOT NULL DEFAULT '',
606 log_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
607 log_user
INT NOT NULL DEFAULT 0,
608 log_user_text
NVARCHAR(255) NOT NULL DEFAULT '',
609 log_namespace
INT NOT NULL DEFAULT 0,
610 log_title
NVARCHAR(255) NOT NULL DEFAULT '',
611 log_page
INT NULL DEFAULT NULL,
612 log_comment
NVARCHAR(255) NOT NULL DEFAULT '',
613 log_params
NVARCHAR(MAX) NOT NULL,
614 log_deleted
BIT NOT NULL DEFAULT 0,
616 CREATE INDEX /*$wgDBprefix*/type_time
ON /*$wgDBprefix*/logging (log_type
, log_timestamp
);
617 CREATE INDEX /*$wgDBprefix*/user_time
ON /*$wgDBprefix*/logging (log_user
, log_timestamp
);
618 CREATE INDEX /*$wgDBprefix*/page_time
ON /*$wgDBprefix*/logging (log_namespace
, log_title
, log_timestamp
);
619 CREATE INDEX /*$wgDBprefix*/times
ON /*$wgDBprefix*/logging (log_timestamp
);
620 CREATE INDEX /*$wgDBprefix*/log_user_type_time
ON /*$wgDBprefix*/logging (log_user
, log_type
, log_timestamp
);
621 CREATE INDEX /*$wgDBprefix*/log_page_id_time
ON /*$wgDBprefix*/logging (log_page
,log_timestamp
);
622 CREATE INDEX /*$wgDBprefix*/log_user_text_type_time
ON /*$wgDBprefix*/logging (log_user_text
, log_type
, log_timestamp
);
623 CREATE INDEX /*$wgDBprefix*/log_user_text_time
ON /*$wgDBprefix*/logging (log_user_text
, log_timestamp
);
625 CREATE TABLE /*$wgDBprefix*/log_search (
626 -- The type of ID (rev ID, log ID, rev timestamp, username)
627 ls_field
NVARCHAR(32) NOT NULL,
628 -- The value of the ID
629 ls_value
NVARCHAR(255) NOT NULL,
631 ls_log_id
INT NOT NULL default 0,
633 CREATE UNIQUE INDEX /*$wgDBprefix*/ls_field_val
ON /*$wgDBprefix*/log_search (ls_field
,ls_value
,ls_log_id
);
634 CREATE INDEX /*$wgDBprefix*/ls_log_id
ON /*$wgDBprefix*/log_search (ls_log_id
);
637 -- Jobs performed by parallel apache threads or a command-line daemon
638 CREATE TABLE /*$wgDBprefix*/job (
639 job_id
INT NOT NULL PRIMARY KEY,
640 job_cmd
NVARCHAR(200) NOT NULL DEFAULT '',
641 job_namespace
INT NOT NULL,
642 job_title
NVARCHAR(200) NOT NULL,
643 job_params
NVARCHAR(255) NOT NULL,
645 CREATE INDEX /*$wgDBprefix*/job_idx
ON /*$wgDBprefix*/job(job_cmd
,job_namespace
,job_title
);
647 -- Details of updates to cached special pages
648 CREATE TABLE /*$wgDBprefix*/querycache_info (
649 qci_type
NVARCHAR(32) NOT NULL DEFAULT '' PRIMARY KEY,
650 qci_timestamp
NVARCHAR(14) NOT NULL DEFAULT '19700101000000',
653 -- For each redirect, this table contains exactly one row defining its target
654 CREATE TABLE /*$wgDBprefix*/redirect (
655 rd_from
INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/[page
](page_id
) ON DELETE CASCADE,
656 rd_namespace
SMALLINT NOT NULL DEFAULT '0',
657 rd_title
NVARCHAR(255) NOT NULL DEFAULT '',
658 rd_interwiki
NVARCHAR(32) DEFAULT NULL,
659 rd_fragment
NVARCHAR(255) DEFAULT NULL,
661 CREATE UNIQUE INDEX /*$wgDBprefix*/rd_ns_title
ON /*$wgDBprefix*/redirect(rd_namespace
,rd_title
,rd_from
);
663 -- Used for caching expensive grouped queries that need two links (for example double-redirects)
664 CREATE TABLE /*$wgDBprefix*/querycachetwo (
665 qcc_type
NCHAR(32) NOT NULL,
666 qcc_value
INT NOT NULL DEFAULT 0,
667 qcc_namespace
INT NOT NULL DEFAULT 0,
668 qcc_title
NCHAR(255) NOT NULL DEFAULT '',
669 qcc_namespacetwo
INT NOT NULL DEFAULT 0,
670 qcc_titletwo
NCHAR(255) NOT NULL DEFAULT '',
671 CONSTRAINT /*$wgDBprefix*/qcc_type
PRIMARY KEY(qcc_type
,qcc_value
),
673 CREATE UNIQUE INDEX /*$wgDBprefix*/qcc_title
ON /*$wgDBprefix*/querycachetwo(qcc_type
,qcc_namespace
,qcc_title
);
674 CREATE UNIQUE INDEX /*$wgDBprefix*/qcc_titletwo
ON /*$wgDBprefix*/querycachetwo(qcc_type
,qcc_namespacetwo
,qcc_titletwo
);
677 --- Used for storing page restrictions (i.e. protection levels)
678 CREATE TABLE /*$wgDBprefix*/page_restrictions (
679 pr_id
INT UNIQUE IDENTITY,
680 pr_page
INT NOT NULL REFERENCES /*$wgDBprefix*/page(page_id
) ON DELETE CASCADE,
681 pr_type
NVARCHAR(200) NOT NULL,
682 pr_level
NVARCHAR(200) NOT NULL,
683 pr_cascade
SMALLINT NOT NULL,
685 pr_expiry DATETIME
NULL,
686 CONSTRAINT /*$wgDBprefix*/pr_pagetype
PRIMARY KEY(pr_page
,pr_type
),
688 CREATE INDEX /*$wgDBprefix*/pr_page
ON /*$wgDBprefix*/page_restrictions(pr_page
);
689 CREATE INDEX /*$wgDBprefix*/pr_typelevel
ON /*$wgDBprefix*/page_restrictions(pr_type
,pr_level
);
690 CREATE INDEX /*$wgDBprefix*/pr_pagelevel
ON /*$wgDBprefix*/page_restrictions(pr_level
);
691 CREATE INDEX /*$wgDBprefix*/pr_cascade
ON /*$wgDBprefix*/page_restrictions(pr_cascade
);
694 -- Protected titles - nonexistent pages that have been protected
695 CREATE TABLE /*$wgDBprefix*/protected_titles (
696 pt_namespace
int NOT NULL,
697 pt_title
NVARCHAR(255) NOT NULL,
698 pt_user
int NOT NULL,
699 pt_reason
NVARCHAR(3555),
700 pt_timestamp DATETIME
NOT NULL,
701 pt_expiry DATETIME
NOT NULL default '',
702 pt_create_perm
NVARCHAR(60) NOT NULL,
703 PRIMARY KEY (pt_namespace
,pt_title
),
705 CREATE INDEX /*$wgDBprefix*/pt_timestamp
ON /*$wgDBprefix*/protected_titles(pt_timestamp
);
708 -- Name/value pairs indexed by page_id
709 CREATE TABLE /*$wgDBprefix*/page_props (
710 pp_page
int NOT NULL,
711 pp_propname
NVARCHAR(60) NOT NULL,
712 pp_value
NVARCHAR(MAX) NOT NULL,
713 PRIMARY KEY (pp_page
,pp_propname
)
716 -- A table to log updates, one text key row per update.
717 CREATE TABLE /*$wgDBprefix*/updatelog (
718 ul_key
NVARCHAR(255) NOT NULL,
722 -- NOTE To enable full text indexing on SQL 2008 you need to create an account FDH$MSSQLSERVER
723 -- AND assign a password for the FDHOST process to run under
724 -- Once you have assigned a password to that account, you need to run the following stored procedure
725 -- replacing XXXXX with the password you used.
726 -- EXEC sp_fulltext_resetfdhostaccount @username = 'FDH$MSSQLSERVER', @password = 'XXXXXX' ;
729 --- 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
730 --sp_fulltext_database 'enable';
731 --sp_fulltext_catalog 'WikiCatalog', 'create'
734 --sp_fulltext_table 'Articles', 'activate'