* Copyright (C) 2011, Jan Kaluza <hanzz.k@gmail.com>

 *

 * This program 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.

 *

 * This program 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 this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02111-1301  USA

 */

#pragma once

#ifdef WITH_PQXX

#include <string>

#include <map>

#include "Swiften/Swiften.h"

#include "transport/storagebackend.h"

#include "transport/config.h"

#include <pqxx/pqxx>

namespace Transport {

/// Used to store transport data into SQLite3 database.

class PQXXBackend : public StorageBackend

{

	public:

		/// Creates new PQXXBackend instance.

		/// \param config cofiguration, this class uses following Config values:

		/// 	- database.database - path to SQLite3 database file, database file is created automatically

		/// 	- service.prefix - prefix for tables created by createDatabase method

		PQXXBackend(Config *config);

		/// Destructor.

		~PQXXBackend();

		/// Connects to the database and creates it if it's needed. This method call createDatabase() function

		/// automatically.

		/// \return true if database is opened successfully.

		bool connect();

		void disconnect();

		/// Creates database structure.

		/// \see connect()

		/// \return true if database structure has been created successfully. Note that it returns True also if database structure

		/// already exists.

		bool createDatabase();

		/// Stores user into database.

		/// \param user user struct containing all information about user which have to be stored

		void setUser(const UserInfo &user);

		/// Gets user data from database and stores them into user reference.

		/// \param barejid barejid of user

		/// \param user UserInfo object where user data will be stored

		/// \return true if user has been found in database

		bool getUser(const std::string &barejid, UserInfo &user);

		/// Changes users online state variable in database.

		/// \param id id of user - UserInfo.id

		/// \param online online state

		void setUserOnline(long id, bool online);

		/// Removes user and all connected data from database.

		/// \param id id of user - UserInfo.id

		/// \return true if user has been found in database and removed

		bool removeUser(long id);

		/// Returns JIDs of all buddies in user's roster.

		/// \param id id of user - UserInfo.id

		/// \param roster string list used to store user's roster

		/// \return true if user has been found in database and roster has been fetched

		bool getBuddies(long id, std::list<BuddyInfo> &roster);

		bool getOnlineUsers(std::vector<std::string> &users);

		long addBuddy(long userId, const BuddyInfo &buddyInfo);

		void updateBuddy(long userId, const BuddyInfo &buddyInfo);

		void removeBuddy(long id) {}

		void getUserSetting(long userId, const std::string &variable, int &type, std::string &value);

		void updateUserSetting(long userId, const std::string &variable, const std::string &value);

		void beginTransaction();

		void commitTransaction();

	private:

		bool exec(const std::string &query, bool show_error = true);

		template<typename T>

		Config *m_config;

		std::string m_prefix;

		pqxx::connection *m_conn;

};

}

#endif

[service]

# 1 if Spectrum should run in server mode.

server_mode = 1

# The name of user/group Spectrum runs as.

#user=spectrum

#group=spectrum

# JID of Spectrum instance.

jid = localhost

# Password used to connect the XMPP server in gateway mode.

# In server mode, this option is ignored.

password = secret

# XMPP server to which Spectrum connects in gateway mode.

# In server mode, this option is ignored.

server = 127.0.0.1

# XMPP server port.

port = 5222

# Interface on which Spectrum listens for backends.

backend_host = localhost

# Port on which Spectrum listens for backends.

# By default Spectrum chooses random backend port and there's

# no need to change it normally

#backend_port=10001

# Full path to PKCS#12 cetficiate used for TLS in server mode.

#cert=

# Certificate password if any.

#cert_password= 

# Number of users per one legacy network backend.

users_per_backend=10

# Full path to backend binary.

backend=/usr/bin/spectrum2_libpurple_backend

#backend=/usr/bin/spectrum2_libircclient-qt_backend

# For skype:

#backend=/usr/bin/setsid /usr/bin/xvfb-run -n BACKEND_ID -s "-screen 0 10x10x8" -f /tmp/x-skype-gw /usr/bin/spectrum2_skype_backend

# Libpurple protocol-id for spectrum_libpurple_backend

protocol=prpl-jabber

#protocol=prpl-msn

