Examples with MSSQLDatabase liquibase.database.core.MSSQLDatabase used on opensource projects

Example 31 with MSSQLDatabase

use of liquibase.database.core.MSSQLDatabase in project liquibase by liquibase.

the class TagDatabaseGeneratorTest method testMSSQL.

////    @Test
////    public void supports() throws Exception {
////        new DatabaseTestTemplate().testOnAllDatabases(new DatabaseTest() {
////            public void performTest(Database database) throws Exception {
////                assertTrue(createGeneratorUnderTest().supportsDatabase(database));
////            }
////        });
////    }
//
//    @Test
//    public void execute() throws Exception {
//        new DatabaseTestTemplate().testOnAvailableDatabases(
//                new SqlStatementDatabaseTest(null, new TagDatabaseStatement("TAG_NAME")) {
//                    protected void setup(Database database) throws Exception {
//                        new Liquibase("changelogs/common/common.tests.changelog.xml", new JUnitResourceAccessor(), database).update(null);
//                    }
//
//                    protected void preExecuteAssert(DatabaseSnapshotGenerator snapshot) throws DatabaseException {
//                        assertFalse(snapshot.getDatabase().doesTagExist("TAG_NAME"));
//                    }
//
//                    protected void postExecuteAssert(DatabaseSnapshotGenerator snapshot) throws DatabaseException {
//                        assertTrue(snapshot.getDatabase().doesTagExist("TAG_NAME"));
//                    }
//
//                });
//    }
@Test
public void testMSSQL() throws Exception {
    TagDatabaseStatement statement = new TagDatabaseStatement("v1.0");
    Sql[] sql = SqlGeneratorFactory.getInstance().generateSql(statement, new MSSQLDatabase());
    assertEquals(1, sql.length);
    assertEquals("UPDATE [changelog] " + "SET [TAG] = 'v1.0' " + "FROM [DATABASECHANGELOG] AS [changelog] " + "INNER JOIN (" + "SELECT TOP (1) [ID], [AUTHOR], [FILENAME] " + "FROM [DATABASECHANGELOG] " + "ORDER BY [DATEEXECUTED] DESC, [ORDEREXECUTED] DESC" + ") AS [latest] " + "ON [latest].[ID] = [changelog].[ID] " + "AND [latest].[AUTHOR] = [changelog].[AUTHOR] " + "AND [latest].[FILENAME] = [changelog].[FILENAME]", sql[0].toSql());
}

Example 32 with MSSQLDatabase

use of liquibase.database.core.MSSQLDatabase in project liquibase by liquibase.

the class UpdateGeneratorTest method testGenerateSqlWithNameValuePlaceholderPairs.

@Test
public void testGenerateSqlWithNameValuePlaceholderPairs() {
    // given
    Database database = new MSSQLDatabase();
    UpdateStatement statement = new UpdateStatement(null, null, "DATABASECHANGELOG").addNewColumnValue("MD5SUM", "7:e27bf9c0c2313160ef960a15d44ced47").setWhereClause(":name = :value AND :name = :value AND :name = :value").addWhereColumnName("ID").addWhereColumnName("AUTHOR").addWhereColumnName("FILENAME").addWhereParameters("SYPA: AUTO_START tüüp INT -> TEXT, vaartus 0 00 17 * * ?", "martin", "db/changelog.xml");
    UpdateGenerator generator = new UpdateGenerator();
    // when
    Sql[] sqls = generator.generateSql(statement, database, null);
    // then
    assertEquals("UPDATE [DATABASECHANGELOG] " + "SET [MD5SUM] = '7:e27bf9c0c2313160ef960a15d44ced47' " + "WHERE [ID] = N'SYPA: AUTO_START tüüp INT -> TEXT, vaartus 0 00 17 * * ?' " + "AND [AUTHOR] = 'martin' " + "AND [FILENAME] = 'db/changelog.xml'", sqls[0].toSql());
}

Example 33 with MSSQLDatabase

use of liquibase.database.core.MSSQLDatabase in project liquibase by liquibase.

the class SqlUtil method parseValue.

