Cleanup database access a bit and add a proper database schema

2 files changed, 219 insertions, 260 deletions

diff --git a/server/pracro.psql b/server/pracro.psql
new file mode 100644
index 0000000..34283d0
--- /dev/null
+++ b/server/pracro.psql
@@ -0,0 +1,60 @@
+-- vim: syn=sql
+-- As root:
+-- #createuser -P -h localhost -U postgres
+-- #createdb -U postgres -h localhost pracro
+
+-- DROP DATABASE IF EXISTS pracro;
+-- CREATE DATABASE pracro WITH OWNER = pracro ENCODING = 'UNICODE' TABLESPACE = pg_default;
+
+-- DROP SEQUENCE IF EXISTS transeq;
+-- CREATE SEQUENCE transeq;
+
+DROP TABLE IF EXISTS transactions;
+CREATE TABLE transactions
+(
+  -- "uid"       bigint PRIMARY KEY,
+  "cpr"       varchar(11),
+  "macro"     text,
+  "version"   text,
+  "timestamp" bigint,
+  "user"      text
+) WITH OIDS;
+ALTER TABLE transactions OWNER TO pracro;
+CREATE INDEX x_transactions_cpr_timestamp ON transactions("cpr", "timestamp");
+
+
+DROP TABLE IF EXISTS fieldnames;
+CREATE TABLE fieldnames
+(
+  "name"        text PRIMARY KEY,
+  "description" text,
+  "timestamp"   bigint
+) WITH OIDS;
+ALTER TABLE fieldnames OWNER TO pracro;
+
+
+DROP TABLE IF EXISTS fields;
+CREATE TABLE fields
+(
+  "transaction" bigint,
+  "name"        text,
+  "value"       text
+) WITH OIDS;
+ALTER TABLE fields OWNER TO pracro;
+-- ALTER TABLE fields ADD CONSTRAINT cx_fields_transactions FOREIGN KEY ("transaction") REFERENCES transactions("uid");
+-- ALTER TABLE fields ADD CONSTRAINT cx_fields_name FOREIGN KEY ("name") REFERENCES fieldnames("name");
+
+
+DROP TABLE IF EXISTS journal;
+CREATE TABLE journal
+(
+  "cpr"       varchar(11),
+  "macro"     text,
+  "version"   text,
+  "timestamp" bigint,
+  "user"      text,
+  "journal"   text
+) WITH OIDS;
+ALTER TABLE journal OWNER TO pracro;
+CREATE INDEX x_journal_cpr_timestamp ON journal("cpr", "timestamp");
+
diff --git a/server/src/database.cc b/server/src/database.cc
index dbc5849..ec1e736 100644
--- a/server/src/database.cc
+++ b/server/src/database.cc
@@ -27,30 +27,8 @@
 #include "database.h"
 
 #include <config.h>
+#include <stdlib.h>
 
-std::string protect(std::string in)
-{
-  std::string out;
-
-  for(size_t i = 0; i < in.size(); i++) {
-    switch(in[i]) {
-    case '\'':
-    case '\\':
-      out.append(2, in[i]);
-      break;
-
-    case '\0':
-      out.append(1, '0');
-      break;
-      
-    default:
-      out.append(1, in[i]);
-      break;
-    }
-  }
-
-  return out;
-}
 
 Database::Database(std::string hostname, std::string user, std::string password)
 #ifndef WITHOUT_DB
@@ -76,88 +54,70 @@ void Database::commit(std::string user,
   std::stringstream timestamp; timestamp << now;
 
 #ifndef WITHOUT_DB
-  pqxx::work W(c);
+  std::string ts;
+  try {
+    pqxx::work W(c);
+    ts = "INSERT INTO transactions VALUES ("
+      " '" + W.esc(cpr) + "', "
+      " '" + W.esc(macro) + "', "
+      " '" + W.esc(version) + "', "
+      " '" + W.esc(timestamp.str()) + "', "
+      " '" + W.esc(user) + "' "
+      ")"
+      ;
+    pqxx::result R = W.exec(ts);
+    std::stringstream oid;
+    oid << R.inserted_oid();
+
+    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 += ")";
+      R = W.exec(ts);
+#ifdef WITH_DEBUG
+    printf("Database::commit: input fields: %d, output fields: %lu\n", fields.size(), R.size());
 #endif/*WITHOUT_DB*/
 
-  std::string ts =
-    "INSERT INTO transactions"
-    " VALUES('"+protect(cpr)+"', '"+protect(macro)+"', '"+protect(version)+
-    "', '"+protect(timestamp.str())+"', '"+protect(user)+"')";
-
-  std::stringstream oid;
-
-#ifndef WITHOUT_DB
-  pqxx::result R = W.exec(ts);
-  oid << R.inserted_oid();
-#else
-  oid << "###GENERATED_OID###";
+      pqxx::result::const_iterator ri = R.begin();
+      // Store known fields
+      while(ri != R.end()) {
+        pqxx::result::tuple t = *ri;
+        std::string name = t[0].c_str();
+#ifdef WITH_DEBUG
+        printf("Storing: %s with value %s\n", name.c_str(), fields[name].c_str());
 #endif/*WITHOUT_DB*/
 
+        ts = "INSERT INTO fields VALUES ("
+          " '" + W.esc(oid.str()) + "', "
+          " '" + W.esc(name) + "', "
+          " '" + W.esc(fields[name]) + "'"
+          ")"
+          ;
+        W.exec(ts);
+        ri++;
+      }
+    }
+    W.commit();
+  } catch(std::exception &e) {
+    printf("Query failed: %s: %s\n", e.what(), ts.c_str());
+  }
+#else
 #ifdef WITH_DEBUG
-  printf("%s\n", ts.c_str());
-#endif/*WITH_DEBUG*/
-
-  //
-  // FIELD TABLE LOOKUP
-  //
-  std::stringstream query;
-  query << "SELECT name";
-  query << " FROM fieldnames";
   std::map< std::string, std::string >::iterator i = fields.begin();
   while(i != fields.end()) {
-    if(i == fields.begin()) query << " WHERE name = '" << protect(i->first) << "'";
-    else query << " OR name = '" << protect(i->first) << "'";
+    printf("Storing field '%s': '%s'\n", i->first, i->second);
     i++;
   }
-#ifdef WITH_DEBUG
-  printf("%s\n", query.str().c_str());
-#endif/*WITH_DEBUG*/
-
-#ifndef WITHOUT_DB
-  R = W.exec(query.str());
-  
-  pqxx::result::const_iterator ri = R.begin();
-  while(ri != R.end()) {
-    pqxx::result::tuple t = *ri;
-    std::string name = t[0].c_str();
-
-    printf("Storing: %s with value %s\n", name.c_str(), fields[name].c_str());
 
-    std::string fs =
-      "INSERT INTO fields"
-      " VALUES('"+protect(oid.str())+"', '"+protect(name)+"', '"+protect(fields[name])+"')";
-    
-#ifndef WITHOUT_DB
-    W.exec(fs);
-#endif/*WITHOUT_DB*/
-
-#ifdef WITH_DEBUG
-    printf("%s\n", fs.c_str());
 #endif/*WITH_DEBUG*/
-      
-
-    ri++;
-  }
 #endif/*WITHOUT_DB*/
