vtkSQLDatabase.cxx 13.5 KB
Newer Older
1
2
/*=========================================================================

3
4
Program:   Visualization Toolkit
Module:    vtkSQLDatabase.cxx
5

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

10
11
12
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.
13
14
15

=========================================================================*/
/*----------------------------------------------------------------------------
16
17
18
  Copyright (c) Sandia Corporation
  See Copyright.txt or http://www.paraview.org/HTML/Copyright.html for details.
  ----------------------------------------------------------------------------*/
19

20
#include "vtkToolkits.h"
21
#include "vtkSQLDatabase.h"
22
23
24
25
#include "vtkSQLQuery.h"

#include "vtkSQLDatabaseSchema.h"

26
#include "vtkSQLiteDatabase.h"
27

28
#ifdef VTK_USE_POSTGRES
29
#include "vtkPostgreSQLDatabase.h"
30
#endif // VTK_USE_POSTGRES
31
32

#ifdef VTK_USE_MYSQL
33
#include "vtkMySQLDatabase.h"
34
#endif // VTK_USE_MYSQL
35
36

#include "vtkObjectFactory.h"
37
#include "vtkStdString.h"
38

39
#include <vtksys/SystemTools.hxx>
40
#include <vtksys/ios/sstream>
41

42
vtkCxxRevisionMacro(vtkSQLDatabase, "1.35");
43
44

// ----------------------------------------------------------------------
45
46
47
48
vtkSQLDatabase::vtkSQLDatabase()
{
}

49
// ----------------------------------------------------------------------
50
51
52
53
vtkSQLDatabase::~vtkSQLDatabase()
{
}

54
// ----------------------------------------------------------------------
55
56
57
58
59
void vtkSQLDatabase::PrintSelf(ostream &os, vtkIndent indent)
{
  this->Superclass::PrintSelf(os, indent);
}

60
61
62
63
64
// ----------------------------------------------------------------------
vtkStdString vtkSQLDatabase::GetColumnSpecification( vtkSQLDatabaseSchema* schema,
                                                     int tblHandle,
                                                     int colHandle )
{
65
66
  vtksys_ios::ostringstream queryStr;
  queryStr << schema->GetColumnNameFromHandle( tblHandle, colHandle );
67

68
  // Figure out column type
69
  int colType = schema->GetColumnTypeFromHandle( tblHandle, colHandle ); 
70
  vtkStdString colTypeStr;
71
72
  switch ( static_cast<vtkSQLDatabaseSchema::DatabaseColumnType>( colType ) )
    {
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
    case vtkSQLDatabaseSchema::SERIAL:    
      colTypeStr = "";
      break;
    case vtkSQLDatabaseSchema::SMALLINT:  
      colTypeStr = "INTEGER";
      break;
    case vtkSQLDatabaseSchema::INTEGER:   
      colTypeStr = "INTEGER";
      break;
    case vtkSQLDatabaseSchema::BIGINT:    
      colTypeStr = "INTEGER";
      break;
    case vtkSQLDatabaseSchema::VARCHAR:   
      colTypeStr = "VARCHAR";
      break;
    case vtkSQLDatabaseSchema::TEXT:      
      colTypeStr = "VARCHAR";
      break;
    case vtkSQLDatabaseSchema::REAL:      
      colTypeStr = "FLOAT";
      break;
    case vtkSQLDatabaseSchema::DOUBLE:    
      colTypeStr = "DOUBLE";
      break;
    case vtkSQLDatabaseSchema::BLOB:      
      colTypeStr = "";
      break;
    case vtkSQLDatabaseSchema::TIME:      
      colTypeStr = "TIME";
      break;
    case vtkSQLDatabaseSchema::DATE:      
      colTypeStr = "DATE";
      break;
    case vtkSQLDatabaseSchema::TIMESTAMP: 
      colTypeStr = "TIMESTAMP";
      break;
109
110
    }
  
111
  if ( colTypeStr.size() )
112
    {
113
    queryStr << " " << colTypeStr;
114
    }
115
  else // if ( colTypeStr.size() )
116
117
    {
    vtkGenericWarningMacro( "Unable to get column specification: unsupported data type " << colType );
118
    return vtkStdString();
119
120
    }
  
121
122
123
124
  // Decide whether size is allowed, required, or unused
  int colSizeType = 0;
  switch ( static_cast<vtkSQLDatabaseSchema::DatabaseColumnType>( colType ) )
    {
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
    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:      
      colSizeType = -1;
      break;
    case vtkSQLDatabaseSchema::REAL:      
144
      colSizeType =  0;
145
146
      break;
    case vtkSQLDatabaseSchema::DOUBLE:    
147
      colSizeType =  0;
148
149
      break;
    case vtkSQLDatabaseSchema::BLOB:      
150
      colSizeType =  0;
151
152
153
154
155
156
157
158
159
160
      break;
    case vtkSQLDatabaseSchema::TIME:      
      colSizeType =  0;
      break;
    case vtkSQLDatabaseSchema::DATE:      
      colSizeType =  0;
      break;
    case vtkSQLDatabaseSchema::TIMESTAMP: 
      colSizeType =  0;
      break;
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
    }

  // 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.
177
    if ( colSize > 0 )
178
      {
179
      queryStr << "(" << colSize << ")";
180
181
182
      }
    }

