connection_unittest.cc revision 2a99a7e74a7f215066514fe81d2bfa6639d9eddd 
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
14class 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
38TEST_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
49TEST_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
59TEST_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
91TEST_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
97TEST_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
114TEST_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
130TEST_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.
141TEST_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.
196TEST_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.
226TEST_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
248TEST_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.
284TEST_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().
318TEST_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)
389TEST_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
400