Examples with RawSqlStatement liquibase.statement.core.RawSqlStatement used on opensource projects

Example 26 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class MySQLIntegrationTest method dateDefaultValue.

@Test
public void dateDefaultValue() throws Exception {
    if (getDatabase() == null) {
        return;
    }
    Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", getDatabase()).execute(new RawSqlStatement("DROP TABLE IF " + "EXISTS ad"));
    try {
        Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", getDatabase()).execute(new RawSqlStatement("CREATE TABLE ad (\n" + "ad_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n" + "advertiser_id int(10) unsigned NOT NULL,\n" + "ad_type_id int(10) unsigned NOT NULL,\n" + "name varchar(155) NOT NULL DEFAULT '',\n" + "label varchar(155)NOT NULL DEFAULT '',\n" + "description text NOT NULL,\n" + "active tinyint(1) NOT NULL DEFAULT '0',\n" + "created datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n" + "updated datetime DEFAULT '0000-00-00 00:00:00',\n" + "PRIMARY KEY (ad_id),\n" + "KEY active (active)\n" + ")"));
    } catch (DatabaseException e) {
        if (e.getCause() instanceof SQLSyntaxErrorException) {
            Scope.getCurrentScope().getLog(getClass()).warning("MySQL returned DatabaseException", e);
            assumeTrue("MySQL seems to run in strict mode (no datetime literals with 0000-00-00 allowed). " + "Cannot run this test", false);
        } else {
            throw e;
        }
    }
    DatabaseSnapshot snapshot = SnapshotGeneratorFactory.getInstance().createSnapshot(CatalogAndSchema.DEFAULT, getDatabase(), new SnapshotControl(getDatabase()));
    Column createdColumn = snapshot.get(new Column().setRelation(new Table().setName("ad").setSchema(new Schema())).setName("created"));
    Object defaultValue = createdColumn.getDefaultValue();
    assertNotNull(defaultValue);
    assertEquals("0000-00-00 00:00:00", defaultValue);
}

Example 27 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class MariaDBIntegrationTest method dateDefaultValue.

@Test
public void dateDefaultValue() throws Exception {
    if (getDatabase() == null) {
        return;
    }
    Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", getDatabase()).execute(new RawSqlStatement("DROP TABLE IF " + "EXISTS ad"));
    try {
        Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", getDatabase()).execute(new RawSqlStatement("CREATE TABLE ad (\n" + "ad_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n" + "advertiser_id int(10) unsigned NOT NULL,\n" + "ad_type_id int(10) unsigned NOT NULL,\n" + "name varchar(155) NOT NULL DEFAULT '',\n" + "label varchar(155)NOT NULL DEFAULT '',\n" + "description text NOT NULL,\n" + "active tinyint(1) NOT NULL DEFAULT '0',\n" + "created datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n" + "updated datetime DEFAULT '0000-00-00 00:00:00',\n" + "PRIMARY KEY (ad_id),\n" + "KEY active (active)\n" + ")"));
    } catch (DatabaseException e) {
        if (e.getCause() instanceof SQLSyntaxErrorException) {
            Scope.getCurrentScope().getLog(getClass()).warning("MariaDB returned DatabaseException", e);
            assumeTrue("MariaDB seems to run in strict mode (no datetime literals with 0000-00-00 allowed). " + "Cannot run this test", false);
        } else {
            throw e;
        }
    }
    DatabaseSnapshot snapshot = SnapshotGeneratorFactory.getInstance().createSnapshot(CatalogAndSchema.DEFAULT, getDatabase(), new SnapshotControl(getDatabase()));
    Column createdColumn = snapshot.get(new Column().setRelation(new Table().setName("ad").setSchema(new Schema())).setName("created"));
    Object defaultValue = createdColumn.getDefaultValue();
    assertNotNull(defaultValue);
    assertEquals("0000-00-00 00:00:00", defaultValue);
}

Example 28 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class MergeColumnChange method generateStatements.

