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