X-Git-Url: https://git.cyclocoop.org/%27.WWW_URL.%27admin/?a=blobdiff_plain;ds=sidebyside;f=maintenance%2Fpostgres%2Ftables.sql;h=446e352c614c0070393966a696185fc9564a10ce;hb=9ac33a362f9a8f461906454b3d9abf0aed165ac7;hp=1b5358df5bc547a6296976814621e95f87826436;hpb=20cfed18d902526219c3542f29a4073575c22715;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 1b5358df5b..446e352c61 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -34,13 +34,13 @@ INSERT INTO mwuser 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 ); @@ -68,6 +68,7 @@ CREATE INDEX page_talk_title ON page (page_title) WHERE page_namespace = 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); @@ -82,13 +83,13 @@ $mw$; 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 SMALLINT NOT NULL DEFAULT 0, @@ -103,18 +104,18 @@ CREATE INDEX rev_user_idx ON revision (rev_user); 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, @@ -124,7 +125,7 @@ CREATE TABLE page_restrictions ( 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 ); @@ -138,7 +139,7 @@ CREATE TABLE archive ( 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, @@ -153,52 +154,65 @@ CREATE INDEX archive_user_text ON archive (ar_user_text); 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 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 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 ); @@ -210,7 +224,7 @@ CREATE TABLE site_stats ( ss_row_id INTEGER NOT NULL UNIQUE, ss_total_views INTEGER DEFAULT 0, ss_total_edits INTEGER DEFAULT 0, - ss_good_articles INTEGER DEFAULT 0, + ss_good_articles INTEGER DEFAULT 0, ss_total_pages INTEGER DEFAULT -1, ss_users INTEGER DEFAULT -1, ss_active_users INTEGER DEFAULT -1, @@ -223,12 +237,12 @@ CREATE TABLE hitcounter ( ); -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, @@ -240,7 +254,9 @@ CREATE TABLE ipblocks ( ipb_range_start TEXT, ipb_range_end TEXT, ipb_deleted SMALLINT NOT NULL DEFAULT 0, - ipb_block_email SMALLINT NOT NULL DEFAULT 0 + ipb_block_email SMALLINT NOT NULL DEFAULT 0, + ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0 + ); 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); @@ -258,7 +274,7 @@ CREATE TABLE image ( 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 '' @@ -273,19 +289,19 @@ CREATE TABLE oldimage ( 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_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_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE 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); @@ -298,7 +314,7 @@ CREATE TABLE filearchive ( 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, @@ -310,7 +326,7 @@ CREATE TABLE filearchive ( 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 @@ -321,12 +337,12 @@ CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp); 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, @@ -334,7 +350,7 @@ CREATE TABLE recentchanges ( 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, @@ -351,7 +367,7 @@ CREATE TABLE recentchanges ( 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_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); @@ -359,7 +375,7 @@ CREATE INDEX rc_ip ON recentchanges (rc_ip); 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 @@ -380,7 +396,9 @@ CREATE TABLE interwiki ( 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 '' ); @@ -423,28 +441,40 @@ CREATE TABLE transcache ( ); -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 SMALLINT 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 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, @@ -525,7 +555,7 @@ 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, + 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, @@ -535,13 +565,14 @@ CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt CREATE TABLE updatelog ( - ul_key TEXT NOT NULL PRIMARY KEY + ul_key TEXT NOT NULL PRIMARY KEY, + ul_value TEXT ); -CREATE SEQUENCE category_id_seq; +CREATE SEQUENCE category_cat_id_seq; CREATE TABLE category ( - cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_id_seq'), + 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, @@ -551,24 +582,72 @@ CREATE TABLE category ( CREATE UNIQUE INDEX category_title ON category(cat_title); CREATE INDEX category_pages ON category(cat_pages); -CREATE TABLE mediawiki_version ( - type TEXT NOT NULL, - mw_version TEXT NOT NULL, - notes TEXT NULL, +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); - 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 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); - sql_version TEXT NULL, - sql_date TEXT NULL, - cdate TIMESTAMPTZ NOT NULL DEFAULT now() +CREATE TABLE valid_tag ( + vt_tag TEXT NOT NULL PRIMARY KEY ); -INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) - VALUES ('Creation','??','$LastChangedRevision$','$LastChangedDate$'); +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);