From 8ec7fbc3c3e848807de9d9513348eb417ae598a0 Mon Sep 17 00:00:00 2001 From: randomdan Date: Fri, 8 Oct 2010 21:11:37 +0000 Subject: Loosely tested code cull and tidy up --- project2/sqlMergeTask.cpp | 76 +++++++++++++++++++++++------------------------ project2/tablepatch.cpp | 65 +++++++++++++++++++--------------------- project2/tablepatch.h | 1 - 3 files changed, 68 insertions(+), 74 deletions(-) diff --git a/project2/sqlMergeTask.cpp b/project2/sqlMergeTask.cpp index 2863b7c..f8177d5 100644 --- a/project2/sqlMergeTask.cpp +++ b/project2/sqlMergeTask.cpp @@ -98,8 +98,8 @@ _SqlMergeTask::execute() const colNames.insert(c->column); } TablePatch tp(*destdb, dtablet, dtable, colNames); - foreach(Keys::const_iterator, keys, k) { - tp.addKey(*k); + BOOST_FOREACH(const Keys::value_type & k, keys) { + tp.addKey(k); } tp.patch(updateWhere->empty() ? NULL : updateWhere->c_str(), patchOrder.empty() ? NULL : patchOrder.c_str()); @@ -149,43 +149,41 @@ _SqlMergeTask::createTempKey() const if (useView) return; /* Primary key */ Buffer idx; - idx.appendf("ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(", - dtablet.c_str(), dtablet.c_str()); - foreach(Keys::const_iterator, keys, k) { - if (k != keys.begin()) { - idx.append(", "); - } - idx.appendf("%s", k->c_str()); - } - idx.append(")"); - ModifyCommand(*destdb, idx.c_str()).execute(); + idx.appendf("ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(%s)", + dtablet.c_str(), dtablet.c_str(), + boost::algorithm::join(keys, ", ").c_str()); + ModifyCommand(*destdb, idx).execute(); /* Indexes */ int n = 0; - foreach(Keys::const_iterator, indexes, i) { + BOOST_FOREACH(const Keys::value_type & i, indexes) { ModifyCommand(*destdb, stringf( "CREATE INDEX idx_%s_%d ON %s(%s)", - dtablet.c_str(), n, dtablet.c_str(), i->c_str())).execute(); + dtablet.c_str(), n, dtablet.c_str(), i.c_str())).execute(); n += 1; } } ModifyCommand * _SqlMergeTask::insertCommand() const { - Buffer insC; - Buffer insV; - insC.appendf("INSERT INTO %s(", dtablet.c_str()); - insV.append(") VALUES ("); + Buffer ins; + ins.appendf("INSERT INTO %s(", dtablet.c_str()); foreach(Columns::const_iterator, cols, c) { if (c != cols.begin()) { - insC.append(", "); - insV.append(", "); + ins.append(", "); } - insC.append((*c)->mapcolumn); - insV.append("?"); + ins.append((*c)->mapcolumn); } - Buffer res; - res.appendf("%s%s)", insC.c_str(), insV.c_str()); - return new ModifyCommand(*destdb, res.c_str()); + ins.append(") VALUES ("); + foreach(Columns::const_iterator, cols, c) { + if (c == cols.begin()) { + ins.append("?"); + } + else { + ins.append(", ?"); + } + } + ins.append(")"); + return new ModifyCommand(*destdb, ins); } class _Populate : public _NoOutputExecute { @@ -253,21 +251,23 @@ _SqlMergeTask::copyToTempTable() const i.second->execute(); } BOOST_FOREACH(const std::string & sql, sqls) { - Buffer insC; - Buffer insV; - insC.appendf("INSERT INTO %s(", dtablet.c_str()); - insV.append(") SELECT "); - BOOST_FOREACH(const TargetColumnPtr & c, cols) { - if (c != *cols.begin()) { - insC.append(", "); - insV.append(", "); + Buffer ins; + ins.appendf("INSERT INTO %s(", dtablet.c_str()); + foreach(Columns::const_iterator, cols, c) { + if (c != cols.begin()) { + ins.append(", "); + } + ins.append((*c)->column); + } + ins.append(") SELECT "); + foreach(Columns::const_iterator, cols, c) { + if (c != cols.begin()) { + ins.append(", "); } - insC.append(c->column); - insV.append(c->column); + ins.append((*c)->column); } - Buffer res; - res.appendf("%s%s FROM (%s) tmp_src", insC.c_str(), insV.c_str(), sql.c_str()); - ModifyCommand(*destdb, res.c_str()).execute(); + ins.appendf(" FROM (%s) tmp_src", sql.c_str()); + ModifyCommand(*destdb, ins).execute(); } BOOST_FOREACH(Columns::value_type c, cols) { if (!c->maptable.empty()) { diff --git a/project2/tablepatch.cpp b/project2/tablepatch.cpp index 0decc1e..9ee5165 100644 --- a/project2/tablepatch.cpp +++ b/project2/tablepatch.cpp @@ -5,6 +5,7 @@ #include #include #include +#include TablePatch::TablePatch(const Connection & wdb, const TablePatch::Table & s, const TablePatch::Table & d, const TablePatch::Columns & c) : @@ -45,10 +46,22 @@ void TablePatch::doDeletes(const char * where, const char * order) { // ----------------------------------------------------------------- - // Build SQL to select keys to delete ------------------------------ + // Build SQL to delete keys ---------------------------------------- // ----------------------------------------------------------------- Buffer toDelSql; - toDelSql.append("SELECT "); + 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(", "); @@ -56,13 +69,10 @@ TablePatch::doDeletes(const char * where, const char * order) toDelSql.appendf("a.%s", pki->c_str()); } - toDelSql.appendf(" FROM %s a LEFT OUTER JOIN %s b", + 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(" ON "); - } - else { + if (pki != pk.begin()) { toDelSql.append(" AND "); } toDelSql.appendf(" a.%s = b.%s", @@ -84,21 +94,8 @@ TablePatch::doDeletes(const char * where, const char * order) if (order) { toDelSql.appendf(" ORDER BY %s", order); } - // ----------------------------------------------------------------- - // Build SQL to delete keys ---------------------------------------- - // ----------------------------------------------------------------- - Buffer delSql; - delSql.appendf("DELETE FROM %s d WHERE (", - dest.c_str()); - foreach (PKI, pk, pki) { - if (pki != pk.begin()) { - delSql.append(", "); - } - delSql.appendf("d.%s", - pki->c_str()); - } - delSql.appendf(") IN (%s)", toDelSql.c_str()); - ModifyCommand del(db, delSql.c_str()); + toDelSql.append(")"); + ModifyCommand del(db, toDelSql); del.execute(); } @@ -113,23 +110,21 @@ TablePatch::doUpdates(const char * where, const char * order) // Build SQL for list of updates to perform ------------------------ // ----------------------------------------------------------------- Buffer toUpdSel; - Buffer ch; - Buffer k; + toUpdSel.append("SELECT "); foreach (Columns::const_iterator, cols, col) { if (pk.find(*col) == pk.end()) { - ch.appendf("b.%s, ", + toUpdSel.appendf("b.%s, ", col->c_str()); } - else { - if (k.length()) { - k.append(", "); - } - k.appendf("b.%s ", + } + foreach (Columns::const_iterator, cols, col) { + if (pk.find(*col) != pk.end()) { + toUpdSel.appendf("b.%s, ", col->c_str()); } } - toUpdSel.appendf("SELECT %s %s FROM %s a, %s b", - ch.c_str(), k.c_str(), dest.c_str(), src.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 "); @@ -191,8 +186,8 @@ TablePatch::doUpdates(const char * where, const char * order) // ----------------------------------------------------------------- // Iterator over update list make changes -------------------------- // ----------------------------------------------------------------- - SelectCommand toUpd(db, toUpdSel.c_str()); - ModifyCommand upd(db, updSql.c_str()); + SelectCommand toUpd(db, toUpdSel); + ModifyCommand upd(db, updSql); int cs = cols.size(); toUpd.execute(); for (int c = 0; c < cs; c += 1) { @@ -255,7 +250,7 @@ TablePatch::doInserts(const char * order) if (order) { toInsSql.appendf(" ORDER BY %s", order); } - ModifyCommand(db, toInsSql.c_str()).execute(); + ModifyCommand(db, toInsSql).execute(); } const char * diff --git a/project2/tablepatch.h b/project2/tablepatch.h index f317fd2..118e379 100644 --- a/project2/tablepatch.h +++ b/project2/tablepatch.h @@ -4,7 +4,6 @@ #include #include #include -#include #include #include #include -- cgit v1.2.3