Add support for updating instead of deleting when merging records

4 files changed, 46 insertions, 34 deletions

diff --git a/project2/sql/sqlMergeTask.cpp b/project2/sql/sqlMergeTask.cpp
index 2bd767b..f70e1ae 100644
--- a/project2/sql/sqlMergeTask.cpp
+++ b/project2/sql/sqlMergeTask.cpp
@@ -47,6 +47,7 @@ DECLARE_LOADER("sqlmergeinsert", SqlMergeInsert);
 SqlMergeTask::SqlMergeTask(const std::string & datasource, const std::string & target) :
 	SourceObject(__PRETTY_FUNCTION__),
 	Task(NULL),
+	insteadOfDelete(NULL),
 	updateWhere(NULL),
 	patchOrder(NULL),
 	earlyKeys(VariableType(false)),
@@ -67,6 +68,8 @@ SqlMergeTask::SqlMergeTask(const std::string & datasource, const std::string & t
 SqlMergeTask::SqlMergeTask(ScriptNodePtr p) :
 	SourceObject(p),
 	Task(p),
+	insteadOfDelete([=](ScriptNodePtr c){
+			return c ? new DynamicSql::SqlCommand(c) : NULL; }(p->child("insteadOfDelete", false))),
 	updateWhere([=](ScriptNodePtr c){
 			return c ? new DynamicSql::SqlCommand(c) : NULL; }(p->child("updatewhere", false))),
 	patchOrder([=](ScriptNodePtr c){
@@ -167,7 +170,7 @@ SqlMergeTask::execute(ExecContext * ec) const
 	BOOST_FOREACH(const Keys::value_type & k, keys) {
 		tp.addKey(k);
 	}
-	tp.patch(ec, updateWhere, patchOrder);
+	tp.patch(ec, insteadOfDelete, updateWhere, patchOrder);
 	dropTempTable();
 }
 
diff --git a/project2/sql/sqlMergeTask.h b/project2/sql/sqlMergeTask.h
index 626814c..eec98cd 100644
--- a/project2/sql/sqlMergeTask.h
+++ b/project2/sql/sqlMergeTask.h
@@ -50,6 +50,7 @@ class SqlMergeTask : public Task {
 		Columns cols;
 		Keys keys;
 		Keys indexes;
+		DynamicSql::SqlWriterPtr insteadOfDelete;
 		DynamicSql::SqlWriterPtr updateWhere;
 		DynamicSql::SqlWriterPtr patchOrder;
 		Variable earlyKeys;
diff --git a/project2/sql/tablepatch.cpp b/project2/sql/tablepatch.cpp
index 6836829..f49c4ee 100644
--- a/project2/sql/tablepatch.cpp
+++ b/project2/sql/tablepatch.cpp
@@ -37,13 +37,13 @@ TablePatch::addKey(const TablePatch::Column & c)
 }
 
 void
-TablePatch::patch(ExecContext * ec, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order)
+TablePatch::patch(ExecContext * ec, DynamicSql::SqlWriterPtr insteadOfDelete, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order)
 {
 	if (pk.empty()) {
 		throw PatchCheckFailure();
 	}
 	if (doDelete) {
-		doDeletes(ec, where, order);
+		doDeletes(ec, insteadOfDelete, where, order);
 	}
 	if (doUpdate) {
 		doUpdates(ec, where, order);
@@ -54,17 +54,25 @@ TablePatch::patch(ExecContext * ec, DynamicSql::SqlWriterPtr where, DynamicSql::
 }
 
 void
-TablePatch::doDeletes(ExecContext * ec, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order)
+TablePatch::doDeletes(ExecContext * ec, DynamicSql::SqlWriterPtr insteadOfDelete, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order)
 {
+	Buffer toDelSql;
 	switch (db.bulkDeleteStyle()) {
 		case BulkDeleteUsingSubSelect:
 			{
 				// -----------------------------------------------------------------
 				// Build SQL to delete keys ----------------------------------------
 				// -----------------------------------------------------------------
-				Buffer toDelSql;
-				toDelSql.appendf("DELETE FROM %s WHERE (",
-						dest.c_str());
+				if (insteadOfDelete) {
+					toDelSql.appendf("UPDATE %s ",
+							dest.c_str());
+					insteadOfDelete->writeSql(toDelSql);
+					toDelSql.append(" WHERE (");
+				}
+				else {
+					toDelSql.appendf("DELETE FROM %s WHERE (",
+							dest.c_str());
+				}
 				foreach (PKI, pk, pki) {
 					if (pki != pk.begin()) {
 						toDelSql.append(", ");
@@ -112,25 +120,22 @@ TablePatch::doDeletes(ExecContext * ec, DynamicSql::SqlWriterPtr where, DynamicS
 					order->writeSql(toDelSql);
 				}
 				toDelSql.append(")");
-				ModifyCommand * del = db.newModifyCommand(toDelSql);
-				unsigned int offset = 0;
-				if (where) {
-					where->bindParams(ec, del, offset);
-				}
-				if (order) {
-					order->bindParams(ec, del, offset);
-				}
-				del->execute();
-				delete del;
 				break;
 			}
 		case BulkDeleteUsingUsingAlias:
 		case BulkDeleteUsingUsing:
 			{
-				Buffer toDelSql;
-				toDelSql.appendf("DELETE FROM %s USING %s a LEFT OUTER JOIN %s b ",
-						(db.bulkDeleteStyle() == BulkDeleteUsingUsingAlias ? "a" : dest.c_str()),
-						dest.c_str(), src.c_str());
+				if (insteadOfDelete) {
+					toDelSql.appendf("UPDATE %s a ",
+							dest.c_str());
+				}
+				else {
+					toDelSql.appendf("DELETE FROM %s USING %s a ",
+							(db.bulkDeleteStyle() == BulkDeleteUsingUsingAlias ? "a" : dest.c_str()),
+							dest.c_str());
+				}
+				toDelSql.appendf(" LEFT OUTER JOIN %s b ",
+						src.c_str());
 				foreach (PKI, pk, pki) {
 					if (pki != pk.begin()) {
 						toDelSql.append(" AND ");
@@ -138,9 +143,12 @@ TablePatch::doDeletes(ExecContext * ec, DynamicSql::SqlWriterPtr where, DynamicS
 					else {
 						toDelSql.append(" ON ");
 					}
-					toDelSql.appendf(" a.%s = b.%s",
+					toDelSql.appendf(" a.%s = b.%s ",
 							pki->c_str(), pki->c_str());
 				}
+				if (insteadOfDelete) {
+					insteadOfDelete->writeSql(toDelSql);	
+				}
 				foreach (PKI, pk, pki) {
 					if (pki != pk.begin()) {
 						toDelSql.append(" AND ");
@@ -159,19 +167,19 @@ TablePatch::doDeletes(ExecContext * ec, DynamicSql::SqlWriterPtr where, DynamicS
 					toDelSql.append(" ORDER BY ");
 					order->writeSql(toDelSql);
 				}
-				ModifyCommand * del = db.newModifyCommand(toDelSql);
-				unsigned int offset = 0;
-				if (where) {
-					where->bindParams(ec, del, offset);
-				}
-				if (order) {
-					order->bindParams(ec, del, offset);
-				}
-				del->execute();
-				delete del;
 				break;
 			}
 	}
+	ModifyCommand * del = db.newModifyCommand(toDelSql);
+	unsigned int offset = 0;
+	if (where) {
+		where->bindParams(ec, del, offset);
+	}
+	if (order) {
+		order->bindParams(ec, del, offset);
+	}
+	del->execute();
+	delete del;
 }
 
 void
diff --git a/project2/sql/tablepatch.h b/project2/sql/tablepatch.h
index 490450b..df574bf 100644
--- a/project2/sql/tablepatch.h
+++ b/project2/sql/tablepatch.h
@@ -26,14 +26,14 @@ class TablePatch {
 		TablePatch(const DB::Connection & db, const Table & src, const Table & dest, const Columns & cols);
 
 		void            addKey(const Column & col);
-		void            patch(ExecContext *, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order);
+		void            patch(ExecContext *, DynamicSql::SqlWriterPtr insteadOfDelete, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order);
 
 		bool						doDelete;
 		bool						doUpdate;
 		bool						doInsert;
 
 	private:
-		void            doDeletes(ExecContext *, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order);
+		void            doDeletes(ExecContext *, DynamicSql::SqlWriterPtr insteadOfDelete, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order);
 		void            doUpdates(ExecContext *, DynamicSql::SqlWriterPtr where, DynamicSql::SqlWriterPtr order);
 		void            doInserts(ExecContext *, DynamicSql::SqlWriterPtr order);