Added the date verified when verifying an email address (ORCID#7101)

Camelia-Orcid · amontenegro · web-flow · commit bb4e1010bfdc · 2024-10-09T11:49:06.000-06:00
Co-authored-by: Angel Montenegro &lt;a.montenegro@orcid.org&gt;
diff --git a/orcid-persistence/src/main/java/org/orcid/persistence/dao/impl/EmailDaoImpl.java b/orcid-persistence/src/main/java/org/orcid/persistence/dao/impl/EmailDaoImpl.java
@@ -123,7 +123,7 @@ public void addSourceToEmail(String sourceId, String email) {
     @Transactional
     @UpdateProfileLastModified
     public boolean verifyEmail(String email) {
-        Query query = entityManager.createNativeQuery("update email set is_verified = true, is_current=true, last_modified=now() where trim(lower(email)) = trim(lower(:email))");
+        Query query = entityManager.createNativeQuery("update email set is_verified = true, is_current=true, last_modified=now(), date_verified=now() where trim(lower(email)) = trim(lower(:email))");
         query.setParameter("email", email);
         return query.executeUpdate() > 0;
     }
diff --git a/orcid-persistence/src/main/java/org/orcid/persistence/jpa/entities/EmailEntity.java b/orcid-persistence/src/main/java/org/orcid/persistence/jpa/entities/EmailEntity.java
@@ -3,6 +3,7 @@
 import java.util.Collection;
 import java.util.HashMap;
 import java.util.Map;
+import java.util.Date;
 
 import javax.persistence.Column;
 import javax.persistence.Entity;
@@ -24,7 +25,8 @@ public class EmailEntity extends SourceAwareEntity<String> implements OrcidAware
     private Boolean primary;
     private Boolean current;
     private Boolean verified;
-    private String visibility;    
+    private String visibility; 
+    private Date dateVerified;
     
     @Override
     @Id
@@ -90,6 +92,15 @@ public String getVisibility() {
     public void setVisibility(String visibility) {
         this.visibility = visibility;
     }
+    
+    @Column(name = "date_verified")
+    public Date getDateVerified() {
+        return dateVerified;
+    }
+
+    public void setDateVerified(Date dateVerified) {
+        this.dateVerified = dateVerified;
+    }
 
     public static Map<String, EmailEntity> mapByLowerCaseEmail(Collection<EmailEntity> emailEntities) {
         Map<String, EmailEntity> map = new HashMap<>();
@@ -109,5 +120,6 @@ public void clean() {
         visibility= null;
         verified = null;
         visibility = null;
+        dateVerified = null;
     }     
 }
diff --git a/orcid-persistence/src/main/resources/db-master.xml b/orcid-persistence/src/main/resources/db-master.xml
@@ -404,4 +404,5 @@
   <include file="/db/updates/create_dw_notification.xml" />
   <include file="/db/updates/create_dw_profile_email_domain.xml" />
   <include file="/db/updates/add_unique_constraint_external_id_disambiguated_org.xml" />
+  <include file="/db/updates/add_date_verified_to_email.xml" />
 </databaseChangeLog>
diff --git a/orcid-persistence/src/main/resources/db/updates/add_date_verified_to_email.xml b/orcid-persistence/src/main/resources/db/updates/add_date_verified_to_email.xml
@@ -0,0 +1,14 @@
+<databaseChangeLog
+	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
+	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
+
+	<changeSet author="Camelia Dumitru"
+		id="ADD-DATE-VERIFIED-COLUMN-TO-EMAIL-TABLE">
+		<addColumn tableName="email">
+			<column name="date_verified" type="Timestamp">
+				<constraints nullable="true" />
+			</column>
+		</addColumn>
+	</changeSet>
+</databaseChangeLog>	

Original file line number	Diff line number	Diff line change
`@@ -123,7 +123,7 @@ public void addSourceToEmail(String sourceId, String email) {`
`123`	`123`	`@Transactional`
`124`	`124`	`@UpdateProfileLastModified`
`125`	`125`	`public boolean verifyEmail(String email) {`
`126`		`- Query query = entityManager.createNativeQuery("update email set is_verified = true, is_current=true, last_modified=now() where trim(lower(email)) = trim(lower(:email))");`
	`126`	`+ Query query = entityManager.createNativeQuery("update email set is_verified = true, is_current=true, last_modified=now(), date_verified=now() where trim(lower(email)) = trim(lower(:email))");`
`127`	`127`	`query.setParameter("email", email);`
`128`	`128`	`return query.executeUpdate() > 0;`
`129`	`129`	`}`