Java 类org.jooq.impl.SQLDataType 实例源码

项目:bouncr    文件:V2__CreateGroups.java   
@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);
    }
}
项目:AdvancedDataProfilingSeminar    文件:DatabaseValidationTest.java   
@BeforeEach
void setUp() throws Exception {
  context = Helper.createInMemoryContext();

  context.createTable("tableA")
      .column("A", SQLDataType.INTEGER)
      .column("B", SQLDataType.INTEGER)
      .execute();

  context.createTable("tableB")
      .column("C", SQLDataType.INTEGER)
      .column("D", SQLDataType.INTEGER)
      .column("E", SQLDataType.INTEGER)
      .execute();

  Helper.loadCsv(context, "tableA.csv", "tableA");
  Helper.loadCsv(context, "tableB.csv", "tableB");
}
项目:filter-sort-jooq-api    文件:TransferTimingPageRepositoryImplPureJooq.java   
/**
 * Build select query
 *
 * @return selected records
 */
private SelectSelectStep<Record16<Long, Integer, Integer, String, String, String, String, Integer, Integer, String, String, String, Byte, Timestamp, Timestamp, Long>> basicSelectQuery() {
    return create.select(
        TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID,
        FILE.ID.as("fileId"),
        ATABLE.ID.as("atableId"),
        ATABLE.NAME.as("atableName"),
        USER.USERNAME,
        FILE.CODE,
        FILE_TYPE.NAME.as("fileTypeName"),
        TRANSFER_TIMING_VISUAL_COUNT,
        TRANSFER_TIMING_SOMETHING_COUNT,
        field(name("tableFromState", TRANSITION_STATE.NAME.getName()), SQLDataType.VARCHAR(50)).as("fromStatus"),
        field(name("tableToState", TRANSITION_STATE.NAME.getName()), SQLDataType.VARCHAR(50)).as("toStatus"),
        FILE_TRANSITION_HISTORY.LOCATION_IPV4,
        TRANSFER_TIMING_TRANSFER_STATUS,
        TRANSFER_TIMING_TRANSFER_STARTED_AT,
        TRANSFER_TIMING_TRANSFER_ENDED_AT,
        TRANSFER_TIMING_TRANSFER_TIME_SPENT_IN_SECONDS
    );
}
项目:filter-sort-jooq-api    文件:TransferTimingPageRepositoryImpl.java   
/**
 * Build select query
 *
 * @return selected records
 */
