This program writes a list of the most often rented videos between January 1, 1994 and January 31, 1994 inclusive. The file called t6out.txt contains the list. This section looks at the code for t6 in detail.
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
RWDBMAIN (MAIN_ARGS_DECL) //7
{
RWDBManager::setErrorHandler (outputStatus); //8
RWCString serverType, serverName, userName,
password, databaseName, role; //9
initializeDatabaseArguments (MAIN_ARGS, serverType,
serverName, userName,
password, databaseName,
role); //10
associateStreams ("", "t6out.txt", "t6err.txt") //11
RWDBDatabase aDB = RWDBManager::database
(serverType, serverName, userName, password,
databaseName, role); //12
VVVideoRepository videos(aDB, videoTableName); //13
VVRentalTransactionRepository rentals(aDB, rentalTableName); //14
RWDBDateTime beginDate (1994, 1, 1, 0, 0, 0); //15
RWDBDateTime endDate (1994, 1, 31, 0, 0, 0); //16
RWDBSelector aSelector = aDB.selector(); //17
aSelector << videos.titleColumn() << rwdbCount(); //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 (int i = 0; i < theTopTenRenters.entries(); i++) { //26
outStream << "#" << i+1 << `\t'
<< theTopTenRenters[i]->name()
<< endl; //27
}
theTopTenRenters.clearAndDestroy(); //28
return 0;
} //29
| //1-4 | Include the declarations for the DBTools.h++ classes used in this program. |
| //5 | Later in this code, the Tools.h++ template-based collection RWTPtrOrderedVector is used. This include file brings in the definitions of that template. |
| //6 | Include the declarations for the DBTools.h++ class RWDBTPtrMemTable. |
| //7-11 | These lines are for initialization and multiplatform portability. They are common to all the tutorials and are explained in Section 15.5.1. |
| //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 reside. The second argument identifies the specific table name that holds the customer information. |
| //15 | This program gives 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 RWDBDateTime 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)
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.
|
| //18 | The query selects the title column from the videos table and the count of the video ID column from the rentals table. The call to the function rwdbCount() is DBTools.h++ 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 spawned the selector. |
| //19 | A predicate 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 //19 to count members in each group. The video table's ID column and title column 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 predicate in the form of an RWDBCriterion. This criterion states that the rental table's video ID column must match the video table's ID column. |
| //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 fetches 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 in the Tools.h++ Class Reference 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 actually in the collection. As the tables are set up by tutsetup, there will always be ten videos. However, if the video stock is for some reason reduced to fewer than ten videos, or if fewer than ten videos were rented during a time period, the RWDBTPtrMemTable may have fewer than 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 Tools.h++ documentation on pointer-based template collections. |
| //29 | Destructors for all the objects are called here. The database closes automatically along with the output and input streams. |
©Copyright 2000, Rogue Wave Software, Inc.
Contact Rogue Wave about documentation or support issues.