maintenance/postgresql/pg_tables.sql

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