camel/camel-db.c

   1 /* -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
   2 /* camel-imap-folder.c: class for an imap folder */
   3
   4 /*
   5  * Authors:
   6  *   Sankar P <psankar@novell.com>
   7  *   Srinivasa Ragavan <sragavan@novell.com>
   8  *
   9  * Copyright (C) 1999-2008 Novell, Inc. (www.novell.com)
  10  *
  11  * This program is free software; you can redistribute it and/or
  12  * modify it under the terms of version 2 of the GNU Lesser General Public
  13  * License as published by the Free Software Foundation.
  14  *
  15  * This program is distributed in the hope that it will be useful,
  16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18  * GNU Lesser General Public License for more details.
  19  *
  20  * You should have received a copy of the GNU Lesser General Public License
  21  * along with this program; if not, write to the Free Software
  22  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301
  23  * USA
  24  */
  25
  26 #include "camel-db.h"
  27 #include "camel-string-utils.h"
  28
  29 #include <config.h>
  30
  31 #include <ctype.h>
  32 #include <stdio.h>
  33 #include <stdlib.h>
  34 #include <string.h>
  35
  36 #include <glib/gi18n-lib.h>
  37
  38 #include "camel-debug.h"
  39 #include "camel-object.h"
  40
  41 /* how long to wait before invoking sync on the file */
  42 #define SYNC_TIMEOUT_SECONDS 5
  43
  44 #define READER_LOCK(cdb) g_rw_lock_reader_lock (&cdb->priv->rwlock)
  45 #define READER_UNLOCK(cdb) g_rw_lock_reader_unlock (&cdb->priv->rwlock)
  46 #define WRITER_LOCK(cdb) g_rw_lock_writer_lock (&cdb->priv->rwlock)
  47 #define WRITER_UNLOCK(cdb) g_rw_lock_writer_unlock (&cdb->priv->rwlock)
  48
  49 static sqlite3_vfs *old_vfs = NULL;
  50 static GThreadPool *sync_pool = NULL;
  51
  52 typedef struct {
  53         sqlite3_file parent;
  54         sqlite3_file *old_vfs_file; /* pointer to old_vfs' file */
  55         GRecMutex sync_mutex;
  56         guint timeout_id;
  57         gint flags;
  58 } CamelSqlite3File;
  59
  60 static gint
  61 call_old_file_Sync (CamelSqlite3File *cFile,
  62                     gint flags)
  63 {
  64         g_return_val_if_fail (old_vfs != NULL, SQLITE_ERROR);
  65         g_return_val_if_fail (cFile != NULL, SQLITE_ERROR);
  66
  67         g_return_val_if_fail (cFile->old_vfs_file->pMethods != NULL, SQLITE_ERROR);
  68         return cFile->old_vfs_file->pMethods->xSync (cFile->old_vfs_file, flags);
  69 }
  70
  71 typedef struct {
  72         GCond cond;
  73         GMutex mutex;
  74         gboolean is_set;
  75 } SyncDone;
  76
  77 struct SyncRequestData
  78 {
  79         CamelSqlite3File *cFile;
  80         guint32 flags;
  81         SyncDone *done; /* not NULL when waiting for a finish; will be freed by the caller */
  82 };
  83
  84 static void
  85 sync_request_thread_cb (gpointer task_data,
  86                         gpointer null_data)
  87 {
  88         struct SyncRequestData *sync_data = task_data;
  89         SyncDone *done;
  90
  91         g_return_if_fail (sync_data != NULL);
  92         g_return_if_fail (sync_data->cFile != NULL);
  93
  94         call_old_file_Sync (sync_data->cFile, sync_data->flags);
  95
  96         done = sync_data->done;
  97         g_free (sync_data);
  98
  99         if (done != NULL) {
 100                 g_mutex_lock (&done->mutex);
 101                 done->is_set = TRUE;
 102                 g_cond_broadcast (&done->cond);
 103                 g_mutex_unlock (&done->mutex);
 104         }
 105 }
 106
 107 static void
 108 sync_push_request (CamelSqlite3File *cFile,
 109                    gboolean wait_for_finish)
 110 {
 111         struct SyncRequestData *data;
 112         SyncDone *done = NULL;
 113         GError *error = NULL;
 114
 115         g_return_if_fail (cFile != NULL);
 116         g_return_if_fail (sync_pool != NULL);
 117
 118         g_rec_mutex_lock (&cFile->sync_mutex);
 119
 120         if (!cFile->flags) {
 121                 /* nothing to sync, might be when xClose is called
 122                    without any pending xSync request */
 123                 g_rec_mutex_unlock (&cFile->sync_mutex);
 124                 return;
 125         }
 126
 127         if (wait_for_finish) {
 128                 done = g_slice_new (SyncDone);
 129                 g_cond_init (&done->cond);
 130                 g_mutex_init (&done->mutex);
 131                 done->is_set = FALSE;
 132         }
 133
 134         data = g_new0 (struct SyncRequestData, 1);
 135         data->cFile = cFile;
 136         data->flags = cFile->flags;
 137         data->done = done;
 138
 139         cFile->flags = 0;
 140
 141         g_rec_mutex_unlock (&cFile->sync_mutex);
 142
 143         g_thread_pool_push (sync_pool, data, &error);
 144
 145         if (error) {
 146                 g_warning ("%s: Failed to push to thread pool: %s\n", G_STRFUNC, error->message);
 147                 g_error_free (error);
 148
 149                 if (done != NULL) {
 150                         g_cond_clear (&done->cond);
 151                         g_mutex_clear (&done->mutex);
 152                         g_slice_free (SyncDone, done);
 153                 }
 154
 155                 return;
 156         }
 157
 158         if (done != NULL) {
 159                 g_mutex_lock (&done->mutex);
 160                 while (!done->is_set)
 161                         g_cond_wait (&done->cond, &done->mutex);
 162                 g_mutex_unlock (&done->mutex);
 163
 164                 g_cond_clear (&done->cond);
 165                 g_mutex_clear (&done->mutex);
 166                 g_slice_free (SyncDone, done);
 167         }
 168 }
 169
 170 static gboolean
 171 sync_push_request_timeout (CamelSqlite3File *cFile)
 172 {
 173         g_rec_mutex_lock (&cFile->sync_mutex);
 174
 175         if (cFile->timeout_id != 0) {
 176                 sync_push_request (cFile, FALSE);
 177                 cFile->timeout_id = 0;
 178         }
 179
 180         g_rec_mutex_unlock (&cFile->sync_mutex);
 181
 182         return FALSE;
 183 }
 184
 185 #define def_subclassed(_nm, _params, _call)                     \
 186 static gint                                                     \
 187 camel_sqlite3_file_ ## _nm _params                              \
 188 {                                                               \
 189         CamelSqlite3File *cFile;                                \
 190                                                                 \
 191         g_return_val_if_fail (old_vfs != NULL, SQLITE_ERROR);   \
 192         g_return_val_if_fail (pFile != NULL, SQLITE_ERROR);     \
 193                                                                 \
 194         cFile = (CamelSqlite3File *) pFile;             \
 195         g_return_val_if_fail (cFile->old_vfs_file->pMethods != NULL, SQLITE_ERROR);     \
 196         return cFile->old_vfs_file->pMethods->_nm _call;        \
 197 }
 198
 199 def_subclassed (xRead, (sqlite3_file *pFile, gpointer pBuf, gint iAmt, sqlite3_int64 iOfst), (cFile->old_vfs_file, pBuf, iAmt, iOfst))
 200 def_subclassed (xWrite, (sqlite3_file *pFile, gconstpointer pBuf, gint iAmt, sqlite3_int64 iOfst), (cFile->old_vfs_file, pBuf, iAmt, iOfst))
 201 def_subclassed (xTruncate, (sqlite3_file *pFile, sqlite3_int64 size), (cFile->old_vfs_file, size))
 202 def_subclassed (xFileSize, (sqlite3_file *pFile, sqlite3_int64 *pSize), (cFile->old_vfs_file, pSize))
 203 def_subclassed (xLock, (sqlite3_file *pFile, gint lockType), (cFile->old_vfs_file, lockType))
 204 def_subclassed (xUnlock, (sqlite3_file *pFile, gint lockType), (cFile->old_vfs_file, lockType))
 205 def_subclassed (xFileControl, (sqlite3_file *pFile, gint op, gpointer pArg), (cFile->old_vfs_file, op, pArg))
 206 def_subclassed (xSectorSize, (sqlite3_file *pFile), (cFile->old_vfs_file))
 207 def_subclassed (xDeviceCharacteristics, (sqlite3_file *pFile), (cFile->old_vfs_file))
 208
 209 #undef def_subclassed
 210
 211 static gint
 212 camel_sqlite3_file_xCheckReservedLock (sqlite3_file *pFile,
 213                                        gint *pResOut)
 214 {
 215         CamelSqlite3File *cFile;
 216
 217         g_return_val_if_fail (old_vfs != NULL, SQLITE_ERROR);
 218         g_return_val_if_fail (pFile != NULL, SQLITE_ERROR);
 219
 220         cFile = (CamelSqlite3File *) pFile;
 221         g_return_val_if_fail (cFile->old_vfs_file->pMethods != NULL, SQLITE_ERROR);
 222
 223         /* check version in runtime */
 224         if (sqlite3_libversion_number () < 3006000)
 225                 return ((gint (*)(sqlite3_file *)) (cFile->old_vfs_file->pMethods->xCheckReservedLock)) (cFile->old_vfs_file);
 226         else
 227                 return ((gint (*)(sqlite3_file *, gint *)) (cFile->old_vfs_file->pMethods->xCheckReservedLock)) (cFile->old_vfs_file, pResOut);
 228 }
 229
 230 static gint
 231 camel_sqlite3_file_xClose (sqlite3_file *pFile)
 232 {
 233         CamelSqlite3File *cFile;
 234         gint res;
 235
 236         g_return_val_if_fail (old_vfs != NULL, SQLITE_ERROR);
 237         g_return_val_if_fail (pFile != NULL, SQLITE_ERROR);
 238
 239         cFile = (CamelSqlite3File *) pFile;
 240
 241         g_rec_mutex_lock (&cFile->sync_mutex);
 242
 243         /* Cancel any pending sync requests. */
 244         if (cFile->timeout_id > 0) {
 245                 g_source_remove (cFile->timeout_id);
 246                 cFile->timeout_id = 0;
 247         }
 248
 249         g_rec_mutex_unlock (&cFile->sync_mutex);
 250
 251         /* Make the last sync. */
 252         sync_push_request (cFile, TRUE);
 253
 254         if (cFile->old_vfs_file->pMethods)
 255                 res = cFile->old_vfs_file->pMethods->xClose (cFile->old_vfs_file);
 256         else
 257                 res = SQLITE_OK;
 258
 259         g_free (cFile->old_vfs_file);
 260         cFile->old_vfs_file = NULL;
 261
 262         g_rec_mutex_clear (&cFile->sync_mutex);
 263
 264         return res;
 265 }
 266
 267 static gint
 268 camel_sqlite3_file_xSync (sqlite3_file *pFile,
 269                           gint flags)
 270 {
 271         CamelSqlite3File *cFile;
 272
 273         g_return_val_if_fail (old_vfs != NULL, SQLITE_ERROR);
 274         g_return_val_if_fail (pFile != NULL, SQLITE_ERROR);
 275
 276         cFile = (CamelSqlite3File *) pFile;
 277
 278         g_rec_mutex_lock (&cFile->sync_mutex);
 279
 280         /* If a sync request is already scheduled, accumulate flags. */
 281         cFile->flags |= flags;
 282
 283         /* Cancel any pending sync requests. */
 284         if (cFile->timeout_id > 0)
 285                 g_source_remove (cFile->timeout_id);
 286
 287         /* Wait SYNC_TIMEOUT_SECONDS before we actually sync. */
 288         cFile->timeout_id = g_timeout_add_seconds (
 289                 SYNC_TIMEOUT_SECONDS, (GSourceFunc)
 290                 sync_push_request_timeout, cFile);
 291
 292         g_rec_mutex_unlock (&cFile->sync_mutex);
 293
 294         return SQLITE_OK;
 295 }
 296
 297 static gint
 298 camel_sqlite3_vfs_xOpen (sqlite3_vfs *pVfs,
 299                          const gchar *zPath,
 300                          sqlite3_file *pFile,
 301                          gint flags,
 302                          gint *pOutFlags)
 303 {
 304         static GRecMutex only_once_lock;
 305         static sqlite3_io_methods io_methods = {0};
 306         CamelSqlite3File *cFile;
 307         gint res;
 308
 309         g_return_val_if_fail (old_vfs != NULL, -1);
 310         g_return_val_if_fail (pFile != NULL, -1);
 311
 312         cFile = (CamelSqlite3File *) pFile;
 313         cFile->old_vfs_file = g_malloc0 (old_vfs->szOsFile);
 314
 315         res = old_vfs->xOpen (old_vfs, zPath, cFile->old_vfs_file, flags, pOutFlags);
 316         if (res != SQLITE_OK) {
 317                 g_free (cFile->old_vfs_file);
 318                 return res;
 319         }
 320
 321         g_rec_mutex_init (&cFile->sync_mutex);
 322
 323         g_rec_mutex_lock (&only_once_lock);
 324
 325         if (!sync_pool)
 326                 sync_pool = g_thread_pool_new (sync_request_thread_cb, NULL, 2, FALSE, NULL);
 327
 328         /* cFile->old_vfs_file->pMethods is NULL when open failed for some reason,
 329          * thus do not initialize our structure when do not know the version */
 330         if (io_methods.xClose == NULL && cFile->old_vfs_file->pMethods) {
 331                 /* initialize our subclass function only once */
 332                 io_methods.iVersion = cFile->old_vfs_file->pMethods->iVersion;
 333
 334                 /* check version in compile time */
 335                 #if SQLITE_VERSION_NUMBER < 3006000
 336                 io_methods.xCheckReservedLock = (gint (*)(sqlite3_file *)) camel_sqlite3_file_xCheckReservedLock;
 337                 #else
 338                 io_methods.xCheckReservedLock = camel_sqlite3_file_xCheckReservedLock;
 339                 #endif
 340
 341                 #define use_subclassed(x) io_methods.x = camel_sqlite3_file_ ## x
 342                 use_subclassed (xClose);
 343                 use_subclassed (xRead);
 344                 use_subclassed (xWrite);
 345                 use_subclassed (xTruncate);
 346                 use_subclassed (xSync);
 347                 use_subclassed (xFileSize);
 348                 use_subclassed (xLock);
 349                 use_subclassed (xUnlock);
 350                 use_subclassed (xFileControl);
 351                 use_subclassed (xSectorSize);
 352                 use_subclassed (xDeviceCharacteristics);
 353                 #undef use_subclassed
 354         }
 355
 356         g_rec_mutex_unlock (&only_once_lock);
 357
 358         cFile->parent.pMethods = &io_methods;
 359
 360         return res;
 361 }
 362
 363 static gpointer
 364 init_sqlite_vfs (void)
 365 {
 366         static sqlite3_vfs vfs = { 0 };
 367
 368         old_vfs = sqlite3_vfs_find (NULL);
 369         g_return_val_if_fail (old_vfs != NULL, NULL);
 370
 371         memcpy (&vfs, old_vfs, sizeof (sqlite3_vfs));
 372
 373         vfs.szOsFile = sizeof (CamelSqlite3File);
 374         vfs.zName = "camel_sqlite3_vfs";
 375         vfs.xOpen = camel_sqlite3_vfs_xOpen;
 376
 377         sqlite3_vfs_register (&vfs, 1);
 378
 379         return NULL;
 380 }
 381
 382 #define d(x) if (camel_debug("sqlite")) x
 383 #define START(stmt)     if (camel_debug("dbtime")) { g_print ("\n===========\nDB SQL operation [%s] started\n", stmt); if (!cdb->priv->timer) { cdb->priv->timer = g_timer_new (); } else { g_timer_reset(cdb->priv->timer);} }
 384 #define END     if (camel_debug("dbtime")) { g_timer_stop (cdb->priv->timer); g_print ("DB Operation ended. Time Taken : %f\n###########\n", g_timer_elapsed (cdb->priv->timer, NULL)); }
 385 #define STARTTS(stmt)   if (camel_debug("dbtimets")) { g_print ("\n===========\nDB SQL operation [%s] started\n", stmt); if (!cdb->priv->timer) { cdb->priv->timer = g_timer_new (); } else { g_timer_reset(cdb->priv->timer);} }
 386 #define ENDTS   if (camel_debug("dbtimets")) { g_timer_stop (cdb->priv->timer); g_print ("DB Operation ended. Time Taken : %f\n###########\n", g_timer_elapsed (cdb->priv->timer, NULL)); }
 387
 388 struct _CamelDBPrivate {
 389         GTimer *timer;
 390         GRWLock rwlock;
 391         gchar *file_name;
 392         gboolean transaction_is_on;
 393 };
 394
 395 /**
 396  * cdb_sql_exec
 397  * @db:
 398  * @stmt:
 399  * @error:
 400  *
 401  * Callers should hold the lock
 402  **/
 403 static gint
 404 cdb_sql_exec (sqlite3 *db,
 405               const gchar *stmt,
 406               gint (*callback)(gpointer ,gint,gchar **,gchar **),
 407               gpointer data,
 408               GError **error)
 409 {
 410         gchar *errmsg = NULL;
 411         gint   ret = -1;
 412
 413         d (g_print ("Camel SQL Exec:\n%s\n", stmt));
 414
 415         ret = sqlite3_exec (db, stmt, callback, data, &errmsg);
 416         while (ret == SQLITE_BUSY || ret == SQLITE_LOCKED || ret == -1) {
 417                 if (errmsg) {
 418                         sqlite3_free (errmsg);
 419                         errmsg = NULL;
 420                 }
 421                 ret = sqlite3_exec (db, stmt, NULL, NULL, &errmsg);
 422         }
 423
 424         if (ret != SQLITE_OK) {
 425                 d (g_print ("Error in SQL EXEC statement: %s [%s].\n", stmt, errmsg));
 426                 g_set_error (
 427                         error, CAMEL_ERROR,
 428                         CAMEL_ERROR_GENERIC, "%s", errmsg);
 429                 sqlite3_free (errmsg);
 430                 errmsg = NULL;
 431                 return -1;
 432         }
 433
 434         if (errmsg) {
 435                 sqlite3_free (errmsg);
 436                 errmsg = NULL;
 437         }
 438
 439         return 0;
 440 }
 441
 442 /* checks whether string 'where' contains whole word 'what',
 443  * case insensitively (ascii, not utf8, same as 'LIKE' in SQLite3)
 444 */
 445 static void
 446 cdb_match_func (sqlite3_context *ctx,
 447                 gint nArgs,
 448                 sqlite3_value **values)
 449 {
 450         gboolean matches = FALSE;
 451         const gchar *what, *where;
 452
 453         g_return_if_fail (ctx != NULL);
 454         g_return_if_fail (nArgs == 2);
 455         g_return_if_fail (values != NULL);
 456
 457         what = (const gchar *) sqlite3_value_text (values[0]);
 458         where = (const gchar *) sqlite3_value_text (values[1]);
 459
 460         if (what && where && !*what) {
 461                 matches = TRUE;
 462         } else if (what && where) {
 463                 gboolean word = TRUE;
 464                 gint i, j;
 465
 466                 for (i = 0, j = 0; where[i] && !matches; i++) {
 467                         gchar c = where[i];
 468
 469                         if (c == ' ') {
 470                                 word = TRUE;
 471                                 j = 0;
 472                         } else if (word && tolower (c) == tolower (what[j])) {
 473                                 j++;
 474                                 if (what[j] == 0 && (where[i + 1] == 0 || isspace (where[i + 1])))
 475                                         matches = TRUE;
 476                         } else {
 477                                 word = FALSE;
 478                         }
 479                 }
 480         }
 481
 482         sqlite3_result_int (ctx, matches ? 1 : 0);
 483 }
 484
 485 /**
 486  * camel_db_open:
 487  *
 488  * Since: 2.24
 489  **/
 490 CamelDB *
 491 camel_db_open (const gchar *path,
 492                GError **error)
 493 {
 494         static GOnce vfs_once = G_ONCE_INIT;
 495         CamelDB *cdb;
 496         sqlite3 *db;
 497         gint ret;
 498
 499         g_once (&vfs_once, (GThreadFunc) init_sqlite_vfs, NULL);
 500
 501         CAMEL_DB_USE_SHARED_CACHE;
 502
 503         ret = sqlite3_open (path, &db);
 504         if (ret) {
 505
 506                 if (!db) {
 507                         g_set_error (
 508                                 error, CAMEL_ERROR,
 509                                 CAMEL_ERROR_GENERIC,
 510                                 _("Insufficient memory"));
 511                 } else {
 512                         const gchar *errmsg;
 513                         errmsg = sqlite3_errmsg (db);
 514                         d (g_print ("Can't open database %s: %s\n", path, errmsg));
 515                         g_set_error (
 516                                 error, CAMEL_ERROR,
 517                                 CAMEL_ERROR_GENERIC, "%s", errmsg);
 518                         sqlite3_close (db);
 519                 }
 520                 return NULL;
 521         }
 522
 523         cdb = g_new (CamelDB, 1);
 524         cdb->db = db;
 525         cdb->priv = g_new (CamelDBPrivate, 1);
 526         cdb->priv->file_name = g_strdup (path);
 527         g_rw_lock_init (&cdb->priv->rwlock);
 528         cdb->priv->timer = NULL;
 529         d (g_print ("\nDatabase succesfully opened  \n"));
 530
 531         sqlite3_create_function (db, "MATCH", 2, SQLITE_UTF8, NULL, cdb_match_func, NULL, NULL);
 532
 533         /* Which is big / costlier ? A Stack frame or a pointer */
 534         if (g_getenv ("CAMEL_SQLITE_DEFAULT_CACHE_SIZE") != NULL) {
 535                 gchar *cache = NULL;
 536
 537                 cache = g_strdup_printf ("PRAGMA cache_size=%s", g_getenv ("CAMEL_SQLITE_DEFAULT_CACHE_SIZE"));
 538                 camel_db_command (cdb, cache, NULL);
 539                 g_free (cache);
 540         }
 541
 542         camel_db_command (cdb, "ATTACH DATABASE ':memory:' AS mem", NULL);
 543
 544         if (g_getenv ("CAMEL_SQLITE_IN_MEMORY") != NULL) {
 545                 /* Optionally turn off Journaling, this gets over fsync issues, but could be risky */
 546                 camel_db_command (cdb, "PRAGMA main.journal_mode = off", NULL);
 547                 camel_db_command (cdb, "PRAGMA temp_store = memory", NULL);
 548         }
 549
 550         sqlite3_busy_timeout (cdb->db, CAMEL_DB_SLEEP_INTERVAL);
 551
 552         return cdb;
 553 }
 554
 555 /**
 556  * camel_db_clone:
 557  *
 558  * Since: 2.26
 559  **/
 560 CamelDB *
 561 camel_db_clone (CamelDB *cdb,
 562                 GError **error)
 563 {
 564         return camel_db_open (cdb->priv->file_name, error);
 565 }
 566
 567 /**
 568  * camel_db_close:
 569  *
 570  * Since: 2.24
 571  **/
 572 void
 573 camel_db_close (CamelDB *cdb)
 574 {
 575         if (cdb) {
 576                 sqlite3_close (cdb->db);
 577                 g_rw_lock_clear (&cdb->priv->rwlock);
 578                 g_free (cdb->priv->file_name);
 579                 g_free (cdb->priv);
 580                 g_free (cdb);
 581                 d (g_print ("\nDatabase succesfully closed \n"));
 582         }
 583 }
 584
 585 /**
 586  * camel_db_set_collate:
 587  *
 588  * Since: 2.24
 589  **/
 590 gint
 591 camel_db_set_collate (CamelDB *cdb,
 592                       const gchar *col,
 593                       const gchar *collate,
 594                       CamelDBCollate func)
 595 {
 596                 gint ret = 0;
 597
 598                 if (!cdb)
 599                         return 0;
 600
 601                 WRITER_LOCK (cdb);
 602                 d (g_print ("Creating Collation %s on %s with %p\n", collate, col, (gpointer) func));
 603                 if (collate && func)
 604                         ret = sqlite3_create_collation (cdb->db, collate, SQLITE_UTF8,  NULL, func);
 605                 WRITER_UNLOCK (cdb);
 606
 607                 return ret;
 608 }
 609
 610 /**
 611  * camel_db_command:
 612  *
 613  * Since: 2.24
 614  **/
 615 gint
 616 camel_db_command (CamelDB *cdb,
 617                   const gchar *stmt,
 618                   GError **error)
 619 {
 620         gint ret;
 621
 622         if (!cdb)
 623                 return TRUE;
 624
 625         WRITER_LOCK (cdb);
 626
 627         START (stmt);
 628         ret = cdb_sql_exec (cdb->db, stmt, NULL, NULL, error);
 629         END;
 630
 631         WRITER_UNLOCK (cdb);
 632
 633         return ret;
 634 }
 635
 636 /**
 637  * camel_db_begin_transaction:
 638  *
 639  * Since: 2.24
 640  **/
 641 gint
 642 camel_db_begin_transaction (CamelDB *cdb,
 643                             GError **error)
 644 {
 645         if (!cdb)
 646                 return -1;
 647
 648         WRITER_LOCK (cdb);
 649         STARTTS ("BEGIN");
 650
 651         cdb->priv->transaction_is_on = TRUE;
 652
 653         return (cdb_sql_exec (cdb->db, "BEGIN", NULL, NULL, error));
 654 }
 655
 656 /**
 657  * camel_db_end_transaction:
 658  *
 659  * Since: 2.24
 660  **/
 661 gint
 662 camel_db_end_transaction (CamelDB *cdb,
 663                           GError **error)
 664 {
 665         gint ret;
 666         if (!cdb)
 667                 return -1;
 668
 669         ret = cdb_sql_exec (cdb->db, "COMMIT", NULL, NULL, error);
 670         cdb->priv->transaction_is_on = FALSE;
 671
 672         ENDTS;
 673         WRITER_UNLOCK (cdb);
 674         CAMEL_DB_RELEASE_SQLITE_MEMORY;
 675
 676         return ret;
 677 }
 678
 679 /**
 680  * camel_db_abort_transaction:
 681  *
 682  * Since: 2.24
 683  **/
 684 gint
 685 camel_db_abort_transaction (CamelDB *cdb,
 686                             GError **error)
 687 {
 688         gint ret;
 689
 690         ret = cdb_sql_exec (cdb->db, "ROLLBACK", NULL, NULL, error);
 691         cdb->priv->transaction_is_on = FALSE;
 692
 693         WRITER_UNLOCK (cdb);
 694         CAMEL_DB_RELEASE_SQLITE_MEMORY;
 695
 696         return ret;
 697 }
 698
 699 /**
 700  * camel_db_add_to_transaction:
 701  *
 702  * Since: 2.24
 703  **/
 704 gint
 705 camel_db_add_to_transaction (CamelDB *cdb,
 706                              const gchar *stmt,
 707                              GError **error)
 708 {
 709         if (!cdb)
 710                 return -1;
 711
 712         g_assert (cdb->priv->transaction_is_on == TRUE);
 713
 714         return (cdb_sql_exec (cdb->db, stmt, NULL, NULL, error));
 715 }
 716
 717 /**
 718  * camel_db_transaction_command:
 719  *
 720  * Since: 2.24
 721  **/
 722 gint
 723 camel_db_transaction_command (CamelDB *cdb,
 724                               GList *qry_list,
 725                               GError **error)
 726 {
 727         gint ret;
 728         const gchar *query;
 729
 730         if (!cdb)
 731                 return -1;
 732
 733         WRITER_LOCK (cdb);
 734
 735         STARTTS ("BEGIN");
 736         ret = cdb_sql_exec (cdb->db, "BEGIN", NULL, NULL, error);
 737         if (ret)
 738                 goto end;
 739
 740         while (qry_list) {
 741                 query = qry_list->data;
 742                 ret = cdb_sql_exec (cdb->db, query, NULL, NULL, error);
 743                 if (ret)
 744                         goto end;
 745                 qry_list = g_list_next (qry_list);
 746         }
 747
 748         ret = cdb_sql_exec (cdb->db, "COMMIT", NULL, NULL, error);
 749         ENDTS;
 750 end:
 751         WRITER_UNLOCK (cdb);
 752         return ret;
 753 }
 754
 755 static gint
 756 count_cb (gpointer data,
 757           gint argc,
 758           gchar **argv,
 759           gchar **azColName)
 760 {
 761         gint i;
 762
 763         for (i = 0; i < argc; i++) {
 764                 if (strstr (azColName[i], "COUNT")) {
 765                         *(guint32 *)data = argv [i] ? strtoul (argv [i], NULL, 10) : 0;
 766                 }
 767         }
 768
 769         return 0;
 770 }
 771
 772 /**
 773  * camel_db_count_message_info:
 774  *
 775  * Since: 2.26
 776  **/
 777 gint
 778 camel_db_count_message_info (CamelDB *cdb,
 779                              const gchar *query,
 780                              guint32 *count,
 781                              GError **error)
 782 {
 783         gint ret = -1;
 784
 785         READER_LOCK (cdb);
 786
 787         START (query);
 788         ret = cdb_sql_exec (cdb->db, query, count_cb, count, error);
 789         END;
 790
 791         READER_UNLOCK (cdb);
 792
 793         CAMEL_DB_RELEASE_SQLITE_MEMORY;
 794
 795         return ret;
 796 }
 797
 798 /**
 799  * camel_db_count_junk_message_info:
 800  *
 801  * Since: 2.24
 802  **/
 803 gint
 804 camel_db_count_junk_message_info (CamelDB *cdb,
 805                                   const gchar *table_name,
 806                                   guint32 *count,
 807                                   GError **error)
 808 {
 809         gint ret;
 810         gchar *query;
 811
 812         if (!cdb)
 813                 return -1;
 814
 815         query = sqlite3_mprintf ("SELECT COUNT (*) FROM %Q WHERE junk = 1", table_name);
 816
 817         ret = camel_db_count_message_info (cdb, query, count, error);
 818         sqlite3_free (query);
 819
 820         return ret;
 821 }
 822
 823 /**
 824  * camel_db_count_unread_message_info:
 825  *
 826  * Since: 2.24
 827  **/
 828 gint
 829 camel_db_count_unread_message_info (CamelDB *cdb,
 830                                     const gchar *table_name,
 831                                     guint32 *count,
 832                                     GError **error)
 833 {
 834         gint ret;
 835         gchar *query;
 836
 837         if (!cdb)
 838                 return -1;
 839
 840         query = sqlite3_mprintf ("SELECT COUNT (*) FROM %Q WHERE read = 0", table_name);
 841
 842         ret = camel_db_count_message_info (cdb, query, count, error);
 843         sqlite3_free (query);
 844
 845         return ret;
 846 }
 847
 848 /**
 849  * camel_db_count_visible_unread_message_info:
 850  *
 851  * Since: 2.24
 852  **/
 853 gint
 854 camel_db_count_visible_unread_message_info (CamelDB *cdb,
 855                                             const gchar *table_name,
 856                                             guint32 *count,
 857                                             GError **error)
 858 {
 859         gint ret;
 860         gchar *query;
 861
 862         if (!cdb)
 863                 return -1;
 864
 865         query = sqlite3_mprintf ("SELECT COUNT (*) FROM %Q WHERE read = 0 AND junk = 0 AND deleted = 0", table_name);
 866
 867         ret = camel_db_count_message_info (cdb, query, count, error);
 868         sqlite3_free (query);
 869
 870         return ret;
 871 }
 872
 873 /**
 874  * camel_db_count_visible_message_info:
 875  *
 876  * Since: 2.24
 877  **/
 878 gint
 879 camel_db_count_visible_message_info (CamelDB *cdb,
 880                                      const gchar *table_name,
 881                                      guint32 *count,
 882                                      GError **error)
 883 {
 884         gint ret;
 885         gchar *query;
 886
 887         if (!cdb)
 888                 return -1;
 889
 890         query = sqlite3_mprintf ("SELECT COUNT (*) FROM %Q WHERE junk = 0 AND deleted = 0", table_name);
 891
 892         ret = camel_db_count_message_info (cdb, query, count, error);
 893         sqlite3_free (query);
 894
 895         return ret;
 896 }
 897
 898 /**
 899  * camel_db_count_junk_not-deleted_message_info:
 900  *
 901  * Since: 2.24
 902  **/
 903 gint
 904 camel_db_count_junk_not_deleted_message_info (CamelDB *cdb,
 905                                               const gchar *table_name,
 906                                               guint32 *count,
 907                                               GError **error)
 908 {
 909         gint ret;
 910         gchar *query;
 911
 912         if (!cdb)
 913                 return -1;
 914
 915         query = sqlite3_mprintf ("SELECT COUNT (*) FROM %Q WHERE junk = 1 AND deleted = 0", table_name);
 916
 917         ret = camel_db_count_message_info (cdb, query, count, error);
 918         sqlite3_free (query);
 919
 920         return ret;
 921 }
 922
 923 /**
 924  * camel_db_count_deleted_message_info:
 925  *
 926  * Since: 2.24
 927  **/
 928 gint
 929 camel_db_count_deleted_message_info (CamelDB *cdb,
 930                                      const gchar *table_name,
 931                                      guint32 *count,
 932                                      GError **error)
 933 {
 934         gint ret;
 935         gchar *query;
 936
 937         if (!cdb)
 938                 return -1;
 939
 940         query = sqlite3_mprintf ("SELECT COUNT (*) FROM %Q WHERE deleted = 1", table_name);
 941
 942         ret = camel_db_count_message_info (cdb, query, count, error);
 943         sqlite3_free (query);
 944
 945         return ret;
 946 }
 947
 948 /**
 949  * camel_db_count_total_message_info:
 950  *
 951  * Since: 2.24
 952  **/
 953 gint
 954 camel_db_count_total_message_info (CamelDB *cdb,
 955                                    const gchar *table_name,
 956                                    guint32 *count,
 957                                    GError **error)
 958 {
 959
 960         gint ret;
 961         gchar *query;
 962
 963         if (!cdb)
 964                 return -1;
 965
 966         query = sqlite3_mprintf ("SELECT COUNT (*) FROM %Q where read=0 or read=1", table_name);
 967
 968         ret = camel_db_count_message_info (cdb, query, count, error);
 969         sqlite3_free (query);
 970
 971         return ret;
 972 }
 973
 974 /**
 975  * camel_db_select:
 976  *
 977  * Since: 2.24
 978  **/
 979 gint
 980 camel_db_select (CamelDB *cdb,
 981                  const gchar *stmt,
 982                  CamelDBSelectCB callback,
 983                  gpointer data,
 984                  GError **error)
 985 {
 986         gint ret = -1;
 987
 988         if (!cdb)
 989                 return ret;
 990
 991         d (g_print ("\n%s:\n%s \n", G_STRFUNC, stmt));
 992         READER_LOCK (cdb);
 993
 994         START (stmt);
 995         ret = cdb_sql_exec (cdb->db, stmt, callback, data, error);
 996         END;
 997
 998         READER_UNLOCK (cdb);
 999         CAMEL_DB_RELEASE_SQLITE_MEMORY;
1000
1001         return ret;
1002 }
1003
1004 static gint
1005 read_uids_callback (gpointer ref_array,
1006                     gint ncol,
1007                     gchar **cols,
1008                     gchar **name)
1009 {
1010         GPtrArray *array = ref_array;
1011
1012         g_return_val_if_fail (ncol == 1, 0);
1013
1014         if (cols[0])
1015                 g_ptr_array_add (array, (gchar *) (camel_pstring_strdup (cols[0])));
1016
1017         return 0;
1018 }
1019
1020 static gint
1021 read_uids_to_hash_callback (gpointer ref_hash,
1022                             gint ncol,
1023                             gchar **cols,
1024                             gchar **name)
1025 {
1026         GHashTable *hash = ref_hash;
1027
1028         g_return_val_if_fail (ncol == 2, 0);
1029
1030         if (cols[0])
1031                 g_hash_table_insert (hash, (gchar *) camel_pstring_strdup (cols[0]), GUINT_TO_POINTER (cols[1] ? strtoul (cols[1], NULL, 10) : 0));
1032
1033         return 0;
1034 }
1035
1036 /**
1037  * camel_db_get_folder_uids:
1038  *
1039  * Fills hash with uid->GUINT_TO_POINTER (flag)
1040  *
1041  * Since: 2.24
1042  **/
1043 gint
1044 camel_db_get_folder_uids (CamelDB *db,
1045                           const gchar *folder_name,
1046                           const gchar *sort_by,
1047                           const gchar *collate,
1048                           GHashTable *hash,
1049                           GError **error)
1050 {
1051          gchar *sel_query;
1052          gint ret;
1053
1054          sel_query = sqlite3_mprintf ("SELECT uid,flags FROM %Q%s%s%s%s", folder_name, sort_by ? " order by " : "", sort_by ? sort_by: "", (sort_by && collate) ? " collate " : "", (sort_by && collate) ? collate : "");
1055
1056          ret = camel_db_select (db, sel_query, read_uids_to_hash_callback, hash, error);
1057          sqlite3_free (sel_query);
1058
1059          return ret;
1060 }
1061
1062 /**
1063  * camel_db_get_folder_junk_uids:
1064  *
1065  * Since: 2.24
1066  **/
1067 GPtrArray *
1068 camel_db_get_folder_junk_uids (CamelDB *db,
1069                                gchar *folder_name,
1070                                GError **error)
1071 {
1072          gchar *sel_query;
1073          gint ret;
1074          GPtrArray *array = g_ptr_array_new ();
1075
1076          sel_query = sqlite3_mprintf ("SELECT uid FROM %Q where junk=1", folder_name);
1077
1078          ret = camel_db_select (db, sel_query, read_uids_callback, array, error);
1079
1080          sqlite3_free (sel_query);
1081
1082          if (!array->len || ret != 0) {
1083                  g_ptr_array_free (array, TRUE);
1084                  array = NULL;
1085          }
1086          return array;
1087 }
1088
1089 /**
1090  * camel_db_get_folder_deleted_uids:
1091  *
1092  * Since: 2.24
1093  **/
1094 GPtrArray *
1095 camel_db_get_folder_deleted_uids (CamelDB *db,
1096                                   const gchar *folder_name,
1097                                   GError **error)
1098 {
1099          gchar *sel_query;
1100          gint ret;
1101          GPtrArray *array = g_ptr_array_new ();
1102
1103          sel_query = sqlite3_mprintf ("SELECT uid FROM %Q where deleted=1", folder_name);
1104
1105          ret = camel_db_select (db, sel_query, read_uids_callback, array, error);
1106          sqlite3_free (sel_query);
1107
1108          if (!array->len || ret != 0) {
1109                  g_ptr_array_free (array, TRUE);
1110                  array = NULL;
1111          }
1112
1113          return array;
1114 }
1115
1116 struct ReadPreviewData
1117 {
1118         GHashTable *columns_hash;
1119         GHashTable *hash;
1120 };
1121
1122 static gint
1123 read_preview_callback (gpointer ref,
1124                        gint ncol,
1125                        gchar **cols,
1126                        gchar **name)
1127 {
1128         struct ReadPreviewData *rpd = ref;
1129         const gchar *uid = NULL;
1130         gchar *msg = NULL;
1131         gint i;
1132
1133         for (i = 0; i < ncol; ++i) {
1134                 if (!name[i] || !cols[i])
1135                         continue;
1136
1137                 switch (camel_db_get_column_ident (&rpd->columns_hash, i, ncol, name)) {
1138                         case CAMEL_DB_COLUMN_UID:
1139                                 uid = camel_pstring_strdup (cols[i]);
1140                                 break;
1141                         case CAMEL_DB_COLUMN_PREVIEW:
1142                                 msg = g_strdup (cols[i]);
1143                                 break;
1144                         default:
1145                                 g_warn_if_reached ();
1146                                 break;
1147                 }
1148         }
1149
1150         g_hash_table_insert (rpd->hash, (gchar *) uid, msg);
1151
1152         return 0;
1153 }
1154
1155 /**
1156  * camel_db_get_folder_preview:
1157  *
1158  * Since: 2.28
1159  **/
1160 GHashTable *
1161 camel_db_get_folder_preview (CamelDB *db,
1162                              const gchar *folder_name,
1163                              GError **error)
1164 {
1165         gchar *sel_query;
1166         gint ret;
1167         struct ReadPreviewData rpd;
1168         GHashTable *hash = g_hash_table_new (g_str_hash, g_str_equal);
1169
1170         sel_query = sqlite3_mprintf ("SELECT uid, preview FROM '%q_preview'", folder_name);
1171
1172         rpd.columns_hash = NULL;
1173         rpd.hash = hash;
1174
1175         ret = camel_db_select (db, sel_query, read_preview_callback, &rpd, error);
1176         sqlite3_free (sel_query);
1177
1178         if (rpd.columns_hash)
1179                 g_hash_table_destroy (rpd.columns_hash);
1180
1181         if (!g_hash_table_size (hash) || ret != 0) {
1182                 g_hash_table_destroy (hash);
1183                 hash = NULL;
1184         }
1185
1186         return hash;
1187 }
1188
1189 /**
1190  * camel_db_write_preview_record:
1191  *
1192  * Since: 2.28
1193  **/
1194 gint
1195 camel_db_write_preview_record (CamelDB *db,
1196                                const gchar *folder_name,
1197                                const gchar *uid,
1198                                const gchar *msg,
1199                                GError **error)
1200 {
1201         gchar *query;
1202         gint ret;
1203
1204         query = sqlite3_mprintf ("INSERT OR REPLACE INTO '%q_preview' VALUES(%Q,%Q)", folder_name, uid, msg);
1205
1206         ret = camel_db_add_to_transaction (db, query, error);
1207         sqlite3_free (query);
1208
1209         return ret;
1210 }
1211
1212 /**
1213  * camel_db_create_folders_table:
1214  *
1215  * Since: 2.24
1216  **/
1217 gint
1218 camel_db_create_folders_table (CamelDB *cdb,
1219                                GError **error)
1220 {
1221         const gchar *query = "CREATE TABLE IF NOT EXISTS folders ( folder_name TEXT PRIMARY KEY, version REAL, flags INTEGER, nextuid INTEGER, time NUMERIC, saved_count INTEGER, unread_count INTEGER, deleted_count INTEGER, junk_count INTEGER, visible_count INTEGER, jnd_count INTEGER, bdata TEXT )";
1222         CAMEL_DB_RELEASE_SQLITE_MEMORY;
1223         return ((camel_db_command (cdb, query, error)));
1224 }
1225
1226 static gint
1227 camel_db_create_message_info_table (CamelDB *cdb,
1228                                     const gchar *folder_name,
1229                                     GError **error)
1230 {
1231         gint ret;
1232         gchar *table_creation_query, *safe_index;
1233
1234         /* 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. */
1235         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 , dirty 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);
1236         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1237         sqlite3_free (table_creation_query);
1238
1239         table_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS '%q_bodystructure' (  uid TEXT PRIMARY KEY , bodystructure TEXT )", folder_name);
1240         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1241         sqlite3_free (table_creation_query);
1242
1243         /* Create message preview table. */
1244         table_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS '%q_preview' (  uid TEXT PRIMARY KEY , preview TEXT)", folder_name);
1245         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1246         sqlite3_free (table_creation_query);
1247
1248         /* FIXME: sqlize folder_name before you create the index */
1249         safe_index = g_strdup_printf ("SINDEX-%s", folder_name);
1250         table_creation_query = sqlite3_mprintf ("DROP INDEX IF EXISTS %Q", safe_index);
1251         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1252         g_free (safe_index);
1253         sqlite3_free (table_creation_query);
1254
1255         /* INDEX on preview */
1256         safe_index = g_strdup_printf ("SINDEX-%s-preview", folder_name);
1257         table_creation_query = sqlite3_mprintf ("CREATE INDEX IF NOT EXISTS %Q ON '%q_preview' (uid, preview)", safe_index, folder_name);
1258         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1259         g_free (safe_index);
1260         sqlite3_free (table_creation_query);
1261
1262         /* Index on deleted*/
1263         safe_index = g_strdup_printf ("DELINDEX-%s", folder_name);
1264         table_creation_query = sqlite3_mprintf ("CREATE INDEX IF NOT EXISTS %Q ON %Q (deleted)", safe_index, folder_name);
1265         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1266         g_free (safe_index);
1267         sqlite3_free (table_creation_query);
1268
1269         /* Index on Junk*/
1270         safe_index = g_strdup_printf ("JUNKINDEX-%s", folder_name);
1271         table_creation_query = sqlite3_mprintf ("CREATE INDEX IF NOT EXISTS %Q ON %Q (junk)", safe_index, folder_name);
1272         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1273         g_free (safe_index);
1274         sqlite3_free (table_creation_query);
1275
1276         /* Index on unread*/
1277         safe_index = g_strdup_printf ("READINDEX-%s", folder_name);
1278         table_creation_query = sqlite3_mprintf ("CREATE INDEX IF NOT EXISTS %Q ON %Q (read)", safe_index, folder_name);
1279         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1280         g_free (safe_index);
1281         sqlite3_free (table_creation_query);
1282
1283         return ret;
1284 }
1285
1286 static gint
1287 camel_db_migrate_folder_prepare (CamelDB *cdb,
1288                                  const gchar *folder_name,
1289                                  gint version,
1290                                  GError **error)
1291 {
1292         gint ret = 0;
1293         gchar *table_creation_query;
1294
1295         /* Migration stage one: storing the old data */
1296
1297         if (version < 1) {
1298
1299                 /* Between version 0-1 the following things are changed
1300                  * ADDED: created: time
1301                  * ADDED: modified: time
1302                  * RENAMED: msg_security to dirty
1303                  * */
1304
1305                 table_creation_query = sqlite3_mprintf ("DROP TABLE IF EXISTS 'mem.%q'", folder_name);
1306                 ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1307                 sqlite3_free (table_creation_query);
1308
1309                 table_creation_query = sqlite3_mprintf ("CREATE TEMP TABLE IF NOT EXISTS 'mem.%q' (  uid TEXT PRIMARY KEY , flags INTEGER , msg_type INTEGER , read INTEGER , deleted INTEGER , replied INTEGER , important INTEGER , junk INTEGER , attachment INTEGER , dirty 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);
1310                 ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1311                 sqlite3_free (table_creation_query);
1312
1313                 table_creation_query = sqlite3_mprintf ("INSERT INTO 'mem.%q' SELECT uid , flags , msg_type , read , deleted , replied , important , junk , attachment , dirty , 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);
1314                 ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1315                 sqlite3_free (table_creation_query);
1316
1317                 table_creation_query = sqlite3_mprintf ("DROP TABLE IF EXISTS %Q", folder_name);
1318                 ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1319                 sqlite3_free (table_creation_query);
1320
1321                 ret = camel_db_create_message_info_table (cdb, folder_name, error);
1322                 g_clear_error (error);
1323         }
1324
1325         /* Add later version migrations here */
1326
1327         return ret;
1328 }
1329
1330 static gint
1331 camel_db_migrate_folder_recreate (CamelDB *cdb,
1332                                   const gchar *folder_name,
1333                                   gint version,
1334                                   GError **error)
1335 {
1336         gint ret = 0;
1337         gchar *table_creation_query;
1338
1339         /* Migration stage two: writing back the old data */
1340
1341         if (version < 2) {
1342                 GError *local_error = NULL;
1343
1344                 table_creation_query = sqlite3_mprintf ("INSERT INTO %Q SELECT uid , flags , msg_type , read , deleted , replied , important , junk , attachment , dirty , 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 'mem.%q'", folder_name, folder_name);
1345                 ret = camel_db_add_to_transaction (cdb, table_creation_query, &local_error);
1346                 sqlite3_free (table_creation_query);
1347
1348                 if (!local_error) {
1349                         table_creation_query = sqlite3_mprintf ("DROP TABLE 'mem.%q'", folder_name);
1350                         ret = camel_db_add_to_transaction (cdb, table_creation_query, &local_error);
1351                         sqlite3_free (table_creation_query);
1352                 }
1353
1354                 if (local_error) {
1355                         if (local_error->message && strstr (local_error->message, "no such table") != NULL) {
1356                                 /* ignore 'no such table' errors here */
1357                                 g_clear_error (&local_error);
1358                                 ret = 0;
1359                         } else {
1360                                 g_propagate_error (error, local_error);
1361                         }
1362                 }
1363         }
1364
1365         /* Add later version migrations here */
1366
1367         return ret;
1368 }
1369
1370 /**
1371  * camel_db_reset_folder_version:
1372  *
1373  * Since: 2.28
1374  **/
1375 gint
1376 camel_db_reset_folder_version (CamelDB *cdb,
1377                                const gchar *folder_name,
1378                                gint reset_version,
1379                                GError **error)
1380 {
1381         gint ret = 0;
1382         gchar *version_creation_query;
1383         gchar *version_insert_query;
1384         gchar *drop_folder_query;
1385
1386         drop_folder_query = sqlite3_mprintf ("DROP TABLE IF EXISTS '%q_version'", folder_name);
1387         version_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS '%q_version' ( version TEXT )", folder_name);
1388
1389         version_insert_query = sqlite3_mprintf ("INSERT INTO '%q_version' VALUES ('%d')", folder_name, reset_version);
1390
1391         ret = camel_db_add_to_transaction (cdb, drop_folder_query, error);
1392         ret = camel_db_add_to_transaction (cdb, version_creation_query, error);
1393         ret = camel_db_add_to_transaction (cdb, version_insert_query, error);
1394
1395         sqlite3_free (drop_folder_query);
1396         sqlite3_free (version_creation_query);
1397         sqlite3_free (version_insert_query);
1398
1399         return ret;
1400 }
1401
1402 static gint
1403 camel_db_write_folder_version (CamelDB *cdb,
1404                                const gchar *folder_name,
1405                                gint old_version,
1406                                GError **error)
1407 {
1408         gint ret = 0;
1409         gchar *version_creation_query;
1410         gchar *version_insert_query;
1411
1412         version_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS '%q_version' ( version TEXT )", folder_name);
1413
1414         if (old_version == -1)
1415                 version_insert_query = sqlite3_mprintf ("INSERT INTO '%q_version' VALUES ('2')", folder_name);
1416         else
1417                 version_insert_query = sqlite3_mprintf ("UPDATE '%q_version' SET version='2'", folder_name);
1418
1419         ret = camel_db_add_to_transaction (cdb, version_creation_query, error);
1420         ret = camel_db_add_to_transaction (cdb, version_insert_query, error);
1421
1422         sqlite3_free (version_creation_query);
1423         sqlite3_free (version_insert_query);
1424
1425         return ret;
1426 }
1427
1428 static gint
1429 read_version_callback (gpointer ref,
1430                        gint ncol,
1431                        gchar **cols,
1432                        gchar **name)
1433 {
1434         gint *version = (gint *) ref;
1435
1436         if (cols[0])
1437                 *version = strtoul (cols [0], NULL, 10);
1438
1439         return 0;
1440 }
1441
1442 static gint
1443 camel_db_get_folder_version (CamelDB *cdb,
1444                              const gchar *folder_name,
1445                              GError **error)
1446 {
1447         gint version = -1;
1448         gchar *query;
1449
1450         query = sqlite3_mprintf ("SELECT version FROM '%q_version'", folder_name);
1451         camel_db_select (cdb, query, read_version_callback, &version, error);
1452         sqlite3_free (query);
1453
1454         return version;
1455 }
1456
1457 /**
1458  * camel_db_prepare_message_info_table:
1459  *
1460  * Since: 2.24
1461  **/
1462 gint
1463 camel_db_prepare_message_info_table (CamelDB *cdb,
1464                                      const gchar *folder_name,
1465                                      GError **error)
1466 {
1467         gint ret, current_version;
1468         GError *err = NULL;
1469
1470         /* Make sure we have the table already */
1471         camel_db_begin_transaction (cdb, &err);
1472         ret = camel_db_create_message_info_table (cdb, folder_name, &err);
1473         if (err)
1474                 goto exit;
1475
1476         camel_db_end_transaction (cdb, &err);
1477
1478         /* Migration stage zero: version fetch */
1479         current_version = camel_db_get_folder_version (cdb, folder_name, &err);
1480
1481         camel_db_begin_transaction (cdb, &err);
1482
1483         /* Migration stage one: storing the old data if necessary */
1484         ret = camel_db_migrate_folder_prepare (cdb, folder_name, current_version, &err);
1485         if (err)
1486                 goto exit;
1487
1488         /* Migration stage two: rewriting the old data if necessary */
1489         ret = camel_db_migrate_folder_recreate (cdb, folder_name, current_version, &err);
1490         if (err)
1491                 goto exit;
1492
1493         /* Final step: (over)write the current version label */
1494         ret = camel_db_write_folder_version (cdb, folder_name, current_version, &err);
1495         if (err)
1496                 goto exit;
1497
1498         camel_db_end_transaction (cdb, &err);
1499
1500 exit:
1501         if (err && cdb->priv->transaction_is_on)
1502                 camel_db_abort_transaction (cdb, NULL);
1503
1504         if (err)
1505                 g_propagate_error (error, err);
1506
1507         return ret;
1508 }
1509
1510 static gint
1511 write_mir (CamelDB *cdb,
1512            const gchar *folder_name,
1513            CamelMIRecord *record,
1514            GError **error,
1515            gboolean delete_old_record)
1516 {
1517         gint ret;
1518         /*char *del_query;*/
1519         gchar *ins_query;
1520
1521         /* FIXME: We should migrate from this DELETE followed by INSERT model to an INSERT OR REPLACE model as pointed out by pvanhoof */
1522
1523         /* NB: UGLIEST Hack. We can't modify the schema now. We are using dirty (an unsed one to notify of FLAGGED/Dirty infos */
1524
1525         ins_query = sqlite3_mprintf (
1526                 "INSERT OR REPLACE INTO %Q VALUES ("
1527                 "%Q, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, "
1528                 "%lld, %lld, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, "
1529                 "%Q, %Q, %Q, %Q, %Q, "
1530                 "strftime(\"%%s\", 'now'), "
1531                 "strftime(\"%%s\", 'now') )",
1532                 folder_name,
1533                 record->uid,
1534                 record->flags,
1535                 record->msg_type,
1536                 record->read,
1537                 record->deleted,
1538                 record->replied,
1539                 record->important,
1540                 record->junk,
1541                 record->attachment,
1542                 record->dirty,
1543                 record->size,
1544                 (gint64) record->dsent,
1545                 (gint64) record->dreceived,
1546                 record->subject,
1547                 record->from,
1548                 record->to,
1549                 record->cc,
1550                 record->mlist,
1551                 record->followup_flag,
1552                 record->followup_completed_on,
1553                 record->followup_due_by,
1554                 record->part,
1555                 record->labels,
1556                 record->usertags,
1557                 record->cinfo,
1558                 record->bdata);
1559
1560         ret = camel_db_add_to_transaction (cdb, ins_query, error);
1561
1562         sqlite3_free (ins_query);
1563
1564         if (ret == 0) {
1565                 ins_query = sqlite3_mprintf (
1566                         "INSERT OR REPLACE INTO "
1567                         "'%q_bodystructure' VALUES (%Q, %Q )",
1568                         folder_name, record->uid, record->bodystructure);
1569                 ret = camel_db_add_to_transaction (cdb, ins_query, error);
1570                 sqlite3_free (ins_query);
1571         }
1572
1573         return ret;
1574 }
1575
1576 /**
1577  * camel_db_write_fresh_message_info_record:
1578  *
1579  * Since: 2.26
1580  **/
1581 gint
1582 camel_db_write_fresh_message_info_record (CamelDB *cdb,
1583                                           const gchar *folder_name,
1584                                           CamelMIRecord *record,
1585                                           GError **error)
1586 {
1587         return write_mir (cdb, folder_name, record, error, FALSE);
1588 }
1589
1590 /**
1591  * camel_db_write_message_info_record:
1592  *
1593  * Since: 2.24
1594  **/
1595 gint
1596 camel_db_write_message_info_record (CamelDB *cdb,
1597                                     const gchar *folder_name,
1598                                     CamelMIRecord *record,
1599                                     GError **error)
1600 {
1601         return write_mir (cdb, folder_name, record, error, TRUE);
1602 }
1603
1604 /**
1605  * camel_db_write_folder_info_record:
1606  *
1607  * Since: 2.24
1608  **/
1609 gint
1610 camel_db_write_folder_info_record (CamelDB *cdb,
1611                                    CamelFIRecord *record,
1612                                    GError **error)
1613 {
1614         gint ret;
1615
1616         gchar *del_query;
1617         gchar *ins_query;
1618
1619         ins_query = sqlite3_mprintf (
1620                 "INSERT INTO folders VALUES ("
1621                 "%Q, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %Q ) ",
1622                 record->folder_name,
1623                 record->version,
1624                 record->flags,
1625                 record->nextuid,
1626                 record->time,
1627                 record->saved_count,
1628                 record->unread_count,
1629                 record->deleted_count,
1630                 record->junk_count,
1631                 record->visible_count,
1632                 record->jnd_count,
1633                 record->bdata);
1634
1635         del_query = sqlite3_mprintf (
1636                 "DELETE FROM folders WHERE folder_name = %Q",
1637                 record->folder_name);
1638
1639         ret = camel_db_add_to_transaction (cdb, del_query, error);
1640         ret = camel_db_add_to_transaction (cdb, ins_query, error);
1641
1642         sqlite3_free (del_query);
1643         sqlite3_free (ins_query);
1644
1645         return ret;
1646 }
1647
1648 struct ReadFirData {
1649         GHashTable *columns_hash;
1650         CamelFIRecord *record;
1651 };
1652
1653 static gint
1654 read_fir_callback (gpointer ref,
1655                    gint ncol,
1656                    gchar **cols,
1657                    gchar **name)
1658 {
1659         struct ReadFirData *rfd = ref;
1660         gint i;
1661
1662         d (g_print ("\nread_fir_callback called \n"));
1663
1664         for (i = 0; i < ncol; ++i) {
1665                 if (!name[i] || !cols[i])
1666                         continue;
1667
1668                 switch (camel_db_get_column_ident (&rfd->columns_hash, i, ncol, name)) {
1669                         case CAMEL_DB_COLUMN_FOLDER_NAME:
1670                                 rfd->record->folder_name = g_strdup (cols[i]);
1671                                 break;
1672                         case CAMEL_DB_COLUMN_VERSION:
1673                                 rfd->record->version = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1674                                 break;
1675                         case CAMEL_DB_COLUMN_FLAGS:
1676                                 rfd->record->flags = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1677                                 break;
1678                         case CAMEL_DB_COLUMN_NEXTUID:
1679                                 rfd->record->nextuid = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1680                                 break;
1681                         case CAMEL_DB_COLUMN_TIME:
1682                                 rfd->record->time = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1683                                 break;
1684                         case CAMEL_DB_COLUMN_SAVED_COUNT:
1685                                 rfd->record->saved_count = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1686                                 break;
1687                         case CAMEL_DB_COLUMN_UNREAD_COUNT:
1688                                 rfd->record->unread_count = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1689                                 break;
1690                         case CAMEL_DB_COLUMN_DELETED_COUNT:
1691                                 rfd->record->deleted_count = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1692                                 break;
1693                         case CAMEL_DB_COLUMN_JUNK_COUNT:
1694                                 rfd->record->junk_count = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1695                                 break;
1696                         case CAMEL_DB_COLUMN_VISIBLE_COUNT:
1697                                 rfd->record->visible_count = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1698                                 break;
1699                         case CAMEL_DB_COLUMN_JND_COUNT:
1700                                 rfd->record->jnd_count = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
1701                                 break;
1702                         case CAMEL_DB_COLUMN_BDATA:
1703                                 rfd->record->bdata = g_strdup (cols[i]);
1704                                 break;
1705                         default:
1706                                 g_warn_if_reached ();
1707                                 break;
1708                 }
1709         }
1710
1711         return 0;
1712 }
1713
1714 /**
1715  * camel_db_read_folder_info_record:
1716  *
1717  * Since: 2.24
1718  **/
1719 gint
1720 camel_db_read_folder_info_record (CamelDB *cdb,
1721                                   const gchar *folder_name,
1722                                   CamelFIRecord *record,
1723                                   GError **error)
1724 {
1725         struct ReadFirData rfd;
1726         gchar *query;
1727         gint ret;
1728
1729         rfd.columns_hash = NULL;
1730         rfd.record = record;
1731
1732         query = sqlite3_mprintf ("SELECT * FROM folders WHERE folder_name = %Q", folder_name);
1733         ret = camel_db_select (cdb, query, read_fir_callback, &rfd, error);
1734         sqlite3_free (query);
1735
1736         if (rfd.columns_hash)
1737                 g_hash_table_destroy (rfd.columns_hash);
1738
1739         return ret;
1740 }
1741
1742 /**
1743  * camel_db_read_message_info_record_with_uid:
1744  *
1745  * Since: 2.24
1746  **/
1747 gint
1748 camel_db_read_message_info_record_with_uid (CamelDB *cdb,
1749                                             const gchar *folder_name,
1750                                             const gchar *uid,
1751                                             gpointer p,
1752                                             CamelDBSelectCB read_mir_callback,
1753                                             GError **error)
1754 {
1755         gchar *query;
1756         gint ret;
1757
1758         query = sqlite3_mprintf ("SELECT uid, flags, size, dsent, dreceived, subject, mail_from, mail_to, mail_cc, mlist, part, labels, usertags, cinfo, bdata FROM %Q WHERE uid = %Q", folder_name, uid);
1759         ret = camel_db_select (cdb, query, read_mir_callback, p, error);
1760         sqlite3_free (query);
1761
1762         return (ret);
1763 }
1764
1765 /**
1766  * camel_db_read_message_info_records:
1767  *
1768  * Since: 2.24
1769  **/
1770 gint
1771 camel_db_read_message_info_records (CamelDB *cdb,
1772                                     const gchar *folder_name,
1773                                     gpointer p,
1774                                     CamelDBSelectCB read_mir_callback,
1775                                     GError **error)
1776 {
1777         gchar *query;
1778         gint ret;
1779
1780         query = sqlite3_mprintf ("SELECT uid, flags, size, dsent, dreceived, subject, mail_from, mail_to, mail_cc, mlist, part, labels, usertags, cinfo, bdata FROM %Q ", folder_name);
1781         ret = camel_db_select (cdb, query, read_mir_callback, p, error);
1782         sqlite3_free (query);
1783
1784         return (ret);
1785 }
1786
1787 /**
1788  * camel_db_create_deleted_table:
1789  *
1790  * Since: 2.24
1791  **/
1792 static gint
1793 camel_db_create_deleted_table (CamelDB *cdb,
1794                                GError **error)
1795 {
1796         gint ret;
1797         gchar *table_creation_query;
1798         table_creation_query = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS Deletes (id INTEGER primary key AUTOINCREMENT not null, uid TEXT, time TEXT, mailbox TEXT)");
1799         ret = camel_db_add_to_transaction (cdb, table_creation_query, error);
1800         sqlite3_free (table_creation_query);
1801         return ret;
1802 }
1803
1804 static gint
1805 camel_db_trim_deleted_table (CamelDB *cdb,
1806                              GError **error)
1807 {
1808         gint ret = 0;
1809
1810         /* TODO: We need a mechanism to get rid of very old deletes, or something
1811          * that keeps the list trimmed at a certain max (deleting upfront when
1812          * appending at the back) */
1813
1814         return ret;
1815 }
1816
1817 /**
1818  * camel_db_delete_uid:
1819  *
1820  * Since: 2.24
1821  **/
1822 gint
1823 camel_db_delete_uid (CamelDB *cdb,
1824                      const gchar *folder,
1825                      const gchar *uid,
1826                      GError **error)
1827 {
1828         gchar *tab;
1829         gint ret;
1830
1831         camel_db_begin_transaction (cdb, error);
1832
1833         ret = camel_db_create_deleted_table (cdb, error);
1834
1835         tab = sqlite3_mprintf ("INSERT OR REPLACE INTO Deletes (uid, mailbox, time) SELECT uid, %Q, strftime(\"%%s\", 'now') FROM %Q WHERE uid = %Q", folder, folder, uid);
1836         ret = camel_db_add_to_transaction (cdb, tab, error);
1837         sqlite3_free (tab);
1838
1839         ret = camel_db_trim_deleted_table (cdb, error);
1840
1841         tab = sqlite3_mprintf ("DELETE FROM '%q_bodystructure' WHERE uid = %Q", folder, uid);
1842         ret = camel_db_add_to_transaction (cdb, tab, error);
1843         sqlite3_free (tab);
1844
1845         tab = sqlite3_mprintf ("DELETE FROM %Q WHERE uid = %Q", folder, uid);
1846         ret = camel_db_add_to_transaction (cdb, tab, error);
1847         sqlite3_free (tab);
1848
1849         ret = camel_db_end_transaction (cdb, error);
1850
1851         CAMEL_DB_RELEASE_SQLITE_MEMORY;
1852         return ret;
1853 }
1854
1855 static gint
1856 cdb_delete_ids (CamelDB *cdb,
1857                 const gchar *folder_name,
1858                 GList *uids,
1859                 const gchar *uid_prefix,
1860                 const gchar *field,
1861                 GError **error)
1862 {
1863         gchar *tmp;
1864         gint ret;
1865         gchar *tab;
1866         gboolean first = TRUE;
1867         GString *str = g_string_new ("DELETE FROM ");
1868         GList *iterator;
1869         GString *ins_str = NULL;
1870
1871         if (strcmp (field, "vuid") != 0)
1872                 ins_str = g_string_new ("INSERT OR REPLACE INTO Deletes (uid, mailbox, time) SELECT uid, ");
1873
1874         camel_db_begin_transaction (cdb, error);
1875
1876         if (ins_str)
1877                 ret = camel_db_create_deleted_table (cdb, error);
1878
1879         if (ins_str) {
1880                 tab = sqlite3_mprintf ("%Q, strftime(\"%%s\", 'now') FROM %Q WHERE %s IN (", folder_name, folder_name, field);
1881                 g_string_append_printf (ins_str, "%s ", tab);
1882                 sqlite3_free (tab);
1883         }
1884
1885         tmp = sqlite3_mprintf ("%Q WHERE %s IN (", folder_name, field);
1886         g_string_append_printf (str, "%s ", tmp);
1887         sqlite3_free (tmp);
1888
1889         iterator = uids;
1890
1891         while (iterator) {
1892                 gchar *foo = g_strdup_printf ("%s%s", uid_prefix, (gchar *) iterator->data);
1893                 tmp = sqlite3_mprintf ("%Q", foo);
1894                 g_free (foo);
1895                 iterator = iterator->next;
1896
1897                 if (first == TRUE) {
1898                         g_string_append_printf (str, " %s ", tmp);
1899                         if (ins_str)
1900                                 g_string_append_printf (ins_str, " %s ", tmp);
1901                         first = FALSE;
1902                 } else {
1903                         g_string_append_printf (str, ", %s ", tmp);
1904                         if (ins_str)
1905                                 g_string_append_printf (ins_str, ", %s ", tmp);
1906                 }
1907
1908                 sqlite3_free (tmp);
1909         }
1910
1911         g_string_append (str, ")");
1912         if (ins_str) {
1913                 g_string_append (ins_str, ")");
1914                 ret = camel_db_add_to_transaction (cdb, ins_str->str, error);
1915                 ret = camel_db_trim_deleted_table (cdb, error);
1916         }
1917
1918         ret = camel_db_add_to_transaction (cdb, str->str, error);
1919
1920         ret = camel_db_end_transaction (cdb, error);
1921
1922         CAMEL_DB_RELEASE_SQLITE_MEMORY;
1923
1924         if (ins_str)
1925                 g_string_free (ins_str, TRUE);
1926         g_string_free (str, TRUE);
1927
1928         return ret;
1929 }
1930
1931 /**
1932  * camel_db_delete_uids:
1933  *
1934  * Since: 2.24
1935  **/
1936 gint
1937 camel_db_delete_uids (CamelDB *cdb,
1938                       const gchar *folder_name,
1939                       GList *uids,
1940                       GError **error)
1941 {
1942         if (!uids || !uids->data)
1943                 return 0;
1944
1945         return cdb_delete_ids (cdb, folder_name, uids, "", "uid", error);
1946 }
1947
1948 /**
1949  * camel_db_clear_folder_summary:
1950  *
1951  * Since: 2.24
1952  **/
1953 gint
1954 camel_db_clear_folder_summary (CamelDB *cdb,
1955                                const gchar *folder,
1956                                GError **error)
1957 {
1958         gint ret;
1959
1960         gchar *folders_del;
1961         gchar *msginfo_del;
1962         gchar *bstruct_del;
1963         gchar *tab;
1964
1965         folders_del = sqlite3_mprintf ("DELETE FROM folders WHERE folder_name = %Q", folder);
1966         msginfo_del = sqlite3_mprintf ("DELETE FROM %Q ", folder);
1967         bstruct_del = sqlite3_mprintf ("DELETE FROM '%q_bodystructure' ", folder);
1968
1969         camel_db_begin_transaction (cdb, error);
1970
1971         ret = camel_db_create_deleted_table (cdb, error);
1972
1973         tab = sqlite3_mprintf ("INSERT OR REPLACE INTO Deletes (uid, mailbox, time) SELECT uid, %Q, strftime(\"%%s\", 'now') FROM %Q", folder, folder);
1974         ret = camel_db_add_to_transaction (cdb, tab, error);
1975         sqlite3_free (tab);
1976
1977         ret = camel_db_trim_deleted_table (cdb, error);
1978
1979         camel_db_add_to_transaction (cdb, msginfo_del, error);
1980         camel_db_add_to_transaction (cdb, folders_del, error);
1981         camel_db_add_to_transaction (cdb, bstruct_del, error);
1982
1983         ret = camel_db_end_transaction (cdb, error);
1984
1985         sqlite3_free (folders_del);
1986         sqlite3_free (msginfo_del);
1987         sqlite3_free (bstruct_del);
1988
1989         return ret;
1990 }
1991
1992 /**
1993  * camel_db_delete_folder:
1994  *
1995  * Since: 2.24
1996  **/
1997 gint
1998 camel_db_delete_folder (CamelDB *cdb,
1999                         const gchar *folder,
2000                         GError **error)
2001 {
2002         gint ret;
2003         gchar *del;
2004         gchar *tab;
2005
2006         camel_db_begin_transaction (cdb, error);
2007
2008         ret = camel_db_create_deleted_table (cdb, error);
2009
2010         tab = sqlite3_mprintf ("INSERT OR REPLACE INTO Deletes (uid, mailbox, time) SELECT uid, %Q, strftime(\"%%s\", 'now') FROM %Q", folder, folder);
2011         ret = camel_db_add_to_transaction (cdb, tab, error);
2012         sqlite3_free (tab);
2013
2014         ret = camel_db_trim_deleted_table (cdb, error);
2015
2016         del = sqlite3_mprintf ("DELETE FROM folders WHERE folder_name = %Q", folder);
2017         ret = camel_db_add_to_transaction (cdb, del, error);
2018         sqlite3_free (del);
2019
2020         del = sqlite3_mprintf ("DROP TABLE %Q ", folder);
2021         ret = camel_db_add_to_transaction (cdb, del, error);
2022         sqlite3_free (del);
2023
2024         del = sqlite3_mprintf ("DROP TABLE '%q_bodystructure' ", folder);
2025         ret = camel_db_add_to_transaction (cdb, del, error);
2026         sqlite3_free (del);
2027
2028         ret = camel_db_end_transaction (cdb, error);
2029
2030         CAMEL_DB_RELEASE_SQLITE_MEMORY;
2031         return ret;
2032 }
2033
2034 /**
2035  * camel_db_rename_folder:
2036  *
2037  * Since: 2.24
2038  **/
2039 gint
2040 camel_db_rename_folder (CamelDB *cdb,
2041                         const gchar *old_folder,
2042                         const gchar *new_folder,
2043                         GError **error)
2044 {
2045         gint ret;
2046         gchar *cmd, *tab;
2047
2048         camel_db_begin_transaction (cdb, error);
2049
2050         ret = camel_db_create_deleted_table (cdb, error);
2051
2052         tab = sqlite3_mprintf ("INSERT OR REPLACE INTO Deletes (uid, mailbox, time) SELECT uid, %Q, strftime(\"%%s\", 'now') FROM %Q", old_folder, old_folder);
2053         ret = camel_db_add_to_transaction (cdb, tab, error);
2054         sqlite3_free (tab);
2055
2056         ret = camel_db_trim_deleted_table (cdb, error);
2057
2058         cmd = sqlite3_mprintf ("ALTER TABLE %Q RENAME TO  %Q", old_folder, new_folder);
2059         ret = camel_db_add_to_transaction (cdb, cmd, error);
2060         sqlite3_free (cmd);
2061
2062         cmd = sqlite3_mprintf ("ALTER TABLE '%q_version' RENAME TO  '%q_version'", old_folder, new_folder);
2063         ret = camel_db_add_to_transaction (cdb, cmd, error);
2064         sqlite3_free (cmd);
2065
2066         cmd = sqlite3_mprintf ("UPDATE %Q SET modified=strftime(\"%%s\", 'now'), created=strftime(\"%%s\", 'now')", new_folder);
2067         ret = camel_db_add_to_transaction (cdb, cmd, error);
2068         sqlite3_free (cmd);
2069
2070         cmd = sqlite3_mprintf ("UPDATE folders SET folder_name = %Q WHERE folder_name = %Q", new_folder, old_folder);
2071         ret = camel_db_add_to_transaction (cdb, cmd, error);
2072         sqlite3_free (cmd);
2073
2074         ret = camel_db_end_transaction (cdb, error);
2075
2076         CAMEL_DB_RELEASE_SQLITE_MEMORY;
2077         return ret;
2078 }
2079
2080 /**
2081  * camel_db_camel_mir_free:
2082  *
2083  * Since: 2.24
2084  **/
2085 void
2086 camel_db_camel_mir_free (CamelMIRecord *record)
2087 {
2088         if (record) {
2089                 camel_pstring_free (record->uid);
2090                 camel_pstring_free (record->subject);
2091                 camel_pstring_free (record->from);
2092                 camel_pstring_free (record->to);
2093                 camel_pstring_free (record->cc);
2094                 camel_pstring_free (record->mlist);
2095                 camel_pstring_free (record->followup_flag);
2096                 camel_pstring_free (record->followup_completed_on);
2097                 camel_pstring_free (record->followup_due_by);
2098                 g_free (record->part);
2099                 g_free (record->labels);
2100                 g_free (record->usertags);
2101                 g_free (record->cinfo);
2102                 g_free (record->bdata);
2103                 g_free (record->bodystructure);
2104
2105                 g_free (record);
2106         }
2107 }
2108
2109 /**
2110  * camel_db_sqlize_string:
2111  *
2112  * Since: 2.24
2113  **/
2114 gchar *
2115 camel_db_sqlize_string (const gchar *string)
2116 {
2117         return sqlite3_mprintf ("%Q", string);
2118 }
2119
2120 /**
2121  * camel_db_free_sqlized_string:
2122  *
2123  * Since: 2.24
2124  **/
2125 void
2126 camel_db_free_sqlized_string (gchar *string)
2127 {
2128         sqlite3_free (string);
2129         string = NULL;
2130 }
2131
2132 /*
2133 "(  uid TEXT PRIMARY KEY ,
2134 flags INTEGER ,
2135 msg_type INTEGER ,
2136 replied INTEGER ,
2137 dirty INTEGER ,
2138 size INTEGER ,
2139 dsent NUMERIC ,
2140 dreceived NUMERIC ,
2141 mlist TEXT ,
2142 followup_flag TEXT ,
2143 followup_completed_on TEXT ,
2144 followup_due_by TEXT ," */
2145
2146 /**
2147  * camel_db_get_column_name:
2148  *
2149  * Since: 2.24
2150  **/
2151 gchar *
2152 camel_db_get_column_name (const gchar *raw_name)
2153 {
2154         if (!g_ascii_strcasecmp (raw_name, "Subject"))
2155                 return g_strdup ("subject");
2156         else if (!g_ascii_strcasecmp (raw_name, "from"))
2157                 return g_strdup ("mail_from");
2158         else if (!g_ascii_strcasecmp (raw_name, "Cc"))
2159                 return g_strdup ("mail_cc");
2160         else if (!g_ascii_strcasecmp (raw_name, "To"))
2161                 return g_strdup ("mail_to");
2162         else if (!g_ascii_strcasecmp (raw_name, "Flagged"))
2163                 return g_strdup ("important");
2164         else if (!g_ascii_strcasecmp (raw_name, "deleted"))
2165                 return g_strdup ("deleted");
2166         else if (!g_ascii_strcasecmp (raw_name, "junk"))
2167                 return g_strdup ("junk");
2168         else if (!g_ascii_strcasecmp (raw_name, "Answered"))
2169                 return g_strdup ("replied");
2170         else if (!g_ascii_strcasecmp (raw_name, "Seen"))
2171                 return g_strdup ("read");
2172         else if (!g_ascii_strcasecmp (raw_name, "user-tag"))
2173                 return g_strdup ("usertags");
2174         else if (!g_ascii_strcasecmp (raw_name, "user-flag"))
2175                 return g_strdup ("labels");
2176         else if (!g_ascii_strcasecmp (raw_name, "Attachments"))
2177                 return g_strdup ("attachment");
2178         else if (!g_ascii_strcasecmp (raw_name, "x-camel-mlist"))
2179                 return g_strdup ("mlist");
2180         else
2181                 return g_strdup (raw_name);
2182
2183 }
2184
2185 /**
2186  * camel_db_start_in_memory_transactions:
2187  *
2188  * Since: 2.26
2189  **/
2190 gint
2191 camel_db_start_in_memory_transactions (CamelDB *cdb,
2192                                        GError **error)
2193 {
2194         gint ret;
2195         gchar *cmd = sqlite3_mprintf ("ATTACH DATABASE ':memory:' AS %s", CAMEL_DB_IN_MEMORY_DB);
2196
2197         ret = camel_db_command (cdb, cmd, error);
2198         sqlite3_free (cmd);
2199
2200         cmd = sqlite3_mprintf ("CREATE TEMPORARY TABLE %Q (  uid TEXT PRIMARY KEY , flags INTEGER , msg_type INTEGER , read INTEGER , deleted INTEGER , replied INTEGER , important INTEGER , junk INTEGER , attachment INTEGER , dirty 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 )", CAMEL_DB_IN_MEMORY_TABLE);
2201         ret = camel_db_command (cdb, cmd, error);
2202         if (ret != 0 )
2203                 abort ();
2204         sqlite3_free (cmd);
2205
2206         return ret;
2207 }
2208
2209 /**
2210  * camel_db_flush_in_memory_transactions:
2211  *
2212  * Since: 2.26
2213  **/
2214 gint
2215 camel_db_flush_in_memory_transactions (CamelDB *cdb,
2216                                        const gchar *folder_name,
2217                                        GError **error)
2218 {
2219         gint ret;
2220         gchar *cmd = sqlite3_mprintf ("INSERT INTO %Q SELECT * FROM %Q", folder_name, CAMEL_DB_IN_MEMORY_TABLE);
2221
2222         ret = camel_db_command (cdb, cmd, error);
2223         sqlite3_free (cmd);
2224
2225         cmd = sqlite3_mprintf ("DROP TABLE %Q", CAMEL_DB_IN_MEMORY_TABLE);
2226         ret = camel_db_command (cdb, cmd, error);
2227         sqlite3_free (cmd);
2228
2229         cmd = sqlite3_mprintf ("DETACH %Q", CAMEL_DB_IN_MEMORY_DB);
2230         ret = camel_db_command (cdb, cmd, error);
2231         sqlite3_free (cmd);
2232
2233         return ret;
2234 }
2235
2236 static struct _known_column_names {
2237         const gchar *name;
2238         CamelDBKnownColumnNames ident;
2239 } known_column_names[] = {
2240         { "attachment",                 CAMEL_DB_COLUMN_ATTACHMENT },
2241         { "bdata",                      CAMEL_DB_COLUMN_BDATA },
2242         { "bodystructure",              CAMEL_DB_COLUMN_BODYSTRUCTURE },
2243         { "cinfo",                      CAMEL_DB_COLUMN_CINFO },
2244         { "deleted",                    CAMEL_DB_COLUMN_DELETED },
2245         { "deleted_count",              CAMEL_DB_COLUMN_DELETED_COUNT },
2246         { "dreceived",                  CAMEL_DB_COLUMN_DRECEIVED },
2247         { "dsent",                      CAMEL_DB_COLUMN_DSENT },
2248         { "flags",                      CAMEL_DB_COLUMN_FLAGS },
2249         { "folder_name",                CAMEL_DB_COLUMN_FOLDER_NAME },
2250         { "followup_completed_on",      CAMEL_DB_COLUMN_FOLLOWUP_COMPLETED_ON },
2251         { "followup_due_by",            CAMEL_DB_COLUMN_FOLLOWUP_DUE_BY },
2252         { "followup_flag",              CAMEL_DB_COLUMN_FOLLOWUP_FLAG },
2253         { "important",                  CAMEL_DB_COLUMN_IMPORTANT },
2254         { "jnd_count",                  CAMEL_DB_COLUMN_JND_COUNT },
2255         { "junk",                       CAMEL_DB_COLUMN_JUNK },
2256         { "junk_count",                 CAMEL_DB_COLUMN_JUNK_COUNT },
2257         { "labels",                     CAMEL_DB_COLUMN_LABELS },
2258         { "mail_cc",                    CAMEL_DB_COLUMN_MAIL_CC },
2259         { "mail_from",                  CAMEL_DB_COLUMN_MAIL_FROM },
2260         { "mail_to",                    CAMEL_DB_COLUMN_MAIL_TO },
2261         { "mlist",                      CAMEL_DB_COLUMN_MLIST },
2262         { "nextuid",                    CAMEL_DB_COLUMN_NEXTUID },
2263         { "part",                       CAMEL_DB_COLUMN_PART },
2264         { "preview",                    CAMEL_DB_COLUMN_PREVIEW },
2265         { "read",                       CAMEL_DB_COLUMN_READ },
2266         { "replied",                    CAMEL_DB_COLUMN_REPLIED },
2267         { "saved_count",                CAMEL_DB_COLUMN_SAVED_COUNT },
2268         { "size",                       CAMEL_DB_COLUMN_SIZE },
2269         { "subject",                    CAMEL_DB_COLUMN_SUBJECT },
2270         { "time",                       CAMEL_DB_COLUMN_TIME },
2271         { "uid",                        CAMEL_DB_COLUMN_UID },
2272         { "unread_count",               CAMEL_DB_COLUMN_UNREAD_COUNT },
2273         { "usertags",                   CAMEL_DB_COLUMN_USERTAGS },
2274         { "version",                    CAMEL_DB_COLUMN_VERSION },
2275         { "visible_count",              CAMEL_DB_COLUMN_VISIBLE_COUNT },
2276         { "vuid",                       CAMEL_DB_COLUMN_VUID }
2277 };
2278
2279 /**
2280  * camel_db_get_column_ident:
2281  *
2282  * Traverses column name from index @index into an enum
2283  * #CamelDBKnownColumnNames value.  The @col_names contains @ncols columns.
2284  * First time this is called is created the @hash from col_names indexes into
2285  * the enum, and this is reused for every other call.  The function expects
2286  * that column names are returned always in the same order.  When all rows
2287  * are read the @hash table can be freed with g_hash_table_destroy().
2288  *
2289  * Since: 3.4
2290  **/
2291 CamelDBKnownColumnNames
2292 camel_db_get_column_ident (GHashTable **hash,
2293                            gint index,
2294                            gint ncols,
2295                            gchar **col_names)
2296 {
2297         gpointer value = NULL;
2298
2299         g_return_val_if_fail (hash != NULL, CAMEL_DB_COLUMN_UNKNOWN);
2300         g_return_val_if_fail (col_names != NULL, CAMEL_DB_COLUMN_UNKNOWN);
2301         g_return_val_if_fail (ncols > 0, CAMEL_DB_COLUMN_UNKNOWN);
2302         g_return_val_if_fail (index >= 0, CAMEL_DB_COLUMN_UNKNOWN);
2303         g_return_val_if_fail (index < ncols, CAMEL_DB_COLUMN_UNKNOWN);
2304
2305         if (!*hash) {
2306                 gint ii, jj, from, max = G_N_ELEMENTS (known_column_names);
2307
2308                 *hash = g_hash_table_new (g_direct_hash, g_direct_equal);
2309
2310                 for (ii = 0, jj = 0; ii < ncols; ii++) {
2311                         const gchar *name = col_names[ii];
2312                         gboolean first = TRUE;
2313
2314                         if (!name)
2315                                 continue;
2316
2317                         for (from = jj; first || jj != from; jj = (jj + 1) % max, first = FALSE) {
2318                                 if (g_str_equal (name, known_column_names[jj].name)) {
2319                                         g_hash_table_insert (*hash, GINT_TO_POINTER (ii), GINT_TO_POINTER (known_column_names[jj].ident));
2320                                         break;
2321                                 }
2322                         }
2323
2324                         if (from == jj && !first)
2325                                 g_warning ("%s: missing column name '%s' in a list of known columns", G_STRFUNC, name);
2326                 }
2327         }
2328
2329         g_return_val_if_fail (g_hash_table_lookup_extended (*hash, GINT_TO_POINTER (index), NULL, &value), CAMEL_DB_COLUMN_UNKNOWN);
2330
2331         return GPOINTER_TO_INT (value);
2332 }