diff --git a/src/kdefrontend/datasources/DatabaseManagerWidget.cpp b/src/kdefrontend/datasources/DatabaseManagerWidget.cpp index f5a112715..93b4370f3 100644 --- a/src/kdefrontend/datasources/DatabaseManagerWidget.cpp +++ b/src/kdefrontend/datasources/DatabaseManagerWidget.cpp @@ -1,599 +1,610 @@ /*************************************************************************** File : DatabaseManagerWidget.cpp Project : LabPlot Description : widget for managing database connections -------------------------------------------------------------------- Copyright : (C) 2017-2018 Alexander Semke (alexander.semke@web.de) ***************************************************************************/ /*************************************************************************** * * * This program is free software; you can redistribute it and/or modify * * it under the terms of the GNU General Public License as published by * * the Free Software Foundation; either version 2 of the License, or * * (at your option) any later version. * * * * This program is distributed in the hope that it will be useful, * * but WITHOUT ANY WARRANTY; without even the implied warranty of * * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * * GNU General Public License for more details. * * * * You should have received a copy of the GNU General Public License * * along with this program; if not, write to the Free Software * * Foundation, Inc., 51 Franklin Street, Fifth Floor, * * Boston, MA 02110-1301 USA * * * ***************************************************************************/ #include "DatabaseManagerWidget.h" #include "backend/lib/macros.h" #include #include #include #include #include #include #include #include #include /*! \class DatabaseManagerWidget \brief widget for managing database connections, embedded in \c DatabaseManagerDialog. \ingroup kdefrontend */ DatabaseManagerWidget::DatabaseManagerWidget(QWidget* parent, QString conn) : QWidget(parent), m_initConnName(std::move(conn)) { m_configPath = QStandardPaths::standardLocations(QStandardPaths::AppDataLocation).constFirst() + "sql_connections"; ui.setupUi(this); ui.tbAdd->setIcon(QIcon::fromTheme("list-add")); ui.tbDelete->setIcon(QIcon::fromTheme("list-remove")); ui.bOpen->setIcon(QIcon::fromTheme("document-open")); ui.bTestConnection->setIcon(QIcon::fromTheme("network-connect")); ui.tbAdd->setToolTip(i18n("Add new database connection")); ui.tbDelete->setToolTip(i18n("Delete selected database connection")); ui.bOpen->setToolTip(i18n("Open database file")); ui.bTestConnection->setToolTip(i18n("Test selected database connection")); //add the list of supported SQL drivers ui.cbDriver->addItems(QSqlDatabase::drivers()); //SIGNALs/SLOTs connect(ui.lwConnections, &QListWidget::currentRowChanged, this, &DatabaseManagerWidget::connectionChanged); connect(ui.tbAdd, &QToolButton::clicked, this, &DatabaseManagerWidget::addConnection); connect(ui.tbDelete, &QToolButton::clicked, this, &DatabaseManagerWidget::deleteConnection); connect(ui.bTestConnection, &QPushButton::clicked, this, &DatabaseManagerWidget::testConnection); connect(ui.bOpen, &QPushButton::clicked, this, &DatabaseManagerWidget::selectFile); connect(ui.cbDriver, static_cast(&QComboBox::currentIndexChanged), this, &DatabaseManagerWidget::driverChanged); connect(ui.leName, &QLineEdit::textChanged, this, &DatabaseManagerWidget::nameChanged); connect(ui.leDatabase, &QLineEdit::textChanged, this, &DatabaseManagerWidget::databaseNameChanged); connect(ui.leHost, &QLineEdit::textChanged, this, &DatabaseManagerWidget::hostChanged); connect(ui.sbPort, static_cast(&QSpinBox::valueChanged), this, &DatabaseManagerWidget::portChanged); connect(ui.chkCustomConnection, &QCheckBox::stateChanged, this, &DatabaseManagerWidget::customConnectionEnabledChanged); connect(ui.teCustomConnection, &QPlainTextEdit::textChanged, this, &DatabaseManagerWidget::customConnectionChanged); connect(ui.leUserName, &QLineEdit::textChanged, this, &DatabaseManagerWidget::userNameChanged); connect(ui.lePassword, &QLineEdit::textChanged, this, &DatabaseManagerWidget::passwordChanged); QTimer::singleShot(100, this, &DatabaseManagerWidget::loadConnections); } QString DatabaseManagerWidget::connection() const { if (ui.lwConnections->currentItem()) return ui.lwConnections->currentItem()->text(); else return QString(); } /*! shows the settings of the currently selected connection */ void DatabaseManagerWidget::connectionChanged(int index) { if (m_initializing) return; - if (index == -1) + if (index == -1) { + m_current_connection = nullptr; return; + } + m_current_connection = &m_connections[index]; //show the settings for the selected connection m_initializing = true; - const QString& driver = m_connections[index].driver; - ui.leName->setText(m_connections[index].name); + const QString& driver = m_current_connection->driver; + ui.leName->setText(m_current_connection->name); ui.cbDriver->setCurrentIndex(ui.cbDriver->findText(driver)); - ui.leDatabase->setText(m_connections[index].dbName); + ui.leDatabase->setText(m_current_connection->dbName); //no host and port number required for file DB and ODBC connections if (!isFileDB(driver) || !isODBC(driver)) { - ui.leHost->setText(m_connections[index].hostName); - ui.sbPort->setValue(m_connections[index].port); + ui.leHost->setText(m_current_connection->hostName); + ui.sbPort->setValue(m_current_connection->port); } //no credentials required for file DB if (!isFileDB(driver)) { - ui.leUserName->setText(m_connections[index].userName); - ui.lePassword->setText(m_connections[index].password); + ui.leUserName->setText(m_current_connection->userName); + ui.lePassword->setText(m_current_connection->password); } if (isODBC(driver)) { - ui.chkCustomConnection->setChecked(m_connections[index].customConnectionEnabled); - ui.teCustomConnection->setPlainText(m_connections[index].customConnectionString); + ui.chkCustomConnection->setChecked(m_current_connection->customConnectionEnabled); + ui.teCustomConnection->setPlainText(m_current_connection->customConnectionString); } m_initializing = false; } void DatabaseManagerWidget::nameChanged(const QString& name) { //check uniqueness of the provided name bool unique = true; for (int i = 0; i < ui.lwConnections->count(); ++i) { if (ui.lwConnections->currentRow() == i) continue; if (name == ui.lwConnections->item(i)->text()) { unique = false; break; } } if (unique) { ui.leName->setStyleSheet(""); - ui.lwConnections->currentItem()->setText(name); + if (auto item = ui.lwConnections->currentItem()) { + item->setText(name); - if (!m_initializing) { - m_connections[ui.lwConnections->currentRow()].name = name; - emit changed(); + if (!m_initializing) { + m_current_connection->name = name; + emit changed(); + } } } else ui.leName->setStyleSheet("QLineEdit{background: red;}"); } void DatabaseManagerWidget::driverChanged() { //hide non-relevant fields (like host name, etc.) for file DBs and ODBC const QString& driver = ui.cbDriver->currentText(); - const bool fileDB = isFileDB(driver); - ui.lHost->setVisible(!fileDB); - ui.leHost->setVisible(!fileDB); - ui.lPort->setVisible(!fileDB); - ui.sbPort->setVisible(!fileDB); - ui.bOpen->setVisible(fileDB); - ui.gbAuthentication->setVisible(!fileDB); if (isFileDB(driver)) { ui.lHost->hide(); ui.leHost->hide(); ui.lPort->hide(); ui.sbPort->hide(); ui.bOpen->show(); ui.gbAuthentication->hide(); ui.lDatabase->setText(i18n("Database:")); ui.leDatabase->setEnabled(true); ui.lCustomConnection->hide(); ui.chkCustomConnection->hide(); ui.teCustomConnection->hide(); } else if (isODBC(driver)) { ui.lHost->hide(); ui.leHost->hide(); ui.lPort->hide(); ui.sbPort->hide(); ui.bOpen->hide(); ui.gbAuthentication->show(); ui.lDatabase->setText(i18n("Data Source Name:")); ui.lCustomConnection->show(); ui.chkCustomConnection->show(); const bool customConnection = ui.chkCustomConnection->isChecked(); ui.leDatabase->setEnabled(!customConnection); ui.teCustomConnection->setVisible(customConnection); } else { ui.lHost->show(); ui.leHost->show(); ui.lPort->show(); ui.sbPort->show(); + ui.sbPort->setValue(defaultPort(driver)); ui.bOpen->hide(); ui.gbAuthentication->show(); ui.lDatabase->setText(i18n("Database:")); ui.leDatabase->setEnabled(true); ui.lCustomConnection->hide(); ui.chkCustomConnection->hide(); ui.teCustomConnection->hide(); } if (m_initializing) return; - m_connections[ui.lwConnections->currentRow()].driver = driver; + if (m_current_connection) + m_current_connection->driver = driver; emit changed(); } void DatabaseManagerWidget::selectFile() { KConfigGroup conf(KSharedConfig::openConfig(), QLatin1String("DatabaseManagerWidget")); QString dir = conf.readEntry(QLatin1String("LastDir"), ""); QString path = QFileDialog::getOpenFileName(this, i18n("Select the database file"), dir); if (path.isEmpty()) return; //cancel was clicked in the file-dialog int pos = path.lastIndexOf(QDir::separator()); if (pos != -1) { QString newDir = path.left(pos); if (newDir != dir) conf.writeEntry(QLatin1String("LastDir"), newDir); } ui.leDatabase->setText(path); } void DatabaseManagerWidget::hostChanged() { if (m_initializing) return; - m_connections[ui.lwConnections->currentRow()].hostName = ui.leHost->text(); + if (m_current_connection) + m_current_connection->hostName = ui.leHost->text(); //don't allow to try to connect if no hostname provided ui.bTestConnection->setEnabled( !ui.leHost->text().simplified().isEmpty() ); emit changed(); } void DatabaseManagerWidget::portChanged() { if (m_initializing) return; - m_connections[ui.lwConnections->currentRow()].port = ui.sbPort->value(); + if (m_current_connection) + m_current_connection->port = ui.sbPort->value(); emit changed(); } void DatabaseManagerWidget::databaseNameChanged() { QString dbName = ui.leDatabase->text().simplified(); if (isFileDB(ui.cbDriver->currentText())) { #ifndef HAVE_WINDOWS // make relative path if ( !dbName.isEmpty() && dbName.at(0) != QDir::separator()) dbName = QDir::homePath() + QDir::separator() + dbName; #endif if (!dbName.isEmpty()) { bool fileExists = QFile::exists(dbName); if (fileExists) ui.leDatabase->setStyleSheet(""); else ui.leDatabase->setStyleSheet("QLineEdit{background:red;}"); } else { ui.leDatabase->setStyleSheet(""); } } else { ui.leDatabase->setStyleSheet(""); } //don't allow to try to connect if no database name was provided ui.bTestConnection->setEnabled( !dbName.isEmpty() ); if (m_initializing) return; - m_connections[ui.lwConnections->currentRow()].dbName = dbName; + if (m_current_connection) + m_current_connection->dbName = dbName; emit changed(); } void DatabaseManagerWidget::customConnectionEnabledChanged(int state) { //in case custom connection string is provided: //disable the line edit for the database name //and hide the textedit for the connection string ui.leDatabase->setEnabled(state != Qt::Checked); ui.teCustomConnection->setVisible(state == Qt::Checked); if (state == Qt::Checked) ui.teCustomConnection->setFocus(); else ui.leDatabase->setFocus(); - m_connections[ui.lwConnections->currentRow()].customConnectionEnabled = (state == Qt::Checked); + if (m_current_connection) + m_current_connection->customConnectionEnabled = (state == Qt::Checked); emit changed(); } void DatabaseManagerWidget::customConnectionChanged() { - m_connections[ui.lwConnections->currentRow()].customConnectionString = ui.teCustomConnection->toPlainText(); + if (m_current_connection) + m_current_connection->customConnectionString = ui.teCustomConnection->toPlainText(); emit changed(); } void DatabaseManagerWidget::userNameChanged() { if (m_initializing) return; - m_connections[ui.lwConnections->currentRow()].userName = ui.leUserName->text(); + if (m_current_connection) + m_current_connection->userName = ui.leUserName->text(); emit changed(); } void DatabaseManagerWidget::passwordChanged() { if (m_initializing) return; - m_connections[ui.lwConnections->currentRow()].password = ui.lePassword->text(); + if (m_current_connection) + m_current_connection->password = ui.lePassword->text(); emit changed(); } void DatabaseManagerWidget::addConnection() { DEBUG("Adding new connection"); SQLConnection conn; conn.name = uniqueName(); conn.driver = ui.cbDriver->currentText(); conn.hostName = QLatin1String("localhost"); if (!isFileDB(conn.driver) && !isODBC(conn.driver)) conn.port = defaultPort(conn.driver); m_connections.append(conn); ui.lwConnections->addItem(conn.name); ui.lwConnections->setCurrentRow(m_connections.size()-1); m_initializing = true; //call this to properly update the widgets for the very first added connection driverChanged(); m_initializing = false; //we have now more then one connection, enable widgets ui.tbDelete->setEnabled(true); ui.leName->setEnabled(true); ui.leDatabase->setEnabled(true); ui.cbDriver->setEnabled(true); ui.leHost->setEnabled(true); ui.sbPort->setEnabled(true); ui.leUserName->setEnabled(true); ui.lePassword->setEnabled(true); } /*! removes the current selected connection. */ void DatabaseManagerWidget::deleteConnection() { int ret = KMessageBox::questionYesNo(this, i18n("Do you really want to delete the connection '%1'?", ui.lwConnections->currentItem()->text()), i18n("Delete Connection")); if (ret != KMessageBox::Yes) return; //remove the current selected connection - m_connections.removeAt(ui.lwConnections->currentRow()); - m_initializing = true; - QListWidgetItem* item = ui.lwConnections->takeItem(ui.lwConnections->currentRow()); - if (item) delete item; - m_initializing = false; + int row = ui.lwConnections->currentRow(); + if (row != -1) { + m_connections.removeAt(row); + m_initializing = true; + delete ui.lwConnections->takeItem(row); + m_initializing = false; + } //show the connection for the item that was automatically selected afte the deletion connectionChanged(ui.lwConnections->currentRow()); //disable widgets if there're no connections anymore if (m_connections.size() == 0) { m_initializing = true; ui.tbDelete->setEnabled(false); ui.bTestConnection->setEnabled(false); ui.leName->clear(); ui.leName->setEnabled(false); ui.leDatabase->clear(); ui.leDatabase->setEnabled(false); ui.cbDriver->setEnabled(false); ui.leHost->clear(); ui.leHost->setEnabled(false); ui.sbPort->clear(); ui.sbPort->setEnabled(false); ui.leUserName->clear(); ui.leUserName->setEnabled(false); ui.lePassword->clear(); ui.lePassword->setEnabled(false); ui.teCustomConnection->clear(); m_initializing = false; } emit changed(); } void DatabaseManagerWidget::loadConnections() { QDEBUG("Loading connections from " << m_configPath); m_initializing = true; KConfig config(m_configPath, KConfig::SimpleConfig); for (const auto& groupName : config.groupList()) { const KConfigGroup& group = config.group(groupName); SQLConnection conn; conn.name = groupName; conn.driver = group.readEntry("Driver",""); conn.dbName = group.readEntry("DatabaseName", ""); if (!isFileDB(conn.driver) && !isODBC(conn.driver)) { conn.hostName = group.readEntry("HostName", "localhost"); conn.port = group.readEntry("Port", defaultPort(conn.driver)); } if (!isFileDB(conn.driver)) { conn.userName = group.readEntry("UserName", "root"); conn.password = group.readEntry("Password", ""); } if (isODBC(conn.driver)) { conn.customConnectionEnabled = group.readEntry("CustomConnectionEnabled", false); conn.customConnectionString = group.readEntry("CustomConnectionString", ""); } m_connections.append(conn); ui.lwConnections->addItem(conn.name); } //show the first connection if available, create a new connection otherwise if (m_connections.size()) { if (!m_initConnName.isEmpty()) { QListWidgetItem* item = ui.lwConnections->findItems(m_initConnName, Qt::MatchExactly).constFirst(); if (item) ui.lwConnections->setCurrentItem(item); else ui.lwConnections->setCurrentRow(ui.lwConnections->count()-1); } else { ui.lwConnections->setCurrentRow(ui.lwConnections->count()-1); } } else addConnection(); //show/hide the driver dependent options driverChanged(); m_initializing = false; //show the settings of the current connection connectionChanged(ui.lwConnections->currentRow()); } void DatabaseManagerWidget::saveConnections() { QDEBUG("Saving connections to " + m_configPath); //delete saved connections KConfig config(m_configPath, KConfig::SimpleConfig); for (const auto& group : config.groupList()) config.deleteGroup(group); //save connections for (const auto& conn : m_connections) { KConfigGroup group = config.group(conn.name); group.writeEntry("Driver", conn.driver); group.writeEntry("DatabaseName", conn.dbName); if (!isFileDB(conn.driver) && !isODBC(conn.driver)) { group.writeEntry("HostName", conn.hostName); group.writeEntry("Port", conn.port); } if (!isFileDB(conn.driver)) { group.writeEntry("UserName", conn.userName); group.writeEntry("Password", conn.password); } if (isODBC(conn.driver)) { group.writeEntry("CustomConnectionEnabled", conn.customConnectionEnabled); group.writeEntry("CustomConnectionString", conn.customConnectionString); } } config.sync(); } void DatabaseManagerWidget::testConnection() { - int row = ui.lwConnections->currentRow(); + if (!m_current_connection) + return; //don't allow to test the connection for file DBs if the file doesn't exist if (isFileDB(ui.cbDriver->currentText())) { QString fileName = ui.leDatabase->text(); #ifndef HAVE_WINDOWS // make relative path if ( !fileName.isEmpty() && fileName.at(0) != QDir::separator()) fileName = QDir::homePath() + QDir::separator() + fileName; #endif if (!QFile::exists(fileName)) { - KMessageBox::error(this, i18n("Failed to connect to the database '%1'.", m_connections[row].dbName), + KMessageBox::error(this, i18n("Failed to connect to the database '%1'.", m_current_connection->dbName), i18n("Connection Failed")); return; } } WAIT_CURSOR; - const QString& driver = m_connections[row].driver; + const QString& driver = m_current_connection->driver; QSqlDatabase db = QSqlDatabase::addDatabase(driver); + db.close(); //db name or custom connection string for ODBC, if available - if (isODBC(driver) && m_connections[row].customConnectionEnabled) - db.setDatabaseName(m_connections[row].customConnectionString); + if (isODBC(driver) && m_current_connection->customConnectionEnabled) + db.setDatabaseName(m_current_connection->customConnectionString); else - db.setDatabaseName(m_connections[row].dbName); + db.setDatabaseName(m_current_connection->dbName); //host and port number, if required if (!isFileDB(driver) && !isODBC(driver)) { - db.setHostName(m_connections[row].hostName); - db.setPort(m_connections[row].port); + db.setHostName(m_current_connection->hostName); + db.setPort(m_current_connection->port); } //authentication, if required if (!isFileDB(driver)) { - db.setUserName(m_connections[row].userName); - db.setPassword(m_connections[row].password); + db.setUserName(m_current_connection->userName); + db.setPassword(m_current_connection->password); } if (db.isValid() && db.open() && db.isOpen()) { db.close(); RESET_CURSOR; - KMessageBox::information(this, i18n("Connection to the database '%1' was successful.", m_connections[row].dbName), + KMessageBox::information(this, i18n("Connection to the database '%1' was successful.", m_current_connection->dbName), i18n("Connection Successful")); } else { RESET_CURSOR; - KMessageBox::error(this, i18n("Failed to connect to the database '%1'.", m_connections[row].dbName) + + KMessageBox::error(this, i18n("Failed to connect to the database '%1'.", m_current_connection->dbName) + QLatin1String("\n\n") + db.lastError().databaseText(), i18n("Connection Failed")); } } /*! * returns \c true if \c driver is for file databases like Sqlite or for ODBC datasources. * returns \false otherwise. * for file databases and for ODBC/ODBC3, only the name of the database/ODBC-datasource is required. * used to show/hide relevant connection settins widgets. */ bool DatabaseManagerWidget::isFileDB(const QString& driver) { //QSQLITE, QSQLITE3 return driver.startsWith(QLatin1String("QSQLITE")); } bool DatabaseManagerWidget::isODBC(const QString& driver) { //QODBC, QODBC3 return driver.startsWith(QLatin1String("QODBC")); } QString DatabaseManagerWidget::uniqueName() { QString name = i18n("New connection"); //TODO QStringList connection_names; for (int row = 0; row < ui.lwConnections->count(); row++) connection_names << ui.lwConnections->item(row)->text(); if (!connection_names.contains(name)) return name; QString base = name; int last_non_digit; for (last_non_digit = base.size()-1; last_non_digit>=0 && base[last_non_digit].category() == QChar::Number_DecimalDigit; --last_non_digit) base.chop(1); if (last_non_digit >=0 && base[last_non_digit].category() != QChar::Separator_Space) base.append(" "); int new_nr = name.rightRef(name.size() - base.size()).toInt(); QString new_name; do new_name = base + QString::number(++new_nr); while (connection_names.contains(new_name)); return new_name; } int DatabaseManagerWidget::defaultPort(const QString& driver) const { // QDB2 IBM DB2 (version 7.1 and above) // QIBASE Borland InterBase // QMYSQL MySQL // QOCI Oracle Call Interface Driver // QODBC Open Database Connectivity (ODBC) - Microsoft SQL Server and other ODBC-compliant databases // QPSQL PostgreSQL (versions 7.3 and above) if (driver == "QDB2") return 50000; else if (driver == "QIBASE") return 3050; else if (driver == "QMYSQL3" || driver == "QMYSQL") return 3306; else if (driver == "QOCI") return 1521; else if (driver == "QODBC") return 1433; else if (driver == "QPSQL") return 5432; else return 0; } diff --git a/src/kdefrontend/datasources/DatabaseManagerWidget.h b/src/kdefrontend/datasources/DatabaseManagerWidget.h index b19aba972..5333709f2 100644 --- a/src/kdefrontend/datasources/DatabaseManagerWidget.h +++ b/src/kdefrontend/datasources/DatabaseManagerWidget.h @@ -1,91 +1,92 @@ /*************************************************************************** File : DatabaseManagerWidget.h Project : LabPlot Description : widget for managing database connections -------------------------------------------------------------------- Copyright : (C) 2017 Alexander Semke (alexander.semke@web.de) ***************************************************************************/ /*************************************************************************** * * * This program is free software; you can redistribute it and/or modify * * it under the terms of the GNU General Public License as published by * * the Free Software Foundation; either version 2 of the License, or * * (at your option) any later version. * * * * This program is distributed in the hope that it will be useful, * * but WITHOUT ANY WARRANTY; without even the implied warranty of * * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * * GNU General Public License for more details. * * * * You should have received a copy of the GNU General Public License * * along with this program; if not, write to the Free Software * * Foundation, Inc., 51 Franklin Street, Fifth Floor, * * Boston, MA 02110-1301 USA * * * ***************************************************************************/ #ifndef DATABASEMANAGERWIDGET_H #define DATABASEMANAGERWIDGET_H #include "ui_databasemanagerwidget.h" class DatabaseManagerWidget : public QWidget { Q_OBJECT public: explicit DatabaseManagerWidget(QWidget*, QString); struct SQLConnection { int port; QString name; QString driver; QString hostName; QString dbName; QString userName; QString password; bool customConnectionEnabled{false}; QString customConnectionString; }; QString connection() const; void setCurrentConnection(const QString&); void saveConnections(); static bool isFileDB(const QString&); static bool isODBC(const QString&); private: Ui::DatabaseManagerWidget ui; QList m_connections; + SQLConnection* m_current_connection = nullptr; bool m_initializing{false}; QString m_configPath; QString m_initConnName; QString uniqueName(); void loadConnection(); int defaultPort(const QString&) const; void dataChanged(); private slots: void loadConnections(); void addConnection(); void deleteConnection(); void testConnection(); void connectionChanged(int); void nameChanged(const QString&); void driverChanged(); void selectFile(); void hostChanged(); void portChanged(); void databaseNameChanged(); void customConnectionEnabledChanged(int); void customConnectionChanged(); void userNameChanged(); void passwordChanged(); signals: void changed(); }; #endif diff --git a/src/kdefrontend/datasources/ImportSQLDatabaseWidget.cpp b/src/kdefrontend/datasources/ImportSQLDatabaseWidget.cpp index ad287e0f1..a43751220 100644 --- a/src/kdefrontend/datasources/ImportSQLDatabaseWidget.cpp +++ b/src/kdefrontend/datasources/ImportSQLDatabaseWidget.cpp @@ -1,480 +1,486 @@ /*************************************************************************** File : ImportSQLDatabaseWidget.cpp Project : LabPlot Description : Datapicker -------------------------------------------------------------------- Copyright : (C) 2016 by Ankit Wagadre (wagadre.ankit@gmail.com) Copyright : (C) 2016-2017 Alexander Semke (alexander.semke@web.de) ***************************************************************************/ /*************************************************************************** * * * This program is free software; you can redistribute it and/or modify * * it under the terms of the GNU General Public License as published by * * the Free Software Foundation; either version 2 of the License, or * * (at your option) any later version. * * * * This program is distributed in the hope that it will be useful, * * but WITHOUT ANY WARRANTY; without even the implied warranty of * * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * * GNU General Public License for more details. * * * * You should have received a copy of the GNU General Public License * * along with this program; if not, write to the Free Software * * Foundation, Inc., 51 Franklin Street, Fifth Floor, * * Boston, MA 02110-1301 USA * * * ***************************************************************************/ #include "ImportSQLDatabaseWidget.h" #include "DatabaseManagerDialog.h" #include "DatabaseManagerWidget.h" #include "backend/datasources/AbstractDataSource.h" #include "backend/datasources/filters/AbstractFileFilter.h" #include "backend/lib/macros.h" #include #include #include #include #include #ifdef HAVE_KF5_SYNTAX_HIGHLIGHTING #include #include #include #endif #include #include ImportSQLDatabaseWidget::ImportSQLDatabaseWidget(QWidget* parent) : QWidget(parent) { ui.setupUi(this); ui.cbImportFrom->addItem(i18n("Table")); ui.cbImportFrom->addItem(i18n("Custom query")); ui.bDatabaseManager->setIcon(QIcon::fromTheme("network-server-database")); ui.bDatabaseManager->setToolTip(i18n("Manage connections")); ui.twPreview->setEditTriggers(QAbstractItemView::NoEditTriggers); ui.cbNumberFormat->addItems(AbstractFileFilter::numberFormats()); ui.cbDateTimeFormat->addItems(AbstractColumn::dateTimeFormats()); #ifdef HAVE_KF5_SYNTAX_HIGHLIGHTING m_highlighter = new KSyntaxHighlighting::SyntaxHighlighter(ui.teQuery->document()); m_highlighter->setDefinition(m_repository.definitionForName("SQL")); m_highlighter->setTheme( (palette().color(QPalette::Base).lightness() < 128) ? m_repository.defaultTheme(KSyntaxHighlighting::Repository::DarkTheme) : m_repository.defaultTheme(KSyntaxHighlighting::Repository::LightTheme) ); #endif m_configPath = QStandardPaths::standardLocations(QStandardPaths::AppDataLocation).constFirst() + "sql_connections"; connect( ui.cbConnection, SIGNAL(currentIndexChanged(int)), SLOT(connectionChanged()) ); connect( ui.cbImportFrom, SIGNAL(currentIndexChanged(int)), SLOT(importFromChanged(int)) ); connect( ui.bDatabaseManager, SIGNAL(clicked()), this, SLOT(showDatabaseManager()) ); connect( ui.lwTables, SIGNAL(currentRowChanged(int)), this, SLOT(refreshPreview()) ); connect( ui.bRefreshPreview, SIGNAL(clicked()), this, SLOT(refreshPreview()) ); //defer the loading of settings a bit in order to show the dialog prior to blocking the GUI in refreshPreview() QTimer::singleShot( 100, this, SLOT(loadSettings()) ); } void ImportSQLDatabaseWidget::loadSettings() { m_initializing = true; //read available connections readConnections(); //load last used connection and other settings KConfigGroup config(KSharedConfig::openConfig(), "ImportSQLDatabaseWidget"); ui.cbConnection->setCurrentIndex(ui.cbConnection->findText(config.readEntry("Connection", ""))); ui.cbImportFrom->setCurrentIndex(config.readEntry("ImportFrom", 0)); importFromChanged(ui.cbImportFrom->currentIndex()); ui.cbNumberFormat->setCurrentIndex(config.readEntry("NumberFormat", (int)QLocale::AnyLanguage)); ui.cbDateTimeFormat->setCurrentItem(config.readEntry("DateTimeFormat", "yyyy-dd-MM hh:mm:ss:zzz")); QList defaultSizes; defaultSizes << 100 << 100; ui.splitterMain->setSizes(config.readEntry("SplitterMainSizes", defaultSizes)); ui.splitterPreview->setSizes(config.readEntry("SplitterPreviewSizes", defaultSizes)); //TODO m_initializing = false; //all settings loaded -> trigger the selection of the last used connection in order to get the data preview connectionChanged(); } ImportSQLDatabaseWidget::~ImportSQLDatabaseWidget() { // save current settings KConfigGroup config(KSharedConfig::openConfig(), "ImportSQLDatabaseWidget"); config.writeEntry("Connection", ui.cbConnection->currentText()); config.writeEntry("ImportFrom", ui.cbImportFrom->currentIndex()); config.writeEntry("NumberFormat", ui.cbNumberFormat->currentIndex()); config.writeEntry("DateTimeFormat", ui.cbDateTimeFormat->currentText()); config.writeEntry("SplitterMainSizes", ui.splitterMain->sizes()); config.writeEntry("SplitterPreviewSizes", ui.splitterPreview->sizes()); //TODO } /*! * in case the import from a table is selected, returns the currently selected database table. * returns empty string otherwise. */ QString ImportSQLDatabaseWidget::selectedTable() const { if (ui.cbImportFrom->currentIndex() == 0) { if (ui.lwTables->currentItem()) return ui.lwTables->currentItem()->text(); } return QString(); } /*! returns \c true if a working connections was selected and a table (or custom query) is provided and ready to be imported. returns \c false otherwise. */ bool ImportSQLDatabaseWidget::isValid() const { return m_valid; } /*! returns \c true if the selected table or the result of a custom query contains numeric data only. returns \c false otherwise. */ bool ImportSQLDatabaseWidget::isNumericData() const { return m_numeric; } /*! loads all available saved connections */ void ImportSQLDatabaseWidget::readConnections() { DEBUG("ImportSQLDatabaseWidget: reading available connections"); KConfig config(m_configPath, KConfig::SimpleConfig); for (const auto& name : config.groupList()) ui.cbConnection->addItem(name); } void ImportSQLDatabaseWidget::connectionChanged() { if (m_initializing) return; QDEBUG("ImportSQLDatabaseWidget: connecting to " + ui.cbConnection->currentText()); //clear the previously shown content ui.teQuery->clear(); ui.lwTables->clear(); ui.twPreview->clear(); + ui.twPreview->setColumnCount(0); + ui.twPreview->setRowCount(0); if (ui.cbConnection->currentIndex() == -1) return; //connection name was changed, determine the current connections settings KConfig config(m_configPath, KConfig::SimpleConfig); KConfigGroup group = config.group(ui.cbConnection->currentText()); //close and remove the previos connection, if available if (m_db.isOpen()) { m_db.close(); QSqlDatabase::removeDatabase(m_db.driverName()); } //open the selected connection const QString& driver = group.readEntry("Driver"); m_db = QSqlDatabase::addDatabase(driver); const QString& dbName = group.readEntry("DatabaseName"); if (DatabaseManagerWidget::isFileDB(driver)) { if (!QFile::exists(dbName)) { KMessageBox::error(this, i18n("Couldn't find the database file '%1'. Please check the connection settings.", dbName), i18n("Connection Failed")); setInvalid(); return; } else m_db.setDatabaseName(dbName); } else if (DatabaseManagerWidget::isODBC(driver)) { if (group.readEntry("CustomConnectionEnabled", false)) m_db.setDatabaseName(group.readEntry("CustomConnectionString")); else m_db.setDatabaseName(dbName); } else { m_db.setDatabaseName(dbName); m_db.setHostName( group.readEntry("HostName") ); m_db.setPort( group.readEntry("Port", 0) ); m_db.setUserName( group.readEntry("UserName") ); m_db.setPassword( group.readEntry("Password") ); } WAIT_CURSOR; if (!m_db.open()) { RESET_CURSOR; KMessageBox::error(this, i18n("Failed to connect to the database '%1'. Please check the connection settings.", ui.cbConnection->currentText()) + QLatin1String("\n\n") + m_db.lastError().databaseText(), i18n("Connection Failed")); setInvalid(); return; } //show all available database tables if (m_db.tables().size()) { ui.lwTables->addItems(m_db.tables()); ui.lwTables->setCurrentRow(0); for (int i = 0; i < ui.lwTables->count(); ++i) ui.lwTables->item(i)->setIcon(QIcon::fromTheme("view-form-table")); } else setInvalid(); RESET_CURSOR; } void ImportSQLDatabaseWidget::refreshPreview() { if (!ui.lwTables->currentItem()) { setInvalid(); return; } WAIT_CURSOR; ui.twPreview->clear(); //execute the current query (select on a table or a custom query) const QString& query = currentQuery(true); if (query.isEmpty()) { RESET_CURSOR; setInvalid(); return; } QSqlQuery q; q.prepare(currentQuery(true)); q.setForwardOnly(true); q.exec(); - if (!q.isActive()) { + if (!q.isActive() || !q.next()) { // check if query was succesful and got to first record RESET_CURSOR; if (!q.lastError().databaseText().isEmpty()) KMessageBox::error(this, q.lastError().databaseText(), i18n("Unable to Execute Query")); setInvalid(); return; } //resize the table to the number of columns (=number of fields in the result set) m_cols = q.record().count(); ui.twPreview->setColumnCount(m_cols); //determine the names and the data type (column modes) of the table columns. //check whether we have numerical data only by checking the data types of the first record. m_columnNames.clear(); m_columnModes.clear(); bool numeric = true; const auto numberFormat = (QLocale::Language)ui.cbNumberFormat->currentIndex(); const QString& dateTimeFormat = ui.cbDateTimeFormat->currentText(); - q.next(); //go to the first record // ui.twPreview->setRowCount(1); //add the first row for the check boxes for (int i = 0; i < m_cols; ++i) { //name m_columnNames << q.record().fieldName(i); //value and type const QString valueString = q.record().value(i).toString(); AbstractColumn::ColumnMode mode = AbstractFileFilter::columnMode(valueString, dateTimeFormat, numberFormat); m_columnModes << mode; if (mode != AbstractColumn::Numeric) numeric = false; //header item QTableWidgetItem* item = new QTableWidgetItem(m_columnNames[i] + QLatin1String(" {") + ENUM_TO_STRING(AbstractColumn, ColumnMode, mode) + QLatin1String("}")); item->setTextAlignment(Qt::AlignLeft); item->setIcon(AbstractColumn::iconForMode(mode)); ui.twPreview->setHorizontalHeaderItem(i, item); //create checked items // QTableWidgetItem* itemChecked = new QTableWidgetItem(); // itemChecked->setCheckState(Qt::Checked); // ui.twPreview->setItem(0, i, itemChecked); } //preview the data const bool customQuery = (ui.cbImportFrom->currentIndex() != 0); int row = 0; do { for (int col = 0; col < m_cols; ++col) { ui.twPreview->setRowCount(row+1); ui.twPreview->setItem(row, col, new QTableWidgetItem(q.value(col).toString()) ); } row++; //in case a custom query is executed, check whether the row number limit is reached if (customQuery && row >= ui.sbPreviewLines->value()) break; } while (q.next()); ui.twPreview->horizontalHeader()->resizeSections(QHeaderView::ResizeToContents); setValid(); if (numeric != m_numeric) { m_numeric = numeric; emit stateChanged(); } RESET_CURSOR; } void ImportSQLDatabaseWidget::importFromChanged(int index) { if (index == 0) { //import from a table ui.gbQuery->hide(); ui.lwTables->show(); } else { //import the result set of a custom query ui.gbQuery->show(); ui.lwTables->hide(); ui.twPreview->clear(); } refreshPreview(); } void ImportSQLDatabaseWidget::read(AbstractDataSource* dataSource, AbstractFileFilter::ImportMode importMode) { if (!dataSource) return; WAIT_CURSOR; //execute the current query (select on a table or a custom query) QSqlQuery q; // q.setForwardOnly(true); //TODO: crashes most probably because of q.last() and q.first() below q.prepare(currentQuery()); if (!q.exec() || !q.isActive()) { RESET_CURSOR; if (!q.lastError().databaseText().isEmpty()) KMessageBox::error(this, q.lastError().databaseText(), i18n("Unable to Execute Query")); setInvalid(); return; } //determine the number of rows/records to read q.last(); const int rows = q.at()+1; q.first(); // pointers to the actual data containers //columnOffset indexes the "start column" in the datasource. Data will be imported starting from this column. QVector dataContainer; int columnOffset = dataSource->prepareImport(dataContainer, importMode, rows, m_cols, m_columnNames, m_columnModes); //number and DateTime formatting const QString& dateTimeFormat = ui.cbDateTimeFormat->currentText(); const QLocale numberFormat = QLocale((QLocale::Language)ui.cbNumberFormat->currentIndex()); //read the data int row = 0; do { for (int col = 0; col < m_cols; ++col) { const QString valueString = q.record().value(col).toString(); // set value depending on data type switch (m_columnModes[col]) { case AbstractColumn::Numeric: { bool isNumber; const double value = numberFormat.toDouble(valueString, &isNumber); static_cast*>(dataContainer[col])->operator[](row) = (isNumber ? value : NAN); break; } case AbstractColumn::Integer: { bool isNumber; const int value = numberFormat.toInt(valueString, &isNumber); static_cast*>(dataContainer[col])->operator[](row) = (isNumber ? value : NAN); break; } case AbstractColumn::DateTime: { const QDateTime valueDateTime = QDateTime::fromString(valueString, dateTimeFormat); static_cast*>(dataContainer[col])->operator[](row) = valueDateTime.isValid() ? valueDateTime : QDateTime(); break; } case AbstractColumn::Text: static_cast*>(dataContainer[col])->operator[](row) = valueString; break; case AbstractColumn::Month: // never happens case AbstractColumn::Day: break; } } row++; emit completed(100 * row/rows); } while (q.next()); DEBUG(" Read " << row << " rows"); dataSource->finalizeImport(columnOffset, 1, m_cols, row, dateTimeFormat, importMode); RESET_CURSOR; } QString ImportSQLDatabaseWidget::currentQuery(bool preview) { QString query; const bool customQuery = (ui.cbImportFrom->currentIndex() != 0); if ( !customQuery ) { const QString& tableName = ui.lwTables->currentItem()->text(); if (!preview) { query = QLatin1String("SELECT * FROM ") + tableName; } else { //preview the content of the currently selected table const QString& driver = m_db.driverName(); const QString& limit = QString::number(ui.sbPreviewLines->value()); if ( (driver == QLatin1String("QSQLITE3")) || (driver == QLatin1String("QSQLITE")) || (driver == QLatin1String("QMYSQL3")) || (driver == QLatin1String("QMYSQL")) || (driver == QLatin1String("QPSQL")) ) query = QLatin1String("SELECT * FROM ") + tableName + QLatin1String(" LIMIT ") + limit; else if (driver == QLatin1String("QOCI")) query = QLatin1String("SELECT * FROM ") + tableName + QLatin1String(" ROWNUM<=") + limit; else if (driver == QLatin1String("QDB2")) query = QLatin1String("SELECT * FROM ") + tableName + QLatin1String(" FETCH FIRST ") + limit + QLatin1String(" ROWS ONLY"); else if (driver == QLatin1String("QIBASE")) query = QLatin1String("SELECT * FROM ") + tableName + QLatin1String(" ROWS ") + limit; else //for ODBC the DBMS is not known and it's not clear what syntax to use -> select all rows query = QLatin1String("SELECT * FROM ") + tableName; } } else { //preview the result of a custom query query = ui.teQuery->toPlainText().simplified(); } return query; } /*! shows the database manager where the connections are created and edited. The selected connection is selected in the connection combo box in this widget. **/ void ImportSQLDatabaseWidget::showDatabaseManager() { DatabaseManagerDialog* dlg = new DatabaseManagerDialog(this, ui.cbConnection->currentText()); if (dlg->exec() == QDialog::Accepted) { //re-read the available connections to be in sync with the changes in DatabaseManager m_initializing = true; ui.cbConnection->clear(); readConnections(); - m_initializing = false; //select the connection the user has selected in DatabaseManager const QString& conn = dlg->connection(); ui.cbConnection->setCurrentIndex(ui.cbConnection->findText(conn)); + m_initializing = false; + + connectionChanged(); } delete dlg; } void ImportSQLDatabaseWidget::setInvalid() { if (m_valid) { + ui.twPreview->setColumnCount(0); + ui.twPreview->setRowCount(0); + m_valid = false; emit stateChanged(); } } void ImportSQLDatabaseWidget::setValid() { if (!m_valid) { m_valid = true; emit stateChanged(); } }