Examples with UnparsedSql liquibase.sql.UnparsedSql used on opensource projects

Example 1 with UnparsedSql

use of liquibase.sql.UnparsedSql in project liquibase by liquibase.

the class CreateTableGenerator method generateSql.

@Override
public Sql[] generateSql(CreateTableStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    if (database instanceof InformixDatabase) {
        AbstractSqlGenerator<CreateTableStatement> gen = new CreateTableGeneratorInformix();
        return gen.generateSql(statement, database, sqlGeneratorChain);
    }
    List<Sql> additionalSql = new ArrayList<Sql>();
    StringBuffer buffer = new StringBuffer();
    buffer.append("CREATE TABLE ").append(database.escapeTableName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName())).append(" ");
    buffer.append("(");
    boolean isSinglePrimaryKeyColumn = statement.getPrimaryKeyConstraint() != null && statement.getPrimaryKeyConstraint().getColumns().size() == 1;
    boolean isPrimaryKeyAutoIncrement = false;
    Iterator<String> columnIterator = statement.getColumns().iterator();
    List<String> primaryKeyColumns = new LinkedList<String>();
    BigInteger mysqlTableOptionStartWith = null;
    while (columnIterator.hasNext()) {
        String column = columnIterator.next();
        DatabaseDataType columnType = statement.getColumnTypes().get(column).toDatabaseDataType(database);
        buffer.append(database.escapeColumnName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName(), column, true));
        buffer.append(" ").append(columnType);
        AutoIncrementConstraint autoIncrementConstraint = null;
        for (AutoIncrementConstraint currentAutoIncrementConstraint : statement.getAutoIncrementConstraints()) {
            if (column.equals(currentAutoIncrementConstraint.getColumnName())) {
                autoIncrementConstraint = currentAutoIncrementConstraint;
                break;
            }
        }
        boolean isAutoIncrementColumn = autoIncrementConstraint != null;
        boolean isPrimaryKeyColumn = statement.getPrimaryKeyConstraint() != null && statement.getPrimaryKeyConstraint().getColumns().contains(column);
        isPrimaryKeyAutoIncrement = isPrimaryKeyAutoIncrement || isPrimaryKeyColumn && isAutoIncrementColumn;
        if (isPrimaryKeyColumn) {
            primaryKeyColumns.add(column);
        }
        if ((database instanceof SQLiteDatabase) && isSinglePrimaryKeyColumn && isPrimaryKeyColumn && isAutoIncrementColumn) {
            String pkName = StringUtils.trimToNull(statement.getPrimaryKeyConstraint().getConstraintName());
            if (pkName == null) {
                pkName = database.generatePrimaryKeyName(statement.getTableName());
            }
            if (pkName != null) {
                buffer.append(" CONSTRAINT ");
                buffer.append(database.escapeConstraintName(pkName));
            }
            buffer.append(" PRIMARY KEY");
        }
        // for the serial data type in postgres, there should be no default value
        if (!columnType.isAutoIncrement() && statement.getDefaultValue(column) != null) {
            Object defaultValue = statement.getDefaultValue(column);
            if (database instanceof MSSQLDatabase) {
                String constraintName = statement.getDefaultValueConstraintName(column);
                if (constraintName == null) {
                    constraintName = ((MSSQLDatabase) database).generateDefaultConstraintName(statement.getTableName(), column);
                }
                buffer.append(" CONSTRAINT ").append(database.escapeObjectName(constraintName, ForeignKey.class));
            }
            if (database instanceof OracleDatabase && statement.getDefaultValue(column).toString().startsWith("GENERATED ALWAYS ")) {
                buffer.append(" ");
            } else {
                buffer.append(" DEFAULT ");
            }
            if (defaultValue instanceof SequenceNextValueFunction) {
                buffer.append(database.generateDatabaseFunctionValue((SequenceNextValueFunction) defaultValue));
            } else {
                buffer.append(statement.getColumnTypes().get(column).objectToSql(defaultValue, database));
            }
        }
        if (isAutoIncrementColumn) {
            // TODO: check if database supports auto increment on non primary key column
            if (database.supportsAutoIncrement()) {
                String autoIncrementClause = database.getAutoIncrementClause(autoIncrementConstraint.getStartWith(), autoIncrementConstraint.getIncrementBy());
                if (!"".equals(autoIncrementClause)) {
                    buffer.append(" ").append(autoIncrementClause);
                }
                if (autoIncrementConstraint.getStartWith() != null) {
                    if (database instanceof PostgresDatabase) {
                        String sequenceName = statement.getTableName() + "_" + column + "_seq";
                        additionalSql.add(new UnparsedSql("alter sequence " + database.escapeSequenceName(statement.getCatalogName(), statement.getSchemaName(), sequenceName) + " start with " + autoIncrementConstraint.getStartWith(), new Sequence().setName(sequenceName).setSchema(statement.getCatalogName(), statement.getSchemaName())));
                    } else if (database instanceof MySQLDatabase) {
                        mysqlTableOptionStartWith = autoIncrementConstraint.getStartWith();
                    }
                }
            } else {
                LogFactory.getLogger().warning(database.getShortName() + " does not support autoincrement columns as requested for " + (database.escapeTableName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName())));
            }
        }
        if (statement.getNotNullColumns().contains(column)) {
            buffer.append(" NOT NULL");
        } else {
            if (database instanceof SybaseDatabase || database instanceof SybaseASADatabase || database instanceof MySQLDatabase || (database instanceof MSSQLDatabase && columnType.toString().toLowerCase().contains("timestamp"))) {
                buffer.append(" NULL");
            }
        }
        if (database instanceof InformixDatabase && isSinglePrimaryKeyColumn && isPrimaryKeyColumn) {
        //buffer.append(" PRIMARY KEY");
        }
        if (database instanceof MySQLDatabase && statement.getColumnRemarks(column) != null) {
            buffer.append(" COMMENT '" + database.escapeStringForDatabase(statement.getColumnRemarks(column)) + "'");
        }
        if (columnIterator.hasNext()) {
            buffer.append(", ");
        }
    }
    buffer.append(",");
    if (!((database instanceof SQLiteDatabase) && isSinglePrimaryKeyColumn && isPrimaryKeyAutoIncrement) && !((database instanceof InformixDatabase) && isSinglePrimaryKeyColumn)) {
        if (statement.getPrimaryKeyConstraint() != null && statement.getPrimaryKeyConstraint().getColumns().size() > 0) {
            if (database.supportsPrimaryKeyNames()) {
                String pkName = StringUtils.trimToNull(statement.getPrimaryKeyConstraint().getConstraintName());
                if (pkName == null) {
                    // TODO ORA-00972: identifier is too long
                    // If tableName lenght is more then 28 symbols
                    // then generated pkName will be incorrect
                    pkName = database.generatePrimaryKeyName(statement.getTableName());
                }
                if (pkName != null) {
                    buffer.append(" CONSTRAINT ");
                    buffer.append(database.escapeConstraintName(pkName));
                }
            }
            buffer.append(" PRIMARY KEY (");
            buffer.append(database.escapeColumnNameList(StringUtils.join(statement.getPrimaryKeyConstraint().getColumns(), ", ")));
            buffer.append(")");
            // Setting up table space for PK's index if it exist
            if ((database instanceof OracleDatabase || database instanceof PostgresDatabase) && statement.getPrimaryKeyConstraint().getTablespace() != null) {
                buffer.append(" USING INDEX TABLESPACE ");
                buffer.append(statement.getPrimaryKeyConstraint().getTablespace());
            }
            buffer.append(",");
        }
    }
    for (ForeignKeyConstraint fkConstraint : statement.getForeignKeyConstraints()) {
        if (!(database instanceof InformixDatabase)) {
            buffer.append(" CONSTRAINT ");
            buffer.append(database.escapeConstraintName(fkConstraint.getForeignKeyName()));
        }
        String referencesString = fkConstraint.getReferences();
        buffer.append(" FOREIGN KEY (").append(database.escapeColumnName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName(), fkConstraint.getColumn())).append(") REFERENCES ");
        if (referencesString != null) {
            if (!referencesString.contains(".") && database.getDefaultSchemaName() != null && database.getOutputDefaultSchema()) {
                referencesString = database.escapeObjectName(database.getDefaultSchemaName(), Schema.class) + "." + referencesString;
            }
            buffer.append(referencesString);
        } else {
            buffer.append(database.escapeObjectName(fkConstraint.getReferencedTableCatalogName(), fkConstraint.getReferencedTableSchemaName(), fkConstraint.getReferencedTableName(), Table.class)).append("(").append(database.escapeColumnNameList(fkConstraint.getReferencedColumnNames())).append(")");
        }
        if (fkConstraint.isDeleteCascade()) {
            buffer.append(" ON DELETE CASCADE");
        }
        if ((database instanceof InformixDatabase)) {
            buffer.append(" CONSTRAINT ");
            buffer.append(database.escapeConstraintName(fkConstraint.getForeignKeyName()));
        }
        if (fkConstraint.isInitiallyDeferred()) {
            buffer.append(" INITIALLY DEFERRED");
        }
        if (fkConstraint.isDeferrable()) {
            buffer.append(" DEFERRABLE");
        }
        buffer.append(",");
    }
    for (UniqueConstraint uniqueConstraint : statement.getUniqueConstraints()) {
        if (uniqueConstraint.getConstraintName() != null && !constraintNameAfterUnique(database)) {
            buffer.append(" CONSTRAINT ");
            buffer.append(database.escapeConstraintName(uniqueConstraint.getConstraintName()));
        }
        buffer.append(" UNIQUE (");
        buffer.append(database.escapeColumnNameList(StringUtils.join(uniqueConstraint.getColumns(), ", ")));
        buffer.append(")");
        if (uniqueConstraint.getConstraintName() != null && constraintNameAfterUnique(database)) {
            buffer.append(" CONSTRAINT ");
            buffer.append(database.escapeConstraintName(uniqueConstraint.getConstraintName()));
        }
        buffer.append(",");
    }
    //        if (constraints != null && constraints.getCheckConstraint() != null) {
    //            buffer.append(constraints.getCheckConstraint()).append(" ");
    //        }
    //    }
    String sql = buffer.toString().replaceFirst(",\\s*$", "") + ")";
    if (database instanceof MySQLDatabase && mysqlTableOptionStartWith != null) {
        LogFactory.getLogger().info("[MySQL] Using last startWith statement (" + mysqlTableOptionStartWith.toString() + ") as table option.");
        sql += " " + ((MySQLDatabase) database).getTableOptionAutoIncrementStartWithClause(mysqlTableOptionStartWith);
    }
    if (statement.getTablespace() != null && database.supportsTablespaces()) {
        if (database instanceof MSSQLDatabase || database instanceof SybaseASADatabase) {
            sql += " ON " + statement.getTablespace();
        } else if (database instanceof DB2Database || database instanceof InformixDatabase) {
            sql += " IN " + statement.getTablespace();
        } else {
            sql += " TABLESPACE " + statement.getTablespace();
        }
    }
    if (database instanceof MySQLDatabase && statement.getRemarks() != null) {
        sql += " COMMENT='" + database.escapeStringForDatabase(statement.getRemarks()) + "' ";
    }
    additionalSql.add(0, new UnparsedSql(sql, getAffectedTable(statement)));
    return additionalSql.toArray(new Sql[additionalSql.size()]);
}

