This ticket is about adding additional tables to the Donation bounded context that store the tracking data.

Due to a mixup in the backlog, parts of this ticket have already been done T336846: Migrate donation tracking data to new table.

Acceptance criteria

• All new donations store tracking data in a way that can be queried from SQL, targeting campaigns and keywords separately. This is in addition to the tracking string in the data blob (for backwards compatibility).

Deployment

The structural database migrations (with Doctrine Migrations) have already been done in the deployment of T390996: Integrate Donation tracking changes in Fundraising Application. We tested the data migration script (vendor/bin/migrate-donation-tracking-data) in the testing environment, but need to run it as part of the production deployment:

• create the file migrations-db.php file in the project root directory (current-release), with PDO connection data returned as an array. You can convert the db section from the JSON configuration file into PHP syntax.
• run data migration script: php8.4 -d display_errors=1 -d memory_limit=2G vendor/wmde/fundraising-donations/bin/migrate-donation-tracking-data.

Implementation details

Parts we already implemented in T336846: Migrate donation tracking data to new table are crossed out

• Store the TrackingInfo domain class as an entity in the donation bounded context. Backing table will probably have 3 columns: id, campaign,keyword (with a 2-column unique index on campaign and keyword). Use donation_tracking as the table name, because we will have tracking for memberships and subscriptions as well.
• Store the tracking ID in donation (using the legacy converter classes)
• Add a 1:n column relationship between Donation Doctrine entity and the Tracking entity
• Create a repository for getting a tracking info entity by keyword and campaign, create it if the entity does not exist.
• Make sure the repository does efficient querying by name, using a compound index of keyword,campaign
• Store the "Banner Impression Count" and "Overall Impression Count" as columns in the Doctrine Donation entity
• Change the DonationTrackingInfo class to store keyword and campaign as separate strings, not a slash-concatenated string.
• Use the repository in the legacy converters:
  • When reading the data must come from the tracking entity. If the tracking entity is null, create DonationTrackingInfo with empty strings for campaign and keywords
  • When writing donations, the tracking entity must be assigned to the Doctrine donation entity (and additionally written in the data blob). If campaign and keywords are empty strings, set the DonationTracking entity to null.
• We want the removal of the tracking data in the legacy "data blob" to be easy. For this purpose, refactor the storage of the impression data into an interface with 3 implementations:
  • The one that creates a TrackingInfo entity
  • The one that writes it to the data blob
  • A Facade one that calls the other ones.
• Make sure that the tracking is loaded "eagerly" when loading a Doctrine Donation entity inside the DoctrineDonationRepository (i.e. accessing $donation->getTracking() does not trigger a second query to the database). You might need to change the code in DoctrineDonationRepsoitory::getDoctrineDonationById to use a DQL query instead of find. If you have to use a query, make sure to test a case where a Doctrine donation entity has no tracking (tracking is null).