Bulk update implementation

6 files changed, 258 insertions, 137 deletions

diff --git a/odb/mssql/error.cxx b/odb/mssql/error.cxx
index acc48dc..23d5be9 100644
--- a/odb/mssql/error.cxx
+++ b/odb/mssql/error.cxx
@@ -191,7 +191,7 @@ namespace odb
               break;
           else if (SQL_SUCCEEDED (r) &&
                    n != SQL_NO_ROW_NUMBER &&
-                   n == SQL_ROW_NUMBER_UNKNOWN &&
+                   n != SQL_ROW_NUMBER_UNKNOWN &&
                    n != static_cast<SQLLEN> (pos + 1)) // 1-based
             continue;
         }
@@ -237,6 +237,8 @@ namespace odb
                                  0,
                                  0);
 
+            cerr << i << " ";
+
             // check error
             if (n == SQL_NO_ROW_NUMBER)
               cerr << "not associated with any row" << endl;
diff --git a/odb/mssql/simple-object-statements.hxx b/odb/mssql/simple-object-statements.hxx
index 7c5c868..dbe1f30 100644
--- a/odb/mssql/simple-object-statements.hxx
+++ b/odb/mssql/simple-object-statements.hxx
@@ -278,7 +278,7 @@ namespace odb
       image_type&
       image (std::size_t i = 0)
       {
-        return image_[i];
+        return images_[i].obj;
       }
 
       // Insert binding.
@@ -323,7 +323,7 @@ namespace odb
       // Object id image and binding.
       //
       id_image_type&
-      id_image (std::size_t i = 0) {return id_image_[i];}
+      id_image (std::size_t i = 0) {return images_[i].id;}
 
       std::size_t
       id_image_version () const {return id_image_version_;}
@@ -388,7 +388,8 @@ namespace odb
             new (details::shared) update_statement_type (
               conn_,
               object_traits::update_statement,
-              object_traits::versioned, // Process if versioned.
+              true,                            // Unique (0 or 1).
+              object_traits::versioned,        // Process if versioned.
               update_image_binding_,
               object_traits::rowversion,
               false));
@@ -434,7 +435,7 @@ namespace odb
       extra_statement_cache ()
       {
         return extra_statement_cache_.get (
-          conn_, image_, id_image_binding_, od_.id_image_binding ());
+          conn_, images_[0].obj, id_image_binding_, od_.id_image_binding ());
       }
 
     public:
@@ -484,7 +485,17 @@ namespace odb
       extra_statement_cache_ptr<extra_statement_cache_type, image_type>
       extra_statement_cache_;
 
-      image_type image_[object_traits::batch];
+      // The UPDATE statement uses both the object and id image. Keep
+      // them next to each other so that the same skip distance can
+      // be used in batch binding.
+      //
+      struct images
+      {
+        image_type obj;
+        id_image_type id;
+      };
+
+      images images_[object_traits::batch];
       SQLUSMALLINT status_[object_traits::batch];
 
       // Select binding.
@@ -517,7 +528,6 @@ namespace odb
       // Id image binding (only used as a parameter or in OUTPUT for
       // auto id and version). Uses the suffix in the update bind.
       //
-      id_image_type id_image_[object_traits::batch];
       std::size_t id_image_version_;
       binding id_image_binding_;
 
