00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022 #include "kspread_cell.h"
00023 #include "kspread_dlg_database.h"
00024 #include "kspread_doc.h"
00025 #include "kspread_sheet.h"
00026 #include "kspread_util.h"
00027 #include "kspread_undo.h"
00028 #include "kspread_view.h"
00029
00030 #include <kdebug.h>
00031 #include <klistview.h>
00032 #include <klocale.h>
00033 #include <kmessagebox.h>
00034 #include <knumvalidator.h>
00035 #include <kpushbutton.h>
00036
00037 #include <qcheckbox.h>
00038 #include <qcombobox.h>
00039 #include <qframe.h>
00040 #include <qheader.h>
00041 #include <qlabel.h>
00042 #include <qlayout.h>
00043 #include <qlineedit.h>
00044 #include <qpushbutton.h>
00045 #include <qradiobutton.h>
00046 #include <qsqldatabase.h>
00047 #include <qsqlerror.h>
00048 #include <qsqlfield.h>
00049 #include <qsqlquery.h>
00050 #include <qsqlrecord.h>
00051 #include <qtextedit.h>
00052 #include <qtooltip.h>
00053 #include <qvariant.h>
00054 #include <qwhatsthis.h>
00055 #include <qwidget.h>
00056
00057 using namespace KSpread;
00058
00059 #ifndef QT_NO_SQL
00060
00061
00062
00063
00064
00065 DatabaseDialog::DatabaseDialog( View * parent, QRect const & rect, const char * name, bool modal, WFlags fl )
00066 : KWizard( (QWidget *) parent, name, modal, fl ),
00067 m_currentPage( eDatabase ),
00068 m_pView( parent ),
00069 m_targetRect( rect ),
00070 m_dbConnection( 0L )
00071 {
00072 if ( !name )
00073 setName( "DatabaseDialog" );
00074
00075 setCaption( i18n( "Insert Data From Database" ) );
00076
00077
00078
00079 m_database = new QWidget( this, "m_database" );
00080 m_databaseLayout = new QGridLayout( m_database, 1, 1, -1, -1, "m_databaseLayout");
00081
00082 QFrame * Frame5 = new QFrame( m_database, "Frame5" );
00083 Frame5->setFrameShape( QFrame::MShape );
00084 Frame5->setFrameShadow( QFrame::MShadow );
00085 QVBoxLayout * Frame5Layout = new QVBoxLayout( Frame5, 11, 6, "Frame5Layout");
00086
00087
00088 QFrame * Frame16 = new QFrame( Frame5, "Frame16" );
00089 Frame16->setFrameShape( QFrame::NoFrame );
00090 Frame16->setFrameShadow( QFrame::Plain );
00091 QGridLayout * Frame16Layout = new QGridLayout( Frame16, 1, 1, 11, 7, "Frame16Layout");
00092
00093 m_Type = new QLabel( Frame16, "m_Type" );
00094 m_Type->setText( i18n( "Type:" ) );
00095
00096 Frame16Layout->addWidget( m_Type, 0, 0 );
00097
00098 QLabel * TextLabel4 = new QLabel( Frame16, "TextLabel4" );
00099 TextLabel4->setText( i18n( "User name:\n"
00100 "(if necessary)" ) );
00101 Frame16Layout->addWidget( TextLabel4, 4, 0 );
00102
00103 QLabel * TextLabel2 = new QLabel( Frame16, "TextLabel2" );
00104 TextLabel2->setText( i18n( "Host:" ) );
00105 Frame16Layout->addWidget( TextLabel2, 2, 0 );
00106
00107 m_driver = new QComboBox( FALSE, Frame16, "m_driver" );
00108 Frame16Layout->addWidget( m_driver, 0, 1 );
00109
00110 m_username = new QLineEdit( Frame16, "m_username" );
00111 Frame16Layout->addWidget( m_username, 4, 1 );
00112
00113 m_host = new QLineEdit( Frame16, "m_host" );
00114 m_host->setText("localhost");
00115 Frame16Layout->addWidget( m_host, 2, 1 );
00116
00117 QLabel * TextLabel3 = new QLabel( Frame16, "TextLabel3" );
00118 TextLabel3->setText( i18n( "Port:\n(if necessary)") );
00119 Frame16Layout->addWidget( TextLabel3, 3, 0 );
00120
00121 m_password = new QLineEdit( Frame16, "m_password" );
00122 m_password->setEchoMode( QLineEdit::Password );
00123 Frame16Layout->addWidget( m_password, 5, 1 );
00124
00125 m_port = new QLineEdit( Frame16, "m_port" );
00126 m_port->setValidator( new KIntValidator( m_port ) );
00127 Frame16Layout->addWidget( m_port, 3, 1 );
00128
00129 QLabel * dbName = new QLabel( Frame16, "dbName" );
00130 dbName->setText( i18n( "Database name: ") );
00131 Frame16Layout->addWidget( dbName, 1, 0 );
00132
00133 m_databaseName = new QLineEdit( Frame16, "m_databaseName" );
00134 Frame16Layout->addWidget( m_databaseName, 1, 1 );
00135
00136 QLabel * TextLabel5 = new QLabel( Frame16, "TextLabel5" );
00137 TextLabel5->setText( i18n( "Password:\n"
00138 "(if necessary)" ) );
00139 Frame16Layout->addWidget( TextLabel5, 5, 0 );
00140 Frame5Layout->addWidget( Frame16 );
00141
00142 m_databaseStatus = new QLabel( Frame5, "m_databaseStatus" );
00143 m_databaseStatus->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)5, 0, 0, m_databaseStatus->sizePolicy().hasHeightForWidth() ) );
00144 m_databaseStatus->setMaximumSize( QSize( 32767, 30 ) );
00145 m_databaseStatus->setText( " " );
00146 Frame5Layout->addWidget( m_databaseStatus );
00147
00148 m_databaseLayout->addWidget( Frame5, 0, 1 );
00149
00150 QFrame * Frame17 = new QFrame( m_database, "Frame17" );
00151 Frame17->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17->sizePolicy().hasHeightForWidth() ) );
00152 Frame17->setMinimumSize( QSize( 111, 0 ) );
00153 Frame17->setFrameShape( QFrame::NoFrame );
00154 Frame17->setFrameShadow( QFrame::Plain );
00155
00156 m_databaseLayout->addWidget( Frame17, 0, 0 );
00157 addPage( m_database, i18n( "Database" ) );
00158
00159
00160
00161 m_sheet = new QWidget( this, "m_table" );
00162 m_sheetLayout = new QGridLayout( m_sheet, 1, 1, 11, 6, "m_tableLayout");
00163
00164 QFrame * Frame5_2 = new QFrame( m_sheet, "Frame5_2" );
00165 Frame5_2->setFrameShape( QFrame::MShape );
00166 Frame5_2->setFrameShadow( QFrame::MShadow );
00167 QGridLayout * Frame5_2Layout = new QGridLayout( Frame5_2, 1, 1, 11, 6, "Frame5_2Layout");
00168
00169 QHBoxLayout * Layout21 = new QHBoxLayout( 0, 0, 6, "Layout21");
00170
00171
00172
00173
00174
00175
00176
00177
00178
00179
00180
00181
00182 Frame5_2Layout->addLayout( Layout21, 0, 0 );
00183
00184 m_sheetStatus = new QLabel( Frame5_2, "m_tableStatus" );
00185 m_sheetStatus->setText( " " );
00186 Frame5_2Layout->addWidget( m_sheetStatus, 3, 0 );
00187
00188 m_SelectSheetLabel = new QLabel( Frame5_2, "m_SelectSheetLabel" );
00189 m_SelectSheetLabel->setText( i18n( "Select tables:" ) );
00190 Frame5_2Layout->addWidget( m_SelectSheetLabel, 1, 0 );
00191
00192 m_sheetView = new KListView( Frame5_2, "m_tableView" );
00193 m_sheetView->addColumn( i18n( "Sheet" ) );
00194 m_sheetView->setRootIsDecorated( FALSE );
00195
00196 Frame5_2Layout->addWidget( m_sheetView, 2, 0 );
00197
00198 m_sheetLayout->addWidget( Frame5_2, 0, 1 );
00199
00200 QFrame * Frame17_2 = new QFrame( m_sheet, "Frame17_2" );
00201 Frame17_2->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17_2->sizePolicy().hasHeightForWidth() ) );
00202 Frame17_2->setMinimumSize( QSize( 111, 0 ) );
00203 Frame17_2->setFrameShape( QFrame::NoFrame );
00204 Frame17_2->setFrameShadow( QFrame::Plain );
00205
00206 m_sheetLayout->addWidget( Frame17_2, 0, 0 );
00207 addPage( m_sheet, i18n( "Sheets" ) );
00208
00209 m_columns = new QWidget( this, "m_columns" );
00210 m_columnsLayout = new QGridLayout( m_columns, 1, 1, 11, 6, "m_columnsLayout");
00211
00212 QFrame * Frame5_2_2 = new QFrame( m_columns, "Frame5_2_2" );
00213 Frame5_2_2->setFrameShape( QFrame::MShape );
00214 Frame5_2_2->setFrameShadow( QFrame::MShadow );
00215 QGridLayout * Frame5_2_2Layout = new QGridLayout( Frame5_2_2, 1, 1, 11, 6, "Frame5_2_2Layout");
00216
00217 QLabel * TextLabel11_2 = new QLabel( Frame5_2_2, "TextLabel11_2" );
00218 TextLabel11_2->setText( i18n( "Select columns:" ) );
00219
00220 Frame5_2_2Layout->addWidget( TextLabel11_2, 0, 0 );
00221
00222 m_columnView = new KListView( Frame5_2_2, "m_columnView" );
00223 m_columnView->addColumn( i18n( "Column" ) );
00224 m_columnView->addColumn( i18n( "Sheet" ) );
00225 m_columnView->addColumn( i18n( "Data Type" ) );
00226 m_columnView->setRootIsDecorated( FALSE );
00227
00228 Frame5_2_2Layout->addWidget( m_columnView, 1, 0 );
00229
00230 m_columnsStatus = new QLabel( Frame5_2_2, "m_columnsStatus" );
00231 m_columnsStatus->setText( " " );
00232 Frame5_2_2Layout->addWidget( m_columnsStatus, 2, 0 );
00233
00234 m_columnsLayout->addWidget( Frame5_2_2, 0, 1 );
00235
00236 QFrame * Frame17_3 = new QFrame( m_columns, "Frame17_3" );
00237 Frame17_3->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17_3->sizePolicy().hasHeightForWidth() ) );
00238 Frame17_3->setMinimumSize( QSize( 111, 0 ) );
00239 Frame17_3->setFrameShape( QFrame::NoFrame );
00240 Frame17_3->setFrameShadow( QFrame::Plain );
00241
00242 m_columnsLayout->addWidget( Frame17_3, 0, 0 );
00243 addPage( m_columns, i18n( "Columns" ) );
00244
00245
00246
00247 m_options = new QWidget( this, "m_options" );
00248 m_optionsLayout = new QGridLayout( m_options, 1, 1, 11, 6, "m_optionsLayout");
00249
00250 QFrame * optionsFrame = new QFrame( m_options, "optionsFrame" );
00251 optionsFrame->setFrameShape( QFrame::MShape );
00252 optionsFrame->setFrameShadow( QFrame::MShadow );
00253 QGridLayout * optionsFrameLayout = new QGridLayout( optionsFrame, 1, 1, 11, 6, "optionsFrameLayout");
00254
00255 m_columns_1 = new QComboBox( false, optionsFrame, "m_columns_1" );
00256 optionsFrameLayout->addWidget( m_columns_1, 2, 0 );
00257
00258 m_operatorValue_2 = new QLineEdit( optionsFrame, "m_operatorValue_2" );
00259 optionsFrameLayout->addWidget( m_operatorValue_2, 3, 2 );
00260
00261 m_andBox = new QRadioButton( optionsFrame, "m_andBox" );
00262 m_andBox->setText( i18n( "Match all of the following (AND)" ) );
00263 m_andBox->setChecked( true );
00264
00265 optionsFrameLayout->addMultiCellWidget( m_andBox, 0, 0, 0, 2 );
00266
00267 m_orBox = new QRadioButton( optionsFrame, "m_orBox" );
00268 m_orBox->setText( i18n( "Match any of the following (OR)" ) );
00269 optionsFrameLayout->addMultiCellWidget( m_orBox, 1, 1, 0, 2 );
00270
00271 m_operatorValue_1 = new QLineEdit( optionsFrame, "m_operatorValue" );
00272 optionsFrameLayout->addWidget( m_operatorValue_1, 2, 2 );
00273
00274 m_columns_2 = new QComboBox( FALSE, optionsFrame, "m_columns_2" );
00275 optionsFrameLayout->addWidget( m_columns_2, 3, 0 );
00276
00277 m_operatorValue_3 = new QLineEdit( optionsFrame, "m_operatorValue_3" );
00278 optionsFrameLayout->addWidget( m_operatorValue_3, 4, 2 );
00279
00280 m_operator_1 = new QComboBox( FALSE, optionsFrame, "m_operator_1" );
00281 m_operator_1->insertItem( i18n( "equals" ) );
00282 m_operator_1->insertItem( i18n( "not equal" ) );
00283 m_operator_1->insertItem( i18n( "in" ) );
00284 m_operator_1->insertItem( i18n( "not in" ) );
00285 m_operator_1->insertItem( i18n( "like" ) );
00286 m_operator_1->insertItem( i18n( "greater" ) );
00287 m_operator_1->insertItem( i18n( "lesser" ) );
00288 m_operator_1->insertItem( i18n( "greater or equal" ) );
00289 m_operator_1->insertItem( i18n( "less or equal" ) );
00290
00291 optionsFrameLayout->addWidget( m_operator_1, 2, 1 );
00292
00293 m_operator_2 = new QComboBox( FALSE, optionsFrame, "m_operator_2" );
00294 m_operator_2->insertItem( i18n( "equals" ) );
00295 m_operator_2->insertItem( i18n( "not equal" ) );
00296 m_operator_2->insertItem( i18n( "in" ) );
00297 m_operator_2->insertItem( i18n( "not in" ) );
00298 m_operator_2->insertItem( i18n( "like" ) );
00299 m_operator_2->insertItem( i18n( "greater" ) );
00300 m_operator_2->insertItem( i18n( "lesser" ) );
00301
00302 optionsFrameLayout->addWidget( m_operator_2, 3, 1 );
00303
00304 m_operator_3 = new QComboBox( FALSE, optionsFrame, "m_operator_3" );
00305 m_operator_3->insertItem( i18n( "equals" ) );
00306 m_operator_3->insertItem( i18n( "not equal" ) );
00307 m_operator_3->insertItem( i18n( "in" ) );
00308 m_operator_3->insertItem( i18n( "not in" ) );
00309 m_operator_3->insertItem( i18n( "like" ) );
00310 m_operator_3->insertItem( i18n( "greater" ) );
00311 m_operator_3->insertItem( i18n( "lesser" ) );
00312
00313 optionsFrameLayout->addWidget( m_operator_3, 4, 1 );
00314
00315 m_columns_3 = new QComboBox( false, optionsFrame, "m_columns_3" );
00316
00317 optionsFrameLayout->addWidget( m_columns_3, 4, 0 );
00318
00319 m_distinct = new QCheckBox( optionsFrame, "m_distinct" );
00320 m_distinct->setText( i18n( "Distinct" ) );
00321
00322 optionsFrameLayout->addWidget( m_distinct, 7, 2 );
00323
00324 QLabel * TextLabel19 = new QLabel( optionsFrame, "TextLabel19" );
00325 TextLabel19->setText( i18n( "Sorted by" ) );
00326 optionsFrameLayout->addWidget( TextLabel19, 5, 0 );
00327
00328 m_columnsSort_1 = new QComboBox( false, optionsFrame, "m_columnsSort_1" );
00329 optionsFrameLayout->addWidget( m_columnsSort_1, 5, 1 );
00330
00331 m_sortMode_1 = new QComboBox( false, optionsFrame, "m_sortMode_1" );
00332 m_sortMode_1->insertItem( i18n( "Ascending" ) );
00333 m_sortMode_1->insertItem( i18n( "Descending" ) );
00334 optionsFrameLayout->addWidget( m_sortMode_1, 5, 2 );
00335
00336 QLabel * TextLabel19_2 = new QLabel( optionsFrame, "TextLabel19_2" );
00337 TextLabel19_2->setText( i18n( "Sorted by" ) );
00338 optionsFrameLayout->addWidget( TextLabel19_2, 6, 0 );
00339
00340 m_columnsSort_2 = new QComboBox( false, optionsFrame, "m_columnsSort_2" );
00341 optionsFrameLayout->addWidget( m_columnsSort_2, 6, 1 );
00342
00343 m_sortMode_2 = new QComboBox( false, optionsFrame, "m_sortMode_2" );
00344 m_sortMode_2->insertItem( i18n( "Ascending" ) );
00345 m_sortMode_2->insertItem( i18n( "Descending" ) );
00346
00347 optionsFrameLayout->addWidget( m_sortMode_2, 6, 2 );
00348 QSpacerItem* spacer = new QSpacerItem( 20, 20, QSizePolicy::Expanding, QSizePolicy::Minimum );
00349 optionsFrameLayout->addItem( spacer, 7, 1 );
00350 QSpacerItem* spacer_2 = new QSpacerItem( 20, 20, QSizePolicy::Expanding, QSizePolicy::Minimum );
00351 optionsFrameLayout->addItem( spacer_2, 7, 0 );
00352
00353 m_optionsLayout->addWidget( optionsFrame, 0, 1 );
00354
00355 QFrame * Frame17_4 = new QFrame( m_options, "Frame17_4" );
00356 Frame17_4->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17_4->sizePolicy().hasHeightForWidth() ) );
00357 Frame17_4->setMinimumSize( QSize( 111, 0 ) );
00358 Frame17_4->setFrameShape( QFrame::NoFrame );
00359 Frame17_4->setFrameShadow( QFrame::Plain );
00360
00361 m_optionsLayout->addWidget( Frame17_4, 0, 0 );
00362 addPage( m_options, i18n( "Query Options" ) );
00363
00364
00365
00366 m_result = new QWidget( this, "m_result" );
00367 m_resultLayout = new QGridLayout( m_result, 1, 1, 11, 6, "m_resultLayout");
00368
00369 QFrame * Frame5_2_2_3 = new QFrame( m_result, "Frame5_2_2_3" );
00370 Frame5_2_2_3->setFrameShape( QFrame::MShape );
00371 Frame5_2_2_3->setFrameShadow( QFrame::MShadow );
00372 QGridLayout * Frame5_2_2_3Layout = new QGridLayout( Frame5_2_2_3, 1, 1, 11, 6, "Frame5_2_2_3Layout");
00373
00374 QLabel * TextLabel17 = new QLabel( Frame5_2_2_3, "TextLabel17" );
00375 TextLabel17->setText( i18n( "SQL query:" ) );
00376 Frame5_2_2_3Layout->addWidget( TextLabel17, 0, 0 );
00377
00378 m_sqlQuery = new QTextEdit( Frame5_2_2_3, "m_sqlQuery" );
00379 Frame5_2_2_3Layout->addWidget( m_sqlQuery, 1, 0 );
00380
00381 QFrame * Frame12 = new QFrame( Frame5_2_2_3, "Frame12" );
00382 Frame12->setFrameShape( QFrame::StyledPanel );
00383 Frame12->setFrameShadow( QFrame::Raised );
00384 QGridLayout * Frame12Layout = new QGridLayout( Frame12, 1, 1, 11, 6, "Frame12Layout");
00385
00386 m_startingRegion = new QRadioButton( Frame12, "m_startingRegion" );
00387 m_startingRegion->setText( i18n( "Insert in region" ) );
00388 Frame12Layout->addWidget( m_startingRegion, 0, 0 );
00389
00390 m_cell = new QLineEdit( Frame12, "m_cell" );
00391 Frame12Layout->addWidget( m_cell, 1, 1 );
00392
00393 m_region = new QLineEdit( Frame12, "m_region" );
00394 Frame12Layout->addWidget( m_region, 0, 1 );
00395
00396 m_startingCell = new QRadioButton( Frame12, "m_startingCell" );
00397 m_startingCell->setText( i18n( "Starting in cell" ) );
00398 m_startingCell->setChecked( TRUE );
00399 Frame12Layout->addWidget( m_startingCell, 1, 0 );
00400
00401 Frame5_2_2_3Layout->addWidget( Frame12, 2, 0 );
00402 m_resultLayout->addWidget( Frame5_2_2_3, 0, 1 );
00403
00404 QFrame * Frame17_5 = new QFrame( m_result, "Frame17_5" );
00405 Frame17_5->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17_5->sizePolicy().hasHeightForWidth() ) );
00406 Frame17_5->setMinimumSize( QSize( 111, 0 ) );
00407 Frame17_5->setFrameShape( QFrame::NoFrame );
00408 Frame17_5->setFrameShadow( QFrame::Plain );
00409
00410 m_resultLayout->addWidget( Frame17_5, 0, 0 );
00411 addPage( m_result, i18n( "Result" ) );
00412
00413 finishButton()->setEnabled(false);
00414
00415
00416 connect( m_orBox, SIGNAL( clicked() ), this, SLOT( orBox_clicked() ) );
00417 connect( m_andBox, SIGNAL( clicked() ), this, SLOT( andBox_clicked() ) );
00418 connect( m_startingCell, SIGNAL( clicked() ), this, SLOT( startingCell_clicked() ) );
00419 connect( m_startingRegion, SIGNAL( clicked() ), this, SLOT( startingRegion_clicked() ) );
00420 connect( m_driver, SIGNAL( activated(int) ), this, SLOT( databaseDriverChanged(int) ) );
00421 connect( m_host, SIGNAL( textChanged(const QString &) ), this, SLOT( databaseHostChanged(const QString &) ) );
00422 connect( m_databaseName, SIGNAL( textChanged(const QString &) ), this, SLOT( databaseNameChanged(const QString &) ) );
00423 connect( m_sheetView, SIGNAL( contextMenuRequested( QListViewItem *, const QPoint &, int ) ),
00424 this, SLOT( popupSheetViewMenu(QListViewItem *, const QPoint &, int ) ) );
00425 connect( m_sheetView, SIGNAL( clicked( QListViewItem * ) ), this, SLOT( sheetViewClicked( QListViewItem * ) ) );
00426
00427 QStringList str = QSqlDatabase::drivers();
00428 m_driver->insertItem("");
00429 m_driver->insertStringList( str );
00430
00431
00432 helpButton()->hide();
00433 setNextEnabled(m_database, false);
00434 setNextEnabled(m_sheet, false);
00435 setNextEnabled(m_columns, false);
00436 setNextEnabled(m_options, false);
00437 setNextEnabled(m_result, false);
00438 }
00439
00440 DatabaseDialog::~DatabaseDialog()
00441 {
00442
00443 if ( m_dbConnection )
00444 m_dbConnection->close();
00445 }
00446
00447 void DatabaseDialog::switchPage( int id )
00448 {
00449 if ( id > eResult )
00450 --m_currentPage;
00451 if ( id < eDatabase )
00452 ++m_currentPage;
00453
00454 switch ( id )
00455 {
00456 case eDatabase:
00457 showPage(m_database);
00458 break;
00459
00460 case eSheets:
00461 showPage(m_sheet);
00462 break;
00463
00464 case eColumns:
00465 showPage(m_columns);
00466 break;
00467
00468 case eOptions:
00469 showPage(m_options);
00470 break;
00471
00472 case eResult:
00473 showPage(m_result);
00474 break;
00475
00476 default:
00477 break;
00478 }
00479 }
00480
00481 void DatabaseDialog::next()
00482 {
00483 switch ( m_currentPage )
00484 {
00485 case eDatabase:
00486 if (!databaseDoNext())
00487 return;
00488 break;
00489
00490 case eSheets:
00491 if (!sheetsDoNext())
00492 return;
00493 break;
00494
00495 case eColumns:
00496 if (!columnsDoNext())
00497 return;
00498 break;
00499
00500 case eOptions:
00501 if (!optionsDoNext())
00502 return;
00503 break;
00504
00505 case eResult:
00506
00507 break;
00508
00509 default:
00510 break;
00511 }
00512
00513 ++m_currentPage;
00514
00515 switchPage( m_currentPage );
00516 }
00517
00518 void DatabaseDialog::back()
00519 {
00520 --m_currentPage;
00521
00522 switchPage( m_currentPage );
00523 }
00524
00525 void DatabaseDialog::accept()
00526 {
00527 Sheet * sheet = m_pView->activeSheet();
00528 int top;
00529 int left;
00530 int width = -1;
00531 int height = -1;
00532 if ( m_startingRegion->isChecked() )
00533 {
00534 Range range( m_region->text() );
00535 if ( range.isSheetKnown() )
00536 {
00537 KMessageBox::error( this, i18n("You cannot specify a table here.") );
00538 m_region->setFocus();
00539 m_region->selectAll();
00540 return;
00541 }
00542
00543 range.setSheet(sheet);
00544
00545 if ( !range.isValid() )
00546 {
00547 KMessageBox::error( this, i18n("You have to specify a valid region.") );
00548 m_region->setFocus();
00549 m_region->selectAll();
00550 return;
00551 }
00552
00553 top = range.range().top();
00554 left = range.range().left();
00555 width = range.range().width();
00556 height = range.range().height();
00557 }
00558 else
00559 {
00560 Point point( m_cell->text() );
00561 if ( point.isSheetKnown() )
00562 {
00563 KMessageBox::error( this, i18n("You cannot specify a table here.") );
00564 m_cell->setFocus();
00565 m_cell->selectAll();
00566 return;
00567 }
00568 point.setSheet(sheet);
00569
00570 if ( !point.isValid() )
00571 {
00572 KMessageBox::error( this, i18n("You have to specify a valid cell.") );
00573 m_cell->setFocus();
00574 m_cell->selectAll();
00575 return;
00576 }
00577 top = point.pos().y();
00578 left = point.pos().x();
00579 }
00580
00581 int i;
00582 QString queryStr;
00583 QString tmp = m_sqlQuery->text();
00584 for ( i = 0; i < (int) tmp.length(); ++i )
00585 {
00586 if ( tmp[i] != '\n' )
00587 queryStr += tmp[i];
00588 else
00589 queryStr += " ";
00590 }
00591
00592 Cell * cell;
00593 QSqlQuery query( QString::null, m_dbConnection );
00594
00595
00596
00597
00598
00599
00600
00601 QRegExp couldModifyDB( "(^|[( \\s])(UPDATE|DELETE|INSERT|CREATE) ", false );
00602 QRegExp couldQueryDB( "(^|[( \\s])(SELECT) ", false );
00603
00604 if (couldModifyDB.search( queryStr ) != -1 || couldQueryDB.search ( queryStr ) == -1 )
00605 {
00606 KMessageBox::error( this, i18n("You are not allowed to change data in the database.") );
00607 m_sqlQuery->setFocus();
00608 return;
00609 }
00610
00611 if ( !query.exec( queryStr ) )
00612 {
00613 KMessageBox::error( this, i18n( "Executing query failed." ) );
00614 m_sqlQuery->setFocus();
00615 return;
00616 }
00617
00618 if ( query.size() == 0 )
00619 {
00620 KMessageBox::error( this, i18n( "You did not get any results with this query." ) );
00621 m_sqlQuery->setFocus();
00622 return;
00623 }
00624
00625 int y = 0;
00626 int count = m_columns_1->count();
00627 if ( width != -1 )
00628 {
00629 if ( count > width )
00630 count = width;
00631 }
00632
00633 if ( height == -1 )
00634 {
00635 height = 0;
00636 if ( query.first() )
00637 {
00638 if ( query.isValid() )
00639 ++height;
00640 }
00641 while ( query.next() )
00642 {
00643 if ( query.isValid() )
00644 ++height;
00645 }
00646 }
00647
00648 if ( !m_pView->doc()->undoLocked() )
00649 {
00650 QRect r(left, top, count, height);
00651 UndoInsertData * undo = new UndoInsertData( m_pView->doc(), sheet, r );
00652 m_pView->doc()->addCommand( undo );
00653 }
00654
00655 m_pView->doc()->emitBeginOperation();
00656
00657 if ( query.first() )
00658 {
00659 if ( query.isValid() )
00660 {
00661 for ( i = 0; i < count; ++i )
00662 {
00663 cell = sheet->nonDefaultCell( left + i, top + y );
00664 cell->setCellText( query.value( i ).toString() );
00665 }
00666 ++y;
00667 }
00668 }
00669
00670 if ( y != height )
00671 {
00672 while ( query.next() )
00673 {
00674 if ( !query.isValid() )
00675 continue;
00676
00677 for ( i = 0; i < count; ++i )
00678 {
00679 cell = sheet->nonDefaultCell( left + i, top + y );
00680 cell->setCellText( query.value( i ).toString() );
00681 }
00682 ++y;
00683
00684 if ( y == height )
00685 break;
00686 }
00687 }
00688
00689 m_pView->slotUpdateView( sheet );
00690 KWizard::accept();
00691 }
00692
00693 bool DatabaseDialog::databaseDoNext()
00694 {
00695 m_dbConnection = QSqlDatabase::addDatabase( m_driver->currentText() );
00696
00697 if ( m_dbConnection )
00698 {
00699 m_dbConnection->setDatabaseName( m_databaseName->text() );
00700 m_dbConnection->setHostName( m_host->text() );
00701
00702 if ( !m_username->text().isEmpty() )
00703 m_dbConnection->setUserName( m_username->text() );
00704
00705 if ( !m_password->text().isEmpty() )
00706 m_dbConnection->setPassword( m_password->text() );
00707
00708 if ( !m_port->text().isEmpty() )
00709 {
00710 bool ok = false;
00711 int port = m_port->text().toInt( &ok );
00712 if (!ok)
00713 {
00714 KMessageBox::error( this, i18n("The port must be a number") );
00715 return false;
00716 }
00717 m_dbConnection->setPort( port );
00718 }
00719
00720 m_databaseStatus->setText( i18n("Connecting to database...") );
00721 if ( m_dbConnection->open() )
00722 {
00723 m_databaseStatus->setText( i18n("Connected. Retrieving table information...") );
00724 QStringList sheetList( m_dbConnection->tables() );
00725
00726 if ( sheetList.isEmpty() )
00727 {
00728 KMessageBox::error( this, i18n("This database contains no tables") );
00729 return false;
00730 }
00731
00732 unsigned int i;
00733 m_sheetView->clear();
00734
00735 for ( i = 0; i < sheetList.size(); ++i )
00736 {
00737 QCheckListItem * item = new QCheckListItem( m_sheetView, sheetList[i],
00738 QCheckListItem::CheckBox );
00739 item->setOn(false);
00740 m_sheetView->insertItem( item );
00741 }
00742
00743 m_sheetView->setEnabled( true );
00744 m_databaseStatus->setText( " " );
00745 }
00746 else
00747 {
00748 QSqlError error = m_dbConnection->lastError();
00749 QString errorMsg;
00750 QString err1 = error.driverText();
00751 QString err2 = error.databaseText();
00752 if ( !err1.isEmpty() )
00753 {
00754 errorMsg.append( error.driverText() );
00755 errorMsg.append( "\n" );
00756 }
00757 if ( !err2.isEmpty() && err1 != err2)
00758 {
00759 errorMsg.append( error.databaseText() );
00760 errorMsg.append( "\n" );
00761 }
00762 m_databaseStatus->setText( " " );
00763
00764 KMessageBox::error( this, errorMsg );
00765 return false;
00766 }
00767 }
00768 else
00769 {
00770 KMessageBox::error( this, i18n("Driver could not be loaded") );
00771 m_databaseStatus->setText( " " );
00772 return false;
00773 }
00774 setNextEnabled(m_sheet, true);
00775
00776 return true;
00777 }
00778
00779 bool DatabaseDialog::sheetsDoNext()
00780 {
00781 m_databaseStatus->setText( i18n("Retrieving meta data of tables...") );
00782 QStringList sheets;
00783
00784 for (QListViewItem * item = (QCheckListItem *) m_sheetView->firstChild(); item; item = item->nextSibling())
00785 {
00786 if (((QCheckListItem * ) item)->isOn())
00787 {
00788 sheets.append(((QCheckListItem * ) item)->text());
00789 }
00790 }
00791
00792 if (sheets.empty())
00793 {
00794 KMessageBox::error( this, i18n("You have to select at least one table.") );
00795 return false;
00796 }
00797
00798 m_columnView->clear();
00799 QSqlRecord info;
00800 QCheckListItem * item;
00801 for (int i = 0; i < (int) sheets.size(); ++i)
00802 {
00803 info = m_dbConnection->record( sheets[i] );
00804 for (int j = 0; j < (int) info.count(); ++j)
00805 {
00806 QString name = info.fieldName(j);
00807 item = new QCheckListItem( m_columnView, name,
00808 QCheckListItem::CheckBox );
00809 item->setOn(false);
00810 m_columnView->insertItem( item );
00811 item->setText( 1, sheets[i] );
00812 QSqlField * field = info.field(name);
00813 item->setText( 2, QVariant::typeToName(field->type()) );
00814 }
00815 }
00816 m_columnView->setSorting(1, true);
00817 m_columnView->sort();
00818 m_columnView->setSorting( -1 );
00819
00820 setNextEnabled(m_columns, true);
00821
00822 return true;
00823 }
00824
00825 bool DatabaseDialog::columnsDoNext()
00826 {
00827 QStringList columns;
00828 for (QListViewItem * item = m_columnView->firstChild(); item; item = item->nextSibling())
00829 {
00830 if (((QCheckListItem * ) item)->isOn())
00831 {
00832 columns.append( item->text(1) + "." + ((QCheckListItem * ) item)->text());
00833 }
00834 }
00835
00836 if (columns.empty())
00837 {
00838 KMessageBox::error( this, i18n("You have to select at least one column.") );
00839 return false;
00840 }
00841
00842 m_columns_1->clear();
00843 m_columns_2->clear();
00844 m_columns_3->clear();
00845 m_columns_1->insertStringList(columns);
00846 m_columns_2->insertStringList(columns);
00847 m_columns_3->insertStringList(columns);
00848 m_columnsSort_1->clear();
00849 m_columnsSort_2->clear();
00850 m_columnsSort_1->insertItem( i18n("None") );
00851 m_columnsSort_2->insertItem( i18n("None") );
00852 m_columnsSort_1->insertStringList(columns);
00853 m_columnsSort_2->insertStringList(columns);
00854
00855 setNextEnabled(m_options, true);
00856
00857 return true;
00858 }
00859
00860
00861 QString DatabaseDialog::getWhereCondition(QString const & column,
00862 QString const & value,
00863 int op)
00864 {
00865 QString wherePart;
00866
00867 switch( op )
00868 {
00869 case 0:
00870 wherePart += column;
00871 wherePart += " = ";
00872 break;
00873 case 1:
00874 wherePart += "NOT ";
00875 wherePart += column;
00876 wherePart += " = ";
00877 break;
00878 case 2:
00879 wherePart += column;
00880 wherePart += " IN ";
00881 break;
00882 case 3:
00883 wherePart += "NOT ";
00884 wherePart += column;
00885 wherePart += " IN ";
00886 break;
00887 case 4:
00888 wherePart += column;
00889 wherePart += " LIKE ";
00890 break;
00891 case 5:
00892 wherePart += column;
00893 wherePart += " > ";
00894 break;
00895 case 6:
00896 wherePart += column;
00897 wherePart += " < ";
00898 break;
00899 case 7:
00900 wherePart += column;
00901 wherePart += " >= ";
00902 break;
00903 case 8:
00904 wherePart += column;
00905 wherePart += " <= ";
00906 break;
00907 }
00908
00909 if ( op != 2 && op != 3 )
00910 {
00911 QString val;
00912 bool ok = false;
00913 value.toDouble(&ok);
00914
00915 if ( !ok )
00916 {
00917 if (value[0] != '\'')
00918 val = "'";
00919
00920 val += value;
00921
00922 if (value[value.length() - 1] != '\'')
00923 val += "'";
00924 }
00925 else
00926 val = value;
00927
00928 wherePart += val;
00929 }
00930 else
00931 {
00932 QString val;
00933 if (value[0] != '(')
00934 val = "(";
00935 val += value;
00936 if ( value[value.length() - 1] != ')' )
00937 val += ")";
00938 wherePart += val;
00939 }
00940
00941 return wherePart;
00942 }
00943
00944 QString DatabaseDialog::exchangeWildcards(QString const & value)
00945 {
00946 QString str(value);
00947 int p = str.find('*');
00948 while ( p > -1 )
00949 {
00950 str = str.replace( p, 1, "%" );
00951 p = str.find('*');
00952 }
00953
00954 p = str.find('?');
00955 while ( p > -1 )
00956 {
00957 str = str.replace( p, 1, "_" );
00958 p = str.find('?');
00959 }
00960 return str;
00961 }
00962
00963 bool DatabaseDialog::optionsDoNext()
00964 {
00965 if ( m_operator_1->currentItem() == 4 )
00966 {
00967 if ( ( m_operatorValue_1->text().find('*') != -1 )
00968 || ( m_operatorValue_1->text().find('?') != -1 ) )
00969 {
00970
00971 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
00972 "The proper replacements are '%' or '_'. Do you want to replace them?") );
00973
00974 if ( res == KMessageBox::Yes )
00975 m_operatorValue_1->setText(exchangeWildcards(m_operatorValue_1->text()));
00976 }
00977 }
00978
00979 if ( m_operator_2->currentItem() == 4 )
00980 {
00981 if ( ( m_operatorValue_2->text().find('*') != -1 )
00982 || ( m_operatorValue_2->text().find('?') != -1 ) )
00983 {
00984
00985 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
00986 "The proper replacements are '%' or '_'. Do you want to replace them?") );
00987
00988 if ( res == KMessageBox::Yes )
00989 m_operatorValue_2->setText(exchangeWildcards(m_operatorValue_2->text()));
00990 }
00991 }
00992
00993 if ( m_operator_3->currentItem() == 4 )
00994 {
00995 if ( ( m_operatorValue_3->text().find('*') != -1 )
00996 || ( m_operatorValue_3->text().find('?') != -1 ) )
00997 {
00998
00999 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
01000 "The proper replacements are '%' or '_'. Do you want to replace them?") );
01001
01002 if ( res == KMessageBox::Yes )
01003 m_operatorValue_3->setText(exchangeWildcards(m_operatorValue_3->text()));
01004 }
01005 }
01006
01007 QString query("SELECT ");
01008
01009 if (m_distinct->isChecked())
01010 query += "DISTINCT ";
01011
01012 int i;
01013 int l = m_columns_1->count() - 1;
01014 for ( i = 0; i < l; ++i )
01015 {
01016 query += m_columns_1->text( i );
01017 query += ", ";
01018 }
01019 query += m_columns_1->text( l );
01020
01021 query += "\nFROM ";
01022
01023 QListViewItem * item = (QCheckListItem *) m_sheetView->firstChild();
01024 bool b = false;
01025 while ( item )
01026 {
01027 if (((QCheckListItem * ) item)->isOn())
01028 {
01029 if ( b )
01030 query += ", ";
01031 b = true;
01032 query += ((QCheckListItem * ) item)->text();
01033 }
01034 item = item->nextSibling();
01035 }
01036
01037 if ( ( !m_operatorValue_1->text().isEmpty() )
01038 || ( !m_operatorValue_2->text().isEmpty() )
01039 || ( !m_operatorValue_3->text().isEmpty() ) )
01040 query += "\nWHERE ";
01041
01042 bool added = false;
01043 if ( !m_operatorValue_1->text().isEmpty() )
01044 {
01045 query += getWhereCondition(m_columns_1->currentText(),
01046 m_operatorValue_1->text(),
01047 m_operator_1->currentItem());
01048 added = true;
01049 }
01050
01051 if ( !m_operatorValue_2->text().isEmpty() )
01052 {
01053 if (added)
01054 query += ( m_andBox->isChecked() ? " AND " : " OR " );
01055
01056 query += getWhereCondition(m_columns_2->currentText(),
01057 m_operatorValue_2->text(),
01058 m_operator_2->currentItem());
01059 added = true;
01060 }
01061
01062 if ( !m_operatorValue_3->text().isEmpty() )
01063 {
01064 if (added)
01065 query += ( m_andBox->isChecked() ? " AND " : " OR " );
01066
01067 query += getWhereCondition(m_columns_3->currentText(),
01068 m_operatorValue_3->text(),
01069 m_operator_3->currentItem());
01070 }
01071
01072 if ( (m_columnsSort_1->currentItem() != 0)
01073 || (m_columnsSort_2->currentItem() != 0) )
01074 {
01075 query += "\nORDER BY ";
01076 bool added = false;
01077 if ( m_columnsSort_1->currentItem() != 0 )
01078 {
01079 added = true;
01080 query += m_columnsSort_1->currentText();
01081 if ( m_sortMode_1->currentItem() == 1 )
01082 query += " DESC ";
01083 }
01084
01085 if ( m_columnsSort_2->currentItem() != 0 )
01086 {
01087 if ( added )
01088 query += ", ";
01089
01090 query += m_columnsSort_2->currentText();
01091 if ( m_sortMode_2->currentItem() == 1 )
01092 query += " DESC ";
01093 }
01094 }
01095
01096 m_sqlQuery->setText(query);
01097 m_cell->setText(Cell::name( m_targetRect.left(), m_targetRect.top() ) );
01098 m_region->setText(util_rangeName( m_targetRect ) );
01099
01100 setFinishEnabled( m_result, true );
01101
01102 return true;
01103 }
01104
01105 void DatabaseDialog::orBox_clicked()
01106 {
01107 m_andBox->setChecked( false );
01108 m_orBox->setChecked( true );
01109 }
01110
01111 void DatabaseDialog::andBox_clicked()
01112 {
01113 m_andBox->setChecked( true );
01114 m_orBox->setChecked( false );
01115 }
01116
01117 void DatabaseDialog::startingCell_clicked()
01118 {
01119 m_startingCell->setChecked( true );
01120 m_startingRegion->setChecked( false );
01121 }
01122
01123 void DatabaseDialog::startingRegion_clicked()
01124 {
01125 m_startingCell->setChecked( false );
01126 m_startingRegion->setChecked( true );
01127 }
01128
01129 void DatabaseDialog::connectButton_clicked()
01130 {
01131 qWarning( "DatabaseDialog::connectButton_clicked(): Not implemented yet!" );
01132 }
01133
01134 void DatabaseDialog::databaseNameChanged(const QString & s)
01135 {
01136 if ( !m_driver->currentText().isEmpty() && !s.isEmpty()
01137 && !m_host->text().isEmpty() )
01138 setNextEnabled(m_database, true);
01139 else
01140 setNextEnabled(m_database, false);
01141 }
01142
01143 void DatabaseDialog::databaseHostChanged(const QString & s)
01144 {
01145 if ( !m_driver->currentText().isEmpty() && !s.isEmpty()
01146 && !m_databaseName->text().isEmpty() )
01147 setNextEnabled(m_database, true);
01148 else
01149 setNextEnabled(m_database, false);
01150 }
01151
01152 void DatabaseDialog::databaseDriverChanged(int index)
01153 {
01154 if ( index > 0 && !m_host->text().isEmpty()
01155 && !m_databaseName->text().isEmpty() )
01156 setNextEnabled(m_database, true);
01157 else
01158 setNextEnabled(m_database, false);
01159 }
01160
01161 void DatabaseDialog::popupSheetViewMenu( QListViewItem *, const QPoint &, int )
01162 {
01163
01164 }
01165
01166 void DatabaseDialog::sheetViewClicked( QListViewItem * )
01167 {
01168
01169
01170
01171
01172
01173
01174 }
01175
01176
01177 #include "kspread_dlg_database.moc"
01178
01179 #endif // QT_NO_SQL