GOFIGURE2  0.9.0
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Groups Pages
SelectQueryDatabaseHelper.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 =========================================================================*/
35 
36 #include "vtkMySQLDatabase.h"
37 #include "vtkSQLQuery.h"
38 #include "vtkStdString.h"
39 #include "vtkSmartPointer.h"
40 #include "vtkVariant.h"
44 
45 #include <sstream>
46 #include <string>
47 #include <algorithm>
48 
49 std::vector< std::string >
50 ListAllValuesForOneColumn(vtkMySQLDatabase *DatabaseConnector,
51  const std::string & ColumnName,
52  const std::string & TableName,
53  std::string OrderByColumnName)
54 {
55  std::string QueryString = SelectQueryStream(TableName, ColumnName, OrderByColumnName);
56 
57  return ExecuteSelectQuery< std::vector< std::string > >(DatabaseConnector, QueryString);
58 }
59 
60 //------------------------------------------------------------------------------
61 
62 //------------------------------------------------------------------------------
63 boost::unordered_map< std::string, std::string >
64 MapTwoColumnsFromTable( vtkMySQLDatabase *DatabaseConnector,
65  const std::vector< std::string > & iColumnNames,
66  const std::string & iTableName,
67  std::string iField,
68  std::string iValue)
69 {
70  boost::unordered_map< std::string, std::string > Result =
71  boost::unordered_map< std::string, std::string >();
72 
73  if ( iColumnNames.size() != 2 )
74  {
75  std::cout << "can not map if the size of the vector for the selected fields is different than 2 ";
76  std::cout << "Debug: In " << __FILE__ << ", line " << __LINE__;
77  std::cout << std::endl;
78  return Result;
79  }
80  std::string QueryString;
81  if ( iField.empty() )
82  {
83  QueryString = SelectQueryStream(iTableName, iColumnNames);
84  }
85  else
86  {
87  QueryString = SelectQueryStreamCondition(iTableName, iColumnNames, iField, iValue);
88  }
89 
90  vtkSQLQuery *query = DatabaseConnector->GetQueryInstance();
91  query->SetQuery( QueryString.c_str() );
92 
93  if ( !query->Execute() )
94  {
95  itkGenericExceptionMacro(
96  << "List of all fields query failed"
97  << query->GetLastErrorText() );
98  query->Delete();
99  return Result;
100  }
101  while ( query->NextRow() )
102  {
103  Result[query->DataValue(0).ToString()] = query->DataValue(1).ToString();
104  }
105  query->Delete();
106 
107  return Result;
108 }
109 
110 //------------------------------------------------------------------------------
111 
112 //------------------------------------------------------------------------------
113 std::vector< std::pair< std::string, std::string > >
114 VectorTwoColumnsFromTable(vtkMySQLDatabase *DatabaseConnector,
115  const std::string & ColumnNameOne,
116  const std::string & ColumnNameTwo,
117  const std::string & TableName,
118  const std::string & OrderByColumnName)
119 {
120  std::vector< std::pair< std::string, std::string > > result;
121 
122  std::vector< std::string > ColumnNames(2);
123  ColumnNames[0] = ColumnNameOne;
124  ColumnNames[1] = ColumnNameTwo;
125  std::string QueryString = SelectQueryStream(TableName, ColumnNames,
126  OrderByColumnName);
127  vtkSQLQuery *query = DatabaseConnector->GetQueryInstance();
128  query->SetQuery( QueryString.c_str() );
129  if ( !query->Execute() )
130  {
131  itkGenericExceptionMacro(
132  << "select 2 columns query failed"
133  << query->GetLastErrorText() );
134  DatabaseConnector->Close();
135  DatabaseConnector->Delete();
136  query->Delete();
137  return result;
138  }
139 
140  while ( query->NextRow() )
141  {
142  std::pair< std::string, std::string > Pair;
143  Pair.first = query->DataValue(0).ToString();
144  Pair.second = query->DataValue(1).ToString();
145  result.push_back(Pair);
146  }
147 
148  query->Delete();
149 
150  return result;
151 }
152 
153 //------------------------------------------------------------------------------
154 
155 //------------------------------------------------------------------------------
156 std::vector< std::string >
157 ListSpecificValuesForRow( vtkMySQLDatabase *DatabaseConnector,
158  const std::string & TableName,
159  const std::string & field,
160  const std::string & value)
161 {
162  std::string QueryString = SelectQueryStreamCondition(TableName, "*", field, value);
163 
164  return ExecuteSelectQuery< std::vector< std::string > >(DatabaseConnector, QueryString);
165 }
166 
167 //------------------------------------------------------------------------------
168 
169 //------------------------------------------------------------------------------
170 int FindOneID(vtkMySQLDatabase *DatabaseConnector,
171  const std::string & TableName,
172  const std::string & ColumnName,
173  const std::string & field,
174  const std::string & value)
175 {
176  int ID = -1;
177 
178  std::string QueryString =
179  SelectQueryStreamCondition(TableName, ColumnName, field, value);
180 
181  std::vector< std::string > Results = ExecuteSelectQuery< std::vector< std::string > >(
182  DatabaseConnector, QueryString);
183  if ( Results.size() > 1 )
184  {
185  std::cout << "there is not an unique ID";
186  std::cout << "Debug: In " << __FILE__ << ", line " << __LINE__;
187  std::cout << std::endl;
188  return ID;
189  }
190  if ( !Results.empty() )
191  {
192  ID = atoi( Results[0].c_str() );
193  }
194  return ID;
195 }
196 
197 //------------------------------------------------------------------------------
198 
199 //------------------------------------------------------------------------------
200 int FindOneID(vtkMySQLDatabase *DatabaseConnector,
201  const std::string & TableName,
202  const std::string & ColumnName,
203  const std::vector< FieldWithValue > & iConditions)
204 {
205  std::string QueryString = SelectQueryStreamListConditions(TableName,
206  ColumnName, iConditions, "AND");
207  int ID = -1;
208 
209  std::vector< int > Results = ExecuteSelectQuery< std::vector< int > >(
210  DatabaseConnector, QueryString);
211 
212  if ( !Results.empty() )
213  {
214  ID = Results[0];
215  }
216  return ID;
217 }
218 
219 //------------------------------------------------------------------------------
220 
221 //------------------------------------------------------------------------------
222 std::vector< std::string > FindSeveralIDs(vtkMySQLDatabase *iDatabaseConnector,
223  const std::string & TableName,
224  const std::string & ColumnName,
225  const std::vector< FieldWithValue > & iConditions)
226 {
227  std::string QueryString = SelectQueryStreamListConditions(TableName,
228  ColumnName, iConditions, "AND");
229 
230  return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
231 }
232 
233 //------------------------------------------------------------------------------
234 
235 //------------------------------------------------------------------------------
236 std::vector< std::string > ListSpecificValuesForOneColumn(
237  vtkMySQLDatabase *iDatabaseConnector,
238  const std::string & TableName,
239  const std::string & ColumnName,
240  const std::string & field,
241  const std::string & value, //bool Distinct,
242  bool ExcludeZero)
243 {
244  std::vector< FieldWithValue > VectorConditions;
245  FieldWithValue EqualValue = { field, value, "=" };
246  VectorConditions.push_back(EqualValue);
247  if ( ExcludeZero )
248  {
249  FieldWithValue DiffZero = { ColumnName, "0", "<>" };
250  VectorConditions.push_back(DiffZero);
251  }
252  std::string QueryString = SelectQueryStreamListConditions(TableName,
253  ColumnName, VectorConditions, "AND");
254 
255  return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
256 }
257 
258 //------------------------------------------------------------------------------
259 
260 //------------------------------------------------------------------------------
261 std::vector< std::string > ListSpecificValuesForOneColumn(
262  vtkMySQLDatabase *iDatabaseConnector,
263  const std::string & TableName,
264  const std::string & ColumnName,
265  const std::string & field,
266  const std::string & value,
267  const std::string & ColumnNameOrder)
268 {
269  std::string QueryString = SelectQueryStreamCondition(TableName, ColumnName, field, value, ColumnNameOrder);
270 
271  return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
272 }
273 
274 //------------------------------------------------------------------------------
275 
276 //------------------------------------------------------------------------------
277 std::vector< std::string > ListSpecificValuesForOneColumn(
278  vtkMySQLDatabase *iDatabaseConnector,
279  const std::string & TableName,
280  const std::string & ColumnName,
281  const std::string & field,
282  const std::vector< std::string > & VectorValues,
283  bool Distinct,
284  bool ExcludeZero)
285 {
286  std::string Conditions;
287 
288  if ( ExcludeZero )
289  {
290  std::vector< FieldWithValue > VectorConditions(1);
291  FieldWithValue DiffZero = { ColumnName, "0", "<>" };
292  VectorConditions[0] = DiffZero;
293  Conditions = GetConditions(VectorConditions, "AND");
294 
295  Conditions = Conditions.substr(0, Conditions.size() - 1);
296  Conditions += " AND ";
297  }
298 
299  Conditions += GetConditions(field, VectorValues, "OR");
300  if ( ExcludeZero )
301  {
302  Conditions += ")";
303  }
304  std::string QueryString = SelectQueryStreamCondition(TableName, ColumnName, Conditions, Distinct);
305  return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
306 }
307 
308 //------------------------------------------------------------------------------
309 
310 //------------------------------------------------------------------------------
311 std::list< unsigned int > ListSpecificValuesForOneColumn(
312  vtkMySQLDatabase *iDatabaseConnector,
313  const std::string & TableName,
314  const std::string & ColumnName,
315  const std::string & field,
316  const std::list< unsigned int > & iListValues,
317  bool Distinct,
318  bool ExcludeZero)
319 {
320  std::vector< unsigned int > VectorValues(
321  iListValues.begin(), iListValues.end() );
322  std::string Conditions;
323  if ( ExcludeZero )
324  {
325  std::vector< FieldWithValue > VectorConditions(1);
326  FieldWithValue DiffZero = { ColumnName, "0", "<>" };
327  VectorConditions[0] = DiffZero;
328  Conditions = GetConditions(VectorConditions, "AND");
329 
330  Conditions = Conditions.substr(0, Conditions.size() - 1);
331  if ( !VectorValues.empty() )
332  {
333  Conditions += " AND ";
334  }
335  }
336 
337  Conditions += GetConditions(field, VectorValues, "OR");
338  if ( ExcludeZero )
339  {
340  Conditions += ")";
341  }
342  std::string QueryString = SelectQueryStreamCondition(TableName,
343  ColumnName, Conditions, Distinct);
344 
345  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
346 }
347 
348 //------------------------------------------------------------------------------
349 
350 //------------------------------------------------------------------------------
351 std::list< unsigned int > ListSpecificValuesForOneColumn(
352  vtkMySQLDatabase *iDatabaseConnector,
353  const std::string & TableName,
354  const std::string & ColumnName,
355  const std::string & fieldOne,
356  const std::list< unsigned int > & ListValuesOne,
357  const std::string & fieldTwo,
358  const std::string & ValueFieldTwo)
359 {
360  std::vector< unsigned int > VectorValuesOne(
361  ListValuesOne.begin(), ListValuesOne.end() );
362  FieldWithValue AndCondition = { fieldTwo, ValueFieldTwo, "=" };
363  std::string Conditions = GetAndORConditions(AndCondition, fieldOne,
364  VectorValuesOne);
365  std::string QueryString = SelectQueryStreamCondition(TableName,
366  ColumnName, Conditions);
367 
368  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
369 }
370 
371 //------------------------------------------------------------------------------
372 
373 //------------------------------------------------------------------------------
374 std::vector< std::pair< std::string, std::string > >
376  vtkMySQLDatabase *DatabaseConnector,
377  const std::string & TableName,
378  const std::string & ColumnNameOne,
379  const std::string & ColumnNameTwo,
380  const std::string & field,
381  const std::string & value,
382  const std::string & ColumnNameOrder)
383 {
384  std::vector< std::pair< std::string, std::string > > result;
385  std::vector< std::string > SelectedColumns(2);
386  SelectedColumns[0] = ColumnNameOne;
387  SelectedColumns[1] = ColumnNameTwo;
388  std::string QueryString = SelectQueryStreamCondition(TableName, SelectedColumns,
389  field, value, ColumnNameOrder);
390 
391  vtkSQLQuery *query = DatabaseConnector->GetQueryInstance();
392 
393  query->SetQuery( QueryString.c_str() );
394  if ( !query->Execute() )
395  {
396  itkGenericExceptionMacro(
397  << "select 2 columns query failed"
398  << query->GetLastErrorText() );
399  DatabaseConnector->Close();
400  DatabaseConnector->Delete();
401  query->Delete();
402  return result;
403  }
404 
405  while ( query->NextRow() )
406  {
407  std::pair< std::string, std::string > Pair;
408  Pair.first = query->DataValue(0).ToString();
409  Pair.second = query->DataValue(1).ToString();
410  result.push_back(Pair);
411  }
412 
413  query->Delete();
414 
415  return result;
416 }
417 
418 //------------------------------------------------------------------------------
419 
420 //------------------------------------------------------------------------------
421 std::string ReturnOnlyOneValue(
422  vtkMySQLDatabase *DatabaseConnector,
423  const std::string & TableName,
424  const std::string & ColumnName,
425  const std::string & field,
426  const std::string & value)
427 {
428  std::string result;
429  std::string Conditions = GetConditions(field, value);
430 
431  Conditions += " LIMIT 1";
432  std::string QueryString = SelectGeneralQueryConditions(ColumnName, TableName, Conditions);
433  return ExecuteSelectQueryOneValue< std::string >(DatabaseConnector, QueryString);
434 }
435 
436 //------------------------------------------------------------------------------
437 
438 //------------------------------------------------------------------------------
440  vtkMySQLDatabase *DatabaseConnector,
441  const std::string & ColumnName,
442  const std::string & TableName)
443 {
444  std::string What = "MAX(";
445 
446  What += ColumnName;
447  What += ")";
448  std::string QueryString = SelectGeneralQuery(What, TableName);
449  return ExecuteSelectQueryOneValue< int >(DatabaseConnector, QueryString);
450 }
451 
452 //------------------------------------------------------------------------------
453 
454 //------------------------------------------------------------------------------
456  vtkMySQLDatabase *DatabaseConnector,
457  const std::string & ColumnName,
458  const std::string & TableName,
459  const std::string & field,
460  const std::string & value)
461 {
462  std::string What = "MAX(";
463 
464  What += ColumnName;
465  What += ")";
466  std::string QueryString = SelectQueryStreamCondition(TableName, What, field, value);
467  return ExecuteSelectQueryOneValue< int >(DatabaseConnector, QueryString);
468 }
469 
470 //------------------------------------------------------------------------------
471 
472 //------------------------------------------------------------------------------
474  vtkMySQLDatabase *DatabaseConnector,
475  const std::string & ColumnName,
476  const std::string & TableName,
477  const std::string & field,
478  const std::vector< std::string > & VectorValues)
479 {
480  std::string What = "MAX(";
481 
482  What += ColumnName;
483  What += ")";
484  std::string QueryString = SelectQueryStreamListConditions(TableName, What, field, VectorValues);
485  return ExecuteSelectQueryOneValue< int >(DatabaseConnector, QueryString);
486 }
487 
488 //------------------------------------------------------------------------------
489 
490 //------------------------------------------------------------------------------
492  vtkMySQLDatabase *DatabaseConnector,
493  const std::string & ColumnName,
494  const std::string & TableName,
495  const std::string & field,
496  const std::vector< std::string > & VectorValues)
497 {
498  std::string What = "MIN(";
499 
500  What += ColumnName;
501  What += ")";
502  std::string QueryString = SelectQueryStreamListConditions(TableName, What, field, VectorValues);
503  return ExecuteSelectQueryOneValue< int >(DatabaseConnector, QueryString);
504 }
505 
506 //------------------------------------------------------------------------------
507 
508 //------------------------------------------------------------------------------
509 //query: "SELECT TableOne.ColumnOne, TableTwo.ColumnTwo FROM TableOne
510 //JOIN TableTwo ON (TableOne.Foreignkey = TableTwo.PrimaryKey)
511 //WHERE field = value;
512 std::vector< std::pair< int, std::string > >
514  vtkMySQLDatabase *DatabaseConnector,
515  const std::string & TableOne,
516  const std::string & ColumnOne,
517  const std::string & TableTwo,
518  const std::string & ColumnTwo,
519  const std::string & ForeignKey,
520  const std::string & PrimaryKey,
521  const std::string & field,
522  const std::string & value)
523 {
524  std::vector< std::pair< int, std::string > > result;
525 
526  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
527  std::stringstream querystream;
528  querystream << "SELECT ";
529  querystream << TableOne;
530  querystream << ".";
531  querystream << ColumnOne;
532  querystream << ",";
533  querystream << TableTwo;
534  querystream << ".";
535  querystream << ColumnTwo;
536  querystream << " FROM ";
537  querystream << TableOne;
538  querystream << " JOIN ";
539  querystream << TableTwo;
540  querystream << " ON (";
541  querystream << TableOne;
542  querystream << ".";
543  querystream << ForeignKey;
544  querystream << " = ";
545  querystream << TableTwo;
546  querystream << ".";
547  querystream << PrimaryKey;
548  querystream << " WHERE ";
549  querystream << field;
550  querystream << " = '";
551  querystream << value;
552  querystream << "';";
553 
554  query->SetQuery( querystream.str().c_str() );
555  if ( !query->Execute() )
556  {
557  itkGenericExceptionMacro(
558  << "join on 2 tables query failed"
559  << query->GetLastErrorText() );
560  DatabaseConnector->Close();
561  DatabaseConnector->Delete();
562  query->Delete();
563  return result;
564  }
565 
566  while ( query->NextRow() )
567  {
568  {
569  std::pair< int, std::string > temp;
570  temp.first = query->DataValue(0).ToInt();
571  temp.second = query->DataValue(1).ToString();
572  result.push_back(temp);
573  }
574  }
575  query->Delete();
576  return result;
577 }
578 
579 //------------------------------------------------------------------------------
580 
581 //------------------------------------------------------------------------------
582 std::vector< std::vector< std::string > >
584  vtkMySQLDatabase *DatabaseConnector,
585  const std::string & MainTable,
586  const std::vector< std::string > & SelectFields,
587  const std::string & field,
588  const std::string & value,
589  const std::vector< std::string > & JoinTablesOnTraceTable,
590  bool Distinct)
591 {
592  std::vector< std::vector< std::string > > Results;
593  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
594 
595  std::stringstream Querystream;
596  Querystream << "SELECT ";
597  if ( Distinct )
598  {
599  Querystream << "DISTINCT ";
600  }
601  unsigned int i;
602  for ( i = 0; i < SelectFields.size() - 1; i++ )
603  {
604  Querystream << SelectFields[i];
605  Querystream << ",";
606  }
607  Querystream << SelectFields[i];
608  Querystream << " FROM ";
609  Querystream << MainTable;
610  unsigned int j = 0;
611  while ( j < JoinTablesOnTraceTable.size() )
612  {
613  Querystream << " LEFT JOIN ";
614  Querystream << JoinTablesOnTraceTable[j];
615  Querystream << " ON ";
616  Querystream << JoinTablesOnTraceTable[j + 1];
617  Querystream << " ";
618  j = j + 2;
619  }
620  Querystream << "WHERE ";
621  Querystream << MainTable;
622  Querystream << ".";
623  Querystream << field;
624  Querystream << " = ";
625  Querystream << value;
626  Querystream << ";";
627 
628  query->SetQuery( Querystream.str().c_str() );
629  if ( !query->Execute() )
630  {
631  itkGenericExceptionMacro(
632  << "return info Contours query failed"
633  << query->GetLastErrorText() );
634  DatabaseConnector->Close();
635  DatabaseConnector->Delete();
636  query->Delete();
637  return Results;
638  }
639  while ( query->NextRow() )
640  {
641  std::vector< std::string > ResultsForOneRow;
642  for ( int k = 0; k < query->GetNumberOfFields(); k++ )
643  {
644  ResultsForOneRow.push_back( query->DataValue(k).ToString() );
645  }
646  Results.push_back(ResultsForOneRow);
647  }
648 
649  query->Delete();
650 
651  return Results;
652 }
653 
654 //------------------------------------------------------------------------------
655 
656 //------------------------------------------------------------------------------
657 std::vector< std::vector< std::string > >
659  vtkMySQLDatabase *DatabaseConnector,
660  const std::string & MainTable,
661  const std::vector< std::string > & SelectFields,
662  const std::string & field,
663  const std::string & value,
664  const std::vector< std::string > & JoinTablesOnTraceTable,
665  bool Distinct,
666  const std::vector<FieldWithValue> & iWhereOrConditions)
667 {
668  std::vector< std::vector< std::string > > Results;
669  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
670 
671  std::stringstream Querystream;
672  Querystream << "SELECT ";
673  if ( Distinct )
674  {
675  Querystream << "DISTINCT ";
676  }
677  unsigned int i;
678  for ( i = 0; i < SelectFields.size() - 1; i++ )
679  {
680  Querystream << SelectFields[i];
681  Querystream << ",";
682  }
683  Querystream << SelectFields[i];
684  Querystream << " FROM ";
685  Querystream << MainTable;
686  unsigned int j = 0;
687  while ( j < JoinTablesOnTraceTable.size() )
688  {
689  Querystream << " LEFT JOIN ";
690  Querystream << JoinTablesOnTraceTable[j];
691  Querystream << " ON ";
692  Querystream << JoinTablesOnTraceTable[j + 1];
693  Querystream << " ";
694  j = j + 2;
695  }
696  Querystream << "WHERE ( ";
697  Querystream << MainTable;
698  Querystream << ".";
699  Querystream << field;
700  Querystream << " = ";
701  Querystream << value;
702  Querystream << " AND (";
703  unsigned int k = 0;
704  while ( k < iWhereOrConditions.size() -1)
705  {
706  Querystream << iWhereOrConditions[k].Field;
707  Querystream << " = ";
708  Querystream << iWhereOrConditions[k].Value;
709  Querystream << " OR ";
710  k = k + 1;
711  }
712  Querystream << iWhereOrConditions[k].Field;
713  Querystream << " = ";
714  Querystream << iWhereOrConditions[k].Value;
715  Querystream << ") );";
716 
717  query->SetQuery( Querystream.str().c_str() );
718  if ( !query->Execute() )
719  {
720  itkGenericExceptionMacro(
721  << "return info Contours query failed"
722  << query->GetLastErrorText() );
723  DatabaseConnector->Close();
724  DatabaseConnector->Delete();
725  query->Delete();
726  return Results;
727  }
728  while ( query->NextRow() )
729  {
730  std::vector< std::string > ResultsForOneRow;
731  for ( int k = 0; k < query->GetNumberOfFields(); k++ )
732  {
733  ResultsForOneRow.push_back( query->DataValue(k).ToString() );
734  }
735  Results.push_back(ResultsForOneRow);
736  }
737 
738  query->Delete();
739 
740  return Results;
741 }
742 //------------------------------------------------------------------------------
743 
744 //------------------------------------------------------------------------------
745 std::vector< std::vector< std::string > >
747  vtkMySQLDatabase *DatabaseConnector,
748  const std::string & MainTable,
749  const std::vector< std::string > & SelectFields,
750  const std::vector< std::string > & WhereAndConditions,
751  const std::vector< std::string > & JoinTablesOnTraceTable,
752  bool Distinct)
753 {
754  std::vector< std::vector< std::string > > Results;
755  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
756 
757  std::stringstream Querystream;
758  Querystream << "SELECT ";
759  if ( Distinct )
760  {
761  Querystream << "DISTINCT ";
762  }
763  unsigned int i;
764  for ( i = 0; i < SelectFields.size() - 1; i++ )
765  {
766  Querystream << SelectFields[i];
767  Querystream << ",";
768  }
769  Querystream << SelectFields[i];
770  Querystream << " FROM (";
771  Querystream << MainTable;
772  unsigned int j = 0;
773  while ( j < JoinTablesOnTraceTable.size() - 1 )
774  {
775  Querystream << " LEFT JOIN ";
776  Querystream << JoinTablesOnTraceTable[j];
777  Querystream << " ON ";
778  Querystream << JoinTablesOnTraceTable[j + 1];
779  Querystream << " ";
780  j = j + 2;
781  }
782 
783  unsigned int k = 0;
784  Querystream << ") WHERE (";
785  while ( k < WhereAndConditions.size() - 2 )
786  {
787  Querystream << WhereAndConditions[k];
788  Querystream << " = ";
789  Querystream << WhereAndConditions[k + 1];
790  Querystream << " AND ";
791  k = k + 2;
792  }
793 
794  Querystream << WhereAndConditions[k];
795  Querystream << " = ";
796  Querystream << WhereAndConditions[k + 1];
797  Querystream << ");";
798 
799  query->SetQuery( Querystream.str().c_str() );
800  if ( !query->Execute() )
801  {
802  itkGenericExceptionMacro(
803  << "return info Contours query failed"
804  << query->GetLastErrorText() );
805  DatabaseConnector->Close();
806  DatabaseConnector->Delete();
807  query->Delete();
808  return Results;
809  }
810  while ( query->NextRow() )
811  {
812  std::vector< std::string > ResultsForOneRow;
813  for ( int kk = 0; kk < query->GetNumberOfFields(); kk++ )
814  {
815  ResultsForOneRow.push_back( query->DataValue(kk).ToString() );
816  }
817  Results.push_back(ResultsForOneRow);
818  }
819 
820  query->Delete();
821 
822  return Results;
823 }
824 
825 //------------------------------------------------------------------------------
826 
827 //------------------------------------------------------------------------------
828 
829 std::vector< std::string >
830 GetSamefieldFromTwoTables( vtkMySQLDatabase *DatabaseConnector,
831  const std::string & iTableOne,
832  const std::string & iTableTwo,
833  const std::string & iColumn,
834  const std::string & iField,
835  const std::string & iValue,
836  const std::string & iFieldTwo,
837  const std::vector< std::string > & iListConditionsTwo)
838 {
839  std::vector< std::string > result;
840  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
841  std::stringstream querystream;
842  querystream << SelectQueryStreamCondition(iTableOne, iColumn, iField, iValue);
843  querystream << " UNION ";
844  querystream << SelectQueryStreamListConditions(iTableTwo, iColumn,
845  iFieldTwo, iListConditionsTwo);
846 
847  query->SetQuery( querystream.str().c_str() );
848  if ( !query->Execute() )
849  {
850  itkGenericExceptionMacro(
851  << "List of all values for 2 tables query failed"
852  << query->GetLastErrorText() );
853  DatabaseConnector->Close();
854  DatabaseConnector->Delete();
855  query->Delete();
856  return result;
857  }
858 
859  while ( query->NextRow() )
860  {
861  for ( int i = 0; i < query->GetNumberOfFields(); i++ )
862  {
863  result.push_back( query->DataValue(i).ToString() );
864  }
865  }
866 
867  query->Delete();
868 
869  return result;
870 }
871 //------------------------------------------------------------------------------
872 
873 //------------------------------------------------------------------------------
874 std::list< unsigned int >
875 GetTwoFieldsFromTwoTables( vtkMySQLDatabase *iDatabaseConnector,
876  const std::string & iTableOne,
877  const std::string & iTableTwo,
878  const FieldWithValue & iOnCondition,
879  const std::string & iColumnOne,
880  const std::string & iColumnTwo,
881  const std::string & iField,
882  const std::vector< std::string > & iListValues,
883  bool Distinct)
884 {
885  std::string QueryString = SelectQueryStreamListConditions(iTableOne, iColumnOne, iField, iListValues, Distinct);
886  QueryString += " UNION ";
887  std::string What = GetLeftJoinTwoTables(iTableOne, iTableTwo, iOnCondition);
888  QueryString += SelectQueryStreamListConditions(What, iColumnTwo, iField, iListValues, Distinct);
889 
890  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
891 }
892 //------------------------------------------------------------------------------
893 
894 //------------------------------------------------------------------------------
895 std::vector< std::string >
896 GetSamefieldsFromTwoTables(vtkMySQLDatabase *DatabaseConnector,
897  const std::string & iTableOne,
898  const std::string & iTableTwo,
899  const std::string & iColumnOne,
900  const std::string & iColumnTwo,
901  const std::string & iField,
902  const std::string & iValue)
903 {
904  std::vector< std::string > result;
905  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
906  std::stringstream querystream;
907  querystream << SelectQueryStreamCondition(iTableOne, iColumnOne, iField, iValue);
908  querystream << " UNION ";
909  querystream << SelectQueryStreamCondition(iTableOne, iColumnTwo, iField, iValue);
910  querystream << " UNION ";
911  querystream << SelectQueryStreamCondition(iTableTwo, iColumnOne, iField, iValue);
912  querystream << " UNION ";
913  querystream << SelectQueryStreamCondition(iTableTwo, iColumnTwo, iField, iValue);
914 
915  query->SetQuery( querystream.str().c_str() );
916  if ( !query->Execute() )
917  {
918  itkGenericExceptionMacro(
919  << "List of all values for 2 tables query failed"
920  << query->GetLastErrorText() );
921  DatabaseConnector->Close();
922  DatabaseConnector->Delete();
923  query->Delete();
924  return result;
925  }
926 
927  while ( query->NextRow() )
928  {
929  for ( int i = 0; i < query->GetNumberOfFields(); i++ )
930  {
931  result.push_back( query->DataValue(i).ToString() );
932  }
933  }
934 
935  query->Delete();
936 
937  return result;
938 }
939 
940 //------------------------------------------------------------------------------
941 
942 //------------------------------------------------------------------------------
943 std::vector< std::string >
944 GetSamefieldsFromTwoTables(vtkMySQLDatabase *DatabaseConnector,
945  const std::string & iTableOne,
946  const std::string & iTableTwo,
947  const std::string & iColumnOne,
948  const std::string & iColumnTwo,
949  const std::string & iField,
950  const std::string & iValue,
951  const std::string & iFieldTwo,
952  const std::vector< std::string > & iListConditionsTwo)
953 {
954  std::vector< std::string > result;
955  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
956  std::stringstream querystream;
957  querystream << SelectQueryStreamCondition(iTableOne, iColumnOne, iField, iValue);
958  querystream << " UNION ";
959  querystream << SelectQueryStreamCondition(iTableOne, iColumnTwo, iField, iValue);
960  querystream << " UNION ";
961  querystream << SelectQueryStreamListConditions(iTableTwo, iColumnOne,
962  iFieldTwo, iListConditionsTwo);
963  querystream << " UNION ";
964  querystream << SelectQueryStreamListConditions(iTableTwo, iColumnTwo,
965  iFieldTwo, iListConditionsTwo);
966 
967  query->SetQuery( querystream.str().c_str() );
968  if ( !query->Execute() )
969  {
970  itkGenericExceptionMacro(
971  << "List of all values for 2 tables query failed"
972  << query->GetLastErrorText() );
973  DatabaseConnector->Close();
974  DatabaseConnector->Delete();
975  query->Delete();
976  return result;
977  }
978 
979  while ( query->NextRow() )
980  {
981  for ( int i = 0; i < query->GetNumberOfFields(); i++ )
982  {
983  result.push_back( query->DataValue(i).ToString() );
984  }
985  }
986 
987  query->Delete();
988 
989  return result;
990 }
991 
992 //------------------------------------------------------------------------------
993 
994 //------------------------------------------------------------------------------
995 std::vector< std::string > GetSameFieldsFromSeveralTables(
996  vtkMySQLDatabase *DatabaseConnector,
997  const std::vector< std::string > & iColumnNames,
998  const std::vector< std::string > & iVectorTablesNames,
999  const std::vector< std::string > & iVectorConditionFieldNames,
1000  const std::vector< std::vector< std::string > > & iVectorConditionsValues)
1001 {
1002  std::vector< std::string > result;
1003  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
1004  std::stringstream querystream;
1005  unsigned int i = 0;
1006  while ( i < iVectorTablesNames.size() - 1 )
1007  {
1008  for ( unsigned int j = 0; j < iColumnNames.size(); j++ )
1009  {
1010  querystream << SelectQueryStreamListConditions(
1011  iVectorTablesNames[i], iColumnNames[j], iVectorConditionFieldNames[i],
1012  iVectorConditionsValues[i], true);
1013  querystream << " UNION DISTINCT ";
1014  }
1015  i++;
1016  }
1017  unsigned j = 0;
1018  while ( j < iColumnNames.size() - 1 )
1019  {
1020  querystream << SelectQueryStreamListConditions(
1021  iVectorTablesNames[i], iColumnNames[j], iVectorConditionFieldNames[i],
1022  iVectorConditionsValues[i], true);
1023  querystream << " UNION DISTINCT ";
1024  j++;
1025  }
1026 
1027  querystream << SelectQueryStreamListConditions(
1028  iVectorTablesNames[i], iColumnNames[j], iVectorConditionFieldNames[i],
1029  iVectorConditionsValues[i], true);
1030 
1031  query->SetQuery( querystream.str().c_str() );
1032  if ( !query->Execute() )
1033  {
1034  itkGenericExceptionMacro(
1035  << "List of same field for different tables query failed"
1036  << query->GetLastErrorText() );
1037  DatabaseConnector->Close();
1038  DatabaseConnector->Delete();
1039  query->Delete();
1040  return result;
1041  }
1042 
1043  while ( query->NextRow() )
1044  {
1045  for ( int k = 0; k < query->GetNumberOfFields(); k++ )
1046  {
1047  result.push_back( query->DataValue(k).ToString() );
1048  }
1049  }
1050 
1051  query->Delete();
1052 
1053  return result;
1054 }
1055 
1056 //-------------------------------------------------------------------------
1057 
1058 //-------------------------------------------------------------------------
1059 //SELECT iColumnName FROM TableName WHERE ( (iFieldOne =
1060 // iVectorConditionFieldOne(i)
1061 // OR iFieldOne = iVectorConditionFieldOne(i+1...) AND (iFieldTwo =
1062 // iVectorConditionFieldTwo(j) OR
1063 //iVectorConditionFieldTwo(j+1)... ) );
1064 std::vector< std::string >
1066  vtkMySQLDatabase *DatabaseConnector,
1067  const std::string & iColumnName,
1068  const std::string & iTableName,
1069  const std::string & iFieldOne,
1070  const std::vector< std::string > & iVectorConditionFieldOne,
1071  const std::string & iFieldTwo,
1072  const std::vector< std::string > & iVectorConditionFieldTwo)
1073 {
1074  std::vector< std::string > result;
1075  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
1076 
1077  std::stringstream querystream;
1078  querystream << "SELECT ";
1079  querystream << iColumnName;
1080  querystream << " FROM ";
1081  querystream << iTableName;
1082  querystream << " WHERE ((";
1083  unsigned int i = 0;
1084  while ( i < iVectorConditionFieldOne.size() - 1 )
1085  {
1086  querystream << iFieldOne;
1087  querystream << " = '";
1088  querystream << iVectorConditionFieldOne.at(i);
1089  querystream << "' OR ";
1090  i++;
1091  }
1092  querystream << iFieldOne;
1093  querystream << " = '";
1094  querystream << iVectorConditionFieldOne.at(i);
1095  querystream << "') AND (";
1096 
1097  unsigned j = 0;
1098  while ( j < iVectorConditionFieldTwo.size() - 1 )
1099  {
1100  querystream << iFieldTwo;
1101  querystream << " = '";
1102  querystream << iVectorConditionFieldTwo.at(j);
1103  querystream << "' OR ";
1104  j++;
1105  }
1106  querystream << iFieldTwo;
1107  querystream << " = '";
1108  querystream << iVectorConditionFieldTwo.at(j);
1109  querystream << "'))";
1110 
1111  query->SetQuery( querystream.str().c_str() );
1112  if ( !query->Execute() )
1113  {
1114  itkGenericExceptionMacro(
1115  << "GetSpecificValueFromOneTableWithConditionsOnTwoColumns query failed"
1116  << query->GetLastErrorText() );
1117  DatabaseConnector->Close();
1118  DatabaseConnector->Delete();
1119  query->Delete();
1120  return result;
1121  }
1122 
1123  while ( query->NextRow() )
1124  {
1125  for ( int k = 0; k < query->GetNumberOfFields(); k++ )
1126  {
1127  result.push_back( query->DataValue(k).ToString() );
1128  }
1129  }
1130 
1131  query->Delete();
1132 
1133  return result;
1134 }
1135 
1136 //-------------------------------------------------------------------------
1137 
1138 //-------------------------------------------------------------------------
1139 std::list< unsigned int >
1140 GetColumnForBoundedValue(const std::string & iColumnName,
1141  const std::string & iTableName,
1142  const std::string & iImgSessionID,
1143  const std::string & iCoordType,
1144  const std::string & iValue,
1145  vtkMySQLDatabase *DatabaseConnector)
1146 {
1147  std::list< unsigned int > result;
1148  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
1149 
1150  std::stringstream querystream;
1151  querystream << "SELECT T1.";
1152  querystream << iColumnName;
1153  querystream << " FROM (SELECT ";
1154  querystream << iTableName;
1155  querystream << ".";
1156  querystream << iColumnName;
1157  querystream << " FROM ";
1158  querystream << iTableName;
1159  querystream << " LEFT JOIN coordinate ON ";
1160  querystream << iTableName;
1161  querystream << ".CoordIDMax = coordinate.coordid WHERE (ImagingsessionID = ";
1162  querystream << iImgSessionID;
1163  querystream << " AND Coordinate.";
1164  querystream << iCoordType;
1165  querystream << " > ";
1166  querystream << iValue;
1167  querystream << ")) AS T1 INNER JOIN (SELECT ";
1168  querystream << iTableName;
1169  querystream << ".";
1170  querystream << iColumnName;
1171  querystream << " FROM ";
1172  querystream << iTableName;
1173  querystream << " LEFT JOIN coordinate ON ";
1174  querystream << iTableName;
1175  querystream << ".CoordIDMin = coordinate.coordid WHERE (ImagingsessionID = ";
1176  querystream << iImgSessionID;
1177  querystream << " AND Coordinate.";
1178  querystream << iCoordType;
1179  querystream << " < ";
1180  querystream << iValue;
1181  querystream << ")) AS T2 on T1.";
1182  querystream << iColumnName;
1183  querystream << " = T2.";
1184  querystream << iColumnName;
1185 
1186  query->SetQuery( querystream.str().c_str() );
1187  if ( !query->Execute() )
1188  {
1189  itkGenericExceptionMacro(
1190  << "GetColumnForBoundedValue query failed"
1191  << query->GetLastErrorText() );
1192  DatabaseConnector->Close();
1193  DatabaseConnector->Delete();
1194  query->Delete();
1195  return result;
1196  }
1197 
1198  while ( query->NextRow() )
1199  {
1200  for ( int k = 0; k < query->GetNumberOfFields(); k++ )
1201  {
1202  result.push_back( query->DataValue(k).ToInt() );
1203  }
1204  }
1205 
1206  query->Delete();
1207 
1208  return result;
1209 }
1210 
1211 //-------------------------------------------------------------------------
1212 
1213 //-------------------------------------------------------------------------
1214 std::list< unsigned int > GetSpecificValuesEqualToZero(
1215  vtkMySQLDatabase *iDatabaseConnector,
1216  const std::string & iColumnName,
1217  const std::string & iTableName,
1218  const std::vector< std::string > & iVectorConditionFieldOne,
1219  const std::string & iFieldTwo)
1220 {
1221  std::list< unsigned int > result;
1222  vtkSQLQuery * query = iDatabaseConnector->GetQueryInstance();
1223  std::stringstream querystream;
1224  std::string temp = SelectQueryStreamListConditions(iTableName,
1225  iColumnName, iColumnName,
1226  iVectorConditionFieldOne);
1227  temp = temp.substr(0, temp.size() - 1);
1228  querystream << temp;
1229  querystream << " AND ";
1230  querystream << iFieldTwo;
1231  querystream << " = 0);";
1232 
1233  query->SetQuery( querystream.str().c_str() );
1235  if ( !query->Execute() )
1236  {
1237  itkGenericExceptionMacro(
1238  << "GetColumnForBoundedValue query failed"
1239  << query->GetLastErrorText() );
1240  iDatabaseConnector->Close();
1241  iDatabaseConnector->Delete();
1242  query->Delete();
1243  return result;
1244  }
1245 
1246  while ( query->NextRow() )
1247  {
1248  for ( int k = 0; k < query->GetNumberOfFields(); k++ )
1249  {
1250  result.push_back( query->DataValue(k).ToInt() );
1251  }
1252  }
1253 
1254  query->Delete();
1255 
1256  return result;
1257 }
1258 
1259 //-------------------------------------------------------------------------
1260 
1261 //-------------------------------------------------------------------------
1262 std::list< unsigned int > GetAllSelectedValuesFromTwoTables(
1263  vtkMySQLDatabase *iDatabaseConnector,
1264  const std::string & iTableOne,
1265  const std::string & iTableTwo,
1266  const std::string & iColumn,
1267  const FieldWithValue & iJoinCondition,
1268  const std::vector< FieldWithValue > & iFieldsWithValues,
1269  bool Distinct)
1270 {
1271  std::string Where = GetLeftJoinTwoTables(iTableOne, iTableTwo, iJoinCondition);
1272  std::string QueryString = SelectQueryStreamListConditions(Where, iColumn,
1273  iFieldsWithValues, "AND", Distinct);
1274 
1275  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1276 }
1277 
1278 //-------------------------------------------------------------------------
1279 
1280 //-------------------------------------------------------------------------
1281 std::vector< std::string >
1283  vtkMySQLDatabase *iDatabaseConnector,
1284  const std::string & iTableOne,
1285  const std::string & iTableTwo,
1286  const std::vector< std::string > & iSelectedFields,
1287  const FieldWithValue & iJoinCondition,
1288  const std::vector< FieldWithValue > & iFieldsWithValues,
1289  std::string iConditionConnector,
1290  std::string ColumnNameOrder)
1291 {
1292  std::string Where = GetLeftJoinTwoTables(iTableOne, iTableTwo, iJoinCondition);
1293 
1294  std::string QueryString = SelectQueryStreamListConditions(Where, iSelectedFields,
1295  iFieldsWithValues, iConditionConnector, false, ColumnNameOrder);
1296  /*if (!ColumnNameOrder.empty())
1297  {
1298  QueryString = SelectQueryStreamListConditions(Where, iSelectedFields, iFieldsWithValues, "AND",
1299  false, ColumnNameOrder);
1300  }*/
1301 
1302  return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
1303 }
1304 
1305 //-------------------------------------------------------------------------
1306 
1307 //-------------------------------------------------------------------------
1308 std::list< unsigned int >
1309 GetAllSelectedValuesFromTwoTables(vtkMySQLDatabase *iDatabaseConnector,
1310  const std::string & iTableOne,
1311  const std::string & iTableTwo,
1312  const std::string & iColumn,
1313  const FieldWithValue & iJoinCondition,
1314  const std::string & iField,
1315  const std::vector< std::string > & iVectorValues,
1316  bool Distinct,
1317  bool NonNULLRows)
1318 {
1319  std::string Where = GetLeftJoinTwoTables(iTableOne, iTableTwo, iJoinCondition, NonNULLRows);
1320  std::string QueryString = SelectQueryStreamListConditions(Where, iColumn, iField, iVectorValues, Distinct);
1321 
1322  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1323 }
1324 
1325 //-------------------------------------------------------------------------
1326 
1327 //-------------------------------------------------------------------------
1328 std::list< unsigned int >
1329 GetAllSelectedValuesFromTwoTables(vtkMySQLDatabase *iDatabaseConnector,
1330  const std::string & iTableOne,
1331  const std::string & iTableTwo,
1332  const std::string & iColumn,
1333  const FieldWithValue & iJoinCondition,
1334  const std::string & iField,
1335  const std::vector< std::string > & iVectorValues,
1336  const FieldWithValue & iAndCondition)
1337 {
1338  std::string Where = GetLeftJoinTwoTables(iTableOne, iTableTwo, iJoinCondition);
1339  std::string Conditions = "(";
1340 
1341  Conditions += GetConditions< std::string >(iField, iVectorValues, "OR");
1342  Conditions += " AND ";
1343  std::vector< FieldWithValue > AndCondition(1);
1344  AndCondition[0] = iAndCondition;
1345  Conditions += GetConditions(AndCondition);
1346  Conditions += ")";
1347  std::string QueryString = SelectGeneralQueryConditions(iColumn, Where, Conditions);
1348 
1349  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1350 }
1351 
1352 //-------------------------------------------------------------------------
1353 
1354 //-------------------------------------------------------------------------
1355 std::list<unsigned int>
1356 GetAllSelectedValuesFromTwoTables( vtkMySQLDatabase *iDatabaseConnector,
1357  const std::string & iTableOne,
1358  const std::string & iTableTwo,
1359  const std::vector<std::string> & iSelectedFields,
1360  const FieldWithValue & iJoinCondition,
1361  const std::string & iField,
1362  const std::string & iValue,
1363  bool NonNULLRows)
1364 {
1365  std::string Where = GetLeftJoinTwoTables(iTableOne, iTableTwo, iJoinCondition, true);
1366  std::string QueryString = SelectQueryStreamCondition(Where, iSelectedFields,
1367  iField, iValue);
1368 
1369  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1370 }
1371 //-------------------------------------------------------------------------
1372 
1373 //-------------------------------------------------------------------------
1374 int GetMaxValueFromTwoTables(vtkMySQLDatabase *iDatabaseConnector,
1375  const std::string & iTableOne,
1376  const std::string & iTableTwo,
1377  const std::string & iColumn,
1378  const FieldWithValue & iJoinCondition,
1379  const std::string & iField,
1380  const std::vector< std::string > & iVectorValues,
1381  const FieldWithValue & iAndCondition)
1382 {
1383  std::string What = "MAX(";
1384 
1385  What += iColumn;
1386  What += ")";
1387  std::string Where = GetLeftJoinTwoTables(iTableOne, iTableTwo, iJoinCondition);
1388  std::string Conditions = "(";
1389  Conditions += GetConditions< std::string >(iField, iVectorValues, "OR");
1390  Conditions += " AND ";
1391  std::vector< FieldWithValue > AndCondition(1);
1392  AndCondition[0] = iAndCondition;
1393  Conditions += GetConditions(AndCondition);
1394  Conditions += ")";
1395  std::string QueryString = SelectGeneralQueryConditions(What, Where, Conditions);
1396  return ExecuteSelectQueryOneValue< int >(iDatabaseConnector, QueryString);
1397 }
1398 
1399 //-------------------------------------------------------------------------
1400 
1401 //-------------------------------------------------------------------------
1402 std::list< unsigned int >
1404  vtkMySQLDatabase *iDatabaseConnector,
1405  const std::string & iTableOne,
1406  const std::string & iTableTwo,
1407  const std::string & iColumn,
1408  const FieldWithValue & iJoinCondition,
1409  const std::string & iField,
1410  const std::vector< std::string > & iVectorValues,
1411  const FieldWithValue & iAndCondition)
1412 {
1413  std::string Where = GetLeftJoinTwoTables(iTableOne, iTableTwo, iJoinCondition);
1414  std::string Conditions = "(";
1415 
1416  Conditions += GetConditions< std::string >(iField, iVectorValues, "OR");
1417  Conditions += " AND ";
1418  std::vector< FieldWithValue > AndCondition(1);
1419  AndCondition[0] = iAndCondition;
1420  Conditions += GetConditions(AndCondition);
1421  Conditions += ")";
1422  std::string QueryString = SelectGeneralQueryConditions(iColumn, Where, Conditions);
1423  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1424 }
1425 
1426 //-------------------------------------------------------------------------
1427 
1428 //-------------------------------------------------------------------------
1429 std::list< unsigned int >
1430 GetDoublonValuesFromTwoTables(vtkMySQLDatabase *iDatabaseConnector,
1431  const std::string & iTableOne,
1432  const std::string & iTableTwo,
1433  const std::string & iColumn,
1434  const FieldWithValue & iJoinCondition,
1435  const std::string & iField,
1436  const std::vector< std::string > & iVectValues) //, std::string GroupByColumn)
1437 
1438 {
1439  std::string Where = GetLeftJoinTwoTables(iTableOne, iTableTwo, iJoinCondition);
1440  std::string Conditions = GetConditions< std::string >(iField, iVectValues, "OR");
1441 
1442  Conditions += GetGroupBy(iColumn, 1);
1443  std::string QueryString = SelectQueryStreamCondition(Where, iColumn, Conditions);
1444 
1445  return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1446 }
1447 
1448 //-------------------------------------------------------------------------
1449 
1450 //-------------------------------------------------------------------------
1451 std::vector< std::string >
1452 GetOrderByWithLimit(vtkMySQLDatabase *iDatabaseConnector,
1453  const std::string & iColumnName,
1454  const std::string & iTableName,
1455  const std::string & iField,
1456  const std::string & iValue,
1457  bool ASC,
1458  const std::string & iNumberLimit)
1459 {
1460  std::stringstream QueryStream;
1461 
1462  QueryStream << SelectQueryStreamCondition(iTableName, iColumnName, iField, iValue);
1463  QueryStream << " ORDER BY ";
1464  QueryStream << iColumnName;
1465  if ( ASC )
1466  {
1467  QueryStream << " ASC LIMIT ";
1468  }
1469  else
1470  {
1471  QueryStream << " DESC LIMIT ";
1472  }
1473  QueryStream << iNumberLimit;
1474  return ExecuteSelectQuery< std::vector< std::string > >( iDatabaseConnector, QueryStream.str() );
1475 }
1476 
1477 //------------------------------------------------------------------------------
1478 
1479 //------------------------------------------------------------------------------
1480 std::string
1481 GetCoordinateValuesQueryString(const std::string & iTableName,
1482  const std::string & iField,
1483  const std::string & iValue,
1484  bool iMin)
1485 {
1486  std::stringstream Querystream;
1487 
1488  Querystream << "SELECT XCoord,YCoord,ZCoord,TCoord,";
1489  Querystream << iTableName;
1490  Querystream << "ID from ";
1491  Querystream << iTableName;
1492  Querystream << " LEFT JOIN coordinate on ";
1493  Querystream << iTableName;
1494  Querystream << ".coordID";
1495  if ( iMin )
1496  {
1497  Querystream << "Min = coordinate.coordID WHERE ";
1498  }
1499  else
1500  {
1501  Querystream << "Max = coordinate.coordID WHERE ";
1502  }
1503  Querystream << iField;
1504  Querystream << " = ";
1505  Querystream << iValue;
1506 
1507  return Querystream.str();
1508 }
1509 
1510 //------------------------------------------------------------------------------
1511 
1512 //------------------------------------------------------------------------------
1513 void
1515  unsigned int iTCoord,
1516  const std::string & iPoints,
1517  const std::string & iTraceName)
1518 {
1519  ioStructure.TCoord = iTCoord;
1520  vtkPolyData *output = vtkPolyData::New();
1521  if ( !iPoints.empty() )
1522  {
1523  if ( iTraceName.compare("contour") == 0 )
1524  {
1525  vtkSmartPointer< vtkPolyDataMySQLContourReader > convert_reader =
1526  vtkSmartPointer< vtkPolyDataMySQLContourReader >::New();
1527  output->DeepCopy( convert_reader->GetPolyData(iPoints) );
1528  }
1529  else
1530  {
1531  if ( iTraceName.compare("mesh") == 0 )
1532  {
1533  vtkIdType N;
1534  std::stringstream str(iPoints);
1535  str >> N;
1536  if ( N > 0 )
1537  {
1538  vtkSmartPointer< vtkPolyDataMySQLMeshReader > convert_reader =
1539  vtkSmartPointer< vtkPolyDataMySQLMeshReader >::New();
1540  output->DeepCopy( convert_reader->GetPolyData(iPoints) );
1541  }
1542  else
1543  {
1544  output->Delete();
1545  output = NULL;
1546  }
1547  }
1548  }
1549  ioStructure.Nodes = output;
1550  }
1551 }
1552 
1553 //------------------------------------------------------------------------------
1554 
1555 //------------------------------------------------------------------------------
1557  unsigned int iTCoord,
1558  const std::string & iPoints,
1559  const std::string & iTraceName)
1560 {
1561  (void)iTCoord;
1562  if ( iTraceName != "track" )
1563  {
1564  std::cout << "this method is only for track and don't have TCoord at this moment";
1565  std::cout << "Debug: In " << __FILE__ << ", line " << __LINE__;
1566  std::cout << std::endl;
1567  return;
1568  }
1569 
1570  if ( !iPoints.empty() )
1571  {
1572  vtkSmartPointer< vtkPolyDataMySQLTrackReader > convert_reader =
1573  vtkSmartPointer< vtkPolyDataMySQLTrackReader >::New();
1574  vtkIdType N;
1575  std::stringstream str(iPoints);
1576  str >> N;
1577  if ( N > 0 )
1578  {
1579  vtkPolyData *output = vtkPolyData::New();
1580  output->DeepCopy( convert_reader->GetPolyData(iPoints) );
1581  ioStructure.PointsMap = convert_reader->GetMap(iPoints);
1582  ioStructure.Nodes = output;
1583  }
1584  }
1585  else
1586  {
1587  ioStructure.Nodes = NULL;
1588  }
1589 }
1590 //------------------------------------------------------------------------------
1591 
1592 //------------------------------------------------------------------------------
1593 void
1595  unsigned int iTrackRootID,
1596  const std::string & iPoints,
1597  const std::string & iTraceName)
1598 {
1599  (void) iPoints;
1600  if ( iTraceName != "lineage" )
1601  {
1602  std::cout << "this method is only for lineage";
1603  std::cout << "Debug: In " << __FILE__ << ", line " << __LINE__;
1604  std::cout << std::endl;
1605  return;
1606  }
1607  ioStructure.TrackRootID = iTrackRootID;
1608 }
1609 //------------------------------------------------------------------------------
1610 
1611 //------------------------------------------------------------------------------
1612 std::list< double * >
1613 GetCenterBoundingBoxes(vtkMySQLDatabase *DatabaseConnector,
1614  const std::string & iTableName,
1615  const std::string & iField,
1616  const std::string & iValue)
1617 {
1618  std::list< double * > Results = std::list< double * >();
1619  vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
1620  std::stringstream Querystream;
1621  Querystream << "SELECT XCoord,YCoord,ZCoord,TCoord FROM (";
1622  Querystream << GetCoordinateValuesQueryString(iTableName, iField, iValue, true);
1623  Querystream << " UNION ";
1624  Querystream << GetCoordinateValuesQueryString(iTableName, iField, iValue, false);
1625  Querystream << " ORDER BY ";
1626  Querystream << iTableName;
1627  Querystream << "ID ASC ) AS T1";
1628 
1629  query->SetQuery( Querystream.str().c_str() );
1630  if ( !query->Execute() )
1631  {
1632  itkGenericExceptionMacro(
1633  << "return coordmin and max query failed"
1634  << query->GetLastErrorText() );
1635  DatabaseConnector->Close();
1636  DatabaseConnector->Delete();
1637  query->Delete();
1638  return Results;
1639  }
1640 
1641  while ( query->NextRow() )
1642  {
1643  double *Center = new double[4];
1644  double minx, miny, minz, mint, maxx, maxy, maxz, maxt;
1645  minx = query->DataValue(0).ToDouble();
1646  miny = query->DataValue(1).ToDouble();
1647  minz = query->DataValue(2).ToDouble();
1648  mint = query->DataValue(3).ToDouble();
1649  if ( query->NextRow() )
1650  {
1651  maxx = query->DataValue(0).ToDouble();
1652  maxy = query->DataValue(1).ToDouble();
1653  maxz = query->DataValue(2).ToDouble();
1654  maxt = query->DataValue(3).ToDouble();
1655  }
1656  else
1657  {
1658  std::cout << "pb query to return center bounding box";
1659  std::cout << "Debug: In " << __FILE__ << ", line " << __LINE__;
1660  std::cout << std::endl;
1661  return Results;
1662  }
1663  Center[0] = ( minx + maxx ) / 2;
1664  Center[1] = ( miny + maxy ) / 2;
1665  Center[2] = ( minz + maxz ) / 2;
1666  Center[3] = ( mint + maxt ) / 2;
1667  Results.push_back(Center);
1668  }
1669  return Results;
1670 }
1671 //------------------------------------------------------------------------------
1672 
1673 //------------------------------------------------------------------------------
1675  vtkMySQLDatabase *DatabaseConnector,
1676  unsigned int iImagingSession,
1677  const std::string & iTrace)
1678 {
1679  std::string What = "COUNT(*)";
1680  std::string Where = iTrace;
1681  std::string Condition = "ImagingSessionID = ";
1682  std::stringstream s;
1683  s << iImagingSession;
1684  Condition += s.str();
1685 
1686  std::string QueryString = SelectGeneralQueryConditions(What,Where,Condition);
1687 
1688  return ExecuteSelectQueryOneValue< int >(DatabaseConnector,
1689  QueryString);
1690 }
1691 //------------------------------------------------------------------------------
1692 
1693 //------------------------------------------------------------------------------
1695  vtkMySQLDatabase *DatabaseConnector,
1696  unsigned int iImagingSession,
1697  const std::string & iTrace,
1698  int iTimePoint)
1699 {
1700  // What
1701  std::string What = "COUNT(*)";
1702 
1703  // Where
1704  FieldWithValue CoordIDMin =
1705  { "CoordIDMin",
1706  "CoordID",
1707  "=" };
1708  std::string Where = GetLeftJoinTwoTables(iTrace,
1709  "coordinate",
1710  CoordIDMin);
1711 
1712  // Condition
1713  std::string Condition = "(ImagingSessionID = ";
1714  std::stringstream s;
1715  s << iImagingSession;
1716  Condition += s.str();
1717  Condition += " AND TCoord = ";
1718  std::stringstream timePoint;
1719  timePoint << iTimePoint;
1720  Condition += timePoint.str() + ")";
1721 
1722  std::string QueryString = SelectGeneralQueryConditions(What,Where,Condition);
1723 
1724  int test = ExecuteSelectQueryOneValue< int >(DatabaseConnector,
1725  QueryString);
1726 
1727  return test;
1728 }
1729 //------------------------------------------------------------------------------
1730 
1731 //------------------------------------------------------------------------------
1732 /*
1733 int NumberOfTimePointsForGivenImagingSession(
1734  vtkMySQLDatabase *DatabaseConnector,
1735  unsigned int iImagingSession)
1736 {
1737  FieldWithValue CoordMaxCondition =
1738  { "CoordIDMax",
1739  "CoordID",
1740  "=" };
1741 
1742  std::string What = GetLeftJoinTwoTables("imagingsession",
1743  "coordinate",
1744  CoordMaxCondition);
1745  // std::string What = "COUNT(*)";
1746  // std::string Where = iTrace;
1747  // std::string Condition = "ImagingSessionID = ";
1748  // std::stringstream s;
1749  // s << iImagingSession;
1750  // Condition += s.str();
1751  //
1752  // std::string QueryString = SelectGeneralQueryConditions(What,Where,Condition);
1753  // return ExecuteSelectQueryOneValue< int >(DatabaseConnector,
1754  // QueryString);
1755 }
1756 
1757 //------------------------------------------------------------------------------
1758 */
unsigned int TrackRootID
std::string SelectGeneralQueryConditions(const std::string &iWhat, const std::string &iWhere, const std::string &iConditions)
SELECT iWhat FROM iWhere WHERE iConditions.
std::list< unsigned int > GetListValuesFromTwoTablesAndCondition(vtkMySQLDatabase *iDatabaseConnector, const std::string &iTableOne, const std::string &iTableTwo, const std::string &iColumn, const FieldWithValue &iJoinCondition, const std::string &iField, const std::vector< std::string > &iVectorValues, const FieldWithValue &iAndCondition)
std::string SelectGeneralQuery(const std::string &iWhat, const std::string &iWhere, std::string iOrderByQuery)
SELECT iWhat FROM iWhere iOrderByQuery.
std::vector< std::vector< std::string > > GetValuesFromSeveralTables(vtkMySQLDatabase *DatabaseConnector, const std::string &MainTable, const std::vector< std::string > &SelectFields, const std::string &field, const std::string &value, const std::vector< std::string > &JoinTablesOnTraceTable, bool Distinct)
std::vector< std::string > GetSamefieldsFromTwoTables(vtkMySQLDatabase *DatabaseConnector, const std::string &iTableOne, const std::string &iTableTwo, const std::string &iColumnOne, const std::string &iColumnTwo, const std::string &iField, const std::string &iValue)
std::string GetCoordinateValuesQueryString(const std::string &iTableName, const std::string &iField, const std::string &iValue, bool iMin)
std::list< unsigned int > GetTwoFieldsFromTwoTables(vtkMySQLDatabase *iDatabaseConnector, const std::string &iTableOne, const std::string &iTableTwo, const FieldWithValue &iOnCondition, const std::string &iColumnOne, const std::string &iColumnTwo, const std::string &iField, const std::vector< std::string > &iListValues, bool Distinct)
void ModifyStructureWithSpecificities(ContourMeshStructure &ioStructure, unsigned int iTCoord, const std::string &iPoints, const std::string &iTraceName)
fill the TCoord and the attributes of the structure obtained from Points
std::list< unsigned int > GetSpecificValuesEqualToZero(vtkMySQLDatabase *iDatabaseConnector, const std::string &iColumnName, const std::string &iTableName, const std::vector< std::string > &iVectorConditionFieldOne, const std::string &iFieldTwo)
std::string GetAndORConditions(const FieldWithValue &iFirtsPartCondition, const std::string &iField, const std::vector< T > &iOrVectorValues)
(iFirstPartCondition AND (iField = iOrVectorValues1 OR iField = iOrVectorValues1...))
std::string GetGroupBy(const std::string &iColumn, unsigned int iNumberDoublons)
int MinValueForOneColumnInTable(vtkMySQLDatabase *DatabaseConnector, const std::string &ColumnName, const std::string &TableName, const std::string &field, const std::vector< std::string > &VectorValues)
SELECT MIN(ColumnName) FROM TableName WHERE (field = value1 or field = value2....".
std::list< double * > GetCenterBoundingBoxes(vtkMySQLDatabase *DatabaseConnector, const std::string &iTableName, const std::string &iField, const std::string &iValue)
std::string SelectQueryStream(const std::string &iTable, const std::string &iColumn, std::string iOrderByColumnName, std::string iAscDesc)
SELECT iColumn FROM iTable ORDER BY iOrderByColumnName iAscDesc;.
std::vector< std::string > GetSamefieldFromTwoTables(vtkMySQLDatabase *DatabaseConnector, const std::string &iTableOne, const std::string &iTableTwo, const std::string &iColumn, const std::string &iField, const std::string &iValue, const std::string &iFieldTwo, const std::vector< std::string > &iListConditionsTwo)
boost::unordered_map< std::string, std::string > MapTwoColumnsFromTable(vtkMySQLDatabase *DatabaseConnector, const std::vector< std::string > &iColumnNames, const std::string &iTableName, std::string iField, std::string iValue)
query: &quot;SELECT ColumnName1, ColumnName2 FROM TableName&quot;
std::vector< std::pair< std::string, std::string > > VectorTwoColumnsFromTable(vtkMySQLDatabase *DatabaseConnector, const std::string &ColumnNameOne, const std::string &ColumnNameTwo, const std::string &TableName, const std::string &OrderByColumnName)
SELECT ColumnNameOne,ColumnNameTwo FROM TableName ORDER BY ColumnName ASC.
std::list< unsigned int > GetAllSelectedValuesFromTwoTables(vtkMySQLDatabase *iDatabaseConnector, const std::string &iTableOne, const std::string &iTableTwo, const std::string &iColumn, const FieldWithValue &iJoinCondition, const std::vector< FieldWithValue > &iFieldsWithValues, bool Distinct)
std::string SelectQueryStreamCondition(const std::string &iTable, const std::string &iColumn, const std::string &iConditions, bool Distinct, std::string iOrderByColumnName, std::string iAscDesc)
SELECT (Distinct) iColumn FROM iTable WHERE iConditions (ORDER BY irderByColumnName iAscDesc);...
std::vector< std::string > ListAllValuesForOneColumn(vtkMySQLDatabase *DatabaseConnector, const std::string &ColumnName, const std::string &TableName, std::string OrderByColumnName)
SELECT ColumnName from TableName ORDER BY OrderbyColumnName.
std::string ReturnOnlyOneValue(vtkMySQLDatabase *DatabaseConnector, const std::string &TableName, const std::string &ColumnName, const std::string &field, const std::string &value)
SELECT ColunmName FROM TableName WHERE field=value limit 1.
PointsMapType PointsMap
std::vector< std::pair< std::string, std::string > > ListSpecificValuesForTwoColumns(vtkMySQLDatabase *DatabaseConnector, const std::string &TableName, const std::string &ColumnNameOne, const std::string &ColumnNameTwo, const std::string &field, const std::string &value, const std::string &ColumnNameOrder)
SELECT ColumnNameOne,ColumnName2 FROM TableName WHERE field = value ORDER BY ColumnNameOrder ASC"...
Structure which represent a track, and used for interaction between Visualization and TableWidget...
int MaxValueForOneColumnInTable(vtkMySQLDatabase *DatabaseConnector, const std::string &ColumnName, const std::string &TableName)
SELECT MAX(ColumnName) FROM TableName This is an overloaded member function, provided for convenience...
std::vector< std::string > ListSpecificValuesForOneColumn(vtkMySQLDatabase *iDatabaseConnector, const std::string &TableName, const std::string &ColumnName, const std::string &field, const std::string &value, bool ExcludeZero)
SELECT ColumnName FROM TableName WHERE field = value and ColumnName &lt;&gt; 0 (if excludezero) ...
std::string GetLeftJoinTwoTables(const std::string &iTableOne, const std::string &iTableTwo, const FieldWithValue &iOnCondition, bool NonNULLRows)
iTableOne LEFT JOIN iTableTwo ON iTableOne.iOnCondition/Field = iTableTwo.iOnCondition/Value ...
std::vector< std::string > GetSameFieldsFromSeveralTables(vtkMySQLDatabase *DatabaseConnector, const std::vector< std::string > &iColumnNames, const std::vector< std::string > &iVectorTablesNames, const std::vector< std::string > &iVectorConditionFieldNames, const std::vector< std::vector< std::string > > &iVectorConditionsValues)
std::vector< std::string > FindSeveralIDs(vtkMySQLDatabase *iDatabaseConnector, const std::string &TableName, const std::string &ColumnName, const std::vector< FieldWithValue > &iConditions)
"SELECT ColumnName FROM TableName WHERE (field1 = value1 AND field2 = value2...); ...
int FindOneID(vtkMySQLDatabase *DatabaseConnector, const std::string &TableName, const std::string &ColumnName, const std::string &field, const std::string &value)
SELECT ColumnName FROM TableName WHERE field = value.
Structure which represent a lineage, and used for interaction between Visualization and TableWidget...
vtkPolyData * Nodes
std::vector< std::pair< int, std::string > > ListSpecificValuesForTwoColumnsAndTwoTables(vtkMySQLDatabase *DatabaseConnector, const std::string &TableOne, const std::string &ColumnOne, const std::string &TableTwo, const std::string &ColumnTwo, const std::string &ForeignKey, const std::string &PrimaryKey, const std::string &field, const std::string &value)
std::list< unsigned int > GetDoublonValuesFromTwoTables(vtkMySQLDatabase *iDatabaseConnector, const std::string &iTableOne, const std::string &iTableTwo, const std::string &iColumn, const FieldWithValue &iJoinCondition, const std::string &iField, const std::vector< std::string > &iVectValues)
Structure which represent a contour or a mesh, and used for interaction between Visualization and Tab...
std::string SelectQueryStreamListConditions(const std::string &iTable, const std::string &iColumn, const std::string &iField, const std::vector< std::string > &iListValues, bool Distinct, std::string iConditionConnector)
SELECT (Distinct) iColumn FROM iTable WHERE iField = iValue Or/And...
std::vector< std::string > ListSpecificValuesForRow(vtkMySQLDatabase *DatabaseConnector, const std::string &TableName, const std::string &field, const std::string &value)
SELECT * FROM TableName WHERE field = value.
std::vector< std::string > GetSpecificValueFromOneTableWithConditionsOnTwoColumns(vtkMySQLDatabase *DatabaseConnector, const std::string &iColumnName, const std::string &iTableName, const std::string &iFieldOne, const std::vector< std::string > &iVectorConditionFieldOne, const std::string &iFieldTwo, const std::vector< std::string > &iVectorConditionFieldTwo)
std::string GetConditions(const std::vector< FieldWithValue > &iConditions, std::string iConditionConnector)
std::list< unsigned int > GetColumnForBoundedValue(const std::string &iColumnName, const std::string &iTableName, const std::string &iImgSessionID, const std::string &iCoordType, const std::string &iValue, vtkMySQLDatabase *DatabaseConnector)
int NumberOfElementForGivenImagingSessionAndTrace(vtkMySQLDatabase *DatabaseConnector, unsigned int iImagingSession, const std::string &iTrace)
int GetMaxValueFromTwoTables(vtkMySQLDatabase *iDatabaseConnector, const std::string &iTableOne, const std::string &iTableTwo, const std::string &iColumn, const FieldWithValue &iJoinCondition, const std::string &iField, const std::vector< std::string > &iVectorValues, const FieldWithValue &iAndCondition)
std::vector< std::string > GetOrderByWithLimit(vtkMySQLDatabase *iDatabaseConnector, const std::string &iColumnName, const std::string &iTableName, const std::string &iField, const std::string &iValue, bool ASC, const std::string &iNumberLimit)
int NumberOfElementForGivenImagingSessionAndTraceForGivenTimePoint(vtkMySQLDatabase *DatabaseConnector, unsigned int iImagingSession, const std::string &iTrace, int iTimePoint)