sqlite_features_unittest.cc 26 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681
  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. #include <stddef.h>
  5. #include <stdint.h>
  6. #include <string>
  7. #include <tuple>
  8. #include "base/bind.h"
  9. #include "base/files/file_path.h"
  10. #include "base/files/file_util.h"
  11. #include "base/files/memory_mapped_file.h"
  12. #include "base/files/scoped_temp_dir.h"
  13. #include "build/build_config.h"
  14. #include "sql/database.h"
  15. #include "sql/statement.h"
  16. #include "sql/test/scoped_error_expecter.h"
  17. #include "sql/test/test_helpers.h"
  18. #include "testing/gtest/include/gtest/gtest.h"
  19. #include "third_party/sqlite/sqlite3.h"
  20. #if BUILDFLAG(IS_APPLE)
  21. #include "base/mac/backup_util.h"
  22. #endif
  23. // Test that certain features are/are-not enabled in our SQLite.
  24. namespace sql {
  25. namespace {
  26. using sql::test::ExecuteWithResult;
  27. using sql::test::ExecuteWithResults;
  28. } // namespace
  29. class SQLiteFeaturesTest : public testing::Test {
  30. public:
  31. ~SQLiteFeaturesTest() override = default;
  32. void SetUp() override {
  33. ASSERT_TRUE(temp_dir_.CreateUniqueTempDir());
  34. db_path_ = temp_dir_.GetPath().AppendASCII("sqlite_features_test.sqlite");
  35. ASSERT_TRUE(db_.Open(db_path_));
  36. }
  37. bool Reopen() {
  38. db_.Close();
  39. return db_.Open(db_path_);
  40. }
  41. protected:
  42. base::ScopedTempDir temp_dir_;
  43. base::FilePath db_path_;
  44. Database db_;
  45. // The error code of the most recent error.
  46. int error_ = SQLITE_OK;
  47. // Original statement which has caused the error.
  48. std::string sql_text_;
  49. };
  50. // Do not include fts1 support, it is not useful, and nobody is
  51. // looking at it.
  52. TEST_F(SQLiteFeaturesTest, NoFTS1) {
  53. sql::test::ScopedErrorExpecter expecter;
  54. expecter.ExpectError(SQLITE_ERROR);
  55. EXPECT_FALSE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts1(x)"));
  56. EXPECT_TRUE(expecter.SawExpectedErrors());
  57. }
  58. // Do not include fts2 support, it is not useful, and nobody is
  59. // looking at it.
  60. TEST_F(SQLiteFeaturesTest, NoFTS2) {
  61. sql::test::ScopedErrorExpecter expecter;
  62. expecter.ExpectError(SQLITE_ERROR);
  63. EXPECT_FALSE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts2(x)"));
  64. EXPECT_TRUE(expecter.SawExpectedErrors());
  65. }
  66. // fts3 is exposed in WebSQL.
  67. TEST_F(SQLiteFeaturesTest, FTS3) {
  68. EXPECT_TRUE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts3(x)"));
  69. }
  70. // Originally history used fts2, which Chromium patched to treat "foo*" as a
  71. // prefix search, though the icu tokenizer would return it as two tokens {"foo",
  72. // "*"}. Test that fts3 works correctly.
  73. TEST_F(SQLiteFeaturesTest, FTS3_Prefix) {
  74. db_.Close();
  75. sql::Database db({.enable_virtual_tables_discouraged = true});
  76. ASSERT_TRUE(db.Open(db_path_));
  77. static constexpr char kCreateSql[] =
  78. "CREATE VIRTUAL TABLE foo USING fts3(x, tokenize icu)";
  79. ASSERT_TRUE(db.Execute(kCreateSql));
  80. ASSERT_TRUE(db.Execute("INSERT INTO foo (x) VALUES ('test')"));
  81. EXPECT_EQ("test",
  82. ExecuteWithResult(&db, "SELECT x FROM foo WHERE x MATCH 'te*'"));
  83. }
  84. // Verify that Chromium's SQLite is compiled with HAVE_USLEEP defined. With
  85. // HAVE_USLEEP, SQLite uses usleep() with millisecond granularity. Otherwise it
  86. // uses sleep() with second granularity.
  87. TEST_F(SQLiteFeaturesTest, UsesUsleep) {
  88. base::TimeTicks before = base::TimeTicks::Now();
  89. sqlite3_sleep(1);
  90. base::TimeDelta delta = base::TimeTicks::Now() - before;
  91. // It is not impossible for this to be over 1000 if things are compiled
  92. // correctly, but that is very unlikely. Most platforms seem to be exactly
  93. // 1ms, with the rest at 2ms, and the worst observed cases was ASAN at 7ms.
  94. EXPECT_LT(delta.InMilliseconds(), 1000);
  95. }
  96. // Ensure that our SQLite version has working foreign key support with cascade
  97. // delete support.
  98. TEST_F(SQLiteFeaturesTest, ForeignKeySupport) {
  99. ASSERT_TRUE(db_.Execute("PRAGMA foreign_keys=1"));
  100. ASSERT_TRUE(db_.Execute("CREATE TABLE parents (id INTEGER PRIMARY KEY)"));
  101. ASSERT_TRUE(db_.Execute(
  102. "CREATE TABLE children ("
  103. " id INTEGER PRIMARY KEY,"
  104. " pid INTEGER NOT NULL REFERENCES parents(id) ON DELETE CASCADE)"));
  105. static const char kSelectParentsSql[] = "SELECT * FROM parents ORDER BY id";
  106. static const char kSelectChildrenSql[] = "SELECT * FROM children ORDER BY id";
  107. // Inserting without a matching parent should fail with constraint violation.
  108. EXPECT_EQ("", ExecuteWithResult(&db_, kSelectParentsSql));
  109. {
  110. sql::test::ScopedErrorExpecter expecter;
  111. expecter.ExpectError(SQLITE_CONSTRAINT | SQLITE_CONSTRAINT_FOREIGNKEY);
  112. EXPECT_FALSE(db_.Execute("INSERT INTO children VALUES (10, 1)"));
  113. EXPECT_TRUE(expecter.SawExpectedErrors());
  114. }
  115. EXPECT_EQ("", ExecuteWithResult(&db_, kSelectChildrenSql));
  116. // Inserting with a matching parent should work.
  117. ASSERT_TRUE(db_.Execute("INSERT INTO parents VALUES (1)"));
  118. EXPECT_EQ("1", ExecuteWithResults(&db_, kSelectParentsSql, "|", "\n"));
  119. EXPECT_TRUE(db_.Execute("INSERT INTO children VALUES (11, 1)"));
  120. EXPECT_TRUE(db_.Execute("INSERT INTO children VALUES (12, 1)"));
  121. EXPECT_EQ("11|1\n12|1",
  122. ExecuteWithResults(&db_, kSelectChildrenSql, "|", "\n"));
  123. // Deleting the parent should cascade, deleting the children as well.
  124. ASSERT_TRUE(db_.Execute("DELETE FROM parents"));
  125. EXPECT_EQ("", ExecuteWithResult(&db_, kSelectParentsSql));
  126. EXPECT_EQ("", ExecuteWithResult(&db_, kSelectChildrenSql));
  127. }
  128. // Ensure that our SQLite version supports booleans.
  129. TEST_F(SQLiteFeaturesTest, BooleanSupport) {
  130. ASSERT_TRUE(
  131. db_.Execute("CREATE TABLE flags ("
  132. " id INTEGER PRIMARY KEY,"
  133. " true_flag BOOL NOT NULL DEFAULT TRUE,"
  134. " false_flag BOOL NOT NULL DEFAULT FALSE)"));
  135. ASSERT_TRUE(db_.Execute(
  136. "ALTER TABLE flags ADD COLUMN true_flag2 BOOL NOT NULL DEFAULT TRUE"));
  137. ASSERT_TRUE(db_.Execute(
  138. "ALTER TABLE flags ADD COLUMN false_flag2 BOOL NOT NULL DEFAULT FALSE"));
  139. ASSERT_TRUE(db_.Execute("INSERT INTO flags (id) VALUES (1)"));
  140. sql::Statement s(db_.GetUniqueStatement(
  141. "SELECT true_flag, false_flag, true_flag2, false_flag2"
  142. " FROM flags WHERE id=1;"));
  143. ASSERT_TRUE(s.Step());
  144. EXPECT_TRUE(s.ColumnBool(0)) << " default TRUE at table creation time";
  145. EXPECT_TRUE(!s.ColumnBool(1)) << " default FALSE at table creation time";
  146. EXPECT_TRUE(s.ColumnBool(2)) << " default TRUE added by altering the table";
  147. EXPECT_TRUE(!s.ColumnBool(3)) << " default FALSE added by altering the table";
  148. }
  149. TEST_F(SQLiteFeaturesTest, IcuEnabled) {
  150. sql::Statement lower_en(db_.GetUniqueStatement("SELECT lower('I', 'en_us')"));
  151. ASSERT_TRUE(lower_en.Step());
  152. EXPECT_EQ("i", lower_en.ColumnString(0));
  153. sql::Statement lower_tr(db_.GetUniqueStatement("SELECT lower('I', 'tr_tr')"));
  154. ASSERT_TRUE(lower_tr.Step());
  155. EXPECT_EQ("\u0131", lower_tr.ColumnString(0));
  156. }
  157. // Verify that OS file writes are reflected in the memory mapping of a
  158. // memory-mapped file. Normally SQLite writes to memory-mapped files using
  159. // memcpy(), which should stay consistent. Our SQLite is slightly patched to
  160. // mmap read only, then write using OS file writes. If the memory-mapped
  161. // version doesn't reflect the OS file writes, SQLite's memory-mapped I/O should
  162. // be disabled on this platform using SQLITE_MAX_MMAP_SIZE=0.
  163. TEST_F(SQLiteFeaturesTest, Mmap) {
  164. // Try to turn on mmap'ed I/O.
  165. std::ignore = db_.Execute("PRAGMA mmap_size = 1048576");
  166. {
  167. sql::Statement s(db_.GetUniqueStatement("PRAGMA mmap_size"));
  168. ASSERT_TRUE(s.Step());
  169. ASSERT_GT(s.ColumnInt64(0), 0);
  170. }
  171. db_.Close();
  172. const uint32_t kFlags =
  173. base::File::FLAG_OPEN | base::File::FLAG_READ | base::File::FLAG_WRITE;
  174. char buf[4096];
  175. // Create a file with a block of '0', a block of '1', and a block of '2'.
  176. {
  177. base::File f(db_path_, kFlags);
  178. ASSERT_TRUE(f.IsValid());
  179. memset(buf, '0', sizeof(buf));
  180. ASSERT_EQ(f.Write(0*sizeof(buf), buf, sizeof(buf)), (int)sizeof(buf));
  181. memset(buf, '1', sizeof(buf));
  182. ASSERT_EQ(f.Write(1*sizeof(buf), buf, sizeof(buf)), (int)sizeof(buf));
  183. memset(buf, '2', sizeof(buf));
  184. ASSERT_EQ(f.Write(2*sizeof(buf), buf, sizeof(buf)), (int)sizeof(buf));
  185. }
  186. // mmap the file and verify that everything looks right.
  187. {
  188. base::MemoryMappedFile m;
  189. ASSERT_TRUE(m.Initialize(db_path_));
  190. memset(buf, '0', sizeof(buf));
  191. ASSERT_EQ(0, memcmp(buf, m.data() + 0*sizeof(buf), sizeof(buf)));
  192. memset(buf, '1', sizeof(buf));
  193. ASSERT_EQ(0, memcmp(buf, m.data() + 1*sizeof(buf), sizeof(buf)));
  194. memset(buf, '2', sizeof(buf));
  195. ASSERT_EQ(0, memcmp(buf, m.data() + 2*sizeof(buf), sizeof(buf)));
  196. // Scribble some '3' into the first page of the file, and verify that it
  197. // looks the same in the memory mapping.
  198. {
  199. base::File f(db_path_, kFlags);
  200. ASSERT_TRUE(f.IsValid());
  201. memset(buf, '3', sizeof(buf));
  202. ASSERT_EQ(f.Write(0*sizeof(buf), buf, sizeof(buf)), (int)sizeof(buf));
  203. }
  204. ASSERT_EQ(0, memcmp(buf, m.data() + 0*sizeof(buf), sizeof(buf)));
  205. // Repeat with a single '4' in case page-sized blocks are different.
  206. const size_t kOffset = 1*sizeof(buf) + 123;
  207. ASSERT_NE('4', m.data()[kOffset]);
  208. {
  209. base::File f(db_path_, kFlags);
  210. ASSERT_TRUE(f.IsValid());
  211. buf[0] = '4';
  212. ASSERT_EQ(f.Write(kOffset, buf, 1), 1);
  213. }
  214. ASSERT_EQ('4', m.data()[kOffset]);
  215. }
  216. }
  217. // Verify that http://crbug.com/248608 is fixed. In this bug, the
  218. // compiled regular expression is effectively cached with the prepared
  219. // statement, causing errors if the regular expression is rebound.
  220. TEST_F(SQLiteFeaturesTest, CachedRegexp) {
  221. ASSERT_TRUE(db_.Execute("CREATE TABLE r (id INTEGER UNIQUE, x TEXT)"));
  222. ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (1, 'this is a test')"));
  223. ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (2, 'that was a test')"));
  224. ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (3, 'this is a stickup')"));
  225. ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (4, 'that sucks')"));
  226. static const char kSimpleSql[] = "SELECT SUM(id) FROM r WHERE x REGEXP ?";
  227. sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE, kSimpleSql));
  228. s.BindString(0, "this.*");
  229. ASSERT_TRUE(s.Step());
  230. EXPECT_EQ(4, s.ColumnInt(0));
  231. s.Reset(true);
  232. s.BindString(0, "that.*");
  233. ASSERT_TRUE(s.Step());
  234. EXPECT_EQ(6, s.ColumnInt(0));
  235. s.Reset(true);
  236. s.BindString(0, ".*test");
  237. ASSERT_TRUE(s.Step());
  238. EXPECT_EQ(3, s.ColumnInt(0));
  239. s.Reset(true);
  240. s.BindString(0, ".* s[a-z]+");
  241. ASSERT_TRUE(s.Step());
  242. EXPECT_EQ(7, s.ColumnInt(0));
  243. }
  244. TEST_F(SQLiteFeaturesTest, JsonIsDisabled) {
  245. static constexpr char kCreateSql[] =
  246. "CREATE TABLE rows(id INTEGER PRIMARY KEY NOT NULL, data TEXT NOT NULL)";
  247. ASSERT_TRUE(db_.Execute(kCreateSql));
  248. ASSERT_TRUE(db_.Execute("INSERT INTO rows(data) VALUES('{\"a\": 1}')"));
  249. {
  250. sql::test::ScopedErrorExpecter expecter;
  251. expecter.ExpectError(SQLITE_ERROR);
  252. EXPECT_FALSE(db_.Execute("SELECT data -> '$.a' FROM rows"));
  253. EXPECT_TRUE(expecter.SawExpectedErrors());
  254. }
  255. }
  256. TEST_F(SQLiteFeaturesTest, WindowFunctionsAreDisabled) {
  257. static constexpr char kCreateSql[] =
  258. "CREATE TABLE rows(id INTEGER PRIMARY KEY NOT NULL, data TEXT NOT NULL)";
  259. ASSERT_TRUE(db_.Execute(kCreateSql));
  260. ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(1, 'a')"));
  261. ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(2, 'c')"));
  262. ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(3, 'b')"));
  263. {
  264. sql::test::ScopedErrorExpecter expecter;
  265. expecter.ExpectError(SQLITE_ERROR);
  266. EXPECT_FALSE(db_.Execute(
  267. "SELECT data, row_number() OVER (ORDER BY data) AS rank FROM rows "
  268. "ORDER BY id"));
  269. EXPECT_TRUE(expecter.SawExpectedErrors());
  270. }
  271. }
  272. // The "No Isolation Between Operations On The Same Database Connection" section
  273. // in https://sqlite.org/isolation.html implies that it's safe to issue multiple
  274. // concurrent SELECTs against the same area.
  275. //
  276. // Chrome code is allowed to rely on this guarantee. So, we test for it here, to
  277. // catch any regressions introduced by SQLite upgrades.
  278. TEST_F(SQLiteFeaturesTest, ConcurrentSelects) {
  279. ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
  280. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
  281. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
  282. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
  283. static const char kSelectAllSql[] = "SELECT id,t FROM rows";
  284. static const char kSelectEvenSql[] = "SELECT id,t FROM rows WHERE id%2=0";
  285. sql::Statement select1(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
  286. sql::Statement select2(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
  287. sql::Statement select3(db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
  288. ASSERT_TRUE(select1.Step());
  289. EXPECT_EQ(select1.ColumnInt(0), 2);
  290. EXPECT_EQ(select1.ColumnString(1), "two");
  291. ASSERT_TRUE(select2.Step());
  292. EXPECT_EQ(select2.ColumnInt(0), 2);
  293. EXPECT_EQ(select2.ColumnString(1), "two");
  294. ASSERT_TRUE(select3.Step());
  295. EXPECT_EQ(select3.ColumnInt(0), 2);
  296. EXPECT_EQ(select3.ColumnString(1), "two");
  297. ASSERT_TRUE(select1.Step());
  298. EXPECT_EQ(select1.ColumnInt(0), 4);
  299. EXPECT_EQ(select1.ColumnString(1), "four");
  300. ASSERT_TRUE(select3.Step());
  301. EXPECT_EQ(select3.ColumnInt(0), 3);
  302. EXPECT_EQ(select3.ColumnString(1), "three");
  303. ASSERT_TRUE(select2.Step());
  304. EXPECT_EQ(select2.ColumnInt(0), 4);
  305. EXPECT_EQ(select2.ColumnString(1), "four");
  306. EXPECT_FALSE(select2.Step());
  307. ASSERT_TRUE(select3.Step());
  308. EXPECT_EQ(select3.ColumnInt(0), 4);
  309. EXPECT_EQ(select3.ColumnString(1), "four");
  310. select2.Reset(/*clear_bound_vars=*/true);
  311. ASSERT_TRUE(select2.Step());
  312. EXPECT_EQ(select2.ColumnInt(0), 2);
  313. EXPECT_EQ(select2.ColumnString(1), "two");
  314. EXPECT_FALSE(select1.Step());
  315. }
  316. // The "No Isolation Between Operations On The Same Database Connection" section
  317. // in https://sqlite.org/isolation.html states that it's safe to DELETE a row
  318. // that was just returned by sqlite_step() executing a SELECT statement.
  319. //
  320. // Chrome code is allowed to rely on this guarantee. So, we test for it here, to
  321. // catch any regressions introduced by SQLite upgrades.
  322. TEST_F(SQLiteFeaturesTest, DeleteCurrentlySelectedRow) {
  323. ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
  324. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
  325. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
  326. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
  327. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(5, 'five')"));
  328. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(6, 'six')"));
  329. static const char kSelectEvenSql[] = "SELECT id,t FROM rows WHERE id%2=0";
  330. sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
  331. ASSERT_TRUE(select.Step());
  332. ASSERT_EQ(select.ColumnInt(0), 2);
  333. ASSERT_EQ(select.ColumnString(1), "two");
  334. ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=2"));
  335. ASSERT_TRUE(select.Step());
  336. ASSERT_EQ(select.ColumnInt(0), 4);
  337. ASSERT_EQ(select.ColumnString(1), "four");
  338. ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=4"));
  339. ASSERT_TRUE(select.Step());
  340. ASSERT_EQ(select.ColumnInt(0), 6);
  341. ASSERT_EQ(select.ColumnString(1), "six");
  342. ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=6"));
  343. EXPECT_FALSE(select.Step());
  344. // Check that the DELETEs were applied as expected.
  345. static const char kSelectAllSql[] = "SELECT id,t FROM rows";
  346. sql::Statement select_all(
  347. db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
  348. std::vector<int> remaining_ids;
  349. std::vector<std::string> remaining_texts;
  350. while (select_all.Step()) {
  351. remaining_ids.push_back(select_all.ColumnInt(0));
  352. remaining_texts.push_back(select_all.ColumnString(1));
  353. }
  354. std::vector<int> expected_remaining_ids = {3, 5};
  355. EXPECT_EQ(expected_remaining_ids, remaining_ids);
  356. std::vector<std::string> expected_remaining_texts = {"three", "five"};
  357. EXPECT_EQ(expected_remaining_texts, remaining_texts);
  358. }
  359. // The "No Isolation Between Operations On The Same Database Connection" section
  360. // in https://sqlite.org/isolation.html states that it's safe to DELETE a row
  361. // that was previously by sqlite_step() executing a SELECT statement.
  362. //
  363. // Chrome code is allowed to rely on this guarantee. So, we test for it here, to
  364. // catch any regressions introduced by SQLite upgrades.
  365. TEST_F(SQLiteFeaturesTest, DeletePreviouslySelectedRows) {
  366. ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
  367. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
  368. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
  369. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
  370. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(5, 'five')"));
  371. ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(6, 'six')"));
  372. static const char kSelectEvenSql[] = "SELECT id,t FROM rows WHERE id%2=0";
  373. sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
  374. ASSERT_TRUE(select.Step());
  375. ASSERT_EQ(select.ColumnInt(0), 2);
  376. ASSERT_EQ(select.ColumnString(1), "two");
  377. ASSERT_TRUE(select.Step());
  378. ASSERT_EQ(select.ColumnInt(0), 4);
  379. ASSERT_EQ(select.ColumnString(1), "four");
  380. ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=2"));
  381. ASSERT_TRUE(select.Step());
  382. ASSERT_EQ(select.ColumnInt(0), 6);
  383. ASSERT_EQ(select.ColumnString(1), "six");
  384. ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=4"));
  385. ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=6"));
  386. EXPECT_FALSE(select.Step());
  387. // Check that the DELETEs were applied as expected.
  388. static const char kSelectAllSql[] = "SELECT id,t FROM rows";
  389. sql::Statement select_all(
  390. db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
  391. std::vector<int> remaining_ids;
  392. std::vector<std::string> remaining_texts;
  393. while (select_all.Step()) {
  394. remaining_ids.push_back(select_all.ColumnInt(0));
  395. remaining_texts.push_back(select_all.ColumnString(1));
  396. }
  397. std::vector<int> expected_remaining_ids = {3, 5};
  398. EXPECT_EQ(expected_remaining_ids, remaining_ids);
  399. std::vector<std::string> expected_remaining_texts = {"three", "five"};
  400. EXPECT_EQ(expected_remaining_texts, remaining_texts);
  401. }
  402. // The "No Isolation Between Operations On The Same Database Connection" section
  403. // in https://sqlite.org/isolation.html states that it's safe to DELETE a row
  404. // while a SELECT statement executes, but the DELETEd row may or may not show up
  405. // in the SELECT results. (See the test above for a case where the DELETEd row
  406. // is guaranteed to now show up in the SELECT results.)
  407. //
  408. // This seems to imply that DELETEing from a table that is not read by the
  409. // concurrent SELECT statement is safe and well-defined, as the DELETEd row(s)
  410. // cannot possibly show up in the SELECT results.
  411. //
  412. // Chrome features are allowed to rely on the implication above, because it
  413. // comes in very handy for DELETEing data across multiple tables. This test
  414. // ensures that our assumption remains valid.
  415. TEST_F(SQLiteFeaturesTest, DeleteWhileSelectingFromDifferentTable) {
  416. ASSERT_TRUE(db_.Execute("CREATE TABLE main(id INTEGER PRIMARY KEY, t TEXT)"));
  417. ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(2, 'two')"));
  418. ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(3, 'three')"));
  419. ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(4, 'four')"));
  420. ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(5, 'five')"));
  421. ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(6, 'six')"));
  422. ASSERT_TRUE(
  423. db_.Execute("CREATE TABLE other(id INTEGER PRIMARY KEY, t TEXT)"));
  424. ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(1, 'one')"));
  425. ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(2, 'two')"));
  426. ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(3, 'three')"));
  427. ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(4, 'four')"));
  428. ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(5, 'five')"));
  429. ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(6, 'six')"));
  430. ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(7, 'seven')"));
  431. static const char kSelectEvenSql[] = "SELECT id,t FROM main WHERE id%2=0";
  432. sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
  433. ASSERT_TRUE(select.Step());
  434. ASSERT_EQ(select.ColumnInt(0), 2);
  435. ASSERT_EQ(select.ColumnString(1), "two");
  436. EXPECT_TRUE(db_.Execute("DELETE FROM other WHERE id=2"));
  437. ASSERT_TRUE(select.Step());
  438. ASSERT_EQ(select.ColumnInt(0), 4);
  439. ASSERT_EQ(select.ColumnString(1), "four");
  440. ASSERT_TRUE(select.Step());
  441. ASSERT_EQ(select.ColumnInt(0), 6);
  442. ASSERT_EQ(select.ColumnString(1), "six");
  443. ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=4"));
  444. ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=5"));
  445. ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=6"));
  446. EXPECT_FALSE(select.Step());
  447. // Check that the DELETEs were applied as expected.
  448. static const char kSelectAllSql[] = "SELECT id,t FROM other";
  449. sql::Statement select_all(
  450. db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
  451. std::vector<int> remaining_ids;
  452. std::vector<std::string> remaining_texts;
  453. while (select_all.Step()) {
  454. remaining_ids.push_back(select_all.ColumnInt(0));
  455. remaining_texts.push_back(select_all.ColumnString(1));
  456. }
  457. std::vector<int> expected_remaining_ids = {1, 3, 7};
  458. EXPECT_EQ(expected_remaining_ids, remaining_ids);
  459. std::vector<std::string> expected_remaining_texts = {"one", "three", "seven"};
  460. EXPECT_EQ(expected_remaining_texts, remaining_texts);
  461. }
  462. // The "No Isolation Between Operations On The Same Database Connection" section
  463. // in https://sqlite.org/isolation.html states that it's possible to INSERT in
  464. // a table while concurrently executing a SELECT statement reading from it, but
  465. // it's undefined whether the row will show up in the SELECT statement's results
  466. // or not.
  467. //
  468. // Given this ambiguity, Chrome code is not allowed to INSERT in the same table
  469. // as a concurrent SELECT. However, it is allowed to INSERT in a table which is
  470. // not covered by SELECT, because this greatly simplifes migrations. So, we test
  471. // the ability to INSERT in a table while SELECTing from another table, to
  472. // catch any regressions introduced by SQLite upgrades.
  473. TEST_F(SQLiteFeaturesTest, InsertWhileSelectingFromDifferentTable) {
  474. ASSERT_TRUE(db_.Execute("CREATE TABLE src(id INTEGER PRIMARY KEY, t TEXT)"));
  475. ASSERT_TRUE(db_.Execute("CREATE TABLE dst(id INTEGER PRIMARY KEY, t TEXT)"));
  476. ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(2, 'two')"));
  477. ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(3, 'three')"));
  478. ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(4, 'four')"));
  479. ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(5, 'five')"));
  480. ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(6, 'six')"));
  481. static const char kSelectSrcEvenSql[] = "SELECT id,t FROM src WHERE id%2=0";
  482. sql::Statement select_src(
  483. db_.GetCachedStatement(SQL_FROM_HERE, kSelectSrcEvenSql));
  484. ASSERT_TRUE(select_src.Step());
  485. ASSERT_EQ(select_src.ColumnInt(0), 2);
  486. ASSERT_EQ(select_src.ColumnString(1), "two");
  487. EXPECT_TRUE(db_.Execute("INSERT INTO dst VALUES(2, 'two')"));
  488. ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(3, 'three')"));
  489. ASSERT_TRUE(select_src.Step());
  490. ASSERT_EQ(select_src.ColumnInt(0), 4);
  491. ASSERT_EQ(select_src.ColumnString(1), "four");
  492. ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(4, 'four')"));
  493. ASSERT_TRUE(select_src.Step());
  494. ASSERT_EQ(select_src.ColumnInt(0), 6);
  495. ASSERT_EQ(select_src.ColumnString(1), "six");
  496. ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(5, 'five')"));
  497. ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(6, 'six')"));
  498. EXPECT_FALSE(select_src.Step());
  499. static const char kSelectDstSql[] = "SELECT id,t FROM dst";
  500. sql::Statement select_dst(
  501. db_.GetCachedStatement(SQL_FROM_HERE, kSelectDstSql));
  502. std::vector<int> dst_ids;
  503. std::vector<std::string> dst_texts;
  504. while (select_dst.Step()) {
  505. dst_ids.push_back(select_dst.ColumnInt(0));
  506. dst_texts.push_back(select_dst.ColumnString(1));
  507. }
  508. std::vector<int> expected_dst_ids = {2, 3, 4, 5, 6};
  509. EXPECT_EQ(expected_dst_ids, dst_ids);
  510. std::vector<std::string> expected_dst_texts = {"two", "three", "four", "five",
  511. "six"};
  512. EXPECT_EQ(expected_dst_texts, dst_texts);
  513. }
  514. #if BUILDFLAG(IS_APPLE)
  515. // If a database file is marked to be excluded from backups, verify that journal
  516. // files are also excluded.
  517. TEST_F(SQLiteFeaturesTest, TimeMachine) {
  518. ASSERT_TRUE(db_.Execute("CREATE TABLE t (id INTEGER PRIMARY KEY)"));
  519. db_.Close();
  520. base::FilePath journal_path = sql::Database::JournalPath(db_path_);
  521. ASSERT_TRUE(base::PathExists(db_path_));
  522. ASSERT_TRUE(base::PathExists(journal_path));
  523. // Not excluded to start.
  524. EXPECT_FALSE(base::mac::GetBackupExclusion(db_path_));
  525. EXPECT_FALSE(base::mac::GetBackupExclusion(journal_path));
  526. // Exclude the main database file.
  527. EXPECT_TRUE(base::mac::SetBackupExclusion(db_path_));
  528. EXPECT_TRUE(base::mac::GetBackupExclusion(db_path_));
  529. EXPECT_FALSE(base::mac::GetBackupExclusion(journal_path));
  530. EXPECT_TRUE(db_.Open(db_path_));
  531. ASSERT_TRUE(db_.Execute("INSERT INTO t VALUES (1)"));
  532. EXPECT_TRUE(base::mac::GetBackupExclusion(db_path_));
  533. EXPECT_TRUE(base::mac::GetBackupExclusion(journal_path));
  534. // TODO(shess): In WAL mode this will touch -wal and -shm files. -shm files
  535. // could be always excluded.
  536. }
  537. #endif
  538. #if !BUILDFLAG(IS_FUCHSIA)
  539. // SQLite WAL mode defaults to checkpointing the WAL on close. This would push
  540. // additional work into Chromium shutdown. Verify that SQLite supports a config
  541. // option to not checkpoint on close.
  542. TEST_F(SQLiteFeaturesTest, WALNoClose) {
  543. base::FilePath wal_path = sql::Database::WriteAheadLogPath(db_path_);
  544. // Turn on WAL mode, then verify that the mode changed (WAL is supported).
  545. ASSERT_TRUE(db_.Execute("PRAGMA journal_mode = WAL"));
  546. ASSERT_EQ("wal", ExecuteWithResult(&db_, "PRAGMA journal_mode"));
  547. // The WAL file is created lazily on first change.
  548. ASSERT_TRUE(db_.Execute("CREATE TABLE foo (a, b)"));
  549. // By default, the WAL is checkpointed then deleted on close.
  550. ASSERT_TRUE(base::PathExists(wal_path));
  551. db_.Close();
  552. ASSERT_FALSE(base::PathExists(wal_path));
  553. // Reopen and configure the database to not checkpoint WAL on close.
  554. ASSERT_TRUE(Reopen());
  555. ASSERT_TRUE(db_.Execute("PRAGMA journal_mode = WAL"));
  556. ASSERT_TRUE(db_.Execute("ALTER TABLE foo ADD COLUMN c"));
  557. ASSERT_EQ(
  558. SQLITE_OK,
  559. sqlite3_db_config(db_.db_, SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, 1, nullptr));
  560. ASSERT_TRUE(base::PathExists(wal_path));
  561. db_.Close();
  562. ASSERT_TRUE(base::PathExists(wal_path));
  563. }
  564. #endif
  565. } // namespace sql