1 /* -*- Mode: C; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2; -*- */
2 /*
3  *  Copyright © 2011 Igalia S.L.
4  *
5  *  This file is part of Epiphany.
6  *
7  *  Epiphany is free software: you can redistribute it and/or modify
8  *  it under the terms of the GNU General Public License as published by
9  *  the Free Software Foundation, either version 3 of the License, or
10  *  (at your option) any later version.
11  *
12  *  Epiphany is distributed in the hope that it will be useful,
13  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
14  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15  *  GNU General Public License for more details.
16  *
17  *  You should have received a copy of the GNU General Public License
18  *  along with Epiphany.  If not, see <http://www.gnu.org/licenses/>.
19  */
20 
21 #include "config.h"
22 
23 #include "ephy-history-service.h"
24 #include "ephy-history-service-private.h"
25 #include "ephy-string.h"
26 #include <glib/gi18n.h>
27 
28 gboolean
ephy_history_service_initialize_hosts_table(EphyHistoryService * self)29 ephy_history_service_initialize_hosts_table (EphyHistoryService *self)
30 {
31   GError *error = NULL;
32 
33   if (ephy_sqlite_connection_table_exists (self->history_database, "hosts")) {
34     return TRUE;
35   }
36   ephy_sqlite_connection_execute (self->history_database,
37                                   "CREATE TABLE hosts ("
38                                   "id INTEGER PRIMARY KEY,"
39                                   "url LONGVARCAR,"
40                                   "title LONGVARCAR,"
41                                   "visit_count INTEGER DEFAULT 0 NOT NULL,"
42                                   "zoom_level REAL DEFAULT 0.0)", &error);
43 
44   if (error) {
45     g_warning ("Could not create hosts table: %s", error->message);
46     g_error_free (error);
47     return FALSE;
48   }
49   return TRUE;
50 }
51 
52 void
ephy_history_service_add_host_row(EphyHistoryService * self,EphyHistoryHost * host)53 ephy_history_service_add_host_row (EphyHistoryService *self,
54                                    EphyHistoryHost    *host)
55 {
56   EphySQLiteStatement *statement = NULL;
57   GError *error = NULL;
58 
59   g_assert (self->history_thread == g_thread_self ());
60   g_assert (self->history_database != NULL);
61 
62   statement = ephy_sqlite_connection_create_statement (self->history_database,
63                                                        "INSERT INTO hosts (url, title, visit_count, zoom_level) "
64                                                        "VALUES (?, ?, ?, ?)", &error);
65 
66   if (error) {
67     g_warning ("Could not build hosts table addition statement: %s", error->message);
68     g_error_free (error);
69     return;
70   }
71 
72   if (ephy_sqlite_statement_bind_string (statement, 0, host->url, &error) == FALSE ||
73       ephy_sqlite_statement_bind_string (statement, 1, host->title, &error) == FALSE ||
74       ephy_sqlite_statement_bind_int (statement, 2, host->visit_count, &error) == FALSE ||
75       ephy_sqlite_statement_bind_double (statement, 3, host->zoom_level, &error) == FALSE) {
76     g_warning ("Could not insert host into hosts table: %s", error->message);
77     g_error_free (error);
78     g_object_unref (statement);
79     return;
80   }
81 
82   ephy_sqlite_statement_step (statement, &error);
83   if (error) {
84     g_warning ("Could not insert host into hosts table: %s", error->message);
85     g_error_free (error);
86   } else {
87     host->id = ephy_sqlite_connection_get_last_insert_id (self->history_database);
88   }
89 
90   g_object_unref (statement);
91 }
92 
93 void
ephy_history_service_update_host_row(EphyHistoryService * self,EphyHistoryHost * host)94 ephy_history_service_update_host_row (EphyHistoryService *self,
95                                       EphyHistoryHost    *host)
96 {
97   EphySQLiteStatement *statement;
98   GError *error = NULL;
99   gdouble zoom_level;
100 
101   g_assert (self->history_thread == g_thread_self ());
102   g_assert (self->history_database != NULL);
103 
104   statement = ephy_sqlite_connection_create_statement (self->history_database,
105                                                        "UPDATE hosts SET url=?, title=?, visit_count=?, zoom_level=?"
106                                                        "WHERE id=?", &error);
107   if (error) {
108     g_warning ("Could not build hosts table modification statement: %s", error->message);
109     g_error_free (error);
110     return;
111   }
112 
113   zoom_level = host->zoom_level;
114 
115   if (ephy_sqlite_statement_bind_string (statement, 0, host->url, &error) == FALSE ||
116       ephy_sqlite_statement_bind_string (statement, 1, host->title, &error) == FALSE ||
117       ephy_sqlite_statement_bind_int (statement, 2, host->visit_count, &error) == FALSE ||
118       ephy_sqlite_statement_bind_double (statement, 3, zoom_level, &error) == FALSE ||
119       ephy_sqlite_statement_bind_int (statement, 4, host->id, &error) == FALSE) {
120     g_warning ("Could not modify host in hosts table: %s", error->message);
121     g_error_free (error);
122     g_object_unref (statement);
123     return;
124   }
125 
126   ephy_sqlite_statement_step (statement, &error);
127   if (error) {
128     g_warning ("Could not modify URL in urls table: %s", error->message);
129     g_error_free (error);
130   }
131   g_object_unref (statement);
132 }
133 
134 EphyHistoryHost *
ephy_history_service_get_host_row(EphyHistoryService * self,const gchar * host_string,EphyHistoryHost * host)135 ephy_history_service_get_host_row (EphyHistoryService *self,
136                                    const gchar        *host_string,
137                                    EphyHistoryHost    *host)
138 {
139   EphySQLiteStatement *statement = NULL;
140   GError *error = NULL;
141 
142   g_assert (self->history_thread == g_thread_self ());
143   g_assert (self->history_database != NULL);
144 
145   if (host_string == NULL && host != NULL)
146     host_string = host->url;
147 
148   g_assert (host_string || (host != NULL && host->id != -1));
149 
150   if (host != NULL && host->id != -1) {
151     statement = ephy_sqlite_connection_create_statement (self->history_database,
152                                                          "SELECT id, url, title, visit_count, zoom_level FROM hosts "
153                                                          "WHERE id=?", &error);
154   } else {
155     statement = ephy_sqlite_connection_create_statement (self->history_database,
156                                                          "SELECT id, url, title, visit_count, zoom_level FROM hosts "
157                                                          "WHERE url=?", &error);
158   }
159 
160   if (error) {
161     g_warning ("Could not build hosts query statement: %s", error->message);
162     g_error_free (error);
163     return NULL;
164   }
165 
166   if (host != NULL && host->id != -1)
167     ephy_sqlite_statement_bind_int (statement, 0, host->id, &error);
168   else
169     ephy_sqlite_statement_bind_string (statement, 0, host_string, &error);
170 
171   if (error) {
172     g_warning ("Could not build hosts table query statement: %s", error->message);
173     g_error_free (error);
174     g_object_unref (statement);
175     return NULL;
176   }
177 
178   if (ephy_sqlite_statement_step (statement, &error) == FALSE) {
179     if (error)
180       g_error_free (error);
181     g_object_unref (statement);
182     return NULL;
183   }
184 
185   if (host == NULL) {
186     host = ephy_history_host_new (NULL, NULL, 0, 0.0);
187   } else {
188     if (host->url)
189       g_free (host->url);
190     if (host->title)
191       g_free (host->title);
192   }
193 
194   host->id = ephy_sqlite_statement_get_column_as_int (statement, 0);
195   host->url = g_strdup (ephy_sqlite_statement_get_column_as_string (statement, 1));
196   host->title = g_strdup (ephy_sqlite_statement_get_column_as_string (statement, 2));
197   host->visit_count = ephy_sqlite_statement_get_column_as_int (statement, 3);
198   host->zoom_level = ephy_sqlite_statement_get_column_as_double (statement, 4);
199 
200   g_object_unref (statement);
201   return host;
202 }
203 
204 static EphyHistoryHost *
create_host_from_statement(EphySQLiteStatement * statement)205 create_host_from_statement (EphySQLiteStatement *statement)
206 {
207   EphyHistoryHost *host =
208     ephy_history_host_new (ephy_sqlite_statement_get_column_as_string (statement, 1),
209                            ephy_sqlite_statement_get_column_as_string (statement, 2),
210                            ephy_sqlite_statement_get_column_as_int (statement, 3),
211                            ephy_sqlite_statement_get_column_as_double (statement, 4));
212   host->id = ephy_sqlite_statement_get_column_as_int (statement, 0);
213 
214   return host;
215 }
216 
217 GList *
ephy_history_service_get_all_hosts(EphyHistoryService * self)218 ephy_history_service_get_all_hosts (EphyHistoryService *self)
219 {
220   EphySQLiteStatement *statement = NULL;
221   GList *hosts = NULL;
222   GError *error = NULL;
223 
224   g_assert (self->history_thread == g_thread_self ());
225   g_assert (self->history_database != NULL);
226 
227   statement = ephy_sqlite_connection_create_statement (self->history_database,
228                                                        "SELECT id, url, title, visit_count, zoom_level FROM hosts", &error);
229 
230   if (error) {
231     g_warning ("Could not build hosts query statement: %s", error->message);
232     g_error_free (error);
233     return NULL;
234   }
235 
236   while (ephy_sqlite_statement_step (statement, &error))
237     hosts = g_list_prepend (hosts, create_host_from_statement (statement));
238 
239   hosts = g_list_reverse (hosts);
240 
241   if (error) {
242     g_warning ("Could not execute hosts table query statement: %s", error->message);
243     g_error_free (error);
244   }
245   g_object_unref (statement);
246 
247   return hosts;
248 }
249 
250 GList *
ephy_history_service_find_host_rows(EphyHistoryService * self,EphyHistoryQuery * query)251 ephy_history_service_find_host_rows (EphyHistoryService *self,
252                                      EphyHistoryQuery   *query)
253 {
254   EphySQLiteStatement *statement = NULL;
255   GList *substring;
256   GString *statement_str;
257   GList *hosts = NULL;
258   GError *error = NULL;
259   const char *base_statement = ""
260                                "SELECT "
261                                "DISTINCT hosts.id, "
262                                "hosts.url, "
263                                "hosts.title, "
264                                "hosts.visit_count, "
265                                "hosts.zoom_level "
266                                "FROM "
267                                "hosts ";
268 
269   int i = 0;
270 
271   g_assert (self->history_thread == g_thread_self ());
272   g_assert (self->history_database != NULL);
273 
274   statement_str = g_string_new (base_statement);
275 
276   /* In either of these cases we need to at least join with the urls table. */
277   if (query->substring_list || query->from > 0 || query->to > 0)
278     statement_str = g_string_append (statement_str, "JOIN urls on hosts.id = urls.host ");
279 
280   /* In these cases, we additionally need to join with the visits table. */
281   if (query->from > 0 || query->to > 0) {
282     statement_str = g_string_append (statement_str, "JOIN visits on urls.id = visits.url WHERE ");
283     if (query->from > 0)
284       statement_str = g_string_append (statement_str, "visits.visit_time >= ? AND ");
285     if (query->to > 0)
286       statement_str = g_string_append (statement_str, "visits.visit_time <= ? AND ");
287   } else {
288     statement_str = g_string_append (statement_str, "WHERE ");
289   }
290 
291   for (substring = query->substring_list; substring != NULL; substring = substring->next)
292     statement_str = g_string_append (statement_str, "(hosts.url LIKE ? OR hosts.title LIKE ? OR "
293                                      "urls.url LIKE ? OR urls.title LIKE ?) AND ");
294 
295   statement_str = g_string_append (statement_str, "1 ");
296 
297   statement = ephy_sqlite_connection_create_statement (self->history_database,
298                                                        statement_str->str, &error);
299   g_string_free (statement_str, TRUE);
300 
301   if (error) {
302     g_warning ("Could not build hosts table query statement: %s", error->message);
303     g_error_free (error);
304     return NULL;
305   }
306   if (query->from > 0) {
307     if (ephy_sqlite_statement_bind_int64 (statement, i++, query->from, &error) == FALSE) {
308       g_warning ("Could not build hosts table query statement: %s", error->message);
309       g_error_free (error);
310       g_object_unref (statement);
311       return NULL;
312     }
313   }
314   if (query->to > 0) {
315     if (ephy_sqlite_statement_bind_int64 (statement, i++, query->to, &error) == FALSE) {
316       g_warning ("Could not build hosts table query statement: %s", error->message);
317       g_error_free (error);
318       g_object_unref (statement);
319       return NULL;
320     }
321   }
322   for (substring = query->substring_list; substring != NULL; substring = substring->next) {
323     int j = 4;
324     char *string = ephy_sqlite_create_match_pattern (substring->data);
325     while (j--)
326       /* The bitwise operation ensures we only skip two characters for titles. */
327       if (ephy_sqlite_statement_bind_string (statement, i++, string + 2 * ((j + 1) & 1), &error) == FALSE) {
328         g_warning ("Could not build hosts table query statement: %s", error->message);
329         g_error_free (error);
330         g_object_unref (statement);
331         g_free (string);
332         return NULL;
333       }
334     g_free (string);
335   }
336 
337   while (ephy_sqlite_statement_step (statement, &error))
338     hosts = g_list_prepend (hosts, create_host_from_statement (statement));
339 
340   hosts = g_list_reverse (hosts);
341 
342   if (error) {
343     g_warning ("Could not execute hosts table query statement: %s", error->message);
344     g_error_free (error);
345   }
346   g_object_unref (statement);
347 
348   return hosts;
349 }
350 
351 /* Inspired from ephy-history.c */
352 static GList *
get_hostname_and_locations(const gchar * url,gchar ** hostname)353 get_hostname_and_locations (const gchar  *url,
354                             gchar       **hostname)
355 {
356   GList *host_locations = NULL;
357   char *scheme = NULL;
358 
359   if (url) {
360     scheme = g_uri_parse_scheme (url);
361     *hostname = ephy_string_get_host_name (url);
362   }
363   /* Build an host name */
364   if (scheme != NULL && strcmp (scheme, "file") == 0) {
365     *hostname = g_strdup ("Local files");
366     host_locations = g_list_append (host_locations,
367                                     g_strdup (url));
368   } else if (scheme == NULL || *hostname == NULL) {
369     *hostname = g_strdup ("Others");
370     host_locations = g_list_append (host_locations,
371                                     g_strdup ("about:blank"));
372   } else {
373     char *location;
374     char *tmp;
375 
376     if (!strcmp (scheme, "https")) {
377       /* If scheme is https, we still fake http. */
378       location = g_strconcat ("http://", *hostname, "/", NULL);
379       host_locations = g_list_append (host_locations, location);
380     }
381 
382     /* We append the real address */
383     location = g_strconcat (scheme,
384                             "://", *hostname, "/", NULL);
385     host_locations = g_list_append (host_locations, location);
386 
387     /* and also a fake www-modified address if it's http or https. */
388     if (g_str_has_prefix (scheme, "http")) {
389       if (g_str_has_prefix (*hostname, "www."))
390         tmp = g_strdup (*hostname + 4);
391       else
392         tmp = g_strconcat ("www.", *hostname, NULL);
393       location = g_strconcat ("http://", tmp, "/", NULL);
394       g_free (tmp);
395       host_locations = g_list_append (host_locations, location);
396     }
397   }
398   g_free (scheme);
399 
400   return host_locations;
401 }
402 
403 EphyHistoryHost *
ephy_history_service_get_host_row_from_url(EphyHistoryService * self,const gchar * url)404 ephy_history_service_get_host_row_from_url (EphyHistoryService *self,
405                                             const gchar        *url)
406 {
407   GList *host_locations, *l;
408   char *hostname;
409   EphyHistoryHost *host = NULL;
410 
411   host_locations = get_hostname_and_locations (url, &hostname);
412   g_assert (host_locations != NULL && hostname != NULL);
413 
414   for (l = host_locations; l != NULL; l = l->next) {
415     host = ephy_history_service_get_host_row (self, l->data, NULL);
416     if (host != NULL)
417       break;
418   }
419 
420   if (host == NULL) {
421     host = ephy_history_host_new (host_locations->data, hostname, 0, 0.0);
422     ephy_history_service_add_host_row (self, host);
423   }
424 
425   g_free (hostname);
426   g_list_free_full (host_locations, (GDestroyNotify)g_free);
427 
428   return host;
429 }
430 
431 void
ephy_history_service_delete_host_row(EphyHistoryService * self,EphyHistoryHost * host)432 ephy_history_service_delete_host_row (EphyHistoryService *self,
433                                       EphyHistoryHost    *host)
434 {
435   EphySQLiteStatement *statement = NULL;
436   const char *sql_statement;
437   GError *error = NULL;
438 
439   g_assert (self->history_thread == g_thread_self ());
440   g_assert (self->history_database != NULL);
441 
442   g_assert (host->id != -1 || host->url);
443 
444   if (host->id != -1)
445     sql_statement = "DELETE FROM hosts WHERE id=?";
446   else
447     sql_statement = "DELETE FROM hosts WHERE url=?";
448 
449   statement = ephy_sqlite_connection_create_statement (self->history_database,
450                                                        sql_statement, &error);
451 
452   if (error) {
453     g_warning ("Could not build urls table query statement: %s", error->message);
454     g_error_free (error);
455     return;
456   }
457 
458   if (host->id != -1)
459     ephy_sqlite_statement_bind_int (statement, 0, host->id, &error);
460   else
461     ephy_sqlite_statement_bind_string (statement, 0, host->url, &error);
462 
463   if (error) {
464     g_warning ("Could not build hosts table query statement: %s", error->message);
465     g_error_free (error);
466     g_object_unref (statement);
467     return;
468   }
469 
470   ephy_sqlite_statement_step (statement, &error);
471   if (error) {
472     g_warning ("Could not modify host in hosts table: %s", error->message);
473     g_error_free (error);
474   }
475   g_object_unref (statement);
476 }
477 
478 void
ephy_history_service_delete_orphan_hosts(EphyHistoryService * self)479 ephy_history_service_delete_orphan_hosts (EphyHistoryService *self)
480 {
481   GError *error = NULL;
482 
483   g_assert (self->history_thread == g_thread_self ());
484   g_assert (self->history_database != NULL);
485 
486   /* Where a JOIN would give us all hosts with urls associated, a LEFT
487    *  JOIN also gives us those hosts for which there are no urls.  By
488    *  means of urls.host == NULL we filter out anything else and
489    *  retrieve only the ids of the hosts without associated urls. Then,
490    *  we delete all these rows from the hosts table. */
491   ephy_sqlite_connection_execute (self->history_database,
492                                   "DELETE FROM hosts WHERE hosts.id IN "
493                                   "  (SELECT hosts.id FROM hosts LEFT JOIN urls "
494                                   "    ON hosts.id = urls.host WHERE urls.host is NULL);",
495                                   &error);
496   if (error) {
497     g_warning ("Couldn't remove orphan hosts from database: %s", error->message);
498     g_error_free (error);
499   }
500 }
501