public static JdbcTimestampBoundStore create(final JdbcKeyValueService kvs) { final JdbcTimestampBoundStore store = new JdbcTimestampBoundStore(kvs); kvs.run((Function<DSLContext, Void>) ctx -> { String partialSql = ctx.createTable(store.TABLE) .column(DUMMY_COLUMN, INTEGER.nullable(false)) .column(LATEST_TIMESTAMP, BIGINT.nullable(false)) .getSQL(); int endIndex = partialSql.lastIndexOf(')'); String fullSql = partialSql.substring(0, endIndex) + "," + " CONSTRAINT " + kvs.primaryKey(TIMESTAMP_TABLE) + " PRIMARY KEY (" + DUMMY_COLUMN.getName() + ")" + partialSql.substring(endIndex); try { ctx.execute(fullSql); } catch (DataAccessException e) { kvs.handleTableCreationException(e); } ctx.insertInto(store.TABLE, DUMMY_COLUMN, LATEST_TIMESTAMP) .select(ctx.select(DUMMY_COLUMN, LATEST_TIMESTAMP) .from(kvs.values(ctx, new RowN[] {(RowN) DSL.row(0, 10000L)}, "t", DUMMY_COLUMN.getName(), LATEST_TIMESTAMP.getName())) .whereNotExists(ctx.selectOne() .from(store.TABLE) .where(DUMMY_COLUMN.eq(0)))) .execute(); return null; }); return store; }
private Select<? extends Record> getAllTimestampsQuerySomeColumns(DSLContext ctx, TableReference tableRef, Select<Record1<byte[]>> subQuery, Collection<byte[]> cols, long timestamp) { return ctx.select(A_ROW_NAME, A_COL_NAME, A_TIMESTAMP) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .where(A_ROW_NAME.in(subQuery) .and(A_COL_NAME.in(cols))) .and(A_TIMESTAMP.lessThan(timestamp)); }
result = leftField.eq(rightField); break; case ge: result = leftField.ge(rightField); break; case gt: result = leftField.gt(rightField); break; case le: result = leftField.le(rightField); break; case lt: result = leftField.lt(rightField); break; case ne: result = leftField.ne(rightField); break; default:
private Select<? extends Record> getLatestTimestampQuerySomeColumns(DSLContext ctx, TableReference tableRef, Collection<byte[]> rows, Collection<byte[]> cols, long timestamp) { return ctx.select(A_ROW_NAME, A_COL_NAME, DSL.max(A_TIMESTAMP).as(MAX_TIMESTAMP)) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .where(A_ROW_NAME.in(rows) .and(A_COL_NAME.in(cols))) .and(A_TIMESTAMP.lessThan(timestamp)) .groupBy(A_ROW_NAME, A_COL_NAME); }
private Select<? extends Record> getLatestTimestampQueryManyTimestamps(DSLContext ctx, TableReference tableRef, RowN[] rows) { return ctx.select(A_ROW_NAME, A_COL_NAME, DSL.max(A_TIMESTAMP).as(MAX_TIMESTAMP)) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .join(values(ctx, rows, TEMP_TABLE_1, ROW_NAME, COL_NAME, TIMESTAMP)) .on(A_ROW_NAME.eq(T1_ROW_NAME) .and(A_COL_NAME.eq(T1_COL_NAME))) .where(A_TIMESTAMP.lessThan(T1_TIMESTAMP)) .groupBy(A_ROW_NAME, A_COL_NAME); }
int i = 0; for (Cell cell : partCells) { rows[i++] = row(new Object[] {cell.getRowName(), cell.getColumnName(), timestamp, value}); ctx.insertInto(table(tableName(tableRef)), field(ROW_NAME, byte[].class), field(COL_NAME, byte[].class), field(TIMESTAMP, Long.class), field(VALUE, byte[].class)) .select(ctx.select(T1_ROW_NAME, T1_COL_NAME, T1_TIMESTAMP, T1_VALUE) .from(values(ctx, rows, TEMP_TABLE_1, ROW_NAME, COL_NAME, TIMESTAMP, VALUE)) .whereNotExists(ctx.selectOne() .from(atlasTable(tableRef).as(ATLAS_TABLE)) .where(A_ROW_NAME.eq(T1_ROW_NAME) .and(A_COL_NAME.eq(T1_COL_NAME)) .and(A_TIMESTAMP.eq(T1_TIMESTAMP))))) .execute(); return null;
@Override public List<PM_R> withConnection(final Connection conn) throws SQLException { return DSL.using(conn, dialect, settings) .selectFrom(paymentMethodsTable) .where(DSL.field(KB_ACCOUNT_ID).equal(kbAccountId.toString())) .and(DSL.field(IS_DELETED).equal(FALSE)) .and(DSL.field(KB_TENANT_ID).equal(kbTenantId.toString())) .orderBy(DSL.field(recordIdFieldName).asc()) .fetch(); } });
/** * Returns an SQL statement that can be used to get when the rows * matching the query were last updated. */ public String lastUpdateStatement() { Table<?> table = initialize(DSL.table(DSL.name(database.recordUpdateTable.getName())).as(recordTableAlias)); return tableRenderContext.render(dslContext .select(DSL.field(DSL.name(recordTableAlias, database.recordUpdateDateField.getName())).max()) .from(table) .where(whereCondition)); }
Field<Date> removeField = table.removeField; ResultQuery<Record1<Long>> idsToDeleteQuery = create() .select(idField) .from(table.table) .where(removeField.lt(cutoffTime) .and(idField.gt(DSL.param(ID_GREATER_THAN_PARAM, 0L)))) .orderBy(idField) .limit(QUERY_LIMIT_ROWS.getValue()); Param<?> idGreaterThanParam = idsToDeleteQuery.getParam(ID_GREATER_THAN_PARAM); Table<?> referencingTable = key.getTable(); Field<Long> foreignKeyField = (Field<Long>) key.getFields().get(0); idsReferencedQuery = create().selectDistinct(foreignKeyField).from(referencingTable); for(Record1<Long> record : idsReferencedQuery.fetch()) { idsReferencedInOtherTables.add(record.value1()); try { int rowsDeleted = create() .delete(table.table) .where(idField.in(idsToDelete)) .execute(); table.addRowsDeleted(rowsDeleted);
private SelectOffsetStep<Record1<byte[]>> getRangeQuery(DSLContext ctx, TableReference tableRef, RangeRequest rangeRequest, long timestamp, int maxRows) { boolean reverse = rangeRequest.isReverse(); byte[] start = rangeRequest.getStartInclusive(); byte[] end = rangeRequest.getEndExclusive(); Condition cond = R_TIMESTAMP.lessThan(timestamp); if (start.length > 0) { cond = cond.and(reverse ? R_ROW_NAME.lessOrEqual(start) : R_ROW_NAME.greaterOrEqual(start)); } if (end.length > 0) { cond = cond.and(reverse ? R_ROW_NAME.greaterThan(end) : R_ROW_NAME.lessThan(end)); } return ctx.selectDistinct(R_ROW_NAME) .from(atlasTable(tableRef).as(RANGE_TABLE)) .where(cond) .orderBy(reverse ? R_ROW_NAME.desc() : R_ROW_NAME.asc()) .limit(maxRows); }
protected Condition toCondition(Part part, Iterator<Object> iterator) { String property = part.getProperty().toDotPath(); Field<Object> field = field(property); switch (part.getType()) { case AFTER: case GREATER_THAN: return field.gt(iterator.next()); case GREATER_THAN_EQUAL: return field.ge(iterator.next()); case BEFORE: case LESS_THAN: return field.lt(iterator.next()); case LESS_THAN_EQUAL: return field.le(iterator.next()); case BETWEEN: return field.between(iterator.next(), iterator.next()); case IS_NULL: return field.isNull(); case IS_NOT_NULL: return field.isNotNull(); case IN: return field.in(toList(iterator)); case NOT_IN: return field.notIn(toList(iterator)); case LIKE: return lowerIfIgnoreCase(part, field, iterator); case NOT_LIKE: return lowerIfIgnoreCase(part, field, iterator).not(); case STARTING_WITH: return field.startsWith(iterator.next()); case ENDING_WITH: return field.endsWith(iterator.next()); case CONTAINING: return field.contains(iterator.next()); case SIMPLE_PROPERTY: return field.eq(iterator.next()); case NEGATING_SIMPLE_PROPERTY: return field.ne(iterator.next()); case TRUE: return field.eq(true); case FALSE: return field.eq(false); default: throw new IllegalArgumentException("Unsupported keyword!"); } }
@Override public Multimap<Cell, Long> getAllTimestamps(final TableReference tableRef, final Set<Cell> cells, final long timestamp) throws InsufficientConsistencyException { if (cells.isEmpty()) { return ImmutableMultimap.of(); } Multimap<Cell, Long> toReturn = ArrayListMultimap.create(); for (List<Cell> partition : Iterables.partition(cells, batchSizeForReads)) { toReturn.putAll(run(ctx -> { Result<? extends Record> records = ctx .select(A_ROW_NAME, A_COL_NAME, A_TIMESTAMP) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .join(values(ctx, toRows(Sets.newHashSet(partition)), TEMP_TABLE_1, ROW_NAME, COL_NAME)) .on(A_ROW_NAME.eq(T1_ROW_NAME) .and(A_COL_NAME.eq(T1_COL_NAME))) .where(A_TIMESTAMP.lessThan(timestamp)) .fetch(); Multimap<Cell, Long> results = ArrayListMultimap.create(records.size() / 4, 4); for (Record record : records) { results.put( Cell.create(record.getValue(A_ROW_NAME), record.getValue(A_COL_NAME)), record.getValue(A_TIMESTAMP)); } return results; })); } return toReturn; }
@WithTimer public int getDepth(UUID parentInstanceId) { return txResult(tx -> tx.withRecursive("ancestors").as( select(PROCESS_QUEUE.INSTANCE_ID, PROCESS_QUEUE.PARENT_INSTANCE_ID, field("1", Integer.class).as("depth")) .from(PROCESS_QUEUE) .where(PROCESS_QUEUE.INSTANCE_ID.eq(parentInstanceId)) .unionAll( select(PROCESS_QUEUE.INSTANCE_ID, PROCESS_QUEUE.PARENT_INSTANCE_ID, field("1 + ancestors.depth", Integer.class).as("depth")) .from(PROCESS_QUEUE) .join(name("ancestors")) .on(PROCESS_QUEUE.INSTANCE_ID.eq( field(name("ancestors", "PARENT_INSTANCE_ID"), UUID.class))))) .select(max(field(name("ancestors", "depth"), Integer.class))) .from(name("ancestors")) .fetchOne(Record1::value1)); } }
private Result<? extends Record> fetchValues(DSLContext ctx, TableReference tableRef, Select<? extends Record> subQuery) { return ctx.select(A_ROW_NAME, A_COL_NAME, A_TIMESTAMP, A_VALUE) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .join(subQuery.asTable(TEMP_TABLE_2)) .on(A_ROW_NAME.eq(T2_ROW_NAME) .and(A_COL_NAME.eq(T2_COL_NAME)) .and(A_TIMESTAMP.eq(T2_MAX_TIMESTAMP))) .fetch(); }
public List<SecretEntry> list(UUID orgId, UUID currentUserId, Field<?> sortField, boolean asc) { SelectConditionStep<Record1<UUID>> teamIds = select(TEAMS.TEAM_ID) .from(TEAMS) .where(TEAMS.ORG_ID.eq(orgId)); Condition filterByTeamMember = exists(selectOne().from(USER_TEAMS) .where(USER_TEAMS.USER_ID.eq(currentUserId) .and(USER_TEAMS.TEAM_ID.in(teamIds)))); try (DSLContext tx = DSL.using(cfg)) { SelectJoinStep<Record12<UUID, String, UUID, String, UUID, String, UUID, String, String, String, String, String>> query = selectEntry(tx); if (currentUserId != null) { query.where(or(SECRETS.VISIBILITY.eq(SecretVisibility.PUBLIC.toString()), filterByTeamMember)); } if (orgId != null) { query.where(SECRETS.ORG_ID.eq(orgId)); } if (sortField != null) { query.orderBy(asc ? sortField.asc() : sortField.desc()); } return query.fetch(SecretDao::toEntry); } }
@SuppressWarnings("unchecked") @Override public /* non-final */ <Z> List<P> fetch(Field<Z> field, Z... values) { return using(configuration) .selectFrom(table) .where(field.in(values)) .fetch() .map(mapper()); }
TableLike<?> values(DSLContext ctx, RowN[] rows, String tableName, String... fieldNames) { switch (sqlDialect.family()) { case H2: List<SelectField<?>> fields = Lists.newArrayListWithCapacity(fieldNames.length); for (int i = 1; i <= fieldNames.length; i++) { fields.add(DSL.field("C" + i).as(fieldNames[i-1])); } RenderContext context = ctx.renderContext(); context.start(TABLE_VALUES) .keyword("values") .formatIndentLockStart(); boolean firstRow = true; for (Row row : rows) { if (!firstRow) { context.sql(',').formatSeparator(); } context.sql(row.toString()); firstRow = false; } context.formatIndentLockEnd() .end(TABLE_VALUES); String valuesClause = context.render(); return ctx.select(fields).from(valuesClause).asTable(tableName); default: return DSL.values(rows).as(tableName, fieldNames); } }
private Result<Record3<String, String, String>> fetchKeys(Condition condition) { return create().select( concat(DB_CLASS.CLASS_NAME, val("__"), DB_INDEX.INDEX_NAME).as("constraint_name"), DB_INDEX_KEY.KEY_ATTR_NAME, DB_CLASS.CLASS_NAME) .from(DB_INDEX) .join(DB_CLASS).on(DB_INDEX.CLASS_NAME.equal(DB_CLASS.CLASS_NAME)) .join(DB_INDEX_KEY).on( DB_INDEX_KEY.INDEX_NAME.equal(DB_INDEX.INDEX_NAME).and( DB_INDEX_KEY.CLASS_NAME.equal(DB_INDEX.CLASS_NAME))) .where(condition) .orderBy( DB_INDEX.INDEX_NAME.asc()) .fetch(); }