1 files changed, 382 insertions, 0 deletions
diff --git a/src/modules/dbpgsql.c b/src/modules/dbpgsql.c
new file mode 100644
index 00000000..91c187ee
--- /dev/null
+++ b/src/modules/dbpgsql.c
@@ -0,0 +1,382 @@
+/*
+  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,'YYYY-MM-DD')";
+		break;
+		case SQL_TO_DATETIME:
+			return "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS')";
+		break;
+		case SQL_TO_UNIXEPOCH:
+			return "ROUND(DATE_PART('epoch',%s))";
+		break;
+		case SQL_CURRENT_TIMESTAMP:
+			return "CURRENT_TIMESTAMP";
+		break;
+		case SQL_EXPIRE:
+			return "NOW() - INTERVAL '%d DAY'";
+		break;
+		case SQL_IGNORE:
+		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);
+}
+
+u64_t db_insert_result(const char *sequence_identifier)
+{
+	char query[DEF_QUERYSIZE];
+	memset(query,0,DEF_QUERYSIZE);
+
+	u64_t insert_result;
+
+	/* postgres uses the currval call on a sequence to determine
+	 * the result value of an insert query */
+	snprintf(query, DEF_QUERYSIZE,
+		 "SELECT currval('%s%s_seq')",_db_params.pfx, sequence_identifier);
+
+	db_query(query);
+	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);
+}
+
+char * db_escape_binary(const char *from, unsigned long length)
+{
+	size_t to_length;
+	unsigned char *esc_to;
+	char *to;
+
+	esc_to = PQescapeBytea((const unsigned char *)from, length, &to_length);
+	to = g_strndup((char *)esc_to, to_length);
+	PQfreemem(esc_to);
+	return to;
+}
+
+int db_savepoint(const char *id)
+{
+	return db_savepoint_transaction(id);
+}
+int db_savepoint_rollback(const char *id)
+{
+	return db_rollback_savepoint_transaction(id);
+}
+
+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;
+}