Create table->Column->Setting default value bug

General Discussion
1

Hi all!

Bug description:
LB version - 2.0 RC2 (my own trunk build)
Database - Oracle

Create script

    CREATE TABLE AAAA (  COLUMN1 NUMBER(22,0) DEFAULT (0),  COLUMN2 NUMBER(22,0) DEFAULT 0 );

Table view

]
Name Type Nullable Default Value
COLUMN1 NUMBER(22,0) Yes (0)
COLUMN2 NUMBER(22,0) Yes 0

ChangeSet view

Exception


    java.sql.SQLException: ORA-01722: invalid number

Error executing SQL CREATE TABLE AAAA (COLUMN1 NUMBER(22,0) DEFAULT ‘(0)’ NOT NULL, COLUMN1 NUMBER(22,0) DEFAULT 0)

Code view [CreateTableGenerator.java (line 67)]

    buffer.append(TypeConverterFactory.getInstance().findTypeConverter(database).getDataType(defaultValue).convertObjectToString(defaultValue, database));

There are two equals ways to set up columns default value in oracle. [don’t know how it relates to other sql-based-families]

  1. DEFAULT (0)
  2. DEFAULT 0

In both cases it will work as insertion ‘0’ number-type value into cell.
But in [1] Liquibase recognizes ‘(0)’ as VARCHAR2-type value, and then it wrapped it by [ ’ ].

Is it LB bug? or it’s just my trouble?
There is workaround fix - using xslt to change all default values [ (value) ] in my changeLog and cut brackets. But it’s uncomfortably.

2

In OracleTypeConverter class I found this method:

       @Override    public Object convertDatabaseValueToObject(Object defaultValue, int dataType, int columnSize, int decimalDigits, Database database) throws ParseException {        if (defaultValue != null) {            if (defaultValue instanceof String) {                if (dataType == Types.DATE || dataType == Types.TIME || dataType == Types.TIMESTAMP) {                    if (((String) defaultValue).indexOf("YYYY-MM-DD HH") > 0) {                        defaultValue = ((String) defaultValue).replaceFirst("^to_date\\('","").replaceFirst("', 'YYYY-MM-DD HH24:MI:SS'\\)$","");                    } else if (((String) defaultValue).indexOf("YYYY-MM-DD") > 0) {                        defaultValue = ((String) defaultValue).replaceFirst("^to_date\\('","").replaceFirst("', 'YYYY-MM-DD'\\)$","");                    } else {                        defaultValue = ((String) defaultValue).replaceFirst("^to_date\\('","").replaceFirst("', 'HH24:MI:SS'\\)$","");                    }                }                defaultValue = ((String) defaultValue).replaceFirst("'\\s*$", "'"); //sometimes oracle adds an extra space after the trailing ' (see http://sourceforge.net/tracker/index.php?func=detail&aid=1824663&group_id=187970&atid=923443).            }        }        return super.convertDatabaseValueToObject(defaultValue, dataType, columnSize, decimalDigits, database);    }

Is it right to add one more “else if” statement?
Something like that:

    } else if (dataType == Types.INTEGER || dataType == Types.DECIMAL) {             defaultValue = ((String) defaultValue).replaceFirst("\\(", "").replaceFirst("\\)", ""); }

If it’s correct fix - I’ll commit it! :slight_smile:

Also there should be included other non-char SQL types in this statement. Am I right?

3

That looks like a good fix to commit.  You may want to check for other numberic types like Types.NUMBER, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, etc.

Nathan

4

Ok, I fixed it.

P.S.
Sorry, I changed code-style in OracleTypeConverter class. That’s why it may looks like full-changed commit.

5

Another one bug :slight_smile:
I’ll be post here other same-typed bugs…

There is a system property in Oracle table:
SYSTIMESTAMP
[description: http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions173.htm]

So, It may be used to setting up default value for date-typed column.
But LB again wrapped it like String value with ’ symbol (how it’s calling? [’] :))) )

    CREATE TABLE AAAC (   ID NUMBER(22,0) NOT NULL,   RECORD_TIME TIMESTAMP(6) DEFAULT 'SYSTIMESTAMP' NOT NULL );

I’ll fix it tomorrow :slight_smile: