Added support for ttl on queries. Some other fies on the query.

2 files changed, 57 insertions, 112 deletions

diff --git a/server/src/database.cc b/server/src/database.cc
index fc87339..1bee954 100644
--- a/server/src/database.cc
+++ b/server/src/database.cc
@@ -38,7 +38,7 @@ Database::~Database()
 void Database::commit(std::string user,
                       std::string cpr,
                       Macro &_macro,
-                      Fields &values,
+                      Fields &fields,
                       time_t now)
 {
   //  / Create transaction ID (transaction OID?)
@@ -67,8 +67,8 @@ void Database::commit(std::string user,
 
   std::stringstream oid; oid << R.inserted_oid();
 
-  std::map< std::string, std::string >::iterator i = values.begin();
-  while(i != values.end()) {
+  std::map< std::string, std::string >::iterator i = fields.begin();
+  while(i != fields.end()) {
 
     std::string fs =
       "INSERT INTO fields"
@@ -82,43 +82,10 @@ void Database::commit(std::string user,
   W.commit();
 
 #if 0
-  char timestamp[32];
-  sprintf(timestamp, "%u", (unsigned int)now); 
-  //  UID uid;
-
 	try {
 		pqxx::work W(c);
-
-    //      std::string transid = transidbase + toString(idx);
-
-    // Insert transaction entry
-    std::string sql = "INSERT INTO transactions VALUES('" + 
-      cpr + "', '" + 
-      uid.toString() + "', '" + 
-      commit.macro + "', '" + 
-      commit.version + "', '" + 
-      timestamp + "', '" + 
-      user + "')";
-    W.exec(sql);
     
-    // Insert field entries
-    Fields::iterator j = commit.fields.begin();
-    while(j != commit.fields.end()) {
-      //      std::string name = j->first;
-      //      std::string value = j->second;
-
-      sql = "INSERT INTO fields VALUES('" + 
-        uid.toString() + "', '" + 
-        j->first + "', '" + 
-        j->second + "')";
-      //        name + "', '" + 
-      //        value + "')";
-      W.exec(sql);
-      
-      j++;
-    }
-
-    uid++;
+    // ...
     
 		W.commit();
 	}	catch(const std::exception &e) {
@@ -128,62 +95,59 @@ void Database::commit(std::string user,
 }
 
 
-Fields Database::getValues(std::string cpr,
+Values Database::getValues(std::string cpr,
                            std::vector< std::string > &fields,
                            time_t oldest)
 {
-  Fields v;
+  Values values;
   pqxx::work W(c);
 
+  std::stringstream query;
+  query << "SELECT fields.name, fields.value, transactions.timestamp";
+  query << " FROM fields, transactions";
+  query << " WHERE transactions.cpr = '" << 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()) {
-
-    // TODO: Return only results that are recent enough (use oldest in statement)
-    std::string query = "SELECT name, value FROM fields WHERE name='" + (*i) + "'";
-    pqxx::result R = W.exec(query);
-  
-    pqxx::result::const_iterator ri = R.begin();
-    while(ri != R.end()) {
-      pqxx::result::tuple t = *ri;
-
-      v[t[0].c_str()] = t[1].c_str();
-
-      ri++;
-    }
-
+    if(first) query << " AND ( fields.name = '" << (*i) << "'";
+    else query << " OR fields.name = '" << (*i) << "'";
+    first = false;
     i++;
   }
+  query << ")";
 
-  return v;
-}
+  query << " ORDER BY transactions.timestamp"; 
 
-/*
-int Database::getTransaction(cpr, transid)
-{
-  SELECT fields.name, fields.value
-    FROM transactions, fields
-    WHERE transactions.cpr='2003791613'
-    AND transactions.transaction='1234567890'
-    AND transactions.transaction=fields.transaction
+  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;
+    }
+    
+    ri++;
+  }
+  
+  return values;
 }
-*/
 
 /*
-int Database::getMakro(cpr, macro)
-{
-  SELECT fields.name, fields.value
-    FROM transactions, fields
-    WHERE transactions.cpr='2003791613'
-    AND transactions.macro='dims'
-    AND transactions.transaction=fields.transaction
-}
-*/
+-- As root:
+-- #createuser -P -h localhost -U postgres
+-- #createdb -U postgres -h localhost pracro
 
-// som root
-// # createuser -P -h localhost -U postgres
-// # createdb -U postgres -h localhost pracro
-
-/*
 DROP DATABASE pracro;
 
 CREATE DATABASE pracro
@@ -215,34 +179,7 @@ CREATE TABLE fields
 WITH OIDS;
 ALTER TABLE fields OWNER TO pracro;
 
-
-primary key(oid) ??
-
-// Get all matching fields
-SELECT transactions.timestamp, transactions.transaction, fields.name, fields.value
-  FROM transactions, fields
-  WHERE transactions.cpr='2003791613'
-  AND transactions.transaction=fields.transaction
-  AND fields.name='fisk';
-*/
-
-/*
-  Employees:
-  Employee_ID 	Name
-  01            Hansen, Ola
-  02            Svendson, Tove
-  03            Svendson, Stephen
-  04            Pettersen, Kari
-
-  Orders:
-  Prod_ID 	Product 	Employee_ID
-  234       Printer   01
-  657       Table     03
-  865       Chair     03
-
-  SELECT Employees.Name, Orders.Product
-  FROM Employees, Orders
-  WHERE Employees.Employee_ID=Orders.Employee_ID
+-- primary key(oid) ??
 */
 
 #ifdef TEST_DATABASE
@@ -251,6 +188,8 @@ int main()
 {
   Database db;
 
+  time_t now = time(NULL);
+
   Macro macro;
   macro.attributes["name"] = "testmacro";
   macro.attributes["version"] = "1.0";
@@ -259,16 +198,17 @@ int main()
   fields["themeaning"] = "42";
   fields["microsoft"] = "waste of money";
 
-  db.commit("testuser", "1505050505", macro, fields);
+  db.commit("testuser", "1505050505", macro, fields, now);
 
   std::vector< std::string > fieldnames;
   fieldnames.push_back("microsoft");
   fieldnames.push_back("themeaning");
 
-  Fields results = db.getValues("1505050505", fieldnames);
-  Fields::iterator i = results.begin();
+  Values results = db.getValues("1505050505", fieldnames, now);
+  Values::iterator i = results.begin();
   while(i != results.end()) {
-    printf("%s -> %s\n", i->first.c_str(), i->second.c_str());
+    Value v = i->second;
+    printf("%s -> %s (%u)\n", i->first.c_str(), v.value.c_str(), (unsigned int)v.timestamp);
     i++;
   }
 }
diff --git a/server/src/database.h b/server/src/database.h
index 51c0f1a..0958b15 100644
--- a/server/src/database.h
+++ b/server/src/database.h
@@ -36,7 +36,12 @@
 
 #include <map>
 
-typedef std::map< std::string, std::string > Fields;
+class Value {
+public:
+  std::string value;
+  time_t timestamp;
+};
+typedef std::map< std::string, Value > Values;
 
 class Database {
 public:
@@ -53,7 +58,7 @@ public:
               time_t now = time(NULL));
 
   // Get a list of values from the db
-  Fields getValues(std::string cpr,
+  Values getValues(std::string cpr,
                    std::vector< std::string > &fieldnames,
                    time_t oldest = 0);