dtl


DBView<DataObj, ParamObj>

Category: containers Component type: type

Description

The DBView Container is a semi-Container with the property that elements are bound to an underlying database. We say that DBView is a semi-Container because it implements all properties and methods of an STL container except for the empty(), size() and max_size() methods. We do not implement empty(), size() or max_size() for this container because we cannot guarantee invariance for these functions; e.g. after a programmer calls the size() function a second user may insert more records into the database on the back-end, therby invalidating the fixed count the programmer might expect. Internally the DBView also manages a key mode and list of key fields so that it can properly work with the underlying DBMS's autokey mechanism for updates and deletes (see select_update_iterator for more details).

Definition

Defined in the DBView.h header file.

Refinement of

semi-Container

Associated types

The types defined by Container and the additional iterator types listed below.

Example 1 :

Mapping a Table to a User Defined Object 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.

Example 2:

//BPA Functor to bind SQL parameters to a data object

// "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)
	{ }

};

// Create an association between table columns and fields in our object
class BCAExampleObj
{
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;
	}
}

class ExampleParamObj
{
    public:
       	int lowIntValue;
	int highIntValue;
	string strValue;
	TIMESTAMP_STRUCT dateValue;
};

class BPAParamObj
{
public:
	void operator()(BoundIOs &boundIOs, ExampleParamObj &paramObj)
	{
	  boundIOs[0] << paramObj.lowIntValue;
	  boundIOs[1] << paramObj.highIntValue;
	  boundIOs[2] << paramObj.strValue;
	  boundIOs[3] << paramObj.dateValue;
	}

};

// read some Example objects from the database and return a vector of
// the results, use BPA to set join parameters
vector<Example> ReadData()
{
	vector<Example> results;

	// construct view
	
	DBView<Example, ExampleParamObj>
		view("DB_EXAMPLE", BCAExampleObj(),
		"WHERE INT_VALUE BETWEEN (?) AND (?) AND "
		"STRING_VALUE = (?) OR EXAMPLE_DATE < (?) ORDER BY EXAMPLE_LONG",
		BPAParamObj());

	// loop through query results and add them to our vector
	// in this loop, read_it.GetLastCount() records read from DB

	DBView<Example, ExampleParamObj>::select_iterator read_it = view.begin();

	// set parameter values for the WHERE clause in our SQL query
	read_it.Params().lowIntValue = 2;
	read_it.Params().highIntValue = 8;
	read_it.Params().strValue = "Example";
	
	TIMESTAMP_STRUCT paramDate = {2000, 1, 1, 0, 0, 0, 0};
	read_it.Params().dateValue = paramDate;

	for ( ; read_it != view.end();  read_it++)
	{
		cout << "Reading element #" << read_it.GetLastCount() << endl;
		results.push_back(*read_it);

		cout << "read_it->exampleInt = " << read_it->exampleInt << endl;
		cout << "read_it->exampleStr = " << read_it->exampleStr << endl;
		
	}
	
	return results;
}

Example 3:

// 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()(BoundIOs &boundIOs, 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;
	}
}

Public Base Classes

None.

Template parameters

Parameter Description Default
DataObj The type of object that will be written to the DBView. This object will be bound through use of the BCA to the appropriate columns in the database. The set of value types of an DBView::insert_iterator consists of a single type, DataObj.  
ParamObj The type of object that will be used to specify the postfix parameters to the DBView. DefaultParamObj<DataObj> 

 

Notation

X A type that is a model of DBView
a Object of type X
t Object of type X::value_type
p, q Object of type X::iterator

 

Associated types

In addition to the types defined in a semi-Container , the following types are defined.:

Iterator type X::select_iterator An input iterator to read records from the view
Iterator type X::insert_iterator An output iterator to insert records into the view
Iterator type X::update_iterator An output iterator to update records in the view
Iterator type X::delete_iterator An ouput iterator to delete records from the view
Argument object type X::Args

Nested class for arguments object used to pass parameters to the DBView constructor.

Accessors to set arguments in an Args object (all return *this as an Args &):

  • tables(const string &tableList) - corresponds to tableList parameter in main DBView constructor - must be set for constructed DBView to be valid
  • bca(const BCA bca_functor) - corresponds to bca_functor parameter in main DBView constructor - default value: DefaultBCA<DataObj>()
  • postfix(const string &postfixClause) - corresponds to postfix parameter in main constructor - default value: ""
  • bpa(const BPA bpa_functor) - corresponds to bpa_functor parameter in main constructor - default value: DefaultBPA<ParamObj>()
  • SelValidate(const SelVal sel_val) - corresponds to sel_val parameter in main constructor - default value: DefaultSelValidate<DataObj>()
  • InsValidate(const InsVal ins_val) - corresponds to ins_val parameter in main constructor - default value: DefaultInsValidate<ParamObj>()
  • handler(const IOHandler<DataObj, ParamObj> io_hand) - corresponds to io_hand parameter in main constructor - default value: DEFAULT_IO_HANDLER<DataObj, ParamObj>()
  • conn(DBConnection &connection) - corresponds to connection parameter in main constructor - default value: DBConnection::GetDefaultConnection()
  • fields(const string &fieldList) - corresponds to fieldList parameter in DynamicDBView main constructor -- ONLY FOR USE WITH A DynamicDBView, NOT DBView - default value: ""
  • key_mode(KeyMode km) - corresponds to km parameter in DynamicDBView main constructor -- ONLY FOR USE WITH A DynamicDBView, NOT DBView - default value: USE_AUTO_KEY
  • keys(const string &keyList) - corresponds to keyList parameter in DBView constructor - default value: ""

