|
1 | | -ALTERTABLE workspace_agents |
2 | | - DROP COLUMN deleted; |
| 1 | +-- Restore prebuilds, previously modified in 000323_workspace_latest_builds_optimization.up.sql. |
| 2 | +DROPVIEW workspace_prebuilds; |
| 3 | + |
| 4 | +CREATEVIEWworkspace_prebuildsAS |
| 5 | + WITH all_prebuildsAS ( |
| 6 | +SELECTw.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_presetsAS ( |
| 13 | +SELECT DISTINCTON (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_idIS NOT NULL) |
| 17 | +ORDER BYworkspace_builds.workspace_id,workspace_builds.build_numberDESC |
| 18 | + ), workspaces_with_agents_statusAS ( |
| 19 | +SELECTw.idAS workspace_id, |
| 20 | + bool_and((wa.lifecycle_state='ready'::workspace_agent_lifecycle_state))AS ready |
| 21 | +FROM (((workspaces w |
| 22 | +JOIN workspace_latest_builds wlbON ((wlb.workspace_id=w.id))) |
| 23 | +JOIN workspace_resources wrON ((wr.job_id=wlb.job_id))) |
| 24 | +JOIN workspace_agents waON ((wa.resource_id=wr.id))) |
| 25 | +WHERE (w.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid) |
| 26 | +GROUP BYw.id |
| 27 | + ), current_presetsAS ( |
| 28 | +SELECTw.idAS prebuild_id, |
| 29 | +wlp.template_version_preset_id |
| 30 | +FROM (workspaces w |
| 31 | +JOIN workspaces_with_latest_presets wlpON ((wlp.workspace_id=w.id))) |
| 32 | +WHERE (w.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid) |
| 33 | + ) |
| 34 | +SELECTp.id, |
| 35 | +p.name, |
| 36 | +p.template_id, |
| 37 | +p.created_at, |
| 38 | + COALESCE(a.ready, false)AS ready, |
| 39 | +cp.template_version_preset_idAS current_preset_id |
| 40 | +FROM ((all_prebuilds p |
| 41 | +LEFT JOIN workspaces_with_agents_status aON ((a.workspace_id=p.id))) |
| 42 | +JOIN current_presets cpON ((cp.prebuild_id=p.id))); |
3 | 43 |
|
4 | 44 | -- Restore trigger without deleted check. |
5 | 45 | DROPTRIGGER IF EXISTS workspace_agent_name_unique_triggerON workspace_agents; |
@@ -51,45 +91,6 @@ COMMENT ON TRIGGER workspace_agent_name_unique_trigger ON workspace_agents IS |
51 | 91 | the uniqueness requirement. A trigger allows us to enforce uniqueness going |
52 | 92 | forward without requiring a migration to clean up historical data.'; |
53 | 93 |
|
54 | | --- Restore prebuilds, previously modified in 000323_workspace_latest_builds_optimization.up.sql. |
55 | | -DROPVIEW workspace_prebuilds; |
56 | 94 |
|
57 | | -CREATEVIEWworkspace_prebuildsAS |
58 | | - WITH all_prebuildsAS ( |
59 | | -SELECTw.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_presetsAS ( |
66 | | -SELECT DISTINCTON (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_idIS NOT NULL) |
70 | | -ORDER BYworkspace_builds.workspace_id,workspace_builds.build_numberDESC |
71 | | - ), workspaces_with_agents_statusAS ( |
72 | | -SELECTw.idAS workspace_id, |
73 | | - bool_and((wa.lifecycle_state='ready'::workspace_agent_lifecycle_state))AS ready |
74 | | -FROM (((workspaces w |
75 | | -JOIN workspace_latest_builds wlbON ((wlb.workspace_id=w.id))) |
76 | | -JOIN workspace_resources wrON ((wr.job_id=wlb.job_id))) |
77 | | -JOIN workspace_agents waON ((wa.resource_id=wr.id))) |
78 | | -WHERE (w.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid) |
79 | | -GROUP BYw.id |
80 | | - ), current_presetsAS ( |
81 | | -SELECTw.idAS prebuild_id, |
82 | | -wlp.template_version_preset_id |
83 | | -FROM (workspaces w |
84 | | -JOIN workspaces_with_latest_presets wlpON ((wlp.workspace_id=w.id))) |
85 | | -WHERE (w.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid) |
86 | | - ) |
87 | | -SELECTp.id, |
88 | | -p.name, |
89 | | -p.template_id, |
90 | | -p.created_at, |
91 | | - COALESCE(a.ready, false)AS ready, |
92 | | -cp.template_version_preset_idAS current_preset_id |
93 | | -FROM ((all_prebuilds p |
94 | | -LEFT JOIN workspaces_with_agents_status aON ((a.workspace_id=p.id))) |
95 | | -JOIN current_presets cpON ((cp.prebuild_id=p.id))); |
| 95 | +ALTERTABLE workspace_agents |
| 96 | + DROP COLUMN deleted; |