Skip to content

Commit 4bfecac

Browse files
VallishpDoris-Extras
authored andcommitted
[enhancement](plsql) Support show procedure and show create procedure (#31297) (#31763)
1 parent 1b783aa commit 4bfecac

14 files changed

Lines changed: 290 additions & 15 deletions

File tree

‎fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,8 @@ statement
3838
| CALL name=multipartIdentifier LEFT_PAREN (expression (COMMA expression)*)? RIGHT_PAREN #callProcedure
3939
| (ALTER | CREATE (OR REPLACE)? | REPLACE) (PROCEDURE | PROC) name=multipartIdentifier LEFT_PAREN .*? RIGHT_PAREN .*? #createProcedure
4040
| DROP (PROCEDURE | PROC) (IF EXISTS)? name=multipartIdentifier #dropProcedure
41+
| SHOW PROCEDURE STATUS (LIKE pattern=valueExpression | whereClause)? #showProcedureStatus
42+
| SHOW CREATE PROCEDURE name=multipartIdentifier #showCreateProcedure
4143
;
4244

4345
statementBase

‎fe/fe-core/src/main/antlr4/org/apache/doris/nereids/PLParser.g4‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -99,6 +99,8 @@ stmt :
9999
| create_procedure_stmt
100100
| declare_stmt
101101
| drop_procedure_stmt
102+
| show_procedure_stmt
103+
| show_create_procedure_stmt
102104
| exec_stmt
103105
| exit_stmt
104106
| fetch_stmt
@@ -333,6 +335,13 @@ create_procedure_stmt :
333335
drop_procedure_stmt:
334336
DROP (PROCEDURE | PROC) (IF EXISTS)? name=multipartIdentifier
335337
;
338+
show_procedure_stmt:
339+
SHOW PROCEDURE STATUS (LIKE pattern=valueExpression | whereClause)?
340+
;
341+
342+
show_create_procedure_stmt:
343+
SHOW CREATE PROCEDURE name=multipartIdentifier
344+
;
336345

337346
create_routine_params :
338347
LEFT_PAREN RIGHT_PAREN

‎fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -161,6 +161,8 @@
161161
import org.apache.doris.nereids.DorisParser.SelectHintContext;
162162
import org.apache.doris.nereids.DorisParser.SetOperationContext;
163163
import org.apache.doris.nereids.DorisParser.ShowConstraintContext;
164+
import org.apache.doris.nereids.DorisParser.ShowCreateProcedureContext;
165+
import org.apache.doris.nereids.DorisParser.ShowProcedureStatusContext;
164166
import org.apache.doris.nereids.DorisParser.SimpleColumnDefContext;
165167
import org.apache.doris.nereids.DorisParser.SimpleColumnDefsContext;
166168
import org.apache.doris.nereids.DorisParser.SingleStatementContext;
@@ -370,6 +372,8 @@
370372
import org.apache.doris.nereids.trees.plans.commands.RefreshMTMVCommand;
371373
import org.apache.doris.nereids.trees.plans.commands.ResumeMTMVCommand;
372374
import org.apache.doris.nereids.trees.plans.commands.ShowConstraintsCommand;
375+
import org.apache.doris.nereids.trees.plans.commands.ShowCreateProcedureCommand;
376+
import org.apache.doris.nereids.trees.plans.commands.ShowProcedureStatusCommand;
373377
import org.apache.doris.nereids.trees.plans.commands.UpdateCommand;
374378
import org.apache.doris.nereids.trees.plans.commands.info.AlterMTMVInfo;
375379
import org.apache.doris.nereids.trees.plans.commands.info.AlterMTMVPropertyInfo;
@@ -3372,4 +3376,16 @@ public LogicalPlan visitDropProcedure(DropProcedureContext ctx) {
33723376
FuncNameInfo procedureName = new FuncNameInfo(nameParts);
33733377
return ParserUtils.withOrigin(ctx, () -> new DropProcedureCommand(procedureName, getOriginSql(ctx)));
33743378
}
3379+
3380+
@Override
3381+
public LogicalPlan visitShowProcedureStatus(ShowProcedureStatusContext ctx) {
3382+
return ParserUtils.withOrigin(ctx, () -> new ShowProcedureStatusCommand());
3383+
}
3384+
3385+
@Override
3386+
public LogicalPlan visitShowCreateProcedure(ShowCreateProcedureContext ctx) {
3387+
List<String> nameParts = visitMultipartIdentifier(ctx.name);
3388+
FuncNameInfo procedureName = new FuncNameInfo(nameParts);
3389+
return ParserUtils.withOrigin(ctx, () -> new ShowCreateProcedureCommand(procedureName));
3390+
}
33753391
}

