Changes to add duration into history database.

chrome/browser/history/visit_database.cc

 // Copyright (c) 2012 The Chromium Authors. All rights reserved.
 // Use of this source code is governed by a BSD-style license that can be
 // found in the LICENSE file.
 
 #include "chrome/browser/history/visit_database.h"
 
 #include <algorithm>
 #include <limits>
 #include <map>
 #include <set>
@@ skipping 350 matching lines @@
   // save space, we do not record those user browsed visits which would be the
   // majority in this table. Only other sources are recorded.
   // Due to the tight relationship between visit_source and visits table, they
   // should be created and dropped at the same time.
   if (!GetDB().DoesTableExist("visit_source")) {
     if (!GetDB().Execute("CREATE TABLE visit_source("
                          "id INTEGER PRIMARY KEY,source INTEGER NOT NULL)"))
         return false;
   }
 
+  // Visit details table contains some extra details about a visit such as
+  // the visit duration. In the future, more information such as active
+  // duration or language can be added too.
+  if (!GetDB().DoesTableExist("visit_details")) {
+    if (!GetDB().Execute("CREATE TABLE visit_details("
+                         "id INTEGER PRIMARY KEY,"
+                         "duration INTEGER DEFAULT 0 NOT NULL)"))
+        return false;
+  }
+
   // Index over url so we can quickly find visits for a page.
   if (!GetDB().Execute(
           "CREATE INDEX IF NOT EXISTS visits_url_index ON visits (url)"))
     return false;
 
   // Create an index over from visits so that we can efficiently find
   // referrers and redirects.
   if (!GetDB().Execute(
           "CREATE INDEX IF NOT EXISTS visits_from_index ON "
           "visits (from_visit)"))
     return false;
 
   // Create an index over time so that we can efficiently find the visits in a
   // given time range (most history views are time-based).
   if (!GetDB().Execute(
           "CREATE INDEX IF NOT EXISTS visits_time_index ON "
           "visits (visit_time)"))
     return false;
 
   return true;
 }
 
 bool VisitDatabase::DropVisitTable() {
   // This will also drop the indices over the table.
   return
+      GetDB().Execute("DROP TABLE IF EXISTS visit_details") &&
       GetDB().Execute("DROP TABLE IF EXISTS visit_source") &&
       GetDB().Execute("DROP TABLE visits");
 }
 
 // Must be in sync with HISTORY_VISIT_ROW_FIELDS.
 // static
 void VisitDatabase::FillVisitRow(sql::Statement& statement, VisitRow* visit) {
   visit->visit_id = statement.ColumnInt64(0);
   visit->url_id = statement.ColumnInt64(1);
   visit->visit_time = base::Time::FromInternalValue(statement.ColumnInt64(2));
@@ skipping 41 matching lines @@
 
   if (source != SOURCE_BROWSED) {
     // Record the source of this visit when it is not browsed.
     sql::Statement statement1(GetDB().GetCachedStatement(SQL_FROM_HERE,
         "INSERT INTO visit_source (id, source) VALUES (?,?)"));
     statement1.BindInt64(0, visit->visit_id);
     statement1.BindInt64(1, source);
 
     if (!statement1.Run()) {
       VLOG(0) << "Failed to execute visit_source insert statement:  "
-              << "url_id = " << visit->visit_id;
+              << "id = " << visit->visit_id;
       return 0;
     }
   }
 
+  // Add the visit into visit_details table as well.
+  sql::Statement statement1(GetDB().GetCachedStatement(SQL_FROM_HERE,
+      "INSERT INTO visit_details (id, duration) VALUES (?,?)"));
+  statement1.BindInt64(0, visit->visit_id);
+  statement1.BindInt64(1, 0);
+
+  if (!statement1.Run()) {
+    VLOG(0) << "Failed to execute visit_details insert statement:  "
+            << "id = " << visit->visit_id;
+    return 0;
+  }
+
   return visit->visit_id;
 }
 
 void VisitDatabase::DeleteVisit(const VisitRow& visit) {
   // Patch around this visit. Any visits that this went to will now have their
   // "source" be the deleted visit's source.
   sql::Statement update_chain(GetDB().GetCachedStatement(SQL_FROM_HERE,
       "UPDATE visits SET from_visit=? WHERE from_visit=?"));
   update_chain.BindInt64(0, visit.referring_visit);
   update_chain.BindInt64(1, visit.visit_id);
   if (!update_chain.Run())
     return;
 
   // Now delete the actual visit.
   sql::Statement del(GetDB().GetCachedStatement(SQL_FROM_HERE,
       "DELETE FROM visits WHERE id=?"));
   del.BindInt64(0, visit.visit_id);
   if (!del.Run())
     return;
 
   // Try to delete the entry in visit_source table as well.
   // If the visit was browsed, there is no corresponding entry in visit_source
   // table, and nothing will be deleted.
   del.Assign(GetDB().GetCachedStatement(SQL_FROM_HERE,
              "DELETE FROM visit_source WHERE id=?"));
   del.BindInt64(0, visit.visit_id);
   del.Run();
+
+  // Delete the entry in visit_details table as well.
+  // We don't care about archiving details information about visits. We will
+  // simply delete all the information for either archived or deleted visits
+  // when they are removed from visits database.
+  del.Assign(GetDB().GetCachedStatement(SQL_FROM_HERE,
+             "DELETE FROM visit_details WHERE id=?"));
+  del.BindInt64(0, visit.visit_id);
+  del.Run();
 }
 
 bool VisitDatabase::GetRowForVisit(VisitID visit_id, VisitRow* out_visit) {
   sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
       "SELECT" HISTORY_VISIT_ROW_FIELDS "FROM visits WHERE id=?"));
   statement.BindInt64(0, visit_id);
 
   if (!statement.Step())
     return false;
 
