From 901a1ba6923340f557d52ee9825d854cfc76a120 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Kjetil=20=C3=98rbekk?= <orbekk@pvv.ntnu.no>
Date: Mon, 8 Sep 2008 00:55:00 +0200
Subject: - Finished db_cleanup code.   Removing paths from .config should now
 work nicely :)

---
 src/mfs_cleanup_db.c | 126 +++++++++++++++++++++++++++++++++++++++++++++++----
 1 file changed, 116 insertions(+), 10 deletions(-)

diff --git a/src/mfs_cleanup_db.c b/src/mfs_cleanup_db.c
index a15b127..c4cf258 100644
--- a/src/mfs_cleanup_db.c
+++ b/src/mfs_cleanup_db.c
@@ -20,33 +20,135 @@
  * A copy of the license can typically be found in COPYING
  */
 
+#include <stdlib.h>
+#include <stdio.h>
+#include <string.h>
 #include <sqlite3.h>
 #include <pthread.h>
 
 #include <debug.h>
 #include <musicfs.h>
 
+int
+execute_statement(sqlite3 *handle, const char *query,
+	       const char *fields[])
+{
+	sqlite3_stmt *st;
+	int res;
+	
+	res = sqlite3_prepare_v2(handle, query, -1, &st, NULL);
+	if (res != SQLITE_OK) {
+		DEBUG("Error preparing statement: %s\n",
+		    sqlite3_errmsg(handle));
+		return (-1);
+	}
+	
+	for (int i=0; fields[i] != NULL; i++) {
+		sqlite3_bind_text(st, i+1, fields[i], -1, SQLITE_TRANSIENT);
+	}
+	res = sqlite3_step(st);
+	if (res != SQLITE_DONE) {
+		DEBUG("Error executing query %s\n\t %s\n",
+		    query, sqlite3_errmsg(handle));
+		return (-1);
+	}
+	sqlite3_finalize(st);
+	return (0);
+}
+
+void
+delete_from_path(sqlite3 *handle, const char *path)
+{
+	const char *fields[] = {path, NULL};
+
+	execute_statement(handle,
+	    "DELETE FROM song WHERE filepath LIKE (?||'%')",
+	    fields);
+}
+
+void
+delete_artist(sqlite3 *handle, const char *artist)
+{
+	const char *fields[] = {artist, NULL};
+	execute_statement(handle,
+	    "DELETE FROM artist WHERE name == ?",
+	    fields);
+}
+
+void
+delete_genre(sqlite3 *handle, const char *genre)
+{
+	const char *fields[] = {genre, NULL};
+	execute_statement(handle,
+	    "DELETE FROM genre WHERE name == ?",
+	    fields);
+}
+
 void
-mfs_delete_from_path(sqlite3 *handle, const char *path) {
+cleanup_artists(sqlite3 *handle)
+{
 	sqlite3_stmt *st;
 	int res;
+	const char *artist, *song_title;
+
+	/* This is a slow, but probably faster than doing a lot of
+	   queries */
+	res = sqlite3_prepare_v2(handle, "SELECT a.name, s.title "
+	    "FROM artist AS a LEFT JOIN song AS s ON "
+	    "(a.name==s.artistname) GROUP BY a.name",
+	    -1, &st, NULL);
 	
-	res = sqlite3_prepare_v2(handle, "DELETE FROM song WHERE "
-	    "filepath LIKE (?||'%')", -1, &st, NULL);
 	if (res != SQLITE_OK) {
 		DEBUG("Error preparing statement: %s\n",
 		    sqlite3_errmsg(handle));
 		return;
 	}
+	
+	res = sqlite3_step(st);
+	while (res == SQLITE_ROW) {
+		artist = (const char*)sqlite3_column_text(st, 0);
+		song_title = (const char*)sqlite3_column_text(st, 1);
+		
+		if (song_title == NULL || strcmp(song_title, "") == 0) {
+			/* No song by this artist */
+			delete_artist(handle, artist);
+		}
+		
+		res = sqlite3_step(st);
+	}
+}
 
-	DEBUG("removing songs from '%s'\n", path);
-	sqlite3_bind_text(st, 1, path, -1, SQLITE_TRANSIENT);
+void
+cleanup_genres(sqlite3 *handle)
+{
+	/* pretty much the same as cleanup_artist, only for genres */
+	sqlite3_stmt *st;
+	int res;
+	const char *genre, *song_title;
+
+	res = sqlite3_prepare_v2(handle, "SELECT g.name, s.title "
+	    "FROM genre AS g LEFT JOIN song AS s ON "
+	    "(g.name==s.genrename) GROUP BY g.name",
+	    -1, &st, NULL);
+	
+	if (res != SQLITE_OK) {
+		DEBUG("Error preparing statement: %s\n",
+		    sqlite3_errmsg(handle));
+		return;
+	}
+	
 	res = sqlite3_step(st);
-	if (res != SQLITE_DONE) {
-		DEBUG("Error deleting '%s' from database: %s\n",
-		    path, sqlite3_errmsg(handle));
+	while (res == SQLITE_ROW) {
+		genre = (const char*)sqlite3_column_text(st, 0);
+		song_title = (const char*)sqlite3_column_text(st, 1);
+		
+		if (song_title == NULL || strcmp(song_title, "") == 0) {
+			/* No song with this genre */
+			delete_genre(handle, genre);
+		}
+		
+		res = sqlite3_step(st);
 	}
-	sqlite3_finalize(st);
 }
 
 /*
@@ -79,7 +181,7 @@ mfs_cleanup_db(sqlite3 *handle)
 	
 	while (res == SQLITE_ROW) {
 		path = (const char*)sqlite3_column_text(st, 0);
-		mfs_delete_from_path(handle, path);
+		delete_from_path(handle, path);
 		res = sqlite3_step(st);
 	}
 	sqlite3_finalize(st);
@@ -99,5 +201,9 @@ mfs_cleanup_db(sqlite3 *handle)
 		return;
 	}
 	sqlite3_finalize(st);
+
+	/* These are a bit heavy :-( */
+	cleanup_artists(handle);
+	cleanup_genres(handle);
 }
 
-- 
cgit v1.2.3