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. The database_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. The function_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 the DISTINCT 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 passes text 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 in test_complete_columns(), test_complete_table_indexes_triggers_views(), and test_complete_schemata().

• Issue: Support completion for sqlite3 command-line interface #133390