Map dt_date to datetime in SQLite3's DDL API

[zann1x]

As the datetime data type doesn't exist in SQLite3, the DDL method create_column_type() mapped dt_date to an integer type. This was possible because SQLite3 internally handles datetime values as text, real or integer. When using the soci::row API however, we want to be able to query datetime columns as std::tm instead of integers. To do this, create_column_type() must assign dt_date to a type that gets mapped back to dt_date in get_data_type_map() in sqlite3/statement.cpp. The most generic one in this case is datetime.

Fixes #969

[vadz]

Thanks, but just to confirm: is it really fine to create columns with any type at all? What about when using strict option?

[zann1x]

You can define STRICT on a per-table basis. If a table is defined as STRICT, the datatypes are restricted to INT, INTEGER, REAL, TEXT, BLOB and ANY (https://www.sqlite.org/stricttables.html). Without STRICT, SQLite is very liberal about the values given and the values stored:

For example, if a table column has a type of "INTEGER", then SQLite tries to convert anything inserted into that column into an integer. So an attempt to insert the string '123' results in an integer 123 being inserted. But if the content cannot be losslessly converted into an integer, for example if the input is 'xyz', then the original string is inserted instead.

Digging a little deeper into SOCI, I have to admit that I didn't see that create_column_type(), add_column() etc. are publicly usable on session_backend. Creating a DDL string that way and then adding STRICT manually would be possible. I suppose that one could even add STRICT when using ddl_type::set_tail(). My change would probably break that behavior :confused:

[zann1x]

I'm closing this PR because adding strict to the DDL string indeed results in an exception with message

sqlite3_statement_backend::prepare: unknown datatype for soci_test.ts: "datetime" while preparing "create table soci_test (ts datetime not null) strict"