protected void set(String name, String value, boolean visible) { create() .insertInto(DATA, DATA.NAME, DATA.VISIBLE, DATA.VALUE) .values(name, visible, value) .execute(); }
public void insert(String firstName, String lastName) { UUID uuid = UUID.randomUUID(); txResult(tx -> tx.insertInto(USERS) .columns(USERS.ID, USERS.FIRST_NAME, USERS.LAST_NAME) .values(uuid, firstName, lastName) .execute() ); } }
@Override public boolean addPref(String u, String i, double v, Object n) { return dsl .insertInto(DATA, UIDX, IIDX, V) .values(user2uidx(u), item2iidx(i), v) .execute() == 1; }
/** * Inserts tags into the database for a DataPointVO. Also inserts the "name" and "device" tags from the data point properties. * * @param dataPoint * @param tags Should not contain tag keys "name" or "device" */ public void insertTagsForDataPoint(DataPointVO dataPoint, Map<String, String> tags) { int dataPointId = dataPoint.getId(); String name = dataPoint.getName(); String deviceName = dataPoint.getDeviceName(); BatchBindStep b = this.create.batch( this.create.insertInto(DATA_POINT_TAGS_NO_ALIAS) .columns(DATA_POINT_ID, TAG_KEY, TAG_VALUE) .values((Integer) null, null, null) ); tags.entrySet().forEach(e -> b.bind(dataPointId, e.getKey(), e.getValue())); if (name != null && !name.isEmpty()) { b.bind(dataPointId, NAME_TAG_KEY, name); } if (deviceName != null && !deviceName.isEmpty()) { b.bind(dataPointId, DEVICE_TAG_KEY, deviceName); } b.execute(); }
public UUID insert(DSLContext tx, UUID orgId, String name, String description) { return tx.insertInto(TEAMS) .columns(TEAMS.ORG_ID, TEAMS.TEAM_NAME, TEAMS.DESCRIPTION) .values(orgId, name, description) .returning(TEAMS.TEAM_ID) .fetchOne() .getTeamId(); }
public UUID insert(DSLContext tx, String username, UserType type, boolean admin) { return tx.insertInto(USERS) .columns(USERS.USERNAME, USERS.IS_ADMIN, USERS.USER_TYPE) .values(username, admin, type.toString()) .returning(USERS.USER_ID) .fetchOne().getUserId(); }
public void upsertAccessLevel(DSLContext tx, UUID secretId, UUID teamId, ResourceAccessLevel level) { tx.insertInto(SECRET_TEAM_ACCESS) .columns(SECRET_TEAM_ACCESS.SECRET_ID, SECRET_TEAM_ACCESS.TEAM_ID, SECRET_TEAM_ACCESS.ACCESS_LEVEL) .values(secretId, teamId, level.toString()) .onDuplicateKeyUpdate() .set(SECRET_TEAM_ACCESS.ACCESS_LEVEL, level.toString()) .execute(); }
public void upsertAccessLevel(DSLContext tx, UUID projectId, UUID teamId, ResourceAccessLevel level) { tx.insertInto(PROJECT_TEAM_ACCESS) .columns(PROJECT_TEAM_ACCESS.PROJECT_ID, PROJECT_TEAM_ACCESS.TEAM_ID, PROJECT_TEAM_ACCESS.ACCESS_LEVEL) .values(projectId, teamId, level.toString()) .onDuplicateKeyUpdate() .set(PROJECT_TEAM_ACCESS.ACCESS_LEVEL, level.toString()) .execute(); }
@Transactional public Pizza save(Pizza pizza) { Long pizzaId = dslContext.insertInto(PIZZA, PIZZA.BASE_ID, PIZZA.NAME, PIZZA.PRICE) .values(pizza.getBase().getId(), pizza.getName(), pizza.getPrice()) .returning(PIZZA.ID) .fetchOne() .getId(); int[] x = dslContext.batch(pizza.getToppings().stream() .map(t -> dslContext .insertInto(PIZZA_TOPPINGS, PIZZA_TOPPINGS.PIZZA_ID, PIZZA_TOPPINGS.TOPPINGS_ID) .values(pizzaId, t.getId())) .toArray(Query[]::new)) .execute(); pizza.setId(pizzaId); return pizza; } }
public void upsertAccessLevel(DSLContext tx, UUID inventoryId, UUID teamId, ResourceAccessLevel level) { tx.insertInto(INVENTORY_TEAM_ACCESS) .columns(INVENTORY_TEAM_ACCESS.INVENTORY_ID, INVENTORY_TEAM_ACCESS.TEAM_ID, INVENTORY_TEAM_ACCESS.ACCESS_LEVEL) .values(inventoryId, teamId, level.toString()) .onDuplicateKeyUpdate() .set(INVENTORY_TEAM_ACCESS.ACCESS_LEVEL, level.toString()) .execute(); }
public void upsertUser(DSLContext tx, UUID teamId, UUID userId, TeamRole role) { tx.insertInto(USER_TEAMS) .columns(USER_TEAMS.TEAM_ID, USER_TEAMS.USER_ID, USER_TEAMS.TEAM_ROLE) .values(teamId, userId, role.toString()) .onConflict(USER_TEAMS.TEAM_ID, USER_TEAMS.USER_ID) .doUpdate().set(USER_TEAMS.TEAM_ROLE, role.toString()) .execute(); }
private UUID insert(DSLContext tx, UUID inventoryId, String queryName, String text) { return tx.insertInto(INVENTORY_QUERIES) .columns(INVENTORY_QUERIES.INVENTORY_ID, INVENTORY_QUERIES.QUERY_NAME, INVENTORY_QUERIES.QUERY_TEXT) .values(value(inventoryId), value(queryName), value(text)) .returning(INVENTORY_QUERIES.QUERY_ID) .fetchOne() .getQueryId(); }
private void merge(DSLContext tx, UUID inventoryId, String itemPath, Object data) { tx.insertInto(INVENTORY_DATA) .columns(INVENTORY_DATA.INVENTORY_ID, INVENTORY_DATA.ITEM_PATH, INVENTORY_DATA.ITEM_DATA) .values(value(inventoryId), value(itemPath), field("?::jsonb", serialize(data))) .onDuplicateKeyUpdate() .set(INVENTORY_DATA.ITEM_DATA, field("?::jsonb", String.class, serialize(data))) .execute(); }
public UUID insert(String name, UUID parentId, Map<String, Object> rules) { return txResult(tx -> tx.insertInto(POLICIES) .columns(POLICIES.POLICY_NAME, POLICIES.PARENT_POLICY_ID, POLICIES.RULES) .values(value(name), value(parentId), field("?::jsonb", serialize(rules))) .returning(POLICIES.POLICY_ID) .fetchOne() .getPolicyId()); }
public synchronized void putString(UUID projectId, String key, String value) { ProjectKvStore kv = PROJECT_KV_STORE.as("kv"); tx(tx -> { int rows = tx.insertInto(kv) .columns(kv.PROJECT_ID, kv.VALUE_KEY, kv.VALUE_STRING) .values(projectId, key, value) .onConflict(kv.PROJECT_ID, kv.VALUE_KEY) .doUpdate().set(kv.VALUE_STRING, value) .execute(); if (rows != 1) { throw new DataAccessException("Invalid number of rows: " + rows); } }); }
public synchronized void putLong(UUID projectId, String key, long value) { ProjectKvStore kv = PROJECT_KV_STORE.as("kv"); tx(tx -> { int rows = tx.insertInto(kv) .columns(kv.PROJECT_ID, kv.VALUE_KEY, kv.VALUE_LONG) .values(projectId, key, value) .onConflict(kv.PROJECT_ID, kv.VALUE_KEY) .doUpdate().set(kv.VALUE_LONG, value) .execute(); if (rows != 1) { throw new DataAccessException("Invalid number of rows: " + rows); } }); }
public List<UUID> grabNextCheckpointId(Timestamp ageCutoff, int limit) { return txResult(tx -> { List<UUID> ids = tx.select(PROCESS_CHECKPOINTS.CHECKPOINT_ID) .from(PROCESS_CHECKPOINTS) .where(PROCESS_CHECKPOINTS.CHECKPOINT_DATE.lessOrEqual(ageCutoff).and( notExists( selectFrom(PROCESS_CHECKPOINT_ARCHIVE) .where(PROCESS_CHECKPOINT_ARCHIVE.CHECKPOINT_ID.eq(PROCESS_CHECKPOINTS.CHECKPOINT_ID))))) .limit(limit) .forUpdate() .skipLocked() .fetch(PROCESS_CHECKPOINTS.CHECKPOINT_ID); if (ids.isEmpty()) { return ids; } for (UUID id : ids) { tx.insertInto(PROCESS_CHECKPOINT_ARCHIVE) .columns(PROCESS_CHECKPOINT_ARCHIVE.CHECKPOINT_ID, PROCESS_CHECKPOINT_ARCHIVE.LAST_UPDATED_AT, PROCESS_CHECKPOINT_ARCHIVE.STATUS) .values(value(id), currentTimestamp(), value(ArchivalStatus.IN_PROGRESS.toString())) .execute(); } return ids; }); }
public synchronized long inc(UUID projectId, String key) { ProjectKvStore kv = PROJECT_KV_STORE.as("kv"); return txResult(tx -> { // grab a lock, it will be released when the transaction ends tx.execute("select from pg_advisory_xact_lock(?)", hash(projectId, key)); // "upsert" the record tx.insertInto(kv) .columns(kv.PROJECT_ID, kv.VALUE_KEY, kv.VALUE_LONG) .values(projectId, key, 1L) .onConflict(kv.PROJECT_ID, kv.VALUE_KEY) .doUpdate().set(kv.VALUE_LONG, kv.VALUE_LONG.plus(1)) .execute(); // get an updated value return tx.select(kv.VALUE_LONG) .from(kv) .where(kv.PROJECT_ID.eq(projectId) .and(kv.VALUE_KEY.eq(key))) .fetchOne(kv.VALUE_LONG); }); }
public List<ProcessKey> grabNext(ProcessStatus[] statuses, Timestamp ageCutoff, int limit) { return txResult(tx -> { List<ProcessKey> keys = tx.select(PROCESS_QUEUE.INSTANCE_ID, PROCESS_QUEUE.CREATED_AT) .from(PROCESS_QUEUE) .where(PROCESS_QUEUE.CURRENT_STATUS.in(Utils.toString(statuses)) .and(PROCESS_QUEUE.LAST_UPDATED_AT.lessOrEqual(ageCutoff)) .andNotExists(selectFrom(PROCESS_STATE_ARCHIVE) .where(PROCESS_STATE_ARCHIVE.INSTANCE_ID.eq(PROCESS_QUEUE.INSTANCE_ID)))) .limit(limit) .forUpdate() .skipLocked() .fetch(r -> new ProcessKey(r.get(PROCESS_QUEUE.INSTANCE_ID), r.get(PROCESS_QUEUE.CREATED_AT))); if (keys.isEmpty()) { return keys; } for (ProcessKey k : keys) { tx.insertInto(PROCESS_STATE_ARCHIVE) .columns(PROCESS_STATE_ARCHIVE.INSTANCE_ID, PROCESS_STATE_ARCHIVE.LAST_UPDATED_AT, PROCESS_STATE_ARCHIVE.STATUS) .values(value(k.getInstanceId()), currentTimestamp(), value(ArchivalStatus.IN_PROGRESS.toString())) .execute(); } return keys; }); }