Much improved flexibility around partial/parameterised SQL merges

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 <list>
 
 /// 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<SqlText, Glib::ustring>, &writers, _1),
-			boost::bind(&appendNewFromNode, &writers, &filters, _1));
+	if (s) {
+		s->composeWithCallbacks(
+				boost::bind(&appendNew<SqlText, Glib::ustring>, &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 <selectcommand.h>
 
 class TablePatch {
-    public:
-        typedef std::string Table;
-        typedef std::string Column;
-        typedef std::set<Column> 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<Column> 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;