Skip to content

feat: enhance .tables command with schema disambiguation and filtering #18641

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
Mytherin merged 3 commits into from
Aug 26, 2025
Merged

feat: enhance .tables command with schema disambiguation and filtering #18641

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
8da5cbe
feat: enhance .tables command with schema disambiguation and filtering
shivampr Aug 17, 2025
73d65d5
style: apply code formatting fixes
shivampr Aug 20, 2025
8124263
fix: use qualified name parser for .tables pattern parsing
shivampr Aug 22, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
101 changes: 78 additions & 23 deletions tools/shell/shell.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -81,6 +81,7 @@
#include <assert.h>
#include "duckdb_shell_wrapper.h"
#include "duckdb/common/box_renderer.hpp"
#include "duckdb/parser/qualified_name.hpp"
#include "sqlite3.h"
typedef sqlite3_int64 i64;
typedef sqlite3_uint64 u64;
Expand Down Expand Up @@ -4033,37 +4034,78 @@ MetadataResult ShellState::DisplayEntries(const char **azArg, idx_t nArg, char t
int ii;
string s;
OpenDB(0);
// rc = sqlite3_prepare_v2(db, "PRAGMA database_list", -1, &pStmt, 0);
// if( rc ){
// sqlite3_finalize(pStmt);
// return shellDatabaseError(db);
// }

if (nArg > 2) {
return MetadataResult::PRINT_USAGE;
}
// for(ii=0; sqlite3_step(pStmt)==SQLITE_ROW; ii++){
// const char *zDbName = (const char*)sqlite3_column_text(pStmt, 1);
// if( zDbName==0 ) continue;
// if( s.z && s.z[0] ) appendText(&s, " UNION ALL ", 0);
appendText(s, "SELECT name FROM ", 0);
// appendText(&s, zDbName, '"');
appendText(s, "sqlite_schema ", 0);

// Parse the filter pattern to check for schema qualification
string filter_pattern = nArg > 1 ? azArg[1] : "%";
string schema_filter = "";
string table_filter = filter_pattern;

// Parse the filter pattern to check for schema qualification
try {
auto components = duckdb::QualifiedName::ParseComponents(filter_pattern);
if (components.size() >= 2) {
// e.g : "schema.table" or "schema.%"
schema_filter = components[0];
table_filter = components[1];
// e.g : "schema."
if (table_filter.empty()) {
table_filter = "%";
}
}
} catch (const duckdb::ParserException &) {
// If parsing fails, treat as a simple table pattern
schema_filter = "";
table_filter = filter_pattern;
}

// Use DuckDB's system tables instead of SQLite's sqlite_schema
if (type == 't') {
appendText(s,
" WHERE type IN ('table','view')"
" AND name NOT LIKE 'sqlite_%'"
" AND name LIKE ?1",
0);
// For tables, we need to handle schema disambiguation
appendText(s, "WITH all_objects AS (", 0);
appendText(s, " SELECT schema_name, table_name as name FROM duckdb_tables", 0);
if (!schema_filter.empty()) {
appendText(s, " WHERE schema_name LIKE ?1", 0);
}
appendText(s, " UNION ALL", 0);
appendText(s, " SELECT schema_name, view_name as name FROM duckdb_views", 0);
if (!schema_filter.empty()) {
appendText(s, " WHERE schema_name LIKE ?1", 0);
}
appendText(s, "),", 0);
appendText(s, "name_counts AS (", 0);
appendText(s, " SELECT name, COUNT(*) as count FROM all_objects", 0);
appendText(s, " GROUP BY name", 0);
appendText(s, "),", 0);
appendText(s, "disambiguated AS (", 0);
appendText(s, " SELECT", 0);
appendText(s, " CASE", 0);
appendText(s, " WHEN nc.count > 1 THEN ao.schema_name || '.' || ao.name", 0);
appendText(s, " ELSE ao.name", 0);
appendText(s, " END as display_name", 0);
appendText(s, " FROM all_objects ao", 0);
appendText(s, " JOIN name_counts nc ON ao.name = nc.name", 0);
if (!schema_filter.empty()) {
appendText(s, " WHERE ao.name LIKE ?2", 0);
} else {
appendText(s, " WHERE ao.name LIKE ?1", 0);
}
appendText(s, ")", 0);
appendText(s, "SELECT DISTINCT display_name FROM disambiguated ORDER BY display_name", 0);
} else {
// For indexes, use the original SQLite approach
appendText(s, "SELECT name FROM ", 0);
appendText(s, "sqlite_schema ", 0);
appendText(s,
" WHERE type='index'"
" AND tbl_name LIKE ?1",
0);
appendText(s, " ORDER BY 1", 0);
}
// }
// rc = sqlite3_finalize(pStmt);
appendText(s, " ORDER BY 1", 0);

int rc = sqlite3_prepare_v2(db, s.c_str(), -1, &pStmt, 0);
if (rc) {
return MetadataResult::FAIL;
Expand All @@ -4073,11 +4115,24 @@ MetadataResult ShellState::DisplayEntries(const char **azArg, idx_t nArg, char t
** as an array of nul-terminated strings in azResult[]. */
nRow = nAlloc = 0;
azResult = nullptr;
if (nArg > 1) {
sqlite3_bind_text(pStmt, 1, azArg[1], -1, SQLITE_TRANSIENT);

if (type == 't') {
// Bind parameters for the new DuckDB query
if (!schema_filter.empty()) {
sqlite3_bind_text(pStmt, 1, schema_filter.c_str(), -1, SQLITE_TRANSIENT);
sqlite3_bind_text(pStmt, 2, table_filter.c_str(), -1, SQLITE_TRANSIENT);
} else {
sqlite3_bind_text(pStmt, 1, filter_pattern.c_str(), -1, SQLITE_TRANSIENT);
}
} else {
sqlite3_bind_text(pStmt, 1, "%", -1, SQLITE_STATIC);
// Original binding for indexes
if (nArg > 1) {
sqlite3_bind_text(pStmt, 1, azArg[1], -1, SQLITE_TRANSIENT);
} else {
sqlite3_bind_text(pStmt, 1, "%", -1, SQLITE_STATIC);
}
}

while (sqlite3_step(pStmt) == SQLITE_ROW) {
if (nRow >= nAlloc) {
char **azNew;
Expand Down
61 changes: 61 additions & 0 deletions tools/shell/tests/test_shell_basics.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -422,6 +422,56 @@ def test_tables_pattern(shell):
result = test.run()
result.check_stdout("asda csda")

def test_tables_schema_disambiguation(shell):
test = (
ShellTest(shell)
.statement("CREATE SCHEMA a;")
.statement("CREATE SCHEMA b;")
.statement("CREATE TABLE a.foobar(name VARCHAR);")
.statement("CREATE TABLE b.foobar(name VARCHAR);")
.statement(".tables")
)
result = test.run()
result.check_stdout("a.foobar b.foobar")

def test_tables_schema_filtering(shell):
test = (
ShellTest(shell)
.statement("CREATE SCHEMA a;")
.statement("CREATE SCHEMA b;")
.statement("CREATE TABLE a.foobar(name VARCHAR);")
.statement("CREATE TABLE b.foobar(name VARCHAR);")
.statement("CREATE TABLE a.unique_table(x INTEGER);")
.statement("CREATE TABLE b.other_table(y INTEGER);")
.statement(".tables a.%")
)
result = test.run()
result.check_stdout("foobar unique_table")

def test_tables_backward_compatibility(shell):
test = (
ShellTest(shell)
.statement("CREATE TABLE main_table(i INTEGER);")
.statement("CREATE TABLE unique_table(x INTEGER);")
.statement(".tables")
)
result = test.run()
result.check_stdout("main_table unique_table")

def test_tables_with_views(shell):
test = (
ShellTest(shell)
.statement("CREATE SCHEMA a;")
.statement("CREATE SCHEMA b;")
.statement("CREATE TABLE a.foobar(name VARCHAR);")
.statement("CREATE TABLE b.foobar(name VARCHAR);")
.statement("CREATE VIEW a.test_view AS SELECT 1 AS x;")
.statement("CREATE VIEW b.test_view AS SELECT 2 AS y;")
.statement(".tables")
)
result = test.run()
result.check_stdout("a.foobar a.test_view b.foobar b.test_view")

def test_indexes(shell):
test = (
ShellTest(shell)
Expand Down Expand Up @@ -1072,4 +1122,15 @@ def test_shell_csv_file(shell):
result = test.run()
result.check_stdout("2008-08-10")

def test_tables_invalid_pattern_handling(shell):
test = (
ShellTest(shell)
.statement("CREATE TABLE test_table(i INTEGER);")
.statement(".tables \"invalid\"pattern\"")
)
result = test.run()
# Should show usage message for invalid pattern
result.check_stderr("Usage: .tables ?TABLE?")


# fmt: on
Loading