|
1
|
package eu.dnetlib.validator.commons.dao.jobs;
|
|
2
|
|
|
3
|
import java.sql.Array;
|
|
4
|
import java.sql.Connection;
|
|
5
|
import java.sql.PreparedStatement;
|
|
6
|
import java.sql.ResultSet;
|
|
7
|
import java.sql.SQLException;
|
|
8
|
import java.text.SimpleDateFormat;
|
|
9
|
import java.util.ArrayList;
|
|
10
|
import java.util.Arrays;
|
|
11
|
import java.util.Calendar;
|
|
12
|
import java.util.Date;
|
|
13
|
import java.util.HashMap;
|
|
14
|
import java.util.HashSet;
|
|
15
|
import java.util.List;
|
|
16
|
import java.util.Map;
|
|
17
|
import java.util.Map.Entry;
|
|
18
|
import java.util.Set;
|
|
19
|
import java.util.stream.Collectors;
|
|
20
|
|
|
21
|
import eu.dnetlib.domain.functionality.validator.JobForValidation;
|
|
22
|
import eu.dnetlib.domain.functionality.validator.JobResultEntry;
|
|
23
|
import eu.dnetlib.domain.functionality.validator.StoredJob;
|
|
24
|
import eu.dnetlib.validator.commons.dao.AbstractDAO;
|
|
25
|
import eu.dnetlib.validator.commons.dao.DaoException;
|
|
26
|
import eu.dnetlib.validator.commons.dao.Utilities;
|
|
27
|
import eu.dnetlib.validator.commons.dao.rules.RuleStatus;
|
|
28
|
|
|
29
|
public class JobsDAOImpl extends AbstractDAO<StoredJob> implements JobsDAO {
|
|
30
|
|
|
31
|
@Override
|
|
32
|
public Integer save(StoredJob job) throws DaoException {
|
|
33
|
Connection con = null;
|
|
34
|
PreparedStatement stmt = null;
|
|
35
|
Integer retId = -1;
|
|
36
|
logger.debug("Accessing DB to save/update Job");
|
|
37
|
try {
|
|
38
|
logger.debug("Accessing DB to update Job");
|
|
39
|
Calendar cal = Calendar.getInstance();
|
|
40
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
|
41
|
|
|
42
|
con = getConnection();
|
|
43
|
logger.debug("getting submittedjob updateStatement");
|
|
44
|
String query="UPDATE jobs SET validation_type=?, started=to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS'), guidelines=?, content_job_status=?, repo=?, duration=? WHERE id=?";
|
|
45
|
|
|
46
|
stmt = con.prepareStatement(query);
|
|
47
|
stmt.setString(1, job.getValidationType());
|
|
48
|
stmt.setString(2, sdf.format(cal.getTime()));
|
|
49
|
stmt.setString(3, job.getDesiredCompatibilityLevel());
|
|
50
|
stmt.setString(4, job.getContentJobStatus());
|
|
51
|
stmt.setString(5, job.getBaseUrl());
|
|
52
|
stmt.setString(6, job.getDuration());
|
|
53
|
stmt.setInt(7, job.getId());
|
|
54
|
|
|
55
|
if (stmt.executeUpdate() == 0) {
|
|
56
|
stmt.close();
|
|
57
|
logger.debug("Accessing DB to save Job");
|
|
58
|
query="INSERT INTO jobs(validation_type,started,guidelines,user_email,content_job_status, usage_job_status, repo, duration, rules, records, set, groupby_xpath, metadata_prefix, job_type) VALUES(?,to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS'),?,?,?,?,?,?,?,?,?,?,?,?)";
|
|
59
|
stmt = con.prepareStatement(query);
|
|
60
|
stmt.setString(1, job.getValidationType());
|
|
61
|
stmt.setString(2, sdf.format(cal.getTime()));
|
|
62
|
// stmt.setTimestamp(2, getCurrentTimeStamp());
|
|
63
|
stmt.setString(3, job.getDesiredCompatibilityLevel());
|
|
64
|
stmt.setString(4, job.getUserEmail());
|
|
65
|
stmt.setString(5, job.getContentJobStatus());
|
|
66
|
stmt.setString(6, job.getUsageJobStatus());
|
|
67
|
stmt.setString(7, job.getBaseUrl());
|
|
68
|
stmt.setString(8, job.getDuration());
|
|
69
|
stmt.setString(9, Utilities.convertSetToString(job.getRules()));
|
|
70
|
stmt.setString(10, Integer.toString(job.getRecords()));
|
|
71
|
stmt.setString(11, job.getValidationSet());
|
|
72
|
stmt.setString(12, job.getGroupByXpath());
|
|
73
|
stmt.setString(13, job.getMetadataPrefix());
|
|
74
|
stmt.setString(14, job.getJobType());
|
|
75
|
stmt.executeUpdate();
|
|
76
|
retId = this.getLastId();
|
|
77
|
} else {
|
|
78
|
logger.debug("Accessing DB to update job-done");
|
|
79
|
retId=job.getId();
|
|
80
|
}
|
|
81
|
|
|
82
|
if (job.isRegistration()) {
|
|
83
|
this.storeJobForRegistration(job, retId);
|
|
84
|
}
|
|
85
|
if (job.isCris()) {
|
|
86
|
this.storeJobForCris(job, retId);
|
|
87
|
}
|
|
88
|
stmt.close();
|
|
89
|
|
|
90
|
} catch (Exception e) {
|
|
91
|
logger.error("Error accessing DB to get save/update Rule.", e);
|
|
92
|
throw new DaoException(e);
|
|
93
|
} finally {
|
|
94
|
if (stmt != null) {
|
|
95
|
try {
|
|
96
|
stmt.close();
|
|
97
|
} catch (SQLException e) {
|
|
98
|
logger.error("Error accessing DB to get save/update Rule.", e);
|
|
99
|
throw new DaoException(e);
|
|
100
|
}
|
|
101
|
}
|
|
102
|
closeConnection(con);
|
|
103
|
}
|
|
104
|
return retId;
|
|
105
|
}
|
|
106
|
|
|
107
|
@Override
|
|
108
|
protected PreparedStatement getDeleteStatement(int id, Connection con) throws SQLException {
|
|
109
|
String query="DELETE FROM jobs " + " WHERE id=?";
|
|
110
|
PreparedStatement stmt = con.prepareStatement(query);
|
|
111
|
stmt.setInt(1, id);
|
|
112
|
return stmt;
|
|
113
|
}
|
|
114
|
|
|
115
|
@Override
|
|
116
|
public StoredJob get(int id) throws DaoException {
|
|
117
|
ResultSet rs = null;
|
|
118
|
Connection con = null;
|
|
119
|
PreparedStatement stmt = null;
|
|
120
|
StoredJob retJob = null;
|
|
121
|
logger.debug("Accessing DB to get Submitted Job");
|
|
122
|
try {
|
|
123
|
con = getConnection();
|
|
124
|
String query="SELECT j.validation_type, j.content_job_status, j.started, j.ended, j.content_job_score, j.user_email, j.repo, j.duration, rs.short_name, j.error_information, j.groupby_xpath, j.set, j.records, j.metadata_prefix, j.job_type, j.usage_job_status, j.usage_job_score, j.records_tested, j.rules, j.guidelines FROM jobs j, rulesets rs WHERE j.guidelines = rs.guidelines_acronym AND j.id=?";
|
|
125
|
stmt = con.prepareStatement(query);
|
|
126
|
stmt.setInt(1, id);
|
|
127
|
rs = stmt.executeQuery();
|
|
128
|
if (rs!=null){
|
|
129
|
retJob = new StoredJob();
|
|
130
|
while (rs.next()) {
|
|
131
|
retJob.setValidationType(rs.getString(1));
|
|
132
|
retJob.setContentJobStatus(rs.getString(2));
|
|
133
|
retJob.setUsageJobStatus(rs.getString(16));
|
|
134
|
retJob.setStarted(rs.getString(3));
|
|
135
|
retJob.setEnded(rs.getString(4));
|
|
136
|
retJob.setContentJobScore(rs.getInt(5));
|
|
137
|
retJob.setUsageJobScore(rs.getInt(17));
|
|
138
|
retJob.setUserEmail(rs.getString(6));
|
|
139
|
retJob.setBaseUrl(rs.getString(7));
|
|
140
|
retJob.setDuration(rs.getString(8));
|
|
141
|
retJob.setGuidelinesShortName(rs.getString(9));
|
|
142
|
retJob.setDesiredCompatibilityLevel(rs.getString(20));
|
|
143
|
retJob.setError(rs.getString(10));
|
|
144
|
retJob.setGroupByXpath(rs.getString(11));
|
|
145
|
retJob.setValidationSet(rs.getString(12));
|
|
146
|
retJob.setRecords(Integer.parseInt(rs.getString(13)));
|
|
147
|
retJob.setMetadataPrefix(rs.getString(14));
|
|
148
|
retJob.setJobType(rs.getString(15));
|
|
149
|
retJob.setRecordsTested(rs.getInt(18));
|
|
150
|
retJob.setRules(Utilities.convertStringToSet(rs.getString(19)));
|
|
151
|
retJob.setId(id);
|
|
152
|
}
|
|
153
|
if (retJob.getJobType().equals("Registration Request")) {
|
|
154
|
retJob.setRegistration(true);
|
|
155
|
this.getJobForRegistration(retJob);
|
|
156
|
}
|
|
157
|
if (retJob.getDesiredCompatibilityLevel().contains("cris")) {
|
|
158
|
retJob.setCris(true);
|
|
159
|
this.getJobForCris(retJob);
|
|
160
|
}
|
|
161
|
}
|
|
162
|
|
|
163
|
|
|
164
|
} catch (Exception e) {
|
|
165
|
logger.error("Error while accessing DB to get Submitted Job.", e);
|
|
166
|
throw new DaoException(e);
|
|
167
|
} finally {
|
|
168
|
if (stmt != null) {
|
|
169
|
try {
|
|
170
|
stmt.close();
|
|
171
|
} catch (SQLException e) {
|
|
172
|
logger.error("Error while accessing DB to get Submitted Job.", e);
|
|
173
|
throw new DaoException(e);
|
|
174
|
}
|
|
175
|
}
|
|
176
|
closeConnection(con);
|
|
177
|
}
|
|
178
|
return retJob;
|
|
179
|
|
|
180
|
}
|
|
181
|
|
|
182
|
@Override
|
|
183
|
public void importOldJobs() throws DaoException {
|
|
184
|
Map<Integer,Integer> idsMap = new HashMap<Integer, Integer>();
|
|
185
|
List<StoredJob> oldJobs = this.getOldJobs(idsMap);
|
|
186
|
this.updateOldJobResults(idsMap);
|
|
187
|
this.insertJobsBatch(oldJobs);
|
|
188
|
this.updateNeededTables();
|
|
189
|
}
|
|
190
|
|
|
191
|
private void updateNeededTables() throws DaoException {
|
|
192
|
Connection con = null;
|
|
193
|
PreparedStatement stmt = null;
|
|
194
|
logger.debug("Accessing DB to update other tables");
|
|
195
|
try {
|
|
196
|
con = getConnection();
|
|
197
|
String query="INSERT INTO job_results (SELECT * FROM job_results_old WHERE job_id IN (SELECT j1.id as id FROM jobs_old j1, jobs_old j2 "
|
|
198
|
+ "WHERE j1.validation_type = 'OAI Content' AND j2.validation_type = 'OAI Usage' AND j2.id = (j1.id+1) AND j1.repo = j2.repo AND j1.guidelines = j2.guidelines AND j1.activation_id = j2.activation_id AND j1.user = j2.user "
|
|
199
|
+ "ORDER BY j1.id));";
|
|
200
|
stmt = con.prepareStatement(query);
|
|
201
|
stmt.executeUpdate();
|
|
202
|
|
|
203
|
stmt.close();
|
|
204
|
query="INSERT INTO tasks (SELECT * FROM tasks_old WHERE job_id IN (SELECT j1.id as id FROM jobs_old j1, jobs_old j2 "
|
|
205
|
+ "WHERE j1.validation_type = 'OAI Content' AND j2.validation_type = 'OAI Usage' AND j2.id = (j1.id+1) AND j1.repo = j2.repo AND j1.guidelines = j2.guidelines AND j1.activation_id = j2.activation_id AND j1.user = j2.user "
|
|
206
|
+ "ORDER BY j1.id));";
|
|
207
|
stmt = con.prepareStatement(query);
|
|
208
|
stmt.executeUpdate();
|
|
209
|
|
|
210
|
stmt.close();
|
|
211
|
query="INSERT INTO jobs_filtered_scores (SELECT * FROM jobs_filtered_scores_old WHERE job_id IN (SELECT j1.id as id FROM jobs_old j1, jobs_old j2 "
|
|
212
|
+ "WHERE j1.validation_type = 'OAI Content' AND j2.validation_type = 'OAI Usage' AND j2.id = (j1.id+1) AND j1.repo = j2.repo AND j1.guidelines = j2.guidelines AND j1.activation_id = j2.activation_id AND j1.user = j2.user "
|
|
213
|
+ "ORDER BY j1.id));";
|
|
214
|
stmt = con.prepareStatement(query);
|
|
215
|
stmt.executeUpdate();
|
|
216
|
|
|
217
|
|
|
218
|
} catch (Exception e) {
|
|
219
|
logger.error("Error while accessing DB to update jobs results batch.", e);
|
|
220
|
throw new DaoException(e);
|
|
221
|
} finally {
|
|
222
|
if (stmt != null) {
|
|
223
|
try {
|
|
224
|
stmt.close();
|
|
225
|
} catch (SQLException e) {
|
|
226
|
logger.error("Error while accessing DB to update jobs results batch.", e);
|
|
227
|
throw new DaoException(e);
|
|
228
|
}
|
|
229
|
}
|
|
230
|
closeConnection(con);
|
|
231
|
}
|
|
232
|
|
|
233
|
}
|
|
234
|
private void updateOldJobResults(Map<Integer, Integer> idsMap) throws DaoException {
|
|
235
|
Connection con = null;
|
|
236
|
PreparedStatement stmt = null;
|
|
237
|
logger.debug("Accessing DB to update jobs results batch");
|
|
238
|
try {
|
|
239
|
con = getConnection();
|
|
240
|
String query="UPDATE job_results_old SET job_id = ? WHERE job_id = ?";
|
|
241
|
stmt = con.prepareStatement(query);
|
|
242
|
for (Map.Entry<Integer, Integer> entry : idsMap.entrySet()) {
|
|
243
|
stmt.setInt(1, entry.getValue());
|
|
244
|
stmt.setInt(2, entry.getKey());
|
|
245
|
stmt.addBatch();
|
|
246
|
}
|
|
247
|
stmt.executeBatch();
|
|
248
|
|
|
249
|
} catch (Exception e) {
|
|
250
|
logger.error("Error while accessing DB to update jobs results batch.", e);
|
|
251
|
throw new DaoException(e);
|
|
252
|
} finally {
|
|
253
|
if (stmt != null) {
|
|
254
|
try {
|
|
255
|
stmt.close();
|
|
256
|
} catch (SQLException e) {
|
|
257
|
logger.error("Error while accessing DB to update jobs results batch.", e);
|
|
258
|
throw new DaoException(e);
|
|
259
|
}
|
|
260
|
}
|
|
261
|
closeConnection(con);
|
|
262
|
}
|
|
263
|
|
|
264
|
}
|
|
265
|
|
|
266
|
private void insertJobsBatch(List<StoredJob> jobs) throws DaoException {
|
|
267
|
Connection con = null;
|
|
268
|
PreparedStatement stmt = null;
|
|
269
|
logger.debug("Accessing DB to insert jobs batch ");
|
|
270
|
try {
|
|
271
|
con = getConnection();
|
|
272
|
String query="INSERT INTO jobs(validation_type,started,ended,guidelines,user_email,content_job_status, usage_job_status, repo, duration, rules, records, records_tested, set, groupby_xpath, metadata_prefix, job_type, usage_job_score, content_job_score,id,error_information) VALUES(?,to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS'),to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
|
|
273
|
stmt = con.prepareStatement(query);
|
|
274
|
for (StoredJob job : jobs) {
|
|
275
|
stmt.setString(1, job.getValidationType());
|
|
276
|
stmt.setString(2, job.getStarted());
|
|
277
|
stmt.setString(3, job.getEnded());
|
|
278
|
stmt.setString(4, job.getDesiredCompatibilityLevel());
|
|
279
|
stmt.setString(5, job.getUserEmail());
|
|
280
|
stmt.setString(6, job.getContentJobStatus());
|
|
281
|
stmt.setString(7, job.getUsageJobStatus());
|
|
282
|
stmt.setString(8, job.getBaseUrl());
|
|
283
|
stmt.setString(9, job.getDuration());
|
|
284
|
stmt.setString(10, Utilities.convertSetToString(job.getRules()));
|
|
285
|
stmt.setInt(11, job.getRecords());
|
|
286
|
stmt.setInt(12, job.getRecordsTested());
|
|
287
|
stmt.setString(13, job.getValidationSet());
|
|
288
|
stmt.setString(14, job.getGroupByXpath());
|
|
289
|
stmt.setString(15, job.getMetadataPrefix());
|
|
290
|
stmt.setString(16, job.getJobType());
|
|
291
|
stmt.setInt(17, job.getUsageJobScore());
|
|
292
|
stmt.setInt(18, job.getContentJobScore());
|
|
293
|
stmt.setInt(19, job.getId());
|
|
294
|
stmt.setString(20, job.getError());
|
|
295
|
stmt.addBatch();
|
|
296
|
}
|
|
297
|
stmt.executeBatch();
|
|
298
|
|
|
299
|
} catch (Exception e) {
|
|
300
|
logger.error("Error while accessing DB to insert jobs batch.", e);
|
|
301
|
throw new DaoException(e);
|
|
302
|
} finally {
|
|
303
|
if (stmt != null) {
|
|
304
|
try {
|
|
305
|
stmt.close();
|
|
306
|
} catch (SQLException e) {
|
|
307
|
logger.error("Error while accessing DB to insert jobs batch.", e);
|
|
308
|
throw new DaoException(e);
|
|
309
|
}
|
|
310
|
}
|
|
311
|
closeConnection(con);
|
|
312
|
}
|
|
313
|
}
|
|
314
|
private List<StoredJob> getOldJobs(Map<Integer, Integer> idsMap) throws DaoException{
|
|
315
|
|
|
316
|
ResultSet rs = null;
|
|
317
|
Connection con = null;
|
|
318
|
PreparedStatement stmt = null;
|
|
319
|
List<StoredJob> retList = new ArrayList<StoredJob>();
|
|
320
|
logger.debug("Accessing DB to get old jobs..");
|
|
321
|
try {
|
|
322
|
con = getConnection();
|
|
323
|
String query= "SELECT j1.id as id, j2.id as id_to_update,'CU' as validation_type, j1.score as content_job_score, j2.score as usage_job_score, j1.status as content_job_status, j2.status as usage_job_status, j1.guidelines as guidelines, j1.started as started, j2.started,j1.ended as ended, j1.user as user_email, j1.repo as repo, j1.duration as duration, j1.set as set, j1.groupby_xpath as grouby_xpath, j1.metadata_prefix as metadata_prefix, j1.job_type as job_type, j1.records as records, j1.error_information as error_information, concat_ws(',', j1.rules, j2.rules) as rules, (select total from job_results_old where job_id = j1.id ORDER BY job_id DESC LIMIT 1 ) as records_tested"
|
|
324
|
+ " FROM jobs_old j1, jobs_old j2"
|
|
325
|
+ " WHERE j1.validation_type = 'OAI Content' AND j2.validation_type = 'OAI Usage' AND j2.id = (j1.id+1) AND j1.repo = j2.repo AND j1.guidelines = j2.guidelines AND j1.activation_id = j2.activation_id AND j1.user = j2.user"
|
|
326
|
+ " ORDER BY j1.id;";
|
|
327
|
stmt = con.prepareStatement(query);
|
|
328
|
|
|
329
|
rs = stmt.executeQuery();
|
|
330
|
while (rs.next()) {
|
|
331
|
StoredJob retJob = new StoredJob();
|
|
332
|
retJob.setValidationType(rs.getString("validation_type"));
|
|
333
|
retJob.setContentJobStatus(rs.getString("content_job_status"));
|
|
334
|
retJob.setUsageJobStatus(rs.getString("usage_job_status"));
|
|
335
|
retJob.setStarted(rs.getString("started"));
|
|
336
|
retJob.setEnded(rs.getString("ended"));
|
|
337
|
retJob.setContentJobScore(rs.getInt("content_job_score"));
|
|
338
|
retJob.setUsageJobScore(rs.getInt("usage_job_score"));
|
|
339
|
retJob.setUserEmail(rs.getString("user_email"));
|
|
340
|
retJob.setBaseUrl(rs.getString("repo"));
|
|
341
|
retJob.setDuration(rs.getString("duration"));
|
|
342
|
retJob.setDesiredCompatibilityLevel(rs.getString("guidelines"));
|
|
343
|
retJob.setId(rs.getInt("id"));
|
|
344
|
retJob.setError(rs.getString("error_information"));
|
|
345
|
retJob.setJobType(rs.getString("job_type"));
|
|
346
|
retJob.setMetadataPrefix(rs.getString("metadata_prefix"));
|
|
347
|
retJob.setRules(Utilities.convertStringToSet(rs.getString("rules")));
|
|
348
|
retJob.setRecordsTested(rs.getInt("records_tested"));
|
|
349
|
retJob.setValidationSet(rs.getString("set"));
|
|
350
|
if (retJob.getContentJobScore() == 0)
|
|
351
|
retJob.setRecordsTested(0);
|
|
352
|
retJob.setRecords(rs.getInt("records"));
|
|
353
|
if (retJob.getJobType().equals("Registration Request")) {
|
|
354
|
retJob.setRegistration(true);
|
|
355
|
// this.getJobForRegistration(retJob);
|
|
356
|
}
|
|
357
|
idsMap.put(rs.getInt("id_to_update"),rs.getInt("id"));
|
|
358
|
retList.add(retJob);
|
|
359
|
}
|
|
360
|
|
|
361
|
} catch (Exception e) {
|
|
362
|
logger.error("Error while accessing DB to .", e);
|
|
363
|
throw new DaoException(e);
|
|
364
|
} finally {
|
|
365
|
if (stmt != null) {
|
|
366
|
try {
|
|
367
|
stmt.close();
|
|
368
|
} catch (SQLException e) {
|
|
369
|
logger.error("Error while accessing DB to get Submitted Jobs of user.", e);
|
|
370
|
throw new DaoException(e);
|
|
371
|
}
|
|
372
|
}
|
|
373
|
closeConnection(con);
|
|
374
|
}
|
|
375
|
return retList;
|
|
376
|
}
|
|
377
|
|
|
378
|
|
|
379
|
@Override
|
|
380
|
public List<StoredJob> getJobs(String userName, String jobType, Integer offset, Integer limit, String dateFrom, String dateTo) throws DaoException {
|
|
381
|
return this.getJobs(userName, jobType, offset, limit, dateFrom, dateTo, null);
|
|
382
|
}
|
|
383
|
|
|
384
|
@Override
|
|
385
|
public List<StoredJob> getJobs(String userName, String jobType, Integer offset, Integer limit, String dateFrom, String dateTo, String validationStatus) throws DaoException {
|
|
386
|
ResultSet rs = null;
|
|
387
|
Connection con = null;
|
|
388
|
PreparedStatement stmt = null;
|
|
389
|
List<StoredJob> retList = new ArrayList<StoredJob>();
|
|
390
|
logger.debug("Accessing DB to get Submitted Jobs of user: "+userName + " and type: " + jobType);
|
|
391
|
try {
|
|
392
|
con = getConnection();
|
|
393
|
String beginQuery="SELECT j.validation_type, j.content_job_status, j.usage_job_status, j.content_job_score, j.usage_job_score, j.started, j.ended, j.user_email, j.repo, j.duration, r.short_name, j.guidelines, j.id, j.error_information, j.job_type, j.records_tested FROM jobs j, rulesets r WHERE j.guidelines = r.guidelines_acronym";
|
|
394
|
String endQuery=" ORDER BY j.id DESC";
|
|
395
|
if (userName != null) {
|
|
396
|
beginQuery += " AND j.user_email=?";
|
|
397
|
}
|
|
398
|
if (jobType != null) {
|
|
399
|
beginQuery += " AND j.job_type=?";
|
|
400
|
}
|
|
401
|
if (dateFrom != null && dateTo != null) {
|
|
402
|
beginQuery += " AND j.started BETWEEN date(?) AND date(?)";
|
|
403
|
}
|
|
404
|
if (validationStatus != null) {
|
|
405
|
if (validationStatus.equalsIgnoreCase("ongoing"))
|
|
406
|
beginQuery += " AND (j.content_job_status='ongoing' OR j.usage_job_status='ongoing')";
|
|
407
|
else if (validationStatus.equalsIgnoreCase("successful"))
|
|
408
|
beginQuery += " AND ((j.validation_type='CU' AND j.content_job_status='finished' AND j.usage_job_status='finished' AND j.content_job_score::integer > '50' AND j.usage_job_score::integer > '50')" +
|
|
409
|
" OR (j.validation_type='C' AND j.content_job_status='finished' AND j.usage_job_status='none' AND j.content_job_score::integer > '50')" +
|
|
410
|
" OR (j.validation_type='U' AND j.content_job_status='none' AND j.usage_job_status='finished' AND j.usage_job_score::integer > '50'))";
|
|
411
|
else if (validationStatus.equalsIgnoreCase("failed"))
|
|
412
|
beginQuery += " AND ((j.validation_type='CU' AND j.content_job_status='finished' AND j.usage_job_status='finished' AND (j.content_job_score::integer <= '50' OR j.usage_job_score::integer <= '50'))" +
|
|
413
|
" OR (j.validation_type='C' AND j.content_job_status='finished' AND j.usage_job_status='none' AND j.content_job_score::integer <= '50')" +
|
|
414
|
" OR (j.validation_type='U' AND j.content_job_status='none' AND j.usage_job_status='finished' AND j.usage_job_score::integer <= '50'))";
|
|
415
|
}
|
|
416
|
if (offset != null) {
|
|
417
|
endQuery += " OFFSET ?";
|
|
418
|
}
|
|
419
|
if (limit != null) {
|
|
420
|
endQuery += " LIMIT ?";
|
|
421
|
}
|
|
422
|
String finalQuery = beginQuery + endQuery;
|
|
423
|
logger.debug("finalQuery" + finalQuery);
|
|
424
|
stmt = con.prepareStatement(finalQuery);
|
|
425
|
int index = 1;
|
|
426
|
if (userName != null) {
|
|
427
|
stmt.setString(index++, userName);
|
|
428
|
}
|
|
429
|
if (jobType != null) {
|
|
430
|
stmt.setString(index++, jobType);
|
|
431
|
}
|
|
432
|
if ((dateFrom != null && dateTo != null)) {
|
|
433
|
stmt.setString(index++, dateFrom);
|
|
434
|
stmt.setString(index++, dateTo);
|
|
435
|
}
|
|
436
|
if (offset != null) {
|
|
437
|
stmt.setInt(index++, offset);
|
|
438
|
}
|
|
439
|
if (limit != null) {
|
|
440
|
stmt.setInt(index++, limit);
|
|
441
|
}
|
|
442
|
|
|
443
|
rs = stmt.executeQuery();
|
|
444
|
if (rs!=null){
|
|
445
|
while (rs.next()) {
|
|
446
|
StoredJob retJob = new StoredJob();
|
|
447
|
retJob.setValidationType(rs.getString("validation_type"));
|
|
448
|
retJob.setContentJobStatus(rs.getString("content_job_status"));
|
|
449
|
retJob.setUsageJobStatus(rs.getString("usage_job_status"));
|
|
450
|
retJob.setStarted(rs.getString("started"));
|
|
451
|
retJob.setEnded(rs.getString("ended"));
|
|
452
|
retJob.setContentJobScore(rs.getInt("content_job_score"));
|
|
453
|
retJob.setUsageJobScore(rs.getInt("usage_job_score"));
|
|
454
|
retJob.setUserEmail(rs.getString("user_email"));
|
|
455
|
retJob.setBaseUrl(rs.getString("repo"));
|
|
456
|
retJob.setDuration(rs.getString("duration"));
|
|
457
|
retJob.setGuidelinesShortName(rs.getString("short_name"));
|
|
458
|
retJob.setDesiredCompatibilityLevel(rs.getString("guidelines"));
|
|
459
|
retJob.setId(rs.getInt("id"));
|
|
460
|
retJob.setError(rs.getString("error_information"));
|
|
461
|
retJob.setJobType(rs.getString("job_type"));
|
|
462
|
retJob.setRecordsTested(rs.getInt("records_tested"));
|
|
463
|
if (retJob.getJobType().equals("Registration Request")) {
|
|
464
|
retJob.setRegistration(true);
|
|
465
|
this.getJobForRegistration(retJob);
|
|
466
|
}
|
|
467
|
if (retJob.getDesiredCompatibilityLevel().contains("cris")) {
|
|
468
|
retJob.setCris(true);
|
|
469
|
this.getJobForCris(retJob);
|
|
470
|
}
|
|
471
|
if (validationStatus != null) {
|
|
472
|
retJob.setValidationStatus(validationStatus);
|
|
473
|
} else {
|
|
474
|
if (retJob.getContentJobStatus().equals("ongoing") || retJob.getUsageJobStatus().equals("ongoing")) {
|
|
475
|
retJob.setValidationStatus("ongoing");
|
|
476
|
} else if ((retJob.getValidationType().equals("CU") && retJob.getContentJobStatus().equals("finished") && retJob.getUsageJobStatus().equals("finished") && retJob.getContentJobScore() > 50 && retJob.getUsageJobScore() > 50)
|
|
477
|
|| (retJob.getValidationType().equals("C") && retJob.getContentJobStatus().equals("finished") && retJob.getUsageJobStatus().equals("none") && retJob.getContentJobScore() > 50)
|
|
478
|
|| (retJob.getValidationType().equals("U") && retJob.getContentJobStatus().equals("none") && retJob.getUsageJobStatus().equals("finished") && retJob.getUsageJobScore() > 50)) {
|
|
479
|
retJob.setValidationStatus("successful");
|
|
480
|
} else if ((retJob.getValidationType().equals("CU") && retJob.getContentJobStatus().equals("finished") && retJob.getUsageJobStatus().equals("finished") && (retJob.getContentJobScore() <= 50 || retJob.getUsageJobScore() <= 50))
|
|
481
|
|| (retJob.getValidationType().equals("C") && retJob.getContentJobStatus().equals("finished") && retJob.getUsageJobStatus().equals("none") && retJob.getContentJobScore() <= 50)
|
|
482
|
|| (retJob.getValidationType().equals("U") && retJob.getContentJobStatus().equals("none") && retJob.getUsageJobStatus().equals("finished") && retJob.getUsageJobScore() <= 50) ) {
|
|
483
|
retJob.setValidationStatus("failed");
|
|
484
|
}
|
|
485
|
}
|
|
486
|
retList.add(retJob);
|
|
487
|
}
|
|
488
|
}
|
|
489
|
|
|
490
|
|
|
491
|
} catch (Exception e) {
|
|
492
|
logger.error("Error while accessing DB to .", e);
|
|
493
|
throw new DaoException(e);
|
|
494
|
} finally {
|
|
495
|
if (stmt != null) {
|
|
496
|
try {
|
|
497
|
stmt.close();
|
|
498
|
} catch (SQLException e) {
|
|
499
|
logger.error("Error while accessing DB to get Submitted Jobs of user.", e);
|
|
500
|
throw new DaoException(e);
|
|
501
|
}
|
|
502
|
}
|
|
503
|
closeConnection(con);
|
|
504
|
}
|
|
505
|
return retList;
|
|
506
|
}
|
|
507
|
|
|
508
|
@Override
|
|
509
|
public int getJobsTotalNumber(String userName, String jobType) throws DaoException {
|
|
510
|
return this.getJobsTotalNumber(userName, jobType, null);
|
|
511
|
}
|
|
512
|
|
|
513
|
@Override
|
|
514
|
public int getJobsTotalNumber(String userName, String jobType, String validationStatus) throws DaoException {
|
|
515
|
ResultSet rs = null;
|
|
516
|
Connection con = null;
|
|
517
|
PreparedStatement stmt = null;
|
|
518
|
int sum = 0;
|
|
519
|
logger.debug("Accessing DB to get total number of Jobs of user: "+userName + " and type: " + jobType);
|
|
520
|
try {
|
|
521
|
con = getConnection();
|
|
522
|
String query="SELECT count(*) as count FROM jobs j WHERE";
|
|
523
|
if (userName != null) {
|
|
524
|
query += " user_email=?";
|
|
525
|
}
|
|
526
|
|
|
527
|
if (jobType != null) {
|
|
528
|
if (userName != null)
|
|
529
|
query += " AND";
|
|
530
|
query += " job_type=?";
|
|
531
|
}
|
|
532
|
if (validationStatus != null) {
|
|
533
|
if ((userName != null) || (jobType != null))
|
|
534
|
query += " AND";
|
|
535
|
if (validationStatus.equalsIgnoreCase("ongoing"))
|
|
536
|
query += " (j.content_job_status='ongoing' OR j.usage_job_status='ongoing')";
|
|
537
|
else if (validationStatus.equalsIgnoreCase("successful"))
|
|
538
|
query += " ((j.validation_type='CU' AND j.content_job_status='finished' AND j.usage_job_status='finished' AND j.content_job_score::integer > '50' AND j.usage_job_score::integer > '50')" +
|
|
539
|
" OR (j.validation_type='C' AND j.content_job_status='finished' AND j.usage_job_status='none' AND j.content_job_score::integer > '50')" +
|
|
540
|
" OR (j.validation_type='U' AND j.content_job_status='none' AND j.usage_job_status='finished' AND j.usage_job_score::integer > '50'))";
|
|
541
|
else if (validationStatus.equalsIgnoreCase("failed"))
|
|
542
|
query += " ((j.validation_type='CU' AND j.content_job_status='finished' AND j.usage_job_status='finished' AND (j.content_job_score::integer <= '50' OR j.usage_job_score::integer <= '50'))" +
|
|
543
|
" OR (j.validation_type='C' AND j.content_job_status='finished' AND j.usage_job_status='none' AND j.content_job_score::integer <= '50')" +
|
|
544
|
" OR (j.validation_type='U' AND j.content_job_status='none' AND j.usage_job_status='finished' AND j.usage_job_score::integer <= '50'))";
|
|
545
|
}
|
|
546
|
logger.debug(query);
|
|
547
|
stmt = con.prepareStatement(query);
|
|
548
|
int index = 1;
|
|
549
|
if (userName != null) {
|
|
550
|
stmt.setString(index++, userName);
|
|
551
|
}
|
|
552
|
if (jobType != null) {
|
|
553
|
stmt.setString(index++, jobType);
|
|
554
|
}
|
|
555
|
rs = stmt.executeQuery();
|
|
556
|
if (rs!=null){
|
|
557
|
if (rs.next()) {
|
|
558
|
sum = rs.getInt("count");
|
|
559
|
}
|
|
560
|
}
|
|
561
|
|
|
562
|
} catch (Exception e) {
|
|
563
|
logger.error("Error while accessing DB to get total number of Jobs of user.", e);
|
|
564
|
throw new DaoException(e);
|
|
565
|
} finally {
|
|
566
|
if (stmt != null) {
|
|
567
|
try {
|
|
568
|
stmt.close();
|
|
569
|
} catch (SQLException e) {
|
|
570
|
logger.error("Error while accessing DB to get total number of Jobs of user.", e);
|
|
571
|
throw new DaoException(e);
|
|
572
|
}
|
|
573
|
}
|
|
574
|
closeConnection(con);
|
|
575
|
}
|
|
576
|
return sum;
|
|
577
|
}
|
|
578
|
|
|
579
|
|
|
580
|
@Override
|
|
581
|
public void setTotalJobFinished(int jobId, String error, Boolean failed) throws DaoException {
|
|
582
|
logger.debug("Accessing DB to set Total Submitted Job: "+jobId+" as finished");
|
|
583
|
|
|
584
|
Connection con = null;
|
|
585
|
PreparedStatement stmt = null;
|
|
586
|
StoredJob job = this.get(jobId);
|
|
587
|
Calendar cal = Calendar.getInstance();
|
|
588
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
|
589
|
|
|
590
|
try {
|
|
591
|
|
|
592
|
if (failed)
|
|
593
|
error = "Server responded with error while issuing the request to retrieve the records.";
|
|
594
|
con = getConnection();
|
|
595
|
String query="UPDATE jobs SET ended=to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS'), duration=?, error_information=? WHERE id=?";
|
|
596
|
stmt = con.prepareStatement(query);
|
|
597
|
|
|
598
|
String endedStr = sdf.format(cal.getTime());
|
|
599
|
Date started = sdf.parse(job.getStarted());
|
|
600
|
Date ended = sdf.parse(endedStr);
|
|
601
|
long diff = ended.getTime() - started.getTime();
|
|
602
|
Calendar cDiff = Calendar.getInstance();
|
|
603
|
cDiff.setTimeInMillis(diff);
|
|
604
|
long mills = cDiff.getTimeInMillis();
|
|
605
|
|
|
606
|
stmt.setString(1, endedStr);
|
|
607
|
stmt.setString(2, Utilities.formatTime(mills));
|
|
608
|
if (error!=null) {
|
|
609
|
// stmt.setString(1, "finished-failed");
|
|
610
|
stmt.setString(3, error);
|
|
611
|
// stmt.setString(4, "an error occured");
|
|
612
|
logger.debug("error: "+error);
|
|
613
|
} else {
|
|
614
|
stmt.setString(3, "no errors");
|
|
615
|
}
|
|
616
|
|
|
617
|
stmt.setInt(4, jobId);
|
|
618
|
|
|
619
|
stmt.executeUpdate();
|
|
620
|
stmt.close();
|
|
621
|
} catch (Exception e) {
|
|
622
|
logger.error("Error while accessing DB to set Submitted Job as finished.", e);
|
|
623
|
throw new DaoException(e);
|
|
624
|
} finally {
|
|
625
|
if (stmt != null) {
|
|
626
|
try {
|
|
627
|
stmt.close();
|
|
628
|
} catch (SQLException e) {
|
|
629
|
logger.error("Error while accessing DB to set Submitted Job as finished.", e);
|
|
630
|
throw new DaoException(e);
|
|
631
|
}
|
|
632
|
}
|
|
633
|
closeConnection(con);
|
|
634
|
}
|
|
635
|
}
|
|
636
|
|
|
637
|
@Override
|
|
638
|
public int setJobFinished(int jobId, Map<String,Map<Integer,RuleStatus>> scoreMapPerGroupBy, String error, Boolean failed, int objsValidated, String validationType) throws DaoException {
|
|
639
|
int retScore = 0;
|
|
640
|
Connection con = null;
|
|
641
|
PreparedStatement stmt = null, stmt1 = null, stmt2 = null;
|
|
642
|
|
|
643
|
logger.debug("Accessing DB to set Submitted Job: "+jobId+" as finished");
|
|
644
|
try {
|
|
645
|
if (failed)
|
|
646
|
error = "Server responded with error while issuing the request to retrieve the records.";
|
|
647
|
con = getConnection();
|
|
648
|
String query = null;
|
|
649
|
if (validationType.equalsIgnoreCase("content")) {
|
|
650
|
query="UPDATE jobs SET " + validationType + "_job_status=? , records_tested=? WHERE id=?";
|
|
651
|
stmt = con.prepareStatement(query);
|
|
652
|
stmt.setString(1, "finished");
|
|
653
|
stmt.setInt(2, objsValidated);
|
|
654
|
stmt.setInt(3, jobId);
|
|
655
|
}
|
|
656
|
else if (validationType.equalsIgnoreCase("usage")) {
|
|
657
|
query="UPDATE jobs SET " + validationType + "_job_status=? WHERE id=?";
|
|
658
|
stmt = con.prepareStatement(query);
|
|
659
|
stmt.setString(1, "finished");
|
|
660
|
stmt.setInt(2, jobId);
|
|
661
|
}
|
|
662
|
|
|
663
|
int res = stmt.executeUpdate();
|
|
664
|
stmt.close();
|
|
665
|
|
|
666
|
logger.debug("job lines updated: "+res + " for query: " +query);
|
|
667
|
|
|
668
|
if (error == null) {
|
|
669
|
logger.debug("Inserting job results..");
|
|
670
|
query="INSERT INTO job_results(rule_id,job_id,total,successes,groupby) VALUES(?,?,?,?,?)";
|
|
671
|
stmt2 = con.prepareStatement(query);
|
|
672
|
stmt1 = con.prepareStatement("INSERT INTO jobs_filtered_scores(job_id,groupby,score) VALUES(?,?,?)");
|
|
673
|
for (Entry<String, Map<Integer, RuleStatus>> entry : scoreMapPerGroupBy.entrySet()) {
|
|
674
|
logger.debug("| JOB_ID | RULEID | TOTAL | SUCCESS | GROUPBY |");
|
|
675
|
String groupBy = entry.getKey();
|
|
676
|
Map<Integer, RuleStatus> scoreMapPerRule = entry.getValue();
|
|
677
|
float score = 0;
|
|
678
|
float weights = 0 ;
|
|
679
|
for (Entry<Integer, RuleStatus> entry2 : scoreMapPerRule.entrySet()) {
|
|
680
|
Integer ruleId = entry2.getKey();
|
|
681
|
RuleStatus ruleSt = entry2.getValue();
|
|
682
|
float perc = 0;
|
|
683
|
if (ruleSt.isMandatory()) {
|
|
684
|
weights += ruleSt.getWeight();
|
|
685
|
perc=100*ruleSt.getSuccess()/ruleSt.getTotal();
|
|
686
|
score += perc * ruleSt.getWeight();
|
|
687
|
}
|
|
688
|
logger.debug("| " + jobId + " | " + ruleId + " | " + ruleSt.getTotal() + " | " + ruleSt.getSuccess() + " | " +groupBy + " |");
|
|
689
|
stmt2.setInt(1, ruleId);
|
|
690
|
stmt2.setInt(2, jobId);
|
|
691
|
stmt2.setInt(3, ruleSt.getTotal());
|
|
692
|
stmt2.setInt(4, ruleSt.getSuccess());
|
|
693
|
stmt2.setString(5, groupBy);
|
|
694
|
stmt2.addBatch();
|
|
695
|
}
|
|
696
|
score /= weights;
|
|
697
|
logger.debug("score: " + score);
|
|
698
|
logger.debug("scoreInt: " + (int)Math.ceil(score));
|
|
699
|
if (groupBy.equals("all")) {
|
|
700
|
query="UPDATE jobs SET " + validationType + "_job_score=? WHERE id=?";
|
|
701
|
stmt = con.prepareStatement(query);
|
|
702
|
stmt.setString(1, Integer.toString((int)Math.ceil(score)));
|
|
703
|
stmt.setInt(2, jobId);
|
|
704
|
if (stmt.executeUpdate()>0 )
|
|
705
|
logger.debug("Job scored successfully set with value: " + Integer.toString((int)Math.ceil(score)));
|
|
706
|
else
|
|
707
|
logger.debug("error while setting score");
|
|
708
|
stmt.close();
|
|
709
|
retScore = (int)Math.ceil(score);
|
|
710
|
} else {
|
|
711
|
stmt1.setInt(1, jobId);
|
|
712
|
stmt1.setString(2, groupBy );
|
|
713
|
stmt1.setInt(3, (int)Math.ceil(score));
|
|
714
|
stmt1.addBatch();
|
|
715
|
}
|
|
716
|
}
|
|
717
|
int result = stmt2.executeBatch().length;
|
|
718
|
logger.debug("job results inserted: "+result);
|
|
719
|
result = stmt1.executeBatch().length;
|
|
720
|
logger.debug("filtered scores inserted: "+result);
|
|
721
|
} else {
|
|
722
|
// logger.debug("errors..");
|
|
723
|
query="UPDATE jobs SET " + validationType + "_job_score=? WHERE id=?";
|
|
724
|
stmt2 = con.prepareStatement(query);
|
|
725
|
stmt2.setString(1, "0");
|
|
726
|
stmt2.setInt(2, jobId);
|
|
727
|
stmt2.executeUpdate();
|
|
728
|
stmt2.close();
|
|
729
|
retScore = 0;
|
|
730
|
}
|
|
731
|
} catch (Exception e) {
|
|
732
|
logger.error("Error while accessing DB to set Submitted Job as finished: ", e);
|
|
733
|
throw new DaoException(e);
|
|
734
|
} finally {
|
|
735
|
if (stmt != null) {
|
|
736
|
try {
|
|
737
|
stmt.close();
|
|
738
|
} catch (SQLException e) {
|
|
739
|
logger.error("Error while accessing DB to set Submitted Job as finished: ", e);
|
|
740
|
throw new DaoException(e);
|
|
741
|
}
|
|
742
|
}
|
|
743
|
if (stmt1 != null) {
|
|
744
|
try {
|
|
745
|
stmt1.close();
|
|
746
|
} catch (SQLException e) {
|
|
747
|
logger.error("Error while accessing DB to set Submitted Job as finished: ", e);
|
|
748
|
throw new DaoException(e);
|
|
749
|
}
|
|
750
|
}
|
|
751
|
if (stmt2 != null) {
|
|
752
|
try {
|
|
753
|
stmt2.close();
|
|
754
|
} catch (SQLException e) {
|
|
755
|
logger.error("Error while accessing DB to set Submitted Job as finished: ", e);
|
|
756
|
throw new DaoException(e);
|
|
757
|
}
|
|
758
|
}
|
|
759
|
|
|
760
|
closeConnection(con);
|
|
761
|
}
|
|
762
|
return retScore;
|
|
763
|
}
|
|
764
|
|
|
765
|
@Deprecated
|
|
766
|
public boolean getJobError(int jobId) throws DaoException {
|
|
767
|
ResultSet rs = null;
|
|
768
|
Connection con = null;
|
|
769
|
PreparedStatement stmt = null;
|
|
770
|
logger.debug("Accessing DB to see if a Submitted Job has an error: ");
|
|
771
|
try {
|
|
772
|
con = getConnection();
|
|
773
|
String query="SELECT error_information FROM jobs WHERE id=?";
|
|
774
|
stmt = con.prepareStatement(query);
|
|
775
|
stmt.setInt(1, jobId);
|
|
776
|
rs = stmt.executeQuery();
|
|
777
|
rs.next();
|
|
778
|
if (rs.getString(1) != null && rs.getString(1).equals("no errors"))
|
|
779
|
return false;
|
|
780
|
|
|
781
|
|
|
782
|
|
|
783
|
|
|
784
|
} catch (Exception e) {
|
|
785
|
logger.error("Error while accessing DB to see if a Submitted Job has an error.", e);
|
|
786
|
throw new DaoException(e);
|
|
787
|
} finally {
|
|
788
|
if (stmt != null) {
|
|
789
|
try {
|
|
790
|
stmt.close();
|
|
791
|
} catch (SQLException e) {
|
|
792
|
logger.error("Error while accessing DB to see if a Submitted Job has an error.", e);
|
|
793
|
throw new DaoException(e);
|
|
794
|
}
|
|
795
|
}
|
|
796
|
closeConnection(con);
|
|
797
|
}
|
|
798
|
return true;
|
|
799
|
}
|
|
800
|
|
|
801
|
@Override
|
|
802
|
protected int getLastId() throws DaoException {
|
|
803
|
ResultSet rs = null;
|
|
804
|
Connection con = null;
|
|
805
|
PreparedStatement stmt = null;
|
|
806
|
int retId = -1;
|
|
807
|
logger.debug("Accessing DB to get Submitted Job's next available id");
|
|
808
|
try {
|
|
809
|
con = getConnection();
|
|
810
|
String query="SELECT currval(pg_get_serial_sequence(?,?)) FROM jobs";
|
|
811
|
stmt = con.prepareStatement(query);
|
|
812
|
stmt.setString(1, "jobs");
|
|
813
|
stmt.setString(2, "id");
|
|
814
|
|
|
815
|
rs = stmt.executeQuery();
|
|
816
|
if (rs!=null){
|
|
817
|
rs.next();
|
|
818
|
retId=rs.getInt(1);
|
|
819
|
}
|
|
820
|
} catch (Exception e) {
|
|
821
|
logger.error("Error while accessing DB to get Submitted Job's next available id.", e);
|
|
822
|
throw new DaoException(e);
|
|
823
|
} finally {
|
|
824
|
if (stmt != null) {
|
|
825
|
try {
|
|
826
|
stmt.close();
|
|
827
|
} catch (SQLException e) {
|
|
828
|
logger.error("Error while accessing DB to get Submitted Job's next available id.", e);
|
|
829
|
throw new DaoException(e);
|
|
830
|
}
|
|
831
|
}
|
|
832
|
closeConnection(con);
|
|
833
|
}
|
|
834
|
return retId;
|
|
835
|
|
|
836
|
|
|
837
|
}
|
|
838
|
|
|
839
|
@Override
|
|
840
|
public int deleteOld(String date, String period, String jobType) throws DaoException {
|
|
841
|
int jobsDeleted = 0;
|
|
842
|
Connection con = null;
|
|
843
|
PreparedStatement stmt = null;
|
|
844
|
logger.debug("Accessing DB to delete old Jobs");
|
|
845
|
String interval = "<";
|
|
846
|
if (period.equalsIgnoreCase("older"))
|
|
847
|
interval = "<";
|
|
848
|
else if (period.equalsIgnoreCase("newer"))
|
|
849
|
interval = ">";
|
|
850
|
if (period.equalsIgnoreCase("exact"))
|
|
851
|
interval = "=";
|
|
852
|
|
|
853
|
try {
|
|
854
|
con = getConnection();
|
|
855
|
logger.debug("Deleting jobs..");
|
|
856
|
if (jobType != null) {
|
|
857
|
String query="DELETE FROM jobs WHERE date(started)" + interval + "to_timestamp(?, 'YYYY-MM-DD') AND job_type = ?";
|
|
858
|
stmt = con.prepareStatement(query);
|
|
859
|
stmt.setString(1, date);
|
|
860
|
stmt.setString(2, jobType);
|
|
861
|
} else {
|
|
862
|
String query="DELETE FROM jobs WHERE date(started)" + interval + "to_timestamp(?, 'YYYY-MM-DD')";
|
|
863
|
stmt = con.prepareStatement(query);
|
|
864
|
stmt.setString(1, date);
|
|
865
|
}
|
|
866
|
jobsDeleted = stmt.executeUpdate();
|
|
867
|
} catch (Exception e) {
|
|
868
|
logger.error("Error while Accessing DB to delete old Jobs.", e);
|
|
869
|
throw new DaoException(e);
|
|
870
|
} finally {
|
|
871
|
if (stmt != null) {
|
|
872
|
try {
|
|
873
|
stmt.close();
|
|
874
|
} catch (SQLException e) {
|
|
875
|
logger.error("Error while Accessing DB to delete old Jobs.", e);
|
|
876
|
throw new DaoException(e);
|
|
877
|
}
|
|
878
|
}
|
|
879
|
closeConnection(con);
|
|
880
|
}
|
|
881
|
return jobsDeleted;
|
|
882
|
}
|
|
883
|
|
|
884
|
@Override
|
|
885
|
public void setStatus(int jobId, String status, int recordsTested, String validationType) throws DaoException {
|
|
886
|
Connection con = null;
|
|
887
|
PreparedStatement stmt = null;
|
|
888
|
|
|
889
|
logger.debug("Accessing DB to set Submitted Job status");
|
|
890
|
try {
|
|
891
|
con = getConnection();
|
|
892
|
String query="UPDATE jobs SET " + validationType + "_job_status=? , records_tested=? WHERE id=?";
|
|
893
|
stmt = con.prepareStatement(query);
|
|
894
|
stmt.setString(1, status);
|
|
895
|
stmt.setInt(2, recordsTested);
|
|
896
|
stmt.setInt(3, jobId);
|
|
897
|
if (stmt.executeUpdate() == 0)
|
|
898
|
stmt.close();
|
|
899
|
} catch (Exception e) {
|
|
900
|
logger.error("Error while accessing DB to set Submitted Job status.", e);
|
|
901
|
throw new DaoException(e);
|
|
902
|
} finally {
|
|
903
|
if (stmt != null) {
|
|
904
|
try {
|
|
905
|
stmt.close();
|
|
906
|
} catch (SQLException e) {
|
|
907
|
logger.error("Error while accessing DB to set Submitted Job status.", e);
|
|
908
|
throw new DaoException(e);
|
|
909
|
}
|
|
910
|
}
|
|
911
|
closeConnection(con);
|
|
912
|
}
|
|
913
|
|
|
914
|
}
|
|
915
|
|
|
916
|
@Override
|
|
917
|
public StoredJob getJobSummary(int jobId, String groupby) throws DaoException {
|
|
918
|
ResultSet rs = null;
|
|
919
|
Connection con = null;
|
|
920
|
PreparedStatement stmt = null;
|
|
921
|
StoredJob retJob = this.get(jobId);
|
|
922
|
logger.debug("Accessing DB to get all Jobs entries with jobId:"+jobId+" and groupBy:"+groupby);
|
|
923
|
try {
|
|
924
|
con = getConnection();
|
|
925
|
String query="select rules.name, rules.description, rules.weight, rules.mandatory, total, successes, rules.id, rules.job_type from job_results join rules on job_results.rule_id = rules.id where (job_results.job_id=? AND job_results.groupby=?) order by rules.name";
|
|
926
|
stmt = con.prepareStatement(query);
|
|
927
|
stmt.setInt(1, jobId);
|
|
928
|
stmt.setString(2, groupby);
|
|
929
|
rs = stmt.executeQuery();
|
|
930
|
if (rs!=null){
|
|
931
|
List<JobResultEntry> resultEntries = new ArrayList<JobResultEntry>();
|
|
932
|
while (rs.next()) {
|
|
933
|
JobResultEntry retEntry = new JobResultEntry();
|
|
934
|
retEntry.setName(rs.getString(1));
|
|
935
|
retEntry.setDescription(rs.getString(2));
|
|
936
|
retEntry.setWeight(rs.getInt(3));
|
|
937
|
retEntry.setMandatory(rs.getBoolean(4));
|
|
938
|
retEntry.setRuleId(rs.getInt(7));
|
|
939
|
retEntry.setType(rs.getString("job_type"));
|
|
940
|
retEntry.setErrors(this.getValidationErrors(jobId, retEntry.getRuleId()));
|
|
941
|
int total = rs.getInt(5);
|
|
942
|
int successes = rs.getInt(6);
|
|
943
|
if (rs.getInt(5) > 0)
|
|
944
|
retEntry.setSuccesses(successes + "/" + total);
|
|
945
|
else
|
|
946
|
retEntry.setSuccesses("--");
|
|
947
|
if (successes < total)
|
|
948
|
retEntry.setHasErrors(true);
|
|
949
|
else
|
|
950
|
retEntry.setHasErrors(false);
|
|
951
|
|
|
952
|
resultEntries.add(retEntry);
|
|
953
|
}
|
|
954
|
retJob.setResultEntries(resultEntries);
|
|
955
|
}
|
|
956
|
} catch (Exception e) {
|
|
957
|
logger.error("Error while Accessing DB to get all Jobs entries.", e);
|
|
958
|
throw new DaoException(e);
|
|
959
|
} finally {
|
|
960
|
if (stmt != null) {
|
|
961
|
try {
|
|
962
|
stmt.close();
|
|
963
|
} catch (SQLException e) {
|
|
964
|
logger.error("Error while Accessing DB to get all Jobs entries.", e);
|
|
965
|
throw new DaoException(e);
|
|
966
|
}
|
|
967
|
}
|
|
968
|
closeConnection(con);
|
|
969
|
}
|
|
970
|
retJob.setFilteredScores(this.getScoresPerGroupBy(jobId));
|
|
971
|
retJob.getFilteredScores().put("all", retJob.getContentJobScore());
|
|
972
|
return retJob;
|
|
973
|
}
|
|
974
|
|
|
975
|
@Override
|
|
976
|
public List<StoredJob> getJobSummary(List<String> baseUrls, int size) throws DaoException {
|
|
977
|
ResultSet rs = null;
|
|
978
|
Connection con = null;
|
|
979
|
PreparedStatement stmt = null;
|
|
980
|
List<JobResultEntry> resultEntries = new ArrayList<JobResultEntry>();
|
|
981
|
StringBuilder builder = new StringBuilder();
|
|
982
|
List<StoredJob> storedJobs = new ArrayList<>();
|
|
983
|
for(int i = 0; i < baseUrls.size(); i++ ) {
|
|
984
|
builder.append("?,");
|
|
985
|
}
|
|
986
|
Map<Integer, List<JobResultEntry>> results = new HashMap<>();
|
|
987
|
try {
|
|
988
|
con = getConnection();
|
|
989
|
String query="select rules.name, rules.description, rules.weight, rules.mandatory, total, successes, rules.id, rules.job_type, job_results.job_id from job_results join rules on job_results.rule_id = rules.id join jobs on jobs.id = job_results.job_id where jobs.repo in ("+ builder.deleteCharAt( builder.length() -1 ).toString() +") order by job_results.job_id limit " + size;
|
|
990
|
stmt = con.prepareStatement(query);
|
|
991
|
int index = 1;
|
|
992
|
for( String o : baseUrls) {
|
|
993
|
stmt.setString( index++, o ); // or whatever it applies
|
|
994
|
}
|
|
995
|
rs = stmt.executeQuery();
|
|
996
|
JobResultEntry retEntry = new JobResultEntry();
|
|
997
|
if (rs!=null){
|
|
998
|
int oldId = 0;
|
|
999
|
while (rs.next()) {
|
|
1000
|
if(oldId!=rs.getInt("job_id")){
|
|
1001
|
logger.debug("Changing job_id from " + oldId + " to "+ rs.getInt("job_id"));
|
|
1002
|
results.put(oldId,resultEntries);
|
|
1003
|
oldId=rs.getInt("job_id");
|
|
1004
|
resultEntries = new ArrayList<>();
|
|
1005
|
}
|
|
1006
|
retEntry.setName(rs.getString(1));
|
|
1007
|
retEntry.setDescription(rs.getString(2));
|
|
1008
|
retEntry.setWeight(rs.getInt(3));
|
|
1009
|
retEntry.setMandatory(rs.getBoolean(4));
|
|
1010
|
retEntry.setRuleId(rs.getInt(7));
|
|
1011
|
retEntry.setType(rs.getString("job_type"));
|
|
1012
|
retEntry.setErrors(this.getValidationErrors(rs.getInt("job_id"), retEntry.getRuleId()));
|
|
1013
|
int total = rs.getInt(5);
|
|
1014
|
int successes = rs.getInt(6);
|
|
1015
|
if (rs.getInt(5) > 0)
|
|
1016
|
retEntry.setSuccesses(successes + "/" + total);
|
|
1017
|
else
|
|
1018
|
retEntry.setSuccesses("--");
|
|
1019
|
if (successes < total)
|
|
1020
|
retEntry.setHasErrors(true);
|
|
1021
|
else
|
|
1022
|
retEntry.setHasErrors(false);
|
|
1023
|
|
|
1024
|
resultEntries.add(retEntry);
|
|
1025
|
}
|
|
1026
|
if(oldId!=0)
|
|
1027
|
results.put(oldId,resultEntries);
|
|
1028
|
|
|
1029
|
logger.debug("Totals job_idS:"+results.size());
|
|
1030
|
if(results.get(0)!=null)
|
|
1031
|
results.remove(0);
|
|
1032
|
for(Map.Entry<Integer,List<JobResultEntry>> entry : results.entrySet()){
|
|
1033
|
logger.debug("Creating StoredJob object for " + entry.getKey());
|
|
1034
|
StoredJob storedJob = this.get(entry.getKey());
|
|
1035
|
storedJob.setResultEntries(entry.getValue());
|
|
1036
|
storedJob.setFilteredScores(this.getScoresPerGroupBy(entry.getKey()));
|
|
1037
|
storedJob.getFilteredScores().put("all", storedJob.getContentJobScore());
|
|
1038
|
storedJobs.add(storedJob);
|
|
1039
|
}
|
|
1040
|
}
|
|
1041
|
} catch (Exception e) {
|
|
1042
|
logger.error("Error while Accessing DB to get all Jobs entries.", e);
|
|
1043
|
throw new DaoException(e);
|
|
1044
|
} finally {
|
|
1045
|
if (stmt != null) {
|
|
1046
|
try {
|
|
1047
|
stmt.close();
|
|
1048
|
} catch (SQLException e) {
|
|
1049
|
logger.error("Error while Accessing DB to get all Jobs entries.", e);
|
|
1050
|
throw new DaoException(e);
|
|
1051
|
}
|
|
1052
|
}
|
|
1053
|
closeConnection(con);
|
|
1054
|
}
|
|
1055
|
return storedJobs;
|
|
1056
|
}
|
|
1057
|
|
|
1058
|
public List<String> getValidationErrors(int jobId, int ruleId) throws DaoException {
|
|
1059
|
ResultSet rs = null;
|
|
1060
|
Connection con = null;
|
|
1061
|
PreparedStatement stmt = null;
|
|
1062
|
List<String> retList = null;
|
|
1063
|
logger.debug("Accessing DB to get Validation Errors of JobId " + jobId + " and RuleId " + ruleId);
|
|
1064
|
try {
|
|
1065
|
con = getConnection();
|
|
1066
|
String query="SELECT record_identifier FROM tasks WHERE job_id=? AND rule_id=? AND success=? LIMIT 30";
|
|
1067
|
stmt = con.prepareStatement(query);
|
|
1068
|
stmt.setInt(1, jobId);
|
|
1069
|
stmt.setInt(2, ruleId);
|
|
1070
|
stmt.setBoolean(3, false);
|
|
1071
|
rs = stmt.executeQuery();
|
|
1072
|
if (rs!=null){
|
|
1073
|
retList = new ArrayList<String>();
|
|
1074
|
|
|
1075
|
while (rs.next()) {
|
|
1076
|
// if (!rs.getBoolean(1))
|
|
1077
|
retList.add(rs.getString(1));
|
|
1078
|
}
|
|
1079
|
}
|
|
1080
|
} catch (Exception e) {
|
|
1081
|
logger.error("Accessing DB to get Validation Errors of a JobId and RuleId.", e);
|
|
1082
|
throw new DaoException(e);
|
|
1083
|
} finally {
|
|
1084
|
if (stmt != null) {
|
|
1085
|
try {
|
|
1086
|
stmt.close();
|
|
1087
|
} catch (SQLException e) {
|
|
1088
|
logger.error("Accessing DB to get Validation Errors of a JobId and RuleId.", e);
|
|
1089
|
throw new DaoException(e);
|
|
1090
|
}
|
|
1091
|
}
|
|
1092
|
closeConnection(con);
|
|
1093
|
}
|
|
1094
|
return retList;
|
|
1095
|
|
|
1096
|
|
|
1097
|
}
|
|
1098
|
|
|
1099
|
@Override
|
|
1100
|
public List<StoredJob> getUncompletedJobs() throws DaoException {
|
|
1101
|
ResultSet rs = null;
|
|
1102
|
Connection con = null;
|
|
1103
|
PreparedStatement stmt = null;
|
|
1104
|
StoredJob retJob = null;
|
|
1105
|
List<StoredJob> retList = null;
|
|
1106
|
logger.debug("Accessing DB to get uncompleted jobs");
|
|
1107
|
try {
|
|
1108
|
con = getConnection();
|
|
1109
|
String query="SELECT * FROM jobs j, rulesets r WHERE j.guidelines = r.guidelines_acronym AND (usage_job_status=? OR content_job_status=?)";
|
|
1110
|
|
|
1111
|
stmt = con.prepareStatement(query);
|
|
1112
|
stmt.setString(1,"ongoing");
|
|
1113
|
stmt.setString(2,"ongoing");
|
|
1114
|
|
|
1115
|
rs = stmt.executeQuery();
|
|
1116
|
if (rs!=null){
|
|
1117
|
retList = new ArrayList<StoredJob>();
|
|
1118
|
while (rs.next()) {
|
|
1119
|
retJob = new StoredJob();
|
|
1120
|
retJob.setValidationType(rs.getString("validation_type"));
|
|
1121
|
retJob.setContentJobStatus(rs.getString("content_job_status"));
|
|
1122
|
retJob.setUsageJobStatus(rs.getString("usage_job_status"));
|
|
1123
|
retJob.setStarted(rs.getString("started"));
|
|
1124
|
retJob.setEnded(rs.getString("ended"));
|
|
1125
|
retJob.setContentJobScore(rs.getInt("content_job_score"));
|
|
1126
|
retJob.setUsageJobScore(rs.getInt("usage_job_score"));
|
|
1127
|
retJob.setUserEmail(rs.getString("user_email"));
|
|
1128
|
retJob.setBaseUrl(rs.getString("repo"));
|
|
1129
|
retJob.setDuration(rs.getString("duration"));
|
|
1130
|
retJob.setGuidelinesShortName(rs.getString("short_name"));
|
|
1131
|
retJob.setDesiredCompatibilityLevel(rs.getString("guidelines"));
|
|
1132
|
retJob.setId(rs.getInt("id"));
|
|
1133
|
retJob.setError(rs.getString("error_information"));
|
|
1134
|
retJob.setJobType(rs.getString("job_type"));
|
|
1135
|
retJob.setRecordsTested(rs.getInt("records_tested"));
|
|
1136
|
retJob.setGroupByXpath(rs.getString("groupby_xpath"));
|
|
1137
|
retJob.setValidationSet(rs.getString("set"));
|
|
1138
|
retJob.setRecords(Integer.parseInt(rs.getString("records")));
|
|
1139
|
retJob.setMetadataPrefix(rs.getString("metadata_prefix"));
|
|
1140
|
retJob.setRules(Utilities.convertStringToSet(rs.getString("rules")));
|
|
1141
|
|
|
1142
|
if (retJob.getJobType().equals("Registration Request")) {
|
|
1143
|
retJob.setRegistration(true);
|
|
1144
|
this.getJobForRegistration(retJob);
|
|
1145
|
}
|
|
1146
|
if (retJob.getDesiredCompatibilityLevel().contains("cris")) {
|
|
1147
|
retJob.setCris(true);
|
|
1148
|
this.getJobForCris(retJob);
|
|
1149
|
}
|
|
1150
|
retList.add(retJob);
|
|
1151
|
}
|
|
1152
|
}
|
|
1153
|
|
|
1154
|
} catch (Exception e) {
|
|
1155
|
logger.error("Error while accessing DB get uncompleted jobs .", e);
|
|
1156
|
throw new DaoException(e);
|
|
1157
|
} finally {
|
|
1158
|
if (stmt != null) {
|
|
1159
|
try {
|
|
1160
|
stmt.close();
|
|
1161
|
} catch (SQLException e) {
|
|
1162
|
logger.error("Error while accessing DB to get uncompleted jobs.", e);
|
|
1163
|
throw new DaoException(e);
|
|
1164
|
}
|
|
1165
|
}
|
|
1166
|
closeConnection(con);
|
|
1167
|
}
|
|
1168
|
return retList;
|
|
1169
|
|
|
1170
|
}
|
|
1171
|
|
|
1172
|
@Override
|
|
1173
|
public int deleteUncompletedJobs() throws DaoException {
|
|
1174
|
int jobsDeleted = 0;
|
|
1175
|
Connection con = null;
|
|
1176
|
PreparedStatement stmt = null;
|
|
1177
|
logger.debug("Accessing DB to delete uncompleted Jobs and their tasks");
|
|
1178
|
try {
|
|
1179
|
con = getConnection();
|
|
1180
|
logger.debug("Deleting jobs..");
|
|
1181
|
String query="DELETE FROM jobs WHERE content_job_status=? OR usage_job_status=?";
|
|
1182
|
stmt = con.prepareStatement(query);
|
|
1183
|
stmt.setString(1, "ongoing");
|
|
1184
|
stmt.setString(2, "ongoing");
|
|
1185
|
jobsDeleted = stmt.executeUpdate();
|
|
1186
|
logger.debug("Finish Deleting jobs..");
|
|
1187
|
} catch (Exception e) {
|
|
1188
|
logger.error("Error while Accessing DB to delete uncompleted Jobs and their tasks.", e);
|
|
1189
|
throw new DaoException(e);
|
|
1190
|
} finally {
|
|
1191
|
if (stmt != null) {
|
|
1192
|
try {
|
|
1193
|
stmt.close();
|
|
1194
|
} catch (SQLException e) {
|
|
1195
|
logger.error("Error while Accessing DB to delete uncompleted Jobs and their tasks.", e);
|
|
1196
|
throw new DaoException(e);
|
|
1197
|
}
|
|
1198
|
}
|
|
1199
|
closeConnection(con);
|
|
1200
|
}
|
|
1201
|
return jobsDeleted;
|
|
1202
|
}
|
|
1203
|
|
|
1204
|
|
|
1205
|
/*
|
|
1206
|
@Override
|
|
1207
|
public void deleteSemiCompletedRegistrationJobs(String activationId) {
|
|
1208
|
Connection con = null;
|
|
1209
|
PreparedStatement stmt = null;
|
|
1210
|
logger.debug("Accessing DB to delete uncompleted Jobs and their tasks");
|
|
1211
|
try {
|
|
1212
|
con = getConnection();
|
|
1213
|
logger.debug("Deleting Semi Completed Registration Jobs..");
|
|
1214
|
String query="DELETE FROM jobs WHERE activation_id=?";
|
|
1215
|
stmt = con.prepareStatement(query);
|
|
1216
|
stmt.setString(1, activationId);
|
|
1217
|
stmt.executeUpdate();
|
|
1218
|
logger.debug("Finish Deleting jobs..");
|
|
1219
|
} catch (Exception e) {
|
|
1220
|
logger.error("Error while Accessing DB to delete uncompleted Jobs and their tasks.", e);
|
|
1221
|
} finally {
|
|
1222
|
if (stmt != null) {
|
|
1223
|
try {
|
|
1224
|
stmt.close();
|
|
1225
|
} catch (SQLException e) {
|
|
1226
|
logger.error("Error while Accessing DB to delete uncompleted Jobs and their tasks.", e);
|
|
1227
|
}
|
|
1228
|
}
|
|
1229
|
}
|
|
1230
|
|
|
1231
|
}
|
|
1232
|
*/
|
|
1233
|
|
|
1234
|
public Map<String, Integer> getScoresPerGroupBy(int jobId) throws DaoException {
|
|
1235
|
ResultSet rs = null;
|
|
1236
|
Connection con = null;
|
|
1237
|
PreparedStatement stmt = null;
|
|
1238
|
Map<String, Integer> retMap = null;
|
|
1239
|
|
|
1240
|
logger.debug("Accessing DB to get filtered score for jobId:"+jobId);
|
|
1241
|
try {
|
|
1242
|
con = getConnection();
|
|
1243
|
String query="SELECT groupBy, score FROM jobs_filtered_scores WHERE job_id=?";
|
|
1244
|
stmt = con.prepareStatement(query);
|
|
1245
|
stmt.setInt(1, jobId);
|
|
1246
|
rs = stmt.executeQuery();
|
|
1247
|
if (rs!=null){
|
|
1248
|
logger.debug("filtered scores found");
|
|
1249
|
retMap = new HashMap<String, Integer>();
|
|
1250
|
while (rs.next()) {
|
|
1251
|
logger.debug("score: " + rs.getInt(2) + " groupBy: " + rs.getString(1) );
|
|
1252
|
retMap.put(rs.getString(1), rs.getInt(2));
|
|
1253
|
}
|
|
1254
|
}
|
|
1255
|
} catch (Exception e) {
|
|
1256
|
logger.error("Error while Accessing DB to get filtered scores .", e);
|
|
1257
|
throw new DaoException(e);
|
|
1258
|
} finally {
|
|
1259
|
if (stmt != null) {
|
|
1260
|
try {
|
|
1261
|
stmt.close();
|
|
1262
|
} catch (SQLException e) {
|
|
1263
|
logger.error("Error while Accessing DB to get filtered scores .", e);
|
|
1264
|
throw new DaoException(e);
|
|
1265
|
}
|
|
1266
|
}
|
|
1267
|
closeConnection(con);
|
|
1268
|
}
|
|
1269
|
return retMap;
|
|
1270
|
|
|
1271
|
}
|
|
1272
|
|
|
1273
|
private void storeJobForRegistration(JobForValidation job, int jobId) throws DaoException {
|
|
1274
|
|
|
1275
|
Connection con = null;
|
|
1276
|
PreparedStatement stmt = null;
|
|
1277
|
|
|
1278
|
logger.debug("Accessing DB to store job values for Registration");
|
|
1279
|
try {
|
|
1280
|
con = getConnection();
|
|
1281
|
String query="INSERT INTO jobs_for_registration (activation_id, official_name, admin_emails, datasource_id, interface_id, interface_id_old, repo_type, update_existing, job_id) VALUES (?,?,?,?,?,?,?,?,?)";
|
|
1282
|
stmt = con.prepareStatement(query);
|
|
1283
|
stmt.setString(1, job.getActivationId());
|
|
1284
|
stmt.setString(2, job.getOfficialName());
|
|
1285
|
String[] data = job.getAdminEmails().toArray(new String[job.getAdminEmails().size()]);
|
|
1286
|
stmt.setArray(3, con.createArrayOf("text", data));
|
|
1287
|
stmt.setString(4, job.getDatasourceId());
|
|
1288
|
stmt.setString(5, job.getInterfaceId());
|
|
1289
|
stmt.setString(6, job.getInterfaceIdOld());
|
|
1290
|
stmt.setString(7, job.getRepoType());
|
|
1291
|
stmt.setBoolean(8, job.isUpdateExisting());
|
|
1292
|
stmt.setInt(9, jobId);
|
|
1293
|
|
|
1294
|
stmt.executeUpdate();
|
|
1295
|
|
|
1296
|
} catch (Exception e) {
|
|
1297
|
logger.error("Error while Accessing DB to store job values for Registration .", e);
|
|
1298
|
throw new DaoException(e);
|
|
1299
|
} finally {
|
|
1300
|
if (stmt != null) {
|
|
1301
|
try {
|
|
1302
|
stmt.close();
|
|
1303
|
} catch (SQLException e) {
|
|
1304
|
logger.error("Error Accessing DB to store job values for Registration .", e);
|
|
1305
|
throw new DaoException(e);
|
|
1306
|
}
|
|
1307
|
}
|
|
1308
|
closeConnection(con);
|
|
1309
|
}
|
|
1310
|
|
|
1311
|
}
|
|
1312
|
|
|
1313
|
private void storeJobForCris(JobForValidation job, int jobId) throws DaoException {
|
|
1314
|
|
|
1315
|
Connection con = null;
|
|
1316
|
PreparedStatement stmt = null;
|
|
1317
|
|
|
1318
|
logger.debug("Accessing DB to store job values for cris");
|
|
1319
|
try {
|
|
1320
|
con = getConnection();
|
|
1321
|
String query="INSERT INTO jobs_for_cris (job_id, entities, referential_checks) VALUES (?,?,?)";
|
|
1322
|
stmt = con.prepareStatement(query);
|
|
1323
|
stmt.setInt(1, jobId);
|
|
1324
|
// String[] data = job.getSelectedCrisEntities().toArray(new String[job.getSelectedCrisEntities().size()]);
|
|
1325
|
stmt.setArray(2, con.createArrayOf("text", job.getSelectedCrisEntities().toArray()));
|
|
1326
|
stmt.setBoolean(3, job.isCrisReferentialChecks());
|
|
1327
|
stmt.executeUpdate();
|
|
1328
|
|
|
1329
|
} catch (Exception e) {
|
|
1330
|
logger.error("Error while Accessing DB to store job values for cris .", e);
|
|
1331
|
throw new DaoException(e);
|
|
1332
|
} finally {
|
|
1333
|
if (stmt != null) {
|
|
1334
|
try {
|
|
1335
|
stmt.close();
|
|
1336
|
} catch (SQLException e) {
|
|
1337
|
logger.error("Error Accessing DB to store job values for cris .", e);
|
|
1338
|
throw new DaoException(e);
|
|
1339
|
}
|
|
1340
|
}
|
|
1341
|
closeConnection(con);
|
|
1342
|
}
|
|
1343
|
|
|
1344
|
}
|
|
1345
|
|
|
1346
|
private void getJobForCris(StoredJob job) throws DaoException {
|
|
1347
|
Connection con = null;
|
|
1348
|
PreparedStatement stmt = null;
|
|
1349
|
|
|
1350
|
logger.debug("Accessing DB to get job values for cris for jobId: " + job.getId());
|
|
1351
|
try {
|
|
1352
|
con = getConnection();
|
|
1353
|
String query="SELECT * FROM jobs_for_cris WHERE job_id = ?";
|
|
1354
|
stmt = con.prepareStatement(query);
|
|
1355
|
stmt.setInt(1, job.getId());
|
|
1356
|
|
|
1357
|
ResultSet rs = stmt.executeQuery();
|
|
1358
|
if (rs.next()) {
|
|
1359
|
logger.debug("cris job found");
|
|
1360
|
Set<String> entities = new HashSet<String>();
|
|
1361
|
Array tt = rs.getArray("entities");
|
|
1362
|
String[] ent = (String[])tt.getArray();
|
|
1363
|
|
|
1364
|
// String[] ret = (String[])rs.getArray("entities").getArray();
|
|
1365
|
|
|
1366
|
entities.addAll(Arrays.asList(ent));
|
|
1367
|
logger.debug("Entities size: " + entities);
|
|
1368
|
job.setSelectedCrisEntities(entities);
|
|
1369
|
|
|
1370
|
job.setCrisReferentialChecks(rs.getBoolean("referential_checks"));
|
|
1371
|
|
|
1372
|
logger.debug("ref checks: " + job.isCrisReferentialChecks());
|
|
1373
|
}
|
|
1374
|
|
|
1375
|
} catch (Exception e) {
|
|
1376
|
logger.error("Error while Accessing DB to get job values for cris .", e);
|
|
1377
|
throw new DaoException(e);
|
|
1378
|
} finally {
|
|
1379
|
if (stmt != null) {
|
|
1380
|
try {
|
|
1381
|
stmt.close();
|
|
1382
|
} catch (SQLException e) {
|
|
1383
|
logger.error("Error Accessing DB to get job values for cris .", e);
|
|
1384
|
throw new DaoException(e);
|
|
1385
|
}
|
|
1386
|
}
|
|
1387
|
closeConnection(con);
|
|
1388
|
}
|
|
1389
|
}
|
|
1390
|
|
|
1391
|
private void getJobForRegistration(StoredJob job) throws DaoException {
|
|
1392
|
Connection con = null;
|
|
1393
|
PreparedStatement stmt = null;
|
|
1394
|
|
|
1395
|
// logger.debug("Accessing DB to get job values for Registration");
|
|
1396
|
try {
|
|
1397
|
con = getConnection();
|
|
1398
|
String query="SELECT * FROM jobs_for_registration WHERE job_id = ?";
|
|
1399
|
stmt = con.prepareStatement(query);
|
|
1400
|
stmt.setInt(1, job.getId());
|
|
1401
|
|
|
1402
|
ResultSet rs = stmt.executeQuery();
|
|
1403
|
if (rs.next()) {
|
|
1404
|
job.getAdminEmails().addAll(Arrays.asList((String[])rs.getArray("admin_emails").getArray()));
|
|
1405
|
job.setDatasourceId(rs.getString("datasource_id"));
|
|
1406
|
job.setInterfaceId(rs.getString("interface_id"));
|
|
1407
|
job.setInterfaceIdOld(rs.getString("interface_id_old"));
|
|
1408
|
job.setActivationId(rs.getString("activation_id"));
|
|
1409
|
job.setRepoType(rs.getString("repo_type"));
|
|
1410
|
job.setOfficialName(rs.getString("official_name"));
|
|
1411
|
job.setUpdateExisting(rs.getBoolean("update_existing"));
|
|
1412
|
}
|
|
1413
|
|
|
1414
|
} catch (Exception e) {
|
|
1415
|
logger.error("Error while Accessing DB to get job values for Registration .", e);
|
|
1416
|
throw new DaoException(e);
|
|
1417
|
} finally {
|
|
1418
|
if (stmt != null) {
|
|
1419
|
try {
|
|
1420
|
stmt.close();
|
|
1421
|
} catch (SQLException e) {
|
|
1422
|
logger.error("Error Accessing DB to get job values for Registration .", e);
|
|
1423
|
throw new DaoException(e);
|
|
1424
|
}
|
|
1425
|
}
|
|
1426
|
closeConnection(con);
|
|
1427
|
}
|
|
1428
|
}
|
|
1429
|
|
|
1430
|
@Override
|
|
1431
|
protected PreparedStatement getUpdateStatement(StoredJob t,
|
|
1432
|
Connection con) throws SQLException {
|
|
1433
|
logger.debug("getting submittedjob updateStatement");
|
|
1434
|
String query="UPDATE jobs SET validation_type=?, started=to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS'), guidelines=?, content_job_status=?, repo=?, duration=? WHERE id=?";
|
|
1435
|
|
|
1436
|
PreparedStatement stmt = con.prepareStatement(query);
|
|
1437
|
Calendar cal = Calendar.getInstance();
|
|
1438
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
|
1439
|
stmt.setString(1, t.getValidationType());
|
|
1440
|
stmt.setString(2, sdf.format(cal.getTime()));
|
|
1441
|
// stmt.setTimestamp(2, getCurrentTimeStamp());
|
|
1442
|
stmt.setString(3, t.getDesiredCompatibilityLevel());
|
|
1443
|
stmt.setString(4, t.getContentJobStatus());
|
|
1444
|
stmt.setString(5, t.getBaseUrl());
|
|
1445
|
stmt.setString(6, t.getDuration());
|
|
1446
|
stmt.setInt(7, t.getId());
|
|
1447
|
|
|
1448
|
return stmt;
|
|
1449
|
}
|
|
1450
|
|
|
1451
|
@Override
|
|
1452
|
protected PreparedStatement getInsertStatement(StoredJob t,
|
|
1453
|
Connection con) throws SQLException {
|
|
1454
|
logger.debug("getting submittedjob insertStatement");
|
|
1455
|
Calendar cal = Calendar.getInstance();
|
|
1456
|
|
|
1457
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
|
1458
|
|
|
1459
|
String query="INSERT INTO jobs(validation_type,started,guidelines,user_email,content_job_status, usage_job_status, repo, duration, rules, records, set, groupby_xpath, metadata_prefix, job_type) VALUES(?,to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS'),?,?,?,?,?,?,?,?,?,?,?,?)";
|
|
1460
|
PreparedStatement stmt = con.prepareStatement(query);
|
|
1461
|
stmt.setString(1, t.getValidationType());
|
|
1462
|
stmt.setString(2, sdf.format(cal.getTime()));
|
|
1463
|
// stmt.setTimestamp(2, getCurrentTimeStamp());
|
|
1464
|
stmt.setString(3, t.getDesiredCompatibilityLevel());
|
|
1465
|
stmt.setString(4, t.getUserEmail());
|
|
1466
|
stmt.setString(5, t.getContentJobStatus());
|
|
1467
|
stmt.setString(6, t.getUsageJobStatus());
|
|
1468
|
stmt.setString(7, t.getBaseUrl());
|
|
1469
|
stmt.setString(8, t.getDuration());
|
|
1470
|
stmt.setString(9, Utilities.convertSetToString(t.getRules()));
|
|
1471
|
stmt.setString(10, Integer.toString(t.getRecords()));
|
|
1472
|
stmt.setString(11, t.getValidationSet());
|
|
1473
|
stmt.setString(12, t.getGroupByXpath());
|
|
1474
|
stmt.setString(13, t.getMetadataPrefix());
|
|
1475
|
stmt.setString(14, t.getJobType());
|
|
1476
|
|
|
1477
|
return stmt;
|
|
1478
|
}
|
|
1479
|
|
|
1480
|
|
|
1481
|
@SuppressWarnings("unused")
|
|
1482
|
private static java.sql.Timestamp getCurrentTimeStamp() {
|
|
1483
|
|
|
1484
|
java.util.Date today = new java.util.Date();
|
|
1485
|
return new java.sql.Timestamp(today.getTime());
|
|
1486
|
|
|
1487
|
}
|
|
1488
|
|
|
1489
|
}
|