SQLServerDatabase.java
/*-
* ========================LICENSE_START=================================
* flyway-sqlserver
* ========================================================================
* Copyright (C) 2010 - 2026 Red Gate Software Ltd
* ========================================================================
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* =========================LICENSE_END==================================
*/
package org.flywaydb.database.sqlserver;
import static org.flywaydb.core.internal.database.base.DatabaseConstants.*;
import org.flywaydb.core.api.FlywayException;
import org.flywaydb.core.api.MigrationVersion;
import org.flywaydb.core.api.configuration.Configuration;
import org.flywaydb.core.extensibility.CleanModePlugin;
import org.flywaydb.core.extensibility.Tier;
import org.flywaydb.core.internal.command.clean.CleanModel.Mode;
import org.flywaydb.core.internal.configuration.ConfigUtils;
import org.flywaydb.core.internal.database.base.Database;
import org.flywaydb.core.internal.database.base.Schema;
import org.flywaydb.core.internal.database.base.Table;
import org.flywaydb.core.internal.jdbc.JdbcConnectionFactory;
import org.flywaydb.core.internal.jdbc.StatementInterceptor;
import org.flywaydb.core.internal.sqlscript.Delimiter;
import org.flywaydb.core.internal.util.StringUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
public class SQLServerDatabase extends Database<SQLServerConnection> {
public SQLServerDatabase(final Configuration configuration,
final JdbcConnectionFactory jdbcConnectionFactory,
final StatementInterceptor statementInterceptor) {
super(configuration, jdbcConnectionFactory, statementInterceptor);
}
@Override
protected SQLServerConnection doGetConnection(final Connection connection) {
return new SQLServerConnection(this, connection);
}
@Override
public void ensureSupported(final Configuration configuration) {
if (isAzure()) {
ensureDatabaseIsRecentEnough("11.0");
ensureDatabaseNotOlderThanOtherwiseRecommendUpgradeToFlywayEdition("12.0", Tier.PREMIUM, configuration);
recommendFlywayUpgradeIfNecessary("12.0");
} else {
ensureDatabaseIsRecentEnough("10.0");
ensureDatabaseNotOlderThanOtherwiseRecommendUpgradeToFlywayEdition("14.0", Tier.PREMIUM, configuration);
recommendFlywayUpgradeIfNecessary("17.0");
}
}
@Override
protected String computeVersionDisplayName(final MigrationVersion version) {
if (isAzure()) {
return "Azure v" + getVersion().getMajorAsString();
}
if (getVersion().isAtLeast("8")) {
if ("8".equals(getVersion().getMajorAsString())) {
return "2000";
}
if ("9".equals(getVersion().getMajorAsString())) {
return "2005";
}
if ("10".equals(getVersion().getMajorAsString())) {
if ("0".equals(getVersion().getMinorAsString())) {
return "2008";
}
return "2008 R2";
}
if ("11".equals(getVersion().getMajorAsString())) {
return "2012";
}
if ("12".equals(getVersion().getMajorAsString())) {
return "2014";
}
if ("13".equals(getVersion().getMajorAsString())) {
return "2016";
}
if ("14".equals(getVersion().getMajorAsString())) {
return "2017";
}
if ("15".equals(getVersion().getMajorAsString())) {
return "2019";
}
if ("16".equals(getVersion().getMajorAsString())) {
return "2022";
}
}
return super.computeVersionDisplayName(version);
}
@Override
public Delimiter getDefaultDelimiter() {
return Delimiter.GO;
}
@Override
protected String doGetCurrentUser() throws SQLException {
return getMainConnection().getJdbcTemplate().queryForString("SELECT SUSER_SNAME()");
}
@Override
public boolean supportsDdlTransactions() {
return true;
}
@Override
public String getBooleanTrue() {
return "1";
}
@Override
public String getBooleanFalse() {
return "0";
}
private String escapeIdentifier(final String identifier) {
return StringUtils.replaceAll(identifier, getCloseQuote(), getEscapedQuote());
}
@Override
public String doQuote(final String identifier) {
return getOpenQuote() + escapeIdentifier(identifier) + getCloseQuote();
}
@Override
public String getOpenQuote() {
return "[";
}
@Override
public String getCloseQuote() {
return "]";
}
@Override
public String getEscapedQuote() {
return "]]";
}
@Override
public boolean catalogIsSchema() {
return false;
}
@Override
public boolean useSingleConnection() {
return true;
}
@Override
public String getRawCreateScript(final Table table, final boolean baseline) {
final String filegroup = isAzure() || configuration.getTablespace() == null
? ""
: " ON \"" + configuration.getTablespace() + "\"";
return "CREATE TABLE "
+ table
+ " (\n"
+ " [installed_rank] INT NOT NULL,\n"
+ " ["
+ "version] NVARCHAR(50),\n"
+ " [description] NVARCHAR(200),\n"
+ " [type] NVARCHAR(20) NOT NULL,\n"
+ " [script] NVARCHAR(1000) NOT NULL,\n"
+ " [checksum] INT,\n"
+ " [installed_by] NVARCHAR(100) NOT NULL,\n"
+ " [installed_on] DATETIME NOT NULL DEFAULT GETDATE(),\n"
+ " [execution_time] INT NOT NULL,\n"
+ " [success] BIT NOT NULL\n"
+ ")"
+ filegroup
+ ";\n"
+ (baseline ? getBaselineStatement(table) + ";\n" : "")
+ "ALTER TABLE "
+ table
+ " ADD CONSTRAINT ["
+ table.getName()
+ "_pk] PRIMARY KEY ([installed_rank]);\n"
+ "CREATE INDEX ["
+ table.getName()
+ "_s_idx] ON "
+ table
+ " ([success]);\n"
+ "GO\n";
}
boolean isAzure() {
return getMainConnection().isAzure();
}
SQLServerEngineEdition getEngineEdition() {
return getMainConnection().getEngineEdition();
}
String getServerName() {
return getMainConnection().getServerName();
}
boolean supportsTemporalTables() {
// SQL Server 2016+, or Azure (which has different versioning)
return isAzure() || getVersion().isAtLeast("13.0");
}
protected boolean supportsPartitions() {
return isAzure()
|| SQLServerEngineEdition.ENTERPRISE.equals(getEngineEdition())
|| getVersion().isAtLeast("13");
}
protected boolean supportsSequences() {
return getVersion().isAtLeast("11");
}
protected boolean supportsSynonyms() {
return true;
}
protected boolean supportsRules() {
return true;
}
protected boolean supportsTypes() {
return true;
}
protected boolean supportsServiceBrokers() {
return true;
}
protected boolean supportsTriggers() {
return true;
}
protected boolean supportsAssemblies() {
return true;
}
protected boolean hasXmlSchemaCollections() {
return true;
}
/**
* Cleans all the objects in this database that need to be cleaned after cleaning schemas.
*
* @param schemas The list of schemas managed by Flyway
* @throws SQLException when the clean failed.
*/
@Override
protected void doCleanPostSchemas(final Schema[] schemas) throws SQLException {
if (supportsPartitions()) {
for (final String statement : cleanPartitionSchemes()) {
jdbcTemplate.execute(statement);
}
for (final String statement : cleanPartitionFunctions()) {
jdbcTemplate.execute(statement);
}
}
if (supportsAssemblies()) {
for (final String statement : cleanAssemblies()) {
jdbcTemplate.execute(statement);
}
}
if (supportsTypes()) {
for (final String statement : cleanTypes(schemas)) {
jdbcTemplate.execute(statement);
}
}
final String cleanMode = ConfigUtils.getCleanModel(configuration).getMode();
if (Mode.ALL.name().equalsIgnoreCase(cleanMode)) {
final CleanModePlugin cleanModePlugin = configuration.getPluginRegister()
.getInstancesOf(CleanModePlugin.class)
.stream()
.filter(p -> p.handlesMode(Mode.valueOf(cleanMode)))
.filter(p -> p.handlesDatabase(this))
.findFirst()
.orElseThrow(() -> new FlywayException("No plugin found to handle clean mode "
+ cleanMode
+ " for SQLServer."));
cleanModePlugin.cleanDatabasePostSchema(this, jdbcTemplate);
}
}
/**
* @param schemas The list of schemas managed by Flyway
* @return The drop statements.
* @throws SQLException when the clean statements could not be generated.
*/
private List<String> cleanTypes(final Schema[] schemas) throws SQLException {
final List<String> statements = new ArrayList<>();
String schemaList = Arrays.stream(schemas).map(s -> "'" + s.getName() + "'").collect(Collectors.joining(","));
if (schemaList.isEmpty()) {
schemaList = "''";
}
final List<Map<String, String>> typesAndSchemas = jdbcTemplate.queryForList(""
+ "SELECT t.name as type_name, s.name as schema_name "
+ "FROM sys.types t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id "
+ "WHERE t.is_user_defined = 1 AND s.name IN ("
+ schemaList
+ ")");
for (final Map<String, String> typeAndSchema : typesAndSchemas) {
statements.add("DROP TYPE " + quote(typeAndSchema.get("schema_name"), typeAndSchema.get("type_name")));
}
return statements;
}
/**
* @return The drop statements.
* @throws SQLException when the clean statements could not be generated.
*/
private List<String> cleanPartitionSchemes() throws SQLException {
final List<String> statements = new ArrayList<>();
final List<String> partitionSchemeNames = jdbcTemplate.queryForStringList(
"SELECT name FROM sys.partition_schemes");
for (final String partitionSchemeName : partitionSchemeNames) {
statements.add("DROP PARTITION SCHEME " + quote(partitionSchemeName));
}
return statements;
}
/**
* @return The drop statements.
* @throws SQLException when the clean statements could not be generated.
*/
private List<String> cleanAssemblies() throws SQLException {
final List<String> statements = new ArrayList<>();
final List<String> assemblyNames = jdbcTemplate.queryForStringList(
"SELECT * FROM sys.assemblies WHERE is_user_defined=1");
for (final String assemblyName : assemblyNames) {
statements.add("DROP ASSEMBLY " + quote(assemblyName));
}
return statements;
}
/**
* @return The drop statements.
* @throws SQLException when the clean statements could not be generated.
*/
private List<String> cleanPartitionFunctions() throws SQLException {
final List<String> statements = new ArrayList<>();
final List<String> partitionFunctionNames = jdbcTemplate.queryForStringList(
"SELECT name FROM sys.partition_functions");
for (final String partitionFunctionName : partitionFunctionNames) {
statements.add("DROP PARTITION FUNCTION " + quote(partitionFunctionName));
}
return statements;
}
@Override
public Schema[] getAllSchemas() {
try {
final List<String> allSchemaNames = jdbcTemplate.queryForStringList("("
+ "SELECT s.name\n"
+ "FROM sys.schemas s\n"
+ "INNER JOIN sys.sysusers u ON u.uid = s.principal_id\n"
+ "WHERE u.issqluser = 1 AND u.name NOT IN ('sys', 'guest', 'INFORMATION_SCHEMA')\n"
+ ") UNION (\n"
+ "SELECT s.name\n"
+ "FROM sys.schemas s\n"
+ "INNER JOIN sys.sysusers u ON u.uid = s.schema_id\n"
+ "WHERE u.islogin = 1 AND u.name NOT IN ('sys', 'guest', 'INFORMATION_SCHEMA')\n"
+ ") UNION (\n"
+ "SELECT DISTINCT s.name\n"
+ "FROM sys.schemas s\n"
+ "INNER JOIN sys.objects o ON o.schema_id = s.schema_id\n"
+ "WHERE o.is_ms_shipped = 0)");
return allSchemaNames.stream().map(n -> getMainConnection().getSchema(n)).toArray(Schema[]::new);
} catch (SQLException e) {
throw new FlywayException("Unable to determine all schemas", e);
}
}
@Override
public String getDatabaseHosting() {
if (isAzure()) {
final int code = getEngineEdition().getCode();
if (code == 5) {
return DATABASE_HOSTING_AZURE_SQL_DATABASE;
} else if (code == 8) {
return DATABASE_HOSTING_AZURE_SQL_MANAGED_INSTANCE;
} else if (code == 12) {
return DATABASE_HOSTING_FABRIC;
}
} else if (getMainConnection().isAwsRds() || DATABASE_HOSTING_RDS_URL_IDENTIFIER.matcher(configuration.getUrl())
.find()) {
return DATABASE_HOSTING_AWS_RDS;
} else if (StringUtils.hasText(getServerName()) && getServerName().toLowerCase()
.contains(DATABASE_HOSTING_EC2_HOSTNAME_IDENTIFIER)) {
return DATABASE_HOSTING_AWS_VM;
} else {
return super.getDatabaseHosting();
}
return DATABASE_HOSTING_LOCAL;
}
}