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

Example 1 with MSSQLDatabase

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

the class LoadDataChange method generateStatements.

@Override
public SqlStatement[] generateStatements(Database database) {
    CSVReader reader = null;
    try {
        reader = getCSVReader();
        if (reader == null) {
            throw new UnexpectedLiquibaseException("Unable to read file " + this.getFile());
        }
        String[] headers = reader.readNext();
        if (headers == null) {
            throw new UnexpectedLiquibaseException("Data file " + getFile() + " was empty");
        }
        List<SqlStatement> statements = new ArrayList<SqlStatement>();
        boolean anyPreparedStatements = false;
        String[] line;
        // Start at '1' to take into account the header (already processed)
        int lineNumber = 1;
        boolean isCommentingEnabled = StringUtils.isNotEmpty(commentLineStartsWith);
        while ((line = reader.readNext()) != null) {
            lineNumber++;
            if (line.length == 0 || (line.length == 1 && StringUtils.trimToNull(line[0]) == null) || (isCommentingEnabled && isLineCommented(line))) {
                //nothing on this line
                continue;
            }
            // (Failure could indicate unquoted strings with commas, for example).
            if (line.length != headers.length) {
                throw new UnexpectedLiquibaseException("CSV file " + getFile() + " Line " + lineNumber + " has " + line.length + " values defined, Header has " + headers.length + ". Numbers MUST be equal (check for unquoted string with embedded commas)");
            }
            boolean needsPreparedStatement = false;
            List<ColumnConfig> columns = new ArrayList<ColumnConfig>();
            for (int i = 0; i < headers.length; i++) {
                Object value = line[i];
                String columnName = headers[i].trim();
                ColumnConfig valueConfig = new ColumnConfig();
                ColumnConfig columnConfig = getColumnConfig(i, headers[i].trim());
                if (columnConfig != null) {
                    if ("skip".equalsIgnoreCase(columnConfig.getType())) {
                        continue;
                    }
                    // don't overwrite header name unless there is actually a value to override it with
                    if (columnConfig.getName() != null) {
                        columnName = columnConfig.getName();
                    }
                    valueConfig.setName(columnName);
                    if (columnConfig.getType() != null) {
                        if (columnConfig.getType().equalsIgnoreCase("BOOLEAN")) {
                            if (value.toString().equalsIgnoreCase("NULL")) {
                                valueConfig.setValue(null);
                            } else {
                                valueConfig.setValueBoolean(BooleanParser.parseBoolean(value.toString().toLowerCase()));
                            }
                        } else if (columnConfig.getType().equalsIgnoreCase("NUMERIC")) {
                            if (value.toString().equalsIgnoreCase("NULL")) {
                                valueConfig.setValue(null);
                            } else {
                                valueConfig.setValueNumeric(value.toString());
                            }
                        } else if (columnConfig.getType().toLowerCase().contains("date") || columnConfig.getType().toLowerCase().contains("time")) {
                            if (value.toString().equalsIgnoreCase("NULL")) {
                                valueConfig.setValue(null);
                            } else {
                                valueConfig.setValueDate(value.toString());
                            }
                        } else if (columnConfig.getType().equalsIgnoreCase("STRING")) {
                            if (value.toString().equalsIgnoreCase("NULL")) {
                                valueConfig.setValue(null);
                            } else {
                                valueConfig.setValue(value.toString());
                            }
                        } else if (columnConfig.getType().equalsIgnoreCase("COMPUTED")) {
                            if (value.toString().equalsIgnoreCase("NULL")) {
                                valueConfig.setValue(null);
                            } else {
                                liquibase.statement.DatabaseFunction function = new liquibase.statement.DatabaseFunction(value.toString());
                                valueConfig.setValueComputed(function);
                            }
                        } else if (columnConfig.getType().equalsIgnoreCase("SEQUENCE")) {
                            String sequenceName;
                            if (value.toString().equalsIgnoreCase("NULL")) {
                                sequenceName = columnConfig.getDefaultValue();
                                if (sequenceName == null) {
                                    throw new UnexpectedLiquibaseException("Must set a sequence name in the loadData column defaultValue attribute");
                                }
                            } else {
                                sequenceName = value.toString();
                            }
                            liquibase.statement.SequenceNextValueFunction function = new liquibase.statement.SequenceNextValueFunction(sequenceName);
                            valueConfig.setValueComputed(function);
                        } else if (columnConfig.getType().equalsIgnoreCase("BLOB")) {
                            if (value.toString().equalsIgnoreCase("NULL")) {
                                valueConfig.setValue(null);
                            } else {
                                valueConfig.setValueBlobFile(value.toString());
                                needsPreparedStatement = true;
                            }
                        } else if (columnConfig.getType().equalsIgnoreCase("CLOB")) {
                            if (value.toString().equalsIgnoreCase("NULL")) {
                                valueConfig.setValue(null);
                            } else {
                                valueConfig.setValueClobFile(value.toString());
                                needsPreparedStatement = true;
                            }
                        } else {
                            throw new UnexpectedLiquibaseException("loadData type of " + columnConfig.getType() + " is not supported.  Please use BOOLEAN, NUMERIC, DATE, STRING, COMPUTED, SEQUENCE or SKIP");
                        }
                    }
                } else {
                    if (columnName.contains("(") || columnName.contains(")") && database instanceof AbstractJdbcDatabase) {
                        columnName = ((AbstractJdbcDatabase) database).quoteObject(columnName, Column.class);
                    }
                    valueConfig.setName(columnName);
                    if (value == null || value.toString().equalsIgnoreCase("NULL")) {
                        // value is always going to be a string unless overridden by ColumnConfig
                        valueConfig.setValue((String) value);
                    } else {
                        valueConfig.setValue(value.toString());
                    }
                }
                columns.add(valueConfig);
            }
            if (needsPreparedStatement) {
                anyPreparedStatements = true;
                statements.add(new InsertExecutablePreparedStatement(database, getCatalogName(), getSchemaName(), getTableName(), columns, getChangeSet(), getResourceAccessor()));
            } else {
                InsertStatement insertStatement = this.createStatement(getCatalogName(), getSchemaName(), getTableName());
                for (ColumnConfig column : columns) {
                    String columnName = column.getName();
                    Object value = column.getValueObject();
                    if (value == null) {
                        value = "NULL";
                    }
                    insertStatement.addColumnValue(columnName, value);
                }
                statements.add(insertStatement);
            }
        }
        if (anyPreparedStatements) {
            return statements.toArray(new SqlStatement[statements.size()]);
        } else {
            InsertSetStatement statementSet = this.createStatementSet(getCatalogName(), getSchemaName(), getTableName());
            for (SqlStatement stmt : statements) {
                statementSet.addInsertStatement((InsertStatement) stmt);
            }
            if (database instanceof MSSQLDatabase || database instanceof MySQLDatabase || database instanceof PostgresDatabase) {
                List<InsertStatement> innerStatements = statementSet.getStatements();
                if (innerStatements != null && innerStatements.size() > 0 && innerStatements.get(0) instanceof InsertOrUpdateStatement) {
                    //cannot do insert or update in a single statement
                    return statementSet.getStatementsArray();
                }
                // we only return a single "statement" - it's capable of emitting multiple sub-statements, should the need arise, on generation.
                return new SqlStatement[] { statementSet };
            } else {
                return statementSet.getStatementsArray();
            }
        }
    } catch (IOException e) {
        throw new RuntimeException(e);
    } catch (UnexpectedLiquibaseException ule) {
        if (getChangeSet() != null && getChangeSet().getFailOnError() != null && !getChangeSet().getFailOnError()) {
            Logger log = LogFactory.getLogger();
            log.info("Change set " + getChangeSet().toString(false) + " failed, but failOnError was false.  Error: " + ule.getMessage());
            return new SqlStatement[0];
        } else {
            throw ule;
        }
    } finally {
        if (null != reader) {
            try {
                reader.close();
            } catch (IOException e) {
                ;
            }
        }
    }
}

