addNotNullConstraint without quotes

General Discussion
1

Have you seen the Merge Columns refactoring?  Maybe that would help?

http://www.liquibase.org/manual/merge_columns

My apologies if you tried this.

Best,
Laird

2

I haven’t found any easy solution with LiquiBase version 2.0.1 for the next - and I think quite common - scenario. (Sure the <sql> tag could be used.)


A new not null column needs to be added to an existing table SCENARIO_FILE(ID, SCENARIO_TYPE) and the new value should be the concatenated value of the existing values (CONCAT). This changeset is made for an Oracle database.


A) If the next changeset is used

  1.  <changeSet author=“ferengra” id=“1103081611-1” dbms=“oracle”>

     <addColumn tableName=“SCENARIO_FILE”>

      <column name=“SCENARIO_NAME” type=“VARCHAR2(500)” />

     </addColumn>

     <addNotNullConstraint tableName=“SCENARIO_FILE” columnName=“SCENARIO_NAME” defaultNullValue=“CONCAT(SCENARIO_TYPE, ID)” />

    </changeSet>

, the result after updateSQL is:

  1. ALTER TABLE SCENARIO_FILE ADD SCENARIO_NAME VARCHAR2(500);
  2. UPDATE SCENARIO_FILE SET SCENARIO_NAME = 'CONCAT(SCENARIO_TYPE, ID)' WHERE SCENARIO_NAME IS NULL;

Well, the SQL concat function is in quotes, so it’ll never be executed, but used as text.


B) With

  1. <changeSet author="ferengra" id="1103081611-1" dbms="oracle">
  2.  <addColumn tableName="SCENARIO_FILE">
  3.   <column name="SCENARIO_NAME" type="VARCHAR2(500)" />
  4.  </addColumn>
  5.  <update tableName="SCENARIO_FILE">
  6.   <column name="SCENARIO_NAME" value="CONCAT(SCENARIO_TYPE, ID)"/>
  7.  </update>
  8.  <addNotNullConstraint tableName="SCENARIO_FILE" columnName="SCENARIO_NAME" defaultNullValue="1" />
  9. </changeSet>

, the result SQL is:

  1. ALTER TABLE SCENARIO_FILE ADD SCENARIO_NAME VARCHAR2(500);
  2. UPDATE SCENARIO_FILE SET SCENARIO_NAME = 'CONCAT(SCENARIO_TYPE, ID)';
  3. UPDATE SCENARIO_FILE SET SCENARIO_NAME = '1' WHERE SCENARIO_NAME IS NULL;

Again the same issue.


C) With defaultValue, defaultValueComputed at

  1. <column name="SCENARIO_NAME" defaultValue="CONCAT(SCENARIO_TYPE, ID)"/>

, the generated value is NULL.

  1. UPDATE SCENARIO_FILE SET SCENARIO_NAME = NULL;

D) However, if the valueNumeric is used with an extra space at the end!

  1. <column name="SCENARIO_NAME" valueNumeric="CONCAT(SCENARIO_TYPE, ID) "/>

, the update SQL is OK. Of course, the valueNumeric is not intended for this kind of purpose, but its result is what is expected in adding a new column cases.

  1. UPDATE SCENARIO_FILE SET SCENARIO_NAME = CONCAT(SCENARIO_TYPE, ID); 



Can someone help me out why the <addNotNullConstraint> puts the values in quotes, why one of the column attributes can not be used for inserting an SQL call and why the extra space at the valueNumeric is necessary?

Thanks.

3

The mergeColumns updates the column properly but it drops the columns afterwards :frowning:

  1. ALTER TABLE SCENARIO_FILE ADD SCENARIO_NAME VARCHAR2(500);
  2. ALTER TABLE SCENARIO_FILE DROP COLUMN ID;

Thanks for the idea anyway.