DataviewServicesImpl.java

/*
 * Copyright 2021 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.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.time.Instant;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import javax.validation.Valid;
import javax.validation.constraints.NotNull;

import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.util.deparser.StatementDeParser;

import org.apache.commons.lang3.StringUtils;
import org.elasticsearch.common.util.set.Sets;
import org.gringlobal.api.exception.InvalidApiUsageException;
import org.gringlobal.api.exception.NotFoundElement;
import org.gringlobal.compatibility.component.SysTableComponent;
import org.gringlobal.model.QSysDataviewField;
import org.gringlobal.model.QSysDataviewFieldLang;
import org.gringlobal.model.QSysDataviewLang;
import org.gringlobal.model.QSysDataviewParam;
import org.gringlobal.model.QSysDataviewSql;
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.persistence.SysDataviewFieldLangRepository;
import org.gringlobal.persistence.SysDataviewFieldRepository;
import org.gringlobal.persistence.SysDataviewLangRepository;
import org.gringlobal.persistence.SysDataviewParamRepository;
import org.gringlobal.persistence.SysDataviewRepository;
import org.gringlobal.persistence.SysDataviewSqlRepository;
import org.gringlobal.persistence.SysTableFieldRepository;
import org.gringlobal.service.DataviewServices;
import org.gringlobal.service.LanguageService;
import org.gringlobal.service.SysDataviewFieldTranslationService;
import org.gringlobal.service.SysDataviewFieldTranslationService.TranslatedSysDataviewField;
import org.gringlobal.service.SysDataviewTranslationService;
import org.gringlobal.service.SysTableFieldTranslationService;
import org.gringlobal.service.SysDataviewTranslationService.SysDataviewDetails;
import org.gringlobal.service.SysTableMappingException;
import org.gringlobal.service.TableServices.SysTableFieldService;
import org.gringlobal.service.filter.SysDataviewFieldFilter;
import org.gringlobal.service.filter.SysDataviewFilter;
import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.JDOMException;
import org.jdom2.input.SAXBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * The Class DataviewServicesImpl.
 */
@Service
@Slf4j
public class DataviewServicesImpl implements DataviewServices {

	/**
	 * The Class SysDataviewServiceImpl.
	 */
	@Service
	@Transactional(readOnly = true)
	public static class SysDataviewServiceImpl extends FilteredTranslatedCRUDServiceImpl<SysDataview, SysDataviewLang, SysDataviewTranslationService.TranslatedSysDataview, SysDataviewFilter, SysDataviewRepository> implements SysDataviewService {

		@Autowired
		private SysDataviewFieldRepository dataviewFieldRepository;

		@Autowired
		private LanguageService languageService;

		@Autowired
		private SysDataviewLangService sysDataviewLangService;

		@Autowired
		private SysDataviewSqlRepository sysDataviewSqlRepository;

		@Autowired
		private SysDataviewLangRepository dataviewLangRepository;

		@Autowired
		private SysDataviewParamRepository dataviewParamRepository;

		@Autowired
		private SysTableFieldRepository sysTableFieldRepository;

		@Autowired
		private SysDataviewFieldLangRepository fieldLangRepository;

		@Autowired
		private SysTableFieldService sysTableFieldService;

		@Autowired
		private SysTableComponent sysTableComponent;

		@Autowired
		private SysDataviewFieldService sysDataviewFieldService;

		@Component
		public static class SysDataviewTranslationSupport extends BaseTranslationSupport<SysDataview, SysDataviewLang, SysDataviewTranslationService.TranslatedSysDataview, SysDataviewFilter, SysDataviewLangRepository> implements SysDataviewTranslationService {

			public SysDataviewTranslationSupport() {
				super();
			}

			@Override
			protected TranslatedSysDataview toTranslated(SysDataview e, String title, String description) {
				return TranslatedSysDataview.from(e, title, description);
			}
		}

		@Override
		public SysDataview load(String dataviewName) {
			return _lazyLoad(repository.findByDataviewName(dataviewName));
		}

		@Override
		@Transactional
		public SysDataview create(SysDataview source) {
			return _lazyLoad(repository.save(source));
		}

		@Override
		@Transactional
		public SysDataview createFast(SysDataview source) {
			return repository.save(source);
		}

		@Override
		@Transactional
		public SysDataview create(SysDataview source, String title, Map<String, String> sqlStatements) {
			var sysDataview = create(source);
			SysDataviewLang lang = new SysDataviewLang();
			lang.setSysLang(new SysLang(1l));
			lang.setTitle(title);
			lang.setEntity(sysDataview);
			sysDataviewLangService.create(lang);

			sqlStatements.forEach((sqlEngine, sqlStatement) -> {
				SysDataviewSql sql = new SysDataviewSql();
				sql.setDatabaseEngineTag(sqlEngine);
				sql.setSqlStatement(sqlStatement);
				sql.setDataview(sysDataview);
				sysDataviewSqlRepository.save(sql);
			});

			return reload(sysDataview);
		}

