Hi there,
This patch deals with converting the old SQLite tables to a new format
and adds created and modified.
It's a first rough idea, so please let me know what I should change.
--
Philip Van Hoof, freelance software developer
home: me at pvanhoof dot be
gnome: pvanhoof at gnome dot org
http://pvanhoof.be/blog
http://codeminded.be
Index: camel/camel-db.c
===================================================================
--- camel/camel-db.c (revision 9841)
+++ camel/camel-db.c (working copy)
@@ -770,12 +770,65 @@
int
camel_db_prepare_message_info_table (CamelDB *cdb, const char *folder_name, CamelException *ex)
{
- int ret;
+ sqlite3_stmt *stmt = NULL;
+ int ret, version = 0, recreate = 0;
char *table_creation_query, *safe_index;
+ char *version_creation_query;
+ char *version_insert_query;
+ table_creation_query = sqlite3_mprintf ("SELECT version FROM '%q_version'", folder_name);
+
+ ret = sqlite3_prepare_v2 (cdb->db, table_creation_query, -1, &stmt, NULL);
+
+ if (ret == SQLITE_OK)
+ ret = sqlite3_step (stmt);
+
+ if (ret == SQLITE_OK)
+ version = sqlite3_column_int (stmt, 0);
+ else
+ recreate = 1;
+
+ sqlite3_finalize (stmt);
+
+ sqlite3_free (table_creation_query);
+
+ if (version < 1) {
+ table_creation_query = sqlite3_mprintf ("DROP TABLE 'temp_%q'", folder_name);
+ stmt = NULL;
+ ret = sqlite3_prepare_v2 (cdb->db, table_creation_query, -1, &stmt, NULL);
+ if (ret == SQLITE_OK)
+ ret = sqlite3_step (stmt);
+ sqlite3_finalize (stmt);
+ sqlite3_free (table_creation_query);
+
+ table_creation_query = sqlite3_mprintf ("CREATE TEMP TABLE IF NOT EXISTS 'temp_%q' ( uid TEXT PRIMARY KEY , flags INTEGER , msg_type INTEGER , read INTEGER , deleted INTEGER , replied INTEGER , important INTEGER , junk INTEGER , attachment INTEGER , msg_security INTEGER , size INTEGER , dsent NUMERIC , dreceived NUMERIC , subject TEXT , mail_from TEXT , mail_to TEXT , mail_cc TEXT , mlist TEXT , followup_flag TEXT , followup_completed_on TEXT , followup_due_by TEXT , part TEXT , labels TEXT , usertags TEXT , cinfo TEXT , bdata TEXT, created TEXT, modified TEXT )", folder_name);
+ stmt = NULL;
+ ret = sqlite3_prepare_v2 (cdb->db, table_creation_query, -1, &stmt, NULL);
+ if (ret == SQLITE_OK)
+ ret = sqlite3_step (stmt);
+ sqlite3_finalize (stmt);
+ sqlite3_free (table_creation_query);
+
+ table_creation_query = sqlite3_mprintf ("INSERT INTO 'temp_%q' SELECT uid , flags , msg_type , read , deleted , replied , important , junk , attachment , msg_security , size , dsent , dreceived , subject , mail_from , mail_to , mail_cc , mlist , followup_flag , followup_completed_on , followup_due_by , part , labels , usertags , cinfo , bdata , strftime(\"%%s\", 'now'), strftime(\"%%s\", 'now') FROM %Q", folder_name, folder_name);
+ stmt = NULL;
+ ret = sqlite3_prepare_v2 (cdb->db, table_creation_query, -1, &stmt, NULL);
+ if (ret == SQLITE_OK)
+ ret = sqlite3_step (stmt);
+ sqlite3_finalize (stmt);
+ sqlite3_free (table_creation_query);
+
+ table_creation_query = sqlite3_mprintf ("DROP TABLE %Q", folder_name);
+ stmt = NULL;
+ ret = sqlite3_prepare_v2 (cdb->db, table_creation_query, -1, &stmt, NULL);
+ if (ret == SQLITE_OK)
+ ret = sqlite3_step (stmt);
+ sqlite3_finalize (stmt);
+ sqlite3_free (table_creation_query);
+ }
+
/* README: It is possible to compress all system flags into a single column and use just as userflags but that makes querying for other applications difficult an d bloats the parsing code. Instead, it is better to bloat the tables. Sqlite should have some optimizations for sparse columns etc. */
- table_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS %Q ( uid TEXT PRIMARY KEY , flags INTEGER , msg_type INTEGER , read INTEGER , deleted INTEGER , replied INTEGER , important INTEGER , junk INTEGER , attachment INTEGER , msg_security INTEGER , size INTEGER , dsent NUMERIC , dreceived NUMERIC , subject TEXT , mail_from TEXT , mail_to TEXT , mail_cc TEXT , mlist TEXT , followup_flag TEXT , followup_completed_on TEXT , followup_due_by TEXT , part TEXT , labels TEXT , usertags TEXT , cinfo TEXT , bdata TEXT )", folder_name);
+ table_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS %Q ( uid TEXT PRIMARY KEY , flags INTEGER , msg_type INTEGER , read INTEGER , deleted INTEGER , replied INTEGER , important INTEGER , junk INTEGER , attachment INTEGER , msg_security INTEGER , size INTEGER , dsent NUMERIC , dreceived NUMERIC , subject TEXT , mail_from TEXT , mail_to TEXT , mail_cc TEXT , mlist TEXT , followup_flag TEXT , followup_completed_on TEXT , followup_due_by TEXT , part TEXT , labels TEXT , usertags TEXT , cinfo TEXT , bdata TEXT, created TEXT, modified TEXT )", folder_name);
ret = camel_db_add_to_transaction (cdb, table_creation_query, ex);
@@ -808,7 +861,29 @@
ret = camel_db_add_to_transaction (cdb, table_creation_query, ex);
g_free (safe_index);
sqlite3_free (table_creation_query);
-
+
+ if (version < 1) {
+ table_creation_query = sqlite3_mprintf ("INSERT INTO %Q SELECT uid , flags , msg_type , read , deleted , replied , important , junk , attachment , msg_security , size , dsent , dreceived , subject , mail_from , mail_to , mail_cc , mlist , followup_flag , followup_completed_on , followup_due_by , part , labels , usertags , cinfo , bdata, created, modified FROM 'temp_%q'", folder_name, folder_name);
+ ret = camel_db_add_to_transaction (cdb, table_creation_query, ex);
+ sqlite3_free (table_creation_query);
+
+ table_creation_query = sqlite3_mprintf ("DROP TABLE 'temp_%q'", folder_name);
+ ret = camel_db_add_to_transaction (cdb, table_creation_query, ex);
+ sqlite3_free (table_creation_query);
+ }
+
+ version_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS '%q_version' ( version TEXT )", folder_name);
+ if (recreate)
+ version_insert_query = sqlite3_mprintf ("INSERT INTO '%q_version' VALUES ('1')", folder_name);
+ else
+ version_insert_query = sqlite3_mprintf ("UPDATE '%q_version' SET version='1'", folder_name);
+
+ ret = camel_db_add_to_transaction (cdb, version_creation_query, ex);
+ ret = camel_db_add_to_transaction (cdb, version_insert_query, ex);
+
+ sqlite3_free (version_creation_query);
+ sqlite3_free (version_insert_query);
+
return ret;
}
@@ -821,7 +896,9 @@
/* NB: UGLIEST Hack. We can't modify the schema now. We are using msg_security (an unsed one to notify of FLAGGED/Dirty infos */
- ins_query = sqlite3_mprintf ("INSERT INTO %Q VALUES (%Q, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %ld, %ld, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q )",
+ /* Why ain't this an INSERT OR REPLACE instead of a DELETE and then an INSERT? */
+
+ ins_query = sqlite3_mprintf ("INSERT INTO %Q VALUES (%Q, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %ld, %ld, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, strftime(\"%%s\", 'now'), strftime(\"%%s\", 'now') )",
folder_name, record->uid, record->flags,
record->msg_type, record->read, record->deleted, record->replied,
record->important, record->junk, record->attachment, record->dirty,
Index: camel/camel-db.h
===================================================================
--- camel/camel-db.h (revision 9841)
+++ camel/camel-db.h (working copy)
@@ -20,7 +20,7 @@
#define CAMEL_DB_FREE_CACHE_SIZE 2 * 1024 * 1024
#define CAMEL_DB_SLEEP_INTERVAL 1*10*10
-#define CAMEL_DB_RELEASE_SQLITE_MEMORY if(!g_getenv("CAMEL_SQLITE_FREE_CACHE")) sqlite3_release_memory(CAMEL_DB_FREE_CACHE_SIZE);
+#define CAMEL_DB_RELEASE_SQLITE_MEMORY
#define CAMEL_DB_USE_SHARED_CACHE if(g_getenv("CAMEL_SQLITE_SHARED_CACHE")) sqlite3_enable_shared_cache(TRUE);
_______________________________________________
Evolution-hackers mailing list
Evolution-hackers@gnome.org
http://mail.gnome.org/mailman/listinfo/evolution-hackers
