Skip to content
rachel/workerd
File

Blob: src/workerd/util/sqlite.h

cpp1044 lines
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#pragma once
6 
7#include <workerd/util/account-limits.h>
8#include <workerd/util/sqlite-metering.h>
9 
10#include <kj/filesystem.h>
11#include <kj/function.h>
12#include <kj/list.h>
13#include <kj/one-of.h>
14#include <kj/string.h>
15 
16#include <utility>
17 
18struct sqlite3;
19struct sqlite3_vfs;
20struct sqlite3_stmt;
21 
22KJ_DECLARE_NON_POLYMORPHIC(sqlite3_stmt);
23 
24namespace workerd {
25 
26using kj::byte;
27using kj::uint;
28 
29// Used to collect periodic metrics about queries and size of sqlite db
30class SqliteObserver {
31 public:
32  void setDbWalSize(uint64_t dbWalSize) {
33    this->dbWalSize = dbWalSize;
34  }
35  uint64_t getDbWalSize() {
36    return dbWalSize;
37  }
38  kj::TimePoint now() {
39    return monotonicClock.now();
40  }
41  virtual void addQueryStats(uint64_t rowsRead, uint64_t rowsWritten) {}
42  // The method is not used by the SqliteDatabase, it is added here for convenience
43  virtual void setSqliteStoredBytes(uint64_t sqliteStoredBytes) {}
44 
45  virtual void reportQueryEvent(kj::Maybe<kj::String> queryStatement,
46      uint64_t queryRowsRead,
47      uint64_t queryRowsWritten,
48      kj::Duration queryLatency,
49      uint64_t dbWalBytesWritten,
50      int queryResult,
51      int extendedErrorCode,
52      bool isInternalQuery,
53      kj::Maybe<kj::String> queryErrorDescription) {}
54 
55  static SqliteObserver DEFAULT;
56 
57 private:
58  uint64_t dbWalSize = 0;
59  const kj::MonotonicClock& monotonicClock = kj::systemPreciseMonotonicClock();
60};
61 
62// C++/KJ API for SQLite.
63//
64// In addition to providing a more modern C++ interface vs. the classic C API, this API layers
65// SQLite on top of KJ's filesystem API. This means that you can use KJ's in-memory filesystem
66// implementation in unit tests. Meanwhile, though, if you do actually give it a `kj::Directory`
67// representing a true disk directory, the real SQLite disk implementation will be used with
68// all of its features.
69class SqliteDatabase {
70 public:
71  class Vfs;
72  class Query;
73  class Statement;
74  class Lock;
75  class LockManager;
76  struct VfsOptions;
77  class Regulator;
78 
79  struct QueryOptions {
80    const Regulator& regulator;
81    bool allowUnconfirmed = false;
82  };
83 
84  struct IngestResult {
85    kj::StringPtr remainder;
86    uint64_t rowsRead;
87    uint64_t rowsWritten;
88    uint64_t statementCount;
89  };
90 
91  SqliteDatabase(const Vfs& vfs,
92      kj::Path path,
93      kj::Maybe<kj::WriteMode> maybeMode = kj::none,
94      size_t sqliteMaxMemoryBytes = kj::maxValue,
95      SqliteObserver& sqliteObserver = SqliteObserver::DEFAULT,
96      kj::Maybe<const ActorAccountLimits&> actorAccountLimits = kj::none);
97 
98  // Returns the current value of the per-actor SQLite memory byte counter for metrics reporting.
99  size_t getSqliteMemoryBytes() const {
100    return sqliteMemoryBytes;
101  }
102 
103  ~SqliteDatabase() noexcept(false);
104  KJ_DISALLOW_COPY_AND_MOVE(SqliteDatabase);
105 
106  // Allows a SqliteDatabase to be passed directly into SQLite API functions where `sqlite*` is
107  // expected.
108  operator sqlite3*();
109 
110  // Class which regulates a SQL query, especially to control how queries created in JavaScript
111  // application code are handled.
112  //
113  // Note that any of the methods that check if actions are allowed my throw an exception instead
114  // of returning false. If they do, this exception will pass through to the caller in place of
115  // a generic "not authorized" exception.
116  class Regulator {
117   public:
118    // Returns whether the given name (which may be a table, index, view, etc.) is allowed to be
119    // accessed. Typically, this is used to deny access to names containing special prefixes
120    // indicating that they are privileged, like `_cf_`.
121    //
122    // This only applies to global names. Scoped names, such as column names, are not subject to
123    // authorization.
124    virtual bool isAllowedName(kj::StringPtr name) const {
125      return true;
126    }
127 
128    // Returns whether a given trigger or view name should be permitted to run as a side effect of a
129    // query running under this Regulator. This is a precaution to prevent application-defined
130    // triggers from executing under a privileged regulator.
131    //
132    // TODO(someday): In theory a trigger should run with the authority level under which it was
133    //   created, but how do we track that? In practice we probably never expect triggers to run on
134    //   trusted queries.
135    virtual bool isAllowedTrigger(kj::StringPtr name) const {
136      return false;
137    }
138 
139    // Report that an error occurred. `message` is the detail message constructed by SQLite. This
140    // function should typically throw an exception. If no exception is thrown, a simple KJ exception
141    // will be thrown after `onError()` returns.
142    //
143    // The purpose of this callback is to allow the JavaScript API bindings to throw a JSG exception.
144    //
145    // Note that SQLITE_MISUSE errors are NOT reported using `onError()` -- they will throw regular
146    // KJ exceptions in all cases. This is because SQLITE_MISUSE indicates a bug that could lead to
147    // undefined behavior. Such bugs are always in C++ code; JavaScript application code must be
148    // prohibited from causing such errors in the first place.
149    virtual void onError(kj::Maybe<int> sqliteErrorCode, kj::StringPtr message) const {}
150 
151    // Are BEGIN TRANSACTION and SAVEPOINT statements allowed? Note that if allowed, SAVEPOINT will
152    // also be subject to `isAllowedName()` for the savepoint name. If denied, the application will
153    // not be able to create any sort of transaction.
154    //
155    // In Durable Objects, we disallow these statements because the platform provides an explicit
156    // API for transactions that is safer (e.g. it automatically rolls back on throw). Also, the
157    // platform automatically wraps every entry into the isolate lock in a transaction.
158    virtual bool allowTransactions() const {
159      return true;
160    }
161 
162    // Whether or not this query's rows read and written should be recorded to the SqliteObserver
163    // when the query is done. (In other words, determines whether this query is billed.)
164    //
165    // We don't bill for TRUSTED queries since they are used internally by the system.
166    virtual bool shouldAddQueryStats() const {
167      return false;
168    }
169  };
170 
171  // Use as the `Regulator&` for queries that are fully trusted. As a general rule, this should
172  // be used if and only if the SQL query is a string literal.
173  static constexpr Regulator TRUSTED;
174 
175  // Prepares the given SQL code as a persistent statement that can be used across several queries.
176  // Don't use this for one-off queries; use run() instead.
177  Statement prepare(const Regulator& regulator, kj::StringPtr sqlCode);
178 
179  // Prepares a statement that may actually be multiple statements (separated by semicolons).
180  // In this case, the code is not actually parsed until first executed (this implies
181  // `prepareMulti()` will never throw since it doesn't actually do anything). This lazy-parsing
182  // behavior is necessary in the case that later statements depend on the effects of earlier ones.
183  // For example, the first statement might create a table, and the next statement insert into that
184  // table. SQLite will refuse to parse the insertion statement until the table has been created,
185  // so each statement must be executed before the next can be parsed.
186  //
187  // As with exec(), the result of executing a batch of multiple statements is always the result
188  // of the last statement. The results of all other statements are discarded.
189  Statement prepareMulti(const Regulator& regulator, kj::String sqlCode);
190 
191  // Convenience method to start a query. This is equivalent to `prepare(sqlCode).run(bindings...)`
192  // except:
193  // - It may be more efficient for one-off use case.
194  // - The code can include multiple statements, separated by semicolons. The bindings and returned
195  //   `Query` object are both associated with the last statement. This is particularly convenient
196  //   for doing database initialization such as creating several tables at once.
197  template <typename... Params>
198  Query run(QueryOptions options, kj::StringPtr sqlCode, Params&&... bindings);
199 
200  template <size_t size>
201  Statement prepare(const char (&sqlCode)[size]);
202 
203  template <size_t size>
204  Statement prepare(const Regulator& regulator, const char (&sqlCode)[size]);
205 
206  // When the input is a string literal, we automatically use the TRUSTED regulator.
207  template <size_t size, typename... Params>
208  Query run(const char (&sqlCode)[size], Params&&... bindings);
209 
210  // Invokes the given callback whenever a query begins which may write to the database. The
211  // callback is called just before executing the query.
212  //
213  // Durable Objects uses this to automatically begin a transaction and close the output gate.
214  //
215  // Note that the write callback is NOT called before (or at any point during) a reset(). Use the
216  // `ResetListener` mechanism or `afterReset()` instead for that case.
217  void onWrite(kj::Function<void(bool allowUnconfirmed)> callback) {
218    onWriteCallback = kj::mv(callback);
219  }
220 
221  // Invokes the given callback when a "critical error" causes an automatic rollback during a
222  // transaction.
223  //
224  // See: https://www.sqlite.org/lang_transaction.html#response_to_errors_within_a_transaction
225  void onCriticalError(
226      kj::Function<void(kj::StringPtr errorMessage, kj::Maybe<kj::Exception> maybeException)>
227          callback) {
228    onCriticalErrorCallback = kj::mv(callback);
229  }
230 
231  SqliteMemoryScope enterMemoryScope();
232 
233  // Returns true if a transaction was automatically rolled due to a critical error.
234  bool observedCriticalError();
235 
236  // Invoke the onWrite() callback.
237  //
238  // This is useful when the caller is about to execute a statement which SQLite considers
239  // read-only, but needs to be considered a write for our purposes. In particular, we use the
240  // onWrite callback to start automatic transactions, and we use the SAVEPOINT statement to
241  // implement explicit transactions. For synchronous transactions, the explicit transaction needs
242  // to be nested inside the automatic transaction, so we need to force an auto-transaction to
243  // start before the SAVEPOINT.
244  void notifyWrite(bool allowUnconfirmed = false);
245 
246  // Get the currently-executing SQL query for debug purposes. The query is normalized to hide
247  // any literal values that might contain sensitive information. This is intended to be safe for
248  // debug logs.
249  kj::StringPtr getCurrentQueryForDebug();
250 
251  // Helper to execute a chunk of SQL that may not be complete.
252  // Executes every valid statement provided, and returns the remaining portion of the input
253  // that was not processed. This is used for streaming SQL ingestion.
254  IngestResult ingestSql(const Regulator& regulator, kj::StringPtr sqlCode);
255 
256  // Execute a function with the given regulator.
257  void executeWithRegulator(const Regulator& regulator, kj::FunctionParam<void()> func);
258 
259  // Resets the database to an empty state by deleting the underlying database file and creating
260  // a new one in its place. This is the recommended way to "drop database" in SQLite, and is used
261  // to implement deleteAll() in Workers.
262  //
263  // reset() will cancel all outstanding queries (further attempts to use the cursors will throw).
264  // Prepared statements will be automatically reprepared the next time they are executed (which
265  // may throw if they depend on tables that haven't been recreated yet).
266  void reset();
267 
268  // Objects that need to be notified when reset() is called may inherit `ResetListener`.
269  class ResetListener {
270   public:
271    ResetListener(SqliteDatabase& db): db(db) {
272      db.resetListeners.add(*this);
273    }
274    ~ResetListener() {
275      if (link.isLinked()) db.resetListeners.remove(*this);
276    }
277    ResetListener(ResetListener&& other): db(other.db) {
278      db.resetListeners.remove(other);
279      db.resetListeners.add(*this);
280    }
281 
282    // When the database's `reset()` method is called, all listeners' `beforeSqliteReset()` will be
283    // called before actually resetting the database.
284    virtual void beforeSqliteReset() = 0;
285 
286   protected:  // so that subclasses don't have to store their own copy of the `db` reference
287    SqliteDatabase& db;
288 
289   private:
290    kj::ListLink<ResetListener> link;
291 
292    friend class SqliteDatabase;
293  };
294 
295  // Registers a callback to call after a reset completes. This can be used to do basic database
296  // initialization, e.g. set WAL mode. (To get notified *before* a reset, use `ResetListener`.)
297  //
298  // Note that the on-write callback is disabled during reset(), including while calling the
299  // after-reset callback. So, queries performed by the after-reset callback will not trigger the
300  // on-write callback.
301  void afterReset(kj::Function<void(SqliteDatabase&)> callback) {
302    afterResetCallback = kj::mv(callback);
303  }
304 
305  // Register a callback which shall be called if the current transaction is rolled back. If the
306  // current transaction commits, then the callback is discarded without invoking it.
307  //
308  // This method correctly handles savepoint stacks. The callback is invoked if any savepoint
309  // currently in the stack ends up being rolled back.
310  //
311  // This is useful when implementing any sort of in-memory caching which must stay in sync with
312  // the database state. The callback can be used to invalidate the cache, or even revert it to
313  // a previous value.
314  //
315  // When a rollback occurs, callbacks are invoked in the reverse of the order in which they were
316  // registered. The database content is rolled back first, before invoking any callbacks.
317  // Callbacks may read from the database, but must not write to it.
318  void onRollback(kj::Function<void()> callback) {
319    if (inTransaction || !savepoints.empty()) {
320      rollbackCallbacks.add(kj::mv(callback));
321    }
322  }
323 
324 private:
325  const Vfs& vfs;
326  kj::Path path;
327  bool readOnly;
328  SqliteObserver& sqliteObserver;
329 
330  // The amount of memory in bytes used by this database for use by sqlite3_mem_methods.
331  size_t sqliteMemoryBytes = 0;
332 
333  // The maximum amount of memory in bytes that can be used by this database for use by
334  // sqlite3_mem_methods (from WorkerLimits::sqliteMaxMemoryMb). This is set to kj::maxValue to
335  // when running in workerd local development mode.
336  size_t sqliteMaxMemoryBytes;
337 
338  kj::Maybe<const ActorAccountLimits&> actorAccountLimits;
339 
340  // This pointer can be left null if a call to reset() failed to re-open the database.
341  kj::Maybe<sqlite3&> maybeDb;
342 
343  // Set while a query is compiling.
344  kj::Maybe<const Regulator&> currentRegulator;
345 
346  // Set during the *first* time a statement is being compiled, to capture information about it
347  // from the authorizer callback. It is assumed that if the statement must be re-parsed later,
348  // the same data would be gathered, so `currentParseContext` is left null on re-parse.
349  struct ParseContext;
350  kj::Maybe<ParseContext&> currentParseContext;
351 
352  // Set while a statement is executing.
353  kj::Maybe<sqlite3_stmt&> currentStatement;
354 
355  bool criticalErrorOccurred = false;
356  kj::Maybe<kj::Function<void(bool allowUnconfirmed)>> onWriteCallback;
357  kj::Maybe<kj::Function<void(kj::StringPtr errorMessage, kj::Maybe<kj::Exception> maybeException)>>
358      onCriticalErrorCallback;
359  kj::Maybe<kj::Function<void(SqliteDatabase&)>> afterResetCallback;
360 
361  kj::List<ResetListener, &ResetListener::link> resetListeners;
362 
363  // Callbacks registered with onRollback that haven't been committed nor rolled back yet.
364  kj::Vector<kj::Function<void()>> rollbackCallbacks;
365 
366  struct Savepoint {
367    kj::String name;
368 
369    // Size of `rollbackCallbackIndex` when this savepoint was created.
370    size_t rollbackCallbackIndex;
371  };
372 
373  // Savepoints that haven't been committed nor rolled back yet.
374  kj::Vector<Savepoint> savepoints;
375 
376  // True if in a BEGIN TRANSACTION transaction.
377  bool inTransaction = false;
378 
379  void init(kj::Maybe<kj::WriteMode> maybeMode);
380 
381  // Describes various kinds of interesting state changes which a statement might apply, which we
382  // need to track to implement the SqliteDatabse interface. In particular, we must track
383  // transactions to implement the onRollback() method.
384  struct NoChange {};
385  struct BeginTxn {
386    kj::Maybe<kj::String> savepointName;
387  };
388  struct CommitTxn {
389    kj::Maybe<kj::String> savepointName;
390  };
391  struct RollbackTxn {
392    kj::Maybe<kj::String> savepointName;
393  };
394  using StateChange = kj::OneOf<NoChange, BeginTxn, CommitTxn, RollbackTxn>;
395 
396  // Called immediately after a statement executes, to update our understanding of the current
397  // state.
398  void applyChange(const StateChange& change);
399 
400  void handleCriticalError(kj::Maybe<int> errorCode,
401      kj::StringPtr errorMessage,
402      kj::Maybe<const kj::Exception&> exception);
403 
404  enum Multi { SINGLE, MULTI };
405 
406  // A pair of a compiled statement, and a description of the interesting state changes it applies.
407  struct StatementAndEffect {
408    kj::Own<sqlite3_stmt> statement;
409    StateChange stateChange;
410  };
411 
412  // Helper to call sqlite3_prepare_v3().
413  //
414  // In SINGLE mode, an exception is thrown if `sqlCode` contains multiple statements.
415  //
416  // In MULTI mode, if `sqlCode` contains multiple statements, each statement before the last one
417  // is executed immediately. The returned object represents the last statement.
418  //
419  // If `prelude` is provided, then, in MULTI mode, all statements which are executed immediately
420  // are also appended to `prelude`.
421  StatementAndEffect prepareSql(const Regulator& regulator,
422      kj::StringPtr sqlCode,
423      uint prepFlags,
424      Multi multi,
425      kj::Maybe<kj::Vector<Statement>&> prelude = kj::none);
426 
427  // Implements SQLite authorizer callback, see sqlite3_set_authorizer().
428  bool isAuthorized(int actionCode,
429      kj::Maybe<kj::StringPtr> param1,
430      kj::Maybe<kj::StringPtr> param2,
431      kj::Maybe<kj::StringPtr> dbName,
432      kj::Maybe<kj::StringPtr> triggerName);
433 
434  // Implements SQLite authorizer for 'temp' DB
435  bool isAuthorizedTemp(int actionCode,
436      const kj::Maybe<kj::StringPtr>& param1,
437      const kj::Maybe<kj::StringPtr>& param2,
438      const Regulator& regulator);
439 
440  void setupSecurity(sqlite3* db);
441 
442  struct ParseContext {
443    // What kind of state change does this statement cause, if any?
444    StateChange stateChange = NoChange();
445 
446    // If the parse fails because the authorizer rejects it, it may fill in `authError` to provide
447    // a more friendly error message. This error will be thrown by the overall query. Otherwise,
448    // a generic "not authorized" error is thrown.
449    kj::Maybe<kj::Exception> authError;
450  };
451};
452 
453// Represents a prepared SQL statement, which can be executed many times.
454class SqliteDatabase::Statement final: private ResetListener {
455 public:
456  Statement(Statement&& other)
457      : ResetListener(kj::mv(other)),
458        regulator(other.regulator),
459        stmt(kj::mv(other.stmt)),
460        prelude(kj::mv(other.prelude)) {}
461 
462  // Destructor needs to install a memory scope for sqlite3_finalize.
463  ~Statement() noexcept(false);
464 
465  // Convenience method to start a query. This is equivalent to:
466  //
467  //     SqliteDatabase::Query(db, statement, bindings...);
468  //
469  // `bindings` are the values to fill into `?`s in the statement. Each value in `bindings` must
470  // be one of the types of Query::ValuePtr. Alternatively, `bindings` can be a single parameter
471  // of type `ArrayPtr<const Query::ValuePtr>` to initialize bindings from an array.
472  //
473  // Any strings or byte blobs in the bindings must remain valid until the `Query` is destroyed.
474  // However, when passing `bindings` as an array, the outer array need only remain valid until
475  // this method returns.
476  template <typename... Params>
477  Query run(Params&&... bindings);
478 
479  struct StatementOptions {
480    bool allowUnconfirmed = false;
481  };
482 
483  template <typename... Params>
484  Query run(StatementOptions options, Params&&... bindings);
485 
486 private:
487  const Regulator& regulator;
488  kj::OneOf<kj::String, StatementAndEffect> stmt;
489 
490  // List of statements to execute before this one. Only non-empty if this Statement was created
491  // by prepareMulti().
492  kj::Vector<Statement> prelude;
493 
494  Statement(SqliteDatabase& db, const Regulator& regulator, StatementAndEffect stmt)
495      : ResetListener(db),
496        regulator(regulator),
497        stmt(kj::mv(stmt)) {}
498 
499  // Lazily-parsed statement -- used by `prepareMulti()`.
500  Statement(SqliteDatabase& db, const Regulator& regulator, kj::String sqlCode)
501      : ResetListener(db),
502        regulator(regulator),
503        stmt(kj::mv(sqlCode)) {}
504 
505  void beforeSqliteReset() override;
506 
507  // Get the underlying StatementAndEffect, which the caller will then execute. If `prelude` is
508  // non-empty, prepareForExecution() actually executes the prelude.
509  StatementAndEffect& prepareForExecution();
510 
511  friend class SqliteDatabase;
512};
513 
514// Represents one SQLite query.
515//
516// Only one Query can exist at a time, for a given database. It should probably be allocated on
517// the stack.
518class SqliteDatabase::Query final: private ResetListener {
519 public:
520  using ValuePtr =
521      kj::OneOf<kj::ArrayPtr<const byte>, kj::StringPtr, int64_t, double, decltype(nullptr)>;
522 
523  // Construct using Statement::run() or SqliteDatabase::run().
524 
525  ~Query() noexcept(false);
526  KJ_DISALLOW_COPY_AND_MOVE(Query);
527 
528  // Row IO counter.
529  uint64_t getRowsRead();
530  // Row IO counter.
531  uint64_t getRowsWritten();
532 
533  // If true, there are no more rows. (When true, the methods below must not be called.)
534  bool isDone() {
535    return done;
536  }
537 
538  // For INSERT, UPDATE, or DELETE queries, returns the number of rows changed. For other query
539  // types the result is undefined.
540  uint changeCount();
541 
542  // Advance to the next row.
543  void nextRow() {
544    nextRow(/*first=*/false);
545  }
546 
547  // How many columns does each row of the result have?
548  uint columnCount();
549 
550  // Get the value at the given column, as whatever type was actually returned.
551  //
552  // Returned pointers (strings and blobs) remain valid only until either (a) nextRow() is called,
553  // or (b) a different get method is called on the same column but with a different type.
554  ValuePtr getValue(uint column);
555 
556  // Get the name of a specific column.
557  kj::StringPtr getColumnName(uint column);
558 
559  // Get the value at the given column, coercing it to the desired type according to SQLite rules.
560  kj::ArrayPtr<const byte> getBlob(uint column);
561 
562  // Get the value at the given column, coercing it to the desired type according to SQLite rules.
563  kj::StringPtr getText(uint column);
564 
565  // Get the value at the given column, coercing it to the desired type according to SQLite rules.
566  int getInt(uint column);
567 
568  // Get the value at the given column, coercing it to the desired type according to SQLite rules.
569  int64_t getInt64(uint column);
570 
571  // Get the value at the given column, coercing it to the desired type according to SQLite rules.
572  double getDouble(uint column);
573 
574  // Get the value at the given column, coercing it to the desired type according to SQLite rules.
575  bool isNull(uint column);
576 
577  kj::Maybe<kj::ArrayPtr<const byte>> getMaybeBlob(uint column) {
578    if (isNull(column)) {
579      return kj::none;
580    } else {
581      return getBlob(column);
582    }
583  }
584  kj::Maybe<kj::StringPtr> getMaybeText(uint column) {
585    if (isNull(column)) {
586      return kj::none;
587    } else {
588      return getText(column);
589    }
590  }
591  kj::Maybe<int> getMaybeInt(uint column) {
592    if (isNull(column)) {
593      return kj::none;
594    } else {
595      return getInt(column);
596    }
597  }
598  kj::Maybe<int64_t> getMaybeInt64(uint column) {
599    if (isNull(column)) {
600      return kj::none;
601    } else {
602      return getInt64(column);
603    }
604  }
605  kj::Maybe<double> getMaybeDouble(uint column) {
606    if (isNull(column)) {
607      return kj::none;
608    } else {
609      return getDouble(column);
610    }
611  }
612 
613 private:
614  class QueryEvent {
615   public:
616    explicit QueryEvent(SqliteObserver& sqliteObserver)
617        : observer(sqliteObserver),
618          dbWalSizeBefore(sqliteObserver.getDbWalSize()),
619          startTime(sqliteObserver.now()) {}
620 
621    ~QueryEvent() noexcept(false) {
622      uint64_t dbWalSizeAfter = observer.getDbWalSize();
623      uint64_t dbWalBytesWritten = (dbWalSizeAfter - dbWalSizeBefore);
624      kj::Duration queryLatency = observer.now() - startTime;
625 
626      observer.reportQueryEvent(kj::mv(queryStatement), rowsRead, rowsWritten, queryLatency,
627          dbWalBytesWritten, queryResult, extendedErrorCode, isInternalQuery,
628          kj::mv(queryErrorDescription));
629    }
630 
631    void setQueryEventStats(uint64_t rowsRead, uint64_t rowsWritten, bool isInternalQuery) {
632      this->rowsRead = rowsRead;
633      this->rowsWritten = rowsWritten;
634      this->isInternalQuery = isInternalQuery;
635    }
636 
637    void setQueryStatement(kj::String queryStatement) {
638      this->queryStatement = kj::mv(queryStatement);
639    }
640 
641    void setQueryErrorDescription(kj::String queryErrorDescription) {
642      this->queryErrorDescription = kj::mv(queryErrorDescription);
643    }
644 
645    void setQueryResult(int res) {
646      queryResult = res;
647    }
648 
649    void setQueryExtendedCode(int res) {
650      extendedErrorCode = res;
651    }
652 
653   private:
654    SqliteObserver& observer;
655    kj::Maybe<kj::String> queryStatement = kj::none;
656    bool isInternalQuery = false;
657    uint64_t dbWalSizeBefore;
658    kj::TimePoint startTime;
659    uint64_t rowsRead = 0;
660    uint64_t rowsWritten = 0;
661    int queryResult = 0;
662    int extendedErrorCode = 0;
663    kj::Maybe<kj::String> queryErrorDescription = kj::none;
664  };
665 
666  const Regulator& regulator;
667  StatementAndEffect ownStatement;                // for one-off queries
668  kj::Maybe<StatementAndEffect&> maybeStatement;  // null if database was reset
669  bool done = false;
670  QueryEvent queryEvent;
671 
672  // Storing the rowsRead and rowsWritten here to use in cases where a DB is reset.
673  // When the DB is reset, getRowdRead and getRowsWritten will fail as the statement they
674  // refer to gets destroyed as part of the reset process.
675  uint64_t rowsRead = 0;
676  uint64_t rowsWritten = 0;
677 
678  // Whether this query allows unconfirmed writes.
679  bool allowUnconfirmed = false;
680 
681  friend class SqliteDatabase;
682 
683  Query(SqliteDatabase& db,
684      QueryOptions options,
685      Statement& statement,
686      kj::ArrayPtr<const ValuePtr> bindings);
687  Query(SqliteDatabase& db,
688      QueryOptions options,
689      kj::StringPtr sqlCode,
690      kj::ArrayPtr<const ValuePtr> bindings);
691  template <typename... Params>
692  Query(SqliteDatabase& db, QueryOptions options, Statement& statement, Params&&... bindings)
693      : ResetListener(db),
694        regulator(options.regulator),
695        maybeStatement(statement.prepareForExecution()),
696        queryEvent(this->db.sqliteObserver),
697        allowUnconfirmed(options.allowUnconfirmed) {
698    // If we throw from the constructor, the destructor won't run. Need to call destroy()
699    // explicitly.
700    KJ_ON_SCOPE_FAILURE(destroy());
701    bindAll(std::index_sequence_for<Params...>(), kj::fwd<Params>(bindings)...);
702  }
703  template <typename... Params>
704  Query(SqliteDatabase& db, QueryOptions options, kj::StringPtr sqlCode, Params&&... bindings)
705      : ResetListener(db),
706        regulator(options.regulator),
707        ownStatement(db.prepareSql(regulator, sqlCode, 0, MULTI)),
708        maybeStatement(ownStatement),
709        queryEvent(this->db.sqliteObserver),
710        allowUnconfirmed(options.allowUnconfirmed) {
711    // If we throw from the constructor, the destructor won't run. Need to call destroy()
712    // explicitly.
713    KJ_ON_SCOPE_FAILURE(destroy());
714    bindAll(std::index_sequence_for<Params...>(), kj::fwd<Params>(bindings)...);
715  }
716 
717  void checkRequirements(size_t size);
718 
719  void init(kj::ArrayPtr<const ValuePtr> bindings);
720  void destroy();
721 
722  void bind(uint column, ValuePtr value);
723  void bind(uint column, kj::ArrayPtr<const byte> value);
724  void bind(uint column, kj::StringPtr value);
725  void bind(uint column, long long value);
726  void bind(uint column, double value);
727  void bind(uint column, decltype(nullptr));
728 
729  void handleCriticalError(kj::Maybe<int> errorCode,
730      kj::StringPtr errorMessage,
731      kj::Maybe<const kj::Exception&> maybeException) {
732    db.handleCriticalError(errorCode, errorMessage, maybeException);
733  }
734 
735  // Some reasonable automatic conversions.
736 
737  inline void bind(uint column, int value) {
738    bind(column, static_cast<long long>(value));
739  }
740  inline void bind(uint column, uint value) {
741    bind(column, static_cast<long long>(value));
742  }
743  inline void bind(uint column, long value) {
744    bind(column, static_cast<long long>(value));
745  }
746  inline void bind(uint column, float value) {
747    bind(column, static_cast<double>(value));
748  }
749 
750  template <typename... T, size_t... i>
751  void bindAll(std::index_sequence<i...>, T&&... value) {
752    checkRequirements(sizeof...(T));
753    (bind(i, kj::fwd<T>(value)), ...);
754    nextRow(/*first=*/true);
755  }
756 
757  StatementAndEffect& getStatementAndEffect();
758  sqlite3_stmt* getStatement() {
759    return getStatementAndEffect().statement;
760  }
761 
762  void beforeSqliteReset() override;
763 
764  void nextRow(bool first);
765};
766 
767// Options affecting SqliteDatabase::Vfs onstructor.
768struct SqliteDatabase::VfsOptions {
769 
770  // Value that should be returned by the SQLite VFS's xDeviceCharacteristics method. This is
771  // a combination of SQLITE_IOCAP_* flags which can improve performance if the device is known
772  // to provide certain guarantees.
773  //
774  // SQLite's default filesystem driver sets this to 0 on unix. On Windows, it sets the
775  // SQLITE_IOCAP_UNDELETABLE_WHEN_OPEN flag. SQLite also lets the application enable
776  // SQLITE_IOCAP_POWERSAFE_OVERWRITE explicitly via the SQLITE_FCNTL_POWERSAFE_OVERWRITE file
777  // control, or the `?psow=1` URL parameter. It is believed that almost all modern disks support
778  // powersafe overwrite, and being able to assume this significantly improves performance.
779  // See: https://www.sqlite.org/psow.html Because it's almost always desirable, this
780  // implementation enables powersafe overwrite by default.
781  //
782  // Note that when the underlying directory is a real disk directory, then this implementation
783  // will fall back to the native VFS implementation. In that case, the options you set here will
784  // be ORed with the ones set by the underlying VFS.
785  int deviceCharacteristics = 0x00001000;  // = SQLITE_FCNTL_POWERSAFE_OVERWRITE
786};
787 
788// Implements a SQLite VFS based on a KJ directory.
789//
790// If the directory is detected to be a disk directory (i.e. getFd() or getWin32Handle() returns
791// non-null), this VFS implementation will actually delegate to the built-in one. This ensures
792// feature-parity for production use.
793//
794// If the directory is not a disk directory, then the VFS will actually use the KJ APIs, but
795// some features will be missing. Most importantly, as of this writing, KJ filesystem APIs do
796// not support locks, so all locking will be ignored.
797//
798// An instance of `Vfs` can safely be used across multiple threads.
799class SqliteDatabase::Vfs {
800 public:
801  // Pretend `Options` is declared nested here. Due to a C++ quirk, we cannot actually declare it
802  // nested while having default-initialized parameters of this type.
803  using Options = VfsOptions;
804 
805  // Create a VFS backed by the given kj::Directory.
806  //
807  // If the directory is a real disk directory (i.e. getFd() returns non-null), then this will
808  // use SQLite's native filesystem implementation AND locking implementation. This is what you
809  // want when opening a database that could simultaneously be opened by other programs which may
810  // not be using this wrapper library.
811  //
812  // If the directory is NOT a real disk directory, this constructor will only arrange to do
813  // locking between clients that use the same Vfs object. This makes sense for in-memory temporary
814  // filesystems and other cases where the application can ensure all clients are using the same
815  // Vfs. If, somehow, the same database file is opened for write via two different `Vfs` instances,
816  // it will likely become corrupted.
817  explicit Vfs(const kj::Directory& directory, Options options = {});
818 
819  // Create a VFS with custom lock management.
820  //
821  // Unlike the other constructor, this version never uses SQLite's native VFS implementation.
822  // `lockManager` will be responsible for coordinating access between multiple concurrent clients
823  // of the same database.
824  explicit Vfs(
825      const kj::Directory& directory, const LockManager& lockManager, Options options = {});
826 
827  ~Vfs() noexcept(false);
828 
829  // Unfortunately, all SQLite VFSes must be registered in a global list with unique names, and
830  // then the _name_ must be passed to sqlite3_open_v2() to use it when opening a database. This is
831  // dumb, you should instead be able to simply pass the sqlite3_vfs* when opening the database,
832  // but this is the way it is. To work around this, each VFS is assigned an auto-generated unique
833  // name.
834  //
835  // TODO(cleanup): Patch SQLite to allow passing the pointer in?
836  kj::StringPtr getName() const {
837    return name;
838  }
839 
840  KJ_DISALLOW_COPY_AND_MOVE(Vfs);
841 
842 private:
843  const kj::Directory& directory;
844  kj::Own<LockManager> ownLockManager;
845  const LockManager& lockManager;
846  Options options;
847 
848  // Value returned by getName();
849  kj::String name = makeName();
850 
851  sqlite3_vfs& native;       // the system's default VFS implementation
852  kj::Own<sqlite3_vfs> vfs;  // our VFS
853 
854  // Result of `directory.getFd()`, if it returns non-null. Cached here for convenience.
855  int rootFd = -1;
856 
857  template <typename T, T slot>
858  struct MethodWrapperHack;
859 
860  struct WrappedNativeFileImpl;
861  // Create a VFS definition that wraps the native VFS implementation except that it treats our
862  // `directory` as the root. Requires that the directory is a real disk directory (and `rootFd`
863  // is filled in).
864  sqlite3_vfs makeWrappedNativeVfs();
865 
866  struct FileImpl;
867  // Create a VFS definition that actually delegates to the KJ filesystem.
868  sqlite3_vfs makeKjVfs();
869 
870  // Create the value returned by `getName()`. Called once at construction time and cached in
871  // `name`.
872  kj::String makeName();
873 
874  // Tries to create a new path by appending the given path to this VFS's root directory path.
875  // This allows us to use the system's default VFS implementation, without wrapping, by passing
876  // the result of this function to sqlite3_open_v2().
877  //
878  // Unfortunately, this requires getting a file path from a kj::Directory. On Windows, we can use
879  // the GetFinalPathNameByHandleW() API. On Unix, there's no portable way to do this.
880  kj::Maybe<kj::Path> tryAppend(kj::PathPtr suffix) const;
881 
882  friend class SqliteDatabase;
883  class DefaultLockManager;
884};
885 
886class SqliteDatabase::LockManager {
887 public:
888  // Obtain a lock for the given database path. The main database file is also provided in case
889  // it is useful. This method only creates the `Lock` object; it's level starts out as UNLOCKED,
890  // meaning no actual lock is held yet.
891  //
892  // `lock()` is only invoked for main database files. SQLite opens other files (journal, WAL); no
893  // `Lock` object is obtained for these.
894  //
895  // If the same database file is opened multiple times via the same `Vfs`, a separate `Lock`
896  // will be obtained each time, so that these locks can coordinate between databases in the
897  // same process. Since typically these databases would be in separate threads, the `lock()`
898  // method is thread-safe (hence `const`). However, a `Lock` instance itself is only accessed
899  // from the calling thread.
900  virtual kj::Own<Lock> lock(kj::PathPtr path, const kj::ReadableFile& mainDatabaseFile) const = 0;
901};
902 
903// Implements file locks and shared memory space used to coordination between clients of a
904// particular database. It is expected that if the database is accessible from other processes,
905// this object will coordinate with them.
906//
907// When using a Vfs based on a regular disk directory, this class isn't used; instead, SQLite's
908// native implementation kicks in, which is based on advisory file locks at the OS level, as well
909// as mmapped shared memory from a file next to the database with suffix `-shm`.
910class SqliteDatabase::Lock {
911 public:
912  // The main database can be locked at one of these levels.
913  //
914  // See the SQLite documentation for an explanation of lock levels:
915  //     https://www.sqlite.org/lockingv3.html
916  //
917  // Note, however, that this locking scheme is mostly unused in WAL mode, which everyone should
918  // be using now. In WAL mode, clients almost always have only a `SHARED` lock. It is increased
919  // to `EXCLUSIVE` only when shutting down the database, in order to safely delete the WAL and
920  // WAL-index (-shm) files.
921  //
922  // (The values of this enum correspond to the SQLITE_LOCK_* constants, but we're trying to
923  // avoid including sqlite's header here.)
924  enum Level { UNLOCKED, SHARED, RESERVED, PENDING, EXCLUSIVE };
925 
926  // Increase the lock's level. Returns false if the requested level is not available. This
927  // method never blocks; SQLite takes care of retrying if needed. Per SQLite docs, if an attempt
928  // to request an EXCLUSIVE lock fails because of other shared locks (but not other exclusive
929  // locks), the lock will still have transitioned to the PENDING state, which prevents new shared
930  // locks from being taken.
931  //
932  // The Lock starts an level UNLOCKED.
933  virtual bool tryIncreaseLevel(Level level) = 0;
934 
935  // Reduce the lock's level. `level` is either UNLOCKED or SHARED.
936  virtual void decreaseLevel(Level level) = 0;
937 
938  // Check if any client has a RESERVED lock on the database.
939  virtual bool checkReservedLock() = 0;
940 
941  // Get a shared memory region. All regions have the same size, so `size` will be the same for
942  // every call. If `index` exceeds the number of regions that exist so far, and `extend` is false,
943  // this returns an empty array, but if `extend` is true, all regions through the given index are
944  // created (containing zeros).
945  //
946  // The returned array is valid until the object is destroyed, or clearSharedMemory() is called.
947  virtual kj::ArrayPtr<byte> getSharedMemoryRegion(uint index, uint size, bool extend) = 0;
948 
949  // Deletes all shared memory regions.
950  //
951  // Called when shutting down the last database client or converting away from WAL mode. The
952  // caller will obtain an exclusive lock before calling this.
953  //
954  // The LockManager is also allowed to discard shared memory automatically any time it knows for
955  // sure that there are no clients.
956  virtual void clearSharedMemory() = 0;
957 
958  // Attempt to obtain shared or exclusive locks for the given WAL-mode lock indices, which are in
959  // the range [0, WAL_LOCK_COUNT). Returns true if the locks were successfully obtained (for all
960  // of them), false if at least one lock wasn't available (in which case no change was made). A
961  // shared lock can be obtained as long as there are no exclusive locks. An exclusive lock can be
962  // obtained as long as there are no other locks of any kind.
963  //
964  // The caller may request a shared lock multiple times, in which case it is expected to unlock
965  // the same number of times.
966  virtual bool tryLockWalShared(uint start, uint count) = 0;
967 
968  // Attempt to obtain shared or exclusive locks for the given WAL-mode lock indices, which are in
969  // the range [0, WAL_LOCK_COUNT). Returns true if the locks were successfully obtained (for all
970  // of them), false if at least one lock wasn't available (in which case no change was made). A
971  // shared lock can be obtained as long as there are no exclusive locks. An exclusive lock can be
972  // obtained as long as there are no other locks of any kind.
973  //
974  // The caller may request a shared lock multiple times, in which case it is expected to unlock
975  // the same number of times.
976  virtual bool tryLockWalExclusive(uint start, uint count) = 0;
977 
978  // Release a previously-obtained WAL-mode lock.
979  virtual void unlockWalShared(uint start, uint count) = 0;
980 
981  // Release a previously-obtained WAL-mode lock.
982  virtual void unlockWalExclusive(uint start, uint count) = 0;
983 
984  // There are exactly this many WAL-mode locks.
985  static constexpr uint WAL_LOCK_COUNT = 8;
986 
987  // Lock names as defined by https://www.sqlite.org/walformat.html#wal_locks
988  static constexpr uint WAL_WRITE_LOCK = 0;
989  static constexpr uint WAL_CKPT_LOCK = 1;
990  static constexpr uint WAL_RECOVER_LOCK = 2;
991  static constexpr uint WAL_READ_LOCK_BASE = 3;
992 
993  // There are exactly this may WAL-mode read-mark locks.
994  static constexpr uint WAL_READ_LOCK_COUNT = WAL_LOCK_COUNT - WAL_READ_LOCK_BASE;
995 
996  // SQLite sets aside bytes [120, 128) of the first shared memory region for use by the WAL locking
997  // implementation. SQLite will never touch these bytes. This may or may not be needed by your
998  // implementation. SQLite's native implementation on Windows acquires locks on these specific
999  // bytes because Windows file locks are mandatory, meaning they actually block concurrent reads
1000  // and writes. SQLite really wants "advisory" locks which block other locks but don't actually
1001  // block reads and writes. So, it applies the mandatory locks to these bytes which are never
1002  // otherwise read nor written.
1003  static constexpr uint RESERVED_LOCK_BYTES_OFFSET = 120;
1004};
1005 
1006template <typename... Params>
1007SqliteDatabase::Query SqliteDatabase::run(
1008    QueryOptions options, kj::StringPtr sqlCode, Params&&... params) {
1009  return Query(*this, options, sqlCode, kj::fwd<Params>(params)...);
1010}
1011 
1012template <typename... Params>
1013SqliteDatabase::Query SqliteDatabase::Statement::run(Params&&... params) {
1014  return Query(db, QueryOptions{.regulator = regulator}, *this, kj::fwd<Params>(params)...);
1015}
1016 
1017template <typename... Params>
1018SqliteDatabase::Query SqliteDatabase::Statement::run(StatementOptions options, Params&&... params) {
1019  return Query(db, {.regulator = regulator, .allowUnconfirmed = options.allowUnconfirmed}, *this,
1020      kj::fwd<Params>(params)...);
1021}
1022 
1023template <size_t size, typename... Params>
1024SqliteDatabase::Query SqliteDatabase::run(const char (&sqlCode)[size], Params&&... params) {
1025  return Query(*this, QueryOptions{.regulator = TRUSTED}, sqlCode, kj::fwd<Params>(params)...);
1026}
1027 
1028template <size_t size>
1029SqliteDatabase::Statement SqliteDatabase::prepare(const char (&sqlCode)[size]) {
1030  return prepare(TRUSTED, kj::StringPtr(sqlCode, size - 1));
1031}
1032template <size_t size>
1033SqliteDatabase::Statement SqliteDatabase::prepare(
1034    const Regulator& regulator, const char (&sqlCode)[size]) {
1035  return prepare(regulator, kj::StringPtr(sqlCode, size - 1));
1036}
1037 
1038inline SqliteDatabase::Statement SqliteDatabase::prepareMulti(
1039    const Regulator& regulator, kj::String sqlCode) {
1040  return Statement(*this, regulator, kj::mv(sqlCode));
1041}
1042 
1043}  // namespace workerd