@Override public void migrate(Connection connection) throws Exception { try (Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("groups")) .column(field("group_id", SQLDataType.BIGINT.identity(true))) .column(field("name", SQLDataType.VARCHAR(100).nullable(false))) .column(field("description", SQLDataType.VARCHAR(100).nullable(false))) .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false))) .constraints( constraint().primaryKey(field("group_id")), constraint().unique(field("name")) ).getSQL(); stmt.execute(ddl); } }
private void saveTaxCodeInternal(final EasyTaxTaxCode taxCode, final DateTime date, final DSLContext dslContext) { int updateCount = dslContext.update(EASYTAX_TAX_CODES) .set(EASYTAX_TAX_CODES.TAX_RATE, taxCode.getTaxRate()) .set(EASYTAX_TAX_CODES.VALID_FROM_DATE, taxCode.getValidFromDate()) .set(EASYTAX_TAX_CODES.VALID_TO_DATE, taxCode.getValidToDate()) .set(EASYTAX_TAX_CODES.CREATED_DATE, date) .where(EASYTAX_TAX_CODES.KB_TENANT_ID.equal(taxCode.getKbTenantId().toString())) .and(EASYTAX_TAX_CODES.TAX_ZONE.equal(taxCode.getTaxZone())) .and(EASYTAX_TAX_CODES.PRODUCT_NAME.equal(taxCode.getProductName())) .and(EASYTAX_TAX_CODES.TAX_CODE.equal(taxCode.getTaxCode())) .and(EASYTAX_TAX_CODES.VALID_FROM_DATE.equal(taxCode.getValidFromDate())).execute(); if (updateCount < 1) { dslContext .insertInto(EASYTAX_TAX_CODES, EASYTAX_TAX_CODES.KB_TENANT_ID, EASYTAX_TAX_CODES.TAX_ZONE, EASYTAX_TAX_CODES.PRODUCT_NAME, EASYTAX_TAX_CODES.TAX_CODE, EASYTAX_TAX_CODES.TAX_RATE, EASYTAX_TAX_CODES.VALID_FROM_DATE, EASYTAX_TAX_CODES.VALID_TO_DATE, EASYTAX_TAX_CODES.CREATED_DATE) .values(taxCode.getKbTenantId().toString(), taxCode.getTaxZone(), taxCode.getProductName(), taxCode.getTaxCode(), taxCode.getTaxRate(), taxCode.getValidFromDate(), taxCode.getValidToDate(), date) .execute(); } }
private ValidationResult notExists(final DSLContext context, final ColumnPermutation lhs, final ColumnPermutation rhs) { final Table<Record> lhsAlias = context.select(fields(lhs)) .from(tables(lhs)) .where(notNull(lhs)) .asTable(); final int violators = context.selectCount().from( selectFrom(lhsAlias).whereNotExists( context.selectOne() .from(tables(rhs)) .where(row(fields(rhs)).eq(row(lhsAlias.fields()))) ).limit(1) ).fetchOne().value1(); return new DefaultValidationResult(violators == 0); }
private ValidationResult leftOuterJoin(final DSLContext context, final ColumnPermutation lhs, final ColumnPermutation rhs) { final String rhsTableName = rhs.getColumnIdentifiers().get(0).getTableIdentifier(); final Table rhsAlias = table(name(rhsTableName)).asTable(randomAlias()); final ColumnPermutation rhsAliasColumns = swapRelationName(rhsAlias.getName(), rhs); final int violators = context.selectDistinct(fields(lhs)) .from(tables(lhs)) .leftOuterJoin(rhsAlias) .on(columnsEqual(lhs, rhsAliasColumns)) .where(isNull(rhsAliasColumns)) .and(notNull(lhs)) .limit(1) .execute(); return new DefaultValidationResult(violators == 0); }
@Test public void testFindUsersWithJOOQ() { //Query query = em.createQuery("select u from User u where u.address.country = 'Norway'"); //Query query = em.createNativeQuery("select * from User where country = 'Norway'"); DSLContext create = DSL.using(SQLDialect.H2); String sql = create .select() .from(table("User")) .where(field("country").eq("Norway")) .getSQL(ParamType.INLINED); Query query = em.createNativeQuery(sql, User.class); List<User> results = query.getResultList(); assertEquals(3, results.size()); /* JOOQ is a popular, easy to use DSL for writing SQL (not JPQL). Besides type-safety and IDE code-completion, one HUGE benefit is that the SQL is targeted for the specific dialect of the target DB. */ }
public static void createGuildSettings(DiscordBot bot, DSLContext database, Guild guild) { SettingsRecord record = database.newRecord(Tables.SETTINGS); record.setGuildid(guild.getId()); record.setSilentcommands(false); record.setInvitelinkremover(false); record.setModlog(false); record.setModlogchannelid(DiscordUtils.getDefaultChannel(guild).getId()); record.setHoldingroom(false); record.setHoldingroomroleid(null); record.setHoldingroomminutes(3); record.setPrefix(bot.getConfig().jim.default_prefix); record.setWelcomemessage(false); record.setMessage(DEFAULT_WELCOME_MESSAGE); record.setWelcomemessagechannelid(DiscordUtils.getDefaultChannel(guild).getId()); record.setNospaceprefix(false); record.setStatistics(false); record.store(); }
@Override public void migrate(Connection connection, DSLContext create) { create.createSequence("S_AUTHOR_ID").execute(); String authorTableName = "AUTHOR"; create.createTable(authorTableName) .column("ID", SQLDataType.INTEGER.nullable(false)) .column("FIRST_NAME", SQLDataType.VARCHAR.length(50)) .column("LAST_NAME", SQLDataType.VARCHAR.length(50).nullable(false)) .column("DATE_OF_BIRTH", SQLDataType.DATE) .column("YEAR_OF_BIRTH", SQLDataType.INTEGER) .column("ADDRESS", SQLDataType.VARCHAR.length(50)) .execute(); create.alterTable(authorTableName).add(constraint("PK_T_AUTHOR").primaryKey("ID")).execute(); String bookTableName = "BOOK"; create.createTable(bookTableName) .column("ID", SQLDataType.INTEGER.nullable(false)) .column("AUTHOR_ID", SQLDataType.INTEGER.nullable(false)) .column("TITLE", SQLDataType.VARCHAR.length(400).nullable(false)) .execute(); create.alterTable(bookTableName).add(constraint("PK_T_BOOK").primaryKey("ID")).execute(); create.alterTable(bookTableName).add( constraint("FK_T_BOOK_AUTHOR_ID").foreignKey("AUTHOR_ID").references(authorTableName, "ID")) .execute(); }
@Override public void handle(RoutingContext ctx, Server server, DiscordBot bot, DSLContext database) { HttpServerRequest request = ctx.request(); HttpServerResponse response = ctx.response(); String userId = ServerUtils.authUser(request, response, config); if (userId == null) { return; } User user = DiscordApiUtils.getUser(userId, config.jim.token); if (user == null) { response.setStatusCode(404); response.end(); return; } Gson gson = new Gson(); response.putHeader("Content-Type", "application/json"); response.end(gson.toJson(user)); }
@Override public boolean run(DiscordBot bot, GuildMessageReceivedEvent event, String args) { if (embed == null) { JDA shard = event.getJDA(); DSLContext database = bot.getDatabase(); Guild guild = event.getGuild(); EmbedBuilder builder = new EmbedBuilder(); builder.setAuthor("Safety Jim - Commands", null, shard.getSelfUser().getAvatarUrl()); builder.setDescription(getUsageTexts(bot, DatabaseUtils.getGuildSettings(database, guild).getPrefix())); builder.setColor(new Color(0x4286F4)); embed = builder.build(); } DiscordUtils.successReact(bot, event.getMessage()); DiscordUtils.sendMessage(event.getChannel(), embed); return false; }
private void init(Connection conn) { DSLContext create = DSL.using(conn, SQLDialect.POSTGRES); Result<Record> coresAttributes = create.select().from(MD_CLASS_ATTRIBUTES) .join(MD_CLASSES).on(MD_CLASS_ATTRIBUTES.CLASS_ID.eq(MD_CLASSES.CLASS_ID)) .where(MD_CLASSES.CLASS_NAME.like("bom%Compute")) .and(MD_CLASS_ATTRIBUTES.ATTRIBUTE_NAME.eq("cores")).fetch(); for (Record coresAttribute : coresAttributes) { coresAttributeIds.add(coresAttribute.getValue(MD_CLASS_ATTRIBUTES.ATTRIBUTE_ID)); } create = DSL.using(conn, SQLDialect.POSTGRES); Result<Record> computeClasses = create.select().from(MD_CLASSES) .where(MD_CLASSES.CLASS_NAME.like("bom%Compute")).fetch(); for (Record computeClass : computeClasses) { computeClassIds.add(computeClass.get(MD_CLASSES.CLASS_ID)); } log.info("cached compute class ids: " + computeClassIds); log.info("cached compute cores attribute ids: " + coresAttributeIds); }
private List<Deployment> getDeployments(Connection conn, Environment env) { List<Deployment> deployments = new ArrayList<>(); DSLContext create = DSL.using(conn, SQLDialect.POSTGRES); Result<Record> records = create.select().from(DJ_DEPLOYMENT) .join(DJ_DEPLOYMENT_STATES).on(DJ_DEPLOYMENT_STATES.STATE_ID.eq(DJ_DEPLOYMENT.STATE_ID)) .join(NS_NAMESPACES).on(NS_NAMESPACES.NS_ID.eq(DJ_DEPLOYMENT.NS_ID)) .where(NS_NAMESPACES.NS_PATH.eq(env.getPath()+ "/" + env.getName() + "/bom")) .and(DJ_DEPLOYMENT.CREATED_BY.notEqual("oneops-autoreplace")) .orderBy(DJ_DEPLOYMENT.CREATED.desc()) .limit(1) .fetch(); for (Record r : records) { Deployment deployment = new Deployment(); deployment.setCreatedAt(r.getValue(DJ_DEPLOYMENT.CREATED)); deployment.setCreatedBy(r.getValue(DJ_DEPLOYMENT.CREATED_BY)); deployment.setState(r.getValue(DJ_DEPLOYMENT_STATES.STATE_NAME)); deployments.add(deployment); } return deployments; }
private List<Environment> getOneopsEnvironments(Connection conn) { List<Environment> envs = new ArrayList<>(); DSLContext create = DSL.using(conn, SQLDialect.POSTGRES); log.info("Fetching all environments.."); Result<Record> envRecords = create.select().from(CM_CI) .join(MD_CLASSES).on(CM_CI.CLASS_ID.eq(MD_CLASSES.CLASS_ID)) .join(NS_NAMESPACES).on(CM_CI.NS_ID.eq(NS_NAMESPACES.NS_ID)) .where(MD_CLASSES.CLASS_NAME.eq("manifest.Environment")) .fetch(); //all the env cis log.info("Got all environments"); for (Record r : envRecords) { long envId = r.getValue(CM_CI.CI_ID); //now query attributes for this env Environment env = new Environment(); env.setName(r.getValue(CM_CI.CI_NAME)); env.setId(r.getValue(CM_CI.CI_ID)); env.setPath(r.getValue(NS_NAMESPACES.NS_PATH)); env.setNsId(r.getValue(NS_NAMESPACES.NS_ID)); envs.add(env); } return envs; }
private List<String> getActiveClouds(Platform platform, Connection conn) { DSLContext create = DSL.using(conn, SQLDialect.POSTGRES); List<String> clouds = new ArrayList<>(); Result<Record> consumesRecords = create.select().from(CM_CI_RELATIONS) .join(MD_RELATIONS).on(MD_RELATIONS.RELATION_ID.eq(CM_CI_RELATIONS.RELATION_ID)) .join(CM_CI_RELATION_ATTRIBUTES).on(CM_CI_RELATION_ATTRIBUTES.CI_RELATION_ID.eq(CM_CI_RELATIONS.CI_RELATION_ID)) .where(CM_CI_RELATIONS.FROM_CI_ID.eq(platform.getId())) .and(CM_CI_RELATION_ATTRIBUTES.DF_ATTRIBUTE_VALUE.eq("active")) .fetch(); for (Record r : consumesRecords) { String comments = r.getValue(CM_CI_RELATIONS.COMMENTS); String cloudName = comments.split(":")[1]; cloudName = cloudName.split("\"")[1]; clouds.add(cloudName); } return clouds; }
@Override public void migrate(Connection connection) throws Exception { try(Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("user_sessions")) .column(field("user_session_id", SQLDataType.BIGINT.identity(true))) .column(field("user_id", SQLDataType.BIGINT.nullable(false))) .column(field("token", SQLDataType.VARCHAR(36).nullable(false))) .column(field("remote_address", SQLDataType.VARCHAR(255))) .column(field("user_agent", SQLDataType.VARCHAR(255))) .column(field("created_at", SQLDataType.TIMESTAMP.nullable(false))) .constraints( constraint().primaryKey(field("user_session_id")), constraint().foreignKey(field("user_id")).references(table("users"), field("user_id")) ).getSQL(); stmt.execute(ddl); } }
@Override public void migrate(Connection connection) throws Exception { try (Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("roles")) .column(field("role_id", SQLDataType.BIGINT.identity(true))) .column(field("name", SQLDataType.VARCHAR(100).nullable(false))) .column(field("description", SQLDataType.VARCHAR(100).nullable(false))) .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false))) .constraints( constraint().primaryKey(field("role_id")), constraint().unique(field("name")) ).getSQL(); stmt.execute(ddl); } }
private String createDDL(DefaultConfiguration config) { DSLContext create = DSL.using(config); String ddl = create.alterTable(table("oidc_invitations")) .renameColumn(field("oidc_sub")) .to(field("oidc_payload", SQLDataType.CLOB)) .getSQL(); if (create.configuration().dialect() == SQLDialect.MYSQL) { Matcher m = Pattern.compile("\\s+RENAME\\s+COLUMN\\s+(\\w+)\\s+TO\\s+", Pattern.CASE_INSENSITIVE).matcher(ddl); StringBuffer sb = new StringBuffer(); if (m.find()) { m.appendReplacement(sb, " change " + m.group(1) + " "); m.appendTail(sb); sb.append(" text not null"); ddl = sb.toString(); } } return ddl; }
@Provides @Singleton static DSLContext dbContext( DataSource dataSource, @ForDatabase ListeningExecutorService dbExecutor) { Configuration configuration = new DefaultConfiguration() .set(dbExecutor) .set(SQLDialect.MYSQL) .set(new Settings().withRenderSchema(false)) .set(new DataSourceConnectionProvider(dataSource)) .set(DatabaseUtil.sfmRecordMapperProvider()); DSLContext ctx = DSL.using(configuration); // Eagerly trigger JOOQ classinit for better startup performance. ctx.select().from("curio_server_framework_init").getSQL(); return ctx; }
@Override public void migrate(Connection connection) throws Exception { try(Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.alterTable(table("oidc_invitations")) .renameColumn(field("oidc_sub")).to(field("oidc_payload", SQLDataType.CLOB)) .getSQL(); if (create.configuration().dialect() == SQLDialect.MYSQL) { Matcher m = Pattern.compile("\\s+RENAME\\s+COLUMN\\s+(\\w+)\\s+TO\\s+", Pattern.CASE_INSENSITIVE).matcher(ddl); StringBuffer sb = new StringBuffer(); if (m.find()) { m.appendReplacement(sb, " change " + m.group(1) + " "); m.appendTail(sb); sb.append(" text not null"); ddl = sb.toString(); } } stmt.execute(ddl); } }
@Override public void saveTaxCodes(final Iterable<EasyTaxTaxCode> taxCodes) throws SQLException { final DateTime now = new DateTime(); execute(dataSource.getConnection(), new WithConnectionCallback<Void>() { @Override public Void withConnection(final Connection conn) throws SQLException { DSL.using(conn, dialect, settings).transaction(new TransactionalRunnable() { @Override public void run(final Configuration configuration) throws Exception { final DSLContext dslContext = DSL.using(configuration); for (EasyTaxTaxCode taxCode : taxCodes) { DateTime date = taxCode.getCreatedDate() != null ? taxCode.getCreatedDate() : now; saveTaxCodeInternal(taxCode, date, dslContext); } } }); return null; } }); }
@Override public void migrate(Connection connection) throws Exception { try(Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("oidc_providers")) .column(field("oidc_provider_id", SQLDataType.BIGINT.identity(true))) .column(field("name", SQLDataType.VARCHAR(100).nullable(false))) .column(field("api_key", SQLDataType.VARCHAR(100).nullable(false))) .column(field("api_secret", SQLDataType.VARCHAR(100).nullable(false))) .column(field("scope", SQLDataType.VARCHAR(100))) .column(field("response_type", SQLDataType.VARCHAR(100))) .column(field("authorization_endpoint", SQLDataType.VARCHAR(100).nullable(false))) .column(field("token_endpoint", SQLDataType.VARCHAR(100).nullable(false))) .column(field("token_endpoint_auth_method", SQLDataType.VARCHAR(10).nullable(false))) .constraints( constraint().primaryKey(field("oidc_provider_id")) ).getSQL(); stmt.execute(ddl); } }
@Override public void migrate(Connection connection) throws Exception { try (Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("assignments")) .column(field("group_id", SQLDataType.BIGINT.nullable(false))) .column(field("role_id", SQLDataType.BIGINT.nullable(false))) .column(field("realm_id", SQLDataType.BIGINT.nullable(false))) .constraints( constraint().primaryKey(field("group_id"), field("role_id"), field("realm_id")), constraint().foreignKey(field("group_id")).references(table("groups"), field("group_id")), constraint().foreignKey(field("role_id")).references(table("roles"), field("role_id")), constraint().foreignKey(field("realm_id")).references(table("realms"), field("realm_id")) ).getSQL(); stmt.execute(ddl); } }
@Override public void migrate(Connection connection) throws Exception { try (Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("realms")) .column(field("realm_id", SQLDataType.BIGINT.identity(true))) .column(field("name", SQLDataType.VARCHAR(100).nullable(false))) .column(field("url", SQLDataType.VARCHAR(100).nullable(false))) .column(field("application_id", SQLDataType.BIGINT.nullable(false))) .column(field("description", SQLDataType.VARCHAR(100).nullable(false))) .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false))) .constraints( constraint().primaryKey(field("realm_id")), constraint().unique(field("name")), constraint().foreignKey(field("application_id")).references(table("applications"), field("application_id")) ).getSQL(); stmt.execute(ddl); } }
@Override public void migrate(Connection connection) throws Exception { try (Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("applications")) .column(field("application_id", SQLDataType.BIGINT.identity(true))) .column(field("name", SQLDataType.VARCHAR(100).nullable(false))) .column(field("virtual_path", SQLDataType.VARCHAR(50).nullable(false))) .column(field("pass_to", SQLDataType.VARCHAR(255).nullable(false))) .column(field("top_page", SQLDataType.VARCHAR(255).nullable(false))) .column(field("description", SQLDataType.VARCHAR(100).nullable(false))) .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false))) .constraints( constraint().primaryKey(field("application_id")), constraint().unique(field("virtual_path")) ).getSQL(); stmt.execute(ddl); } }
@Override public void migrate(Connection connection) throws Exception { try(Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("oidc_users")) .column(field("oidc_provider_id", SQLDataType.BIGINT.nullable(false))) .column(field("user_id", SQLDataType.BIGINT.nullable(false))) .column(field("oidc_sub", SQLDataType.VARCHAR(255).nullable(false))) .constraints( constraint().primaryKey(field("oidc_provider_id"), field("user_id")), constraint().foreignKey(field("oidc_provider_id")).references(table("oidc_providers"), field("oidc_provider_id")), constraint().foreignKey(field("user_id")).references(table("users"), field("user_id")) ).getSQL(); stmt.execute(ddl); } }
@Override public void migrate(Connection connection, DSLContext create) throws Exception { create.insertInto(v1_AUTHOR).values(Sequences.v1_S_AUTHOR_ID.nextval(), "George", "Orwell", "1903-06-25", 1903, null).execute(); create.insertInto(v1_AUTHOR).values(Sequences.v1_S_AUTHOR_ID.nextval(), "Paulo", "Coelho", "1947-08-24", 1947, null).execute(); connection.setAutoCommit(false); create.insertInto(v1_BOOK).values(1, 1, "1984").execute(); create.insertInto(v1_BOOK).values(2, 1, "Animal Farm").execute(); create.insertInto(v1_BOOK).values(3, 2, "O Alquimista").execute(); create.insertInto(v1_BOOK).values(4, 2, "Brida").execute(); connection.commit(); }
public boolean submit(DSLContext contextIn, long taskId, String callbackUrl, String taskName, String taskInfo, Integer retry) { return contextIn.insertInto(JUICE_TASK) .set(JUICE_TASK.TASK_ID, taskId) .set(JUICE_TASK.TASK_NAME, taskName) .set(JUICE_TASK.TASK_STATUS, TaskResult.Result.NOT_START.getType()) .set(JUICE_TASK.CALLBACK_URL, callbackUrl) .set(JUICE_TASK.MESSAGE, "task is submit") .set(JUICE_TASK.TASK_INFO_JSON, taskInfo) .set(JUICE_TASK.RETRY, (null == retry || retry <= 0) ? 0 : 1) .execute() > 0; }
private ValidationResult notIn(final DSLContext context, final ColumnPermutation lhs, final ColumnPermutation rhs) { final int violators = context.selectCount().from( context.select(fields(lhs)) .from(tables(lhs, rhs)) .where(notNull(lhs)) .and(row(fields(lhs)).notIn(select(fields(rhs)).from(tables(rhs)).where(notNull(rhs)))) .limit(1) .asTable("indCheck")) .fetchOne().value1(); return new DefaultValidationResult(violators == 0); }
private ValidationResult except(final DSLContext context, final ColumnPermutation lhs, final ColumnPermutation rhs) { final int violators = context.selectCount().from( select(fields(lhs)) .from(tables(lhs)) .where(notNull(lhs)) .except(select(fields(rhs)).from(tables(rhs))) .limit(1) ).fetchOne().value1(); return new DefaultValidationResult(violators == 0); }
DSLContext create(final Connection connection) { try { final SQLDialect dialect = JDBCUtils.dialect(connection.getMetaData().getURL()); if (dialect == SQLDialect.HSQLDB) { return hsqldb(connection); } return defaultContext(connection); } catch (final SQLException e) { return defaultContext(connection); } }
static void loadCsv(final DSLContext context, final String fileName, final String tableName) throws IOException { try (InputStream in = Helper.class.getResourceAsStream(fileName)) { final Loader<Record> result = context.loadInto(table(name(tableName))) .loadCSV(in) .fields(LoaderFieldContext::field) .execute(); assertThat(result.errors()).as(getMessage(result.errors())).isEmpty(); } }
/** * Load a CSV file with {@code NULL} values which are represented as star {@code *}. * * <p>This method cannot possibly be merged with the other one since the {@code nullString(...)} * API is only available when explicitly passing a collection of o fields to {@code loadCsv}, * which is actually quite redundant.</p> */ static void loadCsvWithNulls(final DSLContext context, final String fileName, final String tableName, final List<String> fieldNames) throws IOException { try (InputStream in = Helper.class.getResourceAsStream(fileName)) { final Loader<Record> result = context.loadInto(table(name(tableName))) .loadCSV(in) .fields(toFields(tableName, fieldNames)) .nullString("*") .execute(); assertThat(result.errors()).as(getMessage(result.errors())).isEmpty(); } }
@Override public DSLContext get() { DSLContext dslContext = dslContexts.get(); if (dslContext != null) { return dslContext; } else { throw new IllegalStateException("UnitOfWork is not running."); } }
@Override public void begin() { if (isActive()) { throw new IllegalStateException("UnitOfWork has already been started."); } else { DSLContext dslContext = DSL.using(configuration); dslContexts.set(dslContext); } }
@Override public void end() { DSLContext dslContext = dslContexts.get(); if (dslContext != null) { try { dslContext.close(); } finally { dslContexts.remove(); } } }
@Override public void migrate(Connection connection) throws Exception { try (Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("memberships")) .column(field("user_id", SQLDataType.BIGINT.nullable(false))) .column(field("group_id", SQLDataType.BIGINT.nullable(false))) .constraints( constraint().primaryKey(field("user_id"), field("group_id")), constraint().foreignKey(field("user_id")).references(table("users"), field("user_id")), constraint().foreignKey(field("group_id")).references(table("groups"), field("group_id")) ).getSQL(); stmt.execute(ddl); } }
public static Map<String, SettingsRecord> getAllGuildSettings(DSLContext database) { HashMap<String, SettingsRecord> map = new HashMap<>(); Result<SettingsRecord> records = database.selectFrom(Tables.SETTINGS).fetch(); for (SettingsRecord record: records) { map.put(record.getGuildid(), record); } return map; }
@Override public void migrate(Connection connection) throws Exception { try(Statement stmt = connection.createStatement()) { DSLContext create = DSL.using(connection); String ddl = create.createTable(table("user_profile_fields")) .column(field("user_profile_field_id", SQLDataType.BIGINT.identity(true))) .column(field("name", SQLDataType.VARCHAR(100).nullable(false))) .column(field("json_name", SQLDataType.VARCHAR(100).nullable(true))) .column(field("is_required", SQLDataType.BOOLEAN.nullable(false))) .column(field("is_identity", SQLDataType.BOOLEAN.nullable(false))) .column(field("regular_expression", SQLDataType.VARCHAR(255).nullable(true))) .column(field("min_length", SQLDataType.SMALLINT.nullable(true))) .column(field("max_length", SQLDataType.SMALLINT.nullable(true))) .column(field("position", SQLDataType.TINYINT.nullable(false))) .constraints( constraint().primaryKey(field("user_profile_field_id")) ).getSQL(); stmt.execute(ddl); ddl = create.createTable(table("user_profile_values")) .column(field("user_profile_field_id", SQLDataType.BIGINT.nullable(false))) .column(field("user_id", SQLDataType.BIGINT.nullable(false))) .column(field("value", SQLDataType.VARCHAR(255).nullable(false))) .constraints( constraint().primaryKey(field("user_profile_field_id"), field("user_id")), constraint().foreignKey(field("user_profile_field_id")) .references(table("user_profile_fields"), field("user_profile_field_id")), constraint().foreignKey(field("user_id")) .references(table("users"), field("user_id")) ) .getSQL(); stmt.execute(ddl); } }
@Override public void migrate(Connection connection, DSLContext create) { create.alterTable(v1_AUTHOR) .alterColumn(v1_AUTHOR.v1_FIRST_NAME) .set(SQLDataType.VARCHAR.length(100)) .execute(); create.alterTable(v1_AUTHOR) .addColumn("GENDER", SQLDataType.INTEGER) .execute(); }
@Override public void handle(RoutingContext ctx, Server server, DiscordBot bot, DSLContext database) { HttpServerRequest request = ctx.request(); HttpServerResponse response = ctx.response(); String userId = ServerUtils.authUser(request, response, config); if (userId == null) { return; } OauthsecretsRecord record = database.selectFrom(Tables.OAUTHSECRETS) .where(Tables.OAUTHSECRETS.USERID.eq(userId)) .fetchAny(); if (record == null) { response.setStatusCode(403); response.end(); return; } List<Guild> jimGuilds = bot.getGuilds(); String[] userGuilds = getGuildsOfUser(record); List<GuildEntity> result = jimGuilds.stream() .filter((guild) -> isInUserGuilds(guild, userGuilds)) .map((guild) -> { String url = guild.getIconUrl(); return new GuildEntity(guild.getId(), guild.getName(), url); }) .collect(Collectors.toList()); Gson gson = new Gson(); response.putHeader("Content-Type", "application/json"); response.end(gson.toJson(result)); }
public Server(DiscordBot bot, DSLContext database, Config config) { this.bot = bot; this.database = database; this.config = config; vertx = Vertx.vertx(); Router router = Router.router(vertx); router.route().handler(BodyHandler.create()); router.get("/login").handler(new Login(bot, database, this, config)); router.get("/guilds").handler(new Guilds(bot, database, this, config)); router.get("/self").handler(new Self(bot, database, this, config)); router.get("/guilds/:guildId/settings").handler(new GetGuildSettings(bot, database, this, config)); router.post("/guilds/:guildId/settings").handler(new PostGuildSettings(bot, database, this, config)); router.get("/guilds/:guildId/messageStats").handler(new GuildMessageStats(bot, database, this, config)); router.get("/guilds/:guildId/messageStats/channels/:channelId").handler(new ChannelMessageStats(bot, database, this, config)); router.get("/guilds/:guildId/messageStats/channels").handler(new ChannelsMessageStats(bot, database, this, config)); router.options().handler((ctx) -> { HttpServerResponse response = ctx.response(); response.putHeader("Access-Control-Allow-Origin", config.server.base_url); response.putHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE"); response.putHeader("Access-Control-Allow-Headers", "token"); response.end(); }); vertx.createHttpServer() .requestHandler((request) -> { request.response().putHeader("Access-Control-Allow-Origin", config.server.base_url); router.accept(request); }) .listen(config.server.port, "0.0.0.0"); log.info("Started web server."); }