D-Net

{

  "type_source": "SVN", 

  "goal": "package -U -T 4C source:jar", 

  "url": "http://svn-public.driver.research-infrastructures.eu/driver/dnet40/modules/uoa-stats-service/trunk",

  "deploy_repository": "dnet4-snapshots", 

  "version": "4", 

  "mail": "antleb@di.uoa.gr, kiatrop@di.uoa.gr, gkatsari@di.uoa.gr",

  "deploy_repository_url": "http://maven.research-infrastructures.eu/nexus/content/repositories/dnet4-snapshots", 

  "name": "uoa-stats-service"

}

package eu.dnetlib.data.statsmanager;

import org.apache.log4j.Logger;

import javax.xml.bind.annotation.XmlRootElement;

@XmlRootElement(name = "Query")

public class Query {

    private String id;

    private String name;

    private int dbResult;

    private int cqlResult;

    private statusFlag status;

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

    public Query() {};

    public Query(String id, String key, Integer dbResult, int cqlResult, boolean qStatus) {

        this.id = id;

        this.name = key;

        this.dbResult = dbResult;

        this.cqlResult = cqlResult;

        if (qStatus) {

            this.status = statusFlag.success;

        } else {

            this.status = statusFlag.fail;

        }

     //   log.debug("Created Query: " + this.toString());

    }

    public enum statusFlag {success, fail}

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public int getDbResult() {

        return dbResult;

    }

    public void setDbResult(int dbResult) {

        this.dbResult = dbResult;

    }

    public int getCqlResult() {

        return cqlResult;

    }

    public void setCqlResult(int cqlResult) {

        this.cqlResult = cqlResult;

    }

    public String getId() {

        return id;

    }

    public void setId(String id) {

        this.id = id;

    }

    public statusFlag getStatus() {

        return status;

    }

    public void setStatus(statusFlag status) {

        this.status = status;

    }

    @Override

    public String toString() {

        return "Query{" +

                "id='" + id + '\'' +

                ", name='" + name + '\'' +

                ", dbResult=" + dbResult +

                ", cqlResult=" + cqlResult +

                ", status=" + status +

                '}';

    }

}

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;

	}

}

package eu.dnetlib.data.statsmanager;

import org.apache.log4j.Logger;

import javax.xml.bind.annotation.XmlRootElement;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.Map;

/**

 * Created by antleb on 10/25/14.

 */

@XmlRootElement(name = "ValidationReport")

public class ValidationReport {

    private ArrayList<Query> Queries;

    private Date validationDate;

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

    public ValidationReport() {

        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

        this.validationDate = new Date();

        Queries = new ArrayList<Query>();

    }

    public void addQuery(String id,String name, int sqlResult, int cqlResult, boolean status)

    {

        Query q = new Query(id,name, sqlResult, cqlResult, status);

        this.getQueries().add(q);

    }

    /*

    DB validation report. Contains:

	- queries:

	  - query name

	  - db result

	  - cql result

	  - status (success/fail)

	- metadata:

	  - validation date

	  - ???

	 */

    public ArrayList<Query> getQueries() {

        return Queries;

    }

    public void setQueries(ArrayList<Query> queries) {

        Queries = queries;

    }

    @Override

    public String toString() {

        return "ValidationReport{" +

                "Queries=" + Queries +

                ", validationDate=" + validationDate +

                '}';

    }

    public Date getValidationDate() {

        return validationDate;

    }

    public void setValidationDate(Date validationDate) {

        this.validationDate = validationDate;

    }

}

package eu.dnetlib.data.statsmanager;

import org.apache.log4j.Logger;

import org.apache.tools.ant.util.Base64Converter;

import javax.net.ssl.HttpsURLConnection;

import java.io.BufferedReader;

import java.io.InputStreamReader;

import java.net.URL;

import java.net.URLConnection;

import java.util.HashMap;

import java.util.Map;

public class CacheController {

    private HashMap<String, URL> actions;

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

    private String httpsCredentials;

    public CacheController() {

    }

    public void executeCommand(String action, Map<String, String> parameters) throws Exception {

        /*if (cacheURL != null && !cacheURL.isEmpty()) {

            log.debug("existing cache url " + this.getActions().get(action));

            String actionUrl = this.getActions().get(action).toString();

            actionUrl = actionUrl.substring(actionUrl.lastIndexOf("/"), actionUrl.length());

            url = new URL(cacheURL + actionUrl);

            log.info("creds for url " + url + "  " + credentials);

            executeRemoteScript(url, credentials);

        } else {

          */

        String urlString = this.actions.get(action).toString();

        for (Map.Entry<String, String> e : parameters.entrySet()) {

            if (!e.getKey().equals("cache")&&!e.getKey().equals("error")) {

                urlString += "&" + e.getKey() + "=" + e.getValue();

            }

        }

        // credentials = this.cacheCredInfo.get(url.toString().substring(0, url.toString().lastIndexOf("/")));

        executeRemoteScript(new URL(urlString));

    }

