OOFile Manual

OOFTST 12 - compound indices

This sample tests the compound indexes by creating a single table and storing and retrieving indexed data.

#include "oofile.h"	// the general oofile library

First, we declare the class for our table.

CLASS_TABLE(dbPeople)
	dbChar		LastName, OtherNames, DepartmentCode, ProjectCode;
	dbLong		Salary;
	dbCompoundField	CombinedNames, NameAndSalary, JobCode;
	dbUshort	StartedYear;

	dbPeople() :
				dbTable("People"),
				LastName(39, "Last Name"),
				OtherNames(79, "Other Names", kIndexCompress),
				DepartmentCode(3, "Dept. Code"), 
				ProjectCode(4, "Proj. Code"),
				Salary("Salary"),
				CombinedNames("Combined Names"),
				NameAndSalary("Name & Salary"),
				JobCode("Job Code"),
				StartedYear("Started Year")

These are the fields containing the compound indices. Notice how we pass the fields in just like a dbView.

We declare 3 different types of indices to test the variations.

Note: The order of these two lines is not significant

	CombinedNames << LastName << OtherNames;
	CombinedNames.index(kIndexNoDups);

	NameAndSalary << LastName << Salary;
	NameAndSalary.index(kIndexCompress);

	JobCode << DepartmentCode << ProjectCode << StartedYear;
	JobCode.index(kIndexed);
};

	void Add(const char *lname, const char *oname, const long salary, const char *dept, const char *proj, const unsigned short year);
};

This is the data entry procedure to add a single record. Each field is simply assigned the parameter given for it.

void dbPeople::Add(const char *lname, const char *oname, const long salary, const char *dept, const char *proj, const unsigned short year)
{
	newRecord();
	LastName = lname;
	OtherNames = oname;
	Salary = salary;
	DepartmentCode = dept;
	ProjectCode = proj;
	StartedYear = year;
	saveRecord();
}

int main()
{
	cout << "OOFILE Validation Suite - Test 12\n"
		 << "Simple test to build compound indexes\n"
		 << "and demonstrate sorting and searching by them and their components\n";

Here we declare our database and table.

	dbConnect_ctree    theDB;
	dbPeople     People;

We check if the file we need exists. if it does, we open it, if not, we create it and add some data.

	if (dbConnect::fileExists("ooftst12.db"))
		theDB.openConnection("ooftst12.db");
	else {
		theDB.newConnection("ooftst12.db");
		People.Add("Smith", "John", 0, "Acc", "XXX", 1970);  // specifically to test zero searches
		People.Add("DENT", "Trissa", 5000, "Acc", "Rec", 1985);
		People.Add("Dent", "Andy", 25000, "Acc", "XXX", 1992);
		People.Add("Dent", "Andrew", 40000, "MIS", "Boss", 1982);  // should sort before Andy on CombinedNames but after on Salary
		People.Add("Denton", "Andrew", 39000, "Pur", "XXX", 1994);  
		People.Add("Taylor", "Ken", 75000, "MIS", "Cntr", 1994);
	}

Here, we sort the database by the first compound index field.

Testing: searching by compound index field

	People.selectAll();
	People.setSortOrder(People.CombinedNames);
	cout << "Listing records by combined names\n" << People << endl;

Then we try sorting by our combined numeric and character field.

Testing: searching by compound index field

	People.setSortOrder(People.NameAndSalary);
	cout << "Listing records by Name and reverse Salary\n" << People << endl;

Then we try sorting by an ordinary character field.

Testing: searching by character field

	People.setSortOrder(People.OtherNames);
	cout << "Listing records in OtherNames order\n" << People << endl;

We do a simple search for all people with lastName of Dent.

Testing: search on single non-indexed field

	People.search(People.LastName=="Dent");  
	cout << "Listing three Dent records: " << endl << People << endl;

Then test use of a compound index in a direct query, using the function startsWith() and passing it a partial string.

Testing: search on compound index using query startsWith()

	People.search(People.JobCode.startsWith("AccXXX"));  
	cout << "Listing two AccXXX records: " << endl << People << endl;

We try a combinatorial search that should be identical to a search using the compound index consisting of that combination of fields.

Testing: combinatorial search on two non-indexed fields

	People[People.LastName=="Dent" && People.Salary==25000];
	cout << "Listing Andy Dent by compound key search of LastName & Salary: " << endl << People << endl;

Testing: combinatorial search on an indexed and a nonindexed field

	People[People.LastName=="Dent" && People.OtherNames=="Andrew"];
	cout << "Listing Andrew Dent by compound key search on both names: " << endl << People << endl;

This is a combinatorial search, using the OR operator '|' and the AND operator '&&'. Note that for OOFILE, it doesn't matter if we use one or two &'s or |'s.

Testing: combinatorial search using '|'
Testing: combinatorial search using '&&'

	People[ (People.LastName=="Dent" && People.Salary==25000) 
			| People.OtherNames=="Andrew"
	];

We then print the search selection to see if it is what we expected.

	cout << "Listing Andy Dent & Denton: " << endl << People << endl;

We are now going to test copying from a read-only compound field.

	cout << "Test Getting copy from read-only compound field" << endl;
	People.start();

Here we copy the field from a single record. Note that we use the function copyAsChars() to convert our numeric-only compund field into a string.

	char* str = People.JobCode.copyAsChars();
	cout << "JobCode is: " << str << endl;
	delete[] str;

Now, we copy the entire database.

	dbPeople People2(People);

This or a search is ***essential*** after cloning a selection:

	People2.start();

Now, we repeat the test above.

	cout << endl << "Repeat above read-only compound field test on cloned selection" << endl;
	cout << "JobCode is: " << People2.JobCode << endl;

	cout << endl << "Test Completed" << endl;

	return EXIT_SUCCESS;
}

Feature index