/* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*- */ /*************************************************************************** * database.cc * * Thu Sep 6 10:59:07 CEST 2007 * Copyright 2007 Bent Bisballe Nyeng * deva@aasimon.org ****************************************************************************/ /* * 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. */ #include "database.h" #include #include #include "debug.h" Database::Database(std::string hostname, std::string user, std::string password) #ifndef WITHOUT_DB : c("host=" + hostname + " user=" + user + " password=" + password + " dbname=pracro") #endif/*WITHOUT_DB*/ { } Database::~Database() { } void Database::commit(std::string user, std::string cpr, Macro &_macro, Fields &fields, time_t now) { 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(c); ts = "INSERT INTO transactions VALUES (" " '" + W.esc(cpr) + "', " " '" + W.esc(macro) + "', " " '" + W.esc(version) + "', " " '" + W.esc(timestamp.str()) + "', " " '" + W.esc(user) + "' " ")" ; pqxx::result R = W.exec(ts); std::stringstream oid; oid << R.inserted_oid(); 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 += ")"; R = W.exec(ts); PRACRO_DEBUG(db, "Database::commit: input fields: %d, output fields: %lu\n", fields.size(), R.size()); pqxx::result::const_iterator ri = R.begin(); // Store known fields while(ri != R.end()) { pqxx::result::tuple t = *ri; std::string name = t[0].c_str(); PRACRO_DEBUG(db, "Storing: %s with value %s\n", name.c_str(), fields[name].c_str()); ts = "INSERT INTO fields VALUES (" " '" + W.esc(oid.str()) + "', " " '" + W.esc(name) + "', " " '" + W.esc(fields[name]) + "'" ")" ; W.exec(ts); ri++; } } 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*/ } void Database::putResume(std::string user, std::string cpr, Macro &_macro, std::string resume, time_t now, bool store_in_journal) { 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(c); ts = "INSERT INTO journal VALUES (" "'" + W.esc(cpr) + "', " "'" + W.esc(macro) + "', " "'" + W.esc(version) + "', " "'" + W.esc(timestamp.str()) + "', " "'" + W.esc(user) + "', " "'" + W.esc(resume)+"'" ")"; pqxx::result R = 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 PRACRO_DEBUG(db, "INSERT INTO journal VALUES ('%s', '%s', '%s', '%s', '%s', '%s')\n", cpr.c_str(), macro.c_str(), version.c_str(), timestamp.str().c_str(), user.c_str(), resume.c_str() ); #endif/*WITH_DEBUG*/ #endif } std::string Database::getResume(std::string cpr, std::string macro, time_t oldest) { std::string resume; #ifndef WITHOUT_DB std::string query; try { pqxx::work W(c); /* FIXME: argument 'oldest' is not used anywhere */ query = "SELECT journal FROM journal" " WHERE cpr = '" + W.esc(cpr) + "'" " AND macro = '" + W.esc(macro) + "'" " ORDER BY timestamp" ; pqxx::result R = W.exec(query); pqxx::result::const_iterator ri = R.begin(); /* FIXME: This will only get the *last* entry of the journal in var resume due to '=' and not '+=' */ while(ri != R.end()) { pqxx::result::tuple t = *ri; resume = t[0].c_str(); 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, "SELECT journal FROM journal WHERE cpr = '%s' AND macro = '%s' ORDER BY timestamp", cpr.c_str(), macro.c_str()); #endif/*WITH_DEBUG*/ #endif/*WITHOUT_DB*/ return resume; } Values Database::getValues(std::string cpr, Fieldnames &fields, time_t oldest) { Values values; #ifndef WITHOUT_DB std::string query; std::stringstream soldest; soldest << oldest; try { pqxx::work W(c); query = "SELECT f.name, f.value, t.timestamp FROM fields f JOIN transactions t ON t.oid = f.transaction " " WHERE t.cpr = '" + W.esc(cpr) + "' " " AND t.timestamp >= " + soldest.str(); if(fields.size() > 0) { std::vector< std::string >::iterator i = fields.begin(); query += " AND f.name IN ('" + W.esc(*i) + "'"; i++; while(i != fields.end()) { query += ", '" + W.esc(*i) + "'"; i++; } query += ')'; } query += " ORDER BY t.timestamp "; pqxx::result R = W.exec(query); 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++; } } 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, "getValues(%s, , %ld) -- not implemented without database...\n", cpr.c_str(), oldest); #endif/*WITH_DEBUG*/ #endif/*WITHOUT_DB*/ return values; } bool Database::checkMacro(std::string cpr, std::string macro, time_t oldest) { #ifndef WITHOUT_DB std::string query; std::stringstream soldest; soldest << oldest; try { pqxx::work W(c); query = "SELECT oid FROM transactions" " WHERE cpr = '" + W.esc(cpr) + "'" " AND macro = '" + W.esc(macro) + "'" " AND timestamp >= " + soldest.str() + "" " ORDER BY timestamp" ; pqxx::result R = W.exec(query); return R.size() != 0; } 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, "SELECT oid FROM transactions WHERE cpr = '%s' AND macro = '%s' AND timestamp >= %ld ORDER BY timestamp\n", cpr.c_str(), macro.c_str(), oldest); #endif/*WITH_DEBUG*/ #endif/*WITHOUT_DB*/ return false; } #ifdef TEST_DATABASE int main() { Database db("localhost", "pracro", "pracro"); time_t now = time(NULL); /* 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, now); std::vector< std::string > fieldnames; fieldnames.push_back("microsoft"); fieldnames.push_back("themeaning"); Values results = db.getValues("1505050505", fieldnames, now); Values::iterator i = results.begin(); while(i != results.end()) { Value v = i->second; printf("%s -> %s (%u)\n", i->first.c_str(), v.value.c_str(), (unsigned int)v.timestamp); i++; } */ printf(db.getResume("1505050505", "B.2.5", now - 60 * 60 * 24).c_str()); } #endif/*TEST_DATABASE*/