0

I need programmatically edit SQL commands in such way that each table in the SQL command will have an alias. The input is a schema for the database and an SQL command. The output should be an SQL query where every table and has an alias and it is always used when we reference an attribute of that table.

For example, let us have a database person(id, name, salary, did) and department(did, name) and the following SQL command:

select id, t.did, maxs
from person
join (
  select did, max(salary) maxs
  from person
  group by did
) t on t.maxs = salary and person.did = t.did

The expected result for such input would be 

select p1.id, t.did, t.maxs
from person p1
join (
  select p2.did, max(p2.salary) maxs
  from person p2
  group by p2.did
) t on t.maxs = p1.salary and p1.did = t.did

I was considering using ANTLR4 for this, however, I was curious whether there is a simpler solution. I recently come across TSqlParser, is it possible to use this class to achieve such rewrite in some simple way?

Improve this question
8
  • 3
    If you are using a tool to generate your sql what is the point of using an alias? Aliases are used to make it easier for the coder. Commented Oct 31, 2017 at 14:13
  • @SeanLange aliases are also good that you can easily assign each attribute to a table (or inline view) in the SQL without the need to have a database schema. Commented Oct 31, 2017 at 14:19
  • If your situation is limited to one table per query, then you can write a parser in any language you are comfortable with. If the queries have joins, where the query author has not explicitly indicated which columns belong to which tables, then you will have issues automating the process, unless you also have access to the schema. Commented Oct 31, 2017 at 14:20
  • Look at your example and ask yourself how in the world that could be automated. It would take years to write a parser that could handle that level of complexity. Commented Oct 31, 2017 at 14:21
  • 2
    @SeanLange . . . If the original query is syntactically correct, then the references can be disambiguated. This parsing is no harder (and is perhaps simpler) than compiling a SQL query, so it is definitely possible. I am not familiar with the TSqlParser class; perhaps it does what the OP wants. Commented Oct 31, 2017 at 14:27
Related questions
0
Trying to use table aliases in SQL
15
Multiple Alias names for a table
0
getting sql table name aliases from SP to ASP.NET C# code
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.