1 // Copyright (c) 2012 The Chromium Authors. All rights reserved.
2 // Use of this source code is governed by a BSD-style license that can be
3 // found in the LICENSE file.
5 #include "chrome/browser/history/visit_database.h"
12 #include "base/logging.h"
13 #include "base/strings/string_number_conversions.h"
14 #include "chrome/browser/history/visit_filter.h"
15 #include "chrome/common/url_constants.h"
16 #include "components/history/core/browser/url_database.h"
17 #include "sql/statement.h"
18 #include "ui/base/page_transition_types.h"
22 VisitDatabase::VisitDatabase() {
25 VisitDatabase::~VisitDatabase() {
28 bool VisitDatabase::InitVisitTable() {
29 if (!GetDB().DoesTableExist("visits")) {
30 if (!GetDB().Execute("CREATE TABLE visits("
31 "id INTEGER PRIMARY KEY,"
32 "url INTEGER NOT NULL," // key of the URL this corresponds to
33 "visit_time INTEGER NOT NULL,"
35 "transition INTEGER DEFAULT 0 NOT NULL,"
37 // Some old DBs may have an "is_indexed" field here, but this is no
38 // longer used and should NOT be read or written from any longer.
39 "visit_duration INTEGER DEFAULT 0 NOT NULL)"))
43 // Visit source table contains the source information for all the visits. To
44 // save space, we do not record those user browsed visits which would be the
45 // majority in this table. Only other sources are recorded.
46 // Due to the tight relationship between visit_source and visits table, they
47 // should be created and dropped at the same time.
48 if (!GetDB().DoesTableExist("visit_source")) {
49 if (!GetDB().Execute("CREATE TABLE visit_source("
50 "id INTEGER PRIMARY KEY,source INTEGER NOT NULL)"))
54 // Index over url so we can quickly find visits for a page.
56 "CREATE INDEX IF NOT EXISTS visits_url_index ON visits (url)"))
59 // Create an index over from visits so that we can efficiently find
60 // referrers and redirects.
62 "CREATE INDEX IF NOT EXISTS visits_from_index ON "
63 "visits (from_visit)"))
66 // Create an index over time so that we can efficiently find the visits in a
67 // given time range (most history views are time-based).
69 "CREATE INDEX IF NOT EXISTS visits_time_index ON "
70 "visits (visit_time)"))
76 bool VisitDatabase::DropVisitTable() {
77 // This will also drop the indices over the table.
79 GetDB().Execute("DROP TABLE IF EXISTS visit_source") &&
80 GetDB().Execute("DROP TABLE visits");
83 // Must be in sync with HISTORY_VISIT_ROW_FIELDS.
85 void VisitDatabase::FillVisitRow(sql::Statement& statement, VisitRow* visit) {
86 visit->visit_id = statement.ColumnInt64(0);
87 visit->url_id = statement.ColumnInt64(1);
88 visit->visit_time = base::Time::FromInternalValue(statement.ColumnInt64(2));
89 visit->referring_visit = statement.ColumnInt64(3);
90 visit->transition = ui::PageTransitionFromInt(statement.ColumnInt(4));
91 visit->segment_id = statement.ColumnInt64(5);
92 visit->visit_duration =
93 base::TimeDelta::FromInternalValue(statement.ColumnInt64(6));
97 bool VisitDatabase::FillVisitVector(sql::Statement& statement,
98 VisitVector* visits) {
99 if (!statement.is_valid())
102 while (statement.Step()) {
103 history::VisitRow visit;
104 FillVisitRow(statement, &visit);
105 visits->push_back(visit);
108 return statement.Succeeded();
112 bool VisitDatabase::FillVisitVectorWithOptions(sql::Statement& statement,
113 const QueryOptions& options,
114 VisitVector* visits) {
115 std::set<URLID> found_urls;
117 // Keeps track of the day that |found_urls| is holding the URLs for, in order
118 // to handle removing per-day duplicates.
119 base::Time found_urls_midnight;
121 while (statement.Step()) {
123 FillVisitRow(statement, &visit);
125 if (options.duplicate_policy != QueryOptions::KEEP_ALL_DUPLICATES) {
126 if (options.duplicate_policy == QueryOptions::REMOVE_DUPLICATES_PER_DAY &&
127 found_urls_midnight != visit.visit_time.LocalMidnight()) {
129 found_urls_midnight = visit.visit_time.LocalMidnight();
131 // Make sure the URL this visit corresponds to is unique.
132 if (found_urls.find(visit.url_id) != found_urls.end())
134 found_urls.insert(visit.url_id);
137 if (static_cast<int>(visits->size()) >= options.EffectiveMaxCount())
139 visits->push_back(visit);
144 VisitID VisitDatabase::AddVisit(VisitRow* visit, VisitSource source) {
145 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
146 "INSERT INTO visits "
147 "(url, visit_time, from_visit, transition, segment_id, "
148 "visit_duration) VALUES (?,?,?,?,?,?)"));
149 statement.BindInt64(0, visit->url_id);
150 statement.BindInt64(1, visit->visit_time.ToInternalValue());
151 statement.BindInt64(2, visit->referring_visit);
152 statement.BindInt64(3, visit->transition);
153 statement.BindInt64(4, visit->segment_id);
154 statement.BindInt64(5, visit->visit_duration.ToInternalValue());
156 if (!statement.Run()) {
157 VLOG(0) << "Failed to execute visit insert statement: "
158 << "url_id = " << visit->url_id;
162 visit->visit_id = GetDB().GetLastInsertRowId();
164 if (source != SOURCE_BROWSED) {
165 // Record the source of this visit when it is not browsed.
166 sql::Statement statement1(GetDB().GetCachedStatement(SQL_FROM_HERE,
167 "INSERT INTO visit_source (id, source) VALUES (?,?)"));
168 statement1.BindInt64(0, visit->visit_id);
169 statement1.BindInt64(1, source);
171 if (!statement1.Run()) {
172 VLOG(0) << "Failed to execute visit_source insert statement: "
173 << "id = " << visit->visit_id;
178 return visit->visit_id;
181 void VisitDatabase::DeleteVisit(const VisitRow& visit) {
182 // Patch around this visit. Any visits that this went to will now have their
183 // "source" be the deleted visit's source.
184 sql::Statement update_chain(GetDB().GetCachedStatement(SQL_FROM_HERE,
185 "UPDATE visits SET from_visit=? WHERE from_visit=?"));
186 update_chain.BindInt64(0, visit.referring_visit);
187 update_chain.BindInt64(1, visit.visit_id);
188 if (!update_chain.Run())
191 // Now delete the actual visit.
192 sql::Statement del(GetDB().GetCachedStatement(SQL_FROM_HERE,
193 "DELETE FROM visits WHERE id=?"));
194 del.BindInt64(0, visit.visit_id);
198 // Try to delete the entry in visit_source table as well.
199 // If the visit was browsed, there is no corresponding entry in visit_source
200 // table, and nothing will be deleted.
201 del.Assign(GetDB().GetCachedStatement(SQL_FROM_HERE,
202 "DELETE FROM visit_source WHERE id=?"));
203 del.BindInt64(0, visit.visit_id);
207 bool VisitDatabase::GetRowForVisit(VisitID visit_id, VisitRow* out_visit) {
208 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
209 "SELECT" HISTORY_VISIT_ROW_FIELDS "FROM visits WHERE id=?"));
210 statement.BindInt64(0, visit_id);
212 if (!statement.Step())
215 FillVisitRow(statement, out_visit);
217 // We got a different visit than we asked for, something is wrong.
218 DCHECK_EQ(visit_id, out_visit->visit_id);
219 if (visit_id != out_visit->visit_id)
225 bool VisitDatabase::UpdateVisitRow(const VisitRow& visit) {
226 // Don't store inconsistent data to the database.
227 DCHECK_NE(visit.visit_id, visit.referring_visit);
228 if (visit.visit_id == visit.referring_visit)
231 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
233 "url=?,visit_time=?,from_visit=?,transition=?,segment_id=?,"
234 "visit_duration=? WHERE id=?"));
235 statement.BindInt64(0, visit.url_id);
236 statement.BindInt64(1, visit.visit_time.ToInternalValue());
237 statement.BindInt64(2, visit.referring_visit);
238 statement.BindInt64(3, visit.transition);
239 statement.BindInt64(4, visit.segment_id);
240 statement.BindInt64(5, visit.visit_duration.ToInternalValue());
241 statement.BindInt64(6, visit.visit_id);
243 return statement.Run();
246 bool VisitDatabase::GetVisitsForURL(URLID url_id, VisitVector* visits) {
249 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
250 "SELECT" HISTORY_VISIT_ROW_FIELDS
253 "ORDER BY visit_time ASC"));
254 statement.BindInt64(0, url_id);
255 return FillVisitVector(statement, visits);
258 bool VisitDatabase::GetVisibleVisitsForURL(URLID url_id,
259 const QueryOptions& options,
260 VisitVector* visits) {
263 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
264 "SELECT" HISTORY_VISIT_ROW_FIELDS
266 "WHERE url=? AND visit_time >= ? AND visit_time < ? "
267 "AND (transition & ?) != 0 " // CHAIN_END
268 "AND (transition & ?) NOT IN (?, ?, ?) " // NO SUBFRAME or
270 "ORDER BY visit_time DESC"));
271 statement.BindInt64(0, url_id);
272 statement.BindInt64(1, options.EffectiveBeginTime());
273 statement.BindInt64(2, options.EffectiveEndTime());
274 statement.BindInt(3, ui::PAGE_TRANSITION_CHAIN_END);
275 statement.BindInt(4, ui::PAGE_TRANSITION_CORE_MASK);
276 statement.BindInt(5, ui::PAGE_TRANSITION_AUTO_SUBFRAME);
277 statement.BindInt(6, ui::PAGE_TRANSITION_MANUAL_SUBFRAME);
278 statement.BindInt(7, ui::PAGE_TRANSITION_KEYWORD_GENERATED);
280 return FillVisitVectorWithOptions(statement, options, visits);
283 bool VisitDatabase::GetVisitsForTimes(const std::vector<base::Time>& times,
284 VisitVector* visits) {
287 for (std::vector<base::Time>::const_iterator it = times.begin();
288 it != times.end(); ++it) {
289 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
290 "SELECT" HISTORY_VISIT_ROW_FIELDS "FROM visits "
291 "WHERE visit_time == ?"));
293 statement.BindInt64(0, it->ToInternalValue());
295 if (!FillVisitVector(statement, visits))
301 bool VisitDatabase::GetAllVisitsInRange(base::Time begin_time,
304 VisitVector* visits) {
307 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
308 "SELECT" HISTORY_VISIT_ROW_FIELDS "FROM visits "
309 "WHERE visit_time >= ? AND visit_time < ?"
310 "ORDER BY visit_time LIMIT ?"));
312 // See GetVisibleVisitsInRange for more info on how these times are bound.
313 int64 end = end_time.ToInternalValue();
314 statement.BindInt64(0, begin_time.ToInternalValue());
315 statement.BindInt64(1, end ? end : std::numeric_limits<int64>::max());
316 statement.BindInt64(2,
317 max_results ? max_results : std::numeric_limits<int64>::max());
319 return FillVisitVector(statement, visits);
322 bool VisitDatabase::GetVisitsInRangeForTransition(
323 base::Time begin_time,
326 ui::PageTransition transition,
327 VisitVector* visits) {
331 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
332 "SELECT" HISTORY_VISIT_ROW_FIELDS "FROM visits "
333 "WHERE visit_time >= ? AND visit_time < ? "
334 "AND (transition & ?) == ?"
335 "ORDER BY visit_time LIMIT ?"));
337 // See GetVisibleVisitsInRange for more info on how these times are bound.
338 int64 end = end_time.ToInternalValue();
339 statement.BindInt64(0, begin_time.ToInternalValue());
340 statement.BindInt64(1, end ? end : std::numeric_limits<int64>::max());
341 statement.BindInt(2, ui::PAGE_TRANSITION_CORE_MASK);
342 statement.BindInt(3, transition);
343 statement.BindInt64(4,
344 max_results ? max_results : std::numeric_limits<int64>::max());
346 return FillVisitVector(statement, visits);
349 bool VisitDatabase::GetVisibleVisitsInRange(const QueryOptions& options,
350 VisitVector* visits) {
352 // The visit_time values can be duplicated in a redirect chain, so we sort
353 // by id too, to ensure a consistent ordering just in case.
354 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
355 "SELECT" HISTORY_VISIT_ROW_FIELDS "FROM visits "
356 "WHERE visit_time >= ? AND visit_time < ? "
357 "AND (transition & ?) != 0 " // CHAIN_END
358 "AND (transition & ?) NOT IN (?, ?, ?) " // NO SUBFRAME or
360 "ORDER BY visit_time DESC, id DESC"));
362 statement.BindInt64(0, options.EffectiveBeginTime());
363 statement.BindInt64(1, options.EffectiveEndTime());
364 statement.BindInt(2, ui::PAGE_TRANSITION_CHAIN_END);
365 statement.BindInt(3, ui::PAGE_TRANSITION_CORE_MASK);
366 statement.BindInt(4, ui::PAGE_TRANSITION_AUTO_SUBFRAME);
367 statement.BindInt(5, ui::PAGE_TRANSITION_MANUAL_SUBFRAME);
368 statement.BindInt(6, ui::PAGE_TRANSITION_KEYWORD_GENERATED);
370 return FillVisitVectorWithOptions(statement, options, visits);
373 void VisitDatabase::GetDirectVisitsDuringTimes(const VisitFilter& time_filter,
375 VisitVector* visits) {
378 visits->reserve(max_results);
379 for (VisitFilter::TimeVector::const_iterator it = time_filter.times().begin();
380 it != time_filter.times().end(); ++it) {
381 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
382 "SELECT" HISTORY_VISIT_ROW_FIELDS "FROM visits "
383 "WHERE visit_time >= ? AND visit_time < ? "
384 "AND (transition & ?) != 0 " // CHAIN_START
385 "AND (transition & ?) IN (?, ?) " // TYPED or AUTO_BOOKMARK only
386 "ORDER BY visit_time DESC, id DESC"));
388 statement.BindInt64(0, it->first.ToInternalValue());
389 statement.BindInt64(1, it->second.ToInternalValue());
390 statement.BindInt(2, ui::PAGE_TRANSITION_CHAIN_START);
391 statement.BindInt(3, ui::PAGE_TRANSITION_CORE_MASK);
392 statement.BindInt(4, ui::PAGE_TRANSITION_TYPED);
393 statement.BindInt(5, ui::PAGE_TRANSITION_AUTO_BOOKMARK);
395 while (statement.Step()) {
397 FillVisitRow(statement, &visit);
398 visits->push_back(visit);
400 if (max_results > 0 && static_cast<int>(visits->size()) >= max_results)
406 VisitID VisitDatabase::GetMostRecentVisitForURL(URLID url_id,
407 VisitRow* visit_row) {
408 // The visit_time values can be duplicated in a redirect chain, so we sort
409 // by id too, to ensure a consistent ordering just in case.
410 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
411 "SELECT" HISTORY_VISIT_ROW_FIELDS "FROM visits "
413 "ORDER BY visit_time DESC, id DESC "
415 statement.BindInt64(0, url_id);
416 if (!statement.Step())
417 return 0; // No visits for this URL.
420 FillVisitRow(statement, visit_row);
421 return visit_row->visit_id;
423 return statement.ColumnInt64(0);
426 bool VisitDatabase::GetMostRecentVisitsForURL(URLID url_id,
428 VisitVector* visits) {
431 // The visit_time values can be duplicated in a redirect chain, so we sort
432 // by id too, to ensure a consistent ordering just in case.
433 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
434 "SELECT" HISTORY_VISIT_ROW_FIELDS
437 "ORDER BY visit_time DESC, id DESC "
439 statement.BindInt64(0, url_id);
440 statement.BindInt(1, max_results);
442 return FillVisitVector(statement, visits);
445 bool VisitDatabase::GetRedirectFromVisit(VisitID from_visit,
448 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
450 "FROM visits v JOIN urls u ON v.url = u.id "
451 "WHERE v.from_visit = ? "
452 "AND (v.transition & ?) != 0")); // IS_REDIRECT_MASK
453 statement.BindInt64(0, from_visit);
454 statement.BindInt(1, ui::PAGE_TRANSITION_IS_REDIRECT_MASK);
456 if (!statement.Step())
457 return false; // No redirect from this visit. (Or SQL error)
459 *to_visit = statement.ColumnInt64(0);
461 *to_url = GURL(statement.ColumnString(1));
465 bool VisitDatabase::GetRedirectToVisit(VisitID to_visit,
469 if (!GetRowForVisit(to_visit, &row))
473 *from_visit = row.referring_visit;
476 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
478 "FROM visits v JOIN urls u ON v.url = u.id "
480 statement.BindInt64(0, row.referring_visit);
482 if (!statement.Step())
485 *from_url = GURL(statement.ColumnString(0));
490 bool VisitDatabase::GetVisibleVisitCountToHost(const GURL& url,
492 base::Time* first_visit) {
493 if (!url.SchemeIs(url::kHttpScheme) &&
494 !url.SchemeIs(url::kHttpsScheme))
497 // We need to search for URLs with a matching host/port. One way to query for
498 // this is to use the LIKE operator, eg 'url LIKE http://google.com/%'. This
499 // is inefficient though in that it doesn't use the index and each entry must
500 // be visited. The same query can be executed by using >= and < operator.
501 // The query becomes:
502 // 'url >= http://google.com/' and url < http://google.com0'.
503 // 0 is used as it is one character greater than '/'.
504 const std::string host_query_min = url.GetOrigin().spec();
505 if (host_query_min.empty())
508 // We also want to restrict ourselves to main frame navigations that are not
509 // in the middle of redirect chains, hence the transition checks.
510 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
511 "SELECT MIN(v.visit_time), COUNT(*) "
512 "FROM visits v INNER JOIN urls u ON v.url = u.id "
513 "WHERE u.url >= ? AND u.url < ? "
514 "AND (transition & ?) != 0 "
515 "AND (transition & ?) NOT IN (?, ?, ?)"));
516 statement.BindString(0, host_query_min);
517 statement.BindString(1,
518 host_query_min.substr(0, host_query_min.size() - 1) + '0');
519 statement.BindInt(2, ui::PAGE_TRANSITION_CHAIN_END);
520 statement.BindInt(3, ui::PAGE_TRANSITION_CORE_MASK);
521 statement.BindInt(4, ui::PAGE_TRANSITION_AUTO_SUBFRAME);
522 statement.BindInt(5, ui::PAGE_TRANSITION_MANUAL_SUBFRAME);
523 statement.BindInt(6, ui::PAGE_TRANSITION_KEYWORD_GENERATED);
525 if (!statement.Step()) {
526 // We've never been to this page before.
531 if (!statement.Succeeded())
534 *first_visit = base::Time::FromInternalValue(statement.ColumnInt64(0));
535 *count = statement.ColumnInt(1);
539 bool VisitDatabase::GetStartDate(base::Time* first_visit) {
540 sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
541 "SELECT MIN(visit_time) FROM visits WHERE visit_time != 0"));
542 if (!statement.Step() || statement.ColumnInt64(0) == 0) {
543 *first_visit = base::Time::Now();
546 *first_visit = base::Time::FromInternalValue(statement.ColumnInt64(0));
550 void VisitDatabase::GetVisitsSource(const VisitVector& visits,
551 VisitSourceMap* sources) {
555 // We query the source in batch. Here defines the batch size.
556 const size_t batch_size = 500;
557 size_t visits_size = visits.size();
559 size_t start_index = 0, end_index = 0;
560 while (end_index < visits_size) {
561 start_index = end_index;
562 end_index = end_index + batch_size < visits_size ? end_index + batch_size
565 // Compose the sql statement with a list of ids.
566 std::string sql = "SELECT id,source FROM visit_source ";
567 sql.append("WHERE id IN (");
568 // Append all the ids in the statement.
569 for (size_t j = start_index; j < end_index; j++) {
570 if (j != start_index)
572 sql.append(base::Int64ToString(visits[j].visit_id));
574 sql.append(") ORDER BY id");
575 sql::Statement statement(GetDB().GetUniqueStatement(sql.c_str()));
577 // Get the source entries out of the query result.
578 while (statement.Step()) {
579 std::pair<VisitID, VisitSource> source_entry(statement.ColumnInt64(0),
580 static_cast<VisitSource>(statement.ColumnInt(1)));
581 sources->insert(source_entry);
586 bool VisitDatabase::MigrateVisitsWithoutDuration() {
587 if (!GetDB().DoesTableExist("visits")) {
588 NOTREACHED() << " Visits table should exist before migration";
592 if (!GetDB().DoesColumnExist("visits", "visit_duration")) {
593 // Old versions don't have the visit_duration column, we modify the table
594 // to add that field.
595 if (!GetDB().Execute("ALTER TABLE visits "
596 "ADD COLUMN visit_duration INTEGER DEFAULT 0 NOT NULL"))
602 void VisitDatabase::GetBriefVisitInfoOfMostRecentVisits(
604 std::vector<BriefVisitInfo>* result_vector) {
605 result_vector->clear();
607 sql::Statement statement(GetDB().GetUniqueStatement(
608 "SELECT url,visit_time,transition FROM visits "
609 "ORDER BY id DESC LIMIT ?"));
611 statement.BindInt64(0, max_visits);
613 if (!statement.is_valid())
616 while (statement.Step()) {
618 info.url_id = statement.ColumnInt64(0);
619 info.time = base::Time::FromInternalValue(statement.ColumnInt64(1));
620 info.transition = ui::PageTransitionFromInt(statement.ColumnInt(2));
621 result_vector->push_back(info);
625 } // namespace history