diff --git a/odb/mssql/simple-object-statements.txx b/odb/mssql/simple-object-statements.txx
index fa1b6e7..a0626fa 100644
--- a/odb/mssql/simple-object-statements.txx
+++ b/odb/mssql/simple-object-statements.txx
@@ -46,28 +46,32 @@ namespace odb
           insert_image_binding_ (insert_image_bind_,
                                  insert_column_count,
                                  object_traits::batch,
-                                 sizeof (image_type),
+                                 sizeof (images),
                                  status_),
           update_image_binding_ (update_image_bind_,
                                  update_column_count + id_column_count +
-                                 managed_optimistic_column_count),
+                                 managed_optimistic_column_count,
+                                 object_traits::batch,
+                                 sizeof (images),
+                                 status_),
           id_image_binding_ (update_image_bind_ + update_column_count,
                              id_column_count,
                              object_traits::batch,
-                             sizeof (id_image_type),
+                             sizeof (images),
                              status_),
           od_ (update_image_bind_ + update_column_count)
     {
-      image_[0].version = 0; // @@ TODO [0]
+      images_[0].obj.version = 0; // @@ TODO [0]
+      images_[0].id.version = 0;  // @@ TODO
+
       select_image_version_ = 0;
       insert_image_version_ = 0;
       update_image_version_ = 0;
       update_id_image_version_ = 0;
-
-      id_image_[0].version = 0; // @@ TODO
       id_image_version_ = 0;
 
-      select_image_binding_.change_callback = image_[0].change_callback ();
+      select_image_binding_.change_callback =
+        images_[0].obj.change_callback ();
 
       std::memset (insert_image_bind_, 0, sizeof (insert_image_bind_));
       std::memset (update_image_bind_, 0, sizeof (update_image_bind_));
diff --git a/odb/mssql/statement.cxx b/odb/mssql/statement.cxx
index e49627c..22b1de9 100644
--- a/odb/mssql/statement.cxx
+++ b/odb/mssql/statement.cxx
@@ -783,12 +783,12 @@ namespace odb
       bool ok (SQL_SUCCEEDED (r) || r == SQL_NO_DATA);
 
       // If the statement failed as a whole, assume no parameter sets
-      // were attempted. Otherwise, the documentation says that the
-      // native client driver keeps processing remaining sets even
-      // in case of an error.
+      // were attempted in case of a batch. Otherwise, the documentation
+      // says that the native client driver keeps processing remaining
+      // sets even in case of an error.
       //
       i_ = 0;
-      n_ = (ok ? n : 0);
+      n_ = (ok ? n : (status_ == 0 ? 1 : 0));
 
       if (mex_ != 0)
       {
@@ -828,6 +828,73 @@ namespace odb
       return r;
     }
 
+    size_t bulk_statement::
+    extract_errors ()
+    {
+      size_t e (0);
+
+      for (size_t i (0); i != n_; ++i)
+      {
+        if (status_[i] != SQL_PARAM_SUCCESS &&
+            status_[i] != SQL_PARAM_SUCCESS_WITH_INFO)
+        {
+          translate_error (SQL_ERROR, conn_, stmt_, i, mex_);
+          e++;
+        }
+      }
+
+      return e;
+    }
+
+    unsigned long long bulk_statement::
+    affected (SQLRETURN r, size_t errors, bool unique)
+    {
+      unsigned long long rows (0);
+
+      // SQL_NO_DATA indicates that the statement hasn't affected any rows.
+      //
+      if (r != SQL_NO_DATA)
+      {
+        SQLLEN n;
+        r = SQLRowCount (stmt_, &n);
+
+        if (!SQL_SUCCEEDED (r))
+          translate_error (r, conn_, stmt_);
+
+        // If all the parameter sets failed, then the returned count is -1,
+        // which means "not available" according to the documentation.
+        //
+        rows = (n != -1 ? static_cast<unsigned long long> (n) : 0);
+      }
+
+      cerr << "total: " << rows << endl;
+
+      if (n_ > 1) // Batch.
+      {
+        if (rows != 0) // Some rows did get affected.
+        {
+          // Subtract the parameter sets that failed since they haven't
+          // affected any rows.
+          //
+          size_t p (n_ - errors);
+
+          if (p > 1) // True batch.
+          {
+            if (unique) // Each can affect 0 or 1 row.
+            {
+              rows = (p == static_cast<size_t> (rows)
+                      ? 1
+                      : result_unknown);
+            }
+            else
+              rows = result_unknown;
+          }
+        }
+      }
+
+      return rows;
+    }
+
     //
     // select_statement
     //
@@ -1085,10 +1152,29 @@ namespace odb
       // (value in n_). Note that in the OUTPUT case if there is an
       // error, the processed count seems to jump by 2 for some reason.
       //
+      // The OUTPUT case can be handled in two different ways: we can
+      // "execute" (with SQLMoreResults()) each set as the user moves
+      // from one result to the next (result() call). The advantage of
+      // this approach is that the returned data ends up in the right
+      // place automatically. The potential drawback is that the total
+      // affected row count will only be available at the end. As a
+      // result, this approach probably won't work if we need to handle,
+      // say, UPDATE with OUTPUT (SQLRowCount() does not return an
+      // intermediate total, at least not for INSERT).
+      //
+      // The alternative implementation would call SQLMoreResults()
+      // inside execute() until all the parameter sets are executed.
+      // In this case we will have to copy the extracted data into
+      // the right place in the bindings (or update the binding before
+      // each call to SQLMoreResults()). It is also not clear whether
+      // the diagnostic records for the failed sets would accumulate.
+      // If not, those will have to be stashed into mex on each
+      // iteration.
+      //
       SQLRETURN r (bulk_statement::execute (n, mex));
 
       // Statement failed as a whole, assume no parameter sets were
-      // attempted.
+      // attempted in case of a batch.
       //
       if (!SQL_SUCCEEDED (r))
       {
@@ -1096,8 +1182,8 @@ namespace odb
         return n_;
       }
 
-      if (n == 1) // Note: not n_; n and n_ not be the same, see command above.
-        fetch (SQL_SUCCESS); // Non-batch case.
+      if (status_ == 0) // Non-batch case.
+        fetch (SQL_SUCCESS);
       else
         fetch (status_[i_] == SQL_PARAM_SUCCESS ||
                status_[i_] == SQL_PARAM_SUCCESS_WITH_INFO
@@ -1209,7 +1295,7 @@ namespace odb
         }
       }
 
