Page MenuHomePhabricator

Change storage of organizer roles
Closed, ResolvedPublic2 Estimated Story Points


As discussed in today's engineering meeting, we would like to simplify the ce_organizers table so that certain operations like limit, count, and pagination are easier.

Rough proposal

We currently store tuples of (event, user, role), so there can be multiple rows for the same event+user combination, one for each role. The new proposal is that we make the "roles" column a bit set, and store all roles in a single row.

The motivations for this are:

  • Limit, count, and pagination would be much easier
  • We don't need timestamps (created_at, deleted_at) for each role


  • Rename the column
  • Remove it from the index
  • Adjust logic in OrganizersStore

Deployment plan

  • Write the patch that changes the schema in the code. Review it, but DO NOT MERGE IT. Only give it a +1, explicitly writing in a comment that the +1 means you are approving the change (and not that you have a working mouse).
  • Merge the patch and wait for it to reach beta
  • Immediately rename the column on beta

ALTERs to run on beta

Before merging the patch: nothing. Note that DB errors are expected in the short period between when the patch reaches beta and the queries below are run.

After merging the patch (noting that beta runs MariaDB 10.6, so we can use RENAME COLUMN):

ALTER TABLE ce_organizers DROP INDEX ceo_event_user_role;
ALTER TABLE ce_organizers RENAME COLUMN ceo_role_id TO ceo_roles;
ALTER TABLE ce_organizers ADD UNIQUE INDEX ceo_event_user (ceo_event_id, ceo_user_id);
NOTE: Since we're not doing this in a backwards-compatible way, you will have to alter the tables manually on your local once the patch is merged

Event Timeline

Restricted Application added a subscriber: Aklapper. ยท View Herald TranscriptSep 22 2022, 10:00 PM

Change 834556 had a related patch set uploaded (by Daimona Eaytoy; author: Daimona Eaytoy):

[mediawiki/extensions/CampaignEvents@master] Change storage of organizer roles

Change 834556 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] Change storage of organizer roles

Mentioned in SAL (#wikimedia-releng) [2022-09-26T21:46:21Z] <Daimona> Applying schema changes to the wikishared DB on beta for the CampaignEvents extension # T318379 T318120

Daimona removed a project: Patch-For-Review.
Daimona added a subscriber: vaughnwalters.

@vaughnwalters For testing, you essentially only need to ensure that the schema is up-to-date on beta, and that lists of participants are still working ("more details" dialog on event page and Special:EventDetails; possibly also the numbers shown on Special:MyEvents).

โœ… schema changes in the AC are correct in betacluster:

Screen Shot 2022-09-28 at 3.42.27 PM.png (388ร—1 px, 89 KB)

Screen Shot 2022-09-28 at 3.34.54 PM.png (364ร—2 px, 132 KB)

The lists on "more details" dialog on event page and Special:EventDetails and numbers on Special:MyEvents still functioning correctly:

Screen Recording 2022-09-29 at 11.03.33 AM.gif (1ร—2 px, 3 MB)

moving this to done / resolved