The Main Routine

SourcePro DB : DB Interface Module User’s Guide : PART V The Tutorials : A Complicated Query : The Program : The Main Routine

The Main Routine

The following example is the main routine for the tutorial. Remember that you cannot run this tutorial unless your compiler supports templates. Not all of the source code is listed here, as the TopTenVideo class is also defined within this file. The line numbers correspond to the comments that follow the code.

#include <rw/db/db.h> //1

#include "tututil.h" //2

#include "rentrep.h" //3

#include "vidrep.h" //4

#include <rw/tpordvec.h> //5

#include <rw/db/tpmemtab.h> //6

int main(int argc, char** argv) //7

{

RWDBManager::setErrorHandler(outputStatus); //8

RWCString serverType, serverName, userName,

password, databaseName, pstring; //9

initializeDatabaseArguments(argc, argv, serverType,

serverName, userName, password,

databaseName, pstring); //10

associateStreams("", "t6out.txt", "t6err.txt") //11

RWDBDatabase aDB = RWDBManager::database

(serverType, serverName, userName, password,

databaseName, pstring); //12

VVVideoRepository videos(aDB, videoTableName); //13

VVRentalTransactionRepository rentals(aDB, rentalTableName); //14

RWDateTime beginDate(1, 1, 1994); //15

RWDateTime endDate(31, 1, 1994); //16

RWDBSelector aSelector = aDB.selector(); //17

aSelector << videos.titleColumn() << rwdbCount()

<< rentals.videoIDColumn() << videos.idColumn(); //18

aSelector.where

(rentals.dueDateColumn().between

(beginDate, endDate)); //19

aSelector.groupBy(rentals.videoIDColumn()).groupBy(

videos.idColumn()).groupBy(videos.titleColumn()); //20

aSelector.having

(rentals.videoIDColumn() == videos.idColumn()); //21

aSelector.orderByDescending(2); //22

aSelector.orderBy(1); //23

RWDBTPtrMemTable<TopTenVideo,

RWTPtrOrderedVector<TopTenVideo> >

theTopTenRenters(aSelector, 10); //24

outStream << "TOP TEN Videos for "

<< beginDate.monthName() << endl << endl; //25

for (size_t i = 0; i < theTopTenRenters.entries(); i++) { //26

outStream << "#" << i+1 << ‘\t’

<< theTopTenRenters[i]->name() << endl; //27

}

theTopTenRenters.clearAndDestroy(); //28

closeStreams("", "t6out.txt", "t6err.txt");

return 0;

} //29

//1-4 Include the declarations for the DB Interface Module classes and the tutorial classes used in this program.

//5 Later in this code, the Essential Tools Module template-based collection RWTPtrOrderedVector is used. This include file brings in the definitions of that template class.

//6 Include the declarations for template-based memory table class RWDBTPtrMemTable of the DB Interface Module.

//7-11 These lines are for initialization. They are common to all the tutorials and are explained in the comments in The Main Routine.

//12 Here a connection to a database server is established. The variable aDB serves as a handle to the database defined by arguments to the RWDBManager::database function.

//13 An instance of the class VVVideoRepository that represents the videos table is created on this line. The first argument aDB identifies the database in which the instance’s data resides. The second argument identifies the specific table name that holds the video information.

//14 An instance of the class VVRentalTransactionRepository is created on this line representing the rentals table. The first argument, aDB, identifies the database in which the instance’s data resides. The second argument identifies the specific table name that holds the rental transaction information.

//15 This program finds the top ten popular videos for a certain time period. On this line the beginning of the time period is defined with an instance of RWDateTime initialized to January 1, 1994.

//16 The end of the time period is defined here as January 31, 1994.

//17 An RWDBSelector instance is defined here. This selector produces the query that fetches the top ten videos. It creates an SQL string equivalent to:

SELECT videos.title, COUNT(*), rentals.videoID, videos.ID

FROM videos, rentals

WHERE rentals.dueDate BETWEEN '1994/1/1' AND '1994/1/31'

GROUP BY rentals.videoID, videos.ID, videos.title

HAVING rentals.videoID = videos.ID

ORDER BY 2 DESC, 1 ASC

//18 The query selects the title column from the videos table, the SQL function COUNT(*) counting the number of entries for each video, the videoID column from the rentals table, and the ID column from the videos table. The call to the function rwdbCount() is the DB Interface Module technique of encapsulating functions in an SQL statement. The RWDBExpr instance returned by rwdbCount() translates into the appropriate dialect of the COUNT function for the database that produced the selector.

//19 A condition in the form of an RWDBCriterion is formed here. It limits the query to rows from the rentals table whose dueDate field falls between two dates.

//20 A GROUP BY clause is added on this line to group all of the rentals of each video together. This enables the COUNT of //18 to count members in each group. The ID and title columns of the videos table are also included in the GROUP BY list to ensure compatibility with servers that require elements of the SELECT clause and HAVING clause to also appear in the GROUP BY list.

//21 Here the HAVING clause is appended using a condition in the form of an RWDBCriterion. This criterion states that the rental table’s videoID column must match the ID column of the videos table.

//22-23 Finally, the query is to be sorted by the COUNT of video rentals in descending order (column number 2 of the select list) and video name in ascending order (column number 1 of the select list). These guarantee the same order across all databases.

//24 Now that the selector has been created, it’s time to fetch all the data into the program’s memory. On this line an instance of RWDBTPtrMemTable called theTopTenRenters is created. It is an instance of a template class based on RWTPtrOrderedVector and the class TopTenVideo. This means that this instance of RWDBTPtrMemTable is an ordered vector of TopTenVideo instances.

The selector is passed in as a parameter to the RWDBTPtrMemTable constructor. Internally, the constructor produces a reader from the selector, which submits the query to the database. Since the TopTenVideo class matches the results fetched by the reader, the RWDBTPtrMemTable constructor can use the reader to populate itself with instances of the class TopTenVideo.

The second parameter that was passed to the constructor, 10, is a limit on the size of the table in memory. A maximum of ten records are read into the memory table. The final result of this line is an instance of RWDBTPtrMemTable, implemented as an RWTPtrOrderedVector and fully populated with topTenVideo instances representing the ten most popular videos in the store. See the documentation of RWTPtrOrderedVector for more information.

//25 Print a header for the top ten list to the output stream.

//26 Since the top ten videos are in memory in a collection that can be accessed like an array, a simple loop is sufficient to access the information. The call to the entries() function returns the number of TopTenVideo instances in the collection. Because the tables set up by tutinit always have ten videos, there will always be ten entries.

//27 On this line, the title from each instance of TopTenVideo is sent to the output stream. Note that the instance of RWDBTPtrMemTable can be accessed like an array by using the square bracket operator, operator[](). For RWDBTPtrMemTable, this operator returns a pointer to a TopTenVideo instance, therefore the arrow operator -> is used to access the member function that returns the video title.

//28 Since instances of TopTenVideo in the RWDBTPtrMemTable instance were created on the heap, they must be destroyed manually. The clearAndDestroy() member function of RWTPtrOrderedVector takes care of that task. For more information, see the Essential Tools Module documentation on pointer-based template collections.

//29 Destructors for all the objects are called here. The database closes automatically when its destructor is called.