summaryrefslogtreecommitdiff
path: root/project2/tablepatch.cpp
diff options
context:
space:
mode:
Diffstat (limited to 'project2/tablepatch.cpp')
-rw-r--r--project2/tablepatch.cpp455
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