autofill_table.cc revision 7dbb3d5cf0c15f500944d211057644d6a2f37371 
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/time/time.h"
19#include "base/tuple.h"
20#include "components/autofill/core/browser/autofill_country.h"
21#include "components/autofill/core/browser/autofill_profile.h"
22#include "components/autofill/core/browser/autofill_type.h"
23#include "components/autofill/core/browser/credit_card.h"
24#include "components/autofill/core/browser/personal_data_manager.h"
25#include "components/autofill/core/browser/webdata/autofill_change.h"
26#include "components/autofill/core/browser/webdata/autofill_entry.h"
27#include "components/autofill/core/common/form_field_data.h"
28#include "components/webdata/common/web_database.h"
29#include "components/webdata/encryptor/encryptor.h"
30#include "sql/statement.h"
31#include "sql/transaction.h"
32#include "ui/base/l10n/l10n_util.h"
33#include "url/gurl.h"
34
35using base::Time;
36
37namespace autofill {
38namespace {
39
40typedef std::vector<Tuple3<int64, base::string16, base::string16> >
41    AutofillElementList;
42
43// TODO(dhollowa): Find a common place for this.  It is duplicated in
44// personal_data_manager.cc.
45template<typename T>
46T* address_of(T& v) {
47  return &v;
48}
49
50base::string16 LimitDataSize(const base::string16& data) {
51  if (data.size() > AutofillTable::kMaxDataLength)
52    return data.substr(0, AutofillTable::kMaxDataLength);
53
54  return data;
55}
56
57void BindAutofillProfileToStatement(const AutofillProfile& profile,
58                                    sql::Statement* s,
59                                    const std::string& app_locale) {
60  DCHECK(base::IsValidGUID(profile.guid()));
61  s->BindString(0, profile.guid());
62
63  base::string16 text = profile.GetRawInfo(COMPANY_NAME);
64  s->BindString16(1, LimitDataSize(text));
65  text = profile.GetRawInfo(ADDRESS_HOME_LINE1);
66  s->BindString16(2, LimitDataSize(text));
67  text = profile.GetRawInfo(ADDRESS_HOME_LINE2);
68  s->BindString16(3, LimitDataSize(text));
69  text = profile.GetRawInfo(ADDRESS_HOME_CITY);
70  s->BindString16(4, LimitDataSize(text));
71  text = profile.GetRawInfo(ADDRESS_HOME_STATE);
72  s->BindString16(5, LimitDataSize(text));
73  text = profile.GetRawInfo(ADDRESS_HOME_ZIP);
74  s->BindString16(6, LimitDataSize(text));
75  text = profile.GetInfo(ADDRESS_HOME_COUNTRY, app_locale);
76  s->BindString16(7, LimitDataSize(text));
77  text = profile.GetRawInfo(ADDRESS_HOME_COUNTRY);
78  s->BindString16(8, LimitDataSize(text));
79  s->BindInt64(9, Time::Now().ToTimeT());
80  s->BindString(10, profile.origin());
81}
82
83AutofillProfile* AutofillProfileFromStatement(const sql::Statement& s,
84                                              const std::string& app_locale) {
85  AutofillProfile* profile = new AutofillProfile;
86  profile->set_guid(s.ColumnString(0));
87  DCHECK(base::IsValidGUID(profile->guid()));
88
89  profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(1));
90  profile->SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(2));
91  profile->SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(3));
92  profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(4));
93  profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(5));
94  profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(6));
95  // Intentionally skip column 7, which stores the localized country name.
96  profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(8));
97  // Intentionally skip column 9, which stores the profile's modification date.
98  profile->set_origin(s.ColumnString(10));
99
100  return profile;
101}
102
103void BindCreditCardToStatement(const CreditCard& credit_card,
104                               sql::Statement* s) {
105  DCHECK(base::IsValidGUID(credit_card.guid()));
106  s->BindString(0, credit_card.guid());
107
108  base::string16 text = credit_card.GetRawInfo(CREDIT_CARD_NAME);
109  s->BindString16(1, LimitDataSize(text));
110  text = credit_card.GetRawInfo(CREDIT_CARD_EXP_MONTH);
111  s->BindString16(2, LimitDataSize(text));
112  text = credit_card.GetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR);
113  s->BindString16(3, LimitDataSize(text));
114  text = credit_card.GetRawInfo(CREDIT_CARD_NUMBER);
115  std::string encrypted_data;
116  Encryptor::EncryptString16(text, &encrypted_data);
117  s->BindBlob(4, encrypted_data.data(),
118              static_cast<int>(encrypted_data.length()));
119  s->BindInt64(5, Time::Now().ToTimeT());
120  s->BindString(6, credit_card.origin());
121}
122
123CreditCard* CreditCardFromStatement(const sql::Statement& s) {
124  CreditCard* credit_card = new CreditCard;
125
126  credit_card->set_guid(s.ColumnString(0));
127  DCHECK(base::IsValidGUID(credit_card->guid()));
128
129  credit_card->SetRawInfo(CREDIT_CARD_NAME, s.ColumnString16(1));
130  credit_card->SetRawInfo(CREDIT_CARD_EXP_MONTH, s.ColumnString16(2));
131  credit_card->SetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR, s.ColumnString16(3));
132  int encrypted_number_len = s.ColumnByteLength(4);
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(4), encrypted_number_len);
138    Encryptor::DecryptString16(encrypted_number, &credit_card_number);
139  }
140  credit_card->SetRawInfo(CREDIT_CARD_NUMBER, credit_card_number);
141  // Intentionally skip column 5, which stores the modification date.
142  credit_card->set_origin(s.ColumnString(6));
143
144  return credit_card;
145}
146
147bool AddAutofillProfileNamesToProfile(sql::Connection* db,
148                                      AutofillProfile* profile) {
149  sql::Statement s(db->GetUniqueStatement(
150      "SELECT guid, first_name, middle_name, last_name "
151      "FROM autofill_profile_names "
152      "WHERE guid=?"));
153  s.BindString(0, profile->guid());
154
155  if (!s.is_valid())
156    return false;
157
158  std::vector<base::string16> first_names;
159  std::vector<base::string16> middle_names;
160  std::vector<base::string16> last_names;
161  while (s.Step()) {
162    DCHECK_EQ(profile->guid(), s.ColumnString(0));
163    first_names.push_back(s.ColumnString16(1));
164    middle_names.push_back(s.ColumnString16(2));
165    last_names.push_back(s.ColumnString16(3));
166  }
167  if (!s.Succeeded())
168    return false;
169
170  profile->SetRawMultiInfo(NAME_FIRST, first_names);
171  profile->SetRawMultiInfo(NAME_MIDDLE, middle_names);
172  profile->SetRawMultiInfo(NAME_LAST, last_names);
173  return true;
174}
175
176bool AddAutofillProfileEmailsToProfile(sql::Connection* db,
177                                       AutofillProfile* profile) {
178  sql::Statement s(db->GetUniqueStatement(
179      "SELECT guid, email "
180      "FROM autofill_profile_emails "
181      "WHERE guid=?"));
182  s.BindString(0, profile->guid());
183
184  if (!s.is_valid())
185    return false;
186
187  std::vector<base::string16> emails;
188  while (s.Step()) {
189    DCHECK_EQ(profile->guid(), s.ColumnString(0));
190    emails.push_back(s.ColumnString16(1));
191  }
192  if (!s.Succeeded())
193    return false;
194
195  profile->SetRawMultiInfo(EMAIL_ADDRESS, emails);
196  return true;
197}
198
199bool AddAutofillProfilePhonesToProfile(sql::Connection* db,
200                                       AutofillProfile* profile) {
201  sql::Statement s(db->GetUniqueStatement(
202      "SELECT guid, type, number "
203      "FROM autofill_profile_phones "
204      "WHERE guid=? AND type=?"));
205
206  // Value used to be either [(0, phone), (1, fax)] but fax has been removed.
207  s.BindString(0, profile->guid());
208  s.BindInt(1, 0);
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(2));
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, type, number) "
284      "VALUES (?,?,?)"));
285    s.BindString(0, profile.guid());
286    // Value used to be either [(0, phone), (1, fax)] but fax has been removed.
287    s.BindInt(1, 0);
288    s.BindString16(2, numbers[i]);
289
290    if (!s.Run())
291      return false;
292  }
293
294  return true;
295}
296
297bool AddAutofillProfilePieces(const AutofillProfile& profile,
298                              sql::Connection* db) {
299  if (!AddAutofillProfileNames(profile, db))
300    return false;
301
302  if (!AddAutofillProfileEmails(profile, db))
303    return false;
304
305  if (!AddAutofillProfilePhones(profile, db))
306    return false;
307
308  return true;
309}
310
311bool RemoveAutofillProfilePieces(const std::string& guid, sql::Connection* db) {
312  sql::Statement s1(db->GetUniqueStatement(
313      "DELETE FROM autofill_profile_names WHERE guid = ?"));
314  s1.BindString(0, guid);
315
316  if (!s1.Run())
317    return false;
318
319  sql::Statement s2(db->GetUniqueStatement(
320      "DELETE FROM autofill_profile_emails WHERE guid = ?"));
321  s2.BindString(0, guid);
322
323  if (!s2.Run())
324    return false;
325
326  sql::Statement s3(db->GetUniqueStatement(
327      "DELETE FROM autofill_profile_phones WHERE guid = ?"));
328  s3.BindString(0, guid);
329
330  return s3.Run();
331}
332
333WebDatabaseTable::TypeKey GetKey() {
334  // We just need a unique constant. Use the address of a static that
335  // COMDAT folding won't touch in an optimizing linker.
336  static int table_key = 0;
337  return reinterpret_cast<void*>(&table_key);
338}
339
340time_t GetEndTime(const base::Time& end) {
341  if (end.is_null() || end == base::Time::Max())
342    return std::numeric_limits<time_t>::max();
343
344  return end.ToTimeT();
345}
346
347}  // namespace
348
349// The maximum length allowed for form data.
350const size_t AutofillTable::kMaxDataLength = 1024;
351
352AutofillTable::AutofillTable(const std::string& app_locale)
353    : app_locale_(app_locale) {
354}
355
356AutofillTable::~AutofillTable() {
357}
358
359AutofillTable* AutofillTable::FromWebDatabase(WebDatabase* db) {
360  return static_cast<AutofillTable*>(db->GetTable(GetKey()));
361}
362
363WebDatabaseTable::TypeKey AutofillTable::GetTypeKey() const {
364  return GetKey();
365}
366
367bool AutofillTable::Init(sql::Connection* db, sql::MetaTable* meta_table) {
368  WebDatabaseTable::Init(db, meta_table);
369  return (InitMainTable() && InitCreditCardsTable() && InitDatesTable() &&
370          InitProfilesTable() && InitProfileNamesTable() &&
371          InitProfileEmailsTable() && InitProfilePhonesTable() &&
372          InitProfileTrashTable());
373}
374
375bool AutofillTable::IsSyncable() {
376  return true;
377}
378
379bool AutofillTable::MigrateToVersion(int version,
380                                     bool* update_compatible_version) {
381  // Migrate if necessary.
382  switch (version) {
383    case 22:
384      return ClearAutofillEmptyValueElements();
385    case 23:
386      return MigrateToVersion23AddCardNumberEncryptedColumn();
387    case 24:
388      return MigrateToVersion24CleanupOversizedStringFields();
389    case 27:
390      *update_compatible_version = true;
391      return MigrateToVersion27UpdateLegacyCreditCards();
392    case 30:
393      *update_compatible_version = true;
394      return MigrateToVersion30AddDateModifed();
395    case 31:
396      *update_compatible_version = true;
397      return MigrateToVersion31AddGUIDToCreditCardsAndProfiles();
398    case 32:
399      *update_compatible_version = true;
400      return MigrateToVersion32UpdateProfilesAndCreditCards();
401    case 33:
402      *update_compatible_version = true;
403      return MigrateToVersion33ProfilesBasedOnFirstName();
404    case 34:
405      *update_compatible_version = true;
406      return MigrateToVersion34ProfilesBasedOnCountryCode();
407    case 35:
408      *update_compatible_version = true;
409      return MigrateToVersion35GreatBritainCountryCodes();
410    // Combine migrations 36 and 37.  This is due to enhancements to the merge
411    // step when migrating profiles.  The original migration from 35 to 36 did
412    // not merge profiles with identical addresses, but the migration from 36 to
413    // 37 does.  The step from 35 to 36 should only happen on the Chrome 12 dev
414    // channel.  Chrome 12 beta and release users will jump from 35 to 37
415    // directly getting the full benefits of the multi-valued merge as well as
416    // the culling of bad data.
417    case 37:
418      *update_compatible_version = true;
419      return MigrateToVersion37MergeAndCullOlderProfiles();
420    case 51:
421      // Combine migrations 50 and 51.  The migration code from version 49 to 50
422      // worked correctly for users with existing 'origin' columns, but failed
423      // to create these columns for new users.
424      return MigrateToVersion51AddOriginColumn();
425  }
426  return true;
427}
428
429bool AutofillTable::AddFormFieldValues(
430    const std::vector<FormFieldData>& elements,
431    std::vector<AutofillChange>* changes) {
432  return AddFormFieldValuesTime(elements, changes, Time::Now());
433}
434
435bool AutofillTable::AddFormFieldValue(const FormFieldData& element,
436                                      std::vector<AutofillChange>* changes) {
437  return AddFormFieldValueTime(element, changes, Time::Now());
438}
439
440bool AutofillTable::GetFormValuesForElementName(
441    const base::string16& name,
442    const base::string16& prefix,
443    std::vector<base::string16>* values,
444    int limit) {
445  DCHECK(values);
446  sql::Statement s;
447
448  if (prefix.empty()) {
449    s.Assign(db_->GetUniqueStatement(
450        "SELECT value FROM autofill "
451        "WHERE name = ? "
452        "ORDER BY count DESC "
453        "LIMIT ?"));
454    s.BindString16(0, name);
455    s.BindInt(1, limit);
456  } else {
457    base::string16 prefix_lower = base::i18n::ToLower(prefix);
458    base::string16 next_prefix = prefix_lower;
459    next_prefix[next_prefix.length() - 1]++;
460
461    s.Assign(db_->GetUniqueStatement(
462        "SELECT value FROM autofill "
463        "WHERE name = ? AND "
464        "value_lower >= ? AND "
465        "value_lower < ? "
466        "ORDER BY count DESC "
467        "LIMIT ?"));
468    s.BindString16(0, name);
469    s.BindString16(1, prefix_lower);
470    s.BindString16(2, next_prefix);
471    s.BindInt(3, limit);
472  }
473
474  values->clear();
475  while (s.Step())
476    values->push_back(s.ColumnString16(0));
477  return s.Succeeded();
478}
479
480bool AutofillTable::HasFormElements() {
481  sql::Statement s(db_->GetUniqueStatement(
482      "SELECT COUNT(*) FROM autofill"));
483  if (!s.Step()) {
484    NOTREACHED();
485    return false;
486  }
487  return s.ColumnInt(0) > 0;
488}
489
490bool AutofillTable::RemoveFormElementsAddedBetween(
491    const Time& delete_begin,
492    const Time& delete_end,
493    std::vector<AutofillChange>* changes) {
494  DCHECK(changes);
495  // Query for the pair_id, name, and value of all form elements that
496  // were used between the given times.
497  sql::Statement s(db_->GetUniqueStatement(
498      "SELECT DISTINCT a.pair_id, a.name, a.value "
499      "FROM autofill_dates ad JOIN autofill a ON ad.pair_id = a.pair_id "
500      "WHERE ad.date_created >= ? AND ad.date_created < ?"));
501  s.BindInt64(0, delete_begin.ToTimeT());
502  s.BindInt64(1,
503              (delete_end.is_null() || delete_end == base::Time::Max()) ?
504                  std::numeric_limits<int64>::max() :
505                  delete_end.ToTimeT());
506
507  AutofillElementList elements;
508  while (s.Step()) {
509    elements.push_back(MakeTuple(s.ColumnInt64(0),
510                                 s.ColumnString16(1),
511                                 s.ColumnString16(2)));
512  }
513  if (!s.Succeeded())
514    return false;
515
516  for (AutofillElementList::iterator itr = elements.begin();
517       itr != elements.end(); ++itr) {
518    int how_many = 0;
519    if (!RemoveFormElementForTimeRange(itr->a, delete_begin, delete_end,
520                                       &how_many)) {
521      return false;
522    }
523    // We store at most 2 time stamps. If we remove both of them we should
524    // delete the corresponding data. If we delete only one it could still be
525    // the last timestamp for the data, so check how many timestamps do remain.
526    bool should_remove = (CountTimestampsData(itr->a) == 0);
527    if (should_remove) {
528      if (!RemoveFormElementForID(itr->a))
529        return false;
530    } else {
531      if (!AddToCountOfFormElement(itr->a, -how_many))
532        return false;
533    }
534    AutofillChange::Type change_type =
535        should_remove ? AutofillChange::REMOVE : AutofillChange::UPDATE;
536    changes->push_back(AutofillChange(change_type,
537                                      AutofillKey(itr->b, itr->c)));
538  }
539
540  return true;
541}
542
543bool AutofillTable::RemoveExpiredFormElements(
544    std::vector<AutofillChange>* changes) {
545  DCHECK(changes);
546
547  base::Time delete_end = AutofillEntry::ExpirationTime();
548  // Query for the pair_id, name, and value of all form elements that
549  // were last used before the |delete_end|.
550  sql::Statement select_for_delete(db_->GetUniqueStatement(
551      "SELECT DISTINCT pair_id, name, value "
552      "FROM autofill WHERE pair_id NOT IN "
553      "(SELECT DISTINCT pair_id "
554      "FROM autofill_dates WHERE date_created >= ?)"));
555  select_for_delete.BindInt64(0, delete_end.ToTimeT());
556  AutofillElementList entries_to_delete;
557  while (select_for_delete.Step()) {
558    entries_to_delete.push_back(MakeTuple(select_for_delete.ColumnInt64(0),
559                                          select_for_delete.ColumnString16(1),
560                                          select_for_delete.ColumnString16(2)));
561  }
562
563  if (!select_for_delete.Succeeded())
564    return false;
565
566  sql::Statement delete_data_statement(db_->GetUniqueStatement(
567      "DELETE FROM autofill WHERE pair_id NOT IN ("
568      "SELECT pair_id FROM autofill_dates WHERE date_created >= ?)"));
569  delete_data_statement.BindInt64(0, delete_end.ToTimeT());
570  if (!delete_data_statement.Run())
571    return false;
572
573  sql::Statement delete_times_statement(db_->GetUniqueStatement(
574      "DELETE FROM autofill_dates WHERE pair_id NOT IN ("
575      "SELECT pair_id FROM autofill_dates WHERE date_created >= ?)"));
576  delete_times_statement.BindInt64(0, delete_end.ToTimeT());
577  if (!delete_times_statement.Run())
578    return false;
579
580  // Cull remaining entries' timestamps.
581  std::vector<AutofillEntry> entries;
582  if (!GetAllAutofillEntries(&entries))
583    return false;
584  sql::Statement cull_date_entry(db_->GetUniqueStatement(
585      "DELETE FROM autofill_dates "
586      "WHERE pair_id == (SELECT pair_id FROM autofill "
587                         "WHERE name = ? and value = ?)"
588      "AND date_created != ? AND date_created != ?"));
589  for (size_t i = 0; i < entries.size(); ++i) {
590    cull_date_entry.BindString16(0, entries[i].key().name());
591    cull_date_entry.BindString16(1, entries[i].key().value());
592    cull_date_entry.BindInt64(2,
593        entries[i].timestamps().empty() ? 0 :
594        entries[i].timestamps().front().ToTimeT());
595    cull_date_entry.BindInt64(3,
596        entries[i].timestamps().empty() ? 0 :
597        entries[i].timestamps().back().ToTimeT());
598    if (!cull_date_entry.Run())
599      return false;
600    cull_date_entry.Reset(true);
601  }
602
603  changes->clear();
604  changes->reserve(entries_to_delete.size());
605
606  for (AutofillElementList::iterator it = entries_to_delete.begin();
607       it != entries_to_delete.end(); ++it) {
608    changes->push_back(AutofillChange(
609        AutofillChange::REMOVE, AutofillKey(it->b, it->c)));
610  }
611  return true;
612}
613
614bool AutofillTable::RemoveFormElementForTimeRange(int64 pair_id,
615                                                  const Time& delete_begin,
616                                                  const Time& delete_end,
617                                                  int* how_many) {
618  sql::Statement s(db_->GetUniqueStatement(
619      "DELETE FROM autofill_dates WHERE pair_id = ? AND "
620      "date_created >= ? AND date_created < ?"));
621  s.BindInt64(0, pair_id);
622  s.BindInt64(1, delete_begin.is_null() ? 0 : delete_begin.ToTimeT());
623  s.BindInt64(2, delete_end.is_null() ? std::numeric_limits<int64>::max() :
624                                        delete_end.ToTimeT());
625
626  bool result = s.Run();
627  if (how_many)
628    *how_many = db_->GetLastChangeCount();
629
630  return result;
631}
632
633int AutofillTable::CountTimestampsData(int64 pair_id) {
634  sql::Statement s(db_->GetUniqueStatement(
635      "SELECT COUNT(*) FROM autofill_dates WHERE pair_id = ?"));
636  s.BindInt64(0, pair_id);
637  if (!s.Step()) {
638    NOTREACHED();
639    return 0;
640  } else {
641    return s.ColumnInt(0);
642  }
643}
644
645bool AutofillTable::AddToCountOfFormElement(int64 pair_id,
646                                            int delta) {
647  int count = 0;
648
649  if (!GetCountOfFormElement(pair_id, &count))
650    return false;
651
652  if (count + delta == 0) {
653    // Should remove the element earlier in the code.
654    NOTREACHED();
655    return false;
656  } else {
657    if (!SetCountOfFormElement(pair_id, count + delta))
658      return false;
659  }
660  return true;
661}
662
663bool AutofillTable::GetIDAndCountOfFormElement(
664    const FormFieldData& element,
665    int64* pair_id,
666    int* count) {
667  DCHECK(pair_id);
668  DCHECK(count);
669
670  sql::Statement s(db_->GetUniqueStatement(
671      "SELECT pair_id, count FROM autofill "
672      "WHERE name = ? AND value = ?"));
673  s.BindString16(0, element.name);
674  s.BindString16(1, element.value);
675
676  if (!s.is_valid())
677    return false;
678
679  *pair_id = 0;
680  *count = 0;
681
682  if (s.Step()) {
683    *pair_id = s.ColumnInt64(0);
684    *count = s.ColumnInt(1);
685  }
686
687  return true;
688}
689
690bool AutofillTable::GetCountOfFormElement(int64 pair_id, int* count) {
691  DCHECK(count);
692  sql::Statement s(db_->GetUniqueStatement(
693      "SELECT count FROM autofill WHERE pair_id = ?"));
694  s.BindInt64(0, pair_id);
695
696  if (s.Step()) {
697    *count = s.ColumnInt(0);
698    return true;
699  }
700  return false;
701}
702
703bool AutofillTable::SetCountOfFormElement(int64 pair_id, int count) {
704  sql::Statement s(db_->GetUniqueStatement(
705      "UPDATE autofill SET count = ? WHERE pair_id = ?"));
706  s.BindInt(0, count);
707  s.BindInt64(1, pair_id);
708
709  return s.Run();
710}
711
712bool AutofillTable::InsertFormElement(const FormFieldData& element,
713                                      int64* pair_id) {
714  DCHECK(pair_id);
715  sql::Statement s(db_->GetUniqueStatement(
716      "INSERT INTO autofill (name, value, value_lower) VALUES (?,?,?)"));
717  s.BindString16(0, element.name);
718  s.BindString16(1, element.value);
719  s.BindString16(2, base::i18n::ToLower(element.value));
720
721  if (!s.Run())
722    return false;
723
724  *pair_id = db_->GetLastInsertRowId();
725  return true;
726}
727
728bool AutofillTable::InsertPairIDAndDate(int64 pair_id,
729                                        const Time& date_created) {
730  sql::Statement s(db_->GetUniqueStatement(
731      "INSERT INTO autofill_dates "
732      "(pair_id, date_created) VALUES (?, ?)"));
733  s.BindInt64(0, pair_id);
734  s.BindInt64(1, date_created.ToTimeT());
735
736  return s.Run();
737}
738
739bool AutofillTable::DeleteLastAccess(int64 pair_id) {
740  // Inner SELECT selects the newest |date_created| for a given |pair_id|.
741  // DELETE deletes only that entry.
742  sql::Statement s(db_->GetUniqueStatement(
743      "DELETE FROM autofill_dates WHERE pair_id = ? and date_created IN "
744      "(SELECT date_created FROM autofill_dates WHERE pair_id = ? "
745      "ORDER BY date_created DESC LIMIT 1)"));
746  s.BindInt64(0, pair_id);
747  s.BindInt64(1, pair_id);
748
749  return s.Run();
750}
751
752bool AutofillTable::AddFormFieldValuesTime(
753    const std::vector<FormFieldData>& elements,
754    std::vector<AutofillChange>* changes,
755    Time time) {
756  // Only add one new entry for each unique element name.  Use |seen_names| to
757  // track this.  Add up to |kMaximumUniqueNames| unique entries per form.
758  const size_t kMaximumUniqueNames = 256;
759  std::set<base::string16> seen_names;
760  bool result = true;
761  for (std::vector<FormFieldData>::const_iterator itr = elements.begin();
762       itr != elements.end(); ++itr) {
763    if (seen_names.size() >= kMaximumUniqueNames)
764      break;
765    if (seen_names.find(itr->name) != seen_names.end())
766      continue;
767    result = result && AddFormFieldValueTime(*itr, changes, time);
768    seen_names.insert(itr->name);
769  }
770  return result;
771}
772
773bool AutofillTable::ClearAutofillEmptyValueElements() {
774  sql::Statement s(db_->GetUniqueStatement(
775      "SELECT pair_id FROM autofill WHERE TRIM(value)= \"\""));
776  if (!s.is_valid())
777    return false;
778
779  std::set<int64> ids;
780  while (s.Step())
781    ids.insert(s.ColumnInt64(0));
782  if (!s.Succeeded())
783    return false;
784
785  bool success = true;
786  for (std::set<int64>::const_iterator iter = ids.begin(); iter != ids.end();
787       ++iter) {
788    if (!RemoveFormElementForID(*iter))
789      success = false;
790  }
791
792  return success;
793}
794
795bool AutofillTable::GetAllAutofillEntries(std::vector<AutofillEntry>* entries) {
796  DCHECK(entries);
797  sql::Statement s(db_->GetUniqueStatement(
798      "SELECT name, value, date_created FROM autofill a JOIN "
799      "autofill_dates ad ON a.pair_id=ad.pair_id"));
800
801  bool first_entry = true;
802  AutofillKey* current_key_ptr = NULL;
803  std::vector<Time>* timestamps_ptr = NULL;
804  base::string16 name, value;
805  Time time;
806  while (s.Step()) {
807    name = s.ColumnString16(0);
808    value = s.ColumnString16(1);
809    time = Time::FromTimeT(s.ColumnInt64(2));
810
811    if (first_entry) {
812      current_key_ptr = new AutofillKey(name, value);
813
814      timestamps_ptr = new std::vector<Time>;
815      timestamps_ptr->push_back(time);
816
817      first_entry = false;
818    } else {
819      // we've encountered the next entry
820      if (current_key_ptr->name().compare(name) != 0 ||
821          current_key_ptr->value().compare(value) != 0) {
822        AutofillEntry entry(*current_key_ptr, *timestamps_ptr);
823        entries->push_back(entry);
824
825        delete current_key_ptr;
826        delete timestamps_ptr;
827
828        current_key_ptr = new AutofillKey(name, value);
829        timestamps_ptr = new std::vector<Time>;
830      }
831      timestamps_ptr->push_back(time);
832    }
833  }
834
835  // If there is at least one result returned, first_entry will be false.
836  // For this case we need to do a final cleanup step.
837  if (!first_entry) {
838    AutofillEntry entry(*current_key_ptr, *timestamps_ptr);
839    entries->push_back(entry);
840    delete current_key_ptr;
841    delete timestamps_ptr;
842  }
843
844  return s.Succeeded();
845}
846
847bool AutofillTable::GetAutofillTimestamps(const base::string16& name,
848                                          const base::string16& value,
849                                          std::vector<Time>* timestamps) {
850  DCHECK(timestamps);
851  sql::Statement s(db_->GetUniqueStatement(
852      "SELECT date_created FROM autofill a JOIN "
853      "autofill_dates ad ON a.pair_id=ad.pair_id "
854      "WHERE a.name = ? AND a.value = ?"));
855  s.BindString16(0, name);
856  s.BindString16(1, value);
857
858  while (s.Step())
859    timestamps->push_back(Time::FromTimeT(s.ColumnInt64(0)));
860
861  return s.Succeeded();
862}
863
864bool AutofillTable::UpdateAutofillEntries(
865    const std::vector<AutofillEntry>& entries) {
866  if (!entries.size())
867    return true;
868
869  // Remove all existing entries.
870  for (size_t i = 0; i < entries.size(); i++) {
871    std::string sql = "SELECT pair_id FROM autofill "
872                      "WHERE name = ? AND value = ?";
873    sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
874    s.BindString16(0, entries[i].key().name());
875    s.BindString16(1, entries[i].key().value());
876
877    if (!s.is_valid())
878      return false;
879
880    if (s.Step()) {
881      if (!RemoveFormElementForID(s.ColumnInt64(0)))
882        return false;
883    }
884  }
885
886  // Insert all the supplied autofill entries.
887  for (size_t i = 0; i < entries.size(); i++) {
888    if (!InsertAutofillEntry(entries[i]))
889      return false;
890  }
891
892  return true;
893}
894
895bool AutofillTable::InsertAutofillEntry(const AutofillEntry& entry) {
896  std::string sql = "INSERT INTO autofill (name, value, value_lower, count) "
897                    "VALUES (?, ?, ?, ?)";
898  sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
899  s.BindString16(0, entry.key().name());
900  s.BindString16(1, entry.key().value());
901  s.BindString16(2, base::i18n::ToLower(entry.key().value()));
902  s.BindInt(3, entry.timestamps().size());
903
904  if (!s.Run())
905    return false;
906
907  int64 pair_id = db_->GetLastInsertRowId();
908  for (size_t i = 0; i < entry.timestamps().size(); i++) {
909    if (!InsertPairIDAndDate(pair_id, entry.timestamps()[i]))
910      return false;
911  }
912
913  return true;
914}
915
916bool AutofillTable::AddFormFieldValueTime(const FormFieldData& element,
917                                          std::vector<AutofillChange>* changes,
918                                          Time time) {
919  int count = 0;
920  int64 pair_id;
921
922  if (!GetIDAndCountOfFormElement(element, &pair_id, &count))
923    return false;
924
925  if (count == 0 && !InsertFormElement(element, &pair_id))
926    return false;
927
928  if (!SetCountOfFormElement(pair_id, count + 1))
929    return false;
930
931  // If we already have more than 2 times delete last one, before adding new
932  // one.
933  if (count >= 2 && !DeleteLastAccess(pair_id))
934    return false;
935
936  if (!InsertPairIDAndDate(pair_id, time))
937    return false;
938
939  AutofillChange::Type change_type =
940      count == 0 ? AutofillChange::ADD : AutofillChange::UPDATE;
941  changes->push_back(
942      AutofillChange(change_type,
943                     AutofillKey(element.name, element.value)));
944  return true;
945}
946
947
948bool AutofillTable::RemoveFormElement(const base::string16& name,
949                                      const base::string16& value) {
950  // Find the id for that pair.
951  sql::Statement s(db_->GetUniqueStatement(
952      "SELECT pair_id FROM autofill WHERE  name = ? AND value= ?"));
953  s.BindString16(0, name);
954  s.BindString16(1, value);
955
956  if (s.Step())
957    return RemoveFormElementForID(s.ColumnInt64(0));
958  return false;
959}
960
961bool AutofillTable::AddAutofillProfile(const AutofillProfile& profile) {
962  if (IsAutofillGUIDInTrash(profile.guid()))
963    return true;
964
965  sql::Statement s(db_->GetUniqueStatement(
966      "INSERT INTO autofill_profiles"
967      "(guid, company_name, address_line_1, address_line_2, city, state,"
968      " zipcode, country, country_code, date_modified, origin)"
969      "VALUES (?,?,?,?,?,?,?,?,?,?,?)"));
970  BindAutofillProfileToStatement(profile, &s, app_locale_);
971
972  if (!s.Run())
973    return false;
974
975  return AddAutofillProfilePieces(profile, db_);
976}
977
978bool AutofillTable::GetAutofillProfile(const std::string& guid,
979                                       AutofillProfile** profile) {
980  DCHECK(base::IsValidGUID(guid));
981  DCHECK(profile);
982  sql::Statement s(db_->GetUniqueStatement(
983      "SELECT guid, company_name, address_line_1, address_line_2, city, state,"
984      " zipcode, country, country_code, date_modified, origin "
985      "FROM autofill_profiles "
986      "WHERE guid=?"));
987  s.BindString(0, guid);
988
989  if (!s.Step())
990    return false;
991
992  scoped_ptr<AutofillProfile> p(AutofillProfileFromStatement(s, app_locale_));
993
994  // Get associated name info.
995  AddAutofillProfileNamesToProfile(db_, p.get());
996
997  // Get associated email info.
998  AddAutofillProfileEmailsToProfile(db_, p.get());
999
1000  // Get associated phone info.
1001  AddAutofillProfilePhonesToProfile(db_, p.get());
1002
1003  *profile = p.release();
1004  return true;
1005}
1006
1007bool AutofillTable::GetAutofillProfiles(
1008    std::vector<AutofillProfile*>* profiles) {
1009  DCHECK(profiles);
1010  profiles->clear();
1011
1012  sql::Statement s(db_->GetUniqueStatement(
1013      "SELECT guid "
1014      "FROM autofill_profiles"));
1015
1016  while (s.Step()) {
1017    std::string guid = s.ColumnString(0);
1018    AutofillProfile* profile = NULL;
1019    if (!GetAutofillProfile(guid, &profile))
1020      return false;
1021    profiles->push_back(profile);
1022  }
1023
1024  return s.Succeeded();
1025}
1026
1027bool AutofillTable::UpdateAutofillProfile(const AutofillProfile& profile) {
1028  DCHECK(base::IsValidGUID(profile.guid()));
1029
1030  // Don't update anything until the trash has been emptied.  There may be
1031  // pending modifications to process.
1032  if (!IsAutofillProfilesTrashEmpty())
1033    return true;
1034
1035  AutofillProfile* tmp_profile = NULL;
1036  if (!GetAutofillProfile(profile.guid(), &tmp_profile))
1037    return false;
1038
1039  // Preserve appropriate modification dates by not updating unchanged profiles.
1040  scoped_ptr<AutofillProfile> old_profile(tmp_profile);
1041  if (old_profile->Compare(profile) == 0 &&
1042      old_profile->origin() == profile.origin())
1043    return true;
1044
1045  sql::Statement s(db_->GetUniqueStatement(
1046      "UPDATE autofill_profiles "
1047      "SET guid=?, company_name=?, address_line_1=?, address_line_2=?, "
1048      "    city=?, state=?, zipcode=?, country=?, country_code=?, "
1049      "    date_modified=?, origin=? "
1050      "WHERE guid=?"));
1051  BindAutofillProfileToStatement(profile, &s, app_locale_);
1052  s.BindString(11, profile.guid());
1053
1054  bool result = s.Run();
1055  DCHECK_GT(db_->GetLastChangeCount(), 0);
1056  if (!result)
1057    return result;
1058
1059  // Remove the old names, emails, and phone numbers.
1060  if (!RemoveAutofillProfilePieces(profile.guid(), db_))
1061    return false;
1062
1063  return AddAutofillProfilePieces(profile, db_);
1064}
1065
1066bool AutofillTable::RemoveAutofillProfile(const std::string& guid) {
1067  DCHECK(base::IsValidGUID(guid));
1068
1069  if (IsAutofillGUIDInTrash(guid)) {
1070    sql::Statement s_trash(db_->GetUniqueStatement(
1071        "DELETE FROM autofill_profiles_trash WHERE guid = ?"));
1072    s_trash.BindString(0, guid);
1073
1074    bool success = s_trash.Run();
1075    DCHECK_GT(db_->GetLastChangeCount(), 0) << "Expected item in trash";
1076    return success;
1077  }
1078
1079  sql::Statement s(db_->GetUniqueStatement(
1080      "DELETE FROM autofill_profiles WHERE guid = ?"));
1081  s.BindString(0, guid);
1082
1083  if (!s.Run())
1084    return false;
1085
1086  return RemoveAutofillProfilePieces(guid, db_);
1087}
1088
1089bool AutofillTable::ClearAutofillProfiles() {
1090  sql::Statement s1(db_->GetUniqueStatement(
1091      "DELETE FROM autofill_profiles"));
1092
1093  if (!s1.Run())
1094    return false;
1095
1096  sql::Statement s2(db_->GetUniqueStatement(
1097      "DELETE FROM autofill_profile_names"));
1098
1099  if (!s2.Run())
1100    return false;
1101
1102  sql::Statement s3(db_->GetUniqueStatement(
1103      "DELETE FROM autofill_profile_emails"));
1104
1105  if (!s3.Run())
1106    return false;
1107
1108  sql::Statement s4(db_->GetUniqueStatement(
1109      "DELETE FROM autofill_profile_phones"));
1110
1111  return s4.Run();
1112}
1113
1114bool AutofillTable::AddCreditCard(const CreditCard& credit_card) {
1115  sql::Statement s(db_->GetUniqueStatement(
1116      "INSERT INTO credit_cards"
1117      "(guid, name_on_card, expiration_month, expiration_year, "
1118      " card_number_encrypted, date_modified, origin)"
1119      "VALUES (?,?,?,?,?,?,?)"));
1120  BindCreditCardToStatement(credit_card, &s);
1121
1122  if (!s.Run())
1123    return false;
1124
1125  DCHECK_GT(db_->GetLastChangeCount(), 0);
1126  return true;
1127}
1128
1129bool AutofillTable::GetCreditCard(const std::string& guid,
1130                                  CreditCard** credit_card) {
1131  DCHECK(base::IsValidGUID(guid));
1132  sql::Statement s(db_->GetUniqueStatement(
1133      "SELECT guid, name_on_card, expiration_month, expiration_year, "
1134      "       card_number_encrypted, date_modified, origin "
1135      "FROM credit_cards "
1136      "WHERE guid = ?"));
1137  s.BindString(0, guid);
1138
1139  if (!s.Step())
1140    return false;
1141
1142  *credit_card = CreditCardFromStatement(s);
1143  return true;
1144}
1145
1146bool AutofillTable::GetCreditCards(
1147    std::vector<CreditCard*>* credit_cards) {
1148  DCHECK(credit_cards);
1149  credit_cards->clear();
1150
1151  sql::Statement s(db_->GetUniqueStatement(
1152      "SELECT guid "
1153      "FROM credit_cards"));
1154
1155  while (s.Step()) {
1156    std::string guid = s.ColumnString(0);
1157    CreditCard* credit_card = NULL;
1158    if (!GetCreditCard(guid, &credit_card))
1159      return false;
1160    credit_cards->push_back(credit_card);
1161  }
1162
1163  return s.Succeeded();
1164}
1165
1166bool AutofillTable::UpdateCreditCard(const CreditCard& credit_card) {
1167  DCHECK(base::IsValidGUID(credit_card.guid()));
1168
1169  CreditCard* tmp_credit_card = NULL;
1170  if (!GetCreditCard(credit_card.guid(), &tmp_credit_card))
1171    return false;
1172
1173  // Preserve appropriate modification dates by not updating unchanged cards.
1174  scoped_ptr<CreditCard> old_credit_card(tmp_credit_card);
1175  if (*old_credit_card == credit_card)
1176    return true;
1177
1178  sql::Statement s(db_->GetUniqueStatement(
1179      "UPDATE credit_cards "
1180      "SET guid=?, name_on_card=?, expiration_month=?, "
1181      "    expiration_year=?, card_number_encrypted=?, date_modified=?, "
1182      "    origin=? "
1183      "WHERE guid=?"));
1184  BindCreditCardToStatement(credit_card, &s);
1185  s.BindString(7, credit_card.guid());
1186
1187  bool result = s.Run();
1188  DCHECK_GT(db_->GetLastChangeCount(), 0);
1189  return result;
1190}
1191
1192bool AutofillTable::RemoveCreditCard(const std::string& guid) {
1193  DCHECK(base::IsValidGUID(guid));
1194  sql::Statement s(db_->GetUniqueStatement(
1195      "DELETE FROM credit_cards WHERE guid = ?"));
1196  s.BindString(0, guid);
1197
1198  return s.Run();
1199}
1200
1201bool AutofillTable::RemoveAutofillDataModifiedBetween(
1202    const Time& delete_begin,
1203    const Time& delete_end,
1204    std::vector<std::string>* profile_guids,
1205    std::vector<std::string>* credit_card_guids) {
1206  DCHECK(delete_end.is_null() || delete_begin < delete_end);
1207
1208  time_t delete_begin_t = delete_begin.ToTimeT();
1209  time_t delete_end_t = GetEndTime(delete_end);
1210
1211  // Remember Autofill profiles in the time range.
1212  sql::Statement s_profiles_get(db_->GetUniqueStatement(
1213      "SELECT guid FROM autofill_profiles "
1214      "WHERE date_modified >= ? AND date_modified < ?"));
1215  s_profiles_get.BindInt64(0, delete_begin_t);
1216  s_profiles_get.BindInt64(1, delete_end_t);
1217
1218  profile_guids->clear();
1219  while (s_profiles_get.Step()) {
1220    std::string guid = s_profiles_get.ColumnString(0);
1221    profile_guids->push_back(guid);
1222  }
1223  if (!s_profiles_get.Succeeded())
1224    return false;
1225
1226  // Remove Autofill profiles in the time range.
1227  sql::Statement s_profiles(db_->GetUniqueStatement(
1228      "DELETE FROM autofill_profiles "
1229      "WHERE date_modified >= ? AND date_modified < ?"));
1230  s_profiles.BindInt64(0, delete_begin_t);
1231  s_profiles.BindInt64(1, delete_end_t);
1232
1233  if (!s_profiles.Run())
1234    return false;
1235
1236  // Remember Autofill credit cards in the time range.
1237  sql::Statement s_credit_cards_get(db_->GetUniqueStatement(
1238      "SELECT guid FROM credit_cards "
1239      "WHERE date_modified >= ? AND date_modified < ?"));
1240  s_credit_cards_get.BindInt64(0, delete_begin_t);
1241  s_credit_cards_get.BindInt64(1, delete_end_t);
1242
1243  credit_card_guids->clear();
1244  while (s_credit_cards_get.Step()) {
1245    std::string guid = s_credit_cards_get.ColumnString(0);
1246    credit_card_guids->push_back(guid);
1247  }
1248  if (!s_credit_cards_get.Succeeded())
1249    return false;
1250
1251  // Remove Autofill credit cards in the time range.
1252  sql::Statement s_credit_cards(db_->GetUniqueStatement(
1253      "DELETE FROM credit_cards "
1254      "WHERE date_modified >= ? AND date_modified < ?"));
1255  s_credit_cards.BindInt64(0, delete_begin_t);
1256  s_credit_cards.BindInt64(1, delete_end_t);
1257
1258  return s_credit_cards.Run();
1259}
1260
1261bool AutofillTable::RemoveOriginURLsModifiedBetween(
1262    const Time& delete_begin,
1263    const Time& delete_end,
1264    ScopedVector<AutofillProfile>* profiles) {
1265  DCHECK(delete_end.is_null() || delete_begin < delete_end);
1266
1267  time_t delete_begin_t = delete_begin.ToTimeT();
1268  time_t delete_end_t = GetEndTime(delete_end);
1269
1270  // Remember Autofill profiles with URL origins in the time range.
1271  sql::Statement s_profiles_get(db_->GetUniqueStatement(
1272      "SELECT guid, origin FROM autofill_profiles "
1273      "WHERE date_modified >= ? AND date_modified < ?"));
1274  s_profiles_get.BindInt64(0, delete_begin_t);
1275  s_profiles_get.BindInt64(1, delete_end_t);
1276
1277  std::vector<std::string> profile_guids;
1278  while (s_profiles_get.Step()) {
1279    std::string guid = s_profiles_get.ColumnString(0);
1280    std::string origin = s_profiles_get.ColumnString(1);
1281    if (GURL(origin).is_valid())
1282      profile_guids.push_back(guid);
1283  }
1284  if (!s_profiles_get.Succeeded())
1285    return false;
1286
1287  // Clear out the origins for the found Autofill profiles.
1288  for (std::vector<std::string>::const_iterator it = profile_guids.begin();
1289       it != profile_guids.end(); ++it) {
1290    sql::Statement s_profile(db_->GetUniqueStatement(
1291        "UPDATE autofill_profiles SET origin='' WHERE guid=?"));
1292    s_profile.BindString(0, *it);
1293    if (!s_profile.Run())
1294      return false;
1295
1296    AutofillProfile* profile;
1297    if (!GetAutofillProfile(*it, &profile))
1298      return false;
1299
1300    profiles->push_back(profile);
1301  }
1302
1303  // Remember Autofill credit cards with URL origins in the time range.
1304  sql::Statement s_credit_cards_get(db_->GetUniqueStatement(
1305      "SELECT guid, origin FROM credit_cards "
1306      "WHERE date_modified >= ? AND date_modified < ?"));
1307  s_credit_cards_get.BindInt64(0, delete_begin_t);
1308  s_credit_cards_get.BindInt64(1, delete_end_t);
1309
1310  std::vector<std::string> credit_card_guids;
1311  while (s_credit_cards_get.Step()) {
1312    std::string guid = s_credit_cards_get.ColumnString(0);
1313    std::string origin = s_credit_cards_get.ColumnString(1);
1314    if (GURL(origin).is_valid())
1315      credit_card_guids.push_back(guid);
1316  }
1317  if (!s_credit_cards_get.Succeeded())
1318    return false;
1319
1320  // Clear out the origins for the found credit cards.
1321  for (std::vector<std::string>::const_iterator it = credit_card_guids.begin();
1322       it != credit_card_guids.end(); ++it) {
1323    sql::Statement s_credit_card(db_->GetUniqueStatement(
1324        "UPDATE credit_cards SET origin='' WHERE guid=?"));
1325    s_credit_card.BindString(0, *it);
1326    if (!s_credit_card.Run())
1327      return false;
1328  }
1329
1330  return true;
1331}
1332
1333bool AutofillTable::GetAutofillProfilesInTrash(
1334    std::vector<std::string>* guids) {
1335  guids->clear();
1336
1337  sql::Statement s(db_->GetUniqueStatement(
1338      "SELECT guid "
1339      "FROM autofill_profiles_trash"));
1340
1341  while (s.Step()) {
1342    std::string guid = s.ColumnString(0);
1343    guids->push_back(guid);
1344  }
1345
1346  return s.Succeeded();
1347}
1348
1349bool AutofillTable::EmptyAutofillProfilesTrash() {
1350  sql::Statement s(db_->GetUniqueStatement(
1351      "DELETE FROM autofill_profiles_trash"));
1352
1353  return s.Run();
1354}
1355
1356
1357bool AutofillTable::RemoveFormElementForID(int64 pair_id) {
1358  sql::Statement s(db_->GetUniqueStatement(
1359      "DELETE FROM autofill WHERE pair_id = ?"));
1360  s.BindInt64(0, pair_id);
1361
1362  if (s.Run())
1363    return RemoveFormElementForTimeRange(pair_id, Time(), Time(), NULL);
1364
1365  return false;
1366}
1367
1368bool AutofillTable::AddAutofillGUIDToTrash(const std::string& guid) {
1369  sql::Statement s(db_->GetUniqueStatement(
1370    "INSERT INTO autofill_profiles_trash"
1371    " (guid) "
1372    "VALUES (?)"));
1373  s.BindString(0, guid);
1374
1375  return s.Run();
1376}
1377
1378bool AutofillTable::IsAutofillProfilesTrashEmpty() {
1379  sql::Statement s(db_->GetUniqueStatement(
1380      "SELECT guid "
1381      "FROM autofill_profiles_trash"));
1382
1383  return !s.Step();
1384}
1385
1386bool AutofillTable::IsAutofillGUIDInTrash(const std::string& guid) {
1387  sql::Statement s(db_->GetUniqueStatement(
1388      "SELECT guid "
1389      "FROM autofill_profiles_trash "
1390      "WHERE guid = ?"));
1391  s.BindString(0, guid);
1392
1393  return s.Step();
1394}
1395
1396bool AutofillTable::InitMainTable() {
1397  if (!db_->DoesTableExist("autofill")) {
1398    if (!db_->Execute("CREATE TABLE autofill ("
1399                      "name VARCHAR, "
1400                      "value VARCHAR, "
1401                      "value_lower VARCHAR, "
1402                      "pair_id INTEGER PRIMARY KEY, "
1403                      "count INTEGER DEFAULT 1)")) {
1404      NOTREACHED();
1405      return false;
1406    }
1407    if (!db_->Execute("CREATE INDEX autofill_name ON autofill (name)")) {
1408       NOTREACHED();
1409       return false;
1410    }
1411    if (!db_->Execute("CREATE INDEX autofill_name_value_lower ON "
1412                      "autofill (name, value_lower)")) {
1413       NOTREACHED();
1414       return false;
1415    }
1416  }
1417  return true;
1418}
1419
1420bool AutofillTable::InitCreditCardsTable() {
1421  if (!db_->DoesTableExist("credit_cards")) {
1422    if (!db_->Execute("CREATE TABLE credit_cards ( "
1423                      "guid VARCHAR PRIMARY KEY, "
1424                      "name_on_card VARCHAR, "
1425                      "expiration_month INTEGER, "
1426                      "expiration_year INTEGER, "
1427                      "card_number_encrypted BLOB, "
1428                      "date_modified INTEGER NOT NULL DEFAULT 0, "
1429                      "origin VARCHAR DEFAULT '')")) {
1430      NOTREACHED();
1431      return false;
1432    }
1433  }
1434
1435  return true;
1436}
1437
1438bool AutofillTable::InitDatesTable() {
1439  if (!db_->DoesTableExist("autofill_dates")) {
1440    if (!db_->Execute("CREATE TABLE autofill_dates ( "
1441                      "pair_id INTEGER DEFAULT 0, "
1442                      "date_created INTEGER DEFAULT 0)")) {
1443      NOTREACHED();
1444      return false;
1445    }
1446    if (!db_->Execute("CREATE INDEX autofill_dates_pair_id ON "
1447                      "autofill_dates (pair_id)")) {
1448      NOTREACHED();
1449      return false;
1450    }
1451  }
1452  return true;
1453}
1454
1455bool AutofillTable::InitProfilesTable() {
1456  if (!db_->DoesTableExist("autofill_profiles")) {
1457    if (!db_->Execute("CREATE TABLE autofill_profiles ( "
1458                      "guid VARCHAR PRIMARY KEY, "
1459                      "company_name VARCHAR, "
1460                      "address_line_1 VARCHAR, "
1461                      "address_line_2 VARCHAR, "
1462                      "city VARCHAR, "
1463                      "state VARCHAR, "
1464                      "zipcode VARCHAR, "
1465                      "country VARCHAR, "
1466                      "country_code VARCHAR, "
1467                      "date_modified INTEGER NOT NULL DEFAULT 0, "
1468                      "origin VARCHAR DEFAULT '')")) {
1469      NOTREACHED();
1470      return false;
1471    }
1472  }
1473  return true;
1474}
1475
1476bool AutofillTable::InitProfileNamesTable() {
1477  if (!db_->DoesTableExist("autofill_profile_names")) {
1478    if (!db_->Execute("CREATE TABLE autofill_profile_names ( "
1479                      "guid VARCHAR, "
1480                      "first_name VARCHAR, "
1481                      "middle_name VARCHAR, "
1482                      "last_name VARCHAR)")) {
1483      NOTREACHED();
1484      return false;
1485    }
1486  }
1487  return true;
1488}
1489
1490bool AutofillTable::InitProfileEmailsTable() {
1491  if (!db_->DoesTableExist("autofill_profile_emails")) {
1492    if (!db_->Execute("CREATE TABLE autofill_profile_emails ( "
1493                      "guid VARCHAR, "
1494                      "email VARCHAR)")) {
1495      NOTREACHED();
1496      return false;
1497    }
1498  }
1499  return true;
1500}
1501
1502bool AutofillTable::InitProfilePhonesTable() {
1503  if (!db_->DoesTableExist("autofill_profile_phones")) {
1504    if (!db_->Execute("CREATE TABLE autofill_profile_phones ( "
1505                      "guid VARCHAR, "
1506                      "type INTEGER DEFAULT 0, "
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      profile.set_guid(s.ColumnString(0));
1919      DCHECK(base::IsValidGUID(profile.guid()));
1920
1921      profile.SetRawInfo(NAME_FIRST, s.ColumnString16(1));
1922      profile.SetRawInfo(NAME_MIDDLE, s.ColumnString16(2));
1923      profile.SetRawInfo(NAME_LAST, s.ColumnString16(3));
1924      profile.SetRawInfo(EMAIL_ADDRESS, s.ColumnString16(4));
1925      profile.SetRawInfo(COMPANY_NAME, s.ColumnString16(5));
1926      profile.SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(6));
1927      profile.SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(7));
1928      profile.SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(8));
1929      profile.SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(9));
1930      profile.SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(10));
1931      profile.SetInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(11), app_locale_);
1932      profile.SetRawInfo(PHONE_HOME_WHOLE_NUMBER, s.ColumnString16(12));
1933      int64 date_modified = s.ColumnInt64(13);
1934
1935      sql::Statement s_insert(db_->GetUniqueStatement(
1936          "INSERT INTO autofill_profiles_temp"
1937          "(guid, company_name, address_line_1, address_line_2, city,"
1938          " state, zipcode, country, date_modified)"
1939          "VALUES (?,?,?,?,?,?,?,?,?)"));
1940      s_insert.BindString(0, profile.guid());
1941      s_insert.BindString16(1, profile.GetRawInfo(COMPANY_NAME));
1942      s_insert.BindString16(2, profile.GetRawInfo(ADDRESS_HOME_LINE1));
1943      s_insert.BindString16(3, profile.GetRawInfo(ADDRESS_HOME_LINE2));
1944      s_insert.BindString16(4, profile.GetRawInfo(ADDRESS_HOME_CITY));
1945      s_insert.BindString16(5, profile.GetRawInfo(ADDRESS_HOME_STATE));
1946      s_insert.BindString16(6, profile.GetRawInfo(ADDRESS_HOME_ZIP));
1947      s_insert.BindString16(7, profile.GetRawInfo(ADDRESS_HOME_COUNTRY));
1948      s_insert.BindInt64(8, date_modified);
1949
1950      if (!s_insert.Run())
1951        return false;
1952
1953      // Add the other bits: names, emails, and phone numbers.
1954      if (!AddAutofillProfilePieces(profile, db_))
1955        return false;
1956    }  // endwhile
1957    if (!s.Succeeded())
1958      return false;
1959
1960    if (!db_->Execute("DROP TABLE autofill_profiles"))
1961      return false;
1962
1963    if (!db_->Execute(
1964        "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) {
1965      return false;
1966    }
1967  }
1968
1969  // Remove the labels column from the credit_cards table.
1970  if (db_->DoesColumnExist("credit_cards", "label")) {
1971    if (!db_->Execute("CREATE TABLE credit_cards_temp ( "
1972                      "guid VARCHAR PRIMARY KEY, "
1973                      "name_on_card VARCHAR, "
1974                      "expiration_month INTEGER, "
1975                      "expiration_year INTEGER, "
1976                      "card_number_encrypted BLOB, "
1977                      "date_modified INTEGER NOT NULL DEFAULT 0)")) {
1978      return false;
1979    }
1980
1981    if (!db_->Execute(
1982        "INSERT INTO credit_cards_temp "
1983        "SELECT guid, name_on_card, expiration_month, "
1984        "expiration_year, card_number_encrypted, date_modified "
1985        "FROM credit_cards")) {
1986      return false;
1987    }
1988
1989    if (!db_->Execute("DROP TABLE credit_cards"))
1990      return false;
1991
1992    if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards"))
1993      return false;
1994  }
1995
1996  return true;
1997}
1998
1999// Test the existence of the |country_code| column as an indication that
2000// we need a migration.  It is possible that the new |autofill_profiles|
2001// schema is in place because the table was newly created when migrating
2002// from a pre-version-22 database.
2003bool AutofillTable::MigrateToVersion34ProfilesBasedOnCountryCode() {
2004  if (!db_->DoesColumnExist("autofill_profiles", "country_code")) {
2005    if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN "
2006                      "country_code VARCHAR")) {
2007      return false;
2008    }
2009
2010    // Set all the |country_code| fields to match existing |country| values.
2011    sql::Statement s(db_->GetUniqueStatement("SELECT guid, country "
2012                                             "FROM autofill_profiles"));
2013
2014    while (s.Step()) {
2015      sql::Statement update_s(
2016          db_->GetUniqueStatement("UPDATE autofill_profiles "
2017                                  "SET country_code=? WHERE guid=?"));
2018
2019      base::string16 country = s.ColumnString16(1);
2020      update_s.BindString(0, AutofillCountry::GetCountryCode(country,
2021                                                             app_locale_));
2022      update_s.BindString(1, s.ColumnString(0));
2023
2024      if (!update_s.Run())
2025        return false;
2026    }
2027    if (!s.Succeeded())
2028      return false;
2029  }
2030
2031  return true;
2032}
2033
2034// Correct all country codes with value "UK" to be "GB".  This data
2035// was mistakenly introduced in build 686.0.  This migration is to clean
2036// it up.  See http://crbug.com/74511 for details.
2037bool AutofillTable::MigrateToVersion35GreatBritainCountryCodes() {
2038  sql::Statement s(db_->GetUniqueStatement(
2039      "UPDATE autofill_profiles SET country_code=\"GB\" "
2040      "WHERE country_code=\"UK\""));
2041
2042  return s.Run();
2043}
2044
2045// Merge and cull older profiles where possible.
2046bool AutofillTable::MigrateToVersion37MergeAndCullOlderProfiles() {
2047  sql::Statement s(db_->GetUniqueStatement(
2048      "SELECT guid, date_modified FROM autofill_profiles"));
2049
2050  // Accumulate the good profiles.
2051  std::vector<AutofillProfile> accumulated_profiles;
2052  std::vector<AutofillProfile*> accumulated_profiles_p;
2053  std::map<std::string, int64> modification_map;
2054  while (s.Step()) {
2055    std::string guid = s.ColumnString(0);
2056    int64 date_modified = s.ColumnInt64(1);
2057    modification_map.insert(
2058        std::pair<std::string, int64>(guid, date_modified));
2059
2060    sql::Statement s(db_->GetUniqueStatement(
2061        "SELECT guid, company_name, address_line_1, address_line_2, city, "
2062        " state, zipcode, country, country_code, date_modified "
2063        "FROM autofill_profiles "
2064        "WHERE guid=?"));
2065    s.BindString(0, guid);
2066
2067    if (!s.Step())
2068      return false;
2069
2070    scoped_ptr<AutofillProfile> profile(new AutofillProfile);
2071    profile->set_guid(s.ColumnString(0));
2072    DCHECK(base::IsValidGUID(profile->guid()));
2073
2074    profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(1));
2075    profile->SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(2));
2076    profile->SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(3));
2077    profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(4));
2078    profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(5));
2079    profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(6));
2080    // Intentionally skip column 7, which stores the localized country name.
2081    profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(8));
2082    // Intentionally skip column 9, which stores the profile's modification
2083    // date.
2084    profile->set_origin(s.ColumnString(10));
2085
2086    // Get associated name info.
2087    AddAutofillProfileNamesToProfile(db_, profile.get());
2088
2089    // Get associated email info.
2090    AddAutofillProfileEmailsToProfile(db_, profile.get());
2091
2092    // Get associated phone info.
2093    AddAutofillProfilePhonesToProfile(db_, profile.get());
2094
2095    if (PersonalDataManager::IsValidLearnableProfile(*profile, app_locale_)) {
2096      std::vector<AutofillProfile> merged_profiles;
2097      bool merged = PersonalDataManager::MergeProfile(
2098          *profile, accumulated_profiles_p, app_locale_, &merged_profiles);
2099
2100      std::swap(accumulated_profiles, merged_profiles);
2101
2102      accumulated_profiles_p.clear();
2103      accumulated_profiles_p.resize(accumulated_profiles.size());
2104      std::transform(accumulated_profiles.begin(),
2105                     accumulated_profiles.end(),
2106                     accumulated_profiles_p.begin(),
2107                     address_of<AutofillProfile>);
2108
2109      // If the profile got merged trash the original.
2110      if (merged)
2111        AddAutofillGUIDToTrash(profile->guid());
2112
2113    } else {
2114      // An invalid profile, so trash it.
2115      AddAutofillGUIDToTrash(profile->guid());
2116    }
2117  }  // endwhile
2118  if (!s.Succeeded())
2119    return false;
2120
2121  // Drop the current profiles.
2122  if (!ClearAutofillProfiles())
2123    return false;
2124
2125  // Add the newly merged profiles back in.
2126  for (std::vector<AutofillProfile>::const_iterator
2127          iter = accumulated_profiles.begin();
2128       iter != accumulated_profiles.end();
2129       ++iter) {
2130    // Save the profile with its original modification date.
2131    std::map<std::string, int64>::const_iterator date_item =
2132        modification_map.find(iter->guid());
2133    if (date_item == modification_map.end())
2134      return false;
2135
2136    sql::Statement s(db_->GetUniqueStatement(
2137        "INSERT INTO autofill_profiles"
2138        "(guid, company_name, address_line_1, address_line_2, city, state,"
2139        " zipcode, country, country_code, date_modified)"
2140        "VALUES (?,?,?,?,?,?,?,?,?,?)"));
2141    s.BindString(0, iter->guid());
2142    base::string16 text = iter->GetRawInfo(COMPANY_NAME);
2143    s.BindString16(1, LimitDataSize(text));
2144    text = iter->GetRawInfo(ADDRESS_HOME_LINE1);
2145    s.BindString16(2, LimitDataSize(text));
2146    text = iter->GetRawInfo(ADDRESS_HOME_LINE2);
2147    s.BindString16(3, LimitDataSize(text));
2148    text = iter->GetRawInfo(ADDRESS_HOME_CITY);
2149    s.BindString16(4, LimitDataSize(text));
2150    text = iter->GetRawInfo(ADDRESS_HOME_STATE);
2151    s.BindString16(5, LimitDataSize(text));
2152    text = iter->GetRawInfo(ADDRESS_HOME_ZIP);
2153    s.BindString16(6, LimitDataSize(text));
2154    text = iter->GetInfo(ADDRESS_HOME_COUNTRY, app_locale_);
2155    s.BindString16(7, LimitDataSize(text));
2156    text = iter->GetRawInfo(ADDRESS_HOME_COUNTRY);
2157    s.BindString16(8, LimitDataSize(text));
2158    s.BindInt64(9, date_item->second);
2159
2160    if (!s.Run())
2161      return false;
2162
2163    if (!AddAutofillProfilePieces(*iter, db_))
2164      return false;
2165  }
2166
2167  return true;
2168}
2169
2170bool AutofillTable::MigrateToVersion51AddOriginColumn() {
2171  sql::Transaction transaction(db_);
2172  if (!transaction.Begin())
2173    return false;
2174
2175  // Add origin to autofill_profiles.
2176  if (!db_->DoesColumnExist("autofill_profiles", "origin") &&
2177      !db_->Execute("ALTER TABLE autofill_profiles "
2178                    "ADD COLUMN origin VARCHAR DEFAULT ''")) {
2179    return false;
2180  }
2181
2182  // Add origin to credit_cards.
2183  if (!db_->DoesColumnExist("credit_cards", "origin") &&
2184      !db_->Execute("ALTER TABLE credit_cards "
2185                    "ADD COLUMN origin VARCHAR DEFAULT ''")) {
2186      return false;
2187  }
2188
2189  return transaction.Commit();
2190}
2191
2192}  // namespace autofill
2193