From fc62107005ef86c85c58ae60983666aafeb891c9 Mon Sep 17 00:00:00 2001 From: randomdan Date: Fri, 11 Feb 2011 14:55:07 +0000 Subject: 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 --- project2/tablepatch.cpp | 455 +++++++++++++++++++++++++++++++++--------------- 1 file 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 -- cgit v1.2.3