Skip to content

Advanced Features

Complete the learning path by turning parsed SQL into reusable analysis and transformation workflows. The examples cover both SDK editions where their APIs or traversal behavior differ.

What you will learn

  • Modify a parsed AST and re-emit the SQL two different ways
  • Walk the AST with a custom visitor
  • Split multi-statement scripts without corrupting procedure bodies
  • Apply performance techniques that match how the parser actually behaves

Before you begin

  • Completed all previous tutorials in the series
  • Working knowledge of Java or C#, and of SQL

1. Modify the AST, then re-emit the SQL

There are two ways to get SQL back out after mutating the tree, and they give noticeably different results. Rename a table by rewriting the token its name was lexed from, then compare both.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.scriptWriter.TScriptGenerator;

TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = "SELECT id, name, email FROM users WHERE active = 1";
parser.parse();

TCustomSqlStatement stmt = parser.sqlstatements.get(0);
for (int i = 0; i < stmt.tables.size(); i++) {
    TTable table = stmt.tables.getTable(i);
    if ("users".equalsIgnoreCase(table.getName())) {
        table.getTableName().getStartToken().astext = "workforce";
    }
}

// (a) regenerate from the tree
System.out.println(new TScriptGenerator().generateScript(stmt));

// (b) re-emit the token list
StringBuilder sb = new StringBuilder();
for (int i = 0; i < parser.sourcetokenlist.size(); i++) {
    sb.append(parser.sourcetokenlist.get(i).astext);
}
System.out.println(sb.toString());

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
using System;
using System.Text;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.nodes;
using gudusoft.gsqlparser.scriptWriter;

var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = "SELECT id, name, email FROM users WHERE active = 1";
parser.parse();

var stmt = parser.sqlstatements.get(0);
for (int i = 0; i < stmt.tables.size(); i++)
{
    TTable table = stmt.tables.getTable(i);
    if (string.Equals(table.Name, "users", StringComparison.OrdinalIgnoreCase))
        table.TableName.startToken.astext = "workforce";
}

// (a) regenerate from the tree
Console.WriteLine(new TScriptGenerator().generateScript(stmt));

// (b) re-emit the token list
var sb = new StringBuilder();
foreach (TSourceToken t in parser.sourcetokenlist) sb.Append(t.astext);
Console.WriteLine(sb.ToString());

Output, identical in both editions:

1
2
3
4
5
6
select 
id,name,email
 from 
workforce
 where active = 1
SELECT id, name, email FROM workforce WHERE active = 1

Both applied the rename. The difference is everything else:

Approach Result
TScriptGenerator.generateScript(node) Rebuilds SQL from the tree. Normalises keywords to lower case and puts clauses on their own lines. Your original formatting and comments are gone.
Re-emitting sourcetokenlist Byte-for-byte the original text with only your edit applied. Comments, casing and whitespace survive.

Pick the token list for rewrites, the generator for canonical output

If your job is "change one thing in the customer's SQL and hand it back", re-emit the token list. generateScript is for when you want normalised output and do not care about the input's shape.

2. Custom visitors

Subclass TParseTreeVisitor and override the preVisit overload for the node type you care about. There are hundreds of overloads (949 in Java, 528 in .NET), one per node type, and the framework dispatches on runtime type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
import gudusoft.gsqlparser.nodes.TParseTreeVisitor;
import gudusoft.gsqlparser.nodes.TTable;
import java.util.LinkedHashSet;
import java.util.Set;

public class TableCollector extends TParseTreeVisitor {
    public final Set<String> tables = new LinkedHashSet<String>();

    public void preVisit(TTable table) {
        if (table.getTableName() != null) {
            tables.add(table.getTableName().toString());
        }
    }
}

Usage:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext =
    "SELECT u.id, p.title\n" +
    "FROM   users u\n" +
    "JOIN   posts p ON u.id = p.user_id\n" +
    "WHERE  u.id IN (SELECT user_id FROM banned_users)";
parser.parse();

TableCollector collector = new TableCollector();
parser.sqlstatements.get(0).acceptChildren(collector);

for (String t : collector.tables) {
    System.out.println("  - " + t);
}

Output:

1
2
3
4
  - join_expr
  - users
  - posts
  - banned_users

Note join_expr: a join node also presents as a TTable, so a naive collector picks up a pseudo-table. Filter it out, or check the table type before adding.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
using System;
using System.Collections.Generic;
using gudusoft.gsqlparser.nodes;

public class TableCollector : TParseTreeVisitor
{
    public HashSet<string> Tables { get; } = new(StringComparer.OrdinalIgnoreCase);

    public override void preVisit(TTable table)
    {
        if (table.TableName != null)
            Tables.Add(table.TableName.ToString());
    }
}

Usage:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = """
    SELECT u.id, p.title
    FROM   users u
    JOIN   posts p ON u.id = p.user_id
    WHERE  u.id IN (SELECT user_id FROM banned_users)
    """;
parser.parse();

var collector = new TableCollector();
parser.sqlstatements.get(0).acceptChildren(collector);

foreach (var t in collector.Tables)
    Console.WriteLine("  - " + t);

