diff --git a/src/kdefrontend/datasources/DatabaseManagerWidget.cpp b/src/kdefrontend/datasources/DatabaseManagerWidget.cpp index fc6811289..5b2aff062 100644 --- a/src/kdefrontend/datasources/DatabaseManagerWidget.cpp +++ b/src/kdefrontend/datasources/DatabaseManagerWidget.cpp @@ -1,496 +1,500 @@ /*************************************************************************** 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_initializing(false), 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, SIGNAL(currentRowChanged(int)), this, SLOT(connectionChanged(int)) ); connect( ui.tbAdd, SIGNAL(clicked()), this, SLOT(addConnection()) ); connect( ui.tbDelete, SIGNAL(clicked()), this, SLOT(deleteConnection()) ); connect( ui.bTestConnection, SIGNAL(clicked()), this, SLOT(testConnection()) ); connect( ui.bOpen, SIGNAL(clicked()), this, SLOT(selectFile()) ); connect( ui.cbDriver, SIGNAL(currentIndexChanged(int)), SLOT(driverChanged()) ); connect( ui.leName, SIGNAL(textChanged(QString)), this, SLOT(nameChanged(QString)) ); connect( ui.leDatabase, SIGNAL(textChanged(QString)), this, SLOT(databaseNameChanged()) ); connect( ui.leHost, SIGNAL(textChanged(QString)), this, SLOT(hostChanged()) ); connect( ui.sbPort, SIGNAL(valueChanged(int)), this, SLOT(portChanged()) ); connect( ui.leUserName, SIGNAL(textChanged(QString)), this, SLOT(userNameChanged()) ); connect( ui.lePassword, SIGNAL(textChanged(QString)), this, SLOT(passwordChanged()) ); QTimer::singleShot( 100, this, SLOT(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) return; //show the settings for the selected connection m_initializing = true; ui.leName->setText(m_connections[index].name); ui.cbDriver->setCurrentIndex(ui.cbDriver->findText(m_connections[index].driver)); ui.leDatabase->setText(m_connections[index].dbName); if (!isFileDB(m_connections[index].driver)) { ui.leHost->setText(m_connections[index].hostName); ui.sbPort->setValue(m_connections[index].port); ui.leUserName->setText(m_connections[index].userName); ui.lePassword->setText(m_connections[index].password); } 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 (!m_initializing) { m_connections[ui.lwConnections->currentRow()].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 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 (driver.startsWith(QLatin1String("QODBC"))) ui.lDatabase->setText(i18n("ODBC datasource:")); else ui.lDatabase->setText(i18n("Database:")); if (m_initializing) return; m_connections[ui.lwConnections->currentRow()].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(); //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(); 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; emit changed(); } void DatabaseManagerWidget::userNameChanged() { if (m_initializing) return; m_connections[ui.lwConnections->currentRow()].userName = ui.leUserName->text(); emit changed(); } void DatabaseManagerWidget::passwordChanged() { if (m_initializing) return; m_connections[ui.lwConnections->currentRow()].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)) 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; //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); 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)) { conn.hostName = group.readEntry("HostName", "localhost"); conn.port = group.readEntry("Port", defaultPort(conn.driver)); conn.userName = group.readEntry("UserName", "root"); conn.password = group.readEntry("Password", ""); } 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)) { group.writeEntry("HostName", conn.hostName); group.writeEntry("Port", conn.port); group.writeEntry("UserName", conn.userName); group.writeEntry("Password", conn.password); } } config.sync(); } void DatabaseManagerWidget::testConnection() { int row = ui.lwConnections->currentRow(); //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), i18n("Connection Failed")); return; } } + WAIT_CURSOR; QSqlDatabase db = QSqlDatabase::addDatabase(m_connections[row].driver); db.setDatabaseName(m_connections[row].dbName); if (!isFileDB(m_connections[row].driver)) { db.setHostName(m_connections[row].hostName); db.setPort(m_connections[row].port); db.setUserName(m_connections[row].userName); db.setPassword(m_connections[row].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), i18n("Connection Successful")); } else { - KMessageBox::error(this, i18n("Failed to connect to the database '%1'.", m_connections[row].dbName), + RESET_CURSOR; + KMessageBox::error(this, i18n("Failed to connect to the database '%1'.", m_connections[row].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, QODBC, QODBC3 return ( driver.startsWith(QLatin1String("QSQLITE")) || 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/ImportSQLDatabaseWidget.cpp b/src/kdefrontend/datasources/ImportSQLDatabaseWidget.cpp index 06da0ee15..a4645d903 100644 --- a/src/kdefrontend/datasources/ImportSQLDatabaseWidget.cpp +++ b/src/kdefrontend/datasources/ImportSQLDatabaseWidget.cpp @@ -1,460 +1,465 @@ /*************************************************************************** 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), m_cols(0), m_rows(0), m_databaseTreeModel(nullptr), m_initializing(false), m_valid(false), m_numeric(false) { 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->currentText()); 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(); 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()); //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 { 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()) { - KMessageBox::error(this, i18n("Failed to connect to the database '%1'. Please check the connection settings.", ui.cbConnection->currentText()), + 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()) { 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("QMYSQL3")) || (driver == QLatin1String("QPSQL")) || (driver == QLatin1String("QSQLITE")) || (driver == QLatin1String("QMYSQL")) ) 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 query = QLatin1String("SELECT TOP ") + limit + QLatin1String(" * 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 ui.cbConnection->clear(); readConnections(); //select the connection the user has selected in DatabaseManager QString conn = dlg->connection(); ui.cbConnection->setCurrentIndex(ui.cbConnection->findText(conn)); } delete dlg; } void ImportSQLDatabaseWidget::setInvalid() { if (m_valid) { m_valid = false; emit stateChanged(); } } void ImportSQLDatabaseWidget::setValid() { if (!m_valid) { m_valid = true; emit stateChanged(); } }