To set the arguments, default construct an Args object and then successively apply accessors to this object. The parameters you reference will be set to the passed in values, all others will receive their default values. For example, to construct a view of Example objects that references the DB_EXAMPLE table and uses a connection named myConn, you would say:

DBView<Example> view(DBView<Example>::Args().tables("DB_EXAMPLE").conn(myConn));

The Args class is designed to emulate named arguments so you don't have to pass arguments around that just use their default values. The constructor call above would have to explicitly pass 6 arguments with their corresponding default values, which is tedious, hard to read, and very error prone.

 

Expression semantics

Name Expression Precondition Semantics Postcondition
Main constructor 
DBView(const string &tableList, 
const BCA bca_functor = DefaultBCA<DataObj>(),
const string &postfix = "", 
const BPA bpa_functor = DefaultBPA<ParamObj>(),
const SelVal sel_val = DefaultSelVal<DataObj>(),
const InsVal ins_val = DefaultInsVal<DataObj>(),
IOHandler<DataObj, ParamObj> io_hand = DEFAULT_IO_HANDLER<DataObj, ParamObj>(),
DBConnection &connection = DBConnection::GetDefaultConnection(),
const string &keyList = "")
   Creates an empty container bound to a table, using the database connection specified in the connection object. The actual queries run againt this table are built by the select, insert, update and delete iterators (see these iterators for details) but essentially follow the following format:

"SELECT " + <columns listed in bca_fn()> + " FROM " + tableList + postfix

In the SQL clause, users may also specify dynamic parameters through the use of SQL '(?)' syntax. In this case BPA is a class that is used to hold these runtime parameters and bpa_functor is a functor used to associate fields in the parameters class with parameter fields in the query.

A raw SQL query can be passed for the first parameter instead of the table names along with an empty postfix clause. Use this technique when you want to (and only when you want to) create a sql_iterator later over the view.

The user can customize the query building mechanism for his DBView by specializing the BuildSpecialQry template functor.

The user can pass in a comma separated list of fields indicating the key for the view. This will get used by select_update_iterator to determine which objects to update if the view's key mode is USE_ALL_FIELDS.

 The size of the container is 0. Rows are operated on by obtaining the appropriate iterator from the container.
Main constructor (accepting Args object) DBView(const Args &args) !args.tableList.empty()

Same as above, with the parameters specified in an Args object.

See description of Args nested type above for use.

  

 

Members

Member Where defined Description
value_type Container The type of object, T, stored in the DBView. In this case T = DataObj
pointer Container Pointer to T.
reference Container Reference to T
const_reference Container Const reference to T
size_type Container An unsigned integral type.
difference_type Container A signed integral type.
iterator Container Iterator used to iterate through a DBView. The default iterator for DBView is the select_iterator.
const_iterator Container Const iterator used to iterate through a DBView. (iterator and const_iterator are the same type.)
reverse_iterator Reversible Container Iterator used to iterate backwards through a DBView.
const_reverse_iterator Reversible Container Const iterator used to iterate backwards through a DBView. (Reverse_iterator and const_reverse_iterator are the same type.)
iterator begin() Container Returns an iterator pointing to the beginning of the DBView.
const_iterator begin() const Container Returns a const_iterator pointing to the beginning of the DBView.
iterator end() Container Returns a const_iterator pointing to the end of the DBView.
const_iterator end() const Container Returns a const_iterator pointing to the end of the DBView. 
DBView(const string &tableList, 
const BCA bca_functor = DefaultBCA<DataObj>(),
const string &postfix = "", 
const BPA bpa_functor = DefaultBPA<ParamObj>(),
const SelVal sel_val = DefaultSelVal<DataObj>(),
const InsVal ins_val = DefaultInsVal<DataObj>(),
const IOHandler<DataObj, ParamObj> io_hand = DEFAULT_IO_HANDLER<DataObj, ParamObj>(),
DBConnection &connection = DBConnection::GetDefaultConnection(),
const string &keyList = "")
 DBView Creates a DBView.
