Incorrect v5 migration script for SQLServer

[iyerha]

Bug description
The migration script for sqlserver provided in spring-batch-core for version 5.0 is incorrect.

Environment
Spring Batch version: 5.0.1, Java version = 17.0.6_10, database=SQLServer 2019

Steps to reproduce
All bugs relate to the table BATCH_JOB_EXECUTION_PARAMS in migration-sqlserver.sql script under 5.0:

1. You cannot drop column DATE_VAL without first dropping the default constraint on it.
2. You cannot use MODIFY COLUMN to rename columns (TYPE_CD, KEY_NAME, STRING_VAL). You would need to use the sp_rename procedure as described here

[fmbenhassine]

Thank you for raising this. This is similar to another issue we had with PostgreSQL and Oracle. The SQL syntax is not the same for each database, which was assumed when creating the migration scripts.. I created an issue to improve the testing process for next releases, see #4289.

I will plan the fix for the next release. You are welcome to contribute if you managed to correctly migrate your schema to v5 on SQLServer.

[fmbenhassine]

The renaming of columns was updated to use SP_RENAME. However, the deletion of DATE_VAL was omitted from the script as there are several way of doing that which depend on the version of SQLServer.

The script was verified with the following test, which I keep here for a later reference:

package org.springframework.batch.core.test.repository;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.testcontainers.containers.MSSQLServerContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.utility.DockerImageName;

import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

/**
 * @author Mahmoud Ben Hassine
 */
@Testcontainers
class SQLServerMigrationScriptIntegrationTests {

	private static final DockerImageName SQLSERVER_IMAGE = DockerImageName
			.parse("mcr.microsoft.com/mssql/server:2019-CU20-ubuntu-20.04");

	@Container
	public static MSSQLServerContainer<?> sqlserver = new MSSQLServerContainer<>(SQLSERVER_IMAGE).acceptLicense();

	@Test
	void migrationScriptShouldBeValid() {
		SQLServerDataSource dataSource = new SQLServerDataSource();
		dataSource.setUser(sqlserver.getUsername());
		dataSource.setPassword(sqlserver.getPassword());
		dataSource.setURL(sqlserver.getJdbcUrl());

		ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
		databasePopulator.addScript(new ClassPathResource("/org/springframework/batch/core/schema-sqlserver-v4.sql"));
		databasePopulator.addScript(new ClassPathResource("/org/springframework/batch/core/migration/5.0/migration-sqlserver.sql"));

		Assertions.assertDoesNotThrow(() -> databasePopulator.execute(dataSource));
	}

}