OOFile Manual

OOFTST 2 - basic relationship between two tables

This sample tests the database backend by creating a pair of tables and creating a field relatioship between the two. It then stores and retrieves indexed data via traversal paths.

The include files are:

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

These are global variables that define the database using the classes in ooftst02.h

	TEST_CONNECT    theDB;
	dbPatients     Patients;
	dbVisits	Visits;

Declaring a relationship

Basic relationship information Declaring the classes Need to know more?

A relationship between tables means that there is some correspondance between records of the two tables. There can be:
- A one to one (1:1) relationship where each record in one table has a single corresponding record in the other.
- A many to many (N:M) relationship where each record in each table may have more or less than one corresponding record in the other table
- A one to many (1:N) relationship where each record in one table may have more or less than one corresponding record in the other table. The records in the other table may ony have one corresponding record from this table.

This example demonstrates a 1:N relationship. As you can see, there may be many visits for each patient but only one patient for each visit. You can see that the two tables are connected by fields in each table. The patients are linked to their visits through the field "visits". The visit is linked to its patient through the field "patient".

	dbRelationship PatientVisits(Patients.Visits, Visits.Patient);

int main()
{
	cout << "OOFILE Validation Suite - Test 2\n"
		 << "Simple test to store some data and retrieve it" << endl
		 << "using a relation joining over a field and showing" << endl
		 << "iterators on related tables and 1-many relations" << endl << endl;

The following is a bit of complicated filename logic to support different backends with this one test program. This code determines what platform we are running under and assigns a name for the database file accordingly.

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

	#else
		const char* kDatabaseName = "ooftst02.db";
		const char* kExistsName = kDatabaseName;
	#endif

Opening the database

This segment shows us how to open or create the database whose name was given in the code above.

If the file already exists we use openConnection() to open the file and use deleteAll() to delete everything so that it is empty. If it does not exist, we create the file using newConnection(). We then use the function AddTestData() to put some data into our tables.

	if (dbConnect::fileExists(kExistsName)) {
		theDB.openConnection(kDatabaseName);
	}
	else {
		theDB.newConnection(kDatabaseName);
		Patients.AddTestData();
	}

Sorting the database

Basic information Need to know more?

Here, we sort one of the tables and print the database by passing it whole to cout. Note that we are passing it using the name "theDB" where in ooftst01.cpp, we merely passed the table "People". This is not appropriate here as we have more than one table and wish to print both. Note also, however, that we have only sorted one of the two tables we are going to print.

Testing: Print of multi-table database using cout

	Patients.setSortOrder(Patients.LastName);
	cout << theDB;

Iteration over database

The code above prints all of one table and then all of the other wheras the code below prints each patient followed by their visits. Iterating through each record, and printing their fields individually, gives us direct control of what fields are printed and in what order. In this case, allowing us to see which visits are related to which patients.

The function start() brings us to the start of the list of records.

Testing: function start() on one table of multi-table database

	Patients.start();

The function more() checks to see if there are any more records in "patients" to print. If so, we print the patient's number and name.

Testing: function more() on one table of multitable database

	while (Patients.more()) { 
		cout << Patients.PatientNo << '\t' 
			 << Patients.LastName << endl;

Here we check if there are any visits to print by counting the number of records there are in the relationship's field. If patients.visits->count() equals zero, there must be no "visits" records associated with this patient's record.

Testing: use of count() through a relationship

		if (Patients.Visits->count()==0) 
			cout << "no visits" << endl;

If there are records related to this patient, we can access each of them by calling them through the patient record's "Visits" field. This is little different from accessing members in a class through pointers.

Testing: while loop iteration through a relationship
Testing:accessing fields through the relationship

		else {
			Patients.Visits->start(); 
			while (Patients.Visits->more()) {
				cout << '\t' << Patients.Visits->VisitDate << '\t'
			      	<< Patients.Visits->Why << endl;
				Patients.Visits->next();
			}
		}
		cout << endl;

Here we access the next record in the list using next().

		Patients.next();
	}

The code below repeats the above process but uses a dbView to organise which fields are to be printed.

	cout << endl << "Now repeating the process using a dbView instead of explicitly" 
	     << endl << "iterating over the related file." << endl;

First, we declare the view then add in the fields we are interested in.

Testing: Creating a dbView from fields in related tables

	dbView relatedVisits(Patients.Visits);
	relatedVisits << Patients.Visits->VisitDate << Patients.Visits->Why;

We initialise to the first record using start().

	Patients.start();

We test if there are any records left using more(), then print out some patient data.

	while (Patients.more()) {
		cout << Patients.PatientNo << '\t' 
			 << Patients.LastName << endl;

Here, we test if there are any related records, but using a different function. The function call source()->isEmpty() does two things at once. The source() function returns the table belonging to the records in the current selection. Then, isEmpty() tests to see if this contains any objects. It is functionally equivalent to checking the number of records in the table, testing if this is zero (as used above).

If there are records in there, we merely pass the dbView to cout then move on to the next record by using next().

Testing: function source()
Testing: function isEmpty()

		if (relatedVisits.source()->isEmpty())
			cout << "no visits" << endl;
		else 
			cout << relatedVisits << endl;
		cout << endl;
		Patients.next();
	}

Searches

Basic information Earlier search tests Need to know more?

The search() command creates a sub-set (or selection) of the table you pass it. The records in this selection consist of all the records that contain the values you have specified of the field given in your search declaration. The example below consists of a search on the "Patients" table. We are searching the field "PatientNo" and looking for values of 3.

Testing: equality search() on numeric field

	Patients.search(Patients.PatientNo==3);
	cout << "Finding Patient No 3 : " << Patients << endl;

We now test through the relationship. We create a subset of the table "patients" but we are searching for all patients who have a corresponding record in the "Visits" table that have a value of "Flu" in the field "Why".

Testing: equality search() on character field *through* the relationship.

	cout << "Now finding the Flu sufferers: " << endl;
	Patients.search(Patients.Visits->Why=="Flu");
	cout << Patients << endl << endl;

Now, we sort the "Patients" table and then print out the whole database to see what the records look like now.

Testing: sorting single table in multi-table database
Testing: print of multi-table database with ony one table sorted

	Patients.setSortOrder(Patients.Othernames);
	cout << "Now dumping the entire database, with patients sorted by Othernames: " << endl << theDB;

Desribing a database's structure

The function describe() allows us to see a description of the structure of the database. It lists each table followed by the information of each field of each table. Such information includes name, size, and any indices on the field.

Testing: use of describe() on multi-table database

	cout << "Description of database schema: " << endl;
	theDB.describe(cout);

	cout << "Test Completed" << endl;

	return EXIT_SUCCESS;
}

Feature index