sql/connection_unittest.cc

   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
  15 class 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
  39 TEST_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
  50 TEST_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
  60 TEST_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
  92 TEST_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
  98 TEST_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
 115 TEST_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
 131 TEST_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.
 142 TEST_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.
 155 TEST_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.
 210 TEST_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.
 240 TEST_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
 262 TEST_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.
 298 TEST_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().
 332 TEST_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)
 403 TEST_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