mirror of
https://github.com/coder/coder.git
synced 2025-07-03 16:13:58 +00:00
8e684c8195
Updates coder/customers#365 This PR updates our migration framework to run all migrations in a single transaction. This is the same behavior we had in v1 and ensures that failed migrations don't bring the whole deployment down. If a migration fails now, it will automatically be rolled back to the previous version, allowing the deployment to continue functioning.
71 lines
1.8 KiB
SQL
71 lines
1.8 KiB
SQL
CREATE TABLE IF NOT EXISTS user_links (
|
|
user_id uuid NOT NULL,
|
|
login_type login_type NOT NULL,
|
|
linked_id text DEFAULT ''::text NOT NULL,
|
|
oauth_access_token text DEFAULT ''::text NOT NULL,
|
|
oauth_refresh_token text DEFAULT ''::text NOT NULL,
|
|
oauth_expiry timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL,
|
|
PRIMARY KEY(user_id, login_type),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- This migrates columns on api_keys to the new user_links table.
|
|
-- It does this by finding all the API keys for each user, choosing
|
|
-- the most recently updated for each one and then assigning its relevant
|
|
-- values to the user_links table.
|
|
-- A user should at most have a row for an OIDC account and a Github account.
|
|
-- 'password' login types are ignored.
|
|
|
|
INSERT INTO user_links
|
|
(
|
|
user_id,
|
|
login_type,
|
|
linked_id,
|
|
oauth_access_token,
|
|
oauth_refresh_token,
|
|
oauth_expiry
|
|
)
|
|
SELECT
|
|
keys.user_id,
|
|
keys.login_type,
|
|
'',
|
|
keys.oauth_access_token,
|
|
keys.oauth_refresh_token,
|
|
keys.oauth_expiry
|
|
FROM
|
|
(
|
|
SELECT
|
|
row_number() OVER (partition by user_id, login_type ORDER BY last_used DESC) AS x,
|
|
api_keys.* FROM api_keys
|
|
) as keys
|
|
WHERE x=1 AND keys.login_type != 'password';
|
|
|
|
-- Drop columns that have been migrated to user_links.
|
|
-- It appears the 'oauth_id_token' was unused and so it has
|
|
-- been dropped here as well to avoid future confusion.
|
|
ALTER TABLE api_keys
|
|
DROP COLUMN oauth_access_token,
|
|
DROP COLUMN oauth_refresh_token,
|
|
DROP COLUMN oauth_id_token,
|
|
DROP COLUMN oauth_expiry;
|
|
|
|
ALTER TABLE users ADD COLUMN login_type login_type NOT NULL DEFAULT 'password';
|
|
|
|
UPDATE
|
|
users
|
|
SET
|
|
login_type = (
|
|
SELECT
|
|
login_type
|
|
FROM
|
|
user_links
|
|
WHERE
|
|
user_links.user_id = users.id
|
|
ORDER BY oauth_expiry DESC
|
|
LIMIT 1
|
|
)
|
|
FROM
|
|
user_links
|
|
WHERE
|
|
user_links.user_id = users.id;
|