1 -- defines must comply with ^define\s*([^\s=]*)\s*=\s?'\{\$([^\}]*)\}';
2 define mw_prefix
='{$wgDBprefix}';
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,
19 user_touched
TIMESTAMP(6) WITH TIME ZONE,
20 user_registration
TIMESTAMP(6) WITH TIME ZONE,
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
);
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);
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
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
);
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
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
);
46 CREATE TABLE &mw_prefix.
user_properties (
47 up_user
NUMBER NOT NULL,
48 up_property
VARCHAR2(32) NOT NULL,
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
);
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
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
);
75 CREATE TRIGGER &mw_prefix.page_set_random
BEFORE INSERT ON &mw_prefix.page
76 FOR EACH ROW WHEN (new.page_random
IS NULL)
78 SELECT dbms_random.
value INTO :NEW.page_random
FROM dual
;
82 CREATE SEQUENCE revision_rev_id_seq
;
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,
94 rev_parent_id
NUMBER DEFAULT NULL
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
);
103 CREATE SEQUENCE text_old_id_seq
;
104 CREATE TABLE &mw_prefix.
pagecontent ( -- replaces reserved word 'text'
105 old_id
NUMBER NOT NULL,
107 old_flags
VARCHAR2(255)
109 ALTER TABLE &mw_prefix.pagecontent
ADD CONSTRAINT &mw_prefix.pagecontent_pk
PRIMARY KEY (old_id
);
111 CREATE TABLE &mw_prefix.
archive (
112 ar_namespace
NUMBER NOT NULL,
113 ar_title
VARCHAR2(255) NOT NULL,
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),
123 ar_deleted
NUMBER DEFAULT '0' NOT NULL,
128 CREATE INDEX &mw_prefix.archive_i01
ON &mw_prefix.
archive (ar_namespace
,ar_title
,ar_timestamp
);
129 CREATE INDEX &mw_prefix.archive_i02
ON &mw_prefix.
archive (ar_user_text
,ar_timestamp
);
132 CREATE TABLE &mw_prefix.
pagelinks (
133 pl_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
134 pl_namespace
NUMBER NOT NULL,
135 pl_title
VARCHAR2(255) NOT NULL
137 CREATE UNIQUE INDEX &mw_prefix.pagelinks_u01
ON &mw_prefix.
pagelinks (pl_from
,pl_namespace
,pl_title
);
138 CREATE UNIQUE INDEX &mw_prefix.pagelinks_u02
ON &mw_prefix.
pagelinks (pl_namespace
,pl_title
,pl_from
);
140 CREATE TABLE &mw_prefix.
templatelinks (
141 tl_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
142 tl_namespace
NUMBER NOT NULL,
143 tl_title
VARCHAR2(255) NOT NULL
145 CREATE UNIQUE INDEX &mw_prefix.templatelinks_u01
ON &mw_prefix.
templatelinks (tl_from
,tl_namespace
,tl_title
);
146 CREATE UNIQUE INDEX &mw_prefix.templatelinks_u02
ON &mw_prefix.
templatelinks (tl_namespace
,tl_title
,tl_from
);
148 CREATE TABLE &mw_prefix.
imagelinks (
149 il_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
150 il_to
VARCHAR2(255) NOT NULL
152 CREATE UNIQUE INDEX &mw_prefix.imagelinks_u01
ON &mw_prefix.
imagelinks (il_from
,il_to
);
153 CREATE UNIQUE INDEX &mw_prefix.imagelinks_u02
ON &mw_prefix.
imagelinks (il_to
,il_from
);
156 CREATE TABLE &mw_prefix.
categorylinks (
157 cl_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
158 cl_to
VARCHAR2(255) NOT NULL,
159 cl_sortkey
VARCHAR2(255),
160 cl_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL
162 CREATE UNIQUE INDEX &mw_prefix.categorylinks_u01
ON &mw_prefix.
categorylinks (cl_from
,cl_to
);
163 CREATE INDEX &mw_prefix.categorylinks_i01
ON &mw_prefix.
categorylinks (cl_to
,cl_sortkey
,cl_from
);
164 CREATE INDEX &mw_prefix.categorylinks_i02
ON &mw_prefix.
categorylinks (cl_to
,cl_timestamp
);
166 CREATE SEQUENCE category_cat_id_seq
;
167 CREATE TABLE &mw_prefix.
category (
168 cat_id
NUMBER NOT NULL,
169 cat_title
VARCHAR2(255) NOT NULL,
170 cat_pages
NUMBER DEFAULT 0 NOT NULL,
171 cat_subcats
NUMBER DEFAULT 0 NOT NULL,
172 cat_files
NUMBER DEFAULT 0 NOT NULL,
173 cat_hidden
NUMBER DEFAULT 0 NOT NULL
175 ALTER TABLE &mw_prefix.category
ADD CONSTRAINT &mw_prefix.category_pk
PRIMARY KEY (cat_id
);
176 CREATE UNIQUE INDEX &mw_prefix.category_u01
ON &mw_prefix.
category (cat_title
);
177 CREATE INDEX &mw_prefix.category_i01
ON &mw_prefix.
category (cat_pages
);
179 CREATE TABLE &mw_prefix.
externallinks (
180 el_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
181 el_to
VARCHAR2(2048) NOT NULL,
182 el_index
VARCHAR2(2048) NOT NULL
184 CREATE INDEX &mw_prefix.externallinks_i01
ON &mw_prefix.
externallinks (el_from
, el_to
);
185 CREATE INDEX &mw_prefix.externallinks_i02
ON &mw_prefix.
externallinks (el_to
, el_from
);
186 CREATE INDEX &mw_prefix.externallinks_i03
ON &mw_prefix.
externallinks (el_index
);
188 CREATE TABLE &mw_prefix.
external_user (
189 eu_local_id
NUMBER NOT NULL,
190 eu_external_id
varchar2(255) NOT NULL
192 ALTER TABLE &mw_prefix.external_user
ADD CONSTRAINT &mw_prefix.external_user_pk
PRIMARY KEY (eu_local_id
);
193 CREATE UNIQUE INDEX &mw_prefix.external_user_u01
ON &mw_prefix.
external_user (eu_external_id
);
195 CREATE TABLE &mw_prefix.
langlinks (
196 ll_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page (page_id
) ON DELETE CASCADE,
197 ll_lang
VARCHAR2(20),
198 ll_title
VARCHAR2(255)
200 CREATE UNIQUE INDEX &mw_prefix.langlinks_u01
ON &mw_prefix.
langlinks (ll_from
, ll_lang
);
201 CREATE INDEX &mw_prefix.langlinks_i01
ON &mw_prefix.
langlinks (ll_lang
, ll_title
);
203 CREATE TABLE &mw_prefix.
site_stats (
204 ss_row_id
NUMBER NOT NULL ,
205 ss_total_views
NUMBER DEFAULT 0,
206 ss_total_edits
NUMBER DEFAULT 0,
207 ss_good_articles
NUMBER DEFAULT 0,
208 ss_total_pages
NUMBER DEFAULT -1,
209 ss_users
NUMBER DEFAULT -1,
210 ss_active_users
NUMBER DEFAULT -1,
211 ss_admins
NUMBER DEFAULT -1,
212 ss_images
NUMBER DEFAULT 0
214 CREATE UNIQUE INDEX &mw_prefix.site_stats_u01
ON &mw_prefix.
site_stats (ss_row_id
);
216 CREATE TABLE &mw_prefix.
hitcounter (
217 hc_id
NUMBER NOT NULL
220 CREATE SEQUENCE ipblocks_ipb_id_seq
;
221 CREATE TABLE &mw_prefix.
ipblocks (
222 ipb_id
NUMBER NOT NULL,
223 ipb_address
VARCHAR2(255) NULL,
224 ipb_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
225 ipb_by
NUMBER NOT NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE CASCADE,
226 ipb_by_text
VARCHAR2(255) NOT NULL,
227 ipb_reason
VARCHAR2(255) NOT NULL,
228 ipb_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
229 ipb_auto
CHAR(1) DEFAULT '0' NOT NULL,
230 ipb_anon_only
CHAR(1) DEFAULT '0' NOT NULL,
231 ipb_create_account
CHAR(1) DEFAULT '1' NOT NULL,
232 ipb_enable_autoblock
CHAR(1) DEFAULT '1' NOT NULL,
233 ipb_expiry
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
234 ipb_range_start
VARCHAR2(255),
235 ipb_range_end
VARCHAR2(255),
236 ipb_deleted
CHAR(1) DEFAULT '0' NOT NULL,
237 ipb_block_email
CHAR(1) DEFAULT '0' NOT NULL,
238 ipb_allow_usertalk
CHAR(1) DEFAULT '0' NOT NULL
240 ALTER TABLE &mw_prefix.ipblocks
ADD CONSTRAINT &mw_prefix.ipblocks_pk
PRIMARY KEY (ipb_id
);
241 CREATE UNIQUE INDEX &mw_prefix.ipblocks_u01
ON &mw_prefix.
ipblocks (ipb_address
, ipb_user
, ipb_auto
, ipb_anon_only
);
242 CREATE INDEX &mw_prefix.ipblocks_i01
ON &mw_prefix.
ipblocks (ipb_user
);
243 CREATE INDEX &mw_prefix.ipblocks_i02
ON &mw_prefix.
ipblocks (ipb_range_start
, ipb_range_end
);
244 CREATE INDEX &mw_prefix.ipblocks_i03
ON &mw_prefix.
ipblocks (ipb_timestamp
);
245 CREATE INDEX &mw_prefix.ipblocks_i04
ON &mw_prefix.
ipblocks (ipb_expiry
);
247 CREATE TABLE &mw_prefix.
image (
248 img_name
VARCHAR2(255) NOT NULL,
249 img_size
NUMBER NOT NULL,
250 img_width
NUMBER NOT NULL,
251 img_height
NUMBER NOT NULL,
254 img_media_type
VARCHAR2(32),
255 img_major_mime
VARCHAR2(32) DEFAULT 'unknown',
256 img_minor_mime
VARCHAR2(32) DEFAULT 'unknown',
257 img_description
VARCHAR2(255),
258 img_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
259 img_user_text
VARCHAR2(255) NOT NULL,
260 img_timestamp
TIMESTAMP(6) WITH TIME ZONE,
261 img_sha1
VARCHAR2(32)
263 ALTER TABLE &mw_prefix.image
ADD CONSTRAINT &mw_prefix.image_pk
PRIMARY KEY (img_name
);
264 CREATE INDEX &mw_prefix.image_i01
ON &mw_prefix.
image (img_user_text
,img_timestamp
);
265 CREATE INDEX &mw_prefix.image_i02
ON &mw_prefix.
image (img_size
);
266 CREATE INDEX &mw_prefix.image_i03
ON &mw_prefix.
image (img_timestamp
);
267 CREATE INDEX &mw_prefix.image_i04
ON &mw_prefix.
image (img_sha1
);
270 CREATE TABLE &mw_prefix.
oldimage (
271 oi_name
VARCHAR2(255) NOT NULL REFERENCES &mw_prefix.
image(img_name
),
272 oi_archive_name
VARCHAR2(255),
273 oi_size
NUMBER NOT NULL,
274 oi_width
NUMBER NOT NULL,
275 oi_height
NUMBER NOT NULL,
276 oi_bits
NUMBER NOT NULL,
277 oi_description
VARCHAR2(255),
278 oi_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
279 oi_user_text
VARCHAR2(255) NOT NULL,
280 oi_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
282 oi_media_type
VARCHAR2(32) DEFAULT NULL,
283 oi_major_mime
VARCHAR2(32) DEFAULT 'unknown',
284 oi_minor_mime
VARCHAR2(32) DEFAULT 'unknown',
285 oi_deleted
NUMBER DEFAULT 0 NOT NULL,
288 CREATE INDEX &mw_prefix.oldimage_i01
ON &mw_prefix.
oldimage (oi_user_text
,oi_timestamp
);
289 CREATE INDEX &mw_prefix.oldimage_i02
ON &mw_prefix.
oldimage (oi_name
,oi_timestamp
);
290 CREATE INDEX &mw_prefix.oldimage_i03
ON &mw_prefix.
oldimage (oi_name
,oi_archive_name
);
291 CREATE INDEX &mw_prefix.oldimage_i04
ON &mw_prefix.
oldimage (oi_sha1
);
294 CREATE SEQUENCE filearchive_fa_id_seq
;
295 CREATE TABLE &mw_prefix.
filearchive (
296 fa_id
NUMBER NOT NULL,
297 fa_name
VARCHAR2(255) NOT NULL,
298 fa_archive_name
VARCHAR2(255),
299 fa_storage_group
VARCHAR2(16),
300 fa_storage_key
VARCHAR2(64),
301 fa_deleted_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
302 fa_deleted_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
303 fa_deleted_reason
CLOB,
304 fa_size
NUMBER NOT NULL,
305 fa_width
NUMBER NOT NULL,
306 fa_height
NUMBER NOT NULL,
309 fa_media_type
VARCHAR2(32) DEFAULT NULL,
310 fa_major_mime
VARCHAR2(32) DEFAULT 'unknown',
311 fa_minor_mime
VARCHAR2(32) DEFAULT 'unknown',
312 fa_description
VARCHAR2(255),
313 fa_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
314 fa_user_text
VARCHAR2(255) NOT NULL,
315 fa_timestamp
TIMESTAMP(6) WITH TIME ZONE,
316 fa_deleted
NUMBER DEFAULT '0' NOT NULL
318 ALTER TABLE &mw_prefix.filearchive
ADD CONSTRAINT &mw_prefix.filearchive_pk
PRIMARY KEY (fa_id
);
319 CREATE INDEX &mw_prefix.filearchive_i01
ON &mw_prefix.
filearchive (fa_name
, fa_timestamp
);
320 CREATE INDEX &mw_prefix.filearchive_i02
ON &mw_prefix.
filearchive (fa_storage_group
, fa_storage_key
);
321 CREATE INDEX &mw_prefix.filearchive_i03
ON &mw_prefix.
filearchive (fa_deleted_timestamp
);
322 CREATE INDEX &mw_prefix.filearchive_i04
ON &mw_prefix.
filearchive (fa_user_text
,fa_timestamp
);
324 CREATE SEQUENCE recentchanges_rc_id_seq
;
325 CREATE TABLE &mw_prefix.
recentchanges (
326 rc_id
NUMBER NOT NULL,
327 rc_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
328 rc_cur_time
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
329 rc_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
330 rc_user_text
VARCHAR2(255) NOT NULL,
331 rc_namespace
NUMBER NOT NULL,
332 rc_title
VARCHAR2(255) NOT NULL,
333 rc_comment
VARCHAR2(255),
334 rc_minor
CHAR(1) DEFAULT '0' NOT NULL,
335 rc_bot
CHAR(1) DEFAULT '0' NOT NULL,
336 rc_new
CHAR(1) DEFAULT '0' NOT NULL,
337 rc_cur_id
NUMBER NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE SET NULL,
338 rc_this_oldid
NUMBER NOT NULL,
339 rc_last_oldid
NUMBER NOT NULL,
340 rc_type
CHAR(1) DEFAULT '0' NOT NULL,
341 rc_moved_to_ns
NUMBER,
342 rc_moved_to_title
VARCHAR2(255),
343 rc_patrolled
CHAR(1) DEFAULT '0' NOT NULL,
347 rc_deleted
NUMBER DEFAULT '0' NOT NULL,
348 rc_logid
NUMBER DEFAULT '0' NOT NULL,
349 rc_log_type
VARCHAR2(255),
350 rc_log_action
VARCHAR2(255),
353 ALTER TABLE &mw_prefix.recentchanges
ADD CONSTRAINT &mw_prefix.recentchanges_pk
PRIMARY KEY (rc_id
);
354 CREATE INDEX &mw_prefix.recentchanges_i01
ON &mw_prefix.
recentchanges (rc_timestamp
);
355 CREATE INDEX &mw_prefix.recentchanges_i02
ON &mw_prefix.
recentchanges (rc_namespace
, rc_title
);
356 CREATE INDEX &mw_prefix.recentchanges_i03
ON &mw_prefix.
recentchanges (rc_cur_id
);
357 CREATE INDEX &mw_prefix.recentchanges_i04
ON &mw_prefix.
recentchanges (rc_new
,rc_namespace
,rc_timestamp
);
358 CREATE INDEX &mw_prefix.recentchanges_i05
ON &mw_prefix.
recentchanges (rc_ip
);
359 CREATE INDEX &mw_prefix.recentchanges_i06
ON &mw_prefix.
recentchanges (rc_namespace
, rc_user_text
);
360 CREATE INDEX &mw_prefix.recentchanges_i07
ON &mw_prefix.
recentchanges (rc_user_text
, rc_timestamp
);
362 CREATE TABLE &mw_prefix.
watchlist (
363 wl_user
NUMBER NOT NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE CASCADE,
364 wl_namespace
NUMBER DEFAULT 0 NOT NULL,
365 wl_title
VARCHAR2(255) NOT NULL,
366 wl_notificationtimestamp
TIMESTAMP(6) WITH TIME ZONE
368 CREATE UNIQUE INDEX &mw_prefix.watchlist_u01
ON &mw_prefix.
watchlist (wl_user
, wl_namespace
, wl_title
);
369 CREATE INDEX &mw_prefix.watchlist_i01
ON &mw_prefix.
watchlist (wl_namespace
, wl_title
);
372 CREATE TABLE &mw_prefix.
math (
373 math_inputhash
VARCHAR2(32) NOT NULL,
374 math_outputhash
VARCHAR2(32) NOT NULL,
375 math_html_conservativeness
NUMBER NOT NULL,
379 CREATE UNIQUE INDEX &mw_prefix.math_u01
ON &mw_prefix.
math (math_inputhash
);
381 CREATE TABLE &mw_prefix.
searchindex (
382 si_page
NUMBER NOT NULL,
383 si_title
VARCHAR2(255) DEFAULT '' NOT NULL,
384 si_text
CLOB NOT NULL
386 CREATE UNIQUE INDEX &mw_prefix.searchindex_u01
ON &mw_prefix.
searchindex (si_page
);
388 CREATE TABLE &mw_prefix.
interwiki (
389 iw_prefix
VARCHAR2(32) NOT NULL,
390 iw_url
VARCHAR2(127) NOT NULL,
391 iw_local
CHAR(1) NOT NULL,
392 iw_trans
CHAR(1) DEFAULT '0' NOT NULL
394 CREATE UNIQUE INDEX &mw_prefix.interwiki_u01
ON &mw_prefix.
interwiki (iw_prefix
);
396 CREATE TABLE &mw_prefix.
querycache (
397 qc_type
VARCHAR2(32) NOT NULL,
398 qc_value
NUMBER NOT NULL,
399 qc_namespace
NUMBER NOT NULL,
400 qc_title
VARCHAR2(255) NOT NULL
402 CREATE INDEX &mw_prefix.querycache_u01
ON &mw_prefix.
querycache (qc_type
,qc_value
);
404 CREATE TABLE &mw_prefix.
objectcache (
405 keyname
VARCHAR2(255) ,
407 exptime
TIMESTAMP(6) WITH TIME ZONE NOT NULL
409 CREATE INDEX &mw_prefix.objectcache_i01
ON &mw_prefix.
objectcache (exptime
);
411 CREATE TABLE &mw_prefix.
transcache (
412 tc_url
VARCHAR2(255) NOT NULL,
413 tc_contents
CLOB NOT NULL,
414 tc_time
TIMESTAMP(6) WITH TIME ZONE NOT NULL
416 CREATE UNIQUE INDEX &mw_prefix.transcache_u01
ON &mw_prefix.
transcache (tc_url
);
419 CREATE SEQUENCE logging_log_id_seq
;
420 CREATE TABLE &mw_prefix.
logging (
421 log_id
NUMBER NOT NULL,
422 log_type
VARCHAR2(10) NOT NULL,
423 log_action
VARCHAR2(10) NOT NULL,
424 log_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
425 log_user
NUMBER REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
426 log_user_text
VARCHAR2(255),
427 log_namespace
NUMBER NOT NULL,
428 log_title
VARCHAR2(255) NOT NULL,
430 log_comment
VARCHAR2(255),
432 log_deleted
NUMBER DEFAULT '0' NOT NULL
434 ALTER TABLE &mw_prefix.logging
ADD CONSTRAINT &mw_prefix.logging_pk
PRIMARY KEY (log_id
);
435 CREATE INDEX &mw_prefix.logging_i01
ON &mw_prefix.
logging (log_type
, log_timestamp
);
436 CREATE INDEX &mw_prefix.logging_i02
ON &mw_prefix.
logging (log_user
, log_timestamp
);
437 CREATE INDEX &mw_prefix.logging_i03
ON &mw_prefix.
logging (log_namespace
, log_title
, log_timestamp
);
438 CREATE INDEX &mw_prefix.logging_i04
ON &mw_prefix.
logging (log_timestamp
);
440 CREATE TABLE &mw_prefix.
log_search (
441 ls_field
VARCHAR2(32) NOT NULL,
442 ls_value
VARCHAR2(255) NOT NULL,
443 ls_log_id
NuMBER DEFAULT 0 NOT NULL
445 ALTER TABLE log_search
ADD CONSTRAINT log_search_pk
PRIMARY KEY (ls_field
,ls_value
,ls_log_id
);
446 CREATE INDEX &mw_prefix.log_search_i01
ON &mw_prefix.
log_search (ls_log_id
);
448 CREATE SEQUENCE trackbacks_tb_id_seq
;
449 CREATE TABLE &mw_prefix.
trackbacks (
450 tb_id
NUMBER NOT NULL,
451 tb_page
NUMBER REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
452 tb_title
VARCHAR2(255) NOT NULL,
453 tb_url
VARCHAR2(255) NOT NULL,
455 tb_name
VARCHAR2(255)
457 ALTER TABLE &mw_prefix.trackbacks
ADD CONSTRAINT &mw_prefix.trackbacks_pk
PRIMARY KEY (tb_id
);
458 CREATE INDEX &mw_prefix.trackbacks_i01
ON &mw_prefix.
trackbacks (tb_page
);
460 CREATE SEQUENCE job_job_id_seq
;
461 CREATE TABLE &mw_prefix.
job (
462 job_id
NUMBER NOT NULL,
463 job_cmd
VARCHAR2(60) NOT NULL,
464 job_namespace
NUMBER NOT NULL,
465 job_title
VARCHAR2(255) NOT NULL,
466 job_params
CLOB NOT NULL
468 ALTER TABLE &mw_prefix.job
ADD CONSTRAINT &mw_prefix.job_pk
PRIMARY KEY (job_id
);
469 CREATE INDEX &mw_prefix.job_i01
ON &mw_prefix.
job (job_cmd
, job_namespace
, job_title
);
471 CREATE TABLE &mw_prefix.
querycache_info (
472 qci_type
VARCHAR2(32) NOT NULL,
473 qci_timestamp
TIMESTAMP(6) WITH TIME ZONE NULL
475 CREATE UNIQUE INDEX &mw_prefix.querycache_info_u01
ON &mw_prefix.
querycache_info (qci_type
);
477 CREATE TABLE &mw_prefix.
redirect (
478 rd_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
479 rd_namespace
NUMBER NOT NULL,
480 rd_title
VARCHAR2(255) NOT NULL,
481 rd_interwiki
VARCHAR2(32),
482 rd_fragment
VARCHAR2(255)
484 CREATE INDEX &mw_prefix.redirect_i01
ON &mw_prefix.
redirect (rd_namespace
,rd_title
,rd_from
);
486 CREATE TABLE &mw_prefix.
querycachetwo (
487 qcc_type
VARCHAR2(32) NOT NULL,
488 qcc_value
NUMBER DEFAULT 0 NOT NULL,
489 qcc_namespace
NUMBER DEFAULT 0 NOT NULL,
490 qcc_title
VARCHAR2(255) DEFAULT '' NOT NULL,
491 qcc_namespacetwo
NUMBER DEFAULT 0 NOT NULL,
492 qcc_titletwo
VARCHAR2(255) DEFAULT '' NOT NULL
494 CREATE INDEX &mw_prefix.querycachetwo_i01
ON &mw_prefix.
querycachetwo (qcc_type
,qcc_value
);
495 CREATE INDEX &mw_prefix.querycachetwo_i02
ON &mw_prefix.
querycachetwo (qcc_type
,qcc_namespace
,qcc_title
);
496 CREATE INDEX &mw_prefix.querycachetwo_i03
ON &mw_prefix.
querycachetwo (qcc_type
,qcc_namespacetwo
,qcc_titletwo
);
498 CREATE SEQUENCE page_restrictions_pr_id_seq
;
499 CREATE TABLE &mw_prefix.
page_restrictions (
500 pr_id
NUMBER NOT NULL,
501 pr_page
NUMBER NULL REFERENCES &mw_prefix.
page (page_id
) ON DELETE CASCADE,
502 pr_type
VARCHAR2(255) NOT NULL,
503 pr_level
VARCHAR2(255) NOT NULL,
504 pr_cascade
NUMBER NOT NULL,
506 pr_expiry
TIMESTAMP(6) WITH TIME ZONE NULL
508 ALTER TABLE &mw_prefix.page_restrictions
ADD CONSTRAINT &mw_prefix.page_restrictions_pk
PRIMARY KEY (pr_page
,pr_type
);
509 CREATE INDEX &mw_prefix.page_restrictions_i01
ON &mw_prefix.
page_restrictions (pr_type
,pr_level
);
510 CREATE INDEX &mw_prefix.page_restrictions_i02
ON &mw_prefix.
page_restrictions (pr_level
);
511 CREATE INDEX &mw_prefix.page_restrictions_i03
ON &mw_prefix.
page_restrictions (pr_cascade
);
513 CREATE TABLE &mw_prefix.
protected_titles (
514 pt_namespace
NUMBER NOT NULL,
515 pt_title
VARCHAR2(255) NOT NULL,
516 pt_user
NUMBER NOT NULL,
517 pt_reason
VARCHAR2(255),
518 pt_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
519 pt_expiry
VARCHAR2(14) NOT NULL,
520 pt_create_perm
VARCHAR2(60) NOT NULL
522 CREATE UNIQUE INDEX &mw_prefix.protected_titles_u01
ON &mw_prefix.
protected_titles (pt_namespace
,pt_title
);
523 CREATE INDEX &mw_prefix.protected_titles_i01
ON &mw_prefix.
protected_titles (pt_timestamp
);
525 CREATE TABLE &mw_prefix.
page_props (
526 pp_page
NUMBER NOT NULL,
527 pp_propname
VARCHAR2(60) NOT NULL,
528 pp_value
BLOB NOT NULL
530 CREATE UNIQUE INDEX &mw_prefix.page_props_u01
ON &mw_prefix.
page_props (pp_page
,pp_propname
);
533 CREATE TABLE &mw_prefix.
updatelog (
534 ul_key
VARCHAR2(255) NOT NULL
536 ALTER TABLE &mw_prefix.updatelog
ADD CONSTRAINT &mw_prefix.updatelog_pk
PRIMARY KEY (ul_key
);
538 CREATE TABLE &mw_prefix.
change_tag (
539 ct_rc_id
NUMBER NULL,
540 ct_log_id
NUMBER NULL,
541 ct_rev_id
NUMBER NULL,
542 ct_tag
VARCHAR2(255) NOT NULL,
545 CREATE UNIQUE INDEX &mw_prefix.change_tag_u01
ON &mw_prefix.
change_tag (ct_rc_id
,ct_tag
);
546 CREATE UNIQUE INDEX &mw_prefix.change_tag_u02
ON &mw_prefix.
change_tag (ct_log_id
,ct_tag
);
547 CREATE UNIQUE INDEX &mw_prefix.change_tag_u03
ON &mw_prefix.
change_tag (ct_rev_id
,ct_tag
);
548 CREATE INDEX &mw_prefix.change_tag_i01
ON &mw_prefix.
change_tag (ct_tag
,ct_rc_id
,ct_rev_id
,ct_log_id
);
550 CREATE TABLE &mw_prefix.
tag_summary (
551 ts_rc_id
NUMBER NULL,
552 ts_log_id
NUMBER NULL,
553 ts_rev_id
NUMBER NULL,
554 ts_tags
BLOB NOT NULL
556 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u01
ON &mw_prefix.
tag_summary (ts_rc_id
);
557 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u02
ON &mw_prefix.
tag_summary (ts_log_id
);
558 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u03
ON &mw_prefix.
tag_summary (ts_rev_id
);
560 CREATE TABLE &mw_prefix.
valid_tag (
561 vt_tag
VARCHAR2(255) NOT NULL
563 ALTER TABLE &mw_prefix.valid_tag
ADD CONSTRAINT &mw_prefix.valid_tag_pk
PRIMARY KEY (vt_tag
);
565 -- This table is not used unless profiling is turned on
566 --CREATE TABLE &mw_prefix.profiling (
567 -- pf_count NUMBER DEFAULT 0 NOT NULL,
568 -- pf_time NUMERIC(18,10) DEFAULT 0 NOT NULL,
569 -- pf_name CLOB NOT NULL,
570 -- pf_server CLOB NULL
572 --CREATE UNIQUE INDEX &mw_prefix.profiling_u01 ON &mw_prefix.profiling (pf_name, pf_server);
574 CREATE INDEX si_title_idx
ON &mw_prefix.
searchindex(si_title
) INDEXTYPE
IS ctxsys.context
;
575 CREATE INDEX si_text_idx
ON &mw_prefix.
searchindex(si_text
) INDEXTYPE
IS ctxsys.context
;
577 CREATE TABLE &mw_prefix.
l10n_cache (
578 lc_lang
varchar2(32) NOT NULL,
579 lc_key
varchar2(255) NOT NULL,
580 lc_value
clob NOT NULL
582 CREATE INDEX &mw_prefix.l10n_cache_u01
ON &mw_prefix.
l10n_cache (lc_lang
, lc_key
);
584 CREATE TABLE &mw_prefix.
wiki_field_info_full (
585 table_name VARCHAR2(35) NOT NULL,
586 column_name VARCHAR2(35) NOT NULL,
587 data_default
VARCHAR2(4000),
588 data_length
NUMBER NOT NULL,
589 data_type
VARCHAR2(106),
590 not_null
CHAR(1) NOT NULL,
595 ALTER TABLE &mw_prefix.wiki_field_info_full
ADD CONSTRAINT &mw_prefix.wiki_field_info_full_pk
PRIMARY KEY (table_name, column_name);
598 CREATE PROCEDURE &mw_prefix.fill_wiki_info
IS
600 DELETE &mw_prefix.wiki_field_info_full
;
602 FOR x_rec
IN (SELECT '&mw_prefix.' || t.
table_name table_name, t.
column_name,
603 t.data_default
, t.data_length
, t.data_type
,
604 DECODE (t.
nullable, 'Y', '1', 'N', '0') not_null
,
606 FROM user_cons_columns ucc
,
608 WHERE ucc.
table_name = t.
table_name
609 AND ucc.
column_name = t.
column_name
610 AND uc.
constraint_name = ucc.
constraint_name
611 AND uc.constraint_type
= 'P'
612 AND ROWNUM
< 2) prim
,
614 FROM user_ind_columns uic
,
616 WHERE uic.
table_name = t.
table_name
617 AND uic.
column_name = t.
column_name
618 AND ui.index_name
= uic.index_name
619 AND ui.uniqueness
= 'UNIQUE'
620 AND ROWNUM
< 2) uniq
,
622 FROM user_ind_columns uic
,
624 WHERE uic.
table_name = t.
table_name
625 AND uic.
column_name = t.
column_name
626 AND ui.index_name
= uic.index_name
627 AND ui.uniqueness
= 'NONUNIQUE'
628 AND ROWNUM
< 2) nonuniq
629 FROM user_tab_columns t
, user_tables ut
630 WHERE ut.
table_name = t.
table_name)
632 INSERT INTO &mw_prefix.wiki_field_info_full
633 (table_name, column_name,
634 data_default
, data_length
,
635 data_type
, not_null
, prim
,
638 VALUES (x_rec.
table_name, x_rec.
column_name,
639 x_rec.data_default
, x_rec.data_length
,
640 x_rec.data_type
, x_rec.not_null
, x_rec.prim
,
641 x_rec.uniq
, x_rec.nonuniq
649 CREATE OR REPLACE PROCEDURE duplicate_table(p_oldname
IN VARCHAR2, p_newname
IN VARCHAR2, p_temporary
IN BOOLEAN)
651 v_oldname
VARCHAR2(32) := RTRIM(LTRIM(p_oldname
, '"'), '"');
652 v_newname
VARCHAR2(32) := RTRIM(LTRIM(p_newname
, '"'), '"');
653 v_prefix
VARCHAR2(32) := SUBSTR(v_newname
, 1, INSTR(v_newname
, v_oldname
, -1)-1);
654 e_table_not_exist
EXCEPTION;
655 PRAGMA
EXCEPTION_INIT(e_table_not_exist
, -00942);
658 EXECUTE IMMEDIATE 'DROP TABLE '||v_newname||
' CASCADE CONSTRAINTS';
659 EXCEPTION WHEN e_table_not_exist
THEN NULL; END;
661 IF (p_temporary
) THEN
662 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||v_newname||
663 ' AS SELECT * FROM '||v_oldname||
' WHERE ROWNUM = 0';
665 EXECUTE IMMEDIATE 'CREATE TABLE '||v_newname||
666 ' AS SELECT * FROM '||v_oldname||
' WHERE ROWNUM = 0';
669 FOR rc
IN (SELECT column_name, data_default
670 FROM user_tab_columns
671 WHERE table_name = v_oldname
672 AND data_default
IS NOT NULL) LOOP
673 EXECUTE IMMEDIATE 'ALTER TABLE '||v_newname||
' MODIFY '||rc.
column_name||
' DEFAULT '||
substr(rc.data_default
, 1, 2000);
676 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl ('CONSTRAINT', constraint_name), 32767, 1),
677 USER||
'"."', USER||
'"."'||v_prefix
),
678 '"'||
constraint_name||
'"', '"'||v_prefix||
constraint_name||
'"') DDLVC2
680 FROM user_constraints uc
681 WHERE table_name = v_oldname
682 AND constraint_type
= 'P') LOOP
683 EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'PCTFREE')-1);
686 FOR rc
IN (SELECT REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl ('REF_CONSTRAINT', constraint_name), 32767, 1),
687 USER||
'"."', USER||
'"."'||v_prefix
) DDLVC2
689 FROM user_constraints uc
690 WHERE table_name = v_oldname
691 AND constraint_type
= 'R') LOOP
692 EXECUTE IMMEDIATE rc.ddlvc2
;
695 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl ('INDEX', index_name
), 32767, 1),
696 USER||
'"."', USER||
'"."'||v_prefix
),
697 '"'||index_name||
'"', '"'||v_prefix||index_name||
'"') DDLVC2
700 WHERE table_name = v_oldname
701 AND index_type
!= 'LOB'
702 AND NOT EXISTS (SELECT NULL FROM user_constraints
703 WHERE table_name = ui.
table_name
704 AND constraint_name = ui.index_name
)) LOOP
705 EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'PCTFREE')-1);
708 FOR rc
IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl ('TRIGGER', trigger_name), 32767, 1)),
709 USER||
'"."', USER||
'"."'||v_prefix
),
710 ' ON '||v_oldname
, ' ON '||v_newname
) DDLVC2
713 WHERE table_name = v_oldname
) LOOP
714 EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'ALTER ')-1);
721 &mw_prefix.fill_wiki_info
;
726 CREATE OR REPLACE FUNCTION BITOR (x
IN NUMBER, y
IN NUMBER) RETURN NUMBER AS
728 RETURN (x
+ y
- BITAND(x
, y
));
733 CREATE OR REPLACE FUNCTION BITNOT (x
IN NUMBER) RETURN NUMBER AS
735 RETURN (4294967295 - x
);
740 CREATE OR REPLACE TYPE GET_OUTPUT_TYPE
IS TABLE OF VARCHAR2(255);
744 CREATE OR REPLACE FUNCTION GET_OUTPUT_LINES
RETURN GET_OUTPUT_TYPE PIPELINED
AS
745 v_line
VARCHAR2(255);
746 v_status
INTEGER := 0;
750 DBMS_OUTPUT.
GET_LINE(v_line
, v_status
);
751 IF (v_status
= 0) THEN RETURN; END IF;
762 CREATE OR REPLACE FUNCTION GET_SEQUENCE_VALUE(seq
IN VARCHAR2) RETURN NUMBER AS
765 EXECUTE IMMEDIATE 'SELECT '||seq||
'.NEXTVAL INTO :outVar FROM DUAL' INTO v_value
;