maintenance/postgres/tables.sql

   1 -- SQL to create the initial tables for the MediaWiki database.
   2 -- This is read and executed by the install script; you should
   3 -- not have to run it by itself unless doing a manual install.
   4 -- This is the PostgreSQL version.
   5 -- For information about each table, please see the notes in ../tables.sql
   6 -- Please make sure all dollar-quoting uses $mw$ at the start of the line
   7 -- We can't use SERIAL everywhere: the sequence names are hard-coded into the PHP
   8 -- TODO: Change CHAR to BOOL, TEXT to CIDR, allow custom schema
   9
  10 SET client_min_messages = 'ERROR';
  11
  12 CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
  13 CREATE TABLE "user" (
  14   user_id                   INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
  15   user_name                 TEXT NOT NULL UNIQUE,
  16   user_real_name            TEXT,
  17   user_password             TEXT,
  18   user_newpassword          TEXT,
  19   user_token                CHAR(32),
  20   user_email                TEXT,
  21   user_email_token          CHAR(32),
  22   user_email_token_expires  TIMESTAMPTZ,
  23   user_email_authenticated  TIMESTAMPTZ,
  24   user_options              TEXT,
  25   user_touched              TIMESTAMPTZ,
  26   user_registration         TIMESTAMPTZ
  27 );
  28 CREATE INDEX user_email_token_idx ON "user"(user_email_token);
  29
  30 CREATE TABLE user_groups (
  31   ug_user   INTEGER NULL REFERENCES "user"(user_id) ON DELETE CASCADE,
  32   ug_group  TEXT NOT NULL
  33 );
  34 CREATE UNIQUE INDEX user_groups_unique ON user_groups(ug_group, ug_user);
  35
  36 CREATE TABLE user_newtalk (
  37   user_id  INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE,
  38   user_ip  TEXT NULL -- change to CIDR later
  39 );
  40 CREATE INDEX user_newtalk_id_idx ON user_newtalk(user_id);
  41 CREATE INDEX user_newtalk_ip_idx ON user_newtalk(user_ip);
  42
  43 CREATE SEQUENCE page_page_id_seq;
  44 CREATE TABLE page (
  45   page_id            INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('page_page_id_seq'),
  46   page_namespace     SMALLINT NOT NULL,
  47   page_title         TEXT NOT NULL,
  48   page_restrictions  TEXT,
  49   page_counter       BIGINT NOT NULL DEFAULT 0,
  50   page_is_redirect   CHAR NOT NULL DEFAULT 0,
  51   page_is_new        CHAR NOT NULL DEFAULT 0,
  52   page_random        NUMERIC(15,14) NOT NULL,
  53   page_touched       TIMESTAMPTZ,
  54   page_latest        INTEGER NOT NULL, -- FK?
  55   page_len           INTEGER NOT NULL
  56 );
  57 CREATE UNIQUE INDEX page_unique_name ON page(page_namespace, page_title);
  58 CREATE INDEX page_random_idx ON page(page_random);
  59 CREATE INDEX page_len_idx ON page(page_len);
  60
  61 -- Create a dummy page to satisfy fk contraints where a page_id of "0" is added
  62 INSERT INTO page (page_id,page_namespace,page_title,page_random,page_latest,page_len)
  63 VALUES (0,0,'',0.0,0,0);
  64
  65
  66 CREATE SEQUENCE rev_rev_id_val;
  67 CREATE TABLE revision (
  68   rev_id          INTEGER NOT NULL UNIQUE DEFAULT nextval('rev_rev_id_val'),
  69   rev_page        INTEGER NULL REFERENCES page (page_id) ON DELETE SET NULL,
  70   rev_text_id     INTEGER NULL, -- FK
  71   rev_comment     TEXT,
  72   rev_user        INTEGER NOT NULL REFERENCES "user"(user_id),
  73   rev_user_text   TEXT NOT NULL,
  74   rev_timestamp   TIMESTAMPTZ NOT NULL,
  75   rev_minor_edit  CHAR NOT NULL DEFAULT '0',
  76   rev_deleted     CHAR NOT NULL DEFAULT '0'
  77 );
  78 CREATE UNIQUE INDEX revision_unique ON revision(rev_page, rev_id);
  79 CREATE INDEX rev_timestamp_idx ON revision(rev_timestamp);
  80 CREATE INDEX rev_user_idx ON revision(rev_user);
  81 CREATE INDEX rev_user_text_idx ON revision(rev_user_text);
  82
  83
  84 CREATE SEQUENCE text_old_id_val;
  85 CREATE TABLE "text" (
  86   old_id     INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_val'),
  87   old_text   TEXT,
  88   old_flags  TEXT
  89 );
  90
  91
  92 CREATE TABLE archive (
  93   ar_namespace   SMALLINT NOT NULL,
  94   ar_title       TEXT NOT NULL,
  95   ar_text        TEXT,
  96   ar_comment     TEXT,
  97   ar_user        INTEGER REFERENCES "user"(user_id) ON DELETE SET NULL,
  98   ar_user_text   TEXT NOT NULL,
  99   ar_timestamp   TIMESTAMPTZ NOT NULL,
 100   ar_minor_edit  CHAR NOT NULL DEFAULT '0',
 101   ar_flags       TEXT,
 102   ar_rev_id      INTEGER,
 103   ar_text_id     INTEGER
 104 );
 105 CREATE INDEX archive_name_title_timestamp ON archive(ar_namespace,ar_title,ar_timestamp);
 106
 107
 108 CREATE TABLE pagelinks (
 109   pl_from       INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
 110   pl_namespace  SMALLINT NOT NULL,
 111   pl_title      TEXT NOT NULL
 112 );
 113 CREATE UNIQUE INDEX pagelink_unique ON pagelinks(pl_namespace,pl_title,pl_from);
 114
 115
 116 CREATE TABLE templatelinks (
 117   tl_from       INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
 118   tl_namespace  TEXT NOT NULL,
 119   tl_title      TEXT NOT NULL
 120 );
 121 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks(tl_namespace,tl_title,tl_from);
 122
 123
 124 CREATE TABLE imagelinks (
 125   il_from  INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
 126   il_to    TEXT NOT NULL
 127 );
 128 CREATE UNIQUE INDEX il_from ON imagelinks(il_to,il_from);
 129
 130
 131 CREATE TABLE categorylinks (
 132   cl_from       INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
 133   cl_to         TEXT NOT NULL,
 134   cl_sortkey    TEXT,
 135   cl_timestamp  TIMESTAMPTZ NOT NULL
 136 );
 137 CREATE UNIQUE INDEX cl_from ON categorylinks(cl_from, cl_to);
 138 CREATE INDEX cl_sortkey ON categorylinks(cl_to, cl_sortkey);
 139
 140
 141 CREATE TABLE externallinks (
 142   el_from   INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
 143   el_to     TEXT NOT NULL,
 144   el_index  TEXT NOT NULL
 145 );
 146 CREATE INDEX externallinks_from_to ON externallinks(el_from,el_to);
 147 CREATE INDEX externallinks_index ON externallinks(el_index);
 148
 149
 150 CREATE TABLE langlinks (
 151   ll_from    INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
 152   ll_lang    TEXT,
 153   ll_title   TEXT
 154 );
 155 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
 156 CREATE INDEX langlinks_lang_title ON langlinks(ll_lang,ll_title);
 157
 158
 159 CREATE TABLE site_stats (
 160   ss_row_id         INTEGER NOT NULL UNIQUE,
 161   ss_total_views    INTEGER DEFAULT 0,
 162   ss_total_edits    INTEGER DEFAULT 0,
 163   ss_good_articles  INTEGER DEFAULT 0,
 164   ss_total_pages    INTEGER DEFAULT -1,
 165   ss_users          INTEGER DEFAULT -1,
 166   ss_admins         INTEGER DEFAULT -1,
 167   ss_images         INTEGER DEFAULT 0
 168 );
 169
 170
 171 CREATE TABLE hitcounter (
 172   hc_id  BIGINT NOT NULL
 173 );
 174
 175 CREATE SEQUENCE ipblocks_ipb_id_val;
 176 CREATE TABLE ipblocks (
 177   ipb_id           INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'),
 178   ipb_address      TEXT NULL, -- change to CIDR later
 179   ipb_user         INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL,
 180   ipb_by           INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE,
 181   ipb_reason       TEXT NOT NULL,
 182   ipb_timestamp    TIMESTAMPTZ NOT NULL,
 183   ipb_auto         CHAR NOT NULL DEFAULT '0',
 184   ipb_expiry       TIMESTAMPTZ NOT NULL,
 185   ipb_range_start  TEXT, -- change to CIDR later
 186   ipb_range_end    TEXT  -- change to CIDR later
 187 );
 188 CREATE INDEX ipb_address ON ipblocks(ipb_address);
 189 CREATE INDEX ipb_user ON ipblocks(ipb_user);
 190 CREATE INDEX ipb_range ON ipblocks(ipb_range_start,ipb_range_end);
 191
 192
 193 CREATE TABLE image (
 194   img_name         TEXT NOT NULL PRIMARY KEY,
 195   img_size         SMALLINT NOT NULL,
 196   img_width        SMALLINT NOT NULL,
 197   img_height       SMALLINT NOT NULL,
 198   img_metadata     TEXT,
 199   img_bits         SMALLINT,
 200   img_media_type   TEXT,
 201   img_major_mime   TEXT DEFAULT 'unknown',
 202   img_minor_mime   TEXT DEFAULT 'unknown',
 203   img_description  TEXT NOT NULL,
 204   img_user         INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL,
 205   img_user_text    TEXT NOT NULL,
 206   img_timestamp    TIMESTAMPTZ
 207 );
 208 CREATE INDEX img_size_idx ON image(img_size);
 209 CREATE INDEX img_timestamp_idx ON image(img_timestamp);
 210
 211
 212 CREATE TABLE oldimage (
 213   oi_name          TEXT NOT NULL REFERENCES image(img_name),
 214   oi_archive_name  TEXT NOT NULL,
 215   oi_size          SMALLINT NOT NULL,
 216   oi_width         SMALLINT NOT NULL,
 217   oi_height        SMALLINT NOT NULL,
 218   oi_bits          SMALLINT NOT NULL,
 219   oi_description   TEXT,
 220   oi_user          INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL,
 221   oi_user_text     TEXT NOT NULL,
 222   oi_timestamp     TIMESTAMPTZ NOT NULL
 223 );
 224 CREATE INDEX oi_name ON oldimage (oi_name);
 225
 226
 227 CREATE TABLE filearchive (
 228   fa_id                 SERIAL      NOT NULL PRIMARY KEY,
 229   fa_name               TEXT        NOT NULL,
 230   fa_archive_name       TEXT,
 231   fa_storage_group      VARCHAR(16),
 232   fa_storage_key        CHAR(64),
 233   fa_deleted_user       INTEGER         NULL REFERENCES "user"(user_id) ON DELETE SET NULL,
 234   fa_deleted_timestamp  TIMESTAMPTZ NOT NULL,
 235   fa_deleted_reason     TEXT,
 236   fa_size               SMALLINT    NOT NULL,
 237   fa_width              SMALLINT    NOT NULL,
 238   fa_height             SMALLINT    NOT NULL,
 239   fa_metadata           TEXT,
 240   fa_bits               SMALLINT,
 241   fa_media_type         TEXT,
 242   fa_major_mime         TEXT                   DEFAULT 'unknown',
 243   fa_minor_mime         TEXT                   DEFAULT 'unknown',
 244   fa_description        TEXT        NOT NULL,
 245   fa_user               INTEGER         NULL REFERENCES "user"(user_id) ON DELETE SET NULL,
 246   fa_user_text          TEXT        NOT NULL,
 247   fa_timestamp          TIMESTAMPTZ
 248 );
 249 CREATE INDEX fa_name_time ON filearchive(fa_name, fa_timestamp);
 250 CREATE INDEX fa_dupe ON filearchive(fa_storage_group, fa_storage_key);
 251 CREATE INDEX fa_notime ON filearchive(fa_deleted_timestamp);
 252 CREATE INDEX fa_nouser ON filearchive(fa_deleted_user);
 253
 254
 255 CREATE SEQUENCE rc_rc_id_seq;
 256 CREATE TABLE recentchanges (
 257   rc_id              INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
 258   rc_timestamp       TIMESTAMPTZ NOT NULL,
 259   rc_cur_time        TIMESTAMPTZ NOT NULL,
 260   rc_user            INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL,
 261   rc_user_text       TEXT NOT NULL,
 262   rc_namespace       SMALLINT NOT NULL,
 263   rc_title           TEXT NOT NULL,
 264   rc_comment         TEXT,
 265   rc_minor           CHAR NOT NULL DEFAULT '0',
 266   rc_bot             CHAR NOT NULL DEFAULT '0',
 267   rc_new             CHAR NOT NULL DEFAULT '0',
 268   rc_cur_id          INTEGER NOT NULL REFERENCES page(page_id),
 269   rc_this_oldid      INTEGER NOT NULL,
 270   rc_last_oldid      INTEGER NOT NULL,
 271   rc_type            CHAR NOT NULL DEFAULT '0',
 272   rc_moved_to_ns     SMALLINT,
 273   rc_moved_to_title  TEXT,
 274   rc_patrolled       CHAR NOT NULL DEFAULT '0',
 275   rc_ip              TEXT -- change to CIDR later
 276 );
 277 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
 278 CREATE INDEX rc_namespace_title ON recentchanges(rc_namespace, rc_title);
 279 CREATE INDEX rc_cur_id ON recentchanges(rc_cur_id);
 280 CREATE INDEX new_name_timestamp ON recentchanges(rc_new, rc_namespace, rc_timestamp);
 281 CREATE INDEX rc_ip ON recentchanges(rc_ip);
 282
 283
 284 CREATE TABLE watchlist (
 285   wl_user                   INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE,
 286   wl_namespace              SMALLINT NOT NULL DEFAULT 0,
 287   wl_title                  TEXT NOT NULL,
 288   wl_notificationtimestamp  TIMESTAMPTZ DEFAULT NULL
 289 );
 290 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
 291
 292
 293 CREATE TABLE math (
 294   math_inputhash              TEXT NOT NULL UNIQUE,
 295   math_outputhash             TEXT NOT NULL,
 296   math_html_conservativeness  SMALLINT NOT NULL,
 297   math_html                   TEXT,
 298   math_mathml                 TEXT
 299 );
 300
 301
 302 CREATE TABLE interwiki (
 303   iw_prefix  TEXT NOT NULL UNIQUE,
 304   iw_url     TEXT NOT NULL,
 305   iw_local   CHAR NOT NULL,
 306   iw_trans   CHAR NOT NULL DEFAULT '0'
 307 );
 308
 309
 310 CREATE TABLE querycache (
 311   qc_type       TEXT NOT NULL,
 312   qc_value      SMALLINT NOT NULL,
 313   qc_namespace  SMALLINT NOT NULL,
 314   qc_title      TEXT NOT NULL
 315 );
 316 CREATE INDEX querycache_type_value ON querycache(qc_type, qc_value);
 317
 318
 319 CREATE TABLE objectcache (
 320   keyname  CHAR(255) UNIQUE,
 321   value    TEXT NOT NULL DEFAULT '',
 322   exptime  TIMESTAMPTZ NOT NULL
 323 );
 324 CREATE INDEX objectcacache_exptime ON objectcache(exptime);
 325
 326 CREATE TABLE transcache (
 327   tc_url       TEXT NOT NULL UNIQUE,
 328   tc_contents  TEXT NOT NULL,
 329   tc_time      TIMESTAMPTZ NOT NULL
 330 );
 331
 332 CREATE TABLE logging (
 333   log_type        TEXT NOT NULL,
 334   log_action      TEXT NOT NULL,
 335   log_timestamp   TIMESTAMPTZ NOT NULL,
 336   log_user        INTEGER REFERENCES "user"(user_id) ON DELETE SET NULL,
 337   log_namespace   SMALLINT NOT NULL,
 338   log_title       TEXT NOT NULL,
 339   log_comment     TEXT,
 340   log_params      TEXT
 341 );
 342 CREATE INDEX logging_type_name ON logging(log_type, log_timestamp);
 343 CREATE INDEX logging_user_time ON logging(log_timestamp, log_user);
 344 CREATE INDEX logging_page_time ON logging(log_namespace, log_title, log_timestamp);
 345
 346 CREATE TABLE trackbacks (
 347   tb_id     SERIAL NOT NULL PRIMARY KEY,
 348   tb_page   INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
 349   tb_title  TEXT NOT NULL,
 350   tb_url    TEXT NOT NULL,
 351   tb_ex     TEXT,
 352   tb_name   TEXT
 353 );
 354 CREATE INDEX trackback_page ON trackbacks(tb_page);
 355
 356 CREATE SEQUENCE job_job_id_seq;
 357 CREATE TABLE job (
 358   job_id         INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
 359   job_cmd        TEXT NOT NULL,
 360   job_namespace  SMALLINT NOT NULL,
 361   job_title      TEXT NOT NULL,
 362   job_params     TEXT NOT NULL
 363 );
 364 CREATE INDEX job_cmd_namespace_title ON job(job_cmd, job_namespace, job_title);
 365
 366 CREATE TABLE querycache_info (
 367   qci_type       TEXT UNIQUE,
 368   qci_timestamp  TIMESTAMPTZ NULL
 369 );
 370
 371
 372 CREATE OR REPLACE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS
 373 $mw$
 374   INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
 375   SELECT 1;
 376 $mw$;