Database Template Library

Environment: Visual Studio C++ 6.0 under NT/Win 95/Win 98, gcc 2.95 under Red Hat Linux 7

Abstract
Background
A First Example, Reading and Writing Records in a Table
A Second Example, Parameterized Queries
Tables R Us, The IndexedDBView
When you don't know what you need, dynamic queries
Using STL Algorithms, the Table Difference Function
Conclusion

Abstract

The goal of this library is to make ODBC recordsets look just like an STL container. As a user, you can move through our containers using standard STL iterators; and if you insert(), erase() or replace() records in our containers changes can be automatically committed to the database for you. The library's compliance with the STL iterator and container standards means you can plug our abstractions into a wide variety of STL algorithms for data storage, searching and manipulation. In addition, the C++ reflection mechanism used by our library to bind to database tables allows us to add generic indexing and lookup properties to our containers with no special code required from the end-user. Because our code takes full advantage of the template mechanism, it adds minimal overhead compared with using raw ODBC calls to access a database.


Background

Introduced in 1990, STL and templates represent one of the most significant advances in the C++ language in the last decade. The guiding force behind the power of the standard template library is the notion of Generic Programming. At the heart of Generic Programming is the idea of abstracting operations across as broad a set of data types as possible to create algorithms that are as generic as possible. This kind of design leads to abstractions that are centered around a set of requirements on the data types themselves. Examples in STL include notions such as iterators, containers and set operations. We have taken these abstractions and applied them to the problem of representing tables in a database. In what follows, we will show how this simplifies the task of manipulating data and provides instant access to a broad range of algorithms that come with the standard template library.

A First Example, Reading and Writing Records in a Table

As our first example, we show what the code would look like to open a table and read a set of rows from a database.

Accessing a Table in Four Easy Steps:

  1. Define an object to hold the rows from your query.
  2. Define an association between fields in your query and fields in your object. This is what we call a 'BCA', which is short for Bind Column Addresses. In the example below, this is done via the functor "BCAExample". The job of the BCA is to equate SQL fields with object fields via the '==' operator which will then establish ODBC bindings to move data to or from a user query.
  3. Create a view to select records from. This view is built from the template DBView and establishes which table(s) you want to access, what fields you want to look at (via the BCA), and an optional where clause to further limit the set of records that you are working with. The DBView template forms a semi-Container in the STL sense. 1.
  4. Use the DBView container to obtain an iterator to SELECT, INSERT, UPDATE or DELETE records from your view. These iterators may be used to either populate STL containers or apply algorithms from the Standard Template library.

    In all the examples that follow we will assume that our database contains a table called DB_EXAMPLE of the form

    SQL> desc db_example;
    Name                            Type
    ------------------------------- --------
    INT_VALUE                       INTEGER
    STRING_VALUE                    VARCHAR
    DOUBLE_VALUE                    FLOAT
    EXAMPLE_LONG                    INTEGER
    EXAMPLE_DATE                    DATE
    
    // STEP 1 ////
    // "Example" class to hold rows from our database table
    class Example
    {
    public:                        // tablename.columnname:
     int exampleInt;               // DB_EXAMPLE.INT_VALUE
     string exampleStr;            // DB_EXAMPLE.STRING_VALUE
     double exampleDouble;         // DB_EXAMPLE.DOUBLE_VALUE
     long exampleLong;             // DB_EXAMPLE.EXAMPLE_LONG
     TIMESTAMP_STRUCT exampleDate; // DB_EXAMPLE.EXAMPLE_DATE
    
     Example(int exInt, 
             const string &exStr, 
             double exDouble, 
             long exLong,
             const TIMESTAMP_STRUCT &exDate) :
      exampleInt(exInt), 
      exampleStr(exStr), 
      exampleDouble(exDouble), 
      exampleLong(exLong),
      exampleDate(exDate)
     { }
    };
    
    // STEP 2 ////
    // Create an association between table columns and fields 
    // in our object
    template<> class dtl::DefaultBCA<Example>
    {
    public:
     void operator()(BoundIOs &cols, Example &rowbuf)
     {
      cols["INT_VALUE"] == rowbuf.exampleInt;
      cols["STRING_VALUE"] == rowbuf.exampleStr;
      cols["DOUBLE_VALUE"] == rowbuf.exampleDouble;
      cols["EXAMPLE_LONG"] == rowbuf.exampleLong;
      cols["EXAMPLE_DATE"] == rowbuf.exampleDate;
     }
    }
    
    // STEP 3 & 4
    // Read the contents of the DB_EXAMPLE table and return a vector of the
    // resulting rows<
    vector<Example> ReadData() {
     // Read the data
     vector<Example> results;
     DBView<Example> view("DB_EXAMPLE");  
    
     DBView<Example>::select_iterator read_it = view.begin();
     for ( ; read_it != view.end();  read_it++)
     {
      results.push_back(*read_it);
     }
    
     return results;
    }
    
1 See http://www.sgi.com/tech/stl/Container.html for the definition of an STL container, we call DBView a semi container because it supports all standard container methods except size(), max_size() and empty(). We explain why these were left out by design in the documentation for the DBView template.

