Mercurial > hg > dbutils / file revision

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2017 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 <stdio.h>
#include <sqlite3.h>

#include <dbutils/dbutils.h>
#include <dbutils/sqlite.h>
#include <dbutils/db.h>

#include <cx/buffer.h>
#include <cx/printf.h>

const char *sql_create_table_person =
"create table if not exists Person ("
"person_id integer primary key autoincrement, "
"name text, "
"email text, "
"age integer, " 
"iscustomer integer , "
"hash integer, "
"address_id integer);";

const char *sql_create_table_address =
"create table if not exists Address ("
"address_id integer primary key autoincrement, "
"street text, "
"zip text, "
"city text);";

const char *sql_create_table_role =
"create table if not exists Role ("
"role_id integer primary key autoincrement, "
"person_id integer, "
"name text);";


const char *sql_check_table =
"select person_id from Person limit 1;";

const char *sql_create_test_data1 = 
"insert into address (street, zip, city) "
"values "
"('street 1', '12343', 'city 17'), "
"('street 2', '23456', 'city 18');";

const char *sql_create_test_data2 = 
"insert into person (name, email, age, iscustomer, hash, address_id) "
"values "
"('alice', 'alice@example.com', 30, 0, 123456789, (select address_id from address where street = 'street 1')), "
"('bob', 'bob@example.com', 25, 1, 987654321,  (select address_id from address where street = 'street 2'));";

const char *sql_create_test_data3 = 
"insert into role (person_id, name) "
"values "
"(1, 'finance'), "
"(1, 'dev'), "
"(1, 'manager'), "
"(2, 'extern');";

typedef struct Address {
    int64_t address_id;
    
    cxmutstr street;
    cxmutstr zip;
    cxmutstr city;
} 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;


static int create_test_data(sqlite3 *db);

int main(int argc, char **argv) {
    
    
    DBUContext *ctx = dbuContextCreate();
    
    DBUClass *address = dbuRegisterClass(ctx, "address", Address, address_id);
    dbuClassAdd(address, Address, street);
    dbuClassAdd(address, Address, zip);
    dbuClassAdd(address, Address, city);
    
    DBUClass *role = dbuRegisterClass(ctx, "role", Role, role_id);
    
    DBUClass *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);
    
    // Open or create the database
    sqlite3 *db;
    int rc = sqlite3_open("test.db", &db);
    if(rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    
    if(create_test_data(db)) {
        return 1;
    }
    
    DBUConnection *conn = dbuSQLiteConnectionFromDB(db, true);
    
    // test dbuConnectionExec
    int execResult = dbuConnectionExec(conn, "drop table if exists test_table;");
    printf("exec result: %d\n\n", execResult);
    
    
    // single value query
    DBUQuery *q0 = conn->createQuery(conn, NULL);
    dbuQuerySetSQL(q0, "select 'hello world';");
    q0->exec(q0);
    DBUResult *r0 = q0->getResult(q0);
    char *str;
    if(!dbuResultAsValue(r0, &str)) {
        printf("single query value: %s\n\n", str);
        free(str);
    }
    
    DBUQuery *query = conn->createQuery(conn, NULL);
    dbuQuerySetSQL(query, "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;");
    
    DBUQuery *roleQuery = conn->createQuery(conn, NULL);
    dbuQuerySetSQL(roleQuery, "select * from role;");
    
    DBUQuery *query2 = conn->createQuery(conn, NULL);
    dbuQuerySetSQL(query2, "select p.*, a.address_id as [__address__address_id], a.street, a.zip, a.city, 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 left join Role r on p.person_id = r.person_id order by p.person_id, r.role_id;");
    
    DBUObjectBuilder *builder = dbuObjectBuilder(person, query2, cxDefaultAllocator);
    dbuObjectBuilderSetDenseResult(builder, true);
    //dbuObjectBuilderAddAdditionalQuery(builder, role, roleQuery);
    CxList *persons = dbuObjectBuilderGetList(builder);
    if(persons) {
        CxIterator i = cxListIterator(persons);
        cx_foreach(Person *, p, i) {
            CxBuffer rolebuffer;
            cxBufferInit(&rolebuffer, NULL, 256, 0, CX_BUFFER_AUTO_EXTEND|CX_BUFFER_FREE_CONTENTS);
            cxBufferPut(&rolebuffer, '[');
            if(p->roles) {
                CxIterator r = cxListIterator(p->roles);
                char *addseparator = "";
                cx_foreach(Role *, role, r) {
                    cx_bprintf(&rolebuffer, "%s{ role_id = %d, name = \"%s\"}", addseparator, role->role_id, role->name.ptr);
                    addseparator = ", ";
                }
            }
            cxBufferPut(&rolebuffer, ']');
            cxBufferPut(&rolebuffer, 0);
            printf("{ person_id = %" PRId64 ", name = \"%s\", email = \"%s\", age = %d, iscustomer = %s, hash = %" PRIu64 " roles = %s }\n",
                    p->person_id, p->name.ptr, p->email.ptr, p->age, p->iscustomer ? "true" : "false", p->hash, rolebuffer.space);
            
            cxBufferDestroy(&rolebuffer);
        }
    } else {
        fprintf(stderr, "Error\n");
    }
    
    conn->free(conn);
    
    return 0;
}

static int create_test_data(sqlite3 *db) {
    char *err_msg = NULL;
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db, sql_check_table, -1, &stmt, 0);
    if(rc == SQLITE_OK) {
        return 0;
    }
    
    rc = sqlite3_exec(db, sql_create_table_person, 0, 0, &err_msg);
    if(rc != SQLITE_OK) {
        fprintf(stderr, "SQLite error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }
    
    rc = sqlite3_exec(db, sql_create_table_address, 0, 0, &err_msg);
    if(rc != SQLITE_OK) {
        fprintf(stderr, "SQLite error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }
    
    rc = sqlite3_exec(db, sql_create_table_role, 0, 0, &err_msg);
    if(rc != SQLITE_OK) {
        fprintf(stderr, "SQLite error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }
    
    rc = sqlite3_exec(db, sql_create_test_data1, 0, 0, &err_msg);
    if(rc != SQLITE_OK) {
        fprintf(stderr, "SQLite error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }
    
    rc = sqlite3_exec(db, sql_create_test_data2, 0, 0, &err_msg);
    if(rc != SQLITE_OK) {
        fprintf(stderr, "SQLite error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }
    
    rc = sqlite3_exec(db, sql_create_test_data3, 0, 0, &err_msg);
    if(rc != SQLITE_OK) {
        fprintf(stderr, "SQLite error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }
    
    return 0;
}