1
|
package eu.dnetlib.goldoa.service.dao;
|
2
|
|
3
|
import eu.dnetlib.goldoa.domain.*;
|
4
|
import org.springframework.beans.factory.annotation.Autowired;
|
5
|
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
|
6
|
import org.springframework.jdbc.core.JdbcTemplate;
|
7
|
import org.springframework.jdbc.core.PreparedStatementCreator;
|
8
|
import org.springframework.jdbc.core.PreparedStatementSetter;
|
9
|
import org.springframework.jdbc.core.RowMapper;
|
10
|
import org.springframework.transaction.annotation.Propagation;
|
11
|
import org.springframework.transaction.annotation.Transactional;
|
12
|
|
13
|
import javax.sql.DataSource;
|
14
|
import java.sql.Connection;
|
15
|
import java.sql.PreparedStatement;
|
16
|
import java.sql.ResultSet;
|
17
|
import java.sql.SQLException;
|
18
|
import java.sql.Timestamp;
|
19
|
import java.sql.Types;
|
20
|
import java.util.ArrayList;
|
21
|
import java.util.Date;
|
22
|
import java.util.List;
|
23
|
|
24
|
/**
|
25
|
* Created by antleb on 3/13/15.
|
26
|
*/
|
27
|
public class PersonDAO {
|
28
|
|
29
|
@Autowired
|
30
|
private DataSource dataSource;
|
31
|
|
32
|
private static final String GET_BY_EMAIL =
|
33
|
"select email, firstname, lastname, initials, password, telephone, active, orcidid, array_agg(distinct pro.id) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
|
34
|
"from person p\n" +
|
35
|
"left join person_role pr on p.email = pr.person\n" +
|
36
|
"left join role r on r.id = pr.role\n" +
|
37
|
"left join affiliation a on a.person = p.email\n" +
|
38
|
"left join organisation o on o.id = a.organization\n" +
|
39
|
"left join project pro on pro.coordinator=p.email\n" +
|
40
|
"where email = ?\n" +
|
41
|
"group by p.email, firstname, lastname, initials, password, telephone, active, orcidid\n";
|
42
|
|
43
|
private static final String INSERT_PERSON = "insert into person (email, firstname, lastname, initials, password, telephone, orcidid) values (?, ?, ?, ?, md5(?), ?, ?);";
|
44
|
|
45
|
private static final String UPDATE_PROJECT_COORDINATOR = "update project set coordinator = ? where array[id]::text[] <@ ?::text[]";
|
46
|
|
47
|
private static final String INSERT_PERSON_ROLE = "insert into person_role (person, role, approved) values (?, ? ,?)";
|
48
|
|
49
|
private static final String INSERT_AFFILIATION = "insert into affiliation (person, organization, startdate, enddate, department) values (?, ?, ?, ?, ?)";
|
50
|
|
51
|
private static final String INSERT_ACTIVATION_TOKEN = "insert into account_action (\"user\", type, token, date, expires) values (?, ?, ? , ?, ?)";
|
52
|
|
53
|
private static final String LOGIN = "select 1 from person where email = ? and password = md5(?)";
|
54
|
|
55
|
private static final String GET_PERSON_TOKEN = "select 1 from person p join account_action ac on ac.user=p.email where p.email=? and ac.token=?";
|
56
|
|
57
|
private static final String ACTIVATE_USER = "update person set active=true where email = ?";
|
58
|
|
59
|
private static final String DELETE_ACTIVATION_TOKEN = "delete from account_action where user=? and token=? and type='activation'";
|
60
|
|
61
|
private static final String UPDATE_PASSWORD = "update person set password=md5(?) where email=?";
|
62
|
|
63
|
private static final String UPDATE_PERSON = "update person set firstname=?, lastname=?, initials=?, password=?, telephone=?, orcidid=? where email=?";
|
64
|
|
65
|
/**
|
66
|
* Returns the person with the given email. Objects of different type are placeholders containing only their id.
|
67
|
* Objects representing relations are fully initialized, with placeholders for other main entities.
|
68
|
*
|
69
|
* @param email
|
70
|
* @return
|
71
|
*/
|
72
|
public Person getPerson(String email) {
|
73
|
return new JdbcTemplate(dataSource).queryForObject(GET_BY_EMAIL, new Object[]{email}, new int[]{Types.VARCHAR}, new RowMapper<Person>() {
|
74
|
@Override
|
75
|
public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
|
76
|
Person person = new Person();
|
77
|
|
78
|
person.setEmail(rs.getString("email"));
|
79
|
person.setName(rs.getString("firstname"));
|
80
|
person.setLastname(rs.getString("lastname"));
|
81
|
person.setInitials(rs.getString("initials"));
|
82
|
person.setTelephone(rs.getString("telephone"));
|
83
|
person.setActive(rs.getBoolean("active"));
|
84
|
person.setOrcidId(rs.getString("orcidid"));
|
85
|
person.setPassword(rs.getString("password"));
|
86
|
|
87
|
person.setCoordinatedProjects(new ArrayList<Project>());
|
88
|
for (String projectId : (String[]) rs.getArray("projects").getArray()) {
|
89
|
person.getCoordinatedProjects().add(new Project(projectId));
|
90
|
}
|
91
|
|
92
|
person.setAffiliations(new ArrayList<Affiliation>());
|
93
|
for (String affString : (String[]) rs.getArray("affiliations").getArray()) {
|
94
|
String[] parts = affString.split("\\|\\|");
|
95
|
String org = parts[0];
|
96
|
String dep = parts[1];
|
97
|
Date start = parts[2] == "0" ? null : new Date(Long.parseLong(parts[2]));
|
98
|
Date end = parts[3] == "0" ? null : new Date(Long.parseLong(parts[3]));
|
99
|
|
100
|
Affiliation affiliation = new Affiliation();
|
101
|
|
102
|
affiliation.setPerson(person);
|
103
|
affiliation.setOrganization(new Organization(org));
|
104
|
affiliation.setDepartment(dep);
|
105
|
affiliation.setStart(start);
|
106
|
affiliation.setEnd(end);
|
107
|
|
108
|
person.getAffiliations().add(affiliation);
|
109
|
}
|
110
|
|
111
|
person.setRoles(new ArrayList<PersonRole>());
|
112
|
for (String rString : (String[]) rs.getArray("roles").getArray()) {
|
113
|
String[] parts = rString.split("\\|\\|");
|
114
|
String role = parts[0];
|
115
|
boolean approved = Boolean.parseBoolean(parts[1]);
|
116
|
|
117
|
person.getRoles().add(new PersonRole(person, getRole(role), approved));
|
118
|
}
|
119
|
|
120
|
return person;
|
121
|
}
|
122
|
});
|
123
|
}
|
124
|
|
125
|
public void saveToken(final Person person, final String token, final int tokenTTL) {
|
126
|
new JdbcTemplate(dataSource).update(INSERT_ACTIVATION_TOKEN, new PreparedStatementSetter() {
|
127
|
@Override
|
128
|
public void setValues(PreparedStatement ps) throws SQLException {
|
129
|
|
130
|
ps.setString(1, person.getEmail());
|
131
|
ps.setString(2, "activation");
|
132
|
ps.setString(3, token);
|
133
|
ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
|
134
|
ps.setTimestamp(5, new Timestamp(System.currentTimeMillis() + 1000L*60L*60L*24L*tokenTTL));
|
135
|
}
|
136
|
});
|
137
|
}
|
138
|
|
139
|
public boolean verifyLogin(String email, String password) {
|
140
|
return new JdbcTemplate(dataSource).query(LOGIN, new Object[]{email, password}, new int[]{Types.VARCHAR, Types.VARCHAR}, new RowMapper<String>() {
|
141
|
@Override
|
142
|
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
|
143
|
return "1";
|
144
|
}
|
145
|
}).size() > 0;
|
146
|
}
|
147
|
|
148
|
public boolean verifyToken(final String email, final String token) {
|
149
|
return new JdbcTemplate(dataSource).query(GET_PERSON_TOKEN, new Object[]{email, token}, new int[]{Types.VARCHAR, Types.VARCHAR}, new RowMapper<Object>() {
|
150
|
@Override
|
151
|
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
|
152
|
return 1;
|
153
|
}
|
154
|
}).size() > 0;
|
155
|
}
|
156
|
|
157
|
public void activateUser(final String email) {
|
158
|
new JdbcTemplate(dataSource).update(ACTIVATE_USER, new PreparedStatementSetter() {
|
159
|
@Override
|
160
|
public void setValues(PreparedStatement ps) throws SQLException {
|
161
|
ps.setString(1, email);
|
162
|
}
|
163
|
});
|
164
|
}
|
165
|
|
166
|
public Role getRole(final String roleId) {
|
167
|
return new JdbcTemplate(dataSource).queryForObject("select id, name from role where id = ?", new Object[]{roleId}, new int[]{Types.VARCHAR}, new RowMapper<Role>() {
|
168
|
@Override
|
169
|
public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
|
170
|
return new Role(rs.getString("id"), rs.getString("name"));
|
171
|
}
|
172
|
});
|
173
|
}
|
174
|
|
175
|
public void deleteToken(final String email, final String token) {
|
176
|
new JdbcTemplate(dataSource).update(DELETE_ACTIVATION_TOKEN, new PreparedStatementSetter() {
|
177
|
@Override
|
178
|
public void setValues(PreparedStatement ps) throws SQLException {
|
179
|
ps.setString(1, email);
|
180
|
ps.setString(2, token);
|
181
|
}
|
182
|
});
|
183
|
}
|
184
|
|
185
|
public boolean updatePassword(final String newPassword, final String email) {
|
186
|
return new JdbcTemplate(dataSource).update(UPDATE_PASSWORD, new PreparedStatementSetter() {
|
187
|
@Override
|
188
|
public void setValues(PreparedStatement ps) throws SQLException {
|
189
|
ps.setString(1, newPassword);
|
190
|
ps.setString(2, email);
|
191
|
}
|
192
|
}) == 0;
|
193
|
}
|
194
|
|
195
|
public List<Role> getRoles() {
|
196
|
return new JdbcTemplate(dataSource).query("select id, name from role", new Object[]{}, new int[]{}, new RowMapper<Role>() {
|
197
|
@Override
|
198
|
public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
|
199
|
return new Role(rs.getString("id"), rs.getString("name"));
|
200
|
}
|
201
|
});
|
202
|
}
|
203
|
|
204
|
public void savePerson(final Person person) {
|
205
|
new JdbcTemplate(dataSource).update(INSERT_PERSON, new PreparedStatementSetter() {
|
206
|
@Override
|
207
|
|
208
|
// email, firstname, lastname, initials, password, telephone, active, orcidid
|
209
|
public void setValues(PreparedStatement ps) throws SQLException {
|
210
|
ps.setString(1, person.getEmail());
|
211
|
ps.setString(2, person.getName());
|
212
|
ps.setString(3, person.getLastname());
|
213
|
ps.setString(4, person.getInitials());
|
214
|
ps.setString(5, person.getPassword());
|
215
|
ps.setString(6, person.getTelephone());
|
216
|
ps.setString(7, person.getOrcidId());
|
217
|
}
|
218
|
});
|
219
|
}
|
220
|
|
221
|
public void updatePerson(final Person person) {
|
222
|
new JdbcTemplate(dataSource).update(UPDATE_PERSON, new PreparedStatementSetter() {
|
223
|
@Override
|
224
|
public void setValues(PreparedStatement ps) throws SQLException {
|
225
|
ps.setString(1, person.getName());
|
226
|
ps.setString(2, person.getLastname());
|
227
|
ps.setString(3, person.getInitials());
|
228
|
ps.setString(4, person.getPassword());
|
229
|
ps.setString(5, person.getTelephone());
|
230
|
ps.setString(6, person.getOrcidId());
|
231
|
ps.setString(7, person.getEmail());
|
232
|
}
|
233
|
});
|
234
|
}
|
235
|
|
236
|
public void deleteAffiliations(final String email) {
|
237
|
new JdbcTemplate(dataSource).update("delete from affiliation where person=?", new PreparedStatementSetter() {
|
238
|
@Override
|
239
|
public void setValues(PreparedStatement preparedStatement) throws SQLException {
|
240
|
preparedStatement.setString(1, email);
|
241
|
}
|
242
|
});
|
243
|
}
|
244
|
|
245
|
public void deletePersonRoles(final String email) {
|
246
|
new JdbcTemplate(dataSource).update("delete from person_role where person=?", new PreparedStatementSetter() {
|
247
|
@Override
|
248
|
public void setValues(PreparedStatement preparedStatement) throws SQLException {
|
249
|
preparedStatement.setString(1, email);
|
250
|
}
|
251
|
});
|
252
|
}
|
253
|
|
254
|
//TODO MOVE TO PROJECT DAO
|
255
|
public void removeProjectCoordinators(final String email) {
|
256
|
new JdbcTemplate(dataSource).update("update project set coordinator=null where coordinator=?", new PreparedStatementSetter() {
|
257
|
@Override
|
258
|
public void setValues(PreparedStatement preparedStatement) throws SQLException {
|
259
|
preparedStatement.setString(1, email);
|
260
|
}
|
261
|
});
|
262
|
}
|
263
|
|
264
|
public void saveProjectCoordinators(final String email, final List<String> projectIds) {
|
265
|
new JdbcTemplate(dataSource).update(new PreparedStatementCreator() {
|
266
|
@Override
|
267
|
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
|
268
|
PreparedStatement ps = con.prepareStatement(UPDATE_PROJECT_COORDINATOR);
|
269
|
|
270
|
ps.setString(1, email);
|
271
|
ps.setArray(2, con.createArrayOf("text", projectIds.toArray()));
|
272
|
|
273
|
return ps;
|
274
|
}
|
275
|
});
|
276
|
}
|
277
|
|
278
|
public void savePersonRoles(final String email, final List<PersonRole> roles) {
|
279
|
new JdbcTemplate(dataSource).batchUpdate(INSERT_PERSON_ROLE, new BatchPreparedStatementSetter() {
|
280
|
@Override
|
281
|
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
282
|
PersonRole pr = roles.get(i);
|
283
|
|
284
|
ps.setString(1, email);
|
285
|
ps.setString(2, pr.getRole().getId());
|
286
|
ps.setBoolean(3, pr.isApproved());
|
287
|
}
|
288
|
|
289
|
@Override
|
290
|
public int getBatchSize() {
|
291
|
return roles!=null?roles.size():0;
|
292
|
}
|
293
|
});
|
294
|
}
|
295
|
|
296
|
public void saveAffiliations(final List<Affiliation> affiliations) {
|
297
|
new JdbcTemplate(dataSource).batchUpdate(INSERT_AFFILIATION, new BatchPreparedStatementSetter() {
|
298
|
@Override
|
299
|
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
300
|
Affiliation affiliation = affiliations.get(i);
|
301
|
|
302
|
ps.setString(1, affiliation.getPerson().getEmail());
|
303
|
|
304
|
if (affiliation.getOrganization() != null)
|
305
|
ps.setString(2, affiliation.getOrganization().getId());
|
306
|
else
|
307
|
ps.setString(2, null);
|
308
|
|
309
|
if (affiliation.getStart() != null)
|
310
|
ps.setTimestamp(3, new Timestamp(affiliation.getStart().getTime()));
|
311
|
else
|
312
|
ps.setTimestamp(3, null);
|
313
|
|
314
|
if (affiliation.getEnd() != null)
|
315
|
ps.setTimestamp(4, new Timestamp(affiliation.getEnd().getTime()));
|
316
|
else
|
317
|
ps.setTimestamp(4, null);
|
318
|
|
319
|
ps.setString(5, affiliation.getDepartment());
|
320
|
}
|
321
|
|
322
|
@Override
|
323
|
public int getBatchSize() {
|
324
|
return affiliations!=null?affiliations.size():0;
|
325
|
}
|
326
|
});
|
327
|
}
|
328
|
|
329
|
public DataSource getDataSource() {
|
330
|
return dataSource;
|
331
|
}
|
332
|
|
333
|
public void setDataSource(DataSource dataSource) {
|
334
|
this.dataSource = dataSource;
|
335
|
}
|
336
|
}
|