link comments table to users table

3 files changed, 69 insertions, 6 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;
diff --git a/perllib/FixMyStreet/DB/Result/Comment.pm b/perllib/FixMyStreet/DB/Result/Comment.pm
index 557348429..0ba034927 100644
--- a/perllib/FixMyStreet/DB/Result/Comment.pm
+++ b/perllib/FixMyStreet/DB/Result/Comment.pm
@@ -22,8 +22,6 @@ __PACKAGE__->add_columns(
   { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
   "name",
   { data_type => "text", is_nullable => 1 },
-  "email",
-  { data_type => "text", is_nullable => 0 },
   "website",
   { data_type => "text", is_nullable => 1 },
   "created",
@@ -50,9 +48,19 @@ __PACKAGE__->add_columns(
   { data_type => "boolean", is_nullable => 0 },
   "mark_open",
   { data_type => "boolean", default_value => \"false", is_nullable => 0 },
+  "user_id",
+  { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
+  "anonymous",
+  { data_type => "boolean", is_nullable => 1 },
 );
 __PACKAGE__->set_primary_key("id");
 __PACKAGE__->belongs_to(
+  "user",
+  "FixMyStreet::DB::Result::User",
+  { id => "user_id" },
+  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
+);
+__PACKAGE__->belongs_to(
   "problem",
   "FixMyStreet::DB::Result::Problem",
   { id => "problem_id" },
@@ -60,8 +68,8 @@ __PACKAGE__->belongs_to(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-05-17 14:18:03
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:CVW3SYgW5Us142cj8zTafg
+# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-05-18 11:45:22
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:1HsEtlhxEN6I/umfbrswSw
 
 
 # You can replace this text with custom code or comments, and it will be preserved on regeneration
diff --git a/perllib/FixMyStreet/DB/Result/User.pm b/perllib/FixMyStreet/DB/Result/User.pm
index 7bb2bfcff..956deaa56 100644
--- a/perllib/FixMyStreet/DB/Result/User.pm
+++ b/perllib/FixMyStreet/DB/Result/User.pm
@@ -36,6 +36,12 @@ __PACKAGE__->has_many(
   { cascade_copy => 0, cascade_delete => 0 },
 );
 __PACKAGE__->has_many(
+  "comments",
+  "FixMyStreet::DB::Result::Comment",
+  { "foreign.user_id" => "self.id" },
+  { cascade_copy => 0, cascade_delete => 0 },
+);
+__PACKAGE__->has_many(
   "problems",
   "FixMyStreet::DB::Result::Problem",
   { "foreign.user_id" => "self.id" },
@@ -43,8 +49,8 @@ __PACKAGE__->has_many(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-05-11 14:21:33
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Qaw6SZ5my5MnH7B5sB9NDw
+# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-05-18 10:55:41
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:PsK+O4EefB4wywX6VZYNuw
 
 use mySociety::EmailUtil;