183
  vtkStdString attStr = schema->GetColumnAttributesFromHandle( tblHandle, colHandle );
184
  if ( attStr.size() )
185
    {
186
    queryStr << " " << attStr;
187
188
    }

189
  return queryStr.str();
190
191
}

192
193
194
// ----------------------------------------------------------------------
vtkStdString vtkSQLDatabase::GetIndexSpecification( vtkSQLDatabaseSchema* schema,
                                                    int tblHandle,
195
196
                                                    int idxHandle,
                                                    bool& skipped )
197
{
198
  vtkStdString queryStr;
199
200
201
202
203

  int idxType = schema->GetIndexTypeFromHandle( tblHandle, idxHandle );
  switch ( idxType )
    {
    case vtkSQLDatabaseSchema::PRIMARY_KEY:
204
205
      queryStr = ", PRIMARY KEY ";
      skipped = false;
206
      break;
207
    case vtkSQLDatabaseSchema::UNIQUE:
208
209
210
211
212
213
214
215
      queryStr = ", UNIQUE ";
      skipped = false;
      break;
    case vtkSQLDatabaseSchema::INDEX:
      // Not supported within a CREATE TABLE statement by all SQL backends: 
      // must be created later with a CREATE INDEX statement
      queryStr = "CREATE INDEX ";
      skipped = true;
216
217
      break;
    default:
218
      return vtkStdString();
219
220
    }
  
221
222
223
224
225
  // No index_name for PRIMARY KEYs nor UNIQUEs
  if ( skipped )
    {
    queryStr += schema->GetIndexNameFromHandle( tblHandle, idxHandle );
    }
226
227
228
229
230
231
232
233

  // CREATE INDEX <index name> ON <table name> syntax
  if ( skipped )
    {
    queryStr += " ON ";
    queryStr += schema->GetTableNameFromHandle( tblHandle );
    }

234
235
236
237
238
239
240
  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 );
241
    return vtkStdString();
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
    }

  bool firstCnm = true;
  for ( int cnmHandle = 0; cnmHandle < numCnm; ++ cnmHandle )
    {
    if ( firstCnm )
      {
      firstCnm = false;
      }
    else
      {
      queryStr += ",";
      }
    queryStr += schema->GetIndexColumnNameFromHandle( tblHandle, idxHandle, cnmHandle );
    }
  queryStr += ")";

  return queryStr;
}

