Note

The knowledge base schema (*Meta.xml, AttributeConfig, DBColumn) does not support database defaults; tables are always created without DEFAULT values. Database defaults are created only as a byproduct of migrations:

• AddPrimitiveMOAttributeProcessor creates a new required column with a type-dependent default (0, `false`, empty string, current date, ...) so that existing rows are assigned a value.
• AddMOReferenceProcessor creates the columns of a new reference with defaults (“dummyTypeValue,” null ID, NULL_REPLACEMENT).
• AddDatabaseColumnProcessor can set any default value using ` default-value `.

The defaults remain permanently associated with the column afterward, even though the knowledge base does not need them (new rows are always written with all columns). A database migrated in this way differs from a newly created one in this respect.

Support for defaults in SQL generation is also flawed in several dialects:

• H2 and DB2: For a non-mandatory column with a default, the result is ... DEFAULT 'x' DEFAULT NULL (two DEFAULT clauses, syntax error), because nullSpec() returns DEFAULT NULL. This affects addColumn(..) and column modifications, e.g., AddDatabaseColumnProcessor with mandatory="false" and default-value.
• PostgreSQL: When changing the data type, the default is appended to the TYPE clause(ALTER COLUMN c TYPE ... DEFAULT 'x'), which is invalid.
• MS SQL Server: A DEFAULT in ALTER COLUMN is not allowed (defaults are separate constraints there).
• MySQL: MODIFY / CHANGE discards an existing default value unless it is specified again; column modifications (AlterColumnProcessor, RecollateStringColumnsProcessor) therefore remove migrated default values, while they are preserved in other databases.

Suggestion

Database defaults are completely removed:

• A mandatory column is added during migration as in DBSchemaUtils: Create the column without setting it as mandatory, populate existing rows with the default value using ` UPDATE`, then make it a mandatory column using ` modifyColumnMandatory(..)`. The following processors need to be updated: AddPrimitiveMOAttributeProcessor and AddMOReferenceProcessor.
• AddDatabaseColumnProcessor: `default-value ` becomes the value used to populate existing rows; a database default is no longer created.
• The default value is removed from SQLAddColumn / SQLModifyColumn (getDefaultValue(), setDefaultValue(..)), from SQLFactory.addColumn(..) (and the modifyColumn..(..) methods, if still present there), from SQLCopy, and from the dialects (appendDefaultValue(..), the defaultValue parameter of the append.. methods in DBHelper and its subclasses).

Defaults already present in databases remain unaffected; they do not cause any issues.

Tests: Adding a required column (primitive and reference) to a populated table on all databases (TestAlterTable or tests of the migration processors), run with ONLY_DEFAULT_DB = false.

Solution

• DBHelper and the dialects (H2Helper, MSSQLHelper, OracleHelper, PostgreSQLHelper) no longer generate a DEFAULT clause. appendDefaultValue(..) and the variant of appendDBType(..) with a default value have been removed, as has the defaultValue parameter from appendChangeColumnName(..), appendChangeColumnType(..), and appendChangeMandatory(..).
• The default value has been removed from SQLAddColumn, SQLModifyColumn, SQLFactory.addColumn(..) (both variants), SQLCopy, and AbstractStatementBuilder.
• AddMOAttributeProcessor.createColumns(..) creates new columns as nullable, populates the existing rows in a single UPDATE, and then sets the column as mandatory. AddPrimitiveMOAttributeProcessor and AddMOReferenceProcessor utilize this; the columns of a reference are thus filled in a single table loop.
• AddDatabaseColumnProcessor: `default-value ` fills only the existing rows and is now called “Fill value” in the user interface; the configuration name remains the same.
• New Test: TestAddColumnProcessors (all databases): Adds a required attribute, a required reference, and a database column with a fill value to a populated table, and verifies that all rows are populated, the columns are NOT NULL, and no database default remains.
• Runtime (measured on PostgreSQL 15): Filling takes about 12 seconds per 1 million rows in the table; the table and indexes grow to about twice their size until the next VACUUM. Creating the table with a default value, on the other hand, was purely a metadata change.

Migration

• SQLFactory.addColumn(..) no longer has a `defaultValue ` parameter; SQLAddColumn.getDefaultValue() and setDefaultValue(..) are deprecated. Custom migration processors that create a mandatory column with a default value should create the column without setting it as mandatory, populate it using an `UPDATE` statement, and then set it as mandatory using `SQLFactory.modifyColumnMandatory(..)`.
• Custom SQL dialects (subclasses of DBHelper): For appendChangeColumnName(..), appendChangeColumnType(..), and appendChangeMandatory(..), the last parameter, Object defaultValue, has been removed; appendDefaultValue(..) and appendDBType(Appendable, DBType, String, long, int, boolean, boolean, Object) no longer exist (instead, use the variant without a default value).
• AddDatabaseColumnProcessor: default-value no longer creates a database default value but only populates existing rows. If you need a permanent database default value, set it using a custom SQL step.
• Migrations that add a required column to a large table take longer (see solution) and require approximately twice as much space for the table during the migration.