Skip to content

Commit

Permalink
Port Execute SQL to new API, add test
Browse files Browse the repository at this point in the history
  • Loading branch information
@nyalldawson
nyalldawson committed Aug 20, 2017
1 parent 99fd727 commit 338ee36
Show file tree
Hide file tree
Showing 5 changed files with 118 additions and 58 deletions.
100 changes: 44 additions & 56 deletions python/plugins/processing/algs/qgis/ExecuteSQL.py
View file
Expand Up @@ -25,21 +25,18 @@

__revision__ = '$Format:%H$'

from qgis.core import (QgsFeature,
QgsVirtualLayerDefinition,
from qgis.core import (QgsVirtualLayerDefinition,
QgsVectorLayer,
QgsCoordinateReferenceSystem,
QgsWkbTypes,
QgsApplication,
QgsProcessingUtils)
QgsProcessingParameterMultipleLayers,
QgsProcessingParameterString,
QgsProcessingParameterEnum,
QgsProcessingParameterCrs,
QgsProcessingParameterFeatureSink,
QgsFeatureSink,
QgsProcessingException)

from processing.algs.qgis.QgisAlgorithm import QgisAlgorithm
from processing.core.GeoAlgorithmExecutionException import GeoAlgorithmExecutionException
from processing.core.parameters import ParameterString
from processing.core.parameters import ParameterMultipleInput
from processing.core.parameters import ParameterCrs
from processing.core.parameters import ParameterSelection
from processing.core.outputs import OutputVector


class ExecuteSQL(QgisAlgorithm):
Expand All @@ -54,7 +51,7 @@ class ExecuteSQL(QgisAlgorithm):
INPUT_GEOMETRY_FIELD = 'INPUT_GEOMETRY_FIELD'
INPUT_GEOMETRY_TYPE = 'INPUT_GEOMETRY_TYPE'
INPUT_GEOMETRY_CRS = 'INPUT_GEOMETRY_CRS'
OUTPUT_LAYER = 'OUTPUT_LAYER'
OUTPUT = 'OUTPUT'

def group(self):
return self.tr('Vector general tools')
Expand All @@ -63,19 +60,19 @@ def __init__(self):
super().__init__()

def initAlgorithm(self, config=None):
self.addParameter(ParameterMultipleInput(name=self.INPUT_DATASOURCES,
description=self.tr('Additional input datasources (called input1, .., inputN in the query)'),
optional=True))
self.addParameter(QgsProcessingParameterMultipleLayers(name=self.INPUT_DATASOURCES,
description=self.tr('Additional input datasources (called input1, .., inputN in the query)'),
optional=True))

self.addParameter(ParameterString(name=self.INPUT_QUERY,
description=self.tr('SQL query'),
multiline=True))
self.addParameter(QgsProcessingParameterString(name=self.INPUT_QUERY,
description=self.tr('SQL query'),
multiLine=True))

self.addParameter(ParameterString(name=self.INPUT_UID_FIELD,
description=self.tr('Unique identifier field'), optional=True))
self.addParameter(QgsProcessingParameterString(name=self.INPUT_UID_FIELD,
description=self.tr('Unique identifier field'), optional=True))

self.addParameter(ParameterString(name=self.INPUT_GEOMETRY_FIELD,
description=self.tr('Geometry field'), optional=True))
self.addParameter(QgsProcessingParameterString(name=self.INPUT_GEOMETRY_FIELD,
description=self.tr('Geometry field'), optional=True))

self.geometryTypes = [
self.tr('Autodetect'),
Expand All @@ -86,13 +83,13 @@ def initAlgorithm(self, config=None):
'MultiPoint',
'MultiLineString',
'MultiPolygon']
self.addParameter(ParameterSelection(self.INPUT_GEOMETRY_TYPE,
self.tr('Geometry type'), self.geometryTypes, optional=True))
self.addParameter(QgsProcessingParameterEnum(self.INPUT_GEOMETRY_TYPE,
self.tr('Geometry type'), options=self.geometryTypes, optional=True))

self.addParameter(ParameterCrs(self.INPUT_GEOMETRY_CRS,
self.tr('CRS'), optional=True))
self.addParameter(QgsProcessingParameterCrs(self.INPUT_GEOMETRY_CRS,
self.tr('CRS'), optional=True))

