|
1 | 1 | CREATEVIEWworkspace_latest_buildAS |
2 | | -SELECT wb.* |
3 | | -FROM (SELECTtv.template_id, |
4 | | -wbmax.workspace_id, |
5 | | -MAX(wbmax.build_number)as max_build_number |
6 | | -FROM workspace_builds wbmax |
7 | | -JOIN template_versions tvON (tv.id=wbmax.template_version_id) |
8 | | -GROUP BYtv.template_id,wbmax.workspace_id) wbmax |
9 | | -JOIN workspace_builds wbON ( |
10 | | -wb.workspace_id=wbmax.workspace_id |
11 | | -ANDwb.build_number=wbmax.max_build_number |
12 | | -); |
| 2 | +SELECT DISTINCTON (workspace_id)* |
| 3 | +FROM workspace_builds |
| 4 | +ORDER BY workspace_id, build_numberDESC; |
13 | 5 |
|
14 | 6 | CREATEVIEWworkspace_prebuildsAS |
15 | 7 | WITH |
16 | | --- All workspaces owned by the "prebuilds" user. |
17 | | -all_prebuildsAS (SELECT w.* |
18 | | -FROM workspaces w |
19 | | -WHEREw.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'),-- The system user responsible for prebuilds. |
20 | | --- All workspace agents belonging to the workspaces owned by the "prebuilds" user. |
21 | | -workspace_agentsAS (SELECTw.idAS workspace_id,wa.idAS agent_id,wa.lifecycle_state,wa.ready_at |
22 | | -FROM workspaces w |
23 | | -INNER JOIN workspace_latest_build wlbONwlb.workspace_id=w.id |
24 | | -INNER JOIN workspace_resources wrONwr.job_id=wlb.job_id |
25 | | -INNER JOIN workspace_agents waONwa.resource_id=wr.id |
26 | | -WHEREw.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'-- The system user responsible for prebuilds. |
27 | | -GROUP BYw.id,wa.id), |
28 | | --- We can't rely on the template_version_preset_id in the workspace_builds table because this value is only set on the |
29 | | --- initial workspace creation. Subsequent stop/start transitions will not have a value for template_version_preset_id, |
30 | | --- and therefore we can't rely on (say) the latest build's chosen template_version_preset_id. |
31 | | --- |
32 | | --- See https://github.com/coder/internal/issues/398 |
33 | | -current_presetsAS (SELECTw.idAS prebuild_id,lps.template_version_preset_id |
34 | | -FROM workspaces w |
35 | | -INNER JOIN ( |
36 | | --- The latest workspace build which had a preset explicitly selected |
37 | | -SELECT wb.* |
38 | | -FROM (SELECTtv.template_id, |
39 | | -wbmax.workspace_id, |
40 | | -MAX(wbmax.build_number)as max_build_number |
41 | | -FROM workspace_builds wbmax |
42 | | -JOIN template_versions tvON (tv.id=wbmax.template_version_id) |
43 | | -WHEREwbmax.template_version_preset_idIS NOT NULL |
44 | | -GROUP BYtv.template_id,wbmax.workspace_id) wbmax |
45 | | -JOIN workspace_builds wbON ( |
46 | | -wb.workspace_id=wbmax.workspace_id |
47 | | -ANDwb.build_number=wbmax.max_build_number |
48 | | -)) lpsONlps.workspace_id=w.id |
49 | | -WHEREw.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0')-- The system user responsible for prebuilds. |
| 8 | +-- All workspaces owned by the "prebuilds" user. |
| 9 | + all_prebuildsAS (SELECT w.* |
| 10 | +FROM workspaces w |
| 11 | +WHEREw.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'),-- The system user responsible for prebuilds. |
| 12 | +-- We can't rely on the template_version_preset_id in the workspace_builds table because this value is only set on the |
| 13 | +-- initial workspace creation. Subsequent stop/start transitions will not have a value for template_version_preset_id, |
| 14 | +-- and therefore we can't rely on (say) the latest build's chosen template_version_preset_id. |
| 15 | +-- |
| 16 | +-- See https://github.com/coder/internal/issues/398 |
| 17 | + latest_prebuild_buildsAS (SELECT* |
| 18 | +FROM workspace_latest_build |
| 19 | +WHERE template_version_preset_idIS NOT NULL), |
| 20 | +-- All workspace agents belonging to the workspaces owned by the "prebuilds" user. |
| 21 | + workspace_agentsAS (SELECTw.idAS workspace_id,wa.idAS agent_id,wa.lifecycle_state,wa.ready_at |
| 22 | +FROM workspaces w |
| 23 | +INNER JOIN workspace_latest_build wlbONwlb.workspace_id=w.id |
| 24 | +INNER JOIN workspace_resources wrONwr.job_id=wlb.job_id |
| 25 | +INNER JOIN workspace_agents waONwa.resource_id=wr.id |
| 26 | +WHEREw.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'-- The system user responsible for prebuilds. |
| 27 | +GROUP BYw.id,wa.id), |
| 28 | + current_presetsAS (SELECTw.idAS prebuild_id,lpb.template_version_preset_id |
| 29 | +FROM workspaces w |
| 30 | +INNER JOIN latest_prebuild_builds lpbONlpb.workspace_id=w.id |
| 31 | +WHEREw.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0')-- The system user responsible for prebuilds. |
50 | 32 | SELECT p.*,a.agent_id,a.lifecycle_state,a.ready_at,cp.template_version_preset_idAS current_preset_id |
51 | 33 | FROM all_prebuilds p |
52 | | -LEFT JOIN workspace_agents aONa.workspace_id=p.id |
53 | | -INNER JOIN current_presets cpONcp.prebuild_id=p.id; |
| 34 | +LEFT JOIN workspace_agents aONa.workspace_id=p.id |
| 35 | +INNER JOIN current_presets cpONcp.prebuild_id=p.id; |
54 | 36 |
|
55 | 37 | CREATEVIEWworkspace_prebuild_buildsAS |
56 | 38 | SELECT* |
|