    private void executeRemoteScript(URL url) throws Exception {

        try {

            Base64Converter converter = new Base64Converter();

            BufferedReader in = null;

            if (url.toString().startsWith("https://")) {

                String encoding = converter.encode(httpsCredentials.getBytes("UTF-8"));

                log.debug("Using https : " + url.toString());

                HttpsURLConnection yc = (HttpsURLConnection) url.openConnection();

                yc.setRequestProperty("Authorization", String.format("Basic %s", encoding));

                in = new BufferedReader(

                        new InputStreamReader(

                                yc.getInputStream()));

            } else {

                  log.debug("Using normal url : " + url.toString());

                URLConnection yc = (URLConnection) url.openConnection();

                in

                        = new BufferedReader(

                        new InputStreamReader(

                                yc.getInputStream()));

            }

            String inputLine;

            while ((inputLine = in.readLine()) != null)

                log.debug(inputLine);

            in.close();

        } catch (Exception e) {

            log.error("Error while calling php script over http. Reason: " + e);

            throw new Exception(e);

        }

    }

    public HashMap<String, URL> getActions() {

        return actions;

    }

    public void setActions(HashMap<String, URL> actions) {

        this.actions = actions;

    }

    public CacheController(HashMap<String, URL> actions) {

        this.actions = actions;

    }

    public String getHttpsCredentials() {

        return httpsCredentials;

    }

    public void setHttpsCredentials(String httpsCredentials) {

        this.httpsCredentials = httpsCredentials;

    }

}

package eu.dnetlib.data.statsmanager;

import org.apache.log4j.Logger;

import eu.dnetlib.enabling.tools.blackboard.BlackboardJob;

import eu.dnetlib.enabling.tools.blackboard.BlackboardNotificationHandler;

import eu.dnetlib.enabling.tools.blackboard.BlackboardServerHandler;

import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;

import java.sql.Connection;

import java.util.HashMap;

import java.util.Map;

public class StatsManager {

    private Validator validator;

    private ValidationReport validationReport;

    private DataSource dataSource;

    private CacheController prodCacheController;

    private CacheController testCacheController;

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

    public StatsManager() {

    }

    public ValidationReport validateDatabase() throws Exception {

        this.validationReport = validator.validateDatabase();

        return validationReport;

    }

    @Transactional

    public void promoteShadowSchema() throws Exception {

        Connection con = dataSource.getConnection();

        log.info("Backing up and Replacing public schema  with shadow in " + dataSource.getConnection().getMetaData().getURL() + " ...");

        con.createStatement().execute("drop schema if exists backup CASCADE ;");

        //con.createStatement().execute("alter schema public rename to backup ;");

        con.createStatement().execute("drop schema if exists public CASCADE ;");

        con.createStatement().execute("alter schema  shadow rename TO public ;");

        log.info("All ops done!");

        con.close();

    }

    public void executeCacheAction(BlackboardJob job) throws Exception {

        if (job.getAction().equals("refreshCache")) {

            executeCommand("refreshAll", job.getParameters());

        } else if (job.getAction().equals("refreshCharts")) {

            executeCommand(job.getAction(), job.getParameters());

        } else if (job.getAction().equals("refreshNums")) {

            executeCommand(job.getAction(), job.getParameters());

        } else if (job.getAction().equals("promoteShadow")) {

            executeCommand("promoteAll", job.getParameters());

            this.promoteShadowSchema();

        } else if (job.getAction().equals("promoteNums")) {

            executeCommand(job.getAction(), job.getParameters());

        } else if (job.getAction().equals("promoteCharts")) {

            executeCommand(job.getAction(), job.getParameters());

        } else if (job.getAction().equals("promoteCache")) {

            executeCommand("promoteAll", job.getParameters());

        } else if (job.getAction().equals("restore")) {

            executeCommand(job.getAction(), job.getParameters());

        } else if (job.getAction().equals("migrate")) {

            executeCommand(job.getAction(), job.getParameters());

        } else if (job.getAction().equals("backup")) {

            executeCommand(job.getAction(), job.getParameters());

        } else {

            log.error("Wrong action given ");

            throw new Exception("Wrong action given ");

        }

        log.info("Done ! ");

    }

