Files
mtusi2026lab6/db/queries/versioned_pipeline.sql

31 lines
1.3 KiB
SQL

-- name: SupersedeAttachmentJob :exec
WITH superseded AS (
UPDATE attachment_jobs
SET lease_until = NULL, next_attempt_at = NULL, updated_at = clock_timestamp()
WHERE id = sqlc.arg(job_id)::uuid
RETURNING id, updated_at
)
UPDATE attachment_attempts AS attempt
SET outcome = 'superseded', error_code = 'superseded', finished_at = superseded.updated_at
FROM superseded
WHERE attempt.job_id = superseded.id AND attempt.finished_at IS NULL;
-- name: QueueAttachmentUpgrade :one
UPDATE attachments
SET pipeline_version = 2, status = 'queued', staging_key = sqlc.arg(source_key)::text,
object_key = NULL, error_code = NULL, updated_at = clock_timestamp()
WHERE id = sqlc.arg(id)::uuid AND pipeline_version = 1
RETURNING *;
-- name: GetPipelineJobID :one
SELECT id FROM attachment_jobs
WHERE attachment_id = sqlc.arg(attachment_id)::uuid AND pipeline_version = sqlc.arg(pipeline_version)::int;
-- name: GetPublishedOriginalKey :one
SELECT published_original_key FROM attachment_jobs
WHERE attachment_id = sqlc.arg(attachment_id)::uuid AND pipeline_version = sqlc.arg(pipeline_version)::int
AND published_original_key IS NOT NULL;
-- name: IsPublishedOriginalKey :one
SELECT EXISTS (SELECT 1 FROM attachment_jobs WHERE published_original_key = sqlc.arg(object_key)::text);