DataviewServiceImpl.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.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Instant;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.Set;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import javax.annotation.Resource;
import javax.persistence.AssociationOverride;
import javax.persistence.DiscriminatorValue;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.PersistenceContext;
import javax.persistence.Table;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Root;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.StopWatch;
import org.apache.commons.text.WordUtils;
import org.genesys.blocks.security.model.AclSid;
import org.gringlobal.api.exception.InvalidApiUsageException;
import org.gringlobal.api.exception.NotFoundElement;
import org.gringlobal.compatibility.component.SysTableComponent;
import org.gringlobal.compatibility.service.DataviewService;
import org.gringlobal.compatibility.service.VirtualDataviewService;
import org.gringlobal.compatibility.service.VirtualLookupService;
import org.gringlobal.model.Cooperator;
import org.gringlobal.model.CooperatorOwnedModel;
import org.gringlobal.model.QSysDataview;
import org.gringlobal.model.QSysDataviewField;
import org.gringlobal.model.QSysDataviewFieldLang;
import org.gringlobal.model.QSysDataviewLang;
import org.gringlobal.model.QSysTableField;
import org.gringlobal.model.QSysTableFieldLang;
import org.gringlobal.model.SysDataview;
import org.gringlobal.model.SysDataviewField;
import org.gringlobal.model.SysDataviewFieldLang;
import org.gringlobal.model.SysDataviewLang;
import org.gringlobal.model.SysDataviewParam;
import org.gringlobal.model.SysDataviewSql;
import org.gringlobal.model.SysLang;
import org.gringlobal.model.SysTableField;
import org.gringlobal.model.SysTableFieldLang;
import org.gringlobal.persistence.CooperatorRepository;
import org.gringlobal.persistence.SysDataviewRepository;
import org.gringlobal.service.CRUDService;
import org.gringlobal.service.LanguageService;
import org.gringlobal.soap.Datatable;
import org.gringlobal.soap.Datatable.Column;
import org.gringlobal.spring.TransactionHelper;
import org.hibernate.Hibernate;
import org.gringlobal.soap.GGXml;
import org.jdom2.Attribute;
import org.jdom2.Element;
import org.jdom2.output.Format;
import org.jdom2.output.XMLOutputter;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.config.BeanDefinition;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.annotation.ClassPathScanningCandidateComponentProvider;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.core.ResolvableType;
import org.springframework.core.convert.ConversionService;
import org.springframework.core.type.filter.AnnotationTypeFilter;
import org.springframework.dao.DataAccessException;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ReflectionUtils;

import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQueryFactory;

import lombok.EqualsAndHashCode;
import lombok.Getter;

@Service
@DependsOn("currentApplicationContext")
@Slf4j
public class DataviewServiceImpl implements InitializingBean, DataviewService, ApplicationContextAware {

	private static final String SAVEDACTION_DELETE = "Delete";
	private static final String SAVEDACTION_UPDATE = "Update";
	private static final String SAVEDACTION_INSERT = "Insert";
	private static final String META_EXCEPTION_INDEX = "ExceptionIndex";
	private static final String META_TABLE_NAME = "TableName";
	private static final String META_ORIGINAL_ROW_INDEX = "OriginalRowIndex";
	private static final String META_ORIGINAL_PRIMARY_KEY_ID = "OriginalPrimaryKeyID";
	private static final String META_NEW_PRIMARY_KEY_ID = "NewPrimaryKeyID";
	public static final String META_EXCEPTION_MESSAGE = "ExceptionMessage";
	public static final String META_SAVED_STATUS = "SavedStatus";
	private static final String META_SAVED_ACTION = "SavedAction";
	public static final String SAVEDSTATUS_FAILURE = "Failure";
	public static final String SAVEDSTATUS_SUCCESS = "Success";

	public static final String NULLVALUE = "{DBNull.Value}";

	public static final String MSSQLENGINE = "sqlserver";
	public static final String MYSQLENGINE = "mysql";
	public static final String POSTGRESENGINE = "postgresql";

	@Autowired
	private VirtualDataviewService virtualDataviewService;

	@Autowired
	private VirtualLookupService virtualLookupService;

	@Autowired
	private SysTableComponent sysTableComponent;

	@Resource
	private Set<Class<?>> entityClassSet;

	@Value("${db.dataviewSql}")
	private String sqlEngine;

	@EqualsAndHashCode
	private static final class Param {
		int pos;
		String name;
		// SysDataviewParam dataviewParameter;

		public Param(final String paramName, final int position, final SysDataviewParam dataviewParameter) {
			this.name = paramName;
			this.pos = position;
			// this.dataviewParameter = dataviewParameter;
		}

		int compareTo(final Param other) {
			return this.pos - other.pos;
		}

		@Override
		public String toString() {
			return name + "@" + pos;
		}
	}

	@Getter
	public final static class JpaEntityInfo<T> {
		// String dbName;
		String dbPrimaryKey;
		private Class<T> target;
		private Field primaryKey;

		public JpaEntityInfo(Class<T> target) {
			this.target = target;
		}

	}

	@Value("${db.jpa.packages}")
	private String[] jpaEntityPackageNames;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@PersistenceContext
	private EntityManager entityManager;

	@Autowired
	private JPAQueryFactory jpaQueryFactory;

	@Autowired
	private LanguageService languageService;

	@Autowired
	private SysDataviewRepository dataviewRepository;

	@Autowired
	private ConversionService conversionService;

	@Autowired
	private CooperatorRepository cooperatorRepository;

	// Static cache of fields (by fieldName) in target entity class
	private static final Map<String, Map<String, Optional<Field>>> entityFieldMap = new HashMap<>(200);

	/**
	 * Detected JPA entities mapped by DB table names
	 */
	private Map<String, JpaEntityInfo<?>> tablesToEntities;

	/**
	 * Mapping of Fields in classes (by name) to JpaEntityInfo, populated on demand.
	 */
	final Map<String, Map<Field, Optional<JpaEntityInfo<?>>>> jpaEntityInfoByField = new HashMap<>();

	/**
	 * Entity repositories
	 */
	private final Map<Class<?>, JpaRepository<?, ?>> entityRepositories = new HashMap<>();
	/**
	 * Entity CRUD services
	 */
	private final Map<Class<?>, CRUDService<?>> entityServices = new HashMap<>();

	private XMLOutputter xmlOutputter;

	@Value("${dataview.max.rows}")
	private int maxRowsLimit;

	private ApplicationContext applicationContext;

