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

Example 46 with Sql

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

the class InsertOrUpdateGeneratorOracleTest method ContainsInsertStatement.

@Test
public void ContainsInsertStatement() {
    OracleDatabase database = new OracleDatabase();
    InsertOrUpdateGeneratorOracle generator = new InsertOrUpdateGeneratorOracle();
    InsertOrUpdateStatement statement = new InsertOrUpdateStatement("mycatalog", "myschema", "mytable", "pk_col1");
    statement.addColumnValue("pk_col1", "value1");
    statement.addColumnValue("col2", "value2");
    Sql[] sql = generator.generateSql(statement, database, null);
    String theSql = sql[0].toSql();
    assertTrue(theSql.contains("INSERT INTO mycatalog.mytable (pk_col1, col2) VALUES ('value1', 'value2');"));
    assertTrue(theSql.contains("UPDATE mycatalog.mytable"));
    String[] sqlLines = theSql.split("\n");
    int lineToCheck = 0;
    assertEquals("DECLARE", sqlLines[lineToCheck].trim());
    lineToCheck++;
    assertEquals("v_reccount NUMBER := 0;", sqlLines[lineToCheck].trim());
    lineToCheck++;
    assertEquals("BEGIN", sqlLines[lineToCheck].trim());
    lineToCheck++;
    assertEquals("SELECT COUNT(*) INTO v_reccount FROM mycatalog.mytable WHERE pk_col1 = 'value1';", sqlLines[lineToCheck].trim());
    lineToCheck++;
    assertEquals("IF v_reccount = 0 THEN", sqlLines[lineToCheck].trim());
    lineToCheck++;
    assertEquals("INSERT INTO mycatalog.mytable (pk_col1, col2) VALUES ('value1', 'value2');", sqlLines[lineToCheck]);
    lineToCheck++;
    assertEquals("ELSIF v_reccount = 1 THEN", sqlLines[lineToCheck].trim());
    lineToCheck++;
    assertEquals("UPDATE mycatalog.mytable SET col2 = 'value2' WHERE pk_col1 = 'value1';", sqlLines[lineToCheck].trim());
    lineToCheck++;
    assertEquals("END IF;", sqlLines[lineToCheck].trim());
    lineToCheck++;
    assertEquals("END;", sqlLines[lineToCheck].trim());
/*
DECLARE
  v_prodcount NUMBER := 0;
BEGIN
  -- Check if product with this name already exists
  SELECT COUNT (*)
  INTO   v_prodcount
   FROM books WHERE isbn = 12345678;
  -- Product does not exist
  IF v_prodcount = 0 THEN
   -- Insert row into PRODUCT based on arguments passed
   INSERT INTO books
   VALUES
         ( 12345678,
           98765432,
           'Working with Liquibase');
  -- Product with this name already exists
  ELSIF v_prodcount = 1 THEN
    -- Update the existing product with values
    -- passed as arguments
    UPDATE books
    SET    author_id = 98765432,
           title = 'Working with liquibase'
    WHERE  isbn = 12345678;
  END IF;
END;*/
}

Example 47 with Sql

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

the class AddColumnGeneratorTest method testAddMultipleColumnsMySql.

@Test
public void testAddMultipleColumnsMySql() {
    AddColumnStatement columns = new AddColumnStatement(new AddColumnStatement(null, null, TABLE_NAME, "column1", "INT", null, new NotNullConstraint()), new AddColumnStatement(null, null, TABLE_NAME, "column2", "INT", null, new NotNullConstraint()));
    assertFalse(generatorUnderTest.validate(columns, new MySQLDatabase(), new MockSqlGeneratorChain()).hasErrors());
    Sql[] sql = generatorUnderTest.generateSql(columns, new MySQLDatabase(), new MockSqlGeneratorChain());
    assertEquals(1, sql.length);
    assertEquals("ALTER TABLE " + TABLE_NAME + " ADD column1 INT NOT NULL, ADD column2 INT NOT NULL", sql[0].toSql());
    assertEquals("[DEFAULT, table_name, table_name.column1, table_name.column2]", String.valueOf(sql[0].getAffectedDatabaseObjects()));
}

Example 48 with Sql

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

the class CreateTableGeneratorTest method testAutoIncrementStartWithIncrementByHsqlDatabase.

@Test
public void testAutoIncrementStartWithIncrementByHsqlDatabase() throws Exception {
    for (Database database : TestContext.getInstance().getAllDatabases()) {
        if (database instanceof HsqlDatabase) {
            CreateTableStatement statement = new CreateTableStatement(CATALOG_NAME, SCHEMA_NAME, TABLE_NAME);
            statement.addColumn(COLUMN_NAME1, DataTypeFactory.getInstance().fromDescription("BIGINT{autoIncrement:true}", database), new AutoIncrementConstraint(COLUMN_NAME1, BigInteger.ONE, BigInteger.TEN));
            Sql[] generatedSql = this.generatorUnderTest.generateSql(statement, database, null);
            assertEquals("CREATE TABLE SCHEMA_NAME.TABLE_NAME (COLUMN1_NAME BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 10))", generatedSql[0].toSql());
        }
    }
}

Example 49 with Sql

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

the class CreateTableGeneratorTest method testAutoIncrementStartWithIncrementByMSSQLDatabase.

@Test
public void testAutoIncrementStartWithIncrementByMSSQLDatabase() throws Exception {
    for (Database database : TestContext.getInstance().getAllDatabases()) {
        if (database instanceof MSSQLDatabase) {
            CreateTableStatement statement = new CreateTableStatement(CATALOG_NAME, SCHEMA_NAME, TABLE_NAME);
            statement.addColumn(COLUMN_NAME1, DataTypeFactory.getInstance().fromDescription("BIGINT{autoIncrement:true}", database), new ColumnConstraint[] { new AutoIncrementConstraint(COLUMN_NAME1, BigInteger.ZERO, BigInteger.TEN), new NotNullConstraint(COLUMN_NAME1) });
            Sql[] generatedSql = this.generatorUnderTest.generateSql(statement, database, null);
            assertEquals("CREATE TABLE [CATALOG_NAME].[SCHEMA_NAME].[TABLE_NAME] ([COLUMN1_NAME] [bigint] IDENTITY (0, 10) NOT NULL)", generatedSql[0].toSql());
        }
    }
}

Example 50 with Sql

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

the class CreateTableGeneratorTest method testAutoIncrementStartWithIncrementBySQLiteDatabase.

@Test
public void testAutoIncrementStartWithIncrementBySQLiteDatabase() throws Exception {
    for (Database database : TestContext.getInstance().getAllDatabases()) {
        if (database instanceof SQLiteDatabase) {
            CreateTableStatement statement = new CreateTableStatement(CATALOG_NAME, SCHEMA_NAME, TABLE_NAME);
            statement.addColumn(COLUMN_NAME1, DataTypeFactory.getInstance().fromDescription("BIGINT{autoIncrement:true}", database), new AutoIncrementConstraint(COLUMN_NAME1, BigInteger.valueOf(2), BigInteger.TEN));
            Sql[] generatedSql = this.generatorUnderTest.generateSql(statement, database, null);
            // start with and increment by not supported by SQLite
            assertEquals("CREATE TABLE SCHEMA_NAME.TABLE_NAME (COLUMN1_NAME BIGINT AUTOINCREMENT)", generatedSql[0].toSql());
        }
    }
}