1
|
package eu.dnetlib.data.statsmanager;
|
2
|
|
3
|
|
4
|
import eu.dnetlib.clients.data.search.ws.SearchWebService;
|
5
|
import eu.dnetlib.common.rmi.UnimplementedException;
|
6
|
import eu.dnetlib.domain.data.SearchResult;
|
7
|
import org.apache.cxf.jaxws.JaxWsProxyFactoryBean;
|
8
|
import org.apache.log4j.Logger;
|
9
|
import org.springframework.core.io.Resource;
|
10
|
|
11
|
import javax.sql.DataSource;
|
12
|
import java.sql.*;
|
13
|
import java.util.HashMap;
|
14
|
import java.util.Map;
|
15
|
import java.util.Properties;
|
16
|
|
17
|
|
18
|
public class Validator {
|
19
|
|
20
|
private ValidationReport validationReport;
|
21
|
private Resource validationQueriesFile;
|
22
|
private DataSource dataSource;
|
23
|
private String historySchema;
|
24
|
private String shadowSearchURL;
|
25
|
private static SearchWebService searchWebService = null;
|
26
|
private Logger log = Logger.getLogger(this.getClass());
|
27
|
|
28
|
public Validator() {
|
29
|
}
|
30
|
|
31
|
private void initSearch() {
|
32
|
if (searchWebService == null) {
|
33
|
JaxWsProxyFactoryBean factory = new JaxWsProxyFactoryBean();
|
34
|
factory.setServiceClass(SearchWebService.class);
|
35
|
factory.setAddress(shadowSearchURL);
|
36
|
searchWebService = (SearchWebService) factory.create();
|
37
|
}
|
38
|
}
|
39
|
|
40
|
public ValidationReport validateDatabase() throws Exception {
|
41
|
ValidationReport report = new ValidationReport();
|
42
|
Properties p = new Properties();
|
43
|
|
44
|
this.validationReport = report;
|
45
|
|
46
|
try { //TODO initiate search
|
47
|
initSearch();
|
48
|
log.debug("Search Client initiated at " + this.shadowSearchURL);
|
49
|
} catch (Exception e) {
|
50
|
log.error(e.toString());
|
51
|
throw new Exception(e);
|
52
|
|
53
|
}
|
54
|
|
55
|
try {
|
56
|
|
57
|
p.loadFromXML(validationQueriesFile.getInputStream());
|
58
|
|
59
|
} catch (Exception e) {
|
60
|
log.error("Error while loading queries file :" + e.toString());
|
61
|
throw new Exception(e);
|
62
|
|
63
|
}
|
64
|
|
65
|
|
66
|
int queryCount = Integer.parseInt(p.getProperty("queries.count"));
|
67
|
log.info("Query Count: " + queryCount + "\n");
|
68
|
|
69
|
for (int i = 1; i <= queryCount; i++) {
|
70
|
String schema = p.getProperty("queries.schema");
|
71
|
|
72
|
String queryType = p.getProperty("queries." + i + ".type");
|
73
|
String sql = p.getProperty("queries." + i + ".sql").replaceAll("schema", schema);
|
74
|
String cql = p.getProperty("queries." + i + ".cql.query");
|
75
|
String name = p.getProperty("queries." + i + ".name");
|
76
|
String id = p.getProperty("queries." + i + ".id");
|
77
|
|
78
|
log.debug("Query: id " + id + " " + name + "\n" + " SQL " + sql + "\n" + "CQL" + cql + "\n");
|
79
|
if (queryType.equals("search")) {
|
80
|
|
81
|
|
82
|
int sqlResult = executeSQLCountQuery(sql);
|
83
|
int cqlResult = executeCQLCountQuery(cql);
|
84
|
|
85
|
boolean status = validate(sqlResult, cqlResult);
|
86
|
saveResult(id, name, sql, String.valueOf(sqlResult), validationReport.getValidationDate().toString());
|
87
|
validationReport.addQuery(id, name, sqlResult, cqlResult, status);
|
88
|
|
89
|
|
90
|
} else if (queryType.equals("browse")) {
|
91
|
|
92
|
// Map<String, Integer> sqlResults = executeSQLBrowseQuery(sql);
|
93
|
// Map<String, Integer> cqlResults = executeCQLBrowseQuery(cql, p.getProperty("queries." + i + ".cql.groupBy"));
|
94
|
|
95
|
// TODO compare and update report
|
96
|
}
|
97
|
}
|
98
|
|
99
|
log.info("Generated report : " + report.toString());
|
100
|
return report;
|
101
|
}
|
102
|
|
103
|
//@Transactional
|
104
|
public void saveResult(String id, String desc, String qString, String result, String date) throws Exception {
|
105
|
Connection con = dataSource.getConnection();
|
106
|
|
107
|
log.info("Writing report to DB " + con.getMetaData().getURL() + " and historySchema " + this.historySchema + "...");
|
108
|
|
109
|
String q = "UPDATE " + this.historySchema + ".query SET query_id=?,query=?, description=? WHERE query_id=? ;" +
|
110
|
"INSERT INTO " + this.historySchema + ".query (query_id, query, description) SELECT ? ,?,? WHERE NOT EXISTS" +
|
111
|
"(SELECT 1 FROM " + this.historySchema + ".query WHERE query_id=?)";
|
112
|
|
113
|
// log.info("Executing query update " + q);
|
114
|
|
115
|
PreparedStatement stm = con.prepareStatement(q);
|
116
|
|
117
|
|
118
|
stm.setString(1, id);
|
119
|
stm.setString(2, qString);
|
120
|
stm.setString(3, desc);
|
121
|
stm.setString(4, id);
|
122
|
stm.setString(5, id);
|
123
|
stm.setString(6, qString);
|
124
|
stm.setString(7, desc);
|
125
|
stm.setString(8, id);
|
126
|
|
127
|
stm.executeUpdate();
|
128
|
|
129
|
// q = "INSERT INTO value(query_id, num, date) values ('" + id + "','" + result + "','" + date + "');";
|
130
|
|
131
|
q = "INSERT INTO " + this.historySchema + ".value(query_id, num, date) values (?,?,?);";
|
132
|
stm.clearParameters();
|
133
|
|
134
|
|
135
|
stm = con.prepareStatement(q);
|
136
|
stm.setString(1, id);
|
137
|
stm.setString(2, result);
|
138
|
stm.setString(3, date);
|
139
|
|
140
|
//log.info("Executing query update " + q);
|
141
|
stm.executeUpdate();
|
142
|
|
143
|
stm.close();
|
144
|
con.close();
|
145
|
}
|
146
|
|
147
|
|
148
|
private boolean validate(int sqlResult, int cqlResult) {
|
149
|
|
150
|
log.debug(" Validation input -> sql : " + sqlResult + " cql : " + cqlResult);
|
151
|
if (sqlResult == cqlResult) {
|
152
|
|
153
|
return true;
|
154
|
}
|
155
|
|
156
|
return false;
|
157
|
}
|
158
|
|
159
|
private Map<String, Integer> executeCQLBrowseQuery(String cql, String groupBy) {
|
160
|
// TODO implement me please
|
161
|
|
162
|
//oaftype=result and deletedbyinferece=false and type=publication , groupby = access_mode
|
163
|
// searchServiceServiceLocator.getService().refine();
|
164
|
|
165
|
//public SearchResult refine(String queryText, String transformer,
|
166
|
//String locale, Collection<String> fields) throws SearchServiceException;
|
167
|
|
168
|
// public SearchResult refine(String queryText, String transformer,
|
169
|
// String locale, Collection<String> fields) throws SearchServiceException;
|
170
|
//[2:52:39 PM] Antonis Lempesis:
|
171
|
|
172
|
|
173
|
//query= oaftype=result and deletedbyinferece=false and type=publication
|
174
|
|
175
|
//transformer = results_openaire
|
176
|
|
177
|
// locale=UTF-8
|
178
|
|
179
|
// fields = access_mode
|
180
|
|
181
|
|
182
|
// set to 1 page and results
|
183
|
// public SearchResult search(String queryText, String transformer,
|
184
|
// String locale, int page, int size) throws SearchServiceException;
|
185
|
|
186
|
throw new UnimplementedException();
|
187
|
}
|
188
|
|
189
|
private Map<String, Integer> executeSQLBrowseQuery(String sql) {
|
190
|
// TODO implement me please
|
191
|
throw new UnimplementedException();
|
192
|
}
|
193
|
|
194
|
private int executeCQLCountQuery(String cql) throws Exception {
|
195
|
try {
|
196
|
if (cql == null || cql.isEmpty() || cql.equalsIgnoreCase("not available")) {
|
197
|
return 0;
|
198
|
}
|
199
|
SearchResult result = searchWebService.search(cql, "results_openaire", "en_GB", 1, 1);
|
200
|
|
201
|
return result.getTotal();
|
202
|
|
203
|
|
204
|
} catch (Exception e) {
|
205
|
log.error("Could not execute CQL query. Reason: " + e);
|
206
|
throw new Exception("Could not execute CQL query. Reason: ", e);
|
207
|
}
|
208
|
}
|
209
|
|
210
|
|
211
|
private int executeSQLCountQuery(String sql) throws Exception {
|
212
|
|
213
|
Connection con = null;
|
214
|
try {
|
215
|
|
216
|
con = dataSource.getConnection();
|
217
|
|
218
|
|
219
|
Statement st = con.createStatement();
|
220
|
if (st.execute(sql)) {
|
221
|
|
222
|
ResultSet rs = st.getResultSet();
|
223
|
int res = getResult(rs);
|
224
|
st.close();
|
225
|
return res;
|
226
|
} else {
|
227
|
log.error("Fail to execute command " + sql + " " + st.getWarnings());
|
228
|
throw new Exception("Fail to execute command " + sql + " " + st.getWarnings());
|
229
|
}
|
230
|
|
231
|
|
232
|
} catch (Exception e) {
|
233
|
log.error("Could not execute sql query " + sql + " : " + e);
|
234
|
throw new Exception("Could not execute sql query " + sql + " : ", e);
|
235
|
} finally {
|
236
|
if (con != null) {
|
237
|
con.close();
|
238
|
|
239
|
|
240
|
}
|
241
|
}
|
242
|
|
243
|
}
|
244
|
|
245
|
private int getResult(ResultSet rs) throws Exception {
|
246
|
HashMap<String, Integer> data = new HashMap<String, Integer>();
|
247
|
int res = -1;
|
248
|
try {
|
249
|
|
250
|
ResultSetMetaData rsmd = rs.getMetaData();
|
251
|
|
252
|
if (rs.next()) {
|
253
|
|
254
|
res = (Integer.valueOf(rs.getString(1)));
|
255
|
rs.close();
|
256
|
}
|
257
|
|
258
|
return res;
|
259
|
} catch (Exception e) {
|
260
|
log.error("Could not process results :" + e);
|
261
|
throw new Exception("Could not process results :", e);
|
262
|
}
|
263
|
|
264
|
}
|
265
|
|
266
|
private HashMap<String, Integer> getResults(ResultSet rs) throws Exception {
|
267
|
HashMap<String, Integer> data = new HashMap<String, Integer>();
|
268
|
try {
|
269
|
|
270
|
ResultSetMetaData rsmd = rs.getMetaData();
|
271
|
while (rs.next()) {
|
272
|
for (int i = 1; i < rsmd.getColumnCount() - 1; i++) {
|
273
|
data.put(rsmd.getColumnName(i), rs.getInt(i));
|
274
|
}
|
275
|
|
276
|
}
|
277
|
|
278
|
return data;
|
279
|
|
280
|
} catch (Exception e) {
|
281
|
log.error("Could not process results :" + e);
|
282
|
throw new Exception("Could not process results :", e);
|
283
|
}
|
284
|
|
285
|
}
|
286
|
|
287
|
|
288
|
public ValidationReport getValidationReport() {
|
289
|
return validationReport;
|
290
|
}
|
291
|
|
292
|
public void setValidationReport(ValidationReport validationReport) {
|
293
|
this.validationReport = validationReport;
|
294
|
}
|
295
|
|
296
|
public Resource getValidationQueriesFile() {
|
297
|
return validationQueriesFile;
|
298
|
}
|
299
|
|
300
|
public void setValidationQueriesFile(Resource validationQueriesFile) {
|
301
|
this.validationQueriesFile = validationQueriesFile;
|
302
|
}
|
303
|
|
304
|
public DataSource getDataSource() {
|
305
|
return dataSource;
|
306
|
}
|
307
|
|
308
|
public void setDataSource(DataSource dataSource) {
|
309
|
this.dataSource = dataSource;
|
310
|
}
|
311
|
|
312
|
public String getShadowSearchURL() {
|
313
|
return shadowSearchURL;
|
314
|
}
|
315
|
|
316
|
public void setShadowSearchURL(String shadowSearchURL) {
|
317
|
this.shadowSearchURL = shadowSearchURL;
|
318
|
}
|
319
|
|
320
|
public static SearchWebService getSearchWebService() {
|
321
|
return searchWebService;
|
322
|
}
|
323
|
|
324
|
public static void setSearchWebService(SearchWebService searchWebService) {
|
325
|
Validator.searchWebService = searchWebService;
|
326
|
}
|
327
|
|
328
|
public String getHistorySchema() {
|
329
|
return historySchema;
|
330
|
}
|
331
|
|
332
|
public void setHistorySchema(String historySchema) {
|
333
|
this.historySchema = historySchema;
|
334
|
}
|
335
|
|
336
|
|
337
|
}
|