		@Override
		@Transactional
		public SysDataview update(SysDataview updated, SysDataview target) {
			target.apply(updated);
			return _lazyLoad(repository.save(target));
		}

		@Override
		@Transactional
		public SysDataview updateFast(@NotNull @Valid SysDataview updated, SysDataview target) {
			target.apply(updated);
			return repository.save(target);
		}

		
		@Override
		public SysDataviewDetails generateFromSQL(String sqlSelectStatement) {

			Statement stmt; 
			
			try {
				// Allow Complex Parsing (which allows nested Expressions, but is much slower)
				stmt = CCJSqlParserUtil.parse(sqlSelectStatement, parser -> parser.withSquareBracketQuotation(true).withAllowComplexParsing(true).withTimeOut(10000).withUnsupportedStatements(false));
			} catch (JSQLParserException e) {
				throw new InvalidApiUsageException(e);
			}

			if (! (stmt instanceof PlainSelect)) {
				throw new InvalidApiUsageException("SQL must be a SELECT statement.");
			}

			List<SysDataviewField> dataviewFields = new ArrayList<>();

			PlainSelect select = (PlainSelect) stmt;

			var tableNames = new HashMap<String, String>(); // Map aliases to tables

			var from = select.getFromItem();
			if (from instanceof Table) {
				Table table = (Table) from;
				tableNames.put(table.getName(), table.getName());
				tableNames.put(noSquareBracket(table.getName()), table.getName());
				var alias = table.getAlias();
				if (alias != null) {
					tableNames.put(table.getAlias().getName(), table.getName());
					tableNames.put(noSquareBracket(table.getAlias().getName()), table.getName());
				}
				log.debug("From table: [{}] as [{}]", table.getName(), (alias == null ? "DEFAULT" : alias.getName()));
			} else {
				log.debug("From: {} {}", from.getClass().getName(), from);
			}

			var joins = select.getJoins();
			if (joins != null) {
				joins.forEach(join -> {
					var fromItem = join.getFromItem();
					if (fromItem instanceof Table) {
						Table fromTable = (Table) fromItem;
						tableNames.put(fromTable.getName(), fromTable.getName());
						tableNames.put(noSquareBracket(fromTable.getName()), fromTable.getName());
						var alias = fromTable.getAlias();
						if (alias != null) {
							tableNames.put(fromTable.getAlias().getName(), fromTable.getName());
							tableNames.put(noSquareBracket(fromTable.getAlias().getName()), fromTable.getName());
						}
						log.debug("Join table: [{}] AS [{}]", fromTable.getName(), (alias == null ? "DEFAULT" : alias.getName()));
					} else {
						log.debug("Join {} {} AS [{}]", fromItem.getClass().getName(), fromItem, fromItem.getAlias());
					}
				});
			}

			tableNames.entrySet().forEach(lookup -> {
				log.debug("Lookup[{}] = {}", lookup.getKey(), lookup.getValue());
			});

			select.getSelectItems().forEach(item -> {
				SysDataviewField dataviewField = new SysDataviewField();
				dataviewField.setCreatedDate(Instant.now());
				dataviewField.setSortOrder(select.getSelectItems().indexOf(item));
				dataviewField.setIsVisible("Y");
				dataviewField.setIsReadonly("Y");

				var expr = item.getExpression();
				if (expr instanceof Column) {
					Column col = (Column) expr;
					String realTableName = null;
					var colTable = col.getTable();
					if (colTable == null) {
						if (from instanceof Table) {
							colTable = (Table) from; // Use default table
							realTableName = noSquareBracket(tableNames.get(colTable.getName()));
						}
					} else {
						realTableName = noSquareBracket(tableNames.get(colTable.getName()));
					}
					var outputColumnName = noSquareBracket(item.getAlias() == null ? col.getColumnName() : item.getAlias().getName());
					log.debug("Column: {} = [{} = {}].[{}] {} AS [{}]", outputColumnName, realTableName, (colTable == null ? "DEFAULT" : noSquareBracket(colTable.getName())), noSquareBracket(col.getColumnName()), col, item.getAlias());

					dataviewField.setFieldName(outputColumnName);
					dataviewField.setTableAliasName(colTable == null ? null : noSquareBracket(colTable.getName()));
					{
						var sysTableField = sysTableFieldService.get(realTableName, noSquareBracket(col.getColumnName()));
						if (sysTableField != null) {
							dataviewField.setSysTableField(sysTableField);
							dataviewField.setIsPrimaryKey(sysTableField.getIsPrimaryKey());
							dataviewField.setIsReadonly(sysTableField.getIsReadonly());
							dataviewField.setForeignKeyDataviewName(sysTableField.getForeignKeyDataviewName());
							dataviewField.setGroupName(sysTableField.getGroupName());
							dataviewField.setGuiHint(sysTableField.getGuiHint());
						} else {
							// Use generated data
							var sysTable = sysTableComponent.getSysTableByTableName(realTableName).orElse(null);
							if (sysTable != null) {
								sysTableField = sysTable.getFields().stream().filter(field -> StringUtils.equalsIgnoreCase(field.getFieldName(), noSquareBracket(col.getColumnName()))).findFirst().orElse(null);
								if (sysTableField != null) {
									log.debug("Using {} for {}.{}", sysTableField.getFieldName(), realTableName, outputColumnName);
									dataviewField.setIsPrimaryKey(sysTableField.getIsPrimaryKey());
									dataviewField.setIsReadonly(sysTableField.getIsReadonly());
									dataviewField.setForeignKeyDataviewName(sysTableField.getForeignKeyDataviewName());
									dataviewField.setGroupName(sysTableField.getGroupName());
									dataviewField.setGuiHint(sysTableField.getGuiHint());
								}
							}
						}
					}
					
				} else {
					var outputColumnName = noSquareBracket(item.getAlias() == null ? "UNNAMED" : item.getAlias().getName());
					log.debug("Selected: {} = {} {}", outputColumnName, expr.getClass().getName(), expr);

					dataviewField.setFieldName(outputColumnName);
				}

				dataviewFields.add(dataviewField);
			});

			var where = select.getWhere();
			if (where != null) {
				log.debug("Where: {} {}", where.getClass().getName(), where);
			}

			StringBuilder builder = new StringBuilder();
			StatementDeParser deParser = new StatementDeParser(builder);
			deParser.visit(select);
			log.debug("Deparsed: {}", builder);

			Map<String, SysDataviewParam> dataviewParameters = new LinkedHashMap<>();
			var SQL_PARAMETER_PATTERN = Pattern.compile(":[a-z][_a-z0-9]*", Pattern.CASE_INSENSITIVE);
			var parameters = SQL_PARAMETER_PATTERN.matcher(sqlSelectStatement);
			while (parameters.find()) {
				var parameterName = parameters.group(0);
				log.debug("Found SQL parameter {}", parameterName);
				SysDataviewParam dataviewParameter = new SysDataviewParam();
				dataviewParameter.setParamName(parameterName);
				dataviewParameter.setParamType("STRING");
				if (StringUtils.endsWithIgnoreCase(parameterName, "id")) {
					dataviewParameter.setParamType("INTEGER");
				}
				dataviewParameter.setSortOrder(dataviewParameters.size());
				dataviewParameters.put(parameterName, dataviewParameter);
			}

			var dataviewSql = new SysDataviewSql();
			dataviewSql.setSqlStatement(builder.toString());

			var dv = new SysDataview();
			dv.setFields(dataviewFields);
			dv.setParameters(List.copyOf(dataviewParameters.values()));
			dv.setSqls(List.of(dataviewSql));

			var details = new SysDataviewDetails();
			details.entity = dv;
			details.setFields(dv.getFields().stream().map(stf -> {
				var tstf = new TranslatedSysDataviewField();
				tstf.entity = stf;
				if (stf.getSysTableField() != null) {
					if (stf.getSysTableField().getId() != null) {
						tstf.setSysTableField(sysTableFieldService.loadTranslated(stf.getSysTableField().getId()));
					} else {
						tstf.setSysTableField(SysTableFieldTranslationService.TranslatedSysTableField.from(stf.getSysTableField(), null, null));
					}
				}
				return tstf;
			}).collect(Collectors.toList()));
			return details;
		}

