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