Skip to content

Commit 9e14f09

Browse files
author
Hugo Mercier
committedDec 18, 2015
Add a plugin to DB Manager to support virtual layers
·
ltr-3_40archive/master_2
1 parent e60712e commit 9e14f09

File tree

10 files changed

+1169
-0
lines changed

10 files changed

+1169
-0
lines changed
 

‎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

0 commit comments

Comments
 (0)
Please sign in to comment.