public class GenerateSQLVisitor implements SqlNodeVisitor { // Annotate should ensureEndofLine ? private static Logger log = LoggerFactory.getLogger(GenerateSQLVisitor.class); protected IndentedWriter out; int levelSelectBlock = 0; // Per Generator public boolean outputAnnotations = SDB.getContext().isTrueOrUndef(SDB.annotateGeneratedSQL); private static final int annotationColumn = 40; private static boolean commentSQLStyle = true; public GenerateSQLVisitor(IndentedWriter out) { this.out = out; } public void visit(SqlProject sqlNode) { shouldNotSee(sqlNode); } public void visit(SqlDistinct sqlNode) { shouldNotSee(sqlNode); } public void visit(SqlRestrict sqlNode) { shouldNotSee(sqlNode); } public void visit(SqlSlice sqlNode) { shouldNotSee(sqlNode); } public void visit(SqlRename sqlNode) { shouldNotSee(sqlNode); } private void shouldNotSee(SqlNode sqlNode) { throw new SDBInternalError("Didn't expect: " + Lib.className(sqlNode)); } // If nested (subquery) @Override public void visit(SqlSelectBlock sqlSelectBlock) { // Need a rename and alias if: // Not top // Not merely a table inside. levelSelectBlock++; if (levelSelectBlock > 1) { // Alias needed. // SqlRename rename = SqlRename.view("X", sqlSelectBlock) ; // rename.visit(this) ; // levelSelectBlock-- ; // return ; } genPrefix(sqlSelectBlock); out.print("SELECT "); if (sqlSelectBlock.getDistinct()) out.print("DISTINCT "); if (annotate(sqlSelectBlock)) out.ensureStartOfLine(); out.incIndent(); genColumnPrefix(sqlSelectBlock); print(sqlSelectBlock.getCols()); out.decIndent(); out.ensureStartOfLine(); // FROM out.print("FROM"); if (!sqlSelectBlock.getSubNode().isTable()) out.println(); else out.print(" "); out.incIndent(); outputNode(sqlSelectBlock.getSubNode(), true); // sqlSelectBlock.getSubNode().visit(this) ; out.decIndent(); out.ensureStartOfLine(); // WHERE if (sqlSelectBlock.getConditions().size() > 0) genWHERE(sqlSelectBlock.getConditions()); // LIMIT/OFFSET out.ensureStartOfLine(); genLimitOffset(sqlSelectBlock); genSuffix(sqlSelectBlock); levelSelectBlock--; } /** Generate anything that needs to appear before the core SELECT. */ protected void genPrefix(SqlSelectBlock sqlSelectBlock) { // By default, NOP. } /** Generate any additional columns. */ protected void genColumnPrefix(SqlSelectBlock sqlSelectBlock) { // By default, NOP. } /** Generate anything that needs to appear after the core SELECT. */ protected void genSuffix(SqlSelectBlock sqlSelectBlock) { // By default, NOP. } protected void genLimitOffset(SqlSelectBlock sqlSelectBlock) { if (sqlSelectBlock.getLength() >= 0) out.println("LIMIT " + sqlSelectBlock.getLength()); if (sqlSelectBlock.getStart() >= 0) out.println("OFFSET " + sqlSelectBlock.getStart()); } private void print(List<ColAlias> cols) { String sep = ""; if (cols.size() == 0) { // Can happen - e.g. query with no variables. // log.info("No SELECT columns") ; out.print("*"); } // Put common prefix on same line String currentPrefix = null; String splitMarker = "."; for (ColAlias c : cols) { out.print(sep); // Choose split points. String cn = c.getColumn().getFullColumnName(); int j = cn.lastIndexOf(splitMarker); if (j == -1) currentPrefix = null; else { String x = cn.substring(0, j); if (currentPrefix != null && !x.equals(currentPrefix)) out.println(); currentPrefix = x; } sep = ", "; out.print(c.getColumn().getFullColumnName()); if (c.getAlias() != null) { out.print(aliasToken()); out.print(c.getAlias().getColumnName()); } } } private void genWHERE(SqlExprList conditions) { out.print("WHERE"); out.print(" "); out.incIndent(); conditionList(conditions); out.decIndent(); } @Override public void visit(SqlTable table) { out.print(table.getTableName()); out.print(aliasToken()); out.print(table.getAliasName()); annotate(table); } @Override public void visit(SqlJoinInner join) { join = rewrite(join); visitJoin(join); } public SqlJoinInner rewrite(SqlJoinInner join) { if (!join.getRight().isInnerJoin()) return join; // if ( join(A, join(B, C)) ) rewrite as join(join(A,B),C) // this then is written without brackets (and so scope changing) // TODO abstract as organiseJoin(List<join elements>) // and remember to do top down to find maximal join trees SqlJoinInner right = join.getRight().asInnerJoin(); String alias1 = join.getAliasName(); String alias2 = right.getAliasName(); SqlNode sn_a = join.getLeft(); SqlNode sn_b = right.getLeft(); SqlNode sn_c = right.getRight(); SqlExprList conditions = new SqlExprList(join.getConditions()); conditions.addAll(right.getConditions()); Set<SqlTable> tables_ab = sn_a.tablesInvolved(); tables_ab.addAll(sn_b.tablesInvolved()); SqlExprList newCond_ab = new SqlExprList(); // Goes to new join(A,B) SqlExprList newCond_c = new SqlExprList(); // Goes to new join(,C) // Place conditions for (SqlExpr e : conditions) { Set<SqlColumn> cols = e.getColumnsNeeded(); // columns to tables. Set<SqlTable> tables = tables(cols); // Are the tables contained in tables_ab? tables.removeAll(tables_ab); if (tables.size() == 0) newCond_ab.add(e); else newCond_c.add(e); } if (newCond_ab.size() + newCond_c.size() != conditions.size()) log.error( String.format( "Conditions mismatch: (%d,%d,%d)", newCond_ab.size(), newCond_c.size(), conditions.size())); SqlJoinInner join2 = new SqlJoinInner(sn_a, sn_b); join2.addConditions(newCond_ab); join2 = new SqlJoinInner(join2, sn_c); join2.addConditions(newCond_c); return join2; } private static Set<SqlTable> tables(Set<SqlColumn> cols) { return Iter.toSet(Iter.map(cols.iterator(), SqlColumn::getTable)); } @Override public void visit(SqlJoinLeftOuter join) { visitJoin(join); } @Override public void visit(SqlCoalesce sqlNode) { out.print("SELECT "); boolean first = true; SqlJoin join = sqlNode.getJoinNode(); // Rough draft code. for (Var v : sqlNode.getCoalesceVars()) { if (!first) out.print(", "); SqlColumn col = sqlNode.getIdScope().findScopeForVar(v).getColumn(); SqlColumn leftCol = join.getLeft().getIdScope().findScopeForVar(v).getColumn(); SqlColumn rightCol = join.getRight().getIdScope().findScopeForVar(v).getColumn(); out.print("COALESCE("); out.print(leftCol.getFullColumnName()); out.print(", "); out.print(rightCol.getFullColumnName()); out.print(")"); out.print(aliasToken()); out.print(col.getColumnName()); first = false; } // And other vars we want. for (Var v : sqlNode.getNonCoalesceVars()) { if (!first) out.print(", "); first = false; // Need generated names. SqlColumn colSub = join.getIdScope().findScopeForVar(v).getColumn(); SqlColumn col = sqlNode.getIdScope().findScopeForVar(v).getColumn(); out.print(colSub.getFullColumnName()); out.print(aliasToken()); out.print(col.getColumnName()); } out.ensureStartOfLine(); out.incIndent(); // INC out.println("FROM"); join.visit(this); out.ensureStartOfLine(); // Alias and annotations handled by outputNode } @Override public void visit(SqlUnion sqlUnion) { throw new SDBNotImplemented("SQL generation of SqlUnion"); } protected void visitJoin(SqlJoin join) { visitJoin(join, join.getJoinType().sqlOperator()); } protected void visitJoin(SqlJoin join, String joinOperatorName) { // TODO revisit this code. Is it now needless complex? // Check brackets for more general SQL generation (safe mode - i.e. always bracketted?) SqlNode left = join.getLeft(); SqlNode right = join.getRight(); // Appearance: stop nesting too much. // Can we linearise the format? (drop indentation) if (left.isJoin() && left.getAliasName() == null) outputNode(left, false); else { out.incIndent(); outputNode(left, true); out.decIndent(); } out.println(); // out.print(" ") ; out.print(joinOperatorName); annotate(join); out.println(); // Aliasing and scoping - may need sub-SELECT - or just don't generate // such SqlNode structures, leaving only COALESCE as the sub-SELECT case boolean bracketsRight = true; // if ( right.isInnerJoin() && join.isInnerJoin() && no conditions ) // bracketsRight = false ; if (bracketsRight) // Why? out.incIndent(); outputNode(right, bracketsRight); if (bracketsRight) out.decIndent(); out.println(); out.print("ON "); if (join.getConditions().size() > 0) conditionList(join.getConditions()); else { out.print(" ( "); out.print(leftJoinNoConditionsString()); out.print(" )"); } } // -------- Extension points for various SQL differences protected String aliasToken() { return " AS "; } protected String leftJoinNoConditionsString() { return "1 = 1"; } // -------- // Interaction with annotations static boolean allOnOneLine = false; public void conditionList(SqlExprList conditions) { if (conditions.size() == 0) return; out.print("( "); String sep = " AND "; boolean first = true; boolean lastAnnotated = false; for (SqlExpr c : conditions) { if (!first) { if (!allOnOneLine) out.println(); out.print(sep); } boolean needsParens = !(c instanceof S_Equal); // TODO Interact with SqlExpr precedence printing if (needsParens) out.print("( "); out.print(c.asSQL()); if (needsParens) out.print(" )"); if (!allOnOneLine) lastAnnotated = annotate(c); first = false; } if (!allOnOneLine && lastAnnotated) out.println(""); out.print(" )"); first = true; if (allOnOneLine) { for (SqlExpr c : conditions) { if (c.hasNotes()) { if (!first) out.println(); annotate(c); first = false; } } } } private void outputNode(SqlNode sqlNode, boolean mayNeedBrackets) { if (sqlNode.isTable()) { sqlNode.visit(this); return; } // boolean brackets = ( mayNeedBrackets && ( sqlNode.isSelectBlock() || sqlNode.isCoalesce() ) ) // ; boolean brackets = false; brackets = brackets || (mayNeedBrackets && sqlNode.isCoalesce()); // Work harder? ready for a better test. brackets = brackets || (mayNeedBrackets && sqlNode.isSelectBlock()); // Need brackets if the subpart is a SELECT if (brackets) { out.print("( "); out.incIndent(); } sqlNode.visit(this); if (brackets) { out.decIndent(); out.ensureStartOfLine(); out.print(")"); } // Every derived table (SELECT ...) must have an alias. // Is there a more principled way to do this? .isDerived? // if ( sqlNode.isRestrict() || sqlNode.isProject()) // out.print(+sqlNode.getAliasName()) ; if (sqlNode.getAliasName() != null) { out.print(aliasToken()); out.print(sqlNode.getAliasName()); } annotate(sqlNode); } private boolean annotate(Annotations sqlNode) { return annotate(sqlNode, annotationColumn); } // return true if annotation was output and it runs to end-of-line private boolean annotate(Annotations sqlNode, int indentationColumn) { if (!outputAnnotations) return false; boolean first = true; for (String s : sqlNode.getNotes()) { if (!first) out.println(); first = false; out.pad(indentationColumn, true); if (commentSQLStyle) { out.print(" -- "); out.print(s); } else { out.print(" /* "); out.print(s); out.print(" */"); } } return !commentSQLStyle || !first; } }