Support for table patching in different ways according to what the connector says

Introduce two proper methods of bulk update
Tested against PG 8.4, MySQL 5.1 with single and multiple column keys

1 files changed, 313 insertions, 142 deletions

diff --git a/project2/tablepatch.cpp b/project2/tablepatch.cpp
index 540da5e..3533b65 100644
--- a/project2/tablepatch.cpp
+++ b/project2/tablepatch.cpp
@@ -44,59 +44,104 @@ TablePatch::patch(const char * where, const char * order)
 void
 TablePatch::doDeletes(const char * where, const char * order)
 {
-    // -----------------------------------------------------------------
-    // Build SQL to delete keys ----------------------------------------
-    // -----------------------------------------------------------------
-    Buffer toDelSql;
-    toDelSql.appendf("DELETE FROM %s d WHERE (",
-            dest.c_str());
-    foreach (PKI, pk, pki) {
-        if (pki != pk.begin()) {
-            toDelSql.append(", ");
-        }
-        toDelSql.appendf("d.%s",
-                pki->c_str());
-    }
-    // -----------------------------------------------------------------
-    // Build SQL to select keys to delete ------------------------------
-    // -----------------------------------------------------------------
-    toDelSql.append(") IN (SELECT ");
-    foreach (PKI, pk, pki) {
-        if (pki != pk.begin()) {
-            toDelSql.append(", ");
-        }
-        toDelSql.appendf("a.%s",
-                pki->c_str());
-    }
-    toDelSql.appendf(" FROM %s a LEFT OUTER JOIN %s b ON ",
-            dest.c_str(), src.c_str());
-    foreach (PKI, pk, pki) {
-        if (pki != pk.begin()) {
-            toDelSql.append(" AND ");
-        }
-        toDelSql.appendf(" a.%s = b.%s",
-                pki->c_str(), pki->c_str());
-    }
-    foreach (PKI, pk, pki) {
-        if (pki == pk.begin()) {
-            toDelSql.append(" WHERE ");
-        }
-        else {
-            toDelSql.append(" AND ");
-        }
-        toDelSql.appendf(" b.%s IS NULL",
-                pki->c_str());
-    }
-    if (where && *where) {
-        toDelSql.appendf(" AND %s", where);
-    }
-    if (order && *order) {
-        toDelSql.appendf(" ORDER BY %s", order);
-    }
-	toDelSql.append(")");
-	ModifyCommand * del = db.newModifyCommand(toDelSql);
-    del->execute();
-	delete del;
+	switch (db.bulkDeleteStyle()) {
+		case BulkDeleteUsingSubSelect:
+			{
+				// -----------------------------------------------------------------
+				// Build SQL to delete keys ----------------------------------------
+				// -----------------------------------------------------------------
+				Buffer toDelSql;
+				toDelSql.appendf("DELETE FROM %s WHERE (",
+						dest.c_str());
+				foreach (PKI, pk, pki) {
+					if (pki != pk.begin()) {
+						toDelSql.append(", ");
+					}
+					toDelSql.appendf("%s.%s",
+							dest.c_str(),
+							pki->c_str());
+				}
+				// -----------------------------------------------------------------
+				// Build SQL to select keys to delete ------------------------------
+				// -----------------------------------------------------------------
+				toDelSql.append(") IN (SELECT ");
+				foreach (PKI, pk, pki) {
+					if (pki != pk.begin()) {
+						toDelSql.append(", ");
+					}
+					toDelSql.appendf("a.%s",
+							pki->c_str());
+				}
+				toDelSql.appendf(" FROM %s a LEFT OUTER JOIN %s b ON ",
+						dest.c_str(), src.c_str());
+				foreach (PKI, pk, pki) {
+					if (pki != pk.begin()) {
+						toDelSql.append(" AND ");
+					}
+					toDelSql.appendf(" a.%s = b.%s",
+							pki->c_str(), pki->c_str());
+				}
+				foreach (PKI, pk, pki) {
+					if (pki == pk.begin()) {
+						toDelSql.append(" WHERE ");
+					}
+					else {
+						toDelSql.append(" AND ");
+					}
+					toDelSql.appendf(" b.%s IS NULL",
+							pki->c_str());
+				}
+				if (where && *where) {
+					toDelSql.appendf(" AND %s", where);
+				}
+				if (order && *order) {
+					toDelSql.appendf(" ORDER BY %s", order);
+				}
+				toDelSql.append(")");
+				ModifyCommand * del = db.newModifyCommand(toDelSql);
+				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());
+				foreach (PKI, pk, pki) {
+					if (pki != pk.begin()) {
+						toDelSql.append(" AND ");
+					}
+					else {
+						toDelSql.append(" ON ");
+					}
+					toDelSql.appendf(" a.%s = b.%s",
+							pki->c_str(), pki->c_str());
+				}
+				foreach (PKI, pk, pki) {
+					if (pki != pk.begin()) {
+						toDelSql.append(" AND ");
+					}
+					else {
+						toDelSql.append(" WHERE ");
+					}
+					toDelSql.appendf(" b.%s IS NULL",
+							pki->c_str());
+				}
+				if (where && *where) {
+					toDelSql.appendf(" AND %s", where);
+				}
+				if (order && *order) {
+					toDelSql.appendf(" ORDER BY %s", order);
+				}
+				ModifyCommand * del = db.newModifyCommand(toDelSql);
+				del->execute();
+				delete del;
+				break;
+			}
+	}
 }
 
 void
