Skip to content

Commit 810d546

Browse files
committed
[enhancement](plsql) Support show procedure and show create procedure (apache#31297)
1 parent bdbad5b commit 810d546

14 files changed

Lines changed: 286 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;
@@ -367,6 +369,8 @@
367369
import org.apache.doris.nereids.trees.plans.commands.RefreshMTMVCommand;
368370
import org.apache.doris.nereids.trees.plans.commands.ResumeMTMVCommand;
369371
import org.apache.doris.nereids.trees.plans.commands.ShowConstraintsCommand;
372+
import org.apache.doris.nereids.trees.plans.commands.ShowCreateProcedureCommand;
373+
import org.apache.doris.nereids.trees.plans.commands.ShowProcedureStatusCommand;
370374
import org.apache.doris.nereids.trees.plans.commands.UpdateCommand;
371375
import org.apache.doris.nereids.trees.plans.commands.info.AlterMTMVInfo;
372376
import org.apache.doris.nereids.trees.plans.commands.info.AlterMTMVPropertyInfo;
@@ -3376,4 +3380,16 @@ public LogicalPlan visitDropProcedure(DropProcedureContext ctx) {
33763380
FuncNameInfo procedureName = new FuncNameInfo(nameParts);
33773381
return ParserUtils.withOrigin(ctx, () -> new DropProcedureCommand(procedureName, getOriginSql(ctx)));
33783382
}
3383+
3384+
@Override
3385+
public LogicalPlan visitShowProcedureStatus(ShowProcedureStatusContext ctx) {
3386+
return ParserUtils.withOrigin(ctx, () -> new ShowProcedureStatusCommand());
3387+
}
3388+
3389+
@Override
3390+
public LogicalPlan visitShowCreateProcedure(ShowCreateProcedureContext ctx) {
3391+
List<String> nameParts = visitMultipartIdentifier(ctx.name);
3392+
FuncNameInfo procedureName = new FuncNameInfo(nameParts);
3393+
return ParserUtils.withOrigin(ctx, () -> new ShowCreateProcedureCommand(procedureName));
3394+
}
33793395
}

‎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
@@ -144,5 +144,7 @@ public enum PlanType {
144144
CANCEL_MTMV_TASK_COMMAND,
145145
CALL_COMMAND,
146146
CREATE_PROCEDURE_COMMAND,
147-
DROP_PROCEDURE_COMMAND
147+
DROP_PROCEDURE_COMMAND,
148+
SHOW_PROCEDURE_COMMAND,
149+
SHOW_CREATE_PROCEDURE_COMMAND
148150
}
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
@@ -38,6 +38,8 @@
3838
import org.apache.doris.nereids.trees.plans.commands.RefreshMTMVCommand;
3939
import org.apache.doris.nereids.trees.plans.commands.ResumeMTMVCommand;
4040
import org.apache.doris.nereids.trees.plans.commands.ShowConstraintsCommand;
41+
import org.apache.doris.nereids.trees.plans.commands.ShowCreateProcedureCommand;
42+
import org.apache.doris.nereids.trees.plans.commands.ShowProcedureStatusCommand;
4143
import org.apache.doris.nereids.trees.plans.commands.UpdateCommand;
4244
import org.apache.doris.nereids.trees.plans.commands.insert.BatchInsertIntoTableCommand;
4345
import org.apache.doris.nereids.trees.plans.commands.insert.InsertIntoTableCommand;
@@ -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: 41 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,33 @@ 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+
row.add(proc.getName());
116+
row.add(proc.getSource());
117+
columns.add(row);
118+
}
88119

89120
@Override
90121
public boolean exec(FuncNameInfo procedureName, Expr_func_paramsContext ctx) {
@@ -196,10 +227,19 @@ public void addUserProcedure(Create_procedure_stmtContext ctx) {
196227
@Override
197228
public void save(FuncNameInfo procedureName, String source, boolean isForce) {
198229
try {
230+
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
231+
String createTime = f.format(Calendar.getInstance().getTime());
232+
String modifyTime = createTime;
233+
if (isForce) {
234+
// need to get create time and use that.
235+
PlsqlStoredProcedure oldProc = client.getPlsqlStoredProcedure(procedureName.getName(),
236+
procedureName.getCtlId(), procedureName.getDbId());
237+
createTime = oldProc.getCreateTime();
238+
}
199239
// TODO support packageName
200240
client.addPlsqlStoredProcedure(procedureName.getName(), procedureName.getCtlId(), procedureName.getDbId(),
201241
"",
202-
ConnectContext.get().getQualifiedUser(), source, isForce);
242+
ConnectContext.get().getQualifiedUser(), source, createTime, modifyTime, isForce);
203243
} catch (Exception e) {
204244
throw new RuntimeException("failed to save procedure", e);
205245
}

‎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)