---

d_postgres_f.c

Go to the documentation of this file.

/* GtkSQL -- an interactive graphical query tool for PostgreSQL
 * Copyright (C) 1998-2003  Lionel ULMER, Darryl Luff
 *
 * 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., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 */

#include "../config.h"

#ifdef USE_POSTGRESQL

#include <libpq-fe.h>
#include <postgres.h>

#include "buf.h"
#include "db_conn.h"

/* Input field names.   */
#define BASENAME    "Database"

#define BASEHOST    "Host"

#define BASEPORT    "Port"

#define BASELOGIN   "Username"

#define BASEPASS    "Password"

typedef struct {
    PGconn *pgconn;

    DBTableDef *table_def;

    int table_num;
} DBConnection_PG;

static char *psqlSqlKeywords[] = {
    "ABORT", "AGGREGATE", "ALL", "AND", "ALTER", "ASC",
    "BEGIN",
    "CLUSTER", "CLOSE", "COMMIT", "COPY", "COUNT", "CREATE",
    "DATABASE", "DECLARE", "DELETE", "DESC", "DISTINCT", "DROP",
    "END", "EXPLAIN",
    "FETCH", "FROM", "FUNCTION",
    "GRANT", "GROUP BY",
    "INDEX", "INSERT", "INTO",
    "LIKE", "LISTEN", "LOAD", "LOCK",
    "MAX", "MIN", "MEAN", "MOVE",
    "NOTIFY",
    "OPERATOR", "OR", "ORDER BY",
    "RESET", "REVOKE", "ROLLBACK", "RULE",
    "SELECT", "SEQUENCE", "SET", "SHOW", "SUM",
    "TABLE", "TRANSACTION", "TRIGGER", "TYPE",
    "UNION", "UPDATE", "USER", "USING",
    "VACUUM", "VIEW",
    "WHERE", "WORK",
    NULL
};

DBConnection *pg_dbconnection_free(DBConnection *dbc) {
    if (dbc) {
        if (dbc->pvt)
            free(dbc->pvt);
        free(dbc);
    }
    return NULL;
}

static int pg_DBdisconnect(DBConnection * dbconn)
{
    DBConnection_PG *dbpg = (DBConnection_PG *) (dbconn->pvt);
    PGconn *co = dbpg->pgconn;

    /* End connection */
    PQfinish(co);

    /* Clear Keywords memory */
    keywords_remove_all(dbconn);

    /* Free structure memory */
    pg_dbconnection_free(dbconn);

    return 0;
}

static char *pg_DBget_error_message(DBConnection * dbconn)
{
    DBConnection_PG *dbpg = (DBConnection_PG *) (dbconn->pvt);
    PGconn *co = dbpg->pgconn;

    return PQerrorMessage(co);
}

static char *pg_DBget_db_name(DBConnection * dbconn)
{
    DBConnection_PG *dbpg = (DBConnection_PG *) (dbconn->pvt);
    PGconn *co = dbpg->pgconn;

    return PQdb(co);
}

static void *pg_DBexecute_query(DBConnection * dbconn, char *query)
{
    DBConnection_PG *dbconn_pg = (DBConnection_PG *) (dbconn->pvt);
    PGconn *co = dbconn_pg->pgconn;

    return (void *) PQexec(co, query);
}

static int pg_DBget_query_status(DBConnection * dbconn, void *result)
{
    if (result == NULL)
        return DB_QUERY_ERROR;

    switch (PQresultStatus((PGresult *) result)) {
    case PGRES_TUPLES_OK:
        return DB_QUERY_RECORDS_OK;
    case PGRES_COMMAND_OK:
        return DB_QUERY_COMMAND_OK;
    case PGRES_EMPTY_QUERY:
        return DB_QUERY_EMPTY;
    case PGRES_BAD_RESPONSE:
    case PGRES_NONFATAL_ERROR:
    case PGRES_FATAL_ERROR:
        return DB_QUERY_ERROR;
    default:
        return DB_QUERY_OTHER;
    }
}

static int pg_DBget_record_number(DBConnection * dbconn, void *result)
{
    return PQntuples((PGresult *) result);
}

static int pg_DBget_field_number(DBConnection * dbconn, void *result)
{
    return PQnfields((PGresult *) result);
}

static char *pg_DBget_field_name(DBConnection * dbconn, void *result, int field)
{
    return PQfname((PGresult *) result, field);
}

