vtkMySQLDatabase.cxx 14 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*=========================================================================

  Program:   Visualization Toolkit
  Module:    vtkMySQLDatabase.cxx

  Copyright (c) Ken Martin, Will Schroeder, Bill Lorensen
  All rights reserved.
  See Copyright.txt or http://www.kitware.com/Copyright.htm for details.

     This software is distributed WITHOUT ANY WARRANTY; without even
     the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
     PURPOSE.  See the above copyright notice for more information.

=========================================================================*/
15
16
17
18
/*----------------------------------------------------------------------------
  Copyright (c) Sandia Corporation
  See Copyright.txt or http://www.paraview.org/HTML/Copyright.html for details.
  ----------------------------------------------------------------------------*/
19
#include "vtkMySQLDatabase.h"
20
#include "vtkMySQLDatabasePrivate.h"
21
22
#include "vtkMySQLQuery.h"

23
24
#include "vtkSQLDatabaseSchema.h"

25
26
27
28
#include "vtkObjectFactory.h"
#include "vtkStringArray.h"

#include <vtksys/SystemTools.hxx>
29
#include <vtksys/ios/sstream>
30
31

#include <assert.h>
32
33

#define VTK_MYSQL_DEFAULT_PORT 3306
34
 
35
vtkCxxRevisionMacro(vtkMySQLDatabase, "1.23");
36
37
38
vtkStandardNewMacro(vtkMySQLDatabase);

// ----------------------------------------------------------------------
39
40
vtkMySQLDatabase::vtkMySQLDatabase() :
  Private(new vtkMySQLDatabasePrivate())
41
{
42
43
44
  this->Tables = vtkStringArray::New();
  this->Tables->Register(this);
  this->Tables->Delete();
45
46
47
  
  // Initialize instance variables
  this->DatabaseType = 0;
48
  this->SetDatabaseType( "mysql" );
49
  this->HostName = 0;
50
  this->User = 0;
51
52
53
  this->Password = 0;
  this->DatabaseName = 0;
  this->ConnectOptions = 0;
54
55
56
57
  // Default: connect to local machine on standard port
  this->SetHostName( "localhost" );
  this->ServerPort = VTK_MYSQL_DEFAULT_PORT;
  //this->SetPassword( "" );
58
59
60
61
62
}

// ----------------------------------------------------------------------
vtkMySQLDatabase::~vtkMySQLDatabase()
{
63
64
65
  if ( this->IsOpen() )
    {
    this->Close();
66
    }
67
68
  this->SetDatabaseType( 0 );
  this->SetHostName( 0 );
69
  this->SetUser( 0 );
70
71
72
  this->SetPassword( 0 );
  this->SetDatabaseName( 0 );
  this->SetConnectOptions( 0 );
73

74
  this->Tables->UnRegister(this);
75
76

  delete this->Private;
77
78
}

79
80
81
82
83
84
// ----------------------------------------------------------------------
void vtkMySQLDatabase::PrintSelf(ostream &os, vtkIndent indent)
{
  this->Superclass::PrintSelf(os, indent);
  os << indent << "DatabaseType: " << (this->DatabaseType ? this->DatabaseType : "NULL") << endl;
  os << indent << "HostName: " << (this->HostName ? this->HostName : "NULL") << endl;
85
  os << indent << "User: " << (this->User ? this->User : "NULL") << endl;
86
87
88
89
90
91
  os << indent << "Password: " << (this->Password ? this->Password : "NULL") << endl;
  os << indent << "DatabaseName: " << (this->DatabaseName ? this->DatabaseName : "NULL") << endl;
  os << indent << "ServerPort: " << this->ServerPort << endl;
  os << indent << "ConnectOptions: " << (this->ConnectOptions ? this->ConnectOptions : "NULL") << endl;
}

92
// ----------------------------------------------------------------------
93
bool vtkMySQLDatabase::IsSupported(int feature)
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
{
  switch (feature)
    {
    case VTK_SQL_FEATURE_BATCH_OPERATIONS:
    case VTK_SQL_FEATURE_NAMED_PLACEHOLDERS:
      return false;

    case VTK_SQL_FEATURE_POSITIONAL_PLACEHOLDERS:
#if MYSQL_VERSION_ID >= 40108
      return true;
#else
      return false;
#endif

    case VTK_SQL_FEATURE_PREPARED_QUERIES:
    {
    if (mysql_get_client_version() >= 40108 &&
111
        mysql_get_server_version(& this->Private->NullConnection) >= 40100)
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
      {
      return true;
      }
    else
      {
      return false;
      }
    };

    case VTK_SQL_FEATURE_QUERY_SIZE:
    case VTK_SQL_FEATURE_BLOB:
    case VTK_SQL_FEATURE_LAST_INSERT_ID:
    case VTK_SQL_FEATURE_UNICODE:
    case VTK_SQL_FEATURE_TRANSACTIONS:
      return true;
      
    default:
    {
    vtkErrorMacro(<< "Unknown SQL feature code " << feature << "!  See "
                  << "vtkSQLDatabase.h for a list of possible features.");
    return false;
    };
    }
}

