extern_mysql.c

#include <stddef.h>
#include <time.h>

#include <pthread.h>


#include "backup_app.h"


/* configuration, see config.c */
char   *DBHOST;
char   *DBUSER;
char   *DBPASSWD;
char   *DBDB;
int     DBPORT;
char   *DBUNIXSOCKET;


typedef struct {
    char       *name;               /* name of the filed in the returned result set */
    size_t      offset;             /* offset in buffer where value will be stored */
    size_t      len;                /* len in buffer for this value */
    unsigned    one_on_null:1;      /* set this value to -1 on NULL */
} mb_bind_t;


typedef struct {
    char       *text;           /* sql statement */
    MYSQL_STMT *mysql_stmt;     /* prepared statement */
    void       *temp_data;      /* buffer to store one row of response */
    size_t      bind_len;       /* the length of a buffer for one row */
    mb_bind_t  *bind_r;         /* bindings */
} mb_mysql_stmt_t;


mb_bind_t   bind_r_schedule[] = {
    {"schedule_id",     offsetof(mb_db_schedule_t, schedule_id),        sizeof(((mb_db_schedule_t*)0)->schedule_id),        0},
    {"name",            offsetof(mb_db_schedule_t, name),               sizeof(((mb_db_schedule_t*)0)->name),               0},
    {"schedule_type",   offsetof(mb_db_schedule_t, schedule_type),      sizeof(((mb_db_schedule_t*)0)->schedule_type),      0},
    {"enabled",         offsetof(mb_db_schedule_t, enabled),            sizeof(((mb_db_schedule_t*)0)->enabled),            0},
    
    {"starthour",       offsetof(mb_db_schedule_t, starthour),          sizeof(((mb_db_schedule_t*)0)->starthour),          0},
    {"startminute",     offsetof(mb_db_schedule_t, startminute),        sizeof(((mb_db_schedule_t*)0)->startminute),        0},
    {"endhour",         offsetof(mb_db_schedule_t, endhour),            sizeof(((mb_db_schedule_t*)0)->endhour),            0},
    {"endminute",       offsetof(mb_db_schedule_t, endminute),          sizeof(((mb_db_schedule_t*)0)->endminute),          0},
    {"everyhours",      offsetof(mb_db_schedule_t, everyhours),         sizeof(((mb_db_schedule_t*)0)->everyhours),         0},
    {"day",             offsetof(mb_db_schedule_t, day),                sizeof(((mb_db_schedule_t*)0)->day),                0},

    {"sunday",          offsetof(mb_db_schedule_t, sunday),             sizeof(((mb_db_schedule_t*)0)->sunday),             0},
    {"monday",          offsetof(mb_db_schedule_t, monday),             sizeof(((mb_db_schedule_t*)0)->monday),             0},
    {"tuesday",         offsetof(mb_db_schedule_t, tuesday),            sizeof(((mb_db_schedule_t*)0)->tuesday),            0},
    {"wednesday",       offsetof(mb_db_schedule_t, wednesday),          sizeof(((mb_db_schedule_t*)0)->wednesday),          0},
    {"thursday",        offsetof(mb_db_schedule_t, thursday),           sizeof(((mb_db_schedule_t*)0)->thursday),           0},
    {"friday",          offsetof(mb_db_schedule_t, friday),             sizeof(((mb_db_schedule_t*)0)->friday),             0},
    {"saturday",        offsetof(mb_db_schedule_t, saturday),           sizeof(((mb_db_schedule_t*)0)->saturday),           0},
    
    {"status",          offsetof(mb_db_schedule_t, status),             sizeof(((mb_db_schedule_t*)0)->status),             1},
    {"retentionmethod", offsetof(mb_db_schedule_t, retentionmethod),    sizeof(((mb_db_schedule_t*)0)->retentionmethod),    1},
    {"retentionlength", offsetof(mb_db_schedule_t, retentionlength),    sizeof(((mb_db_schedule_t*)0)->retentionlength),    0},

    {"username",        offsetof(mb_db_schedule_t, username),           sizeof(((mb_db_schedule_t*)0)->username),           0},
    {"machine_username",offsetof(mb_db_schedule_t, machine_username),   sizeof(((mb_db_schedule_t*)0)->machine_username),   0},
    {"machine_address", offsetof(mb_db_schedule_t, machine_address),    sizeof(((mb_db_schedule_t*)0)->machine_address),    0},
    {"machine_os_type", offsetof(mb_db_schedule_t, machine_os_type),    sizeof(((mb_db_schedule_t*)0)->machine_os_type),    1},
    {NULL, 0, 0, 0}
};
...