‎fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/PlanType.java‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -143,5 +143,7 @@ public enum PlanType {
143143
CANCEL_MTMV_TASK_COMMAND,
144144
CALL_COMMAND,
145145
CREATE_PROCEDURE_COMMAND,
146-
DROP_PROCEDURE_COMMAND
146+
DROP_PROCEDURE_COMMAND,
147+
SHOW_PROCEDURE_COMMAND,
148+
SHOW_CREATE_PROCEDURE_COMMAND
147149
}
Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,78 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
package org.apache.doris.nereids.trees.plans.commands;
19+
20+
import org.apache.doris.catalog.Column;
21+
import org.apache.doris.catalog.ScalarType;
22+
import org.apache.doris.nereids.trees.plans.PlanType;
23+
import org.apache.doris.nereids.trees.plans.commands.info.FuncNameInfo;
24+
import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
25+
import org.apache.doris.qe.ConnectContext;
26+
import org.apache.doris.qe.ShowResultSet;
27+
import org.apache.doris.qe.ShowResultSetMetaData;
28+
import org.apache.doris.qe.StmtExecutor;
29+
30+
import com.google.common.collect.ImmutableList;
31+
import org.apache.logging.log4j.LogManager;
32+
import org.apache.logging.log4j.Logger;
33+
34+
import java.util.ArrayList;
35+
import java.util.List;
36+
37+
/**
38+
* show create procedure command
39+
*/
40+
public class ShowCreateProcedureCommand extends Command implements NoForward {
41+
42+
public static final Logger LOG = LogManager.getLogger(ShowCreateProcedureCommand.class);
43+
public static final ImmutableList<String> TITLE_NAMES = new ImmutableList.Builder<String>().add("Procedure")
44+
.add("Create Procedure").build();
45+
private final FuncNameInfo procedureName;
46+
/**
47+
* constructor
48+
*/
49+
50+
public ShowCreateProcedureCommand(FuncNameInfo procedureName) {
51+
super(PlanType.SHOW_PROCEDURE_COMMAND);
52+
this.procedureName = procedureName;
53+
54+
}
55+
56+
public ShowResultSetMetaData getMetaData() {
57+
ShowResultSetMetaData.Builder builder = ShowResultSetMetaData.builder();
58+
for (String title : TITLE_NAMES) {
59+
builder.addColumn(new Column(title, ScalarType.createStringType()));
60+
}
61+
return builder.build();
62+
}
63+
64+
@Override
65+
public void run(ConnectContext ctx, StmtExecutor executor) throws Exception {
66+
List<List<String>> results = new ArrayList<>();
67+
ctx.getPlSqlOperation().getExec().functions.showCreateProcedure(this.procedureName, results);
68+
if (!results.isEmpty()) {
69+
ShowResultSet commonResultSet = new ShowResultSet(getMetaData(), results);
70+
executor.sendResultSet(commonResultSet);
71+
}
72+
}
73+
74+
@Override
75+
public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
76+
return visitor.visitShowCreateProcedureCommand(this, context);
77+
}
78+
}
Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
package org.apache.doris.nereids.trees.plans.commands;
19+
20+
import org.apache.doris.catalog.Column;
21+
import org.apache.doris.catalog.ScalarType;
22+
import org.apache.doris.nereids.trees.plans.PlanType;
23+
import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
24+
import org.apache.doris.qe.ConnectContext;
25+
import org.apache.doris.qe.ShowResultSet;
26+
import org.apache.doris.qe.ShowResultSetMetaData;
27+
import org.apache.doris.qe.StmtExecutor;
28+
29+
import com.google.common.collect.ImmutableList;
30+
import org.apache.logging.log4j.LogManager;
31+
import org.apache.logging.log4j.Logger;
32+
33+
import java.util.ArrayList;
34+
import java.util.List;
35+
36+
/**
37+
* show procedure status command
38+
*/
39+
public class ShowProcedureStatusCommand extends Command implements NoForward {
40+
41+
public static final Logger LOG = LogManager.getLogger(ShowProcedureStatusCommand.class);
42+
public static final ImmutableList<String> TITLE_NAMES = new ImmutableList.Builder<String>().add("ProcedureName")
43+
.add("CatalogId").add("DbId").add("PackageName")
44+
.add("OwnerName").add("CreateTime").add("ModifyTime")
45+
.build();
46+
47+
/**
48+
* constructor
49+
*/
50+
public ShowProcedureStatusCommand() {
51+
super(PlanType.SHOW_PROCEDURE_COMMAND);
52+
}
53+
54+
public ShowResultSetMetaData getMetaData() {
55+
ShowResultSetMetaData.Builder builder = ShowResultSetMetaData.builder();
56+
for (String title : TITLE_NAMES) {
57+
builder.addColumn(new Column(title, ScalarType.createStringType()));
58+
}
59+
return builder.build();
60+
}
61+
62+
@Override
63+
public void run(ConnectContext ctx, StmtExecutor executor) throws Exception {
64+
List<List<String>> results = new ArrayList<>();
65+
ctx.getPlSqlOperation().getExec().functions.showProcedure(results);
66+
if (!results.isEmpty()) {
67+
ShowResultSet commonResultSet = new ShowResultSet(getMetaData(), results);
68+
executor.sendResultSet(commonResultSet);
69+
}
70+
}
71+
72+
@Override
73+
public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
74+
return visitor.visitShowProcedureStatusCommand(this, context);
75+
}
76+
}

