LookupServiceImpl.java

/*
 * Copyright 2019 Global Crop Diversity Trust
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.gringlobal.compatibility.service.impl;

import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import lombok.extern.slf4j.Slf4j;
import org.genesys.blocks.model.AuditedVersionedModel;
import org.gringlobal.compatibility.service.DataviewService;
import org.gringlobal.compatibility.service.LookupService;
import org.gringlobal.compatibility.service.VirtualLookupService;
import org.gringlobal.model.SysTable;
import org.gringlobal.model.SysTableField;
import org.gringlobal.persistence.SysTableRepository;
import org.gringlobal.soap.Datatable;
import org.gringlobal.soap.Datatable.HasChanges;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional(readOnly = true)
@Slf4j
public class LookupServiceImpl implements LookupService {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private SysTableRepository sysTableRepository;

	@Autowired
	private DataviewService dataviewService;

	@Autowired
	private VirtualLookupService virtualLookupsService;

	@Override
	public Datatable getAllLookupTableStats() {

		final Datatable result = new Datatable("get_lookup_table_stats", List.of(
			new Datatable.Column("dataview_name", String.class),
			new Datatable.Column("title", String.class),
			new Datatable.Column("description", String.class),
			new Datatable.Column("pk_field_name", String.class),
			new Datatable.Column("table_name", String.class),
			new Datatable.Column("min_pk", Long.class),
			new Datatable.Column("max_pk", Long.class),
			new Datatable.Column("row_count", Long.class),
			new Datatable.Column("max_modified_date", Instant.class),
			new Datatable.Column("max_created_date", Instant.class),
			new Datatable.Column("last_touched_date", Instant.class)
		));

		final String dataviewInfoSql = getSqlTemplate("all_lookup_tables.sql");
		final long languageId = 1;
		final List<Map<String, Object>> dataviews = jdbcTemplate.queryForList(dataviewInfoSql, languageId);

		for (final Map<String, Object> dataview : dataviews) {

			final String dataviewName = (String) dataview.get("dataview_name");
			final String tableName = (String) dataview.get("table_name");
			final LookupStats tableStats = getLookupTableStats(tableName);

			result.addRow(
				HasChanges.original,
				//
				dataviewName,
				//
				dataview.get("title"),
				//
				dataview.get("description"),
				//
				dataview.get("pk_field_name"),
				//
				tableName,
				//
				tableStats != null ? tableStats.minPk : 0,
				//
				tableStats != null ? tableStats.maxPk : 0,
				//
				tableStats != null ? tableStats.rowCount : 0,
				//
				tableStats != null ? tableStats.maxModifiedDate : null,
				//
				tableStats != null ? tableStats.maxCreatedDate : null,
				//
				tableStats != null ? tableStats.lastTouchedDate : null);
		}

		// Include virtual lookups
		virtualLookupsService.addAllLookupTableStats(result);

		return result;
	}

	private String getSqlTemplate(final String resourceName) {
		assert resourceName != null;
		// assert(resourceName.startsWith("/"));

		String sqlTemplate;
		try {
			log.trace("Loading SQL template from {}", resourceName);
			final URL resource = LookupServiceImpl.class.getResource("/compatibility/" + resourceName);
			final Path resourcePath = Paths.get(resource.toURI());
			sqlTemplate = Files.readString(resourcePath, StandardCharsets.UTF_8);
		} catch (IOException | URISyntaxException e) {
			throw new RuntimeException("Failed to read SQL template " + e.getMessage(), e);
		}
		return sqlTemplate;
	}

	private LookupStats getLookupTableStats(final String tableName) {
		log.debug("getLookupTableStats for {}", tableName);

		final SysTable table = sysTableRepository.findByTableName(tableName);
		assert table != null;
		final Optional<SysTableField> pkField = table.getFields().stream().filter(field -> field.getIsPrimaryKey().equals("Y")).findFirst();
		if (!pkField.isPresent()) {
			throw new RuntimeException("No PK field in " + tableName);
		}

		String sql = getSqlTemplate("lookup_table_stats.sql");
		sql = sql.replaceAll("\\{0\\}", tableName).replaceAll("\\{1\\}", pkField.get().getFieldName());
		log.trace(sql);

		/*
		 * Handle GGCE tables
		 */
		var entityInfo = dataviewService.getEntityInfo(tableName);
		if (entityInfo != null && AuditedVersionedModel.class.isAssignableFrom(entityInfo.getTarget())) {
			sql = sql.replaceAll("([\\s\\(])\\s*modified_date([,\\s\\)])", "$1last_modified_date$2");
			log.trace("Adapted query for AuditedVersionedModel:\n{}", sql);
		}

		final LookupStats lookupStats = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
			final LookupStats stats = new LookupStats();
			stats.tableName = rs.getString(1);
			stats.pkFieldName = rs.getString(2);
			stats.minPk = rs.getLong(3);
			stats.maxPk = rs.getLong(4);
			stats.rowCount = rs.getLong(5);

			Optional.ofNullable(rs.getObject(6, Timestamp.class)).ifPresent((date) -> stats.maxModifiedDate = date.toInstant());
			Optional.ofNullable(rs.getObject(7, Timestamp.class)).ifPresent((date) -> stats.maxCreatedDate = date.toInstant());
			Optional.ofNullable(rs.getObject(8, Timestamp.class)).ifPresent((date) -> stats.lastTouchedDate = date.toInstant());

			return stats;
		});

		return lookupStats;
	}

	public static final class LookupStats {
		String tableName;
		String pkFieldName;
		long minPk;
		long maxPk;
		long rowCount;
		Instant maxCreatedDate;
		Instant maxModifiedDate;
		Instant lastTouchedDate;

		public LookupStats() {
		}

		public LookupStats(Long minPk, Long maxPk, Long rowCount, Instant maxCreatedDate,
				Instant maxModifiedDate, Instant lastTouchedDate) {
			if (minPk != null) this.minPk = minPk;
			if (maxPk != null) this.maxPk = maxPk;
			if (rowCount != null) this.rowCount = rowCount;

			this.maxCreatedDate = maxCreatedDate;
			this.maxModifiedDate = maxModifiedDate;
			this.lastTouchedDate = lastTouchedDate;

			if (lastTouchedDate == null) {
				this.lastTouchedDate = maxModifiedDate;
			}
			if (lastTouchedDate == null && maxModifiedDate == null) {
				this.lastTouchedDate = maxCreatedDate;
			}
		}

//		public LookupStats(Long minPk, Long maxPk, Long rowCount, Timestamp maxCreatedDate,
//				Timestamp maxModifiedDate, Timestamp lastTouchedDate) {
//			if (minPk != null) this.minPk = minPk;
//			if (maxPk != null) this.maxPk = maxPk;
//			if (rowCount != null) this.rowCount = rowCount;
//
//			Optional.ofNullable(maxCreatedDate).ifPresent((timestamp) -> this.maxCreatedDate = timestamp.toInstant());
//			Optional.ofNullable(maxModifiedDate).ifPresent((timestamp) -> this.maxModifiedDate = timestamp.toInstant());
//			Optional.ofNullable(lastTouchedDate).ifPresent((timestamp) -> this.lastTouchedDate = timestamp.toInstant());
//
//			if (this.lastTouchedDate == null) {
//				this.lastTouchedDate = this.maxModifiedDate;
//			}
//			if (this.lastTouchedDate == null && this.maxModifiedDate == null) {
//				this.lastTouchedDate = this.maxCreatedDate;
//			}
//		}

		public String getTableName() {
			return tableName;
		}

		public String getPkFieldName() {
			return pkFieldName;
		}
	}
}