link comments table to users table

1 files changed, 49 insertions, 0 deletions

diff --git a/db/schema_0004-create_users_from_comments_and_link.sql b/db/schema_0004-create_users_from_comments_and_link.sql
new file mode 100644
index 000000000..6dc50f531
--- /dev/null
+++ b/db/schema_0004-create_users_from_comments_and_link.sql
@@ -0,0 +1,49 @@
+begin;
+
+-- create any users that don't already exist
+INSERT INTO users (email)
+    SELECT distinct( lower( comment.email ) ) FROM comment 
+        LEFT JOIN users ON lower( users.email ) = lower( comment.email )
+        WHERE users.id IS NULL;
+
+ALTER table comment
+    ADD COLUMN user_id INT REFERENCES users(id);
+
+-- populate the user ids in the comment table
+UPDATE comment
+    SET user_id = (
+        SELECT id
+        FROM users
+        WHERE users.email = lower( comment.email )
+    );
+
+CREATE INDEX comment_user_id_idx on comment ( user_id );
+
+UPDATE users
+    SET name = (
+        select name from comment
+        where user_id = users.id
+        order by created desc
+        limit 1
+    )
+WHERE users.name = '';
+
+-- set up the anonymous flag
+ALTER table comment
+    ADD COLUMN anonymous BOOL;
+
+UPDATE comment SET anonymous = true WHERE name = '';
+
+UPDATE comment SET anonymous = false WHERE name <> '';
+
+-- tidy up now everythings in place
+ALTER table comment
+    ALTER COLUMN user_id SET NOT NULL;
+
+ALTER table comment
+    ALTER COLUMN anonymous SET NOT NULL;
+
+ALTER table comment
+    DROP COLUMN email;
+
+commit;