@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); } }
@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"); }
/** * 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 ); }
@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("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) 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); } }
@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("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); } }
@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 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); } }
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; }
@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(); }
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(); }); }
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); }
@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")); }
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"); }
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; }
/** * 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)); }
@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); } }
@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); } }
@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); } }
@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); } }
@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); } }
@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); } }
@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); } }
@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) 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); } }
@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() ); } }
@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(); }
/** * 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(); }
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(); }
@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(); } }
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(); }
/** * 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)); }