finish migration

coder · sreya · Aug 17, 2022 · Aug 9, 2022 · Aug 9, 2022 · Aug 9, 2022
commit 14b5382ce9406049015cc3db0b9800fca1088f11
diff --git a/coderd/database/migrations/000034_linked_user_id.down.sql b/coderd/database/migrations/000034_linked_user_id.down.sql
@@ -1,6 +1,3 @@
 BEGIN;
 
-ALTER TABLE users DROP COLUMN linked_id;
-ALTER TABLE users DROP COLUMN login_type;
-
 COMMIT;
diff --git a/coderd/database/migrations/000034_linked_user_id.up.sql b/coderd/database/migrations/000034_linked_user_id.up.sql
@@ -1,29 +1,44 @@
 BEGIN;
 
-CREATE TABLE IF NOT EXISTS users (
+ALTER TYPE login_type ADD VALUE 'oidc';
+
+CREATE TABLE IF NOT EXISTS user_links (
 	user_id uuid NOT NULL,
 	login_type login_type NOT NULL,
-	linked_id text NOT NULL DEFAULT ''::text NOT NULL,
-    oauth_access_token text DEFAULT ''::text NOT NULL,
-    oauth_refresh_token text DEFAULT ''::text NOT NULL,
-    oauth_id_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,
-	UNIQUE(user_id, login_type),
-)
-ALTER TABLE users ADD COLUMN linked_id text NOT NULL DEFAULT ''; 
+	linked_id text DEFAULT ''::text NOT NULL,
+	oauth_access_token text DEFAULT ''::text NOT NULL,
+	oauth_refresh_token text DEFAULT ''::text NOT NULL,
+	oauth_id_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,
+	UNIQUE(user_id, login_type)
+);
+
+INSERT INTO user_links 
+	( 
+		user_id,
+		login_type,
+		linked_id,
+		oauth_access_token,
+		oauth_refresh_token,
+		oauth_id_token,
+		oauth_expiry
+	)
+SELECT 
+	keys.user_id, 
+	keys.login_type,
+	'',
+	keys.oauth_access_token,
+	keys.oauth_refresh_token,
+	keys.oauth_id_token,
+	keys.oauth_expiry 
+FROM 
+	( 
+		SELECT 
+			row_number() OVER (partition by user_id, login_type ORDER BY updated_at DESC) AS x, 
+			api_keys.* FROM api_keys
+	) as keys
+ WHERE x=1 AND keys.login_type != 'password';
 
-UPDATE 
-  users
-SET
-  login_type = (
-    SELECT 
-      login_type
-    FROM
-      api_keys
-    WHERE
-      api_keys.user_id = users.id
-    ORDER BY updated_at DESC
-    LIMIT 1
-  );
+ALTER TABLE api_keys RENAME COLUMN login_type TO _login_type
 
 COMMIT;