OnlMon/onlmondatabase/OnlMonDBodbc.cc

0001 #include "OnlMonDBodbc.h"
0002 #include "OnlMonDBReturnCodes.h"
0003 #include "OnlMonDBVar.h"
0004
0005 #include <onlmon/OnlMonBase.h>  // for OnlMonBase
0006
0007 #include <odbc++/connection.h>
0008 #include <odbc++/databasemetadata.h>
0009 #include <odbc++/drivermanager.h>
0010 #include <odbc++/resultset.h>  // for ResultSet
0011 #include <odbc++/resultsetmetadata.h>
0012 #include <odbc++/statement.h>  // for Statement
0013 #include <odbc++/types.h>      // for SQLException, odbc
0014
0015 #include <ctype.h>  // for tolower
0016 #include <algorithm>
0017 #include <cstdio>  // for printf
0018 #include <ctime>
0019 #include <fstream>
0020 #include <iostream>
0021 #include <sstream>
0022 #include <utility>  // for pair
0023
0024 static const unsigned int DEFAULTCOLUMNS = 2;
0025 //static const unsigned int COLUMNSPARVAR = 3;
0026 static const std::string addvarname[3] = {"", "err", "qual"};
0027 static const unsigned int MINUTESINTERVAL = 4;
0028
0029 static odbc::Connection* con = nullptr;
0030
0031 // #define VERBOSE
0032
0033 OnlMonDBodbc::OnlMonDBodbc(const std::string& tablename)
0034   : OnlMonBase(tablename)
0035   , dbname("OnlMonDB")
0036   , dbowner("phnxrc")
0037   , dbpasswd("")
0038   , table(tablename)
0039 {
0040   // table names are lower case only, so convert string to lowercase
0041   // The bizarre cast here is needed for newer gccs
0042   transform(table.begin(), table.end(), table.begin(), (int (*)(int)) tolower);
0043 }
0044
0045 OnlMonDBodbc::~OnlMonDBodbc()
0046 {
0047   delete con;
0048   con = nullptr;
0049 }
0050
0051 int OnlMonDBodbc::CreateTable()
0052 {
0053   if (GetConnection())
0054   {
0055     return -1;
0056   }
0057
0058   std::ostringstream cmd;
0059   cmd << "SELECT * FROM pg_tables where tablename = '" << table << "'";
0060   odbc::ResultSet* rs = nullptr;
0061   odbc::Statement* stmt = con->createStatement();
0062   try
0063   {
0064     rs = stmt->executeQuery(cmd.str());
0065   }
0066   catch (odbc::SQLException& e)
0067   {
0068     std::cout << "caught exception Message: " << e.getMessage() << std::endl;
0069   }
0070   int iret = 0;
0071   if (!rs->next())
0072   {
0073     delete rs;
0074     rs = nullptr;
0075     if (verbosity > 0)
0076     {
0077       std::cout << "need to create table" << std::endl;
0078     }
0079     cmd.str("");
0080     cmd << "CREATE TABLE " << table << "(date timestamp(0) with time zone NOT NULL, run int NOT NULL, primary key(date,run))";
0081     try
0082     {
0083       stmt->executeUpdate(cmd.str());
0084     }
0085     catch (odbc::SQLException& e)
0086     {
0087       std::cout << "caught exception Message: " << e.getMessage() << std::endl;
0088     }
0089   }
0090   else
0091   {
0092     if (verbosity > 0)
0093     {
0094       std::cout << "table " << table << " exists" << std::endl;
0095     }
0096   }
0097   delete rs;
0098   delete stmt;
0099   return iret;
0100 }
0101
0102 int OnlMonDBodbc::CheckAndCreateTable(const std::map<const std::string, OnlMonDBVar*>& varmap)
0103 {
0104   if (GetConnection())
0105   {
0106     return -1;
0107   }
0108   if (CreateTable())  // check and create the table
0109   {
0110     return -1;
0111   }
0112   odbc::Statement* stmt = con->createStatement();
0113   odbc::ResultSet* rs = nullptr;
0114   std::ostringstream cmd;
0115   int iret = 0;
0116   cmd << "select * from " << table << " limit 1";
0117   std::map<const std::string, OnlMonDBVar*>::const_iterator iter;
0118   try
0119   {
0120     rs = stmt->executeQuery(cmd.str());
0121   }
0122   catch (odbc::SQLException& e)
0123   {
0124     std::cout << "caught exception Message: " << e.getMessage() << std::endl;
0125   }
0126   odbc::ResultSetMetaData* meta = rs->getMetaData();
0127   unsigned int nocolumn = rs->getMetaData()->getColumnCount();
0128   for (iter = varmap.begin(); iter != varmap.end(); ++iter)
0129   {
0130     std::string varname = iter->first;
0131     // column names are lower case only, so convert string to lowercase
0132     // The bizarre cast here is needed for newer gccs
0133     transform(varname.begin(), varname.end(), varname.begin(), (int (*)(int)) tolower);
0134     for (const auto & j : addvarname)
0135     {
0136       std::string thisvar = varname + j;
0137       for (unsigned int i = DEFAULTCOLUMNS + 1; i <= nocolumn; i++)
0138       {
0139         if (meta->getColumnName(i) == thisvar)
0140         {
0141           if (verbosity > 0)
0142           {
0143             std::cout << thisvar << " is in table" << std::endl;
0144           }
0145           goto foundvar;
0146         }
0147       }
0148       cmd.str("");
0149       cmd << "ALTER TABLE " << table << " ADD COLUMN "
0150           << thisvar << " real";
0151       if (verbosity > 0)
0152       {
0153         std::cout << "executing sql command: " << cmd.str() << std::endl;
0154       }
0155
0156       try
0157       {
0158         odbc::Statement* chgtable = con->createStatement();
0159         iret = chgtable->executeUpdate(cmd.str());
0160         delete chgtable;
0161       }
0162       catch (odbc::SQLException& e)
0163       {
0164         std::cout << "Table " << table << " update failed" << std::endl;
0165         std::cout << "Message: " << e.getMessage() << std::endl;
0166       }
0167
0168     foundvar:
0169       continue;
0170     }
0171   }
0172   delete rs;
0173   delete stmt;
0174   // check columns
0175   return iret;
0176 }
0177
0178 int OnlMonDBodbc::DropTable(const std::string& name)
0179 {
0180   if (GetConnection())
0181   {
0182     return -1;
0183   }
0184   std::string tablename = name;
0185   // table names are lower case only, so convert string to lowercase
0186   // The bizarre cast here is needed for newer gccs
0187   transform(tablename.begin(), tablename.end(), tablename.begin(), (int (*)(int)) tolower);
0188   std::cout << con->getMetaData()->getDatabaseProductVersion() << std::endl;
0189   std::cout << con->getCatalog() << std::endl;
0190   odbc::Statement* stmt = con->createStatement();
0191   std::ostringstream cmd;
0192   cmd << "DROP TABLE " << tablename;
0193
0194   int iret = stmt->executeUpdate(cmd.str());
0195   std::cout << "iret: " << iret << std::endl;
0196   return iret;
0197 }
0198
0199 void OnlMonDBodbc::Dump()
0200 {
0201   if (GetConnection())
0202   {
0203     return;
0204   }
0205
0206   //std::cout << con->getMetaData()-> getDatabaseProductVersion() << std::endl;
0207   odbc::Statement* stmt = con->createStatement();
0208   std::ostringstream cmd;
0209   cmd << "SELECT * FROM " << table;
0210   std::cout << "Executing " << cmd.str() << std::endl;
0211   odbc::ResultSet* rs = stmt->executeQuery(cmd.str());
0212   Dump(rs);
0213   return;
0214 }
0215
0216 void OnlMonDBodbc::Dump(odbc::ResultSet* rs) const
0217 {
0218   odbc::ResultSetMetaData* meta = rs->getMetaData();
0219   unsigned int ncolumn = meta->getColumnCount();
0220   while (rs->next())
0221   {
0222     odbc::Timestamp id = rs->getTimestamp(1);
0223     int Name = rs->getInt(2);
0224     std::cout << "TimeStamp: " << (id.toString()) << " Run #= " << Name << std::endl;
0225     for (unsigned int i = DEFAULTCOLUMNS + 1; i <= ncolumn; i++)
0226     {
0227       float rval = rs->getFloat(i);
0228       if (rs->wasNull())
0229       {
0230         std::cout << meta->getColumnName(i) << ": NULL" << std::endl;
0231       }
0232       else
0233       {
0234         std::cout << meta->getColumnName(i) << ": " << rval << std::endl;
0235       }
0236     }
0237   }
0238   try
0239   {
0240     rs->first();
0241   }
0242   catch (odbc::SQLException& e)
0243   {
0244     std::cout << "Exception caught" << std::endl;
0245     std::cout << "Message: " << e.getMessage() << std::endl;
0246   }
0247
0248   return;
0249 }
0250
0251 int OnlMonDBodbc::Info(const char* name)
0252 {
0253   if (GetConnection())
0254   {
0255     return -1;
0256   }
0257
0258   std::cout << con->getMetaData()->getDatabaseProductVersion() << std::endl;
0259   // if no argument is given print out list of tables
0260   if (!name)
0261   {
0262     std::cout << "Driver: " << con->getMetaData()->getDriverName() << std::endl;
0263     std::cout << "User: " << con->getMetaData()->getUserName() << std::endl;
0264     std::string catalog = con->getMetaData()->getCatalogTerm();
0265     std::cout << "Catalog: " << catalog << std::endl;
0266     std::string schemapattern = con->getMetaData()->getSchemaTerm();
0267     std::cout << "Schema: " << schemapattern << std::endl;
0268     std::string tablenamepattern = con->getMetaData()->getTableTerm();
0269     std::cout << "Table: " << tablenamepattern << std::endl;
0270     std::vector<std::string> types;
0271     odbc::ResultSet* rs = con->getMetaData()->getTableTypes();
0272     while (rs->next())
0273     {
0274       std::cout << "1: " << rs->getString(1) << std::endl;
0275       std::cout << "2: " << rs->getString(2) << std::endl;
0276       std::cout << "3: " << rs->getString(3) << std::endl;
0277       std::cout << "4: " << rs->getString(4) << std::endl;
0278       std::cout << "5: " << rs->getString(5) << std::endl;
0279     }
0280     odbc::ResultSet* rs1 = con->getMetaData()->getTables(catalog, schemapattern, tablenamepattern, types);
0281
0282     std::cout << "rs1: " << rs1 << std::endl;
0283     //        std::cout << rs->getMetaData()->getTableName(1) << std::endl;
0284     while (rs1->next())
0285     {
0286       std::cout << "Table: " << rs->getString("TABLE_CAT") << std::endl;
0287       std::cout << "Table: " << rs->getString(3) << std::endl;
0288     }
0289   }
0290   else
0291   {
0292     odbc::Statement* stmt = con->createStatement();
0293     std::string tablename = name;
0294     // table names are lower case only, so convert string to lowercase
0295     // The bizarre cast here is needed for newer gccs
0296     transform(tablename.begin(), tablename.end(), tablename.begin(), (int (*)(int)) tolower);
0297     std::ostringstream cmd;
0298     cmd << "select * from " << tablename;
0299     odbc::ResultSet* rs = stmt->executeQuery("select * from inttest");
0300     std::cout << rs->getMetaData()->getColumnCount() << std::endl;
0301     while (rs->next())
0302     {
0303       int id = rs->getInt(1);
0304       float Name = rs->getFloat(2);
0305       std::cout << "Row: " << rs->getRow() << ", id=" << id << "   Name= " << Name << std::endl;
0306     }
0307   }
0308   return 0;
0309 }
0310
0311 void OnlMonDBodbc::identify() const
0312 {
0313   std::cout << "DB Name: " << dbname << std::endl;
0314   std::cout << "DB Owner: " << dbowner << std::endl;
0315   std::cout << "DB Pwd: " << dbpasswd << std::endl;
0316   std::cout << "DB table: " << table << std::endl;
0317   return;
0318 }
0319
0320 int OnlMonDBodbc::AddRow(const time_t ticks, const int runnumber, const std::map<const std::string, OnlMonDBVar*>& varmap)
0321 {
0322   // bail out when restarted before a run was taken - run=0, ticks=0
0323   if (ticks == 0 || runnumber <= 0)
0324   {
0325     return -1;
0326   }
0327   std::map<const std::string, OnlMonDBVar*>::const_iterator iter;
0328   int iret = 0;
0329   int minutesinterval = MINUTESINTERVAL;
0330   std::ostringstream cmd, cmd1, datestream;
0331   odbc::Timestamp thistime(ticks);
0332   odbc::Timestamp mintime;
0333   odbc::Timestamp maxtime;
0334
0335   if (GetConnection())
0336   {
0337     return -1;
0338   }
0339
0340   odbc::Statement* query = con->createStatement();
0341 searchagain:
0342   mintime.setTime(ticks - minutesinterval * 60);
0343   maxtime.setTime(ticks + minutesinterval * 60);
0344 #ifdef VERBOSE
0345
0346   std::cout << "mintime stp: " << mintime.toString() << std::endl;
0347   std::cout << "maxtime stp: " << maxtime.toString() << std::endl;
0348 #endif
0349
0350   cmd.str("");
0351   datestream.str("");
0352   datestream << "date > '" << mintime.toString()
0353              << "' and date < '" << maxtime.toString() << "'";
0354   cmd << "SELECT COUNT(*) FROM " << table << " WHERE run = "
0355       << runnumber << " and " << datestream.str();
0356 #ifdef VERBOSE
0357
0358   std::cout << "cmd: " << cmd.str() << std::endl;
0359 #endif
0360
0361   odbc::ResultSet* rs = nullptr;
0362
0363   try
0364   {
0365     rs = query->executeQuery(cmd.str());
0366   }
0367   catch (odbc::SQLException& e)
0368   {
0369     std::cout << "Exception caught" << std::endl;
0370     std::cout << "Message: " << e.getMessage() << std::endl;
0371     std::cout << "sql cmd: " << cmd.str() << std::endl;
0372   }
0373   int haverow = 0;
0374   if (rs)
0375   {
0376     while (rs->next())
0377     {
0378       haverow = rs->getInt(1);
0379 #ifdef VERBOSE
0380
0381       std::cout << "found rows: " << haverow << std::endl;
0382 #endif
0383     }
0384     delete rs;
0385   }
0386   if (haverow > 1)
0387   {
0388     minutesinterval = minutesinterval / 2;
0389     goto searchagain;
0390   }
0391   else if (haverow == 1)
0392   {
0393     cmd.str("");
0394     cmd << "SELECT * FROM " << table << " WHERE " << datestream.str();
0395 #ifdef VERBOSE
0396
0397     std::cout << "command: " << cmd.str() << std::endl;
0398 #endif
0399
0400     rs = query->executeQuery(cmd.str());
0401     // if the following works, the query returned one or more rows
0402     // in the given time interval
0403     while (rs->next())
0404     {
0405       cmd.str("");
0406       for (iter = varmap.begin(); iter != varmap.end(); ++iter)
0407       {
0408         if (iter->second->wasupdated())
0409         {
0410           std::string varqualname = iter->first + addvarname[2];
0411           float varqual = iter->second->GetVar(2);
0412           float sqlvarqual = rs->getFloat(varqualname);
0413           if (varqual > sqlvarqual || rs->wasNull())
0414           {
0415             odbc::Statement* upd = con->createStatement();
0416             // if there is only 1 row we do not need to worry which one to update
0417             for (unsigned int j = 0; j < 3; j++)
0418             {
0419               cmd.str("");
0420               cmd << "UPDATE " << table << " SET "
0421                   << iter->first << addvarname[j] << " = "
0422                   << iter->second->GetVar(j) << " WHERE "
0423                   << datestream.str();
0424 #ifdef VERBOSE
0425
0426               std::cout << "Command: " << cmd.str() << std::endl;
0427 #endif
0428
0429               int iret2 = upd->executeUpdate(cmd.str());
0430               if (!iret2)
0431               {
0432                 std::cout << __PRETTY_FUNCTION__ << "Update failed please send mail to pinkenburg@bnl.gov"
0433                           << std::endl;
0434                 std::cout << "And include the macro and the following info" << std::endl;
0435                 std::cout << "TableName: " << table << std::endl;
0436                 std::cout << "Variable: " << iter->first << addvarname[j] << std::endl;
0437                 std::cout << "Value: " << iter->second->GetVar(j) << std::endl;
0438                 std::cout << "TimeStamp: " << rs->getTimestamp(1).toString() << std::endl;
0439               }
0440             }
0441           }
0442         }
0443       }
0444     }
0445   }
0446   else
0447   {
0448     cmd.str("");
0449     cmd << "INSERT INTO " << table << "(date, run";
0450     cmd1 << "VALUES('" << thistime.toString() << "'," << runnumber;
0451     int newval = 0;
0452     for (iter = varmap.begin(); iter != varmap.end(); ++iter)
0453     {
0454       if (iter->second->wasupdated())
0455       {
0456         for (unsigned int j = 0; j < 3; j++)
0457         {
0458           cmd << ", " << iter->first << addvarname[j];
0459           cmd1 << ", " << iter->second->GetVar(j);
0460           newval++;
0461         }
0462       }
0463     }
0464     if (!newval)
0465     {
0466       printf("No updated values\n");
0467       return -1;
0468     }
0469     cmd << ") ";
0470     cmd1 << ")";
0471     cmd << cmd1.str();
0472
0473 #ifdef VERBOSE
0474
0475     std::cout << cmd.str() << std::endl;
0476 #endif
0477
0478     odbc::Statement* stmt = con->createStatement();
0479     try
0480     {
0481       stmt->executeUpdate(cmd.str());
0482     }
0483     catch (odbc::SQLException& e)
0484     {
0485       const std::string& errmsg = e.getMessage();
0486       if (errmsg.find("Cannot insert a duplicate key into unique index") != std::string::npos)
0487       {
0488 #ifdef VERBOSE
0489         std::cout << "Identical entry already in DB" << std::endl;
0490 #endif
0491         iret = 0;
0492       }
0493       else
0494       {
0495         std::cout << __PRETTY_FUNCTION__ << " DB Error in execute stmt: " << e.getMessage() << std::endl;
0496         std::ofstream savesql("lostupdates.sql", std::ios_base::app);
0497         savesql << cmd.str() << std::endl;
0498         savesql.close();
0499         iret = -1;
0500       }
0501     }
0502   }
0503   return iret;
0504 }
0505
0506 int OnlMonDBodbc::GetVar(const time_t begin, const time_t end, const std::string& varname, std::vector<time_t>& timestp, std::vector<int>& runnumber, std::vector<float>& var, std::vector<float>& varerr)
0507 {
0508   if (GetConnection())
0509   {
0510     return DBNOCON;
0511   }
0512   int iret = 0;
0513
0514   odbc::Statement* query = con->createStatement();
0515   odbc::Timestamp mintime(begin);
0516   odbc::Timestamp maxtime(end);
0517   std::string varnameerr = varname + addvarname[1];
0518   std::ostringstream cmd, datestream;
0519   datestream << "date > '" << mintime.toString()
0520              << "' and date < '" << maxtime.toString() << "'";
0521   cmd << "SELECT COUNT(*) FROM " << table << " WHERE " << datestream.str();
0522
0523 #ifdef VERBOSE
0524   std::cout << "command: " << cmd.str() << std::endl;
0525 #endif
0526
0527   odbc::ResultSet* rs;
0528   try
0529   {
0530     rs = query->executeQuery(cmd.str());
0531   }
0532   catch (odbc::SQLException& e)
0533   {
0534     std::cout << "Exception caught, probably your table "
0535               << table
0536               << " does not exist" << std::endl;
0537     std::cout << "Message: " << e.getMessage() << std::endl;
0538     varerr.resize(0);
0539     var.resize(0);
0540     timestp.resize(0);
0541     runnumber.resize(0);
0542     return -1;
0543   }
0544   int nrows = 0;
0545   while (rs->next())
0546   {
0547     nrows = rs->getInt(1);
0548   }
0549   timestp.resize(nrows);
0550   runnumber.resize(nrows);
0551   var.resize(nrows);
0552   varerr.resize(nrows);
0553   if (!nrows)
0554   {
0555     return DBNOENTRIES;
0556   }
0557   delete rs;
0558   cmd.str("");
0559   // get data in ascending date order (earliest time first)
0560   cmd << "SELECT date, run, " << varname << ", " << varnameerr
0561       << " FROM " << table << " WHERE "
0562       << datestream.str() << " ORDER BY date ASC";
0563 #ifdef VERBOSE
0564
0565   std::cout << "Command: " << cmd.str() << std::endl;
0566 #endif
0567
0568   try
0569   {
0570     rs = query->executeQuery(cmd.str());
0571   }
0572   catch (odbc::SQLException& e)
0573   {
0574     std::cout << "Exception caught, probably your variable "
0575               << varname << " or the table " << table
0576               << " does not exist" << std::endl;
0577     std::cout << "Message: " << e.getMessage() << std::endl;
0578     varerr.resize(0);
0579     var.resize(0);
0580     timestp.resize(0);
0581     runnumber.resize(0);
0582     return -3;
0583   }
0584   unsigned int index = 0;
0585   int filledrows = nrows;
0586   while (rs->next())
0587   {
0588     float val = rs->getFloat(3);
0589     if (rs->wasNull())
0590     {
0591       filledrows--;
0592       continue;
0593     }
0594     timestp[index] = rs->getTimestamp(1).getTime();
0595     runnumber[index] = rs->getInt(2);
0596     var[index] = val;
0597     varerr[index] = rs->getFloat(4);
0598     index++;
0599   }
0600   if (filledrows != nrows)
0601   {
0602     varerr.resize(filledrows);
0603     var.resize(filledrows);
0604     timestp.resize(filledrows);
0605     runnumber.resize(filledrows);
0606   }
0607   return iret;
0608 }
0609
0610 int OnlMonDBodbc::GetConnection()
0611 {
0612   if (con)
0613   {
0614     return 0;
0615   }
0616   try
0617   {
0618     con = odbc::DriverManager::getConnection(dbname.c_str(), dbowner.c_str(), dbpasswd.c_str());
0619   }
0620   catch (odbc::SQLException& e)
0621   {
0622     std::cout << __PRETTY_FUNCTION__
0623               << " Exception caught during DriverManager::getConnection" << std::endl;
0624     std::cout << "Message: " << e.getMessage() << std::endl;
0625     if (con)
0626     {
0627       delete con;
0628       con = nullptr;
0629     }
0630     return -1;
0631   }
0632   printf("opened DB connection\n");
0633   return 0;
0634 }