Re: [Evolution-hackers] Adding the created and modified columns to summary tables
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 (versio
[Evolution-hackers] Adding the created and modified columns to summary tables
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 =