path: root/modules/dbpgsql.c

/*
  Copyright (C) 1999-2004 IC & S  dbmail@ic-s.nl

  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., 675 Mass Ave, Cambridge, MA 02139, USA.
*/

/**
 * dbpgsql.c
 * PostgreSQL driver file
 * Handles connection and queries to PostgreSQL backend
 */

#include "libpq-fe.h"		/* PostgreSQL header */

#include "dbmail.h"
#define THIS_MODULE "sql"

#define BYTEAOID  17

const char * db_get_sql(sql_fragment_t frag)
{
	switch(frag) {
		case SQL_TO_CHAR:
			return "TO_CHAR(%s, 'YYYY-MM-DD HH24:MI:SS' )";
		break;
		case SQL_TO_DATE:
			return "TO_DATE(%s::text,'YYYY-MM-DD')";
		break;
		case SQL_TO_DATETIME:
			return "TO_TIMESTAMP(%s::text, 'YYYY-MM-DD HH24:MI:SS')";
		break;
		case SQL_CURRENT_TIMESTAMP:
			return "CURRENT_TIMESTAMP";
		break;
		case SQL_EXPIRE:
			return "NOW() - INTERVAL '%d DAY'";
		break;
		case SQL_BINARY:
			return "";
		break;
		case SQL_REGEXP:
			return "~";
		break;
		case SQL_SENSITIVE_LIKE:
			return "LIKE";
		break;
		case SQL_INSENSITIVE_LIKE:
			return "ILIKE";
		break;
		case SQL_ENCODE_ESCAPE:
			return "ENCODE(%s::bytea,'escape')";
		break;
		case SQL_STRCASE:
			return "LOWER(%s)";
		break;
		case SQL_PARTIAL:
			return "SUBSTRING(%s,0,255)";
		break;
	}
	return NULL;
}

static PGconn *conn = NULL;
static PGresult *res = NULL;
static void _create_binary_table(void);
static void _free_binary_table(void);
static void _set_binary_table(unsigned row, unsigned field);
static char*** bintbl = NULL;
extern db_param_t _db_params;

int db_connect()
{
	GString *cs = g_string_new("");

	/* Warn if both the host= and sqlsocket= parameters are defined.
	 * Prefer just the socket if given. */
	if (strlen(_db_params.sock) && strlen(_db_params.host)
	 && strncmp(_db_params.host, "localhost", FIELDSIZE != 0)) {
		TRACE(TRACE_WARNING, "PostgreSQL socket and a hostname other "
		      "than localhost have both been defined. The socket "
		      "will be used and the hostname will be ignored.");
		g_string_append_printf(cs, "host='%s'", _db_params.sock);
	} else if (strlen(_db_params.sock)) {
		g_string_append_printf(cs, "host='%s'", _db_params.sock);
	} else {
		g_string_append_printf(cs, "host='%s'", _db_params.host);
	}

	/* Add the username and password. */
	g_string_append_printf(cs,
		" user='%s' password='%s' dbname='%s'",
		_db_params.user, _db_params.pass, _db_params.db);

	/* Finally the port, if given. */
	if (_db_params.port != 0)
		g_string_append_printf(cs, " port='%d'", _db_params.port);

	conn = PQconnectdb(cs->str);

	g_string_free(cs, TRUE);

	if (PQstatus(conn) == CONNECTION_BAD) {
		TRACE(TRACE_ERROR, "PQconnectdb failed: %s", PQerrorMessage(conn));
		return -1;
	}

	return 0;
}

int db_check_connection() {
	if (!conn) {
		/* There seem to be some circumstances which cause
		 * db_check_connection to be called before db_connect. */
		TRACE(TRACE_ERROR, "connection with database invalid, retrying");
		return db_connect();
	}

	if (PQstatus(conn) == CONNECTION_BAD) {
		PQreset(conn);
		if (PQstatus(conn) == CONNECTION_BAD) {
			TRACE(TRACE_ERROR, "connection with database gone bad");
			return -1;
		}
	}	
	return 0;
}

int db_disconnect()
{
	if (res)
		db_free_result();
	PQfinish(conn);
	conn = NULL;

	return 0;
}

unsigned db_num_rows()
{
	int num_rows;
	if (!res)
		return 0;

	num_rows = PQntuples(res);
	if (num_rows < 0)
		return 0;
	else
		return (unsigned) num_rows;
}

unsigned db_num_fields()
{
	int num_fields;

	if (!res)
		return 0;
	num_fields = PQnfields(res);
	if (num_fields < 0)
		return 0;
	else
		return (unsigned) num_fields;
}

void db_free_result()
{
	_free_binary_table();
	if (res != NULL)
		PQclear(res);
	res = NULL;
}

static void _create_binary_table(void){
	unsigned rows, fields, i;
	rows = db_num_rows(); fields = db_num_fields();
	
	if(!bintbl){
		bintbl = (char***)g_malloc(sizeof(char**) * rows);
		memset(bintbl, 0, sizeof(char**) * rows);
		for(i = 0; i < rows; i++){
			*(bintbl + i) = (char**)g_malloc(sizeof(char*) * fields);
			memset(*(bintbl + i), 0, sizeof(char*) * fields);
		}
	}
}

static void _free_binary_table(void){
	unsigned rows, fields, i, j;
	rows = db_num_rows(); fields = db_num_fields();

	if(bintbl){
		for(i = 0; i < rows; i++){
			for(j = 0; j < fields; j++)
				if(bintbl[i][j])
					g_free(bintbl[i][j]);
			g_free(bintbl[i]);
		}
		g_free(bintbl);
		bintbl = NULL;
	}
	
}
static void _set_binary_table(unsigned row, unsigned field){
	unsigned char* tmp;
	size_t result_size;
	if(!bintbl[row][field]){
		tmp = PQunescapeBytea((const unsigned char *)PQgetvalue(res, row, field), &result_size);
		bintbl[row][field] = (char*)g_malloc(result_size + 1);
		memcpy(bintbl[row][field], tmp, result_size);
		PQfreemem(tmp); tmp = NULL;
		bintbl[row][field][result_size] = '\0';
	}
}

const char *db_get_result(unsigned row, unsigned field)
{
	if (!res) {
		TRACE(TRACE_WARNING, "result set is NULL");
		return NULL;
	}

	if ((row > db_num_rows()) || (field > db_num_fields())) {
		TRACE(TRACE_WARNING, "row = %u or field = %u out of range", row, field);
		return NULL;
	}
	if(PQftype(res, field) == BYTEAOID){
		_create_binary_table();
		_set_binary_table(row, field);
		return bintbl[row][field];	
	}
	return PQgetvalue(res, row, field);
}

/* get the result of the RETURNING clause added
 * by way of db_returning() */
u64_t db_insert_result(const char *sequence_identifier)
{
	u64_t insert_result;

	if (db_num_rows() == 0) {
		db_free_result();
		return 0;
	}

	insert_result = strtoull(db_get_result(0, 0), NULL, 10);
	db_free_result();
	return insert_result;
}

int db_query(const char *q)
{
	int PQresultStatusVar;
	
	db_free_result();

	g_return_val_if_fail(q != NULL,DM_EQUERY);

	if (db_check_connection())
		return DM_EQUERY;

	TRACE(TRACE_DEBUG, "[%s]", q);

	if (! (res = PQexec(conn, q)))
		return DM_EQUERY;
	
	PQresultStatusVar = PQresultStatus(res);

	if (PQresultStatusVar != PGRES_COMMAND_OK && PQresultStatusVar != PGRES_TUPLES_OK) {
		TRACE(TRACE_ERROR, "query failed [%s] : [%s]\n", q, PQresultErrorMessage(res));
		db_free_result();
		return DM_EQUERY;
	}

	return 0;
}

unsigned long db_escape_string(char *to,
			       const char *from, unsigned long length)
{
	assert(from);
	return PQescapeString(to, from, length);
}

unsigned long db_escape_binary(char *to,
			       const char *from, unsigned long length)
{
	size_t to_length;
	unsigned char *esc_to;

	esc_to = PQescapeBytea((const unsigned char *)from, length, &to_length);
	strncpy(to, (const char *)esc_to, to_length);
	PQfreemem(esc_to);
	return (unsigned long)(to_length - 1);
}
int db_do_cleanup(const char **tables, int num_tables)
{
	int result = 0;
	int i;
	char the_query[DEF_QUERYSIZE];

	for (i = 0; i < num_tables; i++) {
		snprintf(the_query, DEF_QUERYSIZE, "VACUUM %s%s", _db_params.pfx,tables[i]);
		if (db_query(the_query) == -1) {
			TRACE(TRACE_ERROR, "error vacuuming table [%s%s]", 
				_db_params.pfx,tables[i]);
			result = -1;
		}
	}
	return result;
}

u64_t db_get_length(unsigned row, unsigned field)
{
	if (!res) {
		TRACE(TRACE_WARNING, "result set is NULL");
		return -1;
	}

	if ((row >= db_num_rows()) || (field >= db_num_fields())) {
		TRACE(TRACE_ERROR, "row = %u or field = %u out of range", row, field);
		return -1;
	}
        if(PQftype(res, field) == BYTEAOID){
                _create_binary_table();
                _set_binary_table(row, field);
		return strlen(bintbl[row][field]);
        }
	return PQgetlength(res, row, field);
}

u64_t db_get_affected_rows()

{
	char *s;
	if (! res) return 0;
	if ((s = PQcmdTuples(res)) != NULL)
		return strtoull(s, NULL, 10);
	return 0;
}

void *db_get_result_set()
{
	return (void *) res;
}

void db_set_result_set(void *the_result_set)
{
	res = (PGresult *) the_result_set;
}

char *db_returning(char *query)
{
	unsigned bufferlen = 10 + strlen(query) + 1;
	char *buffer = g_new0(char, bufferlen);
	snprintf(buffer, bufferlen,
		"RETURNING %s", query);
	return buffer;
}