-  //
-  // end of FIELD TABLE LOOKUP
-  //
-  
-#ifndef WITHOUT_DB
-  W.commit();
-#endif/*WITHOUT_DB*/
-
-#if 0
-	try {
-		pqxx::work W(c);
-    
-    // ...
-    
-		W.commit();
-	}	catch(const std::exception &e) {
-    //		throw PostgreSQLException(e.what());
-	}
-#endif/*0*/  
 }
 
 void Database::putResume(std::string user,
@@ -172,27 +132,34 @@ void Database::putResume(std::string user,
   std::stringstream timestamp; timestamp << now;
 
 #ifndef WITHOUT_DB
-  pqxx::work W(c);
-#endif/*WITHOUT_DB*/
-
-  std::string ts =
-    "INSERT INTO journal"
-    " VALUES('"+protect(cpr)+"', '"+protect(macro)+"', '"+protect(version)+
-    "', '"+protect(timestamp.str())+"', '"+protect(user)+"', '"+protect(resume)+"')";
-
-  std::stringstream oid;
-
-#ifndef WITHOUT_DB
-  pqxx::result R = W.exec(ts);
-#endif/*WITHOUT_DB*/
-
+  std::string ts;
+  try {
+    pqxx::work W(c);
+    ts = "INSERT INTO journal VALUES ("
+         "'" + W.esc(cpr) + "', "
+         "'" + W.esc(macro) + "', "
+         "'" + W.esc(version) + "', "
+         "'" + W.esc(timestamp.str()) + "', "
+         "'" + W.esc(user) + "', "
+         "'" + W.esc(resume)+"'"
+         ")";
+    pqxx::result R = W.exec(ts);
+    W.commit();
+  } catch(std::exception &e) {
+    printf("Query failed: %s: %s\n", e.what(), ts.c_str());
+  }
+#else
 #ifdef WITH_DEBUG
-  printf("%s\n", ts.c_str());
+  printf("INSERT INTO journal VALUES ('%s', '%s', '%s', '%s', '%s', '%s')\n",
+         cpr.c_str(),
+         macro.c_str(),
+         version.c_str(),
+         timestamp.str().c_str(),
+         user.c_str(),
+         resume.c_str()
+         );
 #endif/*WITH_DEBUG*/
-
-#ifndef WITHOUT_DB
-  W.commit();
-#endif/*WITHOUT_DB*/
+#endif
 }
 
 
@@ -203,29 +170,30 @@ std::string Database::getResume(std::string cpr,
   std::string resume;
 
 #ifndef WITHOUT_DB
-  pqxx::work W(c);
-#endif/*WITHOUT_DB*/
-
-  std::stringstream query;
-  query << "SELECT journal";
-  query << " FROM journal";
-  query << " WHERE cpr = '" << protect(cpr) << "'";
-  query << " AND macro = '" << protect(macro) << "'";
-  query << " ORDER BY timestamp;"; 
-
+  std::string query;
+  try {
+    pqxx::work W(c);
+    /* FIXME: argument 'oldest' is not used anywhere */
+    query = "SELECT journal FROM journal"
+      " WHERE cpr = '" + W.esc(cpr) + "'"
+      "  AND macro = '" + W.esc(macro) + "'"
+      " ORDER BY timestamp"
+      ;
+    pqxx::result R = W.exec(query);
+    pqxx::result::const_iterator ri = R.begin();
+    /* FIXME: This will only get the *last* entry of the journal in var resume due to '=' and not '+=' */
+    while(ri != R.end()) {
+      pqxx::result::tuple t = *ri;
+      resume = t[0].c_str();
+      ri++;
+    }
+  } catch (std::exception &e) {
+    printf("Query failed: %s: %s\n", e.what(), query.c_str());
+  }
+#else
 #ifdef WITH_DEBUG
-  printf("%s\n", query.str().c_str());
+  printf("SELECT journal FROM journal WHERE cpr = '%s' AND macro = '%s' ORDER BY timestamp", cpr.c_str(), macro.c_str());
 #endif/*WITH_DEBUG*/
-
-#ifndef WITHOUT_DB
-  pqxx::result R = W.exec(query.str());
-  
-  pqxx::result::const_iterator ri = R.begin();
-  while(ri != R.end()) {
-    pqxx::result::tuple t = *ri;
-    resume = t[0].c_str();
-    ri++;
-  }
 #endif/*WITHOUT_DB*/
   
   return resume;
@@ -237,54 +205,49 @@ Values Database::getValues(std::string cpr,
                            time_t oldest)
 {
   Values values;
-#ifndef WITHOUT_DB
-  pqxx::work W(c);
-#endif/*WITHOUT_DB*/
-
-  std::stringstream query;
-  query << "SELECT fields.name, fields.value, transactions.timestamp";
-  query << " FROM fields, transactions";
-  query << " WHERE transactions.cpr = '" << protect(cpr) << "'";
-  query << " AND transactions.oid = fields.transaction";
-  query << " AND transactions.timestamp >= " << oldest;
-
-  std::vector< std::string >::iterator i = fields.begin();
-  bool first = true;
-  while(i != fields.end()) {
-    if(first) query << " AND ( fields.name = '" << protect(*i) << "'";
-    else query << " OR fields.name = '" << protect(*i) << "'";
-    first = false;
-    i++;
-  }
-  if(fields.size() > 0) query << ")";
-
-  query << " ORDER BY transactions.timestamp"; 
-
-#ifdef WITH_DEBUG
-  printf("%s\n", query.str().c_str());
-#endif/*WITH_DEBUG*/
 
 #ifndef WITHOUT_DB
-  pqxx::result R = W.exec(query.str());
-  
-  pqxx::result::const_iterator ri = R.begin();
-  while(ri != R.end()) {
-    pqxx::result::tuple t = *ri;
-    
-    Value v;
-    v.value = t[1].c_str();
-    v.timestamp = atol(t[2].c_str());
-    
-    if(values.find(t[0].c_str()) == values.end() ||
-       (values.find(t[0].c_str()) != values.end() &&
-        values[t[0].c_str()].timestamp <= v.timestamp) ) {
-      values[t[0].c_str()] = v;
+  std::string query;
+  std::stringstream soldest; soldest << oldest;
+  try {
+    pqxx::work W(c);
+    query = "SELECT f.name, f.value, t.timestamp FROM fields f JOIN transactions t ON t.oid = f.transaction "
+      " WHERE t.cpr = '" + W.esc(cpr) + "' "
+      "  AND t.timestamp >= " + soldest.str();
+    if(fields.size() > 0) {
+      std::vector< std::string >::iterator i = fields.begin();
+      query += " AND f.name IN ('" + W.esc(*i) + "'";
+      i++;
+      while(i != fields.end()) {
+        query += ", '" + W.esc(*i) + "'";
+        i++;
+      }
+      query += ')';
+    }
+    query += " ORDER BY t.timestamp ";
+    pqxx::result R = W.exec(query);
+    pqxx::result::const_iterator ri = R.begin();
+    while(ri != R.end()) {
+      pqxx::result::tuple t = *ri;
+      Value v;
+      v.value = t[1].c_str();
+      v.timestamp = atol(t[2].c_str());
+      if(values.find(t[0].c_str()) == values.end() ||
+         (values.find(t[0].c_str()) != values.end() &&
+          values[t[0].c_str()].timestamp <= v.timestamp) ) {
+        values[t[0].c_str()] = v;
+      }
+      ri++;
     }
-    
-    ri++;
+  } catch (std::exception &e) {
+    printf("Query failed: %s: %s\n", e.what(), query.c_str());
   }
+#else
+#ifdef WITH_DEBUG
+  printf("getValues(%s, <fields...>, %ld) -- not implemented without database...\n", cpr.c_str(), oldest);
+#endif/*WITH_DEBUG*/
 #endif/*WITHOUT_DB*/
-  
+
   return values;
 }
 
@@ -293,97 +256,33 @@ bool Database::checkMacro(std::string cpr,
                           time_t oldest)
 {
 #ifndef WITHOUT_DB
-  pqxx::work W(c);
-#endif/*WITHOUT_DB*/
-
-  std::stringstream query;
-  query << "SELECT oid";
-  query << " FROM transactions";
-  query << " WHERE cpr = '" << protect(cpr) << "'";
-  query << " AND macro = '" << protect(macro) << "'";
-  query << " AND timestamp >= " << oldest;
-  query << " ORDER BY timestamp"; 
-
-#ifdef WITH_DEBUG
-  printf("%s\n", query.str().c_str());
-#endif/*WITH_DEBUG*/
-
-#ifndef WITHOUT_DB
+  std::string query;
+  std::stringstream soldest; soldest << oldest;
   try {
-    pqxx::result R = W.exec(query.str());
-
+    pqxx::work W(c);
+    query = "SELECT oid FROM transactions"
+      " WHERE cpr = '" + W.esc(cpr) + "'"
+      "  AND macro = '" + W.esc(macro) + "'"
+      "  AND timestamp >= " + soldest.str() + ""
+      " ORDER BY timestamp"
+      ;
+    pqxx::result R = W.exec(query);
     return R.size() != 0;
-  } catch( ... ) {
-    return false;
+  } catch(std::exception &e) {
+    printf("Query failed: %s: %s\n", e.what(), query.c_str());
   }
 #else
-  return false;
+#ifdef WITH_DEBUG
+    printf("SELECT oid FROM transactions WHERE cpr = '%s' AND macro = '%s' AND timestamp >= %ld ORDER BY timestamp\n",
+      cpr.c_str(), macro.c_str(), oldest);
+#endif/*WITH_DEBUG*/
 #endif/*WITHOUT_DB*/
+
+  return false;
 }
 
-/*
--- As root:
--- #createuser -P -h localhost -U postgres
--- #createdb -U postgres -h localhost pracro
-
-DROP DATABASE pracro;
-
-CREATE DATABASE pracro
-  WITH OWNER = pracro
-       ENCODING = 'UNICODE'
-       TABLESPACE = pg_default;
-
-DROP TABLE transactions;
-
-CREATE TABLE transactions
-(
-  "cpr" varchar(11),
-  "macro" text,
-  "version" text,
-  "timestamp" bigint,
-  "user" text
-)
-WITH OIDS;
-ALTER TABLE transactions OWNER TO pracro;
-
-DROP TABLE fields;
-
-CREATE TABLE fields
-(
-  "transaction" oid,
-  "name" text,
-  "value" text
-) 
-WITH OIDS;
-ALTER TABLE fields OWNER TO pracro;
-
-DROP TABLE journal;
-
-CREATE TABLE journal
-(
-  cpr character varying(11),
-  macro text,
-  "version" text,
-  "timestamp" bigint,
-  "user" text,
-  journal text
-)
-WITH OIDS;
-ALTER TABLE journal OWNER TO pracro;
-
-DROP TABLE fieldnames;
-
-CREATE TABLE fieldnames
-(
-  name text,
-  description text,
-  "timestamp" bigint
-)
-WITH OIDS;
-ALTER TABLE fieldnames OWNER TO pracro;
-
--- primary key(oid) ??
-*/
+
+
 
 #ifdef TEST_DATABASE