SQLite SchemaValidation in 5.3.x

[dominikhasiwar]

Today we updated one of our solutions to the current nhibernate version 5.3.2. During the startup phase we perform a schema validation of the sqlite database file. After the upgrade from 5.2.6 we receive validation errors for all columns of type bool and datetime:

• Found: datetime, Expected TEXT
• Found: bool, Expected INTEGER

The application works without any noticable errors, if we skip the schema validation.

If we change the db-configuration from sqilte to sql server, the schema validation does not report any errors.

[fredericDelaporte]

This is likely to be a regression caused by #2346, which would have forgotten to check/fix the SQLite schema specific classes.

[maca88]

I think we should only add it as a possible breaking change and users should do one of the following:

• Recreate the database
• Update column types which were modified by Fix SQLite SQL types registrations #2346
• Disable schema validation for SQLite database

[fredericDelaporte]

To give some background to dhasi, #2346 has fixed NHibernate SQLite typing which was using invalid types names, like bool or datetime. Those invalid types names were not always causing errors because SQLite accepts literally everything for a type name, and do some loose inference to trans-code the provided type name to one of the very few types it actually supports. See https://www.sqlite.org/datatype3.html.

As a result, using invalid type names was causing SQLite to actually use unexpected actual types, which would cause some operations to fail. By example the datetime type would be "translated" by SQLite rules to the numeric type. If the value received cannot be understood as a numeric, then SQLite handles it as a text or blob in most situations, like storing a value in a "datetime" column. Yes it does just that, no error raised.

But when a NHibernate query attempts to perform a cast, then SQLite is less lax. A cast to this invalid datetime type will be interpreted as a cast to a numeric, and then SQLite will raise an error on a date literal not convertible to a number.

#2346 fixed this.

About the schema validation errors, it appears there is nothing to change on NHibernate side as maca88 has written. The trouble is, SQLite has still stored in your database these invalid types name from previous NHibernate versions.

So now the schema validation tool can only report the columns using those invalid type names as well, invalid. They are actually not using a valid type name. And to avoid troubles, better use one of the two first recommendations of maca88.
His last recommendation, disable schema validation for SQLite database, will dodge the trouble as you have already stated, but you may still have bugs due to this invalid typing.

(We still need to add a possible breaking change in our release notes about this. I will do it in next 5.3.x release PR.)

[dominikhasiwar]

Thank you @fredericDelaporte and @maca88 for the detailed explanation! We follow the recommendation and update the column types to the supported ones. Unfortunately, SQLite does not support changing column types via ALTER TABLE but we already started to write a little migration program for that.