quote-bot/db.c

/* <https://gist.github.com/enile8/2424514> */
#define _POSIX_C_SOURCE 200809L /* strtok_r, strndup */
#include <string.h>
#include <assert.h>             /* assert */
#include <sqlite3.h>
#include <stdio.h>              /* size_t */
#include <stdlib.h>             /* free */
#include "io.h"
#include "strings.h"
#include "unused.h"

static int print_col(sqlite3_stmt * pTableInfo, int col) {
    int n, rc;
    static int ct = 0;
    char outfile[50];
    size_t size;
    printf("%s: ", sqlite3_column_name(pTableInfo, col));
    switch (sqlite3_column_type(pTableInfo, col)) {
    case SQLITE_INTEGER:
        printf("%d ", sqlite3_column_int(pTableInfo, col));
        break;
    case SQLITE_FLOAT:
        printf("%f ", sqlite3_column_double(pTableInfo, col));
        break;
    case SQLITE_TEXT:
        printf("%s ", sqlite3_column_text(pTableInfo, col));
        break;
    case SQLITE_BLOB:          //printf("%s",sqlite3_column_blob(pTableInfo, col));
        /* fprintf(stderr, "IN BLOB bytes %d\n",
           sqlite3_column_bytes(pTableInfo, col)); */
        snprintf(outfile, 20, "outdata.%d.png", ct++);
        n = sqlite3_column_bytes(pTableInfo, col);
        if (n > 0)
            size = (size_t)n;
        else
            break;
        if ((rc = (int)file_write(outfile, sqlite3_column_blob(pTableInfo, col), size))) {
            assert(rc == (int)size);
        }
        break;
    case SQLITE_NULL:
        printf("(null)\n");
        break;
    default:
        printf(" *Cannot determine SQLITE TYPE* col=%d\n", col);
    }
    return 0;
}
static int run_script_callback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    UNUSED(NotUsed);
    for (i = 0; i < argc; i++) {
        printf(" => %s = %s\n", azColName[i], (argv[i] ? argv[i] : "NULL"));
    }
    return 0;
}
int run_script(sqlite3 * db, const char *script_filename) {
    int j, rc;
    char *statements, *zErrMsg;
    char *str1, *saveptr1, *token;
    zErrMsg = 0;
    if (!(statements = file_read(script_filename, NULL))) {
        fprintf(stderr, string_errmsg_readfile, script_filename);
        return -1;
    }
#if 0
    rc = sqlite3_exec(db, statements, run_script_callback, 0, &zErrMsg);
    if (SQLITE_OK != rc) {
        fprintf(stderr, " !! SQL error: %s\n", sqlite3_errmsg(db));
        sqlite3_free(zErrMsg);
    }
#else
    for (j = 1, str1 = statements;; j++, str1 = NULL) {
        token = strtok_r(str1, "\n", &saveptr1);
        if (token == NULL)
            break;
        if (strlen(token) > 2 && token[0] == '-' && token[1] == '-')
            continue;           /* Comment line; TODO multiline comment */
        printf("%d: %s\n", j, token);
        rc = sqlite3_exec(db, token, run_script_callback, 0, &zErrMsg);
        if (SQLITE_OK != rc) {
            fprintf(stderr, " !! SQL error: %s\n", sqlite3_errmsg(db));
            sqlite3_free(zErrMsg);
            break;
        }
    }
#endif
    free(statements);
    fprintf(stderr, "Bye!\n");
    return SQLITE_OK;
}
int run_one(sqlite3 * db, const char *query) {
    int i, n, rc;
    sqlite3_stmt *stmt = NULL;
    printf("> %s\n", query);
    if (SQLITE_OK != (rc = sqlite3_prepare_v2(db, query, -1, &stmt, 0))) {
        fprintf(stderr, string_errmsg_prepare, sqlite3_errmsg(db));
        return SQLITE_ERROR;
    }
    while (SQLITE_ROW == (rc = sqlite3_step(stmt))) {
        n = sqlite3_column_count(stmt);
        for (i = 0; i < n; ++i) {
            printf(" => ");
            print_col(stmt, i);
            puts("");
        }
    }
    if (SQLITE_DONE != rc) {
        fprintf(stderr, string_errmsg_update, sqlite3_errmsg(db));
    }
    if (SQLITE_OK != (rc = sqlite3_reset(stmt))) {
        fprintf(stderr, string_errmsg_reset, sqlite3_errmsg(db));
    }
    if (SQLITE_OK != (rc = sqlite3_clear_bindings(stmt))) {
        fprintf(stderr, string_errmsg_clear, sqlite3_errmsg(db));
    }
    return sqlite3_finalize(stmt);
}
int update_one(sqlite3 * db, int rowid, const char *param1, const char *param2) {
    const char insert_stmt[] = "update quotedb set ? = ? where quote_id = ?";
    int n, rc;
    size_t maxlen;
    sqlite3_stmt *stmt = NULL;
    maxlen = 4096;
    n = 0;
    if (SQLITE_OK != (rc = sqlite3_prepare_v2(db, insert_stmt, -1, &stmt, 0))) {
        fprintf(stderr, string_errmsg_prepare, sqlite3_errmsg(db));
        return SQLITE_ERROR;
    }
#define S(x) if (SQLITE_OK != (rc = sqlite3_bind_text(stmt, ++n, x, (int)strnlen(x, maxlen), SQLITE_STATIC))) { \
    fprintf(stderr, string_errmsg_bind, sqlite3_errmsg(db)); \
    return SQLITE_ERROR; \
}
    S(param1);
    S(param2);
