- Rewrite part of the database backend setup and abstraction

- Fix some printf's into debug statements

1 files changed, 256 insertions, 0 deletions

diff --git a/server/src/pracrodaopgsql.cc b/server/src/pracrodaopgsql.cc
new file mode 100644
index 0000000..d5646bb
--- /dev/null
+++ b/server/src/pracrodaopgsql.cc
@@ -0,0 +1,256 @@
+/* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*- */
+/* vim: set et sw=2 ts=2: */
+/*
+ *  This file is part of Pracro.
+ *
+ *  Pracro is free software; you can redistribute it and/or modify
+ *  it under the terms of the GNU General Public License as published by
+ *  the Free Software Foundation; either version 2 of the License, or
+ *  (at your option) any later version.
+ *
+ *  Pracro is distributed in the hope that it will be useful,
+ *  but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ *  GNU General Public License for more details.
+ *
+ *  You should have received a copy of the GNU General Public License
+ *  along with Pracro; if not, write to the Free Software
+ *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
+ */
+
+/*
+ * Updating the old tables;
+ *
+ * ALTER TABLE transactions ADD COLUMN uid bigint;
+ * 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));
+ */
+
+#include <config.h>
+
+#include <stdlib.h>
+
+#include "pracrodaopgsql.h"
+#include "debug.h"
+
+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;
+  std::string cs;
+  if(host.size()) cs += " host=" + host;
+  if(port.size()) cs += " port=" + port;
+  if(user.size()) cs += " user=" + user;
+  if(passwd.size()) cs += " password=" + passwd;
+  cs += " dbname=" + (dbname.size() ? dbname : "pracro");
+  conn = new pqxx::connection(cs);
+  PRACRO_DEBUG(db, "Pgsql connection %p (%s)\n", conn, cs.c_str());
+}
+
+PracroDAOPgsql::~PracroDAOPgsql()
+{
+  if(conn)  delete conn;
+}
+
+void PracroDAOPgsql::commitTransaction(std::string user, std::string cpr, Macro &_macro, Fields &fields, time_t now)
+{
+  PRACRO_DEBUG(db, "(%s, %s, %s, <%u fields>, %ld)\n", user.c_str(), cpr.c_str(), _macro.attributes["name"].c_str(), fields.size(), now);
+  if(!conn) PRACRO_DEBUG(db, "No pgsql connection\n");
+  if(fields.size() == 0) return;
+
+  std::string version = _macro.attributes["version"];
+  std::string macro = _macro.attributes["name"];
+  std::stringstream timestamp; timestamp << now;
+
+#ifndef WITHOUT_DB
+  std::string ts;
+  try {
+    pqxx::work W(*conn);
+    ts = "INSERT INTO transactions (uid, cpr, macro, version, \"timestamp\", \"user\") VALUES ("
+      " nextval('trseq'), "
+      " '" + W.esc(cpr) + "', "
+      " '" + 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);
+
+    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) + "'";
+      i++;
+      while(i != fields.end()) {
+        ts += ", '" + W.esc(i->first) + "'";
+        i++;
+      }
+      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());
+
+      // 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]) + "')";
+        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());
+
+          ts += ", (currval('trseq'), '" + W.esc(name) + "', '" + W.esc(fields[name]) + "')";
+          ri++;
+        }
+        PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str());
+        W.exec(ts);
+      }
+    }
+    W.commit();
+  } catch(std::exception &e) {
+    PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str());
+  }
+#else
+#ifdef WITH_DEBUG
+  std::map< std::string, std::string >::iterator i = fields.begin();
+  while(i != fields.end()) {
+    PRACRO_DEBUG(db, "Storing field '%s': '%s'\n", i->first, i->second);
+    i++;
+  }
+#endif/*WITH_DEBUG*/
+#endif/*WITHOUT_DB*/
+}
+
+
+/*
+ * 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.oid = f.transaction
+ *        AND t.cpr = '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.oid = ff.transaction
+ *    AND tt.cpr = '1505050505'
+ */
+Values PracroDAOPgsql::getLatestValues(std::string cpr, Macro *macro, Fieldnames &fieldnames, time_t oldest)
+{
+  PRACRO_DEBUG(db, "(%s, %s, <%u fieldnames>, %ld)\n", cpr.c_str(), macro ? macro->attributes["name"].c_str() : "(null)", fieldnames.size(), oldest);
+  if(!conn) PRACRO_DEBUG(db, "No pgsql connection\n");
+  Values values;
+
+#ifndef WITHOUT_DB
+  std::string query;
+  std::stringstream soldest; soldest << oldest;
+  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) + "'";
+      i++;
+      while(i != fieldnames.end()) {
+        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.cpr = '" + W.esc(cpr) + "' "
+      + namecond;
+    if(macro) {
+      query += " AND t.macro = '" + macro->attributes["name"] + "'";
+      if(macro->attributes["version"].size() > 0)
+        query += " AND t.version = '" + macro->attributes["version"] + "'";
+    }
+    query += " GROUP BY f.name) xx, "
+    // End inner query
+      " transactions tt, fields ff "
+      " WHERE xx.ts = tt.timestamp "
+      "   AND xx.name = ff.name "
+      "   AND tt.uid = ff.transaction "
+      "   AND tt.cpr = '" + W.esc(cpr) + "' "
+      ;
+    if(macro) {
+      query += " AND tt.macro = '" + macro->attributes["name"] + "'";
+      if(macro->attributes["version"].size() > 0)
+        query += " AND tt.version = '" + macro->attributes["version"] + "'";
+    }
+
+    PRACRO_DEBUG(sql, "Query: %s\n", query.c_str());
+    pqxx::result R = W.exec(query);
+    pqxx::result::const_iterator ri = R.begin();
+    while(ri != R.end()) {
+      Value v;
+      v.value = (*ri)[1].c_str();
+      v.timestamp = atol((*ri)[2].c_str());
+      values[(*ri)[0].c_str()] = v;
+      ri++;
+    }
+  } catch (std::exception &e) {
+    PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), query.c_str());
+  }
+#else
+#ifdef WITH_DEBUG
+  PRACRO_DEBUG(db, "getLatestValues(%s, <fields...>, %ld) -- not implemented without database...\n", cpr.c_str(), oldest);
+#endif/*WITH_DEBUG*/
+#endif/*WITHOUT_DB*/
+
+  return values;
+}
+
+
+unsigned PracroDAOPgsql::nrOfCommits(std::string cpr, std::string macroname, time_t oldest)
+{
+#ifndef WITHOUT_DB
+  std::string query;
+  std::stringstream soldest; soldest << oldest;
+  try {
+    pqxx::work W(*conn);
+    query = "SELECT count(*) FROM transactions "
+      " WHERE cpr = '" + W.esc(cpr) + "' "
+      " AND macro = '" + W.esc(macroname) + "' "
+      " AND timestamp >= " + soldest.str()
+      ;
+    PRACRO_DEBUG(sql, "Query: %s\n", query.c_str());
+    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, cpr.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());
+  }
+#else
+#ifdef WITH_DEBUG
+  PRACRO_DEBUG(db, "Returning 0 commits without database\n");
+#endif/*WITH_DEBUG*/
+#endif/*WITHOUT_DB*/
+  return 0;
+}
+