link users and problems

2 files changed, 46 insertions, 0 deletions

diff --git a/db/schema_0001.sql b/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql
index ad95f556f..ad95f556f 100644
--- a/db/schema_0001.sql
+++ b/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql
diff --git a/db/schema_0002-create_users_from_problems_and_link.sql b/db/schema_0002-create_users_from_problems_and_link.sql
new file mode 100644
index 000000000..73ec4a91e
--- /dev/null
+++ b/db/schema_0002-create_users_from_problems_and_link.sql
@@ -0,0 +1,46 @@
+-- link the problems to the users, creating the users as required. Removes the
+-- email field from the problems.
+--
+-- Uses the most recently used non-anonymmous name as the name for the user.
+
+begin;
+
+-- create users from the problems
+INSERT INTO users (email)
+    SELECT distinct( lower( email ) ) FROM problem;
+
+-- add a user id to the problems
+ALTER TABLE problem
+    ADD COLUMN user_id INT REFERENCES users(id);
+
+-- populate the user_ids
+update problem
+    set user_id = (
+        select id
+        from users
+        where users.email = lower( problem.email )
+    );
+
+-- create the index now that the entries have been made
+create index problem_user_id_idx on problem ( user_id );
+
+-- add names from the problems
+UPDATE users
+    SET name = (
+        select name from problem
+        where user_id = users.id
+          and anonymous = false
+        order by created desc
+        limit 1
+    );
+
+
+-- make the problems user id not null etc
+ALTER TABLE problem
+    ALTER COLUMN user_id SET NOT NULL;
+
+-- drop emails from the problems
+ALTER TABLE problem
+    DROP COLUMN email;
+
+commit;