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 external user accounts, if ExternalAuth is used
311 CREATE TABLE /*$wgDBprefix*/external_user (
312 -- Foreign key to user_id
313 eu_local_id
INT NOT NULL PRIMARY KEY,
314 -- opaque identifier provided by the external database
315 eu_external_id
NVARCHAR(255) NOT NULL,
317 CREATE UNIQUE INDEX /*$wgDBprefix*/eu_external_idx
ON /*$wgDBprefix*/external_user(eu_external_id
);
320 -- Track INTerlanguage links
322 CREATE TABLE /*$wgDBprefix*/langlinks (
323 ll_from
INT NOT NULL DEFAULT 0,
324 ll_lang
NVARCHAR(20) NOT NULL DEFAULT '',
325 ll_title
NVARCHAR(255) NOT NULL DEFAULT '',
326 CONSTRAINT /*$wgDBprefix*/langlinks_pk
PRIMARY KEY(ll_from
, ll_lang
),
328 CREATE UNIQUE INDEX /*$wgDBprefix*/langlinks_reverse_key
ON /*$wgDBprefix*/langlinks(ll_lang
,ll_title
);
331 -- Track inline interwiki links
333 CREATE TABLE /*$wgDBprefix*/iwlinks (
334 -- page_id of the referring page
335 iwl_from
INT NOT NULL DEFAULT 0,
337 -- Interwiki prefix code of the target
338 iwl_prefix
NVARCHAR(20) NOT NULL DEFAULT '',
340 -- Title of the target, including namespace
341 iwl_title
NVARCHAR(255) NOT NULL DEFAULT '',
344 CREATE UNIQUE INDEX /*$wgDBprefix*/iwl_from
ON /*$wgDBprefix*/iwlinks(iwl_from
,iwl_prefix
,iwl_title
);
345 CREATE UNIQUE INDEX /*$wgDBprefix*/iwl_prefix
ON /*$wgDBprefix*/iwlinks(iwl_prefix
,iwl_title
);
349 -- Contains a single row with some aggregate info
350 -- ON the state of the site.
352 CREATE TABLE /*$wgDBprefix*/site_stats (
353 ss_row_id
INT NOT NULL DEFAULT 1 PRIMARY KEY,
354 ss_total_views
BIGINT DEFAULT 0,
355 ss_total_edits
BIGINT DEFAULT 0,
356 ss_good_articles
BIGINT DEFAULT 0,
357 ss_total_pages
BIGINT DEFAULT -1,
358 ss_users
BIGINT DEFAULT -1,
359 ss_active_users
BIGINT DEFAULT -1,
360 ss_admins
INT DEFAULT -1,
361 ss_images
INT DEFAULT 0,
364 -- INSERT INTO site_stats DEFAULT VALUES;
367 -- Stores an ID for every time any article is visited;
368 -- depending ON $wgHitcounterUpdateFreq, it is
369 -- periodically cleared and the page_counter column
370 -- in the page table updated for the all articles
371 -- that have been visited.)
373 CREATE TABLE /*$wgDBprefix*/hitcounter (
374 hc_id
BIGINT NOT NULL
378 -- The Internet is full of jerks, alas. Sometimes it's handy
379 -- to block a vandal or troll account.
381 CREATE TABLE /*$wgDBprefix*/ipblocks (
382 ipb_id
INT NOT NULL PRIMARY KEY,
383 ipb_address
NVARCHAR(255) NOT NULL,
384 ipb_user
INT NOT NULL DEFAULT 0,
385 ipb_by
INT NOT NULL DEFAULT 0,
386 ipb_by_text
NVARCHAR(255) NOT NULL DEFAULT '',
387 ipb_reason
NVARCHAR(255) NOT NULL,
388 ipb_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
389 ipb_auto
BIT NOT NULL DEFAULT 0,
390 ipb_anon_only
BIT NOT NULL DEFAULT 0,
391 ipb_create_account
BIT NOT NULL DEFAULT 1,
392 ipb_enable_autoblock
BIT NOT NULL DEFAULT 1,
393 ipb_expiry DATETIME
NOT NULL DEFAULT GETDATE(),
394 ipb_range_start
NVARCHAR(32) NOT NULL DEFAULT '',
395 ipb_range_end
NVARCHAR(32) NOT NULL DEFAULT '',
396 ipb_deleted
BIT NOT NULL DEFAULT 0,
397 ipb_block_email
BIT NOT NULL DEFAULT 0,
398 ipb_allow_usertalk
BIT NOT NULL DEFAULT 0,
400 -- Unique index to support "user already blocked" messages
401 -- Any new options which prevent collisions should be included
402 --UNIQUE INDEX ipb_address (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only),
403 CREATE UNIQUE INDEX /*$wgDBprefix*/ipb_address
ON /*$wgDBprefix*/ipblocks(ipb_address
, ipb_user
, ipb_auto
, ipb_anon_only
);
404 CREATE INDEX /*$wgDBprefix*/ipb_user
ON /*$wgDBprefix*/ipblocks(ipb_user
);
405 CREATE INDEX /*$wgDBprefix*/ipb_range
ON /*$wgDBprefix*/ipblocks(ipb_range_start
, ipb_range_end
);
406 CREATE INDEX /*$wgDBprefix*/ipb_timestamp
ON /*$wgDBprefix*/ipblocks(ipb_timestamp
);
407 CREATE INDEX /*$wgDBprefix*/ipb_expiry
ON /*$wgDBprefix*/ipblocks(ipb_expiry
);
411 -- Uploaded images and other files.
412 CREATE TABLE /*$wgDBprefix*/image (
413 img_name
varchar(255) NOT NULL default '',
414 img_size
INT NOT NULL DEFAULT 0,
415 img_width
INT NOT NULL DEFAULT 0,
416 img_height
INT NOT NULL DEFAULT 0,
417 img_metadata
TEXT NOT NULL, -- was MEDIUMBLOB
418 img_bits
SMALLINT NOT NULL DEFAULT 0,
419 img_media_type
NVARCHAR(MAX) DEFAULT 'UNKNOWN',
420 img_major_mime
NVARCHAR(MAX) DEFAULT 'UNKNOWN',
421 img_minor_mime
NVARCHAR(MAX) NOT NULL DEFAULT 'unknown',
422 img_description
NVARCHAR(MAX) NOT NULL,
423 img_user
INT NOT NULL DEFAULT 0,
424 img_user_text
VARCHAR(255) NOT NULL DEFAULT '',
425 img_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
426 img_sha1
VARCHAR(255) NOT NULL default '',
428 -- Used by Special:Imagelist for sort-by-size
429 CREATE INDEX /*$wgDBprefix*/img_size
ON /*$wgDBprefix*/[image
](img_size
);
430 -- Used by Special:Newimages and Special:Imagelist
431 CREATE INDEX /*$wgDBprefix*/img_timestamp
ON /*$wgDBprefix*/[image
](img_timestamp
)
432 CREATE INDEX /*$wgDBprefix*/[img_sha1
] ON /*wgDBprefix*/[image
](img_sha1
)
435 -- Previous revisions of uploaded files.
436 -- Awkwardly, image rows have to be moved into
437 -- this table at re-upload time.
439 CREATE TABLE /*$wgDBprefix*/oldimage (
440 oi_name
VARCHAR(255) NOT NULL DEFAULT '',
441 oi_archive_name
VARCHAR(255) NOT NULL DEFAULT '',
442 oi_size
INT NOT NULL DEFAULT 0,
443 oi_width
INT NOT NULL DEFAULT 0,
444 oi_height
INT NOT NULL DEFAULT 0,
445 oi_bits
SMALLINT NOT NULL DEFAULT 0,
446 oi_description
NVARCHAR(MAX) NOT NULL,
447 oi_user
INT NOT NULL DEFAULT 0,
448 oi_user_text
VARCHAR(255) NOT NULL DEFAULT '',
449 oi_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
450 oi_metadata
TEXT NOT NULL,
451 oi_media_type
NVARCHAR(MAX) DEFAULT 'UNKNOWN',
452 oi_major_mime
NVARCHAR(MAX) NOT NULL DEFAULT 'UNKNOWN',
453 oi_minor_mime
NVARCHAR(MAX) NOT NULL DEFAULT 'unknown',
454 oi_deleted
BIT NOT NULL default 0,
455 oi_sha1
VARCHAR(255) NOT NULL default '',
457 CREATE INDEX /*$wgDBprefix*/oi_usertext_timestamp
ON /*$wgDBprefix*/oldimage(oi_user_text
,oi_timestamp
);
458 CREATE INDEX /*$wgDBprefix*/oi_name_timestamp
ON /*$wgDBprefix*/oldimage(oi_name
, oi_timestamp
);
459 CREATE INDEX /*$wgDBprefix*/oi_name_archive_name
ON /*$wgDBprefix*/oldimage(oi_name
,oi_archive_name
);
460 CREATE INDEX /*$wgDBprefix*/[oi_sha1
] ON /*$wgDBprefix*/oldimage(oi_sha1
);
463 -- Record of deleted file data
465 CREATE TABLE /*$wgDBprefix*/filearchive (
466 fa_id
INT NOT NULL PRIMARY KEY,
467 fa_name
NVARCHAR(255) NOT NULL DEFAULT '',
468 fa_archive_name
NVARCHAR(255) DEFAULT '',
469 fa_storage_group
NVARCHAR(16),
470 fa_storage_key
NVARCHAR(64) DEFAULT '',
472 fa_deleted_timestamp
NVARCHAR(14) DEFAULT NULL,
473 fa_deleted_reason
NVARCHAR(255),
474 fa_size
SMALLINT DEFAULT 0,
475 fa_width
SMALLINT DEFAULT 0,
476 fa_height
SMALLINT DEFAULT 0,
477 fa_metadata
NVARCHAR(MAX), -- was mediumblob
478 fa_bits
SMALLINT DEFAULT 0,
479 fa_media_type
NVARCHAR(11) DEFAULT NULL,
480 fa_major_mime
NVARCHAR(11) DEFAULT 'unknown',
481 fa_minor_mime
NVARCHAR(32) DEFAULT 'unknown',
482 fa_description
NVARCHAR(255),
483 fa_user
INT DEFAULT 0,
484 fa_user_text
NVARCHAR(255) DEFAULT '',
485 fa_timestamp DATETIME
DEFAULT GETDATE(),
486 fa_deleted
BIT NOT NULL DEFAULT 0,
488 -- Pick by image name
489 CREATE INDEX /*$wgDBprefix*/filearchive_name
ON /*$wgDBprefix*/filearchive(fa_name
,fa_timestamp
);
490 -- Pick by dupe files
491 CREATE INDEX /*$wgDBprefix*/filearchive_dupe
ON /*$wgDBprefix*/filearchive(fa_storage_group
,fa_storage_key
);
492 -- Pick by deletion time
493 CREATE INDEX /*$wgDBprefix*/filearchive_time
ON /*$wgDBprefix*/filearchive(fa_deleted_timestamp
);
495 CREATE INDEX /*$wgDBprefix*/filearchive_user
ON /*$wgDBprefix*/filearchive(fa_deleted_user
);
498 -- Primarily a summary table for Special:Recentchanges,
499 -- this table contains some additional info on edits from
500 -- the last few days, see Article::editUpdates()
502 CREATE TABLE /*$wgDBprefix*/recentchanges (
504 rc_timestamp DATETIME
DEFAULT GETDATE(),
505 rc_cur_time DATETIME
DEFAULT GETDATE(),
506 rc_user
INT DEFAULT 0,
507 rc_user_text
NVARCHAR(255) DEFAULT '',
508 rc_namespace
SMALLINT DEFAULT 0,
509 rc_title
NVARCHAR(255) DEFAULT '',
510 rc_comment
NVARCHAR(255) DEFAULT '',
511 rc_minor
BIT DEFAULT 0,
512 rc_bot
BIT DEFAULT 0,
513 rc_new
BIT DEFAULT 0,
514 rc_cur_id
INT DEFAULT 0,
515 rc_this_oldid
INT DEFAULT 0,
516 rc_last_oldid
INT DEFAULT 0,
517 rc_type tinyint
DEFAULT 0,
518 rc_moved_to_ns
BIT DEFAULT 0,
519 rc_moved_to_title
NVARCHAR(255) DEFAULT '',
520 rc_patrolled
BIT DEFAULT 0,
521 rc_ip
NCHAR(40) DEFAULT '',
522 rc_old_len
INT DEFAULT 0,
523 rc_new_len
INT DEFAULT 0,
524 rc_deleted
BIT DEFAULT 0,
525 rc_logid
INT DEFAULT 0,
526 rc_log_type
NVARCHAR(255) NULL DEFAULT NULL,
527 rc_log_action
NVARCHAR(255) NULL DEFAULT NULL,
528 rc_params
NVARCHAR(MAX) DEFAULT '',
530 CREATE INDEX /*$wgDBprefix*/rc_timestamp
ON /*$wgDBprefix*/recentchanges(rc_timestamp
);
531 CREATE INDEX /*$wgDBprefix*/rc_namespace_title
ON /*$wgDBprefix*/recentchanges(rc_namespace
, rc_title
);
532 CREATE INDEX /*$wgDBprefix*/rc_cur_id
ON /*$wgDBprefix*/recentchanges(rc_cur_id
);
533 CREATE INDEX /*$wgDBprefix*/new_name_timestamp
ON /*$wgDBprefix*/recentchanges(rc_new
,rc_namespace
,rc_timestamp
);
534 CREATE INDEX /*$wgDBprefix*/rc_ip
ON /*$wgDBprefix*/recentchanges(rc_ip
);
535 CREATE INDEX /*$wgDBprefix*/rc_ns_usertext
ON /*$wgDBprefix*/recentchanges(rc_namespace
, rc_user_text
);
536 CREATE INDEX /*$wgDBprefix*/rc_user_text
ON /*$wgDBprefix*/recentchanges(rc_user_text
, rc_timestamp
);
539 CREATE TABLE /*$wgDBprefix*/watchlist (
540 wl_user
INT NOT NULL,
541 wl_namespace
SMALLINT NOT NULL DEFAULT 0,
542 wl_title
NVARCHAR(255) NOT NULL DEFAULT '',
543 wl_notificationtimestamp
NVARCHAR(14) DEFAULT NULL,
546 CREATE UNIQUE INDEX /*$wgDBprefix*/namespace_title
ON /*$wgDBprefix*/watchlist(wl_namespace
,wl_title
);
549 -- Used by the math module to keep track
550 -- of previously-rendered items.
552 CREATE TABLE /*$wgDBprefix*/math (
553 math_inputhash
varbinary(16) NOT NULL PRIMARY KEY,
554 math_outputhash
varbinary(16) NOT NULL,
555 math_html_conservativeness tinyint
NOT NULL,
556 math_html
NVARCHAR(MAX),
557 math_mathml
NVARCHAR(MAX),
560 -- Needs fulltext index.
561 CREATE TABLE /*$wgDBprefix*/searchindex (
562 si_page
INT NOT NULL unique REFERENCES /*$wgDBprefix*/page(page_id
) ON DELETE CASCADE,
563 si_title
varbinary(max) NOT NULL,
564 si_text
varbinary(max) NOT NULL,
565 si_ext
CHAR(4) NOT NULL DEFAULT '.txt',
567 CREATE FULLTEXT
CATALOG wikidb
AS DEFAULT;
568 CREATE UNIQUE CLUSTERED
INDEX searchindex_page
ON searchindex (si_page
);
569 CREATE FULLTEXT
INDEX on searchindex (si_title
TYPE COLUMN si_ext
, si_text
TYPE COLUMN si_ext
)
570 KEY INDEX searchindex_page
573 -- This table is not used unless profiling is turned on
574 CREATE TABLE profiling (
575 pf_count
INTEGER NOT NULL DEFAULT 0,
576 pf_time
NUMERIC(18,10) NOT NULL DEFAULT 0,
577 pf_name
NVARCHAR(200) NOT NULL,
578 pf_server
NVARCHAR(200) NULL
580 CREATE UNIQUE INDEX pf_name_server
ON profiling (pf_name
, pf_server
);
583 -- Recognized INTerwiki link prefixes
585 CREATE TABLE /*$wgDBprefix*/interwiki (
586 iw_prefix
NCHAR(32) NOT NULL PRIMARY KEY,
587 iw_url
NCHAR(127) NOT NULL,
588 iw_api
TEXT NOT NULL DEFAULT '',
589 iw_wikiid
NVARCHAR(64) NOT NULL DEFAULT '',
590 iw_local
BIT NOT NULL,
591 iw_trans
BIT NOT NULL DEFAULT 0,
595 -- Used for caching expensive grouped queries
597 CREATE TABLE /*$wgDBprefix*/querycache (
598 qc_type
NCHAR(32) NOT NULL,
599 qc_value
INT NOT NULL DEFAULT '0',
600 qc_namespace
SMALLINT NOT NULL DEFAULT 0,
601 qc_title
NCHAR(255) NOT NULL DEFAULT '',
602 CONSTRAINT /*$wgDBprefix*/qc_pk
PRIMARY KEY (qc_type
,qc_value
)
606 -- For a few generic cache operations if not using Memcached
608 CREATE TABLE /*$wgDBprefix*/objectcache (
609 keyname
NCHAR(255) NOT NULL DEFAULT '',
610 [value] NVARCHAR(MAX), -- IMAGE,
611 exptime DATETIME
, -- This is treated as a DATETIME
613 CREATE CLUSTERED
INDEX /*$wgDBprefix*/[objectcache_time
] ON /*$wgDBprefix*/objectcache(exptime
);
614 CREATE UNIQUE INDEX /*$wgDBprefix*/[objectcache_PK
] ON /*wgDBprefix*/objectcache(keyname
);
616 -- Cache of INTerwiki transclusion
618 CREATE TABLE /*$wgDBprefix*/transcache (
619 tc_url
NVARCHAR(255) NOT NULL PRIMARY KEY,
620 tc_contents
NVARCHAR(MAX),
621 tc_time
INT NOT NULL,
624 CREATE TABLE /*$wgDBprefix*/logging (
625 log_id
INT PRIMARY KEY IDENTITY,
626 log_type
NCHAR(10) NOT NULL DEFAULT '',
627 log_action
NCHAR(10) NOT NULL DEFAULT '',
628 log_timestamp DATETIME
NOT NULL DEFAULT GETDATE(),
629 log_user
INT NOT NULL DEFAULT 0,
630 log_user_text
NVARCHAR(255) NOT NULL DEFAULT '',
631 log_namespace
INT NOT NULL DEFAULT 0,
632 log_title
NVARCHAR(255) NOT NULL DEFAULT '',
633 log_page
INT NULL DEFAULT NULL,
634 log_comment
NVARCHAR(255) NOT NULL DEFAULT '',
635 log_params
NVARCHAR(MAX) NOT NULL,
636 log_deleted
BIT NOT NULL DEFAULT 0,
638 CREATE INDEX /*$wgDBprefix*/type_time
ON /*$wgDBprefix*/logging (log_type
, log_timestamp
);
639 CREATE INDEX /*$wgDBprefix*/user_time
ON /*$wgDBprefix*/logging (log_user
, log_timestamp
);
640 CREATE INDEX /*$wgDBprefix*/page_time
ON /*$wgDBprefix*/logging (log_namespace
, log_title
, log_timestamp
);
641 CREATE INDEX /*$wgDBprefix*/times
ON /*$wgDBprefix*/logging (log_timestamp
);
642 CREATE INDEX /*$wgDBprefix*/log_user_type_time
ON /*$wgDBprefix*/logging (log_user
, log_type
, log_timestamp
);
643 CREATE INDEX /*$wgDBprefix*/log_page_id_time
ON /*$wgDBprefix*/logging (log_page
,log_timestamp
);
645 CREATE TABLE /*$wgDBprefix*/log_search (
646 -- The type of ID (rev ID, log ID, rev timestamp, username)
647 ls_field
NVARCHAR(32) NOT NULL,
648 -- The value of the ID
649 ls_value
NVARCHAR(255) NOT NULL,
651 ls_log_id
INT NOT NULL default 0,
653 CREATE UNIQUE INDEX /*$wgDBprefix*/ls_field_val
ON /*$wgDBprefix*/log_search (ls_field
,ls_value
,ls_log_id
);
654 CREATE INDEX /*$wgDBprefix*/ls_log_id
ON /*$wgDBprefix*/log_search (ls_log_id
);
657 CREATE TABLE /*$wgDBprefix*/trackbacks (
658 tb_id
INT PRIMARY KEY,
659 tb_page
INT REFERENCES /*$wgDBprefix*/page(page_id
) ON DELETE CASCADE,
660 tb_title
NVARCHAR(255) NOT NULL,
661 tb_url
NVARCHAR(255) NOT NULL,
663 tb_name
NVARCHAR(255),
665 CREATE INDEX /*$wgDBprefix*/trackbacks_page
ON /*$wgDBprefix*/trackbacks(tb_page
);
667 -- Jobs performed by parallel apache threads or a command-line daemon
668 CREATE TABLE /*$wgDBprefix*/job (
669 job_id
INT NOT NULL PRIMARY KEY,
670 job_cmd
NVARCHAR(200) NOT NULL DEFAULT '',
671 job_namespace
INT NOT NULL,
672 job_title
NVARCHAR(200) NOT NULL,
673 job_params
NVARCHAR(255) NOT NULL,
675 CREATE INDEX /*$wgDBprefix*/job_idx
ON /*$wgDBprefix*/job(job_cmd
,job_namespace
,job_title
);
677 -- Details of updates to cached special pages
678 CREATE TABLE /*$wgDBprefix*/querycache_info (
679 qci_type
NVARCHAR(32) NOT NULL DEFAULT '' PRIMARY KEY,
680 qci_timestamp
NVARCHAR(14) NOT NULL DEFAULT '19700101000000',
683 -- For each redirect, this table contains exactly one row defining its target
684 CREATE TABLE /*$wgDBprefix*/redirect (
685 rd_from
INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/[page
](page_id
) ON DELETE CASCADE,
686 rd_namespace
SMALLINT NOT NULL DEFAULT '0',
687 rd_title
NVARCHAR(255) NOT NULL DEFAULT '',
688 rd_interwiki
NVARCHAR(32) DEFAULT NULL,
689 rd_fragment
NVARCHAR(255) DEFAULT NULL,
691 CREATE UNIQUE INDEX /*$wgDBprefix*/rd_ns_title
ON /*$wgDBprefix*/redirect(rd_namespace
,rd_title
,rd_from
);
693 -- Used for caching expensive grouped queries that need two links (for example double-redirects)
694 CREATE TABLE /*$wgDBprefix*/querycachetwo (
695 qcc_type
NCHAR(32) NOT NULL,
696 qcc_value
INT NOT NULL DEFAULT 0,
697 qcc_namespace
INT NOT NULL DEFAULT 0,
698 qcc_title
NCHAR(255) NOT NULL DEFAULT '',
699 qcc_namespacetwo
INT NOT NULL DEFAULT 0,
700 qcc_titletwo
NCHAR(255) NOT NULL DEFAULT '',
701 CONSTRAINT /*$wgDBprefix*/qcc_type
PRIMARY KEY(qcc_type
,qcc_value
),
703 CREATE UNIQUE INDEX /*$wgDBprefix*/qcc_title
ON /*$wgDBprefix*/querycachetwo(qcc_type
,qcc_namespace
,qcc_title
);
704 CREATE UNIQUE INDEX /*$wgDBprefix*/qcc_titletwo
ON /*$wgDBprefix*/querycachetwo(qcc_type
,qcc_namespacetwo
,qcc_titletwo
);
707 --- Used for storing page restrictions (i.e. protection levels)
708 CREATE TABLE /*$wgDBprefix*/page_restrictions (
709 pr_page
INT NOT NULL REFERENCES /*$wgDBprefix*/page(page_id
) ON DELETE CASCADE,
710 pr_type
NVARCHAR(200) NOT NULL,
711 pr_level
NVARCHAR(200) NOT NULL,
712 pr_cascade
SMALLINT NOT NULL,
714 pr_expiry DATETIME
NULL,
715 pr_id
INT UNIQUE IDENTITY,
716 CONSTRAINT /*$wgDBprefix*/pr_pagetype
PRIMARY KEY(pr_page
,pr_type
),
718 CREATE INDEX /*$wgDBprefix*/pr_page
ON /*$wgDBprefix*/page_restrictions(pr_page
);
719 CREATE INDEX /*$wgDBprefix*/pr_typelevel
ON /*$wgDBprefix*/page_restrictions(pr_type
,pr_level
);
720 CREATE INDEX /*$wgDBprefix*/pr_pagelevel
ON /*$wgDBprefix*/page_restrictions(pr_level
);
721 CREATE INDEX /*$wgDBprefix*/pr_cascade
ON /*$wgDBprefix*/page_restrictions(pr_cascade
);
724 -- Protected titles - nonexistent pages that have been protected
725 CREATE TABLE /*$wgDBprefix*/protected_titles (
726 pt_namespace
int NOT NULL,
727 pt_title
NVARCHAR(255) NOT NULL,
728 pt_user
int NOT NULL,
729 pt_reason
NVARCHAR(3555),
730 pt_timestamp DATETIME
NOT NULL,
731 pt_expiry DATETIME
NOT NULL default '',
732 pt_create_perm
NVARCHAR(60) NOT NULL,
733 PRIMARY KEY (pt_namespace
,pt_title
),
735 CREATE INDEX /*$wgDBprefix*/pt_timestamp
ON /*$wgDBprefix*/protected_titles(pt_timestamp
);
738 -- Name/value pairs indexed by page_id
739 CREATE TABLE /*$wgDBprefix*/page_props (
740 pp_page
int NOT NULL,
741 pp_propname
NVARCHAR(60) NOT NULL,
742 pp_value
NVARCHAR(MAX) NOT NULL,
743 PRIMARY KEY (pp_page
,pp_propname
)
746 -- A table to log updates, one text key row per update.
747 CREATE TABLE /*$wgDBprefix*/updatelog (
748 ul_key
NVARCHAR(255) NOT NULL,
752 -- NOTE To enable full text indexing on SQL 2008 you need to create an account FDH$MSSQLSERVER
753 -- AND assign a password for the FDHOST process to run under
754 -- Once you have assigned a password to that account, you need to run the following stored procedure
755 -- replacing XXXXX with the password you used.
756 -- EXEC sp_fulltext_resetfdhostaccount @username = 'FDH$MSSQLSERVER', @password = 'XXXXXX' ;
759 --- 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
760 --sp_fulltext_database 'enable';
761 --sp_fulltext_catalog 'WikiCatalog', 'create'
764 --sp_fulltext_table 'Articles', 'activate'