---

d_mysql_f.c

Go to the documentation of this file.

/* GtkSQL -- an interactive graphical query tool for PostgreSQL and MySQL.
 * Copyright (C) 1998-2003 Lionel ULMER, Darryl Luff
 * MySQL "driver" (C) 1998 Des Herriott (des@ops.netcom.net.uk)
 *
 * 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_MYSQL
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <mysql.h>

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

/* The text tags to use as the form field names.    */

#define BASENAME    "Database"

#define BASEHOST    "Host"
#define BASEPORT    "Port"
#define BASELOGIN   "Username"

#define BASEPASS    "Password"

typedef struct {
    MYSQL *conn;            
    char *dbname;           
    DBTableDef *table_def;  
    int table_num;          
} DBConnection_MYSQL;


MYSQL_RES dummyRes;

static char *sqlKeywords[] = {
    "ABS", "AND", "ALTER", "ASC", "ASCII",
    "BEGIN", "BETWEEN", "BIN", "BINARY", "BIT_LENGTH",
    "CASE", "CEILING", "CHAR", "CHAR_LENGTH", "CHARACTER_LENGTH",
    "COALESCE", "COMMIT", "CONCAT", "CONCAT_WS", "CONV", "COUNT",
    "CREATE",
    "DATABASE", "DELETE", "DESC", "DESCRIBE", "DISTINCT", "DISTINCTROW",
    "DROP",
    "ELT", "END", "EXP", "EXPLAIN", "EXPORT_SET",
    "FIELD", "FIND_IN_SET", "FLOOR", "FROM",
    "GRANT", "GROUP BY",
    "HAVING", "HEX",
    "IFNULL", "INDEX", "INNER", "INSERT", "INSTR", "INTERVAL", "INTO",
    "ISNULL",
    "JOIN",
    "LCASE", "LEFT", "LENGTH", "LIKE", "LOAD DATA", "LOAD_FILE", "LOCATE",
    "LOCK", "LOG", "LOG2", "LOG10", "LOWER", "LPAD", "LTRIM",
    "MAKE_SET", "MATCH", "MAX", "MID", "MIN", "MEAN", "MOD",
    "NATURAL", "NOT", "NULL", "NULLIF",
    "OCT", "OCTET_LENGTH", "OR", "ORD", "ORDER BY", "OUTER",
    "POSITION", "POW", "POWER",
    "QUOTE",
    "REGEXP", "REPEAT", "REPLACE", "RESET", "REVERSE", "REVOKE", "RIGHT",
    "RLIKE", "ROLLBACK", "ROUND", "RPAD", "RTRIM",
    "SELECT", "SET", "SHOW", "SIGN", "SOUNDEX", "SPACE", "SQRT", "STRCMP",
    "SUBSTRING", "SUBSTRING_INDEX", "SUM",
    "TABLE", "THEN", "TRANSACTION",
    "UCASE", "UNION", "UPDATE", "UPPER",
    "WHEN", "WHERE", "WORK",
    NULL
};

void mysql_dbconnection_free(DBConnection * dbconn)
{
    DBConnection_MYSQL *dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);
    free(dbmysql->dbname);

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

    /* Free structure memory */
    free(dbmysql);
    free(dbconn);
}

static int my_DBdisconnect(DBConnection * dbconn)
{
    DBConnection_MYSQL *dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);
    MYSQL *co = dbmysql->conn;

    /* End connection */
    mysql_close(co);
    mysql_dbconnection_free(dbconn);
    return 0;
}

static char *my_DBget_error_message(DBConnection * dbconn)
{
    DBConnection_MYSQL *dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);
    return mysql_error(dbmysql->conn);
}

static char *my_DBget_db_name(DBConnection * dbconn)
{
    DBConnection_MYSQL *dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);
    return dbmysql->dbname;
}

