Examples with DatabaseFunction liquibase.statement.DatabaseFunction used on opensource projects

Example 16 with DatabaseFunction

use of liquibase.statement.DatabaseFunction in project liquibase by liquibase.

the class ChangeParameterMetaData method getExampleValue.

public Object getExampleValue(Database database) {
    if (exampleValues != null) {
        Object exampleValue = null;
        for (Map.Entry<String, Object> entry : exampleValues.entrySet()) {
            if (entry.getKey().equalsIgnoreCase("all")) {
                exampleValue = entry.getValue();
            } else if (DatabaseList.definitionMatches(entry.getKey(), database, false)) {
                return entry.getValue();
            }
        }
        if (exampleValue != null) {
            return exampleValue;
        }
    }
    Map standardExamples = new HashMap();
    standardExamples.put("tableName", "person");
    standardExamples.put("schemaName", "public");
    standardExamples.put("tableSchemaName", "public");
    standardExamples.put("catalogName", "cat");
    standardExamples.put("tableCatalogName", "cat");
    standardExamples.put("columnName", "id");
    standardExamples.put("columnNames", "id, name");
    standardExamples.put("indexName", "idx_address");
    standardExamples.put("columnDataType", "int");
    standardExamples.put("dataType", "int");
    standardExamples.put("sequenceName", "seq_id");
    standardExamples.put("viewName", "v_person");
    standardExamples.put("constraintName", "const_name");
    standardExamples.put("primaryKey", "pk_id");
    if (standardExamples.containsKey(parameterName)) {
        return standardExamples.get(parameterName);
    }
    for (String prefix : new String[] { "base", "referenced", "new", "old" }) {
        if (parameterName.startsWith(prefix)) {
            String mainName = StringUtils.lowerCaseFirst(parameterName.replaceFirst("^" + prefix, ""));
            if (standardExamples.containsKey(mainName)) {
                return standardExamples.get(mainName);
            }
        }
    }
    if (dataType.equals("string")) {
        return "A String";
    } else if (dataType.equals("integer")) {
        return 3;
    } else if (dataType.equals("boolean")) {
        return true;
    } else if (dataType.equals("bigInteger")) {
        return new BigInteger("371717");
    } else if (dataType.equals("list")) {
        //"TODO";
        return null;
    } else if (dataType.equals("sequenceNextValueFunction")) {
        return new SequenceNextValueFunction("seq_name");
    } else if (dataType.equals("databaseFunction")) {
        return new DatabaseFunction("now");
    } else if (dataType.equals("list of columnConfig")) {
        ArrayList<ColumnConfig> list = new ArrayList<ColumnConfig>();
        list.add(new ColumnConfig().setName("id").setType("int"));
        return list;
    } else if (dataType.equals("list of addColumnConfig")) {
        ArrayList<ColumnConfig> list = new ArrayList<ColumnConfig>();
        list.add(new AddColumnConfig().setName("id").setType("int"));
        return list;
    } else if (dataType.equals("list of loadDataColumnConfig")) {
        ArrayList<ColumnConfig> list = new ArrayList<ColumnConfig>();
        list.add(new LoadDataColumnConfig().setName("id").setType("int"));
        return list;
    } else {
        throw new UnexpectedLiquibaseException("Unknown dataType " + dataType + " for " + getParameterName());
    }
}

Example 17 with DatabaseFunction

use of liquibase.statement.DatabaseFunction in project liquibase by liquibase.

the class ColumnConfig method load.

