1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
|
-- SQL to create the initial tables for the MediaWiki database.
-- This is read and executed by the install script; you should
-- not have to run it by itself unless doing a manual install.
CREATE SEQUENCE user_user_id_seq;
CREATE TABLE "user" (
user_id NUMBER(5) NOT NULL PRIMARY KEY,
user_name VARCHAR2(255) DEFAULT '' NOT NULL,
user_real_name VARCHAR2(255) DEFAULT '',
user_password VARCHAR2(128) DEFAULT '',
user_newpassword VARCHAR2(128) default '',
user_email VARCHAR2(255) default '',
user_options CLOB default '',
user_touched TIMESTAMP WITH TIME ZONE,
user_token CHAR(32) default '',
user_email_authenticated TIMESTAMP WITH TIME ZONE DEFAULT NULL,
user_email_token CHAR(32),
user_email_token_expires TIMESTAMP WITH TIME ZONE DEFAULT NULL
);
CREATE UNIQUE INDEX user_name_idx ON "user" (user_name);
CREATE INDEX user_email_token_idx ON "user" (user_email_token);
CREATE TABLE user_groups (
ug_user NUMBER(5) DEFAULT '0' NOT NULL
REFERENCES "user" (user_id)
ON DELETE CASCADE,
ug_group VARCHAR2(16) NOT NULL,
CONSTRAINT user_groups_pk PRIMARY KEY (ug_user, ug_group)
);
CREATE INDEX user_groups_group_idx ON user_groups(ug_group);
CREATE TABLE user_newtalk (
user_id NUMBER(5) DEFAULT 0 NOT NULL,
user_ip VARCHAR2(40) DEFAULT '' NOT NULL
);
CREATE INDEX user_newtalk_id_idx ON user_newtalk(user_id);
CREATE INDEX user_newtalk_ip_idx ON user_newtalk(user_ip);
CREATE SEQUENCE page_page_id_seq;
CREATE TABLE page (
page_id NUMBER(8) NOT NULL PRIMARY KEY,
page_namespace NUMBER(5) NOT NULL,
page_title VARCHAR(255) NOT NULL,
page_restrictions CLOB DEFAULT '',
page_counter NUMBER(20) DEFAULT 0 NOT NULL,
page_is_redirect NUMBER(1) DEFAULT 0 NOT NULL,
page_is_new NUMBER(1) DEFAULT 0 NOT NULL,
page_random NUMBER(25, 24) NOT NULL,
page_touched TIMESTAMP WITH TIME ZONE,
page_latest NUMBER(8) NOT NULL,
page_len NUMBER(8) DEFAULT 0
);
CREATE UNIQUE INDEX page_id_namespace_title_idx ON page(page_namespace, page_title);
CREATE INDEX page_random_idx ON page(page_random);
CREATE INDEX page_len_idx ON page(page_len);
CREATE SEQUENCE rev_rev_id_val;
CREATE TABLE revision (
rev_id NUMBER(8) NOT NULL,
rev_page NUMBER(8) NOT NULL
REFERENCES page (page_id)
ON DELETE CASCADE,
rev_text_id NUMBER(8) NOT NULL,
rev_comment CLOB,
rev_user NUMBER(8) DEFAULT 0 NOT NULL,
rev_user_text VARCHAR2(255) DEFAULT '' NOT NULL,
rev_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
rev_minor_edit NUMBER(1) DEFAULT 0 NOT NULL,
rev_deleted NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT revision_pk PRIMARY KEY (rev_page, rev_id)
);
CREATE UNIQUE INDEX rev_id_idx ON revision(rev_id);
CREATE INDEX rev_timestamp_idx ON revision(rev_timestamp);
CREATE INDEX rev_page_timestamp_idx ON revision(rev_page, rev_timestamp);
CREATE INDEX rev_user_timestamp_idx ON revision(rev_user, rev_timestamp);
CREATE INDEX rev_usertext_timestamp_idx ON revision(rev_user_text, rev_timestamp);
CREATE SEQUENCE text_old_id_val;
CREATE TABLE text (
old_id NUMBER(8) NOT NULL,
old_text CLOB,
old_flags CLOB,
CONSTRAINT text_pk PRIMARY KEY (old_id)
);
CREATE TABLE archive (
ar_namespace NUMBER(5) NOT NULL,
ar_title VARCHAR2(255) NOT NULL,
ar_text CLOB,
ar_comment CLOB,
ar_user NUMBER(8),
ar_user_text VARCHAR2(255) NOT NULL,
ar_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
ar_minor_edit NUMBER(1) DEFAULT 0 NOT NULL,
ar_flags CLOB,
ar_rev_id NUMBER(8),
ar_text_id NUMBER(8)
);
CREATE INDEX archive_name_title_timestamp ON archive(ar_namespace,ar_title,ar_timestamp);
CREATE TABLE pagelinks (
pl_from NUMBER(8) NOT NULL
REFERENCES page(page_id)
ON DELETE CASCADE,
pl_namespace NUMBER(4) DEFAULT 0 NOT NULL,
pl_title VARCHAR2(255) NOT NULL
);
CREATE UNIQUE INDEX pl_from ON pagelinks(pl_from, pl_namespace, pl_title);
CREATE INDEX pl_namespace ON pagelinks(pl_namespace, pl_title, pl_from);
CREATE TABLE imagelinks (
il_from NUMBER(8) NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
il_to VARCHAR2(255) NOT NULL
);
CREATE UNIQUE INDEX il_from ON imagelinks(il_from, il_to);
CREATE INDEX il_to ON imagelinks(il_to, il_from);
CREATE TABLE categorylinks (
cl_from NUMBER(8) NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
cl_to VARCHAR2(255) NOT NULL,
cl_sortkey VARCHAR2(86) default '',
cl_timestamp TIMESTAMP WITH TIME ZONE NOT NULL
);
CREATE UNIQUE INDEX cl_from ON categorylinks(cl_from, cl_to);
CREATE INDEX cl_sortkey ON categorylinks(cl_to, cl_sortkey);
CREATE INDEX cl_timestamp ON categorylinks(cl_to, cl_timestamp);
--
-- Contains a single row with some aggregate info
-- on the state of the site.
--
CREATE TABLE site_stats (
ss_row_id NUMBER(8) NOT NULL,
ss_total_views NUMBER(20) default 0,
ss_total_edits NUMBER(20) default 0,
ss_good_articles NUMBER(20) default 0,
ss_total_pages NUMBER(20) default -1,
ss_users NUMBER(20) default -1,
ss_admins NUMBER(10) default -1
);
CREATE UNIQUE INDEX ss_row_id ON site_stats(ss_row_id);
--
-- Stores an ID for every time any article is visited;
-- depending on $wgHitcounterUpdateFreq, it is
-- periodically cleared and the page_counter column
-- in the page table updated for the all articles
-- that have been visited.)
--
CREATE TABLE hitcounter (
hc_id NUMBER NOT NULL
);
--
-- The internet is full of jerks, alas. Sometimes it's handy
-- to block a vandal or troll account.
--
CREATE SEQUENCE ipblocks_ipb_id_val;
CREATE TABLE ipblocks (
ipb_id NUMBER(8) NOT NULL,
ipb_address VARCHAR2(40),
ipb_user NUMBER(8),
ipb_by NUMBER(8) NOT NULL
REFERENCES "user" (user_id)
ON DELETE CASCADE,
ipb_reason CLOB,
ipb_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
ipb_auto NUMBER(1) DEFAULT 0 NOT NULL,
ipb_expiry TIMESTAMP WITH TIME ZONE,
CONSTRAINT ipblocks_pk PRIMARY KEY (ipb_id)
);
CREATE INDEX ipb_address ON ipblocks(ipb_address);
CREATE INDEX ipb_user ON ipblocks(ipb_user);
CREATE TABLE image (
img_name VARCHAR2(255) NOT NULL,
img_size NUMBER(8) NOT NULL,
img_width NUMBER(5) NOT NULL,
img_height NUMBER(5) NOT NULL,
img_metadata CLOB,
img_bits NUMBER(3),
img_media_type VARCHAR2(10),
img_major_mime VARCHAR2(12) DEFAULT 'unknown',
img_minor_mime VARCHAR2(32) DEFAULT 'unknown',
img_description CLOB NOT NULL,
img_user NUMBER(8) NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE,
img_user_text VARCHAR2(255) NOT NULL,
img_timestamp TIMESTAMP WITH TIME ZONE,
CONSTRAINT image_pk PRIMARY KEY (img_name)
);
CREATE INDEX img_size_idx ON image(img_size);
CREATE INDEX img_timestamp_idx ON image(img_timestamp);
CREATE TABLE oldimage (
oi_name VARCHAR2(255) NOT NULL,
oi_archive_name VARCHAR2(255) NOT NULL,
oi_size NUMBER(8) NOT NULL,
oi_width NUMBER(5) NOT NULL,
oi_height NUMBER(5) NOT NULL,
oi_bits NUMBER(3) NOT NULL,
oi_description CLOB,
oi_user NUMBER(8) NOT NULL REFERENCES "user"(user_id),
oi_user_text VARCHAR2(255) NOT NULL,
oi_timestamp TIMESTAMP WITH TIME ZONE NOT NULL
);
CREATE INDEX oi_name ON oldimage (oi_name);
CREATE SEQUENCE rc_rc_id_seq;
CREATE TABLE recentchanges (
rc_id NUMBER(8) NOT NULL,
rc_timestamp TIMESTAMP WITH TIME ZONE,
rc_cur_time TIMESTAMP WITH TIME ZONE,
rc_user NUMBER(8) DEFAULT 0 NOT NULL,
rc_user_text VARCHAR2(255),
rc_namespace NUMBER(4) DEFAULT 0 NOT NULL,
rc_title VARCHAR2(255) NOT NULL,
rc_comment VARCHAR2(255),
rc_minor NUMBER(3) DEFAULT 0 NOT NULL,
rc_bot NUMBER(3) DEFAULT 0 NOT NULL,
rc_new NUMBER(3) DEFAULT 0 NOT NULL,
rc_cur_id NUMBER(8),
rc_this_oldid NUMBER(8) NOT NULL,
rc_last_oldid NUMBER(8) NOT NULL,
rc_type NUMBER(3) DEFAULT 0 NOT NULL,
rc_moved_to_ns NUMBER(3),
rc_moved_to_title VARCHAR2(255),
rc_patrolled NUMBER(3) DEFAULT 0 NOT NULL,
rc_ip VARCHAR2(40),
rc_old_len NUMBER(10) DEFAULT 0,
rc_new_len NUMBER(10) DEFAULT 0,
CONSTRAINT rc_pk PRIMARY KEY (rc_id)
);
CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
CREATE INDEX rc_namespace_title ON recentchanges(rc_namespace, rc_title);
CREATE INDEX rc_cur_id ON recentchanges(rc_cur_id);
CREATE INDEX new_name_timestamp ON recentchanges(rc_new, rc_namespace, rc_timestamp);
CREATE INDEX rc_ip ON recentchanges(rc_ip);
CREATE TABLE watchlist (
wl_user NUMBER(8) NOT NULL
REFERENCES "user"(user_id)
ON DELETE CASCADE,
wl_namespace NUMBER(8) DEFAULT 0 NOT NULL,
wl_title VARCHAR2(255) NOT NULL,
wl_notificationtimestamp TIMESTAMP WITH TIME ZONE DEFAULT NULL
);
CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist
(wl_user, wl_namespace, wl_title);
CREATE INDEX wl_namespace_title ON watchlist(wl_namespace, wl_title);
--
-- Used by texvc math-rendering extension to keep track
-- of previously-rendered items.
--
CREATE TABLE math (
math_inputhash VARCHAR2(16) NOT NULL UNIQUE,
math_outputhash VARCHAR2(16) NOT NULL,
math_html_conservativeness NUMBER(1) NOT NULL,
math_html CLOB,
math_mathml CLOB
);
--
-- Recognized interwiki link prefixes
--
CREATE TABLE interwiki (
iw_prefix VARCHAR2(32) NOT NULL UNIQUE,
iw_url VARCHAR2(127) NOT NULL,
iw_local NUMBER(1) NOT NULL,
iw_trans NUMBER(1) DEFAULT 0 NOT NULL
);
CREATE TABLE querycache (
qc_type VARCHAR2(32) NOT NULL,
qc_value NUMBER(5) DEFAULT 0 NOT NULL,
qc_namespace NUMBER(4) DEFAULT 0 NOT NULL,
qc_title VARCHAR2(255)
);
CREATE INDEX querycache_type_value ON querycache(qc_type, qc_value);
--
-- For a few generic cache operations if not using Memcached
--
CREATE TABLE objectcache (
keyname CHAR(255) DEFAULT '',
value CLOB,
exptime TIMESTAMP WITH TIME ZONE
);
CREATE UNIQUE INDEX oc_keyname_idx ON objectcache(keyname);
CREATE INDEX oc_exptime_idx ON objectcache(exptime);
CREATE TABLE logging (
log_type VARCHAR2(10) NOT NULL,
log_action VARCHAR2(10) NOT NULL,
log_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
log_user NUMBER(8) REFERENCES "user"(user_id),
log_namespace NUMBER(4),
log_title VARCHAR2(255) NOT NULL,
log_comment VARCHAR2(255),
log_params CLOB
);
CREATE INDEX logging_type_name ON logging(log_type, log_timestamp);
CREATE INDEX logging_user_time ON logging(log_user, log_timestamp);
CREATE INDEX logging_page_time ON logging(log_namespace, log_title, log_timestamp);
-- Hold group name and description
--CREATE TABLE /*$wgDBprefix*/groups (
-- gr_id int(5) unsigned NOT NULL auto_increment,
-- gr_name varchar(50) NOT NULL default '',
-- gr_description varchar(255) NOT NULL default '',
-- gr_rights tinyblob,
-- PRIMARY KEY (gr_id)
--
--) TYPE=InnoDB;
CREATE OR REPLACE PROCEDURE add_user_right (name VARCHAR2, new_right VARCHAR2) AS
user_id "user".user_id%TYPE;;
user_is_missing EXCEPTION;;
BEGIN
SELECT user_id INTO user_id FROM "user" WHERE user_name = name;;
INSERT INTO user_groups (ug_user, ug_group) VALUES(user_id, new_right);;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The specified user does not exist.');;
END add_user_right;;
;
CREATE OR REPLACE PROCEDURE add_interwiki (prefix VARCHAR2, url VARCHAR2, is_local NUMBER) AS
BEGIN
INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES(prefix, url, is_local);;
END add_interwiki;;
;
|