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 void putBatch(DSLContext ctx, TableReference tableRef, PutBatch batch, boolean allowReinserts) { InsertValuesStep4<Record, byte[], byte[], Long, byte[]> query = ctx.insertInto(table(tableName(tableRef)), field(ROW_NAME, byte[].class), field(COL_NAME, byte[].class), field(TIMESTAMP, Long.class), field(VALUE, byte[].class)); query = batch.addValuesForInsert(query); try { query.execute(); } catch (DataAccessException e) { if (allowReinserts) { Result<? extends Record> records = ctx .select(A_ROW_NAME, A_COL_NAME, A_TIMESTAMP, A_VALUE) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .where(row(A_ROW_NAME, A_COL_NAME, A_TIMESTAMP).in(batch.getRowsForSelect())) .fetch(); if (records.isEmpty()) { throw e; } PutBatch nextBatch = batch.getNextBatch(records); if (nextBatch != null) { putBatch(ctx, tableRef, nextBatch, allowReinserts); return; } } throw new KeyAlreadyExistsException("Conflict on table " + tableRef, e); } }
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); }
@Override public List<? extends InstanceLink> findBadInstanceLinks(int count) { return create().select(INSTANCE_LINK.fields()) .from(INSTANCE_LINK) .join(INSTANCE) .on(INSTANCE.ID.eq(INSTANCE_LINK.TARGET_INSTANCE_ID)) .where(INSTANCE.STATE.eq(CommonStatesConstants.PURGED)) .limit(count) .fetchInto(InstanceLinkRecord.class); }
@Override public List<? extends Service> findServicesForInstanceId(long instanceId){ return create().select(SERVICE.fields()) .from(SERVICE) .join(SERVICE_EXPOSE_MAP) .on(SERVICE_EXPOSE_MAP.SERVICE_ID.eq(SERVICE.ID)) .where(SERVICE_EXPOSE_MAP.INSTANCE_ID.eq(instanceId)) .and(SERVICE_EXPOSE_MAP.REMOVED.isNull()) .and(SERVICE.REMOVED.isNull()) .fetchInto(ServiceRecord.class); }
@Override public TIntSet getOutlinkIds(int sourceId) throws DaoException { DSLContext context = getJooq(); try { Cursor<Record> result = context.select() .from(Tables.UNIVERSAL_SKELETAL_LINK) .where(Tables.UNIVERSAL_SKELETAL_LINK.SOURCE_ID.eq(sourceId)) .and(Tables.UNIVERSAL_SKELETAL_LINK.ALGORITHM_ID.eq(algorithmId)) .fetchLazy(getFetchSize()); TIntSet ids = new TIntHashSet(); for (Record record : result){ ids.add(record.getValue(Tables.UNIVERSAL_SKELETAL_LINK.DEST_ID)); } return ids; } finally { freeJooq(context); } }
@Override public int numUsers(int iidx) { return dsl .selectCount() .from(DATA) .where(IIDX.eq(iidx)) .fetchOne().value1(); }
private List<String> enumLabels(String nspname, String typname, Field<?> orderBy) { return create().select(PG_ENUM.ENUMLABEL) .from(PG_ENUM) .join(PG_TYPE).on(PG_ENUM.ENUMTYPID.eq(oid(PG_TYPE))) .join(PG_NAMESPACE).on(PG_TYPE.TYPNAMESPACE.eq(oid(PG_NAMESPACE))) .where(PG_NAMESPACE.NSPNAME.eq(nspname)) .and(PG_TYPE.TYPNAME.eq(typname)) .orderBy(orderBy) .fetch(PG_ENUM.ENUMLABEL); } }
@Override public String getBody(Language language, int rawLocalPageId) throws DaoException { DSLContext context = getJooq(); try { return context. select(). from(Tables.RAW_PAGE). where(Tables.RAW_PAGE.PAGE_ID.eq(rawLocalPageId)). and(Tables.RAW_PAGE.LANG_ID.eq(language.getId())). fetchOne(). getValue(Tables.RAW_PAGE.BODY); } finally { freeJooq(context); } }
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)); }
@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; }
public Map<String, List<ProjectProcesses>> processByOrgs(int maxProjectRows, Set<UUID> orgIds, Set<ProcessStatus> processStatuses, Timestamp fromUpdatedAt) { Set<String> statuses = processStatuses.stream().map(Enum::name).collect(Collectors.toSet()); try (DSLContext tx = DSL.using(cfg)) { WindowRowsStep<Integer> rnField = rowNumber().over().partitionBy(V_PROCESS_QUEUE.ORG_NAME).orderBy(V_PROCESS_QUEUE.ORG_NAME); SelectHavingStep<Record4<String, String, Integer, Integer>> a = tx.select(V_PROCESS_QUEUE.ORG_NAME, V_PROCESS_QUEUE.PROJECT_NAME, count(), rnField) .from(V_PROCESS_QUEUE) .where(V_PROCESS_QUEUE.ORG_ID.in(orgIds) .and(V_PROCESS_QUEUE.CURRENT_STATUS.in(statuses)) .and(V_PROCESS_QUEUE.LAST_UPDATED_AT.greaterOrEqual(fromUpdatedAt))) .groupBy(V_PROCESS_QUEUE.ORG_NAME, V_PROCESS_QUEUE.PROJECT_NAME); Result<Record3<String, String, Integer>> r = tx.select(a.field(0, String.class), a.field(1, String.class), a.field(2, Integer.class)) .from(a) .where(a.field(rnField).lessOrEqual(maxProjectRows)) .fetch(); Map<String, List<ProjectProcesses>> result = new HashMap<>(); r.forEach(i -> { String orgName = i.value1(); String projectName = i.value2(); int count = i.value3(); result.computeIfAbsent(orgName, (k) -> new ArrayList<>()).add(new ProjectProcesses(projectName, count)); }); return result; } } }
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); }
@Override public TIntSet getRedirects(LocalPage localPage) throws DaoException { DSLContext context = getJooq(); try { Result<Record> result = context.select(). from(Tables.REDIRECT). where(Tables.REDIRECT.DEST_PAGE_ID.eq(localPage.getLocalId())). and(Tables.REDIRECT.LANG_ID.eq(localPage.getLanguage().getId())). fetch(); TIntSet ids = new TIntHashSet(); for (Record record : result){ ids.add(record.getValue(Tables.REDIRECT.SRC_PAGE_ID)); } return ids; } finally { freeJooq(context); } }
@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(); } });
@Override public List<ProcessSummary> getProcessSummary() { final Map<String, ProcessSummary> processSummary = new TreeMap<>(); final Field<Boolean> running = DSL.field(PROCESS_INSTANCE.RUNNING_PROCESS_SERVER_ID.isNotNull()).as("running"); final Field<Boolean> delayed = DSL.field(PROCESS_INSTANCE.RUN_AFTER.greaterThan(new Date())).as("foo"); final Field<Integer> count = PROCESS_INSTANCE.PROCESS_NAME.count().as("count"); .select(PROCESS_INSTANCE.PROCESS_NAME, running, delayed, count) .from(PROCESS_INSTANCE) .where(PROCESS_INSTANCE.END_TIME.isNull()) .groupBy(PROCESS_INSTANCE.PROCESS_NAME, running, delayed) .fetchInto(new RecordHandler<Record4<String, Boolean, Boolean, Integer>>() { @Override