[CAY-1395] createSetAllowNullToDb() can lead to data corruption, returns incorrect SQL statement to createSql()

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.0RC2
• Fix Version/s: Undefined future, 3.0.3
• Component/s: Core Library
• Assignee: Unassigned
• Reporter: Øyvind Harboe
• Created: 24/Feb/10 08:05
• Updated: 29/Sep/14 19:52

Description

SQLServerManagerFactory.createSetAllowNullToDb() can cause data corruption.

The problem is that it assumes that the column passed in to the constructor contains the properties read from the database.

Consider a text field where the column passed to the constructor is shorter than what's currently used in the database.

In the use of a a merger context one could decide only to try to change the allow null property and leave the length of the text field alone. In this case changing the allow null property will truncate the length of the text field as a side effect.

Consider the MS SQL server syntax to set the NOT NULL (do not allow null) syntax:

ALTER TABLE consequence ALTER COLUMN cons_id int NOT NULL

Here the type of the field must preceed the NOT NULL and hence you have to know the type in the database to modify the allow null property while leaving the type alone...

SetNotNullToDb() does not suffer from this problem as it changes only the allow null property.

@Override
public List<String> createSql(DbAdapter adapter)

{ StringBuilder sqlBuffer = new StringBuilder(); QuotingStrategy context = adapter.getQuotingStrategy(getEntity() .getDataMap() .isQuotingSQLIdentifiers()); sqlBuffer.append("ALTER TABLE "); sqlBuffer.append(context.quoteFullyQualifiedName(getEntity())); sqlBuffer.append(" ALTER COLUMN "); sqlBuffer.append(context.quoteString(getColumn().getName())); sqlBuffer.append(" SET NOT NULL"); return Collections.singletonList(sqlBuffer.toString()); }

I've hacked up SQLMergerFactory locally and it appears to work fine in my particular case:

return new SetAllowNullToDb(entity, column) {

@Override
public List<String> createSql(DbAdapter adapter)

{ StringBuffer sqlBuffer = new StringBuffer(); QuotingStrategy context = adapter.getQuotingStrategy(getEntity() .getDataMap() .isQuotingSQLIdentifiers()); sqlBuffer.append("ALTER TABLE "); sqlBuffer.append(context.quoteFullyQualifiedName(getEntity())); sqlBuffer.append(" ALTER COLUMN "); /* Kludge!!!! how should this be handled really??? */ column.setMandatory(false); adapter.createTableAppendColumn(sqlBuffer, column); return Collections.singletonList(sqlBuffer.toString()); }