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