Token position & replacement in complex queries

[devmaha]

Thank you for this amazing library
I have just started using it for an internal project where we need to modify certain parts of the query. I was wondering if there is a way for the parser to output the position of a token in the query string (so that I can modify it).

For e.g. in the queries below we want to identify the base table(s) and change that - which would be EMP and leads (placed inside the bracket) for below 2 queries

WITH EMP AS (SELECT name, age from EMP) SELECT NAME FROM EMP

select leads.name from (select * from leads) leads

Kindly guide.

I know that the library also allows to modify the query but I couldn't find a good way to use it in a complex query, so any guidance there is appreciated.

[georgesittas]

Can you explain the problem you're trying to solve through an example? Do you just need to put quotes around certain identifiers, that happen to be table references in your case?

[devmaha]

Apologies, I didn't elaborate the use-case better.

Our data model has gone through multiple changes - tables renamed, columns dropped/renamed. The challenge now is to take the existing set of queries and do the following

1. Find base (actual DB) tables
2. Do column disambiguation (i.e. find columns on those base tables)
3. Do inline replacements (of tables or columns) according to new model definitions

Challenges
named In-line views, named sub-queries, CTEs (and perhaps recursive sub queries) can have the same name as Base (actual) tables, so we can't do replace-all kind of refactoring.
We need to first differentiate between actual and named/temp tables -> find the position of base tables (and related columns) -> and then do replacement

sqlglot's support for multiple dialects got us into using it. Its AST tree has been helpful but what we aren't able to achieve is - identify a base table , get the location in query string (so that we can replace it). Or somehow replace it the table/column using any of the sqlglot functions.

Example

stmt='with emp(name) as (select * from emp) select name from emp'
parse_one(stmt)

(SELECT expressions:
  (COLUMN this:
    (IDENTIFIER this: name, quoted: False)), from:
  (FROM expressions:
    (TABLE this:
      (IDENTIFIER this: emp, quoted: False))), with:
  (WITH expressions:
    (CTE this:
      (SELECT expressions:
        (STAR ), from:
        (FROM expressions:
          (TABLE this:
            (IDENTIFIER this: emp, quoted: False)))), alias:
      (TABLEALIAS this:
        (IDENTIFIER this: emp, quoted: False), columns:
        (IDENTIFIER this: name, quoted: False)))))

In this above example we should be able to find the base emp table and then replace just that with say subject table

How should we go about it, kindly advise.

[tobymao]

You need to use the optimizer to qualify columns to find out references. Then you can use transform to replace references. This is an advanced task but SQLGlot has all the tools you need to do it. Study the optimizer examples and transforms.

[devmaha]

Excellent. Thanks for responding.
Let us check optimizer and transform modules and come back.