5. Specialized SQL Structures
Prev |  | Next

## 5. Specialized SQL Structures

The Specialized SQL Structure (SSQLS) feature lets you easily define C++ structures that match the form of your SQL tables. Because of the extra functionality that this feature builds into these structures, MySQL++ can populate them automatically when retrieving data from the database; with queries returning many records, you can ask MySQL++ to populate an STL container of your SSQLS records with the results. When updating the database, MySQL++ can use SSQLS structures to match existing data, and it can insert SSQLS structures directly into the database.

You define an SSQLS using one of several macros. (These are in the file custom.h, and in the file that it includes, custom-macros.h.) There are a bunch of different macros, for different purposes. The following sections will discuss each macro type separately, beginning with the easiest and most generally useful.

### 5.1. sql_create

This is the most basic sort of SSQLS declaration:

    sql_create_5(stock, 0, 0,
        string, item,
        int, num,
        double, weight,
        double, price,
        mysqlpp::Date, date)

This creates a C++ structure called 'stock' containing five member variables, along with some constructors and other member functions useful with MySQL++.

One of the generated constructors takes a reference to a mysqlpp::Row object, allowing you to easily populate a vector of stocks like so:

    vector<stock> result;
    query.storein(result);

That's all there is to it. The only requirements are that the table structure be compatible with the SSQLS's member variables, and that the fields are in the same order.

The general format of this set of macros is:

    sql_create_#(NAME, KEYS, INITPARMS, TYPE1, ITEM1, ... TYPE#, ITEM#)

Where # is the number of member variables, NAME is the name of the structure you wish to create, TYPEx is the type name for a member variable, and ITEMx is that variable's name.

The KEYS and INITPARMS arguments can always be zero, to keep things simple. We will discuss what happens if you use different values in the next few sections.

### 5.2. sql_create with Compare

SSQLS structures can also have member functions that allow you to compare one structure to another. You simply change the first 0 in the previous example (KEYS) to a higher value. If this number is N, then two structures are considered equal if the first N members of each are equal.

For example:

    sql_create_5(stock, 1, 0,
        string, item,
        int, num,
        double, weight,
        double, price,
        mysqlpp::Date, date)

Here we are saying that the 'item' field is a kind of key field: it is always unique between any two 'stock' items, so if two stock records have equal item values, they are the same stock item.

That change adds the following members to the SSQLS:

    struct stock {
        ...
        stock(const std::string &p1);
        set(const std::string &p1);
        bool operator ==(const stock &other) const;
        bool operator !=(const stock &other) const;
        bool operator >(const stock &other) const;
        bool operator <(const stock &other) const;
        bool operator >=(const stock &other) const;
        bool operator <=(const stock &other) const;
        int cmp(const stock &other) const;
        int compare(const stock &other) const;
    }

    int compare(const stock &x, const stock &y);

The global `compare()` function compares x to y and returns <0 if x < y, 0 if x = y, and >0 if x > y. `stock::cmp()` and `stock::compare()` are the same thing as `compare(*this, other)`.

The additional constructor initializes the key fields of the structure and leaves the other member variables undefined. This is useful for creating temporary objects to use for comparisons like `x <= stock("Hotdog")`.

Because stock is now less-than-comparable you can store the query results in an STL associative container:

    std::set<stock> result;
    query.storein(result);

And you can now use it like any other set:

    cout << result.lower_bound(stock("Hamburger"))->item << endl;

This will return the first item that begins with "Hamburger".

You can also use it will any STL algorithm that require the values to be less-than-comparable.

### 5.3. sql_create with Additional Initializers

If third parameter for this macro (INITPARMS) is nonzero, the SSQLS will have two additional members functions that make it easier to initialize the structure's data members. For example:

    sql_create_5(stock, 1, 5,
        string, item,
        int, num,
        double, weight,
        double, price,
        mysqlpp::Date, date)

will add these functions to the structure relative to that in the previous example:

    struct stock {
        ...
        stock(const string&, const int&, const double&,
                const double&, const mysqlpp::Date&);
        void set(const string&, const int&, const double&,
                const double&, const mysqlpp::Date&);
    }

There is one trick with this: because each SSQLS has at least one other constructor besides the one defined by this feature, not every logical value for INITPARMS results in working code. A simple example is setting KEYS and INITPARMS to the same value: you get two identical constructor definitions, so the compiler refuses to compile the code. If you are getting compiler errors having to do with duplicate definitions, try changing this value to zero.

### 5.4. An Important Limitation of sql_create

The features described in the two previous sections work together nicely most of the time. However, if you try to use the same value for the second and third parameters to `sql_create_N`, your program will fail to compile.

Why is this?

The second parameter sets up SSQLS comparisons, one aspect of which defines a constructor taking just the table's key fields. The third parameter sets up an initialization constructor, taking as many fields as you request. When these two values are equal, you get two identical constructor definitions, which is illegal in C++.

The solution is to use 0 for the third parameter, indicating that you do not need a separate full-initialization constructor. `examples/custom6.cpp` illustrates this:

    #include "util.h"

    #include <mysql++.h>
    #include <custom.h>

    #include <iostream>
    #include <iomanip>
    #include <vector>

    using namespace std;

    // To store a subset of a row, we define an SSQLS containing just the
    // fields that we will store.  There are complications here that are
    // covered in the user manual.
    sql_create_1(stock_subset,
                 1, 0,
                 string, item)

    int
    main(int argc, char *argv[])
    {
        try {
            // Establish the connection to the database server.
            mysqlpp::Connection con(mysqlpp::use_exceptions);
            if (!connect_to_db(argc, argv, con)) {
                return 1;
            }

            // Retrieve a subset of the stock table, and store the data in
            // a vector of 'stock_subset' SSQLS structures.
            mysqlpp::Query query = con.query();
            query << "select item from stock";
            vector<stock_subset> res;
            query.storein(res);

            // Display the result set
            cout << "We have:" << endl;
            vector<stock_subset>::iterator it;
            for (it = res.begin(); it != res.end(); ++it) {
                cout << '\t' << it->item << endl;
            }
        }
        catch (const mysqlpp::BadQuery& er) {
            // Handle any query errors
            cerr << "Query error: " << er.what() << endl;
            return -1;
        }
        catch (const mysqlpp::BadConversion& er) {
            // Handle bad conversions; e.g. type mismatch populating 'stock'
            cerr << "Conversion error: " << er.what() << endl <<
                    "\tretrieved data size: " << er.retrieved <<
                    ", actual size: " << er.actual_size << endl;
            return -1;
        }
        catch (const mysqlpp::Exception& er) {
            // Catch-all for any other MySQL++ exceptions
            cerr << "Error: " << er.what() << endl;
            return -1;
        }

        return 0;
    }

This example shows one other thing, which is how to retrieve a subset of a table using SSQLS. Because we wanted only one column from the table, we had to pass 0 for the third parameter to `sql_create_N` to get the code to compile.

(See the simple1 example in the Tutorial for another way to accomplish the same thing.)

### 5.5. Additional Features of Specialized SQL Structures

Up to this point, we haven't been using all of the features in the SSQLS structures we've been generating. We could have used the `sql_create_basic_*` macros instead, which would have worked just as well for what we've seen so far, and the generated code would have been smaller.

Why is it worth ignoring the "basic" variants of these macros, then? Consider this:

    query.insert(s);

This does exactly what you think it does: it inserts 's' into the database. This is possible because a standard SSQLS has functions that the query object can call to get the list of fields and such, which it uses to build an insert query. `query::update()` and `query::replace()` also rely on this SSQLS feature. A basic SSQLS lacks these functions.

Another feature of standard SSQLSes you might find a use for is changing the table name used in queries. By default, the table in the MySQL database is assumed to have the same name as the SSQLS structure type. But if this is inconvenient, you can globally change the table name used in queries like this:

    stock::table() = "MyStockData";

