[Qgis-developer] 1:many relation enhancements

[Hugo Mercier]

Le 22/10/2014 10:12, Even Rouault a écrit :
> 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)

Well, QgsExpression has been designed to mimick the SQL WHERE clause, so
yes I agree.
My point is that if you want to extend expression to support joins,
aggregates, window functions, etc. you end up recreating SQL, but
probably a less powerful one.

The difference I can see between expressions and SQL is that functions
used in expressions may have no direct equivalent SQLite-side. But it
can be resolved by using user-defined functions in SQLite (that would
just call code from QgsExpression for instance), so that the SQL dialect
would be seen as an extension of the current QgsExpression, not
something different.

>>
>> :)
>> :
>>> 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.
> 

Thanks Even for your feedback.

The idea under virtual layers is that SQLite may be exposed to advanced
users if they want to use a complex query on some data sources.

It is very interesting to use SQLite to replace existing features from a
code point of view because it simplifies implementation.
But exposing a SQL query for already existing features such as joins may
not be desirable for standard users.

So for joins, since the information comes from QGIS ("I want to join
tables t1 and t2 on this column predicate"), the SQL query can be
deduced and some other metadata is also known, like the fact that t1 and
t2 may be from the very same database.
In that case the virtual layer may be created with some metadata saying
that this is in fact a join that must be optimized. And then the virtual
layer provider can ask the underlying provider (say postgis) if it can
process the whole join query.