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   }
 476   return true;
 477 }
 478
 479 bool AutofillTable::AddFormFieldValues(
 480     const std::vector<FormFieldData>& elements,
 481     std::vector<AutofillChange>* changes) {
 482   return AddFormFieldValuesTime(elements, changes, Time::Now());
 483 }
 484
 485 bool AutofillTable::AddFormFieldValue(const FormFieldData& element,
 486                                       std::vector<AutofillChange>* changes) {
 487   return AddFormFieldValueTime(element, changes, Time::Now());
 488 }
 489
 490 bool AutofillTable::GetFormValuesForElementName(
 491     const base::string16& name,
 492     const base::string16& prefix,
 493     std::vector<base::string16>* values,
 494     int limit) {
 495   DCHECK(values);
 496   sql::Statement s;
 497
 498   if (prefix.empty()) {
 499     s.Assign(db_->GetUniqueStatement(
 500         "SELECT value FROM autofill "
 501         "WHERE name = ? "
 502         "ORDER BY count DESC "
 503         "LIMIT ?"));
 504     s.BindString16(0, name);
 505     s.BindInt(1, limit);
 506   } else {
 507     base::string16 prefix_lower = base::i18n::ToLower(prefix);
 508     base::string16 next_prefix = prefix_lower;
 509     next_prefix[next_prefix.length() - 1]++;
 510
 511     s.Assign(db_->GetUniqueStatement(
 512         "SELECT value FROM autofill "
 513         "WHERE name = ? AND "
 514         "value_lower >= ? AND "
 515         "value_lower < ? "
 516         "ORDER BY count DESC "
 517         "LIMIT ?"));
 518     s.BindString16(0, name);
 519     s.BindString16(1, prefix_lower);
 520     s.BindString16(2, next_prefix);
 521     s.BindInt(3, limit);
 522   }
 523
 524   values->clear();
 525   while (s.Step())
 526     values->push_back(s.ColumnString16(0));
 527   return s.Succeeded();
 528 }
 529
 530 bool AutofillTable::HasFormElements() {
 531   sql::Statement s(db_->GetUniqueStatement("SELECT COUNT(*) FROM autofill"));
 532   if (!s.Step()) {
 533     NOTREACHED();
 534     return false;
 535   }
 536   return s.ColumnInt(0) > 0;
 537 }
 538
 539 bool AutofillTable::RemoveFormElementsAddedBetween(
 540     const Time& delete_begin,
 541     const Time& delete_end,
 542     std::vector<AutofillChange>* changes) {
 543   const time_t delete_begin_time_t = delete_begin.ToTimeT();
 544   const time_t delete_end_time_t = GetEndTime(delete_end);
 545
 546   // Query for the name, value, count, and access dates of all form elements
 547   // that were used between the given times.
 548   sql::Statement s(db_->GetUniqueStatement(
 549       "SELECT name, value, count, date_created, date_last_used FROM autofill "
 550       "WHERE (date_created >= ? AND date_created < ?) OR "
 551       "      (date_last_used >= ? AND date_last_used < ?)"));
 552   s.BindInt64(0, delete_begin_time_t);
 553   s.BindInt64(1, delete_end_time_t);
 554   s.BindInt64(2, delete_begin_time_t);
 555   s.BindInt64(3, delete_end_time_t);
 556
 557   std::vector<AutofillUpdate> updates;
 558   std::vector<AutofillChange> tentative_changes;
 559   while (s.Step()) {
 560     base::string16 name = s.ColumnString16(0);
 561     base::string16 value = s.ColumnString16(1);
 562     int count = s.ColumnInt(2);
 563     time_t date_created_time_t = s.ColumnInt64(3);
 564     time_t date_last_used_time_t = s.ColumnInt64(4);
 565
 566     // If *all* uses of the element were between |delete_begin| and
 567     // |delete_end|, then delete the element.  Otherwise, update the use
 568     // timestamps and use count.
 569     AutofillChange::Type change_type;
 570     if (date_created_time_t >= delete_begin_time_t &&
 571         date_last_used_time_t < delete_end_time_t) {
 572       change_type = AutofillChange::REMOVE;
 573     } else {
 574       change_type = AutofillChange::UPDATE;
 575
 576       // For all updated elements, set either date_created or date_last_used so
 577       // that the range [date_created, date_last_used] no longer overlaps with
 578       // [delete_begin, delete_end). Update the count by interpolating.
 579       // Precisely, compute the average amount of time between increments to the
 580       // count in the original range [date_created, date_last_used]:
 581       //   avg_delta = (date_last_used_orig - date_created_orig) / (count - 1)
 582       // The count can be expressed as
 583       //   count = 1 + (date_last_used - date_created) / avg_delta
 584       // Hence, update the count to
 585       //   count_new = 1 + (date_last_used_new - date_created_new) / avg_delta
 586       //             = 1 + ((count - 1) *
 587       //                    (date_last_used_new - date_created_new) /
 588       //                    (date_last_used_orig - date_created_orig))
 589       // Interpolating might not give a result that completely accurately
 590       // reflects the user's history, but it's the best that can be done given
 591       // the information in the database.
 592       AutofillUpdate updated_entry;
 593       updated_entry.name = name;
 594       updated_entry.value = value;
 595       updated_entry.date_created =
 596           date_created_time_t < delete_begin_time_t ?
 597           date_created_time_t :
 598           delete_end_time_t;
 599       updated_entry.date_last_used =
 600           date_last_used_time_t >= delete_end_time_t ?
 601           date_last_used_time_t :
 602           delete_begin_time_t - 1;
 603       updated_entry.count =
 604           1 +
 605           Round(1.0 * (count - 1) *
 606                 (updated_entry.date_last_used - updated_entry.date_created) /
 607                 (date_last_used_time_t - date_created_time_t));
 608       updates.push_back(updated_entry);
 609     }
 610
 611     tentative_changes.push_back(
 612         AutofillChange(change_type, AutofillKey(name, value)));
 613   }
 614   if (!s.Succeeded())
 615     return false;
 616
 617   // As a single transaction, remove or update the elements appropriately.
 618   sql::Statement s_delete(db_->GetUniqueStatement(
 619       "DELETE FROM autofill WHERE date_created >= ? AND date_last_used < ?"));
 620   s_delete.BindInt64(0, delete_begin_time_t);
 621   s_delete.BindInt64(1, delete_end_time_t);
 622   sql::Transaction transaction(db_);
 623   if (!transaction.Begin())
 624     return false;
 625   if (!s_delete.Run())
 626     return false;
 627   for (size_t i = 0; i < updates.size(); ++i) {
 628     sql::Statement s_update(db_->GetUniqueStatement(
 629       "UPDATE autofill SET date_created = ?, date_last_used = ?, count = ?"
 630       "WHERE name = ? AND value = ?"));
 631     s_update.BindInt64(0, updates[i].date_created);
 632     s_update.BindInt64(1, updates[i].date_last_used);
 633     s_update.BindInt(2, updates[i].count);
 634     s_update.BindString16(3, updates[i].name);
 635     s_update.BindString16(4, updates[i].value);
 636     if (!s_update.Run())
 637       return false;
 638   }
 639   if (!transaction.Commit())
 640     return false;
 641
 642   *changes = tentative_changes;
 643   return true;
 644 }
 645
 646 bool AutofillTable::RemoveExpiredFormElements(
 647     std::vector<AutofillChange>* changes) {
 648   base::Time expiration_time =
 649       base::Time::Now() - base::TimeDelta::FromDays(kExpirationPeriodInDays);
 650
 651   // Query for the name and value of all form elements that were last used
 652   // before the |expiration_time|.
 653   sql::Statement select_for_delete(db_->GetUniqueStatement(
 654       "SELECT name, value FROM autofill WHERE date_last_used < ?"));
 655   select_for_delete.BindInt64(0, expiration_time.ToTimeT());
 656   std::vector<AutofillChange> tentative_changes;
 657   while (select_for_delete.Step()) {
 658     base::string16 name = select_for_delete.ColumnString16(0);
 659     base::string16 value = select_for_delete.ColumnString16(1);
 660     tentative_changes.push_back(
 661         AutofillChange(AutofillChange::REMOVE, AutofillKey(name, value)));
 662   }
 663
 664   if (!select_for_delete.Succeeded())
 665     return false;
 666
 667   sql::Statement delete_data_statement(db_->GetUniqueStatement(
 668       "DELETE FROM autofill WHERE date_last_used < ?"));
 669   delete_data_statement.BindInt64(0, expiration_time.ToTimeT());
 670   if (!delete_data_statement.Run())
 671     return false;
 672
 673   *changes = tentative_changes;
 674   return true;
 675 }
 676
 677 bool AutofillTable::AddFormFieldValuesTime(
 678     const std::vector<FormFieldData>& elements,
 679     std::vector<AutofillChange>* changes,
 680     Time time) {
 681   // Only add one new entry for each unique element name.  Use |seen_names| to
 682   // track this.  Add up to |kMaximumUniqueNames| unique entries per form.
 683   const size_t kMaximumUniqueNames = 256;
 684   std::set<base::string16> seen_names;
 685   bool result = true;
 686   for (std::vector<FormFieldData>::const_iterator itr = elements.begin();
 687        itr != elements.end(); ++itr) {
 688     if (seen_names.size() >= kMaximumUniqueNames)
 689       break;
 690     if (seen_names.find(itr->name) != seen_names.end())
 691       continue;
 692     result = result && AddFormFieldValueTime(*itr, changes, time);
 693     seen_names.insert(itr->name);
 694   }
 695   return result;
 696 }
 697
 698 bool AutofillTable::GetAllAutofillEntries(std::vector<AutofillEntry>* entries) {
 699   sql::Statement s(db_->GetUniqueStatement(
 700       "SELECT name, value, date_created, date_last_used FROM autofill"));
 701
 702   while (s.Step()) {
 703     base::string16 name = s.ColumnString16(0);
 704     base::string16 value = s.ColumnString16(1);
 705     Time date_created = Time::FromTimeT(s.ColumnInt64(2));
 706     Time date_last_used = Time::FromTimeT(s.ColumnInt64(3));
 707     entries->push_back(
 708         AutofillEntry(AutofillKey(name, value), date_created, date_last_used));
 709   }
 710
 711   return s.Succeeded();
 712 }
 713
 714 bool AutofillTable::GetAutofillTimestamps(const base::string16& name,
 715                                           const base::string16& value,
 716                                           Time* date_created,
 717                                           Time* date_last_used) {
 718   sql::Statement s(db_->GetUniqueStatement(
 719       "SELECT date_created, date_last_used FROM autofill "
 720       "WHERE name = ? AND value = ?"));
 721   s.BindString16(0, name);
 722   s.BindString16(1, value);
 723   if (!s.Step())
 724     return false;
 725
 726   *date_created = Time::FromTimeT(s.ColumnInt64(0));
 727   *date_last_used = Time::FromTimeT(s.ColumnInt64(1));
 728
 729   DCHECK(!s.Step());
 730   return true;
 731 }
 732
 733 bool AutofillTable::UpdateAutofillEntries(
 734     const std::vector<AutofillEntry>& entries) {
 735   if (entries.empty())
 736     return true;
 737
 738   // Remove all existing entries.
 739   for (size_t i = 0; i < entries.size(); ++i) {
 740     sql::Statement s(db_->GetUniqueStatement(
 741         "DELETE FROM autofill WHERE name = ? AND value = ?"));
 742     s.BindString16(0, entries[i].key().name());
 743     s.BindString16(1, entries[i].key().value());
 744     if (!s.Run())
 745       return false;
 746   }
 747
 748   // Insert all the supplied autofill entries.
 749   for (size_t i = 0; i < entries.size(); ++i) {
 750     if (!InsertAutofillEntry(entries[i]))
 751       return false;
 752   }
 753
 754   return true;
 755 }
 756
 757 bool AutofillTable::InsertAutofillEntry(const AutofillEntry& entry) {
 758   std::string sql =
 759       "INSERT INTO autofill "
 760       "(name, value, value_lower, date_created, date_last_used, count) "
 761       "VALUES (?, ?, ?, ?, ?, ?)";
 762   sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
 763   s.BindString16(0, entry.key().name());
 764   s.BindString16(1, entry.key().value());
 765   s.BindString16(2, base::i18n::ToLower(entry.key().value()));
 766   s.BindInt64(3, entry.date_created().ToTimeT());
 767   s.BindInt64(4, entry.date_last_used().ToTimeT());
 768   // TODO(isherman): The counts column is currently synced implicitly as the
 769   // number of timestamps.  Sync the value explicitly instead, since the DB now
 770   // only saves the first and last timestamp, which makes counting timestamps
 771   // completely meaningless as a way to track frequency of usage.
 772   s.BindInt(5, entry.date_last_used() == entry.date_created() ? 1 : 2);
 773   return s.Run();
 774 }
 775
 776 bool AutofillTable::AddFormFieldValueTime(const FormFieldData& element,
 777                                           std::vector<AutofillChange>* changes,
 778                                           Time time) {
 779   sql::Statement s_exists(db_->GetUniqueStatement(
 780       "SELECT COUNT(*) FROM autofill WHERE name = ? AND value = ?"));
 781   s_exists.BindString16(0, element.name);
 782   s_exists.BindString16(1, element.value);
 783   if (!s_exists.Step())
 784     return false;
 785
 786   bool already_exists = s_exists.ColumnInt(0) > 0;
 787   if (already_exists) {
 788     sql::Statement s(db_->GetUniqueStatement(
 789         "UPDATE autofill SET date_last_used = ?, count = count + 1 "
 790         "WHERE name = ? AND value = ?"));
 791     s.BindInt64(0, time.ToTimeT());
 792     s.BindString16(1, element.name);
 793     s.BindString16(2, element.value);
 794     if (!s.Run())
 795       return false;
 796   } else {
 797     time_t time_as_time_t = time.ToTimeT();
 798     sql::Statement s(db_->GetUniqueStatement(
 799         "INSERT INTO autofill "
 800         "(name, value, value_lower, date_created, date_last_used, count) "
 801         "VALUES (?, ?, ?, ?, ?, ?)"));
 802     s.BindString16(0, element.name);
 803     s.BindString16(1, element.value);
 804     s.BindString16(2, base::i18n::ToLower(element.value));
 805     s.BindInt64(3, time_as_time_t);
 806     s.BindInt64(4, time_as_time_t);
 807     s.BindInt(5, 1);
 808     if (!s.Run())
 809       return false;
 810   }
 811
 812   AutofillChange::Type change_type =
 813       already_exists ? AutofillChange::UPDATE : AutofillChange::ADD;
 814   changes->push_back(
 815       AutofillChange(change_type, AutofillKey(element.name, element.value)));
 816   return true;
 817 }
 818
 819
 820 bool AutofillTable::RemoveFormElement(const base::string16& name,
 821                                       const base::string16& value) {
 822   sql::Statement s(db_->GetUniqueStatement(
 823       "DELETE FROM autofill WHERE name = ? AND value= ?"));
 824   s.BindString16(0, name);
 825   s.BindString16(1, value);
 826   return s.Run();
 827 }
 828
 829 bool AutofillTable::AddAutofillProfile(const AutofillProfile& profile) {
 830   if (IsAutofillGUIDInTrash(profile.guid()))
 831     return true;
 832
 833   sql::Statement s(db_->GetUniqueStatement(
 834       "INSERT INTO autofill_profiles"
 835       "(guid, company_name, street_address, dependent_locality, city, state,"
 836       " zipcode, sorting_code, country_code, use_count, use_date, "
 837       " date_modified, origin, language_code)"
 838       "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"));
 839   BindAutofillProfileToStatement(profile, base::Time::Now(), &s);
 840
 841   if (!s.Run())
 842     return false;
 843
 844   return AddAutofillProfilePieces(profile, db_);
 845 }
 846
 847 bool AutofillTable::GetAutofillProfile(const std::string& guid,
 848                                        AutofillProfile** profile) {
 849   DCHECK(base::IsValidGUID(guid));
 850   DCHECK(profile);
 851   sql::Statement s(db_->GetUniqueStatement(
 852       "SELECT guid, company_name, street_address, dependent_locality, city,"
 853       " state, zipcode, sorting_code, country_code, use_count, use_date,"
 854       " date_modified, origin, language_code "
 855       "FROM autofill_profiles "
 856       "WHERE guid=?"));
 857   s.BindString(0, guid);
 858
 859   if (!s.Step())
 860     return false;
 861
 862   scoped_ptr<AutofillProfile> p = AutofillProfileFromStatement(s);
 863
 864   // Get associated name info.
 865   AddAutofillProfileNamesToProfile(db_, p.get());
 866
 867   // Get associated email info.
 868   AddAutofillProfileEmailsToProfile(db_, p.get());
 869
 870   // Get associated phone info.
 871   AddAutofillProfilePhonesToProfile(db_, p.get());
 872
 873   *profile = p.release();
 874   return true;
 875 }
 876
 877 bool AutofillTable::GetAutofillProfiles(
 878     std::vector<AutofillProfile*>* profiles) {
 879   DCHECK(profiles);
 880   profiles->clear();
 881
 882   sql::Statement s(db_->GetUniqueStatement(
 883       "SELECT guid "
 884       "FROM autofill_profiles "
 885       "ORDER BY date_modified DESC, guid"));
 886
 887   while (s.Step()) {
 888     std::string guid = s.ColumnString(0);
 889     AutofillProfile* profile = NULL;
 890     if (!GetAutofillProfile(guid, &profile))
 891       return false;
 892     profiles->push_back(profile);
 893   }
 894
 895   return s.Succeeded();
 896 }
 897
 898 bool AutofillTable::GetAutofillServerProfiles(
 899     std::vector<AutofillProfile*>* profiles) {
 900   profiles->clear();
 901
 902   sql::Statement s(db_->GetUniqueStatement(
 903       "SELECT "
 904         "id,"
 905         "recipient_name,"
 906         "company_name,"
 907         "street_address,"
 908         "address_1,"  // ADDRESS_HOME_STATE
 909         "address_2,"  // ADDRESS_HOME_CITY
 910         "address_3,"  // ADDRESS_HOME_DEPENDENT_LOCALITY
 911         "address_4,"  // Not supported in AutofillProfile yet.
 912         "postal_code,"  // ADDRESS_HOME_ZIP
 913         "sorting_code,"  // ADDRESS_HOME_SORTING_CODE
 914         "country_code,"  // ADDRESS_HOME_COUNTRY
 915         "language_code "
 916       "FROM server_addresses"));
 917
 918   while (s.Step()) {
 919     int index = 0;
 920     scoped_ptr<AutofillProfile> profile(new AutofillProfile(
 921         AutofillProfile::SERVER_PROFILE, s.ColumnString(index++)));
 922
 923     profile->SetRawInfo(NAME_FULL, s.ColumnString16(index++));
 924     profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(index++));
 925     profile->SetRawInfo(ADDRESS_HOME_STREET_ADDRESS, s.ColumnString16(index++));
 926     profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++));
 927     profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++));
 928     profile->SetRawInfo(ADDRESS_HOME_DEPENDENT_LOCALITY,
 929                         s.ColumnString16(index++));
 930     index++;  // Skip address_4 which we haven't added to AutofillProfile yet.
 931     profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++));
 932     profile->SetRawInfo(ADDRESS_HOME_SORTING_CODE, s.ColumnString16(index++));
 933     profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(index++));
 934     profile->set_language_code(s.ColumnString(index++));
 935
 936     profiles->push_back(profile.release());
 937   }
 938
 939   return s.Succeeded();
 940 }
 941
 942 void AutofillTable::SetAutofillServerProfiles(
 943     const std::vector<AutofillProfile>& profiles) {
 944   // Delete all old ones first.
 945   sql::Statement delete_old(db_->GetUniqueStatement(
 946       "DELETE FROM server_addresses"));
 947   delete_old.Run();
 948
 949   sql::Statement insert(db_->GetUniqueStatement(
 950       "INSERT INTO server_addresses("
 951         "id,"
 952         "recipient_name,"
 953         "company_name,"
 954         "street_address,"
 955         "address_1,"  // ADDRESS_HOME_STATE
 956         "address_2,"  // ADDRESS_HOME_CITY
 957         "address_3,"  // ADDRESS_HOME_DEPENDENT_LOCALITY
 958         "address_4,"  // Not supported in AutofillProfile yet.
 959         "postal_code,"  // ADDRESS_HOME_ZIP
 960         "sorting_code,"  // ADDRESS_HOME_SORTING_CODE
 961         "country_code,"  // ADDRESS_HOME_COUNTRY
 962         "language_code) "
 963       "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"));
 964   for (const auto& profile : profiles) {
 965     DCHECK(profile.record_type() == AutofillProfile::SERVER_PROFILE);
 966
 967     int index = 0;
 968     insert.BindString(index++, profile.server_id());
 969     insert.BindString16(index++, profile.GetRawInfo(NAME_FULL));
 970     insert.BindString16(index++, profile.GetRawInfo(COMPANY_NAME));
 971     insert.BindString16(index++,
 972                         profile.GetRawInfo(ADDRESS_HOME_STREET_ADDRESS));
 973     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_STATE));
 974     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_CITY));
 975     insert.BindString16(index++,
 976                         profile.GetRawInfo(ADDRESS_HOME_DEPENDENT_LOCALITY));
 977     index++;  // SKip address_4 which we haven't added to AutofillProfile yet.
 978     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_ZIP));
 979     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_SORTING_CODE));
 980     insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_COUNTRY));
 981     insert.BindString(index++, profile.language_code());
 982
 983     insert.Run();
 984     insert.Reset(true);
 985   }
 986 }
 987
 988 bool AutofillTable::UpdateAutofillProfile(const AutofillProfile& profile) {
 989   DCHECK(base::IsValidGUID(profile.guid()));
 990
 991   // Don't update anything until the trash has been emptied.  There may be
 992   // pending modifications to process.
 993   if (!IsAutofillProfilesTrashEmpty())
 994     return true;
 995
 996   AutofillProfile* tmp_profile = NULL;
 997   if (!GetAutofillProfile(profile.guid(), &tmp_profile))
 998     return false;
 999
1000   scoped_ptr<AutofillProfile> old_profile(tmp_profile);
1001   bool update_modification_date = *old_profile != profile;
1002
1003   sql::Statement s(db_->GetUniqueStatement(
1004       "UPDATE autofill_profiles "
1005       "SET guid=?, company_name=?, street_address=?, dependent_locality=?, "
1006       "    city=?, state=?, zipcode=?, sorting_code=?, country_code=?, "
1007       "    use_count=?, use_date=?, date_modified=?, origin=?, language_code=? "
1008       "WHERE guid=?"));
1009   BindAutofillProfileToStatement(
1010       profile,
1011       update_modification_date ? base::Time::Now() :
1012                                  old_profile->modification_date(),
1013       &s);
1014   s.BindString(14, profile.guid());
1015
1016   bool result = s.Run();
1017   DCHECK_GT(db_->GetLastChangeCount(), 0);
1018   if (!result)
1019     return result;
1020
1021   // Remove the old names, emails, and phone numbers.
1022   if (!RemoveAutofillProfilePieces(profile.guid(), db_))
1023     return false;
1024
1025   return AddAutofillProfilePieces(profile, db_);
1026 }
1027
1028 bool AutofillTable::RemoveAutofillProfile(const std::string& guid) {
1029   DCHECK(base::IsValidGUID(guid));
1030
1031   if (IsAutofillGUIDInTrash(guid)) {
1032     sql::Statement s_trash(db_->GetUniqueStatement(
1033         "DELETE FROM autofill_profiles_trash WHERE guid = ?"));
1034     s_trash.BindString(0, guid);
1035
1036     bool success = s_trash.Run();
1037     DCHECK_GT(db_->GetLastChangeCount(), 0) << "Expected item in trash";
1038     return success;
1039   }
1040
1041   sql::Statement s(db_->GetUniqueStatement(
1042       "DELETE FROM autofill_profiles WHERE guid = ?"));
1043   s.BindString(0, guid);
1044
1045   if (!s.Run())
1046     return false;
1047
1048   return RemoveAutofillProfilePieces(guid, db_);
1049 }
1050
1051 bool AutofillTable::ClearAutofillProfiles() {
1052   sql::Statement s1(db_->GetUniqueStatement(
1053       "DELETE FROM autofill_profiles"));
1054
1055   if (!s1.Run())
1056     return false;
1057
1058   sql::Statement s2(db_->GetUniqueStatement(
1059       "DELETE FROM autofill_profile_names"));
1060
1061   if (!s2.Run())
1062     return false;
1063
1064   sql::Statement s3(db_->GetUniqueStatement(
1065       "DELETE FROM autofill_profile_emails"));
1066
1067   if (!s3.Run())
1068     return false;
1069
1070   sql::Statement s4(db_->GetUniqueStatement(
1071       "DELETE FROM autofill_profile_phones"));
1072
1073   return s4.Run();
1074 }
1075
1076 bool AutofillTable::AddCreditCard(const CreditCard& credit_card) {
1077   sql::Statement s(db_->GetUniqueStatement(
1078       "INSERT INTO credit_cards"
1079       "(guid, name_on_card, expiration_month, expiration_year, "
1080       " card_number_encrypted, use_count, use_date, date_modified, origin)"
1081       "VALUES (?,?,?,?,?,?,?,?,?)"));
1082   BindCreditCardToStatement(credit_card, base::Time::Now(), &s);
1083
1084   if (!s.Run())
1085     return false;
1086
1087   DCHECK_GT(db_->GetLastChangeCount(), 0);
1088   return true;
1089 }
1090
1091 bool AutofillTable::GetCreditCard(const std::string& guid,
1092                                   CreditCard** credit_card) {
1093   DCHECK(base::IsValidGUID(guid));
1094   sql::Statement s(db_->GetUniqueStatement(
1095       "SELECT guid, name_on_card, expiration_month, expiration_year, "
1096              "card_number_encrypted, use_count, use_date, date_modified, "
1097              "origin "
1098       "FROM credit_cards "
1099       "WHERE guid = ?"));
1100   s.BindString(0, guid);
1101
1102   if (!s.Step())
1103     return false;
1104
1105   *credit_card = CreditCardFromStatement(s).release();
1106   return true;
1107 }
1108
1109 bool AutofillTable::GetCreditCards(
1110     std::vector<CreditCard*>* credit_cards) {
1111   DCHECK(credit_cards);
1112   credit_cards->clear();
1113
1114   sql::Statement s(db_->GetUniqueStatement(
1115       "SELECT guid "
1116       "FROM credit_cards "
1117       "ORDER BY date_modified DESC, guid"));
1118
1119   while (s.Step()) {
1120     std::string guid = s.ColumnString(0);
1121     CreditCard* credit_card = NULL;
1122     if (!GetCreditCard(guid, &credit_card))
1123       return false;
1124     credit_cards->push_back(credit_card);
1125   }
1126
1127   return s.Succeeded();
1128 }
1129
1130 bool AutofillTable::GetServerCreditCards(
1131     std::vector<CreditCard*>* credit_cards) {
1132   credit_cards->clear();
1133
1134   sql::Statement s(db_->GetUniqueStatement(
1135       "SELECT "
1136       "card_number_encrypted, "  // 0
1137       "last_four,"     // 1
1138       "masked.id,"     // 2
1139       "use_count,"     // 3
1140       "use_date,"      // 4
1141       "type,"          // 5
1142       "status,"        // 6
1143       "name_on_card,"  // 7
1144       "exp_month,"     // 8
1145       "exp_year "      // 9
1146       "FROM masked_credit_cards masked "
1147       "LEFT OUTER JOIN unmasked_credit_cards unmasked "
1148       "ON masked.id = unmasked.id"));
1149   while (s.Step()) {
1150     int index = 0;
1151
1152     // If the card_number_encrypted field is nonempty, we can assume this card
1153     // is a full card, otherwise it's masked.
1154     base::string16 full_card_number = UnencryptedCardFromColumn(s, index++);
1155     base::string16 last_four = s.ColumnString16(index++);
1156     CreditCard::RecordType record_type = full_card_number.empty() ?
1157         CreditCard::MASKED_SERVER_CARD :
1158         CreditCard::FULL_SERVER_CARD;
1159     std::string server_id = s.ColumnString(index++);
1160
1161     CreditCard* card = new CreditCard(record_type, server_id);
1162     card->SetRawInfo(
1163         CREDIT_CARD_NUMBER,
1164         record_type == CreditCard::MASKED_SERVER_CARD ? last_four
1165                                                       : full_card_number);
1166     int64 use_count = s.ColumnInt64(index++);
1167     int64 use_date = s.ColumnInt64(index++);
1168     std::string card_type = s.ColumnString(index++);
1169     if (record_type == CreditCard::MASKED_SERVER_CARD) {
1170       // The type must be set after setting the number to override the
1171       // autodectected type.
1172       card->SetTypeForMaskedCard(card_type.c_str());
1173       DCHECK_EQ(0, use_count);
1174       DCHECK_EQ(0, use_date);
1175     } else {
1176       DCHECK_EQ(CreditCard::GetCreditCardType(full_card_number), card_type);
1177       card->set_use_count(use_count);
1178       card->set_use_date(base::Time::FromInternalValue(use_date));
1179     }
1180
1181     card->SetServerStatus(ServerStatusStringToEnum(s.ColumnString(index++)));
1182     card->SetRawInfo(CREDIT_CARD_NAME, s.ColumnString16(index++));
1183     card->SetRawInfo(CREDIT_CARD_EXP_MONTH, s.ColumnString16(index++));
1184     card->SetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR, s.ColumnString16(index++));
1185     credit_cards->push_back(card);
1186   }
1187
1188   return s.Succeeded();
1189 }
1190
1191 void AutofillTable::SetServerCreditCards(
1192     const std::vector<CreditCard>& credit_cards) {
1193   // Delete all old values.
1194   sql::Statement masked_delete(db_->GetUniqueStatement(
1195       "DELETE FROM masked_credit_cards"));
1196   masked_delete.Run();
1197
1198   // Delete all items in the unmasked table that aren't in the new set.
1199   sql::Statement get_unmasked(db_->GetUniqueStatement(
1200       "SELECT id FROM unmasked_credit_cards"));
1201   while (get_unmasked.Step()) {
1202     // We expect relatively few cards, just do brute-force.
1203     std::string server_id = get_unmasked.ColumnString(0);
1204     bool found_card = false;
1205     for (const CreditCard& cur_card : credit_cards) {
1206       if (cur_card.server_id() == server_id) {
1207         found_card = true;
1208         break;
1209       }
1210     }
1211     if (!found_card) {
1212       // This unmasked card in the DB isn't present in the input. The statement
1213       // is compiled every time because it's much more likely that this is never
1214       // executed than it runs more than once.
1215       sql::Statement unmasked_delete(db_->GetUniqueStatement(
1216           "DELETE FROM unmasked_credit_cards WHERE id = ?"));
1217       unmasked_delete.BindString(0, server_id);
1218       unmasked_delete.Run();
1219       DCHECK_EQ(1, db_->GetLastChangeCount());
1220     }
1221   }
1222
1223   sql::Statement masked_insert(db_->GetUniqueStatement(
1224       "INSERT INTO masked_credit_cards("
1225       "id,"            // 0
1226       "type,"          // 1
1227       "status,"        // 2
1228       "name_on_card,"  // 3
1229       "last_four,"     // 4
1230       "exp_month,"     // 4
1231       "exp_year) "     // 5
1232       "VALUES (?,?,?,?,?,?,?)"));
1233   sql::Statement unmasked_insert(db_->GetUniqueStatement(
1234       "INSERT INTO unmasked_credit_cards("
1235       "id,"                     // 0
1236       "card_number_encrypted)"  // 1
1237       "VALUES (?,?)"));
1238   for (const CreditCard& card : credit_cards) {
1239     DCHECK(card.record_type() != CreditCard::LOCAL_CARD);
1240
1241     masked_insert.BindString(0, card.server_id());
1242     masked_insert.BindString(1, card.type());
1243     masked_insert.BindString(2,
1244                              ServerStatusEnumToString(card.GetServerStatus()));
1245     masked_insert.BindString16(3, card.GetRawInfo(CREDIT_CARD_NAME));
1246     masked_insert.BindString16(4, card.LastFourDigits());
1247     masked_insert.BindString16(5, card.GetRawInfo(CREDIT_CARD_EXP_MONTH));
1248     masked_insert.BindString16(6,
1249                                card.GetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR));
1250
1251     masked_insert.Run();
1252     masked_insert.Reset(true);
1253
1254     if (card.record_type() == CreditCard::FULL_SERVER_CARD) {
1255       // Unmasked cards also get an entry in the unmasked table. Note that the
1256       // input card could be MASKED but if we have an UNMASKED entry for that
1257       // card already, it will be preserved.
1258       unmasked_insert.BindString(0, card.server_id());
1259       BindEncryptedCardToColumn(&unmasked_insert, 1,
1260                                 card.GetRawInfo(CREDIT_CARD_NUMBER));
1261       unmasked_insert.Run();
1262       unmasked_insert.Reset(true);
1263     }
1264   }
1265 }
1266
1267 bool AutofillTable::UnmaskServerCreditCard(const std::string& id,
1268                                            const base::string16& full_number) {
1269   // Make sure there aren't duplicates for this card.
1270   MaskServerCreditCard(id);
1271   sql::Statement s(db_->GetUniqueStatement(
1272       "INSERT INTO unmasked_credit_cards(id, card_number_encrypted,"
1273       "            use_count, use_date) "
1274       "VALUES (?,?,?,?)"));
1275   s.BindString(0, id);
1276
1277   std::string encrypted_data;
1278   OSCrypt::EncryptString16(full_number, &encrypted_data);
1279   s.BindBlob(1, encrypted_data.data(),
1280              static_cast<int>(encrypted_data.length()));
1281
1282   // Unmasking counts as a usage, so set the stats accordingly.
1283   s.BindInt64(2, 1);
1284   s.BindInt64(3, base::Time::Now().ToInternalValue());
1285
1286   s.Run();
1287   return db_->GetLastChangeCount() > 0;
1288 }
1289
1290 bool AutofillTable::MaskServerCreditCard(const std::string& id) {
1291   sql::Statement s(db_->GetUniqueStatement(
1292       "DELETE FROM unmasked_credit_cards WHERE id = ?"));
1293   s.BindString(0, id);
1294   s.Run();
1295   return db_->GetLastChangeCount() > 0;
1296 }
1297
1298 bool AutofillTable::UpdateUnmaskedCardUsageStats(
1299     const CreditCard& credit_card) {
1300   DCHECK_EQ(CreditCard::FULL_SERVER_CARD, credit_card.record_type());
1301
1302   sql::Statement s(db_->GetUniqueStatement(
1303       "UPDATE unmasked_credit_cards "
1304       "SET use_count=?, use_date=? "
1305       "WHERE id=?"));
1306   s.BindInt64(0, credit_card.use_count());
1307   s.BindInt64(1, credit_card.use_date().ToInternalValue());
1308   s.BindString(2, credit_card.server_id());
1309   s.Run();
1310   return db_->GetLastChangeCount() > 0;
1311 }
1312
1313 bool AutofillTable::UpdateCreditCard(const CreditCard& credit_card) {
1314   DCHECK(base::IsValidGUID(credit_card.guid()));
1315
1316   CreditCard* tmp_credit_card = NULL;
1317   if (!GetCreditCard(credit_card.guid(), &tmp_credit_card))
1318     return false;
1319
1320   scoped_ptr<CreditCard> old_credit_card(tmp_credit_card);
1321   bool update_modification_date = *old_credit_card != credit_card;
1322
1323   sql::Statement s(db_->GetUniqueStatement(
1324       "UPDATE credit_cards "
1325       "SET guid=?, name_on_card=?, expiration_month=?,"
1326       "    expiration_year=?, card_number_encrypted=?, use_count=?, use_date=?,"
1327       "    date_modified=?, origin=?"
1328       "WHERE guid=?"));
1329   BindCreditCardToStatement(
1330       credit_card,
1331       update_modification_date ? base::Time::Now() :
1332                                  old_credit_card->modification_date(),
1333       &s);
1334   s.BindString(9, credit_card.guid());
1335
1336   bool result = s.Run();
1337   DCHECK_GT(db_->GetLastChangeCount(), 0);
1338   return result;
1339 }
1340
1341 bool AutofillTable::RemoveCreditCard(const std::string& guid) {
1342   DCHECK(base::IsValidGUID(guid));
1343   sql::Statement s(db_->GetUniqueStatement(
1344       "DELETE FROM credit_cards WHERE guid = ?"));
1345   s.BindString(0, guid);
1346
1347   return s.Run();
1348 }
1349
1350 bool AutofillTable::RemoveAutofillDataModifiedBetween(
1351     const Time& delete_begin,
1352     const Time& delete_end,
1353     std::vector<std::string>* profile_guids,
1354     std::vector<std::string>* credit_card_guids) {
1355   DCHECK(delete_end.is_null() || delete_begin < delete_end);
1356
1357   time_t delete_begin_t = delete_begin.ToTimeT();
1358   time_t delete_end_t = GetEndTime(delete_end);
1359
1360   // Remember Autofill profiles in the time range.
1361   sql::Statement s_profiles_get(db_->GetUniqueStatement(
1362       "SELECT guid FROM autofill_profiles "
1363       "WHERE date_modified >= ? AND date_modified < ?"));
1364   s_profiles_get.BindInt64(0, delete_begin_t);
1365   s_profiles_get.BindInt64(1, delete_end_t);
1366
1367   profile_guids->clear();
1368   while (s_profiles_get.Step()) {
1369     std::string guid = s_profiles_get.ColumnString(0);
1370     profile_guids->push_back(guid);
1371   }
1372   if (!s_profiles_get.Succeeded())
1373     return false;
1374
1375   // Remove Autofill profiles in the time range.
1376   sql::Statement s_profiles(db_->GetUniqueStatement(
1377       "DELETE FROM autofill_profiles "
1378       "WHERE date_modified >= ? AND date_modified < ?"));
1379   s_profiles.BindInt64(0, delete_begin_t);
1380   s_profiles.BindInt64(1, delete_end_t);
1381
1382   if (!s_profiles.Run())
1383     return false;
1384
1385   // Remember Autofill credit cards in the time range.
1386   sql::Statement s_credit_cards_get(db_->GetUniqueStatement(
1387       "SELECT guid FROM credit_cards "
1388       "WHERE date_modified >= ? AND date_modified < ?"));
1389   s_credit_cards_get.BindInt64(0, delete_begin_t);
1390   s_credit_cards_get.BindInt64(1, delete_end_t);
1391
1392   credit_card_guids->clear();
1393   while (s_credit_cards_get.Step()) {
1394     std::string guid = s_credit_cards_get.ColumnString(0);
1395     credit_card_guids->push_back(guid);
1396   }
1397   if (!s_credit_cards_get.Succeeded())
1398     return false;
1399
1400   // Remove Autofill credit cards in the time range.
1401   sql::Statement s_credit_cards(db_->GetUniqueStatement(
1402       "DELETE FROM credit_cards "
1403       "WHERE date_modified >= ? AND date_modified < ?"));
1404   s_credit_cards.BindInt64(0, delete_begin_t);
1405   s_credit_cards.BindInt64(1, delete_end_t);
1406
1407   return s_credit_cards.Run();
1408 }
1409
1410 bool AutofillTable::RemoveOriginURLsModifiedBetween(
1411     const Time& delete_begin,
1412     const Time& delete_end,
1413     ScopedVector<AutofillProfile>* profiles) {
1414   DCHECK(delete_end.is_null() || delete_begin < delete_end);
1415
1416   time_t delete_begin_t = delete_begin.ToTimeT();
1417   time_t delete_end_t = GetEndTime(delete_end);
1418
1419   // Remember Autofill profiles with URL origins in the time range.
1420   sql::Statement s_profiles_get(db_->GetUniqueStatement(
1421       "SELECT guid, origin FROM autofill_profiles "
1422       "WHERE date_modified >= ? AND date_modified < ?"));
1423   s_profiles_get.BindInt64(0, delete_begin_t);
1424   s_profiles_get.BindInt64(1, delete_end_t);
1425
1426   std::vector<std::string> profile_guids;
1427   while (s_profiles_get.Step()) {
1428     std::string guid = s_profiles_get.ColumnString(0);
1429     std::string origin = s_profiles_get.ColumnString(1);
1430     if (GURL(origin).is_valid())
1431       profile_guids.push_back(guid);
1432   }
1433   if (!s_profiles_get.Succeeded())
1434     return false;
1435
1436   // Clear out the origins for the found Autofill profiles.
1437   for (std::vector<std::string>::const_iterator it = profile_guids.begin();
1438        it != profile_guids.end(); ++it) {
1439     sql::Statement s_profile(db_->GetUniqueStatement(
1440         "UPDATE autofill_profiles SET origin='' WHERE guid=?"));
1441     s_profile.BindString(0, *it);
1442     if (!s_profile.Run())
1443       return false;
1444
1445     AutofillProfile* profile;
1446     if (!GetAutofillProfile(*it, &profile))
1447       return false;
1448
1449     profiles->push_back(profile);
1450   }
1451
1452   // Remember Autofill credit cards with URL origins in the time range.
1453   sql::Statement s_credit_cards_get(db_->GetUniqueStatement(
1454       "SELECT guid, origin FROM credit_cards "
1455       "WHERE date_modified >= ? AND date_modified < ?"));
1456   s_credit_cards_get.BindInt64(0, delete_begin_t);
1457   s_credit_cards_get.BindInt64(1, delete_end_t);
1458
1459   std::vector<std::string> credit_card_guids;
1460   while (s_credit_cards_get.Step()) {
1461     std::string guid = s_credit_cards_get.ColumnString(0);
1462     std::string origin = s_credit_cards_get.ColumnString(1);
1463     if (GURL(origin).is_valid())
1464       credit_card_guids.push_back(guid);
1465   }
1466   if (!s_credit_cards_get.Succeeded())
1467     return false;
1468
1469   // Clear out the origins for the found credit cards.
1470   for (std::vector<std::string>::const_iterator it = credit_card_guids.begin();
1471        it != credit_card_guids.end(); ++it) {
1472     sql::Statement s_credit_card(db_->GetUniqueStatement(
1473         "UPDATE credit_cards SET origin='' WHERE guid=?"));
1474     s_credit_card.BindString(0, *it);
1475     if (!s_credit_card.Run())
1476       return false;
1477   }
1478
1479   return true;
1480 }
1481
1482 bool AutofillTable::GetAutofillProfilesInTrash(
1483     std::vector<std::string>* guids) {
1484   guids->clear();
1485
1486   sql::Statement s(db_->GetUniqueStatement(
1487       "SELECT guid "
1488       "FROM autofill_profiles_trash"));
1489
1490   while (s.Step()) {
1491     std::string guid = s.ColumnString(0);
1492     guids->push_back(guid);
1493   }
1494
1495   return s.Succeeded();
1496 }
1497
1498 bool AutofillTable::EmptyAutofillProfilesTrash() {
1499   sql::Statement s(db_->GetUniqueStatement(
1500       "DELETE FROM autofill_profiles_trash"));
1501
1502   return s.Run();
1503 }
1504
1505
1506 bool AutofillTable::AddAutofillGUIDToTrash(const std::string& guid) {
1507   sql::Statement s(db_->GetUniqueStatement(
1508     "INSERT INTO autofill_profiles_trash"
1509     " (guid) "
1510     "VALUES (?)"));
1511   s.BindString(0, guid);
1512
1513   return s.Run();
1514 }
1515
1516 bool AutofillTable::IsAutofillProfilesTrashEmpty() {
1517   sql::Statement s(db_->GetUniqueStatement(
1518       "SELECT guid "
1519       "FROM autofill_profiles_trash"));
1520
1521   return !s.Step();
1522 }
1523
1524 bool AutofillTable::IsAutofillGUIDInTrash(const std::string& guid) {
1525   sql::Statement s(db_->GetUniqueStatement(
1526       "SELECT guid "
1527       "FROM autofill_profiles_trash "
1528       "WHERE guid = ?"));
1529   s.BindString(0, guid);
1530
1531   return s.Step();
1532 }
1533
1534 bool AutofillTable::InitMainTable() {
1535   if (!db_->DoesTableExist("autofill")) {
1536     if (!db_->Execute("CREATE TABLE autofill ("
1537                       "name VARCHAR, "
1538                       "value VARCHAR, "
1539                       "value_lower VARCHAR, "
1540                       "date_created INTEGER DEFAULT 0, "
1541                       "date_last_used INTEGER DEFAULT 0, "
1542                       "count INTEGER DEFAULT 1, "
1543                       "PRIMARY KEY (name, value))") ||
1544         !db_->Execute("CREATE INDEX autofill_name ON autofill (name)") ||
1545         !db_->Execute("CREATE INDEX autofill_name_value_lower ON "
1546                       "autofill (name, value_lower)")) {
1547        NOTREACHED();
1548        return false;
1549     }
1550   }
1551   return true;
1552 }
1553
1554 bool AutofillTable::InitCreditCardsTable() {
1555   if (!db_->DoesTableExist("credit_cards")) {
1556     if (!db_->Execute("CREATE TABLE credit_cards ( "
1557                       "guid VARCHAR PRIMARY KEY, "
1558                       "name_on_card VARCHAR, "
1559                       "expiration_month INTEGER, "
1560                       "expiration_year INTEGER, "
1561                       "card_number_encrypted BLOB, "
1562                       "date_modified INTEGER NOT NULL DEFAULT 0, "
1563                       "origin VARCHAR DEFAULT '', "
1564                       "use_count INTEGER NOT NULL DEFAULT 0, "
1565                       "use_date INTEGER NOT NULL DEFAULT 0) ")) {
1566       NOTREACHED();
1567       return false;
1568     }
1569   }
1570
1571   return true;
1572 }
1573
1574 bool AutofillTable::InitProfilesTable() {
1575   if (!db_->DoesTableExist("autofill_profiles")) {
1576     if (!db_->Execute("CREATE TABLE autofill_profiles ( "
1577                       "guid VARCHAR PRIMARY KEY, "
1578                       "company_name VARCHAR, "
1579                       "street_address VARCHAR, "
1580                       "dependent_locality VARCHAR, "
1581                       "city VARCHAR, "
1582                       "state VARCHAR, "
1583                       "zipcode VARCHAR, "
1584                       "sorting_code VARCHAR, "
1585                       "country_code VARCHAR, "
1586                       "date_modified INTEGER NOT NULL DEFAULT 0, "
1587                       "origin VARCHAR DEFAULT '', "
1588                       "language_code VARCHAR, "
1589                       "use_count INTEGER NOT NULL DEFAULT 0, "
1590                       "use_date INTEGER NOT NULL DEFAULT 0) ")) {
1591       NOTREACHED();
1592       return false;
1593     }
1594   }
1595   return true;
1596 }
1597
1598 bool AutofillTable::InitProfileNamesTable() {
1599   if (!db_->DoesTableExist("autofill_profile_names")) {
1600     if (!db_->Execute("CREATE TABLE autofill_profile_names ( "
1601                       "guid VARCHAR, "
1602                       "first_name VARCHAR, "
1603                       "middle_name VARCHAR, "
1604                       "last_name VARCHAR, "
1605                       "full_name VARCHAR)")) {
1606       NOTREACHED();
1607       return false;
1608     }
1609   }
1610   return true;
1611 }
1612
1613 bool AutofillTable::InitProfileEmailsTable() {
1614   if (!db_->DoesTableExist("autofill_profile_emails")) {
1615     if (!db_->Execute("CREATE TABLE autofill_profile_emails ( "
1616                       "guid VARCHAR, "
1617                       "email VARCHAR)")) {
1618       NOTREACHED();
1619       return false;
1620     }
1621   }
1622   return true;
1623 }
1624
1625 bool AutofillTable::InitProfilePhonesTable() {
1626   if (!db_->DoesTableExist("autofill_profile_phones")) {
1627     if (!db_->Execute("CREATE TABLE autofill_profile_phones ( "
1628                       "guid VARCHAR, "
1629                       "number VARCHAR)")) {
1630       NOTREACHED();
1631       return false;
1632     }
1633   }
1634   return true;
1635 }
1636
1637 bool AutofillTable::InitProfileTrashTable() {
1638   if (!db_->DoesTableExist("autofill_profiles_trash")) {
1639     if (!db_->Execute("CREATE TABLE autofill_profiles_trash ( "
1640                       "guid VARCHAR)")) {
1641       NOTREACHED();
1642       return false;
1643     }
1644   }
1645   return true;
1646 }
1647
1648 bool AutofillTable::InitMaskedCreditCardsTable() {
1649   if (!db_->DoesTableExist("masked_credit_cards")) {
1650     if (!db_->Execute("CREATE TABLE masked_credit_cards ("
1651                       "id VARCHAR,"
1652                       "status VARCHAR,"
1653                       "name_on_card VARCHAR,"
1654                       "type VARCHAR,"
1655                       "last_four VARCHAR,"
1656                       "exp_month INTEGER DEFAULT 0,"
1657                       "exp_year INTEGER DEFAULT 0)")) {
1658       NOTREACHED();
1659       return false;
1660     }
1661   }
1662   return true;
1663 }
1664
1665 bool AutofillTable::InitUnmaskedCreditCardsTable() {
1666   if (!db_->DoesTableExist("unmasked_credit_cards")) {
1667     if (!db_->Execute("CREATE TABLE unmasked_credit_cards ("
1668                       "id VARCHAR,"
1669                       "card_number_encrypted VARCHAR, "
1670                       "use_count INTEGER NOT NULL DEFAULT 0, "
1671                       "use_date INTEGER NOT NULL DEFAULT 0)")) {
1672       NOTREACHED();
1673       return false;
1674     }
1675   }
1676   return true;
1677 }
1678
1679 bool AutofillTable::InitServerAddressesTable() {
1680   if (!db_->DoesTableExist("server_addresses")) {
1681     // The space after language_code is necessary to match what sqlite does
1682     // when it appends the column in migration.
1683     if (!db_->Execute("CREATE TABLE server_addresses ("
1684                       "id VARCHAR,"
1685                       "company_name VARCHAR,"
1686                       "street_address VARCHAR,"
1687                       "address_1 VARCHAR,"
1688                       "address_2 VARCHAR,"
1689                       "address_3 VARCHAR,"
1690                       "address_4 VARCHAR,"
1691                       "postal_code VARCHAR,"
1692                       "sorting_code VARCHAR,"
1693                       "country_code VARCHAR,"
1694                       "language_code VARCHAR, "  // Space required.
1695                       "recipient_name VARCHAR)")) {
1696       NOTREACHED();
1697       return false;
1698     }
1699   }
1700   return true;
1701 }
1702
1703 bool AutofillTable::MigrateToVersion54AddI18nFieldsAndRemoveDeprecatedFields() {
1704   sql::Transaction transaction(db_);
1705   if (!transaction.Begin())
1706     return false;
1707
1708   // Test the existence of the |address_line_1| column as an indication that a
1709   // migration is needed.  It is possible that the new |autofill_profile_phones|
1710   // schema is in place because the table was newly created when migrating from
1711   // a pre-version-23 database.
1712   if (db_->DoesColumnExist("autofill_profiles", "address_line_1")) {
1713     // Create a temporary copy of the autofill_profiles table in the (newer)
1714     // version 54 format.  This table
1715     //   (a) adds columns for street_address, dependent_locality, and
1716     //       sorting_code,
1717     //   (b) removes the address_line_1 and address_line_2 columns, which are
1718     //       replaced by the street_address column, and
1719     //   (c) removes the country column, which was long deprecated.
1720     if (db_->DoesTableExist("autofill_profiles_temp") ||
1721         !db_->Execute("CREATE TABLE autofill_profiles_temp ( "
1722                       "guid VARCHAR PRIMARY KEY, "
1723                       "company_name VARCHAR, "
1724                       "street_address VARCHAR, "
1725                       "dependent_locality VARCHAR, "
1726                       "city VARCHAR, "
1727                       "state VARCHAR, "
1728                       "zipcode VARCHAR, "
1729                       "sorting_code VARCHAR, "
1730                       "country_code VARCHAR, "
1731                       "date_modified INTEGER NOT NULL DEFAULT 0, "
1732                       "origin VARCHAR DEFAULT '')")) {
1733       return false;
1734     }
1735
1736     // Copy over the data from the autofill_profiles table, taking care to merge
1737     // the address lines 1 and 2 into the new street_address column.
1738     if (!db_->Execute("INSERT INTO autofill_profiles_temp "
1739                       "SELECT guid, company_name, '', '', city, state, zipcode,"
1740                       " '', country_code, date_modified, origin "
1741                       "FROM autofill_profiles")) {
1742       return false;
1743     }
1744     sql::Statement s(db_->GetUniqueStatement(
1745         "SELECT guid, address_line_1, address_line_2 FROM autofill_profiles"));
1746     while (s.Step()) {
1747       std::string guid = s.ColumnString(0);
1748       base::string16 line1 = s.ColumnString16(1);
1749       base::string16 line2 = s.ColumnString16(2);
1750       base::string16 street_address = line1;
1751       if (!line2.empty())
1752         street_address += base::ASCIIToUTF16("\n") + line2;
1753
1754       sql::Statement s_update(db_->GetUniqueStatement(
1755           "UPDATE autofill_profiles_temp SET street_address=? WHERE guid=?"));
1756       s_update.BindString16(0, street_address);
1757       s_update.BindString(1, guid);
1758       if (!s_update.Run())
1759         return false;
1760     }
1761     if (!s.Succeeded())
1762       return false;
1763
1764     // Delete the existing (version 53) table and replace it with the contents
1765     // of the temporary table.
1766     if (!db_->Execute("DROP TABLE autofill_profiles") ||
1767         !db_->Execute("ALTER TABLE autofill_profiles_temp "
1768                       "RENAME TO autofill_profiles")) {
1769       return false;
1770     }
1771   }
1772
1773   // Test the existence of the |type| column as an indication that a migration
1774   // is needed.  It is possible that the new |autofill_profile_phones| schema is
1775   // in place because the table was newly created when migrating from a
1776   // pre-version-23 database.
1777   if (db_->DoesColumnExist("autofill_profile_phones", "type")) {
1778     // Create a temporary copy of the autofill_profile_phones table in the
1779     // (newer) version 54 format.  This table removes the deprecated |type|
1780     // column.
1781     if (db_->DoesTableExist("autofill_profile_phones_temp") ||
1782         !db_->Execute("CREATE TABLE autofill_profile_phones_temp ( "
1783                       "guid VARCHAR, "
1784                       "number VARCHAR)")) {
1785       return false;
1786     }
1787
1788     // Copy over the data from the autofill_profile_phones table.
1789     if (!db_->Execute("INSERT INTO autofill_profile_phones_temp "
1790                       "SELECT guid, number FROM autofill_profile_phones")) {
1791       return false;
1792     }
1793
1794     // Delete the existing (version 53) table and replace it with the contents
1795     // of the temporary table.
1796     if (!db_->Execute("DROP TABLE autofill_profile_phones"))
1797       return false;
1798     if (!db_->Execute("ALTER TABLE autofill_profile_phones_temp "
1799                       "RENAME TO autofill_profile_phones")) {
1800       return false;
1801     }
1802   }
1803
1804   return transaction.Commit();
1805 }
1806
1807 bool AutofillTable::MigrateToVersion55MergeAutofillDatesTable() {
1808   sql::Transaction transaction(db_);
1809   if (!transaction.Begin())
1810     return false;
1811
1812   if (db_->DoesTableExist("autofill_temp") ||
1813       !db_->Execute("CREATE TABLE autofill_temp ("
1814                     "name VARCHAR, "
1815                     "value VARCHAR, "
1816                     "value_lower VARCHAR, "
1817                     "date_created INTEGER DEFAULT 0, "
1818                     "date_last_used INTEGER DEFAULT 0, "
1819                     "count INTEGER DEFAULT 1, "
1820                     "PRIMARY KEY (name, value))")) {
1821     return false;
1822   }
1823
1824   // Slurp up the data from the existing table and write it to the new table.
1825   sql::Statement s(db_->GetUniqueStatement(
1826       "SELECT name, value, value_lower, count, MIN(date_created),"
1827       " MAX(date_created) "
1828       "FROM autofill a JOIN autofill_dates ad ON a.pair_id=ad.pair_id "
1829       "GROUP BY name, value, value_lower, count"));
1830   while (s.Step()) {
1831     sql::Statement s_insert(db_->GetUniqueStatement(
1832         "INSERT INTO autofill_temp "
1833         "(name, value, value_lower, count, date_created, date_last_used) "
1834         "VALUES (?, ?, ?, ?, ?, ?)"));
1835     s_insert.BindString16(0, s.ColumnString16(0));
1836     s_insert.BindString16(1, s.ColumnString16(1));
1837     s_insert.BindString16(2, s.ColumnString16(2));
1838     s_insert.BindInt(3, s.ColumnInt(3));
1839     s_insert.BindInt64(4, s.ColumnInt64(4));
1840     s_insert.BindInt64(5, s.ColumnInt64(5));
1841     if (!s_insert.Run())
1842       return false;
1843   }
1844
1845   if (!s.Succeeded())
1846     return false;
1847
1848   // Delete the existing (version 54) tables and replace them with the contents
1849   // of the temporary table.
1850   if (!db_->Execute("DROP TABLE autofill") ||
1851       !db_->Execute("DROP TABLE autofill_dates") ||
1852       !db_->Execute("ALTER TABLE autofill_temp "
1853                     "RENAME TO autofill")) {
1854     return false;
1855   }
1856
1857   // Create indices on the new table, for fast lookups.
1858   if (!db_->Execute("CREATE INDEX autofill_name ON autofill (name)") ||
1859       !db_->Execute("CREATE INDEX autofill_name_value_lower ON "
1860                     "autofill (name, value_lower)")) {
1861     return false;
1862   }
1863
1864
1865   return transaction.Commit();
1866 }
1867
1868 bool AutofillTable::MigrateToVersion56AddProfileLanguageCodeForFormatting() {
1869   return db_->Execute("ALTER TABLE autofill_profiles "
1870                       "ADD COLUMN language_code VARCHAR");
1871 }
1872
1873 bool AutofillTable::MigrateToVersion57AddFullNameField() {
1874   return db_->Execute("ALTER TABLE autofill_profile_names "
1875                       "ADD COLUMN full_name VARCHAR");
1876 }
1877
1878 bool AutofillTable::MigrateToVersion60AddServerCards() {
1879   sql::Transaction transaction(db_);
1880   if (!transaction.Begin())
1881     return false;
1882
1883   if (!db_->DoesTableExist("masked_credit_cards") &&
1884       !db_->Execute("CREATE TABLE masked_credit_cards ("
1885                     "id VARCHAR,"
1886                     "status VARCHAR,"
1887                     "name_on_card VARCHAR,"
1888                     "type VARCHAR,"
1889                     "last_four VARCHAR,"
1890                     "exp_month INTEGER DEFAULT 0,"
1891                     "exp_year INTEGER DEFAULT 0)")) {
1892     return false;
1893   }
1894
1895   if (!db_->DoesTableExist("unmasked_credit_cards") &&
1896       !db_->Execute("CREATE TABLE unmasked_credit_cards ("
1897                     "id VARCHAR,"
1898                     "card_number_encrypted VARCHAR)")) {
1899     return false;
1900   }
1901
1902   if (!db_->DoesTableExist("server_addresses") &&
1903       !db_->Execute("CREATE TABLE server_addresses ("
1904                     "id VARCHAR,"
1905                     "company_name VARCHAR,"
1906                     "street_address VARCHAR,"
1907                     "address_1 VARCHAR,"
1908                     "address_2 VARCHAR,"
1909                     "address_3 VARCHAR,"
1910                     "address_4 VARCHAR,"
1911                     "postal_code VARCHAR,"
1912                     "sorting_code VARCHAR,"
1913                     "country_code VARCHAR,"
1914                     "language_code VARCHAR)")) {
1915     return false;
1916   }
1917
1918   return transaction.Commit();
1919 }
1920
1921 bool AutofillTable::MigrateToVersion61AddUsageStats() {
1922   sql::Transaction transaction(db_);
1923   if (!transaction.Begin())
1924     return false;
1925
1926   // Add use_count to autofill_profiles.
1927   if (!db_->DoesColumnExist("autofill_profiles", "use_count") &&
1928       !db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN "
1929                     "use_count INTEGER NOT NULL DEFAULT 0")) {
1930     return false;
1931   }
1932
1933   // Add use_date to autofill_profiles.
1934   if (!db_->DoesColumnExist("autofill_profiles", "use_date") &&
1935       !db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN "
1936                     "use_date INTEGER NOT NULL DEFAULT 0")) {
1937     return false;
1938   }
1939
1940   // Add use_count to credit_cards.
1941   if (!db_->DoesColumnExist("credit_cards", "use_count") &&
1942       !db_->Execute("ALTER TABLE credit_cards ADD COLUMN "
1943                     "use_count INTEGER NOT NULL DEFAULT 0")) {
1944     return false;
1945   }
1946
1947   // Add use_date to credit_cards.
1948   if (!db_->DoesColumnExist("credit_cards", "use_date") &&
1949       !db_->Execute("ALTER TABLE credit_cards ADD COLUMN "
1950                     "use_date INTEGER NOT NULL DEFAULT 0")) {
1951     return false;
1952   }
1953
1954   return transaction.Commit();
1955 }
1956
1957 bool AutofillTable::MigrateToVersion62AddUsageStatsForUnmaskedCards() {
1958   sql::Transaction transaction(db_);
1959   if (!transaction.Begin())
1960     return false;
1961
1962   // Add use_count to unmasked_credit_cards.
1963   if (!db_->DoesColumnExist("unmasked_credit_cards", "use_count") &&
1964       !db_->Execute("ALTER TABLE unmasked_credit_cards ADD COLUMN "
1965                     "use_count INTEGER NOT NULL DEFAULT 0")) {
1966     return false;
1967   }
1968
1969   // Add use_date to unmasked_credit_cards.
1970   if (!db_->DoesColumnExist("unmasked_credit_cards", "use_date") &&
1971       !db_->Execute("ALTER TABLE unmasked_credit_cards ADD COLUMN "
1972                     "use_date INTEGER NOT NULL DEFAULT 0")) {
1973     return false;
1974   }
1975
1976   return transaction.Commit();
1977 }
1978
1979 bool AutofillTable::MigrateToVersion63AddServerRecipientName() {
1980   if (!db_->DoesColumnExist("server_addresses", "recipient_name") &&
1981       !db_->Execute("ALTER TABLE server_addresses ADD COLUMN "
1982                     "recipient_name VARCHAR")) {
1983     return false;
1984   }
1985   return true;
1986 }
1987
1988 }  // namespace autofill