X-Git-Url: https://git.cyclocoop.org/%27.WWW_URL.%27admin/?a=blobdiff_plain;f=maintenance%2Fpostgres%2Ftables.sql;h=2dd02da902c1b3f91172906ac7028847d291301c;hb=2a6c9a1dfc96d67549d18c6ca1db7155c778b08e;hp=5f925b665660c325f47d63b34647d92a4e5b794e;hpb=1dfb44b51d2ebfb6c46ad1d8a321bebf8a34d1fd;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 5f925b6656..2dd02da902 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -4,8 +4,7 @@ -- 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'; @@ -26,7 +25,8 @@ CREATE TABLE mwuser ( -- replace reserved word 'user' user_options TEXT, user_touched TIMESTAMPTZ, user_registration TIMESTAMPTZ, - user_editcount INTEGER + user_editcount INTEGER, + user_hidden SMALLINT NOT NULL DEFAULT 0 ); CREATE INDEX user_email_token_idx ON mwuser (user_email_token); @@ -41,8 +41,9 @@ CREATE TABLE user_groups ( 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, + 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); @@ -55,8 +56,8 @@ CREATE TABLE page ( 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? @@ -91,8 +92,8 @@ CREATE TABLE revision ( rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT, 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 ); @@ -123,17 +124,25 @@ 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_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, -- 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_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, @@ -165,6 +174,7 @@ CREATE TABLE templatelinks ( 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, @@ -202,9 +212,10 @@ 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, ss_admins INTEGER DEFAULT -1, ss_images INTEGER DEFAULT 0 ); @@ -220,20 +231,22 @@ CREATE TABLE ipblocks ( 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_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 CHAR 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); @@ -282,8 +295,9 @@ 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 TEXT, @@ -292,8 +306,8 @@ CREATE TABLE filearchive ( fa_deleted_timestamp TIMESTAMPTZ NOT NULL, fa_deleted_reason TEXT, fa_size INTEGER NOT NULL, - fa_width SMALLINT NOT NULL, - fa_height SMALLINT 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, @@ -321,16 +335,16 @@ CREATE TABLE recentchanges ( 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_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_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, @@ -341,6 +355,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_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); @@ -354,7 +369,7 @@ CREATE TABLE watchlist ( 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, @@ -366,10 +381,10 @@ CREATE TABLE math ( 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 ); @@ -423,15 +438,16 @@ CREATE TABLE logging ( 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 ); 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 SEQUENCE trackbacks_tb_id_seq; CREATE TABLE trackbacks ( - tb_id SERIAL NOT NULL PRIMARY KEY, + tb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'), tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE, tb_title TEXT NOT NULL, tb_url TEXT NOT NULL, @@ -452,6 +468,8 @@ CREATE TABLE job ( 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 +-- 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 @@ -487,11 +505,12 @@ CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent 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; @@ -501,11 +520,66 @@ $mw$; 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, + 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 updatelog ( + ul_key TEXT NOT NULL PRIMARY KEY +); + + +CREATE SEQUENCE category_id_seq; +CREATE TABLE category ( + cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_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); + +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 mediawiki_version ( type TEXT NOT NULL, @@ -528,4 +602,3 @@ CREATE TABLE mediawiki_version ( INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) VALUES ('Creation','??','$LastChangedRevision$','$LastChangedDate$'); -