Example 2 with UnparsedSql

use of liquibase.sql.UnparsedSql in project liquibase by liquibase.

the class CreateTableGeneratorInformix method generateSql.

@Override
public Sql[] generateSql(CreateTableStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    StringBuilder buffer = new StringBuilder();
    buffer.append("CREATE TABLE ").append(database.escapeTableName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName())).append(" ");
    buffer.append("(");
    boolean isSinglePrimaryKeyColumn = statement.getPrimaryKeyConstraint() != null && statement.getPrimaryKeyConstraint().getColumns().size() == 1;
    boolean isPrimaryKeyAutoIncrement = false;
    Iterator<String> columnIterator = statement.getColumns().iterator();
    List<String> primaryKeyColumns = new LinkedList<String>();
    while (columnIterator.hasNext()) {
        String column = columnIterator.next();
        buffer.append(database.escapeColumnName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName(), column));
        buffer.append(" ").append(statement.getColumnTypes().get(column).toDatabaseDataType(database).toSql());
        AutoIncrementConstraint autoIncrementConstraint = null;
        for (AutoIncrementConstraint currentAutoIncrementConstraint : statement.getAutoIncrementConstraints()) {
            if (column.equals(currentAutoIncrementConstraint.getColumnName())) {
                autoIncrementConstraint = currentAutoIncrementConstraint;
                break;
            }
        }
        boolean isAutoIncrementColumn = autoIncrementConstraint != null;
        boolean isPrimaryKeyColumn = statement.getPrimaryKeyConstraint() != null && statement.getPrimaryKeyConstraint().getColumns().contains(column);
        isPrimaryKeyAutoIncrement = isPrimaryKeyAutoIncrement || isPrimaryKeyColumn && isAutoIncrementColumn;
        if (isPrimaryKeyColumn) {
            primaryKeyColumns.add(column);
        }
        if (statement.getDefaultValue(column) != null) {
            Object defaultValue = statement.getDefaultValue(column);
            buffer.append(" DEFAULT ");
            buffer.append(statement.getColumnTypes().get(column).objectToSql(defaultValue, database));
        }
        if (isAutoIncrementColumn) {
            // TODO: check if database supports auto increment on non primary key column
            if (database.supportsAutoIncrement()) {
                String autoIncrementClause = database.getAutoIncrementClause(autoIncrementConstraint.getStartWith(), autoIncrementConstraint.getIncrementBy());
                if (autoIncrementClause.length() > 0) {
                    buffer.append(" ").append(autoIncrementClause);
                }
            } else {
                LogFactory.getLogger().warning(database.getShortName() + " does not support autoincrement columns as requested for " + (database.escapeTableName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName())));
            }
        }
        if (statement.getNotNullColumns().contains(column)) {
            buffer.append(" NOT NULL");
        }
        if (columnIterator.hasNext()) {
            buffer.append(", ");
        }
    }
    buffer.append(",");
    // Fix according to: https://liquibase.jira.com/browse/CORE-1775
    if (isSinglePrimaryKeyColumn && isPrimaryKeyAutoIncrement) {
        if (statement.getPrimaryKeyConstraint() != null && statement.getPrimaryKeyConstraint().getColumns().size() > 0) {
            buffer.append(" PRIMARY KEY (");
            buffer.append(StringUtils.join(primaryKeyColumns, ", "));
            buffer.append(")");
            // Setting up table space for PK's index if it exist
            buffer.append(",");
        }
    }
    for (ForeignKeyConstraint fkConstraint : statement.getForeignKeyConstraints()) {
        String referencesString = fkConstraint.getReferences();
        if (!referencesString.contains(".") && database.getDefaultSchemaName() != null) {
            referencesString = database.getDefaultSchemaName() + "." + referencesString;
        }
        buffer.append(" FOREIGN KEY (").append(database.escapeColumnName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName(), fkConstraint.getColumn())).append(") REFERENCES ").append(referencesString);
        if (fkConstraint.isDeleteCascade()) {
            buffer.append(" ON DELETE CASCADE");
        }
        buffer.append(" CONSTRAINT ");
        buffer.append(database.escapeConstraintName(fkConstraint.getForeignKeyName()));
        if (fkConstraint.isInitiallyDeferred()) {
            buffer.append(" INITIALLY DEFERRED");
        }
        if (fkConstraint.isDeferrable()) {
            buffer.append(" DEFERRABLE");
        }
        buffer.append(",");
    }
    for (UniqueConstraint uniqueConstraint : statement.getUniqueConstraints()) {
        if (uniqueConstraint.getConstraintName() != null && !constraintNameAfterUnique(database)) {
            buffer.append(" CONSTRAINT ");
            buffer.append(database.escapeConstraintName(uniqueConstraint.getConstraintName()));
        }
        buffer.append(" UNIQUE (");
        buffer.append(database.escapeColumnNameList(StringUtils.join(uniqueConstraint.getColumns(), ", ")));
        buffer.append(")");
        if (uniqueConstraint.getConstraintName() != null && constraintNameAfterUnique(database)) {
            buffer.append(" CONSTRAINT ");
            buffer.append(database.escapeConstraintName(uniqueConstraint.getConstraintName()));
        }
        buffer.append(",");
    }
    String sql = buffer.toString().replaceFirst(",\\s*$", "") + ")";
    if (statement.getTablespace() != null && database.supportsTablespaces()) {
        sql += " IN " + statement.getTablespace();
    }
    return new Sql[] { new UnparsedSql(sql, new Table().setName(statement.getTableName()).setSchema(new Schema(statement.getCatalogName(), statement.getSchemaName()))) };
}