mb_mysql_stmt_t mb_mysql_stmt[] = {
    {
        "select s.*, u.username, m.username machine_username, m.address machine_address, "
        "m.os_type machine_os_type "
        "from SCHEDULE s left outer join USER u on s.user_id = u.user_id "
        "left outer join MACHINE_SCHEDULE ms on s.schedule_id = ms.schedule_id "
        "left outer join MACHINE m on ms.machine_id = m.machine_id "
        "order by schedule_id",
        NULL, NULL, sizeof(mb_db_schedule_t), bind_r_schedule
    },
    ...
    {NULL, NULL, NULL, 0, NULL}
};


static MYSQL      *mysql;


static int mb_mysql_create_stmt(MYSQL*, int);


int mb_db_init() {

    if(!(mysql = mysql_init(NULL))) {
        mb_log_err("Failed to initate MySQL connection");
        goto error;
    }

    my_bool reconnect = 1, report_trunk = 0;
    mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect);
    mysql_options(mysql, MYSQL_REPORT_DATA_TRUNCATION, &report_trunk);


    if (!mysql_real_connect(mysql, DBHOST, DBUSER, DBPASSWD, DBDB, DBPORT, DBUNIXSOCKET, 0)) {
        mb_log_err("Failed to connect to MySQL: %s", mysql_error(mysql));
        goto error;
    }

    /* Note: for each new thread */
    mysql_thread_init();

    mysql_autocommit(mysql, 1);
        
    /* create statements */
    if (mb_mysql_create_stmt(mysql, 1))
        goto error;

    
    return 0;

error:
    return 1;
}


int mb_db_del() {
    {
        /* free statements */
        mb_mysql_stmt_t    *s;
        
        for (s = mb_mysql_stmt; s->text; s++) {
            mysql_stmt_close(s->mysql_stmt);
            free(s->temp_data);
        }
    }
    
    mysql_thread_end();
    mysql_close(mysql);
    
    return 0;
}


static int mb_mysql_create_stmt(MYSQL *mysql, int create_bufs) {
    mb_mysql_stmt_t    *s;
    
    for (s = mb_mysql_stmt; s->text; s++) {
        MYSQL_STMT *mysql_stmt;

        if (!(mysql_stmt = mysql_stmt_init(mysql))) {
            mb_log_err("mysql_stmt_init(), out of memory");
            goto error;
        }
        if (mysql_stmt_prepare(mysql_stmt, s->text, strlen(s->text))) {
            mb_log_err("%s", mysql_stmt_error(mysql_stmt));
            goto error;
        }
        
        s->mysql_stmt = mysql_stmt;
        
        /* create data buffer */
        if (create_bufs) {
            s->temp_data = malloc(s->bind_len);
            if (!s->temp_data) {
                mb_log_syserr("out of memory");
                goto error;
            }
        }
    }

    
    return 0;
    
error:

    if (create_bufs) {
        for (s = mb_mysql_stmt; s->text; s++)
            if (s->temp_data) {
                free(s->temp_data);
                s->temp_data = NULL;
            }
    }

    return 1;
}


