Skip to content

Commit 751400b

Browse files
aslilacaslilac
committed
💅
1 parent bdf6142 commit 751400b

7 files changed

+160
-96
lines changed

‎coderd/database/dump.sql

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

‎coderd/database/migrations/000189_external_auth_providers_jsonb.down.sql

Lines changed: 39 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -1,43 +1,55 @@
11
-- We cannot alter the column type while a view depends on it, so we drop it and recreate it.
22
DROP VIEW template_version_with_user;
33

4+
45
-- Does the opposite of `migrate_external_auth_providers_to_jsonb`
56
-- eg. `'[{"id": "github"}, {"id": "gitlab"}]'::jsonb` would become `'{github,gitlab}'::text[]`
6-
CREATE OR REPLACE FUNCTION revert_migrate_external_auth_providers_to_jsonb(jsonb) RETURNS text[]
7-
LANGUAGE plpgsql AS $$
7+
CREATE OR REPLACE FUNCTION revert_migrate_external_auth_providers_to_jsonb(jsonb)
8+
RETURNS text[]
9+
LANGUAGE plpgsql
10+
AS $$
811
DECLARE
9-
result text[];
12+
result text[];
1013
BEGIN
11-
SELECT array_agg(id::text)
12-
INTO result
13-
FROM (
14-
SELECT jsonb_array_elements($1)->>'id' AS id
15-
) as external_auth_provider_ids;
16-
RETURN result;
14+
SELECT
15+
array_agg(id::text) INTO result
16+
FROM (
17+
SELECT
18+
jsonb_array_elements($1) ->> 'id' AS id) AS external_auth_provider_ids;
19+
RETURN result;
1720
END;
1821
$$;
1922

2023

24+
-- Remove the non-null constraint and default
25+
ALTER TABLE template_versions
26+
ALTER COLUMN external_auth_providers DROP NOT NULL;
27+
ALTER TABLE template_versions
28+
ALTER COLUMN external_auth_providers DROP DEFAULT;
29+
30+
2131
-- Update the column type and migrate the values
2232
ALTER TABLE template_versions
23-
ALTER COLUMN external_auth_providers TYPE text[]
24-
USING revert_migrate_external_auth_providers_to_jsonb(external_auth_providers);
33+
ALTER COLUMN external_auth_providers TYPE text[]
34+
USING revert_migrate_external_auth_providers_to_jsonb(external_auth_providers);
35+
2536

2637
-- Recreate `template_version_with_user` as described in dump.sql
2738
CREATE VIEW template_version_with_user AS
28-
SELECT template_versions.id,
29-
template_versions.template_id,
30-
template_versions.organization_id,
31-
template_versions.created_at,
32-
template_versions.updated_at,
33-
template_versions.name,
34-
template_versions.readme,
35-
template_versions.job_id,
36-
template_versions.created_by,
37-
template_versions.external_auth_providers,
38-
template_versions.message,
39-
template_versions.archived,
40-
COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
41-
COALESCE(visible_users.username, ''::text) AS created_by_username
42-
FROM (public.template_versions
43-
LEFT JOIN visible_users ON ((template_versions.created_by = visible_users.id)));
39+
SELECT
40+
template_versions.id,
41+
template_versions.template_id,
42+
template_versions.organization_id,
43+
template_versions.created_at,
44+
template_versions.updated_at,
45+
template_versions.name,
46+
template_versions.readme,
47+
template_versions.job_id,
48+
template_versions.created_by,
49+
template_versions.external_auth_providers,
50+
template_versions.message,
51+
template_versions.archived,
52+
COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
53+
COALESCE(visible_users.username, ''::text) AS created_by_username
54+
FROM (public.template_versions
55+
LEFT JOIN visible_users ON (template_versions.created_by = visible_users.id));

‎coderd/database/migrations/000189_external_auth_providers_jsonb.up.sql

Lines changed: 39 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1,40 +1,54 @@
11
-- We cannot alter the column type while a view depends on it, so we drop it and recreate it.
22
DROP VIEW template_version_with_user;
33

4+
45
-- Turns the list of provider names into JSONB with the type `Array<{ id: string; optional?: boolean }>`
56
-- eg. `'{github,gitlab}'::text[]` would become `'[{"id": "github"}, {"id": "gitlab"}]'::jsonb`
6-
CREATE OR REPLACE FUNCTION migrate_external_auth_providers_to_jsonb(text[]) RETURNS jsonb
7-
LANGUAGE plpgsql AS $$
7+
CREATE OR REPLACE FUNCTION migrate_external_auth_providers_to_jsonb(text[])
8+
RETURNS jsonb
9+
LANGUAGE plpgsql
10+
AS $$
811
DECLARE
9-
result jsonb;
12+
result jsonb;
1013
BEGIN
11-
SELECT jsonb_agg(jsonb_build_object('id', value::text))
12-
INTO result
13-
FROM unnest($1) AS value;
14-
RETURN result;
14+
SELECT
15+
jsonb_agg(jsonb_build_object('id', value::text)) INTO result
16+
FROM
17+
unnest($1) AS value;
18+
RETURN result;
1519
END;
1620
$$;
1721

22+
1823
-- Update the column type and migrate the values
1924
ALTER TABLE template_versions
20-
ALTER COLUMN external_auth_providers TYPE jsonb
21-
USING migrate_external_auth_providers_to_jsonb(external_auth_providers);
25+
ALTER COLUMN external_auth_providers TYPE jsonb
26+
USING migrate_external_auth_providers_to_jsonb(external_auth_providers);
27+
28+
29+
-- Make the column non-nullable to make the types nicer on the Go side
30+
ALTER TABLE template_versions
31+
ALTER COLUMN external_auth_providers SET NOT NULL;
32+
ALTER TABLE template_versions
33+
ALTER COLUMN external_auth_providers SET DEFAULT '[]'::jsonb;
34+
2235

2336
-- Recreate `template_version_with_user` as described in dump.sql
2437
CREATE VIEW template_version_with_user AS
25-
SELECT template_versions.id,
26-
template_versions.template_id,
27-
template_versions.organization_id,
28-
template_versions.created_at,
29-
template_versions.updated_at,
30-
template_versions.name,
31-
template_versions.readme,
32-
template_versions.job_id,
33-
template_versions.created_by,
34-
template_versions.external_auth_providers,
35-
template_versions.message,
36-
template_versions.archived,
37-
COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
38-
COALESCE(visible_users.username, ''::text) AS created_by_username
39-
FROM (public.template_versions
40-
LEFT JOIN visible_users ON ((template_versions.created_by = visible_users.id)));
38+
SELECT
39+
template_versions.id,
40+
template_versions.template_id,
41+
template_versions.organization_id,
42+
template_versions.created_at,
43+
template_versions.updated_at,
44+
template_versions.name,
45+
template_versions.readme,
46+
template_versions.job_id,
47+
template_versions.created_by,
48+
template_versions.external_auth_providers,
49+
template_versions.message,
50+
template_versions.archived,
51+
COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
52+
COALESCE(visible_users.username, ''::text) AS created_by_username
53+
FROM (public.template_versions
54+
LEFT JOIN visible_users ON (template_versions.created_by = visible_users.id));

‎coderd/database/models.go

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

‎coderd/database/queries.sql.go

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

0 commit comments

Comments
 (0)