Example 2 with MSSQLDatabase

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

the class StandardLockService method acquireLock.

@Override
public boolean acquireLock() throws LockException {
    if (hasChangeLogLock) {
        return true;
    }
    quotingStrategy = database.getObjectQuotingStrategy();
    Executor executor = ExecutorService.getInstance().getExecutor(database);
    try {
        database.rollback();
        this.init();
        Boolean locked = (Boolean) ExecutorService.getInstance().getExecutor(database).queryForObject(new SelectFromDatabaseChangeLogLockStatement("LOCKED"), Boolean.class);
        if (locked) {
            return false;
        } else {
            executor.comment("Lock Database");
            int rowsUpdated = executor.update(new LockDatabaseChangeLogStatement());
            if (rowsUpdated == -1 && database instanceof MSSQLDatabase) {
                LogFactory.getLogger().debug("Database did not return a proper row count (Might have NOCOUNT enabled)");
                database.rollback();
                Sql[] sql = SqlGeneratorFactory.getInstance().generateSql(new LockDatabaseChangeLogStatement(), database);
                if (sql.length != 1) {
                    throw new UnexpectedLiquibaseException("Did not expect " + sql.length + " statements");
                }
                rowsUpdated = executor.update(new RawSqlStatement("EXEC sp_executesql N'SET NOCOUNT OFF " + sql[0].toSql().replace("'", "''") + "'"));
            }
            if (rowsUpdated > 1) {
                throw new LockException("Did not update change log lock correctly");
            }
            if (rowsUpdated == 0) {
                // another node was faster
                return false;
            }
            database.commit();
            LogFactory.getLogger().info("Successfully acquired change log lock");
            hasChangeLogLock = true;
            database.setCanCacheLiquibaseTableInfo(true);
            return true;
        }
    } catch (Exception e) {
        throw new LockException(e);
    } finally {
        try {
            database.rollback();
        } catch (DatabaseException e) {
            ;
        }
    }
}