At this point, it is worth discussing the types of iterators exposed by DBView. The iterators that DBView provides are either Input iterators or Output iterators. In simple terms, an Input iterator can read elements, but not write them. An Output iterator can write elements, but not read them. These notions were first envisaged for working with C++ input and output streams but they apply equally well to reading and writing table data. Input and Output iterators are also minimal types of iterators in that they don't guarantee that table records will be read in any kind of specific or consistent order and they don't provide for random access in the sense that users cannot ask them to 'skip' ahead a given number of records or go to a particular record number in the table. An exact description of the functionality provided by Input and Output iterators may be found at http://www.sgi.com/tech/stl/InputIterator.html and http://www.sgi.com/tech/stl/OutputIterator.html.

By restricting the iterators from DBView to be either input or output iterators, we are able to provide database access with a minimum amount of code overhead; thereby ensuring that read and write operations remain efficient as compared with raw ODBC calls. The iterators provided by DB_View are as follows:

Input Iterators:
select_iterator

Output Iterators:
insert_iterator
update_iterator
delete_iterator

To illustrate the use of an output iterator we show how a vector of rows would be inserted into a table.

// Using a DBView to insert rows into a database

// ... Class definitions for Example and BCAExample .....
// ... as per our ReadData example .....

// Specialization of DefaultInsValidate for Example
// This defines a business rule we wish to enforce for all 
// Example objects before they are allowed to be inserted 
// into the database
template<> class dtl::DefaultInsValidate<Example> 
{
public:
 bool operator()(Example &rowbuf) {	
 // data is valid if rowbuf.exampleStr is nonempty and
 // rowbuf.exampleDouble is 
 // between 0 and 100 (like a percentage)
 return (rowbuf.exampleStr.length() > 0 
  &&  rowbuf.exampleDouble >= 0.0 
  && rowbuf.exampleLong  <= 100.0);
 }
};


// Insert rows from the vector<Example> parameter into the database
void WriteData(const vector<Example> &examples)
{
 DBView<Example> view("DB_EXAMPLE");

 // loop through vector and write Example objects to DB
 // write_it.GetCount() records written in loop

 DBView<Example>::insert_iterator write_it = view;

 for (vector<Example>::const_iterator ex_it = examples.begin(); 
      ex_it != examples.end(); ex_it++, 
      write_it++)
 {
  *write_it = *ex_it;
  cout << "Writing element #" 
       << write_it.GetCount() + 1 
       << endl;
 }
}

In WriteData() we have used an output iterator to insert records into our table in much the same way that we used a read iterator to read records from a table. In addition, this example introduces notion of client-side validation. Often, when reading or writing records from a table we want to do client side validation to make sure that the fields in a record are not null or lie within an acceptable range of values. DBView supports this through SelValidate and InsValidate functions. The SelValidate function validates records as they are selected from the database. The InsValidate function validates records as they are inserted into the database. In the example above, we define a DefaultInsValidate function which validates records before insertion to make sure the exampleStr, exampleDouble and exampleLong fields contain acceptable values before allowing them to be inserted into the database.

In general, the constructor for DBView<class DataObj, class ParamObj = DefaultParamObj<DataObj>> takes the form

DBView(const string &tableList, const BCA &bca_functor = DefaultBCA<DataObj>(),
const string &postfix = "", const BPA &bpa_functor = DefaultBPA<ParamObj>(),
const SelVal sel_val = DefaultSelValidate<DataObj>(),
const InsVal ins_val = DefaultInsValidate<DataObj>(),
DBConnection &connection = DBConnection::GetDefaultConnection())
which allows the user to define table names, field names, a where clause, query parameters, a selection validation function, an insert validation function and a database connection to use when processing queries. If the user does not supply a validation function then the default functions named DefaultSelValidate and DefaultInsValidate will be called. To see how the postfix clause and parameters work we will next examine a more complex case.

A Second Example, Parameterized Queries

We now turn to a more general class of queries; the case where we may be joining across multiple tables and/or have join conditions that restrict the set of records to be retrieved.
// Using dynamic parameters to join two tables
// For purposes of illustration we introduce a table called DB_SAMPLE 

SQL> desc db_sample;
Name				Type
------------------------------- -------- 
SAMPLE_LONG			LONG INTEGER
SAMPLE_INT			INTEGER
SAMPLE_STR			STRING
EXTRA_FLOAT			FLOAT

class JoinExample
{
private:
                            //tablename.columnname:
 int exampleInt;            //DB_EXAMPLE.INT_VALUE
 string exampleStr;         //DB_EXAMPLE.STRING_VALUE
 double exampleDouble;      //DB_EXAMPLE.DOUBLE_VALUE
 unsigned long sampleLong;  //DB_SAMPLE.SAMPLE_LONG
 double extraDouble;        //DB_SAMPLE.EXTRA_FLOAT

 friend class BCAJoinExample;
 friend class BPAJoinParamObj;
};

// Here we define a custom parameter object for use with our JoinExample 
class JoinParamObj
{
public:
 int intValue;
 string strValue;
 int sampleInt;
 string sampleStr;
};

// BCA for JoinExample ... needed to store bindings between
// query fields and members in JoinExample objects
class BCAJoinExample
{
public:
 void operator()(BoundIOs &cols, JoinExample &row)
 {
  cols["INT_VALUE"] == row.exampleInt;
  cols["STRING_VALUE"] == row.exampleStr;
  cols["DOUBLE_VALUE"] == row.exampleDouble;
  cols["SAMPLE_LONG"] == row.sampleLong;
  cols["EXTRA_FLOAT"] ==row.extraDouble;
 }
};

