db.c

Go to the documentation of this file.

/*
 * Prompt Workbench Core - Database Layer Implementation
 * Copyright (c) 2025 nnlazaro
 * 
 * This file is part of Prompt Workbench Core, licensed under the
 * Educational and Non-Commercial Use License.
 * See LICENSE file for details.
 * 
 * FOR EDUCATIONAL USE ONLY - NOT FOR PRODUCTION OR COMMERCIAL USE
 */
 
#include "db.h"
#include <sqlite3.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
 
static sqlite3* db = NULL;
 
static const char* CREATE_TABLES_SQL = 
    "CREATE TABLE IF NOT EXISTS test_suites ("
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "    title TEXT NOT NULL,"
    "    description TEXT,"
    "    system_prompt TEXT NOT NULL,"
    "    model TEXT NOT NULL"
    ");"
    "CREATE TABLE IF NOT EXISTS user_prompts ("
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "    prompt TEXT NOT NULL,"
    "    test_suite_id INTEGER NOT NULL,"
    "    FOREIGN KEY (test_suite_id) REFERENCES test_suites(id) ON DELETE CASCADE"
    ");";
 
int db_init(const char* db_path) {
    if (!db_path) {
        return -1;
    }
    
    if (db) {
        sqlite3_close(db);
        db = NULL;
    }
    
    int rc = sqlite3_open(db_path, &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        db = NULL;
        return -1;
    }
    
    /* Enable foreign key constraints */
    char* err_msg = NULL;
    rc = sqlite3_exec(db, "PRAGMA foreign_keys = ON;", NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to enable foreign keys: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        db = NULL;
        return -1;
    }
    
    rc = sqlite3_exec(db, CREATE_TABLES_SQL, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        db = NULL;
        return -1;
    }
    
    return 0;
}
 
void db_close(void) {
    if (db) {
        sqlite3_close(db);
        db = NULL;
    }
}
 
/* Test Suite CRUD operations */
 
int db_create_test_suite(const char* title, const char* description, 
                         const char* system_prompt, const char* model) {
    if (!db || !title || !system_prompt || !model) {
        return -1;
    }
    
    const char* sql = "INSERT INTO test_suites (title, description, system_prompt, model) "
                     "VALUES (?, ?, ?, ?);";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    sqlite3_bind_text(stmt, 1, title, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, description ? description : "", -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, system_prompt, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, model, -1, SQLITE_TRANSIENT);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Execution failed: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    return (int)sqlite3_last_insert_rowid(db);
}
 
TestSuite* db_get_test_suite(int id) {
    if (!db || id <= 0) {
        return NULL;
    }
    
    const char* sql = "SELECT id, title, description, system_prompt, model "
                     "FROM test_suites WHERE id = ?;";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return NULL;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_ROW) {
        sqlite3_finalize(stmt);
        return NULL;
    }
    
    TestSuite* suite = malloc(sizeof(TestSuite));
    if (!suite) {
        sqlite3_finalize(stmt);
        return NULL;
    }
    
    suite->id = sqlite3_column_int(stmt, 0);
    suite->title = strdup((const char*)sqlite3_column_text(stmt, 1));
    suite->description = strdup((const char*)sqlite3_column_text(stmt, 2));
    suite->system_prompt = strdup((const char*)sqlite3_column_text(stmt, 3));
    suite->model = strdup((const char*)sqlite3_column_text(stmt, 4));
    
    sqlite3_finalize(stmt);
    return suite;
}
 
TestSuite** db_get_all_test_suites(int* count) {
    if (!db || !count) {
        return NULL;
    }
    
    *count = 0;
    
    const char* sql = "SELECT id, title, description, system_prompt, model FROM test_suites;";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return NULL;
    }
    
    int capacity = 10;
    TestSuite** suites = malloc(sizeof(TestSuite*) * capacity);
    if (!suites) {
        sqlite3_finalize(stmt);
        return NULL;
    }
    
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        if (*count >= capacity) {
            capacity *= 2;
            TestSuite** new_suites = realloc(suites, sizeof(TestSuite*) * capacity);
            if (!new_suites) {
                db_free_test_suites(suites, *count);
                sqlite3_finalize(stmt);
                return NULL;
            }
            suites = new_suites;
        }
        
        TestSuite* suite = malloc(sizeof(TestSuite));
        if (!suite) {
            db_free_test_suites(suites, *count);
            sqlite3_finalize(stmt);
            return NULL;
        }
        
        suite->id = sqlite3_column_int(stmt, 0);
        suite->title = strdup((const char*)sqlite3_column_text(stmt, 1));
        suite->description = strdup((const char*)sqlite3_column_text(stmt, 2));
        suite->system_prompt = strdup((const char*)sqlite3_column_text(stmt, 3));
        suite->model = strdup((const char*)sqlite3_column_text(stmt, 4));
        
        suites[*count] = suite;
        (*count)++;
    }
    
    sqlite3_finalize(stmt);
    
    if (*count == 0) {
        free(suites);
        return NULL;
    }
    
    return suites;
}
 
