correct correspondent full name (db version now 13)

diff --git a/lib/Debbugs/DB/Result/CorrespondentFullName.pm b/lib/Debbugs/DB/Result/CorrespondentFullName.pm
index a5be28391424693094157a86ad4076eb594485d5..69af0205731c4387fba6c5aee6ada3d558f0c07e 100644 (file)
--- a/lib/Debbugs/DB/Result/CorrespondentFullName.pm
+++ b/lib/Debbugs/DB/Result/CorrespondentFullName.pm
@@ -45,16 +45,20 @@ __PACKAGE__->table("correspondent_full_name");
 
 Correspondent ID (matches correspondent)
 
-=head2 full_name
+=head2 full_addr
 
   data_type: 'text'
   is_nullable: 0
 
-Correspondent full name (includes e-mail address)
+=head2 name
+
+  data_type: 'text'
+  default_value: (empty string)
+  is_nullable: 0
 
 =head2 last_seen
 
-  data_type: 'timestamp'
+  data_type: 'timestamp with time zone'
   default_value: current_timestamp
   is_nullable: 0
   original: {default_value => \"now()"}
@@ -64,11 +68,13 @@ Correspondent full name (includes e-mail address)
 __PACKAGE__->add_columns(
   "correspondent",
   { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
-  "full_name",
+  "full_addr",
   { data_type => "text", is_nullable => 0 },
+  "name",
+  { data_type => "text", default_value => "", is_nullable => 0 },
   "last_seen",
   {
-    data_type     => "timestamp",
+    data_type     => "timestamp with time zone",
     default_value => \"current_timestamp",
     is_nullable   => 0,
     original      => { default_value => \"now()" },
@@ -83,7 +89,7 @@ __PACKAGE__->add_columns(
 
 =item * L</correspondent>
 
-=item * L</full_name>
+=item * L</full_addr>
 
 =back
 
@@ -91,7 +97,7 @@ __PACKAGE__->add_columns(
 
 __PACKAGE__->add_unique_constraint(
   "correspondent_full_name_correspondent_full_name_idx",
-  ["correspondent", "full_name"],
+  ["correspondent", "full_addr"],
 );
 
 =head1 RELATIONS
@@ -112,8 +118,8 @@ __PACKAGE__->belongs_to(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07046 @ 2017-03-04 10:59:03
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:2Ac8mrDV2IsE/11YsYoqQQ
+# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-08-01 13:43:06
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:3IdwxC/wrKHGQT05XYLDYg
 
 sub sqlt_deploy_hook {
     my ($self, $sqlt_table) = @_;

diff --git a/lib/Debbugs/DB/ResultSet/CorrespondentFullName.pm b/lib/Debbugs/DB/ResultSet/CorrespondentFullName.pm
new file mode 100644 (file)
index 0000000..0edafdf
--- /dev/null
+++ b/lib/Debbugs/DB/ResultSet/CorrespondentFullName.pm
@@ -0,0 +1,90 @@
+# This module is part of debbugs, and is released
+# under the terms of the GPL version 2, or any later version. See the
+# file README and COPYING for more information.
+# Copyright 2017 by Don Armstrong <don@donarmstrong.com>.
+use utf8;
+package Debbugs::DB::ResultSet::CorrespondentFullName;
+
+=head1 NAME
+
+Debbugs::DB::ResultSet::CorrespondentFullName - Correspondent table actions
+
+=head1 SYNOPSIS
+
+
+
+=head1 DESCRIPTION
+
+
+
+=cut
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::ResultSet';
+
+use Debbugs::DB::Util qw(select_one);
+
+use Debbugs::Common qw(getparsedaddrs);
+use Debbugs::DB::Util qw(select_one);
+use Scalar::Util qw(blessed);
+
+sub upsert_correspondent_id {
+    my ($self,$addr) = @_;
+    my $full_addr;
+    if (blessed($addr)) {
+       $full_addr = $addr->format();
+    } else {
+       $full_addr = $addr;
+       undef $addr;
+    }
+    my $rs = $self->
+       search({full_addr => $addr,
+              },
+             {result_class => 'DBIx::Class::ResultClass::HashRefInflator',
+             }
+             )->first();
+    if (defined $rs) {
+       return $rs->{correspondent};
+    }
+    if (not defined $addr) {
+       $addr = getparsedaddrs($full_addr);
+    }
+    my $email = $addr->address();
+    my $name = $addr->phrase();
+    if (defined $name) {
+       $name =~ s/^\"|\"$//g;
+       $name =~ s/^\s+|\s+$//g;
+    } else {
+       $name = '';
+    }
+    my $ci = $self->result_source->schema->
+       select_one(<<'SQL',$addr,$addr);
+WITH ins AS (
+INSERT INTO correspondent (addr) VALUES (?)
+ ON CONFLICT (addr) DO NOTHING RETURNING id
+)
+SELECT id FROM ins
+UNION ALL
+SELECT id FROM correspondent WHERE addr = ?
+LIMIT 1;
+SQL
+    $self->result_source->schema->
+       select_one(<<'SQL',$ci,$full_addr,$name);
+WITH ins AS (
+INSERT INTO correspondent_full_name (correspondent,full_addr,name)
+   VALUES (?,?,?) ON CONFLICT (correspondent,full_addr) DO UPDATE SET last_seen=NOW() RETURNING correspondent
+)
+SELECT 1 FROM ins
+UNION ALL
+SELECT 1;
+SQL
+    return $ci;
+}
+
+
+
+1;
+
+__END__

diff --git a/sql/upgrade/schema_12_to_13.pl b/sql/upgrade/schema_12_to_13.pl
new file mode 100644 (file)
index 0000000..a9bc3de
--- /dev/null
+++ b/sql/upgrade/schema_12_to_13.pl
@@ -0,0 +1,26 @@
+sub upgrade {
+    my $s = shift;
+
+    $s->prepare_execute(<<'SQL');
+ALTER TABLE correspondent_full_name RENAME TO correspondent_full_name_old;
+
+CREATE TABLE correspondent_full_name(
+correspondent INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE,
+full_addr TEXT NOT NULL,
+"name" TEXT NOT NULL,
+last_seen TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+);
+
+CREATE UNIQUE INDEX ON correspondent_full_name(correspondent,full_addr);
+CREATE INDEX ON correspondent_full_name(full_addr);
+
+INSERT INTO correspondent_full_name
+SELECT cfn.correspondent,CONCAT(cfn.full_name,' <',c.addr,'>') AS full_addr,
+cfn.full_name, cfn.last_seen
+FROM correspondent_full_name_old cfn
+JOIN correspondent c ON cfn.correspondent=c.id;
+
+SQL
+}
+
+1;