dbutils/sqlite.c

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2024 Olaf Wintermann. 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.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS 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 COPYRIGHT HOLDER OR 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.
 */

#ifdef DBU_SQLITE

#include "sqlite.h"
#include "db.h"

#include <string.h>
#include <stdlib.h>
#include <cx/array_list.h>


DBUConnection* dbuSQLiteConnection(const char *filename) {
    sqlite3 *db;
    int rc = sqlite3_open(filename, &db);
    if(rc != SQLITE_OK) {
        return NULL;
    }
    
    DBUConnection *conn = dbuSQLiteConnectionFromDB(db, true);
    if(!conn) {
        sqlite3_close(db);
    }
    return conn;
}

DBUConnection* dbuSQLiteConnectionFromDB(sqlite3 *db, bool autoclose) {
    DBUSQLiteConnection *connection = malloc(sizeof(DBUSQLiteConnection));
    if(!connection) {
        return NULL;
    }
    memset(connection, 0, sizeof(DBUSQLiteConnection));
    connection->connection.createQuery = dbuSQLiteConnCreateQuery;
    connection->connection.isActive = dbuSQLiteConnIsActive;
    connection->connection.free = dbuSQLiteConnFree;
    connection->connection.data = db;
    connection->db = db;
    connection->autoclose = autoclose;
    
    return (DBUConnection*)connection;
}

/* ------------------- SQLite Connection Implementation  -------------------*/

DBUQuery* dbuSQLiteConnCreateQuery(DBUConnection *connection, const CxAllocator *a) {
    DBUSQLiteQuery *query = malloc(sizeof(DBUSQLiteQuery));
    if(!query) {
        return NULL;
    }
    memset(query, 0, sizeof(DBUSQLiteQuery));
    query->query.allocator = a;
    query->query.connection = connection;
    query->query.setSQL = dbuSQLiteQuerySetSQL;
    query->query.setParamString = dbuSQLiteQuerySetParamString;
    query->query.setParamInt = dbuSQLiteQuerySetParamInt;
    query->query.setParamInt64 = dbuSQLiteQuerySetParamInt64;
    query->query.setParamDouble = dbuSQLiteQuerySetParamDouble;
    query->query.setParamNull = dbuSQLiteQuerySetParamNull;
    query->query.setParamBytes = dbuSQLiteQuerySetParamBytes;
    query->query.exec = dbuSQLiteQueryExec;
    query->query.getResult = dbuSQLiteQueryGetResult;
    query->query.reset = dbuSQLiteQueryReset;
    query->query.free = dbuSQLiteQueryFree;
    query->db = connection->data;
    query->query.ref = 1;
    return (DBUQuery*)query;
}

int dbuSQLiteConnIsActive(DBUConnection *connection) {
    return 1;
}

void dbuSQLiteConnFree(DBUConnection *connection) {
    DBUSQLiteConnection *sqlite = (DBUSQLiteConnection*)connection;
    if(sqlite->autoclose) {
        sqlite3_close(sqlite->db);
    }
    free(connection);
}


/* --------------------- SQLite Query Implementation  ---------------------*/

int dbuSQLiteQuerySetSQL(DBUQuery *q, const char *sql) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    if(query->stmt) {
        return 1;
    }
    return sqlite3_prepare_v2(query->db, sql, -1, &query->stmt, 0) != SQLITE_OK;
}

int dbuSQLiteQuerySetParamString(DBUQuery *q, int index, cxstring str) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    return sqlite3_bind_text(query->stmt, index, str.ptr, str.length, NULL);
}

int dbuSQLiteQuerySetParamInt(DBUQuery *q, int index, int i) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    return sqlite3_bind_int(query->stmt, index, i);
}

int dbuSQLiteQuerySetParamInt64(DBUQuery *q, int index, int64_t i) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    return sqlite3_bind_int64(query->stmt, index, i);
}

int dbuSQLiteQuerySetParamDouble(DBUQuery *q, int index, double d) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    return sqlite3_bind_double(query->stmt, index, d);
}

int dbuSQLiteQuerySetParamNull(DBUQuery *q, int index) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    return sqlite3_bind_null(query->stmt, index);
}

int dbuSQLiteQuerySetParamBytes(DBUQuery *q, int index, void *bytes, int len) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    return sqlite3_bind_blob(query->stmt, index, bytes, len, NULL);
}

int dbuSQLiteQueryExec(DBUQuery *q) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    int step = sqlite3_step(query->stmt);
    if(step == SQLITE_ROW || step == SQLITE_DONE) {
        query->step = step;
        return 0;
    }
    return 1;
}

