public String getFullQuotedName() { return table.getQuotedName() + '.' + quotedName; }
@Override public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns, Model model) { String indexedColumns = columns.stream().map(Column::getQuotedName).collect(Collectors.joining(", ")); return String.format("CREATE FULLTEXT INDEX %s ON %s (%s)", quotedIndexName, table.getQuotedName(), indexedColumns); }
public String getStatement() { StringBuilder buf = new StringBuilder(50); buf.append("DELETE FROM "); buf.append(table.getQuotedName()); if (where != null && where.length() != 0) { buf.append(" WHERE "); buf.append(where); } return buf.toString(); } }
@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 String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns, Model model) { return String.format( "CREATE INDEX %s ON %s(%s) INDEXTYPE IS CTXSYS.CONTEXT " + "PARAMETERS('%s SYNC (ON COMMIT) TRANSACTIONAL')", quotedIndexName, table.getQuotedName(), columns.get(0).getQuotedName(), fulltextParameters); }
@Override public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns, Model model) { StringBuilder buf = new StringBuilder(); buf.append(String.format("CREATE FULLTEXT INDEX ON %s (", table.getQuotedName())); Iterator<Column> it = columns.iterator(); while (it.hasNext()) { buf.append(String.format("%s LANGUAGE %s", it.next().getQuotedName(), getQuotedFulltextAnalyzer())); if (it.hasNext()) { buf.append(", "); } } String fulltextUniqueIndex = "[fulltext_pk]"; buf.append(String.format(") KEY INDEX %s ON [%s]", fulltextUniqueIndex, fulltextCatalog)); return buf.toString(); }
public String getStatement() { StringBuilder buf = new StringBuilder(128); buf.append("UPDATE "); buf.append(table.getQuotedName()); buf.append(" SET "); buf.append(newValues); if (from != null) { buf.append(" FROM "); if (table.getDialect().doesUpdateFromRepeatSelf()) { buf.append(table.getQuotedName()); buf.append(", "); } buf.append(StringUtils.join(from, ", ")); } if (where != null) { buf.append(" WHERE "); buf.append(where); } else { throw new IllegalArgumentException("unexpected empty WHERE"); } return buf.toString(); } }
@Override public List<String> getCustomPostCreateSqls(Table table, Model model) { if (!needsClusteredColumn(table)) { return Collections.emptyList(); } String quotedIndexName = getIndexName(table.getKey(), Collections.singletonList(CLUSTER_INDEX_COL)); String sql = String.format("CREATE UNIQUE CLUSTERED INDEX [%s] ON %s ([%s])", quotedIndexName, table.getQuotedName(), CLUSTER_INDEX_COL); return Collections.singletonList(sql); }
@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(); }
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())); }
public void removeLinksFor(String column, String entryId, SQLSession session) { String sql = String.format("DELETE FROM %s WHERE %s = ?", table.getQuotedName(), table.getColumn(column) .getQuotedName()); if (session.logger.isLogEnabled()) { session.logger.logSQL(sql, Collections.<Serializable> singleton(entryId)); } try (PreparedStatement ps = session.sqlConnection.prepareStatement(sql)) { ps.setString(1, entryId); ps.execute(); } catch (SQLException e) { throw new DirectoryException("error remove links to " + entryId, e); } }
public boolean exists(String sourceId, String targetId, SQLSession session) { // "SELECT COUNT(*) FROM %s WHERE %s = ? AND %s = ?", tableName, sourceColumn, targetColumn Select select = new Select(table); select.setFrom(table.getQuotedName()); select.setWhat("count(*)"); String whereString = String.format("%s = ? and %s = ?", table.getColumn(sourceColumn).getQuotedName(), table.getColumn(targetColumn).getQuotedName()); select.setWhere(whereString); String selectSql = select.getStatement(); if (session.logger.isLogEnabled()) { session.logger.logSQL(selectSql, Arrays.<Serializable> asList(sourceId, targetId)); } try (PreparedStatement ps = session.sqlConnection.prepareStatement(selectSql)) { ps.setString(1, sourceId); ps.setString(2, targetId); try (ResultSet rs = ps.executeQuery()) { rs.next(); return rs.getInt(1) > 0; } } catch (SQLException e) { throw new DirectoryException(String.format("error reading link from %s to %s", sourceId, targetId), e); } }
@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()); } }
protected List<String> getIdsFor(String valueColumn, String filterColumn, String filterValue) { try (SQLSession session = getSQLSession()) { // "SELECT %s FROM %s WHERE %s = ?", table.getColumn(valueColumn), tableName, filterColumn Select select = new Select(table); select.setWhat(table.getColumn(valueColumn).getQuotedName()); select.setFrom(table.getQuotedName()); select.setWhere(table.getColumn(filterColumn).getQuotedName() + " = ?"); String sql = select.getStatement(); if (session.logger.isLogEnabled()) { session.logger.logSQL(sql, Collections.<Serializable> singleton(filterValue)); } List<String> ids = new LinkedList<String>(); try (PreparedStatement ps = session.sqlConnection.prepareStatement(sql)) { ps.setString(1, filterValue); try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { ids.add(rs.getString(valueColumn)); } return ids; } } catch (SQLException e) { throw new DirectoryException("error fetching reference values: ", e); } } }
protected void postProcessCopy() { Collection<Column> columns = table.getColumns(); List<String> selectWhats = new ArrayList<>(columns.size()); Column copyIdColumn = table.getColumn(Model.MAIN_KEY); Insert insert = new Insert(table); for (Column column : columns) { if (column.isIdentity()) { // identity column is never copied continue; } insert.addColumn(column); if (column == copyIdColumn) { // explicit value selectWhats.add("?"); } else { // otherwise copy value selectWhats.add(column.getQuotedName()); } } Select select = new Select(table); select.setWhat(String.join(", ", selectWhats)); select.setFrom(table.getQuotedName()); select.setWhere(copyIdColumn.getQuotedName() + " = ?"); insert.setValues(select.getStatement()); copySqlMap.put(tableName, insert.getStatement()); copyIdColumnMap.put(tableName, copyIdColumn); }
protected void postProcessRootIdSelect() { String what = null; String where = null; for (Column column : table.getColumns()) { String key = column.getKey(); String qname = column.getQuotedName(); if (key.equals(Model.MAIN_KEY)) { what = qname; selectRootIdWhatColumn = column; } else if (key.equals(Model.REPOINFO_REPONAME_KEY)) { where = qname + " = ?"; } else { throw new RuntimeException(column.toString()); } } Select select = new Select(table); select.setWhat(what); select.setFrom(table.getQuotedName()); select.setWhere(where); selectRootIdSql = select.getStatement(); }
protected void postProcessSelectChildrenIdsAndTypes() { List<Column> whatColumns = new ArrayList<>(2); List<String> whats = new ArrayList<>(2); Column column = table.getColumn(Model.MAIN_KEY); whatColumns.add(column); whats.add(column.getQuotedName()); column = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY); whatColumns.add(column); whats.add(column.getQuotedName()); column = table.getColumn(Model.MAIN_MIXIN_TYPES_KEY); whatColumns.add(column); whats.add(column.getQuotedName()); Select select = new Select(table); select.setWhat(String.join(", ", whats)); select.setFrom(table.getQuotedName()); String where = table.getColumn(Model.HIER_PARENT_KEY).getQuotedName() + " = ?" + getSoftDeleteClause(tableName); select.setWhere(where); selectChildrenIdsAndTypesSql = select.getStatement(); selectChildrenIdsAndTypesWhatColumns = whatColumns; // now only complex properties where += " AND " + table.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY).getQuotedName() + " = " + dialect.toBooleanValueString(true); select.setWhere(where); selectComplexChildrenIdsAndTypesSql = select.getStatement(); }
@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 boolean hasEntry(String id) { acquireConnection(); Select select = new Select(table); select.setFrom(table.getQuotedName()); select.setWhat("1"); select.setWhere(table.getPrimaryColumn().getQuotedName() + " = ?"); String sql = select.getStatement(); if (logger.isLogEnabled()) { logger.logSQL(sql, Collections.singleton(id)); } try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) { setFieldValue(ps, 1, table.getPrimaryColumn(), id); try (ResultSet rs = ps.executeQuery()) { boolean has = rs.next(); if (logger.isLogEnabled()) { logger.logCount(has ? 1 : 0); } return has; } } catch (SQLException e) { throw new DirectoryException("hasEntry failed", e); } }
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); }