OnlMon/onlmonserver/OnlMonStatusDB.cc

0001 #include "OnlMonStatusDB.h"
0002
0003 #include <odbc++/connection.h>
0004 #include <odbc++/drivermanager.h>
0005 #include <odbc++/resultset.h>
0006 #include <odbc++/statement.h>  // for Statement
0007 #include <odbc++/types.h>      // for SQLException
0008
0009 #include <iostream>
0010 #include <sstream>
0011
0012 static odbc::Connection* con = nullptr;
0013
0014 OnlMonStatusDB::OnlMonStatusDB(const std::string& tablename)
0015   : table(tablename)
0016 {
0017 }
0018
0019 OnlMonStatusDB::~OnlMonStatusDB()
0020 {
0021   delete con;
0022   con = nullptr;
0023 }
0024
0025 int OnlMonStatusDB::CheckAndCreateTable()
0026 {
0027   if (GetConnection())
0028   {
0029     return -1;
0030   }
0031
0032   //Postgres version
0033   //std::cout << con->getMetaData()-> getDatabaseProductVersion() << std::endl;
0034   odbc::Statement* stmt = con->createStatement();
0035   std::ostringstream cmd;
0036   //  cmd << "SELECT COUNT(*) FROM " << name << " WHERE 1 = 2" ;
0037   cmd << "SELECT * FROM pg_tables where tablename = '" << table << "'";
0038 #ifdef VERBOSE
0039
0040   std::cout << cmd.str() << std::endl;
0041 #endif
0042
0043   odbc::ResultSet* rs = nullptr;
0044   try
0045   {
0046     rs = stmt->executeQuery(cmd.str());
0047   }
0048   catch (odbc::SQLException& e)
0049   {
0050     const std::string& message = e.getMessage();
0051     if (message.find("does not exist") == std::string::npos)
0052     {
0053       std::cout << "Exception caught" << std::endl;
0054       std::cout << "Message: " << e.getMessage() << std::endl;
0055     }
0056   }
0057   cmd.str("");
0058   if (!rs->next())
0059   {
0060     delete rs;
0061     cmd << "CREATE TABLE " << table << "(runnumber int, primary key(runnumber))";
0062     try
0063     {
0064       stmt->executeUpdate(cmd.str());
0065     }
0066     catch (odbc::SQLException& e)
0067     {
0068       std::cout << "caught exception Message: " << e.getMessage() << std::endl;
0069     }
0070   }
0071   return 0;
0072 }
0073
0074 int OnlMonStatusDB::CheckAndCreateMonitor(const std::string& name)
0075 {
0076   std::ostringstream cmd;
0077   if (CheckAndCreateTable())
0078   {
0079     std::cout << "Problem creating " << table << std::endl;
0080     return -1;
0081   }
0082   cmd << "SELECT * FROM " << table << " LIMIT 1";
0083   odbc::ResultSet* rs = nullptr;
0084   odbc::Statement* stmt = con->createStatement();
0085   try
0086   {
0087     rs = stmt->executeQuery(cmd.str());
0088   }
0089   catch (odbc::SQLException& e)
0090   {
0091     std::cout << __PRETTY_FUNCTION__ << "Exception caught" << std::endl;
0092     std::cout << "Message: " << e.getMessage() << std::endl;
0093     return -1;
0094   }
0095   try
0096   {
0097     rs->findColumn(name);
0098   }
0099   catch (odbc::SQLException& e)
0100   {
0101     const std::string& exceptionmessage = e.getMessage();
0102     if (exceptionmessage.find("not found in result set") != std::string::npos)
0103     {
0104       cmd.str("");
0105       cmd << "ALTER TABLE "
0106           << table
0107           << " ADD "
0108           << name
0109           << " int default 0";
0110       try
0111       {
0112         odbc::Statement* stmtup = con->createStatement();
0113         stmtup->executeUpdate(cmd.str());
0114       }
0115       catch (odbc::SQLException& e2)
0116       {
0117         std::cout << "Exception caught: " << e2.getMessage() << std::endl;
0118         return -1;
0119       }
0120     }
0121   }
0122   return 0;
0123 }
0124
0125 int OnlMonStatusDB::FindAndInsertRunNum(const int runnumber)
0126 {
0127   if (GetConnection() != 0)
0128   {
0129     std::cout << "problem" << std::endl;
0130     return -1;
0131   }
0132   if (findRunNumInDB(runnumber) == 0)
0133   {
0134     return 0;
0135   }
0136   odbc::Statement* statement = con->createStatement();
0137   std::ostringstream cmd;
0138   cmd << "INSERT INTO "
0139       << table
0140       << " (runnumber) VALUES ("
0141       << runnumber << ")";
0142   try
0143   {
0144     statement->executeUpdate(cmd.str());
0145   }
0146   catch (odbc::SQLException& e)
0147   {
0148     std::cout << e.getMessage() << std::endl;
0149     return -1;
0150   }
0151   return 0;
0152 }
0153
0154 int OnlMonStatusDB::findRunNumInDB(const int runnumber)
0155 {
0156   odbc::Statement* statement = nullptr;
0157   odbc::ResultSet* rs = nullptr;
0158   std::ostringstream cmd;
0159   cmd << "SELECT runnumber FROM "
0160       << table
0161       << " WHERE runnumber = "
0162       << runnumber;
0163
0164   statement = con->createStatement();
0165
0166   try
0167   {
0168     rs = statement->executeQuery(cmd.str());
0169   }
0170   catch (odbc::SQLException& e)
0171   {
0172     std::cout << "exception caught: " << e.getMessage() << std::endl;
0173     return -1;
0174   }
0175
0176   if (rs->next())
0177   {
0178     try
0179     {
0180       rs->getInt("runnumber");
0181     }
0182     catch (odbc::SQLException& e)
0183     {
0184       std::cout << "exception caught: " << e.getMessage() << std::endl;
0185       return -1;
0186     }
0187   }
0188   else
0189   {
0190     return -1;
0191   }
0192   return 0;
0193 }
0194
0195 int OnlMonStatusDB::UpdateStatus(const std::string& name, const int runnumber, const int status)
0196 {
0197   if (CheckAndCreateMonitor(name))
0198   {
0199     std::cout << __PRETTY_FUNCTION__ << "Problem encountered, cannot do update" << std::endl;
0200     return -1;
0201   }
0202   if (FindAndInsertRunNum(runnumber) != 0)
0203   {
0204     std::cout << __PRETTY_FUNCTION__ << "Problem updating runnumber encountered, cannot do update" << std::endl;
0205     return -1;
0206   }
0207
0208   std::ostringstream cmd;
0209   cmd << "Update "
0210       << table
0211       << " set " << name
0212       << " = " << status
0213       << " where runnumber = "
0214       << runnumber;
0215   odbc::Statement* stmtupd = nullptr;
0216   try
0217   {
0218     stmtupd = con->createStatement();
0219   }
0220   catch (odbc::SQLException& e)
0221   {
0222     std::cout << "Cannot create statement" << std::endl;
0223     std::cout << e.getMessage() << std::endl;
0224     return -1;
0225   }
0226
0227   try
0228   {
0229     stmtupd->executeUpdate(cmd.str());
0230   }
0231   catch (odbc::SQLException& e)
0232   {
0233     std::cout << __PRETTY_FUNCTION__ << "Exception caught" << std::endl;
0234     std::cout << "Message: " << e.getMessage() << std::endl;
0235     return -1;
0236   }
0237   return 0;
0238 }
0239
0240 int OnlMonStatusDB::GetConnection()
0241 {
0242   if (con)
0243   {
0244     return 0;
0245   }
0246   try
0247   {
0248     con = odbc::DriverManager::getConnection(dbname.c_str(), dbowner.c_str(), dbpasswd.c_str());
0249   }
0250   catch (odbc::SQLException& e)
0251   {
0252     std::cout << __PRETTY_FUNCTION__
0253               << " Exception caught during DriverManager::getConnection" << std::endl;
0254     std::cout << "Message: " << e.getMessage() << std::endl;
0255     if (con)
0256     {
0257       delete con;
0258       con = nullptr;
0259     }
0260     return -1;
0261   }
0262   return 0;
0263 }