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){
358        TGSqlParser newParser = new TGSqlParser(dbVendor);
359        newParser.sqltext = inputSQL;
360        newParser.setSinglePLBlock(isSQLBlock);
361        newParser.setFrameStack(getFrameStack());
362        int iRet = newParser.parse();
363        if ((iRet == 0)&&(newParser.getSqlstatements().size() >0)){
364            if (isSQLBlock){
365                TCommonBlock commonBlock = (TCommonBlock)newParser.getSqlstatements().get(0);
366                this.setLabelName(commonBlock.getLabelName());
367                this.blockBody = commonBlock.getBlockBody();
368                this.blockBody.setParent(this);
369//                if (this.blockBody.getDeclareStatements().size()>0){
370//                    //this.getDeclareStatements().clear();
371//                    for(int i=0;i<this.blockBody.getDeclareStatements().size();i++){
372//                        this.getDeclareStatements().add(this.blockBody.getDeclareStatements().get(i));
373//                    }
374//                }
375
376//                for(int i=0;i<commonBlock.getDeclareStatements().size();i++){
377//                    this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,commonBlock.getDeclareStatements().get(i)));
378//                    this.getDeclareStatements().add(commonBlock.getDeclareStatements().get(i));
379//                }
380//                for(int i=0;i<commonBlock.getBodyStatements().size();i++){
381//                    commonBlock.getBodyStatements().get(i).setAlreadyAddToParent(false);
382//                    commonBlock.getBodyStatements().get(i).setParentStmt(this);
383//
384//                    this.getBodyStatements().add(commonBlock.getBodyStatements().get(i));
385//                }
386//                if (commonBlock.getExceptionClause() != null){
387//                    this.setExceptionClause(commonBlock.getExceptionClause());
388//                }
389//                for(int i=0;i<commonBlock.getDeclareStatements().size();i++){
390//                    this.getTopStatement().getSymbolTable().pop();
391//                }
392            }else{
393                this.getBodyStatements().add(newParser.getSqlstatements().get(0));
394            }
395        }else {
396            for(int j=0;j<newParser.getErrorCount();j++){
397                this.parseerrormessagehandle(newParser.getSyntaxErrors().get(j));
398            }
399        }
400    }
401
402    private void processProcedureDefinition(EDbVendor pDbVendor, TCustomSqlStatement psql, TCreateProcedureSqlNode createProcedureNode){
403        boolean isSQLBlock = true;
404        String inputSQL = "";
405
406        if ((createProcedureNode.getFunctionBody() != null)&&(getProcedureLanguage() == null || getProcedureLanguage().toString() !=null)){
407            String langStr = (getProcedureLanguage() != null) ? getProcedureLanguage().toString() : "sql";
408            if (langStr.equalsIgnoreCase("sql")
409                    ||(langStr.equalsIgnoreCase("plpgsql"))
410                    ||(langStr.equalsIgnoreCase("plvsql")) //vertica
411                    ||(langStr.equalsIgnoreCase("nzplsql"))){
412
413                String bodyStr  = createProcedureNode.getFunctionBody().getStartToken().getQuotedString();//.trim();
414                // CREATE OR REPLACE FUNCTION testspg__getString (varchar) RETURNS varchar as ' DECLARE inString alias for $1; begin return ''bob''; end; ' LANGUAGE plpgsql
415                // escaped quotes in string literals
416                // mantisbt/view.php?id=1331
417                if (createProcedureNode.getFunctionBody().getStartToken().toString().startsWith("'")){
418                    bodyStr = bodyStr.replaceAll("''","'");
419                }
420
421
422                //System.out.println(bodyStr);
423                int testLen = 9;
424                // if (bodyStr.length() < testLen) testLen = bodyStr.length();
425                String prefixStr = bodyStr.trim();
426                if (prefixStr.length() < testLen) testLen = prefixStr.length();
427
428                // String prefixStr = bodyStr.trim().substring(0,testLen).toLowerCase();
429                                prefixStr = prefixStr.substring(0,testLen).toLowerCase();
430
431                if ((prefixStr.startsWith("declare"))||(prefixStr.startsWith("begin"))||(prefixStr.startsWith("<<"))){
432                    //bodyStr.replaceAll("''","'");
433                    //System.out.println(bodyStr);
434
435                  //  newParser.sqltext = "plpgsql_function_delimiter \n"+bodyStr;
436                    inputSQL = "plpgsql_function_delimiter\n"
437                            + TBaseType.stringBlock((int)createProcedureNode.getFunctionBody().getStartToken().lineNo - 2,(int)createProcedureNode.getFunctionBody().getStartToken().columnNo)
438                            +bodyStr;
439                }else{
440                    //newParser.sqltext = bodyStr;
441
442                    inputSQL =
443                             TBaseType.stringBlock((int)createProcedureNode.getFunctionBody().getStartToken().lineNo - 1,(int)createProcedureNode.getFunctionBody().getStartToken().columnNo)
444                            + bodyStr;
445
446                    isSQLBlock = false;
447                }
448
449
450            }
451        }else if (createProcedureNode.getFunctionBodyTokenList() != null){
452            //netezza, function body inside BEGIN_PROC...END_PROC
453            for(int i=1;i<createProcedureNode.getFunctionBodyTokenList().size()-1;i++){
454                inputSQL = inputSQL+createProcedureNode.getFunctionBodyTokenList().get(i).toString();
455            }
456
457            inputSQL = "plpgsql_function_delimiter "
458                    + TBaseType.stringBlock((int)createProcedureNode.getFunctionBodyTokenList().get(0).lineNo - 1,(int)createProcedureNode.getFunctionBodyTokenList().get(0).columnNo)
459                        +inputSQL;
460            isSQLBlock = true;
461        }
462
463        if (inputSQL.length() > 0){
464            doParseFunctionBody(pDbVendor, inputSQL,isSQLBlock);
465        }
466    }
467
468
469
470    public void accept(TParseTreeVisitor v){
471        v.preVisit(this);
472        v.postVisit(this);
473    }
474
475    public void acceptChildren(TParseTreeVisitor v){
476        v.preVisit(this);
477        this.getProcedureName().acceptChildren(v);
478        if (getParameterDeclarations() != null) getParameterDeclarations().acceptChildren(v);
479        if (getBodyStatements().size() > 0) getBodyStatements().acceptChildren(v);
480        v.postVisit(this);
481    }
482
483    public void setProcedureName(TObjectName procedureName) {
484        this.procedureName = procedureName;
485    }
486
487    /**
488     * Validates that each statement in a Teradata procedure body (except the last) is terminated with a semicolon.
489     * Labels are exempt since they prefix the next statement without requiring a semicolon.
490     */
491    public static void validateSemicolonsBetweenStatements(TCustomSqlStatement psql, TStatementListSqlNode stmtList) {
492        for (int i = 0; i < stmtList.size() - 1; i++) {
493            TStatementSqlNode stmtNode = stmtList.getStatementSqlNode(i);
494            TCustomSqlStatement innerStmt = stmtNode.getStmt();
495            if (innerStmt == null) continue;
496
497            // Labels don't require semicolons (they prefix the next statement)
498            if (innerStmt.sqlstatementtype == ESqlStatementType.sstmssqllabel) {
499                continue;
500            }
501
502            // DECLARE statements (conditions, handlers, variables) don't require semicolons
503            // in Teradata procedure declaration sections
504            if (innerStmt.sqlstatementtype == ESqlStatementType.sstmssqldeclare
505                || innerStmt.sqlstatementtype == ESqlStatementType.sstplsql_vardecl) {
506                continue;
507            }
508
509            // Check for semicolon: first try the wrapper node's end token (set by grammar
510            // when semicolon is present), then fall back to the inner statement's end token
511            TSourceToken endToken = stmtNode.getEndToken();
512            if (endToken == null) {
513                endToken = innerStmt.getEndToken();
514            }
515
516            if (endToken == null || endToken.tokentype != ETokenType.ttsemicolon) {
517                // Missing semicolon - find the next statement's start token for error location
518                TStatementSqlNode nextStmtNode = stmtList.getStatementSqlNode(i + 1);
519                TCustomSqlStatement nextInnerStmt = nextStmtNode.getStmt();
520                TSourceToken nextStart = nextStmtNode.getStartToken();
521                if (nextStart == null && nextInnerStmt != null) {
522                    nextStart = nextInnerStmt.getStartToken();
523                }
524                if (nextStart != null) {
525                    psql.getAncestorStmt().parseerrormessagehandle(
526                        new TSyntaxError(nextStart,
527                            "missing semicolon before",
528                            EErrorType.spfatalerror,
529                            10102,
530                            psql.getAncestorStmt())
531                    );
532                }
533            }
534        }
535    }
536}