X-Git-Url: https://git.cyclocoop.org/%27.WWW_URL.%27admin/?a=blobdiff_plain;f=maintenance%2Fpostgres%2Ftables.sql;h=2dd02da902c1b3f91172906ac7028847d291301c;hb=2a6c9a1dfc96d67549d18c6ca1db7155c778b08e;hp=e74d0ffdbefb6d01481fd21eb4dfe17204773943;hpb=276cd02d716e5e5c1d585f5262d22ffaa3aa670d;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index e74d0ffdbe..2dd02da902 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -4,43 +4,46 @@ -- 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 +-- TODO: Change CHAR/SMALLINT to BOOL (still used in a non-bool fashion in PHP code) BEGIN; SET client_min_messages = 'ERROR'; CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0; -CREATE TABLE "user" ( +CREATE TABLE mwuser ( -- replace reserved word 'user' user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'), user_name TEXT NOT NULL UNIQUE, user_real_name TEXT, user_password TEXT, user_newpassword TEXT, - user_token CHAR(32), + user_newpass_time TIMESTAMPTZ, + 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, user_touched TIMESTAMPTZ, - user_registration TIMESTAMPTZ + user_registration TIMESTAMPTZ, + user_editcount INTEGER, + user_hidden SMALLINT NOT NULL DEFAULT 0 ); -CREATE INDEX user_email_token_idx ON "user" (user_email_token); +CREATE INDEX user_email_token_idx ON mwuser (user_email_token); -- Create a dummy user to satisfy fk contraints especially with revisions -INSERT INTO "user" +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 "user"(user_id) ON DELETE CASCADE, + ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, 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 "user"(user_id) ON DELETE CASCADE, - user_ip CIDR 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); @@ -53,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? @@ -86,55 +89,76 @@ CREATE TABLE revision ( rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, rev_text_id INTEGER NULL, -- FK rev_comment TEXT, - rev_user INTEGER NOT NULL REFERENCES "user"(user_id), + 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 ); 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_timestamp_idx ON revision (rev_timestamp); 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 TABLE "text" ( +CREATE TABLE pagecontent ( -- replaces reserved word 'text' old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_val'), old_text TEXT, old_flags TEXT ); -CREATE TABLE archive2 ( +CREATE SEQUENCE pr_id_val; +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_type TEXT NOT NULL, + pr_level TEXT NOT NULL, + pr_cascade SMALLINT NOT NULL, + pr_user INTEGER NULL, + pr_expiry TIMESTAMPTZ 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_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 "user"(user_id) ON DELETE SET NULL, + 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 + ar_text_id INTEGER, + ar_deleted SMALLINT NOT NULL DEFAULT 0, + ar_len INTEGER NULL ); -CREATE INDEX archive_name_title_timestamp ON archive2 (ar_namespace,ar_title,ar_timestamp); +CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp); +CREATE INDEX archive_user_text ON archive (ar_user_text); --- This is the easiest way to work around the char(15) timestamp hack without modifying PHP code -CREATE VIEW archive AS -SELECT - ar_namespace, ar_title, ar_text, ar_comment, ar_user, ar_user_text, - ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, - TO_CHAR(ar_timestamp, 'YYYYMMDDHH24MISS') AS ar_timestamp -FROM archive2; -CREATE RULE archive_insert AS ON INSERT TO archive -DO INSTEAD INSERT INTO archive2 VALUES ( - NEW.ar_namespace, NEW.ar_title, NEW.ar_text, NEW.ar_comment, NEW.ar_user, NEW.ar_user_text, - TO_DATE(NEW.ar_timestamp, 'YYYYMMDDHH24MISS'), - NEW.ar_minor_edit, NEW.ar_flags, NEW.ar_rev_id, NEW.ar_text_id +CREATE TABLE redirect ( + rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + rd_namespace SMALLINT NOT NULL, + rd_title TEXT NOT NULL ); +CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from); CREATE TABLE pagelinks ( @@ -142,14 +166,15 @@ CREATE TABLE pagelinks ( pl_namespace SMALLINT NOT NULL, pl_title TEXT NOT NULL ); -CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_namespace,pl_title,pl_from); +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_namespace TEXT NOT NULL, + 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, @@ -164,7 +189,7 @@ CREATE TABLE categorylinks ( cl_timestamp TIMESTAMPTZ NOT NULL ); CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to); -CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey); +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, @@ -190,6 +215,7 @@ CREATE TABLE site_stats ( 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 ); @@ -201,20 +227,26 @@ CREATE TABLE hitcounter ( CREATE SEQUENCE ipblocks_ipb_id_val; CREATE TABLE ipblocks ( - ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'), - ipb_address CIDR NULL, - ipb_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, - ipb_by INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE, - 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_expiry TIMESTAMPTZ NOT NULL, - ipb_range_start TEXT, - ipb_range_end TEXT -); -CREATE INDEX ipb_address ON ipblocks (ipb_address); + ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'), + 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 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 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); CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end); @@ -224,55 +256,68 @@ CREATE TABLE image ( 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 "user"(user_id) ON DELETE SET NULL, + img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, img_user_text TEXT NOT NULL, - img_timestamp TIMESTAMPTZ + img_timestamp TIMESTAMPTZ, + img_sha1 TEXT NOT NULL DEFAULT '' ); CREATE INDEX img_size_idx ON image (img_size); CREATE INDEX img_timestamp_idx ON image (img_timestamp); +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 SMALLINT NOT NULL, - oi_width SMALLINT NOT NULL, - oi_height SMALLINT NOT NULL, + oi_size INTEGER NOT NULL, + oi_width INTEGER NOT NULL, + oi_height INTEGER NOT NULL, oi_bits SMALLINT NOT NULL, oi_description TEXT, - oi_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, + oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, oi_user_text TEXT NOT NULL, - oi_timestamp TIMESTAMPTZ NOT NULL + oi_timestamp TIMESTAMPTZ NOT 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 SMALLINT NOT NULL DEFAULT 0, + oi_sha1 TEXT NOT NULL DEFAULT '' ); -CREATE INDEX oi_name ON oldimage (oi_name); +ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE; +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 "user"(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, 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 "user"(user_id) ON DELETE SET NULL, + fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, fa_user_text TEXT NOT NULL, - fa_timestamp TIMESTAMPTZ + fa_timestamp TIMESTAMPTZ, + 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); @@ -285,24 +330,32 @@ CREATE TABLE recentchanges ( rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'), rc_timestamp TIMESTAMPTZ NOT NULL, rc_cur_time TIMESTAMPTZ NOT NULL, - rc_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, + rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, 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_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_ip CIDR + rc_patrolled SMALLINT NOT NULL DEFAULT 0, + rc_ip CIDR, + rc_old_len INTEGER, + rc_new_len INTEGER, + 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); @@ -310,17 +363,17 @@ CREATE INDEX rc_ip ON recentchanges (rc_ip); CREATE TABLE watchlist ( - wl_user INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE, + wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, 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 TEXT NOT NULL UNIQUE, - math_outputhash TEXT NOT NULL, + math_inputhash BYTEA NOT NULL UNIQUE, + math_outputhash BYTEA NOT NULL, math_html_conservativeness SMALLINT NOT NULL, math_html TEXT, math_mathml TEXT @@ -328,16 +381,16 @@ 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 ); 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 ); @@ -348,8 +401,20 @@ CREATE TABLE querycache_info ( qci_timestamp TIMESTAMPTZ NULL ); +CREATE TABLE querycachetwo ( + qcc_type TEXT NOT NULL, + 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, + qcc_titletwo TEXT NOT NULL DEFAULT '' +); +CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value); +CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title); +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 ); @@ -362,23 +427,27 @@ CREATE TABLE transcache ( ); +CREATE SEQUENCE log_log_id_seq; CREATE TABLE logging ( + log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('log_log_id_seq'), log_type TEXT NOT NULL, log_action TEXT NOT NULL, log_timestamp TIMESTAMPTZ NOT NULL, - log_user INTEGER REFERENCES "user"(user_id) ON DELETE SET NULL, + log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL, log_namespace SMALLINT NOT NULL, log_title TEXT NOT NULL, log_comment TEXT, - log_params TEXT + log_params TEXT, + 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, @@ -399,16 +468,17 @@ 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 INDEX ts2_page_title ON page USING gist(titlevector); CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN IF TG_OP = 'INSERT' THEN - NEW.titlevector = to_tsvector(NEW.page_title); + NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); ELSIF NEW.page_title != OLD.page_title THEN - NEW.titlevector := to_tsvector(NEW.page_title); + NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); END IF; RETURN NEW; END; @@ -418,32 +488,103 @@ CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page FOR EACH ROW EXECUTE PROCEDURE ts2_page_title(); -ALTER TABLE text ADD textvector tsvector; -CREATE INDEX ts2_page_text ON text USING gist(textvector); +ALTER TABLE pagecontent ADD textvector tsvector; CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN IF TG_OP = 'INSERT' THEN - NEW.textvector = to_tsvector(NEW.old_text); + NEW.textvector = to_tsvector('default',NEW.old_text); ELSIF NEW.old_text != OLD.old_text THEN - NEW.textvector := to_tsvector(NEW.old_text); + NEW.textvector := to_tsvector('default',NEW.old_text); END IF; RETURN NEW; END; $mw$; -CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON text +CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent FOR EACH ROW EXECUTE PROCEDURE ts2_page_text(); -CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS +-- These are added by the setup script due to version compatibility issues +-- 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,SMALLINT) RETURNS INT LANGUAGE SQL AS $mw$ INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3); SELECT 1; $mw$; +-- 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_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, mw_version TEXT NOT NULL, + notes TEXT NULL, pg_version TEXT NULL, pg_dbname TEXT NULL, @@ -451,6 +592,7 @@ CREATE TABLE mediawiki_version ( pg_port TEXT NULL, mw_schema TEXT NULL, ts2_schema TEXT NULL, + ctype TEXT NULL, sql_version TEXT NULL, sql_date TEXT NULL, @@ -460,5 +602,3 @@ CREATE TABLE mediawiki_version ( INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) VALUES ('Creation','??','$LastChangedRevision$','$LastChangedDate$'); - -COMMIT;