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.cpp270
1 files changed, 270 insertions, 0 deletions
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
generated by cgit v1.2.3 (git 2.25.1) at 2024-05-14 10:19:56 +0000