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