I have been trying to get all column vs. table regardless of alias used for example assume this query
SELECT e.name,
(SELECT COUNT(*) FROM projects AS p WHERE p.employee_id = e.employee_id) AS project_count,
(SELECT COUNT(*) FROM tasks AS p WHERE p.employee_id = e.employee_id) AS task_count
FROM employees AS e;
Considering that alias name can be reused across different scopes I can't simply collect details like column, table and alias and do the work at last How I can do that, below is my try If you have any suggestion or any other algorithm please let me know thanks.
What I am currently doing is resolving tables for an given alias at end of each scope below is the code,
import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.function.Supplier;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.ParenthesedFromItem;
import net.sf.jsqlparser.statement.select.ParenthesedSelect;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SelectVisitor;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.util.TablesNamesFinder;
// I used this class to store data on different scopes when exiting from that scope i simply pop
class ScopedData<T> implements Iterable<T> {
protected final LinkedList<T> scopes = new LinkedList<>();
private final Supplier<T> creator;
public ScopedData(Supplier<T> creator) {
this.creator = creator;
}
public void enterScope() {
scopes.push(creator.get());
}
public void exitScope() {
if (scopes.isEmpty()) {
throw new IllegalStateException("No scope to exit");
} else {
scopes.pop();
}
}
public List<T> scopes() {
return Collections.unmodifiableList(scopes);
}
public T current() {
if (scopes.isEmpty()) {
throw new IllegalStateException("No scope to get");
} else {
return this.scopes.peek();
}
}
@Override
public Iterator<T> iterator() {
return this.scopes.descendingIterator();
}
}
// I am using different class than TablesNamesFinder but it is almost same as that
class SqlTokenVisitor<T> extends TablesNamesFinder<T> {
// I have overridden this method because I have changed the order of visiting from in my class
@Override
public <S> T visit(PlainSelect plainSelect, S context) {
List<WithItem> withItemsList = plainSelect.getWithItemsList();
if (withItemsList != null && !withItemsList.isEmpty()) {
for (WithItem withItem : withItemsList) {
withItem.accept((SelectVisitor<?>) this, context);
}
}
if (plainSelect.getFromItem() != null) {
plainSelect.getFromItem().accept(this, context);
}
if (plainSelect.getJoins() != null) {
visitJoins(plainSelect.getJoins(), context);
}
if (plainSelect.getWhere() != null) {
plainSelect.getWhere().accept(this, context);
}
if (plainSelect.getHaving() != null) {
plainSelect.getHaving().accept(this, context);
}
if (plainSelect.getOracleHierarchical() != null) {
plainSelect.getOracleHierarchical().accept(this, context);
}
if (plainSelect.getSelectItems() != null) {
for (SelectItem<?> item : plainSelect.getSelectItems()) {
item.accept(this, context);
}
}
return null;
}
private <S> T visitJoins(List<Join> joins, S context) {
for (Join join : joins) {
join.getFromItem().accept(this, context);
join.getRightItem().accept(this, context);
for (Expression expression : join.getOnExpressions()) {
expression.accept(this, context);
}
}
return null;
}
public SqlTokenVisitor() {
this.init(true);
}
}
/**
* Scoped visitor helps to process query in scoped manner for example assume this query,
*
* ```
* SELECT e.name, // new scope (select 1)
* (SELECT COUNT(*) FROM projects AS p WHERE p.employee_id = e.employee_id // new scope (select 2)) AS project_count,
* (SELECT COUNT(*) FROM tasks AS p WHERE p.employee_id = e.employee_id // new scope (select 3)) AS task_count
* FROM employees AS e;
* ```
*
* parse_statement(select_1):
* push_state(stack, select_1)
* parse_statement(select_2),
* push_state(stack, select_2)
* use_state(stack)
* pop_state(stack)
* parse_statement(select_3)
* push_state(stack, select_3)
* use_state(stack)
* pop_state(stack)
* use_state(stack)
* pop_state(stack)
*
* we are doing pop since used data no more needed also it helps to reuse the same variable
* in different scope. Another use of stack is assume
*
* ```
* select a.name, (select a.name from table1 as a) from table2 as a
* ```
*
* upon pushing state of sub select it hides the outer variable `a` since it is located at
* top and we look up symbol from top of stack which is good since we need to use nearest
* matching variable.
*
* In below implementation we simply call enterScope before visit and exitScope
* after visit Since the parser parses the Elements as AST tree we can simply
* use that,
*
* enteringScope() <- 1
* select
* |
* |-- enteringScope() <- 2
* |-- select
* |-- exitingScope() <- 2
* |
* |-- enteringScope() <- 3
* |-- select
* |-- exitingScope() <- 3
* |
* exitingScope() <- 1
*/
abstract class ScopedVisitor<T> extends SqlTokenVisitor<T> {
@Override
public <S> T visit(PlainSelect select, S context) {
this.enteringScope();
super.visit(select, context);
this.exitingScope();
return null;
}
public abstract void enteringScope();
public abstract void exitingScope();
}
class ColumnVisitor extends ScopedVisitor<Void> {
private void processScope(ScopeData scopeData) {
for (Column column : scopeData.columns) {
String resolvedTable = this.resolveAlias(column.getTable()).getName();
System.out.println(column.getColumnName() + " : " + resolvedTable);
}
}
private Table resolveAlias(Table alias) {
for (ScopeData data : scopedData) {
Table resolved = resolveAlias(data.tables, alias);
if (resolved != null) {
return resolved;
}
}
return alias;
}
public Table resolveAlias(List<Table> tables, Table alias) {
for (Table table : tables) {
if (table.getAlias() != null && table.getAlias().getName().equals(alias.getName())) {
return table;
}
}
return null;
}
@Override
public <S> Void visit(LateralSubSelect lateralSubSelect, S context) {
if (lateralSubSelect.getAlias() != null) {
this.scopedData.current().otherTables.add(lateralSubSelect.getAlias().getName());
}
super.visit(lateralSubSelect, context);
return null;
}
@Override
public <S> Void visit(WithItem withItem, S context) {
this.scopedData.current().otherTables.add(withItem.getAlias().getName());
super.visit(withItem, context);
return null;
}
@Override
public <S> Void visit(ParenthesedSelect select, S context) {
if (select.getAlias() != null) {
this.scopedData.current().otherTables.add(select.getAlias().getName());
}
super.visit(select, context);
return null;
}
@Override
public <S> Void visit(ParenthesedFromItem parenthesis, S context) {
if (parenthesis.getAlias() != null) {
this.scopedData.current().otherTables.add(parenthesis.getAlias().getName());
}
super.visit(parenthesis, context);
return null;
}
@Override
public <S> Void visit(Column column, S context) {
super.visit(column, context);
if (column.getTable() != null) {
this.scopedData.current().columns.add(column);
}
return null;
}
@Override
public <S> Void visit(Table table, S context) {
super.visit(table, context);
this.scopedData.current().tables.add(table);
return null;
}
@Override
public void enteringScope() {
this.scopedData.enterScope();
}
@Override
public void exitingScope() {
this.processScope(this.scopedData.current());
this.scopedData.exitScope();
}
private static class ScopeData {
public final List<Column> columns = new LinkedList<>();
public final List<Table> tables = new LinkedList<>();
public final List<String> otherTables = new LinkedList<>();
}
private final ScopedData<ScopeData> scopedData = new ScopedData<>(ScopeData::new);
}
public class Main {
public static void main(String[] args) throws JSQLParserException {
String query = "SELECT e.name, \n" + //
" (SELECT COUNT(*) FROM projects AS p WHERE p.employee_id = e.employee_id) AS project_count,\n" + //
" (SELECT COUNT(*) FROM tasks AS p WHERE p.employee_id = e.employee_id) AS task_count\n" + //
"FROM employees AS e;";
Statement stmt = CCJSqlParserUtil.parse(query);
stmt.accept(new ColumnVisitor());
}
}
Output
employee_id : projects
employee_id : employees
employee_id : tasks
employee_id : employees
name : employees
You can find jsqlparser in github