#protocol=prpl-icq

# prpl-any means that user sets his protocol in his JID which has to be

# in following format: protocol.username@domain.tld

# So for example: prpl-jabber.hanzz.k%gmail.com@domain.tld

#protocol=prpl-any

[identity]

# Name of Spectrum instance in service discovery

name=Spectrum Jabber Transport

# Type of transport ("msn", "icq", "xmpp").

# Check http://xmpp.org/registrar/disco-categories.html#gateway

type=xmpp

# Category of transport, default is "gateway

#category=gateway

[logging]

# log4cxx/log4j logging configuration file in ini format used for main spectrum2 instance.

config = /etc/spectrum2/logging.cfg

# log4cxx/log4j logging configuration file in ini format used for backends.

backend_config = /etc/spectrum2/backend-logging.cfg

[database]

# Database backend type

type = none

# For SQLite3: Full path to database

# default database = /var/lib/spectrum2/$jid/database.sql

#database = jabber_transport

# Server.

#server = localhost

# Port.

#port = 0

# User.

#user = spectrum

# Paasword.

#password = secret

# Prefix used for tables

#prefix = jabber_

static LoggerPtr logger = Logger::getLogger("PQXXBackend");

PQXXBackend::PQXXBackend(Config *config) {

	m_config = config;

	m_prefix = CONFIG_STRING(m_config, "database.prefix");

}

PQXXBackend::~PQXXBackend(){

	disconnect();

}

void PQXXBackend::disconnect() {

	LOG4CXX_INFO(logger, "Disconnecting");

	delete m_conn;

}

bool PQXXBackend::connect() {

	LOG4CXX_INFO(logger, "Connecting PostgreSQL server " << CONFIG_STRING(m_config, "database.server") << ", user " <<

		CONFIG_STRING(m_config, "database.user") << ", database " << CONFIG_STRING(m_config, "database.database") <<

		", port " << CONFIG_INT(m_config, "database.port")

	);

	std::string str = "dbname=";

	str += CONFIG_STRING(m_config, "database.database") + " ";

	str += "user=" + CONFIG_STRING(m_config, "database.user") + " ";

	m_conn = new pqxx::connection(str);

	createDatabase();

	return true;

}

bool PQXXBackend::createDatabase() {

	int exist = exec("SELECT * FROM " + m_prefix + "buddies_settings LIMIT 1;", false);

	if (!exist) {

		exec("CREATE TABLE " + m_prefix + "buddies_settings ("

				"user_id integer NOT NULL,"

				"buddy_id integer NOT NULL,"

				"var varchar(50) NOT NULL,"

				"type smallint NOT NULL,"

				"value varchar(255) NOT NULL,"

				"PRIMARY KEY (buddy_id,var)"

			");");

		exec("CREATE TYPE Subscription AS ENUM ('to','from','both','ask','none');");

		exec("CREATE TABLE " + m_prefix + "buddies ("

							"id SERIAL,"

							"user_id integer NOT NULL,"

							"uin varchar(255) NOT NULL,"

							"subscription Subscription NOT NULL,"

							"nickname varchar(255) NOT NULL,"

							"groups varchar(255) NOT NULL,"

							"flags smallint NOT NULL DEFAULT '0',"

							"PRIMARY KEY (id),"

							"UNIQUE (user_id,uin)"

						");");

		exec("CREATE TABLE " + m_prefix + "users ("

				"id SERIAL,"

				"jid varchar(255) NOT NULL,"

				"uin varchar(4095) NOT NULL,"

				"password varchar(255) NOT NULL,"

				"language varchar(25) NOT NULL,"

				"encoding varchar(50) NOT NULL default 'utf8',"

				"last_login timestamp,"

				"vip boolean NOT NULL  default '0',"

				"online boolean NOT NULL  default '0',"

				"PRIMARY KEY (id),"

				"UNIQUE (jid)"

			");");

		exec("CREATE TABLE " + m_prefix + "users_settings ("

				"user_id integer NOT NULL,"

				"var varchar(50) NOT NULL,"

				"type smallint NOT NULL,"

				"value varchar(255) NOT NULL,"

				"PRIMARY KEY (user_id,var)"

			");");

		exec("CREATE TABLE " + m_prefix + "db_version ("

				"ver integer NOT NULL default '1',"

				"UNIQUE (ver)"

			");");

 		exec("INSERT INTO db_version (ver) VALUES ('1');");

	}

	return true;

}

template<typename T>

	return "'" + txn.esc(pqxx::to_string(t)) + "'";

}

bool PQXXBackend::exec(const std::string &query, bool show_error) {

	return exec(txn, query, show_error);

}

	try {

		txn.exec(query);

		txn.commit();

	}

	catch (std::exception& e) {

		if (show_error)

			LOG4CXX_ERROR(logger, e.what());

		return false;

	}

	return true;

}

void PQXXBackend::setUser(const UserInfo &user) {

	std::string encrypted = user.password;

	if (!CONFIG_STRING(m_config, "database.encryption_key").empty()) {

		encrypted = Util::encryptPassword(encrypted, CONFIG_STRING(m_config, "database.encryption_key"));

	}

	exec(txn, "INSERT INTO " + m_prefix + "users (jid, uin, password, language, encoding, last_login, vip) VALUES "

		 "(" + quote(txn, user.jid) + ","

		+ quote(txn, user.uin) + ","

		+ quote(txn, encrypted) + ","

		+ quote(txn, user.language) + ","

		+ quote(txn, user.encoding) + ","

		+ "NOW(),"

		+ (user.vip ? "'true'" : "'false'") +")");

}

bool PQXXBackend::getUser(const std::string &barejid, UserInfo &user) {

	try {

		pqxx::result r = txn.exec("SELECT id, jid, uin, password, encoding, language, vip FROM " + m_prefix + "users WHERE jid="

			+ quote(txn, barejid));

		if (r.size() == 0) {

			return false;

		}

		user.id = r[0][0].as<int>();

		user.jid = r[0][1].as<std::string>();

		user.uin = r[0][2].as<std::string>();

		user.password = r[0][3].as<std::string>();

		user.encoding = r[0][4].as<std::string>();

		user.language = r[0][5].as<std::string>();

		user.vip = r[0][6].as<bool>();

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

		return false;

	}

	return true;

}

void PQXXBackend::setUserOnline(long id, bool online) {

	try {

		exec(txn, "UPDATE " + m_prefix + "users SET online=" + (online ? "'true'" : "'false'") + ", last_login=NOW() WHERE id=" + pqxx::to_string(id));

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

	}

}

bool PQXXBackend::getOnlineUsers(std::vector<std::string> &users) {

	try {

		pqxx::result r = txn.exec("SELECT jid FROM " + m_prefix + "users WHERE online=1");

		for (pqxx::result::const_iterator it = r.begin(); it != r.end(); it++)  {

			users.push_back((*it)[0].as<std::string>());

		}

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

		return false;

	}

	return true;

}

long PQXXBackend::addBuddy(long userId, const BuddyInfo &buddyInfo) {

	pqxx::result r = txn.exec("INSERT INTO " + m_prefix + "buddies (user_id, uin, subscription, groups, nickname, flags) VALUES "

		+ "(" + pqxx::to_string(userId) + ","

		+ quote(txn, buddyInfo.legacyName) + ","

		+ quote(txn, buddyInfo.subscription) + ","

		+ quote(txn, Util::serializeGroups(buddyInfo.groups)) + ","

		+ quote(txn, buddyInfo.alias) + ","

		+ pqxx::to_string(buddyInfo.flags) + ") RETURNING id");

	long id = r[0][0].as<long>();

	r = txn.exec("UPDATE " + m_prefix + "buddies_settings SET var = " + quote(txn, buddyInfo.settings.find("icon_hash")->first) + ", type = " + pqxx::to_string((int)TYPE_STRING) + ", value = " + quote(txn, buddyInfo.settings.find("icon_hash")->second.s) + " WHERE user_id = " + pqxx::to_string(userId) + " AND buddy_id = " + pqxx::to_string(id));

	if (r.affected_rows() == 0) {

			+ "(" + pqxx::to_string(userId) + ","

			+ pqxx::to_string(id) + ","

			+ quote(txn, buddyInfo.settings.find("icon_hash")->first) + ","

			+ pqxx::to_string((int)TYPE_STRING) + ","

			+ quote(txn,  buddyInfo.settings.find("icon_hash")->second.s) + ")");

	}

	return id;

}

