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