34 #ifndef __SelectQueryDatabaseHelper_h
35 #define __SelectQueryDatabaseHelper_h
39 #include "boost/unordered_map.hpp"
42 #include "vtkMySQLDatabase.h"
43 #include "vtkSQLQuery.h"
51 #include "QGoIOConfigure.h"
63 vtkMySQLDatabase *DatabaseConnector,
64 const std::string & ColumnName,
65 const std::string & TableName,
66 std::string OrderByColumnName =
"");
79 std::vector< std::pair< std::string, std::string > >
81 const std::string & ColumnNameOne,
82 const std::string & ColumnNameTwo,
83 const std::string & TableName,
84 const std::string & OrderByColumnName);
97 vtkMySQLDatabase *DatabaseConnector,
98 const std::vector<std::string> & iColumnNames,
99 const std::string & iTableName,
100 std::string iField =
"",
101 std::string iValue =
"");
111 vtkMySQLDatabase *DatabaseConnector,
112 const std::string & TableName,
113 const std::string & field,
114 const std::string & value);
126 int FindOneID(vtkMySQLDatabase *DatabaseConnector,
127 const std::string & TableName,
128 const std::string & ColumnName,
129 const std::string & field,
130 const std::string & value);
137 int FindOneID(vtkMySQLDatabase *DatabaseConnector,
138 const std::string & TableName,
139 const std::string & ColumnName,
140 const std::vector<FieldWithValue> & iConditions);
153 vtkMySQLDatabase * iDatabaseConnector,
154 const std::string & TableName,
155 const std::string & ColumnName,
156 const std::vector<FieldWithValue> & iConditions);
173 vtkMySQLDatabase *iDatabaseConnector,
174 const std::string & TableName,
175 const std::string & ColumnName,
176 const std::string & field,
177 const std::string & value,
178 bool ExcludeZero =
false);
188 vtkMySQLDatabase *iDatabaseConnector,
189 const std::string & TableName,
190 const std::string & ColumnName,
191 const std::string & field,
192 const std::string & value,
193 const std::string & ColumnNameOrder);
202 vtkMySQLDatabase *iDatabaseConnector,
203 const std::string & TableName,
204 const std::string & ColumnName,
205 const std::string & field,
206 const std::vector< std::string > & VectorValues,
207 bool Distinct =
false,
208 bool ExcludeZero =
false);
217 vtkMySQLDatabase *iDatabaseConnector,
218 const std::string & TableName,
219 const std::string & ColumnName,
220 const std::string & field,
221 const std::list< unsigned int > & ListValues,
222 bool Distinct =
false,
223 bool ExcludeZero =
false);
232 vtkMySQLDatabase *iDatabaseConnector,
233 const std::string & TableName,
234 const std::string & ColumnName,
235 const std::string & fieldOne,
236 const std::list< unsigned int > & ListValuesOne,
237 const std::string & fieldTwo,
238 const std::string & ValueFieldTwo);
254 std::vector< std::pair< std::string, std::string > >
256 vtkMySQLDatabase *DatabaseConnector,
257 const std::string & TableName,
258 const std::string & ColumnNameOne,
259 const std::string & ColumnNameTwo,
260 const std::string & field,
261 const std::string & value,
262 const std::string & ColumnNameOrder);
276 vtkMySQLDatabase *DatabaseConnector,
277 const std::string & ColumnName,
278 const std::string & TableName,
279 const std::string & field,
280 const std::vector< std::string > & VectorValues);
288 vtkMySQLDatabase *DatabaseConnector,
289 const std::string & ColumnName,
290 const std::string & TableName);
298 vtkMySQLDatabase *DatabaseConnector,
299 const std::string & ColumnName,
300 const std::string & TableName,
301 const std::string & field,
302 const std::string & value);
316 vtkMySQLDatabase *DatabaseConnector,
317 const std::string & ColumnName,
318 const std::string & TableName,
319 const std::string & field,
320 const std::vector< std::string > & VectorValues);
333 vtkMySQLDatabase *DatabaseConnector,
334 const std::string & TableName,
335 const std::string & ColumnName,
336 const std::string & field,
337 const std::string & value);
340 std::vector< std::pair< int, std::string > >
342 vtkMySQLDatabase *DatabaseConnector,
343 const std::string & TableOne,
344 const std::string & ColumnOne,
345 const std::string & TableTwo,
346 const std::string & ColumnTwo,
347 const std::string & ForeignKey,
348 const std::string & PrimaryKey,
349 const std::string & field,
350 const std::string & value);
362 unsigned int iTCoord,
363 const std::string & iPoints,
364 const std::string & iTraceName);
372 unsigned int iTCoord,
373 const std::string & iPoints,
374 const std::string & iTraceName);
382 unsigned int iTrackRootID,
383 const std::string & iPoints,
384 const std::string & iTraceName);
393 template <
typename T>
395 vtkMySQLDatabase* iDatabaseConnector,
396 const std::string & iQueryString,
397 std::list<T> & ioListStructure,
398 const std::string & iTableOne)
400 vtkSQLQuery *query = iDatabaseConnector->GetQueryInstance();
401 query->SetQuery( iQueryString.c_str() );
402 if ( !query->Execute() )
404 itkGenericExceptionMacro(
405 <<
"get info traces query failed"
406 << query->GetLastErrorText() );
407 iDatabaseConnector->Close();
408 iDatabaseConnector->Delete();
412 while ( query->NextRow() )
416 temp.TraceID = query->DataValue(0).ToUnsignedInt();
417 unsigned int SpecifiedValue;
418 if (iTableOne ==
"lineage")
420 SpecifiedValue = query->DataValue(1).ToUnsignedInt();
424 temp.CollectionID = query->DataValue(1).ToUnsignedInt();
425 SpecifiedValue = query->DataValue(7).ToUnsignedInt();
428 query->DataValue(6).ToString(), iTableOne);
432 temp.rgba[0] = ( query->DataValue(2).ToDouble() ) / 255.;
433 temp.rgba[1] = ( query->DataValue(3).ToDouble() ) / 255.;
434 temp.rgba[2] = ( query->DataValue(4).ToDouble() ) / 255.;
435 temp.rgba[3] = ( query->DataValue(5).ToDouble() ) / 255.;
439 ioListStructure.push_back(temp);
464 std::list< T > & ioListStructure,
465 vtkMySQLDatabase *iDatabaseConnector,
466 const std::vector<std::string> & iSelectedAttributes,
467 const std::string & iTableOne,
468 const std::string & iTableTwo,
469 const std::string & iTableThree,
472 const std::string & iFieldOne,
473 unsigned int iValueFieldOne,
474 const std::string & iIDFieldName,
475 const std::list< unsigned int > & iListIDs)
478 iTableThree, iJoinConditionOne, iJoinConditionTwo, iFieldOne, iValueFieldOne, iIDFieldName,
480 ExecuteQueryAndModifyListStructure<T>(
481 iDatabaseConnector, QueryString, ioListStructure, iTableOne);
488 vtkMySQLDatabase *DatabaseConnector,
489 const std::string & MainTable,
490 const std::vector< std::string > & SelectFields,
491 const std::string & field,
492 const std::string & value,
493 const std::vector< std::string > & JoinTablesOnTraceTable,
501 vtkMySQLDatabase *DatabaseConnector,
502 const std::string & MainTable,
503 const std::vector< std::string > & SelectFields,
504 const std::vector< std::string > & WhereAndConditions,
505 const std::vector< std::string > & JoinTablesOnTraceTable,
509 vtkMySQLDatabase *DatabaseConnector,
510 const std::string & MainTable,
511 const std::vector< std::string > & SelectFields,
512 const std::string & field,
513 const std::string & value,
514 const std::vector< std::string > & JoinTablesOnTraceTable,
516 const std::vector<FieldWithValue> & iWhereOrConditions);
520 std::vector< std::string >
522 vtkMySQLDatabase *DatabaseConnector,
523 const std::string & iTableOne,
524 const std::string & iTableTwo,
525 const std::string & iColumn,
526 const std::string & iField,
527 const std::string & iValue,
528 const std::string & iFieldTwo,
529 const std::vector< std::string > & iListConditionsTwo);
536 std::vector< std::string >
538 const std::string & iTableOne,
539 const std::string & iTableTwo,
540 const std::string & iColumnOne,
541 const std::string & iColumnTwo,
542 const std::string & iField,
543 const std::string & iValue);
549 vtkMySQLDatabase *DatabaseConnector,
550 const std::string & iTableOne,
551 const std::string & iTableTwo,
553 const std::string & iColumnOne,
554 const std::string & iColumnTwo,
555 const std::string & iField,
556 const std::vector< std::string > & iListValues,
564 std::vector< std::string >
566 const std::string & iTableOne,
567 const std::string & iTableTwo,
568 const std::string & iColumnOne,
569 const std::string & iColumnTwo,
570 const std::string & iField,
571 const std::string & iValue,
572 const std::string & iFieldTwo,
573 const std::vector< std::string > & iListConditionsTwo);
576 std::vector< std::string >
578 const std::vector< std::string > & iColumnNames,
579 const std::vector< std::string > & iVectorTablesNames,
580 const std::vector< std::string > & iVectorConditionFieldNames,
581 const std::vector< std::vector< std::string > > & iVectorConditionsValues);
589 std::vector< std::string >
591 vtkMySQLDatabase *DatabaseConnector,
592 const std::string & iColumnName,
593 const std::string & iTableName,
594 const std::string & iFieldOne,
595 const std::vector< std::string > & iVectorConditionFieldOne,
596 const std::string & iFieldTwo,
597 const std::vector< std::string > &iVectorConditionFieldTwo);
608 std::list< unsigned int >
610 const std::string & TableName,
611 const std::string & iImgSessionID,
612 const std::string & iCoordType,
613 const std::string & iValue,
614 vtkMySQLDatabase *DatabaseConnector);
617 std::list< unsigned int >
619 vtkMySQLDatabase *iDatabaseConnection,
620 const std::string & iColumnName,
621 const std::string & iTableName,
622 const std::vector< std::string > & iVectorConditionFieldOne,
623 const std::string & iFieldTwo);
625 template <
class TResultsQuery>
628 const std::string & iQuery)
632 vtkSQLQuery *query = iDatabaseConnector->GetQueryInstance();
634 TResultsQuery oResults;
635 typedef typename TResultsQuery::value_type ValueType;
636 query->SetQuery( iQuery.c_str() );
637 if ( !query->Execute() )
639 itkGenericExceptionMacro(
640 <<
"Execute select query failed"
641 << query->GetLastErrorText() );
642 iDatabaseConnector->Close();
643 iDatabaseConnector->Delete();
647 int NumberOfFields = query->GetNumberOfFields();
648 while ( query->NextRow() )
650 for (
int k = 0; k < NumberOfFields; k++ )
652 ValueType temp = ss_atoi<ValueType>(query->DataValue(k).ToString());
653 oResults.push_back( temp );
663 const std::string & iQuery)
665 vtkSQLQuery *query = iDatabaseConnector->GetQueryInstance();
666 T oResults = ss_atoi<T>(
"-1");
667 query->SetQuery( iQuery.c_str() );
668 if ( !query->Execute() )
670 itkGenericExceptionMacro(
671 <<
"Execute select query failed"
672 << query->GetLastErrorText() );
673 iDatabaseConnector->Close();
674 iDatabaseConnector->Delete();
679 if ( query->NextRow() )
681 oResults = ss_atoi<T>(query->DataValue(0).ToString() );
692 const std::vector<std::string> & iWhereAndConditions,
697 vtkMySQLDatabase *iDatabaseConnector,
698 const std::string & iTableOne,
699 const std::string & iTableTwo,
700 const std::string & iColumn,
702 const std::vector<FieldWithValue> & iFieldsWithValues,
703 bool Distinct =
false);
707 vtkMySQLDatabase *iDatabaseConnector,
708 const std::string & iTableOne,
709 const std::string & iTableTwo,
710 const std::vector<std::string> & iListAttributes,
712 const std::vector<FieldWithValue> & iFieldsWithValues,
713 std::string iConditionConnector =
"AND",
714 std::string ColumnNameOrder =
"");
718 vtkMySQLDatabase *iDatabaseConnector,
719 const std::string & iTableOne,
720 const std::string & iTableTwo,
721 const std::string & iColumn,
723 const std::string & iField,
724 const std::vector<std::string> & iVectorValues,
725 bool Distinct =
false ,
726 bool NonNULLRows =
false);
730 vtkMySQLDatabase *iDatabaseConnector,
731 const std::string & iTableOne,
732 const std::string & iTableTwo,
733 const std::string & iColumn,
735 const std::string & iField,
736 const std::vector<std::string> & iVectorValues,
741 vtkMySQLDatabase *iDatabaseConnector,
742 const std::string & iTableOne,
743 const std::string & iTableTwo,
744 const std::vector<std::string> & iSelectedFields,
746 const std::string & iField,
747 const std::string & iValue,
752 vtkMySQLDatabase* iDatabaseConnector,
753 const std::string & iTableOne,
754 const std::string & iTableTwo,
755 const std::string & iColumn,
757 const std::string & iField,
758 const std::vector<std::string> & iVectValues);
762 vtkMySQLDatabase *iDatabaseConnector,
763 const std::string & iTableOne,
764 const std::string & iTableTwo,
765 const std::string & iColumn,
767 const std::string & iField,
768 const std::vector<std::string> & iVectorValues,
779 vtkMySQLDatabase *iDatabaseConnector,
780 const std::string & iColumnName,
781 const std::string & iTableName,
782 const std::string & iField,
783 const std::string & iValue,
785 const std::string & iNumberLimit);
789 const std::string & iTableName,
790 const std::string & iField,
791 const std::string & iValue,
797 vtkMySQLDatabase *DatabaseConnector,
798 const std::string & iTableName,
799 const std::string & iField,
800 const std::string & iValue);
804 vtkMySQLDatabase *iDatabaseConnector,
805 const std::string & iTableOne,
806 const std::string & iTableTwo,
807 const std::string & iColumn,
809 const std::string & iField,
810 const std::vector<std::string> & iVectorValues,
815 vtkMySQLDatabase *DatabaseConnector,
816 unsigned int iImagingSession,
817 const std::string & iTrace);
821 vtkMySQLDatabase *DatabaseConnector,
822 unsigned int iImagingSession,
823 const std::string & iTrace,
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)
void ExecuteQueryAndModifyListStructure(vtkMySQLDatabase *iDatabaseConnector, const std::string &iQueryString, std::list< T > &ioListStructure, const std::string &iTableOne)
execute iQueryString and put the results in a list of T structure
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)
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::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)
T ExecuteSelectQueryOneValue(vtkMySQLDatabase *iDatabaseConnector, const std::string &iQuery)
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...
TResultsQuery ExecuteSelectQuery(vtkMySQLDatabase *iDatabaseConnector, const std::string &iQuery)
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::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.
QGOIO_EXPORT std::string WhereAndOrConditions(const std::vector< std::string > &iWhereAndConditions, bool iAnd=true)
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::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 SelectForTracesInfo(const std::vector< std::string > &iSelectedAttributes, const std::string &iTableOne, const std::string &iTableTwo, const std::string &iTableThree, const FieldWithValue &iJoinConditionOne, const FieldWithValue &iJoinConditionTwo, const std::string &iFieldOne, unsigned int iValueFieldOne, const std::string &iIDFieldName, const std::list< unsigned int > &iListIDs)
SELECT iSelectedAttributes[0], iSelectedAttributes[1]...FROM (iTableOne left join iTableTwo on iJoinC...
void GetInfoFromDBAndModifyListStructure(std::list< T > &ioListStructure, vtkMySQLDatabase *iDatabaseConnector, const std::vector< std::string > &iSelectedAttributes, const std::string &iTableOne, const std::string &iTableTwo, const std::string &iTableThree, const FieldWithValue &iJoinConditionOne, const FieldWithValue &iJoinConditionTwo, const std::string &iFieldOne, unsigned int iValueFieldOne, const std::string &iIDFieldName, const std::list< unsigned int > &iListIDs)
select iselectedattributes from (tableone left join tabletwo ijoinconditionone) left join tablethree ...
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)