Add SQL language evaluation

[mcintyre94]

Is your feature request related to a problem? Please describe.
Nope, but it'd be sweet! I'd definitely be able to make use of this on my running dashboard, but "I want to do more wacky hacks" probably doesn't count as a problem :)

Describe the solution you'd like
We should add sql as a supported language! You'd be able to run sql queries on an in-memory database in the browser and view their output

Describe alternatives you've considered
Use sql.js directly: https://github.com/sql-js/sql.js/
Or see if this still works: https://pypi.org/project/pandasql/ - since we already have Python + Pandas

Additional context
I came across this awesome project: https://github.com/nalgeon/sqlime / http://sqlime.org
It uses sql.js which turns out to be sqlite -> WASM!
We probably don't want to implement things from sqlime like connecting to a live DB in the core library, but the simple case of database in browser memory + run queries on it + display results would be a nice addition.

[kennethcassel]

Love the idea :) sql.js looks cool and lots of folks would benefit from being able to run sql with this lib

[hatemhosny]

This might give you a starting point: https://github.com/live-codes/livecodes/blob/d9be82ae11b282c225af3056b45920c58536565e/src/livecodes/languages/lang-sql.ts

Demo: https://dev.livecodes.io/?template=sql

[shadab14meb346]

I can try to work on this.
Though it looks a bit challenging so hope, I will get some help from you all.

So the steps I can think of about it.
Part 1

1. Write a class SQLClient in the file RunWasmClient.ts.
2. This class SQLClient will have obliviously have one public method run. This method will take code as an argument.
3. The class SQLClient will have constructor which will have the implementation mentioned here in the code block. The logic can be broken down and maybe not everything needs to be in the constructor, not sure yet will see.

Part 2

1. Use the SQLClient and make a new page /sql in example-nextjs and implement the feature using the Monaco editor as it is done for the ts, python and matplotlib currently.

[mcintyre94]

That sounds great to me! I think you've definitely got the right idea :) I'd lean toward putting everything expensive into/called as part of the constructor as much as possible, mostly because it's much easier to make sure it only happens once there.

I think the biggest decision to make in Part 1 is probably which sql.js function to use to implement our run function. Have you had a chance to dig into their interface yet to have a look at that?

[shadab14meb346]

So all of the things which we will need to make it work I feel is in the below-mentioned code block reference.

So here's this is also a very useful example to achieve our goal.

We can run initSqlJs in the constructor.
Have a public method run which we will assign as this this.run = db.run in contructor
The file coming from dist can be hosted on CDN as well or we can have it locally, I am not sure about it yet.

A few points which I will have to go deeper into is.

1. How to manage this stmt.free
2. Is db.run or db.exec we will have to call for our public method run?

Thoughts plz?

const initSqlJs = require('sql.js');
// or if you are in a browser:
// const initSqlJs = window.initSqlJs;

const SQL = await initSqlJs({
  // Required to load the wasm binary asynchronously. Of course, you can host it wherever you want
  // You can omit locateFile completely when running in node
  locateFile: file => `https://sql.js.org/dist/${file}`
});

// Create a database
const db = new SQL.Database();
// NOTE: You can also use new SQL.Database(data) where
// data is an Uint8Array representing an SQLite database file


// Execute a single SQL string that contains multiple statements
let sqlstr = "CREATE TABLE hello (a int, b char); \
INSERT INTO hello VALUES (0, 'hello'); \
INSERT INTO hello VALUES (1, 'world');";
db.run(sqlstr); // Run the query without returning anything

// Prepare an sql statement
const stmt = db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval");

// Bind values to the parameters and fetch the results of the query
const result = stmt.getAsObject({':aval' : 1, ':bval' : 'world'});
console.log(result); // Will print {a:1, b:'world'}

// Bind other values
stmt.bind([0, 'hello']);
while (stmt.step()) console.log(stmt.get()); // Will print [0, 'hello']
// free the memory used by the statement
stmt.free();
// You can not use your statement anymore once it has been freed.
// But not freeing your statements causes memory leaks. You don't want that.

const res = db.exec("SELECT * FROM hello");
/*
[
  {columns:['a','b'], values:[[0,'hello'],[1,'world']]}
]
*/

[mcintyre94]

How to manage this stmt.free

Hmm I think for our use case I'd skip the prepared statements. Generally they'd be a must to protect from malicious input variables, but in our case the database is only in the user's browser and the entire input is untrusted. I don't think we can gain anything from using them.

Is db.run or db.exec we will have to call for our public method run?

Based on that usage example I'd go with exec because it looks like run doesn't return anything. For some inputs we won't care about the output (unless it's an error) eg. inserting data, but we'll definitely want to be able to display the result of a query too. The output format in that comment looks quite easy for us to render into a HTML table in the example page too.

[shadab14meb346]

I will start working on it then.

What do you all suggest about how do we load the wasm binary mentioned in the code block in this comment?

1. Using a CDN?
2. Or have it locally?

[mcintyre94]

I'd go with a CDN for now. We could consider moving it into the package though when we move to multiple packages (when we merge #92), so that the bundle size would only impact sql users. @kennethcassel Does that make sense to you?

[kennethcassel]

I'd go with a CDN for now. We could consider moving it into the package though when we move to multiple packages (when we merge #92), so that the bundle size would only impact sql users. @kennethcassel Does that make sense to you?

Makes sense to me! I think we're pretty close to getting #92 merged in though