DbiStatement Class Reference

#include <DbiStatement.h>

List of all members.

Public Member Functions

 DbiStatement (DbiConnection &conDb)
virtual ~DbiStatement ()
Dbi::DbTypes GetDBType () const
Bool_t PrintExceptions (Int_t level=3) const
const DbiExceptionLogGetExceptionLog () const
std::list< TString > TestTranslateSQL (const TString &sql, Dbi::DbTypes type)
 Debugging of SQL translation.
TSQLStatement * ExecuteQuery (const TString &sql="")
 Give caller a TSQLStatement of results (Process() and StoreResult() already performed.).
Bool_t ExecuteUpdate (const TString &sql="")
 Apply an update and return success/fail.

Private Member Functions

void AppendExceptionLog (DbiException *e)
void AppendExceptionLog (TSQLStatement *s)
void AppendExceptionLog (DbiConnection &c)
void ClearExceptionLog ()
TSQLStatement * CreateProcessedStatement (const TString &sql="")
std::list< TString > TranslateSQL (const TString &sql)

Private Attributes

DbiConnectionfConDb
 Connection associated with this statement.
Dbi::DbTypes fDbType
 Type of database (MySQL, Oracle).
DbiExceptionLog fExceptionLog


Detailed Description

Id
DbiStatement.h,v 1.14 2007/04/26 14:19:57 west Exp

Definition at line 44 of file DbiStatement.h.


Constructor & Destructor Documentation

DbiStatement::DbiStatement ( DbiConnection conDb  ) 

Definition at line 35 of file DbiStatement.cxx.

References Msg::kVerbose, LEA_CTOR, and MSG.

00035                                                :
00036 fConDb(conDb),
00037 fDbType(conDb.GetDbType())
00038 {
00039 //
00040 //
00041 //  Purpose:  Constructor
00042 //
00043 //  Arguments:  None.
00044 //
00045 //  Return:   
00046 //
00047 //  conDb    in    The connection associated with the statement.
00048 //
00049 
00050   LEA_CTOR    //Leak Checker
00051 
00052   MSG("Dbi", Msg::kVerbose) << "Creating DbiStatement" << endl;
00053   fConDb.ConnectStatement();
00054 
00055 }

DbiStatement::~DbiStatement (  )  [virtual]

Definition at line 59 of file DbiStatement.cxx.

References DbiConnection::DisConnectStatement(), fConDb, Msg::kVerbose, LEA_DTOR, and MSG.

00059                             {
00060 //
00061 //
00062 //  Purpose: Destructor
00063 
00064   LEA_DTOR    //Leak Checker
00065   MSG("Dbi", Msg::kVerbose) << "Destroying DbiStatement" << endl;
00066 
00067   fConDb.DisConnectStatement();
00068 }


Member Function Documentation

void DbiStatement::AppendExceptionLog ( DbiConnection c  )  [inline, private]

Definition at line 83 of file DbiStatement.h.

References DbiExceptionLog::AddLog(), fExceptionLog, and DbiConnection::GetExceptionLog().

void DbiStatement::AppendExceptionLog ( TSQLStatement *  s  )  [inline, private]

Definition at line 82 of file DbiStatement.h.

References DbiExceptionLog::AddEntry(), and fExceptionLog.

00082 { if ( s ) fExceptionLog.AddEntry(*s); }

void DbiStatement::AppendExceptionLog ( DbiException e  )  [inline, private]

Definition at line 81 of file DbiStatement.h.

References DbiExceptionLog::AddEntry(), MuELoss::e, and fExceptionLog.

Referenced by CreateProcessedStatement(), and ExecuteUpdate().

00081 { if ( e ) fExceptionLog.AddEntry(*e); }

void DbiStatement::ClearExceptionLog (  )  [inline, private]

Definition at line 84 of file DbiStatement.h.

References DbiExceptionLog::Clear(), and fExceptionLog.

Referenced by ExecuteQuery(), and ExecuteUpdate().

00084 { fExceptionLog.Clear(); }

TSQLStatement * DbiStatement::CreateProcessedStatement ( const TString &  sql = ""  )  [private]

Definition at line 72 of file DbiStatement.cxx.

References AppendExceptionLog(), DbiConnection::CreatePreparedStatement(), and fConDb.

Referenced by ExecuteQuery().