262
// ----------------------------------------------------------------------
263
264
vtkSQLDatabase* vtkSQLDatabase::CreateFromURL( const char* URL )
{
265
  vtkstd::string protocol;
266
267
268
269
270
  vtkstd::string username; 
  vtkstd::string password;
  vtkstd::string hostname; 
  vtkstd::string dataport; 
  vtkstd::string database;
271
  vtkstd::string dataglom;
272
273
  vtkSQLDatabase* db = 0;
  
274
  // SQLite is a bit special so lets get that out of the way :)
275
  if ( ! vtksys::SystemTools::ParseURLProtocol( URL, protocol, dataglom))
276
277
278
279
280
281
    {
    vtkGenericWarningMacro( "Invalid URL: " << URL );
    return 0;
    }
  if ( protocol == "sqlite" )
    {
282
    db = vtkSQLiteDatabase::New();
283
284
285
286
287
288
289
    vtkSQLiteDatabase *sqlite_db = vtkSQLiteDatabase::SafeDownCast(db);
    sqlite_db->SetDatabaseFileName(dataglom.c_str());
    return db;
    }
    
  // Okay now for all the other database types get more detailed info
  if ( ! vtksys::SystemTools::ParseURL( URL, protocol, username,
290
                                        password, hostname, dataport, database) )
291
292
293
    {
    vtkGenericWarningMacro( "Invalid URL: " << URL );
    return 0;
294
    }
295
  
296
#ifdef VTK_USE_POSTGRES
297
  if ( protocol == "psql" )
298
299
    {
    db = vtkPostgreSQLDatabase::New();
300
    vtkPostgreSQLDatabase *post_db = vtkPostgreSQLDatabase::SafeDownCast(db);
301
    post_db->SetUser(username.c_str());
302
303
    post_db->SetPassword(password.c_str());
    post_db->SetHostName(hostname.c_str());
304
    post_db->SetServerPort(atoi(dataport.c_str()));
305
    post_db->SetDatabaseName(database.c_str());
306
    return db;
307
    }
308
#endif // VTK_USE_POSTGRES
309
#ifdef VTK_USE_MYSQL
310
  if ( protocol == "mysql" )
311
312
    {
    db = vtkMySQLDatabase::New();
313
    vtkMySQLDatabase *mysql_db = vtkMySQLDatabase::SafeDownCast(db);
314
315
    if ( username.size() )
      {
316
      mysql_db->SetUser(username.c_str());
317
318
319
320
321
322
323
324
325
      }
    if ( password.size() )
      {
      mysql_db->SetPassword(password.c_str());
      }
    if ( dataport.size() )
      {
      mysql_db->SetServerPort(atoi(dataport.c_str()));
      }
326
327
    mysql_db->SetHostName(hostname.c_str());
    mysql_db->SetDatabaseName(database.c_str());
328
    return db;
329
    }
330
#endif // VTK_USE_MYSQL
331

332
  vtkGenericWarningMacro( "Unsupported protocol: " << protocol.c_str() );
333
334
335
  return db;
}

