SQLite

Check-in [19cd8e2b05]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:OPFS VFSes: remove the on-open() pragma calls, as those (A) already reflected the build-time default settings and (B) they made it illegal to run locking_mode=exclusive, which is a requirement for WAL mode without shared memory. Modify part of the test suite to demonstrate that the SAHPool VFS can run in WAL mode so long as locking_mode=exclusive is used.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 19cd8e2b056d7842ee39afb7160c901c9dc55a5bac8049cb0b5246210f6b920d
User & Date: stephan 2024-07-22 20:58:51.797
Context
2024-07-22
21:21
Move a block of JS code which was inadvertently (and harmlessly) moved in the previous checkin. Clarify the semantics of an internal-use-only API. Add another WAL-related JS test. (check-in: 6cd9f55a97 user: stephan tags: trunk)
20:58
OPFS VFSes: remove the on-open() pragma calls, as those (A) already reflected the build-time default settings and (B) they made it illegal to run locking_mode=exclusive, which is a requirement for WAL mode without shared memory. Modify part of the test suite to demonstrate that the SAHPool VFS can run in WAL mode so long as locking_mode=exclusive is used. (check-in: 19cd8e2b05 user: stephan tags: trunk)
19:52
wasm build: resolve a circular dep and do some minor tidying up. (check-in: 9df3f1f24c user: stephan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/wasm/api/sqlite3-api-oo1.c-pp.js.
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
          if(capi.SQLITE_TRACE_STMT===t){
            // x == SQL, p == sqlite3_stmt*
            console.log("SQL TRACE #"+(++this.counter)+' via sqlite3@'+c+':',
                        wasm.cstrToJs(x));
          }
        }.bind({counter: 0}));

  /**
     A map of sqlite3_vfs pointers to SQL code or a callback function
     to run when the DB constructor opens a database with the given
     VFS. In the latter case, the call signature is (theDbObject,sqlite3Namespace)
     and the callback is expected to throw on error.
  */
  const __vfsPostOpenSql = Object.create(null);

//#if enable-see
  /**
     Converts ArrayBuffer or Uint8Array ba into a string of hex
     digits.
  */
  const byteArrayToHex = function(ba){
    if( ba instanceof ArrayBuffer ){







<
<
<
<
<
<
<
<







75
76
77
78
79
80
81








82
83
84
85
86
87
88
          if(capi.SQLITE_TRACE_STMT===t){
            // x == SQL, p == sqlite3_stmt*
            console.log("SQL TRACE #"+(++this.counter)+' via sqlite3@'+c+':',
                        wasm.cstrToJs(x));
          }
        }.bind({counter: 0}));









