dtl
Introduction to the Database Template Library
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
Introduction to the Database Template Library
Corwin Joy * Michael Gradman
Caminus, Suite 1150, Two Allen Center, 1200 Smith Street, Houston, TX 77002
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 the database.
//////////////////////////////////////////////////////////////////////////////////////
#include "dtl.h"
using namespace dtl;
// Connect to the database
DBConnection::GetDefaultConnection().Connect("UID=example;PWD=example;DSN=example;");
// Create a container to hold records from a query.
// In this case, the query will be "SELECT * FROM DB_EXAMPLE"
DynamicDBView<> view("DB_EXAMPLE", "*");
// Read all rows from the database and send to cout
copy(view.begin(), view.end(), ostream_iterator<variant_row>(cout, "\n"));
//////////////////////////////////////////////////////////////////////////////////////
The three steps shown above are:
In addition to dynamic queries which examine the database at runtime, we also provide templates that allow the user to bind database tables directly to their own objects.
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.
// 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;
}
}
// 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 ¶mObj)
{
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;
}
// "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 ¶mObj)
{
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 ¶ms)
{
// 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 :
template<class DataField> indexed_iterator find(const
DataField &df1) // One field find
template<class DataField1, class DataField2>
indexed_iterator find(const DataField1 &df1, const DataField2
&df2) { }; // Two field find
// Three field find, four field find, etc.
template<> indexed_iterator find<DataObj>(const
DataObj &key) // Standard find
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:
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.
Copyright © 2001, Michael Gradman and Corwin Joy.
Permission to use, copy, modify, distribute and sell this software and its documentation for any purpose is hereby granted without fee, provided that the above copyright notice appears in all copies and that both that copyright notice and this permission notice appear in supporting documentation. Corwin Joy and Michael Gradman make no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty.