OOFTST 7<header> - searching and sorting 

This header file is included in ooftest07 and 17.

This this sample demonstrates searching and sorting by different types of fields, and how to parameterize calls, passing in table and field references.

First, we must declare the class for the table we are going to use. We create a class very similar to that used in ooftst01 except that many of the fields are repeated in slightly different forms (eg the salary field has various types). We will use these later to see what happens when we load the same information into the different field types.

DECLARE_CLASS(dbPeople)
	dbChar		LastName, Othernames;
	dbLong		Salary, SalaryNoDup;
	dbShort		SalaryShort, SalaryShortNoDup;
	dbReal		SalaryReal, SalaryRealNoDup;
	dbDateTime	LastVisit, LastVisitNoDup;
	dbUshort	ShortUnsigned;
	dbUlong		LongUnsigned;    

Here, we give each field a size and name (where applicable to the field type).

	dbPeople() :
				dbTable("People"),
				LastName(40, "Last Name"),
				Othernames(80, "Other names"),
				Salary("Salary"),
				SalaryNoDup("Salary NoDup"),
				SalaryShort("SalaryShort"),
				SalaryShortNoDup("SalaryShortNoDup"),
				SalaryReal("SalaryReal"),
				SalaryRealNoDup("SalaryRealNoDup"),
				LastVisit("LastVisit"),
				LastVisitNoDup("LastVisitNoDup"),
				ShortUnsigned("ShortUnsigned"),
				LongUnsigned("LongUnsigned")				
	{  

Now, if we are using indexing, each field is given some sort of index. Differences between these are important for some of the tests, so some fields will only differ by the index type applied.

#ifdef TEST_INDEXES
			LastName.index(kIndexed);
			Othernames.index(kIndexNoDups);
			Salary.index(kIndexed);
			SalaryNoDup.index(kIndexNoDups);
			SalaryShort.index(kIndexed);
			SalaryShortNoDup.index(kIndexNoDups);
			SalaryReal.index(kIndexed);
			SalaryRealNoDup.index(kIndexNoDups);
			LastVisit.index(kIndexed);
			LastVisitNoDup.index(kIndexNoDups);
			ShortUnsigned.index(kIndexed);
			LongUnsigned.index(kIndexed);				
#endif
	};
	    

// my own data entry procedure
	void Add(const char *lname, const char *oname, const long salary, const char* visitDate);
};

  

Now we declare a procedure to add information to an intance of the class above. This simply requires the creation of a rew record then the assignment of a given value to each field. Note that many of the fields are derived from the given value of salary.

void dbPeople::Add(const char *lname, const char *oname, const long salary, const char* visitDate)
{
	newRecord();
	LastName = lname;
	Othernames = oname;
	Salary = salary;
	SalaryNoDup = salary;
	SalaryReal = salary;
	SalaryRealNoDup = salary;
	SalaryShort = salary;
	SalaryShortNoDup = salary;
	LastVisit = visitDate;
	LastVisitNoDup = visitDate;
	    

	ShortUnsigned = salary*5;  // numbers 10,000 to 49,999
	LongUnsigned = ULONG_MAX - salary;  
	    

	saveRecord();
}    

Here, we declare the test procedures to be defined later in this file.

Note that the first three procedures have the same name, this is overloading -> each procedure has a different number and/or configuration of parameters to tell which is the one required. Each of these 'testField' procedures tests a different configuration of the fields of the table.

void testField(dbPeople& searchTable, dbNumericField& searchBy);
void testField(dbPeople& searchTable, dbChar& searchBy, const char* searchAgainst, 
                   const char* searchUpto, const char* searchAgainstPartial);
void testField(dbPeople& searchTable, dbDateTime& searchBy, const char* searchAgainst, 
                   const char* searchUpto);  

This is the highest level procedure. It will call all of the other ones.

void doTest07(const char* existsName,const char* databaseName);  

Now we define each of the procedures.

