[FEATURE] Utility to find table names corresponding with SelectItems

[rsmckinney]

For instance, I want something like this:

PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(query);
List<SelectItem<?>> selectItems = select.getSelectItems();
. . .
String tableName = TableNamesFinder.findTable(selectItem, colName -> findTables(colName));

I want a findTable(SelectItem<?> selectItem, Function<String, Set<String>> tableMatcher) method where tableMatcher provides the parser with a lookup for tables having a specified column name. For my case I can supply this via JDBC. Note, selectItem must be a column defined in a table or the all-columns expression, otherwise the method returns null.

I know this type of utility is more on the semantics side of the equation, but I feel like this parser could begin to provide these kinds of services. Even better, build a resolver/backend based on JDBC where not only table names, but also data types etc. could be attached to the AST. Just a thought, considering most of the time a specific DBMS is in context when using this parser.

Btw, I need this utility as a backup for JDBC drivers that don't support ResultSetMetaData#getTableName(int columnIndex) properly; Server and Oracle don't provide table names corresponding with columns.

I'll probably begin writing this utility myself, but would appreciate feedback if you have any. Would be awesome to hear something similar is available!

[manticore-projects]

Greetings.

Its not possible with JSQLParser alone since the SQL itself does not provide enough information. You will always need a combination of the AST and MetaData.

MetaData of Oracle and MS SQL Server ARE sufficient (I do use that myself a lot).
However, there is also INFORMATIOn_SCHEMA and or ALL_TABLES or ALL_TAB_COLUMNS.

So from the JSQLParser side, there is not much else we can do. We don't want to rely on or interfere with JDBC.

[manticore-projects]

Server and Oracle don't provide table names corresponding with columns.

That is not correct. You will get precise Column definitions for each table, as well as indices or primary keys or references.

[rsmckinney]

That is not correct. You will get precise Column definitions for each table, as well as indices or primary keys or references.

Let me clarify. Sql Server and Oracle drivers fail to handle:

ResultSetMetaData#getTableName(int colIndex)

This is a known limitation with those drivers.

[rsmckinney]

Its not possible with JSQLParser alone since the SQL itself does not provide enough information. You will always need a combination of the AST and MetaData.

Yes, I mentioned that this request is more on the semantic side of the equation. I think it can be done with just table metadata and only column names corresponding with tables. This is primarily because column names must either be qualified or be non-ambiguous wrt target tables.

[manticore-projects]

That is not correct. You will get precise Column definitions for each table, as well as indices or primary keys or references.

Let me clarify. Sql Server and Oracle drivers fail to handle:

ResultSetMetaData#getTableName(int colIndex)

This is a known limitation with those drivers.

But you can get all Tables with Columns for a given schema.
You can get the tables names from TablesNameFinder and then browse your schema to get all the information you need.

[rsmckinney]

But you can get all Tables with Columns for a given schema.
You can get the tables names from TablesNameFinder and then browse your schema to get all the information you need.

Right. But in the context of a query TableNameFinder is insufficient. For instance, imagine a complex query with subselects and joins. Some of the subselects may reference a table having a column name matching one in the select list. This can create ambiguity when resolving the column's table in the select list. TableNameFinder produces the full set of tables in the query, not just the subset of tables corresponding with the select list. And without performing deeper static analysis, there is no way to resolve this narrower band of table names.

Anyway, I'm already building the utility as I described it. Thanks for responding to this issue so quickly.

[manticore-projects]

That is not correct. You will get precise Column definitions for each table, as well as indices or primary keys or references.

Let me clarify. Sql Server and Oracle drivers fail to handle:

ResultSetMetaData#getTableName(int colIndex)

This is a known limitation with those drivers.

But you can get all Tables with Columns for a given schema. You can get the tables names from TablesNameFinder and then browse your schema to get all the information you need.

Not in our scope because it relies on JDBC drivers and connection information and RDBMS specific stuff (like types).