		private String noSquareBracket(String str) {
			if (StringUtils.isBlank(str)) return str;
			if (str.startsWith("[") && str.endsWith("]")) return str.substring(1, str.length() - 1);
			return str;
		}

		@Override
		@Transactional
		public SysDataview updateDataviewFromXML(InputStream inputStream, SysDataview target) throws JDOMException, IOException {
			target = reload(target);

			SAXBuilder saxBuilder = new SAXBuilder();
			Document document = saxBuilder.build(inputStream);
			Element rootElement = document.getRootElement();
			Element sysDataviewEl = rootElement.getChild("sys_dataview");

			target.setDataviewName(xmlTextOf(sysDataviewEl, "dataview_name"));
			target.setIsEnabled(xmlTextOf(sysDataviewEl, "is_enabled"));
			target.setIsReadonly(xmlTextOf(sysDataviewEl, "is_readonly"));
			target.setIsTransform(xmlTextOf(sysDataviewEl, "is_transform"));
			target.setCategoryCode(xmlTextOf(sysDataviewEl, "category_code"));
			target.setDatabaseAreaCode(xmlTextOf(sysDataviewEl, "database_area_code"));
			target.setTransformFieldForNames(xmlTextOf(sysDataviewEl, "transform_field_for_names"));
			target.setTransformFieldForCaptions(xmlTextOf(sysDataviewEl, "transform_field_for_captions"));
			target.setTransformFieldForValues(xmlTextOf(sysDataviewEl, "transform_field_for_values"));
			target.setConfigurationOptions(xmlTextOf(sysDataviewEl, "configuration_options"));

			String databaseAreaCodeSortOrderStr = xmlTextOf(sysDataviewEl, "database_area_code_sort_order");
			target.setDatabaseAreaCodeSortOrder(databaseAreaCodeSortOrderStr != null ? Integer.parseInt(databaseAreaCodeSortOrderStr) : null);

			SysDataview updatedSysDataview = repository.save(target);

			HashSet<SysDataviewLang> currentLangs = Sets.newHashSet(dataviewLangRepository.findAll(QSysDataviewLang.sysDataviewLang.entity().eq(updatedSysDataview)));
			rootElement.getChildren("sys_dataview_lang").stream().map(e -> upsertSysDataviewLang(e, updatedSysDataview)).forEach(currentLangs::remove);
			dataviewLangRepository.deleteAllInBatch(currentLangs);

			HashSet<SysDataviewSql> currentSQLs = Sets.newHashSet(sysDataviewSqlRepository.findAll(QSysDataviewSql.sysDataviewSql.dataview().eq(updatedSysDataview)));
			rootElement.getChildren("sys_dataview_sql").stream().map(e -> upsertSysDataviewSql(e, updatedSysDataview)).forEach(currentSQLs::remove);
			sysDataviewSqlRepository.deleteAllInBatch(currentSQLs);

			HashSet<SysDataviewParam> currentParams = Sets.newHashSet(dataviewParamRepository.findAll(QSysDataviewParam.sysDataviewParam.dataview().eq(updatedSysDataview)));
			AtomicInteger sortOrder = new AtomicInteger(0);
			rootElement.getChildren("sys_dataview_param").stream()
				// convert
				.map(e -> upsertSysDataviewParam(e, updatedSysDataview))
				// sort
				.sorted(Comparator.comparingInt(SysDataviewParam::getSortOrder))
				// store
				.forEach(sysDataviewParam -> {
					sysDataviewParam.setSortOrder(sortOrder.getAndIncrement());
					currentParams.remove(dataviewParamRepository.save(sysDataviewParam));
				});
			dataviewParamRepository.deleteAllInBatch(currentParams);
			sortOrder.set(0); // reset

			HashSet<SysDataviewField> currentFields = Sets.newHashSet(dataviewFieldRepository.findAll(QSysDataviewField.sysDataviewField.dataview().eq(updatedSysDataview)));
			List<Element> sysDataviewFieldLangs = rootElement.getChildren("sys_dataview_field_lang");
			rootElement.getChildren("sys_dataview_field").stream()
				// convert
				.map(e -> upsertSysDataviewField(e, updatedSysDataview))
				// sort
				.sorted(Comparator.comparingInt(SysDataviewField::getSortOrder))
				// store
				.forEach(sysDataviewField -> {
					sysDataviewField.setSortOrder(sortOrder.getAndIncrement());
					var savedSysDataviewField = dataviewFieldRepository.save(sysDataviewField);
					currentFields.remove(savedSysDataviewField);

					HashSet<SysDataviewFieldLang> currentFieldLangs = Sets.newHashSet(fieldLangRepository.findAll(QSysDataviewFieldLang.sysDataviewFieldLang.entity().eq(sysDataviewField)));

					sysDataviewFieldLangs.stream().filter(el -> Objects.equals(el.getChild("field_name").getValue(), savedSysDataviewField
						.getFieldName())).forEach(el -> {
						SysDataviewFieldLang sysDataviewFieldLang = upsertSysDataviewFieldLang(el, savedSysDataviewField);
						currentFieldLangs.remove(sysDataviewFieldLang);
					});

					fieldLangRepository.deleteAllInBatch(currentFieldLangs);
				});

			dataviewFieldRepository.deleteAll(currentFields);

			return updatedSysDataview;
		}

		@Override
		@Transactional(noRollbackFor = { NotFoundElement.class })
		public SysDataview registerDataviewFromXML(InputStream inputStream) throws JDOMException, IOException {
			SAXBuilder saxBuilder = new SAXBuilder();
			Document document = saxBuilder.build(inputStream);
			Element rootElement = document.getRootElement();

			Element sysDataviewEl = rootElement.getChild("sys_dataview");
			SysDataview sysDataview = new SysDataview();
			sysDataview.setDataviewName(xmlTextOf(sysDataviewEl, "dataview_name"));
			sysDataview.setIsEnabled(xmlTextOf(sysDataviewEl, "is_enabled"));
			sysDataview.setIsReadonly(xmlTextOf(sysDataviewEl, "is_readonly"));
			sysDataview.setIsTransform(xmlTextOf(sysDataviewEl, "is_transform"));
			sysDataview.setCategoryCode(xmlTextOf(sysDataviewEl, "category_code"));
			sysDataview.setDatabaseAreaCode(xmlTextOf(sysDataviewEl, "database_area_code"));
			sysDataview.setTransformFieldForNames(xmlTextOf(sysDataviewEl, "transform_field_for_names"));
			sysDataview.setTransformFieldForCaptions(xmlTextOf(sysDataviewEl, "transform_field_for_captions"));
			sysDataview.setTransformFieldForValues(xmlTextOf(sysDataviewEl, "transform_field_for_values"));
			sysDataview.setConfigurationOptions(xmlTextOf(sysDataviewEl, "configuration_options"));

			String databaseAreaCodeSortOrderStr = xmlTextOf(sysDataviewEl, "database_area_code_sort_order");
			sysDataview.setDatabaseAreaCodeSortOrder(databaseAreaCodeSortOrderStr != null ? Integer.parseInt(databaseAreaCodeSortOrderStr) : null);

			SysDataview savedSysDataview = repository.save(sysDataview);

			rootElement.getChildren("sys_dataview_lang").forEach(e -> registerSysDataviewLang(e, savedSysDataview));
			rootElement.getChildren("sys_dataview_sql").forEach(e -> registerSysDataviewSql(e, savedSysDataview));

			AtomicInteger sortOrder = new AtomicInteger(0);
			rootElement.getChildren("sys_dataview_param").stream()
				// convert
				.map(e -> registerSysDataviewParam(e, savedSysDataview))
				// sort
				.sorted((a, b) -> Integer.compare(a.getSortOrder(), b.getSortOrder()))
				// store
				.forEach(sysDataviewParam -> {
					sysDataviewParam.setSortOrder(sortOrder.getAndIncrement());
					dataviewParamRepository.save(sysDataviewParam);
				});

			sortOrder.set(0); // reset
			List<Element> sysDataviewFieldLangs = rootElement.getChildren("sys_dataview_field_lang");
			rootElement.getChildren("sys_dataview_field").stream()
				// convert
				.map(e -> registerSysDataviewField(e, savedSysDataview))
				// sort
				.sorted((a, b) -> Integer.compare(a.getSortOrder(), b.getSortOrder()))
				// store
				.forEach(sysDataviewField -> {
					sysDataviewField.setSortOrder(sortOrder.getAndIncrement());
					dataviewFieldRepository.save(sysDataviewField);
					List<SysDataviewFieldLang> langs = sysDataviewFieldLangs.stream().filter(el -> Objects.equals(el.getChild("field_name").getValue(), sysDataviewField
							.getFieldName())).map(el -> registerSysDataviewFieldLang(el, sysDataviewField))
						// keep not-nulls
						.filter(lang -> lang != null).collect(Collectors.toList());
					sysDataviewField.setLangs(langs);
				});

			return savedSysDataview;
		}

