#6182

Committed 08 Jul 2025 07:04PM UTC coverage: 79.433% (+0.03%) from 79.402%

Build # #6182

Build Type

push

Committed by

Commit Message

DT-1934: Add Institution Domains (#2599)

Coverage Stats

109 of 119 new or added lines in 8 files covered. (91.6%)

10459 of 13167 relevant lines covered (79.43%)

0.79 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

98.44

/src/main/java/org/broadinstitute/consent/http/db/InstitutionDAO.java

package org.broadinstitute.consent.http.db;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicReference;
import org.broadinstitute.consent.http.db.mapper.InstitutionMapper;
import org.broadinstitute.consent.http.db.mapper.InstitutionReducer;
import org.broadinstitute.consent.http.db.mapper.InstitutionWithUsersReducer;
import org.broadinstitute.consent.http.models.Institution;
import org.broadinstitute.consent.http.models.User;
import org.broadinstitute.consent.http.service.UserService.SimplifiedUser;
import org.jdbi.v3.sqlobject.config.RegisterBeanMapper;
import org.jdbi.v3.sqlobject.config.RegisterRowMapper;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.statement.GetGeneratedKeys;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;
import org.jdbi.v3.sqlobject.statement.UseRowReducer;
import org.jdbi.v3.sqlobject.transaction.Transactional;

@RegisterRowMapper(InstitutionMapper.class)
public interface InstitutionDAO extends Transactional<InstitutionDAO> {

  @SqlUpdate("""
      INSERT INTO institution
              (institution_name, it_director_name, it_director_email, institution_url,
               duns_number, org_chart_url, verification_url, verification_filename,
               organization_type, create_user, create_date)
      VALUES (:institutionName, :itDirectorName, :itDirectorEmail, :institutionUrl,
              :dunsNumber, :orgChartUrl, :verificationUrl, :verificationFilename,
              :organizationType, :createUser, :createDate)
      """)
  @GetGeneratedKeys
  Integer insertInstitution(@Bind("institutionName") String institutionName,
      @Bind("itDirectorName") String itDirectorName,
      @Bind("itDirectorEmail") String itDirectorEmail,
      @Bind("institutionUrl") String institutionUrl,
      @Bind("dunsNumber") Integer dunsNumber,
      @Bind("orgChartUrl") String orgChartUrl,
      @Bind("verificationUrl") String verificationUrl,
      @Bind("verificationFilename") String verificationFilename,
      @Bind("organizationType") String organizationType,
      @Bind("createUser") Integer createUser,
      @Bind("createDate") Date createDate);

  /**
   * Inserts a full institution record, including domains, into the database. This is the preferred
   * method for inserting institutions as it handles all necessary fields and relationships.
   *
   * @param institution Populated Institution object containing all necessary fields.
   * @param userId The create_user id
   * @return The inserted Institution object
   * @throws SQLException The exception thrown if the insert fails.
   */
  default Institution insertFullInstitution(Institution institution, Integer userId) throws SQLException {
    Date now = new Date();
    AtomicReference<Integer> institutionId = new AtomicReference<>();
    getHandle().useTransaction(handle -> {
      handle.getConnection().setAutoCommit(false);
      Integer id = insertInstitution(
          institution.getName(),
          institution.getItDirectorName(),
          institution.getItDirectorEmail(),
          institution.getInstitutionUrl(),
          institution.getDunsNumber(),
          institution.getOrgChartUrl(),
          institution.getVerificationUrl(),
          institution.getVerificationFilename(),
          (Objects.nonNull(institution.getOrganizationType()) ? institution.getOrganizationType()
              .getValue() : null),
          userId,
          now);
      if (institution.getDomains() != null) {
        String insertDomainQuery = """
            INSERT INTO institution_domains (institution_id, domain) VALUES (:institutionId, :domain)
            """;
        institution.getDomains().forEach(domain -> handle.createUpdate(insertDomainQuery)
            .bind("institutionId", id)
            .bind("domain", domain)
            .execute());
      }
      handle.commit();
      institutionId.set(id);
    });
    if (institutionId.get() != null) {
      return findInstitutionById(institutionId.get());
    }
    throw new SQLException("Failed to insert institution");
  }

  @SqlUpdate("""
      UPDATE institution
      SET
        institution_name = :institutionName,
        it_director_name = :itDirectorName,
        it_director_email = :itDirectorEmail,
        institution_url = :institutionUrl,
        duns_number = :dunsNumber,
        org_chart_url = :orgChartUrl,
        verification_url = :verificationUrl,
        verification_filename = :verificationFilename,
        organization_type = :organizationType,
        update_user = :updateUser,
        update_date = :updateDate
      WHERE institution_id = :institutionId
      """)
  void updateInstitutionById(@Bind("institutionId") Integer institutionId,
      @Bind("institutionName") String institutionName,
      @Bind("itDirectorName") String itDirectorName,
      @Bind("itDirectorEmail") String itDirectorEmail,
      @Bind("institutionUrl") String institutionUrl,
      @Bind("dunsNumber") Integer dunsNumber,
      @Bind("orgChartUrl") String orgChartUrl,
      @Bind("verificationUrl") String verificationUrl,
      @Bind("verificationFilename") String verificationFilename,
      @Bind("organizationType") String organizationType,
      @Bind("updateUser") Integer updateUser,
      @Bind("updateDate") Date updateDate);

  /**
   * Updates all fields of an institution, including its domains. This is the preferred method for
   * updating institutions as it handles all necessary fields and relationships.
   *
   * @param institution The institution to update
   * @param userId The update_user_id
   * @return The updated Institution object
   */
  default Institution updateFullInstitution(Institution institution, Integer userId)
      throws SQLException {
    Date now = new Date();
    Integer institutionId = institution.getId();
    getHandle().useTransaction(handle -> {
      handle.getConnection().setAutoCommit(false);
      updateInstitutionById(
          institution.getId(),
          institution.getName(),
          institution.getItDirectorName(),
          institution.getItDirectorEmail(),
          institution.getInstitutionUrl(),
          institution.getDunsNumber(),
          institution.getOrgChartUrl(),
          institution.getVerificationUrl(),
          institution.getVerificationFilename(),
          (Objects.nonNull(institution.getOrganizationType()) ? institution.getOrganizationType()
              .getValue() : null),
          userId,
          now);
      handle.createUpdate("DELETE FROM institution_domains WHERE institution_id = :institutionId")
          .bind("institutionId", institutionId)
          .execute();
      if (institution.getDomains() != null) {
        String insertDomainQuery = """
            INSERT INTO institution_domains (institution_id, domain) VALUES (:institutionId, :domain)
            """;
        institution.getDomains().forEach(domain -> handle.createUpdate(insertDomainQuery)
            .bind("institutionId", institutionId)
            .bind("domain", domain)
            .execute());
      }
      handle.commit();
    });
    return findInstitutionById(institutionId);
  }

  @SqlUpdate("DELETE FROM institution WHERE institution_id = :institutionId")
  void deleteInstitutionById(@Bind("institutionId") Integer institutionId);

  @UseRowReducer(InstitutionReducer.class)
  @SqlQuery("""
      SELECT i.*, d.domain
      FROM institution i
      LEFT JOIN institution_domains d on d.institution_id = i.institution_id
      WHERE i.institution_id = :institutionId
    """)
  Institution findInstitutionById(@Bind("institutionId") Integer institutionId);

  @UseRowReducer(InstitutionReducer.class)
  @SqlQuery("""
      SELECT i.*, d.domain
      FROM institution i
      LEFT JOIN institution_domains d on d.institution_id = i.institution_id
      WHERE LOWER(TRIM(i.institution_name)) = LOWER(TRIM(:name))
    """)
  List<Institution> findInstitutionsByName(@Bind("name") String name);

  @RegisterBeanMapper(value = User.class, prefix = "u")
  @RegisterBeanMapper(value = SimplifiedUser.class, prefix = "so")
  @UseRowReducer(InstitutionWithUsersReducer.class)
  @SqlQuery("""
      SELECT i.*,
        d.domain,
        u.user_id AS u_user_id, u.email AS u_email,
        u.display_name AS u_display_name, u.create_date AS u_create_date,
        u.email_preference AS u_email_preference,
        u.era_commons_id AS u_era_commons_id,
        u2.user_id AS u2_user_id, u2.email AS u2_email,
        u2.display_name AS u2_display_name, u2.create_date AS u2_create_date,
        u2.email_preference AS u2_email_preference,
        u2.era_commons_id AS u2_era_commons_id,
        so.so_user_id, so.so_email, so.so_display_name
      FROM institution i
      LEFT JOIN users u ON u.user_id = i.create_user
      LEFT JOIN users u2 ON u2.user_id = i.update_user
      LEFT JOIN institution_domains d on d.institution_id = i.institution_id
      LEFT JOIN
        (SELECT
            so.user_id AS so_user_id, so.email AS so_email,
            so.display_name AS so_display_name, so.institution_id AS so_institution_id
            FROM users so
            LEFT JOIN user_role ur ON ur.user_id = so.user_id
            WHERE ur.role_id = 7) so ON i.institution_id = so.so_institution_id
      ORDER BY LOWER(i.institution_name)
      """)
  List<Institution> findAllInstitutions();

  @RegisterBeanMapper(value = User.class, prefix = "u")
  @RegisterBeanMapper(value = SimplifiedUser.class, prefix = "so")
  @UseRowReducer(InstitutionWithUsersReducer.class)
  @SqlQuery("""
      SELECT i.*,
        d.domain,
        u.user_id AS u_user_id,
        u.email AS u_email,
        u.display_name AS u_display_name,
        u.create_date AS u_create_date,
        u.email_preference AS u_email_preference,
        u.era_commons_id AS u_era_commons_id,
        u2.user_id AS u2_user_id, u2.email AS u2_email,
        u2.display_name AS u2_display_name, u2.create_date AS u2_create_date,
        u2.email_preference AS u2_email_preference,
        u2.era_commons_id AS u2_era_commons_id,
        so.so_user_id, so.so_email, so.so_display_name
      FROM institution i
      LEFT JOIN users u ON u.user_id = i.create_user
      LEFT JOIN users u2 ON u2.user_id = i.update_user
      LEFT JOIN institution_domains d on d.institution_id = i.institution_id
      LEFT JOIN
           (SELECT
               so.user_id AS so_user_id, so.email AS so_email,
               so.display_name AS so_display_name, so.institution_id AS so_institution_id
               FROM users so
               LEFT JOIN user_role ur ON ur.user_id = so.user_id
               WHERE ur.role_id = 7) so ON i.institution_id = so.so_institution_id
      WHERE i.institution_id = :institutionId
      """)
  Institution findInstitutionWithSOById(@Bind("institutionId") Integer institutionId);

  default void deleteAllInstitutionsByUser(@Bind("userId") Integer userId) throws SQLException {
    final String domainDeleteQuery = """
        DELETE FROM institution_domains
        WHERE institution_id IN (SELECT institution_id FROM institution WHERE create_user = :userId OR update_user = :userId)
        """;
    final String institutionDeleteQuery = """
        DELETE FROM institution WHERE create_user = :userId OR update_user = :userId
        """;
    getHandle().useTransaction(handle -> {
      handle.getConnection().setAutoCommit(false);
      handle.createUpdate(domainDeleteQuery).bind("userId", userId).execute();
      handle.createUpdate(institutionDeleteQuery).bind("userId", userId).execute();
      handle.commit();
    });
  }
}

1	package org.broadinstitute.consent.http.db;
2
3	import java.sql.SQLException;
4	import java.util.Date;
5	import java.util.List;
6	import java.util.Objects;
7	import java.util.concurrent.atomic.AtomicReference;
8	import org.broadinstitute.consent.http.db.mapper.InstitutionMapper;
9	import org.broadinstitute.consent.http.db.mapper.InstitutionReducer;
10	import org.broadinstitute.consent.http.db.mapper.InstitutionWithUsersReducer;
11	import org.broadinstitute.consent.http.models.Institution;
12	import org.broadinstitute.consent.http.models.User;
13	import org.broadinstitute.consent.http.service.UserService.SimplifiedUser;
14	import org.jdbi.v3.sqlobject.config.RegisterBeanMapper;
15	import org.jdbi.v3.sqlobject.config.RegisterRowMapper;
16	import org.jdbi.v3.sqlobject.customizer.Bind;
17	import org.jdbi.v3.sqlobject.statement.GetGeneratedKeys;
18	import org.jdbi.v3.sqlobject.statement.SqlQuery;
19	import org.jdbi.v3.sqlobject.statement.SqlUpdate;
20	import org.jdbi.v3.sqlobject.statement.UseRowReducer;
21	import org.jdbi.v3.sqlobject.transaction.Transactional;
22
23	@RegisterRowMapper(InstitutionMapper.class)
24	public interface InstitutionDAO extends Transactional<InstitutionDAO> {
25
26	@SqlUpdate("""
27	INSERT INTO institution
28	(institution_name, it_director_name, it_director_email, institution_url,
29	duns_number, org_chart_url, verification_url, verification_filename,
30	organization_type, create_user, create_date)
31	VALUES (:institutionName, :itDirectorName, :itDirectorEmail, :institutionUrl,
32	:dunsNumber, :orgChartUrl, :verificationUrl, :verificationFilename,
33	:organizationType, :createUser, :createDate)
34	""")
35	@GetGeneratedKeys
36	Integer insertInstitution(@Bind("institutionName") String institutionName,
37	@Bind("itDirectorName") String itDirectorName,
38	@Bind("itDirectorEmail") String itDirectorEmail,
39	@Bind("institutionUrl") String institutionUrl,
40	@Bind("dunsNumber") Integer dunsNumber,
41	@Bind("orgChartUrl") String orgChartUrl,
42	@Bind("verificationUrl") String verificationUrl,
43	@Bind("verificationFilename") String verificationFilename,
44	@Bind("organizationType") String organizationType,
45	@Bind("createUser") Integer createUser,
46	@Bind("createDate") Date createDate);
47
48	/**
49	* Inserts a full institution record, including domains, into the database. This is the preferred
50	* method for inserting institutions as it handles all necessary fields and relationships.
51	*
52	* @param institution Populated Institution object containing all necessary fields.
53	* @param userId The create_user id
54	* @return The inserted Institution object
55	* @throws SQLException The exception thrown if the insert fails.
56	*/
57	default Institution insertFullInstitution(Institution institution, Integer userId) throws SQLException {
58	Date now = new Date();	1✔
59	AtomicReference<Integer> institutionId = new AtomicReference<>();	1✔
60	getHandle().useTransaction(handle -> {	1✔
61	handle.getConnection().setAutoCommit(false);	1✔
62	Integer id = insertInstitution(	1✔
63	institution.getName(),	1✔
64	institution.getItDirectorName(),	1✔
65	institution.getItDirectorEmail(),	1✔
66	institution.getInstitutionUrl(),	1✔
67	institution.getDunsNumber(),	1✔
68	institution.getOrgChartUrl(),	1✔
69	institution.getVerificationUrl(),	1✔
70	institution.getVerificationFilename(),	1✔
71	(Objects.nonNull(institution.getOrganizationType()) ? institution.getOrganizationType()	1✔
72	.getValue() : null),	1✔
73	userId,
74	now);
75	if (institution.getDomains() != null) {	1✔
76	String insertDomainQuery = """	1✔
77	INSERT INTO institution_domains (institution_id, domain) VALUES (:institutionId, :domain)
78	""";
79	institution.getDomains().forEach(domain -> handle.createUpdate(insertDomainQuery)	1✔
80	.bind("institutionId", id)	1✔
81	.bind("domain", domain)	1✔
82	.execute());	1✔
83	}
84	handle.commit();	1✔
85	institutionId.set(id);	1✔
86	});	1✔
87	if (institutionId.get() != null) {	1✔
88	return findInstitutionById(institutionId.get());	1✔
89	}
NEW 90	throw new SQLException("Failed to insert institution");	×
91	}
92
93	@SqlUpdate("""
94	UPDATE institution
95	SET
96	institution_name = :institutionName,
97	it_director_name = :itDirectorName,
98	it_director_email = :itDirectorEmail,
99	institution_url = :institutionUrl,
100	duns_number = :dunsNumber,
101	org_chart_url = :orgChartUrl,
102	verification_url = :verificationUrl,
103	verification_filename = :verificationFilename,
104	organization_type = :organizationType,
105	update_user = :updateUser,
106	update_date = :updateDate
107	WHERE institution_id = :institutionId
108	""")
109	void updateInstitutionById(@Bind("institutionId") Integer institutionId,
110	@Bind("institutionName") String institutionName,
111	@Bind("itDirectorName") String itDirectorName,
112	@Bind("itDirectorEmail") String itDirectorEmail,
113	@Bind("institutionUrl") String institutionUrl,
114	@Bind("dunsNumber") Integer dunsNumber,
115	@Bind("orgChartUrl") String orgChartUrl,
116	@Bind("verificationUrl") String verificationUrl,
117	@Bind("verificationFilename") String verificationFilename,
118	@Bind("organizationType") String organizationType,
119	@Bind("updateUser") Integer updateUser,
120	@Bind("updateDate") Date updateDate);
121
122	/**
123	* Updates all fields of an institution, including its domains. This is the preferred method for
124	* updating institutions as it handles all necessary fields and relationships.
125	*
126	* @param institution The institution to update
127	* @param userId The update_user_id
128	* @return The updated Institution object
129	*/
130	default Institution updateFullInstitution(Institution institution, Integer userId)
131	throws SQLException {
132	Date now = new Date();	1✔
133	Integer institutionId = institution.getId();	1✔
134	getHandle().useTransaction(handle -> {	1✔
135	handle.getConnection().setAutoCommit(false);	1✔
136	updateInstitutionById(	1✔
137	institution.getId(),	1✔
138	institution.getName(),	1✔
139	institution.getItDirectorName(),	1✔
140	institution.getItDirectorEmail(),	1✔
141	institution.getInstitutionUrl(),	1✔
142	institution.getDunsNumber(),	1✔
143	institution.getOrgChartUrl(),	1✔
144	institution.getVerificationUrl(),	1✔
145	institution.getVerificationFilename(),	1✔
146	(Objects.nonNull(institution.getOrganizationType()) ? institution.getOrganizationType()	1✔
147	.getValue() : null),	1✔
148	userId,
149	now);
150	handle.createUpdate("DELETE FROM institution_domains WHERE institution_id = :institutionId")	1✔
151	.bind("institutionId", institutionId)	1✔
152	.execute();	1✔
153	if (institution.getDomains() != null) {	1✔
154	String insertDomainQuery = """	1✔
155	INSERT INTO institution_domains (institution_id, domain) VALUES (:institutionId, :domain)
156	""";
157	institution.getDomains().forEach(domain -> handle.createUpdate(insertDomainQuery)	1✔
158	.bind("institutionId", institutionId)	1✔
159	.bind("domain", domain)	1✔
160	.execute());	1✔
161	}
162	handle.commit();	1✔
163	});	1✔
164	return findInstitutionById(institutionId);	1✔
165	}
166
167	@SqlUpdate("DELETE FROM institution WHERE institution_id = :institutionId")
168	void deleteInstitutionById(@Bind("institutionId") Integer institutionId);
169
170	@UseRowReducer(InstitutionReducer.class)
171	@SqlQuery("""
172	SELECT i.*, d.domain
173	FROM institution i
174	LEFT JOIN institution_domains d on d.institution_id = i.institution_id
175	WHERE i.institution_id = :institutionId
176	""")
177	Institution findInstitutionById(@Bind("institutionId") Integer institutionId);
178
179	@UseRowReducer(InstitutionReducer.class)
180	@SqlQuery("""
181	SELECT i.*, d.domain
182	FROM institution i
183	LEFT JOIN institution_domains d on d.institution_id = i.institution_id
184	WHERE LOWER(TRIM(i.institution_name)) = LOWER(TRIM(:name))
185	""")
186	List<Institution> findInstitutionsByName(@Bind("name") String name);
187
188	@RegisterBeanMapper(value = User.class, prefix = "u")
189	@RegisterBeanMapper(value = SimplifiedUser.class, prefix = "so")
190	@UseRowReducer(InstitutionWithUsersReducer.class)
191	@SqlQuery("""
192	SELECT i.*,
193	d.domain,
194	u.user_id AS u_user_id, u.email AS u_email,
195	u.display_name AS u_display_name, u.create_date AS u_create_date,
196	u.email_preference AS u_email_preference,
197	u.era_commons_id AS u_era_commons_id,
198	u2.user_id AS u2_user_id, u2.email AS u2_email,
199	u2.display_name AS u2_display_name, u2.create_date AS u2_create_date,
200	u2.email_preference AS u2_email_preference,
201	u2.era_commons_id AS u2_era_commons_id,
202	so.so_user_id, so.so_email, so.so_display_name
203	FROM institution i
204	LEFT JOIN users u ON u.user_id = i.create_user
205	LEFT JOIN users u2 ON u2.user_id = i.update_user
206	LEFT JOIN institution_domains d on d.institution_id = i.institution_id
207	LEFT JOIN
208	(SELECT
209	so.user_id AS so_user_id, so.email AS so_email,
210	so.display_name AS so_display_name, so.institution_id AS so_institution_id
211	FROM users so
212	LEFT JOIN user_role ur ON ur.user_id = so.user_id
213	WHERE ur.role_id = 7) so ON i.institution_id = so.so_institution_id
214	ORDER BY LOWER(i.institution_name)
215	""")
216	List<Institution> findAllInstitutions();
217
218	@RegisterBeanMapper(value = User.class, prefix = "u")
219	@RegisterBeanMapper(value = SimplifiedUser.class, prefix = "so")
220	@UseRowReducer(InstitutionWithUsersReducer.class)
221	@SqlQuery("""
222	SELECT i.*,
223	d.domain,
224	u.user_id AS u_user_id,
225	u.email AS u_email,
226	u.display_name AS u_display_name,
227	u.create_date AS u_create_date,
228	u.email_preference AS u_email_preference,
229	u.era_commons_id AS u_era_commons_id,
230	u2.user_id AS u2_user_id, u2.email AS u2_email,
231	u2.display_name AS u2_display_name, u2.create_date AS u2_create_date,
232	u2.email_preference AS u2_email_preference,
233	u2.era_commons_id AS u2_era_commons_id,
234	so.so_user_id, so.so_email, so.so_display_name
235	FROM institution i
236	LEFT JOIN users u ON u.user_id = i.create_user
237	LEFT JOIN users u2 ON u2.user_id = i.update_user
238	LEFT JOIN institution_domains d on d.institution_id = i.institution_id
239	LEFT JOIN
240	(SELECT
241	so.user_id AS so_user_id, so.email AS so_email,
242	so.display_name AS so_display_name, so.institution_id AS so_institution_id
243	FROM users so
244	LEFT JOIN user_role ur ON ur.user_id = so.user_id
245	WHERE ur.role_id = 7) so ON i.institution_id = so.so_institution_id
246	WHERE i.institution_id = :institutionId
247	""")
248	Institution findInstitutionWithSOById(@Bind("institutionId") Integer institutionId);
249
250	default void deleteAllInstitutionsByUser(@Bind("userId") Integer userId) throws SQLException {
251	final String domainDeleteQuery = """	1✔
252	DELETE FROM institution_domains
253	WHERE institution_id IN (SELECT institution_id FROM institution WHERE create_user = :userId OR update_user = :userId)
254	""";
255	final String institutionDeleteQuery = """	1✔
256	DELETE FROM institution WHERE create_user = :userId OR update_user = :userId
257	""";
258	getHandle().useTransaction(handle -> {	1✔
259	handle.getConnection().setAutoCommit(false);	1✔
260	handle.createUpdate(domainDeleteQuery).bind("userId", userId).execute();	1✔
261	handle.createUpdate(institutionDeleteQuery).bind("userId", userId).execute();	1✔
262	handle.commit();	1✔
263	});	1✔
264	}	1✔
265	}

DataBiosphere / consent / #6182

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous