Files

Ignoring revisions in .git-blame-ignore-revs. Click here to bypass and see the normal blame view.

263 lines
6.9 KiB
SQL
Raw Permalink Normal View History

2022-04-06 12:42:40 -05:00
-- name: GetTemplateVersionsByTemplateID :many
SELECT
*
FROM
template_version_with_user AS template_versions
2022-04-06 12:42:40 -05:00
WHERE
template_id = @template_id :: uuid
AND CASE
-- If no filter is provided, default to returning ALL template versions.
-- The called should always provide a filter if they want to omit
-- archived versions.
WHEN sqlc.narg('archived') :: boolean IS NULL THEN true
ELSE template_versions.archived = sqlc.narg('archived') :: boolean
END
AND CASE
-- This allows using the last element on a page as effectively a cursor.
-- This is an important option for scripts that need to paginate without
-- duplicating or missing data.
2022-11-06 16:49:43 -06:00
WHEN @after_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN (
-- The pagination cursor is the last ID of the previous page.
-- The query is ordered by the created_at field, so select all
-- rows after the cursor.
(created_at, id) > (
SELECT
created_at, id
FROM
template_versions
WHERE
id = @after_id
)
)
ELSE true
END
ORDER BY
-- Deterministic and consistent ordering of all rows, even if they share
-- a timestamp. This is to ensure consistent pagination.
(created_at, id) ASC OFFSET @offset_opt
LIMIT
-- A null limit means "no limit", so 0 means return all
NULLIF(@limit_opt :: int, 0);
2022-04-06 12:42:40 -05:00
-- name: GetTemplateVersionByJobID :one
SELECT
*
FROM
template_version_with_user AS template_versions
2022-04-06 12:42:40 -05:00
WHERE
job_id = $1;
-- name: GetTemplateVersionsCreatedAfter :many
SELECT * FROM template_version_with_user AS template_versions WHERE created_at > $1;
2022-04-06 12:42:40 -05:00
-- name: GetTemplateVersionByTemplateIDAndName :one
SELECT
*
FROM
template_version_with_user AS template_versions
2022-04-06 12:42:40 -05:00
WHERE
template_id = $1
AND "name" = $2;
2022-11-15 13:24:13 -03:00
2022-04-06 12:42:40 -05:00
-- name: GetTemplateVersionByID :one
SELECT
*
FROM
template_version_with_user AS template_versions
2022-04-06 12:42:40 -05:00
WHERE
id = $1;
-- name: GetTemplateVersionsByIDs :many
SELECT
*
FROM
template_version_with_user AS template_versions
WHERE
id = ANY(@ids :: uuid [ ]);
-- name: InsertTemplateVersion :exec
2022-04-06 12:42:40 -05:00
INSERT INTO
template_versions (
id,
template_id,
organization_id,
created_at,
updated_at,
"name",
message,
readme,
2022-07-15 14:12:39 -07:00
job_id,
created_by,
source_example_id
2022-04-06 12:42:40 -05:00
)
VALUES
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);
2022-04-06 12:42:40 -05:00
-- name: UpdateTemplateVersionByID :exec
2022-04-06 12:42:40 -05:00
UPDATE
template_versions
SET
template_id = $2,
updated_at = $3,
2023-07-13 10:36:10 -03:00
name = $4,
message = $5
2022-04-06 12:42:40 -05:00
WHERE
id = $1;
-- name: UpdateTemplateVersionDescriptionByJobID :exec
UPDATE
template_versions
SET
readme = $2,
updated_at = $3
WHERE
job_id = $1;
-- name: UpdateTemplateVersionExternalAuthProvidersByJobID :exec
UPDATE
template_versions
SET
external_auth_providers = $2,
updated_at = $3
WHERE
job_id = $1;
-- name: GetPreviousTemplateVersion :one
SELECT
*
FROM
template_version_with_user AS template_versions
WHERE
created_at < (
SELECT created_at
FROM template_version_with_user AS tv
WHERE tv.organization_id = $1 AND tv.name = $2 AND tv.template_id = $3
)
AND organization_id = $1
AND template_id = $3
ORDER BY created_at DESC
LIMIT 1;
-- name: UnarchiveTemplateVersion :exec
-- This will always work regardless of the current state of the template version.
UPDATE
template_versions
SET
archived = false,
updated_at = sqlc.arg('updated_at')
WHERE
id = sqlc.arg('template_version_id');
-- name: ArchiveUnusedTemplateVersions :many
-- Archiving templates is a soft delete action, so is reversible.
-- Archiving prevents the version from being used and discovered
-- by listing.
-- Only unused template versions will be archived, which are any versions not
-- referenced by the latest build of a workspace.
UPDATE
template_versions
SET
archived = true,
updated_at = sqlc.arg('updated_at')
FROM
-- Archive all versions that are returned from this query.
(
SELECT
scoped_template_versions.id
FROM
-- Scope an archive to a single template and ignore already archived template versions
(
SELECT
*
FROM
template_versions
WHERE
template_versions.template_id = sqlc.arg('template_id') :: uuid
AND
archived = false
AND
-- This allows archiving a specific template version.
CASE
WHEN sqlc.arg('template_version_id')::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
template_versions.id = sqlc.arg('template_version_id') :: uuid
ELSE
true
END
) AS scoped_template_versions
LEFT JOIN
provisioner_jobs ON scoped_template_versions.job_id = provisioner_jobs.id
LEFT JOIN
templates ON scoped_template_versions.template_id = templates.id
WHERE
-- Actively used template versions (meaning the latest build is using
-- the version) are never archived. A "restart" command on the workspace,
-- even if failed, would use the version. So it cannot be archived until
-- the build is outdated.
NOT EXISTS (
-- Return all "used" versions, where "used" is defined as being
-- used by a latest workspace build.
SELECT template_version_id FROM (
SELECT
DISTINCT ON (workspace_id) template_version_id, transition
FROM
workspace_builds
ORDER BY workspace_id, build_number DESC
) AS used_versions
WHERE
used_versions.transition != 'delete'
AND
scoped_template_versions.id = used_versions.template_version_id
)
-- Also never archive the active template version
AND active_version_id != scoped_template_versions.id
AND CASE
-- Optionally, only archive versions that match a given
-- job status like 'failed'.
WHEN sqlc.narg('job_status') :: provisioner_job_status IS NOT NULL THEN
provisioner_jobs.job_status = sqlc.narg('job_status') :: provisioner_job_status
ELSE
true
END
-- Pending or running jobs should not be archived, as they are "in progress"
AND provisioner_jobs.job_status != 'running'
AND provisioner_jobs.job_status != 'pending'
) AS archived_versions
WHERE
template_versions.id IN (archived_versions.id)
RETURNING template_versions.id;
-- name: UpdateTemplateVersionFlagsByJobID :exec
UPDATE
template_versions
SET
has_external_agent = $2,
updated_at = $3
WHERE
job_id = $1;
-- name: UpdateTemplateVersionScriptOrderUsageByJobID :exec
UPDATE
template_versions
SET
script_order_data_source_count = $2,
script_order_rule_count = $3,
updated_at = $4
WHERE
job_id = $1;
-- name: GetScriptOrderTelemetrySummary :one
-- Count only the active, successfully imported version of each non-deleted
-- template, so repeated imports do not inflate adoption.
SELECT
COUNT(*) AS templates_with_script_order,
COALESCE(SUM(tv.script_order_data_source_count), 0)::bigint AS data_source_count,
COALESCE(SUM(tv.script_order_rule_count), 0)::bigint AS rule_count
FROM templates t
JOIN template_versions tv ON tv.id = t.active_version_id
JOIN provisioner_jobs pj ON pj.id = tv.job_id
WHERE t.deleted = false
AND tv.script_order_data_source_count > 0
AND pj.type = 'template_version_import'
AND pj.completed_at IS NOT NULL
AND COALESCE(pj.error, '') = ''
AND pj.canceled_at IS NULL;