Skip to content

Commit 1c84433

Browse files
mafredrimafredri
committed
fix order
1 parent 106e8ca commit 1c84433

File tree

1 file changed

+44
-43
lines changed

1 file changed

+44
-43
lines changed

‎coderd/database/migrations/000338_use_deleted_boolean_for_subagents.down.sql

Lines changed: 44 additions & 43 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,45 @@
1-
ALTER TABLE workspace_agents
2-
DROP COLUMN deleted;
1+
-- Restore prebuilds, previously modified in 000323_workspace_latest_builds_optimization.up.sql.
2+
DROP VIEW workspace_prebuilds;
3+
4+
CREATE VIEW workspace_prebuilds AS
5+
WITH all_prebuilds AS (
6+
SELECT w.id,
7+
w.name,
8+
w.template_id,
9+
w.created_at
10+
FROM workspaces w
11+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
12+
), workspaces_with_latest_presets AS (
13+
SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
14+
workspace_builds.template_version_preset_id
15+
FROM workspace_builds
16+
WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
17+
ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
18+
), workspaces_with_agents_status AS (
19+
SELECT w.id AS workspace_id,
20+
bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
21+
FROM (((workspaces w
22+
JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
23+
JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
24+
JOIN workspace_agents wa ON ((wa.resource_id = wr.id)))
25+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
26+
GROUP BY w.id
27+
), current_presets AS (
28+
SELECT w.id AS prebuild_id,
29+
wlp.template_version_preset_id
30+
FROM (workspaces w
31+
JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
32+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
33+
)
34+
SELECT p.id,
35+
p.name,
36+
p.template_id,
37+
p.created_at,
38+
COALESCE(a.ready, false) AS ready,
39+
cp.template_version_preset_id AS current_preset_id
40+
FROM ((all_prebuilds p
41+
LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
42+
JOIN current_presets cp ON ((cp.prebuild_id = p.id)));
343

444
-- Restore trigger without deleted check.
545
DROP TRIGGER IF EXISTS workspace_agent_name_unique_trigger ON workspace_agents;
@@ -51,45 +91,6 @@ COMMENT ON TRIGGER workspace_agent_name_unique_trigger ON workspace_agents IS
5191
the uniqueness requirement. A trigger allows us to enforce uniqueness going
5292
forward without requiring a migration to clean up historical data.';
5393

54-
-- Restore prebuilds, previously modified in 000323_workspace_latest_builds_optimization.up.sql.
55-
DROP VIEW workspace_prebuilds;
5694

57-
CREATE VIEW workspace_prebuilds AS
58-
WITH all_prebuilds AS (
59-
SELECT w.id,
60-
w.name,
61-
w.template_id,
62-
w.created_at
63-
FROM workspaces w
64-
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
65-
), workspaces_with_latest_presets AS (
66-
SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
67-
workspace_builds.template_version_preset_id
68-
FROM workspace_builds
69-
WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
70-
ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
71-
), workspaces_with_agents_status AS (
72-
SELECT w.id AS workspace_id,
73-
bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
74-
FROM (((workspaces w
75-
JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
76-
JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
77-
JOIN workspace_agents wa ON ((wa.resource_id = wr.id)))
78-
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
79-
GROUP BY w.id
80-
), current_presets AS (
81-
SELECT w.id AS prebuild_id,
82-
wlp.template_version_preset_id
83-
FROM (workspaces w
84-
JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
85-
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
86-
)
87-
SELECT p.id,
88-
p.name,
89-
p.template_id,
90-
p.created_at,
91-
COALESCE(a.ready, false) AS ready,
92-
cp.template_version_preset_id AS current_preset_id
93-
FROM ((all_prebuilds p
94-
LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
95-
JOIN current_presets cp ON ((cp.prebuild_id = p.id)));
95+
ALTER TABLE workspace_agents
96+
DROP COLUMN deleted;

0 commit comments

Comments
 (0)