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


7ccdb60f8c363d22c50671f29d02ec0a39c199b5
[lhc/web/wiklou.git] / maintenance / oracle / tables.sql
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.
4
5 CREATE SEQUENCE user_user_id_seq;
6
7 CREATE TABLE "user" (
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
20 );
21 CREATE UNIQUE INDEX user_name_idx ON "user" (user_name);
22 CREATE INDEX user_email_token_idx ON "user" (user_email_token);
23
24 CREATE TABLE user_groups (
25 ug_user NUMBER(5) DEFAULT '0' NOT NULL
26 REFERENCES "user" (user_id)
27 ON DELETE CASCADE,
28 ug_group VARCHAR2(16) NOT NULL,
29 CONSTRAINT user_groups_pk PRIMARY KEY (ug_user, ug_group)
30 );
31 CREATE INDEX user_groups_group_idx ON user_groups(ug_group);
32
33 CREATE TABLE user_newtalk (
34 user_id NUMBER(5) DEFAULT 0 NOT NULL,
35 user_ip VARCHAR2(40) DEFAULT '' NOT NULL
36 );
37 CREATE INDEX user_newtalk_id_idx ON user_newtalk(user_id);
38 CREATE INDEX user_newtalk_ip_idx ON user_newtalk(user_ip);
39
40 CREATE SEQUENCE page_page_id_seq;
41 CREATE TABLE page (
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
53 );
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);
57
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)
63 ON DELETE CASCADE,
64 rev_text_id NUMBER(8) NOT NULL,
65 rev_comment CLOB,
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)
72 );
73
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);
79
80 CREATE SEQUENCE text_old_id_val;
81
82 CREATE TABLE text (
83 old_id NUMBER(8) NOT NULL,
84 old_text CLOB,
85 old_flags CLOB,
86 CONSTRAINT text_pk PRIMARY KEY (old_id)
87 );
88
89 CREATE TABLE archive (
90 ar_namespace NUMBER(5) NOT NULL,
91 ar_title VARCHAR2(255) NOT NULL,
92 ar_text CLOB,
93 ar_comment CLOB,
94 ar_user NUMBER(8),
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,
98 ar_flags CLOB,
99 ar_rev_id NUMBER(8),
100 ar_text_id NUMBER(8)
101 );
102 CREATE INDEX archive_name_title_timestamp ON archive(ar_namespace,ar_title,ar_timestamp);
103
104 CREATE TABLE pagelinks (
105 pl_from NUMBER(8) NOT NULL
106 REFERENCES page(page_id)
107 ON DELETE CASCADE,
108 pl_namespace NUMBER(4) DEFAULT 0 NOT NULL,
109 pl_title VARCHAR2(255) NOT NULL
110 );
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);
113
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
117 );
118 CREATE UNIQUE INDEX il_from ON imagelinks(il_from, il_to);
119 CREATE INDEX il_to ON imagelinks(il_to);
120
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
126 );
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);
130
131 --
132 -- Contains a single row with some aggregate info
133 -- on the state of the site.
134 --
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
143 );
144 CREATE UNIQUE INDEX ss_row_id ON site_stats(ss_row_id);
145
146 --
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.)
152 --
153 CREATE TABLE hitcounter (
154 hc_id NUMBER NOT NULL
155 );
156
157 --
158 -- The internet is full of jerks, alas. Sometimes it's handy
159 -- to block a vandal or troll account.
160 --
161 CREATE SEQUENCE ipblocks_ipb_id_val;
162 CREATE TABLE ipblocks (
163 ipb_id NUMBER(8) NOT NULL,
164 ipb_address VARCHAR2(40),
165 ipb_user NUMBER(8),
166 ipb_by NUMBER(8) NOT NULL
167 REFERENCES "user" (user_id)
168 ON DELETE CASCADE,
169 ipb_reason CLOB,
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)
174 );
175 CREATE INDEX ipb_address ON ipblocks(ipb_address);
176 CREATE INDEX ipb_user ON ipblocks(ipb_user);
177
178 CREATE TABLE image (
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,
183 img_metadata CLOB,
184 img_bits NUMBER(3),
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)
193 );
194 CREATE INDEX img_size_idx ON image(img_size);
195 CREATE INDEX img_timestamp_idx ON image(img_timestamp);
196
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,
204 oi_description CLOB,
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
208 );
209 CREATE INDEX oi_name ON oldimage (oi_name);
210
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,
224 rc_cur_id NUMBER(8),
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,
231 rc_ip VARCHAR2(40),
232 CONSTRAINT rc_pk PRIMARY KEY (rc_id)
233 );
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);
239
240 CREATE TABLE watchlist (
241 wl_user NUMBER(8) NOT NULL
242 REFERENCES "user"(user_id)
243 ON DELETE CASCADE,
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
247 );
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);
251
252 --
253 -- Used by texvc math-rendering extension to keep track
254 -- of previously-rendered items.
255 --
256 CREATE TABLE math (
257 math_inputhash VARCHAR2(16) NOT NULL UNIQUE,
258 math_outputhash VARCHAR2(16) NOT NULL,
259 math_html_conservativeness NUMBER(1) NOT NULL,
260 math_html CLOB,
261 math_mathml CLOB
262 );
263
264 --
265 -- Recognized interwiki link prefixes
266 --
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
272 );
273
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)
279 );
280 CREATE INDEX querycache_type_value ON querycache(qc_type, qc_value);
281
282 --
283 -- For a few generic cache operations if not using Memcached
284 --
285 CREATE TABLE objectcache (
286 keyname CHAR(255) DEFAULT '',
287 value CLOB,
288 exptime TIMESTAMP WITH TIME ZONE
289 );
290 CREATE UNIQUE INDEX oc_keyname_idx ON objectcache(keyname);
291 CREATE INDEX oc_exptime_idx ON objectcache(exptime);
292
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),
300 val_ip VARCHAR2(20)
301 );
302 CREATE INDEX val_user ON "validate" (val_user,val_revision);
303
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),
312 log_params CLOB
313 );
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);
317
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)
325 --
326 --) TYPE=InnoDB;
327
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;;
331 BEGIN
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);;
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 DBMS_OUTPUT.PUT_LINE('The specified user does not exist.');;
337 END add_user_right;;
338 ;
339
340 CREATE OR REPLACE PROCEDURE add_interwiki (prefix VARCHAR2, url VARCHAR2, is_local NUMBER) AS
341 BEGIN
342 INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES(prefix, url, is_local);;
343 END add_interwiki;;
344 ;
Wiklou, le Wiki du Wiklou