@@ skipping 336 matching lines @@
 
     // Get the source entries out of the query result.
     while (statement.Step()) {
       std::pair<VisitID, VisitSource> source_entry(statement.ColumnInt64(0),
           static_cast<VisitSource>(statement.ColumnInt(1)));
       sources->insert(source_entry);
     }
   }
 }
 
+bool VisitDatabase::UpdateVisitDetails(VisitID visit_id,
+                                       const base::TimeDelta duration) {
+  sql::Statement statement(GetDB().GetCachedStatement(SQL_FROM_HERE,
+      "UPDATE visit_details SET id=?,duration=? WHERE id=?"));

[GeorgeY, 2012/03/22 00:36:49]

Why do you need to re-set |id|? - you should not do that.

[Wei Li, 2012/03/22 20:05:38]

Done.

+  statement.BindInt64(0, visit_id);
+  statement.BindInt64(1, duration.ToInternalValue());
+  statement.BindInt64(2, visit_id);
+
+  return statement.Run();
+}
+
+void VisitDatabase::GetVisitDetails(const VisitVector& visits,
+                                    VisitDetailMap* details) {
+  DCHECK(details);
+  details->clear();
+
+  // We query the details in batch. Here defines the batch size.
+  const size_t batch_size = 500;
+  size_t visits_size = visits.size();
+
+  size_t start_index = 0, end_index = 0;
+  while (end_index < visits_size) {
+    start_index = end_index;
+    end_index = end_index + batch_size < visits_size ? end_index + batch_size
+                                                     : visits_size;
+
+    // Compose the sql statement with a list of ids.
+    std::string sql = "SELECT id,duration FROM visit_details ";
+    sql.append("WHERE id IN (");

[GeorgeY, 2012/03/22 00:36:49]

Have you considered how long it would take to parse such SQLite statement? It
*probably* will work (the limits for SQLite  are huge), but it would work
slowly.
Another point for *not* doing the separate table

+    // Append all the ids in the statement.
+    for (size_t j = start_index; j < end_index; j++) {
+      if (j != start_index)
+        sql.push_back(',');
+      sql.append(base::Int64ToString(visits[j].visit_id));
+    }
+    sql.append(") ORDER BY id");
+    sql::Statement statement(GetDB().GetUniqueStatement(sql.c_str()));
+
+    // Get the source entries out of the query result.
+    while (statement.Step()) {
+      VisitDetailRow row =
+          {base::TimeDelta::FromInternalValue(statement.ColumnInt64(1))};
+      std::pair<VisitID, VisitDetailRow> entry(statement.ColumnInt64(0), row);
+      details->insert(entry);
+    }
+  }
+}
+
 }  // namespace history