StRoot  1
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Groups Pages
StDbSql.cc
1 /***************************************************************************
2  *
3  * $Id: StDbSql.cc,v 1.39 2016/05/25 20:40:01 dmitry Exp $
4  *
5  * Author: R. Jeff Porter
6  ***************************************************************************
7  *
8  * Description: Implementation class of StDataBaseI in (My)SQL
9  *
10  ***************************************************************************
11  *
12  * $Log: StDbSql.cc,v $
13  * Revision 1.39 2016/05/25 20:40:01 dmitry
14  * coverity - reverse_inull
15  *
16  * Revision 1.38 2016/05/24 20:26:48 dmitry
17  * coverity - unreachable delete loop suppression
18  *
19  * Revision 1.37 2015/05/15 19:05:12 dmitry
20  * missed instance, assign zero to the pointer after delete
21  *
22  * Revision 1.36 2015/05/15 19:02:21 dmitry
23  * assign zero to the pointer after delete
24  *
25  * Revision 1.35 2010/05/24 20:44:09 dmitry
26  * suppressed excessive output for indexed tables
27  *
28  * Revision 1.34 2009/12/10 03:47:07 dmitry
29  * BETWEEN operator was not constructed properly from elementID array => potential origin of several mysterious problems
30  *
31  * Revision 1.33 2009/11/03 00:04:54 dmitry
32  * restored missing endTime check
33  *
34  * Revision 1.32 2009/09/25 19:14:09 dmitry
35  * number of rows is reset to fetched number of rows, if it is less than total indexed number of rows
36  *
37  * Revision 1.31 2007/08/20 18:21:30 deph
38  * New Version of Load Balancer
39  *
40  * Revision 1.30 2007/04/21 03:20:33 deph
41  * removed extra check for endTime replacement
42  *
43  * Revision 1.29 2007/03/08 21:54:40 deph
44  * Added quotes to identifier `schema` for compatibility to mysql 5.0.x
45  *
46  * Revision 1.28 2005/11/28 19:09:55 deph
47  * small bug fix for a rare condition.
48  *
49  * Revision 1.27 2005/11/07 14:45:44 deph
50  * Changed the SQL operator IN to one of the following: = if row =1 ; between if block of element IDs are contiguous; or IN if they are not
51  *
52  * Revision 1.26 2004/01/15 00:02:25 fisyak
53  * Replace ostringstream => StString, add option for alpha
54  *
55  * Revision 1.25 2003/12/16 01:30:32 porter
56  * additional fixes for change from ostrstream to StString that were not exposed until
57  * running in online
58  *
59  * Revision 1.24 2003/09/26 20:40:37 deph
60  * *** empty log message ***
61  *
62  * Revision 1.23 2003/09/23 04:37:16 porter
63  * fixed leak of timeValues array
64  *
65  * Revision 1.22 2003/09/16 22:44:17 porter
66  * got rid of all ostrstream objects; replaced with StString+string.
67  * modified rules.make and added file stdb_streams.h for standalone compilation
68  *
69  * Revision 1.21 2003/09/02 17:57:49 perev
70  * gcc 3.2 updates + WarnOff
71  *
72  * Revision 1.20 2003/04/11 22:47:36 porter
73  * Added a fast multi-row write model specifically needed by the daqEventTag
74  * writer. Speed increased from about 100Hz to ~3000Hz. It is only invoked if
75  * the table is marked as Non-Indexed (daqTags & scalers). For non-indexed tables
76  * which include binary stored data (we don't have any yet), the fast writer has
77  * to invoke a slower buffer so that the rates are a bit slower (~500Hz at 50 rows/insert).
78  *
79  * Revision 1.19 2003/01/29 03:44:54 porter
80  * added setRowNumber in QueryDbFunction method to simplify codes that use
81  * this when plotting directly from the database
82  *
83  * Revision 1.18 2003/01/10 04:19:20 porter
84  * added feature of getting timestamp list (but no data) for a table.
85  * fixed 2 features sometimes used in online in query-by-whereclause.
86  * removed a stray 'cout' in a routine that is rarely accessed
87  *
88  * Revision 1.17 2002/01/30 15:40:48 porter
89  * changed limits on flavor tag & made defaults retrieving more readable
90  *
91  * Revision 1.16 2001/12/21 22:47:33 porter
92  * added DbRelease for new endtime checks - this caused problems with tpcGas
93  *
94  * Revision 1.15 2001/12/21 04:54:46 porter
95  * sped up table definition for emc and changed some ostrstream usage for
96  * insure tests
97  *
98  * Revision 1.14 2001/12/19 20:44:52 porter
99  * fixed endTime for run-level queries
100  *
101  * Revision 1.13 2001/12/05 17:16:35 porter
102  * stand-alone make file no longer had "DLINUX" in compile but this is still needed
103  * and returned. Also retrieve elementID list in query by whereClause for plotting
104  * many row instances.
105  *
106  * Revision 1.12 2001/10/26 20:59:46 porter
107  * fixed new endtime flag from previous checkin. made StDataBaseI available
108  * at root-cli.
109  *
110  * Revision 1.11 2001/10/24 04:05:20 porter
111  * added long long type to I/O and got rid of obsolete dataIndex table
112  *
113  * Revision 1.10 2001/08/02 17:37:19 porter
114  * fixed problem in fetch by where-clause used in online in StDbSql.cc.
115  * also got rid of warning comparing unsigned int to int.
116  *
117  * Revision 1.9 2001/07/23 16:39:30 porter
118  * removed an extraneous "cout" left in by mistake
119  *
120  * Revision 1.8 2001/04/23 19:24:31 porter
121  * fixed row limit & initial buffer contents for query by where clause
122  *
123  * Revision 1.7 2001/03/30 18:48:26 porter
124  * modified code to keep Insure from wigging-out on ostrstream functions.
125  * moved some messaging into a StDbSql method.
126  *
127  * Revision 1.6 2001/02/23 18:35:47 porter
128  * cleaned up a warning messages when compiled under HP's aCC
129  *
130  * Revision 1.5 2001/02/22 23:01:56 porter
131  * Re-introduced many-to-one name-to-table capability
132  * & robustness for query errors
133  *
134  * Revision 1.4 2001/02/09 23:06:25 porter
135  * replaced ostrstream into a buffer with ostrstream creating the
136  * buffer. The former somehow clashed on Solaris with CC5 iostream (current .dev)
137  *
138  * Revision 1.3 2001/02/08 23:23:56 porter
139  * fixed initialization of schemaID in table & fixed some warnings when
140  * compiled with NODEBUG
141  *
142  * Revision 1.2 2001/01/23 14:38:16 porter
143  * fixed bug in parsing flavor string where string contains a list of flavors.
144  *
145  * Revision 1.1 2001/01/22 18:37:59 porter
146  * Update of code needed in next year running. This update has little
147  * effect on the interface (only 1 method has been changed in the interface).
148  * Code also preserves backwards compatibility so that old versions of
149  * StDbLib can read new table structures.
150  * -Important features:
151  * a. more efficient low-level table structure (see StDbSql.cc)
152  * b. more flexible indexing for new systems (see StDbElememtIndex.cc)
153  * c. environment variable override KEYS for each database
154  * d. StMessage support & clock-time logging diagnostics
155  * -Cosmetic features
156  * e. hid stl behind interfaces (see new *Impl.* files) to again allow rootcint access
157  * f. removed codes that have been obsolete for awhile (e.g. db factories)
158  * & renamed some classes for clarity (e.g. tableQuery became StDataBaseI
159  * and mysqlAccessor became StDbSql)
160  *
161  *
162  **************************************************************************/
163 #include "StDbSql.hh"
164 #include "StDbManager.hh"
165 #include "StDbDefaults.hh"
166 #include "StDbConfigNodeImpl.hh"
167 #include "StDbTableIter.hh"
168 #include "StDbTable.h"
169 #include "StDbFastSqlWriter.h"
170 
171 #include "stdb_streams.h"
172 
173 #define __CLASS__ "StDbSql"
174 
175 static const char* DbQFailed = "Query Failed = ";
176 static const char* DbQInfo = " Query = ";
177 static const char* DbQResult = " Query Result = ";
178 
180 
181 StDbSql::StDbSql(MysqlDb &db, StDbBuffer& buffer) : StDataBaseI(),Db(db), buff(buffer) { mgr=StDbManager::Instance(); init(); }
182 
184 
185 StDbSql::StDbSql(MysqlDb &db, StDbBuffer& buffer, StDbType tpe, StDbDomain dom) : StDataBaseI(tpe, dom), Db(db), buff(buffer) {
186 mgr=StDbManager::Instance(); init();
187 }
188 
190 
191 StDbSql::StDbSql(MysqlDb &db, StDbBuffer& buffer, const char* tpe, const char* dom) : StDataBaseI(tpe, dom), Db(db), buff(buffer){
192  mgr=StDbManager::Instance(); init();
193 }
194 
196 
197 StDbSql::~StDbSql() {
198  if(mretString) delete [] mretString;
199  if(mdefaultEndDateTime) delete [] mdefaultEndDateTime;
200  deleteDescriptors();
201 }
202 
204 //
205 // Real Work Begins here
206 //
208 
209 int
210 StDbSql::QueryDb(StDbConfigNode* node) {
211 
212 #define __METHOD__ "QueryDb(StDbConfigNode*)"
213 
214  StDbNode curNode;
215  curNode.setDbName(mdbName);
216  curNode.setDbType(mdbType);
217  curNode.setDbDomain(mdbDomain);
218  int NodeID;
219  int branchID=node->getBranchID();
220 
221  if(!((NodeID)=prepareNode(node)))return 0;
222 
223  // Build node query string
224 
225  Db<<"Select subNode.*, NodeRelation.ID as branchID from Nodes ";
226  Db<<"LEFT JOIN NodeRelation ON Nodes.ID=NodeRelation.ParentID ";
227  Db<<"LEFT JOIN Nodes as subNode ON NodeRelation.NodeID=subNode.ID ";
228  Db<<" Where Nodes.ID="<<NodeID;
229  Db<<" and NodeRelation.BranchID="<<branchID<<endsql;
230 
231  if(!Db.QueryStatus())
232  return sendMess(DbQFailed,Db.printQuery(),dbMWarn,__LINE__,__CLASS__,__METHOD__);
233 
234  if(!Db.NbRows())
235  sendMess(node->printName()," Node has no subnodes",dbMDebug,__LINE__,__CLASS__,__METHOD__);
236 
237  sendMess(DbQInfo,Db.printQuery(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
238 
239 //
240 // Loop over rows in Configuration
241 
242  while(Db.Output(&buff)){
243  curNode.setConfigured(false);
244  if(readNodeInfo(&curNode)){
245 
246  StString fs;
247  fs<<"Found "<<curNode.printNodeType()<<" Node "<<curNode.printName();
248  fs<<" of parent "<<node->printName();
249  sendMess(DbQResult,(fs.str()).c_str(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
250 
251 
252  if(strcmp(curNode.printNodeType(),"table")!=0){ // it is a ConfigNode
253 
254  StDbConfigNode* child= new StDbConfigNodeImpl(node,curNode);
255  if(!child->isDbNode())readConfigNodeInfo(child);
256 
257  } else {
258 
259  StDbTable* table = node->addTable(&curNode);
260  readTableInfo(table);
261  }
262  }
263  buff.Raz();
264  }
265  Db.Release();
266  return 1;
267 #undef __METHOD__
268 }
269 
271 int
272 StDbSql::QueryDb(StDbNode* node){
273 #define __METHOD__ "QueryDb(StDbNode*)"
274 
275  if(!prepareNode(node)) return 0;
276  if(node->IsTable()){
277  readTableInfo((StDbTable*) node);
278  } else {
279  readConfigNodeInfo((StDbConfigNode*)node);
280  }
281 
282 return node->getNodeID();
283 #undef __METHOD__
284 }
285 
287 int
288 StDbSql::QueryDb(StDbTable* table, unsigned int reqTime){
289 
290 #define __METHOD__ "QueryDb(table,time)"
291 
292  // Using name & version information in StDbTable + request timestamp
293  // fill data from the database into the StDbTable pointer.
294  //
295  int retVal=1;
296  if(!table) return 0;
297  char* tName = table->printName();
298  table->clearStoreInfo();
299  int nodeID;
300  if(!((nodeID)=table->getNodeID())){
301  if(!(prepareNode((StDbNode*)table)))
302  return sendMess(tName," Table not found in DB",dbMErr,__LINE__,__CLASS__,__METHOD__);
303 
304  readTableInfo(table);
305  clear();
306  }
307 
308  char* checkString=checkTablePrepForQuery(table,true); // returns null if ok
309  if(checkString)
310  return sendMess(tName,checkString,dbMErr,__LINE__,__CLASS__,__METHOD__);
311 
312  // start preparing the queries
313 
314  // common where clause
315  StString bs;
316  bs<<" Where nodeID="<<nodeID;
317  // prepare "flavor" part of query
318  bs<<" AND "<<getFlavorQuery(table->printFlavor());
319  // prepare for production time
320  bs<<" AND "<<getProdTimeQuery(table->getProdTime());
321  // terminate the baseString
322  bs<<" ";
323 
324  string baseString = bs.str();
325 
326 
327  //--> add element ID list part of query
328  int numRows;
329  int* elementID=table->getElementID(numRows);
330  if(!elementID)
331  return sendMess(tName,"doesn't have an Element List",dbMErr,__LINE__,__CLASS__,__METHOD__);
332 
333  char* elementString=getElementList(elementID,numRows);
334  char* dataTable=getDataTable(table,reqTime);
335 
336 // Query DB for the endtime -> earliest time of next row
337  Db << " select beginTime + 0 as mendDateTime, ";
338  Db << " unix_timestamp(beginTime) as mendTime from "<<dataTable;
339  Db << baseString <<" AND beginTime>from_unixtime("<<reqTime<<")";
340 // Db << " And elementID In("<<elementString<<")";
341  Db << " And elementID "<<elementString;
342  Db << " Order by beginTime limit 1"<<endsql;
343 
344  sendMess(DbQInfo,Db.printQuery(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
345 
346  if(!Db.QueryStatus())
347  return sendMess(DbQFailed,Db.printQuery(),dbMWarn,__LINE__,__CLASS__,__METHOD__);
348 
349  if(Db.NbRows()==1 && Db.Output(&buff)){
350  char* edTime=0;
351  int eTime;
352  buff.ReadScalar(edTime,"mendDateTime");
353  buff.ReadScalar(eTime,"mendTime");
354  table->setEndTime(eTime);
355  table->setEndTime(edTime);
356  if(edTime) delete [] edTime;
357  clear();
358  } else {
359  setDefaultEndTime(table);
360  }
361 
362  int eID;
363 
364  // --> prep for data query which can be 1 or more queries
365  int maxID=1;
366  int i;
367  for(i=0;i<numRows;i++)if(elementID[i]>maxID)maxID=elementID[i];
368  int* idMap = new int[maxID+1];
369  int* dataIDList = new int[numRows];
370  unsigned int* timeValues = new unsigned int[numRows];
371  for(i=0;i<numRows;i++){
372  idMap[elementID[i]]=i;
373  dataIDList[i]=0;
374  timeValues[i]=0;
375  }
376 
377 // --> end of prep <--
378  // query is done where elementID is not necessarily distinct.
379  // yet the in the query we set limit=numRows.
380  // - we take 1st of instance of each elementID returned.
381  // - we make a new list of those not yet found
382  // - redo query with limit="rowsLeft"
383  // continue until we're done
384 
385  int rowsLeft=numRows;
386  bool done=false;
387  while(!done){ //done if all elementIDs are found or (break) if null
388 
389  Db <<" select unix_timestamp(beginTime) as bTime,"<<dataTable<<".* from ";
390  Db << dataTable << baseString;
391  Db <<" AND beginTime<=from_unixtime("<<reqTime<<")";
392  //Db <<" AND elementID In("<<elementString<<") ";
393  Db <<" AND elementID "<<elementString;
394  Db <<" Order by beginTime desc limit "<< rowsLeft <<endsql;
395 
396  sendMess(DbQInfo,Db.printQuery(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
397 
398  if(!Db.QueryStatus()){
399  sendMess(DbQFailed,Db.printQuery(),dbMWarn,__LINE__,__CLASS__,__METHOD__);
400  break;
401  }
402 
403  if(Db.NbRows()==0)break;
404 
405  int numRowsFound=0;
406  while(Db.Output(&buff)){
407  buff.ReadScalar(eID,"elementID");
408  // check to see if this eID is already returned & don't use if so
409  if(!dataIDList[idMap[eID]]){
410  buff.ReadScalar(dataIDList[idMap[eID]],"dataID");
411  buff.ReadScalar(timeValues[idMap[eID]],"bTime");
412  table->setRowNumber(idMap[eID]);
413  table->dbStreamer(&buff,true); // stream data into table
414  numRowsFound++;
415  }
416  buff.Raz();
417  }
418 
419  rowsLeft=rowsLeft-numRowsFound;
420  // Check for left overs & modify query for repeat
421  if(!done && (rowsLeft>0)){
422  int* elementsLeft = new int[rowsLeft];
423  int j=0;
424  for(i=0;i<numRows;i++){
425  if(!dataIDList[i]){
426  elementsLeft[j]=elementID[i];
427  j++;
428  }
429  }
430  elementString = getElementListIN(elementsLeft,rowsLeft);
431  delete [] elementsLeft;
432  } else {
433  done=true;
434  }
435 
436  } // --> end of while loop
437 
438  if(rowsLeft==numRows){
439  sendMess(tName," has No data for query",dbMWarn,__LINE__,__CLASS__,__METHOD__);
440  setDefaultBeginTime(table,reqTime);
441  retVal=0;
442  } else if(rowsLeft>0){
443  StString tp;
444  tp<<" Not all rows filled from DB, Requested="<<numRows;
445  tp<<" Returned="<<numRows-rowsLeft<<" for Table="<<tName;
446  mgr->printInfo((tp.str()).c_str(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
447  // numRows-=rowsLeft;
448  // table->resizeNumRows(numRows);
449  }
450 
451  if(retVal){
452  table->addWrittenRows(dataIDList,numRows);
453  if (rowsLeft > 0) {
454  StString tp;
455  tp<<"Fixing row size, setting it to " << (numRows-rowsLeft) << " for Table = " << tName;
456  mgr->printInfo((tp.str()).c_str(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
457  table->resizeNumRows(numRows-rowsLeft);
458  }
459  table->setTimeValues(timeValues);
460  unsigned int t1=table->getMaxTime();
461  table->setBeginTime(t1);
462  char* dt=getDateTime(t1);
463  table->setBeginTime(dt); delete [] dt;
464  table->setRowNumber(); // reset current row to 0
465  } else {
466  delete [] timeValues;
467  }
468 
469  Db.Release();
470 
471  // Dmitry: returned this line, because we skip endTime completely, if we don't do this fix
472  if(retVal) retVal=(int)updateEndTime(table,dataTable,reqTime);
473 
474  delete [] idMap;
475  delete [] dataIDList;
476  delete [] dataTable;
477 
478  return retVal;
479 #undef __METHOD__
480 }
481 
483 bool StDbSql::checkColumn(const char* tableName, const char* columnName){
484 
485  bool retVal=false;
486  Db<<"show columns from "<<tableName<<" like '"<<columnName<<"'"<<endsql;
487  if(Db.NbRows()==1)retVal=true;
488  Db.Release();
489  return retVal;
490 }
491 
493 bool StDbSql::updateEndTime(StDbTable* table, const char* dataTable, unsigned int requestTime){
494 
495  /********************************
496  resets the table's endtime based on set endTime (if table has this column)
497  instead of the running beginTime timestamp. Returns False if endTime is
498  earlier than requestTime
499  ********************************/
500 
501 #define __METHOD__ "updateEndTime(table,table,time)"
502  bool retVal = true;
503  if(!checkColumn(dataTable,"endTime")) return retVal;
504  int nrows;
505  int* wrows = table->getWrittenRows(nrows);
506 
507  StString mpdOut, mpdOut2;
508 
509 
510  Db<<" select unix_timestamp(Min(endTime)) as mendTime from "<<dataTable;
511  // Db<<" where dataID In("<<getElementList(wrows,nrows)<<")"<<endsql;
512  Db<<" where dataID "<<getElementList(wrows,nrows)<<endsql;
513 
514  sendMess(DbQInfo,Db.printQuery(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
515 
516 
517 if( (Db.NbRows()>0) && Db.Output(&buff)){
518  unsigned int t1 = table->getEndTime();
519  unsigned int t2;
520  buff.ReadScalar(t2,"mendTime");
521  if(t2<t1)table->setEndTime(t2);
522  if(t2<requestTime) retVal=false;
523  buff.Raz();
524  }
525 
526  Db.Release();
527 #undef __METHOD__
528  return retVal;
529 }
530 
532 int
533 StDbSql::QueryDb(StDbTable* table, const char* whereClause){
534 
535 #define __METHOD__ "QueryDb(StDbTable*, const char* where)"
536 
537  unsigned int* timeSet=QueryDbTimes(table,whereClause);
538  if(!timeSet)return 0;
539  delete [] timeSet;
540  return table->GetNRows();
541 #undef __METHOD__
542 }
543 
545 unsigned int*
546 StDbSql::QueryDbTimes(StDbTable* table, const char* whereClause, int opt){
547 
548 #define __METHOD__ "QueryDb(StDbTable*, const char* where)"
549 
550  /*
551  rules for # of rows returned (by user request via setRowLimit(int nrows);)
552  1. table->GetNRows()= 0 or N means no limit or limit N
553  2. returned table->GetNRows()=M where M is how many returned
554 
555  rules for beginTime & endTime
556  1. Non-indexed tables have arbitrary returned times
557  2. beginTime="lastest" begin time of returned rows
558  3. endTime="earliest" begin time of set of "next" rows
559  */
560 
561  unsigned int* retVal=0;
562  char* tName=table->printName();
563 
564  char* checkString=checkTablePrepForQuery(table);
565  if(checkString){ // then error message
566  sendMess(tName,checkString,dbMErr,__LINE__,__CLASS__,__METHOD__);
567  return retVal;
568  }
569 
570  setDefaultReturnValues(table,0);
571  int numRows = table->getRowLimit();
572  int numTables;
573  int numRowsReturned=0;
574  unsigned int t1=0;
575 
576 
577  table->setElementID((int*)retVal,0); // no rows to begin with
578  table->setRowNumber();
579 
580  char** dataTables=getDataTables(table,numTables);
581  int i;
582  for(i=0;i<numTables;i++){
583 
584  char* columnList=0;
585  if(!opt)columnList=getColumnList(table,dataTables[i]);
586  if(!opt && !columnList){
587  sendMess(tName," has no elements?",dbMErr,__LINE__,__CLASS__,__METHOD__);
588  return retVal;
589  }
590 
591  Db<<" select unix_timestamp("<<dataTables[i]<<".beginTime) as bTime,";
592  Db<<" elementID ";
593  if(!opt)Db<<","<<columnList;
594  Db<<" from "<<dataTables[i]<<" "<<whereClause;
595  if(numRows)Db<<" limit "<<numRows;
596  Db<<endsql;
597  sendMess(DbQInfo,Db.printQuery(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
598  if(!Db.QueryStatus()){
599  sendMess(DbQFailed,Db.printQuery(),dbMWarn,__LINE__,__CLASS__,__METHOD__);
600  return retVal;
601  }
602 
603  int retRows=Db.NbRows();
604  if(retRows==0) continue;
605 
606  int* elements = new int[retRows];
607  int* dataIDList = new int[retRows];
608  unsigned int* timeList = new unsigned int[retRows];
609  if(!opt){
610  table->addNRows(retRows);
611  } else {
612  table->resizeElementID(retRows+table->GetNRows());
613  }
614  // table->setRowNumber();
615 
616  int j=0;
617  while(Db.Output(&buff)){
618  buff.ReadScalar(timeList[j],"bTime");
619  buff.ReadScalar(elements[j],"elementID");
620  buff.ReadScalar(dataIDList[j],"dataID");
621  if(!opt)table->dbStreamer(&buff,true);
622  if(timeList[j]>t1)t1=timeList[j];
623  j++;
624  buff.Raz();
625  }
626  table->addNElements(elements,retRows);
627  table->addWrittenRows(dataIDList,retRows);
628  table->setBeginTime(t1);
629 
630  unsigned int* tmpRet=new unsigned int[numRowsReturned+retRows];
631  if(retVal){
632  memcpy(tmpRet,retVal,numRowsReturned*sizeof(int));
633  delete [] retVal;
634  }
635  tmpRet+=numRowsReturned;
636  memcpy(tmpRet,timeList,retRows*sizeof(int));
637  retVal=tmpRet;
638  numRowsReturned+=retRows;
639  Db.Release();
640 
641  if(table->IsIndexed() && t1>0 && !opt){
642  Db<<" select unix_timestamp(beginTime) as eTime from "<<dataTables[i];
643  Db<<" where beginTime>from_unixtime("<<t1<<")";
644  //Db<<" and elementID In("<<getElementList(elements,retRows)<<")";
645  Db<<" and elementID "<<getElementList(elements,retRows);
646  Db<<" Order by beginTime limit 1"<<endsql;
647 
648  sendMess(DbQInfo,Db.printQuery(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
649 
650  if(Db.Output(&buff)){
651  unsigned int eTime=0;
652  if(buff.ReadScalar(eTime,"eTime") && eTime && eTime<table->getEndTime())table->setEndTime(eTime);
653  buff.Raz();
654  }
655  Db.Release();
656  }
657  delete [] elements;
658  delete [] dataIDList;
659  delete [] timeList;
660  }// loop over tables
661 
662  for(i=0;i<numTables;i++) delete [] dataTables[i];
663  delete [] dataTables;
664  if(retVal){
665  char* dateTime=getDateTime(table->getBeginTime());
666  table->setBeginTime(dateTime); if(dateTime) delete [] dateTime;
667  dateTime=getDateTime(table->getEndTime());
668  table->setEndTime(dateTime); if(dateTime) delete [] dateTime;
669  table->setRowNumber();
670  }
671  return retVal;
672 #undef __METHOD__
673 }
674 
676 int
677 StDbSql::QueryDbFunction(StDbTable* table, const char* whereClause, char* funcName) {
678 #define __METHOD__ "QueryDbFunction(table,whereClause,functionName)"
679  /*
680  Method to provide access to Max, Min, Ave,... functions where
681  the result per storage table is put in a row in the StDbTable.
682  This only works for those columns stored in basic Mysql types
683  */
684 
685  char* checkString=checkTablePrepForQuery(table); // null is good
686  if(checkString)
687  return sendMess(table->printName(),checkString,dbMErr,__LINE__,__CLASS__,__METHOD__);
688 
689  int numTables;
690  int numRowsReturned=0;
691 
692  char** dataTables=getDataTables(table,numTables);
693  int i;
694  for(i=0;i<numTables;i++){
695 
696  char* columnList=getColumnList(table,dataTables[i],funcName);
697  if(!columnList)return 0;
698 
699  Db<<" select "<<columnList<<" from "<<dataTables[i];
700  Db<<" "<<whereClause<<endsql;
701 
702  sendMess(DbQInfo,Db.printQuery(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
703 
704  if(!Db.QueryStatus())
705  return sendMess(DbQFailed,Db.printQuery(),dbMWarn,__LINE__,__CLASS__,__METHOD__);
706 
707  int retRows=Db.NbRows();
708  if(retRows==0) continue;
709  numRowsReturned+=retRows;
710 
711  while(Db.Output(&buff)){ table->dbStreamer(&buff,true); buff.Raz(); }
712  Db.Release();
713  }
714 
715  for(i=0;i<numTables;i++) delete [] dataTables[i];
716  delete [] dataTables;
717  table->setRowNumber();
718 
719  return numRowsReturned;
720 #undef __METHOD__
721 };
722 
724 int
725 StDbSql::WriteDb(StDbTable* table, unsigned int storeTime){
726 
727 #define __METHOD__ "WriteDb(StDbTable*,uint storeTime)"
728 
729  int retVal=1;
730  char* tName=table->printName();
731 
732  if(!table->hasData())
733  return sendMess(tName," has no data to store",dbMWarn,__LINE__,__CLASS__,__METHOD__)+1;
734 
735  int nodeID;
736  if(!((nodeID)=table->getNodeID()))
737  if(!(prepareNode((StDbNode*)table)))
738  return sendMess(tName," Not found in DB",dbMErr,__LINE__,__CLASS__,__METHOD__);
739 
740  readTableInfo(table);
741  clear();
742 
743 
744  if(table->IsBaseLine() && hasInstance(table))
745  return sendMess("BaseLine instance already exists",tName,dbMErr,__LINE__,__CLASS__,__METHOD__);
746 
747  if(!QueryDescriptor(table))
748  return sendMess(tName," doesn't have a descriptor",dbMErr,__LINE__,__CLASS__,__METHOD__);
749 
750 
751  table->setRowNumber(); // set to 0
752  if(!table->IsIndexed())return WriteDbNoIndex(table,storeTime);
753 
754  char* dataTable;
755  if(!((dataTable)=getDataTable(table,storeTime)))
756  return sendMess(tName," has no storage table",dbMErr,__LINE__,__CLASS__,__METHOD__);
757 
758  int numRows;
759  int* elements = table->getElementID(numRows);
760 
761  if(!elements)
762  return sendMess(tName,"doesn't have an Element List",dbMErr,__LINE__,__CLASS__,__METHOD__);
763 
764  int* storedData = new int[numRows];
765  memset(storedData,0,numRows*sizeof(int));
766  char* sTime = getDateTime(storeTime);
767  int rowsWritten = 0;
768 
769  // write each row & roll back full writes if any fail.
770  // - Also, for the time being, write to the old index
771  // so that one can read with older versions of the code
772 
773  table->commitData();
774  table->clearStoreInfo();
775  table->setDataTable(dataTable);
776 
777  char* eTime=0;
778  if( table->getEndStoreTime() && checkColumn(dataTable,"endTime"))
779  eTime=getDateTime(table->getEndStoreTime());
780 
781  for(int i=0;i<numRows;i++){
782  clear();
783  buff.WriteScalar(nodeID,"nodeID");
784  buff.WriteScalar(table->getSchemaID(),"schemaID");
785  buff.WriteScalar(sTime,"beginTime");
786  buff.WriteScalar(elements[i],"elementID");
787  if(eTime)buff.WriteScalar(eTime,"endTime");
788  if(!table->defaultFlavor())buff.WriteScalar(table->printFlavor(),"flavor");
789  table->dbStreamer(&buff,false);
790 
791  if(!Db.Input(dataTable,&buff)){
792  deleteRows(dataTable,storedData,i);
793  retVal=0;
794  break;
795  } else {
796  storedData[i]=Db.GetLastInsertID();
797  }
798  clear();
799  rowsWritten++;
800  }
801  if(rowsWritten==numRows)table->addWrittenRows(storedData,numRows,true);
802 
803  delete [] storedData;
804  delete [] sTime;
805  if(eTime) delete [] eTime;
806 
807  table->setRowNumber();
808  delete [] dataTable;
809 
810  if(!retVal)sendMess(" Write failed for table=",tName,dbMWarn,__LINE__,__CLASS__,__METHOD__);
811  return retVal;
812 #undef __METHOD__
813 }
814 
816 int
817 StDbSql::WriteDbNoIndex(StDbTable* table, unsigned int storeTime){
818 #define __METHOD__ "WriteDbNoIndex(table,storeTime)"
819 
820  int retVal=0;
821  char* dataTable;
822  if(!((dataTable)=getDataTable(table,storeTime)))
823  return sendMess(table->printName()," has no storage table",dbMErr,__LINE__,__CLASS__,__METHOD__);
824 
825  StString cList;
826  cList<<"beginTime,"<<getColumnList(table);
827 
828  char* sTime=getDateTime(storeTime);
829 
830  int numRows=table->GetNRows();
831  char* colList = new char[strlen((cList.str()).c_str())+1];
832  strcpy(colList,(cList.str()).c_str());
833 
834  int i;
835  bool hasBinary=false;
836  if(Db.InputStart(dataTable,&buff,colList,numRows,hasBinary)){
837 
838  if(hasBinary){ // got to go through the buffer
839 
840  cout<<" In Binary write???"<<endl;
841 
842  for(i=0;i<numRows;i++){
843  buff.WriteScalar(sTime,"beginTime");
844  table->dbStreamerWrite(&buff); //,false);
845  if(!Db.InputRow(&buff,i)) break;
846  }
847  if( i==numRows && Db.InputEnd() ){
848  retVal=1;
849  table->commitData();
850  }
851  } else {
852 
853  table->setBeginTime(storeTime);
854  StString fsql;
855  StDbFastSqlWriter writer(fsql);
856  writer.ioTable(table);
857  Db<<fsql.str();
858  if(Db.InputEnd()){
859  retVal=1;
860  table->commitData();
861  }
862  }
863 
864  }
865 
866  clear();
867 
868  table->setRowNumber();
869  delete [] colList;
870  delete [] sTime;
871 
872  delete [] dataTable;
873 
874  if(!retVal)sendMess(" Write failed for Non-Indexed table=",table->printName(),dbMWarn,__LINE__,__CLASS__,__METHOD__);
875  return retVal;
876 
877 #undef __METHOD__
878 }
879 
881 int
882 StDbSql::QueryDescriptor(StDbTable* table){
883 #define __METHOD__ "QueryDescriptor(StDbTable*)"
884 
885  // Query the database for the elements associated with this
886  // tableName and with the schemaID that is added at the
887  // constructure or over-written by an input file ... e.g. requested SchemaID
888 
889 if(table->hasDescriptor())return 1;
890 
891  clear();
892 
893  Db<<" SELECT structure.lastSchemaID, structure.ID from structure left join Nodes on structure.name=Nodes.structName";
894  Db<<" WHERE Nodes.name='"<<table->printName() <<"'"<<endsql;
895 
896  if(!Db.Output(&buff))return 0;
897 
898  int schemaID;
899  int structID;
900  buff.ReadScalar(schemaID,"lastSchemaID");
901  buff.ReadScalar(structID,"ID");
902  clear();
903 
904  int requestSchemaID;
905  if(!(requestSchemaID=table->getSchemaID())){
906  requestSchemaID=schemaID;
907  table->setSchemaID(schemaID);
908  }
909 
910  StDbTableDescriptor* descriptor = getDescriptor(structID,requestSchemaID);
911  table->setDescriptor(descriptor);
912 
913  if(descriptor->IsValid())return 1;
914 
915  Db<<"SELECT `schema`.schemaID, `schema`.name, `schema`.type, `schema`.length, ";
916  Db<<"`schema`.position from `schema` WHERE `schema`.structID="<<structID;
917  Db<<" AND `schema`.schemaID="<<requestSchemaID;
918  Db<<" ORDER by `schema`.position"<<endsql;
919 
920  sendMess(DbQInfo,Db.printQuery(),dbMDebug,__LINE__,__CLASS__,__METHOD__);
921 
922  if(!Db.QueryStatus())
923  return sendMess(DbQFailed,Db.printQuery(),dbMWarn,__LINE__,__CLASS__,__METHOD__);
924 
925 
926  if(Db.NbRows()==0) {
927  deleteDescriptor(structID,requestSchemaID);
928  return 0;
929  }
930 
931  while(Db.Output(&buff)){
932  descriptor->fillElement(&buff,requestSchemaID);
933  buff.Raz();
934  }
935  Db.Release();
936  descriptor->endRowPadding();
937 
938  addDescriptor(descriptor);
939 
940  return 1;
941 #undef __METHOD__
942 }
943 
945 int
946 StDbSql::WriteDb(StDbConfigNode* node, int parentID, int& configID){
947 #define __METHOD__ "WriteDb(node,parentID,configID)"
948 
949  if(!node) return 0;
950 
951  char* nName = node->printName();
952 
953  if(!parentID){
954 
955  if(strcmp(node->printNodeType(),"Config")!=0)
956  return sendMess("No Config tag for new config=",nName,dbMErr,__LINE__,__CLASS__,__METHOD__);
957 
958  if(!node->printVersion())
959  return sendMess("No version label for new config=",nName,dbMErr,__LINE__,__CLASS__,__METHOD__);
960 
961  } else {
962  node->setNodeType("directory");
963  }
964 
965  int nodeID;
966  if(!((nodeID)=storeConfigNode(node)))
967  return sendMess(" Could not store ",nName,dbMErr,__LINE__,__CLASS__,__METHOD__);
968 
969  // write this node
970  if(parentID) {
971  insertNodeRelation(configID,parentID,nodeID);
972  } else {
973  configID=node->getNodeID();
974  }
975 
976  // do this node's tables
977  if(node->hasData()){
978  StDbTableIter* itr=node->getStDbTableIter();
979  while(!itr->done()){
980  int childID=0;
981  StDbTable* table= itr->next();
982  table->setNodeType("table");
983  if(!((childID)=storeTableNode(table)))
984  return sendMess(" Could not store table in Node=",nName,dbMErr,__LINE__,__CLASS__,__METHOD__);
985  insertNodeRelation(configID,nodeID,childID);
986  }
987  }
988 
989 return nodeID;
990 }
991 
993 void
994 StDbSql::deleteRows(const char* tableName, int* rowID, int nrows){
995 
996  if(!rowID || nrows==0)return;
997  Db<<" delete from "<<tableName;
998  Db<<" where dataID In("<<getElementList(rowID,nrows)<<")"<<endsql;
999 }
1000 
1002 bool
1003 StDbSql::rollBack(StDbNode* node){
1004 
1005  if(!(node->canRollBack()) || !(node->getNodeID())) return false;
1006  Db<<"delete from Nodes where ID="<<node->getNodeID()<<endsql;
1007  return Db.QueryStatus();
1008 }
1009 
1011 bool
1012 StDbSql::rollBack(StDbTable* table){
1013 
1014  int numRows;
1015  int* numWrittenRows = table->getWrittenRows(numRows);
1016  char* dataTable = table->getDataTable();
1017  char* elementList = getElementList(numWrittenRows,numRows);
1018 
1019  Db<<"delete from "<<dataTable<<" where dataID In("<<elementList<<")"<<endsql;
1020  Db.Release();
1021 
1022  bool retVal=Db.QueryStatus();
1023  Db.Release();
1024  delete [] dataTable;
1025 
1026  return retVal;
1027 }
1028 
1030 int*
1031 StDbSql::selectElements(const char* elementName, StDbElementIndex* inval, int& numElements){
1032  // if table does not have element rows (elementName=="None")
1033  // then will return default elementID=0, numElements=1
1034  // if table does not have elements based on StDbElementIndex,
1035  // then will return null pointer & numElements=0 --> NO DATA CAN BE Gotten
1036  // else, will return element list & numElements based on query
1037 
1038  int* retElements = 0;
1039  numElements=1;
1040  if(!elementName) return retElements;
1041  if(strcmp(elementName,"None")==0){
1042  retElements = new int[1]; retElements[0]=0;
1043  return retElements;
1044  }
1045 
1046  int numIndeces = inval->getNumIndeces();
1047  clear();
1048 
1049  Db<<" select elementID from "<<elementName<<"IDs";
1050  if(numIndeces>0){
1051  Db<<" where "<<inval->printIndexName(0)<<"="<<inval->getIndexVal(0);
1052  for(int i=1; i<numIndeces; i++)
1053  Db<<" AND "<<inval->printIndexName(i)<<"="<<inval->getIndexVal(i);
1054  }
1055  Db<<endsql;
1056  numElements=Db.NbRows();
1057  if(numElements<=0) return retElements;
1058  retElements = new int[numElements];
1059  int j=0;
1060  while(Db.Output(&buff)){
1061  buff.ReadScalar(retElements[j],"elementID");
1062  j++;
1063  buff.Raz();
1064  }
1065  clear();
1066  return retElements;
1067 }
1068 
1070 char**
1071 StDbSql::getIndexNames( const char* elementName, int& numIndexes){
1072 
1073  Db<<"select * from elementIndexes ";
1074  Db<<" where elementName='"<<elementName<<"'"<<endsql;
1075 
1076  char** indexNames = 0;
1077  if(!((numIndexes)=Db.NbRows())) return indexNames;
1078 
1079  indexNames = new char*[numIndexes];
1080  int i = 0;
1081  while(Db.Output(&buff)){
1082  buff.ReadScalar(indexNames[i],"indexName");
1083  i++;
1084  buff.Raz();
1085  }
1086 
1087  clear();
1088  return indexNames;
1089 }
1090 
1092 StDbTableDescriptor*
1093 StDbSql::findDescriptor(int structID, int schemaID){
1094 
1095  StDbTableDescriptor* td = 0;
1096  for(DescList::iterator itr = mdescriptors.begin();
1097  itr != mdescriptors.end(); ++itr){
1098  if( ((*itr)->getSchemaID()==schemaID) && ((*itr)->getStructID()==structID)){
1099  td = *itr;
1100  break;
1101  }
1102  }
1103  return td;
1104 }
1105 
1107 StDbTableDescriptor*
1108 StDbSql::getDescriptor(int structID, int schemaID){
1109  StDbTableDescriptor* retVal=findDescriptor(structID,schemaID);
1110  if(retVal) return new StDbTableDescriptor(*retVal); // make copy
1111  retVal=new StDbTableDescriptor(structID,schemaID); // -else- make new
1112  return retVal;
1113 }
1114 
1116 void
1117 StDbSql::addDescriptor(StDbTableDescriptor* td){
1118  mdescriptors.push_back( new StDbTableDescriptor((*td)) ); // add a copy
1119 };
1120 
1122 void
1123 StDbSql::deleteDescriptors() {
1124  for( auto &it : mdescriptors ) delete it;
1125  mdescriptors.clear();
1126 }
1127 
1129 void
1130 StDbSql::deleteDescriptor(int structID, int schemaID) {
1131 
1132  StDbTableDescriptor* desc;
1133  for(DescList::iterator itr = mdescriptors.begin();
1134  itr != mdescriptors.end(); ++itr){
1135  if(structID==(*itr)->getStructID() && schemaID==(*itr)->getSchemaID()){
1136  desc=(*itr);
1137  mdescriptors.erase(itr);
1138  delete [] desc;
1139  break;
1140  }
1141  }
1142 }
1143 
1145 void
1146 StDbSql::setDefaultReturnValues(StDbTable* table, unsigned int reqTime){
1147  if(!table)return;
1148  setDefaultBeginTime(table,reqTime);
1149  setDefaultEndTime(table);
1150  table->clearStoreInfo();
1151 }
1152 
1154 void StDbSql::setDefaultBeginTime(StDbTable* table, unsigned int reqTime){
1155  // set default return times
1156  char* stime=getDateTime(reqTime);
1157  table->setBeginTime(reqTime); // uint version
1158  table->setBeginTime(stime); // char* version
1159  if(stime) delete [] stime;
1160 }
1161 
1163 void StDbSql::setDefaultEndTime(StDbTable* table){
1164  if(!mdefaultEndDateTime)initEndTime();
1165  table->setEndTime(mdefaultEndDateTime);
1166  table->setEndTime(mdefaultEndTime);
1167 }
1168 
1170 void StDbSql::initEndTime(){
1171  mdefaultEndTime=StDbDefaults::Instance()->getEndTime();
1172  mdefaultEndDateTime=getDateTime(mdefaultEndTime);
1173 }
1174 
1176 int
1177 StDbSql::prepareNode(StDbNode* dbNode){
1178 
1179  //-> takes the input dbNode's name & version and calls the db
1180  // (via method queryNode()) for the other information
1181  // and sets into dbNode
1182 
1183  if( (strcmp(dbNode->printNodeType(),"DB")!=0) &&
1184  (dbNode->IsConfigured())) return dbNode->getNodeID();
1185 
1186  dbNode->setDbName(mdbName);
1187  dbNode->setDbType(mdbType);
1188  dbNode->setDbDomain(mdbDomain);
1189 
1190  return queryNode(dbNode);;
1191 }
1192 
1194 int
1195 StDbSql::queryNode(StDbNode* node){
1196 
1197 int retVal=0;
1198 
1199  Db<< "Select * from Nodes where Nodes.name='"<<node->printName()<<"'";
1200  Db<<" AND Nodes.versionKey='"<<node->printVersion()<<"'"<<endsql;
1201 
1202  if(Db.Output(&buff) && readNodeInfo(node))retVal=node->getNodeID();
1203  if(!retVal) clear();
1204 
1205 return retVal;
1206 }
1207 
1209 bool
1210 StDbSql::readNodeInfo(StDbNode* node){
1211 
1212  int nodeID;
1213  char* tmpString;
1214 
1215  if(!buff.ReadScalar(nodeID,"ID")) return false;
1216  if(!buff.ReadScalar(tmpString,"nodeType"))return false;
1217  node->setNodeType(tmpString);
1218  delete [] tmpString;
1219  tmpString = 0;
1220 
1221  if(!buff.ReadScalar(tmpString,"name"))return false;
1222  node->setName(tmpString);
1223  delete [] tmpString;
1224  tmpString = 0;
1225 
1226  if(!buff.ReadScalar(tmpString,"versionKey"))return false;
1227  node->setVersion(tmpString);
1228  delete [] tmpString;
1229  tmpString = 0;
1230 
1231  node->setNodeID(nodeID);
1232  node->setConfigured(true);
1233 
1234  return true;
1235 }
1236 
1238 bool
1239 StDbSql::readConfigNodeInfo(StDbConfigNode* node){
1240 
1241  char* iname;
1242  if(!buff.ReadScalar(iname,"indexName")) return false;
1243  if(strcmp(iname,"None")!=0){
1244  int id;
1245  buff.ReadScalar(id,"indexVal");
1246  node->setElementIndexInfo(iname,id);
1247  }
1248  delete [] iname;
1249 
1250  int branchID;
1251  buff.ReadScalar(branchID,"branchID");
1252  node->setBranchID(branchID);
1253 
1254  return true;
1255 }
1256 
1258 bool
1259 StDbSql::readTableInfo(StDbTable* table){
1260 
1261 char* tmpString;
1262 
1263  if(!buff.ReadScalar(tmpString,"structName"))return false;
1264  table->setCstructName(tmpString); delete [] tmpString;
1265 
1266  tmpString=0;
1267  char* hy=0;
1268  if(buff.ReadScalar(tmpString,"elementID")){
1269  hy=strstr(tmpString,"-");
1270  if(hy){
1271  *hy='\0';
1272  hy++;
1273  int first=atoi(tmpString);
1274  int last = atoi(hy);
1275  hy--;
1276  *hy='-';
1277  int len = last-first+1;
1278  int * tmpElements = new int[len];
1279  int j=0;
1280  int k;
1281  for(k=first; k<=last;k++){
1282  tmpElements[j]=k;
1283  j++;
1284  }
1285  table->setElementID(tmpElements,len);
1286  delete [] tmpElements;
1287  tmpElements = 0;
1288  } else {
1289  delete [] tmpString;
1290  tmpString = 0;
1291  if(!buff.ReadScalar(tmpString,"indexName")) return false;
1292  table->setElementName(tmpString);
1293  }
1294  delete [] tmpString;
1295  tmpString = 0;
1296  }
1297 
1298 
1299  table->setBaseLine(checkValue("baseLine","Y"));
1300  table->setBinary(checkValue("isBinary","Y"));
1301  table->setIndexed(checkValue("isIndexed","Y"));
1302 
1303  table->setDbName(mdbName);
1304  table->setDbType(mdbType);
1305  table->setDbDomain(mdbDomain);
1306 
1307 return true;
1308 };
1309 
1311 char*
1312 StDbSql::insertNodeString(StDbNode* node){
1313 
1314  StString dqs;
1315  dqs<<"insert into Nodes set name='"<<node->printName()<<"' ";
1316  if(!StDbDefaults::Instance()->IsDefaultVersion(node->printVersion()))
1317  dqs<<", versionKey='"<<node->printVersion()<<"' ";
1318 
1319  dqs<<", nodeType='"<<node->printNodeType()<<"'";
1320 
1321  return mRetString(dqs);
1322 }
1323 
1325 int
1326 StDbSql::storeConfigNode(StDbConfigNode* node){
1327 
1328  int retVal=0;
1329 
1330  Db<<insertNodeString((StDbNode*) node);
1331 
1332  if(node->getNumIndeces()){
1333  char* ename=0; int eid;
1334  node->getElementIndexInfo(ename,eid);
1335  if ( ename ) {
1336  Db<<", indexName='"<<ename<<"'";
1337  Db<<", indexVal="<<eid;
1338  delete [] ename;
1339  }
1340  }
1341  Db<<endsql;
1342 
1343  if(Db.QueryStatus())retVal=Db.GetLastInsertID();
1344  clear();
1345 
1346  return retVal;
1347 }
1348 
1350 int
1351 StDbSql::storeTableNode(StDbTable* table){
1352 
1353  int retVal=0;
1354  if(!table->printCstructName()) return retVal;
1355 
1356  Db<<insertNodeString((StDbNode*) table);
1357  Db<<", structName='"<<table->printCstructName()<<"'";
1358 
1359  if(table->IsBaseLine())Db<<", baseLine='Y'";
1360  if(table->IsBinary())Db<<", isBinary='Y'";
1361  if(!table->IsIndexed())Db<<", isIndexed='N'";
1362 
1363  Db<<endsql;
1364 
1365  if(Db.QueryStatus())retVal=Db.GetLastInsertID();
1366  clear();
1367 
1368  return retVal;
1369 }
1370 
1372 bool
1373 StDbSql::insertNodeRelation(int configID, int parent, int child){
1374 
1375  Db<<" insert into NodeRelation set ParentID="<<parent;
1376  Db<<", NodeID="<<child<<", ConfigID="<<configID<<endsql;
1377 
1378  bool retVal=Db.QueryStatus();
1379  Db.Release();
1380 
1381  return retVal;
1382 }
1383 
1385 unsigned int
1386 StDbSql::getUnixTime(const char* time){
1387 
1388  unsigned int retVal = 0;
1389  clear();
1390  Db<<"select unix_timestamp('"<<time<<"') as requestTime"<<endsql;
1391  if(Db.Output(&buff)) buff.ReadScalar(retVal,"requestTime");
1392  clear();
1393 
1394 return retVal;
1395 }
1396 
1398 char*
1399 StDbSql::getDateTime(unsigned int time){
1400 
1401  char* retVal=0;
1402 // note the " + 0" part formats result without delimiters
1403 // e.g. 1999-01-01 00:12:20 becomes 19990101001220
1404 
1405  clear();
1406  Db<<"select from_unixtime("<<time<<") + 0 as requestTime"<<endsql;
1407  if(Db.Output(&buff)) buff.ReadScalar(retVal,"requestTime");
1408  clear();
1409 
1410 return retVal;
1411 }
1412 
1414 bool
1415 StDbSql::checkValue(const char* colName, const char* colValue){
1416 
1417  bool retVal = false;
1418  char* tmpS=0;
1419  if(buff.ReadScalar(tmpS,colName) && strcmp(tmpS,colValue)==0)retVal=true;
1420  if(tmpS) delete [] tmpS;
1421 
1422 return retVal;
1423 }
1424 
1426 char*
1427 StDbSql::getFlavorQuery(const char* flavor){
1428 
1429 // prepares SQL of " flavor In('flav1','flav2',...)"
1430 
1431  char *id1,*id2,*id3;
1432  id1 = new char[strlen(flavor)+1];
1433  strcpy(id1,flavor);
1434  id3=id1;
1435 
1436  StString fs;
1437  fs<<" flavor In(";
1438  while(( (id2)=strstr(id3,"+")) ){
1439  *id2='\0';
1440  fs<<"'"<<id3<<"',";
1441  *id2='+';
1442  id2++;
1443  id3=id2;
1444  }
1445  fs<<"'"<<id3<<"')";
1446  delete [] id1;
1447 
1448  return mRetString(fs);
1449 }
1450 
1452 char*
1453 StDbSql::getProdTimeQuery(unsigned int prodTime){
1454 
1455 // prepares SQL of " entryTime<="
1456 StString pt;
1457  if(prodTime==0){
1458  pt<<" deactive=0 ";
1459  } else {
1460  pt<<" (deactive=0 OR deactive>="<<prodTime<<")";
1461  pt<<" AND unix_timestamp(entryTime)<="<<prodTime;
1462  }
1463  return mRetString(pt);
1464 }
1465 
1467 char*
1468 StDbSql::getElementList(int* e, int num){
1469 
1470 // prepares comma separated list of integers
1471 // using the between operator
1472 
1473  StString es;
1474 
1475  if (!e || num == 0) {
1476  es<<0;
1477  } else if (num == 1) {
1478  es << " = " << e[0];
1479  } else {
1480  // check for ordered list first, if not ordered - use "IN", not "BETWEEN"
1481  bool ordered = true;
1482  for (int i = 0; i < num; i++) {
1483  if (e[i] != (e[0]+i)) {
1484  ordered = false;
1485  break;
1486  }
1487  }
1488  if (ordered == true) {
1489  es<<" BETWEEN "<<e[0]<<" AND "<< (e[0] + num - 1);
1490  } else {
1491  // list is not ordered, probably, our input is some mixed element IDs, use "IN"
1492  return getElementListIN(e,num);
1493  }
1494  }
1495 
1496  return mRetString(es);
1497 }
1499 char*
1500 StDbSql::getElementListIN(int* e, int num){
1501 // prepares comma separated list of integers
1502 // using the IN operator
1503 
1504 
1505  StString es;
1506  if(!e){
1507  es<<0;
1508  } else {
1509  es<<"In (";
1510  for(int i=0;i<num-1;i++) es<<e[i]<<",";
1511  es<<e[num-1];
1512  }
1513  es<<")";
1514  return mRetString(es);
1515 }
1516 
1518 char*
1519 StDbSql::getColumnList(StDbTable* table,char* tableName,char* funcName){
1520 
1521  StTableDescriptorI* desc=table->getDescriptor();
1522  int numElements=desc->getNumElements();
1523 
1524  StString es; es<<" ";
1525 
1526  int icount=0;
1527  for(int i=0;i<numElements;i++){
1528  if(funcName && (desc->getElementLength(i)>1))continue;
1529  char* name=desc->getElementName(i);
1530  if(funcName)es<<funcName<<"(";
1531  if(tableName)es<<tableName<<".";
1532  es<<name;
1533  if(funcName)es<<") as "<<name;
1534  if(i<(numElements-1))es<<", ";
1535  delete [] name;
1536  icount++;
1537  }
1538 
1539  if(icount) return mRetString(es);
1540  return (char*) 0;
1541 }
1542 
1544 char*
1545 StDbSql::getEmptyString(){
1546 
1547  StString es; es<<" ";
1548  return mRetString(es);
1549 
1550 };
1551 
1553 char*
1554 StDbSql::checkTablePrepForQuery(StDbTable* table, bool checkIndexed){
1555 
1556  if(mretString){
1557  delete [] mretString;
1558  mretString=new char[256];
1559  }else{mretString=new char[256];}
1560 
1561  if(!QueryDescriptor(table))
1562  return strcpy(mretString," doesn't have a descriptor ");
1563 
1564  if(table->IsBinary())
1565  return strcpy(mretString," Binary Store is currently disabled ");
1566 
1567  if(checkIndexed && !table->IsIndexed())
1568  return strcpy(mretString," Table is not time indexed");
1569 
1570  delete [] mretString;
1571  return mretString=0;
1572 }
1573 
1575 bool
1576 StDbSql::checkForNull(const char* src){
1577  return (strstr(src,"null")) ? true : false;
1578 }
1579 
1581 char*
1582 StDbSql::getDataTable(StDbTable* table, unsigned int time){
1583 
1584  if(mtableCatalog==0) checkTableCatalog();
1585  if(mtableCatalog==1) return table->getCstructName();
1586  char* retVal=0;
1587  clear();
1588 
1589 // as of now, just use name... later maybe more so I've sent in StDbTable*
1590 char* tableName=table->printName();
1591 
1592 Db<<" select * from tableCatalog where nodeName='"<<tableName<<"'";
1593 Db<<" AND unix_timestamp(beginTime)<="<<time;
1594 Db<<" Order by beginTime desc limit 1"<<endsql;
1595 
1596 if(Db.Output(&buff))buff.ReadScalar(retVal,"tableName");
1597 clear();
1598 
1599 return (retVal) ? retVal : table->getCstructName();
1600 }
1601 
1603 char**
1604 StDbSql::getDataTables(StDbTable* table,int& numTables){
1605 
1606  char** retVal;
1607  if(mtableCatalog==0) checkTableCatalog();
1608  if(mtableCatalog==1){
1609  numTables=1;
1610  retVal=new char*[1];
1611  retVal[0]=table->getCstructName();
1612  return retVal;
1613  }
1614  clear();
1615 
1616 // as of now, just use name... later maybe more so I've sent in StDbTable*
1617  char* tableName=table->printName();
1618  Db<<" select * from tableCatalog where nodeName='"<<tableName<<"'"<<endsql;
1619 
1620  if(!((numTables)=Db.NbRows()) ){ // use tableName as the 1
1621  numTables++;
1622  retVal= new char*[numTables];
1623  retVal[0]=new char[strlen(tableName)+1];
1624  strcpy(retVal[0],tableName);
1625  return retVal;
1626  }
1627 
1628  retVal=new char*[numTables];
1629  int i=0;
1630  while(Db.Output(&buff)) if(buff.ReadScalar(retVal[i],"tableName"))i++;
1631 
1632  numTables=i;
1633 return retVal;
1634 }
1635 
1637 bool
1638 StDbSql::hasInstance(StDbTable* table){
1639 
1640  // try and find a data instance of this table
1641  // for use in keeping track of baseline instances
1642  bool retVal=false;
1643  clear();
1644 
1645  int numTables;
1646  char** dataTables=getDataTables(table,numTables);
1647  if(numTables==0) return retVal;
1648 
1649  for(int i=0; i<numTables;i++){
1650 
1651  Db<<" select * from "<<dataTables[i];
1652  Db<<" where "<<getFlavorQuery(table->printFlavor());
1653  Db<<" AND "<<getProdTimeQuery(table->getProdTime())<<endsql;
1654 
1655  if(Db.NbRows() !=0 ){
1656  retVal=true;
1657  break;
1658  }
1659  }
1660  for(int k=0;k<numTables;k++) delete [] dataTables[k];
1661  delete [] dataTables;
1662  return retVal;
1663 }
1664 
1666 void
1667 StDbSql::checkTableCatalog(){
1668 
1669  // if(Db.checkForTable("tableCatalog"))mtableCatalog=1
1670  Db<<"show tables like 'tableCatalog'"<<endsql;
1671  if(Db.NbRows()==0){
1672  mtableCatalog=1;
1673  } else {
1674  mtableCatalog=2;
1675  }
1676  Db.Release();
1677 }
1678 
1680 void StDbSql::setDbUtils(MysqlDb& db, StDbBuffer& buffer){
1681  Db=db;
1682  buff=buffer;
1683 };
1684 
1685 #undef __CLASS__
StDbManager::Instance
static StDbManager * Instance()
strdup(..) is not ANSI
Definition: StDbManager.cc:155
Generated by   doxygen 1.8.5