		private String xmlTextOf(Element xmlNode, String nodeName) {
			var node = xmlNode.getChild(nodeName);
			return node != null ? StringUtils.trimToNull(node.getValue()) : null;
		}

		private SysDataviewLang upsertSysDataviewLang(Element element, SysDataview dataview) {
			SysLang sysLang = languageService.getLanguage(element.getChild("ietf_tag").getValue());
			var existing = dataviewLangRepository.getByEntityAndSysLang(dataview, sysLang);
			if (existing != null) {
				existing.setTitle(xmlTextOf(element, "title"));
				existing.setDescription(xmlTextOf(element, "description"));
				return dataviewLangRepository.save(existing);
			} else return registerSysDataviewLang(element, dataview);
		}

		private SysDataviewLang registerSysDataviewLang(Element element, SysDataview dataview) {
			SysLang sysLang = languageService.getLanguage(element.getChild("ietf_tag").getValue());
			if (sysLang == null) {
				log.warn("Could not register SysDataviewLang, no language {}", element.getChild("ietf_tag").getValue());
				return null;
			}
			SysDataviewLang sysDataviewLang = new SysDataviewLang();
			sysDataviewLang.setEntity(dataview);

			sysDataviewLang.setTitle(xmlTextOf(element, "title"));
			sysDataviewLang.setDescription(xmlTextOf(element, "description"));

			sysDataviewLang.setSysLang(sysLang);
			return dataviewLangRepository.save(sysDataviewLang);
		}

