Page MenuHomePhabricator

Re-implement tracking tools schema
Closed, ResolvedPublic

Description

Context: we need to reimplement the schema so that:

  • it can support tools other than just the P&E dashboard
  • it does not duplicate information in each record (e.g., repeating the base URL in each row)
  • it stores a unique identifier of a dashboard that we can use in the code to make API calls etc.

The campaign_events table would have two fields, event_tracking_tool_id and event_tracking_tool_event_id. The former is int|null and refers to a unique identifier of each tracking tool, as determined by the application (see T312869). The latter would be a string|null field holding the identifier of the event inside that tool (could be a numeric ID, or a name, or something else depending on the tool).

The application logic would use these fields for its various use cases (again, see T312869).

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).
  • Create the new columns on beta (both are default null), and add defaults to the old columns
  • Merge the patch and wait for it to reach beta
  • Drop the old columns on beta

ALTERs to run on beta

Before merging the patch:

ALTER TABLE campaign_events ALTER COLUMN event_tracking_tool SET DEFAULT '';
ALTER TABLE campaign_events ALTER COLUMN event_tracking_url SET DEFAULT '';
ALTER TABLE campaign_events ADD COLUMN event_tracking_tool_id INT DEFAULT NULL AFTER event_chat_url;
ALTER TABLE campaign_events ADD COLUMN event_tracking_tool_event_id BLOB DEFAULT NULL AFTER event_tracking_tool_id;

After merging the patch:

ALTER TABLE campaign_events DROP COLUMN event_tracking_tool;
ALTER TABLE campaign_events DROP COLUMN event_tracking_url;
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

vyuen lowered the priority of this task from High to Medium.Aug 30 2022, 12:11 PM
vyuen moved this task from Darkship to V1 (MVP) on the Campaign-Registration board.

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

[mediawiki/extensions/CampaignEvents@master] Update schema for tracking tools

https://gerrit.wikimedia.org/r/828612

Mentioned in SAL (#wikimedia-releng) [2022-09-09T16:36:13Z] <Daimona> Applying schema change to the wikishared DB on beta for the CampaignEvents extension (1/2) # T316409

Change 828612 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] Update schema for tracking tools

https://gerrit.wikimedia.org/r/828612

Mentioned in SAL (#wikimedia-releng) [2022-09-09T17:08:20Z] <Daimona> Applying schema change to the wikishared DB on beta for the CampaignEvents extension (2/2) # T316409

vaughnwalters added a subscriber: vaughnwalters.

Betacluster tables have been ALTERed correctly according to SQL statements in the ticket description

event_tracking_tool_id INT and event_tracking_tool_event_id BLOB have been added to campaign_events table and are NULL
event_tracking_tool and event_tracking_url are not present on campaign_events table.

MariaDB [wikishared]> DESCRIBE campaign_events;
+------------------------------+---------------------+------+-----+---------+----------------+
| Field                        | Type                | Null | Key | Default | Extra          |
+------------------------------+---------------------+------+-----+---------+----------------+
| event_id                     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| event_name                   | varbinary(255)      | NO   |     | NULL    |                |
| event_page_namespace         | int(10) unsigned    | NO   |     | NULL    |                |
| event_page_title             | varbinary(255)      | NO   |     | NULL    |                |
| event_page_wiki              | varbinary(64)       | NO   | MUL | NULL    |                |
| event_page_prefixedtext      | varbinary(512)      | NO   |     | NULL    |                |
| event_chat_url               | blob                | NO   |     | NULL    |                |
| event_tracking_tool_id       | int(11)             | YES  |     | NULL    |                |
| event_tracking_tool_event_id | blob                | YES  |     | NULL    |                |
| event_status                 | int(11)             | NO   |     | NULL    |                |
| event_timezone               | varbinary(64)       | NO   | MUL | NULL    |                |
| event_start_local            | binary(14)          | NO   |     | NULL    |                |
| event_start_utc              | binary(14)          | NO   |     | NULL    |                |
| event_end_local              | binary(14)          | NO   |     | NULL    |                |
| event_end_utc                | binary(14)          | NO   |     | NULL    |                |
| event_type                   | varbinary(255)      | NO   |     | NULL    |                |
| event_meeting_type           | int(11)             | NO   |     | NULL    |                |
| event_meeting_url            | blob                | NO   |     | NULL    |                |
| event_meeting_country        | varbinary(255)      | NO   |     | NULL    |                |
| event_meeting_address        | blob                | NO   |     | NULL    |                |
| event_created_at             | binary(14)          | NO   |     | NULL    |                |
| event_last_edit              | binary(14)          | NO   |     | NULL    |                |
| event_deleted_at             | binary(14)          | YES  |     | NULL    |                |
+------------------------------+---------------------+------+-----+---------+----------------+

Because the tables have the correct updated schema as mentioned in the task description, I am marking this as done.