New database layout.

1 files changed, 160 insertions, 71 deletions

diff --git a/server/src/pracrodaopgsql.cc b/server/src/pracrodaopgsql.cc
index 1c96035..14cb9c1 100644
--- a/server/src/pracrodaopgsql.cc
+++ b/server/src/pracrodaopgsql.cc
@@ -60,8 +60,7 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port,
   cs += " dbname=" + (dbname.size() ? dbname : "pracro");
   try {
     conn = new pqxx::connection(cs);
-    W = new pqxx::work(*conn);
-
+    /*
     std::string ts;
     try {
       ts = "BEGIN;";
@@ -69,6 +68,7 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port,
       pqxx::result R = W->exec(ts);
     } catch(...) {
     }
+    */
   } catch(std::exception &e) {
     ERR_LOG(db, "Postgresql init failed: %s\n", e.what());
     conn = NULL;
@@ -80,59 +80,114 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port,
 PracroDAOPgsql::~PracroDAOPgsql()
 {
   if(conn) {
-    if(W) delete W;
+    //    if(W) delete W;
     delete conn;
   }
 }
 
-void PracroDAOPgsql::commitTransaction(Transaction &transaction,
+std::string PracroDAOPgsql::newSessionId()
+{
+  if(!conn) {
+    ERR(db, "No pgsql connection\n");
+    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());
+    return (*ri)[0].c_str();
+  }
+  ERR(db, "No pgsql connection\n");
+  return "";
+}
+
+void PracroDAOPgsql::commitTransaction(std::string sessionid,
+                                       Transaction &transaction,
                                        Commit &commit,
                                        Macro &_macro,
                                        time_t now)
 {
-  DEBUG(db, "(%s, %s, %s, <%u fields>, %ld)\n",
+  DEBUG(db, "commitTransaction (%s, %s, %s, <%u fields>, %ld)\n",
         transaction.user.c_str(), transaction.cpr.c_str(),
         _macro.attributes["name"].c_str(),
         commit.fields.size(), now);
   
-  if(!conn) DEBUG(db, "No pgsql connection\n");
+  if(!conn) {
+    ERR(db, "No pgsql connection\n");
+    return;
+  }
+
   if(commit.fields.size() == 0) return;
 
+  pqxx::work W(*conn);
+
   std::string version = _macro.attributes["version"];
   std::string macro = _macro.attributes["name"];
   std::stringstream timestamp; timestamp << now;
 
   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());
+    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);
+  }
+
   try {
-    ts = "INSERT INTO transactions (uid, patientid, template, macro, version,"
-      " \"timestamp\", \"user\") VALUES ("
+    ts = "INSERT INTO transactions (uid, macro, version,"
+      " \"timestamp\", \"user\", cid) VALUES ("
       " nextval('trseq'), "
-      " '" + W->esc(transaction.cpr) + "', "
-      " '" + W->esc(commit.templ) + "', "
-      " '" + W->esc(macro) + "', "
-      " '" + W->esc(version) + "', "
-      " '" + W->esc(timestamp.str()) + "', "
-      " '" + W->esc(transaction.user) + "' "
+      " '" + W.esc(macro) + "', "
+      " '" + W.esc(version) + "', "
+      " '" + W.esc(timestamp.str()) + "', "
+      " '" + W.esc(transaction.user) + "', "
+      " '" + W.esc(sessionid) + "' "
       ");"
       ;
     DEBUG(sql, "Query: %s\n", ts.c_str());
-    pqxx::result R = W->exec(ts);
-    statements += ts + "\n";
+    pqxx::result R = W.exec(ts);
 
     if(commit.fields.size() > 0) {
       // field table lookup
-      ts = "SELECT name FROM fieldnames WHERE name IN ( ";
+      ts = "SELECT DISTINCT name FROM fieldnames WHERE name IN ( ";
       std::map< std::string, std::string >::iterator i = commit.fields.begin();
-      ts += "'" + W->esc(i->first) + "'";
+      ts += "'" + W.esc(i->first) + "'";
       i++;
       while(i != commit.fields.end()) {
-        ts += ", '" + W->esc(i->first) + "'";
+        ts += ", '" + W.esc(i->first) + "'";
         i++;
       }
       ts += ");";
       DEBUG(sql, "Query: %s\n", ts.c_str());
-      R = W->exec(ts);
-      //      statements += ts + "\n";
+      R = W.exec(ts);
 
       DEBUG(db, "input fields: %d, output fields: %lu\n",
             commit.fields.size(), R.size());
@@ -144,8 +199,8 @@ void PracroDAOPgsql::commitTransaction(Transaction &transaction,
         DEBUG(db, "Storing: %s with value %s\n",
               name.c_str(), commit.fields[name].c_str());
         ts = "INSERT INTO fields (transaction, name, value) "
-          "VALUES ( currval('trseq'), '" + W->esc(name) + "', '" +
-          W->esc(commit.fields[name]) + "')";
+          "VALUES ( currval('trseq'), '" + W.esc(name) + "', '" +
+          W.esc(commit.fields[name]) + "')";
         ri++;
         while(ri != R.end()) {
           name = (*ri)[0].c_str();
@@ -153,21 +208,22 @@ void PracroDAOPgsql::commitTransaction(Transaction &transaction,
           DEBUG(db, "Storing: %s with value %s\n",
                 name.c_str(), commit.fields[name].c_str());
 
-          ts += ", (currval('trseq'), '" + W->esc(name) + "', '" +
-            W->esc(commit.fields[name]) + "')";
+          ts += ", (currval('trseq'), '" + W.esc(name) + "', '" +
+            W.esc(commit.fields[name]) + "')";
           ri++;
         }
         ts += ";";
         DEBUG(sql, "Query: %s\n", ts.c_str());
-        W->exec(ts);
-        statements += ts + "\n";
-
+        W.exec(ts);
       }
     }
-    //    W->commit();
+
+    W.commit();
+
   } catch(std::exception &e) {
     ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str());
   }
+
 }
 
 
@@ -192,11 +248,15 @@ void PracroDAOPgsql::commitTransaction(Transaction &transaction,
  *    AND tt.uid = ff.transaction
  *    AND tt.patientid = '1505050505'
  */
-Values PracroDAOPgsql::getLatestValues(std::string patientid,
+Values PracroDAOPgsql::getLatestValues(std::string sessionid,
+                                       std::string patientid,
                                        Macro *macro,
                                        Fieldnames &fieldnames,
                                        time_t oldest)
 {
+
+  bool uncom = false; // get results that are not yet committed?
+
   DEBUG(db, "(%s, %s, <%u fieldnames>, %ld)\n",
         patientid.c_str(),
         macro ? macro->attributes["name"].c_str() : "(null)",
@@ -207,23 +267,37 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid,
   std::string query;
   std::stringstream soldest; soldest << oldest;
   try {
+    {
+      pqxx::work W(*conn);
+      query = "UPDATE commits SET status='active' WHERE status='idle'"
+        " AND uid="+sessionid+";";
+      DEBUG(sql, "Query: %s\n", query.c_str());
+      /*pqxx::result R = */W.exec(query);
+      W.commit();
+    }
+
+    pqxx::work W(*conn);
     std::string namecond;
 
     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 += ')';
     }
     query = "SELECT ff.name, ff.value, tt.timestamp FROM "
     // 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) + "' "
+      " (SELECT f.name, MAX(t.timestamp) AS ts "
+      "   FROM commits c, fields f, transactions t "
+      "   WHERE ";
+    if(!uncom) query += "(c.status='committed' OR c.uid="+sessionid+") AND ";
+    query += "c.uid = t.cid AND t.uid = f.transaction"
+      " AND t.timestamp >= " + soldest.str() +
+      " AND c.patientid = '" + W.esc(patientid) + "' "
       + namecond;
     if(macro) {
       query += " AND t.macro = '" + macro->attributes["name"] + "'";
@@ -232,11 +306,14 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid,
     }
     query += " GROUP BY f.name) xx, "
     // End inner query
-      " transactions tt, fields ff "
-      " WHERE xx.ts = tt.timestamp "
+      " transactions tt, fields ff, commits cc "
+      " WHERE ";
+    if(!uncom) query += "(cc.status='committed' OR cc.uid="+sessionid+") AND ";
+    query += " xx.ts = tt.timestamp "
       "   AND xx.name = ff.name "
       "   AND tt.uid = ff.transaction "
-      "   AND tt.patientid = '" + W->esc(patientid) + "' "
+      "   AND tt.cid = cc.uid "
+      "   AND cc.patientid = '" + W.esc(patientid) + "' "
       ;
     if(macro) {
       query += " AND tt.macro = '" + macro->attributes["name"] + "'";
@@ -245,7 +322,7 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid,
     }
 
     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;
@@ -262,20 +339,26 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid,
 }
 
 
-unsigned PracroDAOPgsql::nrOfCommits(std::string patientid,
+unsigned PracroDAOPgsql::nrOfCommits(std::string sessionid,
+                                     std::string patientid,
                                      std::string macroname,
                                      time_t oldest)
 {
+
+  bool uncom = false; // get results that are not yet committed?
+
   std::string query;
   std::stringstream soldest; soldest << oldest;
   try {
-    query = "SELECT count(*) FROM transactions "
-      " WHERE patientid = '" + W->esc(patientid) + "' "
-      " AND macro = '" + W->esc(macroname) + "' "
-      " AND timestamp >= " + soldest.str()
+    pqxx::work W(*conn);
+    query = "SELECT count(*) FROM commits c, transactions f"
+      " WHERE c.patientid = '" + W.esc(patientid) + "' AND c.uid = f.cid";
+    if(!uncom) query += " AND (c.status='committed' OR c.uid="+sessionid+")";
+    query += " AND f.macro = '" + W.esc(macroname) + "' "
+      " AND f.timestamp >= " + soldest.str()
       ;
     DEBUG(sql, "Query: %s\n", query.c_str());
-    pqxx::result R = W->exec(query);
+    pqxx::result R = W.exec(query);
     if(R.size() != 1) {
       ERR_LOG(db, "No result set; expected one row with one column\n");
       return 0;
@@ -296,15 +379,16 @@ 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()) + "' "
       ")"
       ;
     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) {
     ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str());
   }
@@ -314,11 +398,12 @@ 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) + "' ";
     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) {
     ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str());
   }
@@ -331,9 +416,10 @@ std::vector<Fieldname> PracroDAOPgsql::getFieldnames()
 
   std::string query;
   try {
+    pqxx::work W(*conn);
     query = "SELECT * FROM fieldnames";
     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;
@@ -350,41 +436,44 @@ std::vector<Fieldname> PracroDAOPgsql::getFieldnames()
   return fieldnames;
 }
 
-void PracroDAOPgsql::commit()
+void PracroDAOPgsql::commit(std::string sessionid)
 {
   std::string ts;
   try {
-    W->commit();
-    statements = "";
+    pqxx::work W(*conn);
+    ts = "UPDATE commits SET status='committed' WHERE uid="+sessionid+";";
+    /*pqxx::result R = */W.exec(ts);
+    
+    W.commit();
   } catch (std::exception &e) {
     ERR_LOG(db, "Commit failed: %s: %s\n", e.what(), ts.c_str());
   }
 }
 
-void PracroDAOPgsql::discard()
+void PracroDAOPgsql::nocommit(std::string sessionid)
 {
   std::string ts;
   try {
-    W->abort();
-    statements = "";
+    pqxx::work W(*conn);
+    ts = "UPDATE commits SET status='idle' WHERE uid="+sessionid+";";
+    /*pqxx::result R = */W.exec(ts);
+    
+    W.commit();
   } catch (std::exception &e) {
-    ERR_LOG(db, "Abort (rollback) failed: %s: %s\n", e.what(), ts.c_str());
+    ERR_LOG(db, "NoCommit failed: %s: %s\n", e.what(), ts.c_str());
   }
 }
 
-std::string PracroDAOPgsql::serialise()
-{
-  return statements;
-}
-
-void PracroDAOPgsql::restore(const std::string &data)
+void PracroDAOPgsql::discard(std::string sessionid)
 {
   std::string ts;
   try {
-    DEBUG(sql, "Restore: %s\n", data.c_str());
-    pqxx::result R = W->exec(data);
-    statements = data;
-  } catch( ... ) {
+    pqxx::work W(*conn);
+    ts = "DELETE FROM commits WHERE uid="+sessionid+";";
+    /*pqxx::result R = */W.exec(ts);
+    W.commit();
+  } catch (std::exception &e) {
+    ERR_LOG(db, "Abort (rollback) failed: %s: %s\n", e.what(), ts.c_str());
   }
 }