maintenance/postgresql/pg_tables.sql

   1 -- $Id$
   2 --
   3 -- Database schema for MediaWiki PostgreSQL support
   4 --
   5 --
   6
   7 CREATE TABLE cur (
   8     cur_id serial PRIMARY KEY,
   9     cur_namespace smallint NOT NULL,
  10     cur_title varchar(255) NOT NULL,
  11     cur_text text NOT NULL,
  12     cur_comment text,
  13     cur_user integer NOT NULL,
  14     cur_user_text varchar(255) NOT NULL,
  15     cur_timestamp timestamp without time zone NOT NULL,
  16     cur_restrictions text NOT NULL,
  17     cur_counter bigint DEFAULT 0 NOT NULL,
  18     cur_is_redirect smallint DEFAULT 0 NOT NULL,
  19     cur_minor_edit smallint DEFAULT 0 NOT NULL,
  20     cur_is_new smallint DEFAULT 0 NOT NULL,
  21     cur_random double precision,
  22     cur_touched timestamp without time zone,
  23     inverse_timestamp varchar(14)
  24 );
  25 CREATE INDEX cur_title_namespace_idx ON cur (cur_title, cur_namespace);
  26 CREATE INDEX cur_random_idx ON cur (cur_random);
  27 CREATE INDEX cur_name_title_timestamp_idx ON cur (cur_namespace, cur_title, cur_timestamp);
  28 CREATE INDEX cur_timestamp_idx ON cur (cur_timestamp);
  29
  30 CREATE TABLE "old" (
  31     old_id serial PRIMARY KEY,
  32     old_namespace smallint NOT NULL,
  33     old_title varchar(255) NOT NULL,
  34     old_text text NOT NULL,
  35     old_comment text NOT NULL,
  36     old_user integer NOT NULL,
  37     old_user_text varchar(255) NOT NULL,
  38     old_timestamp timestamp without time zone NOT NULL,
  39     old_minor_edit smallint NOT NULL,
  40     old_flags text NOT NULL,
  41     inverse_timestamp varchar(14) NOT NULL
  42 );
  43 CREATE INDEX old_name_title_ts_idx ON "old" (old_namespace, old_title, old_timestamp);
  44 CREATE INDEX old_timestamp ON "old" (old_timestamp);
  45
  46 CREATE TABLE brokenlinks (
  47     bl_from integer DEFAULT 0 NOT NULL,
  48     bl_to varchar(255) NOT NULL,
  49     PRIMARY KEY (bl_from,bl_to)
  50
  51 );
  52 CREATE INDEX bl_to_idx ON brokenlinks (bl_to);
  53
  54 CREATE TABLE hitcounter (
  55     hc_id bigint DEFAULT 0 NOT NULL
  56 );
  57 CREATE INDEX hc_id_idx on hitcounter (hc_id);
  58
  59 CREATE TABLE image (
  60     img_name varchar(255) PRIMARY KEY,
  61     img_size integer NOT NULL,
  62     img_description text NOT NULL,
  63     img_user integer NOT NULL,
  64     img_user_text varchar(255) NOT NULL,
  65     img_timestamp timestamp without time zone
  66 );
  67 CREATE INDEX img_size_idx ON image (img_size);
  68 CREATE INDEX img_timestamp ON image (img_timestamp);
  69
  70 CREATE TABLE imagelinks (
  71     il_from integer,
  72     il_to varchar(255),
  73     PRIMARY KEY (il_from, il_to)
  74 );
  75 CREATE INDEX il_to_idx ON imagelinks (il_to);
  76
  77
  78 CREATE TABLE categorylinks (
  79     cl_from integer DEFAULT 0 NOT NULL,
  80     cl_to varchar(255) NOT NULL,
  81     cl_sortkey varchar(255) NOT NULL,
  82     cl_timestamp timestamp without time zone,
  83     PRIMARY KEY (cl_from,cl_to)
  84 );
  85 CREATE INDEX cl_to_sortkey_idx ON categorylinks (cl_to, cl_sortkey);
  86 CREATE INDEX cl_to_timestamp ON categorylinks (cl_to, cl_timestamp);
  87
  88 CREATE TABLE links (
  89     l_from integer NOT NULL,
  90     l_to integer NOT NULL,
  91     PRIMARY KEY (l_from,l_to)
  92 );
  93 CREATE INDEX l_to_idx ON links (l_to);
  94
  95
  96 CREATE TABLE linkscc (
  97     lcc_pageid integer PRIMARY KEY,
  98     lcc_title varchar(255) DEFAULT ''::character varying NOT NULL,
  99     lcc_cacheobj text NOT NULL
 100 );
 101 CREATE RULE links_del AS ON DELETE TO links DO DELETE FROM linkscc WHERE (linkscc.lcc_pageid = old.l_from);
 102
 103 CREATE TABLE searchindex (
 104     si_page integer PRIMARY KEY,
 105     si_title varchar(255) NOT NULL,
 106     si_text text NOT NULL
 107 );
 108
 109 CREATE TABLE "user" (
 110     user_id serial PRIMARY KEY,
 111     user_name varchar(255) UNIQUE NOT NULL,
 112     user_real_name varchar(255) NOT NULL,
 113     user_rights text DEFAULT ''::text NOT NULL,
 114     user_password text DEFAULT ''::text NOT NULL,
 115     user_newpassword text DEFAULT ''::text NOT NULL,
 116     user_email text DEFAULT ''::text NOT NULL,
 117     user_options text DEFAULT ''::text NOT NULL,
 118     user_touched timestamp without time zone DEFAULT '1900-01-01 00:00:00'::timestamp without time zone NOT NULL
 119 );
 120
 121
 122 CREATE TABLE user_newtalk (
 123     user_id integer NOT NULL,
 124     user_ip inet NOT NULL
 125 );
 126 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
 127 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
 128
 129 CREATE TABLE ipblocks (
 130     ipb_id serial PRIMARY KEY,
 131     ipb_address inet NOT NULL,
 132     ipb_user integer NOT NULL,
 133     ipb_by integer NOT NULL,
 134     ipb_reason text NOT NULL,
 135     ipb_timestamp timestamp without time zone NOT NULL,
 136     ipb_auto smallint NOT NULL,
 137     ipb_expiry timestamp with time zone NOT NULL
 138 );
 139 CREATE INDEX ipb_address_idx ON ipblocks (ipb_address);
 140 CREATE INDEX ipb_user_idx ON ipblocks (ipb_user);
 141
 142 CREATE TABLE math (
 143     math_inputhash varchar(16) PRIMARY KEY,
 144     math_outputhash varchar(16) NOT NULL,
 145     math_html_conservativeness smallint NOT NULL,
 146     math_html text NOT NULL,
 147     math_mathml text NOT NULL
 148 );
 149
 150 CREATE TABLE objectcache (
 151     keyname varchar(255) PRIMARY KEY,
 152     value text,
 153     exptime timestamp without time zone
 154 );
 155 CREATE INDEX oc_exptime ON objectcache (exptime);
 156
 157 CREATE TABLE archive (
 158     ar_namespace smallint NOT NULL,
 159     ar_title varchar(255) NOT NULL,
 160     ar_text text NOT NULL,
 161     ar_comment text NOT NULL,
 162     ar_user integer NOT NULL,
 163     ar_user_text varchar(255) NOT NULL,
 164     ar_timestamp timestamp without time zone NOT NULL,
 165     ar_minor_edit smallint NOT NULL,
 166     ar_flags text NOT NULL
 167 );
 168
 169 CREATE TABLE recentchanges (
 170     rc_id serial PRIMARY KEY,
 171     rc_timestamp timestamp without time zone NOT NULL,
 172     rc_cur_time timestamp without time zone NOT NULL,
 173     rc_user integer NOT NULL,
 174     rc_user_text varchar(255) NOT NULL,
 175     rc_namespace smallint NOT NULL,
 176     rc_title varchar(255) NOT NULL,
 177     rc_comment text NOT NULL,
 178     rc_minor smallint NOT NULL,
 179     rc_bot smallint NOT NULL,
 180     rc_new smallint NOT NULL,
 181     rc_cur_id integer NOT NULL,
 182     rc_this_oldid integer NOT NULL,
 183     rc_last_oldid integer NOT NULL,
 184     rc_type smallint NOT NULL,
 185     rc_moved_to_ns smallint NOT NULL,
 186     rc_moved_to_title varchar NOT NULL,
 187     rc_ip inet,
 188     rc_patrolled smallint
 189 );
 190 CREATE INDEX rc_ip ON recentchanges (rc_ip);
 191 CREATE INDEX rc_new_name_ts_idx ON recentchanges (rc_new, rc_namespace, rc_timestamp);
 192 CREATE INDEX rc_cur_id_idx ON recentchanges (rc_cur_id);
 193
 194 CREATE TABLE site_stats (
 195     ss_row_id serial PRIMARY KEY,
 196     ss_total_views bigint NOT NULL,
 197     ss_total_edits bigint NOT NULL,
 198     ss_good_articles bigint NOT NULL
 199 );
 200
 201 CREATE TABLE oldimage (
 202     oi_name varchar(255) NOT NULL,
 203     oi_archive_name varchar(255) NOT NULL,
 204     oi_size integer NOT NULL,
 205     oi_description text NOT NULL,
 206     oi_user integer NOT NULL,
 207     oi_user_text varchar(255) NOT NULL,
 208     oi_timestamp timestamp without time zone NOT NULL
 209 );
 210 CREATE INDEX oi_name_idx ON oldimage (oi_name);
 211
 212 CREATE TABLE querycache (
 213     qc_type char(32),
 214     qc_value integer,
 215     qc_namespace smallint,
 216     qc_title char(255)
 217 );
 218 CREATE INDEX qc_type_value_idx ON querycache (qc_type, qc_value);
 219
 220 CREATE TABLE watchlist (
 221     wl_user integer NOT NULL,
 222     wl_namespace smallint NOT NULL,
 223     wl_title varchar(255) NOT NULL,
 224     PRIMARY KEY (wl_user, wl_namespace, wl_title)
 225 );
 226 CREATE INDEX idx_wl_user ON watchlist (wl_user);
 227 CREATE INDEX idx_wl_title ON watchlist (wl_title);
 228
 229 CREATE TABLE interwiki (
 230     iw_prefix char(32) PRIMARY KEY,
 231     iw_url varchar(127) NOT NULL,
 232     iw_local smallint NOT NULL
 233 );
 234
 235 CREATE TABLE profiling (
 236     pf_count integer,
 237     pf_time double precision,
 238     pf_name varchar(255) PRIMARY KEY
 239 );
 240
 241 CREATE TABLE validate (
 242     val_user integer DEFAULT 0 NOT NULL,
 243     val_title varchar(255) NOT NULL,
 244     val_timestamp timestamp without time zone NOT NULL,
 245     val_type integer DEFAULT 0 NOT NULL,
 246     val_value integer DEFAULT 0 NOT NULL,
 247     val_comment varchar(255) NOT NULL
 248 );
 249 CREATE INDEX val_user ON validate (val_user, val_title, val_timestamp);
 250
 251 CREATE TABLE user_rights (
 252     user_id integer PRIMARY KEY,
 253     user_rights text NOT NULL
 254 );
 255
 256 CREATE TABLE logging (
 257     log_type character(10) NOT NULL,
 258     log_action character(10) NOT NULL,
 259     log_timestamp timestamp without time zone NOT NULL,
 260     log_user integer NOT NULL,
 261     log_namespace smallint NOT NULL,
 262     log_title character varying(255) NOT NULL,
 263     log_comment character varying(255) NOT NULL
 264 );
 265
 266 CREATE INDEX log_type_time ON logging USING btree (log_type, log_timestamp);
 267 CREATE INDEX log_user_time ON logging USING btree (log_user, log_timestamp);
 268 CREATE INDEX log_page_time ON logging USING btree (log_namespace, log_title, log_timestamp);