8000 gh-133390: Support SQL keyword completion for sqlite3 CLI (GH-133393)… · python/cpython@e7a3c20 · GitHub
[go: up one dir, main page]
Skip to content

Commit e7a3c20

Browse files
encukoutanloong
encukou
and
tanloong
authored
gh-133390: Support SQL keyword completion for sqlite3 CLI (GH-133393) (GH-135292)
Co-authored-by: Tan Long <tanloong@foxmail.com>
1 parent e6c3039 commit e7a3c20

File tree

7 files changed

+206
-6
lines changed

7 files changed

+206
-6
lines changed

‎Doc/whatsnew/3.15.rst

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -134,6 +134,13 @@ shelve
134134
(Contributed by Andrea Oliveri in :gh:`134004`.)
135135

136136

137+
sqlite3
138+
-------
139+
140+
* Support SQL keyword completion in the :mod:`sqlite3` command-line interface.
141+
(Contributed by Long Tan in :gh:`133393`.)
142+
143+
137144
ssl
138145
---
139146

‎Lib/sqlite3/__main__.py

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,8 @@
1212
from textwrap import dedent
1313
from _colorize import get_theme, theme_no_color
1414

15+
from ._completer import completer
16+
1517

1618
def execute(c, sql, suppress_errors=True, theme=theme_no_color):
1719
"""Helper that wraps execution of SQL code.
@@ -136,12 +138,9 @@ def main(*args):
136138
execute(con, args.sql, suppress_errors=False, theme=theme)
137139
else:
138140
# No SQL provided; start the REPL.
139-
console = SqliteInteractiveConsole(con, use_color=True)
140-
try:
141-
import readline # noqa: F401
142-
except ImportError:
143-
pass
144-
console.interact(banner, exitmsg="")
141+
with completer():
142+
console = SqliteInteractiveConsole(con, use_color=True)
143+
console.interact(banner, exitmsg="")
145144
finally:
146145
con.close()
147146

‎Lib/sqlite3/_completer.py

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
from contextlib import contextmanager
2+
3+
try:
4+
from _sqlite3 import SQLITE_KEYWORDS
5+
except ImportError:
6+
SQLITE_KEYWORDS = ()
7+
8+
_completion_matches = []
9+
10+
11+
def _complete(text, state):
12+
global _completion_matches
13+
14+
if state == 0:
15+
text_upper = text.upper()
16+
_completion_matches = [c for c in SQLITE_KEYWORDS if c.startswith(text_upper)]
17+
try:
18+
return _completion_matches[state] + " "
19+
except IndexError:
20+
return None
21+
22+
23+
@contextmanager
24+
def completer():
25+
try:
26+
import readline
27+
except ImportError:
28+
yield
29+
return
30+
31+
old_completer = readline.get_completer()
32+
try:
33+
readline.set_completer(_complete)
34+
if readline.backend == "editline":
35+
# libedit uses "^I" instead of "tab"
36+
command_string = "bind ^I rl_complete"
37+
else:
38+
command_string = "tab: complete"
39+
readline.parse_and_bind(command_string)
40+
yield
41+
finally:
42+
readline.set_completer(old_completer)

‎Lib/test/test_sqlite3/test_cli.py

Lines changed: 111 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,22 @@
11
"""sqlite3 CLI tests."""
22
import sqlite3
3+
import sys
4+
import textwrap
35
import unittest
6+
import unittest.mock
7+
import os
48

59
from sqlite3.__main__ import main as cli
10+
from test.support.import_helper import import_module
611
from test.support.os_helper import TESTFN, unlink
12+
from test.support.pty_helper import run_pty
713
from test.support import (
814
captured_stdout,
915
captured_stderr,
1016
captured_stdin,
1117
force_not_colorized_test_class,
18+
requires_subprocess,
19+
verbose,
1220
)
1321

1422

@@ -200,5 +208,108 @@ def test_color(self):
200208
self.assertIn('\x1b[1;35mOperationalError (SQLITE_ERROR)\x1b[0m: '
201209
'\x1b[35mnear "sel": syntax error\x1b[0m', err)
202210

211+
212+
@requires_subprocess()
213+
@force_not_colorized_test_class
214+
class Completion(unittest.TestCase):
215+
PS1 = "sqlite> "
216+
217+
@classmethod
218+
def setUpClass(cls):
219+
_sqlite3 = import_module("_sqlite3")
220+
if not hasattr(_sqlite3, "SQLITE_KEYWORDS"):
221+
raise unittest.SkipTest("unable to determine SQLite keywords")
222+
223+
readline = import_module("readline")
224+
if readline.backend == "editline":
225+
raise unittest.SkipTest("libedit readline is not supported")
226+
227+
def write_input(self, input_, env=None):
228+
script = textwrap.dedent("""
229+
import readline
230+
from sqlite3.__main__ import main
231+
232+
readline.parse_and_bind("set colored-completion-prefix off")
233+
main()
234+
""")
235+
return run_pty(script, input_, env)
236+
237+
def test_complete_sql_keywords(self):
238+
# List candidates starting with 'S', there should be multiple matches.
239+
input_ = b"S\t\tEL\t 1;\n.quit\n"
240+
output = self.write_input(input_)
241+
self.assertIn(b"SELECT", output)
242+
self.assertIn(b"SET", output)
243+
self.assertIn(b"SAVEPOINT", output)
244+
self.assertIn(b"(1,)", output)
245+
246+
# Keywords are completed in upper case for even lower case user input.
247+
input_ = b"sel\t\t 1;\n.quit\n"
248+
output = self.write_input(input_)
249+
self.assertIn(b"SELECT", output)
250+
self.assertIn(b"(1,)", output)
251+
252+
@unittest.skipIf(sys.platform.startswith("freebsd"),
253+
"Two actual tabs are inserted when there are no matching"
254+
" completions in the pseudo-terminal opened by run_pty()"
255+
" on FreeBSD")
256+
def test_complete_no_match(self):
257+
input_ = b"xyzzy\t\t\b\b\b\b\b\b\b.quit\n"
258+
# Set NO_COLOR to disable coloring for self.PS1.
259+
output = self.write_input(input_, env={**os.environ, "NO_COLOR": "1"})
260+
lines = output.decode().splitlines()
261+
indices = (
262+
i for i, line in enumerate(lines, 1)
263+
if line.startswith(f"{self.PS1}xyzzy")
264+
)
265+
line_num = next(indices, -1)
266+
self.assertNotEqual(line_num, -1)
267+
# Completions occupy lines, assert no extra lines when there is nothing
268+
# to complete.
269+
self.assertEqual(line_num, len(lines))
270+
271+
def test_complete_no_input(self):
272+
from _sqlite3 import SQLITE_KEYWORDS
273+
274+
script = textwrap.dedent("""
275+
import readline
276+
from sqlite3.__main__ import main
277+
278+
# Configure readline to ...:
279+
# - hide control sequences surrounding each candidate
280+
# - hide "Display all xxx possibilities? (y or n)"
281+
# - hide "--More--"
282+
# - show candidates one per line
283+
readline.parse_and_bind("set colored-completion-prefix off")
284+
readline.parse_and_bind("set colored-stats off")
285+
readline.parse_and_bind("set completion-query-items 0")
286+
readline.parse_and_bind("set page-completions off")
287+
readline.parse_and_bind("set completion-display-width 0")
288+
readline.parse_and_bind("set show-all-if-ambiguous off")
289+
readline.parse_and_bind("set show-all-if-unmodified off")
290+
291+
main()
292+
""")
293+
input_ = b"\t\t.quit\n"
294+
output = run_pty(script, input_, env={**os.environ, "NO_COLOR": "1"})
295+
try:
296+
lines = output.decode().splitlines()
297+
indices = [
298+
i for i, line in enumerate(lines)
299+
if line.startswith(self.PS1)
300+
]
301+
self.assertEqual(len(indices), 2)
302+
start, end = indices
303+
candidates = [l.strip() for l in lines[start+1:end]]
304+
self.assertEqual(candidates, sorted(SQLITE_KEYWORDS))
305+
except:
306+
if verbose:
307+
print(' PTY output: '.center(30, '-'))
308+
print(output.decode(errors='replace'))
309+
print(' end PTY output '.center(30, '-'))
310+
raise
311+
312+
313+
203314
if __name__ == "__main__":
204315
unittest.main()

‎Misc/ACKS

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1869,6 +1869,7 @@ Neil Tallim
18691869
Geoff Talvola
18701870
Anish Tambe
18711871
Musashi Tamura
1872+
Long Tan
18721873
William Tanksley
18731874
Christian Tanzer
18741875
Steven Taschuk

‎Misc/NEWS.d/next/Library/2025-05-05-03-14-08.gh-issue-133390.AuTggn.rst

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
Support keyword completion in the :mod:`sqlite3` command-line interface.

‎Modules/_sqlite/module.c

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@
3232
#include "microprotocols.h"
3333
#include "row.h"
3434
#include "blob.h"
35+
#include "util.h"
3536

3637
#if SQLITE_VERSION_NUMBER < 3015002
3738
#error "SQLite 3.15.2 or higher required"
@@ -404,6 +405,40 @@ pysqlite_error_name(int rc)
404405
return NULL;
405406
}
406407

408+
static int
409+
add_keyword_tuple(PyObject *module)
410+
{
411+
#if SQLITE_VERSION_NUMBER >= 3024000
412+
int count = sqlite3_keyword_count();
413+
PyObject *keywords = PyTuple_New(count);
414+
if (keywords == NULL) {
415+
return -1;
416+
}
417+
for (int i = 0; i < count; i++) {
418+
const char *keyword;
419+
int size;
420+
int result = sqlite3_keyword_name(i, &keyword, &size);
421+
if (result != SQLITE_OK) {
422+
pysqlite_state *state = pysqlite_get_state(module);
423+
set_error_from_code(state, result);
424+
goto error;
425+
}
426+
PyObject *kwd = PyUnicode_FromStringAndSize(keyword, size);
427+
if (!kwd) {
428+
goto error;
429+
}
430+
PyTuple_SET_ITEM(keywords, i, kwd);
431+
}
432+
return PyModule_Add(module, "SQLITE_KEYWORDS", keywords);
433+
434+
error:
435+
Py_DECREF(keywords);
436+
return -1;
437+
#else
438+
return 0;
439+
#endif
440+
}
441+
407442
static int
408443
add_integer_constants(PyObject *module) {
409444
#define ADD_INT(ival) \
@@ -702,6 +737,10 @@ module_exec(PyObject *module)
702737
goto error;
703738
}
704739

740+
if (add_keyword_tuple(module) < 0) {
741+
goto error;
742+
}
743+
705744
if (PyModule_AddStringConstant(module, "sqlite_version", sqlite3_libversion())) {
706745
goto error;
707746
}

0 commit comments

Comments
 (0)
0