Output:

1
2
  - users
  - posts

Visitor traversal is NOT the same in both editions

The same visitor over the same SQL gives different answers, measured on Java 4.1.6 and .NET 4.1.0.7:

Java .NET
acceptChildren reaches the subquery's banned_users yes no
surfaces a join_expr pseudo-table yes no
accept (instead of acceptChildren) visits nodes visits nothing at all

Two consequences worth internalising before you build on this:

  • Do not assume a visitor sees subqueries on .NET. It does not. Recurse explicitly through stmt.Statements, or read stmt.tables per nested statement.
  • Use acceptChildren, not accept. On .NET, accept returned zero visits in every case tested, so a walk built on it silently does nothing.

stmt.tables has the same gap on .NET: for the SQL above it contains only users and posts. If you need every table including subqueries, walk the nested statements yourself — see Extract Table Names in the quick start.

3. Split a multi-statement script safely

A common instinct is to split a script on ;. Do not — it cuts procedure bodies in half, because the statements inside BEGIN ... END; contain their own semicolons. Use the parser's own splitter, getrawsqlstatements(), which understands the grammar.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext =
    "CREATE OR REPLACE PROCEDURE p AS BEGIN UPDATE t SET a=1; DELETE FROM t; END;\n" +
    "SELECT 1 FROM dual;";

parser.getrawsqlstatements();       // splits without fully parsing

System.out.println("statements: " + parser.sqlstatements.size());
for (int i = 0; i < parser.sqlstatements.size(); i++) {
    System.out.println("  [" + i + "] " + parser.sqlstatements.get(i).sqlstatementtype);
}
System.out.println("naive split(\";\") would give "
    + parser.sqltext.split(";").length + " fragments");

Output:

1
2
3
4
statements: 2
  [0] sstplsql_createprocedure
  [1] sstselect
naive split(";") would give 4 fragments

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext =
    "CREATE OR REPLACE PROCEDURE p AS BEGIN UPDATE t SET a=1; DELETE FROM t; END;\n" +
    "SELECT 1 FROM dual;";

parser.getrawsqlstatements();       // splits without fully parsing

Console.WriteLine($"statements: {parser.sqlstatements.size()}");
for (int i = 0; i < parser.sqlstatements.size(); i++)
    Console.WriteLine($"  [{i}] {parser.sqlstatements.get(i).sqlstatementtype}");
Console.WriteLine($"naive split(\";\") would give {parser.sqltext.Split(';').Length} fragments");

Output:

1
2
3
4
statements: 2
  [0] sstplsql_createprocedure
  [1] sstselect
naive split(";") would give 5 fragments

Two statements, correctly. The naive split produces four or five fragments depending on trailing-semicolon handling, and neither the procedure nor its body survives intact. This is not a theoretical concern — splitting on ; has produced measurably wrong dialect-coverage numbers in our own tooling.

4. Performance

The full picture, with measured numbers, is in Performance Considerations. The two things that matter most here:

The expensive step is the first parse, not constructing parsers

Constructing a second parser costs about 3 µs on Java and 218 µs on .NET. The first parse() in the process costs 1.8 s on Java and 0.6 s on .NET, because it initialises the grammar tables once.

So warm up at startup, off the request path, and do not build a pooling layer expecting it to recover the cold-start cost — it cannot.

Reuse a parser across a batch because it keeps the code simple:

1
2
3
4
5
6
TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
for (String sql : sqlBatch) {
    parser.sqltext = sql;
    if (parser.parse() != 0) continue;
    process(parser.sqlstatements);
}

1
2
3
4
5
6
7
var parser = new TGSqlParser(EDbVendor.dbvoracle);
foreach (string sql in sqlBatch)
{
    parser.sqltext = sql;
    if (parser.parse() != 0) continue;
    Process(parser.sqlstatements);
}

Tokenise when you only need lexical information — measured 7.5× faster on Java and 9.4× on .NET than a full parse:

1
2
3
4
5
6
parser.sqltext = sql;
parser.tokenizeSqltext();
for (int i = 0; i < parser.sourcetokenlist.size(); i++) {
    TSourceToken tok = parser.sourcetokenlist.get(i);
    System.out.printf("%-20s %s%n", tok.tokencode, tok.astext);
}

1
2
3
4
parser.sqltext = sql;
parser.tokenizeSqltext();
foreach (TSourceToken tok in parser.sourcetokenlist)
    Console.WriteLine($"{tok.tokencode,-20} {tok.astext}");

TGSqlParser is not thread-safe — give each thread its own instance via ThreadLocal. See Performance Considerations for the threading and memory guidance.

What you can do now

You learned how to:

  • Rename a table by rewriting its source token, and choose between generateScript (normalised) and token-list re-emission (source-preserving)
  • Write a TParseTreeVisitor subclass, and filter the join_expr pseudo-table
  • Recognise that visitor traversal differs between the editions, and that accept visits nothing on .NET
  • Split multi-statement scripts with getrawsqlstatements() instead of on ;
  • Warm up rather than pool, because the cost is the first parse

Put these patterns into production