summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorrandomdan <randomdan@localhost>2011-02-11 14:55:07 +0000
committerrandomdan <randomdan@localhost>2011-02-11 14:55:07 +0000
commita51b4d64085a7ee05c3c57b5ca02b41fbce0962d (patch)
tree3b59b115d271a22335562773c4009c0eb4b0fd3f
parentFix free function in xslRows for xmlXPathObject (diff)
downloadproject2-a51b4d64085a7ee05c3c57b5ca02b41fbce0962d.tar.bz2
project2-a51b4d64085a7ee05c3c57b5ca02b41fbce0962d.tar.xz
project2-a51b4d64085a7ee05c3c57b5ca02b41fbce0962d.zip
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
-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