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 CREATE INDEX rc_timestamp
 512   ON recentchanges (rc_timestamp);
 513 CREATE INDEX rc_namespace_title
 514   ON recentchanges (rc_namespace, rc_title);
 515 CREATE INDEX rc_cur_id
 516   ON recentchanges (rc_cur_id);
 517 CREATE INDEX new_name_timestamp
 518   ON recentchanges (rc_new, rc_namespace, rc_timestamp);
 519 CREATE INDEX rc_ip
 520   ON recentchanges (rc_ip);
 521
 522
 523
 524 CREATE TABLE watchlist (
 525   wl_user                   BIGINT NOT NULL DEFAULT 0,
 526   --  REFERENCES user(user_id) ON DELETE CASCADE,
 527   wl_namespace              SMALLINT NOT NULL DEFAULT 0,
 528   wl_title                  VARCHAR(255) NOT NULL,
 529   wl_notificationtimestamp  TIMESTAMP(3)
 530 );
 531 CREATE UNIQUE INDEX wl_user_namespace_title
 532   ON watchlist (wl_namespace, wl_title, wl_user);
 533
 534
 535
 536 CREATE TABLE interwiki (
 537   iw_prefix  VARCHAR(32) NOT NULL UNIQUE,
 538   iw_url     CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 539   iw_api     CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 540   iw_wikiid  VARCHAR(64) NOT NULL,
 541   iw_local   SMALLINT NOT NULL,
 542   iw_trans   SMALLINT NOT NULL DEFAULT 0
 543 );
 544
 545
 546
 547 CREATE TABLE querycache (
 548   qc_type       VARCHAR(255) NOT NULL,
 549   qc_value      BIGINT NOT NULL,
 550   qc_namespace  INTEGER NOT NULL,
 551   qc_title      VARCHAR(255) NOT NULL
 552 );
 553 CREATE INDEX querycache_type_value
 554   ON querycache (qc_type, qc_value);
 555
 556
 557
 558 CREATE TABLE querycache_info (
 559   qci_type        VARCHAR(255) UNIQUE NOT NULL,
 560   qci_timestamp   TIMESTAMP(3)
 561 );
 562
 563
 564
 565 CREATE TABLE querycachetwo (
 566   qcc_type          VARCHAR(255) NOT NULL,
 567   qcc_value         BIGINT NOT NULL DEFAULT 0,
 568   qcc_namespace     INTEGER NOT NULL DEFAULT 0,
 569   qcc_title         VARCHAR(255) NOT NULL DEFAULT '',
 570   qcc_namespacetwo  INTEGER NOT NULL DEFAULT 0,
 571   qcc_titletwo      VARCHAR(255) NOT NULL DEFAULT ''
 572 );
 573 CREATE INDEX querycachetwo_type_value
 574   ON querycachetwo (qcc_type, qcc_value);
 575 CREATE INDEX querycachetwo_title
 576   ON querycachetwo (qcc_type, qcc_namespace, qcc_title);
 577 CREATE INDEX querycachetwo_titletwo
 578   ON querycachetwo (qcc_type, qcc_namespacetwo, qcc_titletwo);
 579
 580
 581
 582 CREATE TABLE objectcache (
 583   keyname   VARCHAR(255) NOT NULL UNIQUE, -- was nullable
 584   value     CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 585   exptime   TIMESTAMP(3) NOT NULL
 586 );
 587 CREATE INDEX objectcacache_exptime
 588   ON objectcache (exptime);
 589
 590
 591
 592 CREATE TABLE transcache (
 593   tc_url       VARCHAR(255) NOT NULL UNIQUE,
 594   tc_contents  CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 595   tc_time      TIMESTAMP(3) NOT NULL
 596 );
 597
 598
 599
 600 CREATE TABLE logging (
 601   log_id          BIGINT NOT NULL
 602     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 603   log_type        VARCHAR(32) NOT NULL,
 604   log_action      VARCHAR(32) NOT NULL,
 605   log_timestamp   TIMESTAMP(3) NOT NULL,
 606   log_user        BIGINT NOT NULL DEFAULT 0,
 607   --                REFERENCES user(user_id) ON DELETE SET NULL,
 608   -- Name of the user who performed this action
 609   log_user_text   VARCHAR(255) NOT NULL DEFAULT '',
 610   log_namespace   SMALLINT NOT NULL,
 611   log_title       VARCHAR(255) NOT NULL,
 612   log_page        BIGINT,
 613   log_comment     VARCHAR(255),
 614   log_params      CLOB(64K) INLINE LENGTH 4096,
 615   log_deleted     SMALLINT NOT NULL DEFAULT 0
 616 );
 617 CREATE INDEX logging_type_name
 618   ON logging (log_type, log_timestamp);
 619 CREATE INDEX logging_user_time
 620   ON logging (log_timestamp, log_user);
 621 CREATE INDEX logging_page_time
 622   ON logging (log_namespace, log_title, log_timestamp);
 623 CREATE INDEX log_user_type_time
 624   ON logging (log_user, log_type, log_timestamp);
 625 CREATE INDEX log_page_id_time
 626   ON logging (log_page, log_timestamp);
 627 CREATE UNIQUE INDEX type_action
 628   ON logging (log_type, log_action, log_timestamp);
 629
 630
 631
 632 CREATE TABLE trackbacks (
 633   tb_id     INTEGER NOT NULL
 634     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 635   -- foreign key also in MySQL
 636   tb_page   INTEGER,
 637   -- REFERENCES page(page_id) ON DELETE CASCADE,
 638   tb_title  VARCHAR(255) NOT NULL,
 639   tb_url    CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 640   tb_ex     CLOB(64K) INLINE LENGTH 4096,
 641   tb_name   VARCHAR(255)
 642 );
 643 CREATE INDEX trackback_page
 644   ON trackbacks (tb_page);
 645
 646
 647
 648 CREATE TABLE job (
 649   job_id         BIGINT NOT NULL
 650     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 651   job_cmd        VARCHAR(255) NOT NULL,
 652   job_namespace  SMALLINT NOT NULL,
 653   job_title      VARCHAR(255) NOT NULL,
 654   job_params     CLOB(64K) INLINE LENGTH 4096 NOT NULL
 655 );
 656 CREATE INDEX job_cmd_namespace_title
 657   ON job (job_cmd, job_namespace, job_title);
 658
 659
 660
 661 --TODO
 662 --CREATE FUNCTION add_interwiki (TEXT, INT, SMALLINT) RETURNS INT LANGUAGE SQL AS
 663 --$mw$
 664 --  INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
 665 --  SELECT 1;
 666 --$mw$;
 667
 668
 669
 670 -- hack implementation
 671 -- should be replaced with OmniFind, Contains(), etc
 672 CREATE TABLE searchindex (
 673   si_page   BIGINT NOT NULL,
 674   si_title  VARCHAR(255) NOT NULL DEFAULT '',
 675   si_text   CLOB NOT NULL
 676 );
 677
 678
 679
 680 -- This table is not used unless profiling is turned on
 681 CREATE TABLE profiling (
 682   pf_count   INTEGER NOT NULL DEFAULT 0,
 683   pf_time    NUMERIC(18,10) NOT NULL DEFAULT 0,
 684   pf_memory  NUMERIC(18,10) NOT NULL DEFAULT 0,
 685   pf_name    VARCHAR(255) NOT NULL,
 686   pf_server  VARCHAR(255)
 687 );
 688 CREATE UNIQUE INDEX pf_name_server
 689   ON profiling (pf_name, pf_server);
 690
 691
 692
 693 CREATE TABLE protected_titles (
 694   pt_namespace   INTEGER NOT NULL,
 695   pt_title       VARCHAR(255) NOT NULL,
 696   pt_user        BIGINT NOT NULL DEFAULT 0,
 697   --       REFERENCES user(user_id) ON DELETE SET NULL,
 698   pt_reason      VARCHAR(1024),
 699   pt_timestamp   TIMESTAMP(3) NOT NULL,
 700   pt_expiry      TIMESTAMP(3),
 701   pt_create_perm VARCHAR(60) NOT NULL DEFAULT ''
 702 );
 703 CREATE UNIQUE INDEX protected_titles_unique
 704   ON protected_titles (pt_namespace, pt_title);
 705
 706
 707
 708 CREATE TABLE updatelog (
 709   ul_key VARCHAR(255) NOT NULL
 710     PRIMARY KEY
 711 );
 712
 713
 714
 715 CREATE TABLE category (
 716   cat_id       INTEGER NOT NULL
 717     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 718   cat_title    VARCHAR(255) NOT NULL,
 719   cat_pages    INTEGER NOT NULL DEFAULT 0,
 720   cat_subcats  INTEGER NOT NULL DEFAULT 0,
 721   cat_files    INTEGER NOT NULL DEFAULT 0,
 722   cat_hidden   SMALLINT NOT NULL DEFAULT 0
 723 );
 724 CREATE UNIQUE INDEX category_title
 725   ON category (cat_title);
 726 CREATE INDEX category_pages
 727   ON category (cat_pages);
 728
 729
 730
 731 -- A table to track tags for revisions, logs and recent changes.
 732 CREATE TABLE change_tag (
 733   ct_rc_id    INTEGER,
 734   ct_log_id   INTEGER,
 735   ct_rev_id   INTEGER,
 736   ct_tag      VARCHAR(255) NOT NULL,
 737   ct_params   CLOB(64K) INLINE LENGTH 4096
 738 );
 739 CREATE UNIQUE INDEX change_tag_rc_tag
 740   ON change_tag (ct_rc_id, ct_tag);
 741 CREATE UNIQUE INDEX change_tag_log_tag
 742   ON change_tag (ct_log_id, ct_tag);
 743 CREATE UNIQUE INDEX change_tag_rev_tag
 744   ON change_tag (ct_rev_id, ct_tag);
 745 -- Covering index, so we can pull all the info only out of the index.
 746 CREATE INDEX change_tag_tag_id
 747   ON change_tag (ct_tag, ct_rc_id, ct_rev_id, ct_log_id);
 748
 749
 750
 751 -- Rollup table to pull a LIST of tags simply
 752 CREATE TABLE tag_summary (
 753   ts_rc_id  INTEGER,
 754   ts_log_id INTEGER,
 755   ts_rev_id INTEGER,
 756   ts_tags   CLOB(64K) INLINE LENGTH 4096 NOT NULL
 757 );
 758 CREATE UNIQUE INDEX tag_summary_rc_id
 759   ON tag_summary (ts_rc_id);
 760 CREATE UNIQUE INDEX tag_summary_log_id
 761   ON tag_summary (ts_log_id);
 762 CREATE UNIQUE INDEX tag_summary_rev_id
 763   ON tag_summary (ts_rev_id);
 764
 765
 766
 767 CREATE TABLE valid_tag (
 768   vt_tag VARCHAR(255) NOT NULL
 769     PRIMARY KEY
 770 );
 771
 772
 773
 774 --
 775 -- User preferences and perhaps other fun stuff. :)
 776 -- Replaces the old user.user_options blob, with a couple nice properties:
 777 --
 778 -- 1) We only store non-default settings, so changes to the DEFAULTs
 779 --    are now reflected for everybody, not just new accounts.
 780 -- 2) We can more easily do bulk lookups, statistics, or modifications of
 781 --    saved options since it's a sane table structure.
 782 --
 783 CREATE TABLE user_properties (
 784   -- Foreign key to user.user_id
 785   up_user       BIGINT NOT NULL,
 786   -- Name of the option being saved. This is indexed for bulk lookup.
 787   up_property   VARCHAR(255) FOR BIT DATA NOT NULL,
 788   -- Property value as a string.
 789   up_value      CLOB(64K) INLINE LENGTH 4096
 790 );
 791 CREATE UNIQUE INDEX user_properties_user_property
 792   ON user_properties (up_user, up_property);
 793 CREATE INDEX user_properties_property
 794   ON user_properties (up_property);
 795
 796 CREATE TABLE log_search (
 797   -- The type of ID (rev ID, log ID, rev TIMESTAMP(3), username)
 798   ls_field    VARCHAR(32) FOR BIT DATA NOT NULL,
 799   -- The value of the ID
 800   ls_value    VARCHAR(255) NOT NULL,
 801   -- Key to log_id
 802   ls_log_id   BIGINT NOT NULL DEFAULT 0
 803 );
 804 CREATE UNIQUE INDEX ls_field_val
 805   ON log_search (ls_field, ls_value, ls_log_id);
 806 CREATE INDEX ls_log_id
 807   ON log_search (ls_log_id);
 808
 809
 810
 811 -- Table for storing localisation data
 812 CREATE TABLE l10n_cache (
 813   -- Language code
 814   lc_lang       VARCHAR(32) NOT NULL,
 815   -- Cache key
 816   lc_key        VARCHAR(255) NOT NULL,
 817   -- Value
 818   lc_value      CLOB(16M) INLINE LENGTH 4096 NOT NULL
 819 );
 820 CREATE INDEX lc_lang_key
 821   ON l10n_cache (lc_lang, lc_key);
 822
 823
 824
 825 CREATE TABLE msg_resource_links
 826 (
 827   mrl_resource  VARCHAR(255) FOR BIT DATA NOT NULL,
 828   mrl_message   VARCHAR(255) FOR BIT DATA NOT NULL
 829 );
 830 CREATE UNIQUE INDEX uq61_msg_resource_links
 831   ON msg_resource_links (mrl_message, mrl_resource);
 832 -- All DB2 indexes DEFAULT to allowing reverse scans
 833
 834
 835
 836 CREATE TABLE msg_resource
 837 (
 838   mr_resource   VARCHAR(255) FOR BIT DATA NOT NULL,
 839   mr_lang       VARCHAR(32) FOR BIT DATA NOT NULL,
 840   mr_blob       CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 841   mr_timestamp  TIMESTAMP(3) NOT NULL
 842 );
 843 CREATE UNIQUE INDEX uq81_msg_resource
 844   ON msg_resource (mr_resource, mr_lang);
 845 -- All DB2 indexes DEFAULT to allowing reverse scans
 846
 847
 848
 849 CREATE TABLE module_deps (
 850   md_module VARCHAR(255) FOR BIT DATA NOT NULL,
 851   md_skin   VARCHAR(32) FOR BIT DATA NOT NULL,
 852   md_deps   CLOB(16M) INLINE LENGTH 4096 NOT NULL
 853 );
 854 CREATE UNIQUE INDEX uq96_module_deps
 855   ON module_deps (md_module, md_skin);
 856 -- All DB2 indexes DEFAULT to allowing reverse scans
 857
 858
 859
 860 CREATE TABLE iwlinks
 861 (
 862   iwl_from    INTEGER NOT NULL,
 863   iwl_prefix  VARCHAR(20) FOR BIT DATA NOT NULL,
 864   iwl_title   VARCHAR(255) FOR BIT DATA NOT NULL
 865 );
 866
 867
 868
 869 --
 870 -- Store information about newly uploaded files before they're
 871 -- moved into the actual filestore
 872 --
 873 CREATE TABLE uploadstash (
 874   us_id           BIGINT NOT NULL
 875     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 876   -- the user who uploaded the file.
 877   us_user         BIGINT NOT NULL,
 878   -- file key. this is how applications actually search for the file.
 879   -- this might go away, or become the primary key.
 880   us_key          VARCHAR(255) NOT NULL,
 881   -- the original path
 882   us_orig_path    VARCHAR(255) NOT NULL,
 883   -- the temporary path at which the file is actually stored
 884   us_path         VARCHAR(255) NOT NULL,
 885   -- which type of upload the file came from (sometimes)
 886   us_source_type  VARCHAR(50),
 887   -- the date/time on which the file was added
 888   us_timestamp    TIMESTAMP(3) NOT NULL,
 889   us_status       VARCHAR(50) NOT NULL,
 890   -- file properties from File::getPropsFromPath.  these may prove unnecessary.
 891   --
 892   us_size         BIGINT NOT NULL,
 893   -- this hash comes from File::sha1Base36(), and is 31 characters
 894   us_sha1         VARCHAR(31) NOT NULL,
 895   us_mime         VARCHAR(255),
 896   -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table
 897   us_media_type   VARCHAR(30)
 898     CONSTRAINT my_constraint
 899       CHECK (
 900         us_media_type in (
 901           'UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA',
 902           'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'
 903       )
 904     ) DEFAULT NULL,
 905   -- image-specific properties
 906   us_image_width  BIGINT,
 907   us_image_height BIGINT,
 908   us_image_bits   INTEGER
 909 );
 910 -- sometimes there's a delete for all of a user's stuff.
 911 CREATE INDEX us_user
 912   ON uploadstash (us_user);
 913 -- pick out files by key, enforce key UNIQUEness
 914 CREATE UNIQUE INDEX us_key
 915   ON uploadstash (us_key);
 916 -- the abandoned upload cleanup script needs this
 917 CREATE INDEX us_timestamp
 918   ON uploadstash (us_timestamp);
 919
 920
 921
 922 -- Stores the groups the user has once belonged to.
 923 -- The user may still belong these groups. Check user_groups.
 924 CREATE TABLE user_former_groups (
 925   ufg_user   BIGINT NOT NULL DEFAULT 0,
 926   ufg_group  VARCHAR(16) FOR BIT DATA NOT NULL
 927 );
 928 CREATE UNIQUE INDEX ufg_user_group
 929   ON user_former_groups (ufg_user, ufg_group);