1# -*- coding: utf-8 -*- 2 3# Copyright (c) 2009 - 2021 Detlev Offenbach <detlev@die-offenbachs.de> 4# 5 6""" 7Module implementing the SQL Browser widget. 8""" 9 10from PyQt5.QtCore import pyqtSignal, QVariant, Qt, pyqtSlot 11from PyQt5.QtGui import QStandardItemModel 12from PyQt5.QtWidgets import QWidget, QDialog, QAbstractItemView 13from PyQt5.QtSql import ( 14 QSqlDatabase, QSqlError, QSqlTableModel, QSqlQueryModel, QSqlQuery 15) 16 17from E5Gui import E5MessageBox 18 19from .Ui_SqlBrowserWidget import Ui_SqlBrowserWidget 20 21 22class SqlBrowserWidget(QWidget, Ui_SqlBrowserWidget): 23 """ 24 Class implementing the SQL Browser widget. 25 26 @signal statusMessage(str) emitted to show a status message 27 """ 28 statusMessage = pyqtSignal(str) 29 30 cCount = 0 31 32 def __init__(self, parent=None): 33 """ 34 Constructor 35 36 @param parent reference to the parent widget (QWidget) 37 """ 38 super().__init__(parent) 39 self.setupUi(self) 40 41 self.table.addAction(self.insertRowAction) 42 self.table.addAction(self.deleteRowAction) 43 44 if len(QSqlDatabase.drivers()) == 0: 45 E5MessageBox.information( 46 self, 47 self.tr("No database drivers found"), 48 self.tr( 49 """This tool requires at least one Qt database driver. """ 50 """Please check the Qt documentation how to build the """ 51 """Qt SQL plugins.""")) 52 53 self.connections.tableActivated.connect( 54 self.on_connections_tableActivated) 55 self.connections.schemaRequested.connect( 56 self.on_connections_schemaRequested) 57 self.connections.cleared.connect(self.on_connections_cleared) 58 59 self.statusMessage.emit(self.tr("Ready")) 60 61 @pyqtSlot() 62 def on_clearButton_clicked(self): 63 """ 64 Private slot to clear the SQL entry widget. 65 """ 66 self.sqlEdit.clear() 67 self.sqlEdit.setFocus() 68 69 @pyqtSlot() 70 def on_executeButton_clicked(self): 71 """ 72 Private slot to execute the entered SQL query. 73 """ 74 self.executeQuery() 75 self.sqlEdit.setFocus() 76 77 @pyqtSlot() 78 def on_insertRowAction_triggered(self): 79 """ 80 Private slot handling the action to insert a new row. 81 """ 82 self.__insertRow() 83 84 @pyqtSlot() 85 def on_deleteRowAction_triggered(self): 86 """ 87 Private slot handling the action to delete a row. 88 """ 89 self.__deleteRow() 90 91 @pyqtSlot(str) 92 def on_connections_tableActivated(self, table): 93 """ 94 Private slot to show the contents of a table. 95 96 @param table name of the table for which to show the contents (string) 97 """ 98 self.showTable(table) 99 100 @pyqtSlot(str) 101 def on_connections_schemaRequested(self, table): 102 """ 103 Private slot to show the schema of a table. 104 105 @param table name of the table for which to show the schema (string) 106 """ 107 self.showSchema(table) 108 109 @pyqtSlot() 110 def on_connections_cleared(self): 111 """ 112 Private slot to clear the table. 113 """ 114 model = QStandardItemModel(self.table) 115 self.table.setModel(model) 116 self.table.setEditTriggers( 117 QAbstractItemView.EditTrigger.NoEditTriggers) 118 119 self.updateActions() 120 121 def addConnection(self, driver, dbName, user, password, host, port): 122 """ 123 Public method to add a database connection. 124 125 @param driver name of the Qt database driver (string) 126 @param dbName name of the database (string) 127 @param user user name (string) 128 @param password password (string) 129 @param host host name (string) 130 @param port port number (integer) 131 @return SQL error object (QSqlError) 132 """ 133 err = QSqlError() 134 135 self.__class__.cCount += 1 136 db = QSqlDatabase.addDatabase( 137 driver.upper(), "Browser{0:d}".format(self.__class__.cCount)) 138 db.setDatabaseName(dbName) 139 db.setHostName(host) 140 db.setPort(port) 141 if not db.open(user, password): 142 err = db.lastError() 143 db = QSqlDatabase() 144 QSqlDatabase.removeDatabase( 145 "Browser{0:d}".format(self.__class__.cCount)) 146 147 self.connections.refresh() 148 149 return err 150 151 def addConnectionByDialog(self): 152 """ 153 Public slot to add a database connection via an input dialog. 154 """ 155 from .SqlConnectionDialog import SqlConnectionDialog 156 dlg = SqlConnectionDialog(self) 157 if dlg.exec() == QDialog.DialogCode.Accepted: 158 driver, dbName, user, password, host, port = dlg.getData() 159 err = self.addConnection( 160 driver, dbName, user, password, host, port) 161 162 if err.type() != QSqlError.ErrorType.NoError: 163 E5MessageBox.warning( 164 self, 165 self.tr("Unable to open database"), 166 self.tr( 167 """An error occurred while opening the connection.""")) 168 169 def showTable(self, table): 170 """ 171 Public slot to show the contents of a table. 172 173 @param table name of the table to be shown (string) 174 """ 175 model = QSqlTableModel(self.table, self.connections.currentDatabase()) 176 model.setEditStrategy(QSqlTableModel.EditStrategy.OnRowChange) 177 model.setTable(table) 178 model.select() 179 if model.lastError().type() != QSqlError.ErrorType.NoError: 180 self.statusMessage.emit(model.lastError().text()) 181 self.table.setModel(model) 182 self.table.setEditTriggers( 183 QAbstractItemView.EditTrigger.DoubleClicked | 184 QAbstractItemView.EditTrigger.EditKeyPressed) 185 186 self.table.resizeColumnsToContents() 187 188 self.table.selectionModel().currentRowChanged.connect( 189 self.updateActions) 190 191 self.updateActions() 192 193 def showSchema(self, table): 194 """ 195 Public slot to show the schema of a table. 196 197 @param table name of the table to be shown (string) 198 """ 199 rec = self.connections.currentDatabase().record(table) 200 model = QStandardItemModel(self.table) 201 202 model.insertRows(0, rec.count()) 203 model.insertColumns(0, 7) 204 205 model.setHeaderData(0, Qt.Orientation.Horizontal, "Fieldname") 206 model.setHeaderData(1, Qt.Orientation.Horizontal, "Type") 207 model.setHeaderData(2, Qt.Orientation.Horizontal, "Length") 208 model.setHeaderData(3, Qt.Orientation.Horizontal, "Precision") 209 model.setHeaderData(4, Qt.Orientation.Horizontal, "Required") 210 model.setHeaderData(5, Qt.Orientation.Horizontal, "Auto Value") 211 model.setHeaderData(6, Qt.Orientation.Horizontal, "Default Value") 212 213 for i in range(rec.count()): 214 fld = rec.field(i) 215 model.setData(model.index(i, 0), fld.name()) 216 if fld.typeID() == -1: 217 model.setData(model.index(i, 1), 218 QVariant.typeToName(fld.type())) 219 else: 220 model.setData( 221 model.index(i, 1), "{0} ({1})".format( 222 QVariant.typeToName(fld.type()), fld.typeID())) 223 if fld.length() < 0: 224 model.setData(model.index(i, 2), "?") 225 else: 226 model.setData(model.index(i, 2), fld.length()) 227 if fld.precision() < 0: 228 model.setData(model.index(i, 3), "?") 229 else: 230 model.setData(model.index(i, 3), fld.precision()) 231 if fld.requiredStatus() == -1: 232 model.setData(model.index(i, 4), "?") 233 else: 234 model.setData(model.index(i, 4), bool(fld.requiredStatus())) 235 model.setData(model.index(i, 5), fld.isAutoValue()) 236 model.setData(model.index(i, 6), fld.defaultValue()) 237 238 self.table.setModel(model) 239 self.table.setEditTriggers( 240 QAbstractItemView.EditTrigger.NoEditTriggers) 241 242 self.table.resizeColumnsToContents() 243 244 self.updateActions() 245 246 def updateActions(self): 247 """ 248 Public slot to update the actions. 249 """ 250 enableIns = isinstance(self.table.model(), QSqlTableModel) 251 enableDel = enableIns & self.table.currentIndex().isValid() 252 253 self.insertRowAction.setEnabled(enableIns) 254 self.deleteRowAction.setEnabled(enableDel) 255 256 def __insertRow(self): 257 """ 258 Private slot to insert a row into the database table. 259 """ 260 model = self.table.model() 261 if not isinstance(model, QSqlTableModel): 262 return 263 264 insertIndex = self.table.currentIndex() 265 row = 0 if insertIndex.row() == -1 else insertIndex.row() 266 model.insertRow(row) 267 insertIndex = model.index(row, 0) 268 self.table.setCurrentIndex(insertIndex) 269 self.table.edit(insertIndex) 270 271 def __deleteRow(self): 272 """ 273 Private slot to delete a row from the database table. 274 """ 275 model = self.table.model() 276 if not isinstance(model, QSqlTableModel): 277 return 278 279 model.setEditStrategy(QSqlTableModel.EditStrategy.OnManualSubmit) 280 281 currentSelection = self.table.selectionModel().selectedIndexes() 282 for selectedIndex in currentSelection: 283 if selectedIndex.column() != 0: 284 continue 285 model.removeRow(selectedIndex.row()) 286 287 model.submitAll() 288 model.setEditStrategy(QSqlTableModel.EditStrategy.OnRowChange) 289 290 self.updateActions() 291 292 def executeQuery(self): 293 """ 294 Public slot to execute the entered query. 295 """ 296 model = QSqlQueryModel(self.table) 297 model.setQuery(QSqlQuery( 298 self.sqlEdit.toPlainText(), self.connections.currentDatabase())) 299 self.table.setModel(model) 300 301 if model.lastError().type() != QSqlError.ErrorType.NoError: 302 self.statusMessage.emit(model.lastError().text()) 303 elif model.query().isSelect(): 304 self.statusMessage.emit(self.tr("Query OK.")) 305 else: 306 self.statusMessage.emit( 307 self.tr("Query OK, number of affected rows: {0}") 308 .format(model.query().numRowsAffected())) 309 310 self.table.resizeColumnsToContents() 311 312 self.updateActions() 313