Source code

001package gudusoft.gsqlparser.stmt;
002
003
004import gudusoft.gsqlparser.*;
005import gudusoft.gsqlparser.compiler.TFrame;
006import gudusoft.gsqlparser.compiler.TSymbolTableManager;
007import gudusoft.gsqlparser.compiler.TVariable;
008import gudusoft.gsqlparser.nodes.*;
009import gudusoft.gsqlparser.nodes.mssql.TProcedureOption;
010
011/**
012 * Create procedure statement.
013 * 存储过程如果是 $$ body $$ 形式的，有两种解析方法，
014 * 方法1，把 $$ body $$ 中的 body 在 lexical 阶段即分解为独立的 token， 整个 create procedure statement 在 parser 中一次性完成解析
015 * 采用这个方法的数据库： greenplum, postgres, redshift, snowflake
016 *
017 * GSP Java version 2.8.9.3(2024-5-27) [Snowflake] support string literal delimiter $ or ' around procedure definition.
018 * '' 中的存储过程代码被  {@link TGSqlParser.expandDollarString()} 方法处理，处理后的代码被解析。
019 *
020 * 2024/6/22, snowflake create procedure 的 body 如果是采用 $$ body $$， 在 lexer 中分解为独立的 token
021 *
022 * 方法2，把 $$ body $$ 作为一个整体字符串看待，需要进行二次解析， 都是调用 postgres parser 进行的分析。采用这种方法是，body中用到的 select
023 * 等语句用的是 postgres的语法分析，而不是原数据库 select 语句的语法分析，这可能导致不正确的解析结果。
024 * 这种方法为历史遗留问题，需要都改为方法1。目前采用这种方法的数据库有：
025 * netezza,
026 * vertica
027 *
028 * Supported databases
029 * <ul>
030 * <li>Postgres</li>
031 *<li>redshift</li>
032 * </ul>
033 */
034public class TCreateProcedureStmt extends TRoutine{
035
036    private TObjectName functionName = null;
037    private TConstant objfile;
038    private TConstant linkSymbol;
039    private TConstant procedureLanguage;
040    private TTableElementList returnTableDefinitions = null;
041
042    /**
043     * Returns the column definitions from the RETURNS TABLE(...) clause.
044     * For example, for {@code RETURNS TABLE(id INT, name VARCHAR)}, this returns
045     * a list containing column definitions for "id INT" and "name VARCHAR".
046     * Returns null if the procedure does not use RETURNS TABLE or has an empty TABLE().
047     * <p>Currently supported for Snowflake.</p>
048     * @return the column definitions list, or null
049     */
050    public TTableElementList getReturnTableDefinitions() {
051        return returnTableDefinitions;
052    }
053
054    public TConstant getProcedureLanguage() {
055        return procedureLanguage;
056    }
057
058    public TConstant getLinkSymbol() {
059        return linkSymbol;
060    }
061    public TConstant getObjfile() {
062        return objfile;
063    }
064
065    private TPTNodeList <TProcedureOption> procedureOptions;
066    private boolean isForReplication;
067
068    public void setProcedureOptions(TPTNodeList<TProcedureOption> procedureOptions) {
069        this.procedureOptions = procedureOptions;
070    }
071
072    public TPTNodeList<TProcedureOption> getProcedureOptions() {
073
074        return procedureOptions;
075    }
076
077    public void setForReplication(boolean isForReplication) {
078        this.isForReplication = isForReplication;
079    }
080
081    public boolean isForReplication() {
082        return isForReplication;
083    }
084
085    public TCreateProcedureStmt (EDbVendor dbvendor){
086        super(dbvendor);
087        sqlstatementtype = ESqlStatementType.sstcreateprocedure ;
088    }
089
090    @Override
091    public TObjectName getStoredProcedureName(){
092        return procedureName;
093    }
094
095    /**
096     * The name that you give to the procedure that you are declaring or defining.
097     * @return
098     */
099    public TObjectName getProcedureName() {
100        return procedureName;
101    }
102
103    private TObjectName procedureName = null;
104
105
106
107    public int doParseStatement(TCustomSqlStatement psql) {
108        if (rootNode == null) return -1;
109        TCreateProcedureSqlNode createProcedureNode = (TCreateProcedureSqlNode)rootNode;
110
111        super.doParseStatement(psql);
112        TFrame currentFrame = new TFrame(this.stmtScope);
113        currentFrame.pushMeToStack(getFrameStack());
114
115        procedureName = createProcedureNode.getProcedureName();
116
117        if (getSqlEnv() != null)  {
118            getSqlEnv().addProcedure(procedureName.toString(),true);
119
120            // move to TDatabaseObjectResolver
121
122//            if (getSqlEnv().getDefaultCatalogName() != null){
123//                if (procedureName.getDatabaseToken() == null){
124//                    procedureName.setDatabaseToken(new TSourceToken(getSqlEnv().getDefaultCatalogName()));
125//                }
126//            }
127        }
128
129        setRoutineName(procedureName);
130
131        procedureLanguage = createProcedureNode.getProcedureLanguage();
132        if (procedureLanguage != null){
133            if (getRoutineLanguage() == null){
134                // not already set during TGsqlParser.dopostgresqlgetrawsqlstatements()
135                // then we set it here
136                setRoutineLanguage(procedureLanguage.toString());
137            }
138            setRoutineLanguageInConstant(procedureLanguage);
139        }else {
140
141            if (dbvendor == EDbVendor.dbvvertica){
142                // vertica, set default to plvsql
143                TConstant c = new TConstant();
144                c.init(ELiteralType.etString, new TSourceToken("plvsql"));
145                procedureLanguage = c;
146                setRoutineLanguageInConstant(procedureLanguage);
147                setRoutineLanguage("plvsql");
148            }
149
150        }
151        this.setLabelName(createProcedureNode.getLabelName());
152        objfile = createProcedureNode.getObjfile();
153        linkSymbol = createProcedureNode.getLinkSymbol();
154        this.setReturnDataType(createProcedureNode.getReturnDataType());
155        // Extract RETURNS TABLE column definitions (Snowflake)
156        if (createProcedureNode.getReturnTable() != null) {
157            TDummy dmy = createProcedureNode.getReturnTable();
158            if (dmy.list1 instanceof TTableElementList) {
159                this.returnTableDefinitions = (TTableElementList) dmy.list1;
160                this.returnTableDefinitions.doParse(this, ESqlClause.unknown);
161            }
162        }
163        if (createProcedureNode.getFunctionBody() != null){
164            setRoutineBodyInConstant(createProcedureNode.getFunctionBody());
165            setRoutineBody(createProcedureNode.getFunctionBody().toString());
166        }
167
168        this.setParameterDeclarations( createProcedureNode.getParameters());
169        // sql server
170        procedureOptions = createProcedureNode.getProcedureOptions();
171        isForReplication  = createProcedureNode.isForReplication();
172        this.setReturnDataType(createProcedureNode.getReturnDataType());
173        // end sql server
174
175        // push parameterDeclarations into symbolTable
176        if (this.getParameterDeclarations() != null){
177            for(int i=0;i< this.getParameterDeclarations().size();i++){
178                this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjParameter,this, this.getParameterDeclarations().getParameterDeclarationItem(i)));
179                TParameterDeclaration parameterDeclaration = this.getParameterDeclarations().getParameterDeclarationItem(i);
180                if (parameterDeclaration.getParameterName() != null){
181                    TVariable variable = new TVariable(parameterDeclaration.getParameterName(),parameterDeclaration,procedureName);
182
183                    TVariable variableOfThisParam =  TSymbolTableManager.searchSymbolVariable(this.getFrameStack(),procedureName+".$"+i);
184                    if (variableOfThisParam != null){
185                        variable.setVariableStr(variableOfThisParam.getVariableStr());
186                    }
187
188                   // variable.variableStr = TBaseType.getStringInsideLiteral(getSqlEnv().getProcedureParameterValue(procedureName.toString(), i));
189                    this.stmtScope.addSymbol(variable);
190                }
191            }
192        }
193
194
195        switch (dbvendor){
196            case dbvdb2:
197                TCompoundSqlNode compoundSqlNode = createProcedureNode.getCompoundSqls();
198                if (compoundSqlNode != null){
199                    if (compoundSqlNode.getDeclareStmts() != null){
200                        compoundSqlNode.getDeclareStmts().doParse(this,ESqlClause.unknown);
201
202                        // push variable declare into symbolTable, and add to declareStatements
203                        for(int i=0;i<compoundSqlNode.getDeclareStmts().size();i++){
204                            this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,compoundSqlNode.getDeclareStmts().getStatementSqlNode(i).getStmt() ));
205                            this.getDeclareStatements().add(compoundSqlNode.getDeclareStmts().getStatementSqlNode(i).getStmt());
206                        }
207                    }
208
209                    if (compoundSqlNode.getStmts() != null){
210                        compoundSqlNode.getStmts().doParse(this,ESqlClause.unknown);
211
212                        for(int i= 0; i<compoundSqlNode.getStmts().size();i++){
213                            this.getBodyStatements().add(compoundSqlNode.getStmts().getStatementSqlNode(i).getStmt());
214                        }
215                    }
216
217                    if (compoundSqlNode.getDeclareStmts() != null){
218                        // pop variable declare from symbolTable
219                        for(int i=0;i<compoundSqlNode.getDeclareStmts().size();i++){
220                            this.getTopStatement().getSymbolTable().pop();
221                        }
222                    }
223                }
224                break;
225            case dbvsnowflake:
226                if (createProcedureNode.getBlcok() != null){
227                    createProcedureNode.getBlcok().doParse(this,ESqlClause.unknown);
228                    this.blockBody = createProcedureNode.getBlcok();
229                }else{
230                    // javascript code body
231                }
232
233                break;
234            case dbvnetezza:
235                if (createProcedureNode.getStmts() != null){
236                    createProcedureNode.getStmts().doParse(this, ESqlClause.unknown);
237
238                    TCommonBlock commonBlock = (TCommonBlock)createProcedureNode.getStmts().getStatementSqlNode(0).getStmt();
239                    this.setLabelName(commonBlock.getLabelName());
240                    this.blockBody = commonBlock.getBlockBody();
241                    this.blockBody.setParent(this);
242
243//                    for(int i=0;i<createProcedureNode.getStmts().size();i++){
244//                        this.getBodyStatements().add(createProcedureNode.getStmts().getStatementSqlNode(i).getStmt());
245//                    }
246                }else if ((createProcedureNode.getFunctionBody() != null)&&(getProcedureLanguage() != null)&&(getProcedureLanguage().toString() !=null)){
247                    // use constant in procedure body
248                    processProcedureDefinition(EDbVendor.dbvnetezza,psql,createProcedureNode);
249                }
250                break;
251            default:
252                if (createProcedureNode.getDeclareStmts() != null){
253                    createProcedureNode.getDeclareStmts().doParse(this, ESqlClause.unknown);
254
255                    // push variable declare into symbolTable    , and add to declareStatements
256                    for(int i=0;i<createProcedureNode.getDeclareStmts().size();i++){
257                        this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,createProcedureNode.getDeclareStmts().getStatementSqlNode(i).getStmt() ));
258                        this.getDeclareStatements().add(createProcedureNode.getDeclareStmts().getStatementSqlNode(i).getStmt());
259                    }
260
261                }
262
263                if (createProcedureNode.getStmt() != null){
264                    createProcedureNode.getStmt().doParse(this, ESqlClause.unknown);
265                    this.getBodyStatements().add(createProcedureNode.getStmt().getStmt());
266                    if (getRoutineBody() == null){
267                        setRoutineBody(createProcedureNode.getStmt().toString());
268                    }
269                }
270                else if (createProcedureNode.getBlcok() != null){
271                    createProcedureNode.getBlcok().doParse(this,ESqlClause.unknown);
272                    this.blockBody = createProcedureNode.getBlcok();
273                    if (getRoutineBody() == null){
274                        setRoutineBody(createProcedureNode.getBlcok().toString());
275                    }
276                }else  if (createProcedureNode.getStmts() != null){ //teradata,sql server
277                    createProcedureNode.getStmts().doParse(this, ESqlClause.unknown);
278
279                    // Teradata: validate semicolons between statements in procedure body
280                    if (this.dbvendor == EDbVendor.dbvteradata) {
281                        validateSemicolonsBetweenStatements(this, createProcedureNode.getStmts());
282                    }
283
284                    for(int i=0;i<createProcedureNode.getStmts().size();i++){
285                        this.getBodyStatements().add(createProcedureNode.getStmts().getStatementSqlNode(i).getStmt());
286                    }
287                    if (getRoutineBody() == null){
288                        String stmtsStr = createProcedureNode.getStmts().toString();
289                        if (stmtsStr == null && this.getBodyStatements().size() > 0){
290                            // Fallback: construct routineBody from body statements text
291                            StringBuilder sb = new StringBuilder();
292                            for(int i=0;i<this.getBodyStatements().size();i++){
293                                if (i > 0) sb.append("\n");
294                                sb.append(this.getBodyStatements().get(i).toString());
295                            }
296                            stmtsStr = sb.toString();
297                        }
298                        if (stmtsStr != null){
299                            setRoutineBody(stmtsStr);
300                        }
301                    }
302                }
303
304                if (createProcedureNode.getExceptionClause() != null){
305                    createProcedureNode.getExceptionClause().doParse(this,ESqlClause.unknown);
306                    this.setExceptionClause(createProcedureNode.getExceptionClause());
307                }
308
309                if (createProcedureNode.getDeclareStmts() != null){
310
311                    // pop variable declare from symbolTable
312                    for(int i=0;i<createProcedureNode.getDeclareStmts().size();i++){
313                        this.getTopStatement().getSymbolTable().pop();
314                    }
315
316                }
317
318                break;
319
320        }
321
322        // pop parameterDeclarations from symbolTable
323        if (this.getParameterDeclarations() != null){
324            for(int i=0;i< this.getParameterDeclarations().size();i++){
325                this.getTopStatement().getSymbolTable().pop();
326            }
327        }
328
329        switch (this.dbvendor){
330            case dbvpostgresql:
331            case dbvredshift:
332                if (createProcedureNode.getFunctionBody() != null){
333                    // function body only inside '' will be processed here
334                    processProcedureDefinition(EDbVendor.dbvpostgresql,psql,createProcedureNode);
335                }else{
336                    // procedure body only inside $$ already processed in the above default section
337                }
338
339                break;
340
341            case dbvvertica:
342                processProcedureDefinition(EDbVendor.dbvpostgresql,psql,createProcedureNode);
343                break;
344            case dbvsnowflake:
345                processProcedureDefinition(this.dbvendor,psql,createProcedureNode);
346                break;
347        }
348
349
350        this.setEndlabelName(createProcedureNode.getEndlabelName());
351
352        currentFrame.popMeFromStack(getFrameStack());
353
354        return 0;
355    }
356
357    private void doParseFunctionBody(EDbVendor dbVendor,String inputSQL,boolean isSQLBlock,int bodyDeltaLine){
358        TGSqlParser newParser = new TGSqlParser(dbVendor);
359        newParser.sqltext = inputSQL;
360        newParser.setSinglePLBlock(isSQLBlock);
361        newParser.setFrameStack(getFrameStack());
362        int iRet = newParser.parse();
363        TReparseCoordinateShifter.shift(newParser, bodyDeltaLine, 0);
364        if ((iRet == 0)&&(newParser.getSqlstatements().size() >0)){
365            if (isSQLBlock){
366                TCommonBlock commonBlock = (TCommonBlock)newParser.getSqlstatements().get(0);
367                this.setLabelName(commonBlock.getLabelName());
368                this.blockBody = commonBlock.getBlockBody();
369                this.blockBody.setParent(this);
370//                if (this.blockBody.getDeclareStatements().size()>0){
371//                    //this.getDeclareStatements().clear();
372//                    for(int i=0;i<this.blockBody.getDeclareStatements().size();i++){
373//                        this.getDeclareStatements().add(this.blockBody.getDeclareStatements().get(i));
374//                    }
375//                }
376
377//                for(int i=0;i<commonBlock.getDeclareStatements().size();i++){
378//                    this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,commonBlock.getDeclareStatements().get(i)));
379//                    this.getDeclareStatements().add(commonBlock.getDeclareStatements().get(i));
380//                }
381//                for(int i=0;i<commonBlock.getBodyStatements().size();i++){
382//                    commonBlock.getBodyStatements().get(i).setAlreadyAddToParent(false);
383//                    commonBlock.getBodyStatements().get(i).setParentStmt(this);
384//
385//                    this.getBodyStatements().add(commonBlock.getBodyStatements().get(i));
386//                }
387//                if (commonBlock.getExceptionClause() != null){
388//                    this.setExceptionClause(commonBlock.getExceptionClause());
389//                }
390//                for(int i=0;i<commonBlock.getDeclareStatements().size();i++){
391//                    this.getTopStatement().getSymbolTable().pop();
392//                }
393            }else{
394                this.getBodyStatements().add(newParser.getSqlstatements().get(0));
395            }
396        }else {
397            for(int j=0;j<newParser.getErrorCount();j++){
398                this.parseerrormessagehandle(newParser.getSyntaxErrors().get(j));
399            }
400        }
401    }
402
403    private void processProcedureDefinition(EDbVendor pDbVendor, TCustomSqlStatement psql, TCreateProcedureSqlNode createProcedureNode){
404        boolean isSQLBlock = true;
405        String inputSQL = "";
406        // Line offset applied to the re-parsed body after parsing, replacing the
407        // blank-line padding that stringBlock() capped at 1000. The column
408        // padding is kept (stringBlock first arg 0); only the line number needs
409        // shifting. A marker line ("plpgsql_function_delimiter\n") occupies
410        // parser line 1, so the body starts on parser line 2 -> delta (lineNo-2);
411        // without a marker the body starts on parser line 1 -> delta (lineNo-1).
412        int bodyDeltaLine = 0;
413
414        if ((createProcedureNode.getFunctionBody() != null)&&(getProcedureLanguage() == null || getProcedureLanguage().toString() !=null)){
415            String langStr = (getProcedureLanguage() != null) ? getProcedureLanguage().toString() : "sql";
416            if (langStr.equalsIgnoreCase("sql")
417                    ||(langStr.equalsIgnoreCase("plpgsql"))
418                    ||(langStr.equalsIgnoreCase("plvsql")) //vertica
419                    ||(langStr.equalsIgnoreCase("nzplsql"))){
420
421                String bodyStr  = createProcedureNode.getFunctionBody().getStartToken().getQuotedString();//.trim();
422                // CREATE OR REPLACE FUNCTION testspg__getString (varchar) RETURNS varchar as ' DECLARE inString alias for $1; begin return ''bob''; end; ' LANGUAGE plpgsql
423                // escaped quotes in string literals
424                // mantisbt/view.php?id=1331
425                if (createProcedureNode.getFunctionBody().getStartToken().toString().startsWith("'")){
426                    bodyStr = bodyStr.replaceAll("''","'");
427                }
428
429
430                //System.out.println(bodyStr);
431                int testLen = 9;
432                // if (bodyStr.length() < testLen) testLen = bodyStr.length();
433                String prefixStr = bodyStr.trim();
434                if (prefixStr.length() < testLen) testLen = prefixStr.length();
435
436                // String prefixStr = bodyStr.trim().substring(0,testLen).toLowerCase();
437                                prefixStr = prefixStr.substring(0,testLen).toLowerCase();
438
439                if ((prefixStr.startsWith("declare"))||(prefixStr.startsWith("begin"))||(prefixStr.startsWith("<<"))){
440                    //bodyStr.replaceAll("''","'");
441                    //System.out.println(bodyStr);
442
443                  //  newParser.sqltext = "plpgsql_function_delimiter \n"+bodyStr;
444                    bodyDeltaLine = (int)createProcedureNode.getFunctionBody().getStartToken().lineNo - 2;
445                    inputSQL = "plpgsql_function_delimiter\n"
446                            + TBaseType.stringBlock(0,(int)createProcedureNode.getFunctionBody().getStartToken().columnNo)
447                            +bodyStr;
448                }else{
449                    //newParser.sqltext = bodyStr;
450
451                    bodyDeltaLine = (int)createProcedureNode.getFunctionBody().getStartToken().lineNo - 1;
452                    inputSQL =
453                             TBaseType.stringBlock(0,(int)createProcedureNode.getFunctionBody().getStartToken().columnNo)
454                            + bodyStr;
455
456                    isSQLBlock = false;
457                }
458
459
460            }
461        }else if (createProcedureNode.getFunctionBodyTokenList() != null){
462            //netezza, function body inside BEGIN_PROC...END_PROC
463            for(int i=1;i<createProcedureNode.getFunctionBodyTokenList().size()-1;i++){
464                inputSQL = inputSQL+createProcedureNode.getFunctionBodyTokenList().get(i).toString();
465            }
466
467            // Use a newline after the marker so the column padding lands the
468            // body at the right column on its own parser line (line 2); the line
469            // offset (lineNo - 2) is applied after parsing instead of via capped
470            // blank-line padding.
471            bodyDeltaLine = (int)createProcedureNode.getFunctionBodyTokenList().get(0).lineNo - 2;
472            inputSQL = "plpgsql_function_delimiter\n"
473                    + TBaseType.stringBlock(0,(int)createProcedureNode.getFunctionBodyTokenList().get(0).columnNo)
474                        +inputSQL;
475            isSQLBlock = true;
476        }
477
478        if (inputSQL.length() > 0){
479            doParseFunctionBody(pDbVendor, inputSQL,isSQLBlock, bodyDeltaLine);
480        }
481    }
482
483
484
485    public void accept(TParseTreeVisitor v){
486        v.preVisit(this);
487        v.postVisit(this);
488    }
489
490    public void acceptChildren(TParseTreeVisitor v){
491        v.preVisit(this);
492        this.getProcedureName().acceptChildren(v);
493        if (getParameterDeclarations() != null) getParameterDeclarations().acceptChildren(v);
494        if (getBodyStatements().size() > 0) getBodyStatements().acceptChildren(v);
495        v.postVisit(this);
496    }
497
498    public void setProcedureName(TObjectName procedureName) {
499        this.procedureName = procedureName;
500    }
501
502    /**
503     * Validates that each statement in a Teradata procedure body (except the last) is terminated with a semicolon.
504     * Labels are exempt since they prefix the next statement without requiring a semicolon.
505     */
506    public static void validateSemicolonsBetweenStatements(TCustomSqlStatement psql, TStatementListSqlNode stmtList) {
507        for (int i = 0; i < stmtList.size() - 1; i++) {
508            TStatementSqlNode stmtNode = stmtList.getStatementSqlNode(i);
509            TCustomSqlStatement innerStmt = stmtNode.getStmt();
510            if (innerStmt == null) continue;
511
512            // Labels don't require semicolons (they prefix the next statement)
513            if (innerStmt.sqlstatementtype == ESqlStatementType.sstmssqllabel) {
514                continue;
515            }
516
517            // DECLARE statements (conditions, handlers, variables) don't require semicolons
518            // in Teradata procedure declaration sections
519            if (innerStmt.sqlstatementtype == ESqlStatementType.sstmssqldeclare
520                || innerStmt.sqlstatementtype == ESqlStatementType.sstplsql_vardecl) {
521                continue;
522            }
523
524            // Check for semicolon: first try the wrapper node's end token (set by grammar
525            // when semicolon is present), then fall back to the inner statement's end token
526            TSourceToken endToken = stmtNode.getEndToken();
527            if (endToken == null) {
528                endToken = innerStmt.getEndToken();
529            }
530
531            if (endToken == null || endToken.tokentype != ETokenType.ttsemicolon) {
532                // Missing semicolon - find the next statement's start token for error location
533                TStatementSqlNode nextStmtNode = stmtList.getStatementSqlNode(i + 1);
534                TCustomSqlStatement nextInnerStmt = nextStmtNode.getStmt();
535                TSourceToken nextStart = nextStmtNode.getStartToken();
536                if (nextStart == null && nextInnerStmt != null) {
537                    nextStart = nextInnerStmt.getStartToken();
538                }
539                if (nextStart != null) {
540                    psql.getAncestorStmt().parseerrormessagehandle(
541                        new TSyntaxError(nextStart,
542                            "missing semicolon before",
543                            EErrorType.spfatalerror,
544                            10102,
545                            psql.getAncestorStmt())
546                    );
547                }
548            }
549        }
550    }
551}