test/database.c

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2025 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
 * POSSIBLIITY OF SUCH DAMAGE.
 */

#include "database.h"

#include <stdio.h>
#include <stdlib.h>
#include <errno.h>
#include <unistd.h>

#include <cx/buffer.h>
#include <cx/streams.h>
#include <cx/mempool.h>

#define TEST_DB        "test.db"
#define TEST_DATA_FILE "testdata.sql"


// create test.db and execute testdata script
int init_test_db(void) {
    sqlite3 *db;
    char *err_msg = NULL;
    
    FILE *f = fopen(TEST_DATA_FILE, "r");
    if(!f) {
        fprintf(stderr, "Cannot open test data file %s: %s\n", TEST_DATA_FILE, strerror(errno));
    }
    
    if(unlink(TEST_DB)) {
        if(errno != ENOENT) {
            fprintf(stderr, "Cannot unlink %s: %s\n", TEST_DB, strerror(errno));
            fclose(f);
            return 1;
        }
    }
    if(sqlite3_open(TEST_DB, &db) != SQLITE_OK) {
        fprintf(stderr, "Cannot open database %s: %s\n", TEST_DB, sqlite3_errmsg(db));
        fclose(f);
        return 1;
    }
    
    CxBuffer *buf = cxBufferCreate(cxDefaultAllocator, NULL, 2048, CX_BUFFER_AUTO_EXTEND | CX_BUFFER_FREE_CONTENTS);
    cx_stream_copy(f, buf, (cx_read_func)fread, (cx_write_func)cxBufferWrite);
    int err = 0;
    if(buf->size > 0) {
        cxBufferTerminate(buf);
        
        if(sqlite3_exec(db, buf->space, 0, 0, &err_msg) != SQLITE_OK) {
            fprintf(stderr, "SQL error: %s\n", err_msg);
            sqlite3_free(err_msg);
            err = 1;
        }
    } else {
        fprintf(stderr, "Error: no file content\n");
        err = 1;
    }
    cxBufferFree(buf);
    sqlite3_close(db);
    
    return err;
}


static DBUContext *ctx;
static DBUConnection *conn;

static DBUClass *country;
static DBUClass *address;
static DBUClass *person;
static DBUClass *role;

typedef struct Country {
    int64_t country_id;
    cxmutstr name;
} Country;

typedef struct Address {
    int64_t address_id;
    
    cxmutstr street;
    cxmutstr zip;
    cxmutstr city;
    
    Country *country;
} Address;

typedef struct Person {
    int64_t person_id;
    
    cxmutstr name;
    cxmutstr email;
    int      age;
    bool     iscustomer;
    uint64_t hash;
    
    Address *address;
    
    CxList *roles;
} Person;

typedef struct Role {
    int64_t role_id;
    int64_t person_id;
    cxmutstr name;
} Role;

int init_db_tests(void) {
    ctx = dbuContextCreate();
    
    country = dbuRegisterClass(ctx, "country", Country, country_id);
    dbuClassAdd(country, Country, name);
    
    address = dbuRegisterClass(ctx, "address", Address, address_id);
    dbuClassAdd(address, Address, street);
    dbuClassAdd(address, Address, zip);
    dbuClassAdd(address, Address, city);
    dbuClassAddObj(address, "country_id", offsetof(Address, country), country);
    
    role = dbuRegisterClass(ctx, "role", Role, role_id);
    dbuClassAdd(role, Role, person_id);
    dbuClassAdd(role, Role, name);
    
    person = dbuRegisterClass(ctx, "person", Person, person_id);
    dbuClassAdd(person, Person, name);
    dbuClassAdd(person, Person, email);
    dbuClassAdd(person, Person, age);
    dbuClassAdd(person, Person, iscustomer);
    dbuClassAdd(person, Person, hash);
    dbuClassAddObj(person, "address_id", offsetof(Person, address), address);
    dbuClassAddCxLinkedList(person, "person_id", offsetof(Person, roles), role);
    
    dbuClassAddForeignKey(role, Role, person_id, person);
    dbuClassAdd(role, Role, name);
    
    return 0;
}

