loon: DbiStatement Class Reference

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 }


Public Member Functions
	DbiStatement (DbiConnection &conDb)
virtual	~DbiStatement ()
Dbi::DbTypes	GetDBType () const
Bool_t	PrintExceptions (Int_t level=3) const
const DbiExceptionLog &	GetExceptionLog () 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
DbiConnection &	fConDb
	Connection associated with this statement.
Dbi::DbTypes	fDbType
	Type of database (MySQL, Oracle).
DbiExceptionLog	fExceptionLog

DbiStatement Class Reference

Public Member Functions

Private Member Functions

Private Attributes

Detailed Description

Constructor & Destructor Documentation

Member Function Documentation

Member Data Documentation