@Override public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam) { returnParam[0] = mixin; String sql = "ARRAY[?]::varchar[] <@ " + mixinsColumn.getFullQuotedName(); return positive ? sql : "NOT(" + sql + ")"; }
protected String getSelectColName(Column col) { return col.getFullQuotedName(); }
/** * Gets the SQL fragment to match a mixin type. */ public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam) { returnParam[0] = "%" + ARRAY_SEP + mixin + ARRAY_SEP + "%"; return String.format("%s %s ?", mixinsColumn.getFullQuotedName(), positive ? "LIKE" : "NOT LIKE"); }
protected void visitExpressionStartsWithPath(String path) { // find the id from the path Serializable id = pathResolver.getIdForPath(path); if (id == null) { // no such path, always return a false // TODO remove the expression more intelligently from the parse // tree buf.append("0=1"); } else { // id is always valid, no need to pass it as argument to getInTreeSql buf.append(dialect.getInTreeSql(hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), null)); whereParams.add(id); } }
/** * Returns null or {@code AND isdeleted IS NULL} if soft delete is activated. * * @return the clause, or null */ public String getSoftDeleteClause() { if (softDeleteEnabled) { return database.getTable(Model.HIER_TABLE_NAME).getColumn(Model.MAIN_IS_DELETED_KEY).getFullQuotedName() + " IS NULL"; } else { return null; } }
public String checkJdbcType(int actual, String actualName, int actualSize) { int expected = jdbcType; if (actual == expected) { return null; } if (dialect.isAllowedConversion(expected, actual, actualName, actualSize)) { return null; } return String.format("SQL type mismatch for %s: expected %s, database has %s / %s(%s)", getFullQuotedName(), getJDBCTypeName(expected), getJDBCTypeName(actual), actualName, actualSize); }
/** * Gets the arraySubquery for the given arrayColumn in the given contextKey, and maybe adds a JOIN if one is not * already done. * <p> * LEFT JOIN (SELECT id, UNNEST(somecol) AS item, generate_subscripts(somecol, 1) AS pos FROM someschema) _A1 ON * _A1.id = hierarchy.id */ protected ArraySubQuery getArraySubQuery(Table contextHier, String contextKey, Column arrayColumn, boolean skipJoin) { ArraySubQuery arraySubQuery = propertyArraySubQueries.get(contextKey); if (arraySubQuery == null) { String alias = SUBQUERY_ARRAY_ALIAS + ++arraySubQueryJoinCount; arraySubQuery = dialect.getArraySubQuery(arrayColumn, alias); propertyArraySubQueries.put(contextKey, arraySubQuery); if (!skipJoin) { Join join = new Join(Join.LEFT, arraySubQuery.toSql(), alias, null, arraySubQuery.getSubQueryIdColumn().getFullQuotedName(), contextHier.getColumn(Model.MAIN_KEY).getFullQuotedName()); joins.add(join); } } return arraySubQuery; }
protected void generateExistsStart(StringBuilder buf, Table table) { String tableName; if (table.isAlias()) { tableName = table.getRealTable().getQuotedName() + " " + table.getQuotedName(); } else { tableName = table.getQuotedName(); } buf.append(String.format("EXISTS (SELECT 1 FROM %s WHERE %s = %s AND ", tableName, dataHierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), table.getColumn(Model.MAIN_KEY).getFullQuotedName())); }
protected void visitExpressionAncestorId(Expression node) { if (node.operator != Operator.EQ && node.operator != Operator.NOTEQ) { throw new QueryParseException(NXQL.ECM_ANCESTORID + " requires = or <> operator"); } if (!(node.rvalue instanceof StringLiteral)) { throw new QueryParseException(NXQL.ECM_ANCESTORID + " requires literal id as right argument"); } boolean not = node.operator == Operator.NOTEQ; String id = ((StringLiteral) node.rvalue).value; if (not) { buf.append("(NOT ("); } String sql = dialect.getInTreeSql(hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), id); if (sql == null) { buf.append("0=1"); } else { buf.append(sql); whereParams.add(id); } if (not) { buf.append("))"); } }
if (nthMatch == 1) { info.joins.add(new Join(Join.LEFT, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(), mainColumn.getFullQuotedName())); ftMain.getFullQuotedName(), // on1 String.format("%s.[KEY]", tableAlias) // on2 ));
@Override public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, Column mainColumn, Model model, Database database) { String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); String indexSuffix = model.getFulltextIndexSuffix(indexName); Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME); Column ftMain = ft.getColumn(Model.MAIN_KEY); Column stColumn = ft.getColumn(Model.FULLTEXT_SIMPLETEXT_KEY + indexSuffix); Column btColumn = ft.getColumn(Model.FULLTEXT_BINARYTEXT_KEY + indexSuffix); String match = String.format("MATCH (%s, %s)", stColumn.getFullQuotedName(), btColumn.getFullQuotedName()); FulltextMatchInfo info = new FulltextMatchInfo(); if (nthMatch == 1) { // Need only one JOIN involving the fulltext table info.joins = Collections.singletonList(new Join(Join.INNER, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(), mainColumn.getFullQuotedName())); } info.whereExpr = String.format("%s AGAINST (? IN BOOLEAN MODE)", match); info.whereExprParam = fulltextQuery; // Note: using the boolean query in non-boolean mode gives approximate // results but it's the best we have as MySQL does not provide a score // in boolean mode. // Note: dividing by 10 is arbitrary, but MySQL cannot really // normalize scores. info.scoreExpr = String.format("(%s AGAINST (?) / 10)", match); info.scoreExprParam = fulltextQuery; info.scoreAlias = "_nxscore" + nthSuffix; info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); return info; }
@Override public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, Column mainColumn, Model model, Database database) { // TODO multiple indexes Table ft = database.getTable(model.FULLTEXT_TABLE_NAME); Column ftMain = ft.getColumn(model.MAIN_KEY); Column ftColumn = ft.getColumn(model.FULLTEXT_FULLTEXT_KEY); String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); String ftColumnName = ftColumn.getFullQuotedName(); if (ftColumn.getJdbcType() == Types.CLOB) { String colFmt = getClobCast(false); if (colFmt != null) { ftColumnName = String.format(colFmt, ftColumnName, Integer.valueOf(255)); } } FulltextMatchInfo info = new FulltextMatchInfo(); info.joins = new ArrayList<Join>(1); if (nthMatch == 1) { // Need only one JOIN involving the fulltext table info.joins.add(new Join(Join.INNER, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(), mainColumn.getFullQuotedName())); } info.whereExpr = String.format("NX_CONTAINS(%s, ?) = 1", ftColumnName); info.whereExprParam = fulltextQuery; info.scoreExpr = "1"; info.scoreAlias = "NXSCORE" + nthSuffix; info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); return info; }
@Override public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, Column mainColumn, Model model, Database database) { String indexSuffix = model.getFulltextIndexSuffix(indexName); Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME); Column ftMain = ft.getColumn(Model.MAIN_KEY); Column ftColumn = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + indexSuffix); String score = String.format("SCORE(%d)", nthMatch); String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); FulltextMatchInfo info = new FulltextMatchInfo(); if (nthMatch == 1) { // Need only one JOIN involving the fulltext table info.joins = Collections.singletonList(new Join(Join.INNER, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(), mainColumn.getFullQuotedName())); } info.whereExpr = String.format("CONTAINS(%s, ?, %d) > 0", ftColumn.getFullQuotedName(), nthMatch); info.whereExprParam = fulltextQuery; info.scoreExpr = String.format("(%s / 100)", score); info.scoreAlias = openQuote() + "_nxscore" + nthSuffix + closeQuote(); info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); return info; }
protected void addJoin(int kind, String alias, Table table, String column, Table contextTable, String contextColumn, String name, int index, String primaryType) { Column column1 = contextTable.getColumn(contextColumn); Column column2 = table.getColumn(column); Join join = new Join(kind, table.getRealTable().getQuotedName(), alias, null, column1, column2); if (name != null) { String nameCol = table.getColumn(Model.HIER_CHILD_NAME_KEY).getFullQuotedName(); join.addWhereClause(nameCol + " = ?", name); } if (index != -1) { String posCol = table.getColumn(Model.HIER_CHILD_POS_KEY).getFullQuotedName(); join.addWhereClause(posCol + " = ?", Long.valueOf(index)); } if (primaryType != null) { String typeCol = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getFullQuotedName(); join.addWhereClause(typeCol + " = ?", primaryType); } joins.add(join); }
public String getClause(Dialect dialect) { if (on1 == null && on2 == null) { on1 = column1.getFullQuotedName(); on2 = column2.getFullQuotedName(); boolean isid1 = column1.getType().isId(); boolean isid2 = column2.getType().isId(); if (dialect != null && isid1 != isid2) { // temporary fix cast uuid to varchar because relation table // has varchar source and target field if (isid1) { on1 = dialect.castIdToVarchar(on1); } else { on2 = dialect.castIdToVarchar(on2); } } } return on1 + " = " + on2; }
@Override public void visitFunction(Function node) { String func = node.name.toUpperCase(); Reference ref = (Reference) node.args.get(0); ref.accept(this); // whatColumns / whatKeys for column // replace column info with aggregate Column col = whatColumns.removeLast(); String key = whatKeys.removeLast(); final String aggFQN = func + "(" + col.getFullQuotedName() + ")"; final ColumnType aggType = getAggregateType(func, col.getType()); final int aggJdbcType = dialect.getJDBCTypeAndString(aggType).jdbcType; Column cc = new Column(col, col.getTable()) { private static final long serialVersionUID = 1L; @Override public String getFullQuotedName() { return aggFQN; } @Override public ColumnType getType() { return aggType; } @Override public int getJdbcType() { return aggJdbcType; } }; whatColumns.add(cc); whatKeys.add(func + "(" + key + ")"); }
@Override public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, Column mainColumn, Model model, Database database) { String phftname = database.getTable(Model.FULLTEXT_TABLE_NAME).getPhysicalName(); String fullIndexName = "PUBLIC_" + phftname + "_" + indexName; String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); String tableAlias = "_NXFTTBL" + nthSuffix; String quotedTableAlias = openQuote() + tableAlias + closeQuote(); FulltextMatchInfo info = new FulltextMatchInfo(); info.joins = Collections.singletonList( // new Join(Join.LEFT, // String.format("NXFT_SEARCH('%s', ?)", fullIndexName), tableAlias, // alias fulltextQuery, // param String.format("%s.KEY", quotedTableAlias), // on1 mainColumn.getFullQuotedName() // on2 )); info.whereExpr = String.format("%s.KEY IS NOT NULL", quotedTableAlias); info.scoreExpr = "1"; info.scoreAlias = "_NXSCORE" + nthSuffix; info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); return info; }
@Override protected void fixSelect(Select select) { if (type == COUNT_SOURCE) { // add a GROUP BY on first col String name; if (dialect.needsOriginalColumnInGroupBy()) { name = firstSelectedColumn.getFullQuotedName(); } else { name = dialect.openQuote() + COL_ALIAS_PREFIX + "1" + dialect.closeQuote(); } select.setGroupBy(name); } }
protected void visitReference(Column column, String cast, int arrayElementIndex) { if (DATE_CAST.equals(cast) && column.getType() != ColumnType.TIMESTAMP) { throw new QueryParseException("Cannot cast to " + cast + ": " + column); } String qname = column.getFullQuotedName(); if (arrayElementIndex != -1) { if (column.isArray()) { qname = dialect.getArrayElementString(qname, arrayElementIndex); } else { throw new QueryParseException( "Cannot use array index " + arrayElementIndex + " for non-array column " + column); } } // some databases (Derby) can't do comparisons on CLOB if (column.getJdbcType() == Types.CLOB) { String colFmt = dialect.getClobCast(inOrderBy); if (colFmt != null) { qname = String.format(colFmt, qname, Integer.valueOf(255)); } } if (cast != null) { // only DATE cast for now String fmt = dialect.getDateCast(); buf.append(String.format(fmt, qname)); } else { buf.append(qname); } }
protected String getArrayOpSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable, String op) { Table table = arrayColumn.getTable(); String tableAliasName = openQuote() + getTableName(refName) + closeQuote(); String sql = String.format("EXISTS (SELECT 1 FROM %s AS %s WHERE %s = %s AND %s %s ?)", getArraySubQuery(arrayColumn, tableAliasName).toSql(), tableAliasName, dataHierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), tableAliasName + '.' + table.getColumn(Model.MAIN_KEY).getQuotedName(), tableAliasName + '.' + Model.COLL_TABLE_VALUE_KEY, op); if (!positive) { sql = "NOT(" + sql + ")"; } return sql; }