Add trivial DB conversion tool
[fuse-music.git] / C / db_convert.c
1 #include <sqlite3.h>
2 #include <string.h>
3 #include <stdlib.h>
4 #include <stdio.h>
5 #include <errno.h>
6 #include <sys/types.h>
7 #include <sys/stat.h>
8 #include <unistd.h>
9 #include <dirent.h>
10 #include <pthread.h>
11 #include <ctype.h>
12 #include "fmdb.h"
13 #include "misc.h"
14
15 FILE *logfile = NULL;
16
17 #define DB_COUNT_DEBUG
18
19 #define DB_DEBUG 2
20
21 #if DB_DEBUG >= 0
22 #   define DBLOG0(x) LOGPRINT(x)
23 #else
24 #   define DBLOG0(x)
25 #endif
26
27 #if DB_DEBUG >= 1
28 #   define DBLOG1(x) LOGPRINT(x)
29 #else
30 #   define DBLOG1(x)
31 #endif
32
33 #if DB_DEBUG >= 2
34 #   define DBLOG2(x) LOGPRINT(x)
35 #else
36 #   define DBLOG2(x)
37 #endif
38
39 static pthread_mutex_t db_mutex = PTHREAD_MUTEX_INITIALIZER;
40 static long long db_cache_space_used_value = 0;
41
42 typedef struct
43 {
44     sqlite3 *db;
45 } db_state_t;
46
47 enum result_type
48 {
49     RES_SIZE,
50     RES_CACHE,
51     RES_COUNT
52 };
53
54 struct results
55 {
56     struct results *next;
57     struct results *prev;
58     enum result_type type;
59     union
60     {
61         db_size_entry_t size;
62         db_cache_entry_t cache;
63         db_count_entry_t count;
64     } data;
65 };
66
67 struct results *res_head = NULL;
68 struct results *res_current = NULL;
69 struct results *res_tail = NULL;
70
71 static void free_results(void)
72 {
73     struct results *entry = res_head;
74     struct results *current = res_head;
75     
76     while(entry)
77     {
78         entry = entry->next;
79         free(current);
80         current = entry;
81     }
82     res_head = NULL;
83     res_current = NULL;
84     res_tail = NULL;
85 }
86
87 static int results_callback(void *pArg, int argc, char **argv, char **columnNames)
88 {
89     struct results *entry = calloc(1, sizeof (*entry));
90     enum result_type *type = pArg;
91
92     if (res_tail)
93         res_tail->next = entry;
94     if (!res_head)
95         res_head = entry;
96
97     entry->prev = res_tail;
98     res_tail = entry;
99
100     switch (*type)
101     {
102         case RES_SIZE:
103             if (argv[0])
104                 strncpy(entry->data.size.flac_path, argv[0], sizeof(entry->data.size.flac_path));
105             if (argv[1])
106                 strncpy(entry->data.size.format_choice, argv[1], sizeof(entry->data.size.format_choice));
107             if (argv[2])
108             {
109                 strncpy(entry->data.size.format_quality, argv[2], sizeof(entry->data.size.format_quality));
110             }
111             if (argv[3])
112             {
113                 entry->data.size.size = strtoull(argv[3], NULL, 10);
114             }
115             if (argv[4])
116                 entry->data.size.mtime = strtoul(argv[4], NULL, 10);
117             break;
118         case RES_CACHE:
119             if (argv[0])
120                 strncpy(entry->data.cache.flac_path, argv[0], sizeof(entry->data.cache.flac_path));
121             if (argv[1])
122                 strncpy(entry->data.cache.cache_path, argv[1], sizeof(entry->data.cache.cache_path));
123             if (argv[2])
124                 entry->data.cache.state = strtoul(argv[2], NULL, 10);
125             if (argv[3])
126                 entry->data.cache.size = strtoull(argv[3], NULL, 10);
127             if (argv[4])
128                 entry->data.cache.mtime = strtoul(argv[4], NULL, 10);
129             break;
130         case RES_COUNT:
131             if (argv[0])
132                 entry->data.count.count = strtol(argv[0], NULL, 10);
133             break;
134     }
135     return 0;
136 }
137
138 /* Needs no locking, internal function called at startup when we're
139  * still single threaded */
140 static int db_create_size_table(db_state_t *dbp)
141 {
142     int error = 0;
143     char *open_error;
144     char sql_command[2 * PATH_MAX];
145     
146     /* Delete the table and create new */
147     error = sqlite3_exec(dbp->db, "DROP TABLE sizes;", NULL, NULL, NULL);
148     sprintf(sql_command, "CREATE TABLE sizes ("
149             "flac_path VARCHAR(%d),"
150             "format_choice VARCHAR(10),"
151             "format_quality VARCHAR(10),"
152             "size INTEGER,"
153             "mtime INTEGER);", PATH_MAX);
154     error = sqlite3_exec(dbp->db, sql_command, NULL, NULL, &open_error);
155     if (error)
156     {
157         DBLOG0((logfile, "%s: got error %d (%s) from create\n",
158                 __func__, error, open_error));
159         if (open_error)
160             sqlite3_free(open_error);
161         return error;
162     }
163     return 0;
164 }
165
166 void db_dump_size_entry(const db_size_entry_t *entry)
167 {
168     DBLOG0((logfile, "  flac_path: %s\n", entry->flac_path));
169     DBLOG0((logfile, "  format_choice: %s\n", entry->format_choice));
170     DBLOG0((logfile, "  format_quality: %s\n", entry->format_quality));
171     DBLOG0((logfile, "  size: %lld\n", entry->size));
172     DBLOG0((logfile, "  mtime: %ld\n", entry->mtime));
173 }
174
175 static int db_dump_size_unlocked(db_state_t *state)
176 {
177     int error = 0;
178     char *open_error;
179     int result_type = RES_SIZE;
180     char sql_command[2 * PATH_MAX];
181     int i = 0;
182
183     free_results();
184     sprintf(sql_command, "SELECT * FROM sizes");
185     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
186     if (error)
187     {
188         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
189                 __func__, error, open_error));
190         if (open_error)
191             sqlite3_free(open_error);
192         return error;
193     }
194
195     res_current = res_head;
196     DBLOG0((logfile, "%s: db dump\n", __func__));
197     while (res_current)
198     {
199         DBLOG0((logfile, "entry %d:\n", i++));
200         db_dump_size_entry(&res_current->data.size);
201         res_current = res_current->next;
202     }
203
204     return error;
205 }
206
207 int db_dump_size(FMDB *dbp)
208 {
209     int error = 0;
210
211     pthread_mutex_lock(&db_mutex);
212     free_results();
213
214     error = db_dump_size_unlocked(dbp);
215     pthread_mutex_unlock(&db_mutex);
216     return error;
217 }
218
219 #ifdef DB_COUNT_DEBUG
220 static int db_count_size(FMDB *dbp)
221 {
222     int error = 0;
223     db_state_t *state = dbp;
224     char *open_error;
225     int result_type = RES_COUNT;
226
227     free_results();
228
229     error = sqlite3_exec(state->db, "SELECT COUNT(*) FROM sizes;", results_callback, &result_type, &open_error);
230     if (error)
231     {
232         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
233                 __func__, error, open_error));
234         if (open_error)
235             sqlite3_free(open_error);
236         return error;
237     }
238
239     res_current = res_head;
240     if (res_current)
241     {
242         DBLOG1((logfile, "%s: %d entries in the sizes DB\n", 
243                 __func__, res_current->data.count.count));
244     }
245     else
246         error = ENOENT;
247
248     return error;
249 }
250 #endif
251
252 int db_store_size_entry(FMDB *dbp, const db_size_entry_t *in)
253 {
254     int error = 0;
255     db_state_t *state = dbp;
256     char *open_error;
257     char sql_command[2 * PATH_MAX];
258
259     pthread_mutex_lock(&db_mutex);
260     free_results();
261
262     /* Delete any existing entries */
263     sprintf(sql_command,
264             "DELETE FROM sizes WHERE flac_path == '%s'",
265             in->flac_path);
266     error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
267     if (error)
268     {
269         DBLOG2((logfile, "%s: Failed to remove old entry for %s, error %d (%s)\n",
270                 __func__, in->flac_path, error, open_error));
271         if (open_error)
272             sqlite3_free(open_error);
273     }
274     else
275         DBLOG2((logfile, "%s: Deleted old entry for %s\n", __func__, in->flac_path));
276
277     DBLOG2((logfile, "%s: storing %s:%s:%s:%lld:%ld\n",
278             __func__, in->flac_path, in->format_choice, in->format_quality,
279             in->size, in->mtime));
280     sprintf(sql_command, "INSERT INTO sizes VALUES('%s','%s','%s',%lld,%ld);",
281             in->flac_path, in->format_choice, in->format_quality,
282             in->size, in->mtime);
283     error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
284     if (error)
285     {
286         DBLOG0((logfile, "%s: Failed to write entry, error %d (%s)\n",
287                 __func__, error, open_error));
288         if (open_error)
289             sqlite3_free(open_error);
290         pthread_mutex_unlock(&db_mutex);
291         return error;
292     }
293     pthread_mutex_unlock(&db_mutex);
294     return error;
295 }
296
297 int db_lookup_size_entry(FMDB *dbp, const db_size_entry_t *in, db_size_entry_t *out)
298 {
299     int error = 0;
300     db_state_t *state = dbp;
301     char *open_error;
302     int result_type = RES_SIZE;
303     char sql_command[2 * PATH_MAX];
304
305     pthread_mutex_lock(&db_mutex);
306     free_results();
307     sprintf(sql_command,
308             "SELECT * FROM sizes WHERE flac_path == '%s' AND format_choice == '%s' AND format_quality == %s;",
309             in->flac_path, in->format_choice, in->format_quality);
310     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
311     if (error)
312     {
313         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n", __func__, error, open_error));
314         if (open_error)
315             sqlite3_free(open_error);
316         pthread_mutex_unlock(&db_mutex);
317         return error;
318     }
319
320     res_current = res_head;
321     if (res_current)
322     {
323         memcpy(out, &res_current->data.size, sizeof(db_size_entry_t));
324         res_current = res_current->next;
325         DBLOG2((logfile, "%s: copying %s:%s:%s:%lld:%ld\n",
326                 __func__, out->flac_path, out->format_choice, out->format_quality,
327                 out->size, out->mtime));
328     }
329     else
330         error = ENOENT;
331
332     pthread_mutex_unlock(&db_mutex);
333     return error;
334 }
335
336 int db_remove_size_entry(FMDB *dbp, const db_size_entry_t *in)
337 {
338     int error = 0;
339     db_state_t *state = dbp;
340     char *open_error;
341     char sql_command[2 * PATH_MAX];
342
343     pthread_mutex_lock(&db_mutex);
344     free_results();
345     sprintf(sql_command,
346             "DELETE FROM sizes WHERE flac_path == '%s' AND format_choice == '%s' AND format_quality == %s;",
347             in->flac_path, in->format_choice, in->format_quality);
348     error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
349     if (error)
350     {
351         DBLOG0((logfile, "%s: Failed to remove, error %d (%s)\n",
352                 __func__, error, open_error));
353         if (open_error)
354             sqlite3_free(open_error);
355     }
356     pthread_mutex_unlock(&db_mutex);
357     return error;
358 }
359
360 /* Needs no locking, internal function called at startup when we're
361  * still single threaded */
362 static int db_create_cache_table(db_state_t *dbp)
363 {
364     int error = 0;
365     char *open_error;
366     char sql_command[2 * PATH_MAX];
367     
368     /* Delete the table and create new */
369     error = sqlite3_exec(dbp->db, "DROP TABLE cache;", NULL, NULL, NULL);
370     sprintf(sql_command, "CREATE TABLE cache ("
371             "flac_path VARCHAR(%d),"
372             "cache_path VARCHAR(%d),"
373             "state INTEGER,"
374             "size INTEGER,"
375             "mtime INTEGER);", PATH_MAX, PATH_MAX);
376     error = sqlite3_exec(dbp->db, sql_command, NULL, NULL, &open_error);
377     if (error)
378     {
379         DBLOG0((logfile, "%s: got error %d (%s) from create\n",
380                 __func__, error, open_error));
381         if (open_error)
382             sqlite3_free(open_error);
383         return error;
384     }
385     return 0;
386 }
387
388 #ifdef DB_DEBUG
389 static char *str_state(db_cache_state_e state)
390 {
391     switch (state)
392     {
393         case FMDB_CACHE_FREE:
394             return "FMDB_CACHE_FREE";
395         case FMDB_CACHE_ENCODING:
396             return "FMDB_CACHE_ENCODING";
397         case FMDB_CACHE_READING:
398             return "FMDB_CACHE_READING";
399         case FMDB_CACHE_DELETING:
400             return "FMDB_CACHE_DELETING";
401         default:
402             return "BOGUS STATE";
403     }
404 }
405 #endif
406
407 void db_dump_cache_entry(const db_cache_entry_t *entry)
408 {
409     DBLOG0((logfile, "  flac_path: %s\n", entry->flac_path));
410     DBLOG0((logfile, "  cache_path: %s\n", entry->cache_path));
411     DBLOG0((logfile, "  state: %s\n", str_state(entry->state)));
412     DBLOG0((logfile, "  size: %lld\n", entry->size));
413     DBLOG0((logfile, "  mtime: %ld\n", entry->mtime));
414 }
415
416 static int db_dump_cache_unlocked(db_state_t *state, long long *size)
417 {
418     int error = 0;
419     char *open_error;
420     int result_type = RES_CACHE;
421     char sql_command[2 * PATH_MAX];
422     int i = 0;
423     long long total_size = 0;
424
425     free_results();
426
427     sprintf(sql_command, "SELECT * FROM cache");
428     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
429     if (error)
430     {
431         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
432                 __func__, error, open_error));
433         if (open_error)
434             sqlite3_free(open_error);
435         return error;
436     }
437
438     res_current = res_head;
439     DBLOG0((logfile, "%s: db dump\n", __func__));
440     while (res_current)
441     {
442         DBLOG0((logfile, "entry %d:\n", i++));
443         db_dump_cache_entry(&res_current->data.cache);
444         total_size += res_current->data.cache.size;
445         res_current = res_current->next;
446     }
447
448     DBLOG0((logfile, "%s: %d entries, %lld total size\n", __func__, i, total_size));
449     *size = total_size;
450     db_cache_space_used_value = total_size;
451     return error;
452 }
453
454 int db_dump_cache(FMDB *dbp, long long *size)
455 {
456     int error = 0;
457
458     pthread_mutex_lock(&db_mutex);
459     free_results();
460
461     error = db_dump_cache_unlocked(dbp, size);
462     pthread_mutex_unlock(&db_mutex);
463     return error;
464 }
465
466 static int _db_cache_space_used_unlocked(FMDB *dbp, long long *size)
467 {
468     int error = 0;
469     db_state_t *state = dbp;
470     char *open_error;
471     int result_type = RES_CACHE;
472     char sql_command[2 * PATH_MAX];
473     long long total_size = 0;
474
475     free_results();
476
477     sprintf(sql_command, "SELECT * FROM cache");
478     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
479     if (error)
480     {
481         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
482                 __func__, error, open_error));
483         if (open_error)
484             sqlite3_free(open_error);
485         return error;
486     }
487
488     res_current = res_head;
489     while (res_current)
490     {
491         total_size += res_current->data.cache.size;
492         res_current = res_current->next;
493     }
494
495     *size = total_size;
496     db_cache_space_used_value = total_size;
497     return error;
498 }
499
500 int db_cache_space_used(FMDB *dbp, long long *size)
501 {
502     *size = db_cache_space_used_value;
503     return 0;
504 }
505
506 static int db_count_cache(FMDB *dbp, int *num_entries)
507 {
508     int error = 0;
509     db_state_t *state = dbp;
510     char *open_error;
511     int result_type = RES_COUNT;
512
513     free_results();
514
515     error = sqlite3_exec(state->db, "SELECT COUNT(*) FROM cache;", results_callback, &result_type, &open_error);
516     if (error)
517     {
518         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
519                 __func__, error, open_error));
520         if (open_error)
521             sqlite3_free(open_error);
522         return error;
523     }
524
525     res_current = res_head;
526     if (res_current)
527     {
528         DBLOG1((logfile, "%s: %d entries in the cache DB\n", 
529                 __func__, res_current->data.count.count));
530         *num_entries = res_current->data.count.count;
531     }
532     else
533         error = ENOENT;
534
535     return error;
536 }
537
538 int db_store_cache_entry(FMDB *dbp, const db_cache_entry_t *in)
539 {
540     int error = 0;
541     db_state_t *state = dbp;
542     char *open_error;
543     char sql_command[2 * PATH_MAX];
544     long long junk;
545
546     pthread_mutex_lock(&db_mutex);
547     /* Delete any existing entries */
548     sprintf(sql_command,
549             "DELETE FROM cache WHERE flac_path == '%s'",
550             in->flac_path);
551     error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
552     if (error)
553     {
554         DBLOG1((logfile, "%s: Failed to remove old entry for %s, error %d (%s)\n",
555                 __func__, in->flac_path, error, open_error));
556         if (open_error)
557             sqlite3_free(open_error);
558     }
559     else
560     {
561         DBLOG1((logfile, "%s: Deleted old entry for %s\n", __func__, in->flac_path));
562     }
563     /* Now write the new entry */
564     DBLOG2((logfile, "%s: storing %s:%s:%s:%lld:%ld\n",
565             __func__, in->flac_path, in->cache_path, str_state(in->state),
566             in->size, in->mtime));
567     sprintf(sql_command, "INSERT INTO cache VALUES('%s','%s',%d,%lld,%ld);",
568             in->flac_path, in->cache_path, in->state,
569             in->size, in->mtime);
570     error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
571     if (error)
572     {
573         DBLOG0((logfile, "%s: Failed to write entry, error %d (%s)\n",
574                 __func__, error, open_error));
575         if (open_error)
576             sqlite3_free(open_error);
577         pthread_mutex_unlock(&db_mutex);
578         return error;
579     }
580     _db_cache_space_used_unlocked(state, &junk);
581     pthread_mutex_unlock(&db_mutex);
582     return error;
583 }
584
585 int db_lookup_cache_entry_by_flac_path(FMDB *dbp, const db_cache_entry_t *in, db_cache_entry_t *out)
586 {
587     int error = 0;
588     db_state_t *state = dbp;
589     char *open_error;
590     int result_type = RES_CACHE;
591 #ifdef DB_COUNT_DEBUG
592     int num_entries = 0;
593 #endif
594     char sql_command[2 * PATH_MAX];
595
596     pthread_mutex_lock(&db_mutex);
597 #ifdef DB_COUNT_DEBUG
598     db_count_cache(dbp, &num_entries);
599 #endif
600     free_results();
601
602     sprintf(sql_command,
603             "SELECT * FROM cache WHERE flac_path == '%s'",
604             in->flac_path);
605     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
606     if (error)
607     {
608         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
609                 __func__, error, open_error));
610         if (open_error)
611             sqlite3_free(open_error);
612         pthread_mutex_unlock(&db_mutex);
613         return error;
614     }
615
616     res_current = res_head;
617     if (res_current)
618     {
619         memcpy(out, &res_current->data.cache, sizeof(db_cache_entry_t));
620         res_current = res_current->next;
621         DBLOG2((logfile, "%s: copying %s:%s:%d:%lld:%ld\n",
622                 __func__, out->flac_path, out->cache_path, out->state,
623                out->size, out->mtime));
624     }
625     else
626         error = ENOENT;
627
628     pthread_mutex_unlock(&db_mutex);
629     return error;
630 }
631
632 int db_lookup_cache_entry_by_cache_path(FMDB *dbp, const db_cache_entry_t *in, db_cache_entry_t *out)
633 {
634     int error = 0;
635     db_state_t *state = dbp;
636     char *open_error;
637     int result_type = RES_CACHE;
638 #ifdef DB_COUNT_DEBUG
639     int num_entries = 0;
640 #endif
641     char sql_command[2 * PATH_MAX];
642
643     pthread_mutex_lock(&db_mutex);
644 #ifdef DB_COUNT_DEBUG
645     db_count_cache(dbp, &num_entries);
646 #endif
647     free_results();
648
649     sprintf(sql_command,
650             "SELECT * FROM cache WHERE cache_path == '%s'",
651             in->cache_path);
652     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
653     if (error)
654     {
655         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
656                 __func__, error, open_error));
657         if (open_error)
658             sqlite3_free(open_error);
659         pthread_mutex_unlock(&db_mutex);
660         return error;
661     }
662
663     res_current = res_head;
664     if (res_current)
665     {
666         memcpy(out, &res_current->data.cache, sizeof(db_cache_entry_t));
667         res_current = res_current->next;
668         DBLOG2((logfile, "%s: copying %s:%s:%d:%lld:%ld\n",
669                 __func__, out->flac_path, out->cache_path, out->state,
670                out->size, out->mtime));
671     }
672     else
673         error = ENOENT;
674
675     pthread_mutex_unlock(&db_mutex);
676     return error;
677 }
678
679 int db_lookup_cache_entry_by_lru(FMDB *dbp, db_cache_entry_t *out, int *num)
680 {
681     int error = 0;
682     db_state_t *state = dbp;
683     char *open_error;
684     int num_entries = 0;
685     int result_type = RES_CACHE;
686     char sql_command[2 * PATH_MAX];
687     long long junk;
688
689     pthread_mutex_lock(&db_mutex);
690     db_count_cache(dbp, &num_entries);
691     free_results();
692
693     sprintf(sql_command,
694             "SELECT * FROM cache WHERE state == %d ORDER BY mtime ASC LIMIT 1", FMDB_CACHE_FREE);
695 //            "SELECT * FROM cache");
696     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
697     if (error)
698     {
699         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
700                 __func__, error, open_error));
701         if (open_error)
702             sqlite3_free(open_error);
703         pthread_mutex_unlock(&db_mutex);
704         return error;
705     }
706
707     res_current = res_head;
708     if (res_current)
709     {
710         memcpy(out, &res_current->data.cache, sizeof(db_cache_entry_t));
711         res_current = res_current->next;
712         *num = num_entries;
713         DBLOG2((logfile, "%s: copying %s:%s:%d:%lld:%ld\n",
714                 __func__, out->flac_path, out->cache_path, out->state,
715                 out->size, out->mtime));
716     }
717     else
718     {
719         DBLOG0((logfile, "%s: failed to find an LRU entry\n", __func__));
720         error = ENOENT;
721         *num = num_entries;
722         db_dump_cache_unlocked(dbp, &junk);
723     }
724     
725     pthread_mutex_unlock(&db_mutex);
726     return error;
727 }
728
729 int db_remove_cache_entry(FMDB *dbp, const db_cache_entry_t *in)
730 {
731     int error = 0;
732     db_state_t *state = dbp;
733     char *open_error;
734     char sql_command[2 * PATH_MAX];
735     long long junk;
736
737     pthread_mutex_lock(&db_mutex);
738     sprintf(sql_command,
739             "DELETE FROM cache WHERE flac_path == '%s';",
740             in->flac_path);
741     error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
742     if (error)
743     {
744         DBLOG0((logfile, "%s: Failed to remove, error %d (%s)\n",
745                 __func__, error, open_error));
746         if (open_error)
747             sqlite3_free(open_error);
748     }
749     _db_cache_space_used_unlocked(state, &junk);
750     pthread_mutex_unlock(&db_mutex);
751     return error;
752 }
753
754 int sync_cache_and_db(FMDB *dbp, char *cache_dir)
755 {
756     int error = 0;
757     struct stat sb;
758     char *open_error;
759     int result_type = RES_CACHE;
760     char sql_command[2 * PATH_MAX];
761     db_state_t *state = dbp;
762 #ifdef DB_COUNT_DEBUG
763     int num_entries = 0;
764 #endif
765     int startup_db_entries = 0;
766     int removed_db_entries = 0;
767     int done = 0;
768
769     /* First, check that all the entries in the DB exist on disk; any
770      * that don't, or that look wrong: delete the DB entry */
771     DBLOG1((logfile, "%s: Checking DB entry sanity\n", __func__));
772 #ifdef DB_COUNT_DEBUG
773     db_count_cache(dbp, &num_entries);
774 #endif
775 #ifdef DB_COUNT_DEBUG
776     db_count_size(dbp);
777 #endif
778     free_results();
779
780     while (!done)
781     {
782         int entry_changed = 0;
783         
784         sprintf(sql_command, "SELECT * FROM cache;");
785         error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
786         if (error)
787         {
788             DBLOG0((logfile, "%s: Failed to lookup results, error %d (%s)\n",
789                     __func__, error, open_error));
790             if (open_error)
791                 sqlite3_free(open_error);
792             return error;
793         }
794
795         res_current = res_head;
796         /* Quick check - if we have no results (i.e. no cache entries,
797          * then break out */
798         if (!res_current)
799             done = 1;
800
801         while (res_current)
802         {
803             db_cache_entry_t *entry = &res_current->data.cache;
804             char cache_file_name[PATH_MAX];
805             
806             startup_db_entries++;
807             
808             /* Remove entries that don't look complete */
809             if (entry->flac_path == NULL ||
810                 entry->cache_path == NULL || 
811                 entry->state == FMDB_CACHE_ENCODING ||
812                 entry->state == FMDB_CACHE_DELETING)
813             {
814                 db_remove_cache_entry(dbp, entry);
815                 removed_db_entries++;
816                 entry_changed++;            }
817             else
818             {
819                 
820                 /* Fix up any entries that might be currently marked as in use */
821                 if (entry->state == FMDB_CACHE_READING)
822                 {
823                     entry->state = FMDB_CACHE_FREE;
824                     error = db_store_cache_entry(dbp, entry);
825                     if (error)
826                     {
827                         DBLOG0((logfile, "%s: can't update cache DB entry for %s; error %d\n",
828                                 __func__, entry->cache_path, error));
829                         return error;
830                     }
831                     entry_changed++;
832                 }
833         
834                 /* Now look for entries that don't have a matching
835                  * (and correct) cache file on disk */
836                 snprintf(cache_file_name, PATH_MAX, "%s/%s", cache_dir, entry->cache_path);
837                 error = stat(cache_file_name, &sb);
838                 if (error)
839                 {
840                     DBLOG1((logfile, "%s: can't stat cache file %s, error %d. Deleting DB entry\n",
841                             __func__, cache_file_name, errno));
842                     db_remove_cache_entry(dbp, entry);
843                     removed_db_entries++;
844                     entry_changed++;
845                 }
846                 else
847                 {
848                     if (sb.st_size != entry->size)
849                     {
850                         DBLOG1((logfile, "%s: cache file %s is wrong size (%lld bytes, DB said %lld). Deleting DB entry\n",
851                             __func__, cache_file_name, (long long)sb.st_size, entry->size));
852                         db_remove_cache_entry(dbp, entry);
853                         removed_db_entries++;
854                         entry_changed++;
855                     }
856                 }
857             }
858             if (!entry_changed)
859             {
860                 /* This DB entry looks OK, then. Next! */
861                 res_current = res_current->next;
862                 /* And if we've reached the end of the list without
863                  * changes, we're finished. */
864                 if (!res_current)
865                     done = 1;
866             }
867             else
868             {
869                 DBLOG0((logfile, "%s: modified a cache DB entry, restart loop\n", __func__));
870                 break;
871             }
872         }
873     }
874     free_results();
875     DBLOG1((logfile, "%s: statistics:\n", __func__));
876     DBLOG1((logfile, "  found %d cache DB entries at startup\n", startup_db_entries));
877     DBLOG1((logfile, "  cleaned up %d cache DB entries\n", removed_db_entries));
878     DBLOG1((logfile, "  now have %d cache DB entries\n", startup_db_entries - removed_db_entries));
879     return 0;
880 }
881
882 FMDB *db_open(char *db_name)
883 {
884     db_state_t *dbp = NULL;
885     int error = 0;            /* function return value */
886     long long junk;
887
888     /* Allocate state structure */
889     dbp = calloc(1, sizeof(*dbp));
890     if (dbp)
891     {
892         error = sqlite3_open(db_name, &dbp->db);
893         if (error)
894         {
895             DBLOG0((logfile, "%s: Unable to open sqlite file %s: error %d\n", __func__, db_name, error));
896             errno = error;
897             return NULL;
898         }
899         
900         /* We have a database pointer open. Do we need to init the
901          * tables? Try to grab the first row of the sizes table and
902          * see if we get an error. There has to be a better way than
903          * this! */
904         error = sqlite3_exec(dbp->db, "SELECT COUNT(*) FROM sizes;", NULL, NULL, NULL);
905         if (SQLITE_ERROR == error)
906         {
907             /* No table found, so create new */
908             /* First, the size table */
909             error = db_create_size_table(dbp);
910             if (error)
911             {
912                 sqlite3_close(dbp->db);
913                 errno = error;
914                 return NULL;
915             }
916             /* 2. The cache table */
917             error = db_create_cache_table(dbp);
918             if (error)
919             {
920                 sqlite3_close(dbp->db);
921                 errno = error;
922                 return NULL;
923             }
924         }
925     }
926     _db_cache_space_used_unlocked(dbp, &junk);
927     return dbp;
928 }
929
930 int db_close(FMDB *dbp)
931 {
932     db_state_t *state = dbp;
933     /* When we're done with the database, close it. */
934     if (state->db)
935         sqlite3_close(state->db);
936     free(state);
937     return 0;
938 }
939
940 int main(int argc, char **argv)
941 {
942     int error = 0;
943     char *dbname = argv[1];
944     FMDB *p = db_open(dbname);
945     char *open_error;
946     int result_type;
947     char sql_command[2 * PATH_MAX];
948     char tmp_path[PATH_MAX];
949     int i = 0;
950     db_state_t *state = p;
951
952     logfile = stdout;
953
954     if (!p)
955     {
956         fprintf(stderr, "Failed to open db, errno %d\n", errno);
957         return errno;
958     }
959
960     free_results();
961     result_type = RES_CACHE;
962     sprintf(sql_command, "SELECT * FROM cache");
963     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
964     if (error)
965     {
966         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
967                 __func__, error, open_error));
968         if (open_error)
969             sqlite3_free(open_error);
970         return error;
971     }
972
973     res_current = res_head;
974     while (res_current)
975     {
976         db_cache_entry_t *entry = &res_current->data.cache;
977         DBLOG0((logfile, "entry %d:\n", i++));
978         sprintf(tmp_path, "%c/%s", toupper(entry->flac_path[0]), entry->flac_path);
979         printf("  old flac_path: %s\n", entry->flac_path);
980         printf("  new flac_path: %s\n", tmp_path);
981         printf("  delete old:\n");
982         /* Delete existing entry */
983         sprintf(sql_command,
984                 "DELETE FROM cache WHERE flac_path == '%s'",
985                 entry->flac_path);
986         error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
987         if (error)
988         {
989             printf("    error!\n");
990             return 1;
991         }
992         else
993             printf("    done\n");
994
995         memcpy(entry->flac_path, tmp_path, PATH_MAX);
996         printf("  insert new:\n");
997
998         DBLOG2((logfile, "%s: storing %s:%s:%s:%lld:%ld\n",
999                 __func__, entry->flac_path, entry->cache_path, str_state(entry->state),
1000                 entry->size, entry->mtime));
1001         sprintf(sql_command, "INSERT INTO cache VALUES('%s','%s',%d,%lld,%ld);",
1002                 entry->flac_path, entry->cache_path, entry->state,
1003                 entry->size, entry->mtime);
1004         error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
1005         if (error)
1006         {
1007             printf("    error!\n");
1008             return 1;
1009         }
1010         else
1011             printf("    done\n");
1012         
1013         res_current = res_current->next;
1014     }
1015
1016     free_results();
1017     result_type = RES_SIZE;
1018     sprintf(sql_command, "SELECT * FROM sizes");
1019     error = sqlite3_exec(state->db, sql_command, results_callback, &result_type, &open_error);
1020     if (error)
1021     {
1022         DBLOG0((logfile, "%s: Failed to lookup, error %d (%s)\n",
1023                 __func__, error, open_error));
1024         if (open_error)
1025             sqlite3_free(open_error);
1026         return error;
1027     }
1028
1029     res_current = res_head;
1030     while (res_current)
1031     {
1032         db_size_entry_t *entry = &res_current->data.size;
1033         DBLOG0((logfile, "entry %d:\n", i++));
1034         sprintf(tmp_path, "%c/%s", toupper(entry->flac_path[0]), entry->flac_path);
1035         printf("  old flac_path: %s\n", entry->flac_path);
1036         printf("  new flac_path: %s\n", tmp_path);
1037         printf("  delete old:\n");
1038         /* Delete existing entry */
1039         sprintf(sql_command,
1040                 "DELETE FROM sizes WHERE flac_path == '%s'",
1041                 entry->flac_path);
1042         error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
1043         if (error)
1044         {
1045             printf("    error!\n");
1046             return 1;
1047         }
1048         else
1049             printf("    done\n");
1050
1051         memcpy(entry->flac_path, tmp_path, PATH_MAX);
1052         printf("  insert new:\n");
1053
1054         DBLOG2((logfile, "%s: storing %s:%s:%s:%lld:%ld\n",
1055                 __func__, entry->flac_path, entry->format_choice, entry->format_quality,
1056                 entry->size, entry->mtime));
1057         sprintf(sql_command, "INSERT INTO sizes VALUES('%s','%s','%s',%lld,%ld);",
1058                 entry->flac_path, entry->format_choice, entry->format_quality,
1059                 entry->size, entry->mtime);
1060         error = sqlite3_exec(state->db, sql_command, NULL, NULL, &open_error);
1061         if (error)
1062         {
1063             printf("    error!\n");
1064             return 1;
1065         }
1066         else
1067             printf("    done\n");
1068         
1069         res_current = res_current->next;
1070     }
1071
1072     return 0;
1073 }
1074