=== Draft schema ===
* Move the target-related fields in ipblocks to a separate table.
* bt_address will be an empty string for user 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_id.
```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_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
-- New foreign key
ipb_target INT UNSIGNED NOT NULL,
ipb_by_actor BIGINT UNSIGNED NOT NULL,
ipb_reason_id BIGINT UNSIGNED NOT NULL,
ipb_timestamp BINARY(14) NOT NULL,
ipb_anon_only TINYINT(1) DEFAULT 0 NOT NULL,
ipb_create_account TINYINT(1) DEFAULT 1 NOT NULL,
ipb_enable_autoblock TINYINT(1) DEFAULT 1 NOT NULL,
ipb_expiry VARBINARY(14) NOT NULL,
-- ipb_deleted becomes a foreign key link to actor_deleted.ad_id
ipb_deleted_id INT UNSIGNED,
TINYINT(1) DEFAULT 0 NOT NULL,
ipb_block_email TINYINT(1) DEFAULT 0 NOT NULL,
ipb_allow_usertalk TINYINT(1) DEFAULT 0 NOT NULL,
ipb_parent_block_id INT UNSIGNED DEFAULT NULL,
ipb_sitewide TINYINT(1) DEFAULT 1 NOT NULL,
-- Index on new field
INDEX ipb_target (ipb_target),
INDEX ipb_timestamp (ipb_timestamp),
INDEX ipb_expiry (ipb_expiry),
INDEX ipb_parent_block_id (ipb_parent_block_id),
PRIMARY KEY(ipb_id)
);
CREATE TABLE actor_deleted (
ad_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
ad_actor BIGINT UNSIGNED NOT NULL,
ad_user INT UNSIGNED,
PRIMARY KEY(ad_id),
INDEX ad_actor (ad_actor),
INDEX ad_user (ad_user)
);
```
=== 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 blank 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 queries.
* Switch to write-both mode. When updating a block, populate block_target/ipb_target of existing conflicting blocks. When inserting a block, follow the new-style conflict detection. The number of blocks of a given target is artificially limited to 1. When deleting a block, update block_target if ipb_target is nonzero. But keep the old fields like ipb_user updated.
* Run a script to fully populate block_target/ipb_target.
* 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 ipblocks.