#undef S
    sqlite3_bind_int(stmt, 3, rowid);
    if (SQLITE_DONE != (rc = sqlite3_step(stmt))) {
        fprintf(stderr, string_errmsg_update, sqlite3_errmsg(db));
    }
    if (SQLITE_OK != (rc = sqlite3_reset(stmt))) {
        fprintf(stderr, string_errmsg_reset, sqlite3_errmsg(db));
    }
    return sqlite3_finalize(stmt);
}
int insert_row(sqlite3 * db, const char *channel, const char *added_by, const char *subject, const char *words) {
    const char insert_stmt[] = "insert into quotedb (date_added, channel, added_by, subject, words) values (datetime('now'), ?, ?, ?, ?)";
    int n, rc;
    size_t maxlen;
    sqlite3_stmt *stmt = NULL;
    maxlen = 4096;
    n = 0;
    if (SQLITE_OK != (rc = sqlite3_prepare_v2(db, insert_stmt, -1, &stmt, 0))) {
        fprintf(stderr, string_errmsg_prepare, sqlite3_errmsg(db));
        return SQLITE_ERROR;
    }
#define S(x) if (SQLITE_OK != (rc = sqlite3_bind_text(stmt, ++n, x, (int)strnlen(x, maxlen), SQLITE_STATIC))) { \
    fprintf(stderr, string_errmsg_bind, sqlite3_errmsg(db)); \
    return SQLITE_ERROR; \
}
    S(channel);
    S(added_by);
    S(subject);
    S(words);
#undef S
    if (SQLITE_DONE != (rc = sqlite3_step(stmt))) {
        fprintf(stderr, string_errmsg_insert, sqlite3_errmsg(db));
    }
    if (SQLITE_OK != (rc = sqlite3_reset(stmt))) {
        fprintf(stderr, string_errmsg_reset, sqlite3_errmsg(db));
    }
    return sqlite3_finalize(stmt);
}
int sqlite_version(sqlite3 * db) {
    int rc;
    sqlite3_stmt *stmt = NULL;
    if (SQLITE_OK != (rc = sqlite3_prepare_v2(db, "SELECT SQLITE_VERSION()", -1, &stmt, 0))) {
        fprintf(stderr, string_errmsg_prepare, sqlite3_errmsg(db));
        return 1;
    }
    if (SQLITE_ROW == (rc = sqlite3_step(stmt))) {
        printf("SQLite version: %s\n", sqlite3_column_text(stmt, 0));
    }
    return sqlite3_finalize(stmt);
}