Column data type validation not quite exact

[abrunetta]

With a PostgreSql database, when I run a schema validation the following happens:

• Property mapped as timestamp against a timestamptz column => Schema Validation Failed (as expected)
• Property mapped as timestamptz against a timestamp column => Schema Validation OK (not as expected)

After checking the repository code, I found where the error might be: src/NHibernate/Mapping/Table.cs, method ValidateColumns:

bool typesMatch = 
    column
        .GetSqlType(dialect, mapping)
        .StartsWith(columnInfo.TypeName, StringComparison.OrdinalIgnoreCase);

Is it correct to check if both types match (mapping and column) with .StartsWith(...)? Wouldn't be better to use .Equals(...) here?

[fredericDelaporte]

There is a mismatch between the type obtained from the mapping and the one obtained from the database. The one obtained from the database does not embed any length, precision or scale specifier, while the type obtained from the mapping may have some. By example, on a string column, the mapping SQL type could be varchar(255), while the database would yield just varchar.

Using StartsWith allows to match those cases.

Maybe some kind of parsing could allow to extract the type name without length/precision/scale specifiers. But here we are in a code path which does not handle database specificities. So if some databases require another logic than, for example, truncating at first parenthesis, it may be troublesome.

[abrunetta]

Frederic, thanks for your reply.

This reminded me of another strange behavior in the schema validation process I came across in the past. Sometimes strings/varchar lengths were not validated correctly; for example, if you set up a column mapping as a "varchar" with a length of 100, and in the database the column was a varchar(50), Hibernate would validate it successfully when it shouldn't. Now, knowing how it works, it makes more sense.

I see that columnInfo, the information obtained from the database, may hold length/precision data (IColumnMetadata.ColumnSize, IColumnMetadata.NumericalPrecision). I know that we can't guarantee that such information is going to be always available (it depends of the specific Dialect) but if we have it, it may be safe to check the lengths. For example:

bool isColumnPrecisionSet = columnInfo.ColumnSize > 0 || columnInfo.NumericalPrecision > 0;
if (isColumnPrecisionSet)
{
    //we might be able to check with Equals here
}
else
{
    //check with StartsWith as it is currently done
}

[fredericDelaporte]

In my opinion, it should be handled separately. The bug should be fixed even if no length/scale/precision information is available. Validating length/scale/precision when available should be done as an improvement, with a dedicated PR.