// ----------------------------------------------------------------------
138
bool vtkMySQLDatabase::Open()
139
140
{

141
  if ( this->IsOpen() )
142
    {
143
    vtkGenericWarningMacro( "Open(): Database is already open." );
144
145
146
    return true;
    }

147
  assert(this->Private->Connection == NULL);
148

149
150
  this->Private->Connection = 
    mysql_real_connect( &this->Private->NullConnection, 
151
                        this->GetHostName(),
152
                        this->GetUser(),
153
154
                        this->GetPassword(), 
                        this->GetDatabaseName(),
David Thompson's avatar
David Thompson committed
155
                        this->GetServerPort(),
156
157
                        0, 0);
                                        
158
  if (this->Private->Connection == NULL)
159
    {
160
    vtkErrorMacro(<<"Open() failed with error: " 
161
                  << mysql_error(& this->Private->NullConnection));
162
163
164
165
166
167
168
169
170
171
    return false;
    }
  else
    {
    vtkDebugMacro(<<"Open() succeeded.");
    return true;
    }
}

// ----------------------------------------------------------------------
172
void vtkMySQLDatabase::Close()
173
174
175
176
177
178
179
{
  if (! this->IsOpen())
    {
    return; // not an error
    }
  else
    {
180
181
    mysql_close(this->Private->Connection);
    this->Private->Connection = NULL;
182
183
184
185
    }
}

// ----------------------------------------------------------------------
186
bool vtkMySQLDatabase::IsOpen()
187
{
188
  return (this->Private->Connection != NULL);
189
190
191
}

// ----------------------------------------------------------------------
192
vtkSQLQuery* vtkMySQLDatabase::GetQueryInstance()
193
{
194
  vtkMySQLQuery* query = vtkMySQLQuery::New();
195
196
197
198
199
  query->SetDatabase(this);
  return query;
}

// ----------------------------------------------------------------------
200
vtkStringArray* vtkMySQLDatabase::GetTables()
201
202
{
  this->Tables->Resize(0);
203
  if ( ! this->IsOpen() )
204
205
206
207
208
209
    {
    vtkErrorMacro(<<"GetTables(): Database is closed!");
    return this->Tables;
    }
  else
    {
210
    MYSQL_RES* tableResult = mysql_list_tables(
211
      this->Private->Connection, NULL );
212

213
    if ( ! tableResult )
214
215
      {
      vtkErrorMacro(<<"GetTables(): MySQL returned error: "
216
                    << mysql_error(this->Private->Connection));
217
218
219
220
221
222
      return this->Tables;
      }

    MYSQL_ROW row;
    int i=0;

223
    while ( tableResult )
224
      {
225
226
227
      mysql_data_seek( tableResult, i );
      row = mysql_fetch_row( tableResult );
      if ( ! row )
228
229
230
231
        {
        break;
        }

232
233
      this->Tables->InsertNextValue( row[0] );
      ++ i;
234
      }
235
      // Done with processing so free it
236
      mysql_free_result( tableResult );
237
238
239
240
241
242

    return this->Tables;
    }
}

// ----------------------------------------------------------------------
243
vtkStringArray* vtkMySQLDatabase::GetRecord(const char *table)
244
245
246
247
248
249
250
251
252
253
{
  vtkStringArray *results = vtkStringArray::New();

  if (!this->IsOpen())
    {
    vtkErrorMacro(<<"GetRecord: Database is not open!");
    return results;
    }

  MYSQL_RES *record = 
254
    mysql_list_fields(this->Private->Connection, table, 0);
255
256
257
258

  if (!record)
    {
    vtkErrorMacro(<<"GetRecord: MySQL returned error: "
259
                  << mysql_error(this->Private->Connection));
260
261
262
263
264
265
266
267
268
269
270
271
272
    return results;
    }

  MYSQL_FIELD *field;
  while ((field = mysql_fetch_field(record)))
    {
    results->InsertNextValue(field->name);
    }
  
  mysql_free_result(record);
  return results;
}

