path: root/src/libtidbit/tidbit-database-sqlite.c

/* Tidbit
 * Copyright (C) 2010 Charlie Brej tidbit@brej.org
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 3.0 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 */

#include <glib.h>
#include <sqlite3.h>
#include <sys/stat.h>

#include "tidbit.h"
#include "tidbit-database.h"
#include "tidbit-record.h"
#include "tidbit-database-sqlite.h"

#define GET_SQLITE_BACKEND(_database) ((PtrTidbitDatabaseSqlite)(_database->backend))

static void tidbit_database_sqlite_free (PtrTidbitDatabase database);
static unsigned int tidbit_database_sqlite_insert (PtrTidbitDatabase database, PtrTidbitRecord record);
static PtrTidbitRecord tidbit_database_sqlite_fetch (PtrTidbitDatabase database, PtrTidbitGuid guid);
static PtrTidbitGuidGroup tidbit_database_sqlite_query (PtrTidbitDatabase database, PtrTidbitQuery query);

static TidbitDatabaseFunctions backend_functions = {
    &tidbit_database_sqlite_free,
    &tidbit_database_sqlite_insert,
    &tidbit_database_sqlite_fetch,
    &tidbit_database_sqlite_query,
    };

static void my_sqlite_exec (sqlite3 *db, char *sql)
{
 gchar *err;
 gint res = sqlite3_exec (db, sql, NULL, NULL, &err);
 if (res != SQLITE_OK)
    g_print("%s\n", err);
 g_assert(res == SQLITE_OK);
}

static sqlite3_int64 my_sqlite_get_int64 (sqlite3 *db, char *sql)
{
 sqlite3_stmt *stmt;
 int res = sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL);
 g_assert(res == SQLITE_OK);
 sqlite3_step (stmt);
 sqlite3_int64 reply = sqlite3_column_int64 (stmt, 0);
 sqlite3_finalize (stmt);
 return reply;
}

static char *my_sqlite_get_string (sqlite3 *db, char *sql)
{
 sqlite3_stmt *stmt;
 int res = sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL);
 g_assert(res == SQLITE_OK);
 sqlite3_step (stmt);
 char* reply = g_strdup((char*) sqlite3_column_text(stmt, 0));
 sqlite3_finalize (stmt);
 return reply;
}

PtrTidbitDatabase tidbit_database_sqlite_new (void)
{
 PtrTidbitDatabaseSqlite backend = g_new(TidbitDatabaseSqlite, 1);
 PtrTidbitDatabase tidbit_database = tidbit_database_new(backend, &backend_functions);
 sqlite3 *db;
 char* filename = g_build_filename (g_get_home_dir(), "/.cache", NULL);
 if (!g_file_test (filename, G_FILE_TEST_EXISTS))
    mkdir (filename, 0700);
 g_free (filename);
 
 filename = g_build_filename (g_get_home_dir(), "/.cache/tidbit_cache.sqlite", NULL);
 int res = sqlite3_open(filename, &db);
 g_free (filename);
 
 backend->db = db;
 g_assert(res == SQLITE_OK);
 int tables_present = my_sqlite_get_int64 (db, "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = 'settings';");
 int schema_version = 0;
 if (tables_present)
    schema_version = my_sqlite_get_int64 (db, "SELECT value FROM settings WHERE key = 'schema_version';");
 
 if (schema_version < 1){
    my_sqlite_exec (db, "CREATE TABLE settings (key TEXT, value TEXT, PRIMARY KEY (key));");
    my_sqlite_exec (db, "CREATE TABLE records (record INTEGER, data TEXT, PRIMARY KEY (record));");
    my_sqlite_exec (db, "CREATE TABLE guids (record INTEGER, guid TEXT KEY, PRIMARY KEY (record));");
    my_sqlite_exec (db, "CREATE TABLE elements (record INTEGER KEY, key TEXT KEY, value TEXT KEY);");
    
    my_sqlite_exec (db, "REPLACE INTO settings (key, value) VALUES ('schema_version', 1);");
    }
 
 my_sqlite_exec(db, "PRAGMA synchronous = 0;");
 my_sqlite_exec(db, "PRAGMA cache_size = 1000000;");
 
 return tidbit_database;
}

static void tidbit_database_sqlite_free (PtrTidbitDatabase database)
{
 PtrTidbitDatabaseSqlite backend = GET_SQLITE_BACKEND(database);
 sqlite3_close (backend->db);
 g_free(backend);
}

struct query_builder{
 GString* string;
 int index;
};

static void add_query_to_select_join_builder (PtrTidbitQueryExp expression, gpointer user_data)
{
 struct query_builder* builder = user_data;
 builder->index++;
 g_string_append_printf (builder->string, ", elements test%d", builder->index);
}

static void add_query_to_select_join_match_builder (PtrTidbitQueryExp expression, gpointer user_data)
{
 struct query_builder* builder = user_data;
 g_string_append_printf (builder->string, " AND test%d.record = test%d.record", builder->index, builder->index + 1);
 builder->index++;
}

static void add_query_to_select_condition_builder (PtrTidbitQueryExp expression, gpointer user_data)
{
 struct query_builder* builder = user_data;
 builder->index++;
 char *sql = sqlite3_mprintf (" AND test%d.key = '%q' AND test%d.value = '%q'", builder->index, expression->key, builder->index, expression->value);
 g_string_append (builder->string, sql);
 sqlite3_free(sql);
}

static unsigned int tidbit_database_sqlite_insert (PtrTidbitDatabase database, PtrTidbitRecord record)
{
 PtrTidbitDatabaseSqlite backend = GET_SQLITE_BACKEND(database);
 sqlite3 *db = backend->db;
 
 char* sql = sqlite3_mprintf("SELECT COUNT(*) FROM records WHERE data = '%q';", tidbit_record_get_raw (record));
 int entry_present = my_sqlite_get_int64 (db, sql);
 sqlite3_free(sql);
 if (entry_present)
    return 1;
 
 PtrTidbitQuery query = tidbit_query_record_replaces (record);
 struct query_builder builder;
 
 builder.index = 0;
 builder.string = g_string_new("SELECT test0.record FROM elements test0");
 tidbit_query_foreach_exp (query, add_query_to_select_join_builder, &builder);
 
 builder.index = 0;
 g_string_append (builder.string, " ON 1 ");
 tidbit_query_foreach_exp (query, add_query_to_select_join_match_builder, &builder);
 
 builder.index = 0;
 sql = sqlite3_mprintf(" WHERE test0.key = '%q' AND test0.value = '%q'", TIDBIT_RECORD_ELEMENT_KEY_TABLE, query->table_name);
 g_string_append (builder.string, sql);
 sqlite3_free(sql);
 tidbit_query_foreach_exp (query, add_query_to_select_condition_builder, &builder);
 
 sqlite3_stmt *stmt;
 int res = sqlite3_prepare_v2 (db, builder.string->str, -1, &stmt, NULL);
 g_assert(res == SQLITE_OK);
 
 while (sqlite3_step (stmt) == SQLITE_ROW){
    sqlite3_int64 record_index = sqlite3_column_int64 (stmt, 0);
    sql = sqlite3_mprintf("DELETE FROM records WHERE record = %lld;", record_index);
    my_sqlite_exec (db, sql);
    sqlite3_free(sql);
    sql = sqlite3_mprintf("DELETE FROM guids WHERE record = %lld;", record_index);
    my_sqlite_exec (db, sql);
    sqlite3_free(sql);
    sql = sqlite3_mprintf("DELETE FROM elements WHERE record = %lld;", record_index);
    my_sqlite_exec (db, sql);
    sqlite3_free(sql);
    }
 sqlite3_finalize (stmt);
 g_string_free (builder.string, TRUE);
 
 sql = sqlite3_mprintf("INSERT INTO records (data) VALUES ('%q');", tidbit_record_get_raw (record));
 my_sqlite_exec (db, sql);
 sqlite3_free(sql);
 
 sqlite3_int64 rowid = sqlite3_last_insert_rowid(db);
 
 sql = sqlite3_mprintf("INSERT INTO guids (record, guid) VALUES (%lld, '%q');", rowid, tidbit_guid_get_string (tidbit_record_get_guid (record)));
 my_sqlite_exec (db, sql);
 sqlite3_free(sql);
 
 sql = sqlite3_mprintf("INSERT INTO elements (record, key, value) VALUES (%lld, '%q', '%q');", rowid, TIDBIT_RECORD_ELEMENT_KEY_TABLE, record->table_name);
 my_sqlite_exec (db, sql);
 sqlite3_free(sql);
 
 sql = sqlite3_mprintf("INSERT INTO elements (record, key, value) VALUES (%lld, '%q', '%q');", rowid, TIDBIT_RECORD_ELEMENT_KEY_USERKEY, record->user_name);
 my_sqlite_exec (db, sql);
 sqlite3_free(sql);
 
 sql = sqlite3_mprintf("INSERT INTO elements (record, key, value) VALUES (%lld, '%q', '%lld');", rowid, TIDBIT_RECORD_ELEMENT_KEY_CREATED, (unsigned long long) record->start_time);
 my_sqlite_exec (db, sql);
 sqlite3_free(sql);
 
 sql = sqlite3_mprintf("INSERT INTO elements (record, key, value) VALUES (%lld, '%q', '%lld');", rowid, TIDBIT_RECORD_ELEMENT_KEY_EXPIRES, (unsigned long long) record->end_time);
 my_sqlite_exec (db, sql);
 sqlite3_free(sql);
 
 sql = sqlite3_mprintf("INSERT INTO elements (record, key, value) VALUES (%lld, '%q', '%q');", rowid, TIDBIT_RECORD_ELEMENT_KEY_SIGNED, tidbit_guid_get_string (tidbit_record_get_guid (record)));
 my_sqlite_exec (db, sql);
 sqlite3_free(sql);

 for (GList* element_itter = record->elements; element_itter; element_itter = g_list_next(element_itter)){
    PtrTidbitRecordElement element = element_itter->data;
    sql = sqlite3_mprintf("INSERT INTO elements (record, key, value) VALUES (%lld, '%q', '%q');", rowid, element->key, element->value);
    my_sqlite_exec (db, sql);
    sqlite3_free(sql);
    }
 return 0;
}

static PtrTidbitRecord tidbit_database_sqlite_fetch (PtrTidbitDatabase database, PtrTidbitGuid guid)
{
 PtrTidbitDatabaseSqlite backend = GET_SQLITE_BACKEND(database);
 sqlite3 *db = backend->db;
 char *sql = sqlite3_mprintf("SELECT record FROM guids WHERE guid = '%q';", tidbit_guid_get_string (guid));
 sqlite3_int64 rowid = my_sqlite_get_int64 (db, sql);
 sqlite3_free(sql);
 
 sql = sqlite3_mprintf("SELECT data FROM records WHERE record = %lld;", rowid);
 char *record_raw = my_sqlite_get_string (db, sql);
 sqlite3_free(sql);
 
 PtrTidbitRecord record = tidbit_record_from_raw (record_raw);
 g_free(record_raw);
 
 return record;
}

static PtrTidbitGuidGroup tidbit_database_sqlite_query (PtrTidbitDatabase database, PtrTidbitQuery query)
{
 PtrTidbitDatabaseSqlite backend = GET_SQLITE_BACKEND(database);
 sqlite3 *db = backend->db;
 struct query_builder builder;
 
 builder.index = 0;
 builder.string = g_string_new("SELECT guid FROM guids, elements test0");
 tidbit_query_foreach_exp (query, add_query_to_select_join_builder, &builder);
 
 builder.index = 0;
 g_string_append (builder.string, " ON guids.record = test0.record");
 tidbit_query_foreach_exp (query, add_query_to_select_join_match_builder, &builder);
 
 builder.index = 0;
 char *sql = sqlite3_mprintf(" WHERE test0.key = '%q' AND test0.value = '%q'", TIDBIT_RECORD_ELEMENT_KEY_TABLE, query->table_name);
 g_string_append (builder.string, sql);
 sqlite3_free(sql);
 tidbit_query_foreach_exp (query, add_query_to_select_condition_builder, &builder);
 
 
 sqlite3_stmt *stmt;
 int res = sqlite3_prepare_v2 (db, builder.string->str, -1, &stmt, NULL);
 g_assert(res == SQLITE_OK);
 
 PtrTidbitGuidGroup guid_group = tidbit_guid_group_new ();
 while (sqlite3_step (stmt) == SQLITE_ROW){
    char* guid_text = (char*) sqlite3_column_text(stmt, 0);
    PtrTidbitGuid guid = tidbit_guid_new (guid_text);
    tidbit_guid_group_append (guid_group, guid);
    tidbit_guid_unref (guid);
    }
 sqlite3_finalize (stmt);
 g_string_free (builder.string, TRUE);
 
 return guid_group;
}