json output mode for litecli
mrchrisadams opened this issue · 5 comments
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?
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
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.
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.
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.
@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.
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 👍