FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

gh-133390: sqlite3 CLI completion for tables, columns, indices, trigg… · python/cpython@161b306 · GitHub

/ cpython Public

Commit 161b306

Browse files
tanloong
authored
gh-133390: sqlite3 CLI completion for tables, columns, indices, triggers, views, functions, schemata (GH-136101)
1 parent 5d2edf7 commit 161b306

5 files changed

Lines changed: 202 additions & 15 deletions

File tree

‎Doc/whatsnew/3.15.rst‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -498,6 +498,9 @@ sqlite3
498498
details.
499499
(Contributed by Stan Ulbrych and Łukasz Langa in :gh:`133461`.)
500500

501+
* Table, index, trigger, view, column, function, and schema completion on <tab>.
502+
(Contributed by Long Tan in :gh:`136101`.)
503+
501504

502505
ssl
503506
---

‎Lib/sqlite3/__main__.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -143,7 +143,7 @@ def main(*args):
143143
execute(con, args.sql, suppress_errors=False, theme=theme)
144144
else:
145145
# No SQL provided; start the REPL.
146-
with completer():
146+
with completer(con):
147147
console = SqliteInteractiveConsole(con, use_color=True)
148148
console.interact(banner, exitmsg="")
149149
finally:

‎Lib/sqlite3/_completer.py‎

Lines changed: 71 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
from _sqlite3 import OperationalError
12
from contextlib import contextmanager
23

34
try:
@@ -10,32 +11,95 @@
1011
_completion_matches = []
1112

1213

13-
def _complete(text, state):
14+
def _complete(con, text, state):
1415
global _completion_matches
1516

1617
if state == 0:
17-
if text.startswith('.'):
18-
_completion_matches = [c for c in CLI_COMMANDS if c.startswith(text)]
18+
if text.startswith("."):
19+
_completion_matches = [
20+
c + " " for c in CLI_COMMANDS if c.startswith(text)
21+
]
1922
else:
2023
text_upper = text.upper()
21-
_completion_matches = [c for c in SQLITE_KEYWORDS if c.startswith(text_upper)]
24+
_completion_matches = [
25+
c + " " for c in SQLITE_KEYWORDS if c.startswith(text_upper)
26+
]
27+
28+
cursor = con.cursor()
29+
schemata = tuple(row[1] for row
30+
in cursor.execute("PRAGMA database_list"))
31+
# tables, indexes, triggers, and views
32+
# escape '_' which can appear in attached database names
33+
select_clauses = (
34+
f"""\
35+
SELECT name || ' ' FROM \"{schema}\".sqlite_master
36+
WHERE name LIKE REPLACE(:text, '_', '^_') || '%' ESCAPE '^'"""
37+
for schema in schemata
38+
)
39+
_completion_matches.extend(
40+
row[0]
41+
for row in cursor.execute(
42+
" UNION ".join(select_clauses), {"text": text}
43+
)
44+
)
45+
# columns
46+
try:
47+
select_clauses = (
48+
f"""\
49+
SELECT pti.name || ' ' FROM "{schema}".sqlite_master AS sm
50+
JOIN pragma_table_xinfo(sm.name,'{schema}') AS pti
51+
WHERE sm.type='table' AND
52+
pti.name LIKE REPLACE(:text, '_', '^_') || '%' ESCAPE '^'"""
53+
for schema in schemata
54+
)
55+
_completion_matches.extend(
56+
row[0]
57+
for row in cursor.execute(
58+
" UNION ".join(select_clauses), {"text": text}
59+
)
60+
)
61+
except OperationalError:
62+
# skip on SQLite<3.16.0 where pragma table-valued function is
63+
# not supported yet
64+
pass
65+
# functions
66+
try:
67+
_completion_matches.extend(
68+
row[0] for row in cursor.execute("""\
69+
SELECT DISTINCT UPPER(name) || '('
70+
FROM pragma_function_list()
71+
WHERE name NOT IN ('->', '->>') AND
72+
name LIKE REPLACE(:text, '_', '^_') || '%' ESCAPE '^'""",
73+
{"text": text},
74+
)
75+
)
76+
except OperationalError:
77+
# skip on SQLite<3.30.0 where function_list is not supported yet
78+
pass
79+
# schemata
80+
text_lower = text.lower()
81+
_completion_matches.extend(c for c in schemata
82+
if c.lower().startswith(text_lower))
83+
_completion_matches = sorted(set(_completion_matches))
2284
try:
23-
return _completion_matches[state] + " "
85+
return _completion_matches[state]
2486
except IndexError:
2587
return None
2688

