00001 #include "OnlineDB/EcalCondDB/interface/LMFDat.h"
00002
00003 #include <sstream>
00004 #include <math.h>
00005
00006 using std::cout;
00007 using std::endl;
00008
00009 LMFDat::LMFDat() : LMFUnique() {
00010 m_tableName = "";
00011 m_max = -1;
00012 _where = "";
00013 _wherePars.clear();
00014 }
00015
00016 LMFDat::LMFDat(EcalDBConnection *c) : LMFUnique(c) {
00017 m_tableName = "";
00018 m_max = -1;
00019 _where = "";
00020 _wherePars.clear();
00021 }
00022
00023 LMFDat::LMFDat(oracle::occi::Environment* env,
00024 oracle::occi::Connection* conn) : LMFUnique(env, conn) {
00025 m_tableName = "";
00026 m_max = -1;
00027 _where = "";
00028 _wherePars.clear();
00029 }
00030
00031 std::string LMFDat::foreignKeyName() const {
00032 return "lmfRunIOV";
00033 }
00034
00035 int LMFDat::getLMFRunIOVID() {
00036 int id = getInt(foreignKeyName());
00037 if (id == 0) {
00038
00039 std::map<std::string, LMFUnique*>::iterator i =
00040 m_foreignKeys.find(foreignKeyName());
00041 if (i != m_foreignKeys.end()) {
00042 LMFRunIOV *iov = (LMFRunIOV*)(i->second);
00043 if (iov != NULL) {
00044 id = iov->fetchID();
00045 setInt(foreignKeyName(), id);
00046 }
00047 }
00048 }
00049 return id;
00050 }
00051
00052 LMFDat& LMFDat::setMaxDataToDump(int n) {
00053 m_max = n;
00054 return *this;
00055 }
00056
00057 std::map<unsigned int, std::string> LMFDat::getReverseMap() const {
00058 std::map<unsigned int, std::string> m;
00059 std::map<std::string, unsigned int>::const_iterator i = m_keys.begin();
00060 std::map<std::string, unsigned int>::const_iterator e = m_keys.end();
00061 while (i != e) {
00062 m[i->second] = i->first;
00063 i++;
00064 }
00065 return m;
00066 }
00067
00068 void LMFDat::dump() const {
00069 dump(0, m_max);
00070 }
00071
00072 void LMFDat::dump(int n) const {
00073 dump(n, m_max);
00074 }
00075
00076 void LMFDat::dump(int n, int max) const {
00077 LMFUnique::dump(n);
00078 int s = m_data.size();
00079 cout << "Stored data: " << s << endl;
00080 if (max >= 0) {
00081 std::map<int, std::vector<float> >::const_iterator p = m_data.begin();
00082 std::map<int, std::vector<float> >::const_iterator end = m_data.end();
00083 int c = 0;
00084 std::map<unsigned int, std::string> rm = getReverseMap();
00085 while ((p != end) && (c < max)) {
00086 int id = p->first;
00087 std::vector<float> x = p->second;
00088 cout << c << " -------------------------------------------" << endl;
00089 cout << " ID: " << id << endl;
00090 for (unsigned int j = 0; j < x.size(); j++) {
00091 if (j % 4 == 0) {
00092 cout << endl << " ";
00093 }
00094 cout << rm[j] << ":" << x[j] << "\t";
00095 }
00096 cout << endl;
00097 p++;
00098 c++;
00099 }
00100 }
00101 }
00102
00103 std::string LMFDat::buildInsertSql() {
00104
00105 std::stringstream sql;
00106 sql << "INSERT INTO " + getTableName() + " VALUES (";
00107 unsigned int nParameters = m_keys.size() + 2;
00108 for (unsigned int i = 0; i < nParameters - 1; i++) {
00109 sql << ":" << i + 1 << ", ";
00110 }
00111 sql << ":" << nParameters << ")";
00112 std::string sqls = sql.str();
00113 if (m_debug) {
00114 cout << m_className << "::writeDB: " << sqls << endl;
00115 }
00116 return sqls;
00117 }
00118
00119 std::string LMFDat::getIovIdFieldName() const {
00120 return "LMF_IOV_ID";
00121 }
00122
00123 void LMFDat::setWhereClause(std::string where) {
00124
00125 _where = where;
00126 }
00127
00128 void LMFDat::setWhereClause(std::string where,
00129 std::vector<std::string> parameters) {
00130
00131
00132
00133
00134
00135 _wherePars = parameters;
00136 _where = where;
00137 }
00138
00139 std::string LMFDat::buildSelectSql(int logic_id, int direction) {
00140
00141
00142 std::stringstream sql;
00143 int count = 1;
00144 if (getLMFRunIOVID() > 0) {
00145 if (_where.length() > 0) {
00146
00147 _where = " AND " + _where;
00148 }
00149
00150
00151 sql << "SELECT * FROM CMS_ECAL_LASER_COND." << getTableName() << " WHERE "
00152 << getIovIdFieldName() << " = " << getLMFRunIOVID()
00153 << _where;
00154
00155
00156 _where = "";
00157 } else {
00158
00159
00160
00161 std::string op = ">";
00162 std::string order = "ASC";
00163 if (direction < 0) {
00164 op = "<";
00165 order = "DESC";
00166 }
00167 sql << "SELECT * FROM (SELECT CMS_ECAL_LASER_COND."
00168 << getTableName() << ".* FROM CMS_ECAL_LASER_COND."
00169 << getTableName()
00170 << " JOIN LMF_RUN_IOV ON "
00171 << "LMF_RUN_IOV.LMF_IOV_ID = "
00172 << getTableName() << "." << getIovIdFieldName() << " "
00173 << "WHERE SUBRUN_START " << op << "= TO_DATE(:" << count;
00174 count++;
00175 sql << ", 'YYYY-MM-DD HH24:MI:SS') ORDER BY SUBRUN_START "
00176 << order << ") WHERE ROWNUM <= 1";
00177 }
00178 if (logic_id > 0) {
00179 sql << " AND LOGIC_ID = :" << count;
00180 }
00181 std::string sqls = sql.str();
00182 if (m_debug) {
00183 cout << m_className << "::buildSelectSqlDB: " << sqls << endl;
00184 }
00185 return sqls;
00186 }
00187
00188 void LMFDat::getPrevious(LMFDat *dat)
00189 throw(std::runtime_error)
00190 {
00191 getNeighbour(dat, -1);
00192 }
00193
00194 void LMFDat::getNext(LMFDat *dat)
00195 throw(std::runtime_error)
00196 {
00197 getNeighbour(dat, +1);
00198 }
00199
00200 void LMFDat::getNeighbour(LMFDat *dat, int which)
00201 throw(std::runtime_error)
00202 {
00203
00204 if (m_data.size() == 1) {
00205 dat->setConnection(this->getEnv(), this->getConn());
00206 int logic_id = m_data.begin()->first;
00207 Tm lastMeasuredOn = getSubrunStart();
00208 lastMeasuredOn += which;
00209 dat->fetch(logic_id, &lastMeasuredOn, which);
00210 dat->setMaxDataToDump(m_max);
00211 } else {
00212 dump();
00213 throw(std::runtime_error(m_className + "::getPrevious: Too many LOGIC_IDs in "
00214 "this object"));
00215 }
00216 }
00217
00218 void LMFDat::fetch(const EcalLogicID &id)
00219 throw(std::runtime_error)
00220 {
00221 fetch(id.getLogicID());
00222 }
00223
00224 void LMFDat::fetch(const EcalLogicID &id, const Tm &tm)
00225 throw(std::runtime_error)
00226 {
00227 fetch(id.getLogicID(), &tm, 1);
00228 }
00229
00230 void LMFDat::fetch(const EcalLogicID &id, const Tm &tm, int direction)
00231 throw(std::runtime_error)
00232 {
00233 setInt(foreignKeyName(), 0);
00234 fetch(id.getLogicID(), &tm, direction);
00235 }
00236
00237 void LMFDat::fetch()
00238 throw(std::runtime_error)
00239 {
00240 fetch(0);
00241 }
00242
00243 void LMFDat::fetch(int logic_id)
00244 throw(std::runtime_error)
00245 {
00246 fetch(logic_id, NULL, 0);
00247 }
00248
00249 void LMFDat::fetch(int logic_id, const Tm &tm)
00250 throw(std::runtime_error)
00251 {
00252 fetch(logic_id, &tm, 1);
00253 }
00254
00255 void LMFDat::adjustParameters(int count, std::string &sql,
00256 Statement *stmt) {
00257
00258
00259 std::size_t nw = 0;
00260 std::size_t n = sql.find(":");
00261 for (int done = 1; done < count; done++) {
00262
00263 n = sql.find(":", n + 1);
00264 }
00265 while (n != std::string::npos) {
00266 char type = sql.at(n + 1);
00267 if (type == 'S') {
00268 stmt->setString(nw + count, _wherePars[nw]);
00269 nw++;
00270 } else if (type == 'F') {
00271 stmt->setFloat(nw + count, atof(_wherePars[nw].c_str()));
00272 nw++;
00273 } else if (type == 'I') {
00274 stmt->setInt(nw + count, atoi(_wherePars[nw].c_str()));
00275 nw++;
00276 }
00277 n = sql.find(":", n + 1);
00278 }
00279 }
00280
00281 void LMFDat::fetch(int logic_id, const Tm *timestamp, int direction)
00282 throw(std::runtime_error)
00283 {
00284 bool ok = check();
00285 if ((timestamp == NULL) && (getLMFRunIOVID() == 0)) {
00286 throw(std::runtime_error(m_className + "::fetch: Cannot fetch data with "
00287 "timestamp = 0 and LMFRunIOV = 0"));
00288 }
00289 if (ok && isValid()) {
00290 if (m_debug) {
00291 std::cout << "[LMFDat] This object is valid..." << std::endl;
00292 }
00293 try {
00294 Statement * stmt = m_conn->createStatement();
00295 std::string sql = buildSelectSql(logic_id, direction);
00296 if (m_debug) {
00297 std::cout << "[LMFDat] Executing query " << std::endl;
00298 std::cout << " " << sql << std::endl << std::flush;
00299 }
00300 if (logic_id == 0) {
00301
00302 stmt->setPrefetchRowCount(10000);
00303 }
00304 stmt->setSQL(sql);
00305 int count = 1;
00306 if (logic_id > 0) {
00307 if (timestamp != NULL) {
00308 stmt->setString(count, timestamp->str());
00309 count++;
00310 }
00311 stmt->setInt(count++, logic_id);
00312 }
00313 adjustParameters(count, sql, stmt);
00314 ResultSet *rset = stmt->executeQuery();
00315 std::vector<float> x;
00316 int nData = m_keys.size();
00317 x.reserve(nData);
00318 while (rset->next() != 0) {
00319 for (int i = 0; i < nData; i++) {
00320 x.push_back(rset->getFloat(i + 3));
00321 }
00322 int id = rset->getInt(2);
00323 if (timestamp != NULL) {
00324 setInt(foreignKeyName(), rset->getInt(1));
00325 }
00326 this->setData(id, x);
00327 x.clear();
00328 }
00329 stmt->setPrefetchRowCount(0);
00330 m_conn->terminateStatement(stmt);
00331 }
00332 catch (oracle::occi::SQLException &e) {
00333 throw(std::runtime_error(m_className + "::fetch: " + e.getMessage()));
00334 }
00335 m_ID = m_data.size();
00336 }
00337 }
00338
00339 bool LMFDat::isValid() {
00340 bool ret = true;
00341 if (m_foreignKeys.find(foreignKeyName()) == m_foreignKeys.end()) {
00342 ret = false;
00343 m_Error += " Can't find lmfRunIOV within foreign keys.";
00344 if (m_debug) {
00345 cout << m_className << ": Foreign keys map size: " << m_foreignKeys.size()
00346 << endl;
00347 }
00348 }
00349 return ret;
00350 }
00351
00352 std::map<int, std::vector<float> > LMFDat::fetchData()
00353 throw(std::runtime_error)
00354 {
00355
00356 std::map<int, std::vector<float> > s = m_data;
00357 std::string sql = "SELECT LOGIC_ID FROM CMS_ECAL_LASER_COND." +
00358 getTableName() + " WHERE "
00359 + getIovIdFieldName() + " = :1";
00360 if (m_debug) {
00361 cout << m_className << ":: candidate data items to be written = "
00362 << s.size() << endl;
00363 cout << m_className << " Executing " << sql;
00364 cout << " where " << getIovIdFieldName() << " = "
00365 << getLMFRunIOVID() << endl;
00366 }
00367 try {
00368 Statement* stmt = m_conn->createStatement();
00369 stmt->setSQL(sql);
00370 stmt->setInt(1, getLMFRunIOVID());
00371 stmt->setPrefetchRowCount(10000);
00372 ResultSet* rset = stmt->executeQuery();
00373 std::map<int, std::vector<float> >::iterator i = s.end();
00374 std::map<int, std::vector<float> >::iterator e = s.end();
00375 while (rset->next() != 0) {
00376 if (m_debug) {
00377 cout << m_className << ":: checking " << rset->getInt(1) << endl
00378 << std::flush;
00379 }
00380 i = s.find(rset->getInt(1));
00381 if (i != e) {
00382 s.erase(i);
00383 }
00384 }
00385 stmt->setPrefetchRowCount(0);
00386 m_conn->terminateStatement(stmt);
00387 }
00388 catch (oracle::occi::SQLException &e) {
00389 throw(std::runtime_error(m_className + "::fetchData: "+e.getMessage()));
00390 }
00391 if (m_debug) {
00392 cout << m_className << ":: data items to write = "
00393 << s.size() << endl;
00394 }
00395 return s;
00396 }
00397
00398 int LMFDat::writeDB()
00399 throw(std::runtime_error)
00400 {
00401
00402 if (m_debug) {
00403 cout << m_className << ": Writing foreign keys" << endl;
00404 }
00405 LMFUnique::writeForeignKeys();
00406 if (m_debug) {
00407 cout << m_className << ": Foreign keys written" << endl;
00408 }
00409
00410 int ret = 0;
00411 std::map<int, std::vector<float> > data2write = fetchData();
00412 if (data2write.size() > 0) {
00413 this->checkConnection();
00414 bool ok = check();
00415
00416 if (ok && isValid()) {
00417 std::list<dvoid *> bufPointers;
00418 int nParameters = m_keys.size();
00419 int nData = data2write.size();
00420 if (m_debug) {
00421 cout << m_className << ": # data items = " << nData << endl;
00422 cout << m_className << ": # parameters = " << nParameters << endl;
00423 }
00424 int * iovid_vec = new int[nData];
00425 int * logicid_vec = new int[nData];
00426 int *intArray = new int[nData];
00427 float *floatArray = new float[nData];
00428 ub2 * intSize = new ub2[nData];
00429 ub2 * floatSize = new ub2[nData];
00430 size_t intTotalSize = sizeof(int)*nData;
00431 size_t floatTotalSize = sizeof(float)*nData;
00432 try {
00433 Statement * stmt = m_conn->createStatement();
00434 std::string sql = buildInsertSql();
00435 stmt->setSQL(sql);
00436
00437 for (int i = 0; i < nData; i++) {
00438 intSize[i] = sizeof(int);
00439 floatSize[i] = sizeof(int);
00440 }
00441
00442 LMFRunIOV *runiov = (LMFRunIOV*)m_foreignKeys[foreignKeyName()];
00443 int iov_id = runiov->getID();
00444 std::map<int, std::vector<float> >::const_iterator b = data2write.begin();
00445 std::map<int, std::vector<float> >::const_iterator e = data2write.end();
00446 for (int i = 0; i < nData; i++) {
00447 iovid_vec[i] = iov_id;
00448 }
00449 stmt->setDataBuffer(1, (dvoid*)iovid_vec, oracle::occi::OCCIINT,
00450 sizeof(iovid_vec[0]), intSize);
00451
00452 int c = 0;
00453 while (b != e) {
00454 int id = b->first;
00455 logicid_vec[c++] = id;
00456 b++;
00457 }
00458 stmt->setDataBuffer(2, (dvoid*)logicid_vec, oracle::occi::OCCIINT,
00459 sizeof(logicid_vec[0]), intSize);
00460
00461 oracle::occi::Type type = oracle::occi::OCCIFLOAT;
00462 for (int i = 0; i < nParameters; i++) {
00463 b = data2write.begin();
00464
00465 c = 0;
00466 while (b != e) {
00467 std::vector<float> x = b->second;
00468 if (m_type[i] == "INT") {
00469 intArray[c] = (int)rint(x[i]);
00470 } else if ((m_type[i] == "FLOAT") || (m_type[i] == "NUMBER")) {
00471 floatArray[c] = x[i];
00472 } else {
00473 throw(std::runtime_error("ERROR: LMFDat::writeDB: unsupported type"));
00474 }
00475 c++;
00476 b++;
00477 }
00478
00479 dvoid * buffer;
00480 type = oracle::occi::OCCIINT;
00481 ub2 *sizeArray = intSize;
00482 int size = sizeof(intArray[0]);
00483 if ((m_type[i] == "FLOAT") || (m_type[i] == "NUMBER")) {
00484 buffer = (dvoid *)malloc(sizeof(float)*nData);
00485 memcpy(buffer, floatArray, floatTotalSize);
00486 type = oracle::occi::OCCIFLOAT;
00487 sizeArray = floatSize;
00488 size = sizeof(floatArray[0]);
00489 } else {
00490 buffer = (dvoid *)malloc(sizeof(int)*nData);
00491 memcpy(buffer, intArray, intTotalSize);
00492 }
00493 bufPointers.push_back(buffer);
00494 if (m_debug) {
00495 for (int k = 0; ((k < nData) && (k < m_max)); k++) {
00496 cout << m_className << ": === Index=== " << k << endl;
00497 cout << m_className << ": RUN_IOV_ID = " << iovid_vec[k] << endl;
00498 cout << m_className << ": LOGIC_ID = " << logicid_vec[k] << endl;
00499 cout << m_className << ": FIELD " << i << ": "
00500 << ((float *)(buffer))[k] << endl;
00501 }
00502 }
00503 stmt->setDataBuffer(i + 3, buffer, type, size, sizeArray);
00504 }
00505 stmt->executeArrayUpdate(nData);
00506 delete [] intArray;
00507 delete [] floatArray;
00508 delete [] intSize;
00509 delete [] floatSize;
00510 delete [] logicid_vec;
00511 delete [] iovid_vec;
00512 std::list<dvoid *>::const_iterator bi = bufPointers.begin();
00513 std::list<dvoid *>::const_iterator be = bufPointers.end();
00514 while (bi != be) {
00515 free(*bi);
00516 bi++;
00517 }
00518 m_conn->commit();
00519 m_conn->terminateStatement(stmt);
00520 ret = nData;
00521 } catch (oracle::occi::SQLException &e) {
00522 debug();
00523 setMaxDataToDump(nData);
00524
00525 LMFRunIOV *runiov = (LMFRunIOV*)m_foreignKeys[foreignKeyName()];
00526 int iov_id = runiov->getID();
00527 std::cout << "==== This object refers to IOV " << iov_id << std::endl;
00528 dump();
00529 m_conn->rollback();
00530 throw(std::runtime_error(m_className + "::writeDB: " +
00531 e.getMessage()));
00532 }
00533 } else {
00534 cout << m_className << "::writeDB: Cannot write because " <<
00535 m_Error << endl;
00536 dump();
00537 }
00538 }
00539 return ret;
00540 }
00541
00542 void LMFDat::getKeyTypes()
00543 throw(std::runtime_error)
00544 {
00545 m_type.reserve(m_keys.size());
00546 for (unsigned int i = 0; i < m_keys.size(); i++) {
00547 m_type.push_back("");
00548 }
00549
00550 std::string sql = "";
00551 try {
00552 Statement *stmt = m_conn->createStatement();
00553 sql = "SELECT * FROM TABLE(CMS_ECAL_LASER_COND.LMF_TAB_COLS(:1, :2))";
00554
00555
00556
00557
00558
00559
00560 stmt->setSQL(sql);
00561 stmt->setString(1, getTableName());
00562 stmt->setString(2, getIovIdFieldName());
00563 ResultSet *rset = stmt->executeQuery();
00564 while (rset->next() != 0) {
00565 std::string name = rset->getString(1);
00566 std::string t = rset->getString(2);
00567 m_type[m_keys[name]] = t;
00568 }
00569 m_conn->terminateStatement(stmt);
00570 } catch (oracle::occi::SQLException &e) {
00571 throw(std::runtime_error(m_className + "::getKeyTypes: " + e.getMessage() +
00572 " [" + sql + "]"));
00573 }
00574 }
00575
00576 bool LMFDat::check() {
00577
00578 bool ret = true;
00579 m_Error = "";
00580
00581 if (m_className == "LMFUnique") {
00582 m_Error = "class name not set ";
00583 ret = false;
00584 }
00585
00586 if (getTableName() == "") {
00587 m_Error += "table name not set ";
00588 ret = false;
00589 }
00590
00591 if (m_type.size() != m_keys.size()) {
00592 getKeyTypes();
00593 if (m_type.size() != m_keys.size()) {
00594 m_Error += "key size does not correspond to table definition";
00595 ret = false;
00596 }
00597 }
00598 return ret;
00599 }
00600
00601
00602
00603 std::vector<float> LMFDat::getData(int id) {
00604 std::vector<float> ret;
00605 if (m_data.find(id) != m_data.end()) {
00606 ret = m_data[id];
00607 }
00608 return ret;
00609 }
00610
00611 std::vector<float> LMFDat::operator[](int id) {
00612 return getData(id);
00613 }
00614
00615 std::vector<float> LMFDat::getData(const EcalLogicID &id) {
00616 return getData(id.getLogicID());
00617 }
00618
00619
00620
00621 bool LMFDat::getData(int id, std::vector<float> &ret) {
00622 bool retval = false;
00623 if (m_data.find(id) != m_data.end()) {
00624 ret= m_data[id];
00625 retval = true;
00626 }
00627 return retval;
00628 }
00629
00630 bool LMFDat::getData(const EcalLogicID &id, std::vector<float> &ret) {
00631 return getData(id.getLogicID(), ret);
00632 }
00633
00634
00635
00636 std::map<int, std::vector<float> > LMFDat::getData() {
00637 return m_data;
00638 }
00639
00640
00641
00642 float LMFDat::getData(int id, unsigned int k) {
00643 return m_data[id][k];
00644 }
00645
00646 float LMFDat::getData(const EcalLogicID &id, unsigned int k) {
00647 return getData(id.getLogicID(), k);
00648 }
00649
00650 float LMFDat::getData(const EcalLogicID &id, const std::string &key) {
00651 return getData(id.getLogicID(), m_keys[key]);
00652 }
00653
00654 float LMFDat::getData(int id, const std::string &key) {
00655 return getData(id, m_keys[key]);
00656 }
00657
00658
00659
00660 bool LMFDat::getData(int id, unsigned int k, float &ret) {
00661 bool retval = false;
00662 std::vector<float> v;
00663 retval = getData(id, v);
00664 if ((retval) && (v.size() > k)) {
00665 ret= v[k];
00666 retval = true;
00667 } else {
00668 retval = false;
00669 }
00670 return retval;
00671 }
00672
00673 bool LMFDat::getData(const EcalLogicID &id, unsigned int k, float &ret) {
00674 return getData(id.getLogicID(), k, ret);
00675 }
00676
00677 bool LMFDat::getData(int id, const std::string &key, float &ret) {
00678 bool retval = false;
00679 if (m_keys.find(key) != m_keys.end()) {
00680 retval = getData(id, m_keys[key], ret);
00681 }
00682 return retval;
00683 }
00684
00685 bool LMFDat::getData(const EcalLogicID &id, const std::string &key, float &ret)
00686 {
00687 return getData(id.getLogicID(), key, ret);
00688 }