From: Corwin Joy (cjoy_at_[hidden])
Date: 2005-08-22 20:54:14
Having designed the database template library (a C++ STL type interface for
ODBC at http://dtemplatelib.sourceforge.net/) I thought I'd throw in a few
cents about some design you'll want to think through *very* carefully.
Here are some key questions to ask:
ODBC supports multiple cursors types which I think are pretty typical of
various kinds of databases.
You want to think carefully about how you map these to STL iterator types.
Lets say you do a simple mapping of
forward cursor -> input iterator and
scrollable cursor -> random access iterator.
Immediately there are "gothcas"
- What happens when you copy your iterator? Should it point to the same
cursor? If you do then making a copy of the iterator and incrementing will
have a "side effect" on the original iterator. If you do not then users may
be surprised when they compare two copies of the same iterator and don't get
the same thing. How can two cursors be equal to begin() of a container but
not have the same value?
random access iterator:
- I assume your iterators will all point to the same recordset resource -
but if so, how do you avoid "thrashing". If a and b are two iterators that
point to the same scrollable recordset at different then you don't really
want to be excuting multiple database positioning operations to go back and
forth between the values. This implies some kind of intelligent caching but
you want to be carefull or you'll end up with more than you can handle in
memory since it is not uncommon for tables to have a million rows or more
(O.K., maybe the Human Genome center is a bit larger than most in this
regard :-) ).
- You'll probably also want to implemnent size() operations
intelligently, paging through every record and counting is not so good...
- Be aware of the performance cost of scrollable recordsets (see below
We also had a input + output iterator that we did to allow users to loop
through a recordset and modify values.
- In DTL we wanted to go away from the MFC model of forcing you to inject
our code into your classes. Instead we defined operators that specify the
mapping relationship between columns in your query and members in your
class. I think this is a good model since it keeps the database mappings
from getting tangled up with the class logic. This kind of design is also
used very successfully in the Java world by popular projects like Hibernate
which let you define your class as you need and simply ask you to provide
XML type mappings. I'm also not a big fan of frameworks that force you into
a particular class design.
Here is a link from DTL showing this:
Here is an example of a hibernate mapping:
- One of the design goals in DTL was to try not to be "smart" about SQL.
The fact is that folks do all kinds of strange things in SQL like different
outer join syntax, hierarchical queries, analytic functions over join
partitions, stored proceedures etc. I think there is a real advantage to
having an iterator that can "get out of the way" and NOT try to parse what
the user is doing so they can execute these kinds of complex queries. Also,
SQL is well understood by most developers so letting them use it
transparently rather than creating your own join language will significantly
reduce the learning curve.
- Having said this, being able to construct select, insert, update
statements for the user has advantages - it lets you create read/write
containers from a set of bindings transparently and can let you work around
limitations of the underlying drivers.
- In ODBC, at least, this is rather ugly. You can choose to either bind a
fixed size buffer (implying a maximum string size) or you can use something
called GetData and PutData to transfer an arbitrary length string in blocks.
If you choose the latter (which is what we did so folks could bind
std::string and not have to recompile every time their database field size
changed) ODBC gets annoying in that many drivers then require that such
string columns have to come last.
Think carefully about how you want to support stored procedures:
1. A stored procedure can have input parameters, output parameters and can
return multiple sets of results (and will in SQL server).
How to support user defined types?
1. Users will want to define custom types that can be mapped transparently
to sets of database fields. Examples:
- A complex data type that maps to a pair of fields.
- A "date" type that is stored as an integer (similar to boost::time)
but might be written to multiple database fields.
- Mapping of more complicated types, graphs, sounds etc. to blobs.
In ODBC, at least, there is not a database independent notion of a
transaction. Instead, when you commit, you commit everything open against
that connection. So if you want multiple transactions you need multiple
connections. We went with C++ try...catch logic to handle transactions
since this closely mirrors some of the ACID concepts you need for
transactions in a database. So we would have
// a set of operations here
More on our design for this is here:
More later... anyway I would be interested in helping on this project but
don't know how much time I will have to commit to it. I definitely like the
idea of defining the base layer in terms of simple operations so that it can
support both ODBC and native drivers since I think this would be a big
advantage for situations where and ODBC driver simply is not available
Here is some background on how the ODBC cursor types compare to the C++
iterator types and a little bit on how the mapping was done in DTL
The process of retrieving rows from a query result set and returning them to
the application is called fetching. An application fetches data with a
cursor. An ODBC cursor on a result set indicates the current position in the
result set and what row of data will be returned next. In ODBC there are
two main cursor types.
Forward Only Cursors:
- Can only move forward through the set of records.
- Are not guaranteed to move through the records in any particular
- Two different cursors against the same table using the same query
may not return the same rows or same number of rows due to inserts, updates
- Can move forward and backward through the result set.
- Two different cursors against the same table using the same query
may not return the same rows or same number of rows due to inserts, updates
Scrollable cursors raise two additional questions not seen in forward-only
cursors. First, how does the cursor establish some kind of order for the set
of records returned? Without some kind of order for the records moving
"forward" or "backward" through the result set does not have any meaning.
Second, should the cursor detect changes to rows that have been previously
fetched? In other words, when we re-position the cursor should it detect
changes due to inserted, updated or deleted rows? Depending on how these
questions are answered one obtains different scrollable cursor types.
- Static cursor type.
o Once the cursor has been created, the set of records is fixed
regardless of any subsequent changes made to the database. Any inserts,
updates or deletes by other applications to the set of records are not
detected by this cursor. Usually this is implemented either by locking the
underlying table so that no changes can be made by other applications while
the cursor is in effect, or the set of records is viewed as of a particular
- Dynamic cursor type.
o Detects any changes made to the set of records due to insert, update
or delete by other applications. Usually this kind of cursor is implemented
by simply ordering the records with a unique index. This cursor then
re-executes a fetch every time it scrolls to a new set of rows. Suppose the
original set of records is defined by "SELECT * FROM EMPLOYEES ORDER BY
EMPLOYEE_ID". Then, when the user requests that this cursor scroll to the
next set of rows the cursor would execute the query "SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID > (?) ORDER BY EMPLOYEE_ID" where it would pass as a
parameter the EMPLOYEE_ID from the last row in the current buffer. This
kind of cursor actually creates multiple result sets due to the re-execution
of the query every time a scroll operation is performed. As a result, the
cursor does not establish a consistent order for the records in the sense
that the "second" record in the result set might change its value and
position if we scroll forward and then attempt to scroll back to this
- Keyset driven cursor type.
o When the cursor is opened, it immediately stores a set of unique
keys for all rows in the result set. Later, when the user attempts a scroll
operation, the cursor simply retrieves rows corresponding to the previously
stored keys. This means that updates by other applications are detected
with a new value for the row (unless the update changes the key - if another
application changes the key this is equivalent to a delete and then an
insert as far as a keyset cursor is concerned). Deleted records show up as
"holes" with a special status flag. As for inserts made by other
applications, these are not detected since these inserted rows did not
appear in the original set of keys that was fetched when the cursor was
first opened. This cursor does establish a consistent order for the records
in the sense that if we scroll forwards and backwards the same records will
appear in the same position, although they may be marked as having been
deleted and may contain updated values.
- Mixed cursor.
o This is a combination of a keyset driven cursor and a dynamic
cursor. This kind of cursor is typically used when the table is too large
to make it practical to save keys for all the rows in the result set. In
this case, the cursor fetches keys for some subset of the rows. Inside this
subset, the cursor behaves like a keyset driven cursor. Outside this
subset, the cursor behaves like a dynamic cursor. A mixed cursor is
equivalent to a keyset-driven cursor when the size of the key subset is
equal to the result set size. A mixed cursor is equivalent to a dynamic
cursor when the size of the key subset is equal to 1.
Here we summarize some key properties of certain C++ standard iterators.
For this discussion we are really interested in only three types of
iterators: input iterators, output iterators and random access iterators.
Input and output iterators are considered primarily because these have the
smallest set of requirements of all the iterator types and so can be mapped
most easily to an ODBC cursor. Random access iterators are interesting
since these have the largest set of requirements and correspond most closely
to the type of usage that is made of scrollable ODBC cursors. Here we
summarize only the iterator properties that are important to our discussion
(for full details on the iterator requirements see section 24.1 of the C++
Noteworthy Input Iterator Requirements
1.. Input iterators are assignable and copy constructable.
2.. Let a, b be input iterators. If a==b and (a,b) is in the domain of ==
then *a is equivalent to *b.
3.. Let r be an input iterator. If we increment r via ++r, then after the
increment any copies of the previous value of r are no longer required to be
dereferencable or in the domain of ==.
4.. For input iterators a==b does not imply ++a==++b. Algorithms on input
iterators should never attempt to pass through the same iterator twice.
They should be single pass algorithms.
5.. Let r be an input iterator. The result of *r must be convertible to
the value type of the iterator.
- Requirement two that the dereferenced value of a and b be
'equivalent' is vague. Standard is not clear on what 'equivalent' means,
but it does not mean these two must have the same value.
- Requirement three means that conceptually when we implement an
input iterator we are allowed to code it such that only one copy of the
iterator can be "active". The reason for this is that once one copy of the
iterator has been incremented other copies become invalid.
Noteworthy Output Iterator Requirements
1.. Output iterators are assignable and copy constructable.
2.. Let r be an output iterator. The only valid use of operator * is on
the left hand side of an assignment. After an assignment, the iterator, r,
must be incremented before it can be assigned to again. Algorithms on output
iterators should be single pass algorithms.
- Typically operator * on an output iterator is defined to return a
proxy class that performs a write of the data upon assignment. This proxy
might be the iterator itself or might be some other class.
How DTL Abstracts ODBC cursors as iterators.
SELECT statement, forward only cursor: Input Iterator
- In order to allow multiple copies of the iterator to be active at
the same time, we chose a design wherein each iterator holds its own ODBC
cursor. This allows us to have logic like a=b; ++a; ++b; and have both
iterators a and b be able to return a value. In fact, we did not have to
do this since the standard allows only one copy of the iterator to be
active. So, under the standard it would be allowable for b to become invalid
once a had been incremented.
- Note that we are taking full advantage of the standard in
performing this mapping. Records are not guaranteed to come back in any
particular order, nor are two copies of an iterator guaranteed to retrieve
the same set of records due the fact that inserts, updates and deletes may
be performed on the set of records the select statement is operating
INSERT, UPDATE, DELETE statement, forward only cursor: Output Iterator
- This is fairly straightforward. The only trick here is that the
execute of the statement must happen on assignment. Otherwise, loops that
use post-increment logic to write to the iterator will not behave correctly.
STORED PROCEDURE, forward only cursor: Input + Output Iterator
- Mapping stored procedures to C++ iterators was less
straightforward since stored procedures perform both input and output.
- Stored procedures accept arguments as input, and in this way act
as an output iterator.
- Stored procedures can return results from a query and can return
results via its arguments so in this way it acts as an input iterator. What
is more, stored procedures can return more than one result set which means
the resulting iterator may need to act over more than one range. Therefore,
we added a MoreResults() method to the sql_iterator object so that the
iterator can pass through multiple result sets that might be returned by
- To split the input and output for stored procedures we have
sql_iterators hold two buffers. One buffer we called the data object
(DataObj) which stores the result sets that come back from the query. The
second buffer we call the parameter object (ParamObj) which is designed to
hold parameters that are passed to the query.
Boost list run by bdawes at acm.org, gregod at cs.rpi.edu, cpdaniel at pacbell.net, john at johnmaddock.co.uk