Skip to content

Commit 60f246a

Browse files
author
Hugo Mercier
committedDec 18, 2015
Merge pull request #2568 from mhugo/vlayers
Add support for virtual layers
·
ltr-3_40archive/master_2
2 parents 13f4081 + 6ffec81 commit 60f246a

35 files changed

+5305
-2
lines changed
 

‎python/core/core.sip

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -141,6 +141,7 @@
141141
%Include qgsvectorlayerfeatureiterator.sip
142142
%Include qgsvisibilitypresetcollection.sip
143143
%Include qgslayerdefinition.sip
144+
%Include qgsvirtuallayerdefinition.sip
144145

145146
%Include auth/qgsauthcertutils.sip
146147
%Include auth/qgsauthconfig.sip

‎python/core/qgsvirtuallayerdefinition.sip

Lines changed: 125 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,125 @@
1+
/**
2+
* Class to manipulate the definition of a virtual layer
3+
*
4+
* It is used to extract parameters from an initial virtual layer definition as well as
5+
* to store the complete, expanded definition once types have been detected.
6+
*/
7+
class QgsVirtualLayerDefinition
8+
{
9+
%TypeHeaderCode
10+
#include <qgsvirtuallayerdefinition.h>
11+
%End
12+
public:
13+
/**
14+
* A SourceLayer is either a reference to a live layer in the registry
15+
* or all the parameters needed to load it (provider key, source, etc.)
16+
*/
17+
class SourceLayer
18+
{
19+
public:
20+
//! Constructor variant to build a live layer reference
21+
SourceLayer( const QString& name, const QString& ref );
22+
//! Constructor variant to build a layer with a provider and a source
23+
SourceLayer( const QString& name, const QString& source, const QString& provider, const QString& encoding );
24+
25+
//! Is it a live layer or not ?
26+
bool isReferenced() const;
27+
28+
//! The reference (id) of the live layer
29+
QString reference() const;
30+
31+
//! Name of the layer
32+
QString name() const;
33+
34+
//! Provider key
35+
QString provider() const;
36+
37+
//! The source url used by the provider to build the layer
38+
QString source() const;
39+
40+
//! Optional encoding for the provider
41+
QString encoding() const;
42+
};
43+
44+
//! Constructor with an optional file path
45+
QgsVirtualLayerDefinition( const QString& filePath = "" );
46+
47+
//! Constructor to build a definition from a QUrl
48+
//! The path part of the URL is extracted as well as the following optional keys:
49+
//! layer_ref=layer_id[:name] represents a live layer referenced by its ID. An optional name can be given
50+
//! layer=provider:source[:name[:encoding]] represents a layer given by its provider key, its source url (URL-encoded).
51+
//! An optional name and encoding can be given
52+
//! geometry=column_name[:type:srid] gives the definition of the geometry column.
53+
//! Type can be either a WKB type code or a string (point, linestring, etc.)
54+
//! srid is an integer
55+
//! uid=column_name is the name of a column with unique integer values.
56+
//! nogeometry is a flag to force the layer to be a non-geometry layer
57+
//! query=sql represents the SQL query. Must be URL-encoded
58+
//! field=column_name:[int|real|text] represents a field with its name and its type
59+
static QgsVirtualLayerDefinition fromUrl( const QUrl& url );
60+
61+
//! Convert the definition into a QUrl
62+
QUrl toUrl() const;
63+
64+
//! Convert into a QString that can be read by the virtual layer provider
65+
QString toString() const;
66+
67+
//! Add a live layer source layer
68+
void addSource( const QString& name, const QString ref );
69+
70+
//! Add a layer with a source, a provider and an encoding
71+
void addSource( const QString& name, const QString source, const QString& provider, const QString& encoding = "" );
72+
73+
//! List of source layers
74+
typedef QList<QgsVirtualLayerDefinition::SourceLayer> SourceLayers;
75+
76+
//! Get access to the source layers
77+
const SourceLayers& sourceLayers() const;
78+
79+
//! Get the SQL query
80+
QString query() const;
81+
//! Set the SQL query
82+
void setQuery( const QString& query );
83+
84+
//! Get the file path. May be empty
85+
QString filePath() const;
86+
//! Set the file path
87+
void setFilePath( const QString& filePath );
88+
89+
//! Get the name of the field with unique identifiers
90+
QString uid() const;
91+
//! Set the name of the field with unique identifiers
92+
void setUid( const QString& uid );
93+
94+
//! Get the name of the geometry field. Empty if no geometry field
95+
QString geometryField() const;
96+
//! Set the name of the geometry field
97+
void setGeometryField( const QString& geometryField );
98+
99+
//! Get the type of the geometry
100+
//! QgsWKBTypes::NoGeometry to hide any geometry
101+
//! QgsWKBTypes::Unknown for unknown types
102+
QgsWKBTypes::Type geometryWkbType() const;
103+
//! Set the type of the geometry
104+
void setGeometryWkbType( QgsWKBTypes::Type t );
105+
106+
//! Get the SRID of the geometry
107+
long geometrySrid() const;
108+
//! Set the SRID of the geometry
109+
void setGeometrySrid( long srid );
110+
111+
//! Get field definitions
112+
const QgsFields& fields() const;
113+
//! Set field definitions
114+
void setFields( const QgsFields& fields );
115+
116+
//! Convenience method to test if a given source layer is part of the definition
117+
bool hasSourceLayer( QString name ) const;
118+
119+
//! Convenience method to test whether the definition has referenced (live) layers
120+
bool hasReferencedLayers() const;
121+
122+
//! Convenient method to test if the geometry is defined (not NoGeometry and not Unknown)
123+
bool hasDefinedGeometry() const;
124+
};
125+

‎python/plugins/db_manager/db_plugins/CMakeLists.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@ ADD_SUBDIRECTORY(spatialite)
33
IF(WITH_ORACLE)
44
ADD_SUBDIRECTORY(oracle)
55
ENDIF(WITH_ORACLE)
6+
ADD_SUBDIRECTORY(vlayers)
67

78
FILE(GLOB PY_FILES *.py)
89
PLUGIN_INSTALL(db_manager db_plugins ${PY_FILES})

‎python/plugins/db_manager/db_plugins/vlayers/CMakeLists.txt

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
2+
FILE(GLOB PY_FILES *.py)
3+
4+
PYQT_ADD_RESOURCES(PYRC_FILES resources.qrc)
5+
6+
PLUGIN_INSTALL(db_manager db_plugins/vlayers ${PY_FILES} ${PYRC_FILES})
7+

‎python/plugins/db_manager/db_plugins/vlayers/__init__.py

Whitespace-only changes.

‎python/plugins/db_manager/db_plugins/vlayers/connector.py

Lines changed: 430 additions & 0 deletions
Large diffs are not rendered by default.

‎python/plugins/db_manager/db_plugins/vlayers/data_model.py

Lines changed: 111 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,111 @@
1+
# -*- coding: utf-8 -*-
2+
3+
"""
4+
/***************************************************************************
5+
Name : Virtual layers plugin for DB Manager
6+
Date : December 2015
7+
copyright : (C) 2015 by Hugo Mercier
8+
email : hugo dot mercier at oslandia dot com
9+
10+
***************************************************************************/
11+
12+
/***************************************************************************
13+
* *
14+
* This program is free software; you can redistribute it and/or modify *
15+
* it under the terms of the GNU General Public License as published by *
16+
* the Free Software Foundation; either version 2 of the License, or *
17+
* (at your option) any later version. *
18+
* *
19+
***************************************************************************/
20+
"""
21+
22+
from ..data_model import TableDataModel, BaseTableModel
23+
24+
from .connector import VLayerRegistry, getQueryGeometryName
25+
from .plugin import LVectorTable
26+
from ..plugin import DbError
27+
28+
from PyQt4.QtCore import QUrl, QTime, QTemporaryFile
29+
from qgis.core import QgsProviderRegistry, QgsErrorMessage, QGis, QgsVectorLayer
30+
31+
import os
32+
33+
34+
class LTableDataModel(TableDataModel):
35+
36+
def __init__(self, table, parent=None):
37+
TableDataModel.__init__(self, table, parent)
38+
39+
self.layer = None
40+
41+
if isinstance(table, LVectorTable):
42+
self.layer = VLayerRegistry.instance().getLayer(table.name)
43+
else:
44+
self.layer = VLayerRegistry.instance().getLayer(table)
45+
46+
if not self.layer:
47+
return
48+
# populate self.resdata
49+
self.resdata = []
50+
for f in self.layer.getFeatures():
51+
self.resdata.append(f.attributes())
52+
53+
self.fetchedFrom = 0
54+
self.fetchedCount = len(self.resdata)
55+
56+
def rowCount(self, index=None):
57+
if self.layer:
58+
return self.layer.featureCount()
59+
return 0
60+
61+
62+
class LSqlResultModel(BaseTableModel):
63+
# BaseTableModel
64+
65+
def __init__(self, db, sql, parent=None):
66+
# create a virtual layer with non-geometry results
67+
q = QUrl.toPercentEncoding(sql)
68+
t = QTime()
69+
t.start()
70+
71+
tf = QTemporaryFile()
72+
tf.open()
73+
tmp = tf.fileName()
74+
tf.close()
75+
76+
p = QgsVectorLayer("%s?query=%s" % (tmp, q), "vv", "virtual")
77+
self._secs = t.elapsed() / 1000.0
78+
79+
if not p.isValid():
80+
data = []
81+
header = []
82+
raise DbError(p.dataProvider().error().summary(), sql)
83+
else:
84+
header = [f.name() for f in p.fields()]
85+
has_geometry = False
86+
if p.geometryType() != QGis.WKBNoGeometry:
87+
gn = getQueryGeometryName(tmp)
88+
if gn:
89+
has_geometry = True
90+
header += [gn]
91+
92+
data = []
93+
for f in p.getFeatures():
94+
a = f.attributes()
95+
if has_geometry:
96+
if f.geometry():
97+
a += [f.geometry().exportToWkt()]
98+
else:
99+
a += [None]
100+
data += [a]
101+
102+
self._secs = 0
103+
self._affectedRows = len(data)
104+
105+
BaseTableModel.__init__(self, header, data, parent)
106+
107+
def secs(self):
108+
return self._secs
109+
110+
def affectedRows(self):
111+
return self._affectedRows

‎python/plugins/db_manager/db_plugins/vlayers/info_model.py

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
# -*- coding: utf-8 -*-
2+
3+
"""
4+
/***************************************************************************
5+
Name : Virtual layers plugin for DB Manager
6+
Date : December 2015
7+
copyright : (C) 2015 by Hugo Mercier
8+
email : hugo dot mercier at oslandia dot com
9+
10+
***************************************************************************/
11+
12+
/***************************************************************************
13+
* *
14+
* This program is free software; you can redistribute it and/or modify *
15+
* it under the terms of the GNU General Public License as published by *
16+
* the Free Software Foundation; either version 2 of the License, or *
17+
* (at your option) any later version. *
18+
* *
19+
***************************************************************************/
20+
"""
21+
22+
from PyQt4.QtGui import QApplication
23+
24+
from ..info_model import DatabaseInfo
25+
from ..html_elems import HtmlTable
26+
27+
28+
class LDatabaseInfo(DatabaseInfo):
29+
30+
def __init__(self, db):
31+
self.db = db
32+
33+
def connectionDetails(self):
34+
tbl = [
35+
]
36+
return HtmlTable(tbl)
37+
38+
def generalInfo(self):
39+
info = self.db.connector.getInfo()
40+
tbl = [
41+
(QApplication.translate("DBManagerPlugin", "SQLite version:"), "3")
42+
]
43+
return HtmlTable(tbl)
44+
45+
def privilegesDetails(self):
46+
return None

‎python/plugins/db_manager/db_plugins/vlayers/plugin.py

