Java Code Examples for org.flywaydb.core.api.migration.Context#getConnection()

The following examples show how to use org.flywaydb.core.api.migration.Context#getConnection() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: V20190406_1__MigrateBugRelation.java From mycollab with GNU Affero General Public License v3.0

6 votes

public void migrate(Context context) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true));
    SimpleJdbcInsert insertOp = new SimpleJdbcInsert(jdbcTemplate).withTableName("m_prj_ticket_relation");

    List<Map<String, Object>> rows = jdbcTemplate.queryForList("SELECT * FROM m_tracker_related_bug");
    rows.forEach(row -> {
        Map<String, Object> parameters = new HashMap<>(6);
        parameters.put("ticketId", row.get("bugid"));
        parameters.put("ticketType", "Project-Bug");
        parameters.put("type", "Project-Bug");
        parameters.put("typeId", row.get("relatedid"));
        parameters.put("rel", row.get("relatetype"));
        parameters.put("comment", row.get("comment"));
        insertOp.execute(parameters);
    });
    jdbcTemplate.execute("DROP TABLE `m_tracker_related_bug`;");
}

Example 2

Source File: V5__CreatePermissions.java From bouncr with Eclipse Public License 1.0

6 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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);
    }
}

Example 3

Source File: V19__CreateUserSessions.java From bouncr with Eclipse Public License 1.0

6 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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().unique(field("token")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);
    }
}

Example 4

Source File: V2__CreateGroups.java From bouncr with Eclipse Public License 1.0

6 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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);
    }
}

Example 5

Source File: R__Hibernate_Sequence.java From spring-cloud-dataflow with Apache License 2.0

6 votes

@Override
public void migrate(Context context) throws Exception {
	logger.info("About to check if mssql hibernate_sequence needs fix from table to a sequence");
	try {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true));
		// in case we have old wrongly created table, this command should succeed
		jdbcTemplate.execute("select 1 from hibernate_sequence");
		fixHibernateSequence = true;
		logger.info("Looks like we have hibernate_sequence table, initiate fix");
	}
	catch (Exception e) {
		logger.debug("Unable to query hibernate_sequence table, looks like we have a proper sequence", e);
	}
	// will result call to get commands from this class and then we choose which ones to run
	super.migrate(context);
}

Example 6

Source File: V3__CreateApplications.java From bouncr with Eclipse Public License 1.0

6 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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("name")),
                        constraint().unique(field("virtual_path"))
                ).getSQL();

        stmt.execute(ddl);
    }

}

Example 7

Source File: V8__CreateRolePermissions.java From bouncr with Eclipse Public License 1.0

6 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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")).onDeleteCascade(),
                        constraint().foreignKey(field("permission_id")).references(table("permissions"), field("permission_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);
    }
}

Example 8

Source File: V15__CreateOidcUsers.java From bouncr with Eclipse Public License 1.0

6 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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")).onDeleteCascade(),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);
    }
}

Example 9

Source File: R__Hibernate_Sequence.java From spring-cloud-skipper with Apache License 2.0

6 votes

@Override
public void migrate(Context context) throws Exception {
	logger.info("About to check if mssql hibernate_sequence needs fix from table to a sequence");
	try {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true));
		// in case we have old wrongly created table, this command should succeed
		jdbcTemplate.execute("select 1 from hibernate_sequence");
		fixHibernateSequence = true;
		logger.info("Looks like we have hibernate_sequence table, initiate fix");
	}
	catch (Exception e) {
		logger.debug("Unable to query hibernate_sequence table, looks like we have a proper sequence", e);
	}
	// will result call to get commands from this class and then we choose which ones to run
	super.migrate(context);
}

Example 10

Source File: V6__CreateRealms.java From bouncr with Eclipse Public License 1.0

6 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);
    }
}

Example 11

Source File: V18__CreateUserLocks.java From bouncr with Eclipse Public License 1.0

6 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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("lock_level", SQLDataType.VARCHAR(10).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")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);
    }
}

Example 12

Source File: V1__CreateUserLicense.java From bouncr with Eclipse Public License 1.0

5 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        createUserLicense(create, stmt);
        createLastActivity(create, stmt);
    }
}

Example 13

Source File: V16__CreateOidcApplications.java From bouncr with Eclipse Public License 1.0

5 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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")),
                        constraint().unique(field("name"))
                )
                .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")).onDeleteCascade(),
                        constraint().foreignKey(field("permission_id"))
                                .references(table("permissions"), field("permission_id")).onDeleteCascade()
                )
                .getSQL();
        stmt.execute(ddl);
    }
}

Example 14

Source File: V20__CreateCerts.java From bouncr with Eclipse Public License 1.0

5 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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().unique(field("serial")),
                        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")).onDeleteCascade()
                )
                .getSQL();
        stmt.execute(ddl);
    }
}

Example 15

Source File: V21__CreateUserProfiles.java From bouncr with Eclipse Public License 1.0

5 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    createProfileFields(connection);
    createProfileValues(connection);
    createProfileVerification(connection);
}

