Connect to database overview of schemas by default

[er1kb]

This is a possible feature request on the postgres loader which might be relevant to other database loaders too.

If no postgres_schema is specified at the command line, the public schema is used. Sometimes the public schema has tables in it, and sometimes not. I would prefer to get a list of the available schemas instead, ie opening the information_schema.schemata table by default. You can reach that table manually within Visidata by connecting to the information_schema schema and opening the schemata table, but that sheet (the PgTable sheet representing the schemata table) does not allow you to open the actual tables within.

Visidata is an excellent improvement on tools like PgAdmin when you are mainly interested in the data itself (as opposed to having a complete db administration tool). It would be convenient to be able to traverse the three levels of the database: from the list of available schemas, to a list of tables within a schema, down to an individual table. That would require an intermediate level, eg a PgSchemasSheet, or possibly a less bloated solution that re-uses the PgTablesSheet class for the schema level.

I don't know what is possible or already implemented for the other database loaders. According to Wikipedia,the information_schema.schemata table is an ANSI standard supported by MySQL and Clickhouse but not SQLite. Has this kind of default behaviour been considered already? Is there an existing feature that I am overlooking? It seems that in order to explore an entire database without knowing anything about its contents, you need to keep one Visidata instance showing the schemas and another one to connect to these schemas individually. Sometimes you look for information in a database and you don't know exactly which table it's in. For those situations it would be useful to have access to an IndexSheet of available schemas and being able to move between the different levels, just as one would interact with the local sheets of Visidata. Maybe zS would take you back to the landing page of the SQL database.

Moreover, connecting to the public schema by default only makes sense if the user is aware of it. I am pretty sure the error in #1743 was not due to a bug in Visidata, but because the public schema of that database has no tables in it. This works:

vd -f postgres postgres://reader:[email protected]:5432/pfmegrnargs --postgres-schema=rnacen

This also works, but returns an empty response:

vd -f postgres postgres://reader:[email protected]:5432/pfmegrnargs