Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

JSON1? #259

Closed
brodycj opened this issue Dec 21, 2018 · 21 comments · Fixed by #440
Closed

JSON1? #259

brodycj opened this issue Dec 21, 2018 · 21 comments · Fixed by #440

Comments

@brodycj
Copy link
Contributor

brodycj commented Dec 21, 2018

A major client needs JSON1 support in SQL.js. Can we add it to SQL.js or would we have to maintain a fork?

@lovasoa
Copy link
Member

lovasoa commented Dec 22, 2018

Hi ! We currently do not support sqlite extensions. However, it should be easy to compile your own sql.js with json1 support by editing the Makefile...

@intijk
Copy link

intijk commented Jan 15, 2019

Hi @lovasoa

I have similar needs of json. My first step is to make the repo without any changes, but i meet this:

$ make
# Generate llvm bitcode
emcc -O2 -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS c/sqlite3.c -o c/sqlite3.bc
emcc --memory-init-file 0 -O3 -s INLINING_LIMIT=50 -s RESERVED_FUNCTION_POINTERS=64 -s EXPORTED_FUNCTIONS=@exported_functions -s EXTRA_EXPORTED_RUNTIME_METHODS=@exported_runtime_methods c/extension-functions.bc c/sqlite3.bc --post-js js/api.js -o js/sql-optimized-raw.js ;\

WARNING  root: Assigning a non-existent settings attribute "EXTRA_EXPORTED_RUNTIME_METHODS"
WARNING  root:  - did you mean one of ORIGINAL_EXPORTED_FUNCTIONS, EXPORTED_FUNCTIONS?
WARNING  root:  - perhaps a typo in emcc's  -s X=Y  notation?
WARNING  root:  - (see src/settings.js for valid values)
Traceback (most recent call last):
  File "/usr/bin/emcc", line 1547, in <module>
    extra_files_to_link = system_libs.calculate([f for _, f in sorted(temp_files)], in_temp, stdout, stderr, forced=forced_stdlibs)
  File "/usr/share/emscripten/tools/system_libs.py", line 502, in calculate
    add_back_deps(symbols)
  File "/usr/share/emscripten/tools/system_libs.py", line 483, in add_back_deps
    shared.Settings.EXPORTED_FUNCTIONS.append('_' + dep)
AttributeError: 'str' object has no attribute 'append'
Makefile:24: recipe for target 'js/sql-optimized-raw.js' failed
make: *** [js/sql-optimized-raw.js] Error 1

Did I do some thing wrong?

@Yavari
Copy link

Yavari commented Mar 8, 2019

I am getting the same error when I try to build. Are there any documentation on what the build dependencies are?

@hoogw
Copy link

hoogw commented Jul 9, 2019

I need json1, json1 is a must have feature for sql.js. Why? Because when use sql.js in browser, get result, the standard format is json, instead of array of value and array of column.

@hoogw
Copy link

hoogw commented Jul 9, 2019

Please add json1 extension. It is a MUST have feature.

@brodycj
Copy link
Contributor Author

brodycj commented Jul 9, 2019

[…] json1 is a must have feature

I suggest you add an upvote (thumbs up) to the description. I think sufficient upvotes should help motivate the maintainers to reconsider the decision.

The ugly workaround is to maintain a special branch, with dist built with JSON1 enabled, as I do in my outdated fork.

@hoogw
Copy link

hoogw commented Jul 9, 2019

This is my work around,

_result_json =  json1_extension(your_raw_records);

function json1_extension(_records){
    var _json = [];
    
    var _columns = _records[0].columns
    var _values = _records[0].values
    
    
    for (var i = 0; i < _values.length; i++) {
        //console.log(_values[i]);
        var _row_json = {};
        
        var _row = _values[i];
        for (var k = 0; k < _row.length; k++) {
            _row_json[_columns[k]] = _row[k]
        }
        
        //console.log('_row_json...',_row_json);
        _json.push(_row_json)
    }
    return _json
}

@hoogw
Copy link

hoogw commented Jul 9, 2019

with json1, my sql will be:

var _sql = "SELECT json_group_array( json_object('id', id, 'name', name)    ) AS json_result FROM (SELECT * FROM ipfs ORDER BY id); ";

but, since it not support, json1, error out,
I made above function manually convert record array to json.

@lovasoa
Copy link
Member

lovasoa commented Jul 9, 2019

Hello @hoogw,
If I understand your use case correctly, all you want is getting your query results as JavaScript objects instead of arrays. This is already possible using stmt.getAsObject. You can read more about it in the README.
Also, this is an free software project maintained by volunteers. There is no MUST have features.

@jsmorph
Copy link

jsmorph commented Dec 15, 2019

@intijk I got the error you reported above. If I install EMSDK per these directions, everything works.

JSON1 is included with the SQLite "amalgations", so just adding -DSQLITE_ENABLE_JSON1 to CFLAGS in Makefile works!

