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