D-Net

package eu.dnetlib.data.statsmanager;

import eu.dnetlib.clients.data.search.ws.SearchWebService;

import eu.dnetlib.common.rmi.UnimplementedException;

import eu.dnetlib.domain.data.SearchResult;

import org.apache.cxf.jaxws.JaxWsProxyFactoryBean;

import org.apache.log4j.Logger;

import org.springframework.core.io.Resource;

import javax.sql.DataSource;

import java.sql.*;

import java.util.HashMap;

import java.util.Map;

import java.util.Properties;

public class Validator {

	private ValidationReport validationReport;

	private Resource validationQueriesFile;

	private DataSource dataSource;

	private String historySchema;

	private String shadowSearchURL;

	private static SearchWebService searchWebService = null;

	private Logger log = Logger.getLogger(this.getClass());

	public Validator() {

	}

	private void initSearch() {

		if (searchWebService == null) {

			JaxWsProxyFactoryBean factory = new JaxWsProxyFactoryBean();

			factory.setServiceClass(SearchWebService.class);

			factory.setAddress(shadowSearchURL);

			searchWebService = (SearchWebService) factory.create();

		}

	}

	public ValidationReport validateDatabase() throws Exception {

		ValidationReport report = new ValidationReport();

		Properties p = new Properties();

		this.validationReport = report;

		try { //TODO initiate search

			initSearch();

			   log.debug("Search Client  initiated at " + this.shadowSearchURL);

		} catch (Exception e) {

			log.error(e.toString());

			throw new Exception(e);

		}

		try {

			p.loadFromXML(validationQueriesFile.getInputStream());

		} catch (Exception e) {

			log.error("Error while loading queries file :" + e.toString());

			throw new Exception(e);

		}

		int queryCount = Integer.parseInt(p.getProperty("queries.count"));

		 log.info("Query  Count: " + queryCount + "\n");

		for (int i = 1; i <= queryCount; i++) {

			String schema = p.getProperty("queries.schema");

			String queryType = p.getProperty("queries." + i + ".type");

			String sql = p.getProperty("queries." + i + ".sql").replaceAll("schema", schema);

			String cql = p.getProperty("queries." + i + ".cql.query");

			String name = p.getProperty("queries." + i + ".name");

			String id = p.getProperty("queries." + i + ".id");

		    log.debug("Query: id  " + id + "  " + name + "\n" + " SQL " + sql + "\n" + "CQL" + cql + "\n");

			if (queryType.equals("search")) {

				int sqlResult = executeSQLCountQuery(sql);

				int cqlResult = executeCQLCountQuery(cql);

				boolean status = validate(sqlResult, cqlResult);

				saveResult(id, name, sql, String.valueOf(sqlResult), validationReport.getValidationDate().toString());

				validationReport.addQuery(id, name, sqlResult, cqlResult, status);

			} else if (queryType.equals("browse")) {

				//   Map<String, Integer> sqlResults = executeSQLBrowseQuery(sql);

				//  Map<String, Integer> cqlResults = executeCQLBrowseQuery(cql, p.getProperty("queries." + i + ".cql.groupBy"));

				// TODO compare and update report

			}

		}

		log.info("Generated report : " + report.toString());

		return report;

	}

	//@Transactional

	public void saveResult(String id, String desc, String qString, String result, String date) throws Exception {

		Connection con = dataSource.getConnection();

		log.info("Writing report to DB " + con.getMetaData().getURL() + " and historySchema " + this.historySchema + "...");

		String q = "UPDATE  " + this.historySchema + ".query SET query_id=?,query=?, description=? WHERE query_id=? ;" +

				"INSERT INTO " + this.historySchema + ".query (query_id, query, description) SELECT ? ,?,?  WHERE NOT EXISTS" +

				"(SELECT 1 FROM " + this.historySchema + ".query WHERE query_id=?)";

		// log.info("Executing query update " + q);

		PreparedStatement stm = con.prepareStatement(q);

		stm.setString(1, id);

		stm.setString(2, qString);

		stm.setString(3, desc);

		stm.setString(4, id);

		stm.setString(5, id);

		stm.setString(6, qString);

		stm.setString(7, desc);

		stm.setString(8, id);

		stm.executeUpdate();

		// q = "INSERT INTO value(query_id, num, date) values ('" + id + "','" + result + "','" + date + "');";

		q = "INSERT INTO " + this.historySchema + ".value(query_id, num, date) values (?,?,?);";

		stm.clearParameters();

		stm = con.prepareStatement(q);

		stm.setString(1, id);

		stm.setString(2, result);

		stm.setString(3, date);

		//log.info("Executing query update " + q);

		stm.executeUpdate();

		stm.close();

		con.close();

	}

