Public Member Functions | Private Member Functions | Private Attributes | List of all members
util::DatabaseUtil Class Reference

#include <DatabaseUtil.h>

Public Member Functions

 DatabaseUtil (fhicl::ParameterSet const &pset)
 
void reconfigure (fhicl::ParameterSet const &pset)
 
int GetLifetimeFromDB (int run, double &lftime_real)
 
int GetTriggerOffsetFromDB (int run, double &T0_real)
 
int GetTemperatureFromDB (int run, double &temp_real)
 
int GetEfieldValuesFromDB (int run, std::vector< double > &efield)
 
int GetPOTFromDB (int run, long double &POT)
 
UBChannelMap_t GetUBChannelMap (int data_taking_timestamp=-1, int swizzling_timestamp=-1)
 
UBChannelReverseMap_t GetUBChannelReverseMap (int data_taking_timestamp=-1, int swizzling_timestamp=-1)
 
int SelectFieldByName (std::vector< std::string > &value, const char *field, const char *condition, const char *table)
 
std::vector< std::string > & split (const std::string &s, char delim, std::vector< std::string > &elems)
 
bool ToughErrorTreatment () const
 
bool ShouldConnect () const
 

Private Member Functions

int SelectSingleFieldByQuery (std::vector< std::string > &value, const char *query)
 
int Connect (int conn_wait=0)
 
int DisConnect ()
 
void LoadUBChannelMap (int data_taking_timestamp=-1, int swizzling_timestamp=-1)
 

Private Attributes

char connection_str [200]
 
PGconn * conn
 
std::string fDBHostName
 
std::string fDBName
 
std::string fDBUser
 
std::string fTableName
 
int fPort
 
std::string fPassword
 
bool fToughErrorTreatment
 
bool fShouldConnect
 
UBChannelMap_t fChannelMap
 
UBChannelReverseMap_t fChannelReverseMap
 

Detailed Description

Definition at line 52 of file DatabaseUtil.h.

Constructor & Destructor Documentation

util::DatabaseUtil::DatabaseUtil ( fhicl::ParameterSet const &  pset)

Definition at line 20 of file DatabaseUtil_service.cc.

21 {
22  conn = NULL;
23  this->reconfigure(pset);
24  fChannelMap.clear();
25  fChannelReverseMap.clear();
26 }
void reconfigure(fhicl::ParameterSet const &pset)
UBChannelReverseMap_t fChannelReverseMap
Definition: DatabaseUtil.h:90
UBChannelMap_t fChannelMap
Definition: DatabaseUtil.h:89

Member Function Documentation

int util::DatabaseUtil::Connect ( int  conn_wait = 0)
private

Definition at line 29 of file DatabaseUtil_service.cc.

30 {
31  if(!fShouldConnect)
32  return -1;
33 
34  if(conn_wait)
35  sleep(conn_wait);
36 
37  conn = PQconnectdb(connection_str);
38  if (PQstatus(conn) == CONNECTION_BAD) {
39  mf::LogWarning("DatabaseUtil") << "Connection to database failed, "<<PQerrorMessage(conn)<<"\n";
40  if( ( strstr(PQerrorMessage(conn),"remaining connection slots are reserved")!=NULL ||
41  strstr(PQerrorMessage(conn),"sorry, too many clients already")!=NULL )
42  && conn_wait<20 ) {
43  conn_wait+=2;
44  mf::LogWarning("DatabaseUtil") << "retrying connection after " << conn_wait << " seconds \n";
45  return this->Connect(conn_wait);
46  }
48  throw cet::exception("DataBaseUtil") << " DB connection failed\n";
49 
50  } else {
51  MF_LOG_DEBUG("DatabaseUtil")<<"Connected OK\n";
52  return 1;
53  }
54  return -1;
55 }
char connection_str[200]
Definition: DatabaseUtil.h:77
MaybeLogger_< ELseverityLevel::ELsev_warning, false > LogWarning
int Connect(int conn_wait=0)
#define MF_LOG_DEBUG(id)
cet::coded_exception< error, detail::translate > exception
Definition: exception.h:33
int util::DatabaseUtil::DisConnect ( )
private

Definition at line 58 of file DatabaseUtil_service.cc.

59 {
60  if(!fShouldConnect)
61  return -1;
62  //close connection
63  MF_LOG_DEBUG("DatabaseUtil")<<"Closing Connection \n";
64  PQfinish(conn);
65  return 1;
66 }
#define MF_LOG_DEBUG(id)
int util::DatabaseUtil::GetEfieldValuesFromDB ( int  run,
std::vector< double > &  efield 
)

Definition at line 192 of file DatabaseUtil_service.cc.

193 {
194 
195  std::vector<std::string> retvalue;
196 
197  char query[200];
198  sprintf(query,"SELECT EFbet FROM EField,%s WHERE Efield.FID = %s.FID AND run = %d ORDER BY planegap",fTableName.c_str(),fTableName.c_str(),run);
199  int err=SelectSingleFieldByQuery(retvalue,query);
200 
201  if(err!=-1 && retvalue.size()>=1){
202  efield.clear(); //clear value before setting new values
203  for(unsigned int i=0;i<retvalue.size();i++) {
204  char * endstr;
205  efield.push_back(std::strtod(retvalue[i].c_str(),&endstr));
206  }
207  return 0;
208  }
209 
210  return -1;
211 
212 }
int SelectSingleFieldByQuery(std::vector< std::string > &value, const char *query)
void err(const char *fmt,...)
Definition: message.cpp:226
std::string fTableName
Definition: DatabaseUtil.h:83
query_result< Args... > query(sqlite3 *db, std::string const &ddl)
Definition: select.h:75
int util::DatabaseUtil::GetLifetimeFromDB ( int  run,
double &  lftime_real 
)

Definition at line 235 of file DatabaseUtil_service.cc.

235  {
236 
237  // char query[100];
238  // sprintf(query,"SELECT tau FROM argoneut_test WHERE run = %d",run);
239 
240  std::vector<std::string> retvalue;
241  char cond[30];
242  sprintf(cond,"run = %d",run);
243  int err=SelectFieldByName(retvalue,"tau",cond,fTableName.c_str());
244 
245  if(err!=-1 && retvalue.size()==1){
246  char * endstr;
247  lftime_real=std::strtod(retvalue[0].c_str(),&endstr);
248  return 0;
249  }
250 
251 
252  return -1;
253 
254 }
void err(const char *fmt,...)
Definition: message.cpp:226
std::string fTableName
Definition: DatabaseUtil.h:83
int SelectFieldByName(std::vector< std::string > &value, const char *field, const char *condition, const char *table)
int util::DatabaseUtil::GetPOTFromDB ( int  run,
long double &  POT 
)

Definition at line 278 of file DatabaseUtil_service.cc.

278  {
279 
280  // char query[100];
281  // sprintf(query,"SELECT tau FROM argoneut_test WHERE run = %d",run);
282 
283  std::vector<std::string> retvalue;
284  char cond[30];
285  sprintf(cond,"run = %d",run);
286  int err=SelectFieldByName(retvalue,"pot",cond,fTableName.c_str());
287 
288  if(err!=-1 && retvalue.size()==1){
289  char * endstr;
290  POT=std::strtold(retvalue[0].c_str(),&endstr);
291  return 0;
292  }
293 
294 
295  return -1;
296 
297 }
void err(const char *fmt,...)
Definition: message.cpp:226
std::string fTableName
Definition: DatabaseUtil.h:83
int SelectFieldByName(std::vector< std::string > &value, const char *field, const char *condition, const char *table)
int util::DatabaseUtil::GetTemperatureFromDB ( int  run,
double &  temp_real 
)

Definition at line 171 of file DatabaseUtil_service.cc.

172 {
173  std::vector<std::string> retvalue;
174  char cond[30];
175  sprintf(cond,"run = %d",run);
176  int err=SelectFieldByName(retvalue,"temp",cond,fTableName.c_str());
177 
178  if(err!=-1 && retvalue.size()==1){
179  char * endstr;
180  temp_real=std::strtod(retvalue[0].c_str(),&endstr);
181  return 0;
182  }
183 
184  return -1;
185 
186 
187 }
void err(const char *fmt,...)
Definition: message.cpp:226
std::string fTableName
Definition: DatabaseUtil.h:83
int SelectFieldByName(std::vector< std::string > &value, const char *field, const char *condition, const char *table)
int util::DatabaseUtil::GetTriggerOffsetFromDB ( int  run,
double &  T0_real 
)

Definition at line 256 of file DatabaseUtil_service.cc.

256  {
257 
258  // char query[100];
259  // sprintf(query,"SELECT tau FROM argoneut_test WHERE run = %d",run);
260 
261  std::vector<std::string> retvalue;
262  char cond[30];
263  sprintf(cond,"run = %d",run);
264  int err=SelectFieldByName(retvalue,"T0",cond,fTableName.c_str());
265 
266  if(err!=-1 && retvalue.size()==1){
267  char * endstr;
268  T0_real=std::strtod(retvalue[0].c_str(),&endstr);
269  return 0;
270  }
271 
272 
273  return -1;
274 
275 }
void err(const char *fmt,...)
Definition: message.cpp:226
std::string fTableName
Definition: DatabaseUtil.h:83
int SelectFieldByName(std::vector< std::string > &value, const char *field, const char *condition, const char *table)
UBChannelMap_t util::DatabaseUtil::GetUBChannelMap ( int  data_taking_timestamp = -1,
int  swizzling_timestamp = -1 
)

Definition at line 382 of file DatabaseUtil_service.cc.

382  {
383  LoadUBChannelMap( data_taking_timestamp, swizzling_timestamp );
384  return fChannelMap;
385  }
void LoadUBChannelMap(int data_taking_timestamp=-1, int swizzling_timestamp=-1)
UBChannelMap_t fChannelMap
Definition: DatabaseUtil.h:89
UBChannelReverseMap_t util::DatabaseUtil::GetUBChannelReverseMap ( int  data_taking_timestamp = -1,
int  swizzling_timestamp = -1 
)

Definition at line 387 of file DatabaseUtil_service.cc.

387  {
388  LoadUBChannelMap( data_taking_timestamp, swizzling_timestamp );
389  return fChannelReverseMap;
390  }
void LoadUBChannelMap(int data_taking_timestamp=-1, int swizzling_timestamp=-1)
UBChannelReverseMap_t fChannelReverseMap
Definition: DatabaseUtil.h:90
void util::DatabaseUtil::LoadUBChannelMap ( int  data_taking_timestamp = -1,
int  swizzling_timestamp = -1 
)
private

Definition at line 301 of file DatabaseUtil_service.cc.

301  {
302 
303  if ( fChannelMap.size()>0 ) {
304  // Use prevously grabbed data to avoid repeated call to database.
305  // Also this avoids inglorious segfault.
306  return;
307  }
308  if ( conn==NULL )
309  Connect( 0 );
310 
311  if(PQstatus(conn)!=CONNECTION_OK) {
312  mf::LogError("") << __PRETTY_FUNCTION__ << ": Couldn't open connection to postgresql interface" << PQdb(conn) <<":"<<PQhost(conn);
313  PQfinish(conn);
315  << "Failed to get channel map from DB."<< std::endl;
316  }
317 
318  fChannelMap.clear();
319  fChannelReverseMap.clear();
320 
321  PGresult *res = PQexec(conn, "BEGIN");
322  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
323  mf::LogError("")<< "postgresql BEGIN failed";
324  PQclear(res);
325  PQfinish(conn);
327  << "postgresql BEGIN failed." << std::endl;
328  }
329 
330  // Jason St. John's updated call to versioned database.
331  // get_map_double_sec (data_taking_timestamp int DEFAULT now() ,
332  // swizzling_timestamp int DEFAULT now() )
333  // Returns rows of: crate, slot, fem_channel, larsoft_channel
334  // Both arguments are optional, or can be passed their default of now(), or can be passed an explicit timestamp:
335  // Example: "SELECT get_map_double_sec(1438430400);"
336  PQclear(res);
337 
338  char dbquery[200];
339  sprintf(dbquery, "SELECT get_map_double_sec(%i,%i);", data_taking_timestamp, swizzling_timestamp);
340  res = PQexec(conn, dbquery);
341 
342  if ((!res) || (PQresultStatus(res) != PGRES_TUPLES_OK) || (PQntuples(res) < 1))
343  {
344  mf::LogError("")<< "SELECT command did not return tuples properly. \n" << PQresultErrorMessage(res) << "Number rows: "<< PQntuples(res);
345  PQclear(res);
346  PQfinish(conn);
348  << "postgresql SELECT failed." << std::endl;
349  }
350 
351  int num_records=PQntuples(res); //One record per channel, ideally.
352 
353  for (int i=0;i<num_records;i++) {
354  std::string tup = PQgetvalue(res, i, 0); // (crate,slot,FEMch,larsoft_chan) format
355  tup = tup.substr(1,tup.length()-2); // Strip initial & final parentheses.
356  std::vector<std::string> fields;
357  split(tup, ',', fields); // Explode substrings into vector with comma delimiters.
358 
359  int crate_id = atoi( fields[0].c_str() );
360  int slot = atoi( fields[1].c_str() );
361  int boardChan = atoi( fields[2].c_str() );
362  int larsoft_chan = atoi( fields[3].c_str() );
363 
364  UBDaqID daq_id(crate_id,slot,boardChan);
365  std::pair<UBDaqID, UBLArSoftCh_t> p(daq_id,larsoft_chan);
366 
367  if ( fChannelMap.find(daq_id) != fChannelMap.end() ){
368  std::cout << __PRETTY_FUNCTION__ << ": ";
369  std::cout << "Multiple entries!" << std::endl;
370  mf::LogWarning("")<< "Multiple DB entries for same (crate,card,channel). "<<std::endl
371  << "Redefining (crate,card,channel)=>id link ("
372  << daq_id.crate<<", "<< daq_id.card<<", "<< daq_id.channel<<")=>"
373  << fChannelMap.find(daq_id)->second;
374  }
375 
376  fChannelMap.insert( p );
377  fChannelReverseMap.insert( std::pair< UBLArSoftCh_t, UBDaqID >( larsoft_chan, daq_id ) );
378  }
379  this->DisConnect();
380  }// end of LoadUBChannelMap
std::string string
Definition: nybbler.cc:12
MaybeLogger_< ELseverityLevel::ELsev_error, false > LogError
p
Definition: test.py:223
cet::coded_exception< errors::ErrorCodes, ExceptionDetail::translate > Exception
Definition: Exception.h:66
MaybeLogger_< ELseverityLevel::ELsev_warning, false > LogWarning
int Connect(int conn_wait=0)
std::vector< std::string > & split(const std::string &s, char delim, std::vector< std::string > &elems)
QTextStream & endl(QTextStream &s)
UBChannelReverseMap_t fChannelReverseMap
Definition: DatabaseUtil.h:90
UBChannelMap_t fChannelMap
Definition: DatabaseUtil.h:89
void util::DatabaseUtil::reconfigure ( fhicl::ParameterSet const &  pset)

