|
| 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() |
0 commit comments