src/journalstore-sqlite.c

   1 /* logjam - a GTK client for LiveJournal.
   2  * Copyright (C) 2000-2005 Evan Martin <evan@livejournal.com>
   3  *
   4  * vim: tabstop=4 shiftwidth=4 noexpandtab :
   5  */
   6 #ifdef HAVE_SQLITE3
   7
   8 #include "glib-all.h"
   9
  10 #include <sqlite3.h>
  11
  12 #include "conf.h"
  13 #include "journalstore.h"
  14 #include "util.h"
  15
  16 struct _JournalStore {
  17   JamAccount *account;
  18   sqlite3 *db;
  19 };
  20
  21 #define SCHEMA "\
  22 CREATE TABLE meta (\n\
  23   schemaver INTEGER\n\
  24 );\n\
  25 CREATE TABLE entry (\n\
  26   itemid INTEGER PRIMARY KEY,\n\
  27   anum INTEGER,\n\
  28   subject STRING,\n\
  29   event STRING,\n\
  30   moodid INTEGER, mood STRING, music STRING, taglist STRING, \n\
  31   pickeyword STRING, preformatted INTEGER, backdated INTEGER, \n\
  32   comments INTEGER, year INTEGER, month INTEGER, day INTEGER, \n\
  33   timestamp INTEGER, security INTEGER\n\
  34 );\n\
  35 CREATE INDEX dateindex ON entry (year, month, day);\n\
  36 CREATE INDEX timeindex ON entry (timestamp);"
  37
  38 /* a number we can tweak if we change the schema. */
  39 #define SCHEMA_VERSION 1
  40 /* this can be done with preprocessor tricks but this works just as well. */
  41 #define SCHEMA_VERSION_STRING "1"
  42
  43 #define SQLCHECK(s) if (SQLITE_OK != s) { g_warning("FIXME: sqlite error %d in " #s ".\n", s); }
  44
  45 static void
  46 report_sqlite_error(sqlite3 *db, int ret, GError **err) {
  47   g_set_error(err, 0, 0, "FIXME sqlite error %d: %s", ret, sqlite3_errmsg(db));
  48 }
  49
  50 static void
  51 sql_trace(void *data, const char *sql) {
  52   /* g_print("SQL> %s\n", sql); */
  53 }
  54
  55 static gboolean
  56 begin_t(sqlite3 *db, GError **err) {
  57   int ret;
  58   ret = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
  59   if (ret != SQLITE_OK) {
  60     report_sqlite_error(db, ret, err);
  61     return FALSE;
  62   }
  63   return TRUE;
  64 }
  65 static gboolean
  66 end_t(sqlite3 *db, GError **err) {
  67   int ret;
  68   ret = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);
  69   if (ret != SQLITE_OK) {
  70     report_sqlite_error(db, ret, err);
  71     return FALSE;
  72   }
  73   return TRUE;
  74 }
  75
  76 static gboolean
  77 init_db(sqlite3 *db, GError **err) {
  78   int ret;
  79
  80   if (!begin_t(db, err))
  81     return FALSE;
  82
  83   ret = sqlite3_exec(db, SCHEMA, NULL, NULL, NULL);
  84   if (ret != SQLITE_OK) {
  85     report_sqlite_error(db, ret, err);
  86     return FALSE;
  87   }
  88   ret = sqlite3_exec(db, "INSERT INTO meta (schemaver) "
  89                  "VALUES (" SCHEMA_VERSION_STRING ")",
  90                      NULL, NULL, NULL);
  91   if (ret != SQLITE_OK) {
  92     report_sqlite_error(db, ret, err);
  93     return FALSE;
  94   }
  95
  96   if (!end_t(db, err))
  97     return FALSE;
  98
  99   return TRUE;
 100 }
 101
 102 static gboolean
 103 check_version(sqlite3 *db) {
 104   int ret, ver;
 105   sqlite3_stmt *stmt = NULL;
 106
 107   SQLCHECK(sqlite3_prepare(db,
 108     "SELECT schemaver FROM meta",
 109     -1, &stmt, NULL));
 110
 111   ret = sqlite3_step(stmt);
 112   if (ret != SQLITE_ROW) {
 113     SQLCHECK(ret);
 114     SQLCHECK(sqlite3_finalize(stmt));
 115     return FALSE;
 116   }
 117
 118   ver = sqlite3_column_int(stmt, 0);
 119   SQLCHECK(sqlite3_finalize(stmt));
 120
 121   return ver == SCHEMA_VERSION;
 122 }
 123
 124 JournalStore* journal_store_open(JamAccount *acc, gboolean create, GError **err) {
 125   JournalStore *js = NULL;
 126   char *path = NULL;
 127   sqlite3 *db = NULL;
 128   int ret;
 129   gboolean exists;
 130
 131   path = conf_make_account_path(acc, "journal.db");
 132   exists = g_file_test(path, G_FILE_TEST_EXISTS);
 133
 134   if (!exists && !create) {
 135     g_set_error(err, 0, 0, "%s", "No offline copy of this journal.");
 136     goto out;
 137   }
 138
 139   if (!verify_path(path, FALSE, err))
 140     goto out;
 141
 142   ret = sqlite3_open(path, &db);
 143   if (ret != SQLITE_OK) {
 144     g_set_error(err, 0, 0, "sqlite error %d: %s", ret, sqlite3_errmsg(db));
 145     goto out;
 146   }
 147
 148   sqlite3_trace(db, sql_trace, NULL);
 149
 150   if (exists) {
 151     if (!check_version(db)) {
 152       g_set_error(err, 0, 0, "%s",
 153           "The on-disk journal version differs from "
 154           "the version understood by this version of LogJam. "
 155           "You need to resynchronize your journal.");
 156       goto out;
 157     }
 158   } else {
 159     if (!init_db(db, err))
 160       goto out;
 161   }
 162
 163   js = g_new0(JournalStore, 1);
 164   js->account = acc;
 165   js->db = db;
 166
 167 out:
 168   g_free(path);
 169   if (!js && db) sqlite3_close(db);
 170   return js;
 171 }
 172 void
 173 journal_store_free(JournalStore *js) {
 174   sqlite3_close(js->db);
 175   g_free(js);
 176 }
 177
 178 /* stubs */
 179 gboolean journal_store_reindex(JamAccount *acc, GError **err) { return TRUE; }
 180 gboolean journal_store_get_invalid(JournalStore *js) { return FALSE; }
 181
 182 int
 183 journal_store_get_count(JournalStore *js) {
 184   int ret;
 185   sqlite3_stmt *stmt = NULL;
 186
 187   SQLCHECK(sqlite3_prepare(js->db, "SELECT COUNT(*) FROM entry", -1,
 188                            &stmt, NULL));
 189   ret = sqlite3_step(stmt);
 190   if (ret != SQLITE_ROW) {
 191     SQLCHECK(ret);
 192     SQLCHECK(sqlite3_finalize(stmt));
 193     return -1;
 194   }
 195
 196   ret = sqlite3_column_int(stmt, 0);
 197   SQLCHECK(sqlite3_finalize(stmt));
 198   return ret;
 199 }
 200
 201 static char*
 202 dup_col_or_null(sqlite3_stmt *stmt, int col) {
 203   if (sqlite3_column_type(stmt, col) != SQLITE_NULL)
 204     return g_strdup((char*)sqlite3_column_text(stmt, col));
 205   return NULL;
 206 }
 207
 208 static void
 209 security_from_int(LJSecurity *security, guint32 value) {
 210   if (value == 0) {
 211     security->type = LJ_SECURITY_PRIVATE;
 212   } else if (value == 1) {
 213     security->type = LJ_SECURITY_FRIENDS;
 214   } else if (value > 1) {
 215     security->type = LJ_SECURITY_CUSTOM;
 216     security->allowmask = value;
 217   }
 218 }
 219
 220 LJEntry*
 221 journal_store_get_entry(JournalStore *js, int get_itemid) {
 222   LJEntry *entry;
 223   sqlite3_stmt *stmt = NULL;
 224   int ret;
 225   time_t timestamp;
 226
 227   SQLCHECK(sqlite3_prepare(js->db,
 228     "SELECT anum, subject, event, moodid, mood, " /* 0-4 */
 229     "music, pickeyword, preformatted, backdated, " /* 5-9 */
 230     "comments, timestamp, security, taglist " /* 10-13 */
 231     "FROM entry WHERE itemid=?1",
 232     -1, &stmt, NULL));
 233   SQLCHECK(sqlite3_bind_int(stmt, 1, get_itemid));
 234
 235   ret = sqlite3_step(stmt);
 236   if (ret != SQLITE_ROW) {
 237     SQLCHECK(sqlite3_finalize(stmt));
 238     return NULL;
 239   }
 240
 241   entry = lj_entry_new();
 242   entry->itemid = get_itemid;
 243   entry->anum = sqlite3_column_int(stmt, 0);
 244   entry->subject = dup_col_or_null(stmt, 1);
 245   entry->event = dup_col_or_null(stmt, 2);
 246   entry->moodid = sqlite3_column_int(stmt, 3);
 247   entry->mood = dup_col_or_null(stmt, 4);
 248
 249   entry->music = dup_col_or_null(stmt, 5);
 250   entry->pickeyword = dup_col_or_null(stmt, 6);
 251   entry->preformatted = sqlite3_column_int(stmt, 7);
 252   entry->backdated = sqlite3_column_int(stmt, 8);
 253   entry->comments = sqlite3_column_int(stmt, 9);
 254   entry->taglist = dup_col_or_null(stmt, 12);
 255
 256   timestamp = sqlite3_column_int(stmt, 10);
 257   gmtime_r(&timestamp, &entry->time);
 258
 259   if (sqlite3_column_type(stmt, 11) != SQLITE_NULL)
 260     security_from_int(&entry->security, sqlite3_column_int(stmt, 11));
 261
 262   SQLCHECK(sqlite3_finalize(stmt));
 263
 264   return entry;
 265 }
 266
 267 int
 268 journal_store_get_latest_id(JournalStore *js) {
 269   #if 0
 270   LJEntry *entry;
 271   sqlite3_stmt *stmt = NULL;
 272   int ret;
 273   SQLCHECK(sqlite3_prepare(js->db,
 274     "SELECT foo, bar FROM baz"
 275     -1, &stmt, NULL));
 276   SQLCHECK(sqlite3_bind_int(stmt, 0, fizz));
 277
 278   ret = sqlite3_step(stmt);
 279   if (ret != SQLITE_ROW) {
 280     SQLCHECK(sqlite3_finalize(stmt));
 281     return NULL;
 282   }
 283   #endif
 284   g_error("unimplemented");
 285   return -1;
 286 }
 287
 288 gboolean
 289 journal_store_find_relative_by_time(JournalStore *js, time_t when,
 290                                     int *ritemid, int dir, GError *err) {
 291   int itemid = -1;
 292   int ret;
 293   sqlite3_stmt *stmt = NULL;
 294
 295   if (dir < 0) {
 296     SQLCHECK(sqlite3_prepare(js->db,
 297       "SELECT itemid FROM entry "
 298       "WHERE timestamp < ?1 "  /* XXX what about entries at the same time? */
 299       "ORDER BY timestamp DESC "
 300       "LIMIT 1",
 301       -1, &stmt, NULL));
 302   } else {
 303     SQLCHECK(sqlite3_prepare(js->db,
 304       "SELECT itemid FROM entry "
 305       "WHERE timestamp > ?1 "  /* XXX what about entries at the same time? */
 306       "ORDER BY timestamp ASC "
 307       "LIMIT 1",
 308       -1, &stmt, NULL));
 309   }
 310
 311   SQLCHECK(sqlite3_bind_int(stmt, 1, when));
 312
 313   ret = sqlite3_step(stmt);
 314   if (ret == SQLITE_ROW) {
 315     itemid = sqlite3_column_int(stmt, 0);
 316   } else if (ret != SQLITE_DONE) {
 317     SQLCHECK(ret);
 318   }
 319
 320   SQLCHECK(sqlite3_finalize(stmt));
 321   if (itemid >= 0) {
 322     *ritemid = itemid;
 323     return TRUE;
 324   }
 325
 326   return FALSE;
 327 }
 328
 329 time_t
 330 journal_store_lookup_entry_time(JournalStore *js, int itemid) {
 331   int ret;
 332   sqlite3_stmt *stmt = NULL;
 333
 334   SQLCHECK(sqlite3_prepare(js->db,
 335     "SELECT timestamp FROM entry WHERE itemid = ?1",
 336     -1, &stmt, NULL));
 337   SQLCHECK(sqlite3_bind_int(stmt, 1, itemid));
 338
 339   ret = sqlite3_step(stmt);
 340   if (ret != SQLITE_ROW) {
 341     SQLCHECK(ret);
 342     SQLCHECK(sqlite3_finalize(stmt));
 343     return 0;
 344   }
 345
 346   ret = sqlite3_column_int(stmt, 0);
 347   SQLCHECK(sqlite3_finalize(stmt));
 348   return ret;
 349 }
 350 JamAccount*
 351 journal_store_get_account(JournalStore *js) {
 352   return js->account;
 353 }
 354
 355 gboolean journal_store_begin_group(JournalStore *js, GError **err) {
 356   return begin_t(js->db, err);
 357 }
 358 gboolean journal_store_end_group(JournalStore *js, GError **err) {
 359   return end_t(js->db, err);
 360 }
 361
 362 gboolean
 363 journal_store_put(JournalStore *js, LJEntry *entry, GError **err) {
 364   sqlite3_stmt *stmt = NULL;
 365   int ret;
 366
 367   SQLCHECK(sqlite3_prepare(js->db,
 368     "INSERT OR REPLACE INTO entry (itemid, anum, subject, event, "
 369     "moodid, mood, music, pickeyword, preformatted, backdated, comments, "
 370     "taglist, year, month, day, timestamp, security) "
 371     "VALUES (?1, ?2, ?3, ?4, "
 372     "?5, ?6, ?7, ?8, ?9, ?10, ?11, "
 373     "?12, ?13, ?14, ?15, ?16, ?17)",
 374     -1, &stmt, NULL));
 375   SQLCHECK(sqlite3_bind_int(stmt, 1, entry->itemid));
 376   SQLCHECK(sqlite3_bind_int(stmt, 2, entry->anum));
 377   if (entry->subject)
 378     SQLCHECK(sqlite3_bind_text(stmt, 3, entry->subject, -1,
 379                                SQLITE_TRANSIENT));
 380   SQLCHECK(sqlite3_bind_text(stmt, 4, entry->event, -1, SQLITE_TRANSIENT));
 381   if (entry->moodid)
 382     SQLCHECK(sqlite3_bind_int(stmt, 5, entry->moodid));
 383   if (entry->mood)
 384     SQLCHECK(sqlite3_bind_text(stmt, 6, entry->mood, -1, SQLITE_TRANSIENT));
 385   if (entry->music)
 386     SQLCHECK(sqlite3_bind_text(stmt, 7, entry->music, -1,
 387                                SQLITE_TRANSIENT));
 388   if (entry->pickeyword)
 389     SQLCHECK(sqlite3_bind_text(stmt, 8, entry->pickeyword, -1,
 390                                SQLITE_TRANSIENT));
 391   if (entry->taglist)
 392     SQLCHECK(sqlite3_bind_text(stmt, 12, entry->taglist, -1,
 393                                SQLITE_TRANSIENT));
 394
 395   if (entry->preformatted)
 396     SQLCHECK(sqlite3_bind_int(stmt, 9, 1));
 397   if (entry->backdated)
 398     SQLCHECK(sqlite3_bind_int(stmt, 10, 1));
 399   if (entry->comments)
 400     SQLCHECK(sqlite3_bind_int(stmt, 11, 1));
 401
 402   if (entry->time.tm_year) {
 403     SQLCHECK(sqlite3_bind_int(stmt, 13, entry->time.tm_year+1900));
 404     SQLCHECK(sqlite3_bind_int(stmt, 14, entry->time.tm_mon+1));
 405     SQLCHECK(sqlite3_bind_int(stmt, 15, entry->time.tm_mday));
 406     SQLCHECK(sqlite3_bind_int(stmt, 16, timegm(&entry->time)));
 407   }
 408   if (entry->security.type != LJ_SECURITY_PUBLIC) {
 409     int sec = -1;
 410     switch (entry->security.type) {
 411     case LJ_SECURITY_FRIENDS:
 412       sec = 1; break;
 413     case LJ_SECURITY_CUSTOM:
 414       sec = entry->security.allowmask; break;
 415     case LJ_SECURITY_PRIVATE:
 416     default:
 417       sec = 0; break;
 418     }
 419     SQLCHECK(sqlite3_bind_int(stmt, 17, sec));
 420   }
 421
 422   ret = sqlite3_step(stmt);
 423   if (ret != SQLITE_DONE) {
 424     SQLCHECK(ret /* step */);
 425     SQLCHECK(sqlite3_finalize(stmt));
 426     return FALSE;
 427   }
 428   SQLCHECK(sqlite3_finalize(stmt));
 429
 430   return TRUE;
 431 }
 432
 433 gboolean
 434 journal_store_put_group(JournalStore *js, LJEntry **entries, int c, GError **err) {
 435   int i;
 436
 437   if (!begin_t(js->db, err))
 438     goto err;
 439
 440   for (i = 0; i < c; i++) {
 441     if (!journal_store_put(js, entries[i], err))
 442       goto err;
 443   }
 444
 445   if (!end_t(js->db, err))
 446     goto err;
 447
 448   return TRUE;
 449
 450 err:
 451   end_t(js->db, NULL);
 452   return FALSE;
 453 }
 454
 455 guint32
 456 journal_store_get_month_entries(JournalStore *js, int year, int mon) {
 457   guint32 month = 0;
 458   int ret;
 459   sqlite3_stmt *stmt = NULL;
 460
 461   SQLCHECK(sqlite3_prepare(js->db,
 462     "SELECT day FROM entry WHERE year=?1 AND month=?2",
 463     -1, &stmt, NULL));
 464   SQLCHECK(sqlite3_bind_int(stmt, 1, year));
 465   SQLCHECK(sqlite3_bind_int(stmt, 2, mon));
 466
 467   ret = sqlite3_step(stmt);
 468   while (ret == SQLITE_ROW) {
 469     int day = sqlite3_column_int(stmt, 0);
 470     month |= (1 << day);
 471     ret = sqlite3_step(stmt);
 472   }
 473
 474   if (ret != SQLITE_DONE) {
 475     SQLCHECK(ret /* step */);
 476     SQLCHECK(sqlite3_finalize(stmt));
 477     return 0;
 478   }
 479   SQLCHECK(sqlite3_finalize(stmt));
 480
 481   return month;
 482 }
 483
 484 gboolean
 485 journal_store_get_day_entries(JournalStore *js,
 486                               int year, int mon, int day,
 487                               JournalStoreSummaryCallback cb_func,
 488                               gpointer cb_data) {
 489   int ret;
 490   sqlite3_stmt *stmt = NULL;
 491   const char *subject;
 492   const char *event;
 493   LJSecurity sec = {0};
 494
 495   SQLCHECK(sqlite3_prepare(js->db,
 496     "SELECT itemid, timestamp, subject, event "
 497     "FROM entry WHERE year=?1 AND month=?2 AND day=?3",
 498     -1, &stmt, NULL));
 499   SQLCHECK(sqlite3_bind_int(stmt, 1, year));
 500   SQLCHECK(sqlite3_bind_int(stmt, 2, mon));
 501   SQLCHECK(sqlite3_bind_int(stmt, 3, day));
 502
 503   ret = sqlite3_step(stmt);
 504   while (ret == SQLITE_ROW) {
 505     int itemid = sqlite3_column_int(stmt, 0);
 506     time_t timestamp = sqlite3_column_int(stmt, 1);
 507     subject = (char*)sqlite3_column_text(stmt, 2);
 508     event = (char*)sqlite3_column_text(stmt, 3);
 509     cb_func(itemid, timestamp,
 510         lj_get_summary(subject, event), &sec /* XXX */,
 511         cb_data);
 512     ret = sqlite3_step(stmt);
 513   }
 514   if (ret != SQLITE_DONE) {
 515     SQLCHECK(ret);
 516     SQLCHECK(sqlite3_finalize(stmt));
 517     return FALSE;
 518   }
 519
 520   SQLCHECK(sqlite3_finalize(stmt));
 521
 522   return TRUE;
 523 }
 524
 525 gboolean
 526 journal_store_scan(JournalStore *js,
 527                    JournalStoreScanCallback scan_cb,
 528                    const gpointer scan_data,
 529                    JournalStoreSummaryCallback cb_func,
 530                    const gpointer cb_data) {
 531   int ret;
 532   sqlite3_stmt *stmt = NULL;
 533   int matchcount = 0;
 534
 535   SQLCHECK(sqlite3_prepare(js->db,
 536     "SELECT itemid, timestamp, subject, event, security "
 537     "FROM entry ORDER BY itemid ASC",
 538     -1, &stmt, NULL));
 539
 540   ret = sqlite3_step(stmt);
 541   while (ret == SQLITE_ROW) {
 542     int itemid = sqlite3_column_int(stmt, 0);
 543     time_t timestamp = sqlite3_column_int(stmt, 1);
 544     const char *subject = (char*)sqlite3_column_text(stmt, 2);
 545     const char *event = (char*)sqlite3_column_text(stmt, 3);
 546     LJSecurity sec = {0};
 547     if (sqlite3_column_type(stmt, 4) != SQLITE_NULL)
 548       security_from_int(&sec, sqlite3_column_int(stmt, 4));
 549
 550     if (scan_cb(subject, scan_data) || scan_cb(event, scan_data)) {
 551       const char *summary = lj_get_summary(subject, event);
 552       cb_func(itemid, timestamp, summary, &sec, cb_data);
 553       if (++matchcount == MAX_MATCHES)
 554         break;
 555     }
 556     ret = sqlite3_step(stmt);
 557   }
 558
 559   if (ret != SQLITE_ROW && ret != SQLITE_DONE) {
 560     SQLCHECK(ret);
 561     SQLCHECK(sqlite3_finalize(stmt));
 562     return FALSE;
 563   }
 564
 565   SQLCHECK(sqlite3_finalize(stmt));
 566
 567   return TRUE;
 568 }
 569
 570 #endif