<div dir="ltr">Here is the way I currently see it. <div><br></div><div>Currently there is no really easy way to do cross layer queries. Lets assume that the layers are not database based, which a lot of time they are not, or are mixed data sources, which I deal with a lot. Currently your only option (if you want to do query type stuff) is to upload everything to a database format and run a query there but even that experience is broken, or not an option. Using SQLIte virtual tables here IMO would gives us a nice quick high level query that we can run on any layer open in QGIS to get some results. Of course there needs to be some information warning the user that there are limitations and if you run it on a large data set while trying to do something complicated you are going to get crap performance, however for the simple case of something like: SELECT cola, buffer(geometry) FROM layer WHERE ID > 10, this would be fine and we could just return a new layer with geom and cola. </div>
<div><br></div><div>Using SQLite here means we can be layer agnostic at the higher level. This would also solve one of my longer standing issue of extracting just the columns you need, currently this is a really painful process. With a query model we just run query -> export like normal.</div>
<div><br></div><div>When it comes to QgsExpression translation to native query language, I don't see any direct problem with this, in fact Martin designed QgsExpression so we can just attach node visitors to the tree and do what we need. Each provider would just have their own expression tree visitor class and do what it needs with each node in the tree. The main problem with this can come with translation when something doesn't quite match up, or isn't supported, in which case you would just need to bail and run it in QGIS.</div>
<div><br></div><div>My other idea is to allow the data providers to run a native query on the connection/database and just return the result as a new layer. This would be a matter just passing a query string, running the query on the provider, returning a new layer with the data. The layers don't need to be opened in QGIS. Pretty much what DB Manager currently does but done at a provider level, so we have it for each provider.</div>
<div><br></div><div>In my mind there are two ways here for running queries:</div><div><br></div><div>1) SQLite over open layers in QGIS</div><div>2) Data provider query</div><div><br></div><div>The query dialog would have both options and the user can pick which one they want to do. If you select SQLite then you get the warning about performance, if you use the provider then you can select which provider and connection to use. This should just reuse the connections you already have defined in the browser.</div>
<div><br></div><div>This is the general idea (stolen from OpenJUMP):</div><div><br></div><div><img src="http://sourceforge.net/apps/mediawiki/jump-pilot/nfs/project/j/ju/jump-pilot/6/63/RunDataStoreQuery.png" width="421" height="186" style="margin-right: 0px;"> <br>
</div><div><br></div><div>Regards,</div><div>Nathan</div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Thu, May 29, 2014 at 6:12 PM, Matthias Kuhn <span dir="ltr"><<a href="mailto:matthias.kuhn@gmx.ch" target="_blank">matthias.kuhn@gmx.ch</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
Using the foreign key constraints to propose automatically created
relations to the user would be a nice feature for any database. One
problem here is, that the providers handle the database management
themselves and only provide QGIS the information that there is a
layer, but not from what database it comes. Maybe it would be
possible to extract this information with the current architecture
but for an optimal handling the provider architecture should be
adapted to provide such information in a standard way.<span class="HOEnZb"><font color="#888888"><br>
<br>
Matthias</font></span><div><div class="h5"><br>
<br>
<div>On 05/28/2014 10:54 PM, Bob and Deb
wrote:<br>
</div>
<blockquote type="cite">
<p dir="ltr">How about querying the sqlite_master table to find
the data type? And why not use the constraints found in this
query to set up the qgis relations?</p>
<p dir="ltr">-Bob (aka cgs_bob on freenode)</p>
<div class="gmail_quote">On May 27, 2014 6:04 AM, "Régis Haubourg"
<<a href="mailto:regis.haubourg@eau-adour-garonne.fr" target="_blank">regis.haubourg@eau-adour-garonne.fr</a>>
wrote:<br type="attribution">
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hugo Mercier wrote<br>
> Or<br>
>> calculated fields in view can't be explicitly cast,
so QGIS should have<br>
>> to<br>
>> guess data type based on a data scan (a major
unadressed issue of sqlite)<br>
><br>
> Hmmm I wasn't aware of this limitation in SQLITE views :(<br>
<br>
Yes, SQLITE does dynamic typing, so user or provider has to
scan values to<br>
guess the right type.<br>
Here is a sqlite topic on that [0]<br>
And here my initial post in qgis list [1]<br>
<br>
[0]<br>
<a href="http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-td56769.html#a56770" target="_blank">http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-td56769.html#a56770</a><br>
<br>
[1]<br>
<a href="http://osgeo-org.1560.x6.nabble.com/Spatialite-can-t-type-fields-of-a-view-td5058436.html" target="_blank">http://osgeo-org.1560.x6.nabble.com/Spatialite-can-t-type-fields-of-a-view-td5058436.html</a><br>
<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href="http://osgeo-org.1560.x6.nabble.com/have-aggregate-window-expressions-ever-been-discussed-tp5142215p5142714.html" target="_blank">http://osgeo-org.1560.x6.nabble.com/have-aggregate-window-expressions-ever-been-discussed-tp5142215p5142714.html</a><br>
Sent from the Quantum GIS - Developer mailing list archive at
Nabble.com.<br>
_______________________________________________<br>
Qgis-developer mailing list<br>
<a href="mailto:Qgis-developer@lists.osgeo.org" target="_blank">Qgis-developer@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/qgis-developer" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
</blockquote>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
Qgis-developer mailing list
<a href="mailto:Qgis-developer@lists.osgeo.org" target="_blank">Qgis-developer@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/mailman/listinfo/qgis-developer" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-developer</a></pre>
</blockquote>
<br>
</div></div></div>
<br>_______________________________________________<br>
Qgis-developer mailing list<br>
<a href="mailto:Qgis-developer@lists.osgeo.org">Qgis-developer@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/qgis-developer" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br></blockquote></div><br></div>