[epiphany] ephy-history-service: remove orphan hosts from database

[Claudio Saavedra <csaavedra src gnome org>]

commit 5d2779a4aa7e8fd9736a9d828bdabe9554b9e008
Author: Claudio Saavedra <csaavedra igalia com>
Date:   Wed Mar 7 16:40:21 2012 +0200

    ephy-history-service: remove orphan hosts from database
    
    After removing all the urls in a host, we don't need that host
    anymore.
    
    https://bugzilla.gnome.org/show_bug.cgi?id=671559

 lib/history/ephy-history-service-hosts-table.c |   25 ++++++++++++++++++++++++
 lib/history/ephy-history-service-private.h     |    1 +
 lib/history/ephy-history-service.c             |    1 +
 3 files changed, 27 insertions(+), 0 deletions(-)
---
diff --git a/lib/history/ephy-history-service-hosts-table.c b/lib/history/ephy-history-service-hosts-table.c
index 9691545..880a639 100644
--- a/lib/history/ephy-history-service-hosts-table.c
+++ b/lib/history/ephy-history-service-hosts-table.c
@@ -368,3 +368,28 @@ ephy_history_service_delete_host_row (EphyHistoryService *self,
   }
   g_object_unref (statement);
 }
+
+void
+ephy_history_service_delete_orphan_hosts (EphyHistoryService *self)
+{
+  EphyHistoryServicePrivate *priv = EPHY_HISTORY_SERVICE (self)->priv;
+  GError *error = NULL;
+
+  g_assert (priv->history_thread == g_thread_self ());
+  g_assert (priv->history_database != NULL);
+
+  /* Where a JOIN would give us all hosts with urls associated, a LEFT
+     JOIN also gives us those hosts for which there are no urls.  By
+     means of urls.host == NULL we filter out anything else and
+     retrieve only the ids of the hosts without associated urls. Then,
+     we delete all these rows from the hosts table. */
+  ephy_sqlite_connection_execute (priv->history_database,
+                                  "DELETE FROM hosts WHERE hosts.id IN "
+                                  "  (SELECT hosts.id FROM hosts LEFT JOIN urls "
+                                  "    ON hosts.id = urls.host WHERE urls.host is NULL);",
+                                  &error);
+  if (error) {
+    g_error ("Couldn't remove orphan hosts from database: %s", error->message);
+    g_error_free (error);
+  }
+}
diff --git a/lib/history/ephy-history-service-private.h b/lib/history/ephy-history-service-private.h
index b585d40..0894770 100644
--- a/lib/history/ephy-history-service-private.h
+++ b/lib/history/ephy-history-service-private.h
@@ -52,5 +52,6 @@ EphyHistoryHost *        ephy_history_service_get_host_row            (EphyHisto
 GList *                  ephy_history_service_get_all_hosts           (EphyHistoryService *self);
 EphyHistoryHost *        ephy_history_service_get_host_row_from_url   (EphyHistoryService *self, const gchar *url);
 void                     ephy_history_service_delete_host_row         (EphyHistoryService *self, EphyHistoryHost *host);
+void                     ephy_history_service_delete_orphan_hosts     (EphyHistoryService *self);
 
 #endif /* EPHY_HISTORY_SERVICE_PRIVATE_H */
diff --git a/lib/history/ephy-history-service.c b/lib/history/ephy-history-service.c
index b6fc827..54af56a 100644
--- a/lib/history/ephy-history-service.c
+++ b/lib/history/ephy-history-service.c
@@ -788,6 +788,7 @@ ephy_history_service_execute_delete_urls (EphyHistoryService *self,
     ephy_history_service_delete_url (self, url);
   }
 
+  ephy_history_service_delete_orphan_hosts (self);
   ephy_history_service_schedule_commit (self);
 
   return TRUE;