feat(ddl): fill out support for databases and schemas

[cpcloud]

Is your feature request related to a problem?

I'd like to have a consistent interface for exploring and accessing the hierarchy of a given backend's databases.

Here's a table showing each backend's support for two levels of hierarchy: database -> schema and if the name differs in the backend from one or more of those levels I list it:

backend	database	schema	distinct from user
bigquery	project	dataset	Y
clickhouse	database
dask	NA	NA
datafusion	database	schema	Y
druid	connector
duckdb	database	schema	Y
impala	database
mssql	database	schema	Y
mysql	database
oracle	database	user (?)	N
pandas	NA
polars	NA
postgres	database	schema	Y
pyspark	database
snowflake	database	schema	Y
sqlite	database
trino	catalog	schema	Y

Looking at this table it appears that most databases that have support for a second level of hierarchy that is, or can reasonably be called schema.

Describe the solution you'd like

I'd like to be able to do the follow where it makes sense for the backend:

1. Create databases
2. Create schemas
3. List databases
4. List schemas

Given the above table, I think it makes sense to make existing create_database()/drop_database()/list_databases() consistent with that table, and add a create_schema()/drop_schema() for the backends where that makes sense.

What version of ibis are you running?

master

What backend(s) are you using, if any?

NA

Code of Conduct

• I agree to follow this project's Code of Conduct

[gforsyth]

Just to add to this, after doing a little bit of spelunking in the mssql backend -- we probably also need to extend some of these changes to table, list_tables and insert.

On mssql, for instance, I've created a schema named Chains in the default database (not ibis_testing) and created a table called Sizes inside of that schema.

[ins] In [1]: import ibis

[ins] In [2]: # Connect to default db

[ins] In [3]: con = ibis.mssql.connect(user="sa", password="1bis_Testing!", port=1433)

[ins] In [4]: con.list_tables()
Out[4]: 
['MSreplication_options',
 'spt_fallback_db',
 'spt_fallback_dev',
 'spt_fallback_usg',
 'spt_monitor',
 'spt_values']

[ins] In [5]: con.list_tables(database="Chains")
Out[5]: ['Sizes']

[ins] In [6]: con.table("Sizes", database="Chains")
/home/gil/github.com/ibis-project/ibis/ibis/backends/base/sql/alchemy/__init__.py:595: FutureWarning: `BaseBackend.database` is deprecated; use equivalent methods in the backend
  return self.database(name=database).table(name=name, schema=schema)
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[6], line 1
----> 1 con.table("Sizes", database="Chains")

File ~/github.com/ibis-project/ibis/ibis/backends/base/sql/alchemy/__init__.py:595, in BaseAlchemyBackend.table(self, name, database, schema)
    591         raise com.IbisTypeError(
    592             f"`database` must be a string; got {type(database)}"
    593         )
    594     if database != self.current_database:
--> 595         return self.database(name=database).table(name=name, schema=schema)
    597 sqla_table = self._get_sqla_table(name, schema=schema)
    599 schema = self._schema_from_sqla_table(
    600     sqla_table, schema=self._schemas.get(name)
    601 )

TypeError: Database.table() got an unexpected keyword argument 'schema'

[ins] In [7]: # try connecting to Chains using `database` arg

[ins] In [8]: con = ibis.mssql.connect(
         ...:     user="sa", password="1bis_Testing!", port=1433, database="Chains"
         ...: )

[ins] In [9]: con.list_tables()
Auth error

[cpcloud]

Yeah, we'll likely need to add a schema argument to our connect APIs that don't already have one and/or support something like database/schema.

To be clear

[ins] In [8]: con = ibis.mssql.connect(
         ...:     user="sa", password="1bis_Testing!", port=1433, database="Chains"
         ...: )

erroring is expected, since there's no database named Chains, but a schema named Chains inside a database (I'm guessing ibis_testing?).

[gforsyth]

Right, I expected the connect error, just pointing out that you can pass database="Chains" to con.list_tables and it works, so one might conclude (incorrectly) that you could then pass the same argument to connect.

[cpcloud]

Oh totally. That's definitely a rough edge.

[lostmygithubaccount]

@cpcloud should this be in 7.0 instead of 6.1 as a breaking change?

[cpcloud]

@lostmygithubaccount I don't think this breaks anything. I've kept backwards compatibility with BigQuery's list_databases() and added a deprecation warning.