{"type":"doc","content":[{"type":"heading","attrs":{"level":2},"content":[{"text":"About","type":"text"}]},{"type":"paragraph","content":[{"text":"This explains the process for identifying and deleting placement or programme memberships which still exist in TIS Self-Service after having been deleted from TIS. It is unoptimised, requires several manual steps and includes workarounds that may have better alternatives.","type":"text"}]},{"type":"paragraph","content":[{"text":"But, it does the job in a pinch.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Steps","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. Get a list of IDs from TIS","type":"text"}]},{"type":"paragraph","content":[{"text":"A simple SQL query can be used, with the results exported to a CSV file, this must be done against the DB directly as Metabase limits the export to 1,048,576 rows.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Placement","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT traineeId, id AS tisId\nFROM tcs.Placement\nORDER BY traineeId ASC, placementId ASC","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Programme Membership","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT personId AS traineeId, uuid AS tisId\nFROM tcs.ProgrammeMembership\nORDER BY traineeId ASC, programmeMembershipId ASC","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. Get a list of IDs from TSS","type":"text"}]},{"type":"paragraph","content":[{"text":"This is a little trickier to get, a Mongo shell script could be used to simplify it but this approach uses Metabase instead. Use the following query and export the result to CSV named ","type":"text"},{"text":"metabase_tss_ids.csv","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Placement","type":"text"}]},{"type":"codeBlock","attrs":{"language":"json"},"content":[{"text":"[{\"$project\":{\"_id\": 0, \"traineeId\":\"$traineeTisId\",\"tisId\":\"$placements.tisId\"}}]","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Programme Membership","type":"text"}]},{"type":"codeBlock","attrs":{"language":"json"},"content":[{"text":"[{\"$project\":{\"_id\": 0, \"traineeId\":\"$traineeTisId\",\"tisId\":\"$programmeMemberships.tisId\"}}]","type":"text"}]},{"type":"paragraph","content":[{"text":"You’ll get a CSV with the IDs in an array. ","type":"text"}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"traineeId,tisId\n315594,\"[\"\"ae5350af-2a3c-4439-b118-f57ea1d8bc94\"\"]\"\n140114,\"[\"\"e92a313c-d83d-11ec-9eb2-0638a616fc76\"\" \"\"e92a3259-d83d-11ec-9eb2-0638a616fc76\"\"]\"","type":"text"}]},{"type":"paragraph","content":[{"text":"The following script will expand the array in to individual rows, change the input filename as needed and send the output to a new CSV file ","type":"text"},{"text":"./my_script_file.sh > expanded_tss_ids.csv","type":"text","marks":[{"type":"code"}]}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"#! /bin/bash\necho \"traineeId,tisId\"\nwhile IFS=\",\" read -r traineeId tisIds\ndo\n\n tisIds=${tisIds//[\\[\\]]/''}\n tisIds=${tisIds//[\\\"]/''}\n tisIds=${tisIds//[ ]/','}\n\n IFS=\",\" read -ra array <<< \"$tisIds\"\n\n for tisId in \"${array[@]}\"\n do\n echo \"$traineeId,$tisId\"\n done\ndone < <(tail -n +2 metabase_tss_ids.csv)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"3. Import to SQL database","type":"text"}]},{"type":"paragraph","content":[{"text":"Spin up a local MySQL instance (or use the TSS local DB from the handbook) and create two tables, one for the IDs from TIS and the other for the IDs from TSS.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Placement","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"CREATE TABLE `tcs_placement_ids` (\n `traineeId` int NOT NULL,\n `tisId` int NOT NULL\n);\n\nCREATE TABLE `tss_placement_ids` (\n `traineeId` int NOT NULL,\n `tisId` int NOT NULL\n);","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Programme Membership","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"CREATE TABLE `tcs_pm_ids` (\n `traineeId` int NOT NULL,\n `programmeMembershipId` varchar(36) NOT NULL\n);\n\nCREATE TABLE `tss_pm_ids` (\n `traineeId` int NOT NULL,\n `programmeMembershipId` varchar(36) NOT NULL\n);","type":"text"}]},{"type":"paragraph","content":[{"text":"Then import the previously exported CSVs in to the appropriate tables.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"4. Query for Orphans","type":"text"}]},{"type":"paragraph","content":[{"text":"Run the query to identify orphaned placements/PM","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Placement","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT traineeId, tisId FROM (\n SELECT tss.traineeId, tss.tisId\n FROM tss_placement_ids tss\n WHERE tss.traineeId > 0 AND tss.tisId NOT IN (SELECT tisId FROM tcs_placement_ids tcs)\n GROUP BY tss.traineeId, tss.tisId\n)\nAS distinct_results ORDER BY traineeId ASC, tisId ASC;","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Programme Membership","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT traineeId, tisId FROM (\n SELECT tss.traineeId, tss.tisId\n FROM tss_pm_ids tss\n WHERE tss.traineeId > 0 AND tss.tisId NOT IN (SELECT tisId FROM tcs_pm_ids tcs)\n GROUP BY tss.traineeId, tss.tisId\n)\nAS distinct_results ORDER BY traineeId ASC, tisId ASC;","type":"text"}]},{"type":"paragraph","content":[{"text":"Export the result to a new CSV named ","type":"text"},{"text":"orphaned_ids.csv","type":"text","marks":[{"type":"code"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"5. (Risky Approach) Publish Messages","type":"text"}]},{"type":"paragraph","content":[{"text":"At the time of writing the below scripts can be used to publish suitable message for Placement and PM deletions, check that the structure is still valid before use and the queue name is correct.","type":"text"}]},{"type":"paragraph","content":[{"text":"An optional, but recommended, buffer queue can be used if publishing directly to the live queue is undesirable","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Placement","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"#! /bin/bash\n\nwhile IFS=\",\" read -r traineeId tisId\ndo\n trimmedTisId=$(echo $tisId| xargs)\n trimmedTraineeId=$(echo $traineeId | xargs)\n aws sqs send-message --region eu-west-2 --queue-url https://sqs.eu-west-2.amazonaws.com/430723991443/tis-trainee-sync-prod-record.fifo --message-group-id corrective_delete --message-body '{\"data\":{\"id\":\"'\"$trimmedTisId\"'\",\"traineeId\":\"'\"$trimmedTraineeId\"'\"},\"metadata\":{\"record-type\":\"data\",\"partition-key-type\":\"schema-table\",\"schema-name\":\"tcs\",\"table-name\":\"Placement\",\"operation\":\"delete\"}}'\ndone < <(tail -n +2 orphaned_ids.csv)","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Programme Membership","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"#! /bin/bash\n\nwhile IFS=\",\" read -r traineeId tisId\ndo\n trimmedTisId=$(echo $tisId | xargs)\n trimmedTraineeId=$(echo $traineeId | xargs)\n aws sqs send-message --region eu-west-2 --queue-url https://sqs.eu-west-2.amazonaws.com/430723991443/tis-trainee-sync-prod-record.fifo --message-group-id corrective_delete --message-body '{\"data\":{\"uuid\":\"'\"$trimmedTisId\"'\",\"personId\":\"'\"$trimmedTraineeId\"'\"},\"metadata\":{\"record-type\":\"data\",\"partition-key-type\":\"schema-table\",\"schema-name\":\"tcs\",\"table-name\":\"ProgrammeMembership\",\"operation\":\"delete\"}}'\ndone < <(tail -n +2 orphaned_ids.csv)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"5. (Safer Approach) Publish Messages Using “Buffer” Queue","type":"text"}]},{"type":"paragraph","content":[{"text":"A temporary queue can be created as a target for the publish, this allows all messages to be collected and verified before they are sent to the live system. Publishing from a local machine is a slow process, so it’s recommended to use the buffer queue to avoid any issues if the process is interrupted and has to be restarted.","type":"text"}]},{"type":"paragraph","content":[{"text":"Create a queue in SQS (since this is temporary creating it in the console is fine, just clean up when you’ve done!) and use the same script with your temp queue name instead.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Placement","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"#! /bin/bash\n\nwhile IFS=\",\" read -r traineeId tisId\ndo\n trimmedTisId=$(echo $tisId| xargs)\n trimmedTraineeId=$(echo $traineeId | xargs)\n aws sqs send-message --region eu-west-2 --queue-url https://sqs.eu-west-2.amazonaws.com/430723991443/temp_my-buffer-queue.fifo --message-group-id corrective_delete --message-body '{\"data\":{\"id\":\"'\"$trimmedTisId\"'\",\"traineeId\":\"'\"$trimmedTraineeId\"'\"},\"metadata\":{\"record-type\":\"data\",\"partition-key-type\":\"schema-table\",\"schema-name\":\"tcs\",\"table-name\":\"Placement\",\"operation\":\"delete\"}}'\ndone < <(tail -n +2 orphaned_ids.csv)","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Programme Membership","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"#! /bin/bash\n\nwhile IFS=\",\" read -r traineeId tisId\ndo\n trimmedTisId=$(echo $tisId | xargs)\n trimmedTraineeId=$(echo $traineeId | xargs)\n aws sqs send-message --region eu-west-2 --queue-url https://sqs.eu-west-2.amazonaws.com/430723991443/temp_my-buffer-queue.fifo --message-group-id corrective_delete --message-body '{\"data\":{\"uuid\":\"'\"$trimmedTisId\"'\",\"personId\":\"'\"$trimmedTraineeId\"'\"},\"metadata\":{\"record-type\":\"data\",\"partition-key-type\":\"schema-table\",\"schema-name\":\"tcs\",\"table-name\":\"ProgrammeMembership\",\"operation\":\"delete\"}}'\ndone < <(tail -n +2 orphaned_ids.csv)","type":"text"}]},{"type":"paragraph","content":[{"text":"Once messages have been published you can use the console to receive messages to verify them.","type":"text"}]},{"type":"paragraph","content":[{"text":"To send them to the live queue we’ll use some DLQ trickery, setting the “target” live queue as the DLQ for our temporary queue. Then using receives to trigger the messages to be sent to the “DLQ”.","type":"text"}]},{"type":"paragraph","content":[{"text":"When you are ready to send the messages to the real queue, set the following in the queue’s config","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Visibility timeout: 1 second","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"DLQ:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Max receives: 1","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Queue ARN: your target queue","type":"text"}]}]}]}]}]},{"type":"paragraph","content":[{"text":"In the “Send and receive messages” section of the queue in the AWS console set the polling settings","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Poll duration: 90 seconds","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Maximum message count: 999","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"This combinations of settings will ensure that we receives as many messages as quickly as possible, after each receive the message will instantly be sent to the “DLQ”.","type":"text"}]},{"type":"paragraph","content":[{"text":"Now just click “Poll for messages” and watch as 999 messages are sent to your target queue, repeat as needed until the buffer queue is empty.","type":"text"}]}],"version":1}

Browser not supported