h2. 1. Editing the configuration file

|_. Section |_. Key |_. Type |_. Change to value |_. Description |

| database| type | string | pqxx | Database type - "none", "mysql", "sqlite3", "pqxx". |

| database| database | string | Name of the already create empty database | Database used to store data. |

| database| server | string | Database server | Database server. |

| database| user | string | PostgreSQL user. | PostgreSQL user. |

| database| password | string | PostgreSQL Password. | PostgreSQL Password. |

| database| prefix | string | | Prefix of tables in database. |

h2. 2. Creating the database

Spectrum 2 will create the database on the first execution. Once the database is created, you can remove the CREATE TABLE permissions to the PostgreSQL database user you use to connect the SQL.

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_libcommuni_backend

# For skype:

#backend=/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

# "sqlite3", "mysql", "pqxx", or "none" without database backend

type = none

# For SQLite3: Full path to database

# For MySQL and PostgreSQL: name of 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_

[registration]

# Enable public registrations

enable_public_registration=1

# Text to display upon user registration form

#username_label=Jabber JID (e.g. user@server.tld):

#instructions=Enter your remote jabber JID and password as well as your local username and password

# If True a local jabber account on <local_account_server> is needed 

# for transport registration, the idea is to enable public registration

# from other servers, but only for users, who have already local accounts

#require_local_account=1

#local_username_label=Local username (without @server.tld):

#local_account_server=localhost

#local_account_server_timeout=10000

/**

 * libtransport -- C++ library for easy XMPP Transports development

 *

 * 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

 */

#ifdef WITH_PQXX

#include "transport/pqxxbackend.h"

#include "transport/util.h"

#include <boost/bind.hpp>

#include "log4cxx/logger.h"

using namespace log4cxx;

using namespace boost;

namespace Transport {

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")

		);

	try {

	}

	catch (std::exception& e) {

		LOG4CXX_ERROR(logger, e.what());

		return false;

	}

	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 'false',"

				"online boolean NOT NULL  default 'false',"

				"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>

std::string PQXXBackend::quote(pqxx::nontransaction &txn, const T &t) {

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

}

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

	pqxx::nontransaction txn(*m_conn);

	return exec(txn, query, show_error);

}

bool PQXXBackend::exec(pqxx::nontransaction &txn, const std::string &query, bool 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;