From 158915fafe286df69a68374032187ae283eb4ded Mon Sep 17 00:00:00 2001
From: deva <deva>
Date: Fri, 23 May 2008 14:55:39 +0000
Subject: Did a lot of work on the database class.

---
 server/src/database.cc | 133 ++++++++++++++++++++++++++++++++++++++++++++-----
 1 file changed, 120 insertions(+), 13 deletions(-)

(limited to 'server/src/database.cc')

diff --git a/server/src/database.cc b/server/src/database.cc
index 8f28443..fc87339 100644
--- a/server/src/database.cc
+++ b/server/src/database.cc
@@ -26,14 +26,8 @@
  */
 #include "database.h"
 
-//#include "tostring.h"
-#include "uid.h"
-
 Database::Database(std::string hostname, std::string user, std::string password)
-  : c("host=" + hostname +
-      " user=" + user +
-      " password=" + password +
-      " dbname=pracro")
+  : c("host=" + hostname + " user=" + user + " password=" + password + " dbname=pracro")
 {
 }
 
@@ -41,11 +35,56 @@ Database::~Database()
 {
 }
 
-int Database::post(std::string &user, std::string &cpr, time_t now, Commit &commit)
+void Database::commit(std::string user,
+                      std::string cpr,
+                      Macro &_macro,
+                      Fields &values,
+                      time_t now)
 {
+  //  / Create transaction ID (transaction OID?)
+  // {
+  //  \ Commit transaction data
+
+  // Commit all field values using transaction ID.
+
+  // INSERT INTO transactions VALUES('cpr', 'macro', 'version', 'timestamp', 'user')
+  // Returns INSERT oid count
+  // count == 1, oid is oid of newly inserted transaction.
+  
+  // INSERT INTO fields VALUES('oid', 'field', 'value')
+
+  std::string version = _macro.attributes["version"];
+  std::string macro = _macro.attributes["name"];
+  std::stringstream timestamp; timestamp << now;
+
+  pqxx::work W(c);
+
+  std::string ts =
+    "INSERT INTO transactions"
+    " VALUES('"+cpr+"', '"+macro+"', '"+version+"', '"+timestamp.str()+"', '"+user+"')";
+
+  pqxx::result R = W.exec(ts);
+
+  std::stringstream oid; oid << R.inserted_oid();
+
+  std::map< std::string, std::string >::iterator i = values.begin();
+  while(i != values.end()) {
+
+    std::string fs =
+      "INSERT INTO fields"
+      " VALUES('"+oid.str()+"', '"+i->first+"', '"+i->second+"')";
+    
+    W.exec(fs);
+    
+    i++;
+  }
+  
+  W.commit();
+
+#if 0
   char timestamp[32];
   sprintf(timestamp, "%u", (unsigned int)now); 
-  UID uid;
+  //  UID uid;
 
 	try {
 		pqxx::work W(c);
@@ -85,9 +124,39 @@ int Database::post(std::string &user, std::string &cpr, time_t now, Commit &comm
 	}	catch(const std::exception &e) {
     //		throw PostgreSQLException(e.what());
 	}
+#endif/*0*/  
+}
+
+
+Fields Database::getValues(std::string cpr,
+                           std::vector< std::string > &fields,
+                           time_t oldest)
+{
+  Fields v;
+  pqxx::work W(c);
+
+  std::vector< std::string >::iterator i = fields.begin();
+  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();
 
-	return 0;
+      ri++;
+    }
+
+    i++;
+  }
+
+  return v;
 }
+
 /*
 int Database::getTransaction(cpr, transid)
 {
@@ -115,32 +184,40 @@ int Database::getMakro(cpr, macro)
 // # 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),
-  "transaction" text,
   "makro" text,
   "version" text,
   "timestamp" bigint,
   "user" text
-) 
+)
 WITH OIDS;
 ALTER TABLE transactions OWNER TO pracro;
 
+DROP TABLE fields;
+
 CREATE TABLE fields
 (
-  "transaction" text,
+  "transaction" oid,
   "name" text,
   "value" text
 ) 
 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
@@ -167,3 +244,33 @@ SELECT transactions.timestamp, transactions.transaction, fields.name, fields.val
   FROM Employees, Orders
   WHERE Employees.Employee_ID=Orders.Employee_ID
 */
+
+#ifdef TEST_DATABASE
+
+int main()
+{
+  Database db;
+
+  Macro macro;
+  macro.attributes["name"] = "testmacro";
+  macro.attributes["version"] = "1.0";
+  
+  Fields fields;
+  fields["themeaning"] = "42";
+  fields["microsoft"] = "waste of money";
+
+  db.commit("testuser", "1505050505", macro, fields);
+
+  std::vector< std::string > fieldnames;
+  fieldnames.push_back("microsoft");
+  fieldnames.push_back("themeaning");
+
+  Fields results = db.getValues("1505050505", fieldnames);
+  Fields::iterator i = results.begin();
+  while(i != results.end()) {
+    printf("%s -> %s\n", i->first.c_str(), i->second.c_str());
+    i++;
+  }
+}
+
+#endif/*TEST_DATABASE*/
-- 
cgit v1.2.3