SQLite incorrect DDL handling

[Sildra]

Mapping all of the types to integer will result in type narrowing while retrieving all of the data. Two solutions here: either integer is mapped to int64 or we distinguish types in the create_column.

In the second case, integer primary key also needs to be handled separately.

Originally posted by @Sildra in #954 (comment)

This issue also contradicts the documentation :
https://github.com/SOCI/soci/blob/master/docs/backends/sqlite3.md#dynamic-binding

[Sildra]

Proposal for fixing the DDL creation of SQLite : Sildra@087f382

The following commit has a perfect matching between DDL creation and SQLite.

[vadz]

I admit I don't understand what does the linked commit change: according to SQLite docs all these types map to INTEGER affinity, so why does it change anything?

[Sildra]

Because SOCI is performing affinity check based on the description of the column and for SQLite it is based on the name of the type. In every test, columns are not created using the DDL sql.create_table("TEST").column("VAL", soci::dt_long_long) but using a raw query. The equivalent query is "CREATE TABLE TEST (VAL INTEGER)". The INTEGER part is wrong, as my expected type is long long (or "bigint" as defined in

soci/src/backends/sqlite3/statement.cpp

Lines 412 to 465 in 2e4b44b

	static sqlite3_data_type_map get_data_type_map()
	{
	sqlite3_data_type_map m;
	// Spaces are removed from decltype before looking up in this map, so we don't use them here as well
	// dt_blob
	m["blob"] = dt_blob;
	// dt_date
	m["date"] = dt_date;
	m["time"] = dt_date;
	m["datetime"] = dt_date;
	m["timestamp"] = dt_date;
	// dt_double
	m["decimal"] = dt_double;
	m["double"] = dt_double;
	m["doubleprecision"] = dt_double;
	m["float"] = dt_double;
	m["number"] = dt_double;
	m["numeric"] = dt_double;
	m["real"] = dt_double;
	// dt_integer
	m["boolean"] = dt_integer;
	m["int"] = dt_integer;
	m["integer"] = dt_integer;
	m["int2"] = dt_integer;
	m["mediumint"] = dt_integer;
	m["smallint"] = dt_integer;
	m["tinyint"] = dt_integer;
	// dt_long_long
	m["bigint"] = dt_long_long;
	m["int8"] = dt_long_long;
	// dt_string
	m["char"] = dt_string;
	m["character"] = dt_string;
	m["clob"] = dt_string;
	m["nativecharacter"] = dt_string;
	m["nchar"] = dt_string;
	m["nvarchar"] = dt_string;
	m["text"] = dt_string;
	m["varchar"] = dt_string;
	m["varyingcharacter"] = dt_string;
	// dt_unsigned_long_long
	m["unsignedbigint"] = dt_unsigned_long_long;
	return m;
	}

).

Then when you get the data with the get, as a developer, I am expected to do v.get<long long>("VAL") but the following throws a std::bad_cast().

The current correct way to retrieve the data is v.get<int>("VAL") but it only supports 32bit values.

With the change, it is possible to do v.get<long long>("VAL") but it will break v.get<int>("VAL"). So for both to work it will require Sildra@10d2f77.