36 #include "vtkMySQLDatabase.h"
37 #include "vtkSQLQuery.h"
38 #include "vtkStdString.h"
39 #include "vtkSmartPointer.h"
40 #include "vtkVariant.h"
49 std::vector< std::string >
51 const std::string & ColumnName,
52 const std::string & TableName,
53 std::string OrderByColumnName)
55 std::string QueryString =
SelectQueryStream(TableName, ColumnName, OrderByColumnName);
57 return ExecuteSelectQuery< std::vector< std::string > >(DatabaseConnector, QueryString);
63 boost::unordered_map< std::string, std::string >
65 const std::vector< std::string > & iColumnNames,
66 const std::string & iTableName,
70 boost::unordered_map< std::string, std::string > Result =
71 boost::unordered_map< std::string, std::string >();
73 if ( iColumnNames.size() != 2 )
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;
80 std::string QueryString;
90 vtkSQLQuery *query = DatabaseConnector->GetQueryInstance();
91 query->SetQuery( QueryString.c_str() );
93 if ( !query->Execute() )
95 itkGenericExceptionMacro(
96 <<
"List of all fields query failed"
97 << query->GetLastErrorText() );
101 while ( query->NextRow() )
103 Result[query->DataValue(0).ToString()] = query->DataValue(1).ToString();
113 std::vector< std::pair< std::string, std::string > >
115 const std::string & ColumnNameOne,
116 const std::string & ColumnNameTwo,
117 const std::string & TableName,
118 const std::string & OrderByColumnName)
120 std::vector< std::pair< std::string, std::string > > result;
122 std::vector< std::string > ColumnNames(2);
123 ColumnNames[0] = ColumnNameOne;
124 ColumnNames[1] = ColumnNameTwo;
127 vtkSQLQuery *query = DatabaseConnector->GetQueryInstance();
128 query->SetQuery( QueryString.c_str() );
129 if ( !query->Execute() )
131 itkGenericExceptionMacro(
132 <<
"select 2 columns query failed"
133 << query->GetLastErrorText() );
134 DatabaseConnector->Close();
135 DatabaseConnector->Delete();
140 while ( query->NextRow() )
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);
156 std::vector< std::string >
158 const std::string & TableName,
159 const std::string & field,
160 const std::string & value)
164 return ExecuteSelectQuery< std::vector< std::string > >(DatabaseConnector, QueryString);
171 const std::string & TableName,
172 const std::string & ColumnName,
173 const std::string & field,
174 const std::string & value)
178 std::string QueryString =
181 std::vector< std::string > Results = ExecuteSelectQuery< std::vector< std::string > >(
182 DatabaseConnector, QueryString);
183 if ( Results.size() > 1 )
185 std::cout <<
"there is not an unique ID";
186 std::cout <<
"Debug: In " << __FILE__ <<
", line " << __LINE__;
187 std::cout << std::endl;
190 if ( !Results.empty() )
192 ID = atoi( Results[0].c_str() );
201 const std::string & TableName,
202 const std::string & ColumnName,
203 const std::vector< FieldWithValue > & iConditions)
206 ColumnName, iConditions,
"AND");
209 std::vector< int > Results = ExecuteSelectQuery< std::vector< int > >(
210 DatabaseConnector, QueryString);
212 if ( !Results.empty() )
223 const std::string & TableName,
224 const std::string & ColumnName,
225 const std::vector< FieldWithValue > & iConditions)
228 ColumnName, iConditions,
"AND");
230 return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
237 vtkMySQLDatabase *iDatabaseConnector,
238 const std::string & TableName,
239 const std::string & ColumnName,
240 const std::string & field,
241 const std::string & value,
244 std::vector< FieldWithValue > VectorConditions;
246 VectorConditions.push_back(EqualValue);
250 VectorConditions.push_back(DiffZero);
253 ColumnName, VectorConditions,
"AND");
255 return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
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)
271 return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
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,
286 std::string Conditions;
290 std::vector< FieldWithValue > VectorConditions(1);
292 VectorConditions[0] = DiffZero;
295 Conditions = Conditions.substr(0, Conditions.size() - 1);
296 Conditions +=
" AND ";
305 return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
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,
320 std::vector< unsigned int > VectorValues(
321 iListValues.begin(), iListValues.end() );
322 std::string Conditions;
325 std::vector< FieldWithValue > VectorConditions(1);
327 VectorConditions[0] = DiffZero;
330 Conditions = Conditions.substr(0, Conditions.size() - 1);
331 if ( !VectorValues.empty() )
333 Conditions +=
" AND ";
343 ColumnName, Conditions, Distinct);
345 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
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)
360 std::vector< unsigned int > VectorValuesOne(
361 ListValuesOne.begin(), ListValuesOne.end() );
366 ColumnName, Conditions);
368 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
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)
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;
389 field, value, ColumnNameOrder);
391 vtkSQLQuery *query = DatabaseConnector->GetQueryInstance();
393 query->SetQuery( QueryString.c_str() );
394 if ( !query->Execute() )
396 itkGenericExceptionMacro(
397 <<
"select 2 columns query failed"
398 << query->GetLastErrorText() );
399 DatabaseConnector->Close();
400 DatabaseConnector->Delete();
405 while ( query->NextRow() )
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);
422 vtkMySQLDatabase *DatabaseConnector,
423 const std::string & TableName,
424 const std::string & ColumnName,
425 const std::string & field,
426 const std::string & value)
431 Conditions +=
" LIMIT 1";
433 return ExecuteSelectQueryOneValue< std::string >(DatabaseConnector, QueryString);
440 vtkMySQLDatabase *DatabaseConnector,
441 const std::string & ColumnName,
442 const std::string & TableName)
444 std::string What =
"MAX(";
449 return ExecuteSelectQueryOneValue< int >(DatabaseConnector, QueryString);
456 vtkMySQLDatabase *DatabaseConnector,
457 const std::string & ColumnName,
458 const std::string & TableName,
459 const std::string & field,
460 const std::string & value)
462 std::string What =
"MAX(";
467 return ExecuteSelectQueryOneValue< int >(DatabaseConnector, QueryString);
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)
480 std::string What =
"MAX(";
485 return ExecuteSelectQueryOneValue< int >(DatabaseConnector, QueryString);
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)
498 std::string What =
"MIN(";
503 return ExecuteSelectQueryOneValue< int >(DatabaseConnector, QueryString);
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)
524 std::vector< std::pair< int, std::string > > result;
526 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
527 std::stringstream querystream;
528 querystream <<
"SELECT ";
529 querystream << TableOne;
531 querystream << ColumnOne;
533 querystream << TableTwo;
535 querystream << ColumnTwo;
536 querystream <<
" FROM ";
537 querystream << TableOne;
538 querystream <<
" JOIN ";
539 querystream << TableTwo;
540 querystream <<
" ON (";
541 querystream << TableOne;
543 querystream << ForeignKey;
544 querystream <<
" = ";
545 querystream << TableTwo;
547 querystream << PrimaryKey;
548 querystream <<
" WHERE ";
549 querystream << field;
550 querystream <<
" = '";
551 querystream << value;
554 query->SetQuery( querystream.str().c_str() );
555 if ( !query->Execute() )
557 itkGenericExceptionMacro(
558 <<
"join on 2 tables query failed"
559 << query->GetLastErrorText() );
560 DatabaseConnector->Close();
561 DatabaseConnector->Delete();
566 while ( query->NextRow() )
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);
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,
592 std::vector< std::vector< std::string > > Results;
593 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
595 std::stringstream Querystream;
596 Querystream <<
"SELECT ";
599 Querystream <<
"DISTINCT ";
602 for ( i = 0; i < SelectFields.size() - 1; i++ )
604 Querystream << SelectFields[i];
607 Querystream << SelectFields[i];
608 Querystream <<
" FROM ";
609 Querystream << MainTable;
611 while ( j < JoinTablesOnTraceTable.size() )
613 Querystream <<
" LEFT JOIN ";
614 Querystream << JoinTablesOnTraceTable[j];
615 Querystream <<
" ON ";
616 Querystream << JoinTablesOnTraceTable[j + 1];
620 Querystream <<
"WHERE ";
621 Querystream << MainTable;
623 Querystream << field;
624 Querystream <<
" = ";
625 Querystream << value;
628 query->SetQuery( Querystream.str().c_str() );
629 if ( !query->Execute() )
631 itkGenericExceptionMacro(
632 <<
"return info Contours query failed"
633 << query->GetLastErrorText() );
634 DatabaseConnector->Close();
635 DatabaseConnector->Delete();
639 while ( query->NextRow() )
641 std::vector< std::string > ResultsForOneRow;
642 for (
int k = 0; k < query->GetNumberOfFields(); k++ )
644 ResultsForOneRow.push_back( query->DataValue(k).ToString() );
646 Results.push_back(ResultsForOneRow);
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,
666 const std::vector<FieldWithValue> & iWhereOrConditions)
668 std::vector< std::vector< std::string > > Results;
669 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
671 std::stringstream Querystream;
672 Querystream <<
"SELECT ";
675 Querystream <<
"DISTINCT ";
678 for ( i = 0; i < SelectFields.size() - 1; i++ )
680 Querystream << SelectFields[i];
683 Querystream << SelectFields[i];
684 Querystream <<
" FROM ";
685 Querystream << MainTable;
687 while ( j < JoinTablesOnTraceTable.size() )
689 Querystream <<
" LEFT JOIN ";
690 Querystream << JoinTablesOnTraceTable[j];
691 Querystream <<
" ON ";
692 Querystream << JoinTablesOnTraceTable[j + 1];
696 Querystream <<
"WHERE ( ";
697 Querystream << MainTable;
699 Querystream << field;
700 Querystream <<
" = ";
701 Querystream << value;
702 Querystream <<
" AND (";
704 while ( k < iWhereOrConditions.size() -1)
706 Querystream << iWhereOrConditions[k].Field;
707 Querystream <<
" = ";
708 Querystream << iWhereOrConditions[k].Value;
709 Querystream <<
" OR ";
712 Querystream << iWhereOrConditions[k].Field;
713 Querystream <<
" = ";
714 Querystream << iWhereOrConditions[k].Value;
715 Querystream <<
") );";
717 query->SetQuery( Querystream.str().c_str() );
718 if ( !query->Execute() )
720 itkGenericExceptionMacro(
721 <<
"return info Contours query failed"
722 << query->GetLastErrorText() );
723 DatabaseConnector->Close();
724 DatabaseConnector->Delete();
728 while ( query->NextRow() )
730 std::vector< std::string > ResultsForOneRow;
731 for (
int k = 0; k < query->GetNumberOfFields(); k++ )
733 ResultsForOneRow.push_back( query->DataValue(k).ToString() );
735 Results.push_back(ResultsForOneRow);
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,
754 std::vector< std::vector< std::string > > Results;
755 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
757 std::stringstream Querystream;
758 Querystream <<
"SELECT ";
761 Querystream <<
"DISTINCT ";
764 for ( i = 0; i < SelectFields.size() - 1; i++ )
766 Querystream << SelectFields[i];
769 Querystream << SelectFields[i];
770 Querystream <<
" FROM (";
771 Querystream << MainTable;
773 while ( j < JoinTablesOnTraceTable.size() - 1 )
775 Querystream <<
" LEFT JOIN ";
776 Querystream << JoinTablesOnTraceTable[j];
777 Querystream <<
" ON ";
778 Querystream << JoinTablesOnTraceTable[j + 1];
784 Querystream <<
") WHERE (";
785 while ( k < WhereAndConditions.size() - 2 )
787 Querystream << WhereAndConditions[k];
788 Querystream <<
" = ";
789 Querystream << WhereAndConditions[k + 1];
790 Querystream <<
" AND ";
794 Querystream << WhereAndConditions[k];
795 Querystream <<
" = ";
796 Querystream << WhereAndConditions[k + 1];
799 query->SetQuery( Querystream.str().c_str() );
800 if ( !query->Execute() )
802 itkGenericExceptionMacro(
803 <<
"return info Contours query failed"
804 << query->GetLastErrorText() );
805 DatabaseConnector->Close();
806 DatabaseConnector->Delete();
810 while ( query->NextRow() )
812 std::vector< std::string > ResultsForOneRow;
813 for (
int kk = 0; kk < query->GetNumberOfFields(); kk++ )
815 ResultsForOneRow.push_back( query->DataValue(kk).ToString() );
817 Results.push_back(ResultsForOneRow);
829 std::vector< std::string >
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)
839 std::vector< std::string > result;
840 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
841 std::stringstream querystream;
843 querystream <<
" UNION ";
845 iFieldTwo, iListConditionsTwo);
847 query->SetQuery( querystream.str().c_str() );
848 if ( !query->Execute() )
850 itkGenericExceptionMacro(
851 <<
"List of all values for 2 tables query failed"
852 << query->GetLastErrorText() );
853 DatabaseConnector->Close();
854 DatabaseConnector->Delete();
859 while ( query->NextRow() )
861 for (
int i = 0; i < query->GetNumberOfFields(); i++ )
863 result.push_back( query->DataValue(i).ToString() );
874 std::list< unsigned int >
876 const std::string & iTableOne,
877 const std::string & iTableTwo,
879 const std::string & iColumnOne,
880 const std::string & iColumnTwo,
881 const std::string & iField,
882 const std::vector< std::string > & iListValues,
886 QueryString +=
" UNION ";
890 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
895 std::vector< std::string >
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)
904 std::vector< std::string > result;
905 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
906 std::stringstream querystream;
908 querystream <<
" UNION ";
910 querystream <<
" UNION ";
912 querystream <<
" UNION ";
915 query->SetQuery( querystream.str().c_str() );
916 if ( !query->Execute() )
918 itkGenericExceptionMacro(
919 <<
"List of all values for 2 tables query failed"
920 << query->GetLastErrorText() );
921 DatabaseConnector->Close();
922 DatabaseConnector->Delete();
927 while ( query->NextRow() )
929 for (
int i = 0; i < query->GetNumberOfFields(); i++ )
931 result.push_back( query->DataValue(i).ToString() );
943 std::vector< std::string >
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)
954 std::vector< std::string > result;
955 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
956 std::stringstream querystream;
958 querystream <<
" UNION ";
960 querystream <<
" UNION ";
962 iFieldTwo, iListConditionsTwo);
963 querystream <<
" UNION ";
965 iFieldTwo, iListConditionsTwo);
967 query->SetQuery( querystream.str().c_str() );
968 if ( !query->Execute() )
970 itkGenericExceptionMacro(
971 <<
"List of all values for 2 tables query failed"
972 << query->GetLastErrorText() );
973 DatabaseConnector->Close();
974 DatabaseConnector->Delete();
979 while ( query->NextRow() )
981 for (
int i = 0; i < query->GetNumberOfFields(); i++ )
983 result.push_back( query->DataValue(i).ToString() );
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)
1002 std::vector< std::string > result;
1003 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
1004 std::stringstream querystream;
1006 while ( i < iVectorTablesNames.size() - 1 )
1008 for (
unsigned int j = 0; j < iColumnNames.size(); j++ )
1011 iVectorTablesNames[i], iColumnNames[j], iVectorConditionFieldNames[i],
1012 iVectorConditionsValues[i],
true);
1013 querystream <<
" UNION DISTINCT ";
1018 while ( j < iColumnNames.size() - 1 )
1021 iVectorTablesNames[i], iColumnNames[j], iVectorConditionFieldNames[i],
1022 iVectorConditionsValues[i],
true);
1023 querystream <<
" UNION DISTINCT ";
1028 iVectorTablesNames[i], iColumnNames[j], iVectorConditionFieldNames[i],
1029 iVectorConditionsValues[i],
true);
1031 query->SetQuery( querystream.str().c_str() );
1032 if ( !query->Execute() )
1034 itkGenericExceptionMacro(
1035 <<
"List of same field for different tables query failed"
1036 << query->GetLastErrorText() );
1037 DatabaseConnector->Close();
1038 DatabaseConnector->Delete();
1043 while ( query->NextRow() )
1045 for (
int k = 0; k < query->GetNumberOfFields(); k++ )
1047 result.push_back( query->DataValue(k).ToString() );
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)
1074 std::vector< std::string > result;
1075 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
1077 std::stringstream querystream;
1078 querystream <<
"SELECT ";
1079 querystream << iColumnName;
1080 querystream <<
" FROM ";
1081 querystream << iTableName;
1082 querystream <<
" WHERE ((";
1084 while ( i < iVectorConditionFieldOne.size() - 1 )
1086 querystream << iFieldOne;
1087 querystream <<
" = '";
1088 querystream << iVectorConditionFieldOne.at(i);
1089 querystream <<
"' OR ";
1092 querystream << iFieldOne;
1093 querystream <<
" = '";
1094 querystream << iVectorConditionFieldOne.at(i);
1095 querystream <<
"') AND (";
1098 while ( j < iVectorConditionFieldTwo.size() - 1 )
1100 querystream << iFieldTwo;
1101 querystream <<
" = '";
1102 querystream << iVectorConditionFieldTwo.at(j);
1103 querystream <<
"' OR ";
1106 querystream << iFieldTwo;
1107 querystream <<
" = '";
1108 querystream << iVectorConditionFieldTwo.at(j);
1109 querystream <<
"'))";
1111 query->SetQuery( querystream.str().c_str() );
1112 if ( !query->Execute() )
1114 itkGenericExceptionMacro(
1115 <<
"GetSpecificValueFromOneTableWithConditionsOnTwoColumns query failed"
1116 << query->GetLastErrorText() );
1117 DatabaseConnector->Close();
1118 DatabaseConnector->Delete();
1123 while ( query->NextRow() )
1125 for (
int k = 0; k < query->GetNumberOfFields(); k++ )
1127 result.push_back( query->DataValue(k).ToString() );
1139 std::list< unsigned int >
1141 const std::string & iTableName,
1142 const std::string & iImgSessionID,
1143 const std::string & iCoordType,
1144 const std::string & iValue,
1145 vtkMySQLDatabase *DatabaseConnector)
1147 std::list< unsigned int > result;
1148 vtkSQLQuery * query = DatabaseConnector->GetQueryInstance();
1150 std::stringstream querystream;
1151 querystream <<
"SELECT T1.";
1152 querystream << iColumnName;
1153 querystream <<
" FROM (SELECT ";
1154 querystream << iTableName;
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;
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;
1186 query->SetQuery( querystream.str().c_str() );
1187 if ( !query->Execute() )
1189 itkGenericExceptionMacro(
1190 <<
"GetColumnForBoundedValue query failed"
1191 << query->GetLastErrorText() );
1192 DatabaseConnector->Close();
1193 DatabaseConnector->Delete();
1198 while ( query->NextRow() )
1200 for (
int k = 0; k < query->GetNumberOfFields(); k++ )
1202 result.push_back( query->DataValue(k).ToInt() );
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)
1221 std::list< unsigned int > result;
1222 vtkSQLQuery * query = iDatabaseConnector->GetQueryInstance();
1223 std::stringstream querystream;
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);";
1233 query->SetQuery( querystream.str().c_str() );
1235 if ( !query->Execute() )
1237 itkGenericExceptionMacro(
1238 <<
"GetColumnForBoundedValue query failed"
1239 << query->GetLastErrorText() );
1240 iDatabaseConnector->Close();
1241 iDatabaseConnector->Delete();
1246 while ( query->NextRow() )
1248 for (
int k = 0; k < query->GetNumberOfFields(); k++ )
1250 result.push_back( query->DataValue(k).ToInt() );
1263 vtkMySQLDatabase *iDatabaseConnector,
1264 const std::string & iTableOne,
1265 const std::string & iTableTwo,
1266 const std::string & iColumn,
1268 const std::vector< FieldWithValue > & iFieldsWithValues,
1273 iFieldsWithValues,
"AND", Distinct);
1275 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
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,
1288 const std::vector< FieldWithValue > & iFieldsWithValues,
1289 std::string iConditionConnector,
1290 std::string ColumnNameOrder)
1295 iFieldsWithValues, iConditionConnector,
false, ColumnNameOrder);
1302 return ExecuteSelectQuery< std::vector< std::string > >(iDatabaseConnector, QueryString);
1308 std::list< unsigned int >
1310 const std::string & iTableOne,
1311 const std::string & iTableTwo,
1312 const std::string & iColumn,
1314 const std::string & iField,
1315 const std::vector< std::string > & iVectorValues,
1322 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1328 std::list< unsigned int >
1330 const std::string & iTableOne,
1331 const std::string & iTableTwo,
1332 const std::string & iColumn,
1334 const std::string & iField,
1335 const std::vector< std::string > & iVectorValues,
1339 std::string Conditions =
"(";
1341 Conditions += GetConditions< std::string >(iField, iVectorValues,
"OR");
1342 Conditions +=
" AND ";
1343 std::vector< FieldWithValue > AndCondition(1);
1344 AndCondition[0] = iAndCondition;
1349 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1355 std::list<unsigned int>
1357 const std::string & iTableOne,
1358 const std::string & iTableTwo,
1359 const std::vector<std::string> & iSelectedFields,
1361 const std::string & iField,
1362 const std::string & iValue,
1369 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1375 const std::string & iTableOne,
1376 const std::string & iTableTwo,
1377 const std::string & iColumn,
1379 const std::string & iField,
1380 const std::vector< std::string > & iVectorValues,
1383 std::string What =
"MAX(";
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;
1396 return ExecuteSelectQueryOneValue< int >(iDatabaseConnector, QueryString);
1402 std::list< unsigned int >
1404 vtkMySQLDatabase *iDatabaseConnector,
1405 const std::string & iTableOne,
1406 const std::string & iTableTwo,
1407 const std::string & iColumn,
1409 const std::string & iField,
1410 const std::vector< std::string > & iVectorValues,
1414 std::string Conditions =
"(";
1416 Conditions += GetConditions< std::string >(iField, iVectorValues,
"OR");
1417 Conditions +=
" AND ";
1418 std::vector< FieldWithValue > AndCondition(1);
1419 AndCondition[0] = iAndCondition;
1423 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1429 std::list< unsigned int >
1431 const std::string & iTableOne,
1432 const std::string & iTableTwo,
1433 const std::string & iColumn,
1435 const std::string & iField,
1436 const std::vector< std::string > & iVectValues)
1440 std::string Conditions = GetConditions< std::string >(iField, iVectValues,
"OR");
1445 return ExecuteSelectQuery< std::list< unsigned int > >(iDatabaseConnector, QueryString);
1451 std::vector< std::string >
1453 const std::string & iColumnName,
1454 const std::string & iTableName,
1455 const std::string & iField,
1456 const std::string & iValue,
1458 const std::string & iNumberLimit)
1460 std::stringstream QueryStream;
1463 QueryStream <<
" ORDER BY ";
1464 QueryStream << iColumnName;
1467 QueryStream <<
" ASC LIMIT ";
1471 QueryStream <<
" DESC LIMIT ";
1473 QueryStream << iNumberLimit;
1474 return ExecuteSelectQuery< std::vector< std::string > >( iDatabaseConnector, QueryStream.str() );
1482 const std::string & iField,
1483 const std::string & iValue,
1486 std::stringstream Querystream;
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";
1497 Querystream <<
"Min = coordinate.coordID WHERE ";
1501 Querystream <<
"Max = coordinate.coordID WHERE ";
1503 Querystream << iField;
1504 Querystream <<
" = ";
1505 Querystream << iValue;
1507 return Querystream.str();
1515 unsigned int iTCoord,
1516 const std::string & iPoints,
1517 const std::string & iTraceName)
1519 ioStructure.
TCoord = iTCoord;
1520 vtkPolyData *output = vtkPolyData::New();
1521 if ( !iPoints.empty() )
1523 if ( iTraceName.compare(
"contour") == 0 )
1525 vtkSmartPointer< vtkPolyDataMySQLContourReader > convert_reader =
1526 vtkSmartPointer< vtkPolyDataMySQLContourReader >::New();
1527 output->DeepCopy( convert_reader->GetPolyData(iPoints) );
1531 if ( iTraceName.compare(
"mesh") == 0 )
1534 std::stringstream str(iPoints);
1538 vtkSmartPointer< vtkPolyDataMySQLMeshReader > convert_reader =
1539 vtkSmartPointer< vtkPolyDataMySQLMeshReader >::New();
1540 output->DeepCopy( convert_reader->GetPolyData(iPoints) );
1549 ioStructure.
Nodes = output;
1557 unsigned int iTCoord,
1558 const std::string & iPoints,
1559 const std::string & iTraceName)
1562 if ( iTraceName !=
"track" )
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;
1570 if ( !iPoints.empty() )
1572 vtkSmartPointer< vtkPolyDataMySQLTrackReader > convert_reader =
1573 vtkSmartPointer< vtkPolyDataMySQLTrackReader >::New();
1575 std::stringstream str(iPoints);
1579 vtkPolyData *output = vtkPolyData::New();
1580 output->DeepCopy( convert_reader->GetPolyData(iPoints) );
1581 ioStructure.
PointsMap = convert_reader->GetMap(iPoints);
1582 ioStructure.
Nodes = output;
1587 ioStructure.
Nodes = NULL;
1595 unsigned int iTrackRootID,
1596 const std::string & iPoints,
1597 const std::string & iTraceName)
1600 if ( iTraceName !=
"lineage" )
1602 std::cout <<
"this method is only for lineage";
1603 std::cout <<
"Debug: In " << __FILE__ <<
", line " << __LINE__;
1604 std::cout << std::endl;
1612 std::list< double * >
1614 const std::string & iTableName,
1615 const std::string & iField,
1616 const std::string & iValue)
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 (";
1623 Querystream <<
" UNION ";
1625 Querystream <<
" ORDER BY ";
1626 Querystream << iTableName;
1627 Querystream <<
"ID ASC ) AS T1";
1629 query->SetQuery( Querystream.str().c_str() );
1630 if ( !query->Execute() )
1632 itkGenericExceptionMacro(
1633 <<
"return coordmin and max query failed"
1634 << query->GetLastErrorText() );
1635 DatabaseConnector->Close();
1636 DatabaseConnector->Delete();
1641 while ( query->NextRow() )
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() )
1651 maxx = query->DataValue(0).ToDouble();
1652 maxy = query->DataValue(1).ToDouble();
1653 maxz = query->DataValue(2).ToDouble();
1654 maxt = query->DataValue(3).ToDouble();
1658 std::cout <<
"pb query to return center bounding box";
1659 std::cout <<
"Debug: In " << __FILE__ <<
", line " << __LINE__;
1660 std::cout << std::endl;
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);
1675 vtkMySQLDatabase *DatabaseConnector,
1676 unsigned int iImagingSession,
1677 const std::string & iTrace)
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();
1688 return ExecuteSelectQueryOneValue< int >(DatabaseConnector,
1695 vtkMySQLDatabase *DatabaseConnector,
1696 unsigned int iImagingSession,
1697 const std::string & iTrace,
1701 std::string What =
"COUNT(*)";
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() +
")";
1724 int test = ExecuteSelectQueryOneValue< int >(DatabaseConnector,
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: "SELECT ColumnName1, ColumnName2 FROM TableName"
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.
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 <> 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...
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)