diff options
| -rw-r--r-- | project2/tablepatch.cpp | 455 |
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 |