3 -- Database schema for MediaWiki PostgreSQL support
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,
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)
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
);
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
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
122 CREATE TABLE user_newtalk (
123 user_id
integer NOT NULL,
124 user_ip inet
NOT NULL
126 CREATE INDEX user_newtalk_id_idx
ON user_newtalk (user_id
);
127 CREATE INDEX user_newtalk_ip_idx
ON user_newtalk (user_ip
);
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
139 CREATE INDEX ipb_address_idx
ON ipblocks (ipb_address
);
140 CREATE INDEX ipb_user_idx
ON ipblocks (ipb_user
);
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
150 CREATE TABLE objectcache (
151 keyname
varchar(255) PRIMARY KEY,
153 exptime
timestamp without time zone
155 CREATE INDEX oc_exptime
ON objectcache (exptime
);
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
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,
188 rc_patrolled
smallint
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
);
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
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
210 CREATE INDEX oi_name_idx
ON oldimage (oi_name
);
212 CREATE TABLE querycache (
215 qc_namespace
smallint,
218 CREATE INDEX qc_type_value_idx
ON querycache (qc_type
, qc_value
);
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
)
226 CREATE INDEX idx_wl_user
ON watchlist (wl_user
);
227 CREATE INDEX idx_wl_title
ON watchlist (wl_title
);
229 CREATE TABLE interwiki (
230 iw_prefix
char(32) PRIMARY KEY,
231 iw_url
varchar(127) NOT NULL,
232 iw_local
smallint NOT NULL
235 CREATE TABLE profiling (
237 pf_time
double precision,
238 pf_name
varchar(255) PRIMARY KEY
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
249 CREATE INDEX val_user
ON validate (val_user
, val_title
, val_timestamp
);
251 CREATE TABLE user_rights (
252 user_id
integer PRIMARY KEY,
253 user_rights
text NOT NULL
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
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
);