void doTest07(const char* existsName,const char* databaseName)
{  

Here we declare the database and open it or create a new one.

	TEST_CONNECT    theDB;
	dbPeople     People;    

	if (dbConnect::fileExists(existsName)){
		theDB.openConnection(databaseName);
	}
	else {
		theDB.newConnection(databaseName);
		People.Add("Smith", "John", 2000, "23/11/94");
		People.Add("DENT", "Trissa", 9999, "22/11/94");
		People.Add("Denton", "Andy", 5000, "23-1-95");
		People.Add("Taylor", "Ken", 7500, "16,11,95");
	}  

Simple numeric field tests

Note the first few tests create temporary dbView's inline to output all records into a single object to pass to cout. We first saw this used in ooftst01.

First, we sort the database by the LongUnsigned field.

Testing: sort by long unsigned field.

 	People.selectAll();
	People.setSortOrder(People.LongUnsigned);
	    

We then print the database. Note that sorting by the field LongUnsigned has meant that the database is sorted by reversed salary. This is due to the way in which it was calculated (= ULONG_MAX - salary).

	cout << "Dumping database in key order by LongUnsigned (reverse of Salary)" 
            << endl << (dbView(People) << People.LastName << People.Othernames 
            << People.LongUnsigned) << endl << endl;    

Then we search the database for records having a certain value in the field ShortUnsigned. We do this using the function equal().

Please note that there are two ways of searching the database. They are both identical. The first is by using search() and specifying what you're searching for in the brackets. the other is demonstrated below, which basically says, "all the records in the database People that have a field of People.ShortUnsigned that equals 25000"

Testing: search by short unsigned field
Testing: use of function equal()

	cout << "Retrieving Andy Denton by ShortUnsigned == 25,000: " << endl;
	People[People.ShortUnsigned.equal(25000)];  

Then print this selection.

	cout << (dbView(People) << People.LastName << People.Othernames 
        << People.ShortUnsigned) << endl << endl;    

Now, we search for people who have a salary within a certain range. This shows the use of the function between().

Testing: search on long unsigned field
Testing: use of function between()

	cout << "Finding LongUnsigned between " << ULONG_MAX-5001 
            << " and " << ULONG_MAX << endl;
	People[People.LongUnsigned.between(ULONG_MAX-5001, ULONG_MAX)];
	cout << (dbView(People) << People.LastName << People.Othernames 
        << People.LongUnsigned) << endl << endl;
    

Other field tests

Here, we use the procedures declared later for character field tests.

These procedures test searches on the two character fields.

	testField(People, People.LastName, "Dent", "Denton", "DE");
	testField(People, People.Othernames, "Andy", "Kell", "J");    

These procedures further test searches on each of the different numeric fields.

Testing: search by numeric field testing duplicates vs no duplicates
Testing: search by numeric field testing short vs real

	testField(People, People.SalaryShort);
	testField(People, People.SalaryShortNoDup);
	testField(People, People.Salary);
	testField(People, People.SalaryNoDup);
	testField(People, People.SalaryReal);
	testField(People, People.SalaryRealNoDup);
	    

These procedures test date searches, comparing the two similar fields (their only difference being that one has been declared as having no duplicates.

Testing: search  by date field testing duplicates vs no duplicates

	testField(People, People.LastVisit, "23/11/94", "23-1-95");
	testField(People, People.LastVisitNoDup, "23/11/94", "22-1-95");
	    

	cout << "done" << endl;
}           

This procedure tests the numeric fields

void testField(dbPeople& p, dbNumericField& searchBy)
{	
	p.selectAll();    

Here, we declare a view to make it possible to dump just a few fields rather than the entire table. We add the name fields and the search field (so we know what search it is we are currently doing).

	dbView fewFields(p);
	fewFields << p.LastName << p.Othernames << searchBy;
	    

Here we sort the table by the field given for searching.

	p.setSortOrder(searchBy);
	cout << "Dumping database in key order by " << searchBy.fieldName() << endl 
	     << fewFields << endl << endl;  

Now we try various simple searches on the field given. I won't describe each one as the line with 'testing' should show what is being done. All the numeric fields should have very similar information as we have assigned the same value to each of them -> the only differences should be due to the actual properties of that field type.

Testing: search for specific record where <FieldValue> equals <GivenValue>

	cout << "Retrieving Andy Denton by " << searchBy.fieldName() 
            << " = 5000: " << endl;
	p[searchBy==5000];
	cout << fewFields << endl << endl;    

Testing: search for records where <FieldValue> is less than <GivenValue>

	cout << "Retrieving John Smith by " << searchBy.fieldName() 
            << " < 5000: " << endl;
	p[searchBy<5000];
	cout << fewFields << endl << endl;  

Testing: search for records where <FieldValue> is less than or equal to <GivenValue>

  	cout << "Retrieving Smith & Andy Denton by " << searchBy.fieldName() 
            << " <= 5000: " << endl;
	p[searchBy<=5000];
	cout << fewFields << endl << endl;  

Testing: search for records where <FieldValue> is greater than <GivenValue>

  	cout << "Retrieving Taylor & Trissa Dent by " << searchBy.fieldName() 
            << " > 5000: " << endl;
	p[searchBy>5000];
	cout << fewFields << endl << endl;   

Testing: search for records where <FieldValue> is greater than or equal to <GivenValue>

 	cout << "Same search with >=5000 (should also get Andy Denton): " << endl;
	p[searchBy>=5000];
	cout << fewFields << endl << endl;   

The next two tests show the use of the functions between() and outside(). These two functions search for records within or outside of the given range and are easier than having to specify the two values in a huge compound statement.

Testing: function between() on numeric field

	cout << "Finding " << searchBy.fieldName() 
            << " between 5000 and 7500 " << endl;
	p[searchBy.between(5000,7500)];
	cout << fewFields << endl << endl;    

Testing: function outside() on numeric field

	cout << "Finding " << searchBy.fieldName() 
            << " outside 5000 to 7500 " << endl;
	p[searchBy.outside(5000,7500)];
	cout << fewFields << endl << endl;    

	
	cout << "done" << endl;
}  

This procedure tests the character fields

void testField(dbPeople& p, dbChar& searchBy, const char* searchAgainst, 
                        const char* searchUpto, const char* searchAgainstPartial)
{	
	p.selectAll();    

Here, we declare a view to make it possible to dump just a few fields rather than the entire table. We add the name fields and the search field (so we know what search it is we are currently doing).

	dbView fewFields(p);
	fewFields << p.LastName << p.Othernames << searchBy;  

Here we sort the table by the field given for searching. Then print it out so we can see the intitial state (before this set of tests).

	p.setSortOrder(searchBy);
	cout << "Dumping database in key order by " << searchBy.fieldName() << endl 
	     << fewFields << endl << endl;  

Now we try various simple searches on the field given.

Our first test searches for any fields that begin with the given partial string.

Testing: function startsWith()

	cout << "Finding " << searchBy.fieldName() 
            << " starting with " << searchAgainstPartial << endl;
	p[searchBy.startsWith(searchAgainstPartial)];
	cout << fewFields << endl << endl;    

Next we test fields against a given string. These are simple tests of equality and comparison. I won't describe each as the 'testing:' should make it clear enough what is happening.

Note that for a string to be 'greater than' another string, it's characters must be further along in the alphabet. eg "b" comes after "a". Also, "antelope" comes after "aardvark" ie when the first part of the two strings are equal, the first character that differs is used to determine whether the string is greater or lesser than the other string.

Testing: search by character field testing <FieldValue> equals <GivenString>

	cout << "Finding " << searchBy.fieldName() << " == " 
            << searchAgainst << endl;
	p[searchBy==searchAgainst];
	cout << fewFields << endl << endl;    

Testing: search by character field testing <FieldValue> less than <GivenString>

	cout << "Finding " << searchBy.fieldName() << " < " 
            << searchAgainst << endl;
	p[searchBy<searchAgainst];
	cout << fewFields << endl << endl;    

Testing: search by character field testing <FieldValue> less than or equal to <GivenString>

	cout << "Finding " << searchBy.fieldName() << " <= " 
            << searchAgainst << endl;
	p[searchBy<=searchAgainst];
	cout << fewFields << endl << endl;    

Testing: search by character field testing <FieldValue> greater than <GivenString>

	cout << "Finding " << searchBy.fieldName() << " > " 
            << searchAgainst << endl;
	p[searchBy>searchAgainst];
	cout << fewFields << endl << endl;    

Testing: search by character field testing <FieldValue> greater than or equal to <GivenString>

	cout << "Finding " << searchBy.fieldName() << " >= " 
            << searchAgainst << endl;
	p[searchBy>=searchAgainst];
	cout << fewFields << endl << endl;    

These next two tests search for records with fields between two given strings or outside of them.

Testing: function between() on character field

	cout << "Finding " << searchBy.fieldName() << " between " 
		<< searchAgainst << " and " << searchUpto << endl;
	p[searchBy.between(searchAgainst,searchUpto)];
	cout << fewFields << endl << endl;    

Testing: function outside() on character field

	cout << "Finding " << searchBy.fieldName() << " outside " 
		<< searchAgainst << " to " << searchUpto << endl;
	p[searchBy.outside(searchAgainst,searchUpto)];
	cout << fewFields << endl << endl;    

}  

This procedure tests date fields

void testField(dbPeople& p, dbDateTime& searchBy, const char* searchAgainst, 
                        const char* searchUpto)
{	
	p.selectAll();  

Here, we declare a view to make it possible to dump just a few fields rather than the entire table. We add the name fields and the search field (so we know what search it is we are currently doing).

	dbView fewFields(p);
	fewFields << p.LastName << p.Othernames << searchBy;  

Here we sort the table by the field given for searching. Then print it out so we can see the intitial state (before this set of tests).

	p.setSortOrder(searchBy);
	cout << "Dumping database in key order by " << searchBy.fieldName() << endl 
	     << fewFields << endl << endl;  

Now we try various simple searches on the field given. I won't describe each one as the line with 'testing' should show what is being done.

Testing: search by date field testing <FieldValue> equals <GivenDate>

	cout << "Finding " << searchBy.fieldName() << " == " 
            << searchAgainst << endl;
	p[searchBy==searchAgainst];
	cout << fewFields << endl << endl;    

Testing: search by date field testing <FieldValue> less than <GivenDate>

	cout << "Finding " << searchBy.fieldName() << " < " 
            << searchAgainst << endl;
	p[searchBy<searchAgainst];
	cout << fewFields << endl << endl;    

Testing: search by date field testing <FieldValue> less than or equal to <GivenDate>

	cout << "Finding " << searchBy.fieldName() << " <= " 
            << searchAgainst << endl;
	p[searchBy<=searchAgainst];
	cout << fewFields << endl << endl;   

Testing: search by date field testing <FieldValue> greater than <GivenDate>

	cout << "Finding " << searchBy.fieldName() << " > " 
            << searchAgainst << endl;
	p[searchBy>searchAgainst];
	cout << fewFields << endl << endl;    

Testing: search by date field testing <FieldValue> greater than or equal to <GivenDate>

	cout << "Finding " << searchBy.fieldName() << " >= " 
            << searchAgainst << endl;
	p[searchBy>=searchAgainst];
	cout << fewFields << endl << endl;    

These next two tests search for records with fields between two given dates or outside of them.

Testing: function between() on date field

	cout << "Finding " << searchBy.fieldName() << " between " 
		<< searchAgainst << " and " << searchUpto << endl;
	p[searchBy.between(searchAgainst,searchUpto)];
	cout << fewFields << endl << endl;    

Testing: function outside() on date field

	cout << "Finding " << searchBy.fieldName() << " outside " 
		<< searchAgainst << " to " << searchUpto << endl;
	p[searchBy.outside(searchAgainst,searchUpto)];
	cout << fewFields << endl << endl;    

}  

Feature index

(c) Copyright A.D. Software 1994-2000 (All Rights Reserved).
Last Updated: 9th September 2001