public static Object parseValue(Database database, Object val, DataType type) {
    if (!(val instanceof String)) {
        return val;
    }
    int typeId = Integer.MIN_VALUE;
    if (type.getDataTypeId() != null) {
        typeId = type.getDataTypeId();
    }
    String typeName = type.getTypeName();
    LiquibaseDataType liquibaseDataType = DataTypeFactory.getInstance().from(type, database);
    String stringVal = (String) val;
    if (stringVal.isEmpty()) {
        if (liquibaseDataType instanceof CharType) {
            return "";
        } else {
            return null;
        }
    }
    if (database instanceof OracleDatabase && !stringVal.startsWith("'") && !stringVal.endsWith("'")) {
        //oracle returns functions without quotes
        Object maybeDate = null;
        if (liquibaseDataType instanceof DateType || typeId == Types.DATE) {
            if (stringVal.endsWith("'HH24:MI:SS')")) {
                maybeDate = DataTypeFactory.getInstance().fromDescription("time", database).sqlToObject(stringVal, database);
            } else {
                maybeDate = DataTypeFactory.getInstance().fromDescription("date", database).sqlToObject(stringVal, database);
            }
        } else if (liquibaseDataType instanceof DateTimeType || typeId == Types.TIMESTAMP) {
            maybeDate = DataTypeFactory.getInstance().fromDescription("datetime", database).sqlToObject(stringVal, database);
        } else if (!stringVal.matches("\\d+\\.?\\d*")) {
            //not just a number
            return new DatabaseFunction(stringVal);
        }
        if (maybeDate != null) {
            if (maybeDate instanceof java.util.Date) {
                return maybeDate;
            } else {
                return new DatabaseFunction(stringVal);
            }
        }
    }
    boolean strippedSingleQuotes = false;
    if (stringVal.startsWith("'") && stringVal.endsWith("'")) {
        stringVal = stringVal.substring(1, stringVal.length() - 1);
        strippedSingleQuotes = true;
    } else if (stringVal.startsWith("((") && stringVal.endsWith("))")) {
        stringVal = stringVal.substring(2, stringVal.length() - 2);
    } else if (stringVal.startsWith("('") && stringVal.endsWith("')")) {
        stringVal = stringVal.substring(2, stringVal.length() - 2);
    } else if (stringVal.startsWith("(") && stringVal.endsWith(")")) {
        return new DatabaseFunction(stringVal.substring(1, stringVal.length() - 1));
    }
    Scanner scanner = new Scanner(stringVal.trim());
    if (typeId == Types.ARRAY) {
        return new DatabaseFunction(stringVal);
    } else if ((liquibaseDataType instanceof BigIntType || typeId == Types.BIGINT)) {
        if (scanner.hasNextBigInteger()) {
            return scanner.nextBigInteger();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.BINARY) {
        return new DatabaseFunction(stringVal.trim());
    } else if (typeId == Types.BIT) {
        if (stringVal.startsWith("b'") || stringVal.startsWith("B'")) {
            //mysql returns boolean values as b'0' and b'1'
            stringVal = stringVal.replaceFirst("b'", "").replaceFirst("B'", "").replaceFirst("'$", "");
        }
        stringVal = stringVal.trim();
        if (database instanceof MySQLDatabase) {
            return stringVal.equals("1") || stringVal.equalsIgnoreCase("true");
        }
        Object value;
        if (scanner.hasNextBoolean()) {
            value = scanner.nextBoolean();
        } else {
            value = Integer.valueOf(stringVal);
        }
        return value;
    } else if (liquibaseDataType instanceof BlobType || typeId == Types.BLOB) {
        if (strippedSingleQuotes) {
            return stringVal;
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if ((liquibaseDataType instanceof BooleanType || typeId == Types.BOOLEAN)) {
        if (scanner.hasNextBoolean()) {
            return scanner.nextBoolean();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (liquibaseDataType instanceof CharType || typeId == Types.CHAR) {
        return stringVal;
    } else if (liquibaseDataType instanceof ClobType || typeId == Types.CLOB) {
        return stringVal;
    } else if (typeId == Types.DATALINK) {
        return new DatabaseFunction(stringVal);
    } else if (liquibaseDataType instanceof DateType || typeId == Types.DATE) {
        if (typeName.equalsIgnoreCase("year")) {
            return stringVal.trim();
        }
        return DataTypeFactory.getInstance().fromDescription("date", database).sqlToObject(stringVal, database);
    } else if ((liquibaseDataType instanceof DecimalType || typeId == Types.DECIMAL)) {
        if (scanner.hasNextBigDecimal()) {
            return scanner.nextBigDecimal();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.DISTINCT) {
        return new DatabaseFunction(stringVal);
    } else if ((liquibaseDataType instanceof DoubleType || typeId == Types.DOUBLE)) {
        if (scanner.hasNextDouble()) {
            return scanner.nextDouble();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if ((liquibaseDataType instanceof FloatType || typeId == Types.FLOAT)) {
        if (scanner.hasNextFloat()) {
            return scanner.nextFloat();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if ((liquibaseDataType instanceof IntType || typeId == Types.INTEGER)) {
        if (scanner.hasNextInt()) {
            return scanner.nextInt();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.JAVA_OBJECT) {
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.LONGNVARCHAR) {
        return stringVal;
    } else if (typeId == Types.LONGVARBINARY) {
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.LONGVARCHAR) {
        return stringVal;
    } else if (liquibaseDataType instanceof NCharType || typeId == Types.NCHAR || liquibaseDataType.getName().equalsIgnoreCase("NCLOB")) {
        return stringVal;
    } else if (typeId == Types.NCLOB) {
        return stringVal;
    } else if (typeId == Types.NULL) {
        return null;
    } else if ((liquibaseDataType instanceof NumberType || typeId == Types.NUMERIC)) {
        if (scanner.hasNextBigDecimal()) {
            if (database instanceof MSSQLDatabase && stringVal.endsWith(".0") || stringVal.endsWith(".00") || stringVal.endsWith(".000")) {
                //MSSQL can store the value with the decimal digits. return it directly to avoid unexpected differences
                return new DatabaseFunction(stringVal);
            }
            return scanner.nextBigDecimal();
        } else {
            if (stringVal.equals("")) {
                //can have numeric default '' on sql server
                return new DatabaseFunction("''");
            }
            return new DatabaseFunction(stringVal);
        }
    } else if (liquibaseDataType instanceof NVarcharType || typeId == Types.NVARCHAR) {
        return stringVal;
    } else if (typeId == Types.OTHER) {
        if (database instanceof DB2Database && typeName.equalsIgnoreCase("DECFLOAT")) {
            return new BigDecimal(stringVal);
        }
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.REAL) {
        return new BigDecimal(stringVal.trim());
    } else if (typeId == Types.REF) {
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.ROWID) {
        return new DatabaseFunction(stringVal);
    } else if ((liquibaseDataType instanceof SmallIntType || typeId == Types.SMALLINT)) {
        if (scanner.hasNextInt()) {
            return scanner.nextInt();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.SQLXML) {
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.STRUCT) {
        return new DatabaseFunction(stringVal);
    } else if (liquibaseDataType instanceof TimeType || typeId == Types.TIME) {
        return DataTypeFactory.getInstance().fromDescription("time", database).sqlToObject(stringVal, database);
    } else if (liquibaseDataType instanceof DateTimeType || liquibaseDataType instanceof TimestampType || typeId == Types.TIMESTAMP) {
        return DataTypeFactory.getInstance().fromDescription("datetime", database).sqlToObject(stringVal, database);
    } else if ((liquibaseDataType instanceof TinyIntType || typeId == Types.TINYINT)) {
        if (scanner.hasNextInt()) {
            return scanner.nextInt();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.VARBINARY) {
        return new DatabaseFunction(stringVal);
    } else if (liquibaseDataType instanceof VarcharType || typeId == Types.VARCHAR) {
        return stringVal;
    } else if (database instanceof MySQLDatabase && typeName.toLowerCase().startsWith("enum")) {
        return stringVal;
    } else {
        if (stringVal.equals("")) {
            return stringVal;
        }
        LogFactory.getLogger().info("Unknown default value: value '" + stringVal + "' type " + typeName + " (" + type + "). Calling it a function so it's not additionally quoted");
        if (strippedSingleQuotes) {
            //put quotes back
            return new DatabaseFunction("'" + stringVal + "'");
        }
        return new DatabaseFunction(stringVal);
    }
}

Example 34 with MSSQLDatabase

use of liquibase.database.core.MSSQLDatabase in project liquibase by liquibase.

the class AbstractSQLChangeTest method generateStatements_convertsEndingsOnSqlServer.

@Test
public void generateStatements_convertsEndingsOnSqlServer() {
    ExampleAbstractSQLChange change = new ExampleAbstractSQLChange("LINE 1;\n--a comment\nLINE 2;\nLINE 3;");
    change.setSplitStatements(false);
    change.setStripComments(true);
    SqlStatement[] statements = change.generateStatements(new MSSQLDatabase());
    assertEquals(1, statements.length);
    assertEquals("LINE 1;\r\n\r\nLINE 2;\r\nLINE 3;", ((RawSqlStatement) statements[0]).getSql());
}

Example 35 with MSSQLDatabase

use of liquibase.database.core.MSSQLDatabase in project liquibase by liquibase.

the class ChangeFactoryTest method supportStatement.

@Test
public void supportStatement() throws Exception {
    CreateSequenceStatement statement = new CreateSequenceStatement(null, null, "seq_my_table");
    MSSQLDatabase database10 = new MSSQLDatabase() {

        @Override
        public int getDatabaseMajorVersion() throws DatabaseException {
            return 10;
        }
    };
    MSSQLDatabase database11 = new MSSQLDatabase() {

        @Override
        public int getDatabaseMajorVersion() throws DatabaseException {
            return 11;
        }
    };
    //make sure there is no problem with SqlGeneratorFactory.generatorsByKey cache
    ChangeFactory.getInstance();
    assertFalse("unsupported create sequence", SqlGeneratorFactory.getInstance().supports(statement, database10));
    assertTrue("supported create sequence", SqlGeneratorFactory.getInstance().supports(statement, database11));
}