//#if enable-see
  /**
     Converts ArrayBuffer or Uint8Array ba into a string of hex
     digits.
  */
  const byteArrayToHex = function(ba){
    if( ba instanceof ArrayBuffer ){
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
    try{
//#if enable-see
      dbCtorApplySEEKey(this,opt);
//#endif
      // Check for per-VFS post-open SQL/callback...
      const pVfs = capi.sqlite3_js_db_vfs(pDb)
            || toss3("Internal error: cannot get VFS for new db handle.");
      const postInitSql = __vfsPostOpenSql[pVfs];
      if(postInitSql){
        /**
           Reminder: if this db is encrypted and the client did _not_ pass
           in the key, any init code will fail, causing the ctor to throw.
           We don't actually know whether the db is encrypted, so we cannot
           sensibly apply any heuristics which skip the init code only for
           encrypted databases for which no key has yet been supplied.







|







267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
    try{
//#if enable-see
      dbCtorApplySEEKey(this,opt);
//#endif
      // Check for per-VFS post-open SQL/callback...
      const pVfs = capi.sqlite3_js_db_vfs(pDb)
            || toss3("Internal error: cannot get VFS for new db handle.");
      const postInitSql = __vfsPostOpenCallback[pVfs];
      if(postInitSql){
        /**
           Reminder: if this db is encrypted and the client did _not_ pass
           in the key, any init code will fail, causing the ctor to throw.
           We don't actually know whether the db is encrypted, so we cannot
           sensibly apply any heuristics which skip the init code only for
           encrypted databases for which no key has yet been supplied.
298
299
300
301
302
303
304

305







306

307
308
309
310
311
312
313
314







315
316




317
318
319
320
321
322
323
324
      }
    }catch(e){
      this.close();
      throw e;
    }
  };


  /**







     Sets SQL which should be exec()'d on a DB instance after it is

     opened with the given VFS pointer. The SQL may be any type
     supported by the "string:flexible" function argument conversion.
     Alternately, the 2nd argument may be a function, in which case it
     is called with (theOo1DbObject,sqlite3Namespace) at the end of
     the DB() constructor. The function must throw on error, in which
     case the db is closed and the exception is propagated.  This
     function is intended only for use by DB subclasses or sqlite3_vfs
     implementations.







  */
  dbCtorHelper.setVfsPostOpenSql = function(pVfs, sql){




    __vfsPostOpenSql[pVfs] = sql;
  };

  /**
     A helper for DB constructors. It accepts either a single
     config-style object or up to 3 arguments (filename, dbOpenFlags,
     dbVfsName). It returns a new object containing:








>

>
>
>
>
>
>
>
|
>
|
|
|
|
|
|
|

>
>
>
>
>
>
>

|
>
>
>
>
|







290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
      }
    }catch(e){
      this.close();
      throw e;
    }
  };


  /**
     A map of sqlite3_vfs pointers to SQL code or a callback function
     to run when the DB constructor opens a database with the given
     VFS. In the latter case, the call signature is
     (theDbObject,sqlite3Namespace) and the callback is expected to
     throw on error.
  */
  const __vfsPostOpenCallback = Object.create(null);

  /**
     Sets a callback which should be called after a db is opened with
     the given sqlite3_vfs pointer. The 2nd argument must be a
     function, which gets called with
     (theOo1DbObject,sqlite3Namespace) at the end of the DB()
     constructor. The function must throw on error, in which case the
     db is closed and the exception is propagated.  This function is
     intended only for use by DB subclasses or sqlite3_vfs
     implementations.

     Prior to 2024-07-22, it was legal to pass SQL code as the second
     argument, but that can interfere with a client's ability to run
     pragmas which must be run before anything else, namely (pragma
     locking_mode=exclusive) for use with WAL mode.  That capability
     had only ever been used as an internal detail of the two OPFS
     VFSes, and they no longer use it that way.
  */
  dbCtorHelper.setVfsPostOpenCallback = function(pVfs, callback){
    if( !(callback instanceof Function)){
      toss3("dbCtorHelper.setVfsPostOpenCallback() should not be used with "+
            "a non-function argument.",arguments);
    }
    __vfsPostOpenCallback[pVfs] = callback;
  };

  /**
     A helper for DB constructors. It accepts either a single
     config-style object or up to 3 arguments (filename, dbOpenFlags,
     dbVfsName). It returns a new object containing:

Changes to ext/wasm/api/sqlite3-vfs-opfs-sahpool.c-pp.js.
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
     SQLITE_OPEN_xyz types not listed here may be inadvertently
     left in OPFS but are treated as transient by this VFS and
     they will be cleaned up during VFS init. */
  const PERSISTENT_FILE_TYPES =
        capi.SQLITE_OPEN_MAIN_DB |
        capi.SQLITE_OPEN_MAIN_JOURNAL |
        capi.SQLITE_OPEN_SUPER_JOURNAL |
        capi.SQLITE_OPEN_WAL /* noting that WAL support is
                                unavailable in the WASM build.*/;

  /** Subdirectory of the VFS's space where "opaque" (randomly-named)
      files are stored. Changing this effectively invalidates the data
      stored under older names (orphaning it), so don't do that. */
  const OPAQUE_DIR_NAME = ".opaque";

  /**







|
<







74
75
76
77
78
79
80
81

82
83
84
85
86
87
88
     SQLITE_OPEN_xyz types not listed here may be inadvertently
     left in OPFS but are treated as transient by this VFS and
     they will be cleaned up during VFS init. */
  const PERSISTENT_FILE_TYPES =
        capi.SQLITE_OPEN_MAIN_DB |
        capi.SQLITE_OPEN_MAIN_JOURNAL |
        capi.SQLITE_OPEN_SUPER_JOURNAL |
        capi.SQLITE_OPEN_WAL;


  /** Subdirectory of the VFS's space where "opaque" (randomly-named)
      files are stored. Changing this effectively invalidates the data
      stored under older names (orphaning it), so don't do that. */
  const OPAQUE_DIR_NAME = ".opaque";

  /**
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
          const OpfsSAHPoolDb = function(...args){
            const opt = oo1.DB.dbCtorHelper.normalizeArgs(...args);
            opt.vfs = theVfs.$zName;
            oo1.DB.dbCtorHelper.call(this, opt);
          };
          OpfsSAHPoolDb.prototype = Object.create(oo1.DB.prototype);
          poolUtil.OpfsSAHPoolDb = OpfsSAHPoolDb;
          oo1.DB.dbCtorHelper.setVfsPostOpenSql(
            theVfs.pointer,
            function(oo1Db, sqlite3){
              sqlite3.capi.sqlite3_exec(oo1Db, [
                /* See notes in sqlite3-vfs-opfs.js */
                "pragma journal_mode=DELETE;",
                "pragma cache_size=-16384;"
              ], 0, 0, 0);
            }
          );
        }/*extend sqlite3.oo1*/
        thePool.log("VFS initialized.");
        return poolUtil;
      }).catch(async (e)=>{
        await thePool.removeVfs().catch(()=>{});
        throw e;
      });







