Skip to content

Commit bdf6142

Browse files
aslilacaslilac
committed
db migration
1 parent 179adae commit bdf6142

File tree

2 files changed

+83
-0
lines changed

2 files changed

+83
-0
lines changed

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

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
-- We cannot alter the column type while a view depends on it, so we drop it and recreate it.
2+
DROP VIEW template_version_with_user;
3+
4+
-- Does the opposite of `migrate_external_auth_providers_to_jsonb`
5+
-- 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 $$
8+
DECLARE
9+
result text[];
10+
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;
17+
END;
18+
$$;
19+
20+
21+
-- Update the column type and migrate the values
22+
ALTER TABLE template_versions
23+
ALTER COLUMN external_auth_providers TYPE text[]
24+
USING revert_migrate_external_auth_providers_to_jsonb(external_auth_providers);
25+
26+
-- Recreate `template_version_with_user` as described in dump.sql
27+
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)));

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

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
-- We cannot alter the column type while a view depends on it, so we drop it and recreate it.
2+
DROP VIEW template_version_with_user;
3+
4+
-- Turns the list of provider names into JSONB with the type `Array<{ id: string; optional?: boolean }>`
5+
-- 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 $$
8+
DECLARE
9+
result jsonb;
10+
BEGIN
11+
SELECT jsonb_agg(jsonb_build_object('id', value::text))
12+
INTO result
13+
FROM unnest($1) AS value;
14+
RETURN result;
15+
END;
16+
$$;
17+
18+
-- Update the column type and migrate the values
19+
ALTER TABLE template_versions
20+
ALTER COLUMN external_auth_providers TYPE jsonb
21+
USING migrate_external_auth_providers_to_jsonb(external_auth_providers);
22+
23+
-- Recreate `template_version_with_user` as described in dump.sql
24+
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)));

0 commit comments

Comments
 (0)