gh-133390: Support table, index, trigger, view, column, function, and schema completion in the sqlite3 CLI #136101
+202
−15
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
…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! |
encukou
reviewed
Aug 26, 2025
Lib/sqlite3/_completer.py
Outdated
| 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.
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 :)
There was a problem hiding this comment.
I agree that pragma_table_list is more suitable for retrieving tables, but sqlite_master holds extra information regarding triggers and indexes, which I think can be taken as extra completion sources.
…ew-column-function-schema
…ew-column-function-schema
…ew-column-function-schema
….0 for columns, 3.30.0 for functions)
encukou
reviewed
Oct 20, 2025
| ON _Attached_table BEGIN SELECT 1; END; | ||
| CREATE VIEW attached._Attached_view AS SELECT 1; | ||
| SELECT id FROM _\t\tta\t; |
There was a problem hiding this comment.
The last \t (after _a) isn't tested. Did you mean to add another assertion with start, end = indices[-2], indices[-1]?
There was a problem hiding this comment.
Thanks for your review! I will add it.
encukou
approved these changes
Oct 21, 2025
|
|
|
Buildbot failures look like unrelated timeouts, passed on re-run: |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
4 participants
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
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
sqlite3module is not built with loadable extension support by default. So, if we want the completion we still have to implement it 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_listcommand 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_listcommand 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 theDISTINCTkeyword is used to remove duplicates.The result of
PRAGMA function_listcontains two items that are not alphabetical:->and->>. These two are excluded from completion list in this PR. Readline considers-and>as word break and passestextas 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().