47 lines
1.3 KiB
PL/PgSQL
47 lines
1.3 KiB
PL/PgSQL
BEGIN;
|
|
|
|
DROP TABLE IF EXISTS _processed_ticket_dedup_map;
|
|
|
|
CREATE TEMP TABLE _processed_ticket_dedup_map AS
|
|
SELECT dup.id AS duplicate_id,
|
|
(
|
|
SELECT keep.id
|
|
FROM processed_tickets keep
|
|
WHERE keep.tracker_id = dup.tracker_id
|
|
AND keep.artifact_id = dup.artifact_id
|
|
ORDER BY keep.rowid ASC
|
|
LIMIT 1
|
|
) AS keep_id
|
|
FROM processed_tickets dup
|
|
WHERE dup.rowid > (
|
|
SELECT MIN(base.rowid)
|
|
FROM processed_tickets base
|
|
WHERE base.tracker_id = dup.tracker_id
|
|
AND base.artifact_id = dup.artifact_id
|
|
);
|
|
|
|
UPDATE worktrees
|
|
SET ticket_id = (
|
|
SELECT keep_id
|
|
FROM _processed_ticket_dedup_map map
|
|
WHERE map.duplicate_id = worktrees.ticket_id
|
|
)
|
|
WHERE ticket_id IN (SELECT duplicate_id FROM _processed_ticket_dedup_map);
|
|
|
|
UPDATE notifications
|
|
SET ticket_id = (
|
|
SELECT keep_id
|
|
FROM _processed_ticket_dedup_map map
|
|
WHERE map.duplicate_id = notifications.ticket_id
|
|
)
|
|
WHERE ticket_id IN (SELECT duplicate_id FROM _processed_ticket_dedup_map);
|
|
|
|
DELETE FROM processed_tickets
|
|
WHERE id IN (SELECT duplicate_id FROM _processed_ticket_dedup_map);
|
|
|
|
DROP TABLE IF EXISTS _processed_ticket_dedup_map;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_processed_tickets_tracker_artifact_unique
|
|
ON processed_tickets(tracker_id, artifact_id);
|
|
|
|
COMMIT;
|