From 92b8216698b939901c231be53e86445f0e77210e Mon Sep 17 00:00:00 2001
From: Charlie Brej <cbrej@cs.man.ac.uk>
Date: Mon, 9 Aug 2010 01:31:10 +0100
Subject: Delete replaced records in the swlite database

---
 src/libtidbit/tidbit-database-sqlite.c | 96 ++++++++++++++++++++++------------
 1 file changed, 64 insertions(+), 32 deletions(-)

diff --git a/src/libtidbit/tidbit-database-sqlite.c b/src/libtidbit/tidbit-database-sqlite.c
index 02ba81f..f0754a4 100644
--- a/src/libtidbit/tidbit-database-sqlite.c
+++ b/src/libtidbit/tidbit-database-sqlite.c
@@ -114,6 +114,34 @@ static void tidbit_database_sqlite_free (PtrTidbitDatabase database)
  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);
@@ -125,6 +153,42 @@ static unsigned int tidbit_database_sqlite_insert (PtrTidbitDatabase database, P
  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);
@@ -182,38 +246,6 @@ static PtrTidbitRecord tidbit_database_sqlite_fetch (PtrTidbitDatabase database,
  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 PtrTidbitGuidGroup tidbit_database_sqlite_query (PtrTidbitDatabase database, PtrTidbitQuery query)
 {
  PtrTidbitDatabaseSqlite backend = GET_SQLITE_BACKEND(database);
-- 
cgit v1.2.3