sql.js Adding the series extension

Hello 👋

I'm trying to add the generate_series function from the series extension to the SQLite build.

At the moment, here is what I have done so far:

modify the Makefile so it downloads the series.c file automatically and puts it in the sql-amalgation folder.
modify the Makefile so it builds the series.c file and add it to the wasm output.
include the _sqlite3_series_init function to the src/exported_functions.json file
(this is where it errors) modify the src/api.js file so it calls the sqlite3_series_init function

I read #459 and it seems like we had to do this...?

when running the tests, I'm getting the following error:

  ⚡ RuntimeError: function signature mismatch
        at sqlite3_series_init (<anonymous>:wasm-function[2577]:0x82a1e)
        at Module._sqlite3_series_init (/workspaces/sql.js/dist/sql-wasm.js:6140:105)
        at new Database (/workspaces/sql.js/dist/sql-wasm.js:937:22)
        at Object.exports.test (/workspaces/sql.js/test/test_generate_series.js:4:14)
        at test generate series (/workspaces/sql.js/test/test_generate_series.js:22:17)
        at test (/workspaces/sql.js/node_modules/test/test.js:29:20)
        at next (/workspaces/sql.js/node_modules/test/test.js:69:7)
        at suite (/workspaces/sql.js/node_modules/test/test.js:71:5)
        at Object.run (/workspaces/sql.js/node_modules/test/test.js:87:3)
        at /workspaces/sql.js/test/test_generate_series.js:20:21

Here is my fork with all the modifications I have made: https://github.com/Androz2091/sql.js

Can someone help me to understand? Maybe @twoxfh, and @rhashimoto, who solved the issue #459?

Jun 21 '23 19:06 Androz2091

I'm also interested if you just explain to me how to add the generate_series without using the method I followed above... of course. Thank you!

Jun 21 '23 20:06 Androz2091

This project has been mostly dormant for a few years, probably in part because there is now a SQLite browser API under the official SQLite umbrella. That might be a more forward-looking path for you. I have no idea how easy or difficult it is to add extensions, but the developer is pretty responsive on the SQLite forum.

Jun 21 '23 20:06 rhashimoto

My guess is your problem is here. For some reason you're not compiling your extension with the same compilation flags used everywhere else.

Jun 21 '23 21:06 rhashimoto

My guess is your problem is here. For some reason you're not compiling your extension with the same compilation flags used everywhere else.

You are correct, this is my exact problem. I was not using SQLITE_COMPILATION_FLAGS... I could have gone hours without seeing it... thank you!

I am also going to have a look at umbrella. We have already built almost all our project with SQL.js so we can not really redo everything at the moment but this is definitely something I will have a look at!

Jun 22 '23 07:06 Androz2091

Sorry @rhashimoto, the build works well on Node.js, but as soon as it's opened on a web browser, I'm getting this error:

Uncaught (in promise) RuntimeError: Aborted(TypeError: WebAssembly.instantiate(): Import #0 module="env" error: module is not an object or function). Build with -sASSERTIONS for more info.

HTML Code to reproduce the issue in 30 seconds:

<meta charset="utf8" />
<html>
  <script src='https://sql.js.org/dist/sql-wasm.js'></script>
  <script>
    config = {
      locateFile: filename => `https://raw.githubusercontent.com/dumpus-app/sql.js/master/dist/${filename}`
    }
    // The `initSqlJs` function is globally provided by all of the main dist files if loaded in the browser.
    // We must specify this locateFile function if we are loading a wasm file from anywhere other than the current html page's folder.
    initSqlJs(config).then(function(SQL){
      //Create the database
      const db = new SQL.Database();
      // Run a query without reading the results
      db.run("CREATE TABLE test (col1, col2);");
      // Insert two rows: (1,111) and (2,222)
      db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]);

      // Prepare a statement
      const stmt = db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");
      stmt.getAsObject({$start:1, $end:1}); // {col1:1, col2:111}

      // Bind new values
      stmt.bind({$start:1, $end:2});
      while(stmt.step()) { //
        const row = stmt.getAsObject();
        console.log('Here is a row: ' + JSON.stringify(row));
      }
    });
  </script>
  <body>
    Output is in Javascript console
  </body>
</html>

All our code is still on our repo https://github.com/dumpus-app/sql.js/tree/master

thank you so much :pray:

Jun 22 '23 09:06 Androz2091

Do you have a idea?

Jun 22 '23 09:06 Androz2091