@@ -106,98 +151,224 @@ TablePatch::doUpdates(const char * where, const char * order)
 		// Can't "change" anything... it's all part of the key
 		return;
 	}
-	// -----------------------------------------------------------------
-	// Build SQL for list of updates to perform ------------------------
-	// -----------------------------------------------------------------
-	Buffer toUpdSel;
-	toUpdSel.append("SELECT ");
-	foreach (Columns::const_iterator, cols, col) {
-		if (pk.find(*col) == pk.end()) {
-			toUpdSel.appendf("b.%s, ",
-					col->c_str());
-		}
-	}
-	foreach (Columns::const_iterator, cols, col) {
-		if (pk.find(*col) != pk.end()) {
-			toUpdSel.appendf("b.%s, ",
-					col->c_str());
-		}
-	}
-	toUpdSel.appendf("0 FROM %s a, %s b",
-			dest.c_str(), src.c_str());
-	foreach (PKI, pk, pki) {
-		if (pki == pk.begin()) {
-			toUpdSel.append(" WHERE ");
-		}
-		else {
-			toUpdSel.append(" AND ");
-		}
-		toUpdSel.appendf(" a.%s = b.%s",
-				pki->c_str(), pki->c_str());
-	}
-	if (where && *where) {
-		toUpdSel.appendf(" AND %s", where);
-	}
-	toUpdSel.append(" AND (");
-	bool first = true;
-	foreach (Columns::const_iterator, cols, col) {
-		if (pk.find(*col) == pk.end()) {
-			if (!first) {
-				toUpdSel.append(" OR ");
+	switch (db.bulkUpdateStyle()) {
+		case BulkUpdateByIteration:
+			{
+				// -----------------------------------------------------------------
+				// Build SQL for list of updates to perform ------------------------
+				// -----------------------------------------------------------------
+				Buffer toUpdSel;
+				toUpdSel.append("SELECT ");
+				foreach (Columns::const_iterator, cols, col) {
+					if (pk.find(*col) == pk.end()) {
+						toUpdSel.appendf("b.%s, ",
+								col->c_str());
+					}
+				}
+				foreach (Columns::const_iterator, cols, col) {
+					if (pk.find(*col) != pk.end()) {
+						toUpdSel.appendf("b.%s, ",
+								col->c_str());
+					}
+				}
+				toUpdSel.appendf("0 FROM %s a, %s b",
+						dest.c_str(), src.c_str());
+				foreach (PKI, pk, pki) {
+					if (pki == pk.begin()) {
+						toUpdSel.append(" WHERE ");
+					}
+					else {
+						toUpdSel.append(" AND ");
+					}
+					toUpdSel.appendf(" a.%s = b.%s",
+							pki->c_str(), pki->c_str());
+				}
+				if (where && *where) {
+					toUpdSel.appendf(" AND %s", where);
+				}
+				toUpdSel.append(" AND (");
+				bool first = true;
+				foreach (Columns::const_iterator, cols, col) {
+					if (pk.find(*col) == pk.end()) {
+						if (!first) {
+							toUpdSel.append(" OR ");
+						}
+						first = false;
+						toUpdSel.appendf(
+								" (((CASE WHEN (a.%s IS NULL AND b.%s IS NULL) THEN 1 ELSE 0 END) \
+							+ (CASE WHEN(a.%s = b.%s) THEN 1 ELSE 0 END)) = 0)",
+								col->c_str(), col->c_str(), col->c_str(), col->c_str());
+					}
+				}
+				toUpdSel.append(")");
+				if (order && *order) {
+					toUpdSel.appendf(" ORDER BY %s", order);
+				}
+				// -----------------------------------------------------------------
+				// Build SQL to perform updates ------------------------------------
+				// -----------------------------------------------------------------
+				Buffer updSql;
+				updSql.appendf("UPDATE %s SET ",
+						dest.c_str());
+				first = true;
+				foreach (Columns::const_iterator, cols, col) {
+					if (pk.find(*col) == pk.end()) {
+						if (!first) {
+							updSql.append(", ");
+						}
+						first = false;
+						updSql.appendf(" %s = ?",
+								col->c_str());
+					}
+				}
+				foreach (PKI, pk, pki) {
+					if (pki == pk.begin()) {
+						updSql.append(" WHERE ");
+					}
+					else {
+						updSql.append(" AND ");
+					}
+					updSql.appendf(" %s = ?",
+							pki->c_str());
+				}
+				// -----------------------------------------------------------------
+				// Iterator over update list make changes --------------------------
+				// -----------------------------------------------------------------
+				SelectCommand * toUpd = db.newSelectCommand(toUpdSel);
+				ModifyCommand * upd = db.newModifyCommand(updSql);
+				int cs = cols.size();
+				toUpd->execute();
+				for (int c = 0; c < cs; c += 1) {
+					(*toUpd)[c].rebind(upd, c);
+				}
+				while (toUpd->fetch()) {
+					upd->execute(false);
+				}
+				delete toUpd;
+				delete upd;
 			}
-			first = false;
-			toUpdSel.appendf(
-					" (((CASE WHEN (a.%s IS NULL AND b.%s IS NULL) THEN 1 ELSE 0 END) \
-				+ (CASE WHEN(a.%s = b.%s) THEN 1 ELSE 0 END)) = 0)",
-					col->c_str(), col->c_str(), col->c_str(), col->c_str());
-		}
-	}
-	toUpdSel.append(")");
-	if (order && *order) {
-		toUpdSel.appendf(" ORDER BY %s", order);
-	}
-	// -----------------------------------------------------------------
-	// Build SQL to perform updates ------------------------------------
-	// -----------------------------------------------------------------
-	Buffer updSql;
-	updSql.appendf("UPDATE %s SET ",
-			dest.c_str());
-	first = true;
-	foreach (Columns::const_iterator, cols, col) {
-		if (pk.find(*col) == pk.end()) {
-			if (!first) {
-				updSql.append(", ");
+			break;
+		case BulkUpdateUsingFromSrc:
+			{
+				// -----------------------------------------------------------------
+				// Build SQL for list of updates to perform ------------------------
+				// -----------------------------------------------------------------
+				Buffer updSql;
+				updSql.appendf("UPDATE %s SET ",
+						dest.c_str());
+				bool first = true;
+				foreach (Columns::const_iterator, cols, col) {
+					if (pk.find(*col) == pk.end()) {
+						if (!first) {
+							updSql.append(", ");
+						}
+						first = false;
+						updSql.appendf(" %s = b.%s ",
+								col->c_str(), col->c_str());
+					}
+				}
+				updSql.appendf(" FROM %s b ",
+						src.c_str());
+				foreach (PKI, pk, pki) {
+					if (pki == pk.begin()) {
+						updSql.append(" WHERE ");
+					}
+					else {
+						updSql.append(" AND ");
+					}
+					updSql.appendf(" %s.%s = b.%s ",
+							dest.c_str(), pki->c_str(), pki->c_str());
+				}
+				updSql.append(" AND (");
+				first = true;
+				foreach (Columns::const_iterator, cols, col) {
+					if (pk.find(*col) == pk.end()) {
+						if (!first) {
+							updSql.append("  OR ");
+						}
+						first = false;
+						updSql.appendf(
+								" (((CASE WHEN (%s.%s IS NULL AND b.%s IS NULL) THEN 1 ELSE 0 END) \
+							+ (CASE WHEN(%s.%s = b.%s) THEN 1 ELSE 0 END)) = 0)",
+								dest.c_str(), col->c_str(), col->c_str(),
+								dest.c_str(), col->c_str(), col->c_str());
+					}
+				}
+				updSql.append(")");
+				if (where && *where) {
+					updSql.appendf(" AND %s ", where);
+				}
+				if (order && *order) {
+					updSql.appendf(" ORDER BY %s", order);
+				}
+				// -----------------------------------------------------------------
+				// Execute the bulk update command ---------------------------------
+				// -----------------------------------------------------------------
+				ModifyCommand * upd = db.newModifyCommand(updSql);
+				upd->execute(true);
+				delete upd;
+				break;
+			}
+		case BulkUpdateUsingJoin:
+			{
+				// -----------------------------------------------------------------
+				// Build SQL for list of updates to perform ------------------------
+				// -----------------------------------------------------------------
+				Buffer updSql;
+				updSql.appendf("UPDATE %s a, %s b SET ",
+						dest.c_str(), src.c_str());
+				bool first = true;
+				foreach (Columns::const_iterator, cols, col) {
+					if (pk.find(*col) == pk.end()) {
+						if (!first) {
+							updSql.append(", ");
+						}
+						first = false;
+						updSql.appendf(" a.%s = b.%s ",
+								col->c_str(), col->c_str());
+					}
+				}
+				foreach (PKI, pk, pki) {
+					if (pki == pk.begin()) {
+						updSql.append(" WHERE ");
+					}
+					else {
+						updSql.append(" AND ");
+					}
+					updSql.appendf(" a.%s = b.%s ",
+							pki->c_str(), pki->c_str());
+				}
+				updSql.append(" AND (");
+				first = true;
+				foreach (Columns::const_iterator, cols, col) {
+					if (pk.find(*col) == pk.end()) {
+						if (!first) {
+							updSql.append("  OR ");
+						}
+						first = false;
+						updSql.appendf(
+								" (((CASE WHEN (a.%s IS NULL AND b.%s IS NULL) THEN 1 ELSE 0 END) \
+							+ (CASE WHEN(a.%s = b.%s) THEN 1 ELSE 0 END)) = 0)",
+								col->c_str(), col->c_str(),
+								col->c_str(), col->c_str());
+					}
+				}
+				updSql.append(")");
+				if (where && *where) {
+					updSql.appendf(" AND %s ", where);
+				}
+				if (order && *order) {
+					updSql.appendf(" ORDER BY %s", order);
+				}
+				// -----------------------------------------------------------------
+				// Execute the bulk update command ---------------------------------
+				// -----------------------------------------------------------------
+				ModifyCommand * upd = db.newModifyCommand(updSql);
+				upd->execute(true);
+				delete upd;
+				break;
 			}
-			first = false;
-			updSql.appendf(" %s = ?",
-					col->c_str());
-		}
-	}
-	foreach (PKI, pk, pki) {
-		if (pki == pk.begin()) {
-			updSql.append(" WHERE ");
-		}
-		else {
-			updSql.append(" AND ");
-		}
-		updSql.appendf(" %s = ?",
-				pki->c_str());
-	}
-	// -----------------------------------------------------------------
-	// Iterator over update list make changes --------------------------
-	// -----------------------------------------------------------------
-	SelectCommand * toUpd = db.newSelectCommand(toUpdSel);
-	ModifyCommand * upd = db.newModifyCommand(updSql);
-	int cs = cols.size();
-	toUpd->execute();
-	for (int c = 0; c < cs; c += 1) {
-		(*toUpd)[c].rebind(upd, c);
-	}
-	while (toUpd->fetch()) {
-		upd->execute(false);
 	}
-	delete toUpd;
-	delete upd;
 }
 
 void