@Override public String getIdentityGeneratedKeySql(Column column) { String table = column.getTable().getPhysicalName(); String seq = table + "_IDSEQ"; return String.format("SELECT \"%s\".CURRVAL FROM DUAL", seq); }
@Override protected void fixWhatColumns(List<Column> whatColumns) { if (type == COUNT_SOURCE) { // 2nd col is a COUNT -> different type Column targetCol = whatColumns.remove(1); Column countCol = new Column(targetCol.getTable(), null, ColumnType.INTEGER, null); whatColumns.add(countCol); } }
@Override public List<String> getPostCreateIdentityColumnSql(Column column) { String table = column.getTable().getPhysicalName(); String col = column.getPhysicalName(); String seq = table + "_IDSEQ"; String trig = table + "_IDTRIG"; String createSeq = String.format("CREATE SEQUENCE \"%s\"", seq); String createTrig = String.format("CREATE TRIGGER \"%s\"\n" // + " BEFORE INSERT ON \"%s\"\n" // + " FOR EACH ROW WHEN (NEW.\"%s\" IS NULL)\n" // + "BEGIN\n" // + " SELECT \"%s\".NEXTVAL INTO :NEW.\"%s\" FROM DUAL;\n" // + "END;", trig, table, col, seq, col); return Arrays.asList(createSeq, createTrig); }
@Override public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, List<Serializable> outValues) { Column keyColumn = columns.get(0); Table table = keyColumn.getTable(); StringBuilder sql = new StringBuilder(); sql.append("MERGE INTO "); sql.append(table.getQuotedName()); sql.append(" KEY ("); sql.append(keyColumn.getQuotedName()); sql.append(") VALUES ("); for (int i = 0; i < columns.size(); i++) { if (i != 0) { sql.append(", "); } sql.append("?"); outColumns.add(columns.get(i)); outValues.add(values.get(i)); } sql.append(")"); return sql.toString(); }
@Override protected String getSelectColName(Column col) { String name = super.getSelectColName(col); if (firstSelectedColumn == null) { firstSelectedColumn = col; } if (type == COUNT_SOURCE && col.getTable().getKey().equals(SCHEMA_RELATION) && col.getKey().equals(PROPERTY_SOURCE)) { name = String.format("COUNT(DISTINCT %s)", name); } return name; }
List<Serializable> outValues) { Column keyColumn = columns.get(0); Table table = keyColumn.getTable(); StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO ");
List<Serializable> outValues) { Column keyColumn = columns.get(0); Table table = keyColumn.getTable(); StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO ");
Column keyColumn = columns.get(0); Serializable keyValue = values.get(0); Table table = keyColumn.getTable(); StringBuilder sql = new StringBuilder(); sql.append("MERGE INTO ");
List<Serializable> outValues) { Column keyColumn = columns.get(0); Table table = keyColumn.getTable(); StringBuilder sql = new StringBuilder(); sql.append("MERGE ");
@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 public String toSql() { Table table = arrayColumn.getTable(); return String.format("(SELECT %s, UNNEST(%s) AS %s, generate_subscripts(%s, 1) AS %s FROM %s) ", table.getColumn(Model.MAIN_KEY).getQuotedName(), arrayColumn.getQuotedName(), Model.COLL_TABLE_VALUE_KEY, arrayColumn.getQuotedName(), Model.COLL_TABLE_POS_KEY, table.getRealTable().getQuotedName()); } }
info.scoreExpr = String.format("(%s.RANK / 1000.0)", tableAlias); info.scoreAlias = "_nxscore" + nthSuffix; info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); return info;
info.scoreExprParam = fulltextQuery; info.scoreAlias = "_nxscore" + nthSuffix; info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); return info;
protected void visitExpressionStartsWithNonPath(Expression node, String path) { String name = ((Reference) node.lvalue).name; ColumnInfo info = getColumnInfo(name); if (info.needsSubSelect) { // use EXISTS with subselect clause generateExistsStart(buf, info.column.getTable()); } buf.append('('); visitExpressionEqOrIn(info.column, Operator.EQ, new StringLiteral(path), null, -1); visitOperator(Operator.OR); // TODO escape % chars... visitExpressionLike(info.column, Operator.LIKE, new StringLiteral(path + PATH_SEP + '%'), name, -1); buf.append(')'); if (info.needsSubSelect) { generateExistsEnd(buf); } }
@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; }
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; }