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