From b0be417b31e2d2577c188a563d531889354b7617 Mon Sep 17 00:00:00 2001
From: deva <deva>
Date: Mon, 26 May 2008 09:26:54 +0000
Subject: Added support for ttl on queries. Some other fies on the query.
---
server/src/database.cc | 160 ++++++++++++++++---------------------------------
server/src/database.h | 9 ++-
2 files changed, 57 insertions(+), 112 deletions(-)
(limited to 'server/src')
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);
--
cgit v1.2.3