Next: 8. Long Names
Up: Usage
Previous: 6. Template Queries
Contents
Subsections
7. Specialized SQL Structures
The Specialized SQL Structures (SSQLS) allows you create structures
to hold data for mysql queries with extra functionality to make your
life easier. These structures are in no way related to any Standard
Template Library (STL) type of containers. These structures are exactly
that structs. Each member item is stored with a unique
name within the structure. You can in no way use STL algorithms are
anything else STL to work with the individual structures. However
you CAN use these structures as the value_type for STL containers.
(They would be pretty useless if you couldn't.)
The following command will create a basic mysql query for use with
the sample database.
-
- sql_create_basic_5(stock, 0, 0,
string, item, // type, id,
int, num,
double, weight,
double, price,
MysqlDate, date)
This will set up the following structure:
-
- struct stock {
stock () {}
stock (const MysqlRow &row);
set (const MysqlRow &row);
string item;
int num;
double weight;
double price;
MysqlDate date;
};
As you can see this is nothing fancy. The main advantage of this
simple structure is the stock (MysqlRow &row) constructor
which allows you to easily populate a vector of stocks like so:
-
- vector<stock> result;
query.storein(result);
That's all there is two it. The requirements are that the query returns
elements in the same order as you specified them in the custom structure.
The general format is:
-
- sql_create_basic_#(NAME, 0, 0, TYPE1, ITEM1, ... TYPE#, ITEM#)
Where # is the number of valuables in the vector, NAME is the name
of the structure you wish to create, and TYPE1 is the type name for
first item and ITEM1 is the valuables name for the first item etc..
You can also make the structure comparable by changing the first 0
in the previous example to a non zero number. This number, lets call
it n, will tell c++ that if the first n number or the same then the
two structures are the same.
For example:
-
- sql_create_basic_5(stock, 1, 0,
string, item, // type, id,
int, num,
double, weight,
double, price,
MysqlDate, date)
will create a structure where only the item valuable is checked to
see if two different stocks are the same. It also allows you to compare
one structure to another based on the value of item. (If n is greater
than one it will compare the structures in a Lexicographic order.
For example if it was 2 it would first compare item and
if item was the same it would then compare num. If num
was the same it would declare the two structures the same.)
In addition what the previous example defines it also defines the
following:
-
- struct stock
...
stock (const string &p1);
set (const 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);
int compare (const stock &x, const stock &y) compares x to y and
return <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).
stock::stock is a constructor that will set item to p1 and leave the
other variables undefined. This is useful for creating temporary
objects to use for comparisons like x <= stock("Hotdog").
Because stock is now less-then-comparable you can store the
query results in a set:
-
- set<stock> result;
query.storein(result);
And you can now use it like any other set, for example:
-
- cout << result.lower_bound(stock("Hamburger"))->item << endl;
will return the first item that begins with Hamburger.
You can also now use it will any STL algorithm that require the values
to be less-then-comparable.
The general format so far is:
-
- sql_create_base_#(NAME, CMP, 0, TYPE1, ITEM1, ... TYPE#, ITEM#)
where CMP is that the number that tells c++ that if the first cmp
variables are the same then the two structures are the same.
The last zero in the last example if for creating another constructor.
Let this zero be m then it will create a constructor which will populate
the first n variables. For example:
-
- sql_create_basic_5(stock, 1, 5,
string, item, // type, id,
int, num,
double, weight,
double, price,
MysqlDate, date)
will also define:
-
- struct stock {
...
stock(const string&, const int&, const double&,
const double&, const MysqlDate&);
set(const string&, const int&, const double&,
const double&, const MysqlDate&);
}
Thus the general format for sql_create_basic is
-
- sql_create_basic_#(NAME, CMP, CNST, TYPE1, ITEM1, ..., TYPE#, ITEM#)
Where:
- # is the number of valuables in the vector
- NAME is the name of the structure you wish to create
- CMP is the number that tells c++, if not set to 0, that if the first
cmp variables are the same then the two structures are the same.
- CNST is the number, if not set to 0, that will create a constructor
which will populate the first n variables.
- TYPE1 is the type name for first item and ITEM1 is the valuables name
for the first item etc..
You can also specify an alternate order for when mysql populates the
structure. For example:
-
- sql_create_basic_c_order_5(stock, 2, 5,
MysqlDate, date, 5, // type, id, order
double, price, 4,
string, item, 1,
int, num, 2,
double, weight, 3)
This will create a similar structure as in the previous example except
that that the order of the data items will be different and c++ will
use the first two items to compare with (date, price). However because
a custom order is specified you can use the same query to populate
the set. It will fill date with the first 5th item of the
query result set, price with the 4th, etc...
Thus the general format for sql_create_basic is
-
- sql_create_basic_c_order_# (NAME, CMP, CNST,
TYPE1, ITEM1, ORDER1,
...
TYPE#, ITEM#, ORDER#)
Where:
- # is the number of valuables in the vector
- NAME is the name of the structure you wish to create
- CMP is the number that tells c++, if not set to 0, that if the first
cmp variables are the same then the two structures are the same.
- CNST is the number, if not set to 0, that will create a constructor
which will populate the first n variables.
- TYPE1 is the type name for first item, ITEM1 is the valuable name
for the first item, ORDER1 is the order number for the first item
...etc...
In addition to the basic structures you can set up enhanced structures
that also have methods defined to aid in the creation of queries
and in the insertion of data in tables.
For example:
-
- sql_create_5(stock, 1, 5,
string, item, // type, id,
int, num,
double, weight,
double, price,
MysqlDate, date)
which will, in addition to that which is defined in sql_create_basic
with Additional Constructor, define the equivalent to:
-
- struct stock {
...
static char *names[];
static char *table;
template <class Manip>
stock_value_list<Manip> value_list(cchar *d = ",", // basic form
Manip m = mysql_quote) const;
template <class Manip>
stock_field_list<Manip> field_list(cchar *d = ",",
Manip m = mysql_do_nothing) const;
template <class Manip>
stock_equal_list<Manip> equal_list(cchar *d = ",",
cchar *e = " = ",
Manip m = mysql_quote,
) const;
template <class Manip> // bool form
stock_cus_value_list<Manip> value_list([cchar *d, [Manip m,] ]
bool i1,
bool i2 = false, ... ,
bool i5 = false) const;
template <class Manip> // list form
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;
template <class Manip> // vector form
stock_cus_value_list<Manip> value_list([cchar *d, [Manip m,] ]
vector<bool> *i) const;
...(The logical equivalent for field_list and equal_list)...
};
value_list() returns a special class that when used with
the << operator with an ostream on the left will
return a comma separated list with values properly quoted and escaped
when needed.
field_list() return a special class than does the same thing
but returns a list of fields that the structure holds which in this
case is the same thing as the valuable names. The field names are
not escaped or quoted
equal_list() returns a comma separated list with the format
field name = value. The field name is not quoted or escaped
and value is escaped or quoted as needed.
For example:
-
- stock s("Dinner Roles",75,0.95,0.97,"1998-05-25");
cout << "Value List: " << s.comma_list() << endl;
cout << "Field List: " << s.field_list() << endl;
cout << "Equal List: " << s.equal_list() << endl;
Would return something like (with a little extra hand formating):
-
- Value List: 'Dinner Roles',75,0.95,0.97,'1998-05-25'
Field List: item,num,weight,price,date
Equal List: item = 'Dinner Roles',num = 75,weight = 0.95,
price = 0.97,date = '1998-05-25'
A combination of the field and value list can be used for insert or
replace queries. For example:
-
- query << "insert into stock (" << s.field_list() ") values "
<< s.value_list();
will insert s into table stock.
You can also use SQLQuery::insert or SQLQuery::replace (and thus Query::insert
or Query::replace) as a short cut to accomplish the same task like
so:
-
- query.insert(s);
It will use s.table for the table name which defaults to the name
of the structure.
You can also specify an different delimiter "d".
If none is specified it defaults to ",". With
this you can use the delimiter " AND " for equal_list
to aid in update and select queries. For example:
-
- stock s2 = s;
s2.item = "6 Dinner Roles";
query << "UPDATE TABLE stock SET " << s2.equal_list()
<< " WHERE " << s.equal_list(" AND ");
would produce the query:
-
- UPDATE TABLE stock SET item = '6 Dinner Roles',num = 75,weight = 0.95,
price = 0.97,date = '1998-05-25'
WHERE item = 'Dinner Roles' AND num = 75
AND weight = 0.95 AND price = 0.97
AND date = '1998-05-25'
which will change the entree in the table so that item is now "6
Dinner Roles" instead of "Dinner Roles"
You can use SQLQuery::update (and thus Query::update)
as a short cut to accomplishing the same task like so:
-
- stock s2 = s;
s2.item = "6 Dinner Roles";
query.update(s,s2);
Like SQLQuery::insert, it will use s.table for the table
name which defaults to the name of the structure.
You can also specify an different manipulator which will effect the
way c++ quotes or escapes the values. This may be any valid stream
manipulator that only effects the item to the right of manipulator.
value_list and equal_list defaults to escape
and field_list defaults to do_nothing. For equal_list
the manipulator only effects the value part and not the field
name part.
This can be useful creating exporting to a file where you don't want
quotes around strings for example.
-
- table_out << q.value_list("\ t", mysql_escape) << endl;
will append data to the file handle table_out.
The three non-basic forms allow you to specify which items are returned.
For example:
-
- cout << q.value_list(false,false,true,true,false) << endl; //bool form
cout << q.value_list(stock_weight, stock_price) << endl; //list form
will both return:
-
- 0.95,0.97
The bool form excepts boolean arguments where each true/false
represents an wether to show a valuable. False means not to show
it while true means to show it. If you leave of some they are assumed
to be false. For example:
-
- cout << q.value_list(false,false,true,true) << endl;
is the same as the above example.
The list form allows you to specify which items to show.
An enum values are created for each valuable with the name of struct
plus the underscore character prefixed before it. For example: item
becomes stock_item.
These forms can be useful is select queries. For example:
-
- query << "SELECT * FROM stock WHERE "
<< q.equal_list(" AND ",stock_weight,stock_price);
would produce the query:
-
- SELECT * FROM stock WHERE weight=0.95 AND price=0.97
which will select all rows from stock which have the same weight and
price as q.
The vector form (not shown above) allows you to pass a boolean
vector which is a time saver if you use the some pattern more than
once as it avoids having to create the vector from the arguments each
time. If a is a boolean vector then a[0] will
hold wether to include the first variable a[1] the second
etc... For example:
-
- vector<bool> a;
a[0] = false; a[1] = false; a[2] = true; a[3] = true; a[4] = false;
query << "SELECT * FROM stock WHERE " << q.equal_list(" AND ", a);
will produce the same query as in the above example.
You can also specify alternate field names like so:
-
- sql_create_c_names_5(stock, 1, 5,
string, item, "item", // type, id, column name
int, num, "quantity",
double, weight, "weight",
double, price, "price"
MysqlDate, date, "shipment")
When field_list or equal_list is used it will
use the given field names rather than the variable names for example:
-
- stock s("Dinner Roles",75,0.95,0.97,"1998-05-25");
cout << "Field List: " << s.field_list() << endl;
cout << "Equal List: " << s.equal_list() << endl;
Would return something like (with a little extra hand formating):
-
- Field List: item,quantity,weight,price,shipment
Equal List: item = 'Dinner Roles',quantity = 75,weight = 0.95,
price = 0.97,shipment = '1998-05-25'
The general format is:
-
- sql_create_c_names_# (NAME, CMP, CNST,
TYPE1, ITEM1, NAME1,
...
TYPE#, ITEM#, NAME#)
where NAME1 is the name of the first field, etc. Everything else
is the same as it is the same as in sql_create_basic_c_order General
Format.
As in sql_create_basic_c_order you may specify a custom order.
The general from is:
-
- sql_create_c_order_# (NAME, CMP, CNST,
TYPE1, ITEM1, ORDER1,
...
TYPE#, ITEM#, ORDER#)
where everything is the same as in sql_create_basic_c_order General
Format.
You can also specify both a custom order and custom field names. The
general from is.
-
- sql_create_complete_# (NAME, CMP, CNST,
TYPE1, ITEM1, NAME1, ORDER1,
...
TYPE#, ITEM#, NAME#, ORDER#)
Where everything is the same as in sql_create_c_order General
Format and sql_create_c_names General Format.
In order to avoid having even more forms we decided not to allow
you to specify a different table name in the actual macro call. The
table name is used by SQLQuery::insert, replace,
and update. However you can easeally change the default
table name, which is the same as the struct name, by changing the
reference NAME::table() returns to a different const
char * For example:
-
- stock::table() = "in_stock"
Will change the table name to "in_stock" in the
examples used through out this guide.
To see the actual code that the macro inserts use sql++pretty. For
example:
-
- sql++pretty < test.cc | less
The best way to add functionality is through inheritance. Even though
you could paste the code outputted from pretty.pl and modify it this
is not recommended because it won't reflect future enhancements.
Macros are defined for structures with up to 25 items. If you need
more modify the underlying perl script custom.pl. This perl script
is used to generate the header file. It in no way tries to parse C++
code.
The header file that the script custom.pl creates is close to a meg.
However, please note that the 1 meg header file (custom-macros.hh)
is NOTHING but macros. Therefor the compiler has to do very little
work when reading is.
Also, everything included by the macro call is done in such a way
that you can safely include the macro call in a header file and not
have to worry about duplicate function calls or anything of the like.
Next: 8. Long Names
Up: Usage
Previous: 6. Template Queries
Contents
2001-05-01