D-Net

SELECT * FROM dsm_apiparams WHERE api = :apiId AND lower(param) = lower(:param)

dnet.openaire.db.url      = jdbc:postgresql://localhost:5432/dnet_openaire

dnet.openaire.db.username = dnet

dnet.openaire.db.password =

package eu.dnetlib.enabling.datasources;

import java.text.ParseException;

import java.util.Date;

import java.util.List;

import java.util.Map;

import java.util.stream.Collectors;

import org.apache.commons.io.IOUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.quartz.CronExpression;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Required;

import org.springframework.core.io.ClassPathResource;

import org.springframework.core.io.Resource;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import org.springframework.transaction.annotation.Transactional;

import com.google.common.collect.ImmutableMap;

import eu.dnetlib.enabling.datasources.common.Api;

import eu.dnetlib.enabling.datasources.common.ApiParam;

import eu.dnetlib.enabling.datasources.common.Datasource;

import eu.dnetlib.enabling.datasources.common.DsmException;

import eu.dnetlib.enabling.datasources.common.Identity;

import eu.dnetlib.enabling.datasources.common.Organization;

import eu.dnetlib.enabling.datasources.common.PidSystem;

import eu.dnetlib.enabling.is.lookup.rmi.ISLookUpDocumentNotFoundException;

import eu.dnetlib.enabling.is.lookup.rmi.ISLookUpException;

import eu.dnetlib.enabling.is.lookup.rmi.ISLookUpService;

import eu.dnetlib.enabling.locators.UniqueServiceLocator;

public class DatasourceManagerClients {

	private static final Log log = LogFactory.getLog(DatasourceManagerClients.class);

	private static final Resource dsQuery = new ClassPathResource(LocalOpenaireDatasourceManagerImpl.QUERY_BASEDIR + "getDatasource.sql");

	private static final Resource dsQueryByPrefix = new ClassPathResource(LocalOpenaireDatasourceManagerImpl.QUERY_BASEDIR + "getDatasourceByPrefix.sql");

	private static final Resource dsIdentitiesQuery = new ClassPathResource(LocalOpenaireDatasourceManagerImpl.QUERY_BASEDIR + "dsIdentitiesQuery.sql");

	private static final Resource dsOrganizationsQuery = new ClassPathResource(LocalOpenaireDatasourceManagerImpl.QUERY_BASEDIR + "dsOrganizationsQuery.sql");

	private static final Resource listApisByDsId = new ClassPathResource(LocalOpenaireDatasourceManagerImpl.QUERY_BASEDIR + "listApisByDsId.sql");

	private static final Resource isDefinedParamQuery = new ClassPathResource(LocalOpenaireDatasourceManagerImpl.QUERY_BASEDIR + "isDefinedParam.sql");

	private NamedParameterJdbcTemplate jdbcTemplate;

	@Autowired

	private UniqueServiceLocator serviceLocator;

	@Transactional(readOnly = true)

	public List<Map<String, Object>> searchSQL(final String sql, final Map<String, Object> sqlParams) throws DsmException {

		try {

			log.debug("Executing SQL: " + sql);

			return jdbcTemplate.queryForList(sql, sqlParams);

		} catch (final Exception e) {

			log.error("Error executing sql", e);

			throw new DsmException(-1, "Error obtaining datasources from db", e);

		}

	}

	@Transactional(readOnly = true)

	public List<Map<String, Object>> searchSQL(final Resource sqlResource, final Map<String, Object> sqlParams) throws DsmException {

		try {

			return searchSQL(IOUtils.toString(sqlResource.getInputStream()), sqlParams);

		} catch (final Exception e) {

			log.error("Error executing sql", e);

			throw new DsmException(-1, "Error obtaining datasources from db", e);

		}

	}

	@Transactional

	public void updateSQL(final String dsId, final String sql, final Map<String, Object> sqlparams)

		throws DsmException {

		log.debug("Executing query SQL: " + sql);

		jdbcTemplate.update(sql, sqlparams);

	}

	@Transactional

	public void updateSQL(final String dsId, final Resource sqlResource, final Map<String, Object> sqlparams)

		throws DsmException {

		try {

			updateSQL(dsId, IOUtils.toString(sqlResource.getInputStream()), sqlparams);

		} catch (final Exception e) {

			log.error("Error in updateSQL", e);

			throw new DsmException(-1, "Error in updateSQL", e);

		}

	}

	@Transactional(readOnly = true)

	public Datasource<Organization<?>, Identity, PidSystem> getDatasourceById(final String id) throws DsmException {

		final List<Map<String, Object>> list = searchSQL(dsQuery, ImmutableMap.of("dsId", id));

		if (list.size() != 1) { throw new DsmException("Invalid number of ds with id: " + id); }

		final Datasource<Organization<?>, Identity, PidSystem> ds = DatasourceFunctions.mapToDatasource(list.get(0));

		ds.setIdentities(searchSQL(dsIdentitiesQuery, ImmutableMap.of("dsId", id))

			.stream()

			.map(DatasourceFunctions::mapToDsIdentity)

			.collect(Collectors.toSet()));

		ds.setOrganizations(searchSQL(dsOrganizationsQuery, ImmutableMap.of("dsId", id))

			.stream()

			.map(DatasourceFunctions::mapToDsOrganization)

			.collect(Collectors.toSet()));

		return ds;

	}

	public Datasource<Organization<?>, Identity, PidSystem> getDatasourceByPrefix(final String prefix) throws DsmException {

		final List<Map<String, Object>> list = searchSQL(dsQueryByPrefix, ImmutableMap.of("prefix", prefix));

		if (list.size() != 1) { throw new DsmException("Invalid number of ds with prefix: " + prefix); }

		final Datasource<Organization<?>, Identity, PidSystem> ds = DatasourceFunctions.mapToDatasource(list.get(0));

		ds.setIdentities(searchSQL(dsIdentitiesQuery, ImmutableMap.of("dsId", ds.getId()))

			.stream()

			.map(DatasourceFunctions::mapToDsIdentity)

			.collect(Collectors.toSet()));

		ds.setOrganizations(searchSQL(dsOrganizationsQuery, ImmutableMap.of("dsId", ds.getId()))

			.stream()

			.map(DatasourceFunctions::mapToDsOrganization)

			.collect(Collectors.toSet()));

		return ds;

	}

	@Transactional(readOnly = true)

	public List<Api<ApiParam>> getApis(final String dsId) throws DsmException {

		return searchSQL(listApisByDsId, ImmutableMap.of("dsId", dsId))

			.stream()

			.map(DatasourceFunctions::mapToApi)

			.collect(Collectors.toList());

	}

	@Transactional(readOnly = true)

	public boolean isDefinedParam(final String apiId, final String param) throws DsmException {

		return !searchSQL(isDefinedParamQuery, ImmutableMap.of("apiId", apiId, "param", param)).isEmpty();

	}

