Implrement the sqlite backend (all 4 functions)

2 files changed, 218 insertions, 3 deletions

diff --git a/src/libtidbit/tidbit-database-sqlite.c b/src/libtidbit/tidbit-database-sqlite.c
index 45e866f..0a43f7d 100644
--- a/src/libtidbit/tidbit-database-sqlite.c
+++ b/src/libtidbit/tidbit-database-sqlite.c
@@ -18,27 +18,239 @@
  */
 
 #include <glib.h>
+#include <sqlite3.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 PtrTidbitGuidSet tidbit_database_sqlite_query (PtrTidbitDatabase database, PtrTidbitQuery query);
 
-static TidbitDatabaseFunctions backend_functions = {&tidbit_database_sqlite_free};
+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 database = tidbit_database_new(backend, &backend_functions);
- return database;
+ PtrTidbitDatabase tidbit_database = tidbit_database_new(backend, &backend_functions);
+ sqlite3 *db;
+ int res = sqlite3_open("/home/brejc8/.cache/tidbit_cache.sqlite", &db);
+ 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);");
+    }
+ 
+ 
+ return tidbit_database;
 }
 
 static void tidbit_database_sqlite_free (PtrTidbitDatabase database)
 {
  PtrTidbitDatabaseSqlite backend = GET_SQLITE_BACKEND(database);
+ sqlite3_close (backend->db);
  g_free(backend);
 }
+
+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;
+ 
+ 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;
+}
+
+
+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);
+}
+
+//SELECT guid FROM guids, elements table_match, elements b ON guids.record = a1.record AND a1.record = b.record WHERE a1.key = 'key' AND a1.value = 'value' AND b.key= 'another_key' AND b.value= 'value'
+
+
+static PtrTidbitGuidSet 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);
+ 
+ PtrTidbitGuidSet guid_set = tidbit_guid_set_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_set_append (guid_set, guid);
+    tidbit_guid_unref (guid);
+    }
+ sqlite3_finalize (stmt);
+ g_string_free (builder.string, TRUE);
+ 
+ return guid_set;
+}
+
+
+
+
+
+
+
+
+
+
+
+
+
+
diff --git a/src/libtidbit/tidbit-database-sqlite.h b/src/libtidbit/tidbit-database-sqlite.h
index 29a1e8a..cff4016 100644
--- a/src/libtidbit/tidbit-database-sqlite.h
+++ b/src/libtidbit/tidbit-database-sqlite.h
@@ -20,7 +20,10 @@
 #ifndef _TIDBIT_DATABASE_SQLITE_H_
 #define _TIDBIT_DATABASE_SQLITE_H_
 
+#include <sqlite3.h>
+
 typedef struct TidbitDatabaseSqlite{
+ sqlite3 *db;
 }TidbitDatabaseSqlite, *PtrTidbitDatabaseSqlite;