components/autofill/core/browser/webdata/autofill_table.cc

   1 // Copyright 2013 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.
   4
   5 #include "components/autofill/core/browser/webdata/autofill_table.h"
   6
   7 #include <algorithm>
   8 #include <cmath>
   9 #include <limits>
  10 #include <map>
  11 #include <set>
  12 #include <string>
  13 #include <vector>
  14
  15 #include "base/command_line.h"
  16 #include "base/guid.h"
  17 #include "base/i18n/case_conversion.h"
  18 #include "base/logging.h"
  19 #include "base/numerics/safe_conversions.h"
  20 #include "base/strings/string_number_conversions.h"
  21 #include "base/strings/utf_string_conversions.h"
  22 #include "base/time/time.h"
  23 #include "components/autofill/core/browser/autofill_country.h"
  24 #include "components/autofill/core/browser/autofill_profile.h"
  25 #include "components/autofill/core/browser/autofill_type.h"
  26 #include "components/autofill/core/browser/credit_card.h"
  27 #include "components/autofill/core/browser/personal_data_manager.h"
  28 #include "components/autofill/core/browser/webdata/autofill_change.h"
  29 #include "components/autofill/core/browser/webdata/autofill_entry.h"
  30 #include "components/autofill/core/common/autofill_switches.h"
  31 #include "components/autofill/core/common/form_field_data.h"
  32 #include "components/os_crypt/os_crypt.h"
  33 #include "components/webdata/common/web_database.h"
  34 #include "sql/statement.h"
  35 #include "sql/transaction.h"
  36 #include "ui/base/l10n/l10n_util.h"
  37 #include "url/gurl.h"
  38
  39 using base::ASCIIToUTF16;
  40 using base::Time;
  41
  42 namespace autofill {
  43 namespace {
  44
  45 // The period after which autocomplete entries should expire in days.
  46 const int64 kExpirationPeriodInDays = 60;
  47
  48 template<typename T>
  49 T* address_of(T& v) {
  50   return &v;
  51 }
  52
  53 // Helper struct for AutofillTable::RemoveFormElementsAddedBetween().
  54 // Contains all the necessary fields to update a row in the 'autofill' table.
  55 struct AutofillUpdate {
  56   base::string16 name;
  57   base::string16 value;
  58   time_t date_created;
  59   time_t date_last_used;
  60   int count;
  61 };
  62
  63 // Rounds a positive floating point number to the nearest integer.
  64 int Round(float f) {
  65   DCHECK_GE(f, 0.f);
  66   return base::checked_cast<int>(std::floor(f + 0.5f));
  67 }
  68
  69 // Returns the |data_model|'s value corresponding to the |type|, trimmed to the
  70 // maximum length that can be stored in a column of the Autofill database.
  71 base::string16 GetInfo(const AutofillDataModel& data_model,
  72                        ServerFieldType type) {
  73   base::string16 data = data_model.GetRawInfo(type);
  74   if (data.size() > AutofillTable::kMaxDataLength)
  75     return data.substr(0, AutofillTable::kMaxDataLength);
  76
  77   return data;
  78 }
  79
  80 void BindAutofillProfileToStatement(const AutofillProfile& profile,
  81                                     const base::Time& modification_date,
  82                                     sql::Statement* s) {
  83   DCHECK(base::IsValidGUID(profile.guid()));
  84   int index = 0;
  85   s->BindString(index++, profile.guid());
  86
  87   s->BindString16(index++, GetInfo(profile, COMPANY_NAME));
  88   s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_STREET_ADDRESS));
  89   s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_DEPENDENT_LOCALITY));
  90   s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_CITY));
  91   s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_STATE));
  92   s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_ZIP));
  93   s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_SORTING_CODE));
  94   s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_COUNTRY));
  95   s->BindInt64(index++, profile.use_count());
  96   s->BindInt64(index++, profile.use_date().ToTimeT());
  97   s->BindInt64(index++, modification_date.ToTimeT());
  98   s->BindString(index++, profile.origin());
  99   s->BindString(index++, profile.language_code());
 100 }
 101
 102 scoped_ptr<AutofillProfile> AutofillProfileFromStatement(
 103     const sql::Statement& s) {
 104   scoped_ptr<AutofillProfile> profile(new AutofillProfile);
 105   int index = 0;
 106   profile->set_guid(s.ColumnString(index++));
 107   DCHECK(base::IsValidGUID(profile->guid()));
 108
 109   profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(index++));
 110   profile->SetRawInfo(ADDRESS_HOME_STREET_ADDRESS, s.ColumnString16(index++));
 111   profile->SetRawInfo(ADDRESS_HOME_DEPENDENT_LOCALITY,
 112                       s.ColumnString16(index++));
 113   profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++));
 114   profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++));
 115   profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++));
 116   profile->SetRawInfo(ADDRESS_HOME_SORTING_CODE, s.ColumnString16(index++));
 117   profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(index++));
 118   profile->set_use_count(s.ColumnInt64(index++));
 119   profile->set_use_date(base::Time::FromTimeT(s.ColumnInt64(index++)));
 120   profile->set_modification_date(base::Time::FromTimeT(s.ColumnInt64(index++)));
 121   profile->set_origin(s.ColumnString(index++));
 122   profile->set_language_code(s.ColumnString(index++));
 123
 124   return profile.Pass();
 125 }
 126
 127 void BindEncryptedCardToColumn(sql::Statement* s,
 128                                  int column_index,
 129                                  const base::string16& number) {
 130   std::string encrypted_data;
 131   OSCrypt::EncryptString16(number, &encrypted_data);
 132   s->BindBlob(column_index, encrypted_data.data(),
 133               static_cast<int>(encrypted_data.length()));
 134
 135 }
 136
 137 void BindCreditCardToStatement(const CreditCard& credit_card,
 138                                const base::Time& modification_date,
 139                                sql::Statement* s) {
 140   DCHECK(base::IsValidGUID(credit_card.guid()));
 141   int index = 0;
 142   s->BindString(index++, credit_card.guid());
 143
 144   s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_NAME));
 145   s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_EXP_MONTH));
 146   s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_EXP_4_DIGIT_YEAR));
 147   BindEncryptedCardToColumn(s, index++,
 148                             credit_card.GetRawInfo(CREDIT_CARD_NUMBER));
 149
 150   s->BindInt64(index++, credit_card.use_count());
 151   s->BindInt64(index++, credit_card.use_date().ToTimeT());
 152   s->BindInt64(index++, modification_date.ToTimeT());
 153   s->BindString(index++, credit_card.origin());
 154 }
 155
 156 base::string16 UnencryptedCardFromColumn(const sql::Statement& s,
 157                                          int column_index) {
 158   base::string16 credit_card_number;
 159   int encrypted_number_len = s.ColumnByteLength(column_index);
 160   if (encrypted_number_len) {
 161     std::string encrypted_number;
 162     encrypted_number.resize(encrypted_number_len);
 163     memcpy(&encrypted_number[0], s.ColumnBlob(column_index),
 164            encrypted_number_len);
 165     OSCrypt::DecryptString16(encrypted_number, &credit_card_number);
 166   }
 167   return credit_card_number;
 168 }
 169
 170 scoped_ptr<CreditCard> CreditCardFromStatement(const sql::Statement& s) {
 171   scoped_ptr<CreditCard> credit_card(new CreditCard);
 172
 173   int index = 0;
 174   credit_card->set_guid(s.ColumnString(index++));
 175   DCHECK(base::IsValidGUID(credit_card->guid()));
 176
 177   credit_card->SetRawInfo(CREDIT_CARD_NAME, s.ColumnString16(index++));
 178   credit_card->SetRawInfo(CREDIT_CARD_EXP_MONTH, s.ColumnString16(index++));
 179   credit_card->SetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR,
 180                           s.ColumnString16(index++));
 181   credit_card->SetRawInfo(CREDIT_CARD_NUMBER,
 182                           UnencryptedCardFromColumn(s, index++));
 183   credit_card->set_use_count(s.ColumnInt64(index++));
 184   credit_card->set_use_date(base::Time::FromTimeT(s.ColumnInt64(index++)));
 185   credit_card->set_modification_date(
 186       base::Time::FromTimeT(s.ColumnInt64(index++)));
 187   credit_card->set_origin(s.ColumnString(index++));
 188
 189   return credit_card.Pass();
 190 }
 191
 192 bool AddAutofillProfileNamesToProfile(sql::Connection* db,
 193                                       AutofillProfile* profile) {
 194   sql::Statement s(db->GetUniqueStatement(
 195       "SELECT guid, first_name, middle_name, last_name, full_name "
 196       "FROM autofill_profile_names "
 197       "WHERE guid=?"));
 198   s.BindString(0, profile->guid());
 199
 200   if (!s.is_valid())
 201     return false;
 202
 203   std::vector<base::string16> first_names;
 204   std::vector<base::string16> middle_names;
 205   std::vector<base::string16> last_names;
 206   std::vector<base::string16> full_names;
 207   while (s.Step()) {
 208     DCHECK_EQ(profile->guid(), s.ColumnString(0));
 209     first_names.push_back(s.ColumnString16(1));
 210     middle_names.push_back(s.ColumnString16(2));
 211     last_names.push_back(s.ColumnString16(3));
 212     full_names.push_back(s.ColumnString16(4));
 213   }
 214   if (!s.Succeeded())
 215     return false;
 216
 217   profile->SetRawMultiInfo(NAME_FIRST, first_names);
 218   profile->SetRawMultiInfo(NAME_MIDDLE, middle_names);
 219   profile->SetRawMultiInfo(NAME_LAST, last_names);
 220   profile->SetRawMultiInfo(NAME_FULL, full_names);
 221   return true;
 222 }
 223
 224 bool AddAutofillProfileEmailsToProfile(sql::Connection* db,
 225                                        AutofillProfile* profile) {
 226   sql::Statement s(db->GetUniqueStatement(
 227       "SELECT guid, email "
 228       "FROM autofill_profile_emails "
 229       "WHERE guid=?"));
 230   s.BindString(0, profile->guid());
 231
 232   if (!s.is_valid())
 233     return false;
 234
 235   std::vector<base::string16> emails;
 236   while (s.Step()) {
 237     DCHECK_EQ(profile->guid(), s.ColumnString(0));
 238     emails.push_back(s.ColumnString16(1));
 239   }
 240   if (!s.Succeeded())
 241     return false;
 242
 243   profile->SetRawMultiInfo(EMAIL_ADDRESS, emails);
 244   return true;
 245 }
 246
 247 bool AddAutofillProfilePhonesToProfile(sql::Connection* db,
 248                                        AutofillProfile* profile) {
 249   sql::Statement s(db->GetUniqueStatement(
 250       "SELECT guid, number "
 251       "FROM autofill_profile_phones "
 252       "WHERE guid=?"));
 253   s.BindString(0, profile->guid());
 254
 255   if (!s.is_valid())
 256     return false;
 257
 258   std::vector<base::string16> numbers;
 259   while (s.Step()) {
 260     DCHECK_EQ(profile->guid(), s.ColumnString(0));
 261     numbers.push_back(s.ColumnString16(1));
 262   }
 263   if (!s.Succeeded())
 264     return false;
 265
 266   profile->SetRawMultiInfo(PHONE_HOME_WHOLE_NUMBER, numbers);
 267   return true;
 268 }
 269
 270 bool AddAutofillProfileNames(const AutofillProfile& profile,
 271                              sql::Connection* db) {
 272   std::vector<base::string16> first_names;
 273   profile.GetRawMultiInfo(NAME_FIRST, &first_names);
 274   std::vector<base::string16> middle_names;
 275   profile.GetRawMultiInfo(NAME_MIDDLE, &middle_names);
 276   std::vector<base::string16> last_names;
 277   profile.GetRawMultiInfo(NAME_LAST, &last_names);
 278   std::vector<base::string16> full_names;
 279   profile.GetRawMultiInfo(NAME_FULL, &full_names);
 280   DCHECK_EQ(first_names.size(), middle_names.size());
 281   DCHECK_EQ(first_names.size(), last_names.size());
 282   DCHECK_EQ(first_names.size(), full_names.size());
 283
 284   for (size_t i = 0; i < first_names.size(); ++i) {
 285     // Add the new name.
 286     sql::Statement s(db->GetUniqueStatement(
 287         "INSERT INTO autofill_profile_names"
 288         " (guid, first_name, middle_name, last_name, full_name) "
 289         "VALUES (?,?,?,?,?)"));
 290     s.BindString(0, profile.guid());
 291     s.BindString16(1, first_names[i]);
 292     s.BindString16(2, middle_names[i]);
 293     s.BindString16(3, last_names[i]);
 294     s.BindString16(4, full_names[i]);
 295
 296     if (!s.Run())
 297       return false;
 298   }
 299   return true;
 300 }
 301
 302 bool AddAutofillProfileEmails(const AutofillProfile& profile,
 303                               sql::Connection* db) {
 304   std::vector<base::string16> emails;
 305   profile.GetRawMultiInfo(EMAIL_ADDRESS, &emails);
 306
 307   for (size_t i = 0; i < emails.size(); ++i) {
 308     // Add the new email.
 309     sql::Statement s(db->GetUniqueStatement(
 310       "INSERT INTO autofill_profile_emails"
 311       " (guid, email) "
 312       "VALUES (?,?)"));
 313     s.BindString(0, profile.guid());
 314     s.BindString16(1, emails[i]);
 315
 316     if (!s.Run())
 317       return false;
 318   }
 319
 320   return true;
 321 }
 322
 323 bool AddAutofillProfilePhones(const AutofillProfile& profile,
 324                               sql::Connection* db) {
 325   std::vector<base::string16> numbers;
 326   profile.GetRawMultiInfo(PHONE_HOME_WHOLE_NUMBER, &numbers);
 327
 328   for (size_t i = 0; i < numbers.size(); ++i) {
 329     // Add the new number.
 330     sql::Statement s(db->GetUniqueStatement(
 331       "INSERT INTO autofill_profile_phones"
 332       " (guid, number) "
 333       "VALUES (?,?)"));
 334     s.BindString(0, profile.guid());
 335     s.BindString16(1, numbers[i]);
 336
 337     if (!s.Run())
 338       return false;
 339   }
 340
 341   return true;
 342 }
 343
 344 bool AddAutofillProfilePieces(const AutofillProfile& profile,
 345                               sql::Connection* db) {
 346   if (!AddAutofillProfileNames(profile, db))
 347     return false;
 348
 349   if (!AddAutofillProfileEmails(profile, db))
 350     return false;
 351
 352   if (!AddAutofillProfilePhones(profile, db))
 353     return false;
 354
 355   return true;
 356 }
 357
 358 bool RemoveAutofillProfilePieces(const std::string& guid, sql::Connection* db) {
 359   sql::Statement s1(db->GetUniqueStatement(
 360       "DELETE FROM autofill_profile_names WHERE guid = ?"));
 361   s1.BindString(0, guid);
 362
 363   if (!s1.Run())
 364     return false;
 365
 366   sql::Statement s2(db->GetUniqueStatement(
 367       "DELETE FROM autofill_profile_emails WHERE guid = ?"));
 368   s2.BindString(0, guid);
 369
 370   if (!s2.Run())
 371     return false;
 372
 373   sql::Statement s3(db->GetUniqueStatement(
 374       "DELETE FROM autofill_profile_phones WHERE guid = ?"));
 375   s3.BindString(0, guid);
 376
 377   return s3.Run();
 378 }
 379
 380 WebDatabaseTable::TypeKey GetKey() {
 381   // We just need a unique constant. Use the address of a static that
 382   // COMDAT folding won't touch in an optimizing linker.
 383   static int table_key = 0;
 384   return reinterpret_cast<void*>(&table_key);
 385 }
 386
 387 time_t GetEndTime(const base::Time& end) {
 388   if (end.is_null() || end == base::Time::Max())
 389     return std::numeric_limits<time_t>::max();
 390
 391   return end.ToTimeT();
 392 }
 393
 394 std::string ServerStatusEnumToString(CreditCard::ServerStatus status) {
 395   switch (status) {
 396     case CreditCard::EXPIRED:
 397       return "EXPIRED";
 398
 399     case CreditCard::OK:
 400       return "OK";
 401   }
 402
 403   NOTREACHED();
 404   return "OK";
 405 }
 406
 407 CreditCard::ServerStatus ServerStatusStringToEnum(const std::string& status) {
 408   if (status == "EXPIRED")
 409     return CreditCard::EXPIRED;
 410
 411   DCHECK_EQ("OK", status);
 412   return CreditCard::OK;
 413 }
 414
 415 }  // namespace
 416
 417 // The maximum length allowed for form data.
 418 const size_t AutofillTable::kMaxDataLength = 1024;
 419
 420 AutofillTable::AutofillTable(const std::string& app_locale)
 421     : app_locale_(app_locale) {
 422 }
 423
 424 AutofillTable::~AutofillTable() {
 425 }
 426
 427 AutofillTable* AutofillTable::FromWebDatabase(WebDatabase* db) {
 428   return static_cast<AutofillTable*>(db->GetTable(GetKey()));
 429 }
 430
 431 WebDatabaseTable::TypeKey AutofillTable::GetTypeKey() const {
 432   return GetKey();
 433 }
 434
 435 bool AutofillTable::CreateTablesIfNecessary() {
 436   return (InitMainTable() && InitCreditCardsTable() && InitProfilesTable() &&
 437           InitProfileNamesTable() && InitProfileEmailsTable() &&
 438           InitProfilePhonesTable() && InitProfileTrashTable() &&
 439           InitMaskedCreditCardsTable() && InitUnmaskedCreditCardsTable() &&
 440           InitServerAddressesTable());
 441 }
 442
 443 bool AutofillTable::IsSyncable() {
 444   return true;
 445 }
 446
 447 bool AutofillTable::MigrateToVersion(int version,
 448                                      bool* update_compatible_version) {
 449   // Migrate if necessary.
 450   switch (version) {
 451     case 54:
 452       *update_compatible_version = true;
 453       return MigrateToVersion54AddI18nFieldsAndRemoveDeprecatedFields();
 454     case 55:
 455       *update_compatible_version = true;
 456       return MigrateToVersion55MergeAutofillDatesTable();
 457     case 56:
 458       *update_compatible_version = true;
 459       return MigrateToVersion56AddProfileLanguageCodeForFormatting();
 460     case 57:
 461       *update_compatible_version = true;
 462       return MigrateToVersion57AddFullNameField();
 463     case 60:
 464       *update_compatible_version = false;
 465       return MigrateToVersion60AddServerCards();
 466     case 61:
 467       *update_compatible_version = false;
 468       return MigrateToVersion61AddUsageStats();
 469     case 62:
 470       *update_compatible_version = false;
 471       return MigrateToVersion62AddUsageStatsForUnmaskedCards();
 472     case 63:
 473       *update_compatible_version = false;
 474       return MigrateToVersion63AddServerRecipientName();
 475     case 64:
 476       *update_compatible_version = false;
 477       return MigrateToVersion64AddUnmaskDate();
 478   }
 479   return true;
 480 }
 481
 482 bool AutofillTable::AddFormFieldValues(
 483     const std::vector<FormFieldData>& elements,
 484     std::vector<AutofillChange>* changes) {
 485   return AddFormFieldValuesTime(elements, changes, Time::Now());
 486 }
 487
 488 bool AutofillTable::AddFormFieldValue(const FormFieldData& element,
 489                                       std::vector<AutofillChange>* changes) {
 490   return AddFormFieldValueTime(element, changes, Time::Now());
 491 }
 492
 493 bool AutofillTable::GetFormValuesForElementName(
 494     const base::string16& name,
 495     const base::string16& prefix,
 496     std::vector<base::string16>* values,
 497     int limit) {
 498   DCHECK(values);
 499   sql::Statement s;
 500
 501   if (prefix.empty()) {
 502     s.Assign(db_->GetUniqueStatement(
 503         "SELECT value FROM autofill "
 504         "WHERE name = ? "
 505         "ORDER BY count DESC "
 506         "LIMIT ?"));
 507     s.BindString16(0, name);
 508     s.BindInt(1, limit);
 509   } else {
 510     base::string16 prefix_lower = base::i18n::ToLower(prefix);
 511     base::string16 next_prefix = prefix_lower;
 512     next_prefix[next_prefix.length() - 1]++;
 513
 514     s.Assign(db_->GetUniqueStatement(
 515         "SELECT value FROM autofill "
 516         "WHERE name = ? AND "
 517         "value_lower >= ? AND "
 518         "value_lower < ? "
 519         "ORDER BY count DESC "
 520         "LIMIT ?"));
 521     s.BindString16(0, name);
 522     s.BindString16(1, prefix_lower);
 523     s.BindString16(2, next_prefix);
 524     s.BindInt(3, limit);
 525   }
 526
 527   values->clear();
 528   while (s.Step())
 529     values->push_back(s.ColumnString16(0));
 530   return s.Succeeded();
 531 }
 532
 533 bool AutofillTable::HasFormElements() {
 534   sql::Statement s(db_->GetUniqueStatement("SELECT COUNT(*) FROM autofill"));
 535   if (!s.Step()) {
 536     NOTREACHED();
 537     return false;
 538   }
 539   return s.ColumnInt(0) > 0;
 540 }
 541
 542 bool AutofillTable::RemoveFormElementsAddedBetween(
 543     const Time& delete_begin,
 544     const Time& delete_end,
 545     std::vector<AutofillChange>* changes) {
 546   const time_t delete_begin_time_t = delete_begin.ToTimeT();
 547   const time_t delete_end_time_t = GetEndTime(delete_end);
 548
 549   // Query for the name, value, count, and access dates of all form elements
 550   // that were used between the given times.
 551   sql::Statement s(db_->GetUniqueStatement(
 552       "SELECT name, value, count, date_created, date_last_used FROM autofill "
 553       "WHERE (date_created >= ? AND date_created < ?) OR "
 554       "      (date_last_used >= ? AND date_last_used < ?)"));
 555   s.BindInt64(0, delete_begin_time_t);
 556   s.BindInt64(1, delete_end_time_t);
 557   s.BindInt64(2, delete_begin_time_t);
 558   s.BindInt64(3, delete_end_time_t);
 559
 560   std::vector<AutofillUpdate> updates;
 561   std::vector<AutofillChange> tentative_changes;
 562   while (s.Step()) {
 563     base::string16 name = s.ColumnString16(0);
 564     base::string16 value = s.ColumnString16(1);
 565     int count = s.ColumnInt(2);
 566     time_t date_created_time_t = s.ColumnInt64(3);
 567     time_t date_last_used_time_t = s.ColumnInt64(4);
 568
 569     // If *all* uses of the element were between |delete_begin| and
 570     // |delete_end|, then delete the element.  Otherwise, update the use
 571     // timestamps and use count.
 572     AutofillChange::Type change_type;
 573     if (date_created_time_t >= delete_begin_time_t &&
 574         date_last_used_time_t < delete_end_time_t) {
 575       change_type = AutofillChange::REMOVE;
 576     } else {
 577       change_type = AutofillChange::UPDATE;
 578
 579       // For all updated elements, set either date_created or date_last_used so
 580       // that the range [date_created, date_last_used] no longer overlaps with
 581       // [delete_begin, delete_end). Update the count by interpolating.
 582       // Precisely, compute the average amount of time between increments to the
 583       // count in the original range [date_created, date_last_used]:
 584       //   avg_delta = (date_last_used_orig - date_created_orig) / (count - 1)
 585       // The count can be expressed as
 586       //   count = 1 + (date_last_used - date_created) / avg_delta
 587       // Hence, update the count to
 588       //   count_new = 1 + (date_last_used_new - date_created_new) / avg_delta
 589       //             = 1 + ((count - 1) *
 590       //                    (date_last_used_new - date_created_new) /
 591       //                    (date_last_used_orig - date_created_orig))
 592       // Interpolating might not give a result that completely accurately
 593       // reflects the user's history, but it's the best that can be done given
 594       // the information in the database.
 595       AutofillUpdate updated_entry;
 596       updated_entry.name = name;
 597       updated_entry.value = value;
 598       updated_entry.date_created =
 599           date_created_time_t < delete_begin_time_t ?
 600           date_created_time_t :
 601           delete_end_time_t;
 602       updated_entry.date_last_used =
 603           date_last_used_time_t >= delete_end_time_t ?
 604           date_last_used_time_t :
 605           delete_begin_time_t - 1;
 606       updated_entry.count =
 607           1 +
 608           Round(1.0 * (count - 1) *
 609                 (updated_entry.date_last_used - updated_entry.date_created) /
 610                 (date_last_used_time_t - date_created_time_t));
 611       updates.push_back(updated_entry);
 612     }
 613
 614     tentative_changes.push_back(
 615         AutofillChange(change_type, AutofillKey(name, value)));
 616   }
 617   if (!s.Succeeded())
 618     return false;
 619
 620   // As a single transaction, remove or update the elements appropriately.
 621   sql::Statement s_delete(db_->GetUniqueStatement(
 622       "DELETE FROM autofill WHERE date_created >= ? AND date_last_used < ?"));
 623   s_delete.BindInt64(0, delete_begin_time_t);
 624   s_delete.BindInt64(1, delete_end_time_t);
 625   sql::Transaction transaction(db_);
 626   if (!transaction.Begin())
 627     return false;
 628   if (!s_delete.Run())
 629     return false;
 630   for (size_t i = 0; i < updates.size(); ++i) {
 631     sql::Statement s_update(db_->GetUniqueStatement(
 632       "UPDATE autofill SET date_created = ?, date_last_used = ?, count = ?"
 633       "WHERE name = ? AND value = ?"));
 634     s_update.BindInt64(0, updates[i].date_created);
 635     s_update.BindInt64(1, updates[i].date_last_used);
 636     s_update.BindInt(2, updates[i].count);
 637     s_update.BindString16(3, updates[i].name);
 638     s_update.BindString16(4, updates[i].value);
 639     if (!s_update.Run())
 640       return false;
 641   }
 642   if (!transaction.Commit())
 643     return false;
 644
 645   *changes = tentative_changes;
 646   return true;
 647 }
 648
 649 bool AutofillTable::RemoveExpiredFormElements(
 650     std::vector<AutofillChange>* changes) {
 651   base::Time expiration_time =
 652       base::Time::Now() - base::TimeDelta::FromDays(kExpirationPeriodInDays);
 653
 654   // Query for the name and value of all form elements that were last used
 655   // before the |expiration_time|.
 656   sql::Statement select_for_delete(db_->GetUniqueStatement(
 657       "SELECT name, value FROM autofill WHERE date_last_used < ?"));
 658   select_for_delete.BindInt64(0, expiration_time.ToTimeT());
 659   std::vector<AutofillChange> tentative_changes;
 660   while (select_for_delete.Step()) {
 661     base::string16 name = select_for_delete.ColumnString16(0);
 662     base::string16 value = select_for_delete.ColumnString16(1);
 663     tentative_changes.push_back(
 664         AutofillChange(AutofillChange::REMOVE, AutofillKey(name, value)));
 665   }
 666
 667   if (!select_for_delete.Succeeded())
 668     return false;
 669
 670   sql::Statement delete_data_statement(db_->GetUniqueStatement(
 671       "DELETE FROM autofill WHERE date_last_used < ?"));
 672   delete_data_statement.BindInt64(0, expiration_time.ToTimeT());
 673   if (!delete_data_statement.Run())
 674     return false;
 675
 676   *changes = tentative_changes;
 677   return true;
 678 }
 679
 680 bool AutofillTable::AddFormFieldValuesTime(
 681     const std::vector<FormFieldData>& elements,
 682     std::vector<AutofillChange>* changes,
 683     Time time) {
 684   // Only add one new entry for each unique element name.  Use |seen_names| to
 685   // track this.  Add up to |kMaximumUniqueNames| unique entries per form.
 686   const size_t kMaximumUniqueNames = 256;
 687   std::set<base::string16> seen_names;
 688   bool result = true;
 689   for (std::vector<FormFieldData>::const_iterator itr = elements.begin();
 690        itr != elements.end(); ++itr) {
 691     if (seen_names.size() >= kMaximumUniqueNames)
 692       break;
 693     if (seen_names.find(itr->name) != seen_names.end())
 694       continue;
 695     result = result && AddFormFieldValueTime(*itr, changes, time);
 696     seen_names.insert(itr->name);
 697   }
 698   return result;
 699 }
 700
 701 bool AutofillTable::GetAllAutofillEntries(std::vector<AutofillEntry>* entries) {
 702   sql::Statement s(db_->GetUniqueStatement(
 703       "SELECT name, value, date_created, date_last_used FROM autofill"));
 704
 705   while (s.Step()) {
 706     base::string16 name = s.ColumnString16(0);
 707     base::string16 value = s.ColumnString16(1);
 708     Time date_created = Time::FromTimeT(s.ColumnInt64(2));
 709     Time date_last_used = Time::FromTimeT(s.ColumnInt64(3));
 710     entries->push_back(
 711         AutofillEntry(AutofillKey(name, value), date_created, date_last_used));
 712   }
 713
 714   return s.Succeeded();
 715 }
 716
 717 bool AutofillTable::GetAutofillTimestamps(const base::string16& name,
 718                                           const base::string16& value,
 719                                           Time* date_created,
 720                                           Time* date_last_used) {
 721   sql::Statement s(db_->GetUniqueStatement(
 722       "SELECT date_created, date_last_used FROM autofill "
 723       "WHERE name = ? AND value = ?"));
 724   s.BindString16(0, name);
 725   s.BindString16(1, value);
 726   if (!s.Step())
 727     return false;
 728
 729   *date_created = Time::FromTimeT(s.ColumnInt64(0));
 730   *date_last_used = Time::FromTimeT(s.ColumnInt64(1));
 731
 732   DCHECK(!s.Step());
 733   return true;
 734 }
 735
 736 bool AutofillTable::UpdateAutofillEntries(
 737     const std::vector<AutofillEntry>& entries) {
 738   if (entries.empty())
 739     return true;
 740
 741   // Remove all existing entries.
 742   for (size_t i = 0; i < entries.size(); ++i) {
 743     sql::Statement s(db_->GetUniqueStatement(
 744         "DELETE FROM autofill WHERE name = ? AND value = ?"));
 745     s.BindString16(0, entries[i].key().name());
 746     s.BindString16(1, entries[i].key().value());
 747     if (!s.Run())
 748       return false;
 749   }
 750
 751   // Insert all the supplied autofill entries.
 752   for (size_t i = 0; i < entries.size(); ++i) {
 753     if (!InsertAutofillEntry(entries[i]))
 754       return false;
 755   }
 756
 757   return true;
 758 }
 759
 760 bool AutofillTable::InsertAutofillEntry(const AutofillEntry& entry) {
 761   std::string sql =
 762       "INSERT INTO autofill "
 763       "(name, value, value_lower, date_created, date_last_used, count) "
 764       "VALUES (?, ?, ?, ?, ?, ?)";
 765   sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
 766   s.BindString16(0, entry.key().name());
 767   s.BindString16(1, entry.key().value());
 768   s.BindString16(2, base::i18n::ToLower(entry.key().value()));
 769   s.BindInt64(3, entry.date_created().ToTimeT());
 770   s.BindInt64(4, entry.date_last_used().ToTimeT());
 771   // TODO(isherman): The counts column is currently synced implicitly as the
 772   // number of timestamps.  Sync the value explicitly instead, since the DB now
 773   // only saves the first and last timestamp, which makes counting timestamps
 774   // completely meaningless as a way to track frequency of usage.
 775   s.BindInt(5, entry.date_last_used() == entry.date_created() ? 1 : 2);
 776   return s.Run();
 777 }
 778
 779 bool AutofillTable::AddFormFieldValueTime(const FormFieldData& element,
 780                                           std::vector<AutofillChange>* changes,
 781                                           Time time) {
 782   sql::Statement s_exists(db_->GetUniqueStatement(
 783       "SELECT COUNT(*) FROM autofill WHERE name = ? AND value = ?"));
 784   s_exists.BindString16(0, element.name);
 785   s_exists.BindString16(1, element.value);
 786   if (!s_exists.Step())
 787     return false;
 788
 789   bool already_exists = s_exists.ColumnInt(0) > 0;
 790   if (already_exists) {
 791     sql::Statement s(db_->GetUniqueStatement(
 792         "UPDATE autofill SET date_last_used = ?, count = count + 1 "
 793         "WHERE name = ? AND value = ?"));
 794     s.BindInt64(0, time.ToTimeT());
 795     s.BindString16(1, element.name);
 796     s.BindString16(2, element.value);
 797     if (!s.Run())
 798       return false;
 799   } else {
 800     time_t time_as_time_t = time.ToTimeT();
 801     sql::Statement s(db_->GetUniqueStatement(
 802         "INSERT INTO autofill "
 803         "(name, value, value_lower, date_created, date_last_used, count) "
 804         "VALUES (?, ?, ?, ?, ?, ?)"));
 805     s.BindString16(0, element.name);
 806     s.BindString16(1, element.value);
 807     s.BindString16(2, base::i18n::ToLower(element.value));
 808     s.BindInt64(3, time_as_time_t);
 809     s.BindInt64(4, time_as_time_t);
 810     s.BindInt(5, 1);
 811     if (!s.Run())
 812       return false;
 813   }
 814
 815   AutofillChange::Type change_type =
 816       already_exists ? AutofillChange::UPDATE : AutofillChange::ADD;
 817   changes->push_back(
 818       AutofillChange(change_type, AutofillKey(element.name, element.value)));
 819   return true;
 820 }
 821
 822
 823 bool AutofillTable::RemoveFormElement(const base::string16& name,
 824                                       const base::string16& value) {
 825   sql::Statement s(db_->GetUniqueStatement(
 826       "DELETE FROM autofill WHERE name = ? AND value= ?"));
 827   s.BindString16(0, name);
 828   s.BindString16(1, value);
 829   return s.Run();
 830 }
 831
 832 bool AutofillTable::AddAutofillProfile(const AutofillProfile& profile) {
 833   if (IsAutofillGUIDInTrash(profile.guid()))
 834     return true;
 835
 836   sql::Statement s(db_->GetUniqueStatement(
 837       "INSERT INTO autofill_profiles"
 838       "(guid, company_name, street_address, dependent_locality, city, state,"
 839       " zipcode, sorting_code, country_code, use_count, use_date, "
 840       " date_modified, origin, language_code)"
 841       "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"));
 842   BindAutofillProfileToStatement(profile, base::Time::Now(), &s);
 843
 844   if (!s.Run())
 845     return false;
 846
 847   return AddAutofillProfilePieces(profile, db_);
 848 }
 849
 850 bool AutofillTable::GetAutofillProfile(const std::string& guid,
 851                                        AutofillProfile** profile) {
 852   DCHECK(base::IsValidGUID(guid));
 853   DCHECK(profile);
 854   sql::Statement s(db_->GetUniqueStatement(
 855       "SELECT guid, company_name, street_address, dependent_locality, city,"
 856       " state, zipcode, sorting_code, country_code, use_count, use_date,"
 857       " date_modified, origin, language_code "
 858       "FROM autofill_profiles "
 859       "WHERE guid=?"));
 860   s.BindString(0, guid);
 861
 862   if (!s.Step())
 863     return false;
 864
 865   scoped_ptr<AutofillProfile> p = AutofillProfileFromStatement(s);
 866
 867   // Get associated name info.
 868   AddAutofillProfileNamesToProfile(db_, p.get());
 869
 870   // Get associated email info.
 871   AddAutofillProfileEmailsToProfile(db_, p.get());
 872
 873   // Get associated phone info.
 874   AddAutofillProfilePhonesToProfile(db_, p.get());
 875
 876   *profile = p.release();
 877   return true;
 878 }
 879
 880 bool AutofillTable::GetAutofillProfiles(
 881     std::vector<AutofillProfile*>* profiles) {
 882   DCHECK(profiles);
 883   profiles->clear();
 884
 885   sql::Statement s(db_->GetUniqueStatement(
 886       "SELECT guid "
 887       "FROM autofill_profiles "
 888       "ORDER BY date_modified DESC, guid"));
 889
 890   while (s.Step()) {
 891     std::string guid = s.ColumnString(0);
 892     AutofillProfile* profile = NULL;
 893     if (!GetAutofillProfile(guid, &profile))
 894       return false;
 895     profiles->push_back(profile);
 896   }
 897
 898   return s.Succeeded();
 899 }
 900
 901 bool AutofillTable::GetServerProfiles(std::vector<AutofillProfile*>* profiles) {
 902   profiles->clear();
 903
 904   sql::Statement s(db_->GetUniqueStatement(
 905       "SELECT "
 906         "id,"
 907         "recipient_name,"
 908         "company_name,"
 909         "street_address,"
 910         "address_1,"  // ADDRESS_HOME_STATE
 911         "address_2,"  // ADDRESS_HOME_CITY
 912         "address_3,"  // ADDRESS_HOME_DEPENDENT_LOCALITY
 913         "address_4,"  // Not supported in AutofillProfile yet.
 914         "postal_code,"  // ADDRESS_HOME_ZIP
 915         "sorting_code,"  // ADDRESS_HOME_SORTING_CODE
 916         "country_code,"  // ADDRESS_HOME_COUNTRY
 917         "language_code "
 918       "FROM server_addresses"));
 919
 920   while (s.Step()) {
 921     int index = 0;
 922     scoped_ptr<AutofillProfile> profile(new AutofillProfile(
 923         AutofillProfile::SERVER_PROFILE, s.ColumnString(index++)));
 924
 925     base::string16 recipient_name = s.ColumnString16(index++);
 926     profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(index++));
 927     profile->SetRawInfo(ADDRESS_HOME_STREET_ADDRESS, s.ColumnString16(index++));
 928     profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++));
 929     profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++));
 930     profile->SetRawInfo(ADDRESS_HOME_DEPENDENT_LOCALITY,
 931                         s.ColumnString16(index++));
 932     index++;  // Skip address_4 which we haven't added to AutofillProfile yet.
 933     profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++));
 934     profile->SetRawInfo(ADDRESS_HOME_SORTING_CODE, s.ColumnString16(index++));
 935     profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(index++));
 936     profile->set_language_code(s.ColumnString(index++));
 937
 938     // SetInfo instead of SetRawInfo on the name so the constituent pieces will
 939     // be parsed.
 940     profile->SetInfo(AutofillType(NAME_FULL), recipient_name,
 941                      profile->language_code());
 942
 943     profiles->push_back(profile.release());
 944   }
 945
 946   return s.Succeeded();
 947 }
 948
 949 void AutofillTable::SetServerProfiles(
 950     const std::vector<AutofillProfile>& profiles) {
 951   // Delete all old ones first.
 952   sql::Statement delete_old(db_->GetUniqueStatement(
 953       "DELETE FROM server_addresses"));
 954   delete_old.Run();
 955
 956   sql::Statement insert(db_->GetUniqueStatement(
 957       "INSERT INTO server_addresses("
 958         "id,"
 959         "recipient_name,"
 960         "company_name,"
 961         "street_address,"
 962         "address_1,"  // ADDRESS_HOME_STATE
 963         "address_2,"  // ADDRESS_HOME_CITY
 964         "address_3,"  // ADDRESS_HOME_DEPENDENT_LOCALITY
 965         "address_4,"  // Not supported in AutofillProfile yet.
 966         "postal_code,"  // ADDRESS_HOME_ZIP
 967         "sorting_code,"  // ADDRESS_HOME_SORTING_CODE
 968         "country_code,"  // ADDRESS_HOME_COUNTRY
 969         "language_code) "
 970       "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"));
 971   for (const auto& profile : profiles) {
 972     DCHECK(profile.record_type() == AutofillProfile::SERVER_PROFILE);
 973
 974     int index = 0;
 975     insert.BindString(index++, profile.server_id());
 976     insert.BindString16(index++, profile.GetRawInfo(NAME_FULL));
 977     insert.BindString16(index++, profile.GetRawInfo(COMPANY_NAME));
 978     insert.BindString16(index++,
 979                         profile.GetRawInfo(ADDRESS_HOME_STREET_ADDRESS));
 980     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_STATE));
 981     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_CITY));
 982     insert.BindString16(index++,
 983                         profile.GetRawInfo(ADDRESS_HOME_DEPENDENT_LOCALITY));
 984     index++;  // SKip address_4 which we haven't added to AutofillProfile yet.
 985     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_ZIP));
 986     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_SORTING_CODE));
 987     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_COUNTRY));
 988     insert.BindString(index++, profile.language_code());
 989
 990     insert.Run();
 991     insert.Reset(true);
 992   }
 993 }
 994
 995 bool AutofillTable::UpdateAutofillProfile(const AutofillProfile& profile) {
 996   DCHECK(base::IsValidGUID(profile.guid()));
 997
 998   // Don't update anything until the trash has been emptied.  There may be
 999   // pending modifications to process.
1000   if (!IsAutofillProfilesTrashEmpty())
1001     return true;
1002
1003   AutofillProfile* tmp_profile = NULL;
1004   if (!GetAutofillProfile(profile.guid(), &tmp_profile))
1005     return false;
1006
1007   scoped_ptr<AutofillProfile> old_profile(tmp_profile);
1008   bool update_modification_date = *old_profile != profile;
1009
1010   sql::Statement s(db_->GetUniqueStatement(
1011       "UPDATE autofill_profiles "
1012       "SET guid=?, company_name=?, street_address=?, dependent_locality=?, "
1013       "    city=?, state=?, zipcode=?, sorting_code=?, country_code=?, "
1014       "    use_count=?, use_date=?, date_modified=?, origin=?, language_code=? "
1015       "WHERE guid=?"));
1016   BindAutofillProfileToStatement(
1017       profile,
1018       update_modification_date ? base::Time::Now() :
1019                                  old_profile->modification_date(),
1020       &s);
1021   s.BindString(14, profile.guid());
1022
1023   bool result = s.Run();
1024   DCHECK_GT(db_->GetLastChangeCount(), 0);
1025   if (!result)
1026     return result;
1027
1028   // Remove the old names, emails, and phone numbers.
1029   if (!RemoveAutofillProfilePieces(profile.guid(), db_))
1030     return false;
1031
1032   return AddAutofillProfilePieces(profile, db_);
1033 }
1034
1035 bool AutofillTable::RemoveAutofillProfile(const std::string& guid) {
1036   DCHECK(base::IsValidGUID(guid));
1037
1038   if (IsAutofillGUIDInTrash(guid)) {
1039     sql::Statement s_trash(db_->GetUniqueStatement(
1040         "DELETE FROM autofill_profiles_trash WHERE guid = ?"));
1041     s_trash.BindString(0, guid);
1042
1043     bool success = s_trash.Run();
1044     DCHECK_GT(db_->GetLastChangeCount(), 0) << "Expected item in trash";
1045     return success;
1046   }
1047
1048   sql::Statement s(db_->GetUniqueStatement(
1049       "DELETE FROM autofill_profiles WHERE guid = ?"));
1050   s.BindString(0, guid);
1051
1052   if (!s.Run())
1053     return false;
1054
1055   return RemoveAutofillProfilePieces(guid, db_);
1056 }
1057
1058 bool AutofillTable::ClearAutofillProfiles() {
1059   sql::Statement s1(db_->GetUniqueStatement(
1060       "DELETE FROM autofill_profiles"));
1061
1062   if (!s1.Run())
1063     return false;
1064
1065   sql::Statement s2(db_->GetUniqueStatement(
1066       "DELETE FROM autofill_profile_names"));
1067
1068   if (!s2.Run())
1069     return false;
1070
1071   sql::Statement s3(db_->GetUniqueStatement(
1072       "DELETE FROM autofill_profile_emails"));
1073
1074   if (!s3.Run())
1075     return false;
1076
1077   sql::Statement s4(db_->GetUniqueStatement(
1078       "DELETE FROM autofill_profile_phones"));
1079
1080   return s4.Run();
1081 }
1082
1083 bool AutofillTable::AddCreditCard(const CreditCard& credit_card) {
1084   sql::Statement s(db_->GetUniqueStatement(
1085       "INSERT INTO credit_cards"
1086       "(guid, name_on_card, expiration_month, expiration_year, "
1087       " card_number_encrypted, use_count, use_date, date_modified, origin)"
1088       "VALUES (?,?,?,?,?,?,?,?,?)"));
1089   BindCreditCardToStatement(credit_card, base::Time::Now(), &s);
1090
1091   if (!s.Run())
1092     return false;
1093
1094   DCHECK_GT(db_->GetLastChangeCount(), 0);
1095   return true;
1096 }
1097
1098 bool AutofillTable::GetCreditCard(const std::string& guid,
1099                                   CreditCard** credit_card) {
1100   DCHECK(base::IsValidGUID(guid));
1101   sql::Statement s(db_->GetUniqueStatement(
1102       "SELECT guid, name_on_card, expiration_month, expiration_year, "
1103              "card_number_encrypted, use_count, use_date, date_modified, "
1104              "origin "
1105       "FROM credit_cards "
1106       "WHERE guid = ?"));
1107   s.BindString(0, guid);
1108
1109   if (!s.Step())
1110     return false;
1111
1112   *credit_card = CreditCardFromStatement(s).release();
1113   return true;
1114 }
1115
1116 bool AutofillTable::GetCreditCards(
1117     std::vector<CreditCard*>* credit_cards) {
1118   DCHECK(credit_cards);
1119   credit_cards->clear();
1120
1121   sql::Statement s(db_->GetUniqueStatement(
1122       "SELECT guid "
1123       "FROM credit_cards "
1124       "ORDER BY date_modified DESC, guid"));
1125
1126   while (s.Step()) {
1127     std::string guid = s.ColumnString(0);
1128     CreditCard* credit_card = NULL;
1129     if (!GetCreditCard(guid, &credit_card))
1130       return false;
1131     credit_cards->push_back(credit_card);
1132   }
1133
1134   return s.Succeeded();
1135 }
1136
1137 bool AutofillTable::GetServerCreditCards(
1138     std::vector<CreditCard*>* credit_cards) {
1139   credit_cards->clear();
1140
1141   sql::Statement s(db_->GetUniqueStatement(
1142       "SELECT "
1143       "card_number_encrypted, "  // 0
1144       "last_four,"     // 1
1145       "masked.id,"     // 2
1146       "use_count,"     // 3
1147       "use_date,"      // 4
1148       "type,"          // 5
1149       "status,"        // 6
1150       "name_on_card,"  // 7
1151       "exp_month,"     // 8
1152       "exp_year "      // 9
1153       "FROM masked_credit_cards masked "
1154       "LEFT OUTER JOIN unmasked_credit_cards unmasked "
1155       "ON masked.id = unmasked.id"));
1156   while (s.Step()) {
1157     int index = 0;
1158
1159     // If the card_number_encrypted field is nonempty, we can assume this card
1160     // is a full card, otherwise it's masked.
1161     base::string16 full_card_number = UnencryptedCardFromColumn(s, index++);
1162     base::string16 last_four = s.ColumnString16(index++);
1163     CreditCard::RecordType record_type = full_card_number.empty() ?
1164         CreditCard::MASKED_SERVER_CARD :
1165         CreditCard::FULL_SERVER_CARD;
1166     std::string server_id = s.ColumnString(index++);
1167
1168     CreditCard* card = new CreditCard(record_type, server_id);
1169     card->SetRawInfo(
1170         CREDIT_CARD_NUMBER,
1171         record_type == CreditCard::MASKED_SERVER_CARD ? last_four
1172                                                       : full_card_number);
1173     int64 use_count = s.ColumnInt64(index++);
1174     int64 use_date = s.ColumnInt64(index++);
1175     std::string card_type = s.ColumnString(index++);
1176     if (record_type == CreditCard::MASKED_SERVER_CARD) {
1177       // The type must be set after setting the number to override the
1178       // autodectected type.
1179       card->SetTypeForMaskedCard(card_type.c_str());
1180       DCHECK_EQ(0, use_count);
1181       DCHECK_EQ(0, use_date);
1182     } else {
1183       DCHECK_EQ(CreditCard::GetCreditCardType(full_card_number), card_type);
1184       card->set_use_count(use_count);
1185       card->set_use_date(base::Time::FromInternalValue(use_date));
1186     }
1187
1188     card->SetServerStatus(ServerStatusStringToEnum(s.ColumnString(index++)));
1189     card->SetRawInfo(CREDIT_CARD_NAME, s.ColumnString16(index++));
1190     card->SetRawInfo(CREDIT_CARD_EXP_MONTH, s.ColumnString16(index++));
1191     card->SetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR, s.ColumnString16(index++));
1192     credit_cards->push_back(card);
1193   }
1194
1195   return s.Succeeded();
1196 }
1197
1198 void AutofillTable::SetServerCreditCards(
1199     const std::vector<CreditCard>& credit_cards) {
1200   sql::Transaction transaction(db_);
1201   if (!transaction.Begin())
1202     return;
1203
1204   // Delete all old values.
1205   sql::Statement masked_delete(db_->GetUniqueStatement(
1206       "DELETE FROM masked_credit_cards"));
1207   masked_delete.Run();
1208
1209   // Delete all items in the unmasked table that aren't in the new set.
1210   sql::Statement get_unmasked(db_->GetUniqueStatement(
1211       "SELECT id FROM unmasked_credit_cards"));
1212   while (get_unmasked.Step()) {
1213     // We expect relatively few cards, just do brute-force.
1214     std::string server_id = get_unmasked.ColumnString(0);
1215     bool found_card = false;
1216     for (const CreditCard& cur_card : credit_cards) {
1217       if (cur_card.server_id() == server_id) {
1218         found_card = true;
1219         break;
1220       }
1221     }
1222     if (!found_card) {
1223       // This unmasked card in the DB isn't present in the input. The statement
1224       // is compiled every time because it's much more likely that this is never
1225       // executed than it runs more than once.
1226       sql::Statement unmasked_delete(db_->GetUniqueStatement(
1227           "DELETE FROM unmasked_credit_cards WHERE id = ?"));
1228       unmasked_delete.BindString(0, server_id);
1229       unmasked_delete.Run();
1230       DCHECK_EQ(1, db_->GetLastChangeCount());
1231     }
1232   }
1233
1234   sql::Statement masked_insert(db_->GetUniqueStatement(
1235       "INSERT INTO masked_credit_cards("
1236       "id,"            // 0
1237       "type,"          // 1
1238       "status,"        // 2
1239       "name_on_card,"  // 3
1240       "last_four,"     // 4
1241       "exp_month,"     // 4
1242       "exp_year) "     // 5
1243       "VALUES (?,?,?,?,?,?,?)"));
1244   for (const CreditCard& card : credit_cards) {
1245     DCHECK_EQ(CreditCard::MASKED_SERVER_CARD, card.record_type());
1246
1247     masked_insert.BindString(0, card.server_id());
1248     masked_insert.BindString(1, card.type());
1249     masked_insert.BindString(2,
1250                              ServerStatusEnumToString(card.GetServerStatus()));
1251     masked_insert.BindString16(3, card.GetRawInfo(CREDIT_CARD_NAME));
1252     masked_insert.BindString16(4, card.LastFourDigits());
1253     masked_insert.BindString16(5, card.GetRawInfo(CREDIT_CARD_EXP_MONTH));
1254     masked_insert.BindString16(6,
1255                                card.GetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR));
1256
1257     masked_insert.Run();
1258     masked_insert.Reset(true);
1259   }
1260
1261   transaction.Commit();
1262 }
1263
1264 bool AutofillTable::UnmaskServerCreditCard(const std::string& id,
1265                                            const base::string16& full_number) {
1266   // Make sure there aren't duplicates for this card.
1267   MaskServerCreditCard(id);
1268   sql::Statement s(db_->GetUniqueStatement(
1269       "INSERT INTO unmasked_credit_cards("
1270           "id,"
1271           "card_number_encrypted,"
1272           "use_count,"
1273           "use_date,"
1274           "unmask_date)"
1275       "VALUES (?,?,?,?,?)"));
1276   s.BindString(0, id);
1277
1278   std::string encrypted_data;
1279   OSCrypt::EncryptString16(full_number, &encrypted_data);
1280   s.BindBlob(1, encrypted_data.data(),
1281              static_cast<int>(encrypted_data.length()));
1282
1283   // Unmasking counts as a usage, so set the stats accordingly.
1284   base::Time now = base::Time::Now();
1285   s.BindInt64(2, 1);                      // use_count
1286   s.BindInt64(3, now.ToInternalValue());  // use_date
1287
1288   s.BindInt64(4, now.ToInternalValue());  // unmask_date
1289
1290   s.Run();
1291   return db_->GetLastChangeCount() > 0;
1292 }
1293
1294 bool AutofillTable::MaskServerCreditCard(const std::string& id) {
1295   sql::Statement s(db_->GetUniqueStatement(
1296       "DELETE FROM unmasked_credit_cards WHERE id = ?"));
1297   s.BindString(0, id);
1298   s.Run();
1299   return db_->GetLastChangeCount() > 0;
1300 }
1301
1302 bool AutofillTable::UpdateUnmaskedCardUsageStats(
1303     const CreditCard& credit_card) {
1304   DCHECK_EQ(CreditCard::FULL_SERVER_CARD, credit_card.record_type());
1305
1306   sql::Statement s(db_->GetUniqueStatement(
1307       "UPDATE unmasked_credit_cards "
1308       "SET use_count=?, use_date=? "
1309       "WHERE id=?"));
1310   s.BindInt64(0, credit_card.use_count());
1311   s.BindInt64(1, credit_card.use_date().ToInternalValue());
1312   s.BindString(2, credit_card.server_id());
1313   s.Run();
1314   return db_->GetLastChangeCount() > 0;
1315 }
1316
1317 bool AutofillTable::UpdateCreditCard(const CreditCard& credit_card) {
1318   DCHECK(base::IsValidGUID(credit_card.guid()));
1319
1320   CreditCard* tmp_credit_card = NULL;
1321   if (!GetCreditCard(credit_card.guid(), &tmp_credit_card))
1322     return false;
1323
1324   scoped_ptr<CreditCard> old_credit_card(tmp_credit_card);
1325   bool update_modification_date = *old_credit_card != credit_card;
1326
1327   sql::Statement s(db_->GetUniqueStatement(
1328       "UPDATE credit_cards "
1329       "SET guid=?, name_on_card=?, expiration_month=?,"
1330           "expiration_year=?, card_number_encrypted=?, use_count=?, use_date=?,"
1331           "date_modified=?, origin=?"
1332       "WHERE guid=?"));
1333   BindCreditCardToStatement(
1334       credit_card,
1335       update_modification_date ? base::Time::Now() :
1336                                  old_credit_card->modification_date(),
1337       &s);
1338   s.BindString(9, credit_card.guid());
1339
1340   bool result = s.Run();
1341   DCHECK_GT(db_->GetLastChangeCount(), 0);
1342   return result;
1343 }
1344
1345 bool AutofillTable::RemoveCreditCard(const std::string& guid) {
1346   DCHECK(base::IsValidGUID(guid));
1347   sql::Statement s(db_->GetUniqueStatement(
1348       "DELETE FROM credit_cards WHERE guid = ?"));
1349   s.BindString(0, guid);
1350
1351   return s.Run();
1352 }
1353
1354 bool AutofillTable::RemoveAutofillDataModifiedBetween(
1355     const Time& delete_begin,
1356     const Time& delete_end,
1357     std::vector<std::string>* profile_guids,
1358     std::vector<std::string>* credit_card_guids) {
1359   DCHECK(delete_end.is_null() || delete_begin < delete_end);
1360
1361   time_t delete_begin_t = delete_begin.ToTimeT();
1362   time_t delete_end_t = GetEndTime(delete_end);
1363
1364   // Remember Autofill profiles in the time range.
1365   sql::Statement s_profiles_get(db_->GetUniqueStatement(
1366       "SELECT guid FROM autofill_profiles "
1367       "WHERE date_modified >= ? AND date_modified < ?"));
1368   s_profiles_get.BindInt64(0, delete_begin_t);
1369   s_profiles_get.BindInt64(1, delete_end_t);
1370
1371   profile_guids->clear();
1372   while (s_profiles_get.Step()) {
1373     std::string guid = s_profiles_get.ColumnString(0);
1374     profile_guids->push_back(guid);
1375   }
1376   if (!s_profiles_get.Succeeded())
1377     return false;
1378
1379   // Remove Autofill profiles in the time range.
1380   sql::Statement s_profiles(db_->GetUniqueStatement(
1381       "DELETE FROM autofill_profiles "
1382       "WHERE date_modified >= ? AND date_modified < ?"));
1383   s_profiles.BindInt64(0, delete_begin_t);
1384   s_profiles.BindInt64(1, delete_end_t);
1385
1386   if (!s_profiles.Run())
1387     return false;
1388
1389   // Remember Autofill credit cards in the time range.
1390   sql::Statement s_credit_cards_get(db_->GetUniqueStatement(
1391       "SELECT guid FROM credit_cards "
1392       "WHERE date_modified >= ? AND date_modified < ?"));
1393   s_credit_cards_get.BindInt64(0, delete_begin_t);
1394   s_credit_cards_get.BindInt64(1, delete_end_t);
1395
1396   credit_card_guids->clear();
1397   while (s_credit_cards_get.Step()) {
1398     std::string guid = s_credit_cards_get.ColumnString(0);
1399     credit_card_guids->push_back(guid);
1400   }
1401   if (!s_credit_cards_get.Succeeded())
1402     return false;
1403
1404   // Remove Autofill credit cards in the time range.
1405   sql::Statement s_credit_cards(db_->GetUniqueStatement(
1406       "DELETE FROM credit_cards "
1407       "WHERE date_modified >= ? AND date_modified < ?"));
1408   s_credit_cards.BindInt64(0, delete_begin_t);
1409   s_credit_cards.BindInt64(1, delete_end_t);
1410   if (!s_credit_cards.Run())
1411     return false;
1412
1413   // Remove unmasked credit cards in the time range.
1414   sql::Statement s_unmasked_cards(db_->GetUniqueStatement(
1415       "DELETE FROM unmasked_credit_cards "
1416       "WHERE unmask_date >= ? AND unmask_date < ?"));
1417   s_unmasked_cards.BindInt64(0, delete_begin.ToInternalValue());
1418   s_unmasked_cards.BindInt64(1, delete_end.ToInternalValue());
1419   return s_unmasked_cards.Run();
1420 }
1421
1422 bool AutofillTable::RemoveOriginURLsModifiedBetween(
1423     const Time& delete_begin,
1424     const Time& delete_end,
1425     ScopedVector<AutofillProfile>* profiles) {
1426   DCHECK(delete_end.is_null() || delete_begin < delete_end);
1427
1428   time_t delete_begin_t = delete_begin.ToTimeT();
1429   time_t delete_end_t = GetEndTime(delete_end);
1430
1431   // Remember Autofill profiles with URL origins in the time range.
1432   sql::Statement s_profiles_get(db_->GetUniqueStatement(
1433       "SELECT guid, origin FROM autofill_profiles "
1434       "WHERE date_modified >= ? AND date_modified < ?"));
1435   s_profiles_get.BindInt64(0, delete_begin_t);
1436   s_profiles_get.BindInt64(1, delete_end_t);
1437
1438   std::vector<std::string> profile_guids;
1439   while (s_profiles_get.Step()) {
1440     std::string guid = s_profiles_get.ColumnString(0);
1441     std::string origin = s_profiles_get.ColumnString(1);
1442     if (GURL(origin).is_valid())
1443       profile_guids.push_back(guid);
1444   }
1445   if (!s_profiles_get.Succeeded())
1446     return false;
1447
1448   // Clear out the origins for the found Autofill profiles.
1449   for (std::vector<std::string>::const_iterator it = profile_guids.begin();
1450        it != profile_guids.end(); ++it) {
1451     sql::Statement s_profile(db_->GetUniqueStatement(
1452         "UPDATE autofill_profiles SET origin='' WHERE guid=?"));
1453     s_profile.BindString(0, *it);
1454     if (!s_profile.Run())
1455       return false;
1456
1457     AutofillProfile* profile;
1458     if (!GetAutofillProfile(*it, &profile))
1459       return false;
1460
1461     profiles->push_back(profile);
1462   }
1463
1464   // Remember Autofill credit cards with URL origins in the time range.
1465   sql::Statement s_credit_cards_get(db_->GetUniqueStatement(
1466       "SELECT guid, origin FROM credit_cards "
1467       "WHERE date_modified >= ? AND date_modified < ?"));
1468   s_credit_cards_get.BindInt64(0, delete_begin_t);
1469   s_credit_cards_get.BindInt64(1, delete_end_t);
1470
1471   std::vector<std::string> credit_card_guids;
1472   while (s_credit_cards_get.Step()) {
1473     std::string guid = s_credit_cards_get.ColumnString(0);
1474     std::string origin = s_credit_cards_get.ColumnString(1);
1475     if (GURL(origin).is_valid())
1476       credit_card_guids.push_back(guid);
1477   }
1478   if (!s_credit_cards_get.Succeeded())
1479     return false;
1480
1481   // Clear out the origins for the found credit cards.
1482   for (std::vector<std::string>::const_iterator it = credit_card_guids.begin();
1483        it != credit_card_guids.end(); ++it) {
1484     sql::Statement s_credit_card(db_->GetUniqueStatement(
1485         "UPDATE credit_cards SET origin='' WHERE guid=?"));
1486     s_credit_card.BindString(0, *it);
1487     if (!s_credit_card.Run())
1488       return false;
1489   }
1490
1491   return true;
1492 }
1493
1494 bool AutofillTable::GetAutofillProfilesInTrash(
1495     std::vector<std::string>* guids) {
1496   guids->clear();
1497
1498   sql::Statement s(db_->GetUniqueStatement(
1499       "SELECT guid "
1500       "FROM autofill_profiles_trash"));
1501
1502   while (s.Step()) {
1503     std::string guid = s.ColumnString(0);
1504     guids->push_back(guid);
1505   }
1506
1507   return s.Succeeded();
1508 }
1509
1510 bool AutofillTable::EmptyAutofillProfilesTrash() {
1511   sql::Statement s(db_->GetUniqueStatement(
1512       "DELETE FROM autofill_profiles_trash"));
1513
1514   return s.Run();
1515 }
1516
1517
1518 bool AutofillTable::AddAutofillGUIDToTrash(const std::string& guid) {
1519   sql::Statement s(db_->GetUniqueStatement(
1520     "INSERT INTO autofill_profiles_trash"
1521     " (guid) "
1522     "VALUES (?)"));
1523   s.BindString(0, guid);
1524
1525   return s.Run();
1526 }
1527
1528 bool AutofillTable::IsAutofillProfilesTrashEmpty() {
1529   sql::Statement s(db_->GetUniqueStatement(
1530       "SELECT guid "
1531       "FROM autofill_profiles_trash"));
1532
1533   return !s.Step();
1534 }
1535
1536 bool AutofillTable::IsAutofillGUIDInTrash(const std::string& guid) {
1537   sql::Statement s(db_->GetUniqueStatement(
1538       "SELECT guid "
1539       "FROM autofill_profiles_trash "
1540       "WHERE guid = ?"));
1541   s.BindString(0, guid);
1542
1543   return s.Step();
1544 }
1545
1546 bool AutofillTable::InitMainTable() {
1547   if (!db_->DoesTableExist("autofill")) {
1548     if (!db_->Execute("CREATE TABLE autofill ("
1549                       "name VARCHAR, "
1550                       "value VARCHAR, "
1551                       "value_lower VARCHAR, "
1552                       "date_created INTEGER DEFAULT 0, "
1553                       "date_last_used INTEGER DEFAULT 0, "
1554                       "count INTEGER DEFAULT 1, "
1555                       "PRIMARY KEY (name, value))") ||
1556         !db_->Execute("CREATE INDEX autofill_name ON autofill (name)") ||
1557         !db_->Execute("CREATE INDEX autofill_name_value_lower ON "
1558                       "autofill (name, value_lower)")) {
1559        NOTREACHED();
1560        return false;
1561     }
1562   }
1563   return true;
1564 }
1565
1566 bool AutofillTable::InitCreditCardsTable() {
1567   if (!db_->DoesTableExist("credit_cards")) {
1568     if (!db_->Execute("CREATE TABLE credit_cards ( "
1569                       "guid VARCHAR PRIMARY KEY, "
1570                       "name_on_card VARCHAR, "
1571                       "expiration_month INTEGER, "
1572                       "expiration_year INTEGER, "
1573                       "card_number_encrypted BLOB, "
1574                       "date_modified INTEGER NOT NULL DEFAULT 0, "
1575                       "origin VARCHAR DEFAULT '', "
1576                       "use_count INTEGER NOT NULL DEFAULT 0, "
1577                       "use_date INTEGER NOT NULL DEFAULT 0) ")) {
1578       NOTREACHED();
1579       return false;
1580     }
1581   }
1582
1583   return true;
1584 }
1585
1586 bool AutofillTable::InitProfilesTable() {
1587   if (!db_->DoesTableExist("autofill_profiles")) {
1588     if (!db_->Execute("CREATE TABLE autofill_profiles ( "
1589                       "guid VARCHAR PRIMARY KEY, "
1590                       "company_name VARCHAR, "
1591                       "street_address VARCHAR, "
1592                       "dependent_locality VARCHAR, "
1593                       "city VARCHAR, "
1594                       "state VARCHAR, "
1595                       "zipcode VARCHAR, "
1596                       "sorting_code VARCHAR, "
1597                       "country_code VARCHAR, "
1598                       "date_modified INTEGER NOT NULL DEFAULT 0, "
1599                       "origin VARCHAR DEFAULT '', "
1600                       "language_code VARCHAR, "
1601                       "use_count INTEGER NOT NULL DEFAULT 0, "
1602                       "use_date INTEGER NOT NULL DEFAULT 0) ")) {
1603       NOTREACHED();
1604       return false;
1605     }
1606   }
1607   return true;
1608 }
1609
1610 bool AutofillTable::InitProfileNamesTable() {
1611   if (!db_->DoesTableExist("autofill_profile_names")) {
1612     if (!db_->Execute("CREATE TABLE autofill_profile_names ( "
1613                       "guid VARCHAR, "
1614                       "first_name VARCHAR, "
1615                       "middle_name VARCHAR, "
1616                       "last_name VARCHAR, "
1617                       "full_name VARCHAR)")) {
1618       NOTREACHED();
1619       return false;
1620     }
1621   }
1622   return true;
1623 }
1624
1625 bool AutofillTable::InitProfileEmailsTable() {
1626   if (!db_->DoesTableExist("autofill_profile_emails")) {
1627     if (!db_->Execute("CREATE TABLE autofill_profile_emails ( "
1628                       "guid VARCHAR, "
1629                       "email VARCHAR)")) {
1630       NOTREACHED();
1631       return false;
1632     }
1633   }
1634   return true;
1635 }
1636
1637 bool AutofillTable::InitProfilePhonesTable() {
1638   if (!db_->DoesTableExist("autofill_profile_phones")) {
1639     if (!db_->Execute("CREATE TABLE autofill_profile_phones ( "
1640                       "guid VARCHAR, "
1641                       "number VARCHAR)")) {
1642       NOTREACHED();
1643       return false;
1644     }
1645   }
1646   return true;
1647 }
1648
1649 bool AutofillTable::InitProfileTrashTable() {
1650   if (!db_->DoesTableExist("autofill_profiles_trash")) {
1651     if (!db_->Execute("CREATE TABLE autofill_profiles_trash ( "
1652                       "guid VARCHAR)")) {
1653       NOTREACHED();
1654       return false;
1655     }
1656   }
1657   return true;
1658 }
1659
1660 bool AutofillTable::InitMaskedCreditCardsTable() {
1661   if (!db_->DoesTableExist("masked_credit_cards")) {
1662     if (!db_->Execute("CREATE TABLE masked_credit_cards ("
1663                       "id VARCHAR,"
1664                       "status VARCHAR,"
1665                       "name_on_card VARCHAR,"
1666                       "type VARCHAR,"
1667                       "last_four VARCHAR,"
1668                       "exp_month INTEGER DEFAULT 0,"
1669                       "exp_year INTEGER DEFAULT 0)")) {
1670       NOTREACHED();
1671       return false;
1672     }
1673   }
1674   return true;
1675 }
1676
1677 bool AutofillTable::InitUnmaskedCreditCardsTable() {
1678   if (!db_->DoesTableExist("unmasked_credit_cards")) {
1679     if (!db_->Execute("CREATE TABLE unmasked_credit_cards ("
1680                       "id VARCHAR,"
1681                       "card_number_encrypted VARCHAR, "
1682                       "use_count INTEGER NOT NULL DEFAULT 0, "
1683                       "use_date INTEGER NOT NULL DEFAULT 0, "
1684                       "unmask_date INTEGER NOT NULL DEFAULT 0)")) {
1685       NOTREACHED();
1686       return false;
1687     }
1688   }
1689   return true;
1690 }
1691
1692 bool AutofillTable::InitServerAddressesTable() {
1693   if (!db_->DoesTableExist("server_addresses")) {
1694     // The space after language_code is necessary to match what sqlite does
1695     // when it appends the column in migration.
1696     if (!db_->Execute("CREATE TABLE server_addresses ("
1697                       "id VARCHAR,"
1698                       "company_name VARCHAR,"
1699                       "street_address VARCHAR,"
1700                       "address_1 VARCHAR,"
1701                       "address_2 VARCHAR,"
1702                       "address_3 VARCHAR,"
1703                       "address_4 VARCHAR,"
1704                       "postal_code VARCHAR,"
1705                       "sorting_code VARCHAR,"
1706                       "country_code VARCHAR,"
1707                       "language_code VARCHAR, "  // Space required.
1708                       "recipient_name VARCHAR, "  // Ditto.
1709                       "phone_number VARCHAR)")) {
1710       NOTREACHED();
1711       return false;
1712     }
1713   }
1714   return true;
1715 }
1716
1717 bool AutofillTable::MigrateToVersion54AddI18nFieldsAndRemoveDeprecatedFields() {
1718   sql::Transaction transaction(db_);
1719   if (!transaction.Begin())
1720     return false;
1721
1722   // Test the existence of the |address_line_1| column as an indication that a
1723   // migration is needed.  It is possible that the new |autofill_profile_phones|
1724   // schema is in place because the table was newly created when migrating from
1725   // a pre-version-23 database.
1726   if (db_->DoesColumnExist("autofill_profiles", "address_line_1")) {
1727     // Create a temporary copy of the autofill_profiles table in the (newer)
1728     // version 54 format.  This table
1729     //   (a) adds columns for street_address, dependent_locality, and
1730     //       sorting_code,
1731     //   (b) removes the address_line_1 and address_line_2 columns, which are
1732     //       replaced by the street_address column, and
1733     //   (c) removes the country column, which was long deprecated.
1734     if (db_->DoesTableExist("autofill_profiles_temp") ||
1735         !db_->Execute("CREATE TABLE autofill_profiles_temp ( "
1736                       "guid VARCHAR PRIMARY KEY, "
1737                       "company_name VARCHAR, "
1738                       "street_address VARCHAR, "
1739                       "dependent_locality VARCHAR, "
1740                       "city VARCHAR, "
1741                       "state VARCHAR, "
1742                       "zipcode VARCHAR, "
1743                       "sorting_code VARCHAR, "
1744                       "country_code VARCHAR, "
1745                       "date_modified INTEGER NOT NULL DEFAULT 0, "
1746                       "origin VARCHAR DEFAULT '')")) {
1747       return false;
1748     }
1749
1750     // Copy over the data from the autofill_profiles table, taking care to merge
1751     // the address lines 1 and 2 into the new street_address column.
1752     if (!db_->Execute("INSERT INTO autofill_profiles_temp "
1753                       "SELECT guid, company_name, '', '', city, state, zipcode,"
1754                       " '', country_code, date_modified, origin "
1755                       "FROM autofill_profiles")) {
1756       return false;
1757     }
1758     sql::Statement s(db_->GetUniqueStatement(
1759         "SELECT guid, address_line_1, address_line_2 FROM autofill_profiles"));
1760     while (s.Step()) {
1761       std::string guid = s.ColumnString(0);
1762       base::string16 line1 = s.ColumnString16(1);
1763       base::string16 line2 = s.ColumnString16(2);
1764       base::string16 street_address = line1;
1765       if (!line2.empty())
1766         street_address += base::ASCIIToUTF16("\n") + line2;
1767
1768       sql::Statement s_update(db_->GetUniqueStatement(
1769           "UPDATE autofill_profiles_temp SET street_address=? WHERE guid=?"));
1770       s_update.BindString16(0, street_address);
1771       s_update.BindString(1, guid);
1772       if (!s_update.Run())
1773         return false;
1774     }
1775     if (!s.Succeeded())
1776       return false;
1777
1778     // Delete the existing (version 53) table and replace it with the contents
1779     // of the temporary table.
1780     if (!db_->Execute("DROP TABLE autofill_profiles") ||
1781         !db_->Execute("ALTER TABLE autofill_profiles_temp "
1782                       "RENAME TO autofill_profiles")) {
1783       return false;
1784     }
1785   }
1786
1787   // Test the existence of the |type| column as an indication that a migration
1788   // is needed.  It is possible that the new |autofill_profile_phones| schema is
1789   // in place because the table was newly created when migrating from a
1790   // pre-version-23 database.
1791   if (db_->DoesColumnExist("autofill_profile_phones", "type")) {
1792     // Create a temporary copy of the autofill_profile_phones table in the
1793     // (newer) version 54 format.  This table removes the deprecated |type|
1794     // column.
1795     if (db_->DoesTableExist("autofill_profile_phones_temp") ||
1796         !db_->Execute("CREATE TABLE autofill_profile_phones_temp ( "
1797                       "guid VARCHAR, "
1798                       "number VARCHAR)")) {
1799       return false;
1800     }
1801
1802     // Copy over the data from the autofill_profile_phones table.
1803     if (!db_->Execute("INSERT INTO autofill_profile_phones_temp "
1804                       "SELECT guid, number FROM autofill_profile_phones")) {
1805       return false;
1806     }
1807
1808     // Delete the existing (version 53) table and replace it with the contents
1809     // of the temporary table.
1810     if (!db_->Execute("DROP TABLE autofill_profile_phones"))
1811       return false;
1812     if (!db_->Execute("ALTER TABLE autofill_profile_phones_temp "
1813                       "RENAME TO autofill_profile_phones")) {
1814       return false;
1815     }
1816   }
1817
1818   return transaction.Commit();
1819 }
1820
1821 bool AutofillTable::MigrateToVersion55MergeAutofillDatesTable() {
1822   sql::Transaction transaction(db_);
1823   if (!transaction.Begin())
1824     return false;
1825
1826   if (db_->DoesTableExist("autofill_temp") ||
1827       !db_->Execute("CREATE TABLE autofill_temp ("
1828                     "name VARCHAR, "
1829                     "value VARCHAR, "
1830                     "value_lower VARCHAR, "
1831                     "date_created INTEGER DEFAULT 0, "
1832                     "date_last_used INTEGER DEFAULT 0, "
1833                     "count INTEGER DEFAULT 1, "
1834                     "PRIMARY KEY (name, value))")) {
1835     return false;
1836   }
1837
1838   // Slurp up the data from the existing table and write it to the new table.
1839   sql::Statement s(db_->GetUniqueStatement(
1840       "SELECT name, value, value_lower, count, MIN(date_created),"
1841       " MAX(date_created) "
1842       "FROM autofill a JOIN autofill_dates ad ON a.pair_id=ad.pair_id "
1843       "GROUP BY name, value, value_lower, count"));
1844   while (s.Step()) {
1845     sql::Statement s_insert(db_->GetUniqueStatement(
1846         "INSERT INTO autofill_temp "
1847         "(name, value, value_lower, count, date_created, date_last_used) "
1848         "VALUES (?, ?, ?, ?, ?, ?)"));
1849     s_insert.BindString16(0, s.ColumnString16(0));
1850     s_insert.BindString16(1, s.ColumnString16(1));
1851     s_insert.BindString16(2, s.ColumnString16(2));
1852     s_insert.BindInt(3, s.ColumnInt(3));
1853     s_insert.BindInt64(4, s.ColumnInt64(4));
1854     s_insert.BindInt64(5, s.ColumnInt64(5));
1855     if (!s_insert.Run())
1856       return false;
1857   }
1858
1859   if (!s.Succeeded())
1860     return false;
1861
1862   // Delete the existing (version 54) tables and replace them with the contents
1863   // of the temporary table.
1864   if (!db_->Execute("DROP TABLE autofill") ||
1865       !db_->Execute("DROP TABLE autofill_dates") ||
1866       !db_->Execute("ALTER TABLE autofill_temp "
1867                     "RENAME TO autofill")) {
1868     return false;
1869   }
1870
1871   // Create indices on the new table, for fast lookups.
1872   if (!db_->Execute("CREATE INDEX autofill_name ON autofill (name)") ||
1873       !db_->Execute("CREATE INDEX autofill_name_value_lower ON "
1874                     "autofill (name, value_lower)")) {
1875     return false;
1876   }
1877
1878
1879   return transaction.Commit();
1880 }
1881
1882 bool AutofillTable::MigrateToVersion56AddProfileLanguageCodeForFormatting() {
1883   return db_->Execute("ALTER TABLE autofill_profiles "
1884                       "ADD COLUMN language_code VARCHAR");
1885 }
1886
1887 bool AutofillTable::MigrateToVersion57AddFullNameField() {
1888   return db_->Execute("ALTER TABLE autofill_profile_names "
1889                       "ADD COLUMN full_name VARCHAR");
1890 }
1891
1892 bool AutofillTable::MigrateToVersion60AddServerCards() {
1893   sql::Transaction transaction(db_);
1894   if (!transaction.Begin())
1895     return false;
1896
1897   if (!db_->DoesTableExist("masked_credit_cards") &&
1898       !db_->Execute("CREATE TABLE masked_credit_cards ("
1899                     "id VARCHAR,"
1900                     "status VARCHAR,"
1901                     "name_on_card VARCHAR,"
1902                     "type VARCHAR,"
1903                     "last_four VARCHAR,"
1904                     "exp_month INTEGER DEFAULT 0,"
1905                     "exp_year INTEGER DEFAULT 0)")) {
1906     return false;
1907   }
1908
1909   if (!db_->DoesTableExist("unmasked_credit_cards") &&
1910       !db_->Execute("CREATE TABLE unmasked_credit_cards ("
1911                     "id VARCHAR,"
1912                     "card_number_encrypted VARCHAR)")) {
1913     return false;
1914   }
1915
1916   if (!db_->DoesTableExist("server_addresses") &&
1917       !db_->Execute("CREATE TABLE server_addresses ("
1918                     "id VARCHAR,"
1919                     "company_name VARCHAR,"
1920                     "street_address VARCHAR,"
1921                     "address_1 VARCHAR,"
1922                     "address_2 VARCHAR,"
1923                     "address_3 VARCHAR,"
1924                     "address_4 VARCHAR,"
1925                     "postal_code VARCHAR,"
1926                     "sorting_code VARCHAR,"
1927                     "country_code VARCHAR,"
1928                     "language_code VARCHAR)")) {
1929     return false;
1930   }
1931
1932   return transaction.Commit();
1933 }
1934
1935 bool AutofillTable::MigrateToVersion61AddUsageStats() {
1936   sql::Transaction transaction(db_);
1937   if (!transaction.Begin())
1938     return false;
1939
1940   // Add use_count to autofill_profiles.
1941   if (!db_->DoesColumnExist("autofill_profiles", "use_count") &&
1942       !db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN "
1943                     "use_count INTEGER NOT NULL DEFAULT 0")) {
1944     return false;
1945   }
1946
1947   // Add use_date to autofill_profiles.
1948   if (!db_->DoesColumnExist("autofill_profiles", "use_date") &&
1949       !db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN "
1950                     "use_date INTEGER NOT NULL DEFAULT 0")) {
1951     return false;
1952   }
1953
1954   // Add use_count to credit_cards.
1955   if (!db_->DoesColumnExist("credit_cards", "use_count") &&
1956       !db_->Execute("ALTER TABLE credit_cards ADD COLUMN "
1957                     "use_count INTEGER NOT NULL DEFAULT 0")) {
1958     return false;
1959   }
1960
1961   // Add use_date to credit_cards.
1962   if (!db_->DoesColumnExist("credit_cards", "use_date") &&
1963       !db_->Execute("ALTER TABLE credit_cards ADD COLUMN "
1964                     "use_date INTEGER NOT NULL DEFAULT 0")) {
1965     return false;
1966   }
1967
1968   return transaction.Commit();
1969 }
1970
1971 bool AutofillTable::MigrateToVersion62AddUsageStatsForUnmaskedCards() {
1972   sql::Transaction transaction(db_);
1973   if (!transaction.Begin())
1974     return false;
1975
1976   // Add use_count to unmasked_credit_cards.
1977   if (!db_->DoesColumnExist("unmasked_credit_cards", "use_count") &&
1978       !db_->Execute("ALTER TABLE unmasked_credit_cards ADD COLUMN "
1979                     "use_count INTEGER NOT NULL DEFAULT 0")) {
1980     return false;
1981   }
1982
1983   // Add use_date to unmasked_credit_cards.
1984   if (!db_->DoesColumnExist("unmasked_credit_cards", "use_date") &&
1985       !db_->Execute("ALTER TABLE unmasked_credit_cards ADD COLUMN "
1986                     "use_date INTEGER NOT NULL DEFAULT 0")) {
1987     return false;
1988   }
1989
1990   return transaction.Commit();
1991 }
1992
1993 bool AutofillTable::MigrateToVersion63AddServerRecipientName() {
1994   if (!db_->DoesColumnExist("server_addresses", "recipient_name") &&
1995       !db_->Execute("ALTER TABLE server_addresses ADD COLUMN "
1996                     "recipient_name VARCHAR")) {
1997     return false;
1998   }
1999   return true;
2000 }
2001
2002 bool AutofillTable::MigrateToVersion64AddUnmaskDate() {
2003   sql::Transaction transaction(db_);
2004   if (!transaction.Begin())
2005     return false;
2006
2007   if (!db_->DoesColumnExist("unmasked_credit_cards", "unmask_date") &&
2008       !db_->Execute("ALTER TABLE unmasked_credit_cards ADD COLUMN "
2009                     "unmask_date INTEGER NOT NULL DEFAULT 0")) {
2010     return false;
2011   }
2012   if (!db_->DoesColumnExist("server_addresses", "phone_number") &&
2013       !db_->Execute("ALTER TABLE server_addresses ADD COLUMN "
2014                     "phone_number VARCHAR")) {
2015     return false;
2016   }
2017
2018   return transaction.Commit();
2019 }
2020
2021 }  // namespace autofill