List:MySQL++« Previous MessageNext Message »
From:Rick Gutleber Date:November 7 2008 4:54pm
Subject:insertfrom( ) questions
View as plain text  
Warren:

I have a patch ready to submit, however, I have a couple of last minute 
questions:

1.  Does the new insertfrom( ) method and the InsertPolicy and SQLStream 
objects require anything new in the tests?  The tests are very concise; 
I don't know if they are meant to be exhaustive.

2.  Do I need to make additions to the user manual to reflect these new 
pieces?  Or is there someone else who handles documentation changes?

I attached the patch but if there is anything else I need to do, I will 
make those changes and resubmit.

Rick



 

Index: lib/manip.cpp
===================================================================
--- lib/manip.cpp	(revision 2396)
+++ lib/manip.cpp	(working copy)
@@ -28,6 +28,7 @@
 #include "manip.h"
 
 #include "query.h"
+#include "sqlstream.h"
 
 using namespace std;
 
@@ -54,45 +55,62 @@
 ostream&
 operator <<(quote_type1 o, const SQLTypeAdapter& in)
 {
-	Query* pq = dynamic_cast<Query*>(o.ostr);
+    Query* pq = dynamic_cast<Query*>(o.ostr);
 
-	if (pq && in.quote_q()) o.ostr->put('\'');
+    // If it's not a Query*, maybe it's a SQLStream*.
+    SQLStream* psqls = pq ? NULL : dynamic_cast<SQLStream*>(o.ostr);
 
-	if (pq) {
-		// It's a Query stream, so we'll be using unformatted output.
-		// Now, is escaping appropriate for source data type of 'in'?
-		if (in.escape_q()) {
-			string escaped;
-			pq->escape_string(&escaped, in.data(), in.length());
-			o.ostr->write(escaped.data(), escaped.length());
-		}
-		else {
-			o.ostr->write(in.data(), in.length());
-		}
-	}
-	else {
-		// Some other stream type, so use formatted output.  User
-		// shouldn't be trying to use the quote manipulator, but
-		// that's no reason to break their formatting.
-		*o.ostr << string(in.data(), in.length());
-	}
+    // If it's a Query or a SQLStream, we'll be using unformatted output.
+    if (pq || psqls) {
+        if (in.quote_q()) o.ostr->put('\'');
 
-	if (pq && in.quote_q()) o.ostr->put('\'');
+        // Now, is escaping appropriate for source data type of 'in'?
+        if (in.escape_q()) {
+            string escaped;
 
-	return *o.ostr;
+            // If it's not a Query*, then it has to be a SQLStream.
+            if (pq) {
+                pq->escape_string(&escaped, in.data(), in.length());
+            }
+            else {
+                psqls->escape_string(&escaped, in.data(), in.length());
+            }
+
+            o.ostr->write(escaped.data(), escaped.length());
+        }
+        else {
+            o.ostr->write(in.data(), in.length());
+        }
+
+        if (in.quote_q()) o.ostr->put('\'');
+    }
+    else {
+        // Some other stream type, so use formatted output.  User
+        // shouldn't be trying to use the quote manipulator, but
+        // that's no 'reason to break their formatting.
+        *o.ostr << string(in.data(), in.length());
+    }
+
+    return *o.ostr;
 }
 
 
