summaryrefslogtreecommitdiff
path: root/project2/sql
diff options
context:
space:
mode:
Diffstat (limited to 'project2/sql')
-rw-r--r--project2/sql/sqlMergeTask.cpp25
-rw-r--r--project2/sql/sqlMergeTask.h9
-rw-r--r--project2/sql/sqlWriter.cpp8
-rw-r--r--project2/sql/tablepatch.cpp188
-rw-r--r--project2/sql/tablepatch.h64
5 files changed, 179 insertions, 115 deletions
diff --git a/project2/sql/sqlMergeTask.cpp b/project2/sql/sqlMergeTask.cpp
index 0182f1e..2c2cdd9 100644
--- a/project2/sql/sqlMergeTask.cpp
+++ b/project2/sql/sqlMergeTask.cpp
@@ -45,10 +45,13 @@ DECLARE_LOADER("sqlmergeinsert", SqlMergeInsert);
SqlMergeTask::SqlMergeTask(ScriptNodePtr p) :
SourceObject(p),
Task(p),
- updateWhere(p, "updatewhere", Null()),
+ updateWhere(p->child("updatewhere", false)),
patchOrder(p, "patchorder", Null()),
earlyKeys(p, "earlykeys", false),
useView(p, "useview", false),
+ doDelete(p, "delete", true),
+ doUpdate(p, "update", true),
+ doInsert(p, "insert", true),
tempTableCreated(false),
sqlCommand(NULL),
insCmd(NULL),
@@ -128,10 +131,13 @@ SqlMergeTask::execute() const
colNames.insert(c->column);
}
TablePatch tp(*destdb, dtablet, dtable, colNames);
+ tp.doDelete = doDelete();
+ tp.doUpdate = doUpdate();
+ tp.doInsert = doInsert();
BOOST_FOREACH(const Keys::value_type & k, keys) {
tp.addKey(k);
}
- tp.patch(updateWhere(), patchOrder());
+ tp.patch(this, patchOrder());
dropTempTable();
}
@@ -303,6 +309,8 @@ SqlMergeTask::copyToTempTable() const
}
ins.appendf(" FROM (%s) tmp_src", sqlCommand->getSqlFor("").c_str());
DB::ModifyCommand * cttt = destdb->newModifyCommand(ins);
+ unsigned int off = 0;
+ sqlCommand->bindParams(cttt, off);
cttt->execute();
delete cttt;
}
@@ -324,3 +332,16 @@ SqlMergeTask::copyToTempTable() const
}
}
+void
+SqlMergeTask::appendWhere(Buffer * buf) const
+{
+ buf->appendf(" AND %s ", updateWhere.getSqlFor("").c_str());
+}
+
+void
+SqlMergeTask::bindWhere(DB::Command * cmd) const
+{
+ unsigned int off = 0;
+ updateWhere.bindParams(cmd, off);
+}
+
diff --git a/project2/sql/sqlMergeTask.h b/project2/sql/sqlMergeTask.h
index dc23936..bd9f050 100644
--- a/project2/sql/sqlMergeTask.h
+++ b/project2/sql/sqlMergeTask.h
@@ -15,7 +15,7 @@
#include <list>
/// Project2 component merge arbitrary data into an RDBMS table
-class SqlMergeTask : public Task {
+class SqlMergeTask : public Task, public TablePatch::WhereProvider {
public:
typedef std::string Table;
typedef std::string Column;
@@ -47,16 +47,21 @@ class SqlMergeTask : public Task {
Columns cols;
Keys keys;
Keys indexes;
- const Variable updateWhere;
+ const DynamicSql::SqlCommand updateWhere;
const Variable patchOrder;
const Variable earlyKeys;
const Variable useView;
+ const Variable doDelete;
+ const Variable doUpdate;
+ const Variable doInsert;
private:
virtual void copyToTempTable() const;
void createTempTable() const;
void dropTempTable() const;
void createTempKey() const;
+ void appendWhere(Buffer *) const;
+ void bindWhere(DB::Command *) const;
mutable bool tempTableCreated;
typedef ANONSTORAGEOF(Iterate) Sources;
diff --git a/project2/sql/sqlWriter.cpp b/project2/sql/sqlWriter.cpp
index 867e1bd..f67eb1c 100644
--- a/project2/sql/sqlWriter.cpp
+++ b/project2/sql/sqlWriter.cpp
@@ -35,9 +35,11 @@ appendNewFromNode(DynamicSql::Writers * w, DynamicSql::SqlCommand::Filters * fs,
DynamicSql::SqlCommand::SqlCommand(ScriptNodePtr s)
{
- s->composeWithCallbacks(
- boost::bind(&appendNew<SqlText, Glib::ustring>, &writers, _1),
- boost::bind(&appendNewFromNode, &writers, &filters, _1));
+ if (s) {
+ s->composeWithCallbacks(
+ boost::bind(&appendNew<SqlText, Glib::ustring>, &writers, _1),
+ boost::bind(&appendNewFromNode, &writers, &filters, _1));
+ }
}
Glib::ustring
diff --git a/project2/sql/tablepatch.cpp b/project2/sql/tablepatch.cpp
index 8443c7b..733f94f 100644
--- a/project2/sql/tablepatch.cpp
+++ b/project2/sql/tablepatch.cpp
@@ -11,41 +11,50 @@ using namespace DB;
TablePatch::TablePatch(const Connection & wdb, const TablePatch::Table & s, const TablePatch::Table & d,
const TablePatch::Columns & c) :
- src(s),
- dest(d),
+ doDelete(true),
+ doUpdate(true),
+ doInsert(true),
+ src(s),
+ dest(d),
cols(c),
- db(wdb)
+ db(wdb)
{
- if (!src.length()) {
- throw PatchCheckFailure();
- }
- if (!dest.length()) {
- throw PatchCheckFailure();
- }
- if (!db.inTx()) {
- throw PatchCheckFailure();
- }
+ if (!src.length()) {
+ throw PatchCheckFailure();
+ }
+ if (!dest.length()) {
+ throw PatchCheckFailure();
+ }
+ if (!db.inTx()) {
+ throw PatchCheckFailure();
+ }
}
void
TablePatch::addKey(const TablePatch::Column & c)
{
- pk.insert(c);
+ pk.insert(c);
}
void
-TablePatch::patch(const char * where, const char * order)
+TablePatch::patch(const WhereProvider * where, const char * order)
{
- if (pk.size() == 0) {
- throw PatchCheckFailure();
- }
- doDeletes(where, order);
- doUpdates(where, order);
- doInserts(order);
+ if (pk.size() == 0) {
+ throw PatchCheckFailure();
+ }
+ if (doDelete) {
+ doDeletes(where, order);
+ }
+ if (doUpdate) {
+ doUpdates(where, order);
+ }
+ if (doInsert) {
+ doInserts(order);
+ }
}
void
-TablePatch::doDeletes(const char * where, const char * order)
+TablePatch::doDeletes(const WhereProvider * where, const char * order)
{
switch (db.bulkDeleteStyle()) {
case BulkDeleteUsingSubSelect:
@@ -94,14 +103,18 @@ TablePatch::doDeletes(const char * where, const char * order)
toDelSql.appendf(" b.%s IS NULL",
pki->c_str());
}
- if (where && *where) {
- toDelSql.appendf(" AND %s", where);
+ if (where) {
+ //toDelSql.appendf(" AND %s", where);
+ where->appendWhere(&toDelSql);
}
if (order && *order) {
toDelSql.appendf(" ORDER BY %s", order);
}
toDelSql.append(")");
ModifyCommand * del = db.newModifyCommand(toDelSql);
+ if (where) {
+ where->bindWhere(del);
+ }
del->execute();
delete del;
break;
@@ -133,13 +146,17 @@ TablePatch::doDeletes(const char * where, const char * order)
toDelSql.appendf(" b.%s IS NULL",
pki->c_str());
}
- if (where && *where) {
- toDelSql.appendf(" AND %s", where);
+ if (where) {
+ //toDelSql.appendf(" AND %s", where);
+ where->appendWhere(&toDelSql);
}
if (order && *order) {
toDelSql.appendf(" ORDER BY %s", order);
}
ModifyCommand * del = db.newModifyCommand(toDelSql);
+ if (where) {
+ where->bindWhere(del);
+ }
del->execute();
delete del;
break;
@@ -148,7 +165,7 @@ TablePatch::doDeletes(const char * where, const char * order)
}
void
-TablePatch::doUpdates(const char * where, const char * order)
+TablePatch::doUpdates(const WhereProvider * where, const char * order)
{
if (cols.size() == pk.size()) {
// Can't "change" anything... it's all part of the key
@@ -186,8 +203,8 @@ TablePatch::doUpdates(const char * where, const char * order)
toUpdSel.appendf(" a.%s = b.%s",
pki->c_str(), pki->c_str());
}
- if (where && *where) {
- toUpdSel.appendf(" AND %s", where);
+ if (where) {
+ where->appendWhere(&toUpdSel);
}
toUpdSel.append(" AND (");
bool first = true;
@@ -238,6 +255,9 @@ TablePatch::doUpdates(const char * where, const char * order)
// Iterator over update list make changes --------------------------
// -----------------------------------------------------------------
SelectCommand * toUpd = db.newSelectCommand(toUpdSel);
+ if (where) {
+ where->bindWhere(toUpd);
+ }
ModifyCommand * upd = db.newModifyCommand(updSql);
int cs = cols.size();
toUpd->execute();
@@ -298,8 +318,8 @@ TablePatch::doUpdates(const char * where, const char * order)
}
}
updSql.append(")");
- if (where && *where) {
- updSql.appendf(" AND %s ", where);
+ if (where) {
+ where->appendWhere(&updSql);
}
if (order && *order) {
updSql.appendf(" ORDER BY %s", order);
@@ -308,6 +328,9 @@ TablePatch::doUpdates(const char * where, const char * order)
// Execute the bulk update command ---------------------------------
// -----------------------------------------------------------------
ModifyCommand * upd = db.newModifyCommand(updSql);
+ if (where) {
+ where->bindWhere(upd);
+ }
upd->execute(true);
delete upd;
break;
@@ -357,8 +380,8 @@ TablePatch::doUpdates(const char * where, const char * order)
}
}
updSql.append(")");
- if (where && *where) {
- updSql.appendf(" AND %s ", where);
+ if (where) {
+ where->appendWhere(&updSql);
}
if (order && *order) {
updSql.appendf(" ORDER BY %s", order);
@@ -367,6 +390,9 @@ TablePatch::doUpdates(const char * where, const char * order)
// Execute the bulk update command ---------------------------------
// -----------------------------------------------------------------
ModifyCommand * upd = db.newModifyCommand(updSql);
+ if (where) {
+ where->bindWhere(upd);
+ }
upd->execute(true);
delete upd;
break;
@@ -377,55 +403,55 @@ TablePatch::doUpdates(const char * where, const char * order)
void
TablePatch::doInserts(const char * order)
{
- // -----------------------------------------------------------------
- // Build SQL for copying new records -------------------------------
- // -----------------------------------------------------------------
+ // -----------------------------------------------------------------
+ // Build SQL for copying new records -------------------------------
+ // -----------------------------------------------------------------
Buffer toInsSql;
- toInsSql.appendf("INSERT INTO %s",
- dest.c_str());
- foreach (Columns::const_iterator, cols, col) {
- if (col == cols.begin()) {
- toInsSql.append("(");
- }
- else {
- toInsSql.append(", ");
- }
- toInsSql.appendf("%s",
- col->c_str());
- }
- toInsSql.append(") SELECT ");
- foreach (Columns::const_iterator, cols, col) {
- if (col != cols.begin()) {
- toInsSql.append(", ");
- }
- toInsSql.appendf("b.%s",
- col->c_str());
- }
- toInsSql.appendf(" FROM %s b LEFT OUTER JOIN %s a",
- src.c_str(), dest.c_str());
- foreach (PKI, pk, pki) {
- if (pki == pk.begin()) {
- toInsSql.append(" ON ");
- }
- else {
- toInsSql.append(" AND ");
- }
- toInsSql.appendf(" a.%s = b.%s",
- pki->c_str(), pki->c_str());
- }
- foreach (PKI, pk, pki) {
- if (pki == pk.begin()) {
- toInsSql.append(" WHERE ");
- }
- else {
- toInsSql.append(" AND ");
- }
- toInsSql.appendf(" a.%s IS NULL",
- pki->c_str());
- }
- if (order && *order) {
- toInsSql.appendf(" ORDER BY %s", order);
- }
+ toInsSql.appendf("INSERT INTO %s",
+ dest.c_str());
+ foreach (Columns::const_iterator, cols, col) {
+ if (col == cols.begin()) {
+ toInsSql.append("(");
+ }
+ else {
+ toInsSql.append(", ");
+ }
+ toInsSql.appendf("%s",
+ col->c_str());
+ }
+ toInsSql.append(") SELECT ");
+ foreach (Columns::const_iterator, cols, col) {
+ if (col != cols.begin()) {
+ toInsSql.append(", ");
+ }
+ toInsSql.appendf("b.%s",
+ col->c_str());
+ }
+ toInsSql.appendf(" FROM %s b LEFT OUTER JOIN %s a",
+ src.c_str(), dest.c_str());
+ foreach (PKI, pk, pki) {
+ if (pki == pk.begin()) {
+ toInsSql.append(" ON ");
+ }
+ else {
+ toInsSql.append(" AND ");
+ }
+ toInsSql.appendf(" a.%s = b.%s",
+ pki->c_str(), pki->c_str());
+ }
+ foreach (PKI, pk, pki) {
+ if (pki == pk.begin()) {
+ toInsSql.append(" WHERE ");
+ }
+ else {
+ toInsSql.append(" AND ");
+ }
+ toInsSql.appendf(" a.%s IS NULL",
+ pki->c_str());
+ }
+ if (order && *order) {
+ toInsSql.appendf(" ORDER BY %s", order);
+ }
ModifyCommand * ins = db.newModifyCommand(toInsSql);
ins->execute();
delete ins;
@@ -434,6 +460,6 @@ TablePatch::doInserts(const char * order)
const char *
TablePatch::PatchCheckFailure::what() const throw()
{
- return "Santiy checks failed: check table names and keys";
+ return "Santiy checks failed: check table names and keys";
}
diff --git a/project2/sql/tablepatch.h b/project2/sql/tablepatch.h
index 0174294..515c8bd 100644
--- a/project2/sql/tablepatch.h
+++ b/project2/sql/tablepatch.h
@@ -9,33 +9,43 @@
#include <selectcommand.h>
class TablePatch {
- public:
- typedef std::string Table;
- typedef std::string Column;
- typedef std::set<Column> Columns;
- typedef Columns PrimaryKey;
- typedef PrimaryKey::const_iterator PKI;
-
- class PatchCheckFailure : public std::exception {
- public:
- const char * what() const throw();
- };
-
- TablePatch(const DB::Connection & db, const Table & src, const Table & dest, const Columns & cols);
-
- void addKey(const Column & col);
- void patch(const char * where, const char * order);
-
- private:
- void doDeletes(const char * where, const char * order);
- void doUpdates(const char * where, const char * order);
- void doInserts(const char * order);
-
- Table src;
- Table dest;
- PrimaryKey pk;
- Columns cols;
- const DB::Connection &db;
+ public:
+ typedef std::string Table;
+ typedef std::string Column;
+ typedef std::set<Column> Columns;
+ typedef Columns PrimaryKey;
+ typedef PrimaryKey::const_iterator PKI;
+
+ class PatchCheckFailure : public std::exception {
+ public:
+ const char * what() const throw();
+ };
+
+ class WhereProvider {
+ public:
+ virtual void appendWhere(Buffer *) const = 0;
+ virtual void bindWhere(DB::Command *) const = 0;
+ };
+
+ TablePatch(const DB::Connection & db, const Table & src, const Table & dest, const Columns & cols);
+
+ void addKey(const Column & col);
+ void patch(const WhereProvider * where, const char * order);
+
+ bool doDelete;
+ bool doUpdate;
+ bool doInsert;
+
+ private:
+ void doDeletes(const WhereProvider * where, const char * order);
+ void doUpdates(const WhereProvider * where, const char * order);
+ void doInserts(const char * order);
+
+ Table src;
+ Table dest;
+ PrimaryKey pk;
+ Columns cols;
+ const DB::Connection &db;
};
#endif
generated by cgit v1.2.3 (git 2.25.1) at 2024-12-26 15:26:27 +0000