GOFIGURE2  0.9.0
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Groups Pages
QueryDataBaseHelper.cxx
Go to the documentation of this file.
1 /*=========================================================================
2  Authors: The GoFigure Dev. Team.
3  at Megason Lab, Systems biology, Harvard Medical school, 2009-11
4 
5  Copyright (c) 2009-11, President and Fellows of Harvard College.
6  All rights reserved.
7 
8  Redistribution and use in source and binary forms, with or without
9  modification, are permitted provided that the following conditions are met:
10 
11  Redistributions of source code must retain the above copyright notice,
12  this list of conditions and the following disclaimer.
13  Redistributions in binary form must reproduce the above copyright notice,
14  this list of conditions and the following disclaimer in the documentation
15  and/or other materials provided with the distribution.
16  Neither the name of the President and Fellows of Harvard College
17  nor the names of its contributors may be used to endorse or promote
18  products derived from this software without specific prior written
19  permission.
20 
21  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
22  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
23  THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
24  PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
25  BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
26  OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
27  OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
28  OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
29  WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
30  OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
31  ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
32 
33 =========================================================================*/
34 #include "QueryDataBaseHelper.h"
35 #include "vtkMySQLDatabase.h"
36 #include "vtkSQLQuery.h"
37 #include "vtkStdString.h"
38 #include "vtkVariant.h"
39 #include "QueryBuilderHelper.h"
40 #include <sstream>
41 #include <string>
42 
43 //------------------------------------------------------------------------------
44 
45 //------------------------------------------------------------------------------
46 std::pair< bool, vtkMySQLDatabase * > ConnectToServer(std::string ServerName, std::string login,
47  std::string Password)
48 {
49  std::pair< bool, vtkMySQLDatabase * > ConnectionServer;
50  vtkMySQLDatabase * ServerConnector = vtkMySQLDatabase::New();
51  ServerConnector->SetHostName( ServerName.c_str() );
52  ServerConnector->SetUser( login.c_str() );
53  ServerConnector->SetPassword( Password.c_str() );
54 
55  if ( !ServerConnector->Open() )
56  {
57  std::cout << "Could not connect to the server." << std::endl;
58  std::cout << "Debug: In " << __FILE__ << ", line " << __LINE__;
59  std::cout << std::endl;
60  ConnectionServer.first = false;
61  return ConnectionServer;
62  }
63  ConnectionServer.first = true;
64  ConnectionServer.second = ServerConnector;
65  return ConnectionServer;
66 }
67 
68 //------------------------------------------------------------------------------
69 
70 //------------------------------------------------------------------------------
71 std::pair< bool, vtkMySQLDatabase * > ConnectToDatabase(std::string ServerName, std::string login,
72  std::string Password, std::string DBName)
73 {
74  std::pair< bool, vtkMySQLDatabase * > ConnectionDatabase(false, (vtkMySQLDatabase *)0);
75  vtkMySQLDatabase * DatabaseConnector = vtkMySQLDatabase::New();
76  DatabaseConnector->SetHostName( ServerName.c_str() );
77  DatabaseConnector->SetUser( login.c_str() );
78  DatabaseConnector->SetPassword( Password.c_str() );
79  DatabaseConnector->SetDatabaseName( DBName.c_str() );
80  if ( !DatabaseConnector->Open() )
81  {
82  itkGenericExceptionMacro(
83  << "Could not open database."
84  << "DB will not be created.");
85  std::cout << "Could not connect to the database." << std::endl;
86  std::cout << "Debug: In " << __FILE__ << ", line " << __LINE__;
87  std::cout << std::endl;
88  return ConnectionDatabase;
89  }
90 
91  ConnectionDatabase.first = true;
92  ConnectionDatabase.second = DatabaseConnector;
93 
94  return ConnectionDatabase;
95 }
96 
97 //------------------------------------------------------------------------------
98 
99 //------------------------------------------------------------------------------
100 vtkMySQLDatabase * OpenDatabaseConnection(
101  std::string ServerName, std::string login,
102  std::string Password, std::string DBName)
103 {
104  std::pair< bool, vtkMySQLDatabase * > ConnectionDatabase = ConnectToDatabase(
105  ServerName, login, Password, DBName);
106 
107  if ( !ConnectionDatabase.first )
108  {
109  std::cout << "No connection open for QGoOpenOrCreateImgSession" << std::endl;
110  std::cout << "Debug: In " << __FILE__ << ", line " << __LINE__;
111  std::cout << std::endl;
112  }
113 
114  return ConnectionDatabase.second;
115 }
116 
117 //------------------------------------------------------------------------------
118 
119 //------------------------------------------------------------------------------
121  vtkMySQLDatabase *DatabaseConnector)
122 {
123  if ( DatabaseConnector != NULL )
124  {
125  DatabaseConnector->Close();
126  DatabaseConnector->Delete();
127  return true;
128  }
129  return false;
130 }
131 
132 //------------------------------------------------------------------------------
133 
134 //------------------------------------------------------------------------------
135 void ExecuteQuery(vtkMySQLDatabase *iDatabaseConnector, std::string iQuery)
136 {
137  vtkSQLQuery *query = iDatabaseConnector->GetQueryInstance();
138 
139  query->SetQuery( iQuery.c_str() );
140  if ( !query->Execute() )
141  {
142  itkGenericExceptionMacro(
143  << "Execute query failed"
144  << query->GetLastErrorText() );
145  iDatabaseConnector->Close();
146  iDatabaseConnector->Delete();
147  }
148  query->Delete();
149 }
150 
151 //------------------------------------------------------------------------------
152 
153 //------------------------------------------------------------------------------
154 std::vector< std::string > ListDatabases(vtkMySQLDatabase *ServerConnector)
155 {
156  std::vector< std::string > result;
157  vtkSQLQuery * query = ServerConnector->GetQueryInstance();
158  query->SetQuery("Show Databases;");
159  if ( !query->Execute() )
160  {
161  itkGenericExceptionMacro(
162  << "Show Databases query failed."
163  << query->GetLastErrorText() );
164  query->Delete();
165  return result;
166  }
167 
168  // all set, proceed
169 
170  // iterate over lines, we know there is only one column
171  // and as many rows as there is databases
172  while ( query->NextRow() )
173  {
174  result.push_back( query->DataValue(0).ToString() );
175  }
176  query->Delete();
177 
178  return result;
179 }
180 
181 //------------------------------------------------------------------------------
182 
183 //------------------------------------------------------------------------------
184 std::vector< std::string > ListTables(vtkMySQLDatabase *DatabaseConnector)
185 {
186  std::vector< std::string > result;
187  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
188  query->SetQuery("Show tables;");
189  if ( !query->Execute() )
190  {
191  itkGenericExceptionMacro(
192  << "Show tables query failed"
193  << query->GetLastErrorText() );
194  DatabaseConnector->Close();
195  DatabaseConnector->Delete();
196  query->Delete();
197  return result;
198  }
199 
200  // all set, proceed
201 
202  // iterate over lines, we know there is only one column
203  // and as many rows as there is databases
204  while ( query->NextRow() )
205  {
206  result.push_back( query->DataValue(0).ToString() );
207  }
208  query->Delete();
209 
210  return result;
211 }
212 
213 //------------------------------------------------------------------------------
214 
215 //------------------------------------------------------------------------------
216 void DropDatabase(vtkMySQLDatabase *ServerConnector, std::string DBName)
217 {
218  vtkSQLQuery * query = ServerConnector->GetQueryInstance();
219  std::ostringstream insertQuery;
220 
221  insertQuery << "DROP DATABASE " << DBName;
222  query->SetQuery( insertQuery.str().c_str() );
223  if ( !query->Execute() )
224  {
225  itkGenericExceptionMacro(
226  << "Drop query failed"
227  << query->GetLastErrorText() );
228  query->Delete();
229  return;
230  }
231  query->Delete();
232 }
233 
234 //------------------------------------------------------------------------------
235 
236 //------------------------------------------------------------------------------
237 void DropTable(vtkMySQLDatabase *DatabaseConnector, std::string TableName)
238 {
239  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
240  std::ostringstream insertQuery;
241 
242  insertQuery << "DROP TABLE " << TableName;
243  query->SetQuery( insertQuery.str().c_str() );
244  if ( !query->Execute() )
245  {
246  itkGenericExceptionMacro(
247  << "Drop query failed"
248  << query->GetLastErrorText() );
249  query->Delete();
250  return;
251  }
252  query->Delete();
253 }
254 
255 //------------------------------------------------------------------------------
256 
257 //------------------------------------------------------------------------------
258 void DeleteRow(vtkMySQLDatabase *DatabaseConnector,
259  std::string TableName, std::string field, std::string value)
260 {
261  std::stringstream querystream;
262 
263  querystream << "DELETE FROM ";
264  querystream << TableName;
265  querystream << " WHERE ";
266  querystream << field;
267  querystream << " = '";
268  querystream << value;
269  querystream << "';";
270 
271  ExecuteQuery( DatabaseConnector, querystream.str() );
272 }
273 
274 //------------------------------------------------------------------------------
275 
276 //------------------------------------------------------------------------------
277 void DeleteRows(vtkMySQLDatabase *DatabaseConnector, std::string TableName,
278  std::string field, std::vector< std::string > VectorValues)
279 {
280  std::stringstream querystream;
281 
282  querystream << "DELETE FROM ";
283  querystream << TableName;
284  querystream << " WHERE (";
285  unsigned int i;
286  for ( i = 0; i < VectorValues.size() - 1; i++ )
287  {
288  querystream << field;
289  querystream << " = '";
290  querystream << VectorValues[i];
291  querystream << "' OR ";
292  }
293  querystream << field;
294  querystream << " = '";
295  querystream << VectorValues[i];
296  querystream << "');";
297 
298  ExecuteQuery( DatabaseConnector, querystream.str() );
299 }
300 
301 //------------------------------------------------------------------------------
302 
303 //------------------------------------------------------------------------------
304 bool DoesDatabaseExist(vtkMySQLDatabase *ServerConnector, std::string DBName)
305 {
306  std::vector< std::string > list;
307  list = ListDatabases(ServerConnector);
308  std::string myString(DBName);
309  std::vector< std::string >::iterator start = list.begin();
310  std::vector< std::string >::iterator end = list.end();
311  while ( start != end )
312  {
313  if ( ( *start ) == myString )
314  {
315  return true;
316  }
317  ++start;
318  }
319  return false;
320 }
321 
322 //------------------------------------------------------------------------------
323 
324 //------------------------------------------------------------------------------
325 bool DoesTableExist(vtkMySQLDatabase *DatabaseConnector,
326  std::string TableName)
327 {
328  std::vector< std::string > list;
329  list = ListTables(DatabaseConnector);
330  std::string myString(TableName);
331  std::vector< std::string >::iterator start = list.begin();
332  std::vector< std::string >::iterator end = list.end();
333  while ( start != end )
334  {
335  if ( ( *start ) == myString )
336  {
337  return true;
338  }
339  ++start;
340  }
341  return false;
342 }
343 
344 //------------------------------------------------------------------------------
345 
346 //------------------------------------------------------------------------------
347 void UpdateValueInDB(vtkMySQLDatabase *DatabaseConnector, std::string iTableName,
348  std::string iColumnName, std::string iNewValue, std::string iField,
349  std::vector< unsigned int > iVectIDs)
350 {
351  std::stringstream querystream;
352 
353  querystream << "UPDATE ";
354  querystream << iTableName;
355  querystream << " SET ";
356  querystream << iColumnName;
357  querystream << " = '";
358  querystream << iNewValue;
359  querystream << "' WHERE ";
360  querystream << GetConditions< unsigned int >(iField, iVectIDs, "OR");
361  ExecuteQuery( DatabaseConnector, querystream.str() );
362 }
363 
364 //------------------------------------------------------------------------------
365 
366 //------------------------------------------------------------------------------
367 void UpdateValueInDB(vtkMySQLDatabase *DatabaseConnector,
368  std::string TableName, std::string field, std::string newValue,
369  std::string ColumnName, std::string value)
370 
371 {
372  std::stringstream querystream;
373 
374  querystream << "UPDATE ";
375  querystream << TableName;
376  querystream << " SET ";
377  querystream << field;
378  querystream << " = '";
379  querystream << newValue;
380  querystream << "' WHERE ";
381  querystream << ColumnName;
382  querystream << " = '";
383  querystream << value;
384  querystream << "';";
385 
386  ExecuteQuery( DatabaseConnector, querystream.str() );
387 }
388 
389 //------------------------------------------------------------------------------
390 
391 //------------------------------------------------------------------------------
392 void UpdateValueInDB(vtkMySQLDatabase *DatabaseConnector,
393  std::string iTableName, std::string ifield,
394  std::string inewValue, std::vector< unsigned int > iVectIDs)
395 {
396  std::stringstream querystream;
397 
398  querystream << "UPDATE ";
399  querystream << iTableName;
400  querystream << " SET ";
401  querystream << ifield;
402  querystream << " = '";
403  querystream << inewValue;
404  querystream << " WHERE (";
405  unsigned int i;
406  for ( i = 0; i < iVectIDs.size() - 1; i++ )
407  {
408  querystream << ifield;
409  querystream << " = '";
410  querystream << iVectIDs[i];
411  querystream << "' OR ";
412  }
413  querystream << ifield;
414  querystream << " = '";
415  querystream << iVectIDs[i];
416  querystream << "');";
417 
418  ExecuteQuery( DatabaseConnector, querystream.str() );
419 }
420 
421 //------------------------------------------------------------------------------
422 
423 //------------------------------------------------------------------------------
424 std::vector< std::string > GetFieldNames(std::string TableName,
425  vtkMySQLDatabase *DatabaseConnector)
426 {
427  std::vector< std::string > result;
428 
429  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
430  std::stringstream querystream;
431  querystream << "DESCRIBE ";
432  querystream << TableName;
433  querystream << ";";
434 
435  query->SetQuery( querystream.str().c_str() );
436  if ( !query->Execute() )
437  {
438  itkGenericExceptionMacro(
439  << "describe table query failed"
440  << query->GetLastErrorText() );
441  DatabaseConnector->Close();
442  DatabaseConnector->Delete();
443  query->Delete();
444  return result;
445  }
446  while ( query->NextRow() )
447  {
448  result.push_back( query->DataValue(0).ToString() );
449  }
450  query->Delete();
451 
452  return result;
453 }
bool CloseDatabaseConnection(vtkMySQLDatabase *DatabaseConnector)
return true if the connection has been closed, false if the connection was already closed ...
bool DoesDatabaseExist(vtkMySQLDatabase *ServerConnector, std::string DBName)
vtkMySQLDatabase * OpenDatabaseConnection(std::string ServerName, std::string login, std::string Password, std::string DBName)
std::vector< std::string > ListDatabases(vtkMySQLDatabase *ServerConnector)
void DropDatabase(vtkMySQLDatabase *ServerConnector, std::string DBName)
void UpdateValueInDB(vtkMySQLDatabase *DatabaseConnector, std::string iTableName, std::string iColumnName, std::string iNewValue, std::string iField, std::vector< unsigned int > iVectIDs)
std::vector< std::string > GetFieldNames(std::string TableName, vtkMySQLDatabase *DatabaseConnector)
void DeleteRows(vtkMySQLDatabase *DatabaseConnector, std::string TableName, std::string field, std::vector< std::string > VectorValues)
void ExecuteQuery(vtkMySQLDatabase *iDatabaseConnector, std::string iQuery)
void DropTable(vtkMySQLDatabase *DatabaseConnector, std::string TableName)
std::pair< bool, vtkMySQLDatabase * > ConnectToDatabase(std::string ServerName, std::string login, std::string Password, std::string DBName)
bool DoesTableExist(vtkMySQLDatabase *DatabaseConnector, std::string TableName)
std::pair< bool, vtkMySQLDatabase * > ConnectToServer(std::string ServerName, std::string login, std::string Password)
std::vector< std::string > ListTables(vtkMySQLDatabase *DatabaseConnector)
void DeleteRow(vtkMySQLDatabase *DatabaseConnector, std::string TableName, std::string field, std::string value)