@Override public Agent getHostAgentForDelegate(long agentId) { List<? extends Agent> result = create().select(AGENT.fields()) .from(AGENT) .join(HOST) .on(HOST.AGENT_ID.eq(AGENT.ID)) .join(INSTANCE_HOST_MAP) .on(INSTANCE_HOST_MAP.HOST_ID.eq(HOST.ID)) .join(INSTANCE) .on(INSTANCE_HOST_MAP.INSTANCE_ID.eq(INSTANCE.ID)) .where(INSTANCE.AGENT_ID.eq(agentId)).fetchInto(AgentRecord.class); return result.size() == 0 ? null : result.get(0); }
@Override public List<String> getSpanNames(String serviceName) { if (serviceName == null) return emptyList(); serviceName = serviceName.toLowerCase(); // service names are always lowercase! try (Connection conn = datasource.getConnection()) { return context.get(conn) .selectDistinct(ZIPKIN_SPANS.NAME) .from(ZIPKIN_SPANS) .join(ZIPKIN_ANNOTATIONS) .on(ZIPKIN_SPANS.TRACE_ID.eq(ZIPKIN_ANNOTATIONS.TRACE_ID)) .and(ZIPKIN_SPANS.ID.eq(ZIPKIN_ANNOTATIONS.SPAN_ID)) .where(ZIPKIN_ANNOTATIONS.ENDPOINT_SERVICE_NAME.eq(serviceName)) .orderBy(ZIPKIN_SPANS.NAME) .fetch(ZIPKIN_SPANS.NAME); } catch (SQLException e) { throw new RuntimeException("Error querying for " + serviceName + ": " + e.getMessage()); } }
protected SelectConditionStep<Record3<String, Long, Long>> getHostQuery(List<String> orderedHostUUIDs, QueryOptions options) { return create() .select(HOST.UUID, HOST.ID, STORAGE_POOL.ID) .from(HOST) .leftOuterJoin(STORAGE_POOL_HOST_MAP) .on(STORAGE_POOL_HOST_MAP.HOST_ID.eq(HOST.ID) .and(STORAGE_POOL_HOST_MAP.REMOVED.isNull())) .join(STORAGE_POOL) .on(STORAGE_POOL.ID.eq(STORAGE_POOL_HOST_MAP.STORAGE_POOL_ID)) .leftOuterJoin(AGENT) .on(AGENT.ID.eq(HOST.AGENT_ID)) .where(getQueryOptionCondition(options, orderedHostUUIDs)); }
@Override public List<? extends Instance> getInstancesWithHealtcheckEnabled(long accountId) { return create().select(INSTANCE.fields()) .from(INSTANCE) .join(HEALTHCHECK_INSTANCE) .on(HEALTHCHECK_INSTANCE.INSTANCE_ID.eq(INSTANCE.ID)) .and(HEALTHCHECK_INSTANCE.REMOVED.isNull()) .and(INSTANCE.REMOVED.isNull()) .and(INSTANCE.STATE.in(InstanceConstants.STATE_STARTING, InstanceConstants.STATE_RUNNING) .and(INSTANCE.ACCOUNT_ID.eq(accountId))) .fetchInto(InstanceRecord.class); }
@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; }
private Result<Record5<String, String, String, String, String>> fetchKeys(String constraintType) { return create().select( Sysschemas.SCHEMANAME, Systables.TABLENAME, Systables.TABLEID, Sysconstraints.CONSTRAINTNAME, Sysconglomerates.DESCRIPTOR) .from(SYSCONGLOMERATES) .join(SYSKEYS) .on(Syskeys.CONGLOMERATEID.equal(Sysconglomerates.CONGLOMERATEID)) .join(SYSCONSTRAINTS) .on(Sysconstraints.CONSTRAINTID.equal(Syskeys.CONSTRAINTID)) .join(SYSTABLES) .on(Systables.TABLEID.equal(Sysconglomerates.TABLEID)) .join(SYSSCHEMAS) .on(Sysschemas.SCHEMAID.equal(Systables.SCHEMAID)) // [#6797] The casts are necessary if a non-standard collation is used .and(Sysschemas.SCHEMANAME.cast(VARCHAR(32672)).in(getInputSchemata())) .where(Sysconstraints.TYPE.cast(VARCHAR(32672)).equal(constraintType)) .orderBy( Sysschemas.SCHEMANAME, Systables.TABLENAME, Sysconstraints.CONSTRAINTNAME) .fetch(); }
.leftOuterJoin(SERVICE) .on(SERVICE.ID.eq(SERVICE_CONSUME_MAP.CONSUMED_SERVICE_ID)) .leftOuterJoin(STACK) .on(STACK.ID.eq(SERVICE.STACK_ID)) .where(SERVICE_CONSUME_MAP.SERVICE_ID.in(ids) .and(SERVICE_CONSUME_MAP.REMOVED.isNull())) .fetchInto(new RecordHandler<Record7<String, Long, String, Long, String, Long, String>>() {
.join(PG_NAMESPACE) .on(COLUMNS.TABLE_SCHEMA.eq(PG_NAMESPACE.NSPNAME)) .join(PG_CLASS) .on(PG_CLASS.RELNAME.eq(COLUMNS.TABLE_NAME)) .and(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE))) .join(PG_ATTRIBUTE) .on(PG_ATTRIBUTE.ATTRELID.eq(oid(PG_CLASS))) .and(PG_ATTRIBUTE.ATTNAME.eq(COLUMNS.COLUMN_NAME)) .leftOuterJoin(PG_DESCRIPTION) .on(PG_DESCRIPTION.OBJOID.eq(oid(PG_CLASS))) .and(PG_DESCRIPTION.OBJSUBID.eq(COLUMNS.ORDINAL_POSITION)) .where(COLUMNS.TABLE_SCHEMA.equal(getSchema().getName())) .and(COLUMNS.TABLE_NAME.equal(getName())) .orderBy(COLUMNS.ORDINAL_POSITION)
.join(n) .on(oid(n).eq(d.TYPNAMESPACE)) .leftJoin(c) .on(oid(d).eq(c.CONTYPID)) .where(d.TYPTYPE.eq("d")) .and(n.NSPNAME.in(getInputSchemata())) .unionAll( .join(d) .on(field(name("domains", d.TYPBASETYPE.getName())).eq(oid(d))) .leftJoin(c) .on(oid(d).eq(c.CONTYPID)) )) .join(name("domains")) .on(field(name("domains", "typbasetype")).eq(0)) .and(field(name("domains", "domain_id")).eq(oid(d))) .join(b) .on(field(name("domains", "base_id")).eq(oid(b))) .join(n) .on(oid(n).eq(d.TYPNAMESPACE)) .where(d.TYPTYPE.eq("d")) .and(n.NSPNAME.in(getInputSchemata())) .orderBy(n.NSPNAME, d.TYPNAME)) {
.join(ROUTINES) .on(PARAMETERS.SPECIFIC_SCHEMA.equal(ROUTINES.SPECIFIC_SCHEMA)) .and(PARAMETERS.SPECIFIC_NAME.equal(ROUTINES.SPECIFIC_NAME)) .leftOuterJoin(ELEMENT_TYPES) .on(ROUTINES.ROUTINE_SCHEMA.equal(ELEMENT_TYPES.OBJECT_SCHEMA)) .and(ROUTINES.ROUTINE_NAME.equal(ELEMENT_TYPES.OBJECT_NAME)) .and(PARAMETERS.DTD_IDENTIFIER.equal(ELEMENT_TYPES.COLLECTION_TYPE_IDENTIFIER)) .where(PARAMETERS.SPECIFIC_SCHEMA.equal(getSchema().getName())) .and(PARAMETERS.SPECIFIC_NAME.equal(this.specificName))
.on(bt.TYPNAMESPACE.eq(oid(nbt)))) .on(t.TYPTYPE.eq(inline("d")).and(t.TYPBASETYPE.eq(oid(bt)))) .leftJoin(co .join(nco) .on(co.COLLNAMESPACE.eq(oid(nco)))) nco.NSPNAME.ne(inline("pg_catalog")).or(co.COLLNAME.ne(inline("default"))) )) .leftJoin(PG_DESCRIPTION) .on(PG_DESCRIPTION.OBJOID.eq(oid(c))) .and(PG_DESCRIPTION.OBJSUBID.eq(a.ATTNUM.coerce(PG_DESCRIPTION.OBJSUBID))) .where( not(condition("pg_is_other_temp_schema({0})", oid(nc))) .and(a.ATTNUM.gt(inline((short) 0)))
public ArticleDetail getDetail(String id) { CArticle article = C_ARTICLE.as("a"); CArticle before = C_ARTICLE.as("b"); CArticle next = C_ARTICLE.as("n"); CArticle inner = C_ARTICLE.as("i"); ArticleDetail a = dao.execute(e -> { return e.select(Fields.all(article.fields(), before.ID.as("beforeId"), before.TITLE.as("beforeTitle"), next.ID.as("nextId"), next.TITLE.as("nextTitle") )).from(article) .leftJoin(before).on(before.ID.eq(e.select(inner.ID).from(inner).where(article.CREATE_TIME.ge(inner.CREATE_TIME)).and(inner.ID.ne(article.ID).and(inner.STATUS.eq(Article.STATUS_PUBLISH))).orderBy(inner.CREATE_TIME.desc()).limit(0, 1))) .leftJoin(next).on((next.ID.eq(e.select(inner.ID).from(inner).where(article.CREATE_TIME.le(inner.CREATE_TIME)).and(inner.ID.ne(article.ID).and(inner.STATUS.eq(Article.STATUS_PUBLISH))).orderBy(inner.CREATE_TIME).limit(0, 1)))) .where(article.ID.eq(id)) .fetchOne(r -> { return dao.mapperEntityEx(r, ArticleDetail.class); }); }); List<Tag> tags = articleTagService.findTags(a.getId()); a.setTags(tags); return a; }
@Override public Integer getCurrentScale(long serviceId) { return create() .select(DSL.count()) .from(INSTANCE) .join(SERVICE_EXPOSE_MAP) .on(SERVICE_EXPOSE_MAP.INSTANCE_ID.eq(INSTANCE.ID) .and(SERVICE_EXPOSE_MAP.SERVICE_ID.eq(serviceId)) .and(SERVICE_EXPOSE_MAP.STATE.in(CommonStatesConstants.ACTIVATING, CommonStatesConstants.ACTIVE, CommonStatesConstants.REQUESTED)) .and(SERVICE_EXPOSE_MAP.UPGRADE.eq(false)) .and(INSTANCE.STATE.notIn(CommonStatesConstants.PURGING, CommonStatesConstants.PURGED, CommonStatesConstants.REMOVED, CommonStatesConstants.REMOVING, InstanceConstants.STATE_ERROR, InstanceConstants.STATE_ERRORING))) .and(SERVICE_EXPOSE_MAP.DNS_PREFIX.isNull()) .fetchOne(0, Integer.class); }
.join(select.asTable().as(distinctAlias)) .on(recordTypeIdField.eq(DSL.field(DSL.name(distinctAlias, recordTypeIdField.getName()), uuidType))) .and(recordIdField.eq(DSL.field(DSL.name(distinctAlias, recordIdField.getName()), uuidType)));
private SelectJoinStep<Record12<UUID, UUID, String, UUID, String, UUID, String, String, String[], String, String, String>> selectTriggers(DSLContext tx) { Organizations o = ORGANIZATIONS.as("o"); Projects p = PROJECTS.as("p"); Repositories r = REPOSITORIES.as("r"); return tx.select( TRIGGERS.TRIGGER_ID, o.ORG_ID, o.ORG_NAME, TRIGGERS.PROJECT_ID, p.PROJECT_NAME, TRIGGERS.REPO_ID, r.REPO_NAME, TRIGGERS.EVENT_SOURCE, TRIGGERS.ACTIVE_PROFILES, TRIGGERS.ARGUMENTS.cast(String.class), TRIGGERS.CONDITIONS.cast(String.class), TRIGGERS.TRIGGER_CFG.cast(String.class)) .from(TRIGGERS) .leftJoin(p).on(p.PROJECT_ID.eq(TRIGGERS.PROJECT_ID)) .leftJoin(o).on(o.ORG_ID.eq(p.ORG_ID)) .leftJoin(r).on(r.REPO_ID.eq(TRIGGERS.REPO_ID)); }
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 void createVolumeInStoragePool(Map<String, Object> volumeData, String volumeName, StoragePool storagePool) { Record record = create() .select(VOLUME.fields()) .from(VOLUME) .join(VOLUME_STORAGE_POOL_MAP) .on(VOLUME_STORAGE_POOL_MAP.VOLUME_ID.eq(VOLUME.ID) .and(VOLUME_STORAGE_POOL_MAP.STORAGE_POOL_ID.eq(storagePool.getId()))) .join(STORAGE_POOL) .on(VOLUME_STORAGE_POOL_MAP.STORAGE_POOL_ID.eq(STORAGE_POOL.ID)) .and(STORAGE_POOL.REMOVED.isNull()) .where(VOLUME.NAME.eq(volumeName) .and((VOLUME.REMOVED.isNull().or(VOLUME.STATE.eq(CommonStatesConstants.REMOVING))))) .and(VOLUME.ACCOUNT_ID.eq(storagePool.getAccountId())) .fetchAny(); if (record != null) { return; } Volume volume = resourceDao.createAndSchedule(Volume.class, volumeData); Map<String, Object> vspm = new HashMap<String, Object>(); vspm.put("volumeId", volume.getId()); vspm.put("storagePoolId", storagePool.getId()); resourceDao.createAndSchedule(VolumeStoragePoolMap.class, vspm); }
public List<ResourceAccessEntry> getAccessLevel(UUID projectId) { List<ResourceAccessEntry> resourceAccessList = new ArrayList<>(); try (DSLContext tx = DSL.using(cfg)) { Result<Record5<UUID, UUID, String, String, String>> teamsAccess = tx.select( PROJECT_TEAM_ACCESS.TEAM_ID, PROJECT_TEAM_ACCESS.PROJECT_ID, TEAMS.TEAM_NAME, ORGANIZATIONS.ORG_NAME, PROJECT_TEAM_ACCESS.ACCESS_LEVEL) .from(PROJECT_TEAM_ACCESS) .leftOuterJoin(TEAMS).on(TEAMS.TEAM_ID.eq(PROJECT_TEAM_ACCESS.TEAM_ID)) .leftOuterJoin(PROJECTS).on(PROJECTS.PROJECT_ID.eq(projectId)) .leftOuterJoin(ORGANIZATIONS).on(ORGANIZATIONS.ORG_ID.eq(PROJECTS.ORG_ID)) .where(PROJECT_TEAM_ACCESS.PROJECT_ID.eq(projectId)) .fetch(); for (Record5<UUID, UUID, String, String, String> t : teamsAccess) { resourceAccessList.add(new ResourceAccessEntry(t.get(PROJECT_TEAM_ACCESS.TEAM_ID), t.get(ORGANIZATIONS.ORG_NAME), t.get(TEAMS.TEAM_NAME), ResourceAccessLevel.valueOf(t.get(PROJECT_TEAM_ACCESS.ACCESS_LEVEL)))); } } return resourceAccessList; }
.join(PG_NAMESPACE) .on(TABLES.TABLE_SCHEMA.eq(PG_NAMESPACE.NSPNAME)) .join(PG_CLASS) .on(PG_CLASS.RELNAME.eq(TABLES.TABLE_NAME)) .and(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE))) .leftOuterJoin(PG_DESCRIPTION) .on(PG_DESCRIPTION.OBJOID.eq(oid(PG_CLASS))) .and(PG_DESCRIPTION.OBJSUBID.eq(0)) .where(TABLES.TABLE_SCHEMA.in(getInputSchemata())) .join(PG_NAMESPACE) .on(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE))) .where(PG_CLASS.RELKIND.eq(inline("m")))) : noCondition() .join(PG_NAMESPACE) .on(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE))) .leftOuterJoin(PG_DESCRIPTION) .on(PG_DESCRIPTION.OBJOID.eq(oid(PG_CLASS))) .and(PG_DESCRIPTION.OBJSUBID.eq(0)) .where(PG_NAMESPACE.NSPNAME.in(getInputSchemata())) .and(PG_CLASS.RELKIND.eq(inline("m")))) .from(ROUTINES) .join(PG_NAMESPACE).on(ROUTINES.SPECIFIC_SCHEMA.eq(PG_NAMESPACE.NSPNAME)) .join(PG_PROC).on(PG_PROC.PRONAMESPACE.eq(oid(PG_NAMESPACE))) .and(PG_PROC.PRONAME.concat("_").concat(oid(PG_PROC)).eq(ROUTINES.SPECIFIC_NAME)) .where(ROUTINES.ROUTINE_SCHEMA.in(getInputSchemata())) .and(PG_PROC.PRORETSET)