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


5e91102d3490cf0ddb1cbd71fe4f537cd81d843b
[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 -- Please make sure all dollar-quoting uses $mw$ at the start of the line
9 -- TODO: Change CHAR/SMALLINT to BOOL (still used in a non-bool fashion in PHP code)
10
11
12
13
14 CREATE SEQUENCE user_user_id_seq AS INTEGER START WITH 0 INCREMENT BY 1;
15 CREATE TABLE mwuser ( -- replace reserved word 'user'
16 user_id INTEGER NOT NULL PRIMARY KEY, -- DEFAULT nextval('user_user_id_seq'),
17 user_name VARCHAR(255) NOT NULL UNIQUE,
18 user_real_name VARCHAR(255),
19 user_password clob(1K),
20 user_newpassword clob(1K),
21 user_newpass_time TIMESTAMP,
22 user_token VARCHAR(255),
23 user_email VARCHAR(255),
24 user_email_token VARCHAR(255),
25 user_email_token_expires TIMESTAMP,
26 user_email_authenticated TIMESTAMP,
27 user_options CLOB(64K),
28 user_touched TIMESTAMP,
29 user_registration TIMESTAMP,
30 user_editcount INTEGER
31 );
32 CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
33
34 -- Create a dummy user to satisfy fk contraints especially with revisions
35 INSERT INTO mwuser
36 VALUES (NEXTVAL FOR user_user_id_seq,'Anonymous','', NULL,NULL,CURRENT_TIMESTAMP,NULL, NULL,NULL,NULL,NULL, NULL,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,0);
37
38 CREATE TABLE user_groups (
39 ug_user INTEGER REFERENCES mwuser(user_id) ON DELETE CASCADE,
40 ug_group VARCHAR(255) NOT NULL
41 );
42 CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
43
44 CREATE TABLE user_newtalk (
45 user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
46 user_ip VARCHAR(255),
47 user_last_timestamp TIMESTAMP
48 );
49 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
50 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
51
52
53 CREATE SEQUENCE page_page_id_seq;
54 CREATE TABLE page (
55 page_id INTEGER NOT NULL PRIMARY KEY, -- DEFAULT NEXT VALUE FOR user_user_id_seq,
56 page_namespace SMALLINT NOT NULL,
57 page_title VARCHAR(255) NOT NULL,
58 page_restrictions clob(1K),
59 page_counter BIGINT NOT NULL DEFAULT 0,
60 page_is_redirect SMALLINT NOT NULL DEFAULT 0,
61 page_is_new SMALLINT NOT NULL DEFAULT 0,
62 page_random NUMERIC(15,14) NOT NULL,
63 page_touched TIMESTAMP,
64 page_latest INTEGER NOT NULL, -- FK?
65 page_len INTEGER NOT NULL
66 );
67 CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
68 --CREATE INDEX page_main_title ON page (page_title) WHERE page_namespace = 0;
69 --CREATE INDEX page_talk_title ON page (page_title) WHERE page_namespace = 1;
70 --CREATE INDEX page_user_title ON page (page_title) WHERE page_namespace = 2;
71 --CREATE INDEX page_utalk_title ON page (page_title) WHERE page_namespace = 3;
72 --CREATE INDEX page_project_title ON page (page_title) WHERE page_namespace = 4;
73 CREATE INDEX page_random_idx ON page (page_random);
74 CREATE INDEX page_len_idx ON page (page_len);
75
76 --CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
77 --$mw$
78 --BEGIN
79 --DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
80 --RETURN NULL;
81 --END;
82 --$mw$;
83
84 --CREATE TRIGGER page_deleted AFTER DELETE ON page
85 -- FOR EACH ROW EXECUTE PROCEDURE page_deleted();
86
87 CREATE SEQUENCE rev_rev_id_val;
88 CREATE TABLE revision (
89 rev_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('rev_rev_id_val'),
90 rev_page INTEGER REFERENCES page (page_id) ON DELETE CASCADE,
91 rev_text_id INTEGER, -- FK
92 rev_comment clob(1K), -- changed from VARCHAR(255)
93 rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT,
94 rev_user_text VARCHAR(255) NOT NULL,
95 rev_timestamp TIMESTAMP NOT NULL,
96 rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
97 rev_deleted SMALLINT NOT NULL DEFAULT 0,
98 rev_len INTEGER,
99 rev_parent_id INTEGER
100 );
101 CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
102 CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
103 CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
104 CREATE INDEX rev_user_idx ON revision (rev_user);
105 CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
106
107
108 CREATE SEQUENCE text_old_id_val;
109 CREATE TABLE pagecontent ( -- replaces reserved word 'text'
110 old_id INTEGER NOT NULL,
111 --PRIMARY KEY DEFAULT nextval('text_old_id_val'),
112 old_text CLOB(16M),
113 old_flags clob(1K)
114 );
115
116 CREATE SEQUENCE pr_id_val;
117 CREATE TABLE page_restrictions (
118 pr_id INTEGER NOT NULL UNIQUE,
119 --DEFAULT nextval('pr_id_val'),
120 pr_page INTEGER NOT NULL
121 --(used to be nullable)
122 REFERENCES page (page_id) ON DELETE CASCADE,
123 pr_type VARCHAR(255) NOT NULL,
124 pr_level VARCHAR(255) NOT NULL,
125 pr_cascade SMALLINT NOT NULL,
126 pr_user INTEGER,
127 pr_expiry TIMESTAMP,
128 PRIMARY KEY (pr_page, pr_type)
129 );
130 --ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
131
132 CREATE TABLE page_props (
133 pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
134 pp_propname VARCHAR(255) NOT NULL,
135 pp_value CLOB(64K) NOT NULL,
136 PRIMARY KEY (pp_page,pp_propname)
137 );
138 --ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
139 CREATE INDEX page_props_propname ON page_props (pp_propname);
140
141
142
143 CREATE TABLE archive (
144 ar_namespace SMALLINT NOT NULL,
145 ar_title VARCHAR(255) NOT NULL,
146 ar_text CLOB(16M),
147 ar_page_id INTEGER,
148 ar_parent_id INTEGER,
149 ar_comment clob(1K),
150 ar_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
151 ar_user_text VARCHAR(255) NOT NULL,
152 ar_timestamp TIMESTAMP NOT NULL,
153 ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
154 ar_flags clob(1K),
155 ar_rev_id INTEGER,
156 ar_text_id INTEGER,
157 ar_deleted SMALLINT NOT NULL DEFAULT 0,
158 ar_len INTEGER
159 );
160 CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
161 CREATE INDEX archive_user_text ON archive (ar_user_text);
162
163
164
165 CREATE TABLE redirect (
166 rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
167 rd_namespace SMALLINT NOT NULL,
168 rd_title VARCHAR(255) NOT NULL
169 );
170 CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
171
172
173 CREATE TABLE pagelinks (
174 pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
175 pl_namespace SMALLINT NOT NULL,
176 pl_title VARCHAR(255) NOT NULL
177 );
178 CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
179
180 CREATE TABLE templatelinks (
181 tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
182 tl_namespace SMALLINT NOT NULL,
183 tl_title VARCHAR(255) NOT NULL
184 );
185 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
186
187 CREATE TABLE imagelinks (
188 il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
189 il_to VARCHAR(255) NOT NULL
190 );
191 CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
192
193 CREATE TABLE categorylinks (
194 cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
195 cl_to VARCHAR(255) NOT NULL,
196 cl_sortkey VARCHAR(255),
197 cl_timestamp TIMESTAMP NOT NULL
198 );
199 CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
200 CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
201
202
203
204 CREATE TABLE externallinks (
205 el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
206 el_to VARCHAR(255) NOT NULL,
207 el_index VARCHAR(255) NOT NULL
208 );
209 CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
210 CREATE INDEX externallinks_index ON externallinks (el_index);
211
212 CREATE TABLE langlinks (
213 ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
214 ll_lang VARCHAR(255),
215 ll_title VARCHAR(255)
216 );
217 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
218 CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);
219
220
221 CREATE TABLE site_stats (
222 ss_row_id INTEGER NOT NULL UNIQUE,
223 ss_total_views INTEGER DEFAULT 0,
224 ss_total_edits INTEGER DEFAULT 0,
225 ss_good_articles INTEGER DEFAULT 0,
226 ss_total_pages INTEGER DEFAULT -1,
227 ss_users INTEGER DEFAULT -1,
228 ss_admins INTEGER DEFAULT -1,
229 ss_images INTEGER DEFAULT 0
230 );
231
232 CREATE TABLE hitcounter (
233 hc_id BIGINT NOT NULL
234 );
235
236 CREATE SEQUENCE ipblocks_ipb_id_val;
237 CREATE TABLE ipblocks (
238 ipb_id INTEGER NOT NULL PRIMARY KEY,
239 --DEFAULT nextval('ipblocks_ipb_id_val'),
240 ipb_address VARCHAR(255),
241 ipb_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
242 ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
243 ipb_by_text VARCHAR(255) NOT NULL DEFAULT '',
244 ipb_reason VARCHAR(255) NOT NULL,
245 ipb_timestamp TIMESTAMP NOT NULL,
246 ipb_auto SMALLINT NOT NULL DEFAULT 0,
247 ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
248 ipb_create_account SMALLINT NOT NULL DEFAULT 1,
249 ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
250 ipb_expiry TIMESTAMP NOT NULL,
251 ipb_range_start VARCHAR(255),
252 ipb_range_end VARCHAR(255),
253 ipb_deleted SMALLINT NOT NULL DEFAULT 0,
254 ipb_block_email SMALLINT NOT NULL DEFAULT 0
255
256 );
257 CREATE INDEX ipb_address ON ipblocks (ipb_address);
258 CREATE INDEX ipb_user ON ipblocks (ipb_user);
259 CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
260
261
262
263 CREATE TABLE image (
264 img_name VARCHAR(255) NOT NULL PRIMARY KEY,
265 img_size INTEGER NOT NULL,
266 img_width INTEGER NOT NULL,
267 img_height INTEGER NOT NULL,
268 img_metadata CLOB(16M) NOT NULL DEFAULT '',
269 img_bits SMALLINT,
270 img_media_type VARCHAR(255),
271 img_major_mime VARCHAR(255) DEFAULT 'unknown',
272 img_minor_mime VARCHAR(255) DEFAULT 'unknown',
273 img_description clob(1K) NOT NULL DEFAULT '',
274 img_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
275 img_user_text VARCHAR(255) NOT NULL DEFAULT '',
276 img_timestamp TIMESTAMP,
277 img_sha1 VARCHAR(255) NOT NULL DEFAULT ''
278 );
279 CREATE INDEX img_size_idx ON image (img_size);
280 CREATE INDEX img_timestamp_idx ON image (img_timestamp);
281 CREATE INDEX img_sha1 ON image (img_sha1);
282
283 CREATE TABLE oldimage (
284 oi_name VARCHAR(255) NOT NULL,
285 oi_archive_name VARCHAR(255) NOT NULL,
286 oi_size INTEGER NOT NULL,
287 oi_width INTEGER NOT NULL,
288 oi_height INTEGER NOT NULL,
289 oi_bits SMALLINT NOT NULL,
290 oi_description clob(1K),
291 oi_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
292 oi_user_text VARCHAR(255) NOT NULL,
293 oi_timestamp TIMESTAMP NOT NULL,
294 oi_metadata CLOB(16M) NOT NULL DEFAULT '',
295 oi_media_type VARCHAR(255) ,
296 oi_major_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
297 oi_minor_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
298 oi_deleted SMALLINT NOT NULL DEFAULT 0,
299 oi_sha1 VARCHAR(255) NOT NULL DEFAULT '',
300 FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
301 );
302 --ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE;
303 CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
304 CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
305 CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
306
307
308 CREATE SEQUENCE filearchive_fa_id_seq;
309 CREATE TABLE filearchive (
310 fa_id INTEGER NOT NULL PRIMARY KEY,
311 --PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
312 fa_name VARCHAR(255) NOT NULL,
313 fa_archive_name VARCHAR(255),
314 fa_storage_group VARCHAR(255),
315 fa_storage_key VARCHAR(255),
316 fa_deleted_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
317 fa_deleted_timestamp TIMESTAMP NOT NULL,
318 fa_deleted_reason VARCHAR(255),
319 fa_size INTEGER NOT NULL,
320 fa_width INTEGER NOT NULL,
321 fa_height INTEGER NOT NULL,
322 fa_metadata CLOB(16M) NOT NULL DEFAULT '',
323 fa_bits SMALLINT,
324 fa_media_type VARCHAR(255),
325 fa_major_mime VARCHAR(255) DEFAULT 'unknown',
326 fa_minor_mime VARCHAR(255) DEFAULT 'unknown',
327 fa_description clob(1K) NOT NULL,
328 fa_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
329 fa_user_text VARCHAR(255) NOT NULL,
330 fa_timestamp TIMESTAMP,
331 fa_deleted SMALLINT NOT NULL DEFAULT 0
332 );
333 CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
334 CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
335 CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
336 CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
337
338 CREATE SEQUENCE rc_rc_id_seq;
339 CREATE TABLE recentchanges (
340 rc_id INTEGER NOT NULL PRIMARY KEY,
341 --PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
342 rc_timestamp TIMESTAMP NOT NULL,
343 rc_cur_time TIMESTAMP NOT NULL,
344 rc_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
345 rc_user_text VARCHAR(255) NOT NULL,
346 rc_namespace SMALLINT NOT NULL,
347 rc_title VARCHAR(255) NOT NULL,
348 rc_comment VARCHAR(255),
349 rc_minor SMALLINT NOT NULL DEFAULT 0,
350 rc_bot SMALLINT NOT NULL DEFAULT 0,
351 rc_new SMALLINT NOT NULL DEFAULT 0,
352 rc_cur_id INTEGER REFERENCES page(page_id) ON DELETE SET NULL,
353 rc_this_oldid INTEGER NOT NULL,
354 rc_last_oldid INTEGER NOT NULL,
355 rc_type SMALLINT NOT NULL DEFAULT 0,
356 rc_moved_to_ns SMALLINT,
357 rc_moved_to_title VARCHAR(255),
358 rc_patrolled SMALLINT NOT NULL DEFAULT 0,
359 rc_ip VARCHAR(255), -- was CIDR type
360 rc_old_len INTEGER,
361 rc_new_len INTEGER,
362 rc_deleted SMALLINT NOT NULL DEFAULT 0,
363 rc_logid INTEGER NOT NULL DEFAULT 0,
364 rc_log_type VARCHAR(255),
365 rc_log_action VARCHAR(255),
366 rc_params CLOB(64K)
367 );
368 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
369 CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
370 CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
371 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
372 CREATE INDEX rc_ip ON recentchanges (rc_ip);
373
374
375
376 CREATE TABLE watchlist (
377 wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
378 wl_namespace SMALLINT NOT NULL DEFAULT 0,
379 wl_title VARCHAR(255) NOT NULL,
380 wl_notificationtimestamp TIMESTAMP
381 );
382 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
383
384
385 CREATE TABLE math (
386 math_inputhash VARGRAPHIC(255) NOT NULL UNIQUE,
387 math_outputhash VARGRAPHIC(255) NOT NULL,
388 math_html_conservativeness SMALLINT NOT NULL,
389 math_html VARCHAR(255),
390 math_mathml VARCHAR(255)
391 );
392
393
394 CREATE TABLE interwiki (
395 iw_prefix VARCHAR(255) NOT NULL UNIQUE,
396 iw_url CLOB(64K) NOT NULL,
397 iw_local SMALLINT NOT NULL,
398 iw_trans SMALLINT NOT NULL DEFAULT 0
399 );
400
401
402 CREATE TABLE querycache (
403 qc_type VARCHAR(255) NOT NULL,
404 qc_value INTEGER NOT NULL,
405 qc_namespace SMALLINT NOT NULL,
406 qc_title VARCHAR(255) NOT NULL
407 );
408 CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
409
410
411
412 CREATE TABLE querycache_info (
413 qci_type VARCHAR(255) UNIQUE NOT NULL,
414 qci_timestamp TIMESTAMP
415 );
416
417
418 CREATE TABLE querycachetwo (
419 qcc_type VARCHAR(255) NOT NULL,
420 qcc_value INTEGER NOT NULL DEFAULT 0,
421 qcc_namespace INTEGER NOT NULL DEFAULT 0,
422 qcc_title VARCHAR(255) NOT NULL DEFAULT '',
423 qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
424 qcc_titletwo VARCHAR(255) NOT NULL DEFAULT ''
425 );
426 CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
427 CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
428 CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
429
430 CREATE TABLE objectcache (
431 keyname VARCHAR(255) NOT NULL UNIQUE, -- was nullable
432 value CLOB(16M) NOT NULL DEFAULT '',
433 exptime TIMESTAMP NOT NULL
434 );
435 CREATE INDEX objectcacache_exptime ON objectcache (exptime);
436
437
438
439 CREATE TABLE transcache (
440 tc_url VARCHAR(255) NOT NULL UNIQUE,
441 tc_contents VARCHAR(255) NOT NULL,
442 tc_time TIMESTAMP NOT NULL
443 );
444
445 CREATE SEQUENCE log_log_id_seq;
446 CREATE TABLE logging (
447 log_id INTEGER NOT NULL PRIMARY KEY,
448 --PRIMARY KEY DEFAULT nextval('log_log_id_seq'),
449 log_type VARCHAR(255) NOT NULL,
450 log_action VARCHAR(255) NOT NULL,
451 log_timestamp TIMESTAMP NOT NULL,
452 log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
453 log_namespace SMALLINT NOT NULL,
454 log_title VARCHAR(255) NOT NULL,
455 log_comment VARCHAR(255),
456 log_params CLOB(64K),
457 log_deleted SMALLINT NOT NULL DEFAULT 0
458 );
459 CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
460 CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
461 CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
462
463 CREATE SEQUENCE trackbacks_tb_id_seq;
464 CREATE TABLE trackbacks (
465 tb_id INTEGER NOT NULL PRIMARY KEY,
466 --PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
467 tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
468 tb_title VARCHAR(255) NOT NULL,
469 tb_url CLOB(64K) NOT NULL,
470 tb_ex VARCHAR(255),
471 tb_name VARCHAR(255)
472 );
473 CREATE INDEX trackback_page ON trackbacks (tb_page);
474
475
476 CREATE SEQUENCE job_job_id_seq;
477 CREATE TABLE job (
478 job_id INTEGER NOT NULL PRIMARY KEY,
479 --PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
480 job_cmd VARCHAR(255) NOT NULL,
481 job_namespace SMALLINT NOT NULL,
482 job_title VARCHAR(255) NOT NULL,
483 job_params CLOB(64K) NOT NULL
484 );
485 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
486
487
488
489 -- Postgres' Tsearch2 dropped
490 --ALTER TABLE page ADD titlevector tsvector;
491 --CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
492 --$mw$
493 --BEGIN
494 --IF TG_OP = 'INSERT' THEN
495 -- NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
496 --ELSIF NEW.page_title != OLD.page_title THEN
497 -- NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
498 --END IF;
499 --RETURN NEW;
500 --END;
501 --$mw$;
502
503 --CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
504 -- FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
505
506
507 --ALTER TABLE pagecontent ADD textvector tsvector;
508 --CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
509 --$mw$
510 --BEGIN
511 --IF TG_OP = 'INSERT' THEN
512 -- NEW.textvector = to_tsvector('default',NEW.old_text);
513 --ELSIF NEW.old_text != OLD.old_text THEN
514 -- NEW.textvector := to_tsvector('default',NEW.old_text);
515 --END IF;
516 --RETURN NEW;
517 --END;
518 --$mw$;
519
520 --CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
521 -- FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
522
523 -- These are added by the setup script due to version compatibility issues
524 -- If using 8.1, we switch from "gin" to "gist"
525
526 --CREATE INDEX ts2_page_title ON page USING gin(titlevector);
527 --CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
528
529 --TODO
530 --CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
531 --$mw$
532 -- INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
533 -- SELECT 1;
534 --$mw$;
535
536 -- hack implementation
537 -- should be replaced with OmniFind, Contains(), etc
538 CREATE TABLE searchindex (
539 si_page int NOT NULL,
540 si_title varchar(255) NOT NULL default '',
541 si_text clob NOT NULL
542 );
543
544 -- This table is not used unless profiling is turned on
545 CREATE TABLE profiling (
546 pf_count INTEGER NOT NULL DEFAULT 0,
547 pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
548 pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,
549 pf_name VARCHAR(255) NOT NULL,
550 pf_server VARCHAR(255)
551 );
552 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
553
554 CREATE TABLE protected_titles (
555 pt_namespace SMALLINT NOT NULL,
556 pt_title VARCHAR(255) NOT NULL,
557 pt_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
558 pt_reason clob(1K),
559 pt_timestamp TIMESTAMP NOT NULL,
560 pt_expiry TIMESTAMP ,
561 pt_create_perm VARCHAR(255) NOT NULL DEFAULT ''
562 );
563 CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
564
565
566
567 CREATE TABLE updatelog (
568 ul_key VARCHAR(255) NOT NULL PRIMARY KEY
569 );
570
571 CREATE SEQUENCE category_id_seq;
572 CREATE TABLE category (
573 cat_id INTEGER NOT NULL PRIMARY KEY,
574 --PRIMARY KEY DEFAULT nextval('category_id_seq'),
575 cat_title VARCHAR(255) NOT NULL,
576 cat_pages INTEGER NOT NULL DEFAULT 0,
577 cat_subcats INTEGER NOT NULL DEFAULT 0,
578 cat_files INTEGER NOT NULL DEFAULT 0,
579 cat_hidden SMALLINT NOT NULL DEFAULT 0
580 );
581 CREATE UNIQUE INDEX category_title ON category(cat_title);
582 CREATE INDEX category_pages ON category(cat_pages);
583
584 CREATE TABLE mediawiki_version (
585 type VARCHAR(255) NOT NULL,
586 mw_version VARCHAR(255) NOT NULL,
587 notes VARCHAR(255) ,
588
589 pg_version VARCHAR(255) ,
590 pg_dbname VARCHAR(255) ,
591 pg_user VARCHAR(255) ,
592 pg_port VARCHAR(255) ,
593 mw_schema VARCHAR(255) ,
594 ts2_schema VARCHAR(255) ,
595 ctype VARCHAR(255) ,
596
597 sql_version VARCHAR(255) ,
598 sql_date VARCHAR(255) ,
599 cdate TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
600 );
601
602 INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date)
603 VALUES ('Creation','??','$LastChangedRevision: 34049 $','$LastChangedDate: 2008-04-30 10:20:36 -0400 (Wed, 30 Apr 2008) $');
604
Wiklou, le Wiki du Wiklou