1 /**
2 * @file db.c sqlite backend
3 *
4 * Copyright (C) 2007-2012 Lars Windolf <lars.windolf@gmx.de>
5 *
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
10 *
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with this program; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
19 */
20
21 #include <sqlite3.h>
22 #include <stdlib.h>
23 #include <string.h>
24
25 #include "common.h"
26 #include "conf.h"
27 #include "db.h"
28 #include "debug.h"
29 #include "item.h"
30 #include "itemset.h"
31 #include "metadata.h"
32 #include "vfolder.h"
33
34 /* You can find a schema description used by this version of Liferea at:
35 https://lzone.de/wiki/doku.php?id=liferea:v1.8:db_schema */
36
37 static sqlite3 *db = NULL;
38 gboolean searchFolderRebuild = FALSE;
39
40 /** hash of all prepared statements */
41 static GHashTable *statements = NULL;
42
43 static void db_view_remove (const gchar *id);
44
45 static void
db_prepare_stmt(sqlite3_stmt ** stmt,const gchar * sql)46 db_prepare_stmt (sqlite3_stmt **stmt, const gchar *sql)
47 {
48 gint res;
49 const char *left;
50
51 res = sqlite3_prepare_v2 (db, sql, -1, stmt, &left);
52 if ((SQLITE_BUSY == res) ||
53 (SQLITE_LOCKED == res)) {
54 g_warning ("The Liferea cache DB seems to be used by another instance (error code=%d)! Only one accessing instance is allowed.", res);
55 exit(1);
56 }
57 if (SQLITE_OK != res)
58 g_error ("Failure while preparing statement, (error=%d, %s) SQL: \"%s\"", res, sqlite3_errmsg(db), sql);
59 }
60
61 static void
db_new_statement(const gchar * name,const gchar * sql)62 db_new_statement (const gchar *name, const gchar *sql)
63 {
64
65 if (!statements)
66 statements = g_hash_table_new (g_str_hash, g_str_equal);
67
68 g_hash_table_insert (statements, (gpointer)name, (gpointer)sql);
69 }
70
71 static sqlite3_stmt *
db_get_statement(const gchar * name)72 db_get_statement (const gchar *name)
73 {
74 sqlite3_stmt *statement;
75 gchar *sql;
76
77 sql = (gchar *) g_hash_table_lookup (statements, name);
78
79 db_prepare_stmt (&statement, sql);
80
81 if (!statement)
82 g_error ("Fatal: unknown prepared statement \"%s\" requested!", name);
83
84 sqlite3_reset (statement);
85 return statement;
86 }
87
88 static void
db_exec(const gchar * sql)89 db_exec (const gchar *sql)
90 {
91 gchar *err;
92 gint res;
93
94 debug1 (DEBUG_DB, "executing SQL: %s", sql);
95 res = sqlite3_exec (db, sql, NULL, NULL, &err);
96 if (1 >= res) {
97 debug2 (DEBUG_DB, " -> result: %d (%s)", res, err?err:"success");
98 } else {
99 g_warning ("Unexpected status on SQL execution: %d (%s)", res, err?err:"success");
100 }
101 sqlite3_free (err);
102 }
103
104 static gboolean
db_table_exists(const gchar * name)105 db_table_exists (const gchar *name)
106 {
107 gchar *sql;
108 sqlite3_stmt *stmt;
109 gint res = 0;
110
111 sql = sqlite3_mprintf ("SELECT COUNT(type) FROM sqlite_master WHERE type = 'table' AND name = '%s';", name);
112 db_prepare_stmt (&stmt, sql);
113 sqlite3_reset (stmt);
114 if (SQLITE_ROW == sqlite3_step (stmt))
115 res = sqlite3_column_int (stmt, 0);
116
117 sqlite3_finalize (stmt);
118 sqlite3_free (sql);
119 return (1 == res);
120 }
121
122 static void
db_set_schema_version(gint schemaVersion)123 db_set_schema_version (gint schemaVersion)
124 {
125 gchar *err, *sql;
126
127 sql = sqlite3_mprintf ("REPLACE INTO info (name, value) VALUES ('schemaVersion',%d);", schemaVersion);
128 if (SQLITE_OK != sqlite3_exec (db, sql, NULL, NULL, &err))
129 debug1 (DEBUG_DB, "setting schema version failed: %s", err);
130 sqlite3_free (sql);
131 sqlite3_free (err);
132 }
133
134 static gint
db_get_schema_version(void)135 db_get_schema_version (void)
136 {
137 guint schemaVersion = 0;
138 sqlite3_stmt *stmt;
139
140 if (!db_table_exists ("info")) {
141 db_exec ("CREATE TABLE info ( "
142 " name TEXT, "
143 " value TEXT, "
144 " PRIMARY KEY (name) "
145 ");");
146 db_set_schema_version (-1);
147 }
148
149 db_prepare_stmt (&stmt, "SELECT value FROM info WHERE name = 'schemaVersion'");
150 if (SQLITE_ROW == sqlite3_step (stmt))
151 schemaVersion = sqlite3_column_int (stmt, 0);
152 sqlite3_finalize (stmt);
153
154 return schemaVersion;
155 }
156
157 static void
db_begin_transaction(void)158 db_begin_transaction (void)
159 {
160 gchar *sql, *err;
161 gint res;
162
163 sql = sqlite3_mprintf ("BEGIN");
164 res = sqlite3_exec (db, sql, NULL, NULL, &err);
165 if (SQLITE_OK != res)
166 g_warning ("Transaction begin failed (%s) SQL: %s", err, sql);
167 sqlite3_free (sql);
168 sqlite3_free (err);
169 }
170
171 static void
db_end_transaction(void)172 db_end_transaction (void)
173 {
174 gchar *sql, *err;
175 gint res;
176
177 sql = sqlite3_mprintf ("END");
178 res = sqlite3_exec (db, sql, NULL, NULL, &err);
179 if (SQLITE_OK != res)
180 g_warning ("Transaction end failed (%s) SQL: %s", err, sql);
181 sqlite3_free (sql);
182 sqlite3_free (err);
183 }
184
185 #define VACUUM_ON_FRAGMENTATION_RATIO 10
186
187 static void
db_vacuum(void)188 db_vacuum (void)
189 {
190 sqlite3_stmt *stmt;
191 gint res, page_count, freelist_count;
192
193 /* Determine fragmentation ratio using
194
195 PRAGMA page_count
196 PRAGMA freelist_count
197
198 as suggested by adriatic in this blog post
199 http://jeff.ecchi.ca/blog/2011/12/24/investigating-lifereas-startup-performance/#comment-19989
200 and perform VACUUM only when needed.
201 */
202
203 db_prepare_stmt (&stmt, "PRAGMA page_count");
204 sqlite3_reset (stmt);
205 res = sqlite3_step (stmt);
206 if (SQLITE_ROW != res)
207 g_error ("Could not determine page count (error code %d)!", res);
208 page_count = sqlite3_column_int (stmt, 0);
209 sqlite3_finalize (stmt);
210
211 db_prepare_stmt (&stmt, "PRAGMA freelist_count");
212 sqlite3_reset (stmt);
213 res = sqlite3_step (stmt);
214 if (SQLITE_ROW != res)
215 g_error ("Could not determine free list count (error code %d)!", res);
216 freelist_count = sqlite3_column_int (stmt, 0);
217 sqlite3_finalize (stmt);
218
219 float fragmentation = (100 * (float)freelist_count/page_count);
220 if (fragmentation > VACUUM_ON_FRAGMENTATION_RATIO) {
221 debug2 (DEBUG_DB, "Performing VACUUM as freelist count/page count ratio %2.2f > %d",
222 fragmentation, VACUUM_ON_FRAGMENTATION_RATIO);
223 debug_start_measurement (DEBUG_DB);
224 db_exec ("VACUUM;");
225 debug_end_measurement (DEBUG_DB, "VACUUM");
226 } else {
227 debug2 (DEBUG_DB, "No VACUUM as freelist count/page count ratio %2.2f <= %d",
228 fragmentation, VACUUM_ON_FRAGMENTATION_RATIO);
229 }
230 }
231
232 static void
db_open(void)233 db_open (void)
234 {
235 gchar *filename;
236 gint res;
237
238 filename = common_create_data_filename ("liferea.db");
239 debug1 (DEBUG_DB, "Opening DB file %s...", filename);
240 res = sqlite3_open_v2 (filename, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
241 if (SQLITE_OK != res)
242 g_error ("Data base file %s could not be opened (error code %d: %s)...", filename, res, sqlite3_errmsg (db));
243 g_free (filename);
244
245 sqlite3_extended_result_codes (db, TRUE);
246
247 db_exec("PRAGMA journal_mode=WAL");
248 db_exec("PRAGMA page_size=32768");
249 db_exec("PRAGMA synchronous=NORMAL");
250 }
251
252 #define SCHEMA_TARGET_VERSION 10
253
254 /* opening or creation of database */
255 void
db_init(void)256 db_init (void)
257 {
258 gint res;
259
260 debug_enter ("db_init");
261
262 db_open ();
263
264 /* create info table/check versioning info */
265 debug1 (DEBUG_DB, "current DB schema version: %d", db_get_schema_version ());
266
267 if (-1 == db_get_schema_version ()) {
268 /* no schema version available -> first installation without tables... */
269 db_set_schema_version (SCHEMA_TARGET_VERSION);
270 /* nothing exists yet, tables will be created below */
271 }
272
273 if (SCHEMA_TARGET_VERSION < db_get_schema_version ())
274 g_error ("Fatal: The cache database was created by a newer version of Liferea than this one!");
275
276 if (SCHEMA_TARGET_VERSION > db_get_schema_version ()) {
277 /* do table migration */
278 if (db_get_schema_version () < 5)
279 g_error ("This version of Liferea doesn't support migrating from such an old DB file!");
280
281 if (db_get_schema_version () == 5 || db_get_schema_version () == 6) {
282 debug0 (DEBUG_DB, "dropping triggers in preparation of database migration");
283 db_exec ("BEGIN; "
284 "DROP TRIGGER item_removal; "
285 "DROP TRIGGER item_insert; "
286 "END;");
287 }
288
289 if (db_get_schema_version () == 5) {
290 /* 1.4.9 -> 1.4.10 adding parent_item_id to itemset relation */
291 debug0 (DEBUG_DB, "migrating from schema version 5 to 6 (this drops all comments)");
292 db_exec ("BEGIN; "
293 "DELETE FROM itemsets WHERE comment = 1; "
294 "DELETE FROM items WHERE comment = 1; "
295 "CREATE TEMPORARY TABLE itemsets_backup(item_id,node_id,read,comment); "
296 "INSERT INTO itemsets_backup SELECT item_id,node_id,read,comment FROM itemsets; "
297 "DROP TABLE itemsets; "
298 "CREATE TABLE itemsets ("
299 " item_id INTEGER,"
300 " parent_item_id INTEGER,"
301 " node_id TEXT,"
302 " read INTEGER,"
303 " comment INTEGER,"
304 " PRIMARY KEY (item_id, node_id)"
305 "); "
306 "INSERT INTO itemsets SELECT item_id,0,node_id,read,comment FROM itemsets_backup; "
307 "DROP TABLE itemsets_backup; "
308 "REPLACE INTO info (name, value) VALUES ('schemaVersion',6); "
309 "END;");
310 }
311
312 if (db_get_schema_version () == 6) {
313 /* 1.4.15 -> 1.4.16 adding parent_node_id to itemset relation */
314 debug0 (DEBUG_DB, "migrating from schema version 6 to 7 (this drops all comments)");
315 db_exec ("BEGIN; "
316 "DELETE FROM itemsets WHERE comment = 1; "
317 "DELETE FROM items WHERE comment = 1; "
318 "CREATE TEMPORARY TABLE itemsets_backup(item_id,node_id,read,comment); "
319 "INSERT INTO itemsets_backup SELECT item_id,node_id,read,comment FROM itemsets; "
320 "DROP TABLE itemsets; "
321 "CREATE TABLE itemsets ("
322 " item_id INTEGER,"
323 " parent_item_id INTEGER,"
324 " node_id TEXT,"
325 " parent_node_id TEXT,"
326 " read INTEGER,"
327 " comment INTEGER,"
328 " PRIMARY KEY (item_id, node_id)"
329 "); "
330 "INSERT INTO itemsets SELECT item_id,0,node_id,node_id,read,comment FROM itemsets_backup; "
331 "DROP TABLE itemsets_backup; "
332 "REPLACE INTO info (name, value) VALUES ('schemaVersion',7); "
333 "END;");
334 }
335
336 if (db_get_schema_version () == 7) {
337 /* 1.7.1 -> 1.7.2 dropping the itemsets and attention_stats relation */
338 db_exec ("BEGIN; "
339 "CREATE TEMPORARY TABLE items_backup("
340 " item_id, "
341 " title, "
342 " read, "
343 " updated, "
344 " popup, "
345 " marked, "
346 " source, "
347 " source_id, "
348 " valid_guid, "
349 " description, "
350 " date, "
351 " comment_feed_id, "
352 " comment); "
353 "INSERT into items_backup SELECT ROWID, title, read, updated, popup, marked, source, source_id, valid_guid, description, date, comment_feed_id, comment FROM items; "
354 "DROP TABLE items; "
355 "CREATE TABLE items ("
356 " item_id INTEGER,"
357 " parent_item_id INTEGER,"
358 " node_id TEXT,"
359 " parent_node_id TEXT,"
360 " title TEXT,"
361 " read INTEGER,"
362 " updated INTEGER,"
363 " popup INTEGER,"
364 " marked INTEGER,"
365 " source TEXT,"
366 " source_id TEXT,"
367 " valid_guid INTEGER,"
368 " description TEXT,"
369 " date INTEGER,"
370 " comment_feed_id INTEGER,"
371 " comment INTEGER,"
372 " PRIMARY KEY (item_id)"
373 ");"
374 "INSERT INTO items SELECT itemsets.item_id, parent_item_id, node_id, parent_node_id, title, itemsets.read, updated, popup, marked, source, source_id, valid_guid, description, date, comment_feed_id, itemsets.comment FROM items_backup JOIN itemsets ON itemsets.item_id = items_backup.item_id; "
375 "DROP TABLE items_backup; "
376 "DROP TABLE itemsets; "
377 "REPLACE INTO info (name, value) VALUES ('schemaVersion',8); "
378 "END;" );
379
380 db_exec ("DROP TABLE attention_stats"); /* this is unconditional, no checks and backups needed */
381 }
382
383 if (db_get_schema_version () == 8) {
384 gchar *sql;
385 sqlite3_stmt *stmt;
386
387 /* 1.7.3 -> 1.7.4 change search folder handling */
388 db_exec ("BEGIN; "
389 "DROP TABLE view_state; "
390 "DROP TABLE update_state; "
391 "CREATE TABLE search_folder_items ("
392 " node_id STRING,"
393 " item_id INTEGER,"
394 " PRIMARY KEY (node_id, item_id)"
395 ");"
396 "REPLACE INTO info (name, value) VALUES ('schemaVersion',9); "
397 "END;" );
398
399 debug0 (DEBUG_DB, "Removing all views.");
400 sql = sqlite3_mprintf("SELECT name FROM sqlite_master WHERE type='view';");
401 res = sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL);
402 sqlite3_free (sql);
403 if (SQLITE_OK != res) {
404 debug1 (DEBUG_DB, "Could not determine views (error=%d)", res);
405 } else {
406 sqlite3_reset (stmt);
407
408 while (sqlite3_step (stmt) == SQLITE_ROW) {
409 const gchar *viewName = sqlite3_column_text (stmt, 0) + strlen("view_");
410 gchar *copySql = g_strdup_printf("INSERT INTO search_folder_items (node_id, item_id) SELECT '%s',item_id FROM view_%s;", viewName, viewName);
411
412 db_exec (copySql);
413 db_view_remove (viewName);
414
415 g_free (copySql);
416 }
417
418 sqlite3_finalize (stmt);
419 }
420 }
421
422 if (db_get_schema_version () == 9) {
423 /* A parent node id to search folder relation to allow cleanups */
424 db_exec ("BEGIN; "
425 "DROP TABLE search_folder_items; "
426 "CREATE TABLE search_folder_items ("
427 " node_id STRING,"
428 " parent_node_id STRING,"
429 " item_id INTEGER,"
430 " PRIMARY KEY (node_id, item_id)"
431 ");"
432 "REPLACE INTO info (name, value) VALUES ('schemaVersion',10); "
433 "END;" );
434
435 searchFolderRebuild = TRUE;
436 }
437 }
438
439 if (SCHEMA_TARGET_VERSION != db_get_schema_version ())
440 g_error ("Fatal: DB schema version not up-to-date! Running with --debug-db could give some hints about the problem!");
441
442 /* Vacuuming... */
443
444 db_vacuum ();
445
446 /* Schema creation */
447
448 debug_start_measurement (DEBUG_DB);
449 db_begin_transaction ();
450
451 /* 1. Create tables if they do not exist yet */
452 db_exec ("CREATE TABLE items ("
453 " item_id INTEGER,"
454 " parent_item_id INTEGER,"
455 " node_id TEXT," /* FIXME: migrate node ids to real integers */
456 " parent_node_id TEXT," /* FIXME: migrate node ids to real integers */
457 " title TEXT,"
458 " read INTEGER,"
459 " updated INTEGER,"
460 " popup INTEGER,"
461 " marked INTEGER,"
462 " source TEXT,"
463 " source_id TEXT,"
464 " valid_guid INTEGER,"
465 " description TEXT,"
466 " date INTEGER,"
467 " comment_feed_id TEXT,"
468 " comment INTEGER,"
469 " PRIMARY KEY (item_id)"
470 ");");
471
472 db_exec ("CREATE INDEX items_idx ON items (source_id);");
473 db_exec ("CREATE INDEX items_idx2 ON items (comment_feed_id);");
474 db_exec ("CREATE INDEX items_idx3 ON items (node_id);");
475 db_exec ("CREATE INDEX items_idx4 ON items (item_id);");
476 db_exec ("CREATE INDEX items_idx5 ON items (parent_item_id);");
477 db_exec ("CREATE INDEX items_idx6 ON items (parent_node_id);");
478
479 db_exec ("CREATE TABLE metadata ("
480 " item_id INTEGER,"
481 " nr INTEGER,"
482 " key TEXT,"
483 " value TEXT,"
484 " PRIMARY KEY (item_id, nr)"
485 ");");
486
487 db_exec ("CREATE INDEX metadata_idx ON metadata (item_id);");
488
489 db_exec ("CREATE TABLE subscription ("
490 " node_id STRING,"
491 " source STRING,"
492 " orig_source STRING,"
493 " filter_cmd STRING,"
494 " update_interval INTEGER,"
495 " default_interval INTEGER,"
496 " discontinued INTEGER,"
497 " available INTEGER,"
498 " PRIMARY KEY (node_id)"
499 ");");
500
501 db_exec ("CREATE TABLE subscription_metadata ("
502 " node_id STRING,"
503 " nr INTEGER,"
504 " key TEXT,"
505 " value TEXT,"
506 " PRIMARY KEY (node_id, nr)"
507 ");");
508
509 db_exec ("CREATE INDEX subscription_metadata_idx ON subscription_metadata (node_id);");
510
511 db_exec ("CREATE TABLE node ("
512 " node_id STRING,"
513 " parent_id STRING,"
514 " title STRING,"
515 " type INTEGER,"
516 " expanded INTEGER,"
517 " view_mode INTEGER,"
518 " sort_column INTEGER,"
519 " sort_reversed INTEGER,"
520 " PRIMARY KEY (node_id)"
521 ");");
522
523 db_exec ("CREATE TABLE search_folder_items ("
524 " node_id STRING,"
525 " parent_node_id STRING,"
526 " item_id INTEGER,"
527 " PRIMARY KEY (node_id, item_id)"
528 ");");
529
530 db_end_transaction ();
531 debug_end_measurement (DEBUG_DB, "table setup");
532
533 /* 2. Removing old triggers */
534 db_exec ("DROP TRIGGER item_insert;");
535 db_exec ("DROP TRIGGER item_update;");
536 db_exec ("DROP TRIGGER item_removal;");
537 db_exec ("DROP TRIGGER subscription_removal;");
538
539 /* 3. Cleanup of DB */
540
541 /* Note: do not check on subscriptions here, as non-subscription node
542 types (e.g. news bin) do contain items too. */
543 debug0 (DEBUG_DB, "Checking for items without a feed list node...\n");
544 db_exec ("DELETE FROM items WHERE comment = 0 AND node_id NOT IN "
545 "(SELECT node_id FROM node);");
546
547 debug0 (DEBUG_DB, "Checking for comments without parent item...\n");
548 db_exec ("BEGIN; "
549 " CREATE TEMP TABLE tmp_id ( id );"
550 " INSERT INTO tmp_id SELECT item_id FROM items WHERE comment = 1 AND parent_item_id NOT IN (SELECT item_id FROM items WHERE comment = 0);"
551 /* limit to 1000 items as it is very slow */
552 " DELETE FROM items WHERE item_id IN (SELECT id FROM tmp_id LIMIT 1000);"
553 " DROP TABLE tmp_id;"
554 "END;");
555
556 debug0 (DEBUG_DB, "Checking for search folder items without a feed list node...\n");
557 db_exec ("DELETE FROM search_folder_items WHERE parent_node_id NOT IN "
558 "(SELECT node_id FROM node);");
559
560 debug0 (DEBUG_DB, "Checking for search folder items without a search folder...\n");
561 db_exec ("DELETE FROM search_folder_items WHERE node_id NOT IN "
562 "(SELECT node_id FROM node);");
563
564 debug0 (DEBUG_DB, "Checking for search folder with comments...\n");
565 db_exec ("DELETE FROM search_folder_items WHERE comment = 1;");
566
567 debug0 (DEBUG_DB, "Checking for subscription metadata without node...\n");
568 db_exec ("DELETE FROM subscription_metadata WHERE node_id NOT IN "
569 "(SELECT node_id FROM node);");
570
571 debug0 (DEBUG_DB, "DB cleanup finished. Continuing startup.");
572
573 /* 4. Creating triggers (after cleanup so it is not slowed down by triggers) */
574
575 /* This trigger does explicitely not remove comments! */
576 db_exec ("CREATE TRIGGER item_removal DELETE ON items "
577 "BEGIN "
578 " DELETE FROM metadata WHERE item_id = old.item_id; "
579 " DELETE FROM search_folder_items WHERE item_id = old.item_id; "
580 "END;");
581
582 db_exec ("CREATE TRIGGER subscription_removal DELETE ON subscription "
583 "BEGIN "
584 " DELETE FROM node WHERE node_id = old.node_id; "
585 " DELETE FROM subscription_metadata WHERE node_id = old.node_id; "
586 " DELETE FROM search_folder_items WHERE parent_node_id = old.node_id; "
587 "END;");
588
589 /* Note: view counting triggers are set up in the view preparation code (see db_view_create()) */
590 /* prepare statements */
591
592 db_new_statement ("itemsetLoadStmt",
593 "SELECT item_id FROM items WHERE node_id = ?");
594
595 db_new_statement ("itemsetLoadOffsetStmt",
596 "SELECT item_id FROM items WHERE comment = 0 LIMIT ? OFFSET ?");
597
598 db_new_statement ("itemsetReadCountStmt",
599 "SELECT COUNT(item_id) FROM items "
600 "WHERE read = 0 AND node_id = ?");
601
602 db_new_statement ("itemsetItemCountStmt",
603 "SELECT COUNT(item_id) FROM items "
604 "WHERE node_id = ?");
605
606 db_new_statement ("itemsetRemoveStmt",
607 "DELETE FROM items WHERE item_id = ? OR (comment = 1 AND parent_item_id = ?)");
608
609 db_new_statement ("itemsetRemoveAllStmt",
610 "DELETE FROM items WHERE node_id = ? OR (comment = 1 AND parent_node_id = ?)");
611
612 db_new_statement ("itemsetMarkAllPopupStmt",
613 "UPDATE items SET popup = 0 WHERE node_id = ?");
614
615 db_new_statement ("itemLoadStmt",
616 "SELECT "
617 "title,"
618 "read,"
619 "updated,"
620 "popup,"
621 "marked,"
622 "source,"
623 "source_id,"
624 "valid_guid,"
625 "description,"
626 "date,"
627 "comment_feed_id,"
628 "comment,"
629 "item_id,"
630 "parent_item_id, "
631 "node_id, "
632 "parent_node_id "
633 " FROM items WHERE item_id = ?");
634
635 db_new_statement ("itemUpdateStmt",
636 "REPLACE INTO items ("
637 "title,"
638 "read,"
639 "updated,"
640 "popup,"
641 "marked,"
642 "source,"
643 "source_id,"
644 "valid_guid,"
645 "description,"
646 "date,"
647 "comment_feed_id,"
648 "comment,"
649 "item_id,"
650 "parent_item_id,"
651 "node_id,"
652 "parent_node_id"
653 ") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
654
655 db_new_statement ("itemStateUpdateStmt",
656 "UPDATE items SET read=?, marked=?, updated=? "
657 "WHERE item_id=?");
658
659 db_new_statement ("duplicatesFindStmt",
660 "SELECT item_id FROM items WHERE source_id = ?");
661
662 db_new_statement ("duplicateNodesFindStmt",
663 "SELECT node_id FROM items WHERE item_id IN "
664 "(SELECT item_id FROM items WHERE source_id = ?)");
665
666 db_new_statement ("duplicatesMarkReadStmt",
667 "UPDATE items SET read = 1, updated = 0 WHERE source_id = ?");
668
669 db_new_statement ("metadataLoadStmt",
670 "SELECT key,value,nr FROM metadata WHERE item_id = ? ORDER BY nr");
671
672 db_new_statement ("metadataUpdateStmt",
673 "REPLACE INTO metadata (item_id,nr,key,value) VALUES (?,?,?,?)");
674
675 db_new_statement ("subscriptionUpdateStmt",
676 "REPLACE INTO subscription ("
677 "node_id,"
678 "source,"
679 "orig_source,"
680 "filter_cmd,"
681 "update_interval,"
682 "default_interval,"
683 "discontinued,"
684 "available"
685 ") VALUES (?,?,?,?,?,?,?,?)");
686
687 db_new_statement ("subscriptionRemoveStmt",
688 "DELETE FROM subscription WHERE node_id = ?");
689
690 db_new_statement ("subscriptionLoadStmt",
691 "SELECT "
692 "node_id,"
693 "source,"
694 "orig_source,"
695 "filter_cmd,"
696 "update_interval,"
697 "default_interval,"
698 "discontinued,"
699 "available "
700 "FROM subscription");
701
702 db_new_statement ("subscriptionMetadataLoadStmt",
703 "SELECT key,value,nr FROM subscription_metadata WHERE node_id = ? ORDER BY nr");
704
705 db_new_statement ("subscriptionMetadataUpdateStmt",
706 "REPLACE INTO subscription_metadata (node_id,nr,key,value) VALUES (?,?,?,?)");
707
708 db_new_statement ("nodeUpdateStmt",
709 "REPLACE INTO node (node_id,parent_id,title,type,expanded,view_mode,sort_column,sort_reversed) VALUES (?,?,?,?,?,?,?,?)");
710
711 db_new_statement ("itemUpdateSearchFoldersStmt",
712 "REPLACE INTO search_folder_items (node_id, parent_node_id, item_id) VALUES (?,?,?)");
713
714 db_new_statement ("itemRemoveFromSearchFolderStmt",
715 "DELETE FROM search_folder_items WHERE node_id =? AND item_id = ?;");
716
717 db_new_statement ("searchFolderLoadStmt",
718 "SELECT item_id FROM search_folder_items WHERE node_id = ?;");
719
720 db_new_statement ("searchFolderCountStmt",
721 "SELECT count(item_id) FROM search_folder_items WHERE node_id = ?;");
722
723 db_new_statement ("nodeIdListStmt",
724 "SELECT node_id FROM node;");
725
726 db_new_statement ("nodeRemoveStmt",
727 "DELETE FROM node WHERE node_id = ?;");
728
729 g_assert (sqlite3_get_autocommit (db));
730
731 debug_exit ("db_init");
732 }
733
734 void
db_deinit(void)735 db_deinit (void)
736 {
737
738 debug_enter ("db_deinit");
739
740 if (FALSE == sqlite3_get_autocommit (db))
741 g_warning ("Fatal: DB not in auto-commit mode. This is a bug. Data may be lost!");
742
743 if (statements) {
744 g_hash_table_destroy (statements);
745 statements = NULL;
746 }
747
748 if (SQLITE_OK != sqlite3_close (db))
749 g_warning ("DB close failed: %s", sqlite3_errmsg (db));
750
751 db = NULL;
752
753 debug_exit ("db_deinit");
754 }
755
756 static GSList *
db_metadata_list_append(GSList * metadata,const char * key,const char * value)757 db_metadata_list_append (GSList *metadata, const char *key, const char *value)
758 {
759 if (metadata_is_type_registered (key))
760 metadata = metadata_list_append (metadata, key, value);
761 else
762 debug1 (DEBUG_DB, "Trying to load unregistered metadata type %s from DB.", key);
763
764 return metadata;
765 }
766
767 static GSList *
db_item_metadata_load(itemPtr item)768 db_item_metadata_load(itemPtr item)
769 {
770 GSList *metadata = NULL;
771 sqlite3_stmt *stmt;
772 gint res;
773
774 stmt = db_get_statement ("metadataLoadStmt");
775 res = sqlite3_bind_int (stmt, 1, item->id);
776 if (SQLITE_OK != res)
777 g_error ("db_item_load_metadata: sqlite bind failed (error code %d)!", res);
778
779 while (sqlite3_step (stmt) == SQLITE_ROW) {
780 const char *key, *value;
781 key = (const char *) sqlite3_column_text(stmt, 0);
782 value = (const char *) sqlite3_column_text(stmt, 1);
783 if (g_str_equal (key, "enclosure"))
784 item->hasEnclosure = TRUE;
785 metadata = db_metadata_list_append (metadata, key, value);
786 }
787
788 sqlite3_finalize (stmt);
789
790 return metadata;
791 }
792
793 static void
db_item_metadata_update_cb(const gchar * key,const gchar * value,guint index,gpointer user_data)794 db_item_metadata_update_cb (const gchar *key,
795 const gchar *value,
796 guint index,
797 gpointer user_data)
798 {
799 sqlite3_stmt *stmt;
800 itemPtr item = (itemPtr)user_data;
801 gint res;
802
803 stmt = db_get_statement ("metadataUpdateStmt");
804 sqlite3_bind_int (stmt, 1, item->id);
805 sqlite3_bind_int (stmt, 2, index);
806 sqlite3_bind_text (stmt, 3, key, -1, SQLITE_TRANSIENT);
807 sqlite3_bind_text (stmt, 4, value, -1, SQLITE_TRANSIENT);
808 res = sqlite3_step (stmt);
809 if (SQLITE_DONE != res)
810 g_warning ("Update in \"metadata\" table failed (error code=%d, %s)", res, sqlite3_errmsg (db));
811
812 sqlite3_finalize (stmt);
813
814 }
815
816 static void
db_item_metadata_update(itemPtr item)817 db_item_metadata_update(itemPtr item)
818 {
819 metadata_list_foreach(item->metadata, db_item_metadata_update_cb, item);
820 }
821
822 /* Item structure loading methods */
823
824 static itemPtr
db_load_item_from_columns(sqlite3_stmt * stmt)825 db_load_item_from_columns (sqlite3_stmt *stmt)
826 {
827 const gchar *tmp;
828
829 itemPtr item = item_new ();
830
831 item->readStatus = sqlite3_column_int (stmt, 1)?TRUE:FALSE;
832 item->updateStatus = sqlite3_column_int (stmt, 2)?TRUE:FALSE;
833 item->popupStatus = sqlite3_column_int (stmt, 3)?TRUE:FALSE;
834 item->flagStatus = sqlite3_column_int (stmt, 4)?TRUE:FALSE;
835 item->validGuid = sqlite3_column_int (stmt, 7)?TRUE:FALSE;
836 item->time = sqlite3_column_int64 (stmt, 9);
837 item->commentFeedId = g_strdup ((const gchar *) sqlite3_column_text (stmt, 10));
838 item->isComment = sqlite3_column_int (stmt, 11);
839 item->id = sqlite3_column_int (stmt, 12);
840 item->parentItemId = sqlite3_column_int (stmt, 13);
841 item->nodeId = g_strdup ((const gchar *) sqlite3_column_text (stmt, 14));
842 item->parentNodeId = g_strdup ((const gchar *) sqlite3_column_text (stmt, 15));
843
844 item->title = g_strdup ((const gchar *) sqlite3_column_text(stmt, 0));
845 item->sourceId = g_strdup ((const gchar *) sqlite3_column_text(stmt, 6));
846
847 tmp = (const gchar *) sqlite3_column_text(stmt, 5);
848 if (tmp)
849 item->source = g_strdup (tmp);
850
851 tmp = (const gchar *) sqlite3_column_text(stmt, 8);
852 if (tmp)
853 item->description = g_strdup (tmp);
854 else
855 item->description = g_strdup ("");
856
857 item->metadata = db_item_metadata_load (item);
858
859 return item;
860 }
861
862 itemSetPtr
db_itemset_load(const gchar * id)863 db_itemset_load (const gchar *id)
864 {
865 sqlite3_stmt *stmt;
866 itemSetPtr itemSet;
867
868 debug1 (DEBUG_DB, "loading itemset for node \"%s\"", id);
869 itemSet = g_new0 (struct itemSet, 1);
870 itemSet->nodeId = (gchar *)id;
871
872 stmt = db_get_statement ("itemsetLoadStmt");
873 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
874
875 while (sqlite3_step (stmt) == SQLITE_ROW) {
876 itemSet->ids = g_list_append (itemSet->ids, GUINT_TO_POINTER (sqlite3_column_int (stmt, 0)));
877 }
878
879 sqlite3_finalize (stmt);
880
881 debug0 (DEBUG_DB, "loading of itemset finished");
882
883 return itemSet;
884 }
885
886 itemPtr
db_item_load(gulong id)887 db_item_load (gulong id)
888 {
889 sqlite3_stmt *stmt;
890 itemPtr item = NULL;
891
892 debug1 (DEBUG_DB, "loading item %lu", id);
893 debug_start_measurement (DEBUG_DB);
894
895 stmt = db_get_statement ("itemLoadStmt");
896 sqlite3_bind_int (stmt, 1, id);
897
898 if (sqlite3_step (stmt) == SQLITE_ROW) {
899 item = db_load_item_from_columns (stmt);
900 (void) sqlite3_step (stmt);
901 } else {
902 debug1 (DEBUG_DB, "Could not load item with id %lu!", id);
903 }
904
905 sqlite3_finalize (stmt);
906
907 debug_end_measurement (DEBUG_DB, "item load");
908
909 return item;
910 }
911
912 /* Item modification methods */
913
914 static int
db_item_set_id_cb(void * user_data,int count,char ** values,char ** columns)915 db_item_set_id_cb (void *user_data,
916 int count,
917 char **values,
918 char **columns)
919 {
920 itemPtr item = (itemPtr)user_data;
921
922 g_assert(NULL != values);
923
924 if(values[0]) {
925 /* the result in *values should be MAX(item_id),
926 so adding one should give a unique new id */
927 item->id = 1 + atol(values[0]);
928 } else {
929 /* empty table causes no result in values[0]... */
930 item->id = 1;
931 }
932
933 debug2(DEBUG_DB, "new item id=%lu for \"%s\"", item->id, item->title);
934 return 0;
935 }
936
937 static void
db_item_set_id(itemPtr item)938 db_item_set_id (itemPtr item)
939 {
940 gchar *sql, *err;
941 gint res;
942
943 g_assert (0 == item->id);
944
945 sql = sqlite3_mprintf ("SELECT MAX(item_id) FROM items");
946 res = sqlite3_exec (db, sql, db_item_set_id_cb, item, &err);
947 if (SQLITE_OK != res)
948 g_warning ("Select failed (%s) SQL: %s", err, sql);
949 sqlite3_free (sql);
950 sqlite3_free (err);
951 }
952
953 static void
db_item_search_folders_update(itemPtr item)954 db_item_search_folders_update (itemPtr item)
955 {
956 sqlite3_stmt *stmt;
957 gint res;
958 GSList *iter, *list;
959
960 /* Bail on comments which are not covered by search folders */
961 if (item->isComment)
962 return;
963
964 /* Add item to all search folders it now belongs to */
965
966 stmt = db_get_statement ("itemUpdateSearchFoldersStmt");
967 iter = list = vfolder_get_all_with_item_id (item);
968 while (iter) {
969 vfolderPtr vfolder = (vfolderPtr)iter->data;
970 sqlite3_reset (stmt);
971 sqlite3_bind_text (stmt, 1, vfolder->node->id, -1, SQLITE_TRANSIENT);
972 sqlite3_bind_text (stmt, 2, item->nodeId, -1, SQLITE_TRANSIENT);
973 sqlite3_bind_int (stmt, 3, item->id);
974 res = sqlite3_step (stmt);
975
976 if (SQLITE_DONE != res)
977 g_warning ("item add to search folder failed (error code=%d, %s)", res, sqlite3_errmsg (db));
978 iter = g_slist_next (iter);
979
980 }
981 g_slist_free (list);
982
983 sqlite3_finalize (stmt);
984
985 /* Remove item from all search folders it does not belong
986 (we do not check if it is in there, just remove it) */
987
988 stmt = db_get_statement ("itemRemoveFromSearchFolderStmt");
989 iter = list = vfolder_get_all_without_item_id (item);
990 while (iter) {
991 vfolderPtr vfolder = (vfolderPtr)iter->data;
992 sqlite3_reset (stmt);
993 sqlite3_bind_text (stmt, 1, vfolder->node->id, -1, SQLITE_TRANSIENT);
994 sqlite3_bind_int (stmt, 2, item->id);
995 res = sqlite3_step (stmt);
996
997 if (SQLITE_DONE != res)
998 g_warning ("item remove from search folder failed (error code=%d, %s)", res, sqlite3_errmsg (db));
999 iter = g_slist_next (iter);
1000
1001 }
1002 g_slist_free (list);
1003
1004 sqlite3_finalize (stmt);
1005 }
1006
1007 void
db_item_update(itemPtr item)1008 db_item_update (itemPtr item)
1009 {
1010 sqlite3_stmt *stmt;
1011 gint res;
1012
1013 debug2 (DEBUG_DB, "update of item \"%s\" (id=%lu)", item->title, item->id);
1014 debug_start_measurement (DEBUG_DB);
1015
1016 db_begin_transaction ();
1017
1018 if (!item->id) {
1019 db_item_set_id (item);
1020
1021 debug1(DEBUG_DB, "insert into table \"items\": \"%s\"", item->title);
1022 }
1023
1024 /* Update the item... */
1025 stmt = db_get_statement ("itemUpdateStmt");
1026 sqlite3_bind_text (stmt, 1, item->title, -1, SQLITE_TRANSIENT);
1027 sqlite3_bind_int (stmt, 2, item->readStatus?1:0);
1028 sqlite3_bind_int (stmt, 3, item->updateStatus?1:0);
1029 sqlite3_bind_int (stmt, 4, item->popupStatus?1:0);
1030 sqlite3_bind_int (stmt, 5, item->flagStatus?1:0);
1031 sqlite3_bind_text (stmt, 6, item->source, -1, SQLITE_TRANSIENT);
1032 sqlite3_bind_text (stmt, 7, item->sourceId, -1, SQLITE_TRANSIENT);
1033 sqlite3_bind_int (stmt, 8, item->validGuid?1:0);
1034 sqlite3_bind_text (stmt, 9, item->description, -1, SQLITE_TRANSIENT);
1035 sqlite3_bind_int64 (stmt, 10, item->time);
1036 sqlite3_bind_text (stmt, 11, item->commentFeedId, -1, SQLITE_TRANSIENT);
1037 sqlite3_bind_int (stmt, 12, item->isComment?1:0);
1038 sqlite3_bind_int (stmt, 13, item->id);
1039 sqlite3_bind_int (stmt, 14, item->parentItemId);
1040 sqlite3_bind_text (stmt, 15, item->nodeId, -1, SQLITE_TRANSIENT);
1041 sqlite3_bind_text (stmt, 16, item->parentNodeId, -1, SQLITE_TRANSIENT);
1042
1043 res = sqlite3_step (stmt);
1044
1045 if (SQLITE_DONE != res)
1046 g_warning ("item update failed (error code=%d, %s)", res, sqlite3_errmsg (db));
1047
1048 sqlite3_finalize (stmt);
1049
1050 db_item_metadata_update (item);
1051 db_item_search_folders_update (item);
1052
1053 db_end_transaction ();
1054
1055 debug_end_measurement (DEBUG_DB, "item update");
1056 }
1057
1058 void
db_item_state_update(itemPtr item)1059 db_item_state_update (itemPtr item)
1060 {
1061 sqlite3_stmt *stmt;
1062
1063 if (!item->id) {
1064 db_item_update (item);
1065 return;
1066 }
1067
1068 db_item_search_folders_update (item);
1069
1070 debug_start_measurement (DEBUG_DB);
1071
1072 stmt = db_get_statement ("itemStateUpdateStmt");
1073 sqlite3_bind_int (stmt, 1, item->readStatus?1:0);
1074 sqlite3_bind_int (stmt, 2, item->flagStatus?1:0);
1075 sqlite3_bind_int (stmt, 3, item->updateStatus?1:0);
1076 sqlite3_bind_int (stmt, 4, item->id);
1077
1078 if (sqlite3_step (stmt) != SQLITE_DONE)
1079 g_warning ("item state update failed (%s)", sqlite3_errmsg (db));
1080
1081 sqlite3_finalize (stmt);
1082
1083 debug_end_measurement (DEBUG_DB, "item state update");
1084
1085 }
1086
1087 void
db_item_remove(gulong id)1088 db_item_remove (gulong id)
1089 {
1090 sqlite3_stmt *stmt;
1091 gint res;
1092
1093 debug1 (DEBUG_DB, "removing item with id %lu", id);
1094
1095 stmt = db_get_statement ("itemsetRemoveStmt");
1096 sqlite3_bind_int (stmt, 1, id);
1097 sqlite3_bind_int (stmt, 2, id);
1098 res = sqlite3_step (stmt);
1099
1100 if (SQLITE_DONE != res)
1101 g_warning ("item remove failed (error code=%d, %s)", res, sqlite3_errmsg (db));
1102
1103 sqlite3_finalize (stmt);
1104 }
1105
1106 GSList *
db_item_get_duplicates(const gchar * guid)1107 db_item_get_duplicates (const gchar *guid)
1108 {
1109 GSList *duplicates = NULL;
1110 sqlite3_stmt *stmt;
1111 gint res;
1112
1113 debug_start_measurement (DEBUG_DB);
1114
1115 stmt = db_get_statement ("duplicatesFindStmt");
1116 res = sqlite3_bind_text (stmt, 1, guid, -1, SQLITE_TRANSIENT);
1117 if (SQLITE_OK != res)
1118 g_error ("db_item_get_duplicates: sqlite bind failed (error code %d)!", res);
1119
1120 while (sqlite3_step (stmt) == SQLITE_ROW)
1121 {
1122 gulong id = sqlite3_column_int (stmt, 0);
1123 duplicates = g_slist_append (duplicates, GUINT_TO_POINTER (id));
1124 }
1125
1126 sqlite3_finalize (stmt);
1127
1128 debug_end_measurement (DEBUG_DB, "searching for duplicates");
1129
1130 return duplicates;
1131 }
1132
1133 GSList *
db_item_get_duplicate_nodes(const gchar * guid)1134 db_item_get_duplicate_nodes (const gchar *guid)
1135 {
1136 GSList *duplicates = NULL;
1137 sqlite3_stmt *stmt;
1138 gint res;
1139
1140 debug_start_measurement (DEBUG_DB);
1141
1142 stmt = db_get_statement ("duplicateNodesFindStmt");
1143 res = sqlite3_bind_text (stmt, 1, guid, -1, SQLITE_TRANSIENT);
1144 if (SQLITE_OK != res)
1145 g_error ("db_item_get_duplicates: sqlite bind failed (error code %d)!", res);
1146
1147 while (sqlite3_step (stmt) == SQLITE_ROW)
1148 {
1149 gchar *id = g_strdup((const gchar *) sqlite3_column_text (stmt, 0));
1150 duplicates = g_slist_append (duplicates, id);
1151 }
1152
1153 sqlite3_finalize (stmt);
1154
1155 debug_end_measurement (DEBUG_DB, "searching for duplicates");
1156
1157 return duplicates;
1158 }
1159
1160 void
db_itemset_remove_all(const gchar * id)1161 db_itemset_remove_all (const gchar *id)
1162 {
1163 sqlite3_stmt *stmt;
1164 gint res;
1165
1166 debug1(DEBUG_DB, "removing all items for item set with %s", id);
1167
1168 stmt = db_get_statement ("itemsetRemoveAllStmt");
1169 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1170 sqlite3_bind_text (stmt, 2, id, -1, SQLITE_TRANSIENT);
1171 res = sqlite3_step (stmt);
1172
1173 if (SQLITE_DONE != res)
1174 g_warning ("removing all items failed (error code=%d, %s)", res, sqlite3_errmsg (db));
1175
1176 sqlite3_finalize (stmt);
1177
1178 }
1179
1180 void
db_itemset_mark_all_popup(const gchar * id)1181 db_itemset_mark_all_popup (const gchar *id)
1182 {
1183 sqlite3_stmt *stmt;
1184 gint res;
1185
1186 debug1 (DEBUG_DB, "marking all items popup for item set with %s", id);
1187
1188 stmt = db_get_statement ("itemsetMarkAllPopupStmt");
1189 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1190 res = sqlite3_step (stmt);
1191
1192 if (SQLITE_DONE != res)
1193 g_warning ("marking all items popup failed (error code=%d, %s)", res, sqlite3_errmsg(db));
1194
1195 sqlite3_finalize (stmt);
1196
1197 }
1198
1199 gboolean
db_itemset_get(itemSetPtr itemSet,gulong offset,guint limit)1200 db_itemset_get (itemSetPtr itemSet, gulong offset, guint limit)
1201 {
1202 sqlite3_stmt *stmt;
1203 gboolean success = FALSE;
1204
1205 debug2 (DEBUG_DB, "loading %d items offset %lu", limit, offset);
1206
1207 stmt = db_get_statement ("itemsetLoadOffsetStmt");
1208 sqlite3_bind_int (stmt, 1, limit);
1209 sqlite3_bind_int (stmt, 2, offset);
1210
1211 while (sqlite3_step (stmt) == SQLITE_ROW) {
1212 itemSet->ids = g_list_append (itemSet->ids, GUINT_TO_POINTER (sqlite3_column_int (stmt, 0)));
1213 success = TRUE;
1214 }
1215
1216 sqlite3_finalize (stmt);
1217
1218 return success;
1219 }
1220
1221 /* Statistics interface */
1222
1223 guint
db_itemset_get_unread_count(const gchar * id)1224 db_itemset_get_unread_count (const gchar *id)
1225 {
1226 sqlite3_stmt *stmt;
1227 gint res;
1228 guint count = 0;
1229
1230 debug_start_measurement (DEBUG_DB);
1231
1232 stmt = db_get_statement ("itemsetReadCountStmt");
1233 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1234 res = sqlite3_step (stmt);
1235
1236 if (SQLITE_ROW == res)
1237 count = sqlite3_column_int (stmt, 0);
1238 else
1239 g_warning("item read counting failed (error code=%d, %s)", res, sqlite3_errmsg (db));
1240
1241 sqlite3_finalize (stmt);
1242
1243 debug_end_measurement (DEBUG_DB, "counting unread items");
1244
1245 return count;
1246 }
1247
1248 guint
db_itemset_get_item_count(const gchar * id)1249 db_itemset_get_item_count (const gchar *id)
1250 {
1251 sqlite3_stmt *stmt;
1252 gint res;
1253 guint count = 0;
1254
1255 debug_start_measurement (DEBUG_DB);
1256
1257 stmt = db_get_statement ("itemsetItemCountStmt");
1258 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1259 res = sqlite3_step (stmt);
1260
1261 if (SQLITE_ROW == res)
1262 count = sqlite3_column_int (stmt, 0);
1263 else
1264 g_warning ("item counting failed (error code=%d, %s)", res, sqlite3_errmsg (db));
1265
1266 sqlite3_finalize (stmt);
1267
1268 debug_end_measurement (DEBUG_DB, "counting items");
1269
1270 return count;
1271 }
1272
1273 /* This method is only used for migration from old schema versions */
1274 static void
db_view_remove_triggers(const gchar * id)1275 db_view_remove_triggers (const gchar *id)
1276 {
1277 gchar *sql, *err;
1278 gint res;
1279
1280 err = NULL;
1281 sql = sqlite3_mprintf ("DROP TRIGGER view_%s_insert_before;", id);
1282 res = sqlite3_exec (db, sql, NULL, NULL, &err);
1283 if (SQLITE_OK != res)
1284 debug2 (DEBUG_DB, "Dropping trigger failed (%s) SQL: %s", err, sql);
1285 sqlite3_free (sql);
1286 sqlite3_free (err);
1287
1288 err = NULL;
1289 sql = sqlite3_mprintf ("DROP TRIGGER view_%s_insert_after;", id);
1290 res = sqlite3_exec (db, sql, NULL, NULL, &err);
1291 if (SQLITE_OK != res)
1292 debug2 (DEBUG_DB, "Dropping trigger failed (%s) SQL: %s", err, sql);
1293 sqlite3_free (sql);
1294 sqlite3_free (err);
1295
1296 err = NULL;
1297 sql = sqlite3_mprintf ("DROP TRIGGER view_%s_delete;", id);
1298 res = sqlite3_exec (db, sql, NULL, NULL, &err);
1299 if (SQLITE_OK != res)
1300 debug2 (DEBUG_DB, "Dropping trigger failed (%s) SQL: %s", err, sql);
1301 sqlite3_free (sql);
1302 sqlite3_free (err);
1303
1304 err = NULL;
1305 sql = sqlite3_mprintf ("DROP TRIGGER view_%s_update_before;", id);
1306 res = sqlite3_exec (db, sql, NULL, NULL, &err);
1307 if (SQLITE_OK != res)
1308 debug2 (DEBUG_DB, "Dropping trigger failed (%s) SQL: %s", err, sql);
1309 sqlite3_free (sql);
1310 sqlite3_free (err);
1311
1312 err = NULL;
1313 sql = sqlite3_mprintf ("DROP TRIGGER view_%s_update_after;", id);
1314 res = sqlite3_exec (db, sql, NULL, NULL, &err);
1315 if (SQLITE_OK != res)
1316 debug2 (DEBUG_DB, "Dropping trigger failed (%s) SQL: %s", err, sql);
1317 sqlite3_free (sql);
1318 sqlite3_free (err);
1319 }
1320
1321 /* This method is only used for migration from old schema versions */
1322 static void
db_view_remove(const gchar * id)1323 db_view_remove (const gchar *id)
1324 {
1325 gchar *sql, *err;
1326 gint res;
1327
1328 debug1 (DEBUG_DB, "Dropping view \"%s\"", id);
1329
1330 db_view_remove_triggers (id);
1331
1332 /* Note: no need to remove anything from view_state, as this
1333 is dropped on schema migration and this method is only
1334 used during schema migration to remove all views. */
1335
1336 sql = sqlite3_mprintf ("DROP VIEW view_%s;", id);
1337 res = sqlite3_exec (db, sql, NULL, NULL, &err);
1338 if (SQLITE_OK != res)
1339 g_warning ("Dropping view failed (%s) SQL: %s", err, sql);
1340
1341 sqlite3_free (sql);
1342 sqlite3_free (err);
1343 }
1344
1345 itemSetPtr
db_search_folder_load(const gchar * id)1346 db_search_folder_load (const gchar *id)
1347 {
1348 gint res;
1349 sqlite3_stmt *stmt;
1350 itemSetPtr itemSet;
1351
1352 debug1 (DEBUG_DB, "loading search folder node \"%s\"", id);
1353
1354 stmt = db_get_statement ("searchFolderLoadStmt");
1355 res = sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1356 if (SQLITE_OK != res)
1357 g_error ("db_search_folder_load: sqlite bind failed (error code %d)!", res);
1358
1359 itemSet = g_new0 (struct itemSet, 1);
1360 itemSet->nodeId = (gchar *)id;
1361
1362 while (sqlite3_step (stmt) == SQLITE_ROW) {
1363 itemSet->ids = g_list_append (itemSet->ids, GUINT_TO_POINTER (sqlite3_column_int (stmt, 0)));
1364 }
1365
1366 sqlite3_finalize (stmt);
1367
1368 debug1 (DEBUG_DB, "loading search folder finished (%d items)", g_list_length (itemSet->ids));
1369
1370 return itemSet;
1371 }
1372
1373 void
db_search_folder_reset(const gchar * id)1374 db_search_folder_reset (const gchar *id)
1375 {
1376 gchar *sql, *err;
1377 gint res;
1378
1379 debug1 (DEBUG_DB, "resetting search folder node \"%s\"", id);
1380
1381 sql = sqlite3_mprintf ("DELETE FROM search_folder_items WHERE node_id = '%s';", id);
1382 res = sqlite3_exec (db, sql, NULL, NULL, &err);
1383 if (SQLITE_OK != res)
1384 g_warning ("resetting search folder failed (%s) SQL: %s", err, sql);
1385
1386 sqlite3_free (sql);
1387 sqlite3_free (err);
1388
1389 debug0 (DEBUG_DB, "removing search folder finished");
1390 }
1391
1392 void
db_search_folder_add_items(const gchar * id,GSList * items)1393 db_search_folder_add_items (const gchar *id, GSList *items)
1394 {
1395 sqlite3_stmt *stmt;
1396 GSList *iter;
1397 gint res;
1398
1399 debug2 (DEBUG_DB, "add %d items to search folder node \"%s\"", g_slist_length (items), id);
1400
1401 stmt = db_get_statement ("itemUpdateSearchFoldersStmt");
1402
1403 iter = items;
1404 while (iter) {
1405 itemPtr item = (itemPtr)iter->data;
1406
1407 sqlite3_reset (stmt);
1408 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1409 sqlite3_bind_text (stmt, 2, item->nodeId, -1, SQLITE_TRANSIENT);
1410 sqlite3_bind_int (stmt, 3, item->id);
1411 res = sqlite3_step (stmt);
1412 if (SQLITE_DONE != res)
1413 g_error ("db_search_folder_add_items: sqlite3_step (error code %d)!", res);
1414
1415 iter = g_slist_next (iter);
1416
1417 }
1418
1419 sqlite3_finalize (stmt);
1420
1421 debug0 (DEBUG_DB, "adding items to search folder finished");
1422 }
1423
1424 guint
db_search_folder_get_item_count(const gchar * id)1425 db_search_folder_get_item_count (const gchar *id)
1426 {
1427 sqlite3_stmt *stmt;
1428 gint res;
1429 guint count = 0;
1430
1431 debug_start_measurement (DEBUG_DB);
1432
1433 stmt = db_get_statement ("searchFolderCountStmt");
1434 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1435 res = sqlite3_step (stmt);
1436
1437 if (SQLITE_ROW == res)
1438 count = sqlite3_column_int (stmt, 0);
1439 else
1440 g_warning("item read counting failed (error code=%d, %s)", res, sqlite3_errmsg (db));
1441
1442 sqlite3_finalize (stmt);
1443
1444 debug_end_measurement (DEBUG_DB, "counting unread items");
1445
1446 return count;
1447 }
1448
1449 static GSList *
db_subscription_metadata_load(const gchar * id)1450 db_subscription_metadata_load (const gchar *id)
1451 {
1452 GSList *metadata = NULL;
1453 sqlite3_stmt *stmt;
1454 gint res;
1455
1456 stmt = db_get_statement ("subscriptionMetadataLoadStmt");
1457 res = sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1458 if (SQLITE_OK != res)
1459 g_error ("db_subscription_metadata_load: sqlite bind failed (error code %d)!", res);
1460
1461 while (sqlite3_step (stmt) == SQLITE_ROW) {
1462 metadata = db_metadata_list_append (metadata, (const char *) sqlite3_column_text(stmt, 0),
1463 (const char *) sqlite3_column_text(stmt, 1));
1464 }
1465
1466 sqlite3_finalize (stmt);
1467
1468 return metadata;
1469 }
1470
1471 static void
db_subscription_metadata_update_cb(const gchar * key,const gchar * value,guint index,gpointer user_data)1472 db_subscription_metadata_update_cb (const gchar *key,
1473 const gchar *value,
1474 guint index,
1475 gpointer user_data)
1476 {
1477 sqlite3_stmt *stmt;
1478 nodePtr node = (nodePtr)user_data;
1479 gint res;
1480
1481 stmt = db_get_statement ("subscriptionMetadataUpdateStmt");
1482 sqlite3_bind_text (stmt, 1, node->id, -1, SQLITE_TRANSIENT);
1483 sqlite3_bind_int (stmt, 2, index);
1484 sqlite3_bind_text (stmt, 3, key, -1, SQLITE_TRANSIENT);
1485 sqlite3_bind_text (stmt, 4, value, -1, SQLITE_TRANSIENT);
1486 res = sqlite3_step (stmt);
1487 if (SQLITE_DONE != res)
1488 g_warning ("Update in \"subscription_metadata\" table failed (error code=%d, %s)", res, sqlite3_errmsg (db));
1489
1490 sqlite3_finalize (stmt);
1491 }
1492
1493 static void
db_subscription_metadata_update(subscriptionPtr subscription)1494 db_subscription_metadata_update (subscriptionPtr subscription)
1495 {
1496 metadata_list_foreach (subscription->metadata, db_subscription_metadata_update_cb, subscription->node);
1497 }
1498
1499 void
db_subscription_load(subscriptionPtr subscription)1500 db_subscription_load (subscriptionPtr subscription)
1501 {
1502 subscription->metadata = db_subscription_metadata_load (subscription->node->id);
1503 }
1504
1505 void
db_subscription_update(subscriptionPtr subscription)1506 db_subscription_update (subscriptionPtr subscription)
1507 {
1508 sqlite3_stmt *stmt;
1509 gint res;
1510
1511 debug1 (DEBUG_DB, "updating subscription info %s", subscription->node->id);
1512 debug_start_measurement (DEBUG_DB);
1513
1514 stmt = db_get_statement ("subscriptionUpdateStmt");
1515 sqlite3_bind_text (stmt, 1, subscription->node->id, -1, SQLITE_TRANSIENT);
1516 sqlite3_bind_text (stmt, 2, subscription->source, -1, SQLITE_TRANSIENT);
1517 sqlite3_bind_text (stmt, 3, subscription->origSource, -1, SQLITE_TRANSIENT);
1518 sqlite3_bind_text (stmt, 4, subscription->filtercmd, -1, SQLITE_TRANSIENT);
1519 sqlite3_bind_int (stmt, 5, subscription->updateInterval);
1520 sqlite3_bind_int (stmt, 6, subscription->defaultInterval);
1521 sqlite3_bind_int (stmt, 7, subscription->discontinued?1:0);
1522 sqlite3_bind_int (stmt, 8, (subscription->updateError ||
1523 subscription->httpError ||
1524 subscription->filterError)?1:0);
1525
1526 res = sqlite3_step (stmt);
1527 if (SQLITE_DONE != res)
1528 g_warning ("Could not update subscription info for node id %s in DB (error code %d)!", subscription->node->id, res);
1529
1530 sqlite3_finalize (stmt);
1531
1532 db_subscription_metadata_update (subscription);
1533
1534 debug_end_measurement (DEBUG_DB, "subscription update");
1535 }
1536
1537 void
db_subscription_remove(const gchar * id)1538 db_subscription_remove (const gchar *id)
1539 {
1540 sqlite3_stmt *stmt;
1541 gint res;
1542
1543 debug1 (DEBUG_DB, "removing subscription %s", id);
1544 debug_start_measurement (DEBUG_DB);
1545
1546 stmt = db_get_statement ("subscriptionRemoveStmt");
1547 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1548
1549 res = sqlite3_step (stmt);
1550 if (SQLITE_DONE != res)
1551 g_warning ("Could not remove subscription %s from DB (error code %d)!", id, res);
1552
1553 sqlite3_finalize (stmt);
1554
1555 debug_end_measurement (DEBUG_DB, "subscription remove");
1556 }
1557
1558 void
db_node_update(nodePtr node)1559 db_node_update (nodePtr node)
1560 {
1561 sqlite3_stmt *stmt;
1562 gint res;
1563
1564 debug1 (DEBUG_DB, "updating node info %s", node->id);
1565 debug_start_measurement (DEBUG_DB);
1566
1567 stmt = db_get_statement ("nodeUpdateStmt");
1568 sqlite3_bind_text (stmt, 1, node->id, -1, SQLITE_TRANSIENT);
1569 sqlite3_bind_text (stmt, 2, node->parent->id, -1, SQLITE_TRANSIENT);
1570 sqlite3_bind_text (stmt, 3, node->title, -1, SQLITE_TRANSIENT);
1571 sqlite3_bind_text (stmt, 4, node_type_to_str (node), -1, SQLITE_TRANSIENT);
1572 sqlite3_bind_int (stmt, 5, node->expanded?1:0);
1573 sqlite3_bind_int (stmt, 6, node->viewMode);
1574 sqlite3_bind_int (stmt, 7, node->sortColumn);
1575 sqlite3_bind_int (stmt, 8, node->sortReversed?1:0);
1576
1577 res = sqlite3_step (stmt);
1578 if (SQLITE_DONE != res)
1579 g_warning ("Could not update node info %s in DB (error code %d)!", node->id, res);
1580
1581 sqlite3_finalize (stmt);
1582
1583 debug_end_measurement (DEBUG_DB, "node update");
1584 }
1585
1586 static gboolean
db_node_find(nodePtr node,gpointer id)1587 db_node_find (nodePtr node, gpointer id)
1588 {
1589 GSList *iter;
1590
1591 if (g_str_equal (node->id, (gchar *)id))
1592 return TRUE;
1593
1594 iter = node->children;
1595 while (iter) {
1596 if (db_node_find ((nodePtr)iter->data, id))
1597 return TRUE;
1598 iter = g_slist_next (iter);
1599 }
1600
1601 return FALSE;
1602 }
1603
1604 static void
db_node_remove(const gchar * id)1605 db_node_remove (const gchar *id)
1606 {
1607 sqlite3_stmt *stmt;
1608 gint res;
1609
1610 stmt = db_get_statement ("nodeRemoveStmt");
1611 sqlite3_bind_text (stmt, 1, id, -1, SQLITE_TRANSIENT);
1612
1613 res = sqlite3_step (stmt);
1614 if (SQLITE_DONE != res)
1615 g_warning ("Could not remove node %s in DB (error code %d)!", id, res);
1616
1617 sqlite3_finalize (stmt);
1618 }
1619
1620 void
db_node_cleanup(nodePtr root)1621 db_node_cleanup (nodePtr root)
1622 {
1623 sqlite3_stmt *stmt;
1624
1625 debug0 (DEBUG_DB, "Cleaning node ids...");
1626
1627 /* Fetch all node ids */
1628 stmt = db_get_statement ("nodeIdListStmt");
1629 while (sqlite3_step (stmt) == SQLITE_ROW) {
1630 /* Drop node ids not in feed list anymore */
1631 const gchar *id = (const gchar *) sqlite3_column_text (stmt, 0);
1632 if (id && !db_node_find (root, (gpointer)id)) {
1633 db_subscription_remove (id); /* in case it is a subscription */
1634 db_node_remove (id); /* in case it is a folder */
1635 }
1636 }
1637
1638 sqlite3_finalize (stmt);
1639 }
1640