    private void executeCommand(String action, Map<String, String> parameters) throws Exception {

        String cache = parameters.get("cache");

        log.info("Executing  action " + action + " in " + cache + "...");

        if (cache == null || cache.contains("test")) {

            testCacheController.executeCommand(action, parameters);

        } else if (cache.contains("beta") || (cache.contains("production"))) {

            prodCacheController.executeCommand(action, parameters);

        } else {

            throw new Exception("Wrong cache id");

        }

    }

    public DataSource getDataSource() {

        return dataSource;

    }

    public void setDataSource(DataSource dataSource) {

        this.dataSource = dataSource;

    }

    public Validator getValidator() {

        return validator;

    }

    public void setValidator(Validator validator) {

        this.validator = validator;

    }

    public ValidationReport getValidationReport() {

        return validationReport;

    }

    public void setValidationReport(ValidationReport validationReport) {

        this.validationReport = validationReport;

    }

    public CacheController getProdCacheController() {

        return prodCacheController;

    }

    public void setProdCacheController(CacheController prodCacheController) {

        this.prodCacheController = prodCacheController;

    }

    public CacheController getTestCacheController() {

        return testCacheController;

    }

    public void setTestCacheController(CacheController testCacheController) {

        this.testCacheController = testCacheController;

    }

}

package eu.dnetlib.data.statsmanager;

import eu.dnetlib.api.DriverService;

import eu.dnetlib.api.data.StatsManagerService;

import eu.dnetlib.domain.ActionType;

import eu.dnetlib.domain.ResourceType;

import eu.dnetlib.domain.enabling.Notification;

import gr.uoa.di.driver.app.DriverServiceImpl;

import gr.uoa.di.driver.enabling.issn.NotificationListener;

/**

 * Created by antleb on 10/25/14.

 */

public class StatsManagerServiceImpl extends DriverServiceImpl implements StatsManagerService {

    private StatsManagerServiceBlackboardHandler statsManagerServiceBlackboardHandler;

    public void init() {

        super.init();

        this.subscribe(

                ActionType.UPDATE,

                ResourceType.STATSMANAGERSERVICERESOURCETYPE,

                this.getServiceEPR().getParameter("serviceId"),

                "RESOURCE_PROFILE/BODY/BLACKBOARD/LAST_REQUEST",

                new NotificationListener() {

                    @Override

                    public void processNotification(Notification notification) {

                        statsManagerServiceBlackboardHandler.notified(

                                notification.getSubscriptionId(),

                                notification.getTopic(),

                                notification.getIsId(),

                                notification.getMessage());

                    }

                });

    }

    public StatsManagerServiceBlackboardHandler getStatsManagerServiceBlackboardHandler() {

        return statsManagerServiceBlackboardHandler;

    }

    public void setStatsManagerServiceBlackboardHandler(StatsManagerServiceBlackboardHandler statsManagerServiceBlackboardHandler) {

        this.statsManagerServiceBlackboardHandler = statsManagerServiceBlackboardHandler;

    }

}

package eu.dnetlib.data.statsmanager;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.xml.bind.JAXBContext;

import javax.xml.bind.JAXBException;

import java.io.IOException;

/**

 * Created by antleb on 10/25/14.

 */

@Controller

public class StatsManagerController {

    private StatsManager statsManager;

    @RequestMapping(value = "/stats", method = RequestMethod.GET)

    public void getReport(HttpServletRequest request, HttpServletResponse response) throws IOException, JAXBException {

        ValidationReport report = statsManager.getValidationReport();

        JAXBContext.newInstance().createMarshaller().marshal(report, response.getWriter());

    }

    public StatsManager getStatsManager() {

        return statsManager;

    }

    public void setStatsManager(StatsManager statsManager) {

        this.statsManager = statsManager;

    }

}

package eu.dnetlib.data.statsmanager;

import eu.dnetlib.enabling.tools.blackboard.BlackboardJob;

import eu.dnetlib.enabling.tools.blackboard.BlackboardNotificationHandler;

import eu.dnetlib.enabling.tools.blackboard.BlackboardServerHandler;

import org.apache.log4j.Logger;

import javax.xml.bind.JAXBContext;

import java.io.PrintWriter;

import java.io.StringWriter;

/**

 * Created by antleb on 10/25/14.

 */

public class StatsManagerServiceBlackboardHandler extends BlackboardNotificationHandler<BlackboardServerHandler> {

    private static Logger logger = Logger.getLogger(StatsManagerServiceBlackboardHandler.class);

    private StatsManager statsManager;