static void *my_DBexecute_query(DBConnection * dbconn, char *query)
{
    int fields;
    DBConnection_MYSQL *dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);
    MYSQL_RES *res;

    if (mysql_query(dbmysql->conn, query))
        return NULL;

    res = mysql_store_result(dbmysql->conn);

    if (res == NULL) {
#ifdef HAVE_MYSQL_FIELD_COUNT
        /* This is for versions 3.22.24 and after */
        fields = mysql_field_count(dbmysql->conn);
#else
        fields = mysql_num_fields(dbmysql->conn);
#endif
        if (fields == 0)
            res = &dummyRes;
    }
    return res;
}

static ABuf *f2ubuf = NULL;

char *my_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 int my_DBget_query_status(DBConnection * dbconn, void *result)
{
    DBConnection_MYSQL *dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);
    MYSQL_RES *res = result;
    char *err;

    err = mysql_error(dbmysql->conn);
    if (err[0]) {
        return DB_QUERY_ERROR;
    }
    else if (res == &dummyRes) {
        return DB_QUERY_COMMAND_OK;
    }
    else if (mysql_num_rows(res) == 0) {
        return DB_QUERY_EMPTY;
    }
    else {
        return DB_QUERY_RECORDS_OK;
    }
}

static int my_DBget_record_number(DBConnection * dbconn, void *result)
{
    MYSQL_RES *res = result;

    return mysql_num_rows(res);
}

static int my_DBget_field_number(DBConnection * dbconn, void *result)
{
    MYSQL_RES *res = result;

    return mysql_num_fields(res);
}

static char *my_DBget_field_name(DBConnection * dbconn, void *result, int field)
{
    MYSQL_RES *res = result;
    MYSQL_FIELD *f;

    mysql_field_seek(res, field);
    if ((f = mysql_fetch_field(res)) == NULL)
        return NULL;
    return f->name;
}

static char *my_DBget_field_value(DBConnection * dbconn, void *result,
                               int record, int field)
{
    MYSQL_RES *res = result;
    MYSQL_ROW row;

    mysql_data_seek(res, record);
    if ((row = mysql_fetch_row(res)) == NULL) {
        return NULL;
    }
    return (char *) row[field];
}

static void my_DBclear_result(DBConnection * dbconn, void *result)
{
    MYSQL_RES *res = result;
    if ((res) && (res != &dummyRes))
        mysql_free_result(res);
}

static int my_add_field_def(DBConnection * dbconn, DBTableDef * tbf)
{
#define DESCRIBE_S  "describe %s"

    DBConnection_MYSQL *dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);
    MYSQL *co = dbmysql->conn;
    MYSQL_RES *table_info;
    ABuf *query = NULL;
    ABuf *type_str = NULL;
    int i;
    char *fname;
    char *rtype, *rnotnull, *rhasdef;

    query = buf_check(query, strlen(DESCRIBE_S) + strlen(tbf->name) + 1);
    if (!query) {
        fprintf(stderr, "add_field_def() cannot allocate memory for query string\n");
        return -1;
    }

    snprintf(query->b_dat, query->b_len, DESCRIBE_S, tbf->name);

    if (mysql_query(co, query->b_dat))
        return -1;

    if ((table_info = mysql_store_result(co)) == NULL)
        return -1;

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

    for (i = 0; i < tbf->field_num; i++) {
        MYSQL_ROW row;

        mysql_data_seek(table_info, i);
        row = mysql_fetch_row(table_info);

        fname = row[0];
        add_keyword(dbconn, fname, KEYWORD_FIELD);

        rtype = row[1];
        rnotnull = row[2];
        rhasdef = row[4];

        /* allocate type_str */
        type_str = buf_new(256);

        if (rhasdef)
            type_str = buf_check(type_str, strlen(rhasdef));

        type_str = buf_strcpy(type_str, rtype);

        if (strncmp(rnotnull, "YES", 3) == 0)
            type_str = buf_strcat(type_str, " not null");

        if (rhasdef) {
            type_str = buf_strcat(type_str, " default");
        }

        tbf->field_def[i].name = strdup(fname);
        tbf->field_def[i].type = strdup(type_str->b_dat);
        tbf->field_def[i].length = strdup("??");
    }

    buf_free(query);
    buf_free(type_str);

    return 0;
}