Lines changed: 191 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,191 @@
1+
# -*- coding: utf-8 -*-
2+
3+
"""
4+
/***************************************************************************
5+
Name : DB Manager plugin for virtual layers
6+
Date : December 2015
7+
copyright : (C) 2015 by Hugo Mercier
8+
email : hugo dot mercier at oslandia dot com
9+
10+
***************************************************************************/
11+
12+
/***************************************************************************
13+
* *
14+
* This program is free software; you can redistribute it and/or modify *
15+
* it under the terms of the GNU General Public License as published by *
16+
* the Free Software Foundation; either version 2 of the License, or *
17+
* (at your option) any later version. *
18+
* *
19+
***************************************************************************/
20+
"""
21+
22+
# this will disable the dbplugin if the connector raise an ImportError
23+
from .connector import VLayerConnector
24+
25+
from PyQt4.QtCore import Qt, QSettings, QUrl
26+
from PyQt4.QtGui import QIcon, QApplication, QAction
27+
from qgis.core import QgsVectorLayer, QgsMapLayerRegistry
28+
from qgis.gui import QgsMessageBar
29+
30+
from ..plugin import DBPlugin, Database, Table, VectorTable, RasterTable, TableField, TableIndex, TableTrigger, InvalidDataException
31+
try:
32+
from . import resources_rc
33+
except ImportError:
34+
pass
35+
36+
37+
def classFactory():
38+
return VLayerDBPlugin
39+
40+
41+
class VLayerDBPlugin(DBPlugin):
42+
43+
@classmethod
44+
def icon(self):
45+
return QIcon(":/db_manager/vlayers/icon")
46+
47+
@classmethod
48+
def typeName(self):
49+
return 'vlayers'
50+
51+
@classmethod
52+
def typeNameString(self):
53+
return 'Virtual Layers'
54+
55+
@classmethod
56+
def providerName(self):
57+
return 'virtual'
58+
59+
@classmethod
60+
def connectionSettingsKey(self):
61+
return 'vlayers'
62+
63+
@classmethod
64+
def connections(self):
65+
return [VLayerDBPlugin('QGIS layers')]
66+
67+
def databasesFactory(self, connection, uri):
68+
return FakeDatabase(connection, uri)
69+
70+
def database(self):
71+
return self.db
72+
73+
# def info( self ):
74+
75+
def connect(self, parent=None):
76+
self.connectToUri("qgis")
77+
return True
78+
79+
80+
class FakeDatabase(Database):
81+
82+
def __init__(self, connection, uri):
83+
Database.__init__(self, connection, uri)
84+
85+
def connectorsFactory(self, uri):
86+
return VLayerConnector(uri)
87+
88+
def dataTablesFactory(self, row, db, schema=None):
89+
return LTable(row, db, schema)
90+
91+
def vectorTablesFactory(self, row, db, schema=None):
92+
return LVectorTable(row, db, schema)
93+
94+
def rasterTablesFactory(self, row, db, schema=None):
95+
return None
96+
97+
def info(self):
98+
from .info_model import LDatabaseInfo
99+
return LDatabaseInfo(self)
100+
101+
def sqlResultModel(self, sql, parent):
102+
from .data_model import LSqlResultModel
103+
return LSqlResultModel(self, sql, parent)
104+
105+
def toSqlLayer(self, sql, geomCol, uniqueCol, layerName="QueryLayer", layerType=None, avoidSelectById=False, _filter=""):
106+
q = QUrl.toPercentEncoding(sql)
107+
s = "?query=%s" % q
108+
if uniqueCol is not None:
109+
s += "&uid=" + uniqueCol
110+
if geomCol is not None:
111+
s += "&geometry=" + geomCol
112+
vl = QgsVectorLayer(s, layerName, "virtual")
113+
if _filter:
114+
vl.setSubsetString(_filter)
115+
return vl
116+
117+
def registerDatabaseActions(self, mainWindow):
118+
return
119+
120+
def runAction(self, action):
121+
return
122+
123+
def uniqueIdFunction(self):
124+
return None
125+
126+
def explicitSpatialIndex(self):
127+
return True
128+
129+
def spatialIndexClause(self, src_table, src_column, dest_table, dest_column):
130+
return '"%s"._search_frame_ = "%s"."%s"' % (src_table, dest_table, dest_column)
131+
132+
133+
class LTable(Table):
134+
135+
def __init__(self, row, db, schema=None):
136+
Table.__init__(self, db, None)
137+
self.name, self.isView, self.isSysTable = row
138+
139+
def tableFieldsFactory(self, row, table):
140+
return LTableField(row, table)
141+
142+
def tableDataModel(self, parent):
143+
from .data_model import LTableDataModel
144+
return LTableDataModel(self, parent)
145+
146+
def canBeAddedToCanvas(self):
147+
return False
148+
149+
150+
class LVectorTable(LTable, VectorTable):
151+
152+
def __init__(self, row, db, schema=None):
153+
LTable.__init__(self, row[:-5], db, schema)
154+
VectorTable.__init__(self, db, schema)
155+
# SpatiaLite does case-insensitive checks for table names, but the
156+
# SL provider didn't do the same in QGis < 1.9, so self.geomTableName
157+
# stores the table name like stored in the geometry_columns table
158+
self.geomTableName, self.geomColumn, self.geomType, self.geomDim, self.srid = row[
159+
-5:]
160+
161+
def uri(self):
162+
uri = self.database().uri()
163+
uri.setDataSource('', self.geomTableName, self.geomColumn)
164+
return uri
165+
166+
def hasSpatialIndex(self, geom_column=None):
167+
return True
168+
169+
def createSpatialIndex(self, geom_column=None):
170+
return
171+
172+
def deleteSpatialIndex(self, geom_column=None):
173+
return
174+
175+
def refreshTableEstimatedExtent(self):
176+
self.extent = self.database().connector.getTableExtent(
177+
("id", self.geomTableName), None)
178+
179+
def runAction(self, action):
180+
return
181+
182+
def toMapLayer(self):
183+
return QgsMapLayerRegistry.instance().mapLayer(self.geomTableName)
184+
185+
186+
class LTableField(TableField):
187+
188+
def __init__(self, row, table):
189+
TableField.__init__(self, table)
190+
self.num, self.name, self.dataType, self.notNull, self.default, self.primaryKey = row
191+
self.hasDefault = self.default

‎python/plugins/db_manager/db_plugins/vlayers/resources.qrc

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
<RCC>
2+
<qresource prefix="/db_manager/vlayers">
3+
<file alias="icon">vlayer.svg</file>
4+
</qresource>
5+
</RCC>

‎python/plugins/db_manager/db_plugins/vlayers/sql_dictionary.py

Lines changed: 136 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,136 @@
1+
# -*- coding: utf-8 -*-
2+
3+
# keywords
4+
keywords = [
5+
# TODO get them from a reference page
6+
"action", "add", "after", "all", "alter", "analyze", "and", "as", "asc",
7+
"before", "begin", "between", "by", "cascade", "case", "cast", "check",
8+
"collate", "column", "commit", "constraint", "create", "cross", "current_date",
9+
"current_time", "current_timestamp", "default", "deferrable", "deferred",
10+
"delete", "desc", "distinct", "drop", "each", "else", "end", "escape",
11+
"except", "exists", "for", "foreign", "from", "full", "group", "having",
12+
"ignore", "immediate", "in", "initially", "inner", "insert", "intersect",
13+
"into", "is", "isnull", "join", "key", "left", "like", "limit", "match",
14+
"natural", "no", "not", "notnull", "null", "of", "offset", "on", "or", "order",
15+
"outer", "primary", "references", "release", "restrict", "right", "rollback",
16+
"row", "savepoint", "select", "set", "table", "temporary", "then", "to",
17+
"transaction", "trigger", "union", "unique", "update", "using", "values",
18+
"view", "when", "where",
19+
20+
"abort", "attach", "autoincrement", "conflict", "database", "detach",
21+
"exclusive", "explain", "fail", "glob", "if", "index", "indexed", "instead",
22+
"plan", "pragma", "query", "raise", "regexp", "reindex", "rename", "replace",
23+
"temp", "vacuum", "virtual"
24+
]
25+
spatialite_keywords = []
26+
27+
# functions
28+
functions = [
29+
# TODO get them from a reference page
30+
"changes", "coalesce", "glob", "ifnull", "hex", "last_insert_rowid",
31+
"nullif", "quote", "random",
32+
"randomblob", "replace", "round", "soundex", "total_change",
33+
"typeof", "zeroblob", "date", "datetime", "julianday", "strftime"
34+
]
35+
operators = [
36+
' AND ', ' OR ', '||', ' < ', ' <= ', ' > ', ' >= ', ' = ', ' <> ', ' IS ', ' IS NOT ', ' IN ', ' LIKE ', ' GLOB ', ' MATCH ', ' REGEXP '
37+
]
38+
39+
math_functions = [
40+
# SQL math functions
41+
"Abs", "ACos", "ASin", "ATan", "Cos", "Cot", "Degrees", "Exp", "Floor", "Log", "Log2",
42+
"Log10", "Pi", "Radians", "Round", "Sign", "Sin", "Sqrt", "StdDev_Pop", "StdDev_Samp", "Tan",
43+
"Var_Pop", "Var_Samp"]
44+
45+
string_functions = ["Length", "Lower", "Upper", "Like", "Trim", "LTrim", "RTrim", "Replace", "Substr"]
46+
47+
aggregate_functions = [
48+
"Max", "Min", "Avg", "Count", "Sum", "Group_Concat", "Total", "Var_Pop", "Var_Samp", "StdDev_Pop", "StdDev_Samp"
49+
]
50+
51+
spatialite_functions = [ # from www.gaia-gis.it/spatialite-2.3.0/spatialite-sql-2.3.0.html
52+
# SQL utility functions for BLOB objects
53+
"*iszipblob", "*ispdfblob", "*isgifblob", "*ispngblob", "*isjpegblob", "*isexifblob",
54+
"*isexifgpsblob", "*geomfromexifgpsblob", "MakePoint", "BuildMbr", "*buildcirclembr", "ST_MinX",
55+
"ST_MinY", "ST_MaxX", "ST_MaxY",
56+
# SQL functions for constructing a geometric object given its Well-known Text Representation
57+
"ST_GeomFromText", "*pointfromtext",
58+
# SQL functions for constructing a geometric object given its Well-known Binary Representation
59+
"*geomfromwkb", "*pointfromwkb",
60+
# SQL functions for obtaining the Well-known Text / Well-known Binary Representation of a geometric object
61+
"ST_AsText", "ST_AsBinary",
62+
# SQL functions supporting exotic geometric formats
63+
"*assvg", "*asfgf", "*geomfromfgf",
64+
# SQL functions on type Geometry
65+
"ST_Dimension", "ST_GeometryType", "ST_Srid", "ST_SetSrid", "ST_isEmpty", "ST_isSimple", "ST_isValid", "ST_Boundary",
66+
"ST_Envelope",
67+
# SQL functions on type Point
68+
"ST_X", "ST_Y",
69+
# SQL functions on type Curve [Linestring or Ring]
70+
"ST_StartPoint", "ST_EndPoint", "ST_Length", "ST_isClosed", "ST_isRing", "ST_Simplify",
71+
"*simplifypreservetopology",
72+
# SQL functions on type LineString
73+
"ST_NumPoints", "ST_PointN",
74+
# SQL functions on type Surface [Polygon or Ring]
75+
"ST_Centroid", "ST_PointOnSurface", "ST_Area",
76+
# SQL functions on type Polygon
77+
"ST_ExteriorRing", "ST_InteriorRingN",
78+
# SQL functions on type GeomCollection
79+
"ST_NumGeometries", "ST_GeometryN",
80+
# SQL functions that test approximative spatial relationships via MBRs
81+
"MbrEqual", "MbrDisjoint", "MbrTouches", "MbrWithin", "MbrOverlaps", "MbrIntersects",
82+
"MbrContains",
83+
# SQL functions that test spatial relationships
84+
"ST_Equals", "ST_Disjoint", "ST_Touches", "ST_Within", "ST_Overlaps", "ST_Crosses", "ST_Intersects", "ST_Contains",
85+
"ST_Relate",
86+
# SQL functions for distance relationships
87+
"ST_Distance",
88+
# SQL functions that implement spatial operators
89+
"ST_Intersection", "ST_Difference", "ST_Union", "ST_SymDifference", "ST_Buffer", "ST_ConvexHull",
90+
# SQL functions for coordinate transformations
91+
"ST_Transform",
92+
# SQL functions for Spatial-MetaData and Spatial-Index handling
93+
"*initspatialmetadata", "*addgeometrycolumn", "*recovergeometrycolumn", "*discardgeometrycolumn",
94+
"*createspatialindex", "*creatembrcache", "*disablespatialindex",
95+
# SQL functions implementing FDO/OGR compatibily
96+
"*checkspatialmetadata", "*autofdostart", "*autofdostop", "*initfdospatialmetadata",
97+
"*addfdogeometrycolumn", "*recoverfdogeometrycolumn", "*discardfdogeometrycolumn",
98+
# SQL functions for MbrCache-based queries
99+
"*filtermbrwithin", "*filtermbrcontains", "*filtermbrintersects", "*buildmbrfilter"
100+
]
101+
102+
# constants
103+
constants = ["null", "false", "true"]
104+
spatialite_constants = []
105+
106+
107+
def getSqlDictionary(spatial=True):
108+
def strip_star(s):
109+
if s[0] == '*':
110+
return s.lower()[1:]
111+
else:
112+
return s.lower()
113+
114+
k, c, f = list(keywords), list(constants), list(functions)
115+
116+
if spatial:
117+
k += spatialite_keywords
118+
f += spatialite_functions
119+
c += spatialite_constants
120+
121+
return {'keyword': map(strip_star, k), 'constant': map(strip_star, c), 'function': map(strip_star, f)}
122+
123+
124+
def getQueryBuilderDictionary():
125+
# concat functions
126+
def ff(l):
127+
return filter(lambda s: s[0] != '*', l)
128+
129+
def add_paren(l):
130+
return map(lambda s: s + "(", l)
131+
foo = sorted(add_paren(ff(list(set.union(set(functions), set(spatialite_functions))))))
132+
m = sorted(add_paren(ff(math_functions)))
133+
agg = sorted(add_paren(ff(aggregate_functions)))
134+
op = ff(operators)
135+
s = sorted(add_paren(ff(string_functions)))
136+
return {'function': foo, 'math': m, 'aggregate': agg, 'operator': op, 'string': s}

