diff options
| -rw-r--r-- | ext/fiddle/sqlite3-api.js | 256 | ||||
| -rw-r--r-- | ext/fiddle/testing1.js | 16 | ||||
| -rw-r--r-- | ext/fiddle/testing2.js | 137 | ||||
| -rw-r--r-- | manifest | 16 | ||||
| -rw-r--r-- | manifest.uuid | 2 |
5 files changed, 356 insertions, 71 deletions
diff --git a/ext/fiddle/sqlite3-api.js b/ext/fiddle/sqlite3-api.js index e625afc9b..853f16e07 100644 --- a/ext/fiddle/sqlite3-api.js +++ b/ext/fiddle/sqlite3-api.js @@ -271,33 +271,55 @@ Module.postRun.push(function(namespace/*the module object, the target for - () - (undefined) (same effect as ()) - - (Uint8Array holding an sqlite3 db image) - - It always generates a random filename and sets is to - the `filename` property of this object. - - Developer's note: the reason it does not (any longer) support - ":memory:" as a name is because we can apparently only export - images of DBs which are stored in the pseudo-filesystem - provided by the JS APIs. Since exporting and importing images - is an important usability feature for this class, ":memory:" - DBs are not supported (until/unless we can find a way to export - those as well). The naming semantics will certainly evolve as - this API does. + - (filename[,buffer]) + - (buffer) + + Where a buffer indicates a Uint8Array holding an sqlite3 db + image. + + If the filename is provided, only the last component of the + path is used - any path prefix is stripped. If no name is + provided, a random name is generated. The resulting filename is + the one used for accessing the db file within root directory of + the emscripten-supplied virtual filesystem, and is set (with no + path part) as the DB object's `filename` property. + + Note that the special sqlite3 db names ":memory:" and "" + (temporary db) have no special meanings here. We can apparently + only export images of DBs which are stored in the + pseudo-filesystem provided by the JS APIs. Since exporting and + importing images is an important usability feature for this + class, ":memory:" DBs are not supported (until/unless we can + find a way to export those as well). The naming semantics will + certainly evolve as this API does. */ const DB = function(arg){ - const fn = "db-"+((Math.random() * 10000000) | 0)+ - "-"+((Math.random() * 10000000) | 0)+".sqlite3"; - let buffer; - if(name instanceof Uint8Array){ + let buffer, fn; + if(arg instanceof Uint8Array){ buffer = arg; arg = undefined; - }else if(arguments.length && undefined!==arg){ - toss("Invalid arguments to DB constructor.", - "Expecting no args, undefined, or a", - "sqlite3 file as a Uint8Array."); + }else if(arguments.length){ /*(filename[,buffer])*/ + if('string'===typeof arg){ + const p = arg.split('/').pop().replace(':',''); + if(p) fn = p; + if(arguments.length>1){ + buffer = arguments[1]; + } + }else if(undefined!==arg){ + toss("Invalid arguments to DB constructor.", + "Expecting (), (undefined), (name,buffer),", + "or (buffer), where buffer an sqlite3 db ", + "as a Uint8Array."); + } + } + if(!fn){ + fn = "db-"+((Math.random() * 10000000) | 0)+ + "-"+((Math.random() * 10000000) | 0)+".sqlite3"; } if(buffer){ + if(!(buffer instanceof Uint8Array)){ + toss("Expecting Uint8Array image of db contents."); + } FS.createDataFile("/", fn, buffer, true, true); } setValue(pPtrArg, 0, "i32"); @@ -382,7 +404,7 @@ Module.postRun.push(function(namespace/*the module object, the target for default: toss("Invalid argument count for exec()."); }; if('string'!==typeof out.sql) toss("Missing SQL argument."); - if(out.opt.callback){ + if(out.opt.callback || out.opt.resultRows){ switch((undefined===out.opt.rowMode) ? 'stmt' : out.opt.rowMode) { case 'object': out.cbArg = (stmt)=>stmt.get({}); break; @@ -462,16 +484,37 @@ Module.postRun.push(function(namespace/*the module object, the target for return stmt; }, /** - This function works like execMulti(), and takes the same - arguments, but is more efficient (performs much less work) - when the input SQL is only a single statement. If passed a - multi-statement SQL, it only processes the first one. + This function works like execMulti(), and takes most of the + same arguments, but is more efficient (performs much less + work) when the input SQL is only a single statement. If + passed a multi-statement SQL, it only processes the first + one. - This function supports one additional option not used by - execMulti(): + This function supports the following additional options not + used by execMulti(): - .multi: if true, this function acts as a proxy for execMulti() and behaves identically to that function. + + - .resultRows: if this is an array, each row of the result + set (if any) is appended to it in the format specified + for the `rowMode` property, with the exception that the + `rowMode` property _must_ be one of 'array' or 'object' + if this is set (else an exception is throws). It is legal + to use both `resultRows` and `callback`, but `resultRows` + is likely much simpler to use for small data sets. + + - .columnNames: if this is an array and the query has + result columns, the array is passed to + Stmt.getColumnNames() to append the column names to it + (regardless of whether the query produces any result + rows). If the query has no result columns, this value is + unchanged. + + The following options to execMulti() are _not_ supported by + this method (they are simply ignored): + + - .saveSql */ exec: function(/*(sql [,optionsObj]) or (optionsObj)*/){ affirmDbOpen(this); @@ -481,15 +524,29 @@ Module.postRun.push(function(namespace/*the module object, the target for return this.execMulti(arg, undefined, BindTypes); } const opt = arg.opt; - let stmt; + let stmt, rowTarget; try { + if(Array.isArray(opt.resultRows)){ + if(opt.rowMode!=='array' && opt.rowMode!=='object'){ + throw new Error("Invalid rowMode for resultRows array: must "+ + "be one of 'array' or 'object'."); + } + rowTarget = opt.resultRows; + } stmt = this.prepare(arg.sql); + if(stmt.columnCount && Array.isArray(opt.columnNames)){ + stmt.getColumnNames(opt.columnNames); + } if(opt.bind) stmt.bind(opt.bind); - if(opt.callback){ + if(opt.callback || rowTarget){ while(stmt.step()){ - stmt._isLocked = true; - opt.callback(arg.cbArg(stmt), stmt); - stmt._isLocked = false; + const row = arg.cbArg(stmt); + if(rowTarget) rowTarget.push(row); + if(opt.callback){ + stmt._isLocked = true; + opt.callback(row, stmt); + stmt._isLocked = false; + } } }else{ stmt.step(); @@ -504,10 +561,11 @@ Module.postRun.push(function(namespace/*the module object, the target for }/*exec()*/, /** - Executes one or more SQL statements. Its arguments - must be either (sql,optionsObject) or (optionsObject). - In the latter case, optionsObject.sql must contain the - SQL to execute. Returns this object. Throws on error. + Executes one or more SQL statements in the form of a single + string. Its arguments must be either (sql,optionsObject) or + (optionsObject). In the latter case, optionsObject.sql + must contain the SQL to execute. Returns this + object. Throws on error. If no SQL is provided, or a non-string is provided, an exception is triggered. Empty SQL, on the other hand, is @@ -548,6 +606,9 @@ Module.postRun.push(function(namespace/*the module object, the target for don't have the string until after that). Empty SQL statements are elided. + See also the exec() method, which is a close cousin of this + one. + ACHTUNG #1: The callback MUST NOT modify the Stmt object. Calling any of the Stmt.get() variants, Stmt.getColumnName(), or similar, is legal, but calling @@ -806,8 +867,13 @@ Module.postRun.push(function(namespace/*the module object, the target for /** Exports a copy of this db's file as a Uint8Array and returns it. It is technically not legal to call this while - any prepared statement are currently active. Throws if this - db is not open. + any prepared statement are currently active because, + depending on the platform, it might not be legal to read + the db while a statement is locking it. Throws if this db + is not open or has any opened statements. + + The resulting buffer can be passed to this class's + constructor to restore the DB. Maintenance reminder: the corresponding sql.js impl of this feature closes the current db, finalizing any active @@ -825,8 +891,7 @@ Module.postRun.push(function(namespace/*the module object, the target for toss("Cannot export with prepared statements active!", "finalize() all statements and try again."); } - const img = FS.readFile(this.filename, {encoding:"binary"}); - return img; + return FS.readFile(this.filename, {encoding:"binary"}); } }/*DB.prototype*/; @@ -1397,8 +1462,13 @@ Module.postRun.push(function(namespace/*the module object, the target for SQLite3 }; + const postApiLoaded = function(){ + setTimeout(()=>postMessage({type:'sqlite3-api',data:'loaded'}), 0); + }; + if(self === self.window){ /* This is running in the main window thread, so we're done. */ + postApiLoaded(); return; } /****************************************************************** @@ -1407,24 +1477,108 @@ Module.postRun.push(function(namespace/*the module object, the target for ******************************************************************/ /* - TODO: we need an API which can proxy the DB API via a Worker - message interface. The primary quirky factor in such an API is - that clients cannot pass callback functions to it, so have to receive - all query results via asynchronous message-passing. + UNDER CONSTRUCTION + + We need an API which can proxy the DB API via a Worker message + interface. The primary quirky factor in such an API is that we + cannot pass callback functions between the window thread and a + worker thread, so we have to receive all db results via + asynchronous message-passing. Certain important considerations here include: - - Support only one db connectior or multiple? The former is far + - Support only one db connection or multiple? The former is far easier, but there's always going to be a user out there who - wants to juggle six database handles at once. + wants to juggle six database handles at once. Do we add that + complexity or tell such users to write their own code using + the provided lower-level APIs? - Fetching multiple results: do we pass them on as a series of messages, with start/end messages on either end, or do we collect all results and bundle them back in a single message? The former is, generically speaking, more memory-efficient but - the latter far easier to implement in this environment. - */ - + the latter far easier to implement in this environment. The + latter is untennable for large data sets. Despite a web page + hypothetically being a relatively limited environment, there + will always be those users who feel that they should/need to + be able to work with multi-hundred-meg (or larger) blobs, and + passing around arrays of those may quickly exhaust the JS + engine's memory. + */ + + const wState = { + db: undefined, + open: function(arg){ + if(!arg && this.db) return this.db; + else if(this.db) this.db.close(); + return this.db = (Array.isArray(arg) ? new DB(...arg) : new DB(arg)); + } + }; + const wMsg = (type,data)=>self.postMessage({type, data}); + + /** + UNDER CONSTRUCTION: + + A subset of the DB API is accessible via Worker messages in the form: + + { type: apiCommand, + data: apiArguments } + + As a rule, these commands respond with a postMessage() of their + own in the same form, but will, if needed, transform the `data` + member to an object and may add state to it. The responses + always have an object-format `data` part. If the inbound `data` + is an object which has a `messageId` property, that property is + always mirrored in the result object, for use in client-side + dispatching of these asynchronous results. Exceptions thrown + during processing result in an `error`-type event with a + payload in the form: + + {message: error string, + errorClass: class name of the error type, + [, messageId: if set in the inbound message]} + + The individual APIs will be documented as they are fleshed out. + */ + self.onmessage = function(ev){ + ev = ev.data; + let response = {}, evType = ev.type; + try { + switch(evType){ + case 'open': { + const args = [], data = (ev.data || {}); + if(data.filename) args.push(data.filename); + if(data.buffer) args.push(data.buffer); + const d = wState.open(args); + response.filename = d.filename; + response.messageId = data.messageId; + break; + } + case 'exec': { + const opt = ( + 'string'===typeof ev.data + ) ? {sql: ev.data} : (ev.data || {}); + if(!opt.rowMode) opt.rowMode = 'array'; + wState.open().exec(opt); + response = opt; + break; + } + default: + throw new Error("Unknown db worker message type: "+ev.type); + } + }catch(err){ + evType = 'error'; + response = { + message: err.message, + errorClass: err.name + }; + } + if(!response.messageId && ev.data + && 'object'===typeof ev.data && ev.data.messageId){ + response.messageId = ev.data.messageId; + } + wMsg(evType, response); + }; - setTimeout(()=>postMessage({type:'sqlite3-api',data:'loaded'}), 0); + postApiLoaded(); }); diff --git a/ext/fiddle/testing1.js b/ext/fiddle/testing1.js index e4afad84e..46af2c926 100644 --- a/ext/fiddle/testing1.js +++ b/ext/fiddle/testing1.js @@ -71,23 +71,27 @@ INSERT INTO t(a,b) VALUES(1,2),(3,4),(?,?);`, T.assert(2 === list.length); //log("Exec'd SQL:", list); let counter = 0, colNames = []; + list.length = 0; db.exec("SELECT a a, b b FROM t",{ rowMode: 'object', + resultRows: list, + columnNames: colNames, callback: function(row,stmt){ - if(!counter) stmt.getColumnNames(colNames); ++counter; T.assert(row.a%2 && row.a<6); } }); - assert(2 === colNames.length); - assert('a' === colNames[0]); - T.assert(3 === counter); + T.assert(2 === colNames.length) + .assert('a' === colNames[0]) + .assert(3 === counter) + .assert(3 === list.length); + list.length = 0; db.exec("SELECT a a, b b FROM t",{ rowMode: 'array', callback: function(row,stmt){ ++counter; - assert(Array.isArray(row)); - T.assert(0===row[1]%2 && row[1]<7); + T.assert(Array.isArray(row)) + .assert(0===row[1]%2 && row[1]<7); } }); T.assert(6 === counter); diff --git a/ext/fiddle/testing2.js b/ext/fiddle/testing2.js index c25885425..de9c92564 100644 --- a/ext/fiddle/testing2.js +++ b/ext/fiddle/testing2.js @@ -13,17 +13,132 @@ A basic test script for sqlite3-worker.js. */ (function(){ - /** Posts a worker message as {type:type, data:data}. */ + const T = self.SqliteTestUtil; const SW = new Worker("sqlite3-worker.js"); - const wMsg = (type,data)=>SW.postMessage({type, data}); + /** Posts a worker message as {type:type, data:data}. */ + const wMsg = function(type,data){ + SW.postMessage({type, data}); + return SW; + }; const log = console.log.bind(console); const warn = console.warn.bind(console); + const error = console.error.bind(console); + + SW.onerror = function(event){ + warn("onerror",event); + }; + + const MsgHandlerQueue = { + queue: [], + id: 0, + push: function(type,f){ + this.queue.push(f); + return type + '-' + (++this.id); + }, + shift: function(){ + return this.queue.shift(); + } + }; + + const runOneTest = function(eventType, eventData, callback){ + T.assert(eventData && 'object'===typeof eventData); + eventData.messageId = MsgHandlerQueue.push(eventType,function(ev){ + log("runOneTest",eventType,"result",ev.data); + callback(ev); + }); + wMsg(eventType, eventData); + }; + + const testCount = ()=>log("Total test count:",T.counter); + + const runTests = function(){ + /** + "The problem" now is that the test results are async. We + know, however, that the messages posted to the worker will + be processed in the order they are passed to it, so we can + create a queue of callbacks to handle them. The problem + with that approach is that it's not error-handling + friendly, in that an error can cause us to bypass a result + handler queue entry. We have to perform some extra + acrobatics to account for that. + */ + runOneTest('open', {filename:'testing2.sqlite3'}, function(ev){ + //log("open result",ev); + T.assert('testing2.sqlite3'===ev.data.filename) + .assert(ev.data.messageId); + testCount(); + }); + runOneTest('exec',{ + sql: ["create table t(a,b)", + "insert into t(a,b) values(1,2),(3,4),(5,6)" + ].join(';'), + multi: true, + resultRows: [], + columnNames: [] + }, function(ev){ + ev = ev.data; + T.assert(0===ev.resultRows.length) + .assert(0===ev.columnNames.length); + testCount(); + }); + runOneTest('exec',{ + sql: 'select a a, b b from t order by a', + resultRows: [], columnNames: [] + }, function(ev){ + ev = ev.data; + T.assert(3===ev.resultRows.length) + .assert(1===ev.resultRows[0][0]) + .assert(6===ev.resultRows[2][1]) + .assert(2===ev.columnNames.length) + .assert('b'===ev.columnNames[1]); + testCount(); + }); + runOneTest('exec',{sql:'select 1 from intentional_error'}, function(){ + throw new Error("This is not supposed to be reached."); + }); + // Ensure that the message-handler queue survives ^^^ that error... + runOneTest('exec',{ + sql:'select 1', + resultRows: [], + rowMode: 'array', + }, function(ev){ + ev = ev.data; + T.assert(1 === ev.resultRows.length) + .assert(1 === ev.resultRows[0][0]); + testCount(); + }); + }; + + const dbMsgHandler = { + open: function(ev){ + log("open result",ev.data); + }, + exec: function(ev){ + log("exec result",ev.data); + }, + error: function(ev){ + error("ERROR from the worker:",ev.data); + } + }; + SW.onmessage = function(ev){ if(!ev.data || 'object'!==typeof ev.data){ warn("Unknown sqlite3-worker message type:",ev); return; } ev = ev.data/*expecting a nested object*/; + //log("main window onmessage:",ev); + if(ev.data && ev.data.messageId){ + /* We're expecting a queued-up callback handler. */ + const f = MsgHandlerQueue.shift(); + if('error'===ev.type){ + dbMsgHandler.error(ev); + return; + } + T.assert(f instanceof Function); + f(ev); + return; + } switch(ev.type){ case 'sqlite3-api': switch(ev.data){ @@ -32,12 +147,24 @@ case 'ready': log("Message:",ev); self.sqlite3TestModule.setStatus(null); + setTimeout(runTests, 0); + return; + default: + warn("Unknown sqlite3-api message type:",ev); return; - default: break; } - break; + default: + if(dbMsgHandler.hasOwnProperty(ev.type)){ + try{dbMsgHandler[ev.type](ev);} + catch(err){ + error("Exception while handling db result message", + ev,":",err); + } + return; + } + warn("Unknown sqlite3-api message type:",ev); } - warn("Unknown sqlite3-api message type:",ev); }; + log("Init complete, but async bits may still be running."); })(); @@ -1,5 +1,5 @@ -C Initial\sbits\sfor\sa\sJS\sAPI\svariant\sin\swhich\sthe\sclient\soperates\sin\sthe\smain\sthread\sand\ssqlite3\sin\sa\sWorker.\sThis\sis\sfar\sfrom\scomplete. -D 2022-06-01T00:00:59.491 +C Initial\sproof\sof\sconcept\scode\sfor\sa\sJavaScript\sbinding\swhich\sruns\sin\sthe\smain\swindow\sthread\sbut\sacts\son\sa\sdb\shandle\srunning\sin\sa\sWorker\sthread.\sExpanded\sthe\sDB.exec()\sand\sDB()\sconstructor\sto\ssimplify\scertain\suse\scases. +D 2022-06-01T08:09:06.176 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -65,13 +65,13 @@ F ext/fiddle/fiddle-worker.js 3a19253dc026d1ad9064ee853f3c4da3385223ce4434dab183 F ext/fiddle/fiddle.html 724f1cd4126616bc87f5871f78d3f7aaaf41e45c9728724627baab87e6af35f0 F ext/fiddle/fiddle.js 5b456ed7085830cda2fc75a0801476174a978521949335f24bc4154d076dcd4d F ext/fiddle/index.md d9c1c308d8074341bc3b11d1d39073cd77754cb3ca9aeb949f23fdd8323d81cf -F ext/fiddle/sqlite3-api.js ff9580cf075c08bd124ad057079bd32fd121f1e122c8c40e3a836466c1fe1197 +F ext/fiddle/sqlite3-api.js a2c0fa1a30e564a16650e3224a23a60cc8636325028223183933630669ebec8d F ext/fiddle/sqlite3-worker.js c137daed6529b5f527ed61eb358cb0d23f90e04784442479cd15ac684eccdf7a F ext/fiddle/testing.css 750572dded671d2cf142bbcb27af5542522ac08db128245d0b9fe410aa1d7f2a F ext/fiddle/testing1.html ea1f3be727f78e420007f823912c1a03b337ecbb8e79449abc2244ad4fe15d9a -F ext/fiddle/testing1.js e09c224da12b34bd90a1128d4a9cfc546ccca00074d00571977eb44746bf487d +F ext/fiddle/testing1.js b5bf7e33b35f02f4208e4d68eaa41e5ed42eaefd57e0a1131e87cba96d4808dc F ext/fiddle/testing2.html 9063b2430ade2fe9da4e711addd1b51a2741cf0c7ebf6926472a5e5dd63c0bc4 -F ext/fiddle/testing2.js 0382f20c6c5e1b2a034240a29d627f0e6ccb9c3be50d7dd1627b04ad5f92b787 +F ext/fiddle/testing2.js 15e53ded82e78a5360daa4af109124c81b52eba79be2de241bef6558697931b7 F ext/fts1/README.txt 20ac73b006a70bcfd80069bdaf59214b6cf1db5e F ext/fts1/ft_hash.c 3927bd880e65329bdc6f506555b228b28924921b F ext/fts1/ft_hash.h 06df7bba40dadd19597aa400a875dbc2fed705ea @@ -1974,8 +1974,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 13e89ef6649475815d3f4e4aef73a4be1157dd388e55c7f856faeb4b7387774b -R 1d9b814402b0908178d5c21bd5502d71 +P f6d6f969791f0d2367ae5418623b4794f6df657d9d7d9002fb5aec4206dcfd4c +R 3e7544f3d688eef8add8c5291273a264 U stephan -Z f7d167c241a574fa46d16e96c1270f84 +Z fea18e61d8da4eacf9dfc674d92c9514 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index d352df1b8..7d9aef22f 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -f6d6f969791f0d2367ae5418623b4794f6df657d9d7d9002fb5aec4206dcfd4c
\ No newline at end of file +d9efe3e92d1c95aee6f5ae37a8ba28d8cf4891d746744ce4aa2464f766821a0b
\ No newline at end of file |