subsystems/pktsize/PktSizeDBodbc.cc

0001 #include "PktSizeDBodbc.h"
0002
0003 #include <phool/phool.h>
0004
0005 #include <odbc++/connection.h>
0006 #include <odbc++/drivermanager.h>
0007 #include <odbc++/errorhandler.h>
0008 #include <odbc++/resultset.h>
0009 #include <odbc++/resultsetmetadata.h>
0010 #include <odbc++/setup.h>
0011 #include <odbc++/types.h>
0012 #include <sql.h>
0013 #include <iostream>
0014
0015 #include <algorithm>
0016 #include <cctype>
0017 #include <ctime>
0018 #include <fstream>
0019 #include <set>
0020 #include <sstream>
0021
0022 static odbc::Connection* con = nullptr;
0023
0024 PktSizeDBodbc::PktSizeDBodbc(const std::string& name)
0025   : OnlMonBase(name)
0026   , tableprefix(name)
0027 {
0028 }
0029
0030 PktSizeDBodbc::~PktSizeDBodbc()
0031 {
0032   delete con;
0033   con = nullptr;
0034 }
0035
0036 int PktSizeDBodbc::CheckAndCreateTable(const std::string& name, const std::map<unsigned int, unsigned int>& packetsize)
0037 {
0038   if (GetConnection())
0039   {
0040     return -1;
0041   }
0042
0043   // Postgres version
0044   // std::cout << con->getMetaData()-> getDatabaseProductVersion() << std::endl;
0045   odbc::Statement* stmt = con->createStatement();
0046   std::ostringstream cmd;
0047   //  cmd << "SELECT COUNT(*) FROM " << name << " WHERE 1 = 2" ;
0048   // cmd << "select " << name << " from pg_tables where schemaname='public";
0049   std::string lowname = name;
0050   // The bizarre cast here is needed for newer gccs
0051   transform(lowname.begin(), lowname.end(), lowname.begin(), (int (*)(int)) tolower);
0052   cmd << "select * from pg_tables where tablename = '" << lowname << "'";
0053   //  cmd << "SELECT * FROM " << name << " LIMIT 1" ;
0054   if (verbosity > 0)
0055   {
0056     std::cout << "cmd: " << cmd.str() << std::endl;
0057   }
0058
0059   odbc::ResultSet* rs = nullptr;
0060   try
0061   {
0062     rs = stmt->executeQuery(cmd.str());
0063   }
0064   catch (odbc::SQLException& e)
0065   {
0066     std::cout << name << " does not exist, creating it" << std::endl;
0067   }
0068   int iret = 0;
0069
0070   cmd.str("");
0071   if (!rs->next())
0072   {
0073     delete rs;
0074     rs = nullptr;
0075     cmd << "CREATE TABLE " << name << "(runnumber int NOT NULL, events int NOT NULL";
0076     std::map<unsigned int, unsigned int>::const_iterator iter;
0077     for (iter = packetsize.begin(); iter != packetsize.end(); ++iter)
0078     {
0079       cmd << ", p_" << iter->first << " float DEFAULT 0";
0080     }
0081     cmd << ", primary key(runnumber))";
0082     if (verbosity > 0)
0083     {
0084       std::cout << "Executing " << cmd.str() << std::endl;
0085     }
0086     try
0087     {
0088       iret = stmt->executeUpdate(cmd.str());
0089     }
0090     catch (odbc::SQLException& e)
0091     {
0092       std::cout << "Exception caught, Message: " << e.getMessage() << std::endl;
0093     }
0094   }
0095   delete stmt;
0096   return iret;
0097 }
0098
0099 int PktSizeDBodbc::AddRow(const std::string& granulename, const int runnumber, const int nevnts, const std::map<unsigned int, unsigned int>& packetsize)
0100 {
0101   std::string table = tableprefix + granulename;
0102
0103   CheckAndCreateTable(table, packetsize);
0104
0105   int iret = 0;
0106   std::ostringstream cmd;
0107
0108   if (GetConnection())
0109   {
0110     return -1;
0111   }
0112
0113   odbc::Statement* stmt = con->createStatement();
0114
0115   // check if an entry for this run exists already
0116   cmd << "SELECT events FROM " << table << " where runnumber = "
0117       << runnumber;
0118   odbc::ResultSet* rs;
0119   try
0120   {
0121     rs = stmt->executeQuery(cmd.str());
0122   }
0123   catch (odbc::SQLException& e)
0124   {
0125     std::cout << "Exception caught, Message: " << e.getMessage() << std::endl;
0126     return -1;
0127   }
0128   if (rs->next())
0129   {
0130     int events = rs->getInt("events");
0131     if (nevnts <= events)
0132     {
0133       std::cout << "Run " << runnumber << " already in table "
0134            << table << " extracted from " << events << " Events"
0135            << std::endl;
0136       std::cout << "Run more events than " << events
0137            << " if you want to overwrite this entry" << std::endl;
0138       delete rs;
0139       return 0;
0140     }
0141     else
0142     {
0143       cmd.str("");
0144       cmd << "DELETE FROM " << table << " WHERE runnumber = " << runnumber;
0145       odbc::Statement* stmt2 = con->createStatement();
0146       stmt2->executeUpdate(cmd.str());
0147     }
0148   }
0149   delete rs;
0150   // update columns in table in case there is a new packet
0151   CheckAndAddColumns(table, packetsize);
0152
0153   // now add the content
0154   std::map<unsigned int, unsigned int>::const_iterator iter;
0155   cmd.str("");
0156   std::ostringstream cmd1, cmd2;
0157   cmd1 << "INSERT INTO " << table
0158        << " (runnumber, events";
0159   cmd2 << " VALUES(" << runnumber << ", " << nevnts;
0160   float size_in_bytes;
0161   for (iter = packetsize.begin(); iter != packetsize.end(); ++iter)
0162   {
0163     cmd1 << ", p_" << iter->first;
0164     size_in_bytes = (float) (iter->second) / (float) (nevnts);
0165     size_in_bytes *= 4;  // convert from 32 bit words to bytes
0166     cmd2 << ", " << size_in_bytes;
0167   }
0168   cmd1 << ")";
0169   cmd2 << ")";
0170   cmd << cmd1.str() << cmd2.str();
0171   try
0172   {
0173     stmt->executeUpdate(cmd.str());
0174   }
0175   catch (odbc::SQLException& e)
0176   {
0177     std::cout << "Exception caught, Message: " << e.getMessage() << std::endl;
0178   }
0179
0180   return iret;
0181 }
0182
0183 int PktSizeDBodbc::GetPacketContent(std::map<unsigned int, float>& packetsize, const int runnumber, const std::string& granulename)
0184 {
0185   if (GetConnection())
0186   {
0187     return -1;
0188   }
0189   int iret = 0;
0190   std::string table = tableprefix + granulename;
0191
0192   odbc::Statement* query = con->createStatement();
0193   std::ostringstream cmd;
0194   cmd << "SELECT * FROM " << table << " WHERE runnumber = " << runnumber;
0195
0196   if (verbosity > 0)
0197   {
0198     std::cout << "command: " << cmd.str() << std::endl;
0199   }
0200
0201   odbc::ResultSet* rs;
0202   try
0203   {
0204     rs = query->executeQuery(cmd.str());
0205   }
0206   catch (odbc::SQLException& e)
0207   {
0208     const std::string& errmsg = e.getMessage();
0209     if (errmsg.find("does not exist") == std::string::npos)
0210     {
0211       std::cout << "Exception caught, when accessing table "
0212            << table << std::endl;
0213       std::cout << "Message: " << e.getMessage() << std::endl;
0214     }
0215     return -1;
0216   }
0217   if (rs->next())
0218   {
0219     for (int i = 1; i <= rs->getMetaData()->getColumnCount(); i++)
0220     {
0221       std::string colname = rs->getMetaData()->getColumnName(i);
0222       if (colname == "runnumber" || colname == "events")
0223       {
0224         continue;
0225       }
0226       std::string packet = colname.substr(colname.find_last_of('_') + 1);
0227       std::istringstream istr(packet);
0228       unsigned int ipkt;
0229       istr >> ipkt;
0230       float size = rs->getFloat(colname);
0231       if (rs->wasNull() || size <= 1.)
0232       {
0233         continue;
0234       }
0235       packetsize[ipkt] = size / 4.;  // convert from bytes to long words
0236     }
0237     delete rs;
0238   }
0239   return iret;
0240 }
0241
0242 int PktSizeDBodbc::GetConnection()
0243 {
0244   if (con)
0245   {
0246     return 0;
0247   }
0248   try
0249   {
0250     con = odbc::DriverManager::getConnection(dbname.c_str(), dbowner.c_str(), dbpasswd.c_str());
0251   }
0252   catch (odbc::SQLException& e)
0253   {
0254     std::cout << PHWHERE
0255          << " Exception caught during DriverManager::getConnection" << std::endl;
0256     std::cout << "Message: " << e.getMessage() << std::endl;
0257     if (con)
0258     {
0259       delete con;
0260       con = nullptr;
0261     }
0262     return -1;
0263   }
0264   printf("opened DB connection\n");
0265   return 0;
0266 }
0267
0268 int PktSizeDBodbc::CheckAndAddColumns(const std::string& table, const std::map<unsigned int, unsigned int>& packetsize)
0269 {
0270   if (GetConnection())
0271   {
0272     return -1;
0273   }
0274   odbc::Statement* stmt = con->createStatement();
0275   std::ostringstream cmd;
0276   cmd << "SELECT * FROM " << table << " limit 1 ";
0277   odbc::ResultSet* rs;
0278   try
0279   {
0280     rs = stmt->executeQuery(cmd.str());
0281   }
0282   catch (odbc::SQLException& e)
0283   {
0284     std::cout << "Exception caught, Message: " << e.getMessage() << std::endl;
0285     return -1;
0286   }
0287   std::map<unsigned int, unsigned int>::const_iterator iter;
0288   std::set<unsigned int> packetids;
0289   for (iter = packetsize.begin(); iter != packetsize.end(); ++iter)
0290   {
0291     packetids.insert(iter->first);
0292   }
0293   for (int i = 1; i <= rs->getMetaData()->getColumnCount(); i++)
0294   {
0295     std::string colname = rs->getMetaData()->getColumnName(i);
0296     if (colname == "runnumber" || colname == "events")
0297     {
0298       continue;
0299     }
0300     std::string packet = colname.substr(colname.find_last_of('_') + 1);
0301     std::istringstream istr(packet);
0302     unsigned int ipkt;
0303     istr >> ipkt;
0304     packetids.erase(ipkt);
0305   }
0306   delete rs;
0307   std::set<unsigned int>::const_iterator siter;
0308   for (siter = packetids.begin(); siter != packetids.end(); ++siter)
0309   {
0310     std::ostringstream newcol;
0311     newcol.str("");
0312     newcol << "p_" << *siter;
0313     cmd.str("");
0314     cmd << "ALTER TABLE " << table << " ADD COLUMN " << newcol.str()
0315         << " float DEFAULT 0";
0316     try
0317     {
0318       stmt->executeUpdate(cmd.str());
0319     }
0320     catch (odbc::SQLException& e)
0321     {
0322       std::cout << "Exception caught, Message: " << e.getMessage() << std::endl;
0323     }
0324   }
0325   return 0;
0326 }