2 -- Database schema for MediaWiki PostgreSQL support
6 CREATE SCHEMA mediawiki;
7 SET search_path=mediawiki;
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,
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
26 CREATE INDEX cur_title_namespace_idx ON cur (cur_title, cur_namespace);
27 CREATE INDEX cur_random_idx ON cur (cur_random);
28 CREATE INDEX cur_name_title_timestamp_idx ON cur (cur_namespace, cur_title, cur_timestamp);
29 CREATE INDEX cur_timestamp_idx ON cur (cur_timestamp);
32 old_id serial PRIMARY KEY,
33 old_namespace smallint NOT NULL,
34 old_title varchar(255) NOT NULL,
35 old_text text NOT NULL,
36 old_comment text NOT NULL,
37 old_user integer NOT NULL,
38 old_user_text varchar(255) NOT NULL,
39 old_timestamp timestamp without time zone NOT NULL,
40 old_minor_edit smallint NOT NULL,
41 old_flags text NOT NULL
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);
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)
52 CREATE INDEX bl_to_idx ON brokenlinks (bl_to);
54 CREATE TABLE hitcounter (
55 hc_id bigint DEFAULT 0 NOT NULL
57 CREATE INDEX hc_id_idx on hitcounter (hc_id);
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
67 CREATE INDEX img_size_idx ON image (img_size);
68 CREATE INDEX img_timestamp ON image (img_timestamp);
70 CREATE TABLE imagelinks (
73 PRIMARY KEY (il_from, il_to)
75 CREATE INDEX il_to_idx ON imagelinks (il_to);
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)
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);
89 l_from integer NOT NULL,
90 l_to integer NOT NULL,
91 PRIMARY KEY (l_from,l_to)
93 CREATE INDEX l_to_idx ON links (l_to);
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
101 CREATE RULE links_del AS ON DELETE TO links DO DELETE FROM linkscc WHERE (linkscc.lcc_pageid = old.l_from);
103 CREATE TABLE searchindex (
104 si_page integer PRIMARY KEY,
105 si_title varchar(255) NOT NULL,
106 si_text text NOT NULL
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 user_token char(32) DEFAULT '' NOT NULL
123 CREATE TABLE user_newtalk (
124 user_id integer NOT NULL,
125 user_ip inet NOT NULL
127 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
128 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
130 CREATE TABLE ipblocks (
131 ipb_id serial PRIMARY KEY,
132 ipb_address inet NOT NULL,
133 ipb_user integer NOT NULL,
134 ipb_by integer NOT NULL,
135 ipb_reason text NOT NULL,
136 ipb_timestamp timestamp without time zone NOT NULL,
137 ipb_auto smallint NOT NULL,
138 ipb_expiry timestamp without time zone NOT NULL
140 CREATE INDEX ipb_address_idx ON ipblocks (ipb_address);
141 CREATE INDEX ipb_user_idx ON ipblocks (ipb_user);
144 math_inputhash varchar(16) PRIMARY KEY,
145 math_outputhash varchar(16) NOT NULL,
146 math_html_conservativeness smallint NOT NULL,
151 CREATE TABLE objectcache (
152 keyname varchar(255) PRIMARY KEY,
154 exptime timestamp without time zone
156 CREATE INDEX oc_exptime ON objectcache (exptime);
158 CREATE TABLE archive (
159 ar_namespace smallint NOT NULL,
160 ar_title varchar(255) NOT NULL,
161 ar_text text NOT NULL,
162 ar_comment text NOT NULL,
163 ar_user integer NOT NULL,
164 ar_user_text varchar(255) NOT NULL,
165 ar_timestamp timestamp without time zone NOT NULL,
166 ar_minor_edit smallint NOT NULL,
167 ar_flags text NOT NULL
170 CREATE TABLE recentchanges (
171 rc_id serial PRIMARY KEY,
172 rc_timestamp timestamp without time zone NOT NULL,
173 rc_cur_time timestamp without time zone NOT NULL,
174 rc_user integer NOT NULL,
175 rc_user_text varchar(255) NOT NULL,
176 rc_namespace smallint NOT NULL,
177 rc_title varchar(255) NOT NULL,
178 rc_comment text NOT NULL,
179 rc_minor smallint NOT NULL,
180 rc_bot smallint NOT NULL,
181 rc_new smallint NOT NULL,
182 rc_cur_id integer NOT NULL,
183 rc_this_oldid integer NOT NULL,
184 rc_last_oldid integer NOT NULL,
185 rc_type smallint NOT NULL,
186 rc_moved_to_ns smallint,
187 rc_moved_to_title varchar,
189 rc_patrolled smallint
191 CREATE INDEX rc_ip ON recentchanges (rc_ip);
192 CREATE INDEX rc_new_name_ts_idx ON recentchanges (rc_new, rc_namespace, rc_timestamp);
193 CREATE INDEX rc_cur_id_idx ON recentchanges (rc_cur_id);
195 CREATE TABLE site_stats (
196 ss_row_id serial PRIMARY KEY,
197 ss_total_views bigint NOT NULL,
198 ss_total_edits bigint NOT NULL,
199 ss_good_articles bigint NOT NULL
202 CREATE TABLE oldimage (
203 oi_name varchar(255) NOT NULL,
204 oi_archive_name varchar(255) NOT NULL,
205 oi_size integer NOT NULL,
206 oi_description text NOT NULL,
207 oi_user integer NOT NULL,
208 oi_user_text varchar(255) NOT NULL,
209 oi_timestamp timestamp without time zone NOT NULL
211 CREATE INDEX oi_name_idx ON oldimage (oi_name);
213 CREATE TABLE querycache (
216 qc_namespace smallint,
219 CREATE INDEX qc_type_value_idx ON querycache (qc_type, qc_value);
221 CREATE TABLE watchlist (
222 wl_user integer NOT NULL,
223 wl_namespace smallint NOT NULL,
224 wl_title varchar(255) NOT NULL,
225 PRIMARY KEY (wl_user, wl_namespace, wl_title)
227 CREATE INDEX idx_wl_user ON watchlist (wl_user);
228 CREATE INDEX idx_wl_title ON watchlist (wl_title);
230 CREATE TABLE interwiki (
231 iw_prefix char(32) PRIMARY KEY,
232 iw_url varchar(127) NOT NULL,
233 iw_local smallint NOT NULL
236 CREATE TABLE profiling (
238 pf_time double precision,
239 pf_name varchar(255) PRIMARY KEY
242 CREATE TABLE validate (
243 val_user integer DEFAULT 0 NOT NULL,
244 val_title varchar(255) NOT NULL,
245 val_timestamp timestamp without time zone NOT NULL,
246 val_type integer DEFAULT 0 NOT NULL,
247 val_value integer DEFAULT 0 NOT NULL,
248 val_comment varchar(255) NOT NULL
250 CREATE INDEX val_user ON validate (val_user, val_title, val_timestamp);
252 CREATE TABLE user_rights (
253 user_id integer PRIMARY KEY,
254 user_rights text NOT NULL
257 CREATE TABLE logging (
258 log_type character(10) NOT NULL,
259 log_action character(10) NOT NULL,
260 log_timestamp timestamp without time zone NOT NULL,
261 log_user integer NOT NULL,
262 log_namespace smallint NOT NULL,
263 log_title character varying(255) NOT NULL,
264 log_comment character varying(255) NOT NULL
267 CREATE INDEX log_type_time ON logging USING btree (log_type, log_timestamp);
268 CREATE INDEX log_user_time ON logging USING btree (log_user, log_timestamp);
269 CREATE INDEX log_page_time ON logging USING btree (log_namespace, log_title, log_timestamp);
273 CREATE TABLE "group" (
274 group_id integer PRIMARY KEY,
275 group_name varchar(50) NOT NULL,
276 group_description varchar(255) NOT NULL,
277 group_rights text NOT NULL
280 -- Relation table between user and groups
281 CREATE TABLE user_groups (
282 ug_user integer NOT NULL,
283 ug_group integer NOT NULL,
284 PRIMARY KEY (ug_user,ug_group)