kspread

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., 51 Franklin Street, Fifth Floor,
00019  * Boston, MA 02110-1301, 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 using namespace KSpread;
00058 
00059 #ifndef QT_NO_SQL
00060 
00061 /********************************************************
00062  *                 Database wizard                      *
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   // database page
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   // new page
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   //  QLabel * TextLabel12_2 = new QLabel( Frame5_2, "TextLabel12_2" );
00172   //  TextLabel12_2->setText( i18n( "Database:" ) );
00173   //  Layout21->addWidget( TextLabel12_2 );
00174 
00175   //  m_databaseList = new QComboBox( FALSE, Frame5_2, "m_databaseList" );
00176   //  Layout21->addWidget( m_databaseList );
00177 
00178   //  m_connectButton = new KPushButton( Frame5_2, "m_connectButton" );
00179   //  m_connectButton->setText( i18n( "&Connect" ) );
00180   //  Layout21->addWidget( m_connectButton );
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   // options page
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   // result page
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   // signals and slots connections
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   // no need to delete child widgets, Qt does it all for us
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     // there is nothing to do here
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     //    if ( point.pos.x() < 1 || point.pos.y() < 1 )
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   // Check the whole query for SQL that might modify database.
00596   // If there is an update command, then it must be at the start of the string,
00597   // or after an open bracket (e.g. nested update) or a space to be valid SQL.
00598   // An update command must also be followed by a space, or it would be parsed
00599   // as an identifier.
00600   // For sanity, also check that there is a SELECT 
00601   QRegExp couldModifyDB( "(^|[( \\s])(UPDATE|DELETE|INSERT|CREATE) ", false /* cs */ );
00602   QRegExp couldQueryDB( "(^|[( \\s])(SELECT) ", false /* cs */ );
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  // "in" & "not in"
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       // xgettext: no-c-format
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       // xgettext: no-c-format
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       // xgettext: no-c-format
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   // TODO: popup menu with "Select All", "Inverse selection", "remove selection"
01164 }
01165 
01166 void DatabaseDialog::sheetViewClicked( QListViewItem * )
01167 {
01168 //   if ( item )
01169 //   {
01170 //     QCheckListItem * i = (QCheckListItem *) item;
01171 //     i->setOn( !i->isOn() );
01172 //   }
01173 //   kdDebug() << "clicked" << endl;
01174 }
01175 
01176 
01177 #include "kspread_dlg_database.moc"
01178 
01179 #endif // QT_NO_SQL
KDE Home | KDE Accessibility Home | Description of Access Keys