[Qgis-developer] 1:many relation enhancements

[Even Rouault]

Le mercredi 22 octobre 2014 09:46:23, Matthias Kuhn a écrit :
> Hi Hugo,
> 
> On 22.10.2014 09:13, Hugo Mercier wrote:
> > Le 22/10/2014 08:39, Matthias Kuhn a écrit :
> >> Hi Régis
> >> 
> >> On 10/21/2014 09:44 PM, HAUBOURG wrote:
> >>> Hi Matthias,
> >>> Agregate functions could be provided by virtual table feature (qep is
> >>> coming I think), I suppose we should chose one common way for advanced
> >>> relationnal capabilities.
> >> 
> >> I agree that one common way is preferable, but actually this QEP itself
> >> is already duplicating functionality (of QgsExpression) and nobody was
> >> able yet to confirm that it is possible to optimize queries for
> >> execution on the database with sqlite virtual tables. Something that is
> >> a must from my point of view. I think it is a good initiative, but for
> >> the aforementioned reasons I am not yet completely convinced, that it's
> >> the one and only way.
> > 
> > Hi,
> > 
> > Since I'm the author of the mentioned QEP
> > (https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5), I have to
> > answer :)
> > 
> > What functionalities of expressions do you think it duplicates ?
> 
> Almost all (IN, LIKE, ILIKE, *, +, / just to name a few)
> 
> :)
> :
> > I don't see anything that prevents virtual layer queries from being
> > optimized server-side. It has to be taken into account during the
> > implementation of virtual layers, of course, but it is hard to exhibit
> > the precise way it will work now without having an almost complete
> > implementation.
> 
> I was referring to this discussion here.
> 
> https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5#issuecomment-5814
> 8788
> 
> Sorry I did not respond again, but I don't think a complete optimization
> is possible without having deep access to virtual table functionality in
> sqlite (or having to parse the same SQL on our end and replace parts of
> it which basically comes close to writing our own engine again).
> But let's discuss this on the QEP PR instead of inside this ml thread.

Just to give you my feeback based on my implementation of SQLite SQL dialect 
in OGR ( 
https://github.com/OSGeo/gdal/blob/trunk/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitevirtualogr.cpp 
), I also doubt that you could easily inform the server that a join should be 
done. AFAIR I could forward simple filtering to the server/backend (things like 
"column_A >= 5 AND column_A <= 10 AND column_B != 10") by implementing the 
xFilter virtual method, but Virtual Tables are not informed of joins happening 
(the sqlite virtual table API hardy exposes SQL at all). So that would indeed 
require analyzing the SQL on QGIS side.

AFAIR, I've just verified that JOIN like "t1.col1 = t2.col2" are seen by the 
virtual table implementation like successive filters "t1.col1 = val1", "t1.col1 
= val2", where val1, val2 are values from t2.col2.

And regarding xFilter, even filters with OR etc.. are hidden to the virtual 
table implementation and evaluated only by SQLite.

Even

> 
> > Anyway, it will try to complete my proposal with some more concrete
> > material about that when I find time.
> 
> I'll make sure I'll have a look at it when I find time after you found
> time :)
> 
> Best
> Matthias

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com