-      // Fetch the row containing the id/version if this statement if
+      // Fetch the row containing the id/version if this statement is
       // returning.
       //
       if (result_ && (returning_id_ || returning_version_))
@@ -1313,12 +1399,15 @@ namespace odb
     update_statement::
     update_statement (connection_type& conn,
                       const string& text,
+                      bool unique,
                       bool process,
                       binding& param,
                       bool returning_version)
-        : statement (conn,
-                     text, statement_update,
-                     (process ? &param : 0), false),
+        : bulk_statement (conn,
+                          text, statement_update,
+                          (process ? &param : 0), false,
+                          param.batch, param.skip, param.status),
+          unique_ (unique),
           returning_version_ (returning_version)
     {
       if (!empty ())
@@ -1333,14 +1422,17 @@ namespace odb
     update_statement::
     update_statement (connection_type& conn,
                       const char* text,
+                      bool unique,
                       bool process,
                       binding& param,
                       bool returning_version,
                       bool copy_text)
-        : statement (conn,
-                     text, statement_update,
-                     (process ? &param : 0), false,
-                     copy_text),
+        : bulk_statement (conn,
+                          text, statement_update,
+                          (process ? &param : 0), false,
+                          param.batch, param.skip, param.status,
+                          copy_text),
+          unique_ (unique),
           returning_version_ (returning_version)
     {
       if (!empty ())
@@ -1367,53 +1459,77 @@ namespace odb
         translate_error (r, conn_, stmt_);
     }
 
-    unsigned long long update_statement::
-    execute ()
+    size_t update_statement::
+    execute (size_t n, multiple_exceptions* mex)
     {
-      SQLRETURN r (statement::execute ());
-
-      // SQL_NO_DATA indicates that the statement hasn't affected any rows.
+      // In batch UPDATE without the OUTPUT clause (which is the
+      // only kind we currently support) all the parameter sets
+      // are processed inside SQLExecute() and the total count of
+      // affected rows is available after it returns.
       //
-      if (r == SQL_NO_DATA)
-        return 0;
+      assert (!returning_version_ || n == 1);
 
-      if (!SQL_SUCCEEDED (r))
-        translate_error (r, conn_, stmt_);
+      SQLRETURN r (bulk_statement::execute (n, mex));
 
-      // Get the number of affected rows.
+      // Statement failed as a whole, assume no parameter sets were
+      // attempted in case of a batch.
       //
-      SQLLEN rows;
-      r = SQLRowCount (stmt_, &rows);
-
-      if (!SQL_SUCCEEDED (r))
-        translate_error (r, conn_, stmt_);
+      if (!(SQL_SUCCEEDED (r) || r == SQL_NO_DATA))
+      {
+        translate_error (r, conn_, stmt_, 0, mex_);
+        return n_;
+      }
 
-      // Fetch the row containing the version if this statement is
-      // returning. We still need to close the cursor even if we
-      // haven't updated any rows.
+      // Extract error information for failed parameter sets. If we do
+      // this after calling SQLRowCount(), all the diagnostics records
+      // that we need will be gone.
       //
-      if (returning_version_)
-      {
-        r = SQLFetch (stmt_);
+      size_t errors (n_ > 1 ? extract_errors () : 0);
 
-        if (r != SQL_NO_DATA && !SQL_SUCCEEDED (r))
-          translate_error (r, conn_, stmt_);
+      // Figure out the affected row count.
+      //
+      result_ = affected (r, errors, unique_);
 
+      if (status_ == 0) // Non-batch case.
+      {
+        // Fetch the row containing the version if this statement is
+        // returning. We still need to close the cursor even if we
+        // haven't updated any rows.
+        //
+        if (returning_version_)
         {
-          SQLRETURN r (SQLCloseCursor (stmt_)); // Don't overwrite r.
+          r = SQLFetch (stmt_);
 
-          if (!SQL_SUCCEEDED (r))
+          if (r != SQL_NO_DATA && !SQL_SUCCEEDED (r))
             translate_error (r, conn_, stmt_);
-        }
 
-        if (rows != 0 && r == SQL_NO_DATA)
-          throw database_exception (
-            0,
-            "?????",
-            "result set expected from a statement with the OUTPUT clause");
+          {
+            SQLRETURN r (SQLCloseCursor (stmt_)); // Don't overwrite r.
+
+            if (!SQL_SUCCEEDED (r))
+              translate_error (r, conn_, stmt_);
+          }
+
+          if (result_ != 0 && r == SQL_NO_DATA)
+            throw database_exception (
+              0,
+              "?????",
+              "result set expected from a statement with the OUTPUT clause");
+        }
       }
 
-      return static_cast<unsigned long long> (rows);
+      return n_;
+    }
+
+    unsigned long long update_statement::
+    result (size_t i)
+    {
+      assert ((i_ == i || i_ + 1 == i) && i < n_);
+
+      if (i != i_)
+        mex_->current (++i_); // mex cannot be NULL since this is a batch.
+
+      return result_;
     }
 
     //
@@ -1467,91 +1583,35 @@ namespace odb
       SQLRETURN r (bulk_statement::execute (n, mex));
 
       // Statement failed as a whole, assume no parameter sets were
-      // attempted.
+      // attempted in case of a batch.
       //
       if (!(SQL_SUCCEEDED (r) || r == SQL_NO_DATA))
       {
-        fetch (r);
+        translate_error (r, conn_, stmt_, 0, mex_);
         return n_;
       }
 
-      // Figure out the affected row count. SQL_NO_DATA indicates that
-      // the statement hasn't affected any rows.
+      // Extract error information for failed parameter sets. If we do
+      // this after calling SQLRowCount(), all the diagnostics records
+      // that we need will be gone.
       //
-      if (r == SQL_NO_DATA)
-        result_ = 0;
-      else
-      {
-        SQLLEN rows;
-        r = SQLRowCount (stmt_, &rows);
+      size_t errors (n_ > 1 ? extract_errors () : 0);
 
-        if (!SQL_SUCCEEDED (r))
-          translate_error (r, conn_, stmt_);
-
-        result_ = static_cast<unsigned long long> (rows);
-      }
-
-      cerr << "total: " << result_ << endl;
-
-      if (n_ > 1) // Batch.
-      {
-        if (result_ != 0) // Some rows did get affected.
-        {
-          size_t p (n_);
-
-          // Subtract the parameter sets that failed since they haven't
-          // affected any rows.
-          //
-          for (size_t i (0); i != n_; ++i)
-            if (status_[i] != SQL_PARAM_SUCCESS &&
-                status_[i] != SQL_PARAM_SUCCESS_WITH_INFO)
-              p--;
-
-          if (p > 1) // True batch.
-          {
-            if (unique_) // Each can affect 0 or 1 row.
-            {
-              result_ = (p == static_cast<size_t> (result_)
-                         ? 1
-                         : result_unknown);
-            }
-            else
-              result_ = result_unknown;
-          }
-        }
-      }
-
-      if (n == 1) // n and n_ are really the same here.
-        fetch (SQL_SUCCESS); // Non-batch case.
-      else
-        fetch (status_[i_] == SQL_PARAM_SUCCESS ||
-               status_[i_] == SQL_PARAM_SUCCESS_WITH_INFO
-               ? SQL_SUCCESS : SQL_ERROR);
+      // Figure out the affected row count.
+      //
+      result_ = affected (r, errors, unique_);
 
       return n_;
     }
 