static char *pg_DBget_field_value(DBConnection * dbconn, void *result,
                               int record, int field)
{
    return PQgetvalue((PGresult *) result, record, field);
}

static void pg_DBclear_result(DBConnection * dbconn, void *result)
{
    PQclear((PGresult *) result);
}

static int pg_add_field_def(DBConnection *dbconn, DBTableDef * tbf)
{
#define DEFQUERY_S  "SELECT a.attnum, a.attname, t.typname, a.attlen,"  \
        "a.atttypmod, a.attnotnull, a.atthasdef\n"  \
        "FROM pg_class c, pg_attribute a, pg_type t\n"  \
        "WHERE c.relname = '%s' AND\n"  \
        "a.attnum > 0 AND\n"    \
        "a.attrelid = c.oid AND\n"  \
        "a.atttypid = t.oid\n"  \
        "ORDER BY attnum"

#define PGDEF_S "SELECT d.adsrc\n"  \
        "FROM pg_attrdef d, pg_class c\n"   \
        "WHERE c.relname = '%s' AND\n"  \
        "c.oid = d.adrelid AND\n"   \
        "d.adnum = %s"

    PGresult *table_info;
    DBConnection_PG *dbconn_pg = (DBConnection_PG *) (dbconn->pvt);
    PGconn *pgconn = dbconn_pg->pgconn;
    ABuf *query = NULL;
    ABuf *type_str = NULL;

    query = buf_new(strlen(DEFQUERY_S) + strlen(tbf->name) + 10);
    snprintf(query->b_dat, query->b_len, DEFQUERY_S, tbf->name);

    table_info = PQexec(pgconn, query->b_dat);
    query = buf_free(query);
    if ((table_info == NULL) ||
        (PQresultStatus(table_info) != PGRES_TUPLES_OK)) {

        if (table_info != NULL)
            PQclear(table_info);

        return -1;
    }
    else {
        int i;

        tbf->field_num = PQntuples(table_info);
        tbf->field_def = (DBFieldDef *)
            malloc(tbf->field_num * sizeof(DBFieldDef));

        for (i = 0; i < tbf->field_num; i++) {
            char *line[3];
            char *rtype, *rnotnull, *rhasdef;
            int attlen, atttypmod;
            char buf[512];
            PGresult *table_info2;

            line[0] = PQgetvalue(table_info, i, 1);
            add_keyword(dbconn, line[0], KEYWORD_FIELD);

            rtype = PQgetvalue(table_info, i, 2);
            attlen = atoi(PQgetvalue(table_info, i, 3));
            atttypmod = atoi(PQgetvalue(table_info, i, 4));
            rnotnull = PQgetvalue(table_info, i, 5);
            rhasdef = PQgetvalue(table_info, i, 6);

            type_str = buf_strcpy(type_str, rtype);
            if (strncmp(rtype, "bpchar", 6) == 0)
                type_str = buf_strcpy(type_str, "char()");
            else if (strncmp(rtype, "varchar", 8) == 0)
                type_str = buf_strcpy(type_str, "varchar()");
            else if (rtype[0] == '_') {
                type_str = buf_strcpy(type_str, rtype + 1);
                type_str = buf_strcat(type_str, "[]");
            }

            if (rnotnull[0] == 't')
                type_str = buf_strcat(type_str, " not null");
            if (rhasdef[0] == 't') {
                sprintf(buf, PGDEF_S, tbf->name, PQgetvalue(table_info, i, 0));
                table_info2 = PQexec(pgconn, buf);
                if ((table_info2 == NULL) ||
                    (PQresultStatus(table_info2) != PGRES_TUPLES_OK)) {
                    if (table_info != NULL)
                        PQclear(table_info);
                    if (table_info2 != NULL)
                        PQclear(table_info2);

                    type_str = buf_free(type_str);
                    return -1;
                }
                type_str = buf_strcat(type_str, " default ");
                type_str = buf_strcat(type_str, PQgetvalue(table_info2, 0, 0));
                PQclear(table_info2);
            }
            line[1] = type_str->b_dat;

            if (strcmp(rtype, "text") == 0)
                sprintf(buf, "var");
            else if (strcmp(rtype, "bpchar") == 0 ||
                     strcmp(rtype, "varchar") == 0) {
                sprintf(buf, "%i",
                        atttypmod != -1 ? atttypmod - VARHDRSZ : 0);
            }
            else {
                if (attlen > 0)
                    sprintf(buf, "%i", attlen);
                else
                    sprintf(buf, "var");
            }
            line[2] = buf;

            tbf->field_def[i].name = strdup(line[0]);
            tbf->field_def[i].type = strdup(line[1]);
            tbf->field_def[i].length = strdup(line[2]);
        }

        if (table_info != NULL)
            PQclear(table_info);
    }
    type_str = buf_free(type_str);
    return 0;
}