<
<
<
<
<
<
<
<
<
<







1275
1276
1277
1278
1279
1280
1281










1282
1283
1284
1285
1286
1287
1288
          const OpfsSAHPoolDb = function(...args){
            const opt = oo1.DB.dbCtorHelper.normalizeArgs(...args);
            opt.vfs = theVfs.$zName;
            oo1.DB.dbCtorHelper.call(this, opt);
          };
          OpfsSAHPoolDb.prototype = Object.create(oo1.DB.prototype);
          poolUtil.OpfsSAHPoolDb = OpfsSAHPoolDb;










        }/*extend sqlite3.oo1*/
        thePool.log("VFS initialized.");
        return poolUtil;
      }).catch(async (e)=>{
        await thePool.removeVfs().catch(()=>{});
        throw e;
      });
Changes to ext/wasm/api/sqlite3-vfs-opfs.c-pp.js.
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
        const opt = sqlite3.oo1.DB.dbCtorHelper.normalizeArgs(...args);
        opt.vfs = opfsVfs.$zName;
        sqlite3.oo1.DB.dbCtorHelper.call(this, opt);
      };
      OpfsDb.prototype = Object.create(sqlite3.oo1.DB.prototype);
      sqlite3.oo1.OpfsDb = OpfsDb;
      OpfsDb.importDb = opfsUtil.importDb;
      sqlite3.oo1.DB.dbCtorHelper.setVfsPostOpenSql(
        opfsVfs.pointer,
        function(oo1Db, sqlite3){
          /* Set a relatively high default busy-timeout handler to
             help OPFS dbs deal with multi-tab/multi-worker
             contention. */
          sqlite3.capi.sqlite3_busy_timeout(oo1Db, 10000);
          sqlite3.capi.sqlite3_exec(oo1Db, [
            /* As of July 2023, the PERSIST journal mode on OPFS is
               somewhat slower than DELETE or TRUNCATE (it was faster
               before Chrome version 108 or 109). TRUNCATE and DELETE
               have very similar performance on OPFS.

               Roy Hashimoto notes that TRUNCATE and PERSIST modes may
               decrease OPFS concurrency because multiple connections
               can open the journal file in those modes:

               https://github.com/rhashimoto/wa-sqlite/issues/68

               Given that, and the fact that testing has not revealed
               any appreciable difference between performance of
               TRUNCATE and DELETE modes on OPFS, we currently (as of
               2023-07-13) default to DELETE mode.
            */
            "pragma journal_mode=DELETE;",
            /*
              This vfs benefits hugely from cache on moderate/large
              speedtest1 --size 50 and --size 100 workloads. We
              currently rely on setting a non-default cache size when
              building sqlite3.wasm. If that policy changes, the cache
              can be set here.
            */
            "pragma cache_size=-16384;"
          ], 0, 0, 0);
        }
      );
    }/*extend sqlite3.oo1*/

    const sanityCheck = function(){
      const scope = wasm.scopedAllocPush();
      const sq3File = new sqlite3_file();







|






<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297



























1298
1299
1300
1301
1302
1303
1304
        const opt = sqlite3.oo1.DB.dbCtorHelper.normalizeArgs(...args);
        opt.vfs = opfsVfs.$zName;
        sqlite3.oo1.DB.dbCtorHelper.call(this, opt);
      };
      OpfsDb.prototype = Object.create(sqlite3.oo1.DB.prototype);
      sqlite3.oo1.OpfsDb = OpfsDb;
      OpfsDb.importDb = opfsUtil.importDb;
      sqlite3.oo1.DB.dbCtorHelper.setVfsPostOpenCallback(
        opfsVfs.pointer,
        function(oo1Db, sqlite3){
          /* Set a relatively high default busy-timeout handler to
             help OPFS dbs deal with multi-tab/multi-worker
             contention. */
          sqlite3.capi.sqlite3_busy_timeout(oo1Db, 10000);



























        }
      );
    }/*extend sqlite3.oo1*/

    const sanityCheck = function(){
      const scope = wasm.scopedAllocPush();
      const sq3File = new sqlite3_file();
Changes to ext/wasm/tester1.c-pp.js.
3109
3110
3111
3112
3113
3114
3115













3116
3117
3118
3119
3120

3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139

3140
3141
3142
3143
3144
3145
3146

        T.assert(0 === u1.getFileCount());
        const dbName = '/foo.db';
        let db = new u1.OpfsSAHPoolDb(dbName);
        T.assert(db instanceof sqlite3.oo1.DB)
          .assert(1 === u1.getFileCount());
        db.exec([













          'create table t(a);',
          'insert into t(a) values(1),(2),(3)'
        ]);
        T.assert(1 === u1.getFileCount());
        T.assert(3 === db.selectValue('select count(*) from t'));

        db.close();
        T.assert(1 === u1.getFileCount());
        db = new u2.OpfsSAHPoolDb(dbName);
        T.assert(1 === u1.getFileCount());
        db.close();
        const fileNames = u1.getFileNames();
        T.assert(1 === fileNames.length)
          .assert(dbName === fileNames[0])
          .assert(1 === u1.getFileCount())

        if(1){ // test exportFile() and importDb()
          const dbytes = u1.exportFile(dbName);
          T.assert(dbytes.length >= 4096);
          const dbName2 = '/exported.db';
          let nWrote = u1.importDb(dbName2, dbytes);
          T.assert( 2 == u1.getFileCount() )
            .assert( dbytes.byteLength == nWrote );
          let db2 = new u1.OpfsSAHPoolDb(dbName2);
          T.assert(db2 instanceof sqlite3.oo1.DB)

            .assert(3 === db2.selectValue('select count(*) from t'));
          db2.close();
          T.assert(true === u1.unlink(dbName2))
            .assert(false === u1.unlink(dbName2))
            .assert(1 === u1.getFileCount())
            .assert(1 === u1.getFileNames().length);
          // Try again with a function as an input source:







>
>
>
>
>
>
>
>
>
>
>
>
>



|
|
>



















>







3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161

        T.assert(0 === u1.getFileCount());
        const dbName = '/foo.db';
        let db = new u1.OpfsSAHPoolDb(dbName);
        T.assert(db instanceof sqlite3.oo1.DB)
          .assert(1 === u1.getFileCount());
        db.exec([
          'pragma locking_mode=exclusive;',
          'pragma journal_mode=wal;'
          /* WAL mode only works in this VFS if locking_mode=exclusive
             is invoked prior to the first db access, as this build
             does not have the shared-memory APIs needed for WAL without
             exclusive-mode locking. See:

             https://sqlite.org/wal.html#use_of_wal_without_shared_memory

             Note that WAL mode here DOES NOT add any concurrency capabilities
             to this VFS, but it MAY provide slightly improved performance
             over the other journaling modes.
          */,
          'create table t(a);',
          'insert into t(a) values(1),(2),(3)'
        ]);
        T.assert(2 === u1.getFileCount() /* one is the journal file */)
          .assert(3 === db.selectValue('select count(*) from t'))
          .assert('wal'===db.selectValue('pragma journal_mode'));
        db.close();
        T.assert(1 === u1.getFileCount());
        db = new u2.OpfsSAHPoolDb(dbName);
        T.assert(1 === u1.getFileCount());
        db.close();
        const fileNames = u1.getFileNames();
        T.assert(1 === fileNames.length)
          .assert(dbName === fileNames[0])
          .assert(1 === u1.getFileCount())

        if(1){ // test exportFile() and importDb()
          const dbytes = u1.exportFile(dbName);
          T.assert(dbytes.length >= 4096);
          const dbName2 = '/exported.db';
          let nWrote = u1.importDb(dbName2, dbytes);
          T.assert( 2 == u1.getFileCount() )
            .assert( dbytes.byteLength == nWrote );
          let db2 = new u1.OpfsSAHPoolDb(dbName2);
          T.assert(db2 instanceof sqlite3.oo1.DB)
            //.assert('wal' == db2.selectValue("pragma journal_mode=WAL"))
            .assert(3 === db2.selectValue('select count(*) from t'));
          db2.close();
          T.assert(true === u1.unlink(dbName2))
            .assert(false === u1.unlink(dbName2))
            .assert(1 === u1.getFileCount())
            .assert(1 === u1.getFileNames().length);
          // Try again with a function as an input source: