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.
5 #include "components/autofill/core/browser/webdata/autofill_table.h"
15 #include "base/guid.h"
16 #include "base/i18n/case_conversion.h"
17 #include "base/logging.h"
18 #include "base/numerics/safe_conversions.h"
19 #include "base/strings/string_number_conversions.h"
20 #include "base/strings/utf_string_conversions.h"
21 #include "base/time/time.h"
22 #include "base/tuple.h"
23 #include "components/autofill/core/browser/autofill_country.h"
24 #include "components/autofill/core/browser/autofill_profile.h"
25 #include "components/autofill/core/browser/autofill_type.h"
26 #include "components/autofill/core/browser/credit_card.h"
27 #include "components/autofill/core/browser/personal_data_manager.h"
28 #include "components/autofill/core/browser/webdata/autofill_change.h"
29 #include "components/autofill/core/browser/webdata/autofill_entry.h"
30 #include "components/autofill/core/common/form_field_data.h"
31 #include "components/os_crypt/os_crypt.h"
32 #include "components/webdata/common/web_database.h"
33 #include "sql/statement.h"
34 #include "sql/transaction.h"
35 #include "ui/base/l10n/l10n_util.h"
43 // The period after which Autofill entries should expire in days.
44 const int64 kExpirationPeriodInDays = 60;
51 // Helper struct for AutofillTable::RemoveFormElementsAddedBetween().
52 // Contains all the necessary fields to update a row in the 'autofill' table.
53 struct AutofillUpdate {
57 time_t date_last_used;
61 // Rounds a positive floating point number to the nearest integer.
64 return base::checked_cast<int>(std::floor(f + 0.5f));
67 // Returns the |data_model|'s value corresponding to the |type|, trimmed to the
68 // maximum length that can be stored in a column of the Autofill database.
69 base::string16 GetInfo(const AutofillDataModel& data_model,
70 ServerFieldType type) {
71 base::string16 data = data_model.GetRawInfo(type);
72 if (data.size() > AutofillTable::kMaxDataLength)
73 return data.substr(0, AutofillTable::kMaxDataLength);
78 void BindAutofillProfileToStatement(const AutofillProfile& profile,
80 DCHECK(base::IsValidGUID(profile.guid()));
82 s->BindString(index++, profile.guid());
84 s->BindString16(index++, GetInfo(profile, COMPANY_NAME));
85 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_STREET_ADDRESS));
86 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_DEPENDENT_LOCALITY));
87 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_CITY));
88 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_STATE));
89 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_ZIP));
90 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_SORTING_CODE));
91 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_COUNTRY));
92 s->BindInt64(index++, Time::Now().ToTimeT());
93 s->BindString(index++, profile.origin());
94 s->BindString(index++, profile.language_code());
97 scoped_ptr<AutofillProfile> AutofillProfileFromStatement(
98 const sql::Statement& s) {
99 scoped_ptr<AutofillProfile> profile(new AutofillProfile);
101 profile->set_guid(s.ColumnString(index++));
102 DCHECK(base::IsValidGUID(profile->guid()));
104 profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(index++));
105 profile->SetRawInfo(ADDRESS_HOME_STREET_ADDRESS, s.ColumnString16(index++));
106 profile->SetRawInfo(ADDRESS_HOME_DEPENDENT_LOCALITY,
107 s.ColumnString16(index++));
108 profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++));
109 profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++));
110 profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++));
111 profile->SetRawInfo(ADDRESS_HOME_SORTING_CODE, s.ColumnString16(index++));
112 profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(index++));
113 // Intentionally skip column 9, which stores the profile's modification date.
115 profile->set_origin(s.ColumnString(index++));
116 profile->set_language_code(s.ColumnString(index++));
118 return profile.Pass();
121 void BindCreditCardToStatement(const CreditCard& credit_card,
123 DCHECK(base::IsValidGUID(credit_card.guid()));
125 s->BindString(index++, credit_card.guid());
127 s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_NAME));
128 s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_EXP_MONTH));
129 s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_EXP_4_DIGIT_YEAR));
131 std::string encrypted_data;
132 OSCrypt::EncryptString16(credit_card.GetRawInfo(CREDIT_CARD_NUMBER),
134 s->BindBlob(index++, encrypted_data.data(),
135 static_cast<int>(encrypted_data.length()));
137 s->BindInt64(index++, Time::Now().ToTimeT());
138 s->BindString(index++, credit_card.origin());
141 scoped_ptr<CreditCard> CreditCardFromStatement(const sql::Statement& s) {
142 scoped_ptr<CreditCard> credit_card(new CreditCard);
145 credit_card->set_guid(s.ColumnString(index++));
146 DCHECK(base::IsValidGUID(credit_card->guid()));
148 credit_card->SetRawInfo(CREDIT_CARD_NAME, s.ColumnString16(index++));
149 credit_card->SetRawInfo(CREDIT_CARD_EXP_MONTH, s.ColumnString16(index++));
150 credit_card->SetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR,
151 s.ColumnString16(index++));
152 int encrypted_number_len = s.ColumnByteLength(index);
153 base::string16 credit_card_number;
154 if (encrypted_number_len) {
155 std::string encrypted_number;
156 encrypted_number.resize(encrypted_number_len);
157 memcpy(&encrypted_number[0], s.ColumnBlob(index++), encrypted_number_len);
158 OSCrypt::DecryptString16(encrypted_number, &credit_card_number);
162 credit_card->SetRawInfo(CREDIT_CARD_NUMBER, credit_card_number);
163 // Intentionally skip column 5, which stores the modification date.
165 credit_card->set_origin(s.ColumnString(index++));
167 return credit_card.Pass();
170 // Obsolete version of AddAutofillProfileNamesToProfile, but still needed
171 // for MigrateToVersion37MergeAndCullOlderProfiles().
172 bool AddAutofillProfileNamesToProfileForVersion37(sql::Connection* db,
173 AutofillProfile* profile) {
174 sql::Statement s(db->GetUniqueStatement(
175 "SELECT guid, first_name, middle_name, last_name "
176 "FROM autofill_profile_names "
178 s.BindString(0, profile->guid());
183 std::vector<base::string16> first_names;
184 std::vector<base::string16> middle_names;
185 std::vector<base::string16> last_names;
187 DCHECK_EQ(profile->guid(), s.ColumnString(0));
188 first_names.push_back(s.ColumnString16(1));
189 middle_names.push_back(s.ColumnString16(2));
190 last_names.push_back(s.ColumnString16(3));
195 profile->SetRawMultiInfo(NAME_FIRST, first_names);
196 profile->SetRawMultiInfo(NAME_MIDDLE, middle_names);
197 profile->SetRawMultiInfo(NAME_LAST, last_names);
201 bool AddAutofillProfileNamesToProfile(sql::Connection* db,
202 AutofillProfile* profile) {
203 sql::Statement s(db->GetUniqueStatement(
204 "SELECT guid, first_name, middle_name, last_name, full_name "
205 "FROM autofill_profile_names "
207 s.BindString(0, profile->guid());
212 std::vector<base::string16> first_names;
213 std::vector<base::string16> middle_names;
214 std::vector<base::string16> last_names;
215 std::vector<base::string16> full_names;
217 DCHECK_EQ(profile->guid(), s.ColumnString(0));
218 first_names.push_back(s.ColumnString16(1));
219 middle_names.push_back(s.ColumnString16(2));
220 last_names.push_back(s.ColumnString16(3));
221 full_names.push_back(s.ColumnString16(4));
226 profile->SetRawMultiInfo(NAME_FIRST, first_names);
227 profile->SetRawMultiInfo(NAME_MIDDLE, middle_names);
228 profile->SetRawMultiInfo(NAME_LAST, last_names);
229 profile->SetRawMultiInfo(NAME_FULL, full_names);
233 bool AddAutofillProfileEmailsToProfile(sql::Connection* db,
234 AutofillProfile* profile) {
235 sql::Statement s(db->GetUniqueStatement(
236 "SELECT guid, email "
237 "FROM autofill_profile_emails "
239 s.BindString(0, profile->guid());
244 std::vector<base::string16> emails;
246 DCHECK_EQ(profile->guid(), s.ColumnString(0));
247 emails.push_back(s.ColumnString16(1));
252 profile->SetRawMultiInfo(EMAIL_ADDRESS, emails);
256 bool AddAutofillProfilePhonesToProfile(sql::Connection* db,
257 AutofillProfile* profile) {
258 sql::Statement s(db->GetUniqueStatement(
259 "SELECT guid, number "
260 "FROM autofill_profile_phones "
262 s.BindString(0, profile->guid());
267 std::vector<base::string16> numbers;
269 DCHECK_EQ(profile->guid(), s.ColumnString(0));
270 numbers.push_back(s.ColumnString16(1));
275 profile->SetRawMultiInfo(PHONE_HOME_WHOLE_NUMBER, numbers);
279 // Obsolete version of AddAutofillProfileNames needed for
280 // MigrateToVersion33ProfilesBasedOnFirstName() and
281 // MigrateToVersion37MergeAndCullOlderProfiles().
282 bool AddAutofillProfileNamesForVersion3x(
283 const AutofillProfile& profile,
284 sql::Connection* db) {
285 std::vector<base::string16> first_names;
286 profile.GetRawMultiInfo(NAME_FIRST, &first_names);
287 std::vector<base::string16> middle_names;
288 profile.GetRawMultiInfo(NAME_MIDDLE, &middle_names);
289 std::vector<base::string16> last_names;
290 profile.GetRawMultiInfo(NAME_LAST, &last_names);
291 DCHECK_EQ(first_names.size(), middle_names.size());
292 DCHECK_EQ(first_names.size(), last_names.size());
294 for (size_t i = 0; i < first_names.size(); ++i) {
296 sql::Statement s(db->GetUniqueStatement(
297 "INSERT INTO autofill_profile_names"
298 " (guid, first_name, middle_name, last_name) "
299 "VALUES (?,?,?,?)"));
300 s.BindString(0, profile.guid());
301 s.BindString16(1, first_names[i]);
302 s.BindString16(2, middle_names[i]);
303 s.BindString16(3, last_names[i]);
311 bool AddAutofillProfileNames(const AutofillProfile& profile,
312 sql::Connection* db) {
313 std::vector<base::string16> first_names;
314 profile.GetRawMultiInfo(NAME_FIRST, &first_names);
315 std::vector<base::string16> middle_names;
316 profile.GetRawMultiInfo(NAME_MIDDLE, &middle_names);
317 std::vector<base::string16> last_names;
318 profile.GetRawMultiInfo(NAME_LAST, &last_names);
319 std::vector<base::string16> full_names;
320 profile.GetRawMultiInfo(NAME_FULL, &full_names);
321 DCHECK_EQ(first_names.size(), middle_names.size());
322 DCHECK_EQ(first_names.size(), last_names.size());
323 DCHECK_EQ(first_names.size(), full_names.size());
325 for (size_t i = 0; i < first_names.size(); ++i) {
327 sql::Statement s(db->GetUniqueStatement(
328 "INSERT INTO autofill_profile_names"
329 " (guid, first_name, middle_name, last_name, full_name) "
330 "VALUES (?,?,?,?,?)"));
331 s.BindString(0, profile.guid());
332 s.BindString16(1, first_names[i]);
333 s.BindString16(2, middle_names[i]);
334 s.BindString16(3, last_names[i]);
335 s.BindString16(4, full_names[i]);
343 bool AddAutofillProfileEmails(const AutofillProfile& profile,
344 sql::Connection* db) {
345 std::vector<base::string16> emails;
346 profile.GetRawMultiInfo(EMAIL_ADDRESS, &emails);
348 for (size_t i = 0; i < emails.size(); ++i) {
349 // Add the new email.
350 sql::Statement s(db->GetUniqueStatement(
351 "INSERT INTO autofill_profile_emails"
354 s.BindString(0, profile.guid());
355 s.BindString16(1, emails[i]);
364 bool AddAutofillProfilePhones(const AutofillProfile& profile,
365 sql::Connection* db) {
366 std::vector<base::string16> numbers;
367 profile.GetRawMultiInfo(PHONE_HOME_WHOLE_NUMBER, &numbers);
369 for (size_t i = 0; i < numbers.size(); ++i) {
370 // Add the new number.
371 sql::Statement s(db->GetUniqueStatement(
372 "INSERT INTO autofill_profile_phones"
375 s.BindString(0, profile.guid());
376 s.BindString16(1, numbers[i]);
385 bool AddAutofillProfilePieces(const AutofillProfile& profile,
386 sql::Connection* db) {
387 if (!AddAutofillProfileNames(profile, db))
390 if (!AddAutofillProfileEmails(profile, db))
393 if (!AddAutofillProfilePhones(profile, db))
399 bool RemoveAutofillProfilePieces(const std::string& guid, sql::Connection* db) {
400 sql::Statement s1(db->GetUniqueStatement(
401 "DELETE FROM autofill_profile_names WHERE guid = ?"));
402 s1.BindString(0, guid);
407 sql::Statement s2(db->GetUniqueStatement(
408 "DELETE FROM autofill_profile_emails WHERE guid = ?"));
409 s2.BindString(0, guid);
414 sql::Statement s3(db->GetUniqueStatement(
415 "DELETE FROM autofill_profile_phones WHERE guid = ?"));
416 s3.BindString(0, guid);
421 WebDatabaseTable::TypeKey GetKey() {
422 // We just need a unique constant. Use the address of a static that
423 // COMDAT folding won't touch in an optimizing linker.
424 static int table_key = 0;
425 return reinterpret_cast<void*>(&table_key);
428 time_t GetEndTime(const base::Time& end) {
429 if (end.is_null() || end == base::Time::Max())
430 return std::numeric_limits<time_t>::max();
432 return end.ToTimeT();
437 // The maximum length allowed for form data.
438 const size_t AutofillTable::kMaxDataLength = 1024;
440 AutofillTable::AutofillTable(const std::string& app_locale)
441 : app_locale_(app_locale) {
444 AutofillTable::~AutofillTable() {
447 AutofillTable* AutofillTable::FromWebDatabase(WebDatabase* db) {
448 return static_cast<AutofillTable*>(db->GetTable(GetKey()));
451 WebDatabaseTable::TypeKey AutofillTable::GetTypeKey() const {
455 bool AutofillTable::CreateTablesIfNecessary() {
456 return (InitMainTable() && InitCreditCardsTable() && InitProfilesTable() &&
457 InitProfileNamesTable() && InitProfileEmailsTable() &&
458 InitProfilePhonesTable() && InitProfileTrashTable());
461 bool AutofillTable::IsSyncable() {
465 bool AutofillTable::MigrateToVersion(int version,
466 bool* update_compatible_version) {
467 // Migrate if necessary.
470 return MigrateToVersion22ClearAutofillEmptyValueElements();
472 return MigrateToVersion23AddCardNumberEncryptedColumn();
474 return MigrateToVersion24CleanupOversizedStringFields();
476 *update_compatible_version = true;
477 return MigrateToVersion27UpdateLegacyCreditCards();
479 *update_compatible_version = true;
480 return MigrateToVersion30AddDateModifed();
482 *update_compatible_version = true;
483 return MigrateToVersion31AddGUIDToCreditCardsAndProfiles();
485 *update_compatible_version = true;
486 return MigrateToVersion32UpdateProfilesAndCreditCards();
488 *update_compatible_version = true;
489 return MigrateToVersion33ProfilesBasedOnFirstName();
491 *update_compatible_version = true;
492 return MigrateToVersion34ProfilesBasedOnCountryCode();
494 *update_compatible_version = true;
495 return MigrateToVersion35GreatBritainCountryCodes();
496 // Combine migrations 36 and 37. This is due to enhancements to the merge
497 // step when migrating profiles. The original migration from 35 to 36 did
498 // not merge profiles with identical addresses, but the migration from 36 to
499 // 37 does. The step from 35 to 36 should only happen on the Chrome 12 dev
500 // channel. Chrome 12 beta and release users will jump from 35 to 37
501 // directly getting the full benefits of the multi-valued merge as well as
502 // the culling of bad data.
504 *update_compatible_version = true;
505 return MigrateToVersion37MergeAndCullOlderProfiles();
507 // Combine migrations 50 and 51. The migration code from version 49 to 50
508 // worked correctly for users with existing 'origin' columns, but failed
509 // to create these columns for new users.
510 return MigrateToVersion51AddOriginColumn();
512 *update_compatible_version = true;
513 return MigrateToVersion54AddI18nFieldsAndRemoveDeprecatedFields();
515 *update_compatible_version = true;
516 return MigrateToVersion55MergeAutofillDatesTable();
518 *update_compatible_version = true;
519 return MigrateToVersion56AddProfileLanguageCodeForFormatting();
521 *update_compatible_version = true;
522 return MigrateToVersion57AddFullNameField();
527 bool AutofillTable::AddFormFieldValues(
528 const std::vector<FormFieldData>& elements,
529 std::vector<AutofillChange>* changes) {
530 return AddFormFieldValuesTime(elements, changes, Time::Now());
533 bool AutofillTable::AddFormFieldValue(const FormFieldData& element,
534 std::vector<AutofillChange>* changes) {
535 return AddFormFieldValueTime(element, changes, Time::Now());
538 bool AutofillTable::GetFormValuesForElementName(
539 const base::string16& name,
540 const base::string16& prefix,
541 std::vector<base::string16>* values,
546 if (prefix.empty()) {
547 s.Assign(db_->GetUniqueStatement(
548 "SELECT value FROM autofill "
550 "ORDER BY count DESC "
552 s.BindString16(0, name);
555 base::string16 prefix_lower = base::i18n::ToLower(prefix);
556 base::string16 next_prefix = prefix_lower;
557 next_prefix[next_prefix.length() - 1]++;
559 s.Assign(db_->GetUniqueStatement(
560 "SELECT value FROM autofill "
561 "WHERE name = ? AND "
562 "value_lower >= ? AND "
564 "ORDER BY count DESC "
566 s.BindString16(0, name);
567 s.BindString16(1, prefix_lower);
568 s.BindString16(2, next_prefix);
574 values->push_back(s.ColumnString16(0));
575 return s.Succeeded();
578 bool AutofillTable::HasFormElements() {
579 sql::Statement s(db_->GetUniqueStatement("SELECT COUNT(*) FROM autofill"));
584 return s.ColumnInt(0) > 0;
587 bool AutofillTable::RemoveFormElementsAddedBetween(
588 const Time& delete_begin,
589 const Time& delete_end,
590 std::vector<AutofillChange>* changes) {
591 const time_t delete_begin_time_t = delete_begin.ToTimeT();
592 const time_t delete_end_time_t = GetEndTime(delete_end);
594 // Query for the name, value, count, and access dates of all form elements
595 // that were used between the given times.
596 sql::Statement s(db_->GetUniqueStatement(
597 "SELECT name, value, count, date_created, date_last_used FROM autofill "
598 "WHERE (date_created >= ? AND date_created < ?) OR "
599 " (date_last_used >= ? AND date_last_used < ?)"));
600 s.BindInt64(0, delete_begin_time_t);
601 s.BindInt64(1, delete_end_time_t);
602 s.BindInt64(2, delete_begin_time_t);
603 s.BindInt64(3, delete_end_time_t);
605 std::vector<AutofillUpdate> updates;
606 std::vector<AutofillChange> tentative_changes;
608 base::string16 name = s.ColumnString16(0);
609 base::string16 value = s.ColumnString16(1);
610 int count = s.ColumnInt(2);
611 time_t date_created_time_t = s.ColumnInt64(3);
612 time_t date_last_used_time_t = s.ColumnInt64(4);
614 // If *all* uses of the element were between |delete_begin| and
615 // |delete_end|, then delete the element. Otherwise, update the use
616 // timestamps and use count.
617 AutofillChange::Type change_type;
618 if (date_created_time_t >= delete_begin_time_t &&
619 date_last_used_time_t < delete_end_time_t) {
620 change_type = AutofillChange::REMOVE;
622 change_type = AutofillChange::UPDATE;
624 // For all updated elements, set either date_created or date_last_used so
625 // that the range [date_created, date_last_used] no longer overlaps with
626 // [delete_begin, delete_end). Update the count by interpolating.
627 // Precisely, compute the average amount of time between increments to the
628 // count in the original range [date_created, date_last_used]:
629 // avg_delta = (date_last_used_orig - date_created_orig) / (count - 1)
630 // The count can be expressed as
631 // count = 1 + (date_last_used - date_created) / avg_delta
632 // Hence, update the count to
633 // count_new = 1 + (date_last_used_new - date_created_new) / avg_delta
634 // = 1 + ((count - 1) *
635 // (date_last_used_new - date_created_new) /
636 // (date_last_used_orig - date_created_orig))
637 // Interpolating might not give a result that completely accurately
638 // reflects the user's history, but it's the best that can be done given
639 // the information in the database.
640 AutofillUpdate updated_entry;
641 updated_entry.name = name;
642 updated_entry.value = value;
643 updated_entry.date_created =
644 date_created_time_t < delete_begin_time_t ?
645 date_created_time_t :
647 updated_entry.date_last_used =
648 date_last_used_time_t >= delete_end_time_t ?
649 date_last_used_time_t :
650 delete_begin_time_t - 1;
651 updated_entry.count =
653 Round(1.0 * (count - 1) *
654 (updated_entry.date_last_used - updated_entry.date_created) /
655 (date_last_used_time_t - date_created_time_t));
656 updates.push_back(updated_entry);
659 tentative_changes.push_back(
660 AutofillChange(change_type, AutofillKey(name, value)));
665 // As a single transaction, remove or update the elements appropriately.
666 sql::Statement s_delete(db_->GetUniqueStatement(
667 "DELETE FROM autofill WHERE date_created >= ? AND date_last_used < ?"));
668 s_delete.BindInt64(0, delete_begin_time_t);
669 s_delete.BindInt64(1, delete_end_time_t);
670 sql::Transaction transaction(db_);
671 if (!transaction.Begin())
675 for (size_t i = 0; i < updates.size(); ++i) {
676 sql::Statement s_update(db_->GetUniqueStatement(
677 "UPDATE autofill SET date_created = ?, date_last_used = ?, count = ?"
678 "WHERE name = ? AND value = ?"));
679 s_update.BindInt64(0, updates[i].date_created);
680 s_update.BindInt64(1, updates[i].date_last_used);
681 s_update.BindInt(2, updates[i].count);
682 s_update.BindString16(3, updates[i].name);
683 s_update.BindString16(4, updates[i].value);
687 if (!transaction.Commit())
690 *changes = tentative_changes;
694 bool AutofillTable::RemoveExpiredFormElements(
695 std::vector<AutofillChange>* changes) {
696 base::Time expiration_time =
697 base::Time::Now() - base::TimeDelta::FromDays(kExpirationPeriodInDays);
699 // Query for the name and value of all form elements that were last used
700 // before the |expiration_time|.
701 sql::Statement select_for_delete(db_->GetUniqueStatement(
702 "SELECT name, value FROM autofill WHERE date_last_used < ?"));
703 select_for_delete.BindInt64(0, expiration_time.ToTimeT());
704 std::vector<AutofillChange> tentative_changes;
705 while (select_for_delete.Step()) {
706 base::string16 name = select_for_delete.ColumnString16(0);
707 base::string16 value = select_for_delete.ColumnString16(1);
708 tentative_changes.push_back(
709 AutofillChange(AutofillChange::REMOVE, AutofillKey(name, value)));
712 if (!select_for_delete.Succeeded())
715 sql::Statement delete_data_statement(db_->GetUniqueStatement(
716 "DELETE FROM autofill WHERE date_last_used < ?"));
717 delete_data_statement.BindInt64(0, expiration_time.ToTimeT());
718 if (!delete_data_statement.Run())
721 *changes = tentative_changes;
725 bool AutofillTable::AddFormFieldValuesTime(
726 const std::vector<FormFieldData>& elements,
727 std::vector<AutofillChange>* changes,
729 // Only add one new entry for each unique element name. Use |seen_names| to
730 // track this. Add up to |kMaximumUniqueNames| unique entries per form.
731 const size_t kMaximumUniqueNames = 256;
732 std::set<base::string16> seen_names;
734 for (std::vector<FormFieldData>::const_iterator itr = elements.begin();
735 itr != elements.end(); ++itr) {
736 if (seen_names.size() >= kMaximumUniqueNames)
738 if (seen_names.find(itr->name) != seen_names.end())
740 result = result && AddFormFieldValueTime(*itr, changes, time);
741 seen_names.insert(itr->name);
746 bool AutofillTable::GetAllAutofillEntries(std::vector<AutofillEntry>* entries) {
747 sql::Statement s(db_->GetUniqueStatement(
748 "SELECT name, value, date_created, date_last_used FROM autofill"));
751 base::string16 name = s.ColumnString16(0);
752 base::string16 value = s.ColumnString16(1);
753 Time date_created = Time::FromTimeT(s.ColumnInt64(2));
754 Time date_last_used = Time::FromTimeT(s.ColumnInt64(3));
756 AutofillEntry(AutofillKey(name, value), date_created, date_last_used));
759 return s.Succeeded();
762 bool AutofillTable::GetAutofillTimestamps(const base::string16& name,
763 const base::string16& value,
765 Time* date_last_used) {
766 sql::Statement s(db_->GetUniqueStatement(
767 "SELECT date_created, date_last_used FROM autofill "
768 "WHERE name = ? AND value = ?"));
769 s.BindString16(0, name);
770 s.BindString16(1, value);
774 *date_created = Time::FromTimeT(s.ColumnInt64(0));
775 *date_last_used = Time::FromTimeT(s.ColumnInt64(1));
781 bool AutofillTable::UpdateAutofillEntries(
782 const std::vector<AutofillEntry>& entries) {
786 // Remove all existing entries.
787 for (size_t i = 0; i < entries.size(); ++i) {
788 sql::Statement s(db_->GetUniqueStatement(
789 "DELETE FROM autofill WHERE name = ? AND value = ?"));
790 s.BindString16(0, entries[i].key().name());
791 s.BindString16(1, entries[i].key().value());
796 // Insert all the supplied autofill entries.
797 for (size_t i = 0; i < entries.size(); ++i) {
798 if (!InsertAutofillEntry(entries[i]))
805 bool AutofillTable::InsertAutofillEntry(const AutofillEntry& entry) {
807 "INSERT INTO autofill "
808 "(name, value, value_lower, date_created, date_last_used, count) "
809 "VALUES (?, ?, ?, ?, ?, ?)";
810 sql::Statement s(db_->GetUniqueStatement(sql.c_str()));
811 s.BindString16(0, entry.key().name());
812 s.BindString16(1, entry.key().value());
813 s.BindString16(2, base::i18n::ToLower(entry.key().value()));
814 s.BindInt64(3, entry.date_created().ToTimeT());
815 s.BindInt64(4, entry.date_last_used().ToTimeT());
816 // TODO(isherman): The counts column is currently synced implicitly as the
817 // number of timestamps. Sync the value explicitly instead, since the DB now
818 // only saves the first and last timestamp, which makes counting timestamps
819 // completely meaningless as a way to track frequency of usage.
820 s.BindInt(5, entry.date_last_used() == entry.date_created() ? 1 : 2);
824 bool AutofillTable::AddFormFieldValueTime(const FormFieldData& element,
825 std::vector<AutofillChange>* changes,
827 sql::Statement s_exists(db_->GetUniqueStatement(
828 "SELECT COUNT(*) FROM autofill WHERE name = ? AND value = ?"));
829 s_exists.BindString16(0, element.name);
830 s_exists.BindString16(1, element.value);
831 if (!s_exists.Step())
834 bool already_exists = s_exists.ColumnInt(0) > 0;
835 if (already_exists) {
836 sql::Statement s(db_->GetUniqueStatement(
837 "UPDATE autofill SET date_last_used = ?, count = count + 1 "
838 "WHERE name = ? AND value = ?"));
839 s.BindInt64(0, time.ToTimeT());
840 s.BindString16(1, element.name);
841 s.BindString16(2, element.value);
845 time_t time_as_time_t = time.ToTimeT();
846 sql::Statement s(db_->GetUniqueStatement(
847 "INSERT INTO autofill "
848 "(name, value, value_lower, date_created, date_last_used, count) "
849 "VALUES (?, ?, ?, ?, ?, ?)"));
850 s.BindString16(0, element.name);
851 s.BindString16(1, element.value);
852 s.BindString16(2, base::i18n::ToLower(element.value));
853 s.BindInt64(3, time_as_time_t);
854 s.BindInt64(4, time_as_time_t);
860 AutofillChange::Type change_type =
861 already_exists ? AutofillChange::UPDATE : AutofillChange::ADD;
863 AutofillChange(change_type, AutofillKey(element.name, element.value)));
868 bool AutofillTable::RemoveFormElement(const base::string16& name,
869 const base::string16& value) {
870 sql::Statement s(db_->GetUniqueStatement(
871 "DELETE FROM autofill WHERE name = ? AND value= ?"));
872 s.BindString16(0, name);
873 s.BindString16(1, value);
877 bool AutofillTable::AddAutofillProfile(const AutofillProfile& profile) {
878 if (IsAutofillGUIDInTrash(profile.guid()))
881 sql::Statement s(db_->GetUniqueStatement(
882 "INSERT INTO autofill_profiles"
883 "(guid, company_name, street_address, dependent_locality, city, state,"
884 " zipcode, sorting_code, country_code, date_modified, origin,"
886 "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"));
887 BindAutofillProfileToStatement(profile, &s);
892 return AddAutofillProfilePieces(profile, db_);
895 bool AutofillTable::GetAutofillProfile(const std::string& guid,
896 AutofillProfile** profile) {
897 DCHECK(base::IsValidGUID(guid));
899 sql::Statement s(db_->GetUniqueStatement(
900 "SELECT guid, company_name, street_address, dependent_locality, city,"
901 " state, zipcode, sorting_code, country_code, date_modified, origin,"
903 "FROM autofill_profiles "
905 s.BindString(0, guid);
910 scoped_ptr<AutofillProfile> p = AutofillProfileFromStatement(s);
912 // Get associated name info.
913 AddAutofillProfileNamesToProfile(db_, p.get());
915 // Get associated email info.
916 AddAutofillProfileEmailsToProfile(db_, p.get());
918 // Get associated phone info.
919 AddAutofillProfilePhonesToProfile(db_, p.get());
921 *profile = p.release();
925 bool AutofillTable::GetAutofillProfiles(
926 std::vector<AutofillProfile*>* profiles) {
930 sql::Statement s(db_->GetUniqueStatement(
932 "FROM autofill_profiles"));
935 std::string guid = s.ColumnString(0);
936 AutofillProfile* profile = NULL;
937 if (!GetAutofillProfile(guid, &profile))
939 profiles->push_back(profile);
942 return s.Succeeded();
945 bool AutofillTable::UpdateAutofillProfile(const AutofillProfile& profile) {
946 DCHECK(base::IsValidGUID(profile.guid()));
948 // Don't update anything until the trash has been emptied. There may be
949 // pending modifications to process.
950 if (!IsAutofillProfilesTrashEmpty())
953 AutofillProfile* tmp_profile = NULL;
954 if (!GetAutofillProfile(profile.guid(), &tmp_profile))
957 // Preserve appropriate modification dates by not updating unchanged profiles.
958 scoped_ptr<AutofillProfile> old_profile(tmp_profile);
959 if (*old_profile == profile)
962 sql::Statement s(db_->GetUniqueStatement(
963 "UPDATE autofill_profiles "
964 "SET guid=?, company_name=?, street_address=?, dependent_locality=?, "
965 " city=?, state=?, zipcode=?, sorting_code=?, country_code=?, "
966 " date_modified=?, origin=?, language_code=? "
968 BindAutofillProfileToStatement(profile, &s);
969 s.BindString(12, profile.guid());
971 bool result = s.Run();
972 DCHECK_GT(db_->GetLastChangeCount(), 0);
976 // Remove the old names, emails, and phone numbers.
977 if (!RemoveAutofillProfilePieces(profile.guid(), db_))
980 return AddAutofillProfilePieces(profile, db_);
983 bool AutofillTable::RemoveAutofillProfile(const std::string& guid) {
984 DCHECK(base::IsValidGUID(guid));
986 if (IsAutofillGUIDInTrash(guid)) {
987 sql::Statement s_trash(db_->GetUniqueStatement(
988 "DELETE FROM autofill_profiles_trash WHERE guid = ?"));
989 s_trash.BindString(0, guid);
991 bool success = s_trash.Run();
992 DCHECK_GT(db_->GetLastChangeCount(), 0) << "Expected item in trash";
996 sql::Statement s(db_->GetUniqueStatement(
997 "DELETE FROM autofill_profiles WHERE guid = ?"));
998 s.BindString(0, guid);
1003 return RemoveAutofillProfilePieces(guid, db_);
1006 bool AutofillTable::ClearAutofillProfiles() {
1007 sql::Statement s1(db_->GetUniqueStatement(
1008 "DELETE FROM autofill_profiles"));
1013 sql::Statement s2(db_->GetUniqueStatement(
1014 "DELETE FROM autofill_profile_names"));
1019 sql::Statement s3(db_->GetUniqueStatement(
1020 "DELETE FROM autofill_profile_emails"));
1025 sql::Statement s4(db_->GetUniqueStatement(
1026 "DELETE FROM autofill_profile_phones"));
1031 bool AutofillTable::AddCreditCard(const CreditCard& credit_card) {
1032 sql::Statement s(db_->GetUniqueStatement(
1033 "INSERT INTO credit_cards"
1034 "(guid, name_on_card, expiration_month, expiration_year, "
1035 " card_number_encrypted, date_modified, origin)"
1036 "VALUES (?,?,?,?,?,?,?)"));
1037 BindCreditCardToStatement(credit_card, &s);
1042 DCHECK_GT(db_->GetLastChangeCount(), 0);
1046 bool AutofillTable::GetCreditCard(const std::string& guid,
1047 CreditCard** credit_card) {
1048 DCHECK(base::IsValidGUID(guid));
1049 sql::Statement s(db_->GetUniqueStatement(
1050 "SELECT guid, name_on_card, expiration_month, expiration_year, "
1051 " card_number_encrypted, date_modified, origin "
1052 "FROM credit_cards "
1054 s.BindString(0, guid);
1059 *credit_card = CreditCardFromStatement(s).release();
1063 bool AutofillTable::GetCreditCards(
1064 std::vector<CreditCard*>* credit_cards) {
1065 DCHECK(credit_cards);
1066 credit_cards->clear();
1068 sql::Statement s(db_->GetUniqueStatement(
1070 "FROM credit_cards"));
1073 std::string guid = s.ColumnString(0);
1074 CreditCard* credit_card = NULL;
1075 if (!GetCreditCard(guid, &credit_card))
1077 credit_cards->push_back(credit_card);
1080 return s.Succeeded();
1083 bool AutofillTable::UpdateCreditCard(const CreditCard& credit_card) {
1084 DCHECK(base::IsValidGUID(credit_card.guid()));
1086 CreditCard* tmp_credit_card = NULL;
1087 if (!GetCreditCard(credit_card.guid(), &tmp_credit_card))
1090 // Preserve appropriate modification dates by not updating unchanged cards.
1091 scoped_ptr<CreditCard> old_credit_card(tmp_credit_card);
1092 if (*old_credit_card == credit_card)
1095 sql::Statement s(db_->GetUniqueStatement(
1096 "UPDATE credit_cards "
1097 "SET guid=?, name_on_card=?, expiration_month=?, "
1098 " expiration_year=?, card_number_encrypted=?, date_modified=?, "
1101 BindCreditCardToStatement(credit_card, &s);
1102 s.BindString(7, credit_card.guid());
1104 bool result = s.Run();
1105 DCHECK_GT(db_->GetLastChangeCount(), 0);
1109 bool AutofillTable::RemoveCreditCard(const std::string& guid) {
1110 DCHECK(base::IsValidGUID(guid));
1111 sql::Statement s(db_->GetUniqueStatement(
1112 "DELETE FROM credit_cards WHERE guid = ?"));
1113 s.BindString(0, guid);
1118 bool AutofillTable::RemoveAutofillDataModifiedBetween(
1119 const Time& delete_begin,
1120 const Time& delete_end,
1121 std::vector<std::string>* profile_guids,
1122 std::vector<std::string>* credit_card_guids) {
1123 DCHECK(delete_end.is_null() || delete_begin < delete_end);
1125 time_t delete_begin_t = delete_begin.ToTimeT();
1126 time_t delete_end_t = GetEndTime(delete_end);
1128 // Remember Autofill profiles in the time range.
1129 sql::Statement s_profiles_get(db_->GetUniqueStatement(
1130 "SELECT guid FROM autofill_profiles "
1131 "WHERE date_modified >= ? AND date_modified < ?"));
1132 s_profiles_get.BindInt64(0, delete_begin_t);
1133 s_profiles_get.BindInt64(1, delete_end_t);
1135 profile_guids->clear();
1136 while (s_profiles_get.Step()) {
1137 std::string guid = s_profiles_get.ColumnString(0);
1138 profile_guids->push_back(guid);
1140 if (!s_profiles_get.Succeeded())
1143 // Remove Autofill profiles in the time range.
1144 sql::Statement s_profiles(db_->GetUniqueStatement(
1145 "DELETE FROM autofill_profiles "
1146 "WHERE date_modified >= ? AND date_modified < ?"));
1147 s_profiles.BindInt64(0, delete_begin_t);
1148 s_profiles.BindInt64(1, delete_end_t);
1150 if (!s_profiles.Run())
1153 // Remember Autofill credit cards in the time range.
1154 sql::Statement s_credit_cards_get(db_->GetUniqueStatement(
1155 "SELECT guid FROM credit_cards "
1156 "WHERE date_modified >= ? AND date_modified < ?"));
1157 s_credit_cards_get.BindInt64(0, delete_begin_t);
1158 s_credit_cards_get.BindInt64(1, delete_end_t);
1160 credit_card_guids->clear();
1161 while (s_credit_cards_get.Step()) {
1162 std::string guid = s_credit_cards_get.ColumnString(0);
1163 credit_card_guids->push_back(guid);
1165 if (!s_credit_cards_get.Succeeded())
1168 // Remove Autofill credit cards in the time range.
1169 sql::Statement s_credit_cards(db_->GetUniqueStatement(
1170 "DELETE FROM credit_cards "
1171 "WHERE date_modified >= ? AND date_modified < ?"));
1172 s_credit_cards.BindInt64(0, delete_begin_t);
1173 s_credit_cards.BindInt64(1, delete_end_t);
1175 return s_credit_cards.Run();
1178 bool AutofillTable::RemoveOriginURLsModifiedBetween(
1179 const Time& delete_begin,
1180 const Time& delete_end,
1181 ScopedVector<AutofillProfile>* profiles) {
1182 DCHECK(delete_end.is_null() || delete_begin < delete_end);
1184 time_t delete_begin_t = delete_begin.ToTimeT();
1185 time_t delete_end_t = GetEndTime(delete_end);
1187 // Remember Autofill profiles with URL origins in the time range.
1188 sql::Statement s_profiles_get(db_->GetUniqueStatement(
1189 "SELECT guid, origin FROM autofill_profiles "
1190 "WHERE date_modified >= ? AND date_modified < ?"));
1191 s_profiles_get.BindInt64(0, delete_begin_t);
1192 s_profiles_get.BindInt64(1, delete_end_t);
1194 std::vector<std::string> profile_guids;
1195 while (s_profiles_get.Step()) {
1196 std::string guid = s_profiles_get.ColumnString(0);
1197 std::string origin = s_profiles_get.ColumnString(1);
1198 if (GURL(origin).is_valid())
1199 profile_guids.push_back(guid);
1201 if (!s_profiles_get.Succeeded())
1204 // Clear out the origins for the found Autofill profiles.
1205 for (std::vector<std::string>::const_iterator it = profile_guids.begin();
1206 it != profile_guids.end(); ++it) {
1207 sql::Statement s_profile(db_->GetUniqueStatement(
1208 "UPDATE autofill_profiles SET origin='' WHERE guid=?"));
1209 s_profile.BindString(0, *it);
1210 if (!s_profile.Run())
1213 AutofillProfile* profile;
1214 if (!GetAutofillProfile(*it, &profile))
1217 profiles->push_back(profile);
1220 // Remember Autofill credit cards with URL origins in the time range.
1221 sql::Statement s_credit_cards_get(db_->GetUniqueStatement(
1222 "SELECT guid, origin FROM credit_cards "
1223 "WHERE date_modified >= ? AND date_modified < ?"));
1224 s_credit_cards_get.BindInt64(0, delete_begin_t);
1225 s_credit_cards_get.BindInt64(1, delete_end_t);
1227 std::vector<std::string> credit_card_guids;
1228 while (s_credit_cards_get.Step()) {
1229 std::string guid = s_credit_cards_get.ColumnString(0);
1230 std::string origin = s_credit_cards_get.ColumnString(1);
1231 if (GURL(origin).is_valid())
1232 credit_card_guids.push_back(guid);
1234 if (!s_credit_cards_get.Succeeded())
1237 // Clear out the origins for the found credit cards.
1238 for (std::vector<std::string>::const_iterator it = credit_card_guids.begin();
1239 it != credit_card_guids.end(); ++it) {
1240 sql::Statement s_credit_card(db_->GetUniqueStatement(
1241 "UPDATE credit_cards SET origin='' WHERE guid=?"));
1242 s_credit_card.BindString(0, *it);
1243 if (!s_credit_card.Run())
1250 bool AutofillTable::GetAutofillProfilesInTrash(
1251 std::vector<std::string>* guids) {
1254 sql::Statement s(db_->GetUniqueStatement(
1256 "FROM autofill_profiles_trash"));
1259 std::string guid = s.ColumnString(0);
1260 guids->push_back(guid);
1263 return s.Succeeded();
1266 bool AutofillTable::EmptyAutofillProfilesTrash() {
1267 sql::Statement s(db_->GetUniqueStatement(
1268 "DELETE FROM autofill_profiles_trash"));
1274 bool AutofillTable::AddAutofillGUIDToTrash(const std::string& guid) {
1275 sql::Statement s(db_->GetUniqueStatement(
1276 "INSERT INTO autofill_profiles_trash"
1279 s.BindString(0, guid);
1284 bool AutofillTable::IsAutofillProfilesTrashEmpty() {
1285 sql::Statement s(db_->GetUniqueStatement(
1287 "FROM autofill_profiles_trash"));
1292 bool AutofillTable::IsAutofillGUIDInTrash(const std::string& guid) {
1293 sql::Statement s(db_->GetUniqueStatement(
1295 "FROM autofill_profiles_trash "
1297 s.BindString(0, guid);
1302 bool AutofillTable::InitMainTable() {
1303 if (!db_->DoesTableExist("autofill")) {
1304 if (!db_->Execute("CREATE TABLE autofill ("
1307 "value_lower VARCHAR, "
1308 "date_created INTEGER DEFAULT 0, "
1309 "date_last_used INTEGER DEFAULT 0, "
1310 "count INTEGER DEFAULT 1, "
1311 "PRIMARY KEY (name, value))") ||
1312 !db_->Execute("CREATE INDEX autofill_name ON autofill (name)") ||
1313 !db_->Execute("CREATE INDEX autofill_name_value_lower ON "
1314 "autofill (name, value_lower)")) {
1322 bool AutofillTable::InitCreditCardsTable() {
1323 if (!db_->DoesTableExist("credit_cards")) {
1324 if (!db_->Execute("CREATE TABLE credit_cards ( "
1325 "guid VARCHAR PRIMARY KEY, "
1326 "name_on_card VARCHAR, "
1327 "expiration_month INTEGER, "
1328 "expiration_year INTEGER, "
1329 "card_number_encrypted BLOB, "
1330 "date_modified INTEGER NOT NULL DEFAULT 0, "
1331 "origin VARCHAR DEFAULT '')")) {
1340 bool AutofillTable::InitProfilesTable() {
1341 if (!db_->DoesTableExist("autofill_profiles")) {
1342 if (!db_->Execute("CREATE TABLE autofill_profiles ( "
1343 "guid VARCHAR PRIMARY KEY, "
1344 "company_name VARCHAR, "
1345 "street_address VARCHAR, "
1346 "dependent_locality VARCHAR, "
1350 "sorting_code VARCHAR, "
1351 "country_code VARCHAR, "
1352 "date_modified INTEGER NOT NULL DEFAULT 0, "
1353 "origin VARCHAR DEFAULT '', "
1354 "language_code VARCHAR)")) {
1362 bool AutofillTable::InitProfileNamesTable() {
1363 if (!db_->DoesTableExist("autofill_profile_names")) {
1364 if (!db_->Execute("CREATE TABLE autofill_profile_names ( "
1366 "first_name VARCHAR, "
1367 "middle_name VARCHAR, "
1368 "last_name VARCHAR, "
1369 "full_name VARCHAR)")) {
1377 bool AutofillTable::InitProfileEmailsTable() {
1378 if (!db_->DoesTableExist("autofill_profile_emails")) {
1379 if (!db_->Execute("CREATE TABLE autofill_profile_emails ( "
1381 "email VARCHAR)")) {
1389 bool AutofillTable::InitProfilePhonesTable() {
1390 if (!db_->DoesTableExist("autofill_profile_phones")) {
1391 if (!db_->Execute("CREATE TABLE autofill_profile_phones ( "
1393 "number VARCHAR)")) {
1401 bool AutofillTable::InitProfileTrashTable() {
1402 if (!db_->DoesTableExist("autofill_profiles_trash")) {
1403 if (!db_->Execute("CREATE TABLE autofill_profiles_trash ( "
1412 bool AutofillTable::MigrateToVersion22ClearAutofillEmptyValueElements() {
1413 if (!db_->DoesTableExist("autofill") &&
1414 (!db_->Execute("CREATE TABLE autofill ("
1417 " value_lower VARCHAR,"
1418 " pair_id INTEGER PRIMARY KEY,"
1419 " count INTEGER DEFAULT 1)") ||
1420 !db_->Execute("CREATE INDEX autofill_name ON autofill (name)") ||
1421 !db_->Execute("CREATE INDEX autofill_name_value_lower ON"
1422 " autofill (name, value_lower)") ||
1423 !db_->Execute("CREATE TABLE autofill_dates ("
1424 " pair_id INTEGER DEFAULT 0,"
1425 " date_created INTEGER DEFAULT 0)") ||
1426 !db_->Execute("CREATE INDEX autofill_dates_pair_id ON"
1427 " autofill (pair_id)")))
1431 sql::Statement s(db_->GetUniqueStatement(
1432 "SELECT pair_id FROM autofill WHERE TRIM(value) = \"\""));
1436 std::set<int64> ids;
1438 ids.insert(s.ColumnInt64(0));
1442 if (!db_->Execute("DELETE FROM autofill WHERE TRIM(value) = \"\""))
1445 for (std::set<int64>::const_iterator it = ids.begin(); it != ids.end();
1447 sql::Statement s(db_->GetUniqueStatement(
1448 "DELETE FROM autofill_dates WHERE pair_id = ?"));
1449 s.BindInt64(0, *it);
1457 // Add the card_number_encrypted column if credit card table was not
1458 // created in this build (otherwise the column already exists).
1459 // WARNING: Do not change the order of the execution of the SQL
1460 // statements in this case! Profile corruption and data migration
1461 // issues WILL OCCUR. See http://crbug.com/10913
1463 // The problem is that if a user has a profile which was created before
1464 // r37036, when the credit_cards table was added, and then failed to
1465 // update this profile between the credit card addition and the addition
1466 // of the "encrypted" columns (44963), the next data migration will put
1467 // the user's profile in an incoherent state: The user will update from
1468 // a data profile set to be earlier than 22, and therefore pass through
1469 // this update case. But because the user did not have a credit_cards
1470 // table before starting Chrome, it will have just been initialized
1471 // above, and so already have these columns -- and thus this data
1472 // update step will have failed.
1474 // The false assumption in this case is that at this step in the
1475 // migration, the user has a credit card table, and that this
1476 // table does not include encrypted columns!
1477 // Because this case does not roll back the complete set of SQL
1478 // transactions properly in case of failure (that is, it does not
1479 // roll back the table initialization done above), the incoherent
1480 // profile will now see itself as being at version 22 -- but include a
1481 // fully initialized credit_cards table. Every time Chrome runs, it
1482 // will try to update the web database and fail at this step, unless
1483 // we allow for the faulty assumption described above by checking for
1484 // the existence of the columns only AFTER we've executed the commands
1486 bool AutofillTable::MigrateToVersion23AddCardNumberEncryptedColumn() {
1487 if (!db_->DoesTableExist("autofill_profiles") &&
1488 (!db_->Execute("CREATE TABLE autofill_profiles ( "
1490 "unique_id INTEGER PRIMARY KEY, "
1491 "first_name VARCHAR, "
1492 "middle_name VARCHAR, "
1493 "last_name VARCHAR, "
1495 "company_name VARCHAR, "
1496 "address_line_1 VARCHAR, "
1497 "address_line_2 VARCHAR, "
1504 !db_->Execute("CREATE INDEX autofill_profiles_label_index"
1505 " ON autofill_profiles (label)")))
1508 if (!db_->DoesTableExist("credit_cards") &&
1509 (!db_->Execute("CREATE TABLE credit_cards ( "
1511 "unique_id INTEGER PRIMARY KEY, "
1512 "name_on_card VARCHAR, "
1514 "card_number VARCHAR, "
1515 "expiration_month INTEGER, "
1516 "expiration_year INTEGER, "
1517 "verification_code VARCHAR, "
1518 "billing_address VARCHAR, "
1519 "shipping_address VARCHAR)") ||
1520 !db_->Execute("CREATE INDEX credit_cards_label_index"
1521 " ON credit_cards (label)")))
1524 if (!db_->DoesColumnExist("credit_cards", "card_number_encrypted")) {
1525 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN "
1526 "card_number_encrypted BLOB DEFAULT NULL")) {
1531 if (!db_->DoesColumnExist("credit_cards", "verification_code_encrypted")) {
1532 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN "
1533 "verification_code_encrypted BLOB DEFAULT NULL")) {
1541 // One-time cleanup for http://crbug.com/38364 - In the presence of
1542 // multi-byte UTF-8 characters, that bug could cause Autofill strings
1543 // to grow larger and more corrupt with each save. The cleanup removes
1544 // any row with a string field larger than a reasonable size. The string
1545 // fields examined here are precisely the ones that were subject to
1546 // corruption by the original bug.
1547 bool AutofillTable::MigrateToVersion24CleanupOversizedStringFields() {
1548 const std::string autofill_is_too_big =
1549 "max(length(name), length(value)) > 500";
1551 const std::string credit_cards_is_too_big =
1552 "max(length(label), length(name_on_card), length(type), "
1553 " length(expiration_month), length(expiration_year), "
1554 " length(billing_address), length(shipping_address) "
1557 const std::string autofill_profiles_is_too_big =
1558 "max(length(label), length(first_name), "
1559 " length(middle_name), length(last_name), length(email), "
1560 " length(company_name), length(address_line_1), "
1561 " length(address_line_2), length(city), length(state), "
1562 " length(zipcode), length(country), length(phone)) > 500";
1564 std::string query = "DELETE FROM autofill_dates WHERE pair_id IN ("
1565 "SELECT pair_id FROM autofill WHERE " + autofill_is_too_big + ")";
1567 if (!db_->Execute(query.c_str()))
1570 query = "DELETE FROM autofill WHERE " + autofill_is_too_big;
1572 if (!db_->Execute(query.c_str()))
1575 // Only delete from legacy credit card tables where specific columns exist.
1576 if (db_->DoesColumnExist("credit_cards", "label") &&
1577 db_->DoesColumnExist("credit_cards", "name_on_card") &&
1578 db_->DoesColumnExist("credit_cards", "type") &&
1579 db_->DoesColumnExist("credit_cards", "expiration_month") &&
1580 db_->DoesColumnExist("credit_cards", "expiration_year") &&
1581 db_->DoesColumnExist("credit_cards", "billing_address") &&
1582 db_->DoesColumnExist("credit_cards", "shipping_address") &&
1583 db_->DoesColumnExist("autofill_profiles", "label")) {
1584 query = "DELETE FROM credit_cards WHERE (" + credit_cards_is_too_big +
1585 ") OR label IN (SELECT label FROM autofill_profiles WHERE " +
1586 autofill_profiles_is_too_big + ")";
1588 if (!db_->Execute(query.c_str()))
1592 if (db_->DoesColumnExist("autofill_profiles", "label")) {
1593 query = "DELETE FROM autofill_profiles WHERE " +
1594 autofill_profiles_is_too_big;
1596 if (!db_->Execute(query.c_str()))
1603 // Change the credit_cards.billing_address column from a string to an
1604 // int. The stored string is the label of an address, so we have to
1605 // select the unique ID of this address using the label as a foreign
1606 // key into the |autofill_profiles| table.
1607 bool AutofillTable::MigrateToVersion27UpdateLegacyCreditCards() {
1608 // Only migrate from legacy credit card tables where specific columns
1610 if (!(db_->DoesColumnExist("credit_cards", "unique_id") &&
1611 db_->DoesColumnExist("credit_cards", "billing_address") &&
1612 db_->DoesColumnExist("autofill_profiles", "unique_id"))) {
1617 "SELECT credit_cards.unique_id, autofill_profiles.unique_id "
1618 "FROM autofill_profiles, credit_cards "
1619 "WHERE credit_cards.billing_address = autofill_profiles.label";
1620 sql::Statement s(db_->GetUniqueStatement(stmt.c_str()));
1622 std::map<int, int> cc_billing_map;
1624 cc_billing_map[s.ColumnInt(0)] = s.ColumnInt(1);
1628 // Windows already stores the IDs as strings in |billing_address|. Try
1629 // to convert those.
1630 if (cc_billing_map.empty()) {
1631 std::string stmt = "SELECT unique_id,billing_address FROM credit_cards";
1632 sql::Statement s(db_->GetUniqueStatement(stmt.c_str()));
1636 if (base::StringToInt(s.ColumnString(1), &id))
1637 cc_billing_map[s.ColumnInt(0)] = id;
1643 if (!db_->Execute("CREATE TABLE credit_cards_temp ( "
1645 "unique_id INTEGER PRIMARY KEY, "
1646 "name_on_card VARCHAR, "
1648 "card_number VARCHAR, "
1649 "expiration_month INTEGER, "
1650 "expiration_year INTEGER, "
1651 "verification_code VARCHAR, "
1652 "billing_address INTEGER, "
1653 "shipping_address VARCHAR, "
1654 "card_number_encrypted BLOB, "
1655 "verification_code_encrypted BLOB)")) {
1660 "INSERT INTO credit_cards_temp "
1661 "SELECT label,unique_id,name_on_card,type,card_number,"
1662 "expiration_month,expiration_year,verification_code,0,"
1663 "shipping_address,card_number_encrypted,"
1664 "verification_code_encrypted FROM credit_cards")) {
1668 if (!db_->Execute("DROP TABLE credit_cards"))
1671 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards"))
1674 for (std::map<int, int>::const_iterator iter = cc_billing_map.begin();
1675 iter != cc_billing_map.end(); ++iter) {
1676 sql::Statement s(db_->GetCachedStatement(
1678 "UPDATE credit_cards SET billing_address=? WHERE unique_id=?"));
1679 s.BindInt(0, (*iter).second);
1680 s.BindInt(1, (*iter).first);
1689 bool AutofillTable::MigrateToVersion30AddDateModifed() {
1690 // Add date_modified to autofill_profiles.
1691 if (!db_->DoesColumnExist("autofill_profiles", "date_modified")) {
1692 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN "
1693 "date_modified INTEGER NON NULL DEFAULT 0")) {
1697 sql::Statement s(db_->GetUniqueStatement(
1698 "UPDATE autofill_profiles SET date_modified=?"));
1699 s.BindInt64(0, Time::Now().ToTimeT());
1705 // Add date_modified to credit_cards.
1706 if (!db_->DoesColumnExist("credit_cards", "date_modified")) {
1707 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN "
1708 "date_modified INTEGER NON NULL DEFAULT 0")) {
1712 sql::Statement s(db_->GetUniqueStatement(
1713 "UPDATE credit_cards SET date_modified=?"));
1714 s.BindInt64(0, Time::Now().ToTimeT());
1723 bool AutofillTable::MigrateToVersion31AddGUIDToCreditCardsAndProfiles() {
1724 // Note that we need to check for the guid column's existence due to the
1725 // fact that for a version 22 database the |autofill_profiles| table
1726 // gets created fresh with |InitAutofillProfilesTable|.
1727 if (!db_->DoesColumnExist("autofill_profiles", "guid")) {
1728 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN "
1729 "guid VARCHAR NOT NULL DEFAULT \"\"")) {
1733 // Set all the |guid| fields to valid values.
1735 sql::Statement s(db_->GetUniqueStatement("SELECT unique_id "
1736 "FROM autofill_profiles"));
1739 sql::Statement update_s(
1740 db_->GetUniqueStatement("UPDATE autofill_profiles "
1741 "SET guid=? WHERE unique_id=?"));
1742 update_s.BindString(0, base::GenerateGUID());
1743 update_s.BindInt(1, s.ColumnInt(0));
1745 if (!update_s.Run())
1752 // Note that we need to check for the guid column's existence due to the
1753 // fact that for a version 22 database the |autofill_profiles| table
1754 // gets created fresh with |InitAutofillProfilesTable|.
1755 if (!db_->DoesColumnExist("credit_cards", "guid")) {
1756 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN "
1757 "guid VARCHAR NOT NULL DEFAULT \"\"")) {
1761 // Set all the |guid| fields to valid values.
1763 sql::Statement s(db_->GetUniqueStatement("SELECT unique_id "
1764 "FROM credit_cards"));
1767 sql::Statement update_s(
1768 db_->GetUniqueStatement("UPDATE credit_cards "
1769 "set guid=? WHERE unique_id=?"));
1770 update_s.BindString(0, base::GenerateGUID());
1771 update_s.BindInt(1, s.ColumnInt(0));
1773 if (!update_s.Run())
1783 bool AutofillTable::MigrateToVersion32UpdateProfilesAndCreditCards() {
1784 if (db_->DoesColumnExist("autofill_profiles", "unique_id")) {
1785 if (!db_->Execute("CREATE TABLE autofill_profiles_temp ( "
1786 "guid VARCHAR PRIMARY KEY, "
1788 "first_name VARCHAR, "
1789 "middle_name VARCHAR, "
1790 "last_name VARCHAR, "
1792 "company_name VARCHAR, "
1793 "address_line_1 VARCHAR, "
1794 "address_line_2 VARCHAR, "
1800 "date_modified INTEGER NOT NULL DEFAULT 0)")) {
1805 "INSERT INTO autofill_profiles_temp "
1806 "SELECT guid, label, first_name, middle_name, last_name, email, "
1807 "company_name, address_line_1, address_line_2, city, state, "
1808 "zipcode, country, phone, date_modified "
1809 "FROM autofill_profiles")) {
1813 if (!db_->Execute("DROP TABLE autofill_profiles"))
1817 "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) {
1822 if (db_->DoesColumnExist("credit_cards", "unique_id")) {
1823 if (!db_->Execute("CREATE TABLE credit_cards_temp ( "
1824 "guid VARCHAR PRIMARY KEY, "
1826 "name_on_card VARCHAR, "
1827 "expiration_month INTEGER, "
1828 "expiration_year INTEGER, "
1829 "card_number_encrypted BLOB, "
1830 "date_modified INTEGER NOT NULL DEFAULT 0)")) {
1835 "INSERT INTO credit_cards_temp "
1836 "SELECT guid, label, name_on_card, expiration_month, "
1837 "expiration_year, card_number_encrypted, date_modified "
1838 "FROM credit_cards")) {
1842 if (!db_->Execute("DROP TABLE credit_cards"))
1845 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards"))
1852 // Test the existence of the |first_name| column as an indication that
1853 // we need a migration. It is possible that the new |autofill_profiles|
1854 // schema is in place because the table was newly created when migrating
1855 // from a pre-version-22 database.
1856 bool AutofillTable::MigrateToVersion33ProfilesBasedOnFirstName() {
1857 if (!db_->DoesTableExist("autofill_profile_names") &&
1858 !db_->Execute("CREATE TABLE autofill_profile_names ( "
1860 "first_name VARCHAR, "
1861 "middle_name VARCHAR, "
1862 "last_name VARCHAR)"))
1865 if (!db_->DoesTableExist("autofill_profile_emails") &&
1866 !db_->Execute("CREATE TABLE autofill_profile_emails ( "
1871 if (!db_->DoesTableExist("autofill_profile_phones") &&
1872 !db_->Execute("CREATE TABLE autofill_profile_phones ( "
1874 "type INTEGER DEFAULT 0, "
1878 if (db_->DoesColumnExist("autofill_profiles", "first_name")) {
1879 // Create autofill_profiles_temp table that will receive the data.
1880 if (!db_->DoesTableExist("autofill_profiles_temp")) {
1881 if (!db_->Execute("CREATE TABLE autofill_profiles_temp ( "
1882 "guid VARCHAR PRIMARY KEY, "
1883 "company_name VARCHAR, "
1884 "address_line_1 VARCHAR, "
1885 "address_line_2 VARCHAR, "
1890 "date_modified INTEGER NOT NULL DEFAULT 0)")) {
1895 sql::Statement s(db_->GetUniqueStatement(
1896 "SELECT guid, first_name, middle_name, last_name, email, "
1897 "company_name, address_line_1, address_line_2, city, state, "
1898 "zipcode, country, phone, date_modified "
1899 "FROM autofill_profiles"));
1902 AutofillProfile profile;
1904 profile.set_guid(s.ColumnString(index++));
1905 DCHECK(base::IsValidGUID(profile.guid()));
1907 profile.SetRawInfo(NAME_FIRST, s.ColumnString16(index++));
1908 profile.SetRawInfo(NAME_MIDDLE, s.ColumnString16(index++));
1909 profile.SetRawInfo(NAME_LAST, s.ColumnString16(index++));
1910 profile.SetRawInfo(EMAIL_ADDRESS, s.ColumnString16(index++));
1911 profile.SetRawInfo(COMPANY_NAME, s.ColumnString16(index++));
1912 profile.SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(index++));
1913 profile.SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(index++));
1914 profile.SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++));
1915 profile.SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++));
1916 profile.SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++));
1917 profile.SetInfo(AutofillType(ADDRESS_HOME_COUNTRY),
1918 s.ColumnString16(index++), app_locale_);
1919 profile.SetRawInfo(PHONE_HOME_WHOLE_NUMBER, s.ColumnString16(index++));
1920 int64 date_modified = s.ColumnInt64(index++);
1922 sql::Statement s_insert(db_->GetUniqueStatement(
1923 "INSERT INTO autofill_profiles_temp"
1924 "(guid, company_name, address_line_1, address_line_2, city,"
1925 " state, zipcode, country, date_modified)"
1926 "VALUES (?,?,?,?,?,?,?,?,?)"));
1928 s_insert.BindString(index++, profile.guid());
1929 s_insert.BindString16(index++, profile.GetRawInfo(COMPANY_NAME));
1930 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_LINE1));
1931 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_LINE2));
1932 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_CITY));
1933 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_STATE));
1934 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_ZIP));
1935 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_COUNTRY));
1936 s_insert.BindInt64(index++, date_modified);
1938 if (!s_insert.Run())
1941 // Add the other bits: names, emails, and phone numbers.
1942 if (!AddAutofillProfileNamesForVersion3x(profile, db_) ||
1943 !AddAutofillProfileEmails(profile, db_) ||
1944 !AddAutofillProfilePhones(profile, db_)) {
1951 if (!db_->Execute("DROP TABLE autofill_profiles"))
1955 "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) {
1960 // Remove the labels column from the credit_cards table.
1961 if (db_->DoesColumnExist("credit_cards", "label")) {
1962 if (!db_->Execute("CREATE TABLE credit_cards_temp ( "
1963 "guid VARCHAR PRIMARY KEY, "
1964 "name_on_card VARCHAR, "
1965 "expiration_month INTEGER, "
1966 "expiration_year INTEGER, "
1967 "card_number_encrypted BLOB, "
1968 "date_modified INTEGER NOT NULL DEFAULT 0)")) {
1973 "INSERT INTO credit_cards_temp "
1974 "SELECT guid, name_on_card, expiration_month, "
1975 "expiration_year, card_number_encrypted, date_modified "
1976 "FROM credit_cards")) {
1980 if (!db_->Execute("DROP TABLE credit_cards"))
1983 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards"))
1990 // Test the existence of the |country_code| column as an indication that
1991 // we need a migration. It is possible that the new |autofill_profiles|
1992 // schema is in place because the table was newly created when migrating
1993 // from a pre-version-22 database.
1994 bool AutofillTable::MigrateToVersion34ProfilesBasedOnCountryCode() {
1995 if (!db_->DoesColumnExist("autofill_profiles", "country_code")) {
1996 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN "
1997 "country_code VARCHAR")) {
2001 // Set all the |country_code| fields to match existing |country| values.
2002 sql::Statement s(db_->GetUniqueStatement("SELECT guid, country "
2003 "FROM autofill_profiles"));
2006 sql::Statement update_s(
2007 db_->GetUniqueStatement("UPDATE autofill_profiles "
2008 "SET country_code=? WHERE guid=?"));
2010 base::string16 country = s.ColumnString16(1);
2011 update_s.BindString(0, AutofillCountry::GetCountryCode(country,
2013 update_s.BindString(1, s.ColumnString(0));
2015 if (!update_s.Run())
2025 // Correct all country codes with value "UK" to be "GB". This data
2026 // was mistakenly introduced in build 686.0. This migration is to clean
2027 // it up. See http://crbug.com/74511 for details.
2028 bool AutofillTable::MigrateToVersion35GreatBritainCountryCodes() {
2029 sql::Statement s(db_->GetUniqueStatement(
2030 "UPDATE autofill_profiles SET country_code=\"GB\" "
2031 "WHERE country_code=\"UK\""));
2036 // Merge and cull older profiles where possible.
2037 bool AutofillTable::MigrateToVersion37MergeAndCullOlderProfiles() {
2038 if (!db_->DoesTableExist("autofill_profiles_trash") &&
2039 !db_->Execute("CREATE TABLE autofill_profiles_trash (guid VARCHAR)"))
2042 sql::Statement s(db_->GetUniqueStatement(
2043 "SELECT guid, date_modified FROM autofill_profiles"));
2045 // Accumulate the good profiles.
2046 std::vector<AutofillProfile> accumulated_profiles;
2047 std::vector<AutofillProfile*> accumulated_profiles_p;
2048 std::map<std::string, int64> modification_map;
2050 std::string guid = s.ColumnString(0);
2051 int64 date_modified = s.ColumnInt64(1);
2052 modification_map.insert(
2053 std::pair<std::string, int64>(guid, date_modified));
2055 sql::Statement s(db_->GetUniqueStatement(
2056 "SELECT guid, company_name, address_line_1, address_line_2, city, "
2057 " state, zipcode, country, country_code, date_modified "
2058 "FROM autofill_profiles "
2060 s.BindString(0, guid);
2065 scoped_ptr<AutofillProfile> profile(new AutofillProfile);
2067 profile->set_guid(s.ColumnString(index++));
2068 DCHECK(base::IsValidGUID(profile->guid()));
2070 profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(index++));
2071 profile->SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(index++));
2072 profile->SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(index++));
2073 profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++));
2074 profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++));
2075 profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++));
2076 // Intentionally skip column 7, which stores the localized country name.
2078 profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(index++));
2079 // Intentionally skip column 9, which stores the profile's modification
2082 profile->set_origin(s.ColumnString(index++));
2084 // Get associated name info.
2085 AddAutofillProfileNamesToProfileForVersion37(db_, profile.get());
2087 // Get associated email info.
2088 AddAutofillProfileEmailsToProfile(db_, profile.get());
2090 // Get associated phone info.
2091 AddAutofillProfilePhonesToProfile(db_, profile.get());
2093 if (PersonalDataManager::IsValidLearnableProfile(*profile, app_locale_)) {
2094 std::vector<AutofillProfile> merged_profiles;
2095 std::string merged_guid = PersonalDataManager::MergeProfile(
2096 *profile, accumulated_profiles_p, app_locale_, &merged_profiles);
2098 std::swap(accumulated_profiles, merged_profiles);
2100 accumulated_profiles_p.clear();
2101 accumulated_profiles_p.resize(accumulated_profiles.size());
2102 std::transform(accumulated_profiles.begin(),
2103 accumulated_profiles.end(),
2104 accumulated_profiles_p.begin(),
2105 address_of<AutofillProfile>);
2107 // If the profile got merged trash the original.
2108 if (merged_guid != profile->guid())
2109 AddAutofillGUIDToTrash(profile->guid());
2111 // An invalid profile, so trash it.
2112 AddAutofillGUIDToTrash(profile->guid());
2118 // Drop the current profiles.
2119 if (!ClearAutofillProfiles())
2122 // Add the newly merged profiles back in.
2123 for (std::vector<AutofillProfile>::const_iterator
2124 iter = accumulated_profiles.begin();
2125 iter != accumulated_profiles.end();
2127 // Save the profile with its original modification date.
2128 std::map<std::string, int64>::const_iterator date_item =
2129 modification_map.find(iter->guid());
2130 if (date_item == modification_map.end())
2133 sql::Statement s(db_->GetUniqueStatement(
2134 "INSERT INTO autofill_profiles"
2135 "(guid, company_name, address_line_1, address_line_2, city, state,"
2136 " zipcode, country, country_code, date_modified)"
2137 "VALUES (?,?,?,?,?,?,?,?,?,?)"));
2139 s.BindString(index++, iter->guid());
2140 s.BindString16(index++, GetInfo(*iter, COMPANY_NAME));
2141 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_LINE1));
2142 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_LINE2));
2143 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_CITY));
2144 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_STATE));
2145 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_ZIP));
2146 s.BindString16(index++, base::string16()); // This column is deprecated.
2147 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_COUNTRY));
2148 s.BindInt64(index++, date_item->second);
2153 if (!AddAutofillProfileNamesForVersion3x(*iter, db_) ||
2154 !AddAutofillProfileEmails(*iter, db_) ||
2155 !AddAutofillProfilePhones(*iter, db_)) {
2163 bool AutofillTable::MigrateToVersion51AddOriginColumn() {
2164 sql::Transaction transaction(db_);
2165 if (!transaction.Begin())
2168 // Add origin to autofill_profiles.
2169 if (!db_->DoesColumnExist("autofill_profiles", "origin") &&
2170 !db_->Execute("ALTER TABLE autofill_profiles "
2171 "ADD COLUMN origin VARCHAR DEFAULT ''")) {
2175 // Add origin to credit_cards.
2176 if (!db_->DoesColumnExist("credit_cards", "origin") &&
2177 !db_->Execute("ALTER TABLE credit_cards "
2178 "ADD COLUMN origin VARCHAR DEFAULT ''")) {
2182 return transaction.Commit();
2185 bool AutofillTable::MigrateToVersion54AddI18nFieldsAndRemoveDeprecatedFields() {
2186 sql::Transaction transaction(db_);
2187 if (!transaction.Begin())
2190 // Test the existence of the |address_line_1| column as an indication that a
2191 // migration is needed. It is possible that the new |autofill_profile_phones|
2192 // schema is in place because the table was newly created when migrating from
2193 // a pre-version-23 database.
2194 if (db_->DoesColumnExist("autofill_profiles", "address_line_1")) {
2195 // Create a temporary copy of the autofill_profiles table in the (newer)
2196 // version 54 format. This table
2197 // (a) adds columns for street_address, dependent_locality, and
2199 // (b) removes the address_line_1 and address_line_2 columns, which are
2200 // replaced by the street_address column, and
2201 // (c) removes the country column, which was long deprecated.
2202 if (db_->DoesTableExist("autofill_profiles_temp") ||
2203 !db_->Execute("CREATE TABLE autofill_profiles_temp ( "
2204 "guid VARCHAR PRIMARY KEY, "
2205 "company_name VARCHAR, "
2206 "street_address VARCHAR, "
2207 "dependent_locality VARCHAR, "
2211 "sorting_code VARCHAR, "
2212 "country_code VARCHAR, "
2213 "date_modified INTEGER NOT NULL DEFAULT 0, "
2214 "origin VARCHAR DEFAULT '')")) {
2218 // Copy over the data from the autofill_profiles table, taking care to merge
2219 // the address lines 1 and 2 into the new street_address column.
2220 if (!db_->Execute("INSERT INTO autofill_profiles_temp "
2221 "SELECT guid, company_name, '', '', city, state, zipcode,"
2222 " '', country_code, date_modified, origin "
2223 "FROM autofill_profiles")) {
2226 sql::Statement s(db_->GetUniqueStatement(
2227 "SELECT guid, address_line_1, address_line_2 FROM autofill_profiles"));
2229 std::string guid = s.ColumnString(0);
2230 base::string16 line1 = s.ColumnString16(1);
2231 base::string16 line2 = s.ColumnString16(2);
2232 base::string16 street_address = line1;
2234 street_address += base::ASCIIToUTF16("\n") + line2;
2236 sql::Statement s_update(db_->GetUniqueStatement(
2237 "UPDATE autofill_profiles_temp SET street_address=? WHERE guid=?"));
2238 s_update.BindString16(0, street_address);
2239 s_update.BindString(1, guid);
2240 if (!s_update.Run())
2246 // Delete the existing (version 53) table and replace it with the contents
2247 // of the temporary table.
2248 if (!db_->Execute("DROP TABLE autofill_profiles") ||
2249 !db_->Execute("ALTER TABLE autofill_profiles_temp "
2250 "RENAME TO autofill_profiles")) {
2255 // Test the existence of the |type| column as an indication that a migration
2256 // is needed. It is possible that the new |autofill_profile_phones| schema is
2257 // in place because the table was newly created when migrating from a
2258 // pre-version-23 database.
2259 if (db_->DoesColumnExist("autofill_profile_phones", "type")) {
2260 // Create a temporary copy of the autofill_profile_phones table in the
2261 // (newer) version 54 format. This table removes the deprecated |type|
2263 if (db_->DoesTableExist("autofill_profile_phones_temp") ||
2264 !db_->Execute("CREATE TABLE autofill_profile_phones_temp ( "
2266 "number VARCHAR)")) {
2270 // Copy over the data from the autofill_profile_phones table.
2271 if (!db_->Execute("INSERT INTO autofill_profile_phones_temp "
2272 "SELECT guid, number FROM autofill_profile_phones")) {
2276 // Delete the existing (version 53) table and replace it with the contents
2277 // of the temporary table.
2278 if (!db_->Execute("DROP TABLE autofill_profile_phones"))
2280 if (!db_->Execute("ALTER TABLE autofill_profile_phones_temp "
2281 "RENAME TO autofill_profile_phones")) {
2286 return transaction.Commit();
2289 bool AutofillTable::MigrateToVersion55MergeAutofillDatesTable() {
2290 sql::Transaction transaction(db_);
2291 if (!transaction.Begin())
2294 if (db_->DoesTableExist("autofill_temp") ||
2295 !db_->Execute("CREATE TABLE autofill_temp ("
2298 "value_lower VARCHAR, "
2299 "date_created INTEGER DEFAULT 0, "
2300 "date_last_used INTEGER DEFAULT 0, "
2301 "count INTEGER DEFAULT 1, "
2302 "PRIMARY KEY (name, value))")) {
2306 // Slurp up the data from the existing table and write it to the new table.
2307 sql::Statement s(db_->GetUniqueStatement(
2308 "SELECT name, value, value_lower, count, MIN(date_created),"
2309 " MAX(date_created) "
2310 "FROM autofill a JOIN autofill_dates ad ON a.pair_id=ad.pair_id "
2311 "GROUP BY name, value, value_lower, count"));
2313 sql::Statement s_insert(db_->GetUniqueStatement(
2314 "INSERT INTO autofill_temp "
2315 "(name, value, value_lower, count, date_created, date_last_used) "
2316 "VALUES (?, ?, ?, ?, ?, ?)"));
2317 s_insert.BindString16(0, s.ColumnString16(0));
2318 s_insert.BindString16(1, s.ColumnString16(1));
2319 s_insert.BindString16(2, s.ColumnString16(2));
2320 s_insert.BindInt(3, s.ColumnInt(3));
2321 s_insert.BindInt64(4, s.ColumnInt64(4));
2322 s_insert.BindInt64(5, s.ColumnInt64(5));
2323 if (!s_insert.Run())
2330 // Delete the existing (version 54) tables and replace them with the contents
2331 // of the temporary table.
2332 if (!db_->Execute("DROP TABLE autofill") ||
2333 !db_->Execute("DROP TABLE autofill_dates") ||
2334 !db_->Execute("ALTER TABLE autofill_temp "
2335 "RENAME TO autofill")) {
2339 // Create indices on the new table, for fast lookups.
2340 if (!db_->Execute("CREATE INDEX autofill_name ON autofill (name)") ||
2341 !db_->Execute("CREATE INDEX autofill_name_value_lower ON "
2342 "autofill (name, value_lower)")) {
2347 return transaction.Commit();
2350 bool AutofillTable::MigrateToVersion56AddProfileLanguageCodeForFormatting() {
2351 return db_->Execute("ALTER TABLE autofill_profiles "
2352 "ADD COLUMN language_code VARCHAR");
2355 bool AutofillTable::MigrateToVersion57AddFullNameField() {
2356 return db_->Execute("ALTER TABLE autofill_profile_names "
2357 "ADD COLUMN full_name VARCHAR");
2360 } // namespace autofill