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 comment_comment_id_seq CASCADE;
16 DROP SEQUENCE IF EXISTS text_old_id_seq CASCADE;
17 DROP SEQUENCE IF EXISTS page_restrictions_pr_id_seq CASCADE;
18 DROP SEQUENCE IF EXISTS ipblocks_ipb_id_seq CASCADE;
19 DROP SEQUENCE IF EXISTS filearchive_fa_id_seq CASCADE;
20 DROP SEQUENCE IF EXISTS uploadstash_us_id_seq CASCADE;
21 DROP SEQUENCE IF EXISTS recentchanges_rc_id_seq CASCADE;
22 DROP SEQUENCE IF EXISTS watchlist_wl_id_seq CASCADE;
23 DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE;
24 DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE;
25 DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE;
26 DROP SEQUENCE IF EXISTS archive_ar_id_seq CASCADE;
27 DROP SEQUENCE IF EXISTS externallinks_el_id_seq CASCADE;
28 DROP SEQUENCE IF EXISTS sites_site_id_seq CASCADE;
29 DROP SEQUENCE IF EXISTS change_tag_ct_id_seq CASCADE;
30 DROP SEQUENCE IF EXISTS tag_summary_ts_id_seq CASCADE;
31 DROP FUNCTION IF EXISTS page_deleted() CASCADE;
32 DROP FUNCTION IF EXISTS ts2_page_title() CASCADE;
33 DROP FUNCTION IF EXISTS ts2_page_text() CASCADE;
34 DROP FUNCTION IF EXISTS add_interwiki(TEXT,INT,SMALLINT) CASCADE;
35 DROP TYPE IF EXISTS media_type CASCADE;
37 CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
38 CREATE TABLE mwuser ( -- replace reserved word 'user'
39 user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
40 user_name TEXT NOT NULL UNIQUE,
43 user_newpassword TEXT,
44 user_newpass_time TIMESTAMPTZ,
47 user_email_token TEXT,
48 user_email_token_expires TIMESTAMPTZ,
49 user_email_authenticated TIMESTAMPTZ,
50 user_touched TIMESTAMPTZ,
51 user_registration TIMESTAMPTZ,
52 user_editcount INTEGER,
53 user_password_expires TIMESTAMPTZ NULL
55 CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
57 -- Create a dummy user to satisfy fk contraints especially with revisions
59 VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
61 CREATE TABLE user_groups (
62 ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
63 ug_group TEXT NOT NULL,
64 ug_expiry TIMESTAMPTZ NULL,
65 PRIMARY KEY(ug_user, ug_group)
67 CREATE INDEX user_groups_group ON user_groups (ug_group);
68 CREATE INDEX user_groups_expiry ON user_groups (ug_expiry);
70 CREATE TABLE user_former_groups (
71 ufg_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
72 ufg_group TEXT NOT NULL
74 CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group);
76 CREATE TABLE user_newtalk (
77 user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
79 user_last_timestamp TIMESTAMPTZ
81 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
82 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
84 CREATE TABLE bot_passwords (
85 bp_user INTEGER NOT NULL,
86 bp_app_id TEXT NOT NULL,
87 bp_password TEXT NOT NULL,
88 bp_token TEXT NOT NULL,
89 bp_restrictions TEXT NOT NULL,
90 bp_grants TEXT NOT NULL,
91 PRIMARY KEY ( bp_user, bp_app_id )
94 CREATE SEQUENCE page_page_id_seq;
96 page_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('page_page_id_seq'),
97 page_namespace SMALLINT NOT NULL,
98 page_title TEXT NOT NULL,
99 page_restrictions TEXT,
100 page_is_redirect SMALLINT NOT NULL DEFAULT 0,
101 page_is_new SMALLINT NOT NULL DEFAULT 0,
102 page_random NUMERIC(15,14) NOT NULL DEFAULT RANDOM(),
103 page_touched TIMESTAMPTZ,
104 page_links_updated TIMESTAMPTZ NULL,
105 page_latest INTEGER NOT NULL, -- FK?
106 page_len INTEGER NOT NULL,
107 page_content_model TEXT,
108 page_lang TEXT DEFAULT NULL
110 CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
111 CREATE INDEX page_main_title ON page (page_title text_pattern_ops) WHERE page_namespace = 0;
112 CREATE INDEX page_talk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 1;
113 CREATE INDEX page_user_title ON page (page_title text_pattern_ops) WHERE page_namespace = 2;
114 CREATE INDEX page_utalk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 3;
115 CREATE INDEX page_project_title ON page (page_title text_pattern_ops) WHERE page_namespace = 4;
116 CREATE INDEX page_mediawiki_title ON page (page_title text_pattern_ops) WHERE page_namespace = 8;
117 CREATE INDEX page_random_idx ON page (page_random);
118 CREATE INDEX page_len_idx ON page (page_len);
120 CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
123 DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
128 CREATE TRIGGER page_deleted AFTER DELETE ON page
129 FOR EACH ROW EXECUTE PROCEDURE page_deleted();
131 CREATE SEQUENCE revision_rev_id_seq;
132 CREATE TABLE revision (
133 rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('revision_rev_id_seq'),
134 rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
135 rev_text_id INTEGER NULL, -- FK
136 rev_comment TEXT NOT NULL DEFAULT '',
137 rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
138 rev_user_text TEXT NOT NULL,
139 rev_timestamp TIMESTAMPTZ NOT NULL,
140 rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
141 rev_deleted SMALLINT NOT NULL DEFAULT 0,
142 rev_len INTEGER NULL,
143 rev_parent_id INTEGER NULL,
144 rev_sha1 TEXT NOT NULL DEFAULT '',
145 rev_content_model TEXT,
146 rev_content_format TEXT
148 CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
149 CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
150 CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
151 CREATE INDEX rev_user_idx ON revision (rev_user);
152 CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
154 CREATE TABLE revision_comment_temp (
155 revcomment_rev INTEGER NOT NULL,
156 revcomment_comment_id INTEGER NOT NULL,
157 PRIMARY KEY (revcomment_rev, revcomment_comment_id)
159 CREATE UNIQUE INDEX revcomment_rev ON revision_comment_temp (revcomment_rev);
161 CREATE SEQUENCE ip_changes_ipc_rev_id_seq;
163 CREATE TABLE ip_changes (
164 ipc_rev_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('ip_changes_ipc_rev_id_seq'),
165 ipc_rev_timestamp TIMESTAMPTZ NOT NULL,
166 ipc_hex BYTEA NOT NULL DEFAULT ''
169 CREATE INDEX ipc_rev_timestamp ON ip_changes (ipc_rev_timestamp);
170 CREATE INDEX ipc_hex_time ON ip_changes (ipc_hex,ipc_rev_timestamp);
172 CREATE SEQUENCE text_old_id_seq;
173 CREATE TABLE pagecontent ( -- replaces reserved word 'text'
174 old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_seq'),
180 CREATE SEQUENCE comment_comment_id_seq;
181 CREATE TABLE comment (
182 comment_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('comment_comment_id_seq'),
183 comment_hash INTEGER NOT NULL,
184 comment_text TEXT NOT NULL,
187 CREATE INDEX comment_hash ON comment (comment_hash);
190 CREATE SEQUENCE page_restrictions_pr_id_seq;
191 CREATE TABLE page_restrictions (
192 pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'),
193 pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
194 pr_type TEXT NOT NULL,
195 pr_level TEXT NOT NULL,
196 pr_cascade SMALLINT NOT NULL,
197 pr_user INTEGER NULL,
198 pr_expiry TIMESTAMPTZ NULL
200 ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
202 CREATE TABLE page_props (
203 pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
204 pp_propname TEXT NOT NULL,
205 pp_value TEXT NOT NULL,
208 ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
209 CREATE INDEX page_props_propname ON page_props (pp_propname);
210 CREATE UNIQUE INDEX pp_propname_page ON page_props (pp_propname,pp_page);
211 CREATE INDEX pp_propname_sortkey_page ON page_props (pp_propname, pp_sortkey, pp_page) WHERE (pp_sortkey IS NOT NULL);
213 CREATE SEQUENCE archive_ar_id_seq;
214 CREATE TABLE archive (
215 ar_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('archive_ar_id_seq'),
216 ar_namespace SMALLINT NOT NULL,
217 ar_title TEXT NOT NULL,
218 ar_text TEXT, -- technically should be bytea, but not used anymore
219 ar_page_id INTEGER NULL,
220 ar_parent_id INTEGER NULL,
221 ar_sha1 TEXT NOT NULL DEFAULT '',
222 ar_comment TEXT NOT NULL DEFAULT '',
223 ar_comment_id INTEGER NOT NULL DEFAULT 0,
224 ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
225 ar_user_text TEXT NOT NULL,
226 ar_timestamp TIMESTAMPTZ NOT NULL,
227 ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
231 ar_deleted SMALLINT NOT NULL DEFAULT 0,
233 ar_content_model TEXT,
234 ar_content_format TEXT
236 CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
237 CREATE INDEX archive_user_text ON archive (ar_user_text);
240 CREATE TABLE redirect (
241 rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
242 rd_namespace SMALLINT NOT NULL,
243 rd_title TEXT NOT NULL,
244 rd_interwiki TEXT NULL,
245 rd_fragment TEXT NULL
247 CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
250 CREATE TABLE pagelinks (
251 pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
252 pl_from_namespace INTEGER NOT NULL DEFAULT 0,
253 pl_namespace SMALLINT NOT NULL,
254 pl_title TEXT NOT NULL
256 CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
257 CREATE INDEX pagelinks_title ON pagelinks (pl_title);
259 CREATE TABLE templatelinks (
260 tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
261 tl_from_namespace INTEGER NOT NULL DEFAULT 0,
262 tl_namespace SMALLINT NOT NULL,
263 tl_title TEXT NOT NULL
265 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
266 CREATE INDEX templatelinks_from ON templatelinks (tl_from);
268 CREATE TABLE imagelinks (
269 il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
270 il_from_namespace INTEGER NOT NULL DEFAULT 0,
273 CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
275 CREATE TABLE categorylinks (
276 cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
278 cl_sortkey TEXT NULL,
279 cl_timestamp TIMESTAMPTZ NOT NULL,
280 cl_sortkey_prefix TEXT NOT NULL DEFAULT '',
281 cl_collation TEXT NOT NULL DEFAULT 0,
282 cl_type TEXT NOT NULL DEFAULT 'page'
284 CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
285 CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
287 CREATE SEQUENCE externallinks_el_id_seq;
288 CREATE TABLE externallinks (
289 el_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'),
290 el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
292 el_index TEXT NOT NULL,
293 el_index_60 BYTEA NOT NULL DEFAULT ''
295 CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
296 CREATE INDEX externallinks_index ON externallinks (el_index);
297 CREATE INDEX el_index_60 ON externallinks (el_index_60, el_id);
298 CREATE INDEX el_from_index_60 ON externallinks (el_from, el_index_60, el_id);
300 CREATE TABLE langlinks (
301 ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
305 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
306 CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);
309 CREATE TABLE site_stats (
310 ss_row_id INTEGER NOT NULL PRIMARY KEY DEFAULT 0,
311 ss_total_edits INTEGER DEFAULT 0,
312 ss_good_articles INTEGER DEFAULT 0,
313 ss_total_pages INTEGER DEFAULT -1,
314 ss_users INTEGER DEFAULT -1,
315 ss_active_users INTEGER DEFAULT -1,
316 ss_admins INTEGER DEFAULT -1,
317 ss_images INTEGER DEFAULT 0
321 CREATE SEQUENCE ipblocks_ipb_id_seq;
322 CREATE TABLE ipblocks (
323 ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'),
324 ipb_address TEXT NULL,
325 ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
326 ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
327 ipb_by_text TEXT NOT NULL DEFAULT '',
328 ipb_reason TEXT NOT NULL DEFAULT '',
329 ipb_reason_id INTEGER NOT NULL DEFAULT 0,
330 ipb_timestamp TIMESTAMPTZ NOT NULL,
331 ipb_auto SMALLINT NOT NULL DEFAULT 0,
332 ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
333 ipb_create_account SMALLINT NOT NULL DEFAULT 1,
334 ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
335 ipb_expiry TIMESTAMPTZ NOT NULL,
336 ipb_range_start TEXT,
338 ipb_deleted SMALLINT NOT NULL DEFAULT 0,
339 ipb_block_email SMALLINT NOT NULL DEFAULT 0,
340 ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0,
341 ipb_parent_block_id INTEGER NULL REFERENCES ipblocks(ipb_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
344 CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
345 CREATE INDEX ipb_user ON ipblocks (ipb_user);
346 CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
347 CREATE INDEX ipb_parent_block_id ON ipblocks (ipb_parent_block_id);
351 img_name TEXT NOT NULL PRIMARY KEY,
352 img_size INTEGER NOT NULL,
353 img_width INTEGER NOT NULL,
354 img_height INTEGER NOT NULL,
355 img_metadata BYTEA NOT NULL DEFAULT '',
358 img_major_mime TEXT DEFAULT 'unknown',
359 img_minor_mime TEXT DEFAULT 'unknown',
360 img_description TEXT NOT NULL DEFAULT '',
361 img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
362 img_user_text TEXT NOT NULL,
363 img_timestamp TIMESTAMPTZ,
364 img_sha1 TEXT NOT NULL DEFAULT ''
366 CREATE INDEX img_size_idx ON image (img_size);
367 CREATE INDEX img_timestamp_idx ON image (img_timestamp);
368 CREATE INDEX img_sha1 ON image (img_sha1);
370 CREATE TABLE image_comment_temp (
371 imgcomment_name TEXT NOT NULL,
372 imgcomment_description_id INTEGER NOT NULL,
373 PRIMARY KEY (imgcomment_name, imgcomment_description_id)
375 CREATE UNIQUE INDEX imgcomment_name ON image_comment_temp (imgcomment_name);
377 CREATE TABLE oldimage (
378 oi_name TEXT NOT NULL,
379 oi_archive_name TEXT NOT NULL,
380 oi_size INTEGER NOT NULL,
381 oi_width INTEGER NOT NULL,
382 oi_height INTEGER NOT NULL,
383 oi_bits SMALLINT NULL,
384 oi_description TEXT NOT NULL DEFAULT '',
385 oi_description_id INTEGER NOT NULL DEFAULT 0,
386 oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
387 oi_user_text TEXT NOT NULL,
388 oi_timestamp TIMESTAMPTZ NULL,
389 oi_metadata BYTEA NOT NULL DEFAULT '',
390 oi_media_type TEXT NULL,
391 oi_major_mime TEXT NULL DEFAULT 'unknown',
392 oi_minor_mime TEXT NULL DEFAULT 'unknown',
393 oi_deleted SMALLINT NOT NULL DEFAULT 0,
394 oi_sha1 TEXT NOT NULL DEFAULT ''
396 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;
397 CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
398 CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
399 CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
402 CREATE SEQUENCE filearchive_fa_id_seq;
403 CREATE TABLE filearchive (
404 fa_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
405 fa_name TEXT NOT NULL,
406 fa_archive_name TEXT,
407 fa_storage_group TEXT,
409 fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
410 fa_deleted_timestamp TIMESTAMPTZ NOT NULL,
411 fa_deleted_reason TEXT NOT NULL DEFAULT '',
412 fa_deleted_reason_id INTEGER NOT NULL DEFAULT 0,
413 fa_size INTEGER NOT NULL,
414 fa_width INTEGER NOT NULL,
415 fa_height INTEGER NOT NULL,
416 fa_metadata BYTEA NOT NULL DEFAULT '',
419 fa_major_mime TEXT DEFAULT 'unknown',
420 fa_minor_mime TEXT DEFAULT 'unknown',
421 fa_description TEXT NOT NULL DEFAULT '',
422 fa_description_id INTEGER NOT NULL DEFAULT 0,
423 fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
424 fa_user_text TEXT NOT NULL,
425 fa_timestamp TIMESTAMPTZ,
426 fa_deleted SMALLINT NOT NULL DEFAULT 0,
427 fa_sha1 TEXT NOT NULL DEFAULT ''
429 CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
430 CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
431 CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
432 CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
433 CREATE INDEX fa_sha1 ON filearchive (fa_sha1);
435 CREATE SEQUENCE uploadstash_us_id_seq;
436 CREATE TYPE media_type AS ENUM ('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE','3D');
438 CREATE TABLE uploadstash (
439 us_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('uploadstash_us_id_seq'),
446 us_timestamp TIMESTAMPTZ,
448 us_chunk_inx INTEGER NULL,
452 us_media_type media_type DEFAULT NULL,
453 us_image_width INTEGER,
454 us_image_height INTEGER,
455 us_image_bits SMALLINT
458 CREATE INDEX us_user_idx ON uploadstash (us_user);
459 CREATE UNIQUE INDEX us_key_idx ON uploadstash (us_key);
460 CREATE INDEX us_timestamp_idx ON uploadstash (us_timestamp);
463 CREATE SEQUENCE recentchanges_rc_id_seq;
464 CREATE TABLE recentchanges (
465 rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'),
466 rc_timestamp TIMESTAMPTZ NOT NULL,
467 rc_cur_time TIMESTAMPTZ NULL,
468 rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
469 rc_user_text TEXT NOT NULL,
470 rc_namespace SMALLINT NOT NULL,
471 rc_title TEXT NOT NULL,
472 rc_comment TEXT NOT NULL DEFAULT '',
473 rc_comment_id INTEGER NOT NULL DEFAULT 0,
474 rc_minor SMALLINT NOT NULL DEFAULT 0,
475 rc_bot SMALLINT NOT NULL DEFAULT 0,
476 rc_new SMALLINT NOT NULL DEFAULT 0,
477 rc_cur_id INTEGER NULL,
478 rc_this_oldid INTEGER NOT NULL,
479 rc_last_oldid INTEGER NOT NULL,
480 rc_type SMALLINT NOT NULL DEFAULT 0,
481 rc_source TEXT NOT NULL,
482 rc_patrolled SMALLINT NOT NULL DEFAULT 0,
486 rc_deleted SMALLINT NOT NULL DEFAULT 0,
487 rc_logid INTEGER NOT NULL DEFAULT 0,
492 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
493 CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0;
494 CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
495 CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
496 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
497 CREATE INDEX rc_ip ON recentchanges (rc_ip);
498 CREATE INDEX rc_name_type_patrolled_timestamp ON recentchanges (rc_namespace, rc_type, rc_patrolled, rc_timestamp);
501 CREATE SEQUENCE watchlist_wl_id_seq;
502 CREATE TABLE watchlist (
503 wl_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('watchlist_wl_id_seq'),
504 wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
505 wl_namespace SMALLINT NOT NULL DEFAULT 0,
506 wl_title TEXT NOT NULL,
507 wl_notificationtimestamp TIMESTAMPTZ
509 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
510 CREATE INDEX wl_user ON watchlist (wl_user);
511 CREATE INDEX wl_user_notificationtimestamp ON watchlist (wl_user, wl_notificationtimestamp);
514 CREATE TABLE interwiki (
515 iw_prefix TEXT NOT NULL UNIQUE,
516 iw_url TEXT NOT NULL,
517 iw_local SMALLINT NOT NULL,
518 iw_trans SMALLINT NOT NULL DEFAULT 0,
519 iw_api TEXT NOT NULL DEFAULT '',
520 iw_wikiid TEXT NOT NULL DEFAULT ''
524 CREATE TABLE querycache (
525 qc_type TEXT NOT NULL,
526 qc_value INTEGER NOT NULL,
527 qc_namespace SMALLINT NOT NULL,
528 qc_title TEXT NOT NULL
530 CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
532 CREATE TABLE querycache_info (
533 qci_type TEXT UNIQUE,
534 qci_timestamp TIMESTAMPTZ NULL
537 CREATE TABLE querycachetwo (
538 qcc_type TEXT NOT NULL,
539 qcc_value INTEGER NOT NULL DEFAULT 0,
540 qcc_namespace INTEGER NOT NULL DEFAULT 0,
541 qcc_title TEXT NOT NULL DEFAULT '',
542 qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
543 qcc_titletwo TEXT NOT NULL DEFAULT ''
545 CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
546 CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
547 CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
549 CREATE TABLE objectcache (
551 value BYTEA NOT NULL DEFAULT '',
552 exptime TIMESTAMPTZ NOT NULL
554 CREATE INDEX objectcacache_exptime ON objectcache (exptime);
556 CREATE TABLE transcache (
557 tc_url TEXT NOT NULL UNIQUE,
558 tc_contents TEXT NOT NULL,
559 tc_time TIMESTAMPTZ NOT NULL
563 CREATE SEQUENCE logging_log_id_seq;
564 CREATE TABLE logging (
565 log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq'),
566 log_type TEXT NOT NULL,
567 log_action TEXT NOT NULL,
568 log_timestamp TIMESTAMPTZ NOT NULL,
569 log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
570 log_namespace SMALLINT NOT NULL,
571 log_title TEXT NOT NULL,
572 log_comment TEXT NOT NULL DEFAULT '',
573 log_comment_id INTEGER NOT NULL DEFAULT 0,
575 log_deleted SMALLINT NOT NULL DEFAULT 0,
576 log_user_text TEXT NOT NULL DEFAULT '',
579 CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
580 CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
581 CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
582 CREATE INDEX logging_times ON logging (log_timestamp);
583 CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp);
584 CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp);
585 CREATE INDEX logging_user_text_type_time ON logging (log_user_text, log_type, log_timestamp);
586 CREATE INDEX logging_user_text_time ON logging (log_user_text, log_timestamp);
588 CREATE TABLE log_search (
589 ls_field TEXT NOT NULL,
590 ls_value TEXT NOT NULL,
591 ls_log_id INTEGER NOT NULL DEFAULT 0,
592 PRIMARY KEY (ls_field,ls_value,ls_log_id)
594 CREATE INDEX ls_log_id ON log_search (ls_log_id);
597 CREATE SEQUENCE job_job_id_seq;
599 job_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
600 job_cmd TEXT NOT NULL,
601 job_namespace SMALLINT NOT NULL,
602 job_title TEXT NOT NULL,
603 job_timestamp TIMESTAMPTZ,
604 job_params TEXT NOT NULL,
605 job_random INTEGER NOT NULL DEFAULT 0,
606 job_attempts INTEGER NOT NULL DEFAULT 0,
607 job_token TEXT NOT NULL DEFAULT '',
608 job_token_timestamp TIMESTAMPTZ,
609 job_sha1 TEXT NOT NULL DEFAULT ''
611 CREATE INDEX job_sha1 ON job (job_sha1);
612 CREATE INDEX job_cmd_token ON job (job_cmd, job_token, job_random);
613 CREATE INDEX job_cmd_token_id ON job (job_cmd, job_token, job_id);
614 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
615 CREATE INDEX job_timestamp_idx ON job (job_timestamp);
617 -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
618 -- Version 8.3 or higher only. Previous versions would need another parmeter for to_tsvector.
619 -- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.
621 ALTER TABLE page ADD titlevector tsvector;
622 CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
625 IF TG_OP = 'INSERT' THEN
626 NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' '));
627 ELSIF NEW.page_title != OLD.page_title THEN
628 NEW.titlevector := to_tsvector(REPLACE(NEW.page_title,'/',' '));
634 CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
635 FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
638 ALTER TABLE pagecontent ADD textvector tsvector;
639 CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
642 IF TG_OP = 'INSERT' THEN
643 NEW.textvector = to_tsvector(NEW.old_text);
644 ELSIF NEW.old_text != OLD.old_text THEN
645 NEW.textvector := to_tsvector(NEW.old_text);
651 CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
652 FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
654 -- These are added by the setup script due to version compatibility issues
655 -- If using 8.1, we switch from "gin" to "gist"
657 CREATE INDEX ts2_page_title ON page USING gin(titlevector);
658 CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
660 CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
662 INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
666 -- This table is not used unless profiling is turned on
667 CREATE TABLE profiling (
668 pf_count INTEGER NOT NULL DEFAULT 0,
669 pf_time FLOAT NOT NULL DEFAULT 0,
670 pf_memory FLOAT NOT NULL DEFAULT 0,
671 pf_name TEXT NOT NULL,
674 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
676 CREATE TABLE protected_titles (
677 pt_namespace SMALLINT NOT NULL,
678 pt_title TEXT NOT NULL,
679 pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
680 pt_reason TEXT NOT NULL DEFAULT '',
681 pt_reason_id INTEGER NOT NULL DEFAULT 0,
682 pt_timestamp TIMESTAMPTZ NOT NULL,
683 pt_expiry TIMESTAMPTZ NULL,
684 pt_create_perm TEXT NOT NULL DEFAULT ''
686 CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
689 CREATE TABLE updatelog (
690 ul_key TEXT NOT NULL PRIMARY KEY,
695 CREATE SEQUENCE category_cat_id_seq;
696 CREATE TABLE category (
697 cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_cat_id_seq'),
698 cat_title TEXT NOT NULL,
699 cat_pages INTEGER NOT NULL DEFAULT 0,
700 cat_subcats INTEGER NOT NULL DEFAULT 0,
701 cat_files INTEGER NOT NULL DEFAULT 0,
702 cat_hidden SMALLINT NOT NULL DEFAULT 0
704 CREATE UNIQUE INDEX category_title ON category(cat_title);
705 CREATE INDEX category_pages ON category(cat_pages);
707 CREATE SEQUENCE change_tag_ct_id_seq;
708 CREATE TABLE change_tag (
709 ct_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('change_tag_ct_id_seq'),
710 ct_rc_id INTEGER NULL,
711 ct_log_id INTEGER NULL,
712 ct_rev_id INTEGER NULL,
713 ct_tag TEXT NOT NULL,
716 CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag);
717 CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag);
718 CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag);
719 CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
721 CREATE SEQUENCE tag_summary_ts_id_seq;
722 CREATE TABLE tag_summary (
723 ts_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('tag_summary_ts_id_seq'),
724 ts_rc_id INTEGER NULL,
725 ts_log_id INTEGER NULL,
726 ts_rev_id INTEGER NULL,
727 ts_tags TEXT NOT NULL
729 CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id);
730 CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id);
731 CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id);
733 CREATE TABLE valid_tag (
734 vt_tag TEXT NOT NULL PRIMARY KEY
737 CREATE TABLE user_properties (
738 up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
739 up_property TEXT NOT NULL,
742 CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);
743 CREATE INDEX user_properties_property ON user_properties (up_property);
745 CREATE TABLE l10n_cache (
746 lc_lang TEXT NOT NULL,
747 lc_key TEXT NOT NULL,
748 lc_value BYTEA NOT NULL
750 CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);
752 CREATE TABLE iwlinks (
753 iwl_from INTEGER NOT NULL DEFAULT 0,
754 iwl_prefix TEXT NOT NULL DEFAULT '',
755 iwl_title TEXT NOT NULL DEFAULT ''
757 CREATE UNIQUE INDEX iwl_from ON iwlinks (iwl_from, iwl_prefix, iwl_title);
758 CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_title, iwl_from);
759 CREATE UNIQUE INDEX iwl_prefix_from_title ON iwlinks (iwl_prefix, iwl_from, iwl_title);
761 CREATE TABLE module_deps (
762 md_module TEXT NOT NULL,
763 md_skin TEXT NOT NULL,
764 md_deps TEXT NOT NULL
766 CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin);
768 CREATE SEQUENCE sites_site_id_seq;
770 site_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('sites_site_id_seq'),
771 site_global_key TEXT NOT NULL,
772 site_type TEXT NOT NULL,
773 site_group TEXT NOT NULL,
774 site_source TEXT NOT NULL,
775 site_language TEXT NOT NULL,
776 site_protocol TEXT NOT NULL,
777 site_domain TEXT NOT NULL,
778 site_data TEXT NOT NULL,
779 site_forward SMALLINT NOT NULL,
780 site_config TEXT NOT NULL
782 CREATE UNIQUE INDEX site_global_key ON sites (site_global_key);
783 CREATE INDEX site_type ON sites (site_type);
784 CREATE INDEX site_group ON sites (site_group);
785 CREATE INDEX site_source ON sites (site_source);
786 CREATE INDEX site_language ON sites (site_language);
787 CREATE INDEX site_protocol ON sites (site_protocol);
788 CREATE INDEX site_domain ON sites (site_domain);
789 CREATE INDEX site_forward ON sites (site_forward);
791 CREATE TABLE site_identifiers (
792 si_site INTEGER NOT NULL,
793 si_type TEXT NOT NULL,
796 CREATE UNIQUE INDEX si_type_key ON site_identifiers (si_type, si_key);
797 CREATE INDEX si_site ON site_identifiers (si_site);
798 CREATE INDEX si_key ON site_identifiers (si_key);