RE: [patch] SQLBOX

/* ==================================================================== 
 * The Kannel Software License, Version 1.0 
 * 
 * Copyright (c) 2001-2004 Kannel Group  
 * Copyright (c) 1998-2001 WapIT Ltd.   
 * All rights reserved. 
 * 
 * Redistribution and use in source and binary forms, with or without 
 * modification, are permitted provided that the following conditions 
 * are met: 
 * 
 * 1. Redistributions of source code must retain the above copyright 
 *    notice, this list of conditions and the following disclaimer. 
 * 
 * 2. Redistributions in binary form must reproduce the above copyright 
 *    notice, this list of conditions and the following disclaimer in 
 *    the documentation and/or other materials provided with the 
 *    distribution. 
 * 
 * 3. The end-user documentation included with the redistribution, 
 *    if any, must include the following acknowledgment: 
 *       "This product includes software developed by the 
 *        Kannel Group (http://www.kannel.org/)." 
 *    Alternately, this acknowledgment may appear in the software itself, 
 *    if and wherever such third-party acknowledgments normally appear. 
 * 
 * 4. The names "Kannel" and "Kannel Group" must not be used to 
 *    endorse or promote products derived from this software without 
 *    prior written permission. For written permission, please  
 *    contact [email protected]. 
 * 
 * 5. Products derived from this software may not be called "Kannel", 
 *    nor may "Kannel" appear in their name, without prior written 
 *    permission of the Kannel Group. 
 * 
 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED 
 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 
 * DISCLAIMED.  IN NO EVENT SHALL THE KANNEL GROUP OR ITS CONTRIBUTORS 
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,  
 * OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT  
 * OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR  
 * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,  
 * WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE  
 * OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,  
 * EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
 * ==================================================================== 
 * 
 * This software consists of voluntary contributions made by many 
 * individuals on behalf of the Kannel Group.  For more information on  
 * the Kannel Group, please see <http://www.kannel.org/>. 
 * 
 * Portions of this software are based upon software originally written at  
 * WapIT Ltd., Helsinki, Finland for the Kannel project.  
 */ 

/*
 * dlr_pgsql.c
 *
 * Implementation of handling delivery reports (DLRs)
 * for PostgreSQL database
 *
 * modeled after dlr_mysql.c
*/

#include "gwlib/gwlib.h"
#include "gwlib/dbpool.h"
#include "dlr_p.h"


#ifdef HAVE_PGSQL
#include <libpq-fe.h>

/*
 * Our connection pool to pgsql.
 */
static DBPool *pool = NULL;

/*
 * Database fields, which we are use.
 */
static struct dlr_db_fields *fields = NULL;


static void pgsql_update(const Octstr *sql)
{
    DBPoolConn *pc;
    PGresult *res;
    ExecStatusType status;

#if defined(DLR_TRACE)
     debug("dlr.pgsql", 0, "sql: %s", octstr_get_cstr(sql));
#endif

    pc = dbpool_conn_consume(pool);
    if (pc == NULL) {
        error(0, "PGSQL: Database pool got no connection! DB update failed!");
        return;
    }

    res = PQexec(pc->conn, octstr_get_cstr(sql));
    status = PQresultStatus(res);
    switch(status) {
	case PGRES_BAD_RESPONSE:
	case PGRES_NONFATAL_ERROR:
	case PGRES_FATAL_ERROR:
	    error (0, "PGSQL: %s", PQresultErrorMessage(res));
	    break;
    }
	    
    dbpool_conn_produce(pc);
}

static PGresult* pgsql_command(const Octstr *sql)
{
    int	state;
    PGresult *res = NULL;
    DBPoolConn *pc;

#if defined(DLR_TRACE)
    debug("dlr.pgsql", 0, "sql: %s", octstr_get_cstr(sql));
#endif

    pc = dbpool_conn_consume(pool);
    if (pc == NULL) {
        error(0, "PGSQL: Database pool got no connection! DB operation failed!");
        return NULL;
    }

    res = PQexec(pc->conn, octstr_get_cstr(sql));
    switch(PQresultStatus(res)) {
	    case PGRES_EMPTY_QUERY:
	    case PGRES_BAD_RESPONSE:
	    case PGRES_NONFATAL_ERROR:
	    case PGRES_FATAL_ERROR:
		    error(0, "PGSQL: %s", PQresultErrorMessage(res));
		    break;
    }
    dbpool_conn_produce(pc);
    return res;
}

static void dlr_pgsql_shutdown()
{
    dbpool_destroy(pool);
    dlr_db_fields_destroy(fields);
}

