class CheckableComboBox: """Basic QCombobox with selectable items.""" def __init__(self, combobox, select_all=None): """Constructor.""" self.combo = combobox self.combo.setEditable(True) self.combo.lineEdit().setReadOnly(True) self.model = QStandardItemModel(self.combo) self.combo.setModel(self.model) self.combo.setItemDelegate(QStyledItemDelegate()) self.model.itemChanged.connect(self.combo_changed) self.combo.lineEdit().textChanged.connect(self.text_changed) if select_all: self.select_all = select_all self.select_all.clicked.connect(self.select_all_clicked) def select_all_clicked(self): for item in self.model.findItems("*", Qt.MatchWildcard): item.setCheckState(Qt.Checked) def append_row(self, item: QStandardItem): """Add an item to the combobox.""" item.setEnabled(True) item.setCheckable(True) item.setSelectable(False) self.model.appendRow(item) def combo_changed(self): """Slot when the combo has changed.""" self.text_changed(None) def selected_items(self) -> list: checked_items = [] for item in self.model.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checked_items.append(item.data()) return checked_items def set_selected_items(self, items): for item in self.model.findItems("*", Qt.MatchWildcard): checked = item.data() in items item.setCheckState(Qt.Checked if checked else Qt.Unchecked) def text_changed(self, text): """Update the preview with all selected items, separated by a comma.""" label = ", ".join(self.selected_items()) if text != label: self.combo.setEditText(label)
class DlgSqlLayerWindow(QWidget, Ui_Dialog): nameChanged = pyqtSignal(str) def __init__(self, iface, layer, parent=None): QWidget.__init__(self, parent) self.iface = iface self.layer = layer uri = QgsDataSourceUri(layer.source()) dbplugin = None db = None if layer.dataProvider().name() == 'postgres': dbplugin = createDbPlugin('postgis', 'postgres') elif layer.dataProvider().name() == 'spatialite': dbplugin = createDbPlugin('spatialite', 'spatialite') elif layer.dataProvider().name() == 'oracle': dbplugin = createDbPlugin('oracle', 'oracle') elif layer.dataProvider().name() == 'virtual': dbplugin = createDbPlugin('vlayers', 'virtual') elif layer.dataProvider().name() == 'ogr': dbplugin = createDbPlugin('gpkg', 'gpkg') if dbplugin: dbplugin.connectToUri(uri) db = dbplugin.db self.dbplugin = dbplugin self.db = db self.filter = "" self.allowMultiColumnPk = isinstance( db, PGDatabase ) # at the moment only PostgreSQL allows a primary key to span multiple columns, SpatiaLite doesn't self.aliasSubQuery = isinstance( db, PGDatabase) # only PostgreSQL requires subqueries to be aliases self.setupUi(self) self.setWindowTitle( u"%s - %s [%s]" % (self.windowTitle(), db.connection().connectionName(), db.connection().typeNameString())) self.defaultLayerName = 'QueryLayer' if self.allowMultiColumnPk: self.uniqueColumnCheck.setText( self.tr("Column(s) with unique values")) else: self.uniqueColumnCheck.setText( self.tr("Column with unique values")) self.editSql.setFocus() self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded) self.editSql.setMarginVisible(True) self.initCompleter() # allow copying results copyAction = QAction("copy", self) self.viewResult.addAction(copyAction) copyAction.setShortcuts(QKeySequence.Copy) copyAction.triggered.connect(self.copySelectedResults) self.btnExecute.clicked.connect(self.executeSql) self.btnSetFilter.clicked.connect(self.setFilter) self.btnClear.clicked.connect(self.clearSql) self.presetStore.clicked.connect(self.storePreset) self.presetDelete.clicked.connect(self.deletePreset) self.presetCombo.activated[str].connect(self.loadPreset) self.presetCombo.activated[str].connect(self.presetName.setText) self.editSql.textChanged.connect(self.updatePresetButtonsState) self.presetName.textChanged.connect(self.updatePresetButtonsState) self.presetCombo.currentIndexChanged.connect( self.updatePresetButtonsState) self.updatePresetsCombobox() self.geomCombo.setEditable(True) self.geomCombo.lineEdit().setReadOnly(True) self.uniqueCombo.setEditable(True) self.uniqueCombo.lineEdit().setReadOnly(True) self.uniqueModel = QStandardItemModel(self.uniqueCombo) self.uniqueCombo.setModel(self.uniqueModel) if self.allowMultiColumnPk: self.uniqueCombo.setItemDelegate(QStyledItemDelegate()) self.uniqueModel.itemChanged.connect( self.uniqueChanged) # react to the (un)checking of an item 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 self.layerTypeWidget.hide() # show if load as raster is supported # self.loadLayerBtn.clicked.connect(self.loadSqlLayer) self.updateLayerBtn.clicked.connect(self.updateSqlLayer) self.getColumnsBtn.clicked.connect(self.fillColumnCombos) self.queryBuilderFirst = True self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif")) self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder) self.presetName.textChanged.connect(self.nameChanged) # Update from layer # First the SQL from QgsDataSourceUri table sql = uri.table() if uri.keyColumn() == '_uid_': match = re.search( r'^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$', sql, re.S | re.X) if match: sql = match.group(1) else: match = re.search(r'^\((SELECT .+ FROM .+)\)$', sql, re.S | re.X) if match: sql = match.group(1) # Need to check on table() since the parentheses were removed by the regexp if not uri.table().startswith('(') and not uri.table().endswith(')'): schema = uri.schema() if schema and schema.upper() != 'PUBLIC': sql = 'SELECT * FROM {0}.{1}'.format( self.db.connector.quoteId(schema), self.db.connector.quoteId(sql)) else: sql = 'SELECT * FROM {0}'.format( self.db.connector.quoteId(sql)) self.editSql.setText(sql) self.executeSql() # Then the columns self.geomCombo.setCurrentIndex( self.geomCombo.findText(uri.geometryColumn(), Qt.MatchExactly)) if uri.keyColumn() != '_uid_': self.uniqueColumnCheck.setCheckState(Qt.Checked) if self.allowMultiColumnPk: itemsData = uri.keyColumn().split(',') for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.data() in itemsData: item.setCheckState(Qt.Checked) else: keyColumn = uri.keyColumn() for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.data() == keyColumn: self.uniqueCombo.setCurrentIndex( self.uniqueModel.indexFromItem(item).row()) # Finally layer name, filter and selectAtId self.layerNameEdit.setText(layer.name()) self.filter = uri.sql() if uri.selectAtIdDisabled(): self.avoidSelectById.setCheckState(Qt.Checked) def getQueryHash(self, name): return 'q%s' % md5(name.encode('utf8')).hexdigest() def updatePresetButtonsState(self, *args): """Slot called when the combo box or the sql or the query name have changed: sets store button state""" self.presetStore.setEnabled( bool(self._getSqlQuery() and self.presetName.text())) self.presetDelete.setEnabled( bool(self.presetCombo.currentIndex() != -1)) def updatePresetsCombobox(self): self.presetCombo.clear() names = [] entries = QgsProject.instance().subkeyList('DBManager', 'savedQueries') for entry in entries: name = QgsProject.instance().readEntry( 'DBManager', 'savedQueries/' + entry + '/name')[0] names.append(name) for name in sorted(names): self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(-1) def storePreset(self): query = self._getSqlQuery() if query == "": return name = self.presetName.text() QgsProject.instance().writeEntry( 'DBManager', 'savedQueries/' + self.getQueryHash(name) + '/name', name) QgsProject.instance().writeEntry( 'DBManager', 'savedQueries/' + self.getQueryHash(name) + '/query', query) index = self.presetCombo.findText(name) if index == -1: self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(self.presetCombo.count() - 1) else: self.presetCombo.setCurrentIndex(index) def deletePreset(self): name = self.presetCombo.currentText() QgsProject.instance().removeEntry( 'DBManager', 'savedQueries/q' + self.getQueryHash(name)) self.presetCombo.removeItem(self.presetCombo.findText(name)) self.presetCombo.setCurrentIndex(-1) def loadPreset(self, name): query = QgsProject.instance().readEntry( 'DBManager', 'savedQueries/' + self.getQueryHash(name) + '/query')[0] name = QgsProject.instance().readEntry( 'DBManager', 'savedQueries/' + self.getQueryHash(name) + '/name')[0] self.editSql.setText(query) def clearSql(self): self.editSql.clear() self.editSql.setFocus() self.filter = "" def executeSql(self): sql = self._getSqlQuery() if sql == "": return with OverrideCursor(Qt.WaitCursor): # delete the old model old_model = self.viewResult.model() self.viewResult.setModel(None) if old_model: old_model.deleteLater() cols = [] quotedCols = [] try: # set the new model model = self.db.sqlResultModel(sql, self) self.viewResult.setModel(model) self.lblResult.setText( self.tr("{0} rows, {1:.1f} seconds").format( model.affectedRows(), model.secs())) cols = self.viewResult.model().columnNames() for col in cols: quotedCols.append(self.db.connector.quoteId(col)) except BaseError as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return self.setColumnCombos(cols, quotedCols) self.update() def _getSqlLayer(self, _filter): hasUniqueField = self.uniqueColumnCheck.checkState() == Qt.Checked if hasUniqueField: if self.allowMultiColumnPk: checkedCols = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedCols.append(item.data()) uniqueFieldName = ",".join(checkedCols) elif self.uniqueCombo.currentIndex() >= 0: uniqueFieldName = self.uniqueModel.item( self.uniqueCombo.currentIndex()).data() else: uniqueFieldName = None else: uniqueFieldName = None hasGeomCol = self.hasGeometryCol.checkState() == Qt.Checked if hasGeomCol: geomFieldName = self.geomCombo.currentText() else: geomFieldName = None query = self._getSqlQuery() if query == "": return None # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] from qgis.core import QgsMapLayer layerType = QgsMapLayer.VectorLayer if self.vectorRadio.isChecked( ) else QgsMapLayer.RasterLayer # get a new layer name names = [] for layer in list(QgsProject.instance().mapLayers().values()): names.append(layer.name()) layerName = self.layerNameEdit.text() if layerName == "": layerName = self.defaultLayerName newLayerName = layerName index = 1 while newLayerName in names: index += 1 newLayerName = u"%s_%d" % (layerName, index) # create the layer layer = self.db.toSqlLayer(query, geomFieldName, uniqueFieldName, newLayerName, layerType, self.avoidSelectById.isChecked(), _filter) if layer.isValid(): return layer else: return None def loadSqlLayer(self): with OverrideCursor(Qt.WaitCursor): layer = self._getSqlLayer(self.filter) if layer is None: return QgsProject.instance().addMapLayers([layer], True) def updateSqlLayer(self): with OverrideCursor(Qt.WaitCursor): layer = self._getSqlLayer(self.filter) if layer is None: return # self.layer.dataProvider().setDataSourceUri(layer.dataProvider().dataSourceUri()) # self.layer.dataProvider().reloadData() XMLDocument = QDomDocument("style") XMLMapLayers = XMLDocument.createElement("maplayers") XMLMapLayer = XMLDocument.createElement("maplayer") self.layer.writeLayerXml(XMLMapLayer, XMLDocument, QgsReadWriteContext()) XMLMapLayer.firstChildElement( "datasource").firstChild().setNodeValue(layer.source()) XMLMapLayers.appendChild(XMLMapLayer) XMLDocument.appendChild(XMLMapLayers) self.layer.readLayerXml(XMLMapLayer, QgsReadWriteContext()) self.layer.reload() self.iface.actionDraw().trigger() self.iface.mapCanvas().refresh() def fillColumnCombos(self): query = self._getSqlQuery() if query == "": return with OverrideCursor(Qt.WaitCursor): # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] # get all the columns cols = [] quotedCols = [] connector = self.db.connector if self.aliasSubQuery: # get a new alias aliasIndex = 0 while True: alias = "_subQuery__%d" % aliasIndex escaped = re.compile('\\b("?)' + re.escape(alias) + '\\1\\b') if not escaped.search(query): break aliasIndex += 1 sql = u"SELECT * FROM (%s\n) AS %s LIMIT 0" % ( str(query), connector.quoteId(alias)) else: sql = u"SELECT * FROM (%s\n) WHERE 1=0" % str(query) c = None try: c = connector._execute(None, sql) cols = connector._get_cursor_columns(c) for col in cols: quotedCols.append(connector.quoteId(col)) except BaseError as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return finally: if c: c.close() del c self.setColumnCombos(cols, quotedCols) def setColumnCombos(self, cols, quotedCols): # get sensible default columns. do this before sorting in case there's hints in the column order (e.g., id is more likely to be first) try: defaultGeomCol = next( col for col in cols if col in ['geom', 'geometry', 'the_geom', 'way']) except: defaultGeomCol = None try: defaultUniqueCol = [col for col in cols if 'id' in col][0] except: defaultUniqueCol = None colNames = sorted(zip(cols, quotedCols)) newItems = [] uniqueIsFilled = False for (col, quotedCol) in colNames: item = QStandardItem(col) item.setData(quotedCol) item.setEnabled(True) item.setCheckable(self.allowMultiColumnPk) item.setSelectable(not self.allowMultiColumnPk) if self.allowMultiColumnPk: matchingItems = self.uniqueModel.findItems(col) if matchingItems: item.setCheckState(matchingItems[0].checkState()) uniqueIsFilled = uniqueIsFilled or matchingItems[ 0].checkState() == Qt.Checked else: item.setCheckState(Qt.Unchecked) newItems.append(item) if self.allowMultiColumnPk: self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) self.uniqueChanged() else: previousUniqueColumn = self.uniqueCombo.currentText() self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) if self.uniqueModel.findItems(previousUniqueColumn): self.uniqueCombo.setEditText(previousUniqueColumn) uniqueIsFilled = True oldGeometryColumn = self.geomCombo.currentText() self.geomCombo.clear() self.geomCombo.addItems(cols) self.geomCombo.setCurrentIndex( self.geomCombo.findText(oldGeometryColumn, Qt.MatchExactly)) # set sensible default columns if the columns are not already set try: if self.geomCombo.currentIndex() == -1: self.geomCombo.setCurrentIndex(cols.index(defaultGeomCol)) except: pass items = self.uniqueModel.findItems(defaultUniqueCol) if items and not uniqueIsFilled: if self.allowMultiColumnPk: items[0].setCheckState(Qt.Checked) else: self.uniqueCombo.setEditText(defaultUniqueCol) try: pass except: pass def copySelectedResults(self): if len(self.viewResult.selectedIndexes()) <= 0: return model = self.viewResult.model() # convert to string using tab as separator text = model.headerToString("\t") for idx in self.viewResult.selectionModel().selectedRows(): text += "\n" + model.rowToString(idx.row(), "\t") QApplication.clipboard().setText(text, QClipboard.Selection) QApplication.clipboard().setText(text, QClipboard.Clipboard) def initCompleter(self): dictionary = None if self.db: dictionary = self.db.connector.getSqlDictionary() if not dictionary: # use the generic sql dictionary from .sql_dictionary import getSqlDictionary dictionary = getSqlDictionary() wordlist = [] for name, value in dictionary.items(): wordlist += value # concat lists wordlist = list(set(wordlist)) # remove duplicates api = QsciAPIs(self.editSql.lexer()) for word in wordlist: api.add(word) api.prepare() self.editSql.lexer().setAPIs(api) def displayQueryBuilder(self): dlg = QueryBuilderDlg(self.iface, self.db, self, reset=self.queryBuilderFirst) self.queryBuilderFirst = False r = dlg.exec_() if r == QDialog.Accepted: self.editSql.setText(dlg.query) def _getSqlQuery(self): sql = self.editSql.selectedText() if len(sql) == 0: sql = self.editSql.text() return sql def uniqueChanged(self): # when an item is (un)checked, simply trigger an update of the combobox text self.uniqueTextChanged(None) def uniqueTextChanged(self, text): # Whenever there is new text displayed in the combobox, check if it is the correct one and if not, display the correct one. checkedItems = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedItems.append(item.text()) label = ", ".join(checkedItems) if text != label: self.uniqueCombo.setEditText(label) def setFilter(self): from qgis.gui import QgsQueryBuilder layer = self._getSqlLayer("") if not layer: return dlg = QgsQueryBuilder(layer) dlg.setSql(self.filter) if dlg.exec_(): self.filter = dlg.sql() layer.deleteLater()
class MultipleSelectTreeView(QListView): """ Custom QListView implementation that displays checkable items from a multiple select column type. """ def __init__(self, column, parent=None): """ Class constructor. :param column: Multiple select column object. :type column: MultipleSelectColumn :param parent: Parent widget for the control. :type parent: QWidget """ QListView.__init__(self, parent) # Disable editing of lookup values self.setEditTriggers(QAbstractItemView.NoEditTriggers) self.column = column self._item_model = QStandardItemModel(self) self._value_list = self.column.value_list # Stores lookup objects based on primary keys self._lookup_cache = {} self._initialize() self._association = self.column.association self._first_parent_col = self._association.first_reference_column.name self._second_parent_col = self._association.second_reference_column.name # Association model self._assoc_cls = entity_model(self._association) def reset_model(self): """ Resets the item model. """ self._item_model.clear() self._item_model.setColumnCount(2) def clear(self): """ Clears all items in the model. """ self._item_model.clear() @property def association(self): """ :return: Returns the association object corresponding to the column. :rtype: AssociationEntity """ return self._association @property def value_list(self): """ :return: Returns the ValueList object corresponding to the configured column object. :rtype: ValueList """ return self._value_list @property def item_model(self): """ :return: Returns the model corresponding to the checkable items. :rtype: QStandardItemModel """ return self._item_model def _add_item(self, id, value): """ Adds a row corresponding to id and corresponding value from a lookup table. :param id: Primary key of a lookup record. :type id: int :param value: Lookup value :type value: str """ value_item = QStandardItem(value) value_item.setCheckable(True) id_item = QStandardItem(str(id)) self._item_model.appendRow([value_item, id_item]) def _initialize(self): # Populate list with lookup items self.reset_model() # Add all lookup values in the value list table vl_cls = entity_model(self._value_list) if not vl_cls is None: vl_obj = vl_cls() res = vl_obj.queryObject().all() for r in res: self._lookup_cache[r.id] = r self._add_item(r.id, r.value) self.setModel(self._item_model) def clear_selection(self): """ Unchecks all items in the view. """ for i in range(self._item_model.rowCount()): value_item = self._item_model.item(i, 0) if value_item.checkState() == Qt.Checked: value_item.setCheckState(Qt.Unchecked) if value_item.rowCount() > 0: value_item.removeRow(0) def selection(self): """ :return: Returns a list of selected items. :rtype: list """ selection = [] for i in range(self._item_model.rowCount()): value_item = self._item_model.item(i, 0) if value_item.checkState() == Qt.Checked: id_item = self._item_model.item(i, 1) id = int(id_item.text()) # Get item from the lookup cache and append to selection if id in self._lookup_cache: lookup_rec = self._lookup_cache[id] selection.append(lookup_rec) return selection def set_selection(self, models): """ Checks items corresponding to the specified models. :param models: List containing model values in the view for selection. :type models: list """ for m in models: search_value = m.value v_items = self._item_model.findItems(search_value) # Loop through result and check items for vi in v_items: if vi.checkState() == Qt.Unchecked: vi.setCheckState(Qt.Checked)
class DlgSqlWindow(QWidget, Ui_Dialog): nameChanged = pyqtSignal(str) def __init__(self, iface, db, parent=None): QWidget.__init__(self, parent) self.iface = iface self.db = db self.filter = "" self.allowMultiColumnPk = isinstance(db, PGDatabase) # at the moment only PostgreSQL allows a primary key to span multiple columns, spatialite doesn't self.aliasSubQuery = isinstance(db, PGDatabase) # only PostgreSQL requires subqueries to be aliases self.setupUi(self) self.setWindowTitle( u"%s - %s [%s]" % (self.windowTitle(), db.connection().connectionName(), db.connection().typeNameString())) self.defaultLayerName = 'QueryLayer' if self.allowMultiColumnPk: self.uniqueColumnCheck.setText(self.tr("Column(s) with unique values")) else: self.uniqueColumnCheck.setText(self.tr("Column with unique values")) self.editSql.setFocus() self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded) self.editSql.setMarginVisible(True) self.initCompleter() # allow copying results copyAction = QAction("copy", self) self.viewResult.addAction(copyAction) copyAction.setShortcuts(QKeySequence.Copy) copyAction.triggered.connect(self.copySelectedResults) self.btnExecute.clicked.connect(self.executeSql) self.btnSetFilter.clicked.connect(self.setFilter) self.btnClear.clicked.connect(self.clearSql) self.presetStore.clicked.connect(self.storePreset) self.presetDelete.clicked.connect(self.deletePreset) self.presetCombo.activated[str].connect(self.loadPreset) self.presetCombo.activated[str].connect(self.presetName.setText) self.updatePresetsCombobox() self.geomCombo.setEditable(True) self.geomCombo.lineEdit().setReadOnly(True) self.uniqueCombo.setEditable(True) self.uniqueCombo.lineEdit().setReadOnly(True) self.uniqueModel = QStandardItemModel(self.uniqueCombo) self.uniqueCombo.setModel(self.uniqueModel) if self.allowMultiColumnPk: self.uniqueCombo.setItemDelegate(QStyledItemDelegate()) self.uniqueModel.itemChanged.connect(self.uniqueChanged) # react to the (un)checking of an item 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 # hide the load query as layer if feature is not supported self._loadAsLayerAvailable = self.db.connector.hasCustomQuerySupport() self.loadAsLayerGroup.setVisible(self._loadAsLayerAvailable) if self._loadAsLayerAvailable: self.layerTypeWidget.hide() # show if load as raster is supported self.loadLayerBtn.clicked.connect(self.loadSqlLayer) self.getColumnsBtn.clicked.connect(self.fillColumnCombos) self.loadAsLayerGroup.toggled.connect(self.loadAsLayerToggled) self.loadAsLayerToggled(False) self._createViewAvailable = self.db.connector.hasCreateSpatialViewSupport() self.btnCreateView.setVisible(self._createViewAvailable) if self._createViewAvailable: self.btnCreateView.clicked.connect(self.createView) self.queryBuilderFirst = True self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif")) self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder) self.presetName.textChanged.connect(self.nameChanged) def updatePresetsCombobox(self): self.presetCombo.clear() names = [] entries = QgsProject.instance().subkeyList('DBManager', 'savedQueries') for entry in entries: name = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + entry + '/name')[0] names.append(name) for name in sorted(names): self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(-1) def storePreset(self): query = self._getSqlQuery() if query == "": return name = self.presetName.text() QgsProject.instance().writeEntry('DBManager', 'savedQueries/q' + str(name.__hash__()) + '/name', name) QgsProject.instance().writeEntry('DBManager', 'savedQueries/q' + str(name.__hash__()) + '/query', query) index = self.presetCombo.findText(name) if index == -1: self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(self.presetCombo.count() - 1) else: self.presetCombo.setCurrentIndex(index) def deletePreset(self): name = self.presetCombo.currentText() QgsProject.instance().removeEntry('DBManager', 'savedQueries/q' + str(name.__hash__())) self.presetCombo.removeItem(self.presetCombo.findText(name)) self.presetCombo.setCurrentIndex(-1) def loadPreset(self, name): query = QgsProject.instance().readEntry('DBManager', 'savedQueries/q' + str(name.__hash__()) + '/query')[0] name = QgsProject.instance().readEntry('DBManager', 'savedQueries/q' + str(name.__hash__()) + '/name')[0] self.editSql.setText(query) def loadAsLayerToggled(self, checked): self.loadAsLayerGroup.setChecked(checked) self.loadAsLayerWidget.setVisible(checked) if checked: self.fillColumnCombos() def clearSql(self): self.editSql.clear() self.editSql.setFocus() self.filter = "" def executeSql(self): sql = self._getSqlQuery() if sql == "": return QApplication.setOverrideCursor(QCursor(Qt.WaitCursor)) # delete the old model old_model = self.viewResult.model() self.viewResult.setModel(None) if old_model: old_model.deleteLater() cols = [] quotedCols = [] try: # set the new model model = self.db.sqlResultModel(sql, self) self.viewResult.setModel(model) self.lblResult.setText(self.tr("%d rows, %.1f seconds") % (model.affectedRows(), model.secs())) cols = self.viewResult.model().columnNames() for col in cols: quotedCols.append(self.db.connector.quoteId(col)) except BaseError as e: QApplication.restoreOverrideCursor() DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return self.setColumnCombos(cols, quotedCols) self.update() QApplication.restoreOverrideCursor() def _getSqlLayer(self, _filter): hasUniqueField = self.uniqueColumnCheck.checkState() == Qt.Checked if hasUniqueField: if self.allowMultiColumnPk: checkedCols = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedCols.append(item.data()) uniqueFieldName = ",".join(checkedCols) elif self.uniqueCombo.currentIndex() >= 0: uniqueFieldName = self.uniqueModel.item(self.uniqueCombo.currentIndex()).data() else: uniqueFieldName = None else: uniqueFieldName = None hasGeomCol = self.hasGeometryCol.checkState() == Qt.Checked if hasGeomCol: geomFieldName = self.geomCombo.currentText() else: geomFieldName = None query = self._getSqlQuery() if query == "": return None # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] from qgis.core import QgsMapLayer layerType = QgsMapLayer.VectorLayer if self.vectorRadio.isChecked() else QgsMapLayer.RasterLayer # get a new layer name names = [] for layer in list(QgsProject.instance().mapLayers().values()): names.append(layer.name()) layerName = self.layerNameEdit.text() if layerName == "": layerName = self.defaultLayerName newLayerName = layerName index = 1 while newLayerName in names: index += 1 newLayerName = u"%s_%d" % (layerName, index) # create the layer layer = self.db.toSqlLayer(query, geomFieldName, uniqueFieldName, newLayerName, layerType, self.avoidSelectById.isChecked(), _filter) if layer.isValid(): return layer else: return None def loadSqlLayer(self): QApplication.setOverrideCursor(QCursor(Qt.WaitCursor)) try: layer = self._getSqlLayer(self.filter) if layer is None: return QgsProject.instance().addMapLayers([layer], True) finally: QApplication.restoreOverrideCursor() def fillColumnCombos(self): query = self._getSqlQuery() if query == "": return QApplication.setOverrideCursor(QCursor(Qt.WaitCursor)) # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] # get all the columns cols = [] quotedCols = [] connector = self.db.connector if self.aliasSubQuery: # get a new alias aliasIndex = 0 while True: alias = "_subQuery__%d" % aliasIndex escaped = re.compile('\\b("?)' + re.escape(alias) + '\\1\\b') if not escaped.search(query): break aliasIndex += 1 sql = u"SELECT * FROM (%s\n) AS %s LIMIT 0" % (str(query), connector.quoteId(alias)) else: sql = u"SELECT * FROM (%s\n) WHERE 1=0" % str(query) c = None try: c = connector._execute(None, sql) cols = connector._get_cursor_columns(c) for col in cols: quotedCols.append(connector.quoteId(col)) except BaseError as e: QApplication.restoreOverrideCursor() DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return finally: if c: c.close() del c self.setColumnCombos(cols, quotedCols) QApplication.restoreOverrideCursor() def setColumnCombos(self, cols, quotedCols): # 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) try: defaultGeomCol = next(col for col in cols if col in ['geom', 'geometry', 'the_geom', 'way']) except: defaultGeomCol = None try: defaultUniqueCol = [col for col in cols if 'id' in col][0] except: defaultUniqueCol = None colNames = sorted(zip(cols, quotedCols)) newItems = [] uniqueIsFilled = False for (col, quotedCol) in colNames: item = QStandardItem(col) item.setData(quotedCol) item.setEnabled(True) item.setCheckable(self.allowMultiColumnPk) item.setSelectable(not self.allowMultiColumnPk) if self.allowMultiColumnPk: matchingItems = self.uniqueModel.findItems(col) if matchingItems: item.setCheckState(matchingItems[0].checkState()) uniqueIsFilled = uniqueIsFilled or matchingItems[0].checkState() == Qt.Checked else: item.setCheckState(Qt.Unchecked) newItems.append(item) if self.allowMultiColumnPk: self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) self.uniqueChanged() else: previousUniqueColumn = self.uniqueCombo.currentText() self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) if self.uniqueModel.findItems(previousUniqueColumn): self.uniqueCombo.setEditText(previousUniqueColumn) uniqueIsFilled = True oldGeometryColumn = self.geomCombo.currentText() self.geomCombo.clear() self.geomCombo.addItems(cols) self.geomCombo.setCurrentIndex(self.geomCombo.findText(oldGeometryColumn, Qt.MatchExactly)) # set sensible default columns if the columns are not already set try: if self.geomCombo.currentIndex() == -1: self.geomCombo.setCurrentIndex(cols.index(defaultGeomCol)) except: pass items = self.uniqueModel.findItems(defaultUniqueCol) if items and not uniqueIsFilled: if self.allowMultiColumnPk: items[0].setCheckState(Qt.Checked) else: self.uniqueCombo.setEditText(defaultUniqueCol) try: pass except: pass def copySelectedResults(self): if len(self.viewResult.selectedIndexes()) <= 0: return model = self.viewResult.model() # convert to string using tab as separator text = model.headerToString("\t") for idx in self.viewResult.selectionModel().selectedRows(): text += "\n" + model.rowToString(idx.row(), "\t") QApplication.clipboard().setText(text, QClipboard.Selection) QApplication.clipboard().setText(text, QClipboard.Clipboard) def initCompleter(self): dictionary = None if self.db: dictionary = self.db.connector.getSqlDictionary() if not dictionary: # use the generic sql dictionary from .sql_dictionary import getSqlDictionary dictionary = getSqlDictionary() wordlist = [] for name, value in list(dictionary.items()): wordlist += value # concat lists wordlist = list(set(wordlist)) # remove duplicates api = QsciAPIs(self.editSql.lexer()) for word in wordlist: api.add(word) api.prepare() self.editSql.lexer().setAPIs(api) def displayQueryBuilder(self): dlg = QueryBuilderDlg(self.iface, self.db, self, reset=self.queryBuilderFirst) self.queryBuilderFirst = False r = dlg.exec_() if r == QDialog.Accepted: self.editSql.setText(dlg.query) def createView(self): name, ok = QInputDialog.getText(None, "View name", "View name") if ok: try: self.db.connector.createSpatialView(name, self._getSqlQuery()) except BaseError as e: DlgDbError.showError(e, self) def _getSqlQuery(self): sql = self.editSql.selectedText() if len(sql) == 0: sql = self.editSql.text() return sql def uniqueChanged(self): # when an item is (un)checked, simply trigger an update of the combobox text self.uniqueTextChanged(None) def uniqueTextChanged(self, text): # Whenever there is new text displayed in the combobox, check if it is the correct one and if not, display the correct one. checkedItems = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedItems.append(item.text()) label = ", ".join(checkedItems) if text != label: self.uniqueCombo.setEditText(label) def setFilter(self): from qgis.gui import QgsQueryBuilder layer = self._getSqlLayer("") if not layer: return dlg = QgsQueryBuilder(layer) dlg.setSql(self.filter) if dlg.exec_(): self.filter = dlg.sql() layer.deleteLater()
class DlgSqlWindow(QWidget, Ui_Dialog): nameChanged = pyqtSignal(str) QUERY_HISTORY_LIMIT = 20 def __init__(self, iface, db, parent=None): QWidget.__init__(self, parent) self.mainWindow = parent self.iface = iface self.db = db self.dbType = db.connection().typeNameString() self.connectionName = db.connection().connectionName() self.filter = "" self.modelAsync = None self.allowMultiColumnPk = isinstance(db, PGDatabase) # at the moment only PostgreSQL allows a primary key to span multiple columns, SpatiaLite doesn't self.aliasSubQuery = isinstance(db, PGDatabase) # only PostgreSQL requires subqueries to be aliases self.setupUi(self) self.setWindowTitle( self.tr(u"{0} - {1} [{2}]").format(self.windowTitle(), self.connectionName, self.dbType)) self.defaultLayerName = self.tr('QueryLayer') if self.allowMultiColumnPk: self.uniqueColumnCheck.setText(self.tr("Column(s) with unique values")) else: self.uniqueColumnCheck.setText(self.tr("Column with unique values")) self.editSql.setFocus() self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded) self.editSql.setMarginVisible(True) self.initCompleter() settings = QgsSettings() self.history = settings.value('DB_Manager/queryHistory/' + self.dbType, {self.connectionName: []}) if self.connectionName not in self.history: self.history[self.connectionName] = [] self.queryHistoryWidget.setVisible(False) self.queryHistoryTableWidget.verticalHeader().hide() self.queryHistoryTableWidget.doubleClicked.connect(self.insertQueryInEditor) self.populateQueryHistory() self.btnQueryHistory.toggled.connect(self.showHideQueryHistory) self.btnCancel.setEnabled(False) self.btnCancel.clicked.connect(self.executeSqlCanceled) self.btnCancel.setShortcut(QKeySequence.Cancel) self.progressBar.setEnabled(False) self.progressBar.setRange(0, 100) self.progressBar.setValue(0) self.progressBar.setFormat("") self.progressBar.setAlignment(Qt.AlignCenter) # allow copying results copyAction = QAction("copy", self) self.viewResult.addAction(copyAction) copyAction.setShortcuts(QKeySequence.Copy) copyAction.triggered.connect(self.copySelectedResults) self.btnExecute.clicked.connect(self.executeSql) self.btnSetFilter.clicked.connect(self.setFilter) self.btnClear.clicked.connect(self.clearSql) self.presetStore.clicked.connect(self.storePreset) self.presetSaveAsFile.clicked.connect(self.saveAsFilePreset) self.presetLoadFile.clicked.connect(self.loadFilePreset) self.presetDelete.clicked.connect(self.deletePreset) self.presetCombo.activated[str].connect(self.loadPreset) self.presetCombo.activated[str].connect(self.presetName.setText) self.updatePresetsCombobox() self.geomCombo.setEditable(True) self.geomCombo.lineEdit().setReadOnly(True) self.uniqueCombo.setEditable(True) self.uniqueCombo.lineEdit().setReadOnly(True) self.uniqueModel = QStandardItemModel(self.uniqueCombo) self.uniqueCombo.setModel(self.uniqueModel) if self.allowMultiColumnPk: self.uniqueCombo.setItemDelegate(QStyledItemDelegate()) self.uniqueModel.itemChanged.connect(self.uniqueChanged) # react to the (un)checking of an item 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 # hide the load query as layer if feature is not supported self._loadAsLayerAvailable = self.db.connector.hasCustomQuerySupport() self.loadAsLayerGroup.setVisible(self._loadAsLayerAvailable) if self._loadAsLayerAvailable: self.layerTypeWidget.hide() # show if load as raster is supported self.loadLayerBtn.clicked.connect(self.loadSqlLayer) self.getColumnsBtn.clicked.connect(self.fillColumnCombos) self.loadAsLayerGroup.toggled.connect(self.loadAsLayerToggled) self.loadAsLayerToggled(False) self._createViewAvailable = self.db.connector.hasCreateSpatialViewSupport() self.btnCreateView.setVisible(self._createViewAvailable) if self._createViewAvailable: self.btnCreateView.clicked.connect(self.createView) self.queryBuilderFirst = True self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif")) self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder) self.presetName.textChanged.connect(self.nameChanged) def insertQueryInEditor(self, item): sql = item.data(Qt.DisplayRole) self.editSql.insertText(sql) def showHideQueryHistory(self, visible): self.queryHistoryWidget.setVisible(visible) def populateQueryHistory(self): self.queryHistoryTableWidget.clearContents() self.queryHistoryTableWidget.setRowCount(0) dictlist = self.history[self.connectionName] if not dictlist: return for i in range(len(dictlist)): self.queryHistoryTableWidget.insertRow(0) queryItem = QTableWidgetItem(dictlist[i]['query']) rowsItem = QTableWidgetItem(str(dictlist[i]['rows'])) durationItem = QTableWidgetItem(str(dictlist[i]['secs'])) self.queryHistoryTableWidget.setItem(0, 0, queryItem) self.queryHistoryTableWidget.setItem(0, 1, rowsItem) self.queryHistoryTableWidget.setItem(0, 2, durationItem) self.queryHistoryTableWidget.resizeColumnsToContents() self.queryHistoryTableWidget.resizeRowsToContents() def writeQueryHistory(self, sql, affectedRows, secs): if len(self.history[self.connectionName]) >= self.QUERY_HISTORY_LIMIT: self.history[self.connectionName].pop(0) settings = QgsSettings() self.history[self.connectionName].append({'query': sql, 'rows': affectedRows, 'secs': secs}) settings.setValue('DB_Manager/queryHistory/' + self.dbType, self.history) self.populateQueryHistory() def getQueryHash(self, name): return 'q%s' % md5(name.encode('utf8')).hexdigest() def updatePresetsCombobox(self): self.presetCombo.clear() names = [] entries = QgsProject.instance().subkeyList('DBManager', 'savedQueries') for entry in entries: name = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + entry + '/name')[0] names.append(name) for name in sorted(names): self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(-1) def storePreset(self): query = self._getSqlQuery() if query == "": return name = str(self.presetName.text()) QgsProject.instance().writeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/name', name) QgsProject.instance().writeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/query', query) index = self.presetCombo.findText(name) if index == -1: self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(self.presetCombo.count() - 1) else: self.presetCombo.setCurrentIndex(index) def saveAsFilePreset(self): settings = QgsSettings() lastDir = settings.value('DB_Manager/lastDirSQLFIle', "") query = self.editSql.text() if query == "": return filename, _ = QFileDialog.getSaveFileName( self, self.tr('Save SQL Query'), lastDir, self.tr("SQL File (*.sql *.SQL)")) if filename: if not filename.lower().endswith('.sql'): filename += ".sql" with open(filename, 'w') as f: f.write(query) lastDir = os.path.dirname(filename) settings.setValue('DB_Manager/lastDirSQLFile', lastDir) def loadFilePreset(self): settings = QgsSettings() lastDir = settings.value('DB_Manager/lastDirSQLFIle', "") filename, _ = QFileDialog.getOpenFileName( self, self.tr("Load SQL Query"), lastDir, self.tr("SQL File (*.sql *.SQL);;All Files (*)")) if filename: with open(filename, 'r') as f: self.editSql.clear() for line in f: self.editSql.insertText(line) lastDir = os.path.dirname(filename) settings.setValue('DB_Manager/lastDirSQLFile', lastDir) def deletePreset(self): name = self.presetCombo.currentText() QgsProject.instance().removeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name)) self.presetCombo.removeItem(self.presetCombo.findText(name)) self.presetCombo.setCurrentIndex(-1) def loadPreset(self, name): query = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/query')[0] self.editSql.setText(query) def loadAsLayerToggled(self, checked): self.loadAsLayerGroup.setChecked(checked) self.loadAsLayerWidget.setVisible(checked) if checked: self.fillColumnCombos() def clearSql(self): self.editSql.clear() self.editSql.setFocus() self.filter = "" def updateUiWhileSqlExecution(self, status): if status: for i in range(0, self.mainWindow.tabs.count()): if i != self.mainWindow.tabs.currentIndex(): self.mainWindow.tabs.setTabEnabled(i, False) self.mainWindow.menuBar.setEnabled(False) self.mainWindow.toolBar.setEnabled(False) self.mainWindow.tree.setEnabled(False) for w in self.findChildren(QWidget): w.setEnabled(False) self.btnCancel.setEnabled(True) self.progressBar.setEnabled(True) self.progressBar.setRange(0, 0) else: for i in range(0, self.mainWindow.tabs.count()): if i != self.mainWindow.tabs.currentIndex(): self.mainWindow.tabs.setTabEnabled(i, True) self.mainWindow.refreshTabs() self.mainWindow.menuBar.setEnabled(True) self.mainWindow.toolBar.setEnabled(True) self.mainWindow.tree.setEnabled(True) for w in self.findChildren(QWidget): w.setEnabled(True) self.btnCancel.setEnabled(False) self.progressBar.setRange(0, 100) self.progressBar.setEnabled(False) def executeSqlCanceled(self): self.btnCancel.setEnabled(False) self.modelAsync.cancel() def executeSqlCompleted(self): self.updateUiWhileSqlExecution(False) with OverrideCursor(Qt.WaitCursor): if self.modelAsync.task.status() == QgsTask.Complete: model = self.modelAsync.model quotedCols = [] self.viewResult.setModel(model) self.lblResult.setText(self.tr("{0} rows, {1:.3f} seconds").format(model.affectedRows(), model.secs())) cols = self.viewResult.model().columnNames() for col in cols: quotedCols.append(self.db.connector.quoteId(col)) self.setColumnCombos(cols, quotedCols) self.writeQueryHistory(self.modelAsync.task.sql, model.affectedRows(), model.secs()) self.update() elif not self.modelAsync.canceled: DlgDbError.showError(self.modelAsync.error, self) self.uniqueModel.clear() self.geomCombo.clear() def executeSql(self): sql = self._getExecutableSqlQuery() if sql == "": return # delete the old model old_model = self.viewResult.model() self.viewResult.setModel(None) if old_model: old_model.deleteLater() try: self.modelAsync = self.db.sqlResultModelAsync(sql, self) self.modelAsync.done.connect(self.executeSqlCompleted) self.updateUiWhileSqlExecution(True) QgsApplication.taskManager().addTask(self.modelAsync.task) except Exception as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return def _getSqlLayer(self, _filter): hasUniqueField = self.uniqueColumnCheck.checkState() == Qt.Checked if hasUniqueField: if self.allowMultiColumnPk: checkedCols = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedCols.append(item.data()) uniqueFieldName = ",".join(checkedCols) elif self.uniqueCombo.currentIndex() >= 0: uniqueFieldName = self.uniqueModel.item(self.uniqueCombo.currentIndex()).data() else: uniqueFieldName = None else: uniqueFieldName = None hasGeomCol = self.hasGeometryCol.checkState() == Qt.Checked if hasGeomCol: geomFieldName = self.geomCombo.currentText() else: geomFieldName = None query = self._getExecutableSqlQuery() if query == "": return None # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] layerType = QgsMapLayerType.VectorLayer if self.vectorRadio.isChecked() else QgsMapLayerType.RasterLayer # get a new layer name names = [] for layer in list(QgsProject.instance().mapLayers().values()): names.append(layer.name()) layerName = self.layerNameEdit.text() if layerName == "": layerName = self.defaultLayerName newLayerName = layerName index = 1 while newLayerName in names: index += 1 newLayerName = u"%s_%d" % (layerName, index) # create the layer layer = self.db.toSqlLayer(query, geomFieldName, uniqueFieldName, newLayerName, layerType, self.avoidSelectById.isChecked(), _filter) if layer.isValid(): return layer else: e = BaseError(self.tr("There was an error creating the SQL layer, please check the logs for further information.")) DlgDbError.showError(e, self) return None def loadSqlLayer(self): with OverrideCursor(Qt.WaitCursor): layer = self._getSqlLayer(self.filter) if layer is None: return QgsProject.instance().addMapLayers([layer], True) def fillColumnCombos(self): query = self._getExecutableSqlQuery() if query == "": return with OverrideCursor(Qt.WaitCursor): # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] # get all the columns quotedCols = [] connector = self.db.connector if self.aliasSubQuery: # get a new alias aliasIndex = 0 while True: alias = "_subQuery__%d" % aliasIndex escaped = re.compile('\\b("?)' + re.escape(alias) + '\\1\\b') if not escaped.search(query): break aliasIndex += 1 sql = u"SELECT * FROM (%s\n) AS %s LIMIT 0" % (str(query), connector.quoteId(alias)) else: sql = u"SELECT * FROM (%s\n) WHERE 1=0" % str(query) c = None try: c = connector._execute(None, sql) cols = connector._get_cursor_columns(c) for col in cols: quotedCols.append(connector.quoteId(col)) except BaseError as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return finally: if c: c.close() del c self.setColumnCombos(cols, quotedCols) def setColumnCombos(self, cols, quotedCols): # get sensible default columns. do this before sorting in case there's hints in the column order (e.g., id is more likely to be first) try: defaultGeomCol = next(col for col in cols if col in ['geom', 'geometry', 'the_geom', 'way']) except: defaultGeomCol = None try: defaultUniqueCol = [col for col in cols if 'id' in col][0] except: defaultUniqueCol = None colNames = sorted(zip(cols, quotedCols)) newItems = [] uniqueIsFilled = False for (col, quotedCol) in colNames: item = QStandardItem(col) item.setData(quotedCol) item.setEnabled(True) item.setCheckable(self.allowMultiColumnPk) item.setSelectable(not self.allowMultiColumnPk) if self.allowMultiColumnPk: matchingItems = self.uniqueModel.findItems(col) if matchingItems: item.setCheckState(matchingItems[0].checkState()) uniqueIsFilled = uniqueIsFilled or matchingItems[0].checkState() == Qt.Checked else: item.setCheckState(Qt.Unchecked) newItems.append(item) if self.allowMultiColumnPk: self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) self.uniqueChanged() else: previousUniqueColumn = self.uniqueCombo.currentText() self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) if self.uniqueModel.findItems(previousUniqueColumn): self.uniqueCombo.setEditText(previousUniqueColumn) uniqueIsFilled = True oldGeometryColumn = self.geomCombo.currentText() self.geomCombo.clear() self.geomCombo.addItems(cols) self.geomCombo.setCurrentIndex(self.geomCombo.findText(oldGeometryColumn, Qt.MatchExactly)) # set sensible default columns if the columns are not already set try: if self.geomCombo.currentIndex() == -1: self.geomCombo.setCurrentIndex(cols.index(defaultGeomCol)) except: pass items = self.uniqueModel.findItems(defaultUniqueCol) if items and not uniqueIsFilled: if self.allowMultiColumnPk: items[0].setCheckState(Qt.Checked) else: self.uniqueCombo.setEditText(defaultUniqueCol) def copySelectedResults(self): if len(self.viewResult.selectedIndexes()) <= 0: return model = self.viewResult.model() # convert to string using tab as separator text = model.headerToString("\t") for idx in self.viewResult.selectionModel().selectedRows(): text += "\n" + model.rowToString(idx.row(), "\t") QApplication.clipboard().setText(text, QClipboard.Selection) QApplication.clipboard().setText(text, QClipboard.Clipboard) def initCompleter(self): dictionary = None if self.db: dictionary = self.db.connector.getSqlDictionary() if not dictionary: # use the generic sql dictionary from .sql_dictionary import getSqlDictionary dictionary = getSqlDictionary() wordlist = [] for value in dictionary.values(): wordlist += value # concat lists wordlist = list(set(wordlist)) # remove duplicates api = QsciAPIs(self.editSql.lexer()) for word in wordlist: api.add(word) api.prepare() self.editSql.lexer().setAPIs(api) def displayQueryBuilder(self): dlg = QueryBuilderDlg(self.iface, self.db, self, reset=self.queryBuilderFirst) self.queryBuilderFirst = False r = dlg.exec_() if r == QDialog.Accepted: self.editSql.setText(dlg.query) def createView(self): name, ok = QInputDialog.getText(None, self.tr("View Name"), self.tr("View name")) if ok: try: self.db.connector.createSpatialView(name, self._getExecutableSqlQuery()) except BaseError as e: DlgDbError.showError(e, self) def _getSqlQuery(self): sql = self.editSql.selectedText() if len(sql) == 0: sql = self.editSql.text() return sql def _getExecutableSqlQuery(self): sql = self._getSqlQuery() uncommented_sql = check_comments_in_sql(sql) return uncommented_sql def uniqueChanged(self): # when an item is (un)checked, simply trigger an update of the combobox text self.uniqueTextChanged(None) def uniqueTextChanged(self, text): # Whenever there is new text displayed in the combobox, check if it is the correct one and if not, display the correct one. checkedItems = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedItems.append(item.text()) label = ", ".join(checkedItems) if text != label: self.uniqueCombo.setEditText(label) def setFilter(self): from qgis.gui import QgsQueryBuilder layer = self._getSqlLayer("") if not layer: return dlg = QgsQueryBuilder(layer) dlg.setSql(self.filter) if dlg.exec_(): self.filter = dlg.sql() layer.deleteLater()
class DlgSqlWindow(QWidget, Ui_Dialog): nameChanged = pyqtSignal(str) QUERY_HISTORY_LIMIT = 20 def __init__(self, iface, db, parent=None): QWidget.__init__(self, parent) self.mainWindow = parent self.iface = iface self.db = db self.dbType = db.connection().typeNameString() self.connectionName = db.connection().connectionName() self.filter = "" self.modelAsync = None self.allowMultiColumnPk = isinstance(db, PGDatabase) # at the moment only PostgreSQL allows a primary key to span multiple columns, SpatiaLite doesn't self.aliasSubQuery = isinstance(db, PGDatabase) # only PostgreSQL requires subqueries to be aliases self.setupUi(self) self.setWindowTitle( self.tr(u"{0} - {1} [{2}]").format(self.windowTitle(), self.connectionName, self.dbType)) self.defaultLayerName = self.tr('QueryLayer') if self.allowMultiColumnPk: self.uniqueColumnCheck.setText(self.tr("Column(s) with unique values")) else: self.uniqueColumnCheck.setText(self.tr("Column with unique values")) self.editSql.setFocus() self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded) self.editSql.setMarginVisible(True) self.initCompleter() settings = QgsSettings() self.history = settings.value('DB_Manager/queryHistory/' + self.dbType, {self.connectionName: []}) if self.connectionName not in self.history: self.history[self.connectionName] = [] self.queryHistoryWidget.setVisible(False) self.queryHistoryTableWidget.verticalHeader().hide() self.queryHistoryTableWidget.doubleClicked.connect(self.insertQueryInEditor) self.populateQueryHistory() self.btnQueryHistory.toggled.connect(self.showHideQueryHistory) self.btnCancel.setEnabled(False) self.btnCancel.clicked.connect(self.executeSqlCanceled) self.btnCancel.setShortcut(QKeySequence.Cancel) self.progressBar.setEnabled(False) self.progressBar.setRange(0, 100) self.progressBar.setValue(0) self.progressBar.setFormat("") self.progressBar.setAlignment(Qt.AlignCenter) # allow copying results copyAction = QAction("copy", self) self.viewResult.addAction(copyAction) copyAction.setShortcuts(QKeySequence.Copy) copyAction.triggered.connect(self.copySelectedResults) self.btnExecute.clicked.connect(self.executeSql) self.btnSetFilter.clicked.connect(self.setFilter) self.btnClear.clicked.connect(self.clearSql) self.presetStore.clicked.connect(self.storePreset) self.presetSaveAsFile.clicked.connect(self.saveAsFilePreset) self.presetLoadFile.clicked.connect(self.loadFilePreset) self.presetDelete.clicked.connect(self.deletePreset) self.presetCombo.activated[str].connect(self.loadPreset) self.presetCombo.activated[str].connect(self.presetName.setText) self.updatePresetsCombobox() self.geomCombo.setEditable(True) self.geomCombo.lineEdit().setReadOnly(True) self.uniqueCombo.setEditable(True) self.uniqueCombo.lineEdit().setReadOnly(True) self.uniqueModel = QStandardItemModel(self.uniqueCombo) self.uniqueCombo.setModel(self.uniqueModel) if self.allowMultiColumnPk: self.uniqueCombo.setItemDelegate(QStyledItemDelegate()) self.uniqueModel.itemChanged.connect(self.uniqueChanged) # react to the (un)checking of an item 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 # hide the load query as layer if feature is not supported self._loadAsLayerAvailable = self.db.connector.hasCustomQuerySupport() self.loadAsLayerGroup.setVisible(self._loadAsLayerAvailable) if self._loadAsLayerAvailable: self.layerTypeWidget.hide() # show if load as raster is supported self.loadLayerBtn.clicked.connect(self.loadSqlLayer) self.getColumnsBtn.clicked.connect(self.fillColumnCombos) self.loadAsLayerGroup.toggled.connect(self.loadAsLayerToggled) self.loadAsLayerToggled(False) self._createViewAvailable = self.db.connector.hasCreateSpatialViewSupport() self.btnCreateView.setVisible(self._createViewAvailable) if self._createViewAvailable: self.btnCreateView.clicked.connect(self.createView) self.queryBuilderFirst = True self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif")) self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder) self.presetName.textChanged.connect(self.nameChanged) def insertQueryInEditor(self, item): sql = item.data(Qt.DisplayRole) self.editSql.insertText(sql) def showHideQueryHistory(self, visible): self.queryHistoryWidget.setVisible(visible) def populateQueryHistory(self): self.queryHistoryTableWidget.clearContents() self.queryHistoryTableWidget.setRowCount(0) dictlist = self.history[self.connectionName] if not dictlist: return for i in range(len(dictlist)): self.queryHistoryTableWidget.insertRow(0) queryItem = QTableWidgetItem(dictlist[i]['query']) rowsItem = QTableWidgetItem(str(dictlist[i]['rows'])) durationItem = QTableWidgetItem(str(dictlist[i]['secs'])) self.queryHistoryTableWidget.setItem(0, 0, queryItem) self.queryHistoryTableWidget.setItem(0, 1, rowsItem) self.queryHistoryTableWidget.setItem(0, 2, durationItem) self.queryHistoryTableWidget.resizeColumnsToContents() self.queryHistoryTableWidget.resizeRowsToContents() def writeQueryHistory(self, sql, affectedRows, secs): if len(self.history[self.connectionName]) >= self.QUERY_HISTORY_LIMIT: self.history[self.connectionName].pop(0) settings = QgsSettings() self.history[self.connectionName].append({'query': sql, 'rows': affectedRows, 'secs': secs}) settings.setValue('DB_Manager/queryHistory/' + self.dbType, self.history) self.populateQueryHistory() def getQueryHash(self, name): return 'q%s' % md5(name.encode('utf8')).hexdigest() def updatePresetsCombobox(self): self.presetCombo.clear() names = [] entries = QgsProject.instance().subkeyList('DBManager', 'savedQueries') for entry in entries: name = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + entry + '/name')[0] names.append(name) for name in sorted(names): self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(-1) def storePreset(self): query = self._getSqlQuery() if query == "": return name = str(self.presetName.text()) QgsProject.instance().writeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/name', name) QgsProject.instance().writeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/query', query) index = self.presetCombo.findText(name) if index == -1: self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(self.presetCombo.count() - 1) else: self.presetCombo.setCurrentIndex(index) def saveAsFilePreset(self): settings = QgsSettings() lastDir = settings.value('DB_Manager/lastDirSQLFIle', "") query = self._getSqlQuery() if query == "": return filename, _ = QFileDialog.getSaveFileName( self, self.tr('Save SQL Query'), lastDir, self.tr("SQL File (*.sql, *.SQL)")) if filename: if not filename.lower().endswith('.sql'): filename += ".sql" with open(filename, 'w') as f: f.write(query) lastDir = os.path.dirname(filename) settings.setValue('DB_Manager/lastDirSQLFile', lastDir) def loadFilePreset(self): settings = QgsSettings() lastDir = settings.value('DB_Manager/lastDirSQLFIle', "") filename, _ = QFileDialog.getOpenFileName( self, self.tr("Load SQL Query"), lastDir, self.tr("SQL File (*.sql, *.SQL)")) if filename: with open(filename, 'r') as f: self.editSql.clear() for line in f: self.editSql.insertText(line) lastDir = os.path.dirname(filename) settings.setValue('DB_Manager/lastDirSQLFile', lastDir) def deletePreset(self): name = self.presetCombo.currentText() QgsProject.instance().removeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name)) self.presetCombo.removeItem(self.presetCombo.findText(name)) self.presetCombo.setCurrentIndex(-1) def loadPreset(self, name): query = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/query')[0] self.editSql.setText(query) def loadAsLayerToggled(self, checked): self.loadAsLayerGroup.setChecked(checked) self.loadAsLayerWidget.setVisible(checked) if checked: self.fillColumnCombos() def clearSql(self): self.editSql.clear() self.editSql.setFocus() self.filter = "" def updateUiWhileSqlExecution(self, status): if status: for i in range(0, self.mainWindow.tabs.count()): if i != self.mainWindow.tabs.currentIndex(): self.mainWindow.tabs.setTabEnabled(i, False) self.mainWindow.menuBar.setEnabled(False) self.mainWindow.toolBar.setEnabled(False) self.mainWindow.tree.setEnabled(False) for w in self.findChildren(QWidget): w.setEnabled(False) self.btnCancel.setEnabled(True) self.progressBar.setEnabled(True) self.progressBar.setRange(0, 0) else: for i in range(0, self.mainWindow.tabs.count()): if i != self.mainWindow.tabs.currentIndex(): self.mainWindow.tabs.setTabEnabled(i, True) self.mainWindow.refreshTabs() self.mainWindow.menuBar.setEnabled(True) self.mainWindow.toolBar.setEnabled(True) self.mainWindow.tree.setEnabled(True) for w in self.findChildren(QWidget): w.setEnabled(True) self.btnCancel.setEnabled(False) self.progressBar.setRange(0, 100) self.progressBar.setEnabled(False) def executeSqlCanceled(self): self.btnCancel.setEnabled(False) self.modelAsync.cancel() def executeSqlCompleted(self): self.updateUiWhileSqlExecution(False) with OverrideCursor(Qt.WaitCursor): if self.modelAsync.task.status() == QgsTask.Complete: model = self.modelAsync.model quotedCols = [] self.viewResult.setModel(model) self.lblResult.setText(self.tr("{0} rows, {1:.3f} seconds").format(model.affectedRows(), model.secs())) cols = self.viewResult.model().columnNames() for col in cols: quotedCols.append(self.db.connector.quoteId(col)) self.setColumnCombos(cols, quotedCols) self.writeQueryHistory(self.modelAsync.task.sql, model.affectedRows(), model.secs()) self.update() elif not self.modelAsync.canceled: DlgDbError.showError(self.modelAsync.error, self) self.uniqueModel.clear() self.geomCombo.clear() def executeSql(self): sql = self._getExecutableSqlQuery() if sql == "": return # delete the old model old_model = self.viewResult.model() self.viewResult.setModel(None) if old_model: old_model.deleteLater() try: self.modelAsync = self.db.sqlResultModelAsync(sql, self) self.modelAsync.done.connect(self.executeSqlCompleted) self.updateUiWhileSqlExecution(True) QgsApplication.taskManager().addTask(self.modelAsync.task) except Exception as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return def _getSqlLayer(self, _filter): hasUniqueField = self.uniqueColumnCheck.checkState() == Qt.Checked if hasUniqueField: if self.allowMultiColumnPk: checkedCols = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedCols.append(item.data()) uniqueFieldName = ",".join(checkedCols) elif self.uniqueCombo.currentIndex() >= 0: uniqueFieldName = self.uniqueModel.item(self.uniqueCombo.currentIndex()).data() else: uniqueFieldName = None else: uniqueFieldName = None hasGeomCol = self.hasGeometryCol.checkState() == Qt.Checked if hasGeomCol: geomFieldName = self.geomCombo.currentText() else: geomFieldName = None query = self._getExecutableSqlQuery() if query == "": return None # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] layerType = QgsMapLayerType.VectorLayer if self.vectorRadio.isChecked() else QgsMapLayerType.RasterLayer # get a new layer name names = [] for layer in list(QgsProject.instance().mapLayers().values()): names.append(layer.name()) layerName = self.layerNameEdit.text() if layerName == "": layerName = self.defaultLayerName newLayerName = layerName index = 1 while newLayerName in names: index += 1 newLayerName = u"%s_%d" % (layerName, index) # create the layer layer = self.db.toSqlLayer(query, geomFieldName, uniqueFieldName, newLayerName, layerType, self.avoidSelectById.isChecked(), _filter) if layer.isValid(): return layer else: e = BaseError(self.tr("There was an error creating the SQL layer, please check the logs for further information.")) DlgDbError.showError(e, self) return None def loadSqlLayer(self): with OverrideCursor(Qt.WaitCursor): layer = self._getSqlLayer(self.filter) if layer is None: return QgsProject.instance().addMapLayers([layer], True) def fillColumnCombos(self): query = self._getExecutableSqlQuery() if query == "": return with OverrideCursor(Qt.WaitCursor): # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] # get all the columns quotedCols = [] connector = self.db.connector if self.aliasSubQuery: # get a new alias aliasIndex = 0 while True: alias = "_subQuery__%d" % aliasIndex escaped = re.compile('\\b("?)' + re.escape(alias) + '\\1\\b') if not escaped.search(query): break aliasIndex += 1 sql = u"SELECT * FROM (%s\n) AS %s LIMIT 0" % (str(query), connector.quoteId(alias)) else: sql = u"SELECT * FROM (%s\n) WHERE 1=0" % str(query) c = None try: c = connector._execute(None, sql) cols = connector._get_cursor_columns(c) for col in cols: quotedCols.append(connector.quoteId(col)) except BaseError as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return finally: if c: c.close() del c self.setColumnCombos(cols, quotedCols) def setColumnCombos(self, cols, quotedCols): # get sensible default columns. do this before sorting in case there's hints in the column order (e.g., id is more likely to be first) try: defaultGeomCol = next(col for col in cols if col in ['geom', 'geometry', 'the_geom', 'way']) except: defaultGeomCol = None try: defaultUniqueCol = [col for col in cols if 'id' in col][0] except: defaultUniqueCol = None colNames = sorted(zip(cols, quotedCols)) newItems = [] uniqueIsFilled = False for (col, quotedCol) in colNames: item = QStandardItem(col) item.setData(quotedCol) item.setEnabled(True) item.setCheckable(self.allowMultiColumnPk) item.setSelectable(not self.allowMultiColumnPk) if self.allowMultiColumnPk: matchingItems = self.uniqueModel.findItems(col) if matchingItems: item.setCheckState(matchingItems[0].checkState()) uniqueIsFilled = uniqueIsFilled or matchingItems[0].checkState() == Qt.Checked else: item.setCheckState(Qt.Unchecked) newItems.append(item) if self.allowMultiColumnPk: self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) self.uniqueChanged() else: previousUniqueColumn = self.uniqueCombo.currentText() self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) if self.uniqueModel.findItems(previousUniqueColumn): self.uniqueCombo.setEditText(previousUniqueColumn) uniqueIsFilled = True oldGeometryColumn = self.geomCombo.currentText() self.geomCombo.clear() self.geomCombo.addItems(cols) self.geomCombo.setCurrentIndex(self.geomCombo.findText(oldGeometryColumn, Qt.MatchExactly)) # set sensible default columns if the columns are not already set try: if self.geomCombo.currentIndex() == -1: self.geomCombo.setCurrentIndex(cols.index(defaultGeomCol)) except: pass items = self.uniqueModel.findItems(defaultUniqueCol) if items and not uniqueIsFilled: if self.allowMultiColumnPk: items[0].setCheckState(Qt.Checked) else: self.uniqueCombo.setEditText(defaultUniqueCol) def copySelectedResults(self): if len(self.viewResult.selectedIndexes()) <= 0: return model = self.viewResult.model() # convert to string using tab as separator text = model.headerToString("\t") for idx in self.viewResult.selectionModel().selectedRows(): text += "\n" + model.rowToString(idx.row(), "\t") QApplication.clipboard().setText(text, QClipboard.Selection) QApplication.clipboard().setText(text, QClipboard.Clipboard) def initCompleter(self): dictionary = None if self.db: dictionary = self.db.connector.getSqlDictionary() if not dictionary: # use the generic sql dictionary from .sql_dictionary import getSqlDictionary dictionary = getSqlDictionary() wordlist = [] for value in dictionary.values(): wordlist += value # concat lists wordlist = list(set(wordlist)) # remove duplicates api = QsciAPIs(self.editSql.lexer()) for word in wordlist: api.add(word) api.prepare() self.editSql.lexer().setAPIs(api) def displayQueryBuilder(self): dlg = QueryBuilderDlg(self.iface, self.db, self, reset=self.queryBuilderFirst) self.queryBuilderFirst = False r = dlg.exec_() if r == QDialog.Accepted: self.editSql.setText(dlg.query) def createView(self): name, ok = QInputDialog.getText(None, self.tr("View Name"), self.tr("View name")) if ok: try: self.db.connector.createSpatialView(name, self._getExecutableSqlQuery()) except BaseError as e: DlgDbError.showError(e, self) def _getSqlQuery(self): sql = self.editSql.selectedText() if len(sql) == 0: sql = self.editSql.text() return sql def _getExecutableSqlQuery(self): sql = self._getSqlQuery() # Clean it up! lines = [] for line in sql.split('\n'): if not line.strip().startswith('--'): lines.append(line) sql = ' '.join(lines) return sql.strip() def uniqueChanged(self): # when an item is (un)checked, simply trigger an update of the combobox text self.uniqueTextChanged(None) def uniqueTextChanged(self, text): # Whenever there is new text displayed in the combobox, check if it is the correct one and if not, display the correct one. checkedItems = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedItems.append(item.text()) label = ", ".join(checkedItems) if text != label: self.uniqueCombo.setEditText(label) def setFilter(self): from qgis.gui import QgsQueryBuilder layer = self._getSqlLayer("") if not layer: return dlg = QgsQueryBuilder(layer) dlg.setSql(self.filter) if dlg.exec_(): self.filter = dlg.sql() layer.deleteLater()
class DlgSqlWindow(QWidget, Ui_Dialog): nameChanged = pyqtSignal(str) def __init__(self, iface, db, parent=None): QWidget.__init__(self, parent) self.iface = iface self.db = db self.filter = "" self.allowMultiColumnPk = isinstance(db, PGDatabase) # at the moment only PostgreSQL allows a primary key to span multiple columns, SpatiaLite doesn't self.aliasSubQuery = isinstance(db, PGDatabase) # only PostgreSQL requires subqueries to be aliases self.setupUi(self) self.setWindowTitle( self.tr(u"{0} - {1} [{2}]").format(self.windowTitle(), db.connection().connectionName(), db.connection().typeNameString())) self.defaultLayerName = 'QueryLayer' if self.allowMultiColumnPk: self.uniqueColumnCheck.setText(self.tr("Column(s) with unique values")) else: self.uniqueColumnCheck.setText(self.tr("Column with unique values")) self.editSql.setFocus() self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded) self.editSql.setMarginVisible(True) self.initCompleter() # allow copying results copyAction = QAction("copy", self) self.viewResult.addAction(copyAction) copyAction.setShortcuts(QKeySequence.Copy) copyAction.triggered.connect(self.copySelectedResults) self.btnExecute.clicked.connect(self.executeSql) self.btnSetFilter.clicked.connect(self.setFilter) self.btnClear.clicked.connect(self.clearSql) self.presetStore.clicked.connect(self.storePreset) self.presetDelete.clicked.connect(self.deletePreset) self.presetCombo.activated[str].connect(self.loadPreset) self.presetCombo.activated[str].connect(self.presetName.setText) self.updatePresetsCombobox() self.geomCombo.setEditable(True) self.geomCombo.lineEdit().setReadOnly(True) self.uniqueCombo.setEditable(True) self.uniqueCombo.lineEdit().setReadOnly(True) self.uniqueModel = QStandardItemModel(self.uniqueCombo) self.uniqueCombo.setModel(self.uniqueModel) if self.allowMultiColumnPk: self.uniqueCombo.setItemDelegate(QStyledItemDelegate()) self.uniqueModel.itemChanged.connect(self.uniqueChanged) # react to the (un)checking of an item 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 # hide the load query as layer if feature is not supported self._loadAsLayerAvailable = self.db.connector.hasCustomQuerySupport() self.loadAsLayerGroup.setVisible(self._loadAsLayerAvailable) if self._loadAsLayerAvailable: self.layerTypeWidget.hide() # show if load as raster is supported self.loadLayerBtn.clicked.connect(self.loadSqlLayer) self.getColumnsBtn.clicked.connect(self.fillColumnCombos) self.loadAsLayerGroup.toggled.connect(self.loadAsLayerToggled) self.loadAsLayerToggled(False) self._createViewAvailable = self.db.connector.hasCreateSpatialViewSupport() self.btnCreateView.setVisible(self._createViewAvailable) if self._createViewAvailable: self.btnCreateView.clicked.connect(self.createView) self.queryBuilderFirst = True self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif")) self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder) self.presetName.textChanged.connect(self.nameChanged) def updatePresetsCombobox(self): self.presetCombo.clear() names = [] entries = QgsProject.instance().subkeyList('DBManager', 'savedQueries') for entry in entries: name = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + entry + '/name')[0] names.append(name) for name in sorted(names): self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(-1) def storePreset(self): query = self._getSqlQuery() if query == "": return name = self.presetName.text() QgsProject.instance().writeEntry('DBManager', 'savedQueries/q' + str(name.__hash__()) + '/name', name) QgsProject.instance().writeEntry('DBManager', 'savedQueries/q' + str(name.__hash__()) + '/query', query) index = self.presetCombo.findText(name) if index == -1: self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(self.presetCombo.count() - 1) else: self.presetCombo.setCurrentIndex(index) def deletePreset(self): name = self.presetCombo.currentText() QgsProject.instance().removeEntry('DBManager', 'savedQueries/q' + str(name.__hash__())) self.presetCombo.removeItem(self.presetCombo.findText(name)) self.presetCombo.setCurrentIndex(-1) def loadPreset(self, name): query = QgsProject.instance().readEntry('DBManager', 'savedQueries/q' + str(name.__hash__()) + '/query')[0] name = QgsProject.instance().readEntry('DBManager', 'savedQueries/q' + str(name.__hash__()) + '/name')[0] self.editSql.setText(query) def loadAsLayerToggled(self, checked): self.loadAsLayerGroup.setChecked(checked) self.loadAsLayerWidget.setVisible(checked) if checked: self.fillColumnCombos() def clearSql(self): self.editSql.clear() self.editSql.setFocus() self.filter = "" def executeSql(self): sql = self._getSqlQuery() if sql == "": return with OverrideCursor(Qt.WaitCursor): # delete the old model old_model = self.viewResult.model() self.viewResult.setModel(None) if old_model: old_model.deleteLater() cols = [] quotedCols = [] try: # set the new model model = self.db.sqlResultModel(sql, self) self.viewResult.setModel(model) self.lblResult.setText(self.tr("{0} rows, {1:.1f} seconds").format(model.affectedRows(), model.secs())) cols = self.viewResult.model().columnNames() for col in cols: quotedCols.append(self.db.connector.quoteId(col)) except BaseError as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return self.setColumnCombos(cols, quotedCols) self.update() def _getSqlLayer(self, _filter): hasUniqueField = self.uniqueColumnCheck.checkState() == Qt.Checked if hasUniqueField: if self.allowMultiColumnPk: checkedCols = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedCols.append(item.data()) uniqueFieldName = ",".join(checkedCols) elif self.uniqueCombo.currentIndex() >= 0: uniqueFieldName = self.uniqueModel.item(self.uniqueCombo.currentIndex()).data() else: uniqueFieldName = None else: uniqueFieldName = None hasGeomCol = self.hasGeometryCol.checkState() == Qt.Checked if hasGeomCol: geomFieldName = self.geomCombo.currentText() else: geomFieldName = None query = self._getSqlQuery() if query == "": return None # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] from qgis.core import QgsMapLayer layerType = QgsMapLayer.VectorLayer if self.vectorRadio.isChecked() else QgsMapLayer.RasterLayer # get a new layer name names = [] for layer in list(QgsProject.instance().mapLayers().values()): names.append(layer.name()) layerName = self.layerNameEdit.text() if layerName == "": layerName = self.defaultLayerName newLayerName = layerName index = 1 while newLayerName in names: index += 1 newLayerName = u"%s_%d" % (layerName, index) # create the layer layer = self.db.toSqlLayer(query, geomFieldName, uniqueFieldName, newLayerName, layerType, self.avoidSelectById.isChecked(), _filter) if layer.isValid(): return layer else: return None def loadSqlLayer(self): with OverrideCursor(Qt.WaitCursor): layer = self._getSqlLayer(self.filter) if layer is None: return QgsProject.instance().addMapLayers([layer], True) def fillColumnCombos(self): query = self._getSqlQuery() if query == "": return with OverrideCursor(Qt.WaitCursor): # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] # get all the columns cols = [] quotedCols = [] connector = self.db.connector if self.aliasSubQuery: # get a new alias aliasIndex = 0 while True: alias = "_subQuery__%d" % aliasIndex escaped = re.compile('\\b("?)' + re.escape(alias) + '\\1\\b') if not escaped.search(query): break aliasIndex += 1 sql = u"SELECT * FROM (%s\n) AS %s LIMIT 0" % (str(query), connector.quoteId(alias)) else: sql = u"SELECT * FROM (%s\n) WHERE 1=0" % str(query) c = None try: c = connector._execute(None, sql) cols = connector._get_cursor_columns(c) for col in cols: quotedCols.append(connector.quoteId(col)) except BaseError as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return finally: if c: c.close() del c self.setColumnCombos(cols, quotedCols) def setColumnCombos(self, cols, quotedCols): # get sensible default columns. do this before sorting in case there's hints in the column order (e.g., id is more likely to be first) try: defaultGeomCol = next(col for col in cols if col in ['geom', 'geometry', 'the_geom', 'way']) except: defaultGeomCol = None try: defaultUniqueCol = [col for col in cols if 'id' in col][0] except: defaultUniqueCol = None colNames = sorted(zip(cols, quotedCols)) newItems = [] uniqueIsFilled = False for (col, quotedCol) in colNames: item = QStandardItem(col) item.setData(quotedCol) item.setEnabled(True) item.setCheckable(self.allowMultiColumnPk) item.setSelectable(not self.allowMultiColumnPk) if self.allowMultiColumnPk: matchingItems = self.uniqueModel.findItems(col) if matchingItems: item.setCheckState(matchingItems[0].checkState()) uniqueIsFilled = uniqueIsFilled or matchingItems[0].checkState() == Qt.Checked else: item.setCheckState(Qt.Unchecked) newItems.append(item) if self.allowMultiColumnPk: self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) self.uniqueChanged() else: previousUniqueColumn = self.uniqueCombo.currentText() self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) if self.uniqueModel.findItems(previousUniqueColumn): self.uniqueCombo.setEditText(previousUniqueColumn) uniqueIsFilled = True oldGeometryColumn = self.geomCombo.currentText() self.geomCombo.clear() self.geomCombo.addItems(cols) self.geomCombo.setCurrentIndex(self.geomCombo.findText(oldGeometryColumn, Qt.MatchExactly)) # set sensible default columns if the columns are not already set try: if self.geomCombo.currentIndex() == -1: self.geomCombo.setCurrentIndex(cols.index(defaultGeomCol)) except: pass items = self.uniqueModel.findItems(defaultUniqueCol) if items and not uniqueIsFilled: if self.allowMultiColumnPk: items[0].setCheckState(Qt.Checked) else: self.uniqueCombo.setEditText(defaultUniqueCol) try: pass except: pass def copySelectedResults(self): if len(self.viewResult.selectedIndexes()) <= 0: return model = self.viewResult.model() # convert to string using tab as separator text = model.headerToString("\t") for idx in self.viewResult.selectionModel().selectedRows(): text += "\n" + model.rowToString(idx.row(), "\t") QApplication.clipboard().setText(text, QClipboard.Selection) QApplication.clipboard().setText(text, QClipboard.Clipboard) def initCompleter(self): dictionary = None if self.db: dictionary = self.db.connector.getSqlDictionary() if not dictionary: # use the generic sql dictionary from .sql_dictionary import getSqlDictionary dictionary = getSqlDictionary() wordlist = [] for name, value in list(dictionary.items()): wordlist += value # concat lists wordlist = list(set(wordlist)) # remove duplicates api = QsciAPIs(self.editSql.lexer()) for word in wordlist: api.add(word) api.prepare() self.editSql.lexer().setAPIs(api) def displayQueryBuilder(self): dlg = QueryBuilderDlg(self.iface, self.db, self, reset=self.queryBuilderFirst) self.queryBuilderFirst = False r = dlg.exec_() if r == QDialog.Accepted: self.editSql.setText(dlg.query) def createView(self): name, ok = QInputDialog.getText(None, "View name", "View name") if ok: try: self.db.connector.createSpatialView(name, self._getSqlQuery()) except BaseError as e: DlgDbError.showError(e, self) def _getSqlQuery(self): sql = self.editSql.selectedText() if len(sql) == 0: sql = self.editSql.text() return sql def uniqueChanged(self): # when an item is (un)checked, simply trigger an update of the combobox text self.uniqueTextChanged(None) def uniqueTextChanged(self, text): # Whenever there is new text displayed in the combobox, check if it is the correct one and if not, display the correct one. checkedItems = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedItems.append(item.text()) label = ", ".join(checkedItems) if text != label: self.uniqueCombo.setEditText(label) def setFilter(self): from qgis.gui import QgsQueryBuilder layer = self._getSqlLayer("") if not layer: return dlg = QgsQueryBuilder(layer) dlg.setSql(self.filter) if dlg.exec_(): self.filter = dlg.sql() layer.deleteLater()
class AbstractSTREnityListView(QListView): """ A widget for listing and selecting one or more STR entities. .. versionadded:: 1.7 """ def __init__(self, parent=None, **kwargs): super(AbstractSTREnityListView, self).__init__(parent) self._model = QStandardItemModel(self) self._model.setColumnCount(1) self.setModel(self._model) self.setEditTriggers(QAbstractItemView.NoEditTriggers) self._model.itemChanged.connect(self._on_item_changed) self._profile = kwargs.get('profile', None) self._social_tenure = kwargs.get('social_tenure', None) # Load appropriate entities to the view if not self._profile is None: self._load_profile_entities() # Load entities in the STR definition if not self._social_tenure is None: self._select_str_entities() def _on_item_changed(self, item): # Emit signals when an item has been (de)selected. To be # implemented by subclasses. pass @property def profile(self): """ :return: Returns the current profile object in the configuration. :rtype: Profile """ return self._profile @profile.setter def profile(self, profile): """ Sets the current profile object in the configuration. :param profile: Profile object. :type profile: Profile """ self._profile = profile self._load_profile_entities() @property def social_tenure(self): """ :return: Returns the profile's social tenure entity. :rtype: SocialTenure """ return self._social_tenure @social_tenure.setter def social_tenure(self, social_tenure): """ Set the social_tenure entity. :param social_tenure: A profile's social tenure entity. :type social_tenure: SocialTenure """ self._social_tenure = social_tenure self._select_str_entities() def _select_str_entities(self): """ Select the entities defined in the STR. E.g. parties for party entity and spatial units for spatial unit entity. Default implementation does nothing, to be implemented by subclasses. """ pass def _load_profile_entities(self): # Reset view self.clear() # Populate entity items in the view for e in self._profile.user_entities(): self._add_entity(e) def _add_entity(self, entity): # Add entity item to view item = QStandardItem( GuiUtils.get_icon('table.png'), entity.short_name ) item.setCheckable(True) item.setCheckState(Qt.Unchecked) self._model.appendRow(item) def select_entities(self, entities): """ Checks STR entities in the view and emit the entity_selected signal for each item selected. :param entities: Collection of STR entities. :type entities: list """ # Clear selection self.clear_selection() for e in entities: name = e.short_name self.select_entity(name) def selected_entities(self): """ :return: Returns a list of selected entity short names. :rtype: list """ selected_items = [] for i in range(self._model.rowCount()): item = self._model.item(i) if item.checkState() == Qt.Checked: selected_items.append(item.text()) return selected_items def clear(self): """ Remove all party items in the view. """ self._model.clear() self._model.setColumnCount(1) def clear_selection(self): """ Uncheck all items in the view. """ for i in range(self._model.rowCount()): item = self._model.item(i) if item.checkState() == Qt.Checked: item.setCheckState(Qt.Unchecked) def select_entity(self, name): """ Selects a party entity with the given short name. :param name: Entity short name :type name: str """ items = self._model.findItems(name) if len(items) > 0: item = items[0] if item.checkState() == Qt.Unchecked: item.setCheckState(Qt.Checked) def deselect_entity(self, name): """ Deselects an entity with the given short name. :param name: Entity short name :type name: str """ items = self._model.findItems(name) if len(items) > 0: item = items[0] if item.checkState() == Qt.Checked: item.setCheckState(Qt.Unchecked)
class DlgSqlLayerWindow(QWidget, Ui_Dialog): nameChanged = pyqtSignal(str) def __init__(self, iface, layer, parent=None): QWidget.__init__(self, parent) self.iface = iface self.layer = layer uri = QgsDataSourceUri(layer.source()) dbplugin = None db = None if layer.dataProvider().name() == 'postgres': dbplugin = createDbPlugin('postgis', 'postgres') elif layer.dataProvider().name() == 'spatialite': dbplugin = createDbPlugin('spatialite', 'spatialite') elif layer.dataProvider().name() == 'oracle': dbplugin = createDbPlugin('oracle', 'oracle') elif layer.dataProvider().name() == 'virtual': dbplugin = createDbPlugin('vlayers', 'virtual') elif layer.dataProvider().name() == 'ogr': dbplugin = createDbPlugin('gpkg', 'gpkg') if dbplugin: dbplugin.connectToUri(uri) db = dbplugin.db self.dbplugin = dbplugin self.db = db self.filter = "" self.allowMultiColumnPk = isinstance(db, PGDatabase) # at the moment only PostgreSQL allows a primary key to span multiple columns, SpatiaLite doesn't self.aliasSubQuery = isinstance(db, PGDatabase) # only PostgreSQL requires subqueries to be aliases self.setupUi(self) self.setWindowTitle( u"%s - %s [%s]" % (self.windowTitle(), db.connection().connectionName(), db.connection().typeNameString())) self.defaultLayerName = 'QueryLayer' if self.allowMultiColumnPk: self.uniqueColumnCheck.setText(self.tr("Column(s) with unique values")) else: self.uniqueColumnCheck.setText(self.tr("Column with unique values")) self.editSql.setFocus() self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded) self.editSql.setMarginVisible(True) self.initCompleter() # allow copying results copyAction = QAction("copy", self) self.viewResult.addAction(copyAction) copyAction.setShortcuts(QKeySequence.Copy) copyAction.triggered.connect(self.copySelectedResults) self.btnExecute.clicked.connect(self.executeSql) self.btnSetFilter.clicked.connect(self.setFilter) self.btnClear.clicked.connect(self.clearSql) self.presetStore.clicked.connect(self.storePreset) self.presetDelete.clicked.connect(self.deletePreset) self.presetCombo.activated[str].connect(self.loadPreset) self.presetCombo.activated[str].connect(self.presetName.setText) self.editSql.textChanged.connect(self.updatePresetButtonsState) self.presetName.textChanged.connect(self.updatePresetButtonsState) self.presetCombo.currentIndexChanged.connect(self.updatePresetButtonsState) self.updatePresetsCombobox() self.geomCombo.setEditable(True) self.geomCombo.lineEdit().setReadOnly(True) self.uniqueCombo.setEditable(True) self.uniqueCombo.lineEdit().setReadOnly(True) self.uniqueModel = QStandardItemModel(self.uniqueCombo) self.uniqueCombo.setModel(self.uniqueModel) if self.allowMultiColumnPk: self.uniqueCombo.setItemDelegate(QStyledItemDelegate()) self.uniqueModel.itemChanged.connect(self.uniqueChanged) # react to the (un)checking of an item 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 self.layerTypeWidget.hide() # show if load as raster is supported # self.loadLayerBtn.clicked.connect(self.loadSqlLayer) self.updateLayerBtn.clicked.connect(self.updateSqlLayer) self.getColumnsBtn.clicked.connect(self.fillColumnCombos) self.queryBuilderFirst = True self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif")) self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder) self.presetName.textChanged.connect(self.nameChanged) # Update from layer # First the SQL from QgsDataSourceUri table sql = uri.table() if uri.keyColumn() == '_uid_': match = re.search(r'^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$', sql, re.S | re.X) if match: sql = match.group(1) else: match = re.search(r'^\((SELECT .+ FROM .+)\)$', sql, re.S | re.X) if match: sql = match.group(1) # Need to check on table() since the parentheses were removed by the regexp if not uri.table().startswith('(') and not uri.table().endswith(')'): schema = uri.schema() if schema and schema.upper() != 'PUBLIC': sql = 'SELECT * FROM {0}.{1}'.format(self.db.connector.quoteId(schema), self.db.connector.quoteId(sql)) else: sql = 'SELECT * FROM {0}'.format(self.db.connector.quoteId(sql)) self.editSql.setText(sql) self.executeSql() # Then the columns self.geomCombo.setCurrentIndex(self.geomCombo.findText(uri.geometryColumn(), Qt.MatchExactly)) if uri.keyColumn() != '_uid_': self.uniqueColumnCheck.setCheckState(Qt.Checked) if self.allowMultiColumnPk: itemsData = uri.keyColumn().split(',') for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.data() in itemsData: item.setCheckState(Qt.Checked) else: keyColumn = uri.keyColumn() if self.uniqueModel.findItems(keyColumn): self.uniqueCombo.setEditText(keyColumn) # Finally layer name, filter and selectAtId self.layerNameEdit.setText(layer.name()) self.filter = uri.sql() if uri.selectAtIdDisabled(): self.avoidSelectById.setCheckState(Qt.Checked) def getQueryHash(self, name): return 'q%s' % md5(name.encode('utf8')).hexdigest() def updatePresetButtonsState(self, *args): """Slot called when the combo box or the sql or the query name have changed: sets store button state""" self.presetStore.setEnabled(bool(self._getSqlQuery() and self.presetName.text())) self.presetDelete.setEnabled(bool(self.presetCombo.currentIndex() != -1)) def updatePresetsCombobox(self): self.presetCombo.clear() names = [] entries = QgsProject.instance().subkeyList('DBManager', 'savedQueries') for entry in entries: name = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + entry + '/name')[0] names.append(name) for name in sorted(names): self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(-1) def storePreset(self): query = self._getSqlQuery() if query == "": return name = self.presetName.text() QgsProject.instance().writeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/name', name) QgsProject.instance().writeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/query', query) index = self.presetCombo.findText(name) if index == -1: self.presetCombo.addItem(name) self.presetCombo.setCurrentIndex(self.presetCombo.count() - 1) else: self.presetCombo.setCurrentIndex(index) def deletePreset(self): name = self.presetCombo.currentText() QgsProject.instance().removeEntry('DBManager', 'savedQueries/q' + self.getQueryHash(name)) self.presetCombo.removeItem(self.presetCombo.findText(name)) self.presetCombo.setCurrentIndex(-1) def loadPreset(self, name): query = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/query')[0] name = QgsProject.instance().readEntry('DBManager', 'savedQueries/' + self.getQueryHash(name) + '/name')[0] self.editSql.setText(query) def clearSql(self): self.editSql.clear() self.editSql.setFocus() self.filter = "" def executeSql(self): sql = self._getSqlQuery() if sql == "": return with OverrideCursor(Qt.WaitCursor): # delete the old model old_model = self.viewResult.model() self.viewResult.setModel(None) if old_model: old_model.deleteLater() cols = [] quotedCols = [] try: # set the new model model = self.db.sqlResultModel(sql, self) self.viewResult.setModel(model) self.lblResult.setText(self.tr("{0} rows, {1:.3f} seconds").format(model.affectedRows(), model.secs())) cols = self.viewResult.model().columnNames() for col in cols: quotedCols.append(self.db.connector.quoteId(col)) except BaseError as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return self.setColumnCombos(cols, quotedCols) self.update() def _getSqlLayer(self, _filter): hasUniqueField = self.uniqueColumnCheck.checkState() == Qt.Checked if hasUniqueField: if self.allowMultiColumnPk: checkedCols = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedCols.append(item.data()) uniqueFieldName = ",".join(checkedCols) elif self.uniqueCombo.currentIndex() >= 0: uniqueFieldName = self.uniqueModel.item(self.uniqueCombo.currentIndex()).data() else: uniqueFieldName = None else: uniqueFieldName = None hasGeomCol = self.hasGeometryCol.checkState() == Qt.Checked if hasGeomCol: geomFieldName = self.geomCombo.currentText() else: geomFieldName = None query = self._getSqlQuery() if query == "": return None # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] from qgis.core import QgsMapLayer layerType = QgsMapLayer.VectorLayer if self.vectorRadio.isChecked() else QgsMapLayer.RasterLayer # get a new layer name names = [] for layer in list(QgsProject.instance().mapLayers().values()): names.append(layer.name()) layerName = self.layerNameEdit.text() if layerName == "": layerName = self.defaultLayerName newLayerName = layerName index = 1 while newLayerName in names: index += 1 newLayerName = u"%s_%d" % (layerName, index) # create the layer layer = self.db.toSqlLayer(query, geomFieldName, uniqueFieldName, newLayerName, layerType, self.avoidSelectById.isChecked(), _filter) if layer.isValid(): return layer else: return None def loadSqlLayer(self): with OverrideCursor(Qt.WaitCursor): layer = self._getSqlLayer(self.filter) if layer is None: return QgsProject.instance().addMapLayers([layer], True) def updateSqlLayer(self): with OverrideCursor(Qt.WaitCursor): layer = self._getSqlLayer(self.filter) if layer is None: return # self.layer.dataProvider().setDataSourceUri(layer.dataProvider().dataSourceUri()) # self.layer.dataProvider().reloadData() XMLDocument = QDomDocument("style") XMLMapLayers = XMLDocument.createElement("maplayers") XMLMapLayer = XMLDocument.createElement("maplayer") self.layer.writeLayerXml(XMLMapLayer, XMLDocument, QgsReadWriteContext()) XMLMapLayer.firstChildElement("datasource").firstChild().setNodeValue(layer.source()) XMLMapLayers.appendChild(XMLMapLayer) XMLDocument.appendChild(XMLMapLayers) self.layer.readLayerXml(XMLMapLayer, QgsReadWriteContext()) self.layer.reload() self.iface.actionDraw().trigger() self.iface.mapCanvas().refresh() def fillColumnCombos(self): query = self._getSqlQuery() if query == "": return with OverrideCursor(Qt.WaitCursor): # remove a trailing ';' from query if present if query.strip().endswith(';'): query = query.strip()[:-1] # get all the columns cols = [] quotedCols = [] connector = self.db.connector if self.aliasSubQuery: # get a new alias aliasIndex = 0 while True: alias = "_subQuery__%d" % aliasIndex escaped = re.compile('\\b("?)' + re.escape(alias) + '\\1\\b') if not escaped.search(query): break aliasIndex += 1 sql = u"SELECT * FROM (%s\n) AS %s LIMIT 0" % (str(query), connector.quoteId(alias)) else: sql = u"SELECT * FROM (%s\n) WHERE 1=0" % str(query) c = None try: c = connector._execute(None, sql) cols = connector._get_cursor_columns(c) for col in cols: quotedCols.append(connector.quoteId(col)) except BaseError as e: DlgDbError.showError(e, self) self.uniqueModel.clear() self.geomCombo.clear() return finally: if c: c.close() del c self.setColumnCombos(cols, quotedCols) def setColumnCombos(self, cols, quotedCols): # get sensible default columns. do this before sorting in case there's hints in the column order (e.g., id is more likely to be first) try: defaultGeomCol = next(col for col in cols if col in ['geom', 'geometry', 'the_geom', 'way']) except: defaultGeomCol = None try: defaultUniqueCol = [col for col in cols if 'id' in col][0] except: defaultUniqueCol = None colNames = sorted(zip(cols, quotedCols)) newItems = [] uniqueIsFilled = False for (col, quotedCol) in colNames: item = QStandardItem(col) item.setData(quotedCol) item.setEnabled(True) item.setCheckable(self.allowMultiColumnPk) item.setSelectable(not self.allowMultiColumnPk) if self.allowMultiColumnPk: matchingItems = self.uniqueModel.findItems(col) if matchingItems: item.setCheckState(matchingItems[0].checkState()) uniqueIsFilled = uniqueIsFilled or matchingItems[0].checkState() == Qt.Checked else: item.setCheckState(Qt.Unchecked) newItems.append(item) if self.allowMultiColumnPk: self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) self.uniqueChanged() else: previousUniqueColumn = self.uniqueCombo.currentText() self.uniqueModel.clear() self.uniqueModel.appendColumn(newItems) if self.uniqueModel.findItems(previousUniqueColumn): self.uniqueCombo.setEditText(previousUniqueColumn) uniqueIsFilled = True oldGeometryColumn = self.geomCombo.currentText() self.geomCombo.clear() self.geomCombo.addItems(cols) self.geomCombo.setCurrentIndex(self.geomCombo.findText(oldGeometryColumn, Qt.MatchExactly)) # set sensible default columns if the columns are not already set try: if self.geomCombo.currentIndex() == -1: self.geomCombo.setCurrentIndex(cols.index(defaultGeomCol)) except: pass items = self.uniqueModel.findItems(defaultUniqueCol) if items and not uniqueIsFilled: if self.allowMultiColumnPk: items[0].setCheckState(Qt.Checked) else: self.uniqueCombo.setEditText(defaultUniqueCol) try: pass except: pass def copySelectedResults(self): if len(self.viewResult.selectedIndexes()) <= 0: return model = self.viewResult.model() # convert to string using tab as separator text = model.headerToString("\t") for idx in self.viewResult.selectionModel().selectedRows(): text += "\n" + model.rowToString(idx.row(), "\t") QApplication.clipboard().setText(text, QClipboard.Selection) QApplication.clipboard().setText(text, QClipboard.Clipboard) def initCompleter(self): dictionary = None if self.db: dictionary = self.db.connector.getSqlDictionary() if not dictionary: # use the generic sql dictionary from .sql_dictionary import getSqlDictionary dictionary = getSqlDictionary() wordlist = [] for name, value in dictionary.items(): wordlist += value # concat lists wordlist = list(set(wordlist)) # remove duplicates api = QsciAPIs(self.editSql.lexer()) for word in wordlist: api.add(word) api.prepare() self.editSql.lexer().setAPIs(api) def displayQueryBuilder(self): dlg = QueryBuilderDlg(self.iface, self.db, self, reset=self.queryBuilderFirst) self.queryBuilderFirst = False r = dlg.exec_() if r == QDialog.Accepted: self.editSql.setText(dlg.query) def _getSqlQuery(self): sql = self.editSql.selectedText() if len(sql) == 0: sql = self.editSql.text() return sql def uniqueChanged(self): # when an item is (un)checked, simply trigger an update of the combobox text self.uniqueTextChanged(None) def uniqueTextChanged(self, text): # Whenever there is new text displayed in the combobox, check if it is the correct one and if not, display the correct one. checkedItems = [] for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.checkState() == Qt.Checked: checkedItems.append(item.text()) label = ", ".join(checkedItems) if text != label: self.uniqueCombo.setEditText(label) def setFilter(self): from qgis.gui import QgsQueryBuilder layer = self._getSqlLayer("") if not layer: return dlg = QgsQueryBuilder(layer) dlg.setSql(self.filter) if dlg.exec_(): self.filter = dlg.sql() layer.deleteLater()
class PosiviewProperties(QgsOptionsDialogBase, Ui_PosiviewPropertiesBase): ''' GUI class classdocs for the Configuration dialog ''' applyChanges = pyqtSignal(dict) def __init__(self, project, parent=None): ''' Setup dialog widgets with the project properties ''' super(PosiviewProperties, self).__init__("PosiViewProperties", parent) self.setupUi(self) self.groupBox_6.hide() self.initOptionsBase(False) self.restoreOptionsBaseUi() self.comboBoxParser.addItems(PARSERS) self.comboBoxProviderType.addItems(DEVICE_TYPES) self.project = project self.projectProperties = project.properties() self.mToolButtonLoad.setDefaultAction(self.actionLoadConfiguration) self.mToolButtonSave.setDefaultAction(self.actionSaveConfiguration) self.mobileModel = QStringListModel() self.mobileListModel = QStringListModel() self.mMobileListView.setModel(self.mobileListModel) self.mobileProviderModel = QStandardItemModel() self.mobileProviderModel.setHorizontalHeaderLabels( ('Provider', 'Filter')) self.mMobileProviderTableView.setModel(self.mobileProviderModel) self.providerListModel = QStringListModel() self.mDataProviderListView.setModel(self.providerListModel) self.comboBoxProviders.setModel(self.providerListModel) self.setupModelData(self.projectProperties) self.setupGeneralData(self.projectProperties) def setupModelData(self, properties): self.mobileListModel.setStringList(sorted( properties['Mobiles'].keys())) self.providerListModel.setStringList( sorted(properties['Provider'].keys())) def setupGeneralData(self, properties): self.lineEditCruise.setText(properties['Mission']['cruise']) self.lineEditDive.setText(properties['Mission']['dive']) self.lineEditStation.setText(properties['Mission']['station']) self.lineEditRecorderPath.setText(properties['RecorderPath']) self.checkBoxAutoRecording.setChecked(properties['AutoRecord']) self.spinBoxNotifyDuration.setValue(properties['NotifyDuration']) self.checkBoxUtcClock.setChecked(properties['ShowUtcClock']) self.checkBoxWithSuffix.setChecked(properties['DefaultFormat'] & 4) self.comboBoxDefaultPositionFormat.setCurrentIndex( (properties['DefaultFormat']) & 3) def updateGeneralData(self): self.projectProperties['Mission']['cruise'] = self.lineEditCruise.text( ) self.projectProperties['Mission']['dive'] = self.lineEditDive.text() self.projectProperties['Mission'][ 'station'] = self.lineEditStation.text() self.projectProperties[ 'RecorderPath'] = self.lineEditRecorderPath.text() self.projectProperties[ 'AutoRecord'] = self.checkBoxAutoRecording.isChecked() self.projectProperties[ 'NotifyDuration'] = self.spinBoxNotifyDuration.value() self.projectProperties[ 'ShowUtcClock'] = self.checkBoxUtcClock.isChecked() self.projectProperties[ 'DefaultFormat'] = self.comboBoxDefaultPositionFormat.currentIndex( ) if self.checkBoxWithSuffix.isChecked(): self.projectProperties['DefaultFormat'] |= 4 def getColor(self, value): try: return QColor.fromRgba(int(value)) except ValueError: return QColor(value) @pyqtSlot(QAbstractButton, name='on_buttonBox_clicked') def onButtonBoxClicked(self, button): role = self.buttonBox.buttonRole(button) if role == QDialogButtonBox.ApplyRole or role == QDialogButtonBox.AcceptRole: self.updateGeneralData() self.applyChanges.emit(self.projectProperties) @pyqtSlot(name='on_actionSaveConfiguration_triggered') def onActionSaveConfigurationTriggered(self): ''' Save the current configuration ''' fn, __ = QFileDialog.getSaveFileName(None, 'Save PosiView configuration', '', 'Configuration (*.ini *.conf)') if fn: if not os.path.splitext(fn)[1]: fn += u'.conf' self.project.store(fn) @pyqtSlot(name='on_actionLoadConfiguration_triggered') def onActionLoadConfigurationTriggered(self): ''' Load configuration from file ''' fn, __ = QFileDialog.getOpenFileName(None, 'Save PosiView configuration', '', 'Configuration (*.ini *.conf)') self.projectProperties = self.project.read(fn) self.setupModelData(self.projectProperties) self.setupGeneralData(self.projectProperties) @pyqtSlot(QModelIndex, name='on_mMobileListView_clicked') def editMobile(self, index): ''' Populate the widgets with the selected mobiles properties ''' if index.isValid(): self.populateMobileWidgets(index) @pyqtSlot(str, name='on_comboBoxMobileType_currentIndexChanged') def mobileTypeChanged(self, mType): if mType == 'SHAPE': self.lineEditMobileShape.setEnabled(True) else: self.lineEditMobileShape.setEnabled(False) @pyqtSlot(QModelIndex, name='on_mMobileListView_activated') def activated(self, index): pass @pyqtSlot(name='on_toolButtonAddMobile_clicked') def addMobile(self): self.mobileListModel.insertRow(self.mobileListModel.rowCount()) index = self.mobileListModel.index(self.mobileListModel.rowCount() - 1) self.lineEditMobileName.setText('NewMobile') self.mobileListModel.setData(index, 'NewMobile', Qt.DisplayRole) self.mMobileListView.setCurrentIndex(index) self.applyMobile() @pyqtSlot(name='on_pushButtonApplyMobile_clicked') def applyMobile(self): index = self.mMobileListView.currentIndex() if index.isValid() and not self.lineEditMobileName.text() == '': mobile = dict() mobile['Name'] = self.lineEditMobileName.text() mobile['type'] = self.comboBoxMobileType.currentText() try: t = eval(self.lineEditMobileShape.text()) if t.__class__ is tuple or t.__class__ is dict: mobile['shape'] = t except SyntaxError: mobile['shape'] = ((0.0, -0.5), (0.3, 0.5), (0.0, 0.2), (-0.5, 0.5)) mobile['length'] = self.doubleSpinBoxMobileLength.value() mobile['width'] = self.doubleSpinBoxMobileWidth.value() mobile['defaultIcon'] = self.checkBoxDefaultIcon.isChecked() mobile['defaultIconFilled'] = self.checkBoxDefIconFilled.isChecked( ) mobile['offsetX'] = self.doubleSpinBoxXOffset.value() mobile['offsetY'] = self.doubleSpinBoxYOffset.value() mobile['zValue'] = self.spinBoxZValue.value() mobile['color'] = self.mColorButtonMobileColor.color().rgba() mobile['fillColor'] = self.mColorButtonMobileFillColor.color( ).rgba() mobile['timeout'] = self.spinBoxMobileTimeout.value() * 1000 mobile['nofixNotify'] = self.spinBoxMobileNotification.value() mobile['fadeOut'] = self.checkBoxFadeOut.isChecked() mobile['trackLength'] = self.spinBoxTrackLength.value() mobile['trackColor'] = self.mColorButtonMobileTrackColor.color( ).rgba() mobile['showLabel'] = self.checkBoxShowLabel.isChecked() provs = dict() for r in range(self.mobileProviderModel.rowCount()): try: fil = int( self.mobileProviderModel.item(r, 1).data(Qt.DisplayRole)) except Exception: fil = self.mobileProviderModel.item(r, 1).data(Qt.DisplayRole) if not fil: fil = None provs[self.mobileProviderModel.item(r, 0).data( Qt.DisplayRole)] = fil mobile['provider'] = provs currName = self.mobileListModel.data(index, Qt.DisplayRole) if not currName == mobile['Name']: del self.projectProperties['Mobiles'][currName] self.mobileListModel.setData(index, mobile['Name'], Qt.DisplayRole) self.projectProperties['Mobiles'][mobile['Name']] = mobile def populateMobileWidgets(self, index): mobile = self.projectProperties['Mobiles'][self.mobileListModel.data( index, Qt.DisplayRole)] self.lineEditMobileName.setText(mobile.get('Name')) self.comboBoxMobileType.setCurrentIndex( self.comboBoxMobileType.findText( mobile.setdefault('type', 'BOX').upper())) if mobile['type'] == 'SHAPE': self.lineEditMobileShape.setText(str(mobile['shape'])) self.lineEditMobileShape.setEnabled(True) self.doubleSpinBoxXOffset.setEnabled(True) self.doubleSpinBoxYOffset.setEnabled(True) else: self.lineEditMobileShape.setEnabled(False) self.doubleSpinBoxXOffset.setEnabled(False) self.doubleSpinBoxYOffset.setEnabled(False) self.lineEditMobileShape.clear() self.doubleSpinBoxMobileLength.setValue(mobile.get('length', 20.0)) self.doubleSpinBoxMobileWidth.setValue(mobile.get('width', 5.0)) self.checkBoxDefaultIcon.setChecked(mobile.get('defaultIcon', True)) self.checkBoxDefIconFilled.setChecked( mobile.get('defaultIconFilled', False)) self.doubleSpinBoxXOffset.setValue(mobile.get('offsetX', 0.0)) self.doubleSpinBoxYOffset.setValue(mobile.get('offsetY', 0.0)) self.spinBoxZValue.setValue(mobile.get('zValue', 100)) self.mColorButtonMobileColor.setColor( self.getColor(mobile.get('color', 'black'))) self.mColorButtonMobileFillColor.setColor( self.getColor(mobile.get('fillColor', 'green'))) self.spinBoxMobileTimeout.setValue(mobile.get('timeout', 3000) / 1000) self.spinBoxMobileNotification.setValue(mobile.get('nofixNotify', 0)) self.checkBoxFadeOut.setChecked(mobile.get('fadeOut', False)) self.spinBoxTrackLength.setValue(mobile.get('trackLength', 100)) self.mColorButtonMobileTrackColor.setColor( self.getColor(mobile.get('trackColor', 'green'))) self.checkBoxShowLabel.setChecked(mobile.get('showLabel', False)) r = 0 self.mobileProviderModel.removeRows( 0, self.mobileProviderModel.rowCount()) if 'provider' in mobile: for k, v in list(mobile['provider'].items()): prov = QStandardItem(k) val = QStandardItem(str(v)) self.mobileProviderModel.setItem(r, 0, prov) self.mobileProviderModel.setItem(r, 1, val) r += 1 @pyqtSlot(name='on_toolButtonRemoveMobile_clicked') def removeMobile(self): idx = self.mMobileListView.currentIndex() if idx.isValid(): self.projectProperties['Mobiles'].pop( self.mobileListModel.data(idx, Qt.DisplayRole)) self.mobileListModel.removeRows(idx.row(), 1) idx = self.mMobileListView.currentIndex() if idx.isValid(): self.populateMobileWidgets(idx) @pyqtSlot(name='on_toolButtonRefreshMobileProvider_clicked') def refreshMobileProvider(self): prov = self.comboBoxProviders.currentText() if prov == '': return fil = None if self.lineEditProviderFilter.text() != '': fil = self.lineEditProviderFilter.text() items = self.mobileProviderModel.findItems(prov, Qt.MatchExactly, 0) if items: for item in items: self.mobileProviderModel.setItem(item.row(), 1, QStandardItem(fil)) else: self.mobileProviderModel.appendRow( [QStandardItem(prov), QStandardItem(fil)]) @pyqtSlot(name='on_toolButtonRemoveMobileProvider_clicked') def removeMobileProvider(self): idx = self.mMobileProviderTableView.currentIndex() if idx.isValid(): self.mobileProviderModel.removeRow(idx.row()) @pyqtSlot(name='on_pushButtonApplyDataProvider_clicked') def applyDataProvider(self): index = self.mDataProviderListView.currentIndex() if index.isValid() and not self.lineEditProviderName.text() == '': provider = dict() provider['Name'] = self.lineEditProviderName.text() provider['DataDeviceType'] = self.comboBoxProviderType.currentText( ) if provider['DataDeviceType'] in NETWORK_TYPES: provider['Host'] = self.lineEditProviderHostName.text() provider['Port'] = self.spinBoxProviderPort.value() provider['Parser'] = self.comboBoxParser.currentText() currName = self.providerListModel.data(index, Qt.DisplayRole) if not currName == provider['Name']: del self.projectProperties['Provider'][currName] self.providerListModel.setData(index, provider['Name'], Qt.DisplayRole) self.projectProperties['Provider'][provider['Name']] = provider @pyqtSlot(QModelIndex, name='on_mDataProviderListView_clicked') def editDataProvider(self, index): ''' ''' if index.isValid(): self.populateDataProviderWidgets(index) def populateDataProviderWidgets(self, index): provider = self.projectProperties['Provider'][ self.providerListModel.data(index, Qt.DisplayRole)] self.lineEditProviderName.setText(provider.get('Name')) self.comboBoxProviderType.setCurrentIndex( self.comboBoxProviderType.findText( provider.setdefault('DataDeviceType', 'UDP').upper())) if provider['DataDeviceType'] in NETWORK_TYPES: self.stackedWidgetDataDevice.setCurrentIndex(0) self.lineEditProviderHostName.setText( provider.setdefault('Host', '0.0.0.0')) self.spinBoxProviderPort.setValue( int(provider.setdefault('Port', 2000))) self.comboBoxParser.setCurrentIndex( self.comboBoxParser.findText( provider.setdefault('Parser', 'NONE').upper())) @pyqtSlot(name='on_toolButtonAddDataProvider_clicked') def addDataProvider(self): self.providerListModel.insertRow(self.providerListModel.rowCount()) index = self.providerListModel.index( self.providerListModel.rowCount() - 1) self.lineEditProviderName.setText('NewDataProvider') self.providerListModel.setData(index, 'NewDataProvider', Qt.DisplayRole) self.mDataProviderListView.setCurrentIndex(index) self.applyDataProvider() @pyqtSlot(name='on_toolButtonRemoveDataProvider_clicked') def removeDataProvider(self): idx = self.mDataProviderListView.currentIndex() if idx.isValid(): self.projectProperties['Provider'].pop( self.providerListModel.data(idx, Qt.DisplayRole)) self.providerListModel.removeRows(idx.row(), 1) idx = self.mDataProviderListView.currentIndex() if idx.isValid(): self.populateDataProviderWidgets(idx) @pyqtSlot(name='on_toolButtonSelectLogPath_clicked') def selectRecorderPath(self): path = QFileDialog.getExistingDirectory( self, self.tr('Select Recorder Path'), self.lineEditRecorderPath.text(), QFileDialog.ShowDirsOnly | QFileDialog.DontResolveSymlinks) if path != '': self.lineEditRecorderPath.setText(path) @pyqtSlot(QPoint, name='on_lineEditMobileShape_customContextMenuRequested') def mobileShapeContextMenu(self, pos): menu = QMenu(self.lineEditMobileShape) vesselAction = menu.addAction(self.tr('Vessel')) rovAction = menu.addAction(self.tr('ROV')) auvAction = menu.addAction(self.tr('AUV')) arrowAction = menu.addAction(self.tr('Arrow')) selectedAction = menu.exec_(self.lineEditMobileShape.mapToGlobal(pos)) if selectedAction == vesselAction: self.lineEditMobileShape.setText( u'((0, -0.5), (0.5, -0.3), (0.5, 0.5), (-0.5, 0.5), (-0.5, -0.3))' ) elif selectedAction == rovAction: self.lineEditMobileShape.setText( u'((0.3, -0.5), (0.5, -0.3), (0.5, 0.5), (-0.5, 0.5), (-0.5, -0.3), (-0.3, -0.5))' ) elif selectedAction == auvAction: self.lineEditMobileShape.setText( u'((0, -0.5), (0.4, -0.3), (0.5, -0.3), (0.5, -0.2), (0.4, -0.2), (0.4, 0.3), (0.5, 0.3), (0.5, 0.4), (0.4, 0.4), (0.0, 0.5), \ (-0.4, 0.4), (-0.5, 0.4), (-0.5, 0.3), (-0.4, 0.3), (-0.4, -0.2), (-0.5, -0.2), (-0.5, -0.3), (-0.4, -0.3))' ) elif selectedAction == arrowAction: self.lineEditMobileShape.setText( u'((0, -0.5), (0.5, 0.5), (0, 0), (-0.5, 0.5))') @pyqtSlot(name='on_buttonBox_helpRequested') def showHelp(self): """Display application help to the user.""" help_file = os.path.join( os.path.split(os.path.dirname(__file__))[0], 'help', 'index.html') QDesktopServices.openUrl(QUrl.fromLocalFile(help_file))
class QRiSDockWidget(QtWidgets.QDockWidget, FORM_CLASS): closingPlugin = pyqtSignal() def __init__(self, parent=None): """Constructor.""" super(QRiSDockWidget, self).__init__(parent) # Set up the user interface from Designer. # After setupUI you can access any designer object by doing # self.<objectname>, and you can use autoconnect slots - see # http://doc.qt.io/qt-5/designer-using-a-ui-file.html # #widgets-and-dialogs-with-auto-connect self.setupUi(self) self.settings = Settings() self.qris_project = None self.menu = ContextMenu() self.treeView.setContextMenuPolicy(Qt.CustomContextMenu) self.treeView.setEditTriggers(QAbstractItemView.NoEditTriggers) self.treeView.customContextMenuRequested.connect(self.open_menu) # self.treeView.doubleClicked.connect(self.default_tree_action) # self.treeView.clicked.connect(self.item_change) # self.treeView.expanded.connect(self.expand_tree_item) self.model = QStandardItemModel() self.treeView.setModel(self.model) # Take this out of init so that nodes can be added as new data is added and imported; def build_tree_view(self, qris_project, new_item=None): """Builds items in the tree view based on dictionary values that are part of the project""" self.qris_project = qris_project self.model.clear() self.tree_state = {} rootNode = self.model.invisibleRootItem() # set the project root project_node = QStandardItem(self.qris_project.project_name) project_node.setIcon(QIcon(':/plugins/qris_toolbar/icon.png')) project_node.setData('project_root', item_code['item_type']) rootNode.appendRow(project_node) self.treeView.setExpanded(project_node.index(), True) # Add project extent layers to tree extent_folder = QStandardItem("Project Extents") extent_folder.setIcon(QIcon(':/plugins/qris_toolbar/test_folder.png')) extent_folder.setData('extent_folder', item_code['item_type']) project_node.appendRow(extent_folder) for extent in self.qris_project.project_extents.values(): extent_node = QStandardItem(extent.display_name) extent_node.setIcon( QIcon(':/plugins/qris_toolbar/test_project_extent.png')) extent_node.setData('extent_node', item_code['item_type']) extent_node.setData(extent, item_code['INSTANCE']) extent_folder.appendRow(extent_node) # Add project layers node layers_folder = QStandardItem("Project Layers") layers_folder.setIcon(QIcon(':/plugins/qris_toolbar/test_folder.png')) layers_folder.setData('layers_folder', item_code['item_type']) project_node.appendRow(layers_folder) # TODO extend this for geometry types and raster layers for layer in self.qris_project.project_vector_layers.values(): layer_node = QStandardItem(layer.display_name) # TODO change icon by type layer_node.setIcon(QIcon(':/plugins/qris_toolbar/test_layers.png')) layer_node.setData('layer_node', item_code['item_type']) layer_node.setData(layer, item_code['INSTANCE']) layers_folder.appendRow(layer_node) # # Add riverscape surfaces node # # TODO go through and add layers to the tree # riverscape_surfaces_node = QStandardItem("Riverscape Surfaces") # riverscape_surfaces_node.setIcon(QIcon(':/plugins/qris_toolbar/BrowseFolder.png')) # riverscape_surfaces_node.setData('riverscape_surfaces_folder', item_code['item_type']) # riverscape_surfaces_node.setData('group', item_code['item_layer']) # project_node.appendRow(riverscape_surfaces_node) # # Add riverscape segments node # # TODO go through and add layers to the tree # riverscape_segments_node = QStandardItem("Riverscape Segments") # riverscape_segments_node.setIcon(QIcon(':/plugins/qris_toolbar/BrowseFolder.png')) # riverscape_segments_node.setData('riverscape_segments_folder', item_code['item_type']) # riverscape_segments_node.setData('group', item_code['item_layer']) # project_node.appendRow(riverscape_segments_node) # # Add detrended rasters to tree # detrended_rasters = QStandardItem("Detrended Rasters") # detrended_rasters.setIcon(QIcon(':/plugins/qris_toolbar/BrowseFolder.png')) # detrended_rasters.setData("DetrendedRastersFolder", item_code['item_type']) # detrended_rasters.setData('group', item_code['item_layer']) # project_node.appendRow(detrended_rasters) # for raster in self.qris_project.detrended_rasters.values(): # detrended_raster = QStandardItem(raster.name) # detrended_raster.setIcon(QIcon(':/plugins/qris_toolbar/qris_raster.png')) # detrended_raster.setData('DetrendedRaster', item_code['item_type']) # detrended_raster.setData(raster, item_code['INSTANCE']) # detrended_raster.setData('raster_layer', item_code['item_layer']) # detrended_rasters.appendRow(detrended_raster) # if len(raster.surfaces.values()) > 0: # item_surfaces = QStandardItem("Surfaces") # item_surfaces.setIcon(QIcon(':/plugins/qris_toolbar/BrowseFolder.png')) # item_surfaces.setData('group', item_code['item_layer']) # detrended_raster.appendRow(item_surfaces) # for surface in raster.surfaces.values(): # item_surface = QStandardItem(surface.name) # item_surface.setIcon(QIcon(':/plugins/qris_toolbar/layers/Polygon.png')) # item_surface.setData('DetrendedRasterSurface', item_code['item_type']) # item_surface.setData('surface_layer', item_code['item_layer']) # item_surface.setData(surface, item_code['INSTANCE']) # item_surfaces.appendRow(item_surface) # # Add assessments to tree # assessments_parent_node = QStandardItem("Riverscape Assessments") # assessments_parent_node.setIcon(QIcon(':/plugins/qris_toolbar/BrowseFolder.png')) # assessments_parent_node.setData('assessments_folder', item_code['item_type']) # assessments_parent_node.setData('group', item_code['item_layer']) # project_node.appendRow(assessments_parent_node) # if self.qris_project.project_assessments: # self.qris_project.assessments_path = os.path.join(self.qris_project.project_path, "Assessments.gpkg") # assessments_layer = QgsVectorLayer(self.qris_project.assessments_path + "|layername=assessments", "assessments", "ogr") # for assessment_feature in assessments_layer.getFeatures(): # assessment_node = QStandardItem(assessment_feature.attribute('assessment_date').toString('yyyy-MM-dd')) # assessment_node.setIcon(QIcon(':/plugins/qris_toolbar/BrowseFolder.png')) # assessment_node.setData('dam_assessment', item_code['item_type']) # assessment_node.setData('group', item_code['item_layer']) # assessment_node.setData(assessment_feature.attribute('fid'), item_code['feature_id']) # assessments_parent_node.appendRow(assessment_node) # assessments_parent_node.sortChildren(Qt.AscendingOrder) # Add designs to tree design_folder = QStandardItem("Low-Tech Designs") design_folder.setIcon(QIcon(':/plugins/qris_toolbar/test_folder.png')) design_folder.setData('design_folder', item_code['item_type']) project_node.appendRow(design_folder) self.treeView.setExpanded(design_folder.index(), True) design_geopackage_path = self.qris_project.project_designs.geopackage_path( self.qris_project.project_path) designs_path = design_geopackage_path + '|layername=designs' if os.path.exists(design_geopackage_path): designs_layer = QgsVectorLayer(designs_path, "designs", "ogr") for design_feature in designs_layer.getFeatures(): # If these data types stick this should be refactored into a create node function design_node = QStandardItem(design_feature.attribute('name')) design_node.setIcon( QIcon(':/plugins/qris_toolbar/test_design.png')) design_node.setData('design', item_code['item_type']) design_node.setData(design_feature.attribute('fid'), item_code['feature_id']) design_folder.appendRow(design_node) # TODO add the structure, footprint, and zoi to the tree under each design # TODO This just doesn't work very well design_folder.sortChildren(Qt.AscendingOrder) # populate structure types structure_type_folder = QStandardItem("Structure Types") structure_type_folder.setIcon( QIcon(':/plugins/qris_toolbar/test_settings.png')) structure_type_folder.setData('structure_type_folder', item_code['item_type']) design_folder.appendRow(structure_type_folder) structure_type_path = design_geopackage_path + '|layername=structure_types' structure_type_layer = QgsVectorLayer(structure_type_path, "structure_types", "ogr") for structure_type in structure_type_layer.getFeatures(): structure_type_node = QStandardItem( structure_type.attribute('name')) # TODO change the icon structure_type_node.setIcon( QIcon(':/plugins/qris_toolbar/test_structure.png')) structure_type_node.setData('structure_type', item_code['item_type']) structure_type_node.setData(structure_type.attribute('fid'), item_code['feature_id']) structure_type_folder.appendRow(structure_type_node) # populate design phases types phase_folder = QStandardItem("Implementation Phases") # TODO change icon phase_folder.setIcon(QIcon(':/plugins/qris_toolbar/test_settings.png')) phase_folder.setData('phase_folder', item_code['item_type']) design_folder.appendRow(phase_folder) phase_path = design_geopackage_path + '|layername=phases' phase_layer = QgsVectorLayer(phase_path, "phases", "ogr") for phase in phase_layer.getFeatures(): phase_node = QStandardItem(phase.attribute('name')) # TODO change the icon phase_node.setIcon(QIcon(':/plugins/qris_toolbar/test_phase.png')) phase_node.setData('phase', item_code['item_type']) phase_node.setData(phase.attribute('fid'), item_code['feature_id']) phase_folder.appendRow(phase_node) # populate zoi types zoi_type_folder = QStandardItem("ZOI Types") zoi_type_folder.setIcon( QIcon(':/plugins/qris_toolbar/test_settings.png')) zoi_type_folder.setData('zoi_type_folder', item_code['item_type']) design_folder.appendRow(zoi_type_folder) zoi_type_path = design_geopackage_path + '|layername=zoi_types' zoi_type_layer = QgsVectorLayer(zoi_type_path, "zoi_types", "ogr") for zoi_type in zoi_type_layer.getFeatures(): zoi_type_node = QStandardItem(zoi_type.attribute('name')) # TODO change the icon zoi_type_node.setIcon( QIcon(':/plugins/qris_toolbar/test_influence.png')) zoi_type_node.setData('zoi_type', item_code['item_type']) zoi_type_node.setData(zoi_type.attribute('fid'), item_code['feature_id']) zoi_type_folder.appendRow(zoi_type_node) # Add a placed for photos # photos_folder = QStandardItem("Project Photos") # photos_folder.setIcon(QIcon(':/plugins/qris_toolbar/BrowseFolder.png')) # photos_folder.setData('photos_folder', item_code['item_type']) # project_node.appendRow(photos_folder) # TODO for now we are expanding the map however need to remember expanded state or add new nodes as we add data # self.treeView.expandAll() # Check if new item is in the tree, if it is pass it to the add_to_map function # Adds a test comment if new_item is not None and new_item != '': selected_item = self._find_item_in_model(new_item) if selected_item is not None: add_to_map(self.qris_project, self.model, selected_item) def _find_item_in_model(self, name): """Looks in the tree for an item name passed from the dataChange method.""" # TODO may want to pass this is a try except block and give an informative error message selected_item = self.model.findItems(name, Qt.MatchRecursive)[0] return selected_item def get_item_expanded_state(self): """Recursively records a list of the expanded state for items in the tree""" def closeEvent(self, event): self.qris_project = None self.closingPlugin.emit() event.accept() def open_menu(self, position): """Connects signals as context menus to items in the tree""" self.menu.clear() indexes = self.treeView.selectedIndexes() if len(indexes) < 1: return # No multiselect so there is only ever one item idx = indexes[0] if not idx.isValid(): return model_item = self.model.itemFromIndex(indexes[0]) item_type = model_item.data(item_code['item_type']) if item_type == 'project_root': self.menu.addAction('EXPAND_ALL', lambda: self.expand_tree()) self.menu.addAction('COLLAPSE_ALL', lambda: self.collapse_tree()) self.menu.addAction( 'REFRESH_TREE', lambda: self.build_tree_view(self.qris_project, None)) elif item_type == "extent_folder": self.menu.addAction('ADD_PROJECT_EXTENT_LAYER', lambda: self.import_project_extent_layer()) self.menu.addAction('CREATE_BLANK_PROJECT_EXTENT_LAYER', lambda: self.create_blank_project_extent()) elif item_type == "layers_folder": self.menu.addAction('IMPORT_PROJECT_LAYER', lambda: self.import_project_layer()) elif item_type == "layer_node": self.menu.addAction( 'ADD_TO_MAP', lambda: add_to_map(self.qris_project, self.model, model_item)) elif item_type in ['extent_node', 'Project_Extent']: # self.menu.addAction('UPDATE_PROJECT_EXTENT', lambda: self.update_project_extent(model_item)) # self.menu.addAction('DELETE_PROJECT_EXTENT', lambda: self.delete_project_extent(model_item)) self.menu.addAction( 'ADD_TO_MAP', lambda: add_to_map(self.qris_project, self.model, model_item)) elif item_type == "design_folder": self.menu.addAction('ADD_DESIGN', lambda: self.add_design()) elif item_type == "design": self.menu.addAction( 'ADD_TO_MAP_OR_UPDATE_SYMBOLOGY', lambda: add_to_map(self.qris_project, self.model, model_item)) elif item_type == "structure_type_folder": self.menu.addAction('ADD_STRUCTURE_TYPE', lambda: self.add_structure_type()) elif item_type == "zoi_type_folder": self.menu.addAction('ADD_ZOI_TYPE', lambda: self.add_zoi_type()) elif item_type == "phase_folder": self.menu.addAction('ADD_PHASE', lambda: self.add_phase()) else: self.menu.clear() self.menu.exec_(self.treeView.viewport().mapToGlobal(position)) def expand_tree(self): self.treeView.expandAll() return def collapse_tree(self): self.treeView.collapseAll() return def add_assessment(self): """Initiates adding a new assessment""" self.assessment_dialog = AssessmentDlg(self.qris_project) self.assessment_dialog.dateEdit_assessment_date.setDate( QDate.currentDate()) self.assessment_dialog.dataChange.connect(self.build_tree_view) self.assessment_dialog.show() def add_design(self): """Initiates adding a new design""" self.design_dialog = DesignDlg(self.qris_project) # TODO remove this stuff about date self.design_dialog.dataChange.connect(self.build_tree_view) self.design_dialog.show() def add_structure_type(self): """Initiates adding a structure type and the structure type dialog""" # TODO First check if the path to the database exists design_geopackage_path = self.qris_project.project_designs.geopackage_path( self.qris_project.project_path) if os.path.exists(design_geopackage_path): self.structure_type_dialog = StructureTypeDlg(self.qris_project) self.structure_type_dialog.dataChange.connect(self.build_tree_view) self.structure_type_dialog.show() else: # TODO move the creation of the design data model so that this isn't necessary QMessageBox.information( self, "Structure Types", "Please create a new project design before adding structure types" ) def add_zoi_type(self): """Initiates adding a zoi type and the zoi type dialog""" # TODO First check if the path to the database exists design_geopackage_path = self.qris_project.project_designs.geopackage_path( self.qris_project.project_path) if os.path.exists(design_geopackage_path): self.zoi_type_dialog = ZoiTypeDlg(self.qris_project) self.zoi_type_dialog.dataChange.connect(self.build_tree_view) self.zoi_type_dialog.show() else: # TODO move the creation of the design data model so that this isn't necessary QMessageBox.information( self, "Structure Types", "Please create a new project design before adding a new influence type" ) def add_phase(self): """Initiates adding a new phase within the phase dialog""" # TODO First check if the path to the database exists design_geopackage_path = self.qris_project.project_designs.geopackage_path( self.qris_project.project_path) if os.path.exists(design_geopackage_path): self.phase_dialog = PhaseDlg(self.qris_project) self.phase_dialog.dataChange.connect(self.build_tree_view) self.phase_dialog.show() else: # TODO move the creation of the design data model so that this isn't necessary QMessageBox.information( self, "Structure Types", "Please create a new project design before adding phases") # This will kick off importing photos def import_photos(self): pass def add_detrended_raster(self): # last_browse_path = self.settings.getValue('lastBrowsePath') # last_dir = os.path.dirname(last_browse_path) if last_browse_path is not None else None dialog_return = QFileDialog.getOpenFileName( None, "Add Detrended Raster to QRiS project", None, self.tr("Raster Data Sources (*.tif)")) if dialog_return is not None and dialog_return[ 0] != "" and os.path.isfile(dialog_return[0]): self.addDetrendedDlg = AddDetrendedRasterDlg( None, dialog_return[0], self.qris_project) self.addDetrendedDlg.dataChange.connect(self.build_tree_view) self.addDetrendedDlg.exec() def import_project_extent_layer(self): """launches the dialog that supports import of a project extent layer polygon""" select_layer = QgsDataSourceSelectDialog() select_layer.exec() uri = select_layer.uri() if uri is not None and uri.isValid() and uri.wkbType == 3: self.project_extent_dialog = ProjectExtentDlg( uri, self.qris_project) self.project_extent_dialog.dataChange.connect(self.build_tree_view) self.project_extent_dialog.exec_() else: QMessageBox.critical(self, "Invalid Layer", "Please select a valid polygon layer") def create_blank_project_extent(self): """Adds a blank project extent that will be edited by the user""" self.project_extent_dialog = ProjectExtentDlg(None, self.qris_project) self.project_extent_dialog.dataChange.connect(self.build_tree_view) self.project_extent_dialog.exec_() def update_project_extent(self): """Renames the project extent layer""" pass # def delete_project_extent(self, selected_item): # """Deletes a project extent layer""" # display_name = selected_item.data(item_code['INSTANCE']).display_name # feature_name = selected_item.data(item_code['INSTANCE']).feature_name # geopackage_path = selected_item.data(item_code['INSTANCE']).geopackage_path(self.qris_project.project_path) # delete_ok = QMessageBox.question(self, f"Delete extent", f"Are you f*****g sure you wanna delete the extent layer: {display_name}") # if delete_ok == QMessageBox.Yes: # # remove from the map if it's there # # TODO consider doing this based on the path # for layer in QgsProject.instance().mapLayers().values(): # if layer.name() == display_name: # QgsProject.instance().removeMapLayers([layer.id()]) # iface.mapCanvas().refresh() # # TODO be sure to test whether the table exists first # gdal_delete = gdal.OpenEx(geopackage_path, gdal.OF_UPDATE, allowed_drivers=['GPKG']) # error = gdal_delete.DeleteLayer(feature_name) # gdal_delete.ExecuteSQL('VACUUM') # # TODO remove this from the Extents dictionary that will also remove from promect xml # del(self.qris_project.project_extents[feature_name]) # # refresh the project xml # self.qris_project.write_project_xml() # # refresh the tree # self.build_tree_view(self.qris_project, None) # else: # QMessageBox.information(self, "Delete extent", "No layers were deleted") def import_project_layer(self): """launches a dialog that supports import of project layers that can be clipped to a project extent""" select_layer = QgsDataSourceSelectDialog() select_layer.exec() uri = select_layer.uri() if uri is not None and uri.isValid(): # and uri.wkbType == 3: self.project_layer_dialog = ProjectLayerDlg(uri, self.qris_project) self.project_layer_dialog.dataChange.connect(self.build_tree_view) self.project_layer_dialog.exec_() else: QMessageBox.critical(self, "Invalid Layer", "Please select a valid gis layer") def explore_elevations(self, selected_item): raster = selected_item.data(item_code['INSTANCE']) self.elevation_widget = ElevationDockWidget(raster, self.qris_project) self.settings.iface.addDockWidget(Qt.LeftDockWidgetArea, self.elevation_widget) self.elevation_widget.dataChange.connect(self.build_tree_view) self.elevation_widget.show()