Some small changes in the postgres error handling, and a lot of tests, inspired by testdb.

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;