static int my_DBget_table_def(DBConnection * dbconn)
{
    DBConnection_MYSQL *dbmysql;
    char query[] = "show tables";
    MYSQL_RES *res;

    if (dbconn == NULL) {
        fprintf(stderr, "WARNING: Could not get table definitions.\n");
        return -1;
    }

    dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);
    if (dbmysql->table_def != NULL) {
        fprintf(stderr, "Internal error (memory leak in my_DBget_table_def)...\n");
        return -1;
    }

    if (mysql_query(dbmysql->conn, query)) {
        return -1;
    }
    if ((res = mysql_store_result(dbmysql->conn)) == NULL) {
        return -1;
    }
    else {
        int i;
        int tables_in_list = mysql_num_rows(res);

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

        /* Fill the table */
        for (i = 0; i < tables_in_list; i++) {
            char *table_name;
            MYSQL_ROW row;

            mysql_data_seek(res, i);
            row = mysql_fetch_row(res);
            table_name = row[0];

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

            /* Table fields */
            if (my_add_field_def(dbconn, dbmysql->table_def + i) < 0) {
                dbmysql->table_def = NULL;
                return -1;
            }

            /* Keywords */
            add_keyword(dbconn, table_name, KEYWORD_TABLE);
        }

        mysql_free_result(res);
    }
    return 0;
}

static void my_DBfree_table_def(DBConnection * dbconn)
{
    DBConnection_MYSQL *dbmysql = (DBConnection_MYSQL *) (dbconn->pvt);

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

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

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

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

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

static int my_DBget_table_num(DBConnection * dbconn)
{
    DBConnection_MYSQL *conn = (DBConnection_MYSQL *) (dbconn->pvt);

    return conn->table_num;
}

void my_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);
    }

#ifdef HAVE_MYSQL_REAL_CONNECT
    if (port != NULL) {
        frm_set_value_by_name(frm, BASEPORT, port);
        free(port);
    }
#endif

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

static DBTableDef *my_DBget_table(DBConnection * dbconn, int table)
{
    DBConnection_MYSQL *conn = (DBConnection_MYSQL *) (dbconn->pvt);

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

    if (conn->table_def == NULL) {
        fprintf(stderr, "Internal error (searching a NULL table)...\n");
        return NULL;
    }
    else {
        return conn->table_def + table;
    }
}

DBConnection *mysql_dbconnection_new()
{
    DBConnection *dbc = calloc(1, sizeof(DBConnection));
    if (dbc) {
        dbc->pvt = calloc(1, sizeof(DBConnection_MYSQL));
        if (dbc->pvt)
            dbc->pvtlen = sizeof(DBConnection_MYSQL);
    }
    return dbc;
}

