Newer
Older
package it.inaf.ia2.gms.persistence;
import it.inaf.ia2.gms.model.Permission;
import it.inaf.ia2.gms.persistence.model.InvitedRegistration;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Optional;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
@Component
public class InvitedRegistrationDAO {
private final JdbcTemplate jdbcTemplate;
@Autowired
public InvitedRegistrationDAO(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Transactional
public void addInvitedRegistration(InvitedRegistration invitedRegistration) {
String sqlReq = "INSERT INTO invited_registration_request (id, token_hash, email) VALUES (?, ?, ?)";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sqlReq);
ps.setString(1, invitedRegistration.getId());
ps.setString(2, invitedRegistration.getTokenHash());
ps.setObject(3, invitedRegistration.getEmail());
return ps;
});
for (Map.Entry<String, Permission> entry : invitedRegistration.getGroupsPermissions().entrySet()) {
String sqlReqGroup = "INSERT INTO invited_registration_request_group (request_id, group_id, permission) VALUES (?, ?, ?)";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sqlReqGroup);
ps.setString(1, invitedRegistration.getId());
ps.setString(2, entry.getKey());
ps.setObject(3, entry.getValue().toString(), Types.OTHER);
return ps;
});
}
}
public Optional<InvitedRegistration> getInvitedRegistrationFromToken(String tokenHash) {
String sqlReq = "SELECT id, email, creation_time FROM invited_registration_request WHERE token_hash = ? AND done IS NOT true";
InvitedRegistration registration = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sqlReq);
ps.setString(1, tokenHash);
return ps;
}, resultSet -> {
if (resultSet.next()) {
InvitedRegistration reg = new InvitedRegistration();
reg.setId(resultSet.getString("id"));
reg.setEmail(resultSet.getString("email"));
reg.setCreationTime(new Date(resultSet.getDate("creation_time").getTime()));
return reg;
}
return null;
});
if (registration != null) {
String sqlReqGroup = "SELECT group_id, permission FROM invited_registration_request_group WHERE request_id = ?";
Map<String, Permission> groupsPermissions = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sqlReqGroup);
ps.setString(1, registration.getId());
return ps;
}, resultSet -> {
Map<String, Permission> map = new HashMap<>();
while (resultSet.next()) {
String groupId = resultSet.getString("group_id");
Permission permission = Permission.valueOf(resultSet.getString("permission"));
map.put(groupId, permission);
registration.setGroupsPermissions(groupsPermissions);
}
return Optional.ofNullable(registration);
}
Sonia Zorba
committed
public void setRegistrationUser(InvitedRegistration invitedRegistration) {
Sonia Zorba
committed
String sql = "UPDATE invited_registration_request SET \"user\" = ? WHERE id = ?";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, invitedRegistration.getUserId());
ps.setString(2, invitedRegistration.getId());
Sonia Zorba
committed
public void setRegistrationDone(InvitedRegistration invitedRegistration) {
String sql = "UPDATE invited_registration_request SET done = true WHERE id = ?";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, invitedRegistration.getId());
return ps;
});
}
/**
* Called before deleting a group.
*/
public void deleteAllGroupsInvitedRegistrations(List<String> groupIds) {
if (groupIds.isEmpty()) {
return;
}
String sql = "DELETE FROM invited_registration_request_group WHERE group_id IN ("
+ String.join(",", groupIds.stream().map(g -> "?").collect(Collectors.toList()))
+ ")";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
int i = 0;
for (String groupId : groupIds) {
ps.setString(++i, groupId);
}
return ps;
});
// Cleanup orphan invited requests
jdbcTemplate.update("DELETE FROM invited_registration_request WHERE id NOT IN "
+ "(SELECT request_id FROM invited_registration_request_group)");
}
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
public List<InvitedRegistration> getPendingInvitedRegistrationsForGroup(String groupId) {
String sql = "SELECT id, email, creation_time, permission\n"
+ "FROM invited_registration_request r\n"
+ "JOIN invited_registration_request_group rg ON r.id = rg.request_id\n"
+ "WHERE done IS NOT TRUE AND rg.group_id = ?";
return jdbcTemplate.query(sql,
ps -> {
ps.setString(1, groupId);
},
rs -> {
// key: id
Map<String, InvitedRegistration> map = new HashMap<>();
while (rs.next()) {
String id = rs.getString("id");
InvitedRegistration reg = map.get(id);
if (reg == null) {
String email = rs.getString("email");
Date creationTime = new Date(rs.getDate("creation_time").getTime());
reg = new InvitedRegistration()
.setId(id)
.setEmail(email)
.setCreationTime(creationTime);
map.put(id, reg);
}
if (reg.getGroupsPermissions() == null) {
reg.setGroupsPermissions(new HashMap<>());
}
Permission permission = Permission.valueOf(rs.getString("permission"));
reg.getGroupsPermissions().put(groupId, permission);
}
List<InvitedRegistration> registrations = new ArrayList<>(map.values());
Collections.sort(registrations, (reg1, reg2) -> reg1.getEmail().compareToIgnoreCase(reg2.getEmail()));
return registrations;
});
}
public void deleteInvitedRegistrationRequest(String requestId, String groupId) {
String sql = "DELETE FROM invited_registration_request_group\n"
+ "WHERE request_id = ? AND group_id = ?";
jdbcTemplate.update(sql, ps -> {
ps.setString(1, requestId);
ps.setString(2, groupId);
});
// Cleanup orphan invited requests
jdbcTemplate.update("DELETE FROM invited_registration_request WHERE id NOT IN "
+ "(SELECT request_id FROM invited_registration_request_group)");
}