00072                                                                                   {
00073 
00074 // Attempt to create a processed statement (caller must delete).  Return 0 if failure.
00075 
00076   TSQLStatement* stmt = fConDb.CreatePreparedStatement(sql.Data());
00077   if ( ! stmt ) {
00078     this->AppendExceptionLog(fConDb);
00079     return 0;
00080   }
00081   if ( stmt->Process() ) return stmt;
00082   this->AppendExceptionLog(stmt);
00083   delete stmt;
00084   stmt = 0;
00085   return 0;
00086 
00087 }

TSQLStatement * DbiStatement::ExecuteQuery ( const TString &  sql = ""  ) 

Give caller a TSQLStatement of results (Process() and StoreResult() already performed.).

Definition at line 92 of file DbiStatement.cxx.

References DbiExceptionLog::AddEntry(), ClearExceptionLog(), CreateProcessedStatement(), fConDb, fExceptionLog, DbiConnection::GetDbName(), DbiExceptionLog::IsEmpty(), Msg::kSynopsis, and MSG.

Referenced by do_command(), DbuDaqConfigFilesText::LookupSeqNo(), and DbmValidate::TestGlobaliseSeqNo().

00092                                                              {
00093 //
00094 //
00095 //  Purpose:  Translate SQL if required and execute. 
00096 //  Return:   TSQLStatement with Process() and StoreResult() already performed.
00097 
00098   this->ClearExceptionLog();
00099 
00100   std::list<TString> sqlList(this->TranslateSQL(sql));
00101   std::list<TString>::const_iterator itr(sqlList.begin()), itrEnd(sqlList.end());
00102 
00103   TSQLStatement* stmt = 0;
00104   while (itr != itrEnd) {
00105     const TString& sql = *itr++;
00106     MSG("Dbi",Msg::kSynopsis) << "SQL:" << fConDb.GetDbName() << ":" << sql << endl;
00107     delete stmt;
00108     stmt = this->CreateProcessedStatement(sql);
00109     if ( ! stmt ) return 0;
00110   }
00111   // Store results from last SQL command (when multiple commands are generated
00112   // the last will be the one that performs the query).
00113   if ( stmt && ! stmt->StoreResult() ) {
00114     this->AppendExceptionLog(stmt);
00115     delete stmt;
00116     stmt = 0;
00117   }
00118 
00119   // Final sanity check: If there is a statement then the exception log should still
00120   // be clear otherwise it should not be.
00121   if ( stmt ) {
00122     if ( ! fExceptionLog.IsEmpty() ) {
00123       delete stmt;
00124       stmt = 0;
00125     }
00126   }
00127   else if ( fExceptionLog.IsEmpty() ) {
00128     ostringstream oss;
00129     oss << "Unknown failure (no execption but no TSQLStatement either executing " << sql;
00130     fExceptionLog.AddEntry(oss.str().c_str());
00131   }
00132   return stmt;
00133   
00134 }

Bool_t DbiStatement::ExecuteUpdate ( const TString &  sql = ""  ) 

Apply an update and return success/fail.

Definition at line 138 of file DbiStatement.cxx.

References AppendExceptionLog(), ClearExceptionLog(), fConDb, fExceptionLog, DbiConnection::GetDbName(), DbiConnection::GetServer(), DbiExceptionLog::IsEmpty(), Msg::kSynopsis, MSG, and DbiConnection::RecordException().

Referenced by DbuRunSummary::Commit(), DbuDaqFileSummary::Commit(), do_command(), DbmValidate::PrepareDb(), DbiCascader::Lock::SetLock(), and DbuDaqMonitorModule::WriteSubRunSummaryEntry().

00138                                                       {
00139 //
00140 //
00141 //  Purpose:  Translate SQL if required and Execute.
00142 //
00143 //  Return true if all updates successful.
00144 
00145 
00146   this->ClearExceptionLog();
00147 
00148   std::list<TString> sqlList(this->TranslateSQL(sql));
00149   std::list<TString>::const_iterator itr(sqlList.begin()), itrEnd(sqlList.end());
00150 
00151   while (itr != itrEnd) {
00152     const TString& sql = *itr++;
00153     MSG("Dbi",Msg::kSynopsis) << "SQL:" << fConDb.GetDbName() << ":" << sql << endl;
00154     bool ok = fConDb.GetServer()->Exec(sql.Data());
00155     if ( ! ok ) {
00156       fConDb.RecordException();
00157       this->AppendExceptionLog(fConDb);
00158       return false;
00159     }
00160   }
00161   return fExceptionLog.IsEmpty();
00162   
00163 }

Dbi::DbTypes DbiStatement::GetDBType (  )  const [inline]

Definition at line 55 of file DbiStatement.h.