@Override
public void load(ParsedNode parsedNode, ResourceAccessor resourceAccessor) throws ParsedNodeException {
    for (ParsedNode child : parsedNode.getChildren()) {
        if (!ObjectUtil.hasProperty(this, child.getName())) {
            throw new ParsedNodeException("Unexpected node: " + child.getName());
        }
    }
    name = parsedNode.getChildValue(null, "name", String.class);
    computed = parsedNode.getChildValue(null, "computed", Boolean.class);
    type = parsedNode.getChildValue(null, "type", String.class);
    encoding = parsedNode.getChildValue(null, "encoding", String.class);
    autoIncrement = parsedNode.getChildValue(null, "autoIncrement", Boolean.class);
    startWith = parsedNode.getChildValue(null, "startWith", BigInteger.class);
    incrementBy = parsedNode.getChildValue(null, "incrementBy", BigInteger.class);
    remarks = parsedNode.getChildValue(null, "remarks", String.class);
    descending = parsedNode.getChildValue(null, "descending", Boolean.class);
    value = parsedNode.getChildValue(null, "value", String.class);
    if (value == null) {
        value = StringUtils.trimToNull((String) parsedNode.getValue());
    }
    setValueNumeric(parsedNode.getChildValue(null, "valueNumeric", String.class));
    try {
        valueDate = parsedNode.getChildValue(null, "valueDate", Date.class);
    } catch (ParsedNodeException e) {
        valueComputed = new DatabaseFunction(parsedNode.getChildValue(null, "valueDate", String.class));
    }
    valueBoolean = parsedNode.getChildValue(null, "valueBoolean", Boolean.class);
    valueBlobFile = parsedNode.getChildValue(null, "valueBlobFile", String.class);
    valueClobFile = parsedNode.getChildValue(null, "valueClobFile", String.class);
    String valueComputedString = parsedNode.getChildValue(null, "valueComputed", String.class);
    if (valueComputedString != null) {
        valueComputed = new DatabaseFunction(valueComputedString);
    }
    String valueSequenceNextString = parsedNode.getChildValue(null, "valueSequenceNext", String.class);
    if (valueSequenceNextString != null) {
        valueSequenceNext = new SequenceNextValueFunction(valueSequenceNextString);
    }
    String valueSequenceCurrentString = parsedNode.getChildValue(null, "valueSequenceCurrent", String.class);
    if (valueSequenceCurrentString != null) {
        valueSequenceCurrent = new SequenceCurrentValueFunction(valueSequenceCurrentString);
    }
    defaultValueConstraintName = parsedNode.getChildValue(null, "defaultValueConstraintName", String.class);
    defaultValue = parsedNode.getChildValue(null, "defaultValue", String.class);
    setDefaultValueNumeric(parsedNode.getChildValue(null, "defaultValueNumeric", String.class));
    try {
        defaultValueDate = parsedNode.getChildValue(null, "defaultValueDate", Date.class);
    } catch (ParsedNodeException e) {
        defaultValueComputed = new DatabaseFunction(parsedNode.getChildValue(null, "defaultValueDate", String.class));
    }
    defaultValueBoolean = parsedNode.getChildValue(null, "defaultValueBoolean", Boolean.class);
    String defaultValueComputedString = parsedNode.getChildValue(null, "defaultValueComputed", String.class);
    if (defaultValueComputedString != null) {
        defaultValueComputed = new DatabaseFunction(defaultValueComputedString);
    }
    String defaultValueSequenceNextString = parsedNode.getChildValue(null, "defaultValueSequenceNext", String.class);
    if (defaultValueSequenceNextString != null) {
        defaultValueSequenceNext = new SequenceNextValueFunction(defaultValueSequenceNextString);
    }
    loadConstraints(parsedNode.getChild(null, "constraints"));
}

Example 18 with DatabaseFunction

use of liquibase.statement.DatabaseFunction in project liquibase by liquibase.

the class SqlUtil method parseValue.

