From dd70ff8bf32c2d7ed365004b1770058265db1978 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?J=C3=B6rg=20Frings-F=C3=BCrst?= Date: Sat, 11 Mar 2023 18:15:37 +0100 Subject: New upstream version 4.1.0 --- src/sql/xbalttbl.cpp | 7 +- src/sql/xbcrix.cpp | 16 +- src/sql/xbcrtbl.cpp | 49 ++-- src/sql/xbcrtbl.save.cpp | 256 ++++++++++++++++++ src/sql/xbdelete.cpp | 2 +- src/sql/xbdrptbl.cpp | 9 +- src/sql/xbselect.cpp | 17 +- src/sql/xbset.cpp | 5 +- src/sql/xbsql.cpp | 3 - src/sql/xbstmt.cpp | 679 +++++++++++++++++++++++++++++++++++++++++++++++ 10 files changed, 992 insertions(+), 51 deletions(-) create mode 100755 src/sql/xbcrtbl.save.cpp create mode 100755 src/sql/xbstmt.cpp (limited to 'src/sql') diff --git a/src/sql/xbalttbl.cpp b/src/sql/xbalttbl.cpp index 576eab7..8a36b46 100755 --- a/src/sql/xbalttbl.cpp +++ b/src/sql/xbalttbl.cpp @@ -79,17 +79,12 @@ xbInt16 xbSql::SqlAlterTable( const xbString &sCmdLine ){ sTrgTbl.ExtractElement( sCmd, ' ', 4, 0 ); sTrgTbl.Trim(); - std::cout << "source table name = " << sSrcTbl.Str() << "\n"; +// std::cout << "source table name = " << sSrcTbl.Str() << "\n"; // std::cout << "target table name = " << sTrgTbl.Str() << "\n"; // if not open, attempt to open it dbf = xbase->GetDbfPtr( sSrcTbl ); - if( dbf ) - std::cout << "dbf valued\n"; - else - std::cout << "dbf not valued\n"; - if( !dbf ){ if(( iRc = xbase->OpenHighestVersion( sSrcTbl, "", &dbf )) != XB_NO_ERROR ){ iErrorStop = 120; diff --git a/src/sql/xbcrix.cpp b/src/sql/xbcrix.cpp index 8dc6f62..6a391f7 100755 --- a/src/sql/xbcrix.cpp +++ b/src/sql/xbcrix.cpp @@ -147,13 +147,13 @@ xbInt16 xbSql::SqlCreateIndex( const xbString &sCmdLine ){ } } if( dbf == NULL ){ - iErrorStop = 50; + iErrorStop = 130; iRc = XB_FILE_NOT_FOUND; throw iRc; } sCmd.Ltrunc( ulPos ); - std::cout << "cp1 ulPos = " << ulPos << " sCmd = [" << sCmd << "]\n"; + // std::cout << "cp1 ulPos = " << ulPos << " sCmd = [" << sCmd << "]\n"; //ulPos = sCmd.GetLastPos( ')' ); xbString sKeyExpression; @@ -180,7 +180,7 @@ xbInt16 xbSql::SqlCreateIndex( const xbString &sCmdLine ){ lPos++; } - std::cout << "Key Expression =[" << sKeyExpression << "]\n"; + // std::cout << "Key Expression =[" << sKeyExpression << "]\n"; sCmd.Ltrunc( lPos ); sCmd.Trim(); @@ -216,7 +216,7 @@ xbInt16 xbSql::SqlCreateIndex( const xbString &sCmdLine ){ #ifdef XB_LOCKING_SUPPORT if(( iRc = dbf->LockTable( XB_LOCK )) != XB_NO_ERROR ){ - iErrorStop = 130; + iErrorStop = 140; throw iRc; } #endif // XB_LOCKING_SUPPORT @@ -227,7 +227,7 @@ xbInt16 xbSql::SqlCreateIndex( const xbString &sCmdLine ){ void *vpTag; if(( iRc = dbf->CreateTag( sIxType, sIxName, sKeyExpression, sFilter, bDesc, bUnique, xbFalse, &pIx, &vpTag )) != XB_NO_ERROR ){ - iErrorStop = 140; + iErrorStop = 150; throw iRc; } // std::cout << "SqlCreateIndex() - back from tag create\n"; @@ -239,7 +239,7 @@ xbInt16 xbSql::SqlCreateIndex( const xbString &sCmdLine ){ sCmd.ToUpperCase(); if( sCmd.Pos( "ASSOCIATE" )){ if(( iRc = dbf->AssociateIndex( "NDX", sIxName, 0 )) != XB_NO_ERROR ){ - iErrorStop = 150; + iErrorStop = 160; throw iRc; } } @@ -255,14 +255,14 @@ xbInt16 xbSql::SqlCreateIndex( const xbString &sCmdLine ){ if(( iRc = pIx->Reindex( &vpTag )) != XB_NO_ERROR ){ - iErrorStop = 160; + iErrorStop = 170; throw iRc; } #ifdef XB_LOCKING_SUPPORT if(( iRc = dbf->LockTable( XB_UNLOCK )) != XB_NO_ERROR ){ - iErrorStop = 140; + iErrorStop = 180; throw iRc; } #endif // XB_LOCKING_SUPPORT diff --git a/src/sql/xbcrtbl.cpp b/src/sql/xbcrtbl.cpp index 7f6b8fa..dfb3403 100755 --- a/src/sql/xbcrtbl.cpp +++ b/src/sql/xbcrtbl.cpp @@ -29,7 +29,7 @@ xbInt16 xbSql::SqlCreateTable( const xbString &sCmdLine ){ // expected format: // CREATE TABLE tablename.dbf (Field1 CHAR(10), INTFLD1 INTEGER, ... ) - // supported fields types + // supported field types // // SQL TYPE XBASE Field Type // -------- ---------------- @@ -61,6 +61,7 @@ xbInt16 xbSql::SqlCreateTable( const xbString &sCmdLine ){ xbLinkList ll; xbSchema *schema = NULL; xbString sMsg; + xbString sWork; try{ // retrieve table name @@ -75,7 +76,7 @@ xbInt16 xbSql::SqlCreateTable( const xbString &sCmdLine ){ xbFile f( xbase ); f.SetFileName( sTableName ); if( f.FileExists() ){ - iErrorStop = 10; + iErrorStop = 100; iRc = XB_FILE_EXISTS; throw iRc; } @@ -90,8 +91,6 @@ xbInt16 xbSql::SqlCreateTable( const xbString &sCmdLine ){ s.Remove( s.Len(), 1 ); s.Trim(); - //std::cout << "s = [" << s << "]\n"; - xbBool bDone = xbFalse; xbInt16 iLoop = 0; @@ -129,7 +128,7 @@ xbInt16 xbSql::SqlCreateTable( const xbString &sCmdLine ){ else if( sDataType == "SMALLINT" || sDataType == "INTEGER" || sDataType == "DATE" || sDataType == "VARCHAR" || sDataType == "LOGICAL" ) iAttribCnt = 0; else{ - iErrorStop = 20; + iErrorStop = 110; iRc = XB_INVALID_FIELD_TYPE; throw iRc; } @@ -143,25 +142,37 @@ xbInt16 xbSql::SqlCreateTable( const xbString &sCmdLine ){ } else if( iAttribCnt > 0 ){ lPos = s.Pos( '(' ); if( lPos <= 0 ){ - iErrorStop = 30; + iErrorStop = 120; iRc = XB_INVALID_FIELD_LEN; throw iRc; } s.Ltrunc( lPos ); - if( iAttribCnt == 1 ) - lPos = s.Pos( ')' ); - else - lPos = s.Pos( ',' ); - - sAttrib1.Assign( s, 1, lPos-1 ); - sAttrib1.Trim(); - s.Ltrunc( lPos ); - if( iAttribCnt > 1 ){ - lPos = s.Pos( ')' ); - sAttrib2.Assign( s, 1, lPos-1 ); - s.Ltrunc( lPos ); + lPos = s.Pos( ')' ); + if( lPos <= 0 ){ + iErrorStop = 130; + iRc = XB_INVALID_FIELD_LEN; + throw iRc; } + sWork.Assign( s, 1, lPos - 1); + sWork.Trim(); + + if( iAttribCnt == 1 ){ + sAttrib1 = sWork; + } else { + lCmPos = sWork.Pos( ',' ); + if( lCmPos <= 0 ){ + iErrorStop = 140; + iRc = XB_INVALID_FIELD_LEN; + throw iRc; + } + sAttrib1.Assign( sWork, 1, lCmPos - 1); + sAttrib1.Trim(); + sWork.Ltrunc( lCmPos ); + sAttrib2 = sWork; + sAttrib2.Trim(); + } + s.Ltrunc( lPos ); } s.Ltrim(); @@ -229,7 +240,7 @@ xbInt16 xbSql::SqlCreateTable( const xbString &sCmdLine ){ dbf = new xbDbf3( xbase ); #endif if(( iRc = dbf->CreateTable( sTableName, "", schema, 0, XB_MULTI_USER )) != XB_NO_ERROR ){ - iErrorStop = 50; + iErrorStop = 150; throw iRc; } diff --git a/src/sql/xbcrtbl.save.cpp b/src/sql/xbcrtbl.save.cpp new file mode 100755 index 0000000..445ad56 --- /dev/null +++ b/src/sql/xbcrtbl.save.cpp @@ -0,0 +1,256 @@ +/* xbcrtbl.cpp + +XBase64 Software Library + +Copyright (c) 1997,2003,2014,2019,2022 Gary A Kunkel + +The xb64 software library is covered under the terms of the GPL Version 3, 2007 license. + +Email Contact: + + XDB-devel@lists.sourceforge.net + XDB-users@lists.sourceforge.net + +*/ + +#include "xbase.h" + +#ifdef XB_SQL_SUPPORT + +namespace xb{ + + +/***********************************************************************/ +xbInt16 xbSql::SqlCreateTable( const xbString &sCmdLine ){ + + + // std::cout << "CREATE TABLE " << sCmdLine << std::endl; + + // expected format: + // CREATE TABLE tablename.dbf (Field1 CHAR(10), INTFLD1 INTEGER, ... ) + + // supported field types + // + // SQL TYPE XBASE Field Type + // -------- ---------------- + // SMALLINT NUMERIC(6,0) + // INTEGER NUMERIC(11,0) + // DECIMAL(x,y) NUMERIC(x+1,y) + // NUMERIC(x,y) NUMERIC(x,y) + // FLOAT(x,y) FLOAT(x,y) + // CHAR(n) CHARACTER(n) + // DATE DATE + // VARCHAR MEMO + // LOGICAL LOGICAL + + xbInt16 iRc = 0; + xbInt16 iErrorStop = 0; + xbString sTableName; + xbString sFieldName; + xbString sDataType; + xbString sAttrib1; + xbString sAttrib2; + xbString sLlEntry; + xbInt16 iType = 0; + xbInt16 iAttribCnt; // number of attributes for a given data type + xbString s; + xbUInt32 lPos = 0; + xbUInt32 lSpPos = 0; // space position + xbUInt32 lCmPos = 0; // comma position + xbUInt32 lLpPos = 0; // left paren position + xbLinkList ll; + xbSchema *schema = NULL; + xbString sMsg; + + try{ + // retrieve table name + s.ExtractElement( sCmdLine, '(', 1, 0 ); + sTableName.ExtractElement( s, ' ', 3, 0 ); + sTableName.Trim(); + + // std::cout << "Tablename = [" << sTableName << "]\n"; + + + // determine if it already exists + xbFile f( xbase ); + f.SetFileName( sTableName ); + if( f.FileExists() ){ + iErrorStop = 100; + iRc = XB_FILE_EXISTS; + throw iRc; + } + + // build out table structure with parms from the sql string + lPos = sCmdLine.Pos( '(' ); + s = sCmdLine; + s.Ltrunc( lPos ); + s.Trim(); + + // remove the last byte, should be a ) + s.Remove( s.Len(), 1 ); + + s.Trim(); + //std::cout << "s = [" << s << "]\n"; + + + xbBool bDone = xbFalse; + xbInt16 iLoop = 0; + while( !bDone && iLoop++ < 255 ){ + sFieldName.ExtractElement( s, ' ', 1 , 0 ); + lPos = sFieldName.Len(); + sFieldName.Trim(); + if( sFieldName.Len() > 10 ) + sFieldName.Mid( 1, 10 ); // shrink to 10 buytes if too big + + //std::cout << "field name=[" << sFieldName << "]\n"; + s.Ltrunc( lPos + 1 ); + s.Ltrim(); + //std::cout << "remainder after field name removed = [" << s << "]\n"; + + // Data type is delimited with either a space, comma or left paren + lPos = 9999999; + lSpPos = s.Pos( ' ' ); + lCmPos = s.Pos( ',' ); + lLpPos = s.Pos( '(' ); + if( lSpPos != 0 ) lPos = lSpPos; + if( lCmPos != 0 && lCmPos < lPos ) lPos = lCmPos; + if( lLpPos != 0 && lLpPos < lPos ) lPos = lLpPos; + + //sMsg.Sprintf( "SpPos=[%d] CmPos=[%d] LpPos=[%d] lPos=[%d]", lSpPos, lCmPos, lLpPos, lPos ); + //std::cout << sMsg << "\n"; + + sDataType.Assign( s, 1, lPos-1 ); + + //std::cout << "DataType=[" << sDataType << "]\n"; + if( sDataType == "CHAR" ) + iAttribCnt = 1; + else if( sDataType == "DECIMAL" || sDataType == "NUMERIC" || sDataType == "FLOAT" ) + iAttribCnt = 2; + else if( sDataType == "SMALLINT" || sDataType == "INTEGER" || sDataType == "DATE" || sDataType == "VARCHAR" || sDataType == "LOGICAL" ) + iAttribCnt = 0; + else{ + iErrorStop = 110; + iRc = XB_INVALID_FIELD_TYPE; + throw iRc; + } + + sAttrib1 = ""; + sAttrib2 = "0"; + + if( iAttribCnt == 0 ){ + s.Ltrunc( sDataType.Len()); + + } else if( iAttribCnt > 0 ){ + lPos = s.Pos( '(' ); + if( lPos <= 0 ){ + iErrorStop = 110; + iRc = XB_INVALID_FIELD_LEN; + throw iRc; + } + s.Ltrunc( lPos ); + if( iAttribCnt == 1 ) + lPos = s.Pos( ')' ); + else + lPos = s.Pos( ',' ); + + sAttrib1.Assign( s, 1, lPos-1 ); + sAttrib1.Trim(); + s.Ltrunc( lPos ); + + if( iAttribCnt > 1 ){ + lPos = s.Pos( ')' ); + sAttrib2.Assign( s, 1, lPos-1 ); + s.Ltrunc( lPos ); + } + } + + s.Ltrim(); + s.ZapLeadingChar( ',' ); + s.Ltrim(); + + if( sDataType == "CHAR" ){ + iType = XB_CHAR_FLD; + } else if( sDataType == "DECIMAL" ){ + xbInt32 lVal = atol( sAttrib1.Str()) + 1; + sAttrib1.Sprintf( "%d", lVal ); + iType = XB_NUMERIC_FLD; + } else if( sDataType == "SMALLINT" ){ + sAttrib1 = "6"; + iType = XB_NUMERIC_FLD; + } else if( sDataType == "INTEGER" ){ + sAttrib1 = "11"; + iType = XB_NUMERIC_FLD; + } else if( sDataType == "NUMERIC" ){ + iType = XB_NUMERIC_FLD; + } else if( sDataType == "FLOAT" ) { + iType = XB_FLOAT_FLD; + } else if( sDataType == "DATE" ){ + iType = XB_DATE_FLD; + sAttrib1 = "8"; + } else if( sDataType == "VARCHAR" ){ + iType = XB_MEMO_FLD; + sAttrib1 = "10"; + } else if( sDataType == "LOGICAL" ){ + iType = XB_LOGICAL_FLD; + sAttrib1 = "1"; + } + sLlEntry.Sprintf( "%s,%s,%c,%s,%s", sFieldName.Str(), sDataType.Str(), iType, sAttrib1.Str(), sAttrib2.Str()); + ll.InsertAtEnd( sLlEntry ); + + if( s.Len() == 0 ) + bDone = xbTrue; + } + + schema = (xbSchema *) calloc( ll.GetNodeCnt()+1, sizeof( xbSchema )); + xbLinkListNode * llN = ll.GetHeadNode(); + xbUInt32 ulCnt = ll.GetNodeCnt(); + + char *pTrg; + for( xbUInt32 i = 0; i < ulCnt; i++ ){ + s = llN->GetKey(); + sFieldName.ExtractElement( s, ',', 1 , 0 ); + pTrg = schema[i].cFieldName; + for( xbUInt32 j = 0; j < sFieldName.Len(); j++ ) + *pTrg++ = sFieldName[j+1]; + sDataType.ExtractElement( s, ',', 3, 0 ); + schema[i].cType = sDataType[1]; + sAttrib1.ExtractElement( s, ',', 4, 0 ); + schema[i].iFieldLen = atoi( sAttrib1.Str()); + sAttrib2.ExtractElement( s, ',', 5, 0 ); + schema[i].iNoOfDecs = atoi( sAttrib2.Str()); + llN = llN->GetNextNode(); + } + + // create the table + xbDbf * dbf = NULL; + #ifdef XB_DBF4_SUPPORT + dbf = new xbDbf4( xbase ); + #elif defined (XB_DBF3_SUPPORT) + dbf = new xbDbf3( xbase ); + #endif + if(( iRc = dbf->CreateTable( sTableName, "", schema, 0, XB_MULTI_USER )) != XB_NO_ERROR ){ + iErrorStop = 120; + throw iRc; + } + + free( schema ); + ll.Clear(); + } + catch (xbInt16 iRc ){ + xbString sMsg; + if( sFieldName.Len() > 0 ) + sMsg.Sprintf( "xbSql::SqlCreateTbl() Exception Caught. Error Stop = [%d] rc = [%d] table = [%s] field = [%s]", iErrorStop, iRc, sTableName.Str(), sFieldName.Str() ); + else + sMsg.Sprintf( "xbSql::SqlCreateTbl() Exception Caught. Error Stop = [%d] rc = [%d] table = [%s]", iErrorStop, iRc, sTableName.Str() ); + + xbase->WriteLogMessage( sMsg.Str() ); + xbase->WriteLogMessage( GetErrorMessage( iRc )); + if( schema ) free( schema ); + } + return iRc; +} + +/***********************************************************************/ +} /* namespace */ +#endif /* XB_SQL_SUPPORT */ + diff --git a/src/sql/xbdelete.cpp b/src/sql/xbdelete.cpp index 157b281..82b4937 100755 --- a/src/sql/xbdelete.cpp +++ b/src/sql/xbdelete.cpp @@ -95,7 +95,7 @@ xbInt16 xbSql::SqlDelete( const xbString &sCmdLine ){ } } else { - xbFilter f( dbf->GetXbasePtr(), dbf ); + xbFilter f( dbf ); if(( iRc = f.Set( sFilter )) != XB_NO_ERROR ){ iErrorStop = 150; throw iRc; diff --git a/src/sql/xbdrptbl.cpp b/src/sql/xbdrptbl.cpp index e2cfa9d..b03ee8f 100755 --- a/src/sql/xbdrptbl.cpp +++ b/src/sql/xbdrptbl.cpp @@ -61,7 +61,7 @@ xbInt16 xbSql::SqlDropTable( const xbString &sCmdLine ){ sNode.Trim(); sNode.ToUpperCase(); if( sNode != "EXISTS" ){ - iErrorStop = 10; + iErrorStop = 100; iRc = XB_SYNTAX_ERROR; throw iRc; } else { @@ -91,22 +91,21 @@ xbInt16 xbSql::SqlDropTable( const xbString &sCmdLine ){ if( iRc == XB_FILE_NOT_FOUND && bIfExists ){ return XB_NO_ERROR; } else { - iErrorStop = 20; + iErrorStop = 110; throw iRc; } } } if( dbf == NULL ){ - iErrorStop = 40; + iErrorStop = 120; iRc = XB_OPEN_ERROR; throw iRc; } if(( dbf->DeleteTable()) != XB_NO_ERROR ){ - iErrorStop = 50; + iErrorStop = 130; throw iRc; } - } catch (xbInt16 iRc ){ diff --git a/src/sql/xbselect.cpp b/src/sql/xbselect.cpp index 91c0485..7f12e99 100755 --- a/src/sql/xbselect.cpp +++ b/src/sql/xbselect.cpp @@ -21,18 +21,23 @@ namespace xb{ /***********************************************************************/ -xbInt16 xbSql::SqlSelect( const xbString &sCmdLine ){ +xbInt16 xbStmt::ExecuteQuery( const xbString &sCmdLine ){ - std::cout << "SELECT [" << sCmdLine << "]\n"; + std::cout << "xbStmt::ExecuteQuery() - SELECT [" << sCmdLine << "]\n"; // expected format: // SELECT xbInt16 iRc = 0; xbInt16 iErrorStop = 0; - xbUInt32 ulPos; + // xbUInt32 ulPos; try{ + if(( iRc = ParseStmt( sCmdLine )) != XB_NO_ERROR ){ + iErrorStop = 100; + throw iRc; + } + /* xbString sCmd = sCmdLine; sCmd.Trim(); @@ -63,12 +68,12 @@ xbInt16 xbSql::SqlSelect( const xbString &sCmdLine ){ if( sToken == '^' ){ if(( iRc = uda.DelTokenForKey( sKey )) != XB_NO_ERROR ){ - iErrorStop = 10; + iErrorStop = 100; throw iRc; } } else { if(( iRc = uda.UpdTokenForKey( sKey, sToken )) != XB_NO_ERROR ){ - iErrorStop = 10; + iErrorStop = 110; throw iRc; } } @@ -78,7 +83,7 @@ xbInt16 xbSql::SqlSelect( const xbString &sCmdLine ){ } catch (xbInt16 iRc ){ xbString sMsg; - sMsg.Sprintf( "xbSql::SqlSelect() Exception Caught. Error Stop = [%d] rc = [%d]", iErrorStop, iRc ); + sMsg.Sprintf( "xbStmt::ExecuteQuery() Exception Caught. Error Stop = [%d] rc = [%d]", iErrorStop, iRc ); xbase->WriteLogMessage( sMsg.Str() ); xbase->WriteLogMessage( GetErrorMessage( iRc )); } diff --git a/src/sql/xbset.cpp b/src/sql/xbset.cpp index 98de309..d0e8821 100755 --- a/src/sql/xbset.cpp +++ b/src/sql/xbset.cpp @@ -63,17 +63,16 @@ xbInt16 xbSql::SqlSet( const xbString &sCmdLine ){ if( sToken == '^' ){ if(( iRc = uda.DelTokenForKey( sKey )) != XB_NO_ERROR ){ - iErrorStop = 10; + iErrorStop = 100; throw iRc; } } else { if(( iRc = uda.UpdTokenForKey( sKey, sToken )) != XB_NO_ERROR ){ - iErrorStop = 10; + iErrorStop = 110; throw iRc; } } } - } catch (xbInt16 iRc ){ xbString sMsg; diff --git a/src/sql/xbsql.cpp b/src/sql/xbsql.cpp index 5261b1a..12d3379 100755 --- a/src/sql/xbsql.cpp +++ b/src/sql/xbsql.cpp @@ -123,9 +123,6 @@ xbInt16 xbSql::ExecuteNonQuery( const xbString &sCmdLine ) { else if( sNode1 == "USE" ) iRc = SqlUse( sCmd ); - else if( sNode1 == "SELECT" ) - iRc = SqlSelect( sCmd ); - else if( sNode1 == "SET" ) iRc = SqlSet( sCmd ); diff --git a/src/sql/xbstmt.cpp b/src/sql/xbstmt.cpp new file mode 100755 index 0000000..61c84fd --- /dev/null +++ b/src/sql/xbstmt.cpp @@ -0,0 +1,679 @@ +/* xbsql.cpp + +XBase64 Software Library + +Copyright (c) 1997,2003,2014,2022 Gary A Kunkel + +The xb64 software library is covered under the terms of the GPL Version 3, 2007 license. + +Email Contact: + + XDB-devel@lists.sourceforge.net + XDB-users@lists.sourceforge.net + +*/ + +#include "xbase.h" + +#ifdef XB_SQL_SUPPORT + +namespace xb{ + +/***********************************************************************/ +xbStmt::xbStmt( xbXBase *x ){ + xbase = x; + pTblList = NULL; + fl = NULL; + ulFromPos = 0; + ulWherePos = 0; + ulOrderByPos = 0; + ulGroupByPos = 0; + ulHavingPos = 0; +} + +/***********************************************************************/ +xbStmt::~xbStmt(){ + + std::cout << "xbStmt::~xbStmt() - need to release all allocated structures and memory here\n"; + +} + +/***********************************************************************/ +xbInt16 xbStmt::CvtSqlExp2DbaseExp( const xbString &sExpIn, xbString &sExpOut ){ + + // convert Ansi SQL expression to a DBase compatible expression + + xbInt16 iRc = 0; + xbInt16 iErrorStop = 0; + + try{ + + // convert tablename.fieldname to tablename->fieldname + sExpOut = sExpIn[1]; + xbUInt32 ulPos = 2; + while( ulPos < sExpIn.Len() ){ + if( sExpIn[ulPos] == '.' && sExpIn[ulPos-1] != ' ' && sExpIn[ulPos+1] != ' ' ) + sExpOut += "->"; + else + sExpOut += sExpIn[ulPos]; + ulPos++; + } + sExpOut += sExpIn[ulPos]; + + sExpOut.Replace( " AND ", " .AND. ", 0 ); + sExpOut.Replace( " and ", " .AND. ", 0 ); + sExpOut.Replace( " OR ", " .OR. ", 0 ); + sExpOut.Replace( " or ", " .OR. ", 0 ); + } + catch (xbInt16 iRc ){ + xbString sMsg; + sMsg.Sprintf( "xbStmt::CvtSqlExp2DbaseExp() Exception Caught. Error Stop = [%d] rc = [%d]", iErrorStop, iRc ); + xbase->WriteLogMessage( sMsg.Str() ); + xbase->WriteLogMessage( GetErrorMessage( iRc )); + } + return iRc; +} + + +/***********************************************************************/ +#ifdef XB_DEBUG_SUPPORT +xbInt16 xbStmt::DumpStmtInternals(){ + + std::cout << "************** Dump Statment Internals ***************" << std::endl; + std::cout << "Statement [" << sStmt << "]\n"; + std::cout << "Fields [" << sFields << "]\n"; + std::cout << "From [" << sFrom << "]\n"; + std::cout << "Where [" << sWhere << "]\n"; + std::cout << "Order By [" << sOrderBy << "]\n"; + std::cout << "Group By [" << sGroupBy << "]\n"; + std::cout << "Having [" << sHaving << "]\n"; + + xbTblJoin *p = pTblList; + if( p ){ + std::cout << "Tbl List\n"; + std::cout << "Type,Name,Alias,Exp\n"; + while( p ){ + std::cout << p->cJoinType << "," << p->sTableName.Str() << "," << p->sAlias.Str() << "," << p->sJoinExp.Str() << "\n"; + p = p->next; + } + } + std::cout << "*************** End of Internals ************" << std::endl; + return 0; +} + +xbInt16 xbStmt::Test(){ + xbString s; + + s = "(ABC)"; + std::cout << "[" << s.Str() << "][" << GetParenCnt( s ) << "]\n"; + s = "(ABC) )"; + std::cout << "[" << s.Str() << "][" << GetParenCnt( s ) << "]\n"; + s = "("; + std::cout << "[" << s.Str() << "][" << GetParenCnt( s ) << "]\n"; + s = "( ("; + std::cout << "[" << s.Str() << "][" << GetParenCnt( s ) << "]\n"; + s = "aaa)"; + std::cout << "[" << s.Str() << "][" << GetParenCnt( s ) << "]\n"; + s = "aaa"; + std::cout << "[" << s.Str() << "][" << GetParenCnt( s ) << "]\n"; + + return 0; + +} +#endif // XB_DEBUG_SUPPORT + + +/***********************************************************************/ +xbInt16 xbStmt::GetNextFromSeg( const xbString &sLineIn, xbString &sFromSegOut ){ + + xbInt16 iRc = 0; + xbInt16 iErrorStop = 0; + xbInt16 iParenCtr = 0; + xbUInt32 ulPos = 1; + xbUInt32 ulTsp = 0; // token start position + xbBool bDone = xbFalse; + xbString sToken; + + try{ + sFromSegOut = ""; + while( !bDone ){ + ulTsp = GetNextToken( sLineIn, sToken, ulPos ); + if( sToken == "" ){ + bDone = xbTrue; + + } else { + iParenCtr += GetParenCnt( sToken ); + if( iParenCtr == 0 && ulPos > 1 ){ + sToken.ToUpperCase(); + if( sToken == "LEFT" || sToken == "RIGHT" || sToken == "FULL" || + sToken == "INNER" || sToken == "OUTER" || sToken[1] == '(' ){ + bDone = xbTrue; + } + } + } + ulPos += (sToken.Len() + 1); + } + if( ulTsp > 1 ) + sFromSegOut.Assign( sLineIn, 1, ulTsp - 2 ); + } + catch (xbInt16 iRc ){ + xbString sMsg; + sMsg.Sprintf( "xbStmt::GetNextFromSeg() Exception Caught. Error Stop = [%d] rc = [%d]", iErrorStop, iRc ); + xbase->WriteLogMessage( sMsg.Str() ); + xbase->WriteLogMessage( GetErrorMessage( iRc )); + } + return iRc; +} + +/***********************************************************************/ + +xbUInt32 xbStmt::GetNextToken( const xbString &sCmdLineIn, xbString &sTokenOut, xbUInt32 ulStartPos ){ + + // input + // sCmdLine - Entire Command line + // ulStartPos - Where to start searching for the next token + // returns the position of the beginning of the token in the main command line + + xbUInt32 ul = ulStartPos; + xbUInt32 ulTokenStartPos = 0; + sTokenOut = ""; + + while( ul <= sCmdLineIn.Len() && sCmdLineIn[ul] == ' ' ) + ul++; + ulTokenStartPos = ul; + + while( ul <= sCmdLineIn.Len() && sCmdLineIn[ul] != ' ' ){ + sTokenOut += sCmdLineIn[ul]; + ul++; + } + + return ulTokenStartPos; +} +/***********************************************************************/ +xbInt16 xbStmt::GetParenCnt( const xbString &sToken ){ + + xbInt16 iParenCnt = 0; + + for( xbUInt32 i = 1; i <= sToken.Len(); i++ ){ + if( sToken[i] == '(' ) + iParenCnt++; + else if( sToken[i] == ')' ) + iParenCnt--; + } + return iParenCnt; +} + + +/***********************************************************************/ +xbInt16 xbStmt::ParseFromStmt( const xbString &sFromLine ){ + + xbInt16 iRc = 0; + xbInt16 iErrorStop = 0; + xbBool bDone = xbFalse; + xbString sFromSeg; + xbString sLine = sFromLine; + + try{ + std::cout << "ParseFromSeg [" << sFromLine.Str() << "]\n"; + + while( !bDone ){ + if(( iRc = GetNextFromSeg( sLine, sFromSeg)) != XB_NO_ERROR ){ + iErrorStop = 100; + throw iRc; + } + if( sFromSeg.Len() == 0 ){ + bDone = xbTrue; + } else { + if(( iRc = ProcessFromSeg( sFromSeg )) != XB_NO_ERROR ){ + iErrorStop = 110; + throw iRc; + } + sLine.Ltrunc( sFromSeg.Len() ); + sLine.Ltrim(); + } + } + } + catch (xbInt16 iRc ){ + xbString sMsg; + sMsg.Sprintf( "xbStmt::ParseFromSeg() Exception Caught. Error Stop = [%d] rc = [%d]", iErrorStop, iRc ); + xbase->WriteLogMessage( sMsg.Str() ); + xbase->WriteLogMessage( GetErrorMessage( iRc )); + } + + return iRc; +} +/***********************************************************************/ +xbInt16 xbStmt::ParseStmt( const xbString &sCmdLine ){ + + xbInt16 iRc = 0; + xbInt16 iErrorStop = 0; + xbInt16 iParenCnt = 0; + xbUInt32 ulStartPos = 7; + xbUInt32 ulLen = 0; + + + xbBool bFromFound = xbFalse; + xbBool bWhereFound = xbFalse; + xbBool bOrderByFound = xbFalse; + xbBool bGroupByFound = xbFalse; + xbBool bHavingFound = xbFalse; + xbBool bDone = xbFalse; + + xbString sToken; + + sStmt.Set( sCmdLine ); + sStmt.Trim(); + + try{ + + std::cout << "ParseStmt - [" << sStmt << "]\n"; + + xbUInt32 ulPos = ulStartPos; + + while( !bFromFound && !bDone ){ + ulPos = GetNextToken( sStmt, sToken, ulPos ); + if( sToken == "" ){ + bDone = xbTrue; + } else { + iParenCnt += GetParenCnt( sToken ); + if( iParenCnt == 0 ){ + sToken.ToUpperCase(); + if( sToken == "FROM" ){ + bFromFound = xbTrue; + ulFromPos = ulPos; + sFrom.Assign( sStmt, ulFromPos ); + } + } + ulPos += sToken.Len() + 1; + } + } + + // look for the where clause + if( bFromFound ) + ulPos = ulFromPos + 5; + else + ulPos = ulStartPos; + + bDone = xbFalse; + while( !bWhereFound && !bDone ){ + ulPos = GetNextToken( sStmt, sToken, ulPos ); + if( sToken == "" ){ + bDone = xbTrue; + } else { + iParenCnt += GetParenCnt( sToken ); + if( iParenCnt == 0 ){ + sToken.ToUpperCase(); + if( sToken == "WHERE" ){ + bWhereFound = xbTrue; + ulWherePos = ulPos; + sWhere.Assign( sStmt, ulWherePos+6 ); + } + } + ulPos += sToken.Len() + 1; + } + } + + + // look for the order clause + if( bWhereFound ) + ulPos = ulWherePos + 6; + else if( bFromFound ) + ulPos = ulFromPos + 5; + else + ulPos = ulStartPos; + + bDone = xbFalse; + while( !bOrderByFound && !bDone ){ + ulPos = GetNextToken( sStmt, sToken, ulPos ); + if( sToken == "" ){ + bDone = xbTrue; + } else { + iParenCnt += GetParenCnt( sToken ); + if( iParenCnt == 0 ){ + sToken.ToUpperCase(); + if( sToken == "ORDER" ){ + xbString sToken2; + xbUInt32 ulPos2 = GetNextToken( sStmt, sToken2, ulPos + 6 ); + if( sToken2 != "" ){ + sToken2.ToUpperCase(); + if( sToken2 == "BY" ){ + bOrderByFound = xbTrue; + ulOrderByPos = ulPos; + sOrderBy.Assign( sStmt, ulPos2 + 3 ); + } + } + } + } + ulPos += sToken.Len() + 1; + } + } + + + bDone = xbFalse; + while( !bGroupByFound && !bDone ){ + ulPos = GetNextToken( sStmt, sToken, ulPos ); + if( sToken == "" ){ + bDone = xbTrue; + } else { + iParenCnt += GetParenCnt( sToken ); + if( iParenCnt == 0 ){ + sToken.ToUpperCase(); + if( sToken == "GROUP" ){ + xbString sToken2; + xbUInt32 ulPos2 = GetNextToken( sStmt, sToken2, ulPos + 6 ); + if( sToken2 != "" ){ + sToken2.ToUpperCase(); + if( sToken2 == "BY" ){ + bGroupByFound = xbTrue; + ulGroupByPos = ulPos; + sGroupBy.Assign( sStmt, ulPos2 + 3 ); + } + } + } + } + ulPos += sToken.Len() + 1; + } + } + + bDone = xbFalse; + while( !bHavingFound && !bDone ){ + ulPos = GetNextToken( sStmt, sToken, ulPos ); + if( sToken == "" ){ + bDone = xbTrue; + } else { + iParenCnt += GetParenCnt( sToken ); + if( iParenCnt == 0 ){ + sToken.ToUpperCase(); + if( sToken == "HAVING" ){ + bHavingFound = xbTrue; + ulHavingPos = ulPos; + sHaving.Assign( sStmt, ulHavingPos + 7 ); + } + } + ulPos += sToken.Len() + 1; + } + } + + + // do the fields part + if( bFromFound ) + ulLen = ulFromPos - 7; + else if( bWhereFound ) + ulLen = ulWherePos - 7; + else if( bOrderByFound ) + ulLen = ulOrderByPos - 7; + else if( bGroupByFound ) + ulLen = ulGroupByPos - 7; + else if( bHavingFound ) + ulLen = ulHavingPos - 7; + else + ulLen = sStmt.Len() - 7; + sFields.Assign( sStmt, 7, ulLen ); + sFields.Trim(); + + // do the FROM part + if( bFromFound ){ + if( bWhereFound ) + ulLen = ulWherePos - ulFromPos; + else if( bOrderByFound ) + ulLen = ulOrderByPos - ulFromPos; + else if( bGroupByFound ) + ulLen = ulGroupByPos - ulFromPos; + else if( bHavingFound ) + ulLen = ulHavingPos - ulFromPos; + else + ulLen = sStmt.Len() - ulFromPos; + sFrom.Resize( ulLen ); + sFrom.Trim(); + + } + + + // do the WHERE part + if( bWhereFound ){ + ulLen = 0; + if( bOrderByFound ) + ulLen = ulOrderByPos - ulWherePos - 6; + else if( bGroupByFound ) + ulLen = ulGroupByPos - ulWherePos - 6; + else if( bHavingFound ) + ulLen = ulHavingPos - ulWherePos - 6; + + if( ulLen > 0 ) + sWhere.Resize( ulLen ); + sWhere.Trim(); + } + + // FIXME if there is more than one space between ORDER and BY then this doesn't work quite right + // do the ORDER BY part + if( bOrderByFound ){ + if( bGroupByFound ) + ulLen = ulGroupByPos - ulOrderByPos - 9; + else if( bHavingFound ) + ulLen = ulHavingPos - ulOrderByPos - 9; + else + ulLen = sStmt.Len() - ulOrderByPos - 9; + sOrderBy.Resize( ulLen ); + sOrderBy.Trim(); + } + + // FIXME if there is more than one space between ORDER and BY then this doesn't work quite right + // do the GROUP BY part + if( bGroupByFound ){ + if( bHavingFound ) + ulLen = ulHavingPos - ulGroupByPos - 9; + else + ulLen = sStmt.Len() - ulGroupByPos - 9; + sGroupBy.Resize( ulLen ); + sGroupBy.Trim(); + } + + if( bFromFound ){ + if(( iRc = ParseFromStmt( sFrom )) != XB_NO_ERROR ){ + iErrorStop = 100; + throw iRc; + } + } + + } + catch (xbInt16 iRc ){ + xbString sMsg; + sMsg.Sprintf( "xbStmt::ParseStmt() Exception Caught. Error Stop = [%d] rc = [%d]", iErrorStop, iRc ); + xbase->WriteLogMessage( sMsg.Str() ); + xbase->WriteLogMessage( GetErrorMessage( iRc )); + } + + return iRc; +} + +/***********************************************************************/ +xbInt16 xbStmt::ProcessFromSeg( const xbString &sFromSeg ){ + + + xbInt16 iRc = 0; + xbInt16 iErrorStop = 0; + char cType = ' '; + xbString sToken; + xbString sTable; + xbString sAlias; + xbString sWork; + xbString sExp; + xbUInt32 iPos; + xbBool bDone; + xbExp * pJoinExp = NULL; + + try{ + + std::cout << "ProcessFromSeg for segment [" << sFromSeg.Str() << "]\n"; + + GetNextToken( sFromSeg, sToken, 1 ); + sToken.ToUpperCase(); + + std::cout << "sToken1 = [" << sToken.Str() << "]\n"; + + if( sToken == "FROM" ){ + // FROM has to be the first statement, and exist only once + if( pTblList ){ + iErrorStop = 100; + iRc = XB_PARSE_ERROR; + throw iRc; + } + cType = 'M'; + } else if( sToken == "LEFT" ) + cType = 'L'; + else if( sToken == "RIGHT" ) + cType = 'R'; + else if( sToken == "RIGHT" ) + cType = 'R'; + else if( sToken == "INNER" ) + cType = 'I'; + else if( sToken == "OUTER" || sToken == "FULL" ) + cType = 'O'; + else if( sToken[1] == '(' ) + cType = 'Q'; + else{ + iErrorStop = 100; + iRc = XB_PARSE_ERROR; + throw iRc; + } + + + if( cType == 'M' ){ + // expecting "FROM table" or "FROM table alias" + iPos = GetNextToken( sFromSeg, sToken, 5 ); + sTable = sToken; + + iPos = GetNextToken( sFromSeg, sToken, iPos + sToken.Len() + 1 ); + if( sToken.Len() > 0 ) + sAlias = sToken; + else + sAlias = ""; + + sWork = sToken; + sWork.ToUpperCase(); + iPos = sWork.Pos( ".DBF" ); + if( iPos == 0 ) + sTable += ".DBF"; + + // std::cout << "table = [" << sTable.Str() << "] alias = [" << sAlias.Str() << "]\n"; + + } else if( cType != 'Q' ){ + sWork = sFromSeg; + sWork.ToUpperCase(); + iPos = sWork.Pos( " ON " ); + if( iPos == 0 ){ + iErrorStop = 110; + iRc = XB_PARSE_ERROR; + throw iRc; + } + bDone = xbFalse; + + sWork.Assign( sFromSeg, iPos + 4 ); + CvtSqlExp2DbaseExp( sWork, sExp ); + sExp.Trim(); + std::cout << "ON = [" << sExp.Str() << "]\n"; + + + // need to get past the keywords LEFT, FULL, OUTER, RIGHT, INNER and get the table name here + iPos = sToken.Len() + 1; + while( !bDone ){ + iPos = GetNextToken( sFromSeg, sToken, iPos ); + sWork = sToken; + sWork.ToUpperCase(); + if( sWork != "LEFT" && sWork != "RIGHT" && sWork != "INNER" && sWork != "OUTER" && sWork != "FULL" && sWork != "JOIN" ) + bDone = xbTrue; + else + iPos += (sWork.Len() + 1); + } + + sTable = sToken; + GetNextToken( sFromSeg, sAlias, iPos + sTable.Len() + 1 ); + sWork.ToUpperCase(); + iPos = sWork.Pos( ".DBF" ); + if( iPos == 0 ) + sTable += ".DBF"; + sWork = sAlias; + sWork.ToUpperCase(); + if( sWork == "ON" ) + sAlias = ""; + } + + + // open table + //std::cout << "attempting to open table " << sTable.Str() << "\n"; + xbDbf *d = NULL; + if(( iRc = xbase->OpenHighestVersion( sTable, sAlias, &d )) != XB_NO_ERROR ){ + iErrorStop = 120; + throw iRc; + } + + + + // build out table linkage expression + if( cType == 'M' ) + sExp = "MASTER"; + else if( cType == 'L' ){ + pJoinExp = new xbExp( xbase ); + + // std::cout << "Parsing expression [" << sExp.Str() << "]\n"; + if(( iRc = pJoinExp->ParseExpression( d, sExp )) != XB_NO_ERROR ){ + iErrorStop = 130; + throw iRc; + } + } + + + // attach to correct location in list + + + // update the UpdateJoinList routine as appropriate +// if(( iRc = UpdateJoinList( cType, sTable, sAlias, NULL, NULL, NULL, NULL )) != XB_NO_ERROR ){ + + if(( iRc = UpdateJoinList( cType, sTable, sAlias, sExp, d, pJoinExp )) != XB_NO_ERROR ){ + iErrorStop = 110; + throw iRc; + } + + + } + catch (xbInt16 iRc ){ + xbString sMsg; + sMsg.Sprintf( "xbStmt::ProcessFromSeg() Exception Caught. Error Stop = [%d] rc = [%d]", iErrorStop, iRc ); + xbase->WriteLogMessage( sMsg.Str() ); + xbase->WriteLogMessage( GetErrorMessage( iRc )); + } + return iRc; +} + +/***********************************************************************/ +xbInt16 xbStmt::UpdateJoinList( char cType, const xbString &sTableName, const xbString &sAlias, const xbString &sExp, xbDbf *d, xbExp *e ){ +// xbTag *t ){ + + + std::cout << "Update join list for table " << sTableName.Str() << "\n"; + + xbTblJoin *pTj; + if(( pTj = (xbTblJoin *) calloc( 1, sizeof( xbTblJoin ))) == NULL ) + return XB_NO_MEMORY; + + pTj->cJoinType = cType; + pTj->sTableName.Set( sTableName ); + pTj->sAlias.Set( sAlias ); + pTj->sJoinExp.Set( sExp ); + pTj->pDbf = d; + pTj->pLinkExp = e; + +/* + + pTj->pTag = t; +*/ + + if( cType == 'M' ) + pTblList = pTj; + + return 0; +} +/***********************************************************************/ +} /* namespace */ +#endif /* XB_SQL_SUPPORT */ + -- cgit v1.2.3