From 6d29dfc7d766482cbe5578d662041431bd3d6dc9 Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Thu, 13 Nov 2014 13:54:29 +0200 Subject: Bulk update implementation --- odb/oracle/binding.hxx | 4 +- odb/oracle/simple-object-statements.hxx | 27 ++- odb/oracle/simple-object-statements.txx | 18 +- odb/oracle/statement.cxx | 316 +++++++++++++++----------------- odb/oracle/statement.hxx | 68 +++++-- odb/oracle/statement.ixx | 30 ++- 6 files changed, 263 insertions(+), 200 deletions(-) diff --git a/odb/oracle/binding.hxx b/odb/oracle/binding.hxx index be201a7..e2d3fa5 100644 --- a/odb/oracle/binding.hxx +++ b/odb/oracle/binding.hxx @@ -38,7 +38,7 @@ namespace odb } binding (bind_type* b, std::size_t n, - std::size_t bt, std::size_t s, auto_handle* st) + std::size_t bt, std::size_t s, sb4* st) : bind (b), count (n), version (0), batch (bt), skip (s), status (st), change_callback (0) @@ -51,7 +51,7 @@ namespace odb std::size_t batch; std::size_t skip; - auto_handle* status; // Batch status array. + sb4* status; // Batch status array. change_callback_type* change_callback; diff --git a/odb/oracle/simple-object-statements.hxx b/odb/oracle/simple-object-statements.hxx index de82c53..18e4270 100644 --- a/odb/oracle/simple-object-statements.hxx +++ b/odb/oracle/simple-object-statements.hxx @@ -276,10 +276,7 @@ namespace odb // Object image. // image_type& - image (std::size_t i = 0) - { - return image_[i]; - } + image (std::size_t i = 0) {return images_[i].obj;} // Insert binding. // @@ -323,7 +320,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_;} @@ -383,7 +380,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_)); return *update_; @@ -425,7 +423,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: @@ -475,8 +473,18 @@ namespace odb extra_statement_cache_ptr extra_statement_cache_; - image_type image_[object_traits::batch]; - auto_handle status_[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]; + sb4 status_[object_traits::batch]; // Select binding. // @@ -508,7 +516,6 @@ namespace odb // Id image binding (only used as a parameter or in RETURNING for // auto ids). 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/oracle/simple-object-statements.txx b/odb/oracle/simple-object-statements.txx index dc226ed..0e42d31 100644 --- a/odb/oracle/simple-object-statements.txx +++ b/odb/oracle/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 + 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/oracle/statement.cxx b/odb/oracle/statement.cxx index 5ca1a98..27fa867 100644 --- a/odb/oracle/statement.cxx +++ b/odb/oracle/statement.cxx @@ -1222,7 +1222,7 @@ namespace odb ~bulk_statement () {} sword bulk_statement:: - execute (size_t n, multiple_exceptions* mex) + execute (size_t n, multiple_exceptions* mex, sb4 ignore_code) { { odb::tracer* t; @@ -1235,6 +1235,13 @@ namespace odb mex_ = mex; OCIError* err (conn_.error_handle ()); + + // We use OCI_BATCH_ERRORS for n == 1 in order to get the batch + // error reporting even for a single parameter set. This makes + // it easier to populate mex since otherwise we would have two + // cases to worry about: batch and non-batch (statement fails + // as a whole). + // sword r (OCIStmtExecute (conn_.handle (), stmt_, err, @@ -1242,14 +1249,17 @@ namespace odb 0, 0, 0, - n == 1 ? OCI_DEFAULT : OCI_BATCH_ERRORS)); + status_ == 0 ? OCI_DEFAULT : OCI_BATCH_ERRORS)); // If the statement failed as a whole, assume no parameter sets - // were attempted. Otherwise, in the batch errors mode, all the - // sets are always attempted (let's hope this is actually true). + // were attempted in case of a batch. Otherwise, in the batch + // errors mode, all the sets are always attempted (let's hope + // this is actually true). // i_ = 0; - n_ = (r == OCI_ERROR || r == OCI_INVALID_HANDLE ? 0 : n); + n_ = (r == OCI_ERROR || r == OCI_INVALID_HANDLE + ? (status_ == 0 ? 1 : 0) + : n); if (mex_ != 0) { @@ -1265,16 +1275,16 @@ namespace odb return r; } - // Initialize the batch status array. + // Initialize the batch status array and extract error information + // for failed parameter sets. // - if (n_ != 1) + if (status_ != 0) { sword r; // Our own return code. // Clear the status array. // - for (size_t i (0); i != n_; ++i) - status_[i].reset (); + memset (status_, sizeof (status_), 0); // @@ TODO allocate per batch stmt (maybe lazily here if NULL). // @@ -1293,10 +1303,10 @@ namespace odb err1.reset (e); } - ub4 en; + ub4 errors; r = OCIAttrGet (stmt_, OCI_HTYPE_STMT, - &en, + &errors, 0, OCI_ATTR_NUM_DML_ERRORS, err1); @@ -1304,9 +1314,10 @@ namespace odb if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) translate_error (err1, r); - cerr << "_NUM_DML_ERRORS: " << en << endl; + cerr << "NUM_DML_ERRORS: " << errors << endl; + errors_ = errors; - for (ub4 i (0); i != en; ++i) + if (errors != 0) { auto_handle err2; @@ -1324,33 +1335,92 @@ namespace odb err2.reset (e); } - OCIError* tmp (err2); - r = OCIParamGet (err, // from - OCI_HTYPE_ERROR, - err1, // diagnostics - reinterpret_cast (&tmp), // to - i); + for (ub4 i (0); i != errors; ++i) + { + { + OCIError* tmp (err2); + r = OCIParamGet (err, // from + OCI_HTYPE_ERROR, + err1, // diagnostics + reinterpret_cast (&tmp), // to + i); - if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) - translate_error (err1, r); + if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) + translate_error (err1, r); + } - ub4 row; - r = OCIAttrGet (err2, - OCI_HTYPE_ERROR, - &row, - 0, - OCI_ATTR_DML_ROW_OFFSET, - err1); + ub4 row; + r = OCIAttrGet (err2, + OCI_HTYPE_ERROR, + &row, + 0, + OCI_ATTR_DML_ROW_OFFSET, + err1); - status_[row].reset (err2.release ()); + if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) + translate_error (err1, r); + + OCIErrorGet (err2, 1, 0, &status_[row], 0, 0, OCI_HTYPE_ERROR); + + if (status_[row] != ignore_code) + translate_error (err2, OCI_ERROR, &conn_, row, mex_); - cerr << "[" << row << "]" << endl; + cerr << "[" << row << "] " << status_[row] << endl; + } } } return r; } + unsigned long long bulk_statement:: + affected (bool unique) + { + unsigned long long rows; + { + ub4 n (0); + OCIError* err (conn_.error_handle ()); + sword r (OCIAttrGet (stmt_, + OCI_HTYPE_STMT, + &n, + 0, + OCI_ATTR_ROW_COUNT, + err)); + + if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) + translate_error (err, r); + + rows = static_cast (n); + } + + 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 (rows) + ? 1 + : result_unknown); + } + else + rows = result_unknown; + } + } + } + + return rows; + } + // // generic_statement // @@ -1911,14 +1981,20 @@ namespace odb size_t insert_statement:: execute (size_t n, multiple_exceptions* mex) { - sword r (bulk_statement::execute (n, mex)); + OCIError* err (conn_.error_handle ()); + + // Ignore ORA-00001 error code, see fetch() below for details. + // + sword r (bulk_statement::execute (n, mex, (ret_ == 0 ? 1 : 0))); // Statement failed as a whole, assume no parameter sets were - // attempted. + // attempted in case of a batch. // if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) { - fetch (r, conn_.error_handle ()); + sb4 e; + OCIErrorGet (err, 1, 0, &e, 0, 0, OCI_HTYPE_ERROR); + fetch (r, e); return n_; } @@ -1943,7 +2019,7 @@ namespace odb } } - if (n == 1) // n and n_ are really the same here. + if (status_ == 0) // Non-batch mode. fetch (OCI_SUCCESS, 0); else fetch (status_[i_] == 0 ? OCI_SUCCESS : OCI_ERROR, status_[i_]); @@ -1951,34 +2027,6 @@ namespace odb return n_; } - void insert_statement:: - fetch (sword r, OCIError* err) - { - result_ = true; - - if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) - { - // An auto-assigned object id should never cause a duplicate primary - // key. - // - if (ret_ == 0) - { - sb4 e; - OCIErrorGet (err, 1, 0, &e, 0, 0, OCI_HTYPE_ERROR); - - // The Oracle error code ORA-00001 indicates unique constraint - // violation, which covers more than just a duplicate primary key. - // Unfortunately, there is nothing more precise that we can use. - // - if (e == 1) - result_ = false; - } - - if (result_) - translate_error (err, r, &conn_, i_, mex_); // Can return. - } - } - bool insert_statement:: result (std::size_t i) { @@ -2007,70 +2055,67 @@ namespace odb update_statement:: update_statement (connection_type& conn, const string& text, + bool unique, bool process, binding& param) - : statement (conn, - text, statement_update, - (process ? ¶m : 0), false) + : bulk_statement (conn, + text, statement_update, + (process ? ¶m : 0), false, + param.batch, param.status), + unique_ (unique) { if (!empty ()) - bind_param (param.bind, param.count); + bind_param (param.bind, param.count, param.batch, param.skip); } update_statement:: update_statement (connection_type& conn, const char* text, + bool unique, bool process, binding& param) - : statement (conn, - text, statement_update, - (process ? ¶m : 0), false) + : bulk_statement (conn, + text, statement_update, + (process ? ¶m : 0), false, + param.batch, param.status), + unique_ (unique) { if (!empty ()) - bind_param (param.bind, param.count); + bind_param (param.bind, param.count, param.batch, param.skip); } - unsigned long long update_statement:: - execute () + size_t update_statement:: + execute (size_t n, multiple_exceptions* mex) { + OCIError* err (conn_.error_handle ()); + sword r (bulk_statement::execute (n, mex)); + + // Statement failed as a whole, assume no parameter sets were + // attempted in case of a batch. + // + if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) { - odb::tracer* t; - if ((t = conn_.transaction_tracer ()) || - (t = conn_.tracer ()) || - (t = conn_.database ().tracer ())) - t->execute (conn_, *this); + translate_error (err, r, &conn_, 0, mex_); // Can return. + return n_; } - OCIError* err (conn_.error_handle ()); - - sword r (OCIStmtExecute (conn_.handle (), - stmt_, - err, - 1, - 0, - 0, - 0, - OCI_DEFAULT)); + // Figure out the affected (matched, not necessarily updated) + // row count. + // + result_ = affected (unique_); - if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) - translate_error (conn_, r); + return n_; + } - ub4 row_count (0); - r = OCIAttrGet (stmt_, - OCI_HTYPE_STMT, - &row_count, - 0, - OCI_ATTR_ROW_COUNT, - err); + unsigned long long update_statement:: + result (std::size_t i) + { + assert ((i_ == i || i_ + 1 == i) && i < n_); - if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) - translate_error (err, r); + if (i != i_) + mex_->current (++i_); // mex cannot be NULL since this is a batch. - // The value of the OCI_ATTR_ROW_COUNT attribute associated with an - // UPDATE statment represents the number of matching rows found. Zero - // indicates no match. - // - return static_cast (row_count); + return result_; } // @@ -2117,85 +2162,28 @@ namespace odb OCIError* err (conn_.error_handle ()); // Statement failed as a whole, assume no parameter sets were - // attempted. + // attempted in case of a batch. // if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) { - fetch (r, err); + translate_error (err, r, &conn_, 0, mex_); // Can return. return n_; } - // Figure out the affected row count. + // Figure out the affected row count. // - { - ub4 rows (0); - r = OCIAttrGet (stmt_, - OCI_HTYPE_STMT, - &rows, - 0, - OCI_ATTR_ROW_COUNT, - err); - - if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) - translate_error (err, r); - - result_ = static_cast (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_] != 0) - p--; - - if (p > 1) // True batch. - { - if (unique_) // Each can affect 0 or 1 row. - { - result_ = (p == static_cast (result_) - ? 1 - : result_unknown); - } - else - result_ = result_unknown; - } - } - } - - if (n == 1) // n and n_ are really the same here. - fetch (OCI_SUCCESS, 0); - else - fetch (status_[i_] == 0 ? OCI_SUCCESS : OCI_ERROR, status_[i_]); + result_ = affected (unique_); return n_; } - void delete_statement:: - fetch (sword r, OCIError* err) - { - if (r == OCI_ERROR || r == OCI_INVALID_HANDLE) - translate_error (err, r, &conn_, i_, mex_); // Can return. - } - unsigned long long delete_statement:: result (std::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_] == 0 ? OCI_SUCCESS : OCI_ERROR, status_[i_]); - } return result_; } diff --git a/odb/oracle/statement.hxx b/odb/oracle/statement.hxx index 6520c31..db032fa 100644 --- a/odb/oracle/statement.hxx +++ b/odb/oracle/statement.hxx @@ -149,13 +149,14 @@ namespace odb virtual ~bulk_statement () = 0; + protected: bulk_statement (connection_type&, const std::string& text, statement_kind, const binding* process, bool optimize, std::size_t batch, - auto_handle* status); + sb4* status); bulk_statement (connection_type&, const char* text, @@ -163,18 +164,25 @@ namespace odb const binding* process, bool optimize, std::size_t batch, - auto_handle* status); + sb4* status); // Call OCIStmtExecute() and set up the batch tracking variables (see - // below). + // below). The ignore_code argument specifies optional error code that + // should not be treated as an error. // sword - execute (std::size_t n, multiple_exceptions*); + execute (std::size_t n, multiple_exceptions*, sb4 ignore_code = 0); + + static const unsigned long long result_unknown = ~0ULL; + + unsigned long long + affected (bool unique); protected: - auto_handle* status_; // Row status array. - std::size_t n_; // Actual batch size. - std::size_t i_; // Position in result. + sb4* status_; // Parameter sets status array. + std::size_t n_; // Actual batch size. + std::size_t i_; // Position in result. + std::size_t errors_; // Number of parameter sets that failed. multiple_exceptions* mex_; }; @@ -353,7 +361,7 @@ namespace odb init (binding& param); void - fetch (sword r, OCIError*); + fetch (sword r, sb4 code); public: // For odb_oracle_returning_*(). binding* ret_; @@ -363,28 +371,62 @@ namespace odb bool result_; }; - class LIBODB_ORACLE_EXPORT update_statement: public statement + class LIBODB_ORACLE_EXPORT update_statement: public bulk_statement { public: virtual ~update_statement (); + // OCI does not expose individual affected row counts for batch + // operations. 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_text, binding& param); update_statement (connection_type& conn, const char* text, + bool unique_hint, bool process_text, binding& param); + // 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); private: update_statement (const update_statement&); update_statement& operator= (const update_statement&); + + private: + bool unique_; + unsigned long long result_; }; class LIBODB_ORACLE_EXPORT delete_statement: public bulk_statement @@ -429,7 +471,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); @@ -439,10 +481,6 @@ namespace odb delete_statement& operator= (const delete_statement&); private: - void - fetch (sword r, OCIError*); - - private: bool unique_; unsigned long long result_; }; diff --git a/odb/oracle/statement.ixx b/odb/oracle/statement.ixx index a41da8a..f1cdb75 100644 --- a/odb/oracle/statement.ixx +++ b/odb/oracle/statement.ixx @@ -8,6 +8,8 @@ namespace odb { namespace oracle { + // bulk_statement + // inline bulk_statement:: bulk_statement (connection_type& c, const std::string& text, @@ -15,7 +17,7 @@ namespace odb const binding* process, bool optimize, std::size_t batch, - auto_handle* status) + sb4* status) : statement (c, text, k, process, optimize), status_ (batch == 1 ? 0 : status) { @@ -28,10 +30,34 @@ namespace odb const binding* process, bool optimize, std::size_t batch, - auto_handle* status) + sb4* status) : statement (c, text, k, process, optimize), status_ (batch == 1 ? 0 : status) { } + + // insert_statement + // + inline void insert_statement:: + fetch (sword r, sb4 code) + { + result_ = true; + + if (r != 0) // OCI_SUCCESS + { + // An auto-assigned object id should never cause a duplicate primary + // key. + // + if (ret_ == 0) + { + // The Oracle error code ORA-00001 indicates unique constraint + // violation, which covers more than just a duplicate primary key. + // Unfortunately, there is nothing more precise that we can use. + // + if (code == 1) + result_ = false; + } + } + } } } -- cgit v1.1