Example 3 with UnparsedSql

use of liquibase.sql.UnparsedSql in project liquibase by liquibase.

the class CreateViewGenerator method generateSql.

@Override
public Sql[] generateSql(CreateViewStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    if (database instanceof InformixDatabase) {
        return new CreateViewGeneratorInformix().generateSql(statement, database, sqlGeneratorChain);
    }
    List<Sql> sql = new ArrayList<Sql>();
    StringClauses viewDefinition = SqlParser.parse(statement.getSelectQuery(), true, true);
    if (!statement.isFullDefinition()) {
        viewDefinition.prepend(" ").prepend("AS").prepend(" ").prepend(database.escapeViewName(statement.getCatalogName(), statement.getSchemaName(), statement.getViewName())).prepend(" ").prepend("VIEW").prepend(" ").prepend("CREATE");
    }
    if (statement.isReplaceIfExists()) {
        if (database instanceof FirebirdDatabase) {
            viewDefinition.replaceIfExists("CREATE", "RECREATE");
        } else if (database instanceof SybaseASADatabase && statement.getSelectQuery().toLowerCase().startsWith("create view")) {
        // Sybase ASA saves view definitions with header.
        } else if (database instanceof MSSQLDatabase) {
            //from http://stackoverflow.com/questions/163246/sql-server-equivalent-to-oracles-create-or-replace-view
            CatalogAndSchema schema = new CatalogAndSchema(statement.getCatalogName(), statement.getSchemaName()).customize(database);
            sql.add(new UnparsedSql("IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[" + schema.getSchemaName() + "].[" + statement.getViewName() + "]'))\n" + "    EXEC sp_executesql N'CREATE VIEW [" + schema.getSchemaName() + "].[" + statement.getViewName() + "] AS SELECT ''This is a code stub which will be replaced by an Alter Statement'' as [code_stub]'"));
            viewDefinition.replaceIfExists("CREATE", "ALTER");
        } else if (database instanceof PostgresDatabase) {
            sql.add(new UnparsedSql("DROP VIEW IF EXISTS " + database.escapeViewName(statement.getCatalogName(), statement.getSchemaName(), statement.getViewName())));
        } else {
            if (!viewDefinition.contains("replace")) {
                viewDefinition.replace("CREATE", "CREATE OR REPLACE");
            }
        }
    }
    sql.add(new UnparsedSql(viewDefinition.toString(), getAffectedView(statement)));
    return sql.toArray(new Sql[sql.size()]);
}

