onaio / onadata

Collect, Analyze and Share
https://ona.io
Other
183 stars 133 forks source link

Optimize attachment xform migration #2599

Closed kelvin-muchiri closed 5 months ago

kelvin-muchiri commented 6 months ago

Changes / Features implemented

Run migration as SQL query to optimize performance when number of records to be updated is huge. The migration SQL is

WITH logger_attachment_instance AS (
  SELECT 
    logger_attachment.id, 
    logger_instance.xform_id, 
    logger_instance.user_id 
  FROM 
    logger_attachment 
    INNER JOIN logger_instance ON logger_attachment.instance_id = logger_instance.id 
    INNER JOIN logger_xform T4 ON logger_instance.xform_id = T4.id 
  WHERE 
    logger_attachment.xform_id IS NULL 
    AND T4.deleted_at IS NULL
) 
UPDATE 
  logger_attachment 
SET 
  xform_id = logger_attachment_instance.xform_id, 
  user_id = logger_attachment_instance.user_id 
FROM 
  logger_attachment_instance 
WHERE 
  logger_attachment.id = logger_attachment_instance.id;

The reverse migration SQL query is

WITH logger_attachment_xform AS (
  SELECT 
    logger_attachment.id 
  FROM 
    logger_attachment 
    INNER JOIN logger_xform T4 ON logger_attachment.xform_id = T4.id 
  WHERE 
    logger_attachment.xform_id IS NOT NULL 
    AND T4.deleted_at IS NULL
) 
UPDATE 
  logger_attachment 
SET 
  xform_id = NULL, 
  user_id = NULL 
FROM 
  logger_attachment_xform 
WHERE 
  logger_attachment.id = logger_attachment_xform.id;

Steps taken to verify this change does what is intended

Side effects of implementing this change

Before submitting this PR for review, please make sure you have:

Closes #