<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jan 9, 2020 at 12:36 PM Sandro Santilli <<a href="mailto:strk@kbt.io">strk@kbt.io</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi all,<br>
some of you might know already I've been working on improving the<br>
DBManager SQL Window so so not rely on "psycopg2" when interacting<br>
with PostgreSQL backend. This work is in progress in this PR:<br>
<br>
<a href="https://github.com/qgis/QGIS/pull/33225" rel="noreferrer" target="_blank">https://github.com/qgis/QGIS/pull/33225</a><br>
<br>
The advice, from Alessandro, was to use the new core API for this:<br>
<br>
<a href="https://qgis.org/api/classQgsAbstractDatabaseProviderConnection.html" rel="noreferrer" target="_blank">https://qgis.org/api/classQgsAbstractDatabaseProviderConnection.html</a><br>
<br>
Using that API does give a more stable interaction (automatic<br>
reconnect on connection loss) but has a lack of features currently<br>
used by SQLWindow, which are:<br>
<br>
1. Extracting names of fields returned by a query<br>
2. Chunk retrival of results (useful for large result sets)<br>
<br>
Alessandro proposed to use QgsVectorLayer to solve the first issue:<br>
<br>
<a href="https://qgis.org/api/classQgsVectorLayer.html" rel="noreferrer" target="_blank">https://qgis.org/api/classQgsVectorLayer.html</a><br>
<br>
but I did not find a way to create a QgsDataSourceURI for a generic query<br>
(when a Geometry field might not even exist).<br>
<br>
Can anyone help with doing this ?<br>
Or, have another suggestion as to how to solve both issues ?<br>
<br>
Thanks in advance.<br>
<br>
--strk; <br>
<br>
() Free GIS & Flash consultant/developer<br>
/\ <a href="https://strk.kbt.io/services.html" rel="noreferrer" target="_blank">https://strk.kbt.io/services.html</a><br>
_______________________________________________<br>
QGIS-Developer mailing list<br>
<a href="mailto:QGIS-Developer@lists.osgeo.org" target="_blank">QGIS-Developer@lists.osgeo.org</a><br>
List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-developer" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-developer" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a></blockquote></div><div></div><div><br>
<br>Hi Sandro,
<br>
<br>This works for me:
<br>
<br>
<br>vl = QgsVectorLayer( "dbname=qgis_tests host=localhost port=5432
key='__rid__' user=ale table=\"(SELECT row_number() OVER () AS __rid__,
* FROM (SELECT * FROM raster_columns) AS foo)\" schema=public", 'layer',
'postgres')
<br>
<br>for fld in vl.fields():
<br> print(fld)
<br>
<br>
<br>this is basically how DB manager creates "query" layers, note that the
above example does not have any geometry column.
<br>
<br>Also, the vector API uses an iterator, so chunk retrieval is definitely
possible.
<br>
<br>
<br>Btw, I would be in favor of adding missing methods to the abstract API,
as long as they are not provider-specifics and they can be abstracted
from the DB backend, a capability flag can be added to know if a
provider supports that feature or if it doesn't.
<br>
<br>In particular, me an Nyall have been chatting about adding a method to
factory a QgsVectorLayer from a generic SQL query, the issues I see is
that we need a PK (at least for PG) and how to decide the geometry
column if there are more than one.
<br>
<br>Having this method would allow us to replace all the Python code that
generates query layers from the provider-specific part of DB manager
(the connectors).
<br>
<br>In any event, I was planning to apply for another grant in order to
continue the development of the abstract API, this is what I believe
must be done:
<br>
<br>There is still a large amount of work to be done in order to complete
all the desired refactoring and to remove all the Python and C++ code
that will be ultimately be made redundant.
<br>
<br>In particular, future work should be undertaken to:
<br>- port all remaining data providers to the new API
<br>refactor and eliminate the remaining DB-manager connectors to make use
of the abstract API
<br>- eliminate duplicate and untested code inside the Processing framework
for working with Postgres databases and port the code to the new,
stable, well-tested API
<br>- refactor and eliminate the remaining QGIS browser data items to make
use of the abstract API
<br>
<br>Hope this helps. <br></div><div><br></div><div><br></div>-- <br><div dir="ltr" class="gmail_signature">Alessandro Pasotti<br>w3: <a href="http://www.itopen.it" target="_blank">www.itopen.it</a></div></div>