static int pg_DBget_table_def(DBConnection * dbconn)
{
#define TBLDEF_S    "SELECT usename, relname, relkind, relhasrules\n"   \
    "FROM pg_class, pg_user\n"  \
    "WHERE relkind = 'r' AND\n" \
    "relname !~ '^pg_' AND\n"   \
    "relname !~ '^xin[vx][0-9]+' AND\n" \
    "usesysid = relowner\n" \
    "ORDER BY relname"

    DBConnection_PG *dbconn_pg = (DBConnection_PG *) (dbconn->pvt);
    if (dbconn_pg == NULL) {
        printf("Internal error. DBConnection_PG not initialised.\n");
        return -1;
    }

    if (dbconn_pg->table_def != NULL) {
        fprintf(stderr, "Internal error (memory leak)...\n");
        return -1;
    }
    else {
        int i;
        PGresult *tables;
        char query[512];

        sprintf(query, TBLDEF_S);
        tables = PQexec(dbconn_pg->pgconn, query);
        if ((tables == NULL) || (PQresultStatus(tables) != PGRES_TUPLES_OK)) {
            printf("Error. tables = %p. PQresultStatus = %d\n", tables, PQresultStatus(tables));
            if (tables != NULL)
                PQclear(tables);            return -1;
        }
        else {
            int tablesInList = PQntuples(tables);

            /* Allocating table array */
            dbconn_pg->table_num = tablesInList;
            dbconn_pg->table_def = (DBTableDef *)
                malloc(tablesInList * sizeof(DBTableDef));

            /* Filling the table */
            for (i = 0; i < tablesInList; i++) {
                char *table_name = PQgetvalue(tables, i, 1);

                /* Name */
                dbconn_pg->table_def[i].name = strdup(table_name);

                /* Fields */
                if (pg_add_field_def(dbconn, dbconn_pg->table_def + i) < 0) {
                    /* TODO: correct leak !!! */

                    dbconn_pg->table_def = NULL;

                    return -1;
                }

                /* Keywords */
                add_keyword(dbconn, table_name, KEYWORD_TABLE);
            }
            if (tables != NULL)
                PQclear(tables);
        }
    }

    return 0;
}

static ABuf *f2ubuf = NULL;

char *pg_DBform_to_url(Form *frm) {
    char *s;

    if (frm == NULL)
        return NULL;

    f2ubuf = buf_check(f2ubuf, 100);
    f2ubuf = buf_strcpy(f2ubuf, frm_get_title(frm));
    f2ubuf = buf_strcat(f2ubuf, "://");
    s = frm_get_value_by_name(frm, BASELOGIN);
    if (s != NULL) {
        f2ubuf = buf_strcat(f2ubuf, s);
        s = frm_get_value_by_name(frm, BASEPASS);
        if (s != NULL) {
            f2ubuf = buf_strcat(f2ubuf, ":");
            f2ubuf = buf_strcat(f2ubuf, s);
        }
        f2ubuf = buf_strcat(f2ubuf, "@");
    }
    s = frm_get_value_by_name(frm, BASEHOST);
    if (s == NULL)
        s = "localhost";

    f2ubuf = buf_strcat(f2ubuf, s);
    f2ubuf = buf_strcat(f2ubuf, "/");
    s = frm_get_value_by_name(frm, BASENAME);
    if (s != NULL)
        f2ubuf = buf_strcat(f2ubuf, s);
    
    return f2ubuf->b_dat;
}

static void pg_DBfree_table_def(DBConnection * dbconn)
{
    DBConnection_PG *conn = (DBConnection_PG *) (dbconn->pvt);

    if (conn->table_def == NULL) {
        /* fprintf(stderr, "Internal error (freeing a NULL
         * connection)...\n"); Dont have to flag an error */
        return;
    }
    else {
        DBFieldDef *dbf;
        int i, j;

        for (j = 0; j < conn->table_num; j++) {
            int fnum = (conn->table_def)[j].field_num;

            free(conn->table_def[j].name);

            dbf = (conn->table_def)[j].field_def;
            for (i = 0; i < fnum; i++) {
                free(dbf[i].name);
                free(dbf[i].type);
            }
            free(dbf);
        }
        free(conn->table_def);
        conn->table_def = NULL;

        /* Removing Keywords */
        keywords_remove(dbconn, KEYWORD_TABLE);
        keywords_remove(dbconn, KEYWORD_FIELD);
    }
}

