Java Sql类代码示例

import liquibase.sql.Sql; //导入依赖的package包/类
@Override
public Sql[] generateSql(RemoveChangeSetRanStatusStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    ChangeSet changeSet = statement.getChangeSet();
    String tmpTable = UUID.randomUUID().toString().replaceAll("-", "");
    String catalogName = database.getLiquibaseCatalogName();
    String schemaName = database.getDefaultSchemaName();
    String tableName = database.getDatabaseChangeLogTableName();
    CreateTableAsSelectStatement createTableAsSelectStatement = new CreateTableAsSelectStatement(catalogName, schemaName, tableName, tmpTable)
            .addColumnNames("ID", "AUTHOR", "FILENAME", "DATEEXECUTED", "ORDEREXECUTED", "EXECTYPE", "MD5SUM", "DESCRIPTION", "COMMENTS", "TAG", "LIQUIBASE", "CONTEXTS", "LABELS", "DEPLOYMENT_ID")
            .setWhereCondition(database.escapeObjectName("ID", Column.class) + " != ? " +
                    "AND " + database.escapeObjectName("FILENAME", Column.class) + " != ?")
            .addWhereParameters(changeSet.getId(), changeSet.getFilePath());

    return CustomSqlGenerator.generateSql(database,
            createTableAsSelectStatement,
            new DropTableStatement(catalogName, schemaName, tableName, false),
            new RenameTableStatement(catalogName, schemaName, tmpTable, tableName));
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
@Override
public Sql[] generateSql(TagDatabaseStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String catalogName = database.getLiquibaseCatalogName();
    String schemaName = database.getDefaultSchemaName();
    String tableName = database.getDatabaseChangeLogTableName();
    String tableNameEscaped = database.escapeObjectName(tableName, Table.class);
    String dateColumnNameEscaped = database.escapeObjectName("DATEEXECUTED", Column.class);
    String tagColumnNameEscaped = database.escapeObjectName("TAG", Column.class);
    String tempTable = UUID.randomUUID().toString().replaceAll("-", "");
    CreateTableAsSelectStatement createTableAsSelectStatement = new CreateTableAsSelectStatement(catalogName, schemaName, tableName, tempTable)
            .addColumnNames("ID", "AUTHOR", "FILENAME", "DATEEXECUTED", "ORDEREXECUTED", "EXECTYPE", "MD5SUM", "DESCRIPTION", "COMMENTS", "TAG", "LIQUIBASE", "CONTEXTS", "LABELS", "DEPLOYMENT_ID")
            .setWhereCondition(tableNameEscaped + "." + dateColumnNameEscaped + " NOT IN (SELECT MAX(" + tableNameEscaped + "." + dateColumnNameEscaped + ") " +
                    "FROM " + tableNameEscaped + ")");
    InsertAsSelectStatement insertAsSelectStatement = new InsertAsSelectStatement(catalogName, schemaName, tableName, tempTable)
            .addColumnNames("ID", "AUTHOR", "FILENAME", "DATEEXECUTED", "ORDEREXECUTED", "EXECTYPE", "MD5SUM", "DESCRIPTION", "COMMENTS", "'" + statement.getTag() + "'", "LIQUIBASE", "CONTEXTS", "LABELS", "DEPLOYMENT_ID")
            .setWhereCondition(tableNameEscaped + "." + dateColumnNameEscaped + " IN (SELECT MAX(" + tableNameEscaped + "." + dateColumnNameEscaped + ") FROM " + tableNameEscaped + ") AND ("
                    + tableNameEscaped + "." + tagColumnNameEscaped + " IS NULL OR " + tableNameEscaped + "." + tagColumnNameEscaped + " != ?)").addWhereParameters(statement.getTag());

    return CustomSqlGenerator.generateSql(database,
            createTableAsSelectStatement,
            insertAsSelectStatement,
            new DropTableStatement(catalogName, schemaName, tableName, false),
            new RenameTableStatement(catalogName, schemaName, tempTable, tableName));
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
@Override
public Sql[] generateSql(CreateDatabaseChangeLogTableStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String TypeNameChar = "STRING";

    CreateTableStatement createTableStatement = new CreateTableStatement(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName())
            .setTablespace(database.getLiquibaseTablespaceName())
            .addColumn("ID", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("AUTHOR", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("FILENAME", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("DATEEXECUTED", DataTypeFactory.getInstance().fromDescription("TIMESTAMP", database))
            .addColumn("ORDEREXECUTED", DataTypeFactory.getInstance().fromDescription("INT", database))
            .addColumn("EXECTYPE", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("MD5SUM", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("DESCRIPTION", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("COMMENTS", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("TAG", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("LIQUIBASE", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("CONTEXTS", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("LABELS", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database))
            .addColumn("DEPLOYMENT_ID", DataTypeFactory.getInstance().fromDescription(TypeNameChar, database));

    return CustomSqlGenerator.generateSql(database, UserSessionSettings.syncDdlStart(), createTableStatement, UserSessionSettings.syncDdlStop());
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
@Override
public Sql[] generateSql(RemoveChangeSetRanStatusStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    ChangeSet changeSet = statement.getChangeSet();
    String tmpTable = UUID.randomUUID().toString().replaceAll("-", "");
    String catalogName = database.getLiquibaseCatalogName();
    String schemaName = database.getDefaultSchemaName();
    String tableName = database.getDatabaseChangeLogTableName();
    CreateTableAsSelectStatement createTableAsSelectStatement = new CreateTableAsSelectStatement(catalogName, schemaName, tableName, tmpTable)
            .addColumnNames("ID", "AUTHOR", "FILENAME", "DATEEXECUTED", "ORDEREXECUTED", "EXECTYPE", "MD5SUM", "DESCRIPTION", "COMMENTS", "TAG", "LIQUIBASE", "CONTEXTS", "LABELS", "DEPLOYMENT_ID")
            .setWhereCondition(" NOT (" + database.escapeObjectName("ID", Column.class) + " = ? " +
                    "AND " + database.escapeObjectName("FILENAME", Column.class) + " = ?)")
            .addWhereParameters(changeSet.getId(), changeSet.getFilePath());

    return CustomSqlGenerator.generateSql(database,
            UserSessionSettings.syncDdlStart(),
            createTableAsSelectStatement,
            new DropTableStatement(catalogName, schemaName, tableName, false),
            new RenameTableStatement(catalogName, schemaName, tmpTable, tableName),
            UserSessionSettings.syncDdlStop());
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
@Override
public Sql[] generateSql(LockDatabaseChangeLogStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String catalogName = database.getLiquibaseCatalogName();
    String schemaName = database.getDefaultSchemaName();
    String tableName = database.getDatabaseChangeLogLockTableName();
    InsertStatement insertStatement = new InsertStatement(catalogName, schemaName, tableName)
            .addColumnValue("ID", 1)
            .addColumnValue("LOCKED", Boolean.TRUE)
            .addColumnValue("LOCKEDBY", hostname + " [" + hostaddress + "]")
            .addColumnValue("LOCKGRANTED", DateTimeUtils.getCurrentTS("yyyy-MM-dd HH:mm:ss"));

    return CustomSqlGenerator.generateSql(database,
            UserSessionSettings.syncDdlStart(),
            new TruncateTableStatement(catalogName, schemaName, tableName),
            insertStatement,
            UserSessionSettings.syncDdlStop());
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
/**
 * 1. View creation to be set to run once only, no more runAlways="true".
 * 2. If there is a materialized view with the same name as this view, we drop it.
 * 3. We can then can create the standard view: 'create or replace force view as ... select 1 from dual' 
 * 4. If the environment is set for materialized views, convert all views to materialized views at the end using the v99.99.99 script.
 */
public Sql[] generateSql( CreateFlexibleViewStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain ) {
	// check if the view already exists, then formulate a plan from there!
	String viewName = statement.getViewName().toUpperCase();
	List<Sql> sequel = new ArrayList<Sql>();

	OracleMaterializedViewExistsPrecondition matViewExistsCondition = new OracleMaterializedViewExistsPrecondition();
	matViewExistsCondition.setViewName( viewName );
	if ( matViewExistsCondition.check( database ) ) {
		DropMaterializedViewStatement dropMViewStmt = new DropMaterializedViewStatement( statement.getViewName() );
		dropMViewStmt.setSchemaName( database.getLiquibaseSchemaName() );
		DropMaterializedViewOracle dropMViewGen = new DropMaterializedViewOracle();
		sequel.addAll( Arrays.asList( dropMViewGen.generateSql( dropMViewStmt, database, null ) ) );
	}
	
	// if the view exists as a regular view already, we don't actually care.
	CreateViewStatement createViewStmt = new CreateViewStatement( database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), statement.getViewName(), statement.getSelectQuery(), true );

	CreateViewGenerator createViewGen = new CreateViewGenerator();
	sequel.addAll( Arrays.asList( createViewGen.generateSql( createViewStmt, database, null ) ) );

	return sequel.toArray( new Sql[0] );
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
public Sql[] generateSql( ConvertViewsIntoMaterializedViewsStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain ) {
	String sql; 

	List<Sql> sqlList = new ArrayList<Sql>();
	RawSqlGenerator rawSqlGen = new RawSqlGenerator();
	
	sql = readSqlFile( "liquibase/ext/flexibleview/createViewConversionExcludesTable.sql" );
	sqlList.addAll( Arrays.asList( rawSqlGen.generateSql( new RawSqlStatement( sql, "" ), database, null ) ) );
	
	sql = readSqlFile( "liquibase/ext/flexibleview/createViewDependencyGraphTempTable.sql" );
	sqlList.addAll( Arrays.asList( rawSqlGen.generateSql( new RawSqlStatement( sql, "" ), database, null ) ) );

	sql = readSqlFile( "liquibase/ext/flexibleview/convertViewsToMaterializedViews.sql" );
	sqlList.addAll( Arrays.asList( rawSqlGen.generateSql( new RawSqlStatement( sql, "" ), database, null ) ) );
	
	sql = readSqlFile( "liquibase/ext/flexibleview/dropViewDependencyGraphTempTable.sql" );
	sqlList.addAll( Arrays.asList( rawSqlGen.generateSql( new RawSqlStatement( sql, "" ), database, null ) ) );

	return sqlList.toArray( new Sql[0] );
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
public Set<DatabaseObject> getAffectedDatabaseObjects(SqlStatement statement, Database database) {
    Set<DatabaseObject> affectedObjects = new HashSet<DatabaseObject>();

    SqlGeneratorChain sqlGeneratorChain = createGeneratorChain(statement, database);
    if (sqlGeneratorChain != null) {
        //noinspection unchecked
        Sql[] sqls = sqlGeneratorChain.generateSql(statement, database);
        if (sqls != null) {
            for (Sql sql : sqls) {
                affectedObjects.addAll(sql.getAffectedDatabaseObjects());
            }
        }
    }

    return affectedObjects;

}
 

import liquibase.sql.Sql; //导入依赖的package包/类
/**
 * Tests
 * {@link CreateSpatialIndexGeneratorPostgreSQL#generateSql(CreateSpatialIndexStatement, Database, SqlGeneratorChain)}
 * with a variety of inputs.
 * 
 * @param statement
 */
@Test(dataProvider = "generateSqlTestData")
public void testGenerateSql(final CreateSpatialIndexStatement statement) {
   final CreateSpatialIndexGeneratorPostgreSQL generator = new CreateSpatialIndexGeneratorPostgreSQL();
   final Database database = new PostgresDatabase();
   final SqlGeneratorChain sqlGeneratorChain = mock(SqlGeneratorChain.class);
   final Sql[] result = generator.generateSql(statement, database, sqlGeneratorChain);
   assertNotNull(result);
   assertEquals(result.length, 1);
   final String sql = result[0].toSql();
   String pattern = "(?i)CREATE INDEX ";
   pattern += statement.getIndexName() + " ON ";
   if (statement.getTableSchemaName() != null) {
      pattern += statement.getTableSchemaName() + '.';
   }
   pattern += statement.getTableName() + " USING GIST \\(" + statement.getColumns()[0];
   if (statement.getColumns().length > 1) {
      pattern += ", " + statement.getColumns()[1];
   }
   pattern += "\\)";
   assertTrue(sql.matches(pattern), "'" + sql + "' does not match the pattern '" + pattern + "'");
   assertNotNull(result[0].getAffectedDatabaseObjects());
   assertTrue(result[0].getAffectedDatabaseObjects().size() > 1, result[0]
         .getAffectedDatabaseObjects().toString());
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
@Test
public void testWithMultipleTables() {
    for (final Database database : TestContext.getInstance().getAllDatabases()) {
        if (database instanceof PostgresDatabase) {
        	final SetSequenceStatement statement = new SetSequenceStatement("SEQUENCE_NAME");
        	statement.addTable("TABLE1_NAME", "COLUMN1_NAME");
        	statement.addTable("TABLE2_NAME", "COLUMN2_NAME");
            if (shouldBeImplementation(database)) {
                final SqlGenerator<SetSequenceStatement> generator = this.generatorUnderTest;
                final String tempTableName = ((SetSequenceGenerator)generator).getTempTableName();
	final Sql[] sql = generator.generateSql(statement, database, null);
	assertEquals(
                	"SELECT pg_catalog.setval('SEQUENCE_NAME',(SELECT max(" + tempTableName + ".id)+1 AS id FROM ((SELECT max(COLUMN1_NAME) AS id FROM TABLE1_NAME LIMIT 1) UNION (SELECT max(COLUMN2_NAME) AS id FROM TABLE2_NAME LIMIT 1)) AS " + tempTableName + " LIMIT 1),true);",
                	sql[0].toSql()
                );
            }
        }
    }
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
@Override
public Sql[] generateSql(final DropSpatialIndexStatement statement,
      final Database database, final SqlGeneratorChain sqlGeneratorChain) {
   final String catalogName = statement.getTableCatalogName();
   String schemaName = statement.getTableSchemaName();
   if (schemaName == null) {
      schemaName = database.getDefaultSchemaName();
   }

   final StringBuilder sql = new StringBuilder("CALL ");
   sql.append(schemaName).append(".DropSpatialIndex(");

   // Add the schema name parameter.
   sql.append("'").append(database.escapeStringForDatabase(schemaName)).append("'");
   sql.append(", ");

   // Add the table name parameter.
   final String tableName = statement.getTableName();
   sql.append("'").append(database.escapeStringForDatabase(tableName)).append("'");
   sql.append(')');

   final Table hatboxTable = new Table().setName(tableName + "_HATBOX");
   hatboxTable.setSchema(catalogName, schemaName);
   final UnparsedSql spatialize = new UnparsedSql(sql.toString(), hatboxTable);
   return new Sql[] { spatialize };
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
public Sql[] generateSql(CreateDatabaseChangeLogTableStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    TypeConverter typeConverter = TypeConverterFactory.getInstance().findTypeConverter(database);
    CreateTableStatement createTableStatement = new CreateTableStatement(database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName())
            .addPrimaryKeyColumn("ID", typeConverter.getDataType("VARCHAR("+getIdColumnSize()+")", false), null, null, null,new NotNullConstraint())
            .addPrimaryKeyColumn("AUTHOR", typeConverter.getDataType("VARCHAR("+getAuthorColumnSize()+")", false), null, null, null,new NotNullConstraint())
            .addPrimaryKeyColumn("FILENAME", typeConverter.getDataType("VARCHAR("+getFilenameColumnSize()+")", false), null, null, null,new NotNullConstraint())
            .addColumn("DATEEXECUTED", typeConverter.getDateTimeType(), null, new NotNullConstraint())
            .addColumn("ORDEREXECUTED", typeConverter.getDataType("INT", false), new NotNullConstraint())
            .addColumn("EXECTYPE", typeConverter.getDataType("VARCHAR(10)", false), new NotNullConstraint())
            .addColumn("MD5SUM", typeConverter.getDataType("VARCHAR(35)", false))
            .addColumn("DESCRIPTION", typeConverter.getDataType("VARCHAR(255)", false))
            .addColumn("COMMENTS", typeConverter.getDataType("VARCHAR(255)", false))
            .addColumn("TAG", typeConverter.getDataType("VARCHAR(255)", false))
            .addColumn("LIQUIBASE", typeConverter.getDataType("VARCHAR(20)", false));

    return SqlGeneratorFactory.getInstance().generateSql(createTableStatement, database);
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
public Sql[] generateSql(GetViewDefinitionStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    try {
        String sql = "select view_definition from information_schema.views where upper(table_name)='" + statement.getViewName().toUpperCase() + "'";
        if (database.convertRequestedSchemaToCatalog(statement.getSchemaName()) != null) {
            sql += " and table_schema='" + database.convertRequestedSchemaToSchema(statement.getSchemaName()) + "'";
        } else if (database.convertRequestedSchemaToCatalog(statement.getSchemaName()) != null) {
            sql += " and table_catalog='" + database.convertRequestedSchemaToCatalog(statement.getSchemaName()) + "'";
        }

        return new Sql[] {
                new UnparsedSql(sql)
        };
    } catch (DatabaseException e) {
        throw new UnexpectedLiquibaseException(e);
    }
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
public Sql[] generateSql(SelectSequencesStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    try {
        String schema = statement.getSchemaName();
        
        return new Sql[] {
                new UnparsedSql("SELECT relname AS SEQUENCE_NAME FROM pg_class, pg_namespace " +
            "WHERE relkind='S' " +
            "AND pg_class.relnamespace = pg_namespace.oid " +
            "AND nspname = '" + database.convertRequestedSchemaToSchema(schema) + "' " +
            "AND 'nextval(''" + (schema == null ? "" : schema + ".") + "'||relname||'''::regclass)' not in (select adsrc from pg_attrdef where adsrc is not null) " +
            "AND 'nextval(''" + (schema == null ? "" : schema + ".") + "\"'||relname||'\"''::regclass)' not in (select adsrc from pg_attrdef where adsrc is not null) " +
            "AND 'nextval('''||relname||'''::regclass)' not in (select adsrc from pg_attrdef where adsrc is not null)")
        };
    } catch (DatabaseException e) {
        throw new UnexpectedLiquibaseException(e);
    }
}
 

import liquibase.sql.Sql; //导入依赖的package包/类
/**
 * Tests
 * {@link CreateSpatialIndexGeneratorMySQL#generateSql(CreateSpatialIndexStatement, Database, SqlGeneratorChain)}
 * with a variety of inputs.
 * 
 * @param statement
 */
@Test(dataProvider = "generateSqlTestData")
public void testGenerateSql(final CreateSpatialIndexStatement statement) {
   final CreateSpatialIndexGeneratorMySQL generator = new CreateSpatialIndexGeneratorMySQL();
   final Database database = new MySQLDatabase();
   final SqlGeneratorChain sqlGeneratorChain = mock(SqlGeneratorChain.class);
   final Sql[] result = generator.generateSql(statement, database, sqlGeneratorChain);
   assertNotNull(result);
   assertEquals(result.length, 1);
   final String sql = result[0].toSql();
   String pattern = "(?i)CREATE SPATIAL INDEX " + statement.getIndexName() + " ON ";
   if (statement.getTableCatalogName() != null) {
      pattern += statement.getTableCatalogName() + '.';
   } else if (statement.getTableSchemaName() != null) {
      pattern += statement.getTableSchemaName() + '.';
   }
   pattern += statement.getTableName() + "\\(" + statement.getColumns()[0] + "\\)";
   assertTrue(sql.matches(pattern), "'" + sql + "' does not match the pattern '" + pattern + "'");
   assertNotNull(result[0].getAffectedDatabaseObjects());
   assertTrue(result[0].getAffectedDatabaseObjects().size() > 1, result[0]
         .getAffectedDatabaseObjects().toString());
}