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