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.
langlinks (
189 ll_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page (page_id
) ON DELETE CASCADE,
190 ll_lang
VARCHAR2(20),
191 ll_title
VARCHAR2(255)
193 CREATE UNIQUE INDEX &mw_prefix.langlinks_u01
ON &mw_prefix.
langlinks (ll_from
, ll_lang
);
194 CREATE INDEX &mw_prefix.langlinks_i01
ON &mw_prefix.
langlinks (ll_lang
, ll_title
);
196 CREATE TABLE &mw_prefix.
site_stats (
197 ss_row_id
NUMBER NOT NULL ,
198 ss_total_views
NUMBER DEFAULT 0,
199 ss_total_edits
NUMBER DEFAULT 0,
200 ss_good_articles
NUMBER DEFAULT 0,
201 ss_total_pages
NUMBER DEFAULT -1,
202 ss_users
NUMBER DEFAULT -1,
203 ss_active_users
NUMBER DEFAULT -1,
204 ss_admins
NUMBER DEFAULT -1,
205 ss_images
NUMBER DEFAULT 0
207 CREATE UNIQUE INDEX &mw_prefix.site_stats_u01
ON &mw_prefix.
site_stats (ss_row_id
);
209 CREATE TABLE &mw_prefix.
hitcounter (
210 hc_id
NUMBER NOT NULL
213 CREATE SEQUENCE ipblocks_ipb_id_seq
;
214 CREATE TABLE &mw_prefix.
ipblocks (
215 ipb_id
NUMBER NOT NULL,
216 ipb_address
VARCHAR2(255) NULL,
217 ipb_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
218 ipb_by
NUMBER NOT NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE CASCADE,
219 ipb_by_text
VARCHAR2(255) NOT NULL,
220 ipb_reason
VARCHAR2(255) NOT NULL,
221 ipb_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
222 ipb_auto
CHAR(1) DEFAULT '0' NOT NULL,
223 ipb_anon_only
CHAR(1) DEFAULT '0' NOT NULL,
224 ipb_create_account
CHAR(1) DEFAULT '1' NOT NULL,
225 ipb_enable_autoblock
CHAR(1) DEFAULT '1' NOT NULL,
226 ipb_expiry
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
227 ipb_range_start
VARCHAR2(255),
228 ipb_range_end
VARCHAR2(255),
229 ipb_deleted
CHAR(1) DEFAULT '0' NOT NULL,
230 ipb_block_email
CHAR(1) DEFAULT '0' NOT NULL,
231 ipb_allow_usertalk
CHAR(1) DEFAULT '0' NOT NULL
233 ALTER TABLE &mw_prefix.ipblocks
ADD CONSTRAINT &mw_prefix.ipblocks_pk
PRIMARY KEY (ipb_id
);
234 CREATE UNIQUE INDEX &mw_prefix.ipblocks_u01
ON &mw_prefix.
ipblocks (ipb_address
, ipb_user
, ipb_auto
, ipb_anon_only
);
235 CREATE INDEX &mw_prefix.ipblocks_i01
ON &mw_prefix.
ipblocks (ipb_user
);
236 CREATE INDEX &mw_prefix.ipblocks_i02
ON &mw_prefix.
ipblocks (ipb_range_start
, ipb_range_end
);
237 CREATE INDEX &mw_prefix.ipblocks_i03
ON &mw_prefix.
ipblocks (ipb_timestamp
);
238 CREATE INDEX &mw_prefix.ipblocks_i04
ON &mw_prefix.
ipblocks (ipb_expiry
);
240 CREATE TABLE &mw_prefix.
image (
241 img_name
VARCHAR2(255) NOT NULL,
242 img_size
NUMBER NOT NULL,
243 img_width
NUMBER NOT NULL,
244 img_height
NUMBER NOT NULL,
247 img_media_type
VARCHAR2(32),
248 img_major_mime
VARCHAR2(32) DEFAULT 'unknown',
249 img_minor_mime
VARCHAR2(32) DEFAULT 'unknown',
250 img_description
VARCHAR2(255),
251 img_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
252 img_user_text
VARCHAR2(255) NOT NULL,
253 img_timestamp
TIMESTAMP(6) WITH TIME ZONE,
254 img_sha1
VARCHAR2(32)
256 ALTER TABLE &mw_prefix.image
ADD CONSTRAINT &mw_prefix.image_pk
PRIMARY KEY (img_name
);
257 CREATE INDEX &mw_prefix.image_i01
ON &mw_prefix.
image (img_user_text
,img_timestamp
);
258 CREATE INDEX &mw_prefix.image_i02
ON &mw_prefix.
image (img_size
);
259 CREATE INDEX &mw_prefix.image_i03
ON &mw_prefix.
image (img_timestamp
);
260 CREATE INDEX &mw_prefix.image_i04
ON &mw_prefix.
image (img_sha1
);
263 CREATE TABLE &mw_prefix.
oldimage (
264 oi_name
VARCHAR2(255) NOT NULL REFERENCES &mw_prefix.
image(img_name
),
265 oi_archive_name
VARCHAR2(255),
266 oi_size
NUMBER NOT NULL,
267 oi_width
NUMBER NOT NULL,
268 oi_height
NUMBER NOT NULL,
269 oi_bits
NUMBER NOT NULL,
270 oi_description
VARCHAR2(255),
271 oi_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
272 oi_user_text
VARCHAR2(255) NOT NULL,
273 oi_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
275 oi_media_type
VARCHAR2(32) DEFAULT NULL,
276 oi_major_mime
VARCHAR2(32) DEFAULT 'unknown',
277 oi_minor_mime
VARCHAR2(32) DEFAULT 'unknown',
278 oi_deleted
NUMBER DEFAULT 0 NOT NULL,
281 CREATE INDEX &mw_prefix.oldimage_i01
ON &mw_prefix.
oldimage (oi_user_text
,oi_timestamp
);
282 CREATE INDEX &mw_prefix.oldimage_i02
ON &mw_prefix.
oldimage (oi_name
,oi_timestamp
);
283 CREATE INDEX &mw_prefix.oldimage_i03
ON &mw_prefix.
oldimage (oi_name
,oi_archive_name
);
284 CREATE INDEX &mw_prefix.oldimage_i04
ON &mw_prefix.
oldimage (oi_sha1
);
287 CREATE SEQUENCE filearchive_fa_id_seq
;
288 CREATE TABLE &mw_prefix.
filearchive (
289 fa_id
NUMBER NOT NULL,
290 fa_name
VARCHAR2(255) NOT NULL,
291 fa_archive_name
VARCHAR2(255),
292 fa_storage_group
VARCHAR2(16),
293 fa_storage_key
VARCHAR2(64),
294 fa_deleted_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
295 fa_deleted_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
296 fa_deleted_reason
CLOB,
297 fa_size
NUMBER NOT NULL,
298 fa_width
NUMBER NOT NULL,
299 fa_height
NUMBER NOT NULL,
302 fa_media_type
VARCHAR2(32) DEFAULT NULL,
303 fa_major_mime
VARCHAR2(32) DEFAULT 'unknown',
304 fa_minor_mime
VARCHAR2(32) DEFAULT 'unknown',
305 fa_description
VARCHAR2(255) NOT NULL,
306 fa_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
307 fa_user_text
VARCHAR2(255) NOT NULL,
308 fa_timestamp
TIMESTAMP(6) WITH TIME ZONE,
309 fa_deleted
NUMBER DEFAULT '0' NOT NULL
311 ALTER TABLE &mw_prefix.filearchive
ADD CONSTRAINT &mw_prefix.filearchive_pk
PRIMARY KEY (fa_id
);
312 CREATE INDEX &mw_prefix.filearchive_i01
ON &mw_prefix.
filearchive (fa_name
, fa_timestamp
);
313 CREATE INDEX &mw_prefix.filearchive_i02
ON &mw_prefix.
filearchive (fa_storage_group
, fa_storage_key
);
314 CREATE INDEX &mw_prefix.filearchive_i03
ON &mw_prefix.
filearchive (fa_deleted_timestamp
);
315 CREATE INDEX &mw_prefix.filearchive_i04
ON &mw_prefix.
filearchive (fa_user_text
,fa_timestamp
);
317 CREATE SEQUENCE recentchanges_rc_id_seq
;
318 CREATE TABLE &mw_prefix.
recentchanges (
319 rc_id
NUMBER NOT NULL,
320 rc_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
321 rc_cur_time
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
322 rc_user
NUMBER NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
323 rc_user_text
VARCHAR2(255) NOT NULL,
324 rc_namespace
NUMBER NOT NULL,
325 rc_title
VARCHAR2(255) NOT NULL,
326 rc_comment
VARCHAR2(255),
327 rc_minor
CHAR(1) DEFAULT '0' NOT NULL,
328 rc_bot
CHAR(1) DEFAULT '0' NOT NULL,
329 rc_new
CHAR(1) DEFAULT '0' NOT NULL,
330 rc_cur_id
NUMBER NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE SET NULL,
331 rc_this_oldid
NUMBER NOT NULL,
332 rc_last_oldid
NUMBER NOT NULL,
333 rc_type
CHAR(1) DEFAULT '0' NOT NULL,
334 rc_moved_to_ns
NUMBER,
335 rc_moved_to_title
VARCHAR2(255),
336 rc_patrolled
CHAR(1) DEFAULT '0' NOT NULL,
340 rc_deleted
NUMBER DEFAULT '0' NOT NULL,
341 rc_logid
NUMBER DEFAULT '0' NOT NULL,
342 rc_log_type
VARCHAR2(255),
343 rc_log_action
VARCHAR2(255),
346 ALTER TABLE &mw_prefix.recentchanges
ADD CONSTRAINT &mw_prefix.recentchanges_pk
PRIMARY KEY (rc_id
);
347 CREATE INDEX &mw_prefix.recentchanges_i01
ON &mw_prefix.
recentchanges (rc_timestamp
);
348 CREATE INDEX &mw_prefix.recentchanges_i02
ON &mw_prefix.
recentchanges (rc_namespace
, rc_title
);
349 CREATE INDEX &mw_prefix.recentchanges_i03
ON &mw_prefix.
recentchanges (rc_cur_id
);
350 CREATE INDEX &mw_prefix.recentchanges_i04
ON &mw_prefix.
recentchanges (rc_new
,rc_namespace
,rc_timestamp
);
351 CREATE INDEX &mw_prefix.recentchanges_i05
ON &mw_prefix.
recentchanges (rc_ip
);
352 CREATE INDEX &mw_prefix.recentchanges_i06
ON &mw_prefix.
recentchanges (rc_namespace
, rc_user_text
);
353 CREATE INDEX &mw_prefix.recentchanges_i07
ON &mw_prefix.
recentchanges (rc_user_text
, rc_timestamp
);
355 CREATE TABLE &mw_prefix.
watchlist (
356 wl_user
NUMBER NOT NULL REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE CASCADE,
357 wl_namespace
NUMBER DEFAULT 0 NOT NULL,
358 wl_title
VARCHAR2(255) NOT NULL,
359 wl_notificationtimestamp
TIMESTAMP(6) WITH TIME ZONE
361 CREATE UNIQUE INDEX &mw_prefix.watchlist_u01
ON &mw_prefix.
watchlist (wl_user
, wl_namespace
, wl_title
);
362 CREATE INDEX &mw_prefix.watchlist_i01
ON &mw_prefix.
watchlist (wl_namespace
, wl_title
);
365 CREATE TABLE &mw_prefix.
math (
366 math_inputhash
VARCHAR2(32) NOT NULL,
367 math_outputhash
VARCHAR2(32) NOT NULL,
368 math_html_conservativeness
NUMBER NOT NULL,
372 CREATE UNIQUE INDEX &mw_prefix.math_u01
ON &mw_prefix.
math (math_inputhash
);
374 CREATE TABLE &mw_prefix.
searchindex (
375 si_page
NUMBER NOT NULL,
376 si_title
VARCHAR2(255) DEFAULT '' NOT NULL,
377 si_text
CLOB NOT NULL
379 CREATE UNIQUE INDEX &mw_prefix.searchindex_u01
ON &mw_prefix.
searchindex (si_page
);
381 CREATE TABLE &mw_prefix.
interwiki (
382 iw_prefix
VARCHAR2(32) NOT NULL,
383 iw_url
VARCHAR2(127) NOT NULL,
384 iw_local
CHAR(1) NOT NULL,
385 iw_trans
CHAR(1) DEFAULT '0' NOT NULL
387 CREATE UNIQUE INDEX &mw_prefix.interwiki_u01
ON &mw_prefix.
interwiki (iw_prefix
);
389 CREATE TABLE &mw_prefix.
querycache (
390 qc_type
VARCHAR2(32) NOT NULL,
391 qc_value
NUMBER NOT NULL,
392 qc_namespace
NUMBER NOT NULL,
393 qc_title
VARCHAR2(255) NOT NULL
395 CREATE INDEX &mw_prefix.querycache_u01
ON &mw_prefix.
querycache (qc_type
,qc_value
);
397 CREATE TABLE &mw_prefix.
objectcache (
398 keyname
VARCHAR2(255) ,
400 exptime
TIMESTAMP(6) WITH TIME ZONE NOT NULL
402 CREATE INDEX &mw_prefix.objectcache_i01
ON &mw_prefix.
objectcache (exptime
);
404 CREATE TABLE &mw_prefix.
transcache (
405 tc_url
VARCHAR2(255) NOT NULL,
406 tc_contents
CLOB NOT NULL,
407 tc_time
TIMESTAMP(6) WITH TIME ZONE NOT NULL
409 CREATE UNIQUE INDEX &mw_prefix.transcache_u01
ON &mw_prefix.
transcache (tc_url
);
412 CREATE SEQUENCE logging_log_id_seq
;
413 CREATE TABLE &mw_prefix.
logging (
414 log_id
NUMBER NOT NULL,
415 log_type
VARCHAR2(10) NOT NULL,
416 log_action
VARCHAR2(10) NOT NULL,
417 log_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
418 log_user
NUMBER REFERENCES &mw_prefix.
mwuser(user_id
) ON DELETE SET NULL,
419 log_user_text
VARCHAR2(255),
420 log_namespace
NUMBER NOT NULL,
421 log_title
VARCHAR2(255) NOT NULL,
423 log_comment
VARCHAR2(255),
425 log_deleted
NUMBER DEFAULT '0' NOT NULL
427 ALTER TABLE &mw_prefix.logging
ADD CONSTRAINT &mw_prefix.logging_pk
PRIMARY KEY (log_id
);
428 CREATE INDEX &mw_prefix.logging_i01
ON &mw_prefix.
logging (log_type
, log_timestamp
);
429 CREATE INDEX &mw_prefix.logging_i02
ON &mw_prefix.
logging (log_user
, log_timestamp
);
430 CREATE INDEX &mw_prefix.logging_i03
ON &mw_prefix.
logging (log_namespace
, log_title
, log_timestamp
);
431 CREATE INDEX &mw_prefix.logging_i04
ON &mw_prefix.
logging (log_timestamp
);
433 CREATE TABLE &mw_prefix.
log_search (
434 ls_field
VARCHAR2(32) NOT NULL,
435 ls_value
VARCHAR2(255) NOT NULL,
436 ls_log_id
NuMBER DEFAULT 0 NOT NULL
438 ALTER TABLE log_search
ADD CONSTRAINT log_search_pk
PRIMARY KEY (ls_field
,ls_value
,ls_log_id
);
439 CREATE INDEX &mw_prefix.log_search_i01
ON &mw_prefix.
log_search (ls_log_id
);
441 CREATE SEQUENCE trackbacks_tb_id_seq
;
442 CREATE TABLE &mw_prefix.
trackbacks (
443 tb_id
NUMBER NOT NULL,
444 tb_page
NUMBER REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
445 tb_title
VARCHAR2(255) NOT NULL,
446 tb_url
VARCHAR2(255) NOT NULL,
448 tb_name
VARCHAR2(255)
450 ALTER TABLE &mw_prefix.trackbacks
ADD CONSTRAINT &mw_prefix.trackbacks_pk
PRIMARY KEY (tb_id
);
451 CREATE INDEX &mw_prefix.trackbacks_i01
ON &mw_prefix.
trackbacks (tb_page
);
453 CREATE SEQUENCE job_job_id_seq
;
454 CREATE TABLE &mw_prefix.
job (
455 job_id
NUMBER NOT NULL,
456 job_cmd
VARCHAR2(60) NOT NULL,
457 job_namespace
NUMBER NOT NULL,
458 job_title
VARCHAR2(255) NOT NULL,
459 job_params
CLOB NOT NULL
461 ALTER TABLE &mw_prefix.job
ADD CONSTRAINT &mw_prefix.job_pk
PRIMARY KEY (job_id
);
462 CREATE INDEX &mw_prefix.job_i01
ON &mw_prefix.
job (job_cmd
, job_namespace
, job_title
);
464 CREATE TABLE &mw_prefix.
querycache_info (
465 qci_type
VARCHAR2(32) NOT NULL,
466 qci_timestamp
TIMESTAMP(6) WITH TIME ZONE NULL
468 CREATE UNIQUE INDEX &mw_prefix.querycache_info_u01
ON &mw_prefix.
querycache_info (qci_type
);
470 CREATE TABLE &mw_prefix.
redirect (
471 rd_from
NUMBER NOT NULL REFERENCES &mw_prefix.
page(page_id
) ON DELETE CASCADE,
472 rd_namespace
NUMBER NOT NULL,
473 rd_title
VARCHAR2(255) NOT NULL,
474 rd_interwiki
VARCHAR2(32),
475 rd_fragment
VARCHAR2(255)
477 CREATE INDEX &mw_prefix.redirect_i01
ON &mw_prefix.
redirect (rd_namespace
,rd_title
,rd_from
);
479 CREATE TABLE &mw_prefix.
querycachetwo (
480 qcc_type
VARCHAR2(32) NOT NULL,
481 qcc_value
NUMBER DEFAULT 0 NOT NULL,
482 qcc_namespace
NUMBER DEFAULT 0 NOT NULL,
483 qcc_title
VARCHAR2(255) DEFAULT '' NOT NULL,
484 qcc_namespacetwo
NUMBER DEFAULT 0 NOT NULL,
485 qcc_titletwo
VARCHAR2(255) DEFAULT '' NOT NULL
487 CREATE INDEX &mw_prefix.querycachetwo_i01
ON &mw_prefix.
querycachetwo (qcc_type
,qcc_value
);
488 CREATE INDEX &mw_prefix.querycachetwo_i02
ON &mw_prefix.
querycachetwo (qcc_type
,qcc_namespace
,qcc_title
);
489 CREATE INDEX &mw_prefix.querycachetwo_i03
ON &mw_prefix.
querycachetwo (qcc_type
,qcc_namespacetwo
,qcc_titletwo
);
491 CREATE SEQUENCE page_restrictions_pr_id_seq
;
492 CREATE TABLE &mw_prefix.
page_restrictions (
493 pr_id
NUMBER NOT NULL,
494 pr_page
NUMBER NULL REFERENCES &mw_prefix.
page (page_id
) ON DELETE CASCADE,
495 pr_type
VARCHAR2(255) NOT NULL,
496 pr_level
VARCHAR2(255) NOT NULL,
497 pr_cascade
NUMBER NOT NULL,
499 pr_expiry
TIMESTAMP(6) WITH TIME ZONE NULL
501 ALTER TABLE &mw_prefix.page_restrictions
ADD CONSTRAINT &mw_prefix.page_restrictions_pk
PRIMARY KEY (pr_page
,pr_type
);
502 CREATE INDEX &mw_prefix.page_restrictions_i01
ON &mw_prefix.
page_restrictions (pr_type
,pr_level
);
503 CREATE INDEX &mw_prefix.page_restrictions_i02
ON &mw_prefix.
page_restrictions (pr_level
);
504 CREATE INDEX &mw_prefix.page_restrictions_i03
ON &mw_prefix.
page_restrictions (pr_cascade
);
506 CREATE TABLE &mw_prefix.
protected_titles (
507 pt_namespace
NUMBER NOT NULL,
508 pt_title
VARCHAR2(255) NOT NULL,
509 pt_user
NUMBER NOT NULL,
510 pt_reason
VARCHAR2(255),
511 pt_timestamp
TIMESTAMP(6) WITH TIME ZONE NOT NULL,
512 pt_expiry
VARCHAR2(14) NOT NULL,
513 pt_create_perm
VARCHAR2(60) NOT NULL
515 CREATE UNIQUE INDEX &mw_prefix.protected_titles_u01
ON &mw_prefix.
protected_titles (pt_namespace
,pt_title
);
516 CREATE INDEX &mw_prefix.protected_titles_i01
ON &mw_prefix.
protected_titles (pt_timestamp
);
518 CREATE TABLE &mw_prefix.
page_props (
519 pp_page
NUMBER NOT NULL,
520 pp_propname
VARCHAR2(60) NOT NULL,
521 pp_value
BLOB NOT NULL
523 CREATE UNIQUE INDEX &mw_prefix.page_props_u01
ON &mw_prefix.
page_props (pp_page
,pp_propname
);
526 CREATE TABLE &mw_prefix.
updatelog (
527 ul_key
VARCHAR2(255) NOT NULL
529 ALTER TABLE &mw_prefix.updatelog
ADD CONSTRAINT &mw_prefix.updatelog_pk
PRIMARY KEY (ul_key
);
531 CREATE TABLE &mw_prefix.
change_tag (
532 ct_rc_id
NUMBER NULL,
533 ct_log_id
NUMBER NULL,
534 ct_rev_id
NUMBER NULL,
535 ct_tag
VARCHAR2(255) NOT NULL,
538 CREATE UNIQUE INDEX &mw_prefix.change_tag_u01
ON &mw_prefix.
change_tag (ct_rc_id
,ct_tag
);
539 CREATE UNIQUE INDEX &mw_prefix.change_tag_u02
ON &mw_prefix.
change_tag (ct_log_id
,ct_tag
);
540 CREATE UNIQUE INDEX &mw_prefix.change_tag_u03
ON &mw_prefix.
change_tag (ct_rev_id
,ct_tag
);
541 CREATE INDEX &mw_prefix.change_tag_i01
ON &mw_prefix.
change_tag (ct_tag
,ct_rc_id
,ct_rev_id
,ct_log_id
);
543 CREATE TABLE &mw_prefix.
tag_summary (
544 ts_rc_id
NUMBER NULL,
545 ts_log_id
NUMBER NULL,
546 ts_rev_id
NUMBER NULL,
547 ts_tags
BLOB NOT NULL
549 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u01
ON &mw_prefix.
tag_summary (ts_rc_id
);
550 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u02
ON &mw_prefix.
tag_summary (ts_log_id
);
551 CREATE UNIQUE INDEX &mw_prefix.tag_summary_u03
ON &mw_prefix.
tag_summary (ts_rev_id
);
553 CREATE TABLE &mw_prefix.
valid_tag (
554 vt_tag
VARCHAR2(255) NOT NULL
556 ALTER TABLE &mw_prefix.valid_tag
ADD CONSTRAINT &mw_prefix.valid_tag_pk
PRIMARY KEY (vt_tag
);
558 -- This table is not used unless profiling is turned on
559 --CREATE TABLE &mw_prefix.profiling (
560 -- pf_count NUMBER DEFAULT 0 NOT NULL,
561 -- pf_time NUMERIC(18,10) DEFAULT 0 NOT NULL,
562 -- pf_name CLOB NOT NULL,
563 -- pf_server CLOB NULL
565 --CREATE UNIQUE INDEX &mw_prefix.profiling_u01 ON &mw_prefix.profiling (pf_name, pf_server);
567 CREATE INDEX si_title_idx
ON &mw_prefix.
searchindex(si_title
) INDEXTYPE
IS ctxsys.context
;
568 CREATE INDEX si_text_idx
ON &mw_prefix.
searchindex(si_text
) INDEXTYPE
IS ctxsys.context
;
570 CREATE TABLE &mw_prefix.
l10n_cache (
571 lc_lang
varchar2(32) NOT NULL,
572 lc_key
varchar2(255) NOT NULL,
573 lc_value
clob NOT NULL
575 CREATE INDEX &mw_prefix.l10n_cache_u01
ON &mw_prefix.
l10n_cache (lc_lang
, lc_key
);
577 CREATE TABLE &mw_prefix.
wiki_field_info_full (
578 table_name VARCHAR2(35) NOT NULL,
579 column_name VARCHAR2(35) NOT NULL,
580 data_default
VARCHAR2(4000),
581 data_length
NUMBER NOT NULL,
582 data_type
VARCHAR2(106),
583 not_null
CHAR(1) NOT NULL,
588 ALTER TABLE &mw_prefix.wiki_field_info_full
ADD CONSTRAINT &mw_prefix.wiki_field_info_full_pk
PRIMARY KEY (table_name, column_name);
591 CREATE PROCEDURE &mw_prefix.fill_wiki_info
IS
593 DELETE &mw_prefix.wiki_field_info_full
;
595 FOR x_rec
IN (SELECT '&mw_prefix.' || t.
table_name table_name, t.
column_name,
596 t.data_default
, t.data_length
, t.data_type
,
597 DECODE (t.
nullable, 'Y', '1', 'N', '0') not_null
,
599 FROM user_cons_columns ucc
,
601 WHERE ucc.
table_name = t.
table_name
602 AND ucc.
column_name = t.
column_name
603 AND uc.
constraint_name = ucc.
constraint_name
604 AND uc.constraint_type
= 'P'
605 AND ROWNUM
< 2) prim
,
607 FROM user_ind_columns uic
,
609 WHERE uic.
table_name = t.
table_name
610 AND uic.
column_name = t.
column_name
611 AND ui.index_name
= uic.index_name
612 AND ui.uniqueness
= 'UNIQUE'
613 AND ROWNUM
< 2) uniq
,
615 FROM user_ind_columns uic
,
617 WHERE uic.
table_name = t.
table_name
618 AND uic.
column_name = t.
column_name
619 AND ui.index_name
= uic.index_name
620 AND ui.uniqueness
= 'NONUNIQUE'
621 AND ROWNUM
< 2) nonuniq
622 FROM user_tab_columns t
, user_tables ut
623 WHERE ut.
table_name = t.
table_name)
625 INSERT INTO &mw_prefix.wiki_field_info_full
626 (table_name, column_name,
627 data_default
, data_length
,
628 data_type
, not_null
, prim
,
631 VALUES (x_rec.
table_name, x_rec.
column_name,
632 x_rec.data_default
, x_rec.data_length
,
633 x_rec.data_type
, x_rec.not_null
, x_rec.prim
,
634 x_rec.uniq
, x_rec.nonuniq
642 CREATE OR REPLACE PROCEDURE duplicate_table(p_oldname
IN VARCHAR2, p_newname
IN VARCHAR2, p_temporary
IN BOOLEAN)
644 v_oldname
VARCHAR2(32) := RTRIM(LTRIM(p_oldname
, '"'), '"');
645 v_newname
VARCHAR2(32) := RTRIM(LTRIM(p_newname
, '"'), '"');
646 v_prefix
VARCHAR2(32) := SUBSTR(v_newname
, 1, INSTR(v_newname
, v_oldname
, -1)-1);
647 e_table_not_exist
EXCEPTION;
648 PRAGMA
EXCEPTION_INIT(e_table_not_exist
, -00942);
651 EXECUTE IMMEDIATE 'DROP TABLE '||v_newname||
' CASCADE CONSTRAINTS';
652 EXCEPTION WHEN e_table_not_exist
THEN NULL; END;
654 IF (p_temporary
) THEN
655 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||v_newname||
656 ' AS SELECT * FROM '||v_oldname||
' WHERE ROWNUM = 0';
658 EXECUTE IMMEDIATE 'CREATE TABLE '||v_newname||
659 ' AS SELECT * FROM '||v_oldname||
' WHERE ROWNUM = 0';
662 FOR rc
IN (SELECT column_name, data_default
663 FROM user_tab_columns
664 WHERE table_name = v_oldname
665 AND data_default
IS NOT NULL) LOOP
666 EXECUTE IMMEDIATE 'ALTER TABLE '||v_newname||
' MODIFY '||rc.
column_name||
' DEFAULT '||
substr(rc.data_default
, 1, 2000);
669 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl ('CONSTRAINT', constraint_name), 32767, 1),
670 USER||
'"."', USER||
'"."'||v_prefix
),
671 '"'||
constraint_name||
'"', '"'||v_prefix||
constraint_name||
'"') DDLVC2
673 FROM user_constraints uc
674 WHERE table_name = v_oldname
675 AND constraint_type
= 'P') LOOP
676 EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'PCTFREE')-1);
679 FOR rc
IN (SELECT REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl ('REF_CONSTRAINT', constraint_name), 32767, 1),
680 USER||
'"."', USER||
'"."'||v_prefix
) DDLVC2
682 FROM user_constraints uc
683 WHERE table_name = v_oldname
684 AND constraint_type
= 'R') LOOP
685 EXECUTE IMMEDIATE rc.ddlvc2
;
688 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl ('INDEX', index_name
), 32767, 1),
689 USER||
'"."', USER||
'"."'||v_prefix
),
690 '"'||index_name||
'"', '"'||v_prefix||index_name||
'"') DDLVC2
693 WHERE table_name = v_oldname
694 AND index_type
!= 'LOB'
695 AND NOT EXISTS (SELECT NULL FROM user_constraints
696 WHERE table_name = ui.
table_name
697 AND constraint_name = ui.index_name
)) LOOP
698 EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'PCTFREE')-1);
701 FOR rc
IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl ('TRIGGER', trigger_name), 32767, 1)),
702 USER||
'"."', USER||
'"."'||v_prefix
),
703 ' ON '||v_oldname
, ' ON '||v_newname
) DDLVC2
706 WHERE table_name = v_oldname
) LOOP
707 EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'ALTER ')-1);
714 &mw_prefix.fill_wiki_info
;
719 CREATE OR REPLACE FUNCTION BITOR (x
IN NUMBER, y
IN NUMBER) RETURN NUMBER AS
721 RETURN (x
+ y
- BITAND(x
, y
));
726 CREATE OR REPLACE FUNCTION BITNOT (x
IN NUMBER) RETURN NUMBER AS
728 RETURN (4294967295 - x
);
733 CREATE OR REPLACE TYPE GET_OUTPUT_TYPE
IS TABLE OF VARCHAR2(255);
737 CREATE OR REPLACE FUNCTION GET_OUTPUT_LINES
RETURN GET_OUTPUT_TYPE PIPELINED
AS
738 v_line
VARCHAR2(255);
739 v_status
INTEGER := 0;
743 DBMS_OUTPUT.
GET_LINE(v_line
, v_status
);
744 IF (v_status
= 0) THEN RETURN; END IF;
755 CREATE OR REPLACE FUNCTION GET_SEQUENCE_VALUE(seq
IN VARCHAR2) RETURN NUMBER AS
758 EXECUTE IMMEDIATE 'SELECT '||seq||
'.NEXTVAL INTO :outVar FROM DUAL' INTO v_value
;