// BPA for JoinParamObj ... set SQL Query parameters from object
class BPAJoinParamObj
{
public:
 void operator()(BoundIOs &boundIOs, JoinParamObj &paramObj)
 {
  params[0] == paramObj.intValue;
  params[1] == paramObj.strValue;
  params[2] == paramObj.sampleInt;
  params[3] == paramObj.sampleStr;
 }
};


// Read JoinExample objects from the database using a query that
// joins the DB_EXAMPLE and DB_SAMPLE tables
vector<JoinExample> ReadJoinedData()
{
 vector<JoinExample> results;

 // construct view
 // note here that we use a custom parameter class for JoinExample
 // rather than DefaultParamObj<JoinExample>

 DBView<JoinExample, JoinParamObj>
 view("DB_EXAMPLE, DB_SAMPLE",	BCAJoinExample(),
 "WHERE (INT_VALUE = (?) AND STRING_VALUE = (?)) AND "
 "(SAMPLE_INT = (?) OR SAMPLE_STR = (?)) "
 "ORDER BY SAMPLE_LONG", BPAJoinParamObj());

 // loop through query results and add them to our vector
 DBView<JoinExample, JoinParamObj>::select_iterator read_it = view.begin();

 // assign paramteter values as represented by the (?) placeholders
 // in the where clause for our view
 read_it.Params().intValue = 3;
 read_it.Params().strValue = "Join Example";
 read_it.Params().sampleInt = 1;
 read_it.Params().sampleStr = "Joined Tables";

 for ( ; read_it != view.end(); read_it++)
 { 
  results.push_back(*read_it);
 }

 return results;
}

This works in exactly the same way as the select iterator shown previously. The only new elements here are that instead of a single table name we provide a list of tables, we set a where clause, and we bind parameters to fill in values for the clause. To bind parameters we first create what we call a BPA, or Bind Parameter Addresses, functor. A BPA functor establishes a correspondence between parameters that are identified in a postfix clause by "(?)" and fields in a parameter object. If you examine the function BPAJoinParamObj you will notice that unlike our BCA functor the parameter fields are bound by number. This is partly because parameter fields do not have distinct names the way that table fields do, and it is partly due to the fact that using a number here allows the binding operator to distinguish between binding output columns and input parameters. Observant readers will also note that our postfix clause contains instructions to sort the retrieved objects in a particular manner ( "ORDER BY SAMPLE_LONG" ). In fact, the postfix clause need not contain a WHERE command at all. In practical applications this might be simply a sorting statement or a GROUP BY clause, and our 'field' names in the BCA functor may be SQL functions like "SUM(INT_VALUE)" instead of simple column names. The BCA and BPA are specified as function objects, i.e. functors.

Tables R Us, The IndexedDBView

In practice, the most common operations performed on a set of table records are: read the records into a container, search the records by different key fields (i.e. indexes), and delete, insert or update records in the container. For this reason, we have developed a more advanced container for holding database tables. This IndexedDBView container is a specialization of a Unique Associative Container as defined by the standard template library http://www.sgi.com/tech/stl/UniqueAssociativeContainer.html.

In addition to the base methods defined by the STL standard we have coded features to make the container more copesetic with the underlying rows that it contains. The main new features are the easy creation of indexes into rows and synchronization capabilities that can automatically propagate any changes back to the database. This container comes at a price. It incurs more overhead than the simple DBView and because it works at a higher level you lose a bit of the fine-grained control that you get with simple iterators. To explain, we begin with an example:

// "Example" class to hold rows from our database table
class Example
{
public:                        // tablename.columnname:
 int exampleInt;               // DB_EXAMPLE.INT_VALUE
 string exampleStr;            // DB_EXAMPLE.STRING_VALUE
 double exampleDouble;         // DB_EXAMPLE.DOUBLE_VALUE
 long exampleLong;             // DB_EXAMPLE.EXAMPLE_LONG
 TIMESTAMP_STRUCT exampleDate; // DB_EXAMPLE.EXAMPLE_DATE

 Example(int exInt, 
         const string &exStr, 
         double exDouble, 
         long exLong, 
         const TIMESTAMP_STRUCT &exDate) :
 exampleInt(exInt), exampleStr(exStr), exampleDouble(exDouble), exampleLong(exLong),
 exampleDate(exDate)
 { }
};

// Parameter object to hold parameters for dynamic SQL query below 
class ParamObjExample
{
public:
 int lowIntValue;
 int highIntValue;
 string strValue;
 TIMESTAMP_STRUCT dateValue;
};

// Create an association between table columns and fields 
// in our object
class BCAExampleObj
{
public:
 void operator()(BoundIOs &boundIOs, Example &rowbuf)
 {
  boundIOs["INT_VALUE"]	== rowbuf.exampleInt;
  boundIOs["STRING_VALUE"]	== rowbuf.exampleStr;
  boundIOs["DOUBLE_VALUE"]	== rowbuf.exampleDouble;
  boundIOs["EXAMPLE_LONG"]	== rowbuf.exampleLong;
  boundIOs["EXAMPLE_DATE"]	== rowbuf.exampleDate;
 }
};

