LArSoft  v09_90_00
Liquid Argon Software toolkit - https://larsoft.org/
Table.cpp
Go to the documentation of this file.
1 #include <iostream>
2 #include <iomanip>
3 #include <fstream>
4 #include <sstream>
5 #include <sys/stat.h>
6 #include <cctype>
7 #include <algorithm>
8 #include <ctime>
9 #include <cinttypes>
10 #include <cstdio>
11 
12 #include <libpq-fe.h>
13 
14 #include <boost/algorithm/string.hpp>
15 #include <boost/algorithm/string/case_conv.hpp>
16 #include <boost/date_time/posix_time/posix_time.hpp>
17 
18 #include "wda.h"
19 
21 #include <nuevdb/IFDatabase/Util.h>
22 
23 namespace {
24  struct LibwdaSentry {
25  LibwdaSentry() { wda_global_init(); }
26  ~LibwdaSentry() { wda_global_cleanup(); }
27  };
28  LibwdaSentry sentry;
29 }
30 
31 namespace nutools {
32  namespace dbi {
33 
34  //************************************************************
35 
36  Table::Table() : fHasRecordTime(false),
37  fFlushCache(false),
38  fDisableCache(false),
39  fMaxTSVld(0),fMinTSVld(0),
40  fRecordTime(0)
41  {
42  fTableName="";
43  fConnection=0;
44  fHasConnection=false;
45  fDetector="";
46  fDBHost="";
47  fDBName="";
48  fDBPort="";
49  fUser="";
50  fSchema="undef";
52  fDataTypeMask = 0;
54 
55  fIgnoreEnvVar = false;
56  fTestedExists = false;
57  fExistsInDB = false;
58  addInsertTime = addUpdateTime = false;
59  addInsertUser = addUpdateUser = false;
60  fIgnoreDB = false;
61  fTimeQueries = true;
62  fTimeParsing = true;
63  fMinChannel = 0;
64  fMaxChannel = 0;
65  fFolder = "";
66 
67  Reset();
68 
69  srandom(time(0));
70 
71  // default total time to attempt to connect to the db/web server will be
72  // ~4 minutes (some randomness is introduced by libwda)
73  fConnectionTimeout = 4*60;
74  // override default timeout if env. variable is set, but must be
75  // greater than 20 seconds
76  char* tmpStr = getenv("DBITIMEOUT");
77  if (tmpStr) {
78  int tmpTO = atoi(tmpStr);
79  if (tmpTO > 20)
80  fConnectionTimeout = tmpTO;
81  }
82 
83  fTag = "";
84  fWSURL = "";
85  const char* wsHost = getenv("DBIWSURL");
86  if (wsHost) fWSURL = std::string(wsHost);
87 
88  fUConDBURL = "";
89  const char* ucondbHost = getenv("DBIUCONDBURL");
90  if (ucondbHost) fUConDBURL = std::string(ucondbHost);
91 
92  fQEURL = "";
93  const char* qeHost = getenv("DBIQEURL");
94  if (qeHost) fQEURL = std::string(qeHost);
95 
96  fVerbosity=0;
97  tmpStr = getenv("DBIVERB");
98  if (tmpStr) {
99  fVerbosity = atoi(tmpStr);
100  }
101  }
102 
103  //************************************************************
104 
105  Table::Table(std::string schemaName, std::string tableName,
106  int ttype,
107  std::string dbhost, std::string dbname,
108  std::string dbport, std::string dbuser)
109  {
110  fConnection=0;
111  fHasConnection=false;
112  std::string errStr;
113  fIgnoreDB = false;
114 
115  fTimeQueries = true;
116  fTimeParsing = true;
117 
118  fMinChannel = 0;
119  fMaxChannel = 0;
120 
121  fFolder = "";
122 
124 
125  fVerbosity=0;
126  char* tmpStr = getenv("DBIVERB");
127  if (tmpStr) {
128  fVerbosity = atoi(tmpStr);
129  }
130 
131  // default total time to attempt to connect to the db/web server will be
132  // ~4 minutes (some randomness is introduced by libwda)
133  fConnectionTimeout = 4*60;
134  // override default timeout if env. variable is set, but must be
135  // greater than 20 seconds
136  tmpStr = getenv("DBITIMEOUT");
137  if (tmpStr) {
138  int tmpTO = atoi(tmpStr);
139  if (tmpTO > 20)
140  fConnectionTimeout = tmpTO;
141  }
142 
143  if (!dbname.empty()) SetDBName(dbname);
144  /*
145  if (DBName() == "") {
146  errStr = "Table::Table(): missing database name!";
147  throw std::runtime_error(errStr);
148  }
149  */
150  if (!dbhost.empty()) SetDBHost(dbhost);
151  /*
152  if (DBHost() == "") {
153  errStr = "Table::Table(): missing database host!";
154  throw std::runtime_error(errStr);
155  }
156  */
157 
158  if (!dbport.empty()) SetDBPort(dbport);
159  if (!dbuser.empty()) SetUser(dbuser);
160 
161  this->SetTableName(tableName);
162  fSchema = std::string(schemaName);
163  boost::to_lower(fSchema);
164 
165  std::string stName = fSchema + std::string(".") + std::string(tableName);
166  // fIgnoreEnvVar = true;
167 
168  if (!ExistsInDB()) {
169  errStr = "Table::Table(): table \'" + stName + "\' not found in database!";
170  throw std::runtime_error(errStr);
171  }
172 
173  Reset();
174  fCol.clear();
175 
176  bool hasConn = fHasConnection;
177  if (! fHasConnection) {
178  GetConnection();
179  hasConn = false;
180  }
181 
182  std::vector<std::string> pkeyList;
183  // get list of rows that are primary keys
184  std::string cmd = "SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute WHERE indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary AND pg_class.oid = '" + stName + "'::regclass";
185 
186  PGresult* res = PQexec(fConnection,cmd.c_str());
187 
188  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
189  errStr = "Table::Table(): command failed: " + std::string(PQerrorMessage(fConnection));
190  if (fVerbosity > 0)
191  std::cerr << errStr << std::endl;
192  PQclear(res);
193  CloseConnection();
194  throw std::runtime_error(errStr);
195  }
196 
197  int nRow = PQntuples(res);
198  if (nRow == 0) {
199  errStr = std::string("Table::Table(): no primary keys defined for table \'") + tableName + std::string("\', unable to proceed.");
200  PQclear(res);
201  fExistsInDB = false;
202  CloseConnection();
203  throw std::runtime_error(errStr);
204  }
205 
206  for (int i=0; i<nRow; ++i) {
207  std::string key = std::string(PQgetvalue(res,i,0));
208  pkeyList.push_back(key);
209  }
210 
211  PQclear(res);
212 
213  // now get names and types of all columns
214  this->GetColsFromDB(pkeyList);
215 
216  if (!hasConn) CloseConnection();
217 
218  // now set the dB command cache file name
219  std::string dirName;
220  tmpStr = getenv("DBICACHEDIR");
221  if (tmpStr)
222  dirName = tmpStr;
223  else {
224  tmpStr = getenv("PWD");
225  if (tmpStr)
226  dirName = tmpStr;
227  else
228  dirName = "/";
229  }
230 
231  fDBCacheFile = dirName + "/" + "." + Name() + ".cache";
232 
233  }
234 
235  //************************************************************
236 
238  {
239  this->Clear();
241  }
242 
243  //************************************************************
244 
245  bool Table::GetColsFromDB(std::vector<std::string> pkeyList)
246  {
248  std::cerr << "Table::GetColsFromDB() currently disabled for unstructured conditions tables." << std::endl;
249  abort();
250  }
251 
252  bool hasConn = fHasConnection;
253  if (! fHasConnection) {
254  GetConnection();
255  hasConn = false;
256  }
257 
258  // now get names and types of all columns
259  std::string cmd = "SELECT column_name, data_type from information_schema.columns where table_name = \'" + std::string(fTableName);
261  cmd += "_update";
262  cmd += "\' and table_schema=\'" + fSchema + "\'";
263 
264  PGresult* res = PQexec(fConnection,cmd.c_str());
265 
266  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
267  std::string errStr = "Table::Table() command failed: " + std::string(PQerrorMessage(fConnection));
268  if (fVerbosity > 0)
269  std::cerr << errStr << std::endl;
270  PQclear(res);
271  fExistsInDB = false; // set this to false just in case
272  CloseConnection();
273  throw std::runtime_error(errStr);
274  }
275 
276  int nRow = PQntuples(res);
277 
278  for (int i=0; i<nRow; ++i) {
279  std::string cname = std::string(PQgetvalue(res,i,0));
280  std::string ctype = std::string(PQgetvalue(res,i,1));
281 
282  if (fTableType == kConditionsTable) {
283  if (cname == "__snapshot_id") continue;
284  if (cname == "__tr") continue;
285  if (cname == "__channel") continue; //cname = "channel";
286  if (cname == "__tv") continue; //cname = "tv";
287  }
288 
289  if (ctype == "smallint") ctype="short";
290  else if (ctype == "double precision") ctype="double";
291  else if (ctype == "boolean") ctype="bool";
292  else if (ctype == "timestamp without time zone") ctype="timestamp";
293  else if (ctype.substr(0,7) == "varchar" || ctype == "text")
294  ctype = "text"; //varchar" + ctype.substr(8,ctype.find(')')-1);
295 
296  // check if this column is "auto_incr", only if !conditions table
297  if (fTableType != kConditionsTable && ctype == "integer") {
298  std::string stName = fSchema + std::string(".") + std::string(fTableName);
299  cmd = "SELECT pg_get_serial_sequence(\'" + stName +
300  "\',\'" + cname + "\')";
301  PGresult* res2 = PQexec(fConnection,cmd.c_str());
302  int nRow2 = PQntuples(res2);
303  for (int j=0; j<nRow2; ++j) {
304  std::string tStr = std::string(PQgetvalue(res2,j,0));
305  if (tStr != "") ctype = "auto_incr";
306  }
307  PQclear(res2);
308  }
309 
310  // now create Column based on this info
311  ColumnDef cdef(cname,ctype);
312 
313  if (find(pkeyList.begin(),pkeyList.end(),cname) != pkeyList.end()) {
314  cdef.SetCanBeNull(false);
315  }
316  fCol.insert(fCol.begin(),cdef);
317 
318  if (cname == "inserttime") addInsertTime = true;
319  if (cname == "insertuser") addInsertUser = true;
320  if (cname == "updatetime") addUpdateTime = true;
321  if (cname == "updateuser") addUpdateUser = true;
322  }
323 
324  PQclear(res);
325 
326  if (!hasConn) CloseConnection();
327 
328  return true;
329  }
330 
331  //************************************************************
332 
333  int Table::AddCol(std::string cname, std::string ctype)
334  {
335  for (size_t i=0; i<fCol.size(); ++i) {
336  if (fCol[i].Name() == cname) {
337  std::cerr << "Table::AddCol: column \'" << cname << "\' already exists! Fatal, aborting..." << std::endl;
338  abort();
339  }
340  }
341 
342  ColumnDef cdef(cname,ctype);
343 
344  fCol.push_back(cdef);
345 
346  if (cname == "inserttime") addInsertTime = true;
347  if (cname == "insertuser") addInsertUser = true;
348  if (cname == "updatetime") addUpdateTime = true;
349  if (cname == "updateuser") addUpdateUser = true;
350 
351  return fCol.size()-1;
352  }
353 
354  //************************************************************
355 
356  void Table::AddRow(const Row* row)
357  {
358  if (!row) return;
359 
360  Row r2(*row);
361 
362  for (unsigned int i=0; i<fCol.size(); ++i) {
363  if (fCol[i].Name() == "inserttime" ||
364  fCol[i].Name() == "insertuser" ||
365  fCol[i].Name() == "updatetime" ||
366  fCol[i].Name() == "updateuser" ) continue;
367  if (!fCol[i].CanBeNull())
368  if (r2.Col(i).IsNull())
369  fNullList.push_back(std::pair<int,int>(fRow.size(),i));
370  }
371 
372  fRow.push_back(r2);
373 
374  }
375 
376  //************************************************************
377 
378  void Table::AddEmptyRows(unsigned int nrow)
379  {
380  Row* row = this->NewRow();
381 
382  fRow.resize(fRow.size()+nrow,*row);
383  }
384 
385  //************************************************************
386  void Table::AddRow(const Row& row)
387  {
388  AddRow(&row);
389  }
390 
391  //************************************************************
392  bool Table::RemoveRow(int i)
393  {
394  if (i < 0) return false;
395 
396  unsigned int j = i;
397 
398  if (j >= fRow.size()) return false;
399 
400  unsigned int kEnd = fNullList.size();
401  for (unsigned int k=0; k<kEnd; ++k)
402  if (fNullList[k].first == i) {
403  fNullList.erase(fNullList.begin()+k);
404  kEnd = fNullList.size();
405  }
406 
407  fRow.erase(fRow.begin()+j);
408 
409  return true;
410  }
411 
412  //************************************************************
413  Row* const Table::GetRow(int i)
414  {
415  if (i >= 0 && i < (int)fRow.size())
416  return &fRow[i];
417  else
418  return 0;
419  }
420 
421  //************************************************************
423  {
424  bool isOk = fNullList.empty();
425 
426  if (!isOk) // print out list of null columns
427  for (unsigned int i=0; i<fNullList.size(); ++i)
428  if (fVerbosity>0)
429  std::cerr << fCol[fNullList[i].second].Name() << " is NULL in row "
430  << fNullList[i].first << std::endl;
431 
432  return isOk;
433 
434  }
435 
436  //************************************************************
437 
438  void Table::CacheDBCommand(std::string cmd)
439  {
440  std::ofstream fout;
441  fout.open(fDBCacheFile.c_str(),std::ios_base::app);
442 
443  fout << cmd << std::endl;
444 
445  fout.close();
446  }
447 
448  //************************************************************
449 
450  void Table::SetRecordTime(double t)
451  {
452  fRecordTime = t;
453  fHasRecordTime = true;
454  }
455 
456  //************************************************************
457 
459  {
460  if (t < 0 || t >= kNTableType) return false;
461  fTableType = t;
462 
463  return true;
464  }
465 
466  //************************************************************
468  {
469  fConnection = 0;
470  fHasConnection = 0;
471  fPKeyList.clear();
472  fDistinctCol.clear();
473  fVerbosity = 0;
474  fDescOrder = true;
475  fSelectLimit = 0;
476  fSelectOffset = 0;
477  ClearValidity();
478  fMinChannel = 0;
479  fMaxChannel = 0;
480  fExcludeCol.clear();
481  }
482 
483  //************************************************************
485  {
486  fValidityStart.clear();
487  fValidityEnd.clear();
488  fValiditySQL = "";
489  fValidityChanged=true;
490  }
491 
492  //************************************************************
494  {
495  if (fConnection)
496  std::cerr << PQerrorMessage(fConnection) << std::endl;
497  }
498 
499  //************************************************************
500  bool Table::SetDetector(std::string det)
501  {
502  fDetector = det;
503 
504  if (fTableType != kHardwareTable)
505  fSchema = det;
506  else
507  fSchema = "public";
508 
509  boost::to_lower(fSchema);
510 
511  return true;
512  }
513 
514  //************************************************************
515  bool Table::GetDetector(std::string& det) const
516  {
517  if (fDetector == "") return false;
518  det = fDetector;
519 
520  return true;
521  }
522 
523  //************************************************************
524  void Table::SetTableName(std::string tname) {
525  boost::to_lower(tname);
526  fTableName = tname;
527  }
528 
529  //************************************************************
530  void Table::SetTableName(const char* tname) {
531  std::string tnameStr = tname;
532  boost::to_lower(tnameStr);
533  fTableName = tnameStr;
534  }
535 
536  //************************************************************
537  void Table::SetDataSource(std::string ds) {
538  if (ds == std::string("DAQ"))
540  else if (ds == std::string("DCS"))
542  else if (ds == std::string("Offline"))
544  else
546  }
547 
548  //************************************************************
549  void Table::SetDataSource(int ids) {
550  if (ids >= 0 && ids < nutools::dbi::kNDataSources)
551  fDataSource = ids;
552  else
554  }
555 
556  //************************************************************
557  void Table::SetDBInfo(std::string name, std::string host, std::string port,
558  std::string user)
559  {
560  SetDBName(name);
561  SetDBHost(host);
562  SetDBPort(port);
563  SetUser(user);
564  }
565 
566  //************************************************************
567  void Table::SetDBInfo(const char* name, const char* host, const char* port,
568  const char* user)
569  {
570  SetDBName(name);
571  SetDBHost(host);
572  SetDBPort(port);
573  SetUser(user);
574  }
575 
576  //************************************************************
577  const nutools::dbi::ColumnDef* Table::GetCol(std::string& cname)
578  {
579  unsigned int i=0;
580  for ( ; i < fCol.size(); ++i)
581  if (fCol[i].Name() == cname) break;
582 
583  if (i >= fCol.size()) return 0;
584 
585  return &fCol[i];
586  }
587 
588  //************************************************************
589  int Table::GetColIndex(std::string cname)
590  {
591  for (unsigned int i=0; i<fCol.size(); ++i)
592  if (fCol[i].Name() == cname) return (int)i;
593  std::cerr << "No such column \"" << cname << "\". Returning -1" << std::endl;
594  return -1;
595  }
596 
597  //************************************************************
598  std::map<std::string,int> Table::GetColNameToIndexMap()
599  {
600  std::map<std::string,int> tmap;
601  for (unsigned int i=0; i<fCol.size(); ++i) {
602  tmap[fCol[i].Name()] = int(i);
603  }
604  return tmap;
605  }
606 
607  //************************************************************
608  std::vector<std::string> Table::GetColNames()
609  {
610  std::vector<std::string> nameList;
611 
612  for (unsigned int i=0; i<fCol.size(); ++i)
613  nameList.push_back(fCol[i].Name());
614 
615  return nameList;
616  }
617 
618  //************************************************************
619  void Table::SetTolerance(std::string& cname, float t)
620  {
621  unsigned int i=0;
622  for ( ; i < fCol.size(); ++i)
623  if (fCol[i].Name() == cname) break;
624 
625  if (i >= fCol.size()) return;
626 
627  fCol[i].SetTolerance(t);
628 
629  }
630 
631  //************************************************************
632  float Table::Tolerance(std::string& cname)
633  {
634  unsigned int i=0;
635  for ( ; i < fCol.size(); ++i)
636  if (fCol[i].Name() == cname) break;
637 
638  if (i >= fCol.size()) return 0.;
639 
640  return fCol[i].Tolerance();
641 
642  }
643 
644  //************************************************************
646  {
647  std::cout << std::endl;
648  int i = 0;
649  std::vector<int> len(0);
650  int tlen;
651  //int sumlen = 0; // unused
652 
653  for ( ; i<this->NCol(); ++i) {
654  tlen = this->GetCol(i)->Name().length();
655  if ((int)this->GetCol(i)->Type().length() > tlen)
656  tlen = this->GetCol(i)->Type().length();
657  len.push_back(tlen);
658  //sumlen += tlen; // unused
659  }
660 
661  int nsp = 0;
662  i = 0;
663  int j = 0;
664  while (i < this->NCol()) {
665  for ( ; i<this->NCol() && nsp<78; ++i)
666  nsp += len[i] + 1;
667 
668  for (int k=0; k<nsp; ++k) {
669  std::cout << "_" ;
670  }
671  std::cout << std::endl;
672 
673  int j_save = j;
674  for (; j<i; ++j)
675  std::cout << "|" << std::setw(len[j]) << std::left << this->GetCol(j)->Name();
676  std::cout << "|" << std::endl;
677 
678  for (int k=0; k<nsp; ++k) {
679  std::cout << "-" ;
680  }
681  std::cout << std::endl;
682 
683  j = j_save;
684  for ( ; j<i; ++j)
685  std::cout << "|" << std::setw(len[j]) << std::left << this->GetCol(j)->Type();
686  std::cout << "|" << std::endl;
687 
688  for (int k=0; k<nsp; ++k) {
689  std::cout << "-" ;
690  }
691  std::cout << std::endl;
692 
693  nsp = 0;
694  }
695 
696  }
697 
698  //************************************************************
700  {
701  char* tmpStr;
702  char hname[256];
703 
704  if (!fIgnoreEnvVar) {
705  // now check environment variables, which will override any previous settings
706  if (ntry == 0) {
707  tmpStr = getenv("DBIHOST");
708  if (tmpStr)
709  fDBHost = tmpStr;
710  }
711  else {
712  sprintf(hname,"DBIHOST%d",ntry);
713  tmpStr = getenv(hname);
714  if (tmpStr) {
715  std::cerr << "Switching to " << tmpStr << std::endl;
716  fDBHost = tmpStr;
717  }
718  else
719  return false;
720  }
721 
722  tmpStr = getenv("DBINAME");
723  if (tmpStr)
724  fDBName = tmpStr;
725  tmpStr = getenv("DBIPORT");
726  if (tmpStr)
727  fDBPort = tmpStr;
728  tmpStr = getenv("DBIUSER");
729  if (tmpStr)
730  fUser = tmpStr;
731  }
732 
733  if (fUser == "") {
734  tmpStr = getenv("USER");
735  if (tmpStr) {
736  fUser = tmpStr;
737  std::cerr << "Table::GetConnectionInfo: DB User undefined. Setting to \""
738  << fUser << "\"" << std::endl;
739  }
740  else {
741  throw std::runtime_error("Table::GetConnectionInfo: DB USER undefined.");
742  }
743 
744  }
745  if (fDBHost == "") {
746  throw std::runtime_error("Table::GetConnectionInfo: DB HOST undefined.");
747  }
748  if (fDBName == "") {
749  throw std::runtime_error("Table::GetConnectionInfo: DB NAME undefined.");
750  }
751 
752  /* if (fDBPort == "")
753  if (fTable->hasDbPort())
754  fDBPort = fTable->getDbPort();
755  */
756  return true;
757  }
758  //************************************************************
759  bool Table::GetConnection(int ntry)
760  {
761  if (fIgnoreDB) return false;
762 
763  bool gotConnInfo = false;
764  try {
765  gotConnInfo = GetConnectionInfo(ntry);
766  }
767  catch (std::runtime_error& e) {
768  std::cerr << e.what() << std::endl;
769  return false;
770  }
771 
772  if (!gotConnInfo) return false;
773 
774  // now get the password file name for read-only access
775 
776  if (Util::RunningOnGrid()) {
777  char* tmpStr = getenv("DBIGRIDPWDFILE");
778  if(tmpStr){
779  if(!this->SetPasswordFile(tmpStr)){
780  return false;
781  }
782  }
783  }
784  else {
785  char* tmpStr = getenv("DBIPWDFILE");
786  if(tmpStr){
787  if(!this->SetPasswordFile(tmpStr)) {
788  return false;
789  }
790  }
791  }
792 
793  if (!fConnection) {
794  std::string cmd = "dbname = " + fDBName + " host = " + fDBHost + " user = " + fUser;
795  if (fDBPort != "")
796  cmd += " port = " + fDBPort;
797 
798  if (fPassword != "")
799  cmd += " password = " + fPassword;
800 
801  fConnection = PQconnectdb(cmd.c_str());
802 
803  int nTry=0;
804  int sleepTime = 2;
805  time_t t0 = time(NULL);
806  time_t t1 = t0;
807 
808  while (PQstatus(fConnection) != CONNECTION_OK &&
809  ((t1-t0) < fConnectionTimeout) ) {
810  std::cerr << "Connection to " << fDBHost << ":"
811  << fDBName << " failed: "
812  << PQerrorMessage(fConnection) << std::endl;
813 
814  CloseConnection();
815  sleepTime = 1 + ((double)random()/(double)RAND_MAX)*(1 << nTry++);
816  sleep(sleepTime);
817  t1 = time(NULL);
818  fConnection = PQconnectdb(cmd.c_str());
819  }
820  if (PQstatus(fConnection) != CONNECTION_OK) {
821  CloseConnection();
822  if (! GetConnection(ntry+1)) {
823  std::cerr << "Too many attempts to connect to the database, "
824  << ", giving up." << std::endl;
825  CloseConnection();
826  return false;
827  }
828  }
829  fHasConnection = true;
830  if (fVerbosity > 0)
831  std::cout << "Got new connection" << std::endl;
832  }
833 
834  return true;
835  }
836  //************************************************************
838  {
839  if (fConnection) {
840  PQfinish(fConnection);
841  if (fVerbosity > 0)
842  std::cout << "Closed connection" << std::endl;
843  }
844 
845  fConnection = 0;
846  fHasConnection = false;
847 
848  return true;
849  }
850 
851  //************************************************************
852  bool Table::SetRole(std::string role)
853  {
854  fRole = role;
855  return true;
856  }
857 
858  //************************************************************
859  bool Table::SetPasswordFile(const char* fname)
860  {
861  std::string fNameStr = "";
862 
863  if (fname == 0) {
864  char* tmpStr = getenv("DBIPWDFILE");
865  if (tmpStr)
866  fNameStr = tmpStr;
867  else {
868  std::cerr << "DBIPWDFILE env. variable is not set, disabling "
869  << "password-access to the dB." << std::endl;
870  fPassword = "";
871  return false;
872  }
873  }
874  else
875  fNameStr = fname;
876 
877  std::ifstream fin;
878  fin.open(fNameStr.c_str());
879  if (!fin.is_open() || !fin.good()) {
880  std::cerr << "Could not open password file " << fNameStr
881  << ". Disabling password-access to the dB." << std::endl;
882  return false;
883  }
884  else {
885  fin >> fPassword;
886  fin.close();
887  }
888 
889  return true;
890  }
891 
892  //************************************************************
894  {
895  if (fIgnoreDB) return false;
896 
897  if (fTestedExists) return fExistsInDB;
898 
899  std::string tname = this->Name();
900 
901  fTestedExists = true;
902 
903  bool hasConn = fHasConnection;
904  if (! fHasConnection) {
905  GetConnection();
906  hasConn = false;
907  }
908 
909  std::string cmd = "SELECT tablename FROM pg_tables WHERE schemaname=\'" +
910  fSchema + "\'";
911  // std::cout << tname << ": " << cmd << std::endl;
912  PGresult* res = PQexec(fConnection,cmd.c_str());
913 
914  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
915  if (fVerbosity > 0)
916  std::cerr << "Table::ExistsInDB command failed: "
917  << PQerrorMessage(fConnection) << std::endl;
918  PQclear(res);
919  fExistsInDB = false; // set this to false just in case
920  CloseConnection();
921  return false;
922  }
923 
924  bool retVal = false;
925  int nRow = PQntuples(res);
926 
927  int tc=0;
928  std::vector<std::string> tList;
929  tList.push_back(tname+std::string("_snapshot"));
930  tList.push_back(tname+std::string("_snapshot_data"));
931  tList.push_back(tname+std::string("_tag"));
932  tList.push_back(tname+std::string("_tag_snapshot"));
933  tList.push_back(tname+std::string("_update"));
934 
935  for (int i=0; i<nRow; ++i) {
936  // std::cout << string(PQgetvalue(res,i,0)) << std::endl;
937  std::string tStr = std::string(PQgetvalue(res,i,0));
938 
939  if (fTableType != kConditionsTable) {
940  if (tStr == tname) {
941  retVal = true;
942  break;
943  }
944  }
945  else {
946  if (std::string(PQgetvalue(res,i,0)) == tList[0] ||
947  std::string(PQgetvalue(res,i,0)) == tList[1] ||
948  std::string(PQgetvalue(res,i,0)) == tList[2] ||
949  std::string(PQgetvalue(res,i,0)) == tList[3] ||
950  std::string(PQgetvalue(res,i,0)) == tList[4] )
951  ++tc;
952 
953  if (tc == 5) {
954  retVal = true;
955  break;
956  }
957  }
958  }
959 
960  PQclear(res);
961 
962  if (!hasConn) CloseConnection();
963 
964  fExistsInDB = true;
965  return retVal;
966 
967  }
968 
969  //************************************************************
970  bool Table::GetCurrSeqVal(std::string col, long& iseq)
971  {
972  if (fIgnoreDB) return false;
973 
974  bool hasConn = fHasConnection;
975  if (! fHasConnection) {
976  GetConnection();
977  hasConn = false;
978  }
979 
980  // now get current sequence value:
981 
982  std::string cmd = "SELECT last_value FROM ";
983  cmd += Schema() + "." + Name();
984  cmd += "_" + col + "_seq";
985 
986  if (fVerbosity > 0)
987  std::cerr << "Table::GetCurrSeqVal: Executing PGSQL command: \n\t"
988  << cmd << std::endl;
989 
990  PGresult* res = PQexec(fConnection, cmd.c_str());
991  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
992  if (fVerbosity > 0)
993  std::cerr << "SELECT failed: " << PQerrorMessage(fConnection) << std::endl;
994  PQclear(res);
995  return false;
996  }
997 
998  // check that the number of columns is consistent
999  if (PQnfields(res) != 1) {
1000  PQclear(res);
1001  return false;
1002  }
1003 
1004  // now cache rows
1005  int nRow = PQntuples(res);
1006 
1007  if (nRow != 1) {
1008  PQclear(res);
1009  return false;
1010  }
1011 
1012  if (! PQgetisnull(res,0,0)) {
1013  std::string vstr = PQgetvalue(res,0,0);
1014  try {
1015  iseq = boost::lexical_cast<long>(vstr);
1016  }
1017  catch (boost::bad_lexical_cast &) {
1018  PQclear(res);
1019  return false;
1020  }
1021  }
1022 
1023  PQclear(res);
1024 
1025  if (!hasConn) CloseConnection();
1026 
1027  return true;
1028  }
1029  //************************************************************
1030  bool Table::ExecuteSQL(std::string cmd, PGresult*& res)
1031  {
1032  if (fIgnoreDB) return false;
1033 
1034  bool hasConn = fHasConnection;
1035  if (! fHasConnection) {
1036  GetConnection();
1037  hasConn = false;
1038  }
1039 
1040  if (!fConnection) {
1041  std::cerr << "Table::ExecuteSQL: No connection to the database!" << std::endl;
1042  return false;
1043  }
1044 
1045  if (cmd == "") return false;
1046 
1047  if (fVerbosity)
1048  std::cerr << "Executing SQL query: " << cmd << std::endl;
1049 
1050  boost::posix_time::ptime ctt1;
1051  boost::posix_time::ptime ctt2;
1052 
1053  if (fTimeQueries) {
1054  ctt1 = boost::posix_time::microsec_clock::local_time();
1055  }
1056 
1057  res = PQexec(fConnection,cmd.c_str());
1058  if (fTimeQueries) {
1059  ctt2 = boost::posix_time::microsec_clock::local_time();
1060  boost::posix_time::time_duration tdiff = ctt2 - ctt1;
1061  std::cerr << "Table::ExecuteSQL(" << cmd << "): query took "
1062  << tdiff.total_milliseconds() << " ms" << std::endl;
1063  }
1064 
1065  // close connection to the dB if necessary
1066  if (! hasConn) CloseConnection();
1067 
1068  return (res != 0);
1069  }
1070 
1071  //************************************************************
1073  {
1074  if (fIgnoreDB) return false;
1075 
1076  if (fSchema == "undef") {
1077  std::cerr << "Table::LoadFromDB: Detector not set! Table::SetDetector()"
1078  << " must be called first!" << std::endl;
1079  return false;
1080  }
1081 
1082  if (!fValidityChanged) return true;
1083 
1084  // make a connection to the dB if there isn't one already
1085  bool hasConn = fHasConnection;
1086  if (! fHasConnection) {
1087  GetConnection();
1088  hasConn = false;
1089  }
1090 
1091  if (!fConnection) {
1092  std::cerr << "Table::LoadFromDB: No connection to the database!" << std::endl;
1093  return false;
1094  }
1095 
1096  if (!ExistsInDB()) {
1097  std::cerr << "Table::LoadFromDB: Table \"" << Name()
1098  << "\" not found in database!" << std::endl;
1099  CloseConnection();
1100  return false;
1101  }
1102 
1103  std::string cmd;
1104  cmd.clear();
1105  PGresult* res;
1106 
1107  std::ostringstream outs;
1108  outs << "BEGIN";
1109  res = PQexec(fConnection, outs.str().c_str());
1110  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
1111  std::cerr << "BEGIN command failed: " << PQerrorMessage(fConnection) << std::endl;
1112  PQclear(res);
1113  CloseConnection();
1114  return false;
1115  }
1116 
1117  PQclear(res);
1118 
1119  outs.str("");
1120  outs << "DECLARE myportal CURSOR FOR SELECT ";
1121  if (!fDistinctCol.empty()) {
1122  outs << "DISTINCT ON (";
1123  if (! fDistinctCol.empty()) {
1124  for (unsigned int i=0; i<fDistinctCol.size(); ++i) {
1125  outs << fDistinctCol[i]->Name();
1126  if (i<(fDistinctCol.size()-1)) outs << ", ";
1127  }
1128  }
1129  outs << ") ";
1130  }
1131 
1132  outs << "* from ";
1133  outs << Schema() << "." << Name();
1134 
1135  if (! fValidityStart.empty() || fValiditySQL != "" ) {
1136  outs << " WHERE " << fValiditySQL;
1137  if (fValiditySQL != "" && !fValidityStart.empty()) outs << " and ";
1138 
1139  for (unsigned int i=0; i<fValidityStart.size(); ++i) {
1140  bool isEqualTo = (fValidityStart[i].Value() == fValidityEnd[i].Value());
1141  bool needsQuotes=false;
1142  if (fValidityStart[i].Type() == "string" ||
1143  fValidityStart[i].Type() == "text" ||
1144  fValidityStart[i].Type() == "timestamp" ||
1145  fValidityStart[i].Type() == "date") needsQuotes=true;
1146 
1147  outs << fValidityStart[i].Name();
1148  if (!isEqualTo)
1149  outs << ">=";
1150  else
1151  outs << "=";
1152 
1153  if (needsQuotes) outs << "'";
1154  outs << fValidityStart[i].Value();
1155  if (needsQuotes) outs << "'";
1156 
1157  if (!isEqualTo) {
1158  outs << " and ";
1159  outs << fValidityEnd[i].Name() + "<=";
1160  if (needsQuotes) outs << "'";
1161  outs << fValidityEnd[i].Value();
1162  if (needsQuotes) outs << "'";
1163  }
1164 
1165  if (i < (fValidityStart.size()-1)) outs << " and ";
1166  }
1167  }
1168 
1169  if (!fDistinctCol.empty() || !fOrderCol.empty()) {
1170  outs << " ORDER BY ";
1171 
1172  if (!fDistinctCol.empty()) {
1173  for (unsigned int i=0; i<fDistinctCol.size(); ++i) {
1174  outs << fDistinctCol[i]->Name();
1175  if (i<(fDistinctCol.size()-1)) outs << ", ";
1176  }
1177  }
1178 
1179  if (!fOrderCol.empty()) {
1180  for (unsigned int i=0; i<fOrderCol.size(); ++i) {
1181  outs << fOrderCol[i]->Name();
1182  if (i<(fOrderCol.size()-1)) outs << ", ";
1183  }
1184  }
1185 
1186  if (fDescOrder)
1187  outs << " DESC";
1188  else
1189  outs << " ASC";
1190  }
1191 
1192  if (fSelectLimit>0) {
1193  outs << " LIMIT " << boost::lexical_cast<std::string>(fSelectLimit);
1194  }
1195 
1196  if (fSelectOffset>0) {
1197  outs << " OFFSET " << boost::lexical_cast<std::string>(fSelectOffset);
1198  }
1199 
1200  if (fVerbosity > 0)
1201  std::cerr << "Table::LoadFromDB: Executing PGSQL command: \n\t" << outs.str() << std::endl;
1202  res = PQexec(fConnection,outs.str().c_str());
1203 
1204  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
1205  std::cerr << "DECLARE CURSOR failed: " << PQerrorMessage(fConnection) << std::endl;
1206  PQclear(res);
1207  CloseConnection();
1208  return false;
1209  }
1210  PQclear(res);
1211 
1212 
1213  boost::posix_time::ptime ctt1;
1214  boost::posix_time::ptime ctt2;
1215 
1216  if (fTimeQueries) {
1217  ctt1 = boost::posix_time::microsec_clock::local_time();
1218  }
1219 
1220  res = PQexec(fConnection, "FETCH ALL in myportal");
1221  if (fTimeQueries) {
1222  ctt2 = boost::posix_time::microsec_clock::local_time();
1223  boost::posix_time::time_duration tdiff = ctt2 - ctt1;
1224  std::cerr << "Table::LoadFromDB(" << Name() << "): query took "
1225  << tdiff.total_milliseconds() << " ms" << std::endl;
1226  }
1227 
1228  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
1229  std::cerr << "FETCH ALL failed: %" << PQerrorMessage(fConnection) << std::endl;
1230  PQclear(res);
1231  CloseConnection();
1232  return false;
1233  }
1234 
1235  // now cache rows
1236  int nRow = PQntuples(res);
1237  if (fVerbosity>0)
1238  std::cerr << "Table::LoadFromDB(" << Name() << "): got " << nRow
1239  << " rows of data." << std::endl;
1240 
1241  if (fTimeParsing)
1242  ctt1 = boost::posix_time::microsec_clock::local_time();
1243 
1244  if (nRow > 0) {
1245  std::vector<int> colMap(fCol.size());
1246 
1247  for (unsigned int i=0; i<fCol.size(); ++i) {
1248  colMap[i] = PQfnumber(res,fCol[i].Name().c_str());
1249  }
1250 
1251  int k;
1252 
1253  unsigned int ioff = fRow.size();
1254  AddEmptyRows(nRow);
1255 
1256  for (int i=0; i < nRow; i++) {
1257  for (unsigned int j=0; j < fCol.size(); j++) {
1258  k = colMap[j];
1259  if (k >= 0) {
1260  if (! PQgetisnull(res,i,k)) {
1261  std::string vstr = PQgetvalue(res,i,k);
1262  fRow[ioff+i].Col(j).FastSet(vstr);
1263  }
1264  // else
1265  // fRow[ioff+i].Col(j).FastSet("");
1266  }
1267  }
1268  fRow[ioff+i].SetInDB();
1269  }
1270  }
1271 
1272  if (fTimeParsing) {
1273  ctt2 = boost::posix_time::microsec_clock::local_time();
1274  boost::posix_time::time_duration tdiff = ctt2 - ctt1;
1275  std::cerr << "Table::LoadFromDB(" << Name() << "): parsing took "
1276  << tdiff.total_milliseconds() << " ms" << std::endl;
1277  }
1278 
1279  PQclear(res);
1280 
1281  /* close the portal ... we don't bother to check for errors ... */
1282  res = PQexec(fConnection, "CLOSE myportal");
1283  PQclear(res);
1284 
1285  /* end the transaction */
1286  res = PQexec(fConnection, "END");
1287  PQclear(res);
1288 
1289  // close connection to the dB if necessary
1290  if (! hasConn) CloseConnection();
1291 
1292  fValidityChanged = false;
1293 
1294  return true;
1295  }
1296 
1297  //************************************************************
1298  bool Table::LoadFromCSV(std::string fname)
1299  {
1300  std::cout << "Reading " << fname << std::endl;
1301 
1302  std::ifstream fin;
1303  fin.open(fname.c_str());
1304  if (!fin.is_open()) {
1305  std::cerr << "Could not open " << fname << std::endl;
1306  return false;
1307  }
1308  if (!fin.good()) {
1309  std::cerr << "Stream not good " << fname << std::endl;
1310  return false;
1311  }
1312 
1313  std::string s;
1314 
1315  char buff[256];
1316  std::string value;
1317 
1318  std::vector<int> colMap(fCol.size());
1319  for (unsigned int i=0; i<fCol.size(); ++i) {
1320  colMap[i] = int(i);
1321  }
1322 
1323  bool hasColNames = true;
1324  bool hasTols = true;
1325 
1326  int chanIdx=-1;
1327  int tvIdx=-1;
1328  int tvEndIdx=-1;
1329 
1330  // check first line to see if it is column names. Should begin with a '#'
1331  std::getline(fin,s);
1332  if (s[0] == '#' || fTableType == kConditionsTable) {
1333  unsigned int ic=1;
1334  if (fTableType == kConditionsTable && s[0] != '#') ic=0;
1335  int k;
1336 
1337  int joff=0;
1338  for (int j=0; ic<s.length(); ++j) {
1339  k=0;
1340 
1341  while (s[ic++] != ',' && ic<s.length())
1342  buff[k++] = s[ic-1];
1343 
1344  if (ic==s.length()) buff[k++] = s[s.length()-1];
1345  buff[k] = '\0';
1346  value = buff;
1347 
1348  boost::algorithm::trim(value);
1349  if (value == "channel") { chanIdx=j; ++joff;}
1350  else if (value == "tv") { tvIdx=j; ++joff;}
1351  else if (value == "tvend") { tvEndIdx=j; ++joff;}
1352  else {
1353  for (unsigned int jc=0; jc<fCol.size(); ++jc)
1354  if (fCol[jc].Name() == value) {
1355  colMap[j-joff] = jc;
1356  break;
1357  }
1358  }
1359  }
1360 
1361  }
1362  else {
1363  hasColNames = false;
1364  fin.clear();
1365  fin.seekg(0);
1366  }
1367 
1368  // now check for tolerances
1369  std::getline(fin,s);
1370  if (fTableType == kConditionsTable && s.substr(0,10) == "tolerance,") {
1371  unsigned int ic=11;
1372  int k;
1373 
1374  int joff=0;
1375  for (int j=0; ic<s.length(); ++j) {
1376  k=0;
1377  while (s[ic] != ',' && ic<s.length())
1378  buff[k++] = s[ic++];
1379  // if (ic==s.length()) buff[k++] = s[s.length()-1];
1380  ++ic;
1381  if (ic < s.length())
1382  buff[k] = '\0';
1383  else {
1384  buff[k] = '\0';
1385  }
1386  value = buff;
1387  if (value.length() > 0) {
1388  if (j==chanIdx || j==tvIdx || j==tvEndIdx)
1389  ++joff;
1390  else
1391  fCol[colMap[j-joff]].SetTolerance(atof(buff));
1392  }
1393  }
1394  }
1395  else {
1396  hasTols = false;
1397  fin.clear();
1398  fin.seekg(0);
1399  }
1400 
1401  Row* r = NewRow();
1402 
1403  int nRow=0;
1404  unsigned int ioff=fRow.size();
1405  unsigned int irow=0;
1406  fin.clear();
1407  fin.seekg(0);
1408  while (std::getline(fin,s))
1409  ++nRow;
1410  fin.clear();
1411  fin.seekg(0);
1412  if (hasColNames) {
1413  --nRow;
1414  std::getline(fin,s);
1415  }
1416  if (hasTols) {
1417  --nRow;
1418  std::getline(fin,s);
1419  }
1420 
1421  if (nRow<=0) {
1422  std::cout << "Table::LoadFromCSV() found no rows in "
1423  << fname << std::endl;
1424  return false;
1425  }
1426 
1427  AddEmptyRows(nRow);
1428  std::cout << "Added " << nRow << " empty rows" << std::endl;
1429 
1430  for (int jrow=0; jrow<nRow; ++jrow) {
1431  std::getline(fin,s);
1432 
1433  unsigned int ic=0;
1434  int k;
1435  bool hasX;
1436  int joff=0;
1437  for (int j=0; ic<s.length(); ++j) {
1438  k=0;
1439  hasX=false;
1440  while (s[ic++] != ',' && ic<s.length()) {
1441  buff[k++] = s[ic-1];
1442  if (buff[k-1] == 'x') hasX=true;
1443  }
1444  if (ic==s.length()) buff[k++] = s[s.length()-1];
1445  buff[k] = '\0';
1446  value = buff;
1447 
1448  if (j==chanIdx) {
1449  fRow[ioff+irow].SetChannel(strtoull(buff,NULL,10));
1450  ++joff;
1451  }
1452  else if (j==tvIdx) {
1453  fRow[ioff+irow].SetVldTime(strtoull(buff,NULL,10));
1454  ++joff;
1455  }
1456  else if (j==tvEndIdx) {
1457  fRow[ioff+irow].SetVldTimeEnd(strtoull(buff,NULL,10));
1458  ++joff;
1459  }
1460  else {
1461  if (hasX) {
1462  if (fCol[j-joff].Type() == "bigint" ||
1463  fCol[j-joff].Type() == "long") {
1464  try {
1465  std::istringstream iss(value);
1466  uint64_t ulongValue;
1467  iss >> std::hex >> ulongValue;
1468  int64_t longValue = (int64_t) ulongValue;
1469  value = boost::lexical_cast<std::string>(longValue);
1470  }
1471  catch (...) {
1472  // simply let "value" remain unchanged
1473  }
1474  }
1475  else if (fCol[j-joff].Type() == "int") {
1476  try {
1477  std::istringstream iss(value);
1478  uint32_t uintValue;
1479  iss >> std::hex >> uintValue;
1480  int32_t intValue = (int32_t) uintValue;
1481  value = boost::lexical_cast<std::string>(intValue);
1482  }
1483  catch (...) {
1484  // simply let "value" remain unchanged
1485  }
1486  }
1487  else if (fCol[j-joff].Type() == "short") {
1488  try {
1489  std::istringstream iss(value);
1490  uint16_t ushortValue;
1491  iss >> std::hex >> ushortValue;
1492  int16_t shortValue = (int16_t) ushortValue;
1493  value = boost::lexical_cast<std::string>(shortValue);
1494  }
1495  catch (...) {
1496  // simply let "value" remain unchanged
1497  }
1498  }
1499  } // if (hasX)
1500  if (fCol[j-joff].Type() == "text") {
1501  boost::algorithm::trim(value);
1502  if ((value[0] == '"' && value[value.length()-1] == '"') ||
1503  (value[0] == '\'' && value[value.length()-1] == '\''))
1504  value = value.substr(1,value.length()-2);
1505  }
1506  fRow[ioff+irow].Col(colMap[j-joff]).FastSet(value);
1507  } // else not a validity channel or time
1508  }
1509 
1510  fRow[ioff+irow].SetInDB();
1511  ++irow;
1512  }
1513  delete r;
1514 
1515  fin.close();
1516 
1517  return true;
1518  }
1519 
1520  //************************************************************
1521 
1523  {
1524  int i = strlen(line);
1525  while (*line < '0' || *line > '9') line++;
1526  line[i-3] = '\0';
1527  i = atoi(line);
1528  return i;
1529  }
1530 
1531 
1532  //************************************************************
1533 
1534  void Table::PrintVMUsed(){ //Note: this value is in MB!
1535  FILE* file = fopen("/proc/self/status", "r");
1536  int result = -1;
1537  char line[128];
1538 
1539 
1540  while (fgets(line, 128, file) != NULL){
1541  if (strncmp(line, "VmSize:", 7) == 0){
1542  result = this->ParseSelfStatusLine(line);
1543  break;
1544  }
1545  }
1546  fclose(file);
1547  std::cerr << Schema() << "." << Name() << ": this process using "
1548  << result/1024 << " MB of VirtualMemory" << std::endl;
1549  }
1550 
1551  //************************************************************
1552 
1553  void Table::PrintPMUsed(){ //Note: this value is in MB!
1554  FILE* file = fopen("/proc/self/status", "r");
1555  int result = -1;
1556  char line[128];
1557 
1558 
1559  while (fgets(line, 128, file) != NULL){
1560  if (strncmp(line, "VmRSS:", 6) == 0){
1561  result = this->ParseSelfStatusLine(line);
1562  break;
1563  }
1564  }
1565  fclose(file);
1566  std::cerr << Schema() << "." << Name() << ": this process using "
1567  << result/1024 << " MB of PhysicalMemory" << std::endl;
1568  }
1569 
1570  //************************************************************
1571 
1572  bool Table::GetDataFromWebService(Dataset& ds, std::string myss)
1573  {
1574  Tuple tu;
1575  char ss[1024];
1576  char ss2[1024];
1577  int wda_err, err;
1578  std::vector<int> colMap(fCol.size());
1579  std::vector<bool> isString(fCol.size());
1580  std::vector<bool> isKnownField(fCol.size());
1581 
1582  const char* uagent = NULL;
1583 
1584  if(fVerbosity > 0)
1585  std::cout << "DBWeb query: " << myss << std::endl;
1586 
1587  boost::posix_time::ptime ctt1;
1588  boost::posix_time::ptime ctt2;
1589 
1590  if (fTimeQueries) {
1591  ctt1 = boost::posix_time::microsec_clock::local_time();
1592  }
1593 
1594  ds = getDataWithTimeout(myss.c_str(), uagent,
1595  fConnectionTimeout, &wda_err);
1596 
1597  if (fTimeQueries) {
1598  ctt2 = boost::posix_time::microsec_clock::local_time();
1599  boost::posix_time::time_duration tdiff = ctt2 - ctt1;
1600  std::cerr << "Table::Load(" << Name() << "): query took "
1601  << tdiff.total_milliseconds() << " ms" << std::endl;
1602  }
1603 
1604  int httpStatus = getHTTPstatus(ds);
1605 
1606  if (httpStatus == 504) {
1607  int nTry=0;
1608  int sleepTime = 2;
1609  time_t t0 = time(NULL);
1610  time_t t1 = t0;
1611 
1612  while (httpStatus == 504 && ((t1-t0) < fConnectionTimeout) ) {
1613  sleepTime = 1 + ((double)random()/(double)RAND_MAX)*(1 << nTry++);
1614 
1615  std::cerr << "Table::Load() for " << Name()
1616  << " failed with error 504, retrying in " << sleepTime
1617  << " seconds." << std::endl;
1618 
1619  sleep(sleepTime);
1620  t1 = time(NULL);
1621  if (fTimeQueries)
1622  ctt1 = boost::posix_time::microsec_clock::local_time();
1623 
1624  ds = getDataWithTimeout(myss.c_str(), uagent,
1625  fConnectionTimeout, &wda_err);
1626 
1627  if (fTimeQueries) {
1628  ctt2 = boost::posix_time::microsec_clock::local_time();
1629  boost::posix_time::time_duration tdiff = ctt2 - ctt1;
1630  std::cerr << "Table::Load(" << Name() << "): query took "
1631  << tdiff.total_milliseconds() << " ms" << std::endl;
1632  }
1633  httpStatus = getHTTPstatus(ds);
1634  }
1635  }
1636 
1637  if (httpStatus != 200) {
1638  std::cerr << "Table::Load: Web Service returned HTTP status "
1639  << httpStatus << ": " << getHTTPmessage(ds) << std::endl;
1640  return false;
1641  }
1642 
1643  if (fTimeParsing)
1644  ctt1 = boost::posix_time::microsec_clock::local_time();
1645 
1646  int ntup = getNtuples(ds);
1647 
1648  // Getting no rows back can be legitimate
1649  if(ntup == 0){
1650  if(fVerbosity > 0)
1651  std::cout << "Got zero rows from database. Is that expected?" << std::endl;
1652 
1653  fRow.clear();
1654 
1655  return true;
1656  }
1657 
1658  if(fVerbosity > 0)
1659  std::cout << "Got " << ntup-1 << " rows from database" << std::endl;
1660 
1661  int ioff=fRow.size();
1662 
1663  AddEmptyRows(ntup);
1664 
1665  tu = getFirstTuple(ds);
1666  if (tu == NULL) {
1667  std::cerr << "Table::Load(" << Name() << ") has NULL first tuple!"
1668  << std::endl;
1669  return false;
1670  }
1671  int ncol2 = getNfields(tu);
1672  std::string chanStr = "channel";
1673  std::string tvStr = "tv";
1674  std::string tvEndStr = "tvend";
1675  int chanIdx=-1;
1676  int tvIdx=-1;
1677  int tvEndIdx=-1;
1678  for (int i=0; i<ncol2; ++i) {
1679  getStringValue(tu,i,ss,sizeof(ss),&err);
1680  if (chanStr == ss) { chanIdx=i; continue;}
1681  if (tvStr == ss) { tvIdx=i; continue;}
1682  if (tvEndStr == ss) { tvEndIdx=i; continue;}
1683 
1684  bool foundMatch=false;
1685  for (unsigned int icol=0; icol<fCol.size(); ++icol) {
1686  if (fCol[icol].Name() == ss) {
1687  colMap[i] = icol;
1688  isString[i] = false;
1689  if (fCol[icol].Type() == "string" || fCol[icol].Type() == "text")
1690  isString[i] = true;
1691  foundMatch=true;
1692  break;
1693  }
1694  }
1695  if (!foundMatch) // this means this field was unexpected, so
1696  // ignore it downstream
1697  isKnownField[i] = false;
1698  else
1699  isKnownField[i] = true;
1700  }
1701 
1702  releaseTuple(tu);
1703  tu = getNextTuple(ds);
1704  int irow=0;
1705  while (tu != NULL) {
1706  for (int i=0; i<ncol2; ++i) {
1707  getStringValue(tu,i,ss,sizeof(ss),&err);
1708  if (i == chanIdx) {
1709  uint64_t chan = strtoull(ss,NULL,10);
1710  fRow[ioff+irow].SetChannel(chan);
1711  continue;
1712  }
1713  else if (i == tvIdx) {
1714  double t1 = strtod(ss,NULL);
1715  fRow[ioff+irow].SetVldTime(t1);
1716  }
1717  else if (i == tvEndIdx) {
1718  double t1 = strtod(ss,NULL);
1719  fRow[ioff+irow].SetVldTimeEnd(t1);
1720  }
1721  else {
1722  if (isKnownField[i]) {
1723  if (isString[i] && (ss[0]=='\'' || ss[0]=='\"')) { // remove quotes
1724  int k = strlen(ss);
1725  strncpy(ss2,&ss[1],k-2);
1726  ss2[k-2] = '\0';
1727  fRow[ioff+irow].Col(colMap[i]).FastSet(ss2);
1728  }
1729  else
1730  fRow[ioff+irow].Col(colMap[i]).FastSet(ss);
1731  }
1732  }
1733  }
1734  releaseTuple(tu);
1735  tu = getNextTuple(ds);
1736  ++irow;
1737  };
1738 
1739  if (fTimeParsing) {
1740  ctt2 = boost::posix_time::microsec_clock::local_time();
1741  boost::posix_time::time_duration tdiff = ctt2 - ctt1;
1742  std::cerr << "Table::Load(" << Name() << "): parsing took "
1743  << tdiff.total_milliseconds() << " ms" << std::endl;
1744  }
1745 
1746  // Make sure that the rows list is no longer than what we actually
1747  // filled. This happens because ntup above included the header row that
1748  // gives the column names.
1749  while(int(fRow.size()) > irow) fRow.pop_back();
1750 
1751  releaseDataset(ds);
1752 
1753  return true;
1754  }
1755 
1756  //************************************************************
1757 
1759  {
1760  if (fQEURL == "") {
1761  std::cerr << "Table::LoadNonConditionsTable: Query Engine URL is not set! using Table::LoadFromDB() instead." << std::endl;
1762  return LoadFromDB();
1763  }
1764 
1765  if (fValiditySQL != "") {
1766  std::cerr << "Table::LoadNonConditionsTable: pure SQL statements are not supported, using Table::LoadFromDB() instead." << std::endl;
1767  return LoadFromDB();
1768  }
1769 
1770  std::stringstream myss;
1771 
1772  myss << fQEURL << "query?t=" << Schema() << "." << Name() << "&";
1773 
1774  int ncol = this->NCol();
1775 
1776  myss << "&c=";
1777  int nc=0;
1778  for (int i=0; i<ncol; ++i) {
1779  std::string cname = this->GetCol(i)->Name();
1780  bool skipCol = false;
1781  for (size_t j=0; j<fExcludeCol.size(); ++j) {
1782  if (fExcludeCol[j] == cname) {
1783  skipCol = true;
1784  break;
1785  }
1786  }
1787  if (skipCol) continue;
1788 
1789  if (nc>0)
1790  myss << ",";
1791  myss << cname;
1792  ++nc;
1793  }
1794 
1795  if (! fValidityStart.empty()) {
1796  for (unsigned int i=0; i<fValidityStart.size(); ++i) {
1797  if (fValidityStart[i].Type() == "string" ||
1798  fValidityStart[i].Type() == "text" ||
1799  fValidityStart[i].Type() == "timestamp" ||
1800  fValidityStart[i].Type() == "date") {
1801  std::cerr << "Table::LoadNonConditionsTable: validity strings are not supported, using Table::LoadFromDB() instead." << std::endl;
1802  return LoadFromDB();
1803  }
1804 
1805  myss << "&w=";
1806  bool isEqualTo = (fValidityStart[i].Value() == fValidityEnd[i].Value());
1807  if (isEqualTo) {
1808  myss << fValidityStart[i].Name() << ":"
1809  << fValidityStart[i].Value();
1810  }
1811  else {
1812  myss << fValidityStart[i].Name() << ":ge:"
1813  << fValidityStart[i].Value() << "&w="
1814  << fValidityEnd[i].Name() << ":le:"
1815  << fValidityEnd[i].Value();
1816  }
1817 
1818  }
1819  }
1820 
1821  if (!fOrderCol.empty()) {
1822  myss << "&o=";
1823  if (fDescOrder)
1824  myss << "-";
1825  for (unsigned int i=0; i<fOrderCol.size(); ++i) {
1826  myss << fOrderCol[i]->Name();
1827  if (i<(fOrderCol.size()-1)) myss << ", ";
1828  }
1829 
1830  }
1831 
1832  if (fSelectLimit>0)
1833  myss << "&l=" << fSelectLimit;
1834 
1835  if (fDisableCache) {
1836  if (fFlushCache)
1837  myss << "&x=clear";
1838  else
1839  myss << "&x=no";
1840  }
1841 
1842  Dataset ds;
1843 
1844  return GetDataFromWebService(ds,myss.str());
1845 
1846  }
1847 
1848  //************************************************************
1849 
1851  {
1852  if (fMinTSVld == 0 || fMaxTSVld == 0) {
1853  std::cerr << "Table::LoadUnstructuredConditionsTable: No validity time is set!" << std::endl;
1854  return false;
1855  }
1856 
1857  if (fUConDBURL == "") {
1858  std::cerr << "Table::LoadConditionsTable: Web Service URL is not set!" << std::endl;
1859  return false;
1860  }
1861 
1862  if (!Util::RunningOnGrid()) {
1863  std::string interactiveURL = getenv("DBIUCONDBURLINT");
1864  if (!interactiveURL.empty())
1865  fUConDBURL = interactiveURL;
1866  }
1867 
1868  // int ncol = this->NCol();
1869 
1870  std::stringstream myss;
1871 
1872  myss << fUConDBURL << "get?folder=" << Folder() << "." << Name() << "&";
1873 
1874 
1875  return false;
1876  }
1877 
1878  //************************************************************
1879 
1881  {
1882  if (fDataTypeMask == 0) {
1883  std::cerr << "Table::LoadConditionsTable: Data type mask is not set!" << std::endl;
1884  return false;
1885  }
1886 
1887  if (fMinTSVld == 0 || fMaxTSVld == 0) {
1888  std::cerr << "Table::LoadConditionsTable: No validity time is set!" << std::endl;
1889  return false;
1890  }
1891 
1892  if (fWSURL == "") {
1893  std::cerr << "Table::LoadConditionsTable: Web Service URL is not set!" << std::endl;
1894  return false;
1895  }
1896 
1897  if (!Util::RunningOnGrid()) {
1898  std::string interactiveURL = getenv("DBIWSURLINT");
1899  if (!interactiveURL.empty())
1900  fWSURL = interactiveURL;
1901  }
1902 
1903  int ncol = this->NCol();
1904 
1905  std::stringstream myss;
1906 
1907  myss << fWSURL << "get?table=" << Schema() << "." << Name() << "&";
1908 
1909  if (fDataTypeMask > kNone) {
1910  myss << "type=";
1911 
1912  if ((fDataTypeMask & kMCOnly)) myss << "mc";
1913  if ((fDataTypeMask & kDataOnly)) myss << "data";
1914 
1915  myss << "&";
1916  }
1917 
1918  if (fMaxChannel > fMinChannel) {
1919  myss << "cr=" << fMinChannel << "-" << fMaxChannel << "&";
1920  }
1921 
1922  if (fValiditySQL != "") {
1923  myss << "where=" << fValiditySQL << "&";
1924  }
1925 
1926  if (fTag != "") myss << "tag=" << fTag << "&";
1927 
1928  // char ts[256];
1929 
1930  if (fMinTSVld == fMaxTSVld) {
1931  // sprintf(ts,"t=%" PRIu64,fMinTSVld);
1932  // myss << ts; //"t=" << fMinTSVld;
1933  myss << "t=" << std::setprecision(12) << fMinTSVld;
1934  }
1935  else {
1936  // sprintf(ts,"t0=%" PRIu64 "&t1=" PRIu64,fMinTSVld,fMaxTSVld);
1937  // myss << ts; //"t0=" << fMinTSVld << "&t1=" << fMaxTSVld;
1938  myss << "t0=" << std::setprecision(12) << fMinTSVld << "&t1=" << std::setprecision(12) << fMaxTSVld;
1939  }
1940 
1941  if (fHasRecordTime) myss << "&rtime=" << fRecordTime;
1942 
1943  if (fFlushCache) myss << "&cache=flush";
1944  if (fDisableCache) myss << "&cache=no";
1945 
1946  myss << "&columns=";
1947  bool firstCol = true;
1948  for (int i=0; i<ncol; ++i) {
1949  std::string cName = this->GetCol(i)->Name();
1950  bool skipCol = false;
1951  for (size_t j=0; j<fExcludeCol.size(); ++j)
1952  if (fExcludeCol[j] == cName) {
1953  skipCol = true;
1954  break;
1955  }
1956  if (skipCol) continue;
1957  if(!firstCol) myss << ",";
1958  myss << this->GetCol(i)->Name();
1959  firstCol = false;
1960  }
1961 
1962  // std::cout << myss.str() << std::endl;
1963  Dataset ds;
1964 
1965  return GetDataFromWebService(ds,myss.str());
1966 
1967  }
1968 
1969  //************************************************************
1970 
1972  {
1973  if (Util::RunningOnGrid()) {
1974  fConnectionTimeout = 1800;
1975  }
1977  return LoadConditionsTable();
1980  else
1981  return LoadNonConditionsTable();
1982  }
1983 
1984  //************************************************************
1985  // Create a look-up table of time-ordered validity rows based on
1986  // channel number
1987  //************************************************************
1988 
1990  {
1991  nutools::dbi::Row* row;
1992  uint64_t chan;
1993  double tv;
1994  fChanRowMap.clear();
1995 
1996  for (int i=0; i<this->NRow(); ++i) {
1997  row = this->GetRow(i);
1998  chan = row->Channel();
1999  tv = row->VldTime();
2000  if (fChanRowMap[chan].empty())
2001  fChanRowMap[chan].push_back(row);
2002  else {
2003  bool wasInserted=false;
2004  for (unsigned int j=0; j<fChanRowMap[chan].size(); ++j) {
2005  if (tv < fChanRowMap[chan][j]->VldTime()) {
2006  fChanRowMap[chan].insert(fChanRowMap[chan].begin()+j,row);
2007  wasInserted=true;
2008  break;
2009  }
2010  }
2011  if (!wasInserted)
2012  fChanRowMap[chan].push_back(row);
2013  }
2014  }
2015 
2016  fChannelVec.clear();
2017  std::unordered_map<uint64_t,std::vector<nutools::dbi::Row*> >::iterator itr = fChanRowMap.begin();
2018  std::unordered_map<uint64_t,std::vector<nutools::dbi::Row*> >::iterator itrEnd = fChanRowMap.end();
2019 
2020  for (; itr != itrEnd; ++itr)
2021  fChannelVec.push_back(itr->first);
2022 
2023 
2024  }
2025 
2026  //************************************************************
2027 
2028  std::vector<nutools::dbi::Row*> Table::GetVldRows(uint64_t channel)
2029  {
2030  return fChanRowMap[channel];
2031  }
2032 
2033  //************************************************************
2034 
2035  nutools::dbi::Row* Table::GetVldRow(uint64_t channel, double t)
2036  {
2037  std::vector<nutools::dbi::Row*>& rlist = fChanRowMap[channel];
2038  if (rlist.empty()) return 0;
2039  int irow=-1;
2040  double tv;
2041  // fChanRowMap is time-ordered, so this simplifies things
2042  unsigned int i=0;
2043  for ( ; i<rlist.size(); ++i) {
2044  tv = rlist[i]->VldTime();
2045  if (t >= tv) irow=i;
2046  else break;
2047  }
2048  if (irow>=0) return rlist[irow];
2049  return 0;
2050  }
2051 
2052  //************************************************************
2053  bool Table::Tag(std::string tn, bool override)
2054  {
2055  if (tn != "") fTag = tn;
2056 
2057  if (fTag == "") return false;
2058 
2059  std::stringstream myss;
2060 
2061  myss << fWSURL << "tag?table=" << Schema() << "." << Name() << "&";
2062  myss << "tag=" << fTag;
2063  if (override)
2064  myss << "&override=yes";
2065 
2066  int status;
2067  std::string pwd = GetPassword();
2068 
2069  postHTTPsigned(myss.str().c_str(), pwd.c_str(), NULL, 0, NULL, 0, &status);
2070 
2071  return (status==0);
2072  }
2073 
2074  //************************************************************
2075  bool Table::WriteToDB(bool commit)
2076  {
2077  if (! CheckForNulls()) return false;
2078 
2079  bool doWrite = ! fIgnoreDB;
2080  bool hasConn = fHasConnection;
2081 
2082  try {
2084  }
2085  catch (std::runtime_error& e) {
2086  std::cerr << e.what() << std::endl;
2087  return false;
2088  }
2089 
2090  // make a connection to the dB if one doesn't already exist
2091  if (doWrite) {
2092  if (! fHasConnection) {
2093  GetConnection();
2094  hasConn = false;
2095  }
2096 
2097  if (!fConnection) {
2098  std::cerr << "Table::WriteToDB: No connection to the database!" << std::endl;
2099  doWrite = false;
2100  }
2101  else {
2102  // now check that the table actually exists...
2103  if (!ExistsInDB()) {
2104  std::cerr << "Table::WriteToDB: Table does not exist in database!"
2105  << std::endl;
2106  doWrite = false;
2107  }
2108  }
2109  }
2110 
2111  bool retVal = true;
2112 
2113  // get current timestamp:
2114  std::string ts = Util::GetCurrentTimeAsString();
2115 
2116  PGresult* res = PQexec(fConnection, "BEGIN");
2117  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
2118  std::cerr << "BEGIN command failed: " << PQerrorMessage(fConnection) << std::endl;
2119  PQclear(res);
2120  CloseConnection();
2121  return false;
2122  }
2123 
2124  PQclear(res);
2125 
2126  std::string cmd = "SET search_path TO " + fSchema;
2127  res = PQexec(fConnection, cmd.c_str());
2128  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
2129  std::cerr << "\'" << cmd << "\' command failed" << std::endl;
2130  PQclear(res);
2131  CloseConnection();
2132  return false;
2133  }
2134  PQclear(res);
2135  cmd.clear();
2136 
2137  std::map<std::string,int> colMap = GetColNameToIndexMap();
2138  int insertTimeIdx = colMap["inserttime"];
2139  int insertUserIdx = colMap["insertuser"];
2140  int updateTimeIdx = colMap["updatetime"];
2141  int updateUserIdx = colMap["updateuser"];
2142  // now create the INSERT command
2143  for (unsigned int i=0; i<fRow.size(); ++i) {
2144  // do an INSERT only if this entry does not already exists in the dB
2145  if (! fRow[i].InDB()) {
2146  Row r(fRow[i]);
2147  if (addInsertTime) r.Set(insertTimeIdx,ts);
2148  if (addInsertUser) r.Set(insertUserIdx,fUser);
2149 
2150  int nrowInsert = fCol.size();
2151  for (unsigned int j=0; j<fCol.size(); ++j) {
2152  if (fCol[j].Name() == "updatetime")
2153  nrowInsert--;
2154  else if (fCol[j].Name() == "updateuser")
2155  nrowInsert--;
2156  else if (fCol[j].Type() == "autoincr")
2157  nrowInsert--;
2158  }
2159 
2160  std::ostringstream outs;
2161 
2162  int ic=0;
2163  outs << "INSERT INTO " << Schema() << "." << Name() << " (";
2164  for (unsigned int j=0; j<fCol.size(); ++j) {
2165  if (fCol[j].Name() == "updatetime") continue;
2166  if (fCol[j].Name() == "updateuser") continue;
2167  if (fCol[j].Type() == "autoincr") continue;
2168 
2169  outs << fCol[j].Name();
2170  if (ic < nrowInsert-1) outs << ",";
2171  ++ic;
2172  }
2173  outs << ") VALUES (";
2174 
2175  ic = 0;
2176  for (unsigned int j=0; j<fCol.size(); ++j) {
2177  if (fCol[j].Name() == "updatetime") continue;
2178  if (fCol[j].Name() == "updateuser") continue;
2179  if (fCol[j].Type() == "autoincr") continue;
2180 
2181  outs << r.Col(j);
2182 
2183  if (ic < nrowInsert-1) outs << ",";
2184  ++ic;
2185  }
2186 
2187  outs << ")";
2188 
2189  if (fVerbosity > 0)
2190  std::cerr << "Table::WriteToDB: Executing PGSQL command: \n\t"
2191  << outs.str() << std::endl;
2192 
2193  if (!commit)
2194  std::cout << outs.str() << std::endl;
2195  else {
2196  if (doWrite) {
2197  boost::posix_time::ptime ctt1;
2198  boost::posix_time::ptime ctt2;
2199 
2200  if (fTimeQueries)
2201  ctt1 = boost::posix_time::microsec_clock::local_time();
2202 
2203  res = PQexec(fConnection, outs.str().c_str());
2204 
2205  if (fTimeQueries) {
2206  ctt2 = boost::posix_time::microsec_clock::local_time();
2207  boost::posix_time::time_duration tdiff = ctt2 - ctt1;
2208  std::cerr << "Table::WriteToDB(" << Name() << "): query took "
2209  << tdiff.total_milliseconds() << " ms" << std::endl;
2210  }
2211 
2212  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
2213  CacheDBCommand(outs.str());
2214  std::cerr << "INSERT failed: " << PQerrorMessage(fConnection)
2215  << std::endl;
2216  retVal = false;
2217  }
2218  else {
2219  fRow[i].SetInDB();
2220  // set insert columns
2221  if (addInsertTime) fRow[i].Col(insertTimeIdx).Set(ts);
2222  if (addInsertUser) fRow[i].Col(insertUserIdx).Set(fUser);
2223  // set autoincr columns
2224  long iseq;
2225  std::string seqstr;
2226  for (unsigned int j=0; j<fCol.size(); ++j) {
2227  if (fCol[j].Type() == "autoincr") {
2228  if (this->GetCurrSeqVal(fCol[j].Name(),iseq)) {
2229  seqstr = boost::lexical_cast<std::string>(iseq);
2230  fRow[i].Col(j).Set(seqstr,true);
2231  }
2232  }
2233  }
2234  }
2235  PQclear(res);
2236  }
2237  else
2238  CacheDBCommand(outs.str());
2239  }
2240  }
2241  else {
2242  if ( fRow[i].NModified() > 0 ) {
2243  Row r(fRow[i]);
2244  if (addUpdateTime) r.Update(updateTimeIdx,ts);
2245  if (addUpdateUser) r.Update(updateUserIdx,fUser);
2246  std::ostringstream outs;
2247  outs << "UPDATE " << Schema() << "." << Name() << " SET ";
2248  int im = 0;
2249  for (unsigned int j=0; j<fCol.size() && im < r.NModified(); ++j) {
2250  if (r.Col(j).Modified()) {
2251  outs << fCol[j].Name() + "=";
2252  outs << r.Col(j);
2253  ++im;
2254  if (im < r.NModified()) outs << ",";
2255  }
2256  }
2257  outs << " WHERE ";
2258  // now print out all pkey values
2259  int nkey = fPKeyList.size();
2260  for (int j=0; j<nkey; ++j) {
2261  std::string pkey = fPKeyList[j]->Name();
2262  int pkeyIdx = colMap[pkey];
2263  outs << pkey << "=" << r.Col(pkeyIdx);
2264  if (j < (nkey-1)) outs << " and ";
2265  }
2266 
2267  if (fVerbosity > 0)
2268  std::cerr << "Table::WriteToDB: Executing PGSQL command: \n\t"
2269  << outs.str() << std::endl;
2270 
2271  if (!commit)
2272  std::cout << outs.str() << std::endl;
2273  else {
2274  if (doWrite) {
2275  res = PQexec(fConnection, outs.str().c_str());
2276  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
2277  CacheDBCommand(outs.str());
2278  std::cerr << "UPDATE failed: " << PQerrorMessage(fConnection) << std::endl;
2279  retVal = false;
2280  }
2281  else {
2282  // set update columns
2283  if (addUpdateTime) fRow[i].Col(updateTimeIdx).Set(ts);
2284  if (addUpdateUser) fRow[i].Col(updateUserIdx).Set(fUser);
2285  }
2286  PQclear(res);
2287  }
2288  else
2289  CacheDBCommand(outs.str());
2290  }
2291  }
2292  }
2293  }
2294 
2295  res = PQexec(fConnection, "END");
2296  PQclear(res);
2297 
2298  // close connection to the dB if necessary
2299  if (! hasConn) CloseConnection();
2300 
2301  return retVal;
2302  }
2303 
2304  //************************************************************
2305  bool Table::MakeConditionsCSVString(std::stringstream& ss)
2306  {
2307  int ncol = this->NCol();
2308  int nrow = this->NRow();
2309 
2310  ss << "channel,tv,";
2311  bool first = true;
2312  for (int i=0; i<ncol; ++i) {
2313  std::string cname = this->GetCol(i)->Name();
2314  if(!first) ss << ",";
2315  first = false;
2316  ss << cname;
2317  }
2318  ss << std::endl;
2319 
2320  ss << "tolerance,,";
2321  first = true;
2322  for (int j=0; j<ncol; ++j) {
2323  std::string cname = this->GetCol(j)->Name();
2324  std::string ctype = this->GetCol(j)->Type();
2325  if(!first) ss << ",";
2326  first = false;
2327  float tol = this->Tolerance(cname);
2328  if (tol == 0.) {
2329  if (ctype == "double")
2330  ss << "1.e-10";
2331  else if (ctype == "float")
2332  ss << "1.e-5";
2333  }
2334  else
2335  ss << this->Tolerance(cname);
2336  }
2337  ss << std::endl;
2338  for (int i=0; i<nrow; ++i) {
2339  ss << GetRow(i)->Channel() << ","
2340  << GetRow(i)->VldTime() << ",";
2341  if (GetRow(i)->VldTimeEnd() > GetRow(i)->VldTime())
2342  ss << GetRow(i)->VldTimeEnd() << ",";
2343  first = true;
2344  for (int j=0; j<ncol; ++j) {
2345  if(!first) ss << ",";
2346  first = false;
2347  ss << GetRow(i)->Col(j);
2348  }
2349  ss << std::endl;
2350  }
2351 
2352  return true;
2353  }
2354 
2355  //************************************************************
2356  bool Table::Write(bool commit)
2357  {
2358  if (fDataTypeMask == 0){
2359  std::cerr << "Table::Write: Data type mask is not set!" << std::endl;
2360  return false;
2361  }
2362 
2363  if (fWSURL == "") {
2364  std::cerr << "Table::Write: Web Service URL is not set!" << std::endl;
2365  return false;
2366  }
2367 
2368  if (!Util::RunningOnGrid()) {
2369  std::string putURL = getenv("DBIWSURLPUT");
2370  if (!putURL.empty())
2371  fWSURL = putURL;
2372  }
2373 
2374  std::stringstream ss;
2375 
2377 
2378  int status;
2379  std::string url = fWSURL + "put?table=" + Schema() + "." + Name();
2380 
2381  std::stringstream typeStr;
2382  typeStr << "&type=";
2383  if ((fDataTypeMask & kMCOnly)) typeStr << "mc";
2384  if ((fDataTypeMask & kDataOnly)) typeStr << "data";
2385 
2386  url += typeStr.str();
2387 
2388  // get web service password
2389  std::string pwd = GetPassword();
2390 
2391  boost::posix_time::ptime ctt1;
2392  boost::posix_time::ptime ctt2;
2393 
2394  if (fTimeQueries)
2395  ctt1 = boost::posix_time::microsec_clock::local_time();
2396 
2397  if (fVerbosity>0)
2398  std::cout << "Posting data to: " << url << std::endl;
2399 
2400  postHTTPsigned(url.c_str(), pwd.c_str(), NULL, 0,
2401  ss.str().c_str(), ss.str().length(), &status);
2402  if (fTimeQueries) {
2403  ctt2 = boost::posix_time::microsec_clock::local_time();
2404  boost::posix_time::time_duration tdiff = ctt2 - ctt1;
2405  std::cerr << "Table::Write(" << Name() << "): query took "
2406  << tdiff.total_milliseconds() << " ms" << std::endl;
2407  }
2408  return (status == 0);
2409  }
2410 
2411  //************************************************************
2412  bool Table::WriteToCSV(std::string fname, bool appendToFile,
2413  bool writeColNames)
2414  {
2415  if (! CheckForNulls()) return false;
2416 
2417  std::ofstream fout;
2418  if (!appendToFile)
2419  fout.open(fname.c_str());
2420  else
2421  fout.open(fname.c_str(),std::ios_base::app);
2422 
2423  if (fTableType==kConditionsTable) {
2424  std::stringstream ss;
2426  fout << ss.str();
2427  }
2428  else {
2429  if (writeColNames) {
2430  for (unsigned int j=0; j<fCol.size(); ++j) {
2431  fout << fCol[j].Name();
2432  if (j<fCol.size()-1) fout << ",";
2433  }
2434  }
2435 
2436  for (unsigned int i=0; i<fRow.size(); ++i) {
2437  for (unsigned int j=0; j<fCol.size(); ++j) {
2438  fout << fRow[i].Col(j);
2439  if (j<fCol.size()-1) fout << ",";
2440  }
2441  fout << std::endl;
2442  }
2443  }
2444 
2445  fout.close();
2446 
2447  return true;
2448  }
2449 
2450  //************************************************************
2451  void Table::RemoveValidityRange(std::string& cname)
2452  {
2453  unsigned int i=0;
2454  for (; i<fValidityStart.size(); ++i)
2455  if (fValidityStart[i].Name() == cname) {
2456  fValidityStart.erase(fValidityStart.begin()+i);
2457  fValidityEnd.erase(fValidityEnd.begin()+i);
2458  }
2459  }
2460 
2461  //************************************************************
2462  // Add the ith column in the table to the list of columns
2463  // that are to be "distinct"
2464  //************************************************************
2465  bool Table::AddDistinctColumn(unsigned int i)
2466  {
2467  if (i >= fCol.size()) return false;
2468 
2469  const ColumnDef* c = &fCol[i];
2470  for (unsigned int j=0; j<fDistinctCol.size(); ++j)
2471  if (fDistinctCol[j] == c) return true;
2472 
2473  fDistinctCol.push_back(c);
2474 
2475  return true;
2476  }
2477 
2478  //************************************************************
2479  // Remove the ith column in the table to the list of columns
2480  // that are to be "distinct"
2481  //************************************************************
2482  bool Table::RemoveDistinctColumn(unsigned int i)
2483  {
2484  if (i >= fCol.size()) return false;
2485 
2486  const ColumnDef* c = &fCol[i];
2487  for (unsigned int j=0; j<fDistinctCol.size(); ++j)
2488  if (fDistinctCol[j] == c) {
2489  fDistinctCol.erase(fDistinctCol.begin() + j);
2490  return true;
2491  }
2492 
2493  return false;
2494  }
2495 
2496  //************************************************************
2497  // Add the column with name "cname" in the table to the list
2498  // of columns that are to be "distinct"
2499  //************************************************************
2500  bool Table::AddDistinctColumn(std::string cname)
2501  {
2502  const ColumnDef* c = GetCol(cname);
2503 
2504  if (!c) return false;
2505 
2506  for (unsigned int j=0; j<fDistinctCol.size(); ++j)
2507  if (fDistinctCol[j] == c) return true;
2508 
2509  fDistinctCol.push_back(c);
2510 
2511  return true;
2512  }
2513 
2514  //************************************************************
2515  // Remove the column with name "cname" in the table to the
2516  // list of columns that are to be "distinct"
2517  //************************************************************
2518  bool Table::RemoveDistinctColumn(std::string cname)
2519  {
2520  const ColumnDef* c = GetCol(cname);
2521 
2522  if (!c) return false;
2523 
2524  for (unsigned int j=0; j<fDistinctCol.size(); ++j)
2525  if (fDistinctCol[j] == c) {
2526  fDistinctCol.erase(fDistinctCol.begin() + j);
2527  return true;
2528  }
2529 
2530  return false;
2531  }
2532 
2533  //************************************************************
2534  // Add the ith column in the table to the list of columns
2535  // that define the sorting order
2536  //************************************************************
2537  bool Table::AddOrderColumn(unsigned int i)
2538  {
2539  if (i >= fCol.size()) return false;
2540 
2541  const ColumnDef* c = &fCol[i];
2542  for (unsigned int j=0; j<fOrderCol.size(); ++j)
2543  if (fOrderCol[j] == c) return true;
2544 
2545  fOrderCol.push_back(c);
2546 
2547  return true;
2548  }
2549 
2550  //************************************************************
2551  // Remove the ith column in the table to the list of columns
2552  // that define the sorting order
2553  //************************************************************
2554  bool Table::RemoveOrderColumn(unsigned int i)
2555  {
2556  if (i >= fCol.size()) return false;
2557 
2558  const ColumnDef* c = &fCol[i];
2559  for (unsigned int j=0; j<fOrderCol.size(); ++j)
2560  if (fOrderCol[j] == c) {
2561  fOrderCol.erase(fOrderCol.begin() + j);
2562  return true;
2563  }
2564 
2565  return false;
2566  }
2567 
2568  //************************************************************
2569  // Add the column with name "cname" in the table to the list
2570  // of columns that define the sorting order
2571  //************************************************************
2572  bool Table::AddOrderColumn(std::string cname)
2573  {
2574  const ColumnDef* c = GetCol(cname);
2575 
2576  if (!c) return false;
2577 
2578  for (unsigned int j=0; j<fOrderCol.size(); ++j)
2579  if (fOrderCol[j] == c) return true;
2580 
2581  fOrderCol.push_back(c);
2582 
2583  return true;
2584  }
2585 
2586  //************************************************************
2587  // Remove the column with name "cname" in the table to the
2588  // list of columns that define the sorting order
2589  //************************************************************
2590  bool Table::RemoveOrderColumn(std::string cname)
2591  {
2592  const ColumnDef* c = GetCol(cname);
2593 
2594  if (!c) return false;
2595 
2596  for (unsigned int j=0; j<fOrderCol.size(); ++j)
2597  if (fOrderCol[j] == c) {
2598  fOrderCol.erase(fOrderCol.begin() + j);
2599  return true;
2600  }
2601 
2602  return false;
2603  }
2604 
2605  //************************************************************
2606  std::string Table::GetPassword()
2607  {
2608  std::string pwd = "";
2609 
2610  char* pwdFile = getenv("DBIWSPWDFILE");
2611  if (pwdFile) {
2612  std::ifstream fin;
2613  fin.open(pwdFile);
2614  if (!fin.is_open() || !fin.good()) {
2615  std::cerr << "Could not open password file " << pwdFile
2616  << ". Canceling Table::Write()" << std::endl;
2617  return pwd;
2618  }
2619  else {
2620  fin >> pwd;
2621  fin.close();
2622  }
2623  }
2624 
2625  return pwd;
2626  }
2627 
2628  }
2629 
2630 }
bool Update(int idx, T value)
Definition: Row.h:38
std::string fUser
Definition: Table.h:369
TRandom r
Definition: spectrum.C:23
void FillChanRowMap()
Definition: Table.cpp:1989
bool addUpdateUser
Definition: Table.h:344
code to link reconstructed objects back to the MC truth information
std::string Name()
Definition: Table.h:59
bool MakeConditionsCSVString(std::stringstream &ss)
Definition: Table.cpp:2305
TString fin
Definition: Style.C:24
bool GetConnection(int ntry=0)
Definition: Table.cpp:759
void CacheDBCommand(std::string cmd)
Definition: Table.cpp:438
std::vector< nutools::dbi::ColumnDef > fCol
Definition: Table.h:387
void PrintColumns()
Definition: Table.cpp:645
std::vector< const nutools::dbi::ColumnDef * > fOrderCol
Definition: Table.h:394
TTree * t1
Definition: plottest35.C:26
bool Modified() const
Definition: Column.h:45
uint64_t fMaxChannel
Definition: Table.h:366
bool fDisableCache
Definition: Table.h:354
const nutools::dbi::ColumnDef * GetCol(int i)
Definition: Table.h:133
static bool RunningOnGrid()
Definition: Util.cpp:132
bool addInsertTime
Definition: Table.h:341
void ClearValidity()
Definition: Table.cpp:484
std::string fQEURL
Definition: Table.h:385
void SetDataSource(std::string ds)
Definition: Table.cpp:537
std::vector< nutools::dbi::Row * > GetVldRows(uint64_t channel)
Definition: Table.cpp:2028
double fMinTSVld
Definition: Table.h:406
std::map< std::string, int > GetColNameToIndexMap()
Definition: Table.cpp:598
bool ExecuteSQL(std::string cmd, PGresult *&res)
Definition: Table.cpp:1030
std::vector< std::string > fExcludeCol
Definition: Table.h:396
bool SetPasswordFile(const char *fname=0)
fname should be the name of the file
Definition: Table.cpp:859
static std::string GetCurrentTimeAsString()
Definition: Util.cpp:42
bool AddOrderColumn(unsigned int i)
Definition: Table.cpp:2537
std::string Schema()
Definition: Table.h:215
std::vector< nutools::dbi::ColumnDef > fValidityStart
Definition: Table.h:390
bool RemoveRow(int i)
Definition: Table.cpp:392
uint64_t Channel()
Definition: Row.h:55
int NModified()
Definition: Row.h:49
double VldTimeEnd()
Definition: Row.h:57
bool CloseConnection()
Definition: Table.cpp:837
int fConnectionTimeout
Definition: Table.h:361
bool fIgnoreEnvVar
Definition: Table.h:345
Simple service to provide a RunHistory configured to the right run.
Definition: Column.cpp:14
void * Tuple
Definition: DBFolder.h:13
std::string fFolder
Definition: Table.h:380
bool RemoveOrderColumn(unsigned int i)
Definition: Table.cpp:2554
bool LoadFromCSV(std::string fname)
Definition: Table.cpp:1298
PGconn * fConnection
Definition: Table.h:401
bool GetConnectionInfo(int ntry=0)
Definition: Table.cpp:699
std::vector< const nutools::dbi::ColumnDef * > fPKeyList
Definition: Table.h:392
bool CheckForNulls()
Definition: Table.cpp:422
bool LoadNonConditionsTable()
Definition: Table.cpp:1758
void RemoveValidityRange(std::string &cname)
Definition: Table.cpp:2451
std::string Folder()
Definition: Table.h:321
std::string fValiditySQL
Definition: Table.h:378
uint64_t fMinChannel
Definition: Table.h:365
fclose(fg1)
bool fHasRecordTime
Definition: Table.h:352
void SetTolerance(std::string &cname, float t)
Definition: Table.cpp:619
bool fHasConnection
Definition: Table.h:351
void SetRecordTime(double t)
Definition: Table.cpp:450
void SetDBName(std::string dbname)
Definition: Table.h:83
std::vector< std::string > GetColNames()
Definition: Table.cpp:608
void SetDBHost(std::string dbhost)
Definition: Table.h:85
nutools::dbi::Row *const NewRow()
Definition: Table.h:126
bool LoadConditionsTable()
Definition: Table.cpp:1880
std::string fPassword
Definition: Table.h:377
bool SetRole(std::string role)
Definition: Table.cpp:852
Int_t col[ntarg]
Definition: Style.C:29
void AddRow(const Row *row)
Definition: Table.cpp:356
float Tolerance(std::string &cname)
Definition: Table.cpp:632
std::string fRole
Definition: Table.h:370
std::vector< uint64_t > fChannelVec
Definition: Table.h:398
double fRecordTime
Definition: Table.h:407
bool Write(bool commit=true)
Definition: Table.cpp:2356
void AddEmptyRows(unsigned int nrow)
Definition: Table.cpp:378
bool SetDetector(std::string det)
Definition: Table.cpp:500
void SetTableName(std::string tname)
Definition: Table.cpp:524
std::string Type() const
Definition: ColumnDef.h:22
double fMaxTSVld
Definition: Table.h:405
short fVerbosity
Definition: Table.h:357
bool IsNull() const
Definition: Column.h:44
void SetDBInfo(std::string name, std::string host, std::string port, std::string user)
Definition: Table.cpp:557
bool Set(int idx, T value)
Definition: Row.h:31
std::string fDBHost
Definition: Table.h:372
bool RemoveDistinctColumn(unsigned int i)
Definition: Table.cpp:2482
pg_result PGresult
Definition: Table.h:19
std::string Name() const
Definition: ColumnDef.h:21
bool AddDistinctColumn(unsigned int i)
Definition: Table.cpp:2465
double value
Definition: spectrum.C:18
std::string fSchema
Definition: Table.h:374
bool LoadUnstructuredConditionsTable()
Definition: Table.cpp:1850
void SetCanBeNull(bool f)
Definition: ColumnDef.h:30
std::string fWSURL
Definition: Table.h:383
int AddCol(std::string cname, std::string ctype)
Definition: Table.cpp:333
bool GetDetector(std::string &det) const
Definition: Table.cpp:515
void SetDBPort(std::string p)
Definition: Table.h:87
constexpr auto const & left(const_AssnsIter< L, R, D, Dir > const &a, const_AssnsIter< L, R, D, Dir > const &b)
Definition: AssnsIter.h:94
void * Dataset
Definition: DBFolder.h:12
bool Tag(std::string tn="", bool override=false)
Definition: Table.cpp:2053
bool GetCurrSeqVal(std::string col, long &iseq)
Definition: Table.cpp:970
bool addInsertUser
Definition: Table.h:342
void PrintPQErrorMsg() const
Definition: Table.cpp:493
TFile * file
bool addUpdateTime
Definition: Table.h:343
int GetColIndex(std::string cname)
Definition: Table.cpp:589
bool fValidityChanged
Definition: Table.h:346
nutools::dbi::Row *const GetRow(int i)
Definition: Table.cpp:413
bool fTestedExists
Definition: Table.h:349
decltype(auto) constexpr begin(T &&obj)
ADL-aware version of std::begin.
Definition: StdUtils.h:69
bool GetColsFromDB(std::vector< std::string > pkeyList={})
Definition: Table.cpp:245
std::string fDBCacheFile
Definition: Table.h:376
nutools::dbi::Row * GetVldRow(uint64_t channel, double t)
Definition: Table.cpp:2035
std::string fTag
Definition: Table.h:382
bool SetTableType(int t)
Definition: Table.cpp:458
Float_t e
Definition: plot.C:35
std::string GetPassword()
Definition: Table.cpp:2606
void SetUser(std::string uname)
Definition: Table.h:79
double VldTime()
Definition: Row.h:56
std::string fDBPort
Definition: Table.h:371
std::vector< std::pair< int, int > > fNullList
Definition: Table.h:395
Column & Col(int i)
Definition: Row.h:53
bool WriteToDB(bool commit=true)
use commit=false if just testing
Definition: Table.cpp:2075
std::string fTableName
Definition: Table.h:368
std::string fDetector
Definition: Table.h:379
std::vector< nutools::dbi::Row > fRow
Definition: Table.h:388
std::vector< nutools::dbi::ColumnDef > fValidityEnd
Definition: Table.h:391
int ParseSelfStatusLine(char *line)
Definition: Table.cpp:1522
std::string fDBName
Definition: Table.h:373
decltype(auto) constexpr empty(T &&obj)
ADL-aware version of std::empty.
Definition: StdUtils.h:109
bool WriteToCSV(std::string fname, bool appendToFile=false, bool writeColNames=false)
Definition: Table.cpp:2412
std::unordered_map< uint64_t, std::vector< nutools::dbi::Row * > > fChanRowMap
Definition: Table.h:399
std::string tableName
std::string fUConDBURL
Definition: Table.h:384
bool GetDataFromWebService(Dataset &, std::string)
Definition: Table.cpp:1572
std::vector< const nutools::dbi::ColumnDef * > fDistinctCol
Definition: Table.h:393
map< int, array< map< int, double >, 2 >> Table
Definition: plot.C:18