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