From 7694faf68f975ea9c4888d575b33dabb84e90083 Mon Sep 17 00:00:00 2001 From: "Marc A. Pelletier" Date: Tue, 27 Mar 2012 22:44:32 -0400 Subject: [PATCH] (bug 5445) remove autoblocks when user is unblocked Previously, whenever we blocked a user, its IP address would be autoblocked whenever he tries to edit a page. Thus when later unblocking the username, he would be automatically blocked again if we forgot to clean up is IP. This patch introduces a the ipb_parent_block_id column in ipblocks table to track which block triggered the autoblock command. Thus, when deleting the original block we can easily remove all subsequentautoblocks. Schema updaters for MySQL, SQLite and postgres have been added to the patch but not for the other database types such as ibm_db2, mssql and Oracle. Change-Id: I4aa820ae9bbd962a12d0b48b6c638a1b6ff4efc9 --- includes/Block.php | 9 +++++++-- includes/installer/MysqlUpdater.php | 2 ++ includes/installer/PostgresUpdater.php | 3 +++ includes/installer/SqliteUpdater.php | 2 ++ maintenance/archives/patch-ipb-parent-block-id-index.sql | 2 ++ maintenance/archives/patch-ipb-parent-block-id.sql | 3 +++ maintenance/ibm_db2/tables.sql | 4 +++- maintenance/mssql/tables.sql | 1 + maintenance/oracle/tables.sql | 3 ++- maintenance/postgres/tables.sql | 4 +++- maintenance/tables.sql | 9 ++++++++- 11 files changed, 36 insertions(+), 6 deletions(-) create mode 100644 maintenance/archives/patch-ipb-parent-block-id-index.sql create mode 100644 maintenance/archives/patch-ipb-parent-block-id.sql diff --git a/includes/Block.php b/includes/Block.php index dff1a5d336..42504bed17 100644 --- a/includes/Block.php +++ b/includes/Block.php @@ -28,7 +28,8 @@ class Block { $mBlockEmail, $mDisableUsertalk, - $mCreateAccount; + $mCreateAccount, + $mParentBlockId; /// @var User|String protected $target; @@ -368,6 +369,7 @@ class Block { $this->mAuto = $row->ipb_auto; $this->mHideName = $row->ipb_deleted; $this->mId = $row->ipb_id; + $this->mParentBlockId = $row->ipb_parent_block_id; // I wish I didn't have to do this $db = wfGetDB( DB_SLAVE ); @@ -411,6 +413,7 @@ class Block { } $dbw = wfGetDB( DB_MASTER ); + $dbw->delete( 'ipblocks', array( 'ipb_parent_block_id' => $this->getId() ), __METHOD__ ); $dbw->delete( 'ipblocks', array( 'ipb_id' => $this->getId() ), __METHOD__ ); return $dbw->affectedRows() > 0; @@ -508,7 +511,8 @@ class Block { 'ipb_range_end' => $this->getRangeEnd(), 'ipb_deleted' => intval( $this->mHideName ), // typecast required for SQLite 'ipb_block_email' => $this->prevents( 'sendemail' ), - 'ipb_allow_usertalk' => !$this->prevents( 'editownusertalk' ) + 'ipb_allow_usertalk' => !$this->prevents( 'editownusertalk' ), + 'ipb_parent_block_id' => $this->mParentBlockId ); return $a; @@ -666,6 +670,7 @@ class Block { # Continue suppressing the name if needed $autoblock->mHideName = $this->mHideName; $autoblock->prevents( 'editownusertalk', $this->prevents( 'editownusertalk' ) ); + $autoblock->mParentBlockId = $this->mId; if ( $this->mExpiry == 'infinity' ) { # Original block was indefinite, start an autoblock now diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 5e6ae7eafb..d577e5146a 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -196,6 +196,8 @@ class MysqlUpdater extends DatabaseUpdater { // 1.20 array( 'addTable', 'config', 'patch-config.sql' ), array( 'addIndex', 'revision', 'page_user_timestamp', 'patch-revision-user-page-index.sql' ), + array( 'addField', 'ipblocks', 'ipb_parent_block_id', 'patch-ipb-parent-block-id.sql' ), + array( 'addIndex', 'ipblocks', 'ipb_parent_block_id', 'patch-ipb-parent-block-id-index.sql' ), ); } diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index a03c6c4520..115f798c7c 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -97,6 +97,7 @@ class PostgresUpdater extends DatabaseUpdater { array( 'addPgField', 'ipblocks', 'ipb_create_account', 'SMALLINT NOT NULL DEFAULT 1' ), array( 'addPgField', 'ipblocks', 'ipb_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), array( 'addPgField', 'ipblocks', 'ipb_enable_autoblock', 'SMALLINT NOT NULL DEFAULT 1' ), + array( 'addPgField', 'ipblocks', 'ipb_parent_block_id', 'INTEGER DEFAULT NULL REFERENCES ipblocks(ipb_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED' ), array( 'addPgField', 'filearchive', 'fa_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), array( 'addPgField', 'logging', 'log_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), array( 'addPgField', 'logging', 'log_id', "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq')" ), @@ -194,6 +195,7 @@ class PostgresUpdater extends DatabaseUpdater { # New indexes array( 'addPgIndex', 'archive', 'archive_user_text', '(ar_user_text)' ), array( 'addPgIndex', 'image', 'img_sha1', '(img_sha1)' ), + array( 'addPgIndex', 'ipblocks', 'ipb_parent_block_id', '(ipb_parent_block_id)' ), array( 'addPgIndex', 'oldimage', 'oi_sha1', '(oi_sha1)' ), array( 'addPgIndex', 'page', 'page_mediawiki_title', '(page_title) WHERE page_namespace = 8' ), array( 'addPgIndex', 'pagelinks', 'pagelinks_title', '(pl_title)' ), @@ -290,6 +292,7 @@ class PostgresUpdater extends DatabaseUpdater { array( 'changeFkeyDeferrable', 'imagelinks', 'il_from', 'page(page_id) ON DELETE CASCADE' ), array( 'changeFkeyDeferrable', 'ipblocks', 'ipb_by', 'mwuser(user_id) ON DELETE CASCADE' ), array( 'changeFkeyDeferrable', 'ipblocks', 'ipb_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'ipblocks', 'ipb_parent_block_id', 'ipblocks(ipb_id) ON DELETE SET NULL' ), array( 'changeFkeyDeferrable', 'langlinks', 'll_from', 'page(page_id) ON DELETE CASCADE' ), array( 'changeFkeyDeferrable', 'logging', 'log_user', 'mwuser(user_id) ON DELETE SET NULL' ), array( 'changeFkeyDeferrable', 'oldimage', 'oi_name', 'image(img_name) ON DELETE CASCADE ON UPDATE CASCADE' ), diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index a98c4db83a..373c9fb642 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -75,6 +75,8 @@ class SqliteUpdater extends DatabaseUpdater { // 1.20 array( 'addTable', 'config', 'patch-config.sql' ), array( 'addIndex', 'revision', 'page_user_timestamp', 'patch-revision-user-page-index.sql' ), + array( 'addField', 'ipblocks', 'ipb_parent_block_id', 'patch-ipb-parent-block-id.sql' ), + array( 'addIndex', 'ipblocks', 'ipb_parent_block_id', 'patch-ipb-parent-block-id-index.sql' ), ); } diff --git a/maintenance/archives/patch-ipb-parent-block-id-index.sql b/maintenance/archives/patch-ipb-parent-block-id-index.sql new file mode 100644 index 0000000000..1f413f37a5 --- /dev/null +++ b/maintenance/archives/patch-ipb-parent-block-id-index.sql @@ -0,0 +1,2 @@ +-- index for ipblocks.ipb_parent_block_id +CREATE INDEX /*i*/ipb_parent_block_id ON /*_*/ipblocks (ipb_parent_block_id); diff --git a/maintenance/archives/patch-ipb-parent-block-id.sql b/maintenance/archives/patch-ipb-parent-block-id.sql new file mode 100644 index 0000000000..8ebcf7863b --- /dev/null +++ b/maintenance/archives/patch-ipb-parent-block-id.sql @@ -0,0 +1,3 @@ +-- Adding ipb_parent_block_id to track the block that caused an autoblock +ALTER TABLE /*$wgDBprefix*/ipblocks + ADD ipb_parent_block_id int DEFAULT NULL; diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql index 66fc6564d9..f5162c94f2 100644 --- a/maintenance/ibm_db2/tables.sql +++ b/maintenance/ibm_db2/tables.sql @@ -371,7 +371,9 @@ CREATE TABLE ipblocks ( ipb_range_end VARCHAR(1024), ipb_deleted SMALLINT NOT NULL DEFAULT 0, ipb_block_email SMALLINT NOT NULL DEFAULT 0, - ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0 + ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0, + ipb_parent_block_id INTEGER DEFAULT NULL + -- REFERENCES ipblocks(ipb_id) ON DELETE SET NULL ); CREATE INDEX ipb_address diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 8c4d500876..a0c3d17ba9 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -396,6 +396,7 @@ CREATE TABLE /*$wgDBprefix*/ipblocks ( ipb_deleted BIT NOT NULL DEFAULT 0, ipb_block_email BIT NOT NULL DEFAULT 0, ipb_allow_usertalk BIT NOT NULL DEFAULT 0, + ipb_parent_block_id INT DEFAULT NULL, ); -- Unique index to support "user already blocked" messages -- Any new options which prevent collisions should be included diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 9f875aed50..3722120519 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -272,7 +272,8 @@ CREATE TABLE &mw_prefix.ipblocks ( ipb_range_end VARCHAR2(255), ipb_deleted CHAR(1) DEFAULT '0' NOT NULL, ipb_block_email CHAR(1) DEFAULT '0' NOT NULL, - ipb_allow_usertalk CHAR(1) DEFAULT '0' NOT NULL + ipb_allow_usertalk CHAR(1) DEFAULT '0' NOT NULL, + ipb_parent_block_id NUMBER DEFAULT NULL ); ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_pk PRIMARY KEY (ipb_id); ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_fk1 FOREIGN KEY (ipb_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index b0cdb84534..1e3eecb53b 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -277,12 +277,14 @@ CREATE TABLE ipblocks ( 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 + ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0, + ipb_parent_block_id INTEGER NULL REFERENCES ipblocks(ipb_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); 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); +CREATE INDEX ipb_parent_block_id ON ipblocks (ipb_parent_block_id); CREATE TABLE image ( diff --git a/maintenance/tables.sql b/maintenance/tables.sql index a848bf5eb4..0a5b2fb75a 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -772,7 +772,13 @@ CREATE TABLE /*_*/ipblocks ( ipb_block_email bool NOT NULL default 0, -- Block allows user to edit their own talk page - ipb_allow_usertalk bool NOT NULL default 0 + ipb_allow_usertalk bool NOT NULL default 0, + + -- ID of the block that caused this block to exist + -- Autoblocks set this to the original block + -- so that the original block being deleted also + -- deletes the autoblocks + ipb_parent_block_id int default NULL ) /*$wgDBTableOptions*/; @@ -784,6 +790,7 @@ CREATE INDEX /*i*/ipb_user ON /*_*/ipblocks (ipb_user); CREATE INDEX /*i*/ipb_range ON /*_*/ipblocks (ipb_range_start(8), ipb_range_end(8)); CREATE INDEX /*i*/ipb_timestamp ON /*_*/ipblocks (ipb_timestamp); CREATE INDEX /*i*/ipb_expiry ON /*_*/ipblocks (ipb_expiry); +CREATE INDEX /*i*/ipb_parent_block_id ON /*_*/ipblocks (ipb_parent_block_id); -- -- 2.20.1