From 1dfb44b51d2ebfb6c46ad1d8a321bebf8a34d1fd Mon Sep 17 00:00:00 2001 From: Greg Sabino Mullane Date: Mon, 24 Sep 2007 20:10:00 +0000 Subject: [PATCH] Major cleanup of schema, based on new compare_schemas.pl functionality. --- maintenance/postgres/tables.sql | 30 +++++++++++++++--------------- maintenance/updaters.inc | 21 ++++++++++++++++++++- 2 files changed, 35 insertions(+), 16 deletions(-) diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index fa55d85517..5f925b6656 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -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, @@ -127,7 +127,7 @@ ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (p 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, @@ -137,7 +137,7 @@ CREATE TABLE archive ( ar_flags TEXT, ar_rev_id INTEGER, ar_text_id INTEGER, - ar_deleted INTEGER NOT NULL DEFAULT 0, + 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); @@ -161,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); @@ -202,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, @@ -229,7 +229,7 @@ CREATE TABLE ipblocks ( ipb_expiry TIMESTAMPTZ NOT NULL, ipb_range_start TEXT, ipb_range_end TEXT, - ipb_deleted INTEGER NOT NULL DEFAULT 0, + ipb_deleted CHAR NOT NULL DEFAULT '0', ipb_block_email CHAR NOT NULL DEFAULT '0' ); @@ -273,7 +273,7 @@ CREATE TABLE oldimage ( oi_media_type TEXT NULL, oi_major_mime TEXT NOT NULL DEFAULT 'unknown', oi_minor_mime TEXT NOT NULL DEFAULT 'unknown', - oi_deleted CHAR NOT NULL DEFAULT '0', + 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; @@ -286,12 +286,12 @@ 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_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_size INTEGER NOT NULL, fa_width SMALLINT NOT NULL, fa_height SMALLINT NOT NULL, fa_metadata BYTEA NOT NULL DEFAULT '', @@ -303,7 +303,7 @@ CREATE TABLE filearchive ( 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_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); @@ -334,7 +334,7 @@ CREATE TABLE recentchanges ( rc_ip CIDR, rc_old_len INTEGER, rc_new_len INTEGER, - rc_deleted INTEGER NOT NULL DEFAULT 0, + rc_deleted SMALLINT NOT NULL DEFAULT 0, rc_logid INTEGER NOT NULL DEFAULT 0, rc_log_type TEXT, rc_log_action TEXT, @@ -375,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 ); @@ -388,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, @@ -399,7 +399,7 @@ CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,q 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 ); diff --git a/maintenance/updaters.inc b/maintenance/updaters.inc index f72e21120d..6bdaaed4ef 100644 --- a/maintenance/updaters.inc +++ b/maintenance/updaters.inc @@ -1351,18 +1351,30 @@ function do_postgres_updates() { # table, column, desired type, USING clause if needed $typechanges = array( + array("archive", "ar_deleted", "smallint", ""), + array("filearchive", "fa_deleted", "smallint", ""), array("filearchive", "fa_metadata", "bytea", "decode(fa_metadata,'escape')"), + array("filearchive", "fa_size", "int4", ""), + array("filearchive", "fa_storage_group","text", ""), array("filearchive", "fa_storage_key", "text", ""), array("image", "img_metadata", "bytea", "decode(img_metadata,'escape')"), array("image", "img_size", "int4", ""), array("image", "img_width", "int4", ""), array("image", "img_height", "int4", ""), array("ipblocks", "ipb_address", "text", "ipb_address::text"), + array("ipblocks", "ipb_deleted", "char", ""), array("math", "math_inputhash", "bytea", "decode(math_inputhash,'escape')"), array("math", "math_outputhash", "bytea", "decode(math_outputhash,'escape')"), + array("mwuser", "user_token", "text", ""), + array("mwuser", "user_email_token","text", ""), + array("objectcache", "keyname", "text", ""), + array("oldimage", "oi_height", "int4", ""), array("oldimage", "oi_size", "int4", ""), array("oldimage", "oi_width", "int4", ""), - array("oldimage", "oi_height", "int4", ""), + array("querycache", "qc_value", "int4", ""), + array("querycachetwo","qcc_value", "int4", ""), + array("recentchanges","rc_deleted", "smallint", ""), + array("templatelinks","tl_namespace", "smallint", "tl_namespace::smallint"), array("user_newtalk", "user_ip", "text", "host(user_ip)"), ); @@ -1443,6 +1455,13 @@ function do_postgres_updates() { } } + if ($wgDatabase->fieldInfo('oldimage','oi_deleted') !== 'smallint') { + echo "... change oldimage.oi_deleted to smallint"; + $wgDatabase->query("ALTER TABLE oldimage ALTER oi_deleted DROP DEFAULT"); + $wgDatabase->query("ALTER TABLE oldimage ALTER oi_deleted TYPE SMALLINT USING (oi_deleted::smallint)"); + $wgDatabase->query("ALTER TABLE oldimage ALTER oi_deleted SET DEFAULT 0"); + } + foreach ($newindexes as $ni) { if (pg_index_exists($ni[0], $ni[1])) { echo "... index $ni[1] on $ni[0] already exists\n"; -- 2.20.1