Skip to content

Commit c1cdfe8

Browse files
committedJul 20, 2016
[DB Manager] Add the ability to update SQL Layer
1 parent cd33ccf commit c1cdfe8

File tree

4 files changed

+985
-0
lines changed

4 files changed

+985
-0
lines changed
 

‎python/plugins/db_manager/db_manager.py

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -199,6 +199,15 @@ def runSqlWindow(self):
199199
self.tabs.setCurrentIndex(index)
200200
query.nameChanged.connect(functools.partial(self.update_query_tab_name, index, dbname))
201201

202+
def runSqlLayerWindow(self, layer):
203+
from dlg_sql_layer_window import DlgSqlLayerWindow
204+
query = DlgSqlLayerWindow(self.iface, layer, self)
205+
lname = layer.name()
206+
tabname = self.tr("Layer") + u" (%s)" % lname
207+
index = self.tabs.addTab(query, tabname)
208+
#self.tabs.setTabIcon(index, db.connection().icon())
209+
self.tabs.setCurrentIndex(index)
210+
202211
def update_query_tab_name(self, index, dbname, queryname):
203212
if not queryname:
204213
queryname = self.tr("Query")

‎python/plugins/db_manager/db_manager_plugin.py

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,9 @@
2424
from qgis.PyQt.QtWidgets import QAction, QApplication
2525
from qgis.PyQt.QtGui import QIcon
2626

27+
from qgis.core import QgsMapLayerRegistry, QgsMapLayer, QgsDataSourceURI
28+
import re
29+
2730
from . import resources_rc # NOQA
2831

2932

@@ -48,6 +51,15 @@ def initGui(self):
4851
else:
4952
self.iface.addPluginToMenu(QApplication.translate("DBManagerPlugin", "DB Manager"), self.action)
5053

54+
self.layerAction = QAction(QIcon(":/db_manager/icon"), QApplication.translate("DBManagerPlugin", "Update Sql Layer"),
55+
self.iface.mainWindow())
56+
self.layerAction.setObjectName("dbManagerUpdateSqlLayer")
57+
QObject.connect(self.layerAction, SIGNAL("triggered()"), self.onUpdateSqlLayer)
58+
self.iface.legendInterface().addLegendLayerAction(self.layerAction, "", "dbManagerUpdateSqlLayer", QgsMapLayer.VectorLayer, False)
59+
for l in QgsMapLayerRegistry.instance().mapLayers().values():
60+
self.onLayerWasAdded(l)
61+
QgsMapLayerRegistry.instance().layerWasAdded.connect(self.onLayerWasAdded)
62+
5163
def unload(self):
5264
# Remove the plugin menu item and icon
5365
if hasattr(self.iface, 'removePluginDatabaseMenu'):
@@ -59,9 +71,36 @@ def unload(self):
5971
else:
6072
self.iface.removeToolBarIcon(self.action)
6173

74+
self.iface.legendInterface().removeLegendLayerAction(self.layerAction)
75+
QgsMapLayerRegistry.instance().layerWasAdded.disconnect(self.onLayerWasAdded)
76+
6277
if self.dlg is not None:
6378
self.dlg.close()
6479

