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,
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,
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_random_idx ON page (page_random);
72 CREATE INDEX page_len_idx ON page (page_len);
74 CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
77 DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
82 CREATE TRIGGER page_deleted AFTER DELETE ON page
83 FOR EACH ROW EXECUTE PROCEDURE page_deleted();
85 CREATE SEQUENCE rev_rev_id_val;
86 CREATE TABLE revision (
87 rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('rev_rev_id_val'),
88 rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE,
89 rev_text_id INTEGER NULL, -- FK
91 rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT,
92 rev_user_text TEXT NOT NULL,
93 rev_timestamp TIMESTAMPTZ NOT NULL,
94 rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
95 rev_deleted SMALLINT NOT NULL DEFAULT 0,
97 rev_parent_id INTEGER NULL
99 CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
100 CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
101 CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp);
102 CREATE INDEX rev_user_idx ON revision (rev_user);
103 CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
106 CREATE SEQUENCE text_old_id_val;
107 CREATE TABLE pagecontent ( -- replaces reserved word 'text'
108 old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_val'),
114 CREATE SEQUENCE pr_id_val;
115 CREATE TABLE page_restrictions (
116 pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('pr_id_val'),
117 pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE,
118 pr_type TEXT NOT NULL,
119 pr_level TEXT NOT NULL,
120 pr_cascade SMALLINT NOT NULL,
121 pr_user INTEGER NULL,
122 pr_expiry TIMESTAMPTZ NULL
124 ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
126 CREATE TABLE page_props (
127 pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
128 pp_propname TEXT NOT NULL,
129 pp_value TEXT NOT NULL
131 ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
132 CREATE INDEX page_props_propname ON page_props (pp_propname);
134 CREATE TABLE archive (
135 ar_namespace SMALLINT NOT NULL,
136 ar_title TEXT NOT NULL,
137 ar_text TEXT, -- technically should be bytea, but not used anymore
138 ar_page_id INTEGER NULL,
139 ar_parent_id INTEGER NULL,
141 ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
142 ar_user_text TEXT NOT NULL,
143 ar_timestamp TIMESTAMPTZ NOT NULL,
144 ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
148 ar_deleted SMALLINT NOT NULL DEFAULT 0,
151 CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
152 CREATE INDEX archive_user_text ON archive (ar_user_text);
155 CREATE TABLE redirect (
156 rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
157 rd_namespace SMALLINT NOT NULL,
158 rd_title TEXT NOT NULL
160 CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
163 CREATE TABLE pagelinks (
164 pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
165 pl_namespace SMALLINT NOT NULL,
166 pl_title TEXT NOT NULL
168 CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
170 CREATE TABLE templatelinks (
171 tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
172 tl_namespace SMALLINT NOT NULL,
173 tl_title TEXT NOT NULL
175 CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
176 CREATE INDEX templatelinks_from ON templatelinks (tl_from);
178 CREATE TABLE imagelinks (
179 il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
182 CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
184 CREATE TABLE categorylinks (
185 cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
188 cl_timestamp TIMESTAMPTZ NOT NULL
190 CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
191 CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
193 CREATE TABLE externallinks (
194 el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
196 el_index TEXT NOT NULL
198 CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
199 CREATE INDEX externallinks_index ON externallinks (el_index);
201 CREATE TABLE langlinks (
202 ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
206 CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
207 CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title);
210 CREATE TABLE site_stats (
211 ss_row_id INTEGER NOT NULL UNIQUE,
212 ss_total_views INTEGER DEFAULT 0,
213 ss_total_edits INTEGER DEFAULT 0,
214 ss_good_articles INTEGER DEFAULT 0,
215 ss_total_pages INTEGER DEFAULT -1,
216 ss_users INTEGER DEFAULT -1,
217 ss_active_users INTEGER DEFAULT -1,
218 ss_admins INTEGER DEFAULT -1,
219 ss_images INTEGER DEFAULT 0
222 CREATE TABLE hitcounter (
223 hc_id BIGINT NOT NULL
227 CREATE SEQUENCE ipblocks_ipb_id_val;
228 CREATE TABLE ipblocks (
229 ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'),
230 ipb_address TEXT NULL,
231 ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
232 ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
233 ipb_by_text TEXT NOT NULL DEFAULT '',
234 ipb_reason TEXT NOT NULL,
235 ipb_timestamp TIMESTAMPTZ NOT NULL,
236 ipb_auto SMALLINT NOT NULL DEFAULT 0,
237 ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
238 ipb_create_account SMALLINT NOT NULL DEFAULT 1,
239 ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
240 ipb_expiry TIMESTAMPTZ NOT NULL,
241 ipb_range_start TEXT,
243 ipb_deleted SMALLINT NOT NULL DEFAULT 0,
244 ipb_block_email SMALLINT NOT NULL DEFAULT 0,
245 ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
248 CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
249 CREATE INDEX ipb_user ON ipblocks (ipb_user);
250 CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
254 img_name TEXT NOT NULL PRIMARY KEY,
255 img_size INTEGER NOT NULL,
256 img_width INTEGER NOT NULL,
257 img_height INTEGER NOT NULL,
258 img_metadata BYTEA NOT NULL DEFAULT '',
261 img_major_mime TEXT DEFAULT 'unknown',
262 img_minor_mime TEXT DEFAULT 'unknown',
263 img_description TEXT NOT NULL,
264 img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
265 img_user_text TEXT NOT NULL,
266 img_timestamp TIMESTAMPTZ,
267 img_sha1 TEXT NOT NULL DEFAULT ''
269 CREATE INDEX img_size_idx ON image (img_size);
270 CREATE INDEX img_timestamp_idx ON image (img_timestamp);
271 CREATE INDEX img_sha1 ON image (img_sha1);
273 CREATE TABLE oldimage (
274 oi_name TEXT NOT NULL,
275 oi_archive_name TEXT NOT NULL,
276 oi_size INTEGER NOT NULL,
277 oi_width INTEGER NOT NULL,
278 oi_height INTEGER NOT NULL,
279 oi_bits SMALLINT NULL,
281 oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
282 oi_user_text TEXT NOT NULL,
283 oi_timestamp TIMESTAMPTZ NULL,
284 oi_metadata BYTEA NOT NULL DEFAULT '',
285 oi_media_type TEXT NULL,
286 oi_major_mime TEXT NULL DEFAULT 'unknown',
287 oi_minor_mime TEXT NULL DEFAULT 'unknown',
288 oi_deleted SMALLINT NOT NULL DEFAULT 0,
289 oi_sha1 TEXT NOT NULL DEFAULT ''
291 ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE;
292 CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
293 CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
294 CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
297 CREATE SEQUENCE filearchive_fa_id_seq;
298 CREATE TABLE filearchive (
299 fa_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
300 fa_name TEXT NOT NULL,
301 fa_archive_name TEXT,
302 fa_storage_group TEXT,
304 fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
305 fa_deleted_timestamp TIMESTAMPTZ NOT NULL,
306 fa_deleted_reason TEXT,
307 fa_size INTEGER NOT NULL,
308 fa_width INTEGER NOT NULL,
309 fa_height INTEGER NOT NULL,
310 fa_metadata BYTEA NOT NULL DEFAULT '',
313 fa_major_mime TEXT DEFAULT 'unknown',
314 fa_minor_mime TEXT DEFAULT 'unknown',
315 fa_description TEXT NOT NULL,
316 fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
317 fa_user_text TEXT NOT NULL,
318 fa_timestamp TIMESTAMPTZ,
319 fa_deleted SMALLINT NOT NULL DEFAULT 0
321 CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
322 CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
323 CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
324 CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
327 CREATE SEQUENCE rc_rc_id_seq;
328 CREATE TABLE recentchanges (
329 rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
330 rc_timestamp TIMESTAMPTZ NOT NULL,
331 rc_cur_time TIMESTAMPTZ NOT NULL,
332 rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
333 rc_user_text TEXT NOT NULL,
334 rc_namespace SMALLINT NOT NULL,
335 rc_title TEXT NOT NULL,
337 rc_minor SMALLINT NOT NULL DEFAULT 0,
338 rc_bot SMALLINT NOT NULL DEFAULT 0,
339 rc_new SMALLINT NOT NULL DEFAULT 0,
340 rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL,
341 rc_this_oldid INTEGER NOT NULL,
342 rc_last_oldid INTEGER NOT NULL,
343 rc_type SMALLINT NOT NULL DEFAULT 0,
344 rc_moved_to_ns SMALLINT,
345 rc_moved_to_title TEXT,
346 rc_patrolled SMALLINT NOT NULL DEFAULT 0,
350 rc_deleted SMALLINT NOT NULL DEFAULT 0,
351 rc_logid INTEGER NOT NULL DEFAULT 0,
356 CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
357 CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0;
358 CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
359 CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
360 CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
361 CREATE INDEX rc_ip ON recentchanges (rc_ip);
364 CREATE TABLE watchlist (
365 wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
366 wl_namespace SMALLINT NOT NULL DEFAULT 0,
367 wl_title TEXT NOT NULL,
368 wl_notificationtimestamp TIMESTAMPTZ
370 CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
371 CREATE INDEX wl_user ON watchlist (wl_user);
374 math_inputhash BYTEA NOT NULL UNIQUE,
375 math_outputhash BYTEA NOT NULL,
376 math_html_conservativeness SMALLINT NOT NULL,
382 CREATE TABLE interwiki (
383 iw_prefix TEXT NOT NULL UNIQUE,
384 iw_url TEXT NOT NULL,
385 iw_local SMALLINT NOT NULL,
386 iw_trans SMALLINT NOT NULL DEFAULT 0
390 CREATE TABLE querycache (
391 qc_type TEXT NOT NULL,
392 qc_value INTEGER NOT NULL,
393 qc_namespace SMALLINT NOT NULL,
394 qc_title TEXT NOT NULL
396 CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
398 CREATE TABLE querycache_info (
399 qci_type TEXT UNIQUE,
400 qci_timestamp TIMESTAMPTZ NULL
403 CREATE TABLE querycachetwo (
404 qcc_type TEXT NOT NULL,
405 qcc_value INTEGER NOT NULL DEFAULT 0,
406 qcc_namespace INTEGER NOT NULL DEFAULT 0,
407 qcc_title TEXT NOT NULL DEFAULT '',
408 qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
409 qcc_titletwo TEXT NOT NULL DEFAULT ''
411 CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value);
412 CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title);
413 CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
415 CREATE TABLE objectcache (
417 value BYTEA NOT NULL DEFAULT '',
418 exptime TIMESTAMPTZ NOT NULL
420 CREATE INDEX objectcacache_exptime ON objectcache (exptime);
422 CREATE TABLE transcache (
423 tc_url TEXT NOT NULL UNIQUE,
424 tc_contents TEXT NOT NULL,
425 tc_time TIMESTAMPTZ NOT NULL
429 CREATE SEQUENCE log_log_id_seq;
430 CREATE TABLE logging (
431 log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('log_log_id_seq'),
432 log_type TEXT NOT NULL,
433 log_action TEXT NOT NULL,
434 log_timestamp TIMESTAMPTZ NOT NULL,
435 log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
436 log_namespace SMALLINT NOT NULL,
437 log_title TEXT NOT NULL,
440 log_deleted SMALLINT NOT NULL DEFAULT 0,
441 log_user_text TEXT NOT NULL DEFAULT '',
444 CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
445 CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
446 CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
447 CREATE INDEX logging_times ON logging (log_timestamp);
448 CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp);
449 CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp);
451 CREATE TABLE log_search (
452 ls_field TEXT NOT NULL,
453 ls_value TEXT NOT NULL,
454 ls_log_id INTEGER NOT NULL DEFAULT 0,
455 PRIMARY KEY (ls_field,ls_value,ls_log_id)
457 CREATE INDEX ls_log_id ON log_search (ls_log_id);
459 CREATE SEQUENCE trackbacks_tb_id_seq;
460 CREATE TABLE trackbacks (
461 tb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
462 tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
463 tb_title TEXT NOT NULL,
464 tb_url TEXT NOT NULL,
468 CREATE INDEX trackback_page ON trackbacks (tb_page);
471 CREATE SEQUENCE job_job_id_seq;
473 job_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
474 job_cmd TEXT NOT NULL,
475 job_namespace SMALLINT NOT NULL,
476 job_title TEXT NOT NULL,
477 job_params TEXT NOT NULL
479 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
481 -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
482 -- Note: if version 8.3 or higher, we remove the 'default' arg
483 -- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.
485 ALTER TABLE page ADD titlevector tsvector;
486 CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
489 IF TG_OP = 'INSERT' THEN
490 NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
491 ELSIF NEW.page_title != OLD.page_title THEN
492 NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
498 CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
499 FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
502 ALTER TABLE pagecontent ADD textvector tsvector;
503 CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
506 IF TG_OP = 'INSERT' THEN
507 NEW.textvector = to_tsvector('default',NEW.old_text);
508 ELSIF NEW.old_text != OLD.old_text THEN
509 NEW.textvector := to_tsvector('default',NEW.old_text);
515 CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
516 FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
518 -- These are added by the setup script due to version compatibility issues
519 -- If using 8.1, we switch from "gin" to "gist"
521 CREATE INDEX ts2_page_title ON page USING gin(titlevector);
522 CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
524 CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
526 INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
530 -- This table is not used unless profiling is turned on
531 CREATE TABLE profiling (
532 pf_count INTEGER NOT NULL DEFAULT 0,
533 pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
534 pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,
535 pf_name TEXT NOT NULL,
538 CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
540 CREATE TABLE protected_titles (
541 pt_namespace SMALLINT NOT NULL,
542 pt_title TEXT NOT NULL,
543 pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
545 pt_timestamp TIMESTAMPTZ NOT NULL,
546 pt_expiry TIMESTAMPTZ NULL,
547 pt_create_perm TEXT NOT NULL DEFAULT ''
549 CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
552 CREATE TABLE updatelog (
553 ul_key TEXT NOT NULL PRIMARY KEY
557 CREATE SEQUENCE category_id_seq;
558 CREATE TABLE category (
559 cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_id_seq'),
560 cat_title TEXT NOT NULL,
561 cat_pages INTEGER NOT NULL DEFAULT 0,
562 cat_subcats INTEGER NOT NULL DEFAULT 0,
563 cat_files INTEGER NOT NULL DEFAULT 0,
564 cat_hidden SMALLINT NOT NULL DEFAULT 0
566 CREATE UNIQUE INDEX category_title ON category(cat_title);
567 CREATE INDEX category_pages ON category(cat_pages);
569 CREATE TABLE change_tag (
570 ct_rc_id INTEGER NULL,
571 ct_log_id INTEGER NULL,
572 ct_rev_id INTEGER NULL,
573 ct_tag TEXT NOT NULL,
576 CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag);
577 CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag);
578 CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag);
579 CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
581 CREATE TABLE tag_summary (
582 ts_rc_id INTEGER NULL,
583 ts_log_id INTEGER NULL,
584 ts_rev_id INTEGER NULL,
585 ts_tags TEXT NOT NULL
587 CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id);
588 CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id);
589 CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id);
591 CREATE TABLE valid_tag (
592 vt_tag TEXT NOT NULL PRIMARY KEY
595 CREATE TABLE user_properties (
596 up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
597 up_property TEXT NOT NULL,
600 CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);
601 CREATE INDEX user_properties_property ON user_properties (up_property);
603 CREATE TABLE mediawiki_version (
605 mw_version TEXT NOT NULL,
608 pg_version TEXT NULL,
613 ts2_schema TEXT NULL,
616 sql_version TEXT NULL,
618 cdate TIMESTAMPTZ NOT NULL DEFAULT now()
621 INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date)
622 VALUES ('Creation','??','$LastChangedRevision$','$LastChangedDate$');
624 CREATE TABLE l10n_cache (
625 lc_lang TEXT NOT NULL,
626 lc_key TEXT NOT NULL,
627 lc_value TEXT NOT NULL
629 CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);