kspread

kspread_dlg_database.cpp
1/* This file is part of the KDE project
2 Copyright (C) 2002-2003 Norbert Andres <nandres@web.de>
3 (C) 2002 Ariya Hidayat <ariya@kde.org>
4 (C) 2002 Laurent Montel <montel@kde.org>
5
6 This library is free software; you can redistribute it and/or
7 modify it under the terms of the GNU Library General Public
8 License as published by the Free Software Foundation; either
9 version 2 of the License, or (at your option) any later version.
10
11 This library is distributed in the hope that it will be useful,
12 but WITHOUT ANY WARRANTY; without even the implied warranty of
13 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 Library General Public License for more details.
15
16 You should have received a copy of the GNU Library General Public License
17 along with this library; see the file COPYING.LIB. If not, write to
18 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
19 * Boston, MA 02110-1301, USA.
20*/
21
22#include "kspread_cell.h"
23#include "kspread_dlg_database.h"
24#include "kspread_doc.h"
25#include "kspread_sheet.h"
26#include "kspread_util.h"
27#include "kspread_undo.h"
28#include "kspread_view.h"
29
30#include <kdebug.h>
31#include <tdelistview.h>
32#include <tdelocale.h>
33#include <tdemessagebox.h>
34#include <knumvalidator.h>
35#include <kpushbutton.h>
36
37#include <tqcheckbox.h>
38#include <tqcombobox.h>
39#include <tqframe.h>
40#include <tqheader.h>
41#include <tqlabel.h>
42#include <tqlayout.h>
43#include <tqlineedit.h>
44#include <tqpushbutton.h>
45#include <tqradiobutton.h>
46#include <tqsqldatabase.h>
47#include <tqsqlerror.h>
48#include <tqsqlfield.h>
49#include <tqsqlquery.h>
50#include <tqsqlrecord.h>
51#include <tqtextedit.h>
52#include <tqtooltip.h>
53#include <tqvariant.h>
54#include <tqwhatsthis.h>
55#include <tqwidget.h>
56
57using namespace KSpread;
58
59#ifndef TQT_NO_SQL
60
61/********************************************************
62 * Database wizard *
63 ********************************************************/
64
65DatabaseDialog::DatabaseDialog( View * parent, TQRect const & rect, const char * name, bool modal, WFlags fl )
66 : KWizard( (TQWidget *) parent, name, modal, fl ),
67 m_currentPage( eDatabase ),
68 m_pView( parent ),
69 m_targetRect( rect ),
70 m_dbConnection( 0L )
71{
72 if ( !name )
73 setName( "DatabaseDialog" );
74
75 setCaption( i18n( "Insert Data From Database" ) );
76
77 // database page
78
79 m_database = new TQWidget( this, "m_database" );
80 m_databaseLayout = new TQGridLayout( m_database, 1, 1, -1, -1, "m_databaseLayout");
81
82 TQFrame * Frame5 = new TQFrame( m_database, "Frame5" );
83 Frame5->setFrameShape( TQFrame::MShape );
84 Frame5->setFrameShadow( TQFrame::MShadow );
85 TQVBoxLayout * Frame5Layout = new TQVBoxLayout( Frame5, 11, 6, "Frame5Layout");
86
87
88 TQFrame * Frame16 = new TQFrame( Frame5, "Frame16" );
89 Frame16->setFrameShape( TQFrame::NoFrame );
90 Frame16->setFrameShadow( TQFrame::Plain );
91 TQGridLayout * Frame16Layout = new TQGridLayout( Frame16, 1, 1, 11, 7, "Frame16Layout");
92
93 m_Type = new TQLabel( Frame16, "m_Type" );
94 m_Type->setText( i18n( "Type:" ) );
95
96 Frame16Layout->addWidget( m_Type, 0, 0 );
97
98 TQLabel * TextLabel4 = new TQLabel( Frame16, "TextLabel4" );
99 TextLabel4->setText( i18n( "User name:\n"
100 "(if necessary)" ) );
101 Frame16Layout->addWidget( TextLabel4, 4, 0 );
102
103 TQLabel * TextLabel2 = new TQLabel( Frame16, "TextLabel2" );
104 TextLabel2->setText( i18n( "Host:" ) );
105 Frame16Layout->addWidget( TextLabel2, 2, 0 );
106
107 m_driver = new TQComboBox( false, Frame16, "m_driver" );
108 Frame16Layout->addWidget( m_driver, 0, 1 );
109
110 m_username = new TQLineEdit( Frame16, "m_username" );
111 Frame16Layout->addWidget( m_username, 4, 1 );
112
113 m_host = new TQLineEdit( Frame16, "m_host" );
114 m_host->setText("localhost");
115 Frame16Layout->addWidget( m_host, 2, 1 );
116
117 TQLabel * TextLabel3 = new TQLabel( Frame16, "TextLabel3" );
118 TextLabel3->setText( i18n( "Port:\n(if necessary)") );
119 Frame16Layout->addWidget( TextLabel3, 3, 0 );
120
121 m_password = new TQLineEdit( Frame16, "m_password" );
122 m_password->setEchoMode( TQLineEdit::Password );
123 Frame16Layout->addWidget( m_password, 5, 1 );
124
125 m_port = new TQLineEdit( Frame16, "m_port" );
126 m_port->setValidator( new KIntValidator( m_port ) );
127 Frame16Layout->addWidget( m_port, 3, 1 );
128
129 TQLabel * dbName = new TQLabel( Frame16, "dbName" );
130 dbName->setText( i18n( "Database name: ") );
131 Frame16Layout->addWidget( dbName, 1, 0 );
132
133 m_databaseName = new TQLineEdit( Frame16, "m_databaseName" );
134 Frame16Layout->addWidget( m_databaseName, 1, 1 );
135
136 TQLabel * TextLabel5 = new TQLabel( Frame16, "TextLabel5" );
137 TextLabel5->setText( i18n( "Password:\n"
138 "(if necessary)" ) );
139 Frame16Layout->addWidget( TextLabel5, 5, 0 );
140 Frame5Layout->addWidget( Frame16 );
141
142 m_databasetStatus = new TQLabel( Frame5, "m_databasetStatus" );
143 m_databasetStatus->setSizePolicy( TQSizePolicy( (TQSizePolicy::SizeType)0, (TQSizePolicy::SizeType)5, 0, 0, m_databasetStatus->sizePolicy().hasHeightForWidth() ) );
144 m_databasetStatus->setMaximumSize( TQSize( 32767, 30 ) );
145 m_databasetStatus->setText( " " );
146 Frame5Layout->addWidget( m_databasetStatus );
147
148 m_databaseLayout->addWidget( Frame5, 0, 1 );
149
150 TQFrame * Frame17 = new TQFrame( m_database, "Frame17" );
151 Frame17->setSizePolicy( TQSizePolicy( (TQSizePolicy::SizeType)0, (TQSizePolicy::SizeType)7, 0, 0, Frame17->sizePolicy().hasHeightForWidth() ) );
152 Frame17->setMinimumSize( TQSize( 111, 0 ) );
153 Frame17->setFrameShape( TQFrame::NoFrame );
154 Frame17->setFrameShadow( TQFrame::Plain );
155
156 m_databaseLayout->addWidget( Frame17, 0, 0 );
157 addPage( m_database, i18n( "Database" ) );
158
159 // new page
160
161 m_sheet = new TQWidget( this, "m_table" );
162 m_sheetLayout = new TQGridLayout( m_sheet, 1, 1, 11, 6, "m_tableLayout");
163
164 TQFrame * Frame5_2 = new TQFrame( m_sheet, "Frame5_2" );
165 Frame5_2->setFrameShape( TQFrame::MShape );
166 Frame5_2->setFrameShadow( TQFrame::MShadow );
167 TQGridLayout * Frame5_2Layout = new TQGridLayout( Frame5_2, 1, 1, 11, 6, "Frame5_2Layout");
168
169 TQHBoxLayout * Layout21 = new TQHBoxLayout( 0, 0, 6, "Layout21");
170
171 // TQLabel * TextLabel12_2 = new TQLabel( Frame5_2, "TextLabel12_2" );
172 // TextLabel12_2->setText( i18n( "Database:" ) );
173 // Layout21->addWidget( TextLabel12_2 );
174
175 // m_databaseList = new TQComboBox( false, Frame5_2, "m_databaseList" );
176 // Layout21->addWidget( m_databaseList );
177
178 // m_connectButton = new KPushButton( Frame5_2, "m_connectButton" );
179 // m_connectButton->setText( i18n( "&Connect" ) );
180 // Layout21->addWidget( m_connectButton );
181
182 Frame5_2Layout->addLayout( Layout21, 0, 0 );
183
184 m_sheetStatus = new TQLabel( Frame5_2, "m_tableStatus" );
185 m_sheetStatus->setText( " " );
186 Frame5_2Layout->addWidget( m_sheetStatus, 3, 0 );
187
188 m_SelectSheetLabel = new TQLabel( Frame5_2, "m_SelectSheetLabel" );
189 m_SelectSheetLabel->setText( i18n( "Select tables:" ) );
190 Frame5_2Layout->addWidget( m_SelectSheetLabel, 1, 0 );
191
192 m_sheetView = new TDEListView( Frame5_2, "m_tableView" );
193 m_sheetView->addColumn( i18n( "Sheet" ) );
194 m_sheetView->setRootIsDecorated( false );
195
196 Frame5_2Layout->addWidget( m_sheetView, 2, 0 );
197
198 m_sheetLayout->addWidget( Frame5_2, 0, 1 );
199
200 TQFrame * Frame17_2 = new TQFrame( m_sheet, "Frame17_2" );
201 Frame17_2->setSizePolicy( TQSizePolicy( (TQSizePolicy::SizeType)0, (TQSizePolicy::SizeType)7, 0, 0, Frame17_2->sizePolicy().hasHeightForWidth() ) );
202 Frame17_2->setMinimumSize( TQSize( 111, 0 ) );
203 Frame17_2->setFrameShape( TQFrame::NoFrame );
204 Frame17_2->setFrameShadow( TQFrame::Plain );
205
206 m_sheetLayout->addWidget( Frame17_2, 0, 0 );
207 addPage( m_sheet, i18n( "Sheets" ) );
208
209 m_columns = new TQWidget( this, "m_columns" );
210 m_columnsLayout = new TQGridLayout( m_columns, 1, 1, 11, 6, "m_columnsLayout");
211
212 TQFrame * Frame5_2_2 = new TQFrame( m_columns, "Frame5_2_2" );
213 Frame5_2_2->setFrameShape( TQFrame::MShape );
214 Frame5_2_2->setFrameShadow( TQFrame::MShadow );
215 TQGridLayout * Frame5_2_2Layout = new TQGridLayout( Frame5_2_2, 1, 1, 11, 6, "Frame5_2_2Layout");
216
217 TQLabel * TextLabel11_2 = new TQLabel( Frame5_2_2, "TextLabel11_2" );
218 TextLabel11_2->setText( i18n( "Select columns:" ) );
219
220 Frame5_2_2Layout->addWidget( TextLabel11_2, 0, 0 );
221
222 m_columnView = new TDEListView( Frame5_2_2, "m_columnView" );
223 m_columnView->addColumn( i18n( "Column" ) );
224 m_columnView->addColumn( i18n( "Sheet" ) );
225 m_columnView->addColumn( i18n( "Data Type" ) );
226 m_columnView->setRootIsDecorated( false );
227
228 Frame5_2_2Layout->addWidget( m_columnView, 1, 0 );
229
230 m_columnsStatus = new TQLabel( Frame5_2_2, "m_columnsStatus" );
231 m_columnsStatus->setText( " " );
232 Frame5_2_2Layout->addWidget( m_columnsStatus, 2, 0 );
233
234 m_columnsLayout->addWidget( Frame5_2_2, 0, 1 );
235
236 TQFrame * Frame17_3 = new TQFrame( m_columns, "Frame17_3" );
237 Frame17_3->setSizePolicy( TQSizePolicy( (TQSizePolicy::SizeType)0, (TQSizePolicy::SizeType)7, 0, 0, Frame17_3->sizePolicy().hasHeightForWidth() ) );
238 Frame17_3->setMinimumSize( TQSize( 111, 0 ) );
239 Frame17_3->setFrameShape( TQFrame::NoFrame );
240 Frame17_3->setFrameShadow( TQFrame::Plain );
241
242 m_columnsLayout->addWidget( Frame17_3, 0, 0 );
243 addPage( m_columns, i18n( "Columns" ) );
244
245 // options page
246
247 m_options = new TQWidget( this, "m_options" );
248 m_optionsLayout = new TQGridLayout( m_options, 1, 1, 11, 6, "m_optionsLayout");
249
250 TQFrame * optionsFrame = new TQFrame( m_options, "optionsFrame" );
251 optionsFrame->setFrameShape( TQFrame::MShape );
252 optionsFrame->setFrameShadow( TQFrame::MShadow );
253 TQGridLayout * optionsFrameLayout = new TQGridLayout( optionsFrame, 1, 1, 11, 6, "optionsFrameLayout");
254
255 m_columns_1 = new TQComboBox( false, optionsFrame, "m_columns_1" );
256 optionsFrameLayout->addWidget( m_columns_1, 2, 0 );
257
258 m_operatorValue_2 = new TQLineEdit( optionsFrame, "m_operatorValue_2" );
259 optionsFrameLayout->addWidget( m_operatorValue_2, 3, 2 );
260
261 m_andBox = new TQRadioButton( optionsFrame, "m_andBox" );
262 m_andBox->setText( i18n( "Match all of the following (AND)" ) );
263 m_andBox->setChecked( true );
264
265 optionsFrameLayout->addMultiCellWidget( m_andBox, 0, 0, 0, 2 );
266
267 m_orBox = new TQRadioButton( optionsFrame, "m_orBox" );
268 m_orBox->setText( i18n( "Match any of the following (OR)" ) );
269 optionsFrameLayout->addMultiCellWidget( m_orBox, 1, 1, 0, 2 );
270
271 m_operatorValue_1 = new TQLineEdit( optionsFrame, "m_operatorValue" );
272 optionsFrameLayout->addWidget( m_operatorValue_1, 2, 2 );
273
274 m_columns_2 = new TQComboBox( false, optionsFrame, "m_columns_2" );
275 optionsFrameLayout->addWidget( m_columns_2, 3, 0 );
276
277 m_operatorValue_3 = new TQLineEdit( optionsFrame, "m_operatorValue_3" );
278 optionsFrameLayout->addWidget( m_operatorValue_3, 4, 2 );
279
280 m_operator_1 = new TQComboBox( false, optionsFrame, "m_operator_1" );
281 m_operator_1->insertItem( i18n( "equals" ) );
282 m_operator_1->insertItem( i18n( "not equal" ) );
283 m_operator_1->insertItem( i18n( "in" ) );
284 m_operator_1->insertItem( i18n( "not in" ) );
285 m_operator_1->insertItem( i18n( "like" ) );
286 m_operator_1->insertItem( i18n( "greater" ) );
287 m_operator_1->insertItem( i18n( "lesser" ) );
288 m_operator_1->insertItem( i18n( "greater or equal" ) );
289 m_operator_1->insertItem( i18n( "less or equal" ) );
290
291 optionsFrameLayout->addWidget( m_operator_1, 2, 1 );
292
293 m_operator_2 = new TQComboBox( false, optionsFrame, "m_operator_2" );
294 m_operator_2->insertItem( i18n( "equals" ) );
295 m_operator_2->insertItem( i18n( "not equal" ) );
296 m_operator_2->insertItem( i18n( "in" ) );
297 m_operator_2->insertItem( i18n( "not in" ) );
298 m_operator_2->insertItem( i18n( "like" ) );
299 m_operator_2->insertItem( i18n( "greater" ) );
300 m_operator_2->insertItem( i18n( "lesser" ) );
301
302 optionsFrameLayout->addWidget( m_operator_2, 3, 1 );
303
304 m_operator_3 = new TQComboBox( false, optionsFrame, "m_operator_3" );
305 m_operator_3->insertItem( i18n( "equals" ) );
306 m_operator_3->insertItem( i18n( "not equal" ) );
307 m_operator_3->insertItem( i18n( "in" ) );
308 m_operator_3->insertItem( i18n( "not in" ) );
309 m_operator_3->insertItem( i18n( "like" ) );
310 m_operator_3->insertItem( i18n( "greater" ) );
311 m_operator_3->insertItem( i18n( "lesser" ) );
312
313 optionsFrameLayout->addWidget( m_operator_3, 4, 1 );
314
315 m_columns_3 = new TQComboBox( false, optionsFrame, "m_columns_3" );
316
317 optionsFrameLayout->addWidget( m_columns_3, 4, 0 );
318
319 m_distinct = new TQCheckBox( optionsFrame, "m_distinct" );
320 m_distinct->setText( i18n( "Distinct" ) );
321
322 optionsFrameLayout->addWidget( m_distinct, 7, 2 );
323
324 TQLabel * TextLabel19 = new TQLabel( optionsFrame, "TextLabel19" );
325 TextLabel19->setText( i18n( "Sorted by" ) );
326 optionsFrameLayout->addWidget( TextLabel19, 5, 0 );
327
328 m_columnsSort_1 = new TQComboBox( false, optionsFrame, "m_columnsSort_1" );
329 optionsFrameLayout->addWidget( m_columnsSort_1, 5, 1 );
330
331 m_sortMode_1 = new TQComboBox( false, optionsFrame, "m_sortMode_1" );
332 m_sortMode_1->insertItem( i18n( "Ascending" ) );
333 m_sortMode_1->insertItem( i18n( "Descending" ) );
334 optionsFrameLayout->addWidget( m_sortMode_1, 5, 2 );
335
336 TQLabel * TextLabel19_2 = new TQLabel( optionsFrame, "TextLabel19_2" );
337 TextLabel19_2->setText( i18n( "Sorted by" ) );
338 optionsFrameLayout->addWidget( TextLabel19_2, 6, 0 );
339
340 m_columnsSort_2 = new TQComboBox( false, optionsFrame, "m_columnsSort_2" );
341 optionsFrameLayout->addWidget( m_columnsSort_2, 6, 1 );
342
343 m_sortMode_2 = new TQComboBox( false, optionsFrame, "m_sortMode_2" );
344 m_sortMode_2->insertItem( i18n( "Ascending" ) );
345 m_sortMode_2->insertItem( i18n( "Descending" ) );
346
347 optionsFrameLayout->addWidget( m_sortMode_2, 6, 2 );
348 TQSpacerItem* spacer = new TQSpacerItem( 20, 20, TQSizePolicy::Expanding, TQSizePolicy::Minimum );
349 optionsFrameLayout->addItem( spacer, 7, 1 );
350 TQSpacerItem* spacer_2 = new TQSpacerItem( 20, 20, TQSizePolicy::Expanding, TQSizePolicy::Minimum );
351 optionsFrameLayout->addItem( spacer_2, 7, 0 );
352
353 m_optionsLayout->addWidget( optionsFrame, 0, 1 );
354
355 TQFrame * Frame17_4 = new TQFrame( m_options, "Frame17_4" );
356 Frame17_4->setSizePolicy( TQSizePolicy( (TQSizePolicy::SizeType)0, (TQSizePolicy::SizeType)7, 0, 0, Frame17_4->sizePolicy().hasHeightForWidth() ) );
357 Frame17_4->setMinimumSize( TQSize( 111, 0 ) );
358 Frame17_4->setFrameShape( TQFrame::NoFrame );
359 Frame17_4->setFrameShadow( TQFrame::Plain );
360
361 m_optionsLayout->addWidget( Frame17_4, 0, 0 );
362 addPage( m_options, i18n( "Query Options" ) );
363
364 // result page
365
366 m_result = new TQWidget( this, "m_result" );
367 m_resultLayout = new TQGridLayout( m_result, 1, 1, 11, 6, "m_resultLayout");
368
369 TQFrame * Frame5_2_2_3 = new TQFrame( m_result, "Frame5_2_2_3" );
370 Frame5_2_2_3->setFrameShape( TQFrame::MShape );
371 Frame5_2_2_3->setFrameShadow( TQFrame::MShadow );
372 TQGridLayout * Frame5_2_2_3Layout = new TQGridLayout( Frame5_2_2_3, 1, 1, 11, 6, "Frame5_2_2_3Layout");
373
374 TQLabel * TextLabel17 = new TQLabel( Frame5_2_2_3, "TextLabel17" );
375 TextLabel17->setText( i18n( "SQL query:" ) );
376 Frame5_2_2_3Layout->addWidget( TextLabel17, 0, 0 );
377
378 m_sqlQuery = new TQTextEdit( Frame5_2_2_3, "m_sqlQuery" );
379 Frame5_2_2_3Layout->addWidget( m_sqlQuery, 1, 0 );
380
381 TQFrame * Frame12 = new TQFrame( Frame5_2_2_3, "Frame12" );
382 Frame12->setFrameShape( TQFrame::StyledPanel );
383 Frame12->setFrameShadow( TQFrame::Raised );
384 TQGridLayout * Frame12Layout = new TQGridLayout( Frame12, 1, 1, 11, 6, "Frame12Layout");
385
386 m_startingRegion = new TQRadioButton( Frame12, "m_startingRegion" );
387 m_startingRegion->setText( i18n( "Insert in region" ) );
388 Frame12Layout->addWidget( m_startingRegion, 0, 0 );
389
390 m_cell = new TQLineEdit( Frame12, "m_cell" );
391 Frame12Layout->addWidget( m_cell, 1, 1 );
392
393 m_region = new TQLineEdit( Frame12, "m_region" );
394 Frame12Layout->addWidget( m_region, 0, 1 );
395
396 m_startingCell = new TQRadioButton( Frame12, "m_startingCell" );
397 m_startingCell->setText( i18n( "Starting in cell" ) );
398 m_startingCell->setChecked( true );
399 Frame12Layout->addWidget( m_startingCell, 1, 0 );
400
401 Frame5_2_2_3Layout->addWidget( Frame12, 2, 0 );
402 m_resultLayout->addWidget( Frame5_2_2_3, 0, 1 );
403
404 TQFrame * Frame17_5 = new TQFrame( m_result, "Frame17_5" );
405 Frame17_5->setSizePolicy( TQSizePolicy( (TQSizePolicy::SizeType)0, (TQSizePolicy::SizeType)7, 0, 0, Frame17_5->sizePolicy().hasHeightForWidth() ) );
406 Frame17_5->setMinimumSize( TQSize( 111, 0 ) );
407 Frame17_5->setFrameShape( TQFrame::NoFrame );
408 Frame17_5->setFrameShadow( TQFrame::Plain );
409
410 m_resultLayout->addWidget( Frame17_5, 0, 0 );
411 addPage( m_result, i18n( "Result" ) );
412
413 finishButton()->setEnabled(false);
414
415 // signals and slots connections
416 connect( m_orBox, TQ_SIGNAL( clicked() ), this, TQ_SLOT( orBox_clicked() ) );
417 connect( m_andBox, TQ_SIGNAL( clicked() ), this, TQ_SLOT( andBox_clicked() ) );
418 connect( m_startingCell, TQ_SIGNAL( clicked() ), this, TQ_SLOT( startingCell_clicked() ) );
419 connect( m_startingRegion, TQ_SIGNAL( clicked() ), this, TQ_SLOT( startingRegion_clicked() ) );
420 connect( m_driver, TQ_SIGNAL( activated(int) ), this, TQ_SLOT( databaseDriverChanged(int) ) );
421 connect( m_host, TQ_SIGNAL( textChanged(const TQString &) ), this, TQ_SLOT( databaseHostChanged(const TQString &) ) );
422 connect( m_databaseName, TQ_SIGNAL( textChanged(const TQString &) ), this, TQ_SLOT( databaseNameChanged(const TQString &) ) );
423 connect( m_sheetView, TQ_SIGNAL( contextMenuRequested( TQListViewItem *, const TQPoint &, int ) ),
424 this, TQ_SLOT( popupSheetViewMenu(TQListViewItem *, const TQPoint &, int ) ) );
425 connect( m_sheetView, TQ_SIGNAL( clicked( TQListViewItem * ) ), this, TQ_SLOT( sheetViewClicked( TQListViewItem * ) ) );
426
427 TQStringList str = TQSqlDatabase::drivers();
428 m_driver->insertItem("");
429 m_driver->insertStringList( str );
430
431
432 helpButton()->hide();
433 setNextEnabled(m_database, false);
434 setNextEnabled(m_sheet, false);
435 setNextEnabled(m_columns, false);
436 setNextEnabled(m_options, false);
437 setNextEnabled(m_result, false);
438}
439
440DatabaseDialog::~DatabaseDialog()
441{
442 // no need to delete child widgets, TQt does it all for us
443 if ( m_dbConnection )
444 m_dbConnection->close();
445}
446
447void DatabaseDialog::switchPage( int id )
448{
449 if ( id > eResult )
450 --m_currentPage;
451 if ( id < eDatabase )
452 ++m_currentPage;
453
454 switch ( id )
455 {
456 case eDatabase:
457 showPage(m_database);
458 break;
459
460 case eSheets:
461 showPage(m_sheet);
462 break;
463
464 case eColumns:
465 showPage(m_columns);
466 break;
467
468 case eOptions:
469 showPage(m_options);
470 break;
471
472 case eResult:
473 showPage(m_result);
474 break;
475
476 default:
477 break;
478 }
479}
480
481void DatabaseDialog::next()
482{
483 switch ( m_currentPage )
484 {
485 case eDatabase:
486 if (!databaseDoNext())
487 return;
488 break;
489
490 case eSheets:
491 if (!sheetsDoNext())
492 return;
493 break;
494
495 case eColumns:
496 if (!columnsDoNext())
497 return;
498 break;
499
500 case eOptions:
501 if (!optionsDoNext())
502 return;
503 break;
504
505 case eResult:
506 // there is nothing to do here
507 break;
508
509 default:
510 break;
511 }
512
513 ++m_currentPage;
514
515 switchPage( m_currentPage );
516}
517
518void DatabaseDialog::back()
519{
520 --m_currentPage;
521
522 switchPage( m_currentPage );
523}
524
525void DatabaseDialog::accept()
526{
527 Sheet * sheet = m_pView->activeSheet();
528 int top;
529 int left;
530 int width = -1;
531 int height = -1;
532 if ( m_startingRegion->isChecked() )
533 {
534 Range range( m_region->text() );
535 if ( range.isSheetKnown() )
536 {
537 KMessageBox::error( this, i18n("You cannot specify a table here.") );
538 m_region->setFocus();
539 m_region->selectAll();
540 return;
541 }
542
543 range.setSheet(sheet);
544
545 if ( !range.isValid() )
546 {
547 KMessageBox::error( this, i18n("You have to specify a valid region.") );
548 m_region->setFocus();
549 m_region->selectAll();
550 return;
551 }
552
553 top = range.range().top();
554 left = range.range().left();
555 width = range.range().width();
556 height = range.range().height();
557 }
558 else
559 {
560 Point point( m_cell->text() );
561 if ( point.isSheetKnown() )
562 {
563 KMessageBox::error( this, i18n("You cannot specify a table here.") );
564 m_cell->setFocus();
565 m_cell->selectAll();
566 return;
567 }
568 point.setSheet(sheet);
569 // if ( point.pos.x() < 1 || point.pos.y() < 1 )
570 if ( !point.isValid() )
571 {
572 KMessageBox::error( this, i18n("You have to specify a valid cell.") );
573 m_cell->setFocus();
574 m_cell->selectAll();
575 return;
576 }
577 top = point.pos().y();
578 left = point.pos().x();
579 }
580
581 int i;
582 TQString queryStr;
583 TQString tmp = m_sqlQuery->text();
584 for ( i = 0; i < (int) tmp.length(); ++i )
585 {
586 if ( tmp[i] != '\n' )
587 queryStr += tmp[i];
588 else
589 queryStr += " ";
590 }
591
592 Cell * cell;
593 TQSqlQuery query( TQString(), m_dbConnection );
594
595 // Check the whole query for SQL that might modify database.
596 // If there is an update command, then it must be at the start of the string,
597 // or after an open bracket (e.g. nested update) or a space to be valid SQL.
598 // An update command must also be followed by a space, or it would be parsed
599 // as an identifier.
600 // For sanity, also check that there is a SELECT
601 TQRegExp couldModifyDB( "(^|[( \\s])(UPDATE|DELETE|INSERT|CREATE) ", false /* cs */ );
602 TQRegExp couldQueryDB( "(^|[( \\s])(SELECT) ", false /* cs */ );
603
604 if (couldModifyDB.search( queryStr ) != -1 || couldQueryDB.search ( queryStr ) == -1 )
605 {
606 KMessageBox::error( this, i18n("You are not allowed to change data in the database.") );
607 m_sqlQuery->setFocus();
608 return;
609 }
610
611 if ( !query.exec( queryStr ) )
612 {
613 KMessageBox::error( this, i18n( "Executing query failed." ) );
614 m_sqlQuery->setFocus();
615 return;
616 }
617
618 if ( query.size() == 0 )
619 {
620 KMessageBox::error( this, i18n( "You did not get any results with this query." ) );
621 m_sqlQuery->setFocus();
622 return;
623 }
624
625 int y = 0;
626 int count = m_columns_1->count();
627 if ( width != -1 )
628 {
629 if ( count > width )
630 count = width;
631 }
632
633 if ( height == -1 )
634 {
635 height = 0;
636 if ( query.first() )
637 {
638 if ( query.isValid() )
639 ++height;
640 }
641 while ( query.next() )
642 {
643 if ( query.isValid() )
644 ++height;
645 }
646 }
647
648 if ( !m_pView->doc()->undoLocked() )
649 {
650 TQRect r(left, top, count, height);
651 UndoInsertData * undo = new UndoInsertData( m_pView->doc(), sheet, r );
652 m_pView->doc()->addCommand( undo );
653 }
654
655 m_pView->doc()->emitBeginOperation();
656
657 if ( query.first() )
658 {
659 if ( query.isValid() )
660 {
661 for ( i = 0; i < count; ++i )
662 {
663 cell = sheet->nonDefaultCell( left + i, top + y );
664 cell->setCellText( query.value( i ).toString() );
665 }
666 ++y;
667 }
668 }
669
670 if ( y != height )
671 {
672 while ( query.next() )
673 {
674 if ( !query.isValid() )
675 continue;
676
677 for ( i = 0; i < count; ++i )
678 {
679 cell = sheet->nonDefaultCell( left + i, top + y );
680 cell->setCellText( query.value( i ).toString() );
681 }
682 ++y;
683
684 if ( y == height )
685 break;
686 }
687 }
688
689 m_pView->slotUpdateView( sheet );
690 KWizard::accept();
691}
692
693bool DatabaseDialog::databaseDoNext()
694{
695 m_dbConnection = TQSqlDatabase::addDatabase( m_driver->currentText() );
696
697 if ( m_dbConnection )
698 {
699 m_dbConnection->setDatabaseName( m_databaseName->text() );
700 m_dbConnection->setHostName( m_host->text() );
701
702 if ( !m_username->text().isEmpty() )
703 m_dbConnection->setUserName( m_username->text() );
704
705 if ( !m_password->text().isEmpty() )
706 m_dbConnection->setPassword( m_password->text() );
707
708 if ( !m_port->text().isEmpty() )
709 {
710 bool ok = false;
711 int port = m_port->text().toInt( &ok );
712 if (!ok)
713 {
714 KMessageBox::error( this, i18n("The port must be a number") );
715 return false;
716 }
717 m_dbConnection->setPort( port );
718 }
719
720 m_databasetStatus->setText( i18n("Connecting to database...") );
721 if ( m_dbConnection->open() )
722 {
723 m_databasetStatus->setText( i18n("Connected. Retrieving table information...") );
724 TQStringList sheetList( m_dbConnection->tables() );
725
726 if ( sheetList.isEmpty() )
727 {
728 KMessageBox::error( this, i18n("This database contains no tables") );
729 return false;
730 }
731
732 unsigned int i;
733 m_sheetView->clear();
734
735 for ( i = 0; i < sheetList.size(); ++i )
736 {
737 TQCheckListItem * item = new TQCheckListItem( m_sheetView, sheetList[i],
738 TQCheckListItem::CheckBox );
739 item->setOn(false);
740 m_sheetView->insertItem( item );
741 }
742
743 m_sheetView->setEnabled( true );
744 m_databasetStatus->setText( " " );
745 }
746 else
747 {
748 TQSqlError error = m_dbConnection->lastError();
749 TQString errorMsg;
750 TQString err1 = error.driverText();
751 TQString err2 = error.databaseText();
752 if ( !err1.isEmpty() )
753 {
754 errorMsg.append( error.driverText() );
755 errorMsg.append( "\n" );
756 }
757 if ( !err2.isEmpty() && err1 != err2)
758 {
759 errorMsg.append( error.databaseText() );
760 errorMsg.append( "\n" );
761 }
762 m_databasetStatus->setText( " " );
763
764 KMessageBox::error( this, errorMsg );
765 return false;
766 }
767 }
768 else
769 {
770 KMessageBox::error( this, i18n("Driver could not be loaded") );
771 m_databasetStatus->setText( " " );
772 return false;
773 }
774 setNextEnabled(m_sheet, true);
775
776 return true;
777}
778
779bool DatabaseDialog::sheetsDoNext()
780{
781 m_databasetStatus->setText( i18n("Retrieving meta data of tables...") );
782 TQStringList sheets;
783
784 for (TQListViewItem * item = (TQCheckListItem *) m_sheetView->firstChild(); item; item = item->nextSibling())
785 {
786 if (((TQCheckListItem * ) item)->isOn())
787 {
788 sheets.append(((TQCheckListItem * ) item)->text());
789 }
790 }
791
792 if (sheets.empty())
793 {
794 KMessageBox::error( this, i18n("You have to select at least one table.") );
795 return false;
796 }
797
798 m_columnView->clear();
799 TQSqlRecord info;
800 TQCheckListItem * item;
801 for (int i = 0; i < (int) sheets.size(); ++i)
802 {
803 info = m_dbConnection->record( sheets[i] );
804 for (int j = 0; j < (int) info.count(); ++j)
805 {
806 TQString name = info.fieldName(j);
807 item = new TQCheckListItem( m_columnView, name,
808 TQCheckListItem::CheckBox );
809 item->setOn(false);
810 m_columnView->insertItem( item );
811 item->setText( 1, sheets[i] );
812 TQSqlField * field = info.field(name);
813 item->setText( 2, TQVariant::typeToName(field->type()) );
814 }
815 }
816 m_columnView->setSorting(1, true);
817 m_columnView->sort();
818 m_columnView->setSorting( -1 );
819
820 setNextEnabled(m_columns, true);
821
822 return true;
823}
824
825bool DatabaseDialog::columnsDoNext()
826{
827 TQStringList columns;
828 for (TQListViewItem * item = m_columnView->firstChild(); item; item = item->nextSibling())
829 {
830 if (((TQCheckListItem * ) item)->isOn())
831 {
832 columns.append( item->text(1) + "." + ((TQCheckListItem * ) item)->text());
833 }
834 }
835
836 if (columns.empty())
837 {
838 KMessageBox::error( this, i18n("You have to select at least one column.") );
839 return false;
840 }
841
842 m_columns_1->clear();
843 m_columns_2->clear();
844 m_columns_3->clear();
845 m_columns_1->insertStringList(columns);
846 m_columns_2->insertStringList(columns);
847 m_columns_3->insertStringList(columns);
848 m_columnsSort_1->clear();
849 m_columnsSort_2->clear();
850 m_columnsSort_1->insertItem( i18n("None") );
851 m_columnsSort_2->insertItem( i18n("None") );
852 m_columnsSort_1->insertStringList(columns);
853 m_columnsSort_2->insertStringList(columns);
854
855 setNextEnabled(m_options, true);
856
857 return true;
858}
859
860
861TQString DatabaseDialog::getWhereCondition(TQString const & column,
862 TQString const & value,
863 int op)
864{
865 TQString wherePart;
866
867 switch( op )
868 {
869 case 0:
870 wherePart += column;
871 wherePart += " = ";
872 break;
873 case 1:
874 wherePart += "NOT ";
875 wherePart += column;
876 wherePart += " = ";
877 break;
878 case 2:
879 wherePart += column;
880 wherePart += " IN ";
881 break;
882 case 3:
883 wherePart += "NOT ";
884 wherePart += column;
885 wherePart += " IN ";
886 break;
887 case 4:
888 wherePart += column;
889 wherePart += " LIKE ";
890 break;
891 case 5:
892 wherePart += column;
893 wherePart += " > ";
894 break;
895 case 6:
896 wherePart += column;
897 wherePart += " < ";
898 break;
899 case 7:
900 wherePart += column;
901 wherePart += " >= ";
902 break;
903 case 8:
904 wherePart += column;
905 wherePart += " <= ";
906 break;
907 }
908
909 if ( op != 2 && op != 3 )
910 {
911 TQString val;
912 bool ok = false;
913 value.toDouble(&ok);
914
915 if ( !ok )
916 {
917 if (value[0] != '\'')
918 val = "'";
919
920 val += value;
921
922 if (value[value.length() - 1] != '\'')
923 val += "'";
924 }
925 else
926 val = value;
927
928 wherePart += val;
929 }
930 else // "in" & "not in"
931 {
932 TQString val;
933 if (value[0] != '(')
934 val = "(";
935 val += value;
936 if ( value[value.length() - 1] != ')' )
937 val += ")";
938 wherePart += val;
939 }
940
941 return wherePart;
942}
943
944TQString DatabaseDialog::exchangeWildcards(TQString const & value)
945{
946 TQString str(value);
947 int p = str.find('*');
948 while ( p > -1 )
949 {
950 str = str.replace( p, 1, "%" );
951 p = str.find('*');
952 }
953
954 p = str.find('?');
955 while ( p > -1 )
956 {
957 str = str.replace( p, 1, "_" );
958 p = str.find('?');
959 }
960 return str;
961}
962
963bool DatabaseDialog::optionsDoNext()
964{
965 if ( m_operator_1->currentItem() == 4 )
966 {
967 if ( ( m_operatorValue_1->text().find('*') != -1 )
968 || ( m_operatorValue_1->text().find('?') != -1 ) )
969 {
970 // xgettext: no-c-format
971 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
972 "The proper replacements are '%' or '_'. Do you want to replace them?") );
973
974 if ( res == KMessageBox::Yes )
975 m_operatorValue_1->setText(exchangeWildcards(m_operatorValue_1->text()));
976 }
977 }
978
979 if ( m_operator_2->currentItem() == 4 )
980 {
981 if ( ( m_operatorValue_2->text().find('*') != -1 )
982 || ( m_operatorValue_2->text().find('?') != -1 ) )
983 {
984 // xgettext: no-c-format
985 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
986 "The proper replacements are '%' or '_'. Do you want to replace them?") );
987
988 if ( res == KMessageBox::Yes )
989 m_operatorValue_2->setText(exchangeWildcards(m_operatorValue_2->text()));
990 }
991 }
992
993 if ( m_operator_3->currentItem() == 4 )
994 {
995 if ( ( m_operatorValue_3->text().find('*') != -1 )
996 || ( m_operatorValue_3->text().find('?') != -1 ) )
997 {
998 // xgettext: no-c-format
999 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
1000 "The proper replacements are '%' or '_'. Do you want to replace them?") );
1001
1002 if ( res == KMessageBox::Yes )
1003 m_operatorValue_3->setText(exchangeWildcards(m_operatorValue_3->text()));
1004 }
1005 }
1006
1007 TQString query("SELECT ");
1008
1009 if (m_distinct->isChecked())
1010 query += "DISTINCT ";
1011
1012 int i;
1013 int l = m_columns_1->count() - 1;
1014 for ( i = 0; i < l; ++i )
1015 {
1016 query += m_columns_1->text( i );
1017 query += ", ";
1018 }
1019 query += m_columns_1->text( l );
1020
1021 query += "\nFROM ";
1022
1023 TQListViewItem * item = (TQCheckListItem *) m_sheetView->firstChild();
1024 bool b = false;
1025 while ( item )
1026 {
1027 if (((TQCheckListItem * ) item)->isOn())
1028 {
1029 if ( b )
1030 query += ", ";
1031 b = true;
1032 query += ((TQCheckListItem * ) item)->text();
1033 }
1034 item = item->nextSibling();
1035 }
1036
1037 if ( ( !m_operatorValue_1->text().isEmpty() )
1038 || ( !m_operatorValue_2->text().isEmpty() )
1039 || ( !m_operatorValue_3->text().isEmpty() ) )
1040 query += "\nWHERE ";
1041
1042 bool added = false;
1043 if ( !m_operatorValue_1->text().isEmpty() )
1044 {
1045 query += getWhereCondition(m_columns_1->currentText(),
1046 m_operatorValue_1->text(),
1047 m_operator_1->currentItem());
1048 added = true;
1049 }
1050
1051 if ( !m_operatorValue_2->text().isEmpty() )
1052 {
1053 if (added)
1054 query += ( m_andBox->isChecked() ? " AND " : " OR " );
1055
1056 query += getWhereCondition(m_columns_2->currentText(),
1057 m_operatorValue_2->text(),
1058 m_operator_2->currentItem());
1059 added = true;
1060 }
1061
1062 if ( !m_operatorValue_3->text().isEmpty() )
1063 {
1064 if (added)
1065 query += ( m_andBox->isChecked() ? " AND " : " OR " );
1066
1067 query += getWhereCondition(m_columns_3->currentText(),
1068 m_operatorValue_3->text(),
1069 m_operator_3->currentItem());
1070 }
1071
1072 if ( (m_columnsSort_1->currentItem() != 0)
1073 || (m_columnsSort_2->currentItem() != 0) )
1074 {
1075 query += "\nORDER BY ";
1076 bool added = false;
1077 if ( m_columnsSort_1->currentItem() != 0 )
1078 {
1079 added = true;
1080 query += m_columnsSort_1->currentText();
1081 if ( m_sortMode_1->currentItem() == 1 )
1082 query += " DESC ";
1083 }
1084
1085 if ( m_columnsSort_2->currentItem() != 0 )
1086 {
1087 if ( added )
1088 query += ", ";
1089
1090 query += m_columnsSort_2->currentText();
1091 if ( m_sortMode_2->currentItem() == 1 )
1092 query += " DESC ";
1093 }
1094 }
1095
1096 m_sqlQuery->setText(query);
1097 m_cell->setText(Cell::name( m_targetRect.left(), m_targetRect.top() ) );
1098 m_region->setText(util_rangeName( m_targetRect ) );
1099
1100 setFinishEnabled( m_result, true );
1101
1102 return true;
1103}
1104
1105void DatabaseDialog::orBox_clicked()
1106{
1107 m_andBox->setChecked( false );
1108 m_orBox->setChecked( true );
1109}
1110
1111void DatabaseDialog::andBox_clicked()
1112{
1113 m_andBox->setChecked( true );
1114 m_orBox->setChecked( false );
1115}
1116
1117void DatabaseDialog::startingCell_clicked()
1118{
1119 m_startingCell->setChecked( true );
1120 m_startingRegion->setChecked( false );
1121}
1122
1123void DatabaseDialog::startingRegion_clicked()
1124{
1125 m_startingCell->setChecked( false );
1126 m_startingRegion->setChecked( true );
1127}
1128
1129void DatabaseDialog::connectButton_clicked()
1130{
1131 tqWarning( "DatabaseDialog::connectButton_clicked(): Not implemented yet!" );
1132}
1133
1134void DatabaseDialog::databaseNameChanged(const TQString & s)
1135{
1136 if ( !m_driver->currentText().isEmpty() && !s.isEmpty()
1137 && !m_host->text().isEmpty() )
1138 setNextEnabled(m_database, true);
1139 else
1140 setNextEnabled(m_database, false);
1141}
1142
1143void DatabaseDialog::databaseHostChanged(const TQString & s)
1144{
1145 if ( !m_driver->currentText().isEmpty() && !s.isEmpty()
1146 && !m_databaseName->text().isEmpty() )
1147 setNextEnabled(m_database, true);
1148 else
1149 setNextEnabled(m_database, false);
1150}
1151
1152void DatabaseDialog::databaseDriverChanged(int index)
1153{
1154 if ( index > 0 && !m_host->text().isEmpty()
1155 && !m_databaseName->text().isEmpty() )
1156 setNextEnabled(m_database, true);
1157 else
1158 setNextEnabled(m_database, false);
1159}
1160
1161void DatabaseDialog::popupSheetViewMenu( TQListViewItem *, const TQPoint &, int )
1162{
1163 // TODO: popup menu with "Select All", "Inverse selection", "remove selection"
1164}
1165
1166void DatabaseDialog::sheetViewClicked( TQListViewItem * )
1167{
1168// if ( item )
1169// {
1170// TQCheckListItem * i = (TQCheckListItem *) item;
1171// i->setOn( !i->isOn() );
1172// }
1173// kdDebug() << "clicked" << endl;
1174}
1175
1176
1177#include "kspread_dlg_database.moc"
1178
1179#endif // TQT_NO_SQL
KSpread::Cell
For every cell in the spread sheet there is a Cell object.
Definition kspread_cell.h:138
KSpread::Cell::name
TQString name() const
Returns the name of the cell.
Definition kspread_cell.cpp:369
KSpread::Cell::setCellText
void setCellText(const TQString &_text, bool asString=false)
The high-level method for setting text, when the user inputs it.
Definition kspread_cell.cpp:4621
KSpread::Point
Represents the position of a single cell in a workbook.
Definition kspread_util.h:55
KSpread::Range
Represents a region within a workbook.
Definition kspread_util.h:131
KSpread::View
The View class displays a KSpread document.
Definition kspread_view.h:188
KSpread
The KSpread namespace.
Definition commands.h:39