Example 16

Source File: V22_1_14_8__MigrateMailchimp.java From alf.io with GNU General Public License v3.0

5 votes

@Override
public void migrate(Context context) throws Exception {
    var jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true));
    Integer enabledCount = jdbcTemplate.queryForObject("select count(*) from plugin_configuration where plugin_id = 'alfio.mailchimp' and conf_name = 'enabled' and conf_value = 'true'", Integer.class);
    if (enabledCount == null || enabledCount == 0) {
        return;
    }

    DataSource dataSource = Objects.requireNonNull(jdbcTemplate.getDataSource());
    ExtensionRepository extensionRepository = QueryFactory.from(ExtensionRepository.class, "PGSQL", dataSource);
    ExtensionLogRepository extensionLogRepository = QueryFactory.from(ExtensionLogRepository.class, "PGSQL", dataSource);
    PluginRepository pluginRepository = QueryFactory.from(PluginRepository.class, "PGSQL", dataSource);
    ExtensionService extensionService = new ExtensionService(new ScriptingExecutionService(HttpClient.newHttpClient(), () -> Executors.newSingleThreadExecutor()), extensionRepository, extensionLogRepository, new DataSourceTransactionManager(dataSource), new ExternalConfiguration());

    extensionService.createOrUpdate(null, null, new Extension("-", "mailchimp", getMailChimpScript(), true));

    int extensionId = extensionRepository.getExtensionIdFor("-", "mailchimp");
    int apiKeyId = pluginRepository.getConfigurationMetadataIdFor(extensionId, "apiKey", "EVENT");
    int listIdId = pluginRepository.getConfigurationMetadataIdFor(extensionId, "listId", "EVENT");


    List<ConfValue> confValues = pluginRepository.findAllMailChimpConfigurationValues();

    for (ConfValue cv : confValues) {
        if(cv.value != null) {
            optionally(() ->jdbcTemplate.queryForObject("select org_id from event where id = "+cv.eventId, Integer.class))
                .ifPresent(orgId -> extensionRepository.insertSettingValue("apiKey".equals(cv.name) ? apiKeyId : listIdId, "-" + orgId + "-" + cv.eventId, cv.value));

        }
    }
}

Example 17

Source File: V2__InsertRandomUsers.java From code-examples with MIT License

5 votes

public void migrate(Context context) {

    final JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true));

    // Create 10 random users
    for (int i = 1; i <= 10; i++) {
      jdbcTemplate.execute(String.format("insert into test_user(username, first_name, last_name) "
                                             + "values('%[email protected]', 'Elvis_%d', 'Presley_%d')", i, i, i));
    }
  }

Example 18

Source File: V17__CreateInvitations.java From bouncr with Eclipse Public License 1.0

4 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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")).onDeleteCascade(),
                        constraint().foreignKey(field("group_id")).references(table("groups"), field("group_id")).onDeleteCascade()
                ).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(2048).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("oidc_invitation_id")),
                        constraint().foreignKey(field("invitation_id")).references(table("invitations"), field("invitation_id")).onDeleteCascade(),
                        constraint().foreignKey(field("oidc_provider_id")).references(table("oidc_providers"), field("oidc_provider_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);

    }
}

Example 19

Source File: V24__AddAccountLowerToUser.java From bouncr with Eclipse Public License 1.0

4 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try(Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE)) {
        DSLContext create = DSL.using(connection);
        // Users.Account
        addAccountLower(create, stmt);
        fillAccountLower(create, stmt);
        notNullAccountLower(create, stmt);

        // Groups.Name
        addNameLower(create, stmt, "groups");
        fillNameLower(create, stmt, "groups", "group_id");
        notNullNameLower(create, stmt, "groups");

        // Roles.Name
        addNameLower(create, stmt, "roles");
        fillNameLower(create, stmt, "roles", "role_id");
        notNullNameLower(create, stmt, "roles");

        // Permissions.Name
        addNameLower(create, stmt, "permissions");
        fillNameLower(create, stmt, "permissions", "permission_id");
        notNullNameLower(create, stmt, "permissions");

        // Applications.Name
        addNameLower(create, stmt, "applications");
        fillNameLower(create, stmt, "applications", "application_id");
        notNullNameLower(create, stmt, "applications");

        // Realms.Name
        addNameLower(create, stmt, "realms");
        fillNameLower(create, stmt, "realms", "realm_id");
        notNullNameLower(create, stmt, "realms");

        // OidcApplications.Name
        addNameLower(create, stmt, "oidc_applications");
        fillNameLower(create, stmt, "oidc_applications", "oidc_application_id");
        notNullNameLower(create, stmt, "oidc_applications");

        // OidcProviders.Name
        addNameLower(create, stmt, "oidc_providers");
        fillNameLower(create, stmt, "oidc_providers", "oidc_provider_id");
        notNullNameLower(create, stmt, "oidc_providers");
    }
}

Example 20

Source File: V12__CreateUserActions.java From bouncr with Eclipse Public License 1.0

4 votes

@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    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();
    }
}