void *mb_db_exec(int what, size_t *count) {
    MYSQL_STMT         *stmt;
    MYSQL_RES          *prepare_meta_result;
    mb_mysql_stmt_t    *mb_stmt;
    
    
    MYSQL_BIND     *bind_r;
    my_bool        *bind_nulls;
    char           *default_vals;
    char            trash[128];
    char           *data;
    
    
    bind_r       = NULL;
    bind_nulls   = NULL;
    default_vals = NULL;
    data         = NULL;
    mb_stmt      = &mb_mysql_stmt[what];
    stmt         = mb_stmt->mysql_stmt;
        
    
    {
        /* db reconnect */
        unsigned long mysql_tid;
        
        mysql_tid = mysql_thread_id(mysql);
        if (mysql_ping(mysql)) {
            mb_log_err("MySQL ping error: %s", mysql_error(mysql));
            goto error;
        }
        if (mysql_tid != mysql_thread_id(mysql)) {
            /* A reconnect occured, stmt were released */
            if (mb_mysql_create_stmt(mysql, 0))
                goto error;
        }
    }
    
    
    prepare_meta_result = mysql_stmt_result_metadata(stmt);
    if (!prepare_meta_result) {
        mb_log_err("%s", mysql_stmt_error(stmt));
        goto error;
    }
    
    if (mysql_stmt_execute(stmt)) {
        mb_log_err("%s", mysql_stmt_error(stmt));
        goto error;
    }
    
    
    {
        /* construct MYSQL_BIND array for results */
        int             num_fields;
        MYSQL_BIND     *mysql_bind;
        MYSQL_FIELD    *field;
        my_bool        *is_null;
        
        num_fields = mysql_num_fields(prepare_meta_result);
        
        bind_r = malloc(sizeof(MYSQL_BIND) * num_fields);
        if (!bind_r) {
            mb_log_syserr("out of memory");
            goto error;
        }
        memset(bind_r, 0, sizeof(MYSQL_BIND) * num_fields);
        bind_nulls = malloc(sizeof(my_bool) * num_fields);
        if (!bind_nulls) {
            mb_log_syserr("out of memory");
            goto error;
        }
        default_vals = malloc(mb_stmt->bind_len);
        if (!default_vals) {
            mb_log_syserr("out of memory");
            goto error;
        }
        memset(default_vals, 0, mb_stmt->bind_len);
        
        for (mysql_bind = bind_r, is_null = bind_nulls; (field = mysql_fetch_field(prepare_meta_result)); mysql_bind++, is_null++) {
            mb_bind_t  *user_bind;
            
            for (user_bind = mb_stmt->bind_r; user_bind->name; user_bind++) {
                if (!strcasecmp(field->name, user_bind->name))
                    break;
            }
            
            if (user_bind->name) {
                mysql_bind->buffer_type   = field->type;
                mysql_bind->buffer        = (char*)mb_stmt->temp_data + user_bind->offset;
                mysql_bind->buffer_length = user_bind->len;
                mysql_bind->is_null       = is_null;
                mysql_bind->length        = 0;
                
                if (user_bind->one_on_null) {
                    uint8_t *p;
                    for (p = (uint8_t*)default_vals + user_bind->offset; p < (uint8_t*)default_vals + user_bind->offset + user_bind->len; p++)
                        *p = 0xFF;
                }
            }
            else {
                mysql_bind->buffer_type   = field->type;
                mysql_bind->buffer        = trash;
                mysql_bind->buffer_length = sizeof(trash);
                mysql_bind->is_null       = is_null;
                mysql_bind->length        = 0;
            }
        }
        
    }
    
                
    if (mysql_stmt_bind_result(stmt, bind_r)) {
        mb_log_err("%s", mysql_stmt_error(stmt));
        goto error;
    }
    
    /* buffer all results to client (to use mysql_stmt_num_rows()) */
    if (mysql_stmt_store_result(stmt)) {
        mb_log_err("%s", mysql_stmt_error(stmt));
        goto error;
    }
    
    
    int     num_rows;
    {
        /* allocate data buffer */
        
        num_rows = mysql_stmt_num_rows(stmt);
        
        if (num_rows) {
            data = malloc(mb_stmt->bind_len * num_rows);
            if (!data) {
                mb_log_syserr("out of memory");
                goto error;
            }
        }
        else
            data    = NULL;
    }
    
    
    {
        char   *p;
        int    res = 0;
        memcpy(mb_stmt->temp_data, default_vals, mb_stmt->bind_len);
        for (p = data; !res; p += mb_stmt->bind_len) {
            res = mysql_stmt_fetch(stmt);
            if (res) {
                if (1 == res)
                    mb_log_err("%s", mysql_stmt_error(stmt));
                break;
            }
            memcpy(p, mb_stmt->temp_data, mb_stmt->bind_len);
            memcpy(mb_stmt->temp_data, default_vals, mb_stmt->bind_len);
        }
    }
        
    
    mysql_free_result(prepare_meta_result);
    free(bind_r);
    free(bind_nulls);
    free(default_vals);

    if (count)
        *count = num_rows;
    
    return data;

    
error:

    if (prepare_meta_result)
        mysql_free_result(prepare_meta_result);
    if (bind_r)
        free(bind_r);
    if (bind_nulls)
        free(bind_nulls);
    if (default_vals)
        free(default_vals);
    if (data)
        free(data);

    if (count)
        *count  = -1;

    return NULL;
}