BEGIN;
SET client_min_messages = 'ERROR';
+DROP SEQUENCE IF EXISTS user_user_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS page_page_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS revision_rev_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS comment_comment_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS text_old_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS page_restrictions_pr_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS ipblocks_ipb_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS filearchive_fa_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS uploadstash_us_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS recentchanges_rc_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS watchlist_wl_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS archive_ar_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS externallinks_el_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS sites_site_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS change_tag_ct_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS tag_summary_ts_id_seq CASCADE;
+DROP FUNCTION IF EXISTS page_deleted() CASCADE;
+DROP FUNCTION IF EXISTS ts2_page_title() CASCADE;
+DROP FUNCTION IF EXISTS ts2_page_text() CASCADE;
+DROP FUNCTION IF EXISTS add_interwiki(TEXT,INT,SMALLINT) CASCADE;
+DROP TYPE IF EXISTS media_type CASCADE;
+
CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
CREATE TABLE mwuser ( -- replace reserved word 'user'
user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
user_email_token TEXT,
user_email_token_expires TIMESTAMPTZ,
user_email_authenticated TIMESTAMPTZ,
- user_options TEXT,
user_touched TIMESTAMPTZ,
user_registration TIMESTAMPTZ,
- user_editcount INTEGER
+ user_editcount INTEGER,
+ user_password_expires TIMESTAMPTZ NULL
);
CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
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 DEFERRABLE INITIALLY DEFERRED,
- ug_group TEXT NOT NULL
+ ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ ug_group TEXT NOT NULL,
+ ug_expiry TIMESTAMPTZ NULL,
+ PRIMARY KEY(ug_user, ug_group)
+);
+CREATE INDEX user_groups_group ON user_groups (ug_group);
+CREATE INDEX user_groups_expiry ON user_groups (ug_expiry);
+
+CREATE TABLE user_former_groups (
+ ufg_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ ufg_group TEXT NOT NULL
);
-CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
+CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group);
CREATE TABLE user_newtalk (
user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
+CREATE TABLE bot_passwords (
+ bp_user INTEGER NOT NULL,
+ bp_app_id TEXT NOT NULL,
+ bp_password TEXT NOT NULL,
+ bp_token TEXT NOT NULL,
+ bp_restrictions TEXT NOT NULL,
+ bp_grants TEXT NOT NULL,
+ PRIMARY KEY ( bp_user, bp_app_id )
+);
CREATE SEQUENCE page_page_id_seq;
CREATE TABLE page (
page_namespace SMALLINT NOT NULL,
page_title TEXT NOT NULL,
page_restrictions TEXT,
- page_counter BIGINT 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_links_updated TIMESTAMPTZ NULL,
page_latest INTEGER NOT NULL, -- FK?
- page_len INTEGER NOT NULL
+ page_len INTEGER NOT NULL,
+ page_content_model TEXT,
+ page_lang TEXT DEFAULT NULL
);
CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
-CREATE INDEX page_main_title ON page (page_title) WHERE page_namespace = 0;
-CREATE INDEX page_talk_title ON page (page_title) WHERE page_namespace = 1;
-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_main_title ON page (page_title text_pattern_ops) WHERE page_namespace = 0;
+CREATE INDEX page_talk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 1;
+CREATE INDEX page_user_title ON page (page_title text_pattern_ops) WHERE page_namespace = 2;
+CREATE INDEX page_utalk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 3;
+CREATE INDEX page_project_title ON page (page_title text_pattern_ops) WHERE page_namespace = 4;
+CREATE INDEX page_mediawiki_title ON page (page_title text_pattern_ops) 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 DEFERRABLE INITIALLY DEFERRED,
- rev_text_id INTEGER NULL, -- FK
- rev_comment TEXT,
- 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,
- rev_deleted SMALLINT NOT NULL DEFAULT 0,
- rev_len INTEGER NULL,
- rev_parent_id INTEGER NULL
+ 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 NOT NULL DEFAULT '',
+ 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,
+ rev_deleted SMALLINT NOT NULL DEFAULT 0,
+ rev_len INTEGER NULL,
+ rev_parent_id INTEGER NULL,
+ rev_sha1 TEXT NOT NULL DEFAULT '',
+ rev_content_model TEXT,
+ rev_content_format TEXT
);
CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
CREATE INDEX rev_user_idx ON revision (rev_user);
CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
+CREATE TABLE revision_comment_temp (
+ revcomment_rev INTEGER NOT NULL,
+ revcomment_comment_id INTEGER NOT NULL,
+ PRIMARY KEY (revcomment_rev, revcomment_comment_id)
+);
+CREATE UNIQUE INDEX revcomment_rev ON revision_comment_temp (revcomment_rev);
+
+CREATE SEQUENCE ip_changes_ipc_rev_id_seq;
+
+CREATE TABLE ip_changes (
+ ipc_rev_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('ip_changes_ipc_rev_id_seq'),
+ ipc_rev_timestamp TIMESTAMPTZ NOT NULL,
+ ipc_hex BYTEA NOT NULL DEFAULT ''
+);
+
+CREATE INDEX ipc_rev_timestamp ON ip_changes (ipc_rev_timestamp);
+CREATE INDEX ipc_hex_time ON ip_changes (ipc_hex,ipc_rev_timestamp);
CREATE SEQUENCE text_old_id_seq;
CREATE TABLE pagecontent ( -- replaces reserved word 'text'
);
+CREATE SEQUENCE comment_comment_id_seq;
+CREATE TABLE comment (
+ comment_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('comment_comment_id_seq'),
+ comment_hash INTEGER NOT NULL,
+ comment_text TEXT NOT NULL,
+ comment_data TEXT
+);
+CREATE INDEX comment_hash ON comment (comment_hash);
+
+
CREATE SEQUENCE page_restrictions_pr_id_seq;
CREATE TABLE page_restrictions (
pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'),
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
+ pp_value TEXT NOT NULL,
+ pp_sortkey FLOAT
);
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 UNIQUE INDEX pp_propname_page ON page_props (pp_propname,pp_page);
+CREATE INDEX pp_propname_sortkey_page ON page_props (pp_propname, pp_sortkey, pp_page) WHERE (pp_sortkey IS NOT NULL);
+CREATE SEQUENCE archive_ar_id_seq;
CREATE TABLE archive (
- ar_namespace SMALLINT NOT NULL,
- ar_title TEXT NOT NULL,
- 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 DEFERRABLE INITIALLY DEFERRED,
- ar_user_text TEXT NOT NULL,
- ar_timestamp TIMESTAMPTZ NOT NULL,
- ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
- ar_flags TEXT,
- ar_rev_id INTEGER,
- ar_text_id INTEGER,
- ar_deleted SMALLINT NOT NULL DEFAULT 0,
- ar_len INTEGER NULL
+ ar_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('archive_ar_id_seq'),
+ ar_namespace SMALLINT NOT NULL,
+ ar_title TEXT NOT NULL,
+ ar_text TEXT, -- technically should be bytea, but not used anymore
+ ar_page_id INTEGER NULL,
+ ar_parent_id INTEGER NULL,
+ ar_sha1 TEXT NOT NULL DEFAULT '',
+ ar_comment TEXT NOT NULL DEFAULT '',
+ ar_comment_id INTEGER NOT NULL DEFAULT 0,
+ 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,
+ ar_flags TEXT,
+ ar_rev_id INTEGER,
+ ar_text_id INTEGER,
+ ar_deleted SMALLINT NOT NULL DEFAULT 0,
+ ar_len INTEGER NULL,
+ ar_content_model TEXT,
+ ar_content_format TEXT
);
CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
CREATE INDEX archive_user_text ON archive (ar_user_text);
CREATE TABLE pagelinks (
pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ pl_from_namespace INTEGER NOT NULL DEFAULT 0,
pl_namespace SMALLINT NOT NULL,
pl_title TEXT NOT NULL
);
CREATE TABLE templatelinks (
tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ tl_from_namespace INTEGER NOT NULL DEFAULT 0,
tl_namespace SMALLINT NOT NULL,
tl_title TEXT NOT NULL
);
CREATE TABLE imagelinks (
il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ il_from_namespace INTEGER NOT NULL DEFAULT 0,
il_to TEXT NOT NULL
);
CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
+CREATE SEQUENCE externallinks_el_id_seq;
CREATE TABLE externallinks (
- 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
+ el_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'),
+ 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,
+ el_index_60 BYTEA NOT NULL DEFAULT ''
);
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 INDEX el_index_60 ON externallinks (el_index_60, el_id);
+CREATE INDEX el_from_index_60 ON externallinks (el_from, el_index_60, el_id);
CREATE TABLE langlinks (
ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
CREATE TABLE site_stats (
- ss_row_id INTEGER NOT NULL UNIQUE,
- ss_total_views INTEGER DEFAULT 0,
+ ss_row_id INTEGER NOT NULL PRIMARY KEY DEFAULT 0,
ss_total_edits INTEGER DEFAULT 0,
ss_good_articles INTEGER DEFAULT 0,
ss_total_pages INTEGER DEFAULT -1,
ss_images INTEGER DEFAULT 0
);
-CREATE TABLE hitcounter (
- hc_id BIGINT NOT NULL
-);
-
CREATE SEQUENCE ipblocks_ipb_id_seq;
CREATE TABLE ipblocks (
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_reason TEXT NOT NULL DEFAULT '',
+ ipb_reason_id INTEGER NOT NULL DEFAULT 0,
ipb_timestamp TIMESTAMPTZ NOT NULL,
ipb_auto SMALLINT NOT NULL DEFAULT 0,
ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
ipb_range_end TEXT,
ipb_deleted SMALLINT NOT NULL DEFAULT 0,
ipb_block_email SMALLINT NOT NULL DEFAULT 0,
- ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
+ ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0,
+ ipb_parent_block_id INTEGER NULL REFERENCES ipblocks(ipb_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
);
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);
+CREATE INDEX ipb_parent_block_id ON ipblocks (ipb_parent_block_id);
CREATE TABLE image (
img_media_type TEXT,
img_major_mime TEXT DEFAULT 'unknown',
img_minor_mime TEXT DEFAULT 'unknown',
- img_description TEXT NOT NULL,
+ img_description TEXT NOT NULL DEFAULT '',
img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
img_user_text TEXT NOT NULL,
img_timestamp TIMESTAMPTZ,
CREATE INDEX img_timestamp_idx ON image (img_timestamp);
CREATE INDEX img_sha1 ON image (img_sha1);
+CREATE TABLE image_comment_temp (
+ imgcomment_name TEXT NOT NULL,
+ imgcomment_description_id INTEGER NOT NULL,
+ PRIMARY KEY (imgcomment_name, imgcomment_description_id)
+);
+CREATE UNIQUE INDEX imgcomment_name ON image_comment_temp (imgcomment_name);
+
CREATE TABLE oldimage (
oi_name TEXT NOT NULL,
oi_archive_name TEXT NOT NULL,
oi_width INTEGER NOT NULL,
oi_height INTEGER NOT NULL,
oi_bits SMALLINT NULL,
- oi_description TEXT,
+ oi_description TEXT NOT NULL DEFAULT '',
+ oi_description_id INTEGER NOT NULL DEFAULT 0,
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,
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_deleted_reason TEXT NOT NULL DEFAULT '',
+ fa_deleted_reason_id INTEGER NOT NULL DEFAULT 0,
fa_size INTEGER NOT NULL,
fa_width INTEGER NOT NULL,
fa_height INTEGER NOT NULL,
fa_media_type TEXT,
fa_major_mime TEXT DEFAULT 'unknown',
fa_minor_mime TEXT DEFAULT 'unknown',
- fa_description TEXT NOT NULL,
+ fa_description TEXT NOT NULL DEFAULT '',
+ fa_description_id INTEGER NOT NULL DEFAULT 0,
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
+ fa_deleted SMALLINT NOT NULL DEFAULT 0,
+ fa_sha1 TEXT NOT NULL DEFAULT ''
);
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_notime ON filearchive (fa_deleted_timestamp);
CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
+CREATE INDEX fa_sha1 ON filearchive (fa_sha1);
+
+CREATE SEQUENCE uploadstash_us_id_seq;
+CREATE TYPE media_type AS ENUM ('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE','3D');
+
+CREATE TABLE uploadstash (
+ us_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('uploadstash_us_id_seq'),
+ us_user INTEGER,
+ us_key TEXT,
+ us_orig_path TEXT,
+ us_path TEXT,
+ us_props BYTEA,
+ us_source_type TEXT,
+ us_timestamp TIMESTAMPTZ,
+ us_status TEXT,
+ us_chunk_inx INTEGER NULL,
+ us_size INTEGER,
+ us_sha1 TEXT,
+ us_mime TEXT,
+ us_media_type media_type DEFAULT NULL,
+ us_image_width INTEGER,
+ us_image_height INTEGER,
+ us_image_bits SMALLINT
+);
+
+CREATE INDEX us_user_idx ON uploadstash (us_user);
+CREATE UNIQUE INDEX us_key_idx ON uploadstash (us_key);
+CREATE INDEX us_timestamp_idx ON uploadstash (us_timestamp);
CREATE SEQUENCE recentchanges_rc_id_seq;
CREATE TABLE recentchanges (
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_cur_time TIMESTAMPTZ 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_comment TEXT NOT NULL DEFAULT '',
+ rc_comment_id INTEGER NOT NULL DEFAULT 0,
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_cur_id INTEGER NULL,
rc_this_oldid INTEGER NOT NULL,
rc_last_oldid INTEGER NOT NULL,
rc_type SMALLINT NOT NULL DEFAULT 0,
- rc_moved_to_ns SMALLINT,
- rc_moved_to_title TEXT,
+ rc_source TEXT NOT NULL,
rc_patrolled SMALLINT NOT NULL DEFAULT 0,
rc_ip CIDR,
rc_old_len INTEGER,
CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id);
CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp);
CREATE INDEX rc_ip ON recentchanges (rc_ip);
+CREATE INDEX rc_name_type_patrolled_timestamp ON recentchanges (rc_namespace, rc_type, rc_patrolled, rc_timestamp);
+CREATE SEQUENCE watchlist_wl_id_seq;
CREATE TABLE watchlist (
+ wl_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('watchlist_wl_id_seq'),
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,
);
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,
- math_outputhash BYTEA NOT NULL,
- math_html_conservativeness SMALLINT NOT NULL,
- math_html TEXT,
- math_mathml TEXT
-);
+CREATE INDEX wl_user_notificationtimestamp ON watchlist (wl_user, wl_notificationtimestamp);
CREATE TABLE interwiki (
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_comment TEXT NOT NULL DEFAULT '',
+ log_comment_id INTEGER NOT NULL DEFAULT 0,
log_params TEXT,
log_deleted SMALLINT NOT NULL DEFAULT 0,
log_user_text TEXT NOT NULL DEFAULT '',
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 INDEX logging_user_text_type_time ON logging (log_user_text, log_type, log_timestamp);
+CREATE INDEX logging_user_text_time ON logging (log_user_text, log_timestamp);
CREATE TABLE log_search (
ls_field TEXT NOT NULL,
);
CREATE INDEX ls_log_id ON log_search (ls_log_id);
-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 DEFERRABLE INITIALLY DEFERRED,
- tb_title TEXT NOT NULL,
- tb_url TEXT NOT NULL,
- tb_ex TEXT,
- tb_name TEXT
-);
-CREATE INDEX trackback_page ON trackbacks (tb_page);
-
CREATE SEQUENCE job_job_id_seq;
CREATE TABLE job (
- job_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
- job_cmd TEXT NOT NULL,
- job_namespace SMALLINT NOT NULL,
- job_title TEXT NOT NULL,
- job_params TEXT NOT NULL
-);
+ job_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
+ job_cmd TEXT NOT NULL,
+ job_namespace SMALLINT NOT NULL,
+ job_title TEXT NOT NULL,
+ job_timestamp TIMESTAMPTZ,
+ job_params TEXT NOT NULL,
+ job_random INTEGER NOT NULL DEFAULT 0,
+ job_attempts INTEGER NOT NULL DEFAULT 0,
+ job_token TEXT NOT NULL DEFAULT '',
+ job_token_timestamp TIMESTAMPTZ,
+ job_sha1 TEXT NOT NULL DEFAULT ''
+);
+CREATE INDEX job_sha1 ON job (job_sha1);
+CREATE INDEX job_cmd_token ON job (job_cmd, job_token, job_random);
+CREATE INDEX job_cmd_token_id ON job (job_cmd, job_token, job_id);
CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
+CREATE INDEX job_timestamp_idx ON job (job_timestamp);
-- 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.
-- This table is not used unless profiling is turned on
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_time FLOAT NOT NULL DEFAULT 0,
+ pf_memory FLOAT NOT NULL DEFAULT 0,
pf_name TEXT NOT NULL,
pf_server TEXT NULL
);
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_reason TEXT NOT NULL DEFAULT '',
+ pt_reason_id INTEGER NOT NULL DEFAULT 0,
pt_timestamp TIMESTAMPTZ NOT NULL,
pt_expiry TIMESTAMPTZ NULL,
pt_create_perm TEXT NOT NULL DEFAULT ''
CREATE UNIQUE INDEX category_title ON category(cat_title);
CREATE INDEX category_pages ON category(cat_pages);
+CREATE SEQUENCE change_tag_ct_id_seq;
CREATE TABLE change_tag (
+ ct_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('change_tag_ct_id_seq'),
ct_rc_id INTEGER NULL,
ct_log_id INTEGER NULL,
ct_rev_id INTEGER NULL,
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 SEQUENCE tag_summary_ts_id_seq;
CREATE TABLE tag_summary (
- ts_rc_id INTEGER NULL,
- ts_log_id INTEGER NULL,
- ts_rev_id INTEGER NULL,
- ts_tags TEXT NOT NULL
+ ts_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('tag_summary_ts_id_seq'),
+ 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 TABLE l10n_cache (
lc_lang TEXT NOT NULL,
lc_key TEXT NOT NULL,
- lc_value TEXT NOT NULL
+ lc_value BYTEA NOT NULL
);
CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key);
);
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 UNIQUE INDEX iwl_prefix_from_title ON iwlinks (iwl_prefix, iwl_from, iwl_title);
CREATE TABLE module_deps (
md_module TEXT NOT NULL,
md_deps TEXT NOT NULL
);
CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin);
+
+CREATE SEQUENCE sites_site_id_seq;
+CREATE TABLE sites (
+ site_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('sites_site_id_seq'),
+ site_global_key TEXT NOT NULL,
+ site_type TEXT NOT NULL,
+ site_group TEXT NOT NULL,
+ site_source TEXT NOT NULL,
+ site_language TEXT NOT NULL,
+ site_protocol TEXT NOT NULL,
+ site_domain TEXT NOT NULL,
+ site_data TEXT NOT NULL,
+ site_forward SMALLINT NOT NULL,
+ site_config TEXT NOT NULL
+);
+CREATE UNIQUE INDEX site_global_key ON sites (site_global_key);
+CREATE INDEX site_type ON sites (site_type);
+CREATE INDEX site_group ON sites (site_group);
+CREATE INDEX site_source ON sites (site_source);
+CREATE INDEX site_language ON sites (site_language);
+CREATE INDEX site_protocol ON sites (site_protocol);
+CREATE INDEX site_domain ON sites (site_domain);
+CREATE INDEX site_forward ON sites (site_forward);
+
+CREATE TABLE site_identifiers (
+ si_site INTEGER NOT NULL,
+ si_type TEXT NOT NULL,
+ si_key TEXT NOT NULL
+);
+CREATE UNIQUE INDEX si_type_key ON site_identifiers (si_type, si_key);
+CREATE INDEX si_site ON site_identifiers (si_site);
+CREATE INDEX si_key ON site_identifiers (si_key);