Example 3 with MSSQLDatabase

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

the class StandardLockService method releaseLock.

@Override
public void releaseLock() throws LockException {
    ObjectQuotingStrategy incomingQuotingStrategy = null;
    if (this.quotingStrategy != null) {
        incomingQuotingStrategy = database.getObjectQuotingStrategy();
        database.setObjectQuotingStrategy(this.quotingStrategy);
    }
    Executor executor = ExecutorService.getInstance().getExecutor(database);
    try {
        if (this.hasDatabaseChangeLogLockTable()) {
            executor.comment("Release Database Lock");
            database.rollback();
            int updatedRows = executor.update(new UnlockDatabaseChangeLogStatement());
            if (updatedRows == -1 && database instanceof MSSQLDatabase) {
                LogFactory.getLogger().debug("Database did not return a proper row count (Might have NOCOUNT enabled.)");
                database.rollback();
                Sql[] sql = SqlGeneratorFactory.getInstance().generateSql(new UnlockDatabaseChangeLogStatement(), database);
                if (sql.length != 1) {
                    throw new UnexpectedLiquibaseException("Did not expect " + sql.length + " statements");
                }
                updatedRows = executor.update(new RawSqlStatement("EXEC sp_executesql N'SET NOCOUNT OFF " + sql[0].toSql().replace("'", "''") + "'"));
            }
            if (updatedRows != 1) {
                throw new LockException("Did not update change log lock correctly.\n\n" + updatedRows + " rows were updated instead of the expected 1 row using executor " + executor.getClass().getName() + " there are " + executor.queryForInt(new RawSqlStatement("select count(*) from " + database.getDatabaseChangeLogLockTableName())) + " rows in the table");
            }
            database.commit();
        }
    } catch (Exception e) {
        throw new LockException(e);
    } finally {
        try {
            hasChangeLogLock = false;
            database.setCanCacheLiquibaseTableInfo(false);
            LogFactory.getLogger().info("Successfully released change log lock");
            database.rollback();
        } catch (DatabaseException e) {
            ;
        }
        if (incomingQuotingStrategy != null) {
            database.setObjectQuotingStrategy(incomingQuotingStrategy);
        }
    }
}

Example 4 with MSSQLDatabase

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

the class MissingUniqueConstraintChangeGenerator method fixMissing.

@Override
public Change[] fixMissing(DatabaseObject missingObject, DiffOutputControl control, Database referenceDatabase, Database comparisonDatabase, ChangeGeneratorChain chain) {
    List<Change> returnList = new ArrayList<Change>();
    UniqueConstraint uc = (UniqueConstraint) missingObject;
    if (uc.getTable() == null) {
        return null;
    }
    AddUniqueConstraintChange change = new AddUniqueConstraintChange();
    change.setTableName(uc.getTable().getName());
    if (uc.getBackingIndex() != null && control.getIncludeTablespace()) {
        change.setTablespace(uc.getBackingIndex().getTablespace());
    }
    if (control.getIncludeCatalog()) {
        change.setCatalogName(uc.getTable().getSchema().getCatalogName());
    }
    if (control.getIncludeSchema()) {
        change.setSchemaName(uc.getTable().getSchema().getName());
    }
    change.setConstraintName(uc.getName());
    change.setColumnNames(uc.getColumnNames());
    change.setDeferrable(uc.isDeferrable() ? Boolean.TRUE : null);
    change.setInitiallyDeferred(uc.isInitiallyDeferred() ? Boolean.TRUE : null);
    change.setDisabled(uc.isDisabled() ? Boolean.TRUE : null);
    if (referenceDatabase instanceof MSSQLDatabase) {
        change.setClustered(uc.isClustered() ? Boolean.TRUE : null);
    }
    if (comparisonDatabase instanceof OracleDatabase) {
        Index backingIndex = uc.getBackingIndex();
        if (backingIndex != null && backingIndex.getName() != null) {
            Change[] changes = ChangeGeneratorFactory.getInstance().fixMissing(backingIndex, control, referenceDatabase, comparisonDatabase);
            if (changes != null) {
                returnList.addAll(Arrays.asList(changes));
                change.setForIndexName(backingIndex.getName());
                Schema schema = backingIndex.getSchema();
                if (schema != null) {
                    if (control.getIncludeCatalog()) {
                        change.setForIndexCatalogName(schema.getCatalogName());
                    }
                    if (control.getIncludeSchema()) {
                        change.setForIndexSchemaName(schema.getName());
                    }
                }
            }
        }
    }
    Index backingIndex = uc.getBackingIndex();
    //        if (backingIndex == null) {
    //            Index exampleIndex = new Index().setTable(uc.getTable());
    //            for (String col : uc.getColumns()) {
    //                exampleIndex.getColumns().add(col);
    //            }
    //            control.setAlreadyHandledMissing(exampleIndex);
    //        } else {
    control.setAlreadyHandledMissing(backingIndex);
    //        }
    returnList.add(change);
    return returnList.toArray(new Change[returnList.size()]);
}

