dépôts / lhc / web / wiklou.git / blob
? search:
summary | shortlog | log | commit | commitdiff | tree
history | raw | HEAD
config/index.php:
[lhc/web/wiklou.git] / maintenance / ibm_db2 / tables.sql
1 -- DB2
2
3 -- SQL to create the initial tables for the MediaWiki database.
4 -- This is read and executed by the install script; you should
5 -- not have to run it by itself unless doing a manual install.
6 -- This is the IBM DB2 version.
7 -- For information about each table, please see the notes in maintenance/tables.sql
8
9
10 CREATE TABLE user (
11 user_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
12 user_name VARCHAR(255) NOT NULL UNIQUE,
13 user_real_name VARCHAR(255),
14 user_password VARCHAR(1024),
15 user_newpassword VARCHAR(1024),
16 user_newpass_time TIMESTAMP(3),
17 user_token VARCHAR(255),
18 user_email VARCHAR(255),
19 user_email_token VARCHAR(255),
20 user_email_token_expires TIMESTAMP(3),
21 user_email_authenticated TIMESTAMP(3),
22 -- obsolete, replace by user_properties table
23 user_options CLOB(64K) INLINE LENGTH 4096,
24 user_touched TIMESTAMP(3),
25 user_registration TIMESTAMP(3),
26 user_editcount INTEGER
27 );
28 CREATE INDEX user_email_token_idx ON user (user_email_token);
29 --leonsp:
30 CREATE UNIQUE INDEX user_include_idx
31 ON user(user_id)
32 INCLUDE (user_name, user_real_name, user_password, user_newpassword, user_newpass_time, user_token,
33 user_email, user_email_token, user_email_token_expires, user_email_authenticated,
34 user_touched, user_registration, user_editcount);
35
36 -- Create a dummy user to satisfy fk contraints especially with revisions
37 INSERT INTO user(
38 user_name, user_real_name, user_password, user_newpassword, user_newpass_time,
39 user_email, user_email_authenticated, user_options, user_token, user_registration, user_editcount)
40 VALUES (
41 'Anonymous','', NULL, NULL, CURRENT_TIMESTAMP,
42 NULL, NULL, NULL, NULL, CURRENT_timestamp, 0);
43
44
45 CREATE TABLE user_groups (
46 ug_user INTEGER REFERENCES user(user_id) ON DELETE CASCADE,
47 ug_group VARCHAR(255) NOT NULL
48 );
49 CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
50 --leonsp:
51 CREATE UNIQUE INDEX user_groups_include_idx
52 ON user_groups(ug_user)
53 INCLUDE (ug_group);
54
55
56 CREATE TABLE user_newtalk (
57 -- registered users key
58 user_id INTEGER NOT NULL REFERENCES user(user_id) ON DELETE CASCADE,
59 -- anonymous users key
60 user_ip VARCHAR(40),
61 user_last_timestamp TIMESTAMP(3)
62 );
63 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
64 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
65 --leonsp:
66 CREATE UNIQUE INDEX user_newtalk_include_idx
67 ON user_newtalk(user_id, user_ip)
68 INCLUDE (user_last_timestamp);
69
70
71 CREATE TABLE page (
72 page_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
73 page_namespace SMALLINT NOT NULL,
74 page_title VARCHAR(255) NOT NULL,
75 page_restrictions VARCHAR(1024),
76 page_counter BIGINT NOT NULL DEFAULT 0,
77 page_is_redirect SMALLINT NOT NULL DEFAULT 0,
78 page_is_new SMALLINT NOT NULL DEFAULT 0,
79 page_random NUMERIC(15,14) NOT NULL,
80 page_touched TIMESTAMP(3),
81 page_latest INTEGER NOT NULL, -- FK?
82 page_len INTEGER NOT NULL
83 );
84 CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
85 CREATE INDEX page_random_idx ON page (page_random);
86 CREATE INDEX page_len_idx ON page (page_len);
87 --leonsp:
88 CREATE UNIQUE INDEX page_id_include
89 ON page (page_id)
90 INCLUDE (page_namespace, page_title, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);
91 CREATE UNIQUE INDEX page_name_include
92 ON page (page_namespace, page_title)
93 INCLUDE (page_id, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);
94
95
96 CREATE TABLE revision (
97 rev_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
98 rev_page INTEGER REFERENCES page (page_id) ON DELETE CASCADE,
99 rev_text_id INTEGER, -- FK
100 rev_comment VARCHAR(1024),
101 rev_user INTEGER NOT NULL REFERENCES user(user_id) ON DELETE RESTRICT,
102 rev_user_text VARCHAR(255) NOT NULL,
103 rev_timestamp TIMESTAMP(3) NOT NULL,
104 rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
105 rev_deleted SMALLINT NOT NULL DEFAULT 0,
106 rev_len INTEGER,
107 rev_parent_id INTEGER
108 );
109 CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
110 CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
111 CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
112 CREATE INDEX rev_user_idx ON revision (rev_user);
113 CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
114
115
116 -- CREATE SEQUENCE text_old_id_val;
117 CREATE TABLE text ( -- replaces reserved word 'text'
118 --old_id INTEGER NOT NULL,
119 old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
120 --PRIMARY KEY DEFAULT nextval('text_old_id_val'),
121 old_text CLOB(16M) INLINE LENGTH 4096,
122 old_flags VARCHAR(1024)
123 );
124
125 --CREATE SEQUENCE pr_id_val;
126 CREATE TABLE page_restrictions (
127 --pr_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('pr_id_val'),
128 --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
129 pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
130 pr_page INTEGER NOT NULL
131 --(used to be nullable)
132 REFERENCES page (page_id) ON DELETE CASCADE,
133 pr_type VARCHAR(60) NOT NULL,
134 pr_level VARCHAR(60) NOT NULL,
135 pr_cascade SMALLINT NOT NULL,
136 pr_user INTEGER,
137 pr_expiry TIMESTAMP(3)
138 --PRIMARY KEY (pr_page, pr_type)
139 );
140 --ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
141 CREATE UNIQUE INDEX pr_pagetype ON page_restrictions (pr_page,pr_type);
142 CREATE INDEX pr_typelevel ON page_restrictions (pr_type,pr_level);
143 CREATE INDEX pr_level ON page_restrictions (pr_level);
144 CREATE INDEX pr_cascade ON page_restrictions (pr_cascade);
145
146 CREATE TABLE page_props (
147 pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
148 pp_propname VARCHAR(255) NOT NULL,
149 pp_value CLOB(64K) INLINE LENGTH 4096 NOT NULL,
150 PRIMARY KEY (pp_page,pp_propname)
151 );
152 --ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
153 CREATE INDEX page_props_propname ON page_props (pp_propname);
154
155
156
157 CREATE TABLE archive (
158 ar_namespace SMALLINT NOT NULL,
159 ar_title VARCHAR(255) NOT NULL,
160 ar_text CLOB(16M) INLINE LENGTH 4096,
161 ar_page_id INTEGER,
162 ar_parent_id INTEGER,
163 ar_comment VARCHAR(1024),
164 ar_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
165 ar_user_text VARCHAR(255) NOT NULL,
166 ar_timestamp TIMESTAMP(3) NOT NULL,
167 ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
168 ar_flags VARCHAR(1024),
169 ar_rev_id INTEGER,
170 ar_text_id INTEGER,
171 ar_deleted SMALLINT NOT NULL DEFAULT 0,
172 ar_len INTEGER
173 );
174 CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
175 CREATE INDEX archive_user_text ON archive (ar_user_text);
176
177
178
179 CREATE TABLE redirect (
180 rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
181 rd_namespace SMALLINT NOT NULL DEFAULT 0,
182 rd_title VARCHAR(255) NOT NULL DEFAULT '',
183 rd_interwiki varchar(32),
184 rd_fragment VARCHAR(255)
185 );
186 CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
187
188
189 CREATE TABLE pagelinks (
190 pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
191 pl_namespace SMALLINT NOT NULL,
192 pl_title VARCHAR(255) NOT NULL
193 );
194 CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
195
196 CREATE TABLE templatelinks (
197 tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
198 tl_namespace SMALLINT NOT NULL,
199 tl_title VARCHAR(255) NOT NULL
200 );
201 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
202 CREATE UNIQUE INDEX tl_from_idx ON templatelinks (tl_from,tl_namespace,tl_title);
203
204 CREATE TABLE imagelinks (
205 il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
206 il_to VARCHAR(255) NOT NULL
207 );
208 CREATE UNIQUE INDEX il_from_idx ON imagelinks (il_to,il_from);
209 CREATE UNIQUE INDEX il_to_idx ON imagelinks (il_from,il_to);
210
211 CREATE TABLE categorylinks (
212 cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
213 cl_to VARCHAR(255) NOT NULL,
214 cl_sortkey VARCHAR(70),
215 cl_timestamp TIMESTAMP(3) NOT NULL
216 );
217 CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
218 CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
219
220
221
222 CREATE TABLE externallinks (
223 el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
224 el_to VARCHAR(1024) NOT NULL,
225 el_index VARCHAR(1024) NOT NULL
226 );
227 CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
228 CREATE INDEX externallinks_index ON externallinks (el_index);
229
230
231 --
232 -- Track external user accounts, if ExternalAuth is used
233 --
234 CREATE TABLE external_user (
235 -- Foreign key to user_id
236 eu_wiki_id INTEGER NOT NULL PRIMARY KEY,
237
238 -- Some opaque identifier provided by the external database
239 eu_external_id VARCHAR(255) NOT NULL
240 );
241 CREATE UNIQUE INDEX eu_external_id_idx
242 ON external_user (eu_external_id)
243 INCLUDE (eu_wiki_id);
244 CREATE UNIQUE INDEX eu_wiki_id_idx
245 ON external_user (eu_wiki_id)
246 INCLUDE (eu_external_id);
247
248
249
250 CREATE TABLE langlinks (
251 ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
252 ll_lang VARCHAR(20),
253 ll_title VARCHAR(255)
254 );
255 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
256 CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);
257
258
259 CREATE TABLE site_stats (
260 ss_row_id INTEGER NOT NULL UNIQUE,
261 ss_total_views INTEGER DEFAULT 0,
262 ss_total_edits INTEGER DEFAULT 0,
263 ss_good_articles INTEGER DEFAULT 0,
264 ss_total_pages INTEGER DEFAULT -1,
265 ss_users INTEGER DEFAULT -1,
266 ss_admins INTEGER DEFAULT -1,
267 ss_images INTEGER DEFAULT 0
268 );
269
270 CREATE TABLE hitcounter (
271 hc_id BIGINT NOT NULL
272 );
273
274 CREATE TABLE ipblocks (
275 ipb_id INTEGER NOT NULL PRIMARY KEY,
276 --DEFAULT nextval('ipblocks_ipb_id_val'),
277 ipb_address VARCHAR(1024),
278 ipb_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
279 ipb_by INTEGER NOT NULL REFERENCES user(user_id) ON DELETE CASCADE,
280 ipb_by_text VARCHAR(255) NOT NULL DEFAULT '',
281 ipb_reason VARCHAR(1024) NOT NULL,
282 ipb_timestamp TIMESTAMP(3) NOT NULL,
283 ipb_auto SMALLINT NOT NULL DEFAULT 0,
284 ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
285 ipb_create_account SMALLINT NOT NULL DEFAULT 1,
286 ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
287 ipb_expiry TIMESTAMP(3) NOT NULL,
288 ipb_range_start VARCHAR(1024),
289 ipb_range_end VARCHAR(1024),
290 ipb_deleted SMALLINT NOT NULL DEFAULT 0,
291 ipb_block_email SMALLINT NOT NULL DEFAULT 0
292
293 );
294 CREATE INDEX ipb_address ON ipblocks (ipb_address);
295 CREATE INDEX ipb_user ON ipblocks (ipb_user);
296 CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
297
298
299
300 CREATE TABLE image (
301 img_name VARCHAR(255) NOT NULL PRIMARY KEY,
302 img_size INTEGER NOT NULL,
303 img_width INTEGER NOT NULL,
304 img_height INTEGER NOT NULL,
305 img_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
306 img_bits SMALLINT,
307 img_media_type VARCHAR(255),
308 img_major_mime VARCHAR(255) DEFAULT 'unknown',
309 img_minor_mime VARCHAR(32) DEFAULT 'unknown',
310 img_description VARCHAR(1024) NOT NULL DEFAULT '',
311 img_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
312 img_user_text VARCHAR(255) NOT NULL DEFAULT '',
313 img_timestamp TIMESTAMP(3),
314 img_sha1 VARCHAR(255) NOT NULL DEFAULT ''
315 );
316 CREATE INDEX img_size_idx ON image (img_size);
317 CREATE INDEX img_timestamp_idx ON image (img_timestamp);
318 CREATE INDEX img_sha1 ON image (img_sha1);
319
320 CREATE TABLE oldimage (
321 oi_name VARCHAR(255) NOT NULL,
322 oi_archive_name VARCHAR(255) NOT NULL,
323 oi_size INTEGER NOT NULL,
324 oi_width INTEGER NOT NULL,
325 oi_height INTEGER NOT NULL,
326 oi_bits SMALLINT NOT NULL,
327 oi_description VARCHAR(1024),
328 oi_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
329 oi_user_text VARCHAR(255) NOT NULL,
330 oi_timestamp TIMESTAMP(3) NOT NULL,
331 oi_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
332 oi_media_type VARCHAR(255) ,
333 oi_major_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
334 oi_minor_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
335 oi_deleted SMALLINT NOT NULL DEFAULT 0,
336 oi_sha1 VARCHAR(255) NOT NULL DEFAULT '',
337 FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
338 );
339 --ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE;
340 CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
341 CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
342 CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
343
344
345 CREATE SEQUENCE filearchive_fa_id_seq;
346 CREATE TABLE filearchive (
347 fa_id INTEGER NOT NULL PRIMARY KEY,
348 --PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
349 fa_name VARCHAR(255) NOT NULL,
350 fa_archive_name VARCHAR(255),
351 fa_storage_group VARCHAR(255),
352 fa_storage_key VARCHAR(32),
353 fa_deleted_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
354 fa_deleted_timestamp TIMESTAMP(3) NOT NULL,
355 fa_deleted_reason VARCHAR(255),
356 fa_size INTEGER NOT NULL,
357 fa_width INTEGER NOT NULL,
358 fa_height INTEGER NOT NULL,
359 fa_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
360 fa_bits SMALLINT,
361 fa_media_type VARCHAR(255),
362 fa_major_mime VARCHAR(255) DEFAULT 'unknown',
363 fa_minor_mime VARCHAR(255) DEFAULT 'unknown',
364 fa_description VARCHAR(1024) NOT NULL,
365 fa_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
366 fa_user_text VARCHAR(255) NOT NULL,
367 fa_timestamp TIMESTAMP(3),
368 fa_deleted SMALLINT NOT NULL DEFAULT 0
369 );
370 CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
371 CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
372 CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
373 CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
374
375 CREATE SEQUENCE rc_rc_id_seq;
376 CREATE TABLE recentchanges (
377 rc_id INTEGER NOT NULL PRIMARY KEY,
378 --PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
379 rc_timestamp TIMESTAMP(3) NOT NULL,
380 rc_cur_time TIMESTAMP(3) NOT NULL,
381 rc_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
382 rc_user_text VARCHAR(255) NOT NULL,
383 rc_namespace SMALLINT NOT NULL,
384 rc_title VARCHAR(255) NOT NULL,
385 rc_comment VARCHAR(255),
386 rc_minor SMALLINT NOT NULL DEFAULT 0,
387 rc_bot SMALLINT NOT NULL DEFAULT 0,
388 rc_new SMALLINT NOT NULL DEFAULT 0,
389 rc_cur_id INTEGER REFERENCES page(page_id) ON DELETE SET NULL,
390 rc_this_oldid INTEGER NOT NULL,
391 rc_last_oldid INTEGER NOT NULL,
392 rc_type SMALLINT NOT NULL DEFAULT 0,
393 rc_moved_to_ns SMALLINT,
394 rc_moved_to_title VARCHAR(255),
395 rc_patrolled SMALLINT NOT NULL DEFAULT 0,
396 rc_ip VARCHAR(40), -- was CIDR type
397 rc_old_len INTEGER,
398 rc_new_len INTEGER,
399 rc_deleted SMALLINT NOT NULL DEFAULT 0,
400 rc_logid INTEGER NOT NULL DEFAULT 0,
401 rc_log_type VARCHAR(255),
402 rc_log_action VARCHAR(255),
403 rc_params CLOB(64K) INLINE LENGTH 4096
404
405 );
406 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
407 CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
408 CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
409 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
410 CREATE INDEX rc_ip ON recentchanges (rc_ip);
411
412
413
414 CREATE TABLE watchlist (
415 wl_user INTEGER NOT NULL REFERENCES user(user_id) ON DELETE CASCADE,
416 wl_namespace SMALLINT NOT NULL DEFAULT 0,
417 wl_title VARCHAR(255) NOT NULL,
418 wl_notificationtimestamp TIMESTAMP(3)
419 );
420 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
421
422
423 CREATE TABLE math (
424 math_inputhash VARCHAR(16) FOR BIT DATA NOT NULL UNIQUE,
425 math_outputhash VARCHAR(16) FOR BIT DATA NOT NULL,
426 math_html_conservativeness SMALLINT NOT NULL,
427 math_html CLOB(64K) INLINE LENGTH 4096,
428 math_mathml CLOB(64K) INLINE LENGTH 4096
429 );
430
431
432 CREATE TABLE interwiki (
433 iw_prefix VARCHAR(32) NOT NULL UNIQUE,
434 iw_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,
435 iw_local SMALLINT NOT NULL,
436 iw_trans SMALLINT NOT NULL DEFAULT 0
437 );
438
439
440 CREATE TABLE querycache (
441 qc_type VARCHAR(255) NOT NULL,
442 qc_value INTEGER NOT NULL,
443 qc_namespace SMALLINT NOT NULL,
444 qc_title VARCHAR(255) NOT NULL
445 );
446 CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
447
448
449
450 CREATE TABLE querycache_info (
451 qci_type VARCHAR(255) UNIQUE NOT NULL,
452 qci_timestamp TIMESTAMP(3)
453 );
454
455
456 CREATE TABLE querycachetwo (
457 qcc_type VARCHAR(255) NOT NULL,
458 qcc_value INTEGER NOT NULL DEFAULT 0,
459 qcc_namespace INTEGER NOT NULL DEFAULT 0,
460 qcc_title VARCHAR(255) NOT NULL DEFAULT '',
461 qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
462 qcc_titletwo VARCHAR(255) NOT NULL DEFAULT ''
463 );
464 CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
465 CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
466 CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
467
468 CREATE TABLE objectcache (
469 keyname VARCHAR(255) NOT NULL UNIQUE, -- was nullable
470 value CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
471 exptime TIMESTAMP(3) NOT NULL
472 );
473 CREATE INDEX objectcacache_exptime ON objectcache (exptime);
474
475
476
477 CREATE TABLE transcache (
478 tc_url VARCHAR(255) NOT NULL UNIQUE,
479 tc_contents VARCHAR(255) NOT NULL,
480 tc_time TIMESTAMP(3) NOT NULL
481 );
482
483 CREATE SEQUENCE log_log_id_seq;
484 CREATE TABLE logging (
485 log_id INTEGER NOT NULL PRIMARY KEY,
486 --PRIMARY KEY DEFAULT nextval('log_log_id_seq'),
487 log_type VARCHAR(32) NOT NULL,
488 log_action VARCHAR(32) NOT NULL,
489 log_timestamp TIMESTAMP(3) NOT NULL,
490 log_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
491 -- Name of the user who performed this action
492 log_user_text VARCHAR(255) NOT NULL default '',
493 log_namespace SMALLINT NOT NULL,
494 log_title VARCHAR(255) NOT NULL,
495 log_page INTEGER,
496 log_comment VARCHAR(255),
497 log_params CLOB(64K) INLINE LENGTH 4096,
498 log_deleted SMALLINT NOT NULL DEFAULT 0
499 );
500 CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
501 CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
502 CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
503 CREATE INDEX log_user_type_time ON logging (log_user, log_type, log_timestamp);
504 CREATE INDEX log_page_id_time ON logging (log_page,log_timestamp);
505
506
507 CREATE SEQUENCE trackbacks_tb_id_seq;
508 CREATE TABLE trackbacks (
509 tb_id INTEGER NOT NULL PRIMARY KEY,
510 --PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
511 tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
512 tb_title VARCHAR(255) NOT NULL,
513 tb_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,
514 tb_ex CLOB(64K) INLINE LENGTH 4096,
515 tb_name VARCHAR(255)
516 );
517 CREATE INDEX trackback_page ON trackbacks (tb_page);
518
519
520 CREATE SEQUENCE job_job_id_seq;
521 CREATE TABLE job (
522 job_id INTEGER NOT NULL PRIMARY KEY,
523 --PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
524 job_cmd VARCHAR(255) NOT NULL,
525 job_namespace SMALLINT NOT NULL,
526 job_title VARCHAR(255) NOT NULL,
527 job_params CLOB(64K) INLINE LENGTH 4096 NOT NULL
528 );
529 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
530
531
532
533 -- Postgres' Tsearch2 dropped
534 --ALTER TABLE page ADD titlevector tsvector;
535 --CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
536 --$mw$
537 --BEGIN
538 --IF TG_OP = 'INSERT' THEN
539 -- NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
540 --ELSIF NEW.page_title != OLD.page_title THEN
541 -- NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
542 --END IF;
543 --RETURN NEW;
544 --END;
545 --$mw$;
546
547 --CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
548 -- FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
549
550
551 --ALTER TABLE text ADD textvector tsvector;
552 --CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
553 --$mw$
554 --BEGIN
555 --IF TG_OP = 'INSERT' THEN
556 -- NEW.textvector = to_tsvector('default',NEW.old_text);
557 --ELSIF NEW.old_text != OLD.old_text THEN
558 -- NEW.textvector := to_tsvector('default',NEW.old_text);
559 --END IF;
560 --RETURN NEW;
561 --END;
562 --$mw$;
563
564 --CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON text
565 -- FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
566
567 -- These are added by the setup script due to version compatibility issues
568 -- If using 8.1, we switch from "gin" to "gist"
569
570 --CREATE INDEX ts2_page_title ON page USING gin(titlevector);
571 --CREATE INDEX ts2_page_text ON text USING gin(textvector);
572
573 --TODO
574 --CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
575 --$mw$
576 -- INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
577 -- SELECT 1;
578 --$mw$;
579
580 -- hack implementation
581 -- should be replaced with OmniFind, Contains(), etc
582 CREATE TABLE searchindex (
583 si_page int NOT NULL,
584 si_title varchar(255) NOT NULL default '',
585 si_text clob NOT NULL
586 );
587
588 -- This table is not used unless profiling is turned on
589 CREATE TABLE profiling (
590 pf_count INTEGER NOT NULL DEFAULT 0,
591 pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
592 pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,
593 pf_name VARCHAR(255) NOT NULL,
594 pf_server VARCHAR(255)
595 );
596 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
597
598 CREATE TABLE protected_titles (
599 pt_namespace SMALLINT NOT NULL,
600 pt_title VARCHAR(255) NOT NULL,
601 pt_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
602 pt_reason VARCHAR(1024),
603 pt_timestamp TIMESTAMP(3) NOT NULL,
604 pt_expiry TIMESTAMP(3) ,
605 pt_create_perm VARCHAR(60) NOT NULL DEFAULT ''
606 );
607 CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
608
609
610
611 CREATE TABLE updatelog (
612 ul_key VARCHAR(255) NOT NULL PRIMARY KEY
613 );
614
615 --CREATE SEQUENCE category_id_seq;
616 CREATE TABLE category (
617 cat_id INTEGER NOT NULL PRIMARY KEY,
618 --PRIMARY KEY DEFAULT nextval('category_id_seq'),
619 cat_title VARCHAR(255) NOT NULL,
620 cat_pages INTEGER NOT NULL DEFAULT 0,
621 cat_subcats INTEGER NOT NULL DEFAULT 0,
622 cat_files INTEGER NOT NULL DEFAULT 0,
623 cat_hidden SMALLINT NOT NULL DEFAULT 0
624 );
625 CREATE UNIQUE INDEX category_title ON category(cat_title);
626 CREATE INDEX category_pages ON category(cat_pages);
627
628 -- added for 1.15
629
630 -- A table to track tags for revisions, logs and recent changes.
631 CREATE TABLE change_tag (
632 ct_rc_id INTEGER,
633 ct_log_id INTEGER,
634 ct_rev_id INTEGER,
635 ct_tag varchar(255) NOT NULL,
636 ct_params CLOB(64K) INLINE LENGTH 4096
637 );
638 CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag (ct_rc_id,ct_tag);
639 CREATE UNIQUE INDEX change_tag_log_tag ON change_tag (ct_log_id,ct_tag);
640 CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag (ct_rev_id,ct_tag);
641 -- Covering index, so we can pull all the info only out of the index.
642 CREATE INDEX change_tag_tag_id ON change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
643
644
645 -- Rollup table to pull a LIST of tags simply
646 CREATE TABLE tag_summary (
647 ts_rc_id INTEGER,
648 ts_log_id INTEGER,
649 ts_rev_id INTEGER,
650 ts_tags CLOB(64K) INLINE LENGTH 4096 NOT NULL
651 );
652 CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary (ts_rc_id);
653 CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary (ts_log_id);
654 CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary (ts_rev_id);
655
656
657 CREATE TABLE valid_tag (
658 vt_tag varchar(255) NOT NULL PRIMARY KEY
659 );
660
661 --
662 -- User preferences and perhaps other fun stuff. :)
663 -- Replaces the old user.user_options blob, with a couple nice properties:
664 --
665 -- 1) We only store non-default settings, so changes to the defaults
666 -- are now reflected for everybody, not just new accounts.
667 -- 2) We can more easily do bulk lookups, statistics, or modifications of
668 -- saved options since it's a sane table structure.
669 --
670 CREATE TABLE user_properties (
671 -- Foreign key to user.user_id
672 up_user INTEGER NOT NULL,
673
674 -- Name of the option being saved. This is indexed for bulk lookup.
675 up_property VARCHAR(32) FOR BIT DATA NOT NULL,
676
677 -- Property value as a string.
678 up_value CLOB(64K) INLINE LENGTH 4096
679 );
680 CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);
681 CREATE INDEX user_properties_property ON user_properties (up_property);
682
683 CREATE TABLE log_search (
684 -- The type of ID (rev ID, log ID, rev TIMESTAMP(3), username)
685 ls_field VARCHAR(32) FOR BIT DATA NOT NULL,
686 -- The value of the ID
687 ls_value varchar(255) NOT NULL,
688 -- Key to log_id
689 ls_log_id INTEGER NOT NULL default 0
690 );
691 CREATE UNIQUE INDEX ls_field_val ON log_search (ls_field,ls_value,ls_log_id);
692 CREATE INDEX ls_log_id ON log_search (ls_log_id);
693
694 CREATE TABLE mediawiki_version (
695 type VARCHAR(1024) NOT NULL,
696 mw_version VARCHAR(1024) NOT NULL,
697 notes VARCHAR(1024) ,
698
699 pg_version VARCHAR(1024) ,
700 pg_dbname VARCHAR(1024) ,
701 pg_user VARCHAR(1024) ,
702 pg_port VARCHAR(1024) ,
703 mw_schema VARCHAR(1024) ,
704 ts2_schema VARCHAR(1024) ,
705 ctype VARCHAR(1024) ,
706
707 sql_version VARCHAR(1024) ,
708 sql_date VARCHAR(1024) ,
709 cdate TIMESTAMP(3) NOT NULL DEFAULT CURRENT TIMESTAMP
710 );
711
712 INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date)
713 VALUES ('Creation','??','$LastChangedRevision: 34049 $','$LastChangedDate: 2008-04-30 10:20:36 -0400 (Wed, 30 Apr 2008) $');
714
715 -- Table for storing localisation data
716 CREATE TABLE l10n_cache (
717 -- Language code
718 lc_lang VARCHAR(32) NOT NULL,
719 -- Cache key
720 lc_key VARCHAR(255) NOT NULL,
721 -- Value
722 lc_value CLOB(16M) INLINE LENGTH 4096 NOT NULL
723 );
724 CREATE INDEX lc_lang_key ON l10n_cache (lc_lang, lc_key);
725
Wiklou, le Wiki du Wiklou