	public Date findNextScheduledExecution(final String dsId, final String ifaceId) throws DsmException {

		final String xquery = "/*[.//DATAPROVIDER/@interface='" + ifaceId + "' and .//SCHEDULING/@enabled='true']//CRON/text()";

		try {

			final String cronExpression = serviceLocator.getService(ISLookUpService.class).getResourceProfileByQuery(xquery);

			final CronExpression cron = new CronExpression(cronExpression);

			return cron.getNextValidTimeAfter(new Date());

		} catch (final ISLookUpDocumentNotFoundException e) {

			// When the value is not found a null value must be returned

			return null;

		} catch (final ISLookUpException e) {

			log.error("Error in xquery: " + xquery, e);

			throw new DsmException(-1, "Error in xquery: " + xquery, e);

		} catch (final ParseException e) {

			log.error("Error parsing cron expression", e);

			throw new DsmException(-1, "Error parsing cron expression", e);

		}

	}

	public NamedParameterJdbcTemplate getJdbcTemplate() {

		return jdbcTemplate;

	}

	@Required

	public void setJdbcTemplate(final NamedParameterJdbcTemplate jdbcTemplate) {

		this.jdbcTemplate = jdbcTemplate;

	}

}

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

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

	<parent>

		<groupId>eu.dnetlib</groupId>

		<artifactId>dnet45-parent</artifactId>

		<version>1.0.0</version>

  	</parent>

	<modelVersion>4.0.0</modelVersion>

	<groupId>eu.dnetlib</groupId>

	<artifactId>dnet-openaire-datasource-manager</artifactId>

	<packaging>jar</packaging>

	<version>2.1.0</version>

	<scm>

		<developerConnection>scm:svn:https://svn.driver.research-infrastructures.eu/driver/dnet45/modules/dnet-openaire-datasource-manager/tags/dnet-openaire-datasource-manager-2.1.0</developerConnection>

	</scm>

	<dependencies>

		<dependency>

			<groupId>eu.dnetlib</groupId>

			<artifactId>cnr-service-common</artifactId>

			<version>[2.1.0,3.0.0)</version>

		</dependency>

		<dependency>

			<groupId>eu.dnetlib</groupId>

			<artifactId>dnet-datasource-manager-common</artifactId>

			<version>[2.1.0,3.0.0)</version>

		</dependency>

		<dependency>

			<groupId>org.springframework</groupId>

			<artifactId>spring-tx</artifactId>

			<version>${spring.version}</version>

		</dependency>

		<dependency>

			<groupId>org.springframework</groupId>

			<artifactId>spring-jdbc</artifactId>

			<version>${spring.version}</version>

		</dependency>

		<dependency>

			<groupId>org.springframework</groupId>

			<artifactId>spring-context-support</artifactId>

			<version>${spring.version}</version>

		</dependency>

		<dependency>

  		    <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>${junit.version}</version>

            <scope>test</scope>

        </dependency>

	</dependencies>

</project>

SELECT 

	a.id                                                                     AS "id",

	a.protocol                                                               AS "protocol",

	coalesce(a.compatibility_override, a.compatibility)                      AS "compliance",

	a.active                                                                 AS "active",

	a.baseurl                                                                AS "baseUrl",

	coalesce(a.last_aggregation_date::text, '')                              AS "aggrDate",

	coalesce(a.last_aggregation_total, 0)                                    AS "aggrTotal",

	coalesce(a.last_aggregation_mdid, '')                                    AS "aggrMdId",

	coalesce(a.last_collection_date::text, '')                               AS "collDate",

	coalesce(a.last_collection_total, 0)                                     AS "collTotal",

	coalesce(a.last_collection_mdid, '')                                     AS "collMdId",

	ds.id                                                                    AS "dsId",

	ds.officialname                                                          AS "name",

	ds.englishname                                                           AS "alternativeName",

	ds.namespaceprefix                                                       AS "prefix",

	ds.websiteurl                                                            AS "websiteUrl",

	coalesce((array_agg(o.country))[1], '-')                                 AS "country",

    (array_agg(o.legalname))[1]                                              AS "organization",

    ds.consenttermsofuse                                                     AS "consenttermsofuse",

    ds.fulltextdownload                                                      AS "fulltextdownload"

FROM dsm_api a

	LEFT OUTER JOIN dsm_services ds ON (a.service = ds.id)

	LEFT OUTER JOIN dsm_service_organization dsorg ON (ds.id = dsorg.service)

	LEFT OUTER JOIN dsm_organizations o ON (dsorg.organization = o.id)

WHERE

	ds.dedup_main_service = true AND (

		ds.id                 ILIKE :value 

		OR ds.officialname    ILIKE :value

		OR ds.englishname     ILIKE :value

		OR ds.namespaceprefix ILIKE :value

		OR ds.websiteurl      ILIKE :value

		OR a.baseurl          ILIKE :value

		OR o.legalname        ILIKE :value

	)

GROUP BY

	a.id,

	a.protocol,

	a.compatibility_override,

	a.compatibility,

	a.active,

	a.last_aggregation_date,

	a.last_download_date,

	a.last_collection_date,

	a.last_aggregation_total,

	a.last_download_total,

	a.last_collection_total,

	a.last_aggregation_mdid,

	a.last_download_objid,

	a.last_collection_mdid,

	ds.id,

	ds.officialname,

	ds.englishname,

	ds.namespaceprefix,

	ds.websiteurl,

    ds.consenttermsofuse,

    ds.fulltextdownload

SELECT * FROM (SELECT 

	a.id                                                                     AS "id",

	a.protocol                                                               AS "protocol",

	coalesce(a.compatibility_override, a.compatibility)                      AS "compliance",

	a.active                                                                 AS "active",

	a.baseurl                                                                AS "baseUrl",

	ds.eosc_datasource_type                                                  AS "type",

	coalesce(a.last_aggregation_date::text, '')                              AS "aggrDate",

	coalesce(a.last_aggregation_total, 0)                                    AS "aggrTotal",

	coalesce(a.last_aggregation_mdid, '')                                    AS "aggrMdId",

	coalesce(a.last_collection_date::text, '')                               AS "collDate",

	coalesce(a.last_collection_total, 0)                                     AS "collTotal",

	coalesce(a.last_collection_mdid, '')                                     AS "collMdId",

	ds.id                                                                    AS "dsId",

	ds.officialname                                                          AS "name",

	ds.englishname                                                           AS "alternativeName",

	ds.namespaceprefix                                                       AS "prefix",

	ds.websiteurl                                                            AS "websiteUrl",

	coalesce((array_agg(o.country))[1], '-')                                 AS "country",

    (array_agg(o.legalname))[1]                                              AS "organization",

    ds.consenttermsofuse                                                     AS "consenttermsofuse",

    ds.fulltextdownload                                                      AS "fulltextdownload"

FROM dsm_api a

	LEFT OUTER JOIN dsm_services ds ON (a.service = ds.id)

	LEFT OUTER JOIN dsm_service_organization dsorg ON (ds.id = dsorg.service)

	LEFT OUTER JOIN dsm_organizations o ON (dsorg.organization = o.id)

WHERE ds.dedup_main_service = true

GROUP BY

	a.id,

	a.protocol,

	a.compatibility_override,

	a.compatibility,

	a.active,

	a.last_aggregation_date,

	a.last_download_date,

	a.last_collection_date,

	a.last_aggregation_total,

	a.last_download_total,

	a.last_collection_total,

	a.last_aggregation_mdid,

	a.last_download_objid,

	a.last_collection_mdid,

	ds.id,

	ds.officialname,

	ds.englishname,

	ds.namespaceprefix,

	ds.websiteurl,

	ds.consenttermsofuse,

    ds.fulltextdownload,

    eosc_datasource_type

) AS t WHERE

UPDATE dsm_api

SET (last_collection_mdid, last_collection_total, last_collection_date) = (:mdId, :total, :date)

WHERE id = :apiId AND service = :dsId;

UPDATE dsm_api

SET first_collection_date = :date

WHERE id = :apiId AND service = :dsId AND first_collection_date IS NULL;

SELECT

	org.id               AS id,

	org.legalshortname   AS legalshortname,

	org.legalname        AS legalname,

	org.websiteurl       AS websiteurl,

	org.logourl          AS logourl,

	org.country          AS country,

	org.collectedfrom    AS collectedfrom,

	org.dateofcollection AS dateofcollection,

	org.provenanceaction AS provenanceaction

FROM

	dsm_organizations org

	LEFT OUTER JOIN dsm_service_organization dso ON (org.id = dso.organization)

WHERE 

	dso.service = :dsId

INSERT INTO dsm_services(

	id,

	officialname,

	englishname,

	websiteurl,

	logourl,

	contactemail,

	latitude,

	longitude,

	timezone,

	namespaceprefix,

	languages,--array

	collectedfrom,

	dateofvalidation,

	provenanceaction,

	platform,

	activationid,

	description,

	releasestartdate,

	releaseenddate,

	missionstatementurl,

	databaseaccesstype,

	datauploadtype,

	databaseaccessrestriction,

	datauploadrestriction,

	citationguidelineurl,

	certificates,

	aggregator,

	issn,

	eissn,

	lissn,

	registeredby,

	subjects,--array

	managed,

	eosc_datasource_type,

	eosc_type

) VALUES (

	:id,

	:officialname,

	:englishname,

	:websiteurl,

	:logourl,

	:contactemail,

	:latitude,

	:longitude,

	:timezone,

	:namespaceprefix,

	:languages,--array

	:collectedfrom,

	:dateofvalidation,

	:provenanceaction,

	:platform,

	:activationid,

	:description,

	:releasestartdate,

	:releaseenddate,

	:missionstatementurl,

	:databaseaccesstype,

	:datauploadtype,

	:databaseaccessrestriction,

	:datauploadrestriction,

	:citationguidelineurl,

	:certificates,

	:aggregator,

	:issn,

	:eissn,

	:lissn,

	:registeredby,

	:subjects,--array

	:managed,

	:eoscdatasourcetype,

	:eosctype

)

BEGIN;

INSERT INTO dsm_organizations(

	id, 

	legalshortname, 

	legalname, 

	websiteurl,

	logourl,

	country,

	collectedfrom

) VALUES (

	:orgId, 

	:legalshortname, 

	:legalname, 

	:websiteurl,

	:logourl,

	:country,

	:collectedfrom

);

INSERT INTO dsm_service_organization(

	service, 

	organization

) VALUES (

	:dsId,

	:orgId

);

COMMIT;

BEGIN;

DELETE FROM dsm_apiparams USING dsm_api WHERE api = dsm_api.id AND api.service = :dsId;

DELETE FROM api WHERE service = :dsId;

DELETE FROM dsm_service_organization WHERE service = :dsId;

DELETE FROM dsm_servicepids WHERE service = :dsId;

DELETE FROM dsm_services WHERE id = :dsId;

COMMIT;

 SELECT

 	id.pid,

 	id.issuertype 

 FROM

 	dsm_identities id 

	LEFT OUTER JOIN dsm_servicepids dp ON (id.pid = dp.pid)

WHERE

	dp.service = :dsId

SELECT

	ds.id                        AS "id",

	ds.officialname              AS "officialname",

	ds.englishname               AS "englishname",

	ds.websiteurl                AS "websiteurl",

	ds.logourl                   AS "logourl",

	ds.contactemail              AS "contactemail",

	ds.latitude                  AS "latitude",

	ds.longitude                 AS "longitude",

	ds.timezone                  AS "timezone",

	ds.namespaceprefix           AS "namespaceprefix",

	ds.languages                 AS "languages",

	ds.collectedfrom             AS "collectedfrom",

	ds.dateofvalidation          AS "dateofvalidation",

	ds.provenanceaction          AS "provenanceaction",

	ds.dateofcollection          AS "dateofcollection",

	ds.platform                  AS "platform",

	ds.activationid              AS "activationId",

	ds.description               AS "description",

	ds.releasestartdate          AS "releasestartdate",

	ds.releaseenddate            AS "releaseenddate",

	ds.missionstatementurl       AS "missionstatementurl",

	ds.databaseaccesstype        AS "databaseaccesstype",

	ds.datauploadtype            AS "datauploadtype",

	ds.databaseaccessrestriction AS "databaseaccessrestriction",

	ds.datauploadrestriction     AS "datauploadrestriction",

	ds.citationguidelineurl      AS "citationguidelineurl",

	ds.certificates              AS "certificates",

	ds.aggregator                AS "aggregator",

	ds.issn                      AS "issn",

	ds.eissn                     AS "eissn",

	ds.lissn                     AS "lissn",

	ds.registeredby              AS "registeredby",

	ds.subjects                  AS "subjects",

	ds.managed                   AS "managed",

	ds.consenttermsofuse         AS "consenttermsofuse",

	ds.consenttermsofusedate     AS "consenttermsofusedate",

	ds.fulltextdownload          AS "fulltextdownload",

	ds.eosc_datasource_type      AS "eosc_datasource_type",

	ds.status                    AS "status"

FROM

	dsm_services ds

WHERE

	ds.id = :dsId 

	AND eosc_datasource_type IS NOT NULL

	AND dedup_main_service = true

SELECT

	ds.id                        AS "id",

	ds.officialname              AS "officialname",

	ds.englishname               AS "englishname",

	ds.websiteurl                AS "websiteurl",

	ds.logourl                   AS "logourl",

	ds.contactemail              AS "contactemail",

	ds.latitude                  AS "latitude",

	ds.longitude                 AS "longitude",

	ds.timezone                  AS "timezone",

	ds.namespaceprefix           AS "namespaceprefix",

	ds.languages                 AS "languages",

	ds.collectedfrom             AS "collectedfrom",

	ds.dateofvalidation          AS "dateofvalidation",

	ds.provenanceaction          AS "provenanceaction",

	ds.dateofcollection          AS "dateofcollection",

	ds.platform                  AS "platform",

	ds.activationid              AS "activationId",

	ds.description               AS "description",

	ds.releasestartdate          AS "releasestartdate",

	ds.releaseenddate            AS "releaseenddate",

	ds.missionstatementurl       AS "missionstatementurl",

	ds.databaseaccesstype        AS "databaseaccesstype",

	ds.datauploadtype            AS "datauploadtype",

	ds.databaseaccessrestriction AS "databaseaccessrestriction",

	ds.datauploadrestriction     AS "datauploadrestriction",

	ds.citationguidelineurl      AS "citationguidelineurl",

	ds.certificates              AS "certificates",

	ds.aggregator                AS "aggregator",

	ds.issn                      AS "issn",

	ds.eissn                     AS "eissn",

	ds.lissn                     AS "lissn",

	ds.registeredby              AS "registeredby",

	ds.subjects                  AS "subjects",

	ds.managed                   AS "managed",

	ds.consenttermsofuse         AS "consenttermsofuse",

	ds.consenttermsofusedate     AS "consenttermsofusedate",

	ds.fulltextdownload          AS "fulltextdownload",

	ds.eosc_datasource_type      AS "eosc_datasource_type",

	ds.status                    AS "status"

FROM

	dsm_services ds

WHERE

	ds.namespaceprefix = :prefix

	AND eosc_datasource_type IS NOT NULL

	AND dedup_main_service = true

INSERT INTO dsm_api(

	id,

	protocol, 

	baseurl,

	service,

	contentdescription,

	compatibility,

	metadata_identifier_path,

	removable

) VALUES (

	:apiId,

	:protocol, 

	:baseUrl,

	:dsId,

	:contentDescription,

	:compatibility,

	:metadataIdentifierPath,

	:removable

)

SELECT

	a.id                                                AS "id",

	a.protocol                                          AS "protocol",

	a.baseurl                                           AS "baseUrl",

	a.service                                           AS "datasource",

	a.contentdescription                                AS "contentdescription",

	a.active                                            AS "active",

	a.removable                                         AS "removable",

	coalesce(a.compatibility_override, a.compatibility) AS "compatibility",

	a.compatibility_override IS NOT null                AS "isCompatibilityOverrided",

	a.metadata_identifier_path                          AS "metadataIdentifierPath",

	a.last_collection_total                             AS "lastCollectionTotal",

	a.last_collection_date                              AS "lastCollectionDate",

	a.last_collection_mdid                              AS "lastCollectionMdid",

	a.last_aggregation_total                            AS "lastAggregationTotal",

	a.last_aggregation_date                             AS "lastAggregationDate",

	a.last_aggregation_mdid                             AS "lastAggregationMdid",

	a.last_download_total                               AS "lastDownloadTotal",

	a.last_download_date                                AS "lastDownloadDate",

	a.last_download_objid                               AS "lastDownloadObjid",

	a.last_validation_job                               AS "lastValidationJob",

	array_agg(p.param || '=' || p.value)                AS "params"

FROM 

	dsm_api a

	LEFT OUTER JOIN dsm_apiparams p on (a.id = p.api)

WHERE

	a.service = :dsId

GROUP BY

	a.id,

	a.protocol,

	a.baseurl,

	a.service,

	a.contentdescription,

	a.active,

	a.removable,

	a.compatibility_override,

	a.compatibility,

	a.metadata_identifier_path,

	a.last_collection_total,

	a.last_collection_date,

	a.last_collection_mdid,

	a.last_aggregation_total,

	a.last_aggregation_date,

	a.last_aggregation_mdid,

	a.last_download_total,

	a.last_download_date,

	a.last_download_objid,

	a.last_validation_job

alter table dsm_api add column compatibility_override text default NULL;

update dsm_api set removable = true where id in (select api.id from dsm_api api left join dsm_services d on d.id = api.service where d.managed = true and api.removable=false);

package eu.dnetlib.enabling.datasources;

import java.io.StringWriter;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.HashSet;

import java.util.List;

import java.util.Map;

import java.util.Optional;

import java.util.Set;

import java.util.stream.Collectors;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.math.NumberUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.springframework.beans.factory.annotation.Required;

import org.springframework.core.io.ClassPathResource;

import org.springframework.core.io.Resource;

import org.springframework.transaction.annotation.Transactional;

import com.google.common.collect.ImmutableMap;

import eu.dnetlib.enabling.datasources.common.Api;

import eu.dnetlib.enabling.datasources.common.ApiParam;

import eu.dnetlib.enabling.datasources.common.BrowsableField;

import eu.dnetlib.enabling.datasources.common.BrowseTerm;

import eu.dnetlib.enabling.datasources.common.BrowseTermImpl;

import eu.dnetlib.enabling.datasources.common.Datasource;

import eu.dnetlib.enabling.datasources.common.DsmException;

import eu.dnetlib.enabling.datasources.common.DsmForbiddenException;

import eu.dnetlib.enabling.datasources.common.DsmNotFoundException;

import eu.dnetlib.enabling.datasources.common.DsmRuntimeException;

import eu.dnetlib.enabling.datasources.common.Identity;

import eu.dnetlib.enabling.datasources.common.Organization;

import eu.dnetlib.enabling.datasources.common.PidSystem;

import eu.dnetlib.enabling.datasources.common.SearchApisEntry;

import eu.dnetlib.enabling.datasources.common.SimpleDatasource;

public class LocalOpenaireDatasourceManagerImpl implements LocalOpenaireDatasourceManager {

	private DatasourceManagerClients datasourceManagerClients;

	private List<DbBrowsableField> browsableFields;

	public static final String QUERY_BASEDIR = "/eu/dnetlib/enabling/datasources/queries/";

	private static final Resource searchDsByType = new ClassPathResource(QUERY_BASEDIR + "searchDsByType.sql");

	private static final Resource searchApis = new ClassPathResource(QUERY_BASEDIR + "searchApisNormal.sql");

	private static final Resource searchApisUsingField = new ClassPathResource(QUERY_BASEDIR + "searchApisUsingField.sql");

	private static final Resource addDs = new ClassPathResource(QUERY_BASEDIR + "addDatasource.sql");

	private static final Resource addOrg = new ClassPathResource(QUERY_BASEDIR + "addOrganization.sql");

	private static final Resource deleteDs = new ClassPathResource(QUERY_BASEDIR + "deleteDatasource.sql");

	private static final Resource setActive = new ClassPathResource(QUERY_BASEDIR + "setActive.sql");

	private static final Resource setManaged = new ClassPathResource(QUERY_BASEDIR + "setManaged.sql");

	private static final Resource setCompliance = new ClassPathResource(QUERY_BASEDIR + "setCompliance.sql");

	private static final Resource overrideCompliance = new ClassPathResource(QUERY_BASEDIR + "overrideCompliance.sql");

	private static final Resource resetCompliance = new ClassPathResource(QUERY_BASEDIR + "resetCompliance.sql");

	private static final Resource setLastCollectionInfo = new ClassPathResource(QUERY_BASEDIR + "setLastCollectionInfo.sql");

	private static final Resource setLastAggregationInfo = new ClassPathResource(QUERY_BASEDIR + "setLastAggregationInfo.sql");

	private static final Resource setLastDownloadInfo = new ClassPathResource(QUERY_BASEDIR + "setLastDownloadInfo.sql");

	private static final Resource setLastValidationJob = new ClassPathResource(QUERY_BASEDIR + "setLastValidationJob.sql");

	private static final Resource resetLastOperationsInfo = new ClassPathResource(QUERY_BASEDIR + "resetLastOperationsInfo.sql");

	private static final Resource insertApiParam = new ClassPathResource(QUERY_BASEDIR + "insertApiParam.sql");

	private static final Resource insertApi = new ClassPathResource(QUERY_BASEDIR + "insertApi.sql");

	private static final Resource setRemovable = new ClassPathResource(QUERY_BASEDIR + "setRemovable.sql");

	private static final Log log = LogFactory.getLog(LocalOpenaireDatasourceManagerImpl.class);

	@Override

	@Transactional(readOnly = true)

	public Set<String> listManagedDatasourceIds() throws DsmRuntimeException {

		try {

			return datasourceManagerClients.searchSQL("SELECT id FROM dsm_services WHERE managed = true", new HashMap<>())

				.stream()

				.map(m -> (String) m.get("id"))

				.collect(Collectors.toCollection(HashSet::new));

		} catch (final DsmException e) {

			throw new DsmRuntimeException(e);

		}

	}

	@Override

	@Transactional(readOnly = true)

	public List<SimpleDatasource> searchDatasourcesByType(final String type) throws DsmException {

		return datasourceManagerClients.searchSQL(searchDsByType, ImmutableMap.of("type", type))

			.stream()

			.map(DatasourceFunctions::mapToSimpleDs)

			.collect(Collectors.toList());

	}

	@Override

	@Transactional(readOnly = true)

	public List<? extends SearchApisEntry> searchApis(final String field, final Object value) throws DsmException {

		try {

			final StringWriter sql = new StringWriter();

			final Map<String, Object> params = new HashMap<>();

			if (field != null && value != null) {

				if (field.equalsIgnoreCase("__search__")) {

					params.put("value", "%" + value + "%");

					sql.append(IOUtils.toString(searchApis.getInputStream()));

				} else {

					sql.append(IOUtils.toString(searchApisUsingField.getInputStream()));

					if (field.equalsIgnoreCase("collbackend")) {

						sql.append("\"collMdId\" is not null and \"collMdId\" != '' and \"collMdId\" ");

						if (StringUtils.equalsIgnoreCase(value.toString(), MDStoreBackend.HDFS.toString())) {

							sql.append("like 'md-%' and length(\"collMdId\") <= 40");

						} else {

							// MONGO

							sql.append("not like 'md-%' and length(\"collMdId\") > 40");

						}

					} else {

						params.put("value", value);

						sql.append(field);

						sql.append("::text = ");

						sql.append(":value");

					}

				}

				return datasourceManagerClients.searchSQL(sql.toString(), params)

					.stream()

					.map(DatasourceFunctions::mapToSearchApisEntry)

					.collect(Collectors.toList());

			}

		} catch (final Exception e) {

			log.error("Error searching field " + field + " - value: " + value, e);

		}

		return new ArrayList<>();

	}

	@Override

	@Transactional

	public void saveDs(final Datasource<Organization<?>, Identity, PidSystem> ds) throws DsmException {

		if (StringUtils.isBlank(ds.getAggregator())) {

			ds.setAggregator("OPENAIRE");

		}

		ds.setManaged(true);

		datasourceManagerClients.updateSQL(ds.getId(), addDs, DatasourceFunctions.dsToMap(ds));

		if (ds.getOrganizations() != null) {

			for (final Organization<?> org : ds.getOrganizations()) {

				final Map<String, Object> orgParams = DatasourceFunctions.orgToMap(ds.getId(), org);

				datasourceManagerClients.updateSQL(ds.getId(), addOrg, orgParams);

			}

		}

		final Map<String, Object> params = new HashMap<>();

		params.put("removable", true);

		params.put("dsId", ds.getId());

		datasourceManagerClients.updateSQL(ds.getId(), setRemovable, params);

	}

	@Override

	@Transactional

	public void deleteDs(final String dsId) throws DsmException {

		datasourceManagerClients.updateSQL(dsId, deleteDs, ImmutableMap.of("dsId", dsId));

	}

	@Override

	public Datasource<Organization<?>, Identity, PidSystem> getDs(final String dsId) throws DsmException {

		return datasourceManagerClients.getDatasourceById(dsId);

	}

	@Override

	public Datasource<Organization<?>, Identity, PidSystem> getDsByNsPrefix(final String prefix) throws DsmException {

		return datasourceManagerClients.getDatasourceByPrefix(prefix);

	}

	@Override

	public List<Api<ApiParam>> getApis(final String dsId) throws DsmException {

		return datasourceManagerClients.getApis(dsId);

	}

	@Override

	public void setManaged(final String dsId, final boolean managed) throws DsmException {

		final String id = dsId;

		final Map<String, Object> params = new HashMap<>();

		params.put("managed", managed);

		params.put("dsId", id);

		datasourceManagerClients.updateSQL(id, setManaged, params);

		params.clear();

		params.put("removable", true);

		params.put("dsId", id);

		datasourceManagerClients.updateSQL(id, setRemovable, params);

	}

	@Override

	public boolean isManaged(final String dsId) throws DsmException {

		final String q = "SELECT id from dsm_services WHERE id = :dsId AND managed = true";

		return !datasourceManagerClients.searchSQL(q, ImmutableMap.of("dsId", dsId)).isEmpty();

	}

	@Override

	@Transactional

	public void setActive(final String dsId, final String apiId, final boolean active) throws DsmException {

		final String id = dsId;

		final Map<String, Object> params = new HashMap<>();

		params.put("active", active);

		params.put("apiId", apiId);

		params.put("dsId", id);

		datasourceManagerClients.updateSQL(id, setActive, params);

		if (!active) {

			datasourceManagerClients.updateSQL(dsId, resetLastOperationsInfo, params);

		}

		setManaged(id, true);

	}

	@Override

	@Transactional(readOnly = true)

	public boolean isActive(final String dsId, final String apiId) throws DsmException {

		final String q = "SELECT * from dsm_api WHERE id = :apiId AND service = :dsId AND active = true";

		return !datasourceManagerClients.searchSQL(q, ImmutableMap.of("dsId", dsId, "apiId", apiId)).isEmpty();

	}

	@Override

	@Transactional(readOnly = true)

	public boolean isRemovable(final String dsId, final String apiId) throws DsmException {

		final String q = "SELECT * from dsm_api WHERE id = :apiId AND service = :dsId AND active != true AND removable = true";

		return !datasourceManagerClients.searchSQL(q, ImmutableMap.of("dsId", dsId, "apiId", apiId)).isEmpty();

	}

	@Override

	@Transactional

	public void updateCompliance(final String dsId, final String apiId, final String level, final boolean override) throws DsmException {

		final String id = dsId;

		if (!override) {

			datasourceManagerClients.updateSQL(id, setCompliance, ImmutableMap.of("level", level, "apiId", apiId, "dsId", id));

		} else if (level != null) {

			datasourceManagerClients.updateSQL(id, overrideCompliance, ImmutableMap.of("level", level, "apiId", apiId, "dsId", id));

		} else {

			datasourceManagerClients.updateSQL(id, resetCompliance, ImmutableMap.of("apiId", apiId, "dsId", id));

		}

		setManaged(id, true);

	}

	@Override

	public void setLastCollectionInfo(final String dsId, final String apiId, final String mdId, final Integer size, final Date date)

		throws DsmException {

		setLastOperationInfo(setLastCollectionInfo, dsId, apiId, mdId, size, date);

	}

	@Override

	public void setLastAggregationInfo(final String dsId, final String apiId, final String mdId, final Integer size, final Date date)

		throws DsmException {

		setLastOperationInfo(setLastAggregationInfo, dsId, apiId, mdId, size, date);

	}

	@Override

	public void setLastDownloadInfo(final String dsId, final String apiId, final String objId, final Integer size, final Date date)

		throws DsmException {

		setLastOperationInfo(setLastDownloadInfo, dsId, apiId, objId, size, date);

	}

	@Override

	public void setLastValidationJob(final String dsId, final String apiId, final String jobId) throws DsmException {

		final Map<String, Object> params = new HashMap<>();

		params.put("dsId", dsId);

		params.put("apiId", apiId);

		params.put("jobId", jobId);

		datasourceManagerClients.updateSQL(dsId, setLastValidationJob, params);

		setManaged(dsId, true);

	}

	@Transactional

	protected void setLastOperationInfo(final Resource sqlResource,

		final String dsId,

		final String apiId,

		final String mdId,

		final Integer size,

		final Date date)

		throws DsmException {

		final Map<String, Object> params = new HashMap<>();

		params.put("dsId", dsId);

		params.put("apiId", apiId);

		params.put("mdId", mdId);

		params.put("total", size);

		if (date != null) {

			params.put("date", new java.sql.Timestamp(date.getTime()));

		}

		datasourceManagerClients.updateSQL(dsId, sqlResource, params);

		setManaged(dsId, true);

	}

	@Override

	@Transactional

	public void updateApiDetails(final String dsId,

		final String apiId,

		final String metadataIdentifierPath,

		final String baseUrl,

		final Map<String, String> params)

		throws DsmException {

		final String id = dsId;

		// Delete old params

		datasourceManagerClients.updateSQL(id, "DELETE FROM dsm_apiparams WHERE api = :api", ImmutableMap.of("api", apiId));

		// Insert new params

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

			final Map<String, Object> sqlParams = ImmutableMap.of("param", e.getKey(), "value", e.getValue(), "api", apiId);

			datasourceManagerClients.updateSQL(id, insertApiParam, sqlParams);

		}

		// Update the BaseURL

		datasourceManagerClients

			.updateSQL(id, "UPDATE dsm_api SET baseurl = :baseurl WHERE id = :api", ImmutableMap.of("baseurl", baseUrl, "api", apiId));

		// Update the metadata_identifier_path

		datasourceManagerClients.updateSQL(id, "UPDATE dsm_api SET metadata_identifier_path = :path WHERE id = :api", ImmutableMap

			.of("path", metadataIdentifierPath, "api", apiId));

		setManaged(id, true);

	}

	@Override

	public List<? extends BrowsableField> listBrowsableFields() throws DsmException {

		return getBrowsableFields();

	}

	@Override

	public List<BrowseTerm> browseField(final String field) throws DsmException {

		final Optional<DbBrowsableField> bf = getBrowsableFields()

			.stream()

			.filter(f -> f.getId().equals(field))

			.findFirst();

		if (bf.isPresent()) {

			return datasourceManagerClients.searchSQL(bf.get().getSql(), new HashMap<>())

				.stream()

				.filter(m -> m.get("term") != null)

				.filter(m -> m.get("count") != null)

				.filter(m -> StringUtils.isNotBlank(m.get("term").toString()))

				.map(m -> new BrowseTermImpl(m.get("term").toString(), NumberUtils.toInt(m.get("count").toString(), 0)))

				.collect(Collectors.toList());

		} else {

			log.error("Not browsable field:" + field);

			throw new DsmException("Not browsable field:" + field);

		}

	}

	@Override

	@Transactional

	public void addApi(final Api<ApiParam> api) throws DsmException {

		datasourceManagerClients.updateSQL(api.getDatasource(), insertApi, DatasourceFunctions.apiToMap(api));

		if (api.getApiParams() != null) {

			api.getApiParams().forEach(p -> {

				final ImmutableMap<String, Object> sqlParams = ImmutableMap.of("param", p.getParam(), "value", p.getValue(), "api", api.getId());

				try {

					datasourceManagerClients.updateSQL(api.getDatasource(), insertApiParam, sqlParams);

				} catch (final DsmException e) {

					throw new RuntimeException(e);

				}

			});

		}

		setManaged(api.getDatasource(), true);

	}

	@Override

	@Transactional

	public void deleteApi(final String dsId, final String apiId) throws DsmForbiddenException, DsmNotFoundException {

		try {

			if (!isRemovable(dsId, apiId)) { throw new DsmForbiddenException("The api " + apiId + " can't be deleted"); }

			datasourceManagerClients.updateSQL(dsId, "DELETE FROM dsm_apiparams WHERE api = :api", ImmutableMap.of("api", apiId));

			datasourceManagerClients.updateSQL(dsId, "DELETE FROM dsm_api WHERE id = :api", ImmutableMap.of("api", apiId));

			setManaged(dsId, true);

		} catch (final Exception e) {

			throw new DsmForbiddenException("The api " + apiId + " can't be deleted");

		}

	}

	public DatasourceManagerClients getDatasourceManagerClients() {

		return datasourceManagerClients;

	}

	@Required

	public void setDatasourceManagerClients(final DatasourceManagerClients datasourceManagerClients) {

		this.datasourceManagerClients = datasourceManagerClients;

	}

	public List<DbBrowsableField> getBrowsableFields() {

		return browsableFields;

	}

	@Required

	public void setBrowsableFields(final List<DbBrowsableField> browsableFields) {

		this.browsableFields = browsableFields;

	}

	@Override

	@Deprecated

	public void regenerateProfiles() throws DsmException {}

	@Override

	public void updateTickets(final String dsId, final Set<Integer> tickets) throws DsmException {

		datasourceManagerClients.updateSQL(dsId, "DELETE FROM dsm_tickets WHERE service = :service", ImmutableMap.of("service", dsId));

		for (final Integer t : tickets) {

			datasourceManagerClients

				.updateSQL(dsId, "INSERT INTO dsm_tickets (service, ticket) VALUES (:service, :ticket)", ImmutableMap.of("service", dsId, "ticket", t));

		}

	}

	@Override

	public Set<Integer> listTickets(final String dsId) throws DsmException {

		return datasourceManagerClients.searchSQL("select ticket from dsm_tickets where service = :service", ImmutableMap.of("service", dsId))

			.stream()

			.map(r -> r.get("ticket"))

			.filter(n -> n instanceof Integer)

			.map(n -> (Integer) n)

			.collect(Collectors.toSet());

	}

}

