maintenance/ibm_db2/tables.sql

   1 -- IBM DB2
   2
   3 -- SQL to create the initial tables for the MediaWiki database.
   4 -- This is read and executed by the install script; you should
   5 -- not have to run it by itself unless doing a manual install.
   6
   7 -- Notes:
   8 --  * DB2 will convert all table and column names to all caps internally.
   9 --  * DB2 has a 32k limit on SQL filesize, so it may be necessary
  10 --     to split this into two files soon.
  11
  12
  13 CREATE TABLE user (
  14   -- Needs to start with 0
  15   user_id                   BIGINT
  16     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
  17   user_name                 VARCHAR(255) NOT NULL UNIQUE,
  18   user_real_name            VARCHAR(255),
  19   user_password             VARCHAR(1024),
  20   user_newpassword          VARCHAR(1024),
  21   user_newpass_time         TIMESTAMP(3),
  22   user_token                VARCHAR(255),
  23   user_email                VARCHAR(1024),
  24   user_email_token          VARCHAR(255),
  25   user_email_token_expires  TIMESTAMP(3),
  26   user_email_authenticated  TIMESTAMP(3),
  27   -- obsolete, replace by user_properties table
  28   -- user_options              CLOB(64K) INLINE LENGTH 4096,
  29   user_touched              TIMESTAMP(3),
  30   user_registration         TIMESTAMP(3),
  31   user_editcount            INTEGER
  32 );
  33 CREATE INDEX user_email_token_idx
  34   ON user (user_email_token);
  35 CREATE UNIQUE INDEX user_include_idx
  36   ON user (user_id)
  37   INCLUDE (user_name, user_real_name, user_password, user_newpassword,
  38     user_newpass_time, user_token,
  39     user_email, user_email_token, user_email_token_expires,
  40     user_email_authenticated,
  41     user_touched, user_registration, user_editcount);
  42 CREATE UNIQUE INDEX user_email
  43   ON user (user_email);
  44
  45
  46
  47 -- Create a dummy user to satisfy fk contraints especially with revisions
  48 INSERT INTO user(
  49   user_name,    user_real_name,           user_password,  user_newpassword,   user_newpass_time,
  50   user_email,   user_email_authenticated, user_token,     user_registration,  user_editcount
  51 )
  52 VALUES (
  53   'Anonymous',  '',                       NULL,           NULL,               CURRENT_TIMESTAMP,
  54   NULL,         NULL,                     NULL,           CURRENT_TIMESTAMP,  0
  55 );
  56
  57
  58
  59 CREATE TABLE user_groups (
  60   ug_user   BIGINT NOT NULL DEFAULT 0,
  61   --    REFERENCES user(user_id) ON DELETE CASCADE,
  62   ug_group  VARCHAR(255) NOT NULL
  63 );
  64 CREATE INDEX user_groups_unique
  65   ON user_groups (ug_user, ug_group);
  66
  67
  68
  69 CREATE TABLE user_newtalk (
  70   -- registered users key
  71   user_id              BIGINT NOT NULL DEFAULT 0,
  72   --  REFERENCES user(user_id) ON DELETE CASCADE,
  73   -- anonymous users key
  74   user_ip              VARCHAR(40),
  75   user_last_timestamp  TIMESTAMP(3)
  76 );
  77 CREATE INDEX user_newtalk_id_idx
  78   ON user_newtalk (user_id);
  79 CREATE INDEX user_newtalk_ip_idx
  80   ON user_newtalk (user_ip);
  81 CREATE UNIQUE INDEX user_newtalk_include_idx
  82   ON user_newtalk (user_id, user_ip)
  83   INCLUDE (user_last_timestamp);
  84
  85
  86
  87 CREATE TABLE page (
  88   page_id            BIGINT
  89     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
  90   page_namespace     SMALLINT NOT NULL,
  91   page_title         VARCHAR(255) NOT NULL,
  92   page_restrictions  VARCHAR(1024),
  93   page_counter       BIGINT NOT NULL DEFAULT 0,
  94   page_is_redirect   SMALLINT NOT NULL DEFAULT 0,
  95   page_is_new        SMALLINT NOT NULL DEFAULT 0,
  96   page_random        NUMERIC(15,14) NOT NULL,
  97   page_touched       TIMESTAMP(3),
  98   page_latest        BIGINT NOT NULL, -- FK?
  99   page_len           BIGINT NOT NULL
 100 );
 101 CREATE UNIQUE INDEX page_unique_name
 102   ON page (page_namespace, page_title);
 103 CREATE INDEX page_random_idx
 104   ON page (page_random);
 105 CREATE INDEX page_len_idx
 106   ON page (page_len);
 107 CREATE UNIQUE INDEX page_id_include
 108   ON page (page_id)
 109   INCLUDE (page_namespace, page_title, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);
 110 CREATE UNIQUE INDEX page_name_include
 111   ON page (page_namespace, page_title)
 112   INCLUDE (page_id, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);
 113
 114
 115
 116 CREATE TABLE revision (
 117   rev_id            BIGINT
 118     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 119   rev_page          BIGINT NOT NULL DEFAULT 0,
 120   --      REFERENCES page (page_id) ON DELETE CASCADE,
 121   rev_text_id       BIGINT, -- FK
 122   rev_comment       VARCHAR(1024),
 123   rev_user          BIGINT NOT NULL DEFAULT 0,
 124   --  REFERENCES user(user_id) ON DELETE RESTRICT,
 125   rev_user_text     VARCHAR(255) NOT NULL,
 126   rev_timestamp     TIMESTAMP(3) NOT NULL,
 127   rev_minor_edit    SMALLINT NOT NULL DEFAULT 0,
 128   rev_deleted       SMALLINT NOT NULL DEFAULT 0,
 129   rev_len           BIGINT,
 130   rev_parent_id     BIGINT DEFAULT NULL,
 131   rev_sha1          VARCHAR(255) NOT NULL DEFAULT ''
 132 );
 133 CREATE UNIQUE INDEX revision_unique
 134   ON revision (rev_page, rev_id);
 135 CREATE INDEX rev_text_id_idx
 136   ON revision (rev_text_id);
 137 CREATE INDEX rev_timestamp_idx
 138   ON revision (rev_timestamp);
 139 CREATE INDEX rev_user_idx
 140   ON revision (rev_user);
 141 CREATE INDEX rev_user_text_idx
 142   ON revision (rev_user_text);
 143
 144
 145
 146 CREATE TABLE text ( -- replaces reserved word 'text'
 147   old_id     INTEGER
 148     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 149   old_text   CLOB(16M) INLINE LENGTH 4096,
 150   old_flags  VARCHAR(1024)
 151 );
 152
 153
 154
 155 CREATE TABLE page_restrictions (
 156   pr_id      BIGINT
 157     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 158   pr_page    INTEGER NOT NULL DEFAULT 0,
 159   --(used to be nullable)
 160   --  REFERENCES page (page_id) ON DELETE CASCADE,
 161   pr_type    VARCHAR(60) NOT NULL,
 162   pr_level   VARCHAR(60) NOT NULL,
 163   pr_cascade SMALLINT NOT NULL,
 164   pr_user    INTEGER,
 165   pr_expiry  TIMESTAMP(3)
 166   --PRIMARY KEY (pr_page, pr_type)
 167 );
 168 --ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page, pr_type);
 169 CREATE UNIQUE INDEX pr_pagetype
 170   ON page_restrictions (pr_page, pr_type);
 171 CREATE INDEX pr_typelevel
 172   ON page_restrictions (pr_type, pr_level);
 173 CREATE INDEX pr_level
 174   ON page_restrictions (pr_level);
 175 CREATE INDEX pr_cascade
 176   ON page_restrictions (pr_cascade);
 177
 178
 179
 180 CREATE TABLE page_props (
 181   pp_page      INTEGER NOT NULL DEFAULT 0,
 182   -- REFERENCES page (page_id) ON DELETE CASCADE,
 183   pp_propname  VARCHAR(255) NOT NULL,
 184   pp_value     CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 185   PRIMARY KEY (pp_page, pp_propname)
 186 );
 187 CREATE INDEX page_props_propname
 188   ON page_props (pp_propname);
 189
 190
 191
 192 CREATE TABLE archive (
 193   ar_namespace   SMALLINT NOT NULL,
 194   ar_title       VARCHAR(255) NOT NULL,
 195   ar_text        CLOB(16M) INLINE LENGTH 4096,
 196   ar_comment     VARCHAR(1024),
 197   ar_user        BIGINT NOT NULL,
 198   -- no foreign keys in MySQL
 199   -- REFERENCES user(user_id) ON DELETE SET NULL,
 200   ar_user_text   VARCHAR(255) NOT NULL,
 201   ar_timestamp   TIMESTAMP(3) NOT NULL,
 202   ar_minor_edit  SMALLINT NOT NULL DEFAULT 0,
 203   ar_flags       VARCHAR(1024),
 204   ar_rev_id      INTEGER,
 205   ar_text_id     INTEGER,
 206   ar_deleted     SMALLINT NOT NULL DEFAULT 0,
 207   ar_len         INTEGER,
 208   ar_page_id     INTEGER,
 209   ar_parent_id   INTEGER,
 210   ar_sha1        VARCHAR(255) NOT NULL DEFAULT ''
 211 );
 212 CREATE INDEX archive_name_title_timestamp
 213   ON archive (ar_namespace, ar_title, ar_timestamp);
 214 CREATE INDEX archive_user_text
 215   ON archive (ar_user_text);
 216
 217
 218
 219 CREATE TABLE redirect (
 220   rd_from       BIGINT NOT NULL
 221     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 222   --REFERENCES page(page_id) ON DELETE CASCADE,
 223   rd_namespace  SMALLINT NOT NULL DEFAULT 0,
 224   rd_title      VARCHAR(255) NOT NULL DEFAULT '',
 225   rd_interwiki  VARCHAR(32),
 226   rd_fragment   VARCHAR(255)
 227 );
 228 CREATE INDEX redirect_ns_title
 229   ON redirect (rd_namespace, rd_title, rd_from);
 230
 231
 232 CREATE TABLE pagelinks (
 233   pl_from       BIGINT NOT NULL DEFAULT 0,
 234   -- REFERENCES page(page_id) ON DELETE CASCADE,
 235   pl_namespace  SMALLINT NOT NULL,
 236   pl_title      VARCHAR(255) NOT NULL
 237 );
 238 CREATE UNIQUE INDEX pagelink_unique
 239   ON pagelinks (pl_from, pl_namespace, pl_title);
 240
 241
 242
 243 CREATE TABLE templatelinks (
 244   tl_from       BIGINT NOT NULL DEFAULT 0,
 245   --  REFERENCES page(page_id) ON DELETE CASCADE,
 246   tl_namespace  SMALLINT NOT NULL,
 247   tl_title      VARCHAR(255) NOT NULL
 248 );
 249 CREATE UNIQUE INDEX templatelinks_unique
 250   ON templatelinks (tl_namespace, tl_title, tl_from);
 251 CREATE UNIQUE INDEX tl_from_idx
 252   ON templatelinks (tl_from, tl_namespace, tl_title);
 253
 254
 255
 256 CREATE TABLE imagelinks (
 257   il_from  BIGINT NOT NULL DEFAULT 0,
 258   -- REFERENCES page(page_id) ON DELETE CASCADE,
 259   il_to    VARCHAR(255) NOT NULL
 260 );
 261 CREATE UNIQUE INDEX il_from_idx
 262   ON imagelinks (il_to, il_from);
 263 CREATE UNIQUE INDEX il_to_idx
 264   ON imagelinks (il_from, il_to);
 265
 266
 267
 268 CREATE TABLE categorylinks (
 269   cl_from           BIGINT NOT NULL DEFAULT 0,
 270   -- REFERENCES page(page_id) ON DELETE CASCADE,
 271   cl_to             VARCHAR(255) NOT NULL,
 272   -- cl_sortkey has to be at least 86 wide
 273   -- in order to be compatible with the old MySQL schema from MW 1.10
 274   --cl_sortkey    VARCHAR(86),
 275   cl_sortkey        VARCHAR(230) FOR BIT DATA NOT NULL,
 276   cl_sortkey_prefix VARCHAR(255) FOR BIT DATA NOT NULL,
 277   cl_timestamp      TIMESTAMP(3) NOT NULL,
 278   cl_collation      VARCHAR(32) FOR BIT DATA NOT NULL,
 279   cl_type           VARCHAR(6) FOR BIT DATA NOT NULL
 280 );
 281 CREATE UNIQUE INDEX cl_from
 282   ON categorylinks (cl_from, cl_to);
 283 CREATE INDEX cl_sortkey
 284   ON categorylinks (cl_to, cl_sortkey, cl_from);
 285
 286
 287
 288 CREATE TABLE externallinks (
 289   el_from   BIGINT NOT NULL DEFAULT 0,
 290   -- REFERENCES page(page_id) ON DELETE CASCADE,
 291   el_to     VARCHAR(1024) NOT NULL,
 292   el_index  VARCHAR(1024) NOT NULL
 293 );
 294 CREATE INDEX externallinks_from_to
 295   ON externallinks (el_from, el_to);
 296 CREATE INDEX externallinks_index
 297   ON externallinks (el_index);
 298
 299
 300
 301 --
 302 -- Track external user accounts, if ExternalAuth is used
 303 --
 304 CREATE TABLE external_user (
 305   -- Foreign key to user_id
 306   eu_local_id      BIGINT NOT NULL
 307     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 308
 309   -- Some opaque identifier provided by the external database
 310   eu_external_id    VARCHAR(255) NOT NULL
 311 );
 312 CREATE UNIQUE INDEX eu_external_id_idx
 313   ON external_user (eu_external_id)
 314   INCLUDE (eu_local_id);
 315 CREATE UNIQUE INDEX eu_local_id_idx
 316   ON external_user (eu_local_id)
 317   INCLUDE (eu_external_id);
 318
 319
 320
 321 CREATE TABLE langlinks (
 322   ll_from    BIGINT NOT NULL DEFAULT 0,
 323   -- REFERENCES page (page_id) ON DELETE CASCADE,
 324   ll_lang    VARCHAR(20),
 325   ll_title   VARCHAR(255)
 326 );
 327 CREATE UNIQUE INDEX langlinks_unique
 328   ON langlinks (ll_from, ll_lang);
 329 CREATE INDEX langlinks_lang_title
 330   ON langlinks (ll_lang, ll_title);
 331
 332
 333
 334 CREATE TABLE site_stats (
 335   ss_row_id         BIGINT NOT NULL UNIQUE,
 336   ss_total_views    BIGINT DEFAULT 0,
 337   ss_total_edits    BIGINT DEFAULT 0,
 338   ss_good_articles  BIGINT DEFAULT 0,
 339   ss_total_pages    INTEGER DEFAULT -1,
 340   ss_users          INTEGER DEFAULT -1,
 341   ss_active_users   INTEGER DEFAULT -1,
 342   ss_admins         INTEGER DEFAULT -1,
 343   ss_images         INTEGER DEFAULT 0
 344 );
 345
 346
 347
 348 CREATE TABLE hitcounter (
 349   hc_id  BIGINT NOT NULL
 350 );
 351
 352
 353
 354 CREATE TABLE ipblocks (
 355   ipb_id                INTEGER NOT NULL
 356     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 357   ipb_address           VARCHAR(1024),
 358   ipb_user              BIGINT NOT NULL DEFAULT 0,
 359   --           REFERENCES user(user_id) ON DELETE SET NULL,
 360   ipb_by                BIGINT NOT NULL DEFAULT 0,
 361   --  REFERENCES user(user_id) ON DELETE CASCADE,
 362   ipb_by_text           VARCHAR(255) NOT NULL DEFAULT '',
 363   ipb_reason            VARCHAR(1024) NOT NULL,
 364   ipb_timestamp         TIMESTAMP(3) NOT NULL,
 365   ipb_auto              SMALLINT NOT NULL DEFAULT 0,
 366   ipb_anon_only         SMALLINT NOT NULL DEFAULT 0,
 367   ipb_create_account    SMALLINT NOT NULL DEFAULT 1,
 368   ipb_enable_autoblock  SMALLINT NOT NULL DEFAULT 1,
 369   ipb_expiry            TIMESTAMP(3) NOT NULL,
 370   ipb_range_start       VARCHAR(1024),
 371   ipb_range_end         VARCHAR(1024),
 372   ipb_deleted           SMALLINT NOT NULL DEFAULT 0,
 373   ipb_block_email       SMALLINT NOT NULL DEFAULT 0,
 374   ipb_allow_usertalk    SMALLINT NOT NULL DEFAULT 0,
 375   ipb_parent_block_id             INTEGER DEFAULT NULL
 376   -- REFERENCES ipblocks(ipb_id) ON DELETE SET NULL
 377
 378 );
 379 CREATE INDEX ipb_address
 380   ON ipblocks (ipb_address);
 381 CREATE INDEX ipb_user
 382   ON ipblocks (ipb_user);
 383 CREATE INDEX ipb_range
 384   ON ipblocks (ipb_range_start, ipb_range_end);
 385
 386
 387
 388 CREATE TABLE image (
 389   img_name         VARCHAR(255) NOT NULL
 390     PRIMARY KEY,
 391   img_size         BIGINT NOT NULL,
 392   img_width        INTEGER NOT NULL,
 393   img_height       INTEGER NOT NULL,
 394   img_metadata     CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 395   img_bits         SMALLINT,
 396   img_media_type   VARCHAR(255),
 397   img_major_mime   VARCHAR(255) DEFAULT 'unknown',
 398   img_minor_mime   VARCHAR(32) DEFAULT 'unknown',
 399   img_description  VARCHAR(1024) NOT NULL DEFAULT '',
 400   img_user         BIGINT NOT NULL DEFAULT 0,
 401   --         REFERENCES user(user_id) ON DELETE SET NULL,
 402   img_user_text    VARCHAR(255) NOT NULL DEFAULT '',
 403   img_timestamp    TIMESTAMP(3),
 404   img_sha1         VARCHAR(255) NOT NULL DEFAULT ''
 405 );
 406 CREATE INDEX img_size_idx
 407   ON image (img_size);
 408 CREATE INDEX img_timestamp_idx
 409   ON image (img_timestamp);
 410 CREATE INDEX img_sha1
 411   ON image (img_sha1);
 412
 413
 414 CREATE TABLE oldimage (
 415   oi_name          VARCHAR(255) NOT NULL DEFAULT '',
 416   oi_archive_name  VARCHAR(255) NOT NULL,
 417   oi_size          BIGINT NOT NULL,
 418   oi_width         INTEGER NOT NULL,
 419   oi_height        INTEGER NOT NULL,
 420   oi_bits          SMALLINT NOT NULL,
 421   oi_description   VARCHAR(1024),
 422   oi_user          BIGINT NOT NULL DEFAULT 0,
 423   --            REFERENCES user(user_id) ON DELETE SET NULL,
 424   oi_user_text     VARCHAR(255) NOT NULL,
 425   oi_timestamp     TIMESTAMP(3) NOT NULL,
 426   oi_metadata      CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 427   oi_media_type    VARCHAR(255),
 428   oi_major_mime    VARCHAR(255) NOT NULL DEFAULT 'unknown',
 429   oi_minor_mime    VARCHAR(255) NOT NULL DEFAULT 'unknown',
 430   oi_deleted       SMALLINT NOT NULL DEFAULT 0,
 431   oi_sha1          VARCHAR(255) NOT NULL DEFAULT ''
 432   --FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
 433 );
 434 CREATE INDEX oi_name_timestamp
 435   ON oldimage (oi_name, oi_timestamp);
 436 CREATE INDEX oi_name_archive_name
 437   ON oldimage (oi_name, oi_archive_name);
 438 CREATE INDEX oi_sha1
 439   ON oldimage (oi_sha1);
 440
 441
 442
 443 CREATE TABLE filearchive (
 444   fa_id                 INTEGER NOT NULL
 445     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 446   fa_name               VARCHAR(255) NOT NULL,
 447   fa_archive_name       VARCHAR(255),
 448   fa_storage_group      VARCHAR(255),
 449   fa_storage_key        VARCHAR(64) DEFAULT '',
 450   fa_deleted_user       BIGINT NOT NULL DEFAULT 0,
 451   --            REFERENCES user(user_id) ON DELETE SET NULL,
 452   fa_deleted_timestamp  TIMESTAMP(3) NOT NULL,
 453   fa_deleted_reason     VARCHAR(255),
 454   fa_size               BIGINT NOT NULL,
 455   fa_width              INTEGER NOT NULL,
 456   fa_height             INTEGER NOT NULL,
 457   fa_metadata           CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 458   fa_bits               SMALLINT,
 459   fa_media_type         VARCHAR(255),
 460   fa_major_mime         VARCHAR(255) DEFAULT 'unknown',
 461   fa_minor_mime         VARCHAR(255) DEFAULT 'unknown',
 462   fa_description        VARCHAR(1024) NOT NULL,
 463   fa_user               BIGINT NOT NULL DEFAULT 0,
 464   --            REFERENCES user(user_id) ON DELETE SET NULL,
 465   fa_user_text          VARCHAR(255) NOT NULL,
 466   fa_timestamp          TIMESTAMP(3),
 467   fa_deleted            SMALLINT NOT NULL DEFAULT 0
 468 );
 469 CREATE INDEX fa_name_time
 470   ON filearchive (fa_name, fa_timestamp);
 471 CREATE INDEX fa_dupe
 472   ON filearchive (fa_storage_group, fa_storage_key);
 473 CREATE INDEX fa_notime
 474   ON filearchive (fa_deleted_timestamp);
 475 CREATE INDEX fa_nouser
 476   ON filearchive (fa_deleted_user);
 477
 478
 479
 480 CREATE TABLE recentchanges (
 481   rc_id              INTEGER NOT NULL
 482     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 483   rc_timestamp       TIMESTAMP(3) NOT NULL,
 484   rc_cur_time        TIMESTAMP(3) NOT NULL,
 485   rc_user            BIGINT NOT NULL DEFAULT 0,
 486   --        REFERENCES user(user_id) ON DELETE SET NULL,
 487   rc_user_text       VARCHAR(255) NOT NULL,
 488   rc_namespace       SMALLINT NOT NULL,
 489   rc_title           VARCHAR(255) NOT NULL,
 490   rc_comment         VARCHAR(255),
 491   rc_minor           SMALLINT NOT NULL DEFAULT 0,
 492   rc_bot             SMALLINT NOT NULL DEFAULT 0,
 493   rc_new             SMALLINT NOT NULL DEFAULT 0,
 494   rc_cur_id          BIGINT NOT NULL DEFAULT 0,
 495   --            REFERENCES page(page_id) ON DELETE SET NULL,
 496   rc_this_oldid      BIGINT NOT NULL,
 497   rc_last_oldid      BIGINT NOT NULL,
 498   rc_type            SMALLINT NOT NULL DEFAULT 0,
 499   rc_moved_to_ns     SMALLINT,
 500   rc_moved_to_title  VARCHAR(255),
 501   rc_patrolled       SMALLINT NOT NULL DEFAULT 0,
 502   rc_ip              VARCHAR(40),  -- was CIDR type
 503   rc_old_len         INTEGER,
 504   rc_new_len         INTEGER,
 505   rc_deleted         SMALLINT NOT NULL DEFAULT 0,
 506   rc_logid           BIGINT NOT NULL DEFAULT 0,
 507   rc_log_type        VARCHAR(255),
 508   rc_log_action      VARCHAR(255),
 509   rc_params          CLOB(64K) INLINE LENGTH 4096
 510
 511 );
 512 CREATE INDEX rc_timestamp
 513   ON recentchanges (rc_timestamp);
 514 CREATE INDEX rc_namespace_title
 515   ON recentchanges (rc_namespace, rc_title);
 516 CREATE INDEX rc_cur_id
 517   ON recentchanges (rc_cur_id);
 518 CREATE INDEX new_name_timestamp
 519   ON recentchanges (rc_new, rc_namespace, rc_timestamp);
 520 CREATE INDEX rc_ip
 521   ON recentchanges (rc_ip);
 522
 523
 524
 525 CREATE TABLE watchlist (
 526   wl_user                   BIGINT NOT NULL DEFAULT 0,
 527   --  REFERENCES user(user_id) ON DELETE CASCADE,
 528   wl_namespace              SMALLINT NOT NULL DEFAULT 0,
 529   wl_title                  VARCHAR(255) NOT NULL,
 530   wl_notificationtimestamp  TIMESTAMP(3)
 531 );
 532 CREATE UNIQUE INDEX wl_user_namespace_title
 533   ON watchlist (wl_namespace, wl_title, wl_user);
 534
 535
 536
 537 CREATE TABLE interwiki (
 538   iw_prefix  VARCHAR(32) NOT NULL UNIQUE,
 539   iw_url     CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 540   iw_api     CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 541   iw_wikiid  VARCHAR(64) NOT NULL,
 542   iw_local   SMALLINT NOT NULL,
 543   iw_trans   SMALLINT NOT NULL DEFAULT 0
 544 );
 545
 546
 547
 548 CREATE TABLE querycache (
 549   qc_type       VARCHAR(255) NOT NULL,
 550   qc_value      BIGINT NOT NULL,
 551   qc_namespace  INTEGER NOT NULL,
 552   qc_title      VARCHAR(255) NOT NULL
 553 );
 554 CREATE INDEX querycache_type_value
 555   ON querycache (qc_type, qc_value);
 556
 557
 558
 559 CREATE TABLE querycache_info (
 560   qci_type        VARCHAR(255) UNIQUE NOT NULL,
 561   qci_timestamp   TIMESTAMP(3)
 562 );
 563
 564
 565
 566 CREATE TABLE querycachetwo (
 567   qcc_type          VARCHAR(255) NOT NULL,
 568   qcc_value         BIGINT NOT NULL DEFAULT 0,
 569   qcc_namespace     INTEGER NOT NULL DEFAULT 0,
 570   qcc_title         VARCHAR(255) NOT NULL DEFAULT '',
 571   qcc_namespacetwo  INTEGER NOT NULL DEFAULT 0,
 572   qcc_titletwo      VARCHAR(255) NOT NULL DEFAULT ''
 573 );
 574 CREATE INDEX querycachetwo_type_value
 575   ON querycachetwo (qcc_type, qcc_value);
 576 CREATE INDEX querycachetwo_title
 577   ON querycachetwo (qcc_type, qcc_namespace, qcc_title);
 578 CREATE INDEX querycachetwo_titletwo
 579   ON querycachetwo (qcc_type, qcc_namespacetwo, qcc_titletwo);
 580
 581
 582
 583 CREATE TABLE objectcache (
 584   keyname   VARCHAR(255) NOT NULL UNIQUE, -- was nullable
 585   value     CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 586   exptime   TIMESTAMP(3) NOT NULL
 587 );
 588 CREATE INDEX objectcacache_exptime
 589   ON objectcache (exptime);
 590
 591
 592
 593 CREATE TABLE transcache (
 594   tc_url       VARCHAR(255) NOT NULL UNIQUE,
 595   tc_contents  CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 596   tc_time      TIMESTAMP(3) NOT NULL
 597 );
 598
 599
 600
 601 CREATE TABLE logging (
 602   log_id          BIGINT NOT NULL
 603     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 604   log_type        VARCHAR(32) NOT NULL,
 605   log_action      VARCHAR(32) NOT NULL,
 606   log_timestamp   TIMESTAMP(3) NOT NULL,
 607   log_user        BIGINT NOT NULL DEFAULT 0,
 608   --                REFERENCES user(user_id) ON DELETE SET NULL,
 609   -- Name of the user who performed this action
 610   log_user_text   VARCHAR(255) NOT NULL DEFAULT '',
 611   log_namespace   SMALLINT NOT NULL,
 612   log_title       VARCHAR(255) NOT NULL,
 613   log_page        BIGINT,
 614   log_comment     VARCHAR(255),
 615   log_params      CLOB(64K) INLINE LENGTH 4096,
 616   log_deleted     SMALLINT NOT NULL DEFAULT 0
 617 );
 618 CREATE INDEX logging_type_name
 619   ON logging (log_type, log_timestamp);
 620 CREATE INDEX logging_user_time
 621   ON logging (log_timestamp, log_user);
 622 CREATE INDEX logging_page_time
 623   ON logging (log_namespace, log_title, log_timestamp);
 624 CREATE INDEX log_user_type_time
 625   ON logging (log_user, log_type, log_timestamp);
 626 CREATE INDEX log_page_id_time
 627   ON logging (log_page, log_timestamp);
 628 CREATE UNIQUE INDEX type_action
 629   ON logging (log_type, log_action, log_timestamp);
 630
 631
 632
 633 CREATE TABLE trackbacks (
 634   tb_id     INTEGER NOT NULL
 635     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 636   -- foreign key also in MySQL
 637   tb_page   INTEGER,
 638   -- REFERENCES page(page_id) ON DELETE CASCADE,
 639   tb_title  VARCHAR(255) NOT NULL,
 640   tb_url    CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 641   tb_ex     CLOB(64K) INLINE LENGTH 4096,
 642   tb_name   VARCHAR(255)
 643 );
 644 CREATE INDEX trackback_page
 645   ON trackbacks (tb_page);
 646
 647
 648
 649 CREATE TABLE job (
 650   job_id         BIGINT NOT NULL
 651     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 652   job_cmd        VARCHAR(255) NOT NULL,
 653   job_namespace  SMALLINT NOT NULL,
 654   job_title      VARCHAR(255) NOT NULL,
 655   job_params     CLOB(64K) INLINE LENGTH 4096 NOT NULL
 656 );
 657 CREATE INDEX job_cmd_namespace_title
 658   ON job (job_cmd, job_namespace, job_title);
 659
 660
 661
 662 --TODO
 663 --CREATE FUNCTION add_interwiki (TEXT, INT, SMALLINT) RETURNS INT LANGUAGE SQL AS
 664 --$mw$
 665 --  INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
 666 --  SELECT 1;
 667 --$mw$;
 668
 669
 670
 671 -- hack implementation
 672 -- should be replaced with OmniFind, Contains(), etc
 673 CREATE TABLE searchindex (
 674   si_page   BIGINT NOT NULL,
 675   si_title  VARCHAR(255) NOT NULL DEFAULT '',
 676   si_text   CLOB NOT NULL
 677 );
 678
 679
 680
 681 -- This table is not used unless profiling is turned on
 682 CREATE TABLE profiling (
 683   pf_count   INTEGER NOT NULL DEFAULT 0,
 684   pf_time    NUMERIC(18,10) NOT NULL DEFAULT 0,
 685   pf_memory  NUMERIC(18,10) NOT NULL DEFAULT 0,
 686   pf_name    VARCHAR(255) NOT NULL,
 687   pf_server  VARCHAR(255)
 688 );
 689 CREATE UNIQUE INDEX pf_name_server
 690   ON profiling (pf_name, pf_server);
 691
 692
 693
 694 CREATE TABLE protected_titles (
 695   pt_namespace   INTEGER NOT NULL,
 696   pt_title       VARCHAR(255) NOT NULL,
 697   pt_user        BIGINT NOT NULL DEFAULT 0,
 698   --       REFERENCES user(user_id) ON DELETE SET NULL,
 699   pt_reason      VARCHAR(1024),
 700   pt_timestamp   TIMESTAMP(3) NOT NULL,
 701   pt_expiry      TIMESTAMP(3),
 702   pt_create_perm VARCHAR(60) NOT NULL DEFAULT ''
 703 );
 704 CREATE UNIQUE INDEX protected_titles_unique
 705   ON protected_titles (pt_namespace, pt_title);
 706
 707
 708
 709 CREATE TABLE updatelog (
 710   ul_key VARCHAR(255) NOT NULL
 711     PRIMARY KEY
 712 );
 713
 714
 715
 716 CREATE TABLE category (
 717   cat_id       INTEGER NOT NULL
 718     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 719   cat_title    VARCHAR(255) NOT NULL,
 720   cat_pages    INTEGER NOT NULL DEFAULT 0,
 721   cat_subcats  INTEGER NOT NULL DEFAULT 0,
 722   cat_files    INTEGER NOT NULL DEFAULT 0,
 723   cat_hidden   SMALLINT NOT NULL DEFAULT 0
 724 );
 725 CREATE UNIQUE INDEX category_title
 726   ON category (cat_title);
 727 CREATE INDEX category_pages
 728   ON category (cat_pages);
 729
 730
 731
 732 -- A table to track tags for revisions, logs and recent changes.
 733 CREATE TABLE change_tag (
 734   ct_rc_id    INTEGER,
 735   ct_log_id   INTEGER,
 736   ct_rev_id   INTEGER,
 737   ct_tag      VARCHAR(255) NOT NULL,
 738   ct_params   CLOB(64K) INLINE LENGTH 4096
 739 );
 740 CREATE UNIQUE INDEX change_tag_rc_tag
 741   ON change_tag (ct_rc_id, ct_tag);
 742 CREATE UNIQUE INDEX change_tag_log_tag
 743   ON change_tag (ct_log_id, ct_tag);
 744 CREATE UNIQUE INDEX change_tag_rev_tag
 745   ON change_tag (ct_rev_id, ct_tag);
 746 -- Covering index, so we can pull all the info only out of the index.
 747 CREATE INDEX change_tag_tag_id
 748   ON change_tag (ct_tag, ct_rc_id, ct_rev_id, ct_log_id);
 749
 750
 751
 752 -- Rollup table to pull a LIST of tags simply
 753 CREATE TABLE tag_summary (
 754   ts_rc_id  INTEGER,
 755   ts_log_id INTEGER,
 756   ts_rev_id INTEGER,
 757   ts_tags   CLOB(64K) INLINE LENGTH 4096 NOT NULL
 758 );
 759 CREATE UNIQUE INDEX tag_summary_rc_id
 760   ON tag_summary (ts_rc_id);
 761 CREATE UNIQUE INDEX tag_summary_log_id
 762   ON tag_summary (ts_log_id);
 763 CREATE UNIQUE INDEX tag_summary_rev_id
 764   ON tag_summary (ts_rev_id);
 765
 766
 767
 768 CREATE TABLE valid_tag (
 769   vt_tag VARCHAR(255) NOT NULL
 770     PRIMARY KEY
 771 );
 772
 773
 774
 775 --
 776 -- User preferences and perhaps other fun stuff. :)
 777 -- Replaces the old user.user_options blob, with a couple nice properties:
 778 --
 779 -- 1) We only store non-default settings, so changes to the DEFAULTs
 780 --    are now reflected for everybody, not just new accounts.
 781 -- 2) We can more easily do bulk lookups, statistics, or modifications of
 782 --    saved options since it's a sane table structure.
 783 --
 784 CREATE TABLE user_properties (
 785   -- Foreign key to user.user_id
 786   up_user       BIGINT NOT NULL,
 787   -- Name of the option being saved. This is indexed for bulk lookup.
 788   up_property   VARCHAR(255) FOR BIT DATA NOT NULL,
 789   -- Property value as a string.
 790   up_value      CLOB(64K) INLINE LENGTH 4096
 791 );
 792 CREATE UNIQUE INDEX user_properties_user_property
 793   ON user_properties (up_user, up_property);
 794 CREATE INDEX user_properties_property
 795   ON user_properties (up_property);
 796
 797 CREATE TABLE log_search (
 798   -- The type of ID (rev ID, log ID, rev TIMESTAMP(3), username)
 799   ls_field    VARCHAR(32) FOR BIT DATA NOT NULL,
 800   -- The value of the ID
 801   ls_value    VARCHAR(255) NOT NULL,
 802   -- Key to log_id
 803   ls_log_id   BIGINT NOT NULL DEFAULT 0
 804 );
 805 CREATE UNIQUE INDEX ls_field_val
 806   ON log_search (ls_field, ls_value, ls_log_id);
 807 CREATE INDEX ls_log_id
 808   ON log_search (ls_log_id);
 809
 810
 811
 812 -- Table for storing localisation data
 813 CREATE TABLE l10n_cache (
 814   -- Language code
 815   lc_lang       VARCHAR(32) NOT NULL,
 816   -- Cache key
 817   lc_key        VARCHAR(255) NOT NULL,
 818   -- Value
 819   lc_value      CLOB(16M) INLINE LENGTH 4096 NOT NULL
 820 );
 821 CREATE INDEX lc_lang_key
 822   ON l10n_cache (lc_lang, lc_key);
 823
 824
 825
 826 CREATE TABLE msg_resource_links
 827 (
 828   mrl_resource  VARCHAR(255) FOR BIT DATA NOT NULL,
 829   mrl_message   VARCHAR(255) FOR BIT DATA NOT NULL
 830 );
 831 CREATE UNIQUE INDEX uq61_msg_resource_links
 832   ON msg_resource_links (mrl_message, mrl_resource);
 833 -- All DB2 indexes DEFAULT to allowing reverse scans
 834
 835
 836
 837 CREATE TABLE msg_resource
 838 (
 839   mr_resource   VARCHAR(255) FOR BIT DATA NOT NULL,
 840   mr_lang       VARCHAR(32) FOR BIT DATA NOT NULL,
 841   mr_blob       CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 842   mr_timestamp  TIMESTAMP(3) NOT NULL
 843 );
 844 CREATE UNIQUE INDEX uq81_msg_resource
 845   ON msg_resource (mr_resource, mr_lang);
 846 -- All DB2 indexes DEFAULT to allowing reverse scans
 847
 848
 849
 850 CREATE TABLE module_deps (
 851   md_module VARCHAR(255) FOR BIT DATA NOT NULL,
 852   md_skin   VARCHAR(32) FOR BIT DATA NOT NULL,
 853   md_deps   CLOB(16M) INLINE LENGTH 4096 NOT NULL
 854 );
 855 CREATE UNIQUE INDEX uq96_module_deps
 856   ON module_deps (md_module, md_skin);
 857 -- All DB2 indexes DEFAULT to allowing reverse scans
 858
 859
 860
 861 CREATE TABLE iwlinks
 862 (
 863   iwl_from    INTEGER NOT NULL,
 864   iwl_prefix  VARCHAR(20) FOR BIT DATA NOT NULL,
 865   iwl_title   VARCHAR(255) FOR BIT DATA NOT NULL
 866 );
 867
 868
 869
 870 --
 871 -- Store information about newly uploaded files before they're
 872 -- moved into the actual filestore
 873 --
 874 CREATE TABLE uploadstash (
 875   us_id           BIGINT NOT NULL
 876     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 877   -- the user who uploaded the file.
 878   us_user         BIGINT NOT NULL,
 879   -- file key. this is how applications actually search for the file.
 880   -- this might go away, or become the primary key.
 881   us_key          VARCHAR(255) NOT NULL,
 882   -- the original path
 883   us_orig_path    VARCHAR(255) NOT NULL,
 884   -- the temporary path at which the file is actually stored
 885   us_path         VARCHAR(255) NOT NULL,
 886   -- which type of upload the file came from (sometimes)
 887   us_source_type  VARCHAR(50),
 888   -- the date/time on which the file was added
 889   us_timestamp    TIMESTAMP(3) NOT NULL,
 890   us_status       VARCHAR(50) NOT NULL,
 891   -- file properties from File::getPropsFromPath.  these may prove unnecessary.
 892   --
 893   us_size         BIGINT NOT NULL,
 894   -- this hash comes from File::sha1Base36(), and is 31 characters
 895   us_sha1         VARCHAR(31) NOT NULL,
 896   us_mime         VARCHAR(255),
 897   -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table
 898   us_media_type   VARCHAR(30)
 899     CONSTRAINT my_constraint
 900       CHECK (
 901         us_media_type in (
 902           'UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA',
 903           'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'
 904       )
 905     ) DEFAULT NULL,
 906   -- image-specific properties
 907   us_image_width  BIGINT,
 908   us_image_height BIGINT,
 909   us_image_bits   INTEGER
 910 );
 911 -- sometimes there's a delete for all of a user's stuff.
 912 CREATE INDEX us_user
 913   ON uploadstash (us_user);
 914 -- pick out files by key, enforce key UNIQUEness
 915 CREATE UNIQUE INDEX us_key
 916   ON uploadstash (us_key);
 917 -- the abandoned upload cleanup script needs this
 918 CREATE INDEX us_timestamp
 919   ON uploadstash (us_timestamp);
 920
 921
 922
 923 -- Stores the groups the user has once belonged to.
 924 -- The user may still belong these groups. Check user_groups.
 925 CREATE TABLE user_former_groups (
 926   ufg_user   BIGINT NOT NULL DEFAULT 0,
 927   ufg_group  VARCHAR(16) FOR BIT DATA NOT NULL
 928 );
 929 CREATE UNIQUE INDEX ufg_user_group
 930   ON user_former_groups (ufg_user, ufg_group);
 931
 932
 933
 934 -- Table for holding configuration changes
 935 CREATE TABLE config (
 936   cf_name   VARCHAR(255) NOT NULL
 937     PRIMARY KEY,
 938   cf_value  CLOB(64K) INLINE LENGTH 4096 NOT NULL
 939 );
 940