### 5.6. Harnessing SSQLS Internals

Continuing the discussion in the previous section, there is a further set of methods that the non-"basic" versions of the `sql_create` macros define for each SSQLS. These methods are mostly for use within the library, but some of them are useful enough that you might want to harness them for your own ends. Here is some pseudocode showing how the most useful of these methods would be defined for the `stock` structure used in all the `custom*.cpp` examples:

    // Basic form
    template <class Manip>
    stock_value_list<Manip> value_list(cchar *d = ",",
        Manip m = mysqlpp::quote) const;

    template <class Manip>
    stock_field_list<Manip> field_list(cchar *d = ",",
        Manip m = mysqlpp::do_nothing) const;

    template <class Manip>
    stock_equal_list<Manip> equal_list(cchar *d = ",",
        cchar *e = " = ", Manip m = mysqlpp::quote) const;

    // Boolean argument form
    template <class Manip>
    stock_cus_value_list<Manip> value_list([cchar *d, [Manip m,] ]
        bool i1, bool i2 = false, ... , bool i5 = false) const;

    // List form
    template <class Manip>
    stock_cus_value_list<Manip> value_list([cchar *d, [Manip m,] ]
        stock_enum i1, stock_enum i2 = stock_NULL, ...,
        stock_enum i5 = stock_NULL) const;

    // Vector form
    template <class Manip>
    stock_cus_value_list<Manip> value_list([cchar *d, [Manip m,] ]
        vector<bool> *i) const;

    ...Plus the obvious equivalents for field_list() and equal_list()

Rather than try to learn what all of these methods do at once, let's ease into the subject. Consider this code:

    stock s("Dinner Rolls", 75, 0.95, 0.97, "1998-05-25");
    cout << "Value list: " << s.value_list() << endl;
    cout << "Field list: " << s.field_list() << endl;
    cout << "Equal list: " << s.equal_list() << endl;

That would produce something like:

    Value list: 'Dinner Rolls',75,0.95,0.97,'1998-05-25'
    Field list: item,num,weight,price,date
    Equal list: item = 'Dinner Rolls',num = 75,weight = 0.95, price = 0.97,date = '1998-05-25'

That is, a "value list" is a list of data member values within a particular SSQLS instance, a "field list" is a list of the fields (columns) within that SSQLS, and an "equal list" is a list in the form of an SQL equals clause.

Just knowing that much, it shouldn't surprise you to learn that `Query::insert()` is implemented more or less like this:

    *this << "INSERT INTO " << v.table() << " (" << v.field_list() <<
            ") VALUES (" << v.value_list() << ")";

where 'v' is the SSQLS you're asking the Query object to insert into the database.

Now let's look at a complete example, which uses one of the more complicated forms of `equal_list()`. This example builds a query with fewer hard-coded strings than the most obvious technique requires, which makes it more robust in the face of change. Here is `examples/custom5.cpp`:

    #include "util.h"

    #include <mysql++.h>
    #include <custom.h>

    #include <iostream>
    #include <iomanip>
    #include <vector>

    using namespace std;

    sql_create_5(stock,
                 1, 5,
                 string, item,
                 mysqlpp::longlong, num,
                 double, weight,
                 double, price,
                 mysqlpp::Date, sdate)

    int
    main(int argc, char *argv[])
    {
        try {
            mysqlpp::Connection con(mysqlpp::use_exceptions);
            if (!connect_to_db(argc, argv, con)) {
                return 1;
            }

            // Get all the rows in the stock table.
            mysqlpp::Query query = con.query();
            query << "select * from stock";
            vector<stock> res;
            query.storein(res);

            if (res.size() > 0) {
                // Build a select query using the data from the first row
                // returned by our previous query.
                query.reset();
                query << "select * from stock where " <<
                    res[0].equal_list(" and ", stock_weight, stock_price);

                // Display the finished query.
                cout << "Custom query:\n" << query.preview() << endl;
            }
        }
        catch (const mysqlpp::BadQuery& er) {
            // Handle any query errors
            cerr << "Query error: " << er.what() << endl;
            return -1;
        }
        catch (const mysqlpp::BadConversion& er) {
            // Handle bad conversions
            cerr << "Conversion error: " << er.what() << endl <<
                    "\tretrieved data size: " << er.retrieved <<
                    ", actual size: " << er.actual_size << endl;
            return -1;
        }
        catch (const mysqlpp::Exception& er) {
            // Catch-all for any other MySQL++ exceptions
            cerr << "Error: " << er.what() << endl;
            return -1;
        }

        return 0;
    }