void PQXXBackend::updateBuddy(long userId, const BuddyInfo &buddyInfo) {

	try {

		exec(txn, "UPDATE " + m_prefix + "buddies SET groups=" + quote(txn, Util::serializeGroups(buddyInfo.groups)) + ", nickname=" + quote(txn, buddyInfo.alias) + ", flags=" + pqxx::to_string(buddyInfo.flags) + ", subscription=" + quote(txn, buddyInfo.subscription) + " WHERE user_id=" + pqxx::to_string(userId) + " AND uin=" + quote(txn, buddyInfo.legacyName));

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

	}

}

bool PQXXBackend::getBuddies(long id, std::list<BuddyInfo> &roster) {

	try {

		pqxx::result r = txn.exec("SELECT id, uin, subscription, nickname, groups, flags FROM " + m_prefix + "buddies WHERE user_id=" + pqxx::to_string(id) + " ORDER BY id ASC");

		for (pqxx::result::const_iterator it = r.begin(); it != r.end(); it++)  {

			BuddyInfo b;

			std::string group;

			b.id = r[0][0].as<long>();

			b.legacyName = r[0][1].as<std::string>();

			b.subscription = r[0][2].as<std::string>();

			b.alias = r[0][3].as<std::string>();

			group = r[0][4].as<std::string>();

			b.flags = r[0][5].as<long>();

			if (!group.empty()) {

				b.groups = Util::deserializeGroups(group);

			}

			roster.push_back(b);

		}

		r = txn.exec("SELECT buddy_id, type, var, value FROM " + m_prefix + "buddies_settings WHERE user_id=" + pqxx::to_string(id) + " ORDER BY buddy_id ASC");

		for (pqxx::result::const_iterator it = r.begin(); it != r.end(); it++)  {

			SettingVariableInfo var;

			long buddy_id = -1;

			std::string key;

			std::string val;

			buddy_id = r[0][0].as<long>();

			var.type = r[0][1].as<long>();

			key = r[0][2].as<std::string>();

			val = r[0][3].as<std::string>();

			switch (var.type) {

				case TYPE_BOOLEAN:

					var.b = atoi(val.c_str());

					break;

				case TYPE_STRING:

					var.s = val;

					break;

				default:

					continue;

					break;

			}

			BOOST_FOREACH(BuddyInfo &b, roster) {

				if (buddy_id == b.id) {

					b.settings[key] = var;

					break;

				}

			}

		}

		return true;

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

	}

	return false;

}

bool PQXXBackend::removeUser(long id) {

	try {

		exec(txn, "DELETE FROM " + m_prefix + "users SET id=" + pqxx::to_string(id));

		exec(txn, "DELETE FROM " + m_prefix + "buddies SET user_id=" + pqxx::to_string(id));

		exec(txn, "DELETE FROM " + m_prefix + "user_settings SET user_id=" + pqxx::to_string(id));

		exec(txn, "DELETE FROM " + m_prefix + "buddies_settings SET user_id=" + pqxx::to_string(id));

		return true;

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

	}

	return false;

}

void PQXXBackend::getUserSetting(long id, const std::string &variable, int &type, std::string &value) {

	try {

		pqxx::result r = txn.exec("SELECT type, value FROM " + m_prefix + "users_settings WHERE user_id=" + pqxx::to_string(id) + " AND var=" + quote(txn, variable));

		if (r.size() == 0) {

		}

		else {

			type = r[0][0].as<int>();

			value = r[0][1].as<std::string>();

		}

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

	}

}

void PQXXBackend::updateUserSetting(long id, const std::string &variable, const std::string &value) {

	try {

		exec(txn, "UPDATE " + m_prefix + "users_settings SET value=" + quote(txn, value) + " WHERE user_id=" + pqxx::to_string(id) + " AND var=" + quote(txn, variable));

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

	}

}

void PQXXBackend::beginTransaction() {

}

void PQXXBackend::commitTransaction() {

	exec("COMMIT;");

}

}

#endif