DBView<const Args &args) DBView Creates a DBView using an arguments object.
DBView(const DBView&) Container The copy constructor.
DBView& operator=(const DBView&) Container The assignment operator
void set_io_handler(IOHandler<DataObj, ParamObj>) DBView Sets the IOHandler for this view. Iterators created from this view will then use this handler by default until set by the iterator's version of this method. The IOHandler will try to handle exceptions thrown by such iterators and tell the code which caught the exception whether to suppress the error or still throw.
template<class UserHandler> const UserHandler & get_io_handler(UserHandler *dummy) const DBView Returns the current IOHandler for this object cast to the actual type of the handler based on the dummy pointer passed in. If the dynamic cast of the IOHandler object fails, an exception will be thrown.
IOHandler<DataObj, ParamObj> &get_io_handler() const DBView Returns the current IOHandler for this object as a raw IOHandler object. You must cast to the actual type of the handler to be able to access any of your handler's public members.
void swap(DBView&) Container Swaps the contents of two DBView's.
DataObj GetDataObj() const DBView Returns a prototype DataObj with its structure built if necessary (needed in the case of variant_row).
void SetKey(const string &keyList) DBView Set the list of key fields for the view. keyList is a comma separated list of those key fields.

BuildSpecialQry: Customizing the query generated by your DBView

In most cases, the SQL query generated by your DBView will do just fine. However, there are times you may wish to do something special, such as fetching distinct values or some other custom sql statement which cannot be expressed through a postfix clause (and for which a sql_iterator will not do). The primary use of BuildSpecialQry is actually to control the sql statements used by an IndexedDBView. Since an IndexedDBView uses a view to do its work we can create a BuildSpecialQry function to control exactly what SQL statements are generated to select, insert, update or delete records by the IndexedDBView. DTL provides a BuildSpecialQry template functor to allow you to customize the query that gets built for any of the iterators generated by a DBView. The generic version of this functor simply forwards the call to the BuildDefaultQry() template function, which serves as the default query building mechanism for DBViews. To override the default query building mechanism, for a given DBView<DataObj, ParamObj>, define a corresponding specialization: BuildSpecialQry<DataObj, ParamObj>. Put your special behavior in BuildSpecialQry::operator()(). You can provide special behavior based on the SQLQueryType value passed in. If you wish to leave the behavior unchanged for a particular query type, simply forward the operator()() call to BuildDefaultQry(). The example below will grab only distinct DistinctExample objects from the database when the records are fetched by a DBView::select_iterator. Note the use of various helper functions and DBView accessors used to build key parts of the query (such as MakeDelimitedList()) based on the view's attributes. Also notice that if we were to use DBView<distinctexample> as an argument to an IndexedDBView then the fetch for the IndexedDBView (which uses a select iterator) would also be built as "SELECT DISTINCT ..."


// special query builder for DBView
template<> class BuildSpecialQry<DistinctExample> :
	public binary_function<const DBView<DistinctExample> &, SQLQueryType, string>
{
public:
  // build the special query
  string operator()(const DBView<DistinctExample> &view, SQLQueryType qryType)
  {
	switch (qryType)
	{
		case SELECT:
		{
		  string Query;

		  // get the necessary info. we need from the view
		  string postfixClause = view.GetPostfixClause();
		  string rawSQL = view.GetRawSQL();
		  set<string> tableNames = view.GetTableNames();
		  set<string> colNames = view.GetColNames();
		 
		  // SELECT DISTINCT colNames FROM tableNames postfixClause
		  if (tableNames.empty())
			throw DBException("DBView::BuildQry()",
				   "SELECT : must select from at least one table", NULL, NULL);

		  // Catch this error in MakeBindings()
		  // Need to leave this test out so that sql_iterator can work
		  //
		  //if (colNames.empty())
		  //	throw DBException("DBView::BuildQry()",
		  //		   "SELECT : must select from at least one column", NULL, NULL);

		  // ***** Special behavior right here *******
		  // build SELECT stmt into Query ... only select DISTINCT records !!!!!
		  Query += "SELECT DISTINCT ";

		  // build comma-separated list of columns and tack on to the query
		  Query += MakeDelimitedList(colNames);

		  Query += " FROM ";

		  // now add a comma-separated list of the tables
		  Query += MakeDelimitedList(tableNames);

		  // tack on postfix clause
		  if (postfixClause.length() > 0)
		  {
		      Query += " ";
		      Query += postfixClause;
		  }
		  
		  return Query;

		}
		default:
		  return BuildDefaultQry(view, qryType);
	} // end switch
  } // end operator()

}; // end BuildSpecialQry<DistinctExample>

See also

Multiple Associative Container, BCA, BPA, InsVal, SelVal, select_update_iterator, DB_iterator

[DTL Home]

Copyright © 2002, 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.

This site written using the ORB. [The ORB]

1