Maxfield Luke
af6077b9c3
Fresh repository history for elektrine/tarakan hosted at https://git.elektrine.com/elektrine/tarakan.
47 lines
1.7 KiB
Elixir
47 lines
1.7 KiB
Elixir
defmodule Tarakan.Repo.Migrations.ConsolidateCanonicalFindingVotes do
|
|
use Ecto.Migration
|
|
|
|
def up do
|
|
execute """
|
|
INSERT INTO votes (
|
|
account_id, subject_type, subject_id, value, inserted_at, updated_at
|
|
)
|
|
SELECT DISTINCT ON (vote.account_id, finding.canonical_finding_id)
|
|
vote.account_id, 'canonical_finding', finding.canonical_finding_id,
|
|
vote.value, vote.inserted_at, vote.updated_at
|
|
FROM votes vote
|
|
JOIN scan_findings finding ON finding.id = vote.subject_id
|
|
WHERE vote.subject_type = 'finding' AND finding.canonical_finding_id IS NOT NULL
|
|
ORDER BY vote.account_id, finding.canonical_finding_id,
|
|
vote.updated_at DESC, vote.id DESC
|
|
ON CONFLICT (account_id, subject_type, subject_id)
|
|
DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at
|
|
"""
|
|
|
|
execute "DELETE FROM votes WHERE subject_type = 'finding'"
|
|
end
|
|
|
|
def down do
|
|
execute """
|
|
INSERT INTO votes (
|
|
account_id, subject_type, subject_id, value, inserted_at, updated_at
|
|
)
|
|
SELECT vote.account_id, 'finding', occurrence.id, vote.value,
|
|
vote.inserted_at, vote.updated_at
|
|
FROM votes vote
|
|
JOIN LATERAL (
|
|
SELECT finding.id
|
|
FROM scan_findings finding
|
|
JOIN scans scan ON scan.id = finding.scan_id
|
|
WHERE finding.canonical_finding_id = vote.subject_id
|
|
ORDER BY scan.inserted_at ASC, finding.id ASC
|
|
LIMIT 1
|
|
) occurrence ON true
|
|
WHERE vote.subject_type = 'canonical_finding'
|
|
ON CONFLICT (account_id, subject_type, subject_id)
|
|
DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at
|
|
"""
|
|
|
|
execute "DELETE FROM votes WHERE subject_type = 'canonical_finding'"
|
|
end
|
|
end
|