[Qgis-developer] QEP/RFC sqlite virtual tables

[Hugo Mercier]

Le 28/10/2014 13:21, Matthias Kuhn a écrit :
> Hi Hugo
> 

>> The optimization plan could be: don't try to optimize in the general
>> case (premature optimization ...), only optimize specific
>> well-identified cases.
>> For now the only simple case I can see is when a join is done on tables
>> from the same database (and the user don't know or can't do a join on
>> the remote database
> ... or a developer wants to develop a provider-independent plugin/core
> functionality that has a good cross-table performance.

Do you have something in mind ?

>> ), then yes we would have to know the syntax of the
>> original query and translate it into the desired SQL dialect.
>> And yes it would be better to have it from SQLite. It would require
>> either to work with the developers, to patch it or to somehow include
>> the parsing part of SQLite (we already ship sqlite / spatialite with
>> QGIS right ?). But I really don't see it as a blocker.
> 
> I don't think we do ship it but I may be wrong?
> Please don't get me wrong, I didn't say it's a blocker. The original
> discussion started from Régis statement "there's a QEP taking care of
> relational queries, is there a need to duplicate functionality?" and my
> response "I am not sure if sqlite virtual tables will be able to satisfy
> all our needs". I was just wondering about the limitations of this
> approach and I am still very open to hear about any experiences you had
> with sqlite developers (I have none) because that is what counts when it
> comes to working with upstream as you proposed.

Indeed, I don't know well the sqlite dev community.
I will try to know a bit more about exposure of internal SQLite data
structures.
In the worst case we could decide to fork or include parts of the code
the license is very permissive.

Btw, there is a spatialite / sqlite source distribution in
src/spatialite (probably only used when WITH_INTERNAL_SPATIALITE is enabled)

> 
>>
>> I really think using SQLite as our database engine has a good potential.
>> It could extend the abilities and expressivity of QGIS. And it could
>> also allow to use *less* code in QGIS (seeing every provider as a
>> virtual layer). And it could also paves the way for a native QGIS file
>> format (this is another discussion, but somehow related).
>>
>> But whatever the implementation is, trying to be (automatically) as
>> performant as a well-designed query on a well-designed db is a waste of
>> energy.
> Yes, we cannot do that. But we can try to find a way that allows
> developers to develop and users to create projects independent of
> providers while still providing good performance on a capable backend.
> 
>>
>> On a more pragmatic side, people are already interested and might pay
>> for db-oriented functionalities in QGIS (the very first need is to be
>> able to filter joined tables). It does not have to be the only incentive
>> for design choices, but this is a good opportunity. Then a decision has
>> to be taken.
>>
>> So, if it is hard for you and me to agree :), are there other opinions ?
>> Other arguments for one or the other side ?
> 
> I don't want to disagree, I just wanted to raise questions / to
> understand the limitations. I see the demand for this functionality and
> I see the potential that sqlite virtual tables have to offer. I just
> wonder what the performance will be like in a scenario where there's a
> network (with latency for every request) involved. And what it would
> require to overcome this issue (if there is any).

Ok, but this performance issue is not related to the backend choice
(SQLite or our own), right ?