diff options
| -rw-r--r-- | includes/db/MWLBFactory.php | 2 | ||||
| -rw-r--r-- | includes/installer/PostgresInstaller.php | 11 | ||||
| -rw-r--r-- | includes/installer/PostgresUpdater.php | 10 | ||||
| -rw-r--r-- | includes/search/SearchPostgres.php | 8 | ||||
| -rw-r--r-- | maintenance/postgres/archives/patch-textsearch_bug66650.sql | 4 | ||||
| -rw-r--r-- | maintenance/postgres/archives/patch-update_sequences.sql | 4 | ||||
| -rwxr-xr-x | maintenance/postgres/compare_schemas.pl | 1 | ||||
| -rwxr-xr-x | maintenance/postgres/mediawiki_mysql2postgres.pl | 7 | ||||
| -rw-r--r-- | maintenance/postgres/tables-generated.sql | 8 | ||||
| -rw-r--r-- | maintenance/postgres/tables.sql | 14 | ||||
| -rw-r--r-- | maintenance/sqlite/tables-generated.sql | 6 | ||||
| -rw-r--r-- | maintenance/tables-generated.sql | 8 | ||||
| -rw-r--r-- | maintenance/tables.json | 38 | ||||
| -rw-r--r-- | maintenance/tables.sql | 39 |
14 files changed, 85 insertions, 75 deletions
diff --git a/includes/db/MWLBFactory.php b/includes/db/MWLBFactory.php index 0dd361eae268..f71124c970c8 100644 --- a/includes/db/MWLBFactory.php +++ b/includes/db/MWLBFactory.php @@ -190,7 +190,7 @@ abstract class MWLBFactory { $server += [ 'port' => $options->get( 'DBport' ), // Work around the reserved word usage in MediaWiki schema - 'keywordTableMap' => [ 'user' => 'mwuser', 'text' => 'pagecontent' ] + 'keywordTableMap' => [ 'user' => 'mwuser' ] ]; } diff --git a/includes/installer/PostgresInstaller.php b/includes/installer/PostgresInstaller.php index 9212d2d116e0..9c9d8ec922bb 100644 --- a/includes/installer/PostgresInstaller.php +++ b/includes/installer/PostgresInstaller.php @@ -168,7 +168,7 @@ class PostgresInstaller extends DatabaseInstaller { 'password' => $password, 'dbname' => $dbName, 'schema' => $schema, - 'keywordTableMap' => [ 'user' => 'mwuser', 'text' => 'pagecontent' ], + 'keywordTableMap' => [ 'user' => 'mwuser' ], ] ); $status->value = $db; } catch ( DBConnectionError $e ) { @@ -621,17 +621,18 @@ class PostgresInstaller extends DatabaseInstaller { return $status; } - $error = $conn->sourceFile( $this->getSchemaPath( $conn ) ); + + $error = $conn->sourceFile( $this->getGeneratedSchemaPath( $conn ) ); if ( $error !== true ) { $conn->reportQueryError( $error, 0, '', __METHOD__ ); $conn->rollback( __METHOD__ ); - $status->fatal( 'config-install-tables-manual-failed', $error ); + $status->fatal( 'config-install-tables-failed', $error ); } else { - $error = $conn->sourceFile( $this->getGeneratedSchemaPath( $conn ) ); + $error = $conn->sourceFile( $this->getSchemaPath( $conn ) ); if ( $error !== true ) { $conn->reportQueryError( $error, 0, '', __METHOD__ ); $conn->rollback( __METHOD__ ); - $status->fatal( 'config-install-tables-failed', $error ); + $status->fatal( 'config-install-tables-manual-failed', $error ); } else { $conn->commit( __METHOD__ ); } diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index d09846cd8c8c..69c1bbc6d85f 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -46,10 +46,8 @@ class PostgresUpdater extends DatabaseUpdater { # rename tables 1.7.3 # r15791 Change reserved word table names "user" and "text" [ 'renameTable', 'user', 'mwuser' ], - [ 'renameTable', 'text', 'pagecontent' ], [ 'renameIndex', 'mwuser', 'user_pkey', 'mwuser_pkey' ], [ 'renameIndex', 'mwuser', 'user_user_name_key', 'mwuser_user_name_key' ], - [ 'renameIndex', 'pagecontent', 'text_pkey', 'pagecontent_pkey' ], # renamed sequences [ 'renameSequence', 'ipblocks_ipb_id_val', 'ipblocks_ipb_id_seq' ], @@ -336,7 +334,7 @@ class PostgresUpdater extends DatabaseUpdater { [ 'checkIndex', 'ts2_page_text', [ [ 'textvector', 'tsvector_ops', 'gist', 0 ], ], - 'CREATE INDEX "ts2_page_text" ON "pagecontent" USING "gist" ("textvector")' ], + 'CREATE INDEX "ts2_page_text" ON "text" USING "gist" ("textvector")' ], [ 'checkIndex', 'ts2_page_title', [ [ 'titlevector', 'tsvector_ops', 'gist', 0 ], ], @@ -548,7 +546,7 @@ class PostgresUpdater extends DatabaseUpdater { [ 'setSequenceOwner', 'page', 'page_id', 'page_page_id_seq' ], [ 'setSequenceOwner', 'revision', 'rev_id', 'revision_rev_id_seq' ], [ 'setSequenceOwner', 'ip_changes', 'ipc_rev_id', 'ip_changes_ipc_rev_id_seq' ], - [ 'setSequenceOwner', 'pagecontent', 'old_id', 'text_old_id_seq' ], + [ 'setSequenceOwner', 'text', 'old_id', 'text_old_id_seq' ], [ 'setSequenceOwner', 'comment', 'comment_id', 'comment_comment_id_seq' ], [ 'setSequenceOwner', 'page_restrictions', 'pr_id', 'page_restrictions_pr_id_seq' ], [ 'setSequenceOwner', 'archive', 'ar_id', 'archive_ar_id_seq' ], @@ -910,6 +908,10 @@ class PostgresUpdater extends DatabaseUpdater { [ 'addPgIndex', 'filearchive', 'fa_actor_timestamp', '(fa_actor, fa_timestamp)' ], [ 'addPgIndex', 'ipblocks', 'ipb_expiry', '(ipb_expiry)' ], [ 'addPgIndex', 'ipblocks', 'ipb_timestamp', '(ipb_timestamp)' ], + [ 'renameTable', 'pagecontent', 'text' ], + [ 'renameIndex', 'text', 'pagecontent_pkey', 'text_pkey' ], + [ 'changeNullableField', 'text', 'old_text', 'NOT NULL', true ], + [ 'changeNullableField', 'text', 'old_flags', 'NOT NULL', true ], ]; } diff --git a/includes/search/SearchPostgres.php b/includes/search/SearchPostgres.php index 345273a8c9dd..08c6724c445d 100644 --- a/includes/search/SearchPostgres.php +++ b/includes/search/SearchPostgres.php @@ -35,7 +35,7 @@ class SearchPostgres extends SearchDatabase { /** * Perform a full text search query via tsearch2 and return a result set. * Currently searches a page's current title (page.page_title) and - * latest revision article text (pagecontent.old_text) + * latest revision article text (text.old_text) * * @param string $term Raw search term * @return SqlSearchResultSet @@ -147,7 +147,7 @@ class SearchPostgres extends SearchDatabase { $slotRoleStore = MediaWikiServices::getInstance()->getSlotRoleStore(); if ( $top === "" ) { # # e.g. if only stopwords are used XXX return something better $query = "SELECT page_id, page_namespace, page_title, 0 AS score " . - "FROM page p, revision r, slots s, content c, pagecontent pc " . + "FROM page p, revision r, slots s, content c, \"text\" pc " . "WHERE p.page_latest = r.rev_id " . "AND s.slot_revision_id = r.rev_id " . "AND s.slot_role_id = " . $slotRoleStore->getId( SlotRecord::MAIN ) . " " . @@ -164,7 +164,7 @@ class SearchPostgres extends SearchDatabase { $query = "SELECT page_id, page_namespace, page_title, " . "ts_rank($fulltext, to_tsquery($searchstring), 5) AS score " . - "FROM page p, revision r, slots s, content c, pagecontent pc " . + "FROM page p, revision r, slots s, content c, \"text\" pc " . "WHERE p.page_latest = r.rev_id " . "AND s.slot_revision_id = r.rev_id " . "AND s.slot_role_id = " . $slotRoleStore->getId( SlotRecord::MAIN ) . " " . @@ -196,7 +196,7 @@ class SearchPostgres extends SearchDatabase { public function update( $pageid, $title, $text ) { # # We don't want to index older revisions $slotRoleStore = MediaWikiServices::getInstance()->getSlotRoleStore(); - $sql = "UPDATE pagecontent SET textvector = NULL " . + $sql = "UPDATE \"text\" SET textvector = NULL " . "WHERE textvector IS NOT NULL " . "AND old_id IN " . "(SELECT DISTINCT substring( c.content_address from '^tt:([0-9]+)$' )::int AS old_rev_text_id " . diff --git a/maintenance/postgres/archives/patch-textsearch_bug66650.sql b/maintenance/postgres/archives/patch-textsearch_bug66650.sql index e4f5681c452f..c3704af0b3cb 100644 --- a/maintenance/postgres/archives/patch-textsearch_bug66650.sql +++ b/maintenance/postgres/archives/patch-textsearch_bug66650.sql @@ -1,5 +1,5 @@ -UPDATE /*_*/pagecontent SET textvector=to_tsvector(old_text) -WHERE textvector IS NULL AND old_id IN +UPDATE /*_*/text SET textvector=to_tsvector(old_text) +WHERE textvector IS NULL AND old_id IN (SELECT max(rev_text_id) FROM revision GROUP BY rev_page); INSERT INTO /*_*/updatelog(ul_key) VALUES ('patch-textsearch_bug66650.sql'); diff --git a/maintenance/postgres/archives/patch-update_sequences.sql b/maintenance/postgres/archives/patch-update_sequences.sql index 94f7be4fd5d7..ece03c422457 100644 --- a/maintenance/postgres/archives/patch-update_sequences.sql +++ b/maintenance/postgres/archives/patch-update_sequences.sql @@ -1,8 +1,8 @@ ALTER TABLE revision RENAME rev_rev_id_val TO revision_rev_id_seq; ALTER TABLE revision ALTER COLUMN rev_id SET DEFAULT NEXTVAL('revision_rev_id_seq'); -ALTER TABLE pagecontent RENAME text_old_id_val TO text_old_id_seq; -ALTER TABLE pagecontent ALTER COLUMN old_id SET DEFAULT nextval('text_old_id_seq'); +ALTER TABLE text RENAME text_old_id_val TO text_old_id_seq; +ALTER TABLE text ALTER COLUMN old_id SET DEFAULT nextval('text_old_id_seq'); ALTER TABLE category RENAME category_id_seq TO category_cat_id_seq; ALTER TABLE category ALTER COLUMN cat_id SET DEFAULT nextval('category_cat_id_seq'); diff --git a/maintenance/postgres/compare_schemas.pl b/maintenance/postgres/compare_schemas.pl index c33eeefa10cc..50b93486a669 100755 --- a/maintenance/postgres/compare_schemas.pl +++ b/maintenance/postgres/compare_schemas.pl @@ -563,4 +563,3 @@ __DATA__ ## Known exceptions OLD: searchindex ## We use tsearch2 directly on the page table instead RENAME: user mwuser ## Reserved word causing lots of problems -RENAME: text pagecontent ## Reserved word diff --git a/maintenance/postgres/mediawiki_mysql2postgres.pl b/maintenance/postgres/mediawiki_mysql2postgres.pl index 34837e1bdcd4..fa7a3191c905 100755 --- a/maintenance/postgres/mediawiki_mysql2postgres.pl +++ b/maintenance/postgres/mediawiki_mysql2postgres.pl @@ -278,9 +278,6 @@ for my $t (@torder, 'objectcache', 'querycache') { } print "\n\n"; -print qq{-- Temporarily rename pagecontent to "${table_prefix}text"\n}; -print qq{ALTER TABLE pagecontent RENAME TO "${table_prefix}text";\n\n}; - print qq{-- Allow rc_ip to contain empty string, will convert at end\n}; print qq{ALTER TABLE recentchanges ALTER rc_ip TYPE text USING host(rc_ip);\n\n}; @@ -378,7 +375,6 @@ my @alist; } warn qq{Writing information to return Postgres database to normal\n} if $verbose; -print qq{ALTER TABLE "${table_prefix}text" RENAME TO pagecontent;\n}; print qq{ALTER TABLE ${table_prefix}recentchanges ALTER rc_ip TYPE cidr USING\n}; print qq{ CASE WHEN rc_ip = '' THEN NULL ELSE rc_ip::cidr END;\n}; @@ -415,7 +411,7 @@ SELECT setval('page_page_id_seq', 1+coalesce(max(page_id),0),false) FROM pa SELECT setval('page_restrictions_pr_id_seq', 1+coalesce(max(pr_id) ,0),false) FROM page_restrictions; SELECT setval('recentchanges_rc_id_seq', 1+coalesce(max(rc_id) ,0),false) FROM recentchanges; SELECT setval('revision_rev_id_seq', 1+coalesce(max(rev_id) ,0),false) FROM revision; -SELECT setval('text_old_id_seq', 1+coalesce(max(old_id) ,0),false) FROM pagecontent; +SELECT setval('text_old_id_seq', 1+coalesce(max(old_id) ,0),false) FROM "text"; SELECT setval('user_user_id_seq', 1+coalesce(max(user_id),0),false) FROM mwuser; }; @@ -428,7 +424,6 @@ exit; __DATA__ ## Known remappings: either indicate the MySQL name, ## or leave blank if it should be skipped -pagecontent text mwuser user archive2 profiling diff --git a/maintenance/postgres/tables-generated.sql b/maintenance/postgres/tables-generated.sql index f74464a05dae..64ca9ac6af10 100644 --- a/maintenance/postgres/tables-generated.sql +++ b/maintenance/postgres/tables-generated.sql @@ -686,3 +686,11 @@ CREATE INDEX fa_deleted_timestamp ON filearchive (fa_deleted_timestamp); CREATE INDEX fa_actor_timestamp ON filearchive (fa_actor, fa_timestamp); CREATE INDEX fa_sha1 ON filearchive (fa_sha1); + + +CREATE TABLE text ( + old_id SERIAL NOT NULL, + old_text TEXT NOT NULL, + old_flags TEXT NOT NULL, + PRIMARY KEY(old_id) +); diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 4e7b2c7b64c3..e4c416e84c23 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -93,14 +93,6 @@ CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp); CREATE INDEX rev_actor_timestamp ON revision (rev_actor,rev_timestamp,rev_id); CREATE INDEX rev_page_actor_timestamp ON revision (rev_page,rev_actor,rev_timestamp); -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_seq'), - old_text TEXT, - old_flags TEXT -); -ALTER SEQUENCE text_old_id_seq OWNED BY pagecontent.old_id; - CREATE SEQUENCE archive_ar_id_seq; CREATE TABLE archive ( @@ -258,7 +250,7 @@ CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page FOR EACH ROW EXECUTE PROCEDURE ts2_page_title(); -ALTER TABLE pagecontent ADD textvector tsvector; +ALTER TABLE text ADD textvector tsvector; CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN @@ -271,8 +263,8 @@ RETURN NEW; END; $mw$; -CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent +CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON text FOR EACH ROW EXECUTE PROCEDURE ts2_page_text(); CREATE INDEX ts2_page_title ON page USING gin(titlevector); -CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector); +CREATE INDEX ts2_page_text ON text USING gin(textvector); diff --git a/maintenance/sqlite/tables-generated.sql b/maintenance/sqlite/tables-generated.sql index c1d5c3eeff73..85ce0c76269b 100644 --- a/maintenance/sqlite/tables-generated.sql +++ b/maintenance/sqlite/tables-generated.sql @@ -642,3 +642,9 @@ CREATE INDEX fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp); CREATE INDEX fa_actor_timestamp ON /*_*/filearchive (fa_actor, fa_timestamp); CREATE INDEX fa_sha1 ON /*_*/filearchive (fa_sha1); + + +CREATE TABLE /*_*/text ( + old_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + old_text BLOB NOT NULL, old_flags BLOB NOT NULL +); diff --git a/maintenance/tables-generated.sql b/maintenance/tables-generated.sql index 7d16054af48f..07a73c7d8b46 100644 --- a/maintenance/tables-generated.sql +++ b/maintenance/tables-generated.sql @@ -616,3 +616,11 @@ CREATE TABLE /*_*/filearchive ( ), PRIMARY KEY(fa_id) ) /*$wgDBTableOptions*/; + + +CREATE TABLE /*_*/text ( + old_id INT UNSIGNED AUTO_INCREMENT NOT NULL, + old_text MEDIUMBLOB NOT NULL, + old_flags TINYBLOB NOT NULL, + PRIMARY KEY(old_id) +) /*$wgDBTableOptions*/; diff --git a/maintenance/tables.json b/maintenance/tables.json index 7026a4f1f333..84d9f77d2d23 100644 --- a/maintenance/tables.json +++ b/maintenance/tables.json @@ -2186,5 +2186,43 @@ "pk": [ "fa_id" ] + }, + { + "name": "text", + "comment": "Holds text of individual page revisions. Field names are a holdover from the 'old' revisions table in MediaWiki 1.4 and earlier: an upgrade will transform that table into the 'text' table to minimize unnecessary churning and downtime. If upgrading, the other fields will be left unused.", + "columns": [ + { + "name": "old_id", + "comment": "Unique text storage key number. Note that the 'oldid' parameter used in URLs does *not* refer to this number anymore, but to rev_id. content.content_address refers to this column", + "type": "integer", + "options": { + "autoincrement": true, + "unsigned": true, + "notnull": true + } + }, + { + "name": "old_text", + "comment": "Depending on the contents of the old_flags field, the text may be convenient plain text, or it may be funkily encoded.", + "type": "blob", + "options": { + "notnull": true, + "length": 16777215 + } + }, + { + "name": "old_flags", + "comment": "Comma-separated list of flags:\n* gzip: text is compressed with PHP's gzdeflate() function.\n* utf-8: text was stored as UTF-8. If $wgLegacyEncoding option is on, rows *without* this flag will be converted to UTF-8 transparently at load time. Note that due to a bug in a maintenance script, this flag may have been stored as 'utf8' in some cases (T18841).\n* object: text field contained a serialized PHP object. The object either contains multiple versions compressed together to achieve a better compression ratio, or it refers to another row where the text can be found.\n* external: text was stored in an external location specified by old_text. Any additional flags apply to the data stored at that URL, not the URL itself. The 'object' flag is *not* set for URLs of the form 'DB://cluster/id/itemid', because the external storage system itself decompresses these.", + "type": "blob", + "options": { + "notnull": true, + "length": 255 + } + } + ], + "indexes": [], + "pk": [ + "old_id" + ] } ] diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 72ce59604171..1a7447ccc727 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -274,45 +274,6 @@ CREATE INDEX /*i*/rev_actor_timestamp ON /*_*/revision (rev_actor,rev_timestamp, -- in ApiQueryContributors. Also for ApiQueryRevisions if rvuser is specified. CREATE INDEX /*i*/rev_page_actor_timestamp ON /*_*/revision (rev_page,rev_actor,rev_timestamp); --- Holds text of individual page revisions. --- --- Field names are a holdover from the 'old' revisions table in --- MediaWiki 1.4 and earlier: an upgrade will transform that --- table into the 'text' table to minimize unnecessary churning --- and downtime. If upgrading, the other fields will be left unused. --- -CREATE TABLE /*_*/text ( - -- Unique text storage key number. - -- Note that the 'oldid' parameter used in URLs does *not* - -- refer to this number anymore, but to rev_id. - -- - -- content.content_address refers to this column - old_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, - - -- Depending on the contents of the old_flags field, the text - -- may be convenient plain text, or it may be funkily encoded. - old_text mediumblob NOT NULL, - - -- Comma-separated list of flags: - -- gzip: text is compressed with PHP's gzdeflate() function. - -- utf-8: text was stored as UTF-8. - -- If $wgLegacyEncoding option is on, rows *without* this flag - -- will be converted to UTF-8 transparently at load time. Note - -- that due to a bug in a maintenance script, this flag may - -- have been stored as 'utf8' in some cases (T18841). - -- object: text field contained a serialized PHP object. - -- The object either contains multiple versions compressed - -- together to achieve a better compression ratio, or it refers - -- to another row where the text can be found. - -- external: text was stored in an external location specified by old_text. - -- Any additional flags apply to the data stored at that URL, not - -- the URL itself. The 'object' flag is *not* set for URLs of the - -- form 'DB://cluster/id/itemid', because the external storage - -- system itself decompresses these. - old_flags tinyblob NOT NULL -) /*$wgDBTableOptions*/ MAX_ROWS=10000000 AVG_ROW_LENGTH=10240; --- In case tables are created as MyISAM, use row hints for MySQL <5.0 to avoid 4GB limit - -- -- Archive area for deleted pages and their revisions. -- These may be viewed (and restored) by admins through the Special:Undelete interface. |