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