File indexing completed on 2024-05-12 16:35:18
0001 /* This file is part of the KDE project 0002 Copyright (C) 2002-2003 Norbert Andres <nandres@web.de> 0003 (C) 2002 Ariya Hidayat <ariya@kde.org> 0004 (C) 2002 Laurent Montel <montel@kde.org> 0005 0006 This library is free software; you can redistribute it and/or 0007 modify it under the terms of the GNU Library General Public 0008 License as published by the Free Software Foundation; either 0009 version 2 of the License, or (at your option) any later version. 0010 0011 This library is distributed in the hope that it will be useful, 0012 but WITHOUT ANY WARRANTY; without even the implied warranty of 0013 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0014 Library General Public License for more details. 0015 0016 You should have received a copy of the GNU Library General Public License 0017 along with this library; see the file COPYING.LIB. If not, write to 0018 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0019 Boston, MA 02110-1301, USA. 0020 */ 0021 0022 // Local 0023 #include "DatabaseDialog.h" 0024 0025 #ifndef QT_NO_SQL 0026 0027 #include "Cell.h" 0028 #include "ui/Selection.h" 0029 #include "Sheet.h" 0030 #include "Util.h" 0031 #include "SheetsDebug.h" 0032 0033 #include "commands/DataManipulators.h" 0034 0035 #include <KoCanvasBase.h> 0036 0037 #include <kcombobox.h> 0038 #include <klineedit.h> 0039 #include <KLocalizedString> 0040 #include <kmessagebox.h> 0041 #include <ktextedit.h> 0042 0043 #include <QIntValidator> 0044 #include <QCheckBox> 0045 #include <QFrame> 0046 #include <QGridLayout> 0047 #include <QHBoxLayout> 0048 #include <QLabel> 0049 #include <QPushButton> 0050 #include <QRadioButton> 0051 #include <QSqlDatabase> 0052 #include <QSqlError> 0053 #include <QSqlField> 0054 #include <QSqlQuery> 0055 #include <QSqlRecord> 0056 #include <QVariant> 0057 #include <QListWidget> 0058 #include <QTreeWidget> 0059 0060 using namespace Calligra::Sheets; 0061 0062 /******************************************************** 0063 * Database Assistant * 0064 ********************************************************/ 0065 0066 DatabaseDialog::DatabaseDialog(QWidget* parent, Selection* selection) 0067 : KAssistantDialog(parent), 0068 m_currentPage(eDatabase), 0069 m_selection(selection), 0070 m_targetRect(selection->lastRange()) 0071 { 0072 setObjectName(QLatin1String("DatabaseDialog")); 0073 setWindowTitle(i18n("Insert Data From Database")); 0074 0075 // database page 0076 0077 QFrame * databaseFrame = new QFrame(this); 0078 QGridLayout * databaseFrameLayout = new QGridLayout(databaseFrame); 0079 0080 m_Type = new QLabel(databaseFrame); 0081 m_Type->setText(i18n("Type:")); 0082 0083 databaseFrameLayout->addWidget(m_Type, 0, 0); 0084 0085 QLabel * TextLabel4 = new QLabel(databaseFrame); 0086 TextLabel4->setText(i18n("User name:\n" 0087 "(if necessary)")); 0088 databaseFrameLayout->addWidget(TextLabel4, 4, 0); 0089 0090 QLabel * TextLabel2 = new QLabel(databaseFrame); 0091 TextLabel2->setText(i18n("Host:")); 0092 databaseFrameLayout->addWidget(TextLabel2, 2, 0); 0093 0094 m_driver = new KComboBox(databaseFrame); 0095 databaseFrameLayout->addWidget(m_driver, 0, 1); 0096 0097 m_username = new KLineEdit(databaseFrame); 0098 databaseFrameLayout->addWidget(m_username, 4, 1); 0099 0100 m_host = new KLineEdit(databaseFrame); 0101 m_host->setText("localhost"); 0102 databaseFrameLayout->addWidget(m_host, 2, 1); 0103 0104 QLabel * TextLabel3 = new QLabel(databaseFrame); 0105 TextLabel3->setText(i18n("Port:\n(if necessary)")); 0106 databaseFrameLayout->addWidget(TextLabel3, 3, 0); 0107 0108 m_password = new KLineEdit(databaseFrame); 0109 m_password->setEchoMode(KLineEdit::Password); 0110 databaseFrameLayout->addWidget(m_password, 5, 1); 0111 0112 m_port = new KLineEdit(databaseFrame); 0113 m_port->setValidator(new QIntValidator(m_port)); 0114 databaseFrameLayout->addWidget(m_port, 3, 1); 0115 0116 QLabel * dbName = new QLabel(databaseFrame); 0117 dbName->setText(i18n("Database name: ")); 0118 databaseFrameLayout->addWidget(dbName, 1, 0); 0119 0120 m_databaseName = new KLineEdit(databaseFrame); 0121 databaseFrameLayout->addWidget(m_databaseName, 1, 1); 0122 0123 QLabel * TextLabel5 = new QLabel(databaseFrame); 0124 TextLabel5->setText(i18n("Password:\n" 0125 "(if necessary)")); 0126 databaseFrameLayout->addWidget(TextLabel5, 5, 0); 0127 0128 m_databaseStatus = new QLabel(databaseFrame); 0129 m_databaseStatus->setSizePolicy(QSizePolicy::Fixed, QSizePolicy::Preferred); 0130 m_databaseStatus->setMaximumSize(QSize(32767, 30)); 0131 m_databaseStatus->setText(" "); 0132 databaseFrameLayout->addWidget(m_databaseStatus, 6, 0, 1, 2); 0133 0134 databaseFrameLayout->setRowStretch(7, 1); 0135 0136 m_database = new KPageWidgetItem(databaseFrame, i18n("Database")); 0137 addPage(m_database); 0138 0139 // tables page 0140 0141 QFrame * tablesFrame = new QFrame(this); 0142 QGridLayout * tablesFrameLayout = new QGridLayout(tablesFrame); 0143 0144 // QHBoxLayout * Layout21 = new QHBoxLayout(); 0145 // Layout21->setMargin(0); 0146 // Layout21->setSpacing(6); 0147 0148 // QLabel * TextLabel12_2 = new QLabel( tablesFrame ); 0149 // TextLabel12_2->setText( i18n( "Database:" ) ); 0150 // Layout21->addWidget( TextLabel12_2 ); 0151 0152 // m_databaseList = new KComboBox( tablesFrame ); 0153 // Layout21->addWidget( m_databaseList ); 0154 0155 // m_connectButton = new QPushButton( tablesFrame, "m_connectButton" ); 0156 // m_connectButton->setText( i18n( "&Connect" ) ); 0157 // Layout21->addWidget( m_connectButton ); 0158 0159 // tablesFrameLayout->addLayout( Layout21, 0, 0 ); 0160 0161 m_tableStatus = new QLabel(tablesFrame); 0162 m_tableStatus->setText(" "); 0163 tablesFrameLayout->addWidget(m_tableStatus, 3, 0); 0164 0165 m_SelectTableLabel = new QLabel(tablesFrame); 0166 m_SelectTableLabel->setText(i18n("Select tables:")); 0167 tablesFrameLayout->addWidget(m_SelectTableLabel, 1, 0); 0168 0169 m_tableView = new QListWidget(tablesFrame); 0170 tablesFrameLayout->addWidget(m_tableView, 2, 0); 0171 0172 tablesFrameLayout->setRowStretch(4, 1); 0173 0174 m_table = new KPageWidgetItem(tablesFrame, i18n("Tables")); 0175 addPage(m_table); 0176 0177 // columns page 0178 0179 QFrame * columnsFrame = new QFrame(this); 0180 QGridLayout * columnsFrameLayout = new QGridLayout(columnsFrame); 0181 0182 QLabel * TextLabel11_2 = new QLabel(columnsFrame); 0183 TextLabel11_2->setText(i18n("Select columns:")); 0184 columnsFrameLayout->addWidget(TextLabel11_2, 0, 0); 0185 0186 m_columnView = new QTreeWidget(columnsFrame); 0187 m_columnView->setColumnCount(3); 0188 m_columnView->setHeaderLabels(QStringList() << i18n("Column") << i18n("Table") << "Data Type"); 0189 0190 columnsFrameLayout->addWidget(m_columnView, 1, 0); 0191 0192 m_columnsStatus = new QLabel(columnsFrame); 0193 m_columnsStatus->setText(" "); 0194 columnsFrameLayout->addWidget(m_columnsStatus, 2, 0); 0195 0196 columnsFrameLayout->setRowStretch(3, 1); 0197 0198 m_columns = new KPageWidgetItem(columnsFrame, i18n("Columns")); 0199 addPage(m_columns); 0200 0201 // options page 0202 0203 QFrame * optionsFrame = new QFrame(this); 0204 QGridLayout * optionsFrameLayout = new QGridLayout(optionsFrame); 0205 0206 m_andBox = new QRadioButton(optionsFrame); 0207 m_andBox->setText(i18n("Match all of the following (AND)")); 0208 m_andBox->setChecked(true); 0209 optionsFrameLayout->addWidget(m_andBox, 0, 0, 1, 3); 0210 0211 m_orBox = new QRadioButton(optionsFrame); 0212 m_orBox->setText(i18n("Match any of the following (OR)")); 0213 optionsFrameLayout->addWidget(m_orBox, 1, 0, 1, 3); 0214 0215 m_columns_1 = new KComboBox(optionsFrame); 0216 optionsFrameLayout->addWidget(m_columns_1, 2, 0); 0217 0218 m_operator_1 = new KComboBox(optionsFrame); 0219 m_operator_1->insertItem(0, i18n("equals")); 0220 m_operator_1->insertItem(1, i18n("not equal")); 0221 m_operator_1->insertItem(2, i18n("in")); 0222 m_operator_1->insertItem(3, i18n("not in")); 0223 m_operator_1->insertItem(4, i18n("like")); 0224 m_operator_1->insertItem(5, i18n("greater")); 0225 m_operator_1->insertItem(6, i18n("lesser")); 0226 m_operator_1->insertItem(7, i18n("greater or equal")); 0227 m_operator_1->insertItem(8, i18n("less or equal")); 0228 optionsFrameLayout->addWidget(m_operator_1, 2, 1); 0229 0230 m_operatorValue_1 = new KLineEdit(optionsFrame); 0231 optionsFrameLayout->addWidget(m_operatorValue_1, 2, 2); 0232 0233 m_columns_2 = new KComboBox(optionsFrame); 0234 m_columns_2->setEditable(false); 0235 optionsFrameLayout->addWidget(m_columns_2, 3, 0); 0236 0237 m_operator_2 = new KComboBox(optionsFrame); 0238 m_operator_2->insertItem(0, i18n("equals")); 0239 m_operator_2->insertItem(1, i18n("not equal")); 0240 m_operator_2->insertItem(2, i18n("in")); 0241 m_operator_2->insertItem(3, i18n("not in")); 0242 m_operator_2->insertItem(4, i18n("like")); 0243 m_operator_2->insertItem(5, i18n("greater")); 0244 m_operator_2->insertItem(6, i18n("lesser")); 0245 optionsFrameLayout->addWidget(m_operator_2, 3, 1); 0246 0247 m_operatorValue_2 = new KLineEdit(optionsFrame); 0248 optionsFrameLayout->addWidget(m_operatorValue_2, 3, 2); 0249 0250 m_columns_3 = new KComboBox(optionsFrame); 0251 optionsFrameLayout->addWidget(m_columns_3, 4, 0); 0252 0253 m_operator_3 = new KComboBox(optionsFrame); 0254 m_operator_3->insertItem(0, i18n("equals")); 0255 m_operator_3->insertItem(1, i18n("not equal")); 0256 m_operator_3->insertItem(2, i18n("in")); 0257 m_operator_3->insertItem(3, i18n("not in")); 0258 m_operator_3->insertItem(4, i18n("like")); 0259 m_operator_3->insertItem(5, i18n("greater")); 0260 m_operator_3->insertItem(6, i18n("lesser")); 0261 optionsFrameLayout->addWidget(m_operator_3, 4, 1); 0262 0263 m_operatorValue_3 = new KLineEdit(optionsFrame); 0264 optionsFrameLayout->addWidget(m_operatorValue_3, 4, 2); 0265 0266 QLabel * TextLabel19 = new QLabel(optionsFrame); 0267 TextLabel19->setText(i18n("Sorted by")); 0268 optionsFrameLayout->addWidget(TextLabel19, 5, 0); 0269 0270 m_columnsSort_1 = new KComboBox(optionsFrame); 0271 optionsFrameLayout->addWidget(m_columnsSort_1, 5, 1); 0272 0273 m_sortMode_1 = new KComboBox(optionsFrame); 0274 m_sortMode_1->insertItem(0, i18n("Ascending")); 0275 m_sortMode_1->insertItem(1, i18n("Descending")); 0276 optionsFrameLayout->addWidget(m_sortMode_1, 5, 2); 0277 0278 QLabel * TextLabel19_2 = new QLabel(optionsFrame); 0279 TextLabel19_2->setText(i18n("Sorted by")); 0280 optionsFrameLayout->addWidget(TextLabel19_2, 6, 0); 0281 0282 m_columnsSort_2 = new KComboBox(optionsFrame); 0283 optionsFrameLayout->addWidget(m_columnsSort_2, 6, 1); 0284 0285 m_sortMode_2 = new KComboBox(optionsFrame); 0286 m_sortMode_2->insertItem(0, i18n("Ascending")); 0287 m_sortMode_2->insertItem(1, i18n("Descending")); 0288 optionsFrameLayout->addWidget(m_sortMode_2, 6, 2); 0289 0290 QSpacerItem* spacer = new QSpacerItem(20, 20, QSizePolicy::Expanding, QSizePolicy::Minimum); 0291 optionsFrameLayout->addItem(spacer, 7, 1); 0292 QSpacerItem* spacer_2 = new QSpacerItem(20, 20, QSizePolicy::Expanding, QSizePolicy::Minimum); 0293 optionsFrameLayout->addItem(spacer_2, 7, 0); 0294 0295 m_distinct = new QCheckBox(optionsFrame); 0296 m_distinct->setText(i18n("Distinct")); 0297 optionsFrameLayout->addWidget(m_distinct, 7, 2); 0298 0299 optionsFrameLayout->setRowStretch(8, 1); 0300 0301 m_options = new KPageWidgetItem(optionsFrame, i18n("Query Options")); 0302 addPage(m_options); 0303 0304 // result page 0305 0306 QFrame * resultFrame = new QFrame(this); 0307 QGridLayout * resultFrameLayout = new QGridLayout(resultFrame); 0308 0309 QLabel * TextLabel17 = new QLabel(resultFrame); 0310 TextLabel17->setText(i18n("SQL query:")); 0311 resultFrameLayout->addWidget(TextLabel17, 0, 0); 0312 0313 m_sqlQuery = new KTextEdit(resultFrame); 0314 resultFrameLayout->addWidget(m_sqlQuery, 1, 0); 0315 0316 QFrame * Frame12 = new QFrame(resultFrame); 0317 Frame12->setFrameShape(QFrame::StyledPanel); 0318 Frame12->setFrameShadow(QFrame::Raised); 0319 QGridLayout * Frame12Layout = new QGridLayout(Frame12); 0320 0321 m_startingRegion = new QRadioButton(Frame12); 0322 m_startingRegion->setText(i18n("Insert in region")); 0323 Frame12Layout->addWidget(m_startingRegion, 0, 0); 0324 0325 m_cell = new KLineEdit(Frame12); 0326 Frame12Layout->addWidget(m_cell, 1, 1); 0327 0328 m_region = new KLineEdit(Frame12); 0329 Frame12Layout->addWidget(m_region, 0, 1); 0330 0331 m_startingCell = new QRadioButton(Frame12); 0332 m_startingCell->setText(i18n("Starting in cell")); 0333 m_startingCell->setChecked(true); 0334 Frame12Layout->addWidget(m_startingCell, 1, 0); 0335 0336 resultFrameLayout->addWidget(Frame12, 2, 0); 0337 0338 resultFrameLayout->setRowStretch(3, 1); 0339 0340 m_result = new KPageWidgetItem(resultFrame, i18n("Result")); 0341 addPage(m_result); 0342 0343 // signals and slots connections 0344 connect(m_orBox, SIGNAL(clicked()), this, SLOT(orBox_clicked())); 0345 connect(m_andBox, SIGNAL(clicked()), this, SLOT(andBox_clicked())); 0346 connect(m_startingCell, SIGNAL(clicked()), this, SLOT(startingCell_clicked())); 0347 connect(m_startingRegion, SIGNAL(clicked()), this, SLOT(startingRegion_clicked())); 0348 connect(m_driver, SIGNAL(activated(int)), this, SLOT(databaseDriverChanged(int))); 0349 connect(m_host, SIGNAL(textChanged(QString)), this, SLOT(databaseHostChanged(QString))); 0350 connect(m_databaseName, SIGNAL(textChanged(QString)), this, SLOT(databaseNameChanged(QString))); 0351 /*connect( m_tableView, SIGNAL(contextMenuRequested(Q3ListViewItem*,QPoint,int)), 0352 this, SLOT(popupTableViewMenu(Q3ListViewItem*,QPoint,int)) );*/ 0353 connect(m_tableView, SIGNAL(itemClicked(QListWidgetItem*)), this, SLOT(tableViewClicked(QListWidgetItem*))); 0354 0355 QStringList str = QSqlDatabase::drivers(); 0356 m_driver->insertItems(0, QSqlDatabase::drivers()); 0357 0358 setValid(m_database, false); 0359 setValid(m_table, false); 0360 setValid(m_columns, false); 0361 setValid(m_options, false); 0362 setValid(m_result, false); 0363 0364 databaseDriverChanged(0); 0365 } 0366 0367 DatabaseDialog::~DatabaseDialog() 0368 { 0369 // no need to delete child widgets, Qt does it all for us 0370 if (m_dbConnection.isValid()) 0371 m_dbConnection.close(); 0372 } 0373 0374 void DatabaseDialog::switchPage(int id) 0375 { 0376 if (id > eResult) 0377 --m_currentPage; 0378 if (id < eDatabase) 0379 ++m_currentPage; 0380 0381 switch (id) { 0382 case eDatabase: 0383 setCurrentPage(m_database); 0384 break; 0385 0386 case eTables: 0387 setCurrentPage(m_table); 0388 break; 0389 0390 case eColumns: 0391 setCurrentPage(m_columns); 0392 break; 0393 0394 case eOptions: 0395 setCurrentPage(m_options); 0396 break; 0397 0398 case eResult: 0399 setCurrentPage(m_result); 0400 break; 0401 0402 default: 0403 break; 0404 } 0405 } 0406 0407 void DatabaseDialog::next() 0408 { 0409 switch (m_currentPage) { 0410 case eDatabase: 0411 if (!databaseDoNext()) 0412 return; 0413 break; 0414 0415 case eTables: 0416 if (!tablesDoNext()) 0417 return; 0418 break; 0419 0420 case eColumns: 0421 if (!columnsDoNext()) 0422 return; 0423 break; 0424 0425 case eOptions: 0426 if (!optionsDoNext()) 0427 return; 0428 break; 0429 0430 case eResult: 0431 // there is nothing to do here 0432 break; 0433 0434 default: 0435 break; 0436 } 0437 0438 ++m_currentPage; 0439 0440 switchPage(m_currentPage); 0441 } 0442 0443 void DatabaseDialog::back() 0444 { 0445 --m_currentPage; 0446 0447 switchPage(m_currentPage); 0448 } 0449 0450 void DatabaseDialog::accept() 0451 { 0452 Sheet * sheet = m_selection->activeSheet(); 0453 int top; 0454 int left; 0455 int width = -1; 0456 int height = -1; 0457 if (m_startingRegion->isChecked()) { 0458 Region region(m_region->text(), sheet->map()); 0459 if (region.isValid() && region.firstSheet() != sheet) { 0460 KMessageBox::error(this, i18n("You cannot specify a table here.")); 0461 m_region->setFocus(); 0462 m_region->selectAll(); 0463 return; 0464 } 0465 0466 if (!region.isValid()) { 0467 KMessageBox::error(this, i18n("You have to specify a valid region.")); 0468 m_region->setFocus(); 0469 m_region->selectAll(); 0470 return; 0471 } 0472 0473 top = region.firstRange().top(); 0474 left = region.firstRange().left(); 0475 width = region.firstRange().width(); 0476 height = region.firstRange().height(); 0477 } else { 0478 const Region region(m_cell->text(), sheet->map(), sheet); 0479 if (region.isValid() && region.firstSheet() != sheet) { 0480 KMessageBox::error(this, i18n("You cannot specify a table here.")); 0481 m_cell->setFocus(); 0482 m_cell->selectAll(); 0483 return; 0484 } 0485 // if ( point.pos.x() < 1 || point.pos.y() < 1 ) 0486 if (!region.isValid()) { 0487 KMessageBox::error(this, i18n("You have to specify a valid cell.")); 0488 m_cell->setFocus(); 0489 m_cell->selectAll(); 0490 return; 0491 } 0492 top = region.firstRange().topLeft().y(); 0493 left = region.firstRange().topLeft().x(); 0494 } 0495 0496 int i; 0497 QString queryStr; 0498 QString tmp = m_sqlQuery->toPlainText(); 0499 for (i = 0; i < (int) tmp.length(); ++i) { 0500 if (tmp[i] != '\n') 0501 queryStr += tmp[i]; 0502 else 0503 queryStr += ' '; 0504 } 0505 0506 Cell cell; 0507 QSqlQuery query(m_dbConnection); 0508 0509 // Check the whole query for SQL that might modify database. 0510 // If there is an update command, then it must be at the start of the string, 0511 // or after an open bracket (e.g. nested update) or a space to be valid SQL. 0512 // An update command must also be followed by a space, or it would be parsed 0513 // as an identifier. 0514 // For sanity, also check that there is a SELECT 0515 QRegExp couldModifyDB("(^|[( \\s])(UPDATE|DELETE|INSERT|CREATE) ", Qt::CaseInsensitive); 0516 QRegExp couldQueryDB("(^|[( \\s])(SELECT) ", Qt::CaseInsensitive); 0517 0518 if (couldModifyDB.indexIn(queryStr) != -1 || couldQueryDB.indexIn(queryStr) == -1) { 0519 KMessageBox::error(this, i18n("You are not allowed to change data in the database.")); 0520 m_sqlQuery->setFocus(); 0521 return; 0522 } 0523 0524 if (!query.exec(queryStr)) { 0525 KMessageBox::error(this, i18n("Executing query failed.")); 0526 m_sqlQuery->setFocus(); 0527 return; 0528 } 0529 0530 if (query.size() == 0) { 0531 KMessageBox::error(this, i18n("You did not get any results with this query.")); 0532 m_sqlQuery->setFocus(); 0533 return; 0534 } 0535 0536 int y = 0; 0537 int count = m_columns_1->count(); 0538 if (width != -1) { 0539 if (count > width) 0540 count = width; 0541 } 0542 0543 if (height == -1) { 0544 height = 0; 0545 if (query.first()) { 0546 if (query.isValid()) 0547 ++height; 0548 } 0549 while (query.next()) { 0550 if (query.isValid()) 0551 ++height; 0552 } 0553 } 0554 0555 KUndo2Command* macroCommand = new KUndo2Command(kundo2_i18n("Insert Data From Database")); 0556 0557 if (query.first()) { 0558 if (query.isValid()) { 0559 for (i = 0; i < count; ++i) { 0560 DataManipulator* command = new DataManipulator(macroCommand); 0561 command->setParsing(true); 0562 command->setSheet(sheet); 0563 command->setValue(Value(query.value(i).toString())); 0564 command->add(Region(left + i, top + y, sheet)); 0565 } 0566 ++y; 0567 } 0568 } 0569 0570 if (y != height) { 0571 while (query.next()) { 0572 if (!query.isValid()) 0573 continue; 0574 0575 for (i = 0; i < count; ++i) { 0576 DataManipulator* command = new DataManipulator(macroCommand); 0577 command->setParsing(true); 0578 command->setSheet(sheet); 0579 command->setValue(Value(query.value(i).toString())); 0580 command->add(Region(left + i, top + y, sheet)); 0581 } 0582 ++y; 0583 0584 if (y == height) 0585 break; 0586 } 0587 } 0588 m_selection->canvas()->addCommand(macroCommand); 0589 0590 m_selection->emitModified(); 0591 KAssistantDialog::accept(); 0592 } 0593 0594 bool DatabaseDialog::databaseDoNext() 0595 { 0596 m_dbConnection = QSqlDatabase::addDatabase(m_driver->currentText()); 0597 0598 if (m_dbConnection.isValid()) { 0599 m_dbConnection.setDatabaseName(m_databaseName->text()); 0600 m_dbConnection.setHostName(m_host->text()); 0601 0602 if (!m_username->text().isEmpty()) 0603 m_dbConnection.setUserName(m_username->text()); 0604 0605 if (!m_password->text().isEmpty()) 0606 m_dbConnection.setPassword(m_password->text()); 0607 0608 if (!m_port->text().isEmpty()) { 0609 bool ok = false; 0610 int port = m_port->text().toInt(&ok); 0611 if (!ok) { 0612 KMessageBox::error(this, i18n("The port must be a number")); 0613 return false; 0614 } 0615 m_dbConnection.setPort(port); 0616 } 0617 0618 m_databaseStatus->setText(i18n("Connecting to database...")); 0619 if (m_dbConnection.open()) { 0620 m_databaseStatus->setText(i18n("Connected. Retrieving table information...")); 0621 QStringList tableList(m_dbConnection.tables()); 0622 0623 if (tableList.isEmpty()) { 0624 KMessageBox::error(this, i18n("This database contains no tables")); 0625 m_databaseStatus->setText(" "); 0626 return false; 0627 } 0628 0629 m_tableView->clear(); 0630 0631 for (int i = 0; i < tableList.size(); ++i) { 0632 QListWidgetItem * item = new QListWidgetItem(tableList[i]); 0633 item->setFlags(item->flags() | Qt::ItemIsUserCheckable); 0634 item->setCheckState(Qt::Unchecked); 0635 m_tableView->addItem(item); 0636 } 0637 0638 m_tableView->setEnabled(true); 0639 m_databaseStatus->setText(" "); 0640 } else { 0641 QSqlError error = m_dbConnection.lastError(); 0642 QString errorMsg; 0643 QString err1 = error.driverText(); 0644 QString err2 = error.databaseText(); 0645 if (!err1.isEmpty()) { 0646 errorMsg.append(error.driverText()); 0647 errorMsg.append('\n'); 0648 } 0649 if (!err2.isEmpty() && err1 != err2) { 0650 errorMsg.append(error.databaseText()); 0651 errorMsg.append('\n'); 0652 } 0653 0654 KMessageBox::error(this, errorMsg); 0655 m_databaseStatus->setText(" "); 0656 return false; 0657 } 0658 } else { 0659 KMessageBox::error(this, i18n("Driver could not be loaded")); 0660 m_databaseStatus->setText(" "); 0661 return false; 0662 } 0663 setValid(m_table, true); 0664 0665 return true; 0666 } 0667 0668 bool DatabaseDialog::tablesDoNext() 0669 { 0670 m_databaseStatus->setText(i18n("Retrieving meta data of tables...")); 0671 QStringList tables; 0672 0673 { 0674 for (int i = 0; i < m_tableView->count(); ++i) { 0675 QListWidgetItem* item = m_tableView->item(i); 0676 if (item->checkState() == Qt::Checked) { 0677 tables.append(item->text()); 0678 } 0679 } 0680 } 0681 0682 if (tables.empty()) { 0683 KMessageBox::error(this, i18n("You have to select at least one table.")); 0684 return false; 0685 } 0686 0687 m_columnView->clear(); 0688 QSqlRecord info; 0689 for (int i = 0; i < (int) tables.size(); ++i) { 0690 info = m_dbConnection.record(tables[i]); 0691 for (int j = 0; j < (int) info.count(); ++j) { 0692 QString name = info.fieldName(j); 0693 QSqlField field = info.field(name); 0694 QTreeWidgetItem * checkItem = new QTreeWidgetItem(QStringList() << name << tables[i] << QVariant::typeToName(field.type())); 0695 0696 checkItem->setFlags(checkItem->flags() | Qt::ItemIsUserCheckable); 0697 checkItem->setCheckState(0, Qt::Unchecked); 0698 m_columnView->addTopLevelItem(checkItem); 0699 } 0700 } 0701 m_columnView->sortItems(1, Qt::AscendingOrder); 0702 0703 setValid(m_columns, true); 0704 0705 return true; 0706 } 0707 0708 bool DatabaseDialog::columnsDoNext() 0709 { 0710 QStringList columns; 0711 for (int row = 0; row < m_columnView->topLevelItemCount(); ++row) { 0712 QTreeWidgetItem* item = m_columnView->topLevelItem(row); 0713 if (item->checkState(0) == Qt::Checked) { 0714 columns.append(item->text(1) + '.' + item->text(0)); 0715 } 0716 } 0717 0718 if (columns.empty()) { 0719 KMessageBox::error(this, i18n("You have to select at least one column.")); 0720 return false; 0721 } 0722 0723 m_columns_1->clear(); 0724 m_columns_2->clear(); 0725 m_columns_3->clear(); 0726 m_columns_1->insertItems(0, columns); 0727 m_columns_2->insertItems(0, columns); 0728 m_columns_3->insertItems(0, columns); 0729 m_columnsSort_1->clear(); 0730 m_columnsSort_2->clear(); 0731 m_columnsSort_1->insertItem(0, i18n("None")); 0732 m_columnsSort_2->insertItem(0, i18n("None")); 0733 m_columnsSort_1->insertItems(1, columns); 0734 m_columnsSort_2->insertItems(2, columns); 0735 0736 setValid(m_options, true); 0737 0738 return true; 0739 } 0740 0741 0742 QString DatabaseDialog::getWhereCondition(QString const & column, 0743 QString const & value, 0744 int op) 0745 { 0746 QString wherePart; 0747 0748 switch (op) { 0749 case 0: 0750 wherePart += column + 0751 " = "; 0752 break; 0753 case 1: 0754 wherePart += "NOT " + 0755 column + 0756 " = "; 0757 break; 0758 case 2: 0759 wherePart += column + 0760 " IN "; 0761 break; 0762 case 3: 0763 wherePart += "NOT " + 0764 column + 0765 " IN "; 0766 break; 0767 case 4: 0768 wherePart += column + 0769 " LIKE "; 0770 break; 0771 case 5: 0772 wherePart += column + 0773 " > "; 0774 break; 0775 case 6: 0776 wherePart += column + 0777 " < "; 0778 break; 0779 case 7: 0780 wherePart += column + 0781 " >= "; 0782 break; 0783 case 8: 0784 wherePart += column + 0785 " <= "; 0786 break; 0787 } 0788 0789 if (op != 2 && op != 3) { 0790 QString val; 0791 bool ok = false; 0792 value.toDouble(&ok); 0793 0794 if (!ok) { 0795 if (value[0] != '\'') 0796 val = '\''; 0797 0798 val += value; 0799 0800 if (value[value.length() - 1] != '\'') 0801 val += '\''; 0802 } else 0803 val = value; 0804 0805 wherePart += val; 0806 } else { // "in" & "not in" 0807 QString val; 0808 if (value[0] != '(') 0809 val = '('; 0810 val += value; 0811 if (value[value.length() - 1] != ')') 0812 val += ')'; 0813 wherePart += val; 0814 } 0815 0816 return wherePart; 0817 } 0818 0819 QString DatabaseDialog::exchangeWildcards(QString const & value) 0820 { 0821 QString str(value); 0822 int p = str.indexOf('*'); 0823 while (p > -1) { 0824 str.replace(p, 1, '%'); 0825 p = str.indexOf('*'); 0826 } 0827 0828 p = str.indexOf('?'); 0829 while (p > -1) { 0830 str.replace(p, 1, '_'); 0831 p = str.indexOf('?'); 0832 } 0833 return str; 0834 } 0835 0836 bool DatabaseDialog::optionsDoNext() 0837 { 0838 if (m_operator_1->currentIndex() == 4) { 0839 if ((m_operatorValue_1->text().indexOf('*') != -1) 0840 || (m_operatorValue_1->text().indexOf('?') != -1)) { 0841 // xgettext: no-c-format 0842 int res = KMessageBox::warningYesNo(this, i18n("'*' or '?' are not valid wildcards in SQL. " 0843 "The proper replacements are '%' or '_'. Do you want to replace them?")); 0844 0845 if (res == KMessageBox::Yes) 0846 m_operatorValue_1->setText(exchangeWildcards(m_operatorValue_1->text())); 0847 } 0848 } 0849 0850 if (m_operator_2->currentIndex() == 4) { 0851 if ((m_operatorValue_2->text().indexOf('*') != -1) 0852 || (m_operatorValue_2->text().indexOf('?') != -1)) { 0853 // xgettext: no-c-format 0854 int res = KMessageBox::warningYesNo(this, i18n("'*' or '?' are not valid wildcards in SQL. " 0855 "The proper replacements are '%' or '_'. Do you want to replace them?")); 0856 0857 if (res == KMessageBox::Yes) 0858 m_operatorValue_2->setText(exchangeWildcards(m_operatorValue_2->text())); 0859 } 0860 } 0861 0862 if (m_operator_3->currentIndex() == 4) { 0863 if ((m_operatorValue_3->text().indexOf('*') != -1) 0864 || (m_operatorValue_3->text().indexOf('?') != -1)) { 0865 // xgettext: no-c-format 0866 int res = KMessageBox::warningYesNo(this, i18n("'*' or '?' are not valid wildcards in SQL. " 0867 "The proper replacements are '%' or '_'. Do you want to replace them?")); 0868 0869 if (res == KMessageBox::Yes) 0870 m_operatorValue_3->setText(exchangeWildcards(m_operatorValue_3->text())); 0871 } 0872 } 0873 0874 QString query("SELECT "); 0875 0876 if (m_distinct->isChecked()) 0877 query += "DISTINCT "; 0878 0879 int i; 0880 int l = m_columns_1->count() - 1; 0881 for (i = 0; i < l; ++i) { 0882 query += m_columns_1->itemText(i) + 0883 ", "; 0884 } 0885 query += m_columns_1->itemText(l) + 0886 0887 "\nFROM "; 0888 0889 bool b = false; 0890 for (int i = 0; i < m_tableView->count(); ++i) { 0891 QListWidgetItem* item = m_tableView->item(i); 0892 if (item->checkState() == Qt::Checked) { 0893 if (b) 0894 query += ", "; 0895 b = true; 0896 query += item->text(); 0897 } 0898 } 0899 0900 if ((!m_operatorValue_1->text().isEmpty()) 0901 || (!m_operatorValue_2->text().isEmpty()) 0902 || (!m_operatorValue_3->text().isEmpty())) 0903 query += "\nWHERE "; 0904 0905 bool added = false; 0906 if (!m_operatorValue_1->text().isEmpty()) { 0907 query += getWhereCondition(m_columns_1->currentText(), 0908 m_operatorValue_1->text(), 0909 m_operator_1->currentIndex()); 0910 added = true; 0911 } 0912 0913 if (!m_operatorValue_2->text().isEmpty()) { 0914 if (added) 0915 query += (m_andBox->isChecked() ? " AND " : " OR "); 0916 0917 query += getWhereCondition(m_columns_2->currentText(), 0918 m_operatorValue_2->text(), 0919 m_operator_2->currentIndex()); 0920 added = true; 0921 } 0922 0923 if (!m_operatorValue_3->text().isEmpty()) { 0924 if (added) 0925 query += (m_andBox->isChecked() ? " AND " : " OR "); 0926 0927 query += getWhereCondition(m_columns_3->currentText(), 0928 m_operatorValue_3->text(), 0929 m_operator_3->currentIndex()); 0930 } 0931 0932 if ((m_columnsSort_1->currentIndex() != 0) 0933 || (m_columnsSort_2->currentIndex() != 0)) { 0934 query += "\nORDER BY "; 0935 bool added = false; 0936 if (m_columnsSort_1->currentIndex() != 0) { 0937 added = true; 0938 query += m_columnsSort_1->currentText(); 0939 if (m_sortMode_1->currentIndex() == 1) 0940 query += " DESC "; 0941 } 0942 0943 if (m_columnsSort_2->currentIndex() != 0) { 0944 if (added) 0945 query += ", "; 0946 0947 query += m_columnsSort_2->currentText(); 0948 if (m_sortMode_2->currentIndex() == 1) 0949 query += " DESC "; 0950 } 0951 } 0952 0953 m_sqlQuery->setText(query); 0954 m_cell->setText(Cell::name(m_targetRect.left(), m_targetRect.top())); 0955 m_region->setText(Region(m_targetRect).name()); 0956 0957 setValid(m_result, true); 0958 0959 return true; 0960 } 0961 0962 void DatabaseDialog::orBox_clicked() 0963 { 0964 m_andBox->setChecked(false); 0965 m_orBox->setChecked(true); 0966 } 0967 0968 void DatabaseDialog::andBox_clicked() 0969 { 0970 m_andBox->setChecked(true); 0971 m_orBox->setChecked(false); 0972 } 0973 0974 void DatabaseDialog::startingCell_clicked() 0975 { 0976 m_startingCell->setChecked(true); 0977 m_startingRegion->setChecked(false); 0978 } 0979 0980 void DatabaseDialog::startingRegion_clicked() 0981 { 0982 m_startingCell->setChecked(false); 0983 m_startingRegion->setChecked(true); 0984 } 0985 0986 void DatabaseDialog::connectButton_clicked() 0987 { 0988 warnSheets << "DatabaseDialog::connectButton_clicked(): Not implemented yet!"; 0989 } 0990 0991 void DatabaseDialog::databaseNameChanged(const QString & s) 0992 { 0993 if (!m_driver->currentText().isEmpty() && !s.isEmpty() 0994 && !m_host->text().isEmpty()) 0995 setValid(m_database, true); 0996 else 0997 setValid(m_database, false); 0998 } 0999 1000 void DatabaseDialog::databaseHostChanged(const QString & s) 1001 { 1002 if (!m_driver->currentText().isEmpty() && !s.isEmpty() 1003 && !m_databaseName->text().isEmpty()) 1004 setValid(m_database, true); 1005 else 1006 setValid(m_database, false); 1007 } 1008 1009 void DatabaseDialog::databaseDriverChanged(int index) 1010 { 1011 Q_UNUSED(index) 1012 if (!m_host->text().isEmpty() 1013 && !m_databaseName->text().isEmpty()) 1014 setValid(m_database, true); 1015 else 1016 setValid(m_database, false); 1017 } 1018 1019 /*void DatabaseDialog::popupTableViewMenu( Q3ListViewItem *, const QPoint &, int ) 1020 { 1021 // TODO: popup menu with "Select All", "Inverse selection", "remove selection" 1022 }*/ 1023 1024 void DatabaseDialog::tableViewClicked(QListWidgetItem *) 1025 { 1026 // if ( item ) 1027 // { 1028 // QCheckListItem * i = (QCheckListItem *) item; 1029 // i->setChecked( !i->isChecked() ); 1030 // } 1031 // debugSheets <<"clicked"; 1032 } 1033 1034 #endif // QT_NO_SQL