Repository navigation
[FEATURE] Utility to find table names corresponding with SelectItems #1873
Description
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!
Activity
manticore-projects commentedon Oct 6, 2023
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_SCHEMAand orALL_TABLESorALL_TAB_COLUMNS.So from the
JSQLParserside, there is not much else we can do. We don't want to rely on or interfere with JDBC.manticore-projects commentedon Oct 6, 2023
That is not correct. You will get precise Column definitions for each table, as well as indices or primary keys or references.
rsmckinney commentedon Oct 6, 2023
Let me clarify. Sql Server and Oracle drivers fail to handle:
This is a known limitation with those drivers.
rsmckinney commentedon Oct 6, 2023
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 commentedon Oct 6, 2023
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.
manticore-projects commentedon Oct 6, 2023
Not in our scope because it relies on JDBC drivers and connection information and RDBMS specific stuff (like types).