Reedy has uploaded a new change for review. https://gerrit.wikimedia.org/r/51675
Change subject: Bug 15441, bug 39675. Add archive, externallinks PK ...................................................................... Bug 15441, bug 39675. Add archive, externallinks PK * New fields: ar_id, el_id. el_id is presently not used for anything, but will help with binasher's online schema migrations. Change-Id: Ib6b0fc3736d173fa4ba7b786ecfc710b2f4711bb --- M includes/WikiPage.php M includes/api/ApiQueryDeletedrevs.php M includes/installer/Ibm_db2Updater.php M includes/installer/MysqlUpdater.php M includes/installer/OracleUpdater.php M includes/installer/PostgresUpdater.php M includes/installer/SqliteUpdater.php M includes/revisiondelete/RevisionDelete.php A maintenance/archives/patch-archive-ar_id.sql A maintenance/archives/patch-externallinks-el_id.sql A maintenance/ibm_db2/patch-archive-ar_id.sql A maintenance/ibm_db2/patch-externallinks-el_id.sql M maintenance/ibm_db2/tables.sql M maintenance/mssql/tables.sql A maintenance/oracle/archives/patch-archive-ar_id.sql A maintenance/oracle/archives/patch-externallinks-el_id.sql M maintenance/oracle/tables.sql M maintenance/postgres/tables.sql M maintenance/sqlite/archives/initial-indexes.sql A maintenance/sqlite/archives/patch-archive-ar_id.sql A maintenance/sqlite/archives/patch-externallinks-el_id.sql M maintenance/tables.sql 22 files changed, 307 insertions(+), 31 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core refs/changes/75/51675/1 diff --git a/includes/WikiPage.php b/includes/WikiPage.php index 6048294..5c671c9 100644 --- a/includes/WikiPage.php +++ b/includes/WikiPage.php @@ -2497,7 +2497,7 @@ public function doDeleteArticleReal( $reason, $suppress = false, $id = 0, $commit = true, &$error = '', User $user = null ) { - global $wgUser, $wgContentHandlerUseDB; + global $wgUser, $wgContentHandlerUseDB, $wgUseArchiveIdLogFields, $wgContLang; wfDebug( __METHOD__ . "\n" ); @@ -2543,6 +2543,17 @@ $dbw = wfGetDB( DB_MASTER ); $dbw->begin( __METHOD__ ); + + # Log the deletion, if the page was suppressed, log it at Oversight instead + $logtype = $suppress ? 'suppress' : 'delete'; + + $logEntry = new ManualLogEntry( $logtype, 'delete' ); + $logEntry->setPerformer( $user ); + $logEntry->setTarget( $this->mTitle ); + $logEntry->setComment( $reason ); + $logid = $logEntry->insert(); + $logEntry->publish( $logid ); + // For now, shunt the revision data into the archive table. // Text is *not* removed from the text table; bulk storage // is left intact to avoid breaking block-compression or @@ -2578,11 +2589,20 @@ $row[ 'ar_content_format' ] = 'rev_content_format'; } - $dbw->insertSelect( 'archive', array( 'page', 'revision' ), + if ( $wgUseArchiveIdLogFields ) { + $row[ 'ar_log_id' ] = 'log_id'; + $row[ 'ar_log_timestamp' ] = 'log_timestamp'; + $row[ 'ar_log_user' ] = 'log_user'; + $row[ 'ar_log_user_text' ] = 'log_user_text'; + $row[ 'ar_log_comment' ] = 'log_comment'; + } + + $dbw->insertSelect( 'archive', array( 'page', 'revision', 'logging' ), $row, array( 'page_id' => $id, - 'page_id = rev_page' + 'page_id = rev_page', + 'log_id' => $logid, ), __METHOD__ ); @@ -3373,9 +3393,9 @@ } elseif ( $isCurrent ) { // XXX: why use RAW audience here, and PUBLIC (default) below? $content = $this->page->getContent( Revision::RAW ); + } else { $rev = Revision::newFromTitle( $this->page->getTitle(), $this->revid ); - if ( $rev === null ) { $content = null; } else { diff --git a/includes/api/ApiQueryDeletedrevs.php b/includes/api/ApiQueryDeletedrevs.php index 31812cf..a4dd8bf 100644 --- a/includes/api/ApiQueryDeletedrevs.php +++ b/includes/api/ApiQueryDeletedrevs.php @@ -36,6 +36,7 @@ } public function execute() { + global $wgUseArchiveIdLogFields; $user = $this->getUser(); // Before doing anything at all, let's check permissions if ( !$user->isAllowed( 'deletedhistory' ) ) { @@ -56,6 +57,15 @@ $fld_sha1 = isset( $prop['sha1'] ); $fld_content = isset( $prop['content'] ); $fld_token = isset( $prop['token'] ); + if ( $wgUseArchiveIdLogFields ) { + $fld_id = isset ( $prop['id'] ); + $fld_logid = isset ( $prop['logid'] ); + $fld_logtimestamp = isset ( $prop['logtimestamp'] ); + $fld_loguser = isset ( $prop['loguser'] ); + $fld_loguserid = isset ( $prop['loguserid'] ); + $fld_logcomment = isset ( $prop['logcomment'] ); + $fld_parsedlogcomment = isset ( $prop['parsedlogcomment'] ); + } $result = $this->getResult(); $pageSet = $this->getPageSet(); @@ -103,6 +113,14 @@ $this->addFieldsIf( 'ar_minor_edit', $fld_minor ); $this->addFieldsIf( 'ar_len', $fld_len ); $this->addFieldsIf( 'ar_sha1', $fld_sha1 ); + if ( $wgUseArchiveIdLogFields ) { + $this->addFieldsIf( 'ar_id', $fld_id ); + $this->addFieldsIf( 'ar_log_id', $fld_logid ); + $this->addFieldsIf( 'ar_log_timestamp', $fld_logtimestamp ); + $this->addFieldsIf( 'ar_log_user_text', $fld_loguser ); + $this->addFieldsIf( 'ar_log_user', $fld_loguserid ); + $this->addFieldsIf( 'ar_log_comment', $fld_logcomment || $fld_parsedlogcomment ); + } if ( $fld_content ) { $this->addTables( 'text' ); @@ -248,7 +266,31 @@ if ( $fld_content ) { ApiResult::setContent( $rev, Revision::getRevisionText( $row ) ); } - + if ( $wgUseArchiveIdLogFields ) { + if ( $fld_id ) { + $rev['id'] = intval ( $row->ar_id ); + } + if ( $fld_logid ) { + $rev['logid'] = intval ( $row->ar_log_id ); + } + if ( $fld_logtimestamp ) { + $rev['logtimestamp'] = wfTimestamp + ( TS_ISO_8601, $row->ar_log_timestamp ); + } + if ( $fld_loguser ) { + $rev['loguser'] = $row->ar_log_user_text; + } + if ( $fld_loguserid ) { + $rev['loguserid'] = $row->ar_log_user; + } + if ( $fld_logcomment ) { + $rev['logcomment'] = $row->ar_log_comment; + } + if ( $fld_parsedlogcomment ) { + $rev['parsedlogcomment'] = Linker::formatComment + ( $row->ar_log_comment, $title ); + } + } if ( !isset( $pageMap[$row->ar_namespace][$row->ar_title] ) ) { $pageID = $newPageID++; $pageMap[$row->ar_namespace][$row->ar_title] = $pageID; @@ -279,7 +321,8 @@ } public function getAllowedParams() { - return array( + global $wgUseArchiveIdLogFields; + $array = array ( 'start' => array( ApiBase::PARAM_TYPE => 'timestamp' ), @@ -328,15 +371,28 @@ 'len', 'sha1', 'content', - 'token' ), ApiBase::PARAM_ISMULTI => true ), ); + if ( $wgUseArchiveIdLogFields ) { + array_push ( $array[ 'prop' ][ApiBase::PARAM_TYPE], + 'id', + 'logid', + 'logtimestamp', + 'loguser', + 'loguserid', + 'logcomment', + 'parsedlogcomment' + ); + } + array_push ( $array[ 'prop' ][ApiBase::PARAM_TYPE], 'token' ); + return $array; } public function getParamDescription() { - return array( + global $wgUseArchiveIdLogFields; + $array = array ( 'start' => 'The timestamp to start enumerating from (1, 2)', 'end' => 'The timestamp to stop enumerating at (1, 2)', 'dir' => $this->getDirectionDescription( $this->getModulePrefix(), ' (1, 3)' ), @@ -346,17 +402,16 @@ 'limit' => 'The maximum amount of revisions to list', 'prop' => array( 'Which properties to get', - ' revid - Adds the revision ID of the deleted revision', - ' parentid - Adds the revision ID of the previous revision to the page', - ' user - Adds the user who made the revision', - ' userid - Adds the user ID whom made the revision', - ' comment - Adds the comment of the revision', - ' parsedcomment - Adds the parsed comment of the revision', - ' minor - Tags if the revision is minor', - ' len - Adds the length (bytes) of the revision', - ' sha1 - Adds the SHA-1 (base 16) of the revision', - ' content - Adds the content of the revision', - ' token - Gives the edit token', + ' revid - Adds the revision ID of the deleted revision', + ' parentid - Adds the revision ID of the previous revision to the page', + ' user - Adds the user who made the revision', + ' userid - Adds the user ID who made the revision', + ' comment - Adds the comment of the revision', + ' parsedcomment - Adds the parsed comment of the revision', + ' minor - Tags if the revision is minor', + ' len - Adds the length (bytes) of the revision', + ' sha1 - Adds the SHA-1 (base 16) of the revision', + ' content - Adds the content of the revision', ), 'namespace' => 'Only list pages in this namespace (3)', 'user' => 'Only list revisions by this user', @@ -364,6 +419,19 @@ 'continue' => 'When more results are available, use this to continue (3)', 'unique' => 'List only one revision for each page (3)', ); + if ( $wgUseArchiveIdLogFields ) { + array_push( $array[ 'prop'], + ' id - Adds the archive ID of the revision', + ' logid - Adds the log ID of the deletion', + ' logtimestamp - Adds the timestamp of the deletion', + ' loguser - Adds the user who deleted the article', + ' loguserid - Adds the user ID who deleted the article', + ' logcomment - Adds the log comment of the deletion', + ' parsedlogcomment - Adds the parsed log comment of the deletion' + ); + } + array_push( $array[ 'prop'], ' token - Gives the edit token' ); + return $array; } public function getResultProperties() { diff --git a/includes/installer/Ibm_db2Updater.php b/includes/installer/Ibm_db2Updater.php index 33bf69c..625743a 100644 --- a/includes/installer/Ibm_db2Updater.php +++ b/includes/installer/Ibm_db2Updater.php @@ -91,6 +91,8 @@ array( 'addField', 'archive', 'ar_content_format', 'patch-archive-ar_content_format.sql' ), array( 'addField', 'archive', 'ar_content_model', 'patch-archive-ar_content_model.sql' ), array( 'addField', 'page', 'page_content_model', 'patch-page-page_content_model.sql' ), + array( 'addField', 'archive', 'ar_id', 'patch-archive-ar_id.sql' ), + array( 'addField', 'externallinks', 'el_id', 'patch-externallinks-el_id.sql' ), ); } } diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 6a2d50f..a61186e 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -229,6 +229,8 @@ array( 'modifyField', 'user_groups', 'ug_group', 'patch-ug_group-length-increase-255.sql' ), array( 'modifyField', 'user_former_groups', 'ufg_group', 'patch-ufg_group-length-increase-255.sql' ), array( 'addIndex', 'page_props', 'pp_propname_page', 'patch-page_props-propname-page-index.sql' ), + array( 'addField', 'archive', 'ar_id', 'patch-archive-ar_id.sql' ), + array( 'addField', 'externallinks', 'el_id', 'patch-externallinks-el_id.sql' ), ); } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index cafe8cd..597799f 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -73,7 +73,9 @@ array( 'addField', 'revision', 'rev_content_format', 'patch-revision-rev_content_format.sql' ), array( 'addField', 'revision', 'rev_content_model', 'patch-revision-rev_content_model.sql' ), array( 'addField', 'archive', 'ar_content_format', 'patch-archive-ar_content_format.sql' ), - array( 'addField', 'archive', 'ar_content_model', 'patch-archive-ar_content_model.sql' ), + array( 'addField', 'archive', 'ar_content_model', 'patch-archive-ar_content_model.sql' ), + array( 'addField', 'archive', 'ar_id', 'patch-archive-ar_id.sql' ), + array( 'addField', 'externallinks', 'el_id', 'patch-externallinks-el_id.sql' ), array( 'addField', 'page', 'page_content_model', 'patch-page-page_content_model.sql' ), array( 'dropField', 'site_stats', 'ss_admins', 'patch-ss_admins.sql' ), array( 'dropField', 'recentchanges', 'rc_moved_to_title', 'patch-rc_moved.sql' ), diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 0a4b5e6..7cae22f 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -103,9 +103,11 @@ array( 'addPgField', 'archive', 'ar_parent_id', 'INTEGER' ), array( 'addPgField', 'archive', 'ar_content_model', 'TEXT' ), array( 'addPgField', 'archive', 'ar_content_format', 'TEXT' ), - array( 'addPgField', 'categorylinks', 'cl_sortkey_prefix', "TEXT NOT NULL DEFAULT ''"), - array( 'addPgField', 'categorylinks', 'cl_collation', "TEXT NOT NULL DEFAULT 0"), - array( 'addPgField', 'categorylinks', 'cl_type', "TEXT NOT NULL DEFAULT 'page'"), + array( 'addPgField', 'archive', 'ar_id', "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('archive_ar_id_seq')" ), + array( 'addPgField', 'categorylinks', 'cl_sortkey_prefix', "TEXT NOT NULL DEFAULT ''" ), + array( 'addPgField', 'categorylinks', 'cl_collation', "TEXT NOT NULL DEFAULT 0" ), + array( 'addPgField', 'categorylinks', 'cl_type', "TEXT NOT NULL DEFAULT 'page'" ), + array( 'addPgField', 'externallinks', 'el_id', "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('archive_el_id_seq')" ), array( 'addPgField', 'image', 'img_sha1', "TEXT NOT NULL DEFAULT ''" ), array( 'addPgField', 'ipblocks', 'ipb_allow_usertalk', 'SMALLINT NOT NULL DEFAULT 0' ), array( 'addPgField', 'ipblocks', 'ipb_anon_only', 'SMALLINT NOT NULL DEFAULT 0' ), @@ -161,6 +163,8 @@ array( 'addPgField', 'job', 'job_token_timestamp', "TIMESTAMPTZ" ), array( 'addPgField', 'job', 'job_sha1', "TEXT NOT NULL DEFAULT ''" ), + + # type changes array( 'changeField', 'archive', 'ar_deleted', 'smallint', '' ), array( 'changeField', 'archive', 'ar_minor_edit', 'smallint', 'ar_minor_edit::smallint DEFAULT 0' ), diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index cd7a2c9..3375f81 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -97,7 +97,6 @@ array( 'addField', 'archive', 'ar_content_format', 'patch-archive-ar_content_format.sql' ), array( 'addField', 'archive', 'ar_content_model', 'patch-archive-ar_content_model.sql' ), array( 'addField', 'page', 'page_content_model', 'patch-page-page_content_model.sql' ), - array( 'dropField', 'site_stats', 'ss_admins', 'patch-drop-ss_admins.sql' ), array( 'dropField', 'recentchanges', 'rc_moved_to_title', 'patch-rc_moved.sql' ), array( 'addTable', 'sites', 'patch-sites.sql' ), @@ -109,6 +108,8 @@ array( 'modifyField', 'user_groups', 'ug_group', 'patch-ug_group-length-increase-255.sql' ), array( 'modifyField', 'user_former_groups', 'ufg_group', 'patch-ufg_group-length-increase-255.sql' ), array( 'addIndex', 'page_props', 'pp_propname_page', 'patch-page_props-propname-page-index.sql' ), + array( 'addField', 'archive', 'ar_id', 'patch-archive-ar_id.sql' ), + array( 'addField', 'externallinks', 'el_id', 'patch-externallinks-el_id.sql' ), ); } diff --git a/includes/revisiondelete/RevisionDelete.php b/includes/revisiondelete/RevisionDelete.php index d64201c..6715734 100644 --- a/includes/revisiondelete/RevisionDelete.php +++ b/includes/revisiondelete/RevisionDelete.php @@ -340,6 +340,26 @@ return 'ar_user_text'; } + public function getLogIdField() { + return 'ar_log_id'; + } + + public function getLogTimestampField() { + return 'ar_log_timestamp'; + } + + public function getLogUserField() { + return 'ar_log_user'; + } + + public function getLogUserTextField() { + return 'ar_log_user_text'; + } + + public function getLogCommentField() { + return 'ar_log_comment'; + } + public function getId() { # Convert DB timestamp to MW timestamp return $this->revision->getTimestamp(); @@ -355,7 +375,7 @@ // use timestamp for index 'ar_timestamp' => $this->row->ar_timestamp, 'ar_rev_id' => $this->row->ar_rev_id, - 'ar_deleted' => $this->getBits() + 'ar_deleted' => $this->getBits(), ), __METHOD__ ); return (bool)$dbw->affectedRows(); diff --git a/maintenance/archives/patch-archive-ar_id.sql b/maintenance/archives/patch-archive-ar_id.sql new file mode 100644 index 0000000..16cfb6e --- /dev/null +++ b/maintenance/archives/patch-archive-ar_id.sql @@ -0,0 +1,8 @@ +-- +-- patch-archive-ar_id.sql +-- +-- Bug 39675. Add archive.ar_id. + +ALTER TABLE /*$wgDBprefix*/archive + ADD COLUMN FIRST ar_id int unsigned NOT NULL AUTO_INCREMENT, + ADD PRIMARY KEY ar_id (ar_id); diff --git a/maintenance/archives/patch-externallinks-el_id.sql b/maintenance/archives/patch-externallinks-el_id.sql new file mode 100644 index 0000000..1e76a77 --- /dev/null +++ b/maintenance/archives/patch-externallinks-el_id.sql @@ -0,0 +1,8 @@ +-- +-- patch-archive-el_id.sql +-- +-- Bug 15441. Add externallinks.el_id. + +ALTER TABLE /*$wgDBprefix*/externallinks + ADD COLUMN FIRST el_id int unsigned NOT NULL AUTO_INCREMENT, + ADD PRIMARY KEY el_id (el_id); diff --git a/maintenance/ibm_db2/patch-archive-ar_id.sql b/maintenance/ibm_db2/patch-archive-ar_id.sql new file mode 100644 index 0000000..9479582 --- /dev/null +++ b/maintenance/ibm_db2/patch-archive-ar_id.sql @@ -0,0 +1 @@ +ALTER TABLE archive ADD ar_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1); diff --git a/maintenance/ibm_db2/patch-externallinks-el_id.sql b/maintenance/ibm_db2/patch-externallinks-el_id.sql new file mode 100644 index 0000000..f6c631e --- /dev/null +++ b/maintenance/ibm_db2/patch-externallinks-el_id.sql @@ -0,0 +1 @@ +ALTER TABLE externallinks ADD el_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1); \ No newline at end of file diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql index 2edb7f0..28aa67fc 100644 --- a/maintenance/ibm_db2/tables.sql +++ b/maintenance/ibm_db2/tables.sql @@ -190,6 +190,8 @@ CREATE TABLE archive ( + ar_id BIGINT + PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), ar_namespace SMALLINT NOT NULL, ar_title VARCHAR(255) NOT NULL, ar_text CLOB(16M) INLINE LENGTH 4096, @@ -286,6 +288,8 @@ CREATE TABLE externallinks ( + el_id BIGINT + PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), el_from BIGINT NOT NULL DEFAULT 0, -- REFERENCES page(page_id) ON DELETE CASCADE, el_to VARCHAR(1024) NOT NULL, @@ -507,6 +511,7 @@ rc_log_type VARCHAR(255), rc_log_action VARCHAR(255), rc_params CLOB(64K) INLINE LENGTH 4096 + ); CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp); diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index ad99617..fc635a5 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -159,6 +159,7 @@ -- Cannot reasonably create views on this table, due to the presence of TEXT -- columns. CREATE TABLE /*$wgDBprefix*/archive ( + ar_id NOT NULL PRIMARY KEY clustered IDENTITY, ar_namespace SMALLINT NOT NULL DEFAULT 0, ar_title NVARCHAR(255) NOT NULL DEFAULT '', ar_text NVARCHAR(MAX) NOT NULL, @@ -298,6 +299,7 @@ -- Track links to external URLs -- IE >= 4 supports no more than 2083 characters in a URL CREATE TABLE /*$wgDBprefix*/externallinks ( + el_id INT NOT NULL PRIMARY KEY clustered IDENTITY, el_from INT NOT NULL DEFAULT '0', el_to VARCHAR(2083) NOT NULL, el_index VARCHAR(896) NOT NULL, diff --git a/maintenance/oracle/archives/patch-archive-ar_id.sql b/maintenance/oracle/archives/patch-archive-ar_id.sql new file mode 100644 index 0000000..a43f760 --- /dev/null +++ b/maintenance/oracle/archives/patch-archive-ar_id.sql @@ -0,0 +1,6 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.archive ADD ( +ar_id NUMBER NOT NULL, +); +ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_pk PRIMARY KEY (ar_id); diff --git a/maintenance/oracle/archives/patch-externallinks-el_id.sql b/maintenance/oracle/archives/patch-externallinks-el_id.sql new file mode 100644 index 0000000..a8c443f --- /dev/null +++ b/maintenance/oracle/archives/patch-externallinks-el_id.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.externallinks ADD el_id NUMBER NOT NULL; +ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_pk PRIMARY KEY (el_id); \ No newline at end of file diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index b5b3b07..cfb86ce 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -129,7 +129,9 @@ ); ALTER TABLE &mw_prefix.pagecontent ADD CONSTRAINT &mw_prefix.pagecontent_pk PRIMARY KEY (old_id); +CREATE SEQUENCE archive_ar_id_seq; CREATE TABLE &mw_prefix.archive ( + ar_id NUMBER NOT NULL, ar_namespace NUMBER DEFAULT 0 NOT NULL, ar_title VARCHAR2(255) NOT NULL, ar_text CLOB, @@ -147,8 +149,9 @@ ar_parent_id NUMBER, ar_sha1 VARCHAR2(32), ar_content_model VARCHAR2(32), - ar_content_format VARCHAR2(64) + ar_content_format VARCHAR2(64), ); +ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_pk PRIMARY KEY (ar_id); ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk1 FOREIGN KEY (ar_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.archive_i01 ON &mw_prefix.archive (ar_namespace,ar_title,ar_timestamp); CREATE INDEX &mw_prefix.archive_i02 ON &mw_prefix.archive (ar_user_text,ar_timestamp); @@ -208,11 +211,14 @@ CREATE UNIQUE INDEX &mw_prefix.category_u01 ON &mw_prefix.category (cat_title); CREATE INDEX &mw_prefix.category_i01 ON &mw_prefix.category (cat_pages); +CREATE SEQUENCE externallinks_el_id_seq; CREATE TABLE &mw_prefix.externallinks ( + el_id NUMBER NOT NULL, el_from NUMBER NOT NULL, el_to VARCHAR2(2048) NOT NULL, el_index VARCHAR2(2048) NOT NULL ); +ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_pk PRIMARY KEY (el_id); ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_fk1 FOREIGN KEY (el_from) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.externallinks_i01 ON &mw_prefix.externallinks (el_from, el_to); CREATE INDEX &mw_prefix.externallinks_i02 ON &mw_prefix.externallinks (el_to, el_from); diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 9cbabfd..c6596a6 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -18,6 +18,8 @@ DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE; DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE; DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE; +DROP SEQUENCE IF EXISTS archive_ar_id_seq CASCADE; +DROP SEQUENCE IF EXISTS externallinks_el_id_seq CASCADE; DROP FUNCTION IF EXISTS page_deleted() CASCADE; DROP FUNCTION IF EXISTS ts2_page_title() CASCADE; DROP FUNCTION IF EXISTS ts2_page_text() CASCADE; @@ -156,7 +158,9 @@ CREATE INDEX page_props_propname ON page_props (pp_propname); CREATE UNIQUE INDEX pp_propname_page ON page_props (pp_propname,pp_page); +CREATE SEQUENCE archive_ar_id_seq; CREATE TABLE archive ( + ar_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('archive_ar_id_seq'), ar_namespace SMALLINT NOT NULL, ar_title TEXT NOT NULL, ar_text TEXT, -- technically should be bytea, but not used anymore @@ -174,7 +178,7 @@ ar_deleted SMALLINT NOT NULL DEFAULT 0, ar_len INTEGER NULL, ar_content_model TEXT, - ar_content_format TEXT + ar_content_format TEXT, ); CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp); CREATE INDEX archive_user_text ON archive (ar_user_text); @@ -224,7 +228,9 @@ CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to); CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from); +CREATE SEQUENCE externallinks_id_seq; CREATE TABLE externallinks ( + el_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_id_seq'), el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, el_to TEXT NOT NULL, el_index TEXT NOT NULL diff --git a/maintenance/sqlite/archives/initial-indexes.sql b/maintenance/sqlite/archives/initial-indexes.sql index 73b008c..ddeca0b 100644 --- a/maintenance/sqlite/archives/initial-indexes.sql +++ b/maintenance/sqlite/archives/initial-indexes.sql @@ -28,6 +28,8 @@ DROP TABLE IF EXISTS /*_*/page_restrictions_tmp; DROP TABLE IF EXISTS /*_*/protected_titles_tmp; DROP TABLE IF EXISTS /*_*/page_props_tmp; +DROP TABLE IF EXISTS /*_*/archive_tmp; +DROP TABLE IF EXISTS /*_*/externallinks_tmp; -------------------------------------------------------------------------------- -- Create new tables @@ -268,6 +270,47 @@ ); CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props_tmp (pp_page,pp_propname); +-- +-- Holding area for deleted articles, which may be viewed +-- or restored by admins through the Special:Undelete interface. +-- The fields generally correspond to the page, revision, and text +-- fields, with several caveats. +-- Cannot reasonably create views on this table, due to the presence of TEXT +-- columns. +CREATE TABLE /*$wgDBprefix*/archive_tmp ( + ar_id NOT NULL PRIMARY KEY clustered IDENTITY, + ar_namespace SMALLINT NOT NULL DEFAULT 0, + ar_title NVARCHAR(255) NOT NULL DEFAULT '', + ar_text NVARCHAR(MAX) NOT NULL, + ar_comment NVARCHAR(255) NOT NULL, + ar_user INT NULL REFERENCES /*$wgDBprefix*/[user](user_id) ON DELETE SET NULL, + ar_user_text NVARCHAR(255) NOT NULL, + ar_timestamp DATETIME NOT NULL DEFAULT GETDATE(), + ar_minor_edit BIT NOT NULL DEFAULT 0, + ar_flags NVARCHAR(255) NOT NULL, + ar_rev_id INT, + ar_text_id INT, + ar_deleted BIT NOT NULL DEFAULT 0, + ar_len INT DEFAULT NULL, + ar_page_id INT NULL, + ar_parent_id INT NULL, +); +CREATE INDEX /*$wgDBprefix*/ar_name_title_timestamp ON /*$wgDBprefix*/archive_tmp(ar_namespace,ar_title,ar_timestamp); +CREATE INDEX /*$wgDBprefix*/ar_usertext_timestamp ON /*$wgDBprefix*/archive_tmp(ar_user_text,ar_timestamp); +CREATE INDEX /*$wgDBprefix*/ar_user_text ON /*$wgDBprefix*/archive_tmp(ar_user_text); + +-- +-- Track links to external URLs +-- IE >= 4 supports no more than 2083 characters in a URL +CREATE TABLE /*$wgDBprefix*/externallinks_tmp ( + el_id INT NOT NULL PRIMARY KEY clustered IDENTITY, + el_from INT NOT NULL DEFAULT '0', + el_to VARCHAR(2083) NOT NULL, + el_index VARCHAR(896) NOT NULL, +); +-- Maximum key length ON SQL Server is 900 bytes +CREATE INDEX /*$wgDBprefix*/externallinks_index ON /*$wgDBprefix*/externallinks_tmp(el_index); + -------------------------------------------------------------------------------- -- Populate the new tables using INSERT SELECT -------------------------------------------------------------------------------- @@ -290,6 +333,8 @@ INSERT OR IGNORE INTO /*_*/page_restrictions_tmp SELECT * FROM /*_*/page_restrictions; INSERT OR IGNORE INTO /*_*/protected_titles_tmp SELECT * FROM /*_*/protected_titles; INSERT OR IGNORE INTO /*_*/page_props_tmp SELECT * FROM /*_*/page_props; +INSERT OR IGNORE INTO /*_*/archive_tmp SELECT * FROM /*_*/archive; +INSERT OR IGNORE INTO /*_*/externallinks_tmp SELECT * FROM /*_*/externallinks; -------------------------------------------------------------------------------- -- Do the table renames @@ -331,6 +376,10 @@ ALTER TABLE /*_*/protected_titles_tmp RENAME TO /*_*/protected_titles; DROP TABLE /*_*/page_props; ALTER TABLE /*_*/page_props_tmp RENAME TO /*_*/page_props; +DROP TABLE /*_*/archive; +ALTER TABLE /*_*/archive_tmp RENAME TO /*_*/archive; +DROP TABLE /*_*/externalllinks; +ALTER TABLE /*_*/externallinks_tmp RENAME TO /*_*/externallinks; -------------------------------------------------------------------------------- -- Drop and create tables with unique indexes but no valuable data diff --git a/maintenance/sqlite/archives/patch-archive-ar_id.sql b/maintenance/sqlite/archives/patch-archive-ar_id.sql new file mode 100644 index 0000000..b49bbc3 --- /dev/null +++ b/maintenance/sqlite/archives/patch-archive-ar_id.sql @@ -0,0 +1,39 @@ +DROP TABLE IF EXISTS /*_*/archive_tmp; + +CREATE TABLE /*$wgDBprefix*/archive_tmp ( + ar_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + ar_namespace int NOT NULL default 0, + ar_title varchar(255) binary NOT NULL default '', + ar_text mediumblob NOT NULL, + ar_comment tinyblob NOT NULL, + ar_user int unsigned NOT NULL default 0, + ar_user_text varchar(255) binary NOT NULL, + ar_timestamp binary(14) NOT NULL default '', + ar_minor_edit tinyint NOT NULL default 0, + ar_flags tinyblob NOT NULL, + ar_rev_id int unsigned, + ar_text_id int unsigned, + ar_deleted tinyint unsigned NOT NULL default 0, + ar_len int unsigned, + ar_page_id int unsigned, + ar_parent_id int unsigned default NULL, + ar_sha1 varbinary(32) NOT NULL default '', + ar_content_model varbinary(32) DEFAULT NULL, + ar_content_format varbinary(64) DEFAULT NULL, +); + +INSERT OR IGNORE INTO /*_*/archive_tmp ( + ar_namespace, ar_title, ar_title, ar_text, ar_comment, ar_user, ar_user_text, ar_timestamp, + ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, ar_deleted, ar_len, ar_page_id, ar_parent_id ) + SELECT + ar_namespace, ar_title, ar_title, ar_text, ar_comment, ar_user, ar_user_text, ar_timestamp, + ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, ar_deleted, ar_len, ar_page_id, ar_parent_id + FROM /*_*/archive; + +DROP TABLE /*_*/archive; + +ALTER TABLE /*_*/archive_tmp RENAME TO /*_*/archive; + +CREATE INDEX /*i*/name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp); +CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp); +CREATE INDEX /*i*/ar_revid ON /*_*/archive (ar_rev_id); diff --git a/maintenance/sqlite/archives/patch-externallinks-el_id.sql b/maintenance/sqlite/archives/patch-externallinks-el_id.sql new file mode 100644 index 0000000..0aad407 --- /dev/null +++ b/maintenance/sqlite/archives/patch-externallinks-el_id.sql @@ -0,0 +1,19 @@ +DROP TABLE IF EXISTS /*_*/externallinks_tmp; + +CREATE TABLE /*$wgDBprefix*/externallinks_tmp ( + el_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + el_from int unsigned NOT NULL default 0, + el_to blob NOT NULL, + el_index blob NOT NULL +); + +INSERT OR IGNORE INTO /*_*/externallinks_tmp (el_from, el_to, el_index) SELECT + el_from, el_to, el_index FROM /*_*/externallinks; + +DROP TABLE /*_*/externallinks; + +ALTER TABLE /*_*/externallinks_tmp RENAME TO /*_*/externallinks; + +CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from, el_to(40)); +CREATE INDEX /*i*/el_to ON /*_*/externallinks (el_to(60), el_from); +CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index(60)); \ No newline at end of file diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 97d6ff2..8877f09 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -378,6 +378,8 @@ -- fields, with several caveats. -- CREATE TABLE /*_*/archive ( + -- Primary key + ar_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, ar_namespace int NOT NULL default 0, ar_title varchar(255) binary NOT NULL default '', @@ -442,8 +444,7 @@ ar_content_model varbinary(32) DEFAULT NULL, -- content format, see CONTENT_FORMAT_XXX constants - ar_content_format varbinary(64) DEFAULT NULL - + ar_content_format varbinary(64) DEFAULT NULL, ) /*$wgDBTableOptions*/; CREATE INDEX /*i*/name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp); @@ -600,6 +601,9 @@ -- Track links to external URLs -- CREATE TABLE /*_*/externallinks ( + -- Primary key + el_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + -- page_id of the referring page el_from int unsigned NOT NULL default 0, @@ -623,7 +627,6 @@ CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from, el_to(40)); CREATE INDEX /*i*/el_to ON /*_*/externallinks (el_to(60), el_from); CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index(60)); - -- -- Track external user accounts, if ExternalAuth is used @@ -1083,7 +1086,7 @@ -- Visibility of recent changes items, bitfield rc_deleted tinyint unsigned NOT NULL default 0, - -- Value corresonding to log_id, specific log entries + -- Value corresponding to log_id, specific log entries rc_logid int unsigned NOT NULL default 0, -- Store log type info here, or null rc_log_type varbinary(255) NULL default NULL, -- To view, visit https://gerrit.wikimedia.org/r/51675 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ib6b0fc3736d173fa4ba7b786ecfc710b2f4711bb Gerrit-PatchSet: 1 Gerrit-Project: mediawiki/core Gerrit-Branch: master Gerrit-Owner: Reedy <re...@wikimedia.org> Gerrit-Reviewer: leucosticte <nathanlarson3...@gmail.com> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits