VSQLManager_MySQL.cc

Go to the documentation of this file.

#include <det/VSQLManager_MySQL.h>
#include <utl/Reader.h>
#include <utl/AugerUnits.h>
#include <fwk/CentralConfig.h>
#include <fwk/CommandLineOptions.h>

using namespace std;
using namespace utl;


namespace det {

  int VSQLManager::fgGlobalLogQueries = 0;


  void
  VSQLManager::Close()
  {
    if (fIsInitialized && fMySQL) {
      const double time = fTime.GetTime();
      if (time) {
        ostringstream info;
        info << "Total time spent for database '" << fDatabaseName
             << "' queries is " << time/second << " sec.";
        INFO(info);
      }
      FreeResult();
      mysql_close(fMySQL);
      fMySQL = nullptr;
    }
    if (fLogQueryStream) {
      fLogQueryStream->close();
      delete fLogQueryStream;
      fLogQueryStream = nullptr;
    }
    fIsInitialized = false;
  }


  void
  VSQLManager::Init(const std::string& configLink)
  {
    VManager::Init(configLink);

    fBranch.GetChild("databaseName").GetData(fDatabaseName);
    fBranch.GetChild("softwareVersion").GetData(fDatabaseSoftwareVersion);
    if (fgGlobalLogQueries) {
      fLogQueries = fgGlobalLogQueries;
    } else {
      Branch logQueriesB = fBranch.GetChild("logQueries");
      if (logQueriesB)
        logQueriesB.GetData(fLogQueries);
    }

    if (fLogQueries) {
      ostringstream fname;
      fname << "mysql-" << fDatabaseName << '-' << getpid() << ".query";
      fLogQueryStream = new ofstream(fname.str().c_str());
      if (fLogQueryStream)
        INFO(string("Database queries will be logged into file '") +
             fname.str() + "'.");
    }
  }


  void
  VSQLManager::Connect()
    const
  {
    if (!fIsInitialized) {
      const string err = "MySQL Manager not initialized.";
      ERROR(err);
      throw IOFailureException(err);
    }

    if (fMySQL) {
      WARNING("MySQL Manager already connected!");
      return;
    }

    fwk::CentralConfig& cc = *fwk::CentralConfig::GetInstance();
    Branch serverListB = cc.GetTopBranch("databaseServers");
    if (!serverListB) {
      const string err = "VSQLManager cannot access databaseServers configuration file!";
      ERROR(err);
      throw IOFailureException(err);
    }

    for (Branch serverB = serverListB.GetChild("serverList").GetFirstChild();
         serverB; serverB = serverB.GetNextSibling()) {

      const vector<string> hostName = serverB.GetChild("hostName").Get<vector<string>>();

      fDatabaseUserName = serverB.GetChild("userName").Get<string>();
      fDatabasePassword = serverB.GetChild("password").Get<string>();
      Branch portB = serverB.GetChild("port");
      if (portB)
        portB.GetData(fPort);
      else
        fPort = 3306;

      for (const auto& hn : hostName) {

        fDatabaseServer = hn;

        fMySQL = mysql_init(nullptr);
        //mysql_options(fMySQL, MYSQL_OPT_CONNECT_TIMEOUT, "5");
        if (mysql_real_connect(fMySQL, fDatabaseServer.c_str(),
                               fDatabaseUserName.c_str(),
                               fDatabasePassword.c_str(),
                               fDatabaseName.c_str(),
                               fPort, nullptr, 0)) {
          ostringstream info;
          info << "Connected to database '" << fDatabaseName << "' "
                  "on host " << mysql_get_host_info(fMySQL);
          INFO(info);
          if (fLogQueryStream)
            *fLogQueryStream << "* * " << info.str() << endl;
          fIsInitialized = true;
          break;
        }

        ostringstream info;
        info << "Connection to the database '" << fDatabaseName << "' "
                "on " << fDatabaseUserName /*<< ':' << fDatabasePassword*/
             << '@' << fDatabaseServer << ':' << fPort <<" failed. "
                "MySQL reports the following error: \"" << mysql_error(fMySQL) << "\". "
                "Trying an alternative database configuration, if one has been specified...";
        INFO(info);
        if (fLogQueryStream) {
          *fLogQueryStream << "* * " << info.str() << endl;
          fLogQueryStream->close();
          delete fLogQueryStream;
          fLogQueryStream = nullptr;
        }

        mysql_close(fMySQL);
        fMySQL = nullptr;
        fIsInitialized = false;

      }

      if (fIsInitialized)
        break;

    }

    if (!fIsInitialized) {
      ostringstream err;
      err << "Could not connect to the requested database: '" << fDatabaseName
          << "' on any of the specified hosts. Giving up.";
      ERROR(err);
      throw IOFailureException(err.str());
    }
  }


  VManager::Status
  VSQLManager::Query(const string& query, const string& what)
    const
  {
    if (!fMySQL)
      Connect();

    fTime.Start();
    const int ret = mysql_query(fMySQL, query.c_str());
    const double elapsedTime = fTime.Stop();

    if (fLogQueryStream)
      *fLogQueryStream << IsReportingErrors() << ' '
                       << elapsedTime/second << '\t' << query.c_str()
                       << (ret ? " -> FAILED\n" : " -> OK") << flush;

    if (ret) {
      if (IsReportingErrors()) {
        ostringstream err;
        err << "Query '" << query << "' ";
        if (!what.empty())
          err << "for " << what << ' ';
        err << "failed. "
               "Reason: \"" << mysql_error(fMySQL) << '"';
        ERROR(err);
      }
      return eNotFound;
    }

    FreeResult();

    fMySQLResult = mysql_store_result(fMySQL);

    if (!fMySQLResult) {
      if (fLogQueryStream)
        *fLogQueryStream << ": NO_RESULT\n";
      if (!what.empty() && IsReportingErrors()) {
        ostringstream warn;
        warn << "Query for " << what << " "
                "returned no results.";
        WARNING(warn);
      }
      return eNotFound;
    }

    // log all rows
    if (fLogQueryStream && fLogQueries > 1) {
      *fLogQueryStream << ": (";
      const int nCols = NumFields();
      if (nCols) {
        MYSQL_ROW row;
        while ((row = FetchRow())) {
          *fLogQueryStream << " ( ";
          if (row[0])
            *fLogQueryStream << '\'' << row[0] << '\'';
          else
            *fLogQueryStream << "NULL*";
          for (int i = 1; i < nCols; ++i)
            if (row[i])
              *fLogQueryStream << ", '" << row[i] << '\'';
            else
              *fLogQueryStream << ", NULL*";
          *fLogQueryStream << " )";
        }
        FreeResult();
        // redo query since we used all rows
        mysql_query(fMySQL, query.c_str());
        fMySQLResult = mysql_store_result(fMySQL);
      }
      *fLogQueryStream << " )" << endl;
    }

    return eFound;
  }


  string
  VSQLManager::MergeIndexMap(const IndexMap& componentIndex)
  {
    VManager::IndexMap::const_iterator it = componentIndex.begin();
    if (it == componentIndex.end())
      return "";
    ostringstream res;
    res << it->first << " = " << it->second;
    for (++it; it != componentIndex.end(); ++it)
      res << " AND " << it->first << " = " << it->second;
    return res.str();
  }

}