OOFile Manual

OOFTST 20 - ad-hoc queries

This sample demonstrates how to create an ad-hoc query.

#include "oofile.h"	// the general oofile library
#include "ooftst01.h"	// the declarations for the database classes we will be using in this test.

These are two procedures we will define later. The first one takes in a set of search strings to look for and whether or not we wish the substrings to be ANDed or ORed together. It searches through the two fields of LastName and OtherNames.

void anyName(const char* searchStrings[], bool isOr, unsigned short numSch);

The second procedure does mostly the same thing but searches only through one field (lastNames) and ******* doesn't give the option to AND or OR the substrings together, they are all ????*ed

void lastName(const char* searchStrings[], unsigned short numSch);

******* This is a quickly defined piece of code to count how many entries there are in something I am not aware of yet

#define numEntries(A) sizeof(A)/sizeof(A[0])

Now we declare our database and table.

	TEST_CONNECT    theDB;
	dbPeople     People;

int main()
{
	cout << "OOFILE Validation Suite - Test 20\n"
		 << "Demonstration of ad-hoc query building\n";

This is a bit of complicated filename logic to support different backends with this one test program

#ifdef TESTING_CTREE
	theDB.useSeparateFiles();

	#ifdef _Macintosh
		const char* kExistsName =  ":ooftst01:People.dat";
		const char* kDatabaseName = ":ooftst01:";
	#else
		const char* kExistsName =   "People.dat"
		const char* kDatabaseName = "";
	#endif

#else
	#ifdef TESTING_DBASE
		#ifdef _Macintosh
			const char* kExistsName =  ":ooftst01:People.dbf";
			const char* kDatabaseName = ":ooftst01:";
		#else
			const char* kExistsName =   "People.dbf"
			const char* kDatabaseName = "";
		#endif

	#else
		#ifdef _Macintosh
			const char* kDatabaseName = ":ooftst01:ooftst01.db";
		#else
			const char* kDatabaseName = "ooftst01.db";
		#endif	
		const char* kExistsName = kDatabaseName;
	#endif
#endif

Now we open or create the database .

	if (dbConnect::fileExists(kExistsName)) {
		theDB.openConnection(kDatabaseName);
		cout << "Just opened...\n" << theDB << endl << endl;
	}
	else {
		theDB.newConnection(kDatabaseName);
		People.AddTestData();
	}

We first select all the records and print the database (through a temporary dbView) so we can see its initial state.

	People.selectAll();
	cout << "Listing records\n" << (dbView(People) << People.LastName << People.OtherNames);

Now we create our searchString array, we just assign some values here, in real life we would probably query the user to enter them, but this way it is much more controlled.

	const char* userEntries[] = {"Dent", "Andy"};

Then we call our testing procedures, passing in the array to both, and trying it once as an AND operation and once as an OR operation.

	anyName(userEntries, true, numEntries(userEntries))
	anyName(userEntries, false, numEntries(userEntries));

Now we create another searchString array and try using the other test procedure.

	const char* userEntries2[] = {"Dent", "Tay*"};	
	lastName(userEntries2, numEntries(userEntries));
	cout << "Test Completed" << endl;

	return EXIT_SUCCESS;
}

This procedure quesries the database, looking for the given substrings within the fields it has governance over (in this case, the two fields LastName and OtherNames).

void anyName(const char* searchStrings[], bool isOr, unsigned short numSch)
{

First we declare our query.

	dbQuery theQuery;

Now, depending on what has been pased to this parameter, either all the substrings must be in the search-string (AND) or any combination of the substrings (OR). This next section is just output depending on whether isOr is true or false.

	if (isOr)
		cout << "OR";
	else
		cout << "AND";
	cout << " Querying either Last or Othernames = \n";

We query for each string in the list of substrings, so must loop through each like so:

	for (unsigned short i=0; i<numSch; i++) {
		cout << "   " << searchStrings[i] << endl;

We must construct our query clauses on the heap otherwise they are temporaries which won't exist by the time we do the search(). The clauses are assigned the value of the sub-string array??

**************** check this one and explain more fully

		dbQueryClause* term1 = new dbQueryBinary(
			People.LastName == searchStrings[i]
		);
		dbQueryClause* term2 = new dbQueryBinary(
			People.OtherNames == searchStrings[i]
		);

The following is a bit subtle - the operator || which takes pointers creates a dbQueryBinaryComboOwner which will delete the sub-clauses

******* don't understand this bit

		if (isOr) {
//			theQuery |= (*term1 || term2); equivalent to the following
			theQuery |= term1;
			theQuery |= term2;
		}
		else
			theQuery &= (*term1 || term2);
	}

Now we search the database, using the query to be the selection made. We then print the results.

	People.search(theQuery);

	cout << "\nListing result of query\n" 
		 << (dbView(People) << People.LastName << People.OtherNames) << endl;

void
lastName(const char* searchStrings[], unsigned short numSch)
{
	dbQuery theQuery;
	cout << "Querying Lastname = any of\n";

	for (unsigned short i=0; i<numSch; i++) {
		cout << "   " << searchStrings[i] << endl;

// construct clauses on the heap otherwise they are temporaries
// which won't exist by the time we do the search()

		dbQueryClause* theTerm = new dbQueryBinary(
			People.LastName == searchStrings[i]
		);

		theQuery |= theTerm;
	}
	People.search(theQuery);

	cout << "\nListing result of query\n" 
		 << (dbView(People) << People.LastName << People.OtherNames) << endl;

Feature index