GOFIGURE2  0.9.0
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Groups Pages
GoDBRecordSet.h
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 #ifndef __GoDBRecordSet_h
35 #define __GoDBRecordSet_h
36 
37 #include <algorithm>
38 #include <utility>
39 #include <vector>
40 #include <string>
41 #include <sstream>
42 
43 #include "vtkVariant.h"
44 #include "QueryDataBaseHelper.h"
45 
46 #include "vtkMySQLDatabase.h"
47 #include "vtkSQLQuery.h"
48 
49 template< class TObject >
51 {
52 public:
53  // row type
54  typedef TObject OriginalObjectType;
55 
56  // decorate the row type to know if it was modified
57  // allows for optimization when synchronising the data
58  typedef std::pair< bool, OriginalObjectType > InternalObjectType;
59  typedef std::vector< InternalObjectType > RowContainerType;
60 
63 
64  std::vector< std::string > GetColumnNamesContainer()
65  {
67  }
68 
70  {
71  return &m_RowContainer;
72  }
73 
76  void AddObject(const OriginalObjectType & object)
77  {
78  m_RowContainer.push_back( InternalObjectType(false, object) );
79  }
80 
81  /* Insert Object
82  note lydie:overwrite the object at the pos position in the
83  m_RowContainer (not yet used)wouldn't that be better to call it "ReplaceObject" ?
84  if the object has changed, why put the bool to true ??? */
85  void InsertObject(const int & pos, const OriginalObjectType & object)
86  {
87  if ( pos > m_RowContainer.size() )
88  {
89  AddObject(object);
90  return;
91  }
92  m_RowContainer[pos] = InternalObjectType(true, object);
93  }
94 
97  void SetWhereString(const std::string& whereString)
98  { this->m_WhereString = whereString; this->IsWhereStringSet = true; }
99 
100  void SetServerName(const std::string& iServerName)
101  { this->ServerName = iServerName; }
102 
103  void SetDataBaseName(const std::string& iDataBaseName)
104  { this->DataBaseName = iDataBaseName; }
105 
106  void SetTableName(const std::string& iTableName)
107  { this->TableName = iTableName; }
108 
109  void SetUser(const std::string& iUser)
110  { this->User = iUser; }
111 
112  void SetPassword(const std::string& iPassword)
113  { this->PassWord = iPassword; }
114 
115  void SetConnector(vtkMySQLDatabase *iDatabaseConnector)
116  { this->m_DatabaseConnector = iDatabaseConnector; }
117 
126  {
128 
129  std::stringstream queryString;
130  queryString << "SELECT * FROM " << this->TableName;
131  if ( IsWhereStringSet )
132  {
133  queryString << " WHERE " << m_WhereString;
134  }
135  queryString << ";";
136 
137  vtkSQLQuery *query = m_DatabaseConnector->GetQueryInstance();
138  query->SetQuery( queryString.str().c_str() );
139  if ( !query->Execute() )
140  {
141  // replace by exception
142  std::cerr << "Create query failed" << std::endl;
143  }
144  else
145  {
146  if ( m_RowContainer.size() > 0 )
147  {
148  m_RowContainer.clear();
149  }
150 
151  //here the m_RowContainer is filled with all the results of the previous
152  // query:
153  //SELECT * FROM table:
154  while ( query->NextRow() )
155  {
156  OriginalObjectType object;
157  for ( size_t colID = 0; colID < m_ColumnNamesContainer.size(); colID++ )
158  {
159  std::string ColumnName = m_ColumnNamesContainer[colID];
160  object.SetField( ColumnName, query->DataValue(colID).ToString() );
161  }
162  m_RowContainer.push_back( InternalObjectType(true, object) );
163  }
164  }
165  //DataBaseConnector->Delete();
166  query->Delete();
167  }
168 
169  // save content to DB - ASYNCHRONOUS
170  bool SaveInDB(bool Update = false)
171  {
172  if ( m_RowContainer.empty() )
173  {
174  return true;
175  }
176 
177  myIteratorType start = m_RowContainer.begin();
178  myIteratorType end = m_RowContainer.end();
179 
180  std::sort( start, end, IsLess() );
181 
183 
184  vtkSQLQuery *query = m_DatabaseConnector->GetQueryInstance();
185 
186  if ( Update )
187  {
188  if ( this->UpdateRows(query, start, end) )
189  {
190  query->Delete();
191  return true;
192  }
193  else
194  {
195  query->Delete();
196  return false;
197  }
198  }
199  else
200  {
201  if ( this->SaveEachRow(query, false) )
202  {
203  query->Delete();
204  return true;
205  }
206  else
207  {
208  query->Delete();
209  return false;
210  }
211  }
212  }
213 
214 private:
219  struct IsLess {
221  {
222  // Dirty first
223  if ( A.first && !B.first )
224  {
225  return true;
226  }
227  return false;
228  }
229  };
230 
231  // underlying container
232  typedef typename std::vector< InternalObjectType >::iterator myIteratorType;
233  std::vector< InternalObjectType > m_RowContainer;
234 
236 
237  bool SaveEachRow(vtkSQLQuery *query, bool Update = false);
238 
239  bool SaveRows(vtkSQLQuery *query,
240  const std::string & what,
241  const myIteratorType & start,
242  const myIteratorType & end);
243 
244  bool UpdateRows(vtkSQLQuery *query,
245  const myIteratorType & start,
246  const myIteratorType & end);
247 
248  // colum names container
249  std::vector< std::string > m_ColumnNamesContainer;
250 
251  // DB variables
252  vtkMySQLDatabase *m_DatabaseConnector;
253  std::string ServerName;
254  std::string DataBaseName;
255  std::string TableName;
256  std::string User;
257  std::string PassWord;
258  std::string m_WhereString;
260  bool IsOpen;
261 };
262 
263 template< class TObject >
264 bool
265 GoDBRecordSet< TObject >::SaveEachRow(vtkSQLQuery *query, bool Update)
266 {
267  // modified rows
268  myIteratorType start = m_RowContainer.begin();
269  myIteratorType firstFalseElement = start;
270  myIteratorType end = m_RowContainer.end();
271 
272  while ( ( *firstFalseElement ).first && firstFalseElement != end )
273  {
274  ++firstFalseElement;
275  }
276 
277  // Here we suppose read and write only, no overwrite
278  //if( end-start > 0 )
279  // {
280  // if( !SaveRows( query, "REPLACE ", start, end ) )
281  // {
282  // return false;
283  // }
284  // }
285 
286  // new rows
287  if ( end - firstFalseElement > 0 )
288  {
289  if ( Update )
290  {
291  if ( !UpdateRows(query, firstFalseElement, end) )
292  {
293  return false;
294  }
295  }
296  else
297  {
298  if ( !SaveRows(query, "INSERT ", firstFalseElement, end) )
299  {
300  return false;
301  }
302  }
303  }
304 
305  return true;
306 }
307 
310 template< class TObject >
311 bool
313  const std::string& what,
314  const myIteratorType& start,
315  const myIteratorType& end)
316 {
317  // safe test
318  /*unsigned int NbOfCol = m_ColumnNamesContainer.size();
319  if( NbOfCol == 0 )
320  {
321  // throw exception
322  std::cerr << "Could not extract column names." << std::endl;
323  return false;
324  }*/
325 
326  // invariant part of the query: corresponds to the insert/replace into table
327  //(names of all the columns):
328  myIteratorType rowIt = start;
329 
330  std::stringstream queryString;
331 
332  queryString << what << "INTO " << this->TableName;
333  queryString << " ( ";
334  queryString << rowIt->second.PrintColumnNames();
335  queryString << " ) ";
336  queryString << " VALUES ";
337 
338  // row dependent part of the query: one row corresponds to the values of one
339  //OriginalObjectType (exp:GoProjectRow)to be saved into the Database. So this
340  // part
341  //saves the values for all the OriginalObjectType contained in the vector
342  // m_RowContainer:
343  while ( rowIt != end )
344  {
345  std::stringstream rowQueryString;
346  rowQueryString << queryString.str();
347  rowQueryString << "(";
348  rowQueryString << rowIt->second.PrintValues();
349  rowQueryString << ");";
350  query->SetQuery( rowQueryString.str().c_str() );
351  if ( !query->Execute() )
352  {
353  // replace by exception
354  std::cerr << "Save query failed: ";
355  std::cerr << rowQueryString.str().c_str() << std::endl;
356  return false;
357  }
358  ++rowIt;
359  }
360  return true;
361 }
362 
365 template< class TObject >
366 bool
368  const myIteratorType& start,
369  const myIteratorType& end)
370 {
371  myIteratorType rowIt = start;
372 
373  std::stringstream queryString;
374 
375  queryString << "UPDATE ";
376  queryString << this->TableName << " SET ";
377  queryString << rowIt->second.PrintColumnNamesWithValues();
378  queryString << " WHERE ";
379  queryString << rowIt->second.GetTableIDName();
380  queryString << " = ";
381  queryString << rowIt->second.GetMapValue( rowIt->second.GetTableIDName() );
382 
383  // row dependent part of the query: one row corresponds to the values of one
384  //OriginalObjectType (exp:GoProjectRow)to be saved into the Database. So this
385  // part
386  //saves the values for all the OriginalObjectType contained in the vector
387  // m_RowContainer:
388  while ( rowIt != end )
389  {
390  queryString << ";";
391  query->SetQuery( queryString.str().c_str() );
392  if ( !query->Execute() )
393  {
394  // replace by exception
395  std::cerr << "Save query failed: ";
396  std::cerr << queryString.str().c_str() << std::endl;
397  return false;
398  }
399  rowIt++;
400  }
401  return true;
402 }
403 
407 template< class TObject >
408 void
410 {
411  if ( m_ColumnNamesContainer.size() > 0 )
412  {
413  m_ColumnNamesContainer.clear();
414  }
415 
416  vtkSQLQuery * query = m_DatabaseConnector->GetQueryInstance();
417  std::stringstream querystream;
418  querystream << "SHOW COLUMNS FROM ";
419  querystream << this->TableName;
420  //querystream << " FROM ";
421  //querystream << this->DataBaseName;
422  querystream << ";";
423 
424  query->SetQuery( querystream.str().c_str() );
425  if ( !query->Execute() )
426  {
427  // replace by exception
428  std::cerr << "Create query failed" << std::endl;
429  }
430  else
431  {
432  while ( query->NextRow() )
433  {
434  m_ColumnNamesContainer.push_back( query->DataValue(0).ToString() );
435  }
436  }
437  query->Delete();
438 }
439 
440 #endif
std::string User
std::vector< InternalObjectType > m_RowContainer
std::pair< bool, OriginalObjectType > InternalObjectType
Definition: GoDBRecordSet.h:58
std::vector< std::string > GetColumnNamesContainer()
Definition: GoDBRecordSet.h:64
TObject OriginalObjectType
Definition: GoDBRecordSet.h:54
void SetUser(const std::string &iUser)
std::string PassWord
bool UpdateRows(vtkSQLQuery *query, const myIteratorType &start, const myIteratorType &end)
uses the INSERT or REPLACE query to save all the objects GoDB..Row currently in the m_RowContainer lo...
void PopulateFromDB()
help read content from DB: select all the fields for a given table (TableName)in the database and fil...
std::string DataBaseName
std::string m_WhereString
bool operator()(const InternalObjectType &A, const InternalObjectType &B)
std::vector< InternalObjectType >::iterator myIteratorType
std::string TableName
bool SaveRows(vtkSQLQuery *query, const std::string &what, const myIteratorType &start, const myIteratorType &end)
uses the INSERT or REPLACE query to save all the objects GoDB..Row currently in the m_RowContainer lo...
void AddObject(const OriginalObjectType &object)
Add a new Object of OriginalObjectType (exp: GoDBprojectRow) in the m_RowContainer and set the bool t...
Definition: GoDBRecordSet.h:76
bool SaveInDB(bool Update=false)
void SetTableName(const std::string &iTableName)
void SetConnector(vtkMySQLDatabase *iDatabaseConnector)
void SetServerName(const std::string &iServerName)
std::string ServerName
void SetDataBaseName(const std::string &iDataBaseName)
void PopulateColumnNamesContainer()
fills the vector m_ColumnNamesContainer with the column names gotten from the database and in the sam...
void SetPassword(const std::string &iPassword)
void InsertObject(const int &pos, const OriginalObjectType &object)
Definition: GoDBRecordSet.h:85
vtkMySQLDatabase * m_DatabaseConnector
bool SaveEachRow(vtkSQLQuery *query, bool Update=false)
void SetWhereString(const std::string &whereString)
is there to be used in case there is a "WHERE" condition to add for the selection in PopulateFromDB()...
Definition: GoDBRecordSet.h:97
RowContainerType * GetRowContainer()
Definition: GoDBRecordSet.h:69
std::vector< std::string > m_ColumnNamesContainer
std::vector< InternalObjectType > RowContainerType
Definition: GoDBRecordSet.h:59