json output mode for litecli

[mrchrisadams]

Hi there,

I love the litecli project - thank you so much for making it!

I have one query that I wanted a few pointers on.

JSON output mode

What version of sqlite3 does litecli use, and is there anyway to control this?

On my laptop (macbook, os 11.6) I have this version of sqlite3 installed:

3.33.0 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f

It has a few handy features that I can't find in the litecli version, like json output, because the new versions of sqlite 3 have, an increasingly nice set of new features.

See the output below for the some of the output modes available by default now:

sqlite3
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode
current output mode: list
sqlite> .help mode
.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements
sqlite> 

However with the latest version of litecli, I can't see this helpful output format.

litecli db.sqlite3
Version: 1.6.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
GitHub: https://github.com/dbcli/litecli
db.sqlite3> .mode
Table format  not recognized. Allowed formats:
        vertical
        csv
        csv-tab
        mediawiki
        html
        latex
        latex_booktabs
        textile
        moinmoin
        jira
        ascii
        plain
        simple
        minimal
        grid
        fancy_grid
        pipe
        orgtbl
        psql
        psql_unicode
        rst
        github
        double
        tsv
Time: 0.000s
db.sqlite3>

I can see plenty of handy formats but not the json output option.

Is there a way to use a new version of sqlite you may have installed locally?

[mrchrisadams]

Ah, it looks a bit more complicated than I thought as even on a recent version of python the version, it seems to link to an older version

python
Python 3.9.0 (default, Jan 19 2021, 12:51:10) 
[Clang 12.0.0 (clang-1200.0.32.27)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.__file__
'/Users/chrisadams/.pyenv/versions/3.9.0/lib/python3.9/sqlite3/__init__.py'
>>> 

There are ways to bundle a new version in, it seems

https://stackoverflow.com/questions/29282380/python-sqlite3-run-different-sqlite3-version

https://stackoverflow.com/questions/14541869/how-to-upgrade-sqlite3-in-python-2-7-3-inside-a-virtualenv

But the simpler option might be to include this as a dependency for the project. I think you can link to it separately this way.

https://github.com/coleifer/pysqlite3

[mrchrisadams]

Oh, scratch that.

It looks like there are a few precompiled extensions, across various domains like working with json, ip addresses, stats and so on. I think these can be loaded in like so below if they're not already loaded. You can find some of the extensions available like so:

https://github.com/nalgeon/sqlean https://sqlite.org/loadext.html

And the code once you've downloaded an extension so use apparently looks a bit like this

import sqlite3

connection = sqlite3.connect(":memory:")
connection.enable_load_extension(True)

# if on linux, you use a .so file, others use different extension
connection.load_extension("./stats.so")

connection.execute("select median(value) from generate_series(1, 100)")
connection.close()

Anyway, I'd be up for having a look at how to implement a json formatter using either python's own json support or finding a way to use any native support inside sqlite3 if it's present.

[mrchrisadams]

Oh hang on, it looks like this support might already be in a lot of distributions of sqlite3.

You can look for the ENABLE_JSON1 compile option like so.

Version: 1.6.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
GitHub: https://github.com/dbcli/litecli
db.sqlite3> pragma compile_options;
+----------------------------------+
| compile_options                  |
+----------------------------------+
| BUG_COMPATIBLE_20160819          |
| COMPILER=clang-12.0.5            |
| DEFAULT_CACHE_SIZE=2000          |
| DEFAULT_CKPTFULLFSYNC            |
| DEFAULT_JOURNAL_SIZE_LIMIT=32768 |
| DEFAULT_PAGE_SIZE=4096           |
| DEFAULT_SYNCHRONOUS=2            |
| DEFAULT_WAL_SYNCHRONOUS=1        |
| ENABLE_API_ARMOR                 |
| ENABLE_COLUMN_METADATA           |
| ENABLE_DBSTAT_VTAB               |
| ENABLE_FTS3                      |
| ENABLE_FTS3_PARENTHESIS          |
| ENABLE_FTS3_TOKENIZER            |
| ENABLE_FTS4                      |
| ENABLE_FTS5                      |
| ENABLE_JSON1                     |
| ENABLE_LOCKING_STYLE=1           |
| ENABLE_PREUPDATE_HOOK            |
| ENABLE_RTREE                     |
| ENABLE_SESSION                   |
| ENABLE_SNAPSHOT                  |
| ENABLE_SQLLOG                    |
| ENABLE_STMT_SCANSTATUS           |
| ENABLE_UNKNOWN_SQL_FUNCTION      |
| ENABLE_UPDATE_DELETE_LIMIT       |
| HAS_CODEC_RESTRICTED             |
| HAVE_ISNAN                       |
| MAX_LENGTH=2147483645            |
| MAX_MMAP_SIZE=1073741824         |
| MAX_VARIABLE_NUMBER=500000       |
| OMIT_AUTORESET                   |
| OMIT_LOAD_EXTENSION              |
| STMTJRNL_SPILL=131072            |
| THREADSAFE=2                     |
| USE_URI                          |
+----------------------------------+

And if you have that, it looks like there is already json support.

db.sqlite3> select json(' { "this" : "is", "a": [ "test" ] } ')
+------------------------------+
| json(' { "this" : "is", "a": |
+------------------------------+
| {"this":"is","a":["test"]}   |
+------------------------------+
1 row in set
Time: 0.006s
db.sqlite3> select json_valid('{"x":35}')
+------------------------+
| json_valid('{"x":35}') |
+------------------------+
| 1                      |
+------------------------+
1 row in set
Time: 0.008s
db.sqlite3> select json_valid('{"x":35')
+-----------------------+
| json_valid('{"x":35') |
+-----------------------+
| 0                     |
+-----------------------+
1 row in set
Time: 0.008s

I think it might be a case of looking for the appropriate compile ENABLE_JSON1 flag, and being able to have some kind of pass through to the underlying display function if it exists.

[amjith]

@mrchrisadams I'm sorry I haven't had a chance to respond to you. Litecli tries to use the sqlite3 library that comes bundled with the Python standard library.

The .mode option is very different between the sqlite3 default cli and litecli. We use a wholly different library to format the output. So the missing json option is not due to the sqlite version but because of the library that we're using.

[mrchrisadams]

Thanks for this @amjith, I understand it better now.

Based on what I know now, it I get the impression that implementing json support would be quite a faff and involve a fair few changes to the cli helper. I'm happy to close this 👍