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"
17 struct _JournalStore
{
24 CREATE TABLE meta (\n\
27 CREATE TABLE entry (\n\
28 itemid INTEGER PRIMARY KEY,\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\
37 CREATE INDEX dateindex ON entry (year, month, day);\n\
38 CREATE INDEX timeindex ON entry (timestamp);"
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"
45 #define SQLCHECK(s) if (SQLITE_OK != s) { g_warning("FIXME: sqlite error %d in " #s ".\n", s); }
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
));
53 static void sql_trace (void *data
, const char *sql
) {
54 /* g_print("SQL> %s\n", sql); */
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
);
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
);
78 static gboolean
init_db (sqlite3
*db
, GError
**err
) {
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
);
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
);
91 if (!end_t(db
, err
)) return FALSE
;
96 static gboolean
check_version (sqlite3
*db
) {
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
) {
103 SQLCHECK(sqlite3_finalize(stmt
));
106 ver
= sqlite3_column_int(stmt
, 0);
107 SQLCHECK(sqlite3_finalize(stmt
));
108 return (ver
== SCHEMA_VERSION
);
112 JournalStore
*journal_store_open (JamAccount
*acc
, gboolean create
, GError
**err
) {
113 JournalStore
*js
= NULL
;
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.");
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
));
130 sqlite3_trace(db
, sql_trace
, NULL
);
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.");
140 if (!init_db(db
, err
)) goto out
;
142 js
= g_new0(JournalStore
, 1);
147 if (!js
&& db
) sqlite3_close(db
);
152 void journal_store_free (JournalStore
*js
) {
153 sqlite3_close(js
->db
);
159 gboolean
journal_store_reindex (JamAccount
*acc
, GError
**err
) {
164 gboolean
journal_store_get_invalid (JournalStore
*js
) {
169 int journal_store_get_count (JournalStore
*js
) {
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
) {
176 SQLCHECK(sqlite3_finalize(stmt
));
179 ret
= sqlite3_column_int(stmt
, 0);
180 SQLCHECK(sqlite3_finalize(stmt
));
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
));
191 static void security_from_int (LJSecurity
*security
, guint32 value
) {
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
;
203 LJEntry
*journal_store_get_entry (JournalStore
*js
, int get_itemid
) {
205 sqlite3_stmt
*stmt
= NULL
;
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
));
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(×tamp
, &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
));
240 int journal_store_get_latest_id(JournalStore
*js
) {
243 sqlite3_stmt
*stmt
= NULL
;
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
));
253 g_error("unimplemented");
258 gboolean
journal_store_find_relative_by_time (JournalStore
*js
, time_t when
, int *ritemid
, int dir
, GError
*err
) {
261 sqlite3_stmt
*stmt
= NULL
;
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
));
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
));
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
) {
276 SQLCHECK(sqlite3_finalize(stmt
));
285 time_t journal_store_lookup_entry_time (JournalStore
*js
, int itemid
) {
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
) {
293 SQLCHECK(sqlite3_finalize(stmt
));
296 ret
= sqlite3_column_int(stmt
, 0);
297 SQLCHECK(sqlite3_finalize(stmt
));
302 JamAccount
*journal_store_get_account (JournalStore
*js
) {
307 gboolean
journal_store_begin_group (JournalStore
*js
, GError
**err
) {
308 return begin_t(js
->db
, err
);
312 gboolean
journal_store_end_group (JournalStore
*js
, GError
**err
) {
313 return end_t(js
->db
, err
);
317 gboolean
journal_store_put (JournalStore
*js
, LJEntry
*entry
, GError
**err
) {
318 sqlite3_stmt
*stmt
= NULL
;
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
)));
343 if (entry
->security
.type
!= LJ_SECURITY_PUBLIC
) {
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
:
353 SQLCHECK(sqlite3_bind_int(stmt
, 17, sec
));
355 ret
= sqlite3_step(stmt
);
356 if (ret
!= SQLITE_DONE
) {
357 SQLCHECK(ret
/* step */ );
358 SQLCHECK(sqlite3_finalize(stmt
));
361 SQLCHECK(sqlite3_finalize(stmt
));
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
;
377 guint32
journal_store_get_month_entries (JournalStore
*js
, int year
, int mon
) {
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);
388 ret
= sqlite3_step(stmt
);
390 if (ret
!= SQLITE_DONE
) {
391 SQLCHECK(ret
/* step */ );
392 SQLCHECK(sqlite3_finalize(stmt
));
395 SQLCHECK(sqlite3_finalize(stmt
));
400 gboolean
journal_store_get_day_entries (JournalStore
*js
, int year
, int mon
, int day
, JournalStoreSummaryCallback cb_func
, gpointer cb_data
) {
402 sqlite3_stmt
*stmt
= NULL
;
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
);
419 if (ret
!= SQLITE_DONE
) {
421 SQLCHECK(sqlite3_finalize(stmt
));
424 SQLCHECK(sqlite3_finalize(stmt
));
429 gboolean
journal_store_scan (JournalStore
*js
,
430 JournalStoreScanCallback scan_cb
, const gpointer scan_data
, JournalStoreSummaryCallback cb_func
, const gpointer cb_data
)
433 sqlite3_stmt
*stmt
= NULL
;
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;
449 ret
= sqlite3_step(stmt
);
451 if (ret
!= SQLITE_ROW
&& ret
!= SQLITE_DONE
) {
453 SQLCHECK(sqlite3_finalize(stmt
));
456 SQLCHECK(sqlite3_finalize(stmt
));