diff options
Diffstat (limited to 'server/src/database.cc')
| -rw-r--r-- | server/src/database.cc | 160 |
1 files changed, 50 insertions, 110 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++; } } |