		private SysDataviewField upsertSysDataviewField(Element element, SysDataview dataview) {
			String fieldName = xmlTextOf(element, "field_name");
			SysDataviewField existing = dataviewFieldRepository.findByDataviewAndFieldName(dataview, fieldName);
			if (existing != null) {
				existing.setFieldName(xmlTextOf(element, "field_name"));
				existing.setIsReadonly(xmlTextOf(element, "is_readonly"));
				existing.setIsPrimaryKey(xmlTextOf(element, "is_primary_key"));
				existing.setIsTransform(xmlTextOf(element, "is_transform"));
				existing.setTableAliasName(xmlTextOf(element, "table_alias_name"));
				existing.setIsVisible(xmlTextOf(element, "is_visible"));
				existing.setGuiHint(xmlTextOf(element, "gui_hint"));
				existing.setForeignKeyDataviewName(xmlTextOf(element, "foreign_key_dataview_name"));
				existing.setConfigurationOptions(xmlTextOf(element, "configuration_options"));
				existing.setGroupName(xmlTextOf(element, "group_name"));
				existing.setSortOrder(Integer.parseInt(xmlTextOf(element, "sort_order")));

				String tableName = xmlTextOf(element, "table_name");
				String tableFieldName = xmlTextOf(element, "table_field_name");
				if (StringUtils.isNotBlank(tableName) && StringUtils.isNotBlank(tableFieldName)) {
					var sysTableField = sysTableFieldRepository.findByTableNameAndFieldName(tableName, tableFieldName);
					if (sysTableField == null) {
						log.warn("Unmapped SysTableField {}.{}", tableName, tableFieldName);
						try {
							sysTableField = sysTableFieldService.generateMapping(tableName, tableFieldName);
						} catch (SysTableMappingException e) {
							log.error("Could not generate mapping for {}.{}: {}", tableName, tableFieldName, e.getMessage());
						}
					}
					existing.setSysTableField(sysTableField);
				}
				return existing;
			} else {
				log.warn("Registering a new SysDataviewField {}.{}", dataview, fieldName);
				return registerSysDataviewField(element, dataview);
			}
		}

		private SysDataviewField registerSysDataviewField(Element element, SysDataview dataview) {
			SysDataviewField sysDataviewField = new SysDataviewField();
			sysDataviewField.setDataview(dataview);

			sysDataviewField.setFieldName(xmlTextOf(element, "field_name"));
			sysDataviewField.setIsReadonly(xmlTextOf(element, "is_readonly"));
			sysDataviewField.setIsPrimaryKey(xmlTextOf(element, "is_primary_key"));
			sysDataviewField.setIsTransform(xmlTextOf(element, "is_transform"));
			sysDataviewField.setTableAliasName(xmlTextOf(element, "table_alias_name"));
			sysDataviewField.setIsVisible(xmlTextOf(element, "is_visible"));
			sysDataviewField.setGuiHint(xmlTextOf(element, "gui_hint"));
			sysDataviewField.setForeignKeyDataviewName(xmlTextOf(element, "foreign_key_dataview_name"));
			sysDataviewField.setConfigurationOptions(xmlTextOf(element, "configuration_options"));
			sysDataviewField.setGroupName(xmlTextOf(element, "group_name"));
			sysDataviewField.setSortOrder(Integer.parseInt(xmlTextOf(element, "sort_order")));

			String tableName = xmlTextOf(element, "table_name");
			String tableFieldName = xmlTextOf(element, "table_field_name");
			if (StringUtils.isNotBlank(tableName) && StringUtils.isNotBlank(tableFieldName)) {
				var sysTableField = sysTableFieldRepository.findByTableNameAndFieldName(tableName, tableFieldName);
				if (sysTableField == null) {
					log.warn("Unmapped SysTableField {}.{}", tableName, tableFieldName);
					try {
						sysTableField = sysTableFieldService.generateMapping(tableName, tableFieldName);
					} catch (SysTableMappingException e) {
						log.error("Could not generate mapping for {}.{}: {}", tableName, tableFieldName, e.getMessage());
					}
				}
				sysDataviewField.setSysTableField(sysTableField);
			}

			return sysDataviewField;
		}

		private SysDataviewFieldLang upsertSysDataviewFieldLang(Element element, SysDataviewField entity) {
			SysLang sysLang = languageService.getLanguage(element.getChild("ietf_tag").getValue());
			var existing = fieldLangRepository.getByEntityAndSysLang(entity, sysLang);
			if (existing != null) {
				existing.setTitle(xmlTextOf(element, "title"));
				existing.setDescription(xmlTextOf(element, "description"));
				return fieldLangRepository.save(existing);
			} else return registerSysDataviewFieldLang(element, entity);
		}