static int my_connect_callback(DBConnector * dbc, DBConnection ** ret)
{
#define DB_CONNECT_ERROR_S  "Database connection error : \n %s"
    char *bname, *bhost, *blogin, *bpass;
#ifdef HAVE_MYSQL_REAL_CONNECT
    char *bport;
    unsigned int portNo = 0;
#endif
    ABuf *buf = NULL;
    char *db_err = NULL;
    MYSQL *my_conn = NULL;
    DBConnection *ret_my = NULL;
    DBConnection_MYSQL *ret_my_mysql = NULL;
    int i;

    *ret = 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;

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

    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;

    if ((my_conn = calloc(1, sizeof(MYSQL))) == NULL) {
        return CONNECT_ERROR;
    }

/* NOTE: This symbol is defined in the mysql headers, so no need for a configure check. */
#ifdef HAVE_MYSQL_REAL_CONNECT
    /* Thanks to Eike Lange */
    my_conn = mysql_init (my_conn);
    if (bport != NULL)
        portNo = atoi(bport);

    if (!mysql_real_connect(my_conn, bhost, blogin, bpass, NULL, portNo, NULL, 0)) {
#else
    if (!mysql_connect(my_conn, bhost, blogin, bpass)) {
#endif
        db_err = mysql_error(my_conn);
        buf = buf_new(strlen(DB_CONNECT_ERROR_S) + strlen(db_err) + 1);
        if (!buf) {
            fprintf(stderr, "connect_callback() couldnt allocate memory for buffer\n");
            return CONNECT_ERROR;
        }

        snprintf(buf->b_dat, buf->b_len, DB_CONNECT_ERROR_S, db_err);

        dbc->app_error_dialog("Connection Error", buf->b_dat);
        buf = buf_free(buf);
        return CONNECT_ERROR;
    }

    if (mysql_select_db(my_conn, bname)) {
        db_err = mysql_error(my_conn);
        buf =
            buf_check(buf,
                      strlen(DB_CONNECT_ERROR_S) + strlen(db_err) + 1);
        if (!buf) {
            fprintf(stderr, "connect_callback() couldnt allocate memory for buffer\n");
            return CONNECT_ERROR;
        }

        snprintf(buf->b_dat, buf->b_len, DB_CONNECT_ERROR_S, db_err);
        dbc->app_error_dialog("Connection Error", buf->b_dat);
        buf = buf_free(buf);
        return CONNECT_ERROR;
    }

    ret_my = mysql_dbconnection_new();
    if (!ret_my)
        return CONNECT_ERROR;

    ret_my->connector = dbc;

    ret_my_mysql = (DBConnection_MYSQL *) (ret_my->pvt);

    /* The connection (private) */
    ret_my_mysql->conn = my_conn;

    /* The database name (private) */
    ret_my_mysql->dbname = strdup(bname);

    /* The functions (public) */
    ret_my->DBclear_result = my_DBclear_result;
    ret_my->DBdisconnect = my_DBdisconnect;
    ret_my->DBexecute_query = my_DBexecute_query;
    ret_my->DBfree_table_def = my_DBfree_table_def;
    ret_my->DBget_db_name = my_DBget_db_name;
    ret_my->DBget_error_message = my_DBget_error_message;
    ret_my->DBget_field_name = my_DBget_field_name;
    ret_my->DBget_field_number = my_DBget_field_number;
    ret_my->DBget_field_value = my_DBget_field_value;
    ret_my->DBget_query_status = my_DBget_query_status;
    ret_my->DBget_record_number = my_DBget_record_number;
    ret_my->DBget_table = my_DBget_table;
    ret_my->DBget_table_def = my_DBget_table_def;
    ret_my->DBget_table_num = my_DBget_table_num;

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

    *ret = ret_my;
    buf = buf_free(buf);
    return CONNECT_OK;
}

#define MYSQL_TYPESTR   "MySQL"

DBConnector *register_MySQL(void (*error_dialog) (char *, char *))
{
    DBConnector *dbconn;
    FormField *fld;

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

    dbconn->dbtype = MYSQL_TYPESTR;

    /* Prepares all the structures */
    dbconn->app_error_dialog = error_dialog;
    dbconn->db_connect_callback = my_connect_callback;
    dbconn->url_to_form = my_DBurl_to_form;
    dbconn->form_to_url = my_DBform_to_url;

    /* Creates the data-structure */
    dbconn->db_frm = frm_new(NULL);
    frm_set_title(dbconn->db_frm, "MySQL");
    fld = frm_add_field(dbconn->db_frm, BASENAME, NULL);
    fld_set_tip(fld, "Database name (required)");
    fld_set_relevance(fld, FR_REQUIRED);

    fld = frm_add_field(dbconn->db_frm, BASEHOST, NULL);
    fld_set_tip(fld, "Host running database (optional)");

#ifdef HAVE_MYSQL_REAL_CONNECT
    fld = frm_add_field(dbconn->db_frm, BASEPORT, NULL);
    fld_set_tip(fld, "Port database listens on (optional)");
#endif

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

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

    return dbconn;
}

#endif

---