273
274
275

bool vtkMySQLDatabase::HasError()
{ 
276
  return (mysql_errno(this->Private->Connection)!=0);
277
278
279
280
}

const char* vtkMySQLDatabase::GetLastErrorText()
{
281
  return mysql_error(this->Private->Connection);
282
283
284
285
286
287
288
289
}

// ----------------------------------------------------------------------
vtkStdString vtkMySQLDatabase::GetURL()
{
  vtkStdString url;
  url = this->GetDatabaseType();
  url += "://";
290
  if ( this->GetUser() && strlen( this->GetUser() ) )
291
    {
292
    url += this->GetUser();
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
    if ( this->GetPassword() && strlen( this->GetPassword() ) )
      {
      url += ":";
      url += this->GetPassword();
      }
    url += "@";
    }
  if ( this->GetHostName() && strlen( this->GetHostName() ) )
    {
    url += this->GetHostName();
    }
  else
    {
    url += "localhost";
    }
  if (
    this->GetServerPort() >= 0 &&
    this->GetServerPort() != VTK_MYSQL_DEFAULT_PORT
    )
    {
    url += ":";
    url += this->GetServerPort();
    }
316
317
318
319
320
  url += "/";
  url += this->GetDatabaseName();
  return url;
}

321
322
323
324
325
326
// ----------------------------------------------------------------------
vtkStdString vtkMySQLDatabase::GetColumnSpecification( vtkSQLDatabaseSchema* schema,
                                                       int tblHandle,
                                                       int colHandle )
{
  // With MySQL, the column name must be enclosed between backquotes
327
328
  vtksys_ios::ostringstream queryStr;
  queryStr << "`" << schema->GetColumnNameFromHandle( tblHandle, colHandle ) << "` ";
329

330
  // Figure out column type
331
  int colType = schema->GetColumnTypeFromHandle( tblHandle, colHandle ); 
332
  vtkStdString colTypeStr;
333
334
335

  switch ( static_cast<vtkSQLDatabaseSchema::DatabaseColumnType>( colType ) )
    {
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
    case vtkSQLDatabaseSchema::SERIAL:    
      colTypeStr = "INT NOT NULL AUTO_INCREMENT";
      break;
    case vtkSQLDatabaseSchema::SMALLINT:  
      colTypeStr = "SMALLINT";
      break;
    case vtkSQLDatabaseSchema::INTEGER:   
      colTypeStr = "INT";
      break;
    case vtkSQLDatabaseSchema::BIGINT:    
      colTypeStr = "BIGINT";
      break;
    case vtkSQLDatabaseSchema::VARCHAR:   
      colTypeStr = "VARCHAR";
      break;
    case vtkSQLDatabaseSchema::TEXT:      
352
      colTypeStr = "TEXT";
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
      break;
    case vtkSQLDatabaseSchema::REAL:      
      colTypeStr = "FLOAT";
      break;
    case vtkSQLDatabaseSchema::DOUBLE:    
      colTypeStr = "DOUBLE PRECISION";
      break;
    case vtkSQLDatabaseSchema::BLOB:      
      colTypeStr = "BLOB";
      break;
    case vtkSQLDatabaseSchema::TIME:      
      colTypeStr = "TIME";
      break;
    case vtkSQLDatabaseSchema::DATE:      
      colTypeStr = "DATE";
      break;
    case vtkSQLDatabaseSchema::TIMESTAMP: 
      colTypeStr = "TIMESTAMP";
      break;
372
    }
373

374
  if ( colTypeStr.size() )
375
    {
376
    queryStr << " " << colTypeStr;
377
    }
378
  else // if ( colTypeStr.size() )
379
380
    {
    vtkGenericWarningMacro( "Unable to get column specification: unsupported data type " << colType );
381
    return vtkStdString();
382
383
    }
  
384
385
386
387
388
  // Decide whether size is allowed, required, or unused
  int colSizeType = 0;

  switch ( static_cast<vtkSQLDatabaseSchema::DatabaseColumnType>( colType ) )
    {
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
    case vtkSQLDatabaseSchema::SERIAL:    
      colSizeType =  0;
      break;
    case vtkSQLDatabaseSchema::SMALLINT:  
      colSizeType =  1;
      break;
    case vtkSQLDatabaseSchema::INTEGER:   
      colSizeType =  1;
      break;
    case vtkSQLDatabaseSchema::BIGINT:    
      colSizeType =  1;
      break;
    case vtkSQLDatabaseSchema::VARCHAR:   
      colSizeType = -1;
      break;
    case vtkSQLDatabaseSchema::TEXT:      
405
      colSizeType =  1;
406
407
      break;
    case vtkSQLDatabaseSchema::REAL:      
408
      colSizeType =  0; // Eventually will make DB schemata handle (M,D) sizes
409
410
      break;
    case vtkSQLDatabaseSchema::DOUBLE:    
411
      colSizeType =  0; // Eventually will make DB schemata handle (M,D) sizes
412
413
414
415
416
417
418
419
420
421
422
423
424
      break;
    case vtkSQLDatabaseSchema::BLOB:      
      colSizeType =  1;
      break;
    case vtkSQLDatabaseSchema::TIME:      
      colSizeType =  0;
      break;
    case vtkSQLDatabaseSchema::DATE:      
      colSizeType =  0;
      break;
    case vtkSQLDatabaseSchema::TIMESTAMP: 
      colSizeType =  0;
      break;
425
    }
David Thompson's avatar
David Thompson committed
426

427
428
429
430
431
432
433
434
435
436
437
438
439
440
  // Specify size if allowed or required
  if ( colSizeType )
    {
    int colSize = schema->GetColumnSizeFromHandle( tblHandle, colHandle );
    // IF size is provided but absurd, 
    // OR, if size is required but not provided OR absurd,
    // THEN assign the default size.
    if ( ( colSize < 0 ) || ( colSizeType == -1 && colSize < 1 ) )
      {
      colSize = VTK_SQL_DEFAULT_COLUMN_SIZE;
      }
    
    // At this point, we have either a valid size if required, or a possibly null valid size
    // if not required. Thus, skip sizing in the latter case.
441
    if ( colSize > 0 )
442
      {
443
      queryStr << "(" << colSize << ")";
444
445
446
      }
    }

447
  vtkStdString attStr = schema->GetColumnAttributesFromHandle( tblHandle, colHandle );
448
  if ( attStr.size() )
449
    {
450
    queryStr << " " << attStr;
451
452
    }

453
  return queryStr.str();
454
455
}

456
457
458
// ----------------------------------------------------------------------
vtkStdString vtkMySQLDatabase::GetIndexSpecification( vtkSQLDatabaseSchema* schema,
                                                      int tblHandle,
459
460
                                                      int idxHandle,
                                                      bool& skipped )
461
{
462
  skipped = false;
463
  vtkStdString queryStr = ", ";
464
  bool mustUseName = true;
465
466
467
468
469
470

  int idxType = schema->GetIndexTypeFromHandle( tblHandle, idxHandle );
  switch ( idxType )
    {
    case vtkSQLDatabaseSchema::PRIMARY_KEY:
      queryStr += "PRIMARY KEY ";
471
      mustUseName = false;
472
473
474
475
476
477
478
479
      break;
    case vtkSQLDatabaseSchema::UNIQUE:
      queryStr += "UNIQUE ";
      break;
    case vtkSQLDatabaseSchema::INDEX:
      queryStr += "INDEX ";
      break;
    default:
480
      return vtkStdString();
481
482
    }
  
483
484
485
486
487
  // No index_name for PRIMARY KEYs
  if ( mustUseName )
    {
    queryStr += schema->GetIndexNameFromHandle( tblHandle, idxHandle );
    }
488
489
490
491
492
493
494
  queryStr += " (";
        
  // Loop over all column names of the index
  int numCnm = schema->GetNumberOfColumnNamesInIndex( tblHandle, idxHandle );
  if ( numCnm < 0 )
    {
    vtkGenericWarningMacro( "Unable to get index specification: index has incorrect number of columns " << numCnm );
495
    return vtkStdString();
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
    }

  bool firstCnm = true;
  for ( int cnmHandle = 0; cnmHandle < numCnm; ++ cnmHandle )
    {
    if ( firstCnm )
      {
      firstCnm = false;
      }
    else
      {
      queryStr += ",";
      }
    // With MySQL, the column name must be enclosed between backquotes
    queryStr += "`";
    queryStr += schema->GetIndexColumnNameFromHandle( tblHandle, idxHandle, cnmHandle );
    queryStr += "` ";
    }
  queryStr += ")";

  return queryStr;
}