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 "sql/connection.h"
   8 #include "sql/meta_table.h"
   9 #include "sql/statement.h"
  10 #include "testing/gtest/include/gtest/gtest.h"
  11 #include "third_party/sqlite/sqlite3.h"
  12
  13 class SQLConnectionTest : public testing::Test {
  14  public:
  15   SQLConnectionTest() {}
  16
  17   void SetUp() {
  18     ASSERT_TRUE(temp_dir_.CreateUniqueTempDir());
  19     ASSERT_TRUE(db_.Open(db_path()));
  20   }
  21
  22   void TearDown() {
  23     db_.Close();
  24   }
  25
  26   sql::Connection& db() { return db_; }
  27
  28   FilePath db_path() {
  29     return temp_dir_.path().AppendASCII("SQLConnectionTest.db");
  30   }
  31
  32  private:
  33   base::ScopedTempDir temp_dir_;
  34   sql::Connection db_;
  35 };
  36
  37 TEST_F(SQLConnectionTest, Execute) {
  38   // Valid statement should return true.
  39   ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
  40   EXPECT_EQ(SQLITE_OK, db().GetErrorCode());
  41
  42   // Invalid statement should fail.
  43   ASSERT_EQ(SQLITE_ERROR,
  44             db().ExecuteAndReturnErrorCode("CREATE TAB foo (a, b"));
  45   EXPECT_EQ(SQLITE_ERROR, db().GetErrorCode());
  46 }
  47
  48 TEST_F(SQLConnectionTest, ExecuteWithErrorCode) {
  49   ASSERT_EQ(SQLITE_OK,
  50             db().ExecuteAndReturnErrorCode("CREATE TABLE foo (a, b)"));
  51   ASSERT_EQ(SQLITE_ERROR,
  52             db().ExecuteAndReturnErrorCode("CREATE TABLE TABLE"));
  53   ASSERT_EQ(SQLITE_ERROR,
  54             db().ExecuteAndReturnErrorCode(
  55                 "INSERT INTO foo(a, b) VALUES (1, 2, 3, 4)"));
  56 }
  57
  58 TEST_F(SQLConnectionTest, CachedStatement) {
  59   sql::StatementID id1("foo", 12);
  60
  61   ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
  62   ASSERT_TRUE(db().Execute("INSERT INTO foo(a, b) VALUES (12, 13)"));
  63
  64   // Create a new cached statement.
  65   {
  66     sql::Statement s(db().GetCachedStatement(id1, "SELECT a FROM foo"));
  67     ASSERT_TRUE(s.is_valid());
  68
  69     ASSERT_TRUE(s.Step());
  70     EXPECT_EQ(12, s.ColumnInt(0));
  71   }
  72
  73   // The statement should be cached still.
  74   EXPECT_TRUE(db().HasCachedStatement(id1));
  75
  76   {
  77     // Get the same statement using different SQL. This should ignore our
  78     // SQL and use the cached one (so it will be valid).
  79     sql::Statement s(db().GetCachedStatement(id1, "something invalid("));
  80     ASSERT_TRUE(s.is_valid());
  81
  82     ASSERT_TRUE(s.Step());
  83     EXPECT_EQ(12, s.ColumnInt(0));
  84   }
  85
  86   // Make sure other statements aren't marked as cached.
  87   EXPECT_FALSE(db().HasCachedStatement(SQL_FROM_HERE));
  88 }
  89
  90 TEST_F(SQLConnectionTest, IsSQLValidTest) {
  91   ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
  92   ASSERT_TRUE(db().IsSQLValid("SELECT a FROM foo"));
  93   ASSERT_FALSE(db().IsSQLValid("SELECT no_exist FROM foo"));
  94 }
  95
  96 TEST_F(SQLConnectionTest, DoesStuffExist) {
  97   // Test DoesTableExist.
  98   EXPECT_FALSE(db().DoesTableExist("foo"));
  99   ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
 100   EXPECT_TRUE(db().DoesTableExist("foo"));
 101
 102   // Should be case sensitive.
 103   EXPECT_FALSE(db().DoesTableExist("FOO"));
 104
 105   // Test DoesColumnExist.
 106   EXPECT_FALSE(db().DoesColumnExist("foo", "bar"));
 107   EXPECT_TRUE(db().DoesColumnExist("foo", "a"));
 108
 109   // Testing for a column on a nonexistent table.
 110   EXPECT_FALSE(db().DoesColumnExist("bar", "b"));
 111 }
 112
 113 TEST_F(SQLConnectionTest, GetLastInsertRowId) {
 114   ASSERT_TRUE(db().Execute("CREATE TABLE foo (id INTEGER PRIMARY KEY, value)"));
 115
 116   ASSERT_TRUE(db().Execute("INSERT INTO foo (value) VALUES (12)"));
 117
 118   // Last insert row ID should be valid.
 119   int64 row = db().GetLastInsertRowId();
 120   EXPECT_LT(0, row);
 121
 122   // It should be the primary key of the row we just inserted.
 123   sql::Statement s(db().GetUniqueStatement("SELECT value FROM foo WHERE id=?"));
 124   s.BindInt64(0, row);
 125   ASSERT_TRUE(s.Step());
 126   EXPECT_EQ(12, s.ColumnInt(0));
 127 }
 128
 129 TEST_F(SQLConnectionTest, Rollback) {
 130   ASSERT_TRUE(db().BeginTransaction());
 131   ASSERT_TRUE(db().BeginTransaction());
 132   EXPECT_EQ(2, db().transaction_nesting());
 133   db().RollbackTransaction();
 134   EXPECT_FALSE(db().CommitTransaction());
 135   EXPECT_TRUE(db().BeginTransaction());
 136 }
 137
 138 // Test that sql::Connection::Raze() results in a database without the
 139 // tables from the original database.
 140 TEST_F(SQLConnectionTest, Raze) {
 141   const char* kCreateSql = "CREATE TABLE foo (id INTEGER PRIMARY KEY, value)";
 142   ASSERT_TRUE(db().Execute(kCreateSql));
 143   ASSERT_TRUE(db().Execute("INSERT INTO foo (value) VALUES (12)"));
 144
 145   int pragma_auto_vacuum = 0;
 146   {
 147     sql::Statement s(db().GetUniqueStatement("PRAGMA auto_vacuum"));
 148     ASSERT_TRUE(s.Step());
 149     pragma_auto_vacuum = s.ColumnInt(0);
 150     ASSERT_TRUE(pragma_auto_vacuum == 0 || pragma_auto_vacuum == 1);
 151   }
 152
 153   // If auto_vacuum is set, there's an extra page to maintain a freelist.
 154   const int kExpectedPageCount = 2 + pragma_auto_vacuum;
 155
 156   {
 157     sql::Statement s(db().GetUniqueStatement("PRAGMA page_count"));
 158     ASSERT_TRUE(s.Step());
 159     EXPECT_EQ(kExpectedPageCount, s.ColumnInt(0));
 160   }
 161
 162   {
 163     sql::Statement s(db().GetUniqueStatement("SELECT * FROM sqlite_master"));
 164     ASSERT_TRUE(s.Step());
 165     EXPECT_EQ("table", s.ColumnString(0));
 166     EXPECT_EQ("foo", s.ColumnString(1));
 167     EXPECT_EQ("foo", s.ColumnString(2));
 168     // Table "foo" is stored in the last page of the file.
 169     EXPECT_EQ(kExpectedPageCount, s.ColumnInt(3));
 170     EXPECT_EQ(kCreateSql, s.ColumnString(4));
 171   }
 172
 173   ASSERT_TRUE(db().Raze());
 174
 175   {
 176     sql::Statement s(db().GetUniqueStatement("PRAGMA page_count"));
 177     ASSERT_TRUE(s.Step());
 178     EXPECT_EQ(1, s.ColumnInt(0));
 179   }
 180
 181   {
 182     sql::Statement s(db().GetUniqueStatement("SELECT * FROM sqlite_master"));
 183     ASSERT_FALSE(s.Step());
 184   }
 185
 186   {
 187     sql::Statement s(db().GetUniqueStatement("PRAGMA auto_vacuum"));
 188     ASSERT_TRUE(s.Step());
 189     // The new database has the same auto_vacuum as a fresh database.
 190     EXPECT_EQ(pragma_auto_vacuum, s.ColumnInt(0));
 191   }
 192 }
 193
 194 // Test that Raze() maintains page_size.
 195 TEST_F(SQLConnectionTest, RazePageSize) {
 196   // Fetch the default page size and double it for use in this test.
 197   // Scoped to release statement before Close().
 198   int default_page_size = 0;
 199   {
 200     sql::Statement s(db().GetUniqueStatement("PRAGMA page_size"));
 201     ASSERT_TRUE(s.Step());
 202     default_page_size = s.ColumnInt(0);
 203   }
 204   ASSERT_GT(default_page_size, 0);
 205   const int kPageSize = 2 * default_page_size;
 206
 207   // Re-open the database to allow setting the page size.
 208   db().Close();
 209   db().set_page_size(kPageSize);
 210   ASSERT_TRUE(db().Open(db_path()));
 211
 212   // page_size should match the indicated value.
 213   sql::Statement s(db().GetUniqueStatement("PRAGMA page_size"));
 214   ASSERT_TRUE(s.Step());
 215   ASSERT_EQ(kPageSize, s.ColumnInt(0));
 216
 217   // After raze, page_size should still match the indicated value.
 218   ASSERT_TRUE(db().Raze());
 219   s.Reset(true);
 220   ASSERT_TRUE(s.Step());
 221   ASSERT_EQ(kPageSize, s.ColumnInt(0));
 222 }
 223
 224 // Test that Raze() results are seen in other connections.
 225 TEST_F(SQLConnectionTest, RazeMultiple) {
 226   const char* kCreateSql = "CREATE TABLE foo (id INTEGER PRIMARY KEY, value)";
 227   ASSERT_TRUE(db().Execute(kCreateSql));
 228
 229   sql::Connection other_db;
 230   ASSERT_TRUE(other_db.Open(db_path()));
 231
 232   // Check that the second connection sees the table.
 233   const char *kTablesQuery = "SELECT COUNT(*) FROM sqlite_master";
 234   sql::Statement s(other_db.GetUniqueStatement(kTablesQuery));
 235   ASSERT_TRUE(s.Step());
 236   ASSERT_EQ(1, s.ColumnInt(0));
 237   ASSERT_FALSE(s.Step());  // Releases the shared lock.
 238
 239   ASSERT_TRUE(db().Raze());
 240
 241   // The second connection sees the updated database.
 242   s.Reset(true);
 243   ASSERT_TRUE(s.Step());
 244   ASSERT_EQ(0, s.ColumnInt(0));
 245 }
 246
 247 TEST_F(SQLConnectionTest, RazeLocked) {
 248   const char* kCreateSql = "CREATE TABLE foo (id INTEGER PRIMARY KEY, value)";
 249   ASSERT_TRUE(db().Execute(kCreateSql));
 250
 251   // Open a transaction and write some data in a second connection.
 252   // This will acquire a PENDING or EXCLUSIVE transaction, which will
 253   // cause the raze to fail.
 254   sql::Connection other_db;
 255   ASSERT_TRUE(other_db.Open(db_path()));
 256   ASSERT_TRUE(other_db.BeginTransaction());
 257   const char* kInsertSql = "INSERT INTO foo VALUES (1, 'data')";
 258   ASSERT_TRUE(other_db.Execute(kInsertSql));
 259
 260   ASSERT_FALSE(db().Raze());
 261
 262   // Works after COMMIT.
 263   ASSERT_TRUE(other_db.CommitTransaction());
 264   ASSERT_TRUE(db().Raze());
 265
 266   // Re-create the database.
 267   ASSERT_TRUE(db().Execute(kCreateSql));
 268   ASSERT_TRUE(db().Execute(kInsertSql));
 269
 270   // An unfinished read transaction in the other connection also
 271   // blocks raze.
 272   const char *kQuery = "SELECT COUNT(*) FROM foo";
 273   sql::Statement s(other_db.GetUniqueStatement(kQuery));
 274   ASSERT_TRUE(s.Step());
 275   ASSERT_FALSE(db().Raze());
 276
 277   // Complete the statement unlocks the database.
 278   ASSERT_FALSE(s.Step());
 279   ASSERT_TRUE(db().Raze());
 280 }
 281
 282 #if defined(OS_ANDROID)
 283 TEST_F(SQLConnectionTest, SetTempDirForSQL) {
 284
 285   sql::MetaTable meta_table;
 286   // Below call needs a temporary directory in sqlite3
 287   // On Android, it can pass only when the temporary directory is set.
 288   // Otherwise, sqlite3 doesn't find the correct directory to store
 289   // temporary files and will report the error 'unable to open
 290   // database file'.
 291   ASSERT_TRUE(meta_table.Init(&db(), 4, 4));
 292 }
 293 #endif
 294
 295 // TODO(shess): Spin up a background thread to hold other_db, to more
 296 // closely match real life.  That would also allow testing
 297 // RazeWithTimeout().