From 4a42852b13d84a283abe54ee569c6d1a8334fa09 Mon Sep 17 00:00:00 2001 From: deva Date: Fri, 18 Feb 2011 09:51:24 +0000 Subject: Some small changes in the postgres error handling, and a lot of tests, inspired by testdb. --- server/src/pracrodaopgsql.cc | 480 +++++++++++++++++++++++++++++++++---------- 1 file changed, 369 insertions(+), 111 deletions(-) (limited to 'server/src') diff --git a/server/src/pracrodaopgsql.cc b/server/src/pracrodaopgsql.cc index 8eb3d16..d3ae8df 100644 --- a/server/src/pracrodaopgsql.cc +++ b/server/src/pracrodaopgsql.cc @@ -60,15 +60,6 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, cs += " dbname=" + (dbname.size() ? dbname : "pracro"); try { conn = new pqxx::connection(cs); - /* - std::string ts; - try { - ts = "BEGIN;"; - DEBUG(sql, "Query: %s\n", ts.c_str()); - pqxx::result R = W->exec(ts); - } catch(...) { - } - */ } catch(std::exception &e) { ERR_LOG(db, "Postgresql init failed: %s\n", e.what()); conn = NULL; @@ -80,7 +71,6 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, PracroDAOPgsql::~PracroDAOPgsql() { if(conn) { - // if(W) delete W; delete conn; } } @@ -92,29 +82,21 @@ std::string PracroDAOPgsql::newSessionId() return ""; } - pqxx::work W(*conn); - pqxx::result R = W.exec("SELECT nextval('sessionseq');"); - pqxx::result::const_iterator ri = R.begin(); - if(ri != R.end()) { - DEBUG(db, "New session id: %s\n", (*ri)[0].c_str()); - /* - std::string ts; - ts = "INSERT INTO commits (patientid, template, version," - " \"timestamp\", uid, status) VALUES (" - " '" + W.esc(transaction.cpr) + "', " - " '" + W.esc(commit.templ) + "', " - " '" + "1.0" + "', " - " '" + W.esc(timestamp.str()) + "', " - " '" + W.esc(sessionid) + "', " - " 'active' " - ");" - ; - DEBUG(sql, "Query: %s\n", ts.c_str()); - pqxx::result R = W.exec(ts); - */ - return (*ri)[0].c_str(); + try { + pqxx::work W(*conn); + pqxx::result R = W.exec("SELECT nextval('sessionseq');"); + pqxx::result::const_iterator ri = R.begin(); + if(ri != R.end()) { + DEBUG(db, "New session id: %s\n", (*ri)[0].c_str()); + return (*ri)[0].c_str(); + } + + ERR(db, "Something wrong with the session counter.\n"); + + } catch(std::exception &e) { + ERR_LOG(db, "Session counter failed: %s\n", e.what()); } - ERR(db, "No pgsql connection\n"); + return ""; } @@ -144,36 +126,41 @@ void PracroDAOPgsql::commitTransaction(std::string sessionid, std::string ts; - ts = "SELECT status FROM commits WHERE uid='"+sessionid+"';"; - pqxx::result R = W.exec(ts); - if(!R.size()) { - ts = "INSERT INTO commits (patientid, template, version," - " \"timestamp\", uid, status) VALUES (" - " '" + W.esc(transaction.cpr) + "', " - " '" + W.esc(commit.templ) + "', " - " '" + "1.0" + "', " - " '" + W.esc(timestamp.str()) + "', " - " '" + W.esc(sessionid) + "', " - " 'active' " - ");" - ; - DEBUG(sql, "Query: %s\n", ts.c_str()); + try { + ts = "SELECT status FROM commits WHERE uid='"+sessionid+"';"; pqxx::result R = W.exec(ts); - } else { - - pqxx::result::const_iterator ri = R.begin(); - if(ri != R.end()) { - std::string status = (*ri)[0].c_str(); - if(status == "committed") { - ERR_LOG(db, "Attempt to add to committed session %s blocked!\n", - sessionid.c_str()); - return; + if(!R.size()) { + ts = "INSERT INTO commits (patientid, template, version," + " \"timestamp\", uid, status) VALUES (" + " '" + W.esc(transaction.cpr) + "', " + " '" + W.esc(commit.templ) + "', " + " '" + "1.0" + "', " + " '" + W.esc(timestamp.str()) + "', " + " '" + W.esc(sessionid) + "', " + " 'active' " + ");" + ; + DEBUG(sql, "Query: %s\n", ts.c_str()); + pqxx::result R = W.exec(ts); + } else { + + pqxx::result::const_iterator ri = R.begin(); + if(ri != R.end()) { + std::string status = (*ri)[0].c_str(); + if(status == "committed") { + ERR_LOG(db, "Attempt to add to committed session %s blocked!\n", + sessionid.c_str()); + return; + } } + + ts = "UPDATE commits SET status='active' WHERE uid="+sessionid+";"; + DEBUG(sql, "Query: %s\n", ts.c_str()); + /*pqxx::result R = */W.exec(ts); } - - ts = "UPDATE commits SET status='active' WHERE uid="+sessionid+";"; - DEBUG(sql, "Query: %s\n", ts.c_str()); - /*pqxx::result R = */W.exec(ts); + } catch(std::exception &e) { + ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str()); + return; } try { @@ -241,34 +228,18 @@ void PracroDAOPgsql::commitTransaction(std::string sessionid, } - -/* - * The following select finds the newest timestamps for each fieldname - * belonging to the designated patient and from a set of fieldnames in the - * inner query. The outer query then finds the corresponding field values. - * Note: there is no protection agains duplicate fields that could - * theoretically occur within the same transaction. - * - * SELECT ff.name, ff.value, tt.timestamp FROM ( - * SELECT f.name, max(t.timestamp) AS ts - * FROM fields f, transactions t - * WHERE t.uid = f.transaction - * AND t.patientid = '1505050505' - * AND t.timestamp >= 0 - * AND f.name IN ('current_eye_disease') - * GROUP BY f.name) xx, - * transactions tt, fields ff - * WHERE xx.ts = tt.timestamp - * AND xx.name = ff.name - * AND tt.uid = ff.transaction - * AND tt.patientid = '1505050505' - */ Values PracroDAOPgsql::getLatestValues(std::string sessionid, std::string patientid, Macro *macro, Fieldnames &fieldnames, time_t oldest) { + Values values; + + if(!conn) { + ERR(db, "No pgsql connection\n"); + return values; + } bool uncom = false; // get results that are not yet committed? @@ -276,8 +247,6 @@ Values PracroDAOPgsql::getLatestValues(std::string sessionid, patientid.c_str(), macro ? macro->attributes["name"].c_str() : "(null)", fieldnames.size(), oldest); - if(!conn) DEBUG(db, "No pgsql connection\n"); - Values values; std::string query; std::stringstream soldest; soldest << oldest; @@ -359,6 +328,10 @@ unsigned PracroDAOPgsql::nrOfCommits(std::string sessionid, std::string macroname, time_t oldest) { + if(!conn) { + ERR(db, "No pgsql connection\n"); + return 0; + } bool uncom = false; // get results that are not yet committed? @@ -391,16 +364,32 @@ unsigned PracroDAOPgsql::nrOfCommits(std::string sessionid, void PracroDAOPgsql::addFieldname(std::string name, std::string description) { + if(!conn) { + ERR(db, "No pgsql connection\n"); + return; + } + 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()) + "' " - ")" - ; + + ts = "SELECT name FROM fieldnames WHERE name='"+W.esc(name)+"';"; + pqxx::result Rc = W.exec(ts); + if(Rc.size()) { + ts = "UPDATE fieldnames SET " + " description='" + W.esc(description) + "', " + " WHERE name='" + W.esc(name) + "';" + ; + } else { + ts = "INSERT INTO fieldnames (name, description, \"timestamp\") VALUES (" + " '" + W.esc(name) + "', " + " '" + W.esc(description) + "', " + " '" + W.esc(timestamp.str()) + "' " + ");" + ; + } DEBUG(sql, "Query: %s\n", ts.c_str()); pqxx::result R = W.exec(ts); W.commit(); @@ -411,6 +400,11 @@ void PracroDAOPgsql::addFieldname(std::string name, std::string description) void PracroDAOPgsql::delFieldname(std::string name) { + if(!conn) { + ERR(db, "No pgsql connection\n"); + return; + } + std::string ts; try { pqxx::work W(*conn); @@ -426,9 +420,13 @@ void PracroDAOPgsql::delFieldname(std::string name) std::vector PracroDAOPgsql::getFieldnames() { - if(!conn) DEBUG(db, "No pgsql connection\n"); std::vector fieldnames; + if(!conn) { + ERR(db, "No pgsql connection\n"); + return fieldnames; + } + std::string query; try { pqxx::work W(*conn); @@ -453,6 +451,11 @@ std::vector PracroDAOPgsql::getFieldnames() void PracroDAOPgsql::commit(std::string sessionid) { + if(!conn) { + ERR(db, "No pgsql connection\n"); + return; + } + std::string ts; try { pqxx::work W(*conn); @@ -467,6 +470,11 @@ void PracroDAOPgsql::commit(std::string sessionid) void PracroDAOPgsql::nocommit(std::string sessionid) { + if(!conn) { + ERR(db, "No pgsql connection\n"); + return; + } + std::string ts; try { pqxx::work W(*conn); @@ -481,6 +489,11 @@ void PracroDAOPgsql::nocommit(std::string sessionid) void PracroDAOPgsql::discard(std::string sessionid) { + if(!conn) { + ERR(db, "No pgsql connection\n"); + return; + } + std::string ts; try { pqxx::work W(*conn); @@ -494,13 +507,22 @@ void PracroDAOPgsql::discard(std::string sessionid) bool PracroDAOPgsql::idle(std::string sessionid) { + if(!conn) { + ERR(db, "No pgsql connection\n"); + return false; + } + std::string ts = "SELECT status FROM commits WHERE uid='"+sessionid+"';"; - pqxx::work W(*conn); - pqxx::result R = W.exec(ts); - pqxx::result::const_iterator ri = R.begin(); - if(ri != R.end()) { - std::string status = (*ri)[0].c_str(); - return status == "idle"; + try { + pqxx::work W(*conn); + pqxx::result R = W.exec(ts); + pqxx::result::const_iterator ri = R.begin(); + if(ri != R.end()) { + std::string status = (*ri)[0].c_str(); + return status == "idle"; + } + } catch (std::exception &e) { + ERR_LOG(db, "setIdle failed: %s: %s\n", e.what(), ts.c_str()); } return false; @@ -508,6 +530,11 @@ bool PracroDAOPgsql::idle(std::string sessionid) void PracroDAOPgsql::setIdle(std::string sessionid, bool idle) { + if(!conn) { + ERR(db, "No pgsql connection\n"); + return; + } + std::string ts; try { pqxx::work W(*conn); @@ -524,7 +551,7 @@ void PracroDAOPgsql::setIdle(std::string sessionid, bool idle) } catch (std::exception &e) { ERR_LOG(db, "setIdle failed: %s: %s\n", e.what(), ts.c_str()); } - + } #endif/*WITHOUT_DB*/ @@ -534,26 +561,257 @@ void PracroDAOPgsql::setIdle(std::string sessionid, bool idle) //cflags: -I.. $(PQXX_CXXFLAGS) //libs: $(PQXX_LIBS) #include +#include + +#define PATIENTID "1234567890" +#define MACRO "testmacro" -#include "configuration.h" -#include "exception.h" +static bool vectorFind(std::vector fs, + std::string name, std::string desc) +{ + std::vector::iterator i = fs.begin(); + while(i != fs.end()) { + Fieldname &fn = *i; + if(fn.name == name && + (desc == "" || fn.description == desc)) return true; + i++; + } + return false; +} TEST_BEGIN; -// TODO: Put some testcode here (see test.h for usable macros). -TEST_TRUE(false, "No tests yet!"); +debug_parse("+all"); -#ifndef WITHOUT_DB -/* - try { - PracroDAOPgsql db(Conf::database_addr, "", Conf::database_user, - Conf::database_passwd, ""); - } catch(Exception &e) { - printf("ERROR: %s\n", e.what()); - return 1; +PracroDAOPgsql db("localhost", "", "pracro", "pracro", "pracrotest"); + +db.addFieldname("field1", "desc1"); +db.addFieldname("field2", "desc2"); +db.addFieldname("field3", "desc3"); +db.delFieldname("field3"); + +std::vector fs = db.getFieldnames(); +TEST_EQUAL_INT(fs.size(), 2, "Test fieldname size."); +TEST_TRUE(vectorFind(fs, "field1", "desc1"), "Test fieldname 'field1'."); +TEST_TRUE(vectorFind(fs, "field2", "desc2"), "Test fieldname 'field2'."); +TEST_FALSE(vectorFind(fs, "field3", ""), "Test fieldname 'field3'."); + +std::string sid1 = db.newSessionId(); +std::string sid2 = db.newSessionId(); + +TEST_NOTEQUAL_STR(sid1, sid2, "Do not produce the same uid each time."); + +Transaction transaction; +transaction.cpr = PATIENTID; +transaction.user = "me"; + +Commit commit; +commit.fields["field1"] = "hello"; +commit.fields["field2"] = "world"; +commit.templ = "tester"; + +Macro macro; +macro.attributes["version"] = "1.0"; +macro.attributes["name"] = MACRO; + +time_t now = time(NULL); + +db.commitTransaction(sid1, transaction, commit, macro, now); + +TEST_EQUAL_INT(db.nrOfCommits(sid1, PATIENTID, MACRO, now), 1, "How many?"); + +Fieldnames fieldnames; +fieldnames.push_back("field1"); +fieldnames.push_back("field_nop"); +Values vals = db.getLatestValues(sid1, PATIENTID, ¯o, fieldnames, 0); +TEST_EQUAL_INT(vals.size(), 1, "One value"); + +TEST_NOTEQUAL(vals.find("field1"), vals.end(), "find value"); + +{ + std::string sid = db.newSessionId(); + db.commitTransaction(sid, transaction, commit, macro, now); + TEST_FALSE(db.idle(sid), "Session should not be idle."); + + db.setIdle(sid, true); + TEST_TRUE(db.idle(sid), "Session should be idle."); + + db.setIdle(sid, false); + TEST_FALSE(db.idle(sid), "Session1 should not be idle."); +} + +{ + std::string sid = db.newSessionId(); + db.commitTransaction(sid, transaction, commit, macro, now); + TEST_FALSE(db.idle(sid), "Session should not be idle."); + db.commit(sid); + TEST_FALSE(db.idle(sid), "Session is not idle (since committed != idle)."); +} + +{ + std::string sid = db.newSessionId(); + db.commitTransaction(sid, transaction, commit, macro, now); + TEST_FALSE(db.idle(sid), "Session should not be idle."); + db.nocommit(sid); + TEST_TRUE(db.idle(sid), "Session is idle."); +} + +{ + std::string sid = db.newSessionId(); + db.commitTransaction(sid, transaction, commit, macro, now); + TEST_FALSE(db.idle(sid), "Session should not be idle."); + db.discard(sid); + TEST_FALSE(db.idle(sid), "Session not idle (it doesn't exist)."); +} + +TEST_FALSE(db.idle("no such session"), "Missing session is not idle."); + +{ + Commit commit; + commit.templ = "tester"; + + std::string sid = db.newSessionId(); + + commit.fields["field1"] = "hello"; + commit.fields["field2"] = "world"; + db.commitTransaction(sid, transaction, commit, macro, now + 1); + + commit.fields["field1"] = "hello2"; + commit.fields["field2"] = "world2"; + db.commitTransaction(sid, transaction, commit, macro, now + 2); + + Fieldnames fieldnames; + fieldnames.push_back("field1"); + fieldnames.push_back("field2"); + Values vals = db.getLatestValues(sid, PATIENTID, ¯o, fieldnames, 0); + TEST_EQUAL_STR(vals["field1"].value, "hello2", "Latest one only please"); + TEST_EQUAL_STR(vals["field2"].value, "world2", "Latest one only please"); +} + +{ + Commit commit; + commit.templ = "tester"; + + std::string sid = db.newSessionId(); + + commit.fields["field1"] = "hello1"; + commit.fields["field2"] = "world1"; + db.commitTransaction(sid, transaction, commit, macro, now + 4); + + commit.fields["field1"] = "hello2"; + commit.fields["field2"] = "world2"; + db.commitTransaction(sid, transaction, commit, macro, now + 3); + + Fieldnames fieldnames; + fieldnames.push_back("field1"); + fieldnames.push_back("field2"); + Values vals = db.getLatestValues(sid, PATIENTID, ¯o, fieldnames, 0); + TEST_EQUAL_STR(vals["field1"].value, "hello1", "Latest one only please"); + TEST_EQUAL_STR(vals["field2"].value, "world1", "Latest one only please"); +} + +{ + Commit commit; + commit.templ = "tester"; + + std::string sid = db.newSessionId(); + + commit.fields["field1"] = "hello3"; + commit.fields["field2"] = "world3"; + db.commitTransaction(sid, transaction, commit, macro, now + 5); + + db.commit(sid); + + commit.fields["field1"] = "hello4"; + commit.fields["field2"] = "world4"; + db.commitTransaction(sid, transaction, commit, macro, now + 6); + + Fieldnames fieldnames; + fieldnames.push_back("field1"); + fieldnames.push_back("field2"); + Values vals = db.getLatestValues(sid, PATIENTID, ¯o, fieldnames, 0); + TEST_EQUAL_STR(vals["field1"].value, "hello3", "Latest one only please"); + TEST_EQUAL_STR(vals["field2"].value, "world3", "Latest one only please"); +} + +{ // Only see values if they are from your own session or committed. + Commit commit; + commit.templ = "tester"; + + std::string sid1 = db.newSessionId(); + std::string sid2 = db.newSessionId(); + + commit.fields["field1"] = "hello1"; + commit.fields["field2"] = "world1"; + db.commitTransaction(sid1, transaction, commit, macro, now + 7); + + commit.fields["field1"] = "hello2"; + commit.fields["field2"] = "world2"; + db.commitTransaction(sid2, transaction, commit, macro, now + 6); + + Fieldnames fieldnames; + fieldnames.push_back("field1"); + fieldnames.push_back("field2"); + { + Values vals = db.getLatestValues(sid2, PATIENTID, ¯o, fieldnames, 0); + TEST_EQUAL_STR(vals["field1"].value, "hello2", "Latest one only please"); + TEST_EQUAL_STR(vals["field2"].value, "world2", "Latest one only please"); } -*/ -#endif/*WITHOUT_DB*/ + + db.commit(sid1); + + { + Values vals = db.getLatestValues(sid2, PATIENTID, ¯o, fieldnames, 0); + TEST_EQUAL_STR(vals["field1"].value, "hello1", "Latest one only please"); + TEST_EQUAL_STR(vals["field2"].value, "world1", "Latest one only please"); + } +} + +{ + Commit commit; + commit.templ = "tester"; + + std::string sid = db.newSessionId(); + + commit.fields["foo"] = "hello"; + commit.fields["bar"] = "world"; + db.commitTransaction(sid, transaction, commit, macro, now); + + Fieldnames fieldnames; + fieldnames.push_back("foo"); + fieldnames.push_back("bar"); + Values vals = db.getLatestValues(sid, PATIENTID, ¯o, fieldnames, 0); + TEST_EQUAL_INT(vals.size(), 0, "Fields did not exists"); +} + +{ // Lets test those ERRORS + PracroDAOPgsql db("localhost", "", "pracro", "pracro", "no_such_db"); + + TEST_EQUAL_STR(db.newSessionId(), "", "Don't get session id."); + + // Just don't crash on this one... + Commit commit; + db.commitTransaction("", transaction, commit, macro, now); + + // Again don't crash. + Fieldnames fieldnames; + Values vals = db.getLatestValues("", PATIENTID, ¯o, fieldnames, 0); + TEST_EQUAL_INT(vals.size(), 0, "Don't get any values."); + + // Don't crash here either... + db.commit(""); + db.nocommit(""); + db.discard(""); + TEST_FALSE(db.idle(""), "no connection == not idle"); + db.setIdle("", true); + TEST_EQUAL_INT(db.nrOfCommits("", "", "", 0), 0, "We should get 0 commits."); + + // And again; no crash. + db.addFieldname("", ""); + db.delFieldname(""); + std::vector > f = db.getFieldnames(); + TEST_EQUAL_INT(f.size(), 0, "No fieldnames"); +} TEST_END; -- cgit v1.2.3