-    void delete_statement::
-    fetch (SQLRETURN r)
-    {
-      if (!SQL_SUCCEEDED (r))
-        translate_error (r, conn_, stmt_, i_, mex_); // Can return.
-    }
-
     unsigned long long delete_statement::
     result (size_t i)
     {
       assert ((i_ == i || i_ + 1 == i) && i < n_);
 
       if (i != i_)
-      {
         mex_->current (++i_); // mex cannot be NULL since this is a batch.
 
-        fetch (status_[i_] == SQL_PARAM_SUCCESS ||
-               status_[i_] == SQL_PARAM_SUCCESS_WITH_INFO
-               ? SQL_SUCCESS : SQL_ERROR);
-      }
-
       return result_;
     }
   }
diff --git a/odb/mssql/statement.hxx b/odb/mssql/statement.hxx
index ea53a38..158964d 100644
--- a/odb/mssql/statement.hxx
+++ b/odb/mssql/statement.hxx
@@ -133,6 +133,7 @@ namespace odb
       virtual
       ~bulk_statement () = 0;
 
+    protected:
       bulk_statement (connection_type&,
                       const std::string& text,
                       statement_kind,
@@ -160,13 +161,23 @@ namespace odb
       SQLRETURN
       execute (std::size_t n, multiple_exceptions*);
 
+      // Return the number of failed parameter sets.
+      //
+      std::size_t
+      extract_errors ();
+
+      static const unsigned long long result_unknown = ~0ULL;
+
+      unsigned long long
+      affected (SQLRETURN, std::size_t errors, bool unique);
+
     private:
       void
       init (std::size_t skip);
 
     protected:
-      SQLULEN processed_;    // Number of batch rows processed so far.
-      SQLUSMALLINT* status_; // Row status array.
+      SQLULEN processed_;    // Number of parameter sets processed so far.
+      SQLUSMALLINT* status_; // Parameter sets status array.
       std::size_t n_;        // Actual batch size.
       std::size_t i_;        // Position in result.
       multiple_exceptions* mex_;
@@ -335,28 +346,63 @@ namespace odb
       bool result_;
     };
 
