[Qgis-developer] QEP/RFC sqlite virtual tables

[Matthias Kuhn]

Hi Hugo,

Sorry for my slow response, I was quite busy the last days.

I'll try to explain a bit more what I have in mind.

For example there is the new expression function "getFeature()" which is 
an ad-hoc replacement for a join. I have been told that the performance 
for rendering is not very good, I assume this is caused by a lot of 
requests performed in the background. It would be great to be able to 
join this on the server side.

I would be very happy to see more of this introduced. For example I 
could well imagine a new Expression (Syntax just made up, that can still 
be designed in a more appropriate way)

   avg( c.population ) { LEFT JOIN cities c ON c.country_code = 
$thistable.code }

This should then be locally evaluated (for shapefiles et al) but 
forwarded to the server for database services (postgres and the like).

Now, as we have different backends and their syntax does not always 
match (most do standard sql, but there are extensions, think of ST_* in 
postgis, SDO_* in oracle spatial etc, and there is no guarantee, that a 
dataprovider supports standard SQL) we need an abstraction layer and 
cannot just assume that every dataprovider will understand the same thing.

I am planning to implement this abstraction layer for ordinary 
expressions in the near future. And I would be very happy to see that 
there is a way to extend this with aggregate functions. This will all 
require a fallback level (if the server does not support a given 
functionality, for shapefiles, to be sure that the result is the very 
same regardless of server function implementation details...). We 
already have this for ordinary expressions as we can just use the 
QgsExpression implementation.

I can only repeat, that I will be more than happy to see this fallback 
level implemented by means of sqlite virtual tables. But in my humble 
opinion it would be very unfortunate if there would be no way to 
optimize this tomorrow or the day after tomorrow. That's why I am asking 
if sqlite developers are open to collaborate because if the will to 
collaborate there is missing we will end up having to reimplement this 
functionality.

I totally agree with your statement "A general cross-database SQL 
optimizer would need lots of work (in QGIS and/or SQLite) and may come 
later (if really needed).", but would append "and will require either a 
re-implementation in QGIS, ending up in duplicated functionality" 
(that's what Régis was worried about in the original mail) "or will need 
the possibility to get deeper access to sqlite's internal data 
structures or callbacks to optimize where required. This will require 
cooperation from SQLite developer's side or forking sqlite.".

Best regards,
Matthias

  
--------------------------------------

Please help taking QGIS to the next level of quality. Before November 15 !
http://blog.vitu.ch/10102014-1046/crowdfunding-initiative-automated-testing