[jira] [Commented] (PHOENIX-5543) Implement show schemas / show tables SQL commands

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-5543?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17391411#comment-17391411 ] 

ASF GitHub Bot commented on PHOENIX-5543:
-----------------------------------------

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


> Implement show schemas / show tables SQL commands
> -------------------------------------------------
>
>                 Key: PHOENIX-5543
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-5543
>             Project: Phoenix
>          Issue Type: Improvement
>    Affects Versions: 4.15.0, 5.1.0
>            Reporter: Bharath Vissapragada
>            Assignee: Bharath Vissapragada
>            Priority: Minor
>             Fix For: 5.1.0, 4.16.1, 4.17.0
>
>         Attachments: PHOENIX-5543-4.x.v1.patch, PHOENIX-5543.master.v1.patch, PHOENIX-5543.master.v2.patch, PHOENIX-5543.master.v3.patch
>
>          Time Spent: 3h 40m
>  Remaining Estimate: 0h
>
> Currently users rely on {{!tables}} and {{!schemas}} commands provided by sqlline which pulls the information using the standard JDBC metadata calls like {{getTables()}} and {{getSchemas()}}.
> Most other databases (like mysql[1,2]) implement these as first class SQL commands that gives the user more flexibility in querying by adding necessary filters and looking up for table information in specific schemas. The ask here is to implement the following SQL commands.
> {noformat}
> SHOW SCHEMAS [LIKE '<pattern>']
> SHOW TABLES [IN <schema>] [LIKE '<pattern>']
> {noformat}
> [1] https://dev.mysql.com/doc/refman/8.0/en/show-tables.html
> [2] https://dev.mysql.com/doc/refman/8.0/en/show-databases.html



--
This message was sent by Atlassian Jira
(v8.3.4#803005)