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
 376 );
 377 CREATE INDEX ipb_address
 378   ON ipblocks (ipb_address);
 379 CREATE INDEX ipb_user
 380   ON ipblocks (ipb_user);
 381 CREATE INDEX ipb_range
 382   ON ipblocks (ipb_range_start, ipb_range_end);
 383
 384
 385
 386 CREATE TABLE image (
 387   img_name         VARCHAR(255) NOT NULL
 388     PRIMARY KEY,
 389   img_size         BIGINT NOT NULL,
 390   img_width        INTEGER NOT NULL,
 391   img_height       INTEGER NOT NULL,
 392   img_metadata     CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 393   img_bits         SMALLINT,
 394   img_media_type   VARCHAR(255),
 395   img_major_mime   VARCHAR(255) DEFAULT 'unknown',
 396   img_minor_mime   VARCHAR(32) DEFAULT 'unknown',
 397   img_description  VARCHAR(1024) NOT NULL DEFAULT '',
 398   img_user         BIGINT NOT NULL DEFAULT 0,
 399   --         REFERENCES user(user_id) ON DELETE SET NULL,
 400   img_user_text    VARCHAR(255) NOT NULL DEFAULT '',
 401   img_timestamp    TIMESTAMP(3),
 402   img_sha1         VARCHAR(255) NOT NULL DEFAULT ''
 403 );
 404 CREATE INDEX img_size_idx
 405   ON image (img_size);
 406 CREATE INDEX img_timestamp_idx
 407   ON image (img_timestamp);
 408 CREATE INDEX img_sha1
 409   ON image (img_sha1);
 410
 411
 412 CREATE TABLE oldimage (
 413   oi_name          VARCHAR(255) NOT NULL DEFAULT '',
 414   oi_archive_name  VARCHAR(255) NOT NULL,
 415   oi_size          BIGINT NOT NULL,
 416   oi_width         INTEGER NOT NULL,
 417   oi_height        INTEGER NOT NULL,
 418   oi_bits          SMALLINT NOT NULL,
 419   oi_description   VARCHAR(1024),
 420   oi_user          BIGINT NOT NULL DEFAULT 0,
 421   --            REFERENCES user(user_id) ON DELETE SET NULL,
 422   oi_user_text     VARCHAR(255) NOT NULL,
 423   oi_timestamp     TIMESTAMP(3) NOT NULL,
 424   oi_metadata      CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 425   oi_media_type    VARCHAR(255),
 426   oi_major_mime    VARCHAR(255) NOT NULL DEFAULT 'unknown',
 427   oi_minor_mime    VARCHAR(255) NOT NULL DEFAULT 'unknown',
 428   oi_deleted       SMALLINT NOT NULL DEFAULT 0,
 429   oi_sha1          VARCHAR(255) NOT NULL DEFAULT ''
 430   --FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
 431 );
 432 CREATE INDEX oi_name_timestamp
 433   ON oldimage (oi_name, oi_timestamp);
 434 CREATE INDEX oi_name_archive_name
 435   ON oldimage (oi_name, oi_archive_name);
 436 CREATE INDEX oi_sha1
 437   ON oldimage (oi_sha1);
 438
 439
 440
 441 CREATE TABLE filearchive (
 442   fa_id                 INTEGER NOT NULL
 443     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 444   fa_name               VARCHAR(255) NOT NULL,
 445   fa_archive_name       VARCHAR(255),
 446   fa_storage_group      VARCHAR(255),
 447   fa_storage_key        VARCHAR(64) DEFAULT '',
 448   fa_deleted_user       BIGINT NOT NULL DEFAULT 0,
 449   --            REFERENCES user(user_id) ON DELETE SET NULL,
 450   fa_deleted_timestamp  TIMESTAMP(3) NOT NULL,
 451   fa_deleted_reason     VARCHAR(255),
 452   fa_size               BIGINT NOT NULL,
 453   fa_width              INTEGER NOT NULL,
 454   fa_height             INTEGER NOT NULL,
 455   fa_metadata           CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 456   fa_bits               SMALLINT,
 457   fa_media_type         VARCHAR(255),
 458   fa_major_mime         VARCHAR(255) DEFAULT 'unknown',
 459   fa_minor_mime         VARCHAR(255) DEFAULT 'unknown',
 460   fa_description        VARCHAR(1024) NOT NULL,
 461   fa_user               BIGINT NOT NULL DEFAULT 0,
 462   --            REFERENCES user(user_id) ON DELETE SET NULL,
 463   fa_user_text          VARCHAR(255) NOT NULL,
 464   fa_timestamp          TIMESTAMP(3),
 465   fa_deleted            SMALLINT NOT NULL DEFAULT 0
 466 );
 467 CREATE INDEX fa_name_time
 468   ON filearchive (fa_name, fa_timestamp);
 469 CREATE INDEX fa_dupe
 470   ON filearchive (fa_storage_group, fa_storage_key);
 471 CREATE INDEX fa_notime
 472   ON filearchive (fa_deleted_timestamp);
 473 CREATE INDEX fa_nouser
 474   ON filearchive (fa_deleted_user);
 475
 476
 477
 478 CREATE TABLE recentchanges (
 479   rc_id              INTEGER NOT NULL
 480     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 481   rc_timestamp       TIMESTAMP(3) NOT NULL,
 482   rc_cur_time        TIMESTAMP(3) NOT NULL,
 483   rc_user            BIGINT NOT NULL DEFAULT 0,
 484   --        REFERENCES user(user_id) ON DELETE SET NULL,
 485   rc_user_text       VARCHAR(255) NOT NULL,
 486   rc_namespace       SMALLINT NOT NULL,
 487   rc_title           VARCHAR(255) NOT NULL,
 488   rc_comment         VARCHAR(255),
 489   rc_minor           SMALLINT NOT NULL DEFAULT 0,
 490   rc_bot             SMALLINT NOT NULL DEFAULT 0,
 491   rc_new             SMALLINT NOT NULL DEFAULT 0,
 492   rc_cur_id          BIGINT NOT NULL DEFAULT 0,
 493   --            REFERENCES page(page_id) ON DELETE SET NULL,
 494   rc_this_oldid      BIGINT NOT NULL,
 495   rc_last_oldid      BIGINT NOT NULL,
 496   rc_type            SMALLINT NOT NULL DEFAULT 0,
 497   rc_moved_to_ns     SMALLINT,
 498   rc_moved_to_title  VARCHAR(255),
 499   rc_patrolled       SMALLINT NOT NULL DEFAULT 0,
 500   rc_ip              VARCHAR(40),  -- was CIDR type
 501   rc_old_len         INTEGER,
 502   rc_new_len         INTEGER,
 503   rc_deleted         SMALLINT NOT NULL DEFAULT 0,
 504   rc_logid           BIGINT NOT NULL DEFAULT 0,
 505   rc_log_type        VARCHAR(255),
 506   rc_log_action      VARCHAR(255),
 507   rc_params          CLOB(64K) INLINE LENGTH 4096
 508
 509 );
 510 CREATE INDEX rc_timestamp
 511   ON recentchanges (rc_timestamp);
 512 CREATE INDEX rc_namespace_title
 513   ON recentchanges (rc_namespace, rc_title);
 514 CREATE INDEX rc_cur_id
 515   ON recentchanges (rc_cur_id);
 516 CREATE INDEX new_name_timestamp
 517   ON recentchanges (rc_new, rc_namespace, rc_timestamp);
 518 CREATE INDEX rc_ip
 519   ON recentchanges (rc_ip);
 520
 521
 522
 523 CREATE TABLE watchlist (
 524   wl_user                   BIGINT NOT NULL DEFAULT 0,
 525   --  REFERENCES user(user_id) ON DELETE CASCADE,
 526   wl_namespace              SMALLINT NOT NULL DEFAULT 0,
 527   wl_title                  VARCHAR(255) NOT NULL,
 528   wl_notificationtimestamp  TIMESTAMP(3)
 529 );
 530 CREATE UNIQUE INDEX wl_user_namespace_title
 531   ON watchlist (wl_namespace, wl_title, wl_user);
 532
 533
 534
 535 CREATE TABLE interwiki (
 536   iw_prefix  VARCHAR(32) NOT NULL UNIQUE,
 537   iw_url     CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 538   iw_api     CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 539   iw_wikiid  VARCHAR(64) NOT NULL,
 540   iw_local   SMALLINT NOT NULL,
 541   iw_trans   SMALLINT NOT NULL DEFAULT 0
 542 );
 543
 544
 545
 546 CREATE TABLE querycache (
 547   qc_type       VARCHAR(255) NOT NULL,
 548   qc_value      BIGINT NOT NULL,
 549   qc_namespace  INTEGER NOT NULL,
 550   qc_title      VARCHAR(255) NOT NULL
 551 );
 552 CREATE INDEX querycache_type_value
 553   ON querycache (qc_type, qc_value);
 554
 555
 556
 557 CREATE TABLE querycache_info (
 558   qci_type        VARCHAR(255) UNIQUE NOT NULL,
 559   qci_timestamp   TIMESTAMP(3)
 560 );
 561
 562
 563
 564 CREATE TABLE querycachetwo (
 565   qcc_type          VARCHAR(255) NOT NULL,
 566   qcc_value         BIGINT NOT NULL DEFAULT 0,
 567   qcc_namespace     INTEGER NOT NULL DEFAULT 0,
 568   qcc_title         VARCHAR(255) NOT NULL DEFAULT '',
 569   qcc_namespacetwo  INTEGER NOT NULL DEFAULT 0,
 570   qcc_titletwo      VARCHAR(255) NOT NULL DEFAULT ''
 571 );
 572 CREATE INDEX querycachetwo_type_value
 573   ON querycachetwo (qcc_type, qcc_value);
 574 CREATE INDEX querycachetwo_title
 575   ON querycachetwo (qcc_type, qcc_namespace, qcc_title);
 576 CREATE INDEX querycachetwo_titletwo
 577   ON querycachetwo (qcc_type, qcc_namespacetwo, qcc_titletwo);
 578
 579
 580
 581 CREATE TABLE objectcache (
 582   keyname   VARCHAR(255) NOT NULL UNIQUE, -- was nullable
 583   value     CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
 584   exptime   TIMESTAMP(3) NOT NULL
 585 );
 586 CREATE INDEX objectcacache_exptime
 587   ON objectcache (exptime);
 588
 589
 590
 591 CREATE TABLE transcache (
 592   tc_url       VARCHAR(255) NOT NULL UNIQUE,
 593   tc_contents  CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 594   tc_time      TIMESTAMP(3) NOT NULL
 595 );
 596
 597
 598
 599 CREATE TABLE logging (
 600   log_id          BIGINT NOT NULL
 601     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 602   log_type        VARCHAR(32) NOT NULL,
 603   log_action      VARCHAR(32) NOT NULL,
 604   log_timestamp   TIMESTAMP(3) NOT NULL,
 605   log_user        BIGINT NOT NULL DEFAULT 0,
 606   --                REFERENCES user(user_id) ON DELETE SET NULL,
 607   -- Name of the user who performed this action
 608   log_user_text   VARCHAR(255) NOT NULL DEFAULT '',
 609   log_namespace   SMALLINT NOT NULL,
 610   log_title       VARCHAR(255) NOT NULL,
 611   log_page        BIGINT,
 612   log_comment     VARCHAR(255),
 613   log_params      CLOB(64K) INLINE LENGTH 4096,
 614   log_deleted     SMALLINT NOT NULL DEFAULT 0
 615 );
 616 CREATE INDEX logging_type_name
 617   ON logging (log_type, log_timestamp);
 618 CREATE INDEX logging_user_time
 619   ON logging (log_timestamp, log_user);
 620 CREATE INDEX logging_page_time
 621   ON logging (log_namespace, log_title, log_timestamp);
 622 CREATE INDEX log_user_type_time
 623   ON logging (log_user, log_type, log_timestamp);
 624 CREATE INDEX log_page_id_time
 625   ON logging (log_page, log_timestamp);
 626 CREATE UNIQUE INDEX type_action
 627   ON logging (log_type, log_action, log_timestamp);
 628
 629
 630
 631 CREATE TABLE trackbacks (
 632   tb_id     INTEGER NOT NULL
 633     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 634   -- foreign key also in MySQL
 635   tb_page   INTEGER,
 636   -- REFERENCES page(page_id) ON DELETE CASCADE,
 637   tb_title  VARCHAR(255) NOT NULL,
 638   tb_url    CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 639   tb_ex     CLOB(64K) INLINE LENGTH 4096,
 640   tb_name   VARCHAR(255)
 641 );
 642 CREATE INDEX trackback_page
 643   ON trackbacks (tb_page);
 644
 645
 646
 647 CREATE TABLE job (
 648   job_id         BIGINT NOT NULL
 649     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 650   job_cmd        VARCHAR(255) NOT NULL,
 651   job_namespace  SMALLINT NOT NULL,
 652   job_title      VARCHAR(255) NOT NULL,
 653   job_params     CLOB(64K) INLINE LENGTH 4096 NOT NULL
 654 );
 655 CREATE INDEX job_cmd_namespace_title
 656   ON job (job_cmd, job_namespace, job_title);
 657
 658
 659
 660 --TODO
 661 --CREATE FUNCTION add_interwiki (TEXT, INT, SMALLINT) RETURNS INT LANGUAGE SQL AS
 662 --$mw$
 663 --  INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
 664 --  SELECT 1;
 665 --$mw$;
 666
 667
 668
 669 -- hack implementation
 670 -- should be replaced with OmniFind, Contains(), etc
 671 CREATE TABLE searchindex (
 672   si_page   BIGINT NOT NULL,
 673   si_title  VARCHAR(255) NOT NULL DEFAULT '',
 674   si_text   CLOB NOT NULL
 675 );
 676
 677
 678
 679 -- This table is not used unless profiling is turned on
 680 CREATE TABLE profiling (
 681   pf_count   INTEGER NOT NULL DEFAULT 0,
 682   pf_time    NUMERIC(18,10) NOT NULL DEFAULT 0,
 683   pf_memory  NUMERIC(18,10) NOT NULL DEFAULT 0,
 684   pf_name    VARCHAR(255) NOT NULL,
 685   pf_server  VARCHAR(255)
 686 );
 687 CREATE UNIQUE INDEX pf_name_server
 688   ON profiling (pf_name, pf_server);
 689
 690
 691
 692 CREATE TABLE protected_titles (
 693   pt_namespace   INTEGER NOT NULL,
 694   pt_title       VARCHAR(255) NOT NULL,
 695   pt_user        BIGINT NOT NULL DEFAULT 0,
 696   --       REFERENCES user(user_id) ON DELETE SET NULL,
 697   pt_reason      VARCHAR(1024),
 698   pt_timestamp   TIMESTAMP(3) NOT NULL,
 699   pt_expiry      TIMESTAMP(3),
 700   pt_create_perm VARCHAR(60) NOT NULL DEFAULT ''
 701 );
 702 CREATE UNIQUE INDEX protected_titles_unique
 703   ON protected_titles (pt_namespace, pt_title);
 704
 705
 706
 707 CREATE TABLE updatelog (
 708   ul_key VARCHAR(255) NOT NULL
 709     PRIMARY KEY
 710 );
 711
 712
 713
 714 CREATE TABLE category (
 715   cat_id       INTEGER NOT NULL
 716     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 717   cat_title    VARCHAR(255) NOT NULL,
 718   cat_pages    INTEGER NOT NULL DEFAULT 0,
 719   cat_subcats  INTEGER NOT NULL DEFAULT 0,
 720   cat_files    INTEGER NOT NULL DEFAULT 0,
 721   cat_hidden   SMALLINT NOT NULL DEFAULT 0
 722 );
 723 CREATE UNIQUE INDEX category_title
 724   ON category (cat_title);
 725 CREATE INDEX category_pages
 726   ON category (cat_pages);
 727
 728
 729
 730 -- A table to track tags for revisions, logs and recent changes.
 731 CREATE TABLE change_tag (
 732   ct_rc_id    INTEGER,
 733   ct_log_id   INTEGER,
 734   ct_rev_id   INTEGER,
 735   ct_tag      VARCHAR(255) NOT NULL,
 736   ct_params   CLOB(64K) INLINE LENGTH 4096
 737 );
 738 CREATE UNIQUE INDEX change_tag_rc_tag
 739   ON change_tag (ct_rc_id, ct_tag);
 740 CREATE UNIQUE INDEX change_tag_log_tag
 741   ON change_tag (ct_log_id, ct_tag);
 742 CREATE UNIQUE INDEX change_tag_rev_tag
 743   ON change_tag (ct_rev_id, ct_tag);
 744 -- Covering index, so we can pull all the info only out of the index.
 745 CREATE INDEX change_tag_tag_id
 746   ON change_tag (ct_tag, ct_rc_id, ct_rev_id, ct_log_id);
 747
 748
 749
 750 -- Rollup table to pull a LIST of tags simply
 751 CREATE TABLE tag_summary (
 752   ts_rc_id  INTEGER,
 753   ts_log_id INTEGER,
 754   ts_rev_id INTEGER,
 755   ts_tags   CLOB(64K) INLINE LENGTH 4096 NOT NULL
 756 );
 757 CREATE UNIQUE INDEX tag_summary_rc_id
 758   ON tag_summary (ts_rc_id);
 759 CREATE UNIQUE INDEX tag_summary_log_id
 760   ON tag_summary (ts_log_id);
 761 CREATE UNIQUE INDEX tag_summary_rev_id
 762   ON tag_summary (ts_rev_id);
 763
 764
 765
 766 CREATE TABLE valid_tag (
 767   vt_tag VARCHAR(255) NOT NULL
 768     PRIMARY KEY
 769 );
 770
 771
 772
 773 --
 774 -- User preferences and perhaps other fun stuff. :)
 775 -- Replaces the old user.user_options blob, with a couple nice properties:
 776 --
 777 -- 1) We only store non-default settings, so changes to the DEFAULTs
 778 --    are now reflected for everybody, not just new accounts.
 779 -- 2) We can more easily do bulk lookups, statistics, or modifications of
 780 --    saved options since it's a sane table structure.
 781 --
 782 CREATE TABLE user_properties (
 783   -- Foreign key to user.user_id
 784   up_user       BIGINT NOT NULL,
 785   -- Name of the option being saved. This is indexed for bulk lookup.
 786   up_property   VARCHAR(255) FOR BIT DATA NOT NULL,
 787   -- Property value as a string.
 788   up_value      CLOB(64K) INLINE LENGTH 4096
 789 );
 790 CREATE UNIQUE INDEX user_properties_user_property
 791   ON user_properties (up_user, up_property);
 792 CREATE INDEX user_properties_property
 793   ON user_properties (up_property);
 794
 795 CREATE TABLE log_search (
 796   -- The type of ID (rev ID, log ID, rev TIMESTAMP(3), username)
 797   ls_field    VARCHAR(32) FOR BIT DATA NOT NULL,
 798   -- The value of the ID
 799   ls_value    VARCHAR(255) NOT NULL,
 800   -- Key to log_id
 801   ls_log_id   BIGINT NOT NULL DEFAULT 0
 802 );
 803 CREATE UNIQUE INDEX ls_field_val
 804   ON log_search (ls_field, ls_value, ls_log_id);
 805 CREATE INDEX ls_log_id
 806   ON log_search (ls_log_id);
 807
 808
 809
 810 -- Table for storing localisation data
 811 CREATE TABLE l10n_cache (
 812   -- Language code
 813   lc_lang       VARCHAR(32) NOT NULL,
 814   -- Cache key
 815   lc_key        VARCHAR(255) NOT NULL,
 816   -- Value
 817   lc_value      CLOB(16M) INLINE LENGTH 4096 NOT NULL
 818 );
 819 CREATE INDEX lc_lang_key
 820   ON l10n_cache (lc_lang, lc_key);
 821
 822
 823
 824 CREATE TABLE msg_resource_links
 825 (
 826   mrl_resource  VARCHAR(255) FOR BIT DATA NOT NULL,
 827   mrl_message   VARCHAR(255) FOR BIT DATA NOT NULL
 828 );
 829 CREATE UNIQUE INDEX uq61_msg_resource_links
 830   ON msg_resource_links (mrl_message, mrl_resource);
 831 -- All DB2 indexes DEFAULT to allowing reverse scans
 832
 833
 834
 835 CREATE TABLE msg_resource
 836 (
 837   mr_resource   VARCHAR(255) FOR BIT DATA NOT NULL,
 838   mr_lang       VARCHAR(32) FOR BIT DATA NOT NULL,
 839   mr_blob       CLOB(64K) INLINE LENGTH 4096 NOT NULL,
 840   mr_timestamp  TIMESTAMP(3) NOT NULL
 841 );
 842 CREATE UNIQUE INDEX uq81_msg_resource
 843   ON msg_resource (mr_resource, mr_lang);
 844 -- All DB2 indexes DEFAULT to allowing reverse scans
 845
 846
 847
 848 CREATE TABLE module_deps (
 849   md_module VARCHAR(255) FOR BIT DATA NOT NULL,
 850   md_skin   VARCHAR(32) FOR BIT DATA NOT NULL,
 851   md_deps   CLOB(16M) INLINE LENGTH 4096 NOT NULL
 852 );
 853 CREATE UNIQUE INDEX uq96_module_deps
 854   ON module_deps (md_module, md_skin);
 855 -- All DB2 indexes DEFAULT to allowing reverse scans
 856
 857
 858
 859 CREATE TABLE iwlinks
 860 (
 861   iwl_from    INTEGER NOT NULL,
 862   iwl_prefix  VARCHAR(20) FOR BIT DATA NOT NULL,
 863   iwl_title   VARCHAR(255) FOR BIT DATA NOT NULL
 864 );
 865
 866
 867
 868 --
 869 -- Store information about newly uploaded files before they're
 870 -- moved into the actual filestore
 871 --
 872 CREATE TABLE uploadstash (
 873   us_id           BIGINT NOT NULL
 874     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 875   -- the user who uploaded the file.
 876   us_user         BIGINT NOT NULL,
 877   -- file key. this is how applications actually search for the file.
 878   -- this might go away, or become the primary key.
 879   us_key          VARCHAR(255) NOT NULL,
 880   -- the original path
 881   us_orig_path    VARCHAR(255) NOT NULL,
 882   -- the temporary path at which the file is actually stored
 883   us_path         VARCHAR(255) NOT NULL,
 884   -- which type of upload the file came from (sometimes)
 885   us_source_type  VARCHAR(50),
 886   -- the date/time on which the file was added
 887   us_timestamp    TIMESTAMP(3) NOT NULL,
 888   us_status       VARCHAR(50) NOT NULL,
 889   -- file properties from File::getPropsFromPath.  these may prove unnecessary.
 890   --
 891   us_size         BIGINT NOT NULL,
 892   -- this hash comes from File::sha1Base36(), and is 31 characters
 893   us_sha1         VARCHAR(31) NOT NULL,
 894   us_mime         VARCHAR(255),
 895   -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table
 896   us_media_type   VARCHAR(30)
 897     CONSTRAINT my_constraint
 898       CHECK (
 899         us_media_type in (
 900           'UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA',
 901           'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'
 902       )
 903     ) DEFAULT NULL,
 904   -- image-specific properties
 905   us_image_width  BIGINT,
 906   us_image_height BIGINT,
 907   us_image_bits   INTEGER
 908 );
 909 -- sometimes there's a delete for all of a user's stuff.
 910 CREATE INDEX us_user
 911   ON uploadstash (us_user);
 912 -- pick out files by key, enforce key UNIQUEness
 913 CREATE UNIQUE INDEX us_key
 914   ON uploadstash (us_key);
 915 -- the abandoned upload cleanup script needs this
 916 CREATE INDEX us_timestamp
 917   ON uploadstash (us_timestamp);
 918
 919
 920
 921 -- Stores the groups the user has once belonged to.
 922 -- The user may still belong these groups. Check user_groups.
 923 CREATE TABLE user_former_groups (
 924   ufg_user   BIGINT NOT NULL DEFAULT 0,
 925   ufg_group  VARCHAR(16) FOR BIT DATA NOT NULL
 926 );
 927 CREATE UNIQUE INDEX ufg_user_group
 928   ON user_former_groups (ufg_user, ufg_group);
 929
 930
 931
 932 -- Table for holding configuration changes
 933 CREATE TABLE config (
 934   cf_name   VARCHAR(255) NOT NULL
 935     PRIMARY KEY,
 936   cf_value  CLOB(64K) INLINE LENGTH 4096 NOT NULL
 937 );
 938