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