public static Object parseValue(Database database, Object val, DataType type) {
    if (!(val instanceof String)) {
        return val;
    }
    int typeId = Integer.MIN_VALUE;
    if (type.getDataTypeId() != null) {
        typeId = type.getDataTypeId();
    }
    String typeName = type.getTypeName();
    LiquibaseDataType liquibaseDataType = DataTypeFactory.getInstance().from(type, database);
    String stringVal = (String) val;
    if (stringVal.isEmpty()) {
        if (liquibaseDataType instanceof CharType) {
            return "";
        } else {
            return null;
        }
    }
    if (database instanceof OracleDatabase && !stringVal.startsWith("'") && !stringVal.endsWith("'")) {
        //oracle returns functions without quotes
        Object maybeDate = null;
        if (liquibaseDataType instanceof DateType || typeId == Types.DATE) {
            if (stringVal.endsWith("'HH24:MI:SS')")) {
                maybeDate = DataTypeFactory.getInstance().fromDescription("time", database).sqlToObject(stringVal, database);
            } else {
                maybeDate = DataTypeFactory.getInstance().fromDescription("date", database).sqlToObject(stringVal, database);
            }
        } else if (liquibaseDataType instanceof DateTimeType || typeId == Types.TIMESTAMP) {
            maybeDate = DataTypeFactory.getInstance().fromDescription("datetime", database).sqlToObject(stringVal, database);
        } else if (!stringVal.matches("\\d+\\.?\\d*")) {
            //not just a number
            return new DatabaseFunction(stringVal);
        }
        if (maybeDate != null) {
            if (maybeDate instanceof java.util.Date) {
                return maybeDate;
            } else {
                return new DatabaseFunction(stringVal);
            }
        }
    }
    boolean strippedSingleQuotes = false;
    if (stringVal.startsWith("'") && stringVal.endsWith("'")) {
        stringVal = stringVal.substring(1, stringVal.length() - 1);
        strippedSingleQuotes = true;
    } else if (stringVal.startsWith("((") && stringVal.endsWith("))")) {
        stringVal = stringVal.substring(2, stringVal.length() - 2);
    } else if (stringVal.startsWith("('") && stringVal.endsWith("')")) {
        stringVal = stringVal.substring(2, stringVal.length() - 2);
    } else if (stringVal.startsWith("(") && stringVal.endsWith(")")) {
        return new DatabaseFunction(stringVal.substring(1, stringVal.length() - 1));
    }
    Scanner scanner = new Scanner(stringVal.trim());
    if (typeId == Types.ARRAY) {
        return new DatabaseFunction(stringVal);
    } else if ((liquibaseDataType instanceof BigIntType || typeId == Types.BIGINT)) {
        if (scanner.hasNextBigInteger()) {
            return scanner.nextBigInteger();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.BINARY) {
        return new DatabaseFunction(stringVal.trim());
    } else if (typeId == Types.BIT) {
        if (stringVal.startsWith("b'") || stringVal.startsWith("B'")) {
            //mysql returns boolean values as b'0' and b'1'
            stringVal = stringVal.replaceFirst("b'", "").replaceFirst("B'", "").replaceFirst("'$", "");
        }
        stringVal = stringVal.trim();
        if (database instanceof MySQLDatabase) {
            return stringVal.equals("1") || stringVal.equalsIgnoreCase("true");
        }
        Object value;
        if (scanner.hasNextBoolean()) {
            value = scanner.nextBoolean();
        } else {
            value = Integer.valueOf(stringVal);
        }
        return value;
    } else if (liquibaseDataType instanceof BlobType || typeId == Types.BLOB) {
        if (strippedSingleQuotes) {
            return stringVal;
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if ((liquibaseDataType instanceof BooleanType || typeId == Types.BOOLEAN)) {
        if (scanner.hasNextBoolean()) {
            return scanner.nextBoolean();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (liquibaseDataType instanceof CharType || typeId == Types.CHAR) {
        return stringVal;
    } else if (liquibaseDataType instanceof ClobType || typeId == Types.CLOB) {
        return stringVal;
    } else if (typeId == Types.DATALINK) {
        return new DatabaseFunction(stringVal);
    } else if (liquibaseDataType instanceof DateType || typeId == Types.DATE) {
        if (typeName.equalsIgnoreCase("year")) {
            return stringVal.trim();
        }
        return DataTypeFactory.getInstance().fromDescription("date", database).sqlToObject(stringVal, database);
    } else if ((liquibaseDataType instanceof DecimalType || typeId == Types.DECIMAL)) {
        if (scanner.hasNextBigDecimal()) {
            return scanner.nextBigDecimal();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.DISTINCT) {
        return new DatabaseFunction(stringVal);
    } else if ((liquibaseDataType instanceof DoubleType || typeId == Types.DOUBLE)) {
        if (scanner.hasNextDouble()) {
            return scanner.nextDouble();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if ((liquibaseDataType instanceof FloatType || typeId == Types.FLOAT)) {
        if (scanner.hasNextFloat()) {
            return scanner.nextFloat();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if ((liquibaseDataType instanceof IntType || typeId == Types.INTEGER)) {
        if (scanner.hasNextInt()) {
            return scanner.nextInt();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.JAVA_OBJECT) {
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.LONGNVARCHAR) {
        return stringVal;
    } else if (typeId == Types.LONGVARBINARY) {
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.LONGVARCHAR) {
        return stringVal;
    } else if (liquibaseDataType instanceof NCharType || typeId == Types.NCHAR || liquibaseDataType.getName().equalsIgnoreCase("NCLOB")) {
        return stringVal;
    } else if (typeId == Types.NCLOB) {
        return stringVal;
    } else if (typeId == Types.NULL) {
        return null;
    } else if ((liquibaseDataType instanceof NumberType || typeId == Types.NUMERIC)) {
        if (scanner.hasNextBigDecimal()) {
            if (database instanceof MSSQLDatabase && stringVal.endsWith(".0") || stringVal.endsWith(".00") || stringVal.endsWith(".000")) {
                //MSSQL can store the value with the decimal digits. return it directly to avoid unexpected differences
                return new DatabaseFunction(stringVal);
            }
            return scanner.nextBigDecimal();
        } else {
            if (stringVal.equals("")) {
                //can have numeric default '' on sql server
                return new DatabaseFunction("''");
            }
            return new DatabaseFunction(stringVal);
        }
    } else if (liquibaseDataType instanceof NVarcharType || typeId == Types.NVARCHAR) {
        return stringVal;
    } else if (typeId == Types.OTHER) {
        if (database instanceof DB2Database && typeName.equalsIgnoreCase("DECFLOAT")) {
            return new BigDecimal(stringVal);
        }
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.REAL) {
        return new BigDecimal(stringVal.trim());
    } else if (typeId == Types.REF) {
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.ROWID) {
        return new DatabaseFunction(stringVal);
    } else if ((liquibaseDataType instanceof SmallIntType || typeId == Types.SMALLINT)) {
        if (scanner.hasNextInt()) {
            return scanner.nextInt();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.SQLXML) {
        return new DatabaseFunction(stringVal);
    } else if (typeId == Types.STRUCT) {
        return new DatabaseFunction(stringVal);
    } else if (liquibaseDataType instanceof TimeType || typeId == Types.TIME) {
        return DataTypeFactory.getInstance().fromDescription("time", database).sqlToObject(stringVal, database);
    } else if (liquibaseDataType instanceof DateTimeType || liquibaseDataType instanceof TimestampType || typeId == Types.TIMESTAMP) {
        return DataTypeFactory.getInstance().fromDescription("datetime", database).sqlToObject(stringVal, database);
    } else if ((liquibaseDataType instanceof TinyIntType || typeId == Types.TINYINT)) {
        if (scanner.hasNextInt()) {
            return scanner.nextInt();
        } else {
            return new DatabaseFunction(stringVal);
        }
    } else if (typeId == Types.VARBINARY) {
        return new DatabaseFunction(stringVal);
    } else if (liquibaseDataType instanceof VarcharType || typeId == Types.VARCHAR) {
        return stringVal;
    } else if (database instanceof MySQLDatabase && typeName.toLowerCase().startsWith("enum")) {
        return stringVal;
    } else {
        if (stringVal.equals("")) {
            return stringVal;
        }
        LogFactory.getLogger().info("Unknown default value: value '" + stringVal + "' type " + typeName + " (" + type + "). Calling it a function so it's not additionally quoted");
        if (strippedSingleQuotes) {
            //put quotes back
            return new DatabaseFunction("'" + stringVal + "'");
        }
        return new DatabaseFunction(stringVal);
    }
}

Example 19 with DatabaseFunction

use of liquibase.statement.DatabaseFunction in project liquibase by liquibase.

the class ColumnSnapshotGenerator method readColumn.

protected Column readColumn(CachedRow columnMetadataResultSet, Relation table, Database database) throws SQLException, DatabaseException {
    String rawTableName = (String) columnMetadataResultSet.get("TABLE_NAME");
    String rawColumnName = (String) columnMetadataResultSet.get("COLUMN_NAME");
    String rawSchemaName = StringUtils.trimToNull((String) columnMetadataResultSet.get("TABLE_SCHEM"));
    String rawCatalogName = StringUtils.trimToNull((String) columnMetadataResultSet.get("TABLE_CAT"));
    String remarks = StringUtils.trimToNull((String) columnMetadataResultSet.get("REMARKS"));
    if (remarks != null) {
        //come back escaped sometimes
        remarks = remarks.replace("''", "'");
    }
    Integer position = columnMetadataResultSet.getInt("ORDINAL_POSITION");
    Column column = new Column();
    column.setName(StringUtils.trimToNull(rawColumnName));
    column.setRelation(table);
    column.setRemarks(remarks);
    column.setOrder(position);
    if (database instanceof OracleDatabase) {
        String nullable = columnMetadataResultSet.getString("NULLABLE");
        if (nullable.equals("Y")) {
            column.setNullable(true);
        } else {
            column.setNullable(false);
        }
    } else {
        int nullable = columnMetadataResultSet.getInt("NULLABLE");
        if (nullable == DatabaseMetaData.columnNoNulls) {
            column.setNullable(false);
        } else if (nullable == DatabaseMetaData.columnNullable) {
            column.setNullable(true);
        } else if (nullable == DatabaseMetaData.columnNullableUnknown) {
            LogFactory.getLogger().info("Unknown nullable state for column " + column.toString() + ". Assuming nullable");
            column.setNullable(true);
        }
    }
    if (database.supportsAutoIncrement()) {
        if (table instanceof Table) {
            if (database instanceof OracleDatabase) {
                String data_default = StringUtils.trimToEmpty((String) columnMetadataResultSet.get("DATA_DEFAULT")).toLowerCase();
                if (data_default.contains("iseq$$") && data_default.endsWith("nextval")) {
                    column.setAutoIncrementInformation(new Column.AutoIncrementInformation());
                }
            } else {
                if (columnMetadataResultSet.containsColumn("IS_AUTOINCREMENT")) {
                    String isAutoincrement = (String) columnMetadataResultSet.get("IS_AUTOINCREMENT");
                    isAutoincrement = StringUtils.trimToNull(isAutoincrement);
                    if (isAutoincrement == null) {
                        column.setAutoIncrementInformation(null);
                    } else if (isAutoincrement.equals("YES")) {
                        column.setAutoIncrementInformation(new Column.AutoIncrementInformation());
                    } else if (isAutoincrement.equals("NO")) {
                        column.setAutoIncrementInformation(null);
                    } else if (isAutoincrement.equals("")) {
                        LogFactory.getLogger().info("Unknown auto increment state for column " + column.toString() + ". Assuming not auto increment");
                        column.setAutoIncrementInformation(null);
                    } else {
                        throw new UnexpectedLiquibaseException("Unknown is_autoincrement value: '" + isAutoincrement + "'");
                    }
                } else {
                    //probably older version of java, need to select from the column to find out if it is auto-increment
                    String selectStatement;
                    if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400")) {
                        selectStatement = "select " + database.escapeColumnName(rawCatalogName, rawSchemaName, rawTableName, rawColumnName) + " from " + rawSchemaName + "." + rawTableName + " where 0=1";
                        LogFactory.getLogger().debug("rawCatalogName : <" + rawCatalogName + ">");
                        LogFactory.getLogger().debug("rawSchemaName : <" + rawSchemaName + ">");
                        LogFactory.getLogger().debug("rawTableName : <" + rawTableName + ">");
                        LogFactory.getLogger().debug("raw selectStatement : <" + selectStatement + ">");
                    } else {
                        selectStatement = "select " + database.escapeColumnName(rawCatalogName, rawSchemaName, rawTableName, rawColumnName) + " from " + database.escapeTableName(rawCatalogName, rawSchemaName, rawTableName) + " where 0=1";
                    }
                    LogFactory.getLogger().debug("Checking " + rawTableName + "." + rawCatalogName + " for auto-increment with SQL: '" + selectStatement + "'");
                    Connection underlyingConnection = ((JdbcConnection) database.getConnection()).getUnderlyingConnection();
                    Statement statement = null;
                    ResultSet columnSelectRS = null;
                    try {
                        statement = underlyingConnection.createStatement();
                        columnSelectRS = statement.executeQuery(selectStatement);
                        if (columnSelectRS.getMetaData().isAutoIncrement(1)) {
                            column.setAutoIncrementInformation(new Column.AutoIncrementInformation());
                        } else {
                            column.setAutoIncrementInformation(null);
                        }
                    } finally {
                        try {
                            if (statement != null) {
                                statement.close();
                            }
                        } catch (SQLException ignore) {
                        }
                        if (columnSelectRS != null) {
                            columnSelectRS.close();
                        }
                    }
                }
            }
        }
    }
    DataType type = readDataType(columnMetadataResultSet, column, database);
    column.setType(type);
    Object defaultValue = readDefaultValue(columnMetadataResultSet, column, database);
    if (defaultValue != null && defaultValue instanceof DatabaseFunction && ((DatabaseFunction) defaultValue).getValue().matches("\\w+")) {
        defaultValue = new DatabaseFunction(((DatabaseFunction) defaultValue).getValue().toUpperCase());
    }
    column.setDefaultValue(defaultValue);
    column.setDefaultValueConstraintName(columnMetadataResultSet.getString("COLUMN_DEF_NAME"));
    return column;
}

Example 20 with DatabaseFunction

use of liquibase.statement.DatabaseFunction in project liquibase by liquibase.

the class MssqlIntegrationTest method defaultValuesTests.

@Test
public void defaultValuesTests() throws Exception {
    if (this.getDatabase() == null) {
        return;
    }
    Liquibase liquibase = createLiquibase("changelogs/mssql/issues/default.values.xml");
    liquibase.update((String) null);
    DatabaseSnapshot snapshot = SnapshotGeneratorFactory.getInstance().createSnapshot(CatalogAndSchema.DEFAULT, this.getDatabase(), new SnapshotControl(getDatabase()));
    for (Table table : snapshot.get(Table.class)) {
        for (Column column : table.getColumns()) {
            if (column.getName().toLowerCase().endsWith("_default")) {
                Object defaultValue = column.getDefaultValue();
                assertNotNull("Null default value for " + table.getName() + "." + column.getName(), defaultValue);
                if (column.getName().toLowerCase().contains("date") || column.getName().toLowerCase().contains("time")) {
                    if (defaultValue instanceof DatabaseFunction) {
                        ((DatabaseFunction) defaultValue).getValue().contains("type datetimeoffset");
                    } else {
                        assertTrue("Unexpected default type " + defaultValue.getClass().getName() + " for " + table.getName() + "." + column.getName(), defaultValue instanceof Date);
                        Calendar calendar = Calendar.getInstance();
                        calendar.setTime(((Date) defaultValue));
                        assertEquals(1, calendar.get(Calendar.DAY_OF_MONTH));
                        assertEquals(1, calendar.get(Calendar.MONTH));
                        assertEquals(2000, calendar.get(Calendar.YEAR));
                    }
                } else if (column.getName().toLowerCase().contains("char_")) {
                    assertTrue("Unexpected default type " + defaultValue.getClass().getName() + " for " + table.getName() + "." + column.getName(), defaultValue instanceof String);
                } else if (column.getName().toLowerCase().contains("binary_")) {
                    assertTrue("Unexpected default type " + defaultValue.getClass().getName() + " for " + table.getName() + "." + column.getName(), defaultValue instanceof DatabaseFunction);
                } else {
                    assertTrue("Unexpected default type " + defaultValue.getClass().getName() + " for " + table.getName() + "." + column.getName(), defaultValue instanceof Number);
                    assertEquals(1, ((Number) defaultValue).intValue());
                }
            }
        }
    }
}