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