-    class LIBODB_MSSQL_EXPORT update_statement: public statement
+    class LIBODB_MSSQL_EXPORT update_statement: public bulk_statement
     {
     public:
       virtual
       ~update_statement ();
 
+      // SQL Server native client ODBC driver does not expose individual
+      // affected row counts for batch operations, even though it says it
+      // does (SQLGetInfo(SQL_PARAM_ARRAY_ROW_COUNTS) returns SQL_PARC_BATCH).
+      // Instead, it adds them all up and returns a single count. This is
+      // bad news for us.
+      //
+      // In case of updating by primary key (the affected row count is
+      // either 1 or 0), we can recognize the presumably successful case
+      // where the total affected row count is equal to the batch size
+      // (we can also recognize the "all unsuccessful" case where the
+      // total affected row count is 0). The unique_hint argument in the
+      // constructors below indicates whether this is a "0 or 1" UPDATE
+      // statement.
+      //
+      // In all other situations (provided this is a batch), the result()
+      // function below returns the special result_unknown value.
+      //
       update_statement (connection_type& conn,
                         const std::string& text,
+                        bool unique_hint,
                         bool process,
                         binding& param,
                         bool returning_version);
 
       update_statement (connection_type& conn,
                         const char* text,
+                        bool unique_hint,
                         bool process,
                         binding& param,
                         bool returning_version,
                         bool copy_text = true);
 
+      // Return the number of parameter sets (out of n) that were attempted.
+      //
+      std::size_t
+      execute (std::size_t n = 1, multiple_exceptions* = 0);
+
+      // Return the number of rows affected (deleted) by the parameter
+      // set. If this is a batch (n > 1 in execute() call above) and it
+      // is impossible to determine the affected row count for each
+      // parameter set, then this function returns result_unknown. All
+      // other errors are reported by throwing exceptions.
+      //
+      using bulk_statement::result_unknown;
+
       unsigned long long
-      execute ();
+      result (std::size_t i = 0);
 
+      // Note that currently the implementation does not support batch
+      // with the OUTPUT clause.
+      //
       unsigned long long
       version ();
 
@@ -369,8 +415,11 @@ namespace odb
       init_result ();
 
     private:
+      bool unique_;
       bool returning_version_;
 
+      unsigned long long result_;
+
       unsigned char version_[8];
       SQLLEN version_size_ind_;
     };
@@ -420,7 +469,7 @@ namespace odb
       // parameter set, then this function returns result_unknown. All
       // other errors are reported by throwing exceptions.
       //
-      static const unsigned long long result_unknown = ~0ULL;
+      using bulk_statement::result_unknown;
 
       unsigned long long
       result (std::size_t i = 0);
@@ -430,10 +479,6 @@ namespace odb
       delete_statement& operator= (const delete_statement&);
 
     private:
-      void
-      fetch (SQLRETURN);
-
-    private:
       bool unique_;
       unsigned long long result_;
     };
diff --git a/odb/mssql/statement.ixx b/odb/mssql/statement.ixx
index 66316f4..642a5d7 100644
--- a/odb/mssql/statement.ixx
+++ b/odb/mssql/statement.ixx
@@ -18,7 +18,7 @@ namespace odb
         : statement (c, text, k, process, optimize),
           status_ (batch == 1 ? 0 : status)
     {
-      if (batch != 1)
+      if (batch != 1 && !empty ())
         init (skip);
     }
 
@@ -35,7 +35,7 @@ namespace odb
         : statement (c, text, k, process, optimize, copy_text),
           status_ (batch == 1 ? 0 : status)
     {
-      if (batch != 1)
+      if (batch != 1 && !empty ())
         init (skip);
     }