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);
    
    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(testConnectionExec) {
    CX_TEST_DO {
        CX_TEST_ASSERT(conn);
        
        int t1 = dbuConnectionExec(conn, "create table ExecTest1(a int);");
        CX_TEST_ASSERT(t1 == 0);
        
        int t2 = dbuConnectionExec(conn, "insert into ExecTest1(a) values (1);");
        CX_TEST_ASSERT(t2 == 0);
        
        int t3 = dbuConnectionExec(conn, "drop table ExecTest1;");
        CX_TEST_ASSERT(t3 == 0);
        
        int fail = dbuConnectionExec(conn, "select * from Fail;");
        CX_TEST_ASSERT(fail != 0);
    }
}

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(testSqlExec) {
    CX_TEST_DO {
        CX_TEST_ASSERT(conn);
        
        DBUResult *r = dbuSqlExec(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(testSqlExecParam) {
    CX_TEST_DO {
        CX_TEST_ASSERT(conn);
        
        DBUResult *r = dbuSqlExecParam(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 = dbuSqlExecParam(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(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(testMultiTableQuery) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        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_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);
        
        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"));
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST(testMultiTableQuery1) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        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_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);
        
        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"));
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}

CX_TEST(testMultiTableQuery2) {
    CxMempool *mp = cxMempoolCreateSimple(64);
    
    CX_TEST_DO {
        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_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);
        
        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_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"));
        
        dbuObjectBuilderDestroy(builder);
    }
    
    cxMempoolFree(mp);
}