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 page_restrictions_id_seq CASCADE;
16 DROP SEQUENCE IF EXISTS ipblocks_ipb_id_seq CASCADE;
17 DROP SEQUENCE IF EXISTS recentchanges_rc_id_seq CASCADE;
18 DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE;
19 DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE;
20 DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE;
21 DROP FUNCTION IF EXISTS page_deleted() CASCADE;
22 DROP FUNCTION IF EXISTS ts2_page_title() CASCADE;
23 DROP FUNCTION IF EXISTS ts2_page_text() CASCADE;
24 DROP FUNCTION IF EXISTS add_interwiki(TEXT,INT,SMALLINT) CASCADE;
26 CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
27 CREATE TABLE mwuser ( -- replace reserved word 'user'
28 user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
29 user_name TEXT NOT NULL UNIQUE,
32 user_newpassword TEXT,
33 user_newpass_time TIMESTAMPTZ,
36 user_email_token TEXT,
37 user_email_token_expires TIMESTAMPTZ,
38 user_email_authenticated TIMESTAMPTZ,
40 user_touched TIMESTAMPTZ,
41 user_registration TIMESTAMPTZ,
42 user_editcount INTEGER
44 CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
46 -- Create a dummy user to satisfy fk contraints especially with revisions
48 VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
50 CREATE TABLE user_groups (
51 ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
52 ug_group TEXT NOT NULL
54 CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
56 CREATE TABLE user_former_groups (
57 ufg_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
58 ufg_group TEXT NOT NULL
60 CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group);
62 CREATE TABLE user_newtalk (
63 user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
65 user_last_timestamp TIMESTAMPTZ
67 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
68 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
71 CREATE SEQUENCE page_page_id_seq;
73 page_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('page_page_id_seq'),
74 page_namespace SMALLINT NOT NULL,
75 page_title TEXT NOT NULL,
76 page_restrictions TEXT,
77 page_counter BIGINT NOT NULL DEFAULT 0,
78 page_is_redirect SMALLINT NOT NULL DEFAULT 0,
79 page_is_new SMALLINT NOT NULL DEFAULT 0,
80 page_random NUMERIC(15,14) NOT NULL DEFAULT RANDOM(),
81 page_touched TIMESTAMPTZ,
82 page_latest INTEGER NOT NULL, -- FK?
83 page_len INTEGER NOT NULL
85 CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
86 CREATE INDEX page_main_title ON page (page_title text_pattern_ops) WHERE page_namespace = 0;
87 CREATE INDEX page_talk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 1;
88 CREATE INDEX page_user_title ON page (page_title text_pattern_ops) WHERE page_namespace = 2;
89 CREATE INDEX page_utalk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 3;
90 CREATE INDEX page_project_title ON page (page_title text_pattern_ops) WHERE page_namespace = 4;
91 CREATE INDEX page_mediawiki_title ON page (page_title text_pattern_ops) WHERE page_namespace = 8;
92 CREATE INDEX page_random_idx ON page (page_random);
93 CREATE INDEX page_len_idx ON page (page_len);
95 CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
98 DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
103 CREATE TRIGGER page_deleted AFTER DELETE ON page
104 FOR EACH ROW EXECUTE PROCEDURE page_deleted();
106 CREATE SEQUENCE revision_rev_id_seq;
107 CREATE TABLE revision (
108 rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('revision_rev_id_seq'),
109 rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
110 rev_text_id INTEGER NULL, -- FK
112 rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
113 rev_user_text TEXT NOT NULL,
114 rev_timestamp TIMESTAMPTZ NOT NULL,
115 rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
116 rev_deleted SMALLINT NOT NULL DEFAULT 0,
117 rev_len INTEGER NULL,
118 rev_parent_id INTEGER NULL,
119 rev_sha1 TEXT NOT NULL DEFAULT ''
121 CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
122 CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
123 CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
124 CREATE INDEX rev_user_idx ON revision (rev_user);
125 CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
128 CREATE SEQUENCE text_old_id_seq;
129 CREATE TABLE pagecontent ( -- replaces reserved word 'text'
130 old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_seq'),
136 CREATE SEQUENCE page_restrictions_pr_id_seq;
137 CREATE TABLE page_restrictions (
138 pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'),
139 pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
140 pr_type TEXT NOT NULL,
141 pr_level TEXT NOT NULL,
142 pr_cascade SMALLINT NOT NULL,
143 pr_user INTEGER NULL,
144 pr_expiry TIMESTAMPTZ NULL
146 ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
148 CREATE TABLE page_props (
149 pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
150 pp_propname TEXT NOT NULL,
151 pp_value TEXT NOT NULL
153 ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
154 CREATE INDEX page_props_propname ON page_props (pp_propname);
156 CREATE TABLE archive (
157 ar_namespace SMALLINT NOT NULL,
158 ar_title TEXT NOT NULL,
159 ar_text TEXT, -- technically should be bytea, but not used anymore
160 ar_page_id INTEGER NULL,
161 ar_parent_id INTEGER NULL,
162 ar_sha1 TEXT NOT NULL DEFAULT '',
164 ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
165 ar_user_text TEXT NOT NULL,
166 ar_timestamp TIMESTAMPTZ NOT NULL,
167 ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
171 ar_deleted SMALLINT NOT NULL DEFAULT 0,
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);
178 CREATE TABLE redirect (
179 rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
180 rd_namespace SMALLINT NOT NULL,
181 rd_title TEXT NOT NULL,
182 rd_interwiki TEXT NULL,
183 rd_fragment TEXT NULL
185 CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
188 CREATE TABLE pagelinks (
189 pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
190 pl_namespace SMALLINT NOT NULL,
191 pl_title TEXT NOT NULL
193 CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
194 CREATE INDEX pagelinks_title ON pagelinks (pl_title);
196 CREATE TABLE templatelinks (
197 tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
198 tl_namespace SMALLINT NOT NULL,
199 tl_title TEXT NOT NULL
201 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
202 CREATE INDEX templatelinks_from ON templatelinks (tl_from);
204 CREATE TABLE imagelinks (
205 il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
208 CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
210 CREATE TABLE categorylinks (
211 cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
213 cl_sortkey TEXT NULL,
214 cl_timestamp TIMESTAMPTZ NOT NULL,
215 cl_sortkey_prefix TEXT NOT NULL DEFAULT '',
216 cl_collation TEXT NOT NULL DEFAULT 0,
217 cl_type TEXT NOT NULL DEFAULT 'page'
219 CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
220 CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
222 CREATE TABLE externallinks (
223 el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
225 el_index TEXT NOT NULL
227 CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
228 CREATE INDEX externallinks_index ON externallinks (el_index);
230 CREATE TABLE external_user (
231 eu_local_id INTEGER NOT NULL PRIMARY KEY,
235 CREATE UNIQUE INDEX eu_external_id ON external_user (eu_external_id);
237 CREATE TABLE langlinks (
238 ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
242 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
243 CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);
246 CREATE TABLE site_stats (
247 ss_row_id INTEGER NOT NULL UNIQUE,
248 ss_total_views INTEGER DEFAULT 0,
249 ss_total_edits INTEGER DEFAULT 0,
250 ss_good_articles INTEGER DEFAULT 0,
251 ss_total_pages INTEGER DEFAULT -1,
252 ss_users INTEGER DEFAULT -1,
253 ss_active_users INTEGER DEFAULT -1,
254 ss_admins INTEGER DEFAULT -1,
255 ss_images INTEGER DEFAULT 0
258 CREATE TABLE hitcounter (
259 hc_id BIGINT NOT NULL
263 CREATE SEQUENCE ipblocks_ipb_id_seq;
264 CREATE TABLE ipblocks (
265 ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'),
266 ipb_address TEXT NULL,
267 ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
268 ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
269 ipb_by_text TEXT NOT NULL DEFAULT '',
270 ipb_reason TEXT NOT NULL,
271 ipb_timestamp TIMESTAMPTZ NOT NULL,
272 ipb_auto SMALLINT NOT NULL DEFAULT 0,
273 ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
274 ipb_create_account SMALLINT NOT NULL DEFAULT 1,
275 ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
276 ipb_expiry TIMESTAMPTZ NOT NULL,
277 ipb_range_start TEXT,
279 ipb_deleted SMALLINT NOT NULL DEFAULT 0,
280 ipb_block_email SMALLINT NOT NULL DEFAULT 0,
281 ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
284 CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
285 CREATE INDEX ipb_user ON ipblocks (ipb_user);
286 CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
290 img_name TEXT NOT NULL PRIMARY KEY,
291 img_size INTEGER NOT NULL,
292 img_width INTEGER NOT NULL,
293 img_height INTEGER NOT NULL,
294 img_metadata BYTEA NOT NULL DEFAULT '',
297 img_major_mime TEXT DEFAULT 'unknown',
298 img_minor_mime TEXT DEFAULT 'unknown',
299 img_description TEXT NOT NULL,
300 img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
301 img_user_text TEXT NOT NULL,
302 img_timestamp TIMESTAMPTZ,
303 img_sha1 TEXT NOT NULL DEFAULT ''
305 CREATE INDEX img_size_idx ON image (img_size);
306 CREATE INDEX img_timestamp_idx ON image (img_timestamp);
307 CREATE INDEX img_sha1 ON image (img_sha1);
309 CREATE TABLE oldimage (
310 oi_name TEXT NOT NULL,
311 oi_archive_name TEXT NOT NULL,
312 oi_size INTEGER NOT NULL,
313 oi_width INTEGER NOT NULL,
314 oi_height INTEGER NOT NULL,
315 oi_bits SMALLINT NULL,
317 oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
318 oi_user_text TEXT NOT NULL,
319 oi_timestamp TIMESTAMPTZ NULL,
320 oi_metadata BYTEA NOT NULL DEFAULT '',
321 oi_media_type TEXT NULL,
322 oi_major_mime TEXT NULL DEFAULT 'unknown',
323 oi_minor_mime TEXT NULL DEFAULT 'unknown',
324 oi_deleted SMALLINT NOT NULL DEFAULT 0,
325 oi_sha1 TEXT NOT NULL DEFAULT ''
327 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;
328 CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
329 CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
330 CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
333 CREATE SEQUENCE filearchive_fa_id_seq;
334 CREATE TABLE filearchive (
335 fa_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
336 fa_name TEXT NOT NULL,
337 fa_archive_name TEXT,
338 fa_storage_group TEXT,
340 fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
341 fa_deleted_timestamp TIMESTAMPTZ NOT NULL,
342 fa_deleted_reason TEXT,
343 fa_size INTEGER NOT NULL,
344 fa_width INTEGER NOT NULL,
345 fa_height INTEGER NOT NULL,
346 fa_metadata BYTEA NOT NULL DEFAULT '',
349 fa_major_mime TEXT DEFAULT 'unknown',
350 fa_minor_mime TEXT DEFAULT 'unknown',
351 fa_description TEXT NOT NULL,
352 fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
353 fa_user_text TEXT NOT NULL,
354 fa_timestamp TIMESTAMPTZ,
355 fa_deleted SMALLINT NOT NULL DEFAULT 0
357 CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
358 CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
359 CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
360 CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
362 CREATE SEQUENCE uploadstash_us_id_seq;
363 CREATE TYPE media_type AS ENUM ('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE');
365 CREATE TABLE uploadstash (
366 us_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('uploadstash_us_id_seq'),
372 us_timestamp TIMESTAMPTZ,
377 us_media_type media_type DEFAULT NULL,
378 us_image_width INTEGER,
379 us_image_height INTEGER,
380 us_image_bits INTEGER
383 CREATE INDEX us_user_idx ON uploadstash (us_user);
384 CREATE UNIQUE INDEX us_key_idx ON uploadstash (us_key);
385 CREATE INDEX us_timestamp_idx ON uploadstash (us_timestamp);
388 CREATE SEQUENCE recentchanges_rc_id_seq;
389 CREATE TABLE recentchanges (
390 rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'),
391 rc_timestamp TIMESTAMPTZ NOT NULL,
392 rc_cur_time TIMESTAMPTZ NOT NULL,
393 rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
394 rc_user_text TEXT NOT NULL,
395 rc_namespace SMALLINT NOT NULL,
396 rc_title TEXT NOT NULL,
398 rc_minor SMALLINT NOT NULL DEFAULT 0,
399 rc_bot SMALLINT NOT NULL DEFAULT 0,
400 rc_new SMALLINT NOT NULL DEFAULT 0,
401 rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
402 rc_this_oldid INTEGER NOT NULL,
403 rc_last_oldid INTEGER NOT NULL,
404 rc_type SMALLINT NOT NULL DEFAULT 0,
405 rc_moved_to_ns SMALLINT,
406 rc_moved_to_title TEXT,
407 rc_patrolled SMALLINT NOT NULL DEFAULT 0,
411 rc_deleted SMALLINT NOT NULL DEFAULT 0,
412 rc_logid INTEGER NOT NULL DEFAULT 0,
417 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
418 CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0;
419 CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
420 CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
421 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
422 CREATE INDEX rc_ip ON recentchanges (rc_ip);
425 CREATE TABLE watchlist (
426 wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
427 wl_namespace SMALLINT NOT NULL DEFAULT 0,
428 wl_title TEXT NOT NULL,
429 wl_notificationtimestamp TIMESTAMPTZ
431 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
432 CREATE INDEX wl_user ON watchlist (wl_user);
435 CREATE TABLE interwiki (
436 iw_prefix TEXT NOT NULL UNIQUE,
437 iw_url TEXT NOT NULL,
438 iw_local SMALLINT NOT NULL,
439 iw_trans SMALLINT NOT NULL DEFAULT 0,
440 iw_api TEXT NOT NULL DEFAULT '',
441 iw_wikiid TEXT NOT NULL DEFAULT ''
445 CREATE TABLE querycache (
446 qc_type TEXT NOT NULL,
447 qc_value INTEGER NOT NULL,
448 qc_namespace SMALLINT NOT NULL,
449 qc_title TEXT NOT NULL
451 CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
453 CREATE TABLE querycache_info (
454 qci_type TEXT UNIQUE,
455 qci_timestamp TIMESTAMPTZ NULL
458 CREATE TABLE querycachetwo (
459 qcc_type TEXT NOT NULL,
460 qcc_value INTEGER NOT NULL DEFAULT 0,
461 qcc_namespace INTEGER NOT NULL DEFAULT 0,
462 qcc_title TEXT NOT NULL DEFAULT '',
463 qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
464 qcc_titletwo TEXT NOT NULL DEFAULT ''
466 CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
467 CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
468 CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
470 CREATE TABLE objectcache (
472 value BYTEA NOT NULL DEFAULT '',
473 exptime TIMESTAMPTZ NOT NULL
475 CREATE INDEX objectcacache_exptime ON objectcache (exptime);
477 CREATE TABLE transcache (
478 tc_url TEXT NOT NULL UNIQUE,
479 tc_contents TEXT NOT NULL,
480 tc_time TIMESTAMPTZ NOT NULL
484 CREATE SEQUENCE logging_log_id_seq;
485 CREATE TABLE logging (
486 log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq'),
487 log_type TEXT NOT NULL,
488 log_action TEXT NOT NULL,
489 log_timestamp TIMESTAMPTZ NOT NULL,
490 log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
491 log_namespace SMALLINT NOT NULL,
492 log_title TEXT NOT NULL,
495 log_deleted SMALLINT NOT NULL DEFAULT 0,
496 log_user_text TEXT NOT NULL DEFAULT '',
499 CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
500 CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
501 CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
502 CREATE INDEX logging_times ON logging (log_timestamp);
503 CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp);
504 CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp);
506 CREATE TABLE log_search (
507 ls_field TEXT NOT NULL,
508 ls_value TEXT NOT NULL,
509 ls_log_id INTEGER NOT NULL DEFAULT 0,
510 PRIMARY KEY (ls_field,ls_value,ls_log_id)
512 CREATE INDEX ls_log_id ON log_search (ls_log_id);
515 CREATE SEQUENCE job_job_id_seq;
517 job_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
518 job_cmd TEXT NOT NULL,
519 job_namespace SMALLINT NOT NULL,
520 job_title TEXT NOT NULL,
521 job_params TEXT NOT NULL
523 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
525 -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
526 -- Version 8.3 or higher only. Previous versions would need another parmeter for to_tsvector.
527 -- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.
529 ALTER TABLE page ADD titlevector tsvector;
530 CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
533 IF TG_OP = 'INSERT' THEN
534 NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' '));
535 ELSIF NEW.page_title != OLD.page_title THEN
536 NEW.titlevector := to_tsvector(REPLACE(NEW.page_title,'/',' '));
542 CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
543 FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
546 ALTER TABLE pagecontent ADD textvector tsvector;
547 CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
550 IF TG_OP = 'INSERT' THEN
551 NEW.textvector = to_tsvector(NEW.old_text);
552 ELSIF NEW.old_text != OLD.old_text THEN
553 NEW.textvector := to_tsvector(NEW.old_text);
559 CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
560 FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
562 -- These are added by the setup script due to version compatibility issues
563 -- If using 8.1, we switch from "gin" to "gist"
565 CREATE INDEX ts2_page_title ON page USING gin(titlevector);
566 CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
568 CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
570 INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
574 -- This table is not used unless profiling is turned on
575 CREATE TABLE profiling (
576 pf_count INTEGER NOT NULL DEFAULT 0,
577 pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
578 pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,
579 pf_name TEXT NOT NULL,
582 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
584 CREATE TABLE protected_titles (
585 pt_namespace SMALLINT NOT NULL,
586 pt_title TEXT NOT NULL,
587 pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
589 pt_timestamp TIMESTAMPTZ NOT NULL,
590 pt_expiry TIMESTAMPTZ NULL,
591 pt_create_perm TEXT NOT NULL DEFAULT ''
593 CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
596 CREATE TABLE updatelog (
597 ul_key TEXT NOT NULL PRIMARY KEY,
602 CREATE SEQUENCE category_cat_id_seq;
603 CREATE TABLE category (
604 cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_cat_id_seq'),
605 cat_title TEXT NOT NULL,
606 cat_pages INTEGER NOT NULL DEFAULT 0,
607 cat_subcats INTEGER NOT NULL DEFAULT 0,
608 cat_files INTEGER NOT NULL DEFAULT 0,
609 cat_hidden SMALLINT NOT NULL DEFAULT 0
611 CREATE UNIQUE INDEX category_title ON category(cat_title);
612 CREATE INDEX category_pages ON category(cat_pages);
614 CREATE TABLE change_tag (
615 ct_rc_id INTEGER NULL,
616 ct_log_id INTEGER NULL,
617 ct_rev_id INTEGER NULL,
618 ct_tag TEXT NOT NULL,
621 CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag);
622 CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag);
623 CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag);
624 CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
626 CREATE TABLE tag_summary (
627 ts_rc_id INTEGER NULL,
628 ts_log_id INTEGER NULL,
629 ts_rev_id INTEGER NULL,
630 ts_tags TEXT NOT NULL
632 CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id);
633 CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id);
634 CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id);
636 CREATE TABLE valid_tag (
637 vt_tag TEXT NOT NULL PRIMARY KEY
640 CREATE TABLE user_properties (
641 up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
642 up_property TEXT NOT NULL,
645 CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);
646 CREATE INDEX user_properties_property ON user_properties (up_property);
648 CREATE TABLE l10n_cache (
649 lc_lang TEXT NOT NULL,
650 lc_key TEXT NOT NULL,
651 lc_value TEXT NOT NULL
653 CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);
655 CREATE TABLE iwlinks (
656 iwl_from INTEGER NOT NULL DEFAULT 0,
657 iwl_prefix TEXT NOT NULL DEFAULT '',
658 iwl_title TEXT NOT NULL DEFAULT ''
660 CREATE UNIQUE INDEX iwl_from ON iwlinks (iwl_from, iwl_prefix, iwl_title);
661 CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_title, iwl_from);
663 CREATE TABLE msg_resource (
664 mr_resource TEXT NOT NULL,
665 mr_lang TEXT NOT NULL,
666 mr_blob TEXT NOT NULL,
667 mr_timestamp TIMESTAMPTZ NOT NULL
669 CREATE UNIQUE INDEX mr_resource_lang ON msg_resource (mr_resource, mr_lang);
671 CREATE TABLE msg_resource_links (
672 mrl_resource TEXT NOT NULL,
673 mrl_message TEXT NOT NULL
675 CREATE UNIQUE INDEX mrl_message_resource ON msg_resource_links (mrl_message, mrl_resource);
677 CREATE TABLE module_deps (
678 md_module TEXT NOT NULL,
679 md_skin TEXT NOT NULL,
680 md_deps TEXT NOT NULL
682 CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin);