D-Net

{"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"}

package eu.dnetlib.enabling.datasources;

import java.io.IOException;

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.List;

import java.util.Map;

import java.util.Objects;

import java.util.stream.Collectors;

import org.apache.commons.lang3.BooleanUtils;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.dom4j.Document;

import org.dom4j.DocumentException;

import org.dom4j.Element;

import org.dom4j.io.SAXReader;

import org.springframework.core.io.ClassPathResource;

import org.springframework.core.io.Resource;

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.SearchApisEntry;

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

import eu.dnetlib.miscutils.datetime.DateUtils;

public class DatasourceFunctions {

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

	private static final Resource baseDsProfile = new ClassPathResource("/eu/dnetlib/enabling/datasources/templates/datasource_base.xml");

	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.setTypology(castObject(map.get("typology"), 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> mapToDatasource(final Map<String, Object> map) {

		final Datasource<Organization<?>, Identity> 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.setOd_contenttypes(castObject(map.get("od_contenttypes"), String.class));

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

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

		ds.setTypology(castObject(map.get("typology"), 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.setDataprovider(castObject(map.get("dataprovider"), Boolean.class));

		ds.setServiceprovider(castObject(map.get("serviceprovider"), Boolean.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.setVersioning(castObject(map.get("versioning"), Boolean.class));

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

		ds.setQualitymanagementkind(castObject(map.get("qualitymanagementkind"), String.class));

		ds.setPidsystems(castObject(map.get("pidsystems"), 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));

		return ds;

	}

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

		final Identity id = new Identity();

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

		id.setIssuertype(castObject(map.get("issuertype"), String.class));

		return id;

	}

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

		final Organization<Datasource<?, ?>> org = new Organization<>();

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

		org.setLegalshortname(castObject(map.get("legalshortname"), String.class));

		org.setLegalname(castObject(map.get("legalname"), String.class));

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

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

		org.setCountry(castObject(map.get("country"), String.class));

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

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

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

		return org;

	}

	public static SearchApisEntry mapToSearchApisEntry(final Map<String, Object> map) {

		final SearchApisEntry a = new SearchApisEntry();

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

		a.setCompliance(castObject(map.get("compliance"), String.class));

		a.setActive(castObject(map.get("active"), Boolean.class));

		a.setRepoId(castObject(map.get("dsId"), String.class));

		a.setRepoName(castObject(map.get("name"), String.class));

		a.setRepoCountry(castObject(map.get("country"), String.class));

		a.setRepoPrefix(castObject(map.get("prefix"), String.class));

		a.setAggrDate(castObject(map.get("aggrDate"), String.class));

		a.setAggrTotal(castObject(map.get("aggrTotal"), Integer.class));

		a.setProtocol(castObject(map.get("protocol"), String.class));

		a.setAlternativeName(castObject(map.get("alternativeName"), String.class));

		a.setRepoOrganization(castObject(map.get("organization"), String.class));

		return a;

	}

	public static Api<ApiParam> mapToApi(final Map<String, Object> map) {

		final Api<ApiParam> a = new Api<>();

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

		a.setProtocol(castObject(map.get("protocol"), String.class));

		a.setDatasource(castObject(map.get("datasource"), String.class));

		a.setContentdescription(castObject(map.get("contentdescription"), String.class));

		a.setActive(castObject(map.get("active"), Boolean.class));

		a.setRemovable(castObject(map.get("removable"), Boolean.class));

		a.setTypology(castObject(map.get("typology"), String.class));

		a.setCompatibility(castObject(map.get("compatibility"), String.class));

		a.setCompatibilityOverrided(castObject(map.get("isCompatibilityOverrided"), Boolean.class));

		a.setMetadataIdentifierPath(castObject(map.get("metadataIdentifierPath"), String.class));

		a.setLastCollectionTotal(castObject(map.get("lastCollectionTotal"), Integer.class));

		a.setLastCollectionDate(castObject(map.get("lastCollectionDate"), Timestamp.class));

		a.setLastCollectionMdid(castObject(map.get("lastCollectionMdid"), String.class));

		a.setLastAggregationTotal(castObject(map.get("lastAggregationTotal"), Integer.class));

		a.setLastAggregationDate(castObject(map.get("lastAggregationDate"), Timestamp.class));

		a.setLastAggregationMdid(castObject(map.get("lastAggregationMdid"), String.class));

		a.setLastDownloadTotal(castObject(map.get("lastDownloadTotal"), Integer.class));

		a.setLastDownloadDate(castObject(map.get("lastDownloadDate"), Timestamp.class));

		a.setLastDownloadObjid(castObject(map.get("lastDownloadObjid"), String.class));

		a.setLastValidationJob(castObject(map.get("lastValidationJob"), String.class));

		a.setBaseurl(castObject(map.get("baseUrl"), String.class));

		try {

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

			if (arr != null) {

				a.setApiParams(Arrays.stream((Object[]) arr.getArray())

						.filter(Objects::nonNull)

						.map(Object::toString)

						.map(s -> {

							final ApiParam p = new ApiParamImpl();

							p.setParam(StringUtils.substringBefore(s, "="));

							p.setValue(StringUtils.substringAfter(s, "="));

							return p;

						})

						.collect(Collectors.toSet()));

			}

		} catch (final SQLException e) {

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

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

		}

		return a;

	}

	public static String dsToProfile(final Datasource<Organization<?>, Identity> ds, final List<Api<ApiParam>> apis, final String profId)

			throws DocumentException, IOException {

		final Document doc = new SAXReader().read(baseDsProfile.getInputStream());

		setValue(doc, "//DATASOURCE_TYPE", ds.getTypology());

		setValue(doc, "//DATASOURCE_ORIGINAL_ID", ds.getId());

		setValue(doc, "//TYPOLOGY", ds.getPlatform());

		setValue(doc, "//OFFICIAL_NAME", ds.getOfficialname());

		setValue(doc, "//ENGLISH_NAME", ds.getEnglishname());

		setValue(doc, "//ICON_URI", ds.getLogourl());

		setValue(doc, "//COUNTRY", ds.getOrganizations().stream().map(Organization::getCountry).findFirst().orElse(""));

		setValue(doc, "//LONGITUDE", ds.getLongitude());

		setValue(doc, "//LATITUDE", ds.getLatitude());

		setValue(doc, "//TIMEZONE", ds.getTimezone());

		setValue(doc, "//REPOSITORY_WEBPAGE", ds.getWebsiteurl());

		setValue(doc, "//REPOSITORY_INSTITUTION", ds.getOrganizations().stream().map(Organization::getLegalname).findFirst().orElse(""));

		setValue(doc, "//ADMIN_INFO", ds.getContactemail());

		setValue(doc, "//REGISTERED_BY", ds.getRegisteredby());

		setValue(doc, "//LAST_UPDATE/@value", DateUtils.now_ISO8601());

		final Element ef = (Element) doc.selectSingleNode("//EXTRA_FIELDS");

		addExtraField(ef, "OpenAireDataSourceId", ds.getId());

		addExtraField(ef, "NamespacePrefix", ds.getNamespaceprefix());

		addExtraField(ef, "VERIFIED", "NO");

		addExtraField(ef, "aggregatorName", ds.getAggregator());

		addExtraField(ef, "dateOfValidation", ds.getDateofvalidation());

		addExtraField(ef, "dateOfCollection", ds.getDateofcollection());

		addExtraField(ef, "mergeHomonyms", "NO");

		addExtraField(ef, "ACTID", ds.getActivationId());

		if (apis != null) {

			final Element ifaces = (Element) doc.selectSingleNode("//INTERFACES");

			apis.forEach(api -> addInterface(ifaces, api));

		}

		return doc.asXML();

	}

	private static void setValue(final Document doc, final String xpath, final Object value) {

		if (value != null) {

			doc.selectSingleNode(xpath).setText(value.toString());

		}

	}

	private static void addInterface(final Element ifaces, final Api<?> api) {

		final Element ifc = ifaces.addElement("INTERFACE");

		ifc.addAttribute("id", api.getId());

		ifc.addAttribute("label", String.format("%s (%s)",

				StringUtils.defaultIfBlank(api.getTypology(), "-"),

				StringUtils.defaultIfBlank(api.getCompatibility(), "-")));

		ifc.addAttribute("typology", StringUtils.defaultIfBlank(api.getTypology(), ""));

		ifc.addAttribute("active", "" + BooleanUtils.toBooleanDefaultIfNull(api.getActive(), false));

		ifc.addAttribute("compliance", StringUtils.defaultIfBlank(api.getCompatibility(), ""));

		ifc.addAttribute("contentDescription", StringUtils.defaultIfBlank(api.getContentdescription(), ""));

		ifc.addAttribute("removable", "" + BooleanUtils.toBooleanDefaultIfNull(api.getRemovable(), false));

		final Element ap = ifc.addElement("ACCESS_PROTOCOL");

		api.getApiParams().forEach(p -> ap.addAttribute(p.getParam(), p.getValue()));

		ap.setText(StringUtils.defaultIfBlank(api.getProtocol(), ""));

		ifc.addElement("BASE_URL").setText(StringUtils.defaultIfBlank(api.getBaseurl(), ""));

		if (api.isCompatibilityOverrided()) {

			addInterfaceExtraField(ifc, "overriding_compliance", true);

		}

		addInterfaceExtraField(ifc, "metadata_identifier_path", api.getMetadataIdentifierPath());

		addInterfaceExtraField(ifc, "last_collection_date", api.getLastCollectionDate());

		addInterfaceExtraField(ifc, "last_collection_mdId", api.getLastCollectionMdid());

		addInterfaceExtraField(ifc, "last_collection_total", api.getLastCollectionTotal());

		addInterfaceExtraField(ifc, "last_aggregation_date", api.getLastAggregationDate());

		addInterfaceExtraField(ifc, "last_aggregation_mdId", api.getLastAggregationMdid());

		addInterfaceExtraField(ifc, "last_aggregation_total", api.getLastAggregationTotal());

		addInterfaceExtraField(ifc, "last_download_date", api.getLastDownloadDate());

		addInterfaceExtraField(ifc, "last_download_objId", api.getLastDownloadObjid());

		addInterfaceExtraField(ifc, "last_download_total", api.getLastDownloadTotal());

	}

	private static void addExtraField(final Element parent, final String field, final Object value) {

		if (value != null && StringUtils.isNotBlank(value.toString())) {

			final Element f = parent.addElement("FIELD");

			f.addElement("key").setText(field);

			f.addElement("value").setText(value.toString());

		}

	}

	private static void addInterfaceExtraField(final Element parent, final String field, final Object value) {

		if (value != null && StringUtils.isNotBlank(value.toString())) {

			final Element f = parent.addElement("INTERFACE_EXTRA_FIELD");

			f.addAttribute("name", field);

			f.setText(value.toString());

		}

	}

	public static Map<String, Object> dsToMap(final Datasource<Organization<?>, Identity> ds) {

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

		map.put("id", ds.getId());

		map.put("officialname", ds.getOfficialname());

		map.put("englishname", ds.getEnglishname());

		map.put("websiteurl", ds.getWebsiteurl());

		map.put("logourl", ds.getLogourl());

		map.put("contactemail", ds.getContactemail());

		map.put("latitude", ds.getLatitude());

		map.put("longitude", ds.getLongitude());

		map.put("timezone", ds.getTimezone());

		map.put("namespaceprefix", ds.getNamespaceprefix());

		map.put("languages", ds.getLanguages());

		map.put("od_contenttypes", ds.getOd_contenttypes());

		map.put("collectedfrom", ds.getCollectedfrom());

		map.put("dateofvalidation", ds.getDateofvalidation());

		map.put("typology", ds.getTypology());

		map.put("provenanceaction", ds.getProvenanceaction());

		map.put("platform", ds.getPlatform());

		map.put("activationid", ds.getActivationId());

		map.put("description", ds.getDescription());

		map.put("releasestartdate", ds.getReleasestartdate());

		map.put("releaseenddate", ds.getReleaseenddate());

		map.put("missionstatementurl", ds.getMissionstatementurl());

		map.put("dataprovider", ds.getDataprovider());

		map.put("serviceprovider", ds.getServiceprovider());

		map.put("databaseaccesstype", ds.getDatabaseaccesstype());

		map.put("datauploadtype", ds.getDatauploadtype());

		map.put("databaseaccessrestriction", ds.getDatabaseaccessrestriction());

		map.put("datauploadrestriction", ds.getDatauploadrestriction());

		map.put("versioning", ds.getVersioning());

		map.put("citationguidelineurl", ds.getCitationguidelineurl());

		map.put("qualitymanagementkind", ds.getQualitymanagementkind());

		map.put("pidsystems", ds.getPidsystems());

		map.put("certificates", ds.getCertificates());

		map.put("aggregator", ds.getAggregator());

		map.put("issn", ds.getIssn());

		map.put("eissn", ds.getEissn());

		map.put("lissn", ds.getLissn());

		map.put("registeredby", ds.getRegisteredby());

		map.put("subjects", ds.getSubjects());

		map.put("managed", ds.getManaged());

		return map;

	}

	public static Map<String, Object> orgToMap(final String dsId, final Organization<?> org) {

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

		map.put("dsId", dsId);

		map.put("orgId", org.getId());

		map.put("legalname", org.getLegalname());

		map.put("legalshortname", org.getLegalshortname());

		map.put("websiteurl", org.getWebsiteurl());

		map.put("logourl", org.getLogourl());

		map.put("country", org.getCountry());

		return map;

	}

	public static Map<String, Object> apiToMap(final Api<?> api) {

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

		map.put("apiId", api.getId());

		map.put("protocol", api.getProtocol());

		map.put("baseUrl", api.getBaseurl());

		map.put("dsId", api.getDatasource());

		map.put("contentDescription", api.getContentdescription());

		map.put("typology", api.getTypology());

		map.put("compatibility", api.getCompatibility());

		map.put("metadataIdentifierPath", api.getMetadataIdentifierPath());

		map.put("removable", api.getRemovable());

		// The other fields are not required in the INSERT operation

		return map;

	}

	@SuppressWarnings("unchecked")

	private static <T> T castObject(final Object o, final Class<T> clazz) {

		if (o == null) { return null; }

		if (clazz.isInstance(o)) { return (T) o; }

		throw new IllegalArgumentException("Type not managed: " + clazz.getSimpleName());

	}

}

package eu.dnetlib.enabling.datasources;

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

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

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

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

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

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

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

}

package eu.dnetlib.enabling.datasources;

import java.io.StringWriter;

import java.util.*;

import java.util.stream.Collectors;

import com.google.common.collect.ImmutableMap;

import eu.dnetlib.enabling.datasources.DatasourceManagerClients.AfterSqlUpdate;

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

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;

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 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 Log log = LogFactory.getLog(LocalOpenaireDatasourceManagerImpl.class);

	private static final String REPO_PROFILEID_SUFFIX = "_UmVwb3NpdG9yeVNlcnZpY2VSZXNvdXJjZXMvUmVwb3NpdG9yeVNlcnZpY2VSZXNvdXJjZVR5cGU=";

	@Override

	@Transactional(readOnly = true)

	public Set<String> listManagedDatasourceIds() throws DsmRuntimeException {

		try {

			return datasourceManagerClients.searchSQL("SELECT id FROM dsm_datasources 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();

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

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

			} else {

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

				sql.append(field);

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

				sql.append(":value");

			}

			return datasourceManagerClients

					.searchSQL(sql.toString(), ImmutableMap.of("value", field.equalsIgnoreCase("__search__") ? "%" + value + "%" : value))

					.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> ds) throws DsmException {

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

			ds.setAggregator("OPENAIRE");

		}

		ds.setManaged(true);

		datasourceManagerClients.updateSQL(ds.getId(), addDs, AfterSqlUpdate.NONE, 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, AfterSqlUpdate.NONE, orgParams);

			}

		}

		datasourceManagerClients.regenerateProfile(ds.getId());

	}

	@Override

	@Transactional

	public void deleteDs(final String dsId) throws DsmException {

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

	}

	@Override

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

		return datasourceManagerClients.getDatasourceById(fixDsId(dsId));

	}

	@Override

	public void regenerateProfiles() throws DsmException {

		datasourceManagerClients.regenerateProfiles();

	}

	@Override

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

		return datasourceManagerClients.getApis(fixDsId(dsId));

	}

	@Override

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

		final String id = fixDsId(dsId);

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

		params.put("managed", managed);

		params.put("dsId", id);

		datasourceManagerClients.updateSQL(id, setManaged, AfterSqlUpdate.UPDATE_DS_PROFILE, params);

	}

	@Override

	public boolean isManaged(final String dsId) throws DsmException {

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

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

	}

	@Override

	@Transactional

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

		final String id = fixDsId(dsId);

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

		params.put("active", active);

		params.put("apiId", apiId);

		params.put("dsId", id);

		datasourceManagerClients.updateSQL(id, setActive, AfterSqlUpdate.NONE, params);

		if (!active) {

			datasourceManagerClients.updateSQL(dsId, resetLastOperationsInfo, AfterSqlUpdate.NONE, params);

		}

		setManaged(id, true); // It also update the IS profile

	}

	@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 datasource = :dsId AND active = true";

		return !datasourceManagerClients.searchSQL(q, ImmutableMap.of("dsId", fixDsId(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 datasource = :dsId AND active != true AND removable = true";

		return !datasourceManagerClients.searchSQL(q, ImmutableMap.of("dsId", fixDsId(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 = fixDsId(dsId);

		if (!override) {

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

		} else if (level != null) {

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

		} else {

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

		}

		setManaged(id, true); // It also update the IS profile

	}

	@Override

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

			throws DsmException {

		setLastOperationInfo(setLastCollectionInfo, fixDsId(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, fixDsId(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, fixDsId(dsId), apiId, objId, size, date);

	}

	@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, AfterSqlUpdate.NONE, params);

		setManaged(dsId, true); // It also update the IS profile

	}

	@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 = fixDsId(dsId);

		// Delete old params

		datasourceManagerClients.updateSQL(id, "DELETE FROM dsm_apiparams WHERE api = :api", AfterSqlUpdate.NONE, 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, AfterSqlUpdate.NONE, sqlParams);

		}

		// Update the BaseURL

		datasourceManagerClients.updateSQL(id,

				"UPDATE dsm_api SET baseurl = :baseurl WHERE id = :api",

				AfterSqlUpdate.NONE,

				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",

				AfterSqlUpdate.NONE,

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

		setManaged(id, true); // It also update the IS profile

	}

	@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, AfterSqlUpdate.NONE, 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, AfterSqlUpdate.NONE, sqlParams);

				} catch (final DsmException e) {

					throw new RuntimeException(e);

				}

			});

		}

		setManaged(api.getDatasource(), true); // It also update the IS profile

	}

	@Override

	@Transactional

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

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

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

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

		setManaged(dsId, true); // It also update the IS profile

	}

	public DatasourceManagerClients getDatasourceManagerClients() {

		return datasourceManagerClients;

	}

	private String fixDsId(final String dsId) throws DsmException {

		return dsId.endsWith(REPO_PROFILEID_SUFFIX) ? datasourceManagerClients.findDatasourceId(dsId) : dsId;

	}

	@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;

	}

}

package eu.dnetlib.enabling.datasources;

import java.io.StringReader;

import java.text.ParseException;

import java.util.Date;

import java.util.HashMap;

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.dom4j.Document;

import org.dom4j.io.SAXReader;

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 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.is.lookup.rmi.ISLookUpDocumentNotFoundException;

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

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

import eu.dnetlib.enabling.is.registry.rmi.ISRegistryService;

import eu.dnetlib.enabling.locators.UniqueServiceLocator;

import org.springframework.transaction.annotation.Transactional;

public class DatasourceManagerClients {

	private static final String REPOSITORY_SERVICE_RESOURCE_TYPE = "RepositoryServiceResourceType";

	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 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;

	public enum AfterSqlUpdate {

		DELETE_DS_PROFILE, UPDATE_DS_PROFILE, NONE

	}

	@Autowired

	private UniqueServiceLocator serviceLocator;

	public String findDatasourceId(final String profileId) throws DsmException {

		try {

			return serviceLocator.getService(ISLookUpService.class).getResourceProfileByQuery(

					"/*[.//RESOURCE_IDENTIFIER/@value='" + profileId + "']//EXTRA_FIELDS/FIELD[./key='OpenAireDataSourceId']/value/text()");

		} catch (final Exception e) {

			log.error("Error finding dsId of profile " + profileId, e);

			throw new DsmException(-1, "Error finding dsId of profile " + profileId, e);

		}

	}

	public String getDatasourceProfile(final String dsId) throws DsmException {

		try {

			return serviceLocator.getService(ISLookUpService.class)

					.getResourceProfileByQuery(

							"collection('/db/DRIVER/RepositoryServiceResources/RepositoryServiceResourceType')/*[.//EXTRA_FIELDS/FIELD[./key='OpenAireDataSourceId']/value/text() = '"

									+ dsId + "']");

		} catch (final Exception e) {

			return null;

		}

	}

	public boolean deleteProfile(final String dsId) throws DsmException {

		try {

			final SAXReader reader = new SAXReader();

			final String profile = getDatasourceProfile(dsId);

			if (profile != null) {

				final Document docOld = reader.read(new StringReader(profile));

				final String profId = docOld.valueOf("//RESOURCE_IDENTIFIER/@value");

				serviceLocator.getService(ISRegistryService.class).deleteProfile(profId);

			}

			return true;

		} catch (final Exception e) {

			log.error("Error deleting profile", e);

			throw new DsmException(-1, "Error deleting profile", e);

		}

	}

	public boolean regenerateProfile(final String dsId) throws DsmException {

		return regenerateProfile(dsId, serviceLocator.getService(ISRegistryService.class));

	}

	public boolean regenerateProfile(final String dsId, final ISRegistryService registry) throws DsmException {

		final Datasource<Organization<?>, Identity> ds = getDatasourceById(dsId);

		final List<Api<ApiParam>> apis = getApis(dsId);

		try {

			final String oldProfile = getDatasourceProfile(dsId);

			if (oldProfile != null) {

				final Document docOld = new SAXReader().read(new StringReader(oldProfile));

				final String profId = docOld.valueOf("//RESOURCE_IDENTIFIER/@value");

				final String profile = DatasourceFunctions.dsToProfile(ds, apis, profId);

				registry.updateProfile(profId, profile, REPOSITORY_SERVICE_RESOURCE_TYPE);

				log.info("Profile " + profId + " UPDATED for ds " + dsId);

			} else {

				final String profile = DatasourceFunctions.dsToProfile(ds, apis, "");

				final String profId = registry.registerProfile(profile);

				log.info("Valid Profile " + profId + " REGISTERED for ds " + dsId);

			}

			return true;

		} catch (final Exception e) {

			log.error("Error saving profile, id: " + dsId, e);

			throw new DsmException(-1, "Error regenerating profile", e);

		}

	}

	@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 AfterSqlUpdate op, final Map<String, Object> sqlparams)

			throws DsmException {

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

		jdbcTemplate.update(sql, sqlparams);

		switch (op) {

		case DELETE_DS_PROFILE:

			deleteProfile(dsId);

			break;

		case UPDATE_DS_PROFILE:

			regenerateProfile(dsId, serviceLocator.getService(ISRegistryService.class));

			break;

		default:

			break;

		}

	}

	@Transactional

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

			throws DsmException {

		try {

			updateSQL(dsId, IOUtils.toString(sqlResource.getInputStream()), op, 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> 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> 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;

	}

	@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);

		}

	}

	@Transactional(readOnly = true)

	public void regenerateProfiles() throws DsmException {

		final ISRegistryService registry = serviceLocator.getService(ISRegistryService.class);

		searchSQL("SELECT id FROM dsm_datasources", new HashMap<>()).stream()

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

				.forEach(id -> {

					try {

						regenerateProfile(id, registry);

					} catch (final DsmException e) {

						log.error("Error regeneating profile: " + id, e);

					}

				});

	}

	public NamedParameterJdbcTemplate getJdbcTemplate() {

		return jdbcTemplate;

	}

	@Required

	public void setJdbcTemplate(final NamedParameterJdbcTemplate jdbcTemplate) {

		this.jdbcTemplate = jdbcTemplate;

	}

}

package eu.dnetlib.enabling.datasources;

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

public class DbBrowsableField extends BrowsableField {

	private String sql;

	public DbBrowsableField() {

		super();

	}

	public DbBrowsableField(final String id, final String label, final String sql) {

		super(id, label);

		this.sql = sql;

	}

	public String getSql() {

		return sql;

	}

	public void setSql(String sql) {

		this.sql = sql;

	}

}

alter table dsm_api add column compatibility_override varchar(255) default NULL;

<?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_datasources d on (a.datasource = d.id) left outer join dsm_datasource_organization dao on (d.id = dao.datasource) 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 a.typology as term, count(*) as count from dsm_api a group by a.typology 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" />

			</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>

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

dnet.openaire.db.username = dnet

dnet.openaire.db.password =

UPDATE dsm_api

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

WHERE id = :apiId AND datasource = :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 datasource = :dsId

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

SELECT

	a.id                                                AS "id",

	a.protocol                                          AS "protocol",

	a.baseurl                                           AS "baseUrl",

	a.datasource                                        AS "datasource",

	a.contentdescription                                AS "contentdescription",

	a.active                                            AS "active",

	a.removable                                         AS "removable",

	a.typology                                          AS "typology",

	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.datasource = :dsId

GROUP BY

	a.id,

	a.protocol,

	a.baseurl,

	a.datasource,

	a.contentdescription,

	a.active,

	a.removable,

	a.typology,

	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

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

SELECT

	ds.id           AS id,

	ds.officialname AS name,

	ds.typology     AS typology,

	array_agg(a.id) AS apis

FROM

	dsm_datasources ds

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

WHERE

	ds.typology = :type

GROUP BY

	ds.id, ds.officialname, ds.typology

ORDER BY

	 ds.officialname

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.od_contenttypes           AS "od_contenttypes",

	ds.collectedfrom             AS "collectedfrom",

	ds.dateofvalidation          AS "dateofvalidation",

	ds.typology                  AS "typology",

	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.dataprovider              AS "dataprovider",

	ds.serviceprovider           AS "serviceprovider",

	ds.databaseaccesstype        AS "databaseaccesstype",

	ds.datauploadtype            AS "datauploadtype",

	ds.databaseaccessrestriction AS "databaseaccessrestriction",

	ds.datauploadrestriction     AS "datauploadrestriction",

	ds.versioning                AS "versioning",

	ds.citationguidelineurl      AS "citationguidelineurl",

	ds.qualitymanagementkind     AS "qualitymanagementkind",

	ds.pidsystems                AS "pidsystems",

	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"

FROM

	dsm_datasources ds

WHERE

	ds.id = :dsId

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, a.last_download_date::text, '')  AS "aggrDate",

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

	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"

FROM dsm_api a

	LEFT OUTER JOIN dsm_datasources ds ON (a.datasource = ds.id)

	LEFT OUTER JOIN dsm_datasource_organization dsorg ON (ds.id = dsorg.datasource)

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

WHERE

	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_aggregation_total,

	a.last_download_total,

	ds.id,

	ds.officialname,

	ds.englishname,

	ds.namespaceprefix,

	ds.websiteurl

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",

	a.typology                                                               AS "type",

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

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

	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"

FROM dsm_api a

	LEFT OUTER JOIN dsm_datasources ds ON (a.datasource = ds.id)

	LEFT OUTER JOIN dsm_datasource_organization dsorg ON (ds.id = dsorg.datasource)

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

GROUP BY

	a.id,

	a.protocol,

	a.compatibility_override,

	a.compatibility,

	a.active,

	a.last_aggregation_date,

	a.last_download_date,

	a.last_aggregation_total,

	a.last_download_total,

	ds.id,

	ds.officialname,

	ds.englishname,

	ds.namespaceprefix,

	ds.websiteurl

) AS t WHERE

INSERT INTO dsm_api(

	id,

	protocol, 

	baseurl,

	datasource,

	contentdescription,

	typology,

	compatibility,

	metadata_identifier_path,

	removable

) VALUES (

	:apiId,

	:protocol, 

	:baseUrl,

	:dsId,

	:contentDescription,

	:typology,

	:compatibility,

	:metadataIdentifierPath,

	:removable

)

INSERT INTO dsm_datasources(

	id,

	officialname,

	englishname,

	websiteurl,

	logourl,

	contactemail,

	latitude,

	longitude,

	timezone,

	namespaceprefix,

	languages,--array

	od_contenttypes,

	collectedfrom,

	dateofvalidation,

	typology,

	provenanceaction,

	platform,

	activationid,

	description,

	releasestartdate,

	releaseenddate,

	missionstatementurl,

	dataprovider,

	serviceprovider,

	databaseaccesstype,

	datauploadtype,

	databaseaccessrestriction,

	datauploadrestriction,

	versioning,

	citationguidelineurl,

	qualitymanagementkind,

	pidsystems,

	certificates,

	aggregator,

	issn,

	eissn,

	lissn,

	registeredby,

	subjects,--array

	managed

) VALUES (

	:id,

	:officialname,

	:englishname,

	:websiteurl,

	:logourl,

	:contactemail,

	:latitude,

	:longitude,

	:timezone,

	:namespaceprefix,

	:languages,--array

	:od_contenttypes,

	:collectedfrom,

	:dateofvalidation,

	:typology,

	:provenanceaction,

	:platform,

	:activationid,

	:description,

	:releasestartdate,

	:releaseenddate,

	:missionstatementurl,

	:dataprovider,

	:serviceprovider,

	:databaseaccesstype,

	:datauploadtype,

	:databaseaccessrestriction,

	:datauploadrestriction,

	:versioning,

	:citationguidelineurl,

	:qualitymanagementkind,

	:pidsystems,

	:certificates,

	:aggregator,

	:issn,

	:eissn,

	:lissn,

	:registeredby,

	:subjects,--array

	:managed

)

UPDATE dsm_api

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