summaryrefslogtreecommitdiff
path: root/server/src/pracrodaopgsql.cc
diff options
context:
space:
mode:
authordeva <deva>2011-02-18 09:51:24 +0000
committerdeva <deva>2011-02-18 09:51:24 +0000
commit4a42852b13d84a283abe54ee569c6d1a8334fa09 (patch)
tree7bb1f34baf4c259d220ffcddf335678b25d4f6ec /server/src/pracrodaopgsql.cc
parenteab5027694025e34b9ed0ad44f22a2db3e89feda (diff)
Some small changes in the postgres error handling, and a lot of tests, inspired by testdb.
Diffstat (limited to 'server/src/pracrodaopgsql.cc')
-rw-r--r--server/src/pracrodaopgsql.cc480
1 files changed, 369 insertions, 111 deletions
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<Fieldname> PracroDAOPgsql::getFieldnames()
{
- if(!conn) DEBUG(db, "No pgsql connection\n");
std::vector<Fieldname> 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<Fieldname> 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 <test.h>
+#include <time.h>
+
+#define PATIENTID "1234567890"
+#define MACRO "testmacro"
-#include "configuration.h"
-#include "exception.h"
+static bool vectorFind(std::vector<Fieldname> fs,
+ std::string name, std::string desc)
+{
+ std::vector<Fieldname>::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<Fieldname> 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, &macro, 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, &macro, 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, &macro, 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, &macro, 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, &macro, 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, &macro, 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, &macro, 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, &macro, 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<Fieldname, std::allocator<Fieldname> > f = db.getFieldnames();
+ TEST_EQUAL_INT(f.size(), 0, "No fieldnames");
+}
TEST_END;