Example 5 with MSSQLDatabase

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

the class MissingPrimaryKeyChangeGenerator method fixMissing.

@Override
public Change[] fixMissing(DatabaseObject missingObject, DiffOutputControl control, Database referenceDatabase, Database comparisonDatabase, ChangeGeneratorChain chain) {
    List<Change> returnList = new ArrayList<Change>();
    PrimaryKey pk = (PrimaryKey) missingObject;
    AddPrimaryKeyChange change = createAddPrimaryKeyChange();
    change.setTableName(pk.getTable().getName());
    if (control.getIncludeCatalog()) {
        change.setCatalogName(pk.getTable().getSchema().getCatalogName());
    }
    if (control.getIncludeSchema()) {
        change.setSchemaName(pk.getTable().getSchema().getName());
    }
    change.setConstraintName(pk.getName());
    change.setColumnNames(pk.getColumnNames());
    if (control.getIncludeTablespace()) {
        change.setTablespace(pk.getTablespace());
    }
    if (referenceDatabase instanceof MSSQLDatabase && pk.getBackingIndex() != null && pk.getBackingIndex().getClustered() != null && !pk.getBackingIndex().getClustered()) {
        change.setClustered(false);
    }
    if (referenceDatabase instanceof PostgresDatabase && pk.getBackingIndex() != null && pk.getBackingIndex().getClustered() != null && pk.getBackingIndex().getClustered()) {
        change.setClustered(true);
    }
    if (comparisonDatabase instanceof OracleDatabase || (comparisonDatabase instanceof DB2Database && pk.getBackingIndex() != null && !comparisonDatabase.isSystemObject(pk.getBackingIndex()))) {
        Index backingIndex = pk.getBackingIndex();
        if (backingIndex != null && backingIndex.getName() != null) {
            try {
                if (!control.getIncludeCatalog() && !control.getIncludeSchema()) {
                    CatalogAndSchema schema = comparisonDatabase.getDefaultSchema().customize(comparisonDatabase);
                    //set table schema so it is found in the correct schema
                    backingIndex.getTable().setSchema(schema.getCatalogName(), schema.getSchemaName());
                }
                if (referenceDatabase.equals(comparisonDatabase) || !SnapshotGeneratorFactory.getInstance().has(backingIndex, comparisonDatabase)) {
                    Change[] fixes = ChangeGeneratorFactory.getInstance().fixMissing(backingIndex, control, referenceDatabase, comparisonDatabase);
                    if (fixes != null) {
                        for (Change fix : fixes) {
                            if (fix != null) {
                                returnList.add(fix);
                            }
                        }
                    }
                }
            } catch (Exception e) {
                throw new UnexpectedLiquibaseException(e);
            }
            change.setForIndexName(backingIndex.getName());
            Schema schema = backingIndex.getSchema();
            if (schema != null) {
                if (control.getIncludeCatalog()) {
                    change.setForIndexCatalogName(schema.getCatalogName());
                }
                if (control.getIncludeSchema()) {
                    change.setForIndexSchemaName(schema.getName());
                }
            }
        }
    }
    control.setAlreadyHandledMissing(pk.getBackingIndex());
    returnList.add(change);
    return returnList.toArray(new Change[returnList.size()]);
}