void cleanup_db_tests(void) {
    if(conn) {
        dbuConnectionFree(conn);
    }
    dbuContextFree(ctx);
}

CX_TEST(testSqliteConnection) {
    CX_TEST_DO {
        conn = dbuSQLiteConnection(TEST_DB);
        CX_TEST_ASSERT(conn);
        CX_TEST_ASSERT(dbuConnectionIsActive(conn));
    }
}

CX_TEST(testSingleValueQuery) {
    CX_TEST_DO {
        CX_TEST_ASSERT(conn);
        
        DBUQuery *q = dbuConnectionQuery(conn, NULL);
        CX_TEST_ASSERT(q);
        CX_TEST_ASSERT(dbuQuerySetSQL(q, "select 12;") == 0);
        CX_TEST_ASSERT(dbuQueryExec(q) == 0);
        
        DBUResult *r = dbuQueryGetResult(q);
        CX_TEST_ASSERT(r);
        int value;
        CX_TEST_ASSERT(dbuResultAsValue(r, &value) == 0);
        CX_TEST_ASSERT(value == 12);
        
        dbuQueryFree(q);
        
    }
}

CX_TEST(testSqlExecQuery) {
    CX_TEST_DO {
        CX_TEST_ASSERT(conn);
        
        DBUResult *r = dbuSqlExecQuery(conn, NULL, "select email from person where name = 'alice';");
        CX_TEST_ASSERT(r);
        cxmutstr value;
        CX_TEST_ASSERT(dbuResultAsValue(r, &value) == 0);
        CX_TEST_ASSERT(!cx_strcmp(value, "alice@example.com"));
        free(value.ptr);
    }
}

CX_TEST(testSqlExecQueryParam) {
    CX_TEST_DO {
        CX_TEST_ASSERT(conn);
        
        DBUResult *r = dbuSqlExecQueryParam(conn, NULL, "select email from person where name = ?;", "alice");
        CX_TEST_ASSERT(r);
        cxmutstr value;
        CX_TEST_ASSERT(dbuResultAsValue(r, &value) == 0);
        CX_TEST_ASSERT(!cx_strcmp(value, "alice@example.com"));
        free(value.ptr);
        
        r = dbuSqlExecQueryParam(conn, NULL, "select name from person where hash = ?;", 987654321);
        CX_TEST_ASSERT(r);
        CX_TEST_ASSERT(dbuResultAsValue(r, &value) == 0);
        CX_TEST_ASSERT(!cx_strcmp(value, "bob"));
        free(value.ptr);
    }
}