References fDbType.

Referenced by DbiCascader::Lock::SetLock().

00055 { return fDbType; } 

const DbiExceptionLog& DbiStatement::GetExceptionLog (  )  const [inline]

Definition at line 64 of file DbiStatement.h.

References fExceptionLog.

Referenced by DbiCascader::Lock::SetLock().

00064 { return fExceptionLog; }

Bool_t DbiStatement::PrintExceptions ( Int_t  level = 3  )  const

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

Definition at line 167 of file DbiStatement.cxx.

References DbiExceptionLog::AddLog(), DbiExceptionLog::GetGELog(), Msg::kWarning, and MSG.

Referenced by DbuRunSummary::Commit(), DbuDaqFileSummary::Commit(), DbiCascader::CreateStatement(), DbmValidate::PrepareDb(), DbiCascader::Lock::SetLock(), and DbmValidate::TestGlobaliseSeqNo().

00167                                                       {
00168 
00169 //  Purpose:  Print accumulated exceptions at supplied Msg level,
00170 //            add them to the Global Exception Log if level >= kWarning
00171 //            and return true if there are any.
00172 
00173   const DbiExceptionLog& el(this->GetExceptionLog());
00174   if ( el.IsEmpty() ) return false;
00175   MSG("Dbi", level) << el;
00176   if ( level >= Msg::kWarning )  DbiExceptionLog::GetGELog().AddLog(el);
00177   return true;
00178 
00179 }

std::list< TString > DbiStatement::TestTranslateSQL ( const TString &  sql,
Dbi::DbTypes  type 
)

Debugging of SQL translation.

Definition at line 183 of file DbiStatement.cxx.

References fDbType.

00183                                                                                     {
00184 //
00185 //
00186 //  Purpose:  Debugging aid: Test translate from MySQL to other dialects of SQL.
00187 //
00188 //  Arguments: 
00189 //    sql          in    The string to be translated
00190 //    type         in    The database type to translate to.
00191 //
00192 //  Return:    A list of translated SQL
00193 //             NB A single MySQL command can generate multiple ORACLE ones.
00194 
00195   Dbi::DbTypes fDbTypeSave = fDbType;
00196   fDbType = type;
00197   std::list<TString> sqlTrans(this->TranslateSQL(sql));
00198   fDbType = fDbTypeSave;
00199   return sqlTrans;
00200 
00201 }

std::list< TString > DbiStatement::TranslateSQL ( const TString &  sql  )  [private]

Definition at line 205 of file DbiStatement.cxx.

References fDbType, Dbi::kOracle, Msg::kSynopsis, MSG, DbiTableMetaData::SetFromSql(), DbiTableMetaData::Sql(), and UtilString::StringTok().

