LMFDat.cc

Go to the documentation of this file.

#include "OnlineDB/EcalCondDB/interface/LMFDat.h"

#include <sstream>
#include <cmath>

using std::cout;
using std::endl;

LMFDat::LMFDat() : LMFUnique() {
  m_tableName = "";
  m_max = -1;
  _where = "";
  _wherePars.clear();
}

LMFDat::LMFDat(EcalDBConnection *c) : LMFUnique(c) {
  m_tableName = "";
  m_max = -1;
  _where = "";
  _wherePars.clear();
}

LMFDat::LMFDat(oracle::occi::Environment *env, oracle::occi::Connection *conn) : LMFUnique(env, conn) {
  m_tableName = "";
  m_max = -1;
  _where = "";
  _wherePars.clear();
}

std::string LMFDat::foreignKeyName() const { return "lmfRunIOV"; }

int LMFDat::getLMFRunIOVID() {
  int id = getInt(foreignKeyName());
  if (id == 0) {
    // try to get it from the list of foreign keys
    std::map<std::string, LMFUnique *>::iterator i = m_foreignKeys.find(foreignKeyName());
    if (i != m_foreignKeys.end()) {
      LMFRunIOV *iov = (LMFRunIOV *)(i->second);
      if (iov != nullptr) {
        id = iov->fetchID();
        setInt(foreignKeyName(), id);
      }
    }
  }
  return id;
}

LMFDat &LMFDat::setMaxDataToDump(int n) {
  m_max = n;
  return *this;
}

std::map<unsigned int, std::string> LMFDat::getReverseMap() const {
  std::map<unsigned int, std::string> m;
  std::map<std::string, unsigned int>::const_iterator i = m_keys.begin();
  std::map<std::string, unsigned int>::const_iterator e = m_keys.end();
  while (i != e) {
    m[i->second] = i->first;
    i++;
  }
  return m;
}

void LMFDat::dump() const { dump(0, m_max); }

void LMFDat::dump(int n) const { dump(n, m_max); }

void LMFDat::dump(int n, int max) const {
  LMFUnique::dump(n);
  int s = m_data.size();
  cout << "Stored data: " << s << endl;
  if (max >= 0) {
    std::map<int, std::vector<float> >::const_iterator p = m_data.begin();
    std::map<int, std::vector<float> >::const_iterator end = m_data.end();
    int c = 0;
    std::map<unsigned int, std::string> rm = getReverseMap();
    while ((p != end) && (c < max)) {
      int id = p->first;
      std::vector<float> x = p->second;
      cout << c << " -------------------------------------------" << endl;
      cout << "   ID: " << id << endl;
      for (unsigned int j = 0; j < x.size(); j++) {
        if (j % 4 == 0) {
          cout << endl << "   ";
        }
        cout << rm[j] << ":" << x[j] << "\t";
      }
      cout << endl;
      p++;
      c++;
    }
  }
}

std::string LMFDat::buildInsertSql() {
  // create the insert statement
  std::stringstream sql;
  sql << "INSERT INTO " + getTableName() + " VALUES (";
  unsigned int nParameters = m_keys.size() + 2;
  for (unsigned int i = 0; i < nParameters - 1; i++) {
    sql << ":" << i + 1 << ", ";
  }
  sql << ":" << nParameters << ")";
  std::string sqls = sql.str();
  if (m_debug) {
    cout << m_className << "::writeDB: " << sqls << endl;
  }
  return sqls;
}

std::string LMFDat::getIovIdFieldName() const { return "LMF_IOV_ID"; }

void LMFDat::setWhereClause(std::string where) {
  // to be used by experts to restrict the results of a query
  _where = where;
}

void LMFDat::setWhereClause(std::string where, const std::vector<std::string> &parameters) {
  // to be used by experts to restrict the results of a query
  // in this case the where clause can contains positional parameter,
  // identified as :/I, :/S, :/F for, respectively, integer, string or
  // float parameters. The parameters are all passed as strings
  // in parameters
  _wherePars = parameters;
  _where = where;
}

std::string LMFDat::buildSelectSql(int logic_id, int direction) {
  // create the insert statement
  // if logic_id = 0 select all channels for a given iov_id
  std::stringstream sql;
  int count = 1;
  if (getLMFRunIOVID() > 0) {
    if (_where.length() > 0) {
      // check if this is an expert query. If so, add a WHERE clause
      _where = " AND " + _where;
    }
    // in this case we are looking for all data collected during the same
    // IOV. There can be many logic_ids per IOV.
    sql << "SELECT * FROM CMS_ECAL_LASER_COND." << getTableName() << " WHERE " << getIovIdFieldName() << " = "
        << getLMFRunIOVID() << _where;
    // the expert query must be specified each time the expert makes the query
    // then empty it
    _where = "";
  } else {
    // in this case we are looking for a specific logic_id whose
    // data have been collected at a given time. There is only
    // one record in this case.
    std::string op = ">";
    std::string order = "ASC";
    if (direction < 0) {
      op = "<";
      order = "DESC";
    }
    sql << "SELECT * FROM (SELECT CMS_ECAL_LASER_COND." << getTableName() << ".* FROM CMS_ECAL_LASER_COND."
        << getTableName() << " JOIN LMF_RUN_IOV ON "
        << "LMF_RUN_IOV.LMF_IOV_ID = " << getTableName() << "." << getIovIdFieldName() << " "
        << "WHERE SUBRUN_START " << op << "= TO_DATE(:" << count;
    count++;
    sql << ", 'YYYY-MM-DD HH24:MI:SS') ORDER BY SUBRUN_START " << order << ") WHERE ROWNUM <= 1";
  }
  if (logic_id > 0) {
    sql << " AND LOGIC_ID = :" << count;
  }
  std::string sqls = sql.str();
  if (m_debug) {
    cout << m_className << "::buildSelectSqlDB: " << sqls << endl;
  }
  return sqls;
}

void LMFDat::getPrevious(LMFDat *dat) noexcept(false) { getNeighbour(dat, -1); }

void LMFDat::getNext(LMFDat *dat) noexcept(false) { getNeighbour(dat, +1); }

void LMFDat::getNeighbour(LMFDat *dat, int which) noexcept(false) {
  // there should be just one record in this case
  if (m_data.size() == 1) {
    dat->setConnection(this->getEnv(), this->getConn());
    int logic_id = m_data.begin()->first;
    Tm lastMeasuredOn = getSubrunStart();
    lastMeasuredOn += which;
    dat->fetch(logic_id, &lastMeasuredOn, which);
    dat->setMaxDataToDump(m_max);
  } else {
    dump();
    throw(std::runtime_error(m_className + "::getPrevious: Too many LOGIC_IDs in "
                                           "this object"));
  }
}

void LMFDat::fetch(const EcalLogicID &id) noexcept(false) { fetch(id.getLogicID()); }

void LMFDat::fetch(const EcalLogicID &id, const Tm &tm) noexcept(false) { fetch(id.getLogicID(), &tm, 1); }

void LMFDat::fetch(const EcalLogicID &id, const Tm &tm, int direction) noexcept(false) {
  setInt(foreignKeyName(), 0); /* set the LMF_IOV_ID to undefined */
  fetch(id.getLogicID(), &tm, direction);
}

void LMFDat::fetch() noexcept(false) { fetch(0); }

void LMFDat::fetch(int logic_id) noexcept(false) { fetch(logic_id, nullptr, 0); }

void LMFDat::fetch(int logic_id, const Tm &tm) noexcept(false) { fetch(logic_id, &tm, 1); }

void LMFDat::adjustParameters(int count, std::string &sql, Statement *stmt) {
  // adjust positional parameters and change them according to their
  // decalred type
  std::size_t nw = 0;
  std::size_t n = sql.find(':');
  for (int done = 1; done < count; done++) {
    // skip already bound variables
    n = sql.find(':', n + 1);
  }
  while (n != std::string::npos) {
    char type = sql.at(n + 1);
    if (type == 'S') {
      stmt->setString(nw + count, _wherePars[nw]);
      nw++;
    } else if (type == 'F') {
      stmt->setFloat(nw + count, atof(_wherePars[nw].c_str()));
      nw++;
    } else if (type == 'I') {
      stmt->setInt(nw + count, atoi(_wherePars[nw].c_str()));
      nw++;
    }
    n = sql.find(':', n + 1);
  }
}

void LMFDat::fetch(int logic_id, const Tm *timestamp, int direction) noexcept(false) {
  bool ok = check();
  if ((timestamp == nullptr) && (getLMFRunIOVID() == 0)) {
    throw(std::runtime_error(m_className + "::fetch: Cannot fetch data with "
                                           "timestamp = 0 and LMFRunIOV = 0"));
  }
  if (ok && isValid()) {
    if (m_debug) {
      std::cout << "[LMFDat] This object is valid..." << std::endl;
    }
    try {
      Statement *stmt = m_conn->createStatement();
      std::string sql = buildSelectSql(logic_id, direction);
      if (m_debug) {
        std::cout << "[LMFDat] Executing query " << std::endl;
        std::cout << "         " << sql << std::endl << std::flush;
      }
      if (logic_id == 0) {
        // get data for all crystals with a given timestamp
        stmt->setPrefetchRowCount(10000);
      }
      stmt->setSQL(sql);
      int count = 1;
      if (logic_id > 0) {
        if (timestamp != nullptr) {
          stmt->setString(count, timestamp->str());
          count++;
        }
        stmt->setInt(count++, logic_id);
      }
      adjustParameters(count, sql, stmt);
      ResultSet *rset = stmt->executeQuery();
      std::vector<float> x;
      int nData = m_keys.size();
      x.reserve(nData);
      while (rset->next() != 0) {
        for (int i = 0; i < nData; i++) {
          x.push_back(rset->getFloat(i + 3));
        }
        int id = rset->getInt(2);
        if (timestamp != nullptr) {
          setInt(foreignKeyName(), rset->getInt(1));
        }
        this->setData(id, x);
        x.clear();
      }
      stmt->setPrefetchRowCount(0);
      m_conn->terminateStatement(stmt);
    } catch (oracle::occi::SQLException &e) {
      throw(std::runtime_error(m_className + "::fetch: " + e.getMessage()));
    }
    m_ID = m_data.size();
  }
}

bool LMFDat::isValid() {
  bool ret = true;
  if (m_foreignKeys.find(foreignKeyName()) == m_foreignKeys.end()) {
    ret = false;
    m_Error += " Can't find lmfRunIOV within foreign keys.";
    if (m_debug) {
      cout << m_className << ": Foreign keys map size: " << m_foreignKeys.size() << endl;
    }
  }
  return ret;
}

std::map<int, std::vector<float> > LMFDat::fetchData() noexcept(false) {
  // see if any of the data is already in the database
  std::map<int, std::vector<float> > s = m_data;
  std::string sql =
      "SELECT LOGIC_ID FROM CMS_ECAL_LASER_COND." + getTableName() + " WHERE " + getIovIdFieldName() + " = :1";
  if (m_debug) {
    cout << m_className << ":: candidate data items to be written = " << s.size() << endl;
    cout << m_className << "   Executing " << sql;
    cout << " where " << getIovIdFieldName() << " = " << getLMFRunIOVID() << endl;
  }
  try {
    Statement *stmt = m_conn->createStatement();
    stmt->setSQL(sql);
    stmt->setInt(1, getLMFRunIOVID());
    stmt->setPrefetchRowCount(10000);
    ResultSet *rset = stmt->executeQuery();
    std::map<int, std::vector<float> >::iterator i = s.end();
    std::map<int, std::vector<float> >::iterator e = s.end();
    while (rset->next() != 0) {
      if (m_debug) {
        cout << m_className << ":: checking " << rset->getInt(1) << endl << std::flush;
      }
      i = s.find(rset->getInt(1));
      if (i != e) {
        s.erase(i);
      }
    }
    stmt->setPrefetchRowCount(0);
    m_conn->terminateStatement(stmt);
  } catch (oracle::occi::SQLException &e) {
    throw(std::runtime_error(m_className + "::fetchData:  " + e.getMessage()));
  }
  if (m_debug) {
    cout << m_className << ":: data items to write = " << s.size() << endl;
  }
  return s;
}