336
// ----------------------------------------------------------------------
337
bool vtkSQLDatabase::EffectSchema( vtkSQLDatabaseSchema* schema, bool dropIfExists )
338
339
340
341
342
343
{
  if ( ! this->IsOpen() )
    {
    vtkGenericWarningMacro( "Unable to effect the schema: no database is open" );
    return false;
    }
344

345
346
347
348
349
350
351
352
  // Instantiate an empty query and begin the transaction.
  vtkSQLQuery* query = this->GetQueryInstance();
  if ( ! query->BeginTransaction() )
    {
    vtkGenericWarningMacro( "Unable to effect the schema: unable to begin transaction" );
    return false;
    }
 
353
354
355
  // In case INDEX indices are encountered in the schema
  vtkstd::vector<vtkStdString> idxStatements;

356
357
358
359
360
361
  // Loop over all tables of the schema and create them
  int numTbl = schema->GetNumberOfTables();
  for ( int tblHandle = 0; tblHandle < numTbl; ++ tblHandle )
    {
    // Construct the query string for this table
    vtkStdString queryStr( "CREATE TABLE " );
362
    queryStr += this->GetTablePreamble( dropIfExists );
363
    queryStr += schema->GetTableNameFromHandle( tblHandle );
364
365
366
367
    queryStr += " (";

    // Loop over all columns of the current table
    int numCol = schema->GetNumberOfColumnsInTable( tblHandle );
368
    if ( numCol < 0 )
369
      {
370
      query->RollbackTransaction();
Philippe Pebay's avatar
Philippe Pebay committed
371
      query->Delete();
372
373
      return false;
      }
374
375

    bool firstCol = true;
376
377
378
379
380
381
    for ( int colHandle = 0; colHandle < numCol; ++ colHandle )
      {
      if ( ! firstCol )
        {
        queryStr += ", ";
        }
382
      else // ( ! firstCol )
383
384
385
        {
        firstCol = false;
        }
386

387
388
      // Get column creation syntax (backend-dependent)
      vtkStdString colStr = this->GetColumnSpecification( schema, tblHandle, colHandle );
389
      if ( colStr.size() )
390
391
392
        {
        queryStr += colStr;
        }
393
      else // if ( colStr.size() )
394
395
        {
        query->RollbackTransaction();
Philippe Pebay's avatar
Philippe Pebay committed
396
        query->Delete();
397
398
        return false;
        }
399
      }
400
401

    // Loop over all indices of the current table
402
    bool skipped = false;
403
404
405
    int numIdx = schema->GetNumberOfIndicesInTable( tblHandle );
    if ( numIdx < 0 )
      {
406
      query->RollbackTransaction();
Philippe Pebay's avatar
Philippe Pebay committed
407
      query->Delete();
408
409
410
411
      return false;
      }
    for ( int idxHandle = 0; idxHandle < numIdx; ++ idxHandle )
      {
412
      // Get index creation syntax (backend-dependent)
413
      vtkStdString idxStr = this->GetIndexSpecification( schema, tblHandle, idxHandle, skipped );
414
      if ( idxStr.size() )
415
        {
416
417
418
419
420
421
422
423
424
425
        if ( skipped )
          {
          // Must create this index later
          idxStatements.push_back( idxStr );
          continue;
          }
        else // if ( skipped )
          {
          queryStr += idxStr;
          }
426
        }
427
      else // if ( idxStr.size() )
428
        {
429
        query->RollbackTransaction();
Philippe Pebay's avatar
Philippe Pebay committed
430
        query->Delete();
431
432
433
434
        return false;
        }
      }
    queryStr += ")";
435

436
437
    // Execute the query
    query->SetQuery( queryStr );
438
439
    if ( ! query->Execute() )
      {
440
      vtkGenericWarningMacro( "Unable to effect the schema: unable to execute query.\nDetails: "
441
                              << query->GetLastErrorText() );
442
      query->RollbackTransaction();
Philippe Pebay's avatar
Philippe Pebay committed
443
      query->Delete();
444
445
      return false;
      }
446
    }
447
448
449
450
451
452
453
454
455
456
457

  // Now, execute the CREATE INDEX statement -- if any
  for ( vtkstd::vector<vtkStdString>::iterator it = idxStatements.begin();
        it != idxStatements.end(); ++ it )
    {
    query->SetQuery( *it );
    if ( ! query->Execute() )
      {
      vtkGenericWarningMacro( "Unable to effect the schema: unable to execute query.\nDetails: "
                              << query->GetLastErrorText() );
      query->RollbackTransaction();
Philippe Pebay's avatar
Philippe Pebay committed
458
      query->Delete();
459
460
461
462
      return false;
      }
    }
 
463
  // FIXME: eventually handle triggers
464
465
466
467

  // Commit the transaction.
  if ( ! query->CommitTransaction() )
    {
468
    vtkGenericWarningMacro( "Unable to effect the schema: unable to commit transaction.\nDetails: "
469
                            << query->GetLastErrorText() );
Philippe Pebay's avatar
Philippe Pebay committed
470
    query->Delete();
471
472
473
    return false;
    }

Philippe Pebay's avatar
Philippe Pebay committed
474
  query->Delete();
475
476
  return true;
}
477