‎fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/CommandVisitor.java‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,8 @@
4141
import org.apache.doris.nereids.trees.plans.commands.RefreshMTMVCommand;
4242
import org.apache.doris.nereids.trees.plans.commands.ResumeMTMVCommand;
4343
import org.apache.doris.nereids.trees.plans.commands.ShowConstraintsCommand;
44+
import org.apache.doris.nereids.trees.plans.commands.ShowCreateProcedureCommand;
45+
import org.apache.doris.nereids.trees.plans.commands.ShowProcedureStatusCommand;
4446
import org.apache.doris.nereids.trees.plans.commands.UpdateCommand;
4547

4648
/** CommandVisitor. */
@@ -146,4 +148,12 @@ default R visitCreateProcedureCommand(CreateProcedureCommand createProcedureComm
146148
default R visitDropProcedureCommand(DropProcedureCommand dropProcedureCommand, C context) {
147149
return visitCommand(dropProcedureCommand, context);
148150
}
151+
152+
default R visitShowProcedureStatusCommand(ShowProcedureStatusCommand showProcedureStatusCommand, C context) {
153+
return visitCommand(showProcedureStatusCommand, context);
154+
}
155+
156+
default R visitShowCreateProcedureCommand(ShowCreateProcedureCommand showCreateProcedureCommand, C context) {
157+
return visitCommand(showCreateProcedureCommand, context);
158+
}
149159
}

‎fe/fe-core/src/main/java/org/apache/doris/plsql/functions/DorisFunctionRegistry.java‎

Lines changed: 45 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,15 +32,19 @@
3232
import org.apache.doris.plsql.Scope;
3333
import org.apache.doris.plsql.Var;
3434
import org.apache.doris.plsql.metastore.PlsqlMetaClient;
35+
import org.apache.doris.plsql.metastore.PlsqlProcedureKey;
3536
import org.apache.doris.plsql.metastore.PlsqlStoredProcedure;
3637
import org.apache.doris.qe.ConnectContext;
3738