int LMFDat::writeDB() noexcept(false) {
  // first of all check if data already present
  if (m_debug) {
    cout << m_className << ": Writing foreign keys" << endl;
  }
  LMFUnique::writeForeignKeys();
  if (m_debug) {
    cout << m_className << ": Foreign keys written" << endl;
  }
  // write data on the database
  int ret = 0;
  std::map<int, std::vector<float> > data2write = fetchData();
  if (!data2write.empty()) {
    this->checkConnection();
    bool ok = check();
    // write
    if (ok && isValid()) {
      std::list<dvoid *> bufPointers;
      int nParameters = m_keys.size();
      int nData = data2write.size();
      if (m_debug) {
        cout << m_className << ": # data items = " << nData << endl;
        cout << m_className << ": # parameters = " << nParameters << endl;
      }
      int *iovid_vec = new int[nData];
      int *logicid_vec = new int[nData];
      int *intArray = new int[nData];
      float *floatArray = new float[nData];
      ub2 *intSize = new ub2[nData];
      ub2 *floatSize = new ub2[nData];
      size_t intTotalSize = sizeof(int) * nData;
      size_t floatTotalSize = sizeof(float) * nData;
      try {
        Statement *stmt = m_conn->createStatement();
        std::string sql = buildInsertSql();
        stmt->setSQL(sql);
        // build the array of the size of each column
        for (int i = 0; i < nData; i++) {
          intSize[i] = sizeof(int);
          floatSize[i] = sizeof(int);
        }
        // build the data array for first column: the same run iov id
        LMFRunIOV *runiov = (LMFRunIOV *)m_foreignKeys[foreignKeyName()];
        int iov_id = runiov->getID();
        std::map<int, std::vector<float> >::const_iterator b = data2write.begin();
        std::map<int, std::vector<float> >::const_iterator e = data2write.end();
        for (int i = 0; i < nData; i++) {
          iovid_vec[i] = iov_id;
        }
        stmt->setDataBuffer(1, (dvoid *)iovid_vec, oracle::occi::OCCIINT, sizeof(iovid_vec[0]), intSize);
        // build the data array for second column: the logic ids
        int c = 0;
        while (b != e) {
          int id = b->first;
          logicid_vec[c++] = id;
          b++;
        }
        stmt->setDataBuffer(2, (dvoid *)logicid_vec, oracle::occi::OCCIINT, sizeof(logicid_vec[0]), intSize);
        // for each column build the data array
        oracle::occi::Type type = oracle::occi::OCCIFLOAT;
        for (int i = 0; i < nParameters; i++) {
          b = data2write.begin();
          // loop on all logic ids
          c = 0;
          while (b != e) {
            std::vector<float> x = b->second;
            if (m_type[i] == "INT") {
              intArray[c] = (int)rint(x[i]);
            } else if ((m_type[i] == "FLOAT") || (m_type[i] == "NUMBER")) {
              floatArray[c] = x[i];
            } else {
              throw(std::runtime_error("ERROR: LMFDat::writeDB: unsupported type"));
            }
            c++;
            b++;
          }
          // copy data into a "permanent" buffer
          dvoid *buffer;
          type = oracle::occi::OCCIINT;
          ub2 *sizeArray = intSize;
          int size = sizeof(intArray[0]);
          if ((m_type[i] == "FLOAT") || (m_type[i] == "NUMBER")) {
            buffer = (dvoid *)malloc(sizeof(float) * nData);
            memcpy(buffer, floatArray, floatTotalSize);
            type = oracle::occi::OCCIFLOAT;
            sizeArray = floatSize;
            size = sizeof(floatArray[0]);
          } else {
            buffer = (dvoid *)malloc(sizeof(int) * nData);
            memcpy(buffer, intArray, intTotalSize);
          }
          bufPointers.push_back(buffer);
          if (m_debug) {
            for (int k = 0; ((k < nData) && (k < m_max)); k++) {
              cout << m_className << ": === Index=== " << k << endl;
              cout << m_className << ": RUN_IOV_ID = " << iovid_vec[k] << endl;
              cout << m_className << ": LOGIC_ID = " << logicid_vec[k] << endl;
              cout << m_className << ": FIELD " << i << ": " << ((float *)(buffer))[k] << endl;
            }
          }
          stmt->setDataBuffer(i + 3, buffer, type, size, sizeArray);
        }
        stmt->executeArrayUpdate(nData);
        delete[] intArray;
        delete[] floatArray;
        delete[] intSize;
        delete[] floatSize;
        delete[] logicid_vec;
        delete[] iovid_vec;
        std::list<dvoid *>::const_iterator bi = bufPointers.begin();
        std::list<dvoid *>::const_iterator be = bufPointers.end();
        while (bi != be) {
          free(*bi);
          bi++;
        }
        m_conn->commit();
        m_conn->terminateStatement(stmt);
        ret = nData;
      } catch (oracle::occi::SQLException &e) {
        debug();
        setMaxDataToDump(nData);
        // get the Foreign Key
        LMFRunIOV *runiov = (LMFRunIOV *)m_foreignKeys[foreignKeyName()];
        int iov_id = runiov->getID();
        std::cout << "==== This object refers to IOV " << iov_id << std::endl;
        dump();
        m_conn->rollback();
        throw(std::runtime_error(m_className + "::writeDB: " + e.getMessage()));
      }
    } else {
      cout << m_className << "::writeDB: Cannot write because " << m_Error << endl;
      dump();
    }
  }
  return ret;
}

void LMFDat::getKeyTypes() noexcept(false) {
  m_type.reserve(m_keys.size());
  for (unsigned int i = 0; i < m_keys.size(); i++) {
    m_type.push_back("");
  }
  // get the description of the table
  std::string sql = "";
  try {
    Statement *stmt = m_conn->createStatement();
    sql = "SELECT * FROM TABLE(CMS_ECAL_LASER_COND.LMF_TAB_COLS(:1, :2))";
    /*
    sql = "SELECT COLUMN_NAME, DATA_TYPE FROM " 
      "USER_TAB_COLS WHERE TABLE_NAME = '" + getTableName() + "' " 
      "AND COLUMN_NAME != '" + getIovIdFieldName() +  "' AND COLUMN_NAME != " 
      "'LOGIC_ID'";
    */
    stmt->setSQL(sql);
    stmt->setString(1, getTableName());
    stmt->setString(2, getIovIdFieldName());
    ResultSet *rset = stmt->executeQuery();
    while (rset->next() != 0) {
      std::string name = rset->getString(1);
      std::string t = rset->getString(2);
      m_type[m_keys[name]] = t;
    }
    m_conn->terminateStatement(stmt);
  } catch (oracle::occi::SQLException &e) {
    throw(std::runtime_error(m_className + "::getKeyTypes: " + e.getMessage() + " [" + sql + "]"));
  }
}

bool LMFDat::check() {
  // check that everything has been correctly setup
  bool ret = true;
  m_Error = "";
  // first of all we need to check that the class name has been set
  if (m_className == "LMFUnique") {
    m_Error = "class name not set ";
    ret = false;
  }
  //then check that the table name has been set
  if (getTableName().empty()) {
    m_Error += "table name not set ";
    ret = false;
  }
  // fill key types if not yet done
  if (m_type.size() != m_keys.size()) {
    getKeyTypes();
    if (m_type.size() != m_keys.size()) {
      m_Error += "key size does not correspond to table definition";
      ret = false;
    }
  }
  return ret;
}

/* unsafe methods */

std::vector<float> LMFDat::getData(int id) {
  std::vector<float> ret;
  if (m_data.find(id) != m_data.end()) {
    ret = m_data[id];
  }
  return ret;
}

std::vector<float> LMFDat::operator[](int id) { return getData(id); }

std::vector<float> LMFDat::getData(const EcalLogicID &id) { return getData(id.getLogicID()); }

/* safe methods */

bool LMFDat::getData(int id, std::vector<float> &ret) {
  bool retval = false;
  if (m_data.find(id) != m_data.end()) {
    ret = m_data[id];
    retval = true;
  }
  return retval;
}

bool LMFDat::getData(const EcalLogicID &id, std::vector<float> &ret) { return getData(id.getLogicID(), ret); }

/* all data */

std::map<int, std::vector<float> > LMFDat::getData() { return m_data; }

/* unsafe */

float LMFDat::getData(int id, unsigned int k) { return m_data[id][k]; }

float LMFDat::getData(const EcalLogicID &id, unsigned int k) { return getData(id.getLogicID(), k); }

float LMFDat::getData(const EcalLogicID &id, const std::string &key) { return getData(id.getLogicID(), m_keys[key]); }

float LMFDat::getData(int id, const std::string &key) { return getData(id, m_keys[key]); }

/* safe */

bool LMFDat::getData(int id, unsigned int k, float &ret) {
  bool retval = false;
  std::vector<float> v;
  retval = getData(id, v);
  if ((retval) && (v.size() > k)) {
    ret = v[k];
    retval = true;
  } else {
    retval = false;
  }
  return retval;
}

bool LMFDat::getData(const EcalLogicID &id, unsigned int k, float &ret) { return getData(id.getLogicID(), k, ret); }

bool LMFDat::getData(int id, const std::string &key, float &ret) {
  bool retval = false;
  if (m_keys.find(key) != m_keys.end()) {
    retval = getData(id, m_keys[key], ret);
  }
  return retval;
}

bool LMFDat::getData(const EcalLogicID &id, const std::string &key, float &ret) {
  return getData(id.getLogicID(), key, ret);
}