    protected void processJob(BlackboardJob job) {

        //TODO needs this?

        super.processJob(job);

       // logger.info("Beginning new job...");

       // logger.info("Id: " + job.getId() + " Date : " + job.getDate());

        String action = job.getAction();

        logger.info("Stats Manager :Got BB message: " + action);

        try {

            getBlackboardHandler().ongoing(job);

            if (action.equals("validate")) {

                ValidationReport report = statsManager.validateDatabase();

                StringWriter sw = new StringWriter();

                JAXBContext.newInstance(ValidationReport.class).createMarshaller().marshal(report, sw);

                job.getParameters().put("report", sw.toString());

            }

            else {

                statsManager.executeCacheAction(job);

            }

            getBlackboardHandler().done(job);

        } catch (

                Exception e

                )

        {

            getBlackboardHandler().failed(job, e);

            logger.error("Error processing job", e);

        }

    }

    public StatsManager getStatsManager() {

        return statsManager;

    }

    public void setStatsManager(StatsManager statsManager) {

        this.statsManager = statsManager;

    }

}

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE properties

		SYSTEM "http://java.sun.com/dtd/properties.dtd">

<properties>

    <entry key="queries.count">38</entry>

    <entry key="queries.schema">shadow</entry>

    <!--RESULTS-->

    <entry key="queries.1.type">search</entry>

    <entry key="queries.1.sql">select count(*)  as  results  from  schema.result</entry>

    <entry key="queries.1.cql.query">oaftype=result and deletedbyinference=false</entry>

    <entry key="queries.1.name">Results </entry>

    <entry key="queries.1.id">res</entry>

    <!--PROJECTS-->

    <entry key="queries.2.type">search</entry>

    <entry key="queries.2.sql">SELECT count(*) FROM schema.project</entry>

    <entry key="queries.2.cql.query">oaftype=project</entry>

    <entry key="queries.2.name">Projects </entry>

    <entry key="queries.2.id">proj</entry>

    <!--PUBS-->

    <entry key="queries.3.type">search</entry>

    <entry key="queries.3.sql">SELECT count(*) FROM schema.result where type='publication'</entry>

    <entry key="queries.3.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication</entry>

    <entry key="queries.3.name">Publications</entry>

    <entry key="queries.3.id">pubs</entry>

    <entry key="queries.4.type">search</entry>

    <entry key="queries.4.sql"> SELECT count(*)  FROM schema.result WHERE bestlicense='Open Access' and type='publication'</entry>

    <entry key="queries.4.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication and resultbestlicenseid exact "OPEN"</entry>

    <entry key="queries.4.name">Open Access Publications</entry>

    <entry key="queries.4.id">oapubs</entry>

    <entry key="queries.5.type">search</entry>

    <entry key="queries.5.sql">SELECT count(*) FROM schema.result WHERE bestlicense='Closed Access' and type='publication'</entry>

    <entry key="queries.5.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication and resultbestlicenseid exact "CLOSED"</entry>

    <entry key="queries.5.name"> Closed Access Publications</entry>

    <entry key="queries.5.id">noapubs</entry>

    <!--FP7-->

    <entry key="queries.6.type">search</entry>

    <entry key="queries.6.sql">SELECT count (distinct result_projects.id)   FROM schema.result, schema.result_projects, schema.project WHERE result.result_projects = result_projects.id and type='publication'

        and result_projects.project = project.id and funding_lvl0 = 'FP7'</entry>

    <entry key="queries.6.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication and relfundinglevel0_id = "ec__________::EC::FP7"</entry>

    <entry key="queries.6.name">FP7 Publications</entry>

    <entry key="queries.6.id">fp7pubstotal</entry>

