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_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
user_ip TEXT NULL,
user_last_timestamp TIMESTAMPTZ
);
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 SEQUENCE revision_rev_id_seq;
CREATE TABLE revision (
rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('revision_rev_id_seq'),
- rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE,
+ 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 SMALLINT NOT NULL DEFAULT 0,
CREATE SEQUENCE page_restrictions_pr_id_seq;
CREATE TABLE page_restrictions (
pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'),
- pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE,
+ 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,
+ 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
);
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 SMALLINT NOT NULL DEFAULT 0,
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_interwiki TEXT NULL,
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_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 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 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
);
CREATE TABLE ipblocks (
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,
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 ''
oi_height INTEGER 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 NULL,
oi_metadata BYTEA NOT NULL DEFAULT '',
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;
+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);
fa_archive_name TEXT,
fa_storage_group TEXT,
fa_storage_key TEXT,
- fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ 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 INTEGER NOT NULL,
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 SMALLINT NOT NULL DEFAULT 0
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_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,
+ 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 SMALLINT NOT NULL DEFAULT 0,
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
iw_prefix TEXT NOT NULL UNIQUE,
iw_url TEXT NOT NULL,
iw_local SMALLINT NOT NULL,
- iw_trans SMALLINT NOT NULL DEFAULT 0
+ iw_trans SMALLINT NOT NULL DEFAULT 0,
+ iw_api TEXT NOT NULL DEFAULT '',
+ iw_wikiid TEXT NOT NULL DEFAULT ''
);
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,
CREATE SEQUENCE trackbacks_tb_id_seq;
CREATE TABLE trackbacks (
tb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
- tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
+ 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
--- Note: if version 8.3 or higher, we remove the 'default' arg
+-- 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;
$mw$
BEGIN
IF TG_OP = 'INSERT' THEN
- NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
+ NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' '));
ELSIF NEW.page_title != OLD.page_title THEN
- NEW.titlevector := to_tsvector('default',REPLACE(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;
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,
+ 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,
CREATE TABLE updatelog (
- ul_key TEXT NOT NULL PRIMARY KEY
+ ul_key TEXT NOT NULL PRIMARY KEY,
+ ul_value TEXT
);
);
CREATE TABLE user_properties (
- up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
+ 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 mediawiki_version (
- type TEXT NOT NULL,
- mw_version TEXT NOT NULL,
- notes TEXT NULL,
+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);
- 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,
+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);
- sql_version TEXT NULL,
- sql_date TEXT NULL,
- cdate TIMESTAMPTZ NOT NULL DEFAULT now()
+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);
-INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date)
- VALUES ('Creation','??','$LastChangedRevision: 59842 $','$LastChangedDate: 2009-12-09 06:32:17 +1100 (Wed, 09 Dec 2009) $');
+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 l10n_cache (
- lc_lang TEXT NOT NULL,
- lc_key TEXT NOT NULL,
- lc_value TEXT NOT NULL
+CREATE TABLE module_deps (
+ md_module TEXT NOT NULL,
+ md_skin TEXT NOT NULL,
+ md_deps TEXT NOT NULL
);
-CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);
+CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin);
scripts.mit.edu / autoinstalls / mediawiki.git / blobdiff