New C++ API for SQL

Hi,

For the past 2 months now I have been working on an a C++ API for
Mysql. In the process of doing this I realized that I was designing
something that could be very use for any SQL database, not just Mysql.

So I decided to extend the scope of my API to include other SQL
Database. See the my next post for how I plan on doing this.

If you are interested please subscribe to mysql++ by sending an email to
mysql-plusplus(at)franklin(dot)oit(dot)unc(dot)edu with any subject and body. My API's
web page is http://sunsite.unc.edu/kevina/mysql++/ and it includes a
link to the web interface for the mysql++ mailing list.

Here is an overview of my planned interface. There is a snapshot
release in the really new area at my API home page if you want to look
at almost newest code.

Like working with most other SQL API the process for executing
queries is the same. 1) You open the connection, 2) You form and
execute the queries, 3) You iterate through the result set. It
not much different in my C++ API. However there is a lot of
extra functionality along each step of the way.

The Main Database Handle

This is a class that handles the connection to the Mysql server.
You always need at least one of these objects to do anything. It
can either create a separate queries object or directly execute
queries. The separate query object is the recommended way as it
gives you far more power.

The Query Object

This object is the recommended way of executing queries. It is
subclassed from strstream which means you can write to it like
any other stream to aid in the formation of queries.

You can also set up Template queries with this class. Template
queries are a way of setting up queries with replaceable
parameters that you can change throughout your program.

You can also use specialized structures and even the dramatic
result sets to aid in creating queries however more on that
latter.

The Query object returns an object with information about the
success of a query for non-select queries (queries that don't
return a result set).

The Result Sets

For queries that return a result set you have essentially two
different ways of handling the results: in a dramatic result
set, or in a static one.

The Dramatic Result Set

The Dramatic Result set is a result set in which the names of
the columns and the type of information of the columns does not
need to be determined at compile time. The result set can be
completely constant in which the data is returned to you in a
constant string link class, semi-constant in which you can
modify the data one row at a time, or a trully mutable in which
in you can modify the data in any way you like.

The constant result set is a result set that is closely bound to
the result set in the C API and is the one that provides the
most functionality. With this result set you can find out
detailed information about the type of information stored in
each of the columns. This is also the fastest because the data
does not need to be copied at all.

The semi-constant result set is like the constant result set
except you can modify the data one row at a time. The data you
modify is actually a copy of the data returned by the server.
This means that modifying the data does not change the actual
result set at all.

The semi-constant result set is actually the same thing as the
constant result set. The only difference is that when you
request a row from the result set you specificly declare the row
as a mutable one. This means that you can get some rows back as
constant rows and others as mutable ones.

The truly mutable result set is a result set similar to the
constant one except that the data is truly mutable in the sense
that you can change the data in the actual result set. However
unlike the first one this result set is not bound to the C API
result set. Instead it containes a copy of the data returned by
the C API in a two-dimensional vector. Because of this the
detailed information about each of the columns is not currently
available, only the column names and the C++ type that most
closly mataches the orognal SQL type. Also, becuase it makes a
copy of the data returned from the C API, there is a little bit
of performance penalty to using this one.

The rows in all the dramatic result sets are very close to an
STL random access container. This means that they have an
iterator which can be used for STL algorithms. There is even
couple of specialized utility function to aid in the use of the
result sets in STL algorithms.

The columns in all the dramatic result are also very close to an
STL random access container. However, in addition to accessing
the columns by there index number you can also access the
columns via there field names.

In addition, becuase both the rows and the columns are STL like
containers, you can also treat the result set as a two-
dementional array. For example you can get the 5th item on the
2nd row by simply saying result[2][5]. Because you can also use
the field names you can substitute the column number by a field
name and say result[2]["price"] to get "price" of the item on
the 2nd row, for example.

The actual data that all the dramatic result sets return is
stored in a special string like class that has some additional
magic too it. The magic is that the column data will
automatically convert itself into all of the basic data types as
well as some additional types types that are designed to handle
mysql types which includes types for handling dates, times,
sets, and types with a null value. If there is a problem in the
conversion it will either set a warning flag or throw an
exception depending on how it is configured.

The drastic result sets can even be used to help form queries
with the help of some additional method. There is a method for
returns: 1) A comma separated list of the data (for example:
1.5, 10, "Dog, "Brown"), 2) A comma separated list of the field
names (for example: age, weight, what, color), and 3) An equal
list (for example: age = 1.5 AND weight = 10 AND what = "Dog"
AND color = "Brown").

Mutable result sets can be created with out an actual query so
that you can take advantage of these methods to aid in inserting
data into the database with out having to first create an
unnesasery query.

The Static Result Sets

The results from an query can also be stored statically in what
I call a specialized SQL structure. These structures are then
stored in some STL container such a vector or list, or even a
set or multi-set as the the specialized structures can also be
made less-than-comparable. Unlike the dramtic result sets it is
assumed that the programmer knows what the result set is going
to look like. Because of this all the information about the
columns, including the names, are lost.

These Specialized Structures are are exactly that C++ `structs'.
Each member item is stored with a unique name within the
structure. You can in no way use STL algorithms or anything else
STL to work with the individual elements of the structures.
However naturally because these structures are then stored in
STL containers you can use STL algorithms on the containers of
these structures. The containers represent the rows, and the
individual elements of the structure represent the columns. For
example you can access the item named "price" on the second row
by saying result[2].price. With the dramatic result set you
would have probably needed to say result[2]["price"] to
accomplish the same result.

If there is a problem in converting from the result set returned
by the server to the specialized structures the API will either
set a warning flag or throw an exception, depending on how it is
configured.

To aid in the creating of queries using these specialized
structures, the same query aiding methods are available to use
that are available for the dramatic result sets. This includes
methods for returning a comma separated list of the data, a
comma separated list of the field names, and an equal list.

In addition

In addition to the material mentuned there are also many generic
classes that can be used with other programs. Examples of this
include a special const string class, a const random access
adapter that will make a random access container out of a class
with nothing but the size() method and the subscript ([])
operator defined and a generic SQL query class that can be used
any SQL C or C++ API.