JSON1 Extension still unavailable under Ubuntu / Linux Mint

[MartinHaeusler]

I'm trying to execute a query using JSON1 in SQLite Browser under Linux Mint, however the extension seems to be missing in this binary.

Details for the issue

What did you do?

I just tried the nightly release on Linux Mint 20 with the following commands:

• sudo add-apt-repository -y ppa:linuxgndu/sqlitebrowser-testing
• sudo apt-get update
• sudo apt-get install sqlitebrowser

This gives me the following version in the "About" dialog: DB Browser for SQLite Version 3.12.99 (Oct 10 2020)

What did you expect to see?

For example, the basic query:

SELECT json_extract(json('{"greeting": "hello"}'), '$.greeting')

... should work.

What did you see instead?

The example query above produces the following error:

Execution finished with errors.
Result: no such function: json
At line 1:
SELECT json_extract(json('{"greeting": "hello"}'), '$.greeting')

Useful extra information

The info below often helps, please fill it out if you're able to. :)

What operating system are you using?

• Windows: ( version: ___ )
• Linux: (Mint 20 )
• macOS: ( version: ___ )
• Other: ___

[chrisjlocke]

The extension isn't compiled statically into the application - you'd have to load it via 'Tools' and 'Load extension'.
It would also need creating, as its not currently available in our packages. I've had a quick look around, but many sources just say, "compile it yourself" which isn't helpful...

[lucydodo]

I just compile JSON1 extension for you. You can load the below file from the 'Preferences' and use it.
At least it works fine on my Ubuntu 20.04.1 PC and DB4S's latest nightly build.

DOWNLOAD LINK REMOVED

• MD5SUM: 6a5166245d1b2609d6b61996bdd9ae39
• SHA256: dab4a626be23514f75485ce77d5959f53e2dfa966011f156985d40941996a820

[MartinHaeusler]

It's odd that the extension isn't enabled by default on linux. I know for a fact that in the windows version, the JSON1 extension is enabled by default because my co-workers use it.

[MartinHaeusler]

@lucydodo I just tried it, sorry but my SQLite crashes immediately (without any error message, the window just closes) when I try to add that extension file...

[justinclift]

The extension isn't compiled statically into the application - you'd have to load it via 'Tools' and 'Load extension'.
It would also need creating, as its not currently available in our packages. I've had a quick look around, but many sources just say, "compile it yourself" which isn't helpful...

@chrisjlocke We include the JSON1 extension (built in) to our Windows and macOS builds:

sqlitebrowser/installer/windows/nightly_build_scripts/win64build.bat

Line 70 in 1c0cf44

cl sqlite3.c -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_STAT4 -DSQLITE_SOUNDEX -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_GEOPOLY -DSQLITE_ENABLE_RTREE -DSQLITE_MAX_ATTACHED=125 -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll

We probably just need to add the same options to the Linux build process. @deepsidhu1313 would you be ok to try adding them? It's probably just a matter of using the same compile options for the build. Something like:

<compiler> sqlite3.c -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_STAT4 -DSQLITE_SOUNDEX -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_GEOPOLY -DSQLITE_ENABLE_RTREE -DSQLITE_MAX_ATTACHED=125

The "<compiler>" thing there being (eg) gcc or whatever is currently being used. Hopefully that works. 😄

[MartinHaeusler]

If there's a way to enable JSON1 by default on linux, please enable it. It would make my daily work that much easier.

[justinclift]

No worries. @deepsidhu1313 is the creator of our Linux packages, so lets see if the extension can be added to them. 😄

[lucydodo]

@justinclift Can you tell me what extensions are currently built-in to DB4S?
It would be nice to add information about this to the wiki. 😄

[deepsidhu1313]

Quick question, to enable json1 support do we need set flag while building DB4S or sqlite ?

[chrisjlocke]

Theoretically, when building SQLite. Specifically, "Add the -DSQLITE_ENABLE_JSON1 compile-time option to enable the json1 extension that is built into the amalgamation. The json1 source code is included with the SQLite amalgamation, though it is disabled by default."

[justinclift]

Oops. Yeah, that's why I was pointing to the compile line above. Forgot I should probably explain it a bit better.

Each of those options in the compile line above are explained here:

    https://www.sqlite.org/compile.html

So:

• -DSQLITE_ENABLE_FTS5 enables the FTS5 extension
• -DSQLITE_ENABLE_FTS3 enables the FTS3 extension
• etc...

Thanks @chrisjlocke. 😄

[deepsidhu1313]

I have to check if default sqlite packages are built with these flags, otherwise we have to compile our own