package eu.dnetlib.enabling.datasources;

public enum MDStoreBackend {

	MONGO,

	HDFS

}

{"type_source": "SVN", "goal": "package -U -T 4C source:jar", "url": "http://svn-public.driver.research-infrastructures.eu/driver/dnet45/modules/dnet-openaire-datasource-manager/trunk/", "deploy_repository": "dnet45-snapshots", "version": "4", "mail": "sandro.labruzzo@isti.cnr.it,michele.artini@isti.cnr.it, claudio.atzori@isti.cnr.it, alessia.bardi@isti.cnr.it", "deploy_repository_url": "http://maven.research-infrastructures.eu/nexus/content/repositories/dnet45-snapshots", "name": "dnet-openaire-datasource-manager"}

UPDATE dsm_api SET compatibility_override = :level WHERE id = :apiId AND service = :dsId

UPDATE dsm_api SET compatibility_override = NULL WHERE id = :apiId AND service = :dsId

UPDATE dsm_api

SET 	(last_aggregation_mdid, last_aggregation_total, last_aggregation_date, last_collection_mdid, last_collection_total, last_collection_date, last_download_objid, last_download_total, last_download_date) = (null, null, null, null, null, null, null, null, null)

WHERE id = :apiId AND service = :dsId

SELECT

	ds.id           AS id,

	ds.officialname AS name,

	ds.eosc_datasource_type     AS eosc_datasource_type,

	array_agg(a.id) AS apis

FROM

	dsm_services ds

	LEFT OUTER JOIN dsm_api a ON (ds.id = a.service)

WHERE

	ds.eosc_datasource_type = :type AND ds.dedup_main_service = true

GROUP BY

	ds.id, ds.officialname, ds.eosc_datasource_type

ORDER BY

	 ds.officialname

UPDATE dsm_api SET active = :active WHERE id = :apiId AND service = :dsId

UPDATE dsm_api SET compatibility = :level WHERE id = :apiId AND service = :dsId

UPDATE dsm_api

SET (last_aggregation_mdid, last_aggregation_total, last_aggregation_date) = (:mdId, :total, :date)

WHERE id = :apiId AND service = :dsId

UPDATE dsm_api

SET (last_download_objid, last_download_total, last_download_date) = (:mdId, :total, :date)

WHERE id = :apiId AND service = :dsId

UPDATE dsm_api

SET (last_validation_job) = (:jobId)

WHERE id = :apiId AND service = :dsId

UPDATE dsm_services SET managed = :managed WHERE id = :dsId

UPDATE dsm_api SET removable = :removable WHERE service = :dsId

INSERT INTO dsm_apiparams(param, value, api) VALUES (:param, :value, :api)

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

<beans xmlns="http://www.springframework.org/schema/beans"

	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jaxws="http://cxf.apache.org/jaxws"

	xmlns:sec="http://cxf.apache.org/configuration/security" xmlns:wsa="http://cxf.apache.org/ws/addressing"

	xmlns:p="http://www.springframework.org/schema/p" xmlns:http="http://cxf.apache.org/transports/http/configuration"

	xmlns:t="http://dnetlib.eu/springbeans/t" xmlns:template="http://dnetlib.eu/springbeans/template"

	xmlns:util="http://www.springframework.org/schema/util"

	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd

                                    http://cxf.apache.org/ws/addressing http://cxf.apache.org/schemas/ws-addr-conf.xsd

                                    http://cxf.apache.org/configuration/security http://cxf.apache.org/schemas/configuration/security.xsd

                                    http://cxf.apache.org/transports/http/configuration http://cxf.apache.org/schemas/configuration/http-conf.xsd

                            http://cxf.apache.org/jaxws http://cxf.apache.org/schemas/jaxws.xsd

                            http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd

                            http://dnetlib.eu/springbeans/template http://dnetlib.eu/springbeans/template.xsd">

	<bean id="localOpenaireDatasourceManager"

		class="eu.dnetlib.enabling.datasources.LocalOpenaireDatasourceManagerImpl"

		p:datasourceManagerClients-ref="datasourceManagerClients">

		<property name="browsableFields">

			<list>

				<bean class="eu.dnetlib.enabling.datasources.DbBrowsableField"

					p:id="country" p:label="Datasource countries"

					p:sql="select o.country as term, count(*) as count from dsm_api a left outer join dsm_services d on (a.service = d.id) left outer join dsm_service_organization dao on (d.id = dao.service) left outer join dsm_organizations o on (dao.organization = o.id) group by o.country order by count desc" />

				<bean class="eu.dnetlib.enabling.datasources.DbBrowsableField"

					p:id="type" p:label="API typologies"

					p:sql="select d.eosc_datasource_type as term, count(*) as count from dsm_api a left outer join dsm_services d on (a.service = d.id) group by d.eosc_datasource_type order by count desc" />

				<bean class="eu.dnetlib.enabling.datasources.DbBrowsableField"

					p:id="protocol" p:label="API protocols"

					p:sql="select a.protocol as term, count(*) as count from dsm_api a group by a.protocol order by count desc" />

				<bean class="eu.dnetlib.enabling.datasources.DbBrowsableField"

					p:id="compliance" p:label="API compatibility levels"

					p:sql="select coalesce(a.compatibility_override, a.compatibility) as term, count(*) as count from dsm_api a group by coalesce(a.compatibility_override, a.compatibility) order by count desc" />

				<bean class="eu.dnetlib.enabling.datasources.DbBrowsableField"

					p:id="active" p:label="API activation"

					p:sql="select a.active as term, count(*) as count from dsm_api a group by a.active order by count desc" />

				<bean class="eu.dnetlib.enabling.datasources.DbBrowsableField"

					p:id="consenttermsofuse" p:label="Consent terms of use"

					p:sql="select d.consenttermsofuse as term, count(*) as count from dsm_api a left outer join dsm_services d on (a.service = d.id) group by d.consenttermsofuse order by count desc" />

				<bean class="eu.dnetlib.enabling.datasources.DbBrowsableField"

					p:id="fulltextdownload" p:label="Fulltext download"

					p:sql="select d.fulltextdownload as term, count(*) as count from dsm_api a left outer join dsm_services d on (a.service = d.id) group by d.fulltextdownload order by count desc" />

				<bean class="eu.dnetlib.enabling.datasources.DbBrowsableField"

					p:id="collbackend" p:label="Last Collection Backend"

					p:sql="select t.term, count(*) from (select CASE WHEN last_collection_mdid LIKE 'md-%' and length(last_collection_mdid) <= 40 THEN 'HDFS' ELSE 'MONGO' END as term from dsm_api where last_collection_mdid is not null) as t group by t.term order by count desc" />			

			</list>

		</property>

	</bean>

	<bean id="datasourceManagerClients"

		class="eu.dnetlib.enabling.datasources.DatasourceManagerClients">

		<property name="jdbcTemplate">

			<bean class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">

				<constructor-arg>

					<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource"

						p:driverClassName="org.postgresql.Driver" p:url="${dnet.openaire.db.url}"

						p:username="${dnet.openaire.db.username}" p:password="${dnet.openaire.db.password}" />

				</constructor-arg>

			</bean>

		</property>

	</bean>