static void dlr_pgsql_add(struct dlr_entry *entry)
{
    Octstr *sql;
    PGresult *res;

    sql = octstr_format("INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s, %s, %s) VALUES "
                        "('%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%d');",
                        octstr_get_cstr(fields->table), octstr_get_cstr(fields->field_smsc),
                        octstr_get_cstr(fields->field_ts),
                        octstr_get_cstr(fields->field_src), octstr_get_cstr(fields->field_dst),
                        octstr_get_cstr(fields->field_serv), octstr_get_cstr(fields->field_url),
                        octstr_get_cstr(fields->field_mask), octstr_get_cstr(fields->field_boxc),
                        octstr_get_cstr(fields->field_status),
                        octstr_get_cstr(entry->smsc), octstr_get_cstr(entry->timestamp), octstr_get_cstr(entry->source),
                        octstr_get_cstr(entry->destination), octstr_get_cstr(entry->service), octstr_get_cstr(entry->url),
                        entry->mask, octstr_get_cstr(entry->boxc_id), 0);


    res = pgsql_command(sql);
    switch(PQresultStatus(res)) {
	case PGRES_EMPTY_QUERY:
	case PGRES_BAD_RESPONSE:
	case PGRES_NONFATAL_ERROR:
	case PGRES_FATAL_ERROR:
		error(0, "PGSQL: %s", PQresultErrorMessage(res));
		break;
    }
    octstr_destroy(sql);
    dlr_entry_destroy(entry);
}

static struct dlr_entry* dlr_pgsql_get(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
{
    struct dlr_entry *res = NULL;
    Octstr *sql;
    PGresult *result;

    sql = octstr_format("SELECT %s, %s, %s, %s, %s, %s FROM %s WHERE %s='%s' AND %s='%s';",
                        octstr_get_cstr(fields->field_mask), octstr_get_cstr(fields->field_serv),
                        octstr_get_cstr(fields->field_url), octstr_get_cstr(fields->field_src),
                        octstr_get_cstr(fields->field_dst), octstr_get_cstr(fields->field_boxc),
                        octstr_get_cstr(fields->table), octstr_get_cstr(fields->field_smsc),
                        octstr_get_cstr(smsc), octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts));


    result = pgsql_command(sql);
    octstr_destroy(sql);

    if (result == NULL) {
        return NULL;
    }
    if (PQntuples(result) < 1) {
        debug("dlr.pgsql", 0, "no rows found");
        PQclear(result);
        return NULL;
    }

    debug("dlr.pgsql", 0, "Found entry, col0=%s, col1=%s, col2=%s, col3=%s, col4=%s col5=%s",
		    PQgetvalue(result, 0, 1),
		    PQgetvalue(result, 0, 2),
		    PQgetvalue(result, 0, 3),
		    PQgetvalue(result, 0, 4),
		    PQgetvalue(result, 0, 5)
	 );

    res = dlr_entry_create();
    gw_assert(res != NULL);
    res->mask        = atoi(PQgetvalue(result, 0, 1));
    res->service     = octstr_create(PQgetvalue(result, 0, 2));
    res->url         = octstr_create(PQgetvalue(result, 0, 3));
    res->source      = octstr_create(PQgetvalue(result, 0, 4));
    res->destination = octstr_create(PQgetvalue(result, 0, 5));
    res->boxc_id     = octstr_create(PQgetvalue(result, 0, 6));
    res->smsc        = octstr_duplicate(smsc);

    PQclear(result);

    return res;
}

static void dlr_pgsql_remove(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
{
    Octstr *sql;
    PGresult *res;

    debug("dlr.pgsql", 0, "removing DLR from database");
    sql = octstr_format("DELETE FROM %s WHERE %s='%s' AND %s='%s' LIMIT 1;",
                        octstr_get_cstr(fields->table), octstr_get_cstr(fields->field_smsc),
                        octstr_get_cstr(smsc), octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts));


    res = pgsql_command(sql);
    PQclear(res);
    octstr_destroy(sql);
}

static void dlr_pgsql_update(const Octstr *smsc, const Octstr *ts, const Octstr *dst, int status)
{
    Octstr *sql;
    PGresult *res;

    debug("dlr.pgsql", 0, "updating DLR status in database");
    sql = octstr_format("UPDATE %s SET %s=%d WHERE %s='%s' AND %s='%s' LIMIT 1;",
                        octstr_get_cstr(fields->table),
                        octstr_get_cstr(fields->field_status), status,
                        octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
                        octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts));
    res = pgsql_command(sql);
    PQclear(res);
    octstr_destroy(sql);
}


static long dlr_pgsql_messages(void)
{
    Octstr *sql;
    long res;
    PGresult *result;

    sql = octstr_format("SELECT count(*) FROM %s;", octstr_get_cstr(fields->table));

    result = pgsql_command(sql);
    octstr_destroy(sql);

    if (result == NULL) {
        return -1;
    }
    if (PQntuples(result) < 1) {
        debug("dlr.pgsql", 0, "Could not get count of DLR table");
        PQclear(result);
        return 0;
    }
    res = atol(PQgetvalue(result, 0, 1));
    PQclear(result);
    octstr_destroy(sql);

    return res;
}

static void dlr_pgsql_flush(void)
{
    Octstr *sql;
    PGresult *res;

    sql = octstr_format("DELETE FROM %s;", octstr_get_cstr(fields->table));

    res = pgsql_command(sql);
    PQclear(res);
    octstr_destroy(sql);
}

static struct dlr_storage  handles = {
    .type = "pgsql",
    .dlr_add = dlr_pgsql_add,
    .dlr_get = dlr_pgsql_get,
    .dlr_update = dlr_pgsql_update,
    .dlr_remove = dlr_pgsql_remove,
    .dlr_shutdown = dlr_pgsql_shutdown,
    .dlr_messages = dlr_pgsql_messages,
    .dlr_flush = dlr_pgsql_flush
};

struct dlr_storage *dlr_init_pgsql(Cfg* cfg)
{
    CfgGroup *grp;
    List *grplist;
    Octstr *pgsql_host, *pgsql_user, *pgsql_pass, *pgsql_db, *pgsql_id;
    Octstr *p = NULL;
    long pool_size;
    DBConf *db_conf = NULL;

    /*
     * check for all mandatory directives that specify the field names
     * of the table used
     */
    if (!(grp = cfg_get_single_group(cfg, octstr_imm("dlr-db"))))
        panic(0, "DLR: PgSQL: group 'dlr-db' is not specified!");

    if (!(pgsql_id = cfg_get(grp, octstr_imm("id"))))
   	    panic(0, "DLR: PgSQL: directive 'id' is not specified!");

    fields = dlr_db_fields_create(grp);
    gw_assert(fields != NULL);

    /*
     * now grap the required information from the 'pgsql-connection' group
     * with the pgsql-id we just obtained
     *
     * we have to loop through all available PostgreSQL connection definitions
     * and search for the one we are looking for
     */

     grplist = cfg_get_multi_group(cfg, octstr_imm("pgsql-connection"));
     while (grplist && (grp = list_extract_first(grplist)) != NULL) {
        p = cfg_get(grp, octstr_imm("id"));
        if (p != NULL && octstr_compare(p, pgsql_id) == 0) {
            goto found;
        }
        if (p != NULL) octstr_destroy(p);
     }
     panic(0, "DLR: PgSQL: connection settings for id '%s' are not specified!",
           octstr_get_cstr(pgsql_id));

found:
    octstr_destroy(p);
    list_destroy(grplist, NULL);

    if (cfg_get_integer(&pool_size, grp, octstr_imm("max-connections")) == -1 || pool_size == 0)
        pool_size = 1;

    if (!(pgsql_host = cfg_get(grp, octstr_imm("host"))))
   	    panic(0, "DLR: PgSQL: directive 'host' is not specified!");
    if (!(pgsql_user = cfg_get(grp, octstr_imm("username"))))
   	    panic(0, "DLR: PgSQL: directive 'username' is not specified!");
    if (!(pgsql_pass = cfg_get(grp, octstr_imm("password"))))
   	    panic(0, "DLR: PgSQL: directive 'password' is not specified!");
    if (!(pgsql_db = cfg_get(grp, octstr_imm("database"))))
   	    panic(0, "DLR: PgSQL: directive 'database' is not specified!");

    /*
     * ok, ready to connect to the database
     */
    db_conf = gw_malloc(sizeof(DBConf));
    gw_assert(db_conf != NULL);

    db_conf->pgsql = gw_malloc(sizeof(PgSQLConf));
    gw_assert(db_conf->pgsql != NULL);

    db_conf->pgsql->pghost = pgsql_host;
    db_conf->pgsql->login = pgsql_user;
    db_conf->pgsql->password = pgsql_pass;
    db_conf->pgsql->dbName = pgsql_db;

    pool = dbpool_create(DBPOOL_PGSQL, db_conf, pool_size);
    gw_assert(pool != NULL);

    /*
     * XXX should a failing connect throw panic?!
     */
    if (dbpool_conn_count(pool) == 0)
        panic(0,"DLR: PgSQL: database pool has no connections!");

    octstr_destroy(pgsql_id);

    return &handles;
}
#else
/*
 * Return NULL , so we point dlr-core that we were
 * not compiled in.
 */
struct dlr_storage *dlr_init_pgsql(Cfg* cfg)
{
    return NULL;
}
#endif /* HAVE_PGSQL */