EIC Software
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
FairDbProxy.cxx
Go to the documentation of this file. Or view the newest version in sPHENIX GitHub for file FairDbProxy.cxx
1 #include <memory>
2 #include <cassert>
3 
4 #include "TCollection.h"
5 #include "TList.h"
6 #include "TSQLColumnInfo.h"
7 #include "TSQLStatement.h"
8 #include "TSQLServer.h"
9 #include "TSQLTableInfo.h"
10 #include "TString.h"
11 
12 #include "Detector.h"
13 #include "SimFlag.h"
14 #include "FairDbProxy.h"
15 #include "FairDbMultConnector.h"
16 #include "FairDbFieldType.h"
17 #include "FairDbResult.h"
18 #include "FairDbServices.h"
19 #include "FairDbString.h"
20 #include "FairDbStatement.h"
21 #include "FairDbTableMetaData.h"
22 #include "FairDbTimerManager.h"
23 #include "ValContext.h"
24 #include "FairDbString.h"
25 
26 ClassImp(FairDbProxy)
27 
28 
29 FairDbProxy::FairDbProxy(FairDbMultConnector& cascader,
30  const string& tableName,
31  const FairDbTableMetaData* metaData,
32  const FairDbTableMetaData* metaValid,
33  const FairDbTableProxy* tableProxy) :
34  fMultConnector(cascader),
35  fMetaData(metaData),
36  fMetaValid(metaValid),
37  fSqlCondition(),
38  fTableName(tableName),
39  fTableNameUc(FairUtilString::ToUpper(tableName)),
40  fTableProxy(tableProxy),
41  fValSuffix("VAL")
42 {
43  if ( fTableName != fTableNameUc ) { fValSuffix = "Validity"; }
44 
45 }
46 
47 FairDbProxy::~FairDbProxy()
48 {
49 
50 }
51 
52 void FairDbProxy::FindTimeBoundaries(const ValContext& vc,
53  const FairDb::Task& task,
54  UInt_t dbNo,
55  ValTimeStamp earliestCreate,
56  ValTimeStamp& start,
57  ValTimeStamp& end) const
58 {
59  cout << "FindTimeBoundaries for table " << fTableName
60  << " context " << vc
61  << " task " << task
62  << " Earliest creation date " << earliestCreate
63  << " database " << dbNo << endl;
64 
65 // Set the limits wide open
66  start = ValTimeStamp(0,0);
67  end = ValTimeStamp(0x7FFFFFFF,0);
68 
69 // Construct a Time Gate on the current date.
70 
71  const ValTimeStamp curVTS = vc.GetTimeStamp();
72  Int_t timeGate = FairDb::GetTimeGate(this->GetTableName());
73  time_t vcSec = curVTS.GetSec() - timeGate;
74  ValTimeStamp startGate(vcSec,0);
75  vcSec += 2*timeGate;
76  ValTimeStamp endGate(vcSec,0);
77  string earliestCreateString(FairDb::MakeDateTimeString(earliestCreate));
78  string startGateString(FairDb::MakeDateTimeString(startGate));
79  string endGateString(FairDb::MakeDateTimeString(endGate));
80 
81 // Extract information for ValContext.
82 
83  Detector::Detector_t detType(vc.GetDetector());
84  SimFlag::SimFlag_t simFlg(vc.GetSimFlag());
85 
86 // Use an auto_ptr to manage ownership of FairDbStatement and TSQLStatement
87  auto_ptr<FairDbStatement> stmtDb(fMultConnector.CreateStatement(dbNo));
88 
89  for (int i_limit =1; i_limit <= 4; ++i_limit ) {
90  FairDbString sql("select ");
91  if ( i_limit == 1 ) sql << "min(TIMESTART) from " << fTableName
92  << "VAL where TIMESTART > '" << endGateString << "' ";
93  if ( i_limit == 2 ) sql << "min(TIMEEND) from " << fTableName
94  << "VAL where TIMEEND > '" << endGateString << "' ";
95  if ( i_limit == 3 ) sql << "max(TIMESTART) from " << fTableName
96  << "VAL where TIMESTART < '" << startGateString << "' ";
97  if ( i_limit == 4 ) sql << "max(TIMEEND) from " << fTableName
98  << "VAL where TIMEEND < '" << startGateString << "' ";
99  sql << " and DetectorMask & " << static_cast<unsigned int>(detType)
100  << " and SimMask & " << static_cast<unsigned int>(simFlg)
101  << " and CREATIONDATE >= '" << earliestCreateString << "'"
102  << " and Task = " << task;
103  cout << " FindTimeBoundaries query no. " << i_limit
104  << " SQL:" <<sql.c_str() << endl;
105 
106  auto_ptr<TSQLStatement> stmt(stmtDb->ExecuteQuery(sql.c_str()));
107  stmtDb->PrintExceptions(0);
108 
109 // If the query returns data, convert to a time stamp and trim the limits
110  TString date;
111  if ( ! stmt.get() || ! stmt->NextResultRow() || stmt->IsNull(0) ) { continue; }
112  date = stmt->GetString(0);
113  if ( date.IsNull() ) { continue; }
114  ValTimeStamp ts(FairDb::MakeTimeStamp(date.Data()));
115  cout << " FindTimeBoundaries query result: " << ts << endl;
116  if ( i_limit <= 2 && ts < end ) { end = ts; }
117  if ( i_limit >= 3 && ts > start ) { start = ts; }
118 
119  }
120 
121  cout << "FindTimeBoundaries for table " << fTableName
122  << " found " << start << " .. " << end << endl;
123 
124 }
125 
126 UInt_t FairDbProxy::GetNumDb() const
127 {
128  return fMultConnector.GetNumDb();
129 }
130 
131 FairDbResultSet* FairDbProxy::QueryAllValidities (UInt_t dbNo,UInt_t seqNo) const
132 {
133 
134  FairDbString sql;
135 
136  sql << "select * from " << fTableName << fValSuffix;
137  if ( fSqlCondition != "" ) { sql << " where " << fSqlCondition; }
138  if ( seqNo > 0 ) {
139  if ( fSqlCondition == "" ) { sql << " where "; }
140  else { sql << " and "; }
141  sql << "seqno = " << seqNo;
142  }
143  sql << ";" << '\0';
144 
145  cout << "Database: " << dbNo
146  << " query: " << sql.GetString() << endl;
147 
148 // Apply query and return result..
149  FairDbStatement* stmtDb = fMultConnector.CreateStatement(dbNo);
150  return new FairDbResultSet(stmtDb,sql,fMetaValid,fTableProxy,dbNo);
151 
152 }
153 
154 
155 
156 FairDbResultSet* FairDbProxy::QuerySeqNo(UInt_t seqNo, UInt_t dbNo) const
157 {
158 
159  FairDbTimerManager::gTimerManager.RecMainQuery();
160  FairDbString sql;
161  sql << "select * from " << fTableName << " where "
162  << " SEQNO= " << seqNo;
163 
164  if ( FairDbServices::OrderContextQuery() && fMetaData->HasRowCounter() ) {
165  sql << " order by ROW_COUNTER";
166  }
167 
168  cout << "Database: " << dbNo
169  << " SeqNo query: " << sql.c_str() << endl;
170 
171 // Apply query and return result..
172  FairDbStatement* stmtDb = fMultConnector.CreateStatement(dbNo);
173  return new FairDbResultSet(stmtDb,sql,fMetaData,fTableProxy,dbNo);
174 
175 }
176 FairDbResultSet* FairDbProxy::QuerySeqNos(SeqList_t& seqNos,
177  UInt_t dbNo,
178  const string& sqlData,
179  const string& fillOpts) const
180 {
181  if ( seqNos.size() == 0 ) { return 0; }
182 
183  FairDbTimerManager::gTimerManager.RecMainQuery();
184  FairDbString sql;
185  sql << "select * from " << fTableName << " where ";
186 
187  if ( sqlData != "" ) { sql << "( "; }
188  Bool_t first = kTRUE;
189  SeqList_t::const_iterator itr1 = seqNos.begin();
190 
191  while ( itr1 != seqNos.end() ) {
192  UInt_t seq1 = *itr1;
193  UInt_t seq2 = seq1;
194  SeqList_t::const_iterator itr2 = itr1;
195  while ( itr2 != seqNos.end() && seq2 == *itr2 ) {
196  ++itr2;
197  ++seq2;
198  }
199  if ( first ) {
200  first = kFALSE;
201  } else {
202  sql << "or ";
203  }
204  if ( seq2 > seq1 + 1) {
205  sql << "SEQNO between " << seq1 << " and " << seq2-1 << ' ';
206  itr1 = itr2;
207  } else {
208  sql << "SEQNO = " << seq1 << ' ';
209  ++itr1;
210  }
211  }
212 
213  if ( sqlData != "" ) { sql << ") and " << "(" << sqlData << ")" << " "; }
214 
215  sql << "order by SEQNO";
216 
217  if ( FairDbServices::OrderContextQuery() && fMetaData->HasRowCounter() ) {
218  sql << ",ROW_COUNTER";
219  }
220 
221  cout << "Database: " << dbNo
222  << " SeqNos query: " << sql.c_str() << endl;
223 
224 // Apply query and return result..
225  FairDbStatement* stmtDb = fMultConnector.CreateStatement(dbNo);
226  return new FairDbResultSet(stmtDb,sql,fMetaData,fTableProxy,dbNo,fillOpts);
227 
228 }
229 FairDbResultSet* FairDbProxy::QueryValidity (const ValContext& vc,
230  const FairDb::Task& task,
231  UInt_t dbNo) const
232 {
233 
234  const ValTimeStamp curVTS = vc.GetTimeStamp();
235  Int_t timeGate = FairDb::GetTimeGate(this->GetTableName());
236  time_t vcSec = curVTS.GetSec() - timeGate;
237  ValTimeStamp startGate(vcSec,0);
238  vcSec += 2*timeGate;
239  ValTimeStamp endGate(vcSec,0);
240 
241 // Extract information for ValContext.
242 
243  string startGateString(FairDb::MakeDateTimeString(startGate));
244  string endGateString(FairDb::MakeDateTimeString(endGate));
245  Detector::Detector_t detType(vc.GetDetector());
246  SimFlag::SimFlag_t simFlg(vc.GetSimFlag());
247 
248 // Generate SQL for context.
249 
250  FairDbString context;
251  context << " TimeStart <= '" << endGateString << "' "
252  << "and TimeEnd > '" << startGateString << "' "
253  << "and DetectorMask & " << static_cast<unsigned int>(detType)
254  << " and SimMask & " << static_cast<unsigned int>(simFlg);
255 
256 // Apply query and return result..
257 
258  return this->QueryValidity(context.GetString(),task,dbNo);
259 
260 }
261 
262 FairDbResultSet* FairDbProxy::QueryValidity (const string& context,
263  const FairDb::Task& task,
264  UInt_t dbNo) const
265 {
266 
267 // Generate SQL for validity table.
268 
269  FairDbString sql;
270 
271 // Queries are normally ordered by creation date (the later the better)
272 // but make an exception for DBUSUBRUNSUMMARY which has open-ended
273 // end date and an unreliable creation date so the latest start date is
274 // best.
275 // Same for DBURUNSUMMARY. Bogus entries with wrong validity are stomping
276 // real values for the same reason. These should be cleaned +up in the DB
277 // but TIMESTART should be always more valid than CREATIONDATE for such info
278  string orderByName("CREATIONDATE");
279  if ((fTableName == "DBUSUBRUNSUMMARY") || (fTableName == "DBURUNSUMMARY")) {
280  orderByName = "TIMESTART";
281  }
282  sql << "select * from " << fTableName << fValSuffix
283  << " where " ;
284  if ( fSqlCondition != ""
285  ) { sql << fSqlCondition << " and "; }
286  sql << context;
287  if ( task != FairDb::kAnyTask
288  ) sql << " and Task = " << task
289  << " order by " << orderByName << " desc;" << '\0';
290 
291  cout << "Database: " << dbNo
292  << " query: " << sql.c_str() << endl;
293 
294 // Apply query and return result..
295 
296  FairDbStatement* stmtDb = fMultConnector.CreateStatement(dbNo);
297  return new FairDbResultSet(stmtDb,sql,fMetaValid,fTableProxy,dbNo);
298 
299 }
300 //.....................................................................
301 
302 FairDbResultSet* FairDbProxy::QueryValidity (UInt_t seqNo,
303  UInt_t dbNo) const
304 {
305 // Generate SQL for validity table.
306 
307  FairDbString sql;
308  sql << "select * from " << fTableName << fValSuffix << " where ";
309  if ( fSqlCondition != "" ) { sql << fSqlCondition << " and "; }
310  sql << "SEQNO = " << seqNo << ";";
311 
312  cout << "Database: " << dbNo
313  << " SEQNO query: " << sql.c_str() << endl;
314 
315 // Apply query and return result..
316 
317  FairDbStatement* stmtDb = fMultConnector.CreateStatement(dbNo);
318  return new FairDbResultSet(stmtDb,sql,fMetaValid,fTableProxy,dbNo);
319 
320 }
321 
322 Bool_t FairDbProxy::RemoveSeqNo(UInt_t seqNo,
323  UInt_t dbNo) const
324 {
325 // Generate SQL to remove SeqNo in main table.
326  FairDbString sql;
327  sql << "delete from " << fTableName
328  << " where SEQNO = " << seqNo << ";"
329  << '\0';
330 
331  cout << "Database: " << dbNo
332  << " RemoveSeqNo SQL: " << sql.c_str() << endl;
333 
334 // Apply query.
335  auto_ptr<FairDbStatement> stmtDb(fMultConnector.CreateStatement(dbNo));
336  if ( ! stmtDb.get() ) { return false; }
337  if ( ! stmtDb->ExecuteUpdate(sql.c_str()) || stmtDb->PrintExceptions() ) {
338  cout << "SQL: " << sql.c_str()
339  << " Failed. " << endl;
340  return false;
341  }
342 
343 // Generate SQL to remove SeqNo in validity table.
344  sql.GetString().erase();
345  sql << "delete from " << fTableName << fValSuffix
346  << " where SEQNO = " << seqNo << ";"
347  << '\0';
348 
349  cout << "Database: " << dbNo
350  << " RemoveSeqNo SQL: " << sql.c_str() << endl;
351 
352 // Apply query.
353  if ( ! stmtDb->ExecuteUpdate(sql.c_str()) || stmtDb->PrintExceptions() ) {
354  cout << "SQL: " << sql.c_str()
355  << " Failed. " << endl;
356  return false;
357  }
358 
359  return true;
360 
361 }
362 
363 Bool_t FairDbProxy::ReplaceInsertDate(const ValTimeStamp& ts,
364  UInt_t SeqNo,
365  UInt_t dbNo) const
366 {
367 
368 // Generate SQL.
369  FairDbString sql;
370  sql << "update " << fTableName << fValSuffix
371  << " set INSERTDATE = \'" << ts.AsString("s")
372  << "\' where SEQNO = " << SeqNo << ";"
373  << '\0';
374 
375  cout << "Database: " << dbNo
376  << " ReplaceInsertDate SQL: "
377  << sql.c_str() << endl;
378 
379 // Apply query.
380  auto_ptr<FairDbStatement> stmtDb(fMultConnector.CreateStatement(dbNo));
381  if ( ! stmtDb.get() ) { return false; }
382  if (! stmtDb->ExecuteUpdate(sql.c_str()) || stmtDb->PrintExceptions() ) {
383  cout << "SQL: " << sql.c_str()
384  << " Failed. " << endl;
385  return false;
386  }
387 
388  return true;
389 
390 }
391 //.....................................................................
392 
393 Bool_t FairDbProxy::ReplaceSeqNo(UInt_t oldSeqNo,
394  UInt_t newSeqNo,
395  UInt_t dbNo) const
396 {
397 
398  if ( ! fMultConnector.GetConnection(dbNo) ) {
399  cout
400  << "Cannot renumber " << oldSeqNo
401  << " no connection to cascade entry " << dbNo << endl;
402  return false;
403  }
404 // Deal with Oracle separately - it's best.
405  if ( fMultConnector.GetConnection(dbNo)->GetDbType () == FairDb::kOracle ) {
406  return this->ReplaceSeqNoOracle(oldSeqNo,newSeqNo,dbNo);
407  }
408 
409 // Generate SQL to replace SeqNo in validity table.
410  FairDbString sql;
411  sql << "update " << fTableName << fValSuffix
412  << " set SEQNO = " << newSeqNo
413  << " where SEQNO = " << oldSeqNo << ";"
414  << '\0';
415 
416  cout << "Database: " << dbNo
417  << " ReplaceSeqNo SQL: " << sql.c_str() << endl;
418 
419 // Apply query.
420  auto_ptr<FairDbStatement> stmtDb(fMultConnector.CreateStatement(dbNo));
421  if ( ! stmtDb.get() ) { return false; }
422  if ( ! stmtDb->ExecuteUpdate(sql.c_str()) || stmtDb->PrintExceptions() ) {
423  cout << "SQL: " << sql.c_str()
424  << " Failed. " << endl;
425  return false;
426  }
427 
428 // Generate SQL to replace SeqNo in main table.
429  sql.GetString().erase();
430  sql << "update " << fTableName
431  << " set SEQNO = " << newSeqNo
432  << " where SEQNO = " << oldSeqNo << ";"
433  << '\0';
434 
435  cout << "Database: " << dbNo
436  << " ReplaceSeqNo SQL: " << sql.c_str() << endl;
437 
438 // Apply query.
439  if ( ! stmtDb->ExecuteUpdate(sql.c_str()) || stmtDb->PrintExceptions() ) {
440  cout << "SQL: " << sql.c_str()
441  << " Failed. " << endl;
442  return false;
443  }
444 
445  return true;
446 
447 }
448 
449 Bool_t FairDbProxy::ReplaceSeqNoOracle(UInt_t oldSeqNo,
450  UInt_t newSeqNo,
451  UInt_t dbNo) const
452 {
453 
454  FairDbResultSet* rsOld = QueryValidity(oldSeqNo,dbNo);
455  if ( rsOld && rsOld->IsBeforeFirst() ) { rsOld->FetchRow(); }
456  if ( ! rsOld || rsOld->IsExhausted() ) {
457  cout << "Cannot renumber " << oldSeqNo
458  << " it does not exist" << endl;
459  delete rsOld;
460  return false;
461  }
462 
463 // Generate SQL to add new SeqNo in validity vable.
464 
465  FairDbString sql;
466  sql << "insert into " << fTableName << fValSuffix
467  << " values (" << newSeqNo;
468 
469  rsOld->IncrementCurCol();
470  UInt_t numCols =rsOld->NumCols();
471  for (UInt_t iCol = 2; iCol <= numCols; ++iCol) {
472  UInt_t concept = rsOld->CurColFieldType().GetConcept();
473  string delim = "";
474  if ( concept == FairDb::kString
475  || concept == FairDb::kDate
476  || concept == FairDb::kChar ) { delim = "\'"; }
477  sql << "," << delim << rsOld->CurColValue() << delim ;
478  rsOld->IncrementCurCol();
479  }
480  sql << ")";
481  cout << "Database: " << dbNo
482  << " ReplaceSeqNo SQL (insert new VAL): " << sql.c_str() << endl;
483  delete rsOld;
484 
485 // Apply query.
486  auto_ptr<FairDbStatement> stmtDb(fMultConnector.CreateStatement(dbNo));
487  if ( ! stmtDb.get() ) { return false; }
488  if ( ! stmtDb->ExecuteUpdate(sql.c_str()) || stmtDb->PrintExceptions() ) {
489  cout << "SQL: " << sql.c_str()
490  << " Failed. " << endl;
491  return false;
492  }
493 
494 // Generate SQL to replace SeqNo in main table.
495  sql.GetString().erase();
496  sql << "update " << fTableName
497  << " set SEQNO = " << newSeqNo
498  << " where SEQNO = " << oldSeqNo << ";";
499 
500  cout << "Database: " << dbNo
501  << " ReplaceSeqNo SQL (rename main entry): " << sql.c_str() << endl;
502 
503 // Apply query.
504  if ( ! stmtDb->ExecuteUpdate(sql.c_str()) || stmtDb->PrintExceptions() ) {
505  cout << "SQL: " << sql.c_str()
506  << " Failed. " << endl;
507  return false;
508  }
509 
510 // Generate SQL to remove old SeqNo in validity table.
511  sql.GetString().erase();
512  sql << "delete from " << fTableName << fValSuffix
513  << " where SEQNO = " << oldSeqNo << ";";
514 
515  cout << "Database: " << dbNo
516  << " ReplaceSeqNo SQL (delete old VAL): " << sql.c_str() << endl;
517 
518 // Apply query.
519  if ( ! stmtDb->ExecuteUpdate(sql.c_str()) || stmtDb->PrintExceptions() ) {
520  cout << "SQL: " << sql.c_str()
521  << " Failed. " << endl;
522  return false;
523  }
524 
525  return true;
526 
527 }
528 
529 void FairDbProxy::StoreMetaData(FairDbTableMetaData& metaData) const
530 {
531 
532 
533  const char* tableName = metaData.TableName().c_str();
534  cout << "Get meta-data for table: " << tableName << endl;
535 
536 // Check each Db in turn until table found and store table meta data.
537 
538  for ( UInt_t dbNo = 0; dbNo < fMultConnector.GetNumDb(); dbNo++ ) {
539  FairDbConnection* connection = fMultConnector.GetConnection(dbNo);
540  TSQLServer* server = connection->GetServer();
541  if ( ! server ) { continue; }
542  connection->Connect();
543  TSQLTableInfo* meta = server->GetTableInfo(tableName);
544  if ( ! meta ) {
545  connection->DisConnect();
546  continue;
547  }
548  cout << "Meta-data query succeeded on cascade entry " << dbNo << endl;
549 
550  // Clear out any existing data, although there should not be any.
551  metaData.Clear();
552 
553  const TList* cols = meta->GetColumns();
554  TIter colItr(cols);
555  int col = 0;
556  while ( TSQLColumnInfo* colInfo = dynamic_cast<TSQLColumnInfo*>(colItr.Next()) ) {
557 
558  ++col;
559  string name(colInfo->GetName());
560  name = FairUtilString::ToUpper(name);
561  metaData.SetColName(name,col);
562 
563  FairDbFieldType fldType(colInfo->GetSQLType(),
564  colInfo->GetLength(),
565  colInfo->GetTypeName());
566 
567  // For now continue to check for unsigned (even though not supported)
568  if ( !colInfo->IsSigned() ) { fldType.SetUnsigned(); }
569  metaData.SetColFieldType(fldType,col);
570 
571  metaData.SetColIsNullable(col,colInfo->IsNullable());
572 
573 
574  cout << "Column " << col << " " << name
575  << " SQL type " << colInfo->GetSQLType()
576  << " SQL type name " << colInfo->GetTypeName()
577  << " DBI type " << fldType.AsString()
578  << " data size: " << fldType.GetSize()
579  << " col size: " << colInfo->GetLength() << endl;
580 
581  }
582  delete meta;
583  connection->DisConnect();
584  return;
585  }
586 }
587 
588 
589 Bool_t FairDbProxy::TableExists(Int_t selectDbNo) const
590 {
591 
592  return fMultConnector.TableExists(fTableName,selectDbNo);
593 
594 }
595