CSCCondDB/src/CSCOnlineDB.cc

0001 #include "OnlineDB/CSCCondDB/interface/CSCOnlineDB.h"
0002
0003 /**
0004    * Constructor for condbon
0005    */
0006 condbon::condbon() noexcept(false) {
0007   std::string db_user;
0008   std::string db_pass;
0009   env = oracle::occi::Environment::createEnvironment(oracle::occi::Environment::OBJECT);
0010   char *c_user = std::getenv("CONDBON_AUTH_USER");
0011   char *c_pass = std::getenv("CONDBON_AUTH_PASSWORD");
0012   db_user = std::string(c_user);
0013   db_pass = std::string(c_pass);
0014   con = env->createConnection(db_user, db_pass, "omds");
0015   std::cout << "Connection to Online DB is done." << std::endl;
0016 }  // end of constructor condbon ()
0017 /**
0018    * Destructor for condbon.
0019    */
0020 condbon::~condbon() noexcept(false) {
0021   env->terminateConnection(con);
0022   oracle::occi::Environment::terminateEnvironment(env);
0023 }  // end of ~condbon ()
0024
0025 void condbon::cdbon_write(CSCobject *obj, std::string obj_name, int record, int global_run, std::string data_time) {
0026   int i, j, k;
0027   std::string tab, tab_map, tab_data;
0028   std::string sqlStmt, sqlStmt1;
0029   int rec_id = 0, map_id = 0, map_index = 0;
0030   tm curtime;
0031   time_t now;
0032
0033   tab = obj_name;
0034   tab_map = obj_name + "_map";
0035   tab_data = obj_name + "_data";
0036   if (obj_name == "test") {
0037     tab = "gains";
0038     tab_map = "gains_map";
0039     tab_data = "gains_data";
0040   }
0041   stmt = con->createStatement();
0042
0043   oracle::occi::ResultSet *rset;
0044
0045   sqlStmt = "SELECT max(record_id) from " + tab;
0046   stmt->setSQL(sqlStmt);
0047   rset = stmt->executeQuery();
0048   //  try{
0049   while (rset->next()) {
0050     rec_id = rset->getInt(1);
0051   }
0052   //     }catch(oracle::occi::SQLException ex)
0053   //    {
0054   // std::cout<<"Exception thrown: "<<std::endl;
0055   // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0056   // std::cout<<ex.getMessage() << std::endl;
0057   //}
0058   stmt->closeResultSet(rset);
0059
0060   if (record > rec_id) {
0061     sqlStmt = "INSERT INTO " + tab + " VALUES (:1, :2, :3, :4, :5, null)";
0062     stmt->setSQL(sqlStmt);
0063     time(&now);
0064     curtime = *localtime(&now);
0065     try {
0066       stmt->setInt(1, record);
0067       stmt->setInt(2, global_run);
0068       stmt->setInt(5, 0);
0069       /* For time as "05/17/2006 16:30:07"
0070   std::string st=data_time.substr(0,2);
0071   int mon=atoi(st.c_str());
0072   st=data_time.substr(3,2);
0073   int mday=atoi(st.c_str());
0074   st=data_time.substr(6,4);
0075   int year=atoi(st.c_str());
0076   st=data_time.substr(11,2);
0077   int hour=atoi(st.c_str());
0078   st=data_time.substr(14,2);
0079   int min=atoi(st.c_str());
0080   st=data_time.substr(17,2);
0081   int sec=atoi(st.c_str());
0082 */
0083       /* For time of format "Mon May 29 10:28:58 2006" */
0084       std::map<std::string, int> month;
0085       month["Jan"] = 1;
0086       month["Feb"] = 2;
0087       month["Mar"] = 3;
0088       month["Apr"] = 4;
0089       month["May"] = 5;
0090       month["Jun"] = 6;
0091       month["Jul"] = 7;
0092       month["Aug"] = 8;
0093       month["Sep"] = 9;
0094       month["Oct"] = 10;
0095       month["Nov"] = 11;
0096       month["Dec"] = 12;
0097       std::string st = data_time.substr(4, 3);
0098       int mon = month[st];
0099       st = data_time.substr(8, 2);
0100       int mday = atoi(st.c_str());
0101       st = data_time.substr(20, 4);
0102       int year = atoi(st.c_str());
0103       st = data_time.substr(11, 2);
0104       int hour = atoi(st.c_str());
0105       st = data_time.substr(14, 2);
0106       int min = atoi(st.c_str());
0107       st = data_time.substr(17, 2);
0108       int sec = atoi(st.c_str());
0109       oracle::occi::Date edate(env, year, mon, mday, hour, min, sec);
0110       stmt->setDate(3, edate);
0111       oracle::occi::Date edate_c(env,
0112                                  curtime.tm_year + 1900,
0113                                  curtime.tm_mon + 1,
0114                                  curtime.tm_mday,
0115                                  curtime.tm_hour,
0116                                  curtime.tm_min,
0117                                  curtime.tm_sec);
0118       stmt->setDate(4, edate_c);
0119       if (obj_name != "test")
0120         stmt->executeUpdate();
0121     } catch (oracle::occi::SQLException &ex) {
0122       std::cout << "Exception thrown for insertBind" << std::endl;
0123       std::cout << "Error number: " << ex.getErrorCode() << std::endl;
0124 #if defined(_GLIBCXX_USE_CXX11_ABI) && (_GLIBCXX_USE_CXX11_ABI == 0)
0125       std::cout << ex.getMessage() << std::endl;
0126 #endif
0127     }
0128   }
0129
0130   sqlStmt = "SELECT max(map_id) from " + tab_map;
0131   stmt->setSQL(sqlStmt);
0132   rset = stmt->executeQuery();
0133   //  try{
0134   while (rset->next()) {
0135     map_id = rset->getInt(1);
0136   }
0137   //     }catch(oracle::occi::SQLException ex)
0138   //{
0139   // std::cout<<"Exception thrown: "<<std::endl;
0140   // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0141   // std::cout<<ex.getMessage() << std::endl;
0142   //}
0143   stmt->closeResultSet(rset);
0144
0145   std::ostringstream ss;
0146   ss << record;
0147
0148   sqlStmt = "SELECT max(map_index) from " + tab_map + " where record_id=" + ss.str();
0149   ss.str("");  // clear
0150   stmt->setSQL(sqlStmt);
0151   rset = stmt->executeQuery();
0152   //  try{
0153   while (rset->next()) {
0154     map_index = rset->getInt(1);
0155   }
0156   //     }catch(oracle::occi::SQLException ex)
0157   //{
0158   // std::cout<<"Exception thrown: "<<std::endl;
0159   // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0160   // std::cout<<ex.getMessage() << std::endl;
0161   //}
0162   stmt->closeResultSet(rset);
0163
0164   sqlStmt = "INSERT INTO " + tab_map + " VALUES (:1, :2, :3, :4)";
0165   stmt->setSQL(sqlStmt);
0166
0167   std::map<int, std::vector<std::vector<float> > >::const_iterator itm;
0168   itm = obj->obj.begin();
0169   int sizeint = itm->second[0].size();
0170   sqlStmt1 = "INSERT INTO " + tab_data + " VALUES (:1, :2";
0171   for (i = 1; i < sizeint + 1; ++i) {
0172     ss << i + 2;
0173     sqlStmt1 = sqlStmt1 + ", :" + ss.str();
0174     ss.str("");  // clear
0175   }
0176   sqlStmt1 = sqlStmt1 + ")";
0177
0178   sb4 si = sizeof(int);
0179   sb4 sf = sizeof(float);
0180   ub2 elensi[200];
0181   ub2 elensf[200];
0182   int c1[200], c2[200];
0183   float c[100][200];
0184   for (i = 0; i < 200; ++i) {
0185     elensi[i] = si;
0186     elensf[i] = sf;
0187   }
0188
0189   stmt1 = con->createStatement();
0190   stmt1->setSQL(sqlStmt1);
0191
0192   for (itm = obj->obj.begin(); itm != obj->obj.end(); ++itm) {
0193     int id_det = itm->first;
0194     int sizev = obj->obj[id_det].size();
0195
0196     map_id = map_id + 1;
0197     map_index = map_index + 1;
0198     //  try{
0199     stmt->setInt(1, map_id);
0200     stmt->setInt(2, record);
0201     stmt->setInt(3, map_index);
0202     stmt->setInt(4, id_det);
0203     if (obj_name != "test")
0204       stmt->executeUpdate();
0205     //}catch(oracle::occi::SQLException ex)
0206     //{
0207     //std::cout<<"Exception thrown for insertBind"<<std::endl;
0208     //std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0209     //std::cout<<ex.getMessage() << std::endl;
0210     //}
0211
0212     k = 0;
0213     for (i = 0; i < sizev; ++i) {
0214       int sizei = obj->obj[id_det][i].size();
0215       if (sizei != sizeint) {
0216         std::cout << "Inconsistent object - dimention of internal vector is not " << sizeint << std::endl;
0217         exit(1);
0218       }
0219       c1[i] = map_id;
0220       k = k + 1;
0221       c2[i] = k;
0222       for (j = 0; j < sizei; ++j) {
0223         c[j][i] = obj->obj[id_det][i][j];
0224       }
0225     }
0226     //  try{
0227     stmt1->setDataBuffer(1, c1, oracle::occi::OCCIINT, si, &elensi[0]);
0228     stmt1->setDataBuffer(2, c2, oracle::occi::OCCIINT, si, &elensi[0]);
0229     for (j = 0; j < sizeint; ++j) {
0230       stmt1->setDataBuffer(j + 3, c[j], oracle::occi::OCCIFLOAT, sf, &elensf[0]);
0231     }
0232     if (obj_name != "test")
0233       stmt1->executeArrayUpdate(sizev);
0234     //     }catch(oracle::occi::SQLException ex)
0235     // {
0236     //  std::cout<<"Exception thrown: "<<std::endl;
0237     //  std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0238     //  std::cout<<ex.getMessage() << std::endl;
0239     // }
0240   }
0241   con->commit();
0242   con->terminateStatement(stmt);
0243   con->terminateStatement(stmt1);
0244 }  //end of cdbon_write
0245
0246 void condbon::cdbon_last_record(std::string obj_name, int *record) {
0247   std::string sqlStmt;
0248   sqlStmt = "SELECT max(record_id) from " + obj_name;
0249   stmt = con->createStatement();
0250   stmt->setSQL(sqlStmt);
0251   oracle::occi::ResultSet *rset;
0252   rset = stmt->executeQuery();
0253   //try{
0254   while (rset->next()) {
0255     *record = rset->getInt(1);
0256   }
0257   //     }catch(oracle::occi::SQLException ex)
0258   //{
0259   // std::cout<<"Exception thrown: "<<std::endl;
0260   // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0261   // std::cout<<ex.getMessage() << std::endl;
0262   //}
0263   con->terminateStatement(stmt);
0264 }  // end of cdbon_last_record
0265
0266 void condbon::cdbon_read_rec(std::string obj_name, int record, CSCobject *obj) {
0267   int i, len;
0268   int map_id = 0, layer_id = 0;
0269   std::string tab, tab_map, tab_data;
0270   tab = obj_name;
0271   tab_map = obj_name + "_map";
0272   tab_data = obj_name + "_data";
0273   int num_var = 0;
0274   int vec_index;
0275
0276   std::string sqlStmt, sqlStmt1;
0277   stmt = con->createStatement();
0278   stmt1 = con->createStatement();
0279   oracle::occi::ResultSet *rset, *rset1;
0280   std::ostringstream ss;
0281
0282   char d;
0283   const char *p = tab_data.c_str();
0284   len = tab_data.length();
0285   for (i = 0; i < len; ++i) {
0286     d = toupper(*(p + i));
0287     ss << d;
0288   }
0289   sqlStmt = "SELECT count(column_name) from user_tab_columns where table_name='" + ss.str() + "'";
0290   ss.str("");  // clear
0291   stmt->setSQL(sqlStmt);
0292   rset = stmt->executeQuery();
0293   //  try{
0294   while (rset->next()) {
0295     num_var = rset->getInt(1) - 2;
0296   }
0297   //     }catch(oracle::occi::SQLException ex)
0298   //{
0299   // std::cout<<"Exception thrown: "<<std::endl;
0300   // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0301   // std::cout<<ex.getMessage() << std::endl;
0302   //}
0303   stmt->closeResultSet(rset);
0304
0305   ss << record;
0306
0307   sqlStmt = "SELECT map_id,map_index,layer_id from " + tab_map + " where record_id=" + ss.str() + " order by map_index";
0308   ss.str("");  // clear
0309   stmt->setSQL(sqlStmt);
0310   rset = stmt->executeQuery();
0311   //  try{
0312   while (rset->next()) {
0313     map_id = rset->getInt(1);
0314     rset->getInt(2);
0315     layer_id = rset->getInt(3);
0316     ss << map_id;
0317     sqlStmt1 = "SELECT * from " + tab_data + " where map_id=" + ss.str() + " order by vec_index";
0318     ss.str("");  // clear
0319     stmt1->setSQL(sqlStmt1);
0320     rset1 = stmt1->executeQuery();
0321     //     try{
0322     while (rset1->next()) {
0323       vec_index = rset1->getInt(2);
0324       obj->obj[layer_id].resize(vec_index);
0325       for (i = 0; i < num_var; ++i) {
0326         obj->obj[layer_id][vec_index - 1].push_back(rset1->getFloat(3 + i));
0327       }
0328     }
0329     //   }catch(oracle::occi::SQLException ex)
0330     //{
0331     // std::cout<<"Exception thrown: "<<std::endl;
0332     // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0333     // std::cout<<ex.getMessage() << std::endl;
0334     //}
0335   }
0336   // }catch(oracle::occi::SQLException ex)
0337   //{
0338   // std::cout<<"Exception thrown: "<<std::endl;
0339   // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
0340   // std::cout<<ex.getMessage() << std::endl;
0341   //}
0342 }  // end of cdbon_read_rec