From bbe2b5f899a9c1bd7c99181f8702ec03c60f3028 Mon Sep 17 00:00:00 2001 From: bertho Date: Tue, 10 Feb 2009 13:39:25 +0000 Subject: - Rewrite part of the database backend setup and abstraction - Fix some printf's into debug statements --- server/src/pracrodaopgsql.cc | 256 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 256 insertions(+) create mode 100644 server/src/pracrodaopgsql.cc (limited to 'server/src/pracrodaopgsql.cc') diff --git a/server/src/pracrodaopgsql.cc b/server/src/pracrodaopgsql.cc new file mode 100644 index 0000000..d5646bb --- /dev/null +++ b/server/src/pracrodaopgsql.cc @@ -0,0 +1,256 @@ +/* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*- */ +/* vim: set et sw=2 ts=2: */ +/* + * This file is part of Pracro. + * + * Pracro is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * Pracro is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with Pracro; if not, write to the Free Software + * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. + */ + +/* + * Updating the old tables; + * + * ALTER TABLE transactions ADD COLUMN uid bigint; + * CREATE SEQUENCE 'trseq'; + * SELECT setval('trseq', (SELECT MAX(oid) FROM transactions)); + * UPDATE transactions SET uid = oid; + * INSERT INTO fieldnames (name, description, timestamp) VALUES ('journal.resume', 'Journal resume text', (SELECT EXTRACT(EPOCH FROM now())::integer)); + */ + +#include + +#include + +#include "pracrodaopgsql.h" +#include "debug.h" + +PracroDAOPgsql::PracroDAOPgsql(std::string _host, std::string _port, std::string _user, std::string _passwd, std::string _dbname) + : PracroDAO(_host, _port, _user, _passwd, _dbname) +{ + conn = NULL; + std::string cs; + if(host.size()) cs += " host=" + host; + if(port.size()) cs += " port=" + port; + if(user.size()) cs += " user=" + user; + if(passwd.size()) cs += " password=" + passwd; + cs += " dbname=" + (dbname.size() ? dbname : "pracro"); + conn = new pqxx::connection(cs); + PRACRO_DEBUG(db, "Pgsql connection %p (%s)\n", conn, cs.c_str()); +} + +PracroDAOPgsql::~PracroDAOPgsql() +{ + if(conn) delete conn; +} + +void PracroDAOPgsql::commitTransaction(std::string user, std::string cpr, Macro &_macro, Fields &fields, time_t now) +{ + PRACRO_DEBUG(db, "(%s, %s, %s, <%u fields>, %ld)\n", user.c_str(), cpr.c_str(), _macro.attributes["name"].c_str(), fields.size(), now); + if(!conn) PRACRO_DEBUG(db, "No pgsql connection\n"); + if(fields.size() == 0) return; + + std::string version = _macro.attributes["version"]; + std::string macro = _macro.attributes["name"]; + std::stringstream timestamp; timestamp << now; + +#ifndef WITHOUT_DB + std::string ts; + try { + pqxx::work W(*conn); + ts = "INSERT INTO transactions (uid, cpr, macro, version, \"timestamp\", \"user\") VALUES (" + " nextval('trseq'), " + " '" + W.esc(cpr) + "', " + " '" + W.esc(macro) + "', " + " '" + W.esc(version) + "', " + " '" + W.esc(timestamp.str()) + "', " + " '" + W.esc(user) + "' " + ")" + ; + PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); + pqxx::result R = W.exec(ts); + + if(fields.size() > 0) { + // field table lookup + ts = "SELECT name FROM fieldnames WHERE name IN ( "; + std::map< std::string, std::string >::iterator i = fields.begin(); + ts += "'" + W.esc(i->first) + "'"; + i++; + while(i != fields.end()) { + ts += ", '" + W.esc(i->first) + "'"; + i++; + } + ts += ")"; + PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); + R = W.exec(ts); + PRACRO_DEBUG(db, "input fields: %d, output fields: %lu\n", fields.size(), R.size()); + + // Store known fields + pqxx::result::const_iterator ri = R.begin(); + if(ri != R.end()) { + std::string name = (*ri)[0].c_str(); + PRACRO_DEBUG(db, "Storing: %s with value %s\n", name.c_str(), fields[name].c_str()); + ts = "INSERT INTO fields (transaction, name, value) VALUES ( currval('trseq'), '" + W.esc(name) + "', '" + W.esc(fields[name]) + "')"; + ri++; + while(ri != R.end()) { + name = (*ri)[0].c_str(); + + PRACRO_DEBUG(db, "Storing: %s with value %s\n", name.c_str(), fields[name].c_str()); + + ts += ", (currval('trseq'), '" + W.esc(name) + "', '" + W.esc(fields[name]) + "')"; + ri++; + } + PRACRO_DEBUG(sql, "Query: %s\n", ts.c_str()); + W.exec(ts); + } + } + W.commit(); + } catch(std::exception &e) { + PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), ts.c_str()); + } +#else +#ifdef WITH_DEBUG + std::map< std::string, std::string >::iterator i = fields.begin(); + while(i != fields.end()) { + PRACRO_DEBUG(db, "Storing field '%s': '%s'\n", i->first, i->second); + i++; + } +#endif/*WITH_DEBUG*/ +#endif/*WITHOUT_DB*/ +} + + +/* + * The following select finds the newest timestamps for each fieldname + * belonging to the designated patient and from a set of fieldnames in the + * inner query. The outer query then finds the corresponding field values. + * Note: there is no protection agains duplicate fields that could + * theoretically occur within the same transaction. + * + * SELECT ff.name, ff.value, tt.timestamp FROM ( + * SELECT f.name, max(t.timestamp) AS ts + * FROM fields f, transactions t + * WHERE t.oid = f.transaction + * AND t.cpr = '1505050505' + * AND t.timestamp >= 0 + * AND f.name IN ('current_eye_disease') + * GROUP BY f.name) xx, + * transactions tt, fields ff + * WHERE xx.ts = tt.timestamp + * AND xx.name = ff.name + * AND tt.oid = ff.transaction + * AND tt.cpr = '1505050505' + */ +Values PracroDAOPgsql::getLatestValues(std::string cpr, Macro *macro, Fieldnames &fieldnames, time_t oldest) +{ + PRACRO_DEBUG(db, "(%s, %s, <%u fieldnames>, %ld)\n", cpr.c_str(), macro ? macro->attributes["name"].c_str() : "(null)", fieldnames.size(), oldest); + if(!conn) PRACRO_DEBUG(db, "No pgsql connection\n"); + Values values; + +#ifndef WITHOUT_DB + std::string query; + std::stringstream soldest; soldest << oldest; + try { + std::string namecond; + + pqxx::work W(*conn); + if(fieldnames.size() > 0) { + std::vector< std::string >::iterator i = fieldnames.begin(); + namecond += " AND f.name IN ('" + W.esc(*i) + "'"; + i++; + while(i != fieldnames.end()) { + 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.cpr = '" + W.esc(cpr) + "' " + + namecond; + if(macro) { + query += " AND t.macro = '" + macro->attributes["name"] + "'"; + if(macro->attributes["version"].size() > 0) + query += " AND t.version = '" + macro->attributes["version"] + "'"; + } + query += " GROUP BY f.name) xx, " + // End inner query + " transactions tt, fields ff " + " WHERE xx.ts = tt.timestamp " + " AND xx.name = ff.name " + " AND tt.uid = ff.transaction " + " AND tt.cpr = '" + W.esc(cpr) + "' " + ; + if(macro) { + query += " AND tt.macro = '" + macro->attributes["name"] + "'"; + if(macro->attributes["version"].size() > 0) + query += " AND tt.version = '" + macro->attributes["version"] + "'"; + } + + PRACRO_DEBUG(sql, "Query: %s\n", query.c_str()); + pqxx::result R = W.exec(query); + pqxx::result::const_iterator ri = R.begin(); + while(ri != R.end()) { + Value v; + v.value = (*ri)[1].c_str(); + v.timestamp = atol((*ri)[2].c_str()); + values[(*ri)[0].c_str()] = v; + ri++; + } + } catch (std::exception &e) { + PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), query.c_str()); + } +#else +#ifdef WITH_DEBUG + PRACRO_DEBUG(db, "getLatestValues(%s, , %ld) -- not implemented without database...\n", cpr.c_str(), oldest); +#endif/*WITH_DEBUG*/ +#endif/*WITHOUT_DB*/ + + return values; +} + + +unsigned PracroDAOPgsql::nrOfCommits(std::string cpr, std::string macroname, time_t oldest) +{ +#ifndef WITHOUT_DB + std::string query; + std::stringstream soldest; soldest << oldest; + try { + pqxx::work W(*conn); + query = "SELECT count(*) FROM transactions " + " WHERE cpr = '" + W.esc(cpr) + "' " + " AND macro = '" + W.esc(macroname) + "' " + " AND timestamp >= " + soldest.str() + ; + PRACRO_DEBUG(sql, "Query: %s\n", query.c_str()); + pqxx::result R = W.exec(query); + if(R.size() != 1) { + PRACRO_ERR_LOG(db, "No result set; expected one row with one column\n"); + return 0; + } + unsigned n = (unsigned)atol((*R.begin())[0].c_str()); + PRACRO_DEBUG(db, "Found %u commits for %s(%s) from %ld\n", n, cpr.c_str(), macroname.c_str(), oldest); + return n; + } catch (std::exception &e) { + PRACRO_ERR_LOG(db, "Query failed: %s: %s\n", e.what(), query.c_str()); + } +#else +#ifdef WITH_DEBUG + PRACRO_DEBUG(db, "Returning 0 commits without database\n"); +#endif/*WITH_DEBUG*/ +#endif/*WITHOUT_DB*/ + return 0; +} + -- cgit v1.2.3