‎python/plugins/db_manager/db_plugins/vlayers/vlayer.svg

Lines changed: 242 additions & 0 deletions
Loading

‎src/core/CMakeLists.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -202,6 +202,7 @@ SET(QGIS_CORE_SRCS
202202
qgsvectorlayerundocommand.cpp
203203
qgsvectorsimplifymethod.cpp
204204
qgsvisibilitypresetcollection.cpp
205+
qgsvirtuallayerdefinition.cpp
205206
qgsxmlutils.cpp
206207
qgsslconnect.cpp
207208
qgslocalec.cpp

‎src/core/qgsvirtuallayerdefinition.cpp

Lines changed: 250 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,250 @@
1+
/***************************************************************************
2+
qgsvirtuallayerdefinition.cpp
3+
begin : December 2015
4+
copyright : (C) 2015 Hugo Mercier, Oslandia
5+
email : hugo dot mercier at oslandia dot com
6+
***************************************************************************/
7+
8+
/***************************************************************************
9+
* *
10+
* This program is free software; you can redistribute it and/or modify *
11+
* it under the terms of the GNU General Public License as published by *
12+
* the Free Software Foundation; either version 2 of the License, or *
13+
* (at your option) any later version. *
14+
* *
15+
***************************************************************************/
16+
17+
#include <QUrl>
18+
#include <QRegExp>
19+
#include <QStringList>
20+
21+
#include "qgsvirtuallayerdefinition.h"
22+
23+
QgsVirtualLayerDefinition::QgsVirtualLayerDefinition( const QString& filePath ) :
24+
mFilePath( filePath ),
25+
mGeometryWkbType( QgsWKBTypes::Unknown ),
26+
mGeometrySrid( 0 )
27+
{
28+
}
29+
30+
QgsVirtualLayerDefinition QgsVirtualLayerDefinition::fromUrl( const QUrl& url )
31+
{
32+
QgsVirtualLayerDefinition def;
33+
34+
def.setFilePath( url.path() );
35+
36+
// regexp for column name
37+
const QString columnNameRx( "[a-zA-Z_\x80-\xFF][a-zA-Z0-9_\x80-\xFF]*" );
38+
39+
QgsFields fields;
40+
41+
int layerIdx = 0;
42+
QList<QPair<QString, QString> > items = url.queryItems();
43+
for ( int i = 0; i < items.size(); i++ )
44+
{
45+
QString key = items.at( i ).first;
46+
QString value = items.at( i ).second;
47+
if ( key == "layer_ref" )
48+
{
49+
layerIdx++;
50+
// layer id, with optional layer_name
51+
int pos = value.indexOf( ':' );
52+
QString layerId, vlayerName;
53+
if ( pos == -1 )
54+
{
55+
layerId = value;
56+
vlayerName = QString( "vtab%1" ).arg( layerIdx );
57+
}
58+
else
59+
{
60+
layerId = value.left( pos );
61+
vlayerName = QUrl::fromPercentEncoding( value.mid( pos + 1 ).toUtf8() );
62+
}
63+
// add the layer to the list
64+
def.addSource( vlayerName, layerId );
65+
}
66+
else if ( key == "layer" )
67+
{
68+
layerIdx++;
69+
// syntax: layer=provider:url_encoded_source_URI(:name(:encoding)?)?
70+
int pos = value.indexOf( ':' );
71+
if ( pos != -1 )
72+
{
73+
QString providerKey, source, vlayerName, encoding = "UTF-8";
74+
75+
providerKey = value.left( pos );
76+
int pos2 = value.indexOf( ':', pos + 1 );
77+
if ( pos2 != -1 )
78+
{
79+
source = QUrl::fromPercentEncoding( value.mid( pos + 1, pos2 - pos - 1 ).toUtf8() );
80+
int pos3 = value.indexOf( ':', pos2 + 1 );
81+
if ( pos3 != -1 )
82+
{
83+
vlayerName = QUrl::fromPercentEncoding( value.mid( pos2 + 1, pos3 - pos2 - 1 ).toUtf8() );
84+
encoding = value.mid( pos3 + 1 );
85+
}
86+
else
87+
{
88+
vlayerName = QUrl::fromPercentEncoding( value.mid( pos2 + 1 ).toUtf8() );
89+
}
90+
}
91+
else
92+
{
93+
source = QUrl::fromPercentEncoding( value.mid( pos + 1 ).toUtf8() );
94+
vlayerName = QString( "vtab%1" ).arg( layerIdx );
95+
}
96+
97+
def.addSource( vlayerName, source, providerKey, encoding );
98+
}
99+
}
100+
else if ( key == "geometry" )
101+
{
102+
// geometry field definition, optional
103+
// geometry_column(:wkb_type:srid)?
104+
QRegExp reGeom( "(" + columnNameRx + ")(?::([a-zA-Z0-9]+):(\\d+))?" );
105+
int pos = reGeom.indexIn( value );
106+
if ( pos >= 0 )
107+
{
108+
def.setGeometryField( reGeom.cap( 1 ) );
109+
if ( reGeom.captureCount() > 1 )
110+
{
111+
// not used by the spatialite provider for now ...
112+
QgsWKBTypes::Type wkbType = QgsWKBTypes::parseType( reGeom.cap( 2 ) );
113+
if ( wkbType == QgsWKBTypes::Unknown )
114+
{
115+
wkbType = static_cast<QgsWKBTypes::Type>( reGeom.cap( 2 ).toLong() );
116+
}
117+
def.setGeometryWkbType( wkbType );
118+
def.setGeometrySrid( reGeom.cap( 3 ).toLong() );
119+
}
120+
}
121+
}
122+
else if ( key == "nogeometry" )
123+
{
124+
def.setGeometryWkbType( QgsWKBTypes::NoGeometry );
125+
}
126+
else if ( key == "uid" )
127+
{
128+
def.setUid( value );
129+
}
130+
else if ( key == "query" )
131+
{
132+
// url encoded query
133+
def.setQuery( value );
134+
}
135+
else if ( key == "field" )
136+
{
137+
// field_name:type (int, real, text)
138+
QRegExp reField( "(" + columnNameRx + "):(int|real|text)" );
139+
int pos = reField.indexIn( value );
140+
if ( pos >= 0 )
141+
{
142+
QString fieldName( reField.cap( 1 ) );
143+
QString fieldType( reField.cap( 2 ) );
144+
if ( fieldType == "int" )
145+
{
146+
fields.append( QgsField( fieldName, QVariant::Int, fieldType ) );
147+
}
148+
else if ( fieldType == "real" )
149+
{
150+
fields.append( QgsField( fieldName, QVariant::Double, fieldType ) );
151+
}
152+
if ( fieldType == "text" )
153+
{
154+
fields.append( QgsField( fieldName, QVariant::String, fieldType ) );
155+
}
156+
}
157+
}
158+
}
159+
def.setFields( fields );
160+
161+
return def;
162+
}
163+
164+
QUrl QgsVirtualLayerDefinition::toUrl() const
165+
{
166+
QUrl url;
167+
url.setPath( filePath() );
168+
169+
foreach ( const QgsVirtualLayerDefinition::SourceLayer& l, sourceLayers() )
170+
{
171+
if ( l.isReferenced() )
172+
url.addQueryItem( "layer_ref", QString( "%1:%2" ).arg( l.reference() ).arg( l.name() ) );
173+
else
174+
url.addQueryItem( "layer", QString( "%1:%4:%2:%3" ) // the order is important, since the 4th argument may contain '%2' as well
175+
.arg( l.provider() )
176+
.arg( QString( QUrl::toPercentEncoding( l.name() ) ) )
177+
.arg( l.encoding() )
178+
.arg( QString( QUrl::toPercentEncoding( l.source() ) ) ) );
179+
}
180+
181+
if ( !query().isEmpty() )
182+
{
183+
url.addQueryItem( "query", query() );
184+
}
185+
186+
if ( !uid().isEmpty() )
187+
url.addQueryItem( "uid", uid() );
188+
189+
if ( geometryWkbType() == QgsWKBTypes::NoGeometry )
190+
url.addQueryItem( "nogeometry", "" );
191+
else if ( !geometryField().isEmpty() )
192+
{
193+
if ( hasDefinedGeometry() )
194+
url.addQueryItem( "geometry", QString( "%1:%2:%3" ).arg( geometryField() ). arg( geometryWkbType() ).arg( geometrySrid() ).toUtf8() );
195+
else
196+
url.addQueryItem( "geometry", geometryField() );
197+
}
198+
199+
for ( int i = 0; i < fields().count(); i++ )
200+
{
201+
const QgsField& f = fields()[i];
202+
if ( f.type() == QVariant::Int )
203+
url.addQueryItem( "field", f.name() + ":int" );
204+
else if ( f.type() == QVariant::Double )
205+
url.addQueryItem( "field", f.name() + ":real" );
206+
else if ( f.type() == QVariant::String )
207+
url.addQueryItem( "field", f.name() + ":text" );
208+
}
209+
210+
return url;
211+
}
212+
213+
QString QgsVirtualLayerDefinition::toString() const
214+
{
215+
return QString( toUrl().toEncoded() );
216+
}
217+
218+
void QgsVirtualLayerDefinition::addSource( const QString& name, const QString ref )
219+
{
220+
mSourceLayers.append( SourceLayer( name, ref ) );
221+
}
222+
223+
void QgsVirtualLayerDefinition::addSource( const QString& name, const QString source, const QString& provider, const QString& encoding )
224+
{
225+
mSourceLayers.append( SourceLayer( name, source, provider, encoding ) );
226+
}
227+
228+
bool QgsVirtualLayerDefinition::hasSourceLayer( QString name ) const
229+
{
230+
foreach ( const QgsVirtualLayerDefinition::SourceLayer& l, sourceLayers() )
231+
{
232+
if ( l.name() == name )
233+
{
234+
return true;
235+
}
236+
}
237+
return false;
238+
}
239+
240+
bool QgsVirtualLayerDefinition::hasReferencedLayers() const
241+
{
242+
foreach ( const QgsVirtualLayerDefinition::SourceLayer& l, sourceLayers() )
243+
{
244+
if ( l.isReferenced() )
245+
{
246+
return true;
247+
}
248+
}
249+
return false;
250+
}

‎src/core/qgsvirtuallayerdefinition.h

Lines changed: 166 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,166 @@
1+
/***************************************************************************
2+
qgsvirtuallayerdefinition.h
3+
begin : Feb, 2015
4+
copyright : (C) 2015 Hugo Mercier, Oslandia
5+
email : hugo dot mercier at oslandia dot com
6+
***************************************************************************/
7+
8+
/***************************************************************************
9+
* *
10+
* This program is free software; you can redistribute it and/or modify *
11+
* it under the terms of the GNU General Public License as published by *
12+
* the Free Software Foundation; either version 2 of the License, or *
13+
* (at your option) any later version. *
14+
* *
15+
***************************************************************************/
16+
17+
#ifndef QGSVIRTUALLAYERDEFINITION_H
18+
#define QGSVIRTUALLAYERDEFINITION_H
19+
20+
#include <qgsfield.h>
21+
#include <qgis.h>
22+
23+
/**
24+
* Class to manipulate the definition of a virtual layer
25+
*
26+
* It is used to extract parameters from an initial virtual layer definition as well as
27+
* to store the complete, expanded definition once types have been detected.
28+
*/
29+
class CORE_EXPORT QgsVirtualLayerDefinition
30+
{
31+
public:
32+
/**
33+
* A SourceLayer is either a reference to a live layer in the registry
34+
* or all the parameters needed to load it (provider key, source, etc.)
35+
*/
36+
class CORE_EXPORT SourceLayer
37+
{
38+
public:
39+
//! Constructor variant to build a live layer reference
40+
SourceLayer( const QString& name, const QString& ref ) : mName( name ), mRef( ref ) {}
41+
//! Constructor variant to build a layer with a provider and a source
42+
SourceLayer( const QString& name, const QString& source, const QString& provider, const QString& encoding )
43+
: mName( name ), mSource( source ), mProvider( provider ), mEncoding( encoding ) {}
44+
45+
//! Is it a live layer or not ?
46+
bool isReferenced() const { return !mRef.isEmpty(); }
47+
48+
//! The reference (id) of the live layer
49+
QString reference() const { return mRef; }
50+
51+
//! Name of the layer
52+
QString name() const { return mName; }
53+
54+
//! Provider key
55+
QString provider() const { return mProvider; }
56+
57+
//! The source url used by the provider to build the layer
58+
QString source() const { return mSource; }
59+
60+
//! Optional encoding for the provider
61+
QString encoding() const { return mEncoding; }
62+
63+
private:
64+
QString mName;
65+
QString mSource;
66+
QString mProvider;
67+
QString mRef;
68+
QString mEncoding;
69+
};
70+
71+
//! Constructor with an optional file path
72+
QgsVirtualLayerDefinition( const QString& filePath = "" );
73+
74+
//! Constructor to build a definition from a QUrl
75+
//! The path part of the URL is extracted as well as the following optional keys:
76+
//! layer_ref=layer_id[:name] represents a live layer referenced by its ID. An optional name can be given
77+
//! layer=provider:source[:name[:encoding]] represents a layer given by its provider key, its source url (URL-encoded).
78+
//! An optional name and encoding can be given
79+
//! geometry=column_name[:type:srid] gives the definition of the geometry column.
80+
//! Type can be either a WKB type code or a string (point, linestring, etc.)
81+
//! srid is an integer
82+
//! uid=column_name is the name of a column with unique integer values.
83+
//! nogeometry is a flag to force the layer to be a non-geometry layer
84+
//! query=sql represents the SQL query. Must be URL-encoded
85+
//! field=column_name:[int|real|text] represents a field with its name and its type
86+
static QgsVirtualLayerDefinition fromUrl( const QUrl& url );
87+
88+
//! Convert the definition into a QUrl
89+
QUrl toUrl() const;
90+
91+
//! Convert into a QString that can be read by the virtual layer provider
92+
QString toString() const;
93+
94+
//! Add a live layer source layer
95+
void addSource( const QString& name, const QString ref );
96+
97+
//! Add a layer with a source, a provider and an encoding
98+
void addSource( const QString& name, const QString source, const QString& provider, const QString& encoding = "" );
99+
100+
//! List of source layers
101+
typedef QList<SourceLayer> SourceLayers;
102+
103+
//! Get access to the source layers
104+
const SourceLayers& sourceLayers() const { return mSourceLayers; }
105+
106+
//! Get the SQL query
107+
QString query() const { return mQuery; }
108+
//! Set the SQL query
109+
void setQuery( const QString& query ) { mQuery = query; }
110+
111+
//! Get the file path. May be empty
112+
QString filePath() const { return mFilePath; }
113+
//! Set the file path
114+
void setFilePath( const QString& filePath ) { mFilePath = filePath; }
115+
116+
//! Get the name of the field with unique identifiers
117+
QString uid() const { return mUid; }
118+
//! Set the name of the field with unique identifiers
119+
void setUid( const QString& uid ) { mUid = uid; }
120+
121+
//! Get the name of the geometry field. Empty if no geometry field
122+
QString geometryField() const { return mGeometryField; }
123+
//! Set the name of the geometry field
124+
void setGeometryField( const QString& geometryField ) { mGeometryField = geometryField; }
125+
126+
//! Get the type of the geometry
127+
//! QgsWKBTypes::NoGeometry to hide any geometry
128+
//! QgsWKBTypes::Unknown for unknown types
129+
QgsWKBTypes::Type geometryWkbType() const { return mGeometryWkbType; }
130+
//! Set the type of the geometry
131+
void setGeometryWkbType( QgsWKBTypes::Type t ) { mGeometryWkbType = t; }
132+
133+
//! Get the SRID of the geometry
134+
long geometrySrid() const { return mGeometrySrid; }
135+
//! Set the SRID of the geometry
136+
void setGeometrySrid( long srid ) { mGeometrySrid = srid; }
137+
138+
//! Get field definitions
139+
const QgsFields& fields() const { return mFields; }
140+
//! Set field definitions
141+
void setFields( const QgsFields& fields ) { mFields = fields; }
142+
143+
//! Convenience method to test if a given source layer is part of the definition
144+
bool hasSourceLayer( QString name ) const;
145+
146+
//! Convenience method to test whether the definition has referenced (live) layers
147+
bool hasReferencedLayers() const;
148+
149+
//! Convenient method to test if the geometry is defined (not NoGeometry and not Unknown)
150+
bool hasDefinedGeometry() const
151+
{
152+
return geometryWkbType() != QgsWKBTypes::NoGeometry && geometryWkbType() != QgsWKBTypes::Unknown;
153+
}
154+
155+
private:
156+
SourceLayers mSourceLayers;
157+
QString mQuery;
158+
QString mUid;
159+
QString mGeometryField;
160+
QString mFilePath;
161+
QgsFields mFields;
162+
QgsWKBTypes::Type mGeometryWkbType;
163+
long mGeometrySrid;
164+
};
165+
166+
#endif

‎src/providers/CMakeLists.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ ADD_SUBDIRECTORY(wcs)
1313
ADD_SUBDIRECTORY(gpx)
1414
ADD_SUBDIRECTORY(wfs)
1515
ADD_SUBDIRECTORY(spatialite)
16+
ADD_SUBDIRECTORY(virtual)
1617

1718
IF (WITH_ORACLE)
1819
ADD_SUBDIRECTORY(oracle)

‎src/providers/virtual/CMakeLists.txt

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
1+
2+
########################################################
3+
# Files
4+
5+
QT4_WRAP_CPP(vlayer_provider_MOC_SRCS qgsvirtuallayerprovider.h qgsslottofunction.h
6+
)
7+
8+
QT4_WRAP_UI(vlayer_provider_UI_H qgsvirtuallayersourceselectbase.ui qgsembeddedlayerselect.ui)
9+
10+
SET(QGIS_VLAYER_PROVIDER_SRCS
11+
${vlayer_provider_MOC_SRCS}
12+
qgsvirtuallayerprovider.cpp
13+
qgsvirtuallayerfeatureiterator.cpp
14+
qgsvirtuallayerblob.cpp
15+
qgsvirtuallayersqlitemodule.cpp
16+
qgsvirtuallayersqlitehelper.cpp
17+
qgsvirtuallayerqueryparser.cpp
18+
)
19+
20+
ADD_LIBRARY(virtuallayerprovider MODULE
21+
${QGIS_VLAYER_PROVIDER_SRCS}
22+
)
23+
24+
TARGET_LINK_LIBRARIES( virtuallayerprovider
25+
qgis_core
26+
qgis_gui
27+
${QT_QTCORE_LIBRARY}
28+
${QT_QTGUI_LIBRARY}
29+
${SQLITE3_LIBRARY}
30+
${SPATIALITE_LIBRARY}
31+
)
32+
33+
INCLUDE_DIRECTORIES(
34+
.
35+
../../core
36+
../../core/auth
37+
../../core/geometry
38+
)
39+
INCLUDE_DIRECTORIES(SYSTEM
40+
${POSTGRES_INCLUDE_DIR}
41+
${GEOS_INCLUDE_DIR}
42+
${QSCINTILLA_INCLUDE_DIR}
43+
${QCA_INCLUDE_DIR}
44+
)
45+
INCLUDE_DIRECTORIES(
46+
../../core
47+
../../gui
48+
../../gui/auth
49+
../../ui
50+
${CMAKE_CURRENT_BINARY_DIR}/../../ui
51+
)
52+
53+
INSTALL(TARGETS virtuallayerprovider
54+
RUNTIME DESTINATION ${QGIS_PLUGIN_DIR}
55+
LIBRARY DESTINATION ${QGIS_PLUGIN_DIR}
56+
)

‎src/providers/virtual/qgsslottofunction.h

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
#ifndef QGSSLOT_TO_FUNCTION_H
2+
#define QGSSLOT_TO_FUNCTION_H
3+
4+
#include <QObject>
5+
6+
/**
7+
* This is an helper Qt object used by the SQLite virtual layer module
8+
* in order to connect to the deletion signal of a vector layer,
9+
* since internal classes of the SQLite module cannot derive from QObject
10+
*/
11+
class QgsSlotToFunction : public QObject
12+
{
13+
Q_OBJECT
14+
public:
15+
QgsSlotToFunction() : mCallback( nullptr ), mArg( nullptr ) {}
16+
QgsSlotToFunction( void ( *callback )( void* ), void* arg ) : mCallback( callback ), mArg( arg ) {}
17+
private slots:
18+
void onSignal() { if ( mCallback ) mCallback( mArg ); }
19+
private:
20+
void ( *mCallback )( void* );
21+
void* mArg;
22+
};
23+
24+
#endif
25+

‎src/providers/virtual/qgsvirtuallayerblob.cpp

Lines changed: 220 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,220 @@
1+
/***************************************************************************
2+
qgsvirtuallayerblob.cpp : Functions to manipulate Spatialite geometry blobs
3+
4+
begin : Nov 2015
5+
copyright : (C) 2015 Hugo Mercier, Oslandia
6+
email : hugo dot mercier at oslandia dot com
7+
***************************************************************************/
8+
9+
/***************************************************************************
10+
* *
11+
* This program is free software; you can redistribute it and/or modify *
12+
* it under the terms of the GNU General Public License as published by *
13+
* the Free Software Foundation; either version 2 of the License, or *
14+
* (at your option) any later version. *
15+
* *
16+
***************************************************************************/
17+
18+
#include "qgsvirtuallayerblob.h"
19+
#include <string.h>
20+
21+
SpatialiteBlobHeader::SpatialiteBlobHeader() :
22+
start( 0x00 ), endianness( 0x01 ), end( 0x7C )
23+
{}
24+
25+
void SpatialiteBlobHeader::readFrom( const char* p )
26+
{
27+
// we cannot use directly memcpy( this, p, sizeof(this) ),
28+
// since there may be padding between struct members
29+
memcpy( &start, p, 1 ); p++;
30+
memcpy( &endianness, p, 1 ); p++;
31+
memcpy( &srid, p, 4 ); p += 4;
32+
memcpy( &mbrMinX, p, 8 ); p += 8;
33+
memcpy( &mbrMinY, p, 8 ); p += 8;
34+
memcpy( &mbrMaxX, p, 8 ); p += 8;
35+
memcpy( &mbrMaxY, p, 8 ); p += 8;
36+
memcpy( &end, p, 1 );
37+
}
38+
39+
void SpatialiteBlobHeader::writeTo( char* p ) const
40+
{
41+
// we cannot use directly memcpy( this, p, sizeof(this) ),
42+
// since there may be padding between struct members
43+
memcpy( p, &start, 1 ); p++;
44+
memcpy( p, &endianness, 1 ); p++;
45+
memcpy( p, &srid, 4 ); p += 4;
46+
memcpy( p, &mbrMinX, 8 ); p += 8;
47+
memcpy( p, &mbrMinY, 8 ); p += 8;
48+
memcpy( p, &mbrMaxX, 8 ); p += 8;
49+
memcpy( p, &mbrMaxY, 8 ); p += 8;
50+
memcpy( p, &end, 1 );
51+
}
52+
53+
//
54+
// Convert a QgsGeometry into a Spatialite geometry BLOB
55+
void qgsGeometryToSpatialiteBlob( const QgsGeometry& geom, int32_t srid, char *&blob, size_t& size )
56+
{
57+
const size_t header_len = SpatialiteBlobHeader::length;
58+
59+
const size_t wkb_size = geom.wkbSize();
60+
size = header_len + wkb_size;
61+
blob = new char[size];
62+
63+
char* p = blob;
64+
65+
// write the header
66+
SpatialiteBlobHeader pHeader;
67+
QgsRectangle bbox = const_cast<QgsGeometry&>( geom ).boundingBox(); // boundingBox should be const
68+
pHeader.srid = srid;
69+
pHeader.mbrMinX = bbox.xMinimum();
70+
pHeader.mbrMinY = bbox.yMinimum();
71+
pHeader.mbrMaxX = bbox.xMaximum();
72+
pHeader.mbrMaxY = bbox.yMaximum();
73+
pHeader.writeTo( blob );
74+
75+
p += header_len;
76+
77+
// wkb of the geometry is
78+
// name size value
79+
// endianness 1 01
80+
// type 4 int
81+
82+
// blob geometry = header + wkb[1:] + 'end'
83+
84+
// copy wkb
85+
const unsigned char* wkb = geom.asWkb();
86+
87+
memcpy( p, wkb + 1, wkb_size - 1 );
88+
p += wkb_size - 1;
89+
90+
// end marker
91+
*p = 0xFE;
92+
}
93+
94+
//
95+
// Return the bouding box of a spatialite geometry blob
96+
QgsRectangle spatialiteBlobBbox( const char* blob, size_t size )
97+
{
98+
Q_UNUSED( size );
99+
100+
SpatialiteBlobHeader h;
101+
h.readFrom( blob );
102+
103+
return QgsRectangle( h.mbrMinX, h.mbrMinY, h.mbrMaxX, h.mbrMaxY );
104+
}
105+
106+
void copySpatialiteSingleWkbToQgsGeometry( QgsWKBTypes::Type type, const char* iwkb, char* owkb, uint32_t& osize )
107+
{
108+
int n_dims = QgsWKBTypes::coordDimensions( type );
109+
switch ( QgsWKBTypes::flatType( type ) )
110+
{
111+
case QgsWKBTypes::Point:
112+
memcpy( owkb, iwkb, n_dims*8 );
113+
iwkb += n_dims * 8;
114+
iwkb += n_dims * 8;
115+
osize = n_dims * 8;
116+
break;
117+
case QgsWKBTypes::LineString:
118+
{
119+
uint32_t n_points = *( uint32_t* )iwkb;
120+
memcpy( owkb, iwkb, 4 );
121+
iwkb += 4; owkb += 4;
122+
for ( uint32_t i = 0; i < n_points; i++ )
123+
{
124+
memcpy( owkb, iwkb, n_dims*8 );
125+
iwkb += n_dims * 8;
126+
owkb += n_dims * 8;
127+
}
128+
osize += n_dims * 8 * n_points + 4;
129+
break;
130+
}
131+
case QgsWKBTypes::Polygon:
132+
{
133+
uint32_t n_rings = *( uint32_t* )iwkb;
134+
memcpy( owkb, iwkb, 4 );
135+
iwkb += 4; owkb += 4;
136+
osize = 4;
137+
for ( uint32_t i = 0; i < n_rings; i++ )
138+
{
139+
uint32_t n_points = *( uint32_t* )iwkb;
140+
memcpy( owkb, iwkb, 4 );
141+
iwkb += 4; owkb += 4;
142+
osize += 4;
143+
for ( uint32_t j = 0; j < n_points; j++ )
144+
{
145+
memcpy( owkb, iwkb, n_dims*8 );
146+
iwkb += n_dims * 8;
147+
owkb += n_dims * 8;
148+
osize += n_dims * 8;
149+
}
150+
}
151+
break;
152+
}
153+
default:
154+
break;
155+
}
156+
}
157+
158+
//
159+
// copy the spatialite blob to wkb for qgsgeometry
160+
// the only difference is
161+
// each spatialite sub geometry begins with the byte 0x69 (ENTITY)
162+
// which should be converted to an endianness code
163+
void copySpatialiteCollectionWkbToQgsGeometry( const char* iwkb, char* owkb, uint32_t& osize, int endianness )
164+
{
165+
// copy first byte + type
166+
memcpy( owkb, iwkb, 5 );
167+
168+
// replace 0x69 by the endianness
169+
owkb[0] = endianness;
170+
171+
QgsWKBTypes::Type type = static_cast<QgsWKBTypes::Type>( *( uint32_t* )( iwkb + 1 ) );
172+
173+
if ( QgsWKBTypes::isMultiType( type ) )
174+
{
175+
// multi type
176+
uint32_t n_elements = *( uint32_t* )( iwkb + 5 );
177+
memcpy( owkb + 5, iwkb + 5, 4 );
178+
uint32_t p = 0;
179+
for ( uint32_t i = 0; i < n_elements; i++ )
180+
{
181+
uint32_t rsize = 0;
182+
copySpatialiteCollectionWkbToQgsGeometry( iwkb + 9 + p, owkb + 9 + p, rsize, endianness );
183+
p += rsize;
184+
}
185+
osize = p + 9;
186+
}
187+
else
188+
{
189+
osize = 0;
190+
copySpatialiteSingleWkbToQgsGeometry( type, iwkb + 5, owkb + 5, osize );
191+
osize += 5;
192+
}
193+
}
194+
195+
QgsGeometry spatialiteBlobToQgsGeometry( const char* blob, size_t size )
196+
{
197+
const size_t header_size = SpatialiteBlobHeader::length;
198+
const size_t wkb_size = size - header_size;
199+
char* wkb = new char[wkb_size];
200+
201+
uint32_t osize = 0;
202+
copySpatialiteCollectionWkbToQgsGeometry( blob + header_size - 1, wkb, osize, /*endianness*/blob[1] );
203+
204+
QgsGeometry geom;
205+
geom.fromWkb(( unsigned char* )wkb, wkb_size );
206+
return geom;
207+
}
208+
209+
QPair<QgsWKBTypes::Type, long> spatialiteBlobGeometryType( const char* blob, size_t size )
210+
{
211+
if ( size < SpatialiteBlobHeader::length + 4 ) // the header + the type on 4 bytes
212+
{
213+
return qMakePair( QgsWKBTypes::NoGeometry, long( 0 ) );
214+
}
215+
216+
uint32_t srid = *( uint32_t* )( blob + 2 );
217+
uint32_t type = *( uint32_t* )( blob + SpatialiteBlobHeader::length );
218+
219+
return qMakePair( static_cast<QgsWKBTypes::Type>( type ), long( srid ) );
220+
}

‎src/providers/virtual/qgsvirtuallayerblob.h

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
/***************************************************************************
2+
qgsvirtuallayerblob.h : Functions to manipulate Spatialite geometry blobs
3+
begin : Nov 2015
4+
copyright : (C) 2015 Hugo Mercier, Oslandia
5+
email : hugo dot mercier at oslandia dot com
6+
***************************************************************************/
7+
8+
/***************************************************************************
9+
* *
10+
* This program is free software; you can redistribute it and/or modify *
11+
* it under the terms of the GNU General Public License as published by *
12+
* the Free Software Foundation; either version 2 of the License, or *
13+
* (at your option) any later version. *
14+
* *
15+
***************************************************************************/
16+
17+
#ifndef QGSVIRTUALLAYER_BLOB_H
18+
#define QGSVIRTUALLAYER_BLOB_H
19+
20+
#include <stdint.h>
21+
22+
#include <qgsgeometry.h>
23+
24+
// BLOB header
25+
// name size value
26+
// start 1 00
27+
// endian 1 01
28+
// srid 4 int
29+
// mbr_min_x 8 double
30+
// mbr_min_y 8 double
31+
// mbr_max_x 8 double
32+
// mbr_max_y 8 double
33+
// mbr_end 1 7C
34+
struct SpatialiteBlobHeader
35+
{
36+
unsigned char start;
37+
unsigned char endianness;
38+
uint32_t srid;
39+
double mbrMinX;
40+
double mbrMinY;
41+
double mbrMaxX;
42+
double mbrMaxY;
43+
unsigned char end;
44+
45+
SpatialiteBlobHeader();
46+
47+
static const size_t length = 39;
48+
49+
void readFrom( const char* p );
50+
51+
void writeTo( char* p ) const;
52+
};
53+
54+
//!
55+
//! Convert a QgsGeometry into a Spatialite geometry BLOB
56+
//! The blob will be allocated and must be handled by the caller
57+
void qgsGeometryToSpatialiteBlob( const QgsGeometry& geom, int32_t srid, char *&blob, size_t& size );
58+
59+
//!
60+
//! Return the bouding box of a spatialite geometry blob
61+
QgsRectangle spatialiteBlobBbox( const char* blob, size_t size );
62+
63+
//!
64+
//! Convert a Spatialite geometry BLOB to a QgsGeometry
65+
QgsGeometry spatialiteBlobToQgsGeometry( const char* blob, size_t size );
66+
67+
//!
68+
//! Get geometry type and srid from a spatialite geometry blob
69+
QPair<QgsWKBTypes::Type, long> spatialiteBlobGeometryType( const char* blob, size_t size );
70+
71+
#endif

‎src/providers/virtual/qgsvirtuallayerfeatureiterator.cpp

Lines changed: 236 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,236 @@
1+
/***************************************************************************
2+
qgsvirtuallayerfeatureiterator.cpp
3+
Feature iterator for the virtual layer provider
4+
begin : Nov 2015
5+
copyright : (C) 2015 Hugo Mercier, Oslandia
6+
email : hugo dot mercier at oslandia dot com
7+
***************************************************************************/
8+
9+
/***************************************************************************
10+
* *
11+
* This program is free software; you can redistribute it and/or modify *
12+
* it under the terms of the GNU General Public License as published by *
13+
* the Free Software Foundation; either version 2 of the License, or *
14+
* (at your option) any later version. *
15+
* *
16+
***************************************************************************/
17+
18+
#include <qgsvirtuallayerfeatureiterator.h>
19+
#include <qgsmessagelog.h>
20+
#include <qgsgeometry.h>
21+
#include <stdexcept>
22+
#include "qgsvirtuallayerblob.h"
23+
24+
static QString quotedColumn( QString name )
25+
{
26+
return "\"" + name.replace( "\"", "\"\"" ) + "\"";
27+
}
28+
29+
QgsVirtualLayerFeatureIterator::QgsVirtualLayerFeatureIterator( QgsVirtualLayerFeatureSource* source, bool ownSource, const QgsFeatureRequest& request )
30+
: QgsAbstractFeatureIteratorFromSource<QgsVirtualLayerFeatureSource>( source, ownSource, request )
31+
{
32+
try
33+
{
34+
mSqlite = mSource->provider()->mSqlite.get();
35+
mDefinition = mSource->provider()->mDefinition;
36+
37+
QString tableName = mSource->provider()->mTableName;
38+
39+
QStringList wheres;
40+
QString subset = mSource->provider()->mSubset;
41+
if ( !subset.isNull() )
42+
{
43+
wheres << subset;
44+
}
45+
46+
if ( mDefinition.hasDefinedGeometry() && !request.filterRect().isNull() )
47+
{
48+
bool do_exact = request.flags() & QgsFeatureRequest::ExactIntersect;
49+
QgsRectangle rect( request.filterRect() );
50+
QString mbr = QString( "%1,%2,%3,%4" ).arg( rect.xMinimum() ).arg( rect.yMinimum() ).arg( rect.xMaximum() ).arg( rect.yMaximum() );
51+
wheres << quotedColumn( mDefinition.geometryField() ) + " is not null";
52+
wheres << QString( "%1Intersects(%2,BuildMbr(%3))" )
53+
.arg( do_exact ? "" : "Mbr" )
54+
.arg( quotedColumn( mDefinition.geometryField() ) )
55+
.arg( mbr );
56+
}
57+
else if ( !mDefinition.uid().isNull() && request.filterType() == QgsFeatureRequest::FilterFid )
58+
{
59+
wheres << QString( "%1=%2" )
60+
.arg( quotedColumn( mDefinition.uid() ) )
61+
.arg( request.filterFid() );
62+
}
63+
else if ( !mDefinition.uid().isNull() && request.filterType() == QgsFeatureRequest::FilterFids )
64+
{
65+
QString values = quotedColumn( mDefinition.uid() ) + " IN (";
66+
bool first = true;
67+
foreach ( auto& v, request.filterFids() )
68+
{
69+
if ( !first )
70+
{
71+
values += ",";
72+
}
73+
first = false;
74+
values += QString::number( v );
75+
}
76+
values += ")";
77+
wheres << values;
78+
}
79+
80+
mFields = mSource->provider()->fields();
81+
if ( request.flags() & QgsFeatureRequest::SubsetOfAttributes )
82+
{
83+
84+
// copy only selected fields
85+
foreach ( int idx, request.subsetOfAttributes() )
86+
{
87+
mAttributes << idx;
88+
}
89+
}
90+
else
91+
{
92+
mAttributes = mFields.allAttributesList();
93+
}
94+
95+
QString columns;
96+
{
97+
// the first column is always the uid (or 0)
98+
if ( !mDefinition.uid().isNull() )
99+
{
100+
columns = quotedColumn( mDefinition.uid() );
101+
}
102+
else
103+
{
104+
columns = "0";
105+
}
106+
foreach ( int i, mAttributes )
107+
{
108+
columns += ",";
109+
QString cname = mFields.at( i ).name().toLower();
110+
columns += quotedColumn( cname );
111+
}
112+
}
113+
// the last column is the geometry, if any
114+
if ( !( request.flags() & QgsFeatureRequest::NoGeometry ) && !mDefinition.geometryField().isNull() && mDefinition.geometryField() != "*no*" )
115+
{
116+
columns += "," + quotedColumn( mDefinition.geometryField() );
117+
}
118+
119+
mSqlQuery = "SELECT " + columns + " FROM " + tableName;
120+
if ( !wheres.isEmpty() )
121+
{
122+
mSqlQuery += " WHERE " + wheres.join( " AND " );
123+
}
124+
125+
mQuery.reset( new Sqlite::Query( mSqlite, mSqlQuery ) );
126+
127+
mFid = 0;
128+
}
129+
catch ( std::runtime_error& e )
130+
{
131+
QgsMessageLog::logMessage( e.what(), QObject::tr( "VLayer" ) );
132+
close();
133+
}
134+
}
135+
136+
QgsVirtualLayerFeatureIterator::~QgsVirtualLayerFeatureIterator()
137+
{
138+
close();
139+
}
140+
141+
bool QgsVirtualLayerFeatureIterator::rewind()
142+
{
143+
if ( mClosed )
144+
{
145+
return false;
146+
}
147+
148+
mQuery->reset();
149+
150+
return true;
151+
}
152+
153+
bool QgsVirtualLayerFeatureIterator::close()
154+
{
155+
if ( mClosed )
156+
{
157+
return false;
158+
}
159+
160+
// this call is absolutely needed
161+
iteratorClosed();
162+
163+
mClosed = true;
164+
return true;
165+
}
166+
167+
bool QgsVirtualLayerFeatureIterator::fetchFeature( QgsFeature& feature )
168+
{
169+
if ( mClosed )
170+
{
171+
return false;
172+
}
173+
if ( mQuery->step() != SQLITE_ROW )
174+
{
175+
return false;
176+
}
177+
178+
feature.setFields( mFields, /* init */ true );
179+
180+
if ( mDefinition.uid().isNull() )
181+
{
182+
// no id column => autoincrement
183+
feature.setFeatureId( mFid++ );
184+
}
185+
else
186+
{
187+
// first column: uid
188+
feature.setFeatureId( mQuery->columnInt64( 0 ) );
189+
}
190+
191+
int n = mQuery->columnCount();
192+
int i = 0;
193+
foreach ( int idx, mAttributes )
194+
{
195+
int type = mQuery->columnType( i + 1 );
196+
switch ( type )
197+
{
198+
case SQLITE_INTEGER:
199+
feature.setAttribute( idx, mQuery->columnInt64( i + 1 ) );
200+
break;
201+
case SQLITE_FLOAT:
202+
feature.setAttribute( idx, mQuery->columnDouble( i + 1 ) );
203+
break;
204+
case SQLITE_TEXT:
205+
default:
206+
feature.setAttribute( idx, mQuery->columnText( i + 1 ) );
207+
break;
208+
};
209+
i++;
210+
}
211+
if ( n > mAttributes.size() + 1 )
212+
{
213+
// geometry field
214+
QByteArray blob( mQuery->columnBlob( n - 1 ) );
215+
if ( blob.size() > 0 )
216+
{
217+
feature.setGeometry( spatialiteBlobToQgsGeometry( blob.constData(), blob.size() ) );
218+
}
219+
}
220+
221+
return true;
222+
}
223+
224+
QgsVirtualLayerFeatureSource::QgsVirtualLayerFeatureSource( const QgsVirtualLayerProvider* p ) :
225+
mProvider( p )
226+
{
227+
}
228+
229+
QgsVirtualLayerFeatureSource::~QgsVirtualLayerFeatureSource()
230+
{
231+
}
232+
233+
QgsFeatureIterator QgsVirtualLayerFeatureSource::getFeatures( const QgsFeatureRequest& request )
234+
{
235+
return QgsFeatureIterator( new QgsVirtualLayerFeatureIterator( this, false, request ) );
236+
}

‎src/providers/virtual/qgsvirtuallayerfeatureiterator.h

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
/***************************************************************************
2+
qgsvirtuallayerfeatureiterator.h
3+
Feature iterator for the virtual layer provider
4+
begin : Feb 2015
5+
copyright : (C) 2015 Hugo Mercier, Oslandia
6+
email : hugo dot mercier at oslandia dot com
7+
***************************************************************************/
8+
9+
/***************************************************************************
10+
* *
11+
* This program is free software; you can redistribute it and/or modify *
12+
* it under the terms of the GNU General Public License as published by *
13+
* the Free Software Foundation; either version 2 of the License, or *
14+
* (at your option) any later version. *
15+
* *
16+
***************************************************************************/
17+
#ifndef QGSVIRTUALLAYER_FEATURE_ITERATOR_H
18+
#define QGSVIRTUALLAYER_FEATURE_ITERATOR_H
19+
20+
21+
#include <qgsvirtuallayerprovider.h>
22+
#include <qgsfeatureiterator.h>
23+
24+
class QgsVirtualLayerFeatureSource : public QgsAbstractFeatureSource
25+
{
26+
public:
27+
QgsVirtualLayerFeatureSource( const QgsVirtualLayerProvider* p );
28+
~QgsVirtualLayerFeatureSource();
29+
30+
virtual QgsFeatureIterator getFeatures( const QgsFeatureRequest& request ) override;
31+
32+
const QgsVirtualLayerProvider* provider() const { return mProvider; }
33+
private:
34+
const QgsVirtualLayerProvider* mProvider;
35+
};
36+
37+
class QgsVirtualLayerFeatureIterator : public QgsAbstractFeatureIteratorFromSource<QgsVirtualLayerFeatureSource>
38+
{
39+
public:
40+
QgsVirtualLayerFeatureIterator( QgsVirtualLayerFeatureSource* source, bool ownSource, const QgsFeatureRequest& request );
41+
~QgsVirtualLayerFeatureIterator();
42+
43+
//! reset the iterator to the starting position
44+
virtual bool rewind() override;
45+
46+
//! end of iterating: free the resources / lock
47+
virtual bool close() override;
48+
49+
protected:
50+
51+
//! fetch next feature, return true on success
52+
virtual bool fetchFeature( QgsFeature& feature ) override;
53+
54+
QScopedPointer<Sqlite::Query> mQuery;
55+
56+
QgsFeatureId mFid;
57+
58+
QString mPath;
59+
sqlite3* mSqlite;
60+
QgsVirtualLayerDefinition mDefinition;
61+
QgsFields mFields;
62+
63+
QString mSqlQuery;
64+
65+
// Index of the id column, -1 if none
66+
int mUidColumn;
67+
68+
QgsAttributeList mAttributes;
69+
};
70+
71+
#endif

‎src/providers/virtual/qgsvirtuallayerprovider.cpp

Lines changed: 636 additions & 0 deletions
Large diffs are not rendered by default.

‎src/providers/virtual/qgsvirtuallayerprovider.h

Lines changed: 146 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,146 @@
1+
/***************************************************************************
2+
qgsvirtuallayerprovider.cpp Virtual layer data provider
3+
begin : Jan, 2015
4+
copyright : (C) 2015 Hugo Mercier, Oslandia
5+
email : hugo dot mercier at oslandia dot com
6+
***************************************************************************/
7+
8+
/***************************************************************************
9+
* *
10+
* This program is free software; you can redistribute it and/or modify *
11+
* it under the terms of the GNU General Public License as published by *
12+
* the Free Software Foundation; either version 2 of the License, or *
13+
* (at your option) any later version. *
14+
* *
15+
***************************************************************************/
16+
17+
#ifndef QGSVIRTUAL_LAYER_PROVIDER_H
18+
#define QGSVIRTUAL_LAYER_PROVIDER_H
19+
20+
#include <qgsvectordataprovider.h>
21+
22+
#include "qgscoordinatereferencesystem.h"
23+
#include "qgsvirtuallayerdefinition.h"
24+
#include "qgsvirtuallayersqlitehelper.h"
25+
26+
class QgsVirtualLayerFeatureIterator;
27+
28+
class QgsVirtualLayerProvider: public QgsVectorDataProvider
29+
{
30+
Q_OBJECT
31+
public:
32+
33+
/**
34+
* Constructor of the vector provider
35+
* @param uri uniform resource locator (URI) for a dataset
36+
*/
37+
QgsVirtualLayerProvider( QString const &uri = "" );
38+
39+
/** Destructor */
40+
virtual ~QgsVirtualLayerProvider();
41+
42+
virtual QgsAbstractFeatureSource* featureSource() const override;
43+
44+
/** Returns the permanent storage type for this layer as a friendly name */
45+
virtual QString storageType() const override;
46+
47+
/** Get the QgsCoordinateReferenceSystem for this layer */
48+
virtual QgsCoordinateReferenceSystem crs() override;
49+
50+
/** Access features through an iterator */
51+
virtual QgsFeatureIterator getFeatures( const QgsFeatureRequest& request ) override;
52+
53+
/** Get the feature geometry type */
54+
QGis::WkbType geometryType() const override;
55+
56+
/** Get the number of features in the layer */
57+
long featureCount() const override;
58+
59+
/** Return the extent for this data layer */
60+
virtual QgsRectangle extent() override;
61+
62+
/** Accessor for sql where clause used to limit dataset */
63+
virtual QString subsetString() override;
64+
65+
/** Set the subset string used to create a subset of features in the layer (WHERE clause) */
66+
virtual bool setSubsetString( const QString& subset, bool updateFeatureCount = true ) override;
67+
68+
/** Provider supports setting of subset strings */
69+
virtual bool supportsSubsetString() override { return true; }
70+
71+
/**
72+
* Get the field information for the layer
73+
* @return vector of QgsField objects
74+
*/
75+
const QgsFields & fields() const override;
76+
77+
/** Returns true if layer is valid */
78+
bool isValid() override;
79+
80+
/** Returns a bitmask containing the supported capabilities*/
81+
int capabilities() const override;
82+
83+
/** Return the provider name */
84+
QString name() const override;
85+
86+
/** Return description */
87+
QString description() const override;
88+
89+
/** Return list of indexes of fields that make up the primary key */
90+
QgsAttributeList pkAttributeIndexes() override;
91+
92+
private:
93+
94+
// file on disk
95+
QString mPath;
96+
97+
QgsScopedSqlite mSqlite;
98+
99+
// underlying vector layers
100+
struct SourceLayer
101+
{
102+
SourceLayer(): layer( 0 ) {}
103+
SourceLayer( QgsVectorLayer *l, const QString& n = "" ) : layer( l ), name( n ) {}
104+
SourceLayer( const QString& p, const QString& s, const QString& n, const QString& e = "UTF-8" ) :
105+
layer( 0 ), name( n ), source( s ), provider( p ), encoding( e ) {}
106+
// non-null if it refers to a live layer
107+
QgsVectorLayer* layer;
108+
QString name;
109+
// non-empty if it is an embedded layer
110+
QString source;
111+
QString provider;
112+
QString encoding;
113+
};
114+
typedef QVector<SourceLayer> SourceLayers;
115+
SourceLayers mLayers;
116+
117+
118+
bool mValid;
119+
120+
QString mTableName;
121+
122+
QgsCoordinateReferenceSystem mCrs;
123+
124+
QgsVirtualLayerDefinition mDefinition;
125+
126+
QString mSubset;
127+
128+
void resetSqlite();
129+
130+
mutable bool mCachedStatistics;
131+
mutable qint64 mFeatureCount;
132+
mutable QgsRectangle mExtent;
133+
134+
void updateStatistics() const;
135+
136+
bool openIt();
137+
bool createIt();
138+
bool loadSourceLayers();
139+
140+
friend class QgsVirtualLayerFeatureIterator;
141+
142+
private slots:
143+
void invalidateStatistics();
144+
};
145+
146+
#endif

‎src/providers/virtual/qgsvirtuallayerqueryparser.cpp

Lines changed: 274 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,274 @@
1+
#include "qgsvirtuallayerqueryparser.h"
2+
#include "qgsvirtuallayersqlitehelper.h"
3+
#include "qgsvirtuallayerblob.h"
4+
5+
#include <QRegExp>
6+
7+
namespace QgsVirtualLayerQueryParser
8+
{
9+
10+
QStringList referencedTables( const QString& query )
11+
{
12+
QStringList tables;
13+
14+
//
15+
// open an empty in-memory sqlite database and execute the query
16+
// sqlite will return an error for each missing table
17+
// this way we know the list of tables referenced by the query
18+
QgsScopedSqlite db( ":memory:", /*withExtension=*/ false );
19+
20+
const QString noSuchError = "no such table: ";
21+
22+
while ( true )
23+
{
24+
char *errMsg = 0;
25+
int r = sqlite3_exec( db.get(), query.toLocal8Bit().constData(), NULL, NULL, &errMsg );
26+
QString err = errMsg;
27+
if ( r && err.startsWith( noSuchError ) )
28+
{
29+
QString tableName = err.mid( noSuchError.size() );
30+
tables << tableName;
31+
32+
// create a dummy table to skip this error
33+
QString createStr = QString( "CREATE TABLE \"%1\" (id int)" ).arg( tableName.replace( "\"", "\"\"" ) );
34+
sqlite3_exec( db.get(), createStr.toLocal8Bit().constData(), NULL, NULL, NULL );
35+
}
36+
else
37+
{
38+
// no error, or another error
39+
break;
40+
}
41+
}
42+
return tables;
43+
}
44+
45+
QMap<QString, ColumnDef> columnCommentDefinitions( const QString& query )
46+
{
47+
QMap<QString, ColumnDef> defs;
48+
49+
// look for special comments in SQL
50+
// a column name followed by /*:type*/
51+
QRegExp rx( "([a-zA-Z_\x80-\xFF][a-zA-Z0-9_\x80-\xFF]*)\\s*/\\*:(int|real|text|((?:multi)?(?:point|linestring|polygon)):(\\d+))\\s*\\*/", Qt::CaseInsensitive );
52+
int pos = 0;
53+
54+
while (( pos = rx.indexIn( query, pos ) ) != -1 )
55+
{
56+
QString column = rx.cap( 1 );
57+
QString type = rx.cap( 2 );
58+
ColumnDef def;
59+
def.setName( column );
60+
if ( type == "int" )
61+
def.setScalarType( QVariant::Int );
62+
else if ( type == "real" )
63+
def.setScalarType( QVariant::Double );
64+
else if ( type == "text" )
65+
def.setScalarType( QVariant::String );
66+
else
67+
{
68+
// there should be 2 more captures
69+
def.setGeometry( QgsWKBTypes::parseType( rx.cap( 3 ) ) );
70+
def.setSrid( static_cast<QgsWKBTypes::Type>( rx.cap( 4 ).toLong() ) );
71+
}
72+
defs[column] = def;
73+
74+
pos += rx.matchedLength();
75+
}
76+
return defs;
77+
}
78+
79+
bool isValidColumnName( const QString& columnName )
80+
{
81+
// identifier name with possible accents
82+
static QRegExp columnNameRx( "[a-zA-Z_\x80-\xFF][a-zA-Z0-9_\x80-\xFF]*" );
83+
84+
return columnNameRx.exactMatch( columnName );
85+
}
86+
87+
// set the type of the column type, given its text representation
88+
void setColumnDefType( const QString& columnType, ColumnDef& d )
89+
{
90+
// geometry type
91+
QRegExp geometryTypeRx( "\\(([0-9]+),([0-9]+)\\)" );
92+
93+
// see qgsvirtuallayersqlitemodule for possible declared types
94+
// the type returned by PRAGMA table_info will be either
95+
// the type declared by one of the virtual tables
96+
// or null
97+
if ( columnType == "int" )
98+
d.setScalarType( QVariant::Int );
99+
else if ( columnType == "real" )
100+
d.setScalarType( QVariant::Double );
101+
else if ( columnType == "text" )
102+
d.setScalarType( QVariant::String );
103+
else if ( columnType.startsWith( "geometry" ) )
104+
{
105+
// parse the geometry type and srid
106+
// geometry(type,srid)
107+
int pos = geometryTypeRx.indexIn( columnType, 0 );
108+
if ( pos != -1 )
109+
{
110+
QgsWKBTypes::Type type = static_cast<QgsWKBTypes::Type>( geometryTypeRx.cap( 1 ).toInt() );
111+
long srid = geometryTypeRx.cap( 2 ).toLong();
112+
d.setGeometry( type );
113+
d.setSrid( srid );
114+
}
115+
}
116+
}
117+
118+
ColumnDef geometryDefinitionFromVirtualTable( sqlite3* db, const QString& tableName )
119+
{
120+
ColumnDef d;
121+
Sqlite::Query q( db, QString( "PRAGMA table_info(%1)" ).arg( tableName ) );
122+
while ( q.step() == SQLITE_ROW )
123+
{
124+
QString columnName = q.columnText( 1 );
125+
QString columnType = q.columnText( 2 );
126+
if ( ! columnType.startsWith( "geometry" ) )
127+
continue;
128+
129+
d.setName( columnName );
130+
131+
setColumnDefType( columnType, d );
132+
133+
break;
134+
}
135+
return d;
136+
}
137+
138+
TableDef columnDefinitionsFromQuery( sqlite3* db, const QString& query )
139+
{
140+
// get column types defined by comments
141+
QMap<QString, ColumnDef> definedColumns = columnCommentDefinitions( query );
142+
143+
// create a view to detect column names and types, using PRAGMA table_info
144+
QString viewStr = "CREATE TEMP VIEW _tview AS " + query;
145+
Sqlite::Query::exec( db, viewStr );
146+
147+
QStringList columns;
148+
bool hasInvalidName = false;
149+
QVector<int> undefinedColumns;
150+
TableDef tableDef;
151+
{
152+
Sqlite::Query q( db, "PRAGMA table_info(_tview)" );
153+
int columnNumber = 0;
154+
while ( q.step() == SQLITE_ROW )
155+
{
156+
QString columnName = q.columnText( 1 );
157+
158+
if ( !isValidColumnName( columnName ) )
159+
{
160+
std::cout << "Invalid name: " << columnName.toLocal8Bit().constData() << std::endl;
161+
hasInvalidName = true;
162+
163+
// add an unnamed column
164+
ColumnDef d;
165+
tableDef << d;
166+
break;
167+
}
168+
169+
columns << columnName;
170+
171+
QString columnType = q.columnText( 2 );
172+
173+
// column type defined by comments
174+
if ( definedColumns.contains( columnName ) )
175+
{
176+
tableDef << definedColumns[columnName];
177+
}
178+
else
179+
{
180+
ColumnDef d;
181+
d.setName( columnName );
182+
183+
setColumnDefType( columnType, d );
184+
185+
if ( d.scalarType() == QVariant::Invalid )
186+
{
187+
// else no type is defined
188+
undefinedColumns << columnNumber;
189+
}
190+
191+
tableDef << d;
192+
}
193+
194+
columnNumber++;
195+
}
196+
}
197+
198+
if ( hasInvalidName || undefinedColumns.size() == 0 )
199+
return tableDef;
200+
201+
// get the first row to introspect types
202+
{
203+
QString qs = "SELECT ";
204+
for ( int i = 0; i < undefinedColumns.size(); i++ )
205+
{
206+
qs += columns[undefinedColumns[i]];
207+
if ( i != undefinedColumns.size() - 1 )
208+
qs += ", ";
209+
}
210+
qs += " FROM _tview LIMIT 1";
211+
std::cout << qs.toLocal8Bit().constData() << std::endl;
212+
213+
Sqlite::Query q( db, qs );
214+
if ( q.step() == SQLITE_ROW )
215+
{
216+
for ( int i = 0; i < undefinedColumns.size(); i++ )
217+
{
218+
int colIdx = undefinedColumns[i];
219+
int type = q.columnType( i );
220+
switch ( type )
221+
{
222+
case SQLITE_INTEGER:
223+
tableDef[colIdx].setScalarType( QVariant::Int );
224+
break;
225+
case SQLITE_FLOAT:
226+
tableDef[colIdx].setScalarType( QVariant::Double );
227+
break;
228+
case SQLITE_BLOB:
229+
{
230+
// might be a geometry, parse the type
231+
QByteArray ba( q.columnBlob( i ) );
232+
QPair<QgsWKBTypes::Type, long> p( spatialiteBlobGeometryType( ba.constData(), ba.size() ) );
233+
if ( p.first != QgsWKBTypes::NoGeometry )
234+
{
235+
tableDef[colIdx].setGeometry( p.first );
236+
tableDef[colIdx].setSrid( p.second );
237+
}
238+
else
239+
{
240+
// interpret it as a string
241+
tableDef[colIdx].setScalarType( QVariant::String );
242+
}
243+
}
244+
break;
245+
case SQLITE_TEXT:
246+
default:
247+
tableDef[colIdx].setScalarType( QVariant::String );
248+
break;
249+
};
250+
}
251+
}
252+
}
253+
return tableDef;
254+
}
255+
256+
TableDef tableDefinitionFromVirtualTable( sqlite3* db, const QString& tableName )
257+
{
258+
TableDef td;
259+
Sqlite::Query q( db, QString( "PRAGMA table_info(%1)" ).arg( tableName ) );
260+
while ( q.step() == SQLITE_ROW )
261+
{
262+
ColumnDef d;
263+
QString columnName = q.columnText( 1 );
264+
QString columnType = q.columnText( 2 );
265+
266+
d.setName( columnName );
267+
setColumnDefType( columnType, d );
268+
269+
td << d;
270+
}
271+
return td;
272+
}
273+
274+
} // namespace

‎src/providers/virtual/qgsvirtuallayerqueryparser.h

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
#ifndef QGSVIRTUALLAYER_QUERY_PARSER_H
2+
#define QGSVIRTUALLAYER_QUERY_PARSER_H
3+
4+
#include <qgis.h>
5+
#include <qgswkbtypes.h>
6+
#include <qgsvectorlayer.h>
7+
8+
namespace QgsVirtualLayerQueryParser
9+
{
10+
11+
//!
12+
//! Return the list of tables referenced in the SQL query
13+
QStringList referencedTables( const QString& q );
14+
15+
/**
16+
* Type used to define a column
17+
*
18+
* It can hold a name and a type.
19+
* The type can be a 'scalar' type (int, double, string) or a geometry type (WKB) and an SRID
20+
*/
21+
class ColumnDef
22+
{
23+
public:
24+
ColumnDef()
25+
: mType( QVariant::Invalid ), mWkbType( QgsWKBTypes::Unknown ), mSrid( -1 )
26+
{}
27+
ColumnDef( const QString& name, QgsWKBTypes::Type aWkbType, long aSrid )
28+
: mName( name ), mType( QVariant::UserType ), mWkbType( aWkbType ), mSrid( aSrid )
29+
{}
30+
ColumnDef( const QString& name, QVariant::Type aType )
31+
: mName( name ), mType( aType ), mWkbType( QgsWKBTypes::NoGeometry ), mSrid( -1 )
32+
{}
33+
34+
QString name() const { return mName; }
35+
void setName( QString name ) { mName = name; }
36+
37+
bool isGeometry() const { return mType == QVariant::UserType; }
38+
void setGeometry( QgsWKBTypes::Type wkbType ) { mType = QVariant::UserType; mWkbType = wkbType; }
39+
long srid() const { return mSrid; }
40+
void setSrid( long srid ) { mSrid = srid; }
41+
42+
void setScalarType( QVariant::Type t ) { mType = t; mWkbType = QgsWKBTypes::NoGeometry; }
43+
QVariant::Type scalarType() const { return mType; }
44+
QgsWKBTypes::Type wkbType() const { return mWkbType; }
45+
46+
private:
47+
QString mName;
48+
QVariant::Type mType;
49+
QgsWKBTypes::Type mWkbType;
50+
long mSrid;
51+
};
52+
53+
//!
54+
//! Type used by the parser to type a query. It is slightly different from a QgsVirtualLayerDefinition since more than one geometry column can be represented
55+
typedef QList<ColumnDef> TableDef;
56+
57+
//! Get the column names and types that can be deduced from the query, using SQLite introspection
58+
//! Special comments can also be used in the query to type columns
59+
//! Comments should be set after the name of the column and are introduced by "/*:"
60+
//! For instance 'SELECT t+1 /*:int*/ FROM table' will type the column 't' as integer
61+
//! A geometry column can also be set by specifying a type and an SRID
62+
//! For instance 'SELECT t, GeomFromText('POINT(0 0)',4326) as geom /*:point:4326*/
63+
TableDef columnDefinitionsFromQuery( sqlite3* db, const QString& query );
64+
65+
//! Get the column types of a virtual table
66+
TableDef tableDefinitionFromVirtualTable( sqlite3* db, const QString& tableName );
67+
68+
}
69+
70+
#endif

‎src/providers/virtual/qgsvirtuallayersqlitehelper.cpp

Lines changed: 189 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,189 @@
1+
/***************************************************************************
2+
qgsvirtuallayersqlitehelper.cpp
3+
begin : December 2015
4+
copyright : (C) 2015 Hugo Mercier, Oslandia
5+
email : hugo dot mercier at oslandia dot com
6+
***************************************************************************/
7+
8+
/***************************************************************************
9+
* *
10+
* This program is free software; you can redistribute it and/or modify *
11+
* it under the terms of the GNU General Public License as published by *
12+
* the Free Software Foundation; either version 2 of the License, or *
13+
* (at your option) any later version. *
14+
* *
15+
***************************************************************************/
16+
17+
#include <QString>
18+
19+
#include <stdexcept>
20+
21+
#include "qgsvirtuallayersqlitehelper.h"
22+
23+
QgsScopedSqlite::QgsScopedSqlite( const QString& path, bool withExtension )
24+
{
25+
if ( withExtension )
26+
{
27+
// register a statically-linked function as extension
28+
// for all future database connection
29+
sqlite3_auto_extension(( void( * )() )qgsvlayer_module_init );
30+
}
31+
int r;
32+
r = sqlite3_open( path.toLocal8Bit().constData(), &db_ );
33+
if ( withExtension )
34+
{
35+
// reset the automatic extensions
36+
sqlite3_reset_auto_extension();
37+
}
38+
39+
if ( r )
40+
{
41+
throw std::runtime_error( sqlite3_errmsg( db_ ) );
42+
}
43+
// enable extended result codes
44+
sqlite3_extended_result_codes( db_, 1 );
45+
}
46+
47+
QgsScopedSqlite::QgsScopedSqlite( QgsScopedSqlite& other )
48+
{
49+
db_ = other.db_;
50+
other.db_ = 0;
51+
}
52+
53+
QgsScopedSqlite& QgsScopedSqlite::operator=( QgsScopedSqlite & other )
54+
{
55+
reset( other.release() );
56+
return *this;
57+
}
58+
59+
QgsScopedSqlite::~QgsScopedSqlite()
60+
{
61+
close_();
62+
}
63+
64+
sqlite3* QgsScopedSqlite::get() const { return db_; }
65+
66+
sqlite3* QgsScopedSqlite::release()
67+
{
68+
sqlite3* pp = db_;
69+
db_ = 0;
70+
return pp;
71+
}
72+
73+
void QgsScopedSqlite::reset( sqlite3* db )
74+
{
75+
close_();
76+
db_ = db;
77+
}
78+
79+
void QgsScopedSqlite::close_()
80+
{
81+
if ( db_ )
82+
sqlite3_close( db_ );
83+
}
84+
85+
namespace Sqlite
86+
{
87+
Query::Query( sqlite3* db, const QString& q ) : db_( db ), nBind_( 1 )
88+
{
89+
QByteArray ba( q.toLocal8Bit() );
90+
int r = sqlite3_prepare_v2( db, ba.constData(), ba.size(), &stmt_, NULL );
91+
if ( r )
92+
{
93+
QString err = QString( "Query preparation error on %1" ).arg( q );
94+
throw std::runtime_error( err.toLocal8Bit().constData() );
95+
}
96+
}
97+
98+
Query::~Query()
99+
{
100+
sqlite3_finalize( stmt_ );
101+
}
102+
103+
int Query::step() { return sqlite3_step( stmt_ ); }
104+
105+
Query& Query::bind( const QString& str, int idx )
106+
{
107+
QByteArray ba( str.toLocal8Bit() );
108+
int r = sqlite3_bind_text( stmt_, idx, ba.constData(), ba.size(), SQLITE_TRANSIENT );
109+
if ( r )
110+
{
111+
throw std::runtime_error( sqlite3_errmsg( db_ ) );
112+
}
113+
return *this;
114+
}
115+
116+
Query& Query::bind( const QString& str )
117+
{
118+
return bind( str, nBind_++ );
119+
}
120+
121+
void Query::exec( sqlite3* db, const QString& sql )
122+
{
123+
char *errMsg = 0;
124+
int r = sqlite3_exec( db, sql.toLocal8Bit().constData(), NULL, NULL, &errMsg );
125+
if ( r )
126+
{
127+
QString err = QString( "Query execution error on %1: %2 - %3" ).arg( sql ).arg( r ).arg( errMsg );
128+
throw std::runtime_error( err.toLocal8Bit().constData() );
129+
}
130+
}
131+
132+
void Query::reset()
133+
{
134+
int r = sqlite3_reset( stmt_ );
135+
if ( r )
136+
{
137+
throw std::runtime_error( sqlite3_errmsg( db_ ) );
138+
}
139+
nBind_ = 1;
140+
}
141+
142+
int Query::columnCount() const
143+
{
144+
return sqlite3_column_count( stmt_ );
145+
}
146+
147+
QString Query::columnName( int i ) const
148+
{
149+
return QString( sqlite3_column_name( stmt_, i ) );
150+
}
151+
152+
int Query::columnType( int i ) const
153+
{
154+
return sqlite3_column_type( stmt_, i );
155+
}
156+
157+
int Query::columnInt( int i ) const
158+
{
159+
return sqlite3_column_int( stmt_, i );
160+
}
161+
162+
qint64 Query::columnInt64( int i ) const
163+
{
164+
return sqlite3_column_int64( stmt_, i );
165+
}
166+
167+
double Query::columnDouble( int i ) const
168+
{
169+
return sqlite3_column_double( stmt_, i );
170+
}
171+
172+
QString Query::columnText( int i ) const
173+
{
174+
int size = sqlite3_column_bytes( stmt_, i );
175+
const char* str = ( const char* )sqlite3_column_text( stmt_, i );
176+
return QString::fromUtf8( str, size );
177+
}
178+
179+
QByteArray Query::columnBlob( int i ) const
180+
{
181+
int size = sqlite3_column_bytes( stmt_, i );
182+
const char* data = ( const char* )sqlite3_column_blob( stmt_, i );
183+
// data is not copied. QByteArray is just here a augmented pointer
184+
return QByteArray::fromRawData( data, size );
185+
}
186+
187+
sqlite3_stmt* Query::stmt() { return stmt_; }
188+
189+
}

‎src/providers/virtual/qgsvirtuallayersqlitehelper.h

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
/***************************************************************************
2+
qgsvirtuallayersqlitehelper.h
3+
begin : December 2015
4+
copyright : (C) 2015 Hugo Mercier, Oslandia
5+
email : hugo dot mercier at oslandia dot com
6+
***************************************************************************/
7+
8+
/***************************************************************************
9+
* *
10+
* This program is free software; you can redistribute it and/or modify *
11+
* it under the terms of the GNU General Public License as published by *
12+
* the Free Software Foundation; either version 2 of the License, or *
13+
* (at your option) any later version. *
14+
* *
15+
***************************************************************************/
16+
17+
#ifndef QGSVIRTUALLAYER_SQLITE_UTILS_H
18+
#define QGSVIRTUALLAYER_SQLITE_UTILS_H
19+
20+
extern "C"
21+
{
22+
#include <sqlite3.h>
23+
24+
int qgsvlayer_module_init( sqlite3 *db,
25+
char **pzErrMsg,
26+
void * unused /*const sqlite3_api_routines *pApi*/ );
27+
28+
}
29+
30+
// RAII class for sqlite3*
31+
// Similar to std::unique_ptr
32+
class QgsScopedSqlite
33+
{
34+
public:
35+
QgsScopedSqlite() : db_( 0 ) {}
36+
37+
explicit QgsScopedSqlite( const QString& path, bool withExtension = true );
38+
39+
QgsScopedSqlite( QgsScopedSqlite& other );
40+
QgsScopedSqlite& operator=( QgsScopedSqlite& other );
41+
~QgsScopedSqlite();
42+
43+
sqlite3* get() const;
44+
sqlite3* release();
45+
void reset( sqlite3* db );
46+
47+
private:
48+
sqlite3* db_;
49+
50+
void close_();
51+
};
52+
53+
namespace Sqlite
54+
{
55+
struct Query
56+
{
57+
Query( sqlite3* db, const QString& q );
58+
~Query();
59+
60+
int step();
61+
62+
Query& bind( const QString& str, int idx );
63+
Query& bind( const QString& str );
64+
65+
static void exec( sqlite3* db, const QString& sql );
66+
67+
void reset();
68+
69+
int columnCount() const;
70+
71+
QString columnName( int i ) const;
72+
73+
int columnType( int i ) const;
74+
75+
int columnInt( int i ) const;
76+
77+
qint64 columnInt64( int i ) const;
78+
79+
double columnDouble( int i ) const;
80+
81+
QString columnText( int i ) const;
82+
83+
QByteArray columnBlob( int i ) const;
84+
85+
sqlite3_stmt* stmt();
86+
87+
private:
88+
sqlite3* db_;
89+
sqlite3_stmt* stmt_;
90+
int nBind_;
91+
};
92+
}
93+
94+
#endif

0 commit comments

Comments
 (0)
Please sign in to comment.