You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by GitBox <gi...@apache.org> on 2021/07/30 09:48:20 UTC
[GitHub] [phoenix] richardantal opened a new pull request #1278: PHOENIX-5543: Implement SHOW TABLES/SCHEMAS sql commands
richardantal opened a new pull request #1278:
URL: https://github.com/apache/phoenix/pull/1278
This patch adds new SQL grammar like following
- SHOW SCHEMAS [like '<pattern>']
- SHOW TABLES [IN <schema>] [like '<pattern']
Example invocations:
- show schemas
- show scemas like 'SYS%'
- show tables
- show tables in SYSTEM
- show tables in SYSTEM like 'CAT%'
The current way of fetching this information is by using
!tables and !schemas via sqlline JDBC support but that is
not flexible enough for the end users to add more fitlers.
This approach is more inline with what other databases do.
Added test coverage in parser tests and core e2e tests.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [phoenix] richardantal merged pull request #1278: PHOENIX-5543: Implement SHOW TABLES/SCHEMAS sql commands
Posted by GitBox <gi...@apache.org>.
richardantal merged pull request #1278:
URL: https://github.com/apache/phoenix/pull/1278
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [phoenix] richardantal commented on a change in pull request #1278: PHOENIX-5543: Implement SHOW TABLES/SCHEMAS sql commands
Posted by GitBox <gi...@apache.org>.
richardantal commented on a change in pull request #1278:
URL: https://github.com/apache/phoenix/pull/1278#discussion_r680736027
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryDatabaseMetaDataIT.java
##########
@@ -352,6 +355,60 @@ public void testSequenceMetadataScan() throws SQLException {
}
}
+ @Test
+ public void testShowSchemas() throws SQLException {
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ ResultSet rs = conn.prepareStatement("show schemas").executeQuery();
+ assertTrue(rs.next());
+ assertEquals("SYSTEM", rs.getString("TABLE_SCHEM"));
+ assertEquals(null, rs.getString("TABLE_CATALOG"));
+ assertFalse(rs.next());
+ // Create another schema and make sure it is listed.
+ String schema = "showschemastest_" + generateUniqueName();
+ String fullTable = schema + "." + generateUniqueName();
+ ensureTableCreated(getUrl(), fullTable, ENTITY_HISTORY_TABLE_NAME, null);
+ // show schemas
+ rs = conn.prepareStatement("show schemas").executeQuery();
+ Set<String> schemas = new HashSet<>();
+ while (rs.next()) {
+ schemas.add(rs.getString("TABLE_SCHEM"));
+ assertEquals(null, rs.getString("TABLE_CATALOG"));
+ }
+ assertEquals(2, schemas.size());
+ assertTrue(schemas.contains("SYSTEM"));
+ assertTrue(schemas.contains(schema.toUpperCase()));
+ // show schemas like 'SYST%' and only SYSTEM should show up.
+ rs = conn.prepareStatement("show schemas like 'SYST%'").executeQuery();
+ assertTrue(rs.next());
+ assertEquals("SYSTEM", rs.getString("TABLE_SCHEM"));
+ assertEquals(null, rs.getString("TABLE_CATALOG"));
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testShowTables() throws SQLException {
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ // List all the tables in a particular schema.
+ ResultSet rs = conn.prepareStatement("show tables in SYSTEM").executeQuery();
+ Set<String> tables = new HashSet<>();
+ while (rs.next()) {
+ tables.add(rs.getString("TABLE_NAME"));
+ assertEquals("SYSTEM", rs.getString("TABLE_SCHEM"));
+ }
+ assertEquals(8, tables.size());
+ assertTrue(tables.contains("CATALOG"));
Review comment:
In line 396 we add what is in the TABLE_NAME column to the set called tables, here we assert that `CATALOG` and `FUNCTION` is in that set of tables.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [phoenix] richardantal edited a comment on pull request #1278: PHOENIX-5543: Implement SHOW TABLES/SCHEMAS sql commands
Posted by GitBox <gi...@apache.org>.
richardantal edited a comment on pull request #1278:
URL: https://github.com/apache/phoenix/pull/1278#issuecomment-890810753
Hey @dbwong
This is a backport PR, we already have this in the 4.x, master and 5.1 branches, 4.16 just got forgotten.
I'll try to answer your questions but I am not the original owner of the ticket.
It will list tables, views and indexes as well.
The output is the same as the output for `!tables` and `!schemas` command, here it is possible to filter those results.
```
jdbc:phoenix:localhost:65205> show tables like 'foo%';
+-----------+-------------+------------+------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+--------------+----------------+-----------+------------+---------------+---------------------+-------------------+---------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT | VIEW_TYPE | INDEX_TYPE | TRANSACTIONAL | IS_NAMESPACE_MAPPED | GUIDE_POSTS_WIDTH | TRANSAC |
+-----------+-------------+------------+------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+--------------+----------------+-----------+------------+---------------+---------------------+-------------------+---------+
| | | foo_ind | INDEX | | | | | ACTIVE | false | null | false | | | GLOBAL | false | false | null | |
| | | foo_1 | TABLE | | | | | | false | null | false | | | | false | false | null | |
| | | foo_view | VIEW | | | | | | false | null | false | | UPDATABLE | | false | false | null | |
+-----------+-------------+------------+------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+--------------+----------------+-----------+------------+---------------+---------------------+-------------------+---------+
3 rows selected (0.023 seconds)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [phoenix] dbwong commented on a change in pull request #1278: PHOENIX-5543: Implement SHOW TABLES/SCHEMAS sql commands
Posted by GitBox <gi...@apache.org>.
dbwong commented on a change in pull request #1278:
URL: https://github.com/apache/phoenix/pull/1278#discussion_r680198866
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryDatabaseMetaDataIT.java
##########
@@ -352,6 +355,60 @@ public void testSequenceMetadataScan() throws SQLException {
}
}
+ @Test
+ public void testShowSchemas() throws SQLException {
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ ResultSet rs = conn.prepareStatement("show schemas").executeQuery();
+ assertTrue(rs.next());
+ assertEquals("SYSTEM", rs.getString("TABLE_SCHEM"));
+ assertEquals(null, rs.getString("TABLE_CATALOG"));
+ assertFalse(rs.next());
+ // Create another schema and make sure it is listed.
+ String schema = "showschemastest_" + generateUniqueName();
+ String fullTable = schema + "." + generateUniqueName();
+ ensureTableCreated(getUrl(), fullTable, ENTITY_HISTORY_TABLE_NAME, null);
+ // show schemas
+ rs = conn.prepareStatement("show schemas").executeQuery();
+ Set<String> schemas = new HashSet<>();
+ while (rs.next()) {
+ schemas.add(rs.getString("TABLE_SCHEM"));
+ assertEquals(null, rs.getString("TABLE_CATALOG"));
+ }
+ assertEquals(2, schemas.size());
+ assertTrue(schemas.contains("SYSTEM"));
+ assertTrue(schemas.contains(schema.toUpperCase()));
+ // show schemas like 'SYST%' and only SYSTEM should show up.
+ rs = conn.prepareStatement("show schemas like 'SYST%'").executeQuery();
+ assertTrue(rs.next());
+ assertEquals("SYSTEM", rs.getString("TABLE_SCHEM"));
+ assertEquals(null, rs.getString("TABLE_CATALOG"));
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testShowTables() throws SQLException {
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ // List all the tables in a particular schema.
+ ResultSet rs = conn.prepareStatement("show tables in SYSTEM").executeQuery();
+ Set<String> tables = new HashSet<>();
+ while (rs.next()) {
+ tables.add(rs.getString("TABLE_NAME"));
+ assertEquals("SYSTEM", rs.getString("TABLE_SCHEM"));
+ }
+ assertEquals(8, tables.size());
+ assertTrue(tables.contains("CATALOG"));
Review comment:
Since this is a user facing feature i think we should probably test against exact string we expect as people may end up relying on the message.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [phoenix] richardantal commented on pull request #1278: PHOENIX-5543: Implement SHOW TABLES/SCHEMAS sql commands
Posted by GitBox <gi...@apache.org>.
richardantal commented on pull request #1278:
URL: https://github.com/apache/phoenix/pull/1278#issuecomment-890810753
Hey @dbwong
This is a backport PR, we already have this in the 4.x, master and 5.1 branches, 4.16 just got forgotten.
I'll try to answer your questions but I am not the original owner of the ticket.
It will list tables, views and indexes as well.
The output is the same as the output for `!tables` and `!schemas` command, here you it is possible to filter those results.
```
jdbc:phoenix:localhost:65205> show tables like 'foo%';
+-----------+-------------+------------+------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+--------------+----------------+-----------+------------+---------------+---------------------+-------------------+---------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT | VIEW_TYPE | INDEX_TYPE | TRANSACTIONAL | IS_NAMESPACE_MAPPED | GUIDE_POSTS_WIDTH | TRANSAC |
+-----------+-------------+------------+------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+--------------+----------------+-----------+------------+---------------+---------------------+-------------------+---------+
| | | foo_ind | INDEX | | | | | ACTIVE | false | null | false | | | GLOBAL | false | false | null | |
| | | foo_1 | TABLE | | | | | | false | null | false | | | | false | false | null | |
| | | foo_view | VIEW | | | | | | false | null | false | | UPDATABLE | | false | false | null | |
+-----------+-------------+------------+------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+--------------+----------------+-----------+------------+---------------+---------------------+-------------------+---------+
3 rows selected (0.023 seconds)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [phoenix] dbwong commented on pull request #1278: PHOENIX-5543: Implement SHOW TABLES/SCHEMAS sql commands
Posted by GitBox <gi...@apache.org>.
dbwong commented on pull request #1278:
URL: https://github.com/apache/phoenix/pull/1278#issuecomment-890131105
One thing that wasn't entirely clear to me was were we considering views in the base JIRA or only tables for the initial scope?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [phoenix] stoty commented on pull request #1278: PHOENIX-5543: Implement SHOW TABLES/SCHEMAS sql commands
Posted by GitBox <gi...@apache.org>.
stoty commented on pull request #1278:
URL: https://github.com/apache/phoenix/pull/1278#issuecomment-890068290
:broken_heart: **-1 overall**
| Vote | Subsystem | Runtime | Comment |
|:----:|----------:|--------:|:--------|
| +0 :ok: | reexec | 0m 35s | Docker mode activated. |
||| _ Prechecks _ |
| +1 :green_heart: | dupname | 0m 0s | No case conflicting files found. |
| +1 :green_heart: | hbaseanti | 0m 0s | Patch does not have any anti-patterns. |
| +1 :green_heart: | @author | 0m 0s | The patch does not contain any @author tags. |
| +1 :green_heart: | test4tests | 0m 0s | The patch appears to include 1 new or modified test files. |
||| _ 4.16 Compile Tests _ |
| +0 :ok: | mvndep | 5m 17s | Maven dependency ordering for branch |
| +1 :green_heart: | mvninstall | 13m 40s | 4.16 passed |
| +1 :green_heart: | compile | 1m 39s | 4.16 passed |
| +1 :green_heart: | checkstyle | 1m 27s | 4.16 passed |
| +1 :green_heart: | javadoc | 1m 3s | 4.16 passed |
| +0 :ok: | spotbugs | 3m 9s | phoenix-core in 4.16 has 948 extant spotbugs warnings. |
| +0 :ok: | spotbugs | 0m 55s | phoenix-pherf in 4.16 has 42 extant spotbugs warnings. |
||| _ Patch Compile Tests _ |
| +0 :ok: | mvndep | 0m 11s | Maven dependency ordering for patch |
| +1 :green_heart: | mvninstall | 12m 45s | the patch passed |
| +1 :green_heart: | compile | 2m 19s | the patch passed |
| +1 :green_heart: | javac | 2m 19s | the patch passed |
| -1 :x: | checkstyle | 1m 38s | phoenix-core: The patch generated 142 new + 1080 unchanged - 103 fixed = 1222 total (was 1183) |
| -1 :x: | checkstyle | 0m 17s | phoenix-pherf: The patch generated 1 new + 68 unchanged - 6 fixed = 69 total (was 74) |
| -1 :x: | whitespace | 0m 0s | The patch 1 line(s) with tabs. |
| +1 :green_heart: | javadoc | 1m 25s | the patch passed |
| -1 :x: | spotbugs | 4m 50s | phoenix-core generated 5 new + 943 unchanged - 5 fixed = 948 total (was 948) |
||| _ Other Tests _ |
| +1 :green_heart: | unit | 143m 17s | phoenix-core in the patch passed. |
| -1 :x: | unit | 7m 36s | phoenix-pherf in the patch failed. |
| +1 :green_heart: | asflicense | 0m 19s | The patch does not generate ASF License warnings. |
| | | 205m 20s | |
| Reason | Tests |
|-------:|:------|
| FindBugs | module:phoenix-core |
| | org.apache.phoenix.util.QueryUtil.getCatalogsStmt(PhoenixConnection) may fail to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:[line 528] is not discharged |
| | org.apache.phoenix.util.QueryUtil.getIndexInfoStmt(PhoenixConnection, String, String, String, boolean, boolean) may fail to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:boolean) may fail to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:[line 635] is not discharged |
| | org.apache.phoenix.util.QueryUtil.getSchemasStmt(PhoenixConnection, String, String) may fail to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:[line 560] is not discharged |
| | org.apache.phoenix.util.QueryUtil.getSuperTablesStmt(PhoenixConnection, String, String, String) may fail to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:fail to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:[line 590] is not discharged |
| | org.apache.phoenix.util.QueryUtil.getTablesStmt(PhoenixConnection, String, String, String, String[]) may fail to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:may fail to clean up java.sql.Statement on checked exception Obligation to clean up resource created at QueryUtil.java:[line 765] is not discharged |
| Failed junit tests | phoenix.pherf.PherfMainIT |
| Subsystem | Report/Notes |
|----------:|:-------------|
| Docker | ClientAPI=1.41 ServerAPI=1.41 base: https://ci-hadoop.apache.org/job/Phoenix/job/Phoenix-PreCommit-GitHub-PR/job/PR-1278/2/artifact/yetus-general-check/output/Dockerfile |
| GITHUB PR | https://github.com/apache/phoenix/pull/1278 |
| JIRA Issue | PHOENIX-5543 |
| Optional Tests | dupname asflicense javac javadoc unit spotbugs hbaseanti checkstyle compile |
| uname | Linux e37a2dc1b998 4.15.0-60-generic #67-Ubuntu SMP Thu Aug 22 16:55:30 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux |
| Build tool | maven |
| Personality | dev/phoenix-personality.sh |
| git revision | 4.16 / 3a4339e |
| Default Java | Private Build-1.8.0_242-8u242-b08-0ubuntu3~16.04-b08 |
| checkstyle | https://ci-hadoop.apache.org/job/Phoenix/job/Phoenix-PreCommit-GitHub-PR/job/PR-1278/2/artifact/yetus-general-check/output/diff-checkstyle-phoenix-core.txt |
| checkstyle | https://ci-hadoop.apache.org/job/Phoenix/job/Phoenix-PreCommit-GitHub-PR/job/PR-1278/2/artifact/yetus-general-check/output/diff-checkstyle-phoenix-pherf.txt |
| whitespace | https://ci-hadoop.apache.org/job/Phoenix/job/Phoenix-PreCommit-GitHub-PR/job/PR-1278/2/artifact/yetus-general-check/output/whitespace-tabs.txt |
| spotbugs | https://ci-hadoop.apache.org/job/Phoenix/job/Phoenix-PreCommit-GitHub-PR/job/PR-1278/2/artifact/yetus-general-check/output/new-spotbugs-phoenix-core.html |
| unit | https://ci-hadoop.apache.org/job/Phoenix/job/Phoenix-PreCommit-GitHub-PR/job/PR-1278/2/artifact/yetus-general-check/output/patch-unit-phoenix-pherf.txt |
| Test Results | https://ci-hadoop.apache.org/job/Phoenix/job/Phoenix-PreCommit-GitHub-PR/job/PR-1278/2/testReport/ |
| Max. process+thread count | 5703 (vs. ulimit of 30000) |
| modules | C: phoenix-core phoenix-pherf U: . |
| Console output | https://ci-hadoop.apache.org/job/Phoenix/job/Phoenix-PreCommit-GitHub-PR/job/PR-1278/2/console |
| versions | git=2.7.4 maven=3.3.9 spotbugs=4.1.3 |
| Powered by | Apache Yetus 0.12.0 https://yetus.apache.org |
This message was automatically generated.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org