Example 4 with UnparsedSql

use of liquibase.sql.UnparsedSql in project liquibase by liquibase.

the class CreateViewGeneratorInformix method generateSql.

@Override
public Sql[] generateSql(CreateViewStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String viewName = database.escapeViewName(statement.getCatalogName(), statement.getSchemaName(), statement.getViewName());
    String createClause = "CREATE VIEW  " + viewName + " AS SELECT * FROM (" + statement.getSelectQuery() + ") AS v";
    if (statement.isReplaceIfExists()) {
        return new Sql[] { new UnparsedSql("DROP VIEW IF EXISTS " + viewName), new UnparsedSql(createClause, new View().setName(viewName).setSchema(statement.getCatalogName(), statement.getViewName())) };
    }
    return new Sql[] { new UnparsedSql(createClause) };
}

Example 5 with UnparsedSql

use of liquibase.sql.UnparsedSql in project liquibase by liquibase.

the class DropColumnGenerator method generateMultipleColumnSql.

private Sql[] generateMultipleColumnSql(List<DropColumnStatement> columns, Database database) {
    List<Sql> result = new ArrayList<Sql>();
    if (database instanceof MySQLDatabase) {
        String alterTable = "ALTER TABLE " + database.escapeTableName(columns.get(0).getCatalogName(), columns.get(0).getSchemaName(), columns.get(0).getTableName());
        for (int i = 0; i < columns.size(); i++) {
            alterTable += " DROP " + database.escapeColumnName(columns.get(i).getCatalogName(), columns.get(i).getSchemaName(), columns.get(i).getTableName(), columns.get(i).getColumnName());
            if (i < columns.size() - 1) {
                alterTable += ",";
            }
        }
        result.add(new UnparsedSql(alterTable, getAffectedColumns(columns)));
    } else {
        for (DropColumnStatement column : columns) {
            result.add(generateSingleColumnSql(column, database)[0]);
        }
    }
    return result.toArray(new Sql[result.size()]);
}