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

[Hugo Mercier]

Le 23/05/2014 22:44, Régis Haubourg a écrit :
> Hi, 
> well, this is something I miss, coming from Mapinfo world. 
> 
> Some tests are being made currently on French ministry of ecology, and by
> Nathan also, to head to some kind of a layer abstraction, based on ogr
> virtual layer and ogr sqlite engine. 
> 
> Once done, that would open a large field of new features and dedicated
> gui's:
>  - SQL assistant (mapinfo like / qspatialite like) on any loaded data source
> and without any explicit import step. This includes aggregate functions
>  - virtual attribute or spatial columns based on functions
>  - ETL feature for changing data types and structure
>  
> FME is providing such kind of SQLexecutor, with some limits currently, and
> that is extremly powerfull in processing tools, allowing to take full
> advantage of existing algorithm for geospatial computing, and SQL-spatial
> SQL for attribute queries AND processing. 
> 
> If we find a way to be sure that any datasource can be correctly used
> without caveats hard to understand for the end user, I think we will have
> the best GIS tool ever done for non 3D uses. Still I see some limits with
> SQLITE. For instance, many SQL clauses are partially supported. 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 :(

> 
> I wish I had more time now to investigate by myself. 
> 
> Nathan , Hugo , could you tell us more on that topic? is ogr sqlite engine
> the right target or does is have to much limitations? 

I've added some lines on the wiki about this :
http://hub.qgis.org/wiki/quantum-gis/virtual_layers

The challenge is to integrate it cleaning more or less transparently for
the end-user and without loosing performances.
If we want end-users to only manipulate GUI dialogs and QgsExpressions
at the maximum and let more advanced users tweak the corresponding SQL
clauses, then we probably have to convert from QgsExpressions to SQL ...

Is SQLITE's virtual tables the right target ? Well ... I am not aware of
any other way to embed an SQL parsing/planner/executor. We do not want
to reimplement our own database engine.
So, in my opinion, we would have to deal with SQLite/Spatialite
limitations. If users want more, they have to install PostgreSQL/PostGIS
and convert their data.

And about Giovanni's first concern, SQLITE does not support WINDOW
functions. There might be some ways to mimick by accessing ROWID though