<!-- ignore duplicate -->

    <entry key="queries.7.type">browse</entry>

    <entry key="queries.7.sql">SELECT count (distinct result_projects.id) FROM schema.result, schema.result_projects, schema.project WHERE result.result_projects = result_projects.id and type='publication' and result_projects.project = project.id and funding_lvl0 = 'FP7'</entry>

    <entry key="queries.7.cql.query"></entry>

    <entry key="queries.7.name"></entry>

    <entry key="queries.7.id"></entry>

    <!-- ignore duplicate -->

    <entry key="queries.8.type">search</entry>

    <entry key="queries.8.sql">SELECT count (distinct result_projects.id)   FROM schema.result, schema.result_projects, schema.project WHERE result.result_projects = result_projects.id AND result_projects.project = project.id AND funding_lvl0 = 'FP7' AND bestlicense='Closed Access' AND type='publication'</entry>

    <entry key="queries.8.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication and  relfundinglevel0_id = "ec__________::EC::FP7"  and resultbestlicenseid exact "CLOSED" </entry>

    <entry key="queries.8.name">FP7 Closed Access Publications</entry>

    <entry key="queries.8.id"> </entry>

    <entry key="queries.8.id">fp7noapubs</entry>

    <entry key="queries.9.type">search</entry>

    <entry key="queries.9.sql"> SELECT count(distinct result_projects.id) FROM schema.result, schema.result_projects,  schema.project WHERE result_projects = result_projects.id AND result_projects.project = project.id and type='publication' and funding_lvl0 = 'FP7' and bestlicense='Open Access'</entry>

    <entry key="queries.9.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication and relfundinglevel0_id = "ec__________::EC::FP7" and resultbestlicenseid exact "OPEN" </entry>

    <entry key="queries.9.name">FP7 Open Access Publications </entry>

    <entry key="queries.9.id">fp7oapubs</entry>

    <entry key="queries.10.type">search</entry>

    <entry key="queries.10.sql"> SELECT count(distinct result_projects.id)  FROM schema.result, schema.result_projects, schema.project

        WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'FP7' and bestlicense='Restricted' and type='publication';</entry>

    <entry key="queries.10.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication and relfundinglevel0_id = "ec__________::EC::FP7" and resultbestlicenseid exact "RESTRICTED" </entry>

    <entry key="queries.10.name">FP7 Restricted Access Publications </entry>

    <entry key="queries.10.id">fp7respubs </entry>

    <entry key="queries.11.type">search</entry>

    <entry key="queries.11.sql">SELECT count(distinct result_projects.id) FROM schema.result, schema.result_projects, schema.project

        WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'FP7' and bestlicense='Embargo' and type='publication' </entry>

    <entry key="queries.11.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication and relfundinglevel0_id = "ec__________::EC::FP7" and resultbestlicenseid exact "EMBARGO"</entry>

    <entry key="queries.11.name">FP7 Embargo Access Publications </entry>

    <entry key="queries.11.id">fp7embpubs</entry>

    <entry key="queries.12.type">search</entry>

    <entry key="queries.12.sql">SELECT count(distinct project.id)  from  schema.result,  schema.result_projects,  schema.project  WHERE result.result_projects = result_projects.id and type='publication' and result_projects.project = project.id and funding_lvl0='FP7' ;</entry>

    <entry key="queries.12.cql.query">not available</entry>

    <entry key="queries.12.name">FP7 Projects with  Publications </entry>

    <entry key="queries.12.id">fp7projpubs</entry>

    <entry key="queries.13.type">search</entry>

    <entry key="queries.13.sql"> SELECT count(id)  FROM schema.project WHERE funding_lvl0 = 'FP7';</entry>

    <entry key="queries.13.cql.query">oaftype=project and fundinglevel0_name=FP7</entry>

    <entry key="queries.13.name">FP7 Projects </entry>

    <entry key="queries.13.id">fp7projtotal</entry>

    <entry key="queries.14.type">search</entry>

    <entry key="queries.14.sql"> SELECT count(number)  from schema.project where funding_lvl0='FP7' and sc39='yes'; </entry>

    <entry key="queries.14.cql.query">oaftype=project and fundinglevel0_name=FP7 and projectecsc39=true</entry>

    <entry key="queries.14.name"> FP7  Projects with SC39 </entry>

    <entry key="queries.14.id">sc39fp7projtotal</entry>

    <entry key="queries.15.type">search</entry>

    <entry key="queries.15.sql"> SELECT count(distinct project.id) FROM  schema.result, schema.result_projects, schema.project WHERE result_projects.project=project.id and funding_lvl0 = 'FP7' and sc39='yes' and result.result_projects = result_projects.id and type='publication';</entry>

    <entry key="queries.15.cql.query">not available</entry>

    <entry key="queries.15.name">FP7 Projects with SC39 that have publications </entry>

    <entry key="queries.15.id">sc39fp7projpubs</entry>

    <entry key="queries.16.type">search</entry>

    <entry key="queries.16.sql"> SELECT count(distinct result_projects.id) FROM schema.result, schema.result_projects, schema.project

        WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'FP7' and bestlicense='Open Access' and sc39='yes' and type='publication';</entry>

    <entry key="queries.16.cql.query">not available</entry>

    <entry key="queries.16.name"> OA publications in FP7 Projects with SC39 </entry>

    <entry key="queries.16.id">sc39fp7oapubs</entry>

   <!-- WT -->

    <entry key="queries.17.type">search</entry>

    <entry key="queries.17.sql"> SELECT count(id)   FROM schema.project WHERE funder = 'Wellcome Trust';</entry>

    <entry key="queries.17.cql.query">oaftype=project and fundername exact Wellcome Trust </entry>

    <entry key="queries.17.name">WT Projects  </entry>

    <entry key="queries.17.id">wtprojtotal</entry>

    <entry key="queries.18.type">search</entry>

    <entry key="queries.18.sql"> SELECT count(distinct project.id)  FROM schema.result, schema.project, schema.result_projects

        where result_projects.project = project.id and funder = 'Wellcome Trust'  and result.result_projects = result_projects.id and type='publication';</entry>

    <entry key="queries.18.cql.query">not available</entry>

    <entry key="queries.18.name">WT Projects with Publications</entry>

    <entry key="queries.18.id">wtprojpubs</entry>

    <entry key="queries.19.type">search</entry>

    <entry key="queries.19.sql">  SELECT count(distinct result_projects.id)    FROM schema.result, schema.result_projects, schema.project WHERE result.result_projects= result_projects.id  AND result_projects.project = project.id and funder = 'Wellcome Trust'  and type='publication';</entry>

    <entry key="queries.19.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication  and relfunderid exact "wt__________::WT" </entry>

    <entry key="queries.19.name"> Publications in WT Projects </entry>

    <entry key="queries.19.id">wtpubs</entry>

    <entry key="queries.20.type">search</entry>

    <entry key="queries.20.sql">  SELECT count(distinct result_projects.id)    FROM schema.result, schema.result_projects, schema.project WHERE result.result_projects= result_projects.id

        AND result_projects.project = project.id and funder = 'Wellcome Trust' and bestlicense='Open Access' and type='publication';</entry>

    <entry key="queries.20.cql.query">oaftype=result and deletedbyinference=false and resulttypeid=publication and  relfunderid exact "wt__________::WT"  and resultbestlicenseid exact "OPEN"</entry>

    <entry key="queries.20.name"> Open Access Publications in WT Projects </entry>

    <entry key="queries.20.id">wtoapubs</entry>

    <entry key="queries.21.type">search</entry>

    <entry key="queries.21.sql">  SELECT count(distinct result_projects.id)   FROM schema.result, schema.result_projects, schema.project WHERE result.result_projects = result_projects.id AND

   result_projects.project = project.id and funder = 'Wellcome Trust' and bestlicense='Restricted' and type='publication';</entry>

    <entry key="queries.21.cql.query"> oaftype=result and deletedbyinference=false and resulttypeid=publication and relfunderid exact "wt__________::WT" and resultbestlicenseid exact "RESTRICTED"</entry>

    <entry key="queries.21.name">Restricted Access Publications in WT Projects </entry>

    <entry key="queries.21.id">wtrespubs</entry>

    <entry key="queries.22.type">search</entry>

    <entry key="queries.22.sql">   SELECT count(distinct result_projects.id)   FROM schema.result,  schema.result_projects,  schema.project

        WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funder = 'Wellcome Trust'        and bestlicense='Embargo' and type='publication';</entry>

    <entry key="queries.22.cql.query"> oaftype=result and deletedbyinference=false and resulttypeid=publication and relfunderid exact "wt__________::WT" and resultbestlicenseid exact "EMBARGO"</entry>

    <entry key="queries.22.name">Embargo Publications in WT Projects</entry>

    <entry key="queries.22.id">wtembpubs</entry>

