DbiStatement.cxx

Go to the documentation of this file.

#include <iostream>
#include <sstream>
#include <string>
#include <vector>

#include "TString.h"

#include "DatabaseInterface/DbiStatement.h"
#include "DatabaseInterface/DbiTableMetaData.h"
#include "LeakChecker/Lea.h"
#include "MessageService/MsgService.h"
#include "Util/UtilString.h"

ClassImp(DbiStatement)


//   Definition of static data members
//   *********************************

CVSID("$Id: DbiStatement.cxx,v 1.37 2007/07/05 09:26:34 west Exp $");

//    Definition of all member functions (static or otherwise)
//    *******************************************************
//
//    -  ordered: ctors, dtor, operators then in alphabetical order.

//.....................................................................

DbiStatement::DbiStatement(DbiConnection& conDb) :
fConDb(conDb),
fDbType(conDb.GetDbType())
{
//
//
//  Purpose:  Constructor
//
//  Arguments:  None.
//
//  Return:   
//
//  conDb    in    The connection associated with the statement.
//

  LEA_CTOR    //Leak Checker

  MSG("Dbi", Msg::kVerbose) << "Creating DbiStatement" << endl;
  fConDb.ConnectStatement();

}

//.....................................................................

DbiStatement::~DbiStatement() {
//
//
//  Purpose: Destructor

  LEA_DTOR    //Leak Checker
  MSG("Dbi", Msg::kVerbose) << "Destroying DbiStatement" << endl;

  fConDb.DisConnectStatement();
}

//.....................................................................

TSQLStatement* DbiStatement::CreateProcessedStatement(const TString& sql /* ="" */) {

// Attempt to create a processed statement (caller must delete).  Return 0 if failure.

  TSQLStatement* stmt = fConDb.CreatePreparedStatement(sql.Data());
  if ( ! stmt ) {
    this->AppendExceptionLog(fConDb);
    return 0;
  }
  if ( stmt->Process() ) return stmt;
  this->AppendExceptionLog(stmt);
  delete stmt;
  stmt = 0;
  return 0;

}


//.....................................................................

TSQLStatement* DbiStatement::ExecuteQuery( const TString& sql) {
//
//
//  Purpose:  Translate SQL if required and execute. 
//  Return:   TSQLStatement with Process() and StoreResult() already performed.

  this->ClearExceptionLog();

  std::list<TString> sqlList(this->TranslateSQL(sql));
  std::list<TString>::const_iterator itr(sqlList.begin()), itrEnd(sqlList.end());

  TSQLStatement* stmt = 0;
  while (itr != itrEnd) {
    const TString& sql = *itr++;
    MSG("Dbi",Msg::kSynopsis) << "SQL:" << fConDb.GetDbName() << ":" << sql << endl;
    delete stmt;
    stmt = this->CreateProcessedStatement(sql);
    if ( ! stmt ) return 0;
  }
  // Store results from last SQL command (when multiple commands are generated
  // the last will be the one that performs the query).
  if ( stmt && ! stmt->StoreResult() ) {
    this->AppendExceptionLog(stmt);
    delete stmt;
    stmt = 0;
  }

  // Final sanity check: If there is a statement then the exception log should still
  // be clear otherwise it should not be.
  if ( stmt ) {
    if ( ! fExceptionLog.IsEmpty() ) {
      delete stmt;
      stmt = 0;
    }
  }
  else if ( fExceptionLog.IsEmpty() ) {
    ostringstream oss;
    oss << "Unknown failure (no execption but no TSQLStatement either executing " << sql;
    fExceptionLog.AddEntry(oss.str().c_str());
  }
  return stmt;
  
}

//.....................................................................

Bool_t DbiStatement::ExecuteUpdate( const TString& sql) {
//
//
//  Purpose:  Translate SQL if required and Execute.
//
//  Return true if all updates successful.


  this->ClearExceptionLog();

  std::list<TString> sqlList(this->TranslateSQL(sql));
  std::list<TString>::const_iterator itr(sqlList.begin()), itrEnd(sqlList.end());

  while (itr != itrEnd) {
    const TString& sql = *itr++;
    MSG("Dbi",Msg::kSynopsis) << "SQL:" << fConDb.GetDbName() << ":" << sql << endl;
    bool ok = fConDb.GetServer()->Exec(sql.Data());
    if ( ! ok ) {
      fConDb.RecordException();
      this->AppendExceptionLog(fConDb);
      return false;
    }
  }
  return fExceptionLog.IsEmpty();
  
}

//.....................................................................

