1 -- SQL to create the initial tables for the MediaWiki database.
2 -- This is read and executed by the install script; you should
3 -- not have to run it by itself unless doing a manual install.
4 -- This is the Oracle version (based on PostgreSQL schema).
5 -- For information about each table, please see the notes in maintenance/tables.sql
7 CREATE SEQUENCE user_user_id_seq
MINVALUE 0 START WITH 0;
9 CREATE TABLE mwuser ( -- replace reserved word 'user'
10 user_id
INTEGER NOT NULL PRIMARY KEY,
11 user_name
VARCHAR(255) NOT NULL UNIQUE,
14 user_newpassword
CLOB,
15 user_newpass_time
TIMESTAMP WITH TIME ZONE,
18 user_email_token
CHAR(32),
19 user_email_token_expires
TIMESTAMP WITH TIME ZONE,
20 user_email_authenticated
TIMESTAMP WITH TIME ZONE,
22 user_touched
TIMESTAMP WITH TIME ZONE,
23 user_registration
TIMESTAMP WITH TIME ZONE,
24 user_editcount
INTEGER
26 CREATE INDEX user_email_token_idx
ON mwuser (user_email_token
);
28 -- Create a dummy user to satisfy fk contraints especially with revisions
30 VALUES (user_user_id_seq.nextval
,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '', current_timestamp, current_timestamp, 0);
32 CREATE TABLE user_groups (
33 ug_user
INTEGER NULL REFERENCES mwuser(user_id
) ON DELETE CASCADE,
34 ug_group
CHAR(16) NOT NULL
36 CREATE UNIQUE INDEX user_groups_unique
ON user_groups (ug_user
, ug_group
);
38 CREATE TABLE user_newtalk (
39 user_id
INTEGER NOT NULL REFERENCES mwuser(user_id
) ON DELETE CASCADE,
40 user_ip
VARCHAR(40) NULL
42 CREATE INDEX user_newtalk_id_idx
ON user_newtalk (user_id
);
43 CREATE INDEX user_newtalk_ip_idx
ON user_newtalk (user_ip
);
45 CREATE SEQUENCE page_page_id_seq
;
47 page_id
INTEGER NOT NULL PRIMARY KEY,
48 page_namespace
SMALLINT NOT NULL,
49 page_title
VARCHAR(255) NOT NULL,
50 page_restrictions
CLOB,
51 page_counter
INTEGER DEFAULT 0 NOT NULL,
52 page_is_redirect
CHAR DEFAULT 0 NOT NULL,
53 page_is_new
CHAR DEFAULT 0 NOT NULL,
54 page_random
NUMERIC(15,14) NOT NULL,
55 page_touched
TIMESTAMP WITH TIME ZONE,
56 page_latest
INTEGER NOT NULL, -- FK?
57 page_len
INTEGER NOT NULL
59 CREATE UNIQUE INDEX page_unique_name
ON page (page_namespace
, page_title
);
60 CREATE INDEX page_random_idx
ON page (page_random
);
61 CREATE INDEX page_len_idx
ON page (page_len
);
63 CREATE TRIGGER page_set_random
BEFORE INSERT ON page
64 FOR EACH ROW WHEN (new.page_random
IS NULL)
66 SELECT dbms_random.
value INTO :new.page_random
FROM dual
;
70 CREATE SEQUENCE rev_rev_id_val
;
71 CREATE TABLE revision (
72 rev_id
INTEGER NOT NULL PRIMARY KEY,
73 rev_page
INTEGER NULL REFERENCES page (page_id
) ON DELETE CASCADE,
74 rev_text_id
INTEGER NULL, -- FK
76 rev_user
INTEGER NOT NULL REFERENCES mwuser(user_id
),
77 rev_user_text
VARCHAR(255) NOT NULL,
78 rev_timestamp
TIMESTAMP WITH TIME ZONE NOT NULL,
79 rev_minor_edit
CHAR DEFAULT '0' NOT NULL,
80 rev_deleted
CHAR DEFAULT '0' NOT NULL,
82 rev_parent_id
INTEGER DEFAULT NULL
84 CREATE UNIQUE INDEX revision_unique
ON revision (rev_page
, rev_id
);
85 CREATE INDEX rev_text_id_idx
ON revision (rev_text_id
);
86 CREATE INDEX rev_timestamp_idx
ON revision (rev_timestamp
);
87 CREATE INDEX rev_user_idx
ON revision (rev_user
);
88 CREATE INDEX rev_user_text_idx
ON revision (rev_user_text
);
91 CREATE SEQUENCE text_old_id_val
;
92 CREATE TABLE pagecontent ( -- replaces reserved word 'text'
93 old_id
INTEGER NOT NULL PRIMARY KEY,
99 CREATE SEQUENCE pr_id_val
;
100 CREATE TABLE page_restrictions (
101 pr_id
INTEGER NOT NULL UNIQUE,
102 pr_page
INTEGER NULL REFERENCES page (page_id
) ON DELETE CASCADE,
103 pr_type
VARCHAR(255) NOT NULL,
104 pr_level
VARCHAR(255) NOT NULL,
105 pr_cascade
SMALLINT NOT NULL,
106 pr_user
INTEGER NULL,
107 pr_expiry
TIMESTAMP WITH TIME ZONE NULL
109 ALTER TABLE page_restrictions
ADD CONSTRAINT page_restrictions_pk
PRIMARY KEY (pr_page
,pr_type
);
111 CREATE TABLE archive (
112 ar_namespace
SMALLINT NOT NULL,
113 ar_title
VARCHAR(255) NOT NULL,
116 ar_user
INTEGER NULL REFERENCES mwuser(user_id
) ON DELETE SET NULL,
117 ar_user_text
CLOB NOT NULL,
118 ar_timestamp
TIMESTAMP WITH TIME ZONE NOT NULL,
119 ar_minor_edit
CHAR DEFAULT '0' NOT NULL,
123 ar_deleted
INTEGER DEFAULT '0' NOT NULL
125 CREATE INDEX archive_name_title_timestamp
ON archive (ar_namespace
,ar_title
,ar_timestamp
);
127 CREATE TABLE redirect (
128 rd_from
INTEGER NOT NULL REFERENCES page(page_id
) ON DELETE CASCADE,
129 rd_namespace
SMALLINT NOT NULL,
130 rd_title
VARCHAR(255) NOT NULL
132 CREATE INDEX redirect_ns_title
ON redirect (rd_namespace
,rd_title
,rd_from
);
135 CREATE TABLE pagelinks (
136 pl_from
INTEGER NOT NULL REFERENCES page(page_id
) ON DELETE CASCADE,
137 pl_namespace
SMALLINT NOT NULL,
138 pl_title
VARCHAR(255) NOT NULL
140 CREATE UNIQUE INDEX pagelink_unique
ON pagelinks (pl_from
,pl_namespace
,pl_title
);
142 CREATE TABLE templatelinks (
143 tl_from
INTEGER NOT NULL REFERENCES page(page_id
) ON DELETE CASCADE,
144 tl_namespace
INTEGER NOT NULL,
145 tl_title
VARCHAR(255) NOT NULL
147 CREATE UNIQUE INDEX templatelinks_unique
ON templatelinks (tl_namespace
,tl_title
,tl_from
);
149 CREATE TABLE imagelinks (
150 il_from
INTEGER NOT NULL REFERENCES page(page_id
) ON DELETE CASCADE,
151 il_to
VARCHAR(255) NOT NULL
153 CREATE UNIQUE INDEX il_from
ON imagelinks (il_to
,il_from
);
155 CREATE TABLE categorylinks (
156 cl_from
INTEGER NOT NULL REFERENCES page(page_id
) ON DELETE CASCADE,
157 cl_to
VARCHAR(255) NOT NULL,
158 cl_sortkey
VARCHAR(86),
159 cl_timestamp
TIMESTAMP WITH TIME ZONE NOT NULL
161 CREATE UNIQUE INDEX cl_from
ON categorylinks (cl_from
, cl_to
);
162 CREATE INDEX cl_sortkey
ON categorylinks (cl_to
, cl_sortkey
);
164 CREATE TABLE externallinks (
165 el_from
INTEGER NOT NULL REFERENCES page(page_id
) ON DELETE CASCADE,
167 el_index
CLOB NOT NULL
169 -- XXX CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
170 -- XXX CREATE INDEX externallinks_index ON externallinks (el_index);
172 CREATE TABLE langlinks (
173 ll_from
INTEGER NOT NULL REFERENCES page (page_id
) ON DELETE CASCADE,
175 ll_title
VARCHAR(255)
177 CREATE UNIQUE INDEX langlinks_unique
ON langlinks (ll_from
,ll_lang
);
178 CREATE INDEX langlinks_lang_title
ON langlinks (ll_lang
,ll_title
);
181 CREATE TABLE site_stats (
182 ss_row_id
INTEGER NOT NULL UNIQUE,
183 ss_total_views
INTEGER DEFAULT 0,
184 ss_total_edits
INTEGER DEFAULT 0,
185 ss_good_articles
INTEGER DEFAULT 0,
186 ss_total_pages
INTEGER DEFAULT -1,
187 ss_users
INTEGER DEFAULT -1,
188 ss_admins
INTEGER DEFAULT -1,
189 ss_images
INTEGER DEFAULT 0
192 CREATE TABLE hitcounter (
193 hc_id
INTEGER NOT NULL
197 CREATE SEQUENCE ipblocks_ipb_id_val
;
198 CREATE TABLE ipblocks (
199 ipb_id
INTEGER NOT NULL PRIMARY KEY,
200 ipb_address
VARCHAR(255) NULL,
201 ipb_user
INTEGER NULL REFERENCES mwuser(user_id
) ON DELETE SET NULL,
202 ipb_by
INTEGER NOT NULL REFERENCES mwuser(user_id
) ON DELETE CASCADE,
203 ipb_reason
VARCHAR(255) NOT NULL,
204 ipb_timestamp
TIMESTAMP WITH TIME ZONE NOT NULL,
205 ipb_auto
CHAR DEFAULT '0' NOT NULL,
206 ipb_anon_only
CHAR DEFAULT '0' NOT NULL,
207 ipb_create_account
CHAR DEFAULT '1' NOT NULL,
208 ipb_enable_autoblock
CHAR DEFAULT '1' NOT NULL,
209 ipb_expiry
TIMESTAMP WITH TIME ZONE NOT NULL,
210 ipb_range_start
CHAR(8),
211 ipb_range_end
CHAR(8),
212 ipb_deleted
INTEGER DEFAULT '0' NOT NULL
214 CREATE INDEX ipb_address
ON ipblocks (ipb_address
);
215 CREATE INDEX ipb_user
ON ipblocks (ipb_user
);
216 CREATE INDEX ipb_range
ON ipblocks (ipb_range_start
,ipb_range_end
);
220 img_name
VARCHAR(255) NOT NULL PRIMARY KEY,
221 img_size
INTEGER NOT NULL,
222 img_width
INTEGER NOT NULL,
223 img_height
INTEGER NOT NULL,
227 img_major_mime
CLOB DEFAULT 'unknown',
228 img_minor_mime
CLOB DEFAULT 'unknown',
229 img_description
CLOB NOT NULL,
230 img_user
INTEGER NULL REFERENCES mwuser(user_id
) ON DELETE SET NULL,
231 img_user_text
CLOB NOT NULL,
232 img_timestamp
TIMESTAMP WITH TIME ZONE
234 CREATE INDEX img_size_idx
ON image (img_size
);
235 CREATE INDEX img_timestamp_idx
ON image (img_timestamp
);
237 CREATE TABLE oldimage (
238 oi_name
VARCHAR(255) NOT NULL REFERENCES image(img_name
),
239 oi_archive_name
VARCHAR(255) NOT NULL,
240 oi_size
INTEGER NOT NULL,
241 oi_width
INTEGER NOT NULL,
242 oi_height
INTEGER NOT NULL,
243 oi_bits
SMALLINT NOT NULL,
245 oi_user
INTEGER NULL REFERENCES mwuser(user_id
) ON DELETE SET NULL,
246 oi_user_text
CLOB NOT NULL,
247 oi_timestamp
TIMESTAMP WITH TIME ZONE NOT NULL
249 CREATE INDEX oi_name
ON oldimage (oi_name
);
251 CREATE SEQUENCE filearchive_fa_id_seq
;
252 CREATE TABLE filearchive (
253 fa_id
INTEGER NOT NULL PRIMARY KEY,
254 fa_name
VARCHAR(255) NOT NULL,
255 fa_archive_name
VARCHAR(255),
256 fa_storage_group
VARCHAR(16),
257 fa_storage_key
CHAR(64),
258 fa_deleted_user
INTEGER NULL REFERENCES mwuser(user_id
) ON DELETE SET NULL,
259 fa_deleted_timestamp
TIMESTAMP WITH TIME ZONE NOT NULL,
260 fa_deleted_reason
CLOB,
261 fa_size
SMALLINT NOT NULL,
262 fa_width
SMALLINT NOT NULL,
263 fa_height
SMALLINT NOT NULL,
267 fa_major_mime
CLOB DEFAULT 'unknown',
268 fa_minor_mime
CLOB DEFAULT 'unknown',
269 fa_description
CLOB NOT NULL,
270 fa_user
INTEGER NULL REFERENCES mwuser(user_id
) ON DELETE SET NULL,
271 fa_user_text
CLOB NOT NULL,
272 fa_timestamp
TIMESTAMP WITH TIME ZONE,
273 fa_deleted
INTEGER DEFAULT '0' NOT NULL
275 CREATE INDEX fa_name_time
ON filearchive (fa_name
, fa_timestamp
);
276 CREATE INDEX fa_dupe
ON filearchive (fa_storage_group
, fa_storage_key
);
277 CREATE INDEX fa_notime
ON filearchive (fa_deleted_timestamp
);
278 CREATE INDEX fa_nouser
ON filearchive (fa_deleted_user
);
281 CREATE SEQUENCE rc_rc_id_seq
;
282 CREATE TABLE recentchanges (
283 rc_id
INTEGER NOT NULL PRIMARY KEY,
284 rc_timestamp
TIMESTAMP WITH TIME ZONE NOT NULL,
285 rc_cur_time
TIMESTAMP WITH TIME ZONE NOT NULL,
286 rc_user
INTEGER NULL REFERENCES mwuser(user_id
) ON DELETE SET NULL,
287 rc_user_text
CLOB NOT NULL,
288 rc_namespace
SMALLINT NOT NULL,
289 rc_title
VARCHAR(255) NOT NULL,
290 rc_comment
VARCHAR(255),
291 rc_minor
CHAR DEFAULT '0' NOT NULL,
292 rc_bot
CHAR DEFAULT '0' NOT NULL,
293 rc_new
CHAR DEFAULT '0' NOT NULL,
294 rc_cur_id
INTEGER NULL REFERENCES page(page_id
) ON DELETE SET NULL,
295 rc_this_oldid
INTEGER NOT NULL,
296 rc_last_oldid
INTEGER NOT NULL,
297 rc_type
CHAR DEFAULT '0' NOT NULL,
298 rc_moved_to_ns
SMALLINT,
299 rc_moved_to_title
CLOB,
300 rc_patrolled
CHAR DEFAULT '0' NOT NULL,
304 rc_deleted
INTEGER DEFAULT '0' NOT NULL,
305 rc_logid
INTEGER DEFAULT '0' NOT NULL,
310 CREATE INDEX rc_timestamp
ON recentchanges (rc_timestamp
);
311 CREATE INDEX rc_namespace_title
ON recentchanges (rc_namespace
, rc_title
);
312 CREATE INDEX rc_cur_id
ON recentchanges (rc_cur_id
);
313 CREATE INDEX new_name_timestamp
ON recentchanges (rc_new
, rc_namespace
, rc_timestamp
);
314 CREATE INDEX rc_ip
ON recentchanges (rc_ip
);
317 CREATE TABLE watchlist (
318 wl_user
INTEGER NOT NULL REFERENCES mwuser(user_id
) ON DELETE CASCADE,
319 wl_namespace
SMALLINT DEFAULT 0 NOT NULL,
320 wl_title
VARCHAR(255) NOT NULL,
321 wl_notificationtimestamp
TIMESTAMP WITH TIME ZONE
323 CREATE UNIQUE INDEX wl_user_namespace_title
ON watchlist (wl_namespace
, wl_title
, wl_user
);
327 math_inputhash
VARCHAR(16) NOT NULL UNIQUE,
328 math_outputhash
VARCHAR(16) NOT NULL,
329 math_html_conservativeness
SMALLINT NOT NULL,
335 CREATE TABLE interwiki (
336 iw_prefix
CHAR(32) NOT NULL UNIQUE,
337 iw_url
CHAR(127) NOT NULL,
338 iw_local
CHAR NOT NULL,
339 iw_trans
CHAR DEFAULT '0' NOT NULL
342 CREATE TABLE querycache (
343 qc_type
CHAR(32) NOT NULL,
344 qc_value
SMALLINT NOT NULL,
345 qc_namespace
SMALLINT NOT NULL,
346 qc_title
CHAR(255) NOT NULL
348 CREATE INDEX querycache_type_value
ON querycache (qc_type
, qc_value
);
350 CREATE TABLE querycache_info (
351 qci_type
VARCHAR(32) UNIQUE,
352 qci_timestamp
TIMESTAMP WITH TIME ZONE NULL
355 CREATE TABLE querycachetwo (
356 qcc_type
CHAR(32) NOT NULL,
357 qcc_value
SMALLINT DEFAULT 0 NOT NULL,
358 qcc_namespace
INTEGER DEFAULT 0 NOT NULL,
359 qcc_title
CHAR(255) DEFAULT '' NOT NULL,
360 qcc_namespacetwo
INTEGER DEFAULT 0 NOT NULL,
361 qcc_titletwo
CHAR(255) DEFAULT '' NOT NULL
363 CREATE INDEX querycachetwo_type_value
ON querycachetwo (qcc_type
, qcc_value
);
364 CREATE INDEX querycachetwo_title
ON querycachetwo (qcc_type
,qcc_namespace
,qcc_title
);
365 CREATE INDEX querycachetwo_titletwo
ON querycachetwo (qcc_type
,qcc_namespacetwo
,qcc_titletwo
);
368 CREATE TABLE objectcache (
369 keyname
CHAR(255) UNIQUE,
371 exptime
TIMESTAMP WITH TIME ZONE NOT NULL
373 CREATE INDEX objectcacache_exptime
ON objectcache (exptime
);
375 CREATE TABLE transcache (
376 tc_url
VARCHAR(255) NOT NULL UNIQUE,
377 tc_contents
CLOB NOT NULL,
378 tc_time
TIMESTAMP WITH TIME ZONE NOT NULL
382 CREATE SEQUENCE log_log_id_seq
;
383 CREATE TABLE logging (
384 log_type
VARCHAR(10) NOT NULL,
385 log_action
VARCHAR(10) NOT NULL,
386 log_timestamp
TIMESTAMP WITH TIME ZONE NOT NULL,
387 log_user
INTEGER REFERENCES mwuser(user_id
) ON DELETE SET NULL,
388 log_namespace
SMALLINT NOT NULL,
389 log_title
VARCHAR(255) NOT NULL,
390 log_comment
VARCHAR(255),
392 log_deleted
INTEGER DEFAULT '0' NOT NULL,
393 log_id
INTEGER NOT NULL PRIMARY KEY
395 CREATE INDEX logging_type_name
ON logging (log_type
, log_timestamp
);
396 CREATE INDEX logging_user_time
ON logging (log_timestamp
, log_user
);
397 CREATE INDEX logging_page_time
ON logging (log_namespace
, log_title
, log_timestamp
);
399 CREATE SEQUENCE trackbacks_tb_id_seq
;
400 CREATE TABLE trackbacks (
401 tb_id
INTEGER NOT NULL PRIMARY KEY,
402 tb_page
INTEGER REFERENCES page(page_id
) ON DELETE CASCADE,
403 tb_title
VARCHAR(255) NOT NULL,
404 tb_url
VARCHAR(255) NOT NULL,
408 CREATE INDEX trackback_page
ON trackbacks (tb_page
);
410 CREATE SEQUENCE job_job_id_seq
;
412 job_id
INTEGER NOT NULL PRIMARY KEY,
413 job_cmd
VARCHAR(255) NOT NULL,
414 job_namespace
SMALLINT NOT NULL,
415 job_title
VARCHAR(255) NOT NULL,
416 job_params
CLOB NOT NULL
418 CREATE INDEX job_cmd_namespace_title
ON job (job_cmd
, job_namespace
, job_title
);
420 -- This table is not used unless profiling is turned on
421 --CREATE TABLE profiling (
422 -- pf_count INTEGER DEFAULT 0 NOT NULL,
423 -- pf_time NUMERIC(18,10) DEFAULT 0 NOT NULL,
424 -- pf_name CLOB NOT NULL,
425 -- pf_server CLOB NULL
427 --CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);