<!--ERC-->

    <entry key="queries.23.type">search</entry>

    <entry key="queries.23.sql">SELECT count(id) FROM schema.project WHERE funding_lvl2 = 'ERC';</entry>

    <entry key="queries.23.cql.query">oaftype=project and fundinglevel2_name exact "ERC"</entry>

    <entry key="queries.23.name"> Projects with ERC funding </entry>

    <entry key="queries.23.id">ercprojtotal</entry>

    <entry key="queries.24.type">search</entry>

    <entry key="queries.24.sql">SELECT count(distinct project.id) FROM schema.result,  schema.project,  schema.result_projects

        where result_projects.project = project.id and project.funding_lvl2='ERC' and result.result_projects = result_projects.id and type='publication';</entry>

    <entry key="queries.24.cql.query">not available</entry>

    <entry key="queries.24.name"> ERC Projects with Publications</entry>

    <entry key="queries.24.id">ercprojpubs</entry>

    <entry key="queries.25.type">search</entry>

    <entry key="queries.25.sql">SELECT count(distinct result_projects.id) FROM schema.result,

        schema.result_projects, schema.project WHERE result.result_projects= result_projects.id

        AND result_projects.project = project.id and funding_lvl2 = 'ERC' and bestlicense='Open Access' and type='publication';</entry>

    <entry key="queries.25.cql.query">resulttypeid=publication and deletedbyinference=false and conceptname=ERC and resultbestlicenseid=OPEN</entry>

    <entry key="queries.25.name">Open Access Publications in ERC Projects </entry>

    <entry key="queries.25.id">ercoapubs</entry>

    <entry key="queries.26.type">search</entry>

    <entry key="queries.26.sql"> SELECT count(distinct result_projects.id) FROM schema.result, schema.result_projects, schema.project WHERE

    result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl2 = 'ERC' and bestlicense='Restricted' and type='publication'; </entry>

    <entry key="queries.26.cql.query">resulttypeid=publication and deletedbyinference=false and conceptname=ERC and resultbestlicenseid=RESTRICTED</entry>

    <entry key="queries.26.name"> Restricted Publications in ERC Projects </entry>

    <entry key="queries.26.id">ercrespubs</entry>

    <entry key="queries.27.type">search</entry>

    <entry key="queries.27.sql"> SELECT count(distinct result_projects.id)  FROM schema.result, schema.result_projects, schema.project

        WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funding_lvl2 = 'ERC' and bestlicense='Embargo' and type='publication'; </entry>

    <entry key="queries.27.cql.query">resulttypeid=publication and deletedbyinference=false and conceptname=ERC and resultbestlicenseid=EMBARGO</entry>

    <entry key="queries.27.name">Embargo Publications in ERC Projects </entry>

    <entry key="queries.27.id">ercembpubs</entry>

    <entry key="queries.28.type">search</entry>

    <entry key="queries.28.sql"> SELECT count(distinct result_projects.id) FROM schema.result, schema.project, schema.result_projects where result_projects.project = project.id and project.funding_lvl2='ERC' and result.result_projects = result_projects.id and type='publication'; </entry>

    <entry key="queries.28.cql.query">resulttypeid=publication and deletedbyinference=false and conceptname=ERC</entry>

    <entry key="queries.28.name">Total Publications in ERC Projects</entry>

    <entry key="queries.28.id">ercpubs</entry>

   <!--DATASOURCES-->

    <entry key="queries.29.type">search</entry>

    <entry key="queries.29.sql">SELECT count(*) FROM schema.datasource</entry>

    <entry key="queries.29.cql.query">oaftype=datasource </entry>

    <entry key="queries.29.name">Total Datasources </entry>

    <entry key="queries.29.id">datasrc</entry>

    <entry key="queries.30.type">search</entry>

    <entry key="queries.30.sql">select count(*) from  schema.datasource where compatibility != 'not available' and compatibility != 'under validation'</entry>

    <entry key="queries.30.cql.query"> oaftype=datasource and datasourcecompatibilityid &#60;&#62; UNKNOWN and datasourcecompatibilityid &#60;&#62; notCompatible</entry>

    <entry key="queries.30.name">Valid Datasources</entry>

    <entry key="queries.30.id">dtsrcpubs</entry>

    <entry key="queries.31.type">search</entry>

    <entry key="queries.31.sql"> SELECT count(distinct rd.datasource) from schema.result_datasources rd ;</entry>

    <entry key="queries.31.cql.query">not available</entry>

    <entry key="queries.31.name">Datasources with Publications</entry>

    <entry key="queries.31.id">datasrc_withpubs</entry>

    <entry key="queries.32.type">search</entry>

    <entry key="queries.32.sql">select count(*) from schema.datasource where type='Journal Platform';</entry>

    <entry key="queries.32.cql.query">oaftype=datasource and datasourcetypeid=pubsrepository::journal</entry>

    <entry key="queries.32.name">Journal Datasources</entry>

    <entry key="queries.32.id">datasrc_journals</entry>

    <entry key="queries.33.type">search</entry>

    <entry key="queries.33.sql">select count(*) from schema.datasource where type='Publication Repository';</entry>

    <entry key="queries.33.cql.query">oaftype=datasource and datasourcetypeid=pubsrepository::unknown</entry>

    <entry key="queries.33.name">Publication Repository Datasources</entry>

    <entry key="queries.33.id">datasrc_pubrepo</entry>

    <entry key="queries.34.type">search</entry>

    <entry key="queries.34.sql">select count(*) from schema.datasource where type='Data Repository';</entry>

    <entry key="queries.34.cql.query">oaftype=datasource and datasourcetypeid=datarepository::unknown</entry>

    <entry key="queries.34.name">Data Repository Datasources</entry>

    <entry key="queries.34.id">datasrc_datarepo</entry>

    <entry key="queries.35.type">search</entry>

    <entry key="queries.35.sql">select count(*) from schema.datasource where type like 'Aggregator%';</entry>

    <entry key="queries.35.cql.query">oaftype=datasource and datasourcetypeid=aggregator::pubsrepository::unknown OR  datasourcetypeid=aggregator::pubsrepository::journals OR   datasourcetypeid=aggregator::datarepository</entry>

    <entry key="queries.35.name">Aggregator Datasources </entry>

    <entry key="queries.35.id">datasrc_aggr</entry>

    <entry key="queries.36.type">search</entry>

    <entry key="queries.36.sql">select count(distinct id) from schema.result where type='dataset';</entry>

    <entry key="queries.36.cql.query">resulttypeid=dataset</entry>

    <entry key="queries.36.name"> Total number of datasets </entry>

    <entry key="queries.36.id">data_total</entry>

    <entry key="queries.37.type">search</entry>

    <entry key="queries.37.sql">SELECT count(distinct funder) FROM schema.project</entry>

    <entry key="queries.37.cql.query">not available</entry>

    <entry key="queries.37.name">Funders</entry>

    <entry key="queries.37.id">funders</entry>

    <entry key="queries.38.type">search</entry>

    <entry key="queries.38.sql">  select count(*) from (select dor.organization as organization from schema.datasource_organizations dor join schema.result_datasources rd on rd.datasource=dor.id join schema.result r on r.id=rd.id where r.type='publication'

        union select por.organization as organization from schema.project_organizations por join schema.result_projects rp on rp.project=por.id join

        schema.result r on r.id=rp.id where r.type='publication') as foo;</entry>

    <entry key="queries.38.cql.query">not available</entry>

    <entry key="queries.38.name">Organizations with Publications </entry>

    <entry key="queries.38.id">org_withpubs</entry>

