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