1/* 2 2022-07-22 3 4 The author disclaims copyright to this source code. In place of a 5 legal notice, here is a blessing: 6 7 * May you do good and not evil. 8 * May you find forgiveness for yourself and forgive others. 9 * May you share freely, never taking more than you give. 10 11 *********************************************************************** 12 13 This file contains the so-called OO #1 API wrapper for the sqlite3 14 WASM build. It requires that sqlite3-api-glue.js has already run 15 and it installs its deliverable as self.sqlite3.oo1. 16*/ 17self.sqlite3ApiBootstrap.initializers.push(function(sqlite3){ 18 const toss = (...args)=>{throw new Error(args.join(' '))}; 19 const toss3 = (...args)=>{throw new sqlite3.SQLite3Error(...args)}; 20 21 const capi = sqlite3.capi, wasm = sqlite3.wasm, util = sqlite3.util; 22 /* What follows is colloquially known as "OO API #1". It is a 23 binding of the sqlite3 API which is designed to be run within 24 the same thread (main or worker) as the one in which the 25 sqlite3 WASM binding was initialized. This wrapper cannot use 26 the sqlite3 binding if, e.g., the wrapper is in the main thread 27 and the sqlite3 API is in a worker. */ 28 29 /** 30 In order to keep clients from manipulating, perhaps 31 inadvertently, the underlying pointer values of DB and Stmt 32 instances, we'll gate access to them via the `pointer` property 33 accessor and store their real values in this map. Keys = DB/Stmt 34 objects, values = pointer values. This also unifies how those are 35 accessed, for potential use downstream via custom 36 wasm.xWrap() function signatures which know how to extract 37 it. 38 */ 39 const __ptrMap = new WeakMap(); 40 /** 41 Map of DB instances to objects, each object being a map of Stmt 42 wasm pointers to Stmt objects. 43 */ 44 const __stmtMap = new WeakMap(); 45 46 /** If object opts has _its own_ property named p then that 47 property's value is returned, else dflt is returned. */ 48 const getOwnOption = (opts, p, dflt)=>{ 49 const d = Object.getOwnPropertyDescriptor(opts,p); 50 return d ? d.value : dflt; 51 }; 52 53 // Documented in DB.checkRc() 54 const checkSqlite3Rc = function(dbPtr, sqliteResultCode){ 55 if(sqliteResultCode){ 56 if(dbPtr instanceof DB) dbPtr = dbPtr.pointer; 57 toss3( 58 "sqlite result code",sqliteResultCode+":", 59 (dbPtr 60 ? capi.sqlite3_errmsg(dbPtr) 61 : capi.sqlite3_errstr(sqliteResultCode)) 62 ); 63 } 64 }; 65 66 /** 67 sqlite3_trace_v2() callback which gets installed by the DB ctor 68 if its open-flags contain "t". 69 */ 70 const __dbTraceToConsole = 71 wasm.installFunction('i(ippp)', function(t,c,p,x){ 72 if(capi.SQLITE_TRACE_STMT===t){ 73 // x == SQL, p == sqlite3_stmt* 74 console.log("SQL TRACE #"+(++this.counter), 75 wasm.cstringToJs(x)); 76 } 77 }.bind({counter: 0})); 78 79 /** 80 A map of sqlite3_vfs pointers to SQL code to run when the DB 81 constructor opens a database with the given VFS. 82 */ 83 const __vfsPostOpenSql = Object.create(null); 84 85 /** 86 A proxy for DB class constructors. It must be called with the 87 being-construct DB object as its "this". See the DB constructor 88 for the argument docs. This is split into a separate function 89 in order to enable simple creation of special-case DB constructors, 90 e.g. JsStorageDb and OpfsDb. 91 92 Expects to be passed a configuration object with the following 93 properties: 94 95 - `.filename`: the db filename. It may be a special name like ":memory:" 96 or "". 97 98 - `.flags`: as documented in the DB constructor. 99 100 - `.vfs`: as documented in the DB constructor. 101 102 It also accepts those as the first 3 arguments. 103 */ 104 const dbCtorHelper = function ctor(...args){ 105 if(!ctor._name2vfs){ 106 /** 107 Map special filenames which we handle here (instead of in C) 108 to some helpful metadata... 109 110 As of 2022-09-20, the C API supports the names :localStorage: 111 and :sessionStorage: for kvvfs. However, C code cannot 112 determine (without embedded JS code, e.g. via Emscripten's 113 EM_JS()) whether the kvvfs is legal in the current browser 114 context (namely the main UI thread). In order to help client 115 code fail early on, instead of it being delayed until they 116 try to read or write a kvvfs-backed db, we'll check for those 117 names here and throw if they're not legal in the current 118 context. 119 */ 120 ctor._name2vfs = Object.create(null); 121 const isWorkerThread = ('function'===typeof importScripts/*===running in worker thread*/) 122 ? (n)=>toss3("The VFS for",n,"is only available in the main window thread.") 123 : false; 124 ctor._name2vfs[':localStorage:'] = { 125 vfs: 'kvvfs', filename: isWorkerThread || (()=>'local') 126 }; 127 ctor._name2vfs[':sessionStorage:'] = { 128 vfs: 'kvvfs', filename: isWorkerThread || (()=>'session') 129 }; 130 } 131 const opt = ctor.normalizeArgs(...args); 132 let fn = opt.filename, vfsName = opt.vfs, flagsStr = opt.flags; 133 if(('string'!==typeof fn && 'number'!==typeof fn) 134 || 'string'!==typeof flagsStr 135 || (vfsName && ('string'!==typeof vfsName && 'number'!==typeof vfsName))){ 136 console.error("Invalid DB ctor args",opt,arguments); 137 toss3("Invalid arguments for DB constructor."); 138 } 139 let fnJs = ('number'===typeof fn) ? wasm.cstringToJs(fn) : fn; 140 const vfsCheck = ctor._name2vfs[fnJs]; 141 if(vfsCheck){ 142 vfsName = vfsCheck.vfs; 143 fn = fnJs = vfsCheck.filename(fnJs); 144 } 145 let pDb, oflags = 0; 146 if( flagsStr.indexOf('c')>=0 ){ 147 oflags |= capi.SQLITE_OPEN_CREATE | capi.SQLITE_OPEN_READWRITE; 148 } 149 if( flagsStr.indexOf('w')>=0 ) oflags |= capi.SQLITE_OPEN_READWRITE; 150 if( 0===oflags ) oflags |= capi.SQLITE_OPEN_READONLY; 151 oflags |= capi.SQLITE_OPEN_EXRESCODE; 152 const stack = wasm.pstack.pointer; 153 try { 154 const pPtr = wasm.pstack.allocPtr() /* output (sqlite3**) arg */; 155 let rc = capi.sqlite3_open_v2(fn, pPtr, oflags, vfsName || 0); 156 pDb = wasm.getPtrValue(pPtr); 157 checkSqlite3Rc(pDb, rc); 158 if(flagsStr.indexOf('t')>=0){ 159 capi.sqlite3_trace_v2(pDb, capi.SQLITE_TRACE_STMT, 160 __dbTraceToConsole, 0); 161 } 162 // Check for per-VFS post-open SQL... 163 const pVfs = capi.sqlite3_js_db_vfs(pDb); 164 //console.warn("Opened db",fn,"with vfs",vfsName,pVfs); 165 if(!pVfs) toss3("Internal error: cannot get VFS for new db handle."); 166 const postInitSql = __vfsPostOpenSql[pVfs]; 167 if(postInitSql){ 168 rc = capi.sqlite3_exec(pDb, postInitSql, 0, 0, 0); 169 checkSqlite3Rc(pDb, rc); 170 } 171 }catch( e ){ 172 if( pDb ) capi.sqlite3_close_v2(pDb); 173 throw e; 174 }finally{ 175 wasm.pstack.restore(stack); 176 } 177 this.filename = fnJs; 178 __ptrMap.set(this, pDb); 179 __stmtMap.set(this, Object.create(null)); 180 }; 181 182 /** 183 Sets SQL which should be exec()'d on a DB instance after it is 184 opened with the given VFS pointer. This is intended only for use 185 by DB subclasses or sqlite3_vfs implementations. 186 */ 187 dbCtorHelper.setVfsPostOpenSql = function(pVfs, sql){ 188 __vfsPostOpenSql[pVfs] = sql; 189 }; 190 191 /** 192 A helper for DB constructors. It accepts either a single 193 config-style object or up to 3 arguments (filename, dbOpenFlags, 194 dbVfsName). It returns a new object containing: 195 196 { filename: ..., flags: ..., vfs: ... } 197 198 If passed an object, any additional properties it has are copied 199 as-is into the new object. 200 */ 201 dbCtorHelper.normalizeArgs = function(filename=':memory:',flags = 'c',vfs = null){ 202 const arg = {}; 203 if(1===arguments.length && 'object'===typeof arguments[0]){ 204 const x = arguments[0]; 205 Object.keys(x).forEach((k)=>arg[k] = x[k]); 206 if(undefined===arg.flags) arg.flags = 'c'; 207 if(undefined===arg.vfs) arg.vfs = null; 208 if(undefined===arg.filename) arg.filename = ':memory:'; 209 }else{ 210 arg.filename = filename; 211 arg.flags = flags; 212 arg.vfs = vfs; 213 } 214 return arg; 215 }; 216 /** 217 The DB class provides a high-level OO wrapper around an sqlite3 218 db handle. 219 220 The given db filename must be resolvable using whatever 221 filesystem layer (virtual or otherwise) is set up for the default 222 sqlite3 VFS. 223 224 Note that the special sqlite3 db names ":memory:" and "" 225 (temporary db) have their normal special meanings here and need 226 not resolve to real filenames, but "" uses an on-storage 227 temporary database and requires that the VFS support that. 228 229 The second argument specifies the open/create mode for the 230 database. It must be string containing a sequence of letters (in 231 any order, but case sensitive) specifying the mode: 232 233 - "c": create if it does not exist, else fail if it does not 234 exist. Implies the "w" flag. 235 236 - "w": write. Implies "r": a db cannot be write-only. 237 238 - "r": read-only if neither "w" nor "c" are provided, else it 239 is ignored. 240 241 - "t": enable tracing of SQL executed on this database handle, 242 sending it to `console.log()`. To disable it later, call 243 `sqlite3.capi.sqlite3_trace_v2(thisDb.pointer, 0, 0, 0)`. 244 245 If "w" is not provided, the db is implicitly read-only, noting 246 that "rc" is meaningless 247 248 Any other letters are currently ignored. The default is 249 "c". These modes are ignored for the special ":memory:" and "" 250 names and _may_ be ignored altogether for certain VFSes. 251 252 The final argument is analogous to the final argument of 253 sqlite3_open_v2(): the name of an sqlite3 VFS. Pass a falsy value, 254 or none at all, to use the default. If passed a value, it must 255 be the string name of a VFS. 256 257 The constructor optionally (and preferably) takes its arguments 258 in the form of a single configuration object with the following 259 properties: 260 261 - `filename`: database file name 262 - `flags`: open-mode flags 263 - `vfs`: the VFS fname 264 265 The `filename` and `vfs` arguments may be either JS strings or 266 C-strings allocated via WASM. `flags` is required to be a JS 267 string (because it's specific to this API, which is specific 268 to JS). 269 270 For purposes of passing a DB instance to C-style sqlite3 271 functions, the DB object's read-only `pointer` property holds its 272 `sqlite3*` pointer value. That property can also be used to check 273 whether this DB instance is still open. 274 275 In the main window thread, the filenames `":localStorage:"` and 276 `":sessionStorage:"` are special: they cause the db to use either 277 localStorage or sessionStorage for storing the database using 278 the kvvfs. If one of these names are used, they trump 279 any vfs name set in the arguments. 280 */ 281 const DB = function(...args){ 282 dbCtorHelper.apply(this, args); 283 }; 284 DB.dbCtorHelper = dbCtorHelper; 285 286 /** 287 Internal-use enum for mapping JS types to DB-bindable types. 288 These do not (and need not) line up with the SQLITE_type 289 values. All values in this enum must be truthy and distinct 290 but they need not be numbers. 291 */ 292 const BindTypes = { 293 null: 1, 294 number: 2, 295 string: 3, 296 boolean: 4, 297 blob: 5 298 }; 299 BindTypes['undefined'] == BindTypes.null; 300 if(wasm.bigIntEnabled){ 301 BindTypes.bigint = BindTypes.number; 302 } 303 304 /** 305 This class wraps sqlite3_stmt. Calling this constructor 306 directly will trigger an exception. Use DB.prepare() to create 307 new instances. 308 309 For purposes of passing a Stmt instance to C-style sqlite3 310 functions, its read-only `pointer` property holds its `sqlite3_stmt*` 311 pointer value. 312 313 Other non-function properties include: 314 315 - `db`: the DB object which created the statement. 316 317 - `columnCount`: the number of result columns in the query, or 0 for 318 queries which cannot return results. 319 320 - `parameterCount`: the number of bindable paramters in the query. 321 */ 322 const Stmt = function(){ 323 if(BindTypes!==arguments[2]){ 324 toss3("Do not call the Stmt constructor directly. Use DB.prepare()."); 325 } 326 this.db = arguments[0]; 327 __ptrMap.set(this, arguments[1]); 328 this.columnCount = capi.sqlite3_column_count(this.pointer); 329 this.parameterCount = capi.sqlite3_bind_parameter_count(this.pointer); 330 }; 331 332 /** Throws if the given DB has been closed, else it is returned. */ 333 const affirmDbOpen = function(db){ 334 if(!db.pointer) toss3("DB has been closed."); 335 return db; 336 }; 337 338 /** Throws if ndx is not an integer or if it is out of range 339 for stmt.columnCount, else returns stmt. 340 341 Reminder: this will also fail after the statement is finalized 342 but the resulting error will be about an out-of-bounds column 343 index rather than a statement-is-finalized error. 344 */ 345 const affirmColIndex = function(stmt,ndx){ 346 if((ndx !== (ndx|0)) || ndx<0 || ndx>=stmt.columnCount){ 347 toss3("Column index",ndx,"is out of range."); 348 } 349 return stmt; 350 }; 351 352 /** 353 Expects to be passed the `arguments` object from DB.exec(). Does 354 the argument processing/validation, throws on error, and returns 355 a new object on success: 356 357 { sql: the SQL, opt: optionsObj, cbArg: function} 358 359 The opt object is a normalized copy of any passed to this 360 function. The sql will be converted to a string if it is provided 361 in one of the supported non-string formats. 362 363 cbArg is only set if the opt.callback or opt.resultRows are set, 364 in which case it's a function which expects to be passed the 365 current Stmt and returns the callback argument of the type 366 indicated by the input arguments. 367 */ 368 const parseExecArgs = function(db, args){ 369 const out = Object.create(null); 370 out.opt = Object.create(null); 371 switch(args.length){ 372 case 1: 373 if('string'===typeof args[0] || util.isSQLableTypedArray(args[0])){ 374 out.sql = args[0]; 375 }else if(Array.isArray(args[0])){ 376 out.sql = args[0]; 377 }else if(args[0] && 'object'===typeof args[0]){ 378 out.opt = args[0]; 379 out.sql = out.opt.sql; 380 } 381 break; 382 case 2: 383 out.sql = args[0]; 384 out.opt = args[1]; 385 break; 386 default: toss3("Invalid argument count for exec()."); 387 }; 388 out.sql = util.flexibleString(out.sql); 389 if('string'!==typeof out.sql){ 390 toss3("Missing SQL argument or unsupported SQL value type."); 391 } 392 const opt = out.opt; 393 switch(opt.returnValue){ 394 case 'resultRows': 395 if(!opt.resultRows) opt.resultRows = []; 396 out.returnVal = ()=>opt.resultRows; 397 break; 398 case 'saveSql': 399 if(!opt.saveSql) opt.saveSql = []; 400 out.returnVal = ()=>opt.saveSql; 401 break; 402 case undefined: 403 case 'this': 404 out.returnVal = ()=>db; 405 break; 406 default: 407 toss3("Invalid returnValue value:",opt.returnValue); 408 } 409 if(opt.callback || opt.resultRows){ 410 switch((undefined===opt.rowMode) 411 ? 'array' : opt.rowMode) { 412 case 'object': out.cbArg = (stmt)=>stmt.get(Object.create(null)); break; 413 case 'array': out.cbArg = (stmt)=>stmt.get([]); break; 414 case 'stmt': 415 if(Array.isArray(opt.resultRows)){ 416 toss3("exec(): invalid rowMode for a resultRows array: must", 417 "be one of 'array', 'object',", 418 "a result column number, or column name reference."); 419 } 420 out.cbArg = (stmt)=>stmt; 421 break; 422 default: 423 if(util.isInt32(opt.rowMode)){ 424 out.cbArg = (stmt)=>stmt.get(opt.rowMode); 425 break; 426 }else if('string'===typeof opt.rowMode && opt.rowMode.length>1){ 427 /* "$X", ":X", and "@X" fetch column named "X" (case-sensitive!) */ 428 const prefix = opt.rowMode[0]; 429 if(':'===prefix || '@'===prefix || '$'===prefix){ 430 out.cbArg = function(stmt){ 431 const rc = stmt.get(this.obj)[this.colName]; 432 return (undefined===rc) ? toss3("exec(): unknown result column:",this.colName) : rc; 433 }.bind({ 434 obj:Object.create(null), 435 colName: opt.rowMode.substr(1) 436 }); 437 break; 438 } 439 } 440 toss3("Invalid rowMode:",opt.rowMode); 441 } 442 } 443 return out; 444 }; 445 446 /** 447 Internal impl of the DB.selectArray() and 448 selectObject() methods. 449 */ 450 const __selectFirstRow = (db, sql, bind, getArg)=>{ 451 let stmt, rc; 452 try { 453 stmt = db.prepare(sql).bind(bind); 454 if(stmt.step()) rc = stmt.get(getArg); 455 }finally{ 456 if(stmt) stmt.finalize(); 457 } 458 return rc; 459 }; 460 461 /** 462 Expects to be given a DB instance or an `sqlite3*` pointer (may 463 be null) and an sqlite3 API result code. If the result code is 464 not falsy, this function throws an SQLite3Error with an error 465 message from sqlite3_errmsg(), using dbPtr as the db handle, or 466 sqlite3_errstr() if dbPtr is falsy. Note that if it's passed a 467 non-error code like SQLITE_ROW or SQLITE_DONE, it will still 468 throw but the error string might be "Not an error." The various 469 non-0 non-error codes need to be checked for in 470 client code where they are expected. 471 */ 472 DB.checkRc = checkSqlite3Rc; 473 474 DB.prototype = { 475 /** Returns true if this db handle is open, else false. */ 476 isOpen: function(){ 477 return !!this.pointer; 478 }, 479 /** Throws if this given DB has been closed, else returns `this`. */ 480 affirmOpen: function(){ 481 return affirmDbOpen(this); 482 }, 483 /** 484 Finalizes all open statements and closes this database 485 connection. This is a no-op if the db has already been 486 closed. After calling close(), `this.pointer` will resolve to 487 `undefined`, so that can be used to check whether the db 488 instance is still opened. 489 490 If this.onclose.before is a function then it is called before 491 any close-related cleanup. 492 493 If this.onclose.after is a function then it is called after the 494 db is closed but before auxiliary state like this.filename is 495 cleared. 496 497 Both onclose handlers are passed this object. If this db is not 498 opened, neither of the handlers are called. Any exceptions the 499 handlers throw are ignored because "destructors must not 500 throw." 501 502 Note that garbage collection of a db handle, if it happens at 503 all, will never trigger close(), so onclose handlers are not a 504 reliable way to implement close-time cleanup or maintenance of 505 a db. 506 */ 507 close: function(){ 508 if(this.pointer){ 509 if(this.onclose && (this.onclose.before instanceof Function)){ 510 try{this.onclose.before(this)} 511 catch(e){/*ignore*/} 512 } 513 const pDb = this.pointer; 514 Object.keys(__stmtMap.get(this)).forEach((k,s)=>{ 515 if(s && s.pointer) s.finalize(); 516 }); 517 __ptrMap.delete(this); 518 __stmtMap.delete(this); 519 capi.sqlite3_close_v2(pDb); 520 if(this.onclose && (this.onclose.after instanceof Function)){ 521 try{this.onclose.after(this)} 522 catch(e){/*ignore*/} 523 } 524 delete this.filename; 525 } 526 }, 527 /** 528 Returns the number of changes, as per sqlite3_changes() 529 (if the first argument is false) or sqlite3_total_changes() 530 (if it's true). If the 2nd argument is true, it uses 531 sqlite3_changes64() or sqlite3_total_changes64(), which 532 will trigger an exception if this build does not have 533 BigInt support enabled. 534 */ 535 changes: function(total=false,sixtyFour=false){ 536 const p = affirmDbOpen(this).pointer; 537 if(total){ 538 return sixtyFour 539 ? capi.sqlite3_total_changes64(p) 540 : capi.sqlite3_total_changes(p); 541 }else{ 542 return sixtyFour 543 ? capi.sqlite3_changes64(p) 544 : capi.sqlite3_changes(p); 545 } 546 }, 547 /** 548 Similar to the this.filename but returns the 549 sqlite3_db_filename() value for the given database name, 550 defaulting to "main". The argument may be either a JS string 551 or a pointer to a WASM-allocated C-string. 552 */ 553 dbFilename: function(dbName='main'){ 554 return capi.sqlite3_db_filename(affirmDbOpen(this).pointer, dbName); 555 }, 556 /** 557 Returns the name of the given 0-based db number, as documented 558 for sqlite3_db_name(). 559 */ 560 dbName: function(dbNumber=0){ 561 return capi.sqlite3_db_name(affirmDbOpen(this).pointer, dbNumber); 562 }, 563 /** 564 Returns the name of the sqlite3_vfs used by the given database 565 of this connection (defaulting to 'main'). The argument may be 566 either a JS string or a WASM C-string. Returns undefined if the 567 given db name is invalid. Throws if this object has been 568 close()d. 569 */ 570 dbVfsName: function(dbName=0){ 571 let rc; 572 const pVfs = capi.sqlite3_js_db_vfs( 573 affirmDbOpen(this).pointer, dbName 574 ); 575 if(pVfs){ 576 const v = new capi.sqlite3_vfs(pVfs); 577 try{ rc = wasm.cstringToJs(v.$zName) } 578 finally { v.dispose() } 579 } 580 return rc; 581 }, 582 /** 583 Compiles the given SQL and returns a prepared Stmt. This is 584 the only way to create new Stmt objects. Throws on error. 585 586 The given SQL must be a string, a Uint8Array holding SQL, a 587 WASM pointer to memory holding the NUL-terminated SQL string, 588 or an array of strings. In the latter case, the array is 589 concatenated together, with no separators, to form the SQL 590 string (arrays are often a convenient way to formulate long 591 statements). If the SQL contains no statements, an 592 SQLite3Error is thrown. 593 594 Design note: the C API permits empty SQL, reporting it as a 0 595 result code and a NULL stmt pointer. Supporting that case here 596 would cause extra work for all clients: any use of the Stmt API 597 on such a statement will necessarily throw, so clients would be 598 required to check `stmt.pointer` after calling `prepare()` in 599 order to determine whether the Stmt instance is empty or not. 600 Long-time practice (with other sqlite3 script bindings) 601 suggests that the empty-prepare case is sufficiently rare that 602 supporting it here would simply hurt overall usability. 603 */ 604 prepare: function(sql){ 605 affirmDbOpen(this); 606 const stack = wasm.pstack.pointer; 607 let ppStmt, pStmt; 608 try{ 609 ppStmt = wasm.pstack.alloc(8)/* output (sqlite3_stmt**) arg */; 610 DB.checkRc(this, capi.sqlite3_prepare_v2(this.pointer, sql, -1, ppStmt, null)); 611 pStmt = wasm.getPtrValue(ppStmt); 612 } 613 finally { 614 wasm.pstack.restore(stack); 615 } 616 if(!pStmt) toss3("Cannot prepare empty SQL."); 617 const stmt = new Stmt(this, pStmt, BindTypes); 618 __stmtMap.get(this)[pStmt] = stmt; 619 return stmt; 620 }, 621 /** 622 Executes one or more SQL statements in the form of a single 623 string. Its arguments must be either (sql,optionsObject) or 624 (optionsObject). In the latter case, optionsObject.sql must 625 contain the SQL to execute. By default it returns this object 626 but that can be changed via the `returnValue` option as 627 described below. Throws on error. 628 629 If no SQL is provided, or a non-string is provided, an 630 exception is triggered. Empty SQL, on the other hand, is 631 simply a no-op. 632 633 The optional options object may contain any of the following 634 properties: 635 636 - `sql` = the SQL to run (unless it's provided as the first 637 argument). This must be of type string, Uint8Array, or an array 638 of strings. In the latter case they're concatenated together 639 as-is, _with no separator_ between elements, before evaluation. 640 The array form is often simpler for long hand-written queries. 641 642 - `bind` = a single value valid as an argument for 643 Stmt.bind(). This is _only_ applied to the _first_ non-empty 644 statement in the SQL which has any bindable parameters. (Empty 645 statements are skipped entirely.) 646 647 - `saveSql` = an optional array. If set, the SQL of each 648 executed statement is appended to this array before the 649 statement is executed (but after it is prepared - we don't have 650 the string until after that). Empty SQL statements are elided 651 but can have odd effects in the output. e.g. SQL of: `"select 652 1; -- empty\n; select 2"` will result in an array containing 653 `["select 1;", "--empty \n; select 2"]`. That's simply how 654 sqlite3 records the SQL for the 2nd statement. 655 656 ================================================================== 657 The following options apply _only_ to the _first_ statement 658 which has a non-zero result column count, regardless of whether 659 the statement actually produces any result rows. 660 ================================================================== 661 662 - `columnNames`: if this is an array, the column names of the 663 result set are stored in this array before the callback (if 664 any) is triggered (regardless of whether the query produces any 665 result rows). If no statement has result columns, this value is 666 unchanged. Achtung: an SQL result may have multiple columns 667 with identical names. 668 669 - `callback` = a function which gets called for each row of 670 the result set, but only if that statement has any result 671 _rows_. The callback's "this" is the options object, noting 672 that this function synthesizes one if the caller does not pass 673 one to exec(). The second argument passed to the callback is 674 always the current Stmt object, as it's needed if the caller 675 wants to fetch the column names or some such (noting that they 676 could also be fetched via `this.columnNames`, if the client 677 provides the `columnNames` option). 678 679 ACHTUNG: The callback MUST NOT modify the Stmt object. Calling 680 any of the Stmt.get() variants, Stmt.getColumnName(), or 681 similar, is legal, but calling step() or finalize() is 682 not. Member methods which are illegal in this context will 683 trigger an exception. 684 685 The first argument passed to the callback defaults to an array of 686 values from the current result row but may be changed with ... 687 688 - `rowMode` = specifies the type of he callback's first argument. 689 It may be any of... 690 691 A) A string describing what type of argument should be passed 692 as the first argument to the callback: 693 694 A.1) `'array'` (the default) causes the results of 695 `stmt.get([])` to be passed to the `callback` and/or appended 696 to `resultRows` 697 698 A.2) `'object'` causes the results of 699 `stmt.get(Object.create(null))` to be passed to the 700 `callback` and/or appended to `resultRows`. Achtung: an SQL 701 result may have multiple columns with identical names. In 702 that case, the right-most column will be the one set in this 703 object! 704 705 A.3) `'stmt'` causes the current Stmt to be passed to the 706 callback, but this mode will trigger an exception if 707 `resultRows` is an array because appending the statement to 708 the array would be downright unhelpful. 709 710 B) An integer, indicating a zero-based column in the result 711 row. Only that one single value will be passed on. 712 713 C) A string with a minimum length of 2 and leading character of 714 ':', '$', or '@' will fetch the row as an object, extract that 715 one field, and pass that field's value to the callback. Note 716 that these keys are case-sensitive so must match the case used 717 in the SQL. e.g. `"select a A from t"` with a `rowMode` of 718 `'$A'` would work but `'$a'` would not. A reference to a column 719 not in the result set will trigger an exception on the first 720 row (as the check is not performed until rows are fetched). 721 Note also that `$` is a legal identifier character in JS so 722 need not be quoted. (Design note: those 3 characters were 723 chosen because they are the characters support for naming bound 724 parameters.) 725 726 Any other `rowMode` value triggers an exception. 727 728 - `resultRows`: if this is an array, it functions similarly to 729 the `callback` option: each row of the result set (if any), 730 with the exception that the `rowMode` 'stmt' is not legal. It 731 is legal to use both `resultRows` and `callback`, but 732 `resultRows` is likely much simpler to use for small data sets 733 and can be used over a WebWorker-style message interface. 734 exec() throws if `resultRows` is set and `rowMode` is 'stmt'. 735 736 - `returnValue`: is a string specifying what this function 737 should return: 738 739 A) The default value is `"this"`, meaning that the 740 DB object itself should be returned. 741 742 B) `"resultRows"` means to return the value of the 743 `resultRows` option. If `resultRows` is not set, this 744 function behaves as if it were set to an empty array. 745 746 C) `"saveSql"` means to return the value of the 747 `saveSql` option. If `saveSql` is not set, this 748 function behaves as if it were set to an empty array. 749 750 Potential TODOs: 751 752 - `bind`: permit an array of arrays/objects to bind. The first 753 sub-array would act on the first statement which has bindable 754 parameters (as it does now). The 2nd would act on the next such 755 statement, etc. 756 757 - `callback` and `resultRows`: permit an array entries with 758 semantics similar to those described for `bind` above. 759 760 */ 761 exec: function(/*(sql [,obj]) || (obj)*/){ 762 affirmDbOpen(this); 763 const arg = parseExecArgs(this, arguments); 764 if(!arg.sql){ 765 return toss3("exec() requires an SQL string."); 766 } 767 const opt = arg.opt; 768 const callback = opt.callback; 769 const resultRows = 770 Array.isArray(opt.resultRows) ? opt.resultRows : undefined; 771 let stmt; 772 let bind = opt.bind; 773 let evalFirstResult = !!(arg.cbArg || opt.columnNames) /* true to evaluate the first result-returning query */; 774 const stack = wasm.scopedAllocPush(); 775 try{ 776 const isTA = util.isSQLableTypedArray(arg.sql) 777 /* Optimization: if the SQL is a TypedArray we can save some string 778 conversion costs. */; 779 /* Allocate the two output pointers (ppStmt, pzTail) and heap 780 space for the SQL (pSql). When prepare_v2() returns, pzTail 781 will point to somewhere in pSql. */ 782 let sqlByteLen = isTA ? arg.sql.byteLength : wasm.jstrlen(arg.sql); 783 const ppStmt = wasm.scopedAlloc(/* output (sqlite3_stmt**) arg and pzTail */ 784 (2 * wasm.ptrSizeof) 785 + (sqlByteLen + 1/* SQL + NUL */)); 786 const pzTail = ppStmt + wasm.ptrSizeof /* final arg to sqlite3_prepare_v2() */; 787 let pSql = pzTail + wasm.ptrSizeof; 788 const pSqlEnd = pSql + sqlByteLen; 789 if(isTA) wasm.heap8().set(arg.sql, pSql); 790 else wasm.jstrcpy(arg.sql, wasm.heap8(), pSql, sqlByteLen, false); 791 wasm.setMemValue(pSql + sqlByteLen, 0/*NUL terminator*/); 792 while(pSql && wasm.getMemValue(pSql, 'i8') 793 /* Maintenance reminder:^^^ _must_ be 'i8' or else we 794 will very likely cause an endless loop. What that's 795 doing is checking for a terminating NUL byte. If we 796 use i32 or similar then we read 4 bytes, read stuff 797 around the NUL terminator, and get stuck in and 798 endless loop at the end of the SQL, endlessly 799 re-preparing an empty statement. */ ){ 800 wasm.setPtrValue(ppStmt, 0); 801 wasm.setPtrValue(pzTail, 0); 802 DB.checkRc(this, capi.sqlite3_prepare_v3( 803 this.pointer, pSql, sqlByteLen, 0, ppStmt, pzTail 804 )); 805 const pStmt = wasm.getPtrValue(ppStmt); 806 pSql = wasm.getPtrValue(pzTail); 807 sqlByteLen = pSqlEnd - pSql; 808 if(!pStmt) continue; 809 if(Array.isArray(opt.saveSql)){ 810 opt.saveSql.push(capi.sqlite3_sql(pStmt).trim()); 811 } 812 stmt = new Stmt(this, pStmt, BindTypes); 813 if(bind && stmt.parameterCount){ 814 stmt.bind(bind); 815 bind = null; 816 } 817 if(evalFirstResult && stmt.columnCount){ 818 /* Only forward SELECT results for the FIRST query 819 in the SQL which potentially has them. */ 820 evalFirstResult = false; 821 if(Array.isArray(opt.columnNames)){ 822 stmt.getColumnNames(opt.columnNames); 823 } 824 while(!!arg.cbArg && stmt.step()){ 825 stmt._isLocked = true; 826 const row = arg.cbArg(stmt); 827 if(resultRows) resultRows.push(row); 828 if(callback) callback.call(opt, row, stmt); 829 stmt._isLocked = false; 830 } 831 }else{ 832 stmt.step(); 833 } 834 stmt.finalize(); 835 stmt = null; 836 } 837 }/*catch(e){ 838 console.warn("DB.exec() is propagating exception",opt,e); 839 throw e; 840 }*/finally{ 841 if(stmt){ 842 delete stmt._isLocked; 843 stmt.finalize(); 844 } 845 wasm.scopedAllocPop(stack); 846 } 847 return arg.returnVal(); 848 }/*exec()*/, 849 /** 850 Creates a new scalar UDF (User-Defined Function) which is 851 accessible via SQL code. This function may be called in any 852 of the following forms: 853 854 - (name, function) 855 - (name, function, optionsObject) 856 - (name, optionsObject) 857 - (optionsObject) 858 859 In the final two cases, the function must be defined as the 860 `callback` property of the options object (optionally called 861 `xFunc` to align with the C API documentation). In the final 862 case, the function's name must be the 'name' property. 863 864 The first two call forms can only be used for creating scalar 865 functions. Creating an aggregate or window function requires 866 the options-object form (see below for details). 867 868 UDFs cannot currently be removed from a DB handle after they're 869 added. More correctly, they can be removed as documented for 870 sqlite3_create_function_v2(), but doing so will "leak" the 871 JS-created WASM binding of those functions. 872 873 On success, returns this object. Throws on error. 874 875 When called from SQL arguments to the UDF, and its result, 876 will be converted between JS and SQL with as much fidelity as 877 is feasible, triggering an exception if a type conversion 878 cannot be determined. The docs for sqlite3_create_function_v2() 879 describe the conversions in more detail. 880 881 The values set in the options object differ for scalar and 882 aggregate functions: 883 884 - Scalar: set the `xFunc` function-type property to the UDF 885 function. 886 887 - Aggregate: set the `xStep` and `xFinal` function-type 888 properties to the "step" and "final" callbacks for the 889 aggregate. Do not set the `xFunc` property. 890 891 - Window: set the `xStep`, `xFinal`, `xValue`, and `xInverse` 892 function-type properties. Do not set the `xFunc` property. 893 894 The options object may optionally have an `xDestroy` 895 function-type property, as per sqlite3_create_function_v2(). 896 Its argument will be the WASM-pointer-type value of the `pApp` 897 property, and this function will throw if `pApp` is defined but 898 is not null, undefined, or a numeric (WASM pointer) 899 value. i.e. `pApp`, if set, must be value suitable for use as a 900 WASM pointer argument, noting that `null` or `undefined` will 901 translate to 0 for that purpose. 902 903 The options object may contain flags to modify how 904 the function is defined: 905 906 - `arity`: the number of arguments which SQL calls to this 907 function expect or require. The default value is `xFunc.length` 908 or `xStep.length` (i.e. the number of declared parameters it 909 has) **MINUS 1** (see below for why). As a special case, if the 910 `length` is 0, its arity is also 0 instead of -1. A negative 911 arity value means that the function is variadic and may accept 912 any number of arguments, up to sqlite3's compile-time 913 limits. sqlite3 will enforce the argument count if is zero or 914 greater. The callback always receives a pointer to an 915 `sqlite3_context` object as its first argument. Any arguments 916 after that are from SQL code. The leading context argument does 917 _not_ count towards the function's arity. See the docs for 918 sqlite3.capi.sqlite3_create_function_v2() for why that argument 919 is needed in the interface. 920 921 The following options-object properties correspond to flags 922 documented at: 923 924 https://sqlite.org/c3ref/create_function.html 925 926 - `deterministic` = sqlite3.capi.SQLITE_DETERMINISTIC 927 - `directOnly` = sqlite3.capi.SQLITE_DIRECTONLY 928 - `innocuous` = sqlite3.capi.SQLITE_INNOCUOUS 929 930 Sidebar: the ability to add new WASM-accessible functions to 931 the runtime requires that the WASM build is compiled with the 932 equivalent functionality as that provided by Emscripten's 933 `-sALLOW_TABLE_GROWTH` flag. 934 */ 935 createFunction: function f(name, xFunc, opt){ 936 const isFunc = (f)=>(f instanceof Function); 937 switch(arguments.length){ 938 case 1: /* (optionsObject) */ 939 opt = name; 940 name = opt.name; 941 xFunc = opt.xFunc || 0; 942 break; 943 case 2: /* (name, callback|optionsObject) */ 944 if(!isFunc(xFunc)){ 945 opt = xFunc; 946 xFunc = opt.xFunc || 0; 947 } 948 break; 949 case 3: /* name, xFunc, opt */ 950 break; 951 default: break; 952 } 953 if(!opt) opt = {}; 954 if('string' !== typeof name){ 955 toss3("Invalid arguments: missing function name."); 956 } 957 let xStep = opt.xStep || 0; 958 let xFinal = opt.xFinal || 0; 959 const xValue = opt.xValue || 0; 960 const xInverse = opt.xInverse || 0; 961 let isWindow = undefined; 962 if(isFunc(xFunc)){ 963 isWindow = false; 964 if(isFunc(xStep) || isFunc(xFinal)){ 965 toss3("Ambiguous arguments: scalar or aggregate?"); 966 } 967 xStep = xFinal = null; 968 }else if(isFunc(xStep)){ 969 if(!isFunc(xFinal)){ 970 toss3("Missing xFinal() callback for aggregate or window UDF."); 971 } 972 xFunc = null; 973 }else if(isFunc(xFinal)){ 974 toss3("Missing xStep() callback for aggregate or window UDF."); 975 }else{ 976 toss3("Missing function-type properties."); 977 } 978 if(false === isWindow){ 979 if(isFunc(xValue) || isFunc(xInverse)){ 980 toss3("xValue and xInverse are not permitted for non-window UDFs."); 981 } 982 }else if(isFunc(xValue)){ 983 if(!isFunc(xInverse)){ 984 toss3("xInverse must be provided if xValue is."); 985 } 986 isWindow = true; 987 }else if(isFunc(xInverse)){ 988 toss3("xValue must be provided if xInverse is."); 989 } 990 const pApp = opt.pApp; 991 if(undefined!==pApp && 992 null!==pApp && 993 (('number'!==typeof pApp) || !util.isInt32(pApp))){ 994 toss3("Invalid value for pApp property. Must be a legal WASM pointer value."); 995 } 996 const xDestroy = opt.xDestroy || 0; 997 if(xDestroy && !isFunc(xDestroy)){ 998 toss3("xDestroy property must be a function."); 999 } 1000 let fFlags = 0 /*flags for sqlite3_create_function_v2()*/; 1001 if(getOwnOption(opt, 'deterministic')) fFlags |= capi.SQLITE_DETERMINISTIC; 1002 if(getOwnOption(opt, 'directOnly')) fFlags |= capi.SQLITE_DIRECTONLY; 1003 if(getOwnOption(opt, 'innocuous')) fFlags |= capi.SQLITE_INNOCUOUS; 1004 name = name.toLowerCase(); 1005 const xArity = xFunc || xStep; 1006 const arity = getOwnOption(opt, 'arity'); 1007 const arityArg = ('number'===typeof arity 1008 ? arity 1009 : (xArity.length ? xArity.length-1/*for pCtx arg*/ : 0)); 1010 let rc; 1011 if( isWindow ){ 1012 rc = capi.sqlite3_create_window_function( 1013 this.pointer, name, arityArg, 1014 capi.SQLITE_UTF8 | fFlags, pApp || 0, 1015 xStep, xFinal, xValue, xInverse, xDestroy); 1016 }else{ 1017 rc = capi.sqlite3_create_function_v2( 1018 this.pointer, name, arityArg, 1019 capi.SQLITE_UTF8 | fFlags, pApp || 0, 1020 xFunc, xStep, xFinal, xDestroy); 1021 } 1022 DB.checkRc(this, rc); 1023 return this; 1024 }/*createFunction()*/, 1025 /** 1026 Prepares the given SQL, step()s it one time, and returns 1027 the value of the first result column. If it has no results, 1028 undefined is returned. 1029 1030 If passed a second argument, it is treated like an argument 1031 to Stmt.bind(), so may be any type supported by that 1032 function. Passing the undefined value is the same as passing 1033 no value, which is useful when... 1034 1035 If passed a 3rd argument, it is expected to be one of the 1036 SQLITE_{typename} constants. Passing the undefined value is 1037 the same as not passing a value. 1038 1039 Throws on error (e.g. malformed SQL). 1040 */ 1041 selectValue: function(sql,bind,asType){ 1042 let stmt, rc; 1043 try { 1044 stmt = this.prepare(sql).bind(bind); 1045 if(stmt.step()) rc = stmt.get(0,asType); 1046 }finally{ 1047 if(stmt) stmt.finalize(); 1048 } 1049 return rc; 1050 }, 1051 /** 1052 Prepares the given SQL, step()s it one time, and returns an 1053 array containing the values of the first result row. If it has 1054 no results, `undefined` is returned. 1055 1056 If passed a second argument other than `undefined`, it is 1057 treated like an argument to Stmt.bind(), so may be any type 1058 supported by that function. 1059 1060 Throws on error (e.g. malformed SQL). 1061 */ 1062 selectArray: function(sql,bind){ 1063 return __selectFirstRow(this, sql, bind, []); 1064 }, 1065 1066 /** 1067 Prepares the given SQL, step()s it one time, and returns an 1068 object containing the key/value pairs of the first result 1069 row. If it has no results, `undefined` is returned. 1070 1071 Note that the order of returned object's keys is not guaranteed 1072 to be the same as the order of the fields in the query string. 1073 1074 If passed a second argument other than `undefined`, it is 1075 treated like an argument to Stmt.bind(), so may be any type 1076 supported by that function. 1077 1078 Throws on error (e.g. malformed SQL). 1079 */ 1080 selectObject: function(sql,bind){ 1081 return __selectFirstRow(this, sql, bind, {}); 1082 }, 1083 1084 /** 1085 Returns the number of currently-opened Stmt handles for this db 1086 handle, or 0 if this DB instance is closed. 1087 */ 1088 openStatementCount: function(){ 1089 return this.pointer ? Object.keys(__stmtMap.get(this)).length : 0; 1090 }, 1091 1092 /** 1093 Starts a transaction, calls the given callback, and then either 1094 rolls back or commits the savepoint, depending on whether the 1095 callback throws. The callback is passed this db object as its 1096 only argument. On success, returns the result of the 1097 callback. Throws on error. 1098 1099 Note that transactions may not be nested, so this will throw if 1100 it is called recursively. For nested transactions, use the 1101 savepoint() method or manually manage SAVEPOINTs using exec(). 1102 */ 1103 transaction: function(callback){ 1104 affirmDbOpen(this).exec("BEGIN"); 1105 try { 1106 const rc = callback(this); 1107 this.exec("COMMIT"); 1108 return rc; 1109 }catch(e){ 1110 this.exec("ROLLBACK"); 1111 throw e; 1112 } 1113 }, 1114 1115 /** 1116 This works similarly to transaction() but uses sqlite3's SAVEPOINT 1117 feature. This function starts a savepoint (with an unspecified name) 1118 and calls the given callback function, passing it this db object. 1119 If the callback returns, the savepoint is released (committed). If 1120 the callback throws, the savepoint is rolled back. If it does not 1121 throw, it returns the result of the callback. 1122 */ 1123 savepoint: function(callback){ 1124 affirmDbOpen(this).exec("SAVEPOINT oo1"); 1125 try { 1126 const rc = callback(this); 1127 this.exec("RELEASE oo1"); 1128 return rc; 1129 }catch(e){ 1130 this.exec("ROLLBACK to SAVEPOINT oo1; RELEASE SAVEPOINT oo1"); 1131 throw e; 1132 } 1133 } 1134 }/*DB.prototype*/; 1135 1136 1137 /** Throws if the given Stmt has been finalized, else stmt is 1138 returned. */ 1139 const affirmStmtOpen = function(stmt){ 1140 if(!stmt.pointer) toss3("Stmt has been closed."); 1141 return stmt; 1142 }; 1143 1144 /** Returns an opaque truthy value from the BindTypes 1145 enum if v's type is a valid bindable type, else 1146 returns a falsy value. As a special case, a value of 1147 undefined is treated as a bind type of null. */ 1148 const isSupportedBindType = function(v){ 1149 let t = BindTypes[(null===v||undefined===v) ? 'null' : typeof v]; 1150 switch(t){ 1151 case BindTypes.boolean: 1152 case BindTypes.null: 1153 case BindTypes.number: 1154 case BindTypes.string: 1155 return t; 1156 case BindTypes.bigint: 1157 if(wasm.bigIntEnabled) return t; 1158 /* else fall through */ 1159 default: 1160 //console.log("isSupportedBindType",t,v); 1161 return util.isBindableTypedArray(v) ? BindTypes.blob : undefined; 1162 } 1163 }; 1164 1165 /** 1166 If isSupportedBindType(v) returns a truthy value, this 1167 function returns that value, else it throws. 1168 */ 1169 const affirmSupportedBindType = function(v){ 1170 //console.log('affirmSupportedBindType',v); 1171 return isSupportedBindType(v) || toss3("Unsupported bind() argument type:",typeof v); 1172 }; 1173 1174 /** 1175 If key is a number and within range of stmt's bound parameter 1176 count, key is returned. 1177 1178 If key is not a number then it is checked against named 1179 parameters. If a match is found, its index is returned. 1180 1181 Else it throws. 1182 */ 1183 const affirmParamIndex = function(stmt,key){ 1184 const n = ('number'===typeof key) 1185 ? key : capi.sqlite3_bind_parameter_index(stmt.pointer, key); 1186 if(0===n || !util.isInt32(n)){ 1187 toss3("Invalid bind() parameter name: "+key); 1188 } 1189 else if(n<1 || n>stmt.parameterCount) toss3("Bind index",key,"is out of range."); 1190 return n; 1191 }; 1192 1193 /** 1194 If stmt._isLocked is truthy, this throws an exception 1195 complaining that the 2nd argument (an operation name, 1196 e.g. "bind()") is not legal while the statement is "locked". 1197 Locking happens before an exec()-like callback is passed a 1198 statement, to ensure that the callback does not mutate or 1199 finalize the statement. If it does not throw, it returns stmt. 1200 */ 1201 const affirmUnlocked = function(stmt,currentOpName){ 1202 if(stmt._isLocked){ 1203 toss3("Operation is illegal when statement is locked:",currentOpName); 1204 } 1205 return stmt; 1206 }; 1207 1208 /** 1209 Binds a single bound parameter value on the given stmt at the 1210 given index (numeric or named) using the given bindType (see 1211 the BindTypes enum) and value. Throws on error. Returns stmt on 1212 success. 1213 */ 1214 const bindOne = function f(stmt,ndx,bindType,val){ 1215 affirmUnlocked(stmt, 'bind()'); 1216 if(!f._){ 1217 f._tooBigInt = (v)=>toss3( 1218 "BigInt value is too big to store without precision loss:", v 1219 ); 1220 /* Reminder: when not in BigInt mode, it's impossible for 1221 JS to represent a number out of the range we can bind, 1222 so we have no range checking. */ 1223 f._ = { 1224 string: function(stmt, ndx, val, asBlob){ 1225 if(1){ 1226 /* _Hypothetically_ more efficient than the impl in the 'else' block. */ 1227 const stack = wasm.scopedAllocPush(); 1228 try{ 1229 const n = wasm.jstrlen(val); 1230 const pStr = wasm.scopedAlloc(n); 1231 wasm.jstrcpy(val, wasm.heap8u(), pStr, n, false); 1232 const f = asBlob ? capi.sqlite3_bind_blob : capi.sqlite3_bind_text; 1233 return f(stmt.pointer, ndx, pStr, n, capi.SQLITE_TRANSIENT); 1234 }finally{ 1235 wasm.scopedAllocPop(stack); 1236 } 1237 }else{ 1238 const bytes = wasm.jstrToUintArray(val,false); 1239 const pStr = wasm.alloc(bytes.length || 1); 1240 wasm.heap8u().set(bytes.length ? bytes : [0], pStr); 1241 try{ 1242 const f = asBlob ? capi.sqlite3_bind_blob : capi.sqlite3_bind_text; 1243 return f(stmt.pointer, ndx, pStr, bytes.length, capi.SQLITE_TRANSIENT); 1244 }finally{ 1245 wasm.dealloc(pStr); 1246 } 1247 } 1248 } 1249 }; 1250 }/* static init */ 1251 affirmSupportedBindType(val); 1252 ndx = affirmParamIndex(stmt,ndx); 1253 let rc = 0; 1254 switch((null===val || undefined===val) ? BindTypes.null : bindType){ 1255 case BindTypes.null: 1256 rc = capi.sqlite3_bind_null(stmt.pointer, ndx); 1257 break; 1258 case BindTypes.string: 1259 rc = f._.string(stmt, ndx, val, false); 1260 break; 1261 case BindTypes.number: { 1262 let m; 1263 if(util.isInt32(val)) m = capi.sqlite3_bind_int; 1264 else if('bigint'===typeof val){ 1265 if(!util.bigIntFits64(val)){ 1266 f._tooBigInt(val); 1267 }else if(wasm.bigIntEnabled){ 1268 m = capi.sqlite3_bind_int64; 1269 }else if(util.bigIntFitsDouble(val)){ 1270 val = Number(val); 1271 m = capi.sqlite3_bind_double; 1272 }else{ 1273 f._tooBigInt(val); 1274 } 1275 }else{ // !int32, !bigint 1276 val = Number(val); 1277 if(wasm.bigIntEnabled && Number.isInteger(val)){ 1278 m = capi.sqlite3_bind_int64; 1279 }else{ 1280 m = capi.sqlite3_bind_double; 1281 } 1282 } 1283 rc = m(stmt.pointer, ndx, val); 1284 break; 1285 } 1286 case BindTypes.boolean: 1287 rc = capi.sqlite3_bind_int(stmt.pointer, ndx, val ? 1 : 0); 1288 break; 1289 case BindTypes.blob: { 1290 if('string'===typeof val){ 1291 rc = f._.string(stmt, ndx, val, true); 1292 }else if(!util.isBindableTypedArray(val)){ 1293 toss3("Binding a value as a blob requires", 1294 "that it be a string, Uint8Array, or Int8Array."); 1295 }else if(1){ 1296 /* _Hypothetically_ more efficient than the impl in the 'else' block. */ 1297 const stack = wasm.scopedAllocPush(); 1298 try{ 1299 const pBlob = wasm.scopedAlloc(val.byteLength || 1); 1300 wasm.heap8().set(val.byteLength ? val : [0], pBlob) 1301 rc = capi.sqlite3_bind_blob(stmt.pointer, ndx, pBlob, val.byteLength, 1302 capi.SQLITE_TRANSIENT); 1303 }finally{ 1304 wasm.scopedAllocPop(stack); 1305 } 1306 }else{ 1307 const pBlob = wasm.allocFromTypedArray(val); 1308 try{ 1309 rc = capi.sqlite3_bind_blob(stmt.pointer, ndx, pBlob, val.byteLength, 1310 capi.SQLITE_TRANSIENT); 1311 }finally{ 1312 wasm.dealloc(pBlob); 1313 } 1314 } 1315 break; 1316 } 1317 default: 1318 console.warn("Unsupported bind() argument type:",val); 1319 toss3("Unsupported bind() argument type: "+(typeof val)); 1320 } 1321 if(rc) DB.checkRc(stmt.db.pointer, rc); 1322 return stmt; 1323 }; 1324 1325 Stmt.prototype = { 1326 /** 1327 "Finalizes" this statement. This is a no-op if the 1328 statement has already been finalizes. Returns 1329 undefined. Most methods in this class will throw if called 1330 after this is. 1331 */ 1332 finalize: function(){ 1333 if(this.pointer){ 1334 affirmUnlocked(this,'finalize()'); 1335 delete __stmtMap.get(this.db)[this.pointer]; 1336 capi.sqlite3_finalize(this.pointer); 1337 __ptrMap.delete(this); 1338 delete this._mayGet; 1339 delete this.columnCount; 1340 delete this.parameterCount; 1341 delete this.db; 1342 delete this._isLocked; 1343 } 1344 }, 1345 /** Clears all bound values. Returns this object. 1346 Throws if this statement has been finalized. */ 1347 clearBindings: function(){ 1348 affirmUnlocked(affirmStmtOpen(this), 'clearBindings()') 1349 capi.sqlite3_clear_bindings(this.pointer); 1350 this._mayGet = false; 1351 return this; 1352 }, 1353 /** 1354 Resets this statement so that it may be step()ed again 1355 from the beginning. Returns this object. Throws if this 1356 statement has been finalized. 1357 1358 If passed a truthy argument then this.clearBindings() is 1359 also called, otherwise any existing bindings, along with 1360 any memory allocated for them, are retained. 1361 */ 1362 reset: function(alsoClearBinds){ 1363 affirmUnlocked(this,'reset()'); 1364 if(alsoClearBinds) this.clearBindings(); 1365 capi.sqlite3_reset(affirmStmtOpen(this).pointer); 1366 this._mayGet = false; 1367 return this; 1368 }, 1369 /** 1370 Binds one or more values to its bindable parameters. It 1371 accepts 1 or 2 arguments: 1372 1373 If passed a single argument, it must be either an array, an 1374 object, or a value of a bindable type (see below). 1375 1376 If passed 2 arguments, the first one is the 1-based bind 1377 index or bindable parameter name and the second one must be 1378 a value of a bindable type. 1379 1380 Bindable value types: 1381 1382 - null is bound as NULL. 1383 1384 - undefined as a standalone value is a no-op intended to 1385 simplify certain client-side use cases: passing undefined as 1386 a value to this function will not actually bind anything and 1387 this function will skip confirmation that binding is even 1388 legal. (Those semantics simplify certain client-side uses.) 1389 Conversely, a value of undefined as an array or object 1390 property when binding an array/object (see below) is treated 1391 the same as null. 1392 1393 - Numbers are bound as either doubles or integers: doubles if 1394 they are larger than 32 bits, else double or int32, depending 1395 on whether they have a fractional part. Booleans are bound as 1396 integer 0 or 1. It is not expected the distinction of binding 1397 doubles which have no fractional parts is integers is 1398 significant for the majority of clients due to sqlite3's data 1399 typing model. If [BigInt] support is enabled then this 1400 routine will bind BigInt values as 64-bit integers if they'll 1401 fit in 64 bits. If that support disabled, it will store the 1402 BigInt as an int32 or a double if it can do so without loss 1403 of precision. If the BigInt is _too BigInt_ then it will 1404 throw. 1405 1406 - Strings are bound as strings (use bindAsBlob() to force 1407 blob binding). 1408 1409 - Uint8Array and Int8Array instances are bound as blobs. 1410 (TODO: binding the other TypedArray types.) 1411 1412 If passed an array, each element of the array is bound at 1413 the parameter index equal to the array index plus 1 1414 (because arrays are 0-based but binding is 1-based). 1415 1416 If passed an object, each object key is treated as a 1417 bindable parameter name. The object keys _must_ match any 1418 bindable parameter names, including any `$`, `@`, or `:` 1419 prefix. Because `$` is a legal identifier chararacter in 1420 JavaScript, that is the suggested prefix for bindable 1421 parameters: `stmt.bind({$a: 1, $b: 2})`. 1422 1423 It returns this object on success and throws on 1424 error. Errors include: 1425 1426 - Any bind index is out of range, a named bind parameter 1427 does not match, or this statement has no bindable 1428 parameters. 1429 1430 - Any value to bind is of an unsupported type. 1431 1432 - Passed no arguments or more than two. 1433 1434 - The statement has been finalized. 1435 */ 1436 bind: function(/*[ndx,] arg*/){ 1437 affirmStmtOpen(this); 1438 let ndx, arg; 1439 switch(arguments.length){ 1440 case 1: ndx = 1; arg = arguments[0]; break; 1441 case 2: ndx = arguments[0]; arg = arguments[1]; break; 1442 default: toss3("Invalid bind() arguments."); 1443 } 1444 if(undefined===arg){ 1445 /* It might seem intuitive to bind undefined as NULL 1446 but this approach simplifies certain client-side 1447 uses when passing on arguments between 2+ levels of 1448 functions. */ 1449 return this; 1450 }else if(!this.parameterCount){ 1451 toss3("This statement has no bindable parameters."); 1452 } 1453 this._mayGet = false; 1454 if(null===arg){ 1455 /* bind NULL */ 1456 return bindOne(this, ndx, BindTypes.null, arg); 1457 } 1458 else if(Array.isArray(arg)){ 1459 /* bind each entry by index */ 1460 if(1!==arguments.length){ 1461 toss3("When binding an array, an index argument is not permitted."); 1462 } 1463 arg.forEach((v,i)=>bindOne(this, i+1, affirmSupportedBindType(v), v)); 1464 return this; 1465 } 1466 else if('object'===typeof arg/*null was checked above*/ 1467 && !util.isBindableTypedArray(arg)){ 1468 /* Treat each property of arg as a named bound parameter. */ 1469 if(1!==arguments.length){ 1470 toss3("When binding an object, an index argument is not permitted."); 1471 } 1472 Object.keys(arg) 1473 .forEach(k=>bindOne(this, k, 1474 affirmSupportedBindType(arg[k]), 1475 arg[k])); 1476 return this; 1477 }else{ 1478 return bindOne(this, ndx, affirmSupportedBindType(arg), arg); 1479 } 1480 toss3("Should not reach this point."); 1481 }, 1482 /** 1483 Special case of bind() which binds the given value using the 1484 BLOB binding mechanism instead of the default selected one for 1485 the value. The ndx may be a numbered or named bind index. The 1486 value must be of type string, null/undefined (both get treated 1487 as null), or a TypedArray of a type supported by the bind() 1488 API. 1489 1490 If passed a single argument, a bind index of 1 is assumed and 1491 the first argument is the value. 1492 */ 1493 bindAsBlob: function(ndx,arg){ 1494 affirmStmtOpen(this); 1495 if(1===arguments.length){ 1496 arg = ndx; 1497 ndx = 1; 1498 } 1499 const t = affirmSupportedBindType(arg); 1500 if(BindTypes.string !== t && BindTypes.blob !== t 1501 && BindTypes.null !== t){ 1502 toss3("Invalid value type for bindAsBlob()"); 1503 } 1504 bindOne(this, ndx, BindTypes.blob, arg); 1505 this._mayGet = false; 1506 return this; 1507 }, 1508 /** 1509 Steps the statement one time. If the result indicates that a 1510 row of data is available, a truthy value is returned. 1511 If no row of data is available, a falsy 1512 value is returned. Throws on error. 1513 */ 1514 step: function(){ 1515 affirmUnlocked(this, 'step()'); 1516 const rc = capi.sqlite3_step(affirmStmtOpen(this).pointer); 1517 switch(rc){ 1518 case capi.SQLITE_DONE: return this._mayGet = false; 1519 case capi.SQLITE_ROW: return this._mayGet = true; 1520 default: 1521 this._mayGet = false; 1522 console.warn("sqlite3_step() rc=",rc, 1523 capi.sqlite3_js_rc_str(rc), 1524 "SQL =", capi.sqlite3_sql(this.pointer)); 1525 DB.checkRc(this.db.pointer, rc); 1526 } 1527 }, 1528 /** 1529 Functions exactly like step() except that... 1530 1531 1) On success, it calls this.reset() and returns this object. 1532 2) On error, it throws and does not call reset(). 1533 1534 This is intended to simplify constructs like: 1535 1536 ``` 1537 for(...) { 1538 stmt.bind(...).stepReset(); 1539 } 1540 ``` 1541 1542 Note that the reset() call makes it illegal to call this.get() 1543 after the step. 1544 */ 1545 stepReset: function(){ 1546 this.step(); 1547 return this.reset(); 1548 }, 1549 /** 1550 Functions like step() except that it finalizes this statement 1551 immediately after stepping unless the step cannot be performed 1552 because the statement is locked. Throws on error, but any error 1553 other than the statement-is-locked case will also trigger 1554 finalization of this statement. 1555 1556 On success, it returns true if the step indicated that a row of 1557 data was available, else it returns false. 1558 1559 This is intended to simplify use cases such as: 1560 1561 ``` 1562 aDb.prepare("insert into foo(a) values(?)").bind(123).stepFinalize(); 1563 ``` 1564 */ 1565 stepFinalize: function(){ 1566 const rc = this.step(); 1567 this.finalize(); 1568 return rc; 1569 }, 1570 /** 1571 Fetches the value from the given 0-based column index of 1572 the current data row, throwing if index is out of range. 1573 1574 Requires that step() has just returned a truthy value, else 1575 an exception is thrown. 1576 1577 By default it will determine the data type of the result 1578 automatically. If passed a second arugment, it must be one 1579 of the enumeration values for sqlite3 types, which are 1580 defined as members of the sqlite3 module: SQLITE_INTEGER, 1581 SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB. Any other value, 1582 except for undefined, will trigger an exception. Passing 1583 undefined is the same as not passing a value. It is legal 1584 to, e.g., fetch an integer value as a string, in which case 1585 sqlite3 will convert the value to a string. 1586 1587 If ndx is an array, this function behaves a differently: it 1588 assigns the indexes of the array, from 0 to the number of 1589 result columns, to the values of the corresponding column, 1590 and returns that array. 1591 1592 If ndx is a plain object, this function behaves even 1593 differentlier: it assigns the properties of the object to 1594 the values of their corresponding result columns. 1595 1596 Blobs are returned as Uint8Array instances. 1597 1598 Potential TODO: add type ID SQLITE_JSON, which fetches the 1599 result as a string and passes it (if it's not null) to 1600 JSON.parse(), returning the result of that. Until then, 1601 getJSON() can be used for that. 1602 */ 1603 get: function(ndx,asType){ 1604 if(!affirmStmtOpen(this)._mayGet){ 1605 toss3("Stmt.step() has not (recently) returned true."); 1606 } 1607 if(Array.isArray(ndx)){ 1608 let i = 0; 1609 while(i<this.columnCount){ 1610 ndx[i] = this.get(i++); 1611 } 1612 return ndx; 1613 }else if(ndx && 'object'===typeof ndx){ 1614 let i = 0; 1615 while(i<this.columnCount){ 1616 ndx[capi.sqlite3_column_name(this.pointer,i)] = this.get(i++); 1617 } 1618 return ndx; 1619 } 1620 affirmColIndex(this, ndx); 1621 switch(undefined===asType 1622 ? capi.sqlite3_column_type(this.pointer, ndx) 1623 : asType){ 1624 case capi.SQLITE_NULL: return null; 1625 case capi.SQLITE_INTEGER:{ 1626 if(wasm.bigIntEnabled){ 1627 const rc = capi.sqlite3_column_int64(this.pointer, ndx); 1628 if(rc>=Number.MIN_SAFE_INTEGER && rc<=Number.MAX_SAFE_INTEGER){ 1629 /* Coerce "normal" number ranges to normal number values, 1630 and only return BigInt-type values for numbers out of this 1631 range. */ 1632 return Number(rc).valueOf(); 1633 } 1634 return rc; 1635 }else{ 1636 const rc = capi.sqlite3_column_double(this.pointer, ndx); 1637 if(rc>Number.MAX_SAFE_INTEGER || rc<Number.MIN_SAFE_INTEGER){ 1638 /* Throwing here is arguable but, since we're explicitly 1639 extracting an SQLITE_INTEGER-type value, it seems fair to throw 1640 if the extracted number is out of range for that type. 1641 This policy may be laxened to simply pass on the number and 1642 hope for the best, as the C API would do. */ 1643 toss3("Integer is out of range for JS integer range: "+rc); 1644 } 1645 //console.log("get integer rc=",rc,isInt32(rc)); 1646 return util.isInt32(rc) ? (rc | 0) : rc; 1647 } 1648 } 1649 case capi.SQLITE_FLOAT: 1650 return capi.sqlite3_column_double(this.pointer, ndx); 1651 case capi.SQLITE_TEXT: 1652 return capi.sqlite3_column_text(this.pointer, ndx); 1653 case capi.SQLITE_BLOB: { 1654 const n = capi.sqlite3_column_bytes(this.pointer, ndx), 1655 ptr = capi.sqlite3_column_blob(this.pointer, ndx), 1656 rc = new Uint8Array(n); 1657 //heap = n ? wasm.heap8() : false; 1658 if(n) rc.set(wasm.heap8u().slice(ptr, ptr+n), 0); 1659 //for(let i = 0; i < n; ++i) rc[i] = heap[ptr + i]; 1660 if(n && this.db._blobXfer instanceof Array){ 1661 /* This is an optimization soley for the 1662 Worker-based API. These values will be 1663 transfered to the main thread directly 1664 instead of being copied. */ 1665 this.db._blobXfer.push(rc.buffer); 1666 } 1667 return rc; 1668 } 1669 default: toss3("Don't know how to translate", 1670 "type of result column #"+ndx+"."); 1671 } 1672 toss3("Not reached."); 1673 }, 1674 /** Equivalent to get(ndx) but coerces the result to an 1675 integer. */ 1676 getInt: function(ndx){return this.get(ndx,capi.SQLITE_INTEGER)}, 1677 /** Equivalent to get(ndx) but coerces the result to a 1678 float. */ 1679 getFloat: function(ndx){return this.get(ndx,capi.SQLITE_FLOAT)}, 1680 /** Equivalent to get(ndx) but coerces the result to a 1681 string. */ 1682 getString: function(ndx){return this.get(ndx,capi.SQLITE_TEXT)}, 1683 /** Equivalent to get(ndx) but coerces the result to a 1684 Uint8Array. */ 1685 getBlob: function(ndx){return this.get(ndx,capi.SQLITE_BLOB)}, 1686 /** 1687 A convenience wrapper around get() which fetches the value 1688 as a string and then, if it is not null, passes it to 1689 JSON.parse(), returning that result. Throws if parsing 1690 fails. If the result is null, null is returned. An empty 1691 string, on the other hand, will trigger an exception. 1692 */ 1693 getJSON: function(ndx){ 1694 const s = this.get(ndx, capi.SQLITE_STRING); 1695 return null===s ? s : JSON.parse(s); 1696 }, 1697 // Design note: the only reason most of these getters have a 'get' 1698 // prefix is for consistency with getVALUE_TYPE(). The latter 1699 // arguably really need that prefix for API readability and the 1700 // rest arguably don't, but consistency is a powerful thing. 1701 /** 1702 Returns the result column name of the given index, or 1703 throws if index is out of bounds or this statement has been 1704 finalized. This can be used without having run step() 1705 first. 1706 */ 1707 getColumnName: function(ndx){ 1708 return capi.sqlite3_column_name( 1709 affirmColIndex(affirmStmtOpen(this),ndx).pointer, ndx 1710 ); 1711 }, 1712 /** 1713 If this statement potentially has result columns, this 1714 function returns an array of all such names. If passed an 1715 array, it is used as the target and all names are appended 1716 to it. Returns the target array. Throws if this statement 1717 cannot have result columns. This object's columnCount member 1718 holds the number of columns. 1719 */ 1720 getColumnNames: function(tgt=[]){ 1721 affirmColIndex(affirmStmtOpen(this),0); 1722 for(let i = 0; i < this.columnCount; ++i){ 1723 tgt.push(capi.sqlite3_column_name(this.pointer, i)); 1724 } 1725 return tgt; 1726 }, 1727 /** 1728 If this statement has named bindable parameters and the 1729 given name matches one, its 1-based bind index is 1730 returned. If no match is found, 0 is returned. If it has no 1731 bindable parameters, the undefined value is returned. 1732 */ 1733 getParamIndex: function(name){ 1734 return (affirmStmtOpen(this).parameterCount 1735 ? capi.sqlite3_bind_parameter_index(this.pointer, name) 1736 : undefined); 1737 } 1738 }/*Stmt.prototype*/; 1739 1740 {/* Add the `pointer` property to DB and Stmt. */ 1741 const prop = { 1742 enumerable: true, 1743 get: function(){return __ptrMap.get(this)}, 1744 set: ()=>toss3("The pointer property is read-only.") 1745 } 1746 Object.defineProperty(Stmt.prototype, 'pointer', prop); 1747 Object.defineProperty(DB.prototype, 'pointer', prop); 1748 } 1749 1750 /** The OO API's public namespace. */ 1751 sqlite3.oo1 = { 1752 version: { 1753 lib: capi.sqlite3_libversion(), 1754 ooApi: "0.1" 1755 }, 1756 DB, 1757 Stmt 1758 }/*oo1 object*/; 1759 1760 if(util.isUIThread()){ 1761 /** 1762 Functionally equivalent to DB(storageName,'c','kvvfs') except 1763 that it throws if the given storage name is not one of 'local' 1764 or 'session'. 1765 */ 1766 sqlite3.oo1.JsStorageDb = function(storageName='session'){ 1767 if('session'!==storageName && 'local'!==storageName){ 1768 toss3("JsStorageDb db name must be one of 'session' or 'local'."); 1769 } 1770 dbCtorHelper.call(this, { 1771 filename: storageName, 1772 flags: 'c', 1773 vfs: "kvvfs" 1774 }); 1775 }; 1776 const jdb = sqlite3.oo1.JsStorageDb; 1777 jdb.prototype = Object.create(DB.prototype); 1778 /** Equivalent to sqlite3_js_kvvfs_clear(). */ 1779 jdb.clearStorage = capi.sqlite3_js_kvvfs_clear; 1780 /** 1781 Clears this database instance's storage or throws if this 1782 instance has been closed. Returns the number of 1783 database blocks which were cleaned up. 1784 */ 1785 jdb.prototype.clearStorage = function(){ 1786 return jdb.clearStorage(affirmDbOpen(this).filename); 1787 }; 1788 /** Equivalent to sqlite3_js_kvvfs_size(). */ 1789 jdb.storageSize = capi.sqlite3_js_kvvfs_size; 1790 /** 1791 Returns the _approximate_ number of bytes this database takes 1792 up in its storage or throws if this instance has been closed. 1793 */ 1794 jdb.prototype.storageSize = function(){ 1795 return jdb.storageSize(affirmDbOpen(this).filename); 1796 }; 1797 }/*main-window-only bits*/ 1798 1799}); 1800 1801