self.addOutput(OutputVector(self.OUTPUT_LAYER, self.tr('SQL Output')))
self.addParameter(QgsProcessingParameterFeatureSink(self.OUTPUT, self.tr('SQL Output')))

def name(self):
return 'executesql'
Expand All @@ -101,24 +98,19 @@ def displayName(self):
return self.tr('Execute SQL')

def processAlgorithm(self, parameters, context, feedback):
layers = self.getParameterValue(self.INPUT_DATASOURCES)
query = self.getParameterValue(self.INPUT_QUERY)
uid_field = self.getParameterValue(self.INPUT_UID_FIELD)
geometry_field = self.getParameterValue(self.INPUT_GEOMETRY_FIELD)
geometry_type = self.getParameterValue(self.INPUT_GEOMETRY_TYPE)
geometry_crs = self.getParameterValue(self.INPUT_GEOMETRY_CRS)
layers = self.parameterAsLayerList(parameters, self.INPUT_DATASOURCES, context)
query = self.parameterAsString(parameters, self.INPUT_QUERY, context)
uid_field = self.parameterAsString(parameters, self.INPUT_UID_FIELD, context)
geometry_field = self.parameterAsString(parameters, self.INPUT_GEOMETRY_FIELD, context)
geometry_type = self.parameterAsEnum(parameters, self.INPUT_GEOMETRY_TYPE, context)
geometry_crs = self.parameterAsCrs(parameters, self.INPUT_GEOMETRY_CRS, context)

df = QgsVirtualLayerDefinition()
layerIdx = 1
if layers:
for layerSource in layers.split(';'):
layer = QgsProcessingUtils.mapLayerFromString(layerSource, context)
if layer:
df.addSource('input{}'.format(layerIdx), layer.id())
layerIdx += 1
for layerIdx, layer in enumerate(layers):
df.addSource('input{}'.format(layerIdx + 1), layer.id())

