[Qgis-developer] have aggregate/window expressions ever been discussed?

[Hugo Mercier]

Le 27/05/2014 15:03, Régis Haubourg a écrit :
> Hugo Mercier wrote
>> Or
>>> calculated fields in view can't be explicitly cast, so QGIS should have
>>> to
>>> guess data type based on a data scan (a major unadressed issue of sqlite)
>>
>> Hmmm I wasn't aware of this limitation in SQLITE views :(
> 
> Yes, SQLITE does dynamic typing, so user or provider has to scan values to
> guess the right type. 
> Here is a sqlite topic on that [0]
> And here my initial post in qgis list [1]
> 
> [0]
> http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-td56769.html#a56770
> 
> [1]
> http://osgeo-org.1560.x6.nabble.com/Spatialite-can-t-type-fields-of-a-view-td5058436.html
> 

Ok. Strange that type "afinity" is available for tables but not for views.

Anyway, the point here is to use SQLite and its virtual table feature as
a way to do SQL queries on any QGIS layer source.
In that particular case, original types of columns can be accessed
directly by the function used to expose QGIS layers as SQLite tables.

And if we need views on top of that, we can find some proprietary
representation, either on one side or the other (QGIS or a particular
table in SQLite).

Offering SQL machinery to any QGIS data source can be done by using
SQLite internally. And if we need to add some special constraints on the
SQLite schema for that, then it's not perfect, but it is not a big deal.
We would then have QGIS-enabled SQLite files (as Spatialite geometry
tables must have a correct row in geometry_columns and so on)

In the other hand, being able to use any features of any SQLite file as
a valid QGIS data source is a more difficult problem, I think.