CX_TEST(testSqlExec) {
    CX_TEST_DO {
        CX_TEST_ASSERT(conn);
        
        int t1 = dbuSqlExec(conn, "create table ExecTest1(a int);");
        CX_TEST_ASSERT(t1 == 0);
        
        int t2 = dbuSqlExec(conn, "insert into ExecTest1(a) values (1);");
        CX_TEST_ASSERT(t2 == 0);
        
        int t3 = dbuSqlExec(conn, "drop table ExecTest1;");
        CX_TEST_ASSERT(t3 == 0);
        
        int fail = dbuSqlExec(conn, "select * from Fail;");
        CX_TEST_ASSERT(fail != 0);
        
        
        CX_TEST_ASSERT(dbuSqlExec(conn, "PRAGMA foreign_keys = ON;") == 0);
        CX_TEST_ASSERT(dbuSqlExec(conn,
                "create table ExecTest3( "
                "test1_id integer primary key autoincrement, "
                "value int, "
                "str text);") == 0);
        CX_TEST_ASSERT(dbuSqlExec(conn,
                "create table ExecTest4( "
                "test2_id integer primary key autoincrement, "
                "exectest1_id int references ExecTest3(test1_id), "
                "value int);") == 0);
        CX_TEST_ASSERT(dbuSqlExec(conn, "insert into ExecTest3(value) values (123);") == 0);
        
        // fail test
        CX_TEST_ASSERT(dbuSqlExec(conn, "insert into ExecTest4(exectest1_id, value) values (-999, 123);") != 0);
        
        // param tests
        CX_TEST_ASSERT(dbuSqlExecParamInt32(conn, "insert into ExecTest3(value) values (?);", 11) == 0);
        CX_TEST_ASSERT(dbuSqlExecParamUInt32(conn, "insert into ExecTest3(value) values (?);", 12) == 0);
        CX_TEST_ASSERT(dbuSqlExecParamInt64(conn, "insert into ExecTest3(value) values (?);", 4000000000L) == 0);
        CX_TEST_ASSERT(dbuSqlExecParamUInt64(conn, "insert into ExecTest3(value) values (?);", 5000000000L) == 0);
        CX_TEST_ASSERT(dbuSqlExecParamString(conn, "insert into ExecTest3(str) values (?);", "str1") == 0);
        CX_TEST_ASSERT(dbuSqlExecParamCxString(conn, "insert into ExecTest3(str) values (?);", cx_str("str2")) == 0);
        CX_TEST_ASSERT(dbuSqlExecParamCxMutStr(conn, "insert into ExecTest3(str) values (?);", cx_mutstr("str3")) == 0);
        
        // param macro test
        CX_TEST_ASSERT(dbuSqlExecParam(conn, "insert into ExecTest3(value) values (?);", 111) == 0);
        CX_TEST_ASSERT(dbuSqlExecParam(conn, "insert into ExecTest3(str) values (?);", "str4") == 0);
        CX_TEST_ASSERT(dbuSqlExecParam(conn, "insert into ExecTest3(str) values (?);", cx_str("str5")) == 0);
        CX_TEST_ASSERT(dbuSqlExecParam(conn, "insert into ExecTest3(str) values (?);", cx_mutstr("str6")) == 0);
    }
}

CX_TEST(testSingleTableQuery) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        DBUQuery *query = dbuQueryCreate(conn, mp->allocator, "select * from address order by street;");
        DBUObjectBuilder *builder = dbuObjectBuilder(address, query, mp->allocator);
        CxList *adr = dbuObjectBuilderGetList(builder);
        
        CX_TEST_ASSERT(adr);
        CX_TEST_ASSERT(cxListSize(adr) == 2);
        
        Address *a0 = cxListAt(adr, 0);
        Address *a1 = cxListAt(adr, 1);
        CX_TEST_ASSERT(a0);
        CX_TEST_ASSERT(a1);
        CX_TEST_ASSERT(!cx_strcmp(a0->street, "street 1"));
        CX_TEST_ASSERT(!cx_strcmp(a1->street, "street 2"));
        CX_TEST_ASSERT(!cx_strcmp(a0->zip, "12343"));
        CX_TEST_ASSERT(!cx_strcmp(a1->zip, "23456"));
        CX_TEST_ASSERT(!cx_strcmp(a0->city, "city 17"));
        CX_TEST_ASSERT(!cx_strcmp(a1->city, "city 18"));
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST_SUBROUTINE(verifyMultiTableResult, CxList *persons, bool verify_address) {
    CX_TEST_ASSERT(persons);
    CX_TEST_ASSERT(cxListSize(persons) == 2);

    Person *p0 = cxListAt(persons, 0);
    Person *p1 = cxListAt(persons, 1);
    
    CX_TEST_ASSERT(p0);
    CX_TEST_ASSERT(p1);
    CX_TEST_ASSERT(!cx_strcmp(p0->name, "alice"));
    CX_TEST_ASSERT(!cx_strcmp(p1->name, "bob"));
    CX_TEST_ASSERT(!cx_strcmp(p0->email, "alice@example.com"));
    CX_TEST_ASSERT(!cx_strcmp(p1->email, "bob@example.com"));
    CX_TEST_ASSERT(p0->age == 30);
    CX_TEST_ASSERT(p1->age == 25);
    CX_TEST_ASSERT(p0->iscustomer == 0);
    CX_TEST_ASSERT(p1->iscustomer == 1);
    CX_TEST_ASSERT(p0->hash == 123456789);
    CX_TEST_ASSERT(p1->hash == 987654321);
    
    if(verify_address) {
        CX_TEST_ASSERT(p0->address != NULL);
        CX_TEST_ASSERT(p1->address != NULL);

        CX_TEST_ASSERT(!cx_strcmp(p0->address->street, "street 1"));
        CX_TEST_ASSERT(!cx_strcmp(p1->address->street, "street 2"));
        CX_TEST_ASSERT(!cx_strcmp(p0->address->zip, "12343"));
        CX_TEST_ASSERT(!cx_strcmp(p1->address->zip, "23456"));
        CX_TEST_ASSERT(!cx_strcmp(p0->address->city, "city 17"));
        CX_TEST_ASSERT(!cx_strcmp(p1->address->city, "city 18"));
    }
}

CX_TEST(testMultiTableQuery) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        // select persons + addresses, a.address_id as __address__address_id (table separator) 
        const char *sql1 = "select p.*, a.address_id as [__address__address_id], a.street, a.zip, a.city from Person p inner join Address a on p.address_id = a.address_id order by p.person_id;";
        DBUQuery *query = dbuQueryCreate(conn, mp->allocator, sql1);
        DBUObjectBuilder *builder = dbuObjectBuilder(person, query, mp->allocator);
        CxList *persons = dbuObjectBuilderGetList(builder);
        
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResult, persons, true);
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST(testMultiTableQuery1) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        // select persons + addresses, NULL as __address (table seprator)
        const char *sql1 = "select p.*, NULL as [__address], a.* from Person p inner join Address a on p.address_id = a.address_id order by p.person_id;";
        DBUQuery *query = dbuQueryCreate(conn, mp->allocator, sql1);
        DBUObjectBuilder *builder = dbuObjectBuilder(person, query, mp->allocator);
        CxList *persons = dbuObjectBuilderGetList(builder);
        
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResult, persons, true);
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST_SUBROUTINE(verifyMultiTableResultCountry, CxList *persons) {
    Person *p0 = cxListAt(persons, 0);
    Person *p1 = cxListAt(persons, 1);
    CX_TEST_ASSERT(p0 && p1);

    CX_TEST_ASSERT(p0->address->country);
    CX_TEST_ASSERT(p1->address->country);
    CX_TEST_ASSERT(!cx_strcmp(p0->address->country->name, "Germany"));
    CX_TEST_ASSERT(!cx_strcmp(p1->address->country->name, "Germany"));
}

CX_TEST(testMultiTableQuery2) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        // select persons, addresses and countries
        const char *sql2 = "select p.*, "
                           "a.address_id as [__address__address_id], a.street, a.zip, a.city, "
                           "c.country_id as [__country__country_id], c.name "
                           "from Person p inner join Address a on p.address_id = a.address_id inner join Country c on a.country_id = c.country_id "
                           "order by p.person_id;";
        DBUQuery *query = dbuQueryCreate(conn, mp->allocator, sql2);
        DBUObjectBuilder *builder = dbuObjectBuilder(person, query, mp->allocator);
        CxList *persons = dbuObjectBuilderGetList(builder);
        
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResult, persons, true);
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResultCountry, persons);
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST(testMultiTableQueryUnknownResult1) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        // select persons, addresses and countries
        const char *sql2 = "select p.*, 'unknown1' as unknown1, "
                           "a.address_id as [__address__address_id], a.street, 'unknown2' as unknown2, a.zip, 123 as unknown3, a.city, "
                           "NULL as [__unknowntab], 456 as unknown_4, "
                           "c.country_id as [__country__country_id], c.name "
                           "from Person p inner join Address a on p.address_id = a.address_id inner join Country c on a.country_id = c.country_id "
                           "order by p.person_id;";
        DBUQuery *query = dbuQueryCreate(conn, mp->allocator, sql2);
        DBUObjectBuilder *builder = dbuObjectBuilder(person, query, mp->allocator);
        CxList *persons = dbuObjectBuilderGetList(builder);
        
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResult, persons, true);
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResultCountry, persons);
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST_SUBROUTINE(verifyPersonRoles, CxList *persons) {
    Person *p0 = cxListAt(persons, 0);
    Person *p1 = cxListAt(persons, 1);
    CX_TEST_ASSERT(p0 && p1);
    
    CX_TEST_ASSERT(cxListSize(p0->roles) == 3);
    CX_TEST_ASSERT(cxListSize(p1->roles) == 1);
    
    Role *r0 = cxListAt(p0->roles, 0);
    Role *r1 = cxListAt(p0->roles, 1);
    Role *r2 = cxListAt(p0->roles, 2);
    
    Role *p1_r0 = cxListAt(p1->roles, 0);
    
    CX_TEST_ASSERT(!cx_strcmp(r0->name, "finance"));
    CX_TEST_ASSERT(!cx_strcmp(r1->name, "dev"));
    CX_TEST_ASSERT(!cx_strcmp(r2->name, "manager"));
    
    CX_TEST_ASSERT(!cx_strcmp(p1_r0->name, "extern"));
}

CX_TEST(testQuerySubListDense1) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        // select persons + a list of roles per person
        const char *sql1 = 
            "select p.*, r.role_id as [__role__role_id], r.person_id, r.name from Person p inner join Role r on p.person_id = r.person_id order by p.person_id;";
        DBUQuery *query = dbuQueryCreate(conn, mp->allocator, sql1);
        DBUObjectBuilder *builder = dbuObjectBuilder(person, query, mp->allocator);
        dbuObjectBuilderSetDenseResult(builder, true);
        CxList *persons = dbuObjectBuilderGetList(builder);
        
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResult, persons, false);
        CX_TEST_CALL_SUBROUTINE(verifyPersonRoles, persons);
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST(testQuerySubListDenseWithMultiTable1) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        const char *sql1 = 
            "select p.*, NULL as [__address], a.*, r.role_id as [__role__role_id], r.person_id, r.name "
            "from Person p inner join Address a on p.address_id = a.address_id "
            "inner join Role r on p.person_id = r.person_id order by p.person_id;";
        DBUQuery *query = dbuQueryCreate(conn, mp->allocator, sql1);
        DBUObjectBuilder *builder = dbuObjectBuilder(person, query, mp->allocator);
        dbuObjectBuilderSetDenseResult(builder, true);
        CxList *persons = dbuObjectBuilderGetList(builder);
        
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResult, persons, true); // also verify address
        CX_TEST_CALL_SUBROUTINE(verifyPersonRoles, persons);
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST(testQuerySubListDenseWithMultiTable2) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    // Same as testQuerySubListDenseWithMultiTable1, but with a different column order
    // person, role, address
    
    CX_TEST_DO {
        const char *sql1 = 
            "select p.*, r.role_id as [__role__role_id], r.person_id, r.name, NULL as [__address], a.* "
            "from Person p inner join Address a on p.address_id = a.address_id "
            "inner join Role r on p.person_id = r.person_id order by p.person_id;";
        DBUQuery *query = dbuQueryCreate(conn, mp->allocator, sql1);
        DBUObjectBuilder *builder = dbuObjectBuilder(person, query, mp->allocator);
        dbuObjectBuilderSetDenseResult(builder, true);
        CxList *persons = dbuObjectBuilderGetList(builder);
        
        CX_TEST_CALL_SUBROUTINE(verifyMultiTableResult, persons, true); // also verify address
        CX_TEST_CALL_SUBROUTINE(verifyPersonRoles, persons);
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}