var x = {"two":2,"three":3},
    js = JSON.stringify(x),
    src = "SELECT JSON_EXTRACT('" + js + "', '$.three')";
console.log("JSON_EXTRACT", db.exec(src));

@lovasoa
Copy link
Member

lovasoa commented Dec 15, 2019

var x = {"two":2,"three":3},
    js = JSON.stringify(x),
    src = "SELECT JSON_EXTRACT('" + js + "', '$.three')";
console.log("JSON_EXTRACT", db.exec(src));

I would avoid doing something like that. This is vulnerable to SQL injections.

@jsmorph
Copy link

jsmorph commented Dec 15, 2019

@lovasoa Running in the user's browser, so the user can self-inject as desired!

@lovasoa
Copy link
Member

lovasoa commented Dec 15, 2019

In any case, this is a bug. The code above just doesn't work if the json contains the character '

@jsmorph
Copy link

jsmorph commented Dec 15, 2019

It's a demo.

@KevinKelchen
Copy link

Hi @brodybits, 👋

I was checking out your fork trying to see what steps are needed to create my own fork.

  1. It looks like WASM was disabled. Does that mean you had to use asm.js for JSON1 support?
  2. Was it a requirement to update SQLite?
  3. Were the other features like FTS4, FTS5, and R-Tree required for JSON1 support?
  4. It looks like a special build command was used as well:
LLVM=/usr/local/homebrew/Cellar/emscripten/1.38.21/libexec/llvm/bin make

Is there a list of steps for someone like me who doesn't know much about this project to add JSON1 support in a fork?

I, too, wish that sql.js supported JSON1 out of the box. 🙂

Thanks!

Kevin

@brodycj
Copy link
Contributor Author

brodycj commented Apr 2, 2020

I was checking out your fork trying to see what steps are needed to create my own fork.

I recommend you check out some of my more recent branches. You should be able to see my recent work to support JSON1 along with some other features including some non-standard features here, should be able to build both asm.js and WASM now: https://github.com/brodybits/sql.js/commits/eu-ext-custom-build

You may also be interested in my custom functions proposal in PR #320.

  1. It looks like WASM was disabled. Does that mean you had to use asm.js for JSON1 support?

I had trouble using WASM in the older fork, should be OK now.

2. Was it a requirement to update SQLite?

In terms of customer requirement: yes but not a super-hard requirement.

In terms of build requirement: it should be at least theoretically possible to build JSON1 with older versions of SQLite but I cannot see much benefit here.

3. Were the other features like FTS4, FTS5, and R-Tree required for JSON1 support?

I think no. I generally build with all of these features included for the benefit of my users.

4. It looks like a special build command was used as well:

I think that was not needed to build my newer branches. I had the best luck installing emcc tool from their installer on GitHub, I think it was https://github.com/emscripten-core/emsdk but not 100% sure now.

Is there a list of steps for someone like me who doesn't know much about this project to add JSON1 support in a fork?

I would recommend that you first install the emcc or emsdk tooling and try building from the master branch of this project. Then you should be able to edit the flags to enable JSON1 and build. You are also welcome to look

Good luck!

@kaizhu256
Copy link
Member

since sql.js targets javascript, i tend to agree json1 should be a builtin feature.

@KevinKelchen
Copy link

@brodybits,

In case you didn't see my reaction on your comment: THANK YOU very much! And especially for responding so quickly! ❤️

@KevinKelchen
Copy link

This is awesome! 😀

I had been trying out @dasha.ai/sql.js as a pre-built package that adds JSON1 support but now it's built into the original library!

@james-bowers
Copy link

james-bowers commented Mar 15, 2024

👋🏼 Hi! I'm late to the party here, but what data type should JSON be inserted as into a table? Is it a text data type? I can't find it in the docs so I'm just experimenting now trying various things 👀

UPDATE 1;

On this blog post I've seen there's a json data type; https://www.beekeeperstudio.io/blog/sqlite-json - but I get an error;

Error: no such function: json_encode

perhaps this is the old way using the extension?

UPDATE 2;

I've made progress using the json data type, and using a json function instead of the json_encode function

@kaizhu256
Copy link
Member

👋🏼 Hi! I'm late to the party here, but what data type should JSON be inserted as into a table? Is it a text data type? I can't find it in the docs so I'm just experimenting now trying various things 👀

  • there's no json_encode function in vanilla SQLite
    • instead there's json_object and json_array:
SELECT JSON_OBJECT(
    'name', 'Alice',
    'age', 25,
    'email', '[email protected]'
);
-- '{"name":"Alice","age":25,"email":"[email protected]"}'

SELECT JSON_ARRAY(
    'Alice',
    25,
    '[email protected]'
);
-- '["Alice",25,"[email protected]"]'

-- nested json example
SELECT JSON_ARRAY(
    JSON_ARRAY(1, 2),
    JSON_OBJECT('name', 'Alice')
)
-- '[[1,2],{"name":"Alice"}]'

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

9 participants