Add transaction support on database pr. pracro session. Makes it possible to discard all edited macros.

1 files changed, 132 insertions, 52 deletions

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 <config.h>
 
@@ -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<Fieldname> 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<Fieldname> 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;