=== 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.
```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 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)
);
```
=== 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) ===
The query review highlighted existing tech debt in the area of user suppression.
The UI for hiding usernames is part of Special:Block. Blocking a user with the "hide user" option causes the user's name to be hidden everywhere. Unblocking such a user causes the user's name to be shown everywhere. In changes (revisions etc.), this is implemented by denormalizing the user-hidden flag into the *_deleted bitfield. In user lists, there is no such denormalization, so everything wanting a user list must join on ipblocks and filter by ipb_deleted.
In the WMCS database replicas, the actor_p view attempts to respect the denormalized field values by joining on every change-related table (archive, image, oldimage, etc.). But each user can only be hidden or not hidden -- the DB modification code does not allow users to be hidden in some changes and not others. If users were somehow hidden in some changes but not others, hiding and unhiding the user would wipe that data.
If time allows, I would deprecate the DELETED_USER value in the *_deleted bitfields and instead denormalize the flag into the actor table. This would improve the worst-case performance of user hiding actions, would simplify user lists, and would simplify WMCS views. It would reduce the number of callers that join on ipblocks and so would simplify the multiblocks migration.
=== ipb_address normalization ===
The polymorphic ipb_address field can contain an IP address, an IP range in CIDR notation, or a username. The username case is one of the few places that missed out on actor migration, so updating it is still required when users are renamed.
Direct references to ipb_address are rare in core and extensions. It would be simple to stop reading this field for user blocks. Then we could stop updating it and a script could blank it for user-block related rows.
The Block interface does expose ipb_address as Block::getTargetName(), but internally it is already discriminated, with a user block being stored as a UserIdentity.
DatabaseBlock::initFromRow() calls AbstractBlock::setTarget() with ipb_address which calls BlockUtils::parseBlockTarget() which converts the string to a UserIdentity. Instead DatabaseBlock::initFromRow() would make a UserIdentity from ipb_user, optionally joined on actor for the name, and would pass the UserIdentity to setTarget().
Special:BlockList does not sort by ipb_address, and searches are already aware of the target type.
ApiQueryBlocks searches ipb_address for user input, so will need to parse its input.
=== 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:
* $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.