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); }
/** * Does not return the WHERE clause. * <p> * {@inheritDoc} */ public String toSql(Dialect dialect) { switch (kind) { case INNER: return String.format(" JOIN %s ON %s", getTable(dialect), getClause(dialect)); case LEFT: return String.format(" LEFT JOIN %s ON %s", getTable(dialect), getClause(dialect)); case RIGHT: return String.format(" RIGHT JOIN %s ON %s", getTable(dialect), getClause(dialect)); case IMPLICIT: return String.format(", %s", getTable(dialect)); default: throw new AssertionError(); } }
securityJoins.add(new Join(Join.INNER, Model.HIER_READ_ACL_TABLE_NAME, READ_ACL_ALIAS, null, id, racl + '.' + Model.HIER_READ_ACL_ID)); securityJoins.add(new Join(Join.INNER, Model.ACLR_USER_MAP_TABLE_NAME, READ_ACL_USER_MAP_ALIAS, null, racl + '.' + Model.HIER_READ_ACL_ACL_ID, aclrum + '.' + Model.ACLR_USER_MAP_ACL_ID)); securityClause = dialect.getReadAclsCheckSql(aclrum + '.' + Model.ACLR_USER_MAP_USER_ID); String withFrom = withTable; for (Join j : securityJoins) { withFrom += j.toSql(dialect); for (Join join : joins) { fromb.append(", "); fromb.append(join.getTable(dialect)); if (join.tableParam != null) { selectParams.add(join.tableParam); String joinClause = join.getClause(dialect); if (join.kind == Join.LEFT) { joinClause += "(+)"; // Oracle implicit LEFT JOIN syntax selectParams.add(join.tableParam); String joinClause = join.toSql(dialect);
/** * Select selection ids for multiple values. */ public SQLInfoSelect getSelectSelectionIds(int nids) { Table table = database.getTable(type.tableName); String from = table.getQuotedName(); Table hierTable = database.getTable(Model.HIER_TABLE_NAME); Join join = new Join(Join.INNER, hierTable.getQuotedName(), null, null, hierTable.getColumn(Model.MAIN_KEY), table.getColumn(Model.MAIN_KEY)); from += join.toSql(dialect); Column whatColumn = table.getColumn(Model.MAIN_KEY); Column whereColumn = table.getColumn(type.selKey); StringBuilder wherebuf = new StringBuilder(whereColumn.getQuotedName()); wherebuf.append(" IN ("); for (int i = 0; i < nids; i++) { if (i != 0) { wherebuf.append(", "); } wherebuf.append('?'); } wherebuf.append(')'); wherebuf.append(getSoftDeleteClause(Model.HIER_TABLE_NAME)); Select select = new Select(table); select.setWhat(whatColumn.getFullQuotedName()); select.setFrom(from); select.setWhere(wherebuf.toString()); return new SQLInfoSelect(select.getStatement(), Collections.singletonList(whatColumn), Collections.singletonList(whereColumn), null); } }
buf.append(getClause(null)); if (!whereClauses.isEmpty()) { buf.append(" WHERE ");
public SQLInfoSelection(SelectionType selType) { this.type = selType; Table table = database.getTable(selType.tableName); SQLInfoSelect selectAll; SQLInfoSelect selectFiltered; String from = table.getQuotedName(); List<String> clauses; if (selType.tableName.equals(Model.HIER_TABLE_NAME)) { // clause already added by makeSelect clauses = null; } else { Table hierTable = database.getTable(Model.HIER_TABLE_NAME); Join join = new Join(Join.INNER, hierTable.getQuotedName(), null, null, hierTable.getColumn(Model.MAIN_KEY), table.getColumn(Model.MAIN_KEY)); from += join.toSql(dialect); String clause = getSoftDeleteClause(); clauses = clause == null ? null : Collections.singletonList(clause); } if (selType.criterionKey == null) { selectAll = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey); selectFiltered = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.filterKey); } else { selectAll = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.criterionKey); selectFiltered = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.filterKey, selType.criterionKey); } this.selectAll = selectAll; this.selectFiltered = selectFiltered; }
/** * 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; }
@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; }
if (nthMatch == 1) { info.joins.add(new Join(Join.INNER, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(), mainColumn.getFullQuotedName()));
@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; }
@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; }