private SelectSelectStep<Record16<Long, Integer, Integer, String, String, String, String, Integer, Integer, String, String, String, Byte, Timestamp, Timestamp, Long>> basicSelectQuery() {
    return create.select(
        TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID,
        FILE.ID.as("fileId"),
        ATABLE.ID.as("atableId"),
        ATABLE.NAME.as("atableName"),
        USER.USERNAME,
        FILE.CODE,
        FILE_TYPE.NAME.as("fileTypeName"),
        TRANSFER_TIMING_VISUAL_COUNT,
        TRANSFER_TIMING_SOMETHING_COUNT,
        field(name("tableFromState", TRANSITION_STATE.NAME.getName()), SQLDataType.VARCHAR(50)).as("fromStatus"),
        field(name("tableToState", TRANSITION_STATE.NAME.getName()), SQLDataType.VARCHAR(50)).as("toStatus"),
        FILE_TRANSITION_HISTORY.LOCATION_IPV4,
        TRANSFER_TIMING_TRANSFER_STATUS,
        TRANSFER_TIMING_TRANSFER_STARTED_AT,
        TRANSFER_TIMING_TRANSFER_ENDED_AT,
        TRANSFER_TIMING_TRANSFER_TIME_SPENT_IN_SECONDS
    );
}
项目:bouncr    文件:V13__CreateOidcProviders.java   
@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);
    }
}
项目:bouncr    文件:V9__CreateAssinments.java   
@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);
    }
}
项目:bouncr    文件:V6__CreateRealms.java   
@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);
    }
}
项目:bouncr    文件:V15__CreateOidcUsers.java   
@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);
    }
}
项目:bouncr    文件:V4__CreateRoles.java   
@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);
    }

}
项目:bouncr    文件:V19__CreateUserSessions.java   
@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);
    }
}
项目:bouncr    文件:V5__CreatePermissions.java   
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("permissions"))
                .column(field("permission_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("permission_id")),
                        constraint().unique(field("name"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
项目:bouncr    文件:V22__AlterInvitations.java   
@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);
    }
}
项目:bouncr    文件:V3__CreateApplications.java   
@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);
    }

}
项目:bouncr    文件:RenameColumnWorkaroundTest.java   
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;
}
项目:jooq-flyway-typesafe-migration    文件:V1__createAuthorAndBookTables.java   
@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();

}
项目:integrity-coded-database    文件:SchemaConverter.java   
private void addOCFColumns(final DBConnection icdb, final Table<?> table) {
    boolean converted = Arrays.stream(table.fields())
            .anyMatch(field -> field.getName().endsWith(Format.IC_SUFFIX));

    if (converted) {
        logger.debug("Table already converted. Skipping {}", table.getName());
        return;
    }

    // Loop through each field and create a corresponding column
    Arrays.stream(table.fields()).forEach(field -> {
        // Create a svc column
        icdb.getCreate().alterTable(table).add(field.getName() + Format.IC_SUFFIX, MySQLDataType.TINYBLOB)
                .execute();

        // Create a serial column
        icdb.getCreate().alterTable(table).add(field.getName() + Format.SERIAL_SUFFIX, SQLDataType.BIGINT)
                .execute();
    });
}
项目:killbill-analytics-plugin    文件:Aggregates.java   
public static AggregateFunction<?> of(final String input) {
    final Matcher matcher = MAGIC_REGEXP.matcher(input.toLowerCase());
    if (!matcher.find()) {
        // Not an aggregate?
        return null;
    }

    final SqlMapping sqlOp = SqlMapping.valueOf(matcher.group(1).toUpperCase());
    final boolean isDistinct = matcher.group(2) != null;
    // Note that the cast here is probably not correct (e.g. for count(distinct currency)),
    // but it's a workaround for jOOQ which requires Number fields for sum, avg, etc. below
    // Since we are simply generating the SQL, we don't really care what the actual column is.
    final Field<BigDecimal> field = DSL.fieldByName(SQLDataType.NUMERIC, matcher.group(3));

    return buildAggregateFunction(field, sqlOp, isDistinct);
}
项目:AdvancedDataProfilingSeminar    文件:NullHandlingTest.java   
@BeforeEach
void setUp() throws Exception {
  context = Helper.createInMemoryContext();

  context.createTable("tableX")
      .column("K", SQLDataType.CHAR(1))
      .column("L", SQLDataType.CHAR(1))
      .column("M", SQLDataType.CHAR(1))
      .column("N", SQLDataType.CHAR(1))
      .execute();

  Helper.loadCsvWithNulls(context, "tableX.csv", "tableX", asList("K", "L", "M", "N"));
}
项目:AdvancedDataProfilingSeminar    文件:QueryAcceptanceTest.java   
private void createPersonRelation() throws IOException {
  context.createTable(name("person"))
      .column("name", SQLDataType.VARCHAR(5))
      .column("second_name", SQLDataType.VARCHAR(5))
      .execute();

  Helper.loadCsv(context, "person.csv", "person");
}
项目:AdvancedDataProfilingSeminar    文件:TestDatabase.java   
private List<Field<String>> getFields() {
  final List<Field<String>> fields = new ArrayList<>();
  for (int index = 0; index < columnNames.size(); ++index) {
    fields.add(field(name(columnNames.get(index)), SQLDataType.VARCHAR(10)));
  }
  return fields;
}
项目:filter-sort-jooq-api    文件:TransferTimingPageRepositoryImplPureJooq.java   
/**
 * Build query with select and table
 *
 * @param selectedField select field(s)
 * @param <T>           record type
 * @return built query
 */