00205                                                                {
00206 //
00207 //
00208 //  Purpose:  Translate from MySQL to other dialects of SQL.
00209 //
00210 //  Arguments: 
00211 //    sql          in    The string to be translated
00212 //
00213 //
00214 //  Return:    A list of translated SQL
00215 //             NB A single MySQL command can generate multiple ORACLE ones.
00216 // 
00217 //  Contact:   N. West
00218 //
00219 //  Specification:-
00220 //  =============
00221 //
00222 //  Translate  MySQL to other dialects of SQL.
00223 
00224 //  This is NOT meant to be a general purpose SQL translator, but rather a very
00225 //  simple translater of the SQL that the DBI employs which is dialect specific.
00226 
00227 //  The only translations supported are as follows:-
00228 
00229 //  1)  MySQL -> Oracle:-
00230 //      Set date format to be compatible with MySQL.
00231 //
00232 //  2)  MySQL -> Oracle:-
00233 //      Convert WHERE expressions of the form A & B to bitand(A,B) != 0
00234 //
00235 //  3)  MySQL -> Oracle:-
00236 //      In CREATE TABLE 
00237 //
00238 //      Conversion is achieved by creating a DbiTableMetaData object
00239 //      from the SQL and then asking it to generate the Oracle equivalent.
00240 //      See DbiTableMetaData for details.
00241 //      
00242 //  4)  MySQL -> Oracle:-
00243 //      SHOW TABLES        ->  SELECT TABLE_NAME FROM ALL_TABLES
00244 //
00245 //  5)  MySQL -> Oracle:-
00246 //      \' -> '' (in general ORACLE doesn't respect escape sequences
00247 //                except single quotes and only then as '' not \').
00248 //
00249 //  6)  MySQL -> Oracle:-
00250 //      Convert now() into sysdate
00251 
00252   std::list<TString> sqlTransList;
00253 
00254   if ( fDbType != Dbi::kOracle ) {
00255     sqlTransList.push_back(sql);
00256     return sqlTransList;
00257 }
00258 
00259   Bool_t translated = false;  //Once true,  sqlTransList contains the translation. No further translation possible.
00260   Bool_t modified  = false;   //sqlTrans has been modified, but  further translation possible.
00261 
00262   TString sqlTrans(sql);   
00263   sqlTrans.ToUpper();
00264 
00265 // Set date format  to be compatible with MySQL.
00266   sqlTransList.push_back("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'");
00267 
00268 // Translate NOW()
00269   if ( sqlTrans.Index("NOW()") != kNPOS ) {
00270     sqlTrans.ReplaceAll("NOW()","SYSDATE");
00271     modified = true;
00272   }
00273 
00274 // Translate SHOW TABLES
00275   if (sqlTrans == "SHOW TABLES" ) {
00276     sqlTrans = "SELECT TABLE_NAME FROM ALL_TABLES";
00277     sqlTransList.push_back(sqlTrans);
00278     translated = true;
00279   }
00280 
00281    TString sqlIncant;
00282 
00283 // Translate CREATE TABLE commands using DbiTableMetaData.
00284   Ssiz_t createTableIndex = sqlTrans.Index("CREATE TABLE",0,TString::kIgnoreCase );
00285   if ( ! translated && createTableIndex != kNPOS ) {
00286     DbiTableMetaData tmd;
00287     tmd.SetFromSql(sql.Data());
00288     std::vector<std::string> sql_list;
00289     UtilString::StringTok(sql_list,tmd.Sql(Dbi::kOracle),";");
00290     std::vector<std::string>::const_iterator itr(sql_list.begin()), itrEnd(sql_list.end());
00291     while ( itr != itrEnd ) { 
00292       sqlTransList.push_back(*itr); 
00293       ++itr;
00294     }
00295     translated = true;
00296   }
00297 
00298   
00299   // Translate DROP TABLE commands.
00300   Ssiz_t dropTableIndex = sqlTrans.Index("DROP TABLE",0,TString::kIgnoreCase );
00301   if ( ! translated && dropTableIndex != kNPOS ) {
00302     // Remove any "IF EXISTS"
00303     sqlTrans.ReplaceAll(" IF EXISTS ",    " ");
00304 
00305     //add the drop table command, then work on the synonym
00306     sqlTransList.push_back(sqlTrans);
00307 
00308     // Extract the table name
00309     Ssiz_t startIndex = dropTableIndex + 10;
00310     while ( isspace(sqlTrans[startIndex]) ) ++startIndex;
00311     Ssiz_t endIndex = startIndex + 1;
00312     Ssiz_t endIndexMax = sqlTrans.Length();
00313     while (     endIndex < endIndexMax
00314             && (isalnum(sqlTrans[endIndex]) || sqlTrans[endIndex] == '_') 
00315           ) ++endIndex;
00316     TString name(sqlTrans.Data()+startIndex,endIndex-startIndex);
00317     translated = true;
00318     
00319     // Add extra ORACLE incantations required when dropping a table with
00320     // a public synonym
00321     TString sqlIncant("DROP PUBLIC SYNONYM ");
00322     sqlIncant  += name ;
00323     sqlTransList.push_back(sqlIncant);
00324   }
00325   
00326 // Translate commands with a WHERE keyword, but take care if this is
00327 // an INSERT INTO command - it could include "where" as part of a character value!
00328   Ssiz_t whereStart = sqlTrans.Index("INSERT INTO",0,TString::kIgnoreCase );
00329   if ( whereStart == kNPOS ) whereStart = 0;
00330   else {
00331     // Skip to end of INSERT by looking for the trailing ")" at level 0
00332     // Yes, I know this can be defeated by mismatched "(" and ")" within
00333     // character values, but it's better than giving up on a possible WHERE clause 
00334     // altogether.
00335     Ssiz_t whereStartMax = sqlTrans.Length();
00336     whereStart = sqlTrans.Index("(",whereStart,TString::kIgnoreCase );
00337     if ( whereStart == kNPOS ) whereStart = whereStartMax;
00338     else ++whereStart;
00339     int level = 1;
00340     while ( whereStart < whereStartMax && level ) {
00341       char chr = sqlTrans[whereStart++];
00342       if ( chr == '(' ) ++level;
00343       if ( chr == ')' ) --level;
00344     }
00345   }
00346   // Look for space separated WHERE (so not fooled by e.g. FABWHERE!)
00347   Ssiz_t whereIndex = sqlTrans.Index(" WHERE ",whereStart,TString::kIgnoreCase );
00348   if ( ! translated && whereIndex != kNPOS ) {
00349 
00350     // Set limit of WHERE clause
00351     ++whereIndex; // Step over leading space
00352     Ssiz_t whereEnd = sqlTrans.Length();
00353     std::string whereDelim[] = { "GROUP BY", "HAVING", "ORDER BY", "LIMIT" };
00354     int numDelims = sizeof(whereDelim)/sizeof(string);
00355     for (int iDelim = 0; iDelim < numDelims; ++iDelim ) {
00356       const string& delimName = whereDelim[iDelim];
00357       Ssiz_t delimIndex = sqlTrans.Index(delimName.c_str(), delimName.size(), whereIndex + 5, TString::kIgnoreCase );
00358       if (delimIndex != kNPOS && delimIndex < whereEnd) whereEnd = delimIndex;
00359     }
00360 
00361     // Translate all bitwise and expressions within the WHERE clause.
00362 
00363     TString whereClause(sql.Data()+whereIndex,whereEnd-whereIndex);
00364     // Convert \n to space so that tokenising works.
00365     whereClause.ReplaceAll("\n"," ");
00366    
00367     std::vector<std::string> tokens;
00368     UtilString::StringTok(tokens,whereClause.Data()," ");
00369     int numTokens = tokens.size();
00370     for (int ithToken = 1; ithToken < numTokens-1; ++ithToken ) {
00371       if ( tokens[ithToken] == "&" ) {
00372         string tmp("bitand(");
00373         tmp += tokens[ithToken-1] + "," +  tokens[ithToken+1] + ") != 0";
00374         tokens[ithToken] = tmp;
00375         tokens[ithToken-1] = "";
00376         tokens[ithToken+1] = "";
00377       }
00378     }
00379 
00380     sqlTrans = sql(0,whereIndex);
00381     for (int ithToken = 0; ithToken < numTokens; ++ithToken ) {
00382       sqlTrans += " ";
00383       sqlTrans += tokens[ithToken].c_str();
00384     }
00385     sqlTrans += " " + sql(whereEnd,999999);
00386     modified = true;
00387   }
00388 
00389 // Translate \' to ''
00390   if ( ! translated && sqlTrans.Index("\\\'") != kNPOS ) {
00391 //  Bit of a kludge, if not yet modified, undo upper case conversion
00392 //  as quoted data is likely to contain characters.
00393     if ( not modified ) sqlTrans = sql;
00394     sqlTrans.ReplaceAll("\\\'","\'\'");
00395     modified = true;
00396   }
00397 
00398   if ( modified && ! translated ) {
00399     sqlTransList.push_back(sqlTrans);
00400     translated = true;
00401   }
00402 
00403   if ( translated ) {
00404     MSG("Dbi",Msg::kSynopsis) << "sql: " << sql  << endl
00405                            << "translates to " << sqlTransList.size() 
00406                            << " statements:- \n";
00407     std::list<TString>::const_iterator itr(sqlTransList.begin()), itrEnd(sqlTransList.end());
00408     while (itr != itrEnd) { MSG("Dbi",Msg::kSynopsis) << "   " << *itr << endl; ++itr;}
00409   }
00410   else {
00411     sqlTransList.push_back(sql);
00412   }
00413 
00414   return sqlTransList;
00415 
00416 }


Member Data Documentation

DbiConnection& DbiStatement::fConDb [private]

Connection associated with this statement.

Definition at line 92 of file DbiStatement.h.

Referenced by CreateProcessedStatement(), ExecuteQuery(), ExecuteUpdate(), and ~DbiStatement().

Dbi::DbTypes DbiStatement::fDbType [private]

Type of database (MySQL, Oracle).

Definition at line 95 of file DbiStatement.h.

Referenced by GetDBType(), TestTranslateSQL(), and TranslateSQL().

DbiExceptionLog DbiStatement::fExceptionLog [private]

A log of reported exceptions. Cleared by calling ExecuteQuery, ExecuteUpdate

Definition at line 99 of file DbiStatement.h.

Referenced by AppendExceptionLog(), ClearExceptionLog(), ExecuteQuery(), ExecuteUpdate(), and GetExceptionLog().


The documentation for this class was generated from the following files:
Generated on Thu Jul 10 22:52:38 2014 for loon by  doxygen 1.4.7