80+
def onLayerWasAdded(self, aMapLayer):
81+
if aMapLayer.dataProvider().name() in ['postgres', 'spatialite', 'oracle']:
82+
uri = QgsDataSourceURI(aMapLayer.source())
83+
if re.search('^\(SELECT .+ FROM .+\)$', uri.table(), re.S):
84+
self.iface.legendInterface().addLegendLayerActionForLayer(self.layerAction, aMapLayer)
85+
# virtual has QUrl source
86+
# url = QUrl(QUrl.fromPercentEncoding(l.source()))
87+
# url.queryItemValue('query')
88+
# url.queryItemValue('uid')
89+
# url.queryItemValue('geometry')
90+
91+
def onUpdateSqlLayer(self):
92+
l = self.iface.legendInterface().currentLayer()
93+
if l.dataProvider().name() in ['postgres', 'spatialite', 'oracle']:
94+
uri = QgsDataSourceURI(l.source())
95+
if re.search('^\(SELECT .+ FROM .+\)$', uri.table(), re.S):
96+
self.run()
97+
self.dlg.runSqlLayerWindow(l)
98+
# virtual has QUrl source
99+
# url = QUrl(QUrl.fromPercentEncoding(l.source()))
100+
# url.queryItemValue('query')
101+
# url.queryItemValue('uid')
102+
# url.queryItemValue('geometry')
103+
65104
def run(self):
66105
# keep opened only one instance
67106
if self.dlg is None:
Lines changed: 530 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,530 @@
1+
# -*- coding: utf-8 -*-
2+
3+
"""
4+
/***************************************************************************
5+
Name : DB Manager
6+
Description : Database manager plugin for QGIS
7+
Date : May 23, 2011
8+
copyright : (C) 2011 by Giuseppe Sucameli
9+
email : brush.tyler@gmail.com
10+
11+
The content of this file is based on
12+
- PG_Manager by Martin Dobias (GPLv2 license)
13+
***************************************************************************/
14+
15+
/***************************************************************************
16+
* *
17+
* This program is free software; you can redistribute it and/or modify *
18+
* it under the terms of the GNU General Public License as published by *
19+
* the Free Software Foundation; either version 2 of the License, or *
20+
* (at your option) any later version. *
21+
* *
22+
***************************************************************************/
23+
"""
24+
25+
from PyQt4.QtCore import Qt, QObject, QSettings, QByteArray, SIGNAL, pyqtSignal
26+
from PyQt4.QtGui import QDialog, QWidget, QAction, QKeySequence, \
27+
QDialogButtonBox, QApplication, QCursor, QMessageBox, QClipboard, QInputDialog, QIcon, QStyledItemDelegate, QStandardItemModel, QStandardItem
28+
from PyQt4.Qsci import QsciAPIs
29+
from PyQt4.QtXml import QDomDocument
30+
31+
from qgis.core import QgsProject, QgsDataSourceURI
32+
33+
from .db_plugins import createDbPlugin
34+
from .db_plugins.plugin import BaseError
35+
from .db_plugins.postgis.plugin import PGDatabase
36+
from .dlg_db_error import DlgDbError
37+
from .dlg_query_builder import QueryBuilderDlg
38+
39+
try:
40+
from qgis.gui import QgsCodeEditorSQL
41+
except:
42+
from .sqledit import SqlEdit
43+
from qgis import gui
44+
45+
gui.QgsCodeEditorSQL = SqlEdit
46+
47+
from .ui.ui_DlgSqlLayerWindow import Ui_DbManagerDlgSqlLayerWindow as Ui_Dialog
48+
49+
import re
50+
51+
52+
class DlgSqlLayerWindow(QWidget, Ui_Dialog):
53+
nameChanged = pyqtSignal(str)
54+
55+
def __init__(self, iface, layer, parent=None):
56+
QWidget.__init__(self, parent)
57+
self.iface = iface
58+
self.layer = layer
59+
60+
uri = QgsDataSourceURI(layer.source())
61+
dbplugin = None
62+
db = None
63+
if layer.dataProvider().name() == 'postgres':
64+
dbplugin = createDbPlugin('postgis', 'postgres')
65+
elif layer.dataProvider().name() == 'spatialite':
66+
dbplugin = createDbPlugin('spatialite', 'spatialite')
67+
elif layer.dataProvider().name() == 'oracle':
68+
dbplugin = createDbPlugin('oracle', 'oracle')
69+
elif layer.dataProvider().name() == 'virtual':
70+
dbplugin = createDbPlugin('vlayers', 'virtual')
71+
if dbplugin:
72+
dbplugin.connectToUri(uri)
73+
db = dbplugin.db
74+
75+
self.dbplugin = dbplugin
76+
self.db = db
77+
self.filter = ""
78+
self.allowMultiColumnPk = isinstance(db, PGDatabase) # at the moment only PostgreSQL allows a primary key to span multiple columns, spatialite doesn't
79+
self.aliasSubQuery = isinstance(db, PGDatabase) # only PostgreSQL requires subqueries to be aliases
80+
self.setupUi(self)
81+
self.setWindowTitle(
82+
u"%s - %s [%s]" % (self.windowTitle(), db.connection().connectionName(), db.connection().typeNameString()))
83+
84+
self.defaultLayerName = 'QueryLayer'
85+
86+
if self.allowMultiColumnPk:
87+
self.uniqueColumnCheck.setText(self.trUtf8("Column(s) with unique values"))
88+
else:
89+
self.uniqueColumnCheck.setText(self.trUtf8("Column with unique values"))
90+
91+
self.editSql.setFocus()
92+
self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded)
93+
self.editSql.setMarginVisible(True)
94+
self.initCompleter()
95+
96+
# allow copying results
97+
copyAction = QAction("copy", self)
98+
self.viewResult.addAction(copyAction)
99+
copyAction.setShortcuts(QKeySequence.Copy)
100+
101+
copyAction.triggered.connect(self.copySelectedResults)
102+
103+
self.btnExecute.clicked.connect(self.executeSql)
104+
self.btnSetFilter.clicked.connect(self.setFilter)
105+
self.btnClear.clicked.connect(self.clearSql)
106+
107+
self.presetStore.clicked.connect(self.storePreset)
108+
self.presetDelete.clicked.connect(self.deletePreset)
109+
self.presetCombo.activated[str].connect(self.loadPreset)
110+
self.presetCombo.activated[str].connect(self.presetName.setText)
111+
112+
self.updatePresetsCombobox()
113+
114+
self.geomCombo.setEditable(True)
115+
self.geomCombo.lineEdit().setReadOnly(True)
116+
117+
self.uniqueCombo.setEditable(True)
118+
self.uniqueCombo.lineEdit().setReadOnly(True)
119+
self.uniqueModel = QStandardItemModel(self.uniqueCombo)
120+
self.uniqueCombo.setModel(self.uniqueModel)
121+
if self.allowMultiColumnPk:
122+
self.uniqueCombo.setItemDelegate(QStyledItemDelegate())
123+
self.uniqueModel.itemChanged.connect(self.uniqueChanged) # react to the (un)checking of an item
124+
self.uniqueCombo.lineEdit().textChanged.connect(self.uniqueTextChanged) # there are other events that change the displayed text and some of them can not be caught directly
125+
126+
self.layerTypeWidget.hide() # show if load as raster is supported
127+
#self.loadLayerBtn.clicked.connect(self.loadSqlLayer)
128+
self.updateLayerBtn.clicked.connect(self.updateSqlLayer)
129+
self.getColumnsBtn.clicked.connect(self.fillColumnCombos)
130+
131+
self.queryBuilderFirst = True
132+
self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif"))
133+
self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder)
134+
135+
self.presetName.textChanged.connect(self.nameChanged)
136+
137+
# Update from layer
138+
# Fisrtly the SQL from QgsDataSourceURI table
139+
sql = uri.table()
140+
if uri.keyColumn() == '_uid_':
141+
match = re.search('^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$', sql, re.S)
142+
if match:
143+
sql = match.group(1)
144+
else:
145+
match = re.search('^\((SELECT .+ FROM .+)\)$', sql, re.S)
146+
if match:
147+
sql = match.group(1)
148+
self.editSql.setText(sql)
149+
self.executeSql()
150+
151+
# Then the columns
152+
self.geomCombo.setCurrentIndex(self.geomCombo.findText(uri.geometryColumn(), Qt.MatchExactly))
153+
if uri.keyColumn() != '_uid_':
154+
self.uniqueColumnCheck.setCheckState(Qt.Checked)
155+
if self.allowMultiColumnPk:
156+
itemsData = uri.keyColumn().split(',')
157+
for item in self.uniqueModel.findItems("*", Qt.MatchWildcard):
158+
if item.data() in itemsData:
159+
item.setCheckState(Qt.Checked)
160+
else:
161+
keyColumn = uri.keyColumn()
162+
for item in self.uniqueModel.findItems("*", Qt.MatchWildcard):
163+
if item.data() == keyColumn:
164+
self.uniqueCombo.setCurrentIndex(self.uniqueModel.indexFromItem(item).row())
165+
166+
# Finally layer name, filter and selectAtId
167+
self.layerNameEdit.setText(layer.name())
168+
self.filter = uri.sql()
169+
if uri.selectAtIdDisabled():
170+
self.avoidSelectById.setCheckState(Qt.Checked)
171+
172+
def updatePresetsCombobox(self):
173+
self.presetCombo.clear()
174+
175+
names = []
176+
entries = QgsProject.instance().subkeyList('DBManager', 'savedQueries')
177+
for entry in entries:
178+
name = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + entry + '/name')[0]
179+
names.append(name)
180+
181+
for name in sorted(names):
182+
self.presetCombo.addItem(name)
183+
self.presetCombo.setCurrentIndex(-1)
184+
185+
def storePreset(self):
186+
query = self._getSqlQuery()
187+
if query == "":
188+
return
189+
name = self.presetName.text()
190+
QgsProject.instance().writeEntry('DBManager', 'savedQueries/q' + unicode(name.__hash__()) + '/name', name)
191+
QgsProject.instance().writeEntry('DBManager', 'savedQueries/q' + unicode(name.__hash__()) + '/query', query)
192+
index = self.presetCombo.findText(name)
193+
if index == -1:
194+
self.presetCombo.addItem(name)
195+
self.presetCombo.setCurrentIndex(self.presetCombo.count() - 1)
196+
else:
197+
self.presetCombo.setCurrentIndex(index)
198+
199+
def deletePreset(self):
200+
name = self.presetCombo.currentText()
201+
QgsProject.instance().removeEntry('DBManager', 'savedQueries/q' + unicode(name.__hash__()))
202+
self.presetCombo.removeItem(self.presetCombo.findText(name))
203+
self.presetCombo.setCurrentIndex(-1)
204+
205+
def loadPreset(self, name):
206+
query = QgsProject.instance().readEntry('DBManager', 'savedQueries/q' + unicode(name.__hash__()) + '/query')[0]
207+
name = QgsProject.instance().readEntry('DBManager', 'savedQueries/q' + unicode(name.__hash__()) + '/name')[0]
208+
self.editSql.setText(query)
209+
210+
def clearSql(self):
211+
self.editSql.clear()
212+
self.editSql.setFocus()
213+
self.filter = ""
214+
215+
def executeSql(self):
216+
217+
sql = self._getSqlQuery()
218+
if sql == "":
219+
return
220+
221+
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
222+
223+
# delete the old model
224+
old_model = self.viewResult.model()
225+
self.viewResult.setModel(None)
226+
if old_model:
227+
old_model.deleteLater()
228+
229+
cols = []
230+
quotedCols = []
231+
232+
try:
233+
# set the new model
234+
model = self.db.sqlResultModel(sql, self)
235+
self.viewResult.setModel(model)
236+
self.lblResult.setText(self.tr("%d rows, %.1f seconds") % (model.affectedRows(), model.secs()))
237+
cols = self.viewResult.model().columnNames()
238+
for col in cols:
239+
quotedCols.append(self.db.connector.quoteId(col))
240+
241+
except BaseError as e:
242+
QApplication.restoreOverrideCursor()
243+
DlgDbError.showError(e, self)
244+
self.uniqueModel.clear()
245+
self.geomCombo.clear()
246+
return
247+
248+
self.setColumnCombos(cols, quotedCols)
249+
250+
self.update()
251+
QApplication.restoreOverrideCursor()
252+
253+
def _getSqlLayer(self, _filter):
254+
hasUniqueField = self.uniqueColumnCheck.checkState() == Qt.Checked
255+
if hasUniqueField:
256+
if self.allowMultiColumnPk:
257+
checkedCols = []
258+
for item in self.uniqueModel.findItems("*", Qt.MatchWildcard):
259+
if item.checkState() == Qt.Checked:
260+
checkedCols.append(item.data())
261+
uniqueFieldName = ",".join(checkedCols)
262+
elif self.uniqueCombo.currentIndex() >= 0:
263+
uniqueFieldName = self.uniqueModel.item(self.uniqueCombo.currentIndex()).data()
264+
else:
265+
uniqueFieldName = None
266+
else:
267+
uniqueFieldName = None
268+
hasGeomCol = self.hasGeometryCol.checkState() == Qt.Checked
269+
if hasGeomCol:
270+
geomFieldName = self.geomCombo.currentText()
271+
else:
272+
geomFieldName = None
273+
274+
query = self._getSqlQuery()
275+
if query == "":
276+
return None
277+
278+
# remove a trailing ';' from query if present
279+
if query.strip().endswith(';'):
280+
query = query.strip()[:-1]
281+
282+
from qgis.core import QgsMapLayer, QgsMapLayerRegistry
283+
284+
layerType = QgsMapLayer.VectorLayer if self.vectorRadio.isChecked() else QgsMapLayer.RasterLayer
285+
286+
# get a new layer name
287+
names = []
288+
for layer in QgsMapLayerRegistry.instance().mapLayers().values():
289+
names.append(layer.name())
290+
291+
layerName = self.layerNameEdit.text()
292+
if layerName == "":
293+
layerName = self.defaultLayerName
294+
newLayerName = layerName
295+
index = 1
296+
while newLayerName in names:
297+
index += 1
298+
newLayerName = u"%s_%d" % (layerName, index)
299+
300+
# create the layer
301+
layer = self.db.toSqlLayer(query, geomFieldName, uniqueFieldName, newLayerName, layerType,
302+
self.avoidSelectById.isChecked(), _filter)
303+
if layer.isValid():
304+
return layer
305+
else:
306+
return None
307+
308+
def loadSqlLayer(self):
309+
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
310+
try:
311+
layer = self._getSqlLayer(self.filter)
312+
if layer == None:
313+
return
314+
315+
from qgis.core import QgsMapLayerRegistry
316+
QgsMapLayerRegistry.instance().addMapLayers([layer], True)
317+
finally:
318+
QApplication.restoreOverrideCursor()
319+
320+
def updateSqlLayer(self):
321+
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
322+
try:
323+
layer = self._getSqlLayer(self.filter)
324+
if layer == None:
325+
return
326+
327+
#self.layer.dataProvider().setDataSourceUri(layer.dataProvider().dataSourceUri())
328+
#self.layer.dataProvider().reloadData()
329+
XMLDocument = QDomDocument("style")
330+
XMLMapLayers = XMLDocument.createElement("maplayers")
331+
XMLMapLayer = XMLDocument.createElement("maplayer")
332+
self.layer.writeLayerXML(XMLMapLayer, XMLDocument)
333+
XMLMapLayer.firstChildElement("datasource").firstChild().setNodeValue(layer.source())
334+
XMLMapLayers.appendChild(XMLMapLayer)
335+
XMLDocument.appendChild(XMLMapLayers)
336+
self.layer.readLayerXML(XMLMapLayer)
337+
self.layer.reload()
338+
self.iface.actionDraw().trigger()
339+
self.iface.mapCanvas().refresh()
340+
self.iface.legendInterface().refreshLayerSymbology(layer)
341+
finally:
342+
QApplication.restoreOverrideCursor()
343+
344+
def fillColumnCombos(self):
345+
query = self._getSqlQuery()
346+
if query == "":
347+
return
348+
349+
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
350+
351+
# remove a trailing ';' from query if present
352+
if query.strip().endswith(';'):
353+
query = query.strip()[:-1]
354+
355+
# get all the columns
356+
cols = []
357+
quotedCols = []
358+
connector = self.db.connector
359+
if self.aliasSubQuery:
360+
# get a new alias
361+
aliasIndex = 0
362+
while True:
363+
alias = "_subQuery__%d" % aliasIndex
364+
escaped = re.compile('\\b("?)' + re.escape(alias) + '\\1\\b')
365+
if not escaped.search(query):
366+
break
367+
aliasIndex += 1
368+
369+
sql = u"SELECT * FROM (%s\n) AS %s LIMIT 0" % (unicode(query), connector.quoteId(alias))
370+
else:
371+
sql = u"SELECT * FROM (%s\n) WHERE 1=0" % unicode(query)
372+
373+
c = None
374+
try:
375+
c = connector._execute(None, sql)
376+
cols = connector._get_cursor_columns(c)
377+
for col in cols:
378+
quotedCols.append(connector.quoteId(col))
379+
380+
except BaseError as e:
381+
QApplication.restoreOverrideCursor()
382+
DlgDbError.showError(e, self)
383+
self.uniqueModel.clear()
384+
self.geomCombo.clear()
385+
return
386+
387+
finally:
388+
if c:
389+
c.close()
390+
del c
391+
392+
self.setColumnCombos(cols, quotedCols)
393+
394+
QApplication.restoreOverrideCursor()
395+
396+
def setColumnCombos(self, cols, quotedCols):
397+
# get sensible default columns. do this before sorting in case there's hints in the column order (eg, id is more likely to be first)
398+
try:
399+
defaultGeomCol = next(col for col in cols if col in ['geom', 'geometry', 'the_geom', 'way'])
400+
except:
401+
defaultGeomCol = None
402+
try:
403+
defaultUniqueCol = [col for col in cols if 'id' in col][0]
404+
except:
405+
defaultUniqueCol = None
406+
407+
colNames = sorted(zip(cols, quotedCols))
408+
newItems = []
409+
uniqueIsFilled = False
410+
for (col, quotedCol) in colNames:
411+
item = QStandardItem(col)
412+
item.setData(quotedCol)
413+
item.setEnabled(True)
414+
item.setCheckable(self.allowMultiColumnPk)
415+
item.setSelectable(not self.allowMultiColumnPk)
416+
if self.allowMultiColumnPk:
417+
matchingItems = self.uniqueModel.findItems(col)
418+
if matchingItems:
419+
item.setCheckState(matchingItems[0].checkState())
420+
uniqueIsFilled = uniqueIsFilled or matchingItems[0].checkState() == Qt.Checked
421+
else:
422+
item.setCheckState(Qt.Unchecked)
423+
newItems.append(item)
424+
if self.allowMultiColumnPk:
425+
self.uniqueModel.clear()
426+
self.uniqueModel.appendColumn(newItems)
427+
self.uniqueChanged()
428+
else:
429+
previousUniqueColumn = self.uniqueCombo.currentText()
430+
self.uniqueModel.clear()
431+
self.uniqueModel.appendColumn(newItems)
432+
if self.uniqueModel.findItems(previousUniqueColumn):
433+
self.uniqueCombo.setEditText(previousUniqueColumn)
434+
uniqueIsFilled = True
435+
436+
oldGeometryColumn = self.geomCombo.currentText()
437+
self.geomCombo.clear()
438+
self.geomCombo.addItems(cols)
439+
self.geomCombo.setCurrentIndex(self.geomCombo.findText(oldGeometryColumn, Qt.MatchExactly))
440+
441+
# set sensible default columns if the columns are not already set
442+
try:
443+
if self.geomCombo.currentIndex() == -1:
444+
self.geomCombo.setCurrentIndex(cols.index(defaultGeomCol))
445+
except:
446+
pass
447+
items = self.uniqueModel.findItems(defaultUniqueCol)
448+
if items and not uniqueIsFilled:
449+
if self.allowMultiColumnPk:
450+
items[0].setCheckState(Qt.Checked)
451+
else:
452+
self.uniqueCombo.setEditText(defaultUniqueCol)
453+
try:
454+
pass
455+
except:
456+
pass
457+
458+
def copySelectedResults(self):
459+
if len(self.viewResult.selectedIndexes()) <= 0:
460+
return
461+
model = self.viewResult.model()
462+
463+
# convert to string using tab as separator
464+
text = model.headerToString("\t")
465+
for idx in self.viewResult.selectionModel().selectedRows():
466+
text += "\n" + model.rowToString(idx.row(), "\t")
467+
468+
QApplication.clipboard().setText(text, QClipboard.Selection)
469+
QApplication.clipboard().setText(text, QClipboard.Clipboard)
470+
471+
def initCompleter(self):
472+
dictionary = None
473+
if self.db:
474+
dictionary = self.db.connector.getSqlDictionary()
475+
if not dictionary:
476+
# use the generic sql dictionary
477+
from .sql_dictionary import getSqlDictionary
478+
479+
dictionary = getSqlDictionary()
480+
481+
wordlist = []
482+
for name, value in dictionary.iteritems():
483+
wordlist += value # concat lists
484+
wordlist = list(set(wordlist)) # remove duplicates
485+
486+
api = QsciAPIs(self.editSql.lexer())
487+
for word in wordlist:
488+
api.add(word)
489+
490+
api.prepare()
491+
self.editSql.lexer().setAPIs(api)
492+
493+
def displayQueryBuilder(self):
494+
dlg = QueryBuilderDlg(self.iface, self.db, self, reset=self.queryBuilderFirst)
495+
self.queryBuilderFirst = False
496+
r = dlg.exec_()
497+
if r == QDialog.Accepted:
498+
self.editSql.setText(dlg.query)
499+
500+
def _getSqlQuery(self):
501+
sql = self.editSql.selectedText()
502+
if len(sql) == 0:
503+
sql = self.editSql.text()
504+
return sql
505+
506+
def uniqueChanged(self):
507+
# when an item is (un)checked, simply trigger an update of the combobox text
508+
self.uniqueTextChanged(None)
509+
510+
def uniqueTextChanged(self, text):
511+
# Whenever there is new text displayed in the combobox, check if it is the correct one and if not, display the correct one.
512+
checkedItems = []
513+
for item in self.uniqueModel.findItems("*", Qt.MatchWildcard):
514+
if item.checkState() == Qt.Checked:
515+
checkedItems.append(item.text())
516+
label = ", ".join(checkedItems)
517+
if text != label:
518+
self.uniqueCombo.setEditText(label)
519+
520+
def setFilter(self):
521+
from qgis.gui import QgsQueryBuilder
522+
layer = self._getSqlLayer("")
523+
if not layer:
524+
return
525+
526+
dlg = QgsQueryBuilder(layer)
527+
dlg.setSql(self.filter)
528+
if dlg.exec_():
529+
self.filter = dlg.sql()
530+
layer.deleteLater()
Lines changed: 407 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,407 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<ui version="4.0">
3+
<class>DbManagerDlgSqlLayerWindow</class>
4+
<widget class="QDialog" name="DbManagerDlgSqlLayerWindow">
5+
<property name="geometry">
6+
<rect>
7+
<x>0</x>
8+
<y>0</y>
9+
<width>662</width>
10+
<height>525</height>
11+
</rect>
12+
</property>
13+
<property name="windowTitle">
14+
<string>SQL window</string>
15+
</property>
16+
<layout class="QGridLayout" name="gridLayout_2">
17+
<item row="3" column="0">
18+
<layout class="QHBoxLayout" name="horizontalLayout_2">
19+
<item>
20+
<widget class="QCheckBox" name="avoidSelectById">
21+
<property name="toolTip">
22+
<string>&lt;html&gt;&lt;head/&gt;&lt;body&gt;&lt;p&gt;Avoid selecting feature by id. Sometimes - especially when running expensive queries/views - fetching the data sequentially instead of fetching features by id can be much quicker.&lt;/p&gt;&lt;/body&gt;&lt;/html&gt;</string>
23+
</property>
24+
<property name="text">
25+
<string>Avoid selecting by feature id</string>
26+
</property>
27+
</widget>
28+
</item>
29+
<item>
30+
<spacer name="horizontalSpacer_2">
31+
<property name="orientation">
32+
<enum>Qt::Horizontal</enum>
33+
</property>
34+
<property name="sizeHint" stdset="0">
35+
<size>
36+
<width>40</width>
37+
<height>20</height>
38+
</size>
39+
</property>
40+
</spacer>
41+
</item>
42+
<item>
43+
<widget class="QPushButton" name="updateLayerBtn">
44+
<property name="text">
45+
<string>Update</string>
46+
</property>
47+
</widget>
48+
</item>
49+
</layout>
50+
</item>
51+
<item row="0" column="0">
52+
<widget class="QSplitter" name="splitter">
53+
<property name="orientation">
54+
<enum>Qt::Vertical</enum>
55+
</property>
56+
<widget class="QWidget" name="layoutWidget">
57+
<layout class="QVBoxLayout" name="verticalLayout_2">
58+
<item>
59+
<layout class="QHBoxLayout" name="horizontalLayout">
60+
<item>
61+
<widget class="QToolButton" name="queryBuilderBtn">
62+
<property name="text">
63+
<string/>
64+
</property>
65+
</widget>
66+
</item>
67+
<item>
68+
<spacer name="horizontalSpacer">
69+
<property name="orientation">
70+
<enum>Qt::Horizontal</enum>
71+
</property>
72+
<property name="sizeHint" stdset="0">
73+
<size>
74+
<width>40</width>
75+
<height>20</height>
76+
</size>
77+
</property>
78+
</spacer>
79+
</item>
80+
<item>
81+
<widget class="QLabel" name="label">
82+
<property name="text">
83+
<string>Saved query:</string>
84+
</property>
85+
</widget>
86+
</item>
87+
<item>
88+
<widget class="QComboBox" name="presetCombo"/>
89+
</item>
90+
<item>
91+
<widget class="QLabel" name="label_2">
92+
<property name="text">
93+
<string>Name</string>
94+
</property>
95+
</widget>
96+
</item>
97+
<item>
98+
<widget class="QLineEdit" name="presetName">
99+
<property name="text">
100+
<string notr="true"/>
101+
</property>
102+
</widget>
103+
</item>
104+
<item>
105+
<widget class="QPushButton" name="presetStore">
106+
<property name="text">
107+
<string>Store</string>
108+
</property>
109+
</widget>
110+
</item>
111+
<item>
112+
<widget class="QPushButton" name="presetDelete">
113+
<property name="text">
114+
<string>Delete</string>
115+
</property>
116+
</widget>
117+
</item>
118+
</layout>
119+
</item>
120+
<item>
121+
<widget class="QgsCodeEditorSQL" name="editSql"/>
122+
</item>
123+
<item>
124+
<layout class="QHBoxLayout">
125+
<item>
126+
<widget class="QPushButton" name="btnExecute">
127+
<property name="text">
128+
<string>&amp;Execute (F5)</string>
129+
</property>
130+
<property name="shortcut">
131+
<string>F5</string>
132+
</property>
133+
</widget>
134+
</item>
135+
<item>
136+
<widget class="QLabel" name="lblResult">
137+
<property name="text">
138+
<string/>
139+
</property>
140+
</widget>
141+
</item>
142+
<item>
143+
<spacer>
144+
<property name="orientation">
145+
<enum>Qt::Horizontal</enum>
146+
</property>
147+
<property name="sizeHint" stdset="0">
148+
<size>
149+
<width>40</width>
150+
<height>20</height>
151+
</size>
152+
</property>
153+
</spacer>
154+
</item>
155+
<item>
156+
<widget class="QPushButton" name="btnClear">
157+
<property name="text">
158+
<string>&amp;Clear</string>
159+
</property>
160+
</widget>
161+
</item>
162+
</layout>
163+
</item>
164+
</layout>
165+
</widget>
166+
<widget class="QWidget" name="layoutWidget">
167+
<layout class="QVBoxLayout" name="verticalLayout">
168+
<item>
169+
<widget class="QTableView" name="viewResult">
170+
<property name="sizePolicy">
171+
<sizepolicy hsizetype="Expanding" vsizetype="Expanding">
172+
<horstretch>0</horstretch>
173+
<verstretch>3</verstretch>
174+
</sizepolicy>
175+
</property>
176+
<property name="selectionBehavior">
177+
<enum>QAbstractItemView::SelectRows</enum>
178+
</property>
179+
<property name="horizontalScrollMode">
180+
<enum>QAbstractItemView::ScrollPerPixel</enum>
181+
</property>
182+
</widget>
183+
</item>
184+
</layout>
185+
</widget>
186+
</widget>
187+
</item>
188+
<item row="1" column="0">
189+
<layout class="QHBoxLayout" name="horizontalLayout_6">
190+
<item>
191+
<widget class="QCheckBox" name="uniqueColumnCheck">
192+
<property name="text">
193+
<string>Column(s) with
194+
unique values</string>
195+
</property>
196+
</widget>
197+
</item>
198+
<item>
199+
<widget class="QComboBox" name="uniqueCombo">
200+
<property name="enabled">
201+
<bool>false</bool>
202+
</property>
203+
<property name="sizePolicy">
204+
<sizepolicy hsizetype="Expanding" vsizetype="Fixed">
205+
<horstretch>0</horstretch>
206+
<verstretch>0</verstretch>
207+
</sizepolicy>
208+
</property>
209+
<property name="editable">
210+
<bool>true</bool>
211+
</property>
212+
<property name="insertPolicy">
213+
<enum>QComboBox::NoInsert</enum>
214+
</property>
215+
</widget>
216+
</item>
217+
<item>
218+
<widget class="QCheckBox" name="hasGeometryCol">
219+
<property name="text">
220+
<string>Geometry column</string>
221+
</property>
222+
<property name="checked">
223+
<bool>true</bool>
224+
</property>
225+
<property name="tristate">
226+
<bool>false</bool>
227+
</property>
228+
</widget>
229+
</item>
230+
<item>
231+
<widget class="QComboBox" name="geomCombo">
232+
<property name="sizePolicy">
233+
<sizepolicy hsizetype="Expanding" vsizetype="Fixed">
234+
<horstretch>0</horstretch>
235+
<verstretch>0</verstretch>
236+
</sizepolicy>
237+
</property>
238+
<property name="editable">
239+
<bool>true</bool>
240+
</property>
241+
<property name="insertPolicy">
242+
<enum>QComboBox::NoInsert</enum>
243+
</property>
244+
</widget>
245+
</item>
246+
<item>
247+
<spacer name="horizontalSpacer_5">
248+
<property name="orientation">
249+
<enum>Qt::Horizontal</enum>
250+
</property>
251+
<property name="sizeType">
252+
<enum>QSizePolicy::Fixed</enum>
253+
</property>
254+
<property name="sizeHint" stdset="0">
255+
<size>
256+
<width>40</width>
257+
<height>20</height>
258+
</size>
259+
</property>
260+
</spacer>
261+
</item>
262+
<item>
263+
<widget class="QPushButton" name="getColumnsBtn">
264+
<property name="text">
265+
<string>Retrieve
266+
columns</string>
267+
</property>
268+
</widget>
269+
</item>
270+
</layout>
271+
</item>
272+
<item row="2" column="0">
273+
<layout class="QHBoxLayout" name="horizontalLayout_7">
274+
<item>
275+
<widget class="QLabel" name="label_5">
276+
<property name="text">
277+
<string>Layer name (prefix)</string>
278+
</property>
279+
</widget>
280+
</item>
281+
<item>
282+
<widget class="QLineEdit" name="layerNameEdit">
283+
<property name="enabled">
284+
<bool>true</bool>
285+
</property>
286+
<property name="text">
287+
<string notr="true"/>
288+
</property>
289+
<property name="readOnly">
290+
<bool>true</bool>
291+
</property>
292+
</widget>
293+
</item>
294+
<item>
295+
<widget class="QWidget" name="layerTypeWidget" native="true">
296+
<layout class="QHBoxLayout" name="horizontalLayout_3">
297+
<property name="margin">
298+
<number>0</number>
299+
</property>
300+
<item>
301+
<widget class="QLabel" name="label_6">
302+
<property name="text">
303+
<string>Type</string>
304+
</property>
305+
<property name="indent">
306+
<number>40</number>
307+
</property>
308+
</widget>
309+
</item>
310+
<item>
311+
<widget class="QRadioButton" name="vectorRadio">
312+
<property name="text">
313+
<string>Vector</string>
314+
</property>
315+
<property name="checked">
316+
<bool>true</bool>
317+
</property>
318+
</widget>
319+
</item>
320+
<item>
321+
<widget class="QRadioButton" name="rasterRadio">
322+
<property name="text">
323+
<string>Raster</string>
324+
</property>
325+
</widget>
326+
</item>
327+
<item>
328+
<spacer name="horizontalSpacer_6">
329+
<property name="orientation">
330+
<enum>Qt::Horizontal</enum>
331+
</property>
332+
<property name="sizeType">
333+
<enum>QSizePolicy::Fixed</enum>
334+
</property>
335+
<property name="sizeHint" stdset="0">
336+
<size>
337+
<width>40</width>
338+
<height>20</height>
339+
</size>
340+
</property>
341+
</spacer>
342+
</item>
343+
</layout>
344+
</widget>
345+
</item>
346+
<item>
347+
<widget class="QPushButton" name="btnSetFilter">
348+
<property name="text">
349+
<string>Set filter</string>
350+
</property>
351+
<property name="autoDefault">
352+
<bool>false</bool>
353+
</property>
354+
</widget>
355+
</item>
356+
</layout>
357+
</item>
358+
</layout>
359+
</widget>
360+
<customwidgets>
361+
<customwidget>
362+
<class>QgsCodeEditorSQL</class>
363+
<extends>QTextEdit</extends>
364+
<header>qgis.gui</header>
365+
</customwidget>
366+
</customwidgets>
367+
<tabstops>
368+
<tabstop>btnExecute</tabstop>
369+
<tabstop>btnClear</tabstop>
370+
<tabstop>viewResult</tabstop>
371+
</tabstops>
372+
<resources/>
373+
<connections>
374+
<connection>
375+
<sender>hasGeometryCol</sender>
376+
<signal>toggled(bool)</signal>
377+
<receiver>geomCombo</receiver>
378+
<slot>setEnabled(bool)</slot>
379+
<hints>
380+
<hint type="sourcelabel">
381+
<x>379</x>
382+
<y>385</y>
383+
</hint>
384+
<hint type="destinationlabel">
385+
<x>525</x>
386+
<y>385</y>
387+
</hint>
388+
</hints>
389+
</connection>
390+
<connection>
391+
<sender>uniqueColumnCheck</sender>
392+
<signal>toggled(bool)</signal>
393+
<receiver>uniqueCombo</receiver>
394+
<slot>setEnabled(bool)</slot>
395+
<hints>
396+
<hint type="sourcelabel">
397+
<x>109</x>
398+
<y>385</y>
399+
</hint>
400+
<hint type="destinationlabel">
401+
<x>274</x>
402+
<y>385</y>
403+
</hint>
404+
</hints>
405+
</connection>
406+
</connections>
407+
</ui>

0 commit comments

Comments
 (0)
Please sign in to comment.