	private boolean validate(int sqlResult, int cqlResult) {

		  log.debug(" Validation input  ->  sql : " + sqlResult + "  cql : " + cqlResult);

		if (sqlResult == cqlResult) {

			return true;

		}

		return false;

	}

	private Map<String, Integer> executeCQLBrowseQuery(String cql, String groupBy) {

		// TODO implement me please

		//oaftype=result and deletedbyinferece=false and type=publication , groupby = access_mode

		// searchServiceServiceLocator.getService().refine();

		//public SearchResult refine(String queryText, String transformer,

		//String locale, Collection<String> fields) throws SearchServiceException;

		//    public SearchResult refine(String queryText, String transformer,

		//          String locale, Collection<String> fields) throws SearchServiceException;

		//[2:52:39 PM] Antonis Lempesis:

		//query=   oaftype=result and deletedbyinferece=false and type=publication

		//transformer = results_openaire

//        locale=UTF-8

		//      fields = access_mode

//        set to 1 page and results

		//       public SearchResult search(String queryText, String transformer,

		//             String locale, int page, int size) throws SearchServiceException;

		throw new UnimplementedException();

	}

	private Map<String, Integer> executeSQLBrowseQuery(String sql) {

		// TODO implement me please

		throw new UnimplementedException();

	}

	private int executeCQLCountQuery(String cql) throws Exception {

		try {

			if (cql == null || cql.isEmpty() || cql.equalsIgnoreCase("not available")) {

				return 0;

			}

			SearchResult result = searchWebService.search(cql, "results_openaire", "en_GB", 1, 1);

			return result.getTotal();

		} catch (Exception e) {

			log.error("Could not execute CQL query. Reason: " + e);

			throw new Exception("Could not execute CQL query. Reason: ", e);

		}

	}

	private int executeSQLCountQuery(String sql) throws Exception {

		Connection con = null;

		try {

			con = dataSource.getConnection();

			Statement st = con.createStatement();

			if (st.execute(sql)) {

				ResultSet rs = st.getResultSet();

				int res = getResult(rs);

				st.close();

				return res;

			} else {

				log.error("Fail to execute command  " + sql + "  " + st.getWarnings());

				throw new Exception("Fail to execute command " + sql + "  " + st.getWarnings());

			}

		} catch (Exception e) {

			log.error("Could not execute sql query  " + sql + " : " + e);

			throw new Exception("Could not execute sql query  " + sql + " : ", e);

		} finally {

			if (con != null) {

				con.close();

			}

		}

	}

	private int getResult(ResultSet rs) throws Exception {

		HashMap<String, Integer> data = new HashMap<String, Integer>();

		int res = -1;

		try {

			ResultSetMetaData rsmd = rs.getMetaData();

			if (rs.next()) {

				res = (Integer.valueOf(rs.getString(1)));

				rs.close();

			}

			return res;

		} catch (Exception e) {

			log.error("Could not process results :" + e);

			throw new Exception("Could not process results :", e);

		}

	}

	private HashMap<String, Integer> getResults(ResultSet rs) throws Exception {

		HashMap<String, Integer> data = new HashMap<String, Integer>();

		try {

			ResultSetMetaData rsmd = rs.getMetaData();

			while (rs.next()) {

				for (int i = 1; i < rsmd.getColumnCount() - 1; i++) {

					data.put(rsmd.getColumnName(i), rs.getInt(i));

				}

			}

			return data;

		} catch (Exception e) {

			log.error("Could not process results :" + e);

			throw new Exception("Could not process results :", e);

		}

	}

	public ValidationReport getValidationReport() {

		return validationReport;

	}

	public void setValidationReport(ValidationReport validationReport) {

		this.validationReport = validationReport;

	}

	public Resource getValidationQueriesFile() {

		return validationQueriesFile;

	}

	public void setValidationQueriesFile(Resource validationQueriesFile) {

		this.validationQueriesFile = validationQueriesFile;

	}

	public DataSource getDataSource() {

		return dataSource;

	}

	public void setDataSource(DataSource dataSource) {

		this.dataSource = dataSource;

	}

	public String getShadowSearchURL() {

		return shadowSearchURL;

	}

	public void setShadowSearchURL(String shadowSearchURL) {

		this.shadowSearchURL = shadowSearchURL;

	}

	public static SearchWebService getSearchWebService() {

		return searchWebService;

	}

	public static void setSearchWebService(SearchWebService searchWebService) {

		Validator.searchWebService = searchWebService;

	}

	public String getHistorySchema() {

		return historySchema;

	}

	public void setHistorySchema(String historySchema) {

		this.historySchema = historySchema;

	}

}