[Summary view] [Print] [Text view]

   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 /*_*/mwuser (
  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 varchar(14) NULL DEFAULT NULL,
  40     user_email        NVARCHAR(255)  NOT NULL DEFAULT '',
  41     user_options      NVARCHAR(MAX) NOT NULL DEFAULT '',
  42     user_touched      varchar(14)      NOT NULL DEFAULT '',
  43     user_token        NCHAR(32)      NOT NULL DEFAULT '',
  44     user_email_authenticated varchar(14) DEFAULT NULL,
  45     user_email_token  NCHAR(32) DEFAULT '',
  46     user_email_token_expires varchar(14) DEFAULT NULL,
  47     user_registration varchar(14) DEFAULT NULL,
  48     user_editcount    INT NULL DEFAULT NULL,
  49     user_password_expires varchar(14) DEFAULT NULL
  50  );
  51  CREATE UNIQUE INDEX /*i*/user_name ON /*_*/mwuser (user_name);
  52  CREATE INDEX /*i*/user_email_token ON /*_*/mwuser (user_email_token);
  53  CREATE INDEX /*i*/user_email ON /*_*/mwuser (user_email);
  54  
  55  -- Insert a dummy user to represent anons
  56  INSERT INTO /*_*/mwuser (user_name) VALUES ('##Anonymous##');
  57  
  58  --
  59  -- User permissions have been broken out to a separate table;
  60  -- this allows sites with a shared user table to have different
  61  -- permissions assigned to a user in each project.
  62  --
  63  -- This table replaces the old user_rights field which used a
  64  -- comma-separated nvarchar(max).
  65  CREATE TABLE /*_*/user_groups (
  66     ug_user  INT     NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
  67     ug_group NVARCHAR(255) NOT NULL DEFAULT '',
  68  );
  69  CREATE UNIQUE clustered INDEX /*i*/ug_user_group ON /*_*/user_groups (ug_user, ug_group);
  70  CREATE INDEX /*i*/ug_group ON /*_*/user_groups(ug_group);
  71  
  72  -- Stores the groups the user has once belonged to.
  73  -- The user may still belong to these groups (check user_groups).
  74  -- Users are not autopromoted to groups from which they were removed.
  75  CREATE TABLE /*_*/user_former_groups (
  76    ufg_user INT NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
  77    ufg_group nvarchar(255) NOT NULL default ''
  78  );
  79  CREATE UNIQUE INDEX /*i*/ufg_user_group ON /*_*/user_former_groups (ufg_user,ufg_group);
  80  
  81  -- Stores notifications of user talk page changes, for the display
  82  -- of the "you have new messages" box
  83  -- Changed user_id column to user_id to avoid clashing with user_id function
  84  CREATE TABLE /*_*/user_newtalk (
  85     user_id INT         NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
  86     user_ip NVARCHAR(40) NOT NULL DEFAULT '',
  87     user_last_timestamp varchar(14) DEFAULT NULL,
  88  );
  89  CREATE INDEX /*i*/un_user_id ON /*_*/user_newtalk (user_id);
  90  CREATE INDEX /*i*/un_user_ip ON /*_*/user_newtalk (user_ip);
  91  
  92  --
  93  -- User preferences and other fun stuff
  94  -- replaces old user.user_options nvarchar(max)
  95  --
  96  CREATE TABLE /*_*/user_properties (
  97      up_user INT NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
  98      up_property NVARCHAR(255) NOT NULL,
  99      up_value NVARCHAR(MAX),
 100  );
 101  CREATE UNIQUE CLUSTERED INDEX /*i*/user_properties_user_property ON /*_*/user_properties (up_user,up_property);
 102  CREATE INDEX /*i*/user_properties_property ON /*_*/user_properties (up_property);
 103  
 104  
 105  --
 106  -- Core of the wiki: each page has an entry here which identifies
 107  -- it by title and contains some essential metadata.
 108  --
 109  CREATE TABLE /*_*/page (
 110     page_id        INT          NOT NULL  PRIMARY KEY IDENTITY(0,1),
 111     page_namespace INT          NOT NULL,
 112     page_title     NVARCHAR(255)  NOT NULL,
 113     page_restrictions NVARCHAR(255) NOT NULL,
 114     page_counter BIGINT            NOT NULL DEFAULT 0,
 115     page_is_redirect BIT           NOT NULL DEFAULT 0,
 116     page_is_new BIT                NOT NULL DEFAULT 0,
 117     page_random real     NOT NULL DEFAULT RAND(),
 118     page_touched varchar(14) NOT NULL default '',
 119     page_links_updated varchar(14) DEFAULT NULL,
 120     page_latest INT, -- FK inserted later
 121     page_len INT NOT NULL,
 122     page_content_model nvarchar(32) default null,
 123     page_lang VARBINARY(35) DEFAULT NULL
 124  );
 125  CREATE UNIQUE INDEX /*i*/name_title ON /*_*/page (page_namespace,page_title);
 126  CREATE INDEX /*i*/page_random ON /*_*/page (page_random);
 127  CREATE INDEX /*i*/page_len ON /*_*/page (page_len);
 128  CREATE INDEX /*i*/page_redirect_namespace_len ON /*_*/page (page_is_redirect, page_namespace, page_len);
 129  
 130  -- insert a dummy page
 131  INSERT INTO /*_*/page (page_namespace, page_title, page_restrictions, page_latest, page_len) VALUES (-1,'','',0,0);
 132  
 133  --
 134  -- Every edit of a page creates also a revision row.
 135  -- This stores metadata about the revision, and a reference
 136  -- to the TEXT storage backend.
 137  --
 138  CREATE TABLE /*_*/revision (
 139     rev_id INT NOT NULL UNIQUE IDENTITY(0,1),
 140     rev_page INT NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 141     rev_text_id INT  NOT NULL, -- FK added later
 142     rev_comment NVARCHAR(255) NOT NULL,
 143     rev_user INT REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
 144     rev_user_text NVARCHAR(255) NOT NULL DEFAULT '',
 145     rev_timestamp varchar(14) NOT NULL default '',
 146     rev_minor_edit BIT NOT NULL DEFAULT 0,
 147     rev_deleted TINYINT  NOT NULL DEFAULT 0,
 148     rev_len INT,
 149     rev_parent_id INT DEFAULT NULL REFERENCES /*_*/revision(rev_id),
 150     rev_sha1 nvarchar(32) not null default '',
 151     rev_content_model nvarchar(32) default null,
 152     rev_content_format nvarchar(64) default null
 153  );
 154  CREATE UNIQUE CLUSTERED INDEX /*i*/rev_page_id ON /*_*/revision (rev_page, rev_id);
 155  CREATE INDEX /*i*/rev_timestamp ON /*_*/revision (rev_timestamp);
 156  CREATE INDEX /*i*/page_timestamp ON /*_*/revision (rev_page,rev_timestamp);
 157  CREATE INDEX /*i*/user_timestamp ON /*_*/revision (rev_user,rev_timestamp);
 158  CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
 159  CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
 160  
 161  -- insert a dummy revision
 162  INSERT INTO /*_*/revision (rev_page,rev_text_id,rev_comment,rev_user,rev_len) VALUES (0,0,'',0,0);
 163  
 164  ALTER TABLE /*_*/page ADD CONSTRAINT FK_page_latest_page_id FOREIGN KEY (page_latest) REFERENCES /*_*/revision(rev_id);
 165  
 166  --
 167  -- Holds TEXT of individual page revisions.
 168  --
 169  -- Field names are a holdover from the 'old' revisions table in
 170  -- MediaWiki 1.4 and earlier: an upgrade will transform that
 171  -- table INTo the 'text' table to minimize unnecessary churning
 172  -- and downtime. If upgrading, the other fields will be left unused.
 173  CREATE TABLE /*_*/text (
 174     old_id INT NOT NULL  PRIMARY KEY IDENTITY(0,1),
 175     old_text nvarchar(max) NOT NULL,
 176     old_flags NVARCHAR(255) NOT NULL,
 177  );
 178  
 179  -- insert a dummy text
 180  INSERT INTO /*_*/text (old_text,old_flags) VALUES ('','');
 181  
 182  ALTER TABLE /*_*/revision ADD CONSTRAINT FK_rev_text_id_old_id FOREIGN KEY (rev_text_id) REFERENCES /*_*/text(old_id) ON DELETE CASCADE;
 183  
 184  --
 185  -- Holding area for deleted articles, which may be viewed
 186  -- or restored by admins through the Special:Undelete interface.
 187  -- The fields generally correspond to the page, revision, and text
 188  -- fields, with several caveats.
 189  -- Cannot reasonably create views on this table, due to the presence of TEXT
 190  -- columns.
 191  CREATE TABLE /*_*/archive (
 192     ar_id int NOT NULL PRIMARY KEY IDENTITY,
 193     ar_namespace SMALLINT NOT NULL DEFAULT 0,
 194     ar_title NVARCHAR(255) NOT NULL DEFAULT '',
 195     ar_text NVARCHAR(MAX) NOT NULL,
 196     ar_comment NVARCHAR(255) NOT NULL,
 197     ar_user INT REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
 198     ar_user_text NVARCHAR(255) NOT NULL,
 199     ar_timestamp varchar(14) NOT NULL default '',
 200     ar_minor_edit BIT NOT NULL DEFAULT 0,
 201     ar_flags NVARCHAR(255) NOT NULL,
 202     ar_rev_id INT NULL, -- NOT a FK, the row gets deleted from revision and moved here
 203     ar_text_id INT REFERENCES /*_*/text(old_id) ON DELETE CASCADE,
 204     ar_deleted TINYINT NOT NULL DEFAULT 0,
 205     ar_len INT,
 206     ar_page_id INT NULL, -- NOT a FK, the row gets deleted from page and moved here
 207     ar_parent_id INT NULL REFERENCES /*_*/revision(rev_id),
 208     ar_sha1 nvarchar(32) default null,
 209     ar_content_model nvarchar(32) DEFAULT NULL,
 210    ar_content_format nvarchar(64) DEFAULT NULL
 211  );
 212  CREATE INDEX /*i*/name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp);
 213  CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
 214  CREATE INDEX /*i*/ar_revid ON /*_*/archive (ar_rev_id);
 215  
 216  
 217  --
 218  -- Track page-to-page hyperlinks within the wiki.
 219  --
 220  CREATE TABLE /*_*/pagelinks (
 221     pl_from INT NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 222     pl_namespace INT NOT NULL DEFAULT 0,
 223     pl_title NVARCHAR(255) NOT NULL DEFAULT '',
 224  );
 225  CREATE UNIQUE INDEX /*i*/pl_from ON /*_*/pagelinks (pl_from,pl_namespace,pl_title);
 226  CREATE UNIQUE INDEX /*i*/pl_namespace ON /*_*/pagelinks (pl_namespace,pl_title,pl_from);
 227  
 228  
 229  --
 230  -- Track template inclusions.
 231  --
 232  CREATE TABLE /*_*/templatelinks (
 233    tl_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 234    tl_namespace int NOT NULL default 0,
 235    tl_title nvarchar(255) NOT NULL default ''
 236  );
 237  
 238  CREATE UNIQUE INDEX /*i*/tl_from ON /*_*/templatelinks (tl_from,tl_namespace,tl_title);
 239  CREATE UNIQUE INDEX /*i*/tl_namespace ON /*_*/templatelinks (tl_namespace,tl_title,tl_from);
 240  
 241  
 242  --
 243  -- Track links to images *used inline*
 244  -- We don't distinguish live from broken links here, so
 245  -- they do not need to be changed on upload/removal.
 246  --
 247  CREATE TABLE /*_*/imagelinks (
 248    -- Key to page_id of the page containing the image / media link.
 249    il_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 250  
 251    -- Filename of target image.
 252    -- This is also the page_title of the file's description page;
 253    -- all such pages are in namespace 6 (NS_FILE).
 254    il_to nvarchar(255) NOT NULL default ''
 255  );
 256  
 257  CREATE UNIQUE INDEX /*i*/il_from ON /*_*/imagelinks (il_from,il_to);
 258  CREATE UNIQUE INDEX /*i*/il_to ON /*_*/imagelinks (il_to,il_from);
 259  
 260  --
 261  -- Track category inclusions *used inline*
 262  -- This tracks a single level of category membership
 263  --
 264  CREATE TABLE /*_*/categorylinks (
 265    -- Key to page_id of the page defined as a category member.
 266    cl_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 267  
 268    -- Name of the category.
 269    -- This is also the page_title of the category's description page;
 270    -- all such pages are in namespace 14 (NS_CATEGORY).
 271    cl_to nvarchar(255) NOT NULL default '',
 272  
 273    -- A binary string obtained by applying a sortkey generation algorithm
 274    -- (Collation::getSortKey()) to page_title, or cl_sortkey_prefix . "\n"
 275    -- . page_title if cl_sortkey_prefix is nonempty.
 276    cl_sortkey varbinary(230) NOT NULL default 0x,
 277  
 278    -- A prefix for the raw sortkey manually specified by the user, either via
 279    -- [[Category:Foo|prefix]] or {{defaultsort:prefix}}.  If nonempty, it's
 280    -- concatenated with a line break followed by the page title before the sortkey
 281    -- conversion algorithm is run.  We store this so that we can update
 282    -- collations without reparsing all pages.
 283    -- Note: If you change the length of this field, you also need to change
 284    -- code in LinksUpdate.php. See bug 25254.
 285    cl_sortkey_prefix varbinary(255) NOT NULL default 0x,
 286  
 287    -- This isn't really used at present. Provided for an optional
 288    -- sorting method by approximate addition time.
 289    cl_timestamp varchar(14) NOT NULL,
 290  
 291    -- Stores $wgCategoryCollation at the time cl_sortkey was generated.  This
 292    -- can be used to install new collation versions, tracking which rows are not
 293    -- yet updated.  '' means no collation, this is a legacy row that needs to be
 294    -- updated by updateCollation.php.  In the future, it might be possible to
 295    -- specify different collations per category.
 296    cl_collation nvarchar(32) NOT NULL default '',
 297  
 298    -- Stores whether cl_from is a category, file, or other page, so we can
 299    -- paginate the three categories separately.  This never has to be updated
 300    -- after the page is created, since none of these page types can be moved to
 301    -- any other.
 302    cl_type varchar(10) NOT NULL default 'page',
 303    -- SQL server doesn't have enums, so we approximate with this
 304    CONSTRAINT cl_type_ckc CHECK (cl_type IN('page', 'subcat', 'file'))
 305  );
 306  
 307  CREATE UNIQUE INDEX /*i*/cl_from ON /*_*/categorylinks (cl_from,cl_to);
 308  
 309  -- We always sort within a given category, and within a given type.  FIXME:
 310  -- Formerly this index didn't cover cl_type (since that didn't exist), so old
 311  -- callers won't be using an index: fix this?
 312  CREATE INDEX /*i*/cl_sortkey ON /*_*/categorylinks (cl_to,cl_type,cl_sortkey,cl_from);
 313  
 314  -- Used by the API (and some extensions)
 315  CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
 316  
 317  -- FIXME: Not used, delete this
 318  CREATE INDEX /*i*/cl_collation ON /*_*/categorylinks (cl_collation);
 319  
 320  --
 321  -- Track all existing categories.  Something is a category if 1) it has an en-
 322  -- try somewhere in categorylinks, or 2) it once did.  Categories might not
 323  -- have corresponding pages, so they need to be tracked separately.
 324  --
 325  CREATE TABLE /*_*/category (
 326    -- Primary key
 327    cat_id int NOT NULL PRIMARY KEY IDENTITY,
 328  
 329    -- Name of the category, in the same form as page_title (with underscores).
 330    -- If there is a category page corresponding to this category, by definition,
 331    -- it has this name (in the Category namespace).
 332    cat_title nvarchar(255) NOT NULL,
 333  
 334    -- The numbers of member pages (including categories and media), subcatego-
 335    -- ries, and Image: namespace members, respectively.  These are signed to
 336    -- make underflow more obvious.  We make the first number include the second
 337    -- two for better sorting: subtracting for display is easy, adding for order-
 338    -- ing is not.
 339    cat_pages int NOT NULL default 0,
 340    cat_subcats int NOT NULL default 0,
 341    cat_files int NOT NULL default 0
 342  );
 343  
 344  CREATE UNIQUE INDEX /*i*/cat_title ON /*_*/category (cat_title);
 345  
 346  -- For Special:Mostlinkedcategories
 347  CREATE INDEX /*i*/cat_pages ON /*_*/category (cat_pages);
 348  
 349  
 350  --
 351  -- Track links to external URLs
 352  --
 353  CREATE TABLE /*_*/externallinks (
 354    -- Primary key
 355    el_id int NOT NULL PRIMARY KEY IDENTITY,
 356  
 357    -- page_id of the referring page
 358    el_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 359  
 360    -- The URL
 361    el_to nvarchar(max) NOT NULL,
 362  
 363    -- In the case of HTTP URLs, this is the URL with any username or password
 364    -- removed, and with the labels in the hostname reversed and converted to
 365    -- lower case. An extra dot is added to allow for matching of either
 366    -- example.com or *.example.com in a single scan.
 367    -- Example:
 368    --      http://user:[email protected]/page.html
 369    --   becomes
 370    --      http://com.example.sub./page.html
 371    -- which allows for fast searching for all pages under example.com with the
 372    -- clause:
 373    --      WHERE el_index LIKE 'http://com.example.%'
 374    el_index nvarchar(450) NOT NULL
 375  );
 376  
 377  CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from);
 378  CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index);
 379  
 380  --
 381  -- Track interlanguage links
 382  --
 383  CREATE TABLE /*_*/langlinks (
 384    -- page_id of the referring page
 385    ll_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 386  
 387    -- Language code of the target
 388    ll_lang nvarchar(20) NOT NULL default '',
 389  
 390    -- Title of the target, including namespace
 391    ll_title nvarchar(255) NOT NULL default ''
 392  );
 393  
 394  CREATE UNIQUE INDEX /*i*/ll_from ON /*_*/langlinks (ll_from, ll_lang);
 395  CREATE INDEX /*i*/ll_lang ON /*_*/langlinks (ll_lang, ll_title);
 396  
 397  
 398  --
 399  -- Track inline interwiki links
 400  --
 401  CREATE TABLE /*_*/iwlinks (
 402    -- page_id of the referring page
 403    iwl_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 404  
 405    -- Interwiki prefix code of the target
 406    iwl_prefix nvarchar(20) NOT NULL default '',
 407  
 408    -- Title of the target, including namespace
 409    iwl_title nvarchar(255) NOT NULL default ''
 410  );
 411  
 412  CREATE UNIQUE INDEX /*i*/iwl_from ON /*_*/iwlinks (iwl_from, iwl_prefix, iwl_title);
 413  CREATE INDEX /*i*/iwl_prefix_title_from ON /*_*/iwlinks (iwl_prefix, iwl_title, iwl_from);
 414  CREATE INDEX /*i*/iwl_prefix_from_title ON /*_*/iwlinks (iwl_prefix, iwl_from, iwl_title);
 415  
 416  
 417  --
 418  -- Contains a single row with some aggregate info
 419  -- on the state of the site.
 420  --
 421  CREATE TABLE /*_*/site_stats (
 422    -- The single row should contain 1 here.
 423    ss_row_id int NOT NULL,
 424  
 425    -- Total number of page views, if hit counters are enabled.
 426    ss_total_views bigint default 0,
 427  
 428    -- Total number of edits performed.
 429    ss_total_edits bigint default 0,
 430  
 431    -- An approximate count of pages matching the following criteria:
 432    -- * in namespace 0
 433    -- * not a redirect
 434    -- * contains the text '[['
 435    -- See Article::isCountable() in includes/Article.php
 436    ss_good_articles bigint default 0,
 437  
 438    -- Total pages, theoretically equal to SELECT COUNT(*) FROM page; except faster
 439    ss_total_pages bigint default '-1',
 440  
 441    -- Number of users, theoretically equal to SELECT COUNT(*) FROM user;
 442    ss_users bigint default '-1',
 443  
 444    -- Number of users that still edit
 445    ss_active_users bigint default '-1',
 446  
 447    -- Number of images, equivalent to SELECT COUNT(*) FROM image
 448    ss_images int default 0
 449  );
 450  
 451  -- Pointless index to assuage developer superstitions
 452  CREATE UNIQUE INDEX /*i*/ss_row_id ON /*_*/site_stats (ss_row_id);
 453  
 454  
 455  --
 456  -- Stores an ID for every time any article is visited;
 457  -- depending on $wgHitcounterUpdateFreq, it is
 458  -- periodically cleared and the page_counter column
 459  -- in the page table updated for all the articles
 460  -- that have been visited.)
 461  --
 462  CREATE TABLE /*_*/hitcounter (
 463    hc_id int NOT NULL
 464  );
 465  
 466  
 467  --
 468  -- The internet is full of jerks, alas. Sometimes it's handy
 469  -- to block a vandal or troll account.
 470  --
 471  CREATE TABLE /*_*/ipblocks (
 472    -- Primary key, introduced for privacy.
 473    ipb_id int NOT NULL PRIMARY KEY IDENTITY,
 474  
 475    -- Blocked IP address in dotted-quad form or user name.
 476    ipb_address nvarchar(255) NOT NULL,
 477  
 478    -- Blocked user ID or 0 for IP blocks.
 479    ipb_user int REFERENCES /*_*/mwuser(user_id),
 480  
 481    -- User ID who made the block.
 482    ipb_by int REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
 483  
 484    -- User name of blocker
 485    ipb_by_text nvarchar(255) NOT NULL default '',
 486  
 487    -- Text comment made by blocker.
 488    ipb_reason nvarchar(255) NOT NULL,
 489  
 490    -- Creation (or refresh) date in standard YMDHMS form.
 491    -- IP blocks expire automatically.
 492    ipb_timestamp varchar(14) NOT NULL default '',
 493  
 494    -- Indicates that the IP address was banned because a banned
 495    -- user accessed a page through it. If this is 1, ipb_address
 496    -- will be hidden, and the block identified by block ID number.
 497    ipb_auto bit NOT NULL default 0,
 498  
 499    -- If set to 1, block applies only to logged-out users
 500    ipb_anon_only bit NOT NULL default 0,
 501  
 502    -- Block prevents account creation from matching IP addresses
 503    ipb_create_account bit NOT NULL default 1,
 504  
 505    -- Block triggers autoblocks
 506    ipb_enable_autoblock bit NOT NULL default 1,
 507  
 508    -- Time at which the block will expire.
 509    -- May be "infinity"
 510    ipb_expiry varchar(14) NOT NULL,
 511  
 512    -- Start and end of an address range, in hexadecimal
 513    -- Size chosen to allow IPv6
 514    -- FIXME: these fields were originally blank for single-IP blocks,
 515    -- but now they are populated. No migration was ever done. They
 516    -- should be fixed to be blank again for such blocks (bug 49504).
 517    ipb_range_start varchar(255) NOT NULL,
 518    ipb_range_end varchar(255) NOT NULL,
 519  
 520    -- Flag for entries hidden from users and Sysops
 521    ipb_deleted bit NOT NULL default 0,
 522  
 523    -- Block prevents user from accessing Special:Emailuser
 524    ipb_block_email bit NOT NULL default 0,
 525  
 526    -- Block allows user to edit their own talk page
 527    ipb_allow_usertalk bit NOT NULL default 0,
 528  
 529    -- ID of the block that caused this block to exist
 530    -- Autoblocks set this to the original block
 531    -- so that the original block being deleted also
 532    -- deletes the autoblocks
 533    ipb_parent_block_id int default NULL REFERENCES /*_*/ipblocks(ipb_id)
 534  
 535  );
 536  
 537  -- Unique index to support "user already blocked" messages
 538  -- Any new options which prevent collisions should be included
 539  CREATE UNIQUE INDEX /*i*/ipb_address ON /*_*/ipblocks (ipb_address, ipb_user, ipb_auto, ipb_anon_only);
 540  
 541  CREATE INDEX /*i*/ipb_user ON /*_*/ipblocks (ipb_user);
 542  CREATE INDEX /*i*/ipb_range ON /*_*/ipblocks (ipb_range_start, ipb_range_end);
 543  CREATE INDEX /*i*/ipb_timestamp ON /*_*/ipblocks (ipb_timestamp);
 544  CREATE INDEX /*i*/ipb_expiry ON /*_*/ipblocks (ipb_expiry);
 545  CREATE INDEX /*i*/ipb_parent_block_id ON /*_*/ipblocks (ipb_parent_block_id);
 546  
 547  
 548  --
 549  -- Uploaded images and other files.
 550  --
 551  CREATE TABLE /*_*/image (
 552    -- Filename.
 553    -- This is also the title of the associated description page,
 554    -- which will be in namespace 6 (NS_FILE).
 555    img_name varbinary(255) NOT NULL default 0x PRIMARY KEY,
 556  
 557    -- File size in bytes.
 558    img_size int NOT NULL default 0,
 559  
 560    -- For images, size in pixels.
 561    img_width int NOT NULL default 0,
 562    img_height int NOT NULL default 0,
 563  
 564    -- Extracted Exif metadata stored as a serialized PHP array.
 565    img_metadata varbinary(max) NOT NULL,
 566  
 567    -- For images, bits per pixel if known.
 568    img_bits int NOT NULL default 0,
 569  
 570    -- Media type as defined by the MEDIATYPE_xxx constants
 571    img_media_type varchar(16) default null,
 572  
 573    -- major part of a MIME media type as defined by IANA
 574    -- see http://www.iana.org/assignments/media-types/
 575    img_major_mime varchar(16) not null default 'unknown',
 576  
 577    -- minor part of a MIME media type as defined by IANA
 578    -- the minor parts are not required to adher to any standard
 579    -- but should be consistent throughout the database
 580    -- see http://www.iana.org/assignments/media-types/
 581    img_minor_mime nvarchar(100) NOT NULL default 'unknown',
 582  
 583    -- Description field as entered by the uploader.
 584    -- This is displayed in image upload history and logs.
 585    img_description nvarchar(255) NOT NULL,
 586  
 587    -- user_id and user_name of uploader.
 588    img_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
 589    img_user_text nvarchar(255) NOT NULL,
 590  
 591    -- Time of the upload.
 592    img_timestamp nvarchar(14) NOT NULL default '',
 593  
 594    -- SHA-1 content hash in base-36
 595    img_sha1 nvarchar(32) NOT NULL default '',
 596  
 597    CONSTRAINT img_major_mime_ckc check (img_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
 598    CONSTRAINT img_media_type_ckc check (img_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
 599  );
 600  
 601  CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
 602  -- Used by Special:ListFiles for sort-by-size
 603  CREATE INDEX /*i*/img_size ON /*_*/image (img_size);
 604  -- Used by Special:Newimages and Special:ListFiles
 605  CREATE INDEX /*i*/img_timestamp ON /*_*/image (img_timestamp);
 606  -- Used in API and duplicate search
 607  CREATE INDEX /*i*/img_sha1 ON /*_*/image (img_sha1);
 608  -- Used to get media of one type
 609  CREATE INDEX /*i*/img_media_mime ON /*_*/image (img_media_type,img_major_mime,img_minor_mime);
 610  
 611  
 612  --
 613  -- Previous revisions of uploaded files.
 614  -- Awkwardly, image rows have to be moved into
 615  -- this table at re-upload time.
 616  --
 617  CREATE TABLE /*_*/oldimage (
 618    -- Base filename: key to image.img_name
 619    oi_name varbinary(255) NOT NULL default 0x REFERENCES /*_*/image(img_name) ON DELETE CASCADE ON UPDATE CASCADE,
 620  
 621    -- Filename of the archived file.
 622    -- This is generally a timestamp and '!' prepended to the base name.
 623    oi_archive_name varbinary(255) NOT NULL default 0x,
 624  
 625    -- Other fields as in image...
 626    oi_size int NOT NULL default 0,
 627    oi_width int NOT NULL default 0,
 628    oi_height int NOT NULL default 0,
 629    oi_bits int NOT NULL default 0,
 630    oi_description nvarchar(255) NOT NULL,
 631    oi_user int REFERENCES /*_*/mwuser(user_id),
 632    oi_user_text nvarchar(255) NOT NULL,
 633    oi_timestamp varchar(14) NOT NULL default '',
 634  
 635    oi_metadata nvarchar(max) NOT NULL,
 636    oi_media_type varchar(16) default null,
 637    oi_major_mime varchar(16) not null default 'unknown',
 638    oi_minor_mime nvarchar(100) NOT NULL default 'unknown',
 639    oi_deleted tinyint NOT NULL default 0,
 640    oi_sha1 nvarchar(32) NOT NULL default '',
 641  
 642    CONSTRAINT oi_major_mime_ckc check (oi_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
 643    CONSTRAINT oi_media_type_ckc check (oi_media_type IN('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
 644  );
 645  
 646  CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
 647  CREATE INDEX /*i*/oi_name_timestamp ON /*_*/oldimage (oi_name,oi_timestamp);
 648  -- oi_archive_name truncated to 14 to avoid key length overflow
 649  CREATE INDEX /*i*/oi_name_archive_name ON /*_*/oldimage (oi_name,oi_archive_name);
 650  CREATE INDEX /*i*/oi_sha1 ON /*_*/oldimage (oi_sha1);
 651  
 652  
 653  --
 654  -- Record of deleted file data
 655  --
 656  CREATE TABLE /*_*/filearchive (
 657    -- Unique row id
 658    fa_id int NOT NULL PRIMARY KEY IDENTITY,
 659  
 660    -- Original base filename; key to image.img_name, page.page_title, etc
 661    fa_name nvarchar(255) NOT NULL default '',
 662  
 663    -- Filename of archived file, if an old revision
 664    fa_archive_name nvarchar(255) default '',
 665  
 666    -- Which storage bin (directory tree or object store) the file data
 667    -- is stored in. Should be 'deleted' for files that have been deleted;
 668    -- any other bin is not yet in use.
 669    fa_storage_group nvarchar(16),
 670  
 671    -- SHA-1 of the file contents plus extension, used as a key for storage.
 672    -- eg 8f8a562add37052a1848ff7771a2c515db94baa9.jpg
 673    --
 674    -- If NULL, the file was missing at deletion time or has been purged
 675    -- from the archival storage.
 676    fa_storage_key nvarchar(64) default '',
 677  
 678    -- Deletion information, if this file is deleted.
 679    fa_deleted_user int,
 680    fa_deleted_timestamp varchar(14) default '',
 681    fa_deleted_reason nvarchar(max),
 682  
 683    -- Duped fields from image
 684    fa_size int default 0,
 685    fa_width int default 0,
 686    fa_height int default 0,
 687    fa_metadata nvarchar(max),
 688    fa_bits int default 0,
 689    fa_media_type varchar(16) default null,
 690    fa_major_mime varchar(16) not null default 'unknown',
 691    fa_minor_mime nvarchar(100) default 'unknown',
 692    fa_description nvarchar(255),
 693    fa_user int default 0 REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
 694    fa_user_text nvarchar(255),
 695    fa_timestamp varchar(14) default '',
 696  
 697    -- Visibility of deleted revisions, bitfield
 698    fa_deleted tinyint NOT NULL default 0,
 699  
 700    -- sha1 hash of file content
 701    fa_sha1 nvarchar(32) NOT NULL default '',
 702  
 703    CONSTRAINT fa_major_mime_ckc check (fa_major_mime in('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
 704    CONSTRAINT fa_media_type_ckc check (fa_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
 705  );
 706  
 707  -- pick out by image name
 708  CREATE INDEX /*i*/fa_name ON /*_*/filearchive (fa_name, fa_timestamp);
 709  -- pick out dupe files
 710  CREATE INDEX /*i*/fa_storage_group ON /*_*/filearchive (fa_storage_group, fa_storage_key);
 711  -- sort by deletion time
 712  CREATE INDEX /*i*/fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp);
 713  -- sort by uploader
 714  CREATE INDEX /*i*/fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp);
 715  -- find file by sha1, 10 bytes will be enough for hashes to be indexed
 716  CREATE INDEX /*i*/fa_sha1 ON /*_*/filearchive (fa_sha1);
 717  
 718  
 719  --
 720  -- Store information about newly uploaded files before they're
 721  -- moved into the actual filestore
 722  --
 723  CREATE TABLE /*_*/uploadstash (
 724    us_id int NOT NULL PRIMARY KEY IDENTITY,
 725  
 726    -- the user who uploaded the file.
 727    us_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
 728  
 729    -- file key. this is how applications actually search for the file.
 730    -- this might go away, or become the primary key.
 731    us_key nvarchar(255) NOT NULL,
 732  
 733    -- the original path
 734    us_orig_path nvarchar(255) NOT NULL,
 735  
 736    -- the temporary path at which the file is actually stored
 737    us_path nvarchar(255) NOT NULL,
 738  
 739    -- which type of upload the file came from (sometimes)
 740    us_source_type nvarchar(50),
 741  
 742    -- the date/time on which the file was added
 743    us_timestamp varchar(14) NOT NULL,
 744  
 745    us_status nvarchar(50) NOT NULL,
 746  
 747    -- chunk counter starts at 0, current offset is stored in us_size
 748    us_chunk_inx int NULL,
 749  
 750    -- Serialized file properties from FSFile::getProps()
 751    us_props nvarchar(max),
 752  
 753    -- file size in bytes
 754    us_size int NOT NULL,
 755    -- this hash comes from FSFile::getSha1Base36(), and is 31 characters
 756    us_sha1 nvarchar(31) NOT NULL,
 757    us_mime nvarchar(255),
 758    -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table
 759    us_media_type varchar(16) default null,
 760    -- image-specific properties
 761    us_image_width int,
 762    us_image_height int,
 763    us_image_bits smallint,
 764  
 765    CONSTRAINT us_media_type_ckc check (us_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
 766  );
 767  
 768  -- sometimes there's a delete for all of a user's stuff.
 769  CREATE INDEX /*i*/us_user ON /*_*/uploadstash (us_user);
 770  -- pick out files by key, enforce key uniqueness
 771  CREATE UNIQUE INDEX /*i*/us_key ON /*_*/uploadstash (us_key);
 772  -- the abandoned upload cleanup script needs this
 773  CREATE INDEX /*i*/us_timestamp ON /*_*/uploadstash (us_timestamp);
 774  
 775  
 776  --
 777  -- Primarily a summary table for Special:Recentchanges,
 778  -- this table contains some additional info on edits from
 779  -- the last few days, see Article::editUpdates()
 780  --
 781  CREATE TABLE /*_*/recentchanges (
 782    rc_id int NOT NULL PRIMARY KEY IDENTITY,
 783    rc_timestamp varchar(14) not null default '',
 784  
 785    -- This is no longer used
 786    -- Field kept in database for downgrades
 787    -- @todo: add drop patch with 1.24
 788    rc_cur_time varchar(14) NOT NULL default '',
 789  
 790    -- As in revision
 791    rc_user int NOT NULL default 0 REFERENCES /*_*/mwuser(user_id),
 792    rc_user_text nvarchar(255) NOT NULL,
 793  
 794    -- When pages are renamed, their RC entries do _not_ change.
 795    rc_namespace int NOT NULL default 0,
 796    rc_title nvarchar(255) NOT NULL default '',
 797  
 798    -- as in revision...
 799    rc_comment nvarchar(255) NOT NULL default '',
 800    rc_minor bit NOT NULL default 0,
 801  
 802    -- Edits by user accounts with the 'bot' rights key are
 803    -- marked with a 1 here, and will be hidden from the
 804    -- default view.
 805    rc_bot bit NOT NULL default 0,
 806  
 807    -- Set if this change corresponds to a page creation
 808    rc_new bit NOT NULL default 0,
 809  
 810    -- Key to page_id (was cur_id prior to 1.5).
 811    -- This will keep links working after moves while
 812    -- retaining the at-the-time name in the changes list.
 813    rc_cur_id int REFERENCES /*_*/page(page_id),
 814  
 815    -- rev_id of the given revision
 816    rc_this_oldid int REFERENCES /*_*/revision(rev_id),
 817  
 818    -- rev_id of the prior revision, for generating diff links.
 819    rc_last_oldid int REFERENCES /*_*/revision(rev_id),
 820  
 821    -- The type of change entry (RC_EDIT,RC_NEW,RC_LOG,RC_EXTERNAL)
 822    rc_type tinyint NOT NULL default 0,
 823  
 824    -- The source of the change entry (replaces rc_type)
 825    -- default of '' is temporary, needed for initial migration
 826    rc_source nvarchar(16) not null default '',
 827  
 828    -- If the Recent Changes Patrol option is enabled,
 829    -- users may mark edits as having been reviewed to
 830    -- remove a warning flag on the RC list.
 831    -- A value of 1 indicates the page has been reviewed.
 832    rc_patrolled bit NOT NULL default 0,
 833  
 834    -- Recorded IP address the edit was made from, if the
 835    -- $wgPutIPinRC option is enabled.
 836    rc_ip nvarchar(40) NOT NULL default '',
 837  
 838    -- Text length in characters before
 839    -- and after the edit
 840    rc_old_len int,
 841    rc_new_len int,
 842  
 843    -- Visibility of recent changes items, bitfield
 844    rc_deleted tinyint NOT NULL default 0,
 845  
 846    -- Value corresponding to log_id, specific log entries
 847    rc_logid int, -- FK added later
 848    -- Store log type info here, or null
 849    rc_log_type nvarchar(255) NULL default NULL,
 850    -- Store log action or null
 851    rc_log_action nvarchar(255) NULL default NULL,
 852    -- Log params
 853    rc_params nvarchar(max) NULL
 854  );
 855  
 856  CREATE INDEX /*i*/rc_timestamp ON /*_*/recentchanges (rc_timestamp);
 857  CREATE INDEX /*i*/rc_namespace_title ON /*_*/recentchanges (rc_namespace, rc_title);
 858  CREATE INDEX /*i*/rc_cur_id ON /*_*/recentchanges (rc_cur_id);
 859  CREATE INDEX /*i*/new_name_timestamp ON /*_*/recentchanges (rc_new,rc_namespace,rc_timestamp);
 860  CREATE INDEX /*i*/rc_ip ON /*_*/recentchanges (rc_ip);
 861  CREATE INDEX /*i*/rc_ns_usertext ON /*_*/recentchanges (rc_namespace, rc_user_text);
 862  CREATE INDEX /*i*/rc_user_text ON /*_*/recentchanges (rc_user_text, rc_timestamp);
 863  
 864  
 865  CREATE TABLE /*_*/watchlist (
 866    -- Key to user.user_id
 867    wl_user int NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
 868  
 869    -- Key to page_namespace/page_title
 870    -- Note that users may watch pages which do not exist yet,
 871    -- or existed in the past but have been deleted.
 872    wl_namespace int NOT NULL default 0,
 873    wl_title nvarchar(255) NOT NULL default '',
 874  
 875    -- Timestamp used to send notification e-mails and show "updated since last visit" markers on
 876    -- history and recent changes / watchlist. Set to NULL when the user visits the latest revision
 877    -- of the page, which means that they should be sent an e-mail on the next change.
 878    wl_notificationtimestamp varchar(14)
 879  
 880  );
 881  
 882  CREATE UNIQUE INDEX /*i*/wl_user ON /*_*/watchlist (wl_user, wl_namespace, wl_title);
 883  CREATE INDEX /*i*/namespace_title ON /*_*/watchlist (wl_namespace, wl_title);
 884  
 885  
 886  --
 887  -- Our search index for the builtin MediaWiki search
 888  --
 889  CREATE TABLE /*_*/searchindex (
 890    -- Key to page_id
 891    si_page int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
 892  
 893    -- Munged version of title
 894    si_title nvarchar(255) NOT NULL default '',
 895  
 896    -- Munged version of body text
 897    si_text nvarchar(max) NOT NULL
 898  );
 899  
 900  CREATE UNIQUE INDEX /*i*/si_page ON /*_*/searchindex (si_page);
 901  -- Fulltext index is defined in MssqlInstaller.php
 902  
 903  --
 904  -- Recognized interwiki link prefixes
 905  --
 906  CREATE TABLE /*_*/interwiki (
 907    -- The interwiki prefix, (e.g. "Meatball", or the language prefix "de")
 908    iw_prefix nvarchar(32) NOT NULL,
 909  
 910    -- The URL of the wiki, with "$1" as a placeholder for an article name.
 911    -- Any spaces in the name will be transformed to underscores before
 912    -- insertion.
 913    iw_url nvarchar(max) NOT NULL,
 914  
 915    -- The URL of the file api.php
 916    iw_api nvarchar(max) NOT NULL,
 917  
 918    -- The name of the database (for a connection to be established with wfGetLB( 'wikiid' ))
 919    iw_wikiid nvarchar(64) NOT NULL,
 920  
 921    -- A boolean value indicating whether the wiki is in this project
 922    -- (used, for example, to detect redirect loops)
 923    iw_local bit NOT NULL,
 924  
 925    -- Boolean value indicating whether interwiki transclusions are allowed.
 926    iw_trans bit NOT NULL default 0
 927  );
 928  
 929  CREATE UNIQUE INDEX /*i*/iw_prefix ON /*_*/interwiki (iw_prefix);
 930  
 931  
 932  --
 933  -- Used for caching expensive grouped queries
 934  --
 935  CREATE TABLE /*_*/querycache (
 936    -- A key name, generally the base name of of the special page.
 937    qc_type nvarchar(32) NOT NULL,
 938  
 939    -- Some sort of stored value. Sizes, counts...
 940    qc_value int NOT NULL default 0,
 941  
 942    -- Target namespace+title
 943    qc_namespace int NOT NULL default 0,
 944    qc_title nvarchar(255) NOT NULL default ''
 945  );
 946  
 947  CREATE INDEX /*i*/qc_type ON /*_*/querycache (qc_type,qc_value);
 948  
 949  
 950  --
 951  -- For a few generic cache operations if not using Memcached
 952  --
 953  CREATE TABLE /*_*/objectcache (
 954    keyname nvarchar(255) NOT NULL default '' PRIMARY KEY,
 955    value varbinary(max),
 956    exptime varchar(14)
 957  );
 958  CREATE INDEX /*i*/exptime ON /*_*/objectcache (exptime);
 959  
 960  
 961  --
 962  -- Cache of interwiki transclusion
 963  --
 964  CREATE TABLE /*_*/transcache (
 965    tc_url nvarchar(255) NOT NULL,
 966    tc_contents nvarchar(max),
 967    tc_time varchar(14) NOT NULL
 968  );
 969  
 970  CREATE UNIQUE INDEX /*i*/tc_url_idx ON /*_*/transcache (tc_url);
 971  
 972  
 973  CREATE TABLE /*_*/logging (
 974    -- Log ID, for referring to this specific log entry, probably for deletion and such.
 975    log_id int NOT NULL PRIMARY KEY IDENTITY(0,1),
 976  
 977    -- Symbolic keys for the general log type and the action type
 978    -- within the log. The output format will be controlled by the
 979    -- action field, but only the type controls categorization.
 980    log_type nvarchar(32) NOT NULL default '',
 981    log_action nvarchar(32) NOT NULL default '',
 982  
 983    -- Timestamp. Duh.
 984    log_timestamp varchar(14) NOT NULL default '',
 985  
 986    -- The user who performed this action; key to user_id
 987    log_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
 988  
 989    -- Name of the user who performed this action
 990    log_user_text nvarchar(255) NOT NULL default '',
 991  
 992    -- Key to the page affected. Where a user is the target,
 993    -- this will point to the user page.
 994    log_namespace int NOT NULL default 0,
 995    log_title nvarchar(255) NOT NULL default '',
 996    log_page int NULL REFERENCES /*_*/page(page_id) ON DELETE SET NULL,
 997  
 998    -- Freeform text. Interpreted as edit history comments.
 999    log_comment nvarchar(255) NOT NULL default '',
1000  
1001    -- miscellaneous parameters:
1002    -- LF separated list (old system) or serialized PHP array (new system)
1003    log_params nvarchar(max) NOT NULL,
1004  
1005    -- rev_deleted for logs
1006    log_deleted tinyint NOT NULL default 0
1007  );
1008  
1009  CREATE INDEX /*i*/type_time ON /*_*/logging (log_type, log_timestamp);
1010  CREATE INDEX /*i*/user_time ON /*_*/logging (log_user, log_timestamp);
1011  CREATE INDEX /*i*/page_time ON /*_*/logging (log_namespace, log_title, log_timestamp);
1012  CREATE INDEX /*i*/times ON /*_*/logging (log_timestamp);
1013  CREATE INDEX /*i*/log_user_type_time ON /*_*/logging (log_user, log_type, log_timestamp);
1014  CREATE INDEX /*i*/log_page_id_time ON /*_*/logging (log_page,log_timestamp);
1015  CREATE INDEX /*i*/type_action ON /*_*/logging (log_type, log_action, log_timestamp);
1016  CREATE INDEX /*i*/log_user_text_type_time ON /*_*/logging (log_user_text, log_type, log_timestamp);
1017  CREATE INDEX /*i*/log_user_text_time ON /*_*/logging (log_user_text, log_timestamp);
1018  
1019  INSERT INTO /*_*/logging (log_user,log_page,log_params) VALUES(0,0,'');
1020  
1021  ALTER TABLE /*_*/recentchanges ADD CONSTRAINT FK_rc_logid_log_id FOREIGN KEY (rc_logid) REFERENCES /*_*/logging(log_id) ON DELETE CASCADE;
1022  
1023  CREATE TABLE /*_*/log_search (
1024    -- The type of ID (rev ID, log ID, rev timestamp, username)
1025    ls_field nvarchar(32) NOT NULL,
1026    -- The value of the ID
1027    ls_value nvarchar(255) NOT NULL,
1028    -- Key to log_id
1029    ls_log_id int REFERENCES /*_*/logging(log_id) ON DELETE CASCADE
1030  );
1031  CREATE UNIQUE INDEX /*i*/ls_field_val ON /*_*/log_search (ls_field,ls_value,ls_log_id);
1032  CREATE INDEX /*i*/ls_log_id ON /*_*/log_search (ls_log_id);
1033  
1034  
1035  -- Jobs performed by parallel apache threads or a command-line daemon
1036  CREATE TABLE /*_*/job (
1037    job_id int NOT NULL PRIMARY KEY IDENTITY,
1038  
1039    -- Command name
1040    -- Limited to 60 to prevent key length overflow
1041    job_cmd nvarchar(60) NOT NULL default '',
1042  
1043    -- Namespace and title to act on
1044    -- Should be 0 and '' if the command does not operate on a title
1045    job_namespace int NOT NULL,
1046    job_title nvarchar(255) NOT NULL,
1047  
1048    -- Timestamp of when the job was inserted
1049    -- NULL for jobs added before addition of the timestamp
1050    job_timestamp nvarchar(14) NULL default NULL,
1051  
1052    -- Any other parameters to the command
1053    -- Stored as a PHP serialized array, or an empty string if there are no parameters
1054    job_params nvarchar(max) NOT NULL,
1055  
1056    -- Random, non-unique, number used for job acquisition (for lock concurrency)
1057    job_random int NOT NULL default 0,
1058  
1059    -- The number of times this job has been locked
1060    job_attempts int NOT NULL default 0,
1061  
1062    -- Field that conveys process locks on rows via process UUIDs
1063    job_token nvarchar(32) NOT NULL default '',
1064  
1065    -- Timestamp when the job was locked
1066    job_token_timestamp varchar(14) NULL default NULL,
1067  
1068    -- Base 36 SHA1 of the job parameters relevant to detecting duplicates
1069    job_sha1 nvarchar(32) NOT NULL default ''
1070  );
1071  
1072  CREATE INDEX /*i*/job_sha1 ON /*_*/job (job_sha1);
1073  CREATE INDEX /*i*/job_cmd_token ON /*_*/job (job_cmd,job_token,job_random);
1074  CREATE INDEX /*i*/job_cmd_token_id ON /*_*/job (job_cmd,job_token,job_id);
1075  CREATE INDEX /*i*/job_cmd ON /*_*/job (job_cmd, job_namespace, job_title);
1076  CREATE INDEX /*i*/job_timestamp ON /*_*/job (job_timestamp);
1077  
1078  
1079  -- Details of updates to cached special pages
1080  CREATE TABLE /*_*/querycache_info (
1081    -- Special page name
1082    -- Corresponds to a qc_type value
1083    qci_type nvarchar(32) NOT NULL default '',
1084  
1085    -- Timestamp of last update
1086    qci_timestamp varchar(14) NOT NULL default ''
1087  );
1088  
1089  CREATE UNIQUE INDEX /*i*/qci_type ON /*_*/querycache_info (qci_type);
1090  
1091  
1092  -- For each redirect, this table contains exactly one row defining its target
1093  CREATE TABLE /*_*/redirect (
1094    -- Key to the page_id of the redirect page
1095    rd_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
1096  
1097    -- Key to page_namespace/page_title of the target page.
1098    -- The target page may or may not exist, and due to renames
1099    -- and deletions may refer to different page records as time
1100    -- goes by.
1101    rd_namespace int NOT NULL default 0,
1102    rd_title nvarchar(255) NOT NULL default '',
1103    rd_interwiki nvarchar(32) default NULL,
1104    rd_fragment nvarchar(255) default NULL
1105  );
1106  
1107  CREATE INDEX /*i*/rd_ns_title ON /*_*/redirect (rd_namespace,rd_title,rd_from);
1108  
1109  
1110  -- Used for caching expensive grouped queries that need two links (for example double-redirects)
1111  CREATE TABLE /*_*/querycachetwo (
1112    -- A key name, generally the base name of of the special page.
1113    qcc_type nvarchar(32) NOT NULL,
1114  
1115    -- Some sort of stored value. Sizes, counts...
1116    qcc_value int NOT NULL default 0,
1117  
1118    -- Target namespace+title
1119    qcc_namespace int NOT NULL default 0,
1120    qcc_title nvarchar(255) NOT NULL default '',
1121  
1122    -- Target namespace+title2
1123    qcc_namespacetwo int NOT NULL default 0,
1124    qcc_titletwo nvarchar(255) NOT NULL default ''
1125  );
1126  
1127  CREATE INDEX /*i*/qcc_type ON /*_*/querycachetwo (qcc_type,qcc_value);
1128  CREATE INDEX /*i*/qcc_title ON /*_*/querycachetwo (qcc_type,qcc_namespace,qcc_title);
1129  CREATE INDEX /*i*/qcc_titletwo ON /*_*/querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
1130  
1131  
1132  -- Used for storing page restrictions (i.e. protection levels)
1133  CREATE TABLE /*_*/page_restrictions (
1134    -- Field for an ID for this restrictions row (sort-key for Special:ProtectedPages)
1135    pr_id int NOT NULL PRIMARY KEY IDENTITY,
1136    -- Page to apply restrictions to (Foreign Key to page).
1137    pr_page int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
1138    -- The protection type (edit, move, etc)
1139    pr_type nvarchar(60) NOT NULL,
1140    -- The protection level (Sysop, autoconfirmed, etc)
1141    pr_level nvarchar(60) NOT NULL,
1142    -- Whether or not to cascade the protection down to pages transcluded.
1143    pr_cascade bit NOT NULL,
1144    -- Field for future support of per-user restriction.
1145    pr_user int NULL,
1146    -- Field for time-limited protection.
1147    pr_expiry varchar(14) NULL
1148  );
1149  
1150  CREATE UNIQUE INDEX /*i*/pr_pagetype ON /*_*/page_restrictions (pr_page,pr_type);
1151  CREATE INDEX /*i*/pr_typelevel ON /*_*/page_restrictions (pr_type,pr_level);
1152  CREATE INDEX /*i*/pr_level ON /*_*/page_restrictions (pr_level);
1153  CREATE INDEX /*i*/pr_cascade ON /*_*/page_restrictions (pr_cascade);
1154  
1155  
1156  -- Protected titles - nonexistent pages that have been protected
1157  CREATE TABLE /*_*/protected_titles (
1158    pt_namespace int NOT NULL,
1159    pt_title nvarchar(255) NOT NULL,
1160    pt_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
1161    pt_reason nvarchar(255),
1162    pt_timestamp varchar(14) NOT NULL,
1163    pt_expiry varchar(14) NOT NULL,
1164    pt_create_perm nvarchar(60) NOT NULL
1165  );
1166  
1167  CREATE UNIQUE INDEX /*i*/pt_namespace_title ON /*_*/protected_titles (pt_namespace,pt_title);
1168  CREATE INDEX /*i*/pt_timestamp ON /*_*/protected_titles (pt_timestamp);
1169  
1170  
1171  -- Name/value pairs indexed by page_id
1172  CREATE TABLE /*_*/page_props (
1173    pp_page int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
1174    pp_propname nvarchar(60) NOT NULL,
1175    pp_value nvarchar(max) NOT NULL
1176  );
1177  
1178  CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props (pp_page,pp_propname);
1179  CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props (pp_propname,pp_page);
1180  
1181  
1182  -- A table to log updates, one text key row per update.
1183  CREATE TABLE /*_*/updatelog (
1184    ul_key nvarchar(255) NOT NULL PRIMARY KEY,
1185    ul_value nvarchar(max)
1186  );
1187  
1188  
1189  -- A table to track tags for revisions, logs and recent changes.
1190  CREATE TABLE /*_*/change_tag (
1191    -- RCID for the change
1192    ct_rc_id int NULL REFERENCES /*_*/recentchanges(rc_id),
1193    -- LOGID for the change
1194    ct_log_id int NULL REFERENCES /*_*/logging(log_id),
1195    -- REVID for the change
1196    ct_rev_id int NULL REFERENCES /*_*/revision(rev_id),
1197    -- Tag applied
1198    ct_tag nvarchar(255) NOT NULL,
1199    -- Parameters for the tag, presently unused
1200    ct_params nvarchar(max) NULL
1201  );
1202  
1203  CREATE UNIQUE INDEX /*i*/change_tag_rc_tag ON /*_*/change_tag (ct_rc_id,ct_tag);
1204  CREATE UNIQUE INDEX /*i*/change_tag_log_tag ON /*_*/change_tag (ct_log_id,ct_tag);
1205  CREATE UNIQUE INDEX /*i*/change_tag_rev_tag ON /*_*/change_tag (ct_rev_id,ct_tag);
1206  -- Covering index, so we can pull all the info only out of the index.
1207  CREATE INDEX /*i*/change_tag_tag_id ON /*_*/change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
1208  
1209  
1210  -- Rollup table to pull a LIST of tags simply without ugly GROUP_CONCAT
1211  -- that only works on MySQL 4.1+
1212  CREATE TABLE /*_*/tag_summary (
1213    -- RCID for the change
1214    ts_rc_id int NULL REFERENCES /*_*/recentchanges(rc_id),
1215    -- LOGID for the change
1216    ts_log_id int NULL REFERENCES /*_*/logging(log_id),
1217    -- REVID for the change
1218    ts_rev_id int NULL REFERENCES /*_*/revision(rev_id),
1219    -- Comma-separated list of tags
1220    ts_tags nvarchar(max) NOT NULL
1221  );
1222  
1223  CREATE UNIQUE INDEX /*i*/tag_summary_rc_id ON /*_*/tag_summary (ts_rc_id);
1224  CREATE UNIQUE INDEX /*i*/tag_summary_log_id ON /*_*/tag_summary (ts_log_id);
1225  CREATE UNIQUE INDEX /*i*/tag_summary_rev_id ON /*_*/tag_summary (ts_rev_id);
1226  
1227  
1228  CREATE TABLE /*_*/valid_tag (
1229    vt_tag nvarchar(255) NOT NULL PRIMARY KEY
1230  );
1231  
1232  -- Table for storing localisation data
1233  CREATE TABLE /*_*/l10n_cache (
1234    -- Language code
1235    lc_lang nvarchar(32) NOT NULL,
1236    -- Cache key
1237    lc_key nvarchar(255) NOT NULL,
1238    -- Value
1239    lc_value varbinary(max) NOT NULL
1240  );
1241  CREATE INDEX /*i*/lc_lang_key ON /*_*/l10n_cache (lc_lang, lc_key);
1242  
1243  -- Table for caching JSON message texts for the resource loader
1244  CREATE TABLE /*_*/msg_resource (
1245    -- Resource name
1246    mr_resource nvarchar(255) NOT NULL,
1247    -- Language code
1248    mr_lang nvarchar(32) NOT NULL,
1249    -- JSON blob
1250    mr_blob varbinary(max) NOT NULL,
1251    -- Timestamp of last update
1252    mr_timestamp varchar(14) NOT NULL
1253  );
1254  CREATE UNIQUE INDEX /*i*/mr_resource_lang ON /*_*/msg_resource (mr_resource, mr_lang);
1255  
1256  -- Table for administering which message is contained in which resource
1257  CREATE TABLE /*_*/msg_resource_links (
1258    mrl_resource varbinary(255) NOT NULL,
1259    -- Message key
1260    mrl_message varbinary(255) NOT NULL
1261  );
1262  CREATE UNIQUE INDEX /*i*/mrl_message_resource ON /*_*/msg_resource_links (mrl_message, mrl_resource);
1263  
1264  -- Table caching which local files a module depends on that aren't
1265  -- registered directly, used for fast retrieval of file dependency.
1266  -- Currently only used for tracking images that CSS depends on
1267  CREATE TABLE /*_*/module_deps (
1268    -- Module name
1269    md_module nvarchar(255) NOT NULL,
1270    -- Skin name
1271    md_skin nvarchar(32) NOT NULL,
1272    -- JSON nvarchar(max) with file dependencies
1273    md_deps nvarchar(max) NOT NULL
1274  );
1275  CREATE UNIQUE INDEX /*i*/md_module_skin ON /*_*/module_deps (md_module, md_skin);
1276  
1277  -- Holds all the sites known to the wiki.
1278  CREATE TABLE /*_*/sites (
1279    -- Numeric id of the site
1280    site_id                    int        NOT NULL PRIMARY KEY IDENTITY,
1281  
1282    -- Global identifier for the site, ie 'enwiktionary'
1283    site_global_key            nvarchar(32)       NOT NULL,
1284  
1285    -- Type of the site, ie 'mediawiki'
1286    site_type                  nvarchar(32)       NOT NULL,
1287  
1288    -- Group of the site, ie 'wikipedia'
1289    site_group                 nvarchar(32)       NOT NULL,
1290  
1291    -- Source of the site data, ie 'local', 'wikidata', 'my-magical-repo'
1292    site_source                nvarchar(32)       NOT NULL,
1293  
1294    -- Language code of the sites primary language.
1295    site_language              nvarchar(32)       NOT NULL,
1296  
1297    -- Protocol of the site, ie 'http://', 'irc://', '//'
1298    -- This field is an index for lookups and is build from type specific data in site_data.
1299    site_protocol              nvarchar(32)       NOT NULL,
1300  
1301    -- Domain of the site in reverse order, ie 'org.mediawiki.www.'
1302    -- This field is an index for lookups and is build from type specific data in site_data.
1303    site_domain                NVARCHAR(255)        NOT NULL,
1304  
1305    -- Type dependent site data.
1306    site_data                  nvarchar(max)                NOT NULL,
1307  
1308    -- If site.tld/path/key:pageTitle should forward users to  the page on
1309    -- the actual site, where "key" is the local identifier.
1310    site_forward              bit                NOT NULL,
1311  
1312    -- Type dependent site config.
1313    -- For instance if template transclusion should be allowed if it's a MediaWiki.
1314    site_config               nvarchar(max)                NOT NULL
1315  );
1316  
1317  CREATE UNIQUE INDEX /*i*/sites_global_key ON /*_*/sites (site_global_key);
1318  CREATE INDEX /*i*/sites_type ON /*_*/sites (site_type);
1319  CREATE INDEX /*i*/sites_group ON /*_*/sites (site_group);
1320  CREATE INDEX /*i*/sites_source ON /*_*/sites (site_source);
1321  CREATE INDEX /*i*/sites_language ON /*_*/sites (site_language);
1322  CREATE INDEX /*i*/sites_protocol ON /*_*/sites (site_protocol);
1323  CREATE INDEX /*i*/sites_domain ON /*_*/sites (site_domain);
1324  CREATE INDEX /*i*/sites_forward ON /*_*/sites (site_forward);
1325  
1326  -- Links local site identifiers to their corresponding site.
1327  CREATE TABLE /*_*/site_identifiers (
1328    -- Key on site.site_id
1329    si_site                    int        NOT NULL REFERENCES /*_*/sites(site_id) ON DELETE CASCADE,
1330  
1331    -- local key type, ie 'interwiki' or 'langlink'
1332    si_type                    nvarchar(32)       NOT NULL,
1333  
1334    -- local key value, ie 'en' or 'wiktionary'
1335    si_key                     nvarchar(32)       NOT NULL
1336  );
1337  
1338  CREATE UNIQUE INDEX /*i*/site_ids_type ON /*_*/site_identifiers (si_type, si_key);
1339  CREATE INDEX /*i*/site_ids_site ON /*_*/site_identifiers (si_site);
1340  CREATE INDEX /*i*/site_ids_key ON /*_*/site_identifiers (si_key);