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/tests/xb_test_sql.cpp | 151 +++++++++++++++++++++++++++++++++------------- 1 file changed, 109 insertions(+), 42 deletions(-) (limited to 'src/tests/xb_test_sql.cpp') diff --git a/src/tests/xb_test_sql.cpp b/src/tests/xb_test_sql.cpp index 20c8514..99a4f0b 100755 --- a/src/tests/xb_test_sql.cpp +++ b/src/tests/xb_test_sql.cpp @@ -2,7 +2,7 @@ XBase64 Software Library -Copyright (c) 1997,2003,2014, 2022 Gary A Kunkel +Copyright (c) 1997,2003,2014, 2022,2023 Gary A Kunkel The xb64 software library is covered under the terms of the GPL Version 3, 2007 license. @@ -44,9 +44,10 @@ int main( int argCnt, char **av ) po = 2; } - - xbSchema MySqlRecord[] = +/* + xbSchema MyAddressRecord[] = { + { "ADDRESS", XB_CHAR_FLD, 30, 0 }, { "CITY", XB_CHAR_FLD, 30, 0 }, { "STATE", XB_CHAR_FLD, 2, 0 }, { "ZIPCODE", XB_NUMERIC_FLD, 9, 0 }, @@ -56,9 +57,23 @@ int main( int argCnt, char **av ) { "",0,0,0 } }; + above structure below, depending on how table is created + + sSql = "CREATE TABLE Address.DBF ( ADDRESS CHAR(30), CITY CHAR(30), STATE CHAR(2), ZIPCODE NUMERIC(9,0), NOTES VARCHAR, LASTUPDATE DATE, ACTIVE LOGICAL )"; +*/ + + xbSchema MyZipRecord[] = + { + { "ZIPCODE", XB_NUMERIC_FLD, 9, 0 }, + { "CITY", XB_CHAR_FLD, 30, 0 }, + { "STATE", XB_CHAR_FLD, 2, 0 }, + { "",0,0,0 } + }; + xbXBase x; #ifdef XB_LOGGING_SUPPORT + x.SetLogDirectory( PROJECT_LOG_DIR ); x.EnableMsgLogging(); if( po ){ std::cout << "Logfile is [" << x.GetLogFqFileName().Str() << "]" << std::endl; @@ -69,9 +84,7 @@ int main( int argCnt, char **av ) #endif - x.SetDataDirectory( PROJECT_DATA_DIR ); - x.EnableMsgLogging(); InitTime(); xbSql sql( &x ); @@ -81,36 +94,48 @@ int main( int argCnt, char **av ) xbDbf4 SqlDbf( &x ); // version 4 dbf file + xbDbf4 SqlDbfZ( &x ); // version 4 dbf file + - // clean up any things possibly needing cleaned up - xbString sDir; - SqlDbf.GetFileDirPart( sDir ); - xbString sDbfName; - xbString sDbtName; - xbString sMdxName; - sDbfName.Sprintf( "%sTestSQL.DBF", sDir.Str()); - sDbtName.Sprintf( "%sTestSQL.DBR", sDir.Str()); - sMdxName.Sprintf( "%sTestSQL.MDX", sDir.Str()); - SqlDbf.xbRemove( sDbfName ); - SqlDbf.xbRemove( sDbtName ); - SqlDbf.xbRemove( sMdxName ); - sDbfName.Sprintf( "%sTestSqlR.DBF", sDir.Str()); - sDbtName.Sprintf( "%sTestSqlR.DBT", sDir.Str()); - sMdxName.Sprintf( "%sTestSqlR.MDX", sDir.Str()); - SqlDbf.xbRemove( sDbfName ); - SqlDbf.xbRemove( sDbtName ); - SqlDbf.xbRemove( sMdxName ); - - - iRc2 = SqlDbf.CreateTable( "TestSQL.DBF", "TestSQL", MySqlRecord, XB_OVERLAY, XB_MULTI_USER ); + + + sSql = "DROP TABLE IF EXISTS AddressR.DBF"; + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "DropTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); + + + + sSql = "DROP TABLE IF EXISTS Address.DBF"; + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "DropTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); + + + + sSql = "CREATE TABLE Address.DBF ( ADDRESS CHAR(30), CITY CHAR(30), STATE CHAR(2), ZIPCODE NUMERIC(9,0), NOTES VARCHAR, LASTUPDATE DATE, ACTIVE LOGICAL )"; + + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "CreateTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); + +/* + non sql way to create a table + iRc2 = SqlDbf.CreateTable( "Address.DBF", "Address", MyAddressRecord, XB_OVERLAY, XB_MULTI_USER ); iRc += TestMethod( po, "CreateTable()", (xbInt32) iRc2, XB_NO_ERROR ); if( iRc2 ) x.DisplayError( iRc2 ); +*/ + +// return 0; #ifdef XB_MDX_SUPPORT - sSql = "CREATE INDEX tag1 ON TestSQL.DBF( CITY, STATE, DTOS( LASTUPDATE )) FILTER .NOT. DELETED()"; - // xbString sSql = "CREATE INDEX tag1 ON TestSQL.DBF( CITY, STATE )"; + sSql = "CREATE INDEX tag1 ON Address.DBF( CITY, STATE, DTOS( LASTUPDATE )) FILTER .NOT. DELETED()"; + // xbString sSql = "CREATE INDEX tag1 ON Address.DBF( CITY, STATE )"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqL CreateIndex()", (xbInt32) iRc2, XB_NO_ERROR ); @@ -118,65 +143,107 @@ int main( int argCnt, char **av ) x.DisplayError( iRc2 ); #endif // XB_MDX_SUPPORT - sSql = "INSERT INTO TestSQL (CITY, STATE, ZIPCODE, NOTES, LASTUPDATE, ACTIVE ) VALUES ( 'San Diego', 'CA', 92007, 'San Diego is a cool place', '1989-02-09', 'Y')"; + sSql = "INSERT INTO Address (CITY, STATE, ZIPCODE, NOTES, LASTUPDATE, ACTIVE ) VALUES ( 'San Diego', 'CA', 92007, 'San Diego is a cool place', '1989-02-09', 'Y')"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqlInsert()", (xbInt32) iRc2, XB_NO_ERROR ); if( iRc2 ) x.DisplayError( iRc2 ); - sSql = "INSERT INTO TestSQL (CITY, STATE, ZIPCODE, NOTES, LASTUPDATE, ACTIVE ) VALUES ( 'Dallas', 'TX', 76248, 'Dallas is hot in the summer', '1989-02-09', 'N')"; + sSql = "INSERT INTO Address (CITY, STATE, ZIPCODE, NOTES, LASTUPDATE, ACTIVE ) VALUES ( 'Dallas', 'TX', 76248, 'Dallas is hot in the summer', '1989-02-09', 'N')"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqlInsert()", (xbInt32) iRc2, XB_NO_ERROR ); if( iRc2 ) x.DisplayError( iRc2 ); - sSql = "ALTER TABLE TestSQL.DBF RENAME TO TestSqlR.DBF"; + sSql = "ALTER TABLE Address.DBF RENAME TO AddressR.DBF"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqlAlterTable()", (xbInt32) iRc2, XB_NO_ERROR ); if( iRc2 ) x.DisplayError( iRc2 ); - x.DisplayTableList(); - - - sSql = "DELETE FROM TestSqlR.DBF WHERE CITY='San Diego'"; + sSql = "DELETE FROM AddressR.DBF WHERE CITY='San Diego'"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_NO_ERROR ); if( iRc2 ) x.DisplayError( iRc2 ); - sSql = "UNDELETE FROM TestSqlR.DBF WHERE CITY='San Diego'"; + sSql = "UNDELETE FROM AddressR.DBF WHERE CITY='San Diego'"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqlUndelete()", (xbInt32) iRc2, XB_NO_ERROR ); if( iRc2 ) x.DisplayError( iRc2 ); - sSql = "DELETE FROM TestSqlR.DBF"; + sSql = "DELETE FROM AddressR.DBF"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_NO_ERROR ); if( iRc2 ) x.DisplayError( iRc2 ); - sSql = "UNDELETE FROM TestSqlR.DBF"; + sSql = "UNDELETE FROM AddressR.DBF"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_NO_ERROR ); if( iRc2 ) x.DisplayError( iRc2 ); - sSql = "DELETE FROM TestSqlR.DBF WHERE BAD='EXPRESSION'"; + iRc2 = SqlDbfZ.CreateTable( "ZipCode.DBF", "", MyZipRecord, XB_OVERLAY, XB_MULTI_USER ); + iRc += TestMethod( po, "CreateTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); + + +// sSql = "INSERT INTO ZipCode.DBF ( ZIPCODE, CITY, STATE ) VALUES ( 75087, 'Rockwall', 'TX' )"; + + +// std::cout << "---------------------------------------------------------\n"; +// std::cout << sSql.Str() << "\n"; + + sSql = "INSERT INTO ZipCode ( CITY ) VALUES ( 'city' )"; + iRc2 = sql.ExecuteNonQuery( sSql ); + + + iRc += TestMethod( po, "SqlInsert()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); + + + +//**************** work in progress + +/* + sSql = "DELETE FROM AddressR.DBF WHERE BAD='EXPRESSION'"; iRc2 = sql.ExecuteNonQuery( sSql ); iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_INVALID_FIELD_NAME ); +*/ // if( iRc2 ) // x.DisplayError( iRc2 ); -// sSql = "SELECT CITY, STATE ZIPCODE FROM TestSQL.DBF WHERE CITY IS NOT NULL"; -// iRc += TestMethod( po, "Select()", SqlDbf.Select( sSql ), XB_NO_ERROR ); - iRc += TestMethod( po, "Close()", SqlDbf.Close(), XB_NO_ERROR ); + iRc += TestMethod( po, "Close()", SqlDbfZ.Close(), XB_NO_ERROR ); + + +// return 0; + +// std::cout << "---------------------------------------------------------\n"; + + xbStmt sqlQry1( &x ); +// sSql = "SELECT CITY, STATE, ZIPCODE FROM Address.DBF T LEFT JOIN LJ.DBF LJ WHERE CITY IS NOT NULL ORDER BY 2 GROUP BY STATE HAVING ZIPCODE .NOT. NULL"; +// sSql = "SELECT CITY, STATE, ZIPCODE FROM AddressR.DBF T WHERE CITY IS NOT NULL ORDER BY 2 GROUP BY STATE HAVING ZIPCODE .NOT. NULL"; + +// sSql = "SELECT CITY, STATE, ZIPCODE FROM AddressR A LEFT JOIN ZipCode Z ON A.ZIPCODE = Z.ZIPCODE WHERE CITY IS NOT NULL ORDER BY 2 GROUP BY STATE HAVING ZIPCODE .NOT. NULL"; +// sSql = "SELECT M.ID, M.LEFTFK0, L0.CFLD FROM MAIN0 M LEFT JOIN LEFT0 L0 ON M.LEFTFK0 = L0.LEFTFK0 WHERE M.ID IS NOT NULL"; + iRc += TestMethod( po, "Select()", sqlQry1.ExecuteQuery( sSql ), XB_NO_ERROR ); + + sqlQry1.DumpStmtInternals(); + + // sqlQry1.Test(); + + + x.DisplayTableList(); + if( po > 0 || iRc < 0 ) fprintf( stdout, "Total Errors = %d\n", iRc * -1 ); -- cgit v1.2.3