int db_update_test_suite(int id, const char* title, const char* description,
                         const char* system_prompt, const char* model) {
    if (!db || id <= 0) {
        return -1;
    }
    
    if (!title && !description && !system_prompt && !model) {
        return 0;
    }
    
    char sql[512] = "UPDATE test_suites SET ";
    int first = 1;
    
    if (title) {
        strcat(sql, "title = ?");
        first = 0;
    }
    if (description) {
        if (!first) strcat(sql, ", ");
        strcat(sql, "description = ?");
        first = 0;
    }
    if (system_prompt) {
        if (!first) strcat(sql, ", ");
        strcat(sql, "system_prompt = ?");
        first = 0;
    }
    if (model) {
        if (!first) strcat(sql, ", ");
        strcat(sql, "model = ?");
    }
    
    strcat(sql, " WHERE id = ?;");
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    int param = 1;
    if (title) sqlite3_bind_text(stmt, param++, title, -1, SQLITE_TRANSIENT);
    if (description) sqlite3_bind_text(stmt, param++, description, -1, SQLITE_TRANSIENT);
    if (system_prompt) sqlite3_bind_text(stmt, param++, system_prompt, -1, SQLITE_TRANSIENT);
    if (model) sqlite3_bind_text(stmt, param++, model, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, param, id);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Execution failed: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    return 0;
}
 
int db_delete_test_suite(int id) {
    if (!db || id <= 0) {
        return -1;
    }
    
    const char* sql = "DELETE FROM test_suites WHERE id = ?;";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Execution failed: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    return 0;
}
 
void db_free_test_suite(TestSuite* suite) {
    if (suite) {
        free(suite->title);
        free(suite->description);
        free(suite->system_prompt);
        free(suite->model);
        free(suite);
    }
}
 
void db_free_test_suites(TestSuite** suites, int count) {
    if (suites) {
        for (int i = 0; i < count; i++) {
            db_free_test_suite(suites[i]);
        }
        free(suites);
    }
}
 
/* User Prompt CRUD operations */
 
int db_create_user_prompt(const char* prompt, int test_suite_id) {
    if (!db || !prompt || test_suite_id <= 0) {
        return -1;
    }
    
    const char* sql = "INSERT INTO user_prompts (prompt, test_suite_id) VALUES (?, ?);";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    sqlite3_bind_text(stmt, 1, prompt, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 2, test_suite_id);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Execution failed: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    return (int)sqlite3_last_insert_rowid(db);
}
 
UserPrompt* db_get_user_prompt(int id) {
    if (!db || id <= 0) {
        return NULL;
    }
    
    const char* sql = "SELECT id, prompt, test_suite_id FROM user_prompts WHERE id = ?;";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return NULL;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_ROW) {
        sqlite3_finalize(stmt);
        return NULL;
    }
    
    UserPrompt* prompt = malloc(sizeof(UserPrompt));
    if (!prompt) {
        sqlite3_finalize(stmt);
        return NULL;
    }
    
    prompt->id = sqlite3_column_int(stmt, 0);
    prompt->prompt = strdup((const char*)sqlite3_column_text(stmt, 1));
    prompt->test_suite_id = sqlite3_column_int(stmt, 2);
    
    sqlite3_finalize(stmt);
    return prompt;
}
 