<!--

    <entry key="queries.2.type">browse</entry>

    <entry key="queries.2.sql">select count(id), access_type from schema.result group by access_type order by count(id)</entry>

    <entry key="queries.2.cql.query">oaftype=result and deletedbyinference=false</entry>

    <entry key="queries.2.cql.groupby">access_mode</entry>

    <entry key="queries.2.name"> Access Level Modes </entry>

    <entry key="queries.2.id"> Access Level Modes </entry>

-->

</properties>

services.statsManager.serviceName = uoa-stats-service

services.statsManager.db.driverClassName = org.postgresql.Driver

services.statsManager.db.url = jdbc:postgresql://duffy.di.uoa.gr:5432/stats

services.statsManager.db.username = sqoop

services.statsManager.db.password = sqoop

services.statsManager.historySchema = "stats_history"

services.statsManager.validationQueriesFile = classpath:/eu/dnetlib/data/statsmanager/validationQueries.xml

services.statsManager.actions.refreshAll=cacheController?action=refreshAll

services.statsManager.actions.refreshNums =cacheController?action=refreshNums

services.statsManager.actions.refreshCharts =cacheController?action=refreshCharts

services.statsManager.actions.promoteNums =cacheController?action=promoteNums

services.statsManager.actions.promoteCharts =cacheController?action=promoteCharts

