String ftColumnName = ftColumn.getFullQuotedName(); String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); FulltextMatchInfo info = new FulltextMatchInfo(); info.joins = new ArrayList<>(); if (nthMatch == 1) {
@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; }
String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); String tableAlias = "_nxfttbl" + nthSuffix; FulltextMatchInfo info = new FulltextMatchInfo();
@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 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; }