if query == '':
raise GeoAlgorithmExecutionException(
raise QgsProcessingException(
self.tr('Empty SQL. Please enter valid SQL expression and try again.'))
else:
df.setQuery(query)
Expand All @@ -133,26 +125,22 @@ def processAlgorithm(self, parameters, context, feedback):
df.setGeometryField(geometry_field)
if geometry_type > 1:
df.setGeometryWkbType(geometry_type - 1)
if geometry_crs:
crs = QgsCoordinateReferenceSystem(geometry_crs)
if crs.isValid():
df.setGeometrySrid(crs.postgisSrid())
if geometry_crs.isValid():
df.setGeometrySrid(geometry_crs.postgisSrid())

vLayer = QgsVectorLayer(df.toString(), "temp_vlayer", "virtual")
if not vLayer.isValid():
raise GeoAlgorithmExecutionException(vLayer.dataProvider().error().message())
raise QgsProcessingException(vLayer.dataProvider().error().message())

writer = self.getOutputFromName(self.OUTPUT_LAYER).getVectorWriter(vLayer.fields(),
vLayer.wkbType() if geometry_type != 1 else 1,
vLayer.crs(), context)
(sink, dest_id) = self.parameterAsSink(parameters, self.OUTPUT, context,
vLayer.fields(), vLayer.wkbType() if geometry_type != 1 else 1, vLayer.crs())

features = QgsProcessingUtils.getFeatures(vLayer, context)
features = vLayer.getFeatures()
total = 100.0 / vLayer.featureCount() if vLayer.featureCount() else 0
outFeat = QgsFeature()
for current, inFeat in enumerate(features):
outFeat.setAttributes(inFeat.attributes())
if geometry_type != 1:
outFeat.setGeometry(inFeat.geometry())
writer.addFeature(outFeat, QgsFeatureSink.FastInsert)
if feedback.isCanceled():
break

sink.addFeature(inFeat, QgsFeatureSink.FastInsert)
feedback.setProgress(int(current * total))
del writer
return {self.OUTPUT: dest_id}
5 changes: 3 additions & 2 deletions python/plugins/processing/algs/qgis/QGISAlgorithmProvider.py
View file
Expand Up @@ -65,6 +65,7 @@
from .DropMZValues import DropMZValues
from .EliminateSelection import EliminateSelection
from .EquivalentNumField import EquivalentNumField
from .ExecuteSQL import ExecuteSQL
from .Explode import Explode
from .ExportGeometryInfo import ExportGeometryInfo
from .ExtendLines import ExtendLines
Expand Down Expand Up @@ -172,7 +173,6 @@
# from .GeometryConvert import GeometryConvert
# from .SelectByAttributeSum import SelectByAttributeSum
# from .RasterCalculator import RasterCalculator
# from .ExecuteSQL import ExecuteSQL

pluginPath = os.path.normpath(os.path.join(
os.path.split(os.path.dirname(__file__))[0], os.pardir))
Expand All @@ -193,7 +193,7 @@ def getAlgs(self):
# GeometryConvert(),
# SelectByAttributeSum()
# RasterCalculator(),
# ExecuteSQL(),
#
# ]
algs = [AddTableField(),
Aggregate(),
Expand All @@ -220,6 +220,7 @@ def getAlgs(self):
DropMZValues(),
EliminateSelection(),
EquivalentNumField(),
ExecuteSQL(),
Explode(),
ExportGeometryInfo(),
ExtendLines(),
Expand Down
26 changes: 26 additions & 0 deletions python/plugins/processing/tests/testdata/expected/execute_sql.gfs
View file
@@ -0,0 +1,26 @@
<GMLFeatureClassList>
<GMLFeatureClass>
<Name>execute_sql</Name>
<ElementPath>execute_sql</ElementPath>
<!--POINT-->
<GeometryType>1</GeometryType>
<SRSName>EPSG:4326</SRSName>
<DatasetSpecificInfo>
<FeatureCount>2</FeatureCount>
<ExtentXMin>1.00000</ExtentXMin>
<ExtentXMax>5.00000</ExtentXMax>
<ExtentYMin>1.00000</ExtentYMin>
<ExtentYMax>2.00000</ExtentYMax>
</DatasetSpecificInfo>
<PropertyDefn>
<Name>id</Name>
<ElementPath>id</ElementPath>
<Type>Integer</Type>
</PropertyDefn>
<PropertyDefn>
<Name>id2</Name>
<ElementPath>id2</ElementPath>
<Type>Integer</Type>
</PropertyDefn>
</GMLFeatureClass>
</GMLFeatureClassList>
28 changes: 28 additions & 0 deletions python/plugins/processing/tests/testdata/expected/execute_sql.gml
View file
@@ -0,0 +1,28 @@
<?xml version="1.0" encoding="utf-8" ?>
<ogr:FeatureCollection
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation=""
xmlns:ogr="http://ogr.maptools.org/"
xmlns:gml="http://www.opengis.net/gml">
<gml:boundedBy>
<gml:Box>
<gml:coord><gml:X>1</gml:X><gml:Y>1</gml:Y></gml:coord>
<gml:coord><gml:X>5</gml:X><gml:Y>2</gml:Y></gml:coord>
</gml:Box>
</gml:boundedBy>

<gml:featureMember>
<ogr:execute_sql fid="points.0">
<ogr:geometryProperty><gml:Point srsName="EPSG:4326"><gml:coordinates>1,1</gml:coordinates></gml:Point></ogr:geometryProperty>
<ogr:id>1</ogr:id>
<ogr:id2>2</ogr:id2>
</ogr:execute_sql>
</gml:featureMember>
<gml:featureMember>
<ogr:execute_sql fid="points.3">
<ogr:geometryProperty><gml:Point srsName="EPSG:4326"><gml:coordinates>5,2</gml:coordinates></gml:Point></ogr:geometryProperty>
<ogr:id>4</ogr:id>
<ogr:id2>2</ogr:id2>
</ogr:execute_sql>
</gml:featureMember>
</ogr:FeatureCollection>
17 changes: 17 additions & 0 deletions python/plugins/processing/tests/testdata/qgis_algorithm_tests.yaml
View file
Expand Up @@ -3210,3 +3210,20 @@ tests:
OUTPUT:
name: expected/pycalculator_points.gml
type: vector

- algorithm: qgis:executesql
name: Test execute SQL
params:
INPUT_DATASOURCES:
params:
- name: points.gml
type: vector
type: multi
INPUT_GEOMETRY_FIELD: ''
INPUT_GEOMETRY_TYPE: 0
INPUT_QUERY: select * from input1 where id2=2
INPUT_UID_FIELD: ''
results:
OUTPUT:
name: expected/execute_sql.gml
type: vector

0 comments on commit 338ee36

Please sign in to comment.