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 -- This is the PostgreSQL version.
5 -- For information about each table, please see the notes in maintenance/tables.sql
6 -- Please make sure all dollar-quoting uses $mw$ at the start of the line
7 -- TODO: Change CHAR/SMALLINT to BOOL (still used in a non-bool fashion in PHP code)
10 SET client_min_messages = 'ERROR';
12 DROP SEQUENCE IF EXISTS user_user_id_seq CASCADE;
13 DROP SEQUENCE IF EXISTS page_page_id_seq CASCADE;
14 DROP SEQUENCE IF EXISTS revision_rev_id_seq CASCADE;
15 DROP SEQUENCE IF EXISTS text_old_id_seq CASCADE;
16 DROP SEQUENCE IF EXISTS page_restrictions_pr_id_seq CASCADE;
17 DROP SEQUENCE IF EXISTS ipblocks_ipb_id_seq CASCADE;
18 DROP SEQUENCE IF EXISTS filearchive_fa_id_seq CASCADE;
19 DROP SEQUENCE IF EXISTS uploadstash_us_id_seq CASCADE;
20 DROP SEQUENCE IF EXISTS recentchanges_rc_id_seq CASCADE;
21 DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE;
22 DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE;
23 DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE;
24 DROP SEQUENCE IF EXISTS archive_ar_id_seq CASCADE;
25 DROP SEQUENCE IF EXISTS externallinks_el_id_seq CASCADE;
26 DROP SEQUENCE IF EXISTS sites_site_id_seq CASCADE;
27 DROP FUNCTION IF EXISTS page_deleted() CASCADE;
28 DROP FUNCTION IF EXISTS ts2_page_title() CASCADE;
29 DROP FUNCTION IF EXISTS ts2_page_text() CASCADE;
30 DROP FUNCTION IF EXISTS add_interwiki(TEXT,INT,SMALLINT) CASCADE;
31 DROP TYPE IF EXISTS media_type CASCADE;
33 CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
34 CREATE TABLE mwuser ( -- replace reserved word 'user'
35 user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
36 user_name TEXT NOT NULL UNIQUE,
39 user_newpassword TEXT,
40 user_newpass_time TIMESTAMPTZ,
43 user_email_token TEXT,
44 user_email_token_expires TIMESTAMPTZ,
45 user_email_authenticated TIMESTAMPTZ,
46 user_touched TIMESTAMPTZ,
47 user_registration TIMESTAMPTZ,
48 user_editcount INTEGER,
49 user_password_expires TIMESTAMPTZ NULL
51 CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
53 -- Create a dummy user to satisfy fk contraints especially with revisions
55 VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
57 CREATE TABLE user_groups (
58 ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
59 ug_group TEXT NOT NULL
61 CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
63 CREATE TABLE user_former_groups (
64 ufg_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
65 ufg_group TEXT NOT NULL
67 CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group);
69 CREATE TABLE user_newtalk (
70 user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
72 user_last_timestamp TIMESTAMPTZ
74 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
75 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
78 CREATE SEQUENCE page_page_id_seq;
80 page_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('page_page_id_seq'),
81 page_namespace SMALLINT NOT NULL,
82 page_title TEXT NOT NULL,
83 page_restrictions TEXT,
84 page_counter BIGINT NOT NULL DEFAULT 0,
85 page_is_redirect SMALLINT NOT NULL DEFAULT 0,
86 page_is_new SMALLINT NOT NULL DEFAULT 0,
87 page_random NUMERIC(15,14) NOT NULL DEFAULT RANDOM(),
88 page_touched TIMESTAMPTZ,
89 page_links_updated TIMESTAMPTZ NULL,
90 page_latest INTEGER NOT NULL, -- FK?
91 page_len INTEGER NOT NULL,
92 page_content_model TEXT
94 CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
95 CREATE INDEX page_main_title ON page (page_title text_pattern_ops) WHERE page_namespace = 0;
96 CREATE INDEX page_talk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 1;
97 CREATE INDEX page_user_title ON page (page_title text_pattern_ops) WHERE page_namespace = 2;
98 CREATE INDEX page_utalk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 3;
99 CREATE INDEX page_project_title ON page (page_title text_pattern_ops) WHERE page_namespace = 4;
100 CREATE INDEX page_mediawiki_title ON page (page_title text_pattern_ops) WHERE page_namespace = 8;
101 CREATE INDEX page_random_idx ON page (page_random);
102 CREATE INDEX page_len_idx ON page (page_len);
104 CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
107 DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
112 CREATE TRIGGER page_deleted AFTER DELETE ON page
113 FOR EACH ROW EXECUTE PROCEDURE page_deleted();
115 CREATE SEQUENCE revision_rev_id_seq;
116 CREATE TABLE revision (
117 rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('revision_rev_id_seq'),
118 rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
119 rev_text_id INTEGER NULL, -- FK
121 rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
122 rev_user_text TEXT NOT NULL,
123 rev_timestamp TIMESTAMPTZ NOT NULL,
124 rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
125 rev_deleted SMALLINT NOT NULL DEFAULT 0,
126 rev_len INTEGER NULL,
127 rev_parent_id INTEGER NULL,
128 rev_sha1 TEXT NOT NULL DEFAULT '',
129 rev_content_model TEXT,
130 rev_content_format TEXT
132 CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
133 CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
134 CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
135 CREATE INDEX rev_user_idx ON revision (rev_user);
136 CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
139 CREATE SEQUENCE text_old_id_seq;
140 CREATE TABLE pagecontent ( -- replaces reserved word 'text'
141 old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_seq'),
147 CREATE SEQUENCE page_restrictions_pr_id_seq;
148 CREATE TABLE page_restrictions (
149 pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'),
150 pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
151 pr_type TEXT NOT NULL,
152 pr_level TEXT NOT NULL,
153 pr_cascade SMALLINT NOT NULL,
154 pr_user INTEGER NULL,
155 pr_expiry TIMESTAMPTZ NULL
157 ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
159 CREATE TABLE page_props (
160 pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
161 pp_propname TEXT NOT NULL,
162 pp_value TEXT NOT NULL,
165 ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
166 CREATE INDEX page_props_propname ON page_props (pp_propname);
167 CREATE UNIQUE INDEX pp_propname_page ON page_props (pp_propname,pp_page);
168 CREATE INDEX pp_propname_sortkey_page ON page_props (pp_propname, pp_sortkey, pp_page) WHERE (pp_sortkey IS NOT NULL);
170 CREATE SEQUENCE archive_ar_id_seq;
171 CREATE TABLE archive (
172 ar_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('archive_ar_id_seq'),
173 ar_namespace SMALLINT NOT NULL,
174 ar_title TEXT NOT NULL,
175 ar_text TEXT, -- technically should be bytea, but not used anymore
176 ar_page_id INTEGER NULL,
177 ar_parent_id INTEGER NULL,
178 ar_sha1 TEXT NOT NULL DEFAULT '',
180 ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
181 ar_user_text TEXT NOT NULL,
182 ar_timestamp TIMESTAMPTZ NOT NULL,
183 ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
187 ar_deleted SMALLINT NOT NULL DEFAULT 0,
189 ar_content_model TEXT,
190 ar_content_format TEXT
192 CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
193 CREATE INDEX archive_user_text ON archive (ar_user_text);
196 CREATE TABLE redirect (
197 rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
198 rd_namespace SMALLINT NOT NULL,
199 rd_title TEXT NOT NULL,
200 rd_interwiki TEXT NULL,
201 rd_fragment TEXT NULL
203 CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
206 CREATE TABLE pagelinks (
207 pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
208 pl_namespace SMALLINT NOT NULL,
209 pl_title TEXT NOT NULL
211 CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
212 CREATE INDEX pagelinks_title ON pagelinks (pl_title);
214 CREATE TABLE templatelinks (
215 tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
216 tl_namespace SMALLINT NOT NULL,
217 tl_title TEXT NOT NULL
219 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
220 CREATE INDEX templatelinks_from ON templatelinks (tl_from);
222 CREATE TABLE imagelinks (
223 il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
226 CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
228 CREATE TABLE categorylinks (
229 cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
231 cl_sortkey TEXT NULL,
232 cl_timestamp TIMESTAMPTZ NOT NULL,
233 cl_sortkey_prefix TEXT NOT NULL DEFAULT '',
234 cl_collation TEXT NOT NULL DEFAULT 0,
235 cl_type TEXT NOT NULL DEFAULT 'page'
237 CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
238 CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
240 CREATE SEQUENCE externallinks_el_id_seq;
241 CREATE TABLE externallinks (
242 el_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'),
243 el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
245 el_index TEXT NOT NULL
247 CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
248 CREATE INDEX externallinks_index ON externallinks (el_index);
250 CREATE TABLE langlinks (
251 ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
255 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
256 CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);
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_active_users INTEGER DEFAULT -1,
267 ss_admins INTEGER DEFAULT -1,
268 ss_images INTEGER DEFAULT 0
271 CREATE TABLE hitcounter (
272 hc_id BIGINT NOT NULL
276 CREATE SEQUENCE ipblocks_ipb_id_seq;
277 CREATE TABLE ipblocks (
278 ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'),
279 ipb_address TEXT NULL,
280 ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
281 ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
282 ipb_by_text TEXT NOT NULL DEFAULT '',
283 ipb_reason TEXT NOT NULL,
284 ipb_timestamp TIMESTAMPTZ NOT NULL,
285 ipb_auto SMALLINT NOT NULL DEFAULT 0,
286 ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
287 ipb_create_account SMALLINT NOT NULL DEFAULT 1,
288 ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
289 ipb_expiry TIMESTAMPTZ NOT NULL,
290 ipb_range_start TEXT,
292 ipb_deleted SMALLINT NOT NULL DEFAULT 0,
293 ipb_block_email SMALLINT NOT NULL DEFAULT 0,
294 ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0,
295 ipb_parent_block_id INTEGER NULL REFERENCES ipblocks(ipb_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
298 CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
299 CREATE INDEX ipb_user ON ipblocks (ipb_user);
300 CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
301 CREATE INDEX ipb_parent_block_id ON ipblocks (ipb_parent_block_id);
305 img_name TEXT NOT NULL PRIMARY KEY,
306 img_size INTEGER NOT NULL,
307 img_width INTEGER NOT NULL,
308 img_height INTEGER NOT NULL,
309 img_metadata BYTEA NOT NULL DEFAULT '',
312 img_major_mime TEXT DEFAULT 'unknown',
313 img_minor_mime TEXT DEFAULT 'unknown',
314 img_description TEXT NOT NULL,
315 img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
316 img_user_text TEXT NOT NULL,
317 img_timestamp TIMESTAMPTZ,
318 img_sha1 TEXT NOT NULL DEFAULT ''
320 CREATE INDEX img_size_idx ON image (img_size);
321 CREATE INDEX img_timestamp_idx ON image (img_timestamp);
322 CREATE INDEX img_sha1 ON image (img_sha1);
324 CREATE TABLE oldimage (
325 oi_name TEXT NOT NULL,
326 oi_archive_name TEXT NOT NULL,
327 oi_size INTEGER NOT NULL,
328 oi_width INTEGER NOT NULL,
329 oi_height INTEGER NOT NULL,
330 oi_bits SMALLINT NULL,
332 oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
333 oi_user_text TEXT NOT NULL,
334 oi_timestamp TIMESTAMPTZ NULL,
335 oi_metadata BYTEA NOT NULL DEFAULT '',
336 oi_media_type TEXT NULL,
337 oi_major_mime TEXT NULL DEFAULT 'unknown',
338 oi_minor_mime TEXT NULL DEFAULT 'unknown',
339 oi_deleted SMALLINT NOT NULL DEFAULT 0,
340 oi_sha1 TEXT NOT NULL DEFAULT ''
342 ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
343 CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
344 CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
345 CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
348 CREATE SEQUENCE filearchive_fa_id_seq;
349 CREATE TABLE filearchive (
350 fa_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
351 fa_name TEXT NOT NULL,
352 fa_archive_name TEXT,
353 fa_storage_group TEXT,
355 fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
356 fa_deleted_timestamp TIMESTAMPTZ NOT NULL,
357 fa_deleted_reason TEXT,
358 fa_size INTEGER NOT NULL,
359 fa_width INTEGER NOT NULL,
360 fa_height INTEGER NOT NULL,
361 fa_metadata BYTEA NOT NULL DEFAULT '',
364 fa_major_mime TEXT DEFAULT 'unknown',
365 fa_minor_mime TEXT DEFAULT 'unknown',
366 fa_description TEXT NOT NULL,
367 fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
368 fa_user_text TEXT NOT NULL,
369 fa_timestamp TIMESTAMPTZ,
370 fa_deleted SMALLINT NOT NULL DEFAULT 0,
371 fa_sha1 TEXT NOT NULL DEFAULT ''
373 CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
374 CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
375 CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
376 CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
377 CREATE INDEX fa_sha1 ON filearchive (fa_sha1);
379 CREATE SEQUENCE uploadstash_us_id_seq;
380 CREATE TYPE media_type AS ENUM ('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE');
382 CREATE TABLE uploadstash (
383 us_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('uploadstash_us_id_seq'),
390 us_timestamp TIMESTAMPTZ,
392 us_chunk_inx INTEGER NULL,
396 us_media_type media_type DEFAULT NULL,
397 us_image_width INTEGER,
398 us_image_height INTEGER,
399 us_image_bits SMALLINT
402 CREATE INDEX us_user_idx ON uploadstash (us_user);
403 CREATE UNIQUE INDEX us_key_idx ON uploadstash (us_key);
404 CREATE INDEX us_timestamp_idx ON uploadstash (us_timestamp);
407 CREATE SEQUENCE recentchanges_rc_id_seq;
408 CREATE TABLE recentchanges (
409 rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'),
410 rc_timestamp TIMESTAMPTZ NOT NULL,
411 rc_cur_time TIMESTAMPTZ NULL,
412 rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
413 rc_user_text TEXT NOT NULL,
414 rc_namespace SMALLINT NOT NULL,
415 rc_title TEXT NOT NULL,
417 rc_minor SMALLINT NOT NULL DEFAULT 0,
418 rc_bot SMALLINT NOT NULL DEFAULT 0,
419 rc_new SMALLINT NOT NULL DEFAULT 0,
420 rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
421 rc_this_oldid INTEGER NOT NULL,
422 rc_last_oldid INTEGER NOT NULL,
423 rc_type SMALLINT NOT NULL DEFAULT 0,
424 rc_source TEXT NOT NULL,
425 rc_patrolled SMALLINT NOT NULL DEFAULT 0,
429 rc_deleted SMALLINT NOT NULL DEFAULT 0,
430 rc_logid INTEGER NOT NULL DEFAULT 0,
435 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
436 CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0;
437 CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
438 CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
439 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
440 CREATE INDEX rc_ip ON recentchanges (rc_ip);
443 CREATE TABLE watchlist (
444 wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
445 wl_namespace SMALLINT NOT NULL DEFAULT 0,
446 wl_title TEXT NOT NULL,
447 wl_notificationtimestamp TIMESTAMPTZ
449 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
450 CREATE INDEX wl_user ON watchlist (wl_user);
451 CREATE INDEX wl_user_notificationtimestamp ON watchlist (wl_user, wl_notificationtimestamp);
454 CREATE TABLE interwiki (
455 iw_prefix TEXT NOT NULL UNIQUE,
456 iw_url TEXT NOT NULL,
457 iw_local SMALLINT NOT NULL,
458 iw_trans SMALLINT NOT NULL DEFAULT 0,
459 iw_api TEXT NOT NULL DEFAULT '',
460 iw_wikiid TEXT NOT NULL DEFAULT ''
464 CREATE TABLE querycache (
465 qc_type TEXT NOT NULL,
466 qc_value INTEGER NOT NULL,
467 qc_namespace SMALLINT NOT NULL,
468 qc_title TEXT NOT NULL
470 CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
472 CREATE TABLE querycache_info (
473 qci_type TEXT UNIQUE,
474 qci_timestamp TIMESTAMPTZ NULL
477 CREATE TABLE querycachetwo (
478 qcc_type TEXT NOT NULL,
479 qcc_value INTEGER NOT NULL DEFAULT 0,
480 qcc_namespace INTEGER NOT NULL DEFAULT 0,
481 qcc_title TEXT NOT NULL DEFAULT '',
482 qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
483 qcc_titletwo TEXT NOT NULL DEFAULT ''
485 CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
486 CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
487 CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
489 CREATE TABLE objectcache (
491 value BYTEA NOT NULL DEFAULT '',
492 exptime TIMESTAMPTZ NOT NULL
494 CREATE INDEX objectcacache_exptime ON objectcache (exptime);
496 CREATE TABLE transcache (
497 tc_url TEXT NOT NULL UNIQUE,
498 tc_contents TEXT NOT NULL,
499 tc_time TIMESTAMPTZ NOT NULL
503 CREATE SEQUENCE logging_log_id_seq;
504 CREATE TABLE logging (
505 log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq'),
506 log_type TEXT NOT NULL,
507 log_action TEXT NOT NULL,
508 log_timestamp TIMESTAMPTZ NOT NULL,
509 log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
510 log_namespace SMALLINT NOT NULL,
511 log_title TEXT NOT NULL,
514 log_deleted SMALLINT NOT NULL DEFAULT 0,
515 log_user_text TEXT NOT NULL DEFAULT '',
518 CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
519 CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
520 CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
521 CREATE INDEX logging_times ON logging (log_timestamp);
522 CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp);
523 CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp);
524 CREATE INDEX logging_user_text_type_time ON logging (log_user_text, log_type, log_timestamp);
525 CREATE INDEX logging_user_text_time ON logging (log_user_text, log_timestamp);
527 CREATE TABLE log_search (
528 ls_field TEXT NOT NULL,
529 ls_value TEXT NOT NULL,
530 ls_log_id INTEGER NOT NULL DEFAULT 0,
531 PRIMARY KEY (ls_field,ls_value,ls_log_id)
533 CREATE INDEX ls_log_id ON log_search (ls_log_id);
536 CREATE SEQUENCE job_job_id_seq;
538 job_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
539 job_cmd TEXT NOT NULL,
540 job_namespace SMALLINT NOT NULL,
541 job_title TEXT NOT NULL,
542 job_timestamp TIMESTAMPTZ,
543 job_params TEXT NOT NULL,
544 job_random INTEGER NOT NULL DEFAULT 0,
545 job_attempts INTEGER NOT NULL DEFAULT 0,
546 job_token TEXT NOT NULL DEFAULT '',
547 job_token_timestamp TIMESTAMPTZ,
548 job_sha1 TEXT NOT NULL DEFAULT ''
550 CREATE INDEX job_sha1 ON job (job_sha1);
551 CREATE INDEX job_cmd_token ON job (job_cmd, job_token, job_random);
552 CREATE INDEX job_cmd_token_id ON job (job_cmd, job_token, job_id);
553 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
554 CREATE INDEX job_timestamp_idx ON job (job_timestamp);
556 -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
557 -- Version 8.3 or higher only. Previous versions would need another parmeter for to_tsvector.
558 -- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.
560 ALTER TABLE page ADD titlevector tsvector;
561 CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
564 IF TG_OP = 'INSERT' THEN
565 NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' '));
566 ELSIF NEW.page_title != OLD.page_title THEN
567 NEW.titlevector := to_tsvector(REPLACE(NEW.page_title,'/',' '));
573 CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
574 FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
577 ALTER TABLE pagecontent ADD textvector tsvector;
578 CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
581 IF TG_OP = 'INSERT' THEN
582 NEW.textvector = to_tsvector(NEW.old_text);
583 ELSIF NEW.old_text != OLD.old_text THEN
584 NEW.textvector := to_tsvector(NEW.old_text);
590 CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
591 FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
593 -- These are added by the setup script due to version compatibility issues
594 -- If using 8.1, we switch from "gin" to "gist"
596 CREATE INDEX ts2_page_title ON page USING gin(titlevector);
597 CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
599 CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
601 INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
605 -- This table is not used unless profiling is turned on
606 CREATE TABLE profiling (
607 pf_count INTEGER NOT NULL DEFAULT 0,
608 pf_time FLOAT NOT NULL DEFAULT 0,
609 pf_memory FLOAT NOT NULL DEFAULT 0,
610 pf_name TEXT NOT NULL,
613 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
615 CREATE TABLE protected_titles (
616 pt_namespace SMALLINT NOT NULL,
617 pt_title TEXT NOT NULL,
618 pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
620 pt_timestamp TIMESTAMPTZ NOT NULL,
621 pt_expiry TIMESTAMPTZ NULL,
622 pt_create_perm TEXT NOT NULL DEFAULT ''
624 CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
627 CREATE TABLE updatelog (
628 ul_key TEXT NOT NULL PRIMARY KEY,
633 CREATE SEQUENCE category_cat_id_seq;
634 CREATE TABLE category (
635 cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_cat_id_seq'),
636 cat_title TEXT NOT NULL,
637 cat_pages INTEGER NOT NULL DEFAULT 0,
638 cat_subcats INTEGER NOT NULL DEFAULT 0,
639 cat_files INTEGER NOT NULL DEFAULT 0,
640 cat_hidden SMALLINT NOT NULL DEFAULT 0
642 CREATE UNIQUE INDEX category_title ON category(cat_title);
643 CREATE INDEX category_pages ON category(cat_pages);
645 CREATE TABLE change_tag (
646 ct_rc_id INTEGER NULL,
647 ct_log_id INTEGER NULL,
648 ct_rev_id INTEGER NULL,
649 ct_tag TEXT NOT NULL,
652 CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag);
653 CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag);
654 CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag);
655 CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
657 CREATE TABLE tag_summary (
658 ts_rc_id INTEGER NULL,
659 ts_log_id INTEGER NULL,
660 ts_rev_id INTEGER NULL,
661 ts_tags TEXT NOT NULL
663 CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id);
664 CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id);
665 CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id);
667 CREATE TABLE valid_tag (
668 vt_tag TEXT NOT NULL PRIMARY KEY
671 CREATE TABLE user_properties (
672 up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
673 up_property TEXT NOT NULL,
676 CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);
677 CREATE INDEX user_properties_property ON user_properties (up_property);
679 CREATE TABLE l10n_cache (
680 lc_lang TEXT NOT NULL,
681 lc_key TEXT NOT NULL,
682 lc_value BYTEA NOT NULL
684 CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);
686 CREATE TABLE iwlinks (
687 iwl_from INTEGER NOT NULL DEFAULT 0,
688 iwl_prefix TEXT NOT NULL DEFAULT '',
689 iwl_title TEXT NOT NULL DEFAULT ''
691 CREATE UNIQUE INDEX iwl_from ON iwlinks (iwl_from, iwl_prefix, iwl_title);
692 CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_title, iwl_from);
693 CREATE UNIQUE INDEX iwl_prefix_from_title ON iwlinks (iwl_prefix, iwl_from, iwl_title);
695 CREATE TABLE msg_resource (
696 mr_resource TEXT NOT NULL,
697 mr_lang TEXT NOT NULL,
698 mr_blob TEXT NOT NULL,
699 mr_timestamp TIMESTAMPTZ NOT NULL
701 CREATE UNIQUE INDEX mr_resource_lang ON msg_resource (mr_resource, mr_lang);
703 CREATE TABLE msg_resource_links (
704 mrl_resource TEXT NOT NULL,
705 mrl_message TEXT NOT NULL
707 CREATE UNIQUE INDEX mrl_message_resource ON msg_resource_links (mrl_message, mrl_resource);
709 CREATE TABLE module_deps (
710 md_module TEXT NOT NULL,
711 md_skin TEXT NOT NULL,
712 md_deps TEXT NOT NULL
714 CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin);
716 CREATE SEQUENCE sites_site_id_seq;
718 site_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('sites_site_id_seq'),
719 site_global_key TEXT NOT NULL,
720 site_type TEXT NOT NULL,
721 site_group TEXT NOT NULL,
722 site_source TEXT NOT NULL,
723 site_language TEXT NOT NULL,
724 site_protocol TEXT NOT NULL,
725 site_domain TEXT NOT NULL,
726 site_data TEXT NOT NULL,
727 site_forward SMALLINT NOT NULL,
728 site_config TEXT NOT NULL
730 CREATE UNIQUE INDEX site_global_key ON sites (site_global_key);
731 CREATE INDEX site_type ON sites (site_type);
732 CREATE INDEX site_group ON sites (site_group);
733 CREATE INDEX site_source ON sites (site_source);
734 CREATE INDEX site_language ON sites (site_language);
735 CREATE INDEX site_protocol ON sites (site_protocol);
736 CREATE INDEX site_domain ON sites (site_domain);
737 CREATE INDEX site_forward ON sites (site_forward);
739 CREATE TABLE site_identifiers (
740 si_site INTEGER NOT NULL,
741 si_type TEXT NOT NULL,
744 CREATE UNIQUE INDEX si_type_key ON site_identifiers (si_type, si_key);
745 CREATE INDEX si_site ON site_identifiers (si_site);
746 CREATE INDEX si_key ON site_identifiers (si_key);