Show Table about phoenix

[jtaylor-sfdc]

Good idea. How/where/from what client would you invoke this? Is this something you'd like to put in a .sql file and run with our psql command line tool? Or are you using SQuirrel, an open source SQL client? If the latter, you can get at the list of tables through the "New Database Object Tree" button in the tool bar or through the "Objects" tab (next to the default "SQL" tab when you open a connection).

If the former, this would be a good, isolated contribution someone could make. Here's what would be required:

• Derive a new ShowTablesStatement in com.salesforce.phoenix.parse that implements SQLStatement. This is the AST node class that captures any state at parse time. In this case, unless the SHOW TABLES has other options, you may not have any state.
• Add to ParseNodeFactory a showTables() method that instantiates your new ShowTablesStatement.
• Modify PhoenixSQL.g to include a SHOW TABLES command. Use the drop_table rule/action as a guide - this will be more or less the same:
  • Add SHOW and TABLES to the list of tokens at the top of the file.
  • Add a SHOW TABLES rule to oneStatement rule where all the other top level commands start.
  • Use the factory.showTables() to instantiate the ShowTablesStatement as the action of the rule.
• In PhoenixStatement:
  • create an ExecutableShowTablesStatement that extends your ShowTablesStatement and implements ExecutableStatement (just copy/paste from ExecutableAddColumnStatement).
  • modify ExecutableNodeFactory to override the showTables method to instantiate and return your ExecutableShowTablesStatement.
  • In your ExecutableShowTablesStatement implement the executeUpdate method and use the standard JDBC way to access table metadata:
    

    
    
    // TODO: if SHOW TABLES allows one to specify a schema pattern,
    
    // then pass that info through here in the second argument to getTables
    
    ResultSet rs = connection.getMetaData().getTables(null,null,null,null);
    
    while (rs.next()) {
    
    String schema = rs.getString(2);
    
    String table = rs.getString(3);
    
    String name = SchemaUtil.getTableDisplayName(schema,table);
    
    System.out.println(name);
    
    }
    
    

• Create a few unit tests to verify it's working correctly. You could add these to QueryDatabaseMetaDataTest.

[jtaylor-sfdc]

Christopher,
Anything I can do to help? Should I just pull in your original request - there were only a couple of minor items to fix up.
Thanks!
James

[testn]

I'm still waiting for my approval to contribute the code... I will submit it again once I get the green light.

[jtaylor-sfdc]

Ah, ok. Thanks for the heads up

[jtaylor-sfdc]

This has been added (thanks, @testn). Still needs to be added to the documentation (in docs/phoenix.csv)

[colorant]

mark.

[jtaylor-sfdc]

With SQLLine support plus JDBC driver support of the metadata APIs, I don't think we need this any longer.