3839
import org.antlr.v4.runtime.CharStreams;
3940
import org.antlr.v4.runtime.CommonTokenStream;
4041
import org.antlr.v4.runtime.ParserRuleContext;
4142

43+
import java.text.SimpleDateFormat;
4244
import java.util.ArrayList;
45+
import java.util.Calendar;
4346
import java.util.HashMap;
47+
import java.util.List;
4448
import java.util.Map;
4549
import java.util.Optional;
4650

@@ -85,6 +89,35 @@ private String qualified(String name) {
8589
return (ConnectContext.get().getDatabase() + "." + name).toUpperCase();
8690
}
8791

92+
@Override
93+
public void showProcedure(List<List<String>> columns) {
94+
Map<PlsqlProcedureKey, PlsqlStoredProcedure> allProc = client.getAllPlsqlStoredProcedures();
95+
for (Map.Entry<PlsqlProcedureKey, PlsqlStoredProcedure> entry : allProc.entrySet()) {
96+
List<String> row = new ArrayList<>();
97+
PlsqlStoredProcedure proc = entry.getValue();
98+
row.add(proc.getName());
99+
row.add(Long.toString(proc.getCatalogId()));
100+
row.add(Long.toString(proc.getDbId()));
101+
row.add(proc.getPackageName());
102+
row.add(proc.getOwnerName());
103+
row.add(proc.getCreateTime());
104+
row.add(proc.getModifyTime());
105+
row.add(proc.getSource());
106+
columns.add(row);
107+
}
108+
}
109+
110+
@Override
111+
public void showCreateProcedure(FuncNameInfo procedureName, List<List<String>> columns) {
112+
List<String> row = new ArrayList<>();
113+
PlsqlStoredProcedure proc = client.getPlsqlStoredProcedure(procedureName.getName(),
114+
procedureName.getCtlId(), procedureName.getDbId());
115+
if (proc != null) {
116+
row.add(proc.getName());
117+
row.add(proc.getSource());
118+
columns.add(row);
119+
}
120+
}
88121

89122
@Override
90123
public boolean exec(FuncNameInfo procedureName, Expr_func_paramsContext ctx) {
@@ -196,10 +229,21 @@ public void addUserProcedure(Create_procedure_stmtContext ctx) {
196229
@Override
197230
public void save(FuncNameInfo procedureName, String source, boolean isForce) {
198231
try {
232+
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
233+
String createTime = f.format(Calendar.getInstance().getTime());
234+
String modifyTime = createTime;
235+
if (isForce) {
236+
// need to get create time and use that.
237+
PlsqlStoredProcedure oldProc = client.getPlsqlStoredProcedure(procedureName.getName(),
238+
procedureName.getCtlId(), procedureName.getDbId());
239+
if (oldProc != null) {
240+
createTime = oldProc.getCreateTime();
241+
}
242+
}
199243
// TODO support packageName
200244
client.addPlsqlStoredProcedure(procedureName.getName(), procedureName.getCtlId(), procedureName.getDbId(),
201245
"",
202-
ConnectContext.get().getQualifiedUser(), source, isForce);
246+
ConnectContext.get().getQualifiedUser(), source, createTime, modifyTime, isForce);
203247
} catch (Exception e) {
204248
throw new RuntimeException("failed to save procedure", e);
205249
}

‎fe/fe-core/src/main/java/org/apache/doris/plsql/functions/FunctionRegistry.java‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,8 @@
2525
import org.apache.doris.nereids.PLParser.Expr_func_paramsContext;
2626
import org.apache.doris.nereids.trees.plans.commands.info.FuncNameInfo;
2727

28+
import java.util.List;
29+
2830
public interface FunctionRegistry {
2931
boolean exec(FuncNameInfo procedureName, Expr_func_paramsContext ctx);
3032

@@ -39,4 +41,8 @@ public interface FunctionRegistry {
3941
void remove(FuncNameInfo procedureName);
4042

4143
void removeCached(String name);
44+
45+
void showProcedure(List<List<String>> columns);
46+
47+
void showCreateProcedure(FuncNameInfo procedureName, List<List<String>> columns);
4248
}

0 commit comments

Comments
 (0)