Java tutorial
/* Copyright (C) 2007-2011 Database Group - Universita' della Basilicata Giansalvatore Mecca - giansalvatore.mecca@unibas.it Salvatore Raunich - salrau@gmail.com This file is part of ++Spicy - a Schema Mapping and Data Exchange Tool ++Spicy is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or any later version. ++Spicy is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with ++Spicy. If not, see <http://www.gnu.org/licenses/>. */ package it.unibas.spicy.model.algebra.query.operators.sql; import it.unibas.spicy.model.mapping.MappingTask; import it.unibas.spicy.model.datasource.INode; import it.unibas.spicy.model.datasource.nodes.SetCloneNode; import it.unibas.spicy.model.datasource.nodes.SetNode; import it.unibas.spicy.model.datasource.operators.FindNode; import it.unibas.spicy.model.exceptions.IllegalMappingTaskException; import it.unibas.spicy.model.expressions.Expression; import it.unibas.spicy.model.mapping.ComplexConjunctiveQuery; import it.unibas.spicy.model.mapping.ComplexQueryWithNegations; import it.unibas.spicy.model.mapping.ConstantFORule; import it.unibas.spicy.model.mapping.EngineConfiguration; import it.unibas.spicy.model.mapping.FORule; import it.unibas.spicy.model.mapping.NegatedComplexQuery; import it.unibas.spicy.model.mapping.SimpleConjunctiveQuery; import it.unibas.spicy.model.mapping.operators.GenerateSetVariables; import it.unibas.spicy.model.mapping.proxies.ChainingDataSourceProxy; import it.unibas.spicy.model.paths.PathExpression; import it.unibas.spicy.model.paths.SetAlias; import it.unibas.spicy.model.paths.VariableCorrespondence; import it.unibas.spicy.model.paths.VariableJoinCondition; import it.unibas.spicy.model.paths.VariablePathExpression; import it.unibas.spicy.model.paths.VariableSelectionCondition; import it.unibas.spicy.utility.SpicyEngineConstants; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; public class GenerateSQL { ////////////////////////////////////////////// private static Log logger = LogFactory.getLog(GenerateSQL.class); public final static String INTERMEDIATE_SCHEMA_NAME = "intermediate"; final static String CREATE_VIEW = "create or replace view "; final static String CREATE_TABLE = "create table "; final static String DELETE_EXISTING_TABLE = "drop table if exists "; final static String SKOLEM_TABLE_NAME = SpicyEngineConstants.WORK_SCHEMA_NAME + ".skolemTable"; final static String SKOLEM_TABLE_COLUMN_ID = "id"; final static String SKOLEM_TABLE_COLUMN_SKOLEM = "skolem"; final static String SKOLEM_VIEW_NAME = SpicyEngineConstants.WORK_SCHEMA_NAME + ".skolemString"; final static String NULL_VALUE = "null"; final static String RELATION_ALIAS_PREFIX = "rel_"; final static String PRE_EXPANSION_PREFIX = "pre_"; final static String POSITIVE_QUERY = "pos_"; final static String INDENT = " "; final static String DOUBLE_INDENT = " "; public final static int SKOLEM_ID_MIN_VALUE = 1000; public final static int CHAINING_FIRST_STEP = 0; public final static int CHAINING_LAST_STEP = 1; public final static int CHAINING_NO_CHAINING = 2; private static List<String> allViewsToDelete = new ArrayList<String>(); private static List<String> allTablesToDelete = new ArrayList<String>(); public static Map<String, String> materializedViews = new HashMap<String, String>(); private static JepToPostgresConverter postgresConv = new JepToPostgresConverter(); private GeneratePLSQLfunctions functionEvaluation = new GeneratePLSQLfunctions(); private static int scenarioNo; public static boolean newSequence; public String generateSQL(MappingTask mappingTask, int scenarioNo) { if (mappingTask.getSourceProxy().getMappingData().isNested() || mappingTask.getTargetProxy().getMappingData().isNested()) { throw new IllegalMappingTaskException( "Data Sources are nested. SQL can be generated for relational sources only"); } this.scenarioNo = scenarioNo; this.newSequence = true; StringBuilder result = new StringBuilder(); result.append("-- This script was automatically generated by the MIPMap mapping tool.\n\n"); result.append("BEGIN TRANSACTION;\n"); result.append("SET CONSTRAINTS ALL DEFERRED;\n"); //ioannisxar //evaluate string operation in postgres - plsql function result.append( "\n---------------------------- GENERATE PLSQL FUNCTIONS ---------------------------------\n"); result.append(functionEvaluation.generateFuncGenerator()); if (mappingTask.getConfig().useSortInSkolems()) { result.append(mappingTask.getDBMSHandler().generateSortArrayFunction()); } // Generate the SQL script for the first step in chaining scenarios if (mappingTask.getSourceProxy() instanceof ChainingDataSourceProxy) { ChainingDataSourceProxy proxy = (ChainingDataSourceProxy) mappingTask.getSourceProxy(); GenerateSQLForSourceToTargetExchange stGeneratorFirstStep = new GenerateSQLForSourceToTargetExchange(); result.append(stGeneratorFirstStep.generateSQL(proxy.getMappingTask(), CHAINING_FIRST_STEP)); } result.append("\n---------------------------- PRE-EXCHANGE ---------------------------------\n"); result.append(this.getDropCreateWorkSchemaScript()); result.append(this.getDeleteTablesScript(mappingTask)); GenerateSQLForSourceToTargetExchange stGenerator = new GenerateSQLForSourceToTargetExchange(); for (Map.Entry<String, String> entry : SpicyEngineConstants.OFFSET_MAPPING.entrySet()) { result.append("\ndrop sequence if exists ").append(entry.getKey()).append(";"); result.append("\ncreate sequence ").append(entry.getKey()).append(";"); } if (!mappingTask.getConfig().useHashTextForSkolems() && mappingTask.getConfig().useSkolemTable()) { result.append(generateSQLForSkolemTable(mappingTask)); } if (mappingTask.getSourceProxy() instanceof ChainingDataSourceProxy) { result.append(stGenerator.generateSQL(mappingTask, CHAINING_LAST_STEP)); } else { result.append(stGenerator.generateSQL(mappingTask, CHAINING_NO_CHAINING)); } allTablesToDelete.addAll(stGenerator.getAllTablesToDelete()); if (mappingTask.getConfig().useCreateTableInSTExchange()) { allTablesToDelete.addAll(stGenerator.getAllViewsToDelete()); } else { allViewsToDelete.addAll(stGenerator.getAllViewsToDelete()); } result.append(generateFinalInserts(mappingTask)); result.append("COMMIT;\n"); /*if (!mappingTask.getConfig().useDebugMode()) { result.append(this.getAllViewsToDeleteScript()); result.append(this.getAllTablesToDeleteScript()); }*/ //giannisk //double quotes fixed value replacement return result.toString().replaceAll(SpicyEngineConstants.DOUBLE_QUOTES, "\""); //return result.toString(); } public static String getSourceSchemaName(int chainingStep) { if (chainingStep == CHAINING_FIRST_STEP || chainingStep == CHAINING_NO_CHAINING) { return SpicyEngineConstants.SOURCE_SCHEMA_NAME + scenarioNo; } if (chainingStep == CHAINING_LAST_STEP) { return INTERMEDIATE_SCHEMA_NAME; } throw new IllegalArgumentException("Wrong value for chainingStep parameter: " + chainingStep); } public static String getTargetSchemaName(int chainingStep) { if (chainingStep == CHAINING_FIRST_STEP) { return INTERMEDIATE_SCHEMA_NAME; } if (chainingStep == CHAINING_LAST_STEP || chainingStep == CHAINING_NO_CHAINING) { return SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo; } throw new IllegalArgumentException("Wrong value for chainingStep parameter: " + chainingStep); } private List<FORule> findRelevantTGDs(SetAlias targetVariable, List<FORule> tgds) { List<FORule> result = new ArrayList<FORule>(); for (FORule tgd : tgds) { if (containsUpToClone(tgd.getTargetView().getGenerators(), targetVariable)) { result.add(tgd); } } return result; } private boolean containsUpToClone(List<SetAlias> variables, SetAlias targetVariable) { for (SetAlias variable : variables) { if (variable.equalsOrIsClone(targetVariable)) { return true; } } return false; } private String generateFinalInserts(MappingTask mappingTask) { StringBuilder result = new StringBuilder(); if (mappingTask.getConfig().useSkolemTable()) { result.append( "\n------------------------------ FINAL INSERTS -----------------------------------\n\n"); result.append( "\n------------------------------ INSERT INTO TARGET DATABASE ---------------------------\n"); result.append(generateFinalInsert(mappingTask)); } return result.toString(); } private static String generateSQLForSkolemTable(MappingTask mappingTask) { StringBuilder result = new StringBuilder(); result.append("\n-------------------------- SKOLEM TABLE -------------------------------\n\n"); result.append(CREATE_TABLE).append(SKOLEM_TABLE_NAME).append("(\n"); result.append(INDENT).append(SKOLEM_TABLE_COLUMN_ID).append(" ") .append(mappingTask.getDBMSHandler().getAutoGeneratedColumnType()).append(",\n"); result.append(INDENT).append(SKOLEM_TABLE_COLUMN_SKOLEM); result.append(GenerateSQL.getSkolemColumnType(mappingTask.getConfig())); allTablesToDelete.add(SKOLEM_TABLE_NAME); result.append(");\n"); return result.toString(); } public static String generateInsertInSkolemTable() { StringBuilder result = new StringBuilder(); result.append("insert into ").append(SKOLEM_TABLE_NAME).append("(").append(SKOLEM_TABLE_COLUMN_SKOLEM) .append(")\n"); result.append(INDENT).append("select *\n"); result.append(INDENT).append("from ").append(SKOLEM_VIEW_NAME); result.append(";\n\n"); return result.toString(); } private String generateFinalInsert(MappingTask mappingTask) { StringBuilder result = new StringBuilder(); for (SetAlias targetVariable : mappingTask.getTargetProxy().getMappingData().getVariables()) { List<FORule> relevantTgds = findRelevantTGDs(targetVariable, mappingTask.getMappingData().getSTTgds()); if (!relevantTgds.isEmpty()) { result.append(generateInsertForRelationAfterExchange(targetVariable, mappingTask)); } } return result.toString(); } /////////////////////////// SQL INSERTS ////////////////////////////////// private String generateInsertForRelationAfterExchange(SetAlias targetVariable, MappingTask mappingTask) { StringBuilder result = new StringBuilder(); String fromValue = finalSqlNameAfterExchange(targetVariable); if (allTablesToDelete.contains(fromValue) || allViewsToDelete.contains(fromValue)) { result.append("insert into ") .append(targetRelationName(targetVariable, mappingTask, CHAINING_NO_CHAINING)).append("\n"); result.append(INDENT).append("select distinct *\n"); result.append(INDENT).append("from ").append(fromValue); result.append(";\n\n"); } return result.toString(); } ////////////////////// SQL VIEW NAMES private static String sqlNameForTgdWithNoSchemaName(FORule tgd) { return replaceSpecialCharacters(tgd.getId().toLowerCase()); } public static String tgdFinalSQLName(FORule tgd) { return SpicyEngineConstants.WORK_SCHEMA_NAME + "." + "TARGET_VALUES_" + sqlNameForTgdWithNoSchemaName(tgd); } private static String sqlNameForTgdWithNoSchemaName(ConstantFORule tgd) { return replaceSpecialCharacters(tgd.getId().toLowerCase()); } public static String tgdFinalSQLName(ConstantFORule tgd) { return SpicyEngineConstants.WORK_SCHEMA_NAME + "." + "TARGET_VALUES_" + sqlNameForTgdWithNoSchemaName(tgd); } public static String finalSqlNameAfterExchange(SetAlias variable) { return SpicyEngineConstants.WORK_SCHEMA_NAME + "." + "EXCHANGE_RESULT_FOR_" + variable.getBindingPathExpression().getLastStep(); } public static String attributeNameInVariable(VariablePathExpression attributePath) { return attributePath.getStartingVariable().toShortString() + attributePath.getLastStep(); } public static String attributeNameWithVariable(VariablePathExpression attributePath) { return RELATION_ALIAS_PREFIX + attributePath.getStartingVariable().toShortString() + "." //giannisk //put column names in double quotes, BUT for now use a fixed value which will be replaced at the end //so that the double quotes are not affected during the function conversion procces + SpicyEngineConstants.DOUBLE_QUOTES + attributePath.getLastStep() + SpicyEngineConstants.DOUBLE_QUOTES; } public static String functionSql(Expression transformationFunctionExpression, boolean withRel) { String functionString = postgresConv.convertToPostgres(transformationFunctionExpression, withRel, scenarioNo); //replace (for selection conditions) functionString = functionString.replaceAll("==", "="); functionString = functionString.replaceAll("&&", "AND"); return functionString; } private static String sourceRelationName(SetAlias variable, int chainingStep) { return getSourceSchemaName(chainingStep) + ".\"" + variable.getBindingPathExpression().getLastStep() + "\""; } public static String targetRelationName(SetAlias variable, MappingTask mappingTask, int chainingStep) { SetNode bindingNode = variable.getBindingNode(mappingTask.getTargetProxy().getIntermediateSchema()); String result = variable.getBindingPathExpression().getLastStep(); if (bindingNode instanceof SetCloneNode) { result = ((SetCloneNode) bindingNode).getOriginalNodePath().getLastStep(); } return getTargetSchemaName(chainingStep) + ".\"" + result + "\""; } private static String sqlString(Expression expression) { String result = ""; List<VariablePathExpression> attributePaths = expression.getAttributePaths(); if (expression.toString().startsWith("isNull(")) { String attributeName = extractAttributeNameFromExpression(attributePaths.get(0)); return attributeName + " IS NULL"; } if (expression.toString().startsWith("isNotNull(")) { String attributeName = extractAttributeNameFromExpression(attributePaths.get(0)); return attributeName + " IS NOT NULL"; } result = expression.toString(); for (int i = 0; i < attributePaths.size(); i++) { VariablePathExpression attributePath = attributePaths.get(i); String attributeName = extractAttributeNameFromExpression(attributePath); result = result.replaceAll(attributePath.toString(), attributeName); } result = result.replaceAll("==", "="); result = result.replaceAll("\"", "\'"); return result.replaceAll("&&", "AND"); } private static String extractAttributeNameFromExpression(VariablePathExpression attributePath) { String variableName = attributePath.getStartingVariable().toShortString(); String lastStep = attributePath.getLastStep(); return GenerateSQL.RELATION_ALIAS_PREFIX + variableName + "." + lastStep; } private static String replaceSpecialCharacters(String value) { return value.replaceAll("#", "_"); } public static String sqlNameForViewWithIntersection(ComplexQueryWithNegations query) { return SpicyEngineConstants.WORK_SCHEMA_NAME + "." + query.getId(); } public static String sqlNameForPositiveView(ComplexQueryWithNegations query) { return SpicyEngineConstants.WORK_SCHEMA_NAME + "." + POSITIVE_QUERY + query.getId(); } public static String sqlNameForRule(FORule tgd) { return SpicyEngineConstants.WORK_SCHEMA_NAME + "." + sqlNameForTgdWithNoSchemaName(tgd); } public static String generateCasting(VariablePathExpression attributePath, String columnName, MappingTask mappingTask) { INode attributeNode = new FindNode().findNodeInSchema(attributePath.getAbsolutePath(), mappingTask.getTargetProxy()); String value = attributeNode.getChild(0).getLabel(); String castedColumnName = mappingTask.getDBMSHandler().forceCast(columnName, value); return castedColumnName; } public static String renameSkolemTable(int skolemTableCounter) { return "sk_" + skolemTableCounter; } private String getAllViewsToDeleteScript() { StringBuilder result = new StringBuilder(); for (int i = allViewsToDelete.size() - 1; i >= 0; i--) { result.append("drop view ").append(allViewsToDelete.get(i)).append(" cascade;\n"); } return result.toString(); } private String getAllTablesToDeleteScript() { StringBuilder result = new StringBuilder(); for (int i = allTablesToDelete.size() - 1; i >= 0; i--) { result.append("drop table ").append(allTablesToDelete.get(i)).append(" cascade;\n"); } return result.toString(); } private String getDropCreateWorkSchemaScript() { StringBuilder result = new StringBuilder(); result.append("drop schema if exists ").append(SpicyEngineConstants.WORK_SCHEMA_NAME).append(" cascade;\n"); result.append("create schema ").append(SpicyEngineConstants.WORK_SCHEMA_NAME).append(";\n"); return result.toString(); } public String getDeleteTablesScript(MappingTask mappingTask) { StringBuilder result = new StringBuilder(); List<PathExpression> setPaths = new GenerateSetVariables() .findSetAbsolutePaths(mappingTask.getTargetProxy()); for (PathExpression pathExpression : setPaths) { INode node = new FindNode().findNodeInSchema(pathExpression, mappingTask.getTargetProxy()); if (!(node instanceof SetCloneNode)) { result.append("delete from ").append(SpicyEngineConstants.TARGET_SCHEMA_NAME).append(scenarioNo) .append(".\"").append(pathExpression.getLastStep()).append("\";\n"); } } return result.toString(); } public static boolean hasDifferences(FORule tgd) { boolean negations = !tgd.getComplexSourceQuery().getNegatedComplexQueries().isEmpty(); return negations; } private static String getSkolemColumnType(EngineConfiguration config) { if (config.useHashTextForSkolems()) { return " integer"; } return " text"; } public static VariableCorrespondence findCorrespondenceFromTargetPathWithSameId( VariablePathExpression targetPath, List<VariableCorrespondence> correspondences) { for (VariableCorrespondence correspondence : correspondences) { if (correspondence.getTargetPath().equalsAndHasSameVariableId(targetPath)) { return correspondence; } } return null; } public static VariablePathExpression findSourcePath(VariableCorrespondence variableCorrespondence) { if (variableCorrespondence != null) { return variableCorrespondence.getFirstSourcePath(); } return null; } private static VariablePathExpression findSourcePathWithEqualsId(List<VariableCorrespondence> correspondences, VariablePathExpression targetPath) { for (VariableCorrespondence variableCorrespondence : correspondences) { if (variableCorrespondence.getTargetPath().equalsAndHasSameVariableId(targetPath)) { return variableCorrespondence.getFirstSourcePath(); } } return null; } //////////////////////////////////////////////( public static String generateFromClause(List<SetAlias> variables, int chainingStep) { StringBuilder result = new StringBuilder(); result.append("from "); List<String> addedVariables = new ArrayList<String>(); for (int i = 0; i < variables.size(); i++) { SetAlias variable = variables.get(i); String relationName = GenerateSQL.sourceRelationName(variable, chainingStep); String aliasRelationName = GenerateSQL.RELATION_ALIAS_PREFIX + variable.toShortString(); // NOTE: we are preventing to reuse the same variable twice in the from clause // this might not be correct in all cases if (!addedVariables.contains(aliasRelationName)) { addedVariables.add(aliasRelationName); result.append(relationName).append(" AS ").append(aliasRelationName); // if (i != variables.size() - 1) result.append(", "); result.append(", "); } } result.delete(result.length() - ", ".length(), result.length() - 1); result.append("\n"); return result.toString(); } public static String generateWhereClause(ComplexConjunctiveQuery view) { if (!isNeededAWhereClause(view)) { return ""; } StringBuilder result = new StringBuilder(); result.append("where \n"); result.append(generateWhereClauseWithoutWhere(view)); return result.toString(); } private static String generateWhereClauseForIntersection(ComplexConjunctiveQuery view) { StringBuilder result = new StringBuilder(); result.append(INDENT).append("where \n"); List<VariableSelectionCondition> allSelectionConditions = view.getAllSelections(); List<VariableJoinCondition> joinConditions = new ArrayList<VariableJoinCondition>(view.getJoinConditions()); for (SimpleConjunctiveQuery simpleConjunctiveQuery : view.getConjunctions()) { joinConditions.addAll(simpleConjunctiveQuery.getAllJoinConditions()); } if (!joinConditions.isEmpty()) { for (int i = 0; i < joinConditions.size(); i++) { VariableJoinCondition joinCondition = joinConditions.get(i); result.append(DOUBLE_INDENT) .append(generateSQLStringForJoinConditionForIntersection(joinCondition, view)); result.append(" AND\n"); } } // check selection conditions for (int i = 0; i < allSelectionConditions.size(); i++) { VariableSelectionCondition condition = allSelectionConditions.get(i); //result.append(DOUBLE_INDENT).append(GenerateSQL.sqlString(condition.getCondition())); result.append(DOUBLE_INDENT) .append(GenerateSQL.functionSql(condition.getCondition(), true).replaceAll("\"", "\'")); if (i != allSelectionConditions.size() - 1 || view.hasIntersection()) result.append(" AND\n"); } if (view.hasIntersection()) { List<VariablePathExpression> leftIntersectionPaths = generateTargetPaths( view.getIntersectionEqualities().getLeftCorrespondences()); List<VariablePathExpression> rightIntersectionPaths = generateTargetPaths( view.getIntersectionEqualities().getRightCorrespondences()); List<VariableCorrespondence> allCorrespondences = new ArrayList<VariableCorrespondence>(); allCorrespondences.addAll(view.getIntersectionEqualities().getLeftCorrespondences()); allCorrespondences.addAll(view.getIntersectionEqualities().getRightCorrespondences()); for (int i = 0; i < leftIntersectionPaths.size(); i++) { VariablePathExpression leftPath = leftIntersectionPaths.get(i); VariablePathExpression rightPath = rightIntersectionPaths.get(i); VariablePathExpression leftSourcePath = findSourcePathWithEqualsId(allCorrespondences, leftPath); if (leftSourcePath == null) { leftSourcePath = leftPath; } VariablePathExpression rightSourcePath = findSourcePathWithEqualsId(allCorrespondences, rightPath); if (rightSourcePath == null) { rightSourcePath = rightPath; } result.append(DOUBLE_INDENT).append(attributeNameWithVariable(leftSourcePath)).append(" = ") .append(attributeNameWithVariable(rightSourcePath)); if (i != leftIntersectionPaths.size() - 1) result.append(" AND\n"); } } result.append("\n"); return result.toString(); } private static List<VariablePathExpression> generateTargetPaths(List<VariableCorrespondence> correspondences) { List<VariablePathExpression> result = new ArrayList<VariablePathExpression>(); for (VariableCorrespondence correspondence : correspondences) { result.add(correspondence.getTargetPath()); } return result; } public static String generateWhereClauseForNegation(ComplexConjunctiveQuery view) { if (!isNeededAWhereClause(view)) { return ""; } StringBuilder result = new StringBuilder(); result.append("where \n"); result.append(generateWhereClauseWithoutWhere(view)); return result.toString(); } private static boolean isNeededAWhereClause(ComplexConjunctiveQuery view) { if (view.getVariables().size() > 1) { return true; } if (view.getAllSelections().size() > 0) { return true; } return false; } private static String generateWhereClauseWithoutWhere(ComplexConjunctiveQuery view) { if (!isNeededAWhereClause(view)) { return ""; } StringBuilder result = new StringBuilder(); List<VariableSelectionCondition> allSelectionConditions = view.getAllSelections(); List<SimpleConjunctiveQuery> conjunctions = view.getConjunctions(); List<VariableJoinCondition> joinConditions = new ArrayList<VariableJoinCondition>(view.getJoinConditions()); for (SimpleConjunctiveQuery simpleConjunctiveQuery : conjunctions) { joinConditions.addAll(simpleConjunctiveQuery.getAllJoinConditions()); } if (!joinConditions.isEmpty()) { result.append("\n"); for (int i = 0; i < joinConditions.size(); i++) { VariableJoinCondition joinCondition = joinConditions.get(i); List<VariableCorrespondence> correspondences = new ArrayList<VariableCorrespondence>(); for (List<VariableCorrespondence> list : view.getCorrespondencesForConjunctions()) { correspondences.addAll(list); } result.append(DOUBLE_INDENT) .append(generateSQLStringForJoinConditionSourcePath(joinCondition, correspondences)); if (i != joinConditions.size() - 1 || !allSelectionConditions.isEmpty()) result.append(" AND\n"); } } // check selection conditions for (int i = 0; i < allSelectionConditions.size(); i++) { VariableSelectionCondition condition = allSelectionConditions.get(i); //result.append(DOUBLE_INDENT).append(GenerateSQL.sqlString(condition.getCondition())); result.append(DOUBLE_INDENT) .append(GenerateSQL.functionSql(condition.getCondition(), true).replaceAll("\"", "\'")); if (i != allSelectionConditions.size() - 1) result.append(" AND\n"); } result.append("\n"); return result.toString(); } private static String generateSQLStringForAdditionalJoinConditionSourcePath(VariableJoinCondition joinCondition, List<VariableCorrespondence> correspondences, String negationViewName) { StringBuilder result = new StringBuilder(); for (int i = 0; i < joinCondition.getFromPaths().size(); i++) { VariablePathExpression fromPath = joinCondition.getFromPaths().get(i); VariablePathExpression toPath = joinCondition.getToPaths().get(i); VariablePathExpression fromSourcePath = findSourcePath( findCorrespondenceFromTargetPathWithSameId(fromPath, correspondences)); if (fromSourcePath == null) { fromSourcePath = fromPath; } VariablePathExpression toSourcePath = findSourcePath( findCorrespondenceFromTargetPathWithSameId(toPath, correspondences)); if (toSourcePath == null) { toSourcePath = toPath; } result.append(DOUBLE_INDENT).append(negationViewName).append(".") .append(attributeNameInVariable(fromSourcePath)); result.append(" = "); result.append(negationViewName).append(".").append(attributeNameInVariable(toSourcePath)); if (i != joinCondition.getFromPaths().size() - 1) result.append(" AND\n"); } return result.toString(); } private static String generateSQLStringForJoinConditionSourcePath(VariableJoinCondition joinCondition, List<VariableCorrespondence> correspondences) { StringBuilder result = new StringBuilder(); for (int i = 0; i < joinCondition.getFromPaths().size(); i++) { VariablePathExpression fromPath = joinCondition.getFromPaths().get(i); VariablePathExpression toPath = joinCondition.getToPaths().get(i); VariablePathExpression fromSourcePath = findSourcePath( findCorrespondenceFromTargetPathWithSameId(fromPath, correspondences)); if (fromSourcePath == null) { fromSourcePath = fromPath; } VariablePathExpression toSourcePath = findSourcePath( findCorrespondenceFromTargetPathWithSameId(toPath, correspondences)); if (toSourcePath == null) { toSourcePath = toPath; } result.append(attributeNameWithVariable(fromSourcePath)).append(" = ") .append(attributeNameWithVariable(toSourcePath)); if (i != joinCondition.getFromPaths().size() - 1) result.append(" AND "); } return result.toString(); } private static String generateSQLStringForJoinConditionForIntersection(VariableJoinCondition joinCondition, ComplexConjunctiveQuery view) { StringBuilder result = new StringBuilder(); for (int i = 0; i < joinCondition.getFromPaths().size(); i++) { VariablePathExpression fromPath = joinCondition.getFromPaths().get(i); VariablePathExpression toPath = joinCondition.getToPaths().get(i); VariablePathExpression fromSourcePath = findSourcePathWithEqualsId(view.getAllCorrespondences(), fromPath); if (fromSourcePath == null) { fromSourcePath = fromPath; } VariablePathExpression toSourcePath = findSourcePathWithEqualsId(view.getAllCorrespondences(), toPath); if (toSourcePath == null) { toSourcePath = toPath; } result.append(attributeNameWithVariable(fromSourcePath)).append(" = ") .append(attributeNameWithVariable(toSourcePath)); if (i != joinCondition.getFromPaths().size() - 1) result.append(" AND "); } return result.toString(); } public static String generateProjectionWithIntersection(ComplexConjunctiveQuery query, MappingTask mappingTask, int chainingStep) { StringBuilder result = new StringBuilder(); List<SetAlias> sourceVariables = new ArrayList<SetAlias>(); sourceVariables.addAll(query.getVariables()); sourceVariables.addAll(query.getConjunctionForIntersection().getVariables()); String fromClause = GenerateSQL.generateFromClause(sourceVariables, chainingStep); String whereClause = GenerateSQL.generateWhereClauseForIntersection(query); result.append(INDENT).append("select distinct \n"); // List<SetAlias> variables = extractDifferentVariables(query.getVariables()); List<SetAlias> variables = query.getVariables(); result.append(generateProjectionOnAllAttributes(variables, mappingTask)); result.append(INDENT).append(fromClause); if (!whereClause.equals("")) { result.append(INDENT).append(whereClause); } return result.toString(); } public static String generateProjectionWithoutIntersection(ComplexConjunctiveQuery query, MappingTask mappingTask, int chainingStep) { StringBuilder result = new StringBuilder(); List<SetAlias> sourceVariables = query.getVariables(); String fromClause = GenerateSQL.generateFromClause(sourceVariables, chainingStep); String whereClause = GenerateSQL.generateWhereClause(query); result.append(INDENT).append("select distinct \n"); // List<SetAlias> variables = extractDifferentVariables(query.getVariables()); List<SetAlias> variables = query.getVariables(); result.append(generateProjectionOnAllAttributes(variables, mappingTask)); result.append(INDENT).append(fromClause); if (!whereClause.equals("")) { result.append(INDENT).append(whereClause); } return result.toString(); } // private static List<SetAlias> extractDifferentVariables(List<SetAlias> variables) { // List<SetAlias> result = new ArrayList<SetAlias>(); // for (SetAlias variable : variables) { // if (!containsVariableWithSameName(variable, result)) { // result.add(variable); // } // } // return result; // } // private static boolean containsVariableWithSameName(SetAlias variable, List<SetAlias> variables) { // for (SetAlias setAlias : variables) { // if (setAlias.hasSameId(variable)) { // return true; // } // } // return false; // } private static String generateProjectionOnAllAttributes(List<SetAlias> variables, MappingTask mappingTask) { StringBuilder result = new StringBuilder(); for (int i = 0; i < variables.size(); i++) { SetAlias variable = variables.get(i); List<VariablePathExpression> attributes = variable .getAttributes(mappingTask.getSourceProxy().getIntermediateSchema()); for (int j = 0; j < attributes.size(); j++) { VariablePathExpression attribute = attributes.get(j); result.append(DOUBLE_INDENT).append(GenerateSQL.attributeNameWithVariable(attribute)); result.append(" AS ").append(attributeNameInVariable(attribute)); if (j != attributes.size() - 1) result.append(",\n"); } if (i != variables.size() - 1) result.append(",\n"); } result.append("\n"); return result.toString(); } public static String generateProjectionForTgd(ComplexQueryWithNegations query, List<VariableCorrespondence> correspondences, int chainingStep) { String fromClause = GenerateSQL.generateFromClause(query.getVariables(), chainingStep); String whereClause = GenerateSQL.generateWhereClause(query.getComplexQuery()); return projectionOnTargetValuesFromCorrespondences(correspondences, fromClause, whereClause); } public static String generateProjectionFromPositiveQuery(ComplexQueryWithNegations query) { StringBuilder result = new StringBuilder(); result.append("select distinct *\n"); result.append("from ").append(sqlNameForPositiveView(query)).append("\n"); return result.toString(); } public static String generateProjectionFromFinalRuleNegation(String positiveViewName, NegatedComplexQuery negation) { StringBuilder result = new StringBuilder(); result.append(INDENT).append("select distinct ").append(positiveViewName).append(".*\n"); String negationViewName = findViewName(negation); result.append(INDENT).append("from ").append(positiveViewName).append(", ").append(negationViewName) .append("\n"); result.append(INDENT) .append(generateWhereClauseFromEqualities(negation, positiveViewName, negationViewName)); result.append(INDENT).append(generateWhereContentForAdditionalJoins(negation, negation.getCorrespondencesForJoin(), positiveViewName, negationViewName)); return result.toString(); } public static String generateProjectionFromNegation(String positiveViewName, NegatedComplexQuery negation) { StringBuilder result = new StringBuilder(); result.append(INDENT).append("select distinct ").append(positiveViewName).append(".*\n"); String negationViewName = findViewName(negation); result.append(INDENT).append("from ").append(positiveViewName).append(", ").append(negationViewName) .append("\n"); result.append(INDENT) .append(generateWhereClauseFromEqualities(negation, positiveViewName, negationViewName)); result.append(INDENT).append(generateWhereContentForAdditionalJoins(negation, negation.getCorrespondencesForJoin(), positiveViewName, negationViewName)); return result.toString(); } private static String generateWhereClauseFromEqualities(NegatedComplexQuery negation, String positiveViewName, String negationViewName) { StringBuilder result = new StringBuilder(); result.append("where \n"); if (negation.isTargetDifference()) { for (int i = 0; i < negation.getTargetEqualities().getLeftCorrespondences().size(); i++) { VariableCorrespondence leftCorrespondence = negation.getTargetEqualities().getLeftCorrespondences() .get(i); VariableCorrespondence rightCorrespondence = negation.getTargetEqualities() .getRightCorrespondences().get(i); //giannisk //so as not to take into account constant values if (!leftCorrespondence.isConstant() && !rightCorrespondence.isConstant()) { VariablePathExpression leftPath = leftCorrespondence.getFirstSourcePath(); VariablePathExpression rightPath = rightCorrespondence.getFirstSourcePath(); result.append(DOUBLE_INDENT).append(positiveViewName).append(".") .append(attributeNameInVariable(leftPath)); result.append(" = "); result.append(negationViewName).append(".").append(attributeNameInVariable(rightPath)); //if (i != negation.getTargetEqualities().getLeftCorrespondences().size() - 1) { result.append(" AND\n"); //} //so as not to take into account constant values /*if ((i != negation.getTargetEqualities().getLeftCorrespondences().size() - 1) && (!negation.getTargetEqualities().getLeftCorrespondences().get(i+1).isConstant()) ) result.append(" AND\n");*/ } } //giannisk //delete the last AND result = result.delete(result.length() - 5, result.length()); } else { for (int i = 0; i < negation.getSourceEqualities().getLeftPaths().size(); i++) { VariablePathExpression leftPath = negation.getSourceEqualities().getLeftPaths().get(i); VariablePathExpression rightPath = negation.getSourceEqualities().getRightPaths().get(i); result.append(DOUBLE_INDENT).append(positiveViewName).append(".") .append(attributeNameInVariable(leftPath)); result.append(" = "); result.append(negationViewName).append(".").append(attributeNameInVariable(rightPath)); if (i != negation.getSourceEqualities().getLeftPaths().size() - 1) result.append(" AND\n"); } } result.append("\n"); return result.toString(); } private static String generateWhereContentForAdditionalJoins(NegatedComplexQuery negation, List<VariableCorrespondence> correspondences, String positiveViewName, String negationViewName) { StringBuilder result = new StringBuilder(); if (negation.getAdditionalCyclicJoins().isEmpty()) { return ""; } result.append(" AND\n"); for (int i = 0; i < negation.getAdditionalCyclicJoins().size(); i++) { VariableJoinCondition joinCondition = negation.getAdditionalCyclicJoins().get(i); result.append(generateSQLStringForAdditionalJoinConditionSourcePath(joinCondition, correspondences, negationViewName)); if (i != negation.getAdditionalCyclicJoins().size() - 1) result.append(" AND\n"); } result.append("\n"); return result.toString(); } public static String generateProjectionFromNegationOnTargetRenaming(ComplexQueryWithNegations positiveQuery, NegatedComplexQuery negation) { StringBuilder result = new StringBuilder(); String positiveViewName = sqlNameForPositiveView(positiveQuery); result.append(INDENT).append("select distinct ").append(positiveViewName).append(".*\n"); String negationViewName = findViewName(negation); result.append(INDENT).append("from ").append(positiveViewName).append(", ").append(negationViewName) .append("\n"); result.append(INDENT) .append(generateWhereClauseFromEqualities(negation, positiveViewName, negationViewName)); return result.toString(); } private static String findViewName(NegatedComplexQuery negation) { String fromViewName; ComplexQueryWithNegations negatedQuery = negation.getComplexQuery(); if (materializedViews.containsKey(negatedQuery.getId())) { return materializedViews.get(negatedQuery.getId()); } // if (materializedViews.containsKey(negatedQuery.toString())) { // return materializedViews.get(negatedQuery.toString()); // } if (negatedQuery.getNegatedComplexQueries().isEmpty()) {// || isRewiQueryWithOnlySubsumeNegations(negatedQuery)) { fromViewName = sqlNameForPositiveView(negation.getComplexQuery()); } else { fromViewName = sqlNameForViewWithIntersection(negation.getComplexQuery()); } return fromViewName; } public static String projectionOnTargetValuesFromCorrespondences(List<VariableCorrespondence> correspondences, String fromClause, String whereClause) { StringBuilder result = new StringBuilder(); result.append(INDENT).append("select distinct \n"); List<String> alreadyCorrespondences = new ArrayList<String>(); for (int i = 0; i < correspondences.size(); i++) { VariableCorrespondence correspondence = correspondences.get(i); //giannisk //so as not to take into account constant values if (!correspondence.isConstant()) { //giannisk, so as not to take into account variables already selected if (!alreadyCorrespondences.contains(correspondence.getFirstSourcePath().toString())) { if (correspondence.getSourcePaths() != null) { result.append(DOUBLE_INDENT) .append(GenerateSQL.attributeNameWithVariable(correspondence.getFirstSourcePath())); alreadyCorrespondences.add(correspondence.getFirstSourcePath().toString()); } else { String sourcePathName = correspondence.getSourceValue().toString(); sourcePathName = sourcePathName.replaceAll("\"", "\'"); result.append(DOUBLE_INDENT).append(sourcePathName); } result.append(" as ") .append(GenerateSQL.attributeNameInVariable(correspondence.getFirstSourcePath())); result.append(",\n"); /*if ((i != correspondences.size() - 1) //giannisk //so as not to take into account constant values && (!correspondences.get(i+1).isConstant())) { result.append(",\n"); }*/ } } } //giannisk //delete the last "," character result = result.delete(result.length() - 2, result.length()); result.append("\n"); result.append(INDENT).append(fromClause); if (!whereClause.equals("")) { result.append(INDENT).append(whereClause); } return result.toString(); } /*************************** SQL GENERATION FOR PRIMARY KEY TRIGGER FUNCTIONS DURING SCHEMA LOADING * @param table * @param tableName * @param schemaName * @param PKcolumnNames * @return ******************************************************/ public static String createTriggerFunction(String table, String schemaName, String tableName, List<String> PKcolumnNames) { String tempTable = SpicyEngineConstants.WORK_SCHEMA_NAME + ".\"" + tableName + "\""; StringBuilder result = new StringBuilder(); result.append("CREATE OR REPLACE FUNCTION ").append(schemaName).append(".").append(tableName) .append("_check_not_equal()\n"); result.append("RETURNS trigger AS ' begin\n"); result.append("if exists (select * from ").append(table).append(" where "); for (String PKcolumnName : PKcolumnNames) { result.append(table).append(".").append(PKcolumnName).append(" = new.").append(PKcolumnName) .append(" and "); } //delete the last " and " result.delete(result.length() - 5, result.length()); result.append(") then\n"); result.append("EXECUTE ''create table if not exists ").append(tempTable).append(" (like ").append(table) .append(")''; \n"); result.append("insert into ").append(tempTable).append(" SELECT (NEW).*; \n"); result.append("raise NOTICE ''").append(SpicyEngineConstants.PRIMARY_KEY_CONSTR_NOTICE).append(tableName) .append("'';"); result.append("return null; \n"); result.append("end if; \n"); result.append("return new; \n"); result.append("end; ' LANGUAGE plpgsql;"); return result.toString(); } public static String createTriggerBeforeInsert(String table, String schemaName, String tableName) { StringBuilder result = new StringBuilder(); result.append("CREATE TRIGGER check_ids BEFORE INSERT ON ").append(table) .append(" FOR EACH ROW EXECUTE PROCEDURE ").append(schemaName).append(".").append(tableName) .append("_check_not_equal();"); return result.toString(); } }