From a21a91a0559ef7a19b5eb2faa74453b05f244a6b Mon Sep 17 00:00:00 2001 From: randomdan Date: Tue, 28 Feb 2012 22:46:30 +0000 Subject: Much improved flexibility around partial/parameterised SQL merges --- project2/common/scripts.h | 2 +- project2/sql/sqlMergeTask.cpp | 25 +++++- project2/sql/sqlMergeTask.h | 9 +- project2/sql/sqlWriter.cpp | 8 +- project2/sql/tablepatch.cpp | 188 ++++++++++++++++++++++----------------- project2/sql/tablepatch.h | 64 +++++++------ project2/xml/xmlScriptParser.cpp | 7 +- project2/xml/xmlScriptParser.h | 2 +- 8 files changed, 186 insertions(+), 119 deletions(-) diff --git a/project2/common/scripts.h b/project2/common/scripts.h index 405056e..972c23f 100644 --- a/project2/common/scripts.h +++ b/project2/common/scripts.h @@ -31,7 +31,7 @@ class ScriptNode : public IntrusivePtrBase { virtual std::string get_name() const = 0; // Child called name - virtual ScriptNodePtr child(const Glib::ustring & name) const = 0; + virtual ScriptNodePtr child(const Glib::ustring & name, bool required = true) const = 0; // All children virtual ScriptNodes children() const = 0; // All children in sub diff --git a/project2/sql/sqlMergeTask.cpp b/project2/sql/sqlMergeTask.cpp index 0182f1e..2c2cdd9 100644 --- a/project2/sql/sqlMergeTask.cpp +++ b/project2/sql/sqlMergeTask.cpp @@ -45,10 +45,13 @@ DECLARE_LOADER("sqlmergeinsert", SqlMergeInsert); SqlMergeTask::SqlMergeTask(ScriptNodePtr p) : SourceObject(p), Task(p), - updateWhere(p, "updatewhere", Null()), + updateWhere(p->child("updatewhere", false)), patchOrder(p, "patchorder", Null()), earlyKeys(p, "earlykeys", false), useView(p, "useview", false), + doDelete(p, "delete", true), + doUpdate(p, "update", true), + doInsert(p, "insert", true), tempTableCreated(false), sqlCommand(NULL), insCmd(NULL), @@ -128,10 +131,13 @@ SqlMergeTask::execute() const colNames.insert(c->column); } TablePatch tp(*destdb, dtablet, dtable, colNames); + tp.doDelete = doDelete(); + tp.doUpdate = doUpdate(); + tp.doInsert = doInsert(); BOOST_FOREACH(const Keys::value_type & k, keys) { tp.addKey(k); } - tp.patch(updateWhere(), patchOrder()); + tp.patch(this, patchOrder()); dropTempTable(); } @@ -303,6 +309,8 @@ SqlMergeTask::copyToTempTable() const } ins.appendf(" FROM (%s) tmp_src", sqlCommand->getSqlFor("").c_str()); DB::ModifyCommand * cttt = destdb->newModifyCommand(ins); + unsigned int off = 0; + sqlCommand->bindParams(cttt, off); cttt->execute(); delete cttt; } @@ -324,3 +332,16 @@ SqlMergeTask::copyToTempTable() const } } +void +SqlMergeTask::appendWhere(Buffer * buf) const +{ + buf->appendf(" AND %s ", updateWhere.getSqlFor("").c_str()); +} + +void +SqlMergeTask::bindWhere(DB::Command * cmd) const +{ + unsigned int off = 0; + updateWhere.bindParams(cmd, off); +} + diff --git a/project2/sql/sqlMergeTask.h b/project2/sql/sqlMergeTask.h index dc23936..bd9f050 100644 --- a/project2/sql/sqlMergeTask.h +++ b/project2/sql/sqlMergeTask.h @@ -15,7 +15,7 @@ #include /// Project2 component merge arbitrary data into an RDBMS table -class SqlMergeTask : public Task { +class SqlMergeTask : public Task, public TablePatch::WhereProvider { public: typedef std::string Table; typedef std::string Column; @@ -47,16 +47,21 @@ class SqlMergeTask : public Task { Columns cols; Keys keys; Keys indexes; - const Variable updateWhere; + const DynamicSql::SqlCommand updateWhere; const Variable patchOrder; const Variable earlyKeys; const Variable useView; + const Variable doDelete; + const Variable doUpdate; + const Variable doInsert; private: virtual void copyToTempTable() const; void createTempTable() const; void dropTempTable() const; void createTempKey() const; + void appendWhere(Buffer *) const; + void bindWhere(DB::Command *) const; mutable bool tempTableCreated; typedef ANONSTORAGEOF(Iterate) Sources; diff --git a/project2/sql/sqlWriter.cpp b/project2/sql/sqlWriter.cpp index 867e1bd..f67eb1c 100644 --- a/project2/sql/sqlWriter.cpp +++ b/project2/sql/sqlWriter.cpp @@ -35,9 +35,11 @@ appendNewFromNode(DynamicSql::Writers * w, DynamicSql::SqlCommand::Filters * fs, DynamicSql::SqlCommand::SqlCommand(ScriptNodePtr s) { - s->composeWithCallbacks( - boost::bind(&appendNew, &writers, _1), - boost::bind(&appendNewFromNode, &writers, &filters, _1)); + if (s) { + s->composeWithCallbacks( + boost::bind(&appendNew, &writers, _1), + boost::bind(&appendNewFromNode, &writers, &filters, _1)); + } } Glib::ustring diff --git a/project2/sql/tablepatch.cpp b/project2/sql/tablepatch.cpp index 8443c7b..733f94f 100644 --- a/project2/sql/tablepatch.cpp +++ b/project2/sql/tablepatch.cpp @@ -11,41 +11,50 @@ using namespace DB; TablePatch::TablePatch(const Connection & wdb, const TablePatch::Table & s, const TablePatch::Table & d, const TablePatch::Columns & c) : - src(s), - dest(d), + doDelete(true), + doUpdate(true), + doInsert(true), + src(s), + dest(d), cols(c), - db(wdb) + db(wdb) { - if (!src.length()) { - throw PatchCheckFailure(); - } - if (!dest.length()) { - throw PatchCheckFailure(); - } - if (!db.inTx()) { - throw PatchCheckFailure(); - } + if (!src.length()) { + throw PatchCheckFailure(); + } + if (!dest.length()) { + throw PatchCheckFailure(); + } + if (!db.inTx()) { + throw PatchCheckFailure(); + } } void TablePatch::addKey(const TablePatch::Column & c) { - pk.insert(c); + pk.insert(c); } void -TablePatch::patch(const char * where, const char * order) +TablePatch::patch(const WhereProvider * where, const char * order) { - if (pk.size() == 0) { - throw PatchCheckFailure(); - } - doDeletes(where, order); - doUpdates(where, order); - doInserts(order); + if (pk.size() == 0) { + throw PatchCheckFailure(); + } + if (doDelete) { + doDeletes(where, order); + } + if (doUpdate) { + doUpdates(where, order); + } + if (doInsert) { + doInserts(order); + } } void -TablePatch::doDeletes(const char * where, const char * order) +TablePatch::doDeletes(const WhereProvider * where, const char * order) { switch (db.bulkDeleteStyle()) { case BulkDeleteUsingSubSelect: @@ -94,14 +103,18 @@ TablePatch::doDeletes(const char * where, const char * order) toDelSql.appendf(" b.%s IS NULL", pki->c_str()); } - if (where && *where) { - toDelSql.appendf(" AND %s", where); + if (where) { + //toDelSql.appendf(" AND %s", where); + where->appendWhere(&toDelSql); } if (order && *order) { toDelSql.appendf(" ORDER BY %s", order); } toDelSql.append(")"); ModifyCommand * del = db.newModifyCommand(toDelSql); + if (where) { + where->bindWhere(del); + } del->execute(); delete del; break; @@ -133,13 +146,17 @@ TablePatch::doDeletes(const char * where, const char * order) toDelSql.appendf(" b.%s IS NULL", pki->c_str()); } - if (where && *where) { - toDelSql.appendf(" AND %s", where); + if (where) { + //toDelSql.appendf(" AND %s", where); + where->appendWhere(&toDelSql); } if (order && *order) { toDelSql.appendf(" ORDER BY %s", order); } ModifyCommand * del = db.newModifyCommand(toDelSql); + if (where) { + where->bindWhere(del); + } del->execute(); delete del; break; @@ -148,7 +165,7 @@ TablePatch::doDeletes(const char * where, const char * order) } void -TablePatch::doUpdates(const char * where, const char * order) +TablePatch::doUpdates(const WhereProvider * where, const char * order) { if (cols.size() == pk.size()) { // Can't "change" anything... it's all part of the key @@ -186,8 +203,8 @@ TablePatch::doUpdates(const char * where, const char * order) toUpdSel.appendf(" a.%s = b.%s", pki->c_str(), pki->c_str()); } - if (where && *where) { - toUpdSel.appendf(" AND %s", where); + if (where) { + where->appendWhere(&toUpdSel); } toUpdSel.append(" AND ("); bool first = true; @@ -238,6 +255,9 @@ TablePatch::doUpdates(const char * where, const char * order) // Iterator over update list make changes -------------------------- // ----------------------------------------------------------------- SelectCommand * toUpd = db.newSelectCommand(toUpdSel); + if (where) { + where->bindWhere(toUpd); + } ModifyCommand * upd = db.newModifyCommand(updSql); int cs = cols.size(); toUpd->execute(); @@ -298,8 +318,8 @@ TablePatch::doUpdates(const char * where, const char * order) } } updSql.append(")"); - if (where && *where) { - updSql.appendf(" AND %s ", where); + if (where) { + where->appendWhere(&updSql); } if (order && *order) { updSql.appendf(" ORDER BY %s", order); @@ -308,6 +328,9 @@ TablePatch::doUpdates(const char * where, const char * order) // Execute the bulk update command --------------------------------- // ----------------------------------------------------------------- ModifyCommand * upd = db.newModifyCommand(updSql); + if (where) { + where->bindWhere(upd); + } upd->execute(true); delete upd; break; @@ -357,8 +380,8 @@ TablePatch::doUpdates(const char * where, const char * order) } } updSql.append(")"); - if (where && *where) { - updSql.appendf(" AND %s ", where); + if (where) { + where->appendWhere(&updSql); } if (order && *order) { updSql.appendf(" ORDER BY %s", order); @@ -367,6 +390,9 @@ TablePatch::doUpdates(const char * where, const char * order) // Execute the bulk update command --------------------------------- // ----------------------------------------------------------------- ModifyCommand * upd = db.newModifyCommand(updSql); + if (where) { + where->bindWhere(upd); + } upd->execute(true); delete upd; break; @@ -377,55 +403,55 @@ TablePatch::doUpdates(const char * where, const char * order) void TablePatch::doInserts(const char * order) { - // ----------------------------------------------------------------- - // Build SQL for copying new records ------------------------------- - // ----------------------------------------------------------------- + // ----------------------------------------------------------------- + // Build SQL for copying new records ------------------------------- + // ----------------------------------------------------------------- Buffer toInsSql; - toInsSql.appendf("INSERT INTO %s", - dest.c_str()); - foreach (Columns::const_iterator, cols, col) { - if (col == cols.begin()) { - toInsSql.append("("); - } - else { - toInsSql.append(", "); - } - toInsSql.appendf("%s", - col->c_str()); - } - toInsSql.append(") SELECT "); - foreach (Columns::const_iterator, cols, col) { - if (col != cols.begin()) { - toInsSql.append(", "); - } - toInsSql.appendf("b.%s", - col->c_str()); - } - toInsSql.appendf(" FROM %s b LEFT OUTER JOIN %s a", - src.c_str(), dest.c_str()); - foreach (PKI, pk, pki) { - if (pki == pk.begin()) { - toInsSql.append(" ON "); - } - else { - toInsSql.append(" AND "); - } - toInsSql.appendf(" a.%s = b.%s", - pki->c_str(), pki->c_str()); - } - foreach (PKI, pk, pki) { - if (pki == pk.begin()) { - toInsSql.append(" WHERE "); - } - else { - toInsSql.append(" AND "); - } - toInsSql.appendf(" a.%s IS NULL", - pki->c_str()); - } - if (order && *order) { - toInsSql.appendf(" ORDER BY %s", order); - } + toInsSql.appendf("INSERT INTO %s", + dest.c_str()); + foreach (Columns::const_iterator, cols, col) { + if (col == cols.begin()) { + toInsSql.append("("); + } + else { + toInsSql.append(", "); + } + toInsSql.appendf("%s", + col->c_str()); + } + toInsSql.append(") SELECT "); + foreach (Columns::const_iterator, cols, col) { + if (col != cols.begin()) { + toInsSql.append(", "); + } + toInsSql.appendf("b.%s", + col->c_str()); + } + toInsSql.appendf(" FROM %s b LEFT OUTER JOIN %s a", + src.c_str(), dest.c_str()); + foreach (PKI, pk, pki) { + if (pki == pk.begin()) { + toInsSql.append(" ON "); + } + else { + toInsSql.append(" AND "); + } + toInsSql.appendf(" a.%s = b.%s", + pki->c_str(), pki->c_str()); + } + foreach (PKI, pk, pki) { + if (pki == pk.begin()) { + toInsSql.append(" WHERE "); + } + else { + toInsSql.append(" AND "); + } + toInsSql.appendf(" a.%s IS NULL", + pki->c_str()); + } + if (order && *order) { + toInsSql.appendf(" ORDER BY %s", order); + } ModifyCommand * ins = db.newModifyCommand(toInsSql); ins->execute(); delete ins; @@ -434,6 +460,6 @@ TablePatch::doInserts(const char * order) const char * TablePatch::PatchCheckFailure::what() const throw() { - return "Santiy checks failed: check table names and keys"; + return "Santiy checks failed: check table names and keys"; } diff --git a/project2/sql/tablepatch.h b/project2/sql/tablepatch.h index 0174294..515c8bd 100644 --- a/project2/sql/tablepatch.h +++ b/project2/sql/tablepatch.h @@ -9,33 +9,43 @@ #include class TablePatch { - public: - typedef std::string Table; - typedef std::string Column; - typedef std::set Columns; - typedef Columns PrimaryKey; - typedef PrimaryKey::const_iterator PKI; - - class PatchCheckFailure : public std::exception { - public: - const char * what() const throw(); - }; - - TablePatch(const DB::Connection & db, const Table & src, const Table & dest, const Columns & cols); - - void addKey(const Column & col); - void patch(const char * where, const char * order); - - private: - void doDeletes(const char * where, const char * order); - void doUpdates(const char * where, const char * order); - void doInserts(const char * order); - - Table src; - Table dest; - PrimaryKey pk; - Columns cols; - const DB::Connection &db; + public: + typedef std::string Table; + typedef std::string Column; + typedef std::set Columns; + typedef Columns PrimaryKey; + typedef PrimaryKey::const_iterator PKI; + + class PatchCheckFailure : public std::exception { + public: + const char * what() const throw(); + }; + + class WhereProvider { + public: + virtual void appendWhere(Buffer *) const = 0; + virtual void bindWhere(DB::Command *) const = 0; + }; + + TablePatch(const DB::Connection & db, const Table & src, const Table & dest, const Columns & cols); + + void addKey(const Column & col); + void patch(const WhereProvider * where, const char * order); + + bool doDelete; + bool doUpdate; + bool doInsert; + + private: + void doDeletes(const WhereProvider * where, const char * order); + void doUpdates(const WhereProvider * where, const char * order); + void doInserts(const char * order); + + Table src; + Table dest; + PrimaryKey pk; + Columns cols; + const DB::Connection &db; }; #endif diff --git a/project2/xml/xmlScriptParser.cpp b/project2/xml/xmlScriptParser.cpp index af69566..f65e7d5 100644 --- a/project2/xml/xmlScriptParser.cpp +++ b/project2/xml/xmlScriptParser.cpp @@ -133,7 +133,7 @@ XmlScriptNode::children() const } ScriptNodePtr -XmlScriptNode::child(const Glib::ustring & n) const +XmlScriptNode::child(const Glib::ustring & n, bool required) const { const xmlpp::Element::NodeList cs = element->get_children(n); if (cs.size() == 1) { @@ -141,7 +141,10 @@ XmlScriptNode::child(const Glib::ustring & n) const return new XmlScriptNode(c, script); } } - throw ValueNotFound(n); + if (required) { + throw ValueNotFound(n); + } + return NULL; } ScriptNode::ScriptNodes diff --git a/project2/xml/xmlScriptParser.h b/project2/xml/xmlScriptParser.h index 9862f49..0ea2a37 100644 --- a/project2/xml/xmlScriptParser.h +++ b/project2/xml/xmlScriptParser.h @@ -18,7 +18,7 @@ class XmlScriptNode : public ScriptNode { const xmlpp::Element * xmlElement() const; bool componentNamespace() const; std::string get_name() const; - ScriptNodePtr child(const Glib::ustring&) const; + ScriptNodePtr child(const Glib::ustring&, bool required) const; ScriptNodes children() const; ScriptNodes childrenIn(const Glib::ustring&) const; bool valueExists(const Glib::ustring&) const; -- cgit v1.2.3