static int pg_DBget_table_num(DBConnection * dbconn)
{
    DBConnection_PG *dbconn_pg = (DBConnection_PG *) (dbconn->pvt);

    return dbconn_pg->table_num;
}


void pg_DBurl_to_form(Form *frm, char *url) {
    char *u;
    char *p;
    char *start;
    char *end;
    char *username = NULL;
    char *password = NULL;
    char *host = NULL;
    char *port = NULL;
    char *database = NULL;

    /* Clear the login parms    */
    frm_clear_fields(frm);

    if (url == NULL)
        return;

    u = strdup(url);
    start = u;

    /* Look for '://'   */
    p = strstr(start, "://");
    if (p != NULL) {
        p += 3;

        /* auth data specified? */
        start = p;
        p = strchr(start, '@');
        if (p != NULL) {    /* Yes, set auth data.  */
            *p = '\0';
            end = p;
            username = strdup(start);
            p++;
            start = p;
        }

        /* host part    */
        p = strchr(start, '/');
        if (p == NULL) {    /* Rest is all host */
            host = strdup(start);
        }
        else {
            *p = '\0';
            host = strdup(start);
            p++;
            if (strlen(p) > 0)
                database = strdup(p);
        }

        /* Check if password specified  */
        if (username != NULL) {
            p = strchr(username, ':');
            if (p != NULL) {
                *p = '\0';
                p++;
                password = strdup(p);
            }
        }

        /* Check if port in host part   */
        if (host != NULL) {
            p = strchr(host, ':');
            if (p != NULL) {
                *p = '\0';
                p++;
                port = strdup(p);
            }
        }
    }

    /* Now, set everything  */
    if (username != NULL) {
        frm_set_value_by_name(frm, BASELOGIN, username);
        free(username);
    }

    if (password != NULL) {
        frm_set_value_by_name(frm, BASEPASS, password);
        free(password);
    }

    if (host != NULL) {
        frm_set_value_by_name(frm, BASEHOST, host);
        free(host);
    }

    if (port != NULL) {
        frm_set_value_by_name(frm, BASEPORT, port);
        free(port);
    }

    if (database != NULL) {
        frm_set_value_by_name(frm, BASENAME, database);
        free(database);
    }
    free(u);
}

static DBTableDef *pg_DBget_table(DBConnection * dbconn, int table)
{
    DBConnection_PG *dbconn_pg = (DBConnection_PG *) (dbconn->pvt);

    if (table > dbconn_pg->table_num) {
        fprintf(stderr, "Internal error (table number too big)...\n");
        return NULL;
    }

    if (dbconn_pg->table_def == NULL) {
        fprintf(stderr, "Internal error (searching a NULL table)...\n");
        return NULL;
    }
    else {
        return dbconn_pg->table_def + table;
    }
}
DBConnection *pg_dbconnection_new()
{
    DBConnection *dbconn = calloc(1, sizeof(DBConnection));
    if (dbconn) {
        dbconn->pvt = calloc(1, sizeof(DBConnection_PG));
        if (dbconn->pvt)
            dbconn->pvtlen = sizeof(DBConnection_PG);
    }
    return dbconn;
}

static int pg_connect_callback(DBConnector * dbc, DBConnection ** ret) {
#define DB_CONNECT_ERROR_S  "Database connection error : \n %s"
    char *bname, *bhost, *bport, *blogin, *bpass;
    PGconn *pg_conn = NULL;
    ABuf *errbuf = NULL;

    /* Gets the results */
    bname = frm_get_value_by_name(dbc->db_frm, BASENAME);
    bhost = frm_get_value_by_name(dbc->db_frm, BASEHOST);
    if ((bhost != NULL) && (strlen(bhost) == 0))
        bhost = NULL;

    bport = frm_get_value_by_name(dbc->db_frm, BASEPORT);
    if ((bport != NULL) && (strlen(bport) == 0))
        bport = NULL;

    blogin = frm_get_value_by_name(dbc->db_frm, BASELOGIN);

    if ((blogin != NULL) && (strlen(blogin) == 0))
        blogin = NULL;

    bpass = frm_get_value_by_name(dbc->db_frm, BASEPASS);
    if ((bpass != NULL) && (strlen(bpass) == 0))
        bpass = NULL;

    /* Tests the results */
    if ((bname == NULL) || (strlen(bname) == 0)) {
        dbc->app_error_dialog("Connection Error", "You need to provide a database name");
        return CONNECT_ERROR;
    }
    else {
        /* Opens the connection to the database */
        pg_conn =
            PQsetdbLogin(bhost, bport, NULL, NULL, bname, blogin, bpass);
        if (pg_conn == NULL) {
            /* This shouldn't happen according to the documentation.    */
            dbc->app_error_dialog("Connection Error", "PostgreSQL returned a NULL pg_conn\n ");
            return CONNECT_ERROR;
        }
        if (PQstatus(pg_conn) == CONNECTION_BAD) {
            /* Bad connection */
            char *errmsg = PQerrorMessage(pg_conn);
            
            errbuf = buf_strcpy(errbuf, "Database connection error : \n ");
            errbuf = buf_strcat(errbuf, errmsg);
            dbc->app_error_dialog("Connection Error", errbuf->b_dat);
            PQfinish(pg_conn);
            errbuf = buf_free(errbuf);

            return CONNECT_ERROR;
        }
        else {
            DBConnection *dbconn;
            DBConnection_PG *dbconn_pg;
            int i;

            dbconn = pg_dbconnection_new();
            dbconn->connector = dbc;

            dbconn_pg = (DBConnection_PG *)(dbconn->pvt);
            dbconn_pg->pgconn = pg_conn;

            /* The functions (public) */
            dbconn->DBdisconnect = pg_DBdisconnect;
            dbconn->DBget_error_message = pg_DBget_error_message;
            dbconn->DBget_db_name = pg_DBget_db_name;
            dbconn->DBexecute_query = pg_DBexecute_query;
            dbconn->DBget_query_status = pg_DBget_query_status;
            dbconn->DBget_record_number = pg_DBget_record_number;
            dbconn->DBget_field_number = pg_DBget_field_number;
            dbconn->DBget_field_name = pg_DBget_field_name;
            dbconn->DBget_field_value = pg_DBget_field_value;
            dbconn->DBclear_result = pg_DBclear_result;

// C=M^e(mod N)

            /* The keywords (public) */
            dbconn->keylist = NULL;
            i = 0;
            while (psqlSqlKeywords[i] != NULL)
                add_keyword(dbconn, psqlSqlKeywords[i++], KEYWORD_SQL);

            /* The tables functions (public) */
            dbconn->DBget_table_def = pg_DBget_table_def;
            dbconn->DBfree_table_def = pg_DBfree_table_def;
            dbconn->DBget_table_num = pg_DBget_table_num;
            dbconn->DBget_table = pg_DBget_table;

            *ret = dbconn;
        }
        errbuf = buf_free(errbuf);
    }
    return CONNECT_OK;
}

#define PSQL_TYPESTR    "PostgreSQL";

DBConnector *register_PostgreSQL(void (*error_dialog) (char *, char *))
{
    DBConnector *ret;
    FormField *fld;

    ret = dbconnector_new();
    if (ret == NULL)
        return NULL;

    ret->dbtype = PSQL_TYPESTR;

    /* Prepares all the structures */
    ret->app_error_dialog = error_dialog;
    ret->db_connect_callback = pg_connect_callback;
    ret->url_to_form = pg_DBurl_to_form;
    ret->form_to_url = pg_DBform_to_url;

    /* Creates the data-structure */
    ret->db_frm = frm_new(NULL);
    frm_set_title(ret->db_frm, "PostgreSQL");

    fld = frm_add_field(ret->db_frm, BASENAME, NULL);
    fld_set_tip(fld, "Database name");

    fld = frm_add_field(ret->db_frm, BASEHOST, NULL);
    fld_set_tip(fld, "Host database is on (optional)");

    fld = frm_add_field(ret->db_frm, BASEPORT, NULL);
    fld_set_tip(fld, "Port database is listening on (optional)");

    fld = frm_add_field(ret->db_frm, BASELOGIN, NULL);
    fld_set_tip(fld, "Username for connection (optional)");

    fld = frm_add_field(ret->db_frm, BASEPASS, NULL);
    fld_set_type(fld, FT_PWD);
    fld_set_tip(fld, "Password for connection (optional)");

    return ret;
}

#endif

---