Hi dbcli/litecli!

This is a one-off automatically generated pull request from LGTM.com :robot:. You might have heard that we’ve integrated LGTM’s underlying CodeQL analysis engine natively into GitHub. The result is GitHub code scanning!

With LGTM fully integrated into code scanning, we are focused on improving CodeQL within the native GitHub code scanning experience. In order to take advantage of current and future improvements to our analysis capabilities, we suggest you enable code scanning on your repository. Please take a look at our blog post for more information.

This pull request enables code scanning by adding an auto-generated codeql.yml workflow file for GitHub Actions to your repository — take a look! We tested it before opening this pull request, so all should be working :heavy_check_mark:. In fact, you might already have seen some alerts appear on this pull request!

Where needed and if possible, we’ve adjusted the configuration to the needs of your particular repository. But of course, you should feel free to tweak it further! Check this page for detailed documentation.

Questions? Check out the FAQ below!

FAQ

Currently, the keyword argument python_requires of setup() is not set, and thus it is assumed that this distribution is compatible with all Python versions. However, I found it is not compatible with Python<3.6. My local Python version is 3.5, and I encounter the following error when executing “pip install litecli==1.9.0”

Collecting litecli==1.9.0
  Downloading litecli-1.9.0-py2.py3-none-any.whl (47 kB)
     |████████████████████████████████| 47 kB 415 kB/s 
Collecting Pygments>=1.6
  Downloading Pygments-2.11.2-py3-none-any.whl (1.1 MB)
     |████████████████████████████████| 1.1 MB 639 kB/s 
Collecting click>=4.1
  Downloading click-7.1.2-py2.py3-none-any.whl (82 kB)
     |████████████████████████████████| 82 kB 417 kB/s 
ERROR: Could not find a version that satisfies the requirement prompt-toolkit<4.0.0,>=3.0.3 (from litecli==1.9.0) (from versions: 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.8, 0.9, 0.10, 0.11, 0.12, 0.13, 0.14, 0.15, 0.16, 0.17, 0.18, 0.19, 0.20, 0.21, 0.22, 0.23, 0.25, 0.26, 0.28, 0.30, 0.31, 0.32, 0.33, 0.34, 0.35, 0.36, 0.37, 0.38, 0.39, 0.40, 0.41, 0.42, 0.43, 0.44, 0.45, 0.46, 0.47, 0.48, 0.49, 0.50, 0.51, 0.52, 0.53, 0.54, 0.55, 0.56, 0.57, 0.58, 0.59, 0.60, 1.0.0, 1.0.1, 1.0.2, 1.0.3, 1.0.4, 1.0.5, 1.0.6, 1.0.7, 1.0.8, 1.0.9, 1.0.10, 1.0.13, 1.0.14, 1.0.15, 1.0.16, 1.0.17, 1.0.18, 2.0.1, 2.0.2, 2.0.3, 2.0.4, 2.0.5, 2.0.6, 2.0.7, 2.0.8, 2.0.9, 2.0.10)
ERROR: No matching distribution found for prompt-toolkit<4.0.0,>=3.0.3 (from litecli==1.9.0)

Dependencies of this distribution are listed as follows:

    "click >= 4.1",
    "Pygments>=1.6",
    "prompt_toolkit>=3.0.3,<4.0.0",
    "sqlparse",
    "configobj >= 5.0.5",
    "cli_helpers[styles] >= 2.2.1",

I found that prompt_toolkit>=3.0.3,<4.0.0 requires Python>=3.6.2, which results in installation failure of litecli in Python3.5.

Way to fix: modify setup() in setup.py, add python_requires keyword argument:

setup(…
     python_requires='>=3.6.2'
     …)

Thanks for your attention. Best regrads, PyVCEchecker

sqlite:

→ sqlite3 imdb-cmudb2022.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema people
CREATE TABLE people (
  person_id VARCHAR PRIMARY KEY,
  name VARCHAR,
  born INTEGER,
  died INTEGER
);
CREATE INDEX ix_people_name ON people (name);
sqlite>

litecli:

→ litecli imdb-cmudb2022.db
Version: 1.9.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
GitHub: https://github.com/dbcli/litecli
imdb-cmudb2022.db> .schema people
+--------------------------------------------------------------------------------------------------------------+
| sql                                                                                                          |
+--------------------------------------------------------------------------------------------------------------+
| CREATE TABLE people (\n  person_id VARCHAR PRIMARY KEY,\n  name VARCHAR,\n  born INTEGER,\n  died INTEGER\n) |
+--------------------------------------------------------------------------------------------------------------+
Time: 0.012s
imdb-cmudb2022.db>

When using .once -o ..., only the last line of the next output remains in the file once that command has completed.

That's because the write_once() function is executed for each individual output line, and each time the output file is opened with a new open(**once_file) call. Because the -o sets the file mode to "w", the output file is truncated for each line, and so all preceding output is lost.

Either the write_once() should execute once_file["mode"] = "a" so further lines are appended instead of replacing the file, or the code should be refactored to work like the tee() command and keep a reference to the opened file for re-use until unset_once_if_written() is called (which would then close the file). With that second option, you could then also drop the written_to_once_file flag variable, because the opened file object would only exist if there had been output to write.

There is an awesome .eqp on command in the default sqlite3 shell. It works like this:

sqlite> .eqp on
sqlite> select distinct book.id, (select coalesce(json_group_array(json_array(v0, v1, v2, v3, v4, v5, v6, v7)), json_array()) from (select b.id as v0, b.path as v1, b.name as v2, b.date as v3, b.added as v4, b.sequence as v5, b.sequence_number as v6, b.lang as v7 from book as b where b.id = book.id) as t) as book, (select coalesce(json_group_array(json_array(v0, v1, v2, v3, v4, v5, v6)), json_array()) from (select distinct author.id as v0, author.fb2id as v1, author.first_name as v2, author.middle_name as v3, author.last_name as v4, author.nickname as v5, author.added as v6 from author join book_author on book_author.author_id = author.id where book_author.book_id = book.id) as t) as authors, (select coalesce(json_group_array(json_array(v0)), json_array()) from (select distinct genre.name as v0 from genre join book_genre on book_genre.genre_id = genre.id where book_genre.book_id = book.id) as t) as genres from book join book_author on book_author.book_id = book.id where (book.sequence = 'Звёздные войны' and book_author.author_id = 45826) order by book.sequence_number asc nulls last, book.name
   ...> ;
QUERY PLAN
|--SEARCH book_author USING INDEX book_author_author_id (author_id=?)
|--SEARCH book USING INTEGER PRIMARY KEY (rowid=?)
|--CORRELATED SCALAR SUBQUERY 2
|  `--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
|--CORRELATED SCALAR SUBQUERY 4
|  |--CO-ROUTINE t
|  |  |--SEARCH book_author USING COVERING INDEX sqlite_autoindex_book_author_1 (book_id=?)
|  |  |--SEARCH author USING INTEGER PRIMARY KEY (rowid=?)
|  |  `--USE TEMP B-TREE FOR DISTINCT
|  `--SCAN t
|--CORRELATED SCALAR SUBQUERY 6
|  |--CO-ROUTINE t
|  |  |--SEARCH book_genre USING COVERING INDEX sqlite_autoindex_book_genre_1 (book_id=?)
|  |  |--SEARCH genre USING INTEGER PRIMARY KEY (rowid=?)
|  |  `--USE TEMP B-TREE FOR DISTINCT
|  `--SCAN t
|--USE TEMP B-TREE FOR DISTINCT
`--USE TEMP B-TREE FOR ORDER BY
122718|[[122718,"/media/sda3/Books/native/439383.fb2","Заря джедаев: В пустоту","","2022-06-19 23:10:14.992363471","Звёздные войны",1,"ru"]]|[[45826,null,"Тим",null,"Леббон",null,"2022-06-19 20:51:32.016737954"]]|[["sf_space"]]
264789|[[264789,"/media/sda3/Books/native/577219.fb2","Заря джедаев: В бесконечность","","2022-06-20 09:48:50.7587699","Звёздные войны",null,"ru"]]|[[45826,null,"Тим",null,"Леббон",null,"2022-06-19 20:51:32.016737954"]]|[["sf_space"]]
173283|[[173283,"/media/sda3/Books/native/439488.fb2","Заря джедаев: В пустоту","","2022-06-20 03:09:41.412632853","Звёздные войны",null,"ru"]]|[[45826,null,"Тим",null,"Леббон",null,"2022-06-19 20:51:32.016737954"]]|[["sf_space"]]

As you can see it outputs the execution plan tree on each request. Would be nice to support it in litecli too