| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: InsertWorkflowCache :one |
| 4 | -- Called by the future runner-side upload handler when an |
| 5 | -- actions/cache step completes its tarball upload. Idempotent on |
| 6 | -- (repo_id, cache_key, cache_version, git_ref): a re-upload with the |
| 7 | -- same coordinates updates size + last_accessed_at + object_key. |
| 8 | INSERT INTO workflow_caches ( |
| 9 | repo_id, cache_key, cache_version, git_ref, object_key, size_bytes |
| 10 | ) VALUES ($1, $2, $3, $4, $5, $6) |
| 11 | ON CONFLICT (repo_id, cache_key, cache_version, git_ref) DO UPDATE |
| 12 | SET object_key = EXCLUDED.object_key, |
| 13 | size_bytes = EXCLUDED.size_bytes, |
| 14 | last_accessed_at = now() |
| 15 | RETURNING id, repo_id, cache_key, cache_version, git_ref, object_key, |
| 16 | size_bytes, last_accessed_at, created_at; |
| 17 | |
| 18 | -- name: ListWorkflowCachesForRepo :many |
| 19 | -- Paginated list filtered optionally by ref + key. NULL params skip |
| 20 | -- the filter. Sorted by last_accessed_at DESC so an operator sees the |
| 21 | -- live caches first. |
| 22 | SELECT id, repo_id, cache_key, cache_version, git_ref, object_key, |
| 23 | size_bytes, last_accessed_at, created_at |
| 24 | FROM workflow_caches |
| 25 | WHERE repo_id = $1 |
| 26 | AND (sqlc.narg(git_ref)::text IS NULL OR git_ref = sqlc.narg(git_ref)::text) |
| 27 | AND (sqlc.narg(cache_key)::text IS NULL OR cache_key = sqlc.narg(cache_key)::text) |
| 28 | ORDER BY last_accessed_at DESC, id DESC |
| 29 | LIMIT $2 OFFSET $3; |
| 30 | |
| 31 | -- name: CountWorkflowCachesForRepo :one |
| 32 | SELECT COUNT(*) FROM workflow_caches |
| 33 | WHERE repo_id = $1 |
| 34 | AND (sqlc.narg(git_ref)::text IS NULL OR git_ref = sqlc.narg(git_ref)::text) |
| 35 | AND (sqlc.narg(cache_key)::text IS NULL OR cache_key = sqlc.narg(cache_key)::text); |
| 36 | |
| 37 | -- name: GetWorkflowCacheByID :one |
| 38 | SELECT id, repo_id, cache_key, cache_version, git_ref, object_key, |
| 39 | size_bytes, last_accessed_at, created_at |
| 40 | FROM workflow_caches |
| 41 | WHERE id = $1; |
| 42 | |
| 43 | -- name: DeleteWorkflowCacheByID :execrows |
| 44 | DELETE FROM workflow_caches WHERE id = $1 AND repo_id = $2; |
| 45 | |
| 46 | -- name: DeleteWorkflowCachesByKey :many |
| 47 | -- Returns the deleted rows' object_keys so the handler can purge the |
| 48 | -- backing tarballs from object storage. |
| 49 | WITH deleted AS ( |
| 50 | DELETE FROM workflow_caches |
| 51 | WHERE repo_id = $1 |
| 52 | AND cache_key = $2 |
| 53 | AND (sqlc.narg(git_ref)::text IS NULL OR git_ref = sqlc.narg(git_ref)::text) |
| 54 | RETURNING object_key |
| 55 | ) |
| 56 | SELECT object_key FROM deleted; |
| 57 | |
| 58 | -- name: TouchWorkflowCache :exec |
| 59 | -- Bumps last_accessed_at on cache hit. Called by the future |
| 60 | -- restore-side handler. |
| 61 | UPDATE workflow_caches |
| 62 | SET last_accessed_at = now() |
| 63 | WHERE id = $1; |
| 64 |