dbcli / dbcli/litecli

Support `.eqp` like in sqlite3

Open
#144 3 comments 1 reaction 0 assignees View on GitHub
Dominant language
Python
Stars
3.3k
Forks
95
PR merge metrics
No merged PRs in 30d

Description

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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.