Bool_t DbiStatement::PrintExceptions(Int_t level) const {

//  Purpose:  Print accumulated exceptions at supplied Msg level,
//            add them to the Global Exception Log if level >= kWarning
//            and return true if there are any.

  const DbiExceptionLog& el(this->GetExceptionLog());
  if ( el.IsEmpty() ) return false;
  MSG("Dbi", level) << el;
  if ( level >= Msg::kWarning )  DbiExceptionLog::GetGELog().AddLog(el);
  return true;

}

//.....................................................................

std::list<TString>  DbiStatement::TestTranslateSQL(const TString& sql, Dbi::DbTypes type) {
//
//
//  Purpose:  Debugging aid: Test translate from MySQL to other dialects of SQL.
//
//  Arguments: 
//    sql          in    The string to be translated
//    type         in    The database type to translate to.
//
//  Return:    A list of translated SQL
//             NB A single MySQL command can generate multiple ORACLE ones.

  Dbi::DbTypes fDbTypeSave = fDbType;
  fDbType = type;
  std::list<TString> sqlTrans(this->TranslateSQL(sql));
  fDbType = fDbTypeSave;
  return sqlTrans;

}

//.....................................................................

std::list<TString>  DbiStatement::TranslateSQL(const TString& sql) {
//
//
//  Purpose:  Translate from MySQL to other dialects of SQL.
//
//  Arguments: 
//    sql          in    The string to be translated
//
//
//  Return:    A list of translated SQL
//             NB A single MySQL command can generate multiple ORACLE ones.
// 
//  Contact:   N. West
//
//  Specification:-
//  =============
//
//  Translate  MySQL to other dialects of SQL.

//  This is NOT meant to be a general purpose SQL translator, but rather a very
//  simple translater of the SQL that the DBI employs which is dialect specific.

//  The only translations supported are as follows:-

//  1)  MySQL -> Oracle:-
//      Set date format to be compatible with MySQL.
//
//  2)  MySQL -> Oracle:-
//      Convert WHERE expressions of the form A & B to bitand(A,B) != 0
//
//  3)  MySQL -> Oracle:-
//      In CREATE TABLE 
//
//      Conversion is achieved by creating a DbiTableMetaData object
//      from the SQL and then asking it to generate the Oracle equivalent.
//      See DbiTableMetaData for details.
//      
//  4)  MySQL -> Oracle:-
//      SHOW TABLES        ->  SELECT TABLE_NAME FROM ALL_TABLES
//
//  5)  MySQL -> Oracle:-
//      \' -> '' (in general ORACLE doesn't respect escape sequences
//                except single quotes and only then as '' not \').
//
//  6)  MySQL -> Oracle:-
//      Convert now() into sysdate

  std::list<TString> sqlTransList;

  if ( fDbType != Dbi::kOracle ) {
    sqlTransList.push_back(sql);
    return sqlTransList;
}

  Bool_t translated = false;  //Once true,  sqlTransList contains the translation. No further translation possible.
  Bool_t modified  = false;   //sqlTrans has been modified, but  further translation possible.

  TString sqlTrans(sql);   
  sqlTrans.ToUpper();

// Set date format  to be compatible with MySQL.
  sqlTransList.push_back("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'");

// Translate NOW()
  if ( sqlTrans.Index("NOW()") != kNPOS ) {
    sqlTrans.ReplaceAll("NOW()","SYSDATE");
    modified = true;
  }

// Translate SHOW TABLES
  if (sqlTrans == "SHOW TABLES" ) {
    sqlTrans = "SELECT TABLE_NAME FROM ALL_TABLES";
    sqlTransList.push_back(sqlTrans);
    translated = true;
  }

   TString sqlIncant;

// Translate CREATE TABLE commands using DbiTableMetaData.
  Ssiz_t createTableIndex = sqlTrans.Index("CREATE TABLE",0,TString::kIgnoreCase );
  if ( ! translated && createTableIndex != kNPOS ) {
    DbiTableMetaData tmd;
    tmd.SetFromSql(sql.Data());
    std::vector<std::string> sql_list;
    UtilString::StringTok(sql_list,tmd.Sql(Dbi::kOracle),";");
    std::vector<std::string>::const_iterator itr(sql_list.begin()), itrEnd(sql_list.end());
    while ( itr != itrEnd ) { 
      sqlTransList.push_back(*itr); 
      ++itr;
    }
    translated = true;
  }

  
  // Translate DROP TABLE commands.
  Ssiz_t dropTableIndex = sqlTrans.Index("DROP TABLE",0,TString::kIgnoreCase );
  if ( ! translated && dropTableIndex != kNPOS ) {
    // Remove any "IF EXISTS"
    sqlTrans.ReplaceAll(" IF EXISTS ",    " ");

    //add the drop table command, then work on the synonym
    sqlTransList.push_back(sqlTrans);

    // Extract the table name
    Ssiz_t startIndex = dropTableIndex + 10;
    while ( isspace(sqlTrans[startIndex]) ) ++startIndex;
    Ssiz_t endIndex = startIndex + 1;
    Ssiz_t endIndexMax = sqlTrans.Length();
    while (     endIndex < endIndexMax
            && (isalnum(sqlTrans[endIndex]) || sqlTrans[endIndex] == '_') 
          ) ++endIndex;
    TString name(sqlTrans.Data()+startIndex,endIndex-startIndex);
    translated = true;
    
    // Add extra ORACLE incantations required when dropping a table with
    // a public synonym
    TString sqlIncant("DROP PUBLIC SYNONYM ");
    sqlIncant  += name ;
    sqlTransList.push_back(sqlIncant);
  }
  
// Translate commands with a WHERE keyword, but take care if this is
// an INSERT INTO command - it could include "where" as part of a character value!
  Ssiz_t whereStart = sqlTrans.Index("INSERT INTO",0,TString::kIgnoreCase );
  if ( whereStart == kNPOS ) whereStart = 0;
  else {
    // Skip to end of INSERT by looking for the trailing ")" at level 0
    // Yes, I know this can be defeated by mismatched "(" and ")" within
    // character values, but it's better than giving up on a possible WHERE clause 
    // altogether.
    Ssiz_t whereStartMax = sqlTrans.Length();
    whereStart = sqlTrans.Index("(",whereStart,TString::kIgnoreCase );
    if ( whereStart == kNPOS ) whereStart = whereStartMax;
    else ++whereStart;
    int level = 1;
    while ( whereStart < whereStartMax && level ) {
      char chr = sqlTrans[whereStart++];
      if ( chr == '(' ) ++level;
      if ( chr == ')' ) --level;
    }
  }
  // Look for space separated WHERE (so not fooled by e.g. FABWHERE!)
  Ssiz_t whereIndex = sqlTrans.Index(" WHERE ",whereStart,TString::kIgnoreCase );
  if ( ! translated && whereIndex != kNPOS ) {

    // Set limit of WHERE clause
    ++whereIndex; // Step over leading space
    Ssiz_t whereEnd = sqlTrans.Length();
    std::string whereDelim[] = { "GROUP BY", "HAVING", "ORDER BY", "LIMIT" };
    int numDelims = sizeof(whereDelim)/sizeof(string);
    for (int iDelim = 0; iDelim < numDelims; ++iDelim ) {
      const string& delimName = whereDelim[iDelim];
      Ssiz_t delimIndex = sqlTrans.Index(delimName.c_str(), delimName.size(), whereIndex + 5, TString::kIgnoreCase );
      if (delimIndex != kNPOS && delimIndex < whereEnd) whereEnd = delimIndex;
    }

    // Translate all bitwise and expressions within the WHERE clause.

    TString whereClause(sql.Data()+whereIndex,whereEnd-whereIndex);
    // Convert \n to space so that tokenising works.
    whereClause.ReplaceAll("\n"," ");
   
    std::vector<std::string> tokens;
    UtilString::StringTok(tokens,whereClause.Data()," ");
    int numTokens = tokens.size();
    for (int ithToken = 1; ithToken < numTokens-1; ++ithToken ) {
      if ( tokens[ithToken] == "&" ) {
        string tmp("bitand(");
        tmp += tokens[ithToken-1] + "," +  tokens[ithToken+1] + ") != 0";
        tokens[ithToken] = tmp;
        tokens[ithToken-1] = "";
        tokens[ithToken+1] = "";
      }
    }

    sqlTrans = sql(0,whereIndex);
    for (int ithToken = 0; ithToken < numTokens; ++ithToken ) {
      sqlTrans += " ";
      sqlTrans += tokens[ithToken].c_str();
    }
    sqlTrans += " " + sql(whereEnd,999999);
    modified = true;
  }

// Translate \' to ''
  if ( ! translated && sqlTrans.Index("\\\'") != kNPOS ) {
//  Bit of a kludge, if not yet modified, undo upper case conversion
//  as quoted data is likely to contain characters.
    if ( not modified ) sqlTrans = sql;
    sqlTrans.ReplaceAll("\\\'","\'\'");
    modified = true;
  }

  if ( modified && ! translated ) {
    sqlTransList.push_back(sqlTrans);
    translated = true;
  }

  if ( translated ) {
    MSG("Dbi",Msg::kSynopsis) << "sql: " << sql  << endl
                           << "translates to " << sqlTransList.size() 
                           << " statements:- \n";
    std::list<TString>::const_iterator itr(sqlTransList.begin()), itrEnd(sqlTransList.end());
    while (itr != itrEnd) { MSG("Dbi",Msg::kSynopsis) << "   " << *itr << endl; ++itr;}
  }
  else {
    sqlTransList.push_back(sql);
  }

  return sqlTransList;

}

---