2
** File: evisdatabaseconnectiongui.cpp
3
** Author: Peter J. Ersts ( ersts at amnh.org )
4
** Creation Date: 2007-03-07
6
** Copyright ( c ) 2007, American Museum of Natural History. All rights reserved.
8
** This library/program is free software; you can redistribute it
9
** and/or modify it under the terms of the GNU Library General Public
10
** License as published by the Free Software Foundation; either
11
** version 2 of the License, or ( at your option ) any later version.
13
** This library/program is distributed in the hope that it will be useful,
14
** but WITHOUT ANY WARRANTY; without even the implied warranty of
15
** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16
** Library General Public License for more details.
18
** This work was made possible through a grant by the the John D. and
19
** Catherine T. MacArthur Foundation. Additionally, this program was prepared by
20
** the American Museum of Natural History under award No. NA05SEC46391002
21
** from the National Oceanic and Atmospheric Administration, U.S. Department
22
** of Commerce. The statements, findings, conclusions, and recommendations
23
** are those of the author( s ) and do not necessarily reflect the views of the
24
** National Oceanic and Atmospheric Administration or the Department of Commerce.
28
#include "evisdatabaseconnectiongui.h"
30
#include "qgscontexthelp.h"
31
#include "qgsapplication.h"
33
#include <QMessageBox>
34
#include <QTextStream>
35
#include <QFileDialog>
36
#include <QtSql/QSqlQuery>
37
#include <QtSql/QSqlError>
38
#include <QtSql/QSqlRecord>
39
#include <QtXml/QDomDocument>
40
#include <QtXml/QDomElement>
41
#include <QtXml/QDomNode>
45
* @param parent - Pointer the to parent QWidget for modality
46
* @param fl - Windown flags
48
eVisDatabaseConnectionGui::eVisDatabaseConnectionGui( QList<QTemporaryFile*>* theTemoraryFileList, QWidget* parent, Qt::WFlags fl )
49
: QDialog( parent, fl )
53
mTempOutputFileList = theTemoraryFileList;
55
//Initialize varaibles
56
mQueryDefinitionMap = new QMap<int, eVisQueryDefinition>;
57
mDatabaseConnection = 0;
59
//Create a new instance of the file selector
60
mDatabaseLayerFieldSelector = new eVisDatabaseLayerFieldSelectionGui( this, fl );
61
connect( mDatabaseLayerFieldSelector, SIGNAL( eVisDatabaseLayerFieldsSelected( QString, QString, QString ) ), this, SLOT( drawNewVectorLayer( QString, QString, QString ) ) );
63
//Populate gui components
65
cboxDatabaseType->insertItem( 0, "MSAccess" );
67
cboxDatabaseType->insertItem( 0, "MYSQL" );
68
cboxDatabaseType->insertItem( 0, "ODBC" );
69
cboxDatabaseType->insertItem( 0, "PostGreSQL" );
70
cboxDatabaseType->insertItem( 0, "SQLITE" );
71
cboxDatabaseType->insertItem( 0, tr( "Undefined" ) );
72
cboxDatabaseType->setCurrentIndex( 0 );
73
cboxPredefinedQueryList->insertItem( 0, tr( "No predefined queries loaded" ) );
76
QString myThemePath = QgsApplication::activeThemePath( );
77
pbtnOpenFile->setIcon( QIcon( QPixmap( myThemePath + "/mActionFolder.png" ) ) );
78
pbtnOpenFile->setToolTip( tr( "Open File" ) );
79
pbtnLoadPredefinedQueries->setIcon( QIcon( QPixmap( myThemePath + "/mActionFolder.png" ) ) );
80
pbtnLoadPredefinedQueries->setToolTip( tr( "Open File" ) );
86
eVisDatabaseConnectionGui::~eVisDatabaseConnectionGui( )
93
* Public and Private Slots
98
* Slot called after the user selects the x, y fields in the field selection gui component
99
* @param layerName - Name to display in the legend
100
* @param xCoordinate - Name of the field containing the x coordinate
101
* @param yCoordinate - Name of the field containing the y coordinate
103
void eVisDatabaseConnectionGui::drawNewVectorLayer( QString layerName, QString xCoordinate, QString yCoordinate )
105
//if coorindate fields are defined, load as a delimited text layer
106
if ( !xCoordinate.isEmpty( ) && !yCoordinate.isEmpty( ) && mTempOutputFileList->size( ) > 0 )
108
//fileName is only available if the file is open
109
//the last file in the list is always the newest
110
mTempOutputFileList->last( )->open( );
111
QString uri = QString( "%1?delimiter=%2&xField=%3&yField=%4" )
112
.arg( mTempOutputFileList->last( )->fileName( ) )
116
emit drawVectorLayer( uri, layerName, "delimitedtext" );
117
mTempOutputFileList->last( )->close( );
122
* Slot called when the accept button is pressed
124
void eVisDatabaseConnectionGui::on_buttonBox_accepted( )
126
//Deallocate memory, basically a predescructor
127
if ( 0 != mDatabaseConnection )
129
mDatabaseConnection->close( );
130
delete( mDatabaseConnection );
133
if ( 0 != mDatabaseLayerFieldSelector )
135
delete( mDatabaseLayerFieldSelector );
138
if ( 0 != mQueryDefinitionMap )
140
mQueryDefinitionMap->clear();
141
delete mQueryDefinitionMap;
148
* Slot called when the cboxDatabaseType combo box index changes
149
* @param currentIndex - The new index of the currently selected field
151
void eVisDatabaseConnectionGui::on_cboxDatabaseType_currentIndexChanged( int currentIndex )
153
if ( cboxDatabaseType->currentText( ) == "MYSQL" )
155
lblDatabaseHost->setEnabled( true );
156
leDatabaseHost->setEnabled( true );
157
lblDatabasePort->setEnabled( true );
158
leDatabasePort->setText( "3306" );
159
leDatabasePort->setEnabled( true );
160
pbtnOpenFile->setEnabled( false );
161
lblDatabaseUsername->setEnabled( true );
162
leDatabaseUsername->setEnabled( true );
163
lblDatabasePassword->setEnabled( true );
164
leDatabasePassword->setEnabled( true );
165
leDatabaseName->setText( "" );
167
else if ( cboxDatabaseType->currentText( ) == "PostGreSQL" )
169
lblDatabaseHost->setEnabled( true );
170
leDatabaseHost->setEnabled( true );
171
lblDatabasePort->setEnabled( true );
172
leDatabasePort->setText( "5432" );
173
leDatabasePort->setEnabled( true );
174
pbtnOpenFile->setEnabled( false );
175
lblDatabaseUsername->setEnabled( true );
176
leDatabaseUsername->setEnabled( true );
177
lblDatabasePassword->setEnabled( true );
178
leDatabasePassword->setEnabled( true );
179
leDatabaseName->setText( "" );
181
else if ( cboxDatabaseType->currentText( ) == "SQLITE" || cboxDatabaseType->currentText( ) == "MSAccess" )
183
lblDatabaseHost->setEnabled( false );
184
leDatabaseHost->setText( "" );
185
leDatabaseHost->setEnabled( false );
186
lblDatabasePort->setEnabled( false );
187
leDatabasePort->setText( "" );
188
leDatabasePort->setEnabled( false );
189
pbtnOpenFile->setEnabled( true );
190
lblDatabaseUsername->setEnabled( false );
191
leDatabaseUsername->setText( "" );
192
leDatabaseUsername->setEnabled( false );
193
lblDatabasePassword->setEnabled( false );
194
leDatabasePassword->setText( "" );
195
leDatabasePassword->setEnabled( false );
196
leDatabaseName->setText( "" );
200
lblDatabaseHost->setEnabled( true );
201
leDatabaseHost->setEnabled( true );
202
lblDatabasePort->setEnabled( false );
203
leDatabasePort->setText( "" );
204
leDatabasePort->setEnabled( false );
205
pbtnOpenFile->setEnabled( false );
206
lblDatabaseUsername->setEnabled( true );
207
leDatabaseUsername->setEnabled( true );
208
lblDatabasePassword->setEnabled( true );
209
leDatabasePassword->setEnabled( true );
210
leDatabaseName->setText( "" );
215
* Slot called when pbtnConnect button pressed. This function does some basic error checking before
216
* requesting a new database connection
218
void eVisDatabaseConnectionGui::on_pbtnConnect_clicked( )
220
teditConsole->append( tr( "New Database connection requested..." ) );
223
if ( cboxDatabaseType->currentText( ) == tr( "Undefined" ) )
225
teditConsole->append( tr( "Error: You must select a database type" ) );
229
if ( !errors && ( cboxDatabaseType->currentText( ) == "MYSQL" || cboxDatabaseType->currentText( ) == "PostGreSQL" ) )
231
if ( leDatabaseHost->text( ).isEmpty( ) )
233
teditConsole->append( tr( "Error: No host name entered" ) );
238
if ( !errors && leDatabaseName->text( ).isEmpty( ) )
240
teditConsole->append( tr( "Error: No database name entered" ) );
244
//If no errors thus far, request a new database connection
247
eVisDatabaseConnection::DATABASE_TYPE myDatabaseType;
248
if ( cboxDatabaseType->currentText( ) == "MSAccess" )
250
myDatabaseType = eVisDatabaseConnection::MSACCESS;
252
else if ( cboxDatabaseType->currentText( ) == "MYSQL" )
254
myDatabaseType = eVisDatabaseConnection::QMYSQL;
256
else if ( cboxDatabaseType->currentText( ) == "ODBC" )
258
myDatabaseType = eVisDatabaseConnection::QODBC;
260
else if ( cboxDatabaseType->currentText( ) == "PostGreSQL" )
262
myDatabaseType = eVisDatabaseConnection::QPSQL;
266
myDatabaseType = eVisDatabaseConnection::QSQLITE;
269
//If there is aready a database connection object, reset with the current parameters
270
if ( 0 != mDatabaseConnection )
272
mDatabaseConnection->resetConnectionParameters( leDatabaseHost->text( ), leDatabasePort->text( ).toInt( ), leDatabaseName->text( ), leDatabaseUsername->text( ), leDatabasePassword->text( ), myDatabaseType );
274
else //create a new database connection object
276
mDatabaseConnection = new eVisDatabaseConnection( leDatabaseHost->text( ), leDatabasePort->text( ).toInt( ), leDatabaseName->text( ), leDatabaseUsername->text( ), leDatabasePassword->text( ), myDatabaseType );
279
//Try to connect the database connection object
280
if ( mDatabaseConnection->connect( ) )
282
teditConsole->append( tr( "Connection to [%1.%2] established" ).arg( leDatabaseHost->text() ).arg( leDatabaseName->text() ) );
283
lblConnectionStatus->setText( tr( "connected" ) );
285
//List the tables in the database
286
teditConsole->append( tr( "Tables" ) + ":" );
287
QStringList myTableList = mDatabaseConnection->tables( );
288
for ( int myIterator = 0; myIterator < myTableList.size( ); myIterator++ )
290
teditConsole->append( "->" + myTableList[myIterator] );
295
teditConsole->append( tr( "Connection to [%1.%2] failed: %3" )
296
.arg( leDatabaseHost->text() ).arg( leDatabaseName->text( ) ).arg( mDatabaseConnection->lastError() ) );
302
* Slot called when pbtnLoadPredefinedQueries button is pressed. The method will open a file dialog and then
303
* try to parse through an XML file of predefined queries.
305
void eVisDatabaseConnectionGui::on_pbtnLoadPredefinedQueries_clicked( )
307
//There probably needs to be some more error checking, but works for now.
309
//Select the XML file to parse
310
QString myFilename = QFileDialog::getOpenFileName( this, tr( "Open File" ), ".", "XML ( *.xml )" );
311
if ( myFilename != "" )
313
//Display the name of the file being parsed
314
lblPredefinedQueryFilename->setText( myFilename );
316
//If the file exists load it into a QDomDocument
317
QFile myInputFile( myFilename );
318
if ( myInputFile.open( QIODevice::ReadOnly | QIODevice::Text ) )
323
QDomDocument myXmlDoc;
324
if ( myXmlDoc.setContent( &myInputFile, &errorString, &errorLine, &errorColumn ) )
326
//clear any existing query descrptions
327
cboxPredefinedQueryList->clear( );
328
if ( !mQueryDefinitionMap->empty( ) )
330
delete( mQueryDefinitionMap );
331
mQueryDefinitionMap = new QMap<int, eVisQueryDefinition>;
334
//Loop through each child looking for a query tag
335
int myQueryCount = 0;
336
QDomNode myNode = myXmlDoc.documentElement( ).firstChild( );
337
while ( !myNode.isNull( ) )
339
if ( myNode.toElement( ).tagName( ) == "query" )
342
eVisQueryDefinition myQueryDefinition;
343
QDomNode myChildNodes = myNode.toElement( ).firstChild( );
344
while ( !myChildNodes.isNull( ) )
346
QDomNode myDataNode = myChildNodes.toElement( ).firstChild( );
347
QString myDataNodeContent = "";
348
if ( !myDataNode.isNull( ) )
350
myDataNodeContent = myDataNode.toText( ).data( );
353
if ( myChildNodes.toElement( ).tagName( ) == "shortdescription" )
355
if ( myDataNodeContent != "" )
357
myQueryDefinition.setShortDescription( myDataNodeContent );
362
else if ( myChildNodes.toElement( ).tagName( ) == "description" )
364
myQueryDefinition.setDescription( myDataNodeContent );
366
else if ( myChildNodes.toElement( ).tagName( ) == "databasetype" )
368
myQueryDefinition.setDatabaseType( myDataNodeContent );
370
else if ( myChildNodes.toElement( ).tagName( ) == "databasehost" )
372
myQueryDefinition.setDatabaseHost( myDataNodeContent );
374
else if ( myChildNodes.toElement( ).tagName( ) == "databaseport" )
376
myQueryDefinition.setDatabasePort( myDataNodeContent.toInt( ) );
378
else if ( myChildNodes.toElement( ).tagName( ) == "databasename" )
380
myQueryDefinition.setDatabaseName( myDataNodeContent );
382
else if ( myChildNodes.toElement( ).tagName( ) == "databaseusername" )
384
myQueryDefinition.setDatabaseUsername( myDataNodeContent );
386
else if ( myChildNodes.toElement( ).tagName( ) == "databasepassword" )
388
myQueryDefinition.setDatabasePassword( myDataNodeContent );
390
else if ( myChildNodes.toElement( ).tagName( ) == "sqlstatement" )
392
myQueryDefinition.setSqlStatement( myDataNodeContent );
395
myChildNodes = myChildNodes.nextSibling( );
396
} //end while( !myChildNodes.isNull( ) )
400
mQueryDefinitionMap->insert( myQueryCount - 1, myQueryDefinition );
401
cboxPredefinedQueryList->insertItem( myQueryCount - 1, myQueryDefinition.shortDescription( ) );
403
} //end if( myNode.toElement( ).tagName( ) == "query" )
404
myNode = myNode.nextSibling( );
405
} // end while( !myNode.isNull( ) )
409
teditConsole->append( tr( "Error: Parse error at line %1, column %2: %3" ).arg( errorLine ).arg( errorColumn ).arg( errorString ) );
414
teditConsole->append( tr( "Error: Unabled to open file [%1]" ).arg( myFilename ) );
420
* Slot called when cboxPredefinedQueryList combo box index changes
421
* @param index - The current index of the selected item
423
void eVisDatabaseConnectionGui::on_cboxPredefinedQueryList_currentIndexChanged( int index )
425
if ( !mQueryDefinitionMap->isEmpty( ) )
427
//get the query definition at the current index
428
//NOTE: not really necessary to check to see if index is out of range from the query definition map because items cannot
429
//be added to the combo box unless they are added to the query definition map
430
eVisQueryDefinition myQueryDefinition = mQueryDefinitionMap->value( index );
432
//Populate the GUI components with the values from the query definition
433
teditQueryDescription->setText( myQueryDefinition.description( ) );
434
cboxDatabaseType->setCurrentIndex( cboxDatabaseType->findText( myQueryDefinition.databaseType( ) ) );
435
leDatabaseHost->setText( myQueryDefinition.databaseHost( ) );
436
leDatabasePort->setText( QString( "%1" ).arg( myQueryDefinition.databasePort( ) ) );
437
leDatabaseName->setText( myQueryDefinition.databaseName( ) );
438
leDatabaseUsername->setText( myQueryDefinition.databaseUsername( ) );
439
leDatabasePassword->setText( myQueryDefinition.databasePassword( ) );
440
teditSqlStatement->setText( myQueryDefinition.sqlStatement( ) );
445
* Slot called when pbtnOpenFile button is pressed
447
void eVisDatabaseConnectionGui::on_pbtnOpenFile_clicked( )
449
if ( cboxDatabaseType->currentText( ) == "MSAccess" )
450
leDatabaseName->setText( QFileDialog::getOpenFileName( this, tr( "Open File" ), ".", "MSAccess ( *.mdb )" ) );
452
leDatabaseName->setText( QFileDialog::getOpenFileName( this, tr( "Open File" ), ".", "Sqlite ( *.db )" ) );
456
* Slot called when the pbtnRunQuery button is pressed
458
void eVisDatabaseConnectionGui::on_pbtnRunQuery_clicked( )
460
//Check to see if we have a query
461
if ( !teditSqlStatement->toPlainText( ).isEmpty( ) )
463
//Verify that we have an active database connection
464
if ( 0 != mDatabaseConnection )
467
QSqlQuery* myResults = mDatabaseConnection->query( teditSqlStatement->toPlainText( ) );
468
if ( 0 == myResults )
470
teditConsole->append( tr( "Error: Query failed: %1" ).arg( mDatabaseConnection->lastError( ) ) );
472
else if ( myResults->isSelect( ) )
474
//if valid and a select query, save results into temporary file and load as layer
476
if ( myResults->isValid( ) )
478
mTempOutputFileList->append( new QTemporaryFile( ) );
479
if ( mTempOutputFileList->last( )->open( ) )
481
QTextStream outputStream( mTempOutputFileList->last( ) );
482
QStringList fieldList;
484
* Output column names
486
for ( int x = 0; x < myResults->record( ).count( ); x++ )
490
outputStream << myResults->record( ).fieldName( x );
494
outputStream << "\t" << myResults->record( ).fieldName( x );
496
fieldList << myResults->record( ).fieldName( x );
498
outputStream << endl;
502
while ( myResults->isValid( ) )
504
for ( int x = 0; x < myResults->record( ).count( ); x++ )
508
outputStream << myResults->value( x ).toString( );
512
outputStream << "\t" << myResults->value( x ).toString( );
515
outputStream << endl;
518
mTempOutputFileList->last( )->close( );
519
mDatabaseLayerFieldSelector->setFieldList( &fieldList );
520
mDatabaseLayerFieldSelector->show( );
524
teditConsole->append( tr( "Error: Could not create temporary file, process halted" ) );
531
teditConsole->append( tr( "Error: A database connection is not currently established" ) );