</beans>

CREATE TABLE dsm_tickets(

	service text REFERENCES dsm_services(id) ON DELETE CASCADE,

	ticket integer,

	PRIMARY KEY (service, ticket)

);

package eu.dnetlib.enabling.datasources;

import java.util.Set;

import eu.dnetlib.enabling.datasources.common.Api;

import eu.dnetlib.enabling.datasources.common.ApiParam;

import eu.dnetlib.enabling.datasources.common.Datasource;

import eu.dnetlib.enabling.datasources.common.DsmException;

import eu.dnetlib.enabling.datasources.common.Identity;

import eu.dnetlib.enabling.datasources.common.LocalDatasourceManager;

import eu.dnetlib.enabling.datasources.common.Organization;

import eu.dnetlib.enabling.datasources.common.PidSystem;

public interface LocalOpenaireDatasourceManager extends LocalDatasourceManager<Datasource<Organization<?>, Identity, PidSystem>, Api<ApiParam>> {

	void updateTickets(String dsId, Set<Integer> tickets) throws DsmException;

	Set<Integer> listTickets(String dsId) throws DsmException;

}

package eu.dnetlib.enabling.datasources;

import java.sql.Array;

import java.sql.Date;

import java.sql.SQLException;

import java.sql.Timestamp;

import java.util.Arrays;

import java.util.HashMap;

import java.util.Map;

import java.util.Objects;

import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import eu.dnetlib.enabling.datasources.common.Api;

import eu.dnetlib.enabling.datasources.common.ApiParam;

import eu.dnetlib.enabling.datasources.common.ApiParamImpl;

import eu.dnetlib.enabling.datasources.common.Datasource;

import eu.dnetlib.enabling.datasources.common.Identity;

import eu.dnetlib.enabling.datasources.common.Organization;

import eu.dnetlib.enabling.datasources.common.PidSystem;

import eu.dnetlib.enabling.datasources.common.SearchApisEntry;

import eu.dnetlib.enabling.datasources.common.SimpleDatasource;

public class DatasourceFunctions {

	private static final String EOSC_DATA_SOURCE = "Data Source";

	private static final Log log = LogFactory.getLog(DatasourceFunctions.class);

	public static SimpleDatasource mapToSimpleDs(final Map<String, Object> map) {

		final SimpleDatasource ds = new SimpleDatasource();

		ds.setId(castObject(map.get("id"), String.class));

		ds.setName(castObject(map.get("name"), String.class));

		ds.setOrigId(castObject(map.get("id"), String.class));

		ds.setEoscDatasourceType(castObject(map.get("eosc_datasource_type"), String.class));

		ds.setValid(true);

		try {

			final Array arr = castObject(map.get("apis"), Array.class);

			if (arr != null && ((Object[]) arr.getArray()).length > 0) {

				ds.setApis(Arrays.stream((Object[]) arr.getArray())

					.filter(Objects::nonNull)

					.map(Object::toString)

					.collect(Collectors.toSet()));

			}

		} catch (final SQLException e) {

			log.error("Error parsing array (apis)", e);

			throw new RuntimeException("Error parsing array (apis)", e);

		}

		return ds;

	}

	public static Datasource<Organization<?>, Identity, PidSystem> mapToDatasource(final Map<String, Object> map) {

		final Datasource<Organization<?>, Identity, PidSystem> ds = new Datasource<>();

		ds.setId(castObject(map.get("id"), String.class));

		ds.setOfficialname(castObject(map.get("officialname"), String.class));

		ds.setEnglishname(castObject(map.get("englishname"), String.class));

		ds.setWebsiteurl(castObject(map.get("websiteurl"), String.class));

		ds.setLogourl(castObject(map.get("logourl"), String.class));

		ds.setContactemail(castObject(map.get("contactemail"), String.class));

		ds.setLatitude(castObject(map.get("latitude"), Double.class));

		ds.setLongitude(castObject(map.get("longitude"), Double.class));

		ds.setTimezone(castObject(map.get("timezone"), String.class));

		ds.setNamespaceprefix(castObject(map.get("namespaceprefix"), String.class));

		ds.setLanguages(castObject(map.get("languages"), String.class));

		ds.setCollectedfrom(castObject(map.get("collectedfrom"), String.class));

		ds.setDateofvalidation(castObject(map.get("dateofvalidation"), Date.class));

		ds.setEoscDatasourceType(castObject(map.get("eosc_datasource_type"), String.class));

		ds.setProvenanceaction(castObject(map.get("provenanceaction"), String.class));

		ds.setDateofcollection(castObject(map.get("dateofcollection"), Date.class));

		ds.setPlatform(castObject(map.get("platform"), String.class));

		ds.setActivationId(castObject(map.get("activationId"), String.class));

		ds.setDescription(castObject(map.get("description"), String.class));

		ds.setReleasestartdate(castObject(map.get("releasestartdate"), Date.class));

		ds.setReleaseenddate(castObject(map.get("releaseenddate"), Date.class));

		ds.setMissionstatementurl(castObject(map.get("missionstatementurl"), String.class));

		ds.setDatabaseaccesstype(castObject(map.get("databaseaccesstype"), String.class));

		ds.setDatauploadtype(castObject(map.get("datauploadtype"), String.class));

		ds.setDatabaseaccessrestriction(castObject(map.get("databaseaccessrestriction"), String.class));

		ds.setDatauploadrestriction(castObject(map.get("datauploadrestriction"), String.class));

		ds.setCitationguidelineurl(castObject(map.get("citationguidelineurl"), String.class));

		ds.setCertificates(castObject(map.get("certificates"), String.class));

		ds.setAggregator(castObject(map.get("aggregator"), String.class));

		ds.setIssn(castObject(map.get("issn"), String.class));

		ds.setEissn(castObject(map.get("eissn"), String.class));

		ds.setLissn(castObject(map.get("lissn"), String.class));

		ds.setRegisteredby(castObject(map.get("registeredby"), String.class));

		ds.setSubjects(castObject(map.get("subjects"), String.class));

		ds.setManaged(castObject(map.get("managed"), Boolean.class));

		ds.setConsentTermsOfUse(castObject(map.get("consenttermsofuse"), Boolean.class));

		ds.setConsentTermsOfUseDate(castObject(map.get("consenttermsofusedate"), Date.class));

		ds.setFullTextDownload(castObject(map.get("fulltextdownload"), Boolean.class));

		ds.setStatus(castObject(map.get("status"), String.class));

		return ds;

	}

	public static Identity mapToDsIdentity(final Map<String, Object> map) {

		final Identity id = new Identity();

		id.setPid(castObject(map.get("pid"), String.class));