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 vote down vote up 
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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
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 vote down vote up 
@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 vote down vote up 
@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 vote down vote up 
@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();
    }
}