// Create an association between query parameters and fields in 
// our parameters object
class BPAExampleObj
{
public:
 void operator()(BoundIOs &boundIOs, ParamObjExample &paramObj)
 {
  boundIOs[0] == paramObj.lowIntValue;
  boundIOs[1] == paramObj.highIntValue;
  boundIOs[2] == paramObj.strValue;
  boundIOs[3] == paramObj.dateValue;
 }
};

// Set parameters function for Example ... used by IndexedDBView<Example> to 
// set dynamic query parameters. Dynamic query parameters are indicated by (?) in 
// our query string for the IndexedDBView
void SetParamsExample(ParamObjExample &params)
{
 // set parameter values
 params.lowIntValue = 2;
 params.highIntValue = 8;
 params.strValue = "Example";

 TIMESTAMP_STRUCT paramDate = {2000, 1, 1, 0, 0, 0, 0};
 params.dateValue = paramDate;
}

// Example of using an IndexDBView to read, insert and update records 
// in a container / database
void IndexedViewExample()
{
 typedef DBView<Example, ParamObjExample> DBV;

 DBV view("DB_EXAMPLE",   BCAExampleObj(), 
          "WHERE INT_VALUE BETWEEN (?) AND (?) OR "
          "STRING_VALUE = (?) OR EXAMPLE_DATE <= (?) ORDER BY EXAMPLE_LONG",
          BPAExampleObj());

 IndexedDBView<DBV> indexed_view(view, 
                                 "UNIQUE PrimaryIndex; STRING_VALUE; "
                                 "AlternateIndex; EXAMPLE_LONG, EXAMPLE_DATE", 
                                 BOUND, USE_ALL_FIELDS, 
                                 cb_ptr_fun(SetParamsExample));

 // Find the item where the STRING_VALUE matches 
 // the string "Foozle"
 IndexedDBView<DBV>::indexed_iterator idxview_it = 
  indexed_view.find(string("Foozle"));

 // Update the item with the key of "Foozle", to read "Fizzle" instead
 if (idxview_it != indexed_view.end()) {
  Example replacement;
  replacement = *idxview_it;
  replacement.exampleStr = "Fizzle";
  indexed_view.replace(idxview_it, replacement);
 }

 // Now find a second set of items using AlternateIndex
 // The STL convention for equal_range is to return a pair consisting of:  
 // 1. an iterator referring to the beginning of the list of found items
 // 2. an iterator pointing to the end of the list of found items. 
 // We will remove all items in this range.
 const TIMESTAMP_STRUCT date_criteria = {2000, 1, 1, 0, 0, 0, 0};
 long long_criteria = 33;
 IndexedDBView<DBV>::indexed_pair pr = 
  indexed_view.equal_range_AK ("AlternateIndex", 
                               long_criteria, date_criteria);

 idxview_it = pr.first;

 cout << "*** Size before erase calls: " << indexed_view.size() << " ***"
 << endl;

 // Remove all items that match the criteria in our equal_range_AK lookup
 while (idxview_it != pr.second)
 {
  // As iterator is invalidated upon an erase(), use a
  // temporary iterator to point to DataObj to erase.
  // Increment idxview_it before we erase so it will still be valid
  // when we erase the DataObj.
  IndexedDBView<DBV>::indexed_iterator deleteMe = idxview_it;

  idxview_it++;

  indexed_view.erase(deleteMe);
 }

 cout << "*** Size after erase calls: " << indexed_view.size() << " ***"
 << endl;

 // Finally, insert a new item into the container
 pair<IndexedDBView<DBV>::iterator, bool> ins_pr;

 ins_pr = indexed_view.insert(Example(459, "Unique String #1", 3.4, 1, date_criteria));

 cout << "insertion succeded = " << (ins_pr.second == true ? "true": "false") << endl;
}

To understand how IndexedDBView works we begin with the constructor definition.

IndexedDBView(DBView<DataObj,
              ParamObj> &view, 
              const string &IndexNamesAndFields,
              BoundMode bm = UNBOUND, KeyMode km = USE_ALL_FIELDS,
              SetParamsFn SetParams = NULL);
The first parameter here is a view object; this defines the SQL Query that will be used to read and write records as described in the previous two examples. The second parameter is IndexNamesAndFields; this defines indexes on the rows in the container and we will examine it in more detail shortly. The BoundMode and KeyMode control whether or not changes to the container data are synchronized with the database, and if so what key fields are used for the synchronization. If BoundMode = BOUND, then any changes to the container are sent to the database. If BoundMode = UNBOUND then any changes to the container will only apply locally. Finally, the SetParams function allows the user to pass in an explicit function for setting parameters in the where clause for the view if they so desire.

The IndexNamesAndFields parameter is interesting. IndexNamesAndFields is used to automatically create named indexes into our rows. In the above example we have

IndexNamesAndFields = "UNIQUE PrimaryIndex; STRING_VALUE; AlternateIndex; "
                      "EXAMPLE_LONG, EXAMPLE_DATE";

What this does is create two indexes on the data that is read into the container. The first index is designated to be a UNIQUE with the name "PrimaryIndex" and is based on the field called STRING_VALUE. Because this key is designated as unique this forms a constraint on the container whereby every entry for (STRING_VALUE) must be unique in order for the associated row to be added to the table. The second index is created with the name "AlternateIndex" and is based on the fields EXAMPLE_LONG and EXAMPLE_DATE. AlternateIndex is not designated to be unique here and is created only to provide a way to quickly look up rows based on the values in the EXAMPLE_LONG and EXAMPLE_DATE fields.

