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
2000-05-16