-- This is the PostgreSQL version.
-- For information about each table, please see the notes in maintenance/tables.sql
-- Please make sure all dollar-quoting uses $mw$ at the start of the line
--- We can't use SERIAL everywhere: the sequence names are hard-coded into the PHP
--- TODO: Change CHAR to BOOL (still needed as CHAR due to some PHP code)
+-- TODO: Change CHAR/SMALLINT to BOOL (still used in a non-bool fashion in PHP code)
BEGIN;
SET client_min_messages = 'ERROR';
user_password TEXT,
user_newpassword TEXT,
user_newpass_time TIMESTAMPTZ,
- user_token CHAR(32),
+ user_token TEXT,
user_email TEXT,
- user_email_token CHAR(32),
+ user_email_token TEXT,
user_email_token_expires TIMESTAMPTZ,
user_email_authenticated TIMESTAMPTZ,
user_options TEXT,
VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
CREATE TABLE user_groups (
- ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
+ ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
ug_group TEXT NOT NULL
);
CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
CREATE TABLE user_newtalk (
- user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
- user_ip TEXT NULL
+ user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ user_ip TEXT NULL,
+ user_last_timestamp TIMESTAMPTZ
);
CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
page_title TEXT NOT NULL,
page_restrictions TEXT,
page_counter BIGINT NOT NULL DEFAULT 0,
- page_is_redirect CHAR NOT NULL DEFAULT 0,
- page_is_new CHAR NOT NULL DEFAULT 0,
+ page_is_redirect SMALLINT NOT NULL DEFAULT 0,
+ page_is_new SMALLINT NOT NULL DEFAULT 0,
page_random NUMERIC(15,14) NOT NULL DEFAULT RANDOM(),
page_touched TIMESTAMPTZ,
page_latest INTEGER NOT NULL, -- FK?
CREATE INDEX page_user_title ON page (page_title) WHERE page_namespace = 2;
CREATE INDEX page_utalk_title ON page (page_title) WHERE page_namespace = 3;
CREATE INDEX page_project_title ON page (page_title) WHERE page_namespace = 4;
+CREATE INDEX page_mediawiki_title ON page (page_title) WHERE page_namespace = 8;
CREATE INDEX page_random_idx ON page (page_random);
CREATE INDEX page_len_idx ON page (page_len);
CREATE TRIGGER page_deleted AFTER DELETE ON page
FOR EACH ROW EXECUTE PROCEDURE page_deleted();
-CREATE SEQUENCE rev_rev_id_val;
+CREATE SEQUENCE revision_rev_id_seq;
CREATE TABLE revision (
- rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('rev_rev_id_val'),
- rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE,
+ rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('revision_rev_id_seq'),
+ rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
rev_text_id INTEGER NULL, -- FK
rev_comment TEXT,
- rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT,
+ rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
rev_user_text TEXT NOT NULL,
rev_timestamp TIMESTAMPTZ NOT NULL,
- rev_minor_edit CHAR NOT NULL DEFAULT '0',
- rev_deleted CHAR NOT NULL DEFAULT '0',
+ rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
+ rev_deleted SMALLINT NOT NULL DEFAULT 0,
rev_len INTEGER NULL,
rev_parent_id INTEGER NULL
);
CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
-CREATE SEQUENCE text_old_id_val;
+CREATE SEQUENCE text_old_id_seq;
CREATE TABLE pagecontent ( -- replaces reserved word 'text'
- old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_val'),
+ old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_seq'),
old_text TEXT,
old_flags TEXT
);
-CREATE SEQUENCE pr_id_val;
+CREATE SEQUENCE page_restrictions_pr_id_seq;
CREATE TABLE page_restrictions (
- pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('pr_id_val'),
- pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE,
+ pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'),
+ pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
pr_type TEXT NOT NULL,
pr_level TEXT NOT NULL,
pr_cascade SMALLINT NOT NULL,
);
ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
+CREATE TABLE page_props (
+ pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ pp_propname TEXT NOT NULL,
+ pp_value TEXT NOT NULL
+);
+ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
+CREATE INDEX page_props_propname ON page_props (pp_propname);
CREATE TABLE archive (
ar_namespace SMALLINT NOT NULL,
ar_title TEXT NOT NULL,
- ar_text TEXT,
+ ar_text TEXT, -- technically should be bytea, but not used anymore
ar_page_id INTEGER NULL,
+ ar_parent_id INTEGER NULL,
ar_comment TEXT,
- ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
ar_user_text TEXT NOT NULL,
ar_timestamp TIMESTAMPTZ NOT NULL,
- ar_minor_edit CHAR NOT NULL DEFAULT '0',
+ ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
ar_flags TEXT,
ar_rev_id INTEGER,
ar_text_id INTEGER,
- ar_deleted INTEGER NOT NULL DEFAULT 0,
+ ar_deleted SMALLINT NOT NULL DEFAULT 0,
ar_len INTEGER NULL
);
CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
CREATE TABLE redirect (
- rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
+ rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
rd_namespace SMALLINT NOT NULL,
- rd_title TEXT NOT NULL
+ rd_title TEXT NOT NULL,
+ rd_interwiki TEXT NULL,
+ rd_fragment TEXT NULL
);
CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
CREATE TABLE pagelinks (
- pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
+ pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
pl_namespace SMALLINT NOT NULL,
pl_title TEXT NOT NULL
);
CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
+CREATE INDEX pagelinks_title ON pagelinks (pl_title);
CREATE TABLE templatelinks (
- tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
- tl_namespace TEXT NOT NULL,
+ tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ tl_namespace SMALLINT NOT NULL,
tl_title TEXT NOT NULL
);
CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
+CREATE INDEX templatelinks_from ON templatelinks (tl_from);
CREATE TABLE imagelinks (
- il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
+ il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
il_to TEXT NOT NULL
);
CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
CREATE TABLE categorylinks (
- cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
- cl_to TEXT NOT NULL,
- cl_sortkey TEXT,
- cl_timestamp TIMESTAMPTZ NOT NULL
+ cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ cl_to TEXT NOT NULL,
+ cl_sortkey TEXT NULL,
+ cl_timestamp TIMESTAMPTZ NOT NULL,
+ cl_sortkey_prefix TEXT NOT NULL DEFAULT '',
+ cl_collation TEXT NOT NULL DEFAULT 0,
+ cl_type TEXT NOT NULL DEFAULT 'page'
);
CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
CREATE TABLE externallinks (
- el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
+ el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
el_to TEXT NOT NULL,
el_index TEXT NOT NULL
);
CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
CREATE INDEX externallinks_index ON externallinks (el_index);
+CREATE TABLE external_user (
+ eu_local_id INTEGER NOT NULL PRIMARY KEY,
+ eu_external_id TEXT
+);
+
+CREATE UNIQUE INDEX eu_external_id ON external_user (eu_external_id);
+
CREATE TABLE langlinks (
- ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
+ ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
ll_lang TEXT,
ll_title TEXT
);
ss_good_articles INTEGER DEFAULT 0,
ss_total_pages INTEGER DEFAULT -1,
ss_users INTEGER DEFAULT -1,
+ ss_active_users INTEGER DEFAULT -1,
ss_admins INTEGER DEFAULT -1,
ss_images INTEGER DEFAULT 0
);
);
-CREATE SEQUENCE ipblocks_ipb_id_val;
+CREATE SEQUENCE ipblocks_ipb_id_seq;
CREATE TABLE ipblocks (
- ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'),
+ ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'),
ipb_address TEXT NULL,
- ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
- ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
+ ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+ ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ ipb_by_text TEXT NOT NULL DEFAULT '',
ipb_reason TEXT NOT NULL,
ipb_timestamp TIMESTAMPTZ NOT NULL,
- ipb_auto CHAR NOT NULL DEFAULT '0',
- ipb_anon_only CHAR NOT NULL DEFAULT '0',
- ipb_create_account CHAR NOT NULL DEFAULT '1',
- ipb_enable_autoblock CHAR NOT NULL DEFAULT '1',
+ ipb_auto SMALLINT NOT NULL DEFAULT 0,
+ ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
+ ipb_create_account SMALLINT NOT NULL DEFAULT 1,
+ ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
ipb_expiry TIMESTAMPTZ NOT NULL,
ipb_range_start TEXT,
ipb_range_end TEXT,
- ipb_deleted INTEGER NOT NULL DEFAULT 0,
- ipb_block_email CHAR NOT NULL DEFAULT '0'
+ ipb_deleted SMALLINT NOT NULL DEFAULT 0,
+ ipb_block_email SMALLINT NOT NULL DEFAULT 0,
+ ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
);
-CREATE INDEX ipb_address ON ipblocks (ipb_address);
+CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
CREATE INDEX ipb_user ON ipblocks (ipb_user);
CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end);
img_size INTEGER NOT NULL,
img_width INTEGER NOT NULL,
img_height INTEGER NOT NULL,
- img_metadata TEXT,
+ img_metadata BYTEA NOT NULL DEFAULT '',
img_bits SMALLINT,
img_media_type TEXT,
img_major_mime TEXT DEFAULT 'unknown',
img_minor_mime TEXT DEFAULT 'unknown',
img_description TEXT NOT NULL,
- img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
img_user_text TEXT NOT NULL,
img_timestamp TIMESTAMPTZ,
img_sha1 TEXT NOT NULL DEFAULT ''
CREATE INDEX img_sha1 ON image (img_sha1);
CREATE TABLE oldimage (
- oi_name TEXT NOT NULL REFERENCES image(img_name),
+ oi_name TEXT NOT NULL,
oi_archive_name TEXT NOT NULL,
oi_size INTEGER NOT NULL,
oi_width INTEGER NOT NULL,
oi_height INTEGER NOT NULL,
- oi_bits SMALLINT NOT NULL,
+ oi_bits SMALLINT NULL,
oi_description TEXT,
- oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
oi_user_text TEXT NOT NULL,
- oi_timestamp TIMESTAMPTZ NOT NULL,
+ oi_timestamp TIMESTAMPTZ NULL,
oi_metadata BYTEA NOT NULL DEFAULT '',
oi_media_type TEXT NULL,
- oi_major_mime TEXT NOT NULL DEFAULT 'unknown',
- oi_minor_mime TEXT NOT NULL DEFAULT 'unknown',
- oi_deleted CHAR NOT NULL DEFAULT '0',
+ oi_major_mime TEXT NULL DEFAULT 'unknown',
+ oi_minor_mime TEXT NULL DEFAULT 'unknown',
+ oi_deleted SMALLINT NOT NULL DEFAULT 0,
oi_sha1 TEXT NOT NULL DEFAULT ''
);
+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;
CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
+CREATE SEQUENCE filearchive_fa_id_seq;
CREATE TABLE filearchive (
- fa_id SERIAL NOT NULL PRIMARY KEY,
+ fa_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
fa_name TEXT NOT NULL,
fa_archive_name TEXT,
- fa_storage_group VARCHAR(16),
- fa_storage_key CHAR(64),
- fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ fa_storage_group TEXT,
+ fa_storage_key TEXT,
+ fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
fa_deleted_timestamp TIMESTAMPTZ NOT NULL,
fa_deleted_reason TEXT,
- fa_size SMALLINT NOT NULL,
- fa_width SMALLINT NOT NULL,
- fa_height SMALLINT NOT NULL,
- fa_metadata TEXT,
+ fa_size INTEGER NOT NULL,
+ fa_width INTEGER NOT NULL,
+ fa_height INTEGER NOT NULL,
+ fa_metadata BYTEA NOT NULL DEFAULT '',
fa_bits SMALLINT,
fa_media_type TEXT,
fa_major_mime TEXT DEFAULT 'unknown',
fa_minor_mime TEXT DEFAULT 'unknown',
fa_description TEXT NOT NULL,
- fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
fa_user_text TEXT NOT NULL,
fa_timestamp TIMESTAMPTZ,
- fa_deleted INTEGER NOT NULL DEFAULT 0
+ fa_deleted SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key);
CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
-CREATE SEQUENCE rc_rc_id_seq;
+CREATE SEQUENCE recentchanges_rc_id_seq;
CREATE TABLE recentchanges (
- rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
+ rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'),
rc_timestamp TIMESTAMPTZ NOT NULL,
rc_cur_time TIMESTAMPTZ NOT NULL,
- rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
rc_user_text TEXT NOT NULL,
rc_namespace SMALLINT NOT NULL,
rc_title TEXT NOT NULL,
rc_comment TEXT,
- rc_minor CHAR NOT NULL DEFAULT '0',
- rc_bot CHAR NOT NULL DEFAULT '0',
- rc_new CHAR NOT NULL DEFAULT '0',
- rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL,
+ rc_minor SMALLINT NOT NULL DEFAULT 0,
+ rc_bot SMALLINT NOT NULL DEFAULT 0,
+ rc_new SMALLINT NOT NULL DEFAULT 0,
+ rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
rc_this_oldid INTEGER NOT NULL,
rc_last_oldid INTEGER NOT NULL,
- rc_type CHAR NOT NULL DEFAULT '0',
+ rc_type SMALLINT NOT NULL DEFAULT 0,
rc_moved_to_ns SMALLINT,
rc_moved_to_title TEXT,
- rc_patrolled CHAR NOT NULL DEFAULT '0',
+ rc_patrolled SMALLINT NOT NULL DEFAULT 0,
rc_ip CIDR,
rc_old_len INTEGER,
rc_new_len INTEGER,
- rc_deleted INTEGER NOT NULL DEFAULT 0,
+ rc_deleted SMALLINT NOT NULL DEFAULT 0,
rc_logid INTEGER NOT NULL DEFAULT 0,
rc_log_type TEXT,
rc_log_action TEXT,
rc_params TEXT
);
CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
+CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0;
CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
CREATE TABLE watchlist (
- wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
+ wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
wl_namespace SMALLINT NOT NULL DEFAULT 0,
wl_title TEXT NOT NULL,
wl_notificationtimestamp TIMESTAMPTZ
);
CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
-
+CREATE INDEX wl_user ON watchlist (wl_user);
CREATE TABLE math (
math_inputhash BYTEA NOT NULL UNIQUE,
CREATE TABLE interwiki (
- iw_prefix TEXT NOT NULL UNIQUE,
- iw_url TEXT NOT NULL,
- iw_local CHAR NOT NULL,
- iw_trans CHAR NOT NULL DEFAULT '0'
+ iw_prefix TEXT NOT NULL UNIQUE,
+ iw_url TEXT NOT NULL,
+ iw_local SMALLINT NOT NULL,
+ iw_trans SMALLINT NOT NULL DEFAULT 0,
+ iw_api TEXT NOT NULL DEFAULT '',
+ iw_wikiid TEXT NOT NULL DEFAULT ''
);
CREATE TABLE querycache (
qc_type TEXT NOT NULL,
- qc_value SMALLINT NOT NULL,
+ qc_value INTEGER NOT NULL,
qc_namespace SMALLINT NOT NULL,
qc_title TEXT NOT NULL
);
CREATE TABLE querycachetwo (
qcc_type TEXT NOT NULL,
- qcc_value SMALLINT NOT NULL DEFAULT 0,
+ qcc_value INTEGER NOT NULL DEFAULT 0,
qcc_namespace INTEGER NOT NULL DEFAULT 0,
qcc_title TEXT NOT NULL DEFAULT '',
qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
CREATE TABLE objectcache (
- keyname CHAR(255) UNIQUE,
+ keyname TEXT UNIQUE,
value BYTEA NOT NULL DEFAULT '',
exptime TIMESTAMPTZ NOT NULL
);
);
-CREATE SEQUENCE log_log_id_seq;
+CREATE SEQUENCE logging_log_id_seq;
CREATE TABLE logging (
- log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('log_log_id_seq'),
+ log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq'),
log_type TEXT NOT NULL,
log_action TEXT NOT NULL,
log_timestamp TIMESTAMPTZ NOT NULL,
- log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
log_namespace SMALLINT NOT NULL,
log_title TEXT NOT NULL,
log_comment TEXT,
log_params TEXT,
- log_deleted INTEGER NOT NULL DEFAULT 0
+ log_deleted SMALLINT NOT NULL DEFAULT 0,
+ log_user_text TEXT NOT NULL DEFAULT '',
+ log_page INTEGER
);
CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
+CREATE INDEX logging_times ON logging (log_timestamp);
+CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp);
+CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp);
+CREATE TABLE log_search (
+ ls_field TEXT NOT NULL,
+ ls_value TEXT NOT NULL,
+ ls_log_id INTEGER NOT NULL DEFAULT 0,
+ PRIMARY KEY (ls_field,ls_value,ls_log_id)
+);
+CREATE INDEX ls_log_id ON log_search (ls_log_id);
+CREATE SEQUENCE trackbacks_tb_id_seq;
CREATE TABLE trackbacks (
- tb_id SERIAL NOT NULL PRIMARY KEY,
- tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
+ tb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
+ tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
tb_title TEXT NOT NULL,
tb_url TEXT NOT NULL,
tb_ex TEXT,
CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
-- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
+-- Version 8.3 or higher only. Previous versions would need another parmeter for to_tsvector.
+-- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.
ALTER TABLE page ADD titlevector tsvector;
CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
$mw$
BEGIN
IF TG_OP = 'INSERT' THEN
- NEW.titlevector = to_tsvector('default',NEW.page_title);
+ NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' '));
ELSIF NEW.page_title != OLD.page_title THEN
- NEW.titlevector := to_tsvector('default',NEW.page_title);
+ NEW.titlevector := to_tsvector(REPLACE(NEW.page_title,'/',' '));
END IF;
RETURN NEW;
END;
$mw$
BEGIN
IF TG_OP = 'INSERT' THEN
- NEW.textvector = to_tsvector('default',NEW.old_text);
+ NEW.textvector = to_tsvector(NEW.old_text);
ELSIF NEW.old_text != OLD.old_text THEN
- NEW.textvector := to_tsvector('default',NEW.old_text);
+ NEW.textvector := to_tsvector(NEW.old_text);
END IF;
RETURN NEW;
END;
FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
-- These are added by the setup script due to version compatibility issues
--- If using 8.1, switch from "gin" to "gist"
--- CREATE INDEX ts2_page_title ON page USING gin(titlevector);
--- CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
+-- If using 8.1, we switch from "gin" to "gist"
+
+CREATE INDEX ts2_page_title ON page USING gin(titlevector);
+CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
-CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS
+CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
$mw$
INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
SELECT 1;
CREATE TABLE profiling (
pf_count INTEGER NOT NULL DEFAULT 0,
pf_time NUMERIC(18,10) NOT NULL DEFAULT 0,
+ pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0,
pf_name TEXT NOT NULL,
pf_server TEXT NULL
);
CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
+CREATE TABLE protected_titles (
+ pt_namespace SMALLINT NOT NULL,
+ pt_title TEXT NOT NULL,
+ pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+ pt_reason TEXT NULL,
+ pt_timestamp TIMESTAMPTZ NOT NULL,
+ pt_expiry TIMESTAMPTZ NULL,
+ pt_create_perm TEXT NOT NULL DEFAULT ''
+);
+CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
+
-CREATE TABLE mediawiki_version (
- type TEXT NOT NULL,
- mw_version TEXT NOT NULL,
- notes TEXT NULL,
+CREATE TABLE updatelog (
+ ul_key TEXT NOT NULL PRIMARY KEY,
+ ul_value TEXT
+);
- pg_version TEXT NULL,
- pg_dbname TEXT NULL,
- pg_user TEXT NULL,
- pg_port TEXT NULL,
- mw_schema TEXT NULL,
- ts2_schema TEXT NULL,
- ctype TEXT NULL,
- sql_version TEXT NULL,
- sql_date TEXT NULL,
- cdate TIMESTAMPTZ NOT NULL DEFAULT now()
+CREATE SEQUENCE category_cat_id_seq;
+CREATE TABLE category (
+ cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_cat_id_seq'),
+ cat_title TEXT NOT NULL,
+ cat_pages INTEGER NOT NULL DEFAULT 0,
+ cat_subcats INTEGER NOT NULL DEFAULT 0,
+ cat_files INTEGER NOT NULL DEFAULT 0,
+ cat_hidden SMALLINT NOT NULL DEFAULT 0
);
+CREATE UNIQUE INDEX category_title ON category(cat_title);
+CREATE INDEX category_pages ON category(cat_pages);
-INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date)
- VALUES ('Creation','??','$LastChangedRevision: 25527 $','$LastChangedDate: 2007-09-05 04:14:18 -0400 (Wed, 05 Sep 2007) $');
+CREATE TABLE change_tag (
+ ct_rc_id INTEGER NULL,
+ ct_log_id INTEGER NULL,
+ ct_rev_id INTEGER NULL,
+ ct_tag TEXT NOT NULL,
+ ct_params TEXT NULL
+);
+CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag);
+CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag);
+CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag);
+CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
+CREATE TABLE tag_summary (
+ ts_rc_id INTEGER NULL,
+ ts_log_id INTEGER NULL,
+ ts_rev_id INTEGER NULL,
+ ts_tags TEXT NOT NULL
+);
+CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id);
+CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id);
+CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id);
+CREATE TABLE valid_tag (
+ vt_tag TEXT NOT NULL PRIMARY KEY
+);
+
+CREATE TABLE user_properties (
+ up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ up_property TEXT NOT NULL,
+ up_value TEXT
+);
+CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);
+CREATE INDEX user_properties_property ON user_properties (up_property);
+
+CREATE TABLE l10n_cache (
+ lc_lang TEXT NOT NULL,
+ lc_key TEXT NOT NULL,
+ lc_value TEXT NOT NULL
+);
+CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);
+
+CREATE TABLE iwlinks (
+ iwl_from INTEGER NOT NULL DEFAULT 0,
+ iwl_prefix TEXT NOT NULL DEFAULT '',
+ iwl_title TEXT NOT NULL DEFAULT ''
+);
+CREATE UNIQUE INDEX iwl_from ON iwlinks (iwl_from, iwl_prefix, iwl_title);
+CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_title, iwl_from);
+
+CREATE TABLE msg_resource (
+ mr_resource TEXT NOT NULL,
+ mr_lang TEXT NOT NULL,
+ mr_blob TEXT NOT NULL,
+ mr_timestamp TIMESTAMPTZ NOT NULL
+);
+CREATE UNIQUE INDEX mr_resource_lang ON msg_resource (mr_resource, mr_lang);
+
+CREATE TABLE msg_resource_links (
+ mrl_resource TEXT NOT NULL,
+ mrl_message TEXT NOT NULL
+);
+CREATE UNIQUE INDEX mrl_message_resource ON msg_resource_links (mrl_message, mrl_resource);
+
+CREATE TABLE module_deps (
+ md_module TEXT NOT NULL,
+ md_skin TEXT NOT NULL,
+ md_deps TEXT NOT NULL
+);
+CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin);
scripts.mit.edu / autoinstallsdev / mediawiki.git / blobdiff