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@franklin.oit.unc.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.