1 /* logjam - a GTK client for LiveJournal.
2 * Copyright (C) 2000-2005 Evan Martin <evan@livejournal.com>
4 * vim: tabstop=4 shiftwidth=4 noexpandtab :
13 #include "journalstore.h"
16 struct _JournalStore
{
22 CREATE TABLE meta (\n\
25 CREATE TABLE entry (\n\
26 itemid INTEGER PRIMARY KEY,\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\
35 CREATE INDEX dateindex ON entry (year, month, day);\n\
36 CREATE INDEX timeindex ON entry (timestamp);"
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"
43 #define SQLCHECK(s) if (SQLITE_OK != s) { g_warning("FIXME: sqlite error %d in " #s ".\n", s); }
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
));
51 sql_trace(void *data
, const char *sql
) {
52 /* g_print("SQL> %s\n", sql); */
56 begin_t(sqlite3
*db
, GError
**err
) {
58 ret
= sqlite3_exec(db
, "BEGIN TRANSACTION", NULL
, NULL
, NULL
);
59 if (ret
!= SQLITE_OK
) {
60 report_sqlite_error(db
, ret
, err
);
66 end_t(sqlite3
*db
, GError
**err
) {
68 ret
= sqlite3_exec(db
, "COMMIT TRANSACTION", NULL
, NULL
, NULL
);
69 if (ret
!= SQLITE_OK
) {
70 report_sqlite_error(db
, ret
, err
);
77 init_db(sqlite3
*db
, GError
**err
) {
80 if (!begin_t(db
, err
))
83 ret
= sqlite3_exec(db
, SCHEMA
, NULL
, NULL
, NULL
);
84 if (ret
!= SQLITE_OK
) {
85 report_sqlite_error(db
, ret
, err
);
88 ret
= sqlite3_exec(db
, "INSERT INTO meta (schemaver) "
89 "VALUES (" SCHEMA_VERSION_STRING
")",
91 if (ret
!= SQLITE_OK
) {
92 report_sqlite_error(db
, ret
, err
);
103 check_version(sqlite3
*db
) {
105 sqlite3_stmt
*stmt
= NULL
;
107 SQLCHECK(sqlite3_prepare(db
,
108 "SELECT schemaver FROM meta",
111 ret
= sqlite3_step(stmt
);
112 if (ret
!= SQLITE_ROW
) {
114 SQLCHECK(sqlite3_finalize(stmt
));
118 ver
= sqlite3_column_int(stmt
, 0);
119 SQLCHECK(sqlite3_finalize(stmt
));
121 return ver
== SCHEMA_VERSION
;
124 JournalStore
* journal_store_open(JamAccount
*acc
, gboolean create
, GError
**err
) {
125 JournalStore
*js
= NULL
;
131 path
= conf_make_account_path(acc
, "journal.db");
132 exists
= g_file_test(path
, G_FILE_TEST_EXISTS
);
134 if (!exists
&& !create
) {
135 g_set_error(err
, 0, 0, "No offline copy of this journal.");
139 if (!verify_path(path
, FALSE
, err
))
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
));
148 sqlite3_trace(db
, sql_trace
, NULL
);
151 if (!check_version(db
)) {
152 g_set_error(err
, 0, 0,
153 "The on-disk journal version differs from "
154 "the version understood by this version of LogJam. "
155 "You need to resynchronize your journal.");
159 if (!init_db(db
, err
))
163 js
= g_new0(JournalStore
, 1);
169 if (!js
&& db
) sqlite3_close(db
);
173 journal_store_free(JournalStore
*js
) {
174 sqlite3_close(js
->db
);
179 gboolean
journal_store_reindex(JamAccount
*acc
, GError
**err
) { return TRUE
; }
180 gboolean
journal_store_get_invalid(JournalStore
*js
) { return FALSE
; }
183 journal_store_get_count(JournalStore
*js
) {
185 sqlite3_stmt
*stmt
= NULL
;
187 SQLCHECK(sqlite3_prepare(js
->db
, "SELECT COUNT(*) FROM entry", -1,
189 ret
= sqlite3_step(stmt
);
190 if (ret
!= SQLITE_ROW
) {
192 SQLCHECK(sqlite3_finalize(stmt
));
196 ret
= sqlite3_column_int(stmt
, 0);
197 SQLCHECK(sqlite3_finalize(stmt
));
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
));
209 security_from_int(LJSecurity
*security
, guint32 value
) {
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
;
221 journal_store_get_entry(JournalStore
*js
, int get_itemid
) {
223 sqlite3_stmt
*stmt
= NULL
;
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",
233 SQLCHECK(sqlite3_bind_int(stmt
, 1, get_itemid
));
235 ret
= sqlite3_step(stmt
);
236 if (ret
!= SQLITE_ROW
) {
237 SQLCHECK(sqlite3_finalize(stmt
));
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);
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);
256 timestamp
= sqlite3_column_int(stmt
, 10);
257 gmtime_r(×tamp
, &entry
->time
);
259 if (sqlite3_column_type(stmt
, 11) != SQLITE_NULL
)
260 security_from_int(&entry
->security
, sqlite3_column_int(stmt
, 11));
262 SQLCHECK(sqlite3_finalize(stmt
));
268 journal_store_get_latest_id(JournalStore
*js
) {
271 sqlite3_stmt
*stmt
= NULL
;
273 SQLCHECK(sqlite3_prepare(js
->db
,
274 "SELECT foo, bar FROM baz"
276 SQLCHECK(sqlite3_bind_int(stmt
, 0, fizz
));
278 ret
= sqlite3_step(stmt
);
279 if (ret
!= SQLITE_ROW
) {
280 SQLCHECK(sqlite3_finalize(stmt
));
284 g_error("unimplemented");
289 journal_store_find_relative_by_time(JournalStore
*js
, time_t when
,
290 int *ritemid
, int dir
, GError
*err
) {
293 sqlite3_stmt
*stmt
= NULL
;
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 "
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 "
311 SQLCHECK(sqlite3_bind_int(stmt
, 1, when
));
313 ret
= sqlite3_step(stmt
);
314 if (ret
== SQLITE_ROW
) {
315 itemid
= sqlite3_column_int(stmt
, 0);
316 } else if (ret
!= SQLITE_DONE
) {
320 SQLCHECK(sqlite3_finalize(stmt
));
330 journal_store_lookup_entry_time(JournalStore
*js
, int itemid
) {
332 sqlite3_stmt
*stmt
= NULL
;
334 SQLCHECK(sqlite3_prepare(js
->db
,
335 "SELECT timestamp FROM entry WHERE itemid = ?1",
337 SQLCHECK(sqlite3_bind_int(stmt
, 1, itemid
));
339 ret
= sqlite3_step(stmt
);
340 if (ret
!= SQLITE_ROW
) {
342 SQLCHECK(sqlite3_finalize(stmt
));
346 ret
= sqlite3_column_int(stmt
, 0);
347 SQLCHECK(sqlite3_finalize(stmt
));
351 journal_store_get_account(JournalStore
*js
) {
355 gboolean
journal_store_begin_group(JournalStore
*js
, GError
**err
) {
356 return begin_t(js
->db
, err
);
358 gboolean
journal_store_end_group(JournalStore
*js
, GError
**err
) {
359 return end_t(js
->db
, err
);
363 journal_store_put(JournalStore
*js
, LJEntry
*entry
, GError
**err
) {
364 sqlite3_stmt
*stmt
= NULL
;
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)",
375 SQLCHECK(sqlite3_bind_int(stmt
, 1, entry
->itemid
));
376 SQLCHECK(sqlite3_bind_int(stmt
, 2, entry
->anum
));
378 SQLCHECK(sqlite3_bind_text(stmt
, 3, entry
->subject
, -1,
380 SQLCHECK(sqlite3_bind_text(stmt
, 4, entry
->event
, -1, SQLITE_TRANSIENT
));
382 SQLCHECK(sqlite3_bind_int(stmt
, 5, entry
->moodid
));
384 SQLCHECK(sqlite3_bind_text(stmt
, 6, entry
->mood
, -1, SQLITE_TRANSIENT
));
386 SQLCHECK(sqlite3_bind_text(stmt
, 7, entry
->music
, -1,
388 if (entry
->pickeyword
)
389 SQLCHECK(sqlite3_bind_text(stmt
, 8, entry
->pickeyword
, -1,
392 SQLCHECK(sqlite3_bind_text(stmt
, 12, entry
->taglist
, -1,
395 if (entry
->preformatted
)
396 SQLCHECK(sqlite3_bind_int(stmt
, 9, 1));
397 if (entry
->backdated
)
398 SQLCHECK(sqlite3_bind_int(stmt
, 10, 1));
400 SQLCHECK(sqlite3_bind_int(stmt
, 11, 1));
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
)));
408 if (entry
->security
.type
!= LJ_SECURITY_PUBLIC
) {
410 switch (entry
->security
.type
) {
411 case LJ_SECURITY_FRIENDS
:
413 case LJ_SECURITY_CUSTOM
:
414 sec
= entry
->security
.allowmask
; break;
415 case LJ_SECURITY_PRIVATE
:
419 SQLCHECK(sqlite3_bind_int(stmt
, 17, sec
));
422 ret
= sqlite3_step(stmt
);
423 if (ret
!= SQLITE_DONE
) {
424 SQLCHECK(ret
/* step */);
425 SQLCHECK(sqlite3_finalize(stmt
));
428 SQLCHECK(sqlite3_finalize(stmt
));
434 journal_store_put_group(JournalStore
*js
, LJEntry
**entries
, int c
, GError
**err
) {
437 if (!begin_t(js
->db
, err
))
440 for (i
= 0; i
< c
; i
++) {
441 if (!journal_store_put(js
, entries
[i
], err
))
445 if (!end_t(js
->db
, err
))
456 journal_store_get_month_entries(JournalStore
*js
, int year
, int mon
) {
459 sqlite3_stmt
*stmt
= NULL
;
461 SQLCHECK(sqlite3_prepare(js
->db
,
462 "SELECT day FROM entry WHERE year=?1 AND month=?2",
464 SQLCHECK(sqlite3_bind_int(stmt
, 1, year
));
465 SQLCHECK(sqlite3_bind_int(stmt
, 2, mon
));
467 ret
= sqlite3_step(stmt
);
468 while (ret
== SQLITE_ROW
) {
469 int day
= sqlite3_column_int(stmt
, 0);
471 ret
= sqlite3_step(stmt
);
474 if (ret
!= SQLITE_DONE
) {
475 SQLCHECK(ret
/* step */);
476 SQLCHECK(sqlite3_finalize(stmt
));
479 SQLCHECK(sqlite3_finalize(stmt
));
485 journal_store_get_day_entries(JournalStore
*js
,
486 int year
, int mon
, int day
,
487 JournalStoreSummaryCallback cb_func
,
490 sqlite3_stmt
*stmt
= NULL
;
493 LJSecurity sec
= {0};
495 SQLCHECK(sqlite3_prepare(js
->db
,
496 "SELECT itemid, timestamp, subject, event "
497 "FROM entry WHERE year=?1 AND month=?2 AND day=?3",
499 SQLCHECK(sqlite3_bind_int(stmt
, 1, year
));
500 SQLCHECK(sqlite3_bind_int(stmt
, 2, mon
));
501 SQLCHECK(sqlite3_bind_int(stmt
, 3, day
));
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 */,
512 ret
= sqlite3_step(stmt
);
514 if (ret
!= SQLITE_DONE
) {
516 SQLCHECK(sqlite3_finalize(stmt
));
520 SQLCHECK(sqlite3_finalize(stmt
));
526 journal_store_scan(JournalStore
*js
,
527 JournalStoreScanCallback scan_cb
,
528 const gpointer scan_data
,
529 JournalStoreSummaryCallback cb_func
,
530 const gpointer cb_data
) {
532 sqlite3_stmt
*stmt
= NULL
;
535 SQLCHECK(sqlite3_prepare(js
->db
,
536 "SELECT itemid, timestamp, subject, event, security "
537 "FROM entry ORDER BY itemid ASC",
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));
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
)
556 ret
= sqlite3_step(stmt
);
559 if (ret
!= SQLITE_ROW
&& ret
!= SQLITE_DONE
) {
561 SQLCHECK(sqlite3_finalize(stmt
));
565 SQLCHECK(sqlite3_finalize(stmt
));