AMolecularSQLManager.cc

Go to the documentation of this file.

#include <sstream>
#include <boost/lexical_cast.hpp>

#include <atm/AMolecularSQLManager.h>
#include <fwk/CentralConfig.h>
#include <det/Detector.h>
#include <utl/ErrorLogger.h>
#include <utl/TimeStamp.h>
#include <utl/Reader.h>
#include <utl/AugerException.h>
#include <utl/StringCompare.h>

using namespace std;
using namespace atm;
using namespace det;
using namespace utl;


REGISTER_A_MANAGER("AMolecularSQLManager", AMolecularSQLManager);


VManager::Status
AMolecularSQLManager::GetDBResFundamental(const string& tableName,
                                          const string& columnName,
                                          const IndexMap& componentIndex)
  const
{
  const utl::TimeStamp detTime = Detector::GetInstance().GetTime();

  ostringstream query;

  // aerosol table is a special case, as it requires checking software version
  if (tableName == "molecular") {

    // for the molecular table, the profile_type must be specified
    //
    if (componentIndex.find("profile_type_id") == componentIndex.end()) {
      ERROR("Request for data in molecular table does not specify the profile_type_id.");
      throw DataNotFoundInDBException("molecular", "profile_type_id", componentIndex,
                                      boost::lexical_cast<string>(Detector::GetInstance().GetTime()));
    }

    // Use the fast query for GDAS and weather station date. This query DOES
    // work for all profile types in the NEW database Atm_Molecular_1_A, but NOT
    // for the monthly models in the OLD database Atm_Molecular_0_A.

    const string profileTypeName = componentIndex.find("profile_type_name")->second;
    if (StringEquivalent(profileTypeName, "weather station")) {

      if (StringEquivalent(fDatabaseSoftwareVersion, "default")) {
        // do NOT check software version
        query
          << "SELECT molecular_id "
             "FROM molecular "
             "WHERE start_time = "
             "(SELECT MAX(start_time) FROM molecular "
               "WHERE profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
               "AND start_time <= \"" << detTime.GetGPSSecond() << "\") "
             "AND profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
             "ORDER BY last_modified DESC LIMIT 1";
      } else {
        // DO check software version
        query
          << "SELECT molecular_id "
             "FROM molecular, software "
             "WHERE start_time = "
             "(SELECT MAX(start_time) FROM molecular "
               "WHERE profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
               "AND molecular.software_id = software.software_id "
               "AND software.software_version = \"" << fDatabaseSoftwareVersion << "\" "
               "AND start_time <= \"" << detTime.GetGPSSecond() << "\") "
             "AND profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
             "ORDER BY molecular.last_modified DESC LIMIT 1";
      }

    } else if (StringEquivalent(profileTypeName, "Malargue GDAS data")) {

      if (StringEquivalent(fDatabaseSoftwareVersion, "default")) {
        // do NOT check software version
        query
          << "SELECT molecular_id "
             "FROM molecular "
             "WHERE start_time = "
             "(SELECT MAX(start_time) FROM molecular "
               "WHERE profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
               "AND start_time <= \"" << detTime.GetGPSSecond() << "\" "
               "AND end_time >= \"" << detTime.GetGPSSecond() << "\") "
             "AND profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
             "ORDER BY last_modified DESC LIMIT 1";
      } else {
        // DO check software version
        query
          << "SELECT molecular_id "
             "FROM molecular, software "
             "WHERE start_time = "
             "(SELECT MAX(start_time) FROM molecular "
               "WHERE profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
               "AND molecular.software_id = software.software_id "
               "AND software.software_version = \"" << fDatabaseSoftwareVersion << "\" "
               "AND start_time <= \"" << detTime.GetGPSSecond() << "\" "
               "AND end_time >= \"" << detTime.GetGPSSecond() << "\") "
             "AND profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
             "ORDER BY molecular.last_modified DESC LIMIT 1";
      }

    } else {

      if (StringEquivalent(fDatabaseSoftwareVersion, "default")) {
        // do NOT check software version
        query
          << "SELECT molecular_id "
             "FROM molecular "
             "WHERE profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
             "AND start_time <= \"" << detTime.GetGPSSecond() << "\" "
             "AND (end_time > \"" << detTime.GetGPSSecond() << "\" OR end_time IS NULL) "
             "ORDER BY last_modified DESC, start_time DESC LIMIT 1";
      } else {
        // DO check software version
        query
          << "SELECT molecular_id "
             "FROM molecular, software "
             "WHERE profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
             "AND start_time <= \"" << detTime.GetGPSSecond() << "\" "
             "AND (end_time > \"" << detTime.GetGPSSecond() << "\" OR end_time IS NULL) "
             "AND molecular.software_id = software.software_id "
             "AND software.software_version = \"" << fDatabaseSoftwareVersion << "\" "
             "ORDER BY molecular.last_modified DESC, start_time DESC LIMIT 1";
      }

    } // end check profile name

  } else if (tableName == "zone") {

    // special case of column id  -- this has to be fetched from the molecular_zone table
    if (columnName == "zone_id") {
      query
        << "SELECT " << columnName << " "
           "FROM molecular_zone "
           "WHERE molecular_id = " << componentIndex.find("molecular_id")->second;
    } else {
      query
        << "SELECT " << columnName << " "
           "FROM zone "
           "WHERE zone_id = " << componentIndex.find("zone_id")->second;
    }

  } else {

    // issue queries in form :
    //  SELECT columnName FROM tableName WHERE
    //   componentIndex[0].first = componentIndex[0].second AND
    //   componentIndex[1].first = componentIndex[1].second
    //    (etc)
    //
    query
      << "SELECT " << columnName << " "
         "FROM " << tableName << " "
         "WHERE " << MergeIndexMap(componentIndex);

  }

  return Query(query, QueryInfoMessage(tableName, columnName));
}


VManager::Status
AMolecularSQLManager::GetDBResVector(const string& tableName,
                                     const string& columnName,
                                     const IndexMap& componentIndex)
  const
{

  const utl::TimeStamp detTime = Detector::GetInstance().GetTime();

  // special case: get all zones valid for a particular molecular_id
  //
  if (tableName == "zone") {

    // index map must specify the molecular_id
    if (componentIndex.find("molecular_id") == componentIndex.end()) {
      ERROR("no molecular_id specified when looking up zone names");
      throw DataNotFoundInDBException("zone", "molecular_id", componentIndex,
                                      boost::lexical_cast<string>(Detector::GetInstance().GetTime()));
    }

    ostringstream query;
    query
      << "SELECT zone." << columnName << " FROM zone, molecular_zone "
         "WHERE " << MergeIndexMap(componentIndex) << " "
         "AND molecular_zone.zone_id = zone.zone_id";

    return Query(query, string("vector from ") + QueryInfoMessage(tableName, columnName));

  } else if (tableName == "molecular") {

    // special case (hack): get all the zones for this timestamp. This is done in rather a
    // hackish way, since the molecular db is not filled properly (ie. the zone tables
    // are not used in the way they were originally intended).

    ostringstream query;

    if (StringEquivalent(fDatabaseSoftwareVersion, "default")) {
      // do NOT check software version
      query
        << "SELECT molecular_id "
           "FROM molecular "
           "WHERE start_time = "
           "(SELECT MAX(start_time) FROM molecular "
             "WHERE profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
             "AND start_time <= \"" << detTime.GetGPSSecond() << "\") "
           "AND profile_type_id = \"" << componentIndex.find("profile_type_id")->second << '"';
    } else {
      // DO check software version
      query
        << "SELECT molecular_id "
           "FROM molecular, software "
           "WHERE start_time = (SELECT MAX(start_time) FROM molecular "
           "WHERE profile_type_id = \"" << componentIndex.find("profile_type_id")->second << "\" "
           "AND molecular.software_id = software.software_id "
           "AND software.software_version = \"" << fDatabaseSoftwareVersion << "\" "
           "AND start_time <= \"" << detTime.GetGPSSecond() << "\") "
           "AND profile_type_id = \"" << componentIndex.find("profile_type_id")->second << '"';
    }

    return Query(query, string("vector from ") + QueryInfoMessage(tableName, columnName));

  } else {

    ostringstream query;
    query
      << "SELECT " << columnName << " "
         "FROM " << tableName << " "
         "WHERE " << MergeIndexMap(componentIndex);

    return Query(query, string("vector from ") + QueryInfoMessage(tableName, columnName));

  }
}


VManager::Status
AMolecularSQLManager::GetDBDataCheckTranspose(vector<double>& returnData,
                                              const string& tableName,
                                              const string& columnName,
                                              const IndexMap& componentIndex)
  const
{
  const unsigned int n = FindComponent<unsigned int>("transpose", 0, componentIndex);
  if (!n)
    return VSQLManager::GetDBData(returnData, tableName, columnName, componentIndex);

  IndexMap newComponentIndex = componentIndex;
  newComponentIndex.erase("transpose");

  ostringstream query;
  query
    << "SELECT " << columnName << " "
       "FROM " << tableName << " "
       "WHERE " << MergeIndexMap(newComponentIndex);

  if (Query(query, string("transposed vector from ") + QueryInfoMessage(tableName, columnName)) != eFound)
    return eNotFound;

  return FetchRow(returnData);
}