kgtk error from local sqlite version

[joelb-git]

After installing the current kgtk and working around an issue with
sh (see #696), I am still seeing an issue, this time with sqlite3.

I did not see this issue on mac, however I do see it on linux.

$ uname -a
Linux saga22 3.10.0-1160.83.1.el7.x86_64 #1 SMP Wed Jan 25 16:41:43 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

$ conda create -n kgtk-env-tmp python=3.9
$ conda activate kgtk-env-tmp
$ conda install -c conda-forge graph-tool
$ conda install -c conda-forge jupyterlab
$ pip --no-cache install -U kgtk
# then workaround sh issue as in https://github.com/usc-isi-i2/kgtk/issues/696

$ kgtk query -i /nas/material02/users/joelb/views/kgtk-notebooks/datasets/arnold/small-graph.tsv --match '()-[]->()'
Exception in thread background thread for pid 8117:
Traceback (most recent call last):
  File "/nas/material02/users/joelb/conda_envs/kgtk-env-tmp/lib/python3.9/threading.py", line 980, in _bootstrap_inner
    self.run()
  File "/nas/material02/users/joelb/conda_envs/kgtk-env-tmp/lib/python3.9/threading.py", line 917, in run
    self._target(*self._args, **self._kwargs)
  File "/nas/material02/users/joelb/conda_envs/kgtk-env-tmp/lib/python3.9/site-packages/sh.py", line 1641, in wrap
    fn(*rgs, **kwargs)
  File "/nas/material02/users/joelb/conda_envs/kgtk-env-tmp/lib/python3.9/site-packages/sh.py", line 2569, in background_thread
    handle_exit_code(exit_code)
  File "/nas/material02/users/joelb/conda_envs/kgtk-env-tmp/lib/python3.9/site-packages/sh.py", line 2269, in fn
    return self.command.handle_command_exit_code(exit_code)
  File "/nas/material02/users/joelb/conda_envs/kgtk-env-tmp/lib/python3.9/site-packages/sh.py", line 869, in handle_command_exit_code
    raise exc
sh.ErrorReturnCode_1:

  RAN: /usr/bin/sqlite3 -cmd '.mode ascii' -cmd '.separator \t \n' /tmp/kgtk-graph-cache-joelb.sqlite3.db '.import /dev/stdin graph_2'

  STDOUT:


  STDERR:
Error: mode should be one of: column csv html insert line list tabs tcl
Error: unknown command or invalid arguments:  "separator". Enter ".help" for help
Error: /dev/stdin line 1: expected 4 columns of data but found 1



  RAN: /usr/bin/sqlite3 -cmd '.mode ascii' -cmd '.separator \t \n' /tmp/kgtk-graph-cache-joelb.sqlite3.db '.import /dev/stdin graph_2'

  STDOUT:


  STDERR:
Error: mode should be one of: column csv html insert line list tabs tcl
Error: unknown command or invalid arguments:  "separator". Enter ".help" for help
Error: /dev/stdin line 1: expected 4 columns of data but found 1

On mac:

$ which sqlite3
/Users/joelb/anaconda3/envs/kgtk-env/bin/sqlite3

$ conda list | grep sqlite
libsqlite                 3.40.0               ha978bb4_0    conda-forge
sqlite                    3.40.0               h9ae0607_0    conda-forge

$ pip freeze | grep sqlite
# no output

$ sqlite3 --version
3.40.0 2022-11-16 12:10:08 89c459e766ea7e9165d0beeb124708b955a4950d0f4792f457465d71b158d318

On linux:

$ conda list | grep sqlite
libsqlite                 3.40.0               h753d276_0    conda-forge

# note sqlite is missing, separate from libsqlite

$ pip freeze | grep sqlite
# no output

$ which sqlite3
/usr/bin/sqlite3

$ sqlite3 --version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

It looks like the install process does not explicitly mention sqlite
in the requirements. On linux, I happen to pick up an old
incompatible version from my path. On mac, I'm not sure why it's in
the conda environment.

A workaround on linux is to install the correct version explicilty:

$ conda install -c anaconda sqlite=3.40.0
$ which sqlite3
/nas/material02/users/joelb/conda_envs/kgtk-env-tmp/bin/sqlite3

$ kgtk query -i /nas/material02/users/joelb/views/kgtk-notebooks/datasets/arnold/small-graph.tsv --match '()-[]->()'
id      node1   label   node2
e1      m_shriver       spouse  a_schwarzenegger
e2      a_schwarzenegger        coactor l_hamilton
e3      g_rydstrom      colleague       g_borders
e4      g_rydstrom      colleague       a_schwarzenegger
e5      a_schwarzenegger        name    'Arnold Schwarzenegger'@de
e6      l_hamilton      name    'Linda Hamilton'@en
e7      g_rydstrom      name    'Gary R. Rydstrom'@en
e8      g_borders       name    'Gloria S. Borders'@en
e9      m_shriver       name    'Maria Shriver'@en

I did find mention of sqlite versions here:

kgtk/kgtk/dependencies.py

Line 30 in 96d143a

# The Python sqlite3 package and library is a builtin which does not

# The Python sqlite3 package and library is a builtin which does not
# require any special installation.  However some Kypher features such
# as explanation and fast, shell-based data import require a recent
# version of sqlite3 to be available as an executable shell command.
# The functions below check for those dependencies.

SQLITE_COMMAND = 'sqlite3'
SQLITE_MINIMUM_VERSION = '3.30'

but it seems the version was not checked. I see the module is indeed
built into python3.x. But what about the command line driver?