2789

2890
@contextmanager
29-
def completer():
91+
def completer(con):
3092
try:
3193
import readline
3294
except ImportError:
3395
yield
3496
return
3597

3698
old_completer = readline.get_completer()
99+
def complete(text, state):
100+
return _complete(con, text, state)
37101
try:
38-
readline.set_completer(_complete)
102+
readline.set_completer(complete)
39103
if readline.backend == "editline":
40104
# libedit uses "^I" instead of "tab"
41105
command_string = "bind ^I rl_complete"

‎Lib/test/test_sqlite3/test_cli.py‎

Lines changed: 125 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -216,10 +216,6 @@ class Completion(unittest.TestCase):
216216

217217
@classmethod
218218
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-
223219
readline = import_module("readline")
224220
if readline.backend == "editline":
225221
raise unittest.SkipTest("libedit readline is not supported")
@@ -229,12 +225,24 @@ def write_input(self, input_, env=None):
229225
import readline
230226
from sqlite3.__main__ import main
231227
228+
# Configure readline to ...:
229+
# - hide control sequences surrounding each candidate
230+
# - hide "Display all xxx possibilities? (y or n)"
231+
# - show candidates one per line
232232
readline.parse_and_bind("set colored-completion-prefix off")
233+
readline.parse_and_bind("set completion-query-items 0")
234+
readline.parse_and_bind("set page-completions off")
235+
readline.parse_and_bind("set completion-display-width 0")
236+
233237
main()
234238
""")
235239
return run_pty(script, input_, env)
236240

237241
def test_complete_sql_keywords(self):
242+
_sqlite3 = import_module("_sqlite3")
243+
if not hasattr(_sqlite3, "SQLITE_KEYWORDS"):
244+
raise unittest.SkipTest("unable to determine SQLite keywords")
245+
238246
# List candidates starting with 'S', there should be multiple matches.
239247
input_ = b"S\t\tEL\t 1;\n.quit\n"
240248
output = self.write_input(input_)
@@ -254,6 +262,118 @@ def test_complete_sql_keywords(self):
254262
output = self.write_input(input_)
255263
self.assertIn(b".version", output)
256264

265+
def test_complete_table_indexes_triggers_views(self):
266+
input_ = textwrap.dedent("""\
267+
CREATE TABLE _Table (id);
268+
CREATE INDEX _Index ON _table (id);
269+
CREATE TRIGGER _Trigger BEFORE INSERT
270+
ON _Table BEGIN SELECT 1; END;
271+
CREATE VIEW _View AS SELECT 1;
272+
273+
CREATE TEMP TABLE _Temp_table (id);
274+
CREATE INDEX temp._Temp_index ON _Temp_table (id);
275+
CREATE TEMP TRIGGER _Temp_trigger BEFORE INSERT
276+
ON _Table BEGIN SELECT 1; END;
277+
CREATE TEMP VIEW _Temp_view AS SELECT 1;
278+
279+
ATTACH ':memory:' AS attached;
280+
CREATE TABLE attached._Attached_table (id);
281+
CREATE INDEX attached._Attached_index ON _Attached_table (id);
282+
CREATE TRIGGER attached._Attached_trigger BEFORE INSERT
283+
ON _Attached_table BEGIN SELECT 1; END;
284+
CREATE VIEW attached._Attached_view AS SELECT 1;
285+
286+
SELECT id FROM _\t\tta\t;
287+
.quit\n""").encode()
288+
output = self.write_input(input_)
289+
lines = output.decode().splitlines()
290+
indices = [i for i, line in enumerate(lines)
291+
if line.startswith(self.PS1)]
292+
start, end = indices[-3], indices[-2]
293+
candidates = [l.strip() for l in lines[start+1:end]]
294+
self.assertEqual(candidates,
295+
[
296+
"_Attached_index",
297+
"_Attached_table",
298+
"_Attached_trigger",
299+
"_Attached_view",
300+
"_Index",
301+
"_Table",
302+
"_Temp_index",
303+
"_Temp_table",
304+
"_Temp_trigger",
305+
"_Temp_view",
306+
"_Trigger",
307+
"_View",
308+
],
309+
)
310+
start, end = indices[-2], indices[-1]
311+
# direct match with '_Table' completed, no candidates displayed
312+
candidates = [l.strip() for l in lines[start+1:end]]
313+
self.assertEqual(len(candidates), 0)
314+
315+
@unittest.skipIf(sqlite3.sqlite_version_info < (3, 16, 0),
316+
"PRAGMA table-valued function is not available until "
317+
"SQLite 3.16.0")
318+
def test_complete_columns(self):
319+
input_ = textwrap.dedent("""\
320+
CREATE TABLE _table (_col_table);
321+
CREATE TEMP TABLE _temp_table (_col_temp);
322+
ATTACH ':memory:' AS attached;
323+
CREATE TABLE attached._attached_table (_col_attached);
324+
325+
SELECT _col_\t\tta\tFROM _table;
326+
.quit\n""").encode()
327+
output = self.write_input(input_)
328+
lines = output.decode().splitlines()
329+
indices = [
330+
i for i, line in enumerate(lines) if line.startswith(self.PS1)
331+
]
332+
start, end = indices[-3], indices[-2]
333+
candidates = [l.strip() for l in lines[start+1:end]]
334+
335+
self.assertEqual(
336+
candidates, ["_col_attached", "_col_table", "_col_temp"]
337+
)
338+
339+
@unittest.skipIf(sqlite3.sqlite_version_info < (3, 30, 0),
340+
"PRAGMA function_list is not available until "
341+
"SQLite 3.30.0")
342+
def test_complete_functions(self):
343+
input_ = b"SELECT AV\t1);\n.quit\n"
344+
output = self.write_input(input_)
345+
self.assertIn(b"AVG(1);", output)
346+
self.assertIn(b"(1.0,)", output)
347+
348+
# Functions are completed in upper case for even lower case user input.
349+
input_ = b"SELECT av\t1);\n.quit\n"
350+
output = self.write_input(input_)
351+
self.assertIn(b"AVG(1);", output)
352+
self.assertIn(b"(1.0,)", output)
353+
354+
def test_complete_schemata(self):
355+
input_ = textwrap.dedent("""\
356+
ATTACH ':memory:' AS MixedCase;
357+
-- Test '_' is escaped in Like pattern filtering
358+
ATTACH ':memory:' AS _underscore;
359+
-- Let database_list pragma have a 'temp' schema entry
360+
CREATE TEMP TABLE _table (id);
361+
362+
SELECT * FROM \t\tmIX\t.sqlite_master;
363+
SELECT * FROM _und\t.sqlite_master;
364+
.quit\n""").encode()
365+
output = self.write_input(input_)
366+
lines = output.decode().splitlines()
367+
indices = [
368+
i for i, line in enumerate(lines) if line.startswith(self.PS1)
369+
]
370+
start, end = indices[-4], indices[-3]
371+
candidates = [l.strip() for l in lines[start+1:end]]
372+
self.assertIn("MixedCase", candidates)
373+
self.assertIn("_underscore", candidates)
374+
self.assertIn("main", candidates)
375+
self.assertIn("temp", candidates)
376+
257377
@unittest.skipIf(sys.platform.startswith("freebsd"),
258378
"Two actual tabs are inserted when there are no matching"
259379
" completions in the pseudo-terminal opened by run_pty()"
@@ -274,8 +394,6 @@ def test_complete_no_match(self):
274394
self.assertEqual(line_num, len(lines))
275395

276396
def test_complete_no_input(self):
277-
from _sqlite3 import SQLITE_KEYWORDS
278-
279397
script = textwrap.dedent("""
280398
import readline
281399
from sqlite3.__main__ import main
@@ -306,7 +424,7 @@ def test_complete_no_input(self):
306424
self.assertEqual(len(indices), 2)
307425
start, end = indices
308426
candidates = [l.strip() for l in lines[start+1:end]]
309-
self.assertEqual(candidates, sorted(SQLITE_KEYWORDS))
427+
self.assertEqual(candidates, sorted(candidates))
310428
except:
311429
if verbose:
312430
print(' PTY output: '.center(30, '-'))

‎Misc/NEWS.d/next/Library/2025-06-29-22-01-00.gh-issue-133390.I1DW_3.rst‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
Support table, index, trigger, view, column, function, and schema completion
2+
for :mod:`sqlite3`'s :ref:`command-line interface <sqlite3-cli>`.

0 commit comments

Comments
 (0)
Back | FazBrowse Home | New Git URL