Getting all column vs. table regardless of alias using jsqlparser?

[srilakshmikanthanp]

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);
    }
}

// I am using this class to provide common way to enter and exit from the scope
abstract class ScopedVisitor<T> extends SqlTokenVisitor<T> {
    @Override
    public <S> T visit(LateralSubSelect lateralSubSelect, S context) {
        this.enteringScope();
        super.visit(lateralSubSelect, context);
        this.exitingScope();
        return null;
    }

    @Override
    public <S> T visit(ParenthesedSelect select, S context) {
        this.enteringScope();
        super.visit(select, context);
        this.exitingScope();
        return null;
    }

    @Override
    public <S> T visit(Select select, S context) {
        this.enteringScope();
        super.visit(select, context);
        this.exitingScope();
        return null;
    }

    @Override
    public <S> T visit(ParenthesedFromItem parenthesis, S context) {
        this.enteringScope();
        super.visit(parenthesis, 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 original post on Code review

[manticore-projects]

Greetings!

Please have a look at https://manticore-projects.com/JSQLTranspiler/resolve.html for getting the correct column information and lineage. Its based on JSQLParser.

[srilakshmikanthanp]

Hi Thanks for replay. I want to resolve original table not alias for further processing it is possible with JSQLTranspiler? like for example in 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;

I want to get projects.employee_id, tasks.employee_id, employees.employee_id, employees.name

[manticore-projects]

Yes, for your query, JSQLTranspiler will resolve the following information. Please note, that JSQLTranspiler uses the actual schema definition, either from a JDBC Connection or from a provided POJO.
This ASCII presentation serves only as illustration and can be customized of course. The actual lineage will be returned as a JdbcResultSetMetaData object (without actually executing the query).

SELECT
 ├─employees.name : Other
 ├─project_count AS SELECT
 │  └─Function COUNT
 │     └─projects.employee_id : Other
 │  └─projects.employee_id : Other
 └─task_count AS SELECT
    └─Function COUNT
       └─tasks.employee_id : Other
    └─tasks.employee_id : Other

    // provide minimum schema information,
    // alternatively JSQLTranspiler can derive that from the Database itself when you provide a JDBC connection
    String[][] schemaDefinition = {
            {"projects", "employee_id"}
            , {"tasks", "employee_id"}
            , {"employees", "name"}
    };

    // the SQL Query to resolve
    String sqlStr =
            " 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"
            ;

    // The expected output in ASCII (alternatively JSON and XML is available)
    String expected =
            "SELECT\n" +
                    " ├─employees.name : Other\n" +
                    " ├─project_count AS SELECT\n" +
                    " │  └─Function COUNT\n" +
                    " │     └─projects.employee_id : Other\n" +
                    " │  └─projects.employee_id : Other\n" +
                    " └─task_count AS SELECT\n" +
                    "    └─Function COUNT\n" +
                    "       └─tasks.employee_id : Other\n" +
                    "    └─tasks.employee_id : Other\n"
            ;
    assertLineage(schemaDefinition, sqlStr, expected);

[srilakshmikanthanp]

Hi I have tried the tool I was great but does it give the column used in all the SQL query not only from select for example,

public class Main {
    public static void main(String[] args) throws Exception {
        String sql = "SELECT e.name, \n" + //
                "       (SELECT name FROM projects AS p WHERE p.employee_id = e.employee_id) AS project_count,\n" + //
                "       (SELECT name FROM tasks AS p WHERE p.employee_id = e.employee_id) AS task_count\n" + //
                "FROM employees AS e;";

        String[][] schemaDefinition = {
                { "projects", "employee_id", "name" }, { "tasks", "employee_id", "name" }, { "employees", "employee_id", "name" }
        };

        JSQLColumResolver resolver = new JSQLColumResolver(schemaDefinition);
        System.out.println(resolver.getLineage(XmlTreeBuilder.class, sql));
    }
}

It gives this,

<?xml version="1.0" encoding="UTF-8"?>
<ColumnSet>
  <Column name='name' table='employees' scope='employees.name' dataType='java.sql.Types.OTHER' typeName='Other' columnSize='0' decimalDigits='0' nullable=''/>
  <Column alias='project_count' name='name'>
   <ColumnSet>
     <Column name='name' table='projects' scope='projects.name' dataType='java.sql.Types.OTHER' typeName='Other' columnSize='0' decimalDigits='0' nullable=''/>
   </ColumnSet>
  </Column>
  <Column alias='task_count' name='name'>
   <ColumnSet>
     <Column name='name' table='tasks' scope='tasks.name' dataType='java.sql.Types.OTHER' typeName='Other' columnSize='0' decimalDigits='0' nullable=''/>
   </ColumnSet>
  </Column>
</ColumnSet>

because i want do some kind of column restriction using java so i can't allow user to use it anywhere even in where condition.