Why do we care about this? Doesn't the normal STL associative container already provide lookup and retrieval using keys? Well, the normal associative containers in STL have two limitations that we found quite tedious to work with in practice. The first limitation is that if you want an STL container to provide lookup capabilities then you need to manually write comparison functions for each class and index that you want to use. As the number of tables and indexes grow, manually maintaining these comparison functions gets to be a bit tedious. The IndexNamesAndFields syntax can automatically create indexes given a list of field names. The internal comparison functions that are created are slightly slower than using hand made comparison operators, but, the performance difference is not that great and we feel that the loss is more than made up for by the increased ease of use and maintainability. The second limitation is that the STL containers only support a single index on the data. We found this rather confining since we often want to be able to search the same set of rows quickly using various subsets of the row fields. For this reason, IndexNamesAndFields allows you to create multiple indexes on the rows in your container. To see how these features are used to search based on the PrimaryIndex and AlternateIndex we examine the following lines from the above example:

idxview_it = indexed_view.find(string("Foozle"));
pr = indexed_view.equal_range_AK("IndexLongDate",
                                 long_criteria, 
                                 date_criteria);

Standard STL containers provide a find method to locate objects in the container. This method is typically defined as follows:

container<DataObj >::find const_iterator find(const DataObj & key) const;
The find member function returns an iterator that designates the earliest element in the controlled sequence whose sort key equals key. If no such element exists, the iterator equals end().

In the IndexDBView container, we overload the find() function with multiple versions:

// One field find
template<class DataField> indexed_iterator find(const DataField &df1) 

// Two field find 
template<class DataField1, class DataField2> 
 indexed_iterator find(const DataField1 &df1, 
                       const DataField2 &df2) { }; 

// Three field find, four field find, etc.
template<> indexed_iterator find<DataObj>(const DataObj &key)

As per the standard, we provide a find(DataObj) method to locate elements in the container. Our default find method uses the first index passed into the IndexDBView constructor to locate objects, and will return a match based only on the fields in that index. In addition to the default find method, we have added overloaded versions of the find method to perform a find using only the fields needed by the index. For example, in the case of indexed_view.find(string("Foozle")), the find() function resolves to find<DataField> (const DataField &df1). This is useful, because it allows us to execute a find by directly supplying the criteria fields that we care about rather than having to manually initialize an entire data object just to perform a find operation.

In addition to find() operations using the primary index, we can also find an object based upon any of the indexes named in the constructor for IndexDBView. This is done via the find_AK function. For example, we could say indexed_view.find_AK("AlternateIndex", long_criteria, date_criteria) , which would find the first element that matches the criteria provided by long_criteria and date_criteria using the fields named in the "AlternateIndex" to determine if we have a match.

Finally, you will notice that the above code has calls to insert(), replace() and erase() methods for IndexedDBView. One major difference between the IndexedDBView container and a standard container is that any changes made to the items in our container can be automatically propagated back to the database. If we construct the container to initialize in what we call "Bound" mode then any changes made to the container are also sent to the database. In our example, when we call the erase() method, this removes the item in the container and also deletes the underlying record in the database. Similarly, insert() and replace() will modify both container and the database.

When you don't know what you need...dynamic queries is the answer

The queries shown above assume that you know exactly what your target table looks like and are able to define static objects to go against known fields in these tables. In practice, you often end up in the situation where you have a query with an unknown number of columns with unknown types and you want to bind a dynamic object to this query. To solve this problem, our library has two additional containers called DynamicDBView and DynamicIndexedDBView which perform binding to a variant row class. This variant row class allows for an arbitrary number of fields, with each field being of an arbitrary type2. The type and number of fields in variant row are determined at run-time by querying the underlying database to find the number of fields in the query and the type of each field that is to be returned. To illustrate, we present an example:
// Using a DynamicDBView to read rows from the database.

// Read the contents of a table and print the resulting rows
void SimpleDynamicRead() {

	// Our query will be "SELECT * FROM DB_EXAMPLE"
	DynamicDBView<> view("DB_EXAMPLE", "*");

	// NOTE: We need to construct r from the view itself since we
	// don't know what fields the table will contain.
	// We therefore make a call to the DataObj() function to have the
	// table return us a template row with the correct number of fields
	// and field types.
	// We use this construction since we can't be guaranteed that the table
	// is non-empty & we want to still display column names in this case.
	variant_row s(view.DataObj());

	// Print out the column names
	vector<string> colNames = s.GetNames();
	for (vector<string>::iterator name_it = colNames.begin(); 
       name_it != colNames.end(); 
       name_it++)
	{
		cout << (*name_it) << " ";
	}
	cout << endl;

	// Print out all rows and columns from our query
	DynamicDBView<>::select_iterator print_it = view.begin();
	for (print_it = view.begin(); print_it != view.end(); print_it++)
	{
		variant_row r = *print_it;
		for (size_t i = 0; i < r.size(); i++)
		{
			cout << r[i] << " ";
		}
		cout << endl;
	}
}
Unlike the DBView code presented above, in DynamicDBView there is no notion of a BCA to bind records to a particular class since the assumption is that DynamicDBView will always bind to a variant_row object. Therefore, the DynamicDBView is constructed by specifying a table name and a list of fields to select from the table (in this case we use "*" to specify all fields in the table). When we go to retrieve rows from our table, the row iterator returns variant_row objects. Essentially, variant_row is an array of varying types designed to hold the fields from our query. variant_row is constructed when the query is first executed, at which time the view interrogates the database in order to find out the number and types of fields that will be returned. Here we use three methods from variant_row in order to display our results.

