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

Example 21 with Sql

use of liquibase.sql.Sql 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()) };
}

Example 22 with Sql

use of liquibase.sql.Sql 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()) };
}

Example 23 with Sql

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

the class InsertOrUpdateGenerator method getInsertStatement.

protected String getInsertStatement(InsertOrUpdateStatement insertOrUpdateStatement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    StringBuffer insertBuffer = new StringBuffer();
    InsertGenerator insert = new InsertGenerator();
    Sql[] insertSql = insert.generateSql(insertOrUpdateStatement, database, sqlGeneratorChain);
    for (Sql s : insertSql) {
        insertBuffer.append(s.toSql());
        insertBuffer.append(";");
    }
    insertBuffer.append("\n");
    return insertBuffer.toString();
}

Example 24 with Sql

use of liquibase.sql.Sql 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()]);
}

Example 25 with Sql

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

the class MarkChangeSetRanGenerator method generateSql.

@Override
public Sql[] generateSql(MarkChangeSetRanStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String dateValue = database.getCurrentDateTimeFunction();
    ChangeSet changeSet = statement.getChangeSet();
    SqlStatement runStatement;
    try {
        if (statement.getExecType().equals(ChangeSet.ExecType.FAILED) || statement.getExecType().equals(ChangeSet.ExecType.SKIPPED)) {
            //don't mark
            return new Sql[0];
        }
        String tag = null;
        for (Change change : changeSet.getChanges()) {
            if (change instanceof TagDatabaseChange) {
                TagDatabaseChange tagChange = (TagDatabaseChange) change;
                tag = tagChange.getTag();
            }
        }
        if (statement.getExecType().ranBefore) {
            runStatement = new UpdateStatement(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName()).addNewColumnValue("DATEEXECUTED", new DatabaseFunction(dateValue)).addNewColumnValue("ORDEREXECUTED", ChangeLogHistoryServiceFactory.getInstance().getChangeLogService(database).getNextSequenceValue()).addNewColumnValue("MD5SUM", changeSet.generateCheckSum().toString()).addNewColumnValue("EXECTYPE", statement.getExecType().value).addNewColumnValue("DEPLOYMENT_ID", ChangeLogHistoryServiceFactory.getInstance().getChangeLogService(database).getDeploymentId()).setWhereClause(database.escapeObjectName("ID", Column.class) + " = ? " + "AND " + database.escapeObjectName("AUTHOR", Column.class) + " = ? " + "AND " + database.escapeObjectName("FILENAME", Column.class) + " = ?").addWhereParameters(changeSet.getId(), changeSet.getAuthor(), changeSet.getFilePath());
            if (tag != null) {
                ((UpdateStatement) runStatement).addNewColumnValue("TAG", tag);
            }
        } else {
            runStatement = new InsertStatement(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName()).addColumnValue("ID", changeSet.getId()).addColumnValue("AUTHOR", changeSet.getAuthor()).addColumnValue("FILENAME", changeSet.getFilePath()).addColumnValue("DATEEXECUTED", new DatabaseFunction(dateValue)).addColumnValue("ORDEREXECUTED", ChangeLogHistoryServiceFactory.getInstance().getChangeLogService(database).getNextSequenceValue()).addColumnValue("MD5SUM", changeSet.generateCheckSum().toString()).addColumnValue("DESCRIPTION", limitSize(changeSet.getDescription())).addColumnValue("COMMENTS", limitSize(StringUtils.trimToEmpty(changeSet.getComments()))).addColumnValue("EXECTYPE", statement.getExecType().value).addColumnValue("CONTEXTS", changeSet.getContexts() == null || changeSet.getContexts().isEmpty() ? null : changeSet.getContexts().toString()).addColumnValue("LABELS", changeSet.getLabels() == null || changeSet.getLabels().isEmpty() ? null : changeSet.getLabels().toString()).addColumnValue("LIQUIBASE", LiquibaseUtil.getBuildVersion().replaceAll("SNAPSHOT", "SNP")).addColumnValue("DEPLOYMENT_ID", ChangeLogHistoryServiceFactory.getInstance().getChangeLogService(database).getDeploymentId());
            if (tag != null) {
                ((InsertStatement) runStatement).addColumnValue("TAG", tag);
            }
        }
    } catch (LiquibaseException e) {
        throw new UnexpectedLiquibaseException(e);
    }
    return SqlGeneratorFactory.getInstance().generateSql(runStatement, database);
}