Change Details

afl_filter is currently a `varchar(64) binary NOT NULL` field. This is because, since global filters have been introduced, that field could hold either a plain numeric ID or the string "global-ID", where ID is again a numeric identifier. The alter was done in patch-global_filters.sql introduced in rEABF14b850f891de27ea09a1439e3835f66c49ad773f, and it was a poor choice. It denormalized more the structure of the DB, and the code was never properly changed to handle afl_filter as a string. In fact, until yesterday there were 4 JOINs on `af_id=afl_filter`, which is a join between an integer and a string. That syntax isn't even supported in postgres, and at any rate, it slows down the query a bit because every row has to be implicitly cast before the comparison. So basically we've lived with a stopgap in place for 10 years, and it's time to get rid of it. The proposal (already implemented) is to split the afl_filter in ``` afl_filter_id BIGINT unsigned NOT NULL, afl_global tinyint(1) NOT NULL ``` and clean up the code accordingly. This also has the benefit of encouraging the integer type for filter IDs all around the code, since we won't need string IDs anymore. === Plan === [x] Create the new schema [x] Merge [[https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/459818/ | r459818]] [x] Migrate WMF wikis (T269712) [x] Set default to SCHEMA_COMPAT_WRITE_BOTH | SCHEMA_COMPAT_READ_OLD in 1.36 (no need for COMPAT_OLD because the schema was introduced in 1.34) [x] Set default to SCHEMA_COMPAT_NEW in 1.37 (so people can run the script in the meanwhile) [x] Remove old stuff in 1.38 : [[https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/488482 | r488482 ]] [ ] Delete the old column on WMF wikis (task TBC)

afl_filter is currently a `varchar(64) binary NOT NULL` field. This is because, since global filters have been introduced, that field could hold either a plain numeric ID or the string "global-ID", where ID is again a numeric identifier. The alter was done in patch-global_filters.sql introduced in rEABF14b850f891de27ea09a1439e3835f66c49ad773f, and it was a poor choice. It denormalized more the structure of the DB, and the code was never properly changed to handle afl_filter as a string. In fact, until yesterday there were 4 JOINs on `af_id=afl_filter`, which is a join between an integer and a string. That syntax isn't even supported in postgres, and at any rate, it slows down the query a bit because every row has to be implicitly cast before the comparison. So basically we've lived with a stopgap in place for 10 years, and it's time to get rid of it. The proposal (already implemented) is to split the afl_filter in ``` afl_filter_id BIGINT unsigned NOT NULL, afl_global tinyint(1) NOT NULL ``` and clean up the code accordingly. This also has the benefit of encouraging the integer type for filter IDs all around the code, since we won't need string IDs anymore. === Plan === [x] Create the new schema [x] Merge [[https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/459818/ | r459818]] [x] Migrate WMF wikis (T269712) [x] Set default to SCHEMA_COMPAT_WRITE_BOTH | SCHEMA_COMPAT_READ_OLD in 1.36 (no need for COMPAT_OLD because the schema was introduced in 1.34) [x] Set default to SCHEMA_COMPAT_NEW in 1.37 (so people can run the script in the meanwhile) [x] Remove old stuff in 1.38 : [[https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/488482 | r488482 ]] [ ] Delete the old column on WMF wikis (T291719)

afl_filter is currently a `varchar(64) binary NOT NULL` field. This is because, since global filters have been introduced, that field could hold either a plain numeric ID or the string "global-ID", where ID is again a numeric identifier. The alter was done in patch-global_filters.sql introduced in rEABF14b850f891de27ea09a1439e3835f66c49ad773f, and it was a poor choice. It denormalized more the structure of the DB, and the code was never properly changed to handle afl_filter as a string. In fact, until yesterday there were 4 JOINs on `af_id=afl_filter`, which is a join between an integer and a string. That syntax isn't even supported in postgres, and at any rate, it slows down the query a bit because every row has to be implicitly cast before the comparison. So basically we've lived with a stopgap in place for 10 years, and it's time to get rid of it. The proposal (already implemented) is to split the afl_filter in ``` afl_filter_id BIGINT unsigned NOT NULL, afl_global tinyint(1) NOT NULL ``` and clean up the code accordingly. This also has the benefit of encouraging the integer type for filter IDs all around the code, since we won't need string IDs anymore. === Plan === [x] Create the new schema [x] Merge [[https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/459818/ | r459818]] [x] Migrate WMF wikis (T269712) [x] Set default to SCHEMA_COMPAT_WRITE_BOTH | SCHEMA_COMPAT_READ_OLD in 1.36 (no need for COMPAT_OLD because the schema was introduced in 1.34) [x] Set default to SCHEMA_COMPAT_NEW in 1.37 (so people can run the script in the meanwhile) [x] Remove old stuff in 1.38 : [[https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/488482 | r488482 ]] [ ] Delete the old column on WMF wikis (task TBCT291719)