private <T extends Record> SelectOnConditionStep<T> buildQuery(final SelectSelectStep<T> selectedField) {
    return selectedField
        .from(TRANSFER_TIMING)
        .join(FILE_TRANSITION_HISTORY).on(FILE_TRANSITION_HISTORY.ID.eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID.getName()), SQLDataType.INTEGER)))
        .join(ATABLE).on(ATABLE.ID.eq(FILE_TRANSITION_HISTORY.FILE_LISTING_ATABLE_ID))
        .join(USER).on(USER.ID.eq(FILE_TRANSITION_HISTORY.LOCATION_USER_ID))
        .join(TRANSITION_STATE.asTable("tableFromState")).on(field(name("tableFromState", TRANSITION_STATE.ID.getName())).eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_TRANSITION_STATE_FROM_ID.getName()), SQLDataType.INTEGER)))
        .join(TRANSITION_STATE.asTable("tableToState")).on(field(name("tableToState", TRANSITION_STATE.ID.getName())).eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_TRANSITION_STATE_TO_ID.getName()), SQLDataType.INTEGER)))
        .join(FILE).on(FILE.ID.eq(FILE_TRANSITION_HISTORY.FILE_ID))
        .join(FILE_TYPE).on(FILE_TYPE.ID.eq(FILE.FILE_TYPE_ID));
}
项目:filter-sort-jooq-api    文件:TransferTimingPageRepositoryImpl.java   
/**
 * Build query with select and table
 *
 * @param selectedField select field(s)
 * @param <T>           record type
 * @return built query
 */
