2 files changed, 49 insertions, 6 deletions
diff --git a/perllib/FixMyStreet/DB/Result/User.pm b/perllib/FixMyStreet/DB/Result/User.pm
index 19adf5d49..b733b47bd 100644
--- a/perllib/FixMyStreet/DB/Result/User.pm
+++ b/perllib/FixMyStreet/DB/Result/User.pm
@@ -19,7 +19,7 @@ __PACKAGE__->add_columns(
     sequence          => "users_id_seq",
   },
   "email",
-  { data_type => "text", is_nullable => 0 },
+  { data_type => "text", is_nullable => 1 },
   "name",
   { data_type => "text", is_nullable => 1 },
   "phone",
@@ -30,21 +30,24 @@ __PACKAGE__->add_columns(
   { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
   "flagged",
   { data_type => "boolean", default_value => \"false", is_nullable => 0 },
+  "is_superuser",
+  { data_type => "boolean", default_value => \"false", is_nullable => 0 },
   "title",
   { data_type => "text", is_nullable => 1 },
   "twitter_id",
   { data_type => "bigint", is_nullable => 1 },
   "facebook_id",
   { data_type => "bigint", is_nullable => 1 },
-  "is_superuser",
-  { data_type => "boolean", default_value => \"false", is_nullable => 0 },
   "area_id",
   { data_type => "integer", is_nullable => 1 },
   "extra",
   { data_type => "text", is_nullable => 1 },
+  "email_verified",
+  { data_type => "boolean", default_value => \"false", is_nullable => 0 },
+  "phone_verified",
+  { data_type => "boolean", default_value => \"false", is_nullable => 0 },
 );
 __PACKAGE__->set_primary_key("id");
-__PACKAGE__->add_unique_constraint("users_email_key", ["email"]);
 __PACKAGE__->add_unique_constraint("users_facebook_id_key", ["facebook_id"]);
 __PACKAGE__->add_unique_constraint("users_twitter_id_key", ["twitter_id"]);
 __PACKAGE__->has_many(
@@ -102,8 +105,13 @@ __PACKAGE__->has_many(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2016-09-16 14:22:10
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:7wfF1VnZax2QTXCIPXr+vg
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2017-09-19 18:02:17
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:OKHKCSahWD3Ov6ulj+2f/w
+
+# These are not fully unique constraints (they only are when the *_verified
+# is true), but this is managed in ResultSet::User's find() wrapper.
+__PACKAGE__->add_unique_constraint("users_email_verified_key", ["email", "email_verified"]);
+__PACKAGE__->add_unique_constraint("users_phone_verified_key", ["phone", "phone_verified"]);
 
 __PACKAGE__->load_components("+FixMyStreet::DB::RABXColumn");
 __PACKAGE__->rabx_column('extra');
diff --git a/perllib/FixMyStreet/DB/ResultSet/User.pm b/perllib/FixMyStreet/DB/ResultSet/User.pm
index 7e657a936..9a8a50559 100644
--- a/perllib/FixMyStreet/DB/ResultSet/User.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/User.pm
@@ -4,5 +4,40 @@ use base 'DBIx::Class::ResultSet';
 use strict;
 use warnings;
 
+use Moo;
+
+# The database has a partial unique index on email (when email_verified is
+# true), and phone (when phone_verified is true). In the code, we can only
+# say these are fully unique indices, which they aren't, as there could be
+# multiple identical unverified phone numbers.
+#
+# We assume that any and all calls to find (also called using find_or_new,
+# find_or_create, or update_or_new/create) are to look up verified entries
+# only (it would make no sense to find() a non-unique entry). Therefore we
+# help the code along by specifying the most appropriate key to use, given
+# the data provided, and setting the appropriate verified boolean.
+
+around find => sub {
+    my ($orig, $self) = (shift, shift);
+    # If there's already a key, assume caller knows what they're doing
+    if (ref $_[0] eq 'HASH' && !$_[1]->{key}) {
+        if ($_[0]->{id}) {
+            $_[1]->{key} = 'primary';
+        } elsif (exists $_[0]->{email} && exists $_[0]->{phone}) {
+            # If there's both email and phone, caller must also have specified
+            # a verified boolean so that we know what we're looking for
+            if (!$_[0]->{email_verified} && !$_[0]->{phone_verified}) {
+                die "Cannot perform a User find() with both email and phone and no verified";
+            }
+        } elsif (exists $_[0]->{email}) {
+            $_[0]->{email_verified} = 1;
+            $_[1]->{key} = 'users_email_verified_key';
+        } elsif (exists $_[0]->{phone}) {
+            $_[0]->{phone_verified} = 1;
+            $_[1]->{key} = 'users_phone_verified_key';
+        }
+    }
+    $self->$orig(@_);
+};
 
 1;