Hard to get the type of computed columns (of type DATETIME for example) of GeoPackage views

[rouault]

This issue is motivated by the issue raised in https://lists.osgeo.org/pipermail/qgis-developer/2024-July/066950.html , that I've analyzed in https://lists.osgeo.org/pipermail/qgis-developer/2024-July/066950.html . This isn't really a GDAL or QGIS issue, but more a general one for anyone using the SQLite3 C "reference" implementation. That is essentially every one, since I don't think there's other implementations.

The issue is when the view has columns that are computed values, and not just the selection of columns from a table. Adding "CAST(my_expr AS {type_name}) AS {field_name}" isn't fully bullet proof. Before SQLite 3.41, if type_name was INTEGER or REAL, then the reported type affinity by PRAGMA TABLE_INFO(view_name) was NUM. Since SQLite 3.42, INT and REAL are distinguished. So far so good. The main remaining issue is with DATETIME fields - for example if computing the min() or max() of a datetime field - as there is no SQLite3 DATETIME type affinity. The only solution for now would be to write a SQL parser for a "CREATE VIEW ..." statement, which is super tedious.

I've no magic solution to propose. One idea that came to mind would be to relax the schema extension to allow the mime_type column to contain any of the values of Table 1. GeoPackage Data Types.