1 // Copyright 2016 The Chromium Authors. All rights reserved.
2 // Use of this source code is governed by a BSD-style license that can be
3 // found in the LICENSE file.
4 
5 #include "components/password_manager/core/browser/sql_table_builder.h"
6 
7 #include "base/bind.h"
8 #include "base/callback_helpers.h"
9 #include "base/logging.h"
10 #include "base/macros.h"
11 #include "base/test/mock_callback.h"
12 #include "sql/database.h"
13 #include "sql/statement.h"
14 #include "testing/gmock/include/gmock/gmock.h"
15 #include "testing/gtest/include/gtest/gtest.h"
16 
17 using ::testing::Return;
18 using ::testing::UnorderedElementsAre;
19 
20 namespace password_manager {
21 
22 class SQLTableBuilderTest : public testing::Test {
23  public:
SQLTableBuilderTest()24   SQLTableBuilderTest() : builder_("my_logins_table") { Init(); }
25 
26   ~SQLTableBuilderTest() override = default;
27 
28  protected:
29   // Checks whether a column with a given |name| is listed with the given
30   // |type| in the database.
31   bool IsColumnOfType(const std::string& name, const std::string& type);
32 
db()33   sql::Database* db() { return &db_; }
34 
builder()35   SQLTableBuilder* builder() { return &builder_; }
36 
37  private:
38   // Part of constructor, needs to be a void-returning function to use ASSERTs.
39   void Init();
40 
41   // Error handler for the SQL connection, prints the error code and the
42   // statement details.
43   void PrintDBError(int code, sql::Statement* statement);
44 
45   sql::Database db_;
46   SQLTableBuilder builder_;
47 
48   DISALLOW_COPY_AND_ASSIGN(SQLTableBuilderTest);
49 };
50 
IsColumnOfType(const std::string & name,const std::string & type)51 bool SQLTableBuilderTest::IsColumnOfType(const std::string& name,
52                                          const std::string& type) {
53   return db()->GetSchema().find(name + " " + type) != std::string::npos;
54 }
55 
Init()56 void SQLTableBuilderTest::Init() {
57   db_.set_error_callback(base::BindRepeating(&SQLTableBuilderTest::PrintDBError,
58                                              base::Unretained(this)));
59   ASSERT_TRUE(db_.OpenInMemory());
60   // The following column must always be present, so let's add it here.
61   builder_.AddColumnToUniqueKey("signon_realm", "VARCHAR NOT NULL");
62 }
63 
PrintDBError(int code,sql::Statement * statement)64 void SQLTableBuilderTest::PrintDBError(int code, sql::Statement* statement) {
65   VLOG(0) << "DB error encountered, code = " << code;
66   if (statement) {
67     VLOG(0) << "statement string = " << statement->GetSQLStatement();
68     VLOG(0) << "statement is " << (statement->is_valid() ? "valid" : "invalid");
69   }
70 }
71 
TEST_F(SQLTableBuilderTest,SealVersion_0)72 TEST_F(SQLTableBuilderTest, SealVersion_0) {
73   EXPECT_EQ(0u, builder()->SealVersion());
74   EXPECT_TRUE(builder()->MigrateFrom(0, db()));
75   EXPECT_TRUE(builder()->CreateTable(db()));
76   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
77   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "signon_realm"));
78   EXPECT_TRUE(IsColumnOfType("signon_realm", "VARCHAR NOT NULL"));
79 }
80 
TEST_F(SQLTableBuilderTest,AddColumn)81 TEST_F(SQLTableBuilderTest, AddColumn) {
82   builder()->AddColumn("password_value", "BLOB");
83   EXPECT_EQ(0u, builder()->SealVersion());
84   EXPECT_TRUE(builder()->MigrateFrom(0, db()));
85   EXPECT_TRUE(builder()->CreateTable(db()));
86   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
87   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "signon_realm"));
88   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "password_value"));
89   EXPECT_TRUE(IsColumnOfType("password_value", "BLOB"));
90 }
91 
TEST_F(SQLTableBuilderTest,AddIndex)92 TEST_F(SQLTableBuilderTest, AddIndex) {
93   builder()->AddIndex("my_logins_table_signon", {"signon_realm"});
94   EXPECT_EQ(0u, builder()->SealVersion());
95   EXPECT_TRUE(builder()->MigrateFrom(0, db()));
96   EXPECT_TRUE(builder()->CreateTable(db()));
97   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
98   EXPECT_TRUE(db()->DoesIndexExist("my_logins_table_signon"));
99 }
100 
TEST_F(SQLTableBuilderTest,AddIndexOnMultipleColumns)101 TEST_F(SQLTableBuilderTest, AddIndexOnMultipleColumns) {
102   builder()->AddColumn("column_1", "BLOB");
103   builder()->AddColumn("column_2", "BLOB");
104   builder()->AddIndex("my_index", {"column_1", "column_2"});
105   EXPECT_EQ(0u, builder()->SealVersion());
106   EXPECT_TRUE(builder()->MigrateFrom(0, db()));
107   EXPECT_TRUE(builder()->CreateTable(db()));
108   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
109   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "column_1"));
110   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "column_2"));
111   EXPECT_TRUE(db()->DoesIndexExist("my_index"));
112 }
113 
TEST_F(SQLTableBuilderTest,RenameColumn_InSameVersion)114 TEST_F(SQLTableBuilderTest, RenameColumn_InSameVersion) {
115   builder()->AddColumn("old_name", "BLOB");
116   builder()->RenameColumn("old_name", "password_value");
117   EXPECT_EQ(0u, builder()->SealVersion());
118   EXPECT_TRUE(builder()->CreateTable(db()));
119   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
120   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "old_name"));
121   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "password_value"));
122   EXPECT_TRUE(IsColumnOfType("password_value", "BLOB"));
123 }
124 
TEST_F(SQLTableBuilderTest,RenameColumn_InNextVersion)125 TEST_F(SQLTableBuilderTest, RenameColumn_InNextVersion) {
126   builder()->AddColumn("old_name", "BLOB");
127   EXPECT_EQ(0u, builder()->SealVersion());
128   builder()->RenameColumn("old_name", "password_value");
129   EXPECT_EQ(1u, builder()->SealVersion());
130   EXPECT_TRUE(builder()->CreateTable(db()));
131   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
132   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "old_name"));
133   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "password_value"));
134   EXPECT_TRUE(IsColumnOfType("password_value", "BLOB"));
135 }
136 
TEST_F(SQLTableBuilderTest,RenameColumn_SameNameInSameVersion)137 TEST_F(SQLTableBuilderTest, RenameColumn_SameNameInSameVersion) {
138   builder()->AddColumn("name", "BLOB");
139   builder()->RenameColumn("name", "name");
140   EXPECT_EQ(0u, builder()->SealVersion());
141   EXPECT_TRUE(builder()->CreateTable(db()));
142   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
143   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "name"));
144   EXPECT_TRUE(IsColumnOfType("name", "BLOB"));
145 }
146 
TEST_F(SQLTableBuilderTest,RenameColumn_SameNameInNextVersion)147 TEST_F(SQLTableBuilderTest, RenameColumn_SameNameInNextVersion) {
148   builder()->AddColumn("name", "BLOB");
149   EXPECT_EQ(0u, builder()->SealVersion());
150   builder()->RenameColumn("name", "name");
151   EXPECT_EQ(1u, builder()->SealVersion());
152   EXPECT_TRUE(builder()->CreateTable(db()));
153   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
154   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "name"));
155   EXPECT_TRUE(IsColumnOfType("name", "BLOB"));
156 }
157 
TEST_F(SQLTableBuilderTest,DropColumn_InSameVersion)158 TEST_F(SQLTableBuilderTest, DropColumn_InSameVersion) {
159   builder()->AddColumn("password_value", "BLOB");
160   builder()->DropColumn("password_value");
161   EXPECT_EQ(0u, builder()->SealVersion());
162   EXPECT_TRUE(builder()->CreateTable(db()));
163   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
164   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "password_value"));
165 }
166 
TEST_F(SQLTableBuilderTest,DropColumn_InNextVersion)167 TEST_F(SQLTableBuilderTest, DropColumn_InNextVersion) {
168   builder()->AddColumn("password_value", "BLOB");
169   EXPECT_EQ(0u, builder()->SealVersion());
170   builder()->DropColumn("password_value");
171   EXPECT_EQ(1u, builder()->SealVersion());
172   EXPECT_TRUE(builder()->CreateTable(db()));
173   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
174   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "password_value"));
175 }
176 
TEST_F(SQLTableBuilderTest,MigrateFrom)177 TEST_F(SQLTableBuilderTest, MigrateFrom) {
178   // First, create a table at version 0, with some columns.
179   builder()->AddColumn("for_renaming", "INTEGER DEFAULT 100");
180   builder()->AddColumn("for_deletion", "INTEGER");
181   builder()->AddIndex("my_signon_index", {"signon_realm"});
182   EXPECT_EQ(0u, builder()->SealVersion());
183   EXPECT_TRUE(builder()->CreateTable(db()));
184   EXPECT_TRUE(db()->DoesTableExist("my_logins_table"));
185   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "for_renaming"));
186   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "for_deletion"));
187   EXPECT_TRUE(db()->DoesIndexExist("my_signon_index"));
188   EXPECT_TRUE(
189       db()->Execute("INSERT INTO my_logins_table (signon_realm, for_renaming, "
190                     "for_deletion) VALUES ('abc', 123, 456)"));
191   const char retrieval[] = "SELECT * FROM my_logins_table";
192   sql::Statement first_check(
193       db()->GetCachedStatement(SQL_FROM_HERE, retrieval));
194   EXPECT_TRUE(first_check.Step());
195   EXPECT_EQ(3, first_check.ColumnCount());
196   EXPECT_EQ("abc", first_check.ColumnString(0));
197   EXPECT_EQ(123, first_check.ColumnInt(1));
198   EXPECT_EQ(456, first_check.ColumnInt(2));
199   EXPECT_FALSE(first_check.Step());
200   EXPECT_TRUE(first_check.Succeeded());
201 
202   // Now, specify some modifications for version 1.
203   builder()->RenameColumn("for_renaming", "renamed");
204   builder()->DropColumn("for_deletion");
205   builder()->AddColumn("new_column", "INTEGER DEFAULT 789");
206   builder()->AddIndex("my_changing_index_v1", {"renamed", "new_column"});
207   EXPECT_EQ(1u, builder()->SealVersion());
208 
209   // The migration should have the following effect:
210   // * The renamed column should keep its non-default value.
211   // * The succession of column removal and addition should not result in the
212   //   values from the deleted column to be copied to the added one.
213   // * Only the signon index and the second version of the changing index should
214   //   be present in the last version.
215   EXPECT_TRUE(builder()->MigrateFrom(0, db()));
216   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "for_renaming"));
217   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "for_deletion"));
218   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "renamed"));
219   EXPECT_TRUE(IsColumnOfType("renamed", "INTEGER"));
220   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "new_column"));
221   EXPECT_TRUE(db()->DoesIndexExist("my_signon_index"));
222   EXPECT_TRUE(db()->DoesIndexExist("my_changing_index_v1"));
223   sql::Statement second_check(
224       db()->GetCachedStatement(SQL_FROM_HERE, retrieval));
225   EXPECT_TRUE(second_check.Step());
226   EXPECT_EQ(3, second_check.ColumnCount());
227   EXPECT_EQ("abc", second_check.ColumnString(0));
228   EXPECT_EQ(123, second_check.ColumnInt(1));
229   EXPECT_EQ(789, second_check.ColumnInt(2));
230   EXPECT_FALSE(second_check.Step());
231   EXPECT_TRUE(second_check.Succeeded());
232 }
233 
TEST_F(SQLTableBuilderTest,MigrateFrom_RenameAndAddColumns)234 TEST_F(SQLTableBuilderTest, MigrateFrom_RenameAndAddColumns) {
235   builder()->AddPrimaryKeyColumn("id");
236   builder()->AddColumn("old_name", "INTEGER");
237   EXPECT_EQ(0u, builder()->SealVersion());
238 
239   EXPECT_TRUE(builder()->CreateTable(db()));
240 
241   builder()->RenameColumn("old_name", "new_name");
242   EXPECT_EQ(1u, builder()->SealVersion());
243 
244   builder()->AddColumn("added", "VARCHAR");
245   EXPECT_EQ(2u, builder()->SealVersion());
246 
247   EXPECT_TRUE(builder()->MigrateFrom(0, db()));
248   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "old_name"));
249   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "id"));
250   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "added"));
251   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "new_name"));
252   EXPECT_TRUE(IsColumnOfType("id", "INTEGER"));
253   EXPECT_TRUE(IsColumnOfType("added", "VARCHAR"));
254   EXPECT_TRUE(IsColumnOfType("new_name", "INTEGER"));
255   EXPECT_EQ(4u, builder()->NumberOfColumns());
256   EXPECT_EQ("signon_realm, id, new_name, added",
257             builder()->ListAllColumnNames());
258   EXPECT_EQ("new_name=?, added=?", builder()->ListAllNonuniqueKeyNames());
259   EXPECT_EQ("signon_realm=?", builder()->ListAllUniqueKeyNames());
260   EXPECT_THAT(builder()->AllPrimaryKeyNames(), UnorderedElementsAre("id"));
261 }
262 
TEST_F(SQLTableBuilderTest,MigrateFrom_RenameAndAddAndDropColumns)263 TEST_F(SQLTableBuilderTest, MigrateFrom_RenameAndAddAndDropColumns) {
264   builder()->AddPrimaryKeyColumn("pk_1");
265   builder()->AddColumnToUniqueKey("uni", "VARCHAR NOT NULL");
266   builder()->AddColumn("old_name", "INTEGER");
267   EXPECT_EQ(0u, builder()->SealVersion());
268 
269   EXPECT_TRUE(builder()->CreateTable(db()));
270 
271   builder()->RenameColumn("old_name", "new_name");
272   EXPECT_EQ(1u, builder()->SealVersion());
273 
274   builder()->AddColumn("added", "VARCHAR");
275   EXPECT_EQ(2u, builder()->SealVersion());
276 
277   builder()->DropColumn("added");
278   EXPECT_EQ(3u, builder()->SealVersion());
279 
280   EXPECT_TRUE(builder()->MigrateFrom(0, db()));
281   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "old_name"));
282   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "added"));
283   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "pk_1"));
284   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "uni"));
285   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "new_name"));
286   EXPECT_TRUE(IsColumnOfType("new_name", "INTEGER"));
287   EXPECT_EQ(4u, builder()->NumberOfColumns());
288   EXPECT_EQ("signon_realm, pk_1, uni, new_name",
289             builder()->ListAllColumnNames());
290   EXPECT_EQ("new_name=?", builder()->ListAllNonuniqueKeyNames());
291   EXPECT_EQ("signon_realm=? AND uni=?", builder()->ListAllUniqueKeyNames());
292 
293   EXPECT_THAT(builder()->AllPrimaryKeyNames(), UnorderedElementsAre("pk_1"));
294 }
295 
TEST_F(SQLTableBuilderTest,MigrateFrom_AddPrimaryKey)296 TEST_F(SQLTableBuilderTest, MigrateFrom_AddPrimaryKey) {
297   builder()->AddColumnToUniqueKey("uni", "VARCHAR NOT NULL");
298   EXPECT_EQ(0u, builder()->SealVersion());
299   EXPECT_TRUE(builder()->CreateTable(db()));
300 
301   builder()->AddPrimaryKeyColumn("pk_1");
302   EXPECT_EQ(1u, builder()->SealVersion());
303 
304   EXPECT_FALSE(db()->DoesColumnExist("my_logins_table", "pk_1"));
305   EXPECT_TRUE(db()->GetSchema().find("PRIMARY KEY (pk_1)") ==
306               std::string::npos);
307 
308   EXPECT_TRUE(builder()->MigrateFrom(0, db()));
309 
310   EXPECT_TRUE(db()->DoesColumnExist("my_logins_table", "pk_1"));
311   EXPECT_TRUE(
312       db()->GetSchema().find("pk_1 INTEGER PRIMARY KEY AUTOINCREMENT") !=
313       std::string::npos);
314 }
315 
TEST_F(SQLTableBuilderTest,MigrateFromWithSuccessfulCallback)316 TEST_F(SQLTableBuilderTest, MigrateFromWithSuccessfulCallback) {
317   EXPECT_EQ(0u, builder()->SealVersion());
318   EXPECT_EQ(1u, builder()->SealVersion());
319 
320   base::MockCallback<base::RepeatingCallback<bool(sql::Database*, unsigned)>>
321       migation_callback;
322 
323   EXPECT_CALL(migation_callback, Run(db(), 1u)).WillOnce(Return(true));
324   EXPECT_TRUE(builder()->MigrateFrom(0, db(), migation_callback.Get()));
325 }
326 
TEST_F(SQLTableBuilderTest,MigrateFromWithUnsuccessfulCallback)327 TEST_F(SQLTableBuilderTest, MigrateFromWithUnsuccessfulCallback) {
328   EXPECT_EQ(0u, builder()->SealVersion());
329   EXPECT_EQ(1u, builder()->SealVersion());
330 
331   base::MockCallback<base::RepeatingCallback<bool(sql::Database*, unsigned)>>
332       migation_callback;
333 
334   EXPECT_CALL(migation_callback, Run(db(), 1u)).WillOnce(Return(false));
335   EXPECT_FALSE(builder()->MigrateFrom(0, db(), migation_callback.Get()));
336 }
337 
338 }  // namespace password_manager
339