This example uses the list form of `equal_list()`. The arguments `stock_weight` and `stock_price` are enum values equal to the position of these columns within the `stock` table. `sql_create_`x generates this enum for you automatically.

The boolean argument form of that `equal_list()` call would look like this:

    query << "select * from stock where " <<
            res[0].equal_list(" and ", false, false, true, true, false);

It's a little more verbose, as you can see. And if you want to get really complicated, use the vector form:

    vector<bool> v(5, false);
    v[stock_weight] = true;
    v[stock_price] = true;
    query << "select * from stock where " <<
            res[0].equal_list(" and ", v);

This form makes the most sense if you are building many other queries, and so can re-use that vector object.

Many of these methods accept manipulators and custom delimiters. The defaults are suitable for building SQL queries, but if you're using these methods in a different context, you may need to override these defaults. For instance, you could use these methods to dump data to a text file using different delimiters and quoting rules than SQL.

At this point, we've seen all the major aspects of the SSQLS feature. The final sections of this chapter look at some of the peripheral aspects.

### 5.7. Alternate Creation Methods

If for some reason you want your SSQLS data members to have different names than used in the MySQL database, you can do so like this:

    sql_create_c_names_5(stock, 1, 5,
        string, item, "item",
        int, num, "quantity",
        double, weight, "weight",
        double, price, "price"
        mysqlpp::Date, date, "shipment")

If you want your SSQLS to have its data members in a different order from those in the MySQL table, you can do it like this:

    sql_create_c_order_5(stock, 2, 5,
        mysqlpp::Date, date, 5,
        double, price, 4,
        string, item, 1,
        int, num, 2,
        double, weight, 3)

You can combine the custom names and custom ordering like this:

    sql_create_complete_5(stock, 2, 5,
        mysqlpp::date, date, "shipment", 5,
        double, price, "price", 4,
        string, item, "item", 1,
        int, num, "quantity", 2,
        double, weight, "weight", 3)

All three of these macro types have "basic" variants that work the same way. Again, basic SSQLSes lack the features necessary for automatic insert, update and replace query creation.

### 5.8. Expanding SSQLS Macros

If you ever need to see the code that a given SSQLS declaration expands out to, use the utility `doc/ssqls-pretty`, like so:

    ssqls-pretty < myprog.cpp |less

This locates the first SSQLS declaration in that file and uses the C++ preprocessor to expand that macro. You may have to change the script to tell it where your MySQL++ header files are.

### 5.9. Extending the SSQLS Mechanism

The SSQLS headers — custom.h and custom-macros.h — are automatically generated by the Perl script custom.pl. Although it is possible to change this script to get additional functionality, it's usually better to do that through inheritance.

A regular user may find it helpful to change the the limit on the maximum number of SSQLS data members allowed. It's 25 out of the box. A smaller value may speed up compile time, or you may require a higher value because you have more complex tables than that. Simply change the `max_data_members` variable at the top of custom.pl and say 'make'. The limit for Visual C++ is 31, according to one report. There doesn't seem to be a practical limit with GCC 3.3 at least: I set the limit to 100 and the only thing that happened is that custom-macros.h went from 1.3 MB to 18 MB and the build time for `examples/custom.*` got a lot longer.

Prev | Up | Next
4. Template Queries | Home | 6. Using Unicode with MySQL++