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?

Radim Bača
  • 10,646
  • 1
  • 19
  • 33
  • 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. – Sean Lange Oct 31 '17 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. – Radim Bača Oct 31 '17 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. – Peter Abolins Oct 31 '17 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. – Sean Lange Oct 31 '17 at 14:21
  • @PeterAbolins as I wrote, the database schema is an input – Radim Bača Oct 31 '17 at 14:21
  • @SeanLange I believe that my first query is syntactically correct and therefore each attribute can be uniquely assigned. – Radim Bača Oct 31 '17 at 14:27
  • 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. – Gordon Linoff Oct 31 '17 at 14:27
  • Maybe this will help you? https://stackoverflow.com/questions/1779737/using-the-tsqlparser – Peter Abolins Oct 31 '17 at 14:35

0 Answers0