dtl_introduction.htm

来自「The goal of this library is to make ODBC」· HTM 代码 · 共 1,132 行 · 第 1/4 页

color="#0000FF" face="Times Roman"> </font><font
face="Times Roman">for the definition of an STL container, we
call DBView a <b>semi</b> container because it supports all
standard container methods <b>except</b> size(), max_size() and
empty(). We explain why these were left out by design in the
documentation for the DBView template. <br>
</font></p>
 </strong><br>
<br>
<font face="Times Roman">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 </font><a
href="http://www.sgi.com/tech/stl/InputIterator.html"><font
color="#0000FF" face="Times Roman"><u>http://www.sgi.com/tech/stl/InputIterator.html</u></font></a><font
color="#0000FF" face="Times Roman"> </font><font
face="Times Roman">and </font><a
href="http://www.sgi.com/tech/stl/OutputIterator.html"><font
color="#0000FF" face="Times Roman"><u>http://www.sgi.com/tech/stl/OutputIterator.html</u></font></a><font
color="#0000FF" face="Times Roman"> </font><font
face="Times Roman">.<br>
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:<br>
<br>
<b>Input Iterators:<br>
</b>select_iterator<br>
<br>
<b>Output Iterators:<br>
</b>insert_iterator<br>
update_iterator<br>
delete_iterator<br>
<br>
To illustrate the use of an output iterator we show how a vector
of rows would be inserted into a table.<br>
<br>
<strong><pre><code><span class="codeComment">// Using a DBView to insert rows into a database</span>


<span class="codeComment">// ... Class definitions for Example and BCAExample as per our </span><a
href="DBViewReadData.htm">ReadData</a> <span class="codeComment">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</span>
template&lt;&gt; class dtl::DefaultInsValidate&lt;Example&gt; 
{
public:

	bool operator()(Example &amp;rowbuf) {	
		<span class="codeComment">// data is valid if rowbuf.exampleStr is nonempty and
		// rowbuf.exampleDouble is 
		// between 0 and 100 (like a percentage)</span>
		return (rowbuf.exampleStr.length() &gt; 0 &amp;&amp;  rowbuf.exampleDouble &gt;= 0.0 
			&amp;&amp; rowbuf.exampleLong  &lt;= 100.0);
	}
};


<span class="codeComment">// Insert rows from the vector&lt;Example&gt; parameter into the database</span>
void WriteData(const vector&lt;Example&gt; &amp;examples)
{
	DBView&lt;Example&gt; view(&quot;DB_EXAMPLE&quot;);

	<span class="codeComment">// loop through vector and write Example objects to DB</span>
	// write_it.GetCount() records written in loop

	DBView&lt;Example&gt;::insert_iterator write_it = view;

	for (vector&lt;Example&gt;::const_iterator ex_it = examples.begin(); ex_it != examples.end(); ex_it++, write_it++)
	{
		*write_it = *ex_it;
	 	cout &lt;&lt; &quot;Writing element #&quot; &lt;&lt; write_it.GetCount() + 1&lt;&lt; endl;
	}
}
</code></pre>

 </strong><br>
<br>
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. <br>
<br>
In general, the constructor for DBView&lt;class DataObj, class
ParamObj = DefaultParamObj&lt;DataObj&gt&gt; takes the form<br>
<br>
<b>DBView(const string &amp;tableList, const BCA &amp;bca_functor =
DefaultBCA&lt;DataObj&gt;(),<br>
const string &amp;postfix = &quot;&quot;, const BPA &amp;bpa_functor =
DefaultBPA&lt;ParamObj&gt;(),<br>
const SelVal sel_val = DefaultSelValidate&lt;DataObj&gt;(),<br>
const InsVal ins_val = DefaultInsValidate&lt;DataObj&gt;(),<br>
DBConnection &amp;connection = DBConnection::GetDefaultConnection())<br>
</b><br>
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.<br>
<br>
<br>
</font><a name="_Toc500425985"></a><font face="Arial"><b><i>A
Second Example, Parameterized Queries:<br>
</i></b></font><font face="Times Roman">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.</font> <br>
<strong><pre><code><span class="codeComment">// Using dynamic parameters to join two tables


// For purposes of illustration we introduce a table called DB_SAMPLE </span>

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

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

friend class BCAJoinExample;
friend class BPAJoinParamObj;
};

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

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

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


<span class="codeComment">// Read JoinExample objects from the database using a query that
// joins the DB_EXAMPLE and DB_SAMPLE tables</span>
vector&lt;JoinExample&gt; ReadJoinedData()
{
	vector&lt;JoinExample&gt; results;

	<span class="codeComment">// construct view
	// note here that we use a custom parameter class for JoinExample
	// rather than DefaultParamObj&lt;JoinExample&gt;</span>

	DBView&lt;JoinExample, JoinParamObj&gt;
	view(&quot;DB_EXAMPLE, DB_SAMPLE&quot;,	BCAJoinExample(),
	&quot;WHERE (INT_VALUE = (?) AND STRING_VALUE = (?)) AND &quot;
	&quot;(SAMPLE_INT = (?) OR SAMPLE_STR = (?)) &quot;
	&quot;ORDER BY SAMPLE_LONG&quot;, BPAJoinParamObj());

	<span class="codeComment">// loop through query results and add them to our vector</span>
	DBView&lt;JoinExample, JoinParamObj&gt;::select_iterator read_it = view.begin();

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

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

	return results;
}
</code></pre> </strong><br>
<font face="Times Roman">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 &quot;(?)&quot; 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 ( </font><font face="Fixedsys">&quot;ORDER
BY SAMPLE_LONG&quot; </font><font face="Times Roman">). 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 </font><font face="Fixedsys">&quot;SUM(INT_VALUE)&quot;
</font><font face="Times Roman">instead of simple column names.
The BCA and BPA are specified as function objects, i.e. functors.<br>
<br>
<br>
</font><a name="_Toc500425986"></a><font face="Arial"><b><i>Tables
R Us, The IndexedDBView:<br>
</i></b></font><font face="Times Roman">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 </font><a
href="http://www.sgi.com/tech/stl/UniqueAssociativeContainer.html"><font
color="#0000FF" face="Times Roman"><u>http://www.sgi.com/tech/stl/UniqueAssociativeContainer.html</u></font></a><font
color="#0000FF" face="Times Roman"> </font><font
face="Times Roman">.<br>
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:<br>