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