GOFIGURE2  0.9.0
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Groups Pages
QueryBuilderHelper.h
Go to the documentation of this file.
1 /*=========================================================================
2  Authors: The GoFigure Dev. Team.
3  at Megason Lab, Systems biology, Harvard Medical school, 2009-11
4 
5  Copyright (c) 2009-11, President and Fellows of Harvard College.
6  All rights reserved.
7 
8  Redistribution and use in source and binary forms, with or without
9  modification, are permitted provided that the following conditions are met:
10 
11  Redistributions of source code must retain the above copyright notice,
12  this list of conditions and the following disclaimer.
13  Redistributions in binary form must reproduce the above copyright notice,
14  this list of conditions and the following disclaimer in the documentation
15  and/or other materials provided with the distribution.
16  Neither the name of the President and Fellows of Harvard College
17  nor the names of its contributors may be used to endorse or promote
18  products derived from this software without specific prior written
19  permission.
20 
21  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
22  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
23  THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
24  PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
25  BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
26  OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
27  OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
28  OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
29  WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
30  OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
31  ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
32 
33 =========================================================================*/
34 #ifndef __QueryBuilderHelper_h
35 #define __QueryBuilderHelper_h
36 
37 #include <vector>
38 #include <string>
39 #include <sstream>
40 #include <list>
41 #include "QGoIOConfigure.h"
42 
43 #include "QGoIOConfigure.h"
44 
45 struct QGOIO_EXPORT FieldWithValue
46  {
47  std::string Field;
48  std::string Value;
49  std::string Operator;
50  };
58 QGOIO_EXPORT
60  const std::string & iWhat,
61  const std::string & iWhere,
62  const std::string & iConditions);
63 
71 QGOIO_EXPORT
72 std::string SelectGeneralQuery(
73  const std::string & iWhat,
74  const std::string & iWhere,
75  std::string iOrderByQuery = "");
76 
82 QGOIO_EXPORT
83 std::string AddDistinctToWhat(const std::string & iWhat);
84 
91 QGOIO_EXPORT
92 std::string AddOrderBy(const std::string & iAttribute,
93  std::string iAscDesc = "ASC");
94 
100 QGOIO_EXPORT
101 std::string GetSelectedAttributes(const std::vector<std::string> & iListAttributes);
102 
111 template< typename T >
112 std::string GetConditions(const std::string & iField,
113  const std::vector<T> & iVectorValues,
114  std::string iConditionConnector = "AND")
115 {
116  std::stringstream oConditions;
117  oConditions << "";
118  if (!iVectorValues.empty() )
119  {
120  oConditions << "(";
121  unsigned int i;
122  for ( i = 0; i < iVectorValues.size() - 1; i++ )
123  {
124  oConditions << iField;
125  oConditions << " = '";
126  oConditions << iVectorValues[i];
127  oConditions << "' ";
128  oConditions << iConditionConnector;
129  oConditions << " ";
130  }
131  oConditions << iField;
132  oConditions << " = '";
133  oConditions << iVectorValues[i];
134  oConditions << "')";
135  }
136  return oConditions.str();
137 }
138 
139 QGOIO_EXPORT
140 std::string GetConditions(const std::vector<FieldWithValue> & iConditions,
141  std::string iConditionConnector = "AND");
142 
143 QGOIO_EXPORT
144 std::string GetConditions(const std::string & iField,
145  const std::string & iValue,
146  std::string iOperator = "=");
147 
151 template< typename T >
152 std::string GetAndORConditions(
153  const FieldWithValue & iFirtsPartCondition,
154  const std::string & iField,
155  const std::vector< T > & iOrVectorValues)
156 {
157  std::string oConditions;
158  std::vector<FieldWithValue> VectorConditions(1);
159  //FieldWithValue AndCondition = {fieldTwo,ValueFieldTwo, "="};
160  VectorConditions[0] = iFirtsPartCondition;
161  oConditions = GetConditions( VectorConditions, "AND" );
162  if (!iOrVectorValues.empty() )
163  {
164  oConditions = oConditions.substr(0, oConditions.size()-1);
165  oConditions += " AND ";
166  oConditions += GetConditions<T>(iField,iOrVectorValues,"OR");
167  oConditions += ")";
168  }
169  return oConditions;
170 }
171 
172 //std::string GetConditions(std::vector<FieldWithValue> iConditions,
173  // std::string iConditionConnector = "AND");
174 
175 //std::string GetConditions(std::string iField, std::string iValue,std::string iOperator = "=");
176 
186 QGOIO_EXPORT
187 std::string SelectQueryStream(
188  const std::string & iTable,
189  const std::string & iColumn,
190  std::string iOrderByColumnName = "",
191  std::string iAscDesc = "ASC");
192 
202 QGOIO_EXPORT
203 std::string SelectQueryStream(
204  const std::string & iTable,
205  const std::vector<std::string > & iListAttributes,
206  std::string iOrderByColumnName = "",
207  std::string iAscDesc = "ASC");
208 
221 QGOIO_EXPORT
222 std::string SelectQueryStreamCondition(
223  const std::string & iTable,
224  const std::string & iColumn,
225  const std::string & iConditions,
226  bool Distinct = false,
227  std::string iOrderByColumnName = "",
228  std::string iAscDesc = "ASC");
232 QGOIO_EXPORT
233 std::string SelectQueryStreamCondition(
234  const std::string & iTable,
235  const std::string & iColumn,
236  const std::string & iField,
237  const std::string & iValue,
238  std::string iOrderByColumnName = "",
239  std::string iAscDesc = "ASC",
240  bool Distinct = false);
241 
245 QGOIO_EXPORT
246 std::string SelectQueryStreamCondition(
247  const std::string & iTable,
248  const std::vector<std::string> & iListAttributes,
249  const std::string & iField,
250  const std::string & iValue,
251  std::string iOrderByColumnName = "",
252  std::string iAscDesc = "ASC");
253 
264 QGOIO_EXPORT
266  const std::string & iTable,
267  const std::string & iColumn,
268  const std::string & iField,
269  const std::vector< std::string > & iListValues,
270  bool Distinct = false,
271  std::string iConditionConnector = "OR");
272 
276 QGOIO_EXPORT
278  const std::string & iTable,
279  const std::vector<std::string> & iListAttributes,
280  const std::string & iField,
281  std::vector< std::string > & iListValues,
282  bool Distinct = false,
283  std::string iConditionConnector = "OR");
284 
288 QGOIO_EXPORT
290  const std::string & iTable,
291  const std::vector<std::string> & iListAttributes,
292  const std::string & iField,
293  const std::string & iValue,
294  bool Distinct = false,
295  std::string iConditionConnector = "OR");
299 QGOIO_EXPORT
301  const std::string & iTable,
302  const std::string & iColumn,
303  const std::vector<FieldWithValue> & iConditions,
304  std::string iConditionConnector = "OR",
305  bool Distinct = false);
306 
310 QGOIO_EXPORT
312  const std::string & iTable,
313  const std::vector<std::string> & iListAttributes,
314  const std::vector<FieldWithValue> & iConditions,
315  std::string iConditionConnector = "OR",
316  bool Distinct = false,
317  std::string iOrderByColumnName = "");
318 
319 QGOIO_EXPORT
320 std::vector< std::string > ListUnsgIntToVectorString(const std::list< unsigned int > & iList);
321 
322 QGOIO_EXPORT
323 std::list< unsigned int > VectorStringToUnsgInt(const std::vector< std::string > & iVector);
324 
325 QGOIO_EXPORT
326 std::vector< std::string > VectorUnsgIntToVectorString(const std::vector<unsigned int> & iVector);
327 
336 QGOIO_EXPORT
337 std::string GetLeftJoinTwoTables(
338  const std::string & iTableOne,
339  const std::string & iTableTwo,
340  const FieldWithValue & iOnCondition,
341  bool NonNULLRows = false);
342 
353 QGOIO_EXPORT
354 std::string GetLeftJoinThreeTables(
355  const std::string & iTable,
356  const std::string & iTableTwo,
357  const std::string & iTableThree,
358  const FieldWithValue & iOnConditionOne,
359  const FieldWithValue & iOnConditionTwo);
360 
361 QGOIO_EXPORT
362 std::string GetGroupBy(const std::string & iColumn, unsigned int iNumberDoublons);
363 
380 QGOIO_EXPORT
381 std::string SelectForTracesInfo(
382  const std::vector<std::string> & iSelectedAttributes,
383  const std::string & iTableOne,
384  const std::string & iTableTwo,
385  const std::string & iTableThree,
386  const FieldWithValue & iJoinConditionOne,
387  const FieldWithValue & iJoinConditionTwo,
388  const std::string & iFieldOne,
389  unsigned int iValueFieldOne,
390  const std::string & iIDFieldName,
391  const std::list< unsigned int > & iListIDs);
392 
393 #endif
std::string SelectGeneralQueryConditions(const std::string &iWhat, const std::string &iWhere, const std::string &iConditions)
SELECT iWhat FROM iWhere WHERE iConditions.
std::string SelectGeneralQuery(const std::string &iWhat, const std::string &iWhere, std::string iOrderByQuery)
SELECT iWhat FROM iWhere iOrderByQuery.
std::string GetAndORConditions(const FieldWithValue &iFirtsPartCondition, const std::string &iField, const std::vector< T > &iOrVectorValues)
(iFirstPartCondition AND (iField = iOrVectorValues1 OR iField = iOrVectorValues1...))
std::string AddOrderBy(const std::string &iAttribute, std::string iAscDesc)
ORDER BY iAttributes iAscDesc.
std::string GetGroupBy(const std::string &iColumn, unsigned int iNumberDoublons)
std::string GetSelectedAttributes(const std::vector< std::string > &iListAttributes)
iListAttributes[i], iListAttributes[i+1]...
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::list< unsigned int > VectorStringToUnsgInt(const std::vector< std::string > &iVector)
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::string AddDistinctToWhat(const std::string &iWhat)
DISTINCT iWhat.
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::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::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...
std::string GetConditions(const std::vector< FieldWithValue > &iConditions, std::string iConditionConnector)
std::string GetLeftJoinThreeTables(const std::string &iTable, const std::string &iTableTwo, const std::string &iTableThree, const FieldWithValue &iOnConditionOne, const FieldWithValue &iOnConditionTwo)
(iTable LEFT JOIN iTableTwo ON iTable.iOnCondition/Field = iTableTwo.iOnCondition/Value) LEFT JOIN iT...
std::vector< std::string > VectorUnsgIntToVectorString(const std::vector< unsigned int > &iVector)
std::string Operator
std::vector< std::string > ListUnsgIntToVectorString(const std::list< unsigned int > &iList)