connection_unittest.cc revision eb525c5499e34cc9c4b825d6d9e75bb07cc06ace
1// Copyright (c) 2012 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 "base/file_util.h" 6#include "base/files/scoped_temp_dir.h" 7#include "base/logging.h" 8#include "sql/connection.h" 9#include "sql/meta_table.h" 10#include "sql/statement.h" 11#include "sql/test/scoped_error_ignorer.h" 12#include "testing/gtest/include/gtest/gtest.h" 13#include "third_party/sqlite/sqlite3.h" 14 15class SQLConnectionTest : public testing::Test { 16 public: 17 SQLConnectionTest() {} 18 19 virtual void SetUp() { 20 ASSERT_TRUE(temp_dir_.CreateUniqueTempDir()); 21 ASSERT_TRUE(db_.Open(db_path())); 22 } 23 24 virtual void TearDown() { 25 db_.Close(); 26 } 27 28 sql::Connection& db() { return db_; } 29 30 base::FilePath db_path() { 31 return temp_dir_.path().AppendASCII("SQLConnectionTest.db"); 32 } 33 34 private: 35 base::ScopedTempDir temp_dir_; 36 sql::Connection db_; 37}; 38 39TEST_F(SQLConnectionTest, Execute) { 40 // Valid statement should return true. 41 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)")); 42 EXPECT_EQ(SQLITE_OK, db().GetErrorCode()); 43 44 // Invalid statement should fail. 45 ASSERT_EQ(SQLITE_ERROR, 46 db().ExecuteAndReturnErrorCode("CREATE TAB foo (a, b")); 47 EXPECT_EQ(SQLITE_ERROR, db().GetErrorCode()); 48} 49 50TEST_F(SQLConnectionTest, ExecuteWithErrorCode) { 51 ASSERT_EQ(SQLITE_OK, 52 db().ExecuteAndReturnErrorCode("CREATE TABLE foo (a, b)")); 53 ASSERT_EQ(SQLITE_ERROR, 54 db().ExecuteAndReturnErrorCode("CREATE TABLE TABLE")); 55 ASSERT_EQ(SQLITE_ERROR, 56 db().ExecuteAndReturnErrorCode( 57 "INSERT INTO foo(a, b) VALUES (1, 2, 3, 4)")); 58} 59 60TEST_F(SQLConnectionTest, CachedStatement) { 61 sql::StatementID id1("foo", 12); 62 63 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)")); 64 ASSERT_TRUE(db().Execute("INSERT INTO foo(a, b) VALUES (12, 13)")); 65 66 // Create a new cached statement. 67 { 68 sql::Statement s(db().GetCachedStatement(id1, "SELECT a FROM foo")); 69 ASSERT_TRUE(s.is_valid()); 70 71 ASSERT_TRUE(s.Step()); 72 EXPECT_EQ(12, s.ColumnInt(0)); 73 } 74 75 // The statement should be cached still. 76 EXPECT_TRUE(db().HasCachedStatement(id1)); 77 78 { 79 // Get the same statement using different SQL. This should ignore our 80 // SQL and use the cached one (so it will be valid). 81 sql::Statement s(db().GetCachedStatement(id1, "something invalid(")); 82 ASSERT_TRUE(s.is_valid()); 83 84 ASSERT_TRUE(s.Step()); 85 EXPECT_EQ(12, s.ColumnInt(0)); 86 } 87 88 // Make sure other statements aren't marked as cached. 89 EXPECT_FALSE(db().HasCachedStatement(SQL_FROM_HERE)); 90} 91 92TEST_F(SQLConnectionTest, IsSQLValidTest) { 93 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)")); 94 ASSERT_TRUE(db().IsSQLValid("SELECT a FROM foo")); 95 ASSERT_FALSE(db().IsSQLValid("SELECT no_exist FROM foo")); 96} 97 98TEST_F(SQLConnectionTest, DoesStuffExist) { 99 // Test DoesTableExist. 100 EXPECT_FALSE(db().DoesTableExist("foo")); 101 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)")); 102 EXPECT_TRUE(db().DoesTableExist("foo")); 103 104 // Should be case sensitive. 105 EXPECT_FALSE(db().DoesTableExist("FOO")); 106 107 // Test DoesColumnExist. 108 EXPECT_FALSE(db().DoesColumnExist("foo", "bar")); 109 EXPECT_TRUE(db().DoesColumnExist("foo", "a")); 110 111 // Testing for a column on a nonexistent table. 112 EXPECT_FALSE(db().DoesColumnExist("bar", "b")); 113} 114 115TEST_F(SQLConnectionTest, GetLastInsertRowId) { 116 ASSERT_TRUE(db().Execute("CREATE TABLE foo (id INTEGER PRIMARY KEY, value)")); 117 118 ASSERT_TRUE(db().Execute("INSERT INTO foo (value) VALUES (12)")); 119 120 // Last insert row ID should be valid. 121 int64 row = db().GetLastInsertRowId(); 122 EXPECT_LT(0, row); 123 124 // It should be the primary key of the row we just inserted. 125 sql::Statement s(db().GetUniqueStatement("SELECT value FROM foo WHERE id=?")); 126 s.BindInt64(0, row); 127 ASSERT_TRUE(s.Step()); 128 EXPECT_EQ(12, s.ColumnInt(0)); 129} 130 131TEST_F(SQLConnectionTest, Rollback) { 132 ASSERT_TRUE(db().BeginTransaction()); 133 ASSERT_TRUE(db().BeginTransaction()); 134 EXPECT_EQ(2, db().transaction_nesting()); 135 db().RollbackTransaction(); 136 EXPECT_FALSE(db().CommitTransaction()); 137 EXPECT_TRUE(db().BeginTransaction()); 138} 139 140// Test the scoped error ignorer by attempting to insert a duplicate 141// value into an index. 142TEST_F(SQLConnectionTest, ScopedIgnoreError) { 143 const char* kCreateSql = "CREATE TABLE foo (id INTEGER UNIQUE)"; 144 ASSERT_TRUE(db().Execute(kCreateSql)); 145 ASSERT_TRUE(db().Execute("INSERT INTO foo (id) VALUES (12)")); 146 147 sql::ScopedErrorIgnorer ignore_errors; 148 ignore_errors.IgnoreError(SQLITE_CONSTRAINT); 149 ASSERT_FALSE(db().Execute("INSERT INTO foo (id) VALUES (12)")); 150 ASSERT_TRUE(ignore_errors.CheckIgnoredErrors()); 151} 152 153// Test that sql::Connection::Raze() results in a database without the 154// tables from the original database. 155TEST_F(SQLConnectionTest, Raze) { 156 const char* kCreateSql = "CREATE TABLE foo (id INTEGER PRIMARY KEY, value)"; 157 ASSERT_TRUE(db().Execute(kCreateSql)); 158 ASSERT_TRUE(db().Execute("INSERT INTO foo (value) VALUES (12)")); 159 160 int pragma_auto_vacuum = 0; 161 { 162 sql::Statement s(db().GetUniqueStatement("PRAGMA auto_vacuum")); 163 ASSERT_TRUE(s.Step()); 164 pragma_auto_vacuum = s.ColumnInt(0); 165 ASSERT_TRUE(pragma_auto_vacuum == 0 || pragma_auto_vacuum == 1); 166 } 167 168 // If auto_vacuum is set, there's an extra page to maintain a freelist. 169 const int kExpectedPageCount = 2 + pragma_auto_vacuum; 170 171 { 172 sql::Statement s(db().GetUniqueStatement("PRAGMA page_count")); 173 ASSERT_TRUE(s.Step()); 174 EXPECT_EQ(kExpectedPageCount, s.ColumnInt(0)); 175 } 176 177 { 178 sql::Statement s(db().GetUniqueStatement("SELECT * FROM sqlite_master")); 179 ASSERT_TRUE(s.Step()); 180 EXPECT_EQ("table", s.ColumnString(0)); 181 EXPECT_EQ("foo", s.ColumnString(1)); 182 EXPECT_EQ("foo", s.ColumnString(2)); 183 // Table "foo" is stored in the last page of the file. 184 EXPECT_EQ(kExpectedPageCount, s.ColumnInt(3)); 185 EXPECT_EQ(kCreateSql, s.ColumnString(4)); 186 } 187 188 ASSERT_TRUE(db().Raze()); 189 190 { 191 sql::Statement s(db().GetUniqueStatement("PRAGMA page_count")); 192 ASSERT_TRUE(s.Step()); 193 EXPECT_EQ(1, s.ColumnInt(0)); 194 } 195 196 { 197 sql::Statement s(db().GetUniqueStatement("SELECT * FROM sqlite_master")); 198 ASSERT_FALSE(s.Step()); 199 } 200 201 { 202 sql::Statement s(db().GetUniqueStatement("PRAGMA auto_vacuum")); 203 ASSERT_TRUE(s.Step()); 204 // The new database has the same auto_vacuum as a fresh database. 205 EXPECT_EQ(pragma_auto_vacuum, s.ColumnInt(0)); 206 } 207} 208 209// Test that Raze() maintains page_size. 210TEST_F(SQLConnectionTest, RazePageSize) { 211 // Fetch the default page size and double it for use in this test. 212 // Scoped to release statement before Close(). 213 int default_page_size = 0; 214 { 215 sql::Statement s(db().GetUniqueStatement("PRAGMA page_size")); 216 ASSERT_TRUE(s.Step()); 217 default_page_size = s.ColumnInt(0); 218 } 219 ASSERT_GT(default_page_size, 0); 220 const int kPageSize = 2 * default_page_size; 221 222 // Re-open the database to allow setting the page size. 223 db().Close(); 224 db().set_page_size(kPageSize); 225 ASSERT_TRUE(db().Open(db_path())); 226 227 // page_size should match the indicated value. 228 sql::Statement s(db().GetUniqueStatement("PRAGMA page_size")); 229 ASSERT_TRUE(s.Step()); 230 ASSERT_EQ(kPageSize, s.ColumnInt(0)); 231 232 // After raze, page_size should still match the indicated value. 233 ASSERT_TRUE(db().Raze()); 234 s.Reset(true); 235 ASSERT_TRUE(s.Step()); 236 ASSERT_EQ(kPageSize, s.ColumnInt(0)); 237} 238 239// Test that Raze() results are seen in other connections. 240TEST_F(SQLConnectionTest, RazeMultiple) { 241 const char* kCreateSql = "CREATE TABLE foo (id INTEGER PRIMARY KEY, value)"; 242 ASSERT_TRUE(db().Execute(kCreateSql)); 243 244 sql::Connection other_db; 245 ASSERT_TRUE(other_db.Open(db_path())); 246 247 // Check that the second connection sees the table. 248 const char *kTablesQuery = "SELECT COUNT(*) FROM sqlite_master"; 249 sql::Statement s(other_db.GetUniqueStatement(kTablesQuery)); 250 ASSERT_TRUE(s.Step()); 251 ASSERT_EQ(1, s.ColumnInt(0)); 252 ASSERT_FALSE(s.Step()); // Releases the shared lock. 253 254 ASSERT_TRUE(db().Raze()); 255 256 // The second connection sees the updated database. 257 s.Reset(true); 258 ASSERT_TRUE(s.Step()); 259 ASSERT_EQ(0, s.ColumnInt(0)); 260} 261 262TEST_F(SQLConnectionTest, RazeLocked) { 263 const char* kCreateSql = "CREATE TABLE foo (id INTEGER PRIMARY KEY, value)"; 264 ASSERT_TRUE(db().Execute(kCreateSql)); 265 266 // Open a transaction and write some data in a second connection. 267 // This will acquire a PENDING or EXCLUSIVE transaction, which will 268 // cause the raze to fail. 269 sql::Connection other_db; 270 ASSERT_TRUE(other_db.Open(db_path())); 271 ASSERT_TRUE(other_db.BeginTransaction()); 272 const char* kInsertSql = "INSERT INTO foo VALUES (1, 'data')"; 273 ASSERT_TRUE(other_db.Execute(kInsertSql)); 274 275 ASSERT_FALSE(db().Raze()); 276 277 // Works after COMMIT. 278 ASSERT_TRUE(other_db.CommitTransaction()); 279 ASSERT_TRUE(db().Raze()); 280 281 // Re-create the database. 282 ASSERT_TRUE(db().Execute(kCreateSql)); 283 ASSERT_TRUE(db().Execute(kInsertSql)); 284 285 // An unfinished read transaction in the other connection also 286 // blocks raze. 287 const char *kQuery = "SELECT COUNT(*) FROM foo"; 288 sql::Statement s(other_db.GetUniqueStatement(kQuery)); 289 ASSERT_TRUE(s.Step()); 290 ASSERT_FALSE(db().Raze()); 291 292 // Complete the statement unlocks the database. 293 ASSERT_FALSE(s.Step()); 294 ASSERT_TRUE(db().Raze()); 295} 296 297// Basic test of RazeAndClose() operation. 298TEST_F(SQLConnectionTest, RazeAndClose) { 299 const char* kCreateSql = "CREATE TABLE foo (id INTEGER PRIMARY KEY, value)"; 300 const char* kPopulateSql = "INSERT INTO foo (value) VALUES (12)"; 301 302 // Test that RazeAndClose() closes the database, and that the 303 // database is empty when re-opened. 304 ASSERT_TRUE(db().Execute(kCreateSql)); 305 ASSERT_TRUE(db().Execute(kPopulateSql)); 306 ASSERT_TRUE(db().RazeAndClose()); 307 ASSERT_FALSE(db().is_open()); 308 db().Close(); 309 ASSERT_TRUE(db().Open(db_path())); 310 { 311 sql::Statement s(db().GetUniqueStatement("SELECT * FROM sqlite_master")); 312 ASSERT_FALSE(s.Step()); 313 } 314 315 // Test that RazeAndClose() can break transactions. 316 ASSERT_TRUE(db().Execute(kCreateSql)); 317 ASSERT_TRUE(db().Execute(kPopulateSql)); 318 ASSERT_TRUE(db().BeginTransaction()); 319 ASSERT_TRUE(db().RazeAndClose()); 320 ASSERT_FALSE(db().is_open()); 321 ASSERT_FALSE(db().CommitTransaction()); 322 db().Close(); 323 ASSERT_TRUE(db().Open(db_path())); 324 { 325 sql::Statement s(db().GetUniqueStatement("SELECT * FROM sqlite_master")); 326 ASSERT_FALSE(s.Step()); 327 } 328} 329 330// Test that various operations fail without crashing after 331// RazeAndClose(). 332TEST_F(SQLConnectionTest, RazeAndCloseDiagnostics) { 333 const char* kCreateSql = "CREATE TABLE foo (id INTEGER PRIMARY KEY, value)"; 334 const char* kPopulateSql = "INSERT INTO foo (value) VALUES (12)"; 335 const char* kSimpleSql = "SELECT 1"; 336 337 ASSERT_TRUE(db().Execute(kCreateSql)); 338 ASSERT_TRUE(db().Execute(kPopulateSql)); 339 340 // Test baseline expectations. 341 db().Preload(); 342 ASSERT_TRUE(db().DoesTableExist("foo")); 343 ASSERT_TRUE(db().IsSQLValid(kSimpleSql)); 344 ASSERT_EQ(SQLITE_OK, db().ExecuteAndReturnErrorCode(kSimpleSql)); 345 ASSERT_TRUE(db().Execute(kSimpleSql)); 346 ASSERT_TRUE(db().is_open()); 347 { 348 sql::Statement s(db().GetUniqueStatement(kSimpleSql)); 349 ASSERT_TRUE(s.Step()); 350 } 351 { 352 sql::Statement s(db().GetCachedStatement(SQL_FROM_HERE, kSimpleSql)); 353 ASSERT_TRUE(s.Step()); 354 } 355 ASSERT_TRUE(db().BeginTransaction()); 356 ASSERT_TRUE(db().CommitTransaction()); 357 ASSERT_TRUE(db().BeginTransaction()); 358 db().RollbackTransaction(); 359 360 ASSERT_TRUE(db().RazeAndClose()); 361 362 // At this point, they should all fail, but not crash. 363 db().Preload(); 364 ASSERT_FALSE(db().DoesTableExist("foo")); 365 ASSERT_FALSE(db().IsSQLValid(kSimpleSql)); 366 ASSERT_EQ(SQLITE_ERROR, db().ExecuteAndReturnErrorCode(kSimpleSql)); 367 ASSERT_FALSE(db().Execute(kSimpleSql)); 368 ASSERT_FALSE(db().is_open()); 369 { 370 sql::Statement s(db().GetUniqueStatement(kSimpleSql)); 371 ASSERT_FALSE(s.Step()); 372 } 373 { 374 sql::Statement s(db().GetCachedStatement(SQL_FROM_HERE, kSimpleSql)); 375 ASSERT_FALSE(s.Step()); 376 } 377 ASSERT_FALSE(db().BeginTransaction()); 378 ASSERT_FALSE(db().CommitTransaction()); 379 ASSERT_FALSE(db().BeginTransaction()); 380 db().RollbackTransaction(); 381 382 // Close normally to reset the poisoned flag. 383 db().Close(); 384 385 // DEATH tests not supported on Android or iOS. 386#if !defined(OS_ANDROID) && !defined(OS_IOS) 387 // Once the real Close() has been called, various calls enforce API 388 // usage by becoming fatal in debug mode. Since DEATH tests are 389 // expensive, just test one of them. 390 if (DLOG_IS_ON(FATAL)) { 391 ASSERT_DEATH({ 392 db().IsSQLValid(kSimpleSql); 393 }, "Illegal use of connection without a db"); 394 } 395#endif 396} 397 398// TODO(shess): Spin up a background thread to hold other_db, to more 399// closely match real life. That would also allow testing 400// RazeWithTimeout(). 401 402#if defined(OS_ANDROID) 403TEST_F(SQLConnectionTest, SetTempDirForSQL) { 404 405 sql::MetaTable meta_table; 406 // Below call needs a temporary directory in sqlite3 407 // On Android, it can pass only when the temporary directory is set. 408 // Otherwise, sqlite3 doesn't find the correct directory to store 409 // temporary files and will report the error 'unable to open 410 // database file'. 411 ASSERT_TRUE(meta_table.Init(&db(), 4, 4)); 412} 413#endif 414 415TEST_F(SQLConnectionTest, Delete) { 416 EXPECT_TRUE(db().Execute("CREATE TABLE x (x)")); 417 db().Close(); 418 419 // Should have both a main database file and a journal file because 420 // of journal_mode PERSIST. 421 base::FilePath journal(db_path().value() + FILE_PATH_LITERAL("-journal")); 422 ASSERT_TRUE(file_util::PathExists(db_path())); 423 ASSERT_TRUE(file_util::PathExists(journal)); 424 425 sql::Connection::Delete(db_path()); 426 EXPECT_FALSE(file_util::PathExists(db_path())); 427 EXPECT_FALSE(file_util::PathExists(journal)); 428} 429