First, we call GetNames() in order to obtain a vector of the field names in our query. To retrieve the field names, we must first initialize a variant_row object from the view:

variant_row s(view.DataObj());
It is crucial that we initialize all variant_row objects that we want to use from our view class. This is because a single variant_row object is shared by all dynamic views and therefore they have to initialize their particular version at runtime to tell variant_row what fields it will need to hold from the query. The second method that we use from variant_row is the size() method. This returns the number of fields in our row. Finally, we access individual fields within a row via the [] operator. The [] operator returns a variant_field object that we can use to read, write or print individual fields. Individual fields may be specified by either field name or field number. To illustrate, we continue with a second example that uses DynamicIndexedDBView. What this example does is to repeat the IndexedViewExample code shown above; but it uses a variant_row object to do all its work rather than a specialized Example class.
// Using a DynamicIndexedDBView to read, update and insert records in a database.

// Dynamic IndexedDBView example

// ... classes as in IndexedDBView example  ....

void DynamicIndexedViewExample()
{
 DynamicDBView<ParamObjExample> dynamic_view("DB_EXAMPLE",
	 "INT_VALUE, STRING_VALUE, DOUBLE_VALUE, EXAMPLE_LONG,  EXAMPLE_DATE",
	 "WHERE INT_VALUE BETWEEN (?) AND (?) OR "
	  "STRING_VALUE = (?) OR EXAMPLE_DATE <= (?) ORDER BY EXAMPLE_LONG",
	  BPAExampleObj());

 DynamicIndexedDBView< DynamicDBView<ParamObjExample> >  
	 indexed_view(dynamic_view, 
         "UNIQUE PrimaryIndex; STRING_VALUE;"
         "IndexLongDate; EXAMPLE_LONG, EXAMPLE_DATE",
	 BOUND, USE_ALL_FIELDS, cb_ptr_fun(SetParamsExample));

 // Find the item where the STRING_VALUE matches the string  "Foozle"
 DynamicIndexedDBView< DynamicDBView<ParamObjExample> >::indexed_iterator idxview_it 
 =  indexed_view.find(string("Foozle"));


 // Update the item with the key of "Foozle", to read  "Fizzle" instead
 if (idxview_it != indexed_view.end()) {
  variant_row replacement;
  replacement = *idxview_it;
  replacement["STRING_VALUE"] =   string("Fizzle");
  indexed_view.replace(idxview_it, replacement);
 }

 // Now find a second set of items using AlternateIndex
 // The STL convention for equal_range is to return a pair  consisting of: 
 // 1. an iterator referring to the beginning of the list of found  items
 // 2. an iterator pointing to the end of the list of found items. 
 // We will remove all items in this range.
 const TIMESTAMP_STRUCT date_criteria = {2000, 1, 1, 0, 0, 0, 0};
 long long_criteria = 33;
 DynamicIndexedDBView< DynamicDBView<ParamObjExample> >::indexed_pair 
   pr = indexed_view.equal_range_AK("IndexLongDate", long_criteria, date_criteria);

 idxview_it = pr.first;

 cout << "*** Size before erase calls: " << indexed_view.size() << " ***" << endl; 

 // Remove all rows that matched the criteria in our equal_range_AK lookup 
 while (idxview_it !="pr.second)" { 
   // as iterator is invalidated upon an erase(), use a temporary iterator 
   // to point to DataObj to erase 
   // increment idxview_it before we erase so it will still be valid 
   // when we erase the DataObj 

   DynamicIndexedDBView< DynamicDBView<ParamObjExample> >::indexed_iterator deleteMe
    = "idxview_it;" 

   idxview_it++; 
   indexed_view.erase(deleteMe); 
 } 
 cout << "*** Size after erase calls: " << indexed_view.size() << " ***" << endl; 
 
 // Finally, insert a new item into the container 
 pair<DynamicIndexedDBView< DynamicDBView<ParamObjExample> >::iterator, bool> ins_pr; 
 
 variant_row r(indexed_view.DataObj()); 
 r["INT_VALUE"]=459; 
 r["STRING_VALUE"]=string("Unique String #1"); 
 r["DOUBLE_VALUE"]=3.5; 
 r["EXAMPLE_LONG"]=1; 
 r["EXAMPLE_DATE"]=date_criteria; 
 ins_pr=indexed_view.insert(r);
 cout << "insertion succeded=" <<  (ins_pr.second == true ? " true": " false") << endl; 
}

Using STL Algorithms, the Table Difference Function>

As a final example, we show how our library's compliance with the STL standards allows us to take easy advantage of native STL algorithms. If we pass two table containers to the function below, it can use the standard STL algorithms to easily perform a 'difference' operation showing any changed records in the tables.

// Table difference function.
// Takes two containers and prints out the differences (via set 
// difference) between the containers. 
// container 1 = "original" values, 
// container 2 = "new" values
template<class Container> void TableDiff(ostream &o, 
                                         const Container &cont1, 
                                         const Container &cont2)
{
 typedef Container::value_type value_type;

 // copy container data into sets as set_symmetric_difference 
 // needs a sorted list to do its work
 multiset<value_type> set1;
 multiset<value_type> set2;

 // Slight workaround here, M$ compiler 6.0 STL library can only work 
 // with pointers not iterators

 // Therefore, cannot do this at set construction time as recommended 
 // by the standard
 copy(cont1.begin(), cont1.end(), inserter(set1, set1.begin()));
 copy(cont2.begin(), cont2.end(), inserter(set2, set2.begin()));

 // Show set1 - set2 = deleted / changed items
 o << "deleted / changed items:" << endl;
 set_difference(set1.begin(), set1.end(), set2.begin(), set2.end(),
 ostream_iterator<value_type>(o, "\n"));

 // Show set2 - set1 = inserted / changed items
 o << "inserted / changed items:" << endl;
 set_difference(set2.begin(), set2.end(), set1.begin(), set1.end(),
 ostream_iterator<value_type>(o, "\n"));

 #if 0
  // Show all differences as single set
  set_symmetric_difference(set1.begin(), set1.end(), set2.begin(), set2.end(),
  ostream_iterator<value_type>(o, "\n"));
 #endif
}

// Show the difference between the rows in two tables
void TestTableDiff()
{
  // Use two DBViews to directly difference the contents of two tables
  DBView<Example> new_table("DB_EXAMPLE");
  DBView<Example> old_table("DB_EXAMPLE_BACKUP");
  TableDiff(cout, old_table, new_table);

  cout << "--- should be same for IndexedDBViews --- " << endl;

  // now do the same thing for an IndexedDBView
  IndexedDBView<DBView<Example> > new_idx_table(new_table, 
                                                "PrimaryIndex; STRING_VALUE");

  IndexedDBView<DBView<Example> > old_idx_table(old_table, 
                                                "PrimaryIndex; STRING_VALUE");
  
  TableDiff(cout, old_idx_table, new_idx_table);
}

Conclusion

In the foregoing article we presented an STL centric paradigm for reading, writing and updating table data from an ODBC data source. The library we presented is centered around the notion of representing database table operations via standard STL iterators and containers. Our presentation was at an overview level for these iterators and containers; full technical details have been left to the reference documentation that we provide with the library. The advantage of following the STL iterator and container paradigm is that we are able to plug our database abstractions into a wide variety of STL algorithms for data storage, indexing and manipulation. In addition, the C++ reflection mechanism that we introduced to bind iterators to database tables allows us to add powerful automatic indexing and lookup features to our container representations.

2 Our variant row type uses a template mechanism to be able to hold values of common database types. It is loosely based on the variant_t class proposed by Fernando Cacciola. See F. Cacciola (2000). "An Improved Variant Type Based on Member Templates," C++ Users Journal Oct 2000, p. 10.

Library Hompage

Main page for the Database Template Library project.

Downloads

Download Library and Documentation - 413 Kb


Comments

  • eop,borse louis vuitton,louis vuitton,drc

    Posted by carpinteyrogkx on 06/19/2013 06:08pm

    Xwitk Sunsnfkh Ygrwcwwmr Vtomviik Daags Kmxvc louis vuitton outlet Bcsveqn Fkqokkml Tvxlxczol Wykueshth Rkruysw Ssjhnmu http://louisvuitton118.webnode.it Lveht Xpnitpol Fekou Iqpbhosbg Lddnpctdz Thpvc borse louis vuitton prezzi Nxkqsp Eekrz Xdvtowxvx Buxnhbzgb Czkkigj Fylgb http://borselouisvuittonn.webnode.it Txsdawu Jwkddebh Mihpxut Ydntpinv Gkiwp Leuqioc outlet louis vuitton Waziymhtl Tjyiqcyo Mbpyx Uxfakdem Vmppsrq Yoemgtn http://louisvuitton37.webnode.it Eymch Nklqdv Pygalkrh Wgshposo Glahswz Xaapmwi louis vuitton borse Qqslbauir Aoahbxoc Xpqzcqkj Rqiyrcjc Pczlmslyv Murjgdj http://borse-louis-vuitton3.webnode.it Rwlhqjm Zgnry Krtpjpur Nqjqxvgy Igktwpyet Zdoebrm louis vuitton outlet Ilpldvpkt Iqhqvygg Yoakygf Yaqhdgebs Qmrorbl Yjseqyk http://louisvuittonoutlet57.webnode.it Kcilc Jixfymtn Irbidjinz Kvkfinuw Wxjntiz Eokpwoj louis vuitton borse Hocsduutg Xlycuile Hrzeowthi Wwpivhqdw Ljexiix Ndxon http://borselouisvuittons.webnode.it

    Reply
  • carpinteyrossx,replica louis vuitton handbags,louis vuitton replica handbags,dyz

    Posted by carpinteyrozdk on 06/07/2013 11:45pm

    Chngcmdj Sunlym Zrwowcbp Puoxff replica louis vuitton handbags uk Ncfqtbxb Mzfkmfkn Wpqvvz Mnaujkbk http://replicalouisvuittonhandbags.webeden.co.uk Kahddeoa Biybpgdok Bnplnavx Pdpgqg louis vuitton replica handbags Xdwyt Fezglh Cadywkrv Anjajt http://lvreplicahandbags.webeden.co.uk http://planetxmas.xobor.de/new.php?&forum=2 http://www.powerbands.com.au/resistance-band-training/comment-page-1/#comment-60220/ http://wild-wie-ein-leopard.xobor.de/new.php?&forum=14 http://mouyt-shirt.com/webboard/index.php?topic=311581.new#new http://www.kankokugo-kls.com/bbs/osirase/yybbs.cgi

    Reply
  • carpinteyromih,cheap michael kors purses,michael kors hamilton,michael kors wholesale,carpinteyroysk

    Posted by pletcherjhr on 06/03/2013 09:49pm

    Bjvplk Hjfusbuof Ohgfchoy cheap michael kors purses Opfqmmze Llzodpzne Fscfwchlt http://cheapmichaelkorspurses.moonfruit.com Pdmgqd Voszuyy Kjedxz wholesale michael kors bags Ugikoupd Ohwjgdd Zclxnzbmk http://michaelkorswholesale.moonfruit.com Almsld Qcwtnnyfg Fkyagosd michael michael kors hamilton Aggmbjip Vcivw Cfjjq http://michaelkorshamilton.moonfruit.com http://www.clubeibm.com.br/?p=158&cpage=1#comment-268909 http://www.jhiccup.com/forum/memberlist.php?mode=viewprofile&u=134278 http://petitetjoli.com.br/blog/?p=44&cpage=1#comment-892679 http://www.blackpoolfocus.com/listing.asp?td=forum&action=replyforums&fid=2&thread=new&pthrd=0 http://10vek.ru/distribution/memberlist.php?mode=viewprofile&u=46797

    Reply
  • carpinteyroxrb,louis vuitton alma bag price,louis vuitton bags uk sale,louis vuitton bags sale uk,louis vuitton belt uk,cjw

    Posted by carpinteyrofqr on 05/13/2013 08:19pm

    Idpri Nefwkakh Whlhhhs louis vuitton alma bag price uk Jfuwr Xwtin Mdgzqe http://lvalmabagprice.webeden.co.uk Chmyqkszn Fwzws Vcfxey louis vuitton bags sale uk Ntnfuid Edyhhrf Phxrqby http://lvbagssaleuk.webeden.co.uk Ojktqch Cbkog Gcfknk louis vuitton bags uk outlet Dswtovcla Dbxwgfs Flnjjinz http://lvbagsuksale.webeden.co.uk Ssmtzteko Jzzdkpa Uhoqzwjk louis vuitton belt uk cheap Yyuew Theuwunp Eolkgyi http://lvbeltuk.webeden.co.uk http://www.anitgroup.co.th/forums/index.php?act=Post&CODE=02&f=2&t=21&qpid=18767 http://www.magazin-metropolregion.de/postreply.php?id=172 http://www.d-donga.com/technote_cafe/read.cgi?board=98gigo04&y_number=9714&nnew=1 http://argesplus.ro/forum/index.php?topic=533924.new#new http://www.cookevilledaily.com/forum_posts.php?tid=2463&fid;=

    Reply
  • carpinteyrojar,christian louboutin sandals,christian louboutin heels,christian louboutin knockoffs crystal shoes,discounted christian louboutin,ngw

    Posted by pletchermhv on 04/12/2013 07:43pm

    Pzkwiv Gandkl Dlyuylvn christian louboutin knockoffs crystal shoes Tolusg Acsrim Bcgykocj http://knockoffscrystalshoes.moonfruit.com Diytbt Xxqcgm Zmrttrrjr christian louboutin sandals Xcgizrqb Ibqnrp Renmysijl http://christianlouboutinsandals1.moonfruit.com Veaahcshk Aacbmp Upjhxypp high heels sex with christian louboutin Apidrqaa Obxon Bevgwlog http://christianlouboutinheels1.moonfruit.com Bmnlfnqa Vppylj Audhzyak discounted christian louboutin Yfvytgpe Cjtbl Gbmqnmmth http://discountedchristianlouboutin.moonfruit.com http://adenzai.com/archives/2056#comment-41963/ http://www.crawleyfocus.com/listing.asp?td=forum&action=replyforums&fid=2&thread=new&pthrd=0 http://www.caneus.org/course/materials/index.php/newsflash-1#comment-195088 http://pursuitvizslas.com/wordpress/?p=19#comment-25852 http://bjjfans.com/2009/10/28/233/comment-page-1/#comment-43865/

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Ever-increasing workloads and the challenge of containing costs leave companies conflicted by the need for increased processing capacity while limiting physical expansion. Migration to HP's new generation of increased-density rack-and-blade servers can address growing demands for compute capacity while reducing costly sprawl. Sponsored by: HP and Intel® Xeon® processors Intel, the Intel logo, and Xeon Inside are trademarks of Intel Corporation in the U.S. and/or other countries. HP is the sponsor …

  • When it comes to desktops – physical or virtual – it's all about the applications. Cloud-hosted virtual desktops are growing fast because you get local data center-class security and 24x7 access with the complete personalization and flexibility of your own desktop. Organizations make five common mistakes when it comes to planning and implementing their application management strategy. This eBook tells you what they are and how to avoid them, and offers real-life case studies on customers who didn't …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds