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