ext/fiddle/testing1.js

   1 /*
   2   2022-05-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   A basic test script for sqlite3-api.js. This file must be run in
  14   main JS thread and sqlite3.js must have been loaded before it.
  15 */
  16 (function(){
  17     const T = self.SqliteTestUtil;
  18     const log = console.log.bind(console);
  19
  20     const assert = function(condition, text) {
  21         if (!condition) {
  22             throw new Error('Assertion failed' + (text ? ': ' + text : ''));
  23         }
  24     };
  25
  26     const test1 = function(db,sqlite3){
  27         const api = sqlite3.api;
  28         log("Basic sanity tests...");
  29         T.assert(db._pDb);
  30         let st = db.prepare("select 3 as a");
  31         //log("statement =",st);
  32         T.assert(st._pStmt)
  33             .assert(!st._mayGet)
  34             .assert('a' === st.getColumnName(0))
  35             .assert(st === db._statements[st._pStmt])
  36             .assert(1===st.columnCount)
  37             .assert(0===st.parameterCount)
  38             .mustThrow(()=>st.bind(1,null))
  39             .assert(true===st.step())
  40             .assert(3 === st.get(0))
  41             .mustThrow(()=>st.get(1))
  42             .mustThrow(()=>st.get(0,~api.SQLITE_INTEGER))
  43             .assert(3 === st.get(0,api.SQLITE_INTEGER))
  44             .assert(3 === st.getInt(0))
  45             .assert('3' === st.get(0,api.SQLITE_TEXT))
  46             .assert('3' === st.getString(0))
  47             .assert(3.0 === st.get(0,api.SQLITE_FLOAT))
  48             .assert(3.0 === st.getFloat(0))
  49             .assert(st.get(0,api.SQLITE_BLOB) instanceof Uint8Array)
  50             .assert(st.getBlob(0) instanceof Uint8Array)
  51             .assert(3 === st.get([])[0])
  52             .assert(3 === st.get({}).a)
  53             .assert(3 === st.getJSON(0))
  54             .assert(st._mayGet)
  55             .assert(false===st.step())
  56             .assert(!st._mayGet)
  57         ;
  58         let pId = st._pStmt;
  59         st.finalize();
  60         T.assert(!st._pStmt)
  61             .assert(!db._statements[pId]);
  62
  63         let list = [];
  64         db.exec({
  65             sql:`CREATE TABLE t(a,b);
  66 INSERT INTO t(a,b) VALUES(1,2),(3,4),(?,?);`,
  67             multi: true,
  68             saveSql: list,
  69             bind: [5,6]
  70         });
  71         T.assert(2 === list.length);
  72         //log("Exec'd SQL:", list);
  73         let counter = 0, colNames = [];
  74         list.length = 0;
  75         db.exec("SELECT a a, b b FROM t",{
  76             rowMode: 'object',
  77             resultRows: list,
  78             columnNames: colNames,
  79             callback: function(row,stmt){
  80                 ++counter;
  81                 T.assert(row.a%2 && row.a<6);
  82             }
  83         });
  84         T.assert(2 === colNames.length)
  85             .assert('a' === colNames[0])
  86             .assert(3 === counter)
  87             .assert(3 === list.length);
  88         list.length = 0;
  89         db.exec("SELECT a a, b b FROM t",{
  90             rowMode: 'array',
  91             callback: function(row,stmt){
  92                 ++counter;
  93                 T.assert(Array.isArray(row))
  94                     .assert(0===row[1]%2 && row[1]<7);
  95             }
  96         });
  97         T.assert(6 === counter);
  98     };
  99
 100     const testUDF = function(db){
 101         log("Testing UDF...");
 102         db.createFunction("foo",function(a,b){return a+b});
 103         T.assert(7===db.selectValue("select foo(3,4)")).
 104             assert(5===db.selectValue("select foo(3,?)",2)).
 105             assert(5===db.selectValue("select foo(?,?)",[1,4])).
 106             assert(5===db.selectValue("select foo($a,$b)",{$a:0,$b:5}));
 107         db.createFunction("bar", {
 108             arity: -1,
 109             callback: function(){
 110                 var rc = 0;
 111                 for(let i = 0; i < arguments.length; ++i) rc += arguments[i];
 112                 return rc;
 113             }
 114         });
 115
 116         log("Testing DB::selectValue() w/ UDF...");
 117         T.assert(0===db.selectValue("select bar()")).
 118             assert(1===db.selectValue("select bar(1)")).
 119             assert(3===db.selectValue("select bar(1,2)")).
 120             assert(-1===db.selectValue("select bar(1,2,-4)"));
 121
 122         const eqApprox = function(v1,v2,factor=0.05){
 123             return v1>=(v2-factor) && v1<=(v2+factor);
 124         };
 125
 126         T.assert('hi' === db.selectValue("select ?",'hi')).
 127             assert(null===db.selectValue("select null")).
 128             assert(null === db.selectValue("select ?",null)).
 129             assert(null === db.selectValue("select ?",[null])).
 130             assert(null === db.selectValue("select $a",{$a:null})).
 131             assert(eqApprox(3.1,db.selectValue("select 3.0 + 0.1")))
 132         ;
 133     };
 134
 135     const testAttach = function(db){
 136         log("Testing ATTACH...");
 137         db.exec({
 138             sql:[
 139                 "attach 'foo.db' as foo",
 140                 "create table foo.bar(a)",
 141                 "insert into foo.bar(a) values(1),(2),(3)"
 142             ].join(';'),
 143             multi: true
 144         });
 145         T.assert(2===db.selectValue('select a from foo.bar where a>1 order by a'));
 146         db.exec("detach foo");
 147         T.mustThrow(()=>db.exec("select * from foo.bar"));
 148     };
 149
 150     const runTests = function(Module){
 151         T.assert(Module._free instanceof Function).
 152             assert(Module.allocate instanceof Function).
 153             assert(Module.addFunction instanceof Function).
 154             assert(Module.removeFunction instanceof Function);
 155         const sqlite3 = Module.sqlite3;
 156         const api = sqlite3.api;
 157         const oo = sqlite3.SQLite3;
 158         console.log("Loaded module:",api.sqlite3_libversion(),
 159                     api.sqlite3_sourceid());
 160         log("Build options:",oo.compileOptionUsed());
 161         const db = new oo.DB();
 162         try {
 163             log("DB:",db.filename);
 164             [
 165                 test1, testUDF, testAttach
 166             ].forEach((f)=>{
 167                 const t = T.counter;
 168                 f(db, sqlite3);
 169                 log("Test count:",T.counter - t);
 170             });
 171         }finally{
 172             db.close();
 173         }
 174         log("Total Test count:",T.counter);
 175     };
 176
 177     initSqlite3Module(self.sqlite3TestModule).then(function(theModule){
 178         /** Use a timeout so that we are (hopefully) out from
 179             under the module init stack when our setup gets
 180             run. Just on principle, not because we _need_ to
 181             be. */
 182         //console.debug("theModule =",theModule);
 183         setTimeout(()=>runTests(theModule), 0);
 184     });
 185 })();