path: root/server/src/database.cc

/* -*- 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 <config.h>
#include <stdlib.h>

#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, <fields...>, %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*/