=== Draft schema ===
* Move the target-related fields in ipblocks to a separate table.
* bt_address will be an empty string for user* Rename ipblocks to blocks.
* User list queries that join on ipblocks to filter out hidden users should ideally be migrated to use a new actor or user field.
* Copy ipb_deleted into a new table indexed by actor_idbt_address will be null for user blocks.
```lang=sql
CREATE TABLE block_target (
bt_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
bt_address TINYBLOB NOT NULL,
bt_user INT UNSIGNED DEFAULT 0 NOT NULL,
bt_auto TINYINT(1) DEFAULT 0 NOT NULL,
bt_range_start TINYBLOB NOT NULL,
bt_range_end TINYBLOB NOT NULL,
bt_count int NOT NULL default 0,
INDEX bt_address (
bt_address(255),
-- No need for bt_user here
bt_auto
),
INDEX bt_user (bt_user),
INDEX bt_range (
bt_range_start(8),
bt_range_end(8)
)
);
CREATE TABLE ipblocks (
ipb_idbl_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
-- New foreign key
ipbbl_target INT UNSIGNED NOT NULL,
ipbbl_by_actor BIGINT UNSIGNED NOT NULL,
ipbbl_reason_id BIGINT UNSIGNED NOT NULL,
ipbbl_timestamp BINARY(14) NOT NULL,
ipbbl_anon_only TINYINT(1) DEFAULT 0 NOT NULL,
ipbbl_create_account TINYINT(1) DEFAULT 1 NOT NULL,
ipbbl_enable_autoblock TINYINT(1) DEFAULT 1 NOT NULL,
ipbbl_expiry VARBINARY(14) NOT NULL,
ipbbl_deleted TINYINT(1) DEFAULT 0 NOT NULL,
ipbbl_block_email TINYINT(1) DEFAULT 0 NOT NULL,
ipbbl_allow_usertalk TINYINT(1) DEFAULT 0 NOT NULL,
ipbbl_parent_block_id INT UNSIGNED DEFAULT NULL,
ipbbl_sitewide TINYINT(1) DEFAULT 1 NOT NULL,
-- Index on new field
INDEX ipbbl_target (ipbbl_target),
INDEX ipbbl_timestamp (ipbbl_timestamp),
INDEX ipbbl_expiry (ipbbl_expiry),
INDEX ipbbl_parent_block_id (ipbbl_parent_block_id),
PRIMARY KEY(ipb_idbl_id)
);
```
=== Query review ===
Searched for /['"]ipblocks['"]/, DatabaseBlock::getQueryInfo
* Writers
- DatabaseBlockStore
- CentralAuthUser::doLocalSuppression (cross-wiki)
- MergeUser::mergeBlocks
- RenameuserSQL
- CleanupBlocks
- CleanupUsersWithNoId (probably broken already)
* Queries only for ipb_deleted
- ApiQueryBlockInfoTrait branch 2
- ActiveUsersPager
- UsersPager
- UserNamePrefixSearch
- UserSelectQueryBuilder
- LocalIdLookup
- Flow: OneStepUserNameQuery, TwoStepUserNameQuery
- WMCS maintain_views.yaml user, user_old, actor
* Readers
- ApiQueryBlocks
- ApiQueryBlockInfoTrait branch 1
- DatabaseBlockStore
- BlockListPager
- CentralAuthUser::localUserData (cross-wiki)
- \MediaWiki\CheckUser\Investigate\Services\PreliminaryCheckService::isUserBlocked
- UncachedMenteeOverviewDataProvider::getFilteredMenteesForMentor
- \MediaWiki\Extension\PageTriage\ArticleCompile\ArticleCompileUserData::compile
- PageTriage ArticleCompileUserData
- WMCS maintain_views.yaml
* Referring to the table but probably not affected
- BlockRestrictionStore::updateByParentBlockId
- findMissingActors
- removeUnusedAccounts
=== User suppression/hiding (ipb_deleted) ===
We could copy the deletion status of a user out to a separate table, and join on the other table instead of joining on ipblocks for ipb_deleted. This would simplify subsequent migration to `block_target`, although it has its own complexities.
Details at T346716.
=== ipb_address normalization ===
Make ipb_address be blanknull for user blocks, so that we don't have to update it in RenameUser.
Details moved to: T346683
=== Migration ===
CentralAuth does cross-wiki read and write queries on ipblocks. This would complicate fast single-flag migration.
Multi-stage migration would have the following configuration in MW core: T346671
* $wgBlockTargetMigrationStage: a combination of SCHEMA_COMPAT_xxx flags.
* $wgEnableMultiBlocks: when this is false, the number of blocks of a given target is limited to 1.
The old Special:Block UI is shown. When this is true, multiple blocks of a target are permitted.
The deployment procedure would be as follows. Each step is global, affecting all wikis.
* Deploy the new code in read-old/write-old mode.
* Create block_target tables.
* Alter ipblocks to add ipb_target, and remove the unique index constraint. Now duplicate blocks are only prevented by pre-insertion select queri and block_target tables.
* Switch to write-both mode. Assume the migration script will copy each ipblocks row atomically, with ipb_id=bl_id. When updating a block, update it in ipblocks, populate block_target/ipb_target of existand if there is a corresponding conflictingrow in the blocks. table, When inserting a block,update it there too. follow the new-style conflict detection.When inserting a block, The number of blocks of a given target is artificially limited to 1insert it into both tables. When deleting a block,The number of blocks of a given target is artificially limited to 1. update block_target if ipb_target is nonzero.When deleting a block, But keepdelete the block_target row if the old fields like ipb_user updatednumber of blocks on the target goes to zero.
* Run a script to fully populate block_target/ipb_targetblock.
* Migrate WMCS and other miscellaneous readers.
* Switch to read-new/write-new mode. It is no longer necessary to upgrade rows when updating a block.
* $wgEnableMultiBlocks can now be set to true.
* Drop the old fields and indexes in ipblocksipblocks tables.