1 /*========================================================================= 2 3 Program: Visualization Toolkit 4 Module: vtkSQLDatabaseSchema.h 5 6 Copyright (c) Ken Martin, Will Schroeder, Bill Lorensen 7 All rights reserved. 8 See Copyright.txt or http://www.kitware.com/Copyright.htm for details. 9 10 This software is distributed WITHOUT ANY WARRANTY; without even 11 the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR 12 PURPOSE. See the above copyright notice for more information. 13 14 =========================================================================*/ 15 /*------------------------------------------------------------------------- 16 Copyright 2008 Sandia Corporation. 17 Under the terms of Contract DE-AC04-94AL85000 with Sandia Corporation, 18 the U.S. Government retains certain rights in this software. 19 -------------------------------------------------------------------------*/ 20 /** 21 * @class vtkSQLDatabaseSchema 22 * @brief represent an SQL database schema 23 * 24 * 25 * This class stores the information required to create 26 * an SQL database from scratch. 27 * Information on each table's columns, indices, and triggers is stored. 28 * You may also store an arbitrary number of preamble statements, intended 29 * to be executed before any tables are created; 30 * this provides a way to create procedures or functions that may be 31 * invoked as part of a trigger action. 32 * Triggers and table options may be specified differently for each backend 33 * database type you wish to support. 34 * 35 * @par Thanks: 36 * Thanks to Philippe Pebay and David Thompson from Sandia National 37 * Laboratories for implementing this class. 38 * 39 * @sa 40 * vtkSQLDatabase 41 */ 42 43 #ifndef vtkSQLDatabaseSchema_h 44 #define vtkSQLDatabaseSchema_h 45 46 #include "vtkIOSQLModule.h" // For export macro 47 #include "vtkObject.h" 48 49 #include <cstdarg> // Because one method has a variable list of arguments 50 51 // This is a list of known supported VTK SQL backend classes. 52 // A particular SQL backend does not have to be listed here to be supported, but 53 // these macros allow for the specification of SQL backend-specific database schema items. 54 #define VTK_SQL_ALLBACKENDS "*" // works for all backends 55 #define VTK_SQL_MYSQL "vtkMySQLDatabase" 56 #define VTK_SQL_POSTGRESQL "vtkPostgreSQLDatabase" 57 #define VTK_SQL_SQLITE "vtkSQLiteDatabase" 58 59 class vtkSQLDatabaseSchemaInternals; 60 61 class VTKIOSQL_EXPORT vtkSQLDatabaseSchema : public vtkObject 62 { 63 public: 64 vtkTypeMacro(vtkSQLDatabaseSchema, vtkObject); 65 void PrintSelf(ostream& os, vtkIndent indent) override; 66 static vtkSQLDatabaseSchema* New(); 67 68 /** 69 * Basic data types for database columns 70 */ 71 enum DatabaseColumnType 72 { 73 SERIAL = 0, // specifying the indices explicitly to prevent bad compiler mishaps 74 SMALLINT = 1, 75 INTEGER = 2, 76 BIGINT = 3, 77 VARCHAR = 4, 78 TEXT = 5, 79 REAL = 6, 80 DOUBLE = 7, 81 BLOB = 8, 82 TIME = 9, 83 DATE = 10, 84 TIMESTAMP = 11 85 }; 86 87 /** 88 * Types of indices that can be generated for database tables 89 */ 90 enum DatabaseIndexType 91 { 92 INDEX = 0, // Non-unique index of values in named columns 93 UNIQUE = 1, // Index of values in named columns required to have at most one entry per pair of valid values. 94 PRIMARY_KEY = 2 // Like UNIQUE but additionally this serves as the primary key for the table to speed up insertions. 95 }; 96 97 /** 98 * Events where database triggers can be registered. 99 */ 100 enum DatabaseTriggerType 101 { 102 BEFORE_INSERT = 0, // Just before a row is inserted 103 AFTER_INSERT = 1, // Just after a row is inserted 104 BEFORE_UPDATE = 2, // Just before a row's values are changed 105 AFTER_UPDATE = 3, // Just after a row's values are changed 106 BEFORE_DELETE = 4, // Just before a row is deleted 107 AFTER_DELETE = 5 // Just after a row is deleted 108 }; 109 110 /** 111 * Add a preamble to the schema 112 * This can be used, in particular, to create functions and/or 113 * load languages in a backend-specific manner. 114 * Example usage: 115 * vtkSQLDatabaseSchema* schema = vtkSQLDatabaseSchema::New(); 116 * schema->SetName( "Example" ); 117 * schema->AddPreamble( "dropPLPGSQL", "DROP LANGUAGE IF EXISTS PLPGSQL CASCADE", VTK_SQL_POSTGRESQL ); 118 * schema->AddPreamble( "loadPLPGSQL", "CREATE LANGUAGE PLPGSQL", VTK_SQL_POSTGRESQL ); 119 * schema->AddPreamble( "createsomefunction", 120 * "CREATE OR REPLACE FUNCTION somefunction() RETURNS TRIGGER AS $btable$ " 121 * "BEGIN " 122 * "INSERT INTO btable (somevalue) VALUES (NEW.somenmbr); " 123 * "RETURN NEW; " 124 * "END; $btable$ LANGUAGE PLPGSQL", 125 * VTK_SQL_POSTGRESQL ); 126 */ 127 virtual int AddPreamble( 128 const char* preName, const char* preAction, 129 const char* preBackend = VTK_SQL_ALLBACKENDS ); 130 131 /** 132 * Add a table to the schema 133 */ 134 virtual int AddTable( const char* tblName ); 135 136 //@{ 137 /** 138 * Add a column to table. 139 140 * The returned value is a column handle or -1 if an error occurred. 141 */ 142 virtual int AddColumnToTable( 143 int tblHandle, int colType, const char* colName, 144 int colSize, const char* colAttribs ); AddColumnToTable(const char * tblName,int colType,const char * colName,int colSize,const char * colAttribs)145 virtual int AddColumnToTable( 146 const char* tblName, int colType, const char* colName, 147 int colSize, const char* colAttribs ) 148 { 149 return this->AddColumnToTable( this->GetTableHandleFromName( tblName ), 150 colType, colName, colSize, colAttribs ); 151 } 152 //@} 153 154 //@{ 155 /** 156 * Add an index to table. 157 158 * The returned value is an index handle or -1 if an error occurred. 159 */ 160 virtual int AddIndexToTable( 161 int tblHandle, int idxType, const char* idxName ); AddIndexToTable(const char * tblName,int idxType,const char * idxName)162 virtual int AddIndexToTable( 163 const char* tblName, int idxType, const char* idxName ) 164 { 165 return this->AddIndexToTable( this->GetTableHandleFromName( tblName ), 166 idxType, idxName ); 167 } 168 //@} 169 170 //@{ 171 /** 172 * Add a column to a table index. 173 174 * The returned value is an index-column handle or -1 if an error occurred. 175 */ 176 virtual int AddColumnToIndex( int tblHandle, int idxHandle, int colHandle ); AddColumnToIndex(const char * tblName,const char * idxName,const char * colName)177 virtual int AddColumnToIndex( 178 const char* tblName, const char* idxName, const char* colName ) 179 { 180 int tblHandle = this->GetTableHandleFromName( tblName ); 181 return this->AddColumnToIndex( tblHandle, 182 this->GetIndexHandleFromName( tblName, idxName ), 183 this->GetColumnHandleFromName( tblName, colName ) ); 184 } 185 //@} 186 187 //@{ 188 /** 189 * Add a (possibly backend-specific) trigger action to a table. 190 191 * Triggers must be given unique, non-nullptr names as some database backends require them. 192 * The returned value is a trigger handle or -1 if an error occurred. 193 */ 194 virtual int AddTriggerToTable( 195 int tblHandle, int trgType, const char* trgName, 196 const char* trgAction, const char* trgBackend = VTK_SQL_ALLBACKENDS ); 197 virtual int AddTriggerToTable( 198 const char* tblName, int trgType, const char* trgName, 199 const char* trgAction, const char* trgBackend = VTK_SQL_ALLBACKENDS ) 200 { 201 return this->AddTriggerToTable( this->GetTableHandleFromName( tblName ), 202 trgType, trgName, trgAction, trgBackend ); 203 } 204 //@} 205 206 //@{ 207 /** 208 * Add (possibly backend-specific) text to the end of a 209 * CREATE TABLE (...) statement. 210 211 * This is most useful for specifying storage semantics of tables 212 * that are specific to the backend. For example, table options 213 * can be used to specify the TABLESPACE of a PostgreSQL table or 214 * the ENGINE of a MySQL table. 215 216 * The returned value is an option handle or -1 if an error occurred. 217 */ 218 virtual int AddOptionToTable( 219 int tblHandle, const char* optStr, 220 const char* optBackend = VTK_SQL_ALLBACKENDS ); 221 virtual int AddOptionToTable( 222 const char* tblName, const char* optStr, 223 const char* optBackend = VTK_SQL_ALLBACKENDS ) 224 { 225 return this->AddOptionToTable( this->GetTableHandleFromName( tblName ), 226 optStr, optBackend ); 227 } 228 //@} 229 230 /** 231 * Given a preamble name, get its handle. 232 */ 233 int GetPreambleHandleFromName( const char* preName ); 234 235 /** 236 * Given a preamble handle, get its name. 237 */ 238 const char* GetPreambleNameFromHandle( int preHandle ); 239 240 /** 241 * Given a preamble handle, get its action. 242 */ 243 const char* GetPreambleActionFromHandle( int preHandle ); 244 245 /** 246 * Given a preamble handle, get its backend. 247 */ 248 const char* GetPreambleBackendFromHandle( int preHandle ); 249 250 /** 251 * Given a table name, get its handle. 252 */ 253 int GetTableHandleFromName( const char* tblName ); 254 255 /** 256 * Given a table hanlde, get its name. 257 */ 258 const char* GetTableNameFromHandle( int tblHandle ); 259 260 /** 261 * Given the names of a table and an index, get the handle of the index in this table. 262 */ 263 int GetIndexHandleFromName( const char* tblName, const char* idxName ); 264 265 /** 266 * Given the handles of a table and an index, get the name of the index. 267 */ 268 const char* GetIndexNameFromHandle( int tblHandle, int idxHandle ); 269 270 /** 271 * Given the handles of a table and an index, get the type of the index. 272 */ 273 int GetIndexTypeFromHandle( int tblHandle, int idxHandle ); 274 275 /** 276 * Given the handles of a table, an index, and a column name, get the column name. 277 */ 278 const char* GetIndexColumnNameFromHandle( 279 int tblHandle, int idxHandle, int cnmHandle ); 280 281 /** 282 * Given the names of a table and a column, get the handle of the column in this table. 283 */ 284 int GetColumnHandleFromName( const char* tblName, const char* colName ); 285 286 /** 287 * Given the handles of a table and a column, get the name of the column. 288 */ 289 const char* GetColumnNameFromHandle( int tblHandle, int colHandle ); 290 291 /** 292 * Given the handles of a table and a column, get the type of the column. 293 */ 294 int GetColumnTypeFromHandle( int tblHandle, int colHandle ); 295 296 /** 297 * Given the handles of a table and a column, get the size of the column. 298 */ 299 int GetColumnSizeFromHandle( int tblHandle, int colHandle ); 300 301 /** 302 * Given the handles of a table and a column, get the attributes of the column. 303 */ 304 const char* GetColumnAttributesFromHandle( int tblHandle, int colHandle ); 305 306 /** 307 * Given the names of a trigger and a table, get the handle of the trigger in this table. 308 */ 309 int GetTriggerHandleFromName( const char* tblName, const char* trgName ); 310 311 /** 312 * Given the handles of a table and a trigger, get the name of the trigger. 313 */ 314 const char* GetTriggerNameFromHandle( int tblHandle, int trgHandle ); 315 316 /** 317 * Given the handles of a table and a trigger, get the type of the trigger. 318 */ 319 int GetTriggerTypeFromHandle( int tblHandle, int trgHandle ); 320 321 /** 322 * Given the handles of a table and a trigger, get the action of the trigger. 323 */ 324 const char* GetTriggerActionFromHandle( int tblHandle, int trgHandle ); 325 326 /** 327 * Given the handles of a table and a trigger, get the backend of the trigger. 328 */ 329 const char* GetTriggerBackendFromHandle( int tblHandle, int trgHandle ); 330 331 /** 332 * Given the handles of a table and one of its options, return the text of the option. 333 */ 334 const char* GetOptionTextFromHandle( int tblHandle, int optHandle ); 335 336 /** 337 * Given the handles of a table and one of its options, get the backend of the option. 338 */ 339 const char* GetOptionBackendFromHandle( int tblHandle, int trgHandle ); 340 341 /** 342 * Reset the schema to its initial, empty state. 343 */ 344 void Reset(); 345 346 /** 347 * Get the number of preambles. 348 */ 349 int GetNumberOfPreambles(); 350 351 /** 352 * Get the number of tables. 353 */ 354 int GetNumberOfTables(); 355 356 /** 357 * Get the number of columns in a particular table . 358 */ 359 int GetNumberOfColumnsInTable( int tblHandle ); 360 361 /** 362 * Get the number of indices in a particular table . 363 */ 364 int GetNumberOfIndicesInTable( int tblHandle ); 365 366 /** 367 * Get the number of column names associated to a particular index in a particular table . 368 */ 369 int GetNumberOfColumnNamesInIndex( int tblHandle, int idxHandle ); 370 371 /** 372 * Get the number of triggers defined for a particular table. 373 */ 374 int GetNumberOfTriggersInTable( int tblHandle ); 375 376 /** 377 * Get the number of options associated with a particular table. 378 */ 379 int GetNumberOfOptionsInTable( int tblHandle ); 380 381 //@{ 382 /** 383 * Set/Get the name of the schema. 384 */ 385 vtkSetStringMacro(Name); 386 vtkGetStringMacro(Name); 387 //@} 388 389 // Tokens passed to AddTable to indicate the type of data that follows. Random integers chosen to prevent mishaps. 390 enum VarargTokens 391 { 392 COLUMN_TOKEN = 58, 393 INDEX_TOKEN = 63, 394 INDEX_COLUMN_TOKEN = 65, 395 END_INDEX_TOKEN = 75, 396 TRIGGER_TOKEN = 81, 397 OPTION_TOKEN = 86, 398 END_TABLE_TOKEN = 99 399 }; 400 401 /** 402 * An unwrappable but useful routine to construct built-in schema. 403 * Example usage: 404 * int main() 405 * { 406 * vtkSQLDatabaseSchema* schema = vtkSQLDatabaseSchema::New(); 407 * schema->SetName( "Example" ); 408 * schema->AddTableMultipleArguments( "atable", 409 * vtkSQLDatabaseSchema::COLUMN_TOKEN, vtkSQLDatabaseSchema::INTEGER, "tablekey", 0, "", 410 * vtkSQLDatabaseSchema::COLUMN_TOKEN, vtkSQLDatabaseSchema::VARCHAR, "somename", 11, "NOT nullptr", 411 * vtkSQLDatabaseSchema::COLUMN_TOKEN, vtkSQLDatabaseSchema::BIGINT, "somenmbr", 17, "DEFAULT 0", 412 * vtkSQLDatabaseSchema::INDEX_TOKEN, vtkSQLDatabaseSchema::PRIMARY_KEY, "bigkey", 413 * vtkSQLDatabaseSchema::INDEX_COLUMN_TOKEN, "tablekey", 414 * vtkSQLDatabaseSchema::END_INDEX_TOKEN, 415 * vtkSQLDatabaseSchema::INDEX_TOKEN, vtkSQLDatabaseSchema::UNIQUE, "reverselookup", 416 * vtkSQLDatabaseSchema::INDEX_COLUMN_TOKEN, "somename", 417 * vtkSQLDatabaseSchema::INDEX_COLUMN_TOKEN, "somenmbr", 418 * vtkSQLDatabaseSchema::END_INDEX_TOKEN, 419 * vtkSQLDatabaseSchema::TRIGGER_TOKEN, vtkSQLDatabaseSchema::AFTER_INSERT, 420 * "InsertTrigger", "DO NOTHING", VTK_SQL_SQLITE, 421 * vtkSQLDatabaseSchema::TRIGGER_TOKEN, vtkSQLDatabaseSchema::AFTER_INSERT, 422 * "InsertTrigger", "FOR EACH ROW EXECUTE PROCEDURE somefunction ()", VTK_SQL_POSTGRESQL, 423 * vtkSQLDatabaseSchema::TRIGGER_TOKEN, vtkSQLDatabaseSchema::AFTER_INSERT, 424 * "InsertTrigger", "FOR EACH ROW INSERT INTO btable SET SomeValue = NEW.SomeNmbr", VTK_SQL_MYSQL, 425 * vtkSQLDatabaseSchema::END_TABLE_TOKEN 426 * ); 427 * return 0; 428 * } 429 */ 430 int AddTableMultipleArguments( const char* tblName, ... ); 431 432 protected: 433 vtkSQLDatabaseSchema(); 434 ~vtkSQLDatabaseSchema() override; 435 436 char* Name; 437 438 class vtkSQLDatabaseSchemaInternals* Internals; 439 440 private: 441 vtkSQLDatabaseSchema(const vtkSQLDatabaseSchema &) = delete; 442 void operator=(const vtkSQLDatabaseSchema &) = delete; 443 }; 444 445 #endif // vtkSQLDatabaseSchema_h 446