kspread Library API Documentation

kspread_dlg_database.cc

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