public static String projectionOnTargetValuesFromCorrespondences(
List<VariableCorrespondence> correspondences, String fromClause, String whereClause) {
StringBuilder result = new StringBuilder();
result.append(INDENT).append("select distinct \n");
for (int i = 0; i < correspondences.size(); i++) {
VariableCorrespondence correspondence = correspondences.get(i);
if (correspondence.getSourcePaths() != null) {
result
.append(DOUBLE_INDENT)
.append(GenerateSQL.attributeNameWithVariable(correspondence.getFirstSourcePath()));
} else {
String sourcePathName = correspondence.getSourceValue().toString();
sourcePathName = sourcePathName.replaceAll("\"", "\'");
result.append(DOUBLE_INDENT).append(sourcePathName);
}
result
.append(" as ")
.append(GenerateSQL.attributeNameInVariable(correspondence.getFirstSourcePath()));
if (i != correspondences.size() - 1) result.append(",\n");
}
result.append("\n");
result.append(INDENT).append(fromClause);
if (!whereClause.equals("")) {
result.append(INDENT).append(whereClause);
}
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 String convertToPostgres(Expression transformationFunctionExpression, boolean withRel) {
String functionExpression =
transformationFunctionExpression.getJepExpression().toStringForSql();
// first fix attribute names
for (VariablePathExpression vpe : transformationFunctionExpression.getAttributePaths()) {
if (functionExpression.contains(vpe.toString())) {
String expressionToCheckForSigns =
transformationFunctionExpression.getJepExpression().toString();
int startIndex = expressionToCheckForSigns.indexOf(vpe.toString());
int endIndex = startIndex + vpe.toString().length();
boolean castToFloat = false;
// check so that the String index will not get out of bounds
if (startIndex - 2 >= 0) {
if (math_sign.contains(expressionToCheckForSigns.charAt(startIndex - 2))) {
castToFloat = true;
}
// to catch the '>=' or '<=' case
else if (expressionToCheckForSigns.charAt(startIndex - 2) == '=' && startIndex - 3 >= 0) {
if (expressionToCheckForSigns.charAt(startIndex - 3) == '<'
|| expressionToCheckForSigns.charAt(startIndex - 3) == '>') {
castToFloat = true;
}
}
}
if (endIndex + 2 <= expressionToCheckForSigns.length()) {
if (math_sign.contains(expressionToCheckForSigns.charAt(endIndex + 1))) {
castToFloat = true;
}
}
String newAttrName;
// replace them with the corresponding attribute name
if (withRel) {
newAttrName = GenerateSQL.attributeNameWithVariable(vpe);
} else {
newAttrName = GenerateSQL.attributeNameInVariable(vpe);
}
// if previous or next character is one of {+,-,*,/,<,>} cast it to float
if (castToFloat) {
functionExpression =
functionExpression.replaceAll(vpe.toString(), "cast(" + newAttrName + " as float)");
} else {
functionExpression = functionExpression.replaceAll(vpe.toString(), newAttrName);
}
}
}
return replaceExpression(functionExpression);
}
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 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()));
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 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();
}
// 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 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();
}
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()));
if (i != allSelectionConditions.size() - 1) result.append(" AND\n");
}
result.append("\n");
return result.toString();
}