Definition at line 71 of file DatabaseUtil_service.cc.

72 {
73  fDBHostName = pset.get< std::string >("DBHostName" );
74  fDBName = pset.get< std::string >("DBName" );
75  fDBUser = pset.get< std::string >("DBUser");
76  fTableName = pset.get< std::string >("TableName");
77  fPort = pset.get< int >("Port" );
78  fPassword = "";
79  fToughErrorTreatment = pset.get< bool >("ToughErrorTreatment");
80  fShouldConnect = pset.get< bool >("ShouldConnect");
81 
82  // constructor decides if initialized value is a path or an environment variable
83  std::string passfname;
84  cet::search_path sp("FW_SEARCH_PATH");
85  sp.find_file(pset.get< std::string >("PassFileName"), passfname);
86 
87  if (!passfname.empty()) {
88  std::ifstream in(passfname.c_str());
89  if(!in) {
91  << "Database password file '" << passfname
92  << "' not found in FW_SEARCH_PATH; using an empty password.\n";
93  }
94  std::getline(in, fPassword);
95  in.close();
96  }
97  else if (fShouldConnect){
99  << "Database password file '" << pset.get< std::string >("PassFileName")
100  << "' not found in FW_SEARCH_PATH; using an empty password.\n";
101  }
102 
103  sprintf(connection_str,"host=%s dbname=%s user=%s port=%d password=%s ",fDBHostName.c_str(),fDBName.c_str(),fDBUser.c_str(),fPort,fPassword.c_str());
104 
105  return;
106 }
std::string fPassword
Definition: DatabaseUtil.h:85
std::string fDBName
Definition: DatabaseUtil.h:81
std::string string
Definition: nybbler.cc:12
std::string fDBHostName
Definition: DatabaseUtil.h:80
char connection_str[200]
Definition: DatabaseUtil.h:77
std::string fTableName
Definition: DatabaseUtil.h:83
cet::coded_exception< errors::ErrorCodes, ExceptionDetail::translate > Exception
Definition: Exception.h:66
std::string fDBUser
Definition: DatabaseUtil.h:82
int util::DatabaseUtil::SelectFieldByName ( std::vector< std::string > &  value,
const char *  field,
const char *  condition,
const char *  table 
)

Definition at line 216 of file DatabaseUtil_service.cc.

219  {
220 
221  char query[100];
222  sprintf(query,"SELECT %s FROM %s WHERE %s",field, table, condition);
223 
224  return SelectSingleFieldByQuery(value,query);
225 
226 }
int SelectSingleFieldByQuery(std::vector< std::string > &value, const char *query)
query_result< Args... > query(sqlite3 *db, std::string const &ddl)
Definition: select.h:75
int util::DatabaseUtil::SelectSingleFieldByQuery ( std::vector< std::string > &  value,
const char *  query 
)
private

Definition at line 111 of file DatabaseUtil_service.cc.

112 {
113  PGresult *result;
114  char * string_val;
115 
116  if(this->Connect()==-1) {
117  if(fShouldConnect)
118  mf::LogWarning("DatabaseUtil")<< "DB Connection error \n";
119  else
120  mf::LogInfo("DatabaseUtil")<< "Not connecting to DB by choice. \n";
121  return -1;
122  }
123 
124  result = PQexec(conn, query);
125 
126  if (!result) {
127  mf::LogInfo("DatabaseUtil")<< "PQexec command failed, no error code\n";
128  return -1;
129  }
130  else if(PQresultStatus(result)!=PGRES_TUPLES_OK) {
131  if(PQresultStatus(result)==PGRES_COMMAND_OK)
132  MF_LOG_DEBUG("DatabaseUtil")<<"Command executed OK, "<< PQcmdTuples(result) <<" rows affected\n";
133  else
134  mf::LogWarning("DatabaseUtil")<<"Command failed with code "
135  <<PQresStatus(PQresultStatus(result)) <<", error message "
136  <<PQresultErrorMessage(result)<<"\n";
137 
138  PQclear(result);
139  this->DisConnect();
140  return -1;
141  }
142  else {
143  // mf::LogInfo("DatabaseUtil")<<"Query may have returned data\n";
144  // mf::LogInfo("DatabaseUtil")<<"Number of rows returned: "<<PQntuples(result)
145  // <<", fields: "<<PQnfields(result)<<" \n";
146 
147  if(PQntuples(result)>=1){
148  for(int i=0;i<PQntuples(result);i++)
149  {
150  string_val=PQgetvalue(result,i,0);
151  value.push_back(string_val);
152  MF_LOG_DEBUG("DatabaseUtil")<<" extracted value: "<<value[i] << "\n";
153  }
154  PQclear(result);
155  this->DisConnect();
156  return 0;
157  }
158  else {
159  mf::LogWarning("DatabaseUtil")<<"wrong number of rows returned:"<<PQntuples(result)<<"\n";
160  PQclear(result);
161  this->DisConnect();
162  return -1;
163  }
164  }
165 
166 
167 }
static QCString result
MaybeLogger_< ELseverityLevel::ELsev_info, false > LogInfo
MaybeLogger_< ELseverityLevel::ELsev_warning, false > LogWarning
int Connect(int conn_wait=0)
#define MF_LOG_DEBUG(id)
query_result< Args... > query(sqlite3 *db, std::string const &ddl)
Definition: select.h:75
bool util::DatabaseUtil::ShouldConnect ( ) const
inline

Definition at line 70 of file DatabaseUtil.h.

70 { return fShouldConnect; }
std::vector< std::string > & util::DatabaseUtil::split ( const std::string s,
char  delim,
std::vector< std::string > &  elems 
)

Definition at line 394 of file DatabaseUtil_service.cc.

394  {
395  std::stringstream ss(s);
396  std::string item;
397  while (std::getline(ss, item, delim)) {
398  elems.push_back(item);
399  }
400  return elems;
401  }
string delim()
Definition: fcldump.cxx:40
std::string string
Definition: nybbler.cc:12
static QCString * s
Definition: config.cpp:1042
bool util::DatabaseUtil::ToughErrorTreatment ( ) const
inline

Definition at line 69 of file DatabaseUtil.h.

69 { return fToughErrorTreatment; }

Member Data Documentation

PGconn* util::DatabaseUtil::conn
private

Definition at line 79 of file DatabaseUtil.h.

char util::DatabaseUtil::connection_str[200]
private

Definition at line 77 of file DatabaseUtil.h.

UBChannelMap_t util::DatabaseUtil::fChannelMap
private

Definition at line 89 of file DatabaseUtil.h.

UBChannelReverseMap_t util::DatabaseUtil::fChannelReverseMap
private

Definition at line 90 of file DatabaseUtil.h.

std::string util::DatabaseUtil::fDBHostName
private

Definition at line 80 of file DatabaseUtil.h.

std::string util::DatabaseUtil::fDBName
private

Definition at line 81 of file DatabaseUtil.h.

std::string util::DatabaseUtil::fDBUser
private

Definition at line 82 of file DatabaseUtil.h.

std::string util::DatabaseUtil::fPassword
private

Definition at line 85 of file DatabaseUtil.h.

int util::DatabaseUtil::fPort
private

Definition at line 84 of file DatabaseUtil.h.

bool util::DatabaseUtil::fShouldConnect
private

Definition at line 87 of file DatabaseUtil.h.

std::string util::DatabaseUtil::fTableName
private

Definition at line 83 of file DatabaseUtil.h.

bool util::DatabaseUtil::fToughErrorTreatment
private

Definition at line 86 of file DatabaseUtil.h.


The documentation for this class was generated from the following files: