AEMDBInterface.cxx

Go to the documentation of this file.

////////////////////////////////////////////////////////////////////////
/// $Id: AEMDBInterface.cxx,v 1.6 2005/08/15 17:31:32 hmeyer Exp $
///
/// Class which defines inteface to database, taken from RunControl
///
/// hmeyer@fnal.gov
////////////////////////////////////////////////////////////////////////

#include "AEMDBInterface.h"

#include <vector>
#include <sstream>
#include <iostream>
#include <cstring>
#include <cstdlib>
#include <ctime>
#include <string>

extern "C" {
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
}

//----------------------------------------------------------------------

DBRunEntry::DBRunEntry()
{
  run = 0; nsubrun = 0; nevent = 0; momentum = 0;
  epochstart = 0; epochend = 0;
  nspill = 0; nemptyspill = 0; nbeamspill = 0;
  for (int i=0; i!=20; ++i) {
    trigcount[i]=0;
  }
  goodrun = 0;

  fRunTime = 0;
  fSpillLength = 0.;
  fSpills = 0;
  fSpillTime = 0.;
  fSpillEvents = 0;
  fDAQRate = 0.;
}

//----------------------------------------------------------------------

void DBRunEntry::dump()
{
  std::cout << "Run number "<< run << " has these entries:\n nsubrun : "
            << nsubrun << "\n nevent : " << nevent
            << "\n target :" << target
            << "\n momentum : " << momentum
            << "\n" << datestart << " to " << dateend
            << "\n nspill : " << nspill
            << "\n nemptyspill : " << nemptyspill
            << "\n goodrun : " << goodrun 
            << std::endl;
  for (int i=0; i!=16; ++i) {
    std::cout << trigcount[i] << ":";
  }
  std::cout << "\n DAQ Rate : " << fDAQRate << std::endl;
}

//----------------------------------------------------------------------

#define CheckDBConn() \
   if (!fDBConn || (PQstatus(fDBConn) != CONNECTION_OK)){ \
      std::ostringstream __str; \
      __str << __FILE__ << ":" << __LINE__ \
            << " -- No DB connection: " \
            << PQerrorMessage(fDBConn); \
      throw(__str.str().c_str()); \
   }


//----------------------------------------------------------------------

AEMDBInterface::AEMDBInterface(std::string const Host) : fDBHost(Host) 
{
  Open();
}

//----------------------------------------------------------------------

AEMDBInterface::~AEMDBInterface()
{
  PQfinish(fDBConn);
}

//----------------------------------------------------------------------
/// Opens connection to database, throws an exception if errors 
/// occurred when doing so
///
void AEMDBInterface::Open()
{
  std::ostringstream str;

  str << "dbname=runs host=" << fDBHost << " user=mippdbread";

  fDBConn = PQconnectdb(str.str().c_str());

  CheckDBConn();
}

//----------------------------------------------------------------------
///
/// Returns the run number of the run in progress or if no run is
/// currently running, returns the number of the run that has finished.
///
int AEMDBInterface::GetRunNumber()
{
  CheckDBConn();

  PGresult* res = 
    ExecQuery("SELECT run,status FROM runs ORDER BY run DESC LIMIT 1");

  if (PQntuples(res) != 1) {
    throw("Invalid number of rows in Runs table");
  }
  int run = atoi(PQgetvalue(res, 0, 0));

  PQclear(res);

  return run;
}

//----------------------------------------------------------------------
///
/// Returns the number of subruns for run fRun
///
int AEMDBInterface::GetnSubrun(int const fRun)
{
  CheckDBConn();

  std::ostringstream str;

  str << "SELECT nsubrun FROM runs WHERE run=" << fRun;

  PGresult* res = 
    ExecQuery(str.str().c_str());

  if (PQntuples(res) != 1) {
    throw("Invalid number of rows in Runs table");
  }
  int nsubrun = atoi(PQgetvalue(res, 0, 0));

  PQclear(res);

  return nsubrun;
}

//----------------------------------------------------------------------
///
/// Returns the number of spills with beam for run fRun
///
int AEMDBInterface::GetnSpill(int const fRun)
{
  CheckDBConn();

  std::ostringstream str;

  str << "SELECT nspill-nemptyspill FROM trigstat WHERE run=" << fRun;

  PGresult* res = 
    ExecQuery(str.str().c_str());

  int nspill;
  if (PQntuples(res) == 0) {
    nspill = 0;
  }
  else if (PQntuples(res) == 1) {
    nspill  = atoi(PQgetvalue(res, 0, 0));
  }
  else {
    throw("Invalid number of rows in Runs table");
  }

  PQclear(res);

  return nspill;
}

//----------------------------------------------------------------------
///
/// Returns the number of events with beam for run fRun
///
int AEMDBInterface::GetnEvent(int const fRun)
{
  CheckDBConn();

  std::ostringstream str;

  str << "SELECT trigcount[1]+trigcount[5]+trigcount[6]+trigcount[7]+trigcount[8]+trigcount[9]+trigcount[10]+trigcount[11]+trigcount[12] FROM trigstat WHERE run=" << fRun;

  PGresult* res = 
    ExecQuery(str.str().c_str());

  int nevt;
  if (PQntuples(res) == 0) {
    nevt = 0;
  }
  else if (PQntuples(res) == 1) {
    nevt  = atoi(PQgetvalue(res, 0, 0));
  }
  else {
    throw("Invalid number of rows in Runs table");
  }

  PQclear(res);

  return nevt;
}

//----------------------------------------------------------------------
///
/// Returns run information for run fRun
///
DBRunEntry AEMDBInterface::GetRunEntry(int const fRun)
{
  DBRunEntries fDBREs = GetRunEntry(fRun, fRun);
  DBRunEntry fDBRE;

  if (fDBREs.size() != 0) {
    fDBRE = fDBREs[0];
  }
  return fDBRE;
}

//----------------------------------------------------------------------
///
/// Returns run information for a range of runs
///
DBRunEntries AEMDBInterface::GetRunEntry(int const fRunMin, int const fRunMax)
{
  CheckDBConn();

  std::ostringstream str;

  str << "SELECT nsubrun,nevent,target,momentum,datestart,dateend,DATE_PART('epoch',datestart),DATE_PART('epoch',dateend),nspill,nemptyspill,goodrun,trigcount,runs.run FROM runs,trigstat WHERE trigstat.run=runs.run and runs.run>=" << fRunMin << " and runs.run<=" << fRunMax << "ORDER BY runs.run";

  PGresult* res = ExecQuery(str.str().c_str());

  std::vector<DBRunEntry> fDBRuns;

  if (fRunMax-fRunMin != PQntuples(res)+1) {
    std::cout << "Warning: "<<fRunMax-fRunMin-PQntuples(res)+1<<
      "  runs in this range have no DB entry!!!"<<std::endl;
  }

  PutQueryToVector(res,fDBRuns);

  PQclear(res);

  return fDBRuns;
}

//----------------------------------------------------------------------
///
/// Returns run information for one week of runs starting at fDateString
///
long AEMDBInterface::GetRunEntry(std::string const fDateStart, DBRunEntries& fDBRuns)
{
  CheckDBConn();

  std::ostringstream st;

  st << "SELECT DATE_PART('epoch','"<<fDateStart<<"'::date)";

  PGresult* r = ExecQuery(st.str().c_str());
  
  long epoch_start = atoi(PQgetvalue(r, 0, 0));
  long epoch_end = epoch_start+(7*24*60*60);

  PQclear(r);

  std::ostringstream str;

  str << "SELECT nsubrun,nevent,target,momentum,datestart,dateend,DATE_PART('epoch',datestart),DATE_PART('epoch',dateend),nspill,nemptyspill,goodrun,trigcount,runs.run FROM runs,trigstat WHERE trigstat.run=runs.run and DATE_PART('epoch',runs.dateend)>=" << epoch_start << " and DATE_PART('epoch',runs.datestart)<=" << epoch_end << "ORDER BY runs.run";

  PGresult* res = ExecQuery(str.str().c_str());

  PutQueryToVector(res,fDBRuns);

  PQclear(res);
  
  return epoch_start;
}
//----------------------------------------------------------------------
///
/// Returns run information for total run
///
void AEMDBInterface::GetRunEntry(DBRunEntries& fDBRuns)
{
  CheckDBConn();

  std::ostringstream str;

  str << "SELECT nsubrun,nevent,target,momentum,datestart,dateend,DATE_PART('epoch',datestart),DATE_PART('epoch',dateend),nspill,nemptyspill,goodrun,trigcount,runs.run FROM runs,trigstat WHERE trigstat.run=runs.run ORDER BY runs.run";

  PGresult* res = ExecQuery(str.str().c_str());

  PutQueryToVector(res,fDBRuns);

  PQclear(res);
  
  return;
}

//----------------------------------------------------------------------
///
/// copy run information from query result
///
void AEMDBInterface::PutQueryToVector(PGresult* res, DBRunEntries& vec)
{
  for (int i=0; i!=PQntuples(res); ++i) {
    DBRunEntry fDBRE;
    fDBRE.run        = atoi(PQgetvalue(res, i, 12));
    fDBRE.nsubrun    = atoi(PQgetvalue(res, i, 0));
    fDBRE.nevent     = atoi(PQgetvalue(res, i, 1));
    fDBRE.target     = std::string(PQgetvalue(res, i, 2));
    fDBRE.momentum   = atoi(PQgetvalue(res, i, 3));
    fDBRE.datestart  = std::string(PQgetvalue(res, i, 4));
    fDBRE.dateend    = std::string(PQgetvalue(res, i, 5));
    fDBRE.epochstart = atoi(PQgetvalue(res, i, 6));
    fDBRE.epochend   = atoi(PQgetvalue(res, i, 7));
    fDBRE.nspill     = atoi(PQgetvalue(res, i, 8));
    fDBRE.nemptyspill= atoi(PQgetvalue(res, i, 9));
    fDBRE.goodrun    = atoi(PQgetvalue(res, i, 10));
    std::string trigstr(PQgetvalue(res, i, 11));
    int elem = 0; // Element number
    std::string::size_type pos = trigstr.find("{");
    // If we couldn't find the bracket, then it's not a "correct"
    // representation of array
    ++pos;
    while (pos != std::string::npos) {
      std::string::size_type endPos = trigstr.find_first_of(",}", pos);
      if (endPos == std::string::npos) break;
      fDBRE.trigcount[elem++] = atoi(trigstr.substr(pos, endPos - pos).c_str());
      pos = endPos + 1;
    }

    fDBRE.fRunTime = fDBRE.epochend-fDBRE.epochstart;
    if (fDBRE.run <=14141) {
      fDBRE.fSpillLength = .6;
    }
    else {
      fDBRE.fSpillLength = 4.;
    }
    fDBRE.fSpills = fDBRE.nspill-fDBRE.nemptyspill;
    fDBRE.fSpillTime = fDBRE.fSpillLength * fDBRE.fSpills;
    fDBRE.fSpillEvents = fDBRE.trigcount[0]+fDBRE.trigcount[4]+fDBRE.trigcount[5]+fDBRE.trigcount[6]+fDBRE.trigcount[7]+fDBRE.trigcount[8]+fDBRE.trigcount[9]+fDBRE.trigcount[10]+fDBRE.trigcount[11];
    if (fDBRE.fSpillTime != 0.) {
      fDBRE.fDAQRate = fDBRE.fSpillEvents/fDBRE.fSpillTime;
    }
    else {
      fDBRE.fDAQRate = 0.;
    }

    vec.push_back(fDBRE);
  }
  return;
}

//----------------------------------------------------------------------
/// 
/// Wrap doing queries so as to clean up code a little
/// Throws a const char* exception if query didn't succeed.
/// User has to clean up the returned query by calling PQclear(res);
///
PGresult* AEMDBInterface::ExecQuery(const char* query)
{
  PGresult* res = PQexec(fDBConn, query);
  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    std::ostringstream str;
    str << "AEMDBInterface: couldn't execute query [" 
    << query << "]: " << PQresultErrorMessage(res);
    PQclear(res);
    throw(str.str().c_str());
  }
  return res;
}

//----------------------------------------------------------------------
/// 
/// Wrap execution of SQL commands (not queries!)
/// so as to clean up code a little, throws a const char* exception
/// if command didn't succeed
///
void AEMDBInterface::ExecCommand(const char* command)
{
  PGresult* res = PQexec(fDBConn, command);
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    std::ostringstream str;
    str << "AEMDBInterface: couldn't execute command [" 
    << command << "]: " << PQresultErrorMessage(res);
    PQclear(res);
    throw(str.str().c_str());
  }
  PQclear(res);
}

---