		private SysDataviewFieldLang registerSysDataviewFieldLang(Element element, SysDataviewField entity) {
			SysLang sysLang = languageService.getLanguage(element.getChild("ietf_tag").getValue());
			if (sysLang == null) {
				log.warn("Will not register SysDataviewFieldLang, missing language {}", element.getChild("ietf_tag").getValue());
				return null;
			}

			SysDataviewFieldLang lang = new SysDataviewFieldLang();
			lang.setSysLang(sysLang);
			lang.setEntity(entity);
			lang.setTitle(xmlTextOf(element, "title"));
			lang.setDescription(xmlTextOf(element, "description"));

			return fieldLangRepository.save(lang);
		}

		private SysDataviewParam upsertSysDataviewParam(Element element, SysDataview dataview) {
			String paramName = xmlTextOf(element, "param_name");

			var existing = dataviewParamRepository.findByDataviewAndParamName(dataview, paramName);
			if (existing != null) {
				existing.setParamType(xmlTextOf(element, "param_type"));

				String sortOrder = xmlTextOf(element, "sort_order");
				existing.setSortOrder(sortOrder != null ? Integer.parseInt(sortOrder) : null);

				return existing;
			} else return registerSysDataviewParam(element, dataview);
		}

		private SysDataviewParam registerSysDataviewParam(Element element, SysDataview dataview) {
			SysDataviewParam sysDataviewParam = new SysDataviewParam();
			sysDataviewParam.setDataview(dataview);

			sysDataviewParam.setParamName(xmlTextOf(element, "param_name"));
			sysDataviewParam.setParamType(xmlTextOf(element, "param_type"));

			String sort_order = xmlTextOf(element, "sort_order");
			sysDataviewParam.setSortOrder(sort_order != null ? Integer.parseInt(sort_order) : null);

			return sysDataviewParam;
		}

		private SysDataviewSql upsertSysDataviewSql(Element element, SysDataview dataview) {
			String databaseEngineTag = xmlTextOf(element, "database_engine_tag");
			SysDataviewSql existing = sysDataviewSqlRepository.findByDataviewAndDatabaseEngineTag(dataview, databaseEngineTag);
			if (existing != null) {
				existing.setSqlStatement(xmlTextOf(element, "sql_statement"));
				return sysDataviewSqlRepository.save(existing);
			} else return registerSysDataviewSql(element, dataview);
		}

		private SysDataviewSql registerSysDataviewSql(Element element, SysDataview dataview) {
			SysDataviewSql sysDataviewSql = new SysDataviewSql();
			sysDataviewSql.setDataview(dataview);

			sysDataviewSql.setDatabaseEngineTag(xmlTextOf(element, "database_engine_tag"));
			sysDataviewSql.setSqlStatement(xmlTextOf(element, "sql_statement"));
			return sysDataviewSqlRepository.save(sysDataviewSql);
		}

		@Override
		public SysDataviewDetails getDetails(long id) {
			var dv = loadTranslated(id);
			var details = new SysDataviewDetails();
			details.entity = dv.entity;
			details.title = dv.getTitle();
			details.description = dv.getDescription();
			details.setFields(dv.entity.getFields().stream().map(stf -> sysDataviewFieldService.loadTranslated(stf.getId())).collect(Collectors.toList()));
			return details;
		}

	}

	@Service
	@Transactional(readOnly = true)
	public static class SysDataviewLangServiceImpl extends CRUDService2Impl<SysDataviewLang, SysDataviewLangRepository> implements SysDataviewLangService {

		@Override
		@Transactional
		public SysDataviewLang create(SysDataviewLang source) {
			return _lazyLoad(repository.save(source));
		}

		@Override
		@Transactional
		public SysDataviewLang createFast(SysDataviewLang source) {
			return repository.save(source);
		}

		@Override
		@Transactional
		public SysDataviewLang update(SysDataviewLang updated, SysDataviewLang target) {
			return _lazyLoad(updateFast(updated, target));
		}

		@Override
		@Transactional
		public SysDataviewLang updateFast(@NotNull @Valid SysDataviewLang updated, SysDataviewLang target) {
			target.apply(updated);
			return repository.save(target);
		}
	}

	/**
	 * The Class SysDataviewFieldServiceImpl.
	 */
	@Service
	@Transactional(readOnly = true)
	public static class SysDataviewFieldServiceImpl extends FilteredTranslatedCRUDServiceImpl<SysDataviewField, SysDataviewFieldLang, SysDataviewFieldTranslationService.TranslatedSysDataviewField, SysDataviewFieldFilter, SysDataviewFieldRepository> implements SysDataviewFieldService {

		@Autowired
		@Lazy
		private SysTableFieldTranslationService sysTableFieldService;

		@Override
		@Transactional
		public SysDataviewField create(SysDataviewField source) {
			return _lazyLoad(repository.save(source));
		}

		@Override
		@Transactional
		public SysDataviewField createFast(SysDataviewField source) {
			return repository.save(source);
		}

		@Override
		public TranslatedSysDataviewField create(TranslatedSysDataviewField source) {
			if (source.getSysTableField() != null && source.getSysTableField().getId() != null) {
				// Need to handle TranslatedSysTableField!
				source.entity.setSysTableField(new SysTableField(source.getSysTableField().getId()));
			}
			return super.create(source);
		}

		@Override
		@Transactional
		public SysDataviewField update(SysDataviewField updated, SysDataviewField target) {
			return _lazyLoad(updateFast(updated, target));
		}

		@Override
		@Transactional
		public SysDataviewField updateFast(@NotNull @Valid SysDataviewField updated, SysDataviewField target) {
			target.apply(updated);
			return repository.save(target);
		}
	
		@Override
		public TranslatedSysDataviewField loadTranslated(long id) {
			var tsdf = super.loadTranslated(id);
			if (tsdf.getEntity().getSysTableField() != null) {
				tsdf.setSysTableField(sysTableFieldService.getTranslated(tsdf.getEntity().getSysTableField()));
			}
			return tsdf;
		}
	}

	@Service
	@Transactional(readOnly = true)
	public static class SysDataviewFieldTranslationSupport extends BaseTranslationSupport<SysDataviewField, SysDataviewFieldLang, SysDataviewFieldTranslationService.TranslatedSysDataviewField, SysDataviewFieldFilter, SysDataviewFieldLangRepository> implements SysDataviewFieldTranslationService {

		public SysDataviewFieldTranslationSupport() {
			super();
		}

		@Override
		protected TranslatedSysDataviewField toTranslated(SysDataviewField e, String title, String description) {
			return TranslatedSysDataviewField.from(e, title, description);
		}
	}

	@Service
	@Transactional(readOnly = true)
	public static class SysDataviewFieldLangServiceImpl extends CRUDService2Impl<SysDataviewFieldLang, SysDataviewFieldLangRepository> implements SysDataviewFieldLangService {

		@Override
		@Transactional
		public SysDataviewFieldLang create(SysDataviewFieldLang source) {
			return _lazyLoad(repository.save(source));
		}

		@Override
		@Transactional
		public SysDataviewFieldLang createFast(SysDataviewFieldLang source) {
			return repository.save(source);
		}

		@Override
		@Transactional
		public SysDataviewFieldLang update(SysDataviewFieldLang updated, SysDataviewFieldLang target) {
			return _lazyLoad(updateFast(updated, target));
		}

		@Override
		@Transactional
		public SysDataviewFieldLang updateFast(@NotNull @Valid SysDataviewFieldLang updated, SysDataviewFieldLang target) {
			target.apply(updated);
			return repository.save(target);
		}
	}

	@Service
	@Transactional(readOnly = true)
	public static class SysDataviewParamServiceImpl extends CRUDService2Impl<SysDataviewParam, SysDataviewParamRepository> implements SysDataviewParamService {

		@Override
		@Transactional
		public SysDataviewParam create(SysDataviewParam source) {
			return _lazyLoad(repository.save(source));
		}

		@Override
		@Transactional
		public SysDataviewParam createFast(SysDataviewParam source) {
			return repository.save(source);
		}

		@Override
		@Transactional
		public SysDataviewParam update(SysDataviewParam updated, SysDataviewParam target) {
			return _lazyLoad(updateFast(updated, target));
		}

		@Override
		@Transactional
		public SysDataviewParam updateFast(@NotNull @Valid SysDataviewParam updated, SysDataviewParam target) {
			target.apply(updated);
			return repository.save(target);
		}
	}

	@Service
	@Transactional(readOnly = true)
	public static class SysDataviewSqlServiceImpl extends CRUDService2Impl<SysDataviewSql, SysDataviewSqlRepository> implements SysDataviewSqlService {

		@Override
		@Transactional
		public SysDataviewSql create(SysDataviewSql source) {
			return _lazyLoad(repository.save(source));
		}

		@Override
		@Transactional
		public SysDataviewSql createFast(SysDataviewSql source) {
			return repository.save(source);
		}

		@Override
		@Transactional
		public SysDataviewSql update(SysDataviewSql updated, SysDataviewSql target) {
			return _lazyLoad(updateFast(updated, target));
		}

		@Override
		@Transactional
		public SysDataviewSql updateFast(@NotNull @Valid SysDataviewSql updated, SysDataviewSql target) {
			target.apply(updated);
			return repository.save(target);
		}
	}
}