From 198b0d886817f2c5bc97cfd11857d4b314dffae3 Mon Sep 17 00:00:00 2001 From: deva Date: Thu, 10 Jun 2010 07:03:06 +0000 Subject: Add transaction support on database pr. pracro session. Makes it possible to discard all edited macros. --- server/src/pracrodaopgsql.cc | 184 +++++++++++++++++++++++++++++++------------ 1 file changed, 132 insertions(+), 52 deletions(-) (limited to 'server/src/pracrodaopgsql.cc') diff --git a/server/src/pracrodaopgsql.cc b/server/src/pracrodaopgsql.cc index d1ba517..6d7afe8 100644 --- a/server/src/pracrodaopgsql.cc +++ b/server/src/pracrodaopgsql.cc @@ -34,7 +34,9 @@ * CREATE SEQUENCE 'trseq'; * SELECT setval('trseq', (SELECT MAX(oid) FROM transactions)); * UPDATE transactions SET uid = oid; - * INSERT INTO fieldnames (name, description, timestamp) VALUES ('journal.resume', 'Journal resume text', (SELECT EXTRACT(EPOCH FROM now())::integer)); + * INSERT INTO fieldnames (name, description, timestamp) + * VALUES ('journal.resume', 'Journal resume text', + * (SELECT EXTRACT(EPOCH FROM now())::integer)); */ #include @@ -44,7 +46,9 @@ #include "debug.h" -PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, std::string _user, std::string _passwd, std::string _dbname) +PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, + std::string _user, std::string _passwd, + std::string _dbname) : PracroDAO(_host, _port, _user, _passwd, _dbname) { conn = NULL; @@ -56,6 +60,15 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, std::string cs += " dbname=" + (dbname.size() ? dbname : "pracro"); try { conn = new pqxx::connection(cs); + W = new pqxx::work(*conn); + + std::string ts; + try { + ts = "BEGIN;"; + PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); + pqxx::result R = W->exec(ts); + } catch(...) { + } } catch(std::exception &e) { PRACRO_ERR_LOG(db, "Postgresql init failed: %s\n", e.what()); conn = NULL; @@ -66,12 +79,23 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, std::string PracroDAOPgsql::~PracroDAOPgsql() { - if(conn) delete conn; + if(conn) { + if(W) delete W; + delete conn; + } } -void PracroDAOPgsql::commitTransaction(std::string user, std::string patientid, Macro &_macro, Fields &fields, time_t now) +void PracroDAOPgsql::commitTransaction(std::string user, + std::string patientid, + Macro &_macro, + Fields &fields, + time_t now) { - PRACRO_DEBUG(db, "(%s, %s, %s, <%u fields>, %ld)\n", user.c_str(), patientid.c_str(), _macro.attributes["name"].c_str(), fields.size(), now); + PRACRO_DEBUG(db, "(%s, %s, %s, <%u fields>, %ld)\n", + user.c_str(), patientid.c_str(), + _macro.attributes["name"].c_str(), + fields.size(), now); + if(!conn) PRACRO_DEBUG(db, "No pgsql connection\n"); if(fields.size() == 0) return; @@ -81,54 +105,66 @@ void PracroDAOPgsql::commitTransaction(std::string user, std::string patientid, std::string ts; try { - pqxx::work W(*conn); - ts = "INSERT INTO transactions (uid, patientid, macro, version, \"timestamp\", \"user\") VALUES (" + ts = "INSERT INTO transactions (uid, patientid, macro, version," + " \"timestamp\", \"user\") VALUES (" " nextval('trseq'), " - " '" + W.esc(patientid) + "', " - " '" + W.esc(macro) + "', " - " '" + W.esc(version) + "', " - " '" + W.esc(timestamp.str()) + "', " - " '" + W.esc(user) + "' " - ")" + " '" + W->esc(patientid) + "', " + " '" + W->esc(macro) + "', " + " '" + W->esc(version) + "', " + " '" + W->esc(timestamp.str()) + "', " + " '" + W->esc(user) + "' " + ");" ; PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); - pqxx::result R = W.exec(ts); + pqxx::result R = W->exec(ts); + statements += ts + "\n"; if(fields.size() > 0) { // field table lookup ts = "SELECT name FROM fieldnames WHERE name IN ( "; std::map< std::string, std::string >::iterator i = fields.begin(); - ts += "'" + W.esc(i->first) + "'"; + ts += "'" + W->esc(i->first) + "'"; i++; while(i != fields.end()) { - ts += ", '" + W.esc(i->first) + "'"; + ts += ", '" + W->esc(i->first) + "'"; i++; } - ts += ")"; + ts += ");"; PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); - R = W.exec(ts); - PRACRO_DEBUG(db, "input fields: %d, output fields: %lu\n", fields.size(), R.size()); + R = W->exec(ts); + // statements += ts + "\n"; + + PRACRO_DEBUG(db, "input fields: %d, output fields: %lu\n", + fields.size(), R.size()); // Store known fields pqxx::result::const_iterator ri = R.begin(); if(ri != R.end()) { std::string name = (*ri)[0].c_str(); - PRACRO_DEBUG(db, "Storing: %s with value %s\n", name.c_str(), fields[name].c_str()); - ts = "INSERT INTO fields (transaction, name, value) VALUES ( currval('trseq'), '" + W.esc(name) + "', '" + W.esc(fields[name]) + "')"; + PRACRO_DEBUG(db, "Storing: %s with value %s\n", + name.c_str(), fields[name].c_str()); + ts = "INSERT INTO fields (transaction, name, value) " + "VALUES ( currval('trseq'), '" + W->esc(name) + "', '" + + W->esc(fields[name]) + "')"; ri++; while(ri != R.end()) { name = (*ri)[0].c_str(); - PRACRO_DEBUG(db, "Storing: %s with value %s\n", name.c_str(), fields[name].c_str()); + PRACRO_DEBUG(db, "Storing: %s with value %s\n", + name.c_str(), fields[name].c_str()); - ts += ", (currval('trseq'), '" + W.esc(name) + "', '" + W.esc(fields[name]) + "')"; + ts += ", (currval('trseq'), '" + W->esc(name) + "', '" + + W->esc(fields[name]) + "')"; ri++; } + ts += ";"; PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); - W.exec(ts); + W->exec(ts); + statements += ts + "\n"; + } } - W.commit(); + // W->commit(); } catch(std::exception &e) { PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str()); } @@ -156,9 +192,15 @@ void PracroDAOPgsql::commitTransaction(std::string user, std::string patientid, * AND tt.uid = ff.transaction * AND tt.patientid = '1505050505' */ -Values PracroDAOPgsql::getLatestValues(std::string patientid, Macro *macro, Fieldnames &fieldnames, time_t oldest) +Values PracroDAOPgsql::getLatestValues(std::string patientid, + Macro *macro, + Fieldnames &fieldnames, + time_t oldest) { - PRACRO_DEBUG(db, "(%s, %s, <%u fieldnames>, %ld)\n", patientid.c_str(), macro ? macro->attributes["name"].c_str() : "(null)", fieldnames.size(), oldest); + PRACRO_DEBUG(db, "(%s, %s, <%u fieldnames>, %ld)\n", + patientid.c_str(), + macro ? macro->attributes["name"].c_str() : "(null)", + fieldnames.size(), oldest); if(!conn) PRACRO_DEBUG(db, "No pgsql connection\n"); Values values; @@ -167,13 +209,12 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, Macro *macro, Fiel try { std::string namecond; - pqxx::work W(*conn); if(fieldnames.size() > 0) { std::vector< std::string >::iterator i = fieldnames.begin(); - namecond += " AND f.name IN ('" + W.esc(*i) + "'"; + namecond += " AND f.name IN ('" + W->esc(*i) + "'"; i++; while(i != fieldnames.end()) { - namecond += ", '" + W.esc(*i) + "'"; + namecond += ", '" + W->esc(*i) + "'"; i++; } namecond += ')'; @@ -182,7 +223,7 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, Macro *macro, Fiel // Begin inner query " (SELECT f.name, MAX(t.timestamp) AS ts FROM fields f, transactions t " " WHERE t.uid = f.transaction AND t.timestamp >= " + soldest.str() + - " AND t.patientid = '" + W.esc(patientid) + "' " + " AND t.patientid = '" + W->esc(patientid) + "' " + namecond; if(macro) { query += " AND t.macro = '" + macro->attributes["name"] + "'"; @@ -195,7 +236,7 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, Macro *macro, Fiel " WHERE xx.ts = tt.timestamp " " AND xx.name = ff.name " " AND tt.uid = ff.transaction " - " AND tt.patientid = '" + W.esc(patientid) + "' " + " AND tt.patientid = '" + W->esc(patientid) + "' " ; if(macro) { query += " AND tt.macro = '" + macro->attributes["name"] + "'"; @@ -204,7 +245,7 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, Macro *macro, Fiel } PRACRO_DEBUG(sql, "Query: %s\n", query.c_str()); - pqxx::result R = W.exec(query); + pqxx::result R = W->exec(query); pqxx::result::const_iterator ri = R.begin(); while(ri != R.end()) { Value v; @@ -221,25 +262,27 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, Macro *macro, Fiel } -unsigned PracroDAOPgsql::nrOfCommits(std::string patientid, std::string macroname, time_t oldest) +unsigned PracroDAOPgsql::nrOfCommits(std::string patientid, + std::string macroname, + time_t oldest) { std::string query; std::stringstream soldest; soldest << oldest; try { - pqxx::work W(*conn); query = "SELECT count(*) FROM transactions " - " WHERE patientid = '" + W.esc(patientid) + "' " - " AND macro = '" + W.esc(macroname) + "' " + " WHERE patientid = '" + W->esc(patientid) + "' " + " AND macro = '" + W->esc(macroname) + "' " " AND timestamp >= " + soldest.str() ; PRACRO_DEBUG(sql, "Query: %s\n", query.c_str()); - pqxx::result R = W.exec(query); + pqxx::result R = W->exec(query); if(R.size() != 1) { PRACRO_ERR_LOG(db, "No result set; expected one row with one column\n"); return 0; } unsigned n = (unsigned)atol((*R.begin())[0].c_str()); - PRACRO_DEBUG(db, "Found %u commits for %s(%s) from %ld\n", n, patientid.c_str(), macroname.c_str(), oldest); + PRACRO_DEBUG(db, "Found %u commits for %s(%s) from %ld\n", + n, patientid.c_str(), macroname.c_str(), oldest); return n; } catch (std::exception &e) { PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), query.c_str()); @@ -253,16 +296,15 @@ void PracroDAOPgsql::addFieldname(std::string name, std::string description) std::stringstream timestamp; timestamp << time(NULL); std::string ts; try { - pqxx::work W(*conn); ts = "INSERT INTO fieldnames (name, description, \"timestamp\") VALUES (" - " '" + W.esc(name) + "', " - " '" + W.esc(description) + "', " - " '" + W.esc(timestamp.str()) + "' " + " '" + W->esc(name) + "', " + " '" + W->esc(description) + "', " + " '" + W->esc(timestamp.str()) + "' " ")" ; PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); - pqxx::result R = W.exec(ts); - W.commit(); + pqxx::result R = W->exec(ts); + W->commit(); } catch (std::exception &e) { PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str()); } @@ -272,12 +314,11 @@ void PracroDAOPgsql::delFieldname(std::string name) { std::string ts; try { - pqxx::work W(*conn); ts = "DELETE FROM fieldnames WHERE name=" - "'" + W.esc(name) + "' "; + "'" + W->esc(name) + "' "; PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); - pqxx::result R = W.exec(ts); - W.commit(); + pqxx::result R = W->exec(ts); + W->commit(); } catch (std::exception &e) { PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str()); } @@ -290,10 +331,9 @@ std::vector PracroDAOPgsql::getFieldnames() std::string query; try { - pqxx::work W(*conn); query = "SELECT * FROM fieldnames"; PRACRO_DEBUG(sql, "Query: %s\n", query.c_str()); - pqxx::result R = W.exec(query); + pqxx::result R = W->exec(query); pqxx::result::const_iterator ri = R.begin(); while(ri != R.end()) { Fieldname f; @@ -310,6 +350,45 @@ std::vector PracroDAOPgsql::getFieldnames() return fieldnames; } +void PracroDAOPgsql::commit() +{ + std::string ts; + try { + W->commit(); + statements = ""; + } catch (std::exception &e) { + PRACRO_ERR_LOG(db, "Commit failed: %s: %s\n", e.what(), ts.c_str()); + } +} + +void PracroDAOPgsql::discard() +{ + std::string ts; + try { + W->abort(); + statements = ""; + } catch (std::exception &e) { + PRACRO_ERR_LOG(db, "Abort (rollback) failed: %s: %s\n", + e.what(), ts.c_str()); + } +} + +std::string PracroDAOPgsql::serialise() +{ + return statements; +} + +void PracroDAOPgsql::restore(const std::string &data) +{ + std::string ts; + try { + PRACRO_DEBUG(sql, "Restore: %s\n", data.c_str()); + pqxx::result R = W->exec(data); + statements = data; + } catch( ... ) { + } +} + #endif/*WITHOUT_DB*/ #ifdef TEST_PRACRODAOPGSQL @@ -321,7 +400,8 @@ int main() { #ifndef WITHOUT_DB try { - PracroDAOPgsql db(Conf::database_addr, "", Conf::database_user, Conf::database_passwd, ""); + PracroDAOPgsql db(Conf::database_addr, "", Conf::database_user, + Conf::database_passwd, ""); } catch(Exception &e) { printf("ERROR: %s\n", e.what()); return 1; -- cgit v1.2.3