Skip to content
rachel/workerd
File

Blob: src/workerd/util/sqlite-test.c++

55.2 KB
1// Copyright (c) 2023 Cloudflare, Inc.
2// Licensed under the Apache 2.0 license found in the LICENSE file or at:
3//     https://opensource.org/licenses/Apache-2.0
4 
5#include "sqlite.h"
6 
7#include <fcntl.h>
8 
9#include <kj/refcount.h>
10#include <kj/test.h>
11#include <kj/thread.h>
12 
13#include <atomic>
14#include <cerrno>
15#include <cstdint>
16#include <cstdlib>
17 
18#if _WIN32
19#include <io.h>
20#else
21#include <unistd.h>
22#endif
23 
24namespace workerd {
25namespace {
26 
27struct GlobalInit {
28  GlobalInit() {
29    installSqliteCustomAllocator();
30  }
31};
32 
33static GlobalInit init;
34 
35// Initialize the database with some data.
36void setupSql(SqliteDatabase& db) {
37  // TODO(sqlite): Do this automatically and don't permit it via run().
38  db.run("PRAGMA journal_mode=WAL;");
39 
40  {
41    auto query = db.run(R"(
42      CREATE TABLE people (
43        id INTEGER PRIMARY KEY,
44        name TEXT NOT NULL,
45        email TEXT NOT NULL UNIQUE
46      );
47 
48      INSERT INTO people (id, name, email)
49      VALUES (?, ?, ?),
50            (?, ?, ?);
51    )",
52        123, "Bob"_kj, "bob@example.com"_kj, 321, "Alice"_kj, "alice@example.com"_kj);
53 
54    KJ_EXPECT(query.changeCount() == 2);
55  }
56}
57 
58// Do some read-only queries on `db` to check that it's in the state that `setupSql()` ought to
59// have left it in.
60void checkSql(SqliteDatabase& db) {
61  {
62    auto query = db.run("SELECT * FROM people ORDER BY name");
63 
64    KJ_ASSERT(!query.isDone());
65    KJ_ASSERT(query.columnCount() == 3);
66    KJ_EXPECT(query.getInt(0) == 321);
67    KJ_EXPECT(query.getText(1) == "Alice");
68    KJ_EXPECT(query.getText(2) == "alice@example.com");
69 
70    query.nextRow();
71    KJ_ASSERT(!query.isDone());
72    KJ_EXPECT(query.getInt(0) == 123);
73    KJ_EXPECT(query.getText(1) == "Bob");
74    KJ_EXPECT(query.getText(2) == "bob@example.com");
75 
76    query.nextRow();
77    KJ_EXPECT(query.isDone());
78  }
79 
80  {
81    auto query = db.run("SELECT * FROM people WHERE people.id = ?", 123l);
82 
83    KJ_ASSERT(!query.isDone());
84    KJ_ASSERT(query.columnCount() == 3);
85    KJ_EXPECT(query.getInt(0) == 123);
86    KJ_EXPECT(query.getText(1) == "Bob");
87    KJ_EXPECT(query.getText(2) == "bob@example.com");
88 
89    query.nextRow();
90    KJ_EXPECT(query.isDone());
91  }
92 
93  {
94    auto query = db.run("SELECT * FROM people WHERE people.name = ?", "Alice"_kj);
95 
96    KJ_ASSERT(!query.isDone());
97    KJ_ASSERT(query.columnCount() == 3);
98    KJ_EXPECT(query.getInt(0) == 321);
99    KJ_EXPECT(query.getText(1) == "Alice");
100    KJ_EXPECT(query.getText(2) == "alice@example.com");
101 
102    query.nextRow();
103    KJ_EXPECT(query.isDone());
104  }
105}
106 
107KJ_TEST("SQLite backed by in-memory directory") {
108  auto dir = kj::newInMemoryDirectory(kj::nullClock());
109  SqliteDatabase::Vfs vfs(*dir);
110 
111  {
112    SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
113 
114    setupSql(db);
115    checkSql(db);
116 
117    {
118      auto files = dir->listNames();
119      KJ_ASSERT(files.size() == 2);
120      KJ_EXPECT(files[0] == "foo");
121      KJ_EXPECT(files[1] == "foo-wal");
122    }
123  }
124 
125  {
126    auto files = dir->listNames();
127    KJ_ASSERT(files.size() == 1);
128    KJ_EXPECT(files[0] == "foo");
129  }
130 
131  // Open it again and make sure the data is still there!
132  {
133    SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::MODIFY);
134 
135    checkSql(db);
136  }
137 
138  // Check read-only-mode.
139  {
140    SqliteDatabase db(vfs, kj::Path({"foo"}));
141    checkSql(db);
142    KJ_EXPECT_THROW_MESSAGE("attempt to write a readonly database",
143        db.run("INSERT INTO people (id, name, email) VALUES (?, ?, ?);", 234, "Carol"_kj,
144            "carol@example.com"));
145  }
146}
147 
148class TempDirOnDisk {
149 public:
150  TempDirOnDisk() {}
151  ~TempDirOnDisk() noexcept(false) {
152    dir = nullptr;
153    disk->getRoot().remove(path);
154  }
155 
156  const kj::Directory* operator->() {
157    return dir;
158  }
159  const kj::Directory& operator*() {
160    return *dir;
161  }
162 
163 private:
164  kj::Own<kj::Filesystem> disk = kj::newDiskFilesystem();
165  kj::Path path = makeTmpPath();
166  kj::Own<const kj::Directory> dir = disk->getRoot().openSubdir(path, kj::WriteMode::MODIFY);
167 
168  kj::Path makeTmpPath() {
169    const char* tmpDir = getenv("TEST_TMPDIR");
170    kj::String pathStr =
171        kj::str(tmpDir != nullptr ? tmpDir : "/var/tmp", "/workerd-sqlite-test.XXXXXX");
172#if _WIN32
173    if (_mktemp(pathStr.begin()) == nullptr) {
174      KJ_FAIL_SYSCALL("_mktemp", errno, pathStr);
175    }
176    auto path = disk->getCurrentPath().evalNative(pathStr);
177    disk->getRoot().openSubdir(
178        path, kj::WriteMode::CREATE | kj::WriteMode::MODIFY | kj::WriteMode::CREATE_PARENT);
179    return path;
180#else
181    if (mkdtemp(pathStr.begin()) == nullptr) {
182      KJ_FAIL_SYSCALL("mkdtemp", errno, pathStr);
183    }
184    return disk->getCurrentPath().evalNative(pathStr);
185#endif
186  }
187};
188 
189KJ_TEST("SQLite backed by real disk") {
190  // Well, I made it possible to use an in-memory directory so that unit tests wouldn't have to
191  // use real disk. But now I have to test that it does actually work on real disk. So here we are,
192  // in a unit test, using real disk.
193 
194  TempDirOnDisk dir;
195  SqliteDatabase::Vfs vfs(*dir);
196 
197  {
198    SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
199 
200    setupSql(db);
201    checkSql(db);
202 
203    {
204      auto files = dir->listNames();
205      KJ_ASSERT(files.size() == 3);
206      KJ_EXPECT(files[0] == "foo");
207      KJ_EXPECT(files[1] == "foo-shm");
208      KJ_EXPECT(files[2] == "foo-wal");
209    }
210  }
211 
212  {
213    auto files = dir->listNames();
214    KJ_ASSERT(files.size() == 1);
215    KJ_EXPECT(files[0] == "foo");
216  }
217 
218  // Open it again and make sure the data is still there!
219  {
220    SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::MODIFY);
221 
222    checkSql(db);
223  }
224 
225  // Check read-only-mode.
226  {
227    SqliteDatabase db(vfs, kj::Path({"foo"}));
228 
229    checkSql(db);
230    KJ_EXPECT_THROW_MESSAGE("attempt to write a readonly database",
231        db.run("INSERT INTO people (id, name, email) VALUES (?, ?, ?);", 234, "Carol"_kj,
232            "carol@example.com"));
233  }
234}
235 
236// Tests that a read-only database client picks up changes made to the database by a read/write
237// client.
238void doReadOnlyUpdateTest(const kj::Directory& dir) {
239  SqliteDatabase::Vfs vfs(dir);
240 
241  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
242 
243  setupSql(db);
244  checkSql(db);
245 
246  SqliteDatabase rodb(vfs, kj::Path({"foo"}));
247  checkSql(rodb);
248 
249  uint64_t startWalSize = 0;
250  {
251    auto file = KJ_ASSERT_NONNULL(dir.tryOpenFile(kj::Path({"foo-wal"})));
252    startWalSize = file->stat().size;
253  }
254 
255  db.run("INSERT INTO people (id, name, email) VALUES (?, ?, ?);", 234, "Carol"_kj,
256      "carol@example.com");
257 
258  {
259    // Make sure there's we added some WAL, since that's where the read-only database will have to
260    // read new rows from.
261    auto file = KJ_ASSERT_NONNULL(dir.tryOpenFile(kj::Path({"foo-wal"})));
262    KJ_EXPECT(file->stat().size > startWalSize);
263  }
264 
265  {
266    auto query = db.run("SELECT COUNT(*) FROM people");
267    KJ_EXPECT(query.getInt(0) == 3);
268  }
269 
270  {
271    auto query = rodb.run("SELECT COUNT(*) FROM people");
272    KJ_EXPECT(query.getInt(0) == 3);
273  }
274}
275 
276KJ_TEST("Read-only database picks up on changes from mutable database (in-memory)") {
277  auto dir = kj::newInMemoryDirectory(kj::nullClock());
278  doReadOnlyUpdateTest(*dir);
279}
280 
281KJ_TEST("Read-only database picks up on changes from mutable database (on-disk)") {
282#if !_WIN32
283  // doReadOnlyUpdateTest triggers the subsequent error in our memory metering because it creates
284  // two databases in the same thread. This will not happen in production. The on-disk test variant
285  // is impacted because it uses SQLite's native OS VFS, which can enable shared memory.
286  KJ_EXPECT_LOG(ERROR, "sqliteMemFree would have triggered a memoryBytes underflow.");
287#endif
288  TempDirOnDisk dir;
289  doReadOnlyUpdateTest(*dir);
290}
291 
292KJ_TEST("In-memory read-only crash regression") {
293  auto dir = kj::newInMemoryDirectory(kj::nullClock());
294  SqliteDatabase::Vfs vfs(*dir);
295 
296  {
297    SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
298    setupSql(db);
299    checkSql(db);
300  }
301 
302  // When using the in-memory file system, if we first create a read-only database
303  kj::Maybe<SqliteDatabase> rodb;
304  rodb.emplace(vfs, kj::Path({"foo"}));
305  checkSql(KJ_ASSERT_NONNULL(rodb));
306 
307  // then create a read/write database
308  kj::Maybe<SqliteDatabase> db;
309  db.emplace(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
310  checkSql(KJ_ASSERT_NONNULL(db));
311 
312  // then write into the read/write database:
313  KJ_ASSERT_NONNULL(db).run("INSERT INTO people (id, name, email) VALUES (?, ?, ?);", 234,
314      "Carol"_kj, "carol@example.com");
315 
316  // we can destroy the read/write database with no problems,
317  db = kj::none;
318 
319  // but we would crash when destroying the read-only database:
320  rodb = kj::none;
321}
322 
323// Tests that concurrent database clients don't clobber each other. This verifies that the
324// LockManager interface is able to protect concurrent access and that our default implementation
325// works.
326void doLockTest(bool walMode) {
327  auto dir = kj::newInMemoryDirectory(kj::nullClock());
328  SqliteDatabase::Vfs vfs(*dir);
329 
330  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
331 
332  if (walMode) {
333    db.run("PRAGMA journal_mode=WAL;");
334  }
335 
336  db.run(R"(
337    CREATE TABLE foo (
338      id INTEGER PRIMARY KEY,
339      counter INTEGER
340    );
341 
342    INSERT INTO foo VALUES (0, 1)
343  )");
344 
345  static constexpr char GET_COUNT[] = "SELECT counter FROM foo WHERE id = 0";
346  static constexpr char INCREMENT[] = "UPDATE foo SET counter = counter + 1 WHERE id = 0";
347 
348  KJ_EXPECT(db.run(GET_COUNT).getInt(0) == 1);
349 
350  // Concurrent write allowed, as long as we're not writing at the same time.
351  // Deliberately not assigning this to a variable: We want to create a thread and join it
352  // immediately.
353  // NOLINTNEXTLINE(bugprone-unused-raii)
354  kj::Thread([&vfs = vfs]() noexcept {
355    SqliteDatabase db2(vfs, kj::Path({"foo"}), kj::WriteMode::MODIFY);
356    KJ_EXPECT(db2.run(GET_COUNT).getInt(0) == 1);
357    db2.run(INCREMENT);
358    KJ_EXPECT(db2.run(GET_COUNT).getInt(0) == 2);
359  });
360 
361  KJ_EXPECT(db.run(GET_COUNT).getInt(0) == 2);
362 
363  std::atomic<bool> stop = false;
364  std::atomic<uint> counter = 2;
365 
366  {
367    // Arrange for two threads to increment in a loop simultaneously. Eventually one will fail with
368    // a conflict.
369    kj::Thread thread([&vfs = vfs, &stop, &counter]() noexcept {
370      KJ_DEFER(stop.store(true, std::memory_order_relaxed););
371      SqliteDatabase db2(vfs, kj::Path({"foo"}), kj::WriteMode::MODIFY);
372      while (!stop.load(std::memory_order_relaxed)) {
373        KJ_IF_SOME(e, kj::runCatchingExceptions([&]() {
374          db2.run(INCREMENT);
375          counter.fetch_add(1, std::memory_order_relaxed);
376        })) {
377          KJ_EXPECT(e.getDescription().contains("database is locked"), e);
378          break;
379        }
380      }
381    });
382 
383    {
384      KJ_DEFER(stop.store(true, std::memory_order_relaxed););
385 
386      while (!stop.load(std::memory_order_relaxed)) {
387        KJ_IF_SOME(e, kj::runCatchingExceptions([&]() {
388          db.run(INCREMENT);
389          counter.fetch_add(1, std::memory_order_relaxed);
390        })) {
391          KJ_EXPECT(e.getDescription().contains("database is locked"), e);
392          break;
393        }
394      }
395    }
396  }
397 
398  // The final value should be consistent with the number of increments that succeeded.
399  KJ_EXPECT(db.run(GET_COUNT).getInt(0) == counter.load(std::memory_order_relaxed));
400}
401 
402KJ_TEST("SQLite locks: rollback journal mode") {
403  doLockTest(false);
404}
405 
406KJ_TEST("SQLite locks: WAL mode") {
407  doLockTest(true);
408}
409 
410KJ_TEST("SQLite Regulator") {
411  TempDirOnDisk dir;
412  SqliteDatabase::Vfs vfs(*dir);
413  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
414 
415  class RegulatorImpl: public SqliteDatabase::Regulator {
416   public:
417    RegulatorImpl(kj::StringPtr blocked): blocked(blocked) {}
418 
419    bool isAllowedName(kj::StringPtr name) const override {
420      if (alwaysFail) return false;
421      return name != blocked;
422    }
423 
424    bool alwaysFail = false;
425 
426   private:
427    kj::StringPtr blocked;
428  };
429 
430  db.run(R"(
431    CREATE TABLE foo(value INTEGER);
432    CREATE TABLE bar(value INTEGER);
433    INSERT INTO foo VALUES (123);
434    INSERT INTO bar VALUES (456);
435  )");
436 
437  RegulatorImpl noFoo("foo");
438  RegulatorImpl noBar("bar");
439 
440  // We can prepare and run statements that comply with the regulator.
441  auto getFoo = db.prepare(noBar, "SELECT value FROM foo");
442  auto getBar = db.prepare(noFoo, "SELECT value FROM bar");
443 
444  KJ_EXPECT(getFoo.run().getInt(0) == 123);
445  KJ_EXPECT(getBar.run().getInt(0) == 456);
446 
447  // Trying to prepare a statement that violates the regulator fails.
448  KJ_EXPECT_THROW_MESSAGE(
449      "access to foo.value is prohibited", db.prepare(noFoo, "SELECT value FROM foo"));
450 
451  // If we create a new table, all statements must be re-prepared, which re-runs the regulator.
452  // Make sure that works.
453  db.run("CREATE TABLE baz(value INTEGER)");
454 
455  KJ_EXPECT(getFoo.run().getInt(0) == 123);
456 
457  // Let's screw with SQLite and make the regulator fail on re-run to see what happens.
458  noFoo.alwaysFail = true;
459  KJ_EXPECT_THROW_MESSAGE(
460      "access to bar.value is prohibited", KJ_EXPECT(getBar.run().getInt(0) == 456));
461}
462 
463KJ_TEST("SQLite onWrite callback") {
464  auto dir = kj::newInMemoryDirectory(kj::nullClock());
465  SqliteDatabase::Vfs vfs(*dir);
466  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
467 
468  bool sawWrite = false;
469  db.onWrite([&](bool allowUnconfirmed) { sawWrite = true; });
470 
471  setupSql(db);
472  KJ_EXPECT(sawWrite);
473  sawWrite = false;
474 
475  checkSql(db);
476  KJ_EXPECT(!sawWrite);  // checkSql() only does reads
477 
478  // Test for bug where the write callback would only be called for the last statement in a
479  // multi-statement execution.
480  auto q = db.run(R"(
481    INSERT INTO people (id, name, email) VALUES (12321, "Eve", "eve@example.com");
482    SELECT COUNT(*) FROM people;
483  )");
484  KJ_EXPECT(q.getInt(0) == 3);
485  KJ_EXPECT(sawWrite);
486}
487 
488struct RowCounts {
489  uint64_t found;
490  uint64_t read;
491  uint64_t written;
492};
493 
494template <typename... Params>
495RowCounts countRowsTouched(SqliteDatabase& db,
496    const SqliteDatabase::Regulator& regulator,
497    kj::StringPtr sqlCode,
498    Params... bindParams) {
499  uint64_t rowsFound = 0;
500 
501  // Runs a query; retrieves and discards all the data.
502  auto query = db.run({.regulator = regulator}, sqlCode, bindParams...);
503  while (!query.isDone()) {
504    rowsFound++;
505    query.nextRow();
506  }
507 
508  return {.found = rowsFound, .read = query.getRowsRead(), .written = query.getRowsWritten()};
509}
510 
511template <typename... Params>
512RowCounts countRowsTouched(SqliteDatabase& db, kj::StringPtr sqlCode, Params... bindParams) {
513  return countRowsTouched(db, SqliteDatabase::TRUSTED, sqlCode, kj::fwd<Params>(bindParams)...);
514}
515 
516KJ_TEST("SQLite read row counters (basic)") {
517  auto dir = kj::newInMemoryDirectory(kj::nullClock());
518  SqliteDatabase::Vfs vfs(*dir);
519  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
520 
521  db.run(R"(
522    CREATE TABLE things (
523      id INTEGER PRIMARY KEY,
524      unindexed_int INTEGER,
525      value TEXT
526    );
527  )");
528 
529  constexpr int dbRowCount = 1000;
530  auto insertStmt = db.prepare("INSERT INTO things (id, unindexed_int, value) VALUES (?, ?, ?)");
531  for (int i = 0; i < dbRowCount; i++) {
532    auto query = insertStmt.run(i, i * 1000, kj::str("value", i));
533    KJ_EXPECT(query.getRowsRead() == 1);
534    KJ_EXPECT(query.getRowsWritten() == 1);
535  }
536 
537  // Sanity check that the inserts worked.
538  {
539    auto getCount = db.prepare("SELECT COUNT(*) FROM things");
540    KJ_EXPECT(getCount.run().getInt(0) == dbRowCount);
541  }
542 
543  // Selecting all the rows reads all the rows.
544  {
545    RowCounts stats = countRowsTouched(db, "SELECT * FROM things");
546    KJ_EXPECT(stats.found == dbRowCount);
547    KJ_EXPECT(stats.read == dbRowCount);
548    KJ_EXPECT(stats.written == 0);
549  }
550 
551  // Selecting one row using an index reads one row.
552  {
553    RowCounts stats = countRowsTouched(db, "SELECT * FROM things WHERE id=?", 5);
554    KJ_EXPECT(stats.found == 1);
555    KJ_EXPECT(stats.read == 1);
556    KJ_EXPECT(stats.written == 0);
557  }
558 
559  // Selecting one row using an index reads one row, even if that row is in the middle of the table.
560  {
561    RowCounts stats = countRowsTouched(db, "SELECT * FROM things WHERE id=?", dbRowCount / 2);
562    KJ_EXPECT(stats.found == 1);
563    KJ_EXPECT(stats.read == 1);
564    KJ_EXPECT(stats.written == 0);
565  }
566 
567  // Selecting a row by an unindexed value reads the whole table.
568  {
569    RowCounts stats = countRowsTouched(db, "SELECT * FROM things WHERE unindexed_int = ?", 5000);
570    KJ_EXPECT(stats.found == 1);
571    KJ_EXPECT(stats.read == dbRowCount);
572    KJ_EXPECT(stats.written == 0);
573  }
574 
575  // Selecting an unindexed aggregate scans all the rows, which counts as reading them.
576  {
577    RowCounts stats = countRowsTouched(db, "SELECT MAX(unindexed_int) FROM things");
578    KJ_EXPECT(stats.found == 1);
579    KJ_EXPECT(stats.read == dbRowCount);
580    KJ_EXPECT(stats.written == 0);
581  }
582 
583  // Selecting an indexed aggregate can use the index, so it only reads the row it found.
584  {
585    RowCounts stats = countRowsTouched(db, "SELECT MIN(id) FROM things");
586    KJ_EXPECT(stats.found == 1);
587    KJ_EXPECT(stats.read == 1);
588    KJ_EXPECT(stats.written == 0);
589  }
590 
591  // Selecting with a limit only reads the returned rows.
592  {
593    RowCounts stats = countRowsTouched(db, "SELECT * FROM things LIMIT 5");
594    KJ_EXPECT(stats.found == 5);
595    KJ_EXPECT(stats.read == 5);
596    KJ_EXPECT(stats.written == 0);
597  }
598}
599 
600KJ_TEST("SQLite write row counters (basic)") {
601  auto dir = kj::newInMemoryDirectory(kj::nullClock());
602  SqliteDatabase::Vfs vfs(*dir);
603  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
604 
605  db.run(R"(
606    CREATE TABLE things (
607      id INTEGER PRIMARY KEY
608    );
609  )");
610 
611  db.run(R"(
612    CREATE TABLE unindexed_things (
613      id INTEGER
614    );
615  )");
616 
617  // Inserting a row counts as one row written.
618  {
619    RowCounts stats = countRowsTouched(db, "INSERT INTO unindexed_things (id) VALUES (?)", 1);
620    KJ_EXPECT(stats.read == 0);
621    KJ_EXPECT(stats.written == 1);
622  }
623 
624  // Inserting a row into a table with a primary key will also do a read (to ensure there's no
625  // duplicate PK).
626  {
627    RowCounts stats = countRowsTouched(db, "INSERT INTO things (id) VALUES (?)", 1);
628    KJ_EXPECT(stats.read == 1);
629    KJ_EXPECT(stats.written == 1);
630  }
631 
632  // Deleting a row counts as a write.
633  {
634    RowCounts stats = countRowsTouched(db, "INSERT INTO things (id) VALUES (?)", 123);
635    KJ_EXPECT(stats.written == 1);
636 
637    stats = countRowsTouched(db, "DELETE FROM things WHERE id=?", 123);
638    KJ_EXPECT(stats.read == 1);
639    KJ_EXPECT(stats.written == 1);
640  }
641 
642  // Deleting nothing is not a write.
643  {
644    RowCounts stats = countRowsTouched(db, "DELETE FROM things WHERE id=?", 998877112233);
645    KJ_EXPECT(stats.written == 0);
646  }
647 
648  // Inserting many things is many writes.
649  {
650    db.run("DELETE FROM things");
651    db.run("INSERT INTO things (id) VALUES (1)");
652    db.run("INSERT INTO things (id) VALUES (3)");
653    db.run("INSERT INTO things (id) VALUES (5)");
654 
655    RowCounts stats =
656        countRowsTouched(db, "INSERT INTO unindexed_things (id) SELECT id FROM things");
657    KJ_EXPECT(stats.read == 3);
658    KJ_EXPECT(stats.written == 3);
659  }
660 
661  // Each updated row is a write.
662  {
663    db.run("DELETE FROM unindexed_things");
664    db.run("INSERT INTO unindexed_things (id) VALUES (1)");
665    db.run("INSERT INTO unindexed_things (id) VALUES (2)");
666    db.run("INSERT INTO unindexed_things (id) VALUES (3)");
667    db.run("INSERT INTO unindexed_things (id) VALUES (4)");
668 
669    RowCounts stats =
670        countRowsTouched(db, "UPDATE unindexed_things SET id = id * 10 WHERE id >= 3");
671    KJ_EXPECT(stats.written == 2);
672  }
673 
674  // Same as above, but with an index.
675  {
676    db.run("DELETE FROM things");
677    db.run("INSERT INTO things (id) VALUES (1)");
678    db.run("INSERT INTO things (id) VALUES (2)");
679    db.run("INSERT INTO things (id) VALUES (3)");
680    db.run("INSERT INTO things (id) VALUES (4)");
681 
682    RowCounts stats = countRowsTouched(db, "UPDATE things SET id = id * 10 WHERE id >= 3");
683    KJ_EXPECT(stats.read >= 4);  // At least one read per updated row
684    KJ_EXPECT(stats.written == 2);
685  }
686}
687 
688KJ_TEST("SQLite read/write row counters (large row insert)") {
689  // This is used to verify reading/writing a large row (bigger than the size of one page in sqlite)
690  // results only in 1 read/row count as returned by the DB
691 
692  auto dir = kj::newInMemoryDirectory(kj::nullClock());
693  SqliteDatabase::Vfs vfs(*dir);
694  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
695 
696  db.run("CREATE TABLE large_things (id INTEGER PRIMARY KEY, large_value TEXT)");
697 
698  // SQLite's default page size is 4096 bytes
699  // So create a string significantly larger than that
700  KJ_EXPECT(db.run("PRAGMA page_size").getInt(0) == 4096);
701  kj::String largeValue = kj::str(kj::repeat('A', 100000));
702 
703  // Insert the large row
704  RowCounts insertStats = countRowsTouched(
705      db, "INSERT INTO large_things (id, large_value) VALUES (?, ?)", 1, kj::mv(largeValue));
706 
707  KJ_EXPECT(insertStats.found == 0);
708  KJ_EXPECT(insertStats.read == 1);
709  KJ_EXPECT(insertStats.written == 1);
710 
711  // Verify the insert
712  auto verifyStmt = db.prepare("SELECT COUNT(*) FROM large_things");
713  KJ_EXPECT(verifyStmt.run().getInt(0) == 1);
714 
715  // Read the large row
716  RowCounts readStats = countRowsTouched(db, "SELECT * FROM large_things WHERE id = ?", 1);
717  KJ_EXPECT(readStats.found == 1);
718  KJ_EXPECT(readStats.read == 1);
719  KJ_EXPECT(readStats.written == 0);
720}
721 
722KJ_TEST("SQLite row counters with triggers") {
723  auto dir = kj::newInMemoryDirectory(kj::nullClock());
724  SqliteDatabase::Vfs vfs(*dir);
725  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
726 
727  class RegulatorImpl: public SqliteDatabase::Regulator {
728   public:
729    RegulatorImpl() = default;
730 
731    bool isAllowedTrigger(kj::StringPtr name) const override {
732      // SqliteDatabase::TRUSTED doesn't let us use triggers at all.
733      return true;
734    }
735  };
736 
737  RegulatorImpl regulator;
738 
739  db.run(R"(
740    CREATE TABLE things (
741      id INTEGER PRIMARY KEY
742    );
743 
744    CREATE TABLE log (
745      id INTEGER,
746      verb TEXT
747    );
748 
749    CREATE TRIGGER log_inserts AFTER INSERT ON things
750    BEGIN
751      insert into log (id, verb) VALUES (NEW.id, "INSERT");
752    END;
753 
754    CREATE TRIGGER log_deletes AFTER DELETE ON things
755    BEGIN
756      insert into log (id, verb) VALUES (OLD.id, "DELETE");
757    END;
758  )");
759 
760  // Each insert incurs two writes: one for the row in `things` and one for the row in `log`.
761  {
762    RowCounts stats = countRowsTouched(db, regulator, "INSERT INTO things (id) VALUES (1)");
763    KJ_EXPECT(stats.written == 2);
764  }
765 
766  // A deletion incurs two writes: one for the row and one for the log.
767  {
768    db.run({.regulator = regulator}, "DELETE FROM things");
769    db.run({.regulator = regulator}, "INSERT INTO things (id) VALUES (1)");
770    db.run({.regulator = regulator}, "INSERT INTO things (id) VALUES (2)");
771    db.run({.regulator = regulator}, "INSERT INTO things (id) VALUES (3)");
772 
773    RowCounts stats = countRowsTouched(db, regulator, "DELETE FROM things");
774    KJ_EXPECT(stats.written == 6);
775  }
776}
777 
778KJ_TEST("DELETE with LIMIT") {
779  auto dir = kj::newInMemoryDirectory(kj::nullClock());
780  SqliteDatabase::Vfs vfs(*dir);
781  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
782 
783  db.run(R"(
784    CREATE TABLE things (
785      id INTEGER PRIMARY KEY
786    );
787  )");
788 
789  db.run(R"(INSERT INTO things (id) VALUES (1))");
790  db.run(R"(INSERT INTO things (id) VALUES (2))");
791  db.run(R"(INSERT INTO things (id) VALUES (3))");
792  db.run(R"(INSERT INTO things (id) VALUES (4))");
793  db.run(R"(INSERT INTO things (id) VALUES (5))");
794  db.run(R"(DELETE FROM things LIMIT 2)");
795  auto q = db.run(R"(SELECT COUNT(*) FROM things;)");
796  KJ_EXPECT(q.getInt(0) == 3);
797}
798 
799KJ_TEST("reset database") {
800  auto dir = kj::newInMemoryDirectory(kj::nullClock());
801  SqliteDatabase::Vfs vfs(*dir);
802  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
803 
804  db.run("PRAGMA journal_mode=WAL;");
805 
806  db.run("CREATE TABLE things (id INTEGER PRIMARY KEY)");
807 
808  db.run("INSERT INTO things VALUES (123)");
809  db.run("INSERT INTO things VALUES (321)");
810 
811  auto stmt = db.prepare("SELECT * FROM things");
812 
813  auto query = stmt.run();
814  KJ_ASSERT(!query.isDone());
815  KJ_EXPECT(query.getInt(0) == 123);
816 
817  db.reset();
818  db.run("PRAGMA journal_mode=WAL;");
819 
820  // The query was canceled.
821  KJ_EXPECT_THROW_MESSAGE("query canceled because reset()", query.nextRow());
822  KJ_EXPECT_THROW_MESSAGE("query canceled because reset()", query.getInt(0));
823 
824  // The statement doesn't work because the table is gone.
825  KJ_EXPECT_THROW_MESSAGE("no such table: things: SQLITE_ERROR", stmt.run());
826 
827  // But we can recreate it.
828  db.run("CREATE TABLE things (id INTEGER PRIMARY KEY)");
829  db.run("INSERT INTO things VALUES (456)");
830 
831  // Now the statement works.
832  {
833    auto q2 = stmt.run();
834    KJ_ASSERT(!q2.isDone());
835    KJ_EXPECT(q2.getInt(0) == 456);
836    q2.nextRow();
837    KJ_EXPECT(q2.isDone());
838  }
839}
840 
841KJ_TEST("SQLite observer addQueryStats") {
842  class TestSqliteObserver: public SqliteObserver {
843   public:
844    void addQueryStats(uint64_t read, uint64_t written) override {
845      rowsRead += read;
846      rowsWritten += written;
847    }
848 
849    uint64_t rowsRead = 0;
850    uint64_t rowsWritten = 0;
851  };
852 
853  class TestQueryStatsRegulator: public SqliteDatabase::Regulator {
854   public:
855    bool shouldAddQueryStats() const override {
856      return true;
857    }
858  };
859 
860  TempDirOnDisk dir;
861  SqliteDatabase::Vfs vfs(*dir);
862  TestSqliteObserver sqliteObserver = TestSqliteObserver();
863  TestQueryStatsRegulator regulator;
864  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY,
865      /*sqliteMaxMemoryBytes=*/kj::maxValue, sqliteObserver);
866 
867  db.run(R"(
868    CREATE TABLE things (
869      id INTEGER PRIMARY KEY
870    );
871  )");
872 
873  // There are some rows read and written when we create the db, we offset this in the test
874  int rowsReadBefore = sqliteObserver.rowsRead;
875  int rowsWrittenBefore = sqliteObserver.rowsWritten;
876  constexpr int dbRowCount = 3;
877  {
878    db.run({.regulator = regulator}, "INSERT INTO things (id) VALUES (10)");
879    db.run({.regulator = regulator}, "INSERT INTO things (id) VALUES (11)");
880    db.run({.regulator = regulator}, "INSERT INTO things (id) VALUES (12)");
881  }
882  KJ_EXPECT(sqliteObserver.rowsRead - rowsReadBefore == dbRowCount);
883  KJ_EXPECT(sqliteObserver.rowsWritten - rowsWrittenBefore == dbRowCount);
884 
885  rowsReadBefore = sqliteObserver.rowsRead;
886  rowsWrittenBefore = sqliteObserver.rowsWritten;
887  {
888    auto getCount = db.prepare(regulator, "SELECT COUNT(*) FROM things");
889    KJ_EXPECT(getCount.run().getInt(0) == dbRowCount);
890  }
891  KJ_EXPECT(sqliteObserver.rowsRead - rowsReadBefore == dbRowCount);
892  KJ_EXPECT(sqliteObserver.rowsWritten - rowsWrittenBefore == 0);
893 
894  // Verify if addQueryStats works correctly when we call query.nextRow()
895  rowsReadBefore = sqliteObserver.rowsRead;
896  rowsWrittenBefore = sqliteObserver.rowsWritten;
897  {
898    auto stmt = db.prepare(regulator, "SELECT * FROM things");
899    auto query = stmt.run();
900    KJ_ASSERT(!query.isDone());
901    while (!query.isDone()) {
902      query.nextRow();
903    }
904  }
905  KJ_EXPECT(sqliteObserver.rowsRead - rowsReadBefore == dbRowCount);
906  KJ_EXPECT(sqliteObserver.rowsWritten - rowsWrittenBefore == 0);
907 
908  // Verify system queries don't affect stats.
909  rowsReadBefore = sqliteObserver.rowsRead;
910  rowsWrittenBefore = sqliteObserver.rowsWritten;
911  db.run("INSERT INTO things (id) VALUES (13)");
912  {
913    auto query = db.run("SELECT * FROM things");
914    while (!query.isDone()) {
915      query.nextRow();
916    }
917  }
918  KJ_EXPECT(sqliteObserver.rowsRead == rowsReadBefore);
919  KJ_EXPECT(sqliteObserver.rowsWritten == rowsWrittenBefore);
920 
921  // Verify addQueryStats works correctly when db is reset
922  rowsReadBefore = sqliteObserver.rowsRead;
923  rowsWrittenBefore = sqliteObserver.rowsWritten;
924  {
925    auto query = db.run({.regulator = regulator}, "INSERT INTO things (id) VALUES (100)");
926    db.reset();
927  }
928  KJ_EXPECT(sqliteObserver.rowsRead - rowsReadBefore == 1);
929  KJ_EXPECT(sqliteObserver.rowsWritten - rowsWrittenBefore == 1);
930}
931 
932KJ_TEST("SQLite observer reportQueryEvent") {
933  class TestSqliteObserver: public SqliteObserver {
934   public:
935    int capturedEvents = 0;
936 
937    void reportQueryEvent(kj::Maybe<kj::String> queryStatement,
938        uint64_t queryRowsRead,
939        uint64_t queryRowsWritten,
940        kj::Duration,
941        uint64_t dbWalBytesWritten,
942        int queryError,
943        int extendedErrorCode,
944        bool isInternalQuery,
945        kj::Maybe<kj::String> queryErrorDescription) override {
946      KJ_IF_SOME(err, queryErrorDescription) {
947        KJ_ASSERT(err.contains("query canceled because reset()"));
948      }
949      capturedEvents++;
950    }
951  };
952 
953  auto dir = kj::newInMemoryDirectory(kj::nullClock());
954  SqliteDatabase::Vfs vfs(*dir);
955  TestSqliteObserver sqliteObserver;
956  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY,
957      /*sqliteMaxMemoryBytes=*/kj::maxValue, sqliteObserver);
958 
959  db.run("PRAGMA journal_mode=WAL;");
960 
961  db.run(R"(
962      CREATE TABLE people (
963        id INTEGER PRIMARY KEY,
964        name TEXT NOT NULL,
965        email TEXT NOT NULL UNIQUE
966      );
967 
968      INSERT INTO people (id, name, email)
969      VALUES (?, ?, ?),
970             (?, ?, ?);
971    )",
972      123, "Bob"_kj, "bob@example.com"_kj, 321, "Alice"_kj, "alice@example.com"_kj);
973 
974  {
975    auto stmt = db.prepare("SELECT * FROM people");
976    auto query = stmt.run();
977  }
978  {
979    // Expect 4 events so far: PRAGMA, CREATE, INSERT, SELECT.
980    KJ_ASSERT(sqliteObserver.capturedEvents == 4);
981 
982    // SELECT #2 (canceled due to reset later)
983    auto stmt = db.prepare("SELECT * FROM people");
984    auto query = stmt.run();
985 
986    KJ_ASSERT(!query.isDone());
987    KJ_EXPECT(query.getInt(0) == 123);
988 
989    db.reset();
990 
991    db.run("PRAGMA journal_mode=WAL;");
992 
993    KJ_EXPECT_THROW_MESSAGE("query canceled because reset()", query.nextRow());
994    KJ_EXPECT_THROW_MESSAGE("query canceled because reset()", query.getInt(0));
995 
996    // 1 more event: PRAGMA
997    KJ_ASSERT(sqliteObserver.capturedEvents == 5);
998  }
999 
1000  // Cancelled SELECT #2 emiited with an errorDesc after query goes out of scope
1001  KJ_ASSERT(sqliteObserver.capturedEvents == 6);
1002}
1003 
1004KJ_TEST("SQLite failed statement reset") {
1005  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1006  SqliteDatabase::Vfs vfs(*dir);
1007  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1008 
1009  db.run(R"(
1010    CREATE TABLE things (
1011      id INTEGER PRIMARY KEY
1012    );
1013  )");
1014 
1015  auto stmt = db.prepare("INSERT INTO things VALUES (?)");
1016 
1017  // Run the statement a couple times.
1018  stmt.run(1);
1019  stmt.run(2);
1020 
1021  // Now run it with a duplicate value, should fail.
1022  KJ_EXPECT_THROW_MESSAGE("UNIQUE constraint failed: things.id", stmt.run(1));
1023 
1024  // The statement shouldn't be left broken. Run it again with a non-duplicate.
1025  stmt.run(3);
1026 
1027  // Same as above but with ValuePtrs, since these use a different path.
1028  using ValuePtr = SqliteDatabase::Query::ValuePtr;
1029  ValuePtr value = static_cast<int64_t>(1);
1030  KJ_EXPECT_THROW_MESSAGE(
1031      "UNIQUE constraint failed: things.id", stmt.run(kj::arrayPtr<const ValuePtr>(value)));
1032  value = static_cast<int64_t>(4);
1033  stmt.run(kj::arrayPtr<const ValuePtr>(value));
1034 
1035  // Sanity check that those queries were doing something.
1036  KJ_EXPECT(db.run("SELECT COUNT(*) FROM things").getInt(0) == 4);
1037}
1038 
1039KJ_TEST("SQLite extended error codes in messages") {
1040  // Verify that error messages include named extended error codes when they differ from the
1041  // primary error code.
1042  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1043  SqliteDatabase::Vfs vfs(*dir);
1044  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1045 
1046  db.run(R"(
1047    CREATE TABLE things (
1048      id INTEGER PRIMARY KEY,
1049      name TEXT NOT NULL
1050    );
1051  )");
1052 
1053  db.run("INSERT INTO things VALUES (1, 'alice')");
1054 
1055  // UNIQUE/PRIMARY KEY constraint: extended code should be SQLITE_CONSTRAINT_PRIMARYKEY.
1056  KJ_EXPECT_THROW_MESSAGE(
1057      "(extended: SQLITE_CONSTRAINT_PRIMARYKEY)", db.run("INSERT INTO things VALUES (1, 'bob')"));
1058 
1059  // NOT NULL constraint: extended code should be SQLITE_CONSTRAINT_NOTNULL.
1060  KJ_EXPECT_THROW_MESSAGE(
1061      "(extended: SQLITE_CONSTRAINT_NOTNULL)", db.run("INSERT INTO things VALUES (2, NULL)"));
1062 
1063  // Errors where extended == primary should NOT have a parenthesized suffix.
1064  // SQLITE_ERROR for "no such table" has no extended variant.
1065  try {
1066    db.run("SELECT * FROM nonexistent");
1067    KJ_FAIL_ASSERT("expected exception");
1068  } catch (kj::Exception& e) {
1069    auto desc = e.getDescription();
1070 
1071    KJ_EXPECT(desc.contains("SQLITE_ERROR"), desc);
1072    // The message should NOT have a parenthesized extended code like "(SQLITE_ERROR_...)".
1073    KJ_EXPECT(!desc.contains("(SQLITE_ERROR_"), desc);
1074  }
1075}
1076 
1077class MockRollbackCallback {
1078 public:
1079  kj::Function<void()> create() {
1080    KJ_ASSERT(!created);
1081    created = true;
1082    return [this, destructor = kj::defer([this]() { destroyed = true; })]() {
1083      KJ_ASSERT(!called, "callback called multiple times?");
1084      called = true;
1085    };
1086  }
1087 
1088  bool isStillLive() {
1089    return !destroyed && !called;
1090  }
1091  bool wasRolledBack() {
1092    return called && destroyed;
1093  }
1094  bool wasCommitted() {
1095    return !called && destroyed;
1096  }
1097 
1098 private:
1099  bool created = false;
1100  bool called = false;
1101  bool destroyed = false;
1102};
1103 
1104KJ_TEST("SQLite onRollback") {
1105  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1106  SqliteDatabase::Vfs vfs(*dir);
1107  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1108 
1109  // With no transactions open, the callback is dropped immediately.
1110  {
1111    MockRollbackCallback cb;
1112    db.onRollback(cb.create());
1113    KJ_EXPECT(cb.wasCommitted());
1114  }
1115 
1116  // Committed transactions drop the callback without invoking it.
1117  {
1118    db.run("BEGIN TRANSACTION");
1119 
1120    MockRollbackCallback cb;
1121    db.onRollback(cb.create());
1122    KJ_EXPECT(cb.isStillLive());
1123 
1124    db.run("COMMIT TRANSACTION");
1125 
1126    KJ_EXPECT(cb.wasCommitted());
1127  }
1128 
1129  {
1130    db.run("SAVEPOINT foo");
1131 
1132    MockRollbackCallback cb;
1133    db.onRollback(cb.create());
1134    KJ_EXPECT(cb.isStillLive());
1135 
1136    db.run("RELEASE SAVEPOINT foo");
1137 
1138    KJ_EXPECT(cb.wasCommitted());
1139  }
1140 
1141  // Rollbacks invoke the callback.
1142  {
1143    db.run("BEGIN TRANSACTION");
1144 
1145    MockRollbackCallback cb;
1146    db.onRollback(cb.create());
1147    KJ_EXPECT(cb.isStillLive());
1148 
1149    db.run("ROLLBACK TRANSACTION");
1150 
1151    KJ_EXPECT(cb.wasRolledBack());
1152  }
1153 
1154  {
1155    db.run("SAVEPOINT foo");
1156 
1157    MockRollbackCallback cb;
1158    db.onRollback(cb.create());
1159    KJ_EXPECT(cb.isStillLive());
1160 
1161    db.run("ROLLBACK TO SAVEPOINT foo");
1162    KJ_EXPECT(cb.wasRolledBack());
1163 
1164    // The savepoint still exists until we release it...
1165    db.run("RELEASE SAVEPOINT foo");
1166  }
1167 
1168  // Prepared statements work.
1169  {
1170    auto begin = db.prepare("BEGIN TRANSACTION");
1171    auto commit = db.prepare("COMMIT TRANSACTION");
1172 
1173    // No transactions are open yet (we only prepared some statements, we didn't execute them), so
1174    // the callback is dropped immediately.
1175    MockRollbackCallback cb1;
1176    db.onRollback(cb1.create());
1177    KJ_EXPECT(cb1.wasCommitted());
1178 
1179    begin.run();
1180 
1181    // Now a transaction is actually open.
1182    MockRollbackCallback cb2;
1183    db.onRollback(cb2.create());
1184    KJ_EXPECT(cb2.isStillLive());
1185 
1186    commit.run();
1187 
1188    KJ_EXPECT(cb2.wasCommitted());
1189  }
1190 
1191  // Make a whole stack, do partial rollbacks...
1192  {
1193    db.run("BEGIN TRANSACTION");
1194 
1195    MockRollbackCallback cb1;
1196    db.onRollback(cb1.create());
1197 
1198    db.run("SAVEPOINT foo");
1199    db.run("SAVEPOINT bar");
1200 
1201    MockRollbackCallback cb2;
1202    db.onRollback(cb2.create());
1203 
1204    db.run("RELEASE bar");
1205 
1206    KJ_EXPECT(cb1.isStillLive());
1207    KJ_EXPECT(cb2.isStillLive());
1208 
1209    db.run("SAVEPOINT baz");
1210    db.run("ROLLBACK TO baz");
1211 
1212    KJ_EXPECT(cb1.isStillLive());
1213    KJ_EXPECT(cb2.isStillLive());
1214 
1215    db.run("SAVEPOINT qux");
1216    db.run("ROLLBACK TO foo");
1217 
1218    KJ_EXPECT(cb1.isStillLive());
1219    KJ_EXPECT(cb2.wasRolledBack());
1220 
1221    db.run("COMMIT TRANSACTION");
1222 
1223    KJ_EXPECT(cb1.wasCommitted());
1224  }
1225}
1226 
1227KJ_TEST("SQLite prepareMulti") {
1228  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1229  SqliteDatabase::Vfs vfs(*dir);
1230  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1231 
1232  auto stmt = db.prepareMulti(SqliteDatabase::TRUSTED, kj::str(R"(
1233    CREATE TABLE IF NOT EXISTS things (
1234      id INTEGER PRIMARY KEY AUTOINCREMENT,
1235      value INTEGER
1236    );
1237    INSERT INTO things(value) VALUES (123);
1238    INSERT INTO things(value) VALUES (456);
1239    INSERT INTO things(value) VALUES (789);
1240    SELECT id, value FROM things;
1241  )"));
1242 
1243  {
1244    auto query = stmt.run();
1245 
1246    KJ_ASSERT(!query.isDone());
1247    KJ_ASSERT(query.getInt(0) == 1);
1248    KJ_ASSERT(query.getInt(1) == 123);
1249    query.nextRow();
1250    KJ_ASSERT(!query.isDone());
1251    KJ_ASSERT(query.getInt(0) == 2);
1252    KJ_ASSERT(query.getInt(1) == 456);
1253    query.nextRow();
1254    KJ_ASSERT(!query.isDone());
1255    KJ_ASSERT(query.getInt(0) == 3);
1256    KJ_ASSERT(query.getInt(1) == 789);
1257    query.nextRow();
1258 
1259    KJ_ASSERT(query.isDone());
1260  }
1261 
1262  {
1263    auto query = stmt.run();
1264 
1265    KJ_ASSERT(!query.isDone());
1266    KJ_ASSERT(query.getInt(0) == 1);
1267    KJ_ASSERT(query.getInt(1) == 123);
1268    query.nextRow();
1269    KJ_ASSERT(!query.isDone());
1270    KJ_ASSERT(query.getInt(0) == 2);
1271    KJ_ASSERT(query.getInt(1) == 456);
1272    query.nextRow();
1273    KJ_ASSERT(!query.isDone());
1274    KJ_ASSERT(query.getInt(0) == 3);
1275    KJ_ASSERT(query.getInt(1) == 789);
1276    query.nextRow();
1277 
1278    // Re-running the statement inserted duplicates, so we'll see those in the results.
1279    KJ_ASSERT(!query.isDone());
1280    KJ_ASSERT(query.getInt(0) == 4);
1281    KJ_ASSERT(query.getInt(1) == 123);
1282    query.nextRow();
1283    KJ_ASSERT(!query.isDone());
1284    KJ_ASSERT(query.getInt(0) == 5);
1285    KJ_ASSERT(query.getInt(1) == 456);
1286    query.nextRow();
1287    KJ_ASSERT(!query.isDone());
1288    KJ_ASSERT(query.getInt(0) == 6);
1289    KJ_ASSERT(query.getInt(1) == 789);
1290    query.nextRow();
1291 
1292    KJ_ASSERT(query.isDone());
1293  }
1294 
1295  // Test resetting the database, which will force re-parsing each statement.
1296  db.reset();
1297 
1298  {
1299    auto query = stmt.run();
1300 
1301    KJ_ASSERT(!query.isDone());
1302    KJ_ASSERT(query.getInt(0) == 1);
1303    KJ_ASSERT(query.getInt(1) == 123);
1304    query.nextRow();
1305    KJ_ASSERT(!query.isDone());
1306    KJ_ASSERT(query.getInt(0) == 2);
1307    KJ_ASSERT(query.getInt(1) == 456);
1308    query.nextRow();
1309    KJ_ASSERT(!query.isDone());
1310    KJ_ASSERT(query.getInt(0) == 3);
1311    KJ_ASSERT(query.getInt(1) == 789);
1312    query.nextRow();
1313 
1314    KJ_ASSERT(query.isDone());
1315  }
1316}
1317 
1318KJ_TEST("SQLite prepareMulti with failure") {
1319  // Test running a multi-line prepared statement that fails in the middle.
1320 
1321  // TODO(soon): Currently the failure does not roll back previous lines, but we should probably
1322  //   change that so it does. If/when we do that, this test will have to get more complicated:
1323  //   we'll need a prepared statement that fails on one call and then succeeds on a later call,
1324  //   so that we can figure out whether duplicate statements were added to the prelude, which
1325  //   is the bug being checked for here.
1326 
1327  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1328  SqliteDatabase::Vfs vfs(*dir);
1329  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1330 
1331  auto stmt = db.prepareMulti(SqliteDatabase::TRUSTED, kj::str(R"(
1332    CREATE TABLE IF NOT EXISTS things (
1333      id INTEGER PRIMARY KEY AUTOINCREMENT,
1334      value INTEGER
1335    );
1336    INSERT INTO things(value) VALUES (123);
1337    INSERT INTO things(id, value) VALUES (1, 456);  -- fails, duplicate primary key
1338  )"));
1339 
1340  KJ_EXPECT_THROW_MESSAGE("SQLITE_CONSTRAINT", stmt.run());
1341  KJ_EXPECT_THROW_MESSAGE("SQLITE_CONSTRAINT", stmt.run());
1342  KJ_EXPECT_THROW_MESSAGE("SQLITE_CONSTRAINT", stmt.run());
1343 
1344  // We ran the statement three times. Each time it should have inserted a new row containing
1345  // `123`, before failing on the second insert. So there should be three rows. (At one point there
1346  // was a bug where the successful prefix of statements would get duplicated on each run leading
1347  // to there being 1 + 2 + 3 = 6 rows here.)
1348  auto query = db.run("SELECT COUNT(*) FROM things");
1349  KJ_ASSERT(!query.isDone());
1350  KJ_EXPECT(query.getInt(0) == 3);
1351}
1352 
1353KJ_TEST("SQLite prepareMulti w/BEGIN TRANSACTION") {
1354  // Test running a multi-line prepared statement where a transaction state change statement
1355  // appears in the middle. At one point, there was a bug causing the state not to be tracked
1356  // correctly on the second (and subsequent) execution of the statement.
1357 
1358  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1359  SqliteDatabase::Vfs vfs(*dir);
1360  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1361 
1362  auto stmt = db.prepareMulti(SqliteDatabase::TRUSTED, kj::str(R"(
1363    CREATE TABLE IF NOT EXISTS things (
1364      id INTEGER PRIMARY KEY AUTOINCREMENT,
1365      value INTEGER
1366    );
1367    INSERT INTO things(value) VALUES (123);
1368    BEGIN TRANSACTION;
1369    INSERT INTO things(value) VALUES (456);
1370    SELECT id, value FROM things;
1371  )"));
1372 
1373  {
1374    auto query = stmt.run();
1375 
1376    KJ_ASSERT(!query.isDone());
1377    KJ_ASSERT(query.getInt(0) == 1);
1378    KJ_ASSERT(query.getInt(1) == 123);
1379    query.nextRow();
1380    KJ_ASSERT(!query.isDone());
1381    KJ_ASSERT(query.getInt(0) == 2);
1382    KJ_ASSERT(query.getInt(1) == 456);
1383    query.nextRow();
1384 
1385    KJ_ASSERT(query.isDone());
1386  }
1387 
1388  db.run("ROLLBACK");
1389 
1390  {
1391    auto query = stmt.run();
1392 
1393    KJ_ASSERT(!query.isDone());
1394    KJ_ASSERT(query.getInt(0) == 1);
1395    KJ_ASSERT(query.getInt(1) == 123);
1396    query.nextRow();
1397    KJ_ASSERT(!query.isDone());
1398    KJ_ASSERT(query.getInt(0) == 2);
1399    KJ_ASSERT(query.getInt(1) == 123);
1400    query.nextRow();
1401    KJ_ASSERT(!query.isDone());
1402    KJ_ASSERT(query.getInt(0) == 3);
1403    KJ_ASSERT(query.getInt(1) == 456);
1404    query.nextRow();
1405 
1406    KJ_ASSERT(query.isDone());
1407  }
1408 
1409  db.run("ROLLBACK");
1410 
1411  {
1412    auto query = db.run("SELECT id, value FROM things;");
1413 
1414    KJ_ASSERT(!query.isDone());
1415    KJ_ASSERT(query.getInt(0) == 1);
1416    KJ_ASSERT(query.getInt(1) == 123);
1417    query.nextRow();
1418    KJ_ASSERT(!query.isDone());
1419    KJ_ASSERT(query.getInt(0) == 2);
1420    KJ_ASSERT(query.getInt(1) == 123);
1421    query.nextRow();
1422 
1423    KJ_ASSERT(query.isDone());
1424  }
1425}
1426 
1427// =======================================================================================
1428// Error pass-through test
1429//
1430// TODO(cleanup): There is a LOT of boilerplate here to inject an exception into the VFS. Do we
1431//   need better test utils for KJ filesystem APIs?
1432 
1433// kj::File that throws errors when written.
1434class ErrorInjectableFile final: public kj::File, public kj::AtomicRefcounted {
1435 public:
1436  // Initialize `error` to cause writes to fail.
1437  kj::Maybe<kj::Exception> error;
1438 
1439  void write(uint64_t offset, kj::ArrayPtr<const byte> data) const override {
1440    KJ_IF_SOME(e, error) {
1441      kj::throwFatalException(e.clone());
1442    }
1443    inner->write(offset, data);
1444  }
1445 
1446  // All other operations just pass through.
1447  // TODO(cleanup): Do we need a FileWrapper base class in KJ?
1448  Metadata stat() const override {
1449    return inner->stat();
1450  }
1451  void sync() const override {
1452    inner->sync();
1453  }
1454  void datasync() const override {
1455    inner->datasync();
1456  }
1457  size_t read(uint64_t offset, kj::ArrayPtr<byte> buffer) const override {
1458    return inner->read(offset, buffer);
1459  }
1460  kj::Array<const byte> mmap(uint64_t offset, uint64_t size) const override {
1461    return inner->mmap(offset, size);
1462  }
1463  kj::Array<byte> mmapPrivate(uint64_t offset, uint64_t size) const override {
1464    return inner->mmapPrivate(offset, size);
1465  }
1466  void zero(uint64_t offset, uint64_t size) const override {
1467    inner->zero(offset, size);
1468  }
1469  void truncate(uint64_t size) const override {
1470    inner->truncate(size);
1471  }
1472  kj::Own<const kj::WritableFileMapping> mmapWritable(
1473      uint64_t offset, uint64_t size) const override {
1474    return inner->mmapWritable(offset, size);
1475  }
1476  size_t copy(uint64_t offset,
1477      const ReadableFile& from,
1478      uint64_t fromOffset,
1479      uint64_t size) const override {
1480    return inner->copy(offset, from, fromOffset, size);
1481  }
1482 
1483 private:
1484  kj::Own<const kj::File> inner = kj::newInMemoryFile(kj::nullClock());
1485 
1486  kj::Own<const FsNode> cloneFsNode() const override {
1487    return kj::atomicAddRef(*this);
1488  }
1489};
1490 
1491// kj::Directory that serves ErrorInjectableFiles to SQLite.
1492class ErrorInjectableDirectory final: public kj::Directory, public kj::AtomicRefcounted {
1493 public:
1494  kj::Maybe<kj::Own<ErrorInjectableFile>> dbFile;
1495  kj::Maybe<kj::Own<ErrorInjectableFile>> walFile;
1496  kj::Maybe<kj::Own<ErrorInjectableFile>> journalFile;
1497 
1498  // Map filenames to the three Maybe<File>s above.
1499  kj::Maybe<kj::Own<ErrorInjectableFile>>& getSlot(kj::PathPtr path) {
1500    if (path.size() == 1) {
1501      kj::StringPtr name = path[0];
1502      if (name == "db"_kj) {
1503        return dbFile;
1504      } else if (name == "db-wal"_kj) {
1505        return walFile;
1506      } else if (name == "db-journal"_kj) {
1507        return journalFile;
1508      }
1509    }
1510    KJ_FAIL_ASSERT("unexpected file opened", path);
1511  }
1512 
1513  kj::Maybe<kj::Own<ErrorInjectableFile>>& getSlot(kj::PathPtr path) const {
1514    // const_cast OK because it's test code
1515    return const_cast<ErrorInjectableDirectory*>(this)->getSlot(path);
1516  }
1517 
1518  // ---------------------------------------------------------------------------
1519  // implements kj::Directory
1520 
1521  kj::Maybe<kj::Own<const kj::ReadableFile>> tryOpenFile(kj::PathPtr path) const override {
1522    return getSlot(path).map([](kj::Own<ErrorInjectableFile>& file) { return file->clone(); });
1523  }
1524 
1525  kj::Maybe<kj::Own<const kj::File>> tryOpenFile(
1526      kj::PathPtr path, kj::WriteMode mode) const override {
1527    auto& slot = getSlot(path);
1528 
1529    KJ_IF_SOME(file, slot) {
1530      if (kj::has(mode, kj::WriteMode::MODIFY)) {
1531        return file->clone();
1532      } else {
1533        return kj::none;
1534      }
1535    } else {
1536      if (kj::has(mode, kj::WriteMode::CREATE)) {
1537        return slot.emplace(kj::atomicRefcounted<ErrorInjectableFile>())->clone();
1538      } else {
1539        return kj::none;
1540      }
1541    }
1542  }
1543 
1544  bool exists(kj::PathPtr path) const override {
1545    return getSlot(path) != kj::none;
1546  }
1547 
1548  bool tryRemove(kj::PathPtr path) const override {
1549    auto& slot = getSlot(path);
1550    bool result = slot != kj::none;
1551    slot = kj::none;
1552    return result;
1553  }
1554 
1555  kj::Own<const FsNode> cloneFsNode() const override {
1556    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1557  }
1558  Metadata stat() const override {
1559    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1560  }
1561  void sync() const override {
1562    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1563  }
1564  void datasync() const override {
1565    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1566  }
1567  kj::Array<kj::String> listNames() const override {
1568    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1569  }
1570  kj::Array<Entry> listEntries() const override {
1571    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1572  }
1573  kj::Maybe<FsNode::Metadata> tryLstat(kj::PathPtr path) const override {
1574    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1575  }
1576  kj::Maybe<kj::Own<const ReadableDirectory>> tryOpenSubdir(kj::PathPtr path) const override {
1577    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1578  }
1579  kj::Maybe<kj::String> tryReadlink(kj::PathPtr path) const override {
1580    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1581  }
1582  kj::Own<Replacer<kj::File>> replaceFile(kj::PathPtr path, kj::WriteMode mode) const override {
1583    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1584  }
1585  kj::Own<const kj::File> createTemporary() const override {
1586    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1587  }
1588  kj::Maybe<kj::Own<kj::AppendableFile>> tryAppendFile(
1589      kj::PathPtr path, kj::WriteMode mode) const override {
1590    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1591  }
1592  kj::Maybe<kj::Own<const kj::Directory>> tryOpenSubdir(
1593      kj::PathPtr path, kj::WriteMode mode) const override {
1594    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1595  }
1596  kj::Own<Replacer<kj::Directory>> replaceSubdir(
1597      kj::PathPtr path, kj::WriteMode mode) const override {
1598    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1599  }
1600  bool trySymlink(kj::PathPtr linkpath, kj::StringPtr content, kj::WriteMode mode) const override {
1601    KJ_UNIMPLEMENTED("this method is unused by SQLite");
1602  }
1603};
1604 
1605KJ_TEST("SQLite memory metering enforces SQLITE_NOMEM when limit is exceeded") {
1606  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1607  SqliteDatabase::Vfs vfs(*dir);
1608  // 64 KiB is large enough for the database to open and run simple queries,
1609  // but zeroblob(65536) requires allocating a 64 KiB buffer which, on top of
1610  // the memory already consumed by opening the database, pushes total usage
1611  // past the limit. This exercises our sqliteMemMalloc limit enforcement
1612  // rather than SQLite's own hard_heap_limit pragma.
1613  static constexpr size_t kTestMemoryLimit = 64 * 1024;
1614  SqliteDatabase db(
1615      vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY, kTestMemoryLimit);
1616  KJ_EXPECT_THROW_MESSAGE("out of memory: SQLITE_NOMEM", db.run("SELECT hex(zeroblob(65536))"));
1617}
1618 
1619KJ_TEST("SQLite memory metering tracks allocations correctly") {
1620  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1621  SqliteDatabase::Vfs vfs(*dir);
1622  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY,
1623      /*sqliteMaxMemoryBytes=*/1024 * 1024);
1624  size_t memoryBytes = db.getSqliteMemoryBytes();
1625  KJ_EXPECT(memoryBytes > 0, "memory should be greater than zero after creating a database");
1626  size_t memoryBytesSnapshot = memoryBytes;
1627 
1628  db.run("CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)");
1629  memoryBytes = db.getSqliteMemoryBytes();
1630  KJ_EXPECT(memoryBytes >= memoryBytesSnapshot, "memory should not decrease when creating a table");
1631  memoryBytesSnapshot = memoryBytes;
1632 
1633  db.run("INSERT INTO test VALUES (1, 'hello world')");
1634  memoryBytes = db.getSqliteMemoryBytes();
1635  KJ_EXPECT(
1636      memoryBytes >= memoryBytesSnapshot, "memory should not decrease when inserting into a table");
1637  memoryBytesSnapshot = memoryBytes;
1638 
1639  {
1640    // Note that query has a different scope so that it does not prevent `PRAGMA shrink_memory`
1641    // from taking effect.
1642    auto query = db.run("SELECT * FROM test WHERE id = 1");
1643    KJ_ASSERT(!query.isDone());
1644    KJ_EXPECT(query.getInt(0) == 1);
1645    KJ_EXPECT(query.getText(1) == "hello world");
1646    memoryBytes = db.getSqliteMemoryBytes();
1647    KJ_EXPECT(
1648        memoryBytes >= memoryBytesSnapshot, "memory should not decrease when querying a table");
1649    memoryBytesSnapshot = memoryBytes;
1650  }
1651 
1652  db.run("PRAGMA shrink_memory");
1653  memoryBytes = db.getSqliteMemoryBytes();
1654  KJ_EXPECT(memoryBytes < memoryBytesSnapshot,
1655      "memory should decrease when running `PRAGMA shrink_memory`");
1656}
1657 
1658KJ_TEST("I/O exceptions pass through SQLite") {
1659  auto dir = kj::atomicRefcounted<ErrorInjectableDirectory>();
1660  SqliteDatabase::Vfs vfs(*dir);
1661  SqliteDatabase db(vfs, kj::Path({"db"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1662 
1663  db.run({.regulator = SqliteDatabase::TRUSTED}, kj::str(R"(
1664    CREATE TABLE IF NOT EXISTS things (
1665      id INTEGER PRIMARY KEY AUTOINCREMENT,
1666      value INTEGER
1667    );
1668    INSERT INTO things(value) VALUES (123);
1669  )"));
1670 
1671  // Now arrange for an error on write().
1672  KJ_ASSERT_NONNULL(dir->dbFile)->error = KJ_EXCEPTION(FAILED, "test-vfs-error");
1673 
1674  // It should pass through.
1675  KJ_EXPECT_THROW_MESSAGE(
1676      "test-vfs-error", db.run({.regulator = SqliteDatabase::TRUSTED}, kj::str(R"(
1677    INSERT INTO things(value) VALUES (456);
1678  )")));
1679}
1680 
1681void testCriticalError(const char* expectedErrorMessage,
1682    kj::Function<void(SqliteDatabase&, SqliteDatabase::Vfs& vfs)> triggerErrorFn) {
1683  auto dir = kj::newInMemoryDirectory(kj::nullClock());
1684  SqliteDatabase::Vfs vfs(*dir);
1685  SqliteDatabase db(vfs, kj::Path({"foo"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1686 
1687  // Create a tracker to verify our callback is called
1688  bool criticalErrorCallbackCalled = false;
1689 
1690  // Register a critical error callback
1691  db.onCriticalError([&](kj::StringPtr errorMessage, kj::Maybe<kj::Exception> maybeException) {
1692    criticalErrorCallbackCalled = true;
1693    KJ_IF_SOME(exception, maybeException) {
1694      KJ_EXPECT(exception.getDescription().contains(expectedErrorMessage));
1695    } else {
1696      KJ_EXPECT(errorMessage.contains(expectedErrorMessage));
1697    }
1698  });
1699 
1700  KJ_EXPECT(!db.observedCriticalError());
1701  KJ_EXPECT_THROW_MESSAGE(expectedErrorMessage, triggerErrorFn(db, vfs));
1702 
1703  KJ_EXPECT(criticalErrorCallbackCalled);
1704  KJ_EXPECT(db.observedCriticalError());
1705}
1706 
1707KJ_TEST("SQLite critical error handling for SQLITE_IOERR") {
1708  auto dir = kj::atomicRefcounted<ErrorInjectableDirectory>();
1709  SqliteDatabase::Vfs vfs(*dir);
1710  SqliteDatabase db(vfs, kj::Path({"db"}), kj::WriteMode::CREATE | kj::WriteMode::MODIFY);
1711 
1712  // Create a tracker to verify our callback is called
1713  bool criticalErrorCallbackCalled = false;
1714  // Register a critical error callback
1715  db.onCriticalError([&](kj::StringPtr errorMessage, kj::Maybe<kj::Exception> maybeException) {
1716    criticalErrorCallbackCalled = true;
1717    KJ_IF_SOME(exception, maybeException) {
1718      KJ_EXPECT(exception.getDescription().contains("test-vfs-error"));
1719    } else {
1720      KJ_EXPECT(errorMessage.contains("test-vfs-error"));
1721    }
1722  });
1723 
1724  // Use a small cache size to force flushing to disk on even a small write
1725  db.run({.regulator = SqliteDatabase::TRUSTED}, "PRAGMA cache_size = 1");  // 1 page cache
1726 
1727  db.run({.regulator = SqliteDatabase::TRUSTED}, kj::str(R"(
1728    CREATE TABLE IF NOT EXISTS things (
1729      id INTEGER PRIMARY KEY AUTOINCREMENT,
1730      value INTEGER
1731    );
1732    INSERT INTO things(value) VALUES (123);
1733  )"));
1734 
1735  db.run("BEGIN TRANSACTION");
1736 
1737  // Now arrange for an error on write().
1738  KJ_ASSERT_NONNULL(dir->dbFile)->error = KJ_EXCEPTION(FAILED, "test-vfs-error");
1739 
1740  KJ_EXPECT(!db.observedCriticalError());
1741  KJ_EXPECT_THROW_MESSAGE(
1742      "test-vfs-error", db.run({.regulator = SqliteDatabase::TRUSTED}, kj::str(R"(
1743    INSERT INTO things(value) VALUES (456);
1744  )")));
1745 
1746  KJ_EXPECT(criticalErrorCallbackCalled);
1747  KJ_EXPECT(db.observedCriticalError());
1748}
1749 
1750// No test for SQLITE_BUSY as a critical error because we haven't been able to figure out how to
1751// trigger it in a way that causes an auto-rollback. It seems like an auto-rollback would only
1752// happen if the transaction had already included other writes before hitting SQLITE_BUSY,
1753// but if a transaction is open and has performed writes, then obviously the caller must hold
1754// the lock, and so would not be expected to see SQLITE_BUSY.
1755 
1756KJ_TEST("SQLite critical error handling for SQLITE_FULL") {
1757  testCriticalError("database or disk is full", [](SqliteDatabase& db, SqliteDatabase::Vfs& vfs) {
1758    // Set up a database with limited size
1759    db.run("PRAGMA max_page_count = 10");
1760    db.run("CREATE TABLE IF NOT EXISTS test_full (id INTEGER PRIMARY KEY, data BLOB)");
1761 
1762    db.run("BEGIN TRANSACTION");
1763 
1764    // Create a large blob to quickly fill the database
1765    auto largeData = kj::heapArray<byte>(100000, 'X');  // 100KB
1766 
1767    // This should eventually trigger SQLITE_FULL
1768    db.run({.regulator = SqliteDatabase::TRUSTED}, "INSERT INTO test_full VALUES (?, ?)", 1,
1769        largeData.asPtr());
1770  });
1771}
1772 
1773KJ_TEST("SQLite critical error handling for SQLITE_NOMEM") {
1774  testCriticalError("out of memory", [](SqliteDatabase& db, SqliteDatabase::Vfs& vfs) {
1775    db.run("CREATE TABLE test_nomem (id INTEGER PRIMARY KEY, data BLOB)");
1776    db.run(
1777        "CREATE TABLE test_refs (id INTEGER PRIMARY KEY, ref_id INTEGER, FOREIGN KEY(ref_id) REFERENCES test_nomem(id) ON DELETE CASCADE)");
1778 
1779    db.run("BEGIN TRANSACTION");
1780 
1781    db.run("INSERT INTO test_nomem VALUES (1, 'small data')");
1782    db.run("INSERT INTO test_refs VALUES (1, 1)");
1783 
1784    // Set SQLite's memory limit very low to trigger SQLITE_NOMEM
1785    db.run("PRAGMA hard_heap_limit=8192");  // 8KB limit
1786 
1787    // Create data that will exceed the memory limit
1788    auto largeData = kj::heapArray<byte>(50000, 'X');  // 50KB
1789 
1790    db.run({.regulator = SqliteDatabase::TRUSTED}, "INSERT INTO test_nomem VALUES (?, ?)", 2,
1791        largeData.asPtr());
1792  });
1793}
1794 
1795}  // namespace
1796}  // namespace workerd