@Override public int getNumViews(LocalId pageId, DateTime startDate, DateTime endDate) throws DaoException { DSLContext context = getJooq(); Timestamp startTime = new Timestamp(startDate.getMillis()); Timestamp endTime = new Timestamp(endDate.getMillis()); try { Cursor<Record> result = context.select(). from(Tables.PAGEVIEW). where(Tables.PAGEVIEW.LANG_ID.eq(pageId.getLanguage().getId())). and(Tables.PAGEVIEW.TSTAMP.between(startTime, endTime)). and(Tables.PAGEVIEW.PAGE_ID.eq(pageId.getId())). fetchLazy(getFetchSize()); int numViews = 0; for (Record record : result){ numViews += record.getValue(Tables.PAGEVIEW.NUM_VIEWS); } return numViews; } finally { freeJooq(context); } }
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); }
@Override public Set<LocalId> getToDest(LocalId dest) throws DaoException { DSLContext context = getJooq(); try { Result<Record> result = context.select().from(Tables.ILL) .where(Tables.ILL.DEST_LANG_ID.equal(dest.getLanguage().getId())) .and(Tables.ILL.DEST_ID.equal(dest.getId())) .fetch(); if (result == null){ return null; } Set<LocalId> ills = new HashSet<LocalId>(); for (Record record : result) { ills.add(new LocalId( Language.getById(record.getValue(Tables.ILL.SOURCE_LANG_ID)), record.getValue(Tables.ILL.SOURCE_ID))); } return ills; } finally { freeJooq(context); } }
@Override public int numUsers(int iidx) { return dsl .selectCount() .from(DATA) .where(IIDX.eq(iidx)) .fetchOne().value1(); }
@Override protected List<TableDefinition> getTables0() throws SQLException { List<TableDefinition> result = new ArrayList<TableDefinition>(); for (Record record : create() .select( TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME) .from(TABLES) .where(TABLES.TABLE_SCHEMA.in(getInputSchemata())) .orderBy( TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME) .fetch()) { SchemaDefinition schema = getSchema(record.get(TABLES.TABLE_SCHEMA)); String name = record.get(TABLES.TABLE_NAME); String comment = ""; result.add(new HSQLDBTableDefinition(schema, name, comment)); } return result; }
@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); } }
select(inline(""), inline(""), inline(""), inline(false), inline(false), inline("")) .where(falseCondition()); .select() .from( .from(TABLES) .join(PG_NAMESPACE) .on(TABLES.TABLE_SCHEMA.eq(PG_NAMESPACE.NSPNAME)) .join(PG_CLASS) .and(canUseTupleInPredicates() PG_NAMESPACE.NSPNAME, PG_CLASS.RELNAME) .from(PG_CLASS) .join(PG_NAMESPACE) .unionAll( .from(PG_CLASS) .join(PG_NAMESPACE) .on(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE))) .leftOuterJoin(PG_DESCRIPTION) .from(ROUTINES) .from(ct)
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;
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)); }
@Override public Service getServiceByServiceIndexId(long serviceIndexId) { Record record = create() .select(SERVICE.fields()) .from(SERVICE) .join(SERVICE_INDEX).on(SERVICE.ID.eq(SERVICE_INDEX.SERVICE_ID)) .where(SERVICE_INDEX.ID.eq(serviceIndexId)) .fetchAny(); return record == null ? null : record.into(Service.class); }
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); } }
public List<IdAndStatus> getCascade(PartialProcessKey parentKey) { UUID parentInstanceId = parentKey.getInstanceId(); try (DSLContext tx = DSL.using(cfg)) { return tx.withRecursive("children").as( select(PROCESS_QUEUE.INSTANCE_ID, PROCESS_QUEUE.CREATED_AT, PROCESS_QUEUE.CURRENT_STATUS).from(PROCESS_QUEUE) .where(PROCESS_QUEUE.INSTANCE_ID.eq(parentInstanceId)) .unionAll( select(PROCESS_QUEUE.INSTANCE_ID, PROCESS_QUEUE.CREATED_AT, PROCESS_QUEUE.CURRENT_STATUS).from(PROCESS_QUEUE) .join(name("children")) .on(PROCESS_QUEUE.PARENT_INSTANCE_ID.eq( field(name("children", "INSTANCE_ID"), UUID.class))))) .select() .from(name("children")) .fetch(r -> new IdAndStatus(new ProcessKey(r.get(0, UUID.class), r.get(1, Timestamp.class)), ProcessStatus.valueOf(r.get(2, String.class)))); } }
private final Table<Record> table(Configuration configuration) { if (table == null) { Select<Record> select = null; for (Object element : array) { // [#1081] Be sure to get the correct cast type also for null Field<?> val = DSL.val(element, field.fields[0].getDataType()); Select<Record> subselect = using(configuration).select(val.as("COLUMN_VALUE")).select(); if (select == null) { select = subselect; } else { select = select.unionAll(subselect); } } // Empty arrays should result in empty tables if (select == null) { select = using(configuration).select(one().as("COLUMN_VALUE")).select().where(falseCondition()); } table = select.asTable(alias); } return table; } }
private void addFkRecords(ReflectedTable t2, HashMap<Object, Object> fkValues, Params params, DSLContext dsl, ArrayList<Record> records) { Field<Object> pk = t2.getPk(); ArrayList<Field<?>> fields = columns.getNames(t2, false, params); ResultQuery<org.jooq.Record> query = dsl.select(fields).from(t2).where(pk.in(fkValues.keySet())); for (org.jooq.Record record : query.fetch()) { records.add(Record.valueOf(record.intoMap())); } }