Source code

001package gudusoft.gsqlparser.dlineage;
002
003import gudusoft.gsqlparser.*;
004import gudusoft.gsqlparser.dlineage.dataflow.model.*;
005import gudusoft.gsqlparser.dlineage.util.DlineageUtil;
006import gudusoft.gsqlparser.nodes.*;
007import gudusoft.gsqlparser.stmt.*;
008import gudusoft.gsqlparser.stmt.oracle.*;
009import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
010import gudusoft.gsqlparser.util.Logger;
011import gudusoft.gsqlparser.util.LoggerFactory;
012import gudusoft.gsqlparser.util.SQLUtil;
013
014import java.util.*;
015
016/**
017 * Handles Oracle pipelined table function cross-boundary data lineage stitching.
018 *
019 * <p>Two-phase approach:
020 * <ol>
021 *   <li>Index phase: indexes object types, collection types, and pipelined function signatures</li>
022 *   <li>Stitch phase: at call sites, appends lineage from function body to caller columns</li>
023 * </ol>
024 */
025public class PipelinedFunctionAnalyzer {
026
027    private static final Logger logger = LoggerFactory.getLogger(PipelinedFunctionAnalyzer.class);
028
029    private final ModelBindingManager modelManager;
030    private final ModelFactory modelFactory;
031    private final Option option;
032
033    public PipelinedFunctionAnalyzer(ModelBindingManager modelManager, ModelFactory modelFactory, Option option) {
034        this.modelManager = modelManager;
035        this.modelFactory = modelFactory;
036        this.option = option;
037    }
038
039    // ---- Phase 1: Index CREATE TYPE AS OBJECT ----
040
041    public void indexObjectType(TPlsqlCreateType stmt) {
042        if (!option.isEnablePipelinedStitching()) return;
043        if (stmt.getTypeAttributes() == null || stmt.getTypeAttributes().size() == 0) return;
044        if (stmt.getTypeName() == null) return;
045
046        String typeName = normalizeTypeName(stmt.getTypeName().toString());
047        List<String> fieldNames = new ArrayList<>();
048        for (int i = 0; i < stmt.getTypeAttributes().size(); i++) {
049            TTypeAttribute attr = stmt.getTypeAttributes().getAttributeItem(i);
050            if (attr.getAttributeName() != null) {
051                fieldNames.add(attr.getAttributeName().toString());
052            }
053        }
054        modelManager.indexObjectType(typeName, fieldNames);
055        // Also index by bare name (no schema prefix)
056        String bareName = getBareName(typeName);
057        if (!bareName.equals(typeName)) {
058            modelManager.indexObjectType(bareName, fieldNames);
059        }
060        logger.trace("Indexed object type: " + typeName + " (bare=" + bareName + "), fields=" + fieldNames);
061    }
062
063    // ---- Phase 1: Index TABLE OF collection types ----
064
065    public void indexCollectionType(TPlsqlTableTypeDefStmt stmt) {
066        if (!option.isEnablePipelinedStitching()) return;
067        if (stmt.getTypeName() == null || stmt.getElementDataType() == null) return;
068
069        String collectionName = normalizeTypeName(stmt.getTypeName().toString());
070        String elementTypeName = normalizeTypeName(stmt.getElementDataType().toString());
071        modelManager.indexCollectionType(collectionName, elementTypeName);
072        // Also index by bare name
073        String bareCollection = getBareName(collectionName);
074        if (!bareCollection.equals(collectionName)) {
075            modelManager.indexCollectionType(bareCollection, elementTypeName);
076        }
077        logger.trace("Indexed collection type: " + collectionName + " -> " + elementTypeName);
078    }
079
080    // ---- Phase 2: Detect pipelined function and build signature ----
081
082    public void analyzePipelinedFunction(TPlsqlCreateFunction func) {
083        if (!option.isEnablePipelinedStitching()) return;
084
085        // Scan body for PIPE ROW statements using iterative traversal
086        boolean hasPipeRow = false;
087        List<TPlsqlPipeRowStmt> pipeRowStmts = new ArrayList<>();
088        List<TAssignStmt> assignStmts = new ArrayList<>();
089        List<TLoopStmt> cursorLoops = new ArrayList<>();
090
091        Deque<TCustomSqlStatement> stack = new ArrayDeque<>();
092        // Push body statements
093        for (int i = func.getBodyStatements().size() - 1; i >= 0; i--) {
094            stack.push(func.getBodyStatements().get(i));
095        }
096        // Also check inner statements
097        for (int i = func.getStatements().size() - 1; i >= 0; i--) {
098            stack.push(func.getStatements().get(i));
099        }
100
101        while (!stack.isEmpty()) {
102            TCustomSqlStatement s = stack.pop();
103            if (s instanceof TPlsqlPipeRowStmt) {
104                pipeRowStmts.add((TPlsqlPipeRowStmt) s);
105                hasPipeRow = true;
106            } else if (s instanceof TAssignStmt) {
107                assignStmts.add((TAssignStmt) s);
108            } else if (s instanceof TLoopStmt) {
109                TLoopStmt loop = (TLoopStmt) s;
110                if (loop.getSubquery() != null || loop.getCursorName() != null) {
111                    cursorLoops.add(loop);
112                }
113                // Push loop body statements
114                for (int i = loop.getBodyStatements().size() - 1; i >= 0; i--) {
115                    stack.push(loop.getBodyStatements().get(i));
116                }
117            } else if (s instanceof TIfStmt) {
118                TIfStmt ifStmt = (TIfStmt) s;
119                if (ifStmt.getThenStatements() != null) {
120                    for (int i = ifStmt.getThenStatements().size() - 1; i >= 0; i--) {
121                        stack.push(ifStmt.getThenStatements().get(i));
122                    }
123                }
124                if (ifStmt.getElseStatements() != null) {
125                    for (int i = ifStmt.getElseStatements().size() - 1; i >= 0; i--) {
126                        stack.push(ifStmt.getElseStatements().get(i));
127                    }
128                }
129                if (ifStmt.getElseifStatements() != null) {
130                    for (int i = ifStmt.getElseifStatements().size() - 1; i >= 0; i--) {
131                        stack.push(ifStmt.getElseifStatements().get(i));
132                    }
133                }
134            } else if (s instanceof TCommonBlock) {
135                TCommonBlock block = (TCommonBlock) s;
136                if (block.getBodyStatements() != null) {
137                    for (int i = block.getBodyStatements().size() - 1; i >= 0; i--) {
138                        stack.push(block.getBodyStatements().get(i));
139                    }
140                }
141                if (block.getStatements() != null) {
142                    for (int i = block.getStatements().size() - 1; i >= 0; i--) {
143                        stack.push(block.getStatements().get(i));
144                    }
145                }
146            }
147            // Fallback: try to push sub-statements for any other compound statement type
148            else {
149                if (s.getStatements() != null && s.getStatements().size() > 0) {
150                    for (int i = s.getStatements().size() - 1; i >= 0; i--) {
151                        stack.push(s.getStatements().get(i));
152                    }
153                }
154                if (s instanceof TBlockSqlStatement) {
155                    TBlockSqlStatement blockStmt = (TBlockSqlStatement) s;
156                    if (blockStmt.getBodyStatements() != null && blockStmt.getBodyStatements().size() > 0) {
157                        for (int i = blockStmt.getBodyStatements().size() - 1; i >= 0; i--) {
158                            stack.push(blockStmt.getBodyStatements().get(i));
159                        }
160                    }
161                }
162            }
163        }
164
165        if (!hasPipeRow) return;
166
167        // Build signature
168        PipelinedFunctionSignature sig = new PipelinedFunctionSignature();
169        String funcName = func.getFunctionName() != null ? func.getFunctionName().toString() : "";
170
171        // Build function key with package context
172        String packagePrefix = "";
173        if (ModelBindingManager.getGlobalOraclePackage() != null) {
174            packagePrefix = ModelBindingManager.getGlobalOraclePackage().getName();
175            if (packagePrefix != null && !packagePrefix.isEmpty()) {
176                packagePrefix = packagePrefix + ".";
177            } else {
178                packagePrefix = "";
179            }
180        }
181
182        int argCount = func.getParameterDeclarations() != null ? func.getParameterDeclarations().size() : 0;
183
184        String fullKey = normalizeKey(packagePrefix + funcName);
185        String nakedKey = normalizeKey(getBareName(funcName));
186
187        sig.setFunctionKey(fullKey);
188        sig.setNakedKey(nakedKey);
189        sig.setArgCount(argCount);
190
191        // Resolve return type → collection type → element/object type → output columns
192        TTypeName returnType = func.getReturnDataType();
193        logger.trace("Pipelined func " + funcName + ": returnType=" + (returnType != null ? returnType.toString() : "null"));
194        if (returnType != null) {
195            String returnTypeName = normalizeTypeName(returnType.toString());
196            logger.trace("  returnTypeName normalized=" + returnTypeName);
197            // Try as collection type first
198            String elementType = modelManager.getElementTypeName(returnTypeName);
199            if (elementType == null) {
200                elementType = modelManager.getElementTypeName(getBareName(returnTypeName));
201            }
202            if (elementType != null) {
203                sig.setCollectionTypeKey(returnTypeName);
204                sig.setRowTypeKey(elementType);
205            } else {
206                // Maybe the return type IS the object type directly
207                sig.setRowTypeKey(returnTypeName);
208            }
209
210            // Resolve output columns from object type index
211            String rowTypeKey = sig.getRowTypeKey();
212            List<String> fields = null;
213            if (rowTypeKey != null) {
214                fields = modelManager.getObjectTypeFields(rowTypeKey);
215                if (fields == null) {
216                    fields = modelManager.getObjectTypeFields(getBareName(rowTypeKey));
217                }
218            }
219            if (fields != null) {
220                sig.setOutputColumns(new ArrayList<>(fields));
221            }
222        }
223
224        // Phase 3: Extract PIPE ROW lineage
225        if (sig.getOutputColumns() != null && !sig.getOutputColumns().isEmpty()) {
226            resolvePipeRowLineage(func, sig, pipeRowStmts, assignStmts, cursorLoops);
227        } else {
228            sig.setStatus(PipelinedFunctionSignature.ResolutionStatus.UNRESOLVED);
229        }
230
231        modelManager.addPipelinedSignature(sig);
232        logger.trace("Pipelined signature built: key=" + sig.getFunctionKey()
233                + ", nakedKey=" + sig.getNakedKey()
234                + ", resolved=" + sig.isResolved()
235                + ", status=" + sig.getStatus()
236                + ", outputCols=" + sig.getOutputColumns()
237                + ", lineageKeys=" + sig.getLineageByOutputColumn().keySet()
238                + ", lineageEntries=" + sig.getLineageByOutputColumn().size());
239        for (Map.Entry<String, List<PipelinedSourceRef>> entry : sig.getLineageByOutputColumn().entrySet()) {
240            logger.trace("  " + entry.getKey() + " <- " + entry.getValue());
241        }
242    }
243
244    // ---- Phase 3: PIPE ROW lineage extraction ----
245
246    private void resolvePipeRowLineage(TPlsqlCreateFunction func, PipelinedFunctionSignature sig,
247                                       List<TPlsqlPipeRowStmt> pipeRowStmts,
248                                       List<TAssignStmt> assignStmts,
249                                       List<TLoopStmt> cursorLoops) {
250        // Build cursor bindings from explicit CURSOR declarations
251        // Check both declare section (getStatements) and body (getBodyStatements)
252        Map<String, TSelectSqlStatement> cursorBindings = new LinkedHashMap<>();
253        for (int i = 0; i < func.getStatements().size(); i++) {
254            TCustomSqlStatement s = func.getStatements().get(i);
255            if (s instanceof TCursorDeclStmt) {
256                TCursorDeclStmt cursorDecl = (TCursorDeclStmt) s;
257                if (cursorDecl.getCursorName() != null && cursorDecl.getSubquery() != null) {
258                    cursorBindings.put(cursorDecl.getCursorName().toString().toLowerCase(), cursorDecl.getSubquery());
259                }
260            }
261        }
262        for (int i = 0; i < func.getBodyStatements().size(); i++) {
263            TCustomSqlStatement s = func.getBodyStatements().get(i);
264            if (s instanceof TCursorDeclStmt) {
265                TCursorDeclStmt cursorDecl = (TCursorDeclStmt) s;
266                if (cursorDecl.getCursorName() != null && cursorDecl.getSubquery() != null) {
267                    cursorBindings.put(cursorDecl.getCursorName().toString().toLowerCase(), cursorDecl.getSubquery());
268                }
269            }
270        }
271
272        // Build loop variable bindings: loopVar -> cursor SELECT
273        Map<String, TSelectSqlStatement> loopVarBindings = new LinkedHashMap<>();
274        for (TLoopStmt loop : cursorLoops) {
275            String loopVar = null;
276            if (loop.getRecordName() != null) {
277                loopVar = loop.getRecordName().toString().toLowerCase();
278            } else if (loop.getIndexName() != null) {
279                loopVar = loop.getIndexName().toString().toLowerCase();
280            }
281            if (loopVar == null) continue;
282
283            if (loop.getSubquery() != null) {
284                // Inline SELECT: FOR item IN (SELECT ...) LOOP
285                loopVarBindings.put(loopVar, loop.getSubquery());
286            } else if (loop.getCursorName() != null) {
287                // Named cursor: FOR item IN cursorName LOOP
288                String cursorName = loop.getCursorName().toString().toLowerCase();
289                TSelectSqlStatement cursorSelect = cursorBindings.get(cursorName);
290                if (cursorSelect != null) {
291                    loopVarBindings.put(loopVar, cursorSelect);
292                }
293                logger.trace("Named cursor loop: var=" + loopVar + ", cursor=" + cursorName
294                        + ", resolved=" + (cursorSelect != null));
295            }
296        }
297
298        // Build assignment maps
299        // outRow := ROW_TYPE(...) --> variable -> list of constructor expressions (union from multiple branches)
300        Map<String, List<TExpression>> varConstructors = new LinkedHashMap<>();
301        // outRow.field := expr --> variable -> field -> expression
302        Map<String, Map<String, TExpression>> varFieldAssignments = new LinkedHashMap<>();
303
304        for (TAssignStmt assign : assignStmts) {
305            TExpression leftExpr = assign.getLeft();
306            TExpression rightExpr = assign.getExpression();
307            if (leftExpr == null || rightExpr == null) continue;
308
309            String leftText = leftExpr.toString().trim();
310            int dotIdx = leftText.indexOf('.');
311            if (dotIdx > 0) {
312                // outRow.field := expr
313                String varName = leftText.substring(0, dotIdx).toLowerCase();
314                String fieldName = leftText.substring(dotIdx + 1);
315                varFieldAssignments.computeIfAbsent(varName, k -> new LinkedHashMap<>())
316                        .put(fieldName, rightExpr);
317            } else {
318                // outRow := ROW_TYPE(...)
319                String varName = leftText.toLowerCase();
320                varConstructors.computeIfAbsent(varName, k -> new ArrayList<>())
321                        .add(rightExpr);
322            }
323        }
324
325        // Check for cursor FOR loops using named cursors (FOR item IN cursorName LOOP)
326        for (TLoopStmt loop : cursorLoops) {
327            if (loop.getIndexName() != null && loop.getSubquery() == null) {
328                // Might reference a named cursor
329                String loopVar = loop.getIndexName().toString().toLowerCase();
330                // Look for cursor name in the loop definition
331                // The cursor name would be in the loop's cursor expression
332                // This is handled differently - the subquery is already resolved
333            }
334        }
335
336        // Process each PIPE ROW statement
337        for (TPlsqlPipeRowStmt pipeRow : pipeRowStmts) {
338            TExpression expr = pipeRow.getExpression();
339            if (expr == null) continue;
340
341            String exprText = expr.toString().trim();
342
343            if (isObjectConstructor(expr)) {
344                // PIPE ROW(ROW_TYPE(arg1, arg2, ...))
345                bindConstructorArgs(sig, expr, loopVarBindings, cursorBindings);
346            } else {
347                // PIPE ROW(outRow) - variable reference
348                String varName = exprText.toLowerCase();
349
350                // Check for constructor assignments: outRow := ROW_TYPE(...)
351                List<TExpression> constructors = varConstructors.get(varName);
352                if (constructors != null) {
353                    for (TExpression constructor : constructors) {
354                        if (isObjectConstructor(constructor)) {
355                            bindConstructorArgs(sig, constructor, loopVarBindings, cursorBindings);
356                        }
357                    }
358                }
359
360                // Check for field assignments: outRow.field := expr
361                Map<String, TExpression> fieldAssigns = varFieldAssignments.get(varName);
362                if (fieldAssigns != null) {
363                    overlayFieldAssignments(sig, fieldAssigns, loopVarBindings, cursorBindings);
364                }
365            }
366        }
367
368        sig.setResolved(true);
369        sig.setStatus(PipelinedFunctionSignature.ResolutionStatus.OK);
370    }
371
372    private boolean isObjectConstructor(TExpression expr) {
373        if (expr == null) return false;
374        // A constructor call looks like a function call: ROW_TYPE(arg1, arg2, ...)
375        if (expr.getExpressionType() == EExpressionType.function_t && expr.getFunctionCall() != null) {
376            return true;
377        }
378        // Also handle simple_object_expr_t
379        if (expr.getExpressionType() == EExpressionType.simple_object_name_t) {
380            return false;
381        }
382        // Check if it's a function call expression
383        String text = expr.toString().trim();
384        if (text.contains("(") && text.endsWith(")")) {
385            return expr.getFunctionCall() != null;
386        }
387        return false;
388    }
389
390    private void bindConstructorArgs(PipelinedFunctionSignature sig, TExpression constructorExpr,
391                                     Map<String, TSelectSqlStatement> loopVarBindings,
392                                     Map<String, TSelectSqlStatement> cursorBindings) {
393        TFunctionCall funcCall = constructorExpr.getFunctionCall();
394        if (funcCall == null || funcCall.getArgs() == null) return;
395
396        TExpressionList args = funcCall.getArgs();
397        List<String> outputColumns = sig.getOutputColumns();
398
399        for (int i = 0; i < args.size() && i < outputColumns.size(); i++) {
400            TExpression arg = args.getExpression(i);
401            String outputCol = outputColumns.get(i).toLowerCase();
402            resolveArgLineage(sig, outputCol, arg, loopVarBindings, cursorBindings);
403        }
404    }
405
406    private void resolveArgLineage(PipelinedFunctionSignature sig, String outputCol,
407                                   TExpression arg,
408                                   Map<String, TSelectSqlStatement> loopVarBindings,
409                                   Map<String, TSelectSqlStatement> cursorBindings) {
410        if (arg == null) return;
411
412        String argText = arg.toString().trim();
413
414        // Check if it's a wrapper function like util.TO_NUMBER(item.xxx) - extract inner argument
415        TExpression innerArg = unwrapFunctionCalls(arg);
416        if (innerArg != arg) {
417            argText = innerArg.toString().trim();
418        }
419
420        // Check for loop variable reference: item.COLUMN_NAME
421        int dotIdx = argText.indexOf('.');
422        if (dotIdx > 0) {
423            String prefix = argText.substring(0, dotIdx).toLowerCase();
424            String fieldName = argText.substring(dotIdx + 1);
425
426            // Check loop variable bindings
427            TSelectSqlStatement cursorSelect = loopVarBindings.get(prefix);
428            if (cursorSelect == null) {
429                cursorSelect = cursorBindings.get(prefix);
430            }
431            if (cursorSelect != null) {
432                // Resolve the field from the cursor's select list
433                resolveFromCursorSelect(sig, outputCol, fieldName, cursorSelect);
434                return;
435            }
436        }
437
438        // Check for simple column reference (no prefix)
439        if (dotIdx < 0 && !argText.contains("(")) {
440            // Could be a local variable or parameter - mark as unresolved for now
441            sig.addSourceRef(outputCol, new PipelinedSourceRef(
442                    "unknown", argText, PipelinedSourceRef.SourceKind.UNRESOLVED));
443            return;
444        }
445
446        // Check for literal/constant
447        if (argText.equals("null") || argText.startsWith("'") || argText.matches("-?\\d+.*")) {
448            // Constant - empty source, just preserve column structure
449            return;
450        }
451
452        // For qualified names like table.column that aren't loop variables
453        if (dotIdx > 0) {
454            String tablePart = argText.substring(0, dotIdx);
455            String colPart = argText.substring(dotIdx + 1);
456            sig.addSourceRef(outputCol, new PipelinedSourceRef(
457                    tablePart, colPart, PipelinedSourceRef.SourceKind.BASE_TABLE));
458        }
459    }
460
461    private TExpression unwrapFunctionCalls(TExpression expr) {
462        // Unwrap wrapper functions like TO_NUMBER(x), TO_CHAR(x), TRIM(x), util.TO_NUMBER(x)
463        if (expr.getExpressionType() == EExpressionType.function_t && expr.getFunctionCall() != null) {
464            TFunctionCall func = expr.getFunctionCall();
465            if (func.getArgs() != null && func.getArgs().size() == 1) {
466                String funcName = func.getFunctionName().toString().toUpperCase();
467                // Common wrapper functions
468                if (funcName.endsWith("TO_NUMBER") || funcName.endsWith("TO_CHAR")
469                        || funcName.endsWith("TO_DATE") || funcName.equals("TRIM")
470                        || funcName.equals("NVL") || funcName.equals("COALESCE")
471                        || funcName.equals("UPPER") || funcName.equals("LOWER")
472                        || funcName.equals("CAST") || funcName.endsWith(".TO_NUMBER")) {
473                    return unwrapFunctionCalls(func.getArgs().getExpression(0));
474                }
475            }
476        }
477        return expr;
478    }
479
480    private void resolveFromCursorSelect(PipelinedFunctionSignature sig, String outputCol,
481                                         String fieldName, TSelectSqlStatement cursorSelect) {
482        // Find the column in the cursor's SELECT list that matches fieldName
483        if (cursorSelect.getResultColumnList() == null) return;
484
485        String fieldNameLower = fieldName.toLowerCase();
486        // Strip quotes
487        fieldNameLower = SQLUtil.trimColumnStringQuote(fieldNameLower);
488
489        for (int i = 0; i < cursorSelect.getResultColumnList().size(); i++) {
490            TResultColumn rc = cursorSelect.getResultColumnList().getResultColumn(i);
491            String alias = null;
492            if (rc.getAliasClause() != null) {
493                alias = rc.getAliasClause().toString().toLowerCase();
494                alias = SQLUtil.trimColumnStringQuote(alias);
495            }
496            String colName = null;
497            if (rc.getExpr() != null) {
498                colName = rc.getExpr().toString().toLowerCase();
499                // Extract just the column part from table.column
500                int dot = colName.lastIndexOf('.');
501                if (dot >= 0) {
502                    colName = colName.substring(dot + 1);
503                }
504                colName = SQLUtil.trimColumnStringQuote(colName);
505            }
506
507            if (fieldNameLower.equals(alias) || fieldNameLower.equals(colName)) {
508                // Found the matching column - trace its source
509                resolveResultColumnSources(sig, outputCol, rc, cursorSelect);
510                return;
511            }
512        }
513
514        // Didn't find by name, try position if same number of columns
515        sig.addSourceRef(outputCol, new PipelinedSourceRef(
516                "cursor", fieldName, PipelinedSourceRef.SourceKind.UNRESOLVED));
517    }
518
519    private void resolveResultColumnSources(PipelinedFunctionSignature sig, String outputCol,
520                                            TResultColumn rc, TSelectSqlStatement select) {
521        resolveResultColumnSourcesWithDepth(sig, outputCol, rc, select, 0);
522    }
523
524    private static final int MAX_CTE_TRACE_DEPTH = 10;
525
526    private void resolveResultColumnSourcesWithDepth(PipelinedFunctionSignature sig, String outputCol,
527                                            TResultColumn rc, TSelectSqlStatement select, int depth) {
528        if (rc.getExpr() == null) return;
529        if (depth > MAX_CTE_TRACE_DEPTH) return;
530
531        TExpression expr = rc.getExpr();
532        // Unwrap wrapper functions
533        expr = unwrapFunctionCalls(expr);
534        String exprText = expr.toString().trim();
535
536        // Try to resolve to a base table column
537        int dotIdx = exprText.lastIndexOf('.');
538        if (dotIdx > 0) {
539            String tableRef = exprText.substring(0, dotIdx);
540            String colRef = exprText.substring(dotIdx + 1);
541
542            // Check if the table reference points to a CTE - if so, trace through it
543            TTable sourceTable = findTableByRefInSelect(tableRef, select);
544            if (sourceTable != null && sourceTable.isCTEName()) {
545                // Use the actual table name (not the alias) to find the CTE definition
546                String cteName = getFullTableName(sourceTable);
547                if (traceThroughCTE(sig, outputCol, colRef, cteName, select, depth)) {
548                    return;
549                }
550            }
551
552            // Resolve table reference against FROM clause
553            String resolvedTable = resolveTableRef(tableRef, select);
554            if (resolvedTable != null) {
555                sig.addSourceRef(outputCol, new PipelinedSourceRef(
556                        resolvedTable, SQLUtil.trimColumnStringQuote(colRef),
557                        PipelinedSourceRef.SourceKind.BASE_TABLE));
558                return;
559            }
560            // Fallback: use the table ref as-is
561            sig.addSourceRef(outputCol, new PipelinedSourceRef(
562                    tableRef, SQLUtil.trimColumnStringQuote(colRef),
563                    PipelinedSourceRef.SourceKind.BASE_TABLE));
564        } else if (exprText.equals("null") || exprText.startsWith("'") || exprText.matches("-?\\d+.*")) {
565            // Constant - just preserve column structure
566            sig.addSourceRef(outputCol, new PipelinedSourceRef(
567                    "constant", exprText, PipelinedSourceRef.SourceKind.CONST));
568        } else if (!exprText.contains("(") && !exprText.contains(" ")) {
569            // Simple column name without table prefix
570            // Try to resolve by checking sourceTable from the parser's column resolution
571            if (rc.getExpr().getObjectOperand() != null
572                    && rc.getExpr().getObjectOperand().getSourceTable() != null) {
573                TTable sourceTable = rc.getExpr().getObjectOperand().getSourceTable();
574                // If the source table is a CTE, trace through it to find actual base tables
575                if (sourceTable.isCTEName()) {
576                    String cteName = getFullTableName(sourceTable);
577                    if (traceThroughCTE(sig, outputCol, exprText, cteName, select, depth)) {
578                        return;
579                    }
580                }
581                String tableName = getFullTableName(sourceTable);
582                sig.addSourceRef(outputCol, new PipelinedSourceRef(
583                        tableName, SQLUtil.trimColumnStringQuote(exprText),
584                        PipelinedSourceRef.SourceKind.BASE_TABLE));
585            } else {
586                // Try to find which table it belongs to from the FROM clause
587                String resolvedTable = resolveColumnTable(exprText, select);
588                if (resolvedTable != null) {
589                    sig.addSourceRef(outputCol, new PipelinedSourceRef(
590                            resolvedTable, SQLUtil.trimColumnStringQuote(exprText),
591                            PipelinedSourceRef.SourceKind.BASE_TABLE));
592                } else {
593                    // Fallback: use the SELECT itself as the source - the analyzer will trace further
594                    // Use the cursor/select name as a proxy source
595                    String selectName = "cursor_result";
596                    if (select.tables != null && select.tables.size() > 0) {
597                        selectName = getFullTableName(select.tables.getTable(0));
598                    }
599                    sig.addSourceRef(outputCol, new PipelinedSourceRef(
600                            selectName, SQLUtil.trimColumnStringQuote(exprText),
601                            PipelinedSourceRef.SourceKind.CTE));
602                }
603            }
604        } else {
605            // Complex expression - try extracting column refs
606            extractColumnRefsFromExpr(sig, outputCol, expr, select);
607        }
608    }
609
610    /**
611     * Traces a column reference through a CTE to find actual base tables.
612     * Returns true if at least one base table source was found.
613     */
614    private boolean traceThroughCTE(PipelinedFunctionSignature sig, String outputCol,
615                                    String colName, String cteRef, TSelectSqlStatement select, int depth) {
616        if (depth > MAX_CTE_TRACE_DEPTH) return false;
617
618        // Find the CTE definition matching cteRef
619        TCTE cte = findCTE(cteRef, select);
620        if (cte == null || cte.getSubquery() == null) return false;
621
622        TSelectSqlStatement cteSelect = cte.getSubquery();
623        // Handle the outermost SELECT of the CTE - for UNION, use leftStmt chain
624        if (cteSelect.getSetOperatorType() != ESetOperatorType.none) {
625            // For UNION queries, trace through the first branch
626            Deque<TSelectSqlStatement> unionStack = new ArrayDeque<>();
627            unionStack.push(cteSelect);
628            while (!unionStack.isEmpty()) {
629                TSelectSqlStatement current = unionStack.pop();
630                if (current.getSetOperatorType() != ESetOperatorType.none) {
631                    if (current.getRightStmt() != null) unionStack.push(current.getRightStmt());
632                    if (current.getLeftStmt() != null) unionStack.push(current.getLeftStmt());
633                } else {
634                    // Process this leaf SELECT
635                    traceCTESelectColumn(sig, outputCol, colName, current, select, depth);
636                    return true; // Just use the first branch for lineage
637                }
638            }
639            return false;
640        }
641
642        return traceCTESelectColumn(sig, outputCol, colName, cteSelect, select, depth);
643    }
644
645    /**
646     * Traces a column through a CTE's SELECT statement to find its source.
647     */
648    private boolean traceCTESelectColumn(PipelinedFunctionSignature sig, String outputCol,
649                                          String colName, TSelectSqlStatement cteSelect,
650                                          TSelectSqlStatement outerSelect, int depth) {
651        if (cteSelect.getResultColumnList() == null) return false;
652
653        String colNameLower = SQLUtil.trimColumnStringQuote(colName.toLowerCase());
654
655        // Find the matching result column in the CTE's SELECT list
656        for (int i = 0; i < cteSelect.getResultColumnList().size(); i++) {
657            TResultColumn rc = cteSelect.getResultColumnList().getResultColumn(i);
658            String alias = null;
659            if (rc.getAliasClause() != null) {
660                alias = rc.getAliasClause().toString().toLowerCase();
661                alias = SQLUtil.trimColumnStringQuote(alias);
662            }
663            String rcColName = null;
664            if (rc.getExpr() != null) {
665                rcColName = rc.getExpr().toString().toLowerCase();
666                int dot = rcColName.lastIndexOf('.');
667                if (dot >= 0) {
668                    rcColName = rcColName.substring(dot + 1);
669                }
670                rcColName = SQLUtil.trimColumnStringQuote(rcColName);
671            }
672
673            if (colNameLower.equals(alias) || colNameLower.equals(rcColName)) {
674                // Found the column - recursively resolve its source
675                resolveResultColumnSourcesWithDepth(sig, outputCol, rc, cteSelect, depth + 1);
676                return true;
677            }
678        }
679        return false;
680    }
681
682    /**
683     * Finds a CTE by name in the given SELECT or its parent statements.
684     */
685    private TCTE findCTE(String cteRef, TSelectSqlStatement select) {
686        String cteRefLower = SQLUtil.trimColumnStringQuote(cteRef.toLowerCase());
687        // Also try bare name
688        String cteRefBare = getBareName(cteRefLower);
689
690        TCustomSqlStatement current = select;
691        while (current != null) {
692            if (current instanceof TSelectSqlStatement) {
693                TSelectSqlStatement sel = (TSelectSqlStatement) current;
694                if (sel.getCteList() != null) {
695                    for (int i = 0; i < sel.getCteList().size(); i++) {
696                        TCTE cte = sel.getCteList().getCTE(i);
697                        if (cte.getTableName() != null) {
698                            String cteName = cte.getTableName().toString().toLowerCase();
699                            if (cteRefLower.equals(cteName) || cteRefBare.equals(cteName)) {
700                                return cte;
701                            }
702                        }
703                    }
704                }
705            }
706            current = current.getParentStmt();
707        }
708        return null;
709    }
710
711    /**
712     * Finds a TTable in the SELECT's FROM clause by reference (name or alias).
713     */
714    private TTable findTableByRefInSelect(String tableRef, TSelectSqlStatement select) {
715        if (select == null || select.tables == null) return null;
716        String refLower = SQLUtil.trimColumnStringQuote(tableRef.toLowerCase());
717
718        for (int i = 0; i < select.tables.size(); i++) {
719            TTable t = select.tables.getTable(i);
720            if (t.getAliasClause() != null) {
721                String alias = SQLUtil.trimColumnStringQuote(t.getAliasClause().toString().toLowerCase());
722                if (alias.equals(refLower)) return t;
723            }
724            String tName = t.getFullName() != null ? t.getFullName().toLowerCase() : "";
725            String bareTableName = getBareName(tName);
726            if (refLower.equals(bareTableName) || refLower.equals(tName)) return t;
727        }
728        return null;
729    }
730
731    private void extractColumnRefsFromExpr(PipelinedFunctionSignature sig, String outputCol,
732                                           TExpression expr, TSelectSqlStatement select) {
733        // Use iterative traversal to find object names in the expression
734        Deque<TExpression> exprStack = new ArrayDeque<>();
735        exprStack.push(expr);
736        boolean found = false;
737
738        while (!exprStack.isEmpty()) {
739            TExpression current = exprStack.pop();
740            if (current == null) continue;
741
742            if (current.getExpressionType() == EExpressionType.simple_object_name_t
743                    && current.getObjectOperand() != null) {
744                String objName = current.getObjectOperand().toString();
745                int dot = objName.lastIndexOf('.');
746                if (dot > 0) {
747                    String tableRef = objName.substring(0, dot);
748                    String colRef = objName.substring(dot + 1);
749                    String resolvedTable = resolveTableRef(tableRef, select);
750                    sig.addSourceRef(outputCol, new PipelinedSourceRef(
751                            resolvedTable != null ? resolvedTable : tableRef,
752                            SQLUtil.trimColumnStringQuote(colRef),
753                            PipelinedSourceRef.SourceKind.BASE_TABLE));
754                    found = true;
755                }
756            }
757
758            // Push children
759            if (current.getLeftOperand() != null) exprStack.push(current.getLeftOperand());
760            if (current.getRightOperand() != null) exprStack.push(current.getRightOperand());
761            if (current.getFunctionCall() != null && current.getFunctionCall().getArgs() != null) {
762                for (int i = 0; i < current.getFunctionCall().getArgs().size(); i++) {
763                    exprStack.push(current.getFunctionCall().getArgs().getExpression(i));
764                }
765            }
766        }
767
768        if (!found) {
769            sig.addSourceRef(outputCol, new PipelinedSourceRef(
770                    "expression", expr.toString(), PipelinedSourceRef.SourceKind.UNRESOLVED));
771        }
772    }
773
774    private String resolveTableRef(String tableRef, TSelectSqlStatement select) {
775        if (select == null || select.tables == null) return null;
776        String tableRefLower = tableRef.toLowerCase();
777        String tableRefNorm = SQLUtil.trimColumnStringQuote(tableRefLower);
778
779        for (int i = 0; i < select.tables.size(); i++) {
780            TTable t = select.tables.getTable(i);
781            // Check alias
782            if (t.getAliasClause() != null) {
783                String alias = t.getAliasClause().toString().toLowerCase();
784                alias = SQLUtil.trimColumnStringQuote(alias);
785                if (alias.equals(tableRefNorm)) {
786                    return getFullTableName(t);
787                }
788            }
789            // Check table name
790            String tName = t.getFullName() != null ? t.getFullName().toLowerCase() : "";
791            String bareTableName = getBareName(tName);
792            if (tableRefNorm.equals(bareTableName) || tableRefNorm.equals(tName)) {
793                return getFullTableName(t);
794            }
795        }
796
797        // Check CTEs
798        if (select.getCteList() != null) {
799            for (int i = 0; i < select.getCteList().size(); i++) {
800                TCTE cte = select.getCteList().getCTE(i);
801                if (cte.getTableName() != null) {
802                    String cteName = cte.getTableName().toString().toLowerCase();
803                    if (tableRefNorm.equals(cteName)) {
804                        return cteName;
805                    }
806                }
807            }
808        }
809
810        // If this is a subquery's select, walk up to find CTEs
811        TCustomSqlStatement parent = select.getParentStmt();
812        if (parent instanceof TSelectSqlStatement) {
813            return resolveTableRef(tableRef, (TSelectSqlStatement) parent);
814        }
815
816        return null;
817    }
818
819    private String resolveColumnTable(String colName, TSelectSqlStatement select) {
820        if (select == null || select.tables == null) return null;
821        // Simplified: just return null to indicate we can't determine the table
822        return null;
823    }
824
825    private String getFullTableName(TTable table) {
826        if (table.getFullName() != null) {
827            return table.getFullName();
828        }
829        return table.getName();
830    }
831
832    private void overlayFieldAssignments(PipelinedFunctionSignature sig,
833                                         Map<String, TExpression> fieldAssigns,
834                                         Map<String, TSelectSqlStatement> loopVarBindings,
835                                         Map<String, TSelectSqlStatement> cursorBindings) {
836        for (Map.Entry<String, TExpression> entry : fieldAssigns.entrySet()) {
837            String fieldName = entry.getKey();
838            TExpression expr = entry.getValue();
839
840            // Find matching output column
841            String outputCol = null;
842            for (String col : sig.getOutputColumns()) {
843                if (SQLUtil.sameName(option.getVendor(), ESQLDataObjectType.dotColumn, col, fieldName)) {
844                    outputCol = col.toLowerCase();
845                    break;
846                }
847            }
848            if (outputCol == null) continue;
849
850            resolveArgLineage(sig, outputCol, expr, loopVarBindings, cursorBindings);
851        }
852    }
853
854    // ---- Phase 4: Call-site stitching ----
855
856    /**
857     * Handle TABLE(func(...)) which is parsed as tableExpr with funcCall=null.
858     * The table name is the function name. Always defers to finalize because
859     * table columns are not yet populated during from-clause processing.
860     */
861    public void tryStitchTableExpr(TTable table) {
862        if (!option.isEnablePipelinedStitching()) return;
863        if (table.getName() == null) return;
864
865        String tableName = table.getName();
866        List<String> candidates = new ArrayList<>();
867        candidates.add(normalizeKey(tableName));
868        String bareName = getBareName(tableName);
869        if (!bareName.equals(normalizeKey(tableName))) {
870            candidates.add(bareName);
871        }
872
873        // Always defer to finalize - columns aren't populated yet during FROM processing
874        modelManager.addPendingPipelinedCallSite(
875                new PendingPipelinedCallSite(candidates, -1, table, null));
876    }
877
878    /**
879     * Called when TABLE(func(...)) is encountered in a FROM clause.
880     * Generates candidate keys and tries to stitch immediately.
881     * If the signature isn't available yet, adds to pending list.
882     */
883    public boolean tryStitchCallSite(TTable table, TFunctionCall funcCall) {
884        if (!option.isEnablePipelinedStitching()) return false;
885
886        List<String> candidates = generateFunctionKeyCandidates(funcCall);
887        int argCount = funcCall.getArgs() != null ? funcCall.getArgs().size() : 0;
888
889        PipelinedFunctionSignature sig = modelManager.findPipelinedSignature(candidates, argCount);
890        if (sig == null) {
891            sig = modelManager.findPipelinedSignatureAny(candidates);
892        }
893
894        if (sig != null && sig.isResolved()
895                && sig.getStatus() == PipelinedFunctionSignature.ResolutionStatus.OK) {
896            logger.trace("Pipelined stitch: found signature " + sig.getFunctionKey()
897                    + " for call " + funcCall.getFunctionName() + ", cols=" + sig.getOutputColumns().size()
898                    + ", lineage keys=" + sig.getLineageByOutputColumn().keySet());
899            stitchOneCallSite(table, sig);
900            return true;
901        }
902
903        // Add to pending for finalize
904        logger.trace("Pipelined stitch: no signature found for candidates=" + candidates
905                + ", adding to pending");
906        modelManager.addPendingPipelinedCallSite(
907                new PendingPipelinedCallSite(candidates, argCount, table, funcCall));
908        return false;
909    }
910
911    /**
912     * Stitch a single pending call site. Called from DataFlowAnalyzer with
913     * the proper statement stack context.
914     */
915    public void stitchOnePending(PendingPipelinedCallSite cs) {
916        if (!option.isEnablePipelinedStitching()) return;
917
918        PipelinedFunctionSignature sig = modelManager.findPipelinedSignature(
919                cs.getFunctionKeyCandidates(), cs.getArgCount());
920        if (sig == null) {
921            sig = modelManager.findPipelinedSignatureAny(cs.getFunctionKeyCandidates());
922        }
923        if (sig != null && sig.isResolved()
924                && sig.getStatus() == PipelinedFunctionSignature.ResolutionStatus.OK) {
925            stitchOneCallSite(cs.getTable(), sig);
926        }
927    }
928
929    /**
930     * Finalize hook: process all pending call sites. Called before XML emit.
931     */
932    public void stitchPendingCallSites() {
933        if (!option.isEnablePipelinedStitching()) return;
934
935        for (PendingPipelinedCallSite cs : modelManager.getPendingPipelinedCallSites()) {
936            stitchOnePending(cs);
937        }
938    }
939
940    private void stitchOneCallSite(TTable table, PipelinedFunctionSignature sig) {
941        Set<String> resolving = modelManager.getResolvingPipelinedFunctions();
942        if (resolving.contains(sig.getFunctionKey())
943                || resolving.size() >= option.getMaxPipelinedExpansionDepth()) {
944            return;
945        }
946
947        resolving.add(sig.getFunctionKey());
948        try {
949            // Get the table model bound to this function call table
950            Object tableModel = modelManager.getModel(table);
951
952            if (tableModel instanceof Table) {
953                Table t = (Table) tableModel;
954                if (t.getColumns() != null && !t.getColumns().isEmpty()) {
955                    stitchToTable(t, sig);
956                    return;
957                }
958            }
959
960            // For tableExpr tables (TABLE(func())), the table model is created
961            // differently. Try createTableFromCreateDDL which returns existing if bound.
962            Table functionTable = modelFactory.createTableFromCreateDDL(table, true);
963            if (functionTable != null && functionTable.getColumns() != null && !functionTable.getColumns().isEmpty()) {
964                stitchToTable(functionTable, sig);
965                return;
966            }
967
968            // Last resort: create output columns from the signature and stitch
969            if (functionTable != null) {
970                for (String outputCol : sig.getOutputColumns()) {
971                    TObjectName colName = new TObjectName();
972                    colName.setString(outputCol);
973                    TableColumn column = modelFactory.createTableColumn(functionTable, colName, true);
974
975                    List<PipelinedSourceRef> refs = sig.getLineageByOutputColumn().get(outputCol.toLowerCase());
976                    if (refs == null || refs.isEmpty()) continue;
977
978                    Set<String> addedSources = new HashSet<>();
979                    int added = 0;
980                    for (PipelinedSourceRef ref : refs) {
981                        if (added >= option.getMaxStitchedSourcesPerColumn()) break;
982                        if (ref.getSourceKind() == PipelinedSourceRef.SourceKind.UNRESOLVED
983                                || ref.getSourceKind() == PipelinedSourceRef.SourceKind.CONST) {
984                            continue;
985                        }
986                        String sourceKey = ref.getParentName() + "." + ref.getColumnName();
987                        if (!addedSources.add(sourceKey)) continue; // dedup
988
989                        Table sourceTable = modelFactory.createTableByName(ref.getParentName(), false);
990                        TObjectName sourceColName = new TObjectName();
991                        sourceColName.setString(ref.getColumnName());
992                        TableColumn sourceCol = modelFactory.createTableColumn(sourceTable, sourceColName, true);
993
994                        DataFlowRelationship relation = modelFactory.createDataFlowRelation();
995                        relation.setEffectType(EffectType.select);
996                        relation.addSource(new TableColumnRelationshipElement(sourceCol));
997                        relation.setTarget(new TableColumnRelationshipElement(column));
998                        added++;
999                    }
1000                }
1001            }
1002        } catch (Exception e) {
1003            logger.error("Error stitching pipelined call site for " + sig.getFunctionKey(), e);
1004        } finally {
1005            resolving.remove(sig.getFunctionKey());
1006        }
1007    }
1008
1009    private void stitchToTable(Table functionTable, PipelinedFunctionSignature sig) {
1010        if (functionTable == null || functionTable.getColumns() == null) return;
1011
1012        for (TableColumn callerCol : functionTable.getColumns()) {
1013            String callerColName = callerCol.getName();
1014            if (callerColName == null) continue;
1015
1016            String mappedKey = mapByNameThenPosition(callerColName, callerCol, functionTable, sig);
1017            if (mappedKey == null) continue;
1018
1019            List<PipelinedSourceRef> refs = sig.getLineageByOutputColumn().get(mappedKey);
1020            if (refs == null || refs.isEmpty()) continue;
1021
1022            Set<String> addedSources = new HashSet<>();
1023            int added = 0;
1024            for (PipelinedSourceRef ref : refs) {
1025                if (added >= option.getMaxStitchedSourcesPerColumn()) break;
1026                if (ref.getSourceKind() == PipelinedSourceRef.SourceKind.UNRESOLVED
1027                        || ref.getSourceKind() == PipelinedSourceRef.SourceKind.CONST) {
1028                    continue;
1029                }
1030
1031                // Dedup: skip if already added
1032                String sourceKey = ref.getParentName() + "." + ref.getColumnName();
1033                if (!addedSources.add(sourceKey)) continue;
1034
1035                // Create the stitched relationship
1036                Table sourceTable = modelFactory.createTableByName(ref.getParentName(), false);
1037                TObjectName sourceColName = new TObjectName();
1038                sourceColName.setString(ref.getColumnName());
1039                TableColumn sourceCol = modelFactory.createTableColumn(sourceTable, sourceColName, true);
1040
1041                DataFlowRelationship relation = modelFactory.createDataFlowRelation();
1042                relation.setEffectType(EffectType.select);
1043                relation.addSource(new TableColumnRelationshipElement(sourceCol));
1044                relation.setTarget(new TableColumnRelationshipElement(callerCol));
1045
1046                added++;
1047            }
1048        }
1049    }
1050
1051    private String mapByNameThenPosition(String callerColName, TableColumn callerCol,
1052                                         Table functionTable, PipelinedFunctionSignature sig) {
1053        List<String> outputColumns = sig.getOutputColumns();
1054        if (outputColumns == null || outputColumns.isEmpty()) return null;
1055
1056        // 1. Exact name match
1057        for (String out : outputColumns) {
1058            if (out.equals(callerColName)) return out.toLowerCase();
1059        }
1060
1061        // 2. Normalized name match (case-insensitive, strip quotes)
1062        String normalizedCaller = SQLUtil.trimColumnStringQuote(callerColName.toLowerCase());
1063        for (String out : outputColumns) {
1064            String normalizedOut = SQLUtil.trimColumnStringQuote(out.toLowerCase());
1065            if (normalizedOut.equals(normalizedCaller)) return normalizedOut;
1066        }
1067
1068        // 3. Position fallback (strict prerequisites)
1069        if (functionTable.getColumns() != null
1070                && functionTable.getColumns().size() == outputColumns.size()) {
1071            int idx = functionTable.getColumns().indexOf(callerCol);
1072            if (idx >= 0 && idx < outputColumns.size()) {
1073                return outputColumns.get(idx).toLowerCase();
1074            }
1075        }
1076
1077        return null;
1078    }
1079
1080    // ---- Utility methods ----
1081
1082    private List<String> generateFunctionKeyCandidates(TFunctionCall funcCall) {
1083        List<String> candidates = new ArrayList<>();
1084        String funcName = funcCall.getFunctionName().toString();
1085
1086        // Try various levels of qualification
1087        // Full name as-is
1088        candidates.add(normalizeKey(funcName));
1089
1090        // With package prefix from current context
1091        if (ModelBindingManager.getGlobalOraclePackage() != null) {
1092            String pkgName = ModelBindingManager.getGlobalOraclePackage().getName();
1093            if (pkgName != null && !pkgName.isEmpty()) {
1094                candidates.add(normalizeKey(pkgName + "." + funcName));
1095            }
1096        }
1097
1098        // Bare name (strip schema/package prefix)
1099        String bareName = getBareName(funcName);
1100        if (!bareName.equals(funcName.toLowerCase())) {
1101            candidates.add(normalizeKey(bareName));
1102        }
1103
1104        return candidates;
1105    }
1106
1107    private String normalizeTypeName(String name) {
1108        if (name == null) return "";
1109        return DlineageUtil.getIdentifierNormalTableName(name.trim());
1110    }
1111
1112    private String normalizeKey(String name) {
1113        if (name == null) return "";
1114        return DlineageUtil.getIdentifierNormalTableName(name);
1115    }
1116
1117    private String getBareName(String name) {
1118        if (name == null) return "";
1119        String normalized = DlineageUtil.getIdentifierNormalTableName(name);
1120        int lastDot = normalized.lastIndexOf('.');
1121        if (lastDot >= 0) {
1122            return normalized.substring(lastDot + 1);
1123        }
1124        return normalized;
1125    }
1126}