X-Git-Url: https://git.heureux-cyclage.org/?a=blobdiff_plain;f=maintenance%2Fpostgres%2Ftables.sql;h=a0d19e13a6ea41addaed7f92d26a06e8a5befb2d;hb=6293e4a1ddb10d818e238187c04034162c717205;hp=e06969c8949ce22a233b4a6c33cdca15db554c06;hpb=e1174cfafe4a2e6d1f09dfcecac220fb9908368f;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index e06969c894..a0d19e13a6 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -5,7 +5,7 @@ -- 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 to BOOL (still needed as CHAR due to some PHP code) BEGIN; SET client_min_messages = 'ERROR'; @@ -18,9 +18,9 @@ CREATE TABLE mwuser ( -- replace reserved word 'user' user_password TEXT, user_newpassword TEXT, user_newpass_time TIMESTAMPTZ, - user_token CHAR(32), + 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, @@ -89,12 +89,12 @@ CREATE TABLE revision ( rev_text_id INTEGER NULL, -- FK rev_comment TEXT, rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT, - rev_user_text TEXT NOT NULL, - rev_timestamp TIMESTAMPTZ NOT NULL, + 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_deleted INTEGER NOT NULL DEFAULT 0, rev_len INTEGER NULL, - rev_parent_id INTEGER DEFAULT 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); @@ -123,10 +123,12 @@ CREATE TABLE page_restrictions ( ); ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type); + 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_comment TEXT, ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, ar_user_text TEXT NOT NULL, @@ -135,10 +137,12 @@ CREATE TABLE archive ( ar_flags TEXT, ar_rev_id INTEGER, ar_text_id INTEGER, - ar_deleted INTEGER NOT NULL DEFAULT '0', - ar_len INTEGER NULL, + ar_deleted SMALLINT NOT NULL DEFAULT 0, + ar_len INTEGER NULL ); 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 redirect ( rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, @@ -157,7 +161,7 @@ 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); @@ -175,7 +179,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, @@ -198,7 +202,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_admins INTEGER DEFAULT -1, @@ -224,8 +228,10 @@ CREATE TABLE ipblocks ( ipb_enable_autoblock CHAR NOT NULL DEFAULT '1', ipb_expiry TIMESTAMPTZ NOT NULL, ipb_range_start TEXT, - ipb_range_end TEXT - ipb_deleted INTEGER NOT NULL, DEFAULT '0', + ipb_range_end TEXT, + ipb_deleted CHAR NOT NULL DEFAULT '0', + ipb_block_email CHAR NOT NULL DEFAULT '0' + ); CREATE INDEX ipb_address ON ipblocks (ipb_address); CREATE INDEX ipb_user ON ipblocks (ipb_user); @@ -237,7 +243,7 @@ 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', @@ -245,13 +251,15 @@ CREATE TABLE image ( img_description TEXT NOT 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 INTEGER NOT NULL, oi_width INTEGER NOT NULL, @@ -260,24 +268,33 @@ CREATE TABLE oldimage ( oi_description TEXT, 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 TABLE filearchive ( fa_id SERIAL NOT NULL PRIMARY KEY, fa_name TEXT NOT NULL, fa_archive_name TEXT, - fa_storage_group VARCHAR(16), - fa_storage_key CHAR(64), + 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', @@ -285,8 +302,8 @@ CREATE TABLE filearchive ( fa_description TEXT NOT NULL, fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, fa_user_text TEXT NOT NULL, - fa_timestamp TIMESTAMPTZ - fa_deleted INTEGER NOT NULL DEFAULT '0', + 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); @@ -317,11 +334,11 @@ CREATE TABLE recentchanges ( rc_ip CIDR, rc_old_len INTEGER, rc_new_len INTEGER, - rc_deleted INTEGER NOT NULL DEFAULT '0', - rc_logid INTEGER NOT NULL DEFAULT '0', - rc_log_type TEXT, + 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, + rc_params TEXT ); CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp); CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title); @@ -340,8 +357,8 @@ CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title 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 @@ -358,7 +375,7 @@ CREATE TABLE interwiki ( 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 ); @@ -371,7 +388,7 @@ CREATE TABLE querycache_info ( CREATE TABLE querycachetwo ( qcc_type TEXT NOT NULL, - qcc_value SMALLINT NOT NULL DEFAULT 0, + 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, @@ -381,9 +398,8 @@ 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 ); @@ -398,6 +414,7 @@ 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, @@ -406,8 +423,7 @@ CREATE TABLE logging ( log_title TEXT NOT NULL, log_comment TEXT, log_params TEXT, - log_deleted INTEGER NOT NULL DEFAULT '0', - log_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('log_log_id_seq'), + log_deleted INTEGER 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); @@ -442,9 +458,9 @@ CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN IF TG_OP = 'INSERT' THEN - NEW.titlevector = to_tsvector('default',NEW.page_title); + NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); ELSIF NEW.page_title != OLD.page_title THEN - NEW.titlevector := to_tsvector('default',NEW.page_title); + NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); END IF; RETURN NEW; END; @@ -490,6 +506,16 @@ CREATE TABLE profiling ( ); 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 mediawiki_version ( type TEXT NOT NULL, @@ -512,5 +538,3 @@ CREATE TABLE mediawiki_version ( INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) VALUES ('Creation','??','$LastChangedRevision$','$LastChangedDate$'); - -COMMIT;