private <T extends Record> SelectOnConditionStep<T> buildQuery(final SelectSelectStep<T> selectedField) {
    return selectedField
        .from(TRANSFER_TIMING)
        .join(FILE_TRANSITION_HISTORY).on(FILE_TRANSITION_HISTORY.ID.eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID.getName()), SQLDataType.INTEGER)))
        .join(ATABLE).on(ATABLE.ID.eq(FILE_TRANSITION_HISTORY.FILE_LISTING_ATABLE_ID))
        .join(USER).on(USER.ID.eq(FILE_TRANSITION_HISTORY.LOCATION_USER_ID))
        .join(TRANSITION_STATE.asTable("tableFromState")).on(field(name("tableFromState", TRANSITION_STATE.ID.getName())).eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_TRANSITION_STATE_FROM_ID.getName()), SQLDataType.INTEGER)))
        .join(TRANSITION_STATE.asTable("tableToState")).on(field(name("tableToState", TRANSITION_STATE.ID.getName())).eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_TRANSITION_STATE_TO_ID.getName()), SQLDataType.INTEGER)))
        .join(FILE).on(FILE.ID.eq(FILE_TRANSITION_HISTORY.FILE_ID))
        .join(FILE_TYPE).on(FILE_TYPE.ID.eq(FILE.FILE_TYPE_ID));
}
项目:bouncr    文件:V20__CreateCerts.java   
@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("certs"))
                .column(field("cert_id", SQLDataType.BIGINT.identity(true)))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("serial",  SQLDataType.BIGINT.nullable(false)))
                .column(field("expires", SQLDataType.DATE.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("cert_id")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("cert_devices"))
                .column(field("cert_device_id", SQLDataType.BIGINT.identity(true)))
                .column(field("cert_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("device_token", SQLDataType.VARCHAR(36)))
                .constraints(
                        constraint().primaryKey(field("cert_device_id")),
                        constraint().foreignKey(field("cert_id")).references(table("certs"), field("cert_id"))
                )
                .getSQL();
        stmt.execute(ddl);
    }
}
项目:bouncr    文件:V18__CreateUserLocks.java   
@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("user_locks"))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("locked_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_id")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
项目:bouncr    文件:V17__CreateInvitations.java   
@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("invitations"))
                .column(field("invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("email", SQLDataType.VARCHAR(100)))
                .column(field("code", SQLDataType.VARCHAR(8)))
                .column(field("invited_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("invitation_id")),
                        constraint().unique(field("code"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("group_invitations"))
                .column(field("group_invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("invitation_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("group_id", SQLDataType.BIGINT.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("group_invitation_id")),
                        constraint().foreignKey(field("invitation_id")).references(table("invitations"), field("invitation_id")),
                        constraint().foreignKey(field("group_id")).references(table("groups"), field("group_id"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("oidc_invitations"))
                .column(field("oidc_invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("invitation_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("oidc_provider_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("oidc_sub", SQLDataType.VARCHAR(255).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("oidc_invitation_id")),
                        constraint().foreignKey(field("invitation_id")).references(table("invitations"), field("invitation_id")),
                        constraint().foreignKey(field("oidc_provider_id")).references(table("oidc_providers"), field("oidc_provider_id"))
                ).getSQL();
        stmt.execute(ddl);

    }
}
项目:bouncr    文件:V10__CreatePasswordCredentials.java   
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("password_credentials"))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("password", SQLDataType.VARBINARY(256).nullable(false)))
                .column(field("salt", SQLDataType.VARCHAR(16).nullable(false)))
                .column(field("initial", SQLDataType.BOOLEAN.nullable(false)))
                .column(field("created_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_id")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("otp_keys"))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))//
                .column(field("otp_key", SQLDataType.BINARY(20).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_id")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id"))
                ).getSQL();
        stmt.execute(ddl);
    }

}
项目:bouncr    文件:V14__CreateCertificateCredentials.java   
@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("certificate_credentials"))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("client_dn", SQLDataType.VARCHAR(150).nullable(false)))
                .column(field("certificate", SQLDataType.BLOB.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_id")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
项目:bouncr    文件:V16__CreateOidcApplications.java   
@Override
public void migrate(Connection connection) throws Exception {
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("oidc_applications"))
                .column(field("oidc_application_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("client_id", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("client_secret", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("private_key", SQLDataType.BLOB.nullable(false)))
                .column(field("public_key", SQLDataType.BLOB.nullable(false)))
                .column(field("home_url", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("callback_url", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("description", SQLDataType.VARCHAR(255).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("oidc_application_id"))
                )
                .getSQL();

        stmt.execute(ddl);

        ddl = create.createTable(table("oidc_application_scopes"))
                .column(field("oidc_application_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("permission_id", SQLDataType.BIGINT.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("oidc_application_id"), field("permission_id")),
                        constraint().foreignKey(field("oidc_application_id"))
                                .references(table("oidc_applications"), field("oidc_application_id")),
                        constraint().foreignKey(field("permission_id"))
                                .references(table("permissions"), field("permission_id"))
                )
                .getSQL();
        stmt.execute(ddl);
    }
}
项目:bouncr    文件:V8__CreateRolePermissions.java   
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("role_permissions"))
                .column(field("role_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("permission_id", SQLDataType.BIGINT.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("role_id"), field("permission_id")),
                        constraint().foreignKey(field("role_id")).references(table("roles"), field("role_id")),
                        constraint().foreignKey(field("permission_id")).references(table("permissions"), field("permission_id"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
项目:bouncr    文件:V21__CreateUserProfiles.java   
@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);
    }
}
项目:bouncr    文件:V7__CreateMemberships.java   
@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);
    }
}
项目:bouncr    文件:V1__CreateUsers.java   
@Override
public void migrate(Connection connection) throws Exception {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("users"))
                .column(field("user_id", SQLDataType.BIGINT.identity(true)))
                .column(field("account", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("email", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_id")),
                        constraint().unique(field("account")),
                        constraint().unique(field("email"))
                ).getSQL();
        stmt.execute(ddl);

        stmt.execute(
                create.createIndex(name("idx_users_01"))
                        .on(table("users"), field("account"))
                        .getSQL()
        );
        stmt.execute(
                create.createIndex(name("idx_users_02"))
                        .on(table("users"), field("email"))
                        .getSQL()
        );
    }
}
项目:jooq-flyway-typesafe-migration    文件:V3__increase.java   
@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();
}
项目:integrity-coded-database    文件:DBConverter.java   
/**
 * We need to augment the load query because MySQL is not smart enough to be able to load blob types in other encodings :(
 */
private static String convertToBlob(Table<?> table) {
    StringBuilder builder = new StringBuilder();
    Field<?>[] fields = table.fields();

    List<String> setValues = new ArrayList<>(fields.length);

    builder.append("(");

    Arrays.stream(fields)
            .forEach(field -> {
                DataType<?> dataType = field.getDataType().getSQLDataType();

                if (dataType.equals(SQLDataType.BLOB) || dataType.equals(SQLDataType.OTHER)) {
                    builder.append("@");
                    setValues.add(field.getName());
                }
                builder.append(field.getName())
                    .append(",");
            });

    builder.setLength(builder.length()-1);
    builder.append(") SET ");

    setValues.forEach(set -> builder.append(set)
            .append("=FROM_BASE64(@")
            .append(set)
            .append("),")
    );

    builder.setLength(builder.length()-1);
    builder.append(";");

    return builder.toString();
}
项目:integrity-coded-database    文件:SchemaConverter.java   
private void addOCTColumns(final DBConnection icdb, final Table<?> table) {
    boolean converted = Arrays.stream(table.fields()).anyMatch(field -> field.getName().equals(Format.IC_COLUMN));

    if (converted) {
        logger.debug("Table already converted. Skipping {}", table.getName());
        return;
    }

    // Create a svc column
    icdb.getCreate().alterTable(table).add(Format.IC_COLUMN, MySQLDataType.TINYBLOB).execute();

    // Create a serial column
    icdb.getCreate().alterTable(table).add(Format.SERIAL_COLUMN, SQLDataType.BIGINT).execute();
}
项目:bouncr    文件:V12__CreateUserActions.java   
@Override
public void migrate(Connection connection) throws Exception {
    DSLContext create = DSL.using(connection);
    try(Statement stmt = connection.createStatement()) {
        String ddl = create.createTable(table("actions"))
                .column(field("action_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("action_id")),
                        constraint().unique(field("name"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("user_actions"))
                .column(field("user_action_id", SQLDataType.BIGINT.identity(true)))
                .column(field("action_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("actor", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("actor_ip", SQLDataType.VARCHAR(50).nullable(false)))
                .column(field("options", SQLDataType.CLOB))
                .column(field("created_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_action_id"))
                ).getSQL();
        stmt.execute(ddl);
    }

    try (PreparedStatement stmt = connection.prepareStatement(create
            .insertInto(table("actions"))
            .columns(
                    field("action_id"),
                    field("name")
            )
            .values("?", "?")
            .getSQL())) {
        for (ActionType actionType : ActionType.values()) {
            stmt.setLong(1, actionType.getId());
            stmt.setString(2, actionType.getName());
            stmt.executeUpdate();
        }
        connection.commit();
    }
}
项目:dropwizard-jooq    文件:HSQLDBInit.java   
public static void initPublic(Map<String, String> properties) throws Exception {
    String url = properties.get("url");
    String username = properties.get("username");
    String password = properties.get("password");

    Properties connectionProps = new Properties();
    connectionProps.put("user", username);
    connectionProps.put("password", password);

    Class.forName("org.hsqldb.jdbc.JDBCDriver");
    Connection conn = DriverManager.getConnection(url, connectionProps);

    DSLContext jooq = DSL.using(conn);

    jooq.createTable(AUTHOR).column(AUTHOR.ID, SQLDataType.INTEGER).column(AUTHOR.NAME, SQLDataType.VARCHAR)
            .execute();

    jooq.execute("ALTER TABLE PUBLIC.author ADD PRIMARY KEY (id)");

    jooq.insertInto(AUTHOR).set(AUTHOR.ID, 1).set(AUTHOR.NAME, "Tariq").execute();

    jooq.execute("CREATE SCHEMA BUGRARA;");

    conn.commit();

    jooq = DSL.using(conn, new Settings().withRenderMapping(
            new RenderMapping().withSchemata(new MappedSchema().withInput("PUBLIC").withOutput("BUGRARA"))));

    jooq.createTable(AUTHOR).column(AUTHOR.ID, SQLDataType.INTEGER).column(AUTHOR.NAME, SQLDataType.VARCHAR)
            .execute();

    jooq.execute("ALTER TABLE BUGRARA.author ADD PRIMARY KEY (id)");

    jooq.insertInto(AUTHOR).set(AUTHOR.ID, 1).set(AUTHOR.NAME, "Narmeen").execute();

    conn.commit();

    conn.close();

}
项目:filter-sort-jooq-api    文件:FieldUtil.java   
/**
 * TODO Check if jOOQ has similar feature in DSL class
 * @param field
 * @param format
 * @return
 */
public static Field<String> dateFormat(Field<?> field, String format) {
    return DSL.field("date_format({0}, {1})", SQLDataType.VARCHAR,
        field, DSL.inline(format));
}