+
 ostream&
 operator <<(quote_only_type1 o, const SQLTypeAdapter& in)
 {
 	Query* pq = dynamic_cast<Query*>(o.ostr);
 
-	if (pq && in.quote_q()) o.ostr->put('\'');
+    // If it's not a Query*, maybe it's a SQLStream*.
+    SQLStream* psqls = pq ? NULL : dynamic_cast<SQLStream*>(o.ostr);
 
-	if (pq) {
-		// It's a Query stream, so use unformatted output
+    // If it's a Query or SQLStream stream, so use unformatted output
+    if (pq || psqls) {
+        if (in.quote_q()) o.ostr->put('\'');
+
 		o.ostr->write(in.data(), in.length());
+
+        if (in.quote_q()) o.ostr->put('\'');
 	}
 	else {
 		// Some other stream type, so use formatted output.  User
@@ -101,8 +119,6 @@
 		*o.ostr << '\'' << in << '\'';
 	}
 
-	if (pq && in.quote_q()) o.ostr->put('\'');
-
 	return *o.ostr;
 }
 
@@ -110,8 +126,8 @@
 ostream&
 operator <<(ostream& o, const SQLTypeAdapter& in)
 {
-	if (dynamic_cast<Query*>(&o)) {
-		// It's a Query stream, so use unformatted output.
+    if (dynamic_cast<Query*>(&o) || dynamic_cast<SQLStream*>(&o)) {
+        // It's a Query or a SQLStream, so use unformatted output.
 		return o.write(in.data(), in.length());
 	}
 	else {
@@ -159,11 +175,16 @@
 {
 	Query* pq = dynamic_cast<Query*>(o.ostr);
 
-	if (pq && in.quote_q()) o.ostr->put('"');
+    // If it's not a Query*, maybe it's a SQLStream*.
+    SQLStream* psqls = pq ? NULL : dynamic_cast<SQLStream*>(o.ostr);
 
-	if (pq) {
-		// It's a Query stream, so use unformatted output
+    // If it's a Query or a SQLStream, use unformatted output
+    if (pq || psqls) {
+        if (in.quote_q()) o.ostr->put('"');
+
 		o.ostr->write(in.data(), in.length());
+
+        if (in.quote_q()) o.ostr->put('"');
 	}
 	else {
 		// Some other stream type, so use formatted output.  User
@@ -172,8 +193,6 @@
 		*o.ostr << '"' << in << '"';
 	}
 
-	if (pq && in.quote_q()) o.ostr->put('"');
-
 	return *o.ostr;
 }
 
@@ -197,25 +216,38 @@
 ostream&
 operator <<(escape_type1 o, const SQLTypeAdapter& in)
 {
-	Query* pq = dynamic_cast<Query*>(o.ostr);
-	if (pq) {
-		// It's a Query stream, so we'll be using unformatted output.
-		// Now, is escaping appropriate for source data type of 'in'?
-		if (in.escape_q()) {
-			string escaped;
-			pq->escape_string(&escaped, in.data(), in.length());
-			return o.ostr->write(escaped.data(), escaped.length());
-		}
-		else {
-			return o.ostr->write(in.data(), in.length());
-		}
-	}
-	else {
-		// Some other stream type, so use formatted output.  User
-		// shouldn't be trying to use the escape manipulator, but
-		// that's no reason to break their formatting.
-		return *o.ostr << string(in.data(), in.length());
-	}
+    Query* pq = dynamic_cast<Query*>(o.ostr);
+
+    // If it's not a Query*, maybe it's a SQLStream*.
+    SQLStream* psqls = pq ? NULL : dynamic_cast<SQLStream*>(o.ostr);
+
+    if (pq || psqls) {
+        // It's a Query or a SQLStream, so we'll be using unformatted output.
+        // Now, is escaping appropriate for source data type of 'in'?
+        if (in.escape_q()) {
+            string escaped;
+
+            // If it's not a Query*, then it has to be a SQLStream.
+            if (pq) {
+                pq->escape_string(&escaped, in.data(), in.length());
+            }
+            else {
+                psqls->escape_string(&escaped, in.data(), in.length());
+            }
+
+            return o.ostr->write(escaped.data(), escaped.length());
+        }
+        else {
+            // It's not escaped, so just write the unformatted output
+            return o.ostr->write(in.data(), in.length());
+        }
+    }
+    else {
+        // Some other stream type, so use formatted output.  User
+        // shouldn't be trying to use the escape manipulator, but
+        // that's no reason to break their formatting.
+        return *o.ostr << string(in.data(), in.length());
+    }
 }
 
 
@@ -230,8 +262,9 @@
 ostream&
 operator <<(do_nothing_type1 o, const SQLTypeAdapter& in)
 {
-	if (dynamic_cast<Query*>(o.ostr)) {
-		// It's a Query stream, so use unformatted output
+    if (dynamic_cast<Query*>(o.ostr) ||
+        dynamic_cast<SQLStream*>(o.ostr)) {
+        // It's a Query or a SQLStream, so use unformatted output
 		return o.ostr->write(in.data(), in.length());
 	}
 	else {
Index: lib/query.cpp
===================================================================
--- lib/query.cpp	(revision 2396)
+++ lib/query.cpp	(working copy)
@@ -35,7 +35,7 @@
 Query::Query(Connection* c, bool te, const char* qstr) :
 #if defined(MYSQLPP_HAVE_STD__NOINIT)
 // prevents a double-init memory leak in native VC++ RTL (not STLport!)
-std::ostream(std::_Noinit), 
+std::ostream(std::_Noinit),
 #else
 std::ostream(0),
 #endif
@@ -84,7 +84,7 @@
 }
 
 
-const char* 
+const char*
 Query::error() const
 {
 	return conn_->error();
@@ -216,7 +216,7 @@
 }
 
 
-bool 
+bool
 Query::more_results()
 {
 	return conn_->driver()->more_results();
@@ -248,7 +248,7 @@
 	std::string name;
 
 	char* s = new char[sbuffer_.str().size() + 1];
-	memcpy(s, sbuffer_.str().data(), sbuffer_.str().size()); 
+	memcpy(s, sbuffer_.str().data(), sbuffer_.str().size());
 	s[sbuffer_.str().size()] = '\0';
 	const char* s0 = s;
 
@@ -408,6 +408,7 @@
 	for (std::vector<SQLParseElement>::iterator i = parse_elems_.begin();
 			i != parse_elems_.end(); ++i) {
 		MYSQLPP_QUERY_THISPTR << i->before;
+
 		int num = i->num;
 		if (num >= 0) {
 			SQLQueryParms* c;
@@ -426,6 +427,7 @@
 			SQLTypeAdapter& param = (*c)[num];
 			SQLTypeAdapter* ss = pprepare(i->option, param, c->bound());
 			MYSQLPP_QUERY_THISPTR << *ss;
+
 			if (ss != &param) {
 				// pprepare() returned a new string object instead of
 				// updating param in place, so we need to delete it.
@@ -456,7 +458,7 @@
 }
 
 
-StoreQueryResult 
+StoreQueryResult
 Query::store(const SQLTypeAdapter& s)
 {
 	if ((parse_elems_.size() == 2) && !template_defaults.processing_) {
@@ -524,7 +526,7 @@
 		if (res) {
 			return StoreQueryResult(res, conn_->driver(),
 					throw_exceptions());
-		} 
+		}
 		else {
 			// Result set is null, but throw an exception only i it is
 			// null because of some error.  If not, it's just an empty
@@ -532,7 +534,7 @@
 			// set if exceptions are disabled, as well.
 			if (conn_->errnum() && throw_exceptions()) {
 				throw BadQuery(error(), errnum());
-			} 
+			}
 			else {
 				return StoreQueryResult();
 			}
@@ -599,7 +601,7 @@
 Query::use(const char* str, size_t len)
 {
 	MYSQL_RES* res = 0;
-	if ((copacetic_ = conn_->driver()->execute(str, len)) == true) {
+    if ((copacetic_ = conn_->driver()-> execute(str, len)) == true) {
 		res = conn_->driver()->use_result();
 	}
 
Index: lib/query.h
===================================================================
--- lib/query.h	(revision 2396)
+++ lib/query.h	(working copy)
@@ -30,12 +30,15 @@
 
 #include "common.h"
 
+#include "insertpolicy.h"
+#include "exceptions.h"
 #include "noexceptions.h"
 #include "qparms.h"
 #include "querydef.h"
 #include "result.h"
 #include "row.h"
 #include "stadapter.h"
+#include "transaction.h"
 
 #include <deque>
 #include <iomanip>
@@ -104,8 +107,8 @@
 /// or \link mysqlpp::Query::use(const SQLTypeAdapter&) use() \endlink
 /// overloads that take SQLTypeAdapter objects.  There are 25 of each by
 /// default, differing only in the number of STA objects they take.
-/// (See \c lib/querydef.pl if you need to change the limit, or 
-/// \c examples/tquery2.cpp for a way around it that doesn't require 
+/// (See \c lib/querydef.pl if you need to change the limit, or
+/// \c examples/tquery2.cpp for a way around it that doesn't require
 /// changing the library.)  Only the version taking a single STA object
 /// is documented below, as to document all of them would just be
 /// repetitive.  For each Query method that takes a single STA object,
@@ -377,7 +380,7 @@
 
 	/// \brief Execute a query that can return rows, with access to
 	/// the rows in sequence
-	/// 
+	///
 	/// Use one of the use() overloads if memory efficiency is
 	/// important.  They return an object that can walk through
 	/// the result records one by one, without fetching the entire
@@ -518,7 +521,7 @@
 	/// \return a copy of the passed functor
 	template <typename Function>
 	Function for_each(const SQLTypeAdapter& query, Function fn)
-	{	
+	{
 		mysqlpp::UseQueryResult res = use(query);
 		if (res) {
 			mysqlpp::NoExceptions ne(res);
@@ -539,7 +542,7 @@
 	/// \return a copy of the passed functor
 	template <typename Function>
 	Function for_each(Function fn)
-	{	
+	{
 		mysqlpp::UseQueryResult res = use();
 		if (res) {
 			mysqlpp::NoExceptions ne(res);
@@ -563,7 +566,7 @@
 	/// \return a copy of the passed functor
 	template <class SSQLS, typename Function>
 	Function for_each(const SSQLS& ssqls, Function fn)
-	{	
+	{
 		std::string query("select * from ");
 		query += ssqls.table();
 		mysqlpp::UseQueryResult res = use(query);
@@ -598,7 +601,7 @@
 	/// \return a copy of the passed functor
 	template <class Sequence, typename Function>
 	Function store_if(Sequence& con, const SQLTypeAdapter& query, Function fn)
-	{	
+	{
 		mysqlpp::UseQueryResult res = use(query);
 		if (res) {
 			mysqlpp::NoExceptions ne(res);
@@ -625,7 +628,7 @@
 	/// \return a copy of the passed functor
 	template <class Sequence, class SSQLS, typename Function>
 	Function store_if(Sequence& con, const SSQLS& ssqls, Function fn)
-	{	
+	{
 		std::string query("select * from ");
 		query += ssqls.table();
 		mysqlpp::UseQueryResult res = use(query);
@@ -652,7 +655,7 @@
 	/// \return a copy of the passed functor
 	template <class Sequence, typename Function>
 	Function store_if(Sequence& con, Function fn)
-	{	
+	{
 		mysqlpp::UseQueryResult res = use();
 		if (res) {
 			mysqlpp::NoExceptions ne(res);
@@ -670,11 +673,11 @@
 	///
 	/// There are two cases where you'd use this function instead of
 	/// the regular store() functions.
-	/// 
+	///
 	/// First, when handling the result of executing multiple queries
 	/// at once.  (See <a
 	/// href="http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html">this
-	/// page</a> in the MySQL documentation for details.) 
+	/// page</a> in the MySQL documentation for details.)
 	///
 	/// Second, when calling a stored procedure, MySQL can return the
 	/// result as a set of results.
@@ -943,7 +946,8 @@
 		MYSQLPP_QUERY_THISPTR << std::setprecision(16) <<
 				"UPDATE " << o.table() << " SET " << n.equal_list() <<
 				" WHERE " << o.equal_list(" AND ", sql_use_compare);
-		return *this;
+
+        return *this;
 	}
 
 	/// \brief Insert a new row.
@@ -963,6 +967,7 @@
 				"INSERT INTO " << v.table() << " (" <<
 				v.field_list() << ") VALUES (" <<
 				v.value_list() << ')';
+
 		return *this;
 	}
 
@@ -978,7 +983,7 @@
 	/// \param last iterator pointing to one past the last element to
 	///    insert
 	///
-	/// \sa replace(), update()
+    /// \sa replace(), update(), insertfrom()
 	template <class Iter>
 	Query& insert(Iter first, Iter last)
 	{
@@ -986,7 +991,7 @@
 		if (first == last) {
 			return *this;	// empty set!
 		}
-		
+
 		MYSQLPP_QUERY_THISPTR << std::setprecision(16) <<
 				"INSERT INTO " << first->table() << " (" <<
 				first->field_list() << ") VALUES (" <<
@@ -1001,6 +1006,33 @@
 		return *this;
 	}
 
+    /// \brief Insert multiple new rows using an insert policy to
+    /// control how the INSERT statements are created using
+    /// items from an STL container.
+    ///
+    /// \param first iterator pointing to first element in range to
+    ///    insert
+    /// \param last iterator pointing to one past the last element to
+    ///    insert
+    /// \param policy insert policy object, see insertpolicy.h for
+    /// details
+    ///
+    /// \sa insert(), _insertfrom()
+
+    template <class Iter, class InsertPolicy>
+    Query& insertfrom(Iter first, Iter last, InsertPolicy& policy )
+    {
+        typename InsertPolicy::access_controller ac(*conn_);
+
+        if (_insertfrom(first, last, policy)) {
+            ac.commit();
+        } else {
+            ac.rollback();
+        }
+
+        return *this;
+    }
+
 	/// \brief Insert new row unless there is an existing row that
 	/// matches on a unique index, in which case we replace it.
 	///
@@ -1018,6 +1050,7 @@
 		MYSQLPP_QUERY_THISPTR << std::setprecision(16) <<
 				"REPLACE INTO " << v.table() << " (" <<
 				v.field_list() << ") VALUES (" << v.value_list() << ')';
+
 		return *this;
 	}
 
@@ -1041,6 +1074,93 @@
 	SQLQueryParms template_defaults;
 
 private:
+    /// \brief Insert multiple new rows using an insert policy to control
+    /// how the INSERT statements are created using items from an STL
+    /// container.
+    ///
+    /// This is the private version of the function with all the brains.
+    /// The public version, insertfrom(), uses the insert policy to wrap
+    /// this operation in a transaction based on whether the insert policy
+    /// allows transactions or not.
+    ///
+    /// Based on the insert policy, this method will generate and execute
+    /// one or more INSERT statements in order to insert the container
+    /// range of objects specified by first and last into the database.
+    ///
+    /// \param first iterator pointing to first element in range to insert
+    /// \param last iterator pointing to one past the last element to
+    ///    insert
+    /// \param policy insert policy object, see insertpolicy.h for
+    /// details
+    ///
+    /// \retval boolean representing the success of the operation
+    ///
+    /// \sa insertfrom(), insert()
+    template <class Iter, class InsertPolicy>
+    bool _insertfrom(Iter first, Iter last, InsertPolicy& policy)
+    {
+        bool success = true,
+             empty = true;
+
+        reset();
+
+        if (first == last) {
+            return true;   // empty set!
+        }
+
+        for (Iter it = first; it != last; it++ ) {
+            if (policy.can_add(tellp(), *it)) {
+                if (empty) {
+                    MYSQLPP_QUERY_THISPTR << std::setprecision(16) <<
+                        "INSERT INTO " << it->table() << " (" <<
+                        it->field_list() << ") VALUES (";
+                } else {
+                    MYSQLPP_QUERY_THISPTR << ", (";
+                }
+
+                MYSQLPP_QUERY_THISPTR << it->value_list() << ")";
+
+                empty = false;
+            } else {
+                // execute what we've built up already, if there is anything
+                if (!empty) {
+                    if (!exec()) {
+                        success = false;
+                        break;
+                    }
+
+                    empty = true;
+                }
+
+                // if we _still_ can't add, the policy is too strict
+                if (policy.can_add(tellp(), *it)) {
+                    MYSQLPP_QUERY_THISPTR << std::setprecision(16) <<
+                        "INSERT INTO " << it->table() << " (" <<
+                        it->field_list() << ") VALUES (" <<
+                        it->value_list() << ")";
+
+                    empty = false;
+                } else {
+                    // at this point all we can do is give up
+                    if (throw_exceptions()) {
+                        throw BadInsertPolicy("Insert policy is too strict");
+                    }
+
+                    success = false;
+                    break;
+                }
+            }
+        }
+
+        // We might need to execute the last query here.
+        if ( success && !empty && !exec()) {
+            success = false;
+        }
+
+        return success;
+    }
+
+
 	friend class SQLQueryParms;
 
 	/// \brief Connection to send queries through
Index: lib/exceptions.h
===================================================================
--- lib/exceptions.h	(revision 2396)
+++ lib/exceptions.h	(working copy)
@@ -419,6 +419,24 @@
 };
 
 
+/// \brief Exception thrown when an insert policy is too strict to
+/// create a valid INSERT statement.
+///
+/// Thrown by Query::insertfrom() if it is unable to add VALUES
+/// to an empty query.  This means the size threshold or max packet
+/// size of the policy is set too small.
+
+class MYSQLPP_EXPORT BadInsertPolicy : public Exception
+{
+public:
+	/// \brief Create exception object
+	explicit BadInsertPolicy(const std::string& w) :
+	Exception(w)
+	{
+	}
+};
+
+
 } // end namespace mysqlpp
 
 #endif // !defined(MYSQLPP_EXCEPTIONS_H)
Index: mysql++.bkl
===================================================================
--- mysql++.bkl	(revision 2396)
+++ mysql++.bkl	(working copy)
@@ -73,7 +73,8 @@
                 lib/query.cpp
                 lib/result.cpp
                 lib/row.cpp
-                lib/sql_buffer.cpp
+		lib/sql_buffer.cpp
+		lib/sqlstream.cpp
                 lib/stadapter.cpp
                 lib/tcp_connection.cpp
                 lib/transaction.cpp
Index: Wishlist
===================================================================
--- Wishlist	(revision 2396)
+++ Wishlist	(working copy)
@@ -201,3 +201,12 @@
       false.  If set, the Transaction object does nothing.  If not
       set, set it and send the query.  This prevents it from trying
       to set up nested queries, which MySQL doesn't support.
+
+    o Query and SQLStream could have a common base class that would
+      allow the stream manipulator functions to catch and modify 
+      strings based on only one dynamic_cast instead of requiring 
+      two as it does since the addition of the SQLStream class.  This
+      could also help ease the unnecessary duplication of 
+      escape_string() methods.
+
+
Index: examples/cpool.cpp
===================================================================
--- examples/cpool.cpp	(revision 2396)
+++ examples/cpool.cpp	(working copy)
@@ -28,6 +28,7 @@
 #include "cmdline.h"
 #include "threads.h"
 
+#include <unistd.h>
 #include <iostream>
 
 using namespace std;

Thread
insertfrom( ) questionsRick Gutleber7 Nov
  • Re: insertfrom( ) questionsWarren Young8 Nov
    • 'result' is not a member of mysqlppJJ Harrison8 Nov
      • Re: 'result' is not a member of mysqlppWarren Young8 Nov
    • Re: insertfrom( ) questionsRick Gutleber10 Nov
      • Re: insertfrom( ) questionsWarren Young11 Nov
        • Re: insertfrom( ) questionsRick Gutleber11 Nov
          • Re: insertfrom( ) questionsWarren Young11 Nov
            • Re: insertfrom( ) questionsRick Gutleber12 Nov
    • Re: insertfrom( ) questionsRick Gutleber13 Nov
      • Re: insertfrom( ) questionsWarren Young13 Nov
        • Re: insertfrom( ) questionsRick Gutleber13 Nov
          • Re: insertfrom( ) questionsWarren Young13 Nov
  • RE: insertfrom( ) questionsIan Daysh11 Nov
    • Re: insertfrom( ) questionsJim Graf11 Nov