DBUResult* dbuSQLiteQueryGetResult(DBUQuery *q) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    if(query->result) {
        return (DBUResult*)query->result;
    }
    
    DBUSQLiteResult *result = malloc(sizeof(DBUSQLiteResult));
    if(!result) {
        return NULL;
    }
    memset(result, 0, sizeof(DBUSQLiteResult));
    query->query.ref++;
    result->query = query;
    query->result = result;
    
    result->result.optional_numRows = NULL;
    result->result.numFields = dbuSQLiteResultNumFields;
    result->result.hasData = dbuSQLiteResultHasData;
    result->result.isOk = dbuSQLiteResultIsOk;
    result->result.nextRow = dbuSQLiteResultNextRow;
    result->result.fieldName = dbuSQLiteResultFieldName;
    result->result.fieldType = dbuSQLiteResultFieldType;
    result->result.isNull = dbuSQLiteResultIsNull;
    result->result.getText = dbuSQLiteResultGetText;
    result->result.optional_getInt = dbuSQLiteResultGetInt;
    result->result.optional_getDouble = dbuSQLiteResultGetDouble;
    result->result.optional_getBinary = dbuSQLiteResultGetBinary;
    result->result.free = dbuSQLiteResultFree;
    result->result.allocator = q->allocator;
    result->stmt = query->stmt;
    result->step = query->step;
    
    return (DBUResult*)result;
}

int dbuSQLiteQueryReset(DBUQuery *q) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    // TODO
    return 0;
}

void dbuSQLiteQueryFree(DBUQuery *q) {
    DBUSQLiteQuery *query = (DBUSQLiteQuery*)q;
    if(--query->query.ref > 0) {
        return;
    }
    sqlite3_finalize(query->stmt);
    free(query);
}


/* --------------------- SQLite Result Implementation  ---------------------*/

int dbuSQLiteResultNumFields(DBUResult *result) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    return sqlite3_column_count(r->stmt);
}

int dbuSQLiteResultHasData(DBUResult *result) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    return r->step == SQLITE_ROW;
}

int dbuSQLiteResultIsOk(DBUResult *result) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    return r->step == SQLITE_DONE;
}

int dbuSQLiteResultNextRow(DBUResult *result) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    r->step = sqlite3_step(r->stmt);
    int done = r->step == SQLITE_DONE;
    r->result.rowIndex++;
    return done;
}

const char* dbuSQLiteResultFieldName(DBUResult *result, int field) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    return sqlite3_column_name(r->stmt, field);
}

DBUFieldType dbuSQLiteResultFieldType(DBUResult *result, int field) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    int type = sqlite3_column_type(r->stmt, field);
    DBUFieldType dbuType;
    switch(type) {
        default: dbuType = DBU_FIELD_TEXT; break;
        case SQLITE_INTEGER: dbuType = DBU_FIELD_INT; break;
        case SQLITE_FLOAT: dbuType = DBU_FIELD_DOUBLE; break;
        case SQLITE_TEXT: dbuType = DBU_FIELD_TEXT; break;
        case SQLITE_BLOB: dbuType = DBU_FIELD_BINARY; break;
        case SQLITE_NULL: dbuType = DBU_FIELD_NULL; break;
    }
    return dbuType;
}

int dbuSQLiteResultIsNull(DBUResult *result, int field) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    return sqlite3_column_type(r->stmt, field) == SQLITE_NULL;
}

cxstring dbuSQLiteResultGetText(DBUResult *result, int field) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    const char *s = (const char*)sqlite3_column_text(r->stmt, field);
    size_t len = (size_t)sqlite3_column_bytes(r->stmt, field);
    return cx_strn(s, len);
}

int64_t dbuSQLiteResultGetInt(DBUResult *result, int field) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    return sqlite3_column_int64(r->stmt, field);
}

double dbuSQLiteResultGetDouble(DBUResult *result, int field) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    return sqlite3_column_double(r->stmt, field);
}

DBUBytes dbuSQLiteResultGetBinary(DBUResult *result, int field) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    const unsigned char *s = sqlite3_column_blob(r->stmt, field);
    size_t len = (size_t)sqlite3_column_bytes(r->stmt, field);
    return (DBUBytes) { s, len };
}

void dbuSQLiteResultFree(DBUResult *result) {
    DBUSQLiteResult *r = (DBUSQLiteResult*)result;
    r->query->query.free((DBUQuery*)r->query);
    free(r);
}

#endif /* DBU_SQLITE */