@Override
public void existingTableDetected(
Connection connection, Table table, Model model, Database database) throws SQLException {
if (Model.ANCESTORS_TABLE_NAME.equals(table.getKey())) {
if (!pathOptimizationsEnabled) {
log.info("Path optimizations disabled");
return;
}
// check if we want to initialize the descendants table now, or log
// a warning if the hierarchy table is too big
String sql = "SELECT id FROM ancestors LIMIT 1";
Statement s = connection.createStatement();
ResultSet rs = s.executeQuery(sql);
boolean empty = !rs.next();
rs.close();
s.close();
if (empty) {
pathOptimizationsEnabled = false;
log.error(
"Table ANCESTORS empty, must be upgraded by hand by calling: "
+ "SELECT nx_init_ancestors()");
log.info("Path optimizations disabled");
}
}
}
@Override
public boolean preCreateTable(Connection connection, Table table, Model model, Database database)
throws SQLException {
String tableKey = table.getKey();
if (model.HIER_TABLE_NAME.equals(tableKey)) {
hierarchyCreated = true;
return true;
}
if (model.ANCESTORS_TABLE_NAME.equals(tableKey)) {
if (hierarchyCreated) {
// database initialization
return true;
}
// upgrade of an existing database
// check hierarchy size
String sql = "SELECT COUNT(*) FROM hierarchy WHERE NOT isproperty";
Statement s = connection.createStatement();
ResultSet rs = s.executeQuery(sql);
rs.next();
long count = rs.getLong(1);
rs.close();
s.close();
if (count > 100000) {
// if the hierarchy table is too big, tell the admin to do the
// init by hand
pathOptimizationsEnabled = false;
log.error(
"Table ANCESTORS not initialized automatically because table HIERARCHY is too big. "
+ "Upgrade by hand by calling: SELECT nx_init_ancestors()");
}
return true;
}
return true;
}
@Override
public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
Map<String, Serializable> properties = new HashMap<String, Serializable>();
properties.put("idType", "varchar(36)");
properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled));
properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled));
properties.put("fulltextAnalyzer", fulltextAnalyzer);
properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
if (!fulltextDisabled) {
Table ft = database.getTable(model.FULLTEXT_TABLE_NAME);
properties.put("fulltextTable", ft.getQuotedName());
ModelFulltext fti = model.getFulltextInfo();
List<String> lines = new ArrayList<String>(fti.indexNames.size());
for (String indexName : fti.indexNames) {
String suffix = model.getFulltextIndexSuffix(indexName);
Column ftft = ft.getColumn(model.FULLTEXT_FULLTEXT_KEY + suffix);
Column ftst = ft.getColumn(model.FULLTEXT_SIMPLETEXT_KEY + suffix);
Column ftbt = ft.getColumn(model.FULLTEXT_BINARYTEXT_KEY + suffix);
String concat;
if (compatibilityFulltextTable) {
// tsvector
concat = " NEW.%s := COALESCE(NEW.%s, ''::TSVECTOR) || COALESCE(NEW.%s, ''::TSVECTOR);";
} else {
// text with space at beginning and end
concat = " NEW.%s := ' ' || COALESCE(NEW.%s, '') || ' ' || COALESCE(NEW.%s, '') || ' ';";
}
String line =
String.format(concat, ftft.getQuotedName(), ftst.getQuotedName(), ftbt.getQuotedName());
lines.add(line);
}
properties.put("fulltextTriggerStatements", StringUtils.join(lines, "\n"));
}
String[] permissions =
NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE);
List<String> permsList = new LinkedList<String>();
for (String perm : permissions) {
permsList.add("('" + perm + "')");
}
properties.put("readPermissions", StringUtils.join(permsList, ", "));
properties.put("usersSeparator", getUsersSeparator());
properties.put("everyone", SecurityConstants.EVERYONE);
properties.put("readAclMaxSize", Integer.toString(readAclMaxSize));
return properties;
}
@Override
public String getCreateFulltextIndexSql(
String indexName, String quotedIndexName, Table table, List<Column> columns, Model model) {
String sql;
if (compatibilityFulltextTable) {
sql = "CREATE INDEX %s ON %s USING GIN(%s)";
} else {
sql = "CREATE INDEX %s ON %s USING GIN(NX_TO_TSVECTOR(%s))";
}
return String.format(
sql, quotedIndexName.toLowerCase(), table.getQuotedName(), columns.get(0).getQuotedName());
}
@Override
public List<String> getPostCreateTableSqls(Table table, Model model, Database database) {
if (Model.ANCESTORS_TABLE_NAME.equals(table.getKey())) {
List<String> sqls = new ArrayList<String>();
if (pathOptimizationsEnabled) {
sqls.add("SELECT nx_init_ancestors()");
} else {
log.info("Path optimizations disabled");
}
return sqls;
}
return Collections.emptyList();
}
// SELECT ..., TS_RANK_CD(fulltext, nxquery, 32) as nxscore
// FROM ... LEFT JOIN fulltext ON fulltext.id = hierarchy.id
// , TO_TSQUERY('french', ?) as nxquery
// WHERE ...
// AND NX_TO_TSVECTOR(fulltext) @@ nxquery
// AND fulltext LIKE '% foo bar %' -- when phrase search
// ORDER BY nxscore DESC
@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 ftColumnName = ftColumn.getFullQuotedName();
String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
String queryAlias = "_nxquery" + nthSuffix;
FulltextMatchInfo info = new FulltextMatchInfo();
info.joins = new ArrayList<Join>();
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()));
}
/*
* for phrase search, fulltextQuery may contain a LIKE part
*/
String like;
if (fulltextQuery.contains(FT_LIKE_SEP)) {
String[] tmp = fulltextQuery.split(FT_LIKE_SEP, 2);
fulltextQuery = tmp[0];
like = tmp[1].replace(FT_LIKE_COL, ftColumnName);
} else {
like = null;
}
info.joins.add(
new Join(
Join.IMPLICIT, //
String.format("TO_TSQUERY('%s', ?)", fulltextAnalyzer),
queryAlias, // alias
fulltextQuery, // param
null,
null));
String tsvector;
if (compatibilityFulltextTable) {
tsvector = ftColumnName;
} else {
tsvector = String.format("NX_TO_TSVECTOR(%s)", ftColumnName);
}
String where = String.format("(%s @@ %s)", queryAlias, tsvector);
if (like != null) {
where += " AND (" + like + ")";
}
info.whereExpr = where;
info.scoreExpr = String.format("TS_RANK_CD(%s, %s, 32)", tsvector, queryAlias);
info.scoreAlias = "_nxscore" + nthSuffix;
info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
return info;
}