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>

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)
{
  //  / Create transaction ID (transaction OID?)
  // {
  //  \ Commit transaction data

  // Commit all field values using transaction ID.

  // INSERT INTO transactions VALUES('cpr', 'macro', 'version', 'timestamp', 'user')
  // Returns INSERT oid count
  // count == 1, oid is oid of newly inserted transaction.
  
  // INSERT INTO fields VALUES('oid', 'field', 'value')

  std::string version = _macro.attributes["version"];
  std::string macro = _macro.attributes["name"];
  std::stringstream timestamp; timestamp << now;

#ifndef WITHOUT_DB
  pqxx::work W(c);
#endif/*WITHOUT_DB*/

  std::string ts =
    "INSERT INTO transactions"
    " VALUES('"+cpr+"', '"+macro+"', '"+version+"', '"+timestamp.str()+"', '"+user+"')";

  std::stringstream oid;

#ifndef WITHOUT_DB
  pqxx::result R = W.exec(ts);
  oid << R.inserted_oid();
#else
  oid << "###GENERATED_OID###";
#endif/*WITHOUT_DB*/

#ifdef WITH_DEBUG
  printf("%s\n", ts.c_str());
#endif/*WITH_DEBUG*/

  std::map< std::string, std::string >::iterator i = fields.begin();
  while(i != fields.end()) {

    std::string fs =
      "INSERT INTO fields"
      " VALUES('"+oid.str()+"', '"+i->first+"', '"+i->second+"')";
    
#ifndef WITHOUT_DB
    W.exec(fs);
#endif/*WITHOUT_DB*/

#ifdef WITH_DEBUG
    printf("%s\n", fs.c_str());
#endif/*WITH_DEBUG*/
    
    i++;
  }
  
#ifndef WITHOUT_DB
  W.commit();
#endif/*WITHOUT_DB*/

#if 0
	try {
		pqxx::work W(c);
    
    // ...
    
		W.commit();
	}	catch(const std::exception &e) {
    //		throw PostgreSQLException(e.what());
	}
#endif/*0*/  
}


Values Database::getValues(std::string cpr,
                           Fieldnames &fields,
                           time_t oldest)
{
  Values values;
#ifndef WITHOUT_DB
  pqxx::work W(c);
#endif/*WITHOUT_DB*/

  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()) {
    if(first) query << " AND ( fields.name = '" << (*i) << "'";
    else query << " OR fields.name = '" << (*i) << "'";
    first = false;
    i++;
  }
  query << ")";

  query << " ORDER BY transactions.timestamp"; 

#ifndef WITHOUT_DB
  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++;
  }
#endif/*WITHOUT_DB*/

#ifdef WITH_DEBUG
  printf("%s\n", query.str().c_str());
#endif/*WITH_DEBUG*/
  
  return values;
}

bool Database::checkMacro(std::string cpr,
                          std::string macro,
                          time_t oldest)
{
#ifndef WITHOUT_DB
  pqxx::work W(c);
#endif/*WITHOUT_DB*/

  std::stringstream query;
  query << "SELECT oid";
  query << " FROM transactions";
  query << " WHERE cpr = '" << cpr << "'";
  query << " AND macro = '" << macro << "'";
  query << " AND timestamp >= " << oldest;
  query << " ORDER BY timestamp"; 

#ifdef WITH_DEBUG
  printf("%s\n", query.str().c_str());
#endif/*WITH_DEBUG*/

#ifndef WITHOUT_DB
  try {
    pqxx::result R = W.exec(query.str());

    return R.size() != 0;
  } catch( ... ) {
    return false;
  }
#else
  return false;
#endif/*WITHOUT_DB*/
}

/*
-- As root:
-- #createuser -P -h localhost -U postgres
-- #createdb -U postgres -h localhost pracro

DROP DATABASE pracro;

CREATE DATABASE pracro
  WITH OWNER = pracro
       ENCODING = 'UNICODE'
       TABLESPACE = pg_default;

DROP TABLE transactions;

CREATE TABLE transactions
(
  "cpr" varchar(11),
  "macro" text,
  "version" text,
  "timestamp" bigint,
  "user" text
)
WITH OIDS;
ALTER TABLE transactions OWNER TO pracro;

DROP TABLE fields;

CREATE TABLE fields
(
  "transaction" oid,
  "name" text,
  "value" text
) 
WITH OIDS;
ALTER TABLE fields OWNER TO pracro;

-- primary key(oid) ??
*/

#ifdef TEST_DATABASE

int main()
{
  Database db;

  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++;
  }
}

#endif/*TEST_DATABASE*/