GOFIGURE2  0.9.0
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Groups Pages
QueryBuilderHelper.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 =========================================================================*/
34 #include "QueryBuilderHelper.h"
35 #include "ConvertToStringHelper.h"
36 
37 #include <sstream>
38 
40  const std::string & iWhat,
41  const std::string & iWhere,
42  const std::string & iConditions)
43 {
44  std::stringstream querystream;
45 
46  querystream << "SELECT ";
47  querystream << iWhat;
48  querystream << " FROM ";
49  querystream << iWhere;
50  querystream << " WHERE ";
51  querystream << iConditions;
52  return querystream.str();
53 }
54 
55 //------------------------------------------------------------------------------
56 
57 //------------------------------------------------------------------------------
58 std::string SelectGeneralQuery(
59  const std::string & iWhat,
60  const std::string & iWhere,
61  std::string iOrderByQuery)
62 {
63  std::stringstream querystream;
64 
65  querystream << "SELECT ";
66  querystream << iWhat;
67  querystream << " FROM ";
68  querystream << iWhere;
69  if ( !iOrderByQuery.empty() )
70  {
71  querystream << iOrderByQuery;
72  }
73  return querystream.str();
74 }
75 
76 //------------------------------------------------------------------------------
77 
78 //------------------------------------------------------------------------------
79 std::string SelectQueryStream(
80  const std::string & iTable,
81  const std::string & iColumn,
82  std::string iOrderByColumnName,
83  std::string iAscDesc)
84 {
85  std::string OrderBy;
86 
87  if ( !iOrderByColumnName.empty() )
88  {
89  OrderBy = AddOrderBy(iOrderByColumnName, iAscDesc);
90  }
91  return SelectGeneralQuery(iColumn, iTable, OrderBy);
92 }
93 
94 //------------------------------------------------------------------------------
95 
96 //------------------------------------------------------------------------------
97 std::string SelectQueryStream(
98  const std::string & iTable,
99  const std::vector< std::string > & iListAttributes,
100  std::string iOrderByColumnName,
101  std::string iAscDesc)
102 {
103  std::string SelectedFields = GetSelectedAttributes(iListAttributes);
104 
105  return SelectQueryStream(iTable, SelectedFields, iOrderByColumnName, iAscDesc);
106 }
107 
108 //------------------------------------------------------------------------------
109 
110 //------------------------------------------------------------------------------
112  const std::string & iTable,
113  const std::string & iColumn,
114  const std::string & iConditions,
115  bool Distinct,
116  std::string iOrderByColumnName,
117  std::string iAscDesc)
118 {
119  std::string What = iColumn;
120  std::string Condition = iConditions;
121 
122  if ( Distinct )
123  {
124  What = AddDistinctToWhat(What);
125  }
126  if ( !iOrderByColumnName.empty() )
127  {
128  Condition += AddOrderBy(iOrderByColumnName, iAscDesc);
129  }
130  return SelectGeneralQueryConditions(What, iTable, Condition);
131 }
132 
133 //------------------------------------------------------------------------------
134 
135 //------------------------------------------------------------------------------
137  const std::string & iTable,
138  const std::string & iColumn,
139  const std::string & iField,
140  const std::string & iValue,
141  std::string iOrderByColumnName,
142  std::string iAscDesc,
143  bool Distinct)
144 {
145  std::stringstream Conditions;
146 
147  Conditions << iField;
148  Conditions << " = '";
149  Conditions << iValue;
150  Conditions << "'";
151  return SelectQueryStreamCondition(iTable, iColumn, Conditions.str(), Distinct, iOrderByColumnName, iAscDesc);
152 }
153 
154 //------------------------------------------------------------------------------
155 
156 //------------------------------------------------------------------------------
158  const std::string & iTable,
159  const std::vector< std::string > & iListAttributes,
160  const std::string & iField,
161  const std::string & iValue,
162  std::string iOrderByColumnName,
163  std::string iAscDesc)
164 {
165  std::string What = GetSelectedAttributes(iListAttributes);
166 
167  return SelectQueryStreamCondition(iTable, What, iField, iValue, iOrderByColumnName, iAscDesc);
168 }
169 
170 //------------------------------------------------------------------------------
171 
172 //------------------------------------------------------------------------------
174  const std::string & iTable,
175  const std::string & iColumn,
176  const std::string & iField,
177  const std::vector< std::string > & iListValues,
178  bool Distinct,
179  std::string iConditionConnector)
180 {
181  std::string Conditions = GetConditions(iField, iListValues, iConditionConnector);
182 
183  return SelectQueryStreamCondition(iTable, iColumn, Conditions, Distinct);
184 }
185 
186 //------------------------------------------------------------------------------
187 
188 //------------------------------------------------------------------------------
190  const std::string & iTable,
191  const std::vector< std::string > & iListAttributes,
192  const std::string & iField,
193  const std::vector< std::string > & iListValues,
194  std::string iConditionConnector,
195  bool Distinct)
196 {
197  std::string What = GetSelectedAttributes(iListAttributes);
198 
199  return SelectQueryStreamListConditions(iTable, What, iField, iListValues,
200  Distinct, iConditionConnector);
201 }
202 
203 //------------------------------------------------------------------------------
204 
205 //------------------------------------------------------------------------------
207  const std::string & iTable,
208  const std::string & iColumn,
209  const std::vector< FieldWithValue > & iConditions,
210  std::string iConditionConnector,
211  bool Distinct)
212 {
213  std::string What = iColumn;
214 
215  if ( Distinct )
216  {
217  What = AddDistinctToWhat (What);
218  }
219  std::string Conditions = GetConditions(iConditions, iConditionConnector);
220  return SelectQueryStreamCondition(iTable, iColumn, Conditions, Distinct);
221 }
222 
223 //------------------------------------------------------------------------------
224 
225 //------------------------------------------------------------------------------
227  const std::string & iTable,
228  const std::vector< std::string > & iListAttributes,
229  const std::vector< FieldWithValue > & iConditions,
230  std::string iConditionConnector,
231  bool Distinct,
232  std::string iOrderByColumnName)
233 {
234  std::string What = GetSelectedAttributes(iListAttributes);
235  std::string oQueryString = SelectQueryStreamListConditions(iTable, What, iConditions, iConditionConnector,
236  Distinct);
237  if (!iOrderByColumnName.empty())
238  {
239  oQueryString += AddOrderBy(iOrderByColumnName);
240  }
241 
242  return oQueryString;
243 }
244 
245 //------------------------------------------------------------------------------
246 
247 //------------------------------------------------------------------------------
248 std::string AddDistinctToWhat(const std::string & iWhat)
249 {
250  std::string oWhat = "DISTINCT ";
251 
252  oWhat += iWhat;
253  return oWhat;
254 }
255 
256 //------------------------------------------------------------------------------
257 
258 //------------------------------------------------------------------------------
259 std::string AddOrderBy(const std::string & iAttribute, std::string iAscDesc)
260 {
261  std::string oOrderBy = " ORDER BY ";
262 
263  oOrderBy += iAttribute;
264  oOrderBy += " ";
265  oOrderBy += iAscDesc;
266  oOrderBy += " ";
267  return oOrderBy;
268 }
269 
270 //------------------------------------------------------------------------------
271 
272 //------------------------------------------------------------------------------
273 std::string GetConditions(
274  const std::vector< FieldWithValue > & iConditions,
275  std::string iConditionConnector)
276 {
277  std::stringstream oConditions;
278 
279  oConditions << "(";
280  unsigned int i;
281  // why -1??
282  for ( i = 0; i < iConditions.size() - 1; i++ )
283  {
284 
285  oConditions << iConditions[i].Field;
286  oConditions << iConditions[i].Operator;
287  oConditions << " '";
288  oConditions << iConditions[i].Value;
289  oConditions << "' ";
290  oConditions << iConditionConnector;
291  oConditions << " ";
292  }
293  oConditions << iConditions[i].Field;
294  oConditions << iConditions[i].Operator;
295  oConditions << " '";
296  oConditions << iConditions[i].Value;
297  oConditions << "')";
298  return oConditions.str();
299 }
300 
301 //------------------------------------------------------------------------------
302 
303 //------------------------------------------------------------------------------
304 std::string GetConditions(
305  const std::string & iField,
306  const std::string & iValue,
307  std::string iConnector)
308 {
309  std::vector< FieldWithValue > Condition(1);
310  FieldWithValue Field = { iField, iValue, iConnector };
311  Condition[0] = Field;
312  return GetConditions(Condition);
313 }
314 
315 //-------------------------------------------------------------------------
316 
317 //-------------------------------------------------------------------------
318 std::string GetSelectedAttributes(const std::vector< std::string > & iListAttributes)
319 {
320  std::stringstream oQueryStream;
321  unsigned int i;
322 
323  for ( i = 0; i < iListAttributes.size() - 1; i++ )
324  {
325  oQueryStream << iListAttributes[i];
326  oQueryStream << ", ";
327  }
328  oQueryStream << iListAttributes[i];
329  return oQueryStream.str();
330 }
331 
332 //------------------------------------------------------------------------------
333 
334 //------------------------------------------------------------------------------
335 std::vector< std::string > ListUnsgIntToVectorString(
336  const std::list< unsigned int > & iList)
337 {
338  std::list< unsigned int >::const_iterator iter = iList.begin();
339  std::vector< std::string > oVector;
340  while ( iter != iList.end() )
341  {
342  unsigned int temp = *iter;
343  oVector.push_back( ConvertToString< unsigned int >(temp) );
344  ++iter;
345  }
346  return oVector;
347 }
348 
349 //-------------------------------------------------------------------------
350 
351 //-------------------------------------------------------------------------
352 std::list< unsigned int > VectorStringToUnsgInt(
353  const std::vector< std::string > & iVector)
354 {
355  std::vector< std::string >::const_iterator iter = iVector.begin();
356  std::list< unsigned int > oList;
357  while ( iter != iVector.end() )
358  {
359  oList.push_back( ss_atoi< unsigned int >( *iter ) );
360  ++iter;
361  }
362  return oList;
363 }
364 
365 //-------------------------------------------------------------------------
366 
367 //-------------------------------------------------------------------------
368 std::vector< std::string > VectorUnsgIntToVectorString(
369  const std::vector< unsigned int > & iVector)
370 {
371  std::vector< unsigned int >::const_iterator iter = iVector.begin();
372  std::vector< std::string > oVector;
373  while ( iter != iVector.end() )
374  {
375  oVector.push_back( ConvertToString< unsigned int >( *iter ) );
376  ++iter;
377  }
378  return oVector;
379 }
380 
381 //-------------------------------------------------------------------------
382 
383 //-------------------------------------------------------------------------
385  const std::string & iTableOne,
386  const std::string & iTableTwo,
387  const FieldWithValue & iOnCondition,
388  bool NonNULLRows)
389 {
390  std::stringstream oQueryStream;
391 
392  oQueryStream << iTableOne;
393  if (NonNULLRows)
394  {
395  oQueryStream << " JOIN ";
396  }
397  else
398  {
399  oQueryStream << " LEFT JOIN ";
400  }
401  oQueryStream << iTableTwo;
402  oQueryStream << " ON ";
403  oQueryStream << iTableOne;
404  oQueryStream << ".";
405  oQueryStream << iOnCondition.Field;
406  oQueryStream << iOnCondition.Operator;
407  oQueryStream << iTableTwo;
408  oQueryStream << ".";
409  oQueryStream << iOnCondition.Value;
410 
411  return oQueryStream.str();
412 }
413 
414 //-------------------------------------------------------------------------
415 
416 //-------------------------------------------------------------------------
418  const std::string & iTable,
419  const std::string & iTableTwo,
420  const std::string & iTableThree,
421  const FieldWithValue & iOnConditionOne,
422  const FieldWithValue & iOnConditionTwo)
423 {
424  std::stringstream oQueryStream;
425 
426  oQueryStream << "(";
427  oQueryStream << GetLeftJoinTwoTables(iTable, iTableTwo, iOnConditionOne);
428  oQueryStream << ")";
429  std::string LeftJoinTwo = GetLeftJoinTwoTables(iTable, iTableThree, iOnConditionTwo);
430  oQueryStream << LeftJoinTwo.substr( iTable.size() + 1, LeftJoinTwo.size() );
431  return oQueryStream.str();
432 }
433 
434 //-------------------------------------------------------------------------
435 
436 //-------------------------------------------------------------------------
437 std::string GetGroupBy(const std::string & iColumn, unsigned int iNumberDoublons)
438 {
439  std::stringstream oQueryStream;
440 
441  oQueryStream << " group by ";
442  oQueryStream << iColumn;
443  if ( iNumberDoublons != 0 )
444  {
445  oQueryStream << " HAVING COUNT(";
446  oQueryStream << iColumn;
447  oQueryStream << ") > ";
448  oQueryStream << iNumberDoublons;
449  }
450  return oQueryStream.str();
451 }
452 
453 //-------------------------------------------------------------------------
454 
455 //-------------------------------------------------------------------------
457  const std::vector< std::string > & iSelectedAttributes,
458  const std::string & iTableOne,
459  const std::string & iTableTwo,
460  const std::string & iTableThree,
461  const FieldWithValue & iJoinConditionOne,
462  const FieldWithValue & iJoinConditionTwo,
463  const std::string & iFieldOne,
464  unsigned int iValueFieldOne,
465  const std::string & iIDFieldName,
466  const std::list< unsigned int > & iListIDs)
467 {
468  std::vector< unsigned int > VectIDs( iListIDs.begin(), iListIDs.end() );
469  std::string What = GetSelectedAttributes(iSelectedAttributes);
470  std::string Where = GetLeftJoinThreeTables(iTableOne, iTableTwo,
471  iTableThree,
472  iJoinConditionOne,
473  iJoinConditionTwo);
474  FieldWithValue FirstPartCondition = { iFieldOne,
475  ConvertToString< unsigned int >(iValueFieldOne),
476  "=" };
477 
478  std::string Conditions = GetAndORConditions< unsigned int >(FirstPartCondition,
479  iIDFieldName,
480  VectIDs);
481  std::string QueryString = SelectQueryStreamCondition(Where, What, Conditions);
482  return QueryString;
483 }
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 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)