Search in sources :

Example 16 with UnparsedSql

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

the class FindForeignKeyConstraintsGeneratorDB2 method generateSql.

@Override
public Sql[] generateSql(FindForeignKeyConstraintsStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    StringBuilder sb = new StringBuilder();
    if (((DB2Database) database).getDataServerType() == DataServerType.DB2Z) {
        CatalogAndSchema baseTableSchema = new CatalogAndSchema(statement.getBaseTableCatalogName(), statement.getBaseTableSchemaName()).customize(database);
        sb.append("SELECT PK.TBNAME  as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_BASE_TABLE_NAME).append(",");
        sb.append("       PK.NAME    as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_BASE_TABLE_COLUMN_NAME).append(",");
        sb.append("       FK.TBNAME  as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_FOREIGN_TABLE_NAME).append(",");
        sb.append("       FK.COLNAME as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_FOREIGN_COLUMN_NAME).append(",");
        sb.append("       R.RELNAME  as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_CONSTRAINT_NAME).append(" ");
        sb.append("  FROM SYSIBM.SYSRELS R,");
        sb.append("       SYSIBM.SYSFOREIGNKEYS FK,");
        sb.append("       SYSIBM.SYSCOLUMNS PK");
        sb.append(" WHERE R.CREATOR = '").append(baseTableSchema.getSchemaName()).append("'");
        sb.append("   AND R.TBNAME  = '").append(statement.getBaseTableName()).append("'");
        sb.append("   AND R.RELNAME = FK.RELNAME ");
        sb.append("   AND R.CREATOR=FK.CREATOR ");
        sb.append("   AND R.TBNAME=FK.TBNAME ");
        sb.append("   AND R.REFTBCREATOR=PK.TBCREATOR ");
        sb.append("   AND R.REFTBNAME=PK.TBNAME ");
        sb.append("   AND FK.COLSEQ=PK.KEYSEQ ");
        sb.append(" ORDER BY R.RELNAME, FK.COLSEQ asc ");
    } else {
        sb.append("SELECT ");
        sb.append("TABNAME as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_BASE_TABLE_NAME).append(", ");
        sb.append("PK_COLNAMES as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_BASE_TABLE_COLUMN_NAME).append(", ");
        sb.append("REFTABNAME as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_FOREIGN_TABLE_NAME).append(", ");
        sb.append("FK_COLNAMES as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_FOREIGN_COLUMN_NAME).append(",");
        sb.append("CONSTNAME as ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_CONSTRAINT_NAME).append(" ");
        sb.append("FROM SYSCAT.REFERENCES ");
        sb.append("WHERE TABNAME='").append(statement.getBaseTableName()).append("'");
    }
    return new Sql[] { new UnparsedSql(sb.toString()) };
}
Also used : UnparsedSql(liquibase.sql.UnparsedSql) CatalogAndSchema(liquibase.CatalogAndSchema) UnparsedSql(liquibase.sql.UnparsedSql) Sql(liquibase.sql.Sql)

Example 17 with UnparsedSql

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

the class FindForeignKeyConstraintsGeneratorDerby method generateSql.

@Override
public Sql[] generateSql(FindForeignKeyConstraintsStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    final CatalogAndSchema schema = database.correctSchema(new CatalogAndSchema(statement.getBaseTableCatalogName(), statement.getBaseTableSchemaName()));
    final StringBuilder sb = new StringBuilder();
    sb.append("SELECT ");
    sb.append("co.constraintname AS ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_CONSTRAINT_NAME).append(", ");
    sb.append("t.tablename AS ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_BASE_TABLE_NAME).append(", ");
    sb.append("t2.tablename AS ").append(FindForeignKeyConstraintsStatement.RESULT_COLUMN_FOREIGN_TABLE_NAME).append(" ");
    sb.append("FROM sys.sysconstraints co ");
    sb.append("JOIN sys.sysschemas sc ON co.schemaid = sc.schemaid ");
    sb.append("JOIN sys.systables t ON co.tableid = t.tableid ");
    sb.append("JOIN sys.sysforeignkeys f ON co.constraintid = f.constraintid ");
    sb.append("JOIN sys.sysconglomerates cg ON f.conglomerateid = cg.conglomerateid ");
    sb.append("JOIN sys.sysconstraints co2 ON f.keyconstraintid = co2.constraintid ");
    sb.append("JOIN sys.systables t2 ON co2.tableid = t2.tableid ");
    sb.append("JOIN sys.syskeys k ON co2.constraintid = k.constraintid ");
    sb.append("JOIN sys.sysconglomerates cg2 ON k.conglomerateid = cg2.conglomerateid ");
    sb.append("WHERE co.type = 'F' ");
    sb.append("AND sc.schemaname = '").append(schema.getCatalogName()).append("' ");
    sb.append("AND t.tablename = '").append(statement.getBaseTableName()).append("'");
    return new Sql[] { new UnparsedSql(sb.toString()) };
}
Also used : UnparsedSql(liquibase.sql.UnparsedSql) CatalogAndSchema(liquibase.CatalogAndSchema) UnparsedSql(liquibase.sql.UnparsedSql) Sql(liquibase.sql.Sql)

Example 18 with UnparsedSql

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

the class InsertSetGenerator method generateSql.

@Override
public Sql[] generateSql(InsertSetStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    if (statement.peek() == null) {
        return new UnparsedSql[0];
    }
    StringBuffer sql = new StringBuffer();
    generateHeader(sql, statement, database);
    ArrayList<Sql> result = new ArrayList<Sql>();
    int index = 0;
    for (InsertStatement sttmnt : statement.getStatements()) {
        index++;
        getInsertGenerator(database).generateValues(sql, sttmnt, database);
        sql.append(",");
        if (index > statement.getBatchThreshold()) {
            result.add(completeStatement(statement, sql));
            index = 0;
            sql = new StringBuffer();
            generateHeader(sql, statement, database);
        }
    }
    if (index > 0) {
        result.add(completeStatement(statement, sql));
    }
    return result.toArray(new UnparsedSql[result.size()]);
}
Also used : UnparsedSql(liquibase.sql.UnparsedSql) ArrayList(java.util.ArrayList) InsertStatement(liquibase.statement.core.InsertStatement) Sql(liquibase.sql.Sql) UnparsedSql(liquibase.sql.UnparsedSql)

Example 19 with UnparsedSql

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

the class TagDatabaseGenerator method generateSql.

@Override
public Sql[] generateSql(TagDatabaseStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String tableNameEscaped = database.escapeTableName(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName());
    String orderColumnNameEscaped = database.escapeObjectName("ORDEREXECUTED", Column.class);
    String dateColumnNameEscaped = database.escapeObjectName("DATEEXECUTED", Column.class);
    String tagColumnNameEscaped = database.escapeObjectName("TAG", Column.class);
    String tagEscaped = DataTypeFactory.getInstance().fromObject(statement.getTag(), database).objectToSql(statement.getTag(), database);
    if (database instanceof MySQLDatabase) {
        return new Sql[] { new UnparsedSql("UPDATE " + tableNameEscaped + " AS C " + "INNER JOIN (" + "SELECT " + orderColumnNameEscaped + ", " + dateColumnNameEscaped + " " + "FROM " + tableNameEscaped + " order by " + dateColumnNameEscaped + " desc, " + orderColumnNameEscaped + " desc limit 1) AS D " + "ON C." + orderColumnNameEscaped + " = D." + orderColumnNameEscaped + " " + "SET C." + tagColumnNameEscaped + " = " + tagEscaped) };
    } else if (database instanceof PostgresDatabase) {
        return new Sql[] { new UnparsedSql("UPDATE " + tableNameEscaped + " t SET TAG=" + tagEscaped + " FROM (SELECT " + dateColumnNameEscaped + ", " + orderColumnNameEscaped + " FROM " + tableNameEscaped + " ORDER BY " + dateColumnNameEscaped + " DESC, " + orderColumnNameEscaped + " DESC LIMIT 1) sub " + "WHERE t." + dateColumnNameEscaped + "=sub." + dateColumnNameEscaped + " AND t." + orderColumnNameEscaped + "=sub." + orderColumnNameEscaped) };
    } else if (database instanceof InformixDatabase) {
        String tempTableNameEscaped = database.escapeObjectName("max_order_temp", Table.class);
        return new Sql[] { new UnparsedSql("SELECT MAX(" + dateColumnNameEscaped + ") AS " + dateColumnNameEscaped + ", MAX(" + orderColumnNameEscaped + ") AS " + orderColumnNameEscaped + " " + "FROM " + tableNameEscaped + " " + "INTO TEMP " + tempTableNameEscaped + " WITH NO LOG"), new UnparsedSql("UPDATE " + tableNameEscaped + " " + "SET TAG = " + tagEscaped + " " + "WHERE " + dateColumnNameEscaped + " = (" + "SELECT " + dateColumnNameEscaped + " " + "FROM " + tempTableNameEscaped + ") AND " + orderColumnNameEscaped + " = (" + "SELECT " + orderColumnNameEscaped + " " + "FROM " + tempTableNameEscaped + ");"), new UnparsedSql("DROP TABLE " + tempTableNameEscaped + ";") };
    } else if (database instanceof MSSQLDatabase) {
        String changelogAliasEscaped = database.escapeObjectName("changelog", Table.class);
        String latestAliasEscaped = database.escapeObjectName("latest", Table.class);
        String idColumnEscaped = database.escapeObjectName("ID", Column.class);
        String authorColumnEscaped = database.escapeObjectName("AUTHOR", Column.class);
        String filenameColumnEscaped = database.escapeObjectName("FILENAME", Column.class);
        String topClause = "TOP (1)";
        try {
            if (database.getDatabaseMajorVersion() < 10) {
                // SQL Server 2005 or earlier
                topClause = "TOP 1";
            }
        } catch (DatabaseException ignored) {
        // assume SQL Server 2008 or later
        }
        return new Sql[] { new UnparsedSql("UPDATE " + changelogAliasEscaped + " " + "SET " + tagColumnNameEscaped + " = " + tagEscaped + " " + "FROM " + tableNameEscaped + " AS " + changelogAliasEscaped + " " + "INNER JOIN (" + "SELECT " + topClause + " " + idColumnEscaped + ", " + authorColumnEscaped + ", " + filenameColumnEscaped + " " + "FROM " + tableNameEscaped + " " + "ORDER BY " + dateColumnNameEscaped + " DESC, " + orderColumnNameEscaped + " DESC" + ") AS " + latestAliasEscaped + " " + "ON " + latestAliasEscaped + "." + idColumnEscaped + " = " + changelogAliasEscaped + "." + idColumnEscaped + " " + "AND " + latestAliasEscaped + "." + authorColumnEscaped + " = " + changelogAliasEscaped + "." + authorColumnEscaped + " " + "AND " + latestAliasEscaped + "." + filenameColumnEscaped + " = " + changelogAliasEscaped + "." + filenameColumnEscaped) };
    } else if (database instanceof OracleDatabase || database instanceof DB2Database) {
        String selectClause = "SELECT";
        String endClause = ")";
        String delimiter = "";
        if (database instanceof OracleDatabase) {
            selectClause = "SELECT * FROM (SELECT";
            endClause = ") where rownum=1)";
        } else if (database instanceof DB2Database) {
            endClause = " FETCH FIRST 1 ROWS ONLY)";
        }
        return new Sql[] { new UnparsedSql("MERGE INTO " + tableNameEscaped + " a " + "USING (" + selectClause + " " + orderColumnNameEscaped + ", " + dateColumnNameEscaped + " from " + tableNameEscaped + " order by " + dateColumnNameEscaped + " desc, " + orderColumnNameEscaped + " desc" + endClause + " b " + "ON ( a." + dateColumnNameEscaped + " = b." + dateColumnNameEscaped + " and a." + orderColumnNameEscaped + "=b." + orderColumnNameEscaped + " ) " + "WHEN MATCHED THEN " + "UPDATE SET  a.tag=" + tagEscaped + delimiter) };
    } else {
        //Only uses dateexecuted as a default. Depending on the timestamp resolution, multiple rows may be tagged which normally works fine but can cause confusion and some issues.
        //We cannot use orderexecuted alone because it is only guaranteed to be incrementing per update call.
        //TODO: Better handle other databases to use dateexecuted desc, orderexecuted desc.
        UpdateStatement updateStatement = new UpdateStatement(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName()).addNewColumnValue("TAG", statement.getTag()).setWhereClause(dateColumnNameEscaped + " = (" + "SELECT MAX(" + dateColumnNameEscaped + ") " + "FROM " + tableNameEscaped + ")");
        return SqlGeneratorFactory.getInstance().generateSql(updateStatement, database);
    }
}
Also used : UpdateStatement(liquibase.statement.core.UpdateStatement) Table(liquibase.structure.core.Table) UnparsedSql(liquibase.sql.UnparsedSql) Sql(liquibase.sql.Sql) UnparsedSql(liquibase.sql.UnparsedSql) Column(liquibase.structure.core.Column) DatabaseException(liquibase.exception.DatabaseException)

Example 20 with UnparsedSql

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

the class SelectFromDatabaseChangeLogGenerator method generateSql.

@Override
public Sql[] generateSql(SelectFromDatabaseChangeLogStatement statement, final Database database, SqlGeneratorChain sqlGeneratorChain) {
    List<ColumnConfig> columnsToSelect = Arrays.asList(statement.getColumnsToSelect());
    String sql = "SELECT " + (database instanceof MSSQLDatabase && statement.getLimit() != null ? "TOP " + statement.getLimit() + " " : "") + StringUtils.join(columnsToSelect, ",", new StringUtils.StringUtilsFormatter<ColumnConfig>() {

        @Override
        public String toString(ColumnConfig column) {
            if (column.getComputed() != null && column.getComputed()) {
                return column.getName();
            } else {
                return database.escapeColumnName(null, null, null, column.getName());
            }
        }
    }).toUpperCase() + " FROM " + database.escapeTableName(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName());
    SelectFromDatabaseChangeLogStatement.WhereClause whereClause = statement.getWhereClause();
    if (whereClause != null) {
        if (whereClause instanceof SelectFromDatabaseChangeLogStatement.ByTag) {
            sql += " WHERE " + database.escapeColumnName(null, null, null, "TAG") + "='" + ((SelectFromDatabaseChangeLogStatement.ByTag) whereClause).getTagName() + "'";
        } else if (whereClause instanceof SelectFromDatabaseChangeLogStatement.ByNotNullCheckSum) {
            sql += " WHERE " + database.escapeColumnName(null, null, null, "MD5SUM") + " IS NOT NULL";
        } else {
            throw new UnexpectedLiquibaseException("Unknown where clause type: " + whereClause.getClass().getName());
        }
    }
    if (statement.getOrderByColumns() != null && statement.getOrderByColumns().length > 0) {
        sql += " ORDER BY ";
        Iterator<String> orderBy = Arrays.asList(statement.getOrderByColumns()).iterator();
        while (orderBy.hasNext()) {
            String orderColumn = orderBy.next();
            String[] orderColumnData = orderColumn.split(" ");
            sql += database.escapeColumnName(null, null, null, orderColumnData[0]);
            if (orderColumnData.length == 2) {
                sql += " ";
                sql += orderColumnData[1].toUpperCase();
            }
            if (orderBy.hasNext()) {
                sql += ", ";
            }
        }
    }
    if (statement.getLimit() != null) {
        if (database instanceof OracleDatabase) {
            if (whereClause == null) {
                sql += " WHERE ROWNUM=" + statement.getLimit();
            } else {
                sql += " AND ROWNUM=" + statement.getLimit();
            }
        } else if (database instanceof MySQLDatabase || database instanceof PostgresDatabase) {
            sql += " LIMIT " + statement.getLimit();
        } else if (database instanceof DB2Database) {
            sql += " FETCH FIRST " + statement.getLimit() + " ROWS ONLY";
        }
    }
    return new Sql[] { new UnparsedSql(sql) };
}
Also used : ColumnConfig(liquibase.change.ColumnConfig) UnparsedSql(liquibase.sql.UnparsedSql) SelectFromDatabaseChangeLogStatement(liquibase.statement.core.SelectFromDatabaseChangeLogStatement) Sql(liquibase.sql.Sql) UnparsedSql(liquibase.sql.UnparsedSql) StringUtils(liquibase.util.StringUtils) UnexpectedLiquibaseException(liquibase.exception.UnexpectedLiquibaseException)

Aggregations

UnparsedSql (liquibase.sql.UnparsedSql)29 Sql (liquibase.sql.Sql)28 ArrayList (java.util.ArrayList)10 CatalogAndSchema (liquibase.CatalogAndSchema)8 MSSQLDatabase (liquibase.database.core.MSSQLDatabase)3 AutoIncrementConstraint (liquibase.statement.AutoIncrementConstraint)3 ForeignKeyConstraint (liquibase.statement.ForeignKeyConstraint)3 Schema (liquibase.structure.core.Schema)3 LinkedList (java.util.LinkedList)2 AddColumnConfig (liquibase.change.AddColumnConfig)2 ColumnConfig (liquibase.change.ColumnConfig)2 DB2Database (liquibase.database.core.DB2Database)2 MySQLDatabase (liquibase.database.core.MySQLDatabase)2 DatabaseDataType (liquibase.datatype.DatabaseDataType)2 SequenceNextValueFunction (liquibase.statement.SequenceNextValueFunction)2 UniqueConstraint (liquibase.statement.UniqueConstraint)2 Table (liquibase.structure.core.Table)2 StringClauses (liquibase.util.StringClauses)2 BigInteger (java.math.BigInteger)1 InformixDatabase (liquibase.database.core.InformixDatabase)1