maintenance/mssql/tables.sql

   1 -- Experimental table definitions for Microsoft SQL Server with
   2 -- content-holding fields switched to explicit BINARY charset.
   3 -- ------------------------------------------------------------
   4
   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.
   8
   9 --
  10 -- General notes:
  11 --
  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.
  17 --
  18
  19
  20 --
  21 -- The user table contains basic account information,
  22 -- authentication keys, etc.
  23 --
  24 -- Some multi-wiki sites may share a single central user table
  25 -- between separate wikis using the $wgSharedDB setting.
  26 --
  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
  30 -- tables.
  31
  32 -- LINE:53
  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
  49 );
  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]);
  52 ;
  53
  54 --
  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.
  58 --
  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 '',
  64 );
  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);
  67
  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 '',
  75 );
  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);
  78
  79 --
  80 -- User preferences and other fun stuff
  81 -- replaces old user.user_options BLOB
  82 --
  83 CREATE TABLE /*$wgDBprefix*/user_properties (
  84         up_user INT NOT NULL,
  85         up_property NVARCHAR(32) NOT NULL,
  86         up_value NVARCHAR(MAX),
  87 );
  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);
  90
  91
  92 --
  93 -- Core of the wiki: each page has an entry here which identifies
  94 -- it by title and contains some essential metadata.
  95 --
  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,
 108 );
 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);
 112 ;
 113
 114 --
 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.
 118 --
 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,
 129    rev_len INT,
 130    rev_parent_id INT DEFAULT NULL,
 131
 132 );
 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);
 139 ;
 140
 141 --
 142 -- Holds TEXT of individual page revisions.
 143 --
 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,
 152 );
 153
 154 --
 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
 160 -- columns.
 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,
 171    ar_rev_id INT,
 172    ar_text_id INT,
 173    ar_deleted BIT NOT NULL DEFAULT 0,
 174    ar_len INT DEFAULT NULL,
 175    ar_page_id INT NULL,
 176    ar_parent_id INT NULL,
 177 );
 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);
 181
 182
 183 --
 184 -- Track page-to-page hyperlinks within the wiki.
 185 --
 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 '',
 190 );
 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);
 193
 194 --
 195 -- Track template inclusions.
 196 --
 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 '',
 201 );
 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);
 204
 205 --
 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.
 209 --
 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),
 214 );
 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);
 217
 218 --
 219 -- Track category inclusions *used inline*
 220 -- This tracks a single level of category membership
 221 -- (folksonomic tagging, really).
 222 --
 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),
 229 );
 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);
 233 -- Not really used?
 234 CREATE INDEX /*$wgDBprefix*/cl_timestamp ON /*$wgDBprefix*/categorylinks(cl_to,cl_timestamp);
 235 --;
 236
 237 --
 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.
 241 --
 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,
 249 );
 250
 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);
 254
 255
 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,
 262 );
 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);
 267
 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
 273 );
 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);
 277
 278 CREATE TABLE /*$wgDBprefix*/valid_tag (
 279   vt_tag varchar(255) NOT NULL PRIMARY KEY
 280 );
 281
 282 --
 283 -- Table for storing localisation data
 284 --
 285 CREATE TABLE /*$wgDBprefix*/l10n_cache (
 286         -- language code
 287         lc_lang NVARCHAR(32) NOT NULL,
 288
 289         -- cache key
 290         lc_key NVARCHAR(255) NOT NULL,
 291
 292         -- Value
 293         lc_value TEXT NOT NULL DEFAULT '',
 294 );
 295 CREATE INDEX /*$wgDBprefix*/lc_lang_key ON /*$wgDBprefix*/l10n_cache (lc_lang, lc_key);
 296
 297 --
 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,
 304 );
 305 -- Maximum key length ON SQL Server is 900 bytes
 306 CREATE INDEX /*$wgDBprefix*/externallinks_index   ON /*$wgDBprefix*/externallinks(el_index);
 307
 308 --
 309 -- Track external user accounts, if ExternalAuth is used
 310 --
 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,
 316 );
 317 CREATE UNIQUE INDEX /*$wgDBprefix*/eu_external_idx ON /*$wgDBprefix*/external_user(eu_external_id);
 318
 319 --
 320 -- Track INTerlanguage links
 321 --
 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),
 327 );
 328 CREATE UNIQUE INDEX /*$wgDBprefix*/langlinks_reverse_key ON /*$wgDBprefix*/langlinks(ll_lang,ll_title);
 329
 330 --
 331 -- Track inline interwiki links
 332 --
 333 CREATE TABLE /*$wgDBprefix*/iwlinks (
 334         -- page_id of the referring page
 335         iwl_from INT NOT NULL DEFAULT 0,
 336
 337         -- Interwiki prefix code of the target
 338         iwl_prefix NVARCHAR(20) NOT NULL DEFAULT '',
 339
 340         -- Title of the target, including namespace
 341         iwl_title NVARCHAR(255) NOT NULL DEFAULT '',
 342 );
 343
 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);
 346
 347
 348 --
 349 -- Contains a single row with some aggregate info
 350 -- ON the state of the site.
 351 --
 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,
 362 );
 363
 364 -- INSERT INTO site_stats DEFAULT VALUES;
 365
 366 --
 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.)
 372 --
 373 CREATE TABLE /*$wgDBprefix*/hitcounter (
 374    hc_id BIGINT NOT NULL
 375 );
 376
 377 --
 378 -- The Internet is full of jerks, alas. Sometimes it's handy
 379 -- to block a vandal or troll account.
 380 --
 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,
 399 );
 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);
 408 ;
 409
 410 --
 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 '',
 427 );
 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)
 433
 434 --
 435 -- Previous revisions of uploaded files.
 436 -- Awkwardly, image rows have to be moved into
 437 -- this table at re-upload time.
 438 --
 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 '',
 456 );
 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);
 461
 462 --
 463 -- Record of deleted file data
 464 --
 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 '',
 471    fa_deleted_user INT,
 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,
 487 );
 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);
 494 -- Pick by deleter
 495 CREATE INDEX /*$wgDBprefix*/filearchive_user ON /*$wgDBprefix*/filearchive(fa_deleted_user);
 496
 497 --
 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()
 501 --
 502 CREATE TABLE /*$wgDBprefix*/recentchanges (
 503    rc_id INT NOT NULL,
 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 '',
 529 );
 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);
 537 ;
 538
 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,
 544
 545 );
 546 CREATE UNIQUE INDEX /*$wgDBprefix*/namespace_title ON /*$wgDBprefix*/watchlist(wl_namespace,wl_title);
 547
 548 --
 549 -- Used by the math module to keep track
 550 -- of previously-rendered items.
 551 --
 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),
 558 );
 559
 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',
 566 );
 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
 571 ;
 572
 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
 579 );
 580 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
 581
 582 --
 583 -- Recognized INTerwiki link prefixes
 584 --
 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,
 592 );
 593
 594 --
 595 -- Used for caching expensive grouped queries
 596 --
 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)
 603 );
 604
 605 --
 606 -- For a few generic cache operations if not using Memcached
 607 --
 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
 612 );
 613 CREATE CLUSTERED INDEX /*$wgDBprefix*/[objectcache_time] ON /*$wgDBprefix*/objectcache(exptime);
 614 CREATE UNIQUE INDEX /*$wgDBprefix*/[objectcache_PK] ON /*wgDBprefix*/objectcache(keyname);
 615 --
 616 -- Cache of INTerwiki transclusion
 617 --
 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,
 622 );
 623
 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,
 637 );
 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);
 644
 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,
 650         -- Key to log_id
 651         ls_log_id INT NOT NULL default 0,
 652 );
 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);
 655
 656
 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,
 662    tb_ex    NVARCHAR(MAX),
 663    tb_name  NVARCHAR(255),
 664 );
 665 CREATE INDEX /*$wgDBprefix*/trackbacks_page ON /*$wgDBprefix*/trackbacks(tb_page);
 666
 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,
 674 );
 675 CREATE INDEX /*$wgDBprefix*/job_idx ON /*$wgDBprefix*/job(job_cmd,job_namespace,job_title);
 676
 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',
 681 );
 682
 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,
 690 );
 691 CREATE UNIQUE INDEX /*$wgDBprefix*/rd_ns_title ON /*$wgDBprefix*/redirect(rd_namespace,rd_title,rd_from);
 692
 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),
 702 );
 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);
 705
 706
 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,
 713    pr_user INT NULL,
 714    pr_expiry DATETIME NULL,
 715    pr_id INT UNIQUE IDENTITY,
 716    CONSTRAINT /*$wgDBprefix*/pr_pagetype PRIMARY KEY(pr_page,pr_type),
 717 );
 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);
 722 ;
 723
 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),
 734 );
 735 CREATE INDEX /*$wgDBprefix*/pt_timestamp   ON /*$wgDBprefix*/protected_titles(pt_timestamp);
 736 ;
 737
 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)
 744 );
 745
 746 -- A table to log updates, one text key row per update.
 747 CREATE TABLE /*$wgDBprefix*/updatelog (
 748   ul_key NVARCHAR(255) NOT NULL,
 749   PRIMARY KEY (ul_key)
 750 );
 751
 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' ;
 757
 758
 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'
 762 --sp_fulltext_table
 763 --sp_fulltext_column
 764 --sp_fulltext_table 'Articles', 'activate'