CODEWITHSUNDEEP
grailsdatabase-migration
John Ormerod
John Ormerod

Reputation: 117

grails database-migration - can it generate for 'not null with default'?

What seems to me to be a common requirement for database changes, is the addition of a column that can't be null. One way round the problem of populating this column in some circumstances would be to define it as 'not null with default' in the DDL.

Grails doesn't appear to support 'NNWD' directly in constraints. I tested an idea that seems to work as an equivalent:

    String name = default
    ...
    name nullable:false

I wondered if dbm-gorm-diff changleog-n.xml would be able to detect this as being a null with a default. But it didn't. That's with version 1.2.2. I see that Liquibase supports this via its <addNotNullConstraint.

Are there any plans to introduce this support? Any suggestions as to how I might work round this, perhaps via a user-written script that makes use of dbm scripts.

Problem:

I tried using 1.3.2, but I get a MissingMethodException when running the script. The actual error line is:

groovy.lang.MissingMethodException: No signature of method: static grails.plugin.databasemigration.ScriptUtils.executeAndWrite() is applicable for argument types: (java.lang.String, java.lang.Boolean, DbmGormDiff$_run_closure1_closure2) values: [changelog-with-data.xml, false, DbmGormDiff$_run_closure1_closure2@2f673724]

Which I don't understand since the args seem to match the signature of the executeAndWrite() method in the plugin's code.

Regards, John

Upvotes: 1

Views: 2130

Answers (2)

Vishwajeet
Vishwajeet

Reputation: 321

If you're using 1.4.0 version of plugin it doesn't generate the changelog xmls for default values but you can edit the generated files to add the defaultValue & value attributes where it'll add the non-null columns without errors by updating the existing records with the value attribute.

Here's a small example for what it'd look like after modification:

    <changeSet author="anybody (generated)" id="1467324956xxx-xx">
    <addColumn tableName="table1">
    <column name="col1" type="varchar(10)" defaultValue="value1" value="value1">
    <constraints nullable="false"/>
    </column>
    </addColumn>
    </changeSet>

More info you can find on liquibase docs http://www.liquibase.org/documentation/column.html http://www.liquibase.org/documentation/changes/add_column.html

Upvotes: 0

John Ormerod
John Ormerod

Reputation: 117

I have done some investigation on this topic and realised that the solution is more complicated, and trying to solve by adding a column using 'not null with default' is not the way to go. As I am planning to use Liquibase and Ant to update the database in test, uat and live environments, I refer to using xml files below.

If the table doesn't have any data, then you can add a column that is 'not null'. This seems to me to be an unlikely situation for a live database.

When the table contains data a new column must be defined as null. There is 3-step process, which requires manual updates to the xml file generated by the plugin:

  • define the new domain attribute as 'nullable:true', and generate the diff xml file.

  • update the xml file to add a new changeset using the < SQL> tag:

    < sql>update [table] set [column]='dflt value' where [column] is null< /sql>

  • Now you can define the new column as not-null. Could use more raw sql, or look up Liquibase's < addNotNullConstraint>.

I don't think there is really a need for a default value for the column, since Grails will ensure that nulls don't get through from the browser. I say this since I haven't found a way to alter the definition of the added column so it has a default value.

John

Upvotes: 1

Related Questions