Skip to content

Commit 524005b

Browse files
aslilacaslilac
committed
nom
1 parent 766c05c commit 524005b

File tree

2 files changed

+119
-0
lines changed

2 files changed

+119
-0
lines changed

‎coderd/database/migrations/000293_user_configs.down.sql

+57
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,57 @@
1+
-- Put back "theme_preference" column
2+
ALTER TABLE users ADD COLUMN IF NOT EXISTS
3+
theme_preference text DEFAULT ''::text NOT NULL;
4+
5+
-- Copy "theme_preference" back to "users"
6+
UPDATE users (theme_preference)
7+
SELECT value
8+
FROM user_configs
9+
WHERE users.id = user_configs.user_id
10+
AND user_configs.key = 'theme_preference';
11+
12+
-- Drop the "user_configs" table.
13+
DROP TABLE user_configs;
14+
15+
-- Replace "group_members_expanded", and bring back with "theme_preference"
16+
DROP VIEW group_members_expanded;
17+
-- Taken from 000242_group_members_view.up.sql
18+
CREATE VIEW
19+
group_members_expanded
20+
AS
21+
-- If the group is a user made group, then we need to check the group_members table.
22+
-- If it is the "Everyone" group, then we need to check the organization_members table.
23+
WITH all_members AS (
24+
SELECT user_id, group_id FROM group_members
25+
UNION
26+
SELECT user_id, organization_id AS group_id FROM organization_members
27+
)
28+
SELECT
29+
users.id AS user_id,
30+
users.email AS user_email,
31+
users.username AS user_username,
32+
users.hashed_password AS user_hashed_password,
33+
users.created_at AS user_created_at,
34+
users.updated_at AS user_updated_at,
35+
users.status AS user_status,
36+
users.rbac_roles AS user_rbac_roles,
37+
users.login_type AS user_login_type,
38+
users.avatar_url AS user_avatar_url,
39+
users.deleted AS user_deleted,
40+
users.last_seen_at AS user_last_seen_at,
41+
users.quiet_hours_schedule AS user_quiet_hours_schedule,
42+
users.theme_preference AS user_theme_preference,
43+
users.name AS user_name,
44+
users.github_com_user_id AS user_github_com_user_id,
45+
groups.organization_id AS organization_id,
46+
groups.name AS group_name,
47+
all_members.group_id AS group_id
48+
FROM
49+
all_members
50+
JOIN
51+
users ON users.id = all_members.user_id
52+
JOIN
53+
groups ON groups.id = all_members.group_id
54+
WHERE
55+
users.deleted = 'false';
56+
57+
COMMENT ON VIEW group_members_expanded IS 'Joins group members with user information, organization ID, group name. Includes both regular group members and organization members (as part of the "Everyone" group).';

‎coderd/database/migrations/000293_user_configs.up.sql

+62
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
CREATE TABLE IF NOT EXISTS user_configs (
2+
user_id uuid NOT NULL,
3+
key varchar(256) NOT NULL,
4+
value text NOT NULL
5+
);
6+
7+
ALTER TABLE ONLY user_configs
8+
ADD CONSTRAINT unique_key_per_user UNIQUE (user_id, key);
9+
10+
11+
--
12+
INSERT INTO user_configs (user_id, key, value)
13+
SELECT id, 'theme_preference', theme_preference
14+
FROM users
15+
WHERE users.theme_preference IS NOT NULL;
16+
17+
18+
-- Replace "group_members_expanded" without "theme_preference"
19+
DROP VIEW group_members_expanded;
20+
-- Taken from 000242_group_members_view.up.sql
21+
CREATE VIEW
22+
group_members_expanded
23+
AS
24+
-- If the group is a user made group, then we need to check the group_members table.
25+
-- If it is the "Everyone" group, then we need to check the organization_members table.
26+
WITH all_members AS (
27+
SELECT user_id, group_id FROM group_members
28+
UNION
29+
SELECT user_id, organization_id AS group_id FROM organization_members
30+
)
31+
SELECT
32+
users.id AS user_id,
33+
users.email AS user_email,
34+
users.username AS user_username,
35+
users.hashed_password AS user_hashed_password,
36+
users.created_at AS user_created_at,
37+
users.updated_at AS user_updated_at,
38+
users.status AS user_status,
39+
users.rbac_roles AS user_rbac_roles,
40+
users.login_type AS user_login_type,
41+
users.avatar_url AS user_avatar_url,
42+
users.deleted AS user_deleted,
43+
users.last_seen_at AS user_last_seen_at,
44+
users.quiet_hours_schedule AS user_quiet_hours_schedule,
45+
users.name AS user_name,
46+
users.github_com_user_id AS user_github_com_user_id,
47+
groups.organization_id AS organization_id,
48+
groups.name AS group_name,
49+
all_members.group_id AS group_id
50+
FROM
51+
all_members
52+
JOIN
53+
users ON users.id = all_members.user_id
54+
JOIN
55+
groups ON groups.id = all_members.group_id
56+
WHERE
57+
users.deleted = 'false';
58+
59+
COMMENT ON VIEW group_members_expanded IS 'Joins group members with user information, organization ID, group name. Includes both regular group members and organization members (as part of the "Everyone" group).';
60+
61+
-- Drop the "theme_preference" column now that the view no longer depends on it.
62+
ALTER TABLE users DROP COLUMN theme_preference;

0 commit comments

Comments
 (0)