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