Enhance to support named schames in spanner-cli commands

[apstndb]

Currently, there are no handling about named schemas.

I think some commands in spanner-cli that target schema objects need to be extended to support named schemas. Let's take the SHOW TABLES command as an example.

The current behavior:

• SHOW TABLES; shows tables only in the default schema.

The proposed behavior:

• (preserving the current behavior) SHOW TABLES; shows tables only in the default schema.
• SHOW TABLES <schema>; shows tables only in the schema named schema.

Another considerations

• SHOW USER TABLES; shows tables in all user generated schemas(without INFORMATION_SCHEMA, SPANNER_SYS. SCHEMA_OWNER != "spanner_system").
• SHOW SYSTEM TABLES; shows tables in all system generated schemas(SCHEMA_OWNER = "spanner_system").
• SHOW ALL TABLES; shows tables in all schemas(USER + SYSTEM).
• SHOW SCHEMAS; shows all schemas.
  • SHOW SCHEMATA may not be user friendly.

[yfuruyama]

Thank you for filing this issue.

The proposed behavior:

• (preserving the current behavior) SHOW TABLES; shows tables only in the default schema.
• SHOW TABLES ; shows tables only in the schema named schema.

How about making SHOW TABLES shows all tables in all named schemas except SPANNER_SYS and INFORMATION_SCHEMA?

If a user has lots of named schemas to manage multiple tables, it might be tedious to run different commands to get all the tables.

As the document says, user needs to use fully qualified name (FQN) to reference a database object in a non-default named schema, so if we can show the table name with FQN, it would be not so weird to show all tables in flat.

For example,

spanner> SHOW TABLES;
+---------------+
| Tables_in_d01 |
+---------------+
| Orders        |
| Players       |
| sch1.Orders   |
| sch1.Players  |
+---------------+

[apstndb]

This is the opinion that it would be better to have a way to list tables across schemas. I think it's better to have a way to only do things in a particular schema. I think these features can coexist like SHOW TABLES <schema> and SHOW USER TABLES.

Named schemas can be context boundaries and may even be access boundaries using FGAC. There may be a use case where a database has a large number of schema objects and users want to see only the schema objects in a particular schema that are relevant to them. The default schema is not an exception.

[apstndb]

Underlying Query of "show all user tables"

SELECT ARRAY_TO_STRING([NULLIF(TABLE_SCHEMA, ""), TABLE_NAME], ".") AS QUALIFIED_TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES 
WHERE (TABLE_CATALOG, TABLE_SCHEMA) IN (
  SELECT AS STRUCT CATALOG_NAME, SCHEMA_NAME
  FROM INFORMATION_SCHEMA.SCHEMATA
  WHERE SCHEMA_OWNER != "spanner_system"
)
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME;

[yfuruyama]

I reconsidered this again and I think that users might use the named schema as a "logical database" while utilizing the database functionalities (such as transactions). So from users perspective, it might not be a big problem to use a different command to show the tables in the named schema because they should be already familiar with the explicit USE database command when switching the database.

So returning to the first point, I agree your initial proposed behavior.

• SHOW TABLES; shows tables only in the default schema.
• SHOW TABLES <schema>; shows tables only in the named schema.

By the way, I want to be careful to create a new spanner-cli-specific command, so unless we find it extremely useful, I want to defer creating new commands such as SHOW USERS TABLES or SHOW SYSTEM TABLES.

[apstndb]

We have a consensus. I will implement SHOW TABLES <schema>. Additionally, there are other commands besides SHOW TABLES that do not support named schemas.

• SHOW CREATE TABLE <table>;
• SHOW COLUMNS FROM <table>;
• SHOW INDEX FROM <table>;

[yfuruyama]

For other commands, maybe we can change it to accept fully qualified name (FQN), like SHOW CREATE TABLE myschema.mytable. It aligns with SQL syntax, which requires to use FQN for referencing the table in a named schema.

[apstndb]

I agree to use FQN as current <table> usages.