update
[lhc/web/wiklou.git] / maintenance / ora / tables.sql
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
6
7 CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
8
9 CREATE TABLE mwuser ( -- replace reserved word 'user'
10 user_id INTEGER NOT NULL PRIMARY KEY,
11 user_name VARCHAR(255) NOT NULL UNIQUE,
12 user_real_name CLOB,
13 user_password CLOB,
14 user_newpassword CLOB,
15 user_newpass_time TIMESTAMP WITH TIME ZONE,
16 user_token CHAR(32),
17 user_email CLOB,
18 user_email_token CHAR(32),
19 user_email_token_expires TIMESTAMP WITH TIME ZONE,
20 user_email_authenticated TIMESTAMP WITH TIME ZONE,
21 user_options CLOB,
22 user_touched TIMESTAMP WITH TIME ZONE,
23 user_registration TIMESTAMP WITH TIME ZONE,
24 user_editcount INTEGER
25 );
26 CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
27
28 -- Create a dummy user to satisfy fk contraints especially with revisions
29 INSERT INTO mwuser
30 VALUES (user_user_id_seq.nextval,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '', current_timestamp, current_timestamp, 0);
31
32 CREATE TABLE user_groups (
33 ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
34 ug_group CHAR(16) NOT NULL
35 );
36 CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
37
38 CREATE TABLE user_newtalk (
39 user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
40 user_ip VARCHAR(40) NULL
41 );
42 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
43 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
44
45 CREATE SEQUENCE page_page_id_seq;
46 CREATE TABLE page (
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
58 );
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);
62
63 CREATE TRIGGER page_set_random BEFORE INSERT ON page
64 FOR EACH ROW WHEN (new.page_random IS NULL)
65 BEGIN
66 SELECT dbms_random.value INTO :new.page_random FROM dual;
67 END;
68 /
69
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
75 rev_comment CLOB,
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,
81 rev_len INTEGER NULL,
82 rev_parent_id INTEGER DEFAULT NULL
83 );
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);
89
90
91 CREATE SEQUENCE text_old_id_val;
92 CREATE TABLE pagecontent ( -- replaces reserved word 'text'
93 old_id INTEGER NOT NULL PRIMARY KEY,
94 old_text CLOB,
95 old_flags CLOB
96 );
97
98
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
108 );
109 ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
110
111 CREATE TABLE archive (
112 ar_namespace SMALLINT NOT NULL,
113 ar_title VARCHAR(255) NOT NULL,
114 ar_text CLOB,
115 ar_comment CLOB,
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,
120 ar_flags CLOB,
121 ar_rev_id INTEGER,
122 ar_text_id INTEGER,
123 ar_deleted INTEGER DEFAULT '0' NOT NULL
124 );
125 CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
126
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
131 );
132 CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
133
134
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
139 );
140 CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
141
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
146 );
147 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
148
149 CREATE TABLE imagelinks (
150 il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
151 il_to VARCHAR(255) NOT NULL
152 );
153 CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
154
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
160 );
161 CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
162 CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey);
163
164 CREATE TABLE externallinks (
165 el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
166 el_to VARCHAR(2048) NOT NULL,
167 el_index CLOB NOT NULL
168 );
169 -- XXX CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
170 -- XXX CREATE INDEX externallinks_index ON externallinks (el_index);
171
172 CREATE TABLE langlinks (
173 ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
174 ll_lang VARCHAR(10),
175 ll_title VARCHAR(255)
176 );
177 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
178 CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);
179
180
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
190 );
191
192 CREATE TABLE hitcounter (
193 hc_id INTEGER NOT NULL
194 );
195
196
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
213 );
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);
217
218
219 CREATE TABLE image (
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,
224 img_metadata CLOB,
225 img_bits SMALLINT,
226 img_media_type CLOB,
227 img_major_mime CLOB DEFAULT 'unknown',
228 img_minor_mime CLOB DEFAULT 'unknown',
229 img_description CLOB,
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
233 );
234 CREATE INDEX img_size_idx ON image (img_size);
235 CREATE INDEX img_timestamp_idx ON image (img_timestamp);
236
237 CREATE TABLE oldimage (
238 oi_name VARCHAR(255) NOT NULL REFERENCES image(img_name),
239 oi_archive_name VARCHAR(255),
240 oi_size INTEGER NOT NULL,
241 oi_width INTEGER NOT NULL,
242 oi_height INTEGER NOT NULL,
243 oi_bits SMALLINT NOT NULL,
244 oi_description CLOB,
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,
248 oi_metadata CLOB,
249 oi_media_type VARCHAR(10) DEFAULT NULL,
250 oi_major_mime VARCHAR(11) DEFAULT 'unknown',
251 oi_minor_mime VARCHAR(32) DEFAULT 'unknown',
252 oi_deleted INTEGER DEFAULT 0 NOT NULL
253 );
254 CREATE INDEX oi_name ON oldimage (oi_name);
255
256 CREATE SEQUENCE filearchive_fa_id_seq;
257 CREATE TABLE filearchive (
258 fa_id INTEGER NOT NULL PRIMARY KEY,
259 fa_name VARCHAR(255) NOT NULL,
260 fa_archive_name VARCHAR(255),
261 fa_storage_group VARCHAR(16),
262 fa_storage_key CHAR(64),
263 fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
264 fa_deleted_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
265 fa_deleted_reason CLOB,
266 fa_size SMALLINT NOT NULL,
267 fa_width SMALLINT NOT NULL,
268 fa_height SMALLINT NOT NULL,
269 fa_metadata CLOB,
270 fa_bits SMALLINT,
271 fa_media_type CLOB,
272 fa_major_mime CLOB DEFAULT 'unknown',
273 fa_minor_mime CLOB DEFAULT 'unknown',
274 fa_description CLOB NOT NULL,
275 fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
276 fa_user_text CLOB NOT NULL,
277 fa_timestamp TIMESTAMP WITH TIME ZONE,
278 fa_deleted INTEGER DEFAULT '0' NOT NULL
279 );
280 CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
281 CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
282 CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
283 CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
284
285
286 CREATE SEQUENCE rc_rc_id_seq;
287 CREATE TABLE recentchanges (
288 rc_id INTEGER NOT NULL PRIMARY KEY,
289 rc_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
290 rc_cur_time TIMESTAMP WITH TIME ZONE NOT NULL,
291 rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
292 rc_user_text CLOB NOT NULL,
293 rc_namespace SMALLINT NOT NULL,
294 rc_title VARCHAR(255) NOT NULL,
295 rc_comment VARCHAR(255),
296 rc_minor CHAR DEFAULT '0' NOT NULL,
297 rc_bot CHAR DEFAULT '0' NOT NULL,
298 rc_new CHAR DEFAULT '0' NOT NULL,
299 rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL,
300 rc_this_oldid INTEGER NOT NULL,
301 rc_last_oldid INTEGER NOT NULL,
302 rc_type CHAR DEFAULT '0' NOT NULL,
303 rc_moved_to_ns SMALLINT,
304 rc_moved_to_title CLOB,
305 rc_patrolled CHAR DEFAULT '0' NOT NULL,
306 rc_ip VARCHAR(15),
307 rc_old_len INTEGER,
308 rc_new_len INTEGER,
309 rc_deleted INTEGER DEFAULT '0' NOT NULL,
310 rc_logid INTEGER DEFAULT '0' NOT NULL,
311 rc_log_type CLOB,
312 rc_log_action CLOB,
313 rc_params CLOB
314 );
315 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
316 CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
317 CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
318 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
319 CREATE INDEX rc_ip ON recentchanges (rc_ip);
320
321
322 CREATE TABLE watchlist (
323 wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
324 wl_namespace SMALLINT DEFAULT 0 NOT NULL,
325 wl_title VARCHAR(255) NOT NULL,
326 wl_notificationtimestamp TIMESTAMP WITH TIME ZONE
327 );
328 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
329
330
331 CREATE TABLE math (
332 math_inputhash VARCHAR(16) NOT NULL UNIQUE,
333 math_outputhash VARCHAR(16) NOT NULL,
334 math_html_conservativeness SMALLINT NOT NULL,
335 math_html CLOB,
336 math_mathml CLOB
337 );
338
339
340 CREATE TABLE interwiki (
341 iw_prefix VARCHAR(32) NOT NULL UNIQUE,
342 iw_url VARCHAR(127) NOT NULL,
343 iw_local CHAR NOT NULL,
344 iw_trans CHAR DEFAULT '0' NOT NULL
345 );
346
347 CREATE TABLE querycache (
348 qc_type CHAR(32) NOT NULL,
349 qc_value SMALLINT NOT NULL,
350 qc_namespace SMALLINT NOT NULL,
351 qc_title CHAR(255) NOT NULL
352 );
353 CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
354
355 CREATE TABLE querycache_info (
356 qci_type VARCHAR(32) UNIQUE,
357 qci_timestamp TIMESTAMP WITH TIME ZONE NULL
358 );
359
360 CREATE TABLE querycachetwo (
361 qcc_type CHAR(32) NOT NULL,
362 qcc_value SMALLINT DEFAULT 0 NOT NULL,
363 qcc_namespace INTEGER DEFAULT 0 NOT NULL,
364 qcc_title CHAR(255) DEFAULT '' NOT NULL,
365 qcc_namespacetwo INTEGER DEFAULT 0 NOT NULL,
366 qcc_titletwo CHAR(255) DEFAULT '' NOT NULL
367 );
368 CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
369 CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
370 CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
371
372
373 CREATE TABLE objectcache (
374 keyname CHAR(255) UNIQUE,
375 value BLOB,
376 exptime TIMESTAMP WITH TIME ZONE NOT NULL
377 );
378 CREATE INDEX objectcacache_exptime ON objectcache (exptime);
379
380 CREATE TABLE transcache (
381 tc_url VARCHAR(255) NOT NULL UNIQUE,
382 tc_contents CLOB NOT NULL,
383 tc_time TIMESTAMP WITH TIME ZONE NOT NULL
384 );
385
386
387 CREATE SEQUENCE log_log_id_seq;
388 CREATE TABLE logging (
389 log_type VARCHAR(10) NOT NULL,
390 log_action VARCHAR(10) NOT NULL,
391 log_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
392 log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
393 log_namespace SMALLINT NOT NULL,
394 log_title VARCHAR(255) NOT NULL,
395 log_comment VARCHAR(255),
396 log_params CLOB,
397 log_deleted INTEGER DEFAULT '0' NOT NULL,
398 log_id INTEGER NOT NULL PRIMARY KEY
399 );
400 CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
401 CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
402 CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
403
404 CREATE SEQUENCE trackbacks_tb_id_seq;
405 CREATE TABLE trackbacks (
406 tb_id INTEGER NOT NULL PRIMARY KEY,
407 tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
408 tb_title VARCHAR(255) NOT NULL,
409 tb_url VARCHAR(255) NOT NULL,
410 tb_ex CLOB,
411 tb_name VARCHAR(255)
412 );
413 CREATE INDEX trackback_page ON trackbacks (tb_page);
414
415 CREATE SEQUENCE job_job_id_seq;
416 CREATE TABLE job (
417 job_id INTEGER NOT NULL PRIMARY KEY,
418 job_cmd VARCHAR(255) NOT NULL,
419 job_namespace SMALLINT NOT NULL,
420 job_title VARCHAR(255) NOT NULL,
421 job_params CLOB NOT NULL
422 );
423 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
424
425 -- This table is not used unless profiling is turned on
426 --CREATE TABLE profiling (
427 -- pf_count INTEGER DEFAULT 0 NOT NULL,
428 -- pf_time NUMERIC(18,10) DEFAULT 0 NOT NULL,
429 -- pf_name CLOB NOT NULL,
430 -- pf_server CLOB NULL
431 --);
432 --CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
433
434 CREATE TABLE searchindex (
435 si_page INTEGER UNIQUE NOT NULL,
436 si_title VARCHAR(255) DEFAULT '' NOT NULL,
437 si_text CLOB NOT NULL
438 );
439
440
441 CREATE INDEX si_title_idx ON searchindex(si_title) INDEXTYPE IS ctxsys.context;
442 CREATE INDEX si_text_idx ON searchindex(si_text) INDEXTYPE IS ctxsys.context;