From 13f286925b1e9e34fe71413edcba23686c005f8a Mon Sep 17 00:00:00 2001 From: deva Date: Tue, 25 Jan 2011 12:17:47 +0000 Subject: New database layout. --- server/src/pracrodaopgsql.cc | 231 ++++++++++++++++++++++++++++++------------- 1 file changed, 160 insertions(+), 71 deletions(-) (limited to 'server/src/pracrodaopgsql.cc') diff --git a/server/src/pracrodaopgsql.cc b/server/src/pracrodaopgsql.cc index 1c96035..14cb9c1 100644 --- a/server/src/pracrodaopgsql.cc +++ b/server/src/pracrodaopgsql.cc @@ -60,8 +60,7 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, cs += " dbname=" + (dbname.size() ? dbname : "pracro"); try { conn = new pqxx::connection(cs); - W = new pqxx::work(*conn); - + /* std::string ts; try { ts = "BEGIN;"; @@ -69,6 +68,7 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, pqxx::result R = W->exec(ts); } catch(...) { } + */ } catch(std::exception &e) { ERR_LOG(db, "Postgresql init failed: %s\n", e.what()); conn = NULL; @@ -80,59 +80,114 @@ PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, PracroDAOPgsql::~PracroDAOPgsql() { if(conn) { - if(W) delete W; + // if(W) delete W; delete conn; } } -void PracroDAOPgsql::commitTransaction(Transaction &transaction, +std::string PracroDAOPgsql::newSessionId() +{ + if(!conn) { + ERR(db, "No pgsql connection\n"); + return ""; + } + + pqxx::work W(*conn); + pqxx::result R = W.exec("SELECT nextval('sessionseq');"); + pqxx::result::const_iterator ri = R.begin(); + if(ri != R.end()) { + DEBUG(db, "New session id: %s\n", (*ri)[0].c_str()); + return (*ri)[0].c_str(); + } + ERR(db, "No pgsql connection\n"); + return ""; +} + +void PracroDAOPgsql::commitTransaction(std::string sessionid, + Transaction &transaction, Commit &commit, Macro &_macro, time_t now) { - DEBUG(db, "(%s, %s, %s, <%u fields>, %ld)\n", + DEBUG(db, "commitTransaction (%s, %s, %s, <%u fields>, %ld)\n", transaction.user.c_str(), transaction.cpr.c_str(), _macro.attributes["name"].c_str(), commit.fields.size(), now); - if(!conn) DEBUG(db, "No pgsql connection\n"); + if(!conn) { + ERR(db, "No pgsql connection\n"); + return; + } + if(commit.fields.size() == 0) return; + pqxx::work W(*conn); + std::string version = _macro.attributes["version"]; std::string macro = _macro.attributes["name"]; std::stringstream timestamp; timestamp << now; std::string ts; + + ts = "SELECT status FROM commits WHERE uid='"+sessionid+"';"; + pqxx::result R = W.exec(ts); + if(!R.size()) { + ts = "INSERT INTO commits (patientid, template, version," + " \"timestamp\", uid, status) VALUES (" + " '" + W.esc(transaction.cpr) + "', " + " '" + W.esc(commit.templ) + "', " + " '" + "1.0" + "', " + " '" + W.esc(timestamp.str()) + "', " + " '" + W.esc(sessionid) + "', " + " 'active' " + ");" + ; + DEBUG(sql, "Query: %s\n", ts.c_str()); + pqxx::result R = W.exec(ts); + } else { + + pqxx::result::const_iterator ri = R.begin(); + if(ri != R.end()) { + std::string status = (*ri)[0].c_str(); + if(status == "committed") { + ERR_LOG(db, "Attempt to add to committed session %s blocked!\n", + sessionid.c_str()); + return; + } + } + + ts = "UPDATE commits SET status='active' WHERE uid="+sessionid+";"; + DEBUG(sql, "Query: %s\n", ts.c_str()); + /*pqxx::result R = */W.exec(ts); + } + try { - ts = "INSERT INTO transactions (uid, patientid, template, macro, version," - " \"timestamp\", \"user\") VALUES (" + ts = "INSERT INTO transactions (uid, macro, version," + " \"timestamp\", \"user\", cid) VALUES (" " nextval('trseq'), " - " '" + W->esc(transaction.cpr) + "', " - " '" + W->esc(commit.templ) + "', " - " '" + W->esc(macro) + "', " - " '" + W->esc(version) + "', " - " '" + W->esc(timestamp.str()) + "', " - " '" + W->esc(transaction.user) + "' " + " '" + W.esc(macro) + "', " + " '" + W.esc(version) + "', " + " '" + W.esc(timestamp.str()) + "', " + " '" + W.esc(transaction.user) + "', " + " '" + W.esc(sessionid) + "' " ");" ; DEBUG(sql, "Query: %s\n", ts.c_str()); - pqxx::result R = W->exec(ts); - statements += ts + "\n"; + pqxx::result R = W.exec(ts); if(commit.fields.size() > 0) { // field table lookup - ts = "SELECT name FROM fieldnames WHERE name IN ( "; + ts = "SELECT DISTINCT name FROM fieldnames WHERE name IN ( "; std::map< std::string, std::string >::iterator i = commit.fields.begin(); - ts += "'" + W->esc(i->first) + "'"; + ts += "'" + W.esc(i->first) + "'"; i++; while(i != commit.fields.end()) { - ts += ", '" + W->esc(i->first) + "'"; + ts += ", '" + W.esc(i->first) + "'"; i++; } ts += ");"; DEBUG(sql, "Query: %s\n", ts.c_str()); - R = W->exec(ts); - // statements += ts + "\n"; + R = W.exec(ts); DEBUG(db, "input fields: %d, output fields: %lu\n", commit.fields.size(), R.size()); @@ -144,8 +199,8 @@ void PracroDAOPgsql::commitTransaction(Transaction &transaction, DEBUG(db, "Storing: %s with value %s\n", name.c_str(), commit.fields[name].c_str()); ts = "INSERT INTO fields (transaction, name, value) " - "VALUES ( currval('trseq'), '" + W->esc(name) + "', '" + - W->esc(commit.fields[name]) + "')"; + "VALUES ( currval('trseq'), '" + W.esc(name) + "', '" + + W.esc(commit.fields[name]) + "')"; ri++; while(ri != R.end()) { name = (*ri)[0].c_str(); @@ -153,21 +208,22 @@ void PracroDAOPgsql::commitTransaction(Transaction &transaction, DEBUG(db, "Storing: %s with value %s\n", name.c_str(), commit.fields[name].c_str()); - ts += ", (currval('trseq'), '" + W->esc(name) + "', '" + - W->esc(commit.fields[name]) + "')"; + ts += ", (currval('trseq'), '" + W.esc(name) + "', '" + + W.esc(commit.fields[name]) + "')"; ri++; } ts += ";"; DEBUG(sql, "Query: %s\n", ts.c_str()); - W->exec(ts); - statements += ts + "\n"; - + W.exec(ts); } } - // W->commit(); + + W.commit(); + } catch(std::exception &e) { ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str()); } + } @@ -192,11 +248,15 @@ void PracroDAOPgsql::commitTransaction(Transaction &transaction, * AND tt.uid = ff.transaction * AND tt.patientid = '1505050505' */ -Values PracroDAOPgsql::getLatestValues(std::string patientid, +Values PracroDAOPgsql::getLatestValues(std::string sessionid, + std::string patientid, Macro *macro, Fieldnames &fieldnames, time_t oldest) { + + bool uncom = false; // get results that are not yet committed? + DEBUG(db, "(%s, %s, <%u fieldnames>, %ld)\n", patientid.c_str(), macro ? macro->attributes["name"].c_str() : "(null)", @@ -207,23 +267,37 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, std::string query; std::stringstream soldest; soldest << oldest; try { + { + pqxx::work W(*conn); + query = "UPDATE commits SET status='active' WHERE status='idle'" + " AND uid="+sessionid+";"; + DEBUG(sql, "Query: %s\n", query.c_str()); + /*pqxx::result R = */W.exec(query); + W.commit(); + } + + pqxx::work W(*conn); std::string namecond; if(fieldnames.size() > 0) { std::vector< std::string >::iterator i = fieldnames.begin(); - namecond += " AND f.name IN ('" + W->esc(*i) + "'"; + namecond += " AND f.name IN ('" + W.esc(*i) + "'"; i++; while(i != fieldnames.end()) { - namecond += ", '" + W->esc(*i) + "'"; + namecond += ", '" + W.esc(*i) + "'"; i++; } namecond += ')'; } query = "SELECT ff.name, ff.value, tt.timestamp FROM " // Begin inner query - " (SELECT f.name, MAX(t.timestamp) AS ts FROM fields f, transactions t " - " WHERE t.uid = f.transaction AND t.timestamp >= " + soldest.str() + - " AND t.patientid = '" + W->esc(patientid) + "' " + " (SELECT f.name, MAX(t.timestamp) AS ts " + " FROM commits c, fields f, transactions t " + " WHERE "; + if(!uncom) query += "(c.status='committed' OR c.uid="+sessionid+") AND "; + query += "c.uid = t.cid AND t.uid = f.transaction" + " AND t.timestamp >= " + soldest.str() + + " AND c.patientid = '" + W.esc(patientid) + "' " + namecond; if(macro) { query += " AND t.macro = '" + macro->attributes["name"] + "'"; @@ -232,11 +306,14 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, } query += " GROUP BY f.name) xx, " // End inner query - " transactions tt, fields ff " - " WHERE xx.ts = tt.timestamp " + " transactions tt, fields ff, commits cc " + " WHERE "; + if(!uncom) query += "(cc.status='committed' OR cc.uid="+sessionid+") AND "; + query += " xx.ts = tt.timestamp " " AND xx.name = ff.name " " AND tt.uid = ff.transaction " - " AND tt.patientid = '" + W->esc(patientid) + "' " + " AND tt.cid = cc.uid " + " AND cc.patientid = '" + W.esc(patientid) + "' " ; if(macro) { query += " AND tt.macro = '" + macro->attributes["name"] + "'"; @@ -245,7 +322,7 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, } DEBUG(sql, "Query: %s\n", query.c_str()); - pqxx::result R = W->exec(query); + pqxx::result R = W.exec(query); pqxx::result::const_iterator ri = R.begin(); while(ri != R.end()) { Value v; @@ -262,20 +339,26 @@ Values PracroDAOPgsql::getLatestValues(std::string patientid, } -unsigned PracroDAOPgsql::nrOfCommits(std::string patientid, +unsigned PracroDAOPgsql::nrOfCommits(std::string sessionid, + std::string patientid, std::string macroname, time_t oldest) { + + bool uncom = false; // get results that are not yet committed? + std::string query; std::stringstream soldest; soldest << oldest; try { - query = "SELECT count(*) FROM transactions " - " WHERE patientid = '" + W->esc(patientid) + "' " - " AND macro = '" + W->esc(macroname) + "' " - " AND timestamp >= " + soldest.str() + pqxx::work W(*conn); + query = "SELECT count(*) FROM commits c, transactions f" + " WHERE c.patientid = '" + W.esc(patientid) + "' AND c.uid = f.cid"; + if(!uncom) query += " AND (c.status='committed' OR c.uid="+sessionid+")"; + query += " AND f.macro = '" + W.esc(macroname) + "' " + " AND f.timestamp >= " + soldest.str() ; DEBUG(sql, "Query: %s\n", query.c_str()); - pqxx::result R = W->exec(query); + pqxx::result R = W.exec(query); if(R.size() != 1) { ERR_LOG(db, "No result set; expected one row with one column\n"); return 0; @@ -296,15 +379,16 @@ void PracroDAOPgsql::addFieldname(std::string name, std::string description) std::stringstream timestamp; timestamp << time(NULL); std::string ts; try { + pqxx::work W(*conn); ts = "INSERT INTO fieldnames (name, description, \"timestamp\") VALUES (" - " '" + W->esc(name) + "', " - " '" + W->esc(description) + "', " - " '" + W->esc(timestamp.str()) + "' " + " '" + W.esc(name) + "', " + " '" + W.esc(description) + "', " + " '" + W.esc(timestamp.str()) + "' " ")" ; DEBUG(sql, "Query: %s\n", ts.c_str()); - pqxx::result R = W->exec(ts); - W->commit(); + pqxx::result R = W.exec(ts); + W.commit(); } catch (std::exception &e) { ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str()); } @@ -314,11 +398,12 @@ void PracroDAOPgsql::delFieldname(std::string name) { std::string ts; try { + pqxx::work W(*conn); ts = "DELETE FROM fieldnames WHERE name=" - "'" + W->esc(name) + "' "; + "'" + W.esc(name) + "' "; DEBUG(sql, "Query: %s\n", ts.c_str()); - pqxx::result R = W->exec(ts); - W->commit(); + pqxx::result R = W.exec(ts); + W.commit(); } catch (std::exception &e) { ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str()); } @@ -331,9 +416,10 @@ std::vector PracroDAOPgsql::getFieldnames() std::string query; try { + pqxx::work W(*conn); query = "SELECT * FROM fieldnames"; DEBUG(sql, "Query: %s\n", query.c_str()); - pqxx::result R = W->exec(query); + pqxx::result R = W.exec(query); pqxx::result::const_iterator ri = R.begin(); while(ri != R.end()) { Fieldname f; @@ -350,41 +436,44 @@ std::vector PracroDAOPgsql::getFieldnames() return fieldnames; } -void PracroDAOPgsql::commit() +void PracroDAOPgsql::commit(std::string sessionid) { std::string ts; try { - W->commit(); - statements = ""; + pqxx::work W(*conn); + ts = "UPDATE commits SET status='committed' WHERE uid="+sessionid+";"; + /*pqxx::result R = */W.exec(ts); + + W.commit(); } catch (std::exception &e) { ERR_LOG(db, "Commit failed: %s: %s\n", e.what(), ts.c_str()); } } -void PracroDAOPgsql::discard() +void PracroDAOPgsql::nocommit(std::string sessionid) { std::string ts; try { - W->abort(); - statements = ""; + pqxx::work W(*conn); + ts = "UPDATE commits SET status='idle' WHERE uid="+sessionid+";"; + /*pqxx::result R = */W.exec(ts); + + W.commit(); } catch (std::exception &e) { - ERR_LOG(db, "Abort (rollback) failed: %s: %s\n", e.what(), ts.c_str()); + ERR_LOG(db, "NoCommit failed: %s: %s\n", e.what(), ts.c_str()); } } -std::string PracroDAOPgsql::serialise() -{ - return statements; -} - -void PracroDAOPgsql::restore(const std::string &data) +void PracroDAOPgsql::discard(std::string sessionid) { std::string ts; try { - DEBUG(sql, "Restore: %s\n", data.c_str()); - pqxx::result R = W->exec(data); - statements = data; - } catch( ... ) { + pqxx::work W(*conn); + ts = "DELETE FROM commits WHERE uid="+sessionid+";"; + /*pqxx::result R = */W.exec(ts); + W.commit(); + } catch (std::exception &e) { + ERR_LOG(db, "Abort (rollback) failed: %s: %s\n", e.what(), ts.c_str()); } } -- cgit v1.2.3