-
-
Notifications
You must be signed in to change notification settings - Fork 32.7k
gh-133390: Support table, index, trigger, view, column, function, and schema completion in the sqlite3 CLI #136101
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
base: main
Are you sure you want to change the base?
Conversation
…pletion for sqlite3 CLI
…ew-column-function-schema
…ew-column-function-schema
It's been a month since this PR is created. Could someone give it a review? Thanks! |
schemata = tuple(row[1] for row | ||
in cursor.execute("PRAGMA database_list")) | ||
# tables, indexes, triggers, and views | ||
select_clauses = (f"SELECT name FROM \"{schema}\".sqlite_master" |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
Did you consider asking SQLite for more of the manipulation and filtering, e.g. SELECT name || ' ' FROM \"{schema}\".sqlite_master WHERE name LIKE :text || '%'
?
select_clauses = (f"SELECT name FROM \"{schema}\".sqlite_master" | ||
for schema in schemata) | ||
tables = (row[0] for row | ||
in cursor.execute(" UNION ".join(select_clauses))) |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
Did you consider using pragma_table_list
to get the tables?
I think it might be OK to simply not support completions for 3.37 (2021-11-27) and below, if it would mean less code to maintain.
But, @erlend-aasland's opinion would would be more important than mine :)
It is not until recently that I found SQLite has a completion extension, which can potentially be used to provide candidates for the CPython sqlite3 CLI. But the CPython
sqlite3
module is not built with loadable extension support by default. So, we still have to implement completion on our own. This PR refers to the implementation of SQLite's completion extension. The completion in both SQLite's implementation and this PR is not context-aware. It simply lists all possible candidates that match the user's input as a prefix.Tables, indexes, triggers, and views
These candidates are retrieved using
PRAGMA database_list
. Thedatabase_list
command is available since at least 2003, thus it should be OK to be used as CPython requires SQLite>=3.15.2 (2016-11-28).Columns
Column candidate access is done with a SQL statement copied from SQLite's implementation. It uses PRAGMA table-valued function (available since SQLite 3.16.0). On SQLite<3.16.0, column completion are skipped.
Functions
Function candidates are retrieved using PRAGMA
function_list
. Thefunction_list
command is available since SQLite v3.30.0, newer than v3.15.2 that CPython requires. On SQLite<3.30.0, function completion will be skipped. Some SQL functions may have multiple rows in the result set if they can be called with a variable number of arguments, so theDISTINCT
keyword is used to remove duplicates.The result of
PRAGMA function_list
contains two items that are not alphabetical:->
and->>
. These two are excluded from complication list in this PR. Readline considers-
and>
as word break and passestext
as an empty string to_complete()
when the cursor is at the right hand side of-
or>
. This means that even if we include->
and->>
in the candidate list, they'll never be auto-completed. To make Readline recognize them as valid prefixes, we'd need to modify rl_special_prefixes, but I didn't find an API in CPython to do this. As->
and->>
are short and not difficult to type, I suppose excluding them is OK.Duplication and sorting
GNU Readline ignores duplicates and sorts candidates by default. To make sure the completion is consistent even without Readline's guard, I drop duplicates (if any) and sort candidates (by
_completion_matches = sorted(set(_completion_matches))
) in this PR as well, but if it looks unnecessary I can remove it.Tests
Some settings are added in
write_input()
to turn off Readline's pager and multi-column layout, making it easier to parse candidates intest_complete_columns()
,test_complete_table_indexes_triggers_views()
, andtest_complete_schemata()
.