summaryrefslogtreecommitdiff
path: root/src/tests/xb_test_sql.cpp
diff options
context:
space:
mode:
Diffstat (limited to 'src/tests/xb_test_sql.cpp')
-rwxr-xr-xsrc/tests/xb_test_sql.cpp151
1 files changed, 109 insertions, 42 deletions
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 );
generated by cgit v1.2.3 (git 2.25.1) at 2024-05-15 15:06:23 +0000