#[1]next [2]previous [3]up [4]next
   
   [5]next [6]up [7]previous [8]contents 
   Next: [9]8. Long Names Up: [10]Usage Previous: [11]6. Template Queries
     [12]Contents
   Subsections
     * [13]7.1 sql_create_basic 
     * [14]7.2 sql_create_basic with compare 
     * [15]7.3 sql_create_basic with Additional Constructor 
     * [16]7.4 sql_create_basic General Format 
     * [17]7.5 sql_create_basic_c_order 
     * [18]7.6 sql_create_basic_c_order General Format 
     * [19]7.7 sql_create 
     * [20]7.8 sql_create_c_names 
     * [21]7.9 sql_create_c_names General Format 
     * [22]7.10 sql_create_c_order 
     * [23]7.11 sql_create_complete 
     * [24]7.12 Changing the table name 
     * [25]7.13 Seeing the actual code 
     * [26]7.14 Adding functionality 
     * [27]7.15 Other notes 
     _________________________________________________________________
   
                         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.)
   
                             7.1 sql_create_basic
                                       
   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..
   
                       7.2 sql_create_basic with compare
                                       
   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.
   
               7.3 sql_create_basic with Additional Constructor
                                       
   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&);
          }
          
                      7.4 sql_create_basic General Format
                                       
   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..
       
                         7.5 sql_create_basic_c_order
                                       
   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, ord
          er
                                     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...
   
                  7.6 sql_create_basic_c_order General Format
                                       
   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...
       
                                7.7 sql_create
                                       
   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) con
          st;
            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 for
          m
            stock_cus_value_list<Manip> value_list([cchar *d, [Manip m,]
          ]
                                                   bool i1,
                                                   bool i2 = false, ... ,
          
                                                   bool i5 = false) const
          ;
            template <class Manip>                            // list for
          m
            stock_cus_value_list<Manip> value_list([cchar *d, [Manip m,]
          ]
                                                     stock_enum i1,
                                                     stock_enum i2 = stoc
          k_NULL, ...,
                                                     stock_enum i5 = stoc
          k_NULL) const;
            template <class Manip>                            // vector f
          orm
            stock_cus_value_list<Manip> value_list([cchar *d, [Manip m,]
          ]
                                                     vector<bool> *i) con
          st;
          
            ...(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;   //li
          st 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] = fa
          lse;
          query << "SELECT * FROM stock WHERE " << q.equal_list(" AND ",
          a);
          
   will produce the same query as in the above example.
   
                            7.8 sql_create_c_names
                                       
   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'
          
                     7.9 sql_create_c_names General Format
                                       
   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.
   
                            7.10 sql_create_c_order
                                       
   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.
   
                           7.11 sql_create_complete
                                       
   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.
   
                         7.12 Changing the table name
                                       
   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.
   
                          7.13 Seeing the actual code
                                       
   To see the actual code that the macro inserts use sql++pretty. For
   example:
   
   sql++pretty < test.cc | less
          
                           7.14 Adding functionality
                                       
   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.
   
                               7.15 Other notes
                                       
   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.
     _________________________________________________________________
   
   [28]next [29]up [30]previous [31]contents 
   Next: [32]8. Long Names Up: [33]Usage Previous: [34]6. Template
   Queries   [35]Contents
   
   
    2001-05-01

References

   1. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/8_Long.html
   2. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/6_Template.html
   3. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Usage.html
   4. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/8_Long.html
   5. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/8_Long.html
   6. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Usage.html
   7. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/6_Template.html
   8. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Contents.html
   9. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/8_Long.html
  10. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Usage.html
  11. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/6_Template.html
  12. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Contents.html
  13. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03410000000000000000
  14. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03420000000000000000
  15. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03430000000000000000
  16. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03440000000000000000
  17. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03450000000000000000
  18. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03460000000000000000
  19. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03470000000000000000
  20. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03480000000000000000
  21. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION03490000000000000000
  22. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION034100000000000000000
  23. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION034110000000000000000
  24. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION034120000000000000000
  25. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION034130000000000000000
  26. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION034140000000000000000
  27. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/7_Specialized.html#SECTION034150000000000000000
  28. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/8_Long.html
  29. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Usage.html
  30. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/6_Template.html
  31. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Contents.html
  32. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/8_Long.html
  33. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Usage.html
  34. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/6_Template.html
  35. file://localhost/home/Sinisa/mysql++-1.7/doc/man-html/Contents.html