UserPrompt** db_get_user_prompts_by_suite(int test_suite_id, int* count) {
    if (!db || test_suite_id <= 0 || !count) {
        return NULL;
    }
    
    *count = 0;
    
    const char* sql = "SELECT id, prompt, test_suite_id FROM user_prompts WHERE test_suite_id = ?;";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return NULL;
    }
    
    sqlite3_bind_int(stmt, 1, test_suite_id);
    
    int capacity = 10;
    UserPrompt** prompts = malloc(sizeof(UserPrompt*) * capacity);
    if (!prompts) {
        sqlite3_finalize(stmt);
        return NULL;
    }
    
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        if (*count >= capacity) {
            capacity *= 2;
            UserPrompt** new_prompts = realloc(prompts, sizeof(UserPrompt*) * capacity);
            if (!new_prompts) {
                db_free_user_prompts(prompts, *count);
                sqlite3_finalize(stmt);
                return NULL;
            }
            prompts = new_prompts;
        }
        
        UserPrompt* prompt = malloc(sizeof(UserPrompt));
        if (!prompt) {
            db_free_user_prompts(prompts, *count);
            sqlite3_finalize(stmt);
            return NULL;
        }
        
        prompt->id = sqlite3_column_int(stmt, 0);
        prompt->prompt = strdup((const char*)sqlite3_column_text(stmt, 1));
        prompt->test_suite_id = sqlite3_column_int(stmt, 2);
        
        prompts[*count] = prompt;
        (*count)++;
    }
    
    sqlite3_finalize(stmt);
    
    if (*count == 0) {
        free(prompts);
        return NULL;
    }
    
    return prompts;
}
 
UserPrompt** db_get_all_user_prompts(int* count) {
    if (!db || !count) {
        return NULL;
    }
    
    *count = 0;
    
    const char* sql = "SELECT id, prompt, test_suite_id FROM user_prompts;";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return NULL;
    }
    
    int capacity = 10;
    UserPrompt** prompts = malloc(sizeof(UserPrompt*) * capacity);
    if (!prompts) {
        sqlite3_finalize(stmt);
        return NULL;
    }
    
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        if (*count >= capacity) {
            capacity *= 2;
            UserPrompt** new_prompts = realloc(prompts, sizeof(UserPrompt*) * capacity);
            if (!new_prompts) {
                db_free_user_prompts(prompts, *count);
                sqlite3_finalize(stmt);
                return NULL;
            }
            prompts = new_prompts;
        }
        
        UserPrompt* prompt = malloc(sizeof(UserPrompt));
        if (!prompt) {
            db_free_user_prompts(prompts, *count);
            sqlite3_finalize(stmt);
            return NULL;
        }
        
        prompt->id = sqlite3_column_int(stmt, 0);
        prompt->prompt = strdup((const char*)sqlite3_column_text(stmt, 1));
        prompt->test_suite_id = sqlite3_column_int(stmt, 2);
        
        prompts[*count] = prompt;
        (*count)++;
    }
    
    sqlite3_finalize(stmt);
    
    if (*count == 0) {
        free(prompts);
        return NULL;
    }
    
    return prompts;
}
 
int db_update_user_prompt(int id, const char* prompt, int test_suite_id) {
    if (!db || id <= 0) {
        return -1;
    }
    
    if (!prompt && test_suite_id <= 0) {
        return 0;
    }
    
    char sql[256] = "UPDATE user_prompts SET ";
    
    if (prompt && test_suite_id > 0) {
        strcat(sql, "prompt = ?, test_suite_id = ? WHERE id = ?;");
    } else if (prompt) {
        strcat(sql, "prompt = ? WHERE id = ?;");
    } else {
        strcat(sql, "test_suite_id = ? WHERE id = ?;");
    }
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    int param = 1;
    if (prompt) sqlite3_bind_text(stmt, param++, prompt, -1, SQLITE_TRANSIENT);
    if (test_suite_id > 0) sqlite3_bind_int(stmt, param++, test_suite_id);
    sqlite3_bind_int(stmt, param, id);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Execution failed: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    return 0;
}
 
int db_delete_user_prompt(int id) {
    if (!db || id <= 0) {
        return -1;
    }
    
    const char* sql = "DELETE FROM user_prompts WHERE id = ?;";
    
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Execution failed: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    return 0;
}
 
void db_free_user_prompt(UserPrompt* prompt) {
    if (prompt) {
        free(prompt->prompt);
        free(prompt);
    }
}
 
void db_free_user_prompts(UserPrompt** prompts, int count) {
    if (prompts) {
        for (int i = 0; i < count; i++) {
            db_free_user_prompt(prompts[i]);
        }
        free(prompts);
    }
}