Skip to content

Commit 9d41a46

Browse files
deansheatherdeansheather
committed
chore: fix and improve app slug migrations
1 parent 4086202 commit 9d41a46

9 files changed

+52
-10
lines changed

‎coderd/database/dump.sql

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000062_app_display_name.down.sql

Lines changed: 0 additions & 2 deletions
This file was deleted.

‎coderd/database/migrations/000062_app_display_name.up.sql

Lines changed: 0 additions & 2 deletions
This file was deleted.

‎coderd/database/migrations/000061_app_slug.down.sql renamed to ‎coderd/database/migrations/000064_app_slug.down.sql

File renamed without changes.

‎coderd/database/migrations/000061_app_slug.up.sql renamed to ‎coderd/database/migrations/000064_app_slug.up.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,4 +10,7 @@ UPDATE "workspace_apps" SET "slug" = "name";
1010
ALTER TABLE "workspace_apps" ALTER COLUMN "slug" SET NOT NULL;
1111
ALTER TABLE "workspace_apps" ALTER COLUMN "slug" DROP DEFAULT;
1212

13+
-- add unique index on "slug" column
14+
ALTER TABLE "workspace_apps" ADD CONSTRAINT "workspace_apps_agent_id_slug_key" UNIQUE ("agent_id", "slug");
15+
1316
COMMIT;

‎coderd/database/migrations/000065_app_display_name.down.sql

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
BEGIN;
2+
3+
-- Select all apps with an extra "row_number" column that determines the "rank"
4+
-- of the display name against other display names in the same agent.
5+
WITH row_numbers AS (
6+
SELECT
7+
*,
8+
row_number() OVER (PARTITION BY agent_id, display_name ORDER BY display_name ASC) AS row_number
9+
FROM
10+
workspace_apps
11+
)
12+
13+
-- Update any app with a "row_number" greater than 1 to have the row number
14+
-- appended to the display name. This effectively means that all lowercase
15+
-- display names remain untouched, while non-unique mixed case usernames are
16+
-- appended with a unique number. If you had three apps called all called asdf,
17+
-- they would then be renamed to e.g. asdf, asdf1234, and asdf5678.
18+
UPDATE
19+
workspace_apps
20+
SET
21+
display_name = workspace_apps.display_name || floor(random() * 10000)::text
22+
FROM
23+
row_numbers
24+
WHERE
25+
workspace_apps.id = row_numbers.id AND
26+
row_numbers.row_number > 1;
27+
28+
-- rename column "display_name" to "name" on "workspace_apps"
29+
ALTER TABLE "workspace_apps" RENAME COLUMN "display_name" TO "name";
30+
31+
-- restore unique index on "workspace_apps" table
32+
ALTER TABLE workspace_apps ADD CONSTRAINT workspace_apps_agent_id_name_key UNIQUE ("agent_id", "name");
33+
34+
COMMIT;

‎coderd/database/migrations/000065_app_display_name.up.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
BEGIN;
2+
3+
-- rename column "name" to "display_name" on "workspace_apps"
4+
ALTER TABLE "workspace_apps" RENAME COLUMN "name" TO "display_name";
5+
6+
-- drop constraint "workspace_apps_agent_id_name_key" on "workspace_apps".
7+
ALTER TABLE ONLY workspace_apps DROP CONSTRAINT IF EXISTS workspace_apps_agent_id_name_key;
8+
9+
COMMIT;

‎coderd/database/queries.sql.go

Lines changed: 4 additions & 4 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/unique_constraint.go

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)