dépôts / lhc / web / wiklou.git / blob
? search:


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