dépôts / lhc / web / wiklou.git / blob
? search:
summary | shortlog | log | commit | commitdiff | tree
history | raw | HEAD
Web installation support for Oracle.
[lhc/web/wiklou.git] / maintenance / ora / tables.sql
1 -- defines must comply with ^define\s*([^\s=]*)\s*=\s?'\{\$([^\}]*)\}';
2 define mw_prefix='{$wgDBprefix}';
3
4
5 CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
6 CREATE TABLE &mw_prefix.mwuser ( -- replace reserved word 'user'
7 user_id NUMBER NOT NULL,
8 user_name VARCHAR2(255) NOT NULL,
9 user_real_name VARCHAR2(512),
10 user_password VARCHAR2(255),
11 user_newpassword VARCHAR2(255),
12 user_newpass_time TIMESTAMP(6) WITH TIME ZONE,
13 user_token VARCHAR2(32),
14 user_email VARCHAR2(255),
15 user_email_token VARCHAR2(32),
16 user_email_token_expires TIMESTAMP(6) WITH TIME ZONE,
17 user_email_authenticated TIMESTAMP(6) WITH TIME ZONE,
18 user_options CLOB,
19 user_touched TIMESTAMP(6) WITH TIME ZONE,
20 user_registration TIMESTAMP(6) WITH TIME ZONE,
21 user_editcount NUMBER
22 );
23 ALTER TABLE &mw_prefix.mwuser ADD CONSTRAINT &mw_prefix.mwuser_pk PRIMARY KEY (user_id);
24 CREATE UNIQUE INDEX &mw_prefix.mwuser_u01 ON &mw_prefix.mwuser (user_name);
25 CREATE INDEX &mw_prefix.mwuser_i01 ON &mw_prefix.mwuser (user_email_token);
26
27 -- Create a dummy user to satisfy fk contraints especially with revisions
28 INSERT INTO &mw_prefix.mwuser
29 VALUES (user_user_id_seq.nextval,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '', current_timestamp, current_timestamp, 0);
30
31 CREATE TABLE &mw_prefix.user_groups (
32 ug_user NUMBER NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE,
33 ug_group VARCHAR2(16) NOT NULL
34 );
35 CREATE UNIQUE INDEX &mw_prefix.user_groups_u01 ON &mw_prefix.user_groups (ug_user,ug_group);
36 CREATE INDEX &mw_prefix.user_groups_i01 ON &mw_prefix.user_groups (ug_group);
37
38 CREATE TABLE &mw_prefix.user_newtalk (
39 user_id NUMBER NOT NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE,
40 user_ip VARCHAR2(40) NULL,
41 user_last_timestamp TIMESTAMP(6) WITH TIME ZONE
42 );
43 CREATE INDEX &mw_prefix.user_newtalk_i01 ON &mw_prefix.user_newtalk (user_id);
44 CREATE INDEX &mw_prefix.user_newtalk_i02 ON &mw_prefix.user_newtalk (user_ip);
45
46 CREATE TABLE &mw_prefix.user_properties (
47 up_user NUMBER NOT NULL,
48 up_property VARCHAR2(32) NOT NULL,
49 up_value BLOB
50 );
51 CREATE UNIQUE INDEX &mw_prefix.user_properties_u01 on &mw_prefix.user_properties (up_user,up_property);
52 CREATE INDEX &mw_prefix.user_properties_i01 on &mw_prefix.user_properties (up_property);
53
54
55 CREATE SEQUENCE page_page_id_seq;
56 CREATE TABLE &mw_prefix.page (
57 page_id NUMBER NOT NULL,
58 page_namespace NUMBER NOT NULL,
59 page_title VARCHAR2(255) NOT NULL,
60 page_restrictions VARCHAR2(255),
61 page_counter NUMBER DEFAULT 0 NOT NULL,
62 page_is_redirect CHAR(1) DEFAULT 0 NOT NULL,
63 page_is_new CHAR(1) DEFAULT 0 NOT NULL,
64 page_random NUMBER(15,14) NOT NULL,
65 page_touched TIMESTAMP(6) WITH TIME ZONE,
66 page_latest NUMBER NOT NULL, -- FK?
67 page_len NUMBER NOT NULL
68 );
69 ALTER TABLE &mw_prefix.page ADD CONSTRAINT &mw_prefix.page_pk PRIMARY KEY (page_id);
70 CREATE UNIQUE INDEX &mw_prefix.page_u01 ON &mw_prefix.page (page_namespace,page_title);
71 CREATE INDEX &mw_prefix.page_i01 ON &mw_prefix.page (page_random);
72 CREATE INDEX &mw_prefix.page_i02 ON &mw_prefix.page (page_len);
73
74 /*$mw$*/
75 CREATE TRIGGER &mw_prefix.page_set_random BEFORE INSERT ON &mw_prefix.page
76 FOR EACH ROW WHEN (new.page_random IS NULL)
77 BEGIN
78 SELECT dbms_random.value INTO :NEW.page_random FROM dual;
79 END;
80 /*$mw$*/
81
82 CREATE SEQUENCE rev_rev_id_val;
83 CREATE TABLE &mw_prefix.revision (
84 rev_id NUMBER NOT NULL,
85 rev_page NUMBER NULL REFERENCES &mw_prefix.page (page_id) ON DELETE CASCADE,
86 rev_text_id NUMBER NULL,
87 rev_comment VARCHAR2(255),
88 rev_user NUMBER NOT NULL REFERENCES &mw_prefix.mwuser(user_id),
89 rev_user_text VARCHAR2(255) NOT NULL,
90 rev_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
91 rev_minor_edit CHAR(1) DEFAULT '0' NOT NULL,
92 rev_deleted CHAR(1) DEFAULT '0' NOT NULL,
93 rev_len NUMBER NULL,
94 rev_parent_id NUMBER DEFAULT NULL
95 );
96 ALTER TABLE &mw_prefix.revision ADD CONSTRAINT &mw_prefix.revision_pk PRIMARY KEY (rev_id);
97 CREATE UNIQUE INDEX &mw_prefix.revision_u01 ON &mw_prefix.revision (rev_page, rev_id);
98 CREATE INDEX &mw_prefix.revision_i01 ON &mw_prefix.revision (rev_timestamp);
99 CREATE INDEX &mw_prefix.revision_i02 ON &mw_prefix.revision (rev_page,rev_timestamp);
100 CREATE INDEX &mw_prefix.revision_i03 ON &mw_prefix.revision (rev_user,rev_timestamp);
101 CREATE INDEX &mw_prefix.revision_i04 ON &mw_prefix.revision (rev_user_text,rev_timestamp);
102
103 CREATE SEQUENCE text_old_id_val;
104 CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text'
105 old_id NUMBER NOT NULL,
106 old_text CLOB,
107 old_flags VARCHAR2(255)
108 );
109 ALTER TABLE &mw_prefix.pagecontent ADD CONSTRAINT &mw_prefix.pagecontent_pk PRIMARY KEY (old_id);
110
111 CREATE TABLE &mw_prefix.archive (
112 ar_namespace NUMBER NOT NULL,
113 ar_title VARCHAR2(255) NOT NULL,
114 ar_text CLOB,
115 ar_comment VARCHAR2(255),
116 ar_user NUMBER NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL,
117 ar_user_text VARCHAR2(255) NOT NULL,
118 ar_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
119 ar_minor_edit CHAR(1) DEFAULT '0' NOT NULL,
120 ar_flags VARCHAR2(255),
121 ar_rev_id NUMBER,
122 ar_text_id NUMBER,
123 ar_deleted NUMBER DEFAULT '0' NOT NULL
124 );
125 CREATE INDEX &mw_prefix.archive_i01 ON &mw_prefix.archive (ar_namespace,ar_title,ar_timestamp);
126 CREATE INDEX &mw_prefix.archive_i02 ON &mw_prefix.archive (ar_user_text,ar_timestamp);
127
128
129 CREATE TABLE &mw_prefix.pagelinks (
130 pl_from NUMBER NOT NULL REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE,
131 pl_namespace NUMBER NOT NULL,
132 pl_title VARCHAR2(255) NOT NULL
133 );
134 CREATE UNIQUE INDEX &mw_prefix.pagelinks_u01 ON &mw_prefix.pagelinks (pl_from,pl_namespace,pl_title);
135 CREATE UNIQUE INDEX &mw_prefix.pagelinks_u02 ON &mw_prefix.pagelinks (pl_namespace,pl_title,pl_from);
136
137 CREATE TABLE &mw_prefix.templatelinks (
138 tl_from NUMBER NOT NULL REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE,
139 tl_namespace NUMBER NOT NULL,
140 tl_title VARCHAR2(255) NOT NULL
141 );
142 CREATE UNIQUE INDEX &mw_prefix.templatelinks_u01 ON &mw_prefix.templatelinks (tl_from,tl_namespace,tl_title);
143 CREATE UNIQUE INDEX &mw_prefix.templatelinks_u02 ON &mw_prefix.templatelinks (tl_namespace,tl_title,tl_from);
144
145 CREATE TABLE &mw_prefix.imagelinks (
146 il_from NUMBER NOT NULL REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE,
147 il_to VARCHAR2(255) NOT NULL
148 );
149 CREATE UNIQUE INDEX &mw_prefix.imagelinks_u01 ON &mw_prefix.imagelinks (il_from,il_to);
150 CREATE UNIQUE INDEX &mw_prefix.imagelinks_u02 ON &mw_prefix.imagelinks (il_to,il_from);
151
152
153 CREATE TABLE &mw_prefix.categorylinks (
154 cl_from NUMBER NOT NULL REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE,
155 cl_to VARCHAR2(255) NOT NULL,
156 cl_sortkey VARCHAR2(255),
157 cl_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL
158 );
159 CREATE UNIQUE INDEX &mw_prefix.categorylinks_u01 ON &mw_prefix.categorylinks (cl_from,cl_to);
160 CREATE INDEX &mw_prefix.categorylinks_i01 ON &mw_prefix.categorylinks (cl_to,cl_sortkey,cl_from);
161 CREATE INDEX &mw_prefix.categorylinks_i02 ON &mw_prefix.categorylinks (cl_to,cl_timestamp);
162
163 CREATE SEQUENCE category_cat_id_val;
164 CREATE TABLE &mw_prefix.category (
165 cat_id NUMBER NOT NULL,
166 cat_title VARCHAR2(255) NOT NULL,
167 cat_pages NUMBER DEFAULT 0 NOT NULL,
168 cat_subcats NUMBER DEFAULT 0 NOT NULL,
169 cat_files NUMBER DEFAULT 0 NOT NULL,
170 cat_hidden NUMBER DEFAULT 0 NOT NULL
171 );
172 ALTER TABLE &mw_prefix.category ADD CONSTRAINT &mw_prefix.category_pk PRIMARY KEY (cat_id);
173 CREATE UNIQUE INDEX &mw_prefix.category_u01 ON &mw_prefix.category (cat_title);
174 CREATE INDEX &mw_prefix.category_i01 ON &mw_prefix.category (cat_pages);
175
176 CREATE TABLE &mw_prefix.externallinks (
177 el_from NUMBER NOT NULL REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE,
178 el_to VARCHAR2(2048) NOT NULL,
179 el_index VARCHAR2(2048) NOT NULL
180 );
181 CREATE INDEX &mw_prefix.externallinks_i01 ON &mw_prefix.externallinks (el_from, el_to);
182 CREATE INDEX &mw_prefix.externallinks_i02 ON &mw_prefix.externallinks (el_to, el_from);
183 CREATE INDEX &mw_prefix.externallinks_i03 ON &mw_prefix.externallinks (el_index);
184
185 CREATE TABLE &mw_prefix.langlinks (
186 ll_from NUMBER NOT NULL REFERENCES &mw_prefix.page (page_id) ON DELETE CASCADE,
187 ll_lang VARCHAR2(20),
188 ll_title VARCHAR2(255)
189 );
190 CREATE UNIQUE INDEX &mw_prefix.langlinks_u01 ON &mw_prefix.langlinks (ll_from, ll_lang);
191 CREATE INDEX &mw_prefix.langlinks_i01 ON &mw_prefix.langlinks (ll_lang, ll_title);
192
193 CREATE TABLE &mw_prefix.site_stats (
194 ss_row_id NUMBER NOT NULL ,
195 ss_total_views NUMBER DEFAULT 0,
196 ss_total_edits NUMBER DEFAULT 0,
197 ss_good_articles NUMBER DEFAULT 0,
198 ss_total_pages NUMBER DEFAULT -1,
199 ss_users NUMBER DEFAULT -1,
200 ss_active_users NUMBER DEFAULT -1,
201 ss_admins NUMBER DEFAULT -1,
202 ss_images NUMBER DEFAULT 0
203 );
204 CREATE UNIQUE INDEX &mw_prefix.site_stats_u01 ON &mw_prefix.site_stats (ss_row_id);
205
206 CREATE TABLE &mw_prefix.hitcounter (
207 hc_id NUMBER NOT NULL
208 );
209
210 CREATE SEQUENCE ipblocks_ipb_id_val;
211 CREATE TABLE &mw_prefix.ipblocks (
212 ipb_id NUMBER NOT NULL,
213 ipb_address VARCHAR2(255) NULL,
214 ipb_user NUMBER NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL,
215 ipb_by NUMBER NOT NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE,
216 ipb_by_text VARCHAR2(255) NOT NULL,
217 ipb_reason VARCHAR2(255) NOT NULL,
218 ipb_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
219 ipb_auto CHAR(1) DEFAULT '0' NOT NULL,
220 ipb_anon_only CHAR(1) DEFAULT '0' NOT NULL,
221 ipb_create_account CHAR(1) DEFAULT '1' NOT NULL,
222 ipb_enable_autoblock CHAR(1) DEFAULT '1' NOT NULL,
223 ipb_expiry TIMESTAMP(6) WITH TIME ZONE NOT NULL,
224 ipb_range_start VARCHAR2(255),
225 ipb_range_end VARCHAR2(255),
226 ipb_deleted CHAR(1) DEFAULT '0' NOT NULL,
227 ipb_block_email CHAR(1) DEFAULT '0' NOT NULL,
228 ipb_allow_usertalk CHAR(1) DEFAULT '0' NOT NULL
229 );
230 ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_pk PRIMARY KEY (ipb_id);
231 CREATE UNIQUE INDEX &mw_prefix.ipblocks_u01 ON &mw_prefix.ipblocks (ipb_address, ipb_user, ipb_auto, ipb_anon_only);
232 CREATE INDEX &mw_prefix.ipblocks_i01 ON &mw_prefix.ipblocks (ipb_user);
233 CREATE INDEX &mw_prefix.ipblocks_i02 ON &mw_prefix.ipblocks (ipb_range_start, ipb_range_end);
234 CREATE INDEX &mw_prefix.ipblocks_i03 ON &mw_prefix.ipblocks (ipb_timestamp);
235 CREATE INDEX &mw_prefix.ipblocks_i04 ON &mw_prefix.ipblocks (ipb_expiry);
236
237 CREATE TABLE image (
238 img_name VARCHAR2(255) NOT NULL,
239 img_size NUMBER NOT NULL,
240 img_width NUMBER NOT NULL,
241 img_height NUMBER NOT NULL,
242 img_metadata CLOB,
243 img_bits NUMBER,
244 img_media_type VARCHAR2(32),
245 img_major_mime VARCHAR2(32) DEFAULT 'unknown',
246 img_minor_mime VARCHAR2(32) DEFAULT 'unknown',
247 img_description VARCHAR2(255),
248 img_user NUMBER NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL,
249 img_user_text VARCHAR2(255) NOT NULL,
250 img_timestamp TIMESTAMP(6) WITH TIME ZONE,
251 img_sha1 VARCHAR2(32)
252 );
253 ALTER TABLE &mw_prefix.image ADD CONSTRAINT &mw_prefix.image_pk PRIMARY KEY (img_name);
254 CREATE INDEX &mw_prefix.image_i01 ON &mw_prefix.image (img_user_text,img_timestamp);
255 CREATE INDEX &mw_prefix.image_i02 ON &mw_prefix.image (img_size);
256 CREATE INDEX &mw_prefix.image_i03 ON &mw_prefix.image (img_timestamp);
257 CREATE INDEX &mw_prefix.image_i04 ON &mw_prefix.image (img_sha1);
258
259
260 CREATE TABLE &mw_prefix.oldimage (
261 oi_name VARCHAR2(255) NOT NULL REFERENCES &mw_prefix.image(img_name),
262 oi_archive_name VARCHAR2(255),
263 oi_size NUMBER NOT NULL,
264 oi_width NUMBER NOT NULL,
265 oi_height NUMBER NOT NULL,
266 oi_bits NUMBER NOT NULL,
267 oi_description VARCHAR2(255),
268 oi_user NUMBER NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL,
269 oi_user_text VARCHAR2(255) NOT NULL,
270 oi_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
271 oi_metadata CLOB,
272 oi_media_type VARCHAR2(32) DEFAULT NULL,
273 oi_major_mime VARCHAR2(32) DEFAULT 'unknown',
274 oi_minor_mime VARCHAR2(32) DEFAULT 'unknown',
275 oi_deleted NUMBER DEFAULT 0 NOT NULL,
276 oi_sha1 VARCHAR2(32)
277 );
278 CREATE INDEX &mw_prefix.oldimage_i01 ON &mw_prefix.oldimage (oi_user_text,oi_timestamp);
279 CREATE INDEX &mw_prefix.oldimage_i02 ON &mw_prefix.oldimage (oi_name,oi_timestamp);
280 CREATE INDEX &mw_prefix.oldimage_i03 ON &mw_prefix.oldimage (oi_name,oi_archive_name);
281 CREATE INDEX &mw_prefix.oldimage_i04 ON &mw_prefix.oldimage (oi_sha1);
282
283
284 CREATE SEQUENCE filearchive_fa_id_seq;
285 CREATE TABLE &mw_prefix.filearchive (
286 fa_id NUMBER NOT NULL,
287 fa_name VARCHAR2(255) NOT NULL,
288 fa_archive_name VARCHAR2(255),
289 fa_storage_group VARCHAR2(16),
290 fa_storage_key VARCHAR2(64),
291 fa_deleted_user NUMBER NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL,
292 fa_deleted_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
293 fa_deleted_reason CLOB,
294 fa_size NUMBER NOT NULL,
295 fa_width NUMBER NOT NULL,
296 fa_height NUMBER NOT NULL,
297 fa_metadata CLOB,
298 fa_bits NUMBER,
299 fa_media_type VARCHAR2(32) DEFAULT NULL,
300 fa_major_mime VARCHAR2(32) DEFAULT 'unknown',
301 fa_minor_mime VARCHAR2(32) DEFAULT 'unknown',
302 fa_description VARCHAR2(255) NOT NULL,
303 fa_user NUMBER NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL,
304 fa_user_text VARCHAR2(255) NOT NULL,
305 fa_timestamp TIMESTAMP(6) WITH TIME ZONE,
306 fa_deleted NUMBER DEFAULT '0' NOT NULL
307 );
308 ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_pk PRIMARY KEY (fa_id);
309 CREATE INDEX &mw_prefix.filearchive_i01 ON &mw_prefix.filearchive (fa_name, fa_timestamp);
310 CREATE INDEX &mw_prefix.filearchive_i02 ON &mw_prefix.filearchive (fa_storage_group, fa_storage_key);
311 CREATE INDEX &mw_prefix.filearchive_i03 ON &mw_prefix.filearchive (fa_deleted_timestamp);
312 CREATE INDEX &mw_prefix.filearchive_i04 ON &mw_prefix.filearchive (fa_user_text,fa_timestamp);
313
314 CREATE SEQUENCE rc_rc_id_seq;
315 CREATE TABLE &mw_prefix.recentchanges (
316 rc_id NUMBER NOT NULL,
317 rc_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
318 rc_cur_time TIMESTAMP(6) WITH TIME ZONE NOT NULL,
319 rc_user NUMBER NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL,
320 rc_user_text VARCHAR2(255) NOT NULL,
321 rc_namespace NUMBER NOT NULL,
322 rc_title VARCHAR2(255) NOT NULL,
323 rc_comment VARCHAR2(255),
324 rc_minor CHAR(1) DEFAULT '0' NOT NULL,
325 rc_bot CHAR(1) DEFAULT '0' NOT NULL,
326 rc_new CHAR(1) DEFAULT '0' NOT NULL,
327 rc_cur_id NUMBER NULL REFERENCES &mw_prefix.page(page_id) ON DELETE SET NULL,
328 rc_this_oldid NUMBER NOT NULL,
329 rc_last_oldid NUMBER NOT NULL,
330 rc_type CHAR(1) DEFAULT '0' NOT NULL,
331 rc_moved_to_ns NUMBER,
332 rc_moved_to_title VARCHAR2(255),
333 rc_patrolled CHAR(1) DEFAULT '0' NOT NULL,
334 rc_ip VARCHAR2(15),
335 rc_old_len NUMBER,
336 rc_new_len NUMBER,
337 rc_deleted NUMBER DEFAULT '0' NOT NULL,
338 rc_logid NUMBER DEFAULT '0' NOT NULL,
339 rc_log_type VARCHAR2(255),
340 rc_log_action VARCHAR2(255),
341 rc_params CLOB
342 );
343 ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_pk PRIMARY KEY (rc_id);
344 CREATE INDEX &mw_prefix.recentchanges_i01 ON &mw_prefix.recentchanges (rc_timestamp);
345 CREATE INDEX &mw_prefix.recentchanges_i02 ON &mw_prefix.recentchanges (rc_namespace, rc_title);
346 CREATE INDEX &mw_prefix.recentchanges_i03 ON &mw_prefix.recentchanges (rc_cur_id);
347 CREATE INDEX &mw_prefix.recentchanges_i04 ON &mw_prefix.recentchanges (rc_new,rc_namespace,rc_timestamp);
348 CREATE INDEX &mw_prefix.recentchanges_i05 ON &mw_prefix.recentchanges (rc_ip);
349 CREATE INDEX &mw_prefix.recentchanges_i06 ON &mw_prefix.recentchanges (rc_namespace, rc_user_text);
350 CREATE INDEX &mw_prefix.recentchanges_i07 ON &mw_prefix.recentchanges (rc_user_text, rc_timestamp);
351
352 CREATE TABLE &mw_prefix.watchlist (
353 wl_user NUMBER NOT NULL REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE,
354 wl_namespace NUMBER DEFAULT 0 NOT NULL,
355 wl_title VARCHAR2(255) NOT NULL,
356 wl_notificationtimestamp TIMESTAMP(6) WITH TIME ZONE
357 );
358 CREATE UNIQUE INDEX &mw_prefix.watchlist_u01 ON &mw_prefix.watchlist (wl_user, wl_namespace, wl_title);
359 CREATE INDEX &mw_prefix.watchlist_i01 ON &mw_prefix.watchlist (wl_namespace, wl_title);
360
361
362 CREATE TABLE &mw_prefix.math (
363 math_inputhash VARCHAR2(16) NOT NULL,
364 math_outputhash VARCHAR2(16) NOT NULL,
365 math_html_conservativeness NUMBER NOT NULL,
366 math_html CLOB,
367 math_mathml CLOB
368 );
369 CREATE UNIQUE INDEX &mw_prefix.math_u01 ON &mw_prefix.math (math_inputhash);
370
371 CREATE TABLE &mw_prefix.searchindex (
372 si_page NUMBER NOT NULL,
373 si_title VARCHAR2(255) DEFAULT '' NOT NULL,
374 si_text CLOB NOT NULL
375 );
376 CREATE UNIQUE INDEX &mw_prefix.searchindex_u01 ON &mw_prefix.searchindex (si_page);
377
378 CREATE TABLE &mw_prefix.interwiki (
379 iw_prefix VARCHAR2(32) NOT NULL,
380 iw_url VARCHAR2(127) NOT NULL,
381 iw_local CHAR(1) NOT NULL,
382 iw_trans CHAR(1) DEFAULT '0' NOT NULL
383 );
384 CREATE UNIQUE INDEX &mw_prefix.interwiki_u01 ON &mw_prefix.interwiki (iw_prefix);
385
386 CREATE TABLE &mw_prefix.querycache (
387 qc_type VARCHAR2(32) NOT NULL,
388 qc_value NUMBER NOT NULL,
389 qc_namespace NUMBER NOT NULL,
390 qc_title VARCHAR2(255) NOT NULL
391 );
392 CREATE INDEX &mw_prefix.querycache_u01 ON &mw_prefix.querycache (qc_type,qc_value);
393
394 CREATE TABLE &mw_prefix.objectcache (
395 keyname VARCHAR2(255) ,
396 value BLOB,
397 exptime TIMESTAMP(6) WITH TIME ZONE NOT NULL
398 );
399 CREATE INDEX &mw_prefix.objectcache_i01 ON &mw_prefix.objectcache (exptime);
400
401 CREATE TABLE &mw_prefix.transcache (
402 tc_url VARCHAR2(255) NOT NULL,
403 tc_contents CLOB NOT NULL,
404 tc_time TIMESTAMP(6) WITH TIME ZONE NOT NULL
405 );
406 CREATE UNIQUE INDEX &mw_prefix.transcache_u01 ON &mw_prefix.transcache (tc_url);
407
408
409 CREATE SEQUENCE log_log_id_seq;
410 CREATE TABLE &mw_prefix.logging (
411 log_id NUMBER NOT NULL,
412 log_type VARCHAR2(10) NOT NULL,
413 log_action VARCHAR2(10) NOT NULL,
414 log_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
415 log_user NUMBER REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL,
416 log_namespace NUMBER NOT NULL,
417 log_title VARCHAR2(255) NOT NULL,
418 log_comment VARCHAR2(255),
419 log_params CLOB,
420 log_deleted NUMBER DEFAULT '0' NOT NULL
421 );
422 ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_pk PRIMARY KEY (log_id);
423 CREATE INDEX &mw_prefix.logging_i01 ON &mw_prefix.logging (log_type, log_timestamp);
424 CREATE INDEX &mw_prefix.logging_i02 ON &mw_prefix.logging (log_user, log_timestamp);
425 CREATE INDEX &mw_prefix.logging_i03 ON &mw_prefix.logging (log_namespace, log_title, log_timestamp);
426 CREATE INDEX &mw_prefix.logging_i04 ON &mw_prefix.logging (log_timestamp);
427
428 CREATE TABLE &mw_prefix.log_search (
429 ls_field VARCHAR2(32) NOT NULL,
430 ls_value VARCHAR2(255) NOT NULL,
431 ls_log_id NuMBER DEFAULT 0 NOT NULL
432 );
433 ALTER TABLE log_search ADD CONSTRAINT log_search_pk PRIMARY KEY (ls_field,ls_value,ls_log_id);
434 CREATE INDEX &mw_prefix.log_search_i01 ON &mw_prefix.log_search (ls_log_id);
435
436 CREATE SEQUENCE trackbacks_tb_id_seq;
437 CREATE TABLE &mw_prefix.trackbacks (
438 tb_id NUMBER NOT NULL,
439 tb_page NUMBER REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE,
440 tb_title VARCHAR2(255) NOT NULL,
441 tb_url VARCHAR2(255) NOT NULL,
442 tb_ex CLOB,
443 tb_name VARCHAR2(255)
444 );
445 ALTER TABLE &mw_prefix.trackbacks ADD CONSTRAINT &mw_prefix.trackbacks_pk PRIMARY KEY (tb_id);
446 CREATE INDEX &mw_prefix.trackbacks_i01 ON &mw_prefix.trackbacks (tb_page);
447
448 CREATE SEQUENCE job_job_id_seq;
449 CREATE TABLE &mw_prefix.job (
450 job_id NUMBER NOT NULL,
451 job_cmd VARCHAR2(60) NOT NULL,
452 job_namespace NUMBER NOT NULL,
453 job_title VARCHAR2(255) NOT NULL,
454 job_params CLOB NOT NULL
455 );
456 ALTER TABLE &mw_prefix.job ADD CONSTRAINT &mw_prefix.job_pk PRIMARY KEY (job_id);
457 CREATE INDEX &mw_prefix.job_i01 ON &mw_prefix.job (job_cmd, job_namespace, job_title);
458
459 CREATE TABLE &mw_prefix.querycache_info (
460 qci_type VARCHAR2(32) NOT NULL,
461 qci_timestamp TIMESTAMP(6) WITH TIME ZONE NULL
462 );
463 CREATE UNIQUE INDEX &mw_prefix.querycache_info_u01 ON &mw_prefix.querycache_info (qci_type);
464
465 CREATE TABLE &mw_prefix.redirect (
466 rd_from NUMBER NOT NULL REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE,
467 rd_namespace NUMBER NOT NULL,
468 rd_title VARCHAR2(255) NOT NULL,
469 rd_interwiki VARCHAR2(32),
470 rd_fragment VARCHAR2(255)
471 );
472 CREATE INDEX &mw_prefix.redirect_i01 ON &mw_prefix.redirect (rd_namespace,rd_title,rd_from);
473
474 CREATE TABLE &mw_prefix.querycachetwo (
475 qcc_type VARCHAR2(32) NOT NULL,
476 qcc_value NUMBER DEFAULT 0 NOT NULL,
477 qcc_namespace NUMBER DEFAULT 0 NOT NULL,
478 qcc_title VARCHAR2(255) DEFAULT '' NOT NULL,
479 qcc_namespacetwo NUMBER DEFAULT 0 NOT NULL,
480 qcc_titletwo VARCHAR2(255) DEFAULT '' NOT NULL
481 );
482 CREATE INDEX &mw_prefix.querycachetwo_i01 ON &mw_prefix.querycachetwo (qcc_type,qcc_value);
483 CREATE INDEX &mw_prefix.querycachetwo_i02 ON &mw_prefix.querycachetwo (qcc_type,qcc_namespace,qcc_title);
484 CREATE INDEX &mw_prefix.querycachetwo_i03 ON &mw_prefix.querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
485
486 CREATE SEQUENCE pr_id_val;
487 CREATE TABLE &mw_prefix.page_restrictions (
488 pr_id NUMBER NOT NULL,
489 pr_page NUMBER NULL REFERENCES &mw_prefix.page (page_id) ON DELETE CASCADE,
490 pr_type VARCHAR2(255) NOT NULL,
491 pr_level VARCHAR2(255) NOT NULL,
492 pr_cascade NUMBER NOT NULL,
493 pr_user NUMBER NULL,
494 pr_expiry TIMESTAMP(6) WITH TIME ZONE NULL
495 );
496 ALTER TABLE &mw_prefix.page_restrictions ADD CONSTRAINT &mw_prefix.page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
497 CREATE INDEX &mw_prefix.page_restrictions_i01 ON &mw_prefix.page_restrictions (pr_type,pr_level);
498 CREATE INDEX &mw_prefix.page_restrictions_i02 ON &mw_prefix.page_restrictions (pr_level);
499 CREATE INDEX &mw_prefix.page_restrictions_i03 ON &mw_prefix.page_restrictions (pr_cascade);
500
501 CREATE TABLE &mw_prefix.protected_titles (
502 pt_namespace NUMBER NOT NULL,
503 pt_title VARCHAR2(255) NOT NULL,
504 pt_user NUMBER NOT NULL,
505 pt_reason VARCHAR2(255),
506 pt_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
507 pt_expiry VARCHAR2(14) NOT NULL,
508 pt_create_perm VARCHAR2(60) NOT NULL
509 );
510 CREATE UNIQUE INDEX &mw_prefix.protected_titles_u01 ON &mw_prefix.protected_titles (pt_namespace,pt_title);
511 CREATE INDEX &mw_prefix.protected_titles_i01 ON &mw_prefix.protected_titles (pt_timestamp);
512
513 CREATE TABLE &mw_prefix.page_props (
514 pp_page NUMBER NOT NULL,
515 pp_propname VARCHAR2(60) NOT NULL,
516 pp_value BLOB NOT NULL
517 );
518 CREATE UNIQUE INDEX &mw_prefix.page_props_u01 ON &mw_prefix.page_props (pp_page,pp_propname);
519
520
521 CREATE TABLE &mw_prefix.updatelog (
522 ul_key VARCHAR2(255) NOT NULL
523 );
524 ALTER TABLE &mw_prefix.updatelog ADD CONSTRAINT &mw_prefix.updatelog_pk PRIMARY KEY (ul_key);
525
526 CREATE TABLE &mw_prefix.change_tag (
527 ct_rc_id NUMBER NULL,
528 ct_log_id NUMBER NULL,
529 ct_rev_id NUMBER NULL,
530 ct_tag VARCHAR2(255) NOT NULL,
531 ct_params BLOB NULL
532 );
533 CREATE UNIQUE INDEX &mw_prefix.change_tag_u01 ON &mw_prefix.change_tag (ct_rc_id,ct_tag);
534 CREATE UNIQUE INDEX &mw_prefix.change_tag_u02 ON &mw_prefix.change_tag (ct_log_id,ct_tag);
535 CREATE UNIQUE INDEX &mw_prefix.change_tag_u03 ON &mw_prefix.change_tag (ct_rev_id,ct_tag);
536 CREATE INDEX &mw_prefix.change_tag_i01 ON &mw_prefix.change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
537
538 CREATE TABLE &mw_prefix.tag_summary (
539 ts_rc_id NUMBER NULL,
540 ts_log_id NUMBER NULL,
541 ts_rev_id NUMBER NULL,
542 ts_tags BLOB NOT NULL
543 );
544 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u01 ON &mw_prefix.tag_summary (ts_rc_id);
545 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u02 ON &mw_prefix.tag_summary (ts_log_id);
546 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u03 ON &mw_prefix.tag_summary (ts_rev_id);
547
548 CREATE TABLE &mw_prefix.valid_tag (
549 vt_tag VARCHAR2(255) NOT NULL
550 );
551 ALTER TABLE &mw_prefix.valid_tag ADD CONSTRAINT &mw_prefix.valid_tag_pk PRIMARY KEY (vt_tag);
552
553 -- This table is not used unless profiling is turned on
554 --CREATE TABLE &mw_prefix.profiling (
555 -- pf_count NUMBER DEFAULT 0 NOT NULL,
556 -- pf_time NUMERIC(18,10) DEFAULT 0 NOT NULL,
557 -- pf_name CLOB NOT NULL,
558 -- pf_server CLOB NULL
559 --);
560 --CREATE UNIQUE INDEX &mw_prefix.profiling_u01 ON &mw_prefix.profiling (pf_name, pf_server);
561
562 CREATE INDEX si_title_idx ON &mw_prefix.searchindex(si_title) INDEXTYPE IS ctxsys.context;
563 CREATE INDEX si_text_idx ON &mw_prefix.searchindex(si_text) INDEXTYPE IS ctxsys.context;
564
565 CREATE TABLE &mw_prefix.wiki_field_info_full (
566 table_name VARCHAR2(35) NOT NULL,
567 column_name VARCHAR2(35) NOT NULL,
568 data_default VARCHAR2(4000),
569 data_length NUMBER NOT NULL,
570 data_type VARCHAR2(106),
571 not_null CHAR(1) NOT NULL,
572 prim NUMBER(1),
573 uniq NUMBER(1),
574 nonuniq NUMBER(1)
575 );
576 ALTER TABLE &mw_prefix.wiki_field_info_full ADD CONSTRAINT &mw_prefix.wiki_field_info_full_pk PRIMARY KEY (table_name, column_name);
577
578 /*$mw$*/
579 CREATE PROCEDURE &mw_prefix.fill_wiki_info IS
580 BEGIN
581 DELETE &mw_prefix.wiki_field_info_full;
582
583 FOR x_rec IN (SELECT '&mw_prefix.' || t.table_name table_name, t.column_name,
584 t.data_default, t.data_length, t.data_type,
585 DECODE (t.nullable, 'Y', '1', 'N', '0') not_null,
586 (SELECT 1
587 FROM user_cons_columns ucc,
588 user_constraints uc
589 WHERE ucc.table_name = t.table_name
590 AND ucc.column_name = t.column_name
591 AND uc.constraint_name = ucc.constraint_name
592 AND uc.constraint_type = 'P'
593 AND ROWNUM < 2) prim,
594 (SELECT 1
595 FROM user_ind_columns uic,
596 user_indexes ui
597 WHERE uic.table_name = t.table_name
598 AND uic.column_name = t.column_name
599 AND ui.index_name = uic.index_name
600 AND ui.uniqueness = 'UNIQUE'
601 AND ROWNUM < 2) uniq,
602 (SELECT 1
603 FROM user_ind_columns uic,
604 user_indexes ui
605 WHERE uic.table_name = t.table_name
606 AND uic.column_name = t.column_name
607 AND ui.index_name = uic.index_name
608 AND ui.uniqueness = 'NONUNIQUE'
609 AND ROWNUM < 2) nonuniq
610 FROM user_tab_columns t, user_tables ut
611 WHERE ut.table_name = t.table_name)
612 LOOP
613 INSERT INTO &mw_prefix.wiki_field_info_full
614 (table_name, column_name,
615 data_default, data_length,
616 data_type, not_null, prim,
617 uniq, nonuniq
618 )
619 VALUES (x_rec.table_name, x_rec.column_name,
620 x_rec.data_default, x_rec.data_length,
621 x_rec.data_type, x_rec.not_null, x_rec.prim,
622 x_rec.uniq, x_rec.nonuniq
623 );
624 END LOOP;
625 COMMIT;
626 END;
627 /*$mw$*/
628
629 /*$mw$*/
630 BEGIN
631 &mw_prefix.fill_wiki_info;
632 END;
633 /*$mw$*/
634
635 /*$mw$*/
636 CREATE OR REPLACE FUNCTION BITOR (x IN NUMBER, y IN NUMBER) RETURN NUMBER AS
637 BEGIN
638 RETURN (x + y - BITAND(x, y));
639 END;
640 /*$mw$*/
Wiklou, le Wiki du Wiklou