Page MenuHomePhabricator

Store tracking tools in a separate DB table
Closed, ResolvedPublic

Description

As described in T320434#8741708, we should create a new table for storing tracking tools:

CREATE TABLE ce_tracking_tools (
  cett_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, -- Surrogate primary key
  cett_event BIGINT UNSIGNED NOT NULL, -- Foreign key to campaign_events.event_id
  cett_tool_id INT NOT NULL, -- Identifier of the tool, same as the existing campaign_events.event_tracking_tool_id
  cett_tool_event_id BLOB NOT NULL, -- Identifier of the event within this tool, same as the existing campaign_events.event_tracking_tool_event_id
  cett_sync_status INT NOT NULL, -- Status of the sync, the meaning of each value is defined in the application
  cett_last_sync BINARY(14) NOT NULL -- Timestamp of the last synchronization
);

Also, campaign_events.event_tracking_tool_id and campaign_events.event_tracking_tool_event_id need to be dropped. Note that these fields can only be NULL for now.

Note: this only has to be backwards-compatible in the sense that the code won't crash: the schema is still unused, so we don't really care what is being read/written. Also, we don't need all the files for the schema change: both production and the beta cluster use a database other than the wiki's local DB, meaning schema changes cannot be applied automatically anyway (related: T314908). On top of that, the extension is still marked as experimental and no-one else should be using it. We will use a simple boolean variable to control the migration, whose values correspond to the write_old_read_old and write_new_read_new stages.

Acceptance criteria

  • Create the new table in the code. Note that the old columns already have DEFAULT NULL
  • Add the migration logic to the code, so that it can read/write either schema. By default, it will read and write the old schema.
  • Create the new schema in beta (T336362)
  • Configure beta to read/write the new schema (T336362)
  • Create the new schema in production (T336365)
  • Configure prod to read/write the new schema (T336364)
  • Drop the old columns in the code
  • Remove the migration logic
  • Remove the migration variable from the beta config (T336362)
  • Drop the old columns in beta (T336362)
  • Remove the migration variable from the prod config (T336364)
  • Drop the old columns in prod (T337940)

Event Timeline

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

[mediawiki/extensions/CampaignEvents@master] Add a new ce_tracking_tools table

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

Change 906573 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] Add new ce_tracking_tools table

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

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

[mediawiki/extensions/CampaignEvents@master] Drop old schema and migration logic for tracking tools

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

Change 923302 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] Drop old schema and migration logic for tracking tools

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