	@Override
	public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
		this.applicationContext = applicationContext;
	}

	@Override
	public void afterPropertiesSet() throws Exception {
		xmlOutputter = new XMLOutputter(Format.getCompactFormat());
		tablesToEntities = Collections.unmodifiableMap(findJpaEntityClasses());
	}

	@SuppressWarnings("unchecked")
	private <T> JpaRepository<T, ?> getRepository(Class<T> target) {
		return (JpaRepository<T, ?>) entityRepositories.computeIfAbsent(target, key -> {
			var repository = (JpaRepository<T, ?>) applicationContext.getBeanProvider(ResolvableType.forClassWithGenerics(JpaRepository.class, key, Long.class)).getIfAvailable();
			if (repository == null) {
				log.warn("No JPA repository for {} found in application context.", key);
			} else {
				log.debug("Found JPA repository {} for {}", repository, key);
			}
			return repository;
		});
	}

	@SuppressWarnings("unchecked")
	private <T> CRUDService<T> getCrudService(Class<T> target) {
		return (CRUDService<T>) entityServices.computeIfAbsent(target, key -> {
			var crudService = (CRUDService<?>) applicationContext.getBeanProvider(ResolvableType.forClassWithGenerics(CRUDService.class, key)).getIfAvailable();
			if (crudService == null) {
				log.warn("No CRUDService for {} found in application context.", key);
			} else {
				log.debug("Found CRUDService {} for {}", crudService, key);
			}
			return crudService;
		});
	}

	@Override
	public final JpaEntityInfo<?> getEntityInfo(final String tableName) {
		return tablesToEntities.get(tableName);
	}

	@Override
	@PreAuthorize("isAuthenticated()")
	@Transactional
	public Datatable transferOwnership(final long newOwnerCooperatorID, final boolean includeDescendents, final List<Element> targets) {

		final Set<String> dataviewNames = targets.stream().map(Element::getName).collect(Collectors.toSet());

		final Cooperator newOwner = cooperatorRepository.findById(newOwnerCooperatorID).orElseThrow(() -> new RuntimeException("No such cooperator"));

		final var newOwnedDate = Instant.now();
		

		final Cooperator currentCooperator = UserServiceImpl.sysUserFromAuthentication().getCooperator();

		// Include all redirects
		final Set<Cooperator> currentOwners = Sets.newHashSet(currentCooperator);
		currentOwners.addAll(cooperatorRepository.findRedirects(currentCooperator));

		// Recursion stopper
		final Set<Class<?>> handledTypes = new HashSet<>();

		for (final String dataviewName : dataviewNames) {
			final SysDataview dataview = dataviewRepository.findByDataviewName(dataviewName);
			log.info("Updating ownership through {} dataview", dataview.getDataviewName());

			final SysDataviewField dvPkField = dataview.getFields().stream()
				// DV PK fields to DB Table fields
				.filter(dvField -> Objects.equals(dvField.getIsPrimaryKey(), "Y"))
				// to tables
				.findFirst().orElse(null);

			final Set<String> pkValues = targets.stream()
				// Matching rows
				.filter(target -> target.getName().equals(dataviewName))
				// Get PK field value
				.map(target -> target.getChild(dvPkField.getFieldName()).getValue()).collect(Collectors.toSet());

			if (pkValues.size() == 0) {
				log.debug("No PKs for {} dataview provided, skipping", dataviewName);
				continue;
			}

			final SysTableField dbPkField = dataview.getFields().stream()
				// DV PK fields to DB Table fields
				.filter(dvField -> Objects.equals(dvField.getIsPrimaryKey(), "Y")).map(SysDataviewField::getSysTableField)
				// to tables
				.findFirst().orElse(null);

			if (dbPkField == null) {
				throw new RuntimeException("No PK field in this Dataview");
			}
			log.debug("PK is {}.{}", dbPkField.getTable().getTableName(), dbPkField.getFieldName());

			var targetEntity = tablesToEntities.get(dbPkField.getTable().getTableName());

			if (!CooperatorOwnedModel.class.isAssignableFrom(targetEntity.target)) {
				throw new RuntimeException("Class " + targetEntity.target + " is not a CooperatorOwnedModel");
			}

			// Set<Serializable> targetIds = pkValues.stream().map(pk -> convert(pk,
			// targetEntity.primaryKey.getType())).map(Serializable.class::cast).collect(Collectors.toSet());
			final Collection<Object> targetIds = pkValues.stream().map(pk -> convert(pk, targetEntity.primaryKey.getType())).collect(Collectors.toSet());

			// ResolvableType repositoryType =
			// ResolvableType.forClassWithGenerics(JpaRepository.class, targetEntity.target,
			// Long.class);
			// LOG.warn("Looking for {}", repositoryType.toString());
			//
			// String[] candidates =
			// CurrentApplicationContext.getContext().getBeanNamesForType(repositoryType);
			// LOG.warn("Found {} canidates for {}: {}", candidates.length, repositoryType,
			// candidates);
			// @SuppressWarnings("unchecked")
			// JpaRepository<Object, Serializable> targetRepository = (JpaRepository<Object,
			// Serializable>) CurrentApplicationContext.getContext().getBean(candidates[0]);
			// LOG.warn("Using JPA repository {}", targetRepository);
			//
			// List<Object> entities = targetRepository.findAll(targetIds);
			// for (Object entity : entities) {
			// LOG.debug("Found entity " + entity);
			// CooperatorOwnedModel com = (CooperatorOwnedModel) entity;
			// com.setOwnedDate(new Date());
			// }
			// LOG.warn("Saving {} {}", entities.size(), targetEntity.target);
			// targetRepository.save(entities);

			final List<?> transfers = transferOwnershipForTarget(targetEntity.target, targetEntity.primaryKey.getName(), targetIds, currentOwners, newOwner, newOwnedDate);

			if (includeDescendents && transfers.size() > 0) {
				log.debug("Finding instances where this PK is a foreign key in many-to-one relationships");
				recursivelyTransferOwnership(handledTypes, targetEntity.target, transfers, currentOwners, newOwner, newOwnedDate);
			}

			// throw new RuntimeException("WIP");
		}

		return new Datatable();
	}

	private void recursivelyTransferOwnership(final Set<Class<?>> handledTypes, final Class<?> target, final Collection<?> parents,
			final Collection<Cooperator> currentOwners, final Cooperator newOwner, final Instant newOwnedDate) {
		if (parents.size() == 0) {
			log.warn("Nothing to transfer");
			return;
		}

		if (handledTypes.contains(target)) {
			log.warn("Type {} already handled", target);
			return;
		}
		handledTypes.add(target);

		log.warn("Transferring ownership of entities related to {} to {}", target, newOwner);

		tablesToEntities.values().forEach(jpaEntity -> {
			log.trace("Looking at {} for fields of type {}", jpaEntity.target, target);
			for (final Field field : getAllFields(jpaEntity.target)) {
				if (field.getType().equals(target)) {
					log.debug("Found field {}.{} with type {}", jpaEntity.target, field.getName(), field.getType());
					final ManyToOne manyToOne = field.getAnnotation(ManyToOne.class);
					if (manyToOne != null) {
						log.debug("Updating via {}.{}", jpaEntity.target, field.getName());

						final List<?> entities = transferOwnershipForTarget(jpaEntity.target, field.getName(), parents, currentOwners, newOwner, newOwnedDate);

						if (entities.size() > 0) {
							recursivelyTransferOwnership(handledTypes, jpaEntity.target, entities, currentOwners, newOwner, newOwnedDate);
						}
					}
				}
			}
		});
	}

	private List<?> transferOwnershipForTarget(final Class<?> target, final String keyFieldName, final Collection<?> keyFields,
			final Collection<Cooperator> currentOwners, final Cooperator newOwner, final Instant newOwnedDate) {

		if (!CooperatorOwnedModel.class.isAssignableFrom(target)) {
			log.warn("{} is not a CooperatorOwnedModel", target);
			return Lists.newArrayList();
		}

		log.info("Transfering ownership of {}.{} for {}", target, keyFieldName, keyFields);

		final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
		final CriteriaQuery<?> criteriaQuery = criteriaBuilder.createQuery(target);
		final Root<?> entityRoot = criteriaQuery.from(target);
		final Path<Object> parentField = entityRoot.get(keyFieldName);
		final Path<Object> ownedByField = entityRoot.get("ownedBy");
		criteriaQuery.where(parentField.in(keyFields), ownedByField.in(currentOwners));

		// find matches
		final List<?> entities = entityManager.createQuery(criteriaQuery).getResultList();
		log.info("Found {} related entities {} with matching owner and parent", entities.size(), target);
		for (final Object entity : entities) {
			log.warn("Updating ownership of entity {}", entity);
			final CooperatorOwnedModel com = (CooperatorOwnedModel) entity;
//			com.setOwnedBy(newOwner);
			com.setOwnedDate(newOwnedDate);
		}
		return entities;
	}

	@Override
	@Transactional(readOnly = true)
	@PreAuthorize("isAuthenticated()")
	public Datatable getData(final String dataviewName, final Map<String, String> parameters, final int offset, final int limit, final String options) throws Exception {

		if (StringUtils.startsWithIgnoreCase(dataviewName, VirtualDataviewServiceImpl.GGCE_PREFIX)) {
			log.debug("GGCE handling virtual dataview {}", dataviewName);
			if (StringUtils.endsWithIgnoreCase(dataviewName, VirtualLookupServiceImpl.LOOKUP_SUFFIX)) {
				log.debug("GGCE handling virtual lookup {}", dataviewName);
				return virtualLookupService.getLookupData(dataviewName, parameters, offset, limit, options);
			}

			return virtualDataviewService.getData(dataviewName, parameters, offset, limit, options);
		}

		if (limit > maxRowsLimit) {
			throw new InvalidApiUsageException("The server is configured with a limit of " + maxRowsLimit + " rows. You requested " + limit);
		}


		final SysDataview dataview = dataviewRepository.findOne(QSysDataview.sysDataview.dataviewName.eq(dataviewName)).orElse(null);
		if (dataview == null) {
			log.warn("No dataview for: {}", dataviewName);
			return new Datatable(dataviewName);
		}

		Optional<SysDataviewSql> sqlMaybe = dataview.getSqls().stream().filter(s -> s.getDatabaseEngineTag().equalsIgnoreCase(this.sqlEngine)).findFirst();

		if (!sqlMaybe.isPresent()) {
			sqlMaybe = dataview.getSqls().stream().filter(s -> s.getDatabaseEngineTag().equalsIgnoreCase(MSSQLENGINE)).findFirst();
			if (!sqlMaybe.isPresent()) {
				throw new RuntimeException("SQL for " + this.sqlEngine + "/sqlserver not present for dataview " + dataviewName);
			}
			log.warn("Missing SQL of {} for engine {}. Using sqlserver query", dataviewName, this.sqlEngine);
		}

		// Selected locale
		final SysLang targetLanguage = languageService.getLanguage(LocaleContextHolder.getLocale());

		log.debug("Client parameters: {}", parameters);
		var params = new HashMap<>(parameters); // a modifiable copy of parameters

		final SysDataviewSql sql = sqlMaybe.get();
		String sqlStatement = sql.getSqlStatement();
		if (log.isDebugEnabled()) {
			log.debug("Original SQL statement for {}:\n{}", this.sqlEngine, stripWhitespace(sqlStatement));
		}

		final List<Param> dataviewParams = new ArrayList<>();
		for (final SysDataviewParam param : dataview.getParameters()) {
			final int paramIndex = dataview.getParameters().indexOf(param);
			log.trace("Param {} {}", paramIndex, param.getParamName());

			if (paramIndex == 0 && !params.containsKey(param.getParamName()) && params.containsKey(":idlist")) {
				// :idlist is a generic name for the first dataview parameter
				log.warn("Overwriting {} with values in :idlist", param.getParamName());
				params.put(param.getParamName(), params.get(":idlist"));
			}

			if (param.getParamType().endsWith("COLLECTION")) {
				final List<String> list = numberCollection(params.get(param.getParamName()));
				if (list.size() == 1) {
					params.put(param.getParamName(), list.get(0));
				} else if (list.size() > 1) {
					// MSSQL does not support arrays in JDBC PreparedStatement.
					// We're handling it below.
					continue;
				}
			}

			// Find parameter names that are not quoted
			final Matcher matcher = Pattern.compile("[^'](" + param.getParamName() + ")").matcher(sqlStatement);
			while (matcher.find()) {
				final String paramName = matcher.group(1);
				log.trace("Found param {} @{}:{}", paramName, matcher.start(1), matcher.end(1));
				dataviewParams.add(new Param(paramName, matcher.start(1), param));
			}
		}

		for (final SysDataviewParam param : dataview.getParameters()) {
			if (param.getParamType().endsWith("COLLECTION")) {
				final List<String> list = numberCollection(params.get(param.getParamName()));

				// Dataviews sometime use quoted ':param1' = '-1' comparisons
				sqlStatement = sqlStatement.replace("'" + param.getParamName() + "'", "'" + list.stream().collect(Collectors.joining(",")) + "'");

				if (list.size() > 1) {
					sqlStatement = sqlStatement.replace(param.getParamName(), list.stream().collect(Collectors.joining(",")));
				} else {
					// use prepared statement for single argument
					sqlStatement = sqlStatement.replace(param.getParamName(), "?");
				}
			} else {
				sqlStatement = sqlStatement.replace(param.getParamName(), "?");
			}
		}
		log.trace("Unsorted params: {}", dataviewParams);
		dataviewParams.sort(Param::compareTo);
		log.debug("Sorted params: {}", dataviewParams);

		sqlStatement = sqlStatement.replaceAll("__LANGUAGEID__", targetLanguage.getId().toString());

		log.trace(sqlStatement);
		// apply the limit only if they're doing a select and they specified a valid
		// limit. We can't do it on stored procs or insert / update.
		if ((limit > 0 || offset > 0) && sqlStatement.strip().toUpperCase().startsWith("SELECT")) {
			sqlStatement = applyLimit(sqlEngine, sqlStatement, limit, offset);
			if (log.isDebugEnabled()) {
				log.debug(stripWhitespace(sqlStatement));
				log.trace(sqlStatement);
			}
		}
		log.debug("Modified SQL statement:\n{}", sqlStatement);

		final Datatable datatable = jdbcTemplate.execute(sqlStatement, new PreparedStatementCallback<Datatable>() {
			@Override
			public Datatable doInPreparedStatement(final PreparedStatement ps) throws SQLException, DataAccessException {
				if (dataviewParams != null) {
					int pos = 0;
					for (final Param param : dataviewParams) {
						pos++;
						log.trace("Setting ?" + pos + ": " + param.name + " <-- " + params.get(param.name));
						setParameter(ps, pos, params.get(param.name));
					}
				}
				try (final ResultSet rs = ps.executeQuery()) {
					return Datatable.from(dataviewName, rs, rs.getMetaData(), offset, limit);
				}
			}

			private void setParameter(final PreparedStatement ps, final int pos, final Object coalesce) throws SQLException {
				ps.setObject(pos, coalesce);
			}
		});

		datatable.setReadonly(dataview.getIsReadonly());
		if (Objects.equals(dataviewName, "get_dataview_list")) {
			virtualDataviewService.addVirtualDataviews(datatable);
		} else if (Objects.equals(dataviewName, "get_dataview_parameters") && isVirtualDataviewParameter(params)) {
			virtualDataviewService.addVirtualDataviewParameters(datatable, params.get(":dataview"));
		}
		annotateDataview(datatable, dataview, targetLanguage);

		return datatable;
	}

	private boolean isVirtualDataviewParameter (Map<String, String> params) {
		String dataview = params.get(":dataview");
		return Objects.nonNull(dataview) && dataview.startsWith("ggce_");
	}

	private String stripWhitespace(final String sqlStatement) {
		return sqlStatement.replaceAll("[\\s\\n]+", " ");
	}

	private static List<String> numberCollection(final String strParam) {
		// assert (strParam != null);
		final ArrayList<String> list = new ArrayList<>();
		if (strParam != null) {
			for (final String el : strParam.split("\\s*,\\s*")) {
				list.add(el);
			}
		}
		log.trace("Converted {} to {}", strParam, list);
		return list;
	}

	/**
	 * Apply metadata from Dataview definition
	 *
	 * @param datatable
	 * @param dataview
	 * @param targetLanguage 
	 */
	private void annotateDataview(final Datatable datatable, final SysDataview dataview, final SysLang targetLanguage) {
		final List<SysDataviewField> fields = dataview.getFields();
		if (fields == null || fields.isEmpty()) {
			return;
		}

		final DataviewTranslation dataviewTranslation = translatedDataview(dataview, targetLanguage);

		final Map<SysDataviewField, DvFieldTranslation> dvFieldLangs = new HashMap<>();
		translatedDvFields(dataview, targetLanguage)
			// map
			.forEach(fieldLang -> {
				dvFieldLangs.put(fieldLang.field, fieldLang);
			});

		final Map<SysTableField, DtFieldTranslation> dtFieldLangs = new HashMap<>();
		translatedDtFields(dataview, targetLanguage)
			// map
			.forEach(fieldLang -> {
				dtFieldLangs.put(fieldLang.field, fieldLang);
			});

		if (dataviewTranslation != null) {
			// msprop:title="Get Accession"
			datatable.setProp("title", dataviewTranslation.title);
			// msprop:description="Get Accession"
			datatable.setProp("description", dataviewTranslation.description);
			// msprop:script_direction=""
			datatable.setProp("script_direction", dataviewTranslation.scriptDirection);
		}
		// msprop:transform_by_fields=""
		// msprop:transform_field_for_names=""
		datatable.setProp("transform_field_for_names", dataview.getTransformFieldForNames());
		// msprop:transform_field_for_captions=""
		datatable.setProp("transform_field_for_captions", dataview.getTransformFieldForCaptions());
		// msprop:is_readonly="N"
		datatable.setProp("is_transform", dataview.getIsReadonly());
		// msprop:transform_field_for_values=""
		datatable.setProp("transform_field_for_captions", dataview.getTransformFieldForValues());
		// msprop:is_transform="N"
		datatable.setProp("is_transform", dataview.getIsTransform());
		// msprop:configuration_options=""
		datatable.setProp("configuration_options", dataview.getConfigurationOptions());

		for (final SysDataviewParam dataviewParam : dataview.getParameters()) {
			log.debug("Dataview parameter {} {}", dataviewParam.getParamName(), dataviewParam.getParamType());
		}

		for (final SysDataviewField field : dataview.getFields()) {
			log.trace("Annotating field {}.{}", datatable.getName(), field.getFieldName());

			final Column column = datatable.getColumns().stream().filter(col -> col.getName().equals(field.getFieldName())).findFirst().orElse(null);
			if (column == null) {
				log.warn("Column {} not present in {}", field.getFieldName(), datatable.getName());
				continue;
			}

			final DvFieldTranslation dvFieldLang = dvFieldLangs.get(field);
			// FIXME either return null if dvFieldLang is not translated
			// or fix this mess here!
		
			if (dvFieldLang != null) {
				// msdata:Caption="Accession ID"
				column.setData("Caption", dvFieldLang.title);
				// msprop:title="Accession ID"
				column.setProp("title", dvFieldLang.title);
				// msprop:description="accession_id -- Accession table's primary key (PK) field.
				// (Generated field; cannot be edited.)"
				column.setProp("description", dvFieldLang.description);
			} else if (field.getSysTableField() != null) {
				final DtFieldTranslation dtFieldLang = dtFieldLangs.get(field.getSysTableField());
				if (dtFieldLang != null) {
					// msdata:Caption="Accession ID"
					column.setData("Caption", dtFieldLang.title);
					// msprop:title="Accession ID"
					column.setProp("title", dtFieldLang.title);
					// msprop:description="accession_id -- Accession table's primary key (PK) field.
					// (Generated field; cannot be edited.)"
					column.setProp("description", dtFieldLang.description);
				} else {
					log.debug("No translation in dataview {} for {} {}", dataview.getDataviewName(), field, field.getSysTableField());
				}
			}

			column.setReadonly(Objects.equals(field.getIsReadonly(), "Y"));

			column.setProp("is_visible", field.getIsVisible());
			column.setProp("is_readonly", field.getIsReadonly());
			column.setProp("group_name", field.getGroupName());
			column.setProp("gui_hint", field.getGuiHint());
			// msprop:is_primary_key="Y"
			column.setProp("is_primary_key", field.getIsPrimaryKey());

			column.setProp("configuration_options", field.getConfigurationOptions());

			column.setProp("dataview_name", dataview.getDataviewName());
			// msprop:table_alias_name="a"
			column.setProp("table_alias_name", field.getTableAliasName());
			// msprop:dataview_field_name="accession_id"
			column.setProp("dataview_field_name", field.getFieldName());
			// msprop:foreign_key_dataview_name=""
			column.setProp("foreign_key_dataview_name", field.getForeignKeyDataviewName());
			if (StringUtils.isNotBlank(field.getForeignKeyDataviewName())) {
				// msprop:foreign_key_dataview_param=":createddate=__createddate__;:modifieddate=__modifieddate__;:valuemember=__valuemember__;:startpkey=__startpkey__;:stoppkey=__stoppkey__"
				column.setProp("foreign_key_dataview_param", determineFkDataviewParams(field.getForeignKeyDataviewName()));
			}

			final SysTableField tableField = field.getSysTableField();
			if (tableField != null) {
				// msprop:table_name="accession"
				column.setProp("table_name", tableField.getTable().getTableName());
				// msprop:table_field_data_type_string="INTEGER"
				column.setProp("table_field_data_type_string", tableField.getFieldType());
				if ("DATETIME".equals(tableField.getFieldType())) {
					// msdata:DateTimeMode="Unspecified"
					column.setData("DateTimeMode", "Unspecified");
				}
				// msprop:is_foreign_key="N"
				column.setProp("is_foreign_key", tableField.getIsForeignKey());
				if (tableField.getForeignKeyTableField() != null) {
					// msprop:foreign_key_field_name=""
					column.setProp("foreign_key_field_name", tableField.getForeignKeyTableField().getFieldName());
					// msprop:foreign_key_table_field_name="taxonomy_species_id"
					column.setProp("foreign_key_table_field_name", tableField.getForeignKeyTableField().getFieldName());
				}
				// // msprop:foreign_key_dataview_name=""
				// column.setProp("foreign_key_dataview_name",
				// tableField.getForeignKeyDataviewName());
				// if (StringUtils.isNotBlank(tableField.getForeignKeyDataviewName())) {
				// //
				// msprop:foreign_key_dataview_param=":createddate=__createddate__;:modifieddate=__modifieddate__;:valuemember=__valuemember__;:startpkey=__startpkey__;:stoppkey=__stoppkey__"
				// column.setProp("foreign_key_dataview_param",
				// determineFkDataviewParams(tableField.getForeignKeyDataviewName()));
				// }
				// msprop:is_nullable="N"
				column.setProp("is_nullable", tableField.getIsNullable());
				// msprop:is_autoincrement="Y"
				column.setProp("is_autoincrement", tableField.getIsAutoincrement());
				// msprop:max_length="0"
				column.setProp("max_length", Integer.toString(tableField.getMaxLength()));
				/// msprop:default_value="{DBNull.Value}"
				column.setProp("default_value", tableField.getDefaultValue());

				// msprop:is_readonly_on_insert="Y"

				// msprop:table_field_name="accession_id"
				column.setProp("table_field_name", tableField.getFieldName());

				// update gui_hint
				column.improveProp("gui_hint", tableField.getGuiHint());
			}
		}
	}

	private String determineFkDataviewParams(final String foreignKeyDataviewName) {
		// msprop:foreign_key_dataview_param=":createddate=__createddate__;:modifieddate=__modifieddate__;:valuemember=__valuemember__;:startpkey=__startpkey__;:stoppkey=__stoppkey__"

		// Hardly any GG Dataviews use GGCE lookups, but still...
		if (StringUtils.startsWith(foreignKeyDataviewName, VirtualDataviewServiceImpl.GGCE_PREFIX)
			&& StringUtils.endsWith(foreignKeyDataviewName, VirtualLookupServiceImpl.LOOKUP_SUFFIX)) {
			// Handle gget_..._lookup parameters
			return ":createddate=__createddate__;:modifieddate=__modifieddate__;:valuemember=__valuemember__;:startpkey=__startpkey__;:stoppkey=__stoppkey__";
		}

		final SysDataview dataview = dataviewRepository.findByDataviewName(foreignKeyDataviewName);
		if (dataview == null) {
			log.warn("Dataview {} does not exist, it has no params", foreignKeyDataviewName);
			return null;
		}

		final StringBuilder sb = new StringBuilder();
		dataview.getParameters().forEach(param -> {
			if (sb.length() > 0) {
				sb.append(";");
			}
			sb.append(param.getParamName());
			sb.append("=__");
			// .Append(drParam["param_name"].ToString().Replace(":", "").Replace("@",
			// "").Replace("?", ""))
			sb.append(param.getParamName().replaceAll("[\\:@\\?]+", "").strip());
			sb.append("__");
		});
		log.trace("FK dataview params: {}", sb);
		return sb.toString();
	}

	private static Pattern __ORDERBY = Pattern.compile("order\\s+by\\s+((?!.*\\bfrom\\b).+)$", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);

	public static String applyLimit(String sqlEngine, String sql, int limit, final int offset) {
		assert(sql != null);
		assert(sql.strip().endsWith(";") == false);

		Matcher orderMatch = __ORDERBY.matcher(sql);
		if (orderMatch.find()) {
			// We have an order by
			String orderBy = orderMatch.group(1);
			log.trace("Final SQL order by: {}", orderBy);
		} else {
			// No final order by, order by first column
			sql += "\nORDER BY 1";
		}

		if (offset < 1) {
			if (MSSQLENGINE.equalsIgnoreCase(sqlEngine)) {
				sql += "\nOFFSET 0 ROWS FETCH NEXT " + limit + " ROWS ONLY";
			} else {
				sql += "\nLIMIT " + limit;
			}
		} else {
			if (MSSQLENGINE.equalsIgnoreCase(sqlEngine)) {
				sql += "\nOFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
			} else {
				sql += "\nLIMIT " + limit + " OFFSET " + offset;
			}
		}
		return sql;
	}

	public static Map<String, String> parseParameterList(String parameterList) {
		final Map<String, String> m = new HashMap<>();

		parameterList = StringUtils.defaultIfBlank(parameterList, "").replaceAll("[\\s+;]+$", "");
		log.trace("Parsing: " + parameterList);

		final Pattern p = Pattern.compile("(^|;)\\s*([^=;]+)=?");
		final Matcher matcher = p.matcher(parameterList);
		log.trace(parameterList);

		String keyword = null;
		int prevPos = 0;
		while (matcher.find()) {
			final String match = matcher.group(2);
			log.trace("+++ " + match + " @" + matcher.start() + ":" + matcher.end());

			if (keyword != null) {
				String value = parameterList.substring(prevPos, matcher.start());
				log.trace("V = " + value);
				if (value.startsWith("\"") && value.endsWith("\"") || value.startsWith("'") && value.endsWith("'")) {
					value = value.substring(1, value.length() - 2);
				}
				value = StringUtils.trimToNull(value);
				m.put(keyword, value);
			}

			keyword = matcher.group(2);
			log.trace("Kw = " + keyword);

			prevPos = matcher.end();
		}

		if (keyword != null) {
			String value = parameterList.substring(prevPos);
			log.trace("V = " + value);
			if (value.startsWith("\"") && value.endsWith("\"") || value.startsWith("'") && value.endsWith("'")) {
				value = value.substring(1, value.length() - 2);
			}
			value = StringUtils.trimToNull(value);
			m.put(keyword, value);
		}

		return m;
	}

	@Override
	// Not @Transactional!
	@PreAuthorize("isAuthenticated()")
	public List<Element> saveDataOneByOne(List<Element> before, List<Element> after, boolean includeStackTraces) {
		return save(before, after, false, includeStackTraces);
	}

	@Override
	@Transactional
	@PreAuthorize("isAuthenticated()")
	public List<Element> saveData(final List<Element> before, final List<Element> after, boolean includeStackTraces) {
		return save(before, after, true, includeStackTraces);
	}

	private static class SaveContext<T> {
		final private Class<T> targetEntity;

		/** Keep track of exception IDs */
		final private AtomicInteger errorCounter = new AtomicInteger();
		/** Generate exception stacktraces */
		public boolean includeStackTraces = false;
		/** Save new entity */
		final public Function<T, T> newEntitySaver;
		/** Update existing entity */
		final public Function<T, T> existingEntityUpdater;
		/** Remove entity */
		final public Consumer<T> entityRemover;
		/** JpaEntityInfo for this type */
		final public JpaEntityInfo<T> jpaEntityInfo;
		/** Entity type constructor */
		final private Constructor<T> ctor;
		/** Are we using an entity manager? If false, then entity will be detached before modifications are applied. */
		public boolean mustDetachEntity;

		public SaveContext(JpaEntityInfo<T> jpaEntityInfo, EntityManager entityManager, Function<Class<T>, CRUDService<T>> serviceGetter, Function<Class<T>, JpaRepository<T, ?>> repositoryGetter) {
			this.jpaEntityInfo = jpaEntityInfo;
			this.targetEntity = jpaEntityInfo.target;
			try {
				this.ctor = this.targetEntity.getConstructor();
			} catch (NoSuchMethodException | SecurityException e) {
				throw new RuntimeException("Cannot find a constructor for " + this.targetEntity.getName(), e);
			}

			// Create handlers
			var targetService = serviceGetter.apply(this.targetEntity);
			if (targetService != null) {
				this.newEntitySaver = targetService::create;
				this.existingEntityUpdater = targetService::update;
				this.entityRemover = targetService::remove;
				this.mustDetachEntity = true;
			} else {
				this.mustDetachEntity = false; // Don't detach entity from entityManager
				log.info("CRUDService not available for {}.", this.targetEntity.getClass());
				var targetRepository = repositoryGetter.apply(this.targetEntity);
				if (targetRepository != null) {
					this.newEntitySaver = targetRepository::save;
					this.existingEntityUpdater = targetRepository::save;
					this.entityRemover = targetRepository::delete;
				} else {
					log.info("JPA repository not available for {}. Using EntityManager.", this.targetEntity.getClass());
					this.newEntitySaver = (T entity) -> { entityManager.persist(entity); return entity; };
					this.existingEntityUpdater = (T entity) -> { entityManager.persist(entity); return entity; };
					this.entityRemover = entityManager::remove;
				}
			}
		}

		public T createNewEntity() throws Throwable {
			return ctor.newInstance();
		}
	}

	/**
	 * {@code saveData} implementation with error handling.
	 *
	 * @param before
	 * @param after
	 * @param throwPersistenceErrors if {@code false} then exceptions are ignored and execution proceeds
	 * @param includeStackTraces Serialize exception stacktrace and include them in the response
	 * @return
	 */
	private List<Element> save(final List<Element> before, final List<Element> after, final boolean throwPersistenceErrors, boolean includeStackTraces) {
		// Since CT sends a Datatable, all changes refer to a SysDataview.
		String dataviewName;
		if (before != null && before.size() > 0) {
			dataviewName = before.get(0).getName();
		} else if (after != null && after.size() > 0) {
			dataviewName = after.get(0).getName();
		} else {
			return List.of(); // NOOP
		}

		var stopWatch = StopWatch.createStarted();

		String jpaEntity = null;

		List<SysTableField> sysTableFields;
		String tableName;

		if (dataviewName.startsWith(VirtualDataviewServiceImpl.GGCE_PREFIX)) {
			String entityName = StringUtils.removeStartIgnoreCase(dataviewName, VirtualDataviewServiceImpl.GGCE_PREFIX);
			Optional<Class<?>> optionalEntityClass = entityClassSet.stream().filter(entity -> StringUtils.equalsIgnoreCase(entity.getSimpleName(), entityName)).findFirst();
			if (optionalEntityClass.isEmpty()) {
				throw new InvalidApiUsageException("Dataview entity not found");
			}
			Class<?> entityClass = optionalEntityClass.get();
			var sysTable = sysTableComponent.getSysTable(entityClass);
			sysTableFields = sysTable.getFields();
			jpaEntity = entityClass.getSimpleName();
			tableName = sysTable.getTableName();

		} else {
			sysTableFields = new ArrayList<>();
			tableName = TransactionHelper.executeInCurrentTransaction(true, () -> { // Loading field.table.tableName requires a session
				final SysDataview dataview = dataviewRepository.findByDataviewName(dataviewName);

				// Map SysDataviewField to its SysTableField
				var dataviewSysTableFields = dataview.getFields().stream().map(SysDataviewField::getSysTableField).filter(Objects::nonNull).collect(Collectors.toList());

				// The first primary key or the first non-read-only field determines the target table
				var firstUsefulField = dataviewSysTableFields.stream()
					.filter(field -> field != null && Objects.equals(field.getIsPrimaryKey(), "Y") || !Objects.equals(field.getIsReadonly(), "Y"))
					.findFirst().orElse(dataviewSysTableFields.get(0));

				// Target table name
				var dataviewTableName = firstUsefulField.getTable().getTableName();

				// Filter for SysTableFields that match target table name
				dataviewSysTableFields.forEach(field -> {
					if (StringUtils.equalsIgnoreCase(dataviewTableName, field.getTable().getTableName())) { // Initializes proxy
						sysTableFields.add(field);
					} else {
						log.debug("Don't know how to update accross multiple JPA entities. Ignoring {}.{}", field.getTable().getTableName(), field.getFieldName());
					}
				});
				return dataviewTableName;
			});
			
			jpaEntity = tableName; // This is our target entity
		}

		var targetEntity = tablesToEntities.get(jpaEntity);
		if (targetEntity == null) {
			log.warn("Target entity not found for {}", jpaEntity);
			throw new InvalidApiUsageException("Dataview entity " + jpaEntity + " not found");
		}
		log.info("Updating {}", targetEntity.target);

		var context = new SaveContext<>(targetEntity, entityManager, this::getCrudService, this::getRepository);
		context.includeStackTraces = includeStackTraces;

		stopWatch.split();
		log.debug("saveData ready for {} at {}ms", jpaEntity, stopWatch.getSplitTime());
		stopWatch.unsplit();

		// Find declared existing rows
		final Map<Object, Element> befores = new HashMap<>();
		if (before != null) before.forEach(dvRow -> {
			final Element dvPk = dvRow.getChild(targetEntity.dbPrimaryKey);
			if (dvPk == null) {
				log.info("Missing primary key field {} in {}", targetEntity.dbPrimaryKey, dvRow.getName());
				addOperationMetaData(SAVEDACTION_UPDATE, dvRow, "?", tableName, "Missing primary key field " + targetEntity.dbPrimaryKey); // Add CT extra columns
				return;
			}
			final String pkValue = StringUtils.trimToNull(dvPk.getText());
			if (pkValue == null) {
				log.info("A {} provided with missing primary key value", dvRow.getName());
				addOperationMetaData(SAVEDACTION_UPDATE, dvRow, "", tableName, "Empty primary key field " + targetEntity.dbPrimaryKey); // Add CT extra columns
				return;
			}
			if (befores.containsKey(pkValue)) {
				log.info("Primary key {}#{} is already declared", dvRow.getName(), pkValue);
				addOperationMetaData(SAVEDACTION_UPDATE, dvRow, pkValue, tableName, "Duplicate row with " + targetEntity.dbPrimaryKey + " " + pkValue); // Add CT extra columns
				return;
			}

			addOperationMetaData(SAVEDACTION_DELETE, dvRow, pkValue, tableName, "Not saved"); // Add CT extra columns: assume delete operation (i.e. element is not in after)
			befores.put(convert(pkValue, targetEntity.primaryKey.getType()), dvRow);
		});

		stopWatch.split();
		log.debug("saveData {} befores for {} analyzed at {}ms", befores.size(), jpaEntity, stopWatch.getSplitTime());
		stopWatch.unsplit();

		// Find inserts and updates
		final Map<Object, Element> inserts = new HashMap<>();
		final Map<Object, Element> updates = new HashMap<>();

		if (after != null) after.forEach(dvRow -> {
			final Element dvPk = dvRow.getChild(targetEntity.dbPrimaryKey);
			final Attribute dvRowChanges = dvRow.getAttribute("hasChanges", GGXml.NsDIFFGR);
			if (dvPk == null) {
				log.info("Missing primary key field {} in {}", targetEntity.dbPrimaryKey, dvRow.getName());
				addOperationMetaData(SAVEDACTION_UPDATE, dvRow, "?", tableName, "Missing primary key field " + targetEntity.dbPrimaryKey); // Add CT extra columns
				return;
			}
			final String pkValue = StringUtils.trimToNull(dvPk.getText());
			if (dvRowChanges == null) {
				log.info("Row does not declare 'diffgr:hasChanges': {}", dvRow);
				addOperationMetaData(SAVEDACTION_UPDATE, dvRow, pkValue, tableName, "No operation declared"); // Add CT extra columns
				return;
			}
			if (pkValue == null) {
				log.info("A {} provided with missing primary key value", dvRow.getName());
				addOperationMetaData(SAVEDACTION_UPDATE, dvRow, "", tableName, "Empty primary key field " + targetEntity.dbPrimaryKey); // Add CT extra columns
				return;
			}

			if (Objects.equals(dvRowChanges.getValue(), "inserted")) {
				addOperationMetaData(SAVEDACTION_INSERT, dvRow, pkValue, tableName, "Not saved"); // Add CT extra columns: insert operation
				inserts.put(convert(pkValue, targetEntity.primaryKey.getType()), dvRow);

			} else if (Objects.equals(dvRowChanges.getValue(), "modified")) {
				addOperationMetaData(SAVEDACTION_UPDATE, dvRow, pkValue, tableName, "Not saved"); // Add CT extra columns: update operation
				updates.put(convert(pkValue, targetEntity.primaryKey.getType()), dvRow);
			} else {
				log.warn("Unknown {} value for 'diffgr:hasChanges'", dvRowChanges.getValue());
				addOperationMetaData(SAVEDACTION_UPDATE, dvRow, pkValue, tableName, "Unsupported operation " + dvRowChanges.getValue()); // Add CT extra columns
			}
		});

		stopWatch.split();
		log.debug("saveData {} inserts and {} updates for {} analyzed at {}ms", inserts.size(), updates.size(), jpaEntity, stopWatch.getSplitTime());
		stopWatch.unsplit();

		// Find deletes
		final HashSet<Object> deletes = new HashSet<>(befores.keySet());
		deletes.removeIf(updates::containsKey);

		stopWatch.split();
		log.debug("saveData {} deletes for {} analyzed at {}ms", deletes.size(), jpaEntity, stopWatch.getSplitTime());
		stopWatch.unsplit();

		log.info("Entities to remove: {}", deletes);
		log.info("Entities to update: {}", updates.keySet());
		log.info("Entities to insert: {}", inserts.size());

		stopWatch.split();
		log.info("saveData {} inserts {} updates {} deletes for {} prepared at {}ms", inserts.size(), updates.size(), deletes.size(), jpaEntity, stopWatch.getSplitTime());
		stopWatch.unsplit();

		// The results
		final List<Element> insertsAndUpdates = new ArrayList<>();

		// Delete
		for (final Object deletePk : deletes) {
			var deleteXml = befores.get(deletePk);
			doDelete(throwPersistenceErrors, deleteXml, deletePk, insertsAndUpdates, context);
			insertsAndUpdates.add(deleteXml);
		}

		stopWatch.split();
		log.info("saveData {} deletes for {} processed at {}ms", deletes.size(), jpaEntity, stopWatch.getSplitTime());
		stopWatch.unsplit();

		// Lower-case field name to SysTableField mappings. This is only to speed up lookups in applyXml
		final Map<String, SysTableField> sysTableFieldsByXmlNameLC = new HashMap<>();
		sysTableFields.forEach(stF -> {
			if (stF == null || stF.getFieldName() == null) {
				// It is possible that Dataviews define columns that are not backed by a field!
				return;
			}
			var existingMapping = sysTableFieldsByXmlNameLC.putIfAbsent(stF.getFieldName().toLowerCase(), stF);
			if (existingMapping != null) {
				log.info("Duplicate field {} defined for {}", stF.getFieldName(), tableName);
			}
		});

		final AclSid currentUser = (AclSid) SecurityContextHolder.getContext().getAuthentication().getPrincipal();

		// Updates
		for (final var update : updates.entrySet()) {
			final Element updateXml = update.getValue();
			doUpdate(throwPersistenceErrors, update.getKey(), updateXml, xmlDiff(befores.get(update.getKey()), updateXml), tableName, sysTableFieldsByXmlNameLC, sysTableFields, currentUser, insertsAndUpdates, context);
			insertsAndUpdates.add(updateXml);
		}
		stopWatch.split();
		log.info("saveData {} updates for {} processed at {}ms", updates.size(), jpaEntity, stopWatch.getSplitTime());
		stopWatch.unsplit();

		// Insert
		for (Map.Entry<Object, Element> insertEntry : inserts.entrySet()) {
			final Element insertXml = insertEntry.getValue();
			if (log.isTraceEnabled()) {
				log.trace("Inserting {}", xmlOutputter.outputString(insertXml));
			}

			try {
				doInsert(throwPersistenceErrors, insertXml, tableName, sysTableFieldsByXmlNameLC, sysTableFields, currentUser, insertsAndUpdates, context);
				insertsAndUpdates.add(insertXml);
			} catch (Throwable e) {
				if (throwPersistenceErrors) throw new RuntimeException(e.getMessage(), e);
			}
		}
		stopWatch.split();
		log.info("saveData {} inserts for {} processed at {}ms", inserts.size(), jpaEntity, stopWatch.getSplitTime());
		stopWatch.unsplit();

		// Original implementation returns the incoming data, but adds
		// <ExceptionIndex>-1</ExceptionIndex>
		// <NewPrimaryKeyID>6</NewPrimaryKeyID>
		// <OriginalPrimaryKeyID>-4</OriginalPrimaryKeyID>
		// <SavedAction>Insert</SavedAction>
		// <SavedStatus>Success</SavedStatus>
		// <OriginalRowIndex>0</OriginalRowIndex>
		// <TableName>accession</TableName>
		// <AliasName>a</AliasName>

		return insertsAndUpdates;
	}

	private <T> Object doInsert(boolean throwPersistenceErrors, Element insertXml, String tableName, Map<String, SysTableField> sysTableFieldsByXmlNameLC, List<SysTableField> sysTableFields, AclSid currentUser, List<Element> insertsAndUpdates, SaveContext<T> context) throws Throwable {
		try {
			var newEntity = context.createNewEntity();

			return TransactionHelper.executeInCurrentTransaction(false, () -> {
				applyXml(newEntity, insertXml, tableName, sysTableFieldsByXmlNameLC);
				// Assume that primary keys are auto-generated
				context.jpaEntityInfo.primaryKey.set(newEntity, null);
				addCreatorInfo(newEntity, currentUser);
				applyDefaults(newEntity, tableName, sysTableFields);

				var result = context.newEntitySaver.apply(newEntity);
				if (result == null) {
					log.warn("context.newEntitySaver returned null for {}", newEntity.getClass().getName());
					throw new InvalidApiUsageException("Adding a record is not supported");
				}
				var generatedPk = context.jpaEntityInfo.primaryKey.get(result);
				log.trace("Persisted new {} with id={}", context.targetEntity, generatedPk);

				describeInsert(insertXml, generatedPk);
				return generatedPk;
			});

		} catch (Throwable e) {
			if (log.isDebugEnabled() || e instanceof RuntimeException) {
				log.warn("Insert failed with {}", e.getMessage(), e);
			}
			var rowException = new Datatable.Exception(context.errorCounter.incrementAndGet(), e, context.includeStackTraces);
			insertsAndUpdates.add(Datatable.exceptionToDiffgram(rowException));
			describeInsertError(insertXml, rowException); // Log error in row

			if (throwPersistenceErrors) throw e; // Stop
			return null;
		}
	}

	private <T> void doUpdate(boolean throwPersistenceErrors, Object pkValue, Element updateXml, Element xmlDiff, String tableName, Map<String, SysTableField> sysTableFieldsByXmlNameLC, List<SysTableField> sysTableFields, AclSid currentUser, List<Element> insertsAndUpdates, SaveContext<T> context) {
		try {
			TransactionHelper.executeInCurrentTransaction(false, () -> {
				var existingEntity = entityManager.find(context.targetEntity, pkValue);
				if (existingEntity == null) {
					log.info("EntityManager did not find {} with id {}", context.targetEntity, pkValue);
					throw new NotFoundElement("No such record");
				}

				Hibernate.initialize(existingEntity);
				if (context.mustDetachEntity) entityManager.detach(existingEntity);

				if (log.isDebugEnabled()) {
					log.debug("Updating {}", xmlOutputter.outputString(updateXml));
				}

				log.trace("Updating entity", existingEntity);
				applyXml(existingEntity, xmlDiff, tableName, sysTableFieldsByXmlNameLC);
				log.trace("Applied XML", existingEntity);
				applyDefaults(existingEntity, tableName, sysTableFields);
				log.trace("Applied defaults", existingEntity);
	
				context.existingEntityUpdater.apply(existingEntity);
				describeUpdate(updateXml, SAVEDSTATUS_SUCCESS, null);
				return updateXml;
			});

		} catch (Throwable e) {
			if (log.isDebugEnabled() || e instanceof RuntimeException) {
				log.warn("Update failed with {}", e.getMessage(), e);
			}
			var rowException = new Datatable.Exception(context.errorCounter.incrementAndGet(), e, context.includeStackTraces);
			insertsAndUpdates.add(Datatable.exceptionToDiffgram(rowException));
			describeUpdate(updateXml, SAVEDSTATUS_FAILURE, rowException); // Log error in row

			if (throwPersistenceErrors) throw e; // Stop
		}
	}

	private <T> boolean doDelete(boolean throwPersistenceErrors, Element deleteXml, Object deletePk, List<Element> insertsAndUpdates, SaveContext<T> context) {
		log.debug("Deleting {}", deletePk);
		try {
			return TransactionHelper.executeInCurrentTransaction(false, () -> {
				var existingEntity = entityManager.find(context.targetEntity, deletePk);
				if (existingEntity == null) {
					log.warn("EntityManager did not find {} with id {}", context.targetEntity, deletePk);
					throw new NotFoundElement("No such record");
				}
				context.entityRemover.accept(existingEntity);
				describeDelete(deleteXml, SAVEDSTATUS_SUCCESS, null); // Flag row as deleted for CT
				return true;
			});

		} catch (Throwable e) {
			if (log.isDebugEnabled() || e instanceof RuntimeException) {
				log.warn("Delete failed with {}", e.getMessage(), e);
			}
			var rowException = new Datatable.Exception(context.errorCounter.incrementAndGet(), e, context.includeStackTraces);
			insertsAndUpdates.add(Datatable.exceptionToDiffgram(rowException));

			describeDelete(deleteXml, SAVEDSTATUS_FAILURE, rowException); // Log error in row
			if (throwPersistenceErrors) throw e; // Stop
			return false;
		}
	}

	private void applyDefaults(final Object targetEntity, final String tableName, List<SysTableField> sysTableFields) {
		final Class<? extends Object> clazz = targetEntity.getClass();
		for (var sysField : sysTableFields) {
			if (sysField == null) continue; // It is possible that a field definition is missing in the DB

			if (sysField.getDefaultValue() != null && !NULLVALUE.equals(sysField.getDefaultValue())) { // Only if SysTableField has default value declared

				if (! StringUtils.equalsIgnoreCase(tableName, sysField.getTable().getTableName())) continue; // Ignore things not in this table

				var targetField = findField(clazz, sysField);
				if (targetField == null) {
					log.debug("Not setting default for missing target field {}.{} in {}", sysField.getTable().getTableName(), sysField.getFieldName(), clazz);
					continue; // No target field
				}
				try {
					ReflectionUtils.makeAccessible(targetField); //  grin-global/grin-global-server#449
					final Object value = targetField.get(targetEntity);
					if (value == null) {
						log.info("Default for {}.{} = {}", clazz, targetField.getName(), sysField.getDefaultValue());
						targetField.set(targetEntity, convert(sysField.getDefaultValue(), targetField.getType()));
					}
				} catch (IllegalArgumentException | IllegalAccessException e) {
					log.warn("Can't set default value {} for {}.{}: {}", sysField.getDefaultValue(), clazz, targetField.getName(), e.getMessage()); // Log reason
				}
			}
		}
	}

	/**
	 * Add metadata columns for CT
	 * 
	 * @param savedActionType one of "Update", "Insert" or "Delete"
	 * @param dvRow the row
	 * @param oldPk row PK value
	 * @param sysTableName SysTable name
	 * @param exceptionMessage Exception message
	 */
	private void addOperationMetaData(final String savedActionType, final Element dvRow, final Object oldPk, final String sysTableName, final String exceptionMessage) {
		dvRow.addContent(new Element(META_EXCEPTION_INDEX).setText("-1"));
		dvRow.addContent(new Element(META_EXCEPTION_MESSAGE).setText(exceptionMessage));
		dvRow.addContent(new Element(META_NEW_PRIMARY_KEY_ID).setText(oldPk.toString()));
		dvRow.addContent(new Element(META_ORIGINAL_PRIMARY_KEY_ID).setText(oldPk.toString()));
		dvRow.addContent(new Element(META_SAVED_ACTION).setText(savedActionType));
		dvRow.addContent(new Element(META_SAVED_STATUS).setText(SAVEDSTATUS_FAILURE)); // Anything but "Success" means error
		dvRow.addContent(new Element(META_ORIGINAL_ROW_INDEX).setText(dvRow.getAttributeValue("rowOrder", GGXml.NsMsDATA, "-1")));
		dvRow.addContent(new Element(META_TABLE_NAME).setText(sysTableName));
//		dvRow.addContent(new Element("AliasName").setText(""));
	}

	/**
	 * Update metadata as successful insert
	 * @param dvRow the row
	 * @param newPk New primary key value generated by the database
	 */
	private void describeInsert(final Element dvRow, final Object newPk) {
		dvRow.getChild(META_SAVED_ACTION).setText(SAVEDACTION_INSERT);
		dvRow.getChild(META_SAVED_STATUS).setText(SAVEDSTATUS_SUCCESS);
		dvRow.getChild(META_EXCEPTION_INDEX).setText("-1");
		dvRow.getChild(META_EXCEPTION_MESSAGE).setText("");
		dvRow.getChild(META_NEW_PRIMARY_KEY_ID).setText(newPk.toString());
	}

	/**
	 * Update metadata as failed insert
	 * @param dvRow the row
	 * @param rowException Exception message
	 */
	private void describeInsertError(final Element dvRow, final org.gringlobal.soap.Datatable.Exception rowException) {
		dvRow.getChild(META_SAVED_ACTION).setText(SAVEDACTION_INSERT);
		dvRow.getChild(META_SAVED_STATUS).setText(SAVEDSTATUS_FAILURE);
		dvRow.getChild(META_EXCEPTION_INDEX).setText(Integer.toString(rowException.getIndex()));
		dvRow.getChild(META_EXCEPTION_MESSAGE).setText(rowException.getMessage());
	}

	/**
	 * Update metadata as update action with saved status and exception message.
	 * @param dvRow the row
	 * @param savedStatus "Success" or "Failure"
	 * @param rowException Exception message. Can be empty string.
	 */
	private void describeUpdate(final Element dvRow, final String savedStatus, final org.gringlobal.soap.Datatable.Exception rowException) {
		dvRow.getChild(META_SAVED_ACTION).setText(SAVEDACTION_UPDATE);
		dvRow.getChild(META_SAVED_STATUS).setText(savedStatus);
		if (rowException == null) {
			dvRow.getChild(META_EXCEPTION_INDEX).setText("-1");
			dvRow.getChild(META_EXCEPTION_MESSAGE).setText("");	
		} else {
			dvRow.getChild(META_EXCEPTION_INDEX).setText(Integer.toString(rowException.getIndex()));
			dvRow.getChild(META_EXCEPTION_MESSAGE).setText(rowException.getMessage());
		}
	}

	/**
	 * Update metadata as successful delete action
	 * @param dvRow the row
	 * @param savedStatus "Success" or "Failure"
	 * @param rowException Exception message. Can be empty string.
	 */
	private void describeDelete(final Element dvRow, final String savedStatus, final org.gringlobal.soap.Datatable.Exception rowException) {
		dvRow.getChild(META_SAVED_ACTION).setText(SAVEDACTION_DELETE);
		dvRow.getChild(META_SAVED_STATUS).setText(savedStatus);
		if (rowException == null) {
			dvRow.getChild(META_EXCEPTION_INDEX).setText("-1");
			dvRow.getChild(META_EXCEPTION_MESSAGE).setText("");	
		} else {
			dvRow.getChild(META_EXCEPTION_INDEX).setText(Integer.toString(rowException.getIndex()));
			dvRow.getChild(META_EXCEPTION_MESSAGE).setText(rowException.getMessage());
		}
	}

	/**
	 * Create a copy of updated XML row and remove fields that have the same value as the original.
	 * 
	 * Note: There are no side effects on original or updated XML.
	 * 
	 * @param original
	 * @param updated
	 * @return a copy of updated row with only modified columns
	 */
	private Element xmlDiff(final Element original, final Element updated) {
		final Element diff = updated.clone();
		if (log.isDebugEnabled()) {
			log.debug("xmlDiff1: {}", xmlOutputter.outputString(diff));
		}
		for (final Element field : original.getChildren()) {
			final Element update = updated.getChild(field.getName(), field.getNamespace());
			if (update != null) {
				if (update.getValue().equals(field.getValue())) {
					if (log.isDebugEnabled()) {
						log.debug("No change in {} = {}", field.getName(), field.getValue());
					}
					diff.removeChild(field.getName(), field.getNamespace());
				}
			} else {
				var nullField = field.clone();
				nullField.removeContent();
//				nullField.setAttribute("toNull", "Y");
				log.info("To NULL {} was {}", field.getName(), field.getValue());
				diff.addContent(nullField);
			}
		}
		if (log.isDebugEnabled()) {
			log.debug("xmlDiff2: {}", xmlOutputter.outputString(diff));
		}
		return diff;
	}

	private void addCreatorInfo(final Object newEntity, final AclSid sysUser) {
		if (!(newEntity instanceof CooperatorOwnedModel)) {
			log.warn("Class {} not instance of CooperatorOwnedModel", newEntity.getClass());
			return;
		}
		final CooperatorOwnedModel com = (CooperatorOwnedModel) newEntity;

		var now = Instant.now();
		if (sysUser != null) {
			log.trace("Using user {} from security context as owner", sysUser);
			com.setOwnedBy(sysUser);
			com.setOwnedDate(now);
		}
	}

	private void applyXml(
		final Object existingEntity,
		final Element updateXml,
		final String tableName,
		final Map<String, SysTableField> sysTableFieldsByXmlNameLC
	) {

		Class<? extends Object> targetClazz = existingEntity.getClass();

		for (final Element xmlField : updateXml.getChildren()) {
			String fieldName = xmlField.getName();
			log.trace("Applying {} = {}", fieldName, xmlField.getValue());

			final var sysTableField = sysTableFieldsByXmlNameLC.get(fieldName.toLowerCase());

			if (sysTableField != null) {
				if (Objects.equals("Y", sysTableField.getIsReadonly())) {
					log.trace("Field {} is readonly. Skipping", fieldName);
					continue;
				}
				if (! StringUtils.equalsIgnoreCase(tableName, sysTableField.getTable().getTableName())) { // This should already be filtered out earlier
					log.trace("Skipping {}.{} not in {}", sysTableField.getTable().getTableName(), sysTableField.getFieldName(), tableName);
					continue;
				}
				log.trace("Applying {}.{} as {}", targetClazz, fieldName, sysTableField.getFieldName());
				fieldName = sysTableField.getFieldName();
			} else {
				log.trace("No SysTableField available for {}.{}. This will be ignored.", targetClazz.getSimpleName(), fieldName);
				continue;
			}

			final Field targetField = findField(targetClazz, sysTableField);

			if (targetField == null) {
				log.trace("Skipping {} = {}, no target field", fieldName, xmlField.getValue());
				continue;
			}

			// Referenced entity
			Object targetValue;
			if (SysTableComponent.isForeignKey(targetField)) {
				if (xmlField.getContentSize() == 0) { // xmlField had no content, setting the value to null
					targetValue = null;

				} else {
					// Only lookup JPAEntityInfo if value is provided
					JpaEntityInfo<?> referencedEntity = findJpaEntityInfoForField(targetClazz, targetField);
					if (referencedEntity == null) {
						throw new RuntimeException("No entity available for foreign key " + fieldName + " in " + targetClazz.getName());
					}
					final Object targetPrimaryKey = convert(xmlField.getValue(), referencedEntity.primaryKey.getType());

					// targetValue = entityManager.getReference(referencedEntity.target, targetPrimaryKey); // Use foreign key reference, don't fetch from DB
					targetValue = entityManager.find(referencedEntity.target, targetPrimaryKey); // Load the record from the database
					if (targetValue == null) { // We must throw an exception if the requested target entity does not exist
						throw new InvalidApiUsageException("Referenced lookup value " + xmlField.getValue() + " not found for field " + targetField.getName() + "");
					}
				}
			} else {
				if (xmlField.getContentSize() == 0) {
					targetValue = null;
				} else {
					targetValue = convert(xmlField.getValue(), targetField.getType());
				}
			}
			log.trace("Setting {}.{} = {}", targetClazz.getName(), targetField.getName(), targetValue);
			try {
				ReflectionUtils.makeAccessible(targetField);
				targetField.set(existingEntity, targetValue);
			} catch (IllegalArgumentException | IllegalAccessException e) {
				log.info("Failed to set {}.{} to {}", targetClazz.getName(), targetField.getName(), targetValue, e);
			}
		}
	}

	/**
	 * Find {@link JpaEntityInfo} for {@code targetClazz.field}.
	 * 
	 * @param targetClazz
	 * @param field
	 * @return {@code JpaEntityInfo} for specified type or {@code null} if entity for type is not available.
	 */
	private JpaEntityInfo<?> findJpaEntityInfoForField(Class<?> targetClazz, Field field) {
		return jpaEntityInfoByField
			// target
			.computeIfAbsent(targetClazz.getName(), className -> {
				log.info("Creating field map for {}", className);
				return new HashMap<>();
			})
			// Find Field in targetClazz
			.computeIfAbsent(field, targetField -> {
				var referencedEntity = tablesToEntities.values().stream().filter(jpaEntity -> jpaEntity.target.equals(targetField.getType())).findFirst().orElse(null);
				if (referencedEntity == null) {
					var resolvableFieldType = ResolvableType.forField(targetField, targetClazz);
					log.info("Referenced entity for {} {} {} not declared. {} {}", targetClazz.getSimpleName(), targetField.getType(), targetField.getName(), resolvableFieldType.getRawClass(), resolvableFieldType.resolve());
					var resolvedGeneric = resolvableFieldType.resolve();
					referencedEntity = tablesToEntities.values().stream().filter(jpaEntity -> jpaEntity.target.equals(resolvedGeneric)).findFirst().orElse(null);
				}
				return Optional.ofNullable(referencedEntity);
			})
			// Or null
			.orElse(null);
	}

	/**
	 * Find field for {@sysTableField} in {@code targetClazz}.
	 * 
	 * @param targetClazz target entity class
	 * @param sysTableField should be in the SysTable corresponding with target class
	 * @return Field or {@code null} if sysTableField is null or not found.
	 */
	static Field findField(final Class<?> targetClazz, final SysTableField sysTableField) {
		if (sysTableField == null) return null; // It is possible that Dataviews have columns not mapped to fields

		return entityFieldMap
			// Find target class map
			.computeIfAbsent(targetClazz.getName(), className -> {
				log.info("Creating field map for {}", className);
				return new HashMap<>();
			})
			// Compute field
			.computeIfAbsent(sysTableField.getFieldName(), fieldName -> {
				log.info("Computing field {} in {}", fieldName, targetClazz);
				return Optional.ofNullable(
					sysTableField.getPropertyName() != null ?
						// We have the property name
						ReflectionUtils.findField(targetClazz, sysTableField.getPropertyName())
						: // Or we look it up by XML tags
						findXmlField(targetClazz, sysTableField.getFieldName())
				);
			})
			// Return null if not present
			.orElse(null);
	}

	static Field findXmlField(final Class<?> clazz, final String xmlName) {
		var lookupName = xmlName;

		// Check for @AssociationOverride and override name of field to lookup
		var associationOverride = clazz.getAnnotation(AssociationOverride.class);
		if (associationOverride != null && associationOverride.joinColumns() != null) {
			for (var join : associationOverride.joinColumns()) {
				var mappedName = join.name();
				if (mappedName.equals(xmlName)) {
					log.debug("Association override using {}", mappedName);
					lookupName = associationOverride.name();
					break;
				}
			}
		}

		for (final Field field : getAllFields(clazz)) {
			if (field.getName().equals(lookupName)) { // this includes name of @AssociationOverride
				return field;
			}

			final javax.persistence.Column annoColumn = field.getAnnotation(javax.persistence.Column.class);
			if (annoColumn != null) {
				if (annoColumn.name().equals(lookupName)) {
					return field;
				}
			}
			final JoinColumn annoJoin = field.getAnnotation(javax.persistence.JoinColumn.class);
			if (annoJoin != null) {
				if (annoJoin.name().equals(lookupName)) {
					return field;
				}
			}
		}

		log.warn("Cannot find target XML field {}.{}", clazz.getName(), xmlName);
		return null;
	}

	private <T> T convert(final String pkValue, final Class<T> type) {
		return conversionService.convert(pkValue, type);
	}

	private Map<String, JpaEntityInfo<?>> findJpaEntityClasses() {
		final Map<String, JpaEntityInfo<?>> tablesToEntities = new HashMap<>();

		final ClassPathScanningCandidateComponentProvider provider = createComponentScanner();
		for (final String scanPackage : jpaEntityPackageNames) {
			for (final BeanDefinition beanDef : provider.findCandidateComponents(scanPackage)) {
				log.trace("Found JPA entity: {}", beanDef.getBeanClassName());
				try {
					var entityClass = Class.forName(beanDef.getBeanClassName());
					final Table annoTable = entityClass.getAnnotation(Table.class);

					String tableName = null;
					JpaEntityInfo<?> entityInfo = null; 
					if (annoTable != null) {
						tableName = annoTable.name();
						entityInfo = new JpaEntityInfo<>(entityClass);
						log.debug("Class {} for table {}", entityClass.getName(), tableName);
						// entityInfo.dbName = annoTable.name();
					} else {
						// Handle JPA inheritance
						var discriminatorValue = entityClass.getAnnotation(DiscriminatorValue.class);
						if (discriminatorValue != null) {
							tableName = WordUtils.uncapitalize(entityClass.getSimpleName()).replaceAll("([A-Z])", "_$1").toLowerCase();
							entityInfo = new JpaEntityInfo<>(entityClass);
							log.debug("Class {} for table {}", entityClass.getName(), tableName);
						}
					}

					if (entityInfo != null) {
						for (final Field field : getAllFields(entityClass)) {
							final Id annoId = field.getAnnotation(Id.class);
							if (annoId != null) {
								final javax.persistence.Column annoColumn = field.getAnnotation(javax.persistence.Column.class);
								entityInfo.dbPrimaryKey = annoColumn.name();
								entityInfo.primaryKey = field;
								// Make it accessible
								ReflectionUtils.makeAccessible(field);
								break;
							}
						}

						if (entityInfo.primaryKey == null) {
							log.warn("Entity {} has no declared primary key", entityClass);
							continue;
						}

						// Register JPA entities we understand
						tablesToEntities.put(tableName, entityInfo);
						tablesToEntities.putIfAbsent(entityClass.getSimpleName(), entityInfo); // Register by entity className
					}

				} catch (final ClassNotFoundException e) {
					log.warn("Cannot access JPA entity class: {}", e.getMessage(), e);
				}
			}
		}

		return tablesToEntities;
	}

	static List<Field> getAllFields(Class<?> clazz) {
		final List<Field> fields = new ArrayList<>();
		while (clazz != Object.class) {
			fields.addAll(Arrays.asList(clazz.getDeclaredFields()));
			clazz = clazz.getSuperclass();
		}
		return fields;
	}

	private ClassPathScanningCandidateComponentProvider createComponentScanner() {
		final ClassPathScanningCandidateComponentProvider provider = new ClassPathScanningCandidateComponentProvider(false);
		provider.addIncludeFilter(new AnnotationTypeFilter(Entity.class));
		return provider;
	}



	// -- TRANSLATIONS

	private DataviewTranslation translatedDataview(SysDataview dataview, SysLang targetLanguage) {
		SysLang defaultLanguage = new SysLang(1l);

		QSysDataviewLang def = new QSysDataviewLang("def");
		QSysDataviewLang lan = new QSysDataviewLang("lan");

		Tuple tuple = jpaQueryFactory.from(QSysDataview.sysDataview)
				.select(QSysDataview.sysDataview, def, lan)
				// Default language
				.leftJoin(QSysDataview.sysDataview.langs, def).on(def.sysLang().id.eq(defaultLanguage.getId()))
				// Target language
				.leftJoin(QSysDataview.sysDataview.langs, lan).on(lan.sysLang().id.eq(targetLanguage.getId()))
				// Filters
				.where(QSysDataview.sysDataview.eq(dataview))
				.fetchFirst();

		DataviewTranslation translation = new DataviewTranslation(tuple);
		return translation.isBlank() ? null : translation;
	}

	private List<DvFieldTranslation> translatedDvFields(SysDataview dataview, SysLang targetLanguage) {
		SysLang defaultLanguage = new SysLang(1l);

		QSysDataviewFieldLang def = new QSysDataviewFieldLang("def");
		QSysDataviewFieldLang lan = new QSysDataviewFieldLang("lan");

		List<Tuple> result = jpaQueryFactory.from(QSysDataviewField.sysDataviewField)
				.select(QSysDataviewField.sysDataviewField, def, lan)
				// Default language
				.leftJoin(QSysDataviewField.sysDataviewField.langs, def).on(def.sysLang().id.eq(defaultLanguage.getId()))
				// Target language
				.leftJoin(QSysDataviewField.sysDataviewField.langs, lan).on(lan.sysLang().id.eq(targetLanguage.getId()))
				// Filters
				.where(QSysDataviewField.sysDataviewField.dataview().eq(dataview))
				.fetch();

		return result.stream().map(DvFieldTranslation::new)
				.filter(translation -> !translation.isBlank())
				.collect(Collectors.toList());
	}


	private List<DtFieldTranslation> translatedDtFields(SysDataview dataview, SysLang targetLanguage) {
		SysLang defaultLanguage = new SysLang(1l);

		QSysTableFieldLang def = new QSysTableFieldLang("def");
		QSysTableFieldLang lan = new QSysTableFieldLang("lan");

		List<SysTableField> dtFields = dataview.getFields().stream().map(SysDataviewField::getSysTableField)
				// not null
				.filter(Objects::nonNull)
				// list
				.collect(Collectors.toList());

		List<Tuple> result = jpaQueryFactory.from(QSysTableField.sysTableField)
				.select(QSysTableField.sysTableField, def, lan)
				// Default language
				.leftJoin(QSysTableField.sysTableField.langs, def).on(def.sysLang().id.eq(defaultLanguage.getId()))
				// Target language
				.leftJoin(QSysTableField.sysTableField.langs, lan).on(lan.sysLang().id.eq(targetLanguage.getId()))
				// Filters
				.where(QSysTableField.sysTableField.in(dtFields))
				.fetch();

		return result.stream().map(DtFieldTranslation::new)
				.filter(translation -> !translation.isBlank())
				.collect(Collectors.toList());
	}

	// -- Field translation classes

	private abstract static class FieldTranslation<E, EL> {
		public E field;
		public String title;
		public String description;

		public FieldTranslation(E field, EL defaultTranslation, EL selectedTranslation) {
			this.field = field;
			// Apply default
			translate(defaultTranslation);
			// Apply selected, can be in the same language as default
			translate(selectedTranslation);
		}

		public abstract void translate(EL fieldTranslation);
		
		/**
		 * @return true if title and description are blank strings
		 */
		public boolean isBlank() {
			return StringUtils.isBlank(this.title) && StringUtils.isBlank(this.description);
		}
	}

	private static class DvFieldTranslation extends FieldTranslation<SysDataviewField, SysDataviewFieldLang> {
		public DvFieldTranslation(Tuple tuple) {
			super(tuple.get(0, SysDataviewField.class), tuple.get(1, SysDataviewFieldLang.class), tuple.get(2, SysDataviewFieldLang.class));
		}

		@Override
		public void translate(SysDataviewFieldLang fieldTranslation) {
			if (fieldTranslation != null) {
				this.title = StringUtils.defaultIfBlank(fieldTranslation.getTitle(), this.title);
				this.description = StringUtils.defaultIfBlank(fieldTranslation.getDescription(), this.description);
			}
		}
	}

	private static class DtFieldTranslation extends FieldTranslation<SysTableField, SysTableFieldLang> {
		public DtFieldTranslation(Tuple tuple) {
			super(tuple.get(0, SysTableField.class), tuple.get(1, SysTableFieldLang.class), tuple.get(2, SysTableFieldLang.class));
		}

		@Override
		public void translate(SysTableFieldLang fieldTranslation) {
			if (fieldTranslation != null) {
				this.title = StringUtils.defaultIfBlank(fieldTranslation.getTitle(), this.title);
				this.description = StringUtils.defaultIfBlank(fieldTranslation.getDescription(), this.description);
			}
		}
	}

	private static class DataviewTranslation extends FieldTranslation<SysDataview, SysDataviewLang> {
		public String scriptDirection;

		public DataviewTranslation(Tuple tuple) {
			super(tuple.get(0, SysDataview.class), tuple.get(1, SysDataviewLang.class), tuple.get(2, SysDataviewLang.class));
		}

		@Override
		public void translate(SysDataviewLang fieldTranslation) {
			if (fieldTranslation != null) {
				this.title = StringUtils.defaultIfBlank(fieldTranslation.getTitle(), this.title);
				this.description = StringUtils.defaultIfBlank(fieldTranslation.getDescription(), this.description);
				// Extra
				this.scriptDirection = StringUtils.defaultIfBlank(fieldTranslation.getSysLang().getScriptDirection(), this.scriptDirection);
			}
		}
	}

}