@Override
public SqlStatement[] generateStatements(final Database database) {
    List<SqlStatement> statements = new ArrayList<>();
    AddColumnChange addNewColumnChange = new AddColumnChange();
    addNewColumnChange.setSchemaName(schemaName);
    addNewColumnChange.setTableName(getTableName());
    final AddColumnConfig columnConfig = new AddColumnConfig();
    columnConfig.setName(getFinalColumnName());
    columnConfig.setType(getFinalColumnType());
    addNewColumnChange.addColumn(columnConfig);
    statements.addAll(Arrays.asList(addNewColumnChange.generateStatements(database)));
    String updateStatement = "";
    if (database instanceof MySQLDatabase || database instanceof MariaDBDatabase) {
        updateStatement = "UPDATE " + database.escapeTableName(getCatalogName(), getSchemaName(), getTableName()) + " SET " + database.escapeObjectName(getFinalColumnName(), Column.class) + " = " + database.getConcatSql("'" + getJoinString() + "'", database.escapeObjectName(getColumn1Name(), Column.class), database.escapeObjectName(getColumn2Name(), Column.class));
    } else {
        updateStatement = "UPDATE " + database.escapeTableName(getCatalogName(), getSchemaName(), getTableName()) + " SET " + database.escapeObjectName(getFinalColumnName(), Column.class) + " = " + database.getConcatSql(database.escapeObjectName(getColumn1Name(), Column.class), "'" + getJoinString() + "'", database.escapeObjectName(getColumn2Name(), Column.class));
    }
    statements.add(new RawSqlStatement(updateStatement));
    if (database instanceof SQLiteDatabase) {
        /* nolgpl: implement */
        // Since SQLite does not support a Merge column statement,
        SQLiteDatabase.AlterTableVisitor alterTableVisitor = new SQLiteDatabase.AlterTableVisitor() {

            @Override
            public ColumnConfig[] getColumnsToAdd() {
                // This gets called after
                ColumnConfig[] columnConfigs = new ColumnConfig[1];
                ColumnConfig mergedColumn = new ColumnConfig();
                mergedColumn.setName(getFinalColumnName());
                mergedColumn.setType(getFinalColumnType());
                columnConfigs[0] = mergedColumn;
                return columnConfigs;
            }

            @Override
            public boolean copyThisColumn(ColumnConfig column) {
                // don't create columns that are merged
                return !column.getName().equals(getColumn1Name()) && !column.getName().equals(getColumn2Name());
            }

            @Override
            public boolean createThisColumn(ColumnConfig column) {
                // don't copy columns that are merged
                return !column.getName().equals(getColumn1Name()) && !column.getName().equals(getColumn2Name());
            }

            @Override
            public boolean createThisIndex(Index index) {
                // skip the index if it has old columns
                for (Column column : index.getColumns()) {
                    if (column.getName().equals(getColumn1Name()) || column.getName().equals(getColumn2Name())) {
                        return false;
                    }
                }
                return true;
            }
        };
        List<SqlStatement> workAroundStatements = null;
        try {
            workAroundStatements = SQLiteDatabase.getAlterTableStatements(alterTableVisitor, database, getCatalogName(), getSchemaName(), getTableName());
            statements.addAll(workAroundStatements);
        } catch (DatabaseException e) {
            throw new UnexpectedLiquibaseException(e);
        }
    } else {
        // ...if it is not a SQLite database
        DropColumnChange dropColumn1Change = new DropColumnChange();
        dropColumn1Change.setSchemaName(schemaName);
        dropColumn1Change.setTableName(getTableName());
        dropColumn1Change.setColumnName(getColumn1Name());
        statements.addAll(Arrays.asList(dropColumn1Change.generateStatements(database)));
        DropColumnChange dropColumn2Change = new DropColumnChange();
        dropColumn2Change.setSchemaName(schemaName);
        dropColumn2Change.setTableName(getTableName());
        dropColumn2Change.setColumnName(getColumn2Name());
        statements.addAll(Arrays.asList(dropColumn2Change.generateStatements(database)));
    }
    return statements.toArray(new SqlStatement[statements.size()]);
}

Example 29 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class SequenceSnapshotGenerator method snapshotObject.

@Override
protected DatabaseObject snapshotObject(DatabaseObject example, DatabaseSnapshot snapshot) throws DatabaseException {
    if (example.getSnapshotId() != null) {
        return example;
    }
    Database database = snapshot.getDatabase();
    List<Map<String, ?>> sequences;
    if (database instanceof Db2zDatabase) {
        sequences = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForList(new RawSqlStatement(getSelectSequenceSql(example.getSchema(), database)));
        return getSequences(example, database, sequences);
    } else {
        if (example.getAttribute("liquibase-complete", false)) {
            // need to go through "snapshotting" the object even if it was previously populated in addTo. Use the "liquibase-complete" attribute to track that it doesn't need to be fully snapshotted
            example.setSnapshotId(SnapshotIdService.getInstance().generateId());
            example.setAttribute("liquibase-complete", null);
            return example;
        }
        if (!database.supportsSequences()) {
            return null;
        }
        sequences = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForList(new RawSqlStatement(getSelectSequenceSql(example.getSchema(), database)));
        DatabaseObject sequenceRow = getSequences(example, database, sequences);
        return sequenceRow;
    }
}