services.statsManager.actions.promoteAll =cacheController?action=promoteAll

services.statsManager.actions.backup =cacheController?action=backup

services.statsManager.actions.migrate =cacheController?action=migrate

services.statsManager.actions.restore =cacheController?action=restore

services.statsManager.shadowSearchServiceURL=http://duffy.di.uoa.gr:8080/shadowSearch/services/searchWebService

services.statsManager.actions.prodURL=https://beta.openaire.eu/stats/

services.statsManager.actions.testURL=https://test.openaire.eu/stats/

services.statsManager.testhttpsCredentials =eri.katsari:UDxG2ydA

services.statsManager.prodhttpsCredentials = :

#DOES NOT DELETE SERVICE PROFILE  OR RE-DEPLOY

services.registration.default.removeRegistration=false

#Search Service

#services.search.indexMode = solr

#services.search.mdFormat = TMF

#services.search.vocabulariesPath = /tmp/vocabularies/

services.search.infrastructure = shadow

--

-- PostgreSQL database dump

--

DROP SCHEMA  IF EXISTS stats_history CASCADE;

CREATE SCHEMA stats_history;

ALTER SCHEMA stats_history OWNER TO sqoop;

--

-- Name: query; Type: TABLE; Schema: public; Owner: postgres; Tablespace:

--

CREATE TABLE stats_history.query (

    query text,

    description text,

    query_id text

);

alter table stats_history.query owner to sqoop;

--

-- Name: value; Type: TABLE; Schema: public; Owner: postgres; Tablespace:

--

CREATE TABLE stats_history.value (

    num text,

    date text,

    query_id text

);

alter table stats_history.value owner to sqoop;

--

-- Name: public; Type: ACL; Schema: -; Owner: postgres

--

--REVOKE ALL ON SCHEMA stats_history FROM PUBLIC;

--REVOKE ALL ON SCHEMA stats_history FROM sqoop;

--GRANT ALL ON SCHEMA stats_history TO sqoop;

--GRANT ALL ON SCHEMA stats_history TO PUBLIC;

--

-- PostgreSQL database dump complete

--

#Generated by ANT

name=uoa-stats-service

version=1.0.0

label=