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
041    public TConstant getProcedureLanguage() {
042        return procedureLanguage;
043    }
044
045    public TConstant getLinkSymbol() {
046        return linkSymbol;
047    }
048    public TConstant getObjfile() {
049        return objfile;
050    }
051
052    private TPTNodeList <TProcedureOption> procedureOptions;
053    private boolean isForReplication;
054
055    public void setProcedureOptions(TPTNodeList<TProcedureOption> procedureOptions) {
056        this.procedureOptions = procedureOptions;
057    }
058
059    public TPTNodeList<TProcedureOption> getProcedureOptions() {
060
061        return procedureOptions;
062    }
063
064    public void setForReplication(boolean isForReplication) {
065        this.isForReplication = isForReplication;
066    }
067
068    public boolean isForReplication() {
069        return isForReplication;
070    }
071
072    public TCreateProcedureStmt (EDbVendor dbvendor){
073        super(dbvendor);
074        sqlstatementtype = ESqlStatementType.sstcreateprocedure ;
075    }
076
077    @Override
078    public TObjectName getStoredProcedureName(){
079        return procedureName;
080    }
081
082    /**
083     * The name that you give to the procedure that you are declaring or defining.
084     * @return
085     */
086    public TObjectName getProcedureName() {
087        return procedureName;
088    }
089
090    private TObjectName procedureName = null;
091
092
093
094    public int doParseStatement(TCustomSqlStatement psql) {
095        if (rootNode == null) return -1;
096        TCreateProcedureSqlNode createProcedureNode = (TCreateProcedureSqlNode)rootNode;
097
098        super.doParseStatement(psql);
099        TFrame currentFrame = new TFrame(this.stmtScope);
100        currentFrame.pushMeToStack(getFrameStack());
101
102        procedureName = createProcedureNode.getProcedureName();
103
104        if (getSqlEnv() != null)  {
105            getSqlEnv().addProcedure(procedureName.toString(),true);
106
107            // move to TDatabaseObjectResolver
108
109//            if (getSqlEnv().getDefaultCatalogName() != null){
110//                if (procedureName.getDatabaseToken() == null){
111//                    procedureName.setDatabaseToken(new TSourceToken(getSqlEnv().getDefaultCatalogName()));
112//                }
113//            }
114        }
115
116        setRoutineName(procedureName);
117
118        procedureLanguage = createProcedureNode.getProcedureLanguage();
119        if (procedureLanguage != null){
120            if (getRoutineLanguage() == null){
121                // not already set during TGsqlParser.dopostgresqlgetrawsqlstatements()
122                // then we set it here
123                setRoutineLanguage(procedureLanguage.toString());
124            }
125            setRoutineLanguageInConstant(procedureLanguage);
126        }else {
127
128            if (dbvendor == EDbVendor.dbvvertica){
129                // vertica, set default to plvsql
130                TConstant c = new TConstant();
131                c.init(ELiteralType.etString, new TSourceToken("plvsql"));
132                procedureLanguage = c;
133                setRoutineLanguageInConstant(procedureLanguage);
134                setRoutineLanguage("plvsql");
135            }
136
137        }
138        this.setLabelName(createProcedureNode.getLabelName());
139        objfile = createProcedureNode.getObjfile();
140        linkSymbol = createProcedureNode.getLinkSymbol();
141        this.setReturnDataType(createProcedureNode.getReturnDataType());
142        if (createProcedureNode.getFunctionBody() != null){
143            setRoutineBodyInConstant(createProcedureNode.getFunctionBody());
144            setRoutineBody(createProcedureNode.getFunctionBody().toString());
145        }
146
147        this.setParameterDeclarations( createProcedureNode.getParameters());
148        // sql server
149        procedureOptions = createProcedureNode.getProcedureOptions();
150        isForReplication  = createProcedureNode.isForReplication();
151        this.setReturnDataType(createProcedureNode.getReturnDataType());
152        // end sql server
153
154        // push parameterDeclarations into symbolTable
155        if (this.getParameterDeclarations() != null){
156            for(int i=0;i< this.getParameterDeclarations().size();i++){
157                this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjParameter,this, this.getParameterDeclarations().getParameterDeclarationItem(i)));
158                TParameterDeclaration parameterDeclaration = this.getParameterDeclarations().getParameterDeclarationItem(i);
159                if (parameterDeclaration.getParameterName() != null){
160                    TVariable variable = new TVariable(parameterDeclaration.getParameterName(),parameterDeclaration,procedureName);
161
162                    TVariable variableOfThisParam =  TSymbolTableManager.searchSymbolVariable(this.getFrameStack(),procedureName+".$"+i);
163                    if (variableOfThisParam != null){
164                        variable.setVariableStr(variableOfThisParam.getVariableStr());
165                    }
166
167                   // variable.variableStr = TBaseType.getStringInsideLiteral(getSqlEnv().getProcedureParameterValue(procedureName.toString(), i));
168                    this.stmtScope.addSymbol(variable);
169                }
170            }
171        }
172
173
174        switch (dbvendor){
175            case dbvdb2:
176                TCompoundSqlNode compoundSqlNode = createProcedureNode.getCompoundSqls();
177                if (compoundSqlNode != null){
178                    if (compoundSqlNode.getDeclareStmts() != null){
179                        compoundSqlNode.getDeclareStmts().doParse(this,ESqlClause.unknown);
180
181                        // push variable declare into symbolTable, and add to declareStatements
182                        for(int i=0;i<compoundSqlNode.getDeclareStmts().size();i++){
183                            this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,compoundSqlNode.getDeclareStmts().getStatementSqlNode(i).getStmt() ));
184                            this.getDeclareStatements().add(compoundSqlNode.getDeclareStmts().getStatementSqlNode(i).getStmt());
185                        }
186                    }
187
188                    if (compoundSqlNode.getStmts() != null){
189                        compoundSqlNode.getStmts().doParse(this,ESqlClause.unknown);
190
191                        for(int i= 0; i<compoundSqlNode.getStmts().size();i++){
192                            this.getBodyStatements().add(compoundSqlNode.getStmts().getStatementSqlNode(i).getStmt());
193                        }
194                    }
195
196                    if (compoundSqlNode.getDeclareStmts() != null){
197                        // pop variable declare from symbolTable
198                        for(int i=0;i<compoundSqlNode.getDeclareStmts().size();i++){
199                            this.getTopStatement().getSymbolTable().pop();
200                        }
201                    }
202                }
203                break;
204            case dbvsnowflake:
205                if (createProcedureNode.getBlcok() != null){
206                    createProcedureNode.getBlcok().doParse(this,ESqlClause.unknown);
207                    this.blockBody = createProcedureNode.getBlcok();
208                }else{
209                    // javascript code body
210                }
211
212                break;
213            case dbvnetezza:
214                if (createProcedureNode.getStmts() != null){
215                    createProcedureNode.getStmts().doParse(this, ESqlClause.unknown);
216
217                    TCommonBlock commonBlock = (TCommonBlock)createProcedureNode.getStmts().getStatementSqlNode(0).getStmt();
218                    this.setLabelName(commonBlock.getLabelName());
219                    this.blockBody = commonBlock.getBlockBody();
220                    this.blockBody.setParent(this);
221
222//                    for(int i=0;i<createProcedureNode.getStmts().size();i++){
223//                        this.getBodyStatements().add(createProcedureNode.getStmts().getStatementSqlNode(i).getStmt());
224//                    }
225                }else if ((createProcedureNode.getFunctionBody() != null)&&(getProcedureLanguage().toString() !=null)){
226                    // use constant in procedure body
227                    processProcedureDefinition(EDbVendor.dbvnetezza,psql,createProcedureNode);
228                }
229                break;
230            default:
231                if (createProcedureNode.getDeclareStmts() != null){
232                    createProcedureNode.getDeclareStmts().doParse(this, ESqlClause.unknown);
233
234                    // push variable declare into symbolTable    , and add to declareStatements
235                    for(int i=0;i<createProcedureNode.getDeclareStmts().size();i++){
236                        this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,createProcedureNode.getDeclareStmts().getStatementSqlNode(i).getStmt() ));
237                        this.getDeclareStatements().add(createProcedureNode.getDeclareStmts().getStatementSqlNode(i).getStmt());
238                    }
239
240                }
241
242                if (createProcedureNode.getStmt() != null){
243                    createProcedureNode.getStmt().doParse(this, ESqlClause.unknown);
244                    this.getBodyStatements().add(createProcedureNode.getStmt().getStmt());
245                }
246                else if (createProcedureNode.getBlcok() != null){
247                    createProcedureNode.getBlcok().doParse(this,ESqlClause.unknown);
248                    this.blockBody = createProcedureNode.getBlcok();
249                }else  if (createProcedureNode.getStmts() != null){ //teradata,sql server
250                    createProcedureNode.getStmts().doParse(this, ESqlClause.unknown);
251                    for(int i=0;i<createProcedureNode.getStmts().size();i++){
252                        this.getBodyStatements().add(createProcedureNode.getStmts().getStatementSqlNode(i).getStmt());
253                    }
254                }
255
256                if (createProcedureNode.getExceptionClause() != null){
257                    createProcedureNode.getExceptionClause().doParse(this,ESqlClause.unknown);
258                    this.setExceptionClause(createProcedureNode.getExceptionClause());
259                }
260
261                if (createProcedureNode.getDeclareStmts() != null){
262
263                    // pop variable declare from symbolTable
264                    for(int i=0;i<createProcedureNode.getDeclareStmts().size();i++){
265                        this.getTopStatement().getSymbolTable().pop();
266                    }
267
268                }
269
270                break;
271
272        }
273
274        // pop parameterDeclarations from symbolTable
275        if (this.getParameterDeclarations() != null){
276            for(int i=0;i< this.getParameterDeclarations().size();i++){
277                this.getTopStatement().getSymbolTable().pop();
278            }
279        }
280
281        switch (this.dbvendor){
282            case dbvpostgresql:
283            case dbvredshift:
284                if (createProcedureNode.getFunctionBody() != null){
285                    // function body only inside '' will be processed here
286                    processProcedureDefinition(EDbVendor.dbvpostgresql,psql,createProcedureNode);
287                }else{
288                    // procedure body only inside $$ already processed in the above default section
289                }
290
291                break;
292
293            case dbvvertica:
294                processProcedureDefinition(EDbVendor.dbvpostgresql,psql,createProcedureNode);
295                break;
296            case dbvsnowflake:
297                processProcedureDefinition(this.dbvendor,psql,createProcedureNode);
298                break;
299        }
300
301
302        this.setEndlabelName(createProcedureNode.getEndlabelName());
303
304        currentFrame.popMeFromStack(getFrameStack());
305
306        return 0;
307    }
308
309    private void doParseFunctionBody(EDbVendor dbVendor,String inputSQL,boolean isSQLBlock){
310        TGSqlParser newParser = new TGSqlParser(dbVendor);
311        newParser.sqltext = inputSQL;
312        newParser.setSinglePLBlock(isSQLBlock);
313        newParser.setFrameStack(getFrameStack());
314        int iRet = newParser.parse();
315        if ((iRet == 0)&&(newParser.getSqlstatements().size() >0)){
316            if (isSQLBlock){
317                TCommonBlock commonBlock = (TCommonBlock)newParser.getSqlstatements().get(0);
318                this.setLabelName(commonBlock.getLabelName());
319                this.blockBody = commonBlock.getBlockBody();
320                this.blockBody.setParent(this);
321//                if (this.blockBody.getDeclareStatements().size()>0){
322//                    //this.getDeclareStatements().clear();
323//                    for(int i=0;i<this.blockBody.getDeclareStatements().size();i++){
324//                        this.getDeclareStatements().add(this.blockBody.getDeclareStatements().get(i));
325//                    }
326//                }
327
328//                for(int i=0;i<commonBlock.getDeclareStatements().size();i++){
329//                    this.getTopStatement().getSymbolTable().push( new TSymbolTableItem(TObjectName.ttobjVariable,this,commonBlock.getDeclareStatements().get(i)));
330//                    this.getDeclareStatements().add(commonBlock.getDeclareStatements().get(i));
331//                }
332//                for(int i=0;i<commonBlock.getBodyStatements().size();i++){
333//                    commonBlock.getBodyStatements().get(i).setAlreadyAddToParent(false);
334//                    commonBlock.getBodyStatements().get(i).setParentStmt(this);
335//
336//                    this.getBodyStatements().add(commonBlock.getBodyStatements().get(i));
337//                }
338//                if (commonBlock.getExceptionClause() != null){
339//                    this.setExceptionClause(commonBlock.getExceptionClause());
340//                }
341//                for(int i=0;i<commonBlock.getDeclareStatements().size();i++){
342//                    this.getTopStatement().getSymbolTable().pop();
343//                }
344            }else{
345                this.getBodyStatements().add(newParser.getSqlstatements().get(0));
346            }
347        }else {
348            for(int j=0;j<newParser.getErrorCount();j++){
349                this.parseerrormessagehandle(newParser.getSyntaxErrors().get(j));
350            }
351        }
352    }
353
354    private void processProcedureDefinition(EDbVendor pDbVendor, TCustomSqlStatement psql, TCreateProcedureSqlNode createProcedureNode){
355        boolean isSQLBlock = true;
356        String inputSQL = "";
357
358        if ((createProcedureNode.getFunctionBody() != null)&&(getProcedureLanguage().toString() !=null)){
359            if (getProcedureLanguage().toString().equalsIgnoreCase("sql")
360                    ||(getProcedureLanguage().toString().equalsIgnoreCase("plpgsql"))
361                    ||(getProcedureLanguage().toString().equalsIgnoreCase("plvsql")) //vertica
362                    ||(getProcedureLanguage().toString().equalsIgnoreCase("nzplsql"))){
363
364                String bodyStr  = createProcedureNode.getFunctionBody().getStartToken().getQuotedString();//.trim();
365                // CREATE OR REPLACE FUNCTION testspg__getString (varchar) RETURNS varchar as ' DECLARE inString alias for $1; begin return ''bob''; end; ' LANGUAGE plpgsql
366                // escaped quotes in string literals
367                // mantisbt/view.php?id=1331
368                if (createProcedureNode.getFunctionBody().getStartToken().toString().startsWith("'")){
369                    bodyStr = bodyStr.replaceAll("''","'");
370                }
371
372
373                //System.out.println(bodyStr);
374                int testLen = 9;
375                // if (bodyStr.length() < testLen) testLen = bodyStr.length();
376                String prefixStr = bodyStr.trim();
377                if (prefixStr.length() < testLen) testLen = prefixStr.length();
378
379                // String prefixStr = bodyStr.trim().substring(0,testLen).toLowerCase();
380                                prefixStr = prefixStr.substring(0,testLen).toLowerCase();
381
382                if ((prefixStr.startsWith("declare"))||(prefixStr.startsWith("begin"))||(prefixStr.startsWith("<<"))){
383                    //bodyStr.replaceAll("''","'");
384                    //System.out.println(bodyStr);
385
386                  //  newParser.sqltext = "plpgsql_function_delimiter \n"+bodyStr;
387                    inputSQL = "plpgsql_function_delimiter\n"
388                            + TBaseType.stringBlock((int)createProcedureNode.getFunctionBody().getStartToken().lineNo - 2,(int)createProcedureNode.getFunctionBody().getStartToken().columnNo)
389                            +bodyStr;
390                }else{
391                    //newParser.sqltext = bodyStr;
392
393                    inputSQL =
394                             TBaseType.stringBlock((int)createProcedureNode.getFunctionBody().getStartToken().lineNo - 1,(int)createProcedureNode.getFunctionBody().getStartToken().columnNo)
395                            + bodyStr;
396
397                    isSQLBlock = false;
398                }
399
400
401            }
402        }else if (createProcedureNode.getFunctionBodyTokenList() != null){
403            //netezza, function body inside BEGIN_PROC...END_PROC
404            for(int i=1;i<createProcedureNode.getFunctionBodyTokenList().size()-1;i++){
405                inputSQL = inputSQL+createProcedureNode.getFunctionBodyTokenList().get(i).toString();
406            }
407
408            inputSQL = "plpgsql_function_delimiter "
409                    + TBaseType.stringBlock((int)createProcedureNode.getFunctionBodyTokenList().get(0).lineNo - 1,(int)createProcedureNode.getFunctionBodyTokenList().get(0).columnNo)
410                        +inputSQL;
411            isSQLBlock = true;
412        }
413
414        if (inputSQL.length() > 0){
415           // System.out.println(inputSQL);
416            doParseFunctionBody(pDbVendor, inputSQL,isSQLBlock);
417        }
418    }
419
420
421
422    public void accept(TParseTreeVisitor v){
423        v.preVisit(this);
424        v.postVisit(this);
425    }
426
427    public void acceptChildren(TParseTreeVisitor v){
428        v.preVisit(this);
429        this.getProcedureName().acceptChildren(v);
430        if (getParameterDeclarations() != null) getParameterDeclarations().acceptChildren(v);
431        if (getBodyStatements().size() > 0) getBodyStatements().acceptChildren(v);
432        v.postVisit(this);
433    }
434
435    public void setProcedureName(TObjectName procedureName) {
436        this.procedureName = procedureName;
437    }
438}