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 CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
13 CREATE TABLE mwuser ( -- replace reserved word 'user'
14 user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
15 user_name TEXT NOT NULL UNIQUE,
18 user_newpassword TEXT,
19 user_newpass_time TIMESTAMPTZ,
22 user_email_token TEXT,
23 user_email_token_expires TIMESTAMPTZ,
24 user_email_authenticated TIMESTAMPTZ,
26 user_touched TIMESTAMPTZ,
27 user_registration TIMESTAMPTZ,
28 user_editcount INTEGER
30 CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
32 -- Create a dummy user to satisfy fk contraints especially with revisions
34 VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
36 CREATE TABLE user_groups (
37 ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
38 ug_group TEXT NOT NULL
40 CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
42 CREATE TABLE user_newtalk (
43 user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
45 user_last_timestamp TIMESTAMPTZ
47 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
48 CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
51 CREATE SEQUENCE page_page_id_seq;
53 page_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('page_page_id_seq'),
54 page_namespace SMALLINT NOT NULL,
55 page_title TEXT NOT NULL,
56 page_restrictions TEXT,
57 page_counter BIGINT NOT NULL DEFAULT 0,
58 page_is_redirect SMALLINT NOT NULL DEFAULT 0,
59 page_is_new SMALLINT NOT NULL DEFAULT 0,
60 page_random NUMERIC(15,14) NOT NULL DEFAULT RANDOM(),
61 page_touched TIMESTAMPTZ,
62 page_latest INTEGER NOT NULL, -- FK?
63 page_len INTEGER NOT NULL
65 CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
66 CREATE INDEX page_main_title ON page (page_title) WHERE page_namespace = 0;
67 CREATE INDEX page_talk_title ON page (page_title) WHERE page_namespace = 1;
68 CREATE INDEX page_user_title ON page (page_title) WHERE page_namespace = 2;
69 CREATE INDEX page_utalk_title ON page (page_title) WHERE page_namespace = 3;
70 CREATE INDEX page_project_title ON page (page_title) WHERE page_namespace = 4;
71 CREATE INDEX page_mediawiki_title ON page (page_title) WHERE page_namespace = 8;
72 CREATE INDEX page_random_idx ON page (page_random);
73 CREATE INDEX page_len_idx ON page (page_len);
75 CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
78 DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
83 CREATE TRIGGER page_deleted AFTER DELETE ON page
84 FOR EACH ROW EXECUTE PROCEDURE page_deleted();
86 CREATE SEQUENCE revision_rev_id_seq;
87 CREATE TABLE revision (
88 rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('revision_rev_id_seq'),
89 rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
90 rev_text_id INTEGER NULL, -- FK
92 rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
93 rev_user_text TEXT NOT NULL,
94 rev_timestamp TIMESTAMPTZ NOT NULL,
95 rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
96 rev_deleted SMALLINT NOT NULL DEFAULT 0,
98 rev_parent_id INTEGER NULL
100 CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
101 CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
102 CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
103 CREATE INDEX rev_user_idx ON revision (rev_user);
104 CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
107 CREATE SEQUENCE text_old_id_seq;
108 CREATE TABLE pagecontent ( -- replaces reserved word 'text'
109 old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_seq'),
115 CREATE SEQUENCE page_restrictions_pr_id_seq;
116 CREATE TABLE page_restrictions (
117 pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'),
118 pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
119 pr_type TEXT NOT NULL,
120 pr_level TEXT NOT NULL,
121 pr_cascade SMALLINT NOT NULL,
122 pr_user INTEGER NULL,
123 pr_expiry TIMESTAMPTZ NULL
125 ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
127 CREATE TABLE page_props (
128 pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
129 pp_propname TEXT NOT NULL,
130 pp_value TEXT NOT NULL
132 ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
133 CREATE INDEX page_props_propname ON page_props (pp_propname);
135 CREATE TABLE archive (
136 ar_namespace SMALLINT NOT NULL,
137 ar_title TEXT NOT NULL,
138 ar_text TEXT, -- technically should be bytea, but not used anymore
139 ar_page_id INTEGER NULL,
140 ar_parent_id INTEGER NULL,
142 ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
143 ar_user_text TEXT NOT NULL,
144 ar_timestamp TIMESTAMPTZ NOT NULL,
145 ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
149 ar_deleted SMALLINT NOT NULL DEFAULT 0,
152 CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
153 CREATE INDEX archive_user_text ON archive (ar_user_text);
156 CREATE TABLE redirect (
157 rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
158 rd_namespace SMALLINT NOT NULL,
159 rd_title TEXT NOT NULL,
160 rd_interwiki TEXT NULL,
161 rd_fragment TEXT NULL
163 CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
166 CREATE TABLE pagelinks (
167 pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
168 pl_namespace SMALLINT NOT NULL,
169 pl_title TEXT NOT NULL
171 CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
172 CREATE INDEX pagelinks_title ON pagelinks (pl_title);
174 CREATE TABLE templatelinks (
175 tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
176 tl_namespace SMALLINT NOT NULL,
177 tl_title TEXT NOT NULL
179 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
180 CREATE INDEX templatelinks_from ON templatelinks (tl_from);
182 CREATE TABLE imagelinks (
183 il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
186 CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
188 CREATE TABLE categorylinks (
189 cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
191 cl_sortkey TEXT NULL,
192 cl_timestamp TIMESTAMPTZ NOT NULL,
193 cl_sortkey_prefix TEXT NOT NULL DEFAULT '',
194 cl_collation TEXT NOT NULL DEFAULT 0,
195 cl_type TEXT NOT NULL DEFAULT 'page'
197 CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
198 CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
200 CREATE TABLE externallinks (
201 el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
203 el_index TEXT NOT NULL
205 CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
206 CREATE INDEX externallinks_index ON externallinks (el_index);
208 CREATE TABLE external_user (
209 eu_local_id INTEGER NOT NULL PRIMARY KEY,
213 CREATE UNIQUE INDEX eu_external_id ON external_user (eu_external_id);
215 CREATE TABLE langlinks (
216 ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
220 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
221 CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);
224 CREATE TABLE site_stats (
225 ss_row_id INTEGER NOT NULL UNIQUE,
226 ss_total_views INTEGER DEFAULT 0,
227 ss_total_edits INTEGER DEFAULT 0,
228 ss_good_articles INTEGER DEFAULT 0,
229 ss_total_pages INTEGER DEFAULT -1,
230 ss_users INTEGER DEFAULT -1,
231 ss_active_users INTEGER DEFAULT -1,
232 ss_admins INTEGER DEFAULT -1,
233 ss_images INTEGER DEFAULT 0
236 CREATE TABLE hitcounter (
237 hc_id BIGINT NOT NULL
241 CREATE SEQUENCE ipblocks_ipb_id_seq;
242 CREATE TABLE ipblocks (
243 ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'),
244 ipb_address TEXT NULL,
245 ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
246 ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
247 ipb_by_text TEXT NOT NULL DEFAULT '',
248 ipb_reason TEXT NOT NULL,
249 ipb_timestamp TIMESTAMPTZ NOT NULL,
250 ipb_auto SMALLINT NOT NULL DEFAULT 0,
251 ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
252 ipb_create_account SMALLINT NOT NULL DEFAULT 1,
253 ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
254 ipb_expiry TIMESTAMPTZ NOT NULL,
255 ipb_range_start TEXT,
257 ipb_deleted SMALLINT NOT NULL DEFAULT 0,
258 ipb_block_email SMALLINT NOT NULL DEFAULT 0,
259 ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
262 CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
263 CREATE INDEX ipb_user ON ipblocks (ipb_user);
264 CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
268 img_name TEXT NOT NULL PRIMARY KEY,
269 img_size INTEGER NOT NULL,
270 img_width INTEGER NOT NULL,
271 img_height INTEGER NOT NULL,
272 img_metadata BYTEA NOT NULL DEFAULT '',
275 img_major_mime TEXT DEFAULT 'unknown',
276 img_minor_mime TEXT DEFAULT 'unknown',
277 img_description TEXT NOT NULL,
278 img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
279 img_user_text TEXT NOT NULL,
280 img_timestamp TIMESTAMPTZ,
281 img_sha1 TEXT NOT NULL DEFAULT ''
283 CREATE INDEX img_size_idx ON image (img_size);
284 CREATE INDEX img_timestamp_idx ON image (img_timestamp);
285 CREATE INDEX img_sha1 ON image (img_sha1);
287 CREATE TABLE oldimage (
288 oi_name TEXT NOT NULL,
289 oi_archive_name TEXT NOT NULL,
290 oi_size INTEGER NOT NULL,
291 oi_width INTEGER NOT NULL,
292 oi_height INTEGER NOT NULL,
293 oi_bits SMALLINT NULL,
295 oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
296 oi_user_text TEXT NOT NULL,
297 oi_timestamp TIMESTAMPTZ NULL,
298 oi_metadata BYTEA NOT NULL DEFAULT '',
299 oi_media_type TEXT NULL,
300 oi_major_mime TEXT NULL DEFAULT 'unknown',
301 oi_minor_mime TEXT NULL DEFAULT 'unknown',
302 oi_deleted SMALLINT NOT NULL DEFAULT 0,
303 oi_sha1 TEXT NOT NULL DEFAULT ''
305 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;
306 CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
307 CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
308 CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
311 CREATE SEQUENCE filearchive_fa_id_seq;
312 CREATE TABLE filearchive (
313 fa_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
314 fa_name TEXT NOT NULL,
315 fa_archive_name TEXT,
316 fa_storage_group TEXT,
318 fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
319 fa_deleted_timestamp TIMESTAMPTZ NOT NULL,
320 fa_deleted_reason TEXT,
321 fa_size INTEGER NOT NULL,
322 fa_width INTEGER NOT NULL,
323 fa_height INTEGER NOT NULL,
324 fa_metadata BYTEA NOT NULL DEFAULT '',
327 fa_major_mime TEXT DEFAULT 'unknown',
328 fa_minor_mime TEXT DEFAULT 'unknown',
329 fa_description TEXT NOT NULL,
330 fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
331 fa_user_text TEXT NOT NULL,
332 fa_timestamp TIMESTAMPTZ,
333 fa_deleted SMALLINT NOT NULL DEFAULT 0
335 CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
336 CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
337 CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
338 CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
341 CREATE SEQUENCE recentchanges_rc_id_seq;
342 CREATE TABLE recentchanges (
343 rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'),
344 rc_timestamp TIMESTAMPTZ NOT NULL,
345 rc_cur_time TIMESTAMPTZ NOT NULL,
346 rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
347 rc_user_text TEXT NOT NULL,
348 rc_namespace SMALLINT NOT NULL,
349 rc_title TEXT NOT NULL,
351 rc_minor SMALLINT NOT NULL DEFAULT 0,
352 rc_bot SMALLINT NOT NULL DEFAULT 0,
353 rc_new SMALLINT NOT NULL DEFAULT 0,
354 rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
355 rc_this_oldid INTEGER NOT NULL,
356 rc_last_oldid INTEGER NOT NULL,
357 rc_type SMALLINT NOT NULL DEFAULT 0,
358 rc_moved_to_ns SMALLINT,
359 rc_moved_to_title TEXT,
360 rc_patrolled SMALLINT NOT NULL DEFAULT 0,
364 rc_deleted SMALLINT NOT NULL DEFAULT 0,
365 rc_logid INTEGER NOT NULL DEFAULT 0,
370 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
371 CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0;
372 CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
373 CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
374 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
375 CREATE INDEX rc_ip ON recentchanges (rc_ip);
378 CREATE TABLE watchlist (
379 wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
380 wl_namespace SMALLINT NOT NULL DEFAULT 0,
381 wl_title TEXT NOT NULL,
382 wl_notificationtimestamp TIMESTAMPTZ
384 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
385 CREATE INDEX wl_user ON watchlist (wl_user);
388 math_inputhash BYTEA NOT NULL UNIQUE,
389 math_outputhash BYTEA NOT NULL,
390 math_html_conservativeness SMALLINT NOT NULL,
396 CREATE TABLE interwiki (
397 iw_prefix TEXT NOT NULL UNIQUE,
398 iw_url TEXT NOT NULL,
399 iw_local SMALLINT NOT NULL,
400 iw_trans SMALLINT NOT NULL DEFAULT 0,
401 iw_api TEXT NOT NULL DEFAULT '',
402 iw_wikiid TEXT NOT NULL DEFAULT ''
406 CREATE TABLE querycache (
407 qc_type TEXT NOT NULL,
408 qc_value INTEGER NOT NULL,
409 qc_namespace SMALLINT NOT NULL,
410 qc_title TEXT NOT NULL
412 CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
414 CREATE TABLE querycache_info (
415 qci_type TEXT UNIQUE,
416 qci_timestamp TIMESTAMPTZ NULL
419 CREATE TABLE querycachetwo (
420 qcc_type TEXT NOT NULL,
421 qcc_value INTEGER NOT NULL DEFAULT 0,
422 qcc_namespace INTEGER NOT NULL DEFAULT 0,
423 qcc_title TEXT NOT NULL DEFAULT '',
424 qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
425 qcc_titletwo TEXT NOT NULL DEFAULT ''
427 CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
428 CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
429 CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
431 CREATE TABLE objectcache (
433 value BYTEA NOT NULL DEFAULT '',
434 exptime TIMESTAMPTZ NOT NULL
436 CREATE INDEX objectcacache_exptime ON objectcache (exptime);
438 CREATE TABLE transcache (
439 tc_url TEXT NOT NULL UNIQUE,
440 tc_contents TEXT NOT NULL,
441 tc_time TIMESTAMPTZ NOT NULL
445 CREATE SEQUENCE logging_log_id_seq;
446 CREATE TABLE logging (
447 log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq'),
448 log_type TEXT NOT NULL,
449 log_action TEXT NOT NULL,
450 log_timestamp TIMESTAMPTZ NOT NULL,
451 log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
452 log_namespace SMALLINT NOT NULL,
453 log_title TEXT NOT NULL,
456 log_deleted SMALLINT NOT NULL DEFAULT 0,
457 log_user_text TEXT NOT NULL DEFAULT '',
460 CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
461 CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
462 CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
463 CREATE INDEX logging_times ON logging (log_timestamp);
464 CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp);
465 CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp);
467 CREATE TABLE log_search (
468 ls_field TEXT NOT NULL,
469 ls_value TEXT NOT NULL,
470 ls_log_id INTEGER NOT NULL DEFAULT 0,
471 PRIMARY KEY (ls_field,ls_value,ls_log_id)
473 CREATE INDEX ls_log_id ON log_search (ls_log_id);
475 CREATE SEQUENCE trackbacks_tb_id_seq;
476 CREATE TABLE trackbacks (
477 tb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
478 tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
479 tb_title TEXT NOT NULL,
480 tb_url TEXT NOT NULL,
484 CREATE INDEX trackback_page ON trackbacks (tb_page);
487 CREATE SEQUENCE job_job_id_seq;
489 job_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
490 job_cmd TEXT NOT NULL,
491 job_namespace SMALLINT NOT NULL,
492 job_title TEXT NOT NULL,
493 job_params TEXT NOT NULL
495 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
497 -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
498 -- Version 8.3 or higher only. Previous versions would need another parmeter for to_tsvector.
499 -- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.
501 ALTER TABLE page ADD titlevector tsvector;
502 CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
505 IF TG_OP = 'INSERT' THEN
506 NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' '));
507 ELSIF NEW.page_title != OLD.page_title THEN
508 NEW.titlevector := to_tsvector(REPLACE(NEW.page_title,'/',' '));
514 CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
515 FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
518 ALTER TABLE pagecontent ADD textvector tsvector;
519 CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
522 IF TG_OP = 'INSERT' THEN
523 NEW.textvector = to_tsvector(NEW.old_text);
524 ELSIF NEW.old_text != OLD.old_text THEN
525 NEW.textvector := to_tsvector(NEW.old_text);
531 CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
532 FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
534 -- These are added by the setup script due to version compatibility issues
535 -- If using 8.1, we switch from "gin" to "gist"
537 CREATE INDEX ts2_page_title ON page USING gin(titlevector);
538 CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
540 CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
542 INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
546 -- This table is not used unless profiling is turned on
547 CREATE TABLE profiling (
548 pf_count INTEGER NOT NULL DEFAULT 0,
549 pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
550 pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,
551 pf_name TEXT NOT NULL,
554 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
556 CREATE TABLE protected_titles (
557 pt_namespace SMALLINT NOT NULL,
558 pt_title TEXT NOT NULL,
559 pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
561 pt_timestamp TIMESTAMPTZ NOT NULL,
562 pt_expiry TIMESTAMPTZ NULL,
563 pt_create_perm TEXT NOT NULL DEFAULT ''
565 CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
568 CREATE TABLE updatelog (
569 ul_key TEXT NOT NULL PRIMARY KEY,
574 CREATE SEQUENCE category_cat_id_seq;
575 CREATE TABLE category (
576 cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_cat_id_seq'),
577 cat_title TEXT NOT NULL,
578 cat_pages INTEGER NOT NULL DEFAULT 0,
579 cat_subcats INTEGER NOT NULL DEFAULT 0,
580 cat_files INTEGER NOT NULL DEFAULT 0,
581 cat_hidden SMALLINT NOT NULL DEFAULT 0
583 CREATE UNIQUE INDEX category_title ON category(cat_title);
584 CREATE INDEX category_pages ON category(cat_pages);
586 CREATE TABLE change_tag (
587 ct_rc_id INTEGER NULL,
588 ct_log_id INTEGER NULL,
589 ct_rev_id INTEGER NULL,
590 ct_tag TEXT NOT NULL,
593 CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag);
594 CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag);
595 CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag);
596 CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
598 CREATE TABLE tag_summary (
599 ts_rc_id INTEGER NULL,
600 ts_log_id INTEGER NULL,
601 ts_rev_id INTEGER NULL,
602 ts_tags TEXT NOT NULL
604 CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id);
605 CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id);
606 CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id);
608 CREATE TABLE valid_tag (
609 vt_tag TEXT NOT NULL PRIMARY KEY
612 CREATE TABLE user_properties (
613 up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
614 up_property TEXT NOT NULL,
617 CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);
618 CREATE INDEX user_properties_property ON user_properties (up_property);
620 CREATE TABLE l10n_cache (
621 lc_lang TEXT NOT NULL,
622 lc_key TEXT NOT NULL,
623 lc_value TEXT NOT NULL
625 CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);
627 CREATE TABLE iwlinks (
628 iwl_from INTEGER NOT NULL DEFAULT 0,
629 iwl_prefix TEXT NOT NULL DEFAULT '',
630 iwl_title TEXT NOT NULL DEFAULT ''
632 CREATE UNIQUE INDEX iwl_from ON iwlinks (iwl_from, iwl_prefix, iwl_title);
633 CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_title, iwl_from);
635 CREATE TABLE msg_resource (
636 mr_resource TEXT NOT NULL,
637 mr_lang TEXT NOT NULL,
638 mr_blob TEXT NOT NULL,
639 mr_timestamp TIMESTAMPTZ NOT NULL
641 CREATE UNIQUE INDEX mr_resource_lang ON msg_resource (mr_resource, mr_lang);
643 CREATE TABLE msg_resource_links (
644 mrl_resource TEXT NOT NULL,
645 mrl_message TEXT NOT NULL
647 CREATE UNIQUE INDEX mrl_message_resource ON msg_resource_links (mrl_message, mrl_resource);
649 CREATE TABLE module_deps (
650 md_module TEXT NOT NULL,
651 md_skin TEXT NOT NULL,
652 md_deps TEXT NOT NULL
654 CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin);