This is an automated email from the ASF dual-hosted git repository.
xuyang pushed a commit to branch branch-1.2-lts
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-1.2-lts by this push:
new a3009fb53ea [branch-1.2](chore)support statement to show views from
database (#33510)
a3009fb53ea is described below
commit a3009fb53ea7df1827477e8754dc8b8e9eecae94
Author: xy720 <[email protected]>
AuthorDate: Thu Apr 11 10:28:00 2024 +0800
[branch-1.2](chore)support statement to show views from database (#33510)
MySQL [test]> show views;
+----------------+
| Tables_in_test |
+----------------+
| t1_view |
| t2_view |
+----------------+
2 rows in set (0.00 sec)
MySQL [test]> show views like '%t1%';
+----------------+
| Tables_in_test |
+----------------+
| t1_view |
+----------------+
1 row in set (0.01 sec)
MySQL [test]> show views where create_time > '2024-03-18';
+----------------+
| Tables_in_test |
+----------------+
| t2_view |
+----------------+
1 row in set (0.02 sec)
---
.../sql-reference/Show-Statements/SHOW-VIEWS.md | 78 ++++++++++++++++++++++
.../sql-reference/Show-Statements/SHOW-VIEWS.md | 78 ++++++++++++++++++++++
fe/fe-core/src/main/cup/sql_parser.cup | 14 ++++
.../org/apache/doris/analysis/ShowTableStmt.java | 34 +++++++++-
.../java/org/apache/doris/qe/ShowExecutor.java | 3 +
fe/fe-core/src/main/jflex/sql_scanner.flex | 1 +
.../apache/doris/analysis/ShowTableStmtTest.java | 23 +++++++
.../java/org/apache/doris/qe/ShowExecutorTest.java | 10 +++
8 files changed, 238 insertions(+), 3 deletions(-)
diff --git
a/docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
b/docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
new file mode 100644
index 00000000000..5622be47fa3
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
@@ -0,0 +1,78 @@
+---
+{
+ "title": "SHOW-VIEWS",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## SHOW-VIEWS
+
+### Name
+
+SHOW VIEWS
+
+### Description
+
+This statement is used to display all logical views under the current db
+
+grammar:
+
+```sql
+SHOW [FULL] VIEWS [LIKE]
+````
+
+illustrate:
+
+1. LIKE: Fuzzy query can be performed according to the table name
+
+### Example
+
+ 1. Desplay all views under DB
+
+ ```sql
+ MySQL [test]> show views;
+ +----------------+
+ | Tables_in_test |
+ +----------------+
+ | t1_view |
+ | t2_view |
+ +----------------+
+ 2 rows in set (0.00 sec)
+ ```
+
+2. Fuzzy query by view name
+
+ ```sql
+ MySQL [test]> show views like '%t1%';
+ +----------------+
+ | Tables_in_test |
+ +----------------+
+ | t1_view |
+ +----------------+
+ 1 row in set (0.01 sec)
+ ```
+
+### Keywords
+
+ SHOW, VIEWS
+
+### Best Practice
diff --git
a/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
b/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
new file mode 100644
index 00000000000..41262dba693
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
@@ -0,0 +1,78 @@
+---
+{
+ "title": "SHOW-VIEWS",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## SHOW-VIEWS
+
+### Name
+
+SHOW VIEWS
+
+### Description
+
+该语句用于展示当前 db 下所有的 logical view
+
+语法：
+
+```sql
+SHOW [FULL] VIEWS [LIKE] | [WHERE where_condition]
+```
+
+说明:
+
+1. LIKE：可按照表名进行模糊查询
+
+### Example
+
+ 1. 查看DB下所有逻辑视图
+
+ ```sql
+ MySQL [test]> show views;
+ +----------------+
+ | Tables_in_test |
+ +----------------+
+ | t1_view |
+ | t2_view |
+ +----------------+
+ 2 rows in set (0.00 sec)
+ ```
+
+2. 按照VIEW名进行模糊查询
+
+ ```sql
+ MySQL [test]> show views like '%t1%';
+ +----------------+
+ | Tables_in_test |
+ +----------------+
+ | t1_view |
+ +----------------+
+ 1 row in set (0.01 sec)
+ ```
+
+### Keywords
+
+ SHOW, VIEWS
+
+### Best Practice
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index 7de2e8b5fc1..b72e4d4ed77 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -43,6 +43,7 @@ import org.apache.doris.catalog.ArrayType;
import org.apache.doris.catalog.MapType;
import org.apache.doris.catalog.StructField;
import org.apache.doris.catalog.StructType;
+import org.apache.doris.catalog.TableIf.TableType;
import org.apache.doris.catalog.View;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.FeConstants;
@@ -597,6 +598,7 @@ terminal String
KW_VERBOSE,
KW_VERSION,
KW_VIEW,
+ KW_VIEWS,
KW_WARNINGS,
KW_WEEK,
KW_WHEN,
@@ -3384,6 +3386,16 @@ show_param ::=
{:
RESULT = new ShowTableStmt(db, ctl, parser.isVerbose, parser.wild,
parser.where);
:}
+ /* show views */
+ | opt_full KW_VIEWS opt_db:db opt_wild_where
+ {:
+ RESULT = new ShowTableStmt(db, null, parser.isVerbose, TableType.VIEW,
parser.wild, parser.where);
+ :}
+ /* show views */
+ | opt_full KW_VIEWS from_or_in ident:ctl DOT ident:db opt_wild_where
+ {:
+ RESULT = new ShowTableStmt(db, ctl, parser.isVerbose, TableType.VIEW,
parser.wild, parser.where);
+ :}
/* show table id */
| KW_TABLE INTEGER_LITERAL:tableId
{:
@@ -6852,6 +6864,8 @@ keyword ::=
{: RESULT = id; :}
| KW_VIEW:id
{: RESULT = id; :}
+ | KW_VIEWS:id
+ {: RESULT = id; :}
| KW_WARNINGS:id
{: RESULT = id; :}
| KW_WORK:id
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowTableStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowTableStmt.java
index c14a05ea164..cd3b3fb6117 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowTableStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowTableStmt.java
@@ -20,6 +20,7 @@ package org.apache.doris.analysis;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.InfoSchemaDb;
import org.apache.doris.catalog.ScalarType;
+import org.apache.doris.catalog.TableIf.TableType;
import org.apache.doris.cluster.ClusterNamespace;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.ErrorCode;
@@ -42,8 +43,9 @@ public class ShowTableStmt extends ShowStmt {
InfoSchemaDb.DATABASE_NAME, "tables");
private String db;
private String catalog;
- private boolean isVerbose;
- private String pattern;
+ private final boolean isVerbose;
+ private TableType type;
+ private final String pattern;
private Expr where;
private SelectStmt selectStmt;
@@ -63,6 +65,12 @@ public class ShowTableStmt extends ShowStmt {
this.catalog = catalog;
}
+ public ShowTableStmt(String db, String catalog, boolean isVerbose,
TableType type, String pattern,
+ Expr where) {
+ this(db, catalog, isVerbose, pattern, where);
+ this.type = type;
+ }
+
public String getDb() {
return db;
}
@@ -75,6 +83,10 @@ public class ShowTableStmt extends ShowStmt {
return isVerbose;
}
+ public TableType getType() {
+ return type;
+ }
+
public String getPattern() {
return pattern;
}
@@ -122,6 +134,11 @@ public class ShowTableStmt extends ShowStmt {
selectList.addItem(item);
aliasMap.put(new SlotRef(null, TYPE_COL),
item.getExpr().clone(null));
}
+ if (type != null) {
+ BinaryPredicate viewFilter = new
BinaryPredicate(BinaryPredicate.Operator.EQ,
+ new SlotRef(TABLE_NAME, "ENGINE"), new
StringLiteral(type.toEngineName()));
+ where = CompoundPredicate.createConjunction(viewFilter, where);
+ }
where = where.substitute(aliasMap);
selectStmt = new SelectStmt(selectList,
new FromClause(Lists.newArrayList(new TableRef(TABLE_NAME,
null))),
@@ -139,7 +156,18 @@ public class ShowTableStmt extends ShowStmt {
if (isVerbose) {
sb.append(" FULL");
}
- sb.append(" TABLES");
+ if (type != null) {
+ switch (type) {
+ // todo(only show views from now)
+ case VIEW:
+ sb.append(" VIEWS");
+ break;
+ default:
+ sb.append(" TABLES");
+ }
+ } else {
+ sb.append(" TABLES");
+ }
if (!Strings.isNullOrEmpty(db)) {
if (!Strings.isNullOrEmpty(catalog)) {
sb.append(" FROM ").append(catalog);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/ShowExecutor.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/ShowExecutor.java
index 12220a33177..5a807c78726 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/ShowExecutor.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/ShowExecutor.java
@@ -742,6 +742,9 @@ public class ShowExecutor {
CaseSensibility.TABLE.getCaseSensibility());
}
for (TableIf tbl : db.getTables()) {
+ if (showTableStmt.getType() != null && tbl.getType() !=
showTableStmt.getType()) {
+ continue;
+ }
if (matcher != null && !matcher.match(tbl.getName())) {
continue;
}
diff --git a/fe/fe-core/src/main/jflex/sql_scanner.flex
b/fe/fe-core/src/main/jflex/sql_scanner.flex
index 98667c31a86..1ce903d439c 100644
--- a/fe/fe-core/src/main/jflex/sql_scanner.flex
+++ b/fe/fe-core/src/main/jflex/sql_scanner.flex
@@ -457,6 +457,7 @@ import org.apache.doris.qe.SqlModeHelper;
keywordMap.put("verbose", new Integer(SqlParserSymbols.KW_VERBOSE));
keywordMap.put("version", new Integer(SqlParserSymbols.KW_VERSION));
keywordMap.put("view", new Integer(SqlParserSymbols.KW_VIEW));
+ keywordMap.put("views", new Integer(SqlParserSymbols.KW_VIEWS));
keywordMap.put("warnings", new Integer(SqlParserSymbols.KW_WARNINGS));
keywordMap.put("week", new Integer(SqlParserSymbols.KW_WEEK));
keywordMap.put("when", new Integer(SqlParserSymbols.KW_WHEN));
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowTableStmtTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowTableStmtTest.java
index eac5a8b394a..e8bf19435ad 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowTableStmtTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowTableStmtTest.java
@@ -17,6 +17,7 @@
package org.apache.doris.analysis;
+import org.apache.doris.catalog.TableIf.TableType;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.mysql.privilege.MockedAuth;
import org.apache.doris.mysql.privilege.PaloAuth;
@@ -68,6 +69,28 @@ public class ShowTableStmtTest {
Assert.assertEquals("Table_type",
stmt.getMetaData().getColumn(1).getName());
}
+ @Test
+ public void testShowViews() throws AnalysisException {
+ ShowTableStmt stmt = new ShowTableStmt("", null, false, TableType.VIEW,
+ null, null);
+ stmt.analyze(analyzer);
+ Assert.assertEquals("SHOW VIEWS FROM internal.testDb",
stmt.toString());
+ Assert.assertEquals("testCluster:testDb", stmt.getDb());
+ Assert.assertEquals(TableType.VIEW, stmt.getType());
+ Assert.assertFalse(stmt.isVerbose());
+ Assert.assertEquals(1, stmt.getMetaData().getColumnCount());
+ Assert.assertEquals("Tables_in_testDb",
stmt.getMetaData().getColumn(0).getName());
+
+ stmt = new ShowTableStmt("abc", null, true, TableType.VIEW, "bcd",
null);
+ stmt.analyze(analyzer);
+ Assert.assertEquals("bcd", stmt.getPattern());
+ Assert.assertEquals("SHOW FULL VIEWS FROM internal.abc LIKE 'bcd'",
stmt.toString());
+ Assert.assertEquals(3, stmt.getMetaData().getColumnCount());
+ Assert.assertEquals("Tables_in_abc",
stmt.getMetaData().getColumn(0).getName());
+ Assert.assertEquals("Table_type",
stmt.getMetaData().getColumn(1).getName());
+ Assert.assertEquals(TableType.VIEW, stmt.getType());
+ }
+
@Test(expected = AnalysisException.class)
public void testNoDb() throws AnalysisException {
ShowTableStmt stmt = new ShowTableStmt("", null, false, null);
diff --git a/fe/fe-core/src/test/java/org/apache/doris/qe/ShowExecutorTest.java
b/fe/fe-core/src/test/java/org/apache/doris/qe/ShowExecutorTest.java
index 33384bdc6d5..32ce74851d9 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/qe/ShowExecutorTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/qe/ShowExecutorTest.java
@@ -334,6 +334,16 @@ public class ShowExecutorTest {
Assert.assertFalse(resultSet.next());
}
+ @Test
+ public void testShowViews() throws AnalysisException {
+ ShowTableStmt stmt = new ShowTableStmt("testDb", null, false,
TableType.VIEW,
+ null, null);
+ ShowExecutor executor = new ShowExecutor(ctx, stmt);
+ ShowResultSet resultSet = executor.execute();
+
+ Assert.assertFalse(resultSet.next());
+ }
+
@Test
public void testShowTableFromCatalog() throws AnalysisException {
ShowTableStmt stmt = new ShowTableStmt("testCluster:testDb",
"internal", false, null);
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
