Add drop database

[pauldix]

We need a way to drop a database by name. This should be an HTTP API in the same area where we set options for last value cache, etc. We'll also want to add support to issue the command via the CLI. By default, the CLI should prompt the user to confirm they want to do the operation. There should be a command line option to auto-confirm.

Under the covers, the server should look up the name and log into the WAL a catalog operation to drop the DB by its id and the time the command was issued. When this is applied to the catalog, the name of the DB should be updated to {db_name} - {time of drop} and a marker should be applied to the DB that states that it is soft deleted.

The write buffer and last value caches should be cleared of any data they have for that dbid.

Actually deleting the underlying files and removing the old db from the Catalog will be a separate operation that we can create in the future.

We'll also want to have an undelete option, but that can be handled as separate follow up work.

[praveen-influx]

@pauldix - high level queries on this feature,

• should SHOW DATABASES now filter out databases with soft delete marker/flag set (or) is it ok to return the deleted one in query result?

• when we talk of cli, I assume you're referring to query variant of influxdb3 command. How do you want to enable confirmation? Maybe make the user type the database name in cli? Or is it just yes/no?

• drop database {name}, if name does not exist do we return an error? Do we need to support if exists sort of idea (possibly in the future)?

[hiltontj]

I think in this case, we want a separate CLI from the query command to execute the drop.

The CLI command could follow how we delete last caches, which I think is like:

influxdb3 last_cache delete <params>

i.e.,

influxdb3 database delete <params>

Perhaps talking about the feature as DROP DATABASE is confusing the matter, since that is SQL/DDL and would be used in the context of a query, but I believe we want to keep write operations out of the query path.

[praveen-influx]

Perhaps talking about the feature as DROP DATABASE is confusing the matter, since that is SQL/DDL and would be used in the context of a query, but I believe we want to keep write operations out of the query path.

Yes, I was thinking of DROP DATABASE as DDL/SQL. Thanks for clarifying 👍

[pauldix]

I believe we want to keep write operations out of the query path.

This is correct. For now we'd like any modification/configuration/write operations outside of the query path. We may want to change this at some point. Most other DBs, even ones with custom configuration usually expose their config in the query language.

We may want to do this? I'm not sure yet. Mostly I want to optimize for usability while also keeping people from shooting themselves in the foot.

[praveen-influx]

@pauldix in that case can I use the following syntax for now? That will keep it completely out of the query path. Maybe we can revisit the DDLs in SQL queries as a separate feature as I guess it'll be far more intrusive?

influxdb3 database delete <params>

should SHOW DATABASES now filter out databases with soft delete marker/flag set (or) is it ok to return the deleted one in query result?

I think this is still valid, if we soft delete should they appear in SHOW DATABASES query response marked as soft deleted? Or we just filter it out?

[pauldix]

For now show them as soft deleted