packages/intt/InttOdbcQuery.cc

0001 /// Originally imitated from coresoftware/offline/database/rundb/CaloTime.*
0002
0003 #include "InttOdbcQuery.h"
0004
0005 #include <phool/phool.h>
0006
0007 #include <odbc++/connection.h>
0008 #include <odbc++/drivermanager.h>
0009 #include <odbc++/resultset.h>
0010 #include <odbc++/statement.h>
0011
0012 #include <iostream>
0013 #include <sstream>
0014
0015 #include <filesystem>
0016 #include <random> // For retrying connections
0017 #include <chrono> // For retrying connections
0018 #include <thread> // For retrying connections
0019
0020 int InttOdbcQuery::Query(int runnumber)
0021 {
0022   m_query_successful = false;
0023
0024   std::random_device ran_dev;
0025   std::seed_seq seeds {ran_dev(), ran_dev(), ran_dev()}; //...
0026   std::mt19937_64 mersenne_twister(seeds);
0027   std::uniform_int_distribution<> uniform(m_MIN_SLEEP_DUR, m_MAX_SLEEP_DUR);
0028
0029   int num_tries = 0;
0030   int total_slept_ms = 0;
0031
0032   odbc::Connection* dbcon = nullptr;
0033   for(num_tries = 0; num_tries < m_MAX_NUM_RETRIES; ++num_tries)
0034   {
0035     try
0036     {
0037       dbcon = odbc::DriverManager::getConnection("daq", "", "");
0038     }
0039     catch (odbc::SQLException &e)
0040     {
0041       std::cerr << PHWHERE << "\n"
0042                 << "\tSQL Exception:\n"
0043                 << "\t" << e.getMessage() << std::endl;
0044     }
0045
0046     if(dbcon)
0047     {
0048       ++num_tries;
0049       break;
0050     }
0051
0052     int sleep_time_ms = uniform(mersenne_twister);
0053     std::this_thread::sleep_for(std::chrono::milliseconds(sleep_time_ms));
0054     total_slept_ms += sleep_time_ms;
0055
0056     if(1 < m_verbosity)
0057     {
0058       std::cout << PHWHERE << "\n"
0059                 << "\tConnection unsuccessful\n"
0060                 << "\tSleeping for addtional " << sleep_time_ms << " ms" << std::endl;
0061     }
0062   }
0063
0064   if(m_verbosity)
0065   {
0066     std::cout << PHWHERE << "\n"
0067               << "\tConnection successful (" << num_tries << " attempts, " << total_slept_ms << " ms)" << std::endl;
0068   }
0069
0070   if(!dbcon)
0071   {
0072     std::cerr << PHWHERE << "\n"
0073               << "\tDB Conntion failed after " << m_MAX_NUM_RETRIES << " retries\n"
0074               << "\tAbandoning query" << std::endl;
0075     return 1;
0076   }
0077
0078   odbc::Statement* statement = dbcon->createStatement();
0079
0080   int iret = 0;
0081   iret += (QueryStreaming(statement, runnumber) != 0);
0082   //...
0083
0084   m_query_successful = (iret == 0);
0085
0086   delete statement;
0087   delete dbcon;
0088
0089   return iret;
0090 }
0091
0092 int InttOdbcQuery::QueryStreaming(void* statement, int runnumber)
0093 {
0094   odbc::ResultSet* result_set = nullptr;
0095
0096   std::string sched_data;
0097   try
0098   {
0099     std::string sql = "SELECT sched_data FROM gtm_scheduler WHERE vgtm=1 AND sched_entry = 1 AND runnumber = " + std::to_string(runnumber) + ";";
0100     result_set = ((odbc::Statement*)statement)->executeQuery(sql);
0101     if(result_set && result_set->next())
0102     {
0103       sched_data = result_set->getString("sched_data");
0104     }
0105   }
0106   catch (odbc::SQLException &e)
0107   {
0108     std::cerr << PHWHERE << "\n"
0109               << "\tSQL Exception:\n"
0110               << "\t" << e.getMessage() << std::endl;
0111     delete result_set;
0112     return 1;
0113   }
0114   delete result_set;
0115
0116   if(std::string{"{17,55,24,54}"} == sched_data)
0117   {
0118     /// Streaming
0119     m_is_streaming = true;
0120   }
0121   else if (std::string{"{0,54,91,53}"} == sched_data)
0122   {
0123     /// Triggered
0124     m_is_streaming = false;
0125   }
0126   else
0127   {
0128     std::cerr << PHWHERE << "\n"
0129               << "\tUnexpected value for sched_data: '" << sched_data << "'" << std::endl;
0130     return 1;
0131   }
0132
0133   if(m_verbosity)
0134   {
0135     std::cout << "\tsched_data: '" << sched_data << "'" << std::endl;
0136   }
0137
0138   return 0;
0139 }
0140
0141 int InttOdbcQuery::QueryType(void* statement, int runnumber)
0142 {
0143   odbc::ResultSet* result_set = nullptr;
0144   m_type = "";
0145
0146   try
0147   {
0148     std::string sql = "SELECT runtype FROM run WHERE runnumber = " + std::to_string(runnumber) + ";";
0149     result_set = ((odbc::Statement*)statement)->executeQuery(sql);
0150     if(result_set && result_set->next())
0151     {
0152       m_type = result_set->getString("runtype");
0153     }
0154   }
0155   catch (odbc::SQLException &e)
0156   {
0157     std::cerr << PHWHERE << "\n"
0158               << "\tSQL Exception:\n"
0159               << "\t" << e.getMessage() << std::endl;
0160     delete result_set;
0161     return 1;
0162   }
0163   delete result_set;
0164
0165   if(m_verbosity)
0166   {
0167     std::cout << "\trun type: " << m_type << std::endl;
0168   }
0169
0170   return 0;
0171 }