Add the pause/unpause capability to the opfs-sahpool VFS, as discussed in [forum:fe8cdb8431c|forum thread fe8cdb8431c]. Summary: this gives clients a way to eke some degree of multi-page/tab/Worker concurrency out of this VFS but requires that coordination to be implemented client-side, e.g. via a SharedWorker or WebLocks.

FossilOrigin-Name: b5dbd521951e129b4dec69f191a872500dbf387b34a8479ad58b053ffcccbab9

8 files changed, 468 insertions, 27 deletions

diff --git a/ext/wasm/GNUmakefile b/ext/wasm/GNUmakefile
index c9852e389..572a3cd33 100644
--- a/ext/wasm/GNUmakefile
+++ b/ext/wasm/GNUmakefile
@@ -103,7 +103,7 @@ else
   ifeq (,$(filter $(OPTIMIZED_TARGETS),$(MAKECMDGOALS)))
     $(info ==============================================================)
     $(info == Development build. Make one of (dist, snapshot) for a)
-    $(info == smaller release build.)
+    $(info == smaller and faster release build.)
     $(info ==============================================================)
   endif
 endif
diff --git a/ext/wasm/api/sqlite3-vfs-opfs-sahpool.c-pp.js b/ext/wasm/api/sqlite3-vfs-opfs-sahpool.c-pp.js
index 6551b5c89..e1e7b45b8 100644
--- a/ext/wasm/api/sqlite3-vfs-opfs-sahpool.c-pp.js
+++ b/ext/wasm/api/sqlite3-vfs-opfs-sahpool.c-pp.js
@@ -501,22 +501,10 @@ globalThis.sqlite3ApiBootstrap.initializers.push(function(sqlite3){
        currently-opened client-specified filenames. */
     getFileNames(){
       const rc = [];
-      const iter = this.#mapFilenameToSAH.keys();
-      for(const n of iter) rc.push(n);
+      for(const n of this.#mapFilenameToSAH.keys()) rc.push(n);
       return rc;
     }
 
-//    #createFileObject(sah,clientName,opaqueName){
-//      const f = Object.assign(Object.create(null),{
-//        clientName, opaqueName
-//      });
-//      this.#mapSAHToMeta.set(sah, f);
-//      return f;
-//    }
-//    #unmapFileObject(sah){
-//      this.#mapSAHToMeta.delete(sah);
-//    }
-
     /**
        Adds n files to the pool's capacity. This change is
        persistent across settings. Returns a Promise which resolves
@@ -557,8 +545,9 @@ globalThis.sqlite3ApiBootstrap.initializers.push(function(sqlite3){
     }
 
     /**
-       Releases all currently-opened SAHs. The only legal
-       operation after this is acquireAccessHandles().
+       Releases all currently-opened SAHs. The only legal operation
+       after this is acquireAccessHandles() or (if this is called from
+       pauseVfs()) either of isPaused() or unpauseVfs().
     */
     releaseAccessHandles(){
       for(const ah of this.#mapSAHToName.keys()) ah.close();
@@ -568,17 +557,21 @@ globalThis.sqlite3ApiBootstrap.initializers.push(function(sqlite3){
     }
 
     /**
-       Opens all files under this.vfsDir/this.#dhOpaque and acquires
-       a SAH for each. returns a Promise which resolves to no value
-       but completes once all SAHs are acquired. If acquiring an SAH
-       throws, SAHPool.$error will contain the corresponding
-       exception.
+       Opens all files under this.vfsDir/this.#dhOpaque and acquires a
+       SAH for each. Returns a Promise which resolves to no value but
+       completes once all SAHs are acquired. If acquiring an SAH
+       throws, this.$error will contain the corresponding Error
+       object.
+
+       If it throws, it releases any SAHs which it may have
+       acquired before the exception was thrown, leaving the VFS in a
+       well-defined but unusable state.
 
        If clearFiles is true, the client-stored state of each file is
        cleared when its handle is acquired, including its name, flags,
        and any data stored after the metadata block.
     */
-    async acquireAccessHandles(clearFiles){
+    async acquireAccessHandles(clearFiles=false){
       const files = [];
       for await (const [name,h] of this.#dhOpaque){
         if('file'===h.kind){
@@ -832,12 +825,18 @@ globalThis.sqlite3ApiBootstrap.initializers.push(function(sqlite3){
        Removes this object's sqlite3_vfs registration and shuts down
        this object, releasing all handles, mappings, and whatnot,
        including deleting its data directory. There is currently no
-       way to "revive" the object and reaquire its resources.
+       way to "revive" the object and reaquire its
+       resources. Similarly, there is no recovery strategy if removal
+       of any given SAH fails, so such errors are ignored by this
+       function.
 
        This function is intended primarily for testing.
 
        Resolves to true if it did its job, false if the
        VFS has already been shut down.
+
+       @see pauseVfs()
+       @see unpauseVfs()
     */
     async removeVfs(){
       if(!this.#cVfs.pointer || !this.#dhOpaque) return false;
@@ -853,13 +852,77 @@ globalThis.sqlite3ApiBootstrap.initializers.push(function(sqlite3){
         );
         this.#dhVfsRoot = this.#dhVfsParent = undefined;
       }catch(e){
-        sqlite3.config.error(this.vfsName,"removeVfs() failed:",e);
+        sqlite3.config.error(this.vfsName,"removeVfs() failed with no recovery strategy:",e);
         /*otherwise ignored - there is no recovery strategy*/
       }
       return true;
     }
 
 
+    /**
+       "Pauses" this VFS by unregistering it from SQLite and
+       relinquishing all open SAHs, leaving the associated files
+       intact. If this object is already paused, this is a
+       no-op. Returns this object.
+
+       This function throws if SQLite has any opened file handles
+       hosted by this VFS, as the alternative would be to invoke
+       Undefined Behavior by closing file handles out from under the
+       library. Similarly, automatically closing any database handles
+       opened by this VFS would invoke Undefined Behavior in
+       downstream code which is holding those pointers.
+
+       If this function throws due to open file handles then it has
+       no side effects. If the OPFS API throws while closing handles
+       then the VFS is left in an undefined state.
+
+       @see isPaused()
+       @see unpauseVfs()
+    */
+    pauseVfs(){
+      if(this.#mapS3FileToOFile_.size>0){
+        sqlite3.SQLite3Error.toss(
+          capi.SQLITE_MISUSE, "Cannot pause VFS",
+          this.vfsName,"because it has opened files."
+        );
+      }
+      if(this.#mapSAHToName.size>0){
+        capi.sqlite3_vfs_unregister(this.vfsName);
+        this.releaseAccessHandles();
+      }
+      return this;
+    }
+
+    /**
+       Returns true if this pool is currently paused else false.
+
+       @see pauseVfs()
+       @see unpauseVfs()
+    */
+    isPaused(){
+      return 0===this.#mapSAHToName.size;
+    }
+
+    /**
+       "Unpauses" this VFS, reacquiring all SAH's and (if successful)
+       re-registering it with SQLite. This is a no-op if the VFS is
+       not currently paused.
+
+       The returned Promise resolves to this object. See
+       acquireAccessHandles() for how it behaves if it throws due to
+       SAH acquisition failure.
+
+       @see isPaused()
+       @see pauseVfs()
+    */
+    async unpauseVfs(){
+      if(0===this.#mapSAHToName.size){
+        return this.acquireAccessHandles(false).
+          then(()=>capi.sqlite3_vfs_register(this.#cVfs, 0),this);
+      }
+      return this;
+    }
+
     //! Documented elsewhere in this file.
     exportFile(name){
       const sah = this.#mapFilenameToSAH.get(name) || toss("File not found:",name);
@@ -984,6 +1047,10 @@ globalThis.sqlite3ApiBootstrap.initializers.push(function(sqlite3){
 
     async removeVfs(){ return this.#p.removeVfs() }
 
+    pauseVfs(){ this.#p.pauseVfs(); return this; }
+    async unpauseVfs(){ return this.#p.unpauseVfs().then(()=>this); }
+    isPaused(){ return this.#p.isPaused() }
+
   }/* class OpfsSAHPoolUtil */;
 
   /**
@@ -1217,6 +1284,41 @@ globalThis.sqlite3ApiBootstrap.initializers.push(function(sqlite3){
      Clears all client-defined state of all SAHs and makes all of them
      available for re-use by the pool. Results are undefined if any such
      handles are currently in use, e.g. by an sqlite3 db.
+
+     APIs specific to the "pause" capability (added in version 3.49):
+
+     Summary: "pausing" the VFS disassociates it from SQLite and
+     relinquishes its SAHs so that they may be opened by another
+     instance of this VFS (running in a separate tab/page or Worker).
+     "Unpausing" it takes back control, if able.
+
+     - pauseVfs()
+
+     "Pauses" this VFS by unregistering it from SQLite and
+     relinquishing all open SAHs, leaving the associated files intact.
+     This enables pages/tabs to coordinate semi-concurrent usage of
+     this VFS.  If this object is already paused, this is a
+     no-op. Returns this object. Throws if SQLite has any opened file
+     handles hosted by this VFS. If this function throws due to open
+     file handles then it has no side effects. If the OPFS API throws
+     while closing handles then the VFS is left in an undefined state.
+
+     - isPaused()
+
+     Returns true if this VFS is paused, else false.
+
+     - [async] unpauseVfs()
+
+     Restores the VFS to an active state after having called
+     pauseVfs() on it.  This is a no-op if the VFS is not paused. The
+     returned Promise resolves to this object on success. A rejected
+     Promise means there was a problem reacquiring the SAH handles
+     (possibly because they're in use by another instance or have
+     since been removed). Generically speaking, there is no recovery
+     strategy for that type of error, but if the problem is simply
+     that the OPFS files are locked, then a later attempt to unpause
+     it, made after the concurrent instance releases the SAHs, may
+     recover from the situation.
   */
   sqlite3.installOpfsSAHPoolVfs = async function(options=Object.create(null)){
     options = Object.assign(Object.create(null), optionDefaults, (options||{}));
diff --git a/ext/wasm/api/sqlite3-worker1-promiser.c-pp.js b/ext/wasm/api/sqlite3-worker1-promiser.c-pp.js
index 55e497ead..c043fd148 100644
--- a/ext/wasm/api/sqlite3-worker1-promiser.c-pp.js
+++ b/ext/wasm/api/sqlite3-worker1-promiser.c-pp.js
@@ -335,8 +335,8 @@ sqlite3Worker1Promiser.v2 = function(config){
 /**
   When built as a module, we export sqlite3Worker1Promiser.v2()
   instead of sqlite3Worker1Promise() because (A) its interface is more
-  conventional for ESM usage and (B) the ESM option export option for
-  this API did not exist until v2 was created, so there's no backwards
+  conventional for ESM usage and (B) the ESM export option for this
+  API did not exist until v2 was created, so there's no backwards
   incompatibility.
 */
 export default sqlite3Worker1Promiser.v2;
diff --git a/ext/wasm/index.html b/ext/wasm/index.html
index a3d41f1a9..fb8a58560 100644
--- a/ext/wasm/index.html
+++ b/ext/wasm/index.html
@@ -119,6 +119,10 @@
             <li><a href='tests/opfs/concurrency/index.html'>OPFS concurrency</a>
               tests using multiple workers.
             </li>
+            <li><a href='tests/opfs/sahpool/index.html'>OPFS SAHPool cooperative semi-concurrency</a>
+              demonstrates usage of the OPFS SAHPool VFS's "pause" feature to coordinate
+              access to a database.
+            </li>
           </ul>
         </li>
         <li><strong>WASMFS</strong>-specific tests which require that
diff --git a/ext/wasm/tester1.c-pp.js b/ext/wasm/tester1.c-pp.js
index 28d61de07..8638845a7 100644
--- a/ext/wasm/tester1.c-pp.js
+++ b/ext/wasm/tester1.c-pp.js
@@ -3189,8 +3189,25 @@ globalThis.sqlite3InitModule = sqlite3InitModule;
         db.close();
         T.assert(1 === u1.getFileCount());
         db = new u2.OpfsSAHPoolDb(dbName);
-        T.assert(1 === u1.getFileCount());
+        T.assert(1 === u1.getFileCount())
+          .mustThrowMatching(
+            ()=>u1.pauseVfs(),
+            (err)=>{
+              return capi.SQLITE_MISUSE===err.resultCode
+                && /^SQLITE_MISUSE: Cannot pause VFS /.test(err.message);
+            },
+            "Cannot pause VFS with opened db."
+          );
         db.close();
+        T.assert( u2===u2.pauseVfs() )
+          .assert( u2.isPaused() )
+          .assert( 0===capi.sqlite3_vfs_find(u2.vfsName) )
+          .mustThrowMatching(()=>new u2.OpfsSAHPoolDb(dbName),
+                             /.+no such vfs: .+/,
+                             "VFS is not available")
+          .assert( u2===await u2.unpauseVfs() )
+          .assert( u2===await u1.unpauseVfs(), "unpause is a no-op if the VFS is not paused" )
+          .assert( 0!==capi.sqlite3_vfs_find(u2.vfsName) );
         const fileNames = u1.getFileNames();
         T.assert(1 === fileNames.length)
           .assert(dbName === fileNames[0])
diff --git a/ext/wasm/tests/opfs/sahpool/index.html b/ext/wasm/tests/opfs/sahpool/index.html
new file mode 100644
index 000000000..f3d07f456
--- /dev/null
+++ b/ext/wasm/tests/opfs/sahpool/index.html
@@ -0,0 +1,31 @@
+<!doctype html>
+<html lang="en-us">
+  <head>
+    <meta charset="utf-8">
+    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
+    <link rel="shortcut icon" href="data:image/x-icon;," type="image/x-icon">
+    <link rel="stylesheet" href="../../../common/emscripten.css"/>
+    <link rel="stylesheet" href="../../../common/testing.css"/>
+    <title>sqlite3 tester: OpfsSAHPool Pausing</title>
+    <style></style>
+  </head>
+  <body><h1 id='color-target'></h1>
+
+    <p>
+      This page provides a <em>very basic</em> demonstration of
+      "pausing" and "unpausing" the OPFS SAHPool VFS such that
+      multiple pages or workers can use it by coordinating which
+      handler may have it open at any given time.
+    </p>
+    <div class='input-wrapper'>
+      <input type='checkbox' id='cb-log-reverse'>
+      <label for='cb-log-reverse'>Reverse log order?</label>
+    </div>
+    <div id='test-output'></div>
+    <script>(function(){
+      document.querySelector('h1').innerHTML =
+        document.querySelector('title').innerHTML;
+    })();</script>
+    <script src="sahpool-pausing.js"></script>
+  </body>
+</html>
diff --git a/ext/wasm/tests/opfs/sahpool/sahpool-pausing.js b/ext/wasm/tests/opfs/sahpool/sahpool-pausing.js
new file mode 100644
index 000000000..1aa98d3cb
--- /dev/null
+++ b/ext/wasm/tests/opfs/sahpool/sahpool-pausing.js
@@ -0,0 +1,183 @@
+/*
+  2025-01-31
+
+  The author disclaims copyright to this source code.  In place of a
+  legal notice, here is a blessing:
+
+  *   May you do good and not evil.
+  *   May you find forgiveness for yourself and forgive others.
+  *   May you share freely, never taking more than you give.
+
+  ***********************************************************************
+
+  These tests are specific to the opfs-sahpool VFS and are limited to
+  demonstrating its pause/unpause capabilities.
+
+  Most of this file is infrastructure for displaying results to the
+  user. Search for runTests() to find where the work actually starts.
+*/
+'use strict';
+(function(){
+  let logClass;
+
+  const mapToString = (v)=>{
+    switch(typeof v){
+      case 'number': case 'string': case 'boolean':
+      case 'undefined': case 'bigint':
+        return ''+v;
+      default: break;
+    }
+    if(null===v) return 'null';
+    if(v instanceof Error){
+      v = {
+        message: v.message,
+        stack: v.stack,
+        errorClass: v.name
+      };
+    }
+    return JSON.stringify(v,undefined,2);
+  };
+  const normalizeArgs = (args)=>args.map(mapToString);
+  const logTarget = document.querySelector('#test-output');
+  logClass = function(cssClass,...args){
+    const ln = document.createElement('div');
+    if(cssClass){
+      for(const c of (Array.isArray(cssClass) ? cssClass : [cssClass])){
+        ln.classList.add(c);
+      }
+    }
+    ln.append(document.createTextNode(normalizeArgs(args).join(' ')));
+    logTarget.append(ln);
+  };
+  const cbReverse = document.querySelector('#cb-log-reverse');
+  //cbReverse.setAttribute('checked','checked');
+  const cbReverseKey = 'tester1:cb-log-reverse';
+  const cbReverseIt = ()=>{
+    logTarget.classList[cbReverse.checked ? 'add' : 'remove']('reverse');
+    //localStorage.setItem(cbReverseKey, cbReverse.checked ? 1 : 0);
+  };
+  cbReverse.addEventListener('change', cbReverseIt, true);
+  /*if(localStorage.getItem(cbReverseKey)){
+    cbReverse.checked = !!(+localStorage.getItem(cbReverseKey));
+    }*/
+  cbReverseIt();
+
+  const log = (...args)=>{
+    //console.log(...args);
+    logClass('',...args);
+  }
+  const warn = (...args)=>{
+    console.warn(...args);
+    logClass('warning',...args);
+  }
+  const error = (...args)=>{
+    console.error(...args);
+    logClass('error',...args);
+  };
+
+  const toss = (...args)=>{
+    error(...args);
+    throw new Error(args.join(' '));
+  };
+
+  const endOfWork = (passed=true)=>{
+    const eH = document.querySelector('#color-target');
+    const eT = document.querySelector('title');
+    if(passed){
+      log("End of work chain. If you made it this far, you win.");
+      eH.innerText = 'PASS: '+eH.innerText;
+      eH.classList.add('tests-pass');
+      eT.innerText = 'PASS: '+eT.innerText;
+    }else{
+      eH.innerText = 'FAIL: '+eH.innerText;
+      eH.classList.add('tests-fail');
+      eT.innerText = 'FAIL: '+eT.innerText;
+    }
+  };
+
+  const nextHandlerQueue = [];
+
+  const nextHandler = function(workerId,...msg){
+    log(workerId,...msg);
+    (nextHandlerQueue.shift())();
+  };
+
+  const postThen = function(W, msgType, callback){
+    nextHandlerQueue.push(callback);
+    W.postMessage({type:msgType});
+  };
+
+  /**
+     Run a series of operations on an sahpool db spanning two workers.
+     This would arguably be more legible with Promises, but creating a
+     Promise-based communication channel for this purpose is left as
+     an exercise for the reader. An example of such a proxy can be
+     found in the SQLite source tree:
+
+     https://sqlite.org/src/file/ext/wasm/api/sqlite3-worker1-promiser.c-pp.js
+  */
+  const runPyramidOfDoom = function(W1, W2){
+    postThen(W1, 'vfs-acquire', function(){
+      postThen(W1, 'db-init', function(){
+        postThen(W1, 'db-query', function(){
+          postThen(W1, 'vfs-pause', function(){
+            postThen(W2, 'vfs-acquire', function(){
+              postThen(W2, 'db-query', function(){
+                postThen(W2, 'vfs-remove', endOfWork);
+              });
+            });
+          });
+        });
+      });
+    });
+  };
+
+  const runTests = function(){
+    log("Running opfs-sahpool pausing tests...");
+    const wjs = 'sahpool-worker.js?sqlite3.dir=../../../jswasm';
+    const W1 = new Worker(wjs+'&workerId=w1'),
+          W2 = new Worker(wjs+'&workerId=w2');
+    W1.workerId = 'w1';
+    W2.workerId = 'w2';
+    let initCount = 0;
+    const onmessage = function({data}){
+      //log("onmessage:",data);
+      switch(data.type){
+        case 'vfs-acquired':
+          nextHandler(data.workerId, "VFS acquired");
+          break;
+        case 'vfs-paused':
+          nextHandler(data.workerId, "VFS paused");
+          break;
+        case 'vfs-unpaused':
+          nextHandler(data.workerId, 'VFS unpaused');
+          break;
+        case 'vfs-removed':
+          nextHandler(data.workerId, 'VFS removed');
+          break;
+        case 'db-inited':
+          nextHandler(data.workerId, 'db initialized');
+          break;
+        case 'query-result':
+          nextHandler(data.workerId, 'query result', data.payload);
+          break;
+        case 'log':
+          log(data.workerId, ':', ...data.payload);
+          break;
+        case 'error':
+          error(data.workerId, ':', ...data.payload);
+          endOfWork(false);
+          break;
+        case 'initialized':
+          log(data.workerId, ': Worker initialized',...data.payload);
+          if( 2===++initCount ){
+            runPyramidOfDoom(W1, W2);
+          }
+          break;
+      }
+    };
+    W1.onmessage = W2.onmessage = onmessage;
+  };
+
+  runTests();
+})();
diff --git a/ext/wasm/tests/opfs/sahpool/sahpool-worker.js b/ext/wasm/tests/opfs/sahpool/sahpool-worker.js
new file mode 100644
index 000000000..592f15955
--- /dev/null
+++ b/ext/wasm/tests/opfs/sahpool/sahpool-worker.js
@@ -0,0 +1,104 @@
+/*
+  2025-01-31
+
+  The author disclaims copyright to this source code.  In place of a
+  legal notice, here is a blessing:
+
+  *   May you do good and not evil.
+  *   May you find forgiveness for yourself and forgive others.
+  *   May you share freely, never taking more than you give.
+
+  ***********************************************************************
+
+  This file is part of sahpool-pausing.js's demonstration of the
+  pause/unpause feature of the opfs-sahpool VFS.
+*/
+const searchParams = new URL(self.location.href).searchParams;
+const workerId = searchParams.get('workerId');
+const wPost = (type,...args)=>postMessage({type, workerId, payload:args});
+const log = (...args)=>wPost('log',...args);
+let capi, wasm, S, poolUtil;
+
+const sahPoolConfig = {
+  name: 'opfs-sahpool-pausable',
+  clearOnInit: false,
+  initialCapacity: 3
+};
+
+importScripts(searchParams.get('sqlite3.dir') + '/sqlite3.js');
+
+const sqlExec = function(sql){
+  const db = new poolUtil.OpfsSAHPoolDb('/my.db');
+  try{
+    return db.exec(sql);
+  }finally{
+    db.close();
+  }
+};
+
+const clog = console.log.bind(console);
+globalThis.onmessage = function({data}){
+  clog(workerId+": onmessage:",data);
+  switch(data.type){
+    case 'vfs-acquire':
+      if( poolUtil ){
+        poolUtil.unpauseVfs().then(()=>wPost('vfs-unpaused'));
+      }else{
+        S.installOpfsSAHPoolVfs(sahPoolConfig).then(pu=>{
+          poolUtil = pu;
+          wPost('vfs-acquired');
+        });
+      }
+      break;
+    case 'db-init':
+      try{
+        sqlExec([
+          "DROP TABLE IF EXISTS mytable;",
+          "CREATE TABLE mytable(a);",
+          "INSERT INTO mytable(a) VALUES(11),(22),(33)"
+        ]);
+        wPost('db-inited');
+      }catch(e){
+        wPost('error',e.message);
+      }
+      break;
+    case 'db-query': {
+      const rc = sqlExec({
+        sql: 'select * from mytable order by a',
+        rowMode: 'array',
+        returnValue: 'resultRows'
+      });
+      wPost('query-result',rc);
+      break;
+    }
+    case 'vfs-remove':
+      poolUtil.removeVfs().then(()=>wPost('vfs-removed'));
+      break;
+    case 'vfs-pause':
+      poolUtil.pauseVfs();
+      wPost('vfs-paused');
+      break;
+  }
+};
+
+const hasOpfs = ()=>{
+  return globalThis.FileSystemHandle
+    && globalThis.FileSystemDirectoryHandle
+    && globalThis.FileSystemFileHandle
+    && globalThis.FileSystemFileHandle.prototype.createSyncAccessHandle
+    && navigator?.storage?.getDirectory;
+};
+if( !hasOpfs() ){
+  wPost('error',"OPFS not detected");
+}else{
+  globalThis.sqlite3InitModule().then(async function(sqlite3){
+    S = sqlite3;
+    capi = S.capi;
+    wasm = S.wasm;
+    log("sqlite3 version:",capi.sqlite3_libversion(),
+        capi.sqlite3_sourceid());
+    //return sqlite3.installOpfsSAHPoolVfs(sahPoolConfig).then(pu=>poolUtil=pu);
+  }).then(()=>{
+    wPost('initialized');
+  });
+}