Example 30 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class SequenceSnapshotGenerator method getSelectSequenceSql.

protected String getSelectSequenceSql(Schema schema, Database database) {
    if (database instanceof DB2Database) {
        if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400")) {
            return "SELECT SEQNAME AS SEQUENCE_NAME FROM QSYS2.SYSSEQUENCES WHERE SEQSCHEMA = '" + schema.getCatalogName() + "'";
        }
        return "SELECT SEQNAME AS SEQUENCE_NAME FROM SYSCAT.SEQUENCES WHERE SEQTYPE='S' AND SEQSCHEMA = '" + schema.getCatalogName() + "'";
    } else if (database instanceof Db2zDatabase) {
        return "SELECT NAME AS SEQUENCE_NAME, " + "START AS START_VALUE, " + "MINVALUE AS MIN_VALUE, " + "MAXVALUE AS MAX_VALUE, " + "CACHE AS CACHE_SIZE, " + "INCREMENT AS INCREMENT_BY, " + "CYCLE AS WILL_CYCLE, " + "ORDER AS IS_ORDERED " + "FROM SYSIBM.SYSSEQUENCES WHERE SEQTYPE = 'S' AND SCHEMA = '" + schema.getCatalogName() + "'";
    } else if (database instanceof DerbyDatabase) {
        return "SELECT " + "  seq.SEQUENCENAME AS SEQUENCE_NAME " + "FROM " + "  SYS.SYSSEQUENCES seq, " + "  SYS.SYSSCHEMAS sch " + "WHERE " + "  sch.SCHEMANAME = '" + new CatalogAndSchema(null, schema.getName()).customize(database).getSchemaName() + "' AND " + "  sch.SCHEMAID = seq.SCHEMAID";
    } else if (database instanceof FirebirdDatabase) {
        return "SELECT TRIM(RDB$GENERATOR_NAME) AS SEQUENCE_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0";
    } else if (database instanceof H2Database) {
        try {
            if (database.getDatabaseMajorVersion() <= 1) {
                return "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA = '" + schema.getName() + "' AND IS_GENERATED=FALSE";
            }
        } catch (DatabaseException e) {
            Scope.getCurrentScope().getLog(getClass()).fine("Cannot determine h2 version in order to generate sequence snapshot query");
        }
        return "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA = '" + schema.getName() + "'";
    } else if (database instanceof HsqlDatabase) {
        return "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES WHERE SEQUENCE_SCHEMA = '" + schema.getName() + "'";
    } else if (database instanceof InformixDatabase) {
        return "SELECT tabname AS SEQUENCE_NAME FROM systables t, syssequences s WHERE s.tabid = t.tabid AND t.owner = '" + schema.getName() + "'";
    } else if (database instanceof OracleDatabase) {
        /*
             * Return an SQL statement that only returns the non-default values so the output changeLog is cleaner
             * and less polluted with unnecessary values.
             * The the following pages for the defaults (consistent for all supported releases ATM):
             * 12cR2: http://docs.oracle.com/database/122/SQLRF/CREATE-SEQUENCE.htm
             * 12cR1: http://docs.oracle.com/database/121/SQLRF/statements_6017.htm
             * 11gR2: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6015.htm
             */
        return "SELECT sequence_name, \n" + "CASE WHEN increment_by > 0 \n" + "     THEN CASE WHEN min_value=1 THEN NULL ELSE min_value END\n" + "     ELSE CASE WHEN min_value=(-999999999999999999999999999) THEN NULL else min_value END\n" + "END AS min_value, \n" + "CASE WHEN increment_by > 0 \n" + "     THEN CASE WHEN max_value=999999999999999999999999999 THEN NULL ELSE max_value END\n" + "     ELSE CASE WHEN max_value=last_number THEN NULL else max_value END \n" + "END  AS max_value, \n" + "CASE WHEN increment_by = 1 THEN NULL ELSE increment_by END AS increment_by, \n" + "CASE WHEN cycle_flag = 'N' THEN NULL ELSE cycle_flag END AS will_cycle, \n" + "CASE WHEN order_flag = 'N' THEN NULL ELSE order_flag END AS is_ordered, \n" + "LAST_NUMBER as START_VALUE, \n" + "CASE WHEN cache_size = 20 THEN NULL ELSE cache_size END AS cache_size \n" + "FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = '" + schema.getCatalogName() + "'";
    } else if (database instanceof PostgresDatabase) {
        int version = 9;
        try {
            version = database.getDatabaseMajorVersion();
        } catch (Exception ignore) {
            Scope.getCurrentScope().getLog(getClass()).warning("Failed to retrieve database version: " + ignore);
        }
        if (version < 10) {
            // 'pg_sequence' view does not exists yet
            return "SELECT c.relname AS \"SEQUENCE_NAME\" FROM pg_class c " + "join pg_namespace on c.relnamespace = pg_namespace.oid " + "WHERE c.relkind='S' " + "AND nspname = '" + schema.getName() + "' " + "AND c.oid not in (select d.objid FROM pg_depend d where d.refobjsubid > 0)";
        } else {
            return "SELECT c.relname AS \"SEQUENCE_NAME\", " + "  s.seqmin AS \"MIN_VALUE\", s.seqmax AS \"MAX_VALUE\", s.seqincrement AS \"INCREMENT_BY\", " + "  s.seqcycle AS \"WILL_CYCLE\", s.seqstart AS \"START_VALUE\", s.seqcache AS \"CACHE_SIZE\", " + "  pg_catalog.format_type(s.seqtypid, NULL) AS \"SEQ_TYPE\" " + "FROM pg_class c " + "JOIN pg_namespace ns on c.relnamespace = ns.oid " + "JOIN pg_sequence s on c.oid = s.seqrelid " + "WHERE c.relkind = 'S' " + "AND ns.nspname = '" + schema.getName() + "' " + "AND c.oid not in (select d.objid FROM pg_depend d where d.refobjsubid > 0)";
        }
    } else if (database instanceof MSSQLDatabase) {
        return "SELECT SEQUENCE_NAME, " + "cast(START_VALUE AS BIGINT) AS START_VALUE, " + "cast(MINIMUM_VALUE AS BIGINT) AS MIN_VALUE, " + "cast(MAXIMUM_VALUE AS BIGINT) AS MAX_VALUE, " + "CAST(INCREMENT AS BIGINT) AS INCREMENT_BY, " + "CYCLE_OPTION AS WILL_CYCLE " + "FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA = '" + schema.getName() + "'";
    } else if (database instanceof MariaDBDatabase) {
        StringJoiner j = new StringJoiner(" \n UNION\n");
        try {
            List<Map<String, ?>> res = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForList(new RawSqlStatement("select table_name AS SEQUENCE_NAME " + "from information_schema.TABLES " + "where TABLE_SCHEMA = '" + schema.getName() + "' " + "and TABLE_TYPE = 'SEQUENCE' order by table_name;"));
            if (res.size() == 0) {
                return "SELECT 'name' AS SEQUENCE_NAME from dual WHERE 1=0";
            }
            for (Map<String, ?> e : res) {
                String seqName = (String) e.get("SEQUENCE_NAME");
                j.add(String.format("SELECT '%s' AS SEQUENCE_NAME, " + "START_VALUE AS START_VALUE, " + "MINIMUM_VALUE AS MIN_VALUE, " + "MAXIMUM_VALUE AS MAX_VALUE, " + "INCREMENT AS INCREMENT_BY, " + "CYCLE_OPTION AS WILL_CYCLE " + "FROM %s ", seqName, seqName));
            }
        } catch (DatabaseException e) {
            throw new UnexpectedLiquibaseException("Could not get list of schemas ", e);
        }
        return j.toString();
    } else if (database instanceof SybaseASADatabase) {
        return "SELECT SEQUENCE_NAME, " + "START_WITH AS START_VALUE, " + "MIN_VALUE, " + "MAX_VALUE, " + "INCREMENT_BY, " + "CYCLE AS WILL_CYCLE " + "FROM SYS.SYSSEQUENCE s " + "JOIN SYS.SYSUSER u ON s.OWNER = u.USER_ID " + "WHERE u.USER_NAME = '" + schema.getName() + "'";
    } else if (database.getClass().getName().contains("MaxDB")) {
        // have to check classname as this is currently an extension
        return "SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG AS WILL_CYCLE " + "FROM sequences WHERE SCHEMANAME = '" + schema.getName() + "'";
    } else {
        throw new UnexpectedLiquibaseException("Don't know how to query for sequences on " + database);
    }
}