Re: [Evolution-hackers] Adding the created and modified columns to summary tables

[Philip Van Hoof <spam pvanhoof be>]

I have created a bug for this:

http://bugzilla.gnome.org/show_bug.cgi?id=565082

I have attached a cleaner version of the patch too.

Please comment and/or review

On Wed, 2008-12-17 at 12:41 +0100, Philip Van Hoof wrote:
> 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)
@@ -767,15 +767,137 @@
 	return ((camel_db_command (cdb, query, ex)));
 }
 
+static int
+camel_db_migrate_folder_prepare (CamelDB *cdb, const char *folder_name, gint version, CamelException *ex)
+{
+	int ret = 0;
+	sqlite3_stmt *stmt = NULL;
+	char *table_creation_query;
+
+	/* Migration stage one: storing the old data */
+
+	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);
+	}
+
+	/* Add later version migrations here */
+
+	return ret;
+}
+
+static int
+camel_db_migrate_folder_recreate (CamelDB *cdb, const char *folder_name, gint version, CamelException *ex)
+{
+	int ret = 0;
+	char *table_creation_query;
+
+	/* Migration stage two: writing back the old data */
+
+	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);
+	}
+
+	/* Add later version migrations here */
+
+	return ret;
+}
+
+static int
+camel_db_write_folder_version (CamelDB *cdb, const char *folder_name, int old_version, CamelException *ex)
+{
+	int ret = 0;
+	char *version_creation_query;
+	char *version_insert_query;
+
+	version_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS '%q_version' ( version TEXT )", folder_name);
+
+	if (old_version == -1)
+		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;
+}
+
+static int
+camel_db_get_folder_version (CamelDB *cdb, const char *folder_name, CamelException *ex)
+{
+	int version = -1, ret;
+	char *query;
+	sqlite3_stmt *stmt = NULL;
+
+	query = sqlite3_mprintf ("SELECT version FROM '%q_version'", folder_name);
+
+	ret = sqlite3_prepare_v2 (cdb->db, query, -1, &stmt, NULL);
+
+	if (ret == SQLITE_OK)
+		ret = sqlite3_step (stmt);
+	if (ret == SQLITE_OK)
+		version = sqlite3_column_int (stmt, 0);
+
+	sqlite3_finalize (stmt);
+
+	sqlite3_free (query);
+
+	return version;
+}
+
 int 
 camel_db_prepare_message_info_table (CamelDB *cdb, const char *folder_name, CamelException *ex)
 {
-	int ret;
+	int ret, current_version;
 	char *table_creation_query, *safe_index;
 
+	current_version = camel_db_get_folder_version (cdb, folder_name, ex);
+
+	/* Migration stage one: storing the old data if necessary */
+	ret = camel_db_migrate_folder_prepare (cdb, folder_name, current_version, ex);
+
 	/* 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,10 +930,17 @@
 	ret = camel_db_add_to_transaction (cdb, table_creation_query, ex);
 	g_free (safe_index);
 	sqlite3_free (table_creation_query);
-	
+
+	/* Migration stage two: rewriting the old data if necessary */
+	ret = camel_db_migrate_folder_recreate (cdb, folder_name, current_version, ex);
+
+	/* Final step: (over)write the current version label */
+	ret = camel_db_write_folder_version (cdb, folder_name, current_version, ex);
+
 	return ret;
 }
 
+
 int
 camel_db_write_message_info_record (CamelDB *cdb, const char *folder_name, CamelMIRecord *record, CamelException *ex)
 {
@@ -821,7 +950,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,