From bfa452a375ea0a0a3f95304a69186936567e5263 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?J=C3=B6rg=20Frings-F=C3=BCrst?= Date: Mon, 14 Aug 2023 19:45:36 +0200 Subject: New upstream version 4.1.4 --- src/tests/xb_test_sql.cpp | 259 ---------------------------------------------- 1 file changed, 259 deletions(-) delete mode 100755 src/tests/xb_test_sql.cpp (limited to 'src/tests/xb_test_sql.cpp') diff --git a/src/tests/xb_test_sql.cpp b/src/tests/xb_test_sql.cpp deleted file mode 100755 index 99a4f0b..0000000 --- a/src/tests/xb_test_sql.cpp +++ /dev/null @@ -1,259 +0,0 @@ -/* xb_test_sql.cpp - -XBase64 Software Library - -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. - -Email Contact: - - xb64-devel@lists.sourceforge.net - xb64-users@lists.sourceforge.net - -*/ - -// This program tests the SQL functions - -// usage: xb_test_sql QUITE|NORMAL|VERBOSE - - - -#include "xbase.h" - -using namespace xb; - -#include "tstfuncs.cpp" - - -int main( int argCnt, char **av ) -{ - int iRc = 0; - int iRc2 = 0; - int po = 1; /* print option */ - /* 0 - QUIET */ - /* 1 - NORMAL */ - /* 2 - VERBOSE */ - - xbString sSql; - - if( argCnt > 1 ) { - if( av[1][0] == 'Q' ) - po = 0; - else if( av[1][0] == 'V' ) - po = 2; - } - -/* - 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 }, - { "NOTES", XB_MEMO_FLD, 10, 0 }, - { "LASTUPDATE", XB_DATE_FLD, 8, 1 }, - { "ACTIVE", XB_LOGICAL_FLD, 1, 0 }, - { "",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; - } - xbString sMsg; - sMsg.Sprintf( "Program [%s] initializing...", av[0] ); - x.WriteLogMessage( sMsg ); - #endif - - - x.SetDataDirectory( PROJECT_DATA_DIR ); - InitTime(); - - xbSql sql( &x ); - - if( po > 0 ) - std::cout << "Default Data Directory is [" << x.GetDataDirectory().Str() << "]" << std::endl; - - - xbDbf4 SqlDbf( &x ); // version 4 dbf file - xbDbf4 SqlDbfZ( &x ); // version 4 dbf file - - - - - - 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 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 ); - if( iRc2 ) - x.DisplayError( iRc2 ); - #endif // XB_MDX_SUPPORT - - 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 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 Address.DBF RENAME TO AddressR.DBF"; - - iRc2 = sql.ExecuteNonQuery( sSql ); - iRc += TestMethod( po, "SqlAlterTable()", (xbInt32) iRc2, XB_NO_ERROR ); - if( iRc2 ) - x.DisplayError( iRc2 ); - - 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 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 AddressR.DBF"; - iRc2 = sql.ExecuteNonQuery( sSql ); - iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_NO_ERROR ); - if( iRc2 ) - x.DisplayError( iRc2 ); - - sSql = "UNDELETE FROM AddressR.DBF"; - iRc2 = sql.ExecuteNonQuery( sSql ); - iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_NO_ERROR ); - if( iRc2 ) - x.DisplayError( iRc2 ); - - - 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 ); - - - - 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 ); - -#ifdef XB_LOGGING_SUPPORT - sMsg.Sprintf( "Program [%s] terminating with [%d] errors...", av[0], iRc * -1 ); - x.WriteLogMessage( sMsg ); -#endif - - return iRc; -} - - \ No newline at end of file -- cgit v1.2.3 From c894a7cdd8686ea695602a23a511a3f1b0d047be Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?J=C3=B6rg=20Frings-F=C3=BCrst?= Date: Mon, 14 Aug 2023 21:07:46 +0200 Subject: New upstream version 4.1.4 --- src/tests/xb_test_sql.cpp | 270 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 270 insertions(+) create mode 100755 src/tests/xb_test_sql.cpp (limited to 'src/tests/xb_test_sql.cpp') diff --git a/src/tests/xb_test_sql.cpp b/src/tests/xb_test_sql.cpp new file mode 100755 index 0000000..3341895 --- /dev/null +++ b/src/tests/xb_test_sql.cpp @@ -0,0 +1,270 @@ +/* xb_test_sql.cpp + +XBase64 Software Library + +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. + +Email Contact: + + xb64-devel@lists.sourceforge.net + xb64-users@lists.sourceforge.net + +*/ + +// This program tests the SQL functions + +// usage: xb_test_sql QUITE|NORMAL|VERBOSE + + + +#include "xbase.h" + +using namespace xb; + +#include "tstfuncs.cpp" + + +int main( int argCnt, char **av ) +{ + int iRc = 0; + int iRc2 = 0; + int po = 1; /* print option */ + /* 0 - QUIET */ + /* 1 - NORMAL */ + /* 2 - VERBOSE */ + + xbString sSql; + + if( argCnt > 1 ) { + if( av[1][0] == 'Q' ) + po = 0; + else if( av[1][0] == 'V' ) + po = 2; + } + +/* + 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 }, + { "NOTES", XB_MEMO_FLD, 10, 0 }, + { "LASTUPDATE", XB_DATE_FLD, 8, 1 }, + { "ACTIVE", XB_LOGICAL_FLD, 1, 0 }, + { "",0,0,0 } + }; + + above structure corresponds to sql below + + 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; + } + xbString sMsg; + sMsg.Sprintf( "Program [%s] initializing...", av[0] ); + x.WriteLogMessage( sMsg ); + #endif + + + x.SetDataDirectory( PROJECT_DATA_DIR ); + + xbSql sql( &x ); + + if( po > 0 ) + std::cout << "Default Data Directory is [" << x.GetDataDirectory().Str() << "]" << std::endl; + +/* + // verify a delete on a non existant table doesn't crash things + sSql = "DROP TABLE IF EXISTS NoTable.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 ); + + #ifdef XB_MDX_SUPPORT + 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 ); + if( iRc2 ) + x.DisplayError( iRc2 ); + #endif // XB_MDX_SUPPORT + + 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 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 = "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 = "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 ); + + 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 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 AddressR.DBF"; + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); + + 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 AddressR.DBF WHERE BAD='EXPRESSION'"; + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_INVALID_FIELD_NAME ); + + + sSql = "DROP TABLE IF EXISTS AddressR.DBF"; + iRc += TestMethod( po, "Drop Table()", sql.ExecuteNonQuery( sSql ), XB_NO_ERROR ); + iRc += TestMethod( po, "Drop Table()", sql.ExecuteNonQuery( sSql ), XB_NO_ERROR ); + +*/ + + + sSql = "DROP TABLE IF EXISTS ZipCode.DBF"; + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "DropTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); + + + sSql = "CREATE TABLE ZipCode.DBF ( ZIPCODE NUMERIC(9,0), CITY CHAR(30), STATE CHAR(2) )"; + iRc2 = sql.ExecuteNonQuery( sSql ); + 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' )"; + iRc2 = sql.ExecuteNonQuery( sSql ); + + sSql = "INSERT INTO ZipCode.DBF ( ZIPCODE, CITY, STATE ) VALUES ( 75087, 'Rockwall', 'TX' )"; + iRc2 = sql.ExecuteNonQuery( sSql ); + + + +/* + + sSql = "CREATE INDEX ZipCode1.NDX ON ZipCode.DBF( ZIPCODE ) ASSOCIATE"; + iRc2 = sql.ExecuteNonQuery( sSql ); + if( iRc2 ) + x.DisplayError( iRc2 ); + iRc += TestMethod( po, "Create Index()", (xbInt32) iRc2, XB_NO_ERROR ); +*/ + + + sSql = "CREATE UNIQUE INDEX ZipCode2.NDX ON ZipCode.DBF( ZIPCODE ) ASSOCIATE"; + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "Create Index()", (xbInt32) iRc2, XB_KEY_NOT_UNIQUE ); + + + +/* + sSql = "DROP TABLE IF EXISTS ZipCode.DBF"; + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "DropTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); +*/ + + +// sSql = "INSERT INTO ZipCode.DBF ( ZIPCODE, CITY, STATE ) VALUES ( 75087, 'Rockwall', 'TX' )"; +// iRc2 = sql.ExecuteNonQuery( sSql ); + + + x.DisplayTableList(); + + + + +/* + sSql = "CREATE INDEX ZipCode.NDX ON Addres.DBF( ZIPCODE )"; + + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "CreateTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); +*/ + +// sSql = "DROP TABLE IF EXISTS AddressR.DBF"; +// iRc += TestMethod( po, "Drop Table()", sqlQry1.ExecuteQuery( sSql ), XB_NO_ERROR ); + + + + + // x.DisplayTableList(); + + x.CloseAllTables(); + + if( po > 0 || iRc < 0 ) + fprintf( stdout, "Total Errors = %d\n", iRc * -1 ); + +#ifdef XB_LOGGING_SUPPORT + sMsg.Sprintf( "Program [%s] terminating with [%d] errors...", av[0], iRc * -1 ); + x.WriteLogMessage( sMsg ); +#endif + + return iRc; +} + + \ No newline at end of file -- cgit v1.2.3