1 // Copyright 2012 The Chromium Authors
2 // Use of this source code is governed by a BSD-style license that can be
3 // found in the LICENSE file.
11 #include "base/bind.h"
12 #include "base/files/file_path.h"
13 #include "base/files/file_util.h"
14 #include "base/files/memory_mapped_file.h"
15 #include "base/files/scoped_temp_dir.h"
16 #include "build/build_config.h"
17 #include "sql/database.h"
18 #include "sql/statement.h"
19 #include "sql/test/scoped_error_expecter.h"
20 #include "sql/test/test_helpers.h"
21 #include "testing/gtest/include/gtest/gtest.h"
22 #include "third_party/sqlite/sqlite3.h"
24 #if BUILDFLAG(IS_APPLE)
25 #include "base/mac/backup_util.h"
28 // Test that certain features are/are-not enabled in our SQLite.
33 using sql::test::ExecuteWithResult;
34 using sql::test::ExecuteWithResults;
38 class SQLiteFeaturesTest : public testing::Test {
40 ~SQLiteFeaturesTest() override = default;
42 void SetUp() override {
43 ASSERT_TRUE(temp_dir_.CreateUniqueTempDir());
44 db_path_ = temp_dir_.GetPath().AppendASCII("sqlite_features_test.sqlite");
45 ASSERT_TRUE(db_.Open(db_path_));
50 return db_.Open(db_path_);
54 base::ScopedTempDir temp_dir_;
55 base::FilePath db_path_;
58 // The error code of the most recent error.
59 int error_ = SQLITE_OK;
60 // Original statement which has caused the error.
61 std::string sql_text_;
64 // Do not include fts1 support, it is not useful, and nobody is
66 TEST_F(SQLiteFeaturesTest, NoFTS1) {
67 sql::test::ScopedErrorExpecter expecter;
68 expecter.ExpectError(SQLITE_ERROR);
69 EXPECT_FALSE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts1(x)"));
70 EXPECT_TRUE(expecter.SawExpectedErrors());
73 // Do not include fts2 support, it is not useful, and nobody is
75 TEST_F(SQLiteFeaturesTest, NoFTS2) {
76 sql::test::ScopedErrorExpecter expecter;
77 expecter.ExpectError(SQLITE_ERROR);
78 EXPECT_FALSE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts2(x)"));
79 EXPECT_TRUE(expecter.SawExpectedErrors());
82 // fts3 is exposed in WebSQL.
83 TEST_F(SQLiteFeaturesTest, FTS3) {
84 EXPECT_TRUE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts3(x)"));
87 // Originally history used fts2, which Chromium patched to treat "foo*" as a
88 // prefix search, though the icu tokenizer would return it as two tokens {"foo",
89 // "*"}. Test that fts3 works correctly.
90 TEST_F(SQLiteFeaturesTest, FTS3_Prefix) {
92 sql::Database db({.enable_virtual_tables_discouraged = true});
93 ASSERT_TRUE(db.Open(db_path_));
95 static constexpr char kCreateSql[] =
96 "CREATE VIRTUAL TABLE foo USING fts3(x, tokenize icu)";
97 ASSERT_TRUE(db.Execute(kCreateSql));
99 ASSERT_TRUE(db.Execute("INSERT INTO foo (x) VALUES ('test')"));
102 ExecuteWithResult(&db, "SELECT x FROM foo WHERE x MATCH 'te*'"));
105 // Verify that Chromium's SQLite is compiled with HAVE_USLEEP defined. With
106 // HAVE_USLEEP, SQLite uses usleep() with millisecond granularity. Otherwise it
107 // uses sleep() with second granularity.
108 TEST_F(SQLiteFeaturesTest, UsesUsleep) {
109 base::TimeTicks before = base::TimeTicks::Now();
111 base::TimeDelta delta = base::TimeTicks::Now() - before;
113 // It is not impossible for this to be over 1000 if things are compiled
114 // correctly, but that is very unlikely. Most platforms seem to be exactly
115 // 1ms, with the rest at 2ms, and the worst observed cases was ASAN at 7ms.
116 EXPECT_LT(delta.InMilliseconds(), 1000);
119 // Ensure that our SQLite version has working foreign key support with cascade
121 TEST_F(SQLiteFeaturesTest, ForeignKeySupport) {
122 ASSERT_TRUE(db_.Execute("PRAGMA foreign_keys=1"));
123 ASSERT_TRUE(db_.Execute("CREATE TABLE parents (id INTEGER PRIMARY KEY)"));
124 ASSERT_TRUE(db_.Execute(
125 "CREATE TABLE children ("
126 " id INTEGER PRIMARY KEY,"
127 " pid INTEGER NOT NULL REFERENCES parents(id) ON DELETE CASCADE)"));
128 static const char kSelectParentsSql[] = "SELECT * FROM parents ORDER BY id";
129 static const char kSelectChildrenSql[] = "SELECT * FROM children ORDER BY id";
131 // Inserting without a matching parent should fail with constraint violation.
132 EXPECT_EQ("", ExecuteWithResult(&db_, kSelectParentsSql));
134 sql::test::ScopedErrorExpecter expecter;
135 expecter.ExpectError(SQLITE_CONSTRAINT | SQLITE_CONSTRAINT_FOREIGNKEY);
136 EXPECT_FALSE(db_.Execute("INSERT INTO children VALUES (10, 1)"));
137 EXPECT_TRUE(expecter.SawExpectedErrors());
139 EXPECT_EQ("", ExecuteWithResult(&db_, kSelectChildrenSql));
141 // Inserting with a matching parent should work.
142 ASSERT_TRUE(db_.Execute("INSERT INTO parents VALUES (1)"));
143 EXPECT_EQ("1", ExecuteWithResults(&db_, kSelectParentsSql, "|", "\n"));
144 EXPECT_TRUE(db_.Execute("INSERT INTO children VALUES (11, 1)"));
145 EXPECT_TRUE(db_.Execute("INSERT INTO children VALUES (12, 1)"));
146 EXPECT_EQ("11|1\n12|1",
147 ExecuteWithResults(&db_, kSelectChildrenSql, "|", "\n"));
149 // Deleting the parent should cascade, deleting the children as well.
150 ASSERT_TRUE(db_.Execute("DELETE FROM parents"));
151 EXPECT_EQ("", ExecuteWithResult(&db_, kSelectParentsSql));
152 EXPECT_EQ("", ExecuteWithResult(&db_, kSelectChildrenSql));
155 // Ensure that our SQLite version supports booleans.
156 TEST_F(SQLiteFeaturesTest, BooleanSupport) {
158 db_.Execute("CREATE TABLE flags ("
159 " id INTEGER PRIMARY KEY,"
160 " true_flag BOOL NOT NULL DEFAULT TRUE,"
161 " false_flag BOOL NOT NULL DEFAULT FALSE)"));
162 ASSERT_TRUE(db_.Execute(
163 "ALTER TABLE flags ADD COLUMN true_flag2 BOOL NOT NULL DEFAULT TRUE"));
164 ASSERT_TRUE(db_.Execute(
165 "ALTER TABLE flags ADD COLUMN false_flag2 BOOL NOT NULL DEFAULT FALSE"));
166 ASSERT_TRUE(db_.Execute("INSERT INTO flags (id) VALUES (1)"));
168 sql::Statement s(db_.GetUniqueStatement(
169 "SELECT true_flag, false_flag, true_flag2, false_flag2"
170 " FROM flags WHERE id=1;"));
171 ASSERT_TRUE(s.Step());
173 EXPECT_TRUE(s.ColumnBool(0)) << " default TRUE at table creation time";
174 EXPECT_TRUE(!s.ColumnBool(1)) << " default FALSE at table creation time";
176 EXPECT_TRUE(s.ColumnBool(2)) << " default TRUE added by altering the table";
177 EXPECT_TRUE(!s.ColumnBool(3)) << " default FALSE added by altering the table";
180 TEST_F(SQLiteFeaturesTest, IcuEnabled) {
181 sql::Statement lower_en(db_.GetUniqueStatement("SELECT lower('I', 'en_us')"));
182 ASSERT_TRUE(lower_en.Step());
183 EXPECT_EQ("i", lower_en.ColumnString(0));
185 sql::Statement lower_tr(db_.GetUniqueStatement("SELECT lower('I', 'tr_tr')"));
186 ASSERT_TRUE(lower_tr.Step());
187 EXPECT_EQ("\u0131", lower_tr.ColumnString(0));
190 // Verify that OS file writes are reflected in the memory mapping of a
191 // memory-mapped file. Normally SQLite writes to memory-mapped files using
192 // memcpy(), which should stay consistent. Our SQLite is slightly patched to
193 // mmap read only, then write using OS file writes. If the memory-mapped
194 // version doesn't reflect the OS file writes, SQLite's memory-mapped I/O should
195 // be disabled on this platform using SQLITE_MAX_MMAP_SIZE=0.
196 TEST_F(SQLiteFeaturesTest, Mmap) {
197 // Try to turn on mmap'ed I/O.
198 std::ignore = db_.Execute("PRAGMA mmap_size = 1048576");
200 sql::Statement s(db_.GetUniqueStatement("PRAGMA mmap_size"));
202 ASSERT_TRUE(s.Step());
203 ASSERT_GT(s.ColumnInt64(0), 0);
207 const uint32_t kFlags =
208 base::File::FLAG_OPEN | base::File::FLAG_READ | base::File::FLAG_WRITE;
211 // Create a file with a block of '0', a block of '1', and a block of '2'.
213 base::File f(db_path_, kFlags);
214 ASSERT_TRUE(f.IsValid());
215 memset(buf, '0', sizeof(buf));
216 ASSERT_EQ(f.Write(0*sizeof(buf), buf, sizeof(buf)), (int)sizeof(buf));
218 memset(buf, '1', sizeof(buf));
219 ASSERT_EQ(f.Write(1*sizeof(buf), buf, sizeof(buf)), (int)sizeof(buf));
221 memset(buf, '2', sizeof(buf));
222 ASSERT_EQ(f.Write(2*sizeof(buf), buf, sizeof(buf)), (int)sizeof(buf));
225 // mmap the file and verify that everything looks right.
227 base::MemoryMappedFile m;
228 ASSERT_TRUE(m.Initialize(db_path_));
230 memset(buf, '0', sizeof(buf));
231 ASSERT_EQ(0, memcmp(buf, m.data() + 0*sizeof(buf), sizeof(buf)));
233 memset(buf, '1', sizeof(buf));
234 ASSERT_EQ(0, memcmp(buf, m.data() + 1*sizeof(buf), sizeof(buf)));
236 memset(buf, '2', sizeof(buf));
237 ASSERT_EQ(0, memcmp(buf, m.data() + 2*sizeof(buf), sizeof(buf)));
239 // Scribble some '3' into the first page of the file, and verify that it
240 // looks the same in the memory mapping.
242 base::File f(db_path_, kFlags);
243 ASSERT_TRUE(f.IsValid());
244 memset(buf, '3', sizeof(buf));
245 ASSERT_EQ(f.Write(0*sizeof(buf), buf, sizeof(buf)), (int)sizeof(buf));
247 ASSERT_EQ(0, memcmp(buf, m.data() + 0*sizeof(buf), sizeof(buf)));
249 // Repeat with a single '4' in case page-sized blocks are different.
250 const size_t kOffset = 1*sizeof(buf) + 123;
251 ASSERT_NE('4', m.data()[kOffset]);
253 base::File f(db_path_, kFlags);
254 ASSERT_TRUE(f.IsValid());
256 ASSERT_EQ(f.Write(kOffset, buf, 1), 1);
258 ASSERT_EQ('4', m.data()[kOffset]);
262 // Verify that http://crbug.com/248608 is fixed. In this bug, the
263 // compiled regular expression is effectively cached with the prepared
264 // statement, causing errors if the regular expression is rebound.
265 TEST_F(SQLiteFeaturesTest, CachedRegexp) {
266 ASSERT_TRUE(db_.Execute("CREATE TABLE r (id INTEGER UNIQUE, x TEXT)"));
267 ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (1, 'this is a test')"));
268 ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (2, 'that was a test')"));
269 ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (3, 'this is a stickup')"));
270 ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (4, 'that sucks')"));
272 static const char kSimpleSql[] = "SELECT SUM(id) FROM r WHERE x REGEXP ?";
273 sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE, kSimpleSql));
275 s.BindString(0, "this.*");
276 ASSERT_TRUE(s.Step());
277 EXPECT_EQ(4, s.ColumnInt(0));
280 s.BindString(0, "that.*");
281 ASSERT_TRUE(s.Step());
282 EXPECT_EQ(6, s.ColumnInt(0));
285 s.BindString(0, ".*test");
286 ASSERT_TRUE(s.Step());
287 EXPECT_EQ(3, s.ColumnInt(0));
290 s.BindString(0, ".* s[a-z]+");
291 ASSERT_TRUE(s.Step());
292 EXPECT_EQ(7, s.ColumnInt(0));
295 TEST_F(SQLiteFeaturesTest, JsonIsDisabled) {
296 static constexpr char kCreateSql[] =
297 "CREATE TABLE rows(id INTEGER PRIMARY KEY NOT NULL, data TEXT NOT NULL)";
298 ASSERT_TRUE(db_.Execute(kCreateSql));
299 ASSERT_TRUE(db_.Execute("INSERT INTO rows(data) VALUES('{\"a\": 1}')"));
302 sql::test::ScopedErrorExpecter expecter;
303 expecter.ExpectError(SQLITE_ERROR);
304 EXPECT_FALSE(db_.Execute("SELECT data -> '$.a' FROM rows"));
305 EXPECT_TRUE(expecter.SawExpectedErrors());
309 TEST_F(SQLiteFeaturesTest, WindowFunctionsAreDisabled) {
310 static constexpr char kCreateSql[] =
311 "CREATE TABLE rows(id INTEGER PRIMARY KEY NOT NULL, data TEXT NOT NULL)";
312 ASSERT_TRUE(db_.Execute(kCreateSql));
313 ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(1, 'a')"));
314 ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(2, 'c')"));
315 ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(3, 'b')"));
318 sql::test::ScopedErrorExpecter expecter;
319 expecter.ExpectError(SQLITE_ERROR);
320 EXPECT_FALSE(db_.Execute(
321 "SELECT data, row_number() OVER (ORDER BY data) AS rank FROM rows "
323 EXPECT_TRUE(expecter.SawExpectedErrors());
327 // The "No Isolation Between Operations On The Same Database Connection" section
328 // in https://sqlite.org/isolation.html implies that it's safe to issue multiple
329 // concurrent SELECTs against the same area.
331 // Chrome code is allowed to rely on this guarantee. So, we test for it here, to
332 // catch any regressions introduced by SQLite upgrades.
333 TEST_F(SQLiteFeaturesTest, ConcurrentSelects) {
334 ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
335 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
336 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
337 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
339 static const char kSelectAllSql[] = "SELECT id,t FROM rows";
340 static const char kSelectEvenSql[] = "SELECT id,t FROM rows WHERE id%2=0";
342 sql::Statement select1(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
343 sql::Statement select2(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
344 sql::Statement select3(db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
346 ASSERT_TRUE(select1.Step());
347 EXPECT_EQ(select1.ColumnInt(0), 2);
348 EXPECT_EQ(select1.ColumnString(1), "two");
350 ASSERT_TRUE(select2.Step());
351 EXPECT_EQ(select2.ColumnInt(0), 2);
352 EXPECT_EQ(select2.ColumnString(1), "two");
354 ASSERT_TRUE(select3.Step());
355 EXPECT_EQ(select3.ColumnInt(0), 2);
356 EXPECT_EQ(select3.ColumnString(1), "two");
358 ASSERT_TRUE(select1.Step());
359 EXPECT_EQ(select1.ColumnInt(0), 4);
360 EXPECT_EQ(select1.ColumnString(1), "four");
362 ASSERT_TRUE(select3.Step());
363 EXPECT_EQ(select3.ColumnInt(0), 3);
364 EXPECT_EQ(select3.ColumnString(1), "three");
366 ASSERT_TRUE(select2.Step());
367 EXPECT_EQ(select2.ColumnInt(0), 4);
368 EXPECT_EQ(select2.ColumnString(1), "four");
370 EXPECT_FALSE(select2.Step());
372 ASSERT_TRUE(select3.Step());
373 EXPECT_EQ(select3.ColumnInt(0), 4);
374 EXPECT_EQ(select3.ColumnString(1), "four");
376 select2.Reset(/*clear_bound_vars=*/true);
377 ASSERT_TRUE(select2.Step());
378 EXPECT_EQ(select2.ColumnInt(0), 2);
379 EXPECT_EQ(select2.ColumnString(1), "two");
381 EXPECT_FALSE(select1.Step());
384 // The "No Isolation Between Operations On The Same Database Connection" section
385 // in https://sqlite.org/isolation.html states that it's safe to DELETE a row
386 // that was just returned by sqlite_step() executing a SELECT statement.
388 // Chrome code is allowed to rely on this guarantee. So, we test for it here, to
389 // catch any regressions introduced by SQLite upgrades.
390 TEST_F(SQLiteFeaturesTest, DeleteCurrentlySelectedRow) {
391 ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
392 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
393 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
394 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
395 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(5, 'five')"));
396 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(6, 'six')"));
398 static const char kSelectEvenSql[] = "SELECT id,t FROM rows WHERE id%2=0";
399 sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
401 ASSERT_TRUE(select.Step());
402 ASSERT_EQ(select.ColumnInt(0), 2);
403 ASSERT_EQ(select.ColumnString(1), "two");
404 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=2"));
406 ASSERT_TRUE(select.Step());
407 ASSERT_EQ(select.ColumnInt(0), 4);
408 ASSERT_EQ(select.ColumnString(1), "four");
409 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=4"));
411 ASSERT_TRUE(select.Step());
412 ASSERT_EQ(select.ColumnInt(0), 6);
413 ASSERT_EQ(select.ColumnString(1), "six");
414 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=6"));
416 EXPECT_FALSE(select.Step());
418 // Check that the DELETEs were applied as expected.
420 static const char kSelectAllSql[] = "SELECT id,t FROM rows";
421 sql::Statement select_all(
422 db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
423 std::vector<int> remaining_ids;
424 std::vector<std::string> remaining_texts;
425 while (select_all.Step()) {
426 remaining_ids.push_back(select_all.ColumnInt(0));
427 remaining_texts.push_back(select_all.ColumnString(1));
430 std::vector<int> expected_remaining_ids = {3, 5};
431 EXPECT_EQ(expected_remaining_ids, remaining_ids);
432 std::vector<std::string> expected_remaining_texts = {"three", "five"};
433 EXPECT_EQ(expected_remaining_texts, remaining_texts);
436 // The "No Isolation Between Operations On The Same Database Connection" section
437 // in https://sqlite.org/isolation.html states that it's safe to DELETE a row
438 // that was previously by sqlite_step() executing a SELECT statement.
440 // Chrome code is allowed to rely on this guarantee. So, we test for it here, to
441 // catch any regressions introduced by SQLite upgrades.
442 TEST_F(SQLiteFeaturesTest, DeletePreviouslySelectedRows) {
443 ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
444 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
445 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
446 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
447 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(5, 'five')"));
448 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(6, 'six')"));
450 static const char kSelectEvenSql[] = "SELECT id,t FROM rows WHERE id%2=0";
451 sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
453 ASSERT_TRUE(select.Step());
454 ASSERT_EQ(select.ColumnInt(0), 2);
455 ASSERT_EQ(select.ColumnString(1), "two");
457 ASSERT_TRUE(select.Step());
458 ASSERT_EQ(select.ColumnInt(0), 4);
459 ASSERT_EQ(select.ColumnString(1), "four");
460 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=2"));
462 ASSERT_TRUE(select.Step());
463 ASSERT_EQ(select.ColumnInt(0), 6);
464 ASSERT_EQ(select.ColumnString(1), "six");
465 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=4"));
466 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=6"));
468 EXPECT_FALSE(select.Step());
470 // Check that the DELETEs were applied as expected.
472 static const char kSelectAllSql[] = "SELECT id,t FROM rows";
473 sql::Statement select_all(
474 db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
475 std::vector<int> remaining_ids;
476 std::vector<std::string> remaining_texts;
477 while (select_all.Step()) {
478 remaining_ids.push_back(select_all.ColumnInt(0));
479 remaining_texts.push_back(select_all.ColumnString(1));
482 std::vector<int> expected_remaining_ids = {3, 5};
483 EXPECT_EQ(expected_remaining_ids, remaining_ids);
484 std::vector<std::string> expected_remaining_texts = {"three", "five"};
485 EXPECT_EQ(expected_remaining_texts, remaining_texts);
488 // The "No Isolation Between Operations On The Same Database Connection" section
489 // in https://sqlite.org/isolation.html states that it's safe to DELETE a row
490 // while a SELECT statement executes, but the DELETEd row may or may not show up
491 // in the SELECT results. (See the test above for a case where the DELETEd row
492 // is guaranteed to now show up in the SELECT results.)
494 // This seems to imply that DELETEing from a table that is not read by the
495 // concurrent SELECT statement is safe and well-defined, as the DELETEd row(s)
496 // cannot possibly show up in the SELECT results.
498 // Chrome features are allowed to rely on the implication above, because it
499 // comes in very handy for DELETEing data across multiple tables. This test
500 // ensures that our assumption remains valid.
501 TEST_F(SQLiteFeaturesTest, DeleteWhileSelectingFromDifferentTable) {
502 ASSERT_TRUE(db_.Execute("CREATE TABLE main(id INTEGER PRIMARY KEY, t TEXT)"));
503 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(2, 'two')"));
504 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(3, 'three')"));
505 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(4, 'four')"));
506 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(5, 'five')"));
507 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(6, 'six')"));
510 db_.Execute("CREATE TABLE other(id INTEGER PRIMARY KEY, t TEXT)"));
511 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(1, 'one')"));
512 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(2, 'two')"));
513 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(3, 'three')"));
514 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(4, 'four')"));
515 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(5, 'five')"));
516 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(6, 'six')"));
517 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(7, 'seven')"));
519 static const char kSelectEvenSql[] = "SELECT id,t FROM main WHERE id%2=0";
520 sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
522 ASSERT_TRUE(select.Step());
523 ASSERT_EQ(select.ColumnInt(0), 2);
524 ASSERT_EQ(select.ColumnString(1), "two");
525 EXPECT_TRUE(db_.Execute("DELETE FROM other WHERE id=2"));
527 ASSERT_TRUE(select.Step());
528 ASSERT_EQ(select.ColumnInt(0), 4);
529 ASSERT_EQ(select.ColumnString(1), "four");
531 ASSERT_TRUE(select.Step());
532 ASSERT_EQ(select.ColumnInt(0), 6);
533 ASSERT_EQ(select.ColumnString(1), "six");
534 ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=4"));
535 ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=5"));
536 ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=6"));
538 EXPECT_FALSE(select.Step());
540 // Check that the DELETEs were applied as expected.
542 static const char kSelectAllSql[] = "SELECT id,t FROM other";
543 sql::Statement select_all(
544 db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
545 std::vector<int> remaining_ids;
546 std::vector<std::string> remaining_texts;
547 while (select_all.Step()) {
548 remaining_ids.push_back(select_all.ColumnInt(0));
549 remaining_texts.push_back(select_all.ColumnString(1));
552 std::vector<int> expected_remaining_ids = {1, 3, 7};
553 EXPECT_EQ(expected_remaining_ids, remaining_ids);
554 std::vector<std::string> expected_remaining_texts = {"one", "three", "seven"};
555 EXPECT_EQ(expected_remaining_texts, remaining_texts);
558 // The "No Isolation Between Operations On The Same Database Connection" section
559 // in https://sqlite.org/isolation.html states that it's possible to INSERT in
560 // a table while concurrently executing a SELECT statement reading from it, but
561 // it's undefined whether the row will show up in the SELECT statement's results
564 // Given this ambiguity, Chrome code is not allowed to INSERT in the same table
565 // as a concurrent SELECT. However, it is allowed to INSERT in a table which is
566 // not covered by SELECT, because this greatly simplifes migrations. So, we test
567 // the ability to INSERT in a table while SELECTing from another table, to
568 // catch any regressions introduced by SQLite upgrades.
569 TEST_F(SQLiteFeaturesTest, InsertWhileSelectingFromDifferentTable) {
570 ASSERT_TRUE(db_.Execute("CREATE TABLE src(id INTEGER PRIMARY KEY, t TEXT)"));
571 ASSERT_TRUE(db_.Execute("CREATE TABLE dst(id INTEGER PRIMARY KEY, t TEXT)"));
572 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(2, 'two')"));
573 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(3, 'three')"));
574 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(4, 'four')"));
575 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(5, 'five')"));
576 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(6, 'six')"));
578 static const char kSelectSrcEvenSql[] = "SELECT id,t FROM src WHERE id%2=0";
579 sql::Statement select_src(
580 db_.GetCachedStatement(SQL_FROM_HERE, kSelectSrcEvenSql));
582 ASSERT_TRUE(select_src.Step());
583 ASSERT_EQ(select_src.ColumnInt(0), 2);
584 ASSERT_EQ(select_src.ColumnString(1), "two");
585 EXPECT_TRUE(db_.Execute("INSERT INTO dst VALUES(2, 'two')"));
586 ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(3, 'three')"));
588 ASSERT_TRUE(select_src.Step());
589 ASSERT_EQ(select_src.ColumnInt(0), 4);
590 ASSERT_EQ(select_src.ColumnString(1), "four");
591 ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(4, 'four')"));
593 ASSERT_TRUE(select_src.Step());
594 ASSERT_EQ(select_src.ColumnInt(0), 6);
595 ASSERT_EQ(select_src.ColumnString(1), "six");
596 ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(5, 'five')"));
597 ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(6, 'six')"));
599 EXPECT_FALSE(select_src.Step());
601 static const char kSelectDstSql[] = "SELECT id,t FROM dst";
602 sql::Statement select_dst(
603 db_.GetCachedStatement(SQL_FROM_HERE, kSelectDstSql));
604 std::vector<int> dst_ids;
605 std::vector<std::string> dst_texts;
606 while (select_dst.Step()) {
607 dst_ids.push_back(select_dst.ColumnInt(0));
608 dst_texts.push_back(select_dst.ColumnString(1));
611 std::vector<int> expected_dst_ids = {2, 3, 4, 5, 6};
612 EXPECT_EQ(expected_dst_ids, dst_ids);
613 std::vector<std::string> expected_dst_texts = {"two", "three", "four", "five",
615 EXPECT_EQ(expected_dst_texts, dst_texts);
618 #if BUILDFLAG(IS_APPLE)
619 // If a database file is marked to be excluded from backups, verify that journal
620 // files are also excluded.
621 TEST_F(SQLiteFeaturesTest, TimeMachine) {
622 ASSERT_TRUE(db_.Execute("CREATE TABLE t (id INTEGER PRIMARY KEY)"));
625 base::FilePath journal_path = sql::Database::JournalPath(db_path_);
626 ASSERT_TRUE(base::PathExists(db_path_));
627 ASSERT_TRUE(base::PathExists(journal_path));
629 // Not excluded to start.
630 EXPECT_FALSE(base::mac::GetBackupExclusion(db_path_));
631 EXPECT_FALSE(base::mac::GetBackupExclusion(journal_path));
633 // Exclude the main database file.
634 EXPECT_TRUE(base::mac::SetBackupExclusion(db_path_));
636 EXPECT_TRUE(base::mac::GetBackupExclusion(db_path_));
637 EXPECT_FALSE(base::mac::GetBackupExclusion(journal_path));
639 EXPECT_TRUE(db_.Open(db_path_));
640 ASSERT_TRUE(db_.Execute("INSERT INTO t VALUES (1)"));
641 EXPECT_TRUE(base::mac::GetBackupExclusion(db_path_));
642 EXPECT_TRUE(base::mac::GetBackupExclusion(journal_path));
644 // TODO(shess): In WAL mode this will touch -wal and -shm files. -shm files
645 // could be always excluded.
649 #if !BUILDFLAG(IS_FUCHSIA)
650 // SQLite WAL mode defaults to checkpointing the WAL on close. This would push
651 // additional work into Chromium shutdown. Verify that SQLite supports a config
652 // option to not checkpoint on close.
653 TEST_F(SQLiteFeaturesTest, WALNoClose) {
654 base::FilePath wal_path = sql::Database::WriteAheadLogPath(db_path_);
656 // Turn on WAL mode, then verify that the mode changed (WAL is supported).
657 ASSERT_TRUE(db_.Execute("PRAGMA journal_mode = WAL"));
658 ASSERT_EQ("wal", ExecuteWithResult(&db_, "PRAGMA journal_mode"));
660 // The WAL file is created lazily on first change.
661 ASSERT_TRUE(db_.Execute("CREATE TABLE foo (a, b)"));
663 // By default, the WAL is checkpointed then deleted on close.
664 ASSERT_TRUE(base::PathExists(wal_path));
666 ASSERT_FALSE(base::PathExists(wal_path));
668 // Reopen and configure the database to not checkpoint WAL on close.
669 ASSERT_TRUE(Reopen());
670 ASSERT_TRUE(db_.Execute("PRAGMA journal_mode = WAL"));
671 ASSERT_TRUE(db_.Execute("ALTER TABLE foo ADD COLUMN c"));
674 sqlite3_db_config(db_.db_, SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, 1, nullptr));
675 ASSERT_TRUE(base::PathExists(wal_path));
677 ASSERT_TRUE(base::PathExists(wal_path));