diff options
Diffstat (limited to 'src/tests/xb_test_sql.cpp')
-rwxr-xr-x | src/tests/xb_test_sql.cpp | 119 |
1 files changed, 65 insertions, 54 deletions
diff --git a/src/tests/xb_test_sql.cpp b/src/tests/xb_test_sql.cpp index 99a4f0b..3341895 100755 --- a/src/tests/xb_test_sql.cpp +++ b/src/tests/xb_test_sql.cpp @@ -57,11 +57,12 @@ int main( int argCnt, char **av ) { "",0,0,0 } }; - above structure below, depending on how table is created + 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 }, @@ -69,7 +70,7 @@ int main( int argCnt, char **av ) { "STATE", XB_CHAR_FLD, 2, 0 }, { "",0,0,0 } }; - +*/ xbXBase x; #ifdef XB_LOGGING_SUPPORT @@ -85,54 +86,32 @@ int main( int argCnt, char **av ) 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"; +/* + // 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 ); -/* - 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 )"; @@ -155,8 +134,13 @@ int main( int argCnt, char **av ) if( iRc2 ) x.DisplayError( iRc2 ); - sSql = "ALTER TABLE Address.DBF RENAME TO AddressR.DBF"; + 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 ) @@ -186,64 +170,91 @@ int main( int argCnt, char **av ) 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 ); - iRc2 = SqlDbfZ.CreateTable( "ZipCode.DBF", "", MyZipRecord, XB_OVERLAY, XB_MULTI_USER ); - iRc += TestMethod( po, "CreateTable()", (xbInt32) iRc2, XB_NO_ERROR ); + + 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 = "INSERT INTO ZipCode.DBF ( ZIPCODE, CITY, STATE ) VALUES ( 75087, 'Rockwall', 'TX' )"; - + 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 ); -// std::cout << "---------------------------------------------------------\n"; -// std::cout << sSql.Str() << "\n"; + sSql = "INSERT INTO ZipCode.DBF ( ZIPCODE, CITY, STATE ) VALUES ( 75087, 'Rockwall', 'TX' )"; + iRc2 = sql.ExecuteNonQuery( sSql ); - sSql = "INSERT INTO ZipCode ( CITY ) VALUES ( 'city' )"; + sSql = "INSERT INTO ZipCode.DBF ( ZIPCODE, CITY, STATE ) VALUES ( 75087, 'Rockwall', 'TX' )"; iRc2 = sql.ExecuteNonQuery( sSql ); - iRc += TestMethod( po, "SqlInsert()", (xbInt32) iRc2, XB_NO_ERROR ); + +/* + + 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 ); + -//**************** work in progress /* - sSql = "DELETE FROM AddressR.DBF WHERE BAD='EXPRESSION'"; + sSql = "DROP TABLE IF EXISTS ZipCode.DBF"; iRc2 = sql.ExecuteNonQuery( sSql ); - iRc += TestMethod( po, "SqlDelete()", (xbInt32) iRc2, XB_INVALID_FIELD_NAME ); + iRc += TestMethod( po, "DropTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); */ -// if( iRc2 ) -// x.DisplayError( iRc2 ); +// sSql = "INSERT INTO ZipCode.DBF ( ZIPCODE, CITY, STATE ) VALUES ( 75087, 'Rockwall', 'TX' )"; +// iRc2 = sql.ExecuteNonQuery( sSql ); + + + x.DisplayTableList(); - iRc += TestMethod( po, "Close()", SqlDbf.Close(), XB_NO_ERROR ); - iRc += TestMethod( po, "Close()", SqlDbfZ.Close(), XB_NO_ERROR ); -// return 0; -// std::cout << "---------------------------------------------------------\n"; +/* + sSql = "CREATE INDEX ZipCode.NDX ON Addres.DBF( ZIPCODE )"; - 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"; + iRc2 = sql.ExecuteNonQuery( sSql ); + iRc += TestMethod( po, "CreateTable()", (xbInt32) iRc2, XB_NO_ERROR ); + if( iRc2 ) + x.DisplayError( iRc2 ); +*/ -// 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 ); +// sSql = "DROP TABLE IF EXISTS AddressR.DBF"; +// iRc += TestMethod( po, "Drop Table()", sqlQry1.ExecuteQuery( sSql ), XB_NO_ERROR ); - sqlQry1.DumpStmtInternals(); - // sqlQry1.Test(); - x.DisplayTableList(); + // x.DisplayTableList(); + x.CloseAllTables(); if( po > 0 || iRc < 0 ) fprintf( stdout, "Total Errors = %d\n", iRc * -1 ); |