Support completion for sqlite3 command-line interface

[tanloong]

Feature or enhancement

Proposal:

Current sqlite3 CLI lacks tab-completion feature. Hope it can be supported to improve usability.

Has this already been discussed elsewhere?

This is a minor feature, which does not need previous discussion elsewhere

Links to previous discussion of this feature:

No response

Linked PRs

• gh-133390: Support SQL keyword completion for sqlite3 CLI #133393
• Revert "gh-133390: Support SQL keyword completion for sqlite3 CLI (#133393)" temporarily #135232
• gh-133390: Support SQL keyword completion for sqlite3 CLI (#133393) #135292
• gh-133390: Extend completion for .commands in sqlite3 #135432
• gh-133390: Document SQLITE_KEYWORDS #135659
• gh-133390: Support table, index, trigger, view, column, function, and schema completion in the sqlite3 CLI #136101
• gh-133390: Amend gh-135659 (sqlite3 docs update) #137447

[StanFromIreland]

This is a minor feature

I would disagree, this would presumably require quite a lot of work to implement and then maintain. Maybe this should be discussion on discourse first?

[tanloong]

Right. But I think for the moment we can support basic completion which takes not much work but gives big experience enhancement.

[StanFromIreland]

#133393 introduces keyword expansion, but what about name expansion (the part I was originally worried about)?

[tanloong]

Can we get the name list when connecting to a disk-file database, merge it with keyword list as completion candidates? Also the name list need to be updated on deletion/creation of table/column.

import sqlite3

def get_names(db_path):
    ret = []
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
    tables = [row[0] for row in cursor.fetchall()] # get table names
    ret.extend(tables)

    for table in tables:
        cursor.execute(f"PRAGMA table_info({table})")
        ret.extend(row[1] for row in cursor.fetchall()) # get column names

    conn.close()
    return ret

db_path = '/path/to/db.db'
list_of_names = get_names(db_path)

[StanFromIreland]

@hugovk (BDFC ;-) out of curiosity, is this on your list for colorising, it is a good candidate ? I’d take a stab at it once this is merged.

[hugovk]

It's not on my list but sounds like a good candidate. See also #133247 that added syntax highlighting in the REPL and tools like https://github.com/dbcli/litecli.

[StanFromIreland]

sounds like a good candidate.

Opened #133447 :-)

[encukou]

Unfortunately, there are issues on the buildbots found before, e.g. here.

I spent some time investigating this, but couldn't reproduce locally or in a container